エクセル関数で、リストのグループ別分割と集計

質問

行いたいことは、添付データの通り、ｓｈｅｅｔ１に元リストがあります。それをグループ別にｓｈｅｅｔ２～４に分割します。分割されたリストは注文番号が一致した場合、数量おうなを合計し、一行に集約します。但し、注文番号が一致しても、拡販対象は行を分けます。ｓｈｅｅｔ１のリストを更新すれば、前述の処理を自動で行い、分割したリストも更新できる様、ｓｈｅｅｔ２～４の各セルに設定する関数をご教授ください。マクロでできるのでしょうが、後でマクロの知識がない人も修正ができるよう、あえて関数で作成したいと思います。色々試みましたが私の力不足です。ご教授いただきますようお願いします。

回答No.1

tom04 · ベストアンサー

こんにちは！
関数で！がご希望だというコトなので・・・
一例です。
↓の画像で説明すると左側が「元リスト」Sheetで右側がSheet2にしています。

まず「元リスト」Sheetに作業用の列を設けます。
作業列G3セルに
=C3&E3
という数式を入れオートフィルでずぃ～～～！っと下へ、これでもか！というくらいコピーしておきます。

次にSheet2以降の操作は全て同じですので、Sheet2を開き　→　Shiftキーを押しながら最後のSheet見出しをクリック！
これでSheet2以降が作業グループ化されましたので全てのSheetに同じ数式が入ります。
Sheet2にも作業列を設けA3セルに
=IF(AND(元リスト!D3=$B$1,COUNTIF(元リスト!$G$3:元リスト!G3,元リスト!G3)=1),ROW(),"")
という数式を入れオートフィルでこれもずぃ～～～！っと下へコピー！

B3セルに
=IF(COUNT($A:$A)<ROW(A1),"",INDEX(元リスト!A:A,SMALL($A:$A,ROW(A1)))&"")
として列方向にF3セルまでオートフィルでコピー！
G列だけは数式が替わります。
G3セルに
=IF(B3="","",SUMIF(元リスト!G:G,D3&F3,元リスト!F:F))

最後にB3～G3セルを範囲指定　→　G3セルのフィルハンドルで下へずぃ～～～！っとコピー！

これで各SheetのB2セルに
グループ名を入力すると画像のような感じになります。

※　作業列が目障りであれば非表示にしておきます。

質問にあるようにVBAの方が簡単だと思いますが、関数で！ということですので
こんな感じではどうでしょうか？m(__)m

お礼

tom04さん、ご回答有り難うございます。早速、私が添付していた質問用のファイル（データ）で検証をさせていただきました。その結果、元リストの７行目と１０行目の内容は、グループが違う（７行目→３Ｇｒ、１０行目→２Ｇｒ）だけで、客先名・注文番号は同じですが、この場合Ｇｒが違うので、７行目はSheet4へ、１０行目はSheet3へリストアップされるべきですが、Sheet4のリスト（３Ｇｒ）で数量が合計され、Sheet3のリスト（２Ｇｒ）にはリストアップされない状況でした。いずれにしろ概ね希望する動きでしたので、入力されている関数の意味を理解し、改善策を考えてみたいと思います。本当に有り難うございました。

回答No.3

KURUMITO

関数式が複雑になりそうなので作業列を作って対応します。
お示しの表がシート1のＡ列からＦ列目出で2行目の項目名が3行目から下方にデータが入力されているとします。
Ｇ３セルには次の式を入力して下方にドラッグコピーします。

=D3&C3&E3

Ｈ1セルからＪ1セルにはＤ列で使われているのと同じグループ名を入力します。Ｈ1セルには1Ｇｒ，Ｉ1セルには2Ｇｒ，Ｊ1セルには3Ｇrのように入力します。
Ｈ3セルには次の式を入力してＪ3セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(AND($D3=H$1,COUNTIF($G$3:$G3,$G3)=1),MAX(H$2:H2)+1,"")

そこでお求めのグループごとの表ですがシート2からシート4に表示させることにして、初めにＣｔｒｌキーを押しながらこれら3つのシート名をシート見出しでシート2からクリックしていきます。それによって3つのシートは同じ作業グループが形成されます。
そこでシート2のＡ1セルにはグループ名を入力するためのセルとして１Ｇｒと入力します。
Ａ2セルからＦ2セルにかけてはシート1と同じ項目名を入力します。
Ａ3セルには次の式を入力したのちにＦ3セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF($A$1="","",IF(ROW(A1)>MAX(INDEX(Sheet1!$H$1:$J$10000,3,MATCH($A$1,Sheet1!$H$1:$J$1,0)):INDEX(Sheet1!$H$1:$J$10000,10000,MATCH($A$1,Sheet1!$H$1:$J$1,0))),"",IF(COLUMN(A1)<6,INDEX(Sheet1!$A:$F,MATCH(ROW(A1),INDEX(Sheet1!$H:$J,1,MATCH($A$1,Sheet1!$H$1:$J$1,0)):INDEX(Sheet1!$H:$J,10000,MATCH($A$1,Sheet1!$H$1:$J$1,0)),0),COLUMN(A1)),IF(COLUMN(A1)=6,SUMIF(Sheet1!$G:$G,IF($E3=0,$D3&$C3,$D3&$C3&$E3),Sheet1!$F:$F),""))))

これで1Ｇｒのデータが表示されますね。
その後はシート1を選択することで作業グループを解除します。
シート3のＡ1セルに3Ｇｒと入力することで関連のデータが表示されますね。シート4でもＡ1セルに3Ｇｒと入力すればよいでしょう。

なお、シート2からシート4のＥ列ですがデータがない場合には0が表示されています。この0の表示を無くすためにはＥ列を選択したのちに右クリックして「セルの書式設定」から「表示形式」で「ユーザー定義」を選び　;; を入力すればよいでしょう。

お礼

KURUMITOさん回答ありがとうございます。
早速検証させていただきます。ただ、折角回答をいただき申し訳ないのですが、最初にtom04さんが回答してくださった内容を検証し、本ちゃんのリストに適用できるようになりましたので、ベストアンサーはtom04さんにたいと思います。
KURUMITOさんの回答も検証させていただき、スキルアップに利用させていただきます。本当にありがとうございました。

回答No.2

tom04

No.1です！

お礼欄の
＞Sheet4のリスト（３Ｇｒ）で数量が合計され、Sheet3のリスト（２Ｇｒ）にはリストアップされない状況でした。

に関してですが・・・

各SheetのB1セルにはそのSheetに抽出したい「グループ名」が入力されているでしょうか？
各Sheetとも、B1セルデータを参照してそのSheetに表示するようにしていますので
前回の数式は他の間違いはあっても、「元リスト」のD列（グループ）だけには
ちゃんと振り分けられ、他のグループがそのSheetに表示されることはないと思います。

他の原因だったらごめんなさいね。m(_ _)m

お礼

昨日、tom04さんが回答してくださった関数を、意味を理解するべく、教本で調べました。おかげさまで、本ちゃんのリストに使用することができます。又、今まで使ったことのない関数も理解することができました。
本当に助かりました。感謝です。

補足

tom04さん
その後私の添付した質問用ファイルで検証した結果です。
シート１（元リスト）の作業列に　　=C3&E3　を =C3&E3&D3　としました。
こうすることで、元リストの注文番号と拡販対象は一致するが、Ｇｒが違う際に、元リストの下方にある案件は、シート２以降（Ｇｒ別リスト）の作業列関数
　　=IF(AND(元リスト!D3=$B$1,COUNTIF(元リスト!$G$3:元リスト!G3,元リスト!G3)=1),ROW(),"")　　のCOUNTIFでの戻り値が2になり、結果ANDの戻り値が0になることにより行番号がブランクになる事を修正。
さらに、シート２以降（Ｇｒ別リスト）の数量を戻り値とするセルに入力されている関数
　　=IF(B3="","",SUMIF(元リスト!G:G,D3&F3,元リスト!F:F))
の中の　D3&F3　を　D3&F3&E3　に修正することでＯＫとなりました。
ただし、シート２以降の、Ｂ３からＦ３までオートフィルする関数の中の最後の部分後の　＆””　がよく理解できませんでした。