No.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
tatsumaru77さん
マクロの登録で問題なく集計が出来ました!
また休みの時にマクロの意味を調べて理解していきます。
ありがとうございました!
No.9
- 回答日時:
No5です。
>エラーメッセージが出てしまい思うように集計が出来ませんでした。
多分、UNIQUEやFILTER関数等が利用できない環境ではないかと想像します。
(回答にも書きましたが、Office365やExcel2021を対象としています)
そうでない場合は、No8様の回答にある方法が、トータルとして一番スマートではないかと思いますのでぜひお試しを。
fujillinさん
PCを調べてみたところ自宅のPCはOffice365やExcel2021ではありませんでした。職場のPCはOffice365ですので、今後のためにもまたそちらで試して学習していきたいと思います。ありがとうございます。
No.8
- 回答日時:
以前、バイトしていたところで、同じニックネームの方に、大変、お世話になっていました。
別人だとは思うのですが、感謝を込めて回答させていただきます。まずは、忠告です。
教えてGooでは、いろいろなアドバイスがいただけるので、回答内容を真似しただけで、2週間の仕事が2分で終わってしまったりします。
しかし、そうなった場合でも、その回答内容がきちんと理解できないうちは、誰にも言わずに今まで通り2週間かかった体で、仕事してください。
もし、「なんだ、2分で出来るのか」って、上司に思われてしまうと大変な目にあいますよ!!
さて、本題です。
最初に、各店舗の商品名を手作業でコピーして、1列に並べます(添付画像ではH列)。当然、重複した商品名も含めて、単純コピーでOKです。
その後、「データ」-「重複の削除」で商品名をユニーク化します。
ここまで出来てしまえば、後はユニークな商品名をキーにSUMIF関数で、値を集計するだけです。
添付画像では、A店に関する式の例だけを挙げていますが、B店以降も同じ要領なので、ご自分で設定してみて下さい。また、ゼロの場合は空白にする等、工夫してみてください。
ママチャリさん
ニックネームのご縁に感謝です。
一気に集計出来なくても、関数を繰り返せば同一商品名に値をいれられました。回答内容を真似して出来ても今後を考えると困まりますよね。
簡単なところから積み重ねていきます。
ありがとうございました!
No.7
- 回答日時:
No6です。
Sheet1,Sheet2の画像のURLが間違っていました。
下記が正しいです。
Sheet1
https://gyazo.com/b48931500b83bbf543391a973080a5ad
Sheet2
https://gyazo.com/12934f11c2a34444895e4c3c1bc4ac10
No.6
- 回答日時:
このサイトは画像が不鮮明なので、下記にアップしました。
以下のように集計すれば良いのでしょうか。
又、Sheet1,Sheet2のレイアウトはこれであってますか。
Sheet1:集計前のシート(あなたが提示した画像の左側)
https://gyazo.com/12934f11c2a34444895e4c3c1bc4ac10
Sheet2:集計後のシート(あなたが提示した画像の右側)
https://gyazo.com/b48931500b83bbf543391a973080a5ad
Sheet1の商品名はA列内で重複あり(その場合は在庫数を合算)、
同様にC、E、G列も重複あり。(黄色のセル)
Sheet2は、商品の重複をなくし、商品ごと、店ごとのまとめたもの。
黄色のセルは商品の重複があったため、合算した結果が格納されている。
No.5
- 回答日時:
こんにちは
画像がよく見えないので、雰囲気しかわからないのですが、左側から右側にできればよいものと解釈しました。
商品名が一つの列内に重複があるのか不明ですが、重複があり得るものとし、合計を算出するものと仮定しました。
以下(=添付図)は原理のみなので、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店の商品名に対応した合計値が集計されています。
fujillinさん
添付図とは私の添付した画像のことでしょうか?
ご説明分から作業をしてみたのですが、エラーメッセージが出てしまい
思うように集計が出来ませんでした。
丁寧にご説明いただいているにも関わらず、知識が追いついておらず申し訳ありません。。
No.4
- 回答日時:
また直接の回答ではなく申仕訳ありませんが。
>一足飛びに回答を得ようとする問いあわせとなったことをお詫びいたします。
こう言ったサイトでは比較的良くある質問ですよ。
気にしなくて良いかと。
気に入らなければ回答しないってのが回答者の考えと思ってます。(放置プレイです)
ここもdポイント制に切り替われば、回答者も変わるかも知れないです。
知恵袋の知恵コインみたいに数十万枚ぽ~んと捨ててはID変更ってのも懐かしいです。
No.2
- 回答日時:
関数やマクロの知識が無いのであれば、
とりあえず、ひとつずつ手作業で行う事になるかと思います。
・・・
ここは「代わりにやってください」や「代わりに作ってください」と作業依頼をする場所ではなく、
自力で問題を可決するためのアドバイスをもらうところです。
そんなわけで、今の質問者さんにできることは、セルの移動だけですね。
普通にセルをドラッグ&ドロップすると上書きになって、下になったセルのデータが消えてしまいますが、
Shiftキーを押しながら移動させたいセルをドラッグすると、移動先にセルを挿入し、下になったセルは自動的にずれてくれます。
コツを掴めばとても簡単にできるようになります。
たぶん、ここで数式を作ってくれる人を待つよりも早く処理が終わりますよ。
・・・
という感じに、少しずつ使い方を覚えていきましょう。
銀輪さん
Shiftキーを押しながら操作で上書きされずに移動出来るんですね。
これは便利ですね。他にも色々あるようなので少しづつ覚えて活用していくようにします。
ありがとうございました!
No.1
- 回答日時:
こんばんは。
直接の回答ではありませんが、
左の表ですが、A列に店舗名、B列に商品名、C列に個数に変更はできない
でしょうか?
それができれば、ピボットテーブルで右の様に並べ替えができるかと。
https://mainichi.doda.jp/article/2019/03/06/1479 …
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
- Excel(エクセル) Excel、同じフォルダ内のExcelファイルの特定シートのみを1つのファイルに集約したい 8 2022/09/07 15:12
- メルカリ メルカリShopsを作るとメルカリで販売は楽になりますか メルカリとまったく変わりませんか? 2 2022/10/11 19:22
- 会計ソフト・業務用ソフト Excelで売上げデータの中の任意の商品の合計を出したい 3 2023/01/18 18:19
- Excel(エクセル) Excelの計算式についてお教えください 8 2023/06/05 10:01
- Excel(エクセル) Excelの関数について 3 2022/11/13 23:47
- Visual Basic(VBA) 【VBA】複数行あるカンマ区切りのデータを全て縦に一列に並べたい 5 2022/04/13 17:03
- Visual Basic(VBA) VBA Userformで一部別シートに転記がしたいのですが 2 2023/05/24 13:08
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- その他(IT・Webサービス) 高速処理可能な表計算ソフトについて ExcelやGoogleスプレッドシートのような表計算ソフトで、 2 2023/04/29 16:06
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excel ピボットテーブルで日付...
-
ピボットテーブルのことです
-
ピボットテーブルへの集計フィ...
-
エクセルで○や×の図形の集計は...
-
Microsoft Formsによるアンケー...
-
パワーポイントで資料を作る時 ...
-
エクセルのピポットテーブルで...
-
エクセルの集計を数字以外です...
-
オートシェイプを色別に個数を...
-
マクロで貼り付け位置を可変さ...
-
ファイルメーカーpro6 チェッ...
-
エクセルのピポットテーブルを...
-
エクセルの表で集計するには
-
エクセルの最大行数を超えるデータ
-
Excelのピポットテーブルでクロ...
-
[エクセル]クロス集計っていう...
-
エクセルで数値のプラス毎とマ...
-
ファイルメーカーの質問(後部...
-
VBA 担当者別 日別 処理別 ...
-
エクセルの表の集計をVBAでやり...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ピボットテーブルのことです
-
エクセルのピポットテーブルで...
-
マクロで貼り付け位置を可変さ...
-
エクセルで○や×の図形の集計は...
-
ピボットテーブルの項目間の計算
-
エクセルの集計を数字以外です...
-
IF関数を使用した数字に、カン...
-
オートシェイプを色別に個数を...
-
パワーポイントで資料を作る時 ...
-
勤務表の中抜け集計の関数を教...
-
Microsoft Formsによるアンケー...
-
列を増やさずに、月だけの件数...
-
ピボットテーブルへの集計フィ...
-
エクセルの集計機能を横方向(...
-
Excel週ごとの集計を関数で
-
保存ブックを開かずコピーペー...
-
エクセルを使ってCSVデータを自...
-
エクセルで数値のプラス毎とマ...
-
ピボットを使ったシートに計算...
-
ピボットテーブル オリジナル...
おすすめ情報
ご指摘ありがとうございます。
例題は簡単に書き換えていますが、本来のデータは商品名ではなく化学的な文字列が並びかなり長いのと
データ数が1万件ほどあり、この2週間で地道に一列ずつ行を揃えるようにしていました。
そこで、せめて横の行の同じ名前のものを並べられるように関数をい色々試してみましたが横の列がうまく取得できず困っていました。これまでにも関数が組めないところは手作業で行って集計をしていましたが、どうしてもミスが発生してしまいミスの確認にかなりの時間を割いていました。
一足飛びに回答を得ようとする問いあわせとなったことをお詫びいたします。
tatsumaru77さん
不鮮明な画像をアップしていまい申し訳ありません。
画像ありがとうございます。
その通りです。
どの店舗にも店舗内においては商品名に重複はありません。
こちらは事前に重複を削除してあります。
各店舗同士では同じ商品があったりなかったりします。
商品の並びはソートで同じ条件で並び替えしています。