- 締切済み
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 1商品番号 商品タグ 売上金額 中カテゴリ 2No1 ■果物■りんご 1000 りんご 3No2 ■野菜■きゅうり 2000 きゅうり 4No3 ■果物■みかん 1000 みかん 5No4 ■野菜■にんじん 2000 にんじん 6No5 ■果物■りんご 1000 りんご 7No6 ドリンク 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の入れ子7個以上でも使えるようにするには、どうしたらいいでしょうか。
- unippa
- お礼率77% (14/18)
- その他MS Office製品
- 回答数8
- ありがとう数6
- みんなの回答 (8)
- 専門家の回答
みんなの回答
- MackyNo1
- ベストアンサー率53% (1521/2850)
>補足ですが、常に最後の■を採用するというわけではないです。 これまでに例示されたデータはすべて最後に出てくる■の後の文字を抜き出していると思うのですが、もし違う条件なのであれば、それ以外の例を具体的に提示してください。
- MackyNo1
- ベストアンサー率53% (1521/2850)
例示の「訳ありりんご」のデータのように、単純に最後の■の後の文字列を抜き出したいという条件だけでよいなら、以下のような関数で簡単に表示できます(20文字までの例)。 =IF(ISERROR(FIND("■",B2)),"その他",TRIM(RIGHT(SUBSTITUTE(B2,"■",REPT(" ",20)),20)))
補足
補足ですが、常に最後の■を採用するというわけではないです。
- KURUMITO
- ベストアンサー率42% (1835/4283)
解答No1,3です。 訳ありりんごの件については▲の次に入力するようにすることが式を簡単にする上で重要ですね。 次の式をD2に入力して下方にオートフィルドラッグすればよいでしょう。 =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を使っていますがこれは■などの後にくる文字数が幾つになるか分からないので多めに取った数として使っています。■の後にくる文字数を求めることができますがそれでは式がより複雑になるからです。■の後に来る文字数の最大の文字数が判っているのでしたらその数字を使ってもよいでしょう。
- MackyNo1
- ベストアンサー率53% (1521/2850)
>ROWの範囲を変更すると#N/Aとエラーが出てしまいます。 最初に提示したCHOOSE関数の式での話でしょうか? いずれにしろ、配列で数式を作る場合は、相互に対応する配列が同じ数になる必要があります。 もしうまく表示されないなら、表示させたいリストの範囲などの情報と共に、実際に入力している数式をそのままコピー貼り付けして提示していただければ、エラーの原因がわかると思います。
- MackyNo1
- ベストアンサー率53% (1521/2850)
>No.3までのデータはうまくいったのですが、No.4以降はすべて「その他」と表示されてしまいます。ROWの範囲を($1:$7)にしてもだめでした。 おそらく、数式を修正した後にCtrl+Shift+Enterで確定していないのではないかと思われます。 >{"にんじん";"きゅうり";"みかん";"りんご"}の部分を絶対参照で指定するというのが、やり方がよくわからなかったのですが、もう少し具体的にお伺いしてもよろしいでしょうか。 そのままセル範囲を入力するということですが、CHOOSE関数の場合は、後半の引数を文字列で指定する必要があるので、それほど効率的にはなりません。 抽出対象をセルに入力しているならINDEX関数を使ったほうが簡単です。 たとえばG1セルに「その他」G2セル以下G10セルに抽出項目が入力されているなら、たとえば以下の式でそれらの値を抽出することができます(これも配列数式ですが、Ctrl+Shift+Enterで確定しなくても表示できるようにINDEX関数で配列を範囲に変更しています)。 =IF(B2="","",INDEX($G$1:$G$10,(MAX(INDEX(COUNTIF(B2,"*"&$G$2:$G$10)*ROW($1:$9),))+1))) ちなみに、ROWの引数の部分は、抽出データ1から抽出データの数までにしてください。
お礼
私が配列のことがよくわからなくて、教えていただいた関数の内容を 理解するのに、まだ時間がかかっています。 >>No.3までのデータはうまくいったのですが、No.4以降はすべて「その他」と表示されてしまいます。ROWの範囲を($1:$7)にしてもだめでした。 >おそらく、数式を修正した後にCtrl+Shift+Enterで確定していないのではないかと思われます。 こちらは確かにCtrl+Shift+Enterで確定させました。 式も"{"で始まっています。 ROWの範囲を変更すると#N/Aとエラーが出てしまいます。
- KURUMITO
- ベストアンサー率42% (1835/4283)
解答No1です。 最初の■の前にも文字などがあるのでしたらD2への入力の式は次のようにしてください。 =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はどこから出た数字でしょうか。 サンプル例では問題ないのですが、実際のデータでは余計なテキストまで 表示されてしまいます。 また、サンプル例では網羅しきれていなかったのですが、 中カテゴリが1つの行に2種類あります。 たとえば、No.5のように「りんご」ですが 「訳ありりんご」も併記されていたら、 そちらを優先して出したいのです。 なぜ中カテゴリが2つもあるかというと、りんごでも集計したいし、 さらに細かく訳ありとそうでないものの集計も出したりしたいからです。 A B C D 1商品番号 商品タグ 売上金額 中カテゴリ 2No1 ■1234果物■りんご 1000 りんご 3No2 ■56789野菜■きゅうり 2000 きゅうり 4No3 ■1234果物■みかん 1000 みかん 5No4 ■56789野菜■にんじん 2000 にんじん 6No5 ■1234果物■りんご■訳ありりんご 1000 訳ありりんご 7No6 ドリンク 1000 その他 教えていただいた関数では2つ目の■以降の文字までしか出てきません。 中カテゴリが2つあるのは曖昧ですよね。 たとえば中カテゴリが2つあったら1つ目は■、二つ目は▲で始めるよう使いわければうまくいきますか。 6No5 ■1234果物■りんご▲訳ありりんご
- MackyNo1
- ベストアンサー率53% (1521/2850)
特定の文字列の含まれる位置を自由にワイルドカードで指定したいなら以下のような配列数式が使えます(入力後Ctrl+Shift+Enterで確定)。 =IF(B2="","",CHOOSE(MAX(COUNTIF(B2,"*■"&{"にんじん";"きゅうり";"みかん";"りんご"})*ROW($1:$4))+1,"その他","にんじん","きゅうり","みかん","りんご")) 上記の例は■の後に検索文字列があり、その文字列で終了しているケースを対象としていますので、必要に応じて適宜ワイルドカード文字を変更してください。 また{"にんじん";"きゅうり";"みかん";"りんご"}の部分はセルに検索文字を入力しておいて、このセル範囲を絶対参照で指定するほうが簡単な数式になります(ROW関数の中の$1$4の部分もセル参照にするとよい)。
お礼
早速のご回答ありがとうございます。 No.3までのデータはうまくいったのですが、No.4以降はすべて「その他」と表示されてしまいます。ROWの範囲を($1:$7)にしてもだめでした。 {"にんじん";"きゅうり";"みかん";"りんご"}の部分を絶対参照で指定するというのが、やり方がよくわからなかったのですが、もう少し具体的にお伺いしてもよろしいでしょうか。
- KURUMITO
- ベストアンサー率42% (1835/4283)
D2セルには次の式を入力して下方にオートフィルドラッグします。 =IF(B2="","",IF(COUNTIF(B2,"■*■*")=1,MID(B2,FIND("■",B2,2)+1,10),"その他")) 中カテゴリは二つ目の■の次に記載されていることが決まりとなっていれば上の式でよいでしょう。IFで多くの入れ子を使うこともないでしょう。
お礼
早速のご回答ありがとうございます。 実際入力してみると、商品タグには■で始まるとはかぎらないので、 ■の前になにか文字があった場合は、結果がすべて「その他」になってしまい、うまくいきませんでした。 A B C D 1商品番号 商品タグ 売上金額 中カテゴリ 2No1 123■果物■りんご 1000 りんご
関連するQ&A
- エクセル2002(winxp) IF関数を使って、この場合はどうなるでしょうか?
お世話になります。ネストするやりかたがわかりません。 具体的には A B C リンゴ りんご 果物 キュウリ 胡瓜 野菜 サバ 鯖 魚 以下、羅列で1000レコードくらいあります。 ↓ リンゴ りんご 果物 キュウリ☆ 胡瓜 野菜 サバ☆ 鯖 魚 入力の順番は、まずB列に単純に入力します。C列はオートフィルで選んで入力。この段階でA列に TRIM(ASC(LOWER(PHONETIC(B3))) というフリガナ関数を入れているので自動的に出てきます。 問題は、この関数に加えて、 もし、 C列が、野菜か魚なら、A列に ☆を後尾に表示する、というようにしたいのです。 どなたかご教授お願いいたします。
- ベストアンサー
- オフィス系ソフト
- エクセル グループごとの検索について
グループごとを検索し、その情報を縦列に表示したいのですが、悩んでおります。 横に表示するのは、以下の関数を使って出来たのですが、 =IF(ISNA(MATCH(COLUMN(A4)&$A4,sheet1!$E$2:$E$700,0)) これだと使い勝手が悪く、困っております。 例えば (sheet1)の情報を、 品種 品名 金額 産地 果物 りんご1 200 津軽 果物1 果物 りんご2 250 むつ 果物2 果物 みかん 300 果物3 果物 ぶどう 400 果物4 ーーーーーーーーーーーーーーーーーーーーーーーー 海外果物 パパイヤ 300 海外果物1 海外果物 アドカボ 100 海外果物2 海外果物 バナナ 130 海外果物3 ーーーーーーーーーーーーーーーーーーーーーーーー 野菜 なす 100 野菜1 野菜 にんじん 200 野菜2 野菜 きゃべつ 300 野菜3 野菜 大根 150 練馬 野菜4 野菜 大根2 180 京都 野菜5 (sheet2)において [A]又は[B]のある位置のセルに、品種(この場合、野菜)を打ち込むと、 [ A ] [ B ]なす 100 にんじん 200 きゃべつ 300 大根 150 練馬 大根2 180 京都 このように、品名と値段、産地が縦に並んで欲しいのです。 どうぞお知恵を貸して下さいませ。 宜しくお願いします。
- ベストアンサー
- オフィス系ソフト
- 入力規則リスト入力のIF関数
申し訳ありませんが助けてください。 A B 1肉 鶏肉 2 豚肉 3 牛肉 4果物 りんご 5 みかん 6 いちご 7野菜 大根 8 にんじん 9 ピーマン 上記のような表をもとに、A10に入力規則で「肉,果物,野菜」のリスト入力をさせます。そしてB10には、A10が肉だったら「鶏肉,豚肉,牛肉」のリスト入力。A10が果物だったら「りんご,みかん,いちご」のリスト入力させたいと思っています。 そのためB10に入力規則をリスト表示にして、元の値に以下の計算式をいれました。 =IF(A10="肉",$B$1:$B$3,IF(A10="果物",$B$4:$B$6,$B$7:$B$9)) 以上のためしで作ってみた計算式はうまくいったのですが、実際業務上のリストはAが15項目あり、ネストの制限によりIF関数を重ねられません。 他にいい方法がないか、お知恵を拝借したいと思います。よろしくお願いいたします。 ※ちなみに、当方マクロの知識がまったくありませんがマクロで解決できるのであれば勉強しますのでよろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- if関数の入れ子の制限で助けていただきたい。
ゴルフのスコアー表を作っています。各々のメンバーのスコアーを算出し、ランキングを作成したのですが、順位がばらばらになってのランキングされます。そこで1位○●さん2位○△さん・・・最下位○×さんと並べ替えるのにIF関数を使いました。が、入れ子の数がオーバーしてしまい、並べかえられませ。スコアー表は50名分までエントリーできるようにしてあり、A列が会員ナンバーB列が氏名・・・J列が順位。 L列に1位からの並べかえた数字を入れてあり、それを参照するIF関数を=IF(J3=1,B3,if(j4=1,b4,if(j5=1,b5,if(j6=1,b6,if(j7=1,b7,if(j8=1,b8,if(j9=1,b9,if(j10=1,b10,if(j11=1,b11,if(j12=1,b12,if(j13=1,b13,"")・・・・・””)))))))))))))) としようと思ったのですが、どうやら入れ子は7つまでが限界らしいので困り果てました。 どうぞ、お助け下さい。 現在エクセル2000使用。 IPHONEのDocmento To Goで最終的に使いたいと思ってます。
- ベストアンサー
- その他MS Office製品
- Excelでこのような集計はどうしたらいいですか?困ってます!
こんにちは。 以下のような集計を行いたいのですが、どうすればよいかわかりません。教えてください!! Sheet(1) A列、B列、C列 カテゴリ、数、品名 =================== 野菜、1、大根 野菜、2、ねぎ 果物、2、いちご 肉、3、牛肉 果物、1、りんご 肉、2、豚肉 ・ ・ ・ Sheet(2) A列、B列、 カテゴリ、金額 =================== 野菜、300 果物、300 肉、500 ・ ・ ・ 各品物は、100円均一とし、Sheet2でカテゴリ毎に必要な金額を求めます。Sheet1は、およそ1000行ほどあるので、一括で数式で求めたいのですが、どのような関数を使えばよいのかわかりません。 お願いします!
- ベストアンサー
- オフィス系ソフト
- エクセルで対象のものを重複しているのもを省いて1つだけ取り出す方法
エクセルの表で次のようなことをしたいのですが どのようにしたらいいのか教えて下さい。 Aの列に大きな項目、Bの列に小さな項目があるとします。 大きな項目の1種類を選んでそれに該当するB列のものを抜き出したいのですが 重複しているものは1つだけを取り出したいのです。 例えば A B 野菜 にんじん くだもの バナナ くだもの りんご 野菜 じゃがいも くだもの バナナ と、いう表があったとして Aで「くだもの」となっている行のB列に書かれているものをC列に抜き出すとします。 その際、重複しているものは、いくつも抜き出さず1つだけ抜き出したいんです。 つまり C バナナ りんご とする方法です。 重複がない時は オートフィルタでA列でくだものを選びBをコピーし貼り付けを行っていたのですが 重複がある時はそれだけではとても面倒ですし しょっちゅうすることなので、重複がなかったとしてももっと作業を簡素化したいと思っているのですが 簡単に行う方法はありますか? 知っている方がいらっしゃいましたらぜひ教えて下さい。
- 締切済み
- オフィス系ソフト
- 絵にかけそうな野菜・果物
簡単に絵に描けそうな野菜や果物はありますか? たとえばりんご、みかん、にんじん、しいたけ、ぶどう・・・といった感じで 野菜や果物っていろいろあると思うのですがいざ考えてみるとなかなかいいものが思いつかないのでどうぞアドバイスよろしくお願いいたします。
- ベストアンサー
- 絵画・イラスト・デザイン
- Excel:シート間参照し置換したい
初心者です。異なるシート間における表を参照し、置換の ような事がしたいのですが、VLOOKUPが使えるのでしょうか? やりたいのは、下記の通りです。 Sheet1のような表があります。 Sheet2のA列を参照し、Sheet1のB列を変換し、 Sheet3のような結果を得たいのです。 近似値もできるみたいなのでVLOOKUPを使おうと 思ったのですが、「含む」は論理値でどう指定すればいいか 分からず困っています。 そもそもVLOOKUPでできるかも疑問ですし... 教えてください、よろしくお願いします。 Sheet1 A列 B列 12345 りんご、みかん、イチゴ 67891 いんげん 23456 チョコ、あめ 67890 みかん、りんご 56789 あめ Sheet2 A列 B列 りんご くだもの みかん くだもの いちご くだもの いんげん 野菜 チョコ おかし あめ おかし Sheet3 A列 B列 12345 くだもの 67891 野菜 23456 おかし 67890 くだもの 56789 おかし
- ベストアンサー
- オフィス系ソフト
- エクセルの空白を上位のデータで埋める
「エクセル1」のようなフォーマットのデータを「エクセル2」のようなフォーマットに変換したいと思います。具体的には列A、列Bの空白を上位のデータで埋めるという変換です。 データが4万件以上あり、手作業(コピー&ペースト)では膨大な時間がかかってしまうので、大変困っています!目的はMSアクセスにデータをインポートするためです。 簡単に変換するための関数の利用方法やアドオンツールがあれば教えて頂けないでしょうか。 <エクセル1> 列A------列B----列C 食べ物---野菜---にんじん 空白-----空白---たまねぎ 空白-----空白---ピーマン 空白-----果物---バナナ 空白-----空白---みかん ↓↓↓ <エクセル2> 列A------列B----列C 食べ物---野菜---にんじん 食べ物---野菜---たまねぎ 食べ物---野菜---ピーマン 食べ物---果物---バナナ 食べ物---果物---みかん
- 締切済み
- オフィス系ソフト
- 野菜の場合は何から成って実に成るのでしょうか?
果物は桃なら桃の木から実ができて、実は桃と呼ばれる それはみかんだろうがりんごだろうがみかんの木、りんごの木から実ができてそれぞれみかん、りんごになります。 つまり果物は木から実になります。 では野菜の場合は何から成ってきゅうりだとかトマトだとかの実ができるのでしょうか?
- ベストアンサー
- 農学
お礼
お礼が遅くなり大変申し訳ありませんでした。 教えていただいた方法で、文字数がいろいろあることから 余計な文字列まで表示されてしまいますが、あとは検索置換などで 対処すればなんとかなりそうです。 こちらの関数のおかげで、大分時間の手間は省けました。 ありがとうございました。