Excelの関数処理教えてください

質問

Excelでデータ抽出処理を以下のようにしたく、関数でできませんか？

１、Sheet1には以下のようなデータになっており、
　　同じ取引先のデータも含んでいます。
　　<取引先>　　<商品名>　　　<数量>　　<金額>　　<重要フラグ>
　　　Ａ社　　　みかん　　　　　10　　　　23,000　　　　0
　　　Ａ社　　　もも　　　　　　5　　　　12,000　　　　1
　　　Ｂ社　　　ぶどう　　　　　3　　　　8,000　　　　　　1
　　　Ｃ社　　　みかん　　　　　5　　　　11,500　　　　0
　　　Ｃ社　　　バナナ　　　　　5　　　　9,000　　　　　1
　　　Ｄ社　　　すいか　　　　　8　　　　21,500　　　　0
　　　Ｄ社　　　メロン　　　　　15　　　　39,000　　　　　1

２、このSheet1のデータをSheet2に需要フラグ="1"のデータのみ抽出したいです。
　　Sheet2表の最下に合計行も追加した。

３、このSheet1のデータをSheet3に取引先毎に集計したデータを表示したいです。
　　Sheet3表の最下に合計行も追加した。

　できれば関数でできればいいのですが、
　何かよい方法はないでしょうか？よろしくお願いします。

　関数でできなければ、ＶＢＡの標準モジュールでもやりたいですが、
　どなたかご教授よろしくお願いします。

回答No.9

kagakusuki · ベストアンサー

>取引先毎に売上件数と売上金額の集計一覧がほしいです。

　それは失礼しました。
　それでしたら、Sheet4のC列から右側の作業列は必要御座いません。
　ANo.4で数式を入力したSheet4のB列をそのまま使用します。
　そして、Sheet3のA2セルに、次の数式を入力して下さい。

=IF(ROWS($1:2)>COUNT(Sheet4!$B:$B),IF(ROWS($1:2)=COUNT(Sheet4!$B:$B)+2,"合計",""),INDEX(Sheet1!$A:$A,SMALL(Sheet4!$B:$B,ROWS($1:2))))

　次に、Sheet3のB2セルに、次の数式を入力して下さい。

=IF(ROWS($1:2)>COUNT(Sheet4!$B:$B),IF(ROWS($1:2)=COUNT(Sheet4!$B:$B)+2,COUNT(Sheet1!$C:$C),""),COUNTIF(Sheet1!$A:$A,$A2))

　次に、Sheet3のC2セルに、次の数式を入力して下さい。

=IF(ROWS($1:2)>COUNT(Sheet4!$B:$B),IF(ROWS($1:2)=COUNT(Sheet4!$B:$B)+2,SUM(Sheet1!$D:$D),""),SUMIF(Sheet1!$A:$A,$A2,Sheet1!$D:$D))

　次に、Sheet3のA2～C2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

お礼

kagakusukiさん

お蔭様で、Ｅｘｃｅｌ帳票がうまくできました。
どうもありがとうございました。

普段Ｅｘｃｅｌは使うけれども、
関数とかマクロとかあまり触ってないので、
今回は大変勉強になりました。
感謝いたします。

回答No.11

MackyNo1

No10の回答の補足です。

Sheet1のA列に入力されている取引先の重複のないデータを関数で表示させるなら、以下のような数式をA2セルに入力して下方向にオートフィルしてください。

=INDEX(Sheet1!A:A,SMALL(INDEX((MATCH(Sheet1!$A$2:$A$100&"",Sheet1!$A$2:$A$100&"",0)<>ROW(Sheet1!$A$2:$A$100)-1)*1000+ROW(Sheet1!$A$2:$A$100),),ROW(A1)))&""

なお、この数式は計算負荷が高いので、データ範囲が大きかったり、表示データ数が多い場合には再計算に時間がかかりシートの動きが重くなるかもしれません。

その場合は、エクセルで最も便利な集計機能の一つであるピボットテーブルを使用することをお勧めします。

具体的には、行フィールドに取引先、データフィールドには取引先と金額をドロップしてみて下さい（表のレイアウトの修正もできます）。

＃Officeソフトはバージョンによって使用できる機能や操作方法が大きく異なりますので(ピボットテーブルもExcel2007から大きく変更されました）、質問の際には必ずバージョンを明記するようにしましょう。
（

お礼

MackyNo1さん

熱心なご教授どうもありがとうございます。

kagakusukiさんの回答を参考して、
無事解決できました。

回答No.10

MackyNo1

>やりたいことは、
取引先毎に売上件数と売上金額の集計一覧がほしいです。

この場合は、最も基本的な関数であるCOUNTIF関数とSUMIF関数で表示できます。

B2セル（件数の集計）
=IF(COUNTIF(Sheet1!A:A,A2)<ROW(A1),"",COUNTIF(Sheet1!A:A,A2))

C2セル（金額の集計）
=IF(COUNTIF(Sheet1!A:A,A2)<ROW(A1),"",SUMIF(Sheet1!$A:A,A2,Sheet1!D:D))

合計を自動表示するならNo2の回答と同じようにデータ数よりも１大きいセルに集計する関数をIF関数で表示するようにしてください。

=IF(COUNTIF(Sheet1!A:A,A2)+1<ROW(A1),"",IF(COUNTIF(Sheet1!A:A,A2)+1=ROW(A1),SUM($C1:C$2),元の式）

#関数やVBAの回答を含めて、いろいろなパターンの回答が出ていますが、試されているのでしょうか？
それぞれの回答には、条件によってメリット・デメリットがあると思いますが、もし不都合な点などがあれば返信のコメントを入れたほうが良いと思います。

回答No.8

某HN クロメート(Chromate)（@CoalTar）

データ数がある程度多い(5千～1万件くらい)場合として
Sheet1!F列 作業列
Sheet1!F1 0
Sheet1!F2 =SUM(E2,INDEX(F:F,ROW()-1))
フィルハンドルダブルクリック 意味は =SUM(E2,F1)

Sheet2!A列 作業列
Sheet2!A2セルに
=IF(MAX(Sheet1!F:F)=ROW()-2,"合計",IF(MAX(Sheet1!F:F)<ROW()-2,"",MATCH(ROW()-2,Sheet1!F:F)+1))
下へオートフィル
Sheet2!B2セル
=IF(ISNUMBER($A2),INDEX(Sheet1!A:A,$A2),"")
右へ下へオートフィル
数量のD2セル
=IF(ISNUMBER($A2),INDEX(Sheet1!C:C,$A2),IF($A2="","",SUM(D$1:INDEX($D:$D,ROW()-1))))
右へ下へオートフィル

Sheet3はピボットテーブルを使えばよいと思います。

# OFFSET関数、SMALL関数、MATCHなどの検索系で完全一致は計算が重くなります

お礼

CoalTarさん

熱心なご教授どうもありがとうございます。

kagakusukiさんの回答を参考して、
無事解決できました。

回答No.7

KURUMITO

関数のみで対応する方法です。シート1のデータは取引先ごとに整理されている必要もありません。
シート1はもとの表でA1セルからE1セルに項目名が有り、下行にそれぞれのデータがあるとします。
作業列のF列ではF2セルに次の式を入力して下方にオートフィルドラッグします。

=IF(OR(E2=0,E2=""),"",SUM(E$2:E2))

G2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,INT(MAX(G$1:G1))+1.0001,INT(INDEX(G$1:G1,MATCH(A2,A:A,0)))+COUNTIF(A$2:A2,A2)/10000))

シート2ではA1セルからE1セルにはシート1と同じ項目名を入力します。
A2セルには次の式を入力してE2セルまでオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>MAX(Sheet1!$F:$F),"",INDEX(Sheet1!$A:$E,MATCH(ROW(A1),Sheet1!$F:$F,0),COLUMN(A1)))

シート3でも同じように1行目に項目名を入力します。
A2セルには次の式を入力したのちにE2セルまでオートフィルドラッグし、その後に下方にもオートフィルドラッグします。

=IF(COUNTIF($A$1:$A1,"総合計")>0,"",IF(COUNTIF($A$1:$A1,"合計")=MAX(Sheet1!$F:$F),IF(COLUMN(A1)=1,"総合計",IF(COLUMN(A1)=2,"",IF(COLUMN(A1)=3,SUM(Sheet1!$C:$C),IF(COLUMN(A1)=4,SUM(Sheet1!$D:$D),IF(COLUMN(A1)=5,SUM(Sheet1!$E:$E),""))))),IF(OR(ROW(A1)=1,COUNTIF($A$1:$A1,$A1)<COUNTIF(Sheet1!$A:$A,$A1),$A1="合計"),INDEX(Sheet1!$A:$E,MATCH(COUNTIF($A$1:$A1,"合計")+1+IF(OR(ROW(A1)=1,$A1="合計"),0.0001,(COUNTIF($A$1:$A1,$A1)+1)/10000),Sheet1!$G:$G,1),COLUMN(A1)),IF(COUNTIF($A$1:$A1,$A1)=COUNTIF(Sheet1!$A:$A,$A1),IF(COLUMN(A1)=1,"合計",IF(COLUMN(A1)=2,"",IF(COLUMN(A1)=3,SUM(INDIRECT("C"&(ROW()-COUNTIF($A:$A,$A1))):$C1),IF(COLUMN(A1)=4,SUM(INDIRECT("D"&(ROW()-COUNTIF($A:$A,$A1))):$D1),IF(COLUMN(A1)=5,SUM(INDIRECT("E"&(ROW()-COUNTIF($A:$A,$A1))):$E1),""))))),""))))

これでそれぞれの取引先では合計が、また、最終行には総合計が表示されます。
取引先と取引先の間に合計が表示されみにくいですが、それを解消するためには表全体を選択したのちに「条件付き書式」の設定を行い数式によるセルの設定で数式の窓には　=OR($A1="合計,$A1="総合計") 　と入力し「書式」では「塗りつぶし」のタブで黄色などを指定してOKすればよいでしょう。

お礼

KURUMITOさん

熱心なご教授どうもありがとうございます。

kagakusukiさんの回答を参考して、
無事解決できました。

回答No.6

mu2011

>関数でできなければ、ＶＢＡの標準モジュールでもやりたい
⇒複数列項目のデータ抽出を関数で行う事は出来ないとは言いませんが非常の長い数式が必要になります。
　このような作業はマクロ（ＶＢＡか、操作をマクロ記録）が適切と思う。
　複雑な関数やＶＢＡは荷が重いのであれば、フィルタオプションの設定をマクロ記録する方法があるが如何でしょうか。
・データ抽出
　（１）Sheet1の空き列（仮にＧ列）のG1に見出し名として重要フラグを入力、G2に１を入力
　　　　「フィルタオプションの設定」の
　（２）マクロ記録開始→Sheet2のA1を選択フィルタオプションの設定→指定した範囲を選択、リスト範囲欄にSheet1!A:E、検索条件範囲欄にSheet1!G1:G2、抽出範囲欄にA1→ＯＫ→マクロ記録終了
　　因みにSheet1にはご例示の見出し名があるものとしていますので無ければ挿入して下さい。
・データ集計
　ピボットテーブルなら簡単に集計できますのでご検討下さい。

回答No.5

kagakusuki

回答番号ANo.4です。

【作業列を使わずに関数のみで行う方法】
※但し、元データの表が数千行以上にもなりますと、計算処理に要する負荷が、非常に大きくなります。

まず、Sheet2のA2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF(Sheet1!$E:$E,1),IF(ROWS($2:2)=COUNTIF(Sheet1!$E:$E,1)+2,"合計",""),INDEX(Sheet1!$A:$A,SUMPRODUCT(ROW(INDEX(Sheet1!$D:$D,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$D:$D,MATCH(9^99,Sheet1!$D:$D)))*(INDEX(Sheet1!$E:$E,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$E:$E,MATCH(9^99,Sheet1!$D:$D))=1)*(COUNTIF(OFFSET(Sheet1!$E$1,,,ROW(INDEX(Sheet1!$D:$D,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$D:$D,MATCH(9^99,Sheet1!$D:$D)))-ROW(Sheet1!$D$1)+1),1)=ROWS($2:2)))))

　次に、Sheet2のB2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF(Sheet1!$E:$E,1),"",INDEX(Sheet1!$B:$B,SUMPRODUCT(ROW(INDEX(Sheet1!$D:$D,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$D:$D,MATCH(9^99,Sheet1!$D:$D)))*(INDEX(Sheet1!$E:$E,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$E:$E,MATCH(9^99,Sheet1!$D:$D))=1)*(COUNTIF(OFFSET(Sheet1!$E$1,,,ROW(INDEX(Sheet1!$D:$D,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$D:$D,MATCH(9^99,Sheet1!$D:$D)))-ROW(Sheet1!$D$1)+1),1)=ROWS($2:2)))))

　次に、Sheet2のC2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF(Sheet1!$E:$E,1),IF(ROWS($2:2)=COUNTIF(Sheet1!$E:$E,1)+2,SUM(C$1:C1),""),INDEX(Sheet1!C:C,SUMPRODUCT(ROW(INDEX(Sheet1!$D:$D,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$D:$D,MATCH(9^99,Sheet1!$D:$D)))*(INDEX(Sheet1!$E:$E,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$E:$E,MATCH(9^99,Sheet1!$D:$D))=1)*(COUNTIF(OFFSET(Sheet1!$E$1,,,ROW(INDEX(Sheet1!$D:$D,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$D:$D,MATCH(9^99,Sheet1!$D:$D)))-ROW(Sheet1!$D$1)+1),1)=ROWS($2:2)))))

　次に、Sheet2のC2セルをコピーして、Sheet2のD2セルに貼り付けて下さい。

　次に、Sheet2のA2～D2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　次に、Sheet3のA4セルに次の数式を入力して下さい。

=IF(ROWS($4:4)>COUNTIF(Sheet1!$A:$A,$B$1),IF(ROWS($4:4)=COUNTIF(Sheet1!$A:$A,$B$1)+2,"合計",""),INDEX(Sheet1!$B:$B,SUMPRODUCT(ROW(INDEX(Sheet1!$D:$D,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$D:$D,MATCH(9^99,Sheet1!$D:$D)))*(INDEX(Sheet1!$A:$A,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$A:$A,MATCH(9^99,Sheet1!$D:$D))=$B$1)*(COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(INDEX(Sheet1!$D:$D,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$D:$D,MATCH(9^99,Sheet1!$D:$D)))-ROW(Sheet1!$D$1)+1),$B$1)=ROWS($4:4)))))　次に、Sheet3のB4セルに次の数式を入力して下さい。

　次に、Sheet3のB4セルに次の数式を入力して下さい。

=IF(ROWS($4:4)>COUNTIF(Sheet1!$A:$A,$B$1),IF(ROWS($4:4)=COUNTIF(Sheet1!$A:$A,$B$1)+2,SUM(B$3:B3),""),INDEX(Sheet1!C:C,SUMPRODUCT(ROW(INDEX(Sheet1!$D:$D,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$D:$D,MATCH(9^99,Sheet1!$D:$D)))*(INDEX(Sheet1!$A:$A,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$A:$A,MATCH(9^99,Sheet1!$D:$D))=$B$1)*(COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(INDEX(Sheet1!$D:$D,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$D:$D,MATCH(9^99,Sheet1!$D:$D)))-ROW(Sheet1!$D$1)+1),$B$1)=ROWS($4:4)))))

　次に、Sheet3のD4セルに次の数式を入力して下さい。

=IF(ROWS($4:4)>COUNTIF(Sheet1!$A:$A,$B$1),"",INDEX(Sheet1!E:E,SUMPRODUCT(ROW(INDEX(Sheet1!$D:$D,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$D:$D,MATCH(9^99,Sheet1!$D:$D)))*(INDEX(Sheet1!$A:$A,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$A:$A,MATCH(9^99,Sheet1!$D:$D))=$B$1)*(COUNTIF(OFFSET(Sheet1!$A$1,,,ROW(INDEX(Sheet1!$D:$D,ROW(Sheet1!$D$1)+1):INDEX(Sheet1!$D:$D,MATCH(9^99,Sheet1!$D:$D)))-ROW(Sheet1!$D$1)+1),$B$1)=ROWS($4:4)))))

　次に、Sheet3のB4セルをコピーして、Sheet3のC4セルに貼り付けて下さい。
　次に、Sheet3のA4～D4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　最後に、Sheet3のB1セルに

A社

と入力して下さい。

回答No.4

kagakusuki

【関数と作業用シートを使用する方法】
　今仮に、Sheet4を作業用シートとして使用するものとします。
　まず、Sheet4のA1セルに次の数式を入力して下さい。

=IF(INDEX(Sheet1!$E:$E,ROW())=1,ROW(),"")

　次に、Sheet4のB1セルに次の数式を入力して下さい。

=IF(COUNTIF(INDEX(Sheet1!$A:$A,ROW(Sheet1!$A$1)):INDEX(Sheet1!$A:$A,ROW()),INDEX(Sheet1!$A:$A,ROW()))=1,ROW(),"")

　次に、Sheet4のC1セルに次の数式を入力して下さい。

=IF(COLUMNS($A:B)>COUNT($B:$B),"",INDEX(Sheet1!$A:$A,SMALL($B:$B,COLUMNS($A:B))))

　次に、Sheet4のC2セルに次の数式を入力して下さい。

=IF(AND(C$1<>"",INDEX(Sheet1!$A:$A,ROW())=C$1),ROW(),"")

　次に、Sheet4のA1～B1の範囲をコピーして、Sheet4のA2～B2の範囲に貼り付けて下さい。
　次に、Sheet4のA2～C2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
　次に、Sheet4のC列全体をコピーして、C列よりも右にある列に貼り付けて下さい。

　次に、Sheet2のA2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet4!$A:$A),IF(ROWS($2:2)=COUNT(Sheet4!$A:$A)+2,"合計",""),INDEX(Sheet1!$A:$A,SMALL(Sheet4!$A:$A,ROWS($2:2))))

　次に、Sheet2のB2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet4!$A:$A),"",INDEX(Sheet1!$B:$B,SMALL(Sheet4!$A:$A,ROWS($2:2))))

　次に、Sheet2のC2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet4!$A:$A),IF(ROWS($2:2)=COUNT(Sheet4!$A:$A)+2,SUM(C$1:C1),""),INDEX(Sheet1!C:C,SMALL(Sheet4!$A:$A,ROWS($2:2))))))))

　次に、Sheet2のC2セルをコピーして、Sheet2のD2セルに貼り付けて下さい。

　次に、Sheet2のA2～D2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　次に、Sheet3のA4セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet4!$1:$1,$B$1),IF(ROWS($4:4)>COUNT(OFFSET(Sheet4!$B:$B,,MATCH($B$1,Sheet4!$1:$1,0)-COLUMN(Sheet4!$B$1))),IF(ROWS($4:4)=COUNT(OFFSET(Sheet4!$B:$B,,MATCH($B$1,Sheet4!$1:$1,0)-COLUMN(Sheet4!$B$1)))+2,"合計",""),INDEX(Sheet1!$B:$B,SMALL(OFFSET(Sheet4!$B:$B,,MATCH($B$1,Sheet4!$1:$1,0)-COLUMN(Sheet4!$B$1)),ROWS($4:4)))),"")

　次に、Sheet3のB4セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet4!$1:$1,$B$1),IF(ROWS($4:4)>COUNT(OFFSET(Sheet4!$B:$B,,MATCH($B$1,Sheet4!$1:$1,0)-COLUMN(Sheet4!$B$1))),IF(ROWS($4:4)=COUNT(OFFSET(Sheet4!$B:$B,,MATCH($B$1,Sheet4!$1:$1,0)-COLUMN(Sheet4!$B$1)))+2,SUM(B$3:B3),""),INDEX(Sheet1!C:C,SMALL(OFFSET(Sheet4!$B:$B,,MATCH($B$1,Sheet4!$1:$1,0)-COLUMN(Sheet4!$B$1)),ROWS($4:4)))),"")

　次に、Sheet3のD4セルに次の数式を入力して下さい。

=IF(COUNTIF(Sheet4!$1:$1,$B$1),IF(ROWS($4:4)>COUNT(OFFSET(Sheet4!$B:$B,,MATCH($B$1,Sheet4!$1:$1,0)-COLUMN(Sheet4!$B$1))),"",INDEX(Sheet1!E:E,SMALL(OFFSET(Sheet4!$B:$B,,MATCH($B$1,Sheet4!$1:$1,0)-COLUMN(Sheet4!$B$1)),ROWS($4:4)))),"")

　次に、Sheet3のB4セルをコピーして、Sheet3のC4セルに貼り付けて下さい。
　次に、Sheet3のA4～D4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。

　最後に、Sheet3のB1セルに

A社

と入力して下さい。

お礼

kagakusukiさん

早速のご教授ありがとうございます。
いま、書いて頂いた内容を勉強消化中です。
Sheet2の結果行けそうと思います。
Sheet3は私の質問の説明不足ですので、
やりたいことは、
取引先毎に売上件数と売上金額の集計一覧がほしいです。
例：
取引先　　件数　　金額
Ａ社　　　　2 　 35,000
Ｂ社 　　 1 　 8,000
Ｃ社 　　 2 　 20,500
・・・・・・・

合計　　　200　　　　1,234,000　　　　

kagakusukiさんが教えって頂いた取引先毎の取引明細の抽出イメージはちょっと違います。
私の説明不足結果で、kagakusukiさんの貴重な時間を取られてしまって本当にすみません。
また、ご教授よろしくお願いします。

回答No.3

keithin

標準モジュールに次のように記入し実行します。
言わずもがなですがシート１は
　１行目にタイトル行，２行目から実データ
　Ａ列からデータ，都合Ｅ列がフラグ列
という配置に並べてから行ってください。

sub macro1()
　dim r as long
　worksheets("Sheet3").cells.clearcontents
　worksheets("Sheet2").cells.clearcontents
　worksheets("Sheet1").select

’重要フラグ（E列）が１のデータをコピー
　range("E:E").autofilter field:=1, criteria1:=1
　range("A:E").copy destination:=worksheets("Sheet2").range("A1")

’合計行の追加
　with worksheets("Sheet2").range("D65536").end(xlup)
　.offset(1).formular1c1 = "=SUM(R1C:R[-1]C)"
　.offset(1, -2) = "合計"
　end with

’取引先一覧を抽出しコピー
　range("A:A").advancedfilter _
　　action:=xlfiltercopy, _
　　copytorange:=worksheets("Sheet3").range("A1"), _
　　unique:=true

’集計表の作成
　worksheets("Sheet3").select
　r = .range("A65536").end(xlup).row
　range("B1") = "金額"
　cells(r + 1, "A") = "合計"
　cells(r + 1, "B").formular1c1 = "=SUM(R1C:R[-1]C)"
　range("B2:B" & r).formula = "=SUMIF(Sheet1!A:A,A2,Sheet1!D:D)"

end sub

回答No.2

MackyNo1

＞２、このSheet1のデータをSheet2に需要フラグ="1"のデータのみ抽出したいです。

関数で対応するなら以下のような数式をSheet2のA2セル（どのセルに入力する場合でも基本は同じ数式です）に入力し、右方向に４つ、下方向に適当数オートフィルします。

=IF(COUNTIF(Sheet1!$E$2:$E$10,1)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(INDEX(Sheet1!$E$2:$E$10*ROW($A$2:$A$10),),ROW(A1)+COUNTIF(Sheet1!$E$2:$E$10,"<>1"))))

＞Sheet2表の最下に合計行も追加した。

上記の数式は合計欄も空白になっていますので、C2セルを以下のように変更し右方向に１つ、下方向に適当数オートフィルしてください。

=IF(COUNTIF(Sheet1!$E$2:$E$10,1)+1<ROW(C1),"",IF(COUNTIF(Sheet1!$E$2:$E$10,1)+1=ROW(C1),SUM($C1:C$2),INDEX(Sheet1!C:C,SMALL(INDEX(Sheet1!$E$2:$E$10*ROW($A$2:$A$10),),ROW(C1)+COUNTIF(Sheet1!$E$2:$E$10,"<>1")))))

＞３、このSheet1のデータをSheet3に取引先毎に集計したデータを表示したいです。

この場合もSheet3に以下のような数式を入力してください（A列のデータは必要ないのかな？）。

=IF(COUNTIF(Sheet1!$A$2:$A$10,"Ａ社")<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(INDEX((Sheet1!$A$2:$A$10="Ａ社")*ROW($A$2:$A$10),),ROW(A1)+COUNTIF(Sheet1!$A$2:$A$10,"<>"&"Ａ社"))))

上記の数式を多用するとシートの動きが重くなりますので、会社名で抽出するシートは1枚にして、”A社”の部分をセル参照にして、この部分のセルを入力規則のリストを使ってドロップダウンリストから会社名を選択できるようにする（1枚のシートで処理する）ことをお勧めします。

同様に合計欄も数式もご自分で訂正してみてください。

回答No.1

tom04

こんばんは！
関数の方法での一例です。

↓の画像（少し小さくて見づらいかもしれません）のようにSheet1に作業用の列を設けています（Sheet3に重複なしに抽出するため）
Sheet2・Sheet3の1行目の各項目は入力してあるとします。

Sheet1の作業列F2セルに
=IF(COUNTIF($A$2:A2,A2)=1,ROW(),"")
という数式を入れオートフィルでずぃ～～～！っと下へコピーしておきます。

Sheet2のA2セルに
=IF(COUNTIF(Sheet1!$E:$E,1)<ROW(A1),"",INDEX(Sheet1!A$1:A$100,SMALL(IF(Sheet1!$E$1:$E$100=1,ROW($A$1:$A$100)),ROW(A1))))
※　これは配列数式になってしまいますので、この画面からSheet2のA2セルにコピー＆ペーストする場合は
A2セルに貼り付け後、数式バー内で一度クリック！編集可能になりますので
Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
これを列方向と行方向にオートフィルでコピーすると画像のSheet2のようになります。

続いてSheet3のA2セルに（どちらも配列数式ではありません！）
=IF(COUNT(Sheet1!F:F)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!F:F,ROW(A1))))
B2セルに
=IF(A2="","",SUMIF(Sheet1!A:A,A2,Sheet1!D:D))
としてA2・B2セルを範囲指定　→　B2セルのフィルハンドルで下へコピー！
これで画像のような感じになります。

ただし・・・
最終行に合計の欄がほしいというコトですが、関数ではかなり難しいと思います。
VBAだと可能ですので、コードの一例も載せておきます。
この場合はSheet1の作業列は不要です。

Alt+F11キー　→　VBE画面が出ますので、画面左側の「This Workbook」をダブルクリックし、
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（この場合もSheet2・Sheet3の1行目の項目はすでに入力済みだとしています。）

Alt+F8キー　→　マクロ　→　マクロ実行です。

Sub test() 'この行から
Dim i, j As Long
Dim vl As Variant
Dim ws1, ws2, ws3 As Worksheet
Set ws1 = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
Set ws3 = Worksheets("sheet3")
i = ws2.Cells(Rows.Count, 1).End(xlUp).Row
j = ws3.Cells(Rows.Count, 1).End(xlUp).Row
If i > 1 Then
Range(ws2.Cells(2, 1), ws2.Cells(i, 5)).Clear
End If
If j > 1 Then
Range(ws3.Cells(2, 1), ws3.Cells(j, 2)).Clear
End If
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To 5
If ws1.Cells(i, 5) = 1 Then
ws2.Cells(Rows.Count, j).End(xlUp).Offset(1) = ws1.Cells(i, j)
End If
Next j
Next i
j = ws2.Cells(Rows.Count, 1).End(xlUp).Row
With ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = "合計"
.Offset(, 3) = WorksheetFunction.Sum(Range(ws2.Cells(2, 4), ws2.Cells(j, 4)))
End With
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
If WorksheetFunction.CountIf(ws3.Columns(1), ws1.Cells(i, 1)) = 0 Then
ws3.Cells(Rows.Count, 1).End(xlUp).Offset(1) = ws1.Cells(i, 1)
End If
Next i
For j = 2 To ws3.Cells(Rows.Count, 1).End(xlUp).Row
vl = 0
For i = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
If ws1.Cells(i, 1) = ws3.Cells(j, 1) Then
vl = vl + ws1.Cells(i, 4)
End If
Next i
ws3.Cells(j, 2) = vl
Next j
j = ws3.Cells(Rows.Count, 1).End(xlUp).Row
With ws3.Cells(j + 1, 1)
.Value = "合計"
.Offset(, 1) = WorksheetFunction.Sum(Range(ws3.Cells(2, 2), ws3.Cells(j, 2)))
End With
End Sub 'この行まで

こんな感じではどうでしょうか？
どうも長々と失礼しました。m(_ _)m

お礼

tom04さん

丁念なご教授どうもありがとうございます。

kagakusukiさんの回答を参考して、
無事解決できました。