複数の種類の語句のセル内検索

質問

A列にキーワード、Ｂ列にその説明が入ってるとします
　　　Ａ　　　　Ｂ 　　　　　　　　　　　　　　　　　Ｃ
１　美しい　美について形容した物
２　愛　　　感情の一つに付けられた名前
３　投げる　物を動かす動作の一つ
４　～の　　他の品詞の末尾に付く物
５　早い　　速度を表すときの形容詞
６　…　　　　…　　　　　　　　　　　　　　　　　　…
…
と言う具合に

勿論Ｂ列の内容には十分配慮するとして
このＢ行の文字の中に"形容"・"名"・"動"・"尾"…等の物があるか調べ
有ればそれぞれ同一行のＣ列にそれぞれ"形容詞"・"名詞"・"動詞"・"接尾語"…などと表示させ
無ければ"-"を表示させたいのですが

ＩＦ構文を使えば取り敢えずは出来そうですがＣ行に表示させる種類が多い場合
文が長くなるし、それに連れて処理も重くなってしますので限界があると思うのですね
また、マクロはそれと判らない人に白い目で見られて
「安全管理上問題有り」と言われるので出来れば使いたくないのです
更に、他のセル・Bookは使用を許されていません

上手くできる方法はありますでしょうか？

回答No.1

mshr1962 · ベストアンサー

=CHOOSE(SUMPRODUCT(COUNTIF(B1,{"*形容*","*名*","*動*","*尾*"})*{1,2,3,4})+1,"-","形容詞","名詞","動詞","接尾詞")
※CHOOSE関数の設定上設定できる品詞は最大29個（なしの"-"を含む）です。

お礼

早速のご回答有難う御座います

感服しました

正に「我、強者とまみえん」の瞬間でした
恐れ入りました
近年希にみないブレイクスルーだと思います

私なりに後学にと
頂いたこの構文(慣用句？)を分解して
「SUMPRODUCT」の中の「COUNTIF」を外に出したら(参照にしたら)
第1列の場合1が10に変わってしまいました
しかも引数が1個しかないし…
なぜに？？　ヘ(～～;

更に「COUNTIF」に「｛　｝」が使われているのにも驚きました
全く意味不明、未知との遭遇です

やはりそもそもExcel流の配列の捕まえ方が判っていない性なのでしょうか？
どう考えても
「=SUMPRODUCT(COUNTIF(B1,{"*形容*","*名*","*動*","*尾*"})*{1,2,3,4})」
に掛けられた魔法が解けません

出来れば解説いただければ助かるのですが

宜しくお願い致します　<(_ _)>

補足

戸頃で
G1に1、G2からG6まで0を入れても
1に1、G1からG6まで1を入れても
G1から順にG6まで1から6まで入れても
=SUMPRODUCT(SUMIF(B1,{"*形容*","*名*","*動*","*尾*"},$G$1:$G$5)*{1,2,3,4})
としたところちゃんと動作しました

が
=SUMPRODUCT(SUMIF(B1,{"*形容*","*名*","*動*","*尾*"},{1,1,1,1})*{1,2,3,4})
とか
=SUMPRODUCT(SUMIF(B1,{"*形容*","*名*","*動*","*尾*"},{1,1,1,1,1})*{1,2,3,4})
とか
=SUMPRODUCT(SUMIF(B1,{"*形容*","*名*","*動*","*尾*"},{1;1;1;1})*{1,2,3,4})
とか
=SUMPRODUCT(SUMIF(B1,{"*形容*","*名*","*動*","*尾*"},{1;1;1;1;1})*{1,2,3,4})
とか
=SUMPRODUCT(SUMIF(B1,{"*形容*","*名*","*動*","*尾*"},$G$1:$G$4)*{1,2,3,4})
とか
=SUMPRODUCT(SUMIF(B1,{"*形容*","*名*","*動*","*尾*"},$G$1:$G$6)*{1,2,3,4})
とか
全て文字列として認識しました

全く意味不明…

回答No.8

rin01

何度もゴメンナサ～イ。。。！！

そそっかしいので
間違えたとこがありましたから
訂正しておきます。。。

★式は、C1　じゃなくて　E1　ですね。。

★式の中の　
> "形容詞動詞"},
は、"形容動詞" 　ですね。。

★
>C列は、残しておくんでしょうか？
>C列があるのでしたら

今回B列が増えましたので
D列の間違いです。

以上、訂正でした。。。。Rin

回答No.7

rin01

追伸で～す。。。♪

NO6で書いた式では、不十分かも？
まちがえて違うB列の行へ　M　入れても形容動詞に
なってしまいま～すので。。。

こちらの方がいいですね。。
チョット、追加して
C1: =INDEX({"-","形容詞","名詞","動詞","接尾詞","形容詞動詞"},MAX((B1="M")*COUNTIF(C1,"*形容*")*5,INDEX(COUNTIF(C1,{"*形容*","*名*","*動*","*尾*"})*COLUMN(A:D),))+1)

下にコピーです。

★ただ、理解力がなくてすみませんが
C列は、残しておくんでしょうか？
C列があるのでしたら、
MAX((B1="M")*COUNTIF(C1,"*形容*")*5
の部分は
MAX((B1="M")*(C1,"形容詞)*5

でもいいですね。。。。

失礼しました～。。。。Rin～♪♪

回答No.6

rin01

こんにちは～♪
Rinで～す。。。

★形容詞と形容動詞の違いとか、私はぜんぜんわかりません。。
　B列に、　M　と入力した時だけ
　提示された表　↓　の様にE列に(形容動詞)表示するには

　　　　Ａ　　　　Ｂ　　Ｃ　　　　　　　　　　　　　　　　　　　Ｄ　　　　　Ｅ
１　　　美しい　　　　美について形容した物　　　　　形容詞　　形容詞
２　　　愛　　　　　　感情の一つに付けられた名前　名詞　　　名詞
３　　　投げる　　　　物を動かす動作の一つ　　　　　動詞　　　動詞
４　　　～の　　　　　他の品詞の末尾に付く物　　　　接尾詞　　接尾詞
５　　　早い　　M 　　速度を表すときの形容詞　　　形容詞　　形容動詞

E1: =INDEX({"-","形容詞","名詞","動詞","接尾詞","形容詞動詞"},MAX((B1="M")*5,INDEX(COUNTIF(C1,{"*形容*","*名*","*動*","*尾*"})*COLUMN(A:D),))+1)

下にコピー。
で、いかがでしょうか？
他の式は、考えていませんが～。

★それから。。。
>Excel2000環境で配列を扱うCOUNTIFが上手く動作しませんでした　(T_T)
>ヒットしないキーの部分がヴァリューのエラーになるのです

私には、なぜだかわかりません。
私も今会社でExcel2000を使っていますけれど。。

。。。。Rin～♪♪

回答No.5

rin01

ふたたび～です。。。。Rin～♪

>参照(検査)対象が複数列になったとき
>どう変えたらいいのでしょうか？

これは、どんな場合でしょうか？
どんな複数列で、結果はどんなになれば良いのでしょうか？
1行でも、例題の表を書いて頂けるといいのですが～。。。

それから、回答では、今回個人的に LOOKUP にこだわったので
LOOKUPを書きましたが。
ホントは最初の回答は、こちらだったんです。
Noubleさんが、色々試されていろ様ですので
アップします。

C1:　=INDEX({"-","形容詞","名詞","動詞","接尾詞"},MAX(INDEX(COUNTIF(B1,{"*形容*","*名*","*動*","*尾*"})*COLUMN(A:D),))+1)

ご参考に、どうぞ～。。。

>RinさんにはExcelハンドラーの称号を

なあ～んて！！。書かれると・・すごくうれしい・・ですけど
そんなレベルじゃありませんし。。。
たまたまの回答ですので。

でした。。。。♪♪

お礼

いえいえご謙遜を
マスターしただけには留まらず
我が儘・気ままな暴れ馬のExcelを
ちゃんと御せる方とお見受けしましたので
僭越ながら…

補足

早速のお運び感謝です

戸頃で
　　　Ａ　　　　Ｂ　　　　　　　　　　　　　　　　　Ｃ
１　美しい　美について形容した物
２　愛　　　感情の一つに付けられた名前
３　投げる　物を動かす動作の一つ
４　～の　　他の品詞の末尾に付く物
５　早い　　速度を表すときの形容詞
６　…　　　　…　　　　　　　　　　　　　　　　　　…
…
前回のこの表で形容動詞が抜けていたと思いのですが
余談ですが形容詞と形容動詞は学会でも分けるか分けないか賛否両論だそうな

そこでB列の左に1行追加して
形容詞はNULLのまま形容動詞はAAの列のどう行セルにMを入れ
後で値コピーペースト後に行削除して体裁をとるとします

　　　　Ａ　　　　Ｂ　　Ｃ　　　　　　　　　　　　　　　　　　　Ｄ　　　　　　Ｅ
１　　　美しい　　　　美について形容した物　　　　　形容詞
２　　　愛　　　　　　感情の一つに付けられた名前　名詞
３　　　投げる　　　　物を動かす動作の一つ　　　　動詞
４　　　～の　　　　　他の品詞の末尾に付く物　　　　接尾詞
５　　　早い　　M 　　速度を表すときの形容詞　　　形容詞

こんな感じですね

で
この上の表を

　　　　Ａ　　　　Ｂ　　Ｃ　　　　　　　　　　　　　　　　　　　Ｄ　　　　　Ｅ
１　　　美しい　　　　美について形容した物　　　　　形容詞　　形容詞
２　　　愛　　　　　　感情の一つに付けられた名前　名詞　　　名詞
３　　　投げる　　　　物を動かす動作の一つ　　　　　動詞　　　動詞
４　　　～の　　　　　他の品詞の末尾に付く物　　　　接尾詞　　接尾詞
５　　　早い　　M 　　速度を表すときの形容詞　　　形容詞　　形容動詞

Ｅ列みたいなのを追加するにはどうすればいいの？
と言うことなのです

ちょっと考えてみたのですが
このままでは4×2のマトリクスにはならないので
ダミー記号を入れて
{"m","a","a","a";"*形容*","*名*","*動*","*尾*"；}
として
countifとSUMを掛けて
=SUM(COUNTIF({B1,C1},{"m","a","a","a";"*形容*","*名*","*動*","*尾*"})*{5,6/2,7/3,8/4;1,2,3,4})+1とか
countifを2つに分けて
=SUM({COUNTIF(B1,{"m","a","a","a"});COUNTIF(C1,{"*形容*","*名*","*動*","*尾*"})}*{5,6/2,7/3,8/4;1,2,3,4})+1とか

=SUM(COUNTIF(B5,{"m","a","a","a"})*{4,4,4.4,4},COUNTIF(C5,{"*形容*","*名*","*動*","*尾*"})*{1,2,3,4})+1とか
したいとこ…
うお～っと
最後のは許容しちゃった　(^^;)
でも他のって許容されませんよね？

まあこんな感じなのですけど
そんなのIF使えよ～　ょ～　　　ょ～　　　　　　～　　　　　　　　　　～
て言うか答え出てるやんけ～　け～　　　　　　～　　　　　　　　　　～

と突っ込み入りそうですが
そこを曲げて4×2のマトリクスで何か名案合ったら宜しくお願いします

後、この例では隣接した列ですが
不細工ですし、制作途中で上司に見られないか冷やヽ物ですから
それを思うとAA当たりならばれずに良いかな？
とも思うのでそっちも出来たら宜しくお願い致します　(*^_^*)
(同一慣用句を入れ子にするのは極力なしで)

ｐｓ
今日早速会社で使おうと思ったのですが
Excel2000環境で配列を扱うCOUNTIFが上手く動作しませんでした　(T_T)
ヒットしないキーの部分がヴァリューのエラーになるのです
なのでLookupも試してみたのですが今一…
何かこつでもあれば併せて宜しくお願いします
（環境がExcel2000の最新版でないからなんて関係あります？）

回答No.4

rin01

Noubleさん
こんばんは～♪

>かのスレッドでは失礼いたしました
どのスレッドの事かと、探してしまいました～。。。。
どうか、お気になさらずに～。

>Rinさま
は、ご遠慮申し上げます。で～す。。！！
Rinさんでいいのです。。

★COUNTIF(B1,{"*",　←ここは、内緒ですけれど。。。
　　　　　　　　　　あたま固いので～。。
　　　　　　　　　　１時間も考えてしまいました～(^_^;)汗!!笑い!!

>countIFを分母に持って行ってるじゃないですか？
>これって？

今回は、COUNTIFですが、こんな使い方は先輩の回答者の方の
回答で覚えました。

★B2の場合
=COUNTIF(B2,{"*","*形容*","*名*","*動*","*尾*"})
は
={1,1,0,0,0}の配列ができます。

これを分母にして、1 を分子にすると
=1/{1,1,0,0,0}　→　{1,1,#DIV/0!,#DIV/0!,#DIV/0!}　になります。

エルセルでは、ご存知と思いますが数値を　
0(ゼロ) で割るとエラーになります。

LOOKUPは、検索値を探す時エラーを無視しますので
これを利用して、={1,1,0,0,0}の0(ゼロ)を意図的に
エラーにさせるんですよね～。
どなたが最初に考えたのか、ホントにビックリ。すごいですよね。

そして、検索値 1 を探す場合LOOKUPは下の行を取得しますので
B2の場合は、対応範囲の2つ目の 1 なので、
,{"-","形容詞","名詞","動詞","接尾詞"})　
→　2つ目は、形容詞になります。

今回の場合、検索値が無い場合、
={1,0,0,0,0} になって、1つ目で　"-"　が返ります。。。

いろいろ書いたのに、説明がへたで、すみませ～ん。。。
Noubleさんは、上級者の方でご存知のことは
沢山あると思いましたけれど、この掲示板を見られる方も
いると思って、書きました。

失礼があれば、ゴメンナサイ！！
わたしは、すごくそそっかしいので。。
トンチンカンな、回答する場合も多いので～す。。。
そんな時は、遠慮なく指摘して下さい。

これからも、ご指導よろしくお願いしま～す。。。。
でした。。。。Rin～♪♪

お礼

Rinさん
サイドお越し頂き感謝です

そうなんですか
実は僕判らなかったので
お！　なるほど～　φ(。。
と思ってしまいました

またまた感謝です　<(_ _)>

補足

ちょっと疑問がわいたのですが
これって
参照(検査)対象が複数列になったとき
どう変えたらいいのでしょうか？

SUMPRODUCTやSUM構文は元々2次元配列対応のようなので
出来そうに思ったのですが
COUNTIFも2次元化出来るのでしょうか？
それぞれの列に1回ずつ、2回使って「;」で繋ぐとか？
頭難いのか発想が追いつきません　(T_T)

御指南お願い致します　m(_ _)m

ｐｓ
頑張って勉強しました
=CHOOSE(SUM(COUNTIF(B1,{"*形容*","*名*","*動*","*尾*"})*{1,2,3,4})+1,"-","形容詞","名詞","動詞","接尾詞")

=CHOOSE(SUMPRODUCT(SUMIF(B1,{"*形容*","*名*","*動*","*尾*"},$E$1:$E$5),{1,2,3,4})+1,"-","形容詞","名詞","動詞","接尾詞")

=VLOOKUP(SUMPRODUCT(COUNTIF(B1,{"*形容*","*名*","*動*","*尾*"}),{1,2,3,4})+1,{1,"-";2,"形容詞";3,"名詞";4,"動詞";5,"接尾詞";6,""},2,0)

=LOOKUP(1,FIND({"","形容","名","動","尾"},B1)/FIND({"","形容","名","動","尾"},B1),{"-","形容詞","名詞","動詞","接尾詞"})

sumifはセル参照が取れなくて残念です～ぅ　(T_T)
あとSUMPRODUCTは「＊」にせず「,」の方が本来の形なのでしょうかね
この形はSUMPRODUCTでもSUMでもいける汎用性のある形ですね

やはりmshr1962様の構文中の「…,4})+1,…」の威力は絶大ですね
検索にヒットしない時は基本「0」が返されるのを利用して
これに1を足して照合対象をスライドさせて「"-"」をフックするなんて…
恐れ入ります　参りました

もし私に権利があるなら
RinさんにはExcelハンドラーの称号を
mshr1962様にはExcelハンドラーマイスターの称号を
是非差し上げたいところですね
(どちらもExcelマスターより遙か上ですね)
残念ながらそんな権限無いのですが…　(*^_^*)ｖ

回答No.3

rin01

こんにちは～♪

失礼しま～す。。。。

mshr1962さんの式を一部お借りして～。
こんな式でも良さそうですね！！

C1:LOOKUP(1,1/COUNTIF(B1,{"*","*形容*","*名*","*動*","*尾*"}),{"-","形容詞","名詞","動詞","接尾詞"})

B列が空白の場合、エラーになります。
エラー表示では、困る場合は
　↓

C1:=IF(B1="","",上の式)
で、空白処理にします。

★ただ、このような、検索は
Noubleさんご自身が書かれて
いるように
>勿論Ｂ列の内容には十分配慮するとして
が、大事ですね。。。。

。。。。Rinでした～♪♪

お礼

おｏｈ！！
Rinさま
来て頂けて有難う御座います
感謝です

かのスレッドでは失礼いたしました

ところで
countif
お主やるね！！
て感じですよね
他にも隠れ「配列数」ハンドラーて
何かあるのですかね？

それとこの式ですが
countIFを分母に持って行ってるじゃないですか？
これって？

回答No.2

Cupper

＞このＢ行の文字の中に"形容"・"名"・"動"・"尾"…等の物があるか調べ

膨大なデータベースが必要になりますけど、この部分をExcelで行うのですか？
データベース専用ソフト（Accessなど）を使用する事をお薦めします。

お礼

早速の助言感謝します

ですが申し訳ないのですが賛同いたしかねます

意外とこういうシーンは資料作成中に有ると思います
しかし「あ！サーチ掛けたい」と思う度に
Ｃ列のためだけにアクセスに切り替えるわけにはいかないと思うのです

この計算だけしかしないブックなら未だ良いですが
ブックの説明から初めて更新歴・用語解説・目的と位置づけ…
その他計算されない膨大なセルの片鱗の1シートの更に1列のためだけに移すのですか？

加えて
社内の主要ポストの方が使われている全パソコンに対し
新たにそのファイル形式を開示できるようにするのですか？

たった1列の為だけに…

「て、いうかそんなことよりお前を首にした方が何百倍も安くて早くないか？」
と言われそうです
ご免なさい
同意しかねます
申し訳ない