- ベストアンサー
エクセルのマクロ【複数検索&検索結果の合計】
以下のように 【sheet1】にA列B列からなるデータにおいて A列の内容が同じものどうしのB列の値を合計し その結果の大きい順に並べて 【sheet2】に出力するマクロを 作りたいのですが、どうしたらよいかわかりません。 どなたかお分かりになる方いらっしゃいましたら 教えて頂きたくお願い致します。 【sheet1】 A列 B列 aaa 5 bbb 4 aaa 3 aaa 2 ccc 89 bbb 100 【sheet2】 A列 bbb 104 ccc 89 aaa 10
- altete
- お礼率100% (4/4)
- Visual Basic
- 回答数4
- ありがとう数4
- みんなの回答 (4)
- 専門家の回答
質問者が選んだベストアンサー
ピボットでできるけど。。。 VBA なら Dictionary を利用するとか? 以下は適当に書いたコード なので参考までに。 Sub SampleProc() Dim dic As Object ' Dictionary ' ※ Microsoft Scripting Runtime Dim r As Range Dim sKey As String Dim i As Long Dim n As Long Set dic = CreateObject("Scripting.Dictionary") ' // 集計 For i = 1 To 50 ' 1-50 行まで sKey = Trim$(Cells(i, "A")) Set r = Cells(i, "B") If sKey <> "" And IsNumeric(r.Value) Then If Not dic.Exists(sKey) Then ' // 新しい項目ならキーを追加 dic.Add Key:=sKey, Item:=r.Value Else ' // 既出項目ならキーの値と合計する ' // 少数がある場合は丸め誤差に注意 dic(sKey) = CDbl(dic(sKey)) + r.Value End If End If Next i ' // 結果出力 n = dic.Count If n > 0 Then ' // セルへ記入 Range("D1").Resize(n).Value = Application.Transpose(dic.Keys) Range("E1").Resize(n).Value = Application.Transpose(dic.Items) ' // 並べ替え Range("D1:E1").Resize(n).Sort Key1:=Range("E1"), _ Order1:=xlDescending Else MsgBox "集計できません", vbInformation End If Set r = Nothing Set dic = Nothing End Sub
その他の回答 (3)
- pauNed
- ベストアンサー率74% (129/173)
(#2コメントへのレスです) >どうしたらよいのでしょう。。。 えーと... そのコードのセルアドレスをよく確認してください。 ご自分の環境にあわせて変更する際には、内容を理解して修正しましょう。 >Key1:=.Range("I1") と、わざわざI列に修正しているという事は、 Consolidateの書き出し位置がH列I列という事ですね? であれば、Sortの対象範囲もそれにあわせて修正しなければいけませんよ? .Range("H1", .Range("H1").End(xlDown).Offset(0, 1)).Sort _ Key1:=.Range("I1"), _ Order1:=xlDescending, _ Header:=xlNo, _ OrderCustom:=1, _ Orientation:=xlTopToBottom [並び替え]の動作を[マクロの記録]を録ってみると参考になりますよ。
お礼
pauNedさん ありがとうございました! できました。
- imogasi
- ベストアンサー率27% (4737/17068)
データが変わった都度に瞬間的に処理しなければならない場合でなければ(、バッチ処理で良いなら) 判りやすいのは、下記と思います。古典的小計算出アルゴリズムです。 (1)A列でソートします。(もちろんA,B列を範囲指定して) コードはマクロの記録をとれば、4行ぐらいだったと思う。 データ範囲の最下行の捉え方は d=Range("A65536").End(xlUp).Row ソート範囲は Sub test01() d = Range("A65536").End(xlUp).Row MsgBox d Range("A2:B" & d).Select End Sub その後 Selection.Sort・・ ーー ソート後のデータについて 第1行のA列の値をーー>前行データとして記憶 (2)データの最上行行から、順次、A列データについて、前の行のデータと変わったか、IF文でで聞く (A)変わらない行は、合計(小計)にB列データを加える。 (B)変わったら、(A)の合計(小計)とA列直前行データをシート2のA,B列にそれぞれ書きだす。 合計(小計)を0に(ご破算)する。 今のB列現在行データを加える。 現在行のA列の値をーー>前行データとして記憶 次行の処理へ (C)最終行になったら、合計(小計)と最後のA列データをシート2に書き出す。 さあVBAコードが組めるかな。
お礼
imogasiさん ありがとうございました! できました。
- pauNed
- ベストアンサー率74% (129/173)
こんにちは。 [データ]-[統合]機能を使う手もありますね。 Dim r As Range With Sheets("sheet1") Set r = .Range("B1", .Range("A1").End(xlDown)) End With With Sheets("sheet2") .Range("A1").Consolidate _ Sources:=r.Address(ReferenceStyle:=xlR1C1, External:=True), _ Function:=xlSum, _ LeftColumn:=True .Range("B1", .Range("A1").End(xlDown)).Sort _ Key1:=.Range("B1"), _ Order1:=xlDescending, _ Header:=xlNo, _ OrderCustom:=1, _ Orientation:=xlTopToBottom End With Set r = Nothing
お礼
pauNedさん Sheet2に合計がきちんと出力されました! 有難うございます。 が、実行すると、 ***************************************************** .Range("B1", .Range("B1").End(xlDown)).Sort _ Key1:=.Range("I1"), _ Order1:=xlDescending, _ Header:=xlNo, _ OrderCustom:=1, _ Orientation:=xlTopToBottom ***************************************************** の部分が黄色くなり 『実行時エラー'1004'』 並べ替えの参照が正しくありません。 並べ替えるデータ内にあることこ、 [最優先されるキー]ボックスが空白でないことを確認してください。 というエラーメッセージがでます。 度々すみません。 どうしたらよいのでしょう。。。
関連するQ&A
- エクセルで特定の数値の合計を出したいと思ってます
エクセルで特定の数値の合計を出したいと思ってます 初心者のため、わかりづらい表現があるかと思いますが、ご了承頂ければと思います。 以下のようなテーブルがあります。 A___|_ _ __B_ _ _________|_ _C_ _| 12/1| aa/taro/ccc |1540 | 12/1| bbb/hanako/ccc |1 | 12/1 | cc/taro/ddd |1540 | 12/1 | aa/yuka/ccc |6 | 12/1 | aaa/taro/bbb |209 | 12/2 | bb/taro/bbb |224 | 12/2 | cc/yuko/ccc |127 | 12/2 | bb/taro/aaa |127 | 12/2| aa/hanako/ccc |6 | 12/3| aa/taro/ccc |127 | 12/3| bbb/taro/ccc |253 | 12/3| aa/yuko/bbb |253 | 12/3| cc/hanako/ccc |16 | 12/3| aa/yuko/dd |1 | 12/3| bb/taro/ccc |16 | 12/3| aa/taro/c |6 | ~~~~~~~~~~~~~~~~~~~~~~~~~ <列の説明> A:日付 B:URL C:数値 <今回行いたいこと> 同一の日付(列A)内の行の中でURL(列B)に含まれる特定の文字を含む、数値(列C)の合計を出したいと思ってます。 例としては、 12/1のtaroを含むURLの合計値 C1+C3+C5=1540+1540+209=3289 このシートのデータは約20000件くらいありますが、最終的には下記のようなシートを作れればと思ってます。 URLにtaroを含むDailyの合計値 日付__|_合計値_| 12/1 | 455 | 12/2 | 3322 | 12/3 | 5439 | 12/4 | 2764 | 非常にわかりづらい表現で申し訳ございませんが、どなたかお力を頂ければと思います。 よろしくお願い致します。
- ベストアンサー
- その他MS Office製品
- EXCELで複数行の検索
初心者です。 Aファイルのaaa、bbbなどをキーに Bファイルを検索してできるだけ簡単にマクロ等使わずに 下記のような抽出をする方法があれば教えてください。 よろしくお願いします。 Aファイル aaa bbb ccc Bファイル(例えば「ddd」と「あ」は別セルです) ddd あ eee い aaa う fff え bbb お ggg か ccc き 結果 Aファイル aaa う bbb お ccc き
- ベストアンサー
- オフィス系ソフト
- Excelシートの統合するマクロなどがありましたらお教えください。
各支店(約200)からExcelシートで送信されてくる報告内容を別のシートに一気にまとめるマクロ等はないでしょうか?ご存知の方よろしくお願いします。 (例) A支店 項目 aaa bbb ccc ddd eee 10 20 30 0 0 8 8 0 0 0 以下約300件 B支店 項目 aaa bbb ccc ddd eee 11 22 33 0 0 9 9 0 0 0 以下約300件 別シートに 項目 aaa bbb ccc ddd eee 10 20 30 0 0 8 8 0 0 0 11 22 33 0 0 9 9 0 0 0 以下省略 よろしくお願いします
- ベストアンサー
- オフィス系ソフト
- Excel2002:複数条件のデータの個数の集計
苦手な集計について質問させてください。 [A] [B] [1] AAA aaa [2] BBB bbb [3] AAA aaa [4] AAA bbb [5] BBB ccc 上記のようなデータで[A]と[B]のAND条件でみたときの個数を集計したいと思います。 例えば上記であれば、 [A]AAAかつ[B]aaa・・・2個 [A]BBBかつ[B]bbb・・・1個 [A]AAAかつ[B]bbb・・・1個 [A]BBBかつ[B]ccc・・・1個 になります。[B]のデータは[A]のデータに依存せず、[A]の各値にまたがっています。([B]bbbは[A]AAAと[A]BBBの場合があります) 実際のシートでは、全パターンを網羅したリストがA列とB列、集計対象がD列とE列にあります。 上記の例でいえば、以下のようになっています。 [A] [B] [C] [D] [E] [1] AAA aaa AAA aaa [2] AAA bbb BBB bbb [3] BBB bbb AAA aaa [4] BBB ccc AAA bbb [5] BBB ccc つまり[A]列と[B]列が組み合わせパターン、[D]列と[E]列が集計元データです。 ちなみにパターン数が414個、集計元データは29000個程度です。 そして最終的な各パターンの個数を[C]列に表示させたいと思います。 VLOOKUPやピボットテーブルを考えましたが、ピンと来ません。 適切なやり方を教えてください。よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- マクロの作り方について
お世話になっております。 エクセルで次のようなテーブルの自動生成マクロを作成したいと考えておりますが、なかなかうまくいきません。 皆さまのお知恵を拝借いたしたく、お願いいたします。 「Data」シート、「Table」シートの2つのシートをエクセルに用意します。 「Data」シートには、200行2列のデータが予め入っています。 1列目にはaaa.F、aaa.W、aaa.R、bbb.F、bbb.W、bbb.Rといった形の文字列が延々と入っています。 2列目には数字のデータが入っています。 行いたいのは、まず1列目のデータを「.」で区切って、2つの配列「st」と「pr」に入れなおすことです。 ただ、単にsplitするだけですと、1列目のデータを分けた際に、 st=(aaa、aaa、aaa、bbb、bbb、bbb) pr=(F、W、R、F、W、R) といった形で同じ値が格納されますが、ダブっている文字ははじく様にしたいのです。 このため、例えばstについて、次のようなマクロを考えたのですが、これだとstをdebug.Print等で出力した際、一番最後のデータだけしか格納されていませんでした。 Dim C As Variant C = Worksheets("DATA").Range("A1:B65000") Dim D As Variant Dim i As Integer Dim st As Variant For i = 1 To 10 D = Split(C(i, 1), ".") If st <> D(0) Then st = D(0) Debug.Print (st) ↑ここでstを出力している分には、aaa、bbb、cccとダブった文字を省いたすべてのデータが出力されます。 Else End If Next Debug.Print (st) ↑ここでstを出力すると、最後のデータだけしか出力することができません。 st(3)等、特定の要素を出力しようとしてもできないようです。 stをvariant型にしていることが問題なのかもしれませんが、stをstring型のarrayで定義し、各要素ごとに入力すると、st(0)=aaa、st(1)=aaaといった形で同じ値のものも入ってしまいます。 異なった文字列だけ各要素に入力していくにはどのようにマクロを組めばよいのでしょうか?
- ベストアンサー
- Visual Basic
- vbs csv編集 プログラム
vbsで以下のように編集したいです。 <編集前> A B C 1 aaa@aaa.jp bbb@bbb.jp ccc@ccc.jp 2 bbb@bbb.jp ccc@ccc.jp 3 ccc@ccc.jp <編集後> A B C D 1 aaa@aaa.jp aaa@aaa.jp bbb@bbb.jp ccc@ccc.jp 2 bbb@bbb.jp bbb@bbb.jp ccc@ccc.jp 3 ccc@ccc.jp ccc@ccc.jp <編集後> (1)A列に値がある場合は、その値をA列にコピーする。 (2)A列に値がなく、B列に値がある場合は、その値をA列にコピーする。 (3)B列に値がなく、C列に値がある場合は、その値をA列のコピーする ※A、B、C列すべてに値がある場合は、A列の値をA列(編集後)にコピーする。 上記可能でしたら、コードのご教示お願いいたします。
- ベストアンサー
- Visual Basic
- エクセル 複数データ 抽出
エクセルの複数データの抽出で困っています。 助けてください。 以下の様なシートで別シートから複数のデータを取り出したいのですが・・・・ 他の方の質問を参照したのですがうまく行かなく 皆さんの知識を教えてください。 シート1 テーブル A列 B列 C列 D列 グループ メーカ 部品 数量 1 A社 AAA 1 1 B社 BBB 2 1 C社 CCC 4 2 F社 ZZZ 3 2 S社 HHH 2 3 以下も同様 3 4 5 5 5 シート2 摘出を考えているのですが シート1のA列グループを選択すると全て摘出する様にしたいです。 シート2 グループ選択 「1」 「2」 を選択すると 1と2が全て摘出される 1 A社 AAA 1 1 B社 BBB 2 1 C社 CCC 4 2 F社 ZZZ 3 2 S社 HHH 2 選択は1と3 2と4 1と5 とか結構なパターンがあります。 VLOOKUP、その他の方の回答例も使用してみましたがうまく出来ませんでした。 関数、マクロ問いませんので どうぞ、お力をお貸し願います。
- ベストアンサー
- その他MS Office製品
- Excelでお尋ねしたい事があります。
エクセルのデータを下記のようにするのに 良い方法があれば教えて下さい。 A B A B C D 1 AAA 100 1 AAA 100 101 102 2 AAA 101 ⇒ 2 BBB 100 101 102 3 AAA 102 3 CCC 100 101 102 4 BBB 100 5 BBB 101 6 BBB 102 7 CCC 100 8 CCC 101 9 CCC 102 A列にある同一のデータを一行にして B列のデータを一行にした列に持っていく作業です。 現在は、A列にある同一データのB列をコピー。 行列を入れ替えて貼り付け。 B列が空いた所を行事削除しています。 このデータが何千とありまして… 延々とコピー、貼り付け、削除。 をしています。 何か良い方法があれば教えて下さい。 よろしくお願いします。
- 締切済み
- Windows XP
- (EXCEL)重複したデーターの抽出について
EXCEL2003 シート「sheet1」に下記のようなデーターがあります。 <sheet1> A B C D 1 りんご 青森 AAA BBB 2 みかん 愛媛 CCC DDD 3 みかん 青森 AAA BBB 4 りんご 青森 AAA BBB 5 みかん 愛媛 CCC DDD A列とB列が同じ値のものをシート「sheet2」に重複しているデーター は1行だけになり、E列にカウントした数が入力されるようにしたいです。 (C列やD列は同じ値でなくてもA列とB列が同じ値の時にカウントして抽出したいです) <sheet2> A B C D E りんご 青森 AAA BBB 2 みかん 愛媛 CCC DDD 2 ご教示のほどお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセルのマクロについて
エクセル97のマクロについて教えてください。 下のようなエクセルシートがあると仮定します。 このシート全体を選択して用意してあるボタンを押すと、マクロが流れるようにします。 マクロの中身は、項目1~4の値が同じであれば、同じデータを一つにするというものを考えています。 たとえば下の例の場合、追番でいえば2と4のデータは同じなので、マクロ処理にかけると、追番の大きい4のデータは消え、2のデータの"200"項目にフラグ1が追加されるようにしたいのです。 項目の値が同じであれば、いくつでもデータを統一したいと考えています。 ================ エクセル シート例 ================================== 追番_項目1_項目2_項目3_項目4_100_200_300_400 ← 見出し ----------------------------------------------------------------- 1****AAA****BBB****CCC****DDD****1*********1****1**** 2****EEE****FFF****CCC****GGG****1**************1**** 3****HHH****FFF****KKK****JJJ****1****1****1********* 4****EEE****FFF****CCC****GGG****1****1*********1**** ↓ マクロ処理後 追番_項目1_項目2_項目3_項目4_100_200_300_400 ← 見出し ----------------------------------------------------------------- 1****AAA****BBB****CCC****DDD****1*********1****1**** 2****EEE****FFF****CCC****GGG****1****1*********1**** 3****HHH****FFF****KKK****JJJ****1****1****1********* =================================================================== 注:見出しの_とデータの中の*は空白を生めるためのもので、データとはまったく 関係ありません。 なにぶんエクセルVBAは初心者同然なもので... よろしくお願い致します。
- 締切済み
- オフィス系ソフト
お礼
KenKen_SPさん 有難うございました! できました!