タグ別アーカイブ: XLOOKUP関数
XLOOKUP関数を使用した複数ドロップダウンリストでの検索
今回は、新関数の特徴を活かして「複数のドロップダウンリストと連動した検索作業」をサクッと行う方法を解説します
この検索方法が行えると、上のGIF画像にあるようなクロス表からの検索がグッと楽になります
「河合さんの数学の点数は？」「中島さんの国語の点数は？」
こんな検索もドロップダウンリストを2回操作するだけで行えます！
従来であれば、複数の種類の関数を組み合わせなければこの検索は行えませんでした
今回は、XLOOKUP関数を一度だけ組み合わせるだけで作成する方法を解説します
尚、XLOOKUP関数の基本的な使い方に関しては、過去の記事をご確認ください
今回解説に使用するデータ
今回、解説に使用するデータは、生徒毎の3科目の点数表です
この表から、下の図の黄色に印を付けた箇所からリストの選択を行うだけで上の表から該当の点数を検索できるようにします
今回のポイント
今回は新関数の特徴を最大限に生かします
新関数の特徴は、１つのセルへの入力で複数の値を抽出できる点です
XLOOKUP関数も新関数ですので、複数の値を抽出できます
例として今回のデータを使用して、XLOOKUP関数を「複数の値」を抽出するように２つ設定してみます
①列方向に抽出
下の図をご覧下さい
XLOOKUP関数の3番目の引数/戻り列を、1列だけでなく「C列～E列」指定しています
戻り列を複数指定すると、下のGIF画像のように検索値の「伊藤さん」の全ての点数を抽出します
②行方向に抽出
XLOOKUP関数のメリットの一つは、従来のVLOOKUP関数では検索できなかった方向にも検索が行えることです
①の列方向に抽出では、「伊藤さん」の点数を全ての科目について抽出しました
今回は、全ての生徒の数学の点数を抽出してみます
今度は、3番目の引数で「複数行」を指定します
すると下の図のように、全ての生徒の数学の点数を抽出します
XLOOKUP関数の組み合わせ
では、前述の①と②を組み合わせて、検索を行う関数を完成させます
列方向に抽出する①のXLOOKUP関数の3番目の引数に、②の行方向に抽出するXLOOKUP関数を組み合わせます
このように「行列」双方向のXLOOKUP関数を組み合わせるだけで、「 複数のドロップダウンリストと連動した検索作業 」を行う関数の入力が完成です
＜まとめ＞
今回は、行列双方向に抽出するXLOOKUP関数を組み合わせて「複数のドロップダウンリストと連動した検索作業」を行う関数を解説しました
今回解説した方法はXLOOKUP関数の２つの特徴、ⅰ）複数の値を出力、ⅱ）行列双方向で検索化、を組み合わせたものといえます
XLOOKUP関数にはまだまだ紹介していない魅力があります
また次回、XLOOKUP関数の魅力について解説します！
長文に最後までお付き合い頂き誠にありがとうございまいた
参考までに今回使用したファイルを添付します
#SPILLエラーについて~新関数に独自のエラー
こんにちは、Excellent仕事術のガッツ鶴岡です
これまで、１つのセルへの入力で複数セルに出力する新関数について、複数の記事を書いてきました
例えば、下のGIF画像はFILTER関数を使った検索付きのリストの例です
C列にFILTER関数を仕込んであり、E列で検索文字「鈴木」が設定されるとA列のリストデータから「鈴木」を含むリストデータを出力します
この新関数では独特のエラー「＃SPILL」が出力される時があります
このエラーの意味について、今日は少し解説したいと思います
見慣れないエラーですが、発生理由はシンプルです
この新関数は複数のセルに出力を行いますが、出力範囲に何らかの入力がある場合にこのエラーは発生します
上の画像で言えば、！の文字が新関数の出力をブロックしてるのです
このブロックしている文字を取り除けば、このエラーは解消されます
尚、ブロックしている文字のフォントが「白色」で人の目では見えない時にも同じエラーが発生するので注意が必要です
では、今回は以上です
最後までブログ記事を見て下さり、大変感謝しております！
新型関数のおすすめ記事～スピルを体験しよう～
新たなに加わった新型関数は使い勝手がとてもいいです
ピボットテーブルの集計データを無理に使い回さなくても、集計が簡単に行えます
１つのセルに関数を入れたら、値があふれでるスピル体験をしていない人はぜひ早めに体験してみましょう！
これまで五月雨式に記事を書きましたが、以下にまとめて紹介します
１．FILETER関数⇒特定の条件のデータを抽出する関数
１セルに１FILTER関数を入力すれば、それだけで複数のデータを抽出してくれます
FILTER関数を使用して、検索付き機能付きののリストを作成します
テーブル機能と連携して効率よくデータを抽出する方法を紹介しています
２．XLOOKUP関数⇒VLOOKUP関数の進化型関数
エクセル分析を行う人で、2軸でのランク付けを楽に行いたい人におすすめです
以下のGIFでは、ⅰ売上とⅱ前期比の２つの軸でランク付けを、自動で行っています
XLOOKUP関数をアニメ付きのエクセルを使用し、XLOOKUP関数が集計されるまでの複数の動きを体感できるようにします
３．SORT関数⇒データを並べ替えて出力する関数です
①SORT関数によるエクセルデータの並び替え~列の並び替えも可
エクセルではこれまで、列方向のフィルターや並び替えが弱い傾向はありました
列フィルターを「列の非表示」を通じて実現する方法はこれまでもありましたが、列方向の並び替え機能はあまり使い勝手はよくありませんでした
今回紹介するSORT関数は、そんなこれまでのエクセルの弱点を補ってくれる関数です
４．UNIQUE関数⇒重複を排除してリスト化する関数
UNIQUE関数を使用してリストを簡単に作成する裏技も紹介します
今回は以上です
ではスピル体験をぜひ楽しんでください！
XLOOKUP関数を体で覚える
エクセル関数は、複数の箇所が一瞬で同時に動くことで結果が出力されます
ですので、エクセル初心者にはマンツーマンツによる導入指導が必要だと考えます
誰かが実際にエクセル画面上で動かすのを見て、自分も手を動かしてみることでエクセル関数の仕組みが身についていきます
ところが、昨今のリモートワークの浸透によりこの手の学習機会が少なくなってきました
そこで考えたのが、ご自身のPCにダウンロードして使用する学習ドリルです
自分でリストの値を選択・変更していくことで、関数の仕組みを体感したうえで自分でも関数の入力を試せるようになっています
今回はXLOOKUP関数のドリルです！
このXLOOKUP関数は、VLOOKUP関数からの進化版です
VLOOKUP関数より格段に使いやすくなっていますので、ぜひこの回についてはエクセル上級者にも見て頂きたいと思います
１．XLOOKUP関数の解説
エクセルを操作する上で、複数の表を扱うことは避けられません
複数の表の間で「表の値」をやり取りするのがXLOOKUP関数です
①XLOOKUP関数の特徴
VLOOKUP関数と比べると著しい進歩があります
ⅰ）行検索だけでなく、列方向にも検索できます！
従来はVLOOKUP関数とは別に、列方向に検索値を検索するHLOOKUP関数がありましたが、XLOOKUP関数では、検索方向は行列のどちらにも対応しています
ⅱ）抽出する値（戻り値）は、検索値の右にある必要はありません
VLOOKUP関数では戻り値は検索値の右にある必要がありましたが、XLOOKUP関数では左右どちらにも対応しています（列方向の検索では上下）
ⅲ）検索値が表に無い場合の値を指定できる
VLOOKUP関数では検索値が無い場合には、他の関数と組み合わせて、出力する値を指定していました
XLOOKUP関数では、引数（指示）の４番目に指定することができます
②XLOOKUP関数の書き方
直接、セルに書き込むのであれば、”=XL”をセルに入力した段階で下のようなダイアログが出ます
ハイライトされている箇所をタブでクリックし、（）の中に４つ引数（指示）を入力すれば関数が完成します
入力する引数は以下の内容になります
＝XLOOKUP（A.検索値,B.検索値・範囲,C.戻り値範囲,D.検索値が見つからない場合に表示する値）
２．ドリルの操作
学習ドリルをダウンロードして開いてみてください
注意事項のシートについては、事前に確認をお願いします
リストが２つ（検索値と戻り値）とボタンが３つあります（ヒント表示、解答表示、解答非表示）があります
それぞれの操作の仕方を以下に解説します
①リスト表示の変更
ドリルはリストの表示を変更する度に、XLOOKUP関数に関連する箇所が動きます
リストの表示を変えながら、XLOOKUP関数の仕組みを体感して頂く意図になっています
②ヒント
ヒントボタンを押すと、ヒントが数秒の間は表示されます
③解答表示・非表示
解答は表示・非表示を選択できます
必要に応じて、「解答表示」「解答非表示」ボタンを使い分けてください
解答については、セル上でF2キーを押して解答の中味を確認してみながら、数式の書き方を実感してみてください
③フリーアドインの活用
上記のドリルの操作方法については動画の解説もみれます
エクセルシート上のQRコードをスマホで読み込んで頂くか、フリーアドインのWeb Video Playerをエクセルシートにダウンロードしてください
Web Video Playerでは、エクセルシート上で動画を見ることができます
＜まとめ＞
XLOOKUP関数は、関数の中でもSUM関数に次いで一番使う機会の多い関数です
前述の「XLOOKUP関数の３つの特徴」を体感できるように、ドリルを使いこなして頂けると光栄です！
とにかく自身の手を動かすのが「関数の上達」の秘訣です！
ちなみに、ドリルで使用しているバージョンは以下です
XLOOKUP関数は、エクセルのバージョンによっては動かないことがあるので注意が必要です
おすすめ記事_２０２０年１０月１０日
最新のエクセル技術を紹介した、おすすめ記事を紹介します
１．スピードメーターグラフテンプレート（散布図グラフ使用）
これまでも円グラフとドーナツグラフを組み合わせたスピードメーターグラフは紹介してきました
今回、紹介するのは針部分に散布図グラフを使用した、よりリアルなスピードメーターグラフのテンプレートです⇒記事
２．XLOOKUP関数を組み合わせて集計上手になる
マトリクス表に関する処理は手間がかかるものです
２つの軸に合うもの選択するのに、目で照合したりする必要がありました
例えば、ある部署の売上成績と前期比から、２つの条件に合うランクを選択する場合などです
ところが、XLOOKUP関数を組み合わせれば、２次元のランク付けを自動化できるのです⇒記事
３．Power Queryで複合キーをうまく活用する
集計上手になるコツのひとつが複合キーをうまく使いこなせることです
例えば、条件１と条件２がともにBの場合は〇を出力する、などのケースです
この場合は複合キーをもとにした別表を作成し、VLOOKUP関数で参照して〇を出力します
今回はPower Queryを使用してもっとスマートに行える方法を紹介します⇒記事
＜まとめ＞
エクセルが誕生して２０年、その間にエクセル機能は目覚ましい進歩を遂げました
そのエクセル機能・進化の果実はしっかり活用していきましょう！
XxXLookup関数で集計上手になる！
読者の皆さんがこれまで使用したエクセル関数の中で、一番使用頻度が高かったのはどの関数でしょうか？
多くの皆さんはVLOOKUP関数と答えるのではないでしょうか？
ところが、XLOOKUP関数の登場により、関数の使用シーンがこれから大きく変わっていきそうです
まだ使用できるエクセルバージョンは少ないかもしれませんが、それだけXLOOKUP関数の登場はインパクトがあります
特にXLOOKUP関数と他の関数を組み合わせることにより、データ集計の効率を格段に向上させることができそうです
今回は、XLOOKUP関数同士を組み合わせた例を２つ紹介します
どちらの例も、表を2次元で処理できるので、利用シーンが多そうです
①マトリクス表から２つの条件に合致するものを抽出する
条件を２つ（条件①部門ｘ条件➁四半期間）をリストで指定し、２つの条件に一致する値を抽出する
➁マトリクス表でランク付けをする
事業の売上額と成長率、在庫商品の在庫額と回転率、などなど二次元でランク付けすることはよくあります
ところが、これまでは単に関数を使用するだけでは、ランク付けは自動ではできませんでした
尚、これからの解説は既にXLOOKUP関数を使用したことがある人を主として想定しています
但し、そうでない方もサンプルファイルをダウンロードすることで、そのままサンプルファイルの使用例を他の目的に利用していくことも想定しています！
１．そもそもXLOOKUP関数とは？
２つの事例を紹介する前に少しXLOOKUP関数について簡単におさらいをしておきましょう
サンプルファイルでは1シート目の内容です
サンプルの内容は部門毎に対応する部門長を別表から抽出するものです
XLOOKUP関数も、VLOOKUP関数と同じように検索値を基にして表からデータを抽出します（事例では北海道を検索値として、部門長の木村を抽出）
但し、VLOOKUP関数で行っていたような
「○○から右に何番目」
といった列数の指定が無く、検索する範囲（サンプルの部門）、戻り範囲（部門長）を指定しするだけです
つまり、 「○○から右に何番目」 の縛りがなく、検索値が参照表の左に無くてもいいし、列方向でなく行方向でもデータを抽出できます
引数は以下のような形で指定します
＝XLOOKUP（検索値、検索値範囲、戻り範囲、【省略可】)
２．マトリクス表から２つの条件に合致するものを抽出する
XLOOUP関数の3番目の引数、戻り範囲にXLOOKUP関数を入れるのがポイントになります
仮に戻り範囲にXLOOKUP関数を入れなければ、いわゆるスピる（値があふれ出す）状態になります
以下が検索の軸を部門にし、戻り範囲を表の全範囲にした場合です
列方向でスピります
・=XLOOKUP(I3,B3:B9,C3:F9)＊3番目の引数を全範囲
以下が期間を軸に、戻り範囲を表の全範囲にした場合です
行方向でスピります
・=XLOOKUP(I4,C2:F2,C3:F9)＊3番目の引数を全範囲
この２つのスピりの交点が求まれば正解の値です
ですので、以下のように3段階でXLOOKUP関数を組み合わせます
A.部門の検索を行うXLOOKUP関数を作成
B.但し、3番目の引数に期間を検索するXLOOKUP関数を入れる
C.3番目の引数になるXLOOKUP関数は”期間の選択”に備えて戻り範囲＝全範囲
最終的にはこのようになります
=XLOOKUP(I3,B3:B9,XLOOKUP(I4,C2:F2,C3:F9))
数式の内容はサンプルファイルにて、実際に入力されている内容を確認してくみてください
３．マトリクス表でランク付けをする
２．で行ったXLOOKUPによる関数では、4番目以降の引数を省略していました
ここで、5番目の引数を登場させます
VLOOKUP関数でも4番目の引数で検索方法を指定しています
FALSE=完全一致
TRUE≒完全一致
XLOOKUP関数では以下のように検索方法を指定できます
0⇒完全一致
-1⇒検索値の次に小さい値
1⇒検索値の次に大きい値
今回使用するの-1、検索値の次に小さい値です
以下の表で言えば、もし売上が30なら30より小さい値の０が該当し、前期比が15％であれば、10%が該当します
つまり、２．で行ったXLOOKUP関数の組み合わせに、検索方法-1を組み合わせれば2次元でのランク付けは、簡単に自動化できるのです
式は以下のようになります
=XLOOKUP(C5,$H$4:$H$6,XLOOKUP(D5,$I$3:$K$3,$I$4:$K$6,,-1),,-1)
企業ではABC分析の延長で、２つの変数（例：売上、利益率）で事業や商品を評価する機会が多いので、このXLOOKUP関数の組み合わせを行う機会は多いと思います
＜まとめ＞
今回は大きく分けて２つの事を解説しました
ⅰ）XLOOKUP関数を行列、双方向で組み合わせる（スピるの組み合わせ）
ⅱ）XLOOKUP関数の検索方法を工夫し、自動でランク付けをする
このⅰ）ⅱ）ができれば、データ集計がとても楽に行えます
ぜひ、有効活用していきましょう！
このブログではFILTER関数など、XLOOKUP関数と同じ新たなタイプの関数の解説も行っていますので、そちらもぜひ参照してみてください
尚、XLOOKUP関数が使えるエクセルバージョン（2020年9月19日時点）は限られており、OFFICE365ユーザー向けとなっています
2019バージョンユーザーなどは使用できないので、その点はまだ不便ではあります
