目次
VLOOKUP関数について
VLOOKUPの概要
| 検索値に一致する行のデータを検索Excel関数/検索 | ||||||||||||||||||||||||||||||||
|
=VLOOKUP( 検索値 , 範囲 , 列番号 , FALSE ) 概要 検索値に一致する行のデータを検索して表示 |
||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||
VLOOKUPはどのような時に使用するか
- 特定の商品番号についての価格を知りたい
- 検索語に対応する詳細データを知りたい
VLOOKUPの使い方
以下はA1～A4のIDで検索し、対応する首都名を表示します
| A | B | C | D | E | |
| 1 | A1 | 日本 | 東京 | ||
| 2 | A2 | アメリカ | ワシントン | A1 | |
| 3 | A3 | フランス | パリ | 東京
=VLOOKUP( D2 , A1:C4 , 3 , FALSE ) ※もし国名で検索したいときは、 =VLOOKUP(D2,B1:C4,2,FALSE)とする |
|
| 4 | A4 | ドイツ | ベルリン | ||
| 5 |
VLOOKUP関数の使用を想定して最も左の列に個別に識別できるIDなどを記入しておくとよい。 エラーとなる場合は、表示形式を統一してください。 |
||||
| 6 | |||||
| 7 | |||||
| 8 | |||||
| 9 |
VLOOKUPとCOLUMNを組み合わせる
- VLOOKUP関数の列番号は、行の値を全て取り出すには列をマウスドラッグで拡張するが、数値を指定していると変化しないために値が変わらない。そのため、COLUMNを用いる。こうすることで、常に列番号は正しい値になる。
- 範囲については、マウスドラッグで拡張しても変化しないようにするため、$A$2:$C$4として固定している
- 自分で列番号を1、2と打ってももちろん問題はないし、C1に1、D1に２として、=VLOOKUP(2 , $A$2:$C$4 , C1 , FALSE) としてもよい
| A | B | C | D | E | |
| 1 | 2 | ||||
| 2 | 1 | 4 | 2
=VLOOKUP($C$1 , $A$2:$B$4 , COLUMN(A2) , FALSE) D2の式は=VLOOKUP($C$1 , $A$2:$B$4 , COLUMN(B2) , FALSE) となっている。 COLUMNを用いず1としていると、D2でも1のままになる。 範囲がAからスタートしていない場合、列番号とCOLUMNの差を考慮しなければならず、例えばCスタートの場合はCOLUMN-2とする。 |
5 | |
| 3 | 2 | 5 | |||
| 4 | 3 | 3 | |||
| 5 | |||||
| 6 | |||||
| 7 | |||||
| 8 | |||||
| 9 | |||||
| 10 | |||||
＄で範囲を指定する意味
- 上記の場合において＄とせずに、マウスドラッグで拡張した場合、C2とD2での範囲の部分は以下のようになるため、結果として、＃N/Aとなる
- $A$2:$B$4としておけば、D2でも範囲が変わらず変わらず$A$2:$B$4となるため、2のあるA3行の2列目であるB3の5が表示される
| A | B | C | D | E | |
| 1 | |||||
| 2 | 1 | 4 | A2:B4 | B2:C4 | |
| 3 | 2 | 5 | |||
| 4 | 3 | 3 |
VLOOKUPで別シートから取得するには
- 別シート指定は「シート名!セル名」とすればよい。例えば上記A2:B4の内容を「データシート」という名称で別シートに保存しているとすると以下のようにして呼び出しを行う
| A | B | C | D | E | |
| 3 | 2
=VLOOKUP(E1, データシート!$A$2:$B$4, COLUMN(A2), FALSE) ”データシート”と""で囲む必要はない |
2 | |||
| 4 | |||||
| 5 | |||||
| 6 | |||||
| 7 |
VLOOKUPで複数結果に対応する
- 複数の結果に対応するには、COUNTIFで算出した何個目かの数値と検索値を結合した文字列を新たな検索値として設定します。新たな検索値を用意することで重複しないデータとして扱います。
- COUNTIFを用いて、A列に該当するB列の文字が何番目かを表示し、Bと結合します。B4で2個目の1が表示されるためA4に21(2と1を結合)が表示されています。A1に=COUNTIF($B$1:B1,B1)&B1とし、A6までマウスで拡張します。A4は=COUNTIF($B$1:B4,B4)&B4となり、B4までB4が２こあるため、A4に21が表示されるわけです。
- その後、VLOOKUPを使用します。以下は2個目の3、つまりFを表記させる方法です。
- D2の部分を変更すれば、1個目、2個目、3個目と複数のセル表示することもできる。
| A | B | C | D | E | |
| 1 | 11 | 1 | A | 3 | ←探す数(B列) |
| 2 | 12 | 2 | B | 2 | ←何個目？ |
| 3 | 13 | 3 | C | F
=VLOOKUP(D2&$D$1,$A$1:$C$6,3,FALSE)) |
|
| 4 | 21 | 1 | D | ||
| 5 | 14 | 4 | E | ||
| 6 | 23 | 3 | F | ||
| 7 |
A4は=COUNTIF($B$1:B4,B4)&B4とし、B1からB4までの1の個数をカウント(2)し、その数と結合し、"21"としています。 |
||||
| 8 | |||||
| 9 | |||||
| 10 | |||||
| 11 |
1を探す場合
| 1個目 | =VLOOKUP(1&$D$1,$A$1:$C$6,3,FALSE))→A |
|---|---|
| 2個目 | =VLOOKUP(2&$D$1,$A$1:$C$6,3,FALSE))→D |
| 3個目 | =VLOOKUP(3&$D$1,$A$1:$C$6,3,FALSE))→#N/A |
VLOOKUPについてまとめの一言
- ExcelのVLOOKUP関数は、縦方向に並んだデータから特定の値を検索し、その行に対応するデータを取得するための便利な関数です。大規模なデータセットから特定の情報を素早く抽出したり、他の表と連携してデータを統合する場合に非常に役立ちます。ただし、検索対象の範囲は左端の列に限られ、柔軟性が低いため、列の位置を正確に指定する必要があります。
- VLOOKUP関数の代替として、より柔軟なINDEX関数とMATCH関数の組み合わせや、新しいXLOOKUP関数が推奨される場合もあります。これらの関数を使うことで、範囲内の検索をより効率的に行うことができます。
VLOOKUPに関する参考解説
Excelの関数を検索する
| 分類 | 関数 | 説明 |
|---|---|---|
| 日付 | DATE | 年月日から日付を求める |
| 日付 | DATEDIF | 開始日から終了日までの期間を求める |
| 日付 | DAY | 日付から日を抽出する |
| 日付 | EDATE | 指定月後の月日を求める |
| 日付 | EOMONTH | 指定月後の月末を求める |
| 日付 | EOMONTH+1 | 指定月後の月初めを求める |
| 日付 | HOUR | 時刻から時間を取り出す |
| 日付 | MINUTE | 時刻から分を取り出す |
| 日付 | MONTH | 日付から付きを抽出する |
| 日付 | NETWORKDAYS | 営業日数を求める(土日休日型) |
| 日付 | NETWORKDAYS.INTL | 営業日数を求める(定休日と休業日指定型) |
| 日付 | NOW | 今日の日付と時刻を求める |
| 日付 | SECOND | 時刻から秒を取り出す |
| 日付 | TIME | 時間を指定して時刻データを作成する |
| 日付 | TODAY | 今日の日付を求める |
| 日付 | WEEKNUM | 1月1日から何週目かを求める |
| 日付 | WEEKDAY | 年月日から曜日番号を求める |
| 日付 | WORKDAY | 営業日について指定した日数後の年月日を求める |
| 日付 | WORKDAY.INTL | 営業日について指定した日数後の年月日を求める(定休日と休業日指定型) |
| 日付 | YEAR | 日付から年を抽出する |
| 文字操作 | & | 文字列を結合する |
| 文字操作 | ASC | 全角文字を半角文字に置き換える |
| 文字操作 | CHAR/CLEAN | 改行を削除する |
| 文字操作 | CONCAT | 文字を範囲指定で結合する |
| 文字操作 | CONCATENATE | 文字列を結合する |
| 文字操作 | COLUMN | 列番号を取得する |
| 文字操作 | DELTA | 数値が等しいかを判定する |
| 文字操作 | EXACT | 文字列が等しいかを判定する |
| 文字操作 | FIND(B) | セル内の指定した文字の位置を求める |
| 文字操作 | ISODD | 指定した数値が奇数か偶数かを判定する |
| 文字操作 | JIS | 半角文字を全角文字に置き換える |
| 文字操作 | LEFT(B) | 指定した文字数分の文字を先頭から取り出す |
| 文字操作 | LEN(B) | 文字数をカウントする |
| 文字操作 | LOWER | 文字列を小文字にする |
| 文字操作 | MID(B) | 指定した文字以降の文字を取り出す |
| 文字操作 | NUMBERSTRING | 漢数字に変換する |
| 文字操作 | PHONETIC | フリガナを取り出す |
| 文字操作 | PROPER | 文字列の先頭を大文字にする |
| 文字操作 | REPLACE(B) | 指定した位置にある文字を置き換える |
| 文字操作 | REPT | 指定回数分の文字列を表示 |
| 文字操作 | RIGHT(B) | 指定した文字数分の文字を末尾から取り出す |
| 文字操作 | ROW | 行番号を取得する |
| 文字操作 | SUBSTITUTE | 指定した文字列を置き換える |
| 文字操作 | SEARCH(B) | セル内の指定した文字の位置を求める |
| 文字操作 | TEXT | 日付などの数値(シリアル値)を文字形式に変換、抽出する |
| 文字操作 | TEXTJOIN | 文字列を結合する |
| 文字操作 | TRIM | 余分な空白を削除する |
| 文字操作 | UPPER | 文字列を大文字にする |
| 文字操作 | VALUE | 文字列を数値(シリアル値など)に変換する |
| 論理 | 論理式 | TRUEとFALSE |
| 論理 | AND | すべての条件を満たす論理式 |
| 論理 | IF | 論理式の結果によって表示を切り替える |
| 論理 | IFS | 複数の論理式の結果によって表示を切り替える |
| 論理 | OR | いずれかの条件を満たす論理式 |
| 論理 | SWITCH | 論理式の結果によって表示を切り替える |
| 数学 | AGGREGATE | 指定した19の集計方法で集計値を求める |
| 数学 | CEILING | 基準値の単位で切り上げる |
| 数学 | FLOOR | 基準値の単位で切り下げる |
| 数学 | INT | 小数点以下を切り捨てる |
| 数学 | ISEVEN | 偶数柿数かを判定する |
| 数学 | MOD | 割り算の余りを求める |
| 数学 | MROUND | 基準値の単位で四捨五入する |
| 数学 | POWER | べき乗を求める |
| 数学 | PRODUCT | 掛け算を求める |
| 数学 | QUOTIENT | 割り算の商を求める |
| 数学 | RAND | 乱数を発生させる |
| 数学 | ROUND | 指定した桁で四捨五入する |
| 数学 | ROUNDDOWN | 指定した桁で切り捨てる |
| 数学 | ROUNDUP | 指定した桁で切り上げる |
| 数学 | SUBTOTAL | 指定した11の集計方法で集計値を求める |
| 数学 | SUM | 数値の合計を求める |
| 数学 | SUMIF | 特定の条件を満たすセルの合計値を求める |
| 数学 | SUMIFS | 複数の条件を満たすセルの合計値を求める |
| 数学 | SUMPRODUCT | 条件付きセルの乗算数の合計を求める |
| 数学 | SUMPRODUCT | セルの乗算数の合計を求める |
| 数学 | TRUNC | 指定した桁数を切り捨てる |
| 数学 | TRUNC | 小数点以下を切り捨てる |
| 検索 | CHOOSE | インデックス数値に対応する値を表示する |
| 検索 | FILTER | 条件に一致する行のデータを求める |
| 検索 | INDEX | 範囲内の指定したデータを取り出す |
| 検索 | INDEX | 指定した行位置に対応する列データを取り出す |
| 検索 | INDEX | 指定した列位置に対応する行データを取り出す |
| 検索 | LOOKUP | 検索値に一致するデータを検索する |
| 検索 | MATCH | 値に一致する行位置を求める |
| 検索 | MATCH | 値に一致する列位置を求める |
| 検索 | UNIQUE | 重複するデータをまとめる |
| 検索 | VLOOKUP | 検索値に一致するデータを検索する |
| 検索 | VLOOKUP | 検索値に対応するデータ(範囲型)を求める |
| 統計 | AVERAGE | 平均値を求める |
| 統計 | AVERAGEA | 平均値を求める |
| 統計 | AVERAGEIF | 条件に合う数値の平均値を求める |
| 統計 | AVERAGEIFS | 複数の条件に合う数値の平均値を求める |
| 統計 | COUNT | 指定したデータの個数を求める |
| 統計 | COUNTA | 指定したデータの個数を求める |
| 統計 | COUNTBLANK | 空白セルの個数を求める |
| 統計 | COUNTIF | 条件に合うデータ数を求める |
| 統計 | COUNTIFS | 複数の条件に合うデータ数を求める |
| 統計 | FREQUENCY | データの度数を求める |
| 統計 | LARGE | 大きいほうからの順位の数値を求める |
| 統計 | MAX | データの最大値を求める |
| 統計 | MAXA | データの最大値を求める |
| 統計 | MAXIFS | 複数条件を満たす最大値を求める |
| 統計 | MEDIAN | 中央値を求める |
| 統計 | MIN | データの最大値を求める |
| 統計 | MINA | データの最大値を求める |
| 統計 | MINIFS | 複数条件を満たす最小値を求める |
| 統計 | MODE.MULT | データの最頻値を求める(こちらを推奨) |
| 統計 | MODE.SNGL | データの最頻値を求める(MODE.MULT推奨) |
| 統計 | PERCENTILE.EXC | 百分位数(パーセント)を求める |
| 統計 | PERCENTILE.INC | 百分位数(パーセント)を求める |
| 統計 | PERCENTRANK.EXC | データの位置を百分率で求める |
| 統計 | PERCENTRANK.INC | データの位置を百分率で求める |
| 統計 | QUARTILE.EXC | 四分位数を求める |
| 統計 | QUARTILE.INC | 四分位数を求める |
| 統計 | RANK.EQ | データの順位を求める |
| 統計 | SMALL | 小さいほうからの順位の数値を求める |
| 統計 | STDEV.P | 標準偏差(母集団型)を求める |
| 統計 | STDEV.S | 標準偏差(標本型)を求める |
| 統計 | STDEVA | 標準偏差(標本型で論理等含む)を求める |
| 統計 | STDEVPA | 標準偏差(母集団型で論理等含む)を求める |
| 統計 | VAR.P | データの分散(標本分散)を求める |
| 統計 | VAR.S | データの分散(不偏分散)を求める |
| 統計 | VARA | データの分散(不偏分散)を求める |
| 統計 | VARPA | データの分散(標本分散)を求める |