Excel。縦横の見出しの交わるデータをXLOOKUP関数で抽出してみた【Cross Tabulation】

Excel。縦横の見出しの交わるデータをXLOOKUP関数で抽出してみた

＜XLOOKUP関数＞

縦横の見出しの交わるところにあるデータを抽出する方法は、色々あります。

例えば、INDEX関数とMATCH関数を組み合わせて使う方法があります。

INDEX関数とMATCH関数の組み合わせは慣れると、コツをつかむことができるのですが、少し難解なところもあります。

そこで、今回は、XLOOKUP関数だけで、対応してみたいと思います。

やりたいことは、B7に曜日、B8に時限を設定したら、B9に該当する講義内容を表示するというものです。

B7が金曜日で、B8が2限ということなので、金曜日の2限は、「PowerPoint」です。

このPowerPointをXLOOKUP関数だけで、表示していきます。

B9をクリックして、次の数式を設定します。

=XLOOKUP(B7,B1:F1,XLOOKUP(B8,A2:A5,B2:F5,"",0,1))

これで、縦見出しと横見出しがクロスするデータを抽出することができます。

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

数式の構造は、XLOOKUP関数にXLOOKUP関数をネストさせています。

最初のXLOOKUP関数ですが、

最初の引数の検索値は、曜日が設定されている、B7です。

2つ目の引数の検索範囲は、B1:F1の曜日を設定します。

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

ここに、XLOOKUP関数をネストしていきます。

4つ目以降の引数は、省略可能です。

今回は、ネストのXLOOUP関数を設定すれば大丈夫なので、省略しました。

では、ネストである、戻り範囲で設定したXLOOKUP関数をみていくことにします。

XLOOKUP(B8,A2:A5,B2:F5,"",0,1)

1つ目の引数は、検索値なので、時限が設定されているB8。

2つ目の引数は、検索範囲なので、A2:A5を設定します。

3つ目の引数は、戻り範囲なので、B2:F5の教科を範囲選択します。

4つ目の引数は、見つからない場合なので、「””（ダブルコーテーション×2）」で空白とします。

5つ目の引数は、一致モード。今回は、完全一致にしたいので、「0」を採用します。

6つ目の引数は、検索モード。上側からでかまわないので、「1」と設定します。

このように、INDEX関数＋MATCH関数の組み合わせでもいいのですが、XLOOKUP関数を重ねてつかうことでも対応することができます。

Excel。XLOOKUP関数で連続する複数の項目を表引きしたい【Search and Extract】

Excel。XLOOKUP関数で連続する複数の項目を表引きしたい

＜XLOOKUP関数＞

連続する項目を表引きしたい場合、VLOOKUP関数だと何度も数式を作る必要があります。

例えば、次のような表。

A1:F9に表があって、A11:F13に表引きの結果を表示したいわけです。

VLOOKUP関数の場合、B12に設定した数式を、オートフィルで数式をコピーする場合、まず、検索値を列固定の複合参照にした上で、3番目の引数の「列番号」をコピー後に一つずつ修正する。

あるいは、COLUMN関数をつかって列番号に対応する必要があります。

慣れれば特に問題はないかもしれませんが、少々面倒です。

そこで、XLOOKUP関数をつかってみたらどうなるのか、確認をしてみましょう。

B12にXLOOKUP関数をつかった数式を設定します。

=XLOOKUP(A12,$A$2:$A$9,$B$2:$F$9,"",0,1)

スピル機能によって、行方向には、数式をコピーする必要はありません。

列方向には、オートフィルで数式をコピーする必要があります。

これで、連続する複数項目の表引きを行うことができました。

COLUMN関数をつかわなくても大丈夫です。

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

=XLOOKUP(A12,$A$2:$A$9,$B$2:$F$9,"",0,1)

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

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

検索値のある列なので、$A$2:$A$9。

オートフィルで数式をコピーする必要があるので、絶対参照を忘れずに設定します。

3つ目の引数は、戻り範囲。抽出したい列です。

連続する列なので、$B$2:$F$9。

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

ここをまとめて設定することができるので、VLOOKUP関数よりもわかりやすいかと思われます。

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

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

5つ目の引数は、一致モード。

完全一致なので「0」を設定します。

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

上から検索しますので、「1」と設定します。

Excel。関数で指定したランクS・A・B順で並べ替えしたい【SORT】

Excel。関数で指定したランクS・A・B順で並べ替えしたい

＜XLOOKUP関数＞

指定した順番で並べ替えを行いたい場合には、ユーザー設定リストに登録するといいのですが、今回は、ユーザー設定リストはそのままにしたい場合、どのようにしたらいいのでしょうか。

次の表を用意しました。

C列のチームがSABとわけてあります。

これをSABの順番で並べ替えをしたいわけです。

当然、昇順にしたら、ABSという順番になってしまうので、指定した順番で並べ替えをしたいわけです。

まずは、SABという順番にしたいので、Sが1Ａが2。Bが3というように割り振ります。

SABと3つならば、IF関数やIFS関数。

あるいはSWITCH関数など色々な方法があります。

ただ、振り分けの種類が10とか20とか多い場合のことも考慮して、今回はXLOOKUP関数をつかってみることにしました。

VLOOKUP関数でもいいのですが、別表の作り方が面倒なので、XLOOKUP関数を採用しました。

F1:G4に表引き元の表を用意しました。

D2には、

=XLOOKUP(C2:C11,G2:G4,F2:F4,"",0,1)

という数式を設定します。

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

XLOOKUP関数を確認しておきます。

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

C2:C11のチームを選択します。

スピル機能をつかいたいので、セルではなく、フィールドで選択します。

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

G2:G4の表引き用のチームを選択します。

スピル機能をつかうので、絶対参照は不要です。

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

F2:F4を選択します。

この値をつかって、後ほど、並べ替えをおこないます。

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

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

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

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

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

データ量が少ないので、「1」の先頭からでかまいません。

これで、チームごとの数値を割り振ることができました。

D列は、数式なので、このまま並べ替えを行うことができません。

XLOOKUP関数で求めた結果を値としてコピー貼付けをします。

D2:D11をコピーします。

そして、値で貼り付けます。

値で貼り付けたい場合は、Ctrl+Shift+Vというショートカットキーが便利です。

数式ではなくなったので、あとはデータタブの昇順ボタンをクリックして完了です。

Excel。横方向に表引きするHLOOKUP関数の代わりにXLOOKUP関数でやってみる【beside】

Excel。横方向に表引きするHLOOKUP関数の代わりにXLOOKUP関数でやってみる

＜XLOOKUP関数＞

横長の表から、データを表引きするには、HLOOKUP関数をつかいます。

HLOOKUP関数の使い方は、VLOOKUP関数と変わりません。

そこで、今回は、XLOOKUP関数をつかって、横方向に表引きしてみます。

A7に品川店と入力しております。

品川店の差のデータをXLOOKUP関数で算出します。

B7の数式は、

=XLOOKUP(A7,B1:F1,B4:F4,"",0,1)

これで、品川店の差を表引きすることができました。

では、数式を確認します。

最初の引数は、検索値。

A7に入力されたデータの差を表引きしたいので、A7

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

検索値がある範囲なので、B1:F1を範囲選択します。

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

表引きしたいデータの範囲です。

差を表示したいので、B4:F4を範囲選択します。

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

データがない場合には、空白とするので、「””（ダブルコーテーション×2）」

5つ目の引数は、一致モード。完全一致にしたいので、「0（ゼロ）」

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

大量のデータではないので、先頭から末尾に向けて検索としますので、「1」

これで、差を表引きすることができました。

Excel。以上未満の条件で表引きするならXLOOKUP関数がオススメです。【XLOOKUP】

Excel。以上未満の条件で表引きするならXLOOKUP関数がオススメです。

＜XLOOKUP関数＞

AからEに分かれたランク一覧から、得点ごとのランクを表引きしたいのですが、

VLOOKUP関数でやってみたら、表引きすることができませんでした。

 

C2の数式は、

=VLOOKUP(B2,$E$2:$F$6,2,TRUE)

と設定しています。

検索値は、B2で、範囲は、E2:F6として絶対参照の設定もしている。

列番号もランクの2にしている。

そして、検索方法は、近似値のTRUE。

数式には問題はないのですが、表引きするためのE2:F6の表が問題なのです。

何が問題なのかというと、今回のような近似値でVLOOKUP関数を使いたい場合、表引きの範囲は、昇順で作らないといけません。

では、E列を昇順で並べ替えをしてみます。

 

表引きが出来たことが、確認できました。

このようにVLOOKUP関数をつかうと、確かに、表引きはできるのですが、範囲の表が昇順でなければいけないというのが、ネックです。

通常、このような表は、降順で作成します。

そこで、VLOOKUP関数でもいいのですが、XLOOKUP関数をつかってみるという方法があります。

 

C2にXLOOKUP関数をつかった数式を設定しました。

=XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,"",-1,1)

範囲が降順のままでも、ランクを表引きすることができました。

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

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

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

オートフィルで数式をコピーするので、$E$2:$E$6　と絶対参照を設定します。

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

ランクの列なので、$F$2:$F$6。

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

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

空白で表示したいので「””」と「””(ダブルコーテーション×2)」と設定します。

5つ目の引数は、一致モード。

今回は完全一致ではありません。

「-1」と設定します。

「-1」は完全一致または、次に小さい項目を表引きしてくれます。

内輪の数なので、小さい項目を選ぶ必要があります。

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

「1」と設定します。

データの上部から下部へ検索させます。

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

ただ、今回は、VLOOKUP関数との違いも含めて紹介しました。

そのため、オートフィルで数式をコピーするようにしましたが、XLOOKUP関数は、ネスト機能に対応していますので、範囲のところを絶対参照にしなくても、次のように数式を設定すれば大丈夫です。

=XLOOKUP(B2:B6,E2:E6,F2:F6,"",-1,1)

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。複数の項目を組み合わせて検索するには、どうすればいいの。【Table pull】

Excel。複数の項目を組み合わせて検索するには、どうすればいいの。

＜XLOOKUP関数＞

検索値が1つならば、VLOOKUP関数などをつかうことで、手早く検索して抽出することができます。

では、次の表のように、2つの項目が抽出条件の場合、どのようにしたら、該当するデータを抽出することができるのでしょうか。

A1:D7にリストがあります。

検索したい条件は、

F1:G2にあります。

検索条件は、

店舗名は　新宿

フロアーは　2

この2つの項目に合致する販売金額を　G3に抽出したいわけです。

VLOOKUP関数の場合だと、店舗名とフロアーを合体させた列を作って…など、色々考えないといけません。

そこで、XLOOKUP関数をつかえば、手早く抽出することができます。

G3にXLOOKUP関数の数式を設定します。

=XLOOKUP(G1&G2,B2:B7&C2:C7,D2:D7,"",0,1)

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

では、数式を確認します。

最初の引数は、「検索値」には、G1&G2　と設定します。

検索値が2つありますので、「&（アンパサンド）」で結合します。

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

ここも検索範囲が2つありますので、「&（アンパサンド）」で結合させます。

よって、B2:B7&C2:C7

3つ目の引数は、「戻り範囲」は、抽出結果なので、D2:D7　と設定します。

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

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

5つ目の引数は、「一致モード」です。VLOOKUP関数でいうところに、検索方法に該当します。

完全一致で対応させたいので、「0（ゼロ）」と設定します。

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

先頭データから検索するかなど、検索方法を設定することができます。

大きなデータではないので、「1」の先頭から最終行へ向けて検索する方法にしました。

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

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

＜Excel関数辞典：VOL.91＞

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

XIRR関数

読み方： エックスアイアールアール　

読み方： エクストラ・アイ・アール・アール

分類： 財務 

XIRR(範囲,日付,[推定値])

非定期キャッシュフローに対しる内部利益率を算出します　

XLOOKUP関数

読み方： エックスルックアップ　　

分類： 検索/行列 

XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])

範囲または配列で一致の検索を行い、2つめの範囲または配列から対応する項目を返す　

XMATCH関数

読み方： エックスマッチ　　

分類： 検索/行列 

XMATCH(検索値,検索範囲,[一致モード],[検索モード])

配列内での項目の相対的な位置を返す　

XNPV関数

読み方： エックスエヌピーヴイ　　

読み方： エクストラ・ネット・プレゼント・バリュー

分類： 財務 

XNPV(割引率,キャッシュフロー,日付)

非定期キャッシュフローに対する正味現在価値を算出します　

XOR関数

読み方： エックスオア　

読み方： エクスクルーシブ・オア

分類： 論理 

XOR(論理式1,[論理式2],…)

複数の条件で奇数の数を満たすかどうかを調べる

Excel。表の行列が交わるデータを手早く抽出するならXLOOKUP関数がオススメ【cross table】

Excel。表の行列が交わるデータを手早く抽出するならXLOOKUP関数がオススメ

＜XLOOKUP関数＞

今までは、INDEX関数とMATCH関数を組み合わせないと、算出することが難しかったことが、XLOOKUP+XLOOKUP関数というXLOOKUP関数のネストで、算出することができますので、その方法をご紹介します。

次の表を用意しました。

A1:G4には、運送料の表があります。

重さと地域が交わるところが、その運送料なわけです。

これを算出するのに、以前は、INDEX関数とMATCH関数をつかうことで、算出していましたが、少々わかりにくいところがありました。

ちなみに、B9の送料をINDEX関数とMATCH関数で数式を設定すると、

=INDEX(B2:G4,MATCH(B7,A2:A4,1),MATCH(B8,B1:G1,0))

行と列の座標をMATCH関数で算出させるわけですね。

これと同じように、XLOOKUP関数で、算出することができます。

B9にXLOOKUP関数をつかった数式を設定してみます。

=XLOOKUP(B8,B1:G1,XLOOKUP(B7,A2:A4,B2:G4,,-1,1))

これで、先程の、INDEX関数とMATCH関数と同じように算出することができます。

このXLOOKUP関数の数式のポイントは、

最初のXLOOKUP関数で、地域の「列」の座標軸になっています。

引数内の2つ目のXLOOKUP関数が、重さである、「行」の座標軸で使用しています。

XLOOKUP(B7,A2:A4,B2:G4,,-1,1)

重さが完全一致だけとは限りません。

そこで、一致モードを「-1」に設定することで、近似値で算出できます。

XLOOKUP関数は、アイディアによって、色々使える可能性がありますね。

Excel。検索値の文字数が異なってもと合致するデータを手早く検索抽出したい。【extract】

Excel。検索値の文字数が異なってもと合致するデータを手早く検索抽出したい。

＜XLOOKUP＋LEFT関数＋ワイルドカード＞

東日本を「EJP」、東京を「TK」というように、検索値が文字列の一部で、しかも文字数が一致していないデータを元に、検索抽出したい場合は、どのようにしたらいいのでしょうか。

例えば、次の表。

C2:C5の地域番号は、ハイフンよりも左側で、地域を区分しています。

ハイフンから左側の文字列と合致する地域名を、A7:B10の表から、検索抽出したいわけです。

VLOOKUP関数でもいいのですが、XLOOKUP関数で数式をつくっていきます。

C2に次のように数式を設定します。

=XLOOKUP(LEFT(B2:B5,2)&"*",A8:A10,B8:B10,"",2,1)

これで、検索値の文字数が異なっても手早く検索抽出することができます。

XLOOKUP関数をつかうことで、オートフィルで数式をコピーしなくても、スピル機能によって、数式がコピー（ゴースト）されますし、絶対参照も不要です。

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

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

地域番号と合致させるには、ハイフンから左側の文字列が対象になるわけですが、ハイフンから左側の文字数が異なっているため、単純に左から何文字というわけにはいきません。

FIND関数をつかって、ハイフンまでの文字数をカウントして、その数をつかって抽出という方法もありますが、XLOOKUP関数には、一致モードのワイルドカードをつかうことができます。

幸い、「EJ」「WJ」「TK」と左から2文字だけ抽出しても、区分けがつくことがわかります。

そこで、検索値は、左側から指定した文字列を抽出するLEFT関数をつかい設定します。

LEFT(B2:B5,2)&"*"

「&”*”」とワイルドカードを文字結合します。

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

検索値を元にどこの範囲から検索するのかを設定しますので、

A8:A10

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

表示したいデータは、どの列にあるのかを指定しますので、B8:B10。

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

今回は、「””（ダブルコーテーション×2）」と空白にするように設定します。

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

完全一致ではなく、ワイルドカードで検索させますので、「2」と設定します。

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

データも多くないので、通常通り、先頭から後方に向けて検索させますので「１」。

XLOOKUP関数とLEFT関数。そしてワイルドカードを組み合した方法をご紹介しました。

Excel。表から抽出するデータが検索値の左側ならXLOOKUP関数の出番です。【XLOOKUP】

Excel。表から抽出するデータが検索値の左側ならXLOOKUP関数の出番です。

＜XLOOKUP関数＞

VLOOKUP関数は、表から抽出する時に、とても便利な関数ですが、残念ながら万能ではありません。

次の表のような場合は、対応することができないからです。

A8の商品番号を入力したら、A1:D4の表から、該当する商品名をB8に表示するには、VLOOKUP関数をつかうことで算出できます。

B8の数式は、

=VLOOKUP(A8,B2:D4,2,FALSE)

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

ところが、C8のアンケート結果を抽出するには、VLOOKUP関数では対応することができません。

VLOOKUP関数の2番目の引数、「範囲」には、B2:D4を設定します。

3番目の引数の「列番号」で、2番目の引数で設定した「範囲」の左側から何番目のデータを抽出するのかという意味の「列番号」を設定するわけです。

今回は、左側から2番目に商品名があるので「2」と設定するわけです。

アンケート結果を抽出したい場合は、「範囲」の左側にあります。

仮に「範囲」を左側に拡張しても、検索値の左側になってしまうので、「列番号」を「-1」と設定することは出来ません。

このようにVLOOKUP関数では、検索値の左側からデータを抽出することができません。

そこで、登場するのが「XLOOKUP関数」です。

C8にXLOOKUP関数をつかって、数式を設定してみましょう。

=XLOOKUP(A8,B2:B4,A2:A4,"",0,1)

これで、抽出した結果を表示することができました。

XLOOKUP関数は、検索値の左側であっても関係なく抽出することができる関数です。

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

1番目の引数は、「検索値」なので、A8

2番目の引数は、「検索範囲」なので、B2:B4

3番目の引数は、「戻り範囲」なので、A2:A4

この戻り範囲が、抽出したいデータがある範囲です。

4番目の引数は、「見つからない場合」なので、「””」。

もしデータがなければ「””（空白）」と表示するようにしています。

VLOOKUP関数だと#N/Aが表示されるのですが、それを防止することもできます。

5番目の引数は、「一致モード」なので、「0」の完全一致を選択します。

VLOOKUP関数の検索方法をFALSEの完全一致にするのと同じですね。

6番目の引数は、「検索モード」。

これは、データの先頭から検索させることにしますので「1」としました。

VLOOKUP関数もわかりやすい関数でしたが、XLOOKUP関数だと、より多くのケースにも対応してくれます。

使えるバージョンならば、つかってみると、数式を改善することができるかもしれませんね。

Excel。通知書など5段階相対評価を算出するにはどうしたらいいの？【Relative evaluation】

Excel。通知書など5段階相対評価を算出するにはどうしたらいいの？

＜XLOOKUP+PERCENTRANK.INC関数＞

資料を作成するにあたり、相対的に評価をつけないといけないことがあるわけです。

例えば、先生ご用達というか、「通知書」などの相対評価表です。

では、どうやったら、楽に相対評価を算出することができるのでしょうか？

次の表のD列の評価フィールドを算出していきます。

準備するのは、F1:H6の相対評価表です。

H2は、累計を算出するための数式が設定してあります。

H2の数式は、

=SUM($G$2:G2)

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

作業スペースをつくらないで、一発で算出するならば、XLOOKUP+ PERCENTRANK.INC関数で算出できるのですが、説明の為、数式を分解して説明していきます。

得点が全体でどの位置にあるのかという相対位置がわからないことには、相対評価を算出することはできません。

そこで登場するのが、PERCENTRANK.INC関数です。

PERCENTRANK.INC関数は、百分位を算出することできる関数です。

E列には、PERCENTRANK.INC関数をつかって百分位を算出しています。

E2の数式は、

=PERCENTRANK.INC($C$2:$C$11,C2,2)

最初の引数は、配列。

範囲選択なので、C2:C11。オートフィルで数式をコピーするので、絶対参照を設定していきます。

2つ目の引数は、C2。

これで、C2の値は、全体の中で、何パーセントの位置にあるのかがわかります。

最後の引数は、有効桁数。

小数点第何位まで表示するのかということですね。

この数式を、次のように変更する必要があります。

E2の数式を変更します。

=1-PERCENTRANK.INC($C$2:$C$11,C2,2)

なぜ、1から減算する必要があったのかというと、原因は、評価表にあります。

百分位は、100％の位置にあるデータは100%と算出されるのはいいのですが、Aの配分累計は10%の位置で、Eの配分累計が100%となっています。

ようするに「逆」になっているので、１から減算する必要があるわけです。

あとは、=1-PERCENTRANK.INC($C$2:$C$11,C2,2) で算出した値が、どの配分累計に属しているのかを算出すればいいわけです。

VLOOKUP関数をつかって評価を検索抽出するように思われますが、F1;H6の表。

一番左側の列に、検索抽出したい値があるわけです。

これだと、VLOOKUP関数は使えません。

INDEX+MATCH関数なんて方法もありますが、XLOOKUP関数が登場したわけですから、XLOOKUP関数をつかえば、一番左側に検索抽出の列があっても問題ありません。

D2に設定した数式は、

=XLOOKUP(E2,$H$2:$H$6,$F$2:$F$6,,1,-1)

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

検索値が、「E2」。

検索範囲が、「$H$2:$H$6」　この範囲は、検索したい範囲です。

戻り範囲が、「$F$2:$F$6」　抽出したい範囲です。

見つからない場合は、省略とします。

一致モードには「1」。これは、「完全合致または次に大きい項目」を選択します。

検索モードには、「-1」の「末尾から先頭へ検索」を選択しました。

本当ならば、スピル機能を考えて範囲選択するのですが、今回は、VLOOKUP関数っぽく使用しております。

二段階の計算式を一つにまとめたのが、次の数式です。

=XLOOKUP(1-PERCENTRANK.INC($C$2:$C$11,C2,2),$H$2:$H$6,$F$2:$F$6,,1,-1)

この数式を、D2に設定すれば相対評価を算出することができます。

見慣れない関数のネストで、長い数式になると、わかりにくくなりますので、二段階で算出するのでいいと思います。

ということで、Excelには様々な関数が用意されていますので、色々組み合わせみると、意外につかえるネストとか見つかるかもしれませんね。

Excel。曜日ごとの最新の最大値を算出するにはどうしたらいいの？【By day of the week】

Excel。曜日ごとの最新の最大値を算出するにはどうしたらいいの？

＜TEXT関数・MAXIFS関数・XLOOKUP関数＞

曜日別に最大値を算出したいとします。

しかも、最大値が同じ場合は、新しい日付を優先して算出させたいわけです。

次の表をつかって、今回は、Office365のExcelをつかって算出していきます。

この表の補足として、数式を使ってB列の曜日を算出しています。

B2の数式は、

=TEXT(A2,"aaa")

というように、TEXT関数をつかっています。

WEEKDAY関数でもいいのですが、TEXT関数の方が何かと便利ですし、楽です。

あと、算出する場所を確認しておきます。

E列の曜日は、「日」というように直接入力しております。

F列の最大値から先に算出していきます。

その後、最大値の数値が何日なのかを算出していきます。

日曜日の最大値を算出したいので、ここで使用する関数は、「MAXIFS関数」です。

MAXIFS関数は、条件付き最大値を算出することができる関数です。

F2に数式を設定していきます。

=MAXIFS(C2:C15,B2:B15,E2:E8)

数式を確定すると、オートフィルで数式をコピーしなくても、日曜日から土曜日までの最大値が算出されました。

これは、Excelに新しく加わった「スピル機能」をつかったことで、オートフィルで数式をコピーしなくても、算出してくれます。

あと、絶対参照の設定も不要です。

なお、スキル機能をつかわなくても、算出することはできます。

その場合の数式は、

=MAXIFS($C$2:$C$15,$B$2:$B$15,E2)

とすればOKです。

また、MAXIFS関数は、

MAXIFS(最大範囲,条件範囲1,条件1,…)

という引数をもっていて、複数条件にも対応しております。

まずは、最大値を算出することができました。

つづいて、日付を算出していきます。そして、直近の日付を優先したいわけです。

ここで、使用する関数は、「XLOOKUP関数」です。

VLOOKUP関数を使いたいところですが、範囲の右側（一番左側にない）なので、VLOOKUP関数では対応できません。

それでは、G2にXLOOKUP関数の数式を設定していきます。

XLOOKUP関数は、ダイアログボックスで設定するよりも、手入力するほうがいいかと思います。

またこのXLOOKUP関数は、先程紹介した「スピル機能」をつかうことができます。

なので、検索値は、F2のみを選択するのではなく、F2:F8までを範囲選択すると、「F2#」と表示がかわります。

検索範囲は、C2:C15。

スピル機能をつかいますので、絶対参照は不要です。

戻り範囲は、A2:A15。

日付を算出したいわけですから、戻り値は、日付ですね。

見つからない場合は、「””」（空白）とします。

一致モードは、完全一致ですので、「0」（ゼロ）を設定します。

検索モードは、「-1」（マイナス1）を設定します。

「-1」にすることで、末尾から先頭へ検索してくれます。

これは、最大値が同じ場合は、直近の日付にしたいので、「-1」とすれば、いいわけです。

ただし、日付を昇順にしておかないと意味がありませんので、注意が必要です。

確定して完成です。

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

=XLOOKUP(F2#,C2:C15,A2:A15,"",0,-1)

このような方法で、今回は、曜日別の最大値を算出してみました。

Excel。XLOOKUP関数をつかうと、楽に、上位3位の点数に該当する氏名を抽出できます。【XLOOKUP】

Excel。XLOOKUP関数をつかうと、楽に、上位3位の点数に該当する氏名を抽出できます。

＜SMALL+ROW関数&XLOOKUP関数＞

最高値を算出するには、MAX関数を使いますし、2位や3位など指定の順位を算出するには、LARGE関数をつかいます。

ただ、困ったことに、数値そのものしか算出することができません。

どういうことかというと、次の表があります。

 

AB列の表は、ある競技の時間の表です。

上位3位までのタイムのいい人を抽出したいわけです。

作業としては、最初にD列を算出します。

数値が小さい上位3名までなので、SMALL関数を使えばOKです。

D2の数式を確認してみましょう。

=SMALL($B$2:$B$11,ROW(A1))

引数のROW(A1)は何を意味似ているのかというと、ROW(A1)としないで、1位を算出したいので、「1」と設定したら、オートフィルで数式をコピーしても、「1」のままなので、「2」「3」と修正しないといけません。

ROW関数をつかって、数式を修正する手間を省いています。

SMALL関数を使うことで、上位1～3位までのデータを算出することはできるのですが、そのデータの他のフィールドを抽出することは、SMALL関数ではできません。

垂直の表になっているので、VLOOKUP関数を使えば、抽出することができるのではと考えるかもしれませんが、残念ながら、今回のような表からでは、VLOOKUP関数で、別のフィールドのデータを抽出することはできません。

なぜならば、VLOOKUP関数は、抽出する範囲の一番左側が、検索値でなければなりません。

つまり、今回は、参加者・時間という並びになっていますので、VLOOKUP関数が使えないわけです。

時間・参加者ならばVLOOKUP関数が使えます。

このような表に対応するには、INDEX+MATCH関数をつかうことで、抽出することはできるのですが、なかなか面倒な関数の組み合わせです。

ところが、近年追加された「XLOOKUP関数」を使うと、とても簡単に問題を解決してデータを抽出することができます。

E2をクリックして、XLOOKUP関数をつかった数式を作っていきます。

なお、XLOOKUP関数ですが、XLOOKUP関数ダイアログボックスで数式をつくると、どのように引数を設定したらわかりにくいので、出来ることならば、手入力で数式をつくるほうがいいように思えます。

なので、今回は、手入力で、XLOOKUP関数を作っていきます。

最初は、検索値ですが、これは、D2の時間が該当しますので、「D2」と入力したいところですが、Office365のExcelやXLOOKUP関数が使えるようになったExcel2019には、「スピル機能」があるので、「D2:D4」と入力します。

検索範囲ですが、検索値が所属しているフィールド（列）ですから、B2:B11。

オートフィルで数式をコピーするのではなく、「スピル機能」によって数式がコピー（スピル）されますので、絶対参照は不要です。

戻り範囲ですが、これは、抽出したいものですから、参加者の列ですね。

A2:A11と入力します。

ここも、絶対参照は不要です。

見つからない場合は、空白にしたいので「””（ダブルコーテーション×2）」

一致モードは、完全一致なので「0」

検索モードは、データの上部から下部へと検索しますので「1」

Enterキーで数式を確定すると、スピル機能によって、数式が設定されます。

E2の数式は、

=XLOOKUP(D2:D4,B2:B11,A2:A11,"",0,1)

です。

このように、XLOOKUP関数は、VLOOKUP関数よりも、融通がききやすいので、使ってみると便利です。

なお、「スピル機能」がよくわからない場合は、旧来通り、絶対参照をつかった数式をつくり、オートフィルで数式をコピーしてもOKですよ。

その場合の数式は、

=XLOOKUP(D2,$B$2:$B$11,$A$2:$A$11,"",0,1)

です。

新しく加わった機能や関数を、取り入れてみると、改善できるものとかあるかもしれませんね。

Excel。XLOOKUP関数だと簡単！降順の表から検索するならINDEX+MATCH関数の苦労から解放?!【XLOOKUP】

Excel。XLOOKUP関数だと簡単！降順の表から検索するならINDEX+MATCH関数の苦労から解放?!

＜INDEX+MATCH関数・XLOOKUP関数＞

次のような合計値に応じてランクを決定する表があります。

数値をもとにして、ランクの表から検索するので、「VLOOKUP関数」を使うように思われますが、このケース。

VLOOKUP関数では、対応することができません。

C2:C10を一度クリアして、VLOOKUP関数で算出してみましょう。

C2には、
=VLOOKUP(B2,$F$2:$G$7,1,TRUE)
という数式になるはずです。

しかし、「#N/A」というエラーが表示されてしまいます。

検索値は、B2だし、範囲も問題ない。
抽出したいのは、ランクなので、列番号は「1」。
完全一致ではなく、近似値なので、「TRUE」と大丈夫なような気がしますが、VLOOKUP関数のルールを逸脱しています。

１つ目は、検索値の左側に抽出したいデータがある。
今回はG列の合計の左にランクがある。
抽出したいものが検索値の右側にないとVLOOKUP関数を使うことができません。

では、F列とG列を入れ替えればいいのかというと…

C2の数式も合わせて次のように修正します。

=VLOOKUP(B2,$F$2:$G$7,2,TRUE)
オートフィルを使って数式をコピーしたところ、エラーのセルがあります。

実は、まだVLOOKUP関数のルールから逸脱しています。

2つ目として、範囲のF2:G7の表ですが、今回のような数値の場合は、【昇順】でなければいけません。

商品コードや氏名の場合は、ルール上よろしくありませんが、完全一致のケースが多く、検索されます。

なので、次のようにF列を昇順にすると、問題なく検索してくれます。

しかしながら、通常数値の高い順でリストをつくるだろうし、Sが何点以上というように表を閲覧するはずです。

よって、最初の表では、VLOOKUP関数を使うことはできないわけです。

では、どのようにしたらいいのかというと、このケースでの定番「INDEX+MATCH関数」をつかうことで、検索することができます。

さらに、F1:G7の表だけではINDEX+MATCH関数でも、検索することはできません。

H列の「～まで」という数値を用意する必要があります。

B2に、INDEX+MATCH関数で数式を作ってみましょう。

=INDEX($F$2:$F$7,MATCH(B2,$H$2:$H$7,-1),1)

これで、検索することができるのですが、数式が非常に「難解」ですね。

数式の説明をしてきます。

MATCH関数がどのような動きをしているのか確認してみましょう。

D2の数式は、
=MATCH(B2,$I$2:$I$7,-1)
検索値は、B2で198を指します。

検索範囲は、$I$2:$I$7
照合は、「-1」とします。「-1」は、検索値以上の最小値を検索します。

すると、上から3コ目が該当するので、3と算出されました。

それを受けて、E2のINDEX関数を確認しましょう。
=INDEX($G$2:$G$7,D2)
G2:G7の範囲で、上から、D2、つまり、3番目を検索するので、「B」を検索してくれるわけです。

このように、検索する表をそのまま使用して、検索することは容易ではありませんでした。

しかし、Office365のExcel　 Insiderで搭載された、「スピル」そして、新しく登場した【XLOOKUP関数】をつかうと、INDEX+MATCH関数のような苦労はせずにすむようになりました。

D2にXLOOKUP関数の計算式をつくっていきます。

=XLOOKUP(B2:B10,G2:G7,F2:F7,,-1,1)
これで、あっという間に算出してくれます。

範囲の表が、どうのこうのというような条件はありません。

XLOOKUP関数は、スピル機能を使う関数なので、オートフィルを使って数式をコピーする必要はありません。

また、絶対参照で範囲の表を固定する必要もありません。

それでは、数式を説明しましょう。

まず、XLOOKUP関数は、
XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
という形式になっています。

検索値は、B2:B10。
合計点がどのランクなのか検索するので、B2:B10と設定します。

検索範囲は、G2:G7。検索値に該当するのを検索する範囲です。

戻り範囲は、F2:F7。検索結果の範囲です。

このように、検索範囲と戻り範囲を別々に設定できるようになったので、検索範囲を範囲の表の一列目にないといけないというルールから解放されたわけです。

見つからない場合は、今回はないので、省略。

一致モードは、完全一致かどうか決めるところです。
VLOOKUP関数では省略すると、「近似値」になってしまいましたが、XLOOKUP関数では、省略すると「完全一致」になります。

今回は、完全一致または、次に小さい項目の「-1」を設定します。

1件目は198なので、198の次に小さい項目は、190が該当しますので、Bを検索するわけです。

最後の、
検索モードは、リストの上から検索させるので、先頭から末尾へ検索の「1」を選択します。

この検索モードによって、VLOOKUP関数だと、範囲の表を昇順にしておかないといけないというルールから解放されました。

このように、XLOOKUP関数は、現場で大きなインパクトを与える関数になるのかもしれませんね。

Excel。Office Insiderで追加したXLOOKUP関数は思考の刷新を起こすかも！【XLOOKUP】

Excel。Office Insiderで追加したXLOOKUP関数は思考の刷新を起こすかも！

＜XLOOKUP関数＞

Office365のOffice Insiderで追加された新しい関数【XLOOKUP関数】。

今までのお馴染みのVLOOKUP関数やHLOOKUP関数をより、わかりやすく、より使いやすくしたといえるかもしれません。

いずれ、正式にExcelに搭載されるだろう、XLOOKUP関数を少し早いですが、現時点の状態でご紹介します。

次のような表があります。

2行目のデータは、氏名を検索値として、その該当するデータを抽出のに、VLOOKUP関数を使って算出しています。

B2のセルには、
=VLOOKUP($A2,$A$4:$F$16,2,FALSE)
という数式を設定してあります。

検索値が、$A2と複合参照にしているのは、オートフィルを使って数式をコピーしても大丈夫なようにしていています。

範囲には、$A$4:$F$16と絶対参照を忘れると、オートフィルを使って数式をコピーときに、範囲がズレてしまうので、忘れないようにしないといけませんね。

列番号には、「2」。これは、範囲の左から何列目のデータを抽出するのかを指示する数値ですね。

検索方法は、「FALSE」。完全一致ですね。省略しちゃうと、近似値になってしまうので、忘れずに設定する必要がありますね。

算出後は、オートフィルを使って数式をコピーしますが、列番号を修正しないといけませんね。
修正しないようにする場合には、COLUMN関数を使ったりして対応させる必要があります。


このようにVLOOKUP関数は、覚えると大したことはないのですが、慣れないと苦労する関数ですし、複数ある場合は修正するのも面倒な関数です。

ところが、【XLOOKUP関数】は、これらの考え方を大きく刷新しています。
『革命的』といってもいいすぎでないかもしれません。

では、【XLOOKUP関数】を使って算出してみましょう。

B2をクリックして、XLOOKUP関数を設定してきます。

検索値には、A2。これは、VLOOKUP関数と同じです。

検索範囲ですが、A5:A13。ここがちがいますよね。VLOOKUP関数では、範囲は、該当するリスト全体だったのですが、検索値がある列を選択します。

戻り配列ですが、抽出するデータを範囲選択しています。
絶対参照は？と思うかもしれませんが、不要です。

一致モードですが、「0」を入力していますが、完全一致ならば、「0」。
ちなみに省略可能です。
XLOOKUP関数は、近似値でなくて、完全一致が基本設定なので、
省略しても完全一致で検索してくれます。

少し説明をすると、
「0」は完全一致
「-1」は見つからないとき次に小さい値を検索します
「1」は見つからないとき次に大きい値を検索します
「2」はワイルドカードを使って検索したい
このように、4パターンにわかれました。

検索モードは、便利かもしれません。
ここも省略が可能ですが、通常は、「1」の先頭から末尾へで設定します。

検索モードも、補足すると、
「1」は先頭から末尾へ検索します。
「-1」は末尾から先頭へ検索します。
「2」はバイナリ検索(昇順で並べられているとき)
「-2」はバイナリ検索(降順で並べられているとき)
「+」と「-」の違いですが、複数あった場合、どっちのデータを抽出するの？ということです。

では、設定が終わったら、OKボタンをクリックしてみましょう。

なんと、オートフィルを使って数式をコピーしたわけでもないのに、F列まで算出されていますよね。
これは、『スピル』という、これまた新しい機能によって、簡単に算出されたわけです。

なお、B2の数式は、
=XLOOKUP(A2,A5:A13,B5:F13,0,1)

正式実装される日は、まだかもしれませんが、早めに知っておくほうがいい機能がありそうです。