【会員アンケートご協力のお願い】抽選で計5名様に役立つ書籍をプレゼント！

HOME > Q&A > Excel （一般機能）

Excel （一般機能）
Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2019)
COUNTIF関数で複数条件指定を配列定数からセル範囲に変更できないか？
投稿日時: 24/11/28 10:10:30
投稿者: WinArrow

ワイルドカード付きの複数条件を「配列定数」指定しています。
今後条件が増えることを想定して、「配列指定」部分をセル範囲に変更できないかと考え、試行錯誤したが、「配列指定」と同じ結果が得られません。
そもそも、無理な要求なんでしょうか？
 
表の形式 

[列I]　　　　　[列J]
分別区分 　　　種類
可	　　　　R
可	　　　　R
資源・金	B
雑･ペ･蛍･ス	B
雑･ペ･小	B
プラ資･大	Y
プラ資･破	Y
プラ資	　　　　Y
プラ資･埋	Y
プラ資	　　　　Y
プラ資･大･破	Y
プラ資･特	B

種類列の「配列指定」の数式
=IF(I2="可","R",IF(SUM(COUNTIF(I2,"*"&{"資源","雑","ペ","蛍","ス","小","特"}&"*"))>0,"B","Y"))
 
試行１
{"資源","雑","ペ","蛍","ス","小","特"}部分を１つづつセルに入力し「区分」という名前を定義
数式変更
=IF(I2="可","R",IF(SUM(COUNTIF(I2,"*"&区分&"*"))>0,"B","Y"))
 
結果
配列定数の結果が"B"となっているところ
なぜか「雑･ペ･蛍･ス」だけが"B"、他の"B"は"Y"が返ってきます。
 
試行２
=SUM(COUNTIF(I2,"*"&{"資源","雑","ペ","蛍","ス","小","特"}&"*"))
で戻り値を求めると、「雑･ペ･蛍･ス」は、"4"(正常な値)ですが、配列定数部分を「区分」に変更すると、
「雑･ペ･蛍･ス」は"1" 他は、"0"になります。
 
確認
「雑･ペ･蛍･ス」の中でヒットしている文字列は「雑」でした。
そうすると
「雑･ペ･小」も"1"が返ってもよいと考えるが・・・・不思議です。
 
回答
投稿日時: 24/11/28 11:10:00
投稿者: Suzu

配列数式になっていないからでは？
 
{=IF(I2="可","R",IF(SUM(COUNTIF(I2,"*"&区分&"*"))>0,"B","Y"))}
回答
投稿日時: 24/11/28 12:28:02
投稿者: Suzu

引用:
試行２
=SUM(COUNTIF(I2,"*"&{"資源","雑","ペ","蛍","ス","小","特"}&"*"))
で戻り値を求めると、「雑･ペ･蛍･ス」は、"4"(正常な値)ですが、配列定数部分を「区分」に変更すると、
「雑･ペ･蛍･ス」は"1" 他は、"0"になります。
  
確認
「雑･ペ･蛍･ス」の中でヒットしている文字列は「雑」でした。
そうすると
「雑･ペ･小」も"1"が返ってもよいと考えるが・・・・不思議です。

 
 
ヒットしている文字を どのようにして 確認されたのでしょうか？
 
 
配列にしていないので、
分別区分 の 上から何番目か？ と、同じリストの順番の区分のリストとが比較されているのでは？
 
雑･ペ･蛍･ス は、上から 4番目 の リスト　-　区分 の リスト 4番目 は 蛍　　→ 1
雑･ペ･小　　は、上から 5番目 の リスト　-　区分 の リスト 5番目 は ス　　→ 0
 
 
その下の プラ資･大　に、
　　雑･ペ･小 貼り付けてみると、1が返ります。
　更に、
　　小 だけにしても、 1が返ります。
　　特　に変更すると　0が返ります。
 
上記から、リストの順番と同じ区分の中身としか比較されてないのが判ります。

投稿日時: 24/11/28 13:49:11
投稿者: WinArrow

引用:
配列数式になっていないからでは？

 
セル範囲に変更したことで、配列として認識されるものと勝手に解釈していました。
 
数式入力後に[Shift]+[Ctrl]+[Enter]で確定することをすっかり忘れていました。
 
アドバイスありがとうございました。
 
戻り値の確認方法ですが、条件の「雑」を消してみました。
 
無事、意図した通りになりました。
ありがおうございました。