Excel。LOOKUP関数は、縦横を指定しないで検索し値を返すこともできます。【LOOKUP】

Excel。LOOKUP関数は、縦横を指定しないで検索し値を返すこともできます。

＜関数辞典：LOOKUP関数＞

LOOKUP関数

読み方： ルックアップ　　

分類： 検索/行列 

LOOKUP(検索値,配列)

縦横を指定しないでセルを検索し対応するセルの値を返す

Excel。LOOKUP関数のベクトル形式は、1行/1列のセル範囲で検索し値を返します。【LOOKUP】

Excel。LOOKUP関数のベクトル形式は、1行/1列のセル範囲で検索し値を返します。

＜関数辞典：LOOKUP関数＞

LOOKUP関数（ベクトル形式）

読み方： ルックアップ　　

分類： 検索/行列 

LOOKUP(検索値,検索範囲,対応範囲)

1行/1列のセル範囲でセルを検索し対応するセルの値を返す　

Excel。VLOOKUP関数を使わずに多分岐はLOOKUP関数で手早く算出できます。【Multi-branch】

Excel。VLOOKUP関数を使わずに多分岐はLOOKUP関数で手早く算出できます。

＜LOOKUP関数＞

多分岐の処理をExcelで行うには、IF+IF関数のネスト。

IFS関数。

VLOOKUP関数やXLOOKUP関数など、様々な方法があります。

一番やりやすい、わかりやすい方法で処理するのがいいわけですが、意外とLOOKUP関数でも算出することができます。

次の表を用意しています。

E列に合計点によってA～E判定を行いたいわけです。

VLOOKUP関数などで求める場合と同じように、事前にG1:H6の判定表を用意しております。

なお、このような判定表は、昇順で作る必要があります。

降順で作成するとうまくいきません。

基本的には、IF+IF関数のネストで数式をつくると、判定表のような別表を用意しなくてすみますが、ネストが多くなり、可読性も悪くなってしまいます。

IF関数で数式をつくってみると、

=IF(D2>=160,"A",IF(D2>=140,"B",IF(D2>=120,"C",IF(D2>=80,"D","E"))))

やはり、かなり長いですね。

VLOOKUP関数で算出しようとすれば、E2の数式は、次のようになります。

=VLOOKUP(D2,$G$2:$H$6,2,TRUE)

4番目の引数をTRUEで設定することで、近似値の算出をおこなうことができるので、判定を算出できるわけです。

また、XLOOKUP関数をつかった数式だと、

=XLOOKUP(D2:D11,G2:G6,H2:H6,,-1,1)

スピル機能をつかうので、引数の検索値は、D2:D11という範囲で設定します。

XLOOKUP関数は、引数が多いですが、細かい設定ができるのも特徴ですね。

そこで、LOOKUP関数ならどうなるのでしょうか？

 

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

=LOOKUP(D2:D11,G2:H6)

たったこれだけです。

列番号もなければ、検索方法もありません。

LOOKUP関数の配列方式で、手早く算出することができます。

引数を確認しておきます。

検索値は、D2:D11。スピル機能をつかうので、一つのセルではなく、範囲選択で設定することができます。

二つ目の引数の配列には、G2:H6。

判定表の見出し行を除いたデータ部を範囲選択します。

絶対参照する必要もありません。

これで、算出することができました。

多分岐は、色々な方法で算出することができますが、LOOKUP関数という方法もありますので、使いやすいものをつかってみるといいかもしれませんね。

Excel。検索する値の一部だけ含む表から該当するデータを抽出したい。【Part of the data】

Excel。検索する値の一部だけ含む表から該当するデータを抽出したい。

＜LOOKUP+FIND関数＞

VLOOKUP関数は、とても使い勝手がいい関数なので、アチラコチラで使用されているのですが、対応できないケースというのも、結構あります。

例えば、次のような場合です。

やりたいことは、B3の担当者を、住所から判断して、その地域を担当している担当者名をB6:B8の中から抽出したいわけです。

簡単に言えば、神奈川県厚木市にお住いのお客様の担当は、誰なんだということ。

この程度の表ならば、目視で解決しますが、データ量が増えれば目視でというわけにもいきません。

VLOOKUP関数を使えば、抽出できるように思えますが、VLOOKUP関数では対応できません。

理由は、VLOOKUP関数の検索値です。

今回の検索値は、「神奈川県厚木市東町」とすると、範囲に該当するデータの地区には、「神奈川県厚木市東町」というデータはありません。

一致しませんから、検索方法の完全一致では対応できませんし、近似値で対応できるわけでもありません。

つまり、検索値と範囲の検索値が同じ、あるいは、数値の近似値でなければVLOOKUP関数をつかうことができないのです。

このようなケースの場合は、LOOKUP関数をアレンジしてつかうことで解決することができます。

B3に次の数式を設定してみましょう。

=LOOKUP(1,0/FIND(A6:A8,B2),B6:B8)

正しくデータを抽出することができました。

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

FIND(A6:A8,B2)　は何をしているのかというと、A6～A8のそれぞれのデータにB2の文字と合致するものが、何文字目にあるのかを算出することができます。

今回の場合、A6とA8は含まれていないので、「#VALUE」となり、A7は、1文字目にあるので1と算出されます。

常に1というケースならば、0で除算しなくてもいいのですが、5文字目に見つかった場合にも対応したいので、除算します。

該当するデータのみが「0」、それ以外はエラーとすることができます。

そして、LOOKUP関数をつかい、検索値を1に指定して、「0」と同じ番目にある担当者名を抽出することができます。

今回のケースのように、VLOOKUP関数をつかって対応することができない場合、何か他の関数を使えないのか、色々考えてみると、意外な方法が見つかるかもしれませんね。

Excel。検索する値の一部だけを含む表から対応する値を抽出したい【LOOKUP】

Excel。検索する値の一部だけを含む表から対応する値を抽出したい

＜IFERROR+LOOKUP+FIND関数＞

何をしたいのかを、まずは確認しておきましょう。

A列の住所の文字列。

例えば、A2に横浜市という文字が含まれています。

横浜市という文字が含まれていたら、D1;E4までの担当者リストから、横浜市の担当である。内藤さんの名前をB2に表示するという作業です。

ワイルドカードをつかうのか？VLOOKUP関数をつかうのか？

あれやこれやと考えると思います。

ただ、VLOOKUP関数では、解決しないことは、すぐにわかります。

では、B2には、どのような数式が設定しているのか、確認しましょう。

=IFERROR(LOOKUP(0,0/FIND($D$2:$D$4,A2),$E$2:$E$4),"")

最初のIFERROR関数をつかっているのは、該当するデータが無い場合「#N/A」エラーが表示されるので、エラー表示を空白にするようにするために使っています。

わかりにくいのが、LOOKUP関数ですが、先にFIND関数から説明します。

D列の地区名のデータが、A2の住所の文字内に含まれているのかを確認します。

このケースでは、5,#VALUE!,#VALUE!と算出されます。

5と算出されたのは、A2のセルに横浜市という文字が、左から5文字目にあることを意味しています。

そして、LOOKUP関数。

今回は、LOOKUP(検索値,検索範囲,対象範囲)という引数をもっている、ベクトル形式のLOOKUP関数で処理をします。

最初の検索値は、「0」。

次の引数の「検索範囲」から「0」をみつけるためのものです。

検索範囲は、0を先程紹介したFIND関数で算出した値で除算させます。

なぜこのようなことをしているのかというと、FIND関数で見つからなかったものはエラーと算出されています。

「0/エラー」と計算させたところで、結果はエラーになるわけです。

今回は、「0」を探していますので、エラーと算出されているものを除外することができるというわけです。

逆に、今回のように「5」という算出結果が出ているのであれば「0/5」となり「0」という結果を算出します。

こうすることで、検索値が「0」のものをみつけることができるわけです。

最後の引数の対象範囲には、「$E$2:$E$4」と担当列を設定します。

オートフィルで数式をコピーするので、絶対参照も忘れずに設定しておきます。

これで、「0」の結果の行を抽出することになるので、担当の列に、それぞれの担当者名を表示することができるというわけです。

なかなか、使う機会が少ない、「LOOKUP関数」ですが、VLOOKUP関数でもダメ、HLOOKUP関数でもダメというケースでは、意外と活躍する関数なので、LOOKUP関数も抑えておくといい関数なのかもしれませんね。

Excel関数辞典 VOL.47。LOGINV関数～LOWER関数【dictionary】

Excel関数辞典　VOL.47。LOGINV関数～LOWER関数

＜Excel関数＞

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

LOGINV関数

ログインバース

累積確率から対数正規分布を算出

LOGINV(確率,平均,標準偏差)

LOGNORMDIST関数

ログノーマルディスト/ログノーマルディストリビューション

対数正規分布の累積確率を算出

LOGNORMDIST(x,平均,標準偏差,関数形式)

LOGNORM.DIST関数

ログノーマル・ディスト/ログノーマル・ディストリビューション

対数正規分布の累積確率か確率密度を算出

LOGNORM.DIST(x,平均,標準偏差)

LOGNORM.INV関数

ログノーマル・インバース

累積確率から対数正規分布を算出

LOGNORM.INV(確率,平均,標準偏差)

LOOKUP関数

ルックアップ

1行/1列のセル範囲でせるを検索し対応するセルの値を返す　

LOOKUP(検索値,検索範囲,対応範囲)　ベクトル形式　

縦横を指定しないでセルを検索し対応するセルの値を返す

LOOKUP(検索値,配列)

LOWER関数

ロウアー

英字を小文字に変換する

LOWER(文字列)

Excel。VLOOKUP関数やHLOOKUP関数はダメで、LOOKUP関数を使用する場合

Excel。VLOOKUP関数やHLOOKUP関数はダメで、LOOKUP関数を使用する場合

＜VLOOKUP関数・HLOOKUP関数・LOOKUP関数＞

現場ではあまり、発生しないかもしれませんが、MOS（マイクロソフトオフィススペシャリスト）のExcel 2016 Expertの出題範囲でLOOKUP関数がありまして、受験したい方から、『なんでVLOOKUP関数ではダメで、LOOKUP関数でないといけないのか？』というご質問をよく受けることがありますので、今回は、LOOKUP関数になるケースを確認してみましょう。
次の表があります。

【VLOOKUP関数のおさらい】

F2に、E2の商品コードを検索して、商品名をA1:C4のリストを使って抽出する場合には、VLOOKUP関数を使うことは結構お馴染みですよね。

最初は、おさらいということで、F2にVLOOKUP関数で抽出してみます。

なお、今回は、話がブレないようにするために、IF+VLOOKUP関数ではなくて、VLOOKUP関数単独で作っていきましょう。

検索値は、E2。検索するキーになる場所で、コンビニとかのPOSレジでバーコードを読む処理と同じですね。

範囲は、商品リスト全体ですので、A2:C4。

見出し行は不要ですね。

あと今回は、オートフィルを使って数式をコピーする必要がないので、絶対参照も設定しません。

列番号は、範囲で選択したリストの中で、抽出したいデータは商品名なので2列目ということで、2と入力します。

検索方法は、完全一致なので、FALSE。
または、0（ゼロ）と入力して、OKボタンをクリックします。

F2の数式は、=VLOOKUP(E2,A2:C4,2,FALSE)
商品名は、商品コードと合致するペンと表示されました。

では、LOOKUP関数ではできるのでしょうか？

【LOOKUP関数でも抽出できる】

F2をクリックして、LOOKUP関数ダイアログボックスを表示しますが、引数の選択ダイアログボックスが表示されるため、

手入力で作成すると効率化いい関数なので、慣れてきたら手入力でおこなうといいでしょう。

今回のケースは、検索値,検索範囲,対応範囲の3つの引数を選んで、OKボタンをクリックします。

検索値はE2。
検査範囲には、A2:A4。検索値がある列を選択します。
対応範囲には、B2:B4。抽出したいものがある列を選択します。
OKボタンをクリックして確認してみましょう。

先程のVLOOKUP関数と同じ結果で抽出することができました。

ここからが本題。VLOOKUP関数がダメなケースを確認してみましょう。

【VLOOKUP関数が使えない！】

使うのは次の表。

変わっているのは、A列とB列が入れ替わっていますね。実はここがポイント。
では、F7をクリックして、VLOOKUP関数で抽出してみましょう。

検索値には、E7。
範囲には、A7:C9
列番号は1。商品名は1列目にあるので、1。
検索方法は、FALSE
これで、OKボタンをクリックします。

残念ながら、#N/Aというエラーが表示されてしまいました。

原因は、商品コードのような検索範囲の左端の列や上端の行にキーとなるデータがないと抽出することがVLOOKUP関数やHLOOKUP関数では出来ないのです。

今回は検索のキーとなる商品コードがB列にあり、抽出したい商品名がA列。

つまり、検索のキーの左側にあるために抽出することができませんでした。

このようなケースでは、LOOKUP関数を使わないと抽出することができません。

最後のLOOKUP関数では算出できるのかを確認しておきましょう。

【LOOKUP関数なら大丈夫】

F7をクリックして、LOOKUP関数を作っていきましょう。

検査値には、E7。
検査範囲には、B7:B9
対応範囲には、A7:A9
では、OKボタンをクリックしましょう。

きちんと、商品名を抽出することができましたね。

なお、F7の数式は、
=LOOKUP(E7,B7:B9,A7:A9)

ということで、検索値や検査値の右側に抽出したいデータがあるかないかで、使い分けがあると覚えおくといいかもしれませんね。

Excel。LOOK。あれれ？LOOKUP関数を使ってみたら、ちゃんと検索されないぞ！

Excel。あれれ？LOOKUP関数を使ってみたら、ちゃんと検索されないぞ！

＜LOOKUP関数＞

先日打ち合わせに行った際に、先方の担当さんから、
ちょこっと教えてほしいんだけど…ということで、伺ってみると、
なんでも、LOOKUP関数を使ってみたんだけど、
ちゃんと検索してくれなくて困っているとのこと。

LOOKUP関数は、たぶん、VLOOKUP関数よりもマイナーだと思うわけでして、
似ているかと思えば、全く似ていない別の検索系の関数なんですよね。

では、下記のような表があります。

E3に入力されている日付に近い日を検索して、その商品名を検索するというのをやりたいのです。

では、F3をクリックして、LOOKUP関数ダイアログボックスを表示しましょう。

すると、引数の選択ダイアログボックスが表示されてきます。ドキッとしちゃいますよね。
上段は、検索値,検索範囲,対応範囲の3つの引数を使う、

【ベクトル形式】

と
下段は、検索値,配列の2つの引数を使う、

【配列形式】

の2つを選択することが出来る関数なんですね。
で、今回は上段のベクトル形式を使いますので、選択して、OKボタンをクリックしましょう。

検索値には、E3の検索したい日付
検索範囲には、B2:B21の表の日付の列
対応範囲には、C2:C21の商品名
を設定しました。

これで、問題はないはずなので、OKボタンをクリックしましょう。
すると、

Jというのが検索されました。
これで、終わり…と思ったら大間違い。2016/2/10に一番近いのは、Jではなくて、

本当は、Iのはずです。
これが、このLOOKUP関数がちゃんと、検索してくれないといったのでしょう。

実は、このLOOKUP関数。
データの小計と同じように、検索範囲を先に昇順で並び替えを行っておかないと、
今回のように、正しい結果を算出してくれないことがあるのです。

まさか、関数をつかうだけなのに、並び替えもしなくてはいけないというのが、
最大のポイントなんですね。

では、日付を並び替えをして、改めて算出してみましょう。

B1をクリックして、データタブの昇順ボタンをクリックしましょう。

さて、並び替えが終わりましたので、先ほどの結果はどうなっているでしょうか？

今度は、Iと算出されましたね。先ほどは、Jでしたので、今回はちゃんと、検索されましたね。

ですので、LOOKUP関数を使うときには、
キーになる列で並び替え（昇順）で行うことを忘れないようにする。

そして、引数は【ベクトル形式】で、LOOKUP関数を作成していくという、
この2点に注意をしていただいて、作っていくといいと思いますよ。