カテゴリー
Excel ROW TEXT

Excel 自動で連番を作成する方法

Excelで自動的に連番と採番を作成する方法を紹介します。
連続した数字に対して新たに番号を降るときや整理番号を取得するときに役立ちます。

連番を作成する方法①
オートフィル機能を使った連番作成の方法です。下記のようにA列1と2を記入し、A1とA2を選択した状態で下にドラッグすると自動で連番が取得されます。


一番手間がかからないやり方ですが、行の追加をして番号が飛んでしまったときに手間がかかってしまいます。次の②の方法を使えば番号が飛んでも関数表示による正しい番号作成ができるようになります。


連番を取得する方法②
ROW関数を使用する方法です
=ROW(セル番号)

例:=ROW(A1)
と入力するとA1が何行目であるかを表示します。A1は1行目にあるので1という結果が返ってきます。

このまま関数を作成するとセル番号にある値しか返ってきませんので、関数の後ろに加算させたい値を+させると任意の値を作成することができます。
下記のようにA2から100番から整理番号を取得したい場合は
=ROW(A2)+98
と入力すると、100番から自動で連番を取得してくれます。A2にこの式を作成できたら下にセルをオートフィルで持っていけば自動で連番が作成されます。

もし1番から整理番号を作成したい場合は
=ROW(A2)-1
と入力すると1番から番号作成されます。

式の中身はこのようになっています。

1行追加して番号が飛んでしまったときの対応
上記の表に3行目と4行目の間に2行追加し、セロリともやしを追加しました。するとA列に入力されてた整理番号が抜けてしまいます。

画像に alt 属性が指定されていません。ファイル名: image-31.png

手で入力するならば102と103を入れれば済んでしまいますが、関数を作成しているので再度A2をオートフィルで下に持っていけば自動で番号作成できます。


ROW関数の応用
関数を組み合わせて次の応用ができます。
-001から始まる連番を作成する。
=TEXT(ROW(A1),”-000“)
-000の部分が自動で -001 -002と連番作成してくれます。

2020-001から始まる連番を作成する。
=TEXT(ROW(A1),”2020-000″)
2020は固定で-000の部分が自動で連番作成してくれます。西暦から始まる番号で管理したいときに役立ちます。

カテゴリー
Excel 条件付き書式

特定のセル選択(入力)すると自動的にセルの色を変える方法

エクセルでタスク管理しているときに、作業状況を入力した内容によってセルの色が自動的に変える方法を紹介します。


下記のようなタスクリストを作ったとします。
完了・未完了の列に完了を入れれば灰色・未完了であれば黄色・空白だと白色が自動的に色が変わるようにします。

入力後のイメージ


①リストを作る
No.1から5のタスクを管理する完了・未完了欄のC列にリストを作成します。
C2からC5を選択した状態で
エクセル上部のメニューからデータ→データの入力規則をクリックして下さい。


②データ入力規則の設定
設定画面の入力画面になります。
条件の設定
①入力値の種類を・・・リストに変更

②チェックマーク
✔空白を無視する
✔ドロップダウンリストから選択する
にチェックマークをつけて下さい。

③元の値を・・・完了,未完了を入れて下さい。

設定が終わったらOKボタンをクリックして下さい。

③入力規制の内容を確認
②で設定した入力規制が正しくセットされているか確認します。C2からC5のセルにはドロップダウンによる選択肢で完了・未完了が選択できるようになっています。


また、入力規制でセットした元の値以外の値を入れるとエラー表示がされます。

このエラーが表示されれば正しく設定ができています。エラー表示についてはキャンセルを選択すれば表示が消えます。

④条件付き書式の設定
自動で色が変わるようにする設定をします。
マウスでA2からC6をクリックしてセルを選択した状態を保ちながらホーム→条件付き書式をクリックし、新しいルールをクリックします。

次に新しい書式ルールを選択します。
一番下の数式を使用して、書式設定する説を決定を選択します。
選択すると表示されてたメニューが変わりテキストボックスが現れます。
テキストボックスには下記を入れます。
=$C2=”未完了”

C2の列に未完了だった場合は書式設定するという条件が入ってます。
次に、右下の書式ボタンをクリックしC列に未完了を選択したときに変更する色を選択します。
セルの書式設定のメニューから塗りつぶしのメニューのタブを選択します。背景色を任意の色に設定します。ここでは未完了を選択したときに黄色が表示されるようにしたいので、黄色を選択します。
その他、未完了が選択されたときに他のタブにある表示形式やフォント、罫線を設定すれば自動的に変更されます。
(注意)未完了と選択した行がすべてその書式設定になってしまいす。例えば表示形式で数値から日付に変えた場合はその一行がすべて日付表記となってしまいます。数値が入ってる箇所がすべて日付表記になるので注意してください。


書式設定が終わったら、書式ルールがこのような形になります。


問題がなければOKを押せば設定完了となります。

⑤完了を選択したときの条件付き書式を設定する
④と同じ要領で完了を選択した場合の書式設定を作ります。新しい書式設定で入力するテキストボックスには
=$C2=”完了”
を入力し、書式設定で変更される色を灰色に設定してください。これで設定が完了となります。思い通りの設定になっているか確認してみて下さい。

その他
列や行を増やしたいときの対処
D列や8行目にデータを増やした場合、条件書式は自動的に修正されません。行列を増やしたい場合は、条件書式に設定した範囲内で増やす操作をしてください。その範囲内で行列を増やすと相対的にC列あった条件を増えた列だけ対象範囲を広げてくれます。



カテゴリー
VLOOKUP 関数

VLOOKUP関数 入力ミスを気づきやすくする方法

VLOOKUP関数が完成したものの人為的なミスで、情報入力する箇所(検索値)に、未入力及び検索値に誤った情報を入れるとミスであることを気づきやすくする方法を紹介します。

下記の赤丸のJ5に入れる関数を例として紹介します。


通常の方法
J5には通常のVLOOKUP関数であればこれだけ入れれば作動します。

=VLOOKUP(I5,$D$4:$F$9,2,FALSE)

未入力だけを表示させる方法
もっと便利にする為に、検索値のI5が未記入の場合は未記入であることを表示させたい場合は次の関数を追加します。

IF(I5=””,”未記入”,VLOOKUP(I5,$D$4:$F$9,2,FALSE))

赤字の部分が追加した関数です。
IF関数を追加しています。
もし、I5に値が入っていなければ未記入(真の場合)と表示させ、値が入ってた場合(偽の場合)はVLOOKUP関数を処理するという内容になっています。赤字の未記入と記載されている部分の文字を任意の表記にしても問題ありません。値に絶対入力が必要な場合に役に立ちます。入力漏れがなくても支障がない場合は未記入となっている部分は、””にすると検索値欄(I5)が空白だったときに♯M/Aではなく空白の値を返してくれ見栄えが良くなります。


データベースに載ってない値を入れた場合は”情報入力誤り”と表示させる方法

=IFERROR(VLOOKUP(I5,$D$4:$F$9,2,FALSE),”情報入力誤り”)

赤字の部分が追加した関数です。IFERROR関数を追加しています。もし、I5に検索値のエラーがあった場合は”情報入力誤り”と表示させるという内容になっています。赤字の”情報入力誤り”と記載されている部分を任意の表記にしても問題ありません。また”情報入力誤り”という部分を無表記 ”” だけにすると、エラーがあった場合は空白として表示するようになります。

検索値が未入力の表示かつデータベースに載ってない値を入れた場合に”情報入力誤り”と表示させる方法

上記2つの関数を組み合わせたものです。
=IFERROR(IF(I5=””,”未入力”,VLOOKUP(I5,$D$4:$F$9,2,FALSE)),”情報入力誤り”)

黒文字→本来のVLOOKUP関数
赤文字→空白だった場合に未入力と表示させる部分の関数
青文字→データベース上に存在しない値があった場合に情報入力誤りと表示させる

入力を絶対に間違えたくない場合はこの関数を使えば入力漏れミスを減らせると思います。

カテゴリー
VLOOKUP 関数

誰でもできるVLOOKUPをマスターしよう(サンプルデータ配布)

EXCELの基本となるVLOOKUPをマスターして、仕事の業務効率化をしましょう

VLOOKUP(ブイルックアップ)は指定した値を参照し、すぐ隣にあるデータを引っ張ってくる関数です。この関数を使いこなせば1つの値を入れればデータを複数個飛ばすことができるのでデーター入力の効率化ができます。

用途
・情報整理
・売上票の作成
・データーベースからの参照

関数

=VLOOKUP(①検索値, ②範囲, ③列番号, ④検索方法)

関数の説明
①検索値 入力したデーターの参照元になる値を入れます。
②範囲  データーベースの表の範囲を指定します。
③列番号 データーベースの左から数えた番号を入れます。
④検索方法 FALSEまたはTRUEのどちらかを入れます。

サンプルとしまして、商品情報を入れれば自動で売上金額を自動で計算される表を用意しました。

こちらをクリックしてダウンロード


サンプルでの表の見方
データーベース部分

左側にある部分がデーターベースの部分になります。

VLOOKUP関数が入っている箇所は右側のデーター入力箇所の黄色に塗ったところになります。

茶色の情報入力欄の部分に各項目を入れると自動で黄色の値(商品名と単価)が表示され、緑色の金額が自動で計算されるという仕組みになっています。

黄色の表のL5にはこのようにVLOOKUP関数を入れています。

=VLOOKUP(K5,$D$4:$F$9,2,FALSE)
これを分解すると・・・
①検索値 →K5
セル番号K5に入っている商品コード1と、範囲で合致した値を検索する。

②範囲  →$D$4:$F$9
データーベースの表D4からF9までの範囲までを参照する。

③列番号 →2
データーベースの2列目の値(商品名)を出すこと。

④検索方法→FALSE
一致しないものはエラーを表示する。

としています。

隣のM5には・・・
=VLOOKUP(K5,$D$4:$F$9,3,FALSE)
が入っています。

①検索値 →K5
セル番号K5に入っている商品コード1と、範囲で合致した値を検索する。

②範囲  →$D$4:$F$9
データーベースの表D4からF9までの範囲までを参照する。

③列番号 →3
データーベースの3列目の値(単価)を出すこと。

④検索方法→FALSE
一致しないものはエラーを表示する。

関数の引用の画面だとこのような形になります。

L5とM5の関数が出来上がれば、オートフィルで下に式をコピーすれば検索値が自然と移動するので簡単にできあがります。

・データーベース作成の注意点
①検索値の値を左側のセルに持ってくること
データーベースを作成するには次の注意点が必要です。VLOOKUP関数では①検索値から右側にある値しかもっていきません。そのため、①検索値になるようなコード類は必ず左側に持っていき、必ず②範囲指定で最左にするようにしてください。検索値はデーターベースと入力項目で一致していれば記号でも文字列でも結果が返ってきます。結果が返ってくるといっても検索値にするのはできるだけシンプルな数字や英語を使ったコードにすると良いです。

②データーベースの検索値部分は同一の値を存在させないこと
データーベース内に同一のコードが存在すると、VLOOKUP関数の性質から上位にあるコードを優先して値を返すようになっています。重複させないようにしてください。

このようにデーターベース部分にコード2と書いたバナナとレモンがあったときに、データーべースから返ってくる値はデーターベース上部にあるコードのバナナを優先して値が返ってきます。レモンには他のコードとは重複しないコードを与えれば正しい結果が返ってくるようになります。

・VLOOKUP関数でよくあるエラー
TRUEとFALSEの使い分け
FALSEを使う場合
完全にデーターベースと一致するコードを参照したい場合はFALSEを使ってください。

TRUEを使う場合
検索値に近いコードで結果を求めている場合はTRUEを選択してください。(TRUEを使う用途があまり思いつかないです。)
試しにサンプルのL5の関数にて④検索方法部分をFALSEからTRUEに修正し、K5を2.4にすると2に近いバナナが表示されます。FALSEだとエラー表示になります。この幅は2.0~2.9の値であればすべてバナナが表示されます。こういった不確定な幅で検索結果を求める場合はTRUEを使うと良いです。

・②範囲指定がずれる
同じデーターベースを参照して関数を作る場合は、②範囲の指定は他のセルにコピーしたときでも、範囲がズレないように②範囲に入れたセルの部分を&で囲うと便利です。

・③列番号の指定
列番号を指定を誤るとVLOOKUPの値の結果が誤った値が出てきてしまいます。サンプルのようにデーターベースの上に列番号を表記しておくと便利です。データーベースが20列になるとゴチャゴチャになってしまいますからね。

VLOOKUP関数と他の関数を組み合わせて使う
#N/Aの表示を無くす
①検索値にある値の箇所に数値が入ってないとこのようにエラー表記になってしまいます。表の作りとしては問題ありませんが、見栄えが悪いです。

これを回避するために、VLOOKUP関数と組わせでIFERROR関数を組み合わせると①検索値が空白であっても#N/Aが表示されなくなります。
サンプルデータのL5を
=IFERROR(VLOOKUP(K5,$D$4:$F$9,2,FALSE),” “)
にすると#N/Aの表示が消えます。
これはエラー表示だった場合にどのような表記をするかという関数を組み合わせた結果でエラー表示を消しています。
エラー部分を空白としていますが、上記の組み合わせ関数で赤く塗った箇所を
=IFERROR(VLOOKUP(K5,$D$4:$F$9,2,FALSE),”記入なし”)
とすると、無記入の①検索値に対しては記入なしと自動で表示されます。

カテゴリー
LEFT LEN MID RIGHT 関数

セル内の特定・不特定の文字を取り出す関数

エクセルから文字を切り取って別のセルに貼り付ける方法を紹介します。別のセルに切り取るには特定の文字だけを削除する方法、不規則の文字列から抽出する方法があります。この関数を使いこなせば表記ズレを直したり、一括で文字を修正するのに役立てます。


セル内の特定した文字だけを取り出す場合

例:メールアドレスの一部(@以降)を切り取りたい場合


@example.comを削除
下記の表でメールアドレスの@以降のドメイン部分を切り取りたい場合は置換機能で切り取ることができます。

①削除したいセルの範囲をマウスでドラッグする。



②検索と置換機能を使う
キーボードのコントロールボタンとHボタンを同時に押して検索と置換機能を呼び出して下さい。

③削除する
検索する文字列:@example.com
置換後の文字列:
空白にします。
この入力が終わったらすべて置換をクリックします。


④削除後の内容確認
置換が終わったら削除できてるか確認してみて下さい。


同一の内容であればこのような一括削除が可能です。
置換後の文字列:に任意の文字を入力すれば、検索する文字列から置き換えすることができます。ドメインの変更があった場合に役に立ちます。



不規則の文字を取り出す場合
特定の文字列ではなく、不規則に並んでいるセルうち、指定した範囲の文字を取り出します。

取り出す前に指定した文字がセル内の何文字目に存在するか確認する必要があります。セル内に記載された内容を読み上げれば数えられますが、関数によって探すことができます。

=FIND(”検索したい単語”,文字数目を表示したいセル)
=FIND(“o”,B3)
”o”→セル内に含まれているoを検索する。大文字と小文字は区別されてます
B3→B3の中身を検索する。
関数の入力結果は16と表示されました。
1文字目から検索開始して16文字目にoという文字が含まれてましたのでこのような結果となります。

下記の例でoが含まれてるのを抜き出すとこのような結果になります。

また、文字は1文字だけではなく単語を入れても検索可能です。
co.jpを検索文字とした場合は下記のような結果となります。
結果に♯VALUE!となっている場合は検索単語が含まれていないという意味になります。



セル内の検索したい文字数目が判明したら抜き出す情報が手に入りました。抜き出すのに使用する関数は
①LEFT関数
②RIGHT関数
③MID関数
になります。

例 URLを記載したセルからそれぞれの関数を使って抜き出す。

①LEFT関数
セルに記載されている文字から左から数えて○個目の文字まで表示する関数
=LEFT(指定したセル,抜き出す文字数)

URLからhttps://の部分を抜き取る場合は次のような式になります。

=LEFT(B3,8)
B3にあるセル→https://www.yahoo.co.jp/
文字数→8文字

FIND関数でwwwを検索値にすればwにある文字数が9文字目と結果が返ってきますので、その1文字前のLEFT関数内では文字数8と入力すれば良いということがわかります。


②RIGHT関数
セルに記載されている文字から右から数えて○個目の文字まで表示する関数
=RIGHT(指定したセル,抜き出す文字数)

URLから右から数えて10文字目以降の文字を表示させる
=RIGHT(B3,10)
B3にあるセル→https://www.yahoo.co.jp/
文字数→10文字
その結果、 hoo.co.jp/ が表示されます。

③MID関数
指定した文字以降から指定した文字数までを取り出して表示する関数
=MID(指定したセル,抜き出したい文字数目,抜き出したい文字数)

FIND関数で抜き出したい文字を検索値にして何文字目に存在するのかを調べておくとMID関数を入力するのが楽です。

例 www以降の文字を抜き取りたい
=FIND(“www”,B3)
と入力し、結果が9と返ってきます。
なので、MID関数に入力する抜き出したい文字数目には9と入力すれば良いと言うことがわかります。

URLから9文字目以降の文字を表示させる
=MID(B3,9,1000)


B3にあるセル→https://www.yahoo.co.jp/
9→B3にあるセルの9文字目
1000→セル内の1000個までを取り出す。
1000は例です。セル内の値を超えた分は表示されず、表示可能な中身が全て表示されます。10とすれば10文字分取り出します。
10文字の場合は www.yahoo. となります。
半角・全角関係なく1文字は1文字として取り出します。

この関数を使う場合、上記の特定の文字を削除すると同じような結果になります。不特定の文字が並んでいる内の○文字以降を抜き出したいという場合はこの関数が有効的に使えます。


おまけ
セル内の文字数が何文字入っているかを調べる関数

=LEN(調べたいセル)
先程のB3の文字数を調べるときは・・・
=LEN(B3)
と入れます。すると24という数字が返ってきますので、この数字が文字数を表しています。

FIND関数でwwwを検索値にすればwにある文字数が15文字目と結果が返ってきますので、その1文字前のRIGHT関数内では文字数10と入力すれば良いということがわかります。

カテゴリー
関数

エクセルテクニック 関数の中身を分解し結合して正しい関数を作る方法

エクセルで関数を作成しているときに、引数によってはセルのドラッグしたときに自動で引数の数値が変化してくれないものがあります。
例えば、VLOOKUP関数だと列番号が自動で数値が変わらず固定した数値になってしまいます。10個程度の関数ならば1個ずつ手で直すことはできますが、50個や100個になると手で直すのが面倒くさいです。せっかくのエクセルなのにここは手作業なの?と思うかもしれますが、原始的な方法にはなりますが関数の中身を一度分解して、再度結合することで思うように関数を作成することができます。

作成例
下記の個人情報を整理するためのVLOOKUPを関数を分解して作成する

データはダミーのものです。
http://kazina.com/dummy/
のサイトよりデータ出力しました。


作業エリア
ここの黄色のエリアに全ての関数を作ります。

作業手順
①S5に通常のVLOOKUP関数を作ります。

画像に alt 属性が指定されていません。ファイル名: image-46.png



=VLOOKUP(R5,$B$4:$M$21,2,FALSE)

中身
検索値:R5 固定する必要ありません
範囲:B4からM21を絶対参照にする
列番号:2 
検索方法:FALSE

この状態でS5を右のセルにコピペするとこんな感じになってしまいます。

この状態ではダメなので次のステップで式を分解して式を作成します。

②式の分解

作業エリア外の余白にVLOOKUP関数を分解したセルを作成します。

分解前
=VLOOKUP(R5,$B$4:$M$21,2,FALSE)

これを・・・

S25
=VLOOKUP(R5,$B$4:$M$21,

=の前にある ’ ←クオーテーションは必須です。
これがないとエクセルは式の一部として認識してしまいます。

T25
,FALSE)

を入れます。
分解前にあった列番号は次のステップで挿入します。

③列番号の作成

余白の23行目に挿入したい列番号を数字を並べます。


④セルの結合
この並べた数字の2の下(T24)に次の式を挿入します。

$S$25&T23&$T$25

式の中身
$S$25→②で作成したS25です。
&T23&→23行目に作成した列番号とS25とT25を文章として結合するものです。
$T$25→②で作成したT25です。

これを挿入すると式の計算結果が文字列として

=VLOOKUP(R5,$B$4:$M$21,2,FALSE)

という結果が返ってきます。
この結果が正しければ、T24のセルをコピーして23行目に作成した列番号12まで作成していきます。
するとこのようなものができあがります。


⑤セルのコピペ
これをこのまま作業エリアに値の貼り付けたくなりますが、貼り付けても式を貼り付けただけになてしまいます。
24行目に作成した式を全てコピーして、Windowsに入っているメモ帳を起動し、そのまま貼り付けをして下さい

貼り付けが終わったら、また貼り付けしたものを全て選択してコピーします。その後に、作業エリアのT5に貼り付けしてみて下さい。すると作成した式がVLOOKUP関数として認識され正しい値が返ってきます


⑥作業エリア 下のセルにコピペ
S5からAC5を選択した状態で21列目まで式をコピペして下さい。一気に作成できます。作業エリアの完成です。


関数を分解して結合するテクニックは他の関数でも使えるテクニックになります。難しそうにみえますが慣れれば、すぐに作成できるようになります。このテクニックを使いこなせば巨大な関数を作ったとき苦ではなくなります。

カテゴリー
VLOOKUP 関数

VLOOKUP関数 複数の検索値条件で関数を作成する方法

VLOOKUP関数を作成したときに、基本では1つの検索値でないと検索できないですが、関数にちょっとした工夫を加えれば複数条件による検索が可能になります。

この表で実現したいこと
黄色の情報入力欄にて商品名(H列)と銘柄(I列)を入力したらVLOOKUP関数にてコード(J列)・単価(K列))・生産地(L列)を自動で表示させたい。

実現させる方法
通常のVLOOKUP関数では1つの条件でしか検索をかけることができません。VLOOKUP関数に検索値の欄にHとIを合体して参照する式を作成し、データーベース上に商品名を合体した式を作成すれば実現することができます。

データーベースの準備
VLOOKUO関数の性質上、検索値にあたる部分には他のコードと重複しない項目が必要になります。データーベースを一通り作成したら、固有コードを作る為にA列にはB列とC列を合体させた列を作ります。
A4にはセル同士を結合する =B4&C4 を入れています。このセルをA10までコピペしたら準備完了です。


複数検索を実現させた式
セル番号:J4にはこのような式が入っています。
=VLOOKUP(H4&I4,$A$4:$F$10,4,FALSE)

検索値:H4&I4
&を使ってセルのH4とI4を合体させています。これをすることで商品名と銘柄をセットにして検索値として認識してくれます。

範囲:$A$4:$F$10
A4からF10を絶対参照で指定

列番号:4
データーベース4列目のコードを飛ばす

検索方法:FALSE
完全一致した値を出す


複数検索かつ未入力と情報入力誤りを表示させた式

過去の記事でVLOOKUP関数にて検索値に未入力を表示させ、情報入力誤りを自動で表示させる式を組み合わせた式を紹介しました。
これに対応させた式はこのようになります。
=IFERROR(IF(H10=””,”未入力”,VLOOKUP(H10&I10,$A$4:$F$10,4,FALSE)),”情報入力誤り”)

H10が無記入だった場合は、未入力と表示され誤った情報が入ってたら情報入力誤りと表示されます。サンプルデータの10行目J・K・Lにこの式を組み込んでますので参考にしてみてください。


未入力の場合

情報入力誤りの場合

複数検索をかけるときのコツ
複数検索する場合は情報入力欄が2箇所になってしまいます。2箇所作りたくなければ、データベース上に簡単なコードを1列作ってしまうのも一つの方法になります。単純に上から順番に12345と番号を振っていくだけでも十分OKです。VLOOKUP関数の性質上、他のコードと重複してなければ結果として返ってきますので状況に応じて楽な手段を選んでみてください。

カテゴリー
VLOOKUP 関数

VLOOKUP関数を使うときに覚えておくと効率的になるテクニック

VVLOOKUP関数を使ったエクセルのシートを作るときに覚えておくと便利なテクニックを紹介します。

テクニック1
データベースシートを専用に作る
大きなデータベースを使って作業をする場合はデーターベース専用のシートを作ると良いです。
データーベースの隣に作業スペースを作っても良いですが、データーベースエリアは一度データをおいたらほとんど手を触れない場所になります。作業エリアで作業している間にうっかりとデーターベース部分のところに触れてしまったら時間の無駄になってしまいます。

テクニック2
列の絶対参照・行の絶対参照を使いこなす

列の絶対参照
下記の画像のようにC列の下に関数をコピペするときに使うときに便利です。
列の絶対参照が入っている部分はこの$の部分です。

式の中身
=$B2*$D$1
$B2→本体価格の18900円
$D$1→10%

これがあることで下にコピペしてもB列の下にコピペしてもB3やB4にも正しい結果が返ってくるようになります。

行の絶対参照
横に対して絶対参照をかける方法です。下記の画像のように右にある単価と本数をかけた場合に表を作成したものです。

E5の式の中身
=$C$5*E$2
$C$5→本数を固定
E$2→単価の100円を指している

E$2の部分を行参照にしています。この状態で右のセルにコピーしていくと自動的にそれぞれの単価で本数をかけた結果が表示されます。


VLOOKUP関数の場合だと検索値の部分で列と行の絶対参照が有効です。

テクニック3
関数の一括置換して効率的に式を作る
下記の例で関数に対して一括置換をかけることで簡単に黄色のエリアに正しいVLOOKUP関数を作成することができます。

①検索値のセルを固定する
=VLOOKUP($I$5,$D$4:$G9,2,FALSE)
上記の赤丸の部分にこのような式を作成します。

②J5を右にセルコピーする
コピーすると自動でK列・L列に式が挿入されます。

③式を修正する
列番号が正しくなるように修正します。
K列(修正前)
=VLOOKUP($I$5,$D$4:$G9,2,FALSE)
K列(修正後)
=VLOOKUP($I$5,$D$4:$G9,3,FALSE)

L列(修正前)
=VLOOKUP($I$5,$D$4:$G9,2,FALSE)
L列(修正後)
=VLOOKUP($I$5,$D$4:$G9,4,FALSE)

修正したあとはこのようになります。

④式に対して置換する
J・K・Lに入っている関数に対して一括で置換します。
キーボードのコントロールボタン、シフトボタン、アットマークボタンを同時に押します。押すと、式の中身が表示されます。


⑤一括置換する
黄色エリアにある式に対して一括置換します。
今、これらの式が入っています。
J5
=VLOOKUP($I$5,$D$4:$G9,2,FALSE)
K5
=VLOOKUP($I$5,$D$4:$G9,3,FALSE)
L5
=VLOOKUP($I$5,$D$4:$G9,3,FALSE)

検索値の$I$5が入っている為、下にドラッグしたら全てI5を参照してしまい5行目に入ってる式と全く同じ結果になってしまいます。ドラッグしたあとに1個ずつ直すことで正しい結果を出すこともできますが、これが100個や1000個になれば手で直す事は困難になります。ここで一括置換をすれば簡単にドラッグ可能な式を作成することができます。J5・K5・L5をドラッグで選択し、コントロールボタンとシフトボタンとアットマークボタンを同時に押します。このような感じでセルが式の中身を表示するようになります。

次に、コントロールボタンとHボタンを押して置換メニューを表示させます。

メニューを表示したら
検索する文字列→ $I$5
置換後の文字列→ I5
を指定しすべて置換をクリックします。

置換後は閉じるを押して下さい。
J5・K5・L5に入っていた式の一部が$I$5からI5になっているはずです。これを確認したら再度コントロールボタンとシフトボタンとアットマークボタンを同時に押します。これで元に戻ります。

⑥セルのコピー
J5・K5・L5を下のセルにコピーして下さい。
これで一括で式の作成が完了となります。

応用すれば他の関数でも活用できます。複数の式の置換があればこのテクニックを使ってみて下さい。

カテゴリー
VLOOKUP 関数

VLOOKUP関数のエラーを表示させないようにするコツ

VLOOKUPでエラー表示が出たとき下記のチェックをしてみてください。

#N/Aが表示されてしまう。

原因① データーベース上に値がない。
完全一致もしくは近似値の値が見当たらない可能性があります。

→対応
範囲指定した中に検索値が含まれているか、検索値が左側のセルに存在するか確認してください。下記の例だと検索値にデータベース上に存在しない商品コード9・8・7を入力していることが原因です。データーベース上に9・8・7のコードをもたせたデーターを追加するか、商品コードをデーターベースの中にある数値を入れることで解消されます。


原因② 検索値に値がない
データ入力箇所に検索値に値が入ってない場合は#N/Aが表示されます。

→対応
回避する手段としてIFERROR関数と組み合わせると良いです。検索値にコード入力がされてないとVLOOKUPでは値がないことでエラー表示となってしまいます。

J列に入っている式にはこのような関数が入っています。
=VLOOKUP(I5,$D$4:$F$9,2,FALSE)

ここにIFERROR関数を入れると
=IFERROR(VLOOKUP(I5,$D$4:$F$9,2,FALSE),””)
検索値に値が空白であってもエラー表示されなくなります。


原因③ 検索値とデーターベースの値が完全一致していない
データーベースにある値と入力欄の値が一致していとVLOOKUPでは違うデーターとして認識し正しい結果が返ってきません。

→対応
必ずデーターベースと一致するものを入力するようにしてください。
下記の画像のようにりんごは一致しますが、リンゴやリンゴはエラーとなります。データーベース上に存在する”りんご”を入れるようにしてください。

原因④ 値が近似値になっている。(FALSEを指定している)

検索値の値がデーターベース上にある値と異なっているか、近似値になっていることが原因です。

→対応
検索値を数字として、データーベース上の数字を参照して呼び出しているときに起きる現象です。VLOOKUP関数の検索方法をTRUEにするか、データーベース上にある数値と一致させてください。

検索方法をFALSEを使うならば商品コードをデーターベース上にある数値と同じにしてください。

検索方法をTRUEを使うことで、商品コードが近似値となっていて合致しているものを検索結果として結果が返ってきます。下記の画像のようにデーターベース上に存在しない1.5を使っても近似値となる”りんご”が結果として出てきています。

原因⑤ データーベース上の並び替えをしていない(TRUEを使っている場合)

VLOOKUPの性質上、データーベースに存在するデータの上にある数値から値を返してきます。

↓の画像だと、商品コード1を選択した場合、データーベースの上から順番に見ていった場合1に該当するコードは存在しないことになっています。検索値に2を入れた場合、2から3.9までが”りんご”という値を返してきてます。

→対応
データーベース上の値を並び替えする。データーベース上のコードを昇順で並び替えすることで正しい結果が返ってきます。

並び替えの仕方
①データーベース上の並び替えしたい範囲をドラックで選択する

②上のタブからデータ→並び替えをクリックします

③並び替えのメニューが出てきたら最優先されたキーの箇所に検索値となる列を指定します。順序は昇順を指定し、OKを押します

④正しく入れ替わったか確認する
並び替えが正しくできているか、検索値の値が正しいものが出ているか確認してください。

→対応②
検索方法をTRUEではなくFALSEに修正してください。

原因⑤各種項目の指定が誤っている
検索値→誤った箇所を指定している。
対応:正しい箇所を指定してください。

範囲→範囲指定が変な所を設定している。
対応:範囲を見直してください。コピペして範囲がズレた場合は範囲をセルを$で囲み絶対参照にしてください。

列番号→範囲からはみ出た番号を指定している。
対応:範囲で指定した列番号にしてください。

#REF!が表示される
原因:参照している先の値(セルや行・列・シート)を削除した。

対応:正しい式を組み直すか、削除する前の状態に戻して下さい。VLOOKUP特有の問題ではないですが、うっかり1列削除してREFが大量に表示されたときはびっくりしてしまいますね。

例:検索値にあるJ列を削除すると・・・

こんな感じに検索値がなくなった為、REFが大量に出現してしまいます。

VALUEが表示される