ウォーターサーバーとコーヒーマシンが一体化した画期的マシン >>

こんにちは。いつもお世話になっております。とかく昨日からは幾度か質問させて頂いておりまして、都度ご丁寧な回答を承っており、感謝しております。

さて、早速ですが今度は下記の事で悩んでおります。

◆エクセルバージョン → エクセル2003

◆VLOOKUPの入ったセルをコピーして貼り付けすると “範囲”が自動的に “1”ずつプラスされて式が変わってしまう為、都度、手修正で直している。
 これを改善したい。

【具体例】
SHEET名 = 顧客名一覧 に対して

A1に お と入力すると B1に 小野 と表示される。
A2に か と入力すると B2に 加藤 と表示される。
A3に さ と入力すると B3に 斉藤 と表示される。

ように作成しております。
この 顧客名一覧Sheetの B1 に
 =VLOOKUP(A1,VLOOKUP用シート!A1:B100,2,0)
という関数式を入れているので、これをコピーし、B2~B100まで貼り付けをしたのですが、そうすると

B2には =VLOOKUP(A2,VLOOKUP用シート!A2:B101,2,0) と入ってしまい、
B3には =VLOOKUP(A3,VLOOKUP用シート!A3:B102,2,0) と入ってしまいます。

何故、この数式の中の“範囲”が勝手にプラス1ずつされてしまうのか…これを防ぎたい。

現在は、100個ほどペーストしたものに対して、全て直接入力で“範囲”を正しい VLOOKUP用シート!A1:B100 と修正している状態です。

どなたかお手隙の方いらっしゃいましたら、どうぞご教授の程、お願い致します。

このQ&Aに関連する最新のQ&A

A 回答 (8件)

範囲は相対的な位置で記録されていると考えてください。


そんなわけで
 =VLOOKUP(A1,VLOOKUP用シート!A1:B100,2,0)
を下にコピーすると
 =VLOOKUP(A2,VLOOKUP用シート!A2:B101,2,0)
と下に範囲がずれてくるのです。
右にコピーすると
 =VLOOKUP(B1,VLOOKUP用シート!B1:C100,2,0)
となります。

同じ計算を同列の行や列で繰り返し行なう場合は入力の手間を省くことができるので、便利な機能です。
例:
 C1 =A1+B1
C1セルを下にコピーすると
 C2 =A2+B2
 C3 =A3+B3 

ずれないようにするには、絶対参照の形式で範囲を指定する必要があります。
列方向を固定するには列番号の前に "$" マークを付けます。
行方向を固定するには行番号の前に "$" マークを付けます。
 =VLOOKUP(A$1,VLOOKUP用シート!A$1:B$100,2,0)
これで下へコピーしてもずれることはなくなります。
ただし列番号に "$" マークを付けていないので、横へコピーすると列番号は変わります。
    • good
    • 15
この回答へのお礼

おはようございます。早々にご回答有難うございました。
連日残業で帰宅時間が23時近く、昨夜はそのままくたばってしまった為にお礼が遅くなって恐縮です<(_ _)>

早速本題ですが無事、解決しました!有難うございます。
そして、同じ計算を同列の行や列で繰り返し行なう場合の記載方法まで…嬉しかったです☆
早速試して全てやってみました。これで頭の中、スッキリです"^_^"

また、お礼ポイントですが、みなさんご親切に早急にご教授下さり、全員に付けさせて頂きたい気持ちが本音なのですが
そうもいかないので一番最初にご回答下さった方、そして全回答の中で自身の知識不足に更に刺激を与えて下さった方に
付与させて頂きます事をお許し下さい。

でも本当、助かりました♪重ねて御礼申し上げます。
そしてまた近々(本当に近いと本日中、再び会社からですが…汗)、ご質問させて頂くかも知れません。

たまたまお時間があって、お手隙な時と重なりましたらどうぞ宣しくお願い申し上げます。

お礼日時:2007/03/16 11:59

ああ、自分も失敗してますね。



× =VLOOKUP(A$1,VLOOKUP用シート!A$1:B$100,2,0)
○ =VLOOKUP(A1,VLOOKUP用シート!A$1:B$100,2,0)

追記
範囲を固定するのであれば、範囲に名前を付けて、範囲名で指定する方法もあります。
この場合、シート名を省略することができるので数式を見やすくなります。
・A1からB100セルを選択
・左上の名前ボックスに範囲名を入力(例えば、参照範囲)
・=VLOOKUP(A1,参照範囲,2,0)

設定した範囲は
 挿入 → 名前 → 定義
で、あとから設定を変更できます。
ここで確認すると分かると思いますが、上記の方法で設定した範囲はシート名を含めた絶対参照として設定されています。
    • good
    • 5
この回答へのお礼

度々恐縮です&有難うございます。了解です!大丈夫でした♪
無事、テスト終了してます<(_ _)>

お礼日時:2007/03/16 12:00

NO1です。


数式を間違えましたので訂正します。
=VLOOKUP(A1,VLOOKUP用シート!$A$1:$B$100,2,0)
    • good
    • 1
この回答へのお礼

たびたび有難うございます☆了解ですっ<(_ _)>

お礼日時:2007/03/16 11:55

こんにちは。



えーと、的外れな回答をしてたら申し訳ないんですが、VLOOKUP用に作ったA1:B100のシート範囲を動かないようにしたいんですよね?

それには絶対値指定というのが必要です。

=VLOOKUP(A1,VLOOKUP用シート!A1:B100,2,0)
この式を
=VLOOKUP(A1,VLOOKUP用シート!$A$1:$B$100,2,0)
にしてみてください。$マークはいわば「動かさない」という意味です。エクセルはこのような指定をしないかぎり、相対値になっていて自動的に範囲がずれるようになっています。
    • good
    • 2
この回答へのお礼

おはようございます。早々にご回答有難うございました。
連日残業で帰宅時間が23時近く、昨夜はそのままくたばってしまった為にお礼が遅くなって恐縮です<(_ _)>

早速本題ですが無事、解決しました!有難うございます。
$マークはいわば「動かさない」という意味あい…時折、この$マークを目にする事はあったものの、その意味すら
知りませんでした…。有難うございます!スッキリしました☆

また、お礼ポイントですが、みなさんご親切に早急にご教授下さり、全員に付けさせて頂きたい気持ちが本音なのですが
そうもいかないので一番最初にご回答下さった方、そして全回答の中で自身の知識不足に更に刺激を与えて下さった方に
付与させて頂きます事をお許し下さい。

でも本当、助かりました♪重ねて御礼申し上げます。
そしてまた近々(本当に近いと本日中、再び会社からですが…汗)、ご質問させて頂くかも知れません。

たまたまお時間があって、お手隙な時と重なりましたらどうぞ宣しくお願い申し上げます。

お礼日時:2007/03/16 11:45

=VLOOKUP(A2,VLOOKUP用シート!$A$1:$B$100,2,0)


これで範囲が固定されたと思います

絶対参照と相対参照
http://yokohama.cool.ne.jp/takesima1/excel/zetai …
http://www.nbcom.co.jp/PC-Support/FAQ/xls/ot/xls …
http://www.asahi-net.or.jp/~ef2o-inue/shiki/sub0 …

参照範囲を替えたくないセルの数値の所でF4キーを押して$を付けて下さい。
    • good
    • 1
この回答へのお礼

おはようございます。早々にご回答有難うございました。
連日残業で帰宅時間が23時近く、昨夜はそのままくたばってしまった為にお礼が遅くなって恐縮です<(_ _)>

早速本題ですが無事、解決しました!有難うございます。
F4キーを押す回数によって参照方法を繰り返し切り替えることが出来る…目からうろこです(笑)

また、お礼ポイントですが、みなさんご親切に早急にご教授下さり、全員に付けさせて頂きたい気持ちが本音なのですが
そうもいかないので一番最初にご回答下さった方、そして全回答の中で自身の知識不足に更に刺激を与えて下さった方に
付与させて頂きます事をお許し下さい。

でも本当、助かりました♪重ねて御礼申し上げます。
そしてまた近々(本当に近いと本日中、再び会社からですが…汗)、ご質問させて頂くかも知れません。

たまたまお時間があって、お手隙な時と重なりましたらどうぞ宣しくお願い申し上げます。

お礼日時:2007/03/16 11:42

=VLOOKUP(A1,VLOOKUP用シート!$A$1:$B$100,2,0)



というように範囲を絶対参照に変えてみてください。
    • good
    • 0
この回答へのお礼

おはようございます。早々にご回答有難うございました。
連日残業で帰宅時間が23時近く、昨夜はそのままくたばってしまった為にお礼が遅くなって恐縮です<(_ _)>

早速本題ですが無事、解決しました!有難うございます。

また、お礼ポイントですが、みなさんご親切に早急にご教授下さり、全員に付けさせて頂きたい気持ちが本音なのですが
そうもいかないので一番最初にご回答下さった方、そして全回答の中で自身の知識不足に更に刺激を与えて下さった方に
付与させて頂きます事をお許し下さい。

でも本当、助かりました♪重ねて御礼申し上げます。
そしてまた近々(本当に近いと本日中、再び会社からですが…汗)、ご質問させて頂くかも知れません。

たまたまお時間があって、お手隙な時と重なりましたらどうぞ宣しくお願い申し上げます。

お礼日時:2007/03/16 11:39

コピー元を


=VLOOKUP(A1,VLOOKUP用シート!$A$1:$B$100,2,0)
とすればいいです。
セル指定しているところにカーソルを合わせ、F4を押せば「$」がついて
コピーされても参照先が変化されることはありません。
    • good
    • 1
この回答へのお礼

おはようございます。早々にご回答有難うございました。
連日残業で帰宅時間が23時近く、昨夜はそのままくたばってしまった為にお礼が遅くなって恐縮です<(_ _)>

早速本題ですが無事、解決しました!有難うございます。
F4を押せば「$」がつく…初めて知りました☆何てラクチンなのでしょう(^.^) 助かりました。

また、お礼ポイントですが、みなさんご親切に早急にご教授下さり、全員に付けさせて頂きたい気持ちが本音なのですが
そうもいかないので一番最初にご回答下さった方、そして全回答の中で自身の知識不足に更に刺激を与えて下さった方に
付与させて頂きます事をお許し下さい。

でも本当、助かりました♪重ねて御礼申し上げます。
そしてまた近々(本当に近いと本日中、再び会社からですが…汗)、ご質問させて頂くかも知れません。

たまたまお時間があって、お手隙な時と重なりましたらどうぞ宣しくお願い申し上げます。

お礼日時:2007/03/16 11:11

数式コピーする場合、固定範囲とする表などは、絶対参照としますので、以下の様にセルに$を付加して下さい。


=VLOOKUP(A1,VLOOKUP用シート!$A$2:$B$100,2,0)
    • good
    • 0
この回答へのお礼

おはようございます。早々にご回答有難うございました。
連日残業で帰宅時間が23時近く、昨夜はそのままくたばってしまった為にお礼が遅くなって恐縮です<(_ _)>

早速本題ですが無事、解決しました!有難うございます。

また、お礼ポイントですが、みなさんご親切に早急にご教授下さり、全員に付けさせて頂きたい気持ちが本音なのですが
そうもいかないので一番最初にご回答下さった方、そして全回答の中で自身の知識不足に更に刺激を与えて下さった方に
付与させて頂きます事をお許し下さい。

でも本当、助かりました♪重ねて御礼申し上げます。
そしてまた近々(本当に近いと本日中、再び会社からですが…汗)、ご質問させて頂くかも知れません。

たまたまお時間があって、お手隙な時と重なりましたらどうぞ宣しくお願い申し上げます。

お礼日時:2007/03/16 11:09

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセルで打ち込んだ数字を自動で別シートに表示したい

エクセルでセルに打ち込んだ数字を自動で別シートに表示できる方法があれば、教えてください。

例えば、シート1のC1に5を打ち込んだら、シート2のD2にシート1で打ち込んだ5が自動で表示される。

また1列すべてを自動で表示させる場合、一つのセルの時と違いがありましたら教えてください。よろしくお願いします。

Aベストアンサー

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コピー&リンク貼り付けを使うと便利です。

例)例)シート1のC1~C5に何かを入力したら、
  シート2のD2~D7にシート1で打ち込んだものが自動で表示される

  シート1にあるコピー元のセルを範囲選択して、
  シート2のD2の上で「右クリック」⇒「形式を選択して貼り付け」
  をクリックします。

  そして出てきた小さな画面の左下にある「リンク貼り付け」という
  ボタンをクリックすると完成です。
  試してみてください。。

  念のためにリンク貼り付けを図解しているURLを載せておきます。
  参考にしてみてくださいね。。
  http://www.geocities.jp/office_inoue/excel/eq21.htm

こんばんは。
入力したセルの値を合計とかでなくて、
純粋に別のシートに自動的に表示したいのであれば、
以下の方法があります。

1.1つのセルだけの場合
例)シート1のC1に5を打ち込んだら、
  シート2のD2にシート1で打ち込んだ5が自動で表示される

⇒シート2のD2のセルをアクティブにして「=」を入力
 した後、シート1のC1をクリックする。
 そうするとD2のセルに「=Sheet1!C1」と表示され、値が自動的に
 表示されるようになります。

2.1列全てコピーしたい場合。
  コ...続きを読む

QEXCELで別のシートのデータを参照して返す方法

ちょっとしたことなのですが、うまく式が書けなくて悩んでいます。

作業用のシート1と参照用のシート2があり、
シート1のA列には
 あおき
 あべ
 いぐち
 いはら
などと文字列(名前)が入力されています。
こちらは今度の野球のスタメンだと思ってください。

シート2にはA列とB列があって、
わたなべ 55
いぐち  43
あおやま 67
いはら  41
などと、名前:背番号が羅列されています。こちらが参照用の全選手の背番号リストだと思ってください。

ここで、 シート1のB列に、A列の名前に対応した背番号を返したいのです。
なので、シート1のB列に
IF(シート1のA列の値=シート2のA列のいずれかの値)だったとき、
適合したシート2の行のB列の値を表示する
という式を入れたいのですが、どのように式を書けばよいでしょうか。

すみませんがどなたか教えてください。

Aベストアンサー

私もその場合はVLOOKUP関数を使用しています。

「B1」=VLOOKUP(A1,Sheet2!$A:$B,2,0)

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Q(Excel)あるセルに文字を入力しただけで、同じブック内のほかのワークシートにも、同じ文字が自動的に入るようにするには?

こんにちは。
質問内容はタイトルのとおりです。

あるワークシートのあるセルに文字を入力すると、ほかのワークシートのセルにも同じ文字が自動的に書き込まれる方法を知りたいです(ブックは同じ)。複数のワークシートを制御するには、やはりマクロを使うのでしょうか?

よろしくお願いします。

Aベストアンサー

clam_chowderさん、こんにちは。

Sheet1のA1にたとえば「100」と入力しますね。
Sheet2のA1に、これと同じ数値を表示するには、
 =Sheet1!A1
でOKです。

数式をいれるのが苦手なら、
Sheet2のA1セルで「=」を入力すると、数式入力状態に入りますから、
ここでSheet1のシートタブをクリックし、
リンクしたいA1セルをクリックしてEnterすると、
自動的にさきほどと同じ式が入ります。

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

Qエクセルで重複しているデータの抽出のしかたを教えてください。

エクセルで重複しているデータを抽出したいのですが・・。なにぶん初心者なもので簡単な方法があればよいのですが・・。
A列に日付 B列に企業名このデータが300件ほどあります。

 Å列      B列
1月 1日    AAAA社
1月 4日   BBBB社
1月 8日   CCCC社
2月 1日   BBBB社
2月20日    DDDD社
3月 2日   AAAA社

と、あるとしてB列の企業名が重複しているものを抽出したいのですが・・・。この場合 AAAA社とBBBB社ですが。
これを簡単に抽出する方法はないでしょうか?
教えてください お願いいたします。

Aベストアンサー

企業名がセルB2からセルB300まで入力されていると仮定します。
セルC2に IF(COUNTIF($B$2:B2,B2)>1,"*","") を入力し、セルC300までコピーして下さい。すると重複したデータの二番目以降に "*" のマークが付きます。
(注) 数式を下のセルまでコピーするため、COUNTIF($B$2 は絶対参照にして下さい。

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

QVLOOKUPコピーができない

お世話になります。
あるブック(ファイル)で作ったVLOOKUP()を別のブックへそのままコピーしたいのですが、コピー元のブックを参照する数式が勝手についてしまいます。「形式を選択をして貼り付け」で「数式のみ」をコピーしてもどうしてもできません。別ブックでもVLOOKUP(検索値,範囲,列番号,検索の型)の「検索値」も「範囲」も同じ場所なので数式を写真のようにコピーしたいのですが可能でしょうか。よろしくお願いします。

Aベストアンサー

頭のVLOOKUP関数がコピーできたら、そのセルをコピーすれば済むのではないのですか?

駄目ということなら、数式を一旦文字列にして、コピー貼り付け後、数式に戻すといった作業になります。

VLOOKUP関数セルを範囲選択
編集メニューの「置換」で
=

#
に「すべて置換」て文字列にする。
そのまま選択範囲を「コピー」
コピー先に移動して「貼り付け」
編集メニューの「置換」で
#

=
に「すべて置換」で数式に戻す。

QVLOOKUP関数で得られた値「#N/A」は「0」にならないでしょうか?

 エクセル2000でVLOOKUP関数のことで教えてください。
 利用日6桁、室名を2桁、時間帯を1桁、計9桁の検索値を持った施設利用状況一覧表をつくります。そして、この表をもとに毎日の利用一覧表もつくれるファイルづくりを考えています。
 さて、この施設利用状況一覧表には飛番号が多く、VLOOKUP関数で検索をかけた場合、飛番号分には「#N/A」が返ってくることがわかったのですが、これを「0」(数として認識して欲しい)にする方法がないでしょうか。宜しくご教示の程お願いします。

Aベストアンサー

=IF(ISERROR(VLOOKUP(A1,$H$1:$I$30,2,FALSE)),0,VLOOKUP(A1,$H$1:$I$30,2,FALSE))
でも0に出来ます。0でも何でもセットできるということです。
見つからなかったケースを必ず考えておかないといけないので、VLOOKUPを使う人は、すぐこの関数の組み合わせを勉強(必要とし)ます。
また表(上例ではH列に)見つからないもの(行)を見つけるためにも上記の式を積極的な目的に使うこともあります。式に2度同じVLOOKUP関数が出てくるが、略記する方法をいつも考えているが、その方法は無いようだ。マイクロソフトよ、何とかしてほしい。

Q2つのシート間での重複データのチェック

Excelについて教えてください。
以下の2つのシートがあります。

Sheet1
すずき 03-0000-0000
やまだ 03-1111-1111
たなか 03-2222-2222

Sheet2
03-1111-1111
03-4444-4444
03-0000-0000
03-2222-2222

Sheet1には名称と電話番号、Sheet2には別所から抽出した電話番号のみのデータがあります。
Sheet1のデータの中からSheet2に電話番号があるものだけを知りたいのですが、たとえばSheet1の 各行のC列 に Sheet2に一致する番号があった場合は●等付けるにはどうすればよいでしょうか。

よろしくお願いします

Aベストアンサー

シート1のC列に式を入れます。
=IF(ISNA(VLOOKUP(B1,Sheet2!A:A,1,FALSE))=TRUE,"-","●")

【式の説明】
シート1のセルB1の値が、シート2のA列に、存在する場合は●を、存在しない場合は-をセット。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング