excel 小計 他の列も残したい

質問

たとえばExcelシートで
商品コード,商品名,数量
CD01,商品名1,2
CD02,商品名2,1
CD01,商品名1,2

というような列項目でを小計処理する場合、
・商品コードで集計したら商品名がわからない
・商品名で集計したらコードがわからないから中間処理の場合、後が面倒。
というジレンマになると思いますが、比較的楽に
商品コード,商品名,数量(小計)
CD01,商品名1,4
CD02,商品名2,1
この形にするテクニックなどはありますでしょうか？
今の流れは
1.コードをキーに小計
2.小計行のみ表示
3.ジャンプ[可視セル]を選択し、別シートに小計行のみ複製
4." の集計"を置換削除
5.vlookup()でコード基準で商品名を引用する
……毎回コレをしてるけど、面倒だなぁと。

よろしくお願いします。

回答No.2

tsubu-yuki · ベストアンサー

商品コード＆商品名
の列を用意してみては。
集計の機能を使うなら、それが一番楽かもです。

あるいは、コード表的なモノを別枠で用意しておいて、
SUMIF関数やSUMPRODUCT関数を使うのも良いかもしれませんね。
別枠に集計するなら、コレも難しくはないです。
> 3.ジャンプ[可視セル]を選択し、別シートに小計行のみ「複製」
する手間だけは省けますよ。

もしくは、ピボットテーブルを使う。
「エクセル的に」はコレは最適解だと思いますよ。

お礼

商品コード＆商品名 で小計しろという事ですね。
いい気づきをありがとうございます。

sumif()も別枠前提だけど小計処理の方も結局
別シート使ってるし、その分のコストはあんまり
変わりませんね。
シート複製=>コードで重複削除=>sumif()でたしかに
手間は少し減ってくれそうです。

sumproduct()は…解説読んだけど手に負えそうにない
意味は分かるけど使いこなせそうに思えないです…

ピボットは融通が利かなくていまいち使いにくい…
できあがる表のレイアウトの訂正とか
図示頂いたピボットでも、CDと商品名は1行になって
欲しいんだけど…

1番詳しく色々な気づきを頂けたtsubu-yuki 様をBAにさせて頂きます。
ありがとうございました。

回答No.5

imogasi

私も質問に上げるデータ数が、３つでは少ないと思いますよ。
それまでの回答に、文句が出ないからといって、意見がおかしいとはならないだろう。
ーー
（１）ピボットでやってみたが、名称列（商品名や店名や人名文字列）をだすのができなかった（ただし勉強不足の可能性あり）。
（２）ＶＢＡでやればなんということない。
（３）ＳＱＬ（ＡＤＯ利用）でも出せる。
（４）関数＋操作（ユニークデータを簡単に作るためフィルタ利用）
関数でユニークデータを作る人もいるだろうが、式が面倒くさい。
例データ　Ａ１：Ｃ９
店コード 店名 数量
001 ああ 1
002 いい 2
001 ああ 3
003 うう 4
005 おお 5
001 ああ 6
003 うう 7
002 いい 8
ーーー
Ａ，Ｂ列のデータのあるセルを範囲指定して
データーフィルター詳細設定ー重複するレコードは無視する、で
Ｇ，Ｈ列に
店コード 店名
001 ああ
002 いい
003 うう
005 おお
を出し、Ｉ列に関数
=SUMIF($A$2:$A$9,G2,$C$2:$C$9)を入れて下方向に式複写
結果
店コード 店名 　数量
001 ああ 　10
002 いい　 10
003 うう　 11
005 おお 　5

お礼

mogasi様
データ3つで少ないのは、そもそも簡単な設問内容に対して文全体が長くて
読み手に苦痛になりそうなのを、出来るだけ軽くするために、一見して意味を
類推できる所は冗長分を排除しようとした結果なので、その通りですといいますか。

あと「意見がおかしい」とはいってませんよ？「質問内容が足りない」と
いわれて「(状況から)特に不足は無いと考えてます」という回答ですけど…

--
以下のサンプルはsumif()を使った実例でしょうか。
ありがとうございます。

回答No.4

bunjii

>他の方が普通に回答されてるので設問に対して情報の不足は特に無いと考えてます。
その結果はあなたが求めている状態でしょうか？
単なる考え方または勝手解釈で処理した結果を見せているだけですよね？
私の勝手解釈を披露すると添付画像のようになります。
E列は作業用です。E1セルへ次の数式を入力して下へ必要数コピーします。
=IF(COUNTIFS(A$1:A1,A1,B$1:B1,B1)=1,ROW(),"")
G列からI列が求める結果になります。
G1セルの数式 =IF(COUNT(E:E)>=ROW(),INDEX(A:A,SMALL(E:E,ROW())),"")
H1セルの数式 =IF(G1="","",VLOOKUP(G1,A:B,2,FALSE))
I1セルの数式 =IF(ROW()>1,IF(G1="","",SUMIFS(C:C,A:A,G1,B:B,H1)),C1)
G1:I1の3つのセルを選択して下へ必要数コピーすれば目的の品名毎の集計ができます。
尚、Excel 2013で検証しましたがExcel 2007以降で再現できるはずです。
Excel 2003以前のバージョンではSUMIFS関数が無いので再現できません。
集計条件が商品コードまたは商品名の何れか1つの場合はSUMIF関数に置き換えても問題ないでしょう。（あなたが提示した条件ではSUMIF関数でも結果は同じと思われます）

お礼

bunjii様

>>他の方が普通に回答されてるので設問に対して情報の不足は特に無いと考えてます。
>その結果はあなたが求めている状態でしょうか？

意味がずれると怖いので回答しにくいですが、

「その結果」という言葉が、設問内11行目～13行目を指すなら
この結果が求めているそのものです。

「その結果」という言葉が、「頂いた回答群」を指すなら
これも求めた結果として納得していました。

2回目のこちら発言の回答例は興味深く見させて頂きましたが、
なにぶん判断基準が「私の手間」でして、コレをサラッと構築するの
はしんどそうなのでちょっと使えそうにないです。すいません。

情報の過不足については、お互いに感覚がずれてるような状態と
思いますので、もう諦めて頂ければと。

私としては
設問内容の、一般性(ありがちな事)や難易度(簡単)を考えると、
設問が無駄に長くて読み手に負担を掛けて申し訳ないと思っている
状態だったりしますので。

昨日、BA作業を失敗して質問が続いてるような感じですので、この質問は閉めますね。
回答ありがとうございました。

回答No.3

bunjii

>たとえばExcelシートで
模擬データが少ないので的確な回答が困難です。
コードと商品名の関係は？
1.CD01=商品名1のような関係である
2.CD01には商品名1と商品名Aがある

>・商品コードで集計したら商品名がわからない
>・商品名で集計したらコードがわからないから中間処理の場合、後が面倒。
前述の「CD01=商品名1」と言う関係ならそのようなことにならない。
「CD01には商品名1と商品名Aがある」と言う関係なら集計目的が異なる。

>今の流れは
1.コードをキーに小計・・何処のセルへ数式を設定しますか？
3.ジャンプ[可視セル]を選択し、別シートに小計行のみ複製・・意味不明
4." の集計"を置換削除・・意味不明
5.vlookup()でコード基準で商品名を引用する・・「CD01=商品名1」ですよね？

模擬データを10行ほど追加して、小計（商品名毎の合計？）を求めた結果を提示してください。
シート名と行番号や列記号（A、B、C等）も分かるようにしてください。
どのシートのどのセルにどのような数式を設定するかについて提言できます。
尚、Excelのバージョンも提示してください。

補足

>模擬データが少ないので的確な回答が困難です。
他の方が普通に回答されてるので設問に対して
情報の不足は特に無いと考えてます。

Excelの小計処理で集計すると、集計行にはキー項目と集計列しか
残らないので、他の列も残って欲しい時の作業が手間なのを
どう低コストに実現するかという設問です。

回答No.1

Nouble

ピボットテーブルを
使えば、良い
と、思いますよ?

中級者以上、なら
関数で、楽ちん
ですが

メンテ、できなければ
駄目、ですものね

お礼

Nouble様
回答ありがとうございます。
ピボットは着想が至りませんでした…

思い起こしてみると、毎回、一念発起して
覚えては忘却していき、いつまでたっても
身につかない。なかなか難しいです。