行いたいことは、添付データの通り、sheet1に元リストがあります。それをグループ別にsheet2~4に分割します。分割されたリストは注文番号が一致した場合、数量おうなを合計し、一行に集約します。但し、注文番号が一致しても、拡販対象は行を分けます。sheet1のリストを更新すれば、前述の処理を自動で行い、分割したリストも更新できる様、sheet2~4の各セルに設定する関数をご教授ください。マクロでできるのでしょうが、後でマクロの知識がない人も修正ができるよう、あえて関数で作成したいと思います。色々試みましたが私の力不足です。ご教授いただきますようお願いします。
No.1ベストアンサー
- 回答日時:
こんにちは!
関数で!がご希望だというコトなので・・・
一例です。
↓の画像で説明すると左側が「元リスト」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さん、ご回答有り難うございます。早速、私が添付していた質問用のファイル(データ)で検証をさせていただきました。その結果、元リストの7行目と10行目の内容は、グループが違う(7行目→3Gr、10行目→2Gr)だけで、客先名・注文番号は同じですが、この場合Grが違うので、7行目はSheet4へ、10行目はSheet3へリストアップされるべきですが、Sheet4のリスト(3Gr)で数量が合計され、Sheet3のリスト(2Gr)にはリストアップされない状況でした。いずれにしろ概ね希望する動きでしたので、入力されている関数の意味を理解し、改善策を考えてみたいと思います。本当に有り難うございました。
No.3
- 回答日時:
関数式が複雑になりそうなので作業列を作って対応します。
お示しの表がシート1のA列からF列目出で2行目の項目名が3行目から下方にデータが入力されているとします。
G3セルには次の式を入力して下方にドラッグコピーします。
=D3&C3&E3
H1セルからJ1セルにはD列で使われているのと同じグループ名を入力します。H1セルには1Gr,I1セルには2Gr,J1セルには3Grのように入力します。
H3セルには次の式を入力してJ3セルまでドラッグコピーしたのちに下方にもドラッグコピーします。
=IF(AND($D3=H$1,COUNTIF($G$3:$G3,$G3)=1),MAX(H$2:H2)+1,"")
そこでお求めのグループごとの表ですがシート2からシート4に表示させることにして、初めにCtrlキーを押しながらこれら3つのシート名をシート見出しでシート2からクリックしていきます。それによって3つのシートは同じ作業グループが形成されます。
そこでシート2のA1セルにはグループ名を入力するためのセルとして1Grと入力します。
A2セルからF2セルにかけてはシート1と同じ項目名を入力します。
A3セルには次の式を入力したのちにF3セルまでドラッグコピーしたのちに下方にもドラッグコピーします。
=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),""))))
これで1Grのデータが表示されますね。
その後はシート1を選択することで作業グループを解除します。
シート3のA1セルに3Grと入力することで関連のデータが表示されますね。シート4でもA1セルに3Grと入力すればよいでしょう。
なお、シート2からシート4のE列ですがデータがない場合には0が表示されています。この0の表示を無くすためにはE列を選択したのちに右クリックして「セルの書式設定」から「表示形式」で「ユーザー定義」を選び ;; を入力すればよいでしょう。
KURUMITOさん回答ありがとうございます。
早速検証させていただきます。ただ、折角回答をいただき申し訳ないのですが、最初にtom04さんが回答してくださった内容を検証し、本ちゃんのリストに適用できるようになりましたので、ベストアンサーはtom04さんにたいと思います。
KURUMITOさんの回答も検証させていただき、スキルアップに利用させていただきます。本当にありがとうございました。
No.2
- 回答日時:
No.1です!
お礼欄の
>Sheet4のリスト(3Gr)で数量が合計され、Sheet3のリスト(2Gr)にはリストアップされない状況でした。
に関してですが・・・
各SheetのB1セルにはそのSheetに抽出したい「グループ名」が入力されているでしょうか?
各Sheetとも、B1セルデータを参照してそのSheetに表示するようにしていますので
前回の数式は他の間違いはあっても、「元リスト」のD列(グループ)だけには
ちゃんと振り分けられ、他のグループがそのSheetに表示されることはないと思います。
他の原因だったらごめんなさいね。m(_ _)m
この回答への補足
tom04さん
その後私の添付した質問用ファイルで検証した結果です。
シート1(元リスト)の作業列に =C3&E3 を =C3&E3&D3 としました。
こうすることで、元リストの注文番号と拡販対象は一致するが、Grが違う際に、元リストの下方にある案件は、シート2以降(Gr別リスト)の作業列関数
=IF(AND(元リスト!D3=$B$1,COUNTIF(元リスト!$G$3:元リスト!G3,元リスト!G3)=1),ROW(),"") のCOUNTIFでの戻り値が2になり、結果ANDの戻り値が0になることにより行番号がブランクになる事を修正。
さらに、シート2以降(Gr別リスト)の数量を戻り値とするセルに入力されている関数
=IF(B3="","",SUMIF(元リスト!G:G,D3&F3,元リスト!F:F))
の中の D3&F3 を D3&F3&E3 に修正することでOKとなりました。
ただし、シート2以降の、B3からF3までオートフィルする関数の中の最後の部分後の &”” がよく理解できませんでした。
昨日、tom04さんが回答してくださった関数を、意味を理解するべく、教本で調べました。おかげさまで、本ちゃんのリストに使用することができます。又、今まで使ったことのない関数も理解することができました。
本当に助かりました。感謝です。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 【困っています】VBA 追加処理の記述を教えてください。 1 2022/08/25 22:54
- Visual Basic(VBA) 【困っています2】VBA 追加処理の記述を教えてください。 2 2022/08/26 11:42
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- 大学・短大 C言語線形リストの問題です 3 2022/12/22 00:45
- Excel(エクセル) エクセルでキーリストからデータを取り出して1枚1枚印刷するには? 11 2022/06/27 09:52
- Visual Basic(VBA) 【前回の続き続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/24 20:49
- Excel(エクセル) 【Excel】指定した文字列に該当する行を重複しないようにリスト 3 2022/03/30 12:27
- Excel(エクセル) 【Excel質問】別シートにある複数の同型の表から、同じ行項目にある数字を集計する 4 2023/02/16 00:14
- Visual Basic(VBA) 【前回の続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/16 16:44
- Excel(エクセル) エクセルの集計方法 3 2022/12/06 20:58
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
Excel 日付を比較したら、同じ...
-
Excelで空白以外の値がある列の...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
Excelで時間計算(負)
-
ウィンドウィズ メモ帳で日付だ...
-
Excel テーブル内の空白行の削除
-
Microsoft Formsの「個人情報や...
-
エクセルで英文字に入れた下線...
-
【Excel VBA】PDFを作成して,...
-
OFFICEで自動保存されたファイ...
-
【スプレッドシート】指定の日...
-
Excelに貼ったリンクについて E...
-
エクセルでXLOOKUP関数...
-
VBAファイルの保存先について
-
エクセルでレーダーチャートの...
-
Outlook 電源OFFの受診の仕方
-
Microsoft Edgeの「ニュースと...
-
何文字超えたファイルだけを抽...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報