こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

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

同一ブック内に2つのシートがあります。
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の同日付の横欄に業者と支払額が抽出されるように関数を定義したいのです。

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

投稿日時 - 2007-10-31 15:51:04

QNo.3477555

困ってます

このQ&Aは役に立ちましたか?

3人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.6

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

投稿日時 - 2007-11-14 16:54:39

お礼

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

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

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

投稿日時 - 2007-11-26 10:19:53

ANo.5

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

投稿日時 - 2007-11-07 12:22:44

お礼

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

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

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

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

投稿日時 - 2007-11-14 16:01:15

ANo.4

Excel2003で説明します。

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

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

投稿日時 - 2007-11-05 13:31:43

お礼

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

投稿日時 - 2007-11-07 11:46:31

こっちが若干スマートかな?
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するのがスマートなんですけど・・・

投稿日時 - 2007-11-01 11:51:55

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

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

ここで、
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/20aaa2000
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))
ととして、各行にコピーする。

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

投稿日時 - 2007-11-01 09:12:20

ANo.1

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

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

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

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

投稿日時 - 2007-10-31 18:28:17

お礼

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

投稿日時 - 2007-11-05 10:24:04

あなたにオススメの質問