【Excel】条件を満たすデータをまとめる

質問

Excel2003を使用しています。

《表1》
日付　　　　　　　コード　　社名　　　受注番号　　金額
2012/04/30　　　100　　　AAA　　　123-45　　　10000
2012/05/31　　　100　　　AAA　　　123-45　　　15000
2012/06/30　　　100　　　AAA　　　120-56　　　10000
2012/05/31　　　101　　　BBB　　　121-44　　　20000
2012/06/30　　　101　　　BBB　　　123-45　　　20000
2012/04/30　　　102　　　CCC　　　124-40　　　30000
2012/06/30　　　102　　　CCC　　　124-40　　　10000
2012/07/31　　　102　　　CCC　　　124-40　　　10000

《表1》で、コードと受注番号の両方が同じ場合、その金額を合計して1行にまとめ、《表2》のようにしたいです。
日付は新しいほうを残したいのですが、可能でしょうか？

《表2》
日付　　　　　　　　コード　　社名　　　受注番号　　金額
2012/05/31　　　　100　　　AAA　　　123-45　　　25000
2012/06/30　　　　100　　　AAA　　　120-56　　　10000
2012/05/31　　　　101　　　BBB　　　121-44　　　20000
2012/06/30　　　　101　　　BBB　　　123-45　　　20000
2012/07/31　　　　102　　　CCC　　　124-40　　　50000

《表2》の状態からさらに他のデータと比較して転記したく、最初はピボットテーブルを使用してみたのですが、使い慣れていないせいか、うまくいかず…。
できれば、VBAか関数で《表2》のようにしたいです。

よろしくお願いします。

回答No.2

don9don9 · ベストアンサー

仮に表1のデータがA1:E9に入っているとします。
以下の手順で作業を行ってみてください。

データを日付の降順に並べ替えます。

F2セルに以下の式を入力します。
=SUMPRODUCT(($B$2:$B$9=B2)*($D$2:$D$9=D2)*($E$2:$E$9))

G2セルに以下の式を入力します。
=SUMPRODUCT(($B$2:$B2=B2)*($D$2:$D2=D2))

このF2:G2の式を最終行（F9:G9）までコピーします。

F列とG列を範囲選択してコピー＞形式を選択して貼り付け＞値とします。
これでF列とG列の内容は計算式から値に置き換わります。

オートフィルタで「G列の値が1と等しくない」行を選択して削除します。

G列とE列を削除し、F列の値を「金額」とします。

データを、コード・日付・受注番号の昇順に並べ替えます。

以上です。

お礼

回答ありがとうございます。

私も質問する前にSUMPRODUCT関数を使って、いろいろと試してみたのですが、関数で得たデータを値として貼り付けてオートフィルタを利用するところまではとても考えが及びませんでした。

勉強になりました。ありがとうございました。

回答No.6

KURUMITO

配列数式やSUMPRODUCT関数を使う場合にはデータが多くなるにつれて計算速度が遅くなります。出来るだけ作業列などを設けて簡単な関数を使って対応することです。次のようにしてはどうでしょう。
表1がシート1に有るとしてA1セルからE1セルには項目名が、下方にそれぞれのデータが入力されているとします。
F2セルには次の式を入力して下方にドラッグコピーします。

=IF(B2="","",B2&"/"&D2)

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

=IF(F2="","",IF(COUNTIF(F$2:F2,F2)=COUNTIF(F:F,F2),MAX(G$1:G1)+1,""))

シート2には表2を作るとしてA1セルからE1セルまでにはシート1と同じ項目名を入力します。

A2セルには次の式を入力して右横方向にE2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(ROW(A1)>MAX(Sheet1!$G:$G),"",IF(COLUMN(A1)<=4,INDEX(Sheet1!$A:$E,MATCH(ROW(A1),Sheet1!$G:$G,0),COLUMN(A1)),IF(COLUMN(A1)=5,SUMIF(Sheet1!$F:$F,$B2&"/"&$D2,Sheet1!$E:$E),"")))

お礼

回答ありがとうございます。

教えていただいた方法を試してみたところ、条件を満たすデータは1つにまとまっているのですが、金額が合計されていませんでした。(日付の新しいほうの金額が残っています)

記載されていた数式を自分でいじってみたのですが、思うようにいかないので、もしよろしければ、再度教えていただけると助かります。

回答No.5

kagakusuki

ANo.4です。
　申し訳御座いません、先程投稿したANo.4は完全に間違っておりました。
　正しくは以下の通りです。

　今仮に、《表1》が存在しているシートがSheet1であり、その中で「日付」という項目名が入力されているセルはA1セルであるものとして、日付は順不同に並んでいて、Sheet3のA列～E列を作業列として使用して、Sheet2に《表2》を表示させるものとします。

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

=IF(ISNUMBER(INDEX(Sheet1!$A:$A,ROW())),COUNTIF(Sheet1!$A:$A,">"&INDEX(Sheet1!$A:$A,ROW()))+COUNTIF(Sheet1!$A$1:INDEX(Sheet1!$A:$A,ROW()),INDEX(Sheet1!$A:$A,ROW()))-(Sheet1!$A$1<=INDEX(Sheet1!$A:$A,ROW())),"")

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

=IF(OR(INDEX(Sheet1!$B:$B,ROW())="",INDEX(Sheet1!$D:$D,ROW())=""),"",INDEX(Sheet1!$B:$B,ROW())&"■"&INDEX(Sheet1!$D:$D,ROW()))

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

=IF(COUNTIF($C$1:$C2,$C2)=1,COUNT(B$1:B1)+1,"")

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

=IF(ISNUMBER($A2),VLOOKUP(ROWS($2:2),$A:$C,3,FALSE),"")

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

=IF(COUNTIF($D$1:$D2,$D2)=1,INDEX(Sheet1!$A:$A,MATCH(ROWS($2:2),$A:$A,0)),"")

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

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

=IF(ROWS($2:2)>COUNT(Sheet3!$B:$B),"",INDEX(Sheet43!B:B,MATCH(ROWS($2:2),Sheet3!$B:$B)))

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

=IF(ROWS($2:2)>COUNT(Sheet3!$B:$B),"",SUMIF(Sheet3!$D:$D,VLOOKUP(ROWS($2:2),Sheet3!$B:$C,2),Sheet3!$E:$E))

　次に、Sheet2のA2セルの書式設定を[日付]として下さい。
　次に、Sheet2のB2セルをコピーして、Sheet2のC2～D2の範囲に貼り付けて下さい。
　次に、Sheet2のE2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet3!$B:$B),"",SUMIF(Sheet3!$C:$C,VLOOKUP(ROWS($2:2),Sheet3!$B:$C,2),Sheet43!$E:$E))

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

　これで、Sheet2に《表2》が自動的に表示されます。

お礼

再度の回答ありがとうございます。

教えていただいた方法だと、自動的に別シートに表示されるので、このあとの作業のことを考えると、ありがたい方法です。

お手数をおかけしました。ありがとうございました。

回答No.4

kagakusuki

今仮に、《表1》が存在しているシートがSheet1であり、その中で「日付」という項目名が入力されているセルはA1セルであるものとして、日付は必ず昇順に並んでいて、Sheet3のA列～C列を作業列として使用して、Sheet2に《表2》を表示させるものとします。

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

=IF(OR(INDEX(Sheet1!$B:$B,ROW())="",INDEX(Sheet1!$D:$D,ROW())=""),"",INDEX(Sheet1!$B:$B,ROW())&"■"&INDEX(Sheet1!$D:$D,ROW()))

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

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

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

=IF(COUNTIF($A$1:$A2,$A2)=1,COUNT(C$1:C1)+1,"")

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

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

=IF(ROWS($2:2)>COUNT(Sheet3!$D:$D),"",INDEX(Sheet1!B:B,MATCH(ROWS($2:2),Sheet3!$D:$D)))

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

=IF(ROWS($2:2)>COUNT(Sheet3!$D:$D),"",SUMIF(Sheet3!$A:$A,INDEX(Sheet3!$A:$A,MATCH(ROWS($2:2),Sheet3!$D:$D)),Sheet3!$B:$B))

　次に、Sheet2のA2セルの書式設定を[日付]として下さい。
　次に、Sheet2のB2セルをコピーして、Sheet2のC2～E2の範囲に貼り付けて下さい。
　次に、Sheet2のA2～E2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　これで、Sheet2に《表2》が自動的に表示されます。

　処で《表2》は、日付順に並べ替えなくとも宜しいのでしょうか？

補足

回答ありがとうございます。

＞処で《表2》は、日付順に並べ替えなくとも宜しいのでしょうか？

今回は日付順に並べる必要はありません。
最終的にはコード順で、そのコード内で受注番号順に並んでいるデータを作成できればと思っています。

回答No.3

tom04

こんにちは！
一例です。

↓の画像のようにSheet1（元データ）に作業用の列を2列設けます。

作業列1のF2セルに
=IF(A2="","",B2&"_"&D2)
作業列2のG2セルに
=IF(A2="","",IF(A2=MAX(IF($F$2:$F$1000=F2,$A$2:$A$1000)),ROW(),""))
※　G2セルは配列数式になってしまいますので、Shift＋Ctrl＋Enterで確定！
この画面からコピー＆ペーストする場合はG2セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

F2：G2セルを範囲指定　→　G2セルのフィルハンドルでオートフィルで下へしっかりコピーしておきます。
（データがなくても構いませんのでしっかり下へコピー！）

そしてSheet2のA2セルに
=IF(COUNT(Sheet1!$G:$G)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$G:$G,ROW(A1))))
という数式を入れ列方向にD2セルまでオートフィルでコピー！
（A列の表示形式は日付にします）

E2セルに
=IF(A2="","",SUMIF(Sheet1!F:F,B2&"_"&D2,Sheet1!E:E))

という数式を入れ最後にA2～E2セルを範囲指定　→　E2セルのフィルハンドルでオートフィルで下へコピー！

これで画像のような感じになります。m（_ _）m

お礼

回答ありがとうございます。

作業列を使用する方法は私も考えました。
が、その作業列で得たデータをうまく利用できず、質問させていただいた次第です。

配列数式はあまり使ったこともなく、勉強になりました。
ありがとうございました。

回答No.1

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

VBAはあまり得意ではないので、一般機能(for XL2003)
ただし、順番が少し異なる

F2セルに =B2&D2 フィルハンドルダブルクリック

データ - 並べ替え 最優先 コード、2番目 受注番号、3番目 日付
データ - 集計 グループの基準 集計用 、金額の合計
表を選択して([Ctrl]+[Shift]+[*]) - [Ctrl]+[G]ジャンプ - セル選択
●空白セル - [OK]
=を入力して、一つ上のセルを選択
セルの確定時は[Ctrl]+[Enter]
左の [2]をクリック
余分な数式を消す

ほとんど 記録マクロで
Sub 集計を行う()
Range("A1:E9").Sort Key1:=Range("B2"), Order1:=xlAscending, Key2:=Range( _
"D2"), Order2:=xlAscending, Key3:=Range("A2"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, SortMethod:=xlPinYin, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal, DataOption3:=xlSortNormal
Range("F1").Select
ActiveCell.FormulaR1C1 = "集計用"
ActiveCell.Characters(1, 3).PhoneticCharacters = "シュウケイヨウ"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-4]&RC[-2]"
Selection.AutoFill Destination:=Range("F2:F9")
Range("F2:F9").Select
Range("A1").Select
Selection.Subtotal GroupBy:=6, Function:=xlSum, TotalList:=Array(5), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
ActiveSheet.Outline.ShowLevels RowLevels:=2
Range("A14:D14").Select
Selection.ClearContents
End Sub

Sub 集計を解除する()
Columns("A:F").Select
Selection.RemoveSubtotal
Columns("F:F").Select
Selection.ClearContents
End Sub

お礼

回答ありがとうございます。

データを並べ替えてから集計機能を利用するなんて、私は思いもつきませんでした。
集計機能もあまり使ったことがないので、勉強になりました。

ありがとうございました。