Excelでデータ抽出処理を以下のようにしたく、関数でできませんか?
1、Sheet1には以下のようなデータになっており、
同じ取引先のデータも含んでいます。
<取引先> <商品名> <数量> <金額> <重要フラグ>
A社 みかん 10 23,000 0
A社 もも 5 12,000 1
B社 ぶどう 3 8,000 1
C社 みかん 5 11,500 0
C社 バナナ 5 9,000 1
D社 すいか 8 21,500 0
D社 メロン 15 39,000 1
2、このSheet1のデータをSheet2に需要フラグ="1"のデータのみ抽出したいです。
Sheet2表の最下に合計行も追加した。
3、このSheet1のデータをSheet3に取引先毎に集計したデータを表示したいです。
Sheet3表の最下に合計行も追加した。
できれば関数でできればいいのですが、
何かよい方法はないでしょうか?よろしくお願いします。
関数でできなければ、VBAの標準モジュールでもやりたいですが、
どなたかご教授よろしくお願いします。
No.1
- 回答日時:
こんばんは!
関数の方法での一例です。
↓の画像(少し小さくて見づらいかもしれません)のように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
No.2
- 回答日時:
>2、このSheet1のデータをSheet2に需要フラグ="1"のデータのみ抽出したいです。
関数で対応するなら以下のような数式をSheet2のA2セル(どのセルに入力する場合でも基本は同じ数式です)に入力し、右方向に4つ、下方向に適当数オートフィルします。
=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セルを以下のように変更し右方向に1つ、下方向に適当数オートフィルしてください。
=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")))))
>3、このSheet1のデータをSheet3に取引先毎に集計したデータを表示したいです。
この場合もSheet3に以下のような数式を入力してください(A列のデータは必要ないのかな?)。
=IF(COUNTIF(Sheet1!$A$2:$A$10,"A社")<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(INDEX((Sheet1!$A$2:$A$10="A社")*ROW($A$2:$A$10),),ROW(A1)+COUNTIF(Sheet1!$A$2:$A$10,"<>"&"A社"))))
上記の数式を多用するとシートの動きが重くなりますので、会社名で抽出するシートは1枚にして、”A社”の部分をセル参照にして、この部分のセルを入力規則のリストを使ってドロップダウンリストから会社名を選択できるようにする(1枚のシートで処理する)ことをお勧めします。
同様に合計欄も数式もご自分で訂正してみてください。
No.3
- 回答日時:
標準モジュールに次のように記入し実行します。
言わずもがなですがシート1は
1行目にタイトル行,2行目から実データ
A列からデータ,都合E列がフラグ列
という配置に並べてから行ってください。
sub macro1()
dim r as long
worksheets("Sheet3").cells.clearcontents
worksheets("Sheet2").cells.clearcontents
worksheets("Sheet1").select
’重要フラグ(E列)が1のデータをコピー
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.4
- 回答日時:
【関数と作業用シートを使用する方法】
今仮に、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は私の質問の説明不足ですので、
やりたいことは、
取引先毎に売上件数と売上金額の集計一覧がほしいです。
例:
取引先 件数 金額
A社 2 35,000
B社 1 8,000
C社 2 20,500
・・・・・・・
合計 200 1,234,000
kagakusukiさんが教えって頂いた取引先毎の取引明細の抽出イメージはちょっと違います。
私の説明不足結果で、kagakusukiさんの貴重な時間を取られてしまって本当にすみません。
また、ご教授よろしくお願いします。
No.5
- 回答日時:
回答番号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.6
- 回答日時:
>関数でできなければ、VBAの標準モジュールでもやりたい
⇒複数列項目のデータ抽出を関数で行う事は出来ないとは言いませんが非常の長い数式が必要になります。
このような作業はマクロ(VBAか、操作をマクロ記録)が適切と思う。
複雑な関数やVBAは荷が重いのであれば、フィルタオプションの設定をマクロ記録する方法があるが如何でしょうか。
・データ抽出
(1)Sheet1の空き列(仮にG列)のG1に見出し名として重要フラグを入力、G2に1を入力
「フィルタオプションの設定」の
(2)マクロ記録開始→Sheet2のA1を選択フィルタオプションの設定→指定した範囲を選択、リスト範囲欄にSheet1!A:E、検索条件範囲欄にSheet1!G1:G2、抽出範囲欄にA1→OK→マクロ記録終了
因みにSheet1にはご例示の見出し名があるものとしていますので無ければ挿入して下さい。
・データ集計
ピボットテーブルなら簡単に集計できますのでご検討下さい。
No.7
- 回答日時:
関数のみで対応する方法です。
シート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.8
- 回答日時:
データ数がある程度多い(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などの検索系で完全一致は計算が重くなります
No.9ベストアンサー
- 回答日時:
>取引先毎に売上件数と売上金額の集計一覧がほしいです。
それは失礼しました。
それでしたら、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さん
お蔭様で、Excel帳票がうまくできました。
どうもありがとうございました。
普段Excelは使うけれども、
関数とかマクロとかあまり触ってないので、
今回は大変勉強になりました。
感謝いたします。
No.10
- 回答日時:
>やりたいことは、
取引先毎に売上件数と売上金額の集計一覧がほしいです。
この場合は、最も基本的な関数である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の回答と同じようにデータ数よりも1大きいセルに集計する関数をIF関数で表示するようにしてください。
=IF(COUNTIF(Sheet1!A:A,A2)+1<ROW(A1),"",IF(COUNTIF(Sheet1!A:A,A2)+1=ROW(A1),SUM($C1:C$2),元の式)
#関数やVBAの回答を含めて、いろいろなパターンの回答が出ていますが、試されているのでしょうか?
それぞれの回答には、条件によってメリット・デメリットがあると思いますが、もし不都合な点などがあれば返信のコメントを入れたほうが良いと思います。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 【Excel質問】別シートにある複数の同型の表から、同じ行項目にある数字を集計する 4 2023/02/16 00:14
- Visual Basic(VBA) vbaのvlookup関数エラー原因を教えていただけないでしょうか。 3 2022/04/25 16:16
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- Visual Basic(VBA) このプログラムなんですがsheetにデータを置いて表示できるようにしてありますがsheetに101を 2 2023/02/23 20:13
- その他(プログラミング・Web制作) pythonでクラスで複数のメソッドを利用する方法 2 2022/04/15 04:17
- Visual Basic(VBA) VBA For Each 〜 複数条件について 3 2022/10/20 20:05
- その他(IT・Webサービス) 高速処理可能な表計算ソフトについて ExcelやGoogleスプレッドシートのような表計算ソフトで、 2 2023/04/29 16:06
- Excel(エクセル) Excel 売上管理シートに入力した売上データを、日報に自動反映させたいと考えています。 売上管理シ 3 2023/04/29 18:08
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【関数】スペースがいくつ入っ...
-
西暦や和暦の表示をyyyymmdd表...
-
【Microsoft Office Excel Comp...
-
Excelはなんで先頭の0を消すん...
-
Excelのセルを飛ばして入力する
-
別シートからの文字を変更
-
エクセルの行の抽出について質...
-
Excelのオートフィル
-
Excel 2019 のピボットテーブル...
-
スプレッドシート クエリ関数 1...
-
excelの不要な行の削除ができな...
-
Excel初心者です。 詳しい方、...
-
【Excel】セル内の時間帯が特定...
-
Excel初心者です。 詳しい方、...
-
EXACT関数とIF関数の組み合わせ...
-
Excelのグラフ軸について
-
スマートな関数を教えて下さい。
-
Excelで全角を半角にしたいので...
-
【マクロ】エクセルにかいてあ...
-
Excel:一部のフォントでセルの...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報