エクセルで、データのある行だけを抽出したい。

質問

エクセルのSheet1にデータが入っており、セルが空白でない行のデータのみを、Sheet2に抽出したいと思っています。
Sheet2にあらかじめ関数を入れておく方法にしたいため、関数について教えてください。

タイムテーブルで予定を管理するような表で、A～Hまでにデータが入っています。
予定を入れる際にはE列には必ず入力をするため、E列が空白でない行のみを抽出したいです。

A｜B｜C｜D｜E｜F｜G｜H
月｜日｜時間｜…
11｜22｜10:00｜…
11｜22｜10:30｜…

時間枠は10:00～17:00の30分刻みで、1日15行使用するので、1年で5475行まで使用します。
1行目はタイトル行なので、データは2行目から入力しています。

フィルタオプションで抽出する方法や、それをマクロで登録しておく方法は分かったのですが、あとから予定を追加することがあるため、マクロを実行する度にデータが置き換わると困ってしまいます。
また、抽出したSheet2のI列以降には備考などを入力したいため、やはり後から列がずれると困るため、A～H列に関数を入れておく方法で実行したいと思います。

よろしくお願いいたします。

回答No.1

KURUMITO · ベストアンサー

最も分かり易く計算にも負担のかからない方法は作業列を作って対応することです。
例えばシート1のJ列を作業列としてJ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(E2="","",MAX(J$1:J1)+1)

シート2では1行目に項目名が有るとしてA2セルには次の式を入力してH2セルまでオートフィルドラッグしたのちに下方向にもオートフィルドラッグします。

=IF(ROW(A1)>MAX(Sheet1!$J:$J),"",INDEX(Sheet1!$A:$H,MATCH(ROW(A1),Sheet1!$J:$J,0),COLUMN(A1)))

お礼

できました！

似たような関数を使っているのを他のページで見て試したのですが、参照範囲などの変更の仕方が間違っているのかうまくいかず…。
困っていたので助かりました。

なぜこのような式になるのかまだ理解できていないので、関数を調べて勉強しようと思います。
ありがとうございました。

回答No.2

mu2011

既に回答済みにつき、マクロ例です。
（１）シート１のJ1にＥ列の見出し名、J2に<>を入力
　　　シート２の１行目から表示する為、シート１の空きセルとしていますので
　　　変更する場合、コードのCriteriaRange:=Sheets("sheet2").Range("j1:j2"),
　　　を変更して下さい。
（２）シート１タブ上で右クリック→コードの表示→以下のコードを貼り付け
　　　このマクロはシート１のイベントプロシージャでＥ列のセルが変更された場合、
　　　シート２に自動的に貼り付けるようにしています。

■サンプルコード
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
With Sheets("sheet2")
.Columns("A:H").ClearContents
Sheets("sheet1").Columns("A:H").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("sheet1").Range("J1:J2"), CopyToRange:=.Range("A1"), Unique:=False
End With
End Sub

お礼

ありがとうございます。

VBAは少し本を読んだ程度でまだあまり知識がないのですが、こちらの方法も試してみます。
都度マクロを実行するのではなくて、E列に入力すると自動で貼り付けられるんですね。
フィルタオプションを利用したマクロでは、マクロを実行しなければいけないことと、データがそのたびに上書きされてしまうことから使えないと思ったのですが、このような方法もあるのですね。
勉強になりました。