Excel LOOKUP関数を使う際の範囲指定の仕方

質問

ExcelでLOOKUP関数を使いデータ検索したいと思います。
現在のデータはB-Eまでしか入っていないので
LOOKUP(1,B2:E2,B1:E1)
の様に範囲を指定しました。
しかし徐々にF,G,H,I～と横方向にデータが増えていきますと，その都度範囲指定をやり直さなければなりません。
なにか関数かもしくは条件書式などで，（B2:今データがある列までの2行）などという指定はできないものでしょうか。考えついたのは（B2:作成できる最大の列の2行）というような方法です。それより他によい方法はありませんか？
できればデータを増やした時に計算式内の範囲指定部分も増えるというような方法が望ましいのですが，おわかりの方，何かお知恵をお貸しください。よろしくお願いします。

回答No.3

Cupper · ベストアンサー

失礼しました。ちょっと失敗。
訂正します。

A2セルが空白でない時

×　=LOOKUP(1,OFFSET(B2,0,0,1,COUNTA(2:2)-1),OFFSET(B1,0,0,1,COUNTA(2:2-1)))
○　=LOOKUP(1,OFFSET(B2,0,0,1,COUNTA(2:2)-1),OFFSET(B1,0,0,1,COUNTA(2:2)-1))

回答No.4

imogasi

質問はＬＯＯＫＵＰ関数なれど、ＶＬＯＯＫＵＰでやってみました。
下記の範囲名を使う方法で、質問のニーズは満たされるのでは？。
（関数で範囲の指定の最下行を可変にすることもできるが、関数が長く難しくなり関数の利用の上級編というところか。

Ａ列に社員番号を入力し、Ｂ列に対応した姓を出す例で説明する。
ーーー
Ｇ１：Ｇ１０００（１０００は大きく取る）を範囲指定する
挿入ー名前ー定義
範囲の名前として「範囲」と定義（どんな名前でも良い）
ーーー
Ｂ列Ｂ１に
=IF(A1="","",VLOOKUP(A1,範囲,2,FALSE))
と入れて適当な行数まで式を縦方向に複写。
ーー
番号ー氏名の対応テーブルについて、Ｇ１：Ｈ５である時まで使っていて（Ｇ１：Ｈ５）
番号 氏名
2 山田
5 植田
7 近藤
9 田中
新入社員が３人入ってきて
Ｇ１：Ｈ８
番号 氏名
2 山田
5 植田
7 近藤
9 田中
11 上田
20 鈴木
34 林
と追加したときに、Ｂ列の式は、修正する必要は無い。
ーー
上記例の１０００行までしか、セル範囲の定義をしてないことは、表の不使用の上の行に注記しておくと良いかも。
ーー
Ａ列の番号が当初設定を超えることになった場合に、Ｂ列の式を
（当初複写済みの行より）さらに下方向に複写して増やさ無ければならないが、これを自動でやるのは、ＶＢＡでも使わないとできない。
こんな質問はいままで無い。

お礼

すみません。この質問がアップされていないと思い，名前を変えて同じ質問を再アップしてました。再アップの方で教えていただいた名前定義は参考させていただきました。先回共々ありがとうございました。

回答No.2

Cupper

範囲の右側にはデータ以外入力されていないとすると、
A2セルが空白の時
　=LOOKUP(1,OFFSET(B2,0,0,1,COUNTA(2:2)),OFFSET(B1,0,0,1,COUNTA(2:2)))

A2セルが空白でない時
　=LOOKUP(1,OFFSET(B2,0,0,1,COUNTA(2:2)-1),OFFSET(B1,0,0,1,COUNTA(2:2-1)))

入力の有無で範囲を決めるのであれば、入力されているセルを カウント すれば良いわけですよね。
ならば、COUNTA関数で2行目に入力されているセルの数を数えましょう。
その数を元に OFFSET関数で範囲を指定すれば良いと思います。

※COUNTA関数、OFFSET関数の詳細は、Excelのヘルプを参照してください。

回答No.1

noname#245936

最近のExcel詳細機能はよく判らないのですが。

貴方の表現したい部分、つまり、別データ一覧がありまして。
キーとなるIDでくっつけて表示云々…というのは、どちらかというと
データベースの手法になります。

データベースというジャンルでは、表をテーブル、列をフィールド、
表同士をくっつけることをリレーションと言いまして。
簡単にいいますと縦行がいくら伸びて、データが何百万件になっても
一度決めた処理は常に、いくらデータが増えようと同じく扱えるよう
な仕組みになっていることを言います。

一方でExcelのような表計算ソフトでは、基本的にPULL型の参照、
つまりセルに関数を埋め込むと、別セルの結果を「引っ張ってくる」
ことはできるのですが、新たに行が増加したり挿入されたりすると
新しいセルには関数が入っていないのでイチイチ関数を書かないと
いけない手間がかかります。
が、ま、こういうジャンルのソフトなんですよね。

ですので、ExcelでリレーションをLookupするなら、あらかじめ
増える予定のデータを入れる入れ子を先に作成しておいて、
それ全部を検索ターゲットに入れるような作り方をする必要が
あります。
多分、LookUp参照先の「親テーブル」は別のシートに構えている
のがいいんじゃないでしょうか。

Excelはお手軽に変更がかけられる分、データの増減が激しい
恒常的なデータ操作を表現するのがちょっと面倒なソフトです。
マクロ制御などの工夫にてある程度はどうにでもありますが、
それ以上はデータベースということになるでしょう。

お礼

ありがとうございます。
やっぱりデータベースの方がいいんですかね。でもお値段が・・・
シートを別にするという方法はなるほどと思いました。さっそく行ってみます。ありがとうございました。