プロが教える店舗&オフィスのセキュリティ対策術

複数の重複するデータを集計する関数もしくはマクロを教えて下さい。
集計まで出来なくても、同じ商品名を同じ行に表示させ、在庫数も一緒に移動させることは可能でしょうか?
IF(COUNTIF)関数で商品名の行を揃えることは出来ましたが、在庫数も一緒に移動させることは出来ませんでした。
関数の知識がなく、お力をお借り出来ればと思います。
どうかよろしくお願いします。

「Excel 複数列の集計」の質問画像

質問者からの補足コメント

  • ご指摘ありがとうございます。
    例題は簡単に書き換えていますが、本来のデータは商品名ではなく化学的な文字列が並びかなり長いのと
    データ数が1万件ほどあり、この2週間で地道に一列ずつ行を揃えるようにしていました。
    そこで、せめて横の行の同じ名前のものを並べられるように関数をい色々試してみましたが横の列がうまく取得できず困っていました。これまでにも関数が組めないところは手作業で行って集計をしていましたが、どうしてもミスが発生してしまいミスの確認にかなりの時間を割いていました。
    一足飛びに回答を得ようとする問いあわせとなったことをお詫びいたします。

    「Excel 複数列の集計」の補足画像1
    No.2の回答に寄せられた補足コメントです。 補足日時:2021/12/17 06:09
  • tatsumaru77さん

    不鮮明な画像をアップしていまい申し訳ありません。
    画像ありがとうございます。
    その通りです。

    どの店舗にも店舗内においては商品名に重複はありません。
    こちらは事前に重複を削除してあります。
    各店舗同士では同じ商品があったりなかったりします。
    商品の並びはソートで同じ条件で並び替えしています。

    No.7の回答に寄せられた補足コメントです。 補足日時:2021/12/18 14:04
教えて!goo グレード

A 回答 (10件)

No6です。

私の提示したレイアウト通りということなので、
以下のマクロを標準モジュールに登録してください。
集計前のシート:Sheet1
集計後のシート:Sheet2
になっています。あなたの環境にあわせて適切に設定してください。
集計後のシートの2行目から書き出します。1行目の見出しはあなたが
事前に設定しておいてください。
商品の重複はあってもなくても構いません。
(重複があれば合算します)

Option Explicit
Public Sub 複数列の集計()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim rg As Range
Dim ad As Variant
Dim maxrow As Long
Dim maxcol As Long
Dim row1 As Long
Dim row2 As Long
Dim col1 As Long
Dim col2 As Long
Dim key1 As Variant
Dim key2 As Variant
Dim tenNo As Long '店番号(0始まり)

Dim dicT1 As Object 'キー:商品名
Dim dicT2 As Object 'キー:店番号+商品名
Set dicT1 = CreateObject("Scripting.Dictionary") ' 連想配列の定義
Set dicT2 = CreateObject("Scripting.Dictionary") ' 連想配列の定義
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")
maxcol = ws1.Cells(2, Columns.Count).End(xlToLeft).Column '2行目の最終列を求める
If maxcol Mod 2 <> 0 Or maxcol < 2 Then
MsgBox ("最大列数不正:" & maxcol)
Exit Sub
End If
ws2.Rows("2:" & Rows.Count).ClearContents 'Sheet2クリア
'Sheet1集計
For col1 = 1 To maxcol - 1 Step 2
tenNo = (col1 - 1) \ 2
maxrow = ws1.Cells(Rows.Count, col1).End(xlUp).Row '該当列の最終行を求める
For row1 = 3 To maxrow
key1 = ws1.Cells(row1, col1).Value
key2 = tenNo & "|" & key1
dicT1(key1) = True
If dicT2.exists(key2) = False Then
dicT2(key2) = 0
End If
dicT2(key2) = dicT2(key2) + ws1.Cells(row1, col1 + 1).Value
Next
Next
'Sheet2へ出力
row2 = 2
'商品名出力
For Each key1 In dicT1.keys
ws2.Cells(row2, 1).Value = key1
row2 = row2 + 1
Next
'在庫数出力
For tenNo = 0 To (maxcol \ 2)
col2 = tenNo + 2
row2 = 2
For Each key1 In dicT1.keys
key2 = tenNo & "|" & key1
If dicT2.exists(key2) = True Then
ws2.Cells(row2, col2).Value = dicT2(key2)
End If
row2 = row2 + 1
Next
Next
MsgBox ("完了")
End Sub
    • good
    • 0
この回答へのお礼

tatsumaru77さん

マクロの登録で問題なく集計が出来ました!
また休みの時にマクロの意味を調べて理解していきます。
ありがとうございました!

お礼日時:2021/12/18 20:39

No5です。



>エラーメッセージが出てしまい思うように集計が出来ませんでした。
多分、UNIQUEやFILTER関数等が利用できない環境ではないかと想像します。
(回答にも書きましたが、Office365やExcel2021を対象としています)

そうでない場合は、No8様の回答にある方法が、トータルとして一番スマートではないかと思いますのでぜひお試しを。
    • good
    • 0
この回答へのお礼

fujillinさん

PCを調べてみたところ自宅のPCはOffice365やExcel2021ではありませんでした。職場のPCはOffice365ですので、今後のためにもまたそちらで試して学習していきたいと思います。ありがとうございます。

お礼日時:2021/12/18 14:42

以前、バイトしていたところで、同じニックネームの方に、大変、お世話になっていました。

別人だとは思うのですが、感謝を込めて回答させていただきます。

まずは、忠告です。
教えてGooでは、いろいろなアドバイスがいただけるので、回答内容を真似しただけで、2週間の仕事が2分で終わってしまったりします。
しかし、そうなった場合でも、その回答内容がきちんと理解できないうちは、誰にも言わずに今まで通り2週間かかった体で、仕事してください。
もし、「なんだ、2分で出来るのか」って、上司に思われてしまうと大変な目にあいますよ!!

さて、本題です。
最初に、各店舗の商品名を手作業でコピーして、1列に並べます(添付画像ではH列)。当然、重複した商品名も含めて、単純コピーでOKです。
その後、「データ」-「重複の削除」で商品名をユニーク化します。
ここまで出来てしまえば、後はユニークな商品名をキーにSUMIF関数で、値を集計するだけです。
添付画像では、A店に関する式の例だけを挙げていますが、B店以降も同じ要領なので、ご自分で設定してみて下さい。また、ゼロの場合は空白にする等、工夫してみてください。
「Excel 複数列の集計」の回答画像8
    • good
    • 0
この回答へのお礼

ママチャリさん

ニックネームのご縁に感謝です。
一気に集計出来なくても、関数を繰り返せば同一商品名に値をいれられました。回答内容を真似して出来ても今後を考えると困まりますよね。
簡単なところから積み重ねていきます。
ありがとうございました!

お礼日時:2021/12/18 14:34

No6です。


Sheet1,Sheet2の画像のURLが間違っていました。
下記が正しいです。
Sheet1
https://gyazo.com/b48931500b83bbf543391a973080a5ad
Sheet2
https://gyazo.com/12934f11c2a34444895e4c3c1bc4ac10
この回答への補足あり
    • good
    • 0

このサイトは画像が不鮮明なので、下記にアップしました。


以下のように集計すれば良いのでしょうか。
又、Sheet1,Sheet2のレイアウトはこれであってますか。
Sheet1:集計前のシート(あなたが提示した画像の左側)
https://gyazo.com/12934f11c2a34444895e4c3c1bc4ac10

Sheet2:集計後のシート(あなたが提示した画像の右側)
https://gyazo.com/b48931500b83bbf543391a973080a5ad

Sheet1の商品名はA列内で重複あり(その場合は在庫数を合算)、
同様にC、E、G列も重複あり。(黄色のセル)

Sheet2は、商品の重複をなくし、商品ごと、店ごとのまとめたもの。
黄色のセルは商品の重複があったため、合算した結果が格納されている。
    • good
    • 0

こんにちは



画像がよく見えないので、雰囲気しかわからないのですが、左側から右側にできればよいものと解釈しました。

商品名が一つの列内に重複があるのか不明ですが、重複があり得るものとし、合計を算出するものと仮定しました。
以下(=添付図)は原理のみなので、20行目までを対象にしていますが、必要な範囲に拡張すればそのまま使えるはずです。

・添付図では、画像の都合上同じシート内に表示しており、A~H列がご提示の左側、
 J~N列を右側を示すものとしています。
・式を簡単にするため、スピル機能が使えるバージョンを想定しています。
・原理のみなので、合計が0の場合には0表示となっています、
 (0を消す方法は何種類かありますので、検索すれば見つかると思います)


◇数量の合計を求める
J列に品名一覧があるとして、K~N列に数量の合計を求めます。
添付図ではK2セルに以下の式を入力し、N2までフィルコピーしています。
(下方にはスピル機能で、自動的に延長されます)
=IF($J2:$J20="","",SUMIF(OFFSET($A:$A,0,COLUMN(A1)*2-2),$J2:$J20,OFFSET($B:$B,0,COLUMN(A1)*2-2)))

・合計する必要がない(=列内に商品の重複がない)場合は、LOOKUP関数等で求める方が簡単かも知れません。
(エラー値(=該当なし)の場合に、空白にしておけば0表示を同時に消せます)


◇商品名一覧を求める
もしも、J列ができていない場合でこれも関数で求めたい場合、どこかの列に各店の商品名を並べておきます。
(添付図ではP列にそのままコピペしてあります)
この全体から「重複を省いた一覧」を作成すれば良いので、J2セルに以下の式を入力してあります。
=UNIQUE(FILTER(P2:P80,P2:P80<>"",""))

・上式の結果は出現順に並びますが、並べ替えて表示したいのなら、SORT関数でラップすることで昇順(降順)に並び替えることが可能です。


※ すみませんが、何度やってもなぜか画像の添付が受け付けられません。
(ので、あきらめました。わかりにくいですがご容赦ください。)
・A~H列にご提示の左側と似た様なものを作成し
・J~N列をご提示の右側のような表にしてあります
・J列は商品の一覧
・K~N列がA~D店の商品名に対応した合計値が集計されています。
    • good
    • 1
この回答へのお礼

fujillinさん

添付図とは私の添付した画像のことでしょうか?
ご説明分から作業をしてみたのですが、エラーメッセージが出てしまい
思うように集計が出来ませんでした。
丁寧にご説明いただいているにも関わらず、知識が追いついておらず申し訳ありません。。

お礼日時:2021/12/18 14:16

また直接の回答ではなく申仕訳ありませんが。



>一足飛びに回答を得ようとする問いあわせとなったことをお詫びいたします。

こう言ったサイトでは比較的良くある質問ですよ。
気にしなくて良いかと。
気に入らなければ回答しないってのが回答者の考えと思ってます。(放置プレイです)
ここもdポイント制に切り替われば、回答者も変わるかも知れないです。
知恵袋の知恵コインみたいに数十万枚ぽ~んと捨ててはID変更ってのも懐かしいです。
    • good
    • 0
この回答へのお礼

めぐみんさん

質問したことで逆に落ち込みかけていましたが、めぐみんさんのお言葉で救われました。ありがとうございます。

お礼日時:2021/12/18 13:31

最初から右の表をA列を並び替えておいて打ち込んでも良いような?



もしくは『どのようなデータが各店から集まるのか?』の情報によっては、個人的にはデータベースか経験ないけどパワークエリで#1さんの言う左の表の書き換えは可能に感じますけどね。
    • good
    • 0
この回答へのお礼

めぐみんさん

パワークエリという言葉さえ知りませんでした。
調べてみたところ使えそうな感じではありました。
ありがとうございます。

お礼日時:2021/12/18 13:25

関数やマクロの知識が無いのであれば、


とりあえず、ひとつずつ手作業で行う事になるかと思います。

・・・
ここは「代わりにやってください」や「代わりに作ってください」と作業依頼をする場所ではなく、
自力で問題を可決するためのアドバイスをもらうところです。

そんなわけで、今の質問者さんにできることは、セルの移動だけですね。
普通にセルをドラッグ&ドロップすると上書きになって、下になったセルのデータが消えてしまいますが、
Shiftキーを押しながら移動させたいセルをドラッグすると、移動先にセルを挿入し、下になったセルは自動的にずれてくれます。

コツを掴めばとても簡単にできるようになります。

たぶん、ここで数式を作ってくれる人を待つよりも早く処理が終わりますよ。

・・・
という感じに、少しずつ使い方を覚えていきましょう。
この回答への補足あり
    • good
    • 3
この回答へのお礼

銀輪さん

Shiftキーを押しながら操作で上書きされずに移動出来るんですね。
これは便利ですね。他にも色々あるようなので少しづつ覚えて活用していくようにします。
ありがとうございました!

お礼日時:2021/12/18 13:21

こんばんは。



直接の回答ではありませんが、
左の表ですが、A列に店舗名、B列に商品名、C列に個数に変更はできない
でしょうか?
それができれば、ピボットテーブルで右の様に並べ替えができるかと。

https://mainichi.doda.jp/article/2019/03/06/1479 …
    • good
    • 0
この回答へのお礼

早速のご回答ありがとうございました。
地道にやってみます!

お礼日時:2021/12/17 06:11

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

教えて!goo グレード

このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング