• ベストアンサー
  • 困ってます

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 わかりにくい図で申し訳ございません。 お手数をおかけしますが、ご教授の方をお願いできますでしょうか。 よろしくお願い致します。

共感・応援の気持ちを伝えよう!

  • 回答数4
  • 閲覧数258
  • ありがとう数10

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.2
  • FEX2053
  • ベストアンサー率37% (7926/21137)

COUNTIFS関数だけで済む話なんじゃないかと。 http://www.becoolusers.com/excel/countifs.html =COUNTIFS(A2:A50,"yamada10x",B2:B50,"Grp1") これでyamada10xのGrp1の件数合計が出てきますが?

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ご回答ありがとうございます。 =COUNTIFS関数で無事できました。 サイトの方もわかりやすく解説されていたので 助かりました。

関連する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 わかりにくい図と説明で申し訳ございません。 お手数をおかけしますが、ご教授の方をお願いできますでしょうか。 よろしくお願い致します。

  • 名前のローマ字表記

    皆さん自分の名前をローマ字で書くときどういう風に書きますか? 例、山田太郎 A)姓名略さないとき 1)Yamada Taro 2)YAMADA Taro 3)Taro YAMADA 4)Taro Yamada 5)それ以外 A)名を略すとき 1)YAMADA.T. 2)Yamada.T. 3)YAMADA T. 4)Yamada T. 5)T. YAMADA 6)T. Yamada 7)それ以外 太郎は「taro」「tarou」かよく分かりませんが、ここでは問題ではありません。 また綴り方はヘボン式、日本式、訓戒式などどれを使うか教えてくれるとうれしいです。

  • Documents and Settings がおかしくなりました。

    osは2000SP4、会社内LANですがドメインは使っていません。ワークグループです。 今日レイアウト変更して、同じハブ下のLANにつなぎ変えたところ、突然今までのユーザログインが出来なくなってしまいました。新品pcのデスクトップ画面です。 今までのユーザ名は「yamada」、コンピュータ名は「yamada3」としており、Documents and Settings(以下DSと書きます)内には\yamadaというフォルダに設定が記憶されていました。 pcがおかしくなってからDSをみると、\yamada.yamada3というフォルダが出来ていて、こちらの設定(初期設定)に支配されているようです。imeユーザ辞書もcokiee、ie6のお気に入り等も、\yamadaの方に残ったままです。 コンパネのユーザ&パスワードを見ると、administrator、guestとyamadaしかおりません。 pcログオン時のデフォルトユーザ名はyamadaままです。ログオンパスワードは今までのものが効きます。試しにログオン名をyamada\yamada3にすると、パスワード不明ではねられます。 セーフモードにするとようやく\DS\yamada.yamada3のフォルダは削除できたのですが、再起動すると同じようになります。 \DS内のフォルダは現在、\adminisitrator、\all users、\default user、そして\yamadaと\yamada.yamada3です。 いったい何がおこったのでしょうか? もとのyamadaユーザに戻す方法は、ありますでしょうか?

その他の回答 (3)

  • 回答No.4
  • 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

共感・感謝の気持ちを伝えよう!

  • 回答No.3
  • f_a_007
  • ベストアンサー率20% (955/4570)

訂正:求めるのが合計数ではないですね。 SELECT Count([A]) FROM [Sheet1] WHERE [A]='yamada10x' AND [B]='Grp1' UCount("A", "Sheet1", "[A]='yamada10x' AND [B]='GrpN'") どうしても、VBAで自作するなら USum() ではなく UCount() ですね。

共感・感謝の気持ちを伝えよう!

  • 回答No.1
  • f_a_007
  • ベストアンサー率20% (955/4570)

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

  • エクセルの関数を使った抽出方法

    エクセルの関数を使った抽出方法 お世話になります。Excelで下記のような例で同じ製品が複数存在するリストから 製品が重複しないようにリスト化することは可能でしょうか? 「重複の削除」を使わずに抽出したいです。 当方はオフィス2003使用です。 sheet1が日報でsheet2が請求書です。金額は抽出して合計しなくていいです。 あくまでも製品名だけをsheet2の請求書に抽出したいです。 何かよい方法をご存知の方がいらっしゃいましたらご指導願います。 よろしくお願いいたします。 【抽出前】sheet1 製品名   金額   A     ○円   B     ○円     B     ○円    B     ○円    C     ○円     C     ○円      D     ○円     E     ○円   E     ○円   F     ○円 【抽出後】sheet2 製品名   金額   A     ○円   B     ○円      C     ○円   D     ○円   E     ○円   F     ○円

  • エクセルの関数を使った抽出方法

    エクセルの関数を使った抽出方法 Excelで下記のような例で抽出は出来るでしょうか? 当方はオフィス2003使用です。 何かよい方法をご存知の方がいらっしゃいましたらご指導願います。 よろしくお願いいたします。 sheet2の製品名と製品番号はsheet1の日報か抽出して出しているので関数の数式が入ってます。 【抽出前】sheet2 製品名   製品番号   AL    1D8597   BL     6F1472    BL     7Y8654    BL     9P3256     CL     3K2145      CL     2Q6321       DL     8T4578     EL     5X7412   EL     4S9127   FL     7F2369   AL      8R8456 【抽出後】sheet3 製品名   製品番号   AL    1D8597        AL     8R8456        【抽出後】sheet4 製品名   製品番号 BL     6F1472  BL     7Y8654  BL     9P3256   【抽出後】sheet5 製品名   製品番号 CL     3K2145    CL     2Q6321     以下同じです。

  • Excel 検索値について

    どなたかご教授ください。宜しくお願い致します。 【Sheet1】 --------------------------------------------------   A   |   B  |  C   |  D(AとBを繋げた番号) -------------------------------------------------- 店舗番号 | 支店番号 |  売上  | 管理番号  -------------------------------------------------- 369    |   1  | 123,000 | 369-1 -------------------------------------------------- 258    |   1  |  45,000 | 258-1 -------------------------------------------------- 258    |   2  |  45,000 | 258-2 -------------------------------------------------- 258    |   3  |  8,000 | 258-3 -------------------------------------------------- 147    |   1  |  9,000 | 147-1 -------------------------------------------------- 147    |   2  |  1,000 | 147-2 -------------------------------------------------- 【Sheet2】 -----------------------------------------   A   |   B  |  C   |   -----------------------------------------  検索値 |  258  |      |   ----------------------------------------- 検索値に店舗番号「258」を入力し、「258-1~258-3」の 売上を下記のように表示させたいのですが、どうすれば 良いでしょうか。宜しくお願いします。 ↓ --------------------------      |  売上   | --------------------------      |  45,000  | ←258-1の売上 --------------------------      |  45,000  | ←258-2の売上 --------------------------      |  8,000   | ←258-3の売上 --------------------------      |        | --------------------------      |        | --------------------------  合計  |  98,000   | --------------------------

  • エクセルの関数を使った抽出方法

    エクセルの関数を使った抽出方法 Excelで下記のような例で抽出は出来るでしょうか? 当方はオフィス2003使用です。 何かよい方法をご存知の方がいらっしゃいましたらご指導願います。 よろしくお願いいたします。 【抽出前】sheet1 製品名   製品番号   AL    1D8597   BL     6F1472    BL     7Y8654    BL     9P3256     CL     3K2145      CL     2Q6321       DL     8T4578     EL     5X7412   EL     4S9127   FL     7F2369   AL      8R8456 【抽出後】sheet2 製品名   製品番号   AL    1D8597        AL     8R8456        【抽出後】sheet3 製品名   製品番号 BL     6F1472  BL     7Y8654  BL     9P3256   【抽出後】sheet4 製品名   製品番号 CL     3K2145    CL     2Q6321     以下同じです。      

  • エクセルのデータ検索&抽出作成について

    Excelの別シートでの検索と抽出について ExcelファイルでSheet1~5まで部署別の発注データで項目は下記の通りA列~G列まですべて同じになっているものがあります。 A列 B列 C列 D列 E列 F列 G列 注文番号 商品名 納品日 納品先 発注額 消費税 合計額 別シート(Sheet6)に「商品名」や「納品先」で検索する項目を作成し、その結果を表示できるようにしたいのですが何か良い方法はないでしょうか? マクロ・VBAでの作成方法もあればお願いします。 Excelのバージョンは2010です。 並べ替えとフィルター → 詳細設定 → フィルタオプションの設定 で試してみましたが、検索する商品名(納品場所)を変更してしまうと抽出されませんでした。 宜しくお願いします。

  • SQL文の書き方について

    お世話になります。下記のような結果を求めるSQL文についてどなたかご教示をお願いします。 以下の2つのテーブル(SMPL_TBL1,SMPL_TBL2)があったとします。 (SMPL_TBL1) ID | NAME | BUSHO ---+--------+--------- 01 | YAMADA | A_GRP 02 | SUZUKI | A_GRP 03 | OKADA | B_GRP 04 | TAKAGI | C_GRP (SMPL_TBL2) ID | POINT | ---+-------+- 01 | 1 | 01 | 2 | 01 | 3 | 02 | 1 | 02 | 2 | 03 | 1 | 04 | 1 | 04 | 2 | 04 | 3 | 04 | 4 | 04 | 5 | 上記テーブルから以下のような結果を得たい場合、 どのようなSQL文を投げればいいでしょうか? (求める結果) ・各人ごとのPOINTのMAX値と各人のデータを抽出(POINTは1~999までの整数です) ID | NAME | POINT| BUSHO ---+--------+------+-------- 01 | YAMADA | 3 | A_GRP 02 | SUZUKI | 2 | A_GRP 03 | OKADA | 1 | B_GRP 04 | TAKAGI | 5 | C_GRP よろしくお願いいたします。

  • エクセルで、条件にあう複数の行を別のシートに抽出する

    《どなたか、お教え下さい》   エクセルで、条件にあう複数の行を別のシートに抽出してまとめる。 「sheet1」の下記の基本データを「sheet2」に「得意先」ごと「日付」の早い順にまとめたいのでが。関数を使ってできないものか、どなたかお教え下さい。「マクロ」や「Access」は使用したことがないので、関数を使ってやりたいと思いますが、できるものでしょうか。よろしくお願いします。 sheet1 番号 得意先 日付 商品名 商品詳細 金額 備考 1   A 2   B 2   B 4   D 5   E 6   F 2   B 3   C 5   E   A    B  番号  得意先   1   A      2   B   得意先名は、番号を入力することで表示されるように   3   C   関数「VLOOKUP」を使って表示させるようにしていま   4   D   す。    5   E   6   F  

  • 複数のシートの合計

    3つシートがあります。 各シート、A列にA01,A02・・・B05・・・などの番号が入力されています。平均300行くらいです。 ただし、各シートの番号は全く同じではなく、例えばsheet1と3にはF45があってもsheet2にはない、といった感じです。 そして、このA列に対してB、C、D列にはカテゴリわけされて数字が入っています。 たとえばりんご、みかん、バナナとあり、A01のりんごは5、みかんは0、バナナは5個といった感じです。 このシートを1つに重複せずにまとめたいのです。 例えばsheet1-3のA02のりんごの合計は5、E06のバナナの合計は1といった感じです。 一度A列を別シートに全て貼り付け、フィルタオプションで重複を除き、その後、VLOOKUPで各シート抽出し、その合計を出す、とやっていたのですが、#N/Aが出てしまい、値に直そうにもフィルタがかかっていてさらに別シートに貼り付けなおして・・・とやっているのですが、あまりにも時間がかかり(同じようなファイルが50個近くある)、もっと効率のいい方法があったら教えてほしいです。

  • Excelで特定の行だけ別シートに抽出する方法

    sheet1に下記のリストを作ります。 番号  名前    郵便番号     住所    電話番号     会社名  部署 1    高橋    〒111-1111   東京都   00-0000-0000  ○×    人事 2    田中    〒222-2222   神奈川県  111-11-1111   △□   営業 3    佐藤    〒333-3333   埼玉県    22-2222-2222  ・・・・   ・・・・ sheet2のA1に番号を入力すると「1」とした場合   名前    郵便番号    住所     部署   高橋    〒111-11111  東京都   人事 というように該当の列の特定の列だけを表示させる方法はありますでしょうか? 自動で抽出したいのですが、なにとぞよろしくおねい致します。

  • EXCELの関数について

    質問なのですが下記の関数だとCommodity(Sheet2)のB2~B331で 該当(MATCH)する物の番号を抽出するのですが複数あっても一つ しか抽出されません。どうすれば複数抽出されますか?また抽出 されたときに1,2,3といった様に数字の若い順から表記したいです。 =MATCH(A2,Commodity!B$2:B$331,0)+1