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

Question

EXCEL２０１０にて

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

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

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

tom04 · Accepted Answer

こんにちは！
一例です。

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

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

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

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

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

imogasi · Answer

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

tom04 · Answer

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

kichi4182 · Answer

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

頑張って下さい。

MackyNo1 · Answer

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

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

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

mu2011 · Answer

一例です。
仮に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")

keithin · Answer

沢山の調べる言葉を列記するだけでも随分長くなりますが，
=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で入力するなどで，式を短くできます。

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

こんにちは！

質問に、こんな数式の長いのを挙げて、読者に読み解かすのでなく、多くても１０行程度のデータ実例と、やりたいことを文章で表現できる力を磨くこと。

No.2です！

もしもあなたが、プログラミングが出来る方なら、VBAで関数を作ってしまうという手もありますよ。

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

一例です。

沢山の調べる言葉を列記するだけでも随分長くなりますが，

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング