
エクセル関数ついて質問させていただきます。
あるデータにおいて
1 食品 (カレー、ラーメン、焼きそば・・・・)
2 化粧品 (口紅、マスカラ、ファンデーション・・・・)
3 飲料 (烏龍茶、緑茶、コーヒー、紅茶・・・・)
と、グルーピングされています。
エクセル上に
A列 B列
カレー 1
口紅 2
マスカラ 2
紅茶 3
焼きそば 1
マスカラ 2
ラーメン 1
烏龍茶 3
カレー 1
・ ・
・ ・
・ ・
とデータが入っています。(B列の数字はそれぞれのグループの数字です。個数ではありません)
そこで質問なのですが、
エクセルのセルD1に ”1”という数字を入力するとその下の行に自動的にデータにある”1”の項目(カレー、焼きそば、ラーメン)が表示されるにはどうしたらいいでしょうか?その際ダブっている項目は表示されてもされなくてもかまいせん。(できれば表示されないほうがいいです。)
例
カレー (セルD4)
焼きそば セルD5)
ラーメン (セルD6)
のようなイメージです。
業務に必要で困っています。どなたか詳しい方ご教授お願いいたします。<m(__)m>
No.5ベストアンサー
- 回答日時:
Z列に
=IF(B2="","",IF(COUNTIF($B$2:B2,B2)>1,"",C2&"-"&COUNTIF($C$2:C2,C2)))
という関数を入れ、
D1セルに1、2,3…を入力し、
A列のコードを D2,3,4,5…セルにリストアップするのであれば
=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を振りなさい
D列において、分類コードをD1に入れますので、それと分類コードの出てきた数をrow文にて自動発生させ、それを検索値として、vlookupまたはlookup文にて、検索範囲を指定して表示させるようにしています。
-> -> -> -> -> -> -> ->-> -> -> -> -> -> -> ->-> -> -> -> -> -> -> ->
エクセルは、確かに独学です。
いくつかのHPや書籍で学び、PCでいろいろ組みました。
ここ「教えてgoo」におきまして、このような質問がいくつか出てきていると思いますが、index文とmatch文を組み合わせるのが得意な方の方法と 私めのvlookupの方法とが、登場するかも知れません。
vlookupを使う手法は、確かにオリジナルです。rowを検索値としvlookupが使えたら、並べ替えができるのではと、発想したのが発端です。使用するうちに応用範囲が広いかも知れない…と、重用しています。
134様
数日にわたり、ご丁寧なご回答本当にありがとうございました。おかげさまで問題も解決致しました。
それにしても、、独学でここまで知識を習得されたのには驚きです。私も少しずつではありますが頑張っていきたいと思います。
また、質問させていただくこともあると思いますがどうぞよろしくお願いします。
No.4
- 回答日時:
詳しくないもので…
関数で条件に合うものをリストアップすることは、あまり容易ではありません。今回は特に重複したものを除くという条件まで加わっています。
このため、検索のための計算セル範囲を使用しました。
計算セルを1列使用しましたが、時には2列、3列になることもあります。
コードを使用して…とのアイディアですが、コードをリストアップするという条件でしたら、有効ですが、この質問の場合、商品名(?)のリストアップとなっていますので、特効薬になるという性質のものではないと思われます。
Z列に
=IF(B2="","",IF(COUNTIF($B$2:B2,B2)>1,"",C2&"-"&COUNTIF($C$2:C2,C2)))
という関数を入れ、
D1セルに1、2,3…を入力し、
B列の物の名前を D2,3,4,5…セルにリストアップするのであれば
=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様は独学で勉強されたのでしょうか?現在は基本的な関数中心にVBAを少し勉強している状態です。
もし、お時間がありましたら、D1のセルに数字の”1”を入力すれば
自動的にその下の行に
5005
9009
7007
5005←(これはできれば表示されないほうがありがたいです。ダブッテ いますので。ただ、それが不可能ならば表示されてもOKで す。)
と表示させることができるのでありばご教授ください。<m(__)m>
私は都内在住ですが、今日は昨日よりは少し涼しい感があります。
お仕事頑張ってください。
No.3
- 回答日時:
VLOOKUPがダメだったら、LOOKUPの適用でできそうに思います。
この回答への補足
134様おはようございます。昨晩はありがとうございました。
もし、お時間があればまた、ご教授お願いしたいのですが、
上記の元データにおいて
A列 B列 C列
5005 カレー 1
6006 口紅 2
3333 マスカラ 2
2002 紅茶 3
9009 焼きそば 1
3333 マスカラ 2
7007 ラーメン 1
8888 烏龍茶 3
5005 カレー 1
・ ・
・ ・
・ ・
と、実際には商品名の隣の列に4桁の商品コートがあります。(これは商品ごとにふってあり、カレーなら5005、マルカラなら3333であり、変化しません。)
ひょっとしたらこのコードを活用できないかな、と考えています。
もし、お知恵を拝借できましたら、非常にありがたいです。
ご教授の程よろしくお願いします。<m(__)m>
No.2
- 回答日時:
別に詳しくはないですけど…
A列に1列挿入します。
A2セルに次のような関数を入れます。
=IF(B2="","",IF(COUNTIF($B$2:B2,B2)>1,"",C2&"-"&COUNTIF($C$2:C2,C2)))
これをA列に適当な行数分、関数をコピーします。
D1セルをということですが、1列挿入したことにより E1セルに1、2,…と入力するとします。
E2セル以降に、リストアップするとします。
E2セルに次のような関数を入れます
=IF(ISERROR(VLOOKUP($E$1&"-"&ROW()-1,A2:C10,2,0)),"",VLOOKUP($E$1&"-"&ROW()-1,A2:C10,2,0))
E3より下へ、適当な行数関数をコピーします。
A列は、非表示にしておいてください。
この回答への補足
134様御回答ありがとうございます。
早速試してみたところ完璧にできあがりました。
ただ、実際のデータですが、A列に一列挿入できるかどうかが現在確認できませんが、(現在自宅ということと、セルに色々な関数が入力されており一列挿入することにより弊害がでる可能性があるということです。)おそらくその可能性が非常に高いとかんがえられます。
もし、A列に挿入せずに、たとえばZ列を利用するなどの方法があればご教授願えないでしょうか?VLOOKUP関数を利用しているので難しいかなとは思いますが・・・。
ただ、本当に助かりました。ありがとうございます。<m(__)m>
No.1
- 回答日時:
ピボットテーブルはいかが?ちょっと変則的ですが、下記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>
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで、Scroll Lockと同じ...
-
エクセルでフィルターした値を...
-
表計算ソフトでの様式の呼称
-
【マクロ】【画像あり】4つの...
-
エクセルシートの見出しの文字...
-
エクセルに写真が貼れない(フ...
-
【関数】3つのセルの中で最新...
-
【マクロ】【画像あり】ファイ...
-
空白のはずがSUBTOTAL関数でカ...
-
【画像あり】【関数】指定した...
-
Dir関数のDo Whileステートメン...
-
【マクロ】excelファイルを開く...
-
【マクロ】【画像あり】❶ブック...
-
【マクロ】既存ファイルの名前...
-
Excel 日付の表示が直せません...
-
エクセルのライセンスが分かり...
-
LibreOffice Clalc(またはエク...
-
【関数】=EXACT(a1,b1) a1とb1...
-
セルにぴったし写真を挿入
-
Excelに貼ったXのURLのリンク...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【マクロ】実行時エラー '424':...
-
エクセルのVBAで集計をしたい
-
Office2021のエクセルで米国株...
-
【画像あり】オートフィルター...
-
vba テキストボックスとリフト...
-
他のシートの検索
-
【マクロ】【相談】Excelブック...
-
【マクロ】【配列】3つのシー...
-
【マクロ】元データと同じお客...
-
【マクロ】数式を入力したい。...
-
【マクロ】左のブックと右のブ...
-
エクセルの関数について
-
エクセルのリストについて
-
【マクロ】変数に入れるコード...
-
エクセルシートの見出しの文字...
-
【マクロ】excelファイルを開く...
-
【関数】3つのセルの中で最新...
-
エクセルの複雑なシフト表から...
-
【マクロ】【画像あり】❶ブック...
-
LibreOffice Clalc(またはエク...
おすすめ情報