エクセル関数についての質問です。

質問

エクセル関数ついて質問させていただきます。

あるデータにおいて
1　食品　　　（カレー、ラーメン、焼きそば・・・・）
2　化粧品　　（口紅、マスカラ、ファンデーション・・・・）
3　飲料　　　（烏龍茶、緑茶、コーヒー、紅茶・・・・）

と、グルーピングされています。

エクセル上に

Ａ列　　　Ｂ列
カレー　　　1
口紅　　　　2
マスカラ　　2
紅茶　　　　3
焼きそば　　1
マスカラ　　2
ラーメン　　1　　
烏龍茶　　　3
カレー　　　1　　　　
　・　　　　・
　・　　　　・
　・　　　　・

とデータが入っています。（Ｂ列の数字はそれぞれのグループの数字です。個数ではありません）

そこで質問なのですが、

エクセルのセルＤ1に　”1”という数字を入力するとその下の行に自動的にデータにある”1”の項目（カレー、焼きそば、ラーメン）が表示されるにはどうしたらいいでしょうか？その際ダブっている項目は表示されてもされなくてもかまいせん。（できれば表示されないほうがいいです。）

例
カレー　　（セルＤ4）
焼きそば　　セルＤ5）
ラーメン　（セルＤ6）

のようなイメージです。

業務に必要で困っています。どなたか詳しい方ご教授お願いいたします。<m(__)m>

回答No.5

134 · ベストアンサー

Ｚ列に
=IF(B2="","",IF(COUNTIF($B$2:B2,B2)>1,"",C2&"-"&COUNTIF($C$2:C2,C2)))
という関数を入れ、
Ｄ１セルに１、２，３…を入力し、
Ａ列のコードを　Ｄ２，３，４，５…セルにリストアップするのであれば
=IF(ISERROR(LOOKUP($D$1&"-"&ROW()-1,$Z$2:$Z$1000,$A$2:$A$1000)),"",LOOKUP($D$1&"-"&ROW()-1,$Z$2:$Z$1000,$A$2:$A$1000))
ですかね。
lookup文では、完全一致の引数指定がないので、iserrorでエラー回避の必要がなく、
=LOOKUP($D$1&"-"&ROW()-1,$Z$2:$Z$1000,$A$2:$A$1000)
でも良いのかも知れませんけど。

関数の解説
Z列に入れようとしている関数は、リストアップに必要な個別のコードNo.を振っています。
・商品名がなければ空欄
・商品名が2回以上出てきたら、空欄
・上記2つに当てはまらなければ、分類コードと分類コードが出てきた数を組み合わせた個別コードNoを振りなさい

Ｄ列において、分類コードをＤ１に入れますので、それと分類コードの出てきた数をrow文にて自動発生させ、それを検索値として、vlookupまたはlookup文にて、検索範囲を指定して表示させるようにしています。

-> -> -> -> -> -> -> ->-> -> -> -> -> -> -> ->-> -> -> -> -> -> -> ->
エクセルは、確かに独学です。
いくつかのＨＰや書籍で学び、ＰＣでいろいろ組みました。
ここ「教えてｇｏｏ」におきまして、このような質問がいくつか出てきていると思いますが、index文とmatch文を組み合わせるのが得意な方の方法と　私めのvlookupの方法とが、登場するかも知れません。

　vlookupを使う手法は、確かにオリジナルです。rowを検索値としvlookupが使えたら、並べ替えができるのではと、発想したのが発端です。使用するうちに応用範囲が広いかも知れない…と、重用しています。

お礼

134様
数日にわたり、ご丁寧なご回答本当にありがとうございました。おかげさまで問題も解決致しました。
それにしても、、独学でここまで知識を習得されたのには驚きです。私も少しずつではありますが頑張っていきたいと思います。
また、質問させていただくこともあると思いますがどうぞよろしくお願いします。

回答No.4

134

詳しくないもので…

関数で条件に合うものをリストアップすることは、あまり容易ではありません。今回は特に重複したものを除くという条件まで加わっています。
このため、検索のための計算セル範囲を使用しました。
計算セルを1列使用しましたが、時には2列、3列になることもあります。

コードを使用して…とのアイディアですが、コードをリストアップするという条件でしたら、有効ですが、この質問の場合、商品名（？）のリストアップとなっていますので、特効薬になるという性質のものではないと思われます。

Ｚ列に
=IF(B2="","",IF(COUNTIF($B$2:B2,B2)>1,"",C2&"-"&COUNTIF($C$2:C2,C2)))
という関数を入れ、
Ｄ１セルに１、２，３…を入力し、
Ｂ列の物の名前を　Ｄ２，３，４，５…セルにリストアップするのであれば
=IF(ISERROR(LOOKUP($D$1&"-"&ROW()-1,$Z$2:$Z$1000,$B$2:$B$1000)),"",LOOKUP($D$1&"-"&ROW()-1,$Z$2:$Z$1000,$B$2:$B$1000))

というふうにする方法もあるかもしれません。
ただし、この場合、関数を入力した部分において、最後の商品名が連続で表示されてしまいますので、条件付き書式で、すぐ上と同じ内容の時は、表示をセルの背景色に設定するなどの小技が必要になりそうです。

…おぼろげに、提示した関数がどういう働きをして、リストアップしえるのかを、ご理解いただけていないように感じていたり…!?

お礼

134様。おはようございます。
いつも的確なアドバイスありがとうございます。

＞コードをリストアップするという条件でしたら、有効ですが

とのコメントをいただきましたが、実はコードがリストアップさえできれば一番ありがたいのが本音なのです。もし、その解決方法があれば教えていただけないでしょうか？

＞…おぼろげに、提示した関数がどういう働きをして、リストアップしえるのかを、ご理解いただけていないように感じていたり…!?

お恥ずかしい話です。
おっしゃるとおり、理解できていません。134様は独学で勉強されたのでしょうか？現在は基本的な関数中心にＶＢＡを少し勉強している状態です。

もし、お時間がありましたら、Ｄ1のセルに数字の”1”を入力すれば
自動的にその下の行に
5005
9009
7007
5005←（これはできれば表示されないほうがありがたいです。ダブッテ　　　　いますので。ただ、それが不可能ならば表示されてもＯＫで　　　　　す。）

と表示させることができるのでありばご教授ください。<m(__)m>

私は都内在住ですが、今日は昨日よりは少し涼しい感があります。
お仕事頑張ってください。

回答No.3

134

VLOOKUPがダメだったら、LOOKUPの適用でできそうに思います。

補足

134様おはようございます。昨晩はありがとうございました。
もし、お時間があればまた、ご教授お願いしたいのですが、

上記の元データにおいて

Ａ列　　　Ｂ列　　　Ｃ列　　
5005　　　カレー　　　1
6006　　　口紅　　　　2
3333　　　マスカラ　　2
2002　　　紅茶　　　　3
9009　　　焼きそば　　1
3333　　　マスカラ　　2
7007　　　ラーメン　　1　　
8888　　　烏龍茶　　　3
5005　　　カレー　　　1　　　　
　・　　　　・
　・　　　　・
　・　　　　・

と、実際には商品名の隣の列に４桁の商品コートがあります。（これは商品ごとにふってあり、カレーなら5005、マルカラなら3333であり、変化しません。）

ひょっとしたらこのコードを活用できないかな、と考えています。

もし、お知恵を拝借できましたら、非常にありがたいです。
ご教授の程よろしくお願いします。<m(__)m>

回答No.2

134

別に詳しくはないですけど…

Ａ列に1列挿入します。
Ａ２セルに次のような関数を入れます。
=IF(B2="","",IF(COUNTIF($B$2:B2,B2)>1,"",C2&"-"&COUNTIF($C$2:C2,C2)))
これをＡ列に適当な行数分、関数をコピーします。

Ｄ１セルをということですが、1列挿入したことにより　Ｅ１セルに１、２，…と入力するとします。

Ｅ２セル以降に、リストアップするとします。
Ｅ２セルに次のような関数を入れます
=IF(ISERROR(VLOOKUP($E$1&"-"&ROW()-1,A2:C10,2,0)),"",VLOOKUP($E$1&"-"&ROW()-1,A2:C10,2,0))
Ｅ３より下へ、適当な行数関数をコピーします。

Ａ列は、非表示にしておいてください。

補足

134様御回答ありがとうございます。
早速試してみたところ完璧にできあがりました。
ただ、実際のデータですが、Ａ列に一列挿入できるかどうかが現在確認できませんが、（現在自宅ということと、セルに色々な関数が入力されており一列挿入することにより弊害がでる可能性があるということです。）おそらくその可能性が非常に高いとかんがえられます。

もし、Ａ列に挿入せずに、たとえばＺ列を利用するなどの方法があればご教授願えないでしょうか？ＶＬＯＯＫＵＰ関数を利用しているので難しいかなとは思いますが・・・。

ただ、本当に助かりました。ありがとうございます。<m(__)m>

回答No.1

mitarashi

ピボットテーブルはいかが？ちょっと変則的ですが、下記D1以下がピボットテーブルです。ページのフィールドに分類を、行のフィールドとデータアイテムの両方に品名をドラッグします。
...............A...............B.......C.................................D......E
..1.........品名.........分類....................................分類......2
..2.....カレー................1....................................................
..3.........口紅...............2.........データの個数 : 品名........
..4.マスカラ...............2....................................品名....計
..5.........紅茶...............3............................マスカラ......2
..6.焼きそば................1....................................口紅.......1
..7.マスカラ...............2....................................総計......3
..8.ラーメン................1....................................................
..9.....烏龍茶...............3....................................................
10.....カレー................1....................................................
E2で分類番号を選ぶと、該当する品名が表示されます。

参考URL：

http://kokoro.kir.jp/excel/pivottable.html

お礼

mitarashi様、早速の御回答ありがとうございます。

おっしゃる通りピボットテーブルは一つの手ですね。ただ、できることなら関数で処理できないかなと考えています。というのは実際のエクセルシートには商品のデータの上のセルに他のデータが入力されていて中々うまくいかないようなのです。

非常に丁寧で分かりやすい御回答感謝します。<m(__)m>