Excel VBA 検索してその合計数を抽出
- Excel VBAを使って特定の条件で検索し、合計数を抽出する方法について教えてください。
- Excel VBAで特定のシートにあるデータを検索し、それに対応する合計数を別のシートに抽出する方法を教えてください。
- Excel VBAのマクロを使用して、指定した条件で検索したグループごとの合計数を抽出する方法を教えてください。
- ベストアンサー
Excel VBA 検索してその合計数を抽出
はじめまして、業務用で下記の用にExcel VBABasicでマクロを 組みたいのですが、組み方の方がわかりません。 是非、ご教授願えませんでしょうか。 Ms Oficeは2010です。 検索したいsheet1には ユーザの名前とGrp番号などがあります。 A B C D E F G 1 Username Grp番号 2 yamada10x Grp1 3 yamada4x Grp1 4 yamada10x Grp1 5 yamada10x Grp1 6 yamada4x Grp2 7 yamada10x Grp2 8 yamada4x Grp2 9 yamada10x Grp3 . . 50 yamada4x grp40 Sheet2にはyamada10xやyamada4xの合計数とGrp番号などがあります A B C D E F 1 yamada10xの合計数 Grp番号 yamada4xの合計数 Grp番号 2 3 4 5 sheet1で検索したGrp番号などの合計数を下記の用にGrp番号にはGrp1などを抽出 そのGrp番号に合ったyamada10xやyamada4xの合計数などをsheet2に抽出させたいのですが A B C D E F 1 yamada10xの合計数 Grp番号 yamada4xの合計数 Grp番号 2 3 Grp1 1 Grp1 3 1 Grp2 2 Grp2 4 1 Grp3 . . 10 1 Grp40 わかりにくい図で申し訳ございません。 お手数をおかけしますが、ご教授の方をお願いできますでしょうか。 よろしくお願い致します。
- hazama12
- お礼率50% (2/4)
- Visual Basic
- 回答数4
- ありがとう数10
- みんなの回答 (4)
- 専門家の回答
質問者が選んだベストアンサー
COUNTIFS関数だけで済む話なんじゃないかと。 http://www.becoolusers.com/excel/countifs.html =COUNTIFS(A2:A50,"yamada10x",B2:B50,"Grp1") これでyamada10xのGrp1の件数合計が出てきますが?
その他の回答 (3)
- tom04
- ベストアンサー率49% (2537/5117)
こんばんは! COUNTIFS関数で対応できそうですが、VBAでの方法をご希望というコトですので 一例です。 Sheet3を作業用のSheetとして使用していますので、Sheet3は使用していない状態にしておいてください。 Sheet1のA列データが何種類あっても対応できるようにしてみました。 標準モジュールです。 Sub Sample1() Dim i As Long, k As Long, lastRow As Long, wS2 As Worksheet, wS3 As Worksheet Set wS2 = Worksheets("Sheet2") Set wS3 = Worksheets("Sheet3") Application.ScreenUpdating = False wS2.Cells.Clear With Worksheets("Sheet1") lastRow = .Cells(Rows.Count, "A").End(xlUp).Row .Range("A:A").AdvancedFilter Action:=xlFilterCopy, copytorange:=wS3.Range("A1"), unique:=True For i = 2 To wS3.Cells(Rows.Count, "A").End(xlUp).Row .Range("A1").AutoFilter field:=1, Criteria1:=wS3.Cells(i, "A") wS2.Cells(1, (i - 1) * 2 - 1) = wS3.Cells(i, "A") & "の合計数" wS2.Cells(1, (i - 1) * 2) = "Grp番号" Range(.Cells(2, "B"), .Cells(lastRow, "B")).SpecialCells(xlCellTypeVisible).Copy wS2.Cells(2, (i - 1) * 2) For k = wS2.Cells(Rows.Count, (i - 1) * 2).End(xlUp).Row To 2 Step -1 wS2.Cells(k, (i - 1) * 2 - 1) = WorksheetFunction.CountIfs(.Range("A:A"), wS3.Cells(i, "A"), _ .Range("B:B"), wS2.Cells(k, (i - 1) * 2)) If WorksheetFunction.CountIf(wS2.Columns((i - 1) * 2), wS2.Cells(k, (i - 1) * 2)) > 1 Then wS2.Cells(k, (i - 1) * 2 - 1).Resize(, 2).Delete shift:=xlUp End If Next k Next i wS2.Columns.AutoFit wS2.Range("A1").CurrentRegion.Borders.LineStyle = xlContinuous wS3.Cells.Clear .AutoFilterMode = False End With Application.ScreenUpdating = True End Sub こんな感じではどうでしょうか?m(_ _)m
- 山田 太郎(@f_a_007)
- ベストアンサー率20% (955/4574)
訂正:求めるのが合計数ではないですね。 SELECT Count([A]) FROM [Sheet1] WHERE [A]='yamada10x' AND [B]='Grp1' UCount("A", "Sheet1", "[A]='yamada10x' AND [B]='GrpN'") どうしても、VBAで自作するなら USum() ではなく UCount() ですね。
- 山田 太郎(@f_a_007)
- ベストアンサー率20% (955/4574)
SELECT Sum([A]) FROM [Sheet1] WHERE [A]='yamada10x' AND [B]='Grp1' SELECT Sum([B]) FROM [Sheet1] WHERE [A]='yamada10x' AND [B]='Grp1' Sheet2の合計の求め方は、このようかと思います。これを集計関数にすれば・・・ USum("A", "Sheet1", "[A]='yamada10x' AND [B]='Grp1'") USum("A", "Sheet1", "[A]='yamada10x' AND [B]='Grp2'") ・・・・・ USum("A", "Sheet1", "[A]='yamada10x' AND [B]='GrpN'") こういうことを実現したいとなると課題はUSum関数の書き方。 Public Function USum(ByVal strCellName As String, ByVal strSheetName As String, ByVal strWhereCondition As String) As Integer ・・・・・ End Function 確かに、USum関数をExcelに書き込む必要がありますが、何をしているかは一目瞭然。と、こんな考え方もあります。 【質問者の自前のアイデアとニーズの提示を】 話を本題に戻せば、VBAとなるとやり方は100人100様。ですから、質問者は、どのようなVBAを構想し、どこが書けないのかを提示する必要があるかと思いますよ。 PS、そもそも USum関数を自作する必要があるのか? EXCELは、集計ソフト。USum()程度のことはEXCEL関数でできませんか? ※EXCEL VBA も EXCEL も無知な私。”補足質問はなし”ですよ。
お礼
ご回答ありがとうございます。 参考にしていきたいと思います 自前のアイデアとニーズの提示がやはり足りなかった という事ですので、次回からは詳しく提示できるように したいと思います。
関連するQ&A
- VBA 検索をかけ合計数とGrp番号を抽出
現在、関数ではなく VBAでマクロを勉強しているのですが、 下記のコードでエラーが発生してしまいます。 是非、ご教授願えませんでしょうか。 Sheet3を作業用のSheetとして使用しています。 Sub Sample1() Dim i As Long, k As Long, lastRow As Long, wS2 As Worksheet, wS3 As Worksheet Set wS2 = Worksheets("Sheet2") Set wS3 = Worksheets("Sheet3") Application.ScreenUpdating = False With Worksheets("Sheet1") lastRow = .Cells(Rows.Count, "A").End(xlUp).Row .Range("A:A").AdvancedFilter Action:=xlFilterCopy, copytorange:=wS3.Range("A1"), unique:=True For i = 2 To wS3.Cells(Rows.Count, "B").End(xlUp).Row .Range("B1").AutoFilter field:=1, Criteria1:=wS3.Cells(i, "B") Range(.Cells(3, "B"), .Cells(lastRow, "B")).SpecialCells(xlCellTypeVisible).Copy wS2.Cells(3, (i - 1) * 2) For k = wS2.Cells(Rows.Count, (i - 1) * 2).End(xlUp).Row To 2 Step -1 wS2.Cells(k, (i - 1) * 2 - 1) = WorksheetFunction.CountIfs(.Range("A:A"), wS3.Cells(i, "A"), _ .Range("C:C"), wS2.Cells(k, (i - 1) * 2)) For j = 2 To wS3.Cells(Rows.Count, "E").End(xlUp).Row .Range("E1").AutoFilter field:=1, Criteria1:=wS3.Cells(j, "E") Range(.Cells(3, "E"), .Cells(lastRow, "E")).SpecialCells(xlCellTypeVisible).Copy wS2.Cells(3, (j - 1) * 2) For l = wS2.Cells(Rows.Count, (j - 1) * 2).End(xlUp).Row To 2 Step -1 wS2.Cells(j, (j - 1) * 2 - 1) = WorksheetFunction.CountIfs(.Range("A:A"), wS3.Cells(j, "A"), _ .Range("E:E"), wS2.Cells(k, (j - 1) * 2)) If WorksheetFunction.CountIf(wS2.Columns((j - 1) * 2), wS2.Cells(k, (j - 1) * 2)) > 1 Then wS2.Cells(l, (j - 1) * 2 - 1).Resize(, 2).Delete shift:=xlUp End If Next k Next i wS2.Range("A1").CurrentRegion.Borders.LineStyle = xlContinuous .AutoFilterMode = False End With Application.ScreenUpdating = True End Sub 実際に抽出結果を出したい概要は下記に用になります。 検索したいsheet1には セルBにはUsername番号などがあります。 A B C D E F G 1 Username Grp番号 2 yamada10x Grp1 3 yamada4x Grp1 4 yamada10x Grp1 5 yamada10x Grp1 6 yamada4x Grp2 7 yamada10x Grp2 8 yamada4x Grp2 9 yamada10x Grp3 . . 50 yamada4x Grp40 Sheet2にはセルBとCにyamada10xの合計数とgrp番号、セルEとFにはyamada4xの合計数とgrp番号などがあります。 A B C D E F 1 2 yamada10xの合計数 Grp番号 yamada4xの合計数 Grp番号 3 4 5 . . 10 sheet1で検索したユーザ名・Grp番号などを行数3のセルC・FにはGrp番号を抽出 行数3のセルB・EにはGrp番号ごとのyamada10xとyamada4xの合計数をsheet2に 抽出させたいという形になります。 A B C D E F 1 2 yamada10xの合計数 Grp番号 yamada4xの合計数 Grp番号 3 3 Grp1 1 Grp1 4 1 Grp2 2 Grp2 5 1 Grp3 . . 10 1 Grp40 わかりにくい図と説明で申し訳ございません。 お手数をおかけしますが、ご教授の方をお願いできますでしょうか。 よろしくお願い致します。
- ベストアンサー
- Visual Basic
- Excel VBA 検索して該当行を抽出
はじめまして、下記のように、Excelでマクロを組みたいのですが 組み方がわかりません。 ご教授願えませんでしょうか。 MS Ofiice2010 生徒数500名ほど シート1には生徒の生徒番号、氏名などがあります。 A B C D 1 生徒番号 氏名 備考 2 120001 田中 3 120002 山田 試験時休み 4 T120009 相田 転入 シート2には生徒の成績表:生徒番号、氏名、国語、算数、理科、社会 生徒番号でソートされていません。 A B C D E F 1 生徒番号 氏名 国語 算数 理科 社会 2 120001 田中 80 65 65 75 3 T120009 相田 90 85 80 80 シート1の生徒番号でシート2生徒番号を検索して、該当したら成績を シート1の検索した生徒番号のD列以降にコピーしたいのですが A B C D E F G 1 生徒番号 氏名 備考 国語 算数 理科 社会 2 120001 田中 80 65 65 75 3 120002 山田 試験時休み 4 5 10 T120009 相田 転入 90 85 80 80 お手数ですが、ご教授願えますでしょうか。 よろしくお願いいたします。
- ベストアンサー
- その他MS Office製品
- エクセル 名簿以外のを抽出するには
エクセルについて教えてください。 元データシートがあります。データの行列は結構な数が入っています。 A B C D E F G ・・・・・ 1 東京 山田 小計 ・・・ ・・・ ・・・ ・・・ ・・・・・ 2 埼玉 菊池 小計 3 栃木 中山 平均 4 茨城 岸本 小計 5 東京 村井 合計 といった具合にいくつものデータがあります。 抽出データシートに「名簿シート以外の人物」でなおかつ「元データのC列が"小計"と記載されているもの」だけを抜き出したいのです。 名簿シート(今後増減あり) A B 1 埼玉 菊池 2 東京 山田 3 ・・・ ・・・ ・ ・ 抽出データシート A B C D E F G ・・・・・ 1 茨城 岸本 小計 2 ・・・ ・・・ 小計 3 ・・・ ・・・ 小計 ・ 関数で可能でしょうか? 難しいなら、せめて、名簿以外の人物だけを抽出したいです。 また、元データや名簿が更新されるごとに抽出データが自動的に「名簿以外の人物」と 「C列が小計」のものだけを抽出したいのですが、関数以外に何かありますでしょうか? よろしくお願いいたします。
- 締切済み
- その他(インターネット・Webサービス)
- EXCELでの合計
今シート1に名前と作業と時間がかかれています。 これをシート2にそれぞれ合計させたいです。 sumif関数を使おうと思ったのですが、条件が1つしか使えないので 何かいい方法はありますか?よろしくお願いします。 シート1 B C D 2 鈴木 清掃 1.0 3 山田 水替 2.0 4 鈴木 営業 3.0 5 岡 清掃 2.0 6 鈴木 清掃 3.0 7 山田 営業 1.0 シート2 A B C D E 2 清掃 水替 営業 合計 3 鈴木 4.0 0.0 3.0 7.0 4 山田 0.0 2.0 1.0 3.0 5 岡 2.0 0.0 0.0 2.0 6 合計 6.0 2.0 4.0 12.0 シート1は次々に項目が増えていきます。 また、人名、作業項目は増えません。 合計はsum関数でできますが、それぞれの人と作業の 時間の合計をしたいです。 よろしくお願いします。
- 締切済み
- オフィス系ソフト
- エクセルで複数シートから、検索して抽出
はじめまして、右も左も分からない初心者です。 説明出来ないんですが、よろしくお願いします。 【ご質問内容】 エクセルは、XPを使用しています。 エクセルのVBAで、指定したキーワードを 含むデータを抽出したいと思っています。 【例】 Sheet1 A B C D E 1 番号 氏名 郵便番号 住所 メールアドレス 2 1 ○ △ 大阪府 ○○ 3 2 △ ○ 茨城県 ○○ Sheet2 A B C D E 1 氏名 番号 郵便番号 住所 メールアドレス 2 ○ 1 △ 福岡県 ○○ 3 △ 3 ○ 茨城県 ○○ と各シートに、列がそれぞれバラバラになっています。 茨城県を検索すると、新しいシートに、 Sheet3 A B C D E 1 氏名 番号 郵便番号 住所 メールアドレス 2 2 △ ○ 茨城県 ○○ 3 A B C D E 4 氏名 番号 郵便番号 住所 メールアドレス 5 △ 3 ○ 茨城県 ○○ という風に、抽出したいと思っています。 どうしたらいいでしょうか?? よろしくお願いします。
- 締切済み
- オフィス系ソフト
- エクセル データの抽出。延べ数を構成する個の数を求める。
エクセル初心者です。 エクセルでに下のように500人ほど人名(a b c d e f・・・)が無作為乱雑に列挙されています。 c d d c c b b a a d d d a a b b c c a a b b c c c c c c a c c c d e e e d d e e e・・・500人分 のべ500人のうち、一度でも出現していた人名を上手く抽出できますでしょうか? 最終的に求めているのは、一度でも出現していた人名の「数」ではなく、「一度でも出現していた人名それ自体」を新しいシートなりで利用したいのです。 ご教示願います。
- ベストアンサー
- オフィス系ソフト
- エクセルで検索と抽出
マイクロソフトエクセル2003で Aさん 100 1月 Bさん 101 Cさん 102 1月 Dさん 103 Fさん 104 1月 から抽出 Aさん 100 Cさん 102 Dさん 103 とういうデーターから1月と記入がある人だけど別のシートの抽出したいのですが何か良い関数またはマクロなどありましたらご指導お願いします。
- 締切済み
- その他(業務ソフトウェア)
- エクセル 時間の合計数が適正に表示されません
添付のようなタイムシートを作成しましたが、「合計の時間数」が適正に表示されません。 D12、E12には正しくない合計時間数が表示され、 F12、G12に至っては、表示すらされません(TT) 間違っているのではなく、適正な表示になってくれていないだけだろうなとは理解できるのですが なぜこんな表示になってしまうのか、まったくわかりません。 どなたかご教示いただけないでしょうか。 よろしくお願いいたしますm(_ _)m 各列の関数は以下のとおりです。 D列=C-B-J E列=MIN("8:00",D) F列=D-K G列=C-I 合計のセルは次のように設定しています。 D12=SUM(D5:D11) E12=SUM(E5:E11) F12=SUM(F5:F11) G12=SUM(G5:G11) 各セルの表示形式は、 [h]:mm;; です。 よろしくお願いいたしますm(_ _)m
- ベストアンサー
- Excel(エクセル)
- エクセルの関数を使った抽出方法
エクセルの関数を使った抽出方法 お世話になります。Excelで下記のような例で同じ製品が複数存在するリストから 製品が重複しないようにリスト化することは可能でしょうか? 「重複の削除」を使わずに抽出したいです。 当方はオフィス2003使用です。 sheet1が日報でsheet2が請求書です。金額は抽出して合計しなくていいです。 あくまでも製品名だけをsheet2の請求書に抽出したいです。 何かよい方法をご存知の方がいらっしゃいましたらご指導願います。 よろしくお願いいたします。 【抽出前】sheet1 製品名 金額 A ○円 B ○円 B ○円 B ○円 C ○円 C ○円 D ○円 E ○円 E ○円 F ○円 【抽出後】sheet2 製品名 金額 A ○円 B ○円 C ○円 D ○円 E ○円 F ○円
- ベストアンサー
- 財務・会計・経理
- エクセル抽出に関して教えて下さい。
エクセル初心者なんですが、抽出について教えて下さい! 例 シート(1) A B C D E 1 8 2 ■ シート(2) A B C D E 1 4 5 6 7 8 2 1 1 3 7 2 3 5 6 7 1 2 シート(1)のB2に シート(1)のA1に入った数字を シート(2)のA1~E1の中で探して 該当した数字の列の3行目をシート(1)のB2に表示させる。 すみません、意味不明だったらごめんなさい。 ご理解いただける方、よろしくお願い致します。
- 締切済み
- オフィス系ソフト
お礼
ご回答ありがとうございます。 =COUNTIFS関数で無事できました。 サイトの方もわかりやすく解説されていたので 助かりました。