エクセル 離れたデータを抽出して集計

質問

関数で抽出・集計をしたいのですが、式の意味や組み方なども教えていただきたいです。
また、ピボットテーブルでもできないか考えています。

添付画像が見にくいのですが

A列からU列まで
オーダーNO　
商品　
オーダー数　
単価　
合計　
在庫　
出荷１　
出荷数　
書類　
出荷２　
出荷数　
書類
と出荷５～書類まで続きます。

求めたい集計表は、出荷日を軸にして、
出荷日を入力すると、自動的に
オーダーNO
それぞれのオーダーNOに対して、商品・出荷数・単価・合計
を一覧表で作成したいです。

先日、同じような質問をしたのですが、列数が変わるととたんにできなくなってしまい、関数の意味から理解して応用ができるようにしたいです。
よろしくお願いします。

回答No.2

KURUMITO · ベストアンサー

回答Ｎｏ１です。遅れましてごめんなさい。
関数が複雑になりますので作業列を使って対応することにします。
シート1は元の表でＡ1セルからＵ1セルまでの項目名が有り2行目から下方に入力されているとします。そこで作業列としてＶ2セルには次の式を入力して下方にドラッグコピーします。

=IF(OR(A2="",COUNTIF(G2:S2,Sheet2!A$4)=0),"",A2)

ここでシート2のＡ4セルには抽出表示したい出荷日が入力されるとしています。
シート1のＷ2セルには次の式を入力して下方にドラッグコピーします。

=IF(V2="","",IF(COUNTIF(V$2:V2,V2)=1,ROUNDDOWN(MAX(W$1:W1),-3)+1000,INDEX(W$1:W1,MATCH(V2,V$1:V1,0))+COUNTIF(V$1:V1,V2)))

Ｘ2セルには次の式を入力して下方にドラッグコピーします。

=IF(W2="","",MATCH(Sheet2!A$4,A2:S2,0))

以上でシート1での作業は終わって指定した出荷日でのデータをシート2に表示させる作業に移ります。
シート2の2行目にはオーダーＮｏを表示させるためにＢ2セルには次の式を入力して右横方向にドラッグコピーします。

=IF(ROUNDUP(COLUMN(A1)/4,0)>MAX(Sheet1!$W:$W)/1000,"",IF(MOD(COLUMN(A1),4)=1,INDEX(Sheet1!$V:$V,MATCH(ROUNDUP(COLUMN(A1)/4,0)*1000,Sheet1!$W:$W,0)),""))

Ａ3セルには出荷日と文字を入力します。Ｂ3セルから右横方向には項目名を表示させるためにＢ3セルには次の式を入力して右横方向にドラッグコピーします。

=IF(ROUNDUP(COLUMN(A1)/4,0)>MAX(Sheet1!$W:$W)/1000,"",CHOOSE(MOD((COLUMN(A1)-1),4)+1,"商品","出荷数","単価","合計"))

お求めのデータを表示させるためにＡ4セルには出荷日を入力します。
Ｂ4セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IFERROR(IF(MOD(COLUMN(A1),4)<>0,INDEX(Sheet1!$A:$T,MATCH(ROUNDUP(COLUMN(A1)/4,0)*1000+ROW(A1)-1,Sheet1!$W:$W,0),IF(MOD(COLUMN(A1),4)=1,2,IF(MOD(COLUMN(A1),4)=2,INDEX(Sheet1!$X:$X,MATCH(ROUNDUP(COLUMN(A1)/4,0)*1000+ROW(A1)-1,Sheet1!$W:$W,0))+1,4))),IF(MOD(COLUMN(A1),4)=0,INDEX($B4:B4,COLUMN(A1)-2)*INDEX($B4:B4,COLUMN(A1)-1),"")),"")

上の式はエクセル2010などでは有効ですが古いバージョンのエクセルでは次の式を入力します。

=IF(ISERROR(IF(MOD(COLUMN(A1),4)<>0,INDEX(Sheet1!$A:$T,MATCH(ROUNDUP(COLUMN(A1)/4,0)*1000+ROW(A1)-1,Sheet1!$W:$W,0),IF(MOD(COLUMN(A1),4)=1,2,IF(MOD(COLUMN(A1),4)=2,INDEX(Sheet1!$X:$X,MATCH(ROUNDUP(COLUMN(A1)/4,0)*1000+ROW(A1)-1,Sheet1!$W:$W,0))+1,4))),IF(MOD(COLUMN(A1),4)=0,INDEX($B4:B4,COLUMN(A1)-2)*INDEX($B4:B4,COLUMN(A1)-1),""))),"",IF(MOD(COLUMN(A1),4)<>0,INDEX(Sheet1!$A:$T,MATCH(ROUNDUP(COLUMN(A1)/4,0)*1000+ROW(A1)-1,Sheet1!$W:$W,0),IF(MOD(COLUMN(A1),4)=1,2,IF(MOD(COLUMN(A1),4)=2,INDEX(Sheet1!$X:$X,MATCH(ROUNDUP(COLUMN(A1)/4,0)*1000+ROW(A1)-1,Sheet1!$W:$W,0))+1,4))),IF(MOD(COLUMN(A1),4)=0,INDEX($B4:B4,COLUMN(A1)-2)*INDEX($B4:B4,COLUMN(A1)-1),"")))

これでシート2のＡ4セルへ抽出したい日付を入力することでその日のデータをシート2に表示させることができますね。
ぜひ一度試験してみてください。

お礼

ありがとうございます！
まさに、私が欲しかった表ができあがりました。
Sheet1の列を増やしても、同じ結果を得ることができて大変うれしいです！

どうやってこういう関数を組めるようになったのか、また改めてお伺いしたいと思います。

本当にありがとうございます。

回答No.1

KURUMITO

元の表ですが出荷日が出荷1から出荷5までで重なることはあるのでしょうか？　あるとなしではかなり式が違ってきますので。

補足

検索したい出荷日3月10日が、
オーダー2の商品（あ・い・お）の出荷5
オーダー3の商品（あ～お）の出荷2
にあります。

出荷1というのは、注文に対して1回目の出荷
以下、出荷2は2回目・・・
という意味です。

説明不足で申し訳ありません。