エクセルのシートAに商品番号(実際は1000個近くあります)と
その売り上げ個数がずらっと並んでいます。
各顧客ごとの売り上げ結果なので、商品番号がかなり重複しています。
これをシートBに各商品ごとに売り上げ個数が表示されるような
関数式が知りたいです。
ちなみにシートAとシートBの商品の並び順は違います。
今までは商品ごとに合計を出してから、別シートで該当商品を探し、
手入力で入れていたので、この作業に3時間くらいかかっていました。
入力ミスの可能性もあるので、さらに確認作業も大変でした。
【シートA】
A B
---------
001 3
001 2
001 2
002 1
002 1
003 1
003 2
003 5
003 3
【シートB】
A B
---------
001 7
002 2
003 11
いろいろ調べて試してみましたが苦戦しています。
まずはシートAの「C1」に関数「=SUMIF($A$1:$A$2000,A1,$B$1:$B$2000)」を入力し、
各商品ごとの合計数を出し、それをC欄の下(C2000)までとドラッグ?してみたのですが、
そうすると下のように、同じ商品内で数値が重複して出るので、
今度は余計な数値(セル)を削除しないといけないと思い、
そこからつまづいてしまいました。
【シートA】
A B C
---------------
001 3 7
001 2 7
001 2 7
002 1 2
002 1 2
003 1 11
003 2 11
003 5 11
003 3 11
長文ですみません。
方法をご存知の方、ぜひ教えてください。
よろしくお願いします!
A 回答 (6件)
- 最新から表示
- 回答順に表示
No.6
- 回答日時:
2007以上の場合
(1)シートAのA列をシートBのA列へコピーする
(2)シートBのA列を列選択した状態でデータ→重複削除→OKを選択
(3)シートBのB1セルに『=SUMIF(シートA!A:A,A1,シートA!B:B)』を入力
(4)シートBのA列最終行までB1セルの数式をオートフィル
2003未満の場合
上記(2)ではなく、以下を参考にしていただき、
http://allabout.co.jp/gm/gc/376556/2/
シートAのA列「A:A」をリスト範囲とし、C列にでも書き出し
(1行目に項目名を指定していない場合はC1セルを削除)
シートBのA列へシートAのC列をコピーしてください。
No.5
- 回答日時:
こんばんは!
すでに色々回答が出ていますので、参考程度で・・・
VBAでの一例です。
Sheet1のデータをSheet2に表示するとします。
Alt+F11キー → メニュー → 挿入 → 標準モジュール → VBE画面のカーソルが点滅しているところに
↓のコードをコピー&ペースト → Excel画面に戻りマクロを実行してみてください。
(Alt+F8キー → マクロ → マクロ実行です)
Sub Sample1() 'この行から
Dim i As Long, lastRow As Long, wS As Worksheet
Set wS = Worksheets("Sheet2") '←「Sheet2」は実際のSheet名に!
wS.Cells.ClearContents
wS.Range("B1") = "数量"
With Worksheets("Sheet1") '←「Sheet1」も実際のSheet名に!
.Range("A:A").AdvancedFilter Action:=xlFilterCopy, copytorange:=wS.Range("A1"), unique:=True
lastRow = wS.Cells(Rows.Count, "A").End(xlUp).Row
With Range(wS.Cells(2, "B"), wS.Cells(lastRow, "B"))
.Formula = "=SUMIF(Sheet1!A:A,A2,Sheet1!B:B)"
End With
End With
End Sub 'この行まで
※ コード内に説明を入れていますが、各Sheet名は実際のSheet名にしてください。
※ 関数でないのでSheet1のデータ変更があるたびにマクロを実行する必要があります。m(_ _)m
No.4
- 回答日時:
別シートに商品番号が入力されているなら、単純にそのシートでSUMIF関数で集計するだけだと思うのですが、どうしてC列に数式を入力しているのでしょうか?
もし、現在の数式で最初の商品番号のデータだけ集計数を表示したいなら、C2セル(本当は項目名があるのではないでしょうか)に以下の式を入力しオートフィルします。
=IF(COUNTIF($A$2:A2,A2)>1,"",SUMIF($A$2:$A$2000,A1,$B$2:$B$2000))
このようにしておけば、オートフィルタでC列の「空白」のチェックを外して数値だけを表示して、別シートにコピー貼り付けするのが簡単です。
このような手順を踏まずに、別シートに商品番号だけのリストを作成しておき、このセルを参照するSUMIF関数を使うのが簡単だと思います。
もしSheet1のデータにある商品番号から、別シートに重複のない商品データリストを作成したい場合は、商品名の列をコピーし、この列でデータタブの「詳細設定」で「重複するデータは無視する」にチェックを入れれば必要なデータを表示させることができます。
ちなみに、表示する商品データ数が少なければ複雑な関数を使用すれば自動的に表示できますが、データ数が多い場合はシートの動きが重くなるのでお勧めできません。
#Officeソフトはバージョンによって使用できる機能や操作方法が大きく異なりますので、質問の際には必ずバージョンを明記するようにしましょう。
No.3
- 回答日時:
VBAの一例です。
シートBを選択すると計算するようになっています。
シートBのシート名の上で右クリック後、コードの表示を選択して
出てきた画面の白い部分に下記をコピーして貼り付けて下さい。
Private Sub Worksheet_Activate()
Cells.ClearContents
Sheets("A").Columns("A:A").Copy Destination:=Columns("A:A")
myRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:A" & myRow).Sort Key1:=Range("A1"), order1:=xlAscending
For i = myRow To 2 Step -1
If Range("A" & i) = Range("A" & i - 1) Then
Range("A" & i).EntireRow.Delete
End If
Next i
Range("B1").FormulaR1C1 = "=SUMIF(A!C[-1],RC[-1],A!C)"
Range("B1").Copy Destination:=Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row)
End Sub
データが膨大になると時間がかかるようになってしまいますが
2000くらいなら問題ないかと思います。
No.2
- 回答日時:
No1の方の言われるように、ピボットテーブル使うほうが楽ですけど。
。。あくまでコピー&ペースト使うのであれば
「データ」「並び替えとフィルタ」「詳細設定」で、
リスト範囲をA列のみ指定して、「重複するレコードは無視する」にチェックしてください。
【シートA】
A B C
---------------
001 3 7
002 1 2
003 1 11
と間の重複する行が非表示になります。
これをコピー&ペーストして、B列を消せばできます。
※非表示の解除は「データ」「並び替えとフィルタ」「クリア」でできます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) excelで検索した商品の画像(ネットワーク上の)を表示させたい。 3 2023/06/28 00:32
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Excel(エクセル) 【条件付き書式】countifsで複数条件を満たしたセルを赤くする方法 2 2023/02/09 23:53
- Excel(エクセル) 特定のセルに数値が入っているシートのみ印刷する方法 2 2022/12/07 13:03
- Excel(エクセル) Excel 関数 vlookupなどの使い方について質問です。 シート1に品番、商品名、単価、発注条 6 2022/06/15 19:16
- Excel(エクセル) エクセルで複数設定したハイパーリンク先を、どれを選んでも画面の左上に来るようにしたいのですが・・・ 3 2022/04/07 16:15
- Excel(エクセル) SUMIFのIF分岐について 4 2023/04/15 12:57
- Excel(エクセル) Excelマクロ 差分抽出の方法が知りたいです。 2 2023/03/07 13:25
- Excel(エクセル) VBAで同フォルダ内の別ブックを開かず参照して条件の一致する行の指定セルを抽出するには? 1 2022/07/21 19:29
- その他(Microsoft Office) Excelで総数量を変動させたい 2 2022/11/04 23:49
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
文字の色も参照 VLOOKUP
-
Excel VBA ピボットテーブルに...
-
VBAで繰り返しコピーしながら下...
-
エクセルのブック分割マクロを...
-
ExcelのVlookup関数の制限について
-
エクセルの列の限界は255列以上...
-
エクセルの保護で、列の表示や...
-
エクセルで、book全体の検索&...
-
エクセルの複数シートにあるデ...
-
エクセルで、チェックボックス...
-
エクセルの表を自動的に、項目...
-
Excelでの並べ替えを全シートま...
-
VBAで項目ごとにシートを分けた...
-
エクセル マクロ 標準モジュー...
-
Excelで条件別にシートを振り分...
-
Excel の複数シートの列幅を同...
-
別シートから月(MONTH)で抽出す...
-
VBA 複数シートをひとつにまと...
-
エクセルの特定の複数の行を非...
-
SUMPRODUCTにて別シートのデー...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ExcelのVlookup関数の制限について
-
文字の色も参照 VLOOKUP
-
オートフィルタ使用時にCOUNTIF...
-
エクセルの保護で、列の表示や...
-
VBAで繰り返しコピーしながら下...
-
エクセル関数に詳しい方、教え...
-
【条件付き書式】countifsで複...
-
Excel の複数シートの列幅を同...
-
エクセル マクロ 標準モジュー...
-
エクセルで横並びの複数データ...
-
エクセルの列の限界は255列以上...
-
Excelでの並べ替えを全シートま...
-
VLOOKアップ関数の結果の...
-
SUMPRODUCTにて別シートのデー...
-
エクセルで、チェックボックス...
-
Excel VBA ピボットテーブルに...
-
【エクセル】1列のデータを交...
-
エクセルVBAで、ある文字を含ん...
-
エクセルのブック分割マクロを...
-
excel 複数のシートの同じ場所...
おすすめ情報