マスターSheetにある複数ブロックから検索値と合致するものを検索し、その隣の列を表示させたいです
マスターはプロジェクト名をドロップダウンで選択して、該当の"No."列を絞り込み表示をするドロップダウンを組んでいるため、横に並べざる得ませんでした
リストSheetのB:検索値"No."が、マスターシートのプロジェクトAになければ、B・C・・・と検索し該当する"No."が見つかれば、隣の列の"対象"を表示させるのが希望です
IFとVLOOKを繰り返すのが良いのか、他に簡単な関数があるのか知りたいです
(マスターSheet)
A B C D E F
プロジェクトA 対象 プロジェクトB 対象 プロジェクトC 対象
No.1 ●課 No.2 ▲課 No.7 ×課
No.3 ▲課 No.5 ●課 No.6 ◆課
No.15 ◆課 No.8 ×課 No.10 ●課
No.4 ×課 No.12 ◆課 No.20 ×課
(リストSheet)
A B C
プロジェクト名 No.● 表示させたいセル
↑ ≪検索値≫
ドロップダウンで選択
Windows7
Excel2010使用です
お知恵を貸していただけたら幸甚です
No.3ベストアンサー
- 回答日時:
No.1・2です。
>マスター範囲:C列からN列・・・
「リスト」シートのH9セルに
=IF(COUNTIF(マスター!C:N,G9),INDEX(マスター!C$1:N$1000,SUMPRODUCT((マスター!C$1:N$1000=G9)*ROW(A$1:A$1000)),SUMPRODUCT((マスター!C$1:N$1000=G9)*COLUMN(A$1:L$1))+1),"")
とし、フィルハンドルで下へコピーしてみてください。
※ ここでCOLUMN関数はINDEX関数で範囲指定した何列目を返すか?という数式になりますので
必ずINDEX関数の列数分を必ずA列から指定します。
列数が多すぎてどこまでの列を指定すれば良いのか判りにくい場合は
=IF(COUNTIF(マスター!C:N,G9),INDEX(マスター!C$1:N$1000,SUMPRODUCT((マスター!C$1:N$1000=G9)*ROW(A$1:A$1000)),SUMPRODUCT((マスター!C$1:N$1000=G9)*COLUMN(C$1:N$1))+1-2),"")
でも大丈夫だと思います。
数式の説明は
G9セルと一致する列番号を見つけ出すためにSUMPRODUCT関数を使っていますが、
G9セルのデータを一致した列だけが「TRUE」=1となり、その他の列は「FALSE」=0 ですので
それに配列数式で列番号を掛け合わせるとG9セルが入っている列番号が取得できます。
その一つ右側のセルを表示させたいため、「+1」としています。
後者の数式はC列からINDEX関数で範囲指定した列数分を掛け合わせていますので
×1 からではなく、 ×3 からになってしまいます。
これでは2列ずれてしまうために「-2」としています。
尚、行番号も同じ考え方になりますので、
仮に「マスター」シートの2行目(項目行を含む)以降にデータがあっても
「A1:A1000」のように必ず1行目からINDEX関数で範囲指定した行数分を掛けないと行もずれることになります。
要は、INDEX関数の範囲指定した何行目、何列目を返したいか!というコトです。
この程度でよろしいでしょうかね。m(_ _)m
No.2
- 回答日時:
No.1です。
>マスターの項目がプロジェクトC以降、Fまであるのですが
すなわち、L列までのデータになるのですね。
=IF(B2="","",INDEX(マスター!A$1:L$1000,SUMPRODUCT((マスター!A$1:L$1000=B2)*ROW(A$1:A$1000)),SUMPRODUCT((マスター!A$1:L$1000=B2)*COLUMN(A$1:L$1))+1))
にしてみてください。m(_ _)m
早速のご回答ありがとうございます
何度も後出しになって申し訳ありませんが、マスター及び表示をしたいリストの場所が帳票の一部分のため、COLUMN関数の範囲がこのままで良いのか分からなくなってしまっております
自力でコンバートしておりましたが、COLUMN関数を使用したことがないので迷走中です
実際は
マスター範囲:C列からN列
表示をさせたいリスト側の位置は8列目にタイトルが明記されており、データは9列目から存在します
F8(プロジェクト名) G8(検査値) H8(結果)
プロジェクトA No.3 ▲課 ←表示させたい値
頼りきりになって申し訳ありませんが、この場合のCOLUMN関数の範囲=マスター範囲と同じ列数で良い解釈であっているかも、ご教示いただけると幸甚です
No.1
- 回答日時:
こんばんは!
「マスター」シートのNo.○に重複がないのであれば
「リスト」シートのB列だけの検索で対応できると思います。
↓の画像で「リスト」シートのC2セルに
=IF(B2="","",INDEX(マスター!A$1:F$1000,SUMPRODUCT((マスター!A$1:F$1000=B2)*ROW(A$1:A$1000)),SUMPRODUCT((マスター!A$1:F$1000=B2)*COLUMN(A$1:F$1))+1))
という数式を入れフィルハンドルで下へコピーしています。
※ エラー処理はしていません。m(_ _)m
ご回答ありがとうございます
マスターの項目がプロジェクトC以降、Fまであるのですがその場合も教えていただいた式で対応可能でしょうか
SUMPRODUCT((マスター!A$1:F$1000=B2)*ROW(A$1:A$1000))を式内に増やす形になるのでしょうか
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Access(アクセス) Accessで予定表を作成しようとしてます。 テーブル フィールド名 連番 オートナンバー型 年月日 2 2023/07/23 11:40
- Excel(エクセル) EXCEL 関数を教えてください。(A列の同じ値が複数ある場合vlookupで出来ますか) 4 2022/12/07 20:54
- Visual Basic(VBA) VBAで、1つのエクセルで、2つのシートからもう1つのシートに条件のある転記コードを教えてください。 1 2023/03/16 18:07
- Excel(エクセル) vba シート名の一覧を2列に分けるには 5 2023/04/24 08:56
- Excel(エクセル) EXCELで次の数式を教えてください。 5 2022/11/23 18:33
- Visual Basic(VBA) ユーザーフォーム「frm_基本❶」を立ち上げると新規で入力する行数を右下のNoとして表示しています。 1 2023/03/16 19:02
- Excel(エクセル) Excelヘルプの原文を表示する最速の方法(手順)には? 1 2023/08/11 11:30
- Excel(エクセル) 【Excel】指定のセル内容を基に別シートのセルを検索して選択する【VBA】 1 2022/06/16 16:16
- Visual Basic(VBA) マクロで設定時刻の入力がわかりません 2 2022/03/29 02:24
- Excel(エクセル) Excelで質問です! 現在マクロを勉強中の初心者です。 以下のような表から、会社名が空白のもの以外 2 2022/06/14 12:16
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelについて質問です。 ・デ...
-
Excel2010で、今の、Ex...
-
UNIQUE関数が使えないバージョ...
-
Excelについて質問です。
-
エクセルに詳しい方教えて下さ...
-
Excelで「時間の足し算」はどう...
-
Excelについて質問です。 表の...
-
オートフィルのショートカット...
-
Excel表の文字の幅を狭くしたい
-
勤務表をエクセルで作る際、 最...
-
Excelのフォントについて
-
Excel 2019 のピボットテーブル...
-
エクセルのパスワードの一括解...
-
エクセルのソートについて
-
Excel 連番を入力する方法
-
Excel 漢字二文字の先頭と最後 ...
-
列を増やさずに、月だけの件数...
-
Excelで、10000,20000,30000と...
-
エクセルの検索関数でシート内...
-
Excel 2019 での上書き保存につ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセル詳しい方教えて下さい
-
ExcelでA列をコピーしたいので...
-
エクセルのセル統合について
-
Excelの関数で起きた現象の原因...
-
Excelファイルが閉じられい!
-
Excelのシート背景に不明な文字...
-
エクセルの枠線
-
Excel 領収書発行
-
エクセル関数の使い方を教えて...
-
C列にF列の担当者(A〜)を順番...
-
ピポットテーブルの参照元を別...
-
EXCEl VBA
-
Excelでの判別方法
-
VBAで、サブフォルダにある複数...
-
"りんご"と"みかん"というシー...
-
マクロについて教えてください。
-
EXCELファイルが読み取り専用で...
-
同一セルに入力規則のリストと...
-
100行50列の表で、1~40列でフ...
-
なぜか「Nextに対応するForがあ...
おすすめ情報