Excel。VLOOKUP関数で複数の離れた列を表引きしたい。【distance】

Excel。VLOOKUP関数で複数の離れた列を表引きしたい。

＜VLOOKUP関数＞

A1:H5には、四半期の売上表があります。

A8にNOを入力すると、該当するデータがA1:H5から表引きされるようにしたいわけです。

このような表引きをするならば、VLOOKUP関数をつかうと対応できそうです。

しかし、B8:E8までにVLOOKUP関数をつかって、設定する場合、列番号が異なるので複数のVLOOKUP関数を設定する必要があります。

また、連続していれば、列番号に、COLUMN関数をつかうことで、対応することもできますが、今回は、表引きしたいデータが離れています。

これでは、COLUMN関数をつかうことができません。

そこで、VLOOKUP関数の列番号に配列数式をつかうことで、対応することができます。

B8にVLOOKUP関数の数式を設定します。

=VLOOKUP(A8,A2:H5,{2,4,6,8},FALSE)

今回は、検索値が空欄時に発生する、#N/Aは考慮しておりませんので、考慮する場合には、IF関数かIFERROR関数で対応します。

またスピル機能によって、オートフィルで数式をコピーする必要はありません。

これで、複数の離れた列から表引きすることができました。

では、VLOOKUP関数を確認しておきましょう

最初の引数は、検索値です。A8を設定します。

この番号を変えることで、該当するデータを表引きします。

2つ目の引数は、範囲です。

A2:H5。

表引きの件数が1件なので、絶対参照は不要です。

3つ目の引数は、列番号です。

2つ目の引数で設定した範囲の左側から何列目のデータを抽出するのかという数値なのですが、離れた列になります。

そこで、

{2,4,6,8}

という配列数式にすることで、表引きすることができます。

通常の「()」ではなくて「{}」で囲むことで、配列数式にすることができます。

旧来は、先に表引きをすべて範囲選択して、数式の確定時にCtrl+Shift+Enterをすることで配列数式にすることができましたが、スピル機能によって、不要になりました。

数式の作成は、最初から「{}」で表引きしたい列番号を囲うだけです。

表引きしたい列は、2,4,6,8列です。

最後の引数は、検索方法です。

完全一致なので、FALSEを設定します。

Excel。これは便利。VLOOKUP関数の列番号にIFS関数をつかってみた。【flexible】

Excel。これは便利。VLOOKUP関数の列番号にIFS関数をつかってみた。

＜VLOOKUP＋IFS関数＞

次の表があります。

 

A4:D8に店舗別の販売表があります。

この表から、A2に店舗名を入力したら、B2にB1の見出しの売上データを表示したいわけです。

5月売上と固定していれば、次のような数式をB2に設定します。

=VLOOKUP(A2,A5:D8,3,FALSE)

確かに、5月売上の中野店の販売金額を表引きすることができます。

ただ、VLOOKUP関数の3番目の引数の列番号を「3」と固定してしまうと、4月売上や6月売上の列から表引きすることができません。

B1の見出しを変更したら、それに合わせて、列番号を変更するのは面倒です。]

どうにか、表引きすることはできないものでしょうか。

そこで、列番号にIF関数などの条件分岐する関数をつかってみたらどうなるのでしょうか。

今回は、4月売上・5月売上・6月売上を3列ありますから、IFS関数をつかって、VLOOKUP関数を修正してみます。

=VLOOKUP(A2,A5:D8,IFS(B1="4月売上",2,B1="5月売上",3,TRUE,4),FALSE)

では、B1の見出しを6月売上で、A2の店舗名を渋谷に変更してみます。

3639と正しく表引きされていることが確認できます。

では、設定したVLOOKUP関数を確認しておきましょう。

=VLOOKUP(A2,A5:D8,IFS(B1="4月売上",2,B1="5月売上",3,TRUE,4),FALSE)

最初の引数は、検索値。

店舗名で表引きをしますので、A2です。

2番目の引数は、範囲。

表なので、A5:D8。見出し行は不要です。

そして、3番目の引数、列番号。

ここは2番目の引数で設定した範囲の左から何列目のデータを表引きするのかという数値を設定します。

4月売上から6月売上まで自由に選択したいわけなので、列番号は2～４となるわけです。

ここで条件分岐ということで、IFS関数をつかってみました。

IFS(B1="4月売上",2,B1="5月売上",3,TRUE,4)

4月売上ならば、2。

5月売上ならば、3

それ以外は、4

と条件分岐するようにしました。

VLOOKUP関数の最後の引数は、完全一致なので、FALSEです。

これで、複数の見出し列に対応した自由度の高い表引きをすることができました。

Excel。1列置きのデータを抽出し、さらに表引きも合わせて行いたい【Table pull】

Excel。1列置きのデータを抽出し、さらに表引きも合わせて行いたい

＜VLOOKUP+COLUMN関数＞

横長のデータがA1:H5にあります。

4月から6月までの売上のデータだけを抽出した表をつくりたい。

できれば、NOを入力したデータのみを表引きしたい場合、どのようにしたらいいのでしょうか。

全体の表から必要な列だけを抽出するだけならば、

HSTACK関数をつかうことで、手早く抽出することができます。

A8に設定した数式は、

=HSTACK(A2:B5,D2:D5,F2:F5,H2:H5)

引数には、必要なフィールドを設定するだけの関数なので、必要な列だけを抽出するならば、とても便利な関数です。

ただ、これに合わせて、例えば、NOが1と3だけのデータを抽出したい。

表引きしたいとなると、HSTACK関数だけでは対応することができません。

少々複雑な数式に変更しなければならなくなります。

そこで、今回は、VLOOKUP関数とCOLUMN関数を組み合わせて、対応していきます。

B8に数式を設定しました。

=VLOOKUP($A8,$A$2:$H$5,COLUMN(A1)*2,FALSE)

あとは、オートフィルで数式をコピーします。

これで、月別の売上の列と、NOが1と3だけのデータを抽出することができました。

では、この数式を確認しておきましょう。

最初の引数は、検索値。A8を選択します。

ただ、列固定の複合参照にする必要がありますので、$A8　とします。

2つ目の引数は、A2:H5の表全体を選択します。

オートフィルで数式をコピーしますので、絶対参照が必要です。

よって、$A$2:$H$5 とします。

3つ目の引数は、列番号。

ここがポイントになるわけです。

オートフィルで数式をコピーしたときに、列番号を2・4・6というように2の倍数が必要になります。

横方向にオートフィルした時に、数値が2の倍数にするために、列番号を求めることができる、COLUMN関数をつかってみます。

COLUMN(A1)*2

これで、A1は1と求められるので、その値に、×2すれば、2の倍数を求めることができます。

最後は、検索方法です。

完全一致なので、FALSE　と設定します。

最近、新しい関数が色々登場しております。

関数を組み合わせることで、もっと効率的に求めたり、表引きできると思いますので、試してみるといいものが、発見できるかもしれませんね。

Excel。検索値を入力して、行方向のデータを列方向で表引きしたい【matrix】

Excel。検索値を入力して、行方向のデータを列方向で表引きしたい

＜VLOOKUP+ROW関数＞

行方向のデータを表引きした結果を、列方向で表示したい場合、どのようにしたら、いいのでしょうか。

次の表を用意しました。

B6に3と入力したら、B7:B10にNO3のデータを表示させたいわけです。

単に、コピー貼り付けで、行列入れ替えというのも、1回だけならば、OKですが、NOを入力するたびに、そのデータを表引きしたいので、コピーだけというわけにはいきません。

やりたいことは「表引き」です。

そこで、VLOOKUP関数をつかってみることにします。

問題になるのは、3つ目の引数の「列番号」です。

では、B7に次の数式を設定します。

=VLOOKUP($B$6,$A$2:$E$4,ROW(A2),FALSE)

オートフィルで数式をコピーします。

行方向のデータを列方向に表引きすることができました。

数式を確認しておきましょう。

1番目の引数は、検索値。

B6ですが、オートフィルで数式をコピーするので、絶対参照で固定する必要がありますので、$B$6

2番目の引数は、範囲。$A$2:$E$4

こちらも、オートフィルで数式をコピーしますので、絶対参照を設定します。

次がポイントになる

3番目の引数の、列番号。

下方向に、オートフィルで数式をコピーしたときに、2～4としていきたいわけです。

列番号を求めることができるROW関数をつかえば、列番号をつくることができますので、

ROW(A2)

最後は、検索方法。完全一致なので、FALSE　と設定します。

Excel。VLOOKUP関数で0（ゼロ）が表示される問題の解決方法【Shows 0】

Excel。VLOOKUP関数で0（ゼロ）が表示される問題の解決方法

＜VLOOKUP関数＞

表引きのVLOOKUP関数でのエラーといえば、#N/Aがあります。

この#N/Aエラーは、検索値がないので、発生するエラーです。

このエラーに対応するには、IF関数やIFERROR関数をつかうことで対応することができます。

この対応方法は、有名なのですが、VLOOKUP関数には、もう一つ問題があります。

それが、0（ゼロ）が表示されてしまう問題です。

次の表で0（ゼロ）が発生する状況を確認しましょう。

B2に設定した数式は、

=VLOOKUP(A2,$A$8:$B$11,2,FALSE)

オートフィルで数式をコピーした状態です。

すると、B4には0（ゼロ）と表示されていることがわかります。

数式を確認すると、

検索値は、A列で、範囲が、A8:B11なので、おかしなところはありません。

ただ、検索値が4の場合、表引き元になる範囲をみてみると、B11は、空白になっていることが確認できます。

0（ゼロ）が表示されてしまった原因は、表引き元の値が空白だと発生したということがわかります。

本来は、0（ゼロ）で表示されるのではなく、空白でないと困るわけですね。

どのようにしたら、いいのでしょうか。

エラーではないので、IF関数やIFERROR関数で対応するというわけにはいきません。

そこで、次のように数式を修正してみましょう。

=VLOOKUP(A2,$A$8:$B$11,2,FALSE)&””

修正した数式を、オートフィルでコピーしてみると、0（ゼロ）が表示されずに、空白になっています。

このように、表引きの結果、空白ではなくて、0（ゼロ）が表示されてしまって、対応したい場合には、「&””」と空白の文字結合をすることで、対応することができます。

なお、これは、VLOOKUP関数だから発生したのではなく、XLOOKUP関数でも同様に発生します。

その場合も「&””」で対応することができます。

Excel。VLOOKUP関数で抽出した文字からフリガナを表示するには【furigana】

Excel。VLOOKUP関数で抽出した文字からフリガナを表示するには

＜VLOOKUP関数・PHONETIC+XLOOKUP関数＞

氏名からフリガナを抽出するには、PHONETIC関数をつかうわけですが、VLOOKUP関数で抽出した文字列からフリガナを抽出することはできません。

次の表を用意しました。

A4:C8に表があります。

B2には、VLOOKUP関数をつかって、A4:C8の表から、氏名を抽出しています。

B2の数式は、

=VLOOKUP(A2,A5:C8,2,FALSE)

と設定しています。

さて、C2にB2の文字列のフリガナを表示したいので、

C2に、PHONETIC関数だけの数式を設定してみます。

=PHONETIC(B2)

結果は、何も表示されません。

PHONETIC関数は、文字列の入力時のデータを表示するだけの関数なので、VLOOKUP関数で表引きした結果からは、表示することはできないというわけです。

では、PHONETIC関数の引数にVLOOKUP関数を直接設定すればいいように思えます。

C2の数式を

=PHONETIC(VLOOKUP(A2,A5:C8,2,FALSE))

にしてみると、エラーが表示されてます。

残念ながら、PHONETIC関数とVLOOKUP関数のネストはできないようです。

そこで、以前ならば、INDEX関数とMATCH関数を組み合わせて対応したりしましたが、XLOOKUP関数をつかえば、解決します。

PHONETIC関数は、VLOOKUP関数とのネストはできないのですが、XLOOKUP関数とのネストはできるようになっています。

C2の数式をPHONETIC関数は、XLOOKUP関数とのネストで数式を設定してみます。

=PHONETIC(XLOOKUP(A2,A5:A8,B5:B8,"",0,1))

これで、C2にフリガナを表示することができました。

XLOOKUP関数の引数を確認しておきましょう。

最初の引数は、「検索値」。

VLOOKUP関数で表引きした結果が表示されている、A2を設定します。

2番目の引数は、「検索範囲」。

NOのA5:A8を設定します。

3番目の引数は、「戻り範囲」。

氏名のB5:B8を設定します。

4番目の引数は、「見つからなかった場合」。

見つからなかったら空白にしますので「””（ダブルコーテーション×2）」と設定します。

5番目の引数は、「一致モード」。

完全一致にしたいので、「0」と設定します。

最後の引数は、「検索モード」。

先頭から検索させますので「1」と設定します。

これで、VLOOKUP関数の表引き結果をつかったフリガナを表示することができました。

Excel。値を検索して、複数の列から対応する結果を抽出したい【Multi-column】

Excel。値を検索して、複数の列から対応する結果を抽出したい

＜VLOOKUP関数＋IF関数・XLOOKUP＋INDIRECT関数＞

会員と一般で、単価が異なる表から、会員なら会員の、一般なら一般の単価を抽出し表示したい場合、どのようにしたらいいのでしょうか。

次の表をつかって説明します。

A2の所属が一般で、商品コードがB2のA01です。A5:D7の表から、商品コードがA01の

一般なので、D2の単価は、100と表引きされています。

A2の値を会員にすれば、D2の単価は80となるようにしたいわけです。

このような場合、VLOOKUP関数をつかうといいように思えますが、うまくいきません。

C2の商品名は、VLOOKUP関数で対応することは、できます。

C2の数式は、

=VLOOKUP(B2,A6:D7,2,FALSE)

VLOOKUP関数の最初の引数、検索値は、B2

2つ目の引数は、範囲なので、A6:D7 と設定します。

3つ目の引数は、列番号です。

2つ目の引数で設定した範囲の左から何列目のデータを表示するのかということなので、左から「2」列目なので、2と設定します。

最後の引数の、検索方法は、完全一致ですから、FALSE。

商品名は、VLOOKUP関数でもいいのですが、問題は、列番号です。

会員ならば、3。一般なら4としなければなりません。

このような場合、

=VLOOKUP(B2,A6:D7,IF(A2="会員",3,4),FALSE)

と列番号を判断させるようにIF関数をつかってもいいと思います。

ただし、所属数が会員・準会員・一般・学生のように、増えた場合、IF関数では対応するのが大変になるので、多分岐できる関数を使う必要がでてきます。

また、所属、それぞれの表をつくって、INDIRECT関数をつかって対応する方法があります。

ただ、その方法では、別表を作る必要がありますので、面倒です。

そこで、INDIRECT関数はつかうのですが、VLOOKUP関数よりもXLOOKUP関数で対応する方法があります。

事前に「名前の定義」を設定します。

C6:C7に、「会員」

D6:D7に、「一般」

と名前を設定しました。

D2の数式は、

=XLOOKUP(B2,A6:A7,INDIRECT(A2),"",0,1)

A2の所属を変更しても、会員と一般、それぞれの数値を表引きしてくれます。

もし、所属数が増えても、名前の定義を増やすだけで、数式を変更する必要はありません。

では、

=XLOOKUP(B2,A6:A7,INDIRECT(A2),"",0,1)

XLOOKUP関数の数式を確認します。

最初の引数は、検索値　なので、B2を設定します。

2つ目の引数は、検索範囲。

検索値が所属されている範囲なので、A6:A7。

3つ目の引数は、戻り範囲。

表示したい範囲です。

ここに、会員なのか、一般なのかで、戻り範囲を対応させたい。

そこで、INDIRECT関数をつかうことで、戻り範囲を変更させています。

INDIRECT関数は、値そのものをつかうことができます。

名前の定義で、会員と一般を設定していますので、切り替えることができるというわけです。

4つ目の引数は、見つからない場合。

見つからない時は「””（ダブルコーテーション×2）」で空白と設定します。

5つ目の引数は、一致モードなので、０の完全一致にしました。

6つ目の引数は、検索モード。

先頭から検索させますので、1と設定します。

このように、XLOOKUP関数とINDIRECT関数を組み合わせることで、値を検索して、複数の列から対応する結果を抽出することができます。

Excel。VLOOKUP関数をつかって行方向のデータを列方向で抽出表示したい【Rows and columns】

Excel。VLOOKUP関数をつかって行方向のデータを列方向で抽出表示したい

＜VLOOKUP+ROW関数＞

VLOOKUP関数は、抽出表示するのに便利な関数です。

ただ、行方向のデータを、行方向に抽出表示するのではなく、行方向のデータを、「列方向」に抽出表示したい場合、どのようにしたら、手早く数式を設定することができるのでしょうか。

 

ポイントは、下方向にオートフィルで数式をコピーしますが、3番目の引数の「列番号」をどのように設定してあげたらいいのかという点です。

その点を踏まえて、次の数式を、B7に設定します。

=VLOOKUP($B$6,$A$2:$D$4,ROW(A2),FALSE)

あとは、オートフィルで数式をコピーします。

これで、B6のNOを変えるだけで、氏名～Listeningまでを該当の番号の氏名からListeningまでを抽出表示することができます。

では、数式を確認しておきましょう。

最初の引数は「検索値」。

NOに該当するデータを抽出したいので、「B6」を設定します。

ただ、オートフィルで数式をコピーしますので、絶対参照の設定をしなければなりませんので、「$B$6」と設定します。

2つ目の引数の「範囲」。

これは、抽出元のデータベースなので、見出し行を除いた、「$A$2:$D$4」と設定します。

こちらも絶対参照が必要ですね。

3つ目の引数が「列番号」。

直接数値を入力して変更してもいいかもしれませんが、数式の数が増えれば増えるだけ、修正数が増えるので、面倒です。

2・3・4と数値を変える方法を考える必要があります。

そこで、行番号を算出することができるROW関数をつかうことで、対応することができます。

よって、「ROW(A2)」と設定することで、この問題を解決することができます。

ROW(A2)=2と算出されます。

オートフィルで下方向にコピーすれば、ROW(A3)となりますから、3と算出され、3列目のデータを抽出することができるというわけです。

最後の引数は「検索方法」は、完全一致なので「FALSE」と設定します。

Excelの様々な関数の読み方や引数などを紹介。今回は、VARA関数～VSTACK関数です。【dictionary】

Excelの様々な関数の読み方や引数などを紹介。今回は、VARA関数～VSTACK関数です。

＜Excel関数辞典：VOL.88＞

今回は、VARA関数～VSTACK関数までをご紹介しております。

VARA関数

読み方： バリアンスエー　　

VARA(値1,[値2],…)

全データを標本データとみなした不偏分散を算出します　

VARP関数

読み方： バリアンスピー　　

VARP(数値1,[数値2],…)

数値群を母集団全体とみなした分散を算出します　

VARPA関数

読み方： バリアンスピーエー　　

VARPA(値1,[値2],…)

全データを母集団全体とみなした分散を算出します　

VDB関数

読み方： ヴィディービー　　

読み方： バリアブル・ディクライニング・バランス

VDB(取得価額,残存価額,耐用年数,開始期,終了期,[率],[切り替えなし])

減価償却の概算を新定率法で算出する　Variable Declining Balanceの略

VLOOKUP関数

読み方： ブイルックアップ　　

VLOOKUP(検索値,範囲,列番号,[検索方法])

縦方向の表からデータを検索して抽出する　

VSTACK関数

読み方： ブイスタック　　

VSTACK(array1,[array2],…)

垂直方向に配列を1つの配列に積み重ね（スタック）する　

Excel。VLOOKUP関数で条件によって検索する列を変えるにはどうしたらいいの【column change】

Excel。VLOOKUP関数で条件によって検索する列を変えるにはどうしたらいいの

＜VLOOKUP+IF関数＞

VLOOKUP関数は、INDIRECT関数と名前の定義を組み合わせることで、別々の表（範囲）から検索値を抽出することはできます。

では、表（範囲）の中で条件によって抽出対象になる列を変えることはできるのでしょうか。

次の表で説明します。

A2に商品名を入力したら、B2にD1:F5の表（範囲）から、単価かセールの数値を表示したいわけです。

B1には、入力規則のリストをつかって、「単価」と「セール」を切り替えるようにしています。

B1が単価なら、B2には、E列の単価の数値を抽出し、B1がセールならB2にはF列のセールの数値を抽出したいわけです。

そこで、B2に設定するVLOOKUP関数は次のようにします。

=VLOOKUP(A2,$D$2:$F$5,IF(B1="単価",2,3),FALSE)

これで、B1を切り替えると、該当する列からデータを抽出することができます。

VLOOKUP関数のポイントは、3番目の引数の列番号です。

この列番号を、単価なら「2」、セールなら「3」としたいわけですから、IF関数をつかって列番号を変えることができます。

これによって、B1の値と連動して、抽出範囲を切り替えることができるようになります。

Excel。VLOOKUP関数で「&””」で0表示は消せるが、数値型だとうまくいきません。【numeric type】

Excel。VLOOKUP関数で「&””」で0表示は消せるが、数値型だとうまくいきません。

＜IFERROR+VALUE+VLOOKUP関数＞

VLOOKUP関数で、検索値はあるけども、抽出する値が無い場合には、「0（ゼロ）」と表示されてしまいます。

B2に設定した数式は、

=IFERROR((VLOOKUP(A2,$A$7:$B$9,2,FALSE)),"")

この数式を、オートフィルでコピーしています。

検索値が無い場合は、IFERROR関数を組み合わせることで、「#N/A」のエラーを消すことができます。

ただ、B3のように、検索値はあるけども、抽出する値が無い場合には「0（ゼロ）」が表示されてしまいます。

そこで、VLOOKUP関数のあとに「&””」をつけることで、非表示にすることができます。

B2の数式は、

=IFERROR((VLOOKUP(A3,$A$7:$B$9,2,FALSE)&""),"")

と修正して、オートフィルで数式をコピーしています。

これで、「0（ゼロ）」を非表示にすることができました。

さて、ここまでは、抽出する値が「文字型」だったので問題はなかったのですが、B7の値を「鉛筆」から、わざと「100」という数値に変更してみましょう。

B4の値は、「100」と表示されていますが、左揃えになっています。

つまり、数値型ではなく「数値文字型」という文字型に変わってしまっています。

「&””」はたしかに、「0（ゼロ）」を非表示にしてくれますが、数値型を「文字型」に変えてしまいます。

そこで、VALUE関数を追加することで、数値型のままにすることができます。

=IFERROR(VALUE(VLOOKUP(A2,$A$7:$B$9,2,FALSE)&""),"")

これで、数値型のままにすることができました。

しかし、数値型に対応した数式なので、この数式を使って、文字型を抽出しようとしても、表示することができません。

このようなことから、抽出するデータが数値の場合と文字の場合で「VALUE関数」の使い分けをする必要がありそうです。

なお、VALUE関数は、文字列として入力されている数値を、数値列に変換する関数です。

Excel。VLOOKUP関数で０が表示されても「&””」を追加すれば解決できます。【zero】

Excel。VLOOKUP関数で０が表示されても「&””」を追加すれば解決できます。

＜IFERROR+VLOOKUP関数＞

色々なケースを考えると、VLOOKUP関数だけでは、対応することができません。

次の表をつかって、説明していきます。

B2に設定した数式は、

=VLOOKUP(A2,$A$7:$B$9,2,FALSE)

この数式をB4までオートフィルで数式をコピーしています。

B2の結果は「#N/A」と表示されました。

原因は、A2の「4」は、参照先の「範囲」であるA7:A9に無いので、検索抽出できないことによるエラー「#N/A」が表示されてたわけです。

IFERROR関数をつかうことで、エラーに対応することができます。

B2の数式を次のように修正しました。

=IFERROR(VLOOKUP(A2,$A$7:$B$9,2,FALSE),"")

エラーだったら、空白にするようにしたわけですね。

この数式も、オートフィルで数式をコピーしています。

B2は、たしかにエラーを消すことができましたが、B3は、「0（ゼロ）」が表示されたままです。

この原因は、検索抽出データは、A2:A4にあるのですが、検索結果が空白になってしまっていることで、発生したわけです。

検索するデータはあるわけなので、エラーは表示されないけど、表示したいデータが空白の為、「0」になってしまう。

このような場合は、「&””」をつけると、「0」の表示を消すことができます。

B2の数式を修正して、オートフィルで数式をコピーしてみます。

修正したのが次の数式です。

=IFERROR((VLOOKUP(A2,$A$7:$B$9,2,FALSE)&""),"")

VLOOKUP関数のうしろに「&””」を追加しました。

これで、「0（ゼロ）」の表示も消すことができました。

検索するデータに空白のデータがある場合には「0（ゼロ）」と表示されてしまうので、「&””」をつけることで、対応することができます。

なお、VLOOKUP関数に限らず、「0(ゼロ)」が表示されてしまう場合は、「&””」をつけることで対応することができます。

Excel。VLOOKUP関数で#N/Aというエラーが表示されたら、どう解決するの【Error】

Excel。VLOOKUP関数で#N/Aというエラーが表示されたら、どう解決するの

＜IFERROR＋VLOOKUP関数＞

VLOOUP関数をつかった数式の算出結果で「#N/A（ノーアサイン）」というエラーが表示されることがあります。

どのようにしたら、エラーを表示させないで済むのでしょうか。

B2の数式は、

=VLOOKUP(A2,$A$5:$B$7,2,FALSE)

と設定してあります。

B2に表示されている「#N/A」の原因は、検索するコードが、範囲にないので探せないというエラーです。

今回の場合は、A2に「A04」と入力されていますが、A5:A7には、「A04」はありません。

そのため、抽出することができないため、「#N/A」が表示されてしまったというわけです。

そこで、エラーが発生した時に、どのようにするのか処理できるIFERROR関数をつかうことで、「#N/A」を表示しないようにすることができます。

B2の式を次のように修正します。

=IFERROR(VLOOKUP(A2,$A$5:$B$7,2,FALSE),"")

これで、「#N/A」エラーを非表示にすることができます。

Excel。VLOOKUP関数で、手早く別々の表から抽出するには、どうしたらいい【multiple list】

Excel。VLOOKUP関数で、手早く別々の表から抽出するには、どうしたらいい

＜VLOOKUP+INDIRECT関数・名前の定義＞

リストから該当するデータを抽出することができる、VLOOKUP関数。

このリストの選択先が複数ある場合、どのようにしたら、手早く抽出することができるのでしょうか。

 

C2の数式は、

=VLOOKUP(B2,INDIRECT(A2),2,FALSE)

どのような仕組みなのか説明していきます。

A2に、「校庭」「体育館」を入力します。会場番号を入力すると、「校庭」の3番である「陸上」が抽出されるという仕組みです。

この数式を作る場合には、事前に引数の範囲に該当するところに、名前の定義を使って「名前」を設定します。

A5:B7には、「校庭」という名前を設定してあります。

D5:E7には、「体育館」という名前を設定してあります。

あとは、VLOOKUP関数を設定していきます。

最初の引数の検索値には、「B2」を設定します。

2つ目の引数の範囲ですが、ここが2か所あるわけです。

そこで、名前の定義を指定してあげれば、その範囲の検索値からデータを抽出してくれるわけですが、検索する範囲を変更するたびに、数式を変更するのは大変です。

そこで、INDIRECT関数をつかうことで、範囲に、名前の定義で設定した名前を指定することができます。

なので2つ目の引数は、「INDIRECT(A2)」と設定します。

これで、A2に、「校庭」と入力されれば、名前の定義で設定した「校庭」の範囲を選んでくれます。

3つ目の引数の列番号には、「2」。

2つ目の引数で選択した範囲の左から2列目に、抽出したい列がありますので、「2」と設定するわけですね。

最後の引数の検索方法は、「FALSE」。

完全一致で抽出しますので、「FALSE」または、「0」と設定します。

Excelでは、FALSE=0なので、「0」と設定しても大丈夫です。

これで、A2の値を校庭・体育館と切り替えれば、それぞれの範囲から該当するデータを抽出することができます。

このように、VLOOKUP関数は、アイディアを追加することで、様々なビジネスシーンでさらに使えるようになりますので、色々試してみるといいかもしれませんね。

なお、あとプラスするとしたら、A列に入力規則のリストをつかうと、「校庭」「体育館」を選択して選ぶことができますので、おすすめの機能ですね。

Excel。VLOOKUP関数で先頭の複数文字と合致するデータを抽出したい【first character】

Excel。VLOOKUP関数で先頭の複数文字と合致するデータを抽出したい

＜VLOOKUP＋LEFT+FIND関数＞

別表から合致するデータを検索し抽出することができる「VLOOKUP関数」。

例えば、商品コードと合致する商品名や単価などを検索し抽出することはできますが、次のような、先頭からハイフンまでの複数文字を別表から抽出するにはどのようにしたらいいのでしょうか。

VLOOKUP関数は、4番目の引数の「検索方法」を近似値か完全一致でなければなりません。

講座番号のハイフンまでの左の文字列と合致するものという場合には、VLOOKUP関数だけでは対応することができません。

そこで、B2の数式は、ちょっと長くなりますが、次のように設定することで、対応することができます。

=VLOOKUP(LEFT(A2,FIND("-",A2)-1),$A$8:$B$10,2,FALSE)

あとは、オートフィルで数式をコピーします。

完全一致は完全一致ではあるのですが、ハイフンまでというのを、どう表現すればいいのかを考えていきます。

VLOOKUP関数の最初の引数「検索値」をハイフンより前の文字列としてあげればいいわけです。

そこで、検索値には、LEFT関数とFIND関数をつかうことで、ハイフンよりも前の文字列を抽出することができます。

「LEFT(A2,FIND("-",A2)-1)」

ハイフンの位置が文字列の何文字目にあるのかを、算出すればいいわけなので、FIND関数をつかって、ハイフンの文字位置を確認します。

「EX-001」の場合は、「3」と算出されます。

ただし、この「3」のままLEFT関数で使ってしまうと、左から3文字目までを抽出ということになり「EX-」となってしまうので、検索することができません。

そこで、「-1」しているというわけです。

これで、「EX」というハイフンより前の文字列を抽出することができましたので、あとは、VLOOKUP関数を設定するだけで完成します。

なお、今回は、ハイフンで区切られていたので、ハイフンより左側の文字列を抽出するというルールがありましたが、区切られているルールがない場合は、この数式をつかうことはできません。

Excel。VLOOKUP関数で抽出した文字からフリガナを表示させたい【furigana】

Excel。VLOOKUP関数で抽出した文字からフリガナを表示させたい

＜VLOOKUP関数・PHONETIC+INDEX+MATCH関数＞

氏名からフリガナを抽出したい時には、PHONETIC関数をつかうことで、表示できます。

ところが、PHONETIC関数は、文字を入力した時のデータを表示するため、VLOOKUP関数やセル参照などで、間接的に表示されているデータからフリガナ情報を表示することができません。

では、どのようにしたら、次のように、フリガナを表示することができるのでしょうか。

B2には、VLOOKUP関数の数式を設定してあります。

=VLOOKUP(A2,A5:F14,2,FALSE)

C2に、PHONETIC関数をつかった数式を設定してみても、何も表示されません。

C2に、

=PHONETIC(B2)

と設定していますが、表示されないわけですね。

解決するポイントは、文字入力が入っている元のデータをつかえばいいわけです。

では、次のようにC2の数式を修正していきます。

=PHONETIC(INDEX(B5:B14,MATCH(B2,B5:B14,0)))

これで、フリガナを表示することができるようになります。

それでは、この数式を説明していきます。

最初のPHONETIC関数は、「フリガナ」情報を表示することができる関数ですね。

INDEX関数で指定した行列番号が交差するセル参照する関数ですね。

そして、INDEX関数と相性抜群のMATCH関数を組み合わせます。

MATCH(B2,B5:B14,0)

と数式を設定していますが、B2の値は、B5:B14のなかで、何番目にあるのかを算出することができます。

MATCH関数は、範囲内にある検索値の位置を算出する関数です。

このように、INDEX関数とMATCH関数を合わせることで、元にあるフリガナ情報を抽出することができます。

INDEX＋MATCH関数は、覚えておくといい、関数の組み合わせかもしれませんね。

Excel。カレンダー。曜日の欄に祝日名を手早く表示するにはどうしたらいい【holiday】

Excel。カレンダー。曜日の欄に祝日名を手早く表示するにはどうしたらいい

＜IFNA+VLOOKUP+TEXT関数＞

祝日名をカレンダーの曜日のとなりに、祝日名という列を設けると、あまり使わない列を追加することになります。

当然、印刷した時に幅広な表になってしまいます。

そこで、次の表のように、曜日の列に祝日名を表示するようにしたい場合、どのようにしたら、手早く表示することができるのでしょうか。

最初に、準備として、祝日の一覧表を用意する必要があります。

祝日一覧表は、運用上、成人の日など、どうしても毎年祝日が変わるので更新し続ける必要があります。

今回は、わかりやすいように、5月の祝日一覧をD1:E4に設定しています。

B列の数式を考えていくことにしましょう。

祝日一覧に該当する日付があれば、E列を表示して、そうでなければ、曜日を表示するという考え方がわかりやすいかと思います。

曜日は、WEEKDAY関数だと、曜日ごとの数値は算出できますが、1だったら、「日」というようしなければいけないので、多くの関数を使う必要がありそうです。

そのため、曜日は、「TEXT関数」をつかって、日付を曜日で表示させることにします。

祝日一覧から抽出するには、A列の日付が含まれているかいないかを判断する必要があります。

あるかないかを判断するには、MATCH関数をつかうことになるわけです。

ただ、MATCH関数をつかった場合、何番目にそのデータがあるのかを算出してくれるだけで、E列の祝日名をB列に表示させるためには、さらに、数式を修正する必要があります。

IFNA関数をつかうことで、当初イメージした、日付が祝日一覧にあれば、祝日名をそうでなければ、曜日を表示するという数式を作ることができます。

これらのことを踏まえて、B2の数式を確認してみましょう。

B2の数式は、

=IFNA(VLOOKUP(A2,$D$2:$E$4,2,FALSE),TEXT(A2,"aaa"))

VLOOKUP関数で検索方法を完全一致で設定した場合、抽出データが無い場合「#N/A」というエラーが表示されます。

ならば、「#N/A」エラーなのかを判断材料として、数式を組み立てることができれば、コンパクトな数式で処理を行うことができそうです。

「#N/A」エラーかどうなのかを判断することが出来る関数が「IFNA関数」です。

IFNA関数をつかうことで、#N/Aだったら、VLOOKUP関数。

そうでなければ、曜日を表示するという数式をつくることができるというわけです。

エラーで対応というわけですから、IFERROR関数でも問題はありません。

今回のように、エラーだったらということを判断基準とする関数をつかうことで、数式をシンプルにすることができるかもしれませんね。

Excel。2列1組の表から1列置きに行方向に手早く抽出するには【Row direction】

Excel。2列1組の表から1列置きに行方向に手早く抽出するには

＜VLOOKUP+ROW関数＞

先のことを考えずに、帳票をつくってみたら、そのあと、有効に使えないという表というのがあります。

例えば次のような表。

月ごとに、販売数と金額の2列1組の表ですが、2列目の金額の数値を、B8に転記したいわけです。

効率よく作業したいので、A7に店舗名を入力したら、その月の金額一覧のようにしたいわけです。

月ごとのデータを、一つずつ、コピーして貼り付けるというのでは、時間がかかってしまいます。

まして、データの件数が増えれば、手作業というわけにもいきません。

では、どのようにしたら、手早く1列置きのデータを抽出することができるのでしょうか？

このような場合、「VLOOKUP関数」を使うことで、対応することが可能です。

特に、A7の店舗名を変更したら、それに連動させる必要がありますので、VLOOKUP関数をつかうことで、手早く抽出することができます。

B8に次の数式を設定します。

=VLOOKUP($A$7,$A$3:$G$5,ROW(A1)*2+1,FALSE)

あとは、B10までオートフィルで数式をコピーすれば、4月から6月まで金額を抽出することができました。

これならば、1年分のデータでも、手早く抽出することができます。

では、VLOOKUP関数の引数を説明していきます。

検索値は、「$A$7」。

A7の店舗名を変更することで、それぞれの店舗に合わせたデータを抽出することができます。

オートフィルで数式をコピーするので、絶対参照を設定しています。

範囲は、「$A$3:$G$5」。

帳票のデータ部分を範囲選択します。

ここも、オートフィルで数式をコピーするので、絶対参照を設定します。見出し行は不要ですね。

列番号が、今回のポイントです。

「ROW(A1)*2+1」。

VLOOKUP関数の列番号は、先程「範囲」に設定したデータの左から何列目なのかという指示をするわけです。

そこで、逆にどうやったら、「３・５・７」という、一つ置きの数値を、もってくることができるのかを考えてあげればいいわけです。

オートフィルで数式を下方向にコピーするわけなので、変化があるのは、行番号です。

行番号を算出するには、「ROW関数」を使います。

2列1組なので、ROW(A1)とすれば、「1」という数値が算出できます。

オートフィルで数式をコピーすることで、5月のデータは、ROW(A2)となり「2」という数値を算出します。

この算出された結果を2倍すれば、偶数列のデータを抽出できます。

さらに、「+1」することで、奇数列である、「３・５・７」列のデータを抽出することができるという仕組みです。

引数の最後の検索方法は、完全一致なので「FALSE」と設定すれば、完成です。

このように、VLOOKUP関数をつかうことで、色々な抽出方法が見つかるかもしれませんので、試してみるといいかもしれませんね。

Excel。連続した列のデータを手早く抽出するには、どうしたらいいの？【Consecutive columns】

Excel。連続した列のデータを手早く抽出するには、どうしたらいいの？

＜VLOOKUP＋COLUMN関数＞

リストから列方向に連続したデータを抽出したい場合、どうしたら効率よく実施することが出来るでしょうか？

次の表で確認してみましょう。

NOが2のデータを抽出したいとします。

A2に2と入力したら、B2:E2までのデータを抽出したいわけですね。

このような場合は、VLOOKUP関数をつかうことが、解決の一歩目となります。

B2に次の数式をつくったとします。

=VLOOKUP(A2,$A$6:$E$10,2,FALSE)

この数式をオートフィルで列方向にコピーしてみると、残念ながら、うまくデータを抽出することができません。

普通に、VLOOKUP関数で数式をコピーでは、対応できない数式ということがわかりました。

どこを、どのように修正すればいいのかを考えていきましょう。

最初の引数の検索値。

オートフィルで数式をコピーしたときに、検索値が動いてしまっては、検索することが出来ませんので、絶対参照をつかって、セル番地を固定しておく必要があります。

よって、検索値は、「$A$2」

2つ目の引数の範囲ですが、ここは、絶対参照をつかうことは変わりませんので、範囲は「$A$6:$E$10」

引数の範囲は、オートフィルで数式をコピーすることが前提の場合、絶対参照を設定することが多いです。

3つ目の引数の列数。

ここが一番のポイントになります。

「2」とか「3」のように、入力してしまうと、数式をコピーしただけ修正する件数が発生します。

そのため、「2」「3」…というように、連続した数値を自動的に入力する必要があります。

列方向に連続した数値が欲しい場合には、列番号を算出してくれる「COLUMN関数」をつかいます。

列番号に「COLUMN(B2)」と設定します。COLUMN(B2)は、B列なので、「2」を返してくれます。

列ではなくて行方向に連続した数値を算出させたい時には、ROW関数をつかいます。

最後の引数、検索方法は、完全一致なので、「FALSE」と入力します。

よって、修正した数式は、

=VLOOKUP($A$2,$A$6:$E$10,COLUMN(B2),FALSE)

と変更したら、オートフィルで数式をコピーして確認してみましょう。

これで、A2の数値を変更するだけで、列方向に連続するデータを容易に抽出することができました。

オートフィルターをつかって、抽出したデータをコピー&ペーストするという方法もありますが、VLOOKUPをつかってみると、簡単にデータを抽出することができます。

Excel。VLOOKUP関数で空白欄が「0（ゼロ）」で表示されてしまうので空欄にしたい【Zero display】

Excel。VLOOKUP関数で空白欄が「0（ゼロ）」で表示されてしまうので空欄にしたい

＜IFERROR+VLOOKUP関数＞

ExcelのVLOOKUP関数で、検索値が無いと、「#N/A」というエラーが表示されてしまうときには、IFERROR関数とVLOOKUP関数のネストで、対応することができます。

しかし、今回は、検索値はあるのだけど、範囲のデータが空欄の場合、「0（ゼロ）」で表示されてしまうので、それを空欄で表示したいわけです。

C2の数式を確認しておきましょう。

=VLOOKUP(A2,$A$6:$C$9,3,FALSE)

検索値は、A2。

範囲は、$A$6:$C$9

列番号は、3。

検索方法は、FALSE　で完全一致。

数式にミスは、ありませんね。

ところが、C7は空欄なので、C2の数式の結果は、空欄であってほしいのですが、「0（ゼロ）」と算出されてしまっています。

どうやったら、「0（ゼロ）」を空欄にすることができるのでしょうか。

そこで、C2の数式を次のようにしてみます。

=VLOOKUP(A2,$A$6:$C$9,3,FALSE)&””

「&””」をつけました。これで、文字型に変えることができます。

文字型にしたことで、空欄にすることができました。

これで問題解決と思ったら、大間違い。

NOを1にしてみます。

C2の値は90と表示はされているものの、左揃えになっています。

つまり、数値型のものも「&””」をつけたことで、文字型になってしまいました。

そこで、今度は、数値型に戻すために、C2の数式を次のように修正します。

=(VLOOKUP(A2,$A$6:$C$9,3,FALSE)&"")*1

「*1」することで、数値型にすることができます。

90は、右揃えに変わりました。

文字型を数字型にすることができたわけですが、では、空欄はどうなるのでしょうか？

NOを2にして確認してみましょう。

せっかく、空欄の対応をしたはずなのに、「#VALUE!」というエラーが表示されてしまいました。

原因は、文字に数字をかけたからです。

エラーを消すために、IFERROR関数とこの数式をネストしてみます。

C2の数式を次のように修正します。

=IFERROR((VLOOKUP(A2,$A$6:$C$9,3,FALSE)&"")*1,"")

結果を確認してみましょう。

空欄で表示することができました。

NOを1にしてみます。

きちんと、表示することができています。

このように、VLOOKUP関数をつかって、検索抽出結果が空欄のままで表示したい場合には、以外と数式をアレンジする必要が発生します。

残念ながら、XLOOKUP関数でも空欄は、「0（ゼロ）」とVLOOKUP関数と同様に、表示してしまいます。

今回のケースに限らず、関数をつかって算出した結果が、想像通りに算出されないことがありますので、その時には、色々アレンジが必要になることがあります。