エクセルで別シートにデータ抽出

質問

同一ブック内に２つのシートがあります。
Sheet1に列H：業者、列I：支払額、列J：支払期日・・・があります。
Sheet2（結果）にはカレンダー一覧があります。
例）Sheet1
…　　　業者　　　　支払額　　　支払期日
　　　　aaa 　1000 2007/10/5
　　　　bbb 　5000 2007/10/5
　　　　aaa 　2000 2007/10/20
　　　　ccc 　2000 2007/10/20

例）Sheet2（結果）
日付　　　　業者　　　　支払額　　　　業者　　　支払額　
2007/10/1
2007/10/2
…（略）
2007/10/5　 aaa 1000 bbb 5000
2007/10/6
…（略）
2007/10/31

Sheet1に業者、支払額、支払期日を入力すると、Sheet2の同日付の横欄に業者と支払額が抽出されるように関数を定義したいのです。

同じような質問を参考にやってみたのですが、なかなかうまくいきません。どなたかエクセル初心者の私に丁寧に説明していただけませんでしょうか？

回答No.6

borg

日付だけの行を追加（１日から３０日）のデータを後ろに追加してやりましたが、全日付データが出ますよ。
業者名の空データは列名(空白）で表示されます。

お礼

ご連絡遅くなってすみません。

全日付データは出ますが、１日から３０日まで順番に抽出したいのです。業者や支払額が空白の場合は順番が後回しになってしまうようなのです。業者や支払額の入力の有無に関係なく１日から順番に抽出する方法はありますでしょうか？

何度も素人質問に付き合っていただきありがとうございます。

回答No.5

borg

データエリアを指した後、ピボットテーブルのフィールド設定（ボタンにあります）で、データの個数となっているところを、合計にすればできますよ。

お礼

お礼が遅くなってすみません。

できました！
もうひとつ、ワガママなのですが・・・
抽出されたピボットテーブルがカレンダーのように日付を追って一覧できるようにしたいのです。

自分なりにやってみたのですが、３列とも全て入力されているものが優先で抽出されてしまい、支払期日だけが入力されていても業者や支払額が入力されていないと飛ばされてしまうのです。

これを日付優先で抽出することは可能でしょうか？

回答No.4

borg

Excel2003で説明します。

(1)データ群の１つのセルをどこでもいいので指します。
(2)ツール→ピボットテーブルとピボットグラフ・・
(3)通常そのまま次へ
(4)範囲が表示されるので確認します
(5)結果の表示を新規のワークしーとにするか既存シートのどこかにするかを指定し→完了。
(6)行フィールドに日付、列フィールドに業者、データフィールドに支払額をドラッグすれば出来上がりです。

結果をデータとしてまた再利用、編集したければ、全体を(列でもOK)コピー→値で針付けで、ピボットテーブル機能のイメージは消え値として扱えるようになります。

お礼

とても分かりやすい説明ありがとうございます。
早速やってみましたが、支払額の部分が金額が表記されるのではなく、１、２などある日付に対して業者への支払が何回かという回数が表示されているような感じです。金額が表示されるにはどうしたらいいのでしょう？

回答No.3

fumufumu_2006

こっちが若干スマートかな？
countifで日付毎のキーを作るのは同じだけど、いっそ、日付+日付毎のキーで、重複しないキーを作ります。

作業用のsheet3を用意する。
A1=IF(Sheet1!C1<>"",Sheet1!C1 & "-"& COUNTIF(Sheet1!C$1:C1,Sheet1!C1),"")
B1=IF(Sheet1!A1<>"",Sheet1!A1,"")
C1=IF(Sheet1!B1<>"",Sheet1!B1,"")
これをA-C列の各行にコピーする。

A列は、範囲をC$1:C1としたことによって、日付-自分以前の同一日付の数を組み合わせた値が出る。
2007/10/05-1 とはならず、39360-1 と言う風になるのは、書式の関係なので気にしないでください。

ここから、Sheet2を求める。
A列は日付が入っているとします。
B,C列は日付毎のキーが1、D,E列は日付毎のキーが2、F,G列は日付毎のキーが3・・・となります。
B1=IF(ISNA(VLOOKUP($A1 &"-1",Sheet3!$A:$C,2,FALSE)),"",VLOOKUP($A1 &"-1",Sheet3!$A:$C,2,FALSE))
C1=IF(ISNA(VLOOKUP($A1 &"-1",Sheet3!$A:$C,3,FALSE)),"",VLOOKUP($A1 &"-1",Sheet3!$A:$C,3,FALSE))
D1=IF(ISNA(VLOOKUP($A1 &"-2",Sheet3!$A:$C,2,FALSE)),"",VLOOKUP($A1 &"-2",Sheet3!$A:$C,2,FALSE))
E1=IF(ISNA(VLOOKUP($A1 &"-2",Sheet3!$A:$C,3,FALSE)),"",VLOOKUP($A1 &"-2",Sheet3!$A:$C,3,FALSE))
F1=IF(ISNA(VLOOKUP($A1 &"-3",Sheet3!$A:$C,2,FALSE)),"",VLOOKUP($A1 &"-3",Sheet3!$A:$C,2,FALSE))
G1=IF(ISNA(VLOOKUP($A1 &"-3",Sheet3!$A:$C,3,FALSE)),"",VLOOKUP($A1 &"-3",Sheet3!$A:$C,3,FALSE))
以降同一日付で出てくる最大まで用意する。

これを、1日から31日までにコピーする。
これではどうでしょう。

p.s.
計算式が多いので、sheet1の値を変更すると、再計算に時間がかかるようです・・・
やはりVBAするのがスマートなんですけど・・・

回答No.2

fumufumu_2006

こんなのはどうでしょう？

作業用のsheet3を用意する。
A1=IF(Sheet1!C1="","",Sheet1!C1)
B1=IF(Sheet1!C1<>"",COUNTIF(Sheet1!C$1:C1,A1),"")
これをA,B列の各行にコピーすると、範囲をA$1:A1としたことによって、自分以前の同一日付の数が出る
2007/10/05 1
2007/10/05 2
2007/10/20 1
2007/10/20 2

ここで、
C1=IF(D1<>"",$A1,"")
D1=IF($B1=1,Sheet1!$A1,"")
E1=IF($B1=1,Sheet1!$B1,"")
F1=IF(D1<>"",$A1,"")
G1=IF($B1=2,Sheet1!$A1,"")
H1=IF($B1=2,Sheet1!$B1,"")

として、各行にコピーすると、
2007/10/05 1 2007/10/05 aaa 1000
2007/10/05 2 　　　　　　　　　　2007/10/05 bbb 5000
2007/10/20 1 2007/10/20 aaa 2000
2007/10/20 2 　　　　　　　　　　2007/10/20 ccc 2000

となる。
ちなみに、C列->1 D列->1 E列->2 F列->2を出すために
D1=IF($B1=COLUMN(C1)/3,Sheet1!$A1,"")
E1=IF($B1=COLUMN(C1)/3,Sheet1!$B1,"")
として、C1:D1範囲を適当な大きさの範囲にコピーすると同様の結果を求められる。
こちらの方がコピーが楽です。

以上の結果から、sheet2を求める。
sheet3のA行が日付が入っていれば、
B1=IF(ISNA(VLOOKUP($A1,Sheet3!C:E,1,FALSE)),"",VLOOKUP($A1,Sheet3!C:E,2,FALSE))
C1=IF(ISNA(VLOOKUP($A1,Sheet3!C:E,1,FALSE)),"",VLOOKUP($A1,Sheet3!C:E,3,FALSE))
D1=IF(ISNA(VLOOKUP($A1,Sheet3!F:H,1,FALSE)),"",VLOOKUP($A1,Sheet3!F:H,2,FALSE))
E1=IF(ISNA(VLOOKUP($A1,Sheet3!F:H,1,FALSE)),"",VLOOKUP($A1,Sheet3!F:H,3,FALSE))
ととして、各行にコピーする。

と、求める結果が出ませんか?

回答No.1

borg

この場合、Keyとなる項目が支払期日と業者になります。
複数のキーと複数の答えをこの形式で出すには、セルの参照、関数の引用だけでは非常に厳しいものがあります。
Vlook関数、If関数、Iserror関数などを途中ワークエリアを使いまくればできるかもしれませんが、非現実的です。
またVBAを使えば話は別ですが、開発の知識が必要です。

ちょっと結果の形式が変わりますが、ピポットテーブルを使うと最終的に求めたい結果はでます。

日付　　　 aaaa bbbb cccc
・・・
20007/10/5 1000 5000
・・・
2007/10/20 2000 2000

こんなイメージとなります。

お礼

早速の回答ありがとうございます。
ピボットテーブルの知識もないものですから・・・
詳しくご説明していただけると助かります。