No.5ベストアンサー
- 回答日時:
ご質問者が別の質問でお示しになった「野菜.xlsx」「果物.xlsx」「魚.xlsx」というファイルが、
仮にCドライブの「教えてgoo」というフォルダにあるとして、別の検索用ブックに「検索シート」がある場合、
検索シートのA2に「シャインマスカット」と入力したときB2に「H-3」が表示される関数は以下のようなものです。
=IFERROR(VLOOKUP(A2,'C:\教えてgoo\[野菜.xlsx]キャベツ'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[野菜.xlsx]にんじん'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[野菜.xlsx]じゃがいも'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[野菜.xlsx]たまねぎ'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[果物.xlsx]りんご'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[果物.xlsx]いちご'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[果物.xlsx]すいか'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[果物.xlsx]ぶどう'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[魚.xlsx]あじ'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[魚.xlsx]いわし'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[魚.xlsx]たい'!$A:$B,2,FALSE),
IFERROR(VLOOKUP(A2,'C:\教えてgoo\[魚.xlsx]マグロ'!$A:$B,2,FALSE),""))))))))))))
INDIRECT関数を使用するともっとすっきりしたものになるかも知れませんが、No.3さんの回答のとおり、INDIRECT関数は対象ブックを開いておかないと使えません。
このため、VLOOKUP関数を使用しました。
「野菜」「果物」「魚」以外のカテゴリーが追加された場合は、その内容を「新カテゴリー名.xlsx」というブックに作成し、Cドライブの同じフォルダに保存した上で、数式に新たに追記していくことになります。
数式の作成にはテキストエディタを活用されると良いと思います。
No.4
- 回答日時:
No2です
>非常に興味があります。具体的に教えていただけないでしょうか?
もとのご質問が抽象的なので、具体的にと言われても限界がありますが・・・
各シートが2列構成と仮定して、まとめシートは(A、B)(C、D)・・・列をセットに考えて、各シートを参照する式を設定しておきます。
単純参照だと、空白欄が「0」表示になってしまうので、空白欄は空白になるような式にしておくほうが良いでしょう。
以上でまとめシートは作成できると思います。
(各シートで項目を変更や追加したりすると、自動的にまとめに反映されるようになります)
(ブックが異なる場合は、両方開いていないと即時反映はされませんが…)
検索は、このまとめシートから検索することになりますが、「奇数列で値が一致する」ことを条件に検索し、ヒットしたセルの右隣にある値を返すようにすれば良いです。
複数条件検索かつ二次元からの検索になるので、配列計算になると思いますが、対象範囲を列全体などにしてしまう計算時間がかかってしまう可能性が高いので、計算範囲はある程度限定した式にしておくほうが良いと考えられます。
面的な表からの検索方法はいろいろあると思いますが、例えば、
AGGREGATE(15,6,(ROW(セル範囲)+COLUMN(セル範囲)*定数)/(セル範囲=検索値)/(MOD(COLUMN(セル範囲),2)=1),1)
を計算すると、ヒットした「行番号+列番号*定数」の値(=nとする)が返されます。
(定数はセル範囲に応じて、適切な値を任意に設定しておけば良いです)
上記の値に対して、MOD(n, 定数)で行番号が、INT(n/定数)で列番号が逆算できますので、これを利用して、INDEX関数で右隣を得れば良いので、
=INDEX(OFFSET(A:A,,列番号),行番号)
で、最終的に求めている値を得ることができます。
全体をひとつの式に纏めたければ、式を組合わせることで(多少長くはなりますが)可能です。
また、ヒットする値が存在しなければ、当然、結果はエラー値になります。
No.3
- 回答日時:
やろうと思えばできますが対象の全てのブックの全てのシートに対し
て数式を一個ずつ作って計算したものを最後に集約することになるか
と思います。 INDIRECTは対象ブックを開いておかないといけなくな
るので使えません。要は1つ検索するのに全シート分の数式を作って
まとめる作業が必要です。
結局のところ情報をブックやシートでやたらめったら分けた時点で数
式では何一つ上手くいかなくなります。検索対象のデータは分けない
のが鉄則です。
どうしても数式でやりたいのなら(それに意味があるかどうかは置い
といて)マクロかクエリの追加でデータを一箇所に集約してからにし
た方がいいかと思います。外部参照を数式で直接やること自体お勧め
できかねます。
「検索対象のデータは分けないのが鉄則」なのはわかります。
検索対象のデータが増えたり減ったり変更したりで把握しにくいので分けていたのですが、、、、、、
やっぱりひとつにまとめないとダメですよね。
No.2
- 回答日時:
こんばんは
完全一致検索でよいのですよね?
INDEX MATCHのみでは難しいと思いますが、シート数が大量でなければ、不可能ではないでしょう。
ひとつのシートで検索して、存在すれば値を、しなければ空白を返す関数を作成します。
後は、それをシートの数だけ連結してしまえば可能と思います。
ブックに仕組みを作っておいても良ければ、直接の検索の式をもっと簡単な式にすることも可能と思います。
例えば、各ブック内のシートをひとつのシートにまとめるように関数式で設定しておきます。
次に、同様にして各ブックのまとめシートをひとつのシートにまとめます。
この「全体まとめシート」から検索するようにすれば、直接の検索の関数式は、ご想像のような
> INDEX MATCH を使用して検索できますか?
という検索に近いものでも可能になると思います。
まとめる際に、全てを1列に並べるのではなく、(項目数可変で並べるのは面倒なので)平面的な表としてまとめた場合は、違う関数を利用する方が検索はしやすくなると思います。
こちらの方法なら、各シート(2列分)をそのまま横に並べる形で表にまとめればよくなるので、「まとめ」の処理の方は簡単になると考えられます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) マクロVBA 1シートをまとめる 閉じ方 初心者 SOS! 1 2022/06/17 14:54
- Excel(エクセル) セルの値と同じ名前のシートのセルの結合を解除する方法を教えてください 3 2022/10/03 10:23
- Excel(エクセル) 【マクロ】マクロが保存されているエクセルとは、別のエクセルブックの全シートの非表示列を再表示したい 1 2022/12/24 20:48
- Excel(エクセル) 【マクロ】マクロが保存されているエクセルとは、別のエクセルのオートフィルターのしぼりをクリアーしたい 2 2022/12/24 08:36
- Excel(エクセル) エクセルでファイル保存時に複数シートのオートフィルタを全て解除したい 1 2023/05/10 13:23
- Excel(エクセル) セルの値からファイルを複数作りたい 2 2022/10/06 12:42
- Excel(エクセル) エクセルでこのようなことはできますか? 3 2022/07/10 19:57
- Visual Basic(VBA) VBA 検索と入力 Excel ブック ぶぶぶ シート ししし 列V 検索対象の列です 最終行は、お 6 2023/05/17 01:40
- Visual Basic(VBA) マクロで最終行を取得したい 4 2023/05/28 12:14
- Excel(エクセル) エクセルで列の行の重複の検索方法を教えてください。 8 2022/10/11 14:51
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
【Excel】 INDEX ,MATCH でいいのか。該当が複数ある場合
Excel(エクセル)
-
特定セルの内容を更新したら、その更新日を自動的に表示する方法について
Excel(エクセル)
-
Excelで重複データの件数ではなく、何番目かを求める方法
Excel(エクセル)
-
-
4
エクセルで条件に一致したセルの隣のセルを取得したい
その他(Microsoft Office)
-
5
vba 2つの条件が一致したらコピーして別シートに値のみ貼り付け
Visual Basic(VBA)
-
6
ExcelのINDEXとMATCH関数でスピル機能?が邪魔をする
Excel(エクセル)
-
7
IF関数の複数条件とINDEX関数とMATCH関数を使った長い関数を短くできる?
Excel(エクセル)
-
8
【vba】指定範囲の中に任意の文字があるときの条件分岐
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
首吊りどこ締めるの
-
至急!尿検査前日にオナニーし...
-
尿検査前日に自慰行為した時の...
-
白血球が多いとどんな心配があ...
-
尿検査の前日は自慰控えたほう...
-
検便についてです。 便は取れた...
-
彼女のことが好きすぎて彼女の...
-
勃起する時って痛いんですか? ...
-
EXCELで条件付き書式で空白セル...
-
腕を見たら黄色くなってる部分...
-
EXCELで式からグラフを描くには?
-
変な話しになります。尿検査で...
-
excelでsin二乗のやり方を教え...
-
エクセル指定した範囲からラン...
-
Excelで""で囲む方法
-
ある範囲のセルから任意の値を...
-
2つの数値のうち、数値が小さい...
-
精子が黄色?
-
エクセルでエラーが出て困って...
-
納豆食べた後の尿の納豆臭は何故?
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
至急!尿検査前日にオナニーし...
-
首吊りどこ締めるの
-
尿検査の前日は自慰控えたほう...
-
尿検査前日に自慰行為した時の...
-
検便についてです。 便は取れた...
-
白血球が多いとどんな心配があ...
-
中出しをするとお腹が痛い・・・。
-
射精をして1週間以内に尿検査を...
-
彼女のことが好きすぎて彼女の...
-
腕を見たら黄色くなってる部分...
-
勃起する時って痛いんですか? ...
-
変な話しになります。尿検査で...
-
これって喉仏ですか? 私は女性...
-
EXCELで条件付き書式で空白セル...
-
男です。昨日の午後3時くらいに...
-
今朝、毎朝の習慣でオナニーし...
-
納豆食べた後の尿の納豆臭は何故?
-
1日前の検尿
-
値が入っているときだけ計算結...
-
精子が黄色?
おすすめ情報
まとめる際に、全てを1列に並べるのではなく、(項目数可変で並べるのは面倒なので)平面的な表としてまとめた場合は、違う関数を利用する方が検索はしやすくなると思います。
こちらの方法なら、各シート(2列分)をそのまま横に並べる形で表にまとめればよくなるので、「まとめ」の処理の方は簡単になると考えられます。
非常に興味があります。具体的に教えていただけないでしょうか?