• 締切済み

なぜない？フィルタの使えるCOUNTIF関数

VBA初心者です。 エクセルでフィルターをかけた上で、特定のデータを数えたいと思うのですが・・・。 例 A　　　　　　　　B 東京都　　　　● 神奈川県　　 ▲ 東京都　　　　▲ 愛知県　　　　● 静岡県　　　　● 東京都　　　　▲ このようにデータが入力されているとして、A列でオートフィルターをかけてA列が「東京都」でB列が「●」のセルをカウントしたいとします。 作業列を使ったり、SUMPRODUCT関数を使う方法もありますが、他の方に教えて頂き以下のようなユーザー定義関数を使っております。 Function AAA(myRange As Range, myStr As Variant) As Long 　Dim Rng As Range 　Dim Cnt As Long 　For Each Rng In myRange 　　If Rng.EntireRow.Hidden = False And Rng.Value = myStr Then 　　　　Cnt = Cnt + 1 　　End If 　Next Rng 　AAA = Cnt End Function これで確かに希望通りの動作にはなるのですが、他のマクロを動作させるとエラーになってしまうケースが多いようです。 エラーになると、セル内の表示は「######」になってしまい、何らかの原因で非常に桁数の大きな結果が返っているのかと思いましたが、そうでもないようです。 この状態になっても、別セルに1つデータを入れたりするとまた正常に戻ったりして、ちょっと原因が掴めない状態でいます。 しかし疑問に思っているのは、フィルターの使えるCOUNTIF関数は、非常に需要が高いように思うのですが、なぜEXCELにはこういう関数が標準で存在していないのでしょうか？ 何か理由をご存じの方いらっしゃいますか？

みんなの回答

  • keithin
  • ベストアンサー率66% (5278/7940)
回答No.3

＞A列が「東京都」でB列が「●」のセルをカウントしたい エクセル２００７以降で実装された、COUNTIFS関数を使って計算できます。 また、ＡでもＢでもない別列でオートフィルタしたという状況でも、そのオートフィルタの条件を同様にCOUNTIFS関数に組み込んで計算する事で、できます。 ＃一応２００３以前のエクセルでも、「Ａ列東京都」「Ｂ列○」を共にオートフィルタで絞り込み、SUBTOTAL関数を３で使うなどの方法で「オートフィルタの絞り込み結果を数える」ことはできました。 ご質問の直接の回答として ＞なぜEXCELにはこういう関数が標準で存在していないのでしょうか？ 基本的には、エクセルでは「１つの何か」を計算する関数が主だったからです。 ○条件に合致する「１つの結果」を計算する検索系の関数 ○「１つの条件」に合致するものを計算するCOUNTIFやSUMIF関数 例外的に配列の計算結果を算出するFREQUENCYとかLINESTといったものもありましたが、一般的な関数ではありません。 複数条件の計算ができるCOUNTIFS,SUMIFS等の関数は、長く求められていましたがようやく実装されました。今後も、たとえば「条件に合致する複数の計算結果を返す検索系の関数」などが追加されると嬉しいと思います。

yasupon2
質問者

お礼

ご回答ありがとうございます。 COUNTIFSという関数があるのですね。 勉強不足でした。 これから研究してみようと思います。 ただ、これだとA列に例えば全都道府県が存在すると、数を表示する列も47個必要になりそうですね。 できれば同じセルにその都度フィルターで抽出した対象の数を表示したいのですが…。 もう少し考えてみます。

  • bunjii
  • ベストアンサー率43% (3589/8248)
回答No.2

>作業列を使ったり、SUMPRODUCT関数を使う方法もありますが、他の方に教えて頂き以下のようなユーザー定義関数を使っております。 配列演算を利用してIF関数とCOUNT関数の組み合わせで目的が達成できます。 =COUNT(IF((A$2:A$7=A2)*(B$2:B$7=B2),ROW($2:$7),"")) IF関数の返り値を配列とするため演算式を入力後、Ctrl+Shift+Enterで確定します。

画像を拡大する
yasupon2
質問者

お礼

ご回答ありがとうございます。 確かにこの方法でも解答No.1の方に教えていただいたCOUNTIFS関数と同じ結果が出せるのですね。 しかもEXCEL2003でも使えるみたいです。

  • mshr1962
  • ベストアンサー率39% (7418/18948)
回答No.1

＞なぜない？フィルタの使えるCOUNTIF関数 SUBTOTAL関数があるからでは？ オートフィルタで条件を追加しなけりゃいけないので複数の結果の同時表示は出来ませんけど 複数の結果が必要な場合は、フィルタでなくピボットテーブルを使えということなのでしょう。 ・SUBTOTAL関数 http://exinfo.biz/func/func_subtotal.shtml ・ピボットテーブル http://hamachan.info/excel/piboto.html

yasupon2
質問者

お礼

ご回答ありがとうございます。 入力されているデータの種類が少なければビボットテーブルで一覧にしても良いのですが…。 対象になるデータ数が多くなると、どうしても見にくくなってしまい、何とか1つのセルに都度フィルターで抽出した結果を表示したいのです。 B列の条件がなければSUBTOTALでOKなのですが、もう少し考えてみることにします。

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. Excel（エクセル）

関連するQ&A

  • ユーザー定義関数の再計算

    ユーザー定義関数を作りました。 ところが、この関数が自動再計算をしてくれません。 どうしたら自動再計算するようになるのでしょうか？ よろしくお願いします。 ちなにこの関数は、自分のシートのB2とsheet1～sheet4のB9を比較して、正しければB9の４つ右のセルの値を合計して返すものです。 =SheetLook($B$2,"sheet1,sheet2,sheet3,sheet4",B9,4) コードです。 Function SheetLook(参照元 As Variant, 比較対象シート As String, 比較対象セル As Range, 参照セル位置 As Integer) As Variant 　　Dim i As Integer 　　Dim rng As Range 　　Dim sss As Variant 　　Dim kei As Variant 　　Dim cnt As Integer 　　sss = Split(比較対象シート, ",") 　　kei = 0 　　cnt = 0 　　For i = 0 To UBound(sss) 　　　　Set rng = Sheets(sss(i)).Range(比較対象セル.Address) 　　　　If 参照元 = rng Then 　　　　　　kei = kei + rng.Offset(0, 参照セル位置) 　　　　　　cnt = cnt + 1 　　　　End If 　　Next 　　If cnt <> 0 Then 　　　　SheetLook = kei 　　Else 　　　　SheetLook = "" 　　End If End Function

  • EXCEL　Spirit関数　繰り返し処理

    EXCEL VBAでテキストファイルを読み込み、Spirit関数にて項目内容を書き出しています。 下記のコードの日付部分は1日～31日まで繰り返しになります。 Dim maxRow As Integer Dim maxCol As Integer Dim i As Long Dim mytxtfile As String, myStr As String Dim myRange As Range Dim mySp() As String Dim sh1 As Worksheet On Error GoTo myError mytxtfile = "D:\明細.TXT" Set sh1 = Worksheets("元データ") Set myRange = sh1.Range("A1") sh1.Cells.Clear Open mytxtfile For Input As #1 Do Until EOF(1) Line Input #1, myStr myRange.Offset(i, 0).Value = myStr myStr = Replace(myStr, """", "") mySp = Split(myStr, ",") myRange.Offset(i, 1).Value = mySp(0) '卸名 myRange.Offset(i, 2).Value = mySp(1) 'コード myRange.Offset(i, 3).Value = mySp(2) 'フラグ myRange.Offset(i, 4).Value = mySp(6) '納品先 myRange.Offset(i, 5).Value = mySp(7) '店舗 myRange.Offset(i, 6).Value = mySp(23) '日付 ---------ここから myRange.Offset(i, 7).Value = mySp(26) '1日_金額A myRange.Offset(i, 8).Value = mySp(27) '1日_金額B myRange.Offset(i, 9).Value = mySp(34) '1日_金額C --------ここまでが 1日分です。これが31日まで続きます。 i = i + 1 Loop Close #1 '31日までのデータが無い場合のエラーを無視する（データが存在する日まで書き出す） myError: Resume Next sh1.Range("A:A").Delete 'txt取込作業列を削除 End Sub カンマ区切りの位置は、15ずつ増えますので 繰り返し処理したいのですが、うまくいきません。 最初は、金額A だけの書き出しだったため、 1行ずつ書いていたのですが、金額B、Cの列も必要になりました。 for　next　で　すっきりした書き方を教えていただけないでしょうか？ 宜しくお願い致します。

  • Excelで同一セル内に入力されているデータを他のセルに分割したい

    http://okwave.jp/qa4369634.html?ans_count_asc=20 で質問をして、何度かやりとりをさせていただいて エクセルで同一セル内に、セル内改行で１～６列ほどのデータが入力されています。 縦にデータが入力されていて、それぞれのセルにセル内改行を含み、データが入力されています。 それぞれのセル内のデータを… 例えば、A1セル内に５行入力されていたら、A2セルから入力されている行数分（ここでいうと5行）挿入し、それぞれにデータを分割して入力させたい。 かつ、B・Cセルは増えたセルにそれぞれのデータをコピーしたいと言ったら、 Sub Macro1() Dim idx, cnt As Integer Dim wkStr() As String Dim rng As Range 　　ActiveSheet.Copy after:=ActiveSheet 　　For idx = Range("A65536").End(xlUp).Row To 1 Step -1 　　　　If InStr(Cells(idx, "A"), Chr(10)) > 0 Then 　　　　　　wkStr = Split(Cells(idx, "A").Value, Chr(10)) 　　　　　　Set rng = Cells(idx, "B") 　　　　　　For cnt = UBound(wkStr) To 0 Step -1 　　　　　　　　Cells(idx, "A").Value = wkStr(cnt) 　　　　　　　　Cells(idx, "B").Value = rng.Value 　　　　　　　　Cells(idx, "C").Value = rng.Offset(0, 1).Value 　　　　　　　　If cnt > 0 Then 　　　　　　　　　　Cells(idx, "A").Resize(1, 3).Insert shift:=xlDown 　　　　　　　　End If 　　　　　　Next cnt 　　　　End If 　　Next idx End Sub といったマクロのご回答をいただきました。 これを元に、 ・データが入っているセルをA列→B列に変更 ・A列のデータはセルが増えた分だけ増やしたい ・A1に対応するデータがC1・D1に入っていた場合、対応するデータは残したまま、B列が増えただけ、列を増やしたい と変更したいのですが…。 すいませんが、宜しくお願い致します。

  • シート内セルに条件付着色でエラーメッセージ

    Excelのチェックボタンをクリックしたときにシート１のセル"C4:G50"内に条件付書式により着色（ColorIndex =7）されたセルがあった場合、エラーメッセージ（"　ヶ所　日付が入力されていません"）を表示したいのですが？ 下記のコードでセルに直接着色（"C7"）されたものは添付のようにメッセージが出たのですが条件付書式による着色がカウントしメッセージが出るようにしたいのですが、コード表示が解る方どうかよろしくお願いします。 尚、C列とG列のみ50行まで条件下で着色するよう同じ条件付書式が入っています。 Sub チェック() Dim CheckRange As Range Dim rng As Range Dim cnt As Long Set CheckRange = Range("C4:G50") For Each rng In CheckRange If rng.Interior.ColorIndex = 7 Then cnt = cnt + 1 End If Next If cnt > 0 Then MsgBox cnt & "ヶ所、日付が入力されていません。", vbCritical Exit Sub End If Worksheets("sheet1").Range("D1") = "1" End Sub

  • 【Excel VBA】チェックボックスの挿入位置

    Excel2003を使用しています。 Sheet2のN1セルに入力されている番号と同じ番号が入力されているセルをSheet1のA列(A11:A200)から探して、その行のB列にチェックボックスを挿入したく、下記のようにコードを書いてみましたが、チェックボックスの挿入と挿入位置等(？部分)をどのように書いたらいいのかわかりません。 ---------------------------------------- Sub test1() Dim myStr As String Dim myRange As Range myStr = Sheets("Sheet2").Range("N1").Value Set myRange = Sheets("Sheet1").Range("A11:A200").Find(myStr) If myRange Is Nothing Then Exit Sub Else 　'？←この部分がわかりません…。 End If End Sub ---------------------------------------- 実際にチェックボックスを挿入してマクロの記録もとってみたのですが、あまり参考にすることができず、質問させていただいた次第です。 チェックボックスは、コントロールツールボックスのチェックボックスを使用したいのですが…。 よろしくお願いします。

  • 色のついたセルと文字を両方数えられるVBA

    VBAの初心者です。 エクセルで文字の色でカウントする為に以下のVBAをこちらで教えていただきました。 順調に快適にエクセルを使えていたたのですが今回同じエクセル内で 文字ではなくセルに色付けしたものもカウントしなければならなくなりました。 以下のVBAがあるからかよく言われている4.0マクロ関数ではうまくいきません。 以下のVBAに何かを足せば一方では文字色をカウントし 一方ではセル色をカウントすると言う事は可能でしょうか？ どなたか力を貸してください Function CCount(Rng As Range, idx) Dim R As Range Dim Cnt As Long Application.Volatile For Each R In Rng     If R.Font.ColorIndex = idx Then Cnt = Cnt + 1 Next R CCount = Cnt End Function

  • ユーザー定義関数で辞書のような使用法

    Excel2000ユーザー定義関数で、 参照された文字列を、任意のフォルダ内にあるエクセルファイルから、 Vlookup関数のような形で引っ張ってくることは出来るでしょうか？ どうしても、ファイルパスを指定出来ません。 ｅx) Function kanjiname(セル) Application.Volatile Dim myRange As Range Set myRange = Workbooks("C:\辞書ファイル.xls").Worksheets("Sheet1").Range("C1:C9") kanjiname = Application.VLookup(セル, myRange, 5, False) という感じにしたいのです。 開いていないファイルをＯｐｅｎしても無理ですし、 どうすればいいのでしょうか。 End Function

  • VBA?　色のついた文字のセルを数えたい

    色のついた文字の記載があるセルをカウントしたく 色々調べました。結局VBAで設定する方法にしたのですが 設定しテストをするとどうしてもカウント数が合いません。 全くの初心者の為何が間違っているのか全く分かりません。 どなたか教えて下さい。 VBAも全く知らない者でしたので 調べて以下のものをそのまま貼り付けました。 Function CCount(Rng As Range, idx) Dim R As Range Dim Cnt As Long Application.Volatile For Each R In Rng 　　If R.Font.ColorIndex = idx Then Cnt = Cnt + 1 Next R CCount = Cnt End Function Function GetIndx(Rng As Range) If Rng.Count > 1 Then 　　GetIndx = vbNullString 　　Exit Function End If GetIndx = Rng.Font.ColorIndex End Function 何が間違っているのでしょうか？

  • 色付セルの数

    こんにちは、詳しい方よろしくお願いします。 添付画像の表内赤色セルの数の合計を表したいのですが、０になってしまいます。 VBA Function CountColorA(Rng As Range) As Long Dim myRng As Range Dim Col_cnt As Long Application.Volatile Col_cnt = 0 For Each myRng In Rng If myRng.Interior.ColorIndex > 0 Then Col_cnt = Col_cnt + 1 End If Next myRng CountColorA = Col_cnt End Function 数を表示させたいセルに =CountColorA（D3:D29）としています。 どこが間違っているかわかる方、よろしくお願いします。 勉強不足ですみません。

  • CollectionとArrayの呼び出し順

    こんばんは。 いつも勉強させてもらっています。 ご教授ください。 セル範囲Ａ１：Ｂ３に適当な値を入れ 下記のコードを実行してみました。 ----------------------------------------------- Sub Test() 　　Dim Rng As Range 　　Dim myRange As Range 　　Set myRange = Range("A1").CurrentRegion 　　　　For Each Rng In myRange 　　　　　　Debug.Print "Collection: " & Rng.Value 　　　　Next Rng 　　Dim Ary 　　Dim myArray 　　 　　myArray = Range("A1").CurrentRegion.Value 　　　　For Each Ary In myArray 　　　　　　Debug.Print "Array: " & Ary 　　　　Next Ary End Sub ----------------------------------------------- その結果、 Collectionの場合は、　A1>B1 >A2>B2 >A3>B3 Arrayの場合は、　　　A1>A2>A3 >B1>B2>B3 の順で呼び出されました。 なぜこのような違いがでるのか分かりません。 教えてください。 宜しくお願いします。 　　

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する