EXCELのIF、OR、COUNTIF関数について

質問

EXCEL２０１０にて

=IF(OR(COUNTIF(K20,"*○○町*"),COUNTIF(K20,"*●●町*"),COUNTIF(K20,"*◎◎町*")),"○○区",IF(OR(COUNTIF(K20,"*△△町*"),COUNTIF(K20,"*▲▲町*"),COUNTIF(K20,"*▽▽町*")),"▼▼区",IF(OR(COUNTIF(・・・・・・・・・・,"error")))))))

という感じで判別しているのですが
このまま書いていると８０００字ほどの文字数制限にかかってしまいます。
もっと短くかける書き方、関数などありますでしょうか？

よろしければ詳しい方回答お願いします。m(_ _)m

回答No.2

tom04 · ベストアンサー

こんにちは！
一例です。

↓の画像のように検索（含まれるもの）のデータを表にしておきます。

そしてB2セルに
=IF(A2="","",IF(OR(ISNUMBER(FIND($D$1:$D$16,A2))),"有り","なし"))
配列数式になりますので、この画面からコピー＆ペーストする場合は
B2セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

これをオートフィルで下へコピーすると画像のような感じになります。

※　画像では「＃」（まず入ることはないであろう文字）
を入れている部分も範囲指定していますので、今後データが増える場合は＃の部分に追加すればOKです。

こんなんではどうでしょうか？m(__)m

お礼

回答ありがとうございます。
IFで続けていけば「結果」部分を増やすことができそうですね、勉強になります。

回答No.7

imogasi

質問に、こんな数式の長いのを挙げて、読者に読み解かすのでなく、多くても１０行程度のデータ実例と、やりたいことを文章で表現できる力を磨くこと。
例　住所などの市区町村名が、指定した市区町村群に該当するかの印を出したい、
ーー
IF関数はネストの数（深さ）に制限があるはず。特に２００３まではね。
＃２のご回答のような例なら、VLOOKUPでも考えられないか考えるものだ。
IF関数のネストが３，４以上になれば他の（関数を使った）方法はないか、考えるべきです。
またはVBAを使ったり、SQL使ったりして処理するものです。
VLOOKUP関数など知らないようでは、エクセルの関数の勉強が足りない。
VLOOKUP、MATCH、COUNTIF関数を使う方法を考えるべき。
ーー
本件は住所などの市区町村名が、指定市区町村群に該当するかということらしいが、住所のほうで市区町村名がそのままでは切り出されていないのでVLOOKUP関数が使いにくい。
住所から、まず作業列に、市区町村名部分だけをを抜き出すことを考えるべきではないか。
Googleででも「エクセル 市区町村 抽出」で照会すれば
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1429197924
以下の記事に適当なものを探すとか。

お礼

回答ありがとうございます。
8000字から200字程度にまとめて私の現状を分かりやすく伝えたつもりでしたが理解されなかったようで力不足です、すいません。
勉強が足りないのは承知しております、「聞くは一時の恥、聞かぬは一生の恥」精神で質問させていただきました。

回答No.6

tom04

No.2です！
たびたびごめんなさい。

前回は質問の数式をしっかり確認せずに投稿していました。
結局↓の画像のように条件分けがたくさんある訳ですよね？

もしそうであると関数で処理してもIFのネストだらけで大変だと思います。
そこでVBAでの一例です。

画像のようにSheet2に表を作成しておきます。
Sheet2の2行目以降のデータには重複はないものとして、実状に合わせて何列・何行作成しておいても構いません。
Sheet2のデータがSheet1のA列に含まれる場合、
Sheet1のB列にSheet2の1行目データを表示させるとします。

画面左下のSheet1にSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, j, k As Long
Dim ws As Worksheet
Set ws = Worksheets(2)
Application.ScreenUpdating = False
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To ws.Cells(1, Columns.Count).End(xlToLeft).Column
For k = 2 To ws.Cells(Rows.Count, j).End(xlUp).Row
If Cells(i, 1) Like "*" & ws.Cells(k, j) & "*" Then
Cells(i, 2) = ws.Cells(1, j)
End If
Next k
Next j
Next i
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 2) = "" Then
Cells(i, 2) = "Error"
End If
Next i
Application.ScreenUpdating = True
End Sub 'この行まで

※　一旦マクロを実行すると元に戻せませんので、別Sheetでマクロを試してみてください。

参考になれば良いのですが・・・m(_ _)m

お礼

何度も回答していただいてすみません、ありがとうございます。
ExcelのVBAというものにまだ手を出していなかったのでかなりレベルアップしたな…と正直思っております（笑）
お手数かけました、親切にしていただいて感謝しきれません。

回答No.5

kichi4182

もしもあなたが、プログラミングが出来る方なら、VBAで関数を作ってしまうという手もありますよ。
気を付けないといけないことが多々有りますが、文字数制限は無いですし、セルに表示させる文字数は飛躍的に短くなります。１文で表す必要も無いので、複雑な処理も可能です。勉強は必要ですが．．．．．．．

頑張って下さい。

お礼

回答ありがとうございます。
プログラミングは学校で勉強しましたがまだまだ勉強不足なので難しいです・・・精進します。

回答No.4

MackyNo1

町名と区の対応表があれば、簡単にVLOOKUP関数で表示できます。

数式で対応表を利用せず数式だけで対応するなら、以下のように検索値に配列定数を利用することになります。

=IF(COUNT(1/FIND({"○○町","●●町","◎◎町"},K20),"○○区",IF(COUNT(1/FIND({"△△町,"▲▲町","▽▽町"}),"▼▼区",IF(・・・・・・・・・・,"error")))))

お礼

回答ありがとうございます。
一つのセルから複数の条件を探して対応した答えを出すと言うのも色々あるのですね、勉強になります。

回答No.3

mu2011

一例です。
仮にSheet2のA1:A10に変換元町名（アスタリスク付き）、B1:B10に変換先の区名を定義します。
=IF(SUMPRODUCT(COUNTIF(K20,Sheet2!$A$1:$A$10)),INDEX(Sheet2!B:B,SUMPRODUCT(COUNTIF(K20,Sheet2!$A$1:$A$10)*ROW($A$1:$A$10))),"error")

お礼

回答ありがとうございます。
他の回答の例と合わせて一番使いやすいものを使っていきたいと思います、勉強になりました。

回答No.1

keithin

沢山の調べる言葉を列記するだけでも随分長くなりますが，
=IF(K3="","",IF(OR(ISNUMBER(FIND({"○○語","△△語","□□語三"},K3))),"語学",IF(OR(ISNUMBER(FIND({"●●数学","▲▲学","▲▲学２"},K3))),"数学","該当無し")))
などように集約することはできます。

長いのがイヤなのでしたら，Ａ列に調べる言葉，Ｂ列に該当する語学等を列記しておいて
=IF(OR(ISNUMBER(FIND(A1:A6,K3))),INDEX(B1:B6,SUMPRODUCT(IF(ISNUMBER(FIND(A1:A6,K3)),ROW(A1:A6)))),"該当無し")
と記入してコントロールキーとシフトキーを押しながらEnterで入力するなどで，式を短くできます。

お礼

お早い回答ありがとうございます。
すべてを書き終える前に文字数制限に引っかかっておりますので、いろいろ試してみたいと思います。