エクセル計算について

質問

エクセル計算について

Ａ（施設種別）Ｂ（売上）Ｃ（コード）
施設種別 売上 施設種別コード
売店 2000 1
遊園地 3000 2
映画館 4000 3

エクセル2007で上記のような表を作りました。
Ｃ列欄には以下のような数式が入っています。

=IF(A2="売店",1,IF(A2="遊園地",2,IF(A2="映画館",3,)))

Ａ列欄の施設名を検索して、Ｃ列欄にコードがあらわれるようにしたのです。
そして、数式をＣ列欄のずいぶん下までオートフィルしました。

ところが、うまくできているかどうか確認したくて、売店から4000までのデータを
Ａ列の7行目くらいに「切り取り」して貼り付けると、＃ＲＥＦエラーがでました。

そして、数式にも以下のようなエラーが出ました。

=IF(#REF!="売店",1,IF(#REF!="遊園地",2,IF(#REF!="映画館",3,)))

コピーだとうまくいっているので、なぜ切り取りだとうまくいかないのでしょうか。

初心者ですいません、お願いします。

回答No.2

dark_kappa · ベストアンサー

オートフィルは、コピー元の上下の状態、データの状態を見て、勝手に中身を改編しながらコピーしてくれる機能。
切り取り＞貼り付けは、そういう勝手に改編しながらのコピーはしてくれない機能。

機能の違いです。

切り取り＞貼り付け（または、コピー＞貼り付け）を使うならば、貼り付けた後で式をそのセルに合うように変更する必要があります。

このような場合、VLOOKUP関数などを使って、別表からデータを引用する形式にしておくのが一番間違いがなく、管理も簡単なんですが…。

例えば、こんな感じ。

E列
１行目　施設種別
２行目　映画館
３行目　遊園地
４行目　映画館

F列
１行目　コード
２行目　１
３行目　２
４行目　３

C列
２行目　=VLOOKUP(A2,$E$1:$F$4,2,TRUE)

C列２行目に入れた式をオートフィルで下へコピーすればOK。
逆に、コードを入れれば施設種別が日本語で表示させることも可能です。
上記表の例だと、施設種別が100件だろうが何だろうが、自動的にコード番号を持ってきてくれるようになります。
IFのネスト（入れ子）だと限界がある処理ですし、何よりも一か所変わっただけでIF関数を全部見直す必要があるので、IFは使わないというのがこういう処理の方法のコツです。

回答No.4

KURUMITO

例えばＡ２に売店となっているときにお示しの式がＣ2セルに入力されていればＣ2セルには1と表示されていますね。Ｃ列ではＣ2セルの式を下方にオートフィルドラッグしている状態にしておきます。
そこでＣ2セルと参照元との関係をみるためにＣ2セルを選択してから「数式」タブから「参照元のトレース」をクリックします。Ａ2セルからＣ2セルに向かって矢印が表示されています。
次にＡ2セルを選択して「切り取り」を行い、例えばＡ5セルに貼り付けを行います。するとＣ5セルにはエラーが表示されますね。しかし、Ｃ2セルの値は1のままで変わりません。Ｃ2セルを選んで「参照先のトレース」を選択してください。コピーして貼り付けた先のＡ5セルからＣ2セルに向かって矢印が表示されます。
そのために切り取って貼り付けた後でもＣ2セルの値は1のままになっているのです。従来のＡ5セルは無くなってその場所にはＡ2セルが貼りつけられたと同じ状態になっているのです。
Ａ2セルからＢ2セルを切り取って他の行でも正常な表示になるようにするためには次の式をＣ2セルに入力して下方にオートフィルドラッグすればよいでしょう。

=IF(INDEX(A:A,ROW(C2))="売店",1,IF(INDEX(A:A,ROW(C2))="遊園地",2,IF(INDEX(A:A,ROW(C2))="映画館",3,)))

このような式では参照先のトレースを見ればわかるようにＡ2，B2セルなどは参照元はなっておらず切り取りや貼り付けに影響されない式となっているからです。
それにしてもなかなか気のつかないことをよくご質問くださいました。お陰さまで、こちらも勉強になりました。

お礼

ありがとうございました。

勉強中なのですが、がんばります。

回答No.3

imogasi

質問を読んで感じるのが、IF分をネストして、名称からコードを割り出している点
VLOOKUP関数ぐらいエクセル関数の常識です。それをGoogleででも（VLOOKUP関数で）照会して、勉強し役立てること。
http://www.yoshikawa.co.jp/ybs/skilup/ybs0308.htm
ほか腐るほどたくさんある。
ーー
エクセルの式の複写（セルの貼り付け）の仕組みを勉強のこと。
エクセル関数理解の基礎の基礎。
エクセル関数は、
在るセルに式を入れると計算などしてくれること。
他（近接）セルには、改めて式を打ち込まなくても、オートフィルや貼り付け、Ctrl+ENTERで、引数セルを、決ったルールで変化させて、入れてくれること。
の２点が決定的に便利な点である。
ーー
後者の「決ったルール」を勉強すること。
近接セルとは、より下の行、より右の列で、上方向や、左方向に式を複写は要注意。
この質問の場合C列でA列（C列からして２列前）のデータを参照している式を、A列に貼り付けて持っていくと、A列の２列前に式を直そうと駿河、そんなものは構造上存在しないので、REFエラーにせざるを得ないわけ。（絶対番地に指定して無い場合）。
Googleで「エクセル　式の複写　番地の変化」などで照会し勉強すること。
別件だが
http://www7.ocn.ne.jp/~nsuido/exce.htm　コピーした計算式がうまく動作しない
も参照。

回答No.1

P5Q

Ｃ列に入っている式で、指定されているセルが　Ａ２　なのに

Ａ列に張りつけると、３列分左を指定することになります。

なので、Ａ２のＡ列より左の列が無いため　#REF!　のエラーになります。

式が正しく、エラーにしないためには、

　=IF($A2="売店",1,IF($A2="遊園地",2,IF($A2="映画館",3,)))

　A2の前に　$　を付けつると良いです。