エクセル関数ついて質問させていただきます。
あるデータにおいて
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で質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 条件に合った数値の合計を表示させたい関数と条件指定の方法 3 2023/05/13 16:07
- Visual Basic(VBA) エクセルVBAについて 2 2023/01/31 16:21
- Excel(エクセル) エクセルの祝日に色が反映しない 4 2022/05/18 09:58
- 飲み物・水・お茶 茶 4 2022/12/07 00:07
- Excel(エクセル) エクセルで、特定のセルの内容を更新すると、別の特定セルに 更新日付が自動的に表示させる方法はあります 1 2022/11/14 21:03
- Excel(エクセル) 【Excel質問】 「本日の日付」から指定条件を満たす営業日経過後の日数を表示させる関数式 3 2022/06/06 23:28
- Excel(エクセル) エクセルについて質問です 1 2022/12/10 11:49
- Excel(エクセル) エクセルの自動更新のタイミングについて 1 2022/07/20 16:12
- Excel(エクセル) [オートフィルター]機能について 3 2023/02/04 14:32
- Excel(エクセル) エクセルの散布図で新たに入力した値のデータラベルが空欄になる現象 1 2022/04/26 09:31
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelで「時間の足し算」はどう...
-
オートフィルのショートカット...
-
エクセルに詳しい方教えて下さ...
-
勤務表をエクセルで作る際、 最...
-
Excel2010で、今の、Ex...
-
UNIQUE関数が使えないバージョ...
-
Excelについて質問です。 表の...
-
Excelについて質問です。 ・デ...
-
Excelのフォントについて
-
Excel表の文字の幅を狭くしたい
-
Excelについて質問です。
-
エクセルのパスワードの一括解...
-
Excel 2019 での上書き保存につ...
-
EXCELで、関数を使って対象の項...
-
VBA
-
Excelで、10000,20000,30000と...
-
FormulaR1C1の 相対参照式のコピー
-
エクセルのソートについて
-
Excel 連番を入力する方法
-
Excelで投入数、加工数、検査待...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセル詳しい方教えて下さい
-
ExcelでA列をコピーしたいので...
-
エクセルのセル統合について
-
Excelの関数で起きた現象の原因...
-
Excelファイルが閉じられい!
-
Excelのシート背景に不明な文字...
-
エクセルの枠線
-
Excel 領収書発行
-
エクセル関数の使い方を教えて...
-
C列にF列の担当者(A〜)を順番...
-
ピポットテーブルの参照元を別...
-
EXCEl VBA
-
Excelでの判別方法
-
VBAで、サブフォルダにある複数...
-
"りんご"と"みかん"というシー...
-
マクロについて教えてください。
-
EXCELファイルが読み取り専用で...
-
同一セルに入力規則のリストと...
-
100行50列の表で、1~40列でフ...
-
なぜか「Nextに対応するForがあ...
おすすめ情報