• ベストアンサー
  • 困ってます

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

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

共感・応援の気持ちを伝えよう!

  • 回答数4
  • 閲覧数340
  • ありがとう数4

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.3
  • Cupper
  • ベストアンサー率32% (2123/6444)

失礼しました。ちょっと失敗。 訂正します。 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))

共感・感謝の気持ちを伝えよう!

関連するQ&A

  • Excel LOOKUP関数で行全体を範囲指定する

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

  • Excel 関数での範囲指定

     Excelでの関数計算は、ほとんど使用した事の無い初心者ですが、もし分かれば教えてください。 あるアプリケーションソフトにパラメーターを投入し実行させるとExcelにて表が出来ます。この表のある列の関連するものを他のある列で指定したものが有るか無いか LOOKUP関数を使用して結果を見ようと思います。しかし、計算を実行するごとに結果を出したい範囲が変わります。簡単に、関数内の範囲指定をする方法は無いでしょうか。    A    B    C    D    E 1  ミカン  100   南      10 2  リンゴ   150   北    15 3  ミカン   200   南      20 4  バナナ   250   南      30 5  ミカン   300   南      55 リンゴ有り 例えば、上のような表が出来今回は、B列の100~200(B列は連続して範囲指定できる)までの範囲を決め A列に「リンゴ」があれば、「リンゴ」とどこかのセルに表示させる。 説明がうまく出来ずわかりにくいかもしれませんがよろしくお願いします。(要は、関数内の範囲指定を他から指定する事は出来ないかなあ)

  • excel 関数の中にセルの引用

    うまく質問できないのですが、宜しくお願いします。 今、最大値を求める関数を使おうと思っていて、次を使います。 =LARGE(B1000:B1150,1) ここで求める範囲を簡単に変えるために、上B列の指定はそのままに、1000や1150の行指定のみを他のセルから持ってきたいと思います。  具体的には、範囲の始まりを指定するセルと、範囲の終わりを指定するセルがあり、例えばそれぞれに200、210と入力すると、 =LARGE(B***:B???,1) という関数の***と???に、200と210が入り、B200からB210までの最大値が求められるといったものです。  このような、方法を実現する表記方法をお教え下さい。よろしくお願い申し上げます。

その他の回答 (3)

  • 回答No.4
  • imogasi
  • ベストアンサー率27% (4595/16450)

質問はLOOKUP関数なれど、VLOOKUPでやってみました。 下記の範囲名を使う方法で、質問のニーズは満たされるのでは?。 (関数で範囲の指定の最下行を可変にすることもできるが、関数が長く難しくなり関数の利用の上級編というところか。 A列に社員番号を入力し、B列に対応した姓を出す例で説明する。 ーーー G1:G1000(1000は大きく取る)を範囲指定する 挿入ー名前ー定義 範囲の名前として「範囲」と定義(どんな名前でも良い) ーーー B列B1に =IF(A1="","",VLOOKUP(A1,範囲,2,FALSE)) と入れて適当な行数まで式を縦方向に複写。 ーー 番号ー氏名の対応テーブルについて、G1:H5である時まで使っていて(G1:H5) 番号 氏名 2 山田 5 植田 7 近藤 9 田中 新入社員が3人入ってきて G1:H8 番号 氏名 2 山田 5 植田 7 近藤 9 田中 11 上田 20 鈴木 34 林 と追加したときに、B列の式は、修正する必要は無い。 ーー 上記例の1000行までしか、セル範囲の定義をしてないことは、表の不使用の上の行に注記しておくと良いかも。 ーー A列の番号が当初設定を超えることになった場合に、B列の式を (当初複写済みの行より)さらに下方向に複写して増やさ無ければならないが、これを自動でやるのは、VBAでも使わないとできない。 こんな質問はいままで無い。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

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

  • 回答No.2
  • Cupper
  • ベストアンサー率32% (2123/6444)

範囲の右側にはデータ以外入力されていないとすると、 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
noname#245936

最近のExcel詳細機能はよく判らないのですが。 貴方の表現したい部分、つまり、別データ一覧がありまして。 キーとなるIDでくっつけて表示云々…というのは、どちらかというと データベースの手法になります。 データベースというジャンルでは、表をテーブル、列をフィールド、 表同士をくっつけることをリレーションと言いまして。 簡単にいいますと縦行がいくら伸びて、データが何百万件になっても 一度決めた処理は常に、いくらデータが増えようと同じく扱えるよう な仕組みになっていることを言います。 一方でExcelのような表計算ソフトでは、基本的にPULL型の参照、 つまりセルに関数を埋め込むと、別セルの結果を「引っ張ってくる」 ことはできるのですが、新たに行が増加したり挿入されたりすると 新しいセルには関数が入っていないのでイチイチ関数を書かないと いけない手間がかかります。 が、ま、こういうジャンルのソフトなんですよね。 ですので、ExcelでリレーションをLookupするなら、あらかじめ 増える予定のデータを入れる入れ子を先に作成しておいて、 それ全部を検索ターゲットに入れるような作り方をする必要が あります。 多分、LookUp参照先の「親テーブル」は別のシートに構えている のがいいんじゃないでしょうか。 Excelはお手軽に変更がかけられる分、データの増減が激しい 恒常的なデータ操作を表現するのがちょっと面倒なソフトです。 マクロ制御などの工夫にてある程度はどうにでもありますが、 それ以上はデータベースということになるでしょう。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

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

関連するQ&A

  • EXCEL2000にて1列内の範囲にてシート間ソート?(LOOKUP?)

    EXCELの関数ではまっています。 1列内の範囲(B2:B7)間に入力された数値(未ソート(3,6,2,8,1,4)を、別のセルで対象数値(2)が有ったら、その数値(2)を表示したいのですが、Lookup系を使ってどうもうまくいきません。  ABCDEF… ABACAD… ――――――――――――――――― 1  2 3 HB    1 2H  3 6 4H    2 4B ←ここの関数を 4 2 4B    3 HB  =LOOKUP(2,B2:B10,B2:B10)  5 8 2B    4 3B   とすると返り値が『1』になってしまいます。 6 1 2H         (他にH,VLOOKUPも試したのですがどうも・・・)  7 4 3B    6 4H 8                  8 2B         希望としては上記のような形にしたいのですがうまくいきません 正確なLOOKUPの使用方法または別の適切な関数が有ったら教えてください。          

  • LOOKUP関数で

    こんばんは。 ある範囲内で右の列を基準にして左側のデータを検索したいのですが、 (VLOOKUPの基準が右になったようなもの) LOOKUP関数ではデータが昇順に並ぶ必要があるなど、 制約が多く上手く求める結果が得られません。 元のデータ(昇順になってません)を触ることなく 右側を基準にVLOOKUPした時のような答えを得るにはどうすれば良いでしょうか? 何か別の関数を指定すればよいのでしょうか? 色々考えてみるのですが、どうも上手く行きません。 ご存知の方がいらっしゃればぜひご教示いただけませんでしょうか? よろしくお願いいたします

  • エクセル lookup関数 等について

    lookup関数 等は、検査値を特定の範囲から探す際、 その特定の範囲内でデータを昇順に並べておく必要が ありますが、昇順に並べなくても良い方法はありますか?

  • EXCELのSUM()関数の範囲指定について

    お世話になります。以前に同様の質問がありましたら、その場所を教えて下さって頂いても構いません。 各セルには、以下の項目が入っています。 A列には西暦、B列には台数が入っています。 現在、2004年まで入っていますが、毎年行を増やして行こうと思っています。 開始年に1993、終了年に2000と入力すると、合計欄には、1993~2000年の合計値600が自動的に入ります。このとき、計算式をB3に入れ、VBA(マクロ)は使わない方法を教えて下さい。 SUM(範囲)関数なのですが、この範囲の指定を任意に変更できればと思っていますが、どう指定すればいいのか、見当も付きません。 A1="開始年"  B1=1993 A2="終了年"  B2=2000 A3="合計"   B3=600 A5="西暦[年]" B5="台数" A6=1990    B6=10 A7=1991    B7=20 A8=1992    B8=30 A9=1993    B9=40 A10=1994    B10=50 A11=1995    B11=60 A12=1996    B12=70 A13=1997    B13=80 A14=1998    B14=90 A15=1999    B15=100 A16=2000    B16=110 A17=2001    B17=120 A18=2002    B18=130 A19=2003    B19=140 A20=2004    B20=150 よろしくお願いします。

  • VLOOKUP関数の範囲をセルで指定したいと思っています。

    VLOOKUP関数の範囲をセルで指定したいと思っています。 B1:参照シート名(VLOOKUP関数で使用する範囲があるシート) C2:範囲の開始位置 E2:範囲の終了位置 B3:G3:列番号(B3の値は、B6:B8の範囲で使用する列番号、 D3の値は、D6:D8の範囲で使用する列番号) これらのセルに入力された情報をもとにB6:G8の範囲にVLOOKUP関数を 反映させたいと思っています。 例えば、 B1に参照シート名「2ケタ」 C2にVLOOKUP関数の範囲の開始位置「A5」 E2にVLOOKUP関数の範囲の終了位置「G7」 B3に範囲の列番号「2」がある場合 B6に「=VLOOKUP(A6,'2ケタ'!A5:G7,2,0)の関数を入力。 B2に参照シート名「3ケタ」 C5にVLOOKUP関数の範囲の開始位置「B5」 E2にVLOOKUP関数の範囲の終了位置「H7」 C3に範囲の列番号「3」がある場合 C6に「=VLOOKUP(A6,'3ケタ'!B5:H7,3,0)の関数を入力。 といった感じです。 現在、B6に「=VLOOKUP($A6,INDIRECT($B$1&"!a5:g7"),INDIRECT("$b$3"),0)」と 関数を入力して、VLOOKUP関数の「範囲の参照シート名」と「列番号」の情報は セルから持ってくることができました。 しかし、「a5:g7」という範囲だけは、INDIRECT関数をうまく入れることができません。 どのように関数を入力したら、上手くいくのか、教えていただけないでしょうか。

  • LOOKUP関数で検査値が見つからない場合の問題

    LOOKUP関数を使用した場合、検査値 が見つからないと、検査範囲 に含まれている 「検査値 以下の最大の値」が使用されますが。 見つからない場合、エラーを返すようにするにはどうすればよいでしょう? 例えば以下のようなシートがあったとします。 1)製品カテゴリシート A       |B カテゴリ番号  |カテゴリ名 105      |コネクタ 106      |ケーブル 201      |パソコン 2-1)データシート A       |B カテゴリ番号  |カテゴリ名 1 105      | 2 150      | 3 105      | 4 105      | 2-1)のデータシートのB列にカテゴリ名を引き出すために以下のような関数を記述したとします。 -------------------------------------------------------- LOOKUP(B2,製品カテゴリシート!A:A,製品カテゴリシート!B:B) -------------------------------------------------------- すると以下のような結果が返ってきてしまいます。 2-2)データシート A       |B カテゴリ番号  |カテゴリ名 1 105      |コネクタ 2 150      |ケーブル←"入力ミスなのにケーブルと表示" 3 105      |コネクタ 4 105      |コネクタ 存在しない"150"番があたかも存在するかのようです。 これでは入力ミスに気づきません。 以下のようにエラーを返す方法はありませんか? 2-3)データシート A       |B カテゴリ番号  |カテゴリ名 1 105      |コネクタ 2 150      |#N/A 3 105      |コネクタ 4 105      |コネクタ

  • エクセル2003のLookup関数について

    検査範囲(D2:D5)に県名のリストを作成し、対応範囲(E2:E5)に例えば人口を入れるとします。 F2セルに県名を入力してG2セルに人口を表示するために、G2セルに[=LOOKUP(F2,D2:D5,E2:E5)]と入力します。ところがこれだけではうまく作動しません。いろいろ実験してみると、検査範囲のデータが昇順に並んでないとだめなようです。そこで検査範囲を昇順に並び替えてやってみたのですが、それでもうまくいきません。県名をローマ字にすればうまくいくのですが、漢字ではうまくいきません。 どうすればいいのでしょうか。Lookup関数以外の方法があるでしょうか。 よろしくお願いします。

  • IF関数とLOOKUP関数の組み合わせについて

    指定したセルと等しい数値(文字列?)をLOOKUP関数を使って別シートのデータベースを検索させて、そのシートに無ければまた別のシートのデータベースを検索させるものを作りたいのですが上手くいきません。 指定したセルが空白なら空白を返すように次のような感じで作っています。 IF(A1="","",IF(LOOKUP(A1,シート1!A1:A15,A1:A15)<>A1,LOOKUP(A1,シート2!A1:A15,B1:B15),LOOKUP(A1,シート1!A1:A15,B1:B15))) VLOOKUP関数でもやってみたのですがこちらも上手くいきません。 IF(A1="","",IF(LOOKUP(A1,シート1!A1:A15,A1:A15)<>A1,VLOOKUP(A1,シート2!A1:B15,2,0),VLOOKUP(A1,シート1!A1:B15,2,0))) ご指摘いただければ嬉しいです。

  • LOOKUP関数について

    現在、次のようなエクセルのシートを作成中ですが、どうしても、上手くいかないものですから教えていただけませんでしょうか。    A    B    C    D 1 7日間  0.7  7日間  0.1 2          15日間  0.2 3          1ヶ月間  0.3 4 2ヶ月間 0.4 5          3ヶ月間  0.5 6          4ヶ月間 0.6 7          7ヶ月間 0.7 上記のシートを作成しているのですが、A1のセルに7日間と打ち込めばLOOKUP関数にて0.1とB1に出力させたいのですがどうしても、7ヶ月間の0.7が出力されるなど、上手く拾ってきてくれないのですが、関数式について教えてください。 ちなみに  =LOOKUP(A1,C1:C7,D1:D7) といった数式を入れてみております。Cの欄などは文字列に設定してみたりもしております。  

  • EXCELのLOOKUP関数に似た関数

    EXCELのLOOKUP関数でベクトル形式の場合、 「検査値が見つからない場合、検査範囲内で検査値以下の最大値が、一致する値として使用されます」 のですが、これと似たケースで 「検査値が見つからない場合、検査範囲内で検査値以上の最小値が、一致する値として使用されます」 のように値を返して欲しいのです。 方法をご存知の方、お力をお貸し願います。

専門家に質問してみよう