[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『別シートのデータを抽出』（いわっちゃん）
＜発注書＞ A B C D
1 部署 時間 メニュー 個数
2 A課 9：00 朝食 2
3 B課 10：00 朝食 2
4 D課 11：00 昼食 2
＜納品書＞
A B C D
1 日付
2 A課 2
3 B課 2
上記の発注書にA社の各部署に届ける時間とメニュー、個数を入力するシートが １日〜３１日まであります。
それを別ブックの納品書A1に日付を入力すると、該当の日付を発注書から参照し A課、B課の個数を抽出したいです。 よろしくお願いします。
WindowsXP、Excel2003
サンプルデータをもう少したくさん 載せておかれると良いかもしれません。
非常に単純に B2セルはA1セルの日付のシートの D2セルの値を参照すればよい と考えるなら、INDIRECT関数が 使用できるのではないかと思います。
(HANA)
HANAさま
非常に簡単なことで困ってしまっています・・・＞＜ 発注書には部署、時間、メニューは入力されています。 個数のみが毎日変動するので、毎日入力します。
納品書はA課、B課のみで該当の日付を発注書から抽出するということです。
じゃあ、それぞれの日の発注書シートの A:C列のデータは固定なんですね？
それぞれのシート名はどの様につけられていますか？ 1,2,3 と日付を表す数字のみ？ それとも、1日,2日,3日 の様に？ 或いは 1日の発注書,2日の発注書,3日の発注書 といった感じで？
また、納品書シートのA1セルにはどの様に日付を入力しますか？ 1,2,3 の様に日付の数字だけ？ 或いは、4/1 , 4/2 の様に？
納品書シートのA1セルに「1」と入っている場合
=INDIRECT("'"&A1&"日の発注書'!D2")
↑の式で
A1&"日の発注書" ＝ 1日の発注書 シートのD2セルの値を参照します。
(HANA)
HANAさま
早速のお返事ありがとうございます。
A:C列は固定ではなく、A課があったり、日によってはなかったりします。
それぞれのシート名は 1,2,3,4・・・となっており 納品書のA1セルには3/19と日付を入力します。
なお、納品書は別ブックで作成しております。 参照ができませんでした・・・。
いわっちゃん
＞なお、納品書は別ブックで作成しております。 INDIRECT関数を使う場合別ブックを開いておかないといけませんね。 それから、シート名だけでなくブック名も指定が必要です。
3/19 と入力するなら DAY(A1) で日付を取り出して下さい。
31×2 件なら 自ブックの空いたスペース（作業用シートを追加とかでも）に参照しておいて そこから抽出作業を行っても良い様に思います。 [A] [B] [C] [1] 日付 A課 B課 [2] 1 [3] 2 [4] 3 こんな感じで参照しておけば INDIRECT関数を使わなくても良くなりますし。
B2〜のセルに入れる数式も 簡単に作る方法はいくつか有ると思います。
また、発注書ブックのデータが一つのシートにまとまっていれば （まとめ方も考慮が必要ですが。） 他にも方法はありそうです。
(HANA)
HANAさま
納品書ブックにHANAさまがご提案された、参照するシートを作成しましたが、 B2やC2へのセルは＝で参照するようにするのでしょうか？
いわっちゃん
最終的には「＝」で参照する事に成ります。 でも、一つずつ数式を埋めていくのは面倒なので 一寸楽をしようと思います。
さて、実際もA課とB課の2箇所なのでしょうか？ それとも、実際はもう少し多いのでしょうか？
データを参照するときに １．日付を行に並べて、部署を列に並べる（上で載せた形式） ２．部署を行に並べて、日付を列に並べる と言う二つの形式が考えられます。
どちらが良いかは Ａ．納品書シートに参照させる際に元データがどの様に並んでいるのが簡単か Ｂ．参照してある表を見たときに、どちらが見やすいか 等を考えて決めるのが良いと思います。
２の形にして於いた方が、納品書シートに参照しやすいのではないかと思いますし それよりも、日付が縦に並んでいる１の形の方がぱっと見て分かりやすいかもしれません。
参照してある表を作成する手順を載せておきます。 １の形で参照する事を想定して手順を書きますが ２の形の方が良い場合は、適宜読み替えてください。
参照してある表を作るシートのシート名は「参照」シート とします。 発注書データが有るブック名を「発注書.xls」とします。 それぞれのシート名は 1,2,3,4・・・ ですね。
１．参照シートの、A2セルから下方向に 1,2,3,4・・・ と値を並べます。 シート名と一致させて下さい。
２．B2セルには一旦、発注書ブックの1シートのD2セルを「=」で参照してください。
B2セルには「='[発注書.xls]1'!$D$2」の数式が入ります。
~~~~~~~~~~~~~~ ~~~~~~
B列内で考えた時、↑の下線を引いた部分が固定に成ります。
B3セルには「='[発注書.xls]2'!$D$2」と成った数式が入れば良いですね。
３．B2セルの数式を「="★'[発注書.xls]"&A2&"'!$D$2"」に変更します。
２の式の固定部分を""で囲い、変化する部分を &A2& の様に
セル参照にしました。
また、先頭に★マークを付けておきます。
B2セルには ★'[発注書.xls]1'!$D$2 と表示され、下にドラッグすると
B3セルには ★'[発注書.xls]2'!$D$2 と表示されますね？
もしも「★」が「=」に成ったら、実際に作りたい数式と同じに成ります。
４．C2セルには D2セルではなく、D3セルを参照したいので
「="★'[発注書.xls]"&A2&"'!$D$3"」の式になります。
５．31行分フィルドラッグ。
６．B2:C32 をコピーして、同じ位置で 右クリック→形式を選択して貼り付け(S) ●値(V) で貼り付け。
７．Ctrl + H（置換ダイアログ）で、★→= に置換
以上で、発注書シートのそれぞれのシートのそれぞれのセルの値を 参照する式が出来ると思います。
まずは、やってみてもらうのと ＞＞実際もA課とB課の2箇所なのでしょうか？ ＞＞それとも、実際はもう少し多いのでしょうか？ を教えていただければと思います。
(HANA)
HANAさま
やってみましたが、「6」まではできたのですが ★→=に置き換えをしてもセルには ='[発注書.xlsx]1'!$D$2 としか表示されません。
＞＞実際もA課とB課の2箇所なのでしょうか？ ＞＞それとも、実際はもう少し多いのでしょうか？ A課、B課以外にも複数の課が存在します。
いわっちゃん
数式バーにも ='[発注書.xlsx]1'!$D$2 と表示されていますか？
また、セルの書式設定の表示形式はどうなっていますか？
(HANA)
HANAさま
数式バーの表示は ="='[発注書.xlsx]"&A2&"'!$D$2" となっております。
いわっちゃん
でしたら、６番の作業が完了する前に ７番の作業をやっていると思います。
もう一度「=」を「★」に置換して 数式バーに ="★'[発注書.xls]"&A2&"'!$D$2" セルに ★'[発注書.xls]1'!$D$2 と表示される様にしてください。
数式の範囲をコピーして、貼り付ける際に 形式を選択して貼り付け から 値貼り付けをすると 数式バーに ★'[発注書.xls]1'!$D$2 セルに ★'[発注書.xls]1'!$D$2 数式バーの表示と、セルの表示が同じになります。
それを確認して、「★」を「=」に置換してください。
(HANA)
HANAさま
出来ました！ これで日付の個数の一覧が参照できましたが、各日付納品書へは「＝」で参照すればよいですか？
出来ましたか、良かったです。
ただ ＞ A課、B課以外にも複数の課が存在します。 と言う事ですので、お手数ですが 列方向に日付、行方向に課を持ってきて 一覧にしてもらうのが良いと思います。
＞各日付納品書へは「＝」で参照すればよいですか？ 各日付納品書。。。？ 納品書は１つで、A1セルの日付によって B2,B3・・・セルの値が変われば良いのですよね？
納品書シートには、INDEX関数で参照してください。 B2セルに =INDEX(参照!B2:AF2,DAY($A$1)) として、課の数分下にフィルドラッグしてください。
(HANA)
HANAさま
おはようございます。 ようやくできました。ありがとうございます。
>納品書は１つで、A1セルの日付によって こちらですが、A課、B課以外にもほかの課が存在しております。 毎月の発注数一覧を相手先より頂いておりまして（発注数量は除く） その一覧表を元に、1日 A課とB課、2日 B課とC課、3日 A課とB課、4日 B課とC課 取引数量の大まかな予約数を把握しております。
それに合わせて、納品書は 1日と3日は同じ課に配達なので、A課とB課を表記した納品書フォームを作成。 2日と4日は同じ課に配達なので、B課とC課を表記した納品書フォームを作成。
ここでここまで出来てしまうので、 A B C D 1 日付 2 3 4
日付のみ入力すると、弁当の数だけではなく各課も反映されるようには 出来ないものかと考えてしまいました・・・。
最初から、A課 B課 C課 D課入力しておいて、「0」でいいじゃないか？ とはいかないのが問題でして・・・。
せっかく、丁寧親切に教えていただいたにも拘らず何度も申し訳ありません。 よろしくお願いします。
いわっちゃん
そもそも ＞＞非常に単純に ＞＞ B2セルはA1セルの日付のシートの ＞＞ D2セルの値を参照すればよい ＞＞と考えるなら と言う確認の元に話が進んでいます。
＞日付のみ入力すると、弁当の数だけではなく各課も反映されるようには でしたら、前提が違ってきますよね？
B2セルは、D2セルの値だったりD3セルの値だったりするのですから。 せめて「A列の課に対応する数」と言わないと。
今、行方向に課,列方向に日付が入った一覧表がありますね？ ピボットテーブルを使用されてはどうですか？
A1セルでは日付を入力するのではなく 1,2,3・・・と項目を入れ替えることに成りますが。
でも、これなら 日付を変更してオートフィルタで 0以外を表示させる事にした方が簡単かもしれませんね。
関数でやるなら、課を抜き出す数式が出来れば B列の方はSUMPRODUCT関数や、VLOOKUP関数等が使えると思います。
(HANA)
あれ？もしかして ＞ A:C列は固定ではなく、A課があったり、日によってはなかったりします。 これの事だったのですかね。。。 納品書は、A:B列しか言及されていなかったので 他のシートの印象を受けてスルーしちゃいましたが。
＞発注書には部署、時間、メニューは入力されています。 ＞納品書はA課、B課のみで該当の日付を発注書から抽出するということです。 って事だったので、てっきり両方固定なのかと。。。
失礼しました。
まず、納品書のA列に必要な課のデータのみ表示出来たとして 個数を参照するには =IF(A2="","",VLOOKUP(A2,参照!A2:AF2,DAY($A$1)+1,FALSE)) こんな式で参照出来居ますか？
参照シートは、行方向に課名、列方向に日付が並んでいる場合です。
(HANA)
HANAさま
おはようございます。 個数は上記の式で参照することはできました。
いわっちゃん
でしたら、参照シートのA列を挿入して下さい。 A2セルに =IF(INDEX(C2:AG2,DAY(納品書!$A$1))=0,"",ROW(A2)) の式を入れて下にフィルドラッグ。
納品書シートの方は A2セルに =IF(COUNT(参照!$A$2:$A$10)<ROW(A1),"",VLOOKUP(SMALL(参照!$A$2:$A$10,ROW(A1)),参照!$A$2:$B$10,2,FALSE)) B2セルに =IF(A2="","",VLOOKUP(A2,参照!$B$2:$AG$10,DAY($A$1)+1,FALSE)) の式を入れてフィルドラッグです。
納品書シートの式は、10件分を想定した式に成っています。 実際のデータ量に合わせて変更して下さい。
上手く行くと良いですが。。。
(HANA)
HANAさま
なんだか良く分からなくなってしまいました・・・。
まず発注書からの参照シートへの抽出は ='[発注書.xls]1'!$D$2 で参照する形でよいのですよね？
>でしたら、参照シートのA列を挿入して下さい。 A2セルに =IF(INDEX(C2:AG2,DAY(納品書!$A$1))=0,"",ROW(A2)) の式を入れて下にフィルドラッグ。 これは納品書の A2セルに入れるのでしょうか?
いわっちゃん
むむ。。。スミマセン。
えっと ＞まず発注書からの参照シートへの抽出は ＞='[発注書.xls]1'!$D$2 で参照する形でよいのですよね？ はい。そうして下さい。
で、今 参照シートは（納品書ブックにある） [A] [B] [C] [D] [1] 1 2 3 [2] A課 5 [3] B課 3 1 こんな感じに成ってますよね？
B1〜AF1 に 1,2,3・・・ と日付を入力 A2〜A○ に 課の名前を入力 B2:AF○ に 「='[発注書.xls]1'!$D$2」等の式
で、A列を挿入して
A2セルに =IF(INDEX(C2:AG2,DAY(納品書!$A$1))=0,"",ROW(A2)) の式を入れます。
[A] [B] [C] [D] [E]
[1] 1 2 3
[2] 2 A課 5
[3] 3 B課 3 1
↑ここに列を挿入して、数式をセット。
B列のデータ数分フィルドラッグです。
納品書ブックの参照シートの事です。
納品書ブックの納品書シートには [A] [B] [1] 3月1日 [2] A課 5 [3] B課 3 A2セルに =IF(COUNT(参照!$A$2:$A$10)<ROW(A1),"",VLOOKUP(SMALL(参照!$A$2:$A$10,ROW(A1)),参照!$A$2:$B$10,2,FALSE)) B2セルに =IF(A2="","",VLOOKUP(A2,参照!$B$2:$AG$10,DAY($A$1)+1,FALSE)) の式を入れて、フィルドラッグです。
＞A課、B課以外にもほかの課が存在しております。 と言う事ですが、何件有りますか？
例えば、20件有る場合は、参照シートの B2:B21の間に課の名前が入力されていますね？ すると、参照シートのA列に入れた式も 21行目までフィルドラッグ。 納品書シートのA,B列に入れた式も 21行目までフィルドラッグ。。。。するのですが、 参照範囲が現在、10行目までに成っているので、これは21行目までに変更してから フィルドラッグして貰うことになります。
。。。どうでしょう？
(HANA)
HANAさま
参照シートには、式を入れた結果反映しております。
納品書に該当の式を入力しても #REF! がでてしまいます。
あと、参照シートは発注書の D2 D3 を参照しているため A課だったり、B課だったりという識別は出来ないんではないでしょうか？
HANAさまが、記入してくださったものにはA課の2日、3日 B課の2日がなかったりするので・・・。
[A] [B] [C] [D] [E] [1] 1 2 3 [2] 2 A課 5 [3] 3 B課 3 1
いわっちゃん
えっと ＞あと、参照シートは発注書の D2 D3 を参照しているため ＞A課だったり、B課だったりという識別は出来ないんではないでしょうか？ この部分がちょっとよく分かりません。
単純に「=」で参照していたら、D2,D3,E2セルは「0」に成りますね。。。 式の方は「0」で判定しているのですが、サンプルデータの方に 「0」を入れるのを忘れていました。すみません。
問題点を明確にするために 新しいシートに以下と同じ表を作成し 所定の場所に数式を入れて どの様に成るか教えて下さい。
作成するデータ。ただし、A2:A3,A8:A9,B8:B9（☆,◇,△のセル） は数式を入れます。 B1:E3 と、A7 に以下と同じ様にデータを入れて下さい。 [A] [B] [C] [D] [E] [1] 1 2 3 [2] ☆ A課 5 0 0 [3] ↓☆ B課 3 0 1 [4] [5] [6] [7] 1 [8] ◇ △ [9] ↓◇ ↓△
それぞれ入れる数式は ☆のセル（A2） =IF(INDEX(C2:E2,$A$7)=0,"",ROW(A2)) ◇のセル(A8) =IF(COUNT($A$2:$A$3)<ROW(A1),"",VLOOKUP(SMALL($A$2:$A$3,ROW(A1)),$A$2:$B$3,2,FALSE)) △のセル(B8) =IF(A8="","",VLOOKUP(A8,$B$2:$E$3,$A$7+1,FALSE))
また、A7の値を入れ替えた時の A7:B9の結果図 [A] [B] [A] [B] [A] [B] ： ： ： [7] 1 [7] 2 [7] 3 [8] A課 5 [8] [8] B課 1 [9] B課 3 [9] [9]
A7セルの値を1〜3に変更すると、上記の様にA8:B9の状態が変化しますか？
(HANA)
HANAさま
お返事遅くなりました。 新しいシートに作成したところ、A7セルの値は上記通り変化しました。
いわっちゃん
でしたら、少しずつ実際のデータに合わせて行きたいと思います。 各ステップの変更をしたら、その都度希望する結果が得られるか セルの値を変更して確認して下さい。
希望した結果が得られなく成った場合は、その時の数式と一緒に セルに何が返されるのか教えて下さい。
【Step１】 実際は、A7セルの位置には月日を入れると思います。 入力された日付から、日の部分を取り出すには DAY関数を使います。
☆と△の数式の中に $A$7 と成っている部分が有りますが 該当部分を DAY($A$7) に変更して下さい。
【Step2】 A1:E3の表が有るシートは「参照」シートなので 現在のシート名を「参照」に変更して下さい。 また、もう一つのシートは「納品書」シートなので その名前のシートを作成して下さい。
A7:B9セルを切り取って、納品書シートのA1セルから貼り付けてください。
【Step3】 納品書シートのセルの配置を実際の配置に揃えてください。 その際、行の挿入や、切り取り・貼り付け、ドラッグ＆ドロップ 等で 移動させて下さい。
【Step4】 納品書シートの◇のセルの数式には ROW関数が使ってありますが その参照先が 参照シートに成っていると思います。 「参照!」を消して、自分のシートのセルを参照させて下さい。
その際、セル番地は変更しないで下さい。 「参照!A1」 と成っていると思いますので、「A1」に変更です。
【Step5】 参照シートは、現在データがE列まで作ってあり 各数式は、E列までの範囲を参照しています。 C列から1日が始まると、31日はAG列に成りますので E列を選択して、AG列に移動するまで列を挿入してください。 E1:E3セルを選択して、AG1:AG3にドラッグ＆ドロップしてもらっても良いです。 C1:AG1に連番を振りなおして下さい。
これで、実際の状態に近くなったと思います。 まだ違う所が有る場合は、何処が違うのか教えて下さい。
なお、途中のStepで希望する結果が得られなくなった場合 その先に進んでも、間違った結果になります。 上でも書きましたが どのステップ中に上手く行かなくなったのか。 その時の数式と、セルの値。 を教えて下さい。
(HANA)
HANAさま
うまく出来ました。 が、A課が0だった場合は表示そのものも反映されないようですね・・・。
>最初から、A課 B課 C課 D課入力しておいて、「0」でいいじゃないか？ とはいかないのが問題でして・・・。
>毎月の発注数一覧を相手先より頂いておりまして（発注数量は除く） こちらに実際の配達数が「0」であっても表記されてきており 配達がなくても、実績として「0」であったという証明で表記しなくてはならないのです・・。
毎度、後手に回ってしまい申し訳ありません。
いわっちゃん
最初に ＞＞サンプルデータをもう少したくさん ＞＞載せておかれると良いかもしれません。 と書きましたが、この場合は
・・・・・
注文書.xls シート名は 1,2,3・・・と成っており、日付を意味する。日数分(31枚)有る。 A:Cは、全てのシートで共通。D列の個数が日に依って変わる。 1 [A] [B] [C] [D] 2 [A] [B] [C] [D] 3 [A] [B] [C] [D] [1] 部署 時間 メニュー 個数 [1] 部署 時間 メニュー 個数 [1] 部署 時間 メニュー 個数 [2] A課 9:00 朝食 1 [2] A課 9:00 朝食 [2] A課 9:00 朝食 [3] B課 10:00 朝食 2 [3] B課 10:00 朝食 3 [3] B課 10:00 朝食 0 [4] D課 11:00 昼食 5 [4] D課 11:00 昼食 2 [4] D課 11:00 昼食 4
納品書.xls シート名 納品書・・・一つのシートをA1セルの日付を変更しながら使用する [A] [B] [A] [B] [A] [B] [1] 4月1日 [1] 4月2日 [1] 4月3日 [2] 部署 個数 [2] 部署 個数 [2] 部署 個数 [3] A課 1 [3] B課 3 [3] B課 0 [4] B課 2 [4] [4] [5] [5] [5]
A1セルの日付に対応するA課,B課のD列の個数が 空白に成っていない物を、A3セルから抽出
・・・・・
の様に書いておいて頂けると良かったと思いますよ。 もしも文章での説明に不足が有った場合でも こちらで推測出来る部分も増えると思いますし どう言った事に成れば良いのかイメージも伝わりやすいと思います。
＞納品書に該当の式を入力しても #REF! がでてしまいます。 と言う事なので、御提示のレイアウトや今まで話をしてきたレイアウトと 実際のレイアウトは違うのではないかと思います。
今回上手く行った数式と、前回上手く行かなかった数式を見比べて 式の仕組みを理解して頂ければと思います。
＞「0」でいいじゃないか？ ＞とはいかない ＝「0」の場合は表示しない と言う事かと思いました。
一応、セルの値を参照しているので 少しの変更で済みそうです。
まず、参照表に現在、単純に
='[発注書.xls]1'!$D$2 の式で参照していると思いますが
これを
=IF('[発注書.xls]1'!$D$2="","",'[発注書.xls]1'!$D$2)
の式で参照する様にして下さい。
すると、元のデータのセルに入力が無い場合は「""」が返され それ以外はそのセルの値が参照される様に成ります。
それから☆のセル（A2）の式が、現在「0」かどうかを確認していますが =IF(INDEX(C2:E2,$A$7)=0,"",ROW(A2)) これを =IF(INDEX(C2:E2,$A$7)="","",ROW(A2)) 「""」かどうかを確認するようにしてみて下さい。
(HANA)
HANAさま
遅くなりました。
>まず、参照表に現在、単純に
='[発注書.xls]1'!$D$2 の式で参照していると思いますが
これを
=IF('[発注書.xls]1'!$D$2="","",'[発注書.xls]1'!$D$2)
の式で参照する様にして下さい。
="★IF('[発注書.xls]"&A2&"'!$D$2="","",'[発注書.xls]"&A2&"'!$D$2)"
フィルドラッグ → コピー → 値で貼り付け → 変換※ここで変換出来なくなってしまっています。
上記の式がまちがっているのでしょうか？
いわっちゃん
どうでしょうね？
別のセルに一度、セルを参照しながら =IF(発注書ブックの1日のD2セル（絶対参照で）="","",発注書ブックの1日のD2セル（絶対参照で）) の正解の式を作って下さい。
きちんと参照される式が出来たら、最初の = を★に直して下さい。
また、数式で作った数式を、値貼り付けしたセルと「=」で結んで 同じになっているか（TRUEとなるかどうか）確認して下さい。
数式で作った式の文字列の★以降が 実際に参照出来る数式の = 以降と一致していないと ★→= に変換したときに、参照出来ないのは不思議は無いと思います。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.