IFの入れ子7個以上をEXCEL2003で使いたい

質問

EXCEL2003でIFの入れ子が7個までしか使えませんが、
関数またはVBAを使って7個以上の入れ子を機能させるには
どうしたらいいでしょうか。

やりたいことは、ピボットテーブルで使いやすいように
文字列データを別の列にコピーしたいのですが、
元データにはいろいろな文字列が順不同で入っており、
それを取り出すのにIFの入れ子を多様しています。

たとえばこんなデータなのですが、

商品番号　商品タグ　　　　　　　　　　売上金額
No1　　　　■果物■りんご　　　　　　　1000
No2　　　　■野菜■きゅうり　　　　　　2000
No3　　　　■果物■みかん　　　　　　　1000
No4　　　　■野菜■にんじん　　　　　　2000
No5　　　　■果物■りんご　　　　　　　1000
No5　　　　■果物■りんご　　　　　　　1000　　　
No6　　　　ドリンク　　　　　　　　　　1000　　　

商品タグには
大カテゴリ　果物、野菜
中カテゴリ　果物の場合、りんご、みかん
　　　　　　野菜の場合、きゅうり、にんじん
と分類されています。
一つのセルに大カテゴリと中カテゴリの両方が入ります。
これを中カテゴリをキーにしてピボットテーブルで集計したい場合、
下記の様もう一つ列を作り、そこに集計したいものだけが入った
データを作らなければなりません。

　　　A　　　　　B　　　　　　　　　　　　　C　　　　　D
１商品番号　商品タグ　　　　　　　　　　売上金額　中カテゴリ
２No1　　　　■果物■りんご　　　　　　　1000　　　りんご
３No2　　　　■野菜■きゅうり　　　　　　2000　　　きゅうり
４No3　　　　■果物■みかん　　　　　　　1000　　　みかん
５No4　　　　■野菜■にんじん　　　　　　2000　　　にんじん
６No5　　　　■果物■りんご　　　　　　　1000　　　りんご
７No6　　　　ドリンク　　　　　　　　　　1000　　　その他

商品タグは実際にはこの前後にたくさんの文字列があり、
文字数も決まっていませんので、文字列を取り出すには
search関数やmid関数などを使って取り出しています。

また、取り出したい文字列が含まれていない場合は
すべて「その他」と入力したいのです。

セルD2には下記の式を入れています。
=IF(ISERROR(MID(B2,SEARCH("■りんご",B2),3)=TRUE),
IF(ISERROR(MID(B2,SEARCH("■みかん",B2),4)=TRUE),
IF(ISERROR(MID(B2,SEARCH("■きゅうり",B2),5)=TRUE),
IF(ISERROR(MID(B2,SEARCH("にんじん",B2),5)=TRUE),"その他","にんじん"),"きゅうり"),"みかん"),"りんご")

これをIFの入れ子７個以上でも使えるようにするには、どうしたらいいでしょうか。

回答No.8

MackyNo1

>補足ですが、常に最後の■を採用するというわけではないです。

これまでに例示されたデータはすべて最後に出てくる■の後の文字を抜き出していると思うのですが、もし違う条件なのであれば、それ以外の例を具体的に提示してください。

お礼

お礼が遅くなり大変申し訳ありませんでした。
教えていただいた方法で、文字数がいろいろあることから
余計な文字列まで表示されてしまいますが、あとは検索置換などで
対処すればなんとかなりそうです。
こちらの関数のおかげで、大分時間の手間は省けました。
ありがとうございました。

回答No.7

MackyNo1

例示の「訳ありりんご」のデータのように、単純に最後の■の後の文字列を抜き出したいという条件だけでよいなら、以下のような関数で簡単に表示できます（20文字までの例）。

=IF(ISERROR(FIND("■",B2)),"その他",TRIM(RIGHT(SUBSTITUTE(B2,"■",REPT(" ",20)),20)))

補足

補足ですが、常に最後の■を採用するというわけではないです。

回答No.6

KURUMITO

解答Ｎｏ1，3です。
訳ありりんごの件については▲の次に入力するようにすることが式を簡単にする上で重要ですね。
次の式をＤ2に入力して下方にオートフィルドラッグすればよいでしょう。
=IF(B2="","",IF(COUNTIF(B2,"*■*■*")=0,"その他",IF(COUNTIF(B2,"*▲*")=0,MID(MID(B2,FIND("■",B2)+1,20),FIND("■",MID(B2,FIND("■",B2)+1,20))+1,10),MID(B2,FIND("▲",B2)+1,10))))
なお、式の上で10や20を使っていますがこれは■などの後にくる文字数が幾つになるか分からないので多めに取った数として使っています。■の後にくる文字数を求めることができますがそれでは式がより複雑になるからです。■の後に来る文字数の最大の文字数が判っているのでしたらその数字を使ってもよいでしょう。

回答No.5

MackyNo1

>ROWの範囲を変更すると＃N/Aとエラーが出てしまいます。

最初に提示したCHOOSE関数の式での話でしょうか？

いずれにしろ、配列で数式を作る場合は、相互に対応する配列が同じ数になる必要があります。

もしうまく表示されないなら、表示させたいリストの範囲などの情報と共に、実際に入力している数式をそのままコピー貼り付けして提示していただければ、エラーの原因がわかると思います。

回答No.4

MackyNo1

＞No.3までのデータはうまくいったのですが、No.4以降はすべて「その他」と表示されてしまいます。ROWの範囲を($1:$7)にしてもだめでした。

おそらく、数式を修正した後にＣｔｒｌ＋Ｓｈｉｆｔ＋Ｅｎｔｅｒで確定していないのではないかと思われます。

＞{"にんじん";"きゅうり";"みかん";"りんご"}の部分を絶対参照で指定するというのが、やり方がよくわからなかったのですが、もう少し具体的にお伺いしてもよろしいでしょうか。

そのままセル範囲を入力するということですが、ＣＨＯＯＳＥ関数の場合は、後半の引数を文字列で指定する必要があるので、それほど効率的にはなりません。

抽出対象をセルに入力しているならＩＮＤＥＸ関数を使ったほうが簡単です。

たとえばＧ１セルに「その他」Ｇ２セル以下Ｇ１０セルに抽出項目が入力されているなら、たとえば以下の式でそれらの値を抽出することができます（これも配列数式ですが、Ｃｔｒｌ＋Ｓｈｉｆｔ＋Ｅｎｔｅｒで確定しなくても表示できるようにＩＮＤＥＸ関数で配列を範囲に変更しています）。

=IF(B2="","",INDEX($G$1:$G$10,(MAX(INDEX(COUNTIF(B2,"*"&$G$2:$G$10)*ROW($1:$9),))+1)))

ちなみに、ＲＯＷの引数の部分は、抽出データ１から抽出データの数までにしてください。

お礼

私が配列のことがよくわからなくて、教えていただいた関数の内容を
理解するのに、まだ時間がかかっています。

＞＞No.3までのデータはうまくいったのですが、No.4以降はすべて「その他」と表示されてしまいます。ROWの範囲を($1:$7)にしてもだめでした。

＞おそらく、数式を修正した後にＣｔｒｌ＋Ｓｈｉｆｔ＋Ｅｎｔｅｒで確定していないのではないかと思われます。

こちらは確かにＣｔｒｌ＋Ｓｈｉｆｔ＋Ｅｎｔｅｒで確定させました。
式も"｛"で始まっています。
ROWの範囲を変更すると＃N/Aとエラーが出てしまいます。

回答No.3

KURUMITO

解答Ｎｏ１です。
最初の■の前にも文字などがあるのでしたらＤ2への入力の式は次のようにしてください。
=IF(B2="","",IF(COUNTIF(B2,"*■*■*")=0,"その他",MID(MID(B2,FIND("■",B2)+1,20),FIND("■",MID(B2,FIND("■",B2)+1,20))+1,10)))

お礼

この式で■の前にテキストがあってもうまく行きました。
ありがとうございました！

質問なのですが、midの後にある20や10はどこから出た数字でしょうか。
サンプル例では問題ないのですが、実際のデータでは余計なテキストまで
表示されてしまいます。

また、サンプル例では網羅しきれていなかったのですが、
中カテゴリが１つの行に2種類あります。

たとえば、No.5のように「りんご」ですが
「訳ありりんご」も併記されていたら、
そちらを優先して出したいのです。
なぜ中カテゴリが２つもあるかというと、りんごでも集計したいし、
さらに細かく訳ありとそうでないものの集計も出したりしたいからです。

　　　A　　　　　B　　　　　　　　　　　　　C　　　　　D
１商品番号　商品タグ　　　　　　　　　　売上金額　　中カテゴリ
２No1　　■１２３４果物■りんご　　　　　　　1000　　　りんご
３No2　　■５６７８９野菜■きゅうり　　　　　2000　　　きゅうり
４No3　　■１２３４果物■みかん　　　　　　　1000　　　みかん
５No4　　■５６７８９野菜■にんじん　　　　　2000　　　にんじん
６No5　　■１２３４果物■りんご■訳ありりんご　1000　　訳ありりんご
７No6　　ドリンク　　　　　　　　　　　　　　1000　　　その他

教えていただいた関数では２つ目の■以降の文字までしか出てきません。
中カテゴリが２つあるのは曖昧ですよね。
たとえば中カテゴリが２つあったら１つ目は■、二つ目は▲で始めるよう使いわければうまくいきますか。

６No5　　■１２３４果物■りんご▲訳ありりんご

回答No.2

MackyNo1

特定の文字列の含まれる位置を自由にワイルドカードで指定したいなら以下のような配列数式が使えます（入力後Ctrl+Shift+Enterで確定）。

=IF(B2="","",CHOOSE(MAX(COUNTIF(B2,"*■"&{"にんじん";"きゅうり";"みかん";"りんご"})*ROW($1:$4))+1,"その他","にんじん","きゅうり","みかん","りんご"))

上記の例は■の後に検索文字列があり、その文字列で終了しているケースを対象としていますので、必要に応じて適宜ワイルドカード文字を変更してください。

また{"にんじん";"きゅうり";"みかん";"りんご"}の部分はセルに検索文字を入力しておいて、このセル範囲を絶対参照で指定するほうが簡単な数式になります（ROW関数の中の$1$4の部分もセル参照にするとよい）。

お礼

早速のご回答ありがとうございます。
No.3までのデータはうまくいったのですが、No.4以降はすべて「その他」と表示されてしまいます。ROWの範囲を($1:$7)にしてもだめでした。

{"にんじん";"きゅうり";"みかん";"りんご"}の部分を絶対参照で指定するというのが、やり方がよくわからなかったのですが、もう少し具体的にお伺いしてもよろしいでしょうか。

回答No.1

KURUMITO

Ｄ2セルには次の式を入力して下方にオートフィルドラッグします。
=IF(B2="","",IF(COUNTIF(B2,"■*■*")=1,MID(B2,FIND("■",B2,2)+1,10),"その他"))
中カテゴリは二つ目の■の次に記載されていることが決まりとなっていれば上の式でよいでしょう。IFで多くの入れ子を使うこともないでしょう。

お礼

早速のご回答ありがとうございます。
実際入力してみると、商品タグには■で始まるとはかぎらないので、
■の前になにか文字があった場合は、結果がすべて「その他」になってしまい、うまくいきませんでした。

　　A　　　　　B　　　　　　　　　　　　　C　　　　　D
１商品番号　商品タグ　　　　　　　　　　売上金額　中カテゴリ
２No1　　　　123■果物■りんご　　　　　　　1000　　　りんご