スキルアップ・問題解決なら、モーグにおまかせ！

エクセルのカウント、抽出方法について
 
＜ご質問１＞
1つのセル内に、255文字以上入力された(電話対応結果等)15000行位のデータに対して、複数のキーワードのいずれかに該当したものの件数をカウントしたいと考えております。
 
＜セルの例＞※セルA1内(セル内のデータは、アルファベット大文字・半角。カタカナ半角で統一済み)
 
A1セル
WI-FI接続できない。配線抜け。D-ONUﾘｾｯﾄ。WIFIﾙｰﾀﾘｾｯﾄ。
 
〜 A15000セル（上記の様な内容が複数のセル内にある）
 
＜エクセル式の例＞
 
=COUNTIFS(A1:A15000,"*WI*FI*")+COUNTIFS(A1:A15000,"*D*ONU*")+COUNTIFS(A1:A15000,"*ﾘｾｯﾄ*")
 
この様な式でカウントすると、1セル内で複数のキーワードに一致すると、した分だけカウントされてしまうかと思います。が、そうではなく、複数のキーワードのどれに該当したとしても、1セルに対するカウントは1としたいです。
 
期待する結果（例）→15000セル内で500件(セル)が該当
 
お手数ですが、ご教示をお願いいたします。
 
＜ご質問２＞
 
上記に該当したデータ（セル）を抽出する方法も知りたいです。
 
（指定したセルに条件式等を入れて表示 or (一番理想)マクロボタンを押すと別シートに該当するセルの一覧が表示等）
 
＜私のPC環境＞
 
エクセル2016、アクセス2016がPCに入っています。
 
エクセルの条件式が理想ですが、一応VBAもWEB上の記述式を張り付けて動かした事があります。一からは作れませんが、エクセルVBAに貼り付け・部分的に修正して動かす事は可能だと思われます。
 
＜ご参考＞
 
Chat GPTに試しにこちらの質問を貼り付けると以下の回答がありましたが、内容の解読が足りないためか上手くいきませんでした。
 
もし、解読できる方はこちらの内容での活用の仕方をご教示頂けると助かります。
 
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
 
＜ご質問１＞ 複数のキーワードに該当した場合でも、1セルに対するカウントを1とする方法は、以下のような式を使用することで実現できます。
 
=SUMPRODUCT(--(LEN(A1:A15000)-LEN(SUBSTITUTE(LOWER(A1:A15000),"キーワード1",""))>0),
--(LEN(A1:A15000)-LEN(SUBSTITUTE(LOWER(A1:A15000),"キーワード2",""))>0),
...)
 
この式では、キーワード1、キーワード2、...の部分に該当するキーワードを入力します。式内のA1:A15000
はデータが入っている範囲を指定するため、必要に応じて修正してください。この式を適用すると、複数のキーワードに該当する場合でも、1セルに対してカウントは1となります。
 
＜ご質問２＞ 該当したセルを抽出する方法については、いくつかのアプローチがあります。
 
1.条件付き書式を使用する方法:
- データの範囲を選択します。
- [ホーム]タブの[スタイル]グループ内にある[条件付き書式]を選択します。
- [新しいルール]をクリックし、[数式を使って書式設定する]を選択します。
- 数式フィールドに以下の式を入力します（A1はデータの最初のセルを指定してください）:
 
=AND(ISNUMBER(SEARCH("キーワード1",A1)), ISNUMBER(SEARCH("キーワード2",A1)), ...)
 
- 条件を満たした場合の書式を設定し、OKボタンをクリックします。
 
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
 
以上 お手数ですが、何卒 ご教示をお願いいたします。

フィルタオプション(Advanced Filter)を使った例を示します。
 

<<Sheet1>>データ（A列）および抽出条件（C列）  B列は空白列としてください。
     A列                                                         B列     C列
 1   内容                                                                内容
 2   WI-FI接続できない。配線抜け。D-ONUﾘｾｯﾄ。WIFIﾙｰﾀﾘｾｯﾄ。               *WI*FI*
 3   WI-FI接続できない。配線抜け。                                       *D*ONU*
 4   WI-FI接続できない。                                                 *ﾘｾｯﾄ*
 5   D-ONUﾘｾｯﾄ                                                            ↑OR条件
 6   WIFIﾙｰﾀﾘｾｯﾄ。                                                         個数は不問
 7   全角カタカナ。リセットを使ったデータがあるとする。（非抽出） 
 8   半角小文字英数はどう? wi-fi（大文字小文字は同一視され抽出）
 9   ＷＩＦＩ（これも抽出されない。こういうデータは無いそうだ。） 

<<Sheet2>>処理前
     A列                                                 B列  C列
 1   内容                                                                        
 2 

<<Sheet2>>処理後
     A列                                               B列   C列
 1   内容                                                    8セル内で 6件(セル)が該当
 2   WI-FI接続できない。配線抜け。D-ONUﾘｾｯﾄ。WIFIﾙｰﾀﾘｾｯﾄ。
 3   WI-FI接続できない。配線抜け。
 4   WI-FI接続できない。
 5   D-ONUﾘｾｯﾄ
 6   WIFIﾙｰﾀﾘｾｯﾄ。
 7   半角小文字英数はどう? wi-fi（大文字小文字は同一視され抽出）

Sub main()
    Dim ws1      As Worksheet
    Dim ws2      As Worksheet
    Dim rng      As Range
    Dim cnts     As Long
    Dim hitCnts  As Long
    Dim t
    t = Timer - t
    
    Set ws1 = Worksheets("Sheet1")  ' ■必要に応じてシート名を修正
    Set ws2 = Worksheets("Sheet2")  ' ■同上
    
    '対象範囲
    Set rng = ws1.Range("A1", ws1.Cells(Rows.Count, "A").End(xlUp))
    
    'フィルタオプション(Advanced Filter)の実行
    rng.AdvancedFilter _
            Action:=xlFilterCopy, _
            CriteriaRange:=ws1.Range("C1").CurrentRegion, _
            CopyToRange:=ws2.Range("A1"), Unique:=False

    cnts = Application.CountA(rng) - 1
    hitCnts = ws2.Cells(Rows.Count, "A").End(xlUp).Row - 1
    ws2.Range("C1") = cnts & "セル内で " & hitCnts & "件(セル)が該当"

    Application.Goto ws2.Range("A1"), True
    MsgBox "抽出終了　" & Format(Timer - t, "0.00") & "秒"
End Sub

>=COUNTIFS(A1:A15000,"*WI*FI*")+COUNTIFS(A1:A15000,"*D*ONU*")+COUNTIFS(A1:A15000,"*ﾘｾｯﾄ*")>>
  
この数式は、重複してカウントされます。
折角のCOUNTIFS・・・・・・使い方間違っています。
HELPで勉強しましょう。
どこが間違っているかChat GPTに教えてもらったら・・・
  
Chat GPTの結果が、意に沿わないときは、質問の仕方が悪いと考えた方がよいでしょう。
若しくは、使い物にならないかも・・・
 

他にもご回答ありましたら、何卒　ご教示をお願い致します。

他の回答を要望する前に言うことはないんですか？
回答をどのように受け止めたのですか？
こちらは質問者さんの直面する問題に対応できるようにと回答をしています。
回答を確認されたのなら、その結果を書いて下さい。
確認できない事情があれば、その旨を知らせてください。
相手はMougGPTではなく、生きている人間です。
人としての最低限のマナーは守りましょうよ。

simple さんの引用:
他の回答を要望する前に言うことはないんですか？
回答をどのように受け止めたのですか？
こちらは質問者さんの直面する問題に対応できるようにと回答をしています。
回答を確認されたのなら、その結果を書いて下さい。
確認できない事情があれば、その旨を知らせてください。
相手はMougGPTではなく、生きている人間です。
人としての最低限のマナーは守りましょうよ。

＜ご質問１＞
 
については、指定したキーワドに対するカウント数一覧も作成したいと考えております。
 
恐れ入りますが、ご教示をお願い致します。
 
＜結果例＞
 
WIFI 3000
DONU 2000
TEL 4500

引用:

=COUNTIFS(A1:A15000,"*WI*FI*")+COUNTIFS(A1:A15000,"*D*ONU*")+COUNTIFS(A1:A15000,"*ﾘｾｯﾄ*")

=COUNTIFS(A1:A15000,"*WI*FI*")+COUNTIFS(A1:A15000,"*D*ONU*")+COUNTIFS(A1:A15000,"*ﾘｾｯﾄ*")
を
=COUNTIFS(A1:A15000,"*WI*FI*",A1:A15000,"*D*ONU*",A1:A15000,"*ﾘｾｯﾄ*")
に変更してみてください。

＞WinArrow さんの引用:
=COUNTIFS(A1:A15000,"*WI*FI*")+COUNTIFS(A1:A15000,"*D*ONU*")+COUNTIFS＞(A1:A15000,"*ﾘｾｯﾄ*")
＞を=COUNTIFS(A1:A15000,"*WI*FI*",A1:A15000,"*D*ONU*",A1:A15000,"*ﾘｾｯﾄ*")
＞に変更してみてください。
 
  
ご返答、ありがとうございます。
  
また、質問の仕方が下手ですみません。
  
カウント方法をOR表現にしていたのは、一つのセル(記録文章)の中で、例：３つのキーワードの内、１つでも該当するものがあれば一つのセルにつき１カウントのみとしたかったからになります。
（例えばWIFIと記録をするのに、WIFIの人もいれば、無線ルータの人もいれば、ワイファイ等、複数の表現で入力している場合があるため、複数の条件指定をする必要がありました）
 
しかし、結果は重複カウントになってしまい、望んだ結果にはなりませんでした。
 
→ご教示頂いた式は、AND表現になりませんでしょうか？

引用:
１つでも該当するものがあれば一つのセルにつき１カウントのみとしたかった

作業列の数式
=IF(COUNTIF(A2,"*WI*FI*")>0,1,IF(COUNTIF(A2,"D*COUNT*")>0,1,IF(COUNTIF(A2,"*ﾘｾｯﾄ*")>0,1,0)))
下へフィルドラッグ
して、全部を合計します。
作業列のセルは、
どれかに該当すると「1」が返ります。
 
ご教示、ありがとうございます。
 
こちらの式についてですが、１つのセル内で実行（完結）する方法はありませんでしょうか？
 
カウントを取りたい項目は全部で数十個あるため、項目ごとに自動で集計・表示できるようにしたいと考えております。
 
＜結果例＞
 
DONU　３０００件
WIFI ２０００件
電話　３０００件
リセット ４０００件　　といった項目が数十個

引用:
＜結果例＞
DONU　３０００件
WIFI ２０００件
電話　３０００件
リセット ４０００件　　といった項目が数十個

>DONU　３０００件
(1)
キーワード単独の出現回数は、単純にCOUNTIF関数で算出できます。
(2)
すべてをOR条件と見たときの出現回数は、
その数十個のキーワードを、C2以下に並べて、既に提示したフィルタオプションを実行するだけです。
 
■参考記事
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_advancedfilter.html
 
フィルタオプションの条件の書き方は下記を参考にしてください。
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter3.htm
(1)
項目を縦に並べるとOR条件、横に並べるとAND条件です
例えば、以下の条件は

    C列     D列
1   内容    内容
2   A       B
3   C

    C列
1   条件            (C2セルは空白か、見出しと一致しない例えば「条件」などとします）
2   =OR(COUNTIF(A2,"*WI*FI*")>0, COUNTIF(A2,"D*COUNT*")>0, COUNTIF(A2,"*ﾘｾｯﾄ*")>0))

[quote="半平太"]

引用:
＜結果例＞
 
実際はワイルドカード考慮後でのカウントですよね？
つまり、*WI*FI*　*D*ONU*　*ﾘｾｯﾄ*　等でカウントするのあり、上記項目と同じ文字列ではない。
 
そうなると、数十個あると言うその他の項目名もどのようにワイルドカードが
挿入されているのか説明が必要と思うのですが？
それとも、*WI*FI*　*D*ONU*　以外は「*ﾘｾｯﾄ*」の様に前後に*を付ければいいのですか？
 
ご意見、ありがとうございます。
 
説明が下手ですみません。
 
WIFIについては、WI−FIと、DONUについては、D-ONUと入力している人もいましたので、
ワイルドカードを入れておりました。
それ以外はおそらくないと思われます。
 
＞＜私のPC環境＞
＞エクセル2016、アクセス2016がPCに入っています。
最終的には会社のPCで処理するハズなので、会社のバージョンを申告して下さい。
※複数種ある場合は保守的に一番古いバージョン

ここは、ＶＢＡのばんだから、ＶＢＡで考えてみましょう。
 
↓のコードが参考になれば・・・・動作確認してありません。
処理時間は、期待しない方がよいかも・・・・

Sub test()
Dim SLTMOJI, i As Long
Dim DATACELL, RX As Long

    SLTMOJI = Range(検索文字列).Value
    DATACELL = Range("A1:B15000").Value
    
    For RX = LBound(DATACELL) To UBound(DATACELL)
        DATACELL(RX, 2) = 0
        For i = LBound(SLTMOJI) To UBound(SKTMOJI)
            If WorksheetFunction.CountIf(DATACELL(RX, 1), SLTMOJI(i, 1)) > 0 Then
                DATACELL(RX, 2) = 1
                Exit For
            End If
        Next
    Next
            
    Range("A1:B15000").Value = DATACELL
        
    Range("B15001").Value = "=SUM(B1:B15000)"
End Sub

データ個数の制限もありません。スムーズに抽出ができます。
 
#　どうやら提案したフィルタオプションはお好みではないようですね。
#　高機能の道具を使わないのは機会損失だと個人的には思いますが、各人の自由です。
#　こちらは情報提供するまでです。別に使わなくても差し支えありません。
 
ご丁寧なご教示ありがとうございます。
 
また、理解不足ですみません＞＜。
 
＜ご質問２＞
 
の回答だと思っておりました。是非、会社で試させて頂きたいと思いますm(__)m
 
※半年前に異動してきた現在の事務系部署の仕事は正直あまり得意ではありませんが、その前に通信系の
　技術者をしておりましたので、高機能の道具には大変興味があります。教えて頂きありがとうございます！

１列の複数ＯＲ条件に対応して抽出するには、
simpleさん紹介のフィルタオプションが適していると思います。
抽出したら、件数を求めるだけなので、簡単です。
 
 
なお、フィルタオプションは、一般機能の「データ」タブの「詳細設定」ダイアログで操作します。
勿論、ＶＢＡでも対応可能ですが、抽出したデータを破棄してもよければ、
敢えてＶＢＡで対応することもないような気がします。
ともあれ、フィルタオプションを検討してみてください。

こんな感じかな？ 　実質、数式とフィルタオプションだけれども・・
 

Sub trial()
    Dim rScope As Range
    Dim bottom As Range
    Dim rSearch As Range
    Dim vOrigin
    
    Set rScope = Range("A1", Cells(Rows.Count, "A").End(xlUp))
    
    Set bottom = Cells(Rows.Count, "B").End(xlUp)
    Set bottom = bottom.Offset(IIf(bottom = "重複除き", -1, 0))
    Set rSearch = Range("B2", bottom)
    
    vOrigin = rSearch.Value
    
    With rSearch.Offset(, 1)
        .Formula = "=IF(B2=""WIFI"",""*WI*FI*"",IF(B2=""DONU"",""*D*ONU*"",""*""&B2&""*""))"
        .Value = .Value
        .Offset(, -1).Value = .Value
        .FormulaLocal = "=COUNTIF(A:A,B2)"
        .Value = .Value
    End With
    
    Range("D1", Cells(Rows.Count, "D").End(xlUp)).ClearContents
    Range("D2").Formula = "=SUMPRODUCT(COUNTIF(A2," & rSearch.Address & ":$B$5))>0"
    rScope.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("D1:D2"), _
                                                CopyToRange:=Range("D3"), Unique:=False
    
    Range("D3", Cells(Rows.Count, "D").End(xlUp).Offset(2)).Copy Range("D1")
    Range("D1") = "該当セル"
    
    bottom.Offset(1) = "重複除き"
    bottom.Offset(1, 1) = Evaluate("COUNTA(D:D)-1")
    rSearch.Value = vOrigin '検索値をワイルドカードなしに戻す
End Sub

<実行前>
行 _________________A_________________ _____B_____ __C__
 1 対象データ                          検索文字列  件数 
 2 WI-FI接続不可。配線抜け。D-ONUﾘｾｯﾄ  WIFI             
 3 a                                   DONU             
 4 WIRELESS                            ﾘｾｯﾄ             
 5 ｂ                                  WIRELESS         
 6 WI-FI接続不可。                     
 7 WI-FI接続不可。配線抜け                              
 8 WI-FI接続不可。配線抜け。D-ONUﾘｾｯﾄ                   
 9 WIRELESS LAN                                         

<実行後>
行 _________________A_________________ _____B_____ __C__ _________________D________________
 1 対象データ                          検索文字列  件数  該当セル                           
 2 WI-FI接続不可。配線抜け。D-ONUﾘｾｯﾄ  WIFI          4   WI-FI接続不可。配線抜け。D-ONUﾘｾｯﾄ 
 3 a                                   DONU          2   WIRELESS                           
 4 WIRELESS                            ﾘｾｯﾄ          2   WI-FI接続不可。                    
 5 ｂ                                  WIRELESS      2   WI-FI接続不可。配線抜け            
 6 WI-FI接続不可。                     重複除き      6   WI-FI接続不可。配線抜け。D-ONUﾘｾｯﾄ 
 7 WI-FI接続不可。配線抜け                               WIRELESS LAN                       
 8 WI-FI接続不可。配線抜け。D-ONUﾘｾｯﾄ                                                       
 9 WIRELESS LAN   

間違い易いので予め指摘しておきます。
 
先述の参照サイト
http://www.eurus.dti.ne.jp/~yoneyama/Excel/filter3.htm
に以下の記述があります。
 
| 前方一致となる例
| データ表から「担当者」が『井上』のデータをM5セル以降に抽出します。
|
| Excel2002の場合は、M3セルに「井上」と入力しても完全一致での検索が行われます。
| Excel2007,2010,2013,2016の場合は「井上」とだけ入力すると前方一致での検索となります。
| つまり、「井上」と一緒に「井上A」も抽出されます。「A井上」は抽出されません。

つまり、
文中に「井上」を含むものを指定するには、
・*井上
とすればよいことになります。
尤も、
・*井上*
と書いても支障ないと思います。
安全側に倒してこう書くことも一法でしょうか。

＞ともあれ、フィルタオプションを検討してみてください。[/quote]
 
ありがとうございます。承知しました。

[quote="半平太"]こんな感じかな？ 　実質、数式とフィルタオプションだけれども・・
 
ご教示ありがとうございます。確認いたします。

＞安全側に倒してこう書くことも一法でしょうか。
＞[/quote]
 
ご丁寧にありがとうございます。大変勉強になりました。。