タグ別アーカイブ: VLOOKUP関数

分析からダッシュボード作成を体験する講座

【実践的エクセル力向上】エクセル便利機能をうまく活用する力➡エクセル力をあげてワンランクアップ上の仕事ができるようになりましょう!

お申込み

  *ストリートアカデミーのページに遷移します

エクセルの進化は凄まじいものがあります

驚くほど便利な機能が備わっています

重要なのはそれらの便利機能を「知る」かどうかです

「便利機能を知り、うまく便利機能を組み合わせて実際の仕事を効率化する」、それがエクセル力です

 今回のレッスンでは目から鱗の便利機能に沢山触れて頂き、エクセル力を上げる勘所を掴んで頂きます

エクセル力事例

エクセル力の2つの例を紹介します

1つ目はABC分析のランク分けです

従来のABC分析の場合には、ランク分けは次の3つの手順で行っていました

①データを降順で並べ替え➡

②ランク分けの基準作成➡

③IF関数で基準毎にランク分け

 この方法の場合は「ランク分けの基準を変更したり」「データを追加」した場合には①~③をまた繰り返さねばなりません

 今回のレッスンでは「VLOOKUP関数の隠れた便利機能との組み合わせ」により、この面倒なランク分けを自動化します!

 上のGIF画像では、画像上の表にて、ランク別に上位の比率を変更すると、元データのランクが自動的に変更されていきます!

2つ目は双方向グラフです

 このグラフデザインだとABCのランク別に、販売個数と商品数の比較が行い易くなります

一見、難しそうですが、グラフの標準機能を組み合わせるだけで行えます

実は、グラフの軸は反転できるのです!

この「軸の反転」機能を中心に双方向グラフの作成を体験して頂きます

 最終的には、今紹介した2つの事例等を組み合わせて、下の画像の様なダッシュボードの作成を体験して頂きます(全てのコンテンツを一から作成するわけではないので、その点は予めご了承お願いします)

 ダッシュボードの作成では、「スライサー/選択」をはじめとするピボットテーブルの有効活用方法についても解説させて頂きます

レッスンの特徴

個別にマンツーマンでレッスンを行います

 事前に個別に課題や要望等をすり合わせさせて頂き、レッスンプログラムについては調整を行わせて頂きます

 尚、レッスンは体験という位置づけであり、ダッシュボードのコンテンツを全て一から作成・演習するわけではない旨を予めご了承お願いします

では、レッスンでお会いしましょう!

 このレッスンを通じて飛躍的にエクセル力を向上させる機会になるようにしましょう!

お申込み

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ


にほんブログ村

クエリのマージ/項目追加~中級編2回目~

中級編2回目は前回紹介したマージの技術を、実際にサンプルを使用して解説を行います

下の図にある商品台帳売上台帳が解説に使用するデータです

こちらの表2つを組み合わせる(マージする)ことにより、2つの表に分かれていた項目を組み合わせて活用できるようにします

下の図のように、売上台帳に商品台帳の「商品名」と「販売単価」を追加します

今回のポイント

A.キー列を設定します

2つの表を組み合わせるためのキー列を指定します

B.組みあせる項目、組み合わせ方を選択します

下記の画面で組み合わせる項目と組み合わせ方を指定します

上の図に”集計”とありますが、集計については次回に解説します

では早速、下記にサンプルファイルをダウンロードして演習をすすめていきましょう!

1.クエリ作成

商品台帳と売上台帳ともに、次の①~③の処理を行い、クエリを作成しておきます

①Power Query Editorを開く

データタブから”テーブルまたは範囲から”を指定して、Power Query Editorを開きます

範囲を指定する際、自動的に題名の部分(下の図であれば商品台帳)がテーブルの見出しとして範囲に入ってしまうケースがあります

こちらについては、題名部分を除いて範囲を指定し直してください

②Power Query Editor上の処理

Power Query Editorの画面右にて、クエリに名前をつけましょう!

マージ処理をする時にクエリー名が分かり易くつけてあると、処理がとても進めやすいです

③読込処理

ホームタブの右上の「閉じて読み込む」ボタンの右に▼マークがあるので、こちらをクリックしてください

すると、次の画面のように「閉じて次に読込む」ボタンが出てきますので、こちらをクリックします

そうすればデータのインポート画面が開くので、読込先を指定できるようになります

下の図のようにデータのインポート画面が開いたら、読込先は”接続の作成のみ”を指定してください

ここまで行えば、クエリが画面右に出来ています!

上の①~③の処理を通じて2つのクエリを作成したら、2.のマージ作業に移ります

2.マージ作業

2つのクエリをマージする場合、事前に2つの方法が選択できます

ⅰ)既存のクエリにマージする方法

ⅱ)新規にマージ用のクエリを作成する方法

今回はⅱ)で行います

➀売上台帳クエリを参照

新規にクエリを作成するといっても、全く一からクエリを作成するわけではありません

売上台帳クエリを基にして新規のクエリを作成します

まずは、売上台帳クエリ上にカーソルを置いて右クリックし、更に”参照”をクリックします

「参照」をクリックしたら、Power Query Editorが開きます!

➁クエリのマージをクリック

新しいクエリのクエリ名は、次の画面のように変更しておきましょう!

③クエリのマージ

次に画面上にある、「クエリのマージ」ボタンをクリックします

④マージするテーブルを指定

マージをクリックした後に、新規に次のGIFにあるように「マージ」方法を指定する画面が立ち上がります

この画面では マージするテーブルとキー列を次のように指定します

テーブル⇒商品台帳、キー列⇒商品コード

⑤マージする項目、方法を指定

④が終了すると下記の画像のように、商品台帳の内容が一番右の列に追加されます

次に、商品台帳の右横にあるマーク(2つの矢印の組み合わせたマーク)をクリックし、実際に組み合わせる項目と組み合わせ方法を指定します

上のGIFのように、マークをクリックした段階では全てにチェックが入っています

ここでは商品名と販売単価はチェックを入れたままにしましょう!

「元の列名をプレフィックスとして使用します」についてはチェックを外しましょう

チェックを入れたままだと下の図のように、余計な名前(商品台帳)が商品名と販売単価の左横に表示されるようになってしまいます

⑥読込処理

今回は”閉じて読み込む”を指定し、接続の作成のみでなく、実際にシートに読み込みます

これでマージ処理は完成です!

<まとめ>

はじめてのマージ処理はいかがだっだでしょうか?

2つの表を組み合わせるにあたり、VLOOKUP関数を2つの列 (商品名、販売単価 で作成する)よりずっと簡単だったのではないでしょうか?

もし、”しまった、他の項目も追加が必要だった”ということになっても、関数を追加したりしなくても、簡単に追加が行えます

具体的な方法については次回、詳細に解説します

次回は単に項目の組み合わせだけでなく、計算処理も行います!

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村
</a

クエリのマージ/計算処理~中級編3回目~

こんにちは、Excellent仕事術ガッツ鶴岡です

前回は2つのクエリーをマージして、項目が追加されたクエリーを作成しました

今回は、追加した項目を更に深堀活用し、本来であれば関数やVBAの使用が必要な処理をサクッと行う方法を習得しましょう!

解説は前回と同じサンプルファイルを使用して行います

今回使用するデータ

今回の解説では、商品台帳と売上台帳の2つの表から、下の表を作成します

前回、売上台帳に商品台帳の「商品名」と「販売単価」項目を追加しましたが、今回は、更に「販売個数の合計」と「売上金額」も算出した表にします

<今回のポイント>

A.条件付き集計

今回の集計処理では、同じ商品コードに紐付く「複数の値」を集計します

エクセル関数でいえば、SUMIFS関数と同じ処理です

しかも、SUMIFS関数よりももっとシンプルな方法で集計します

前回、クエリの項目を追加する時に使用した画面と同じ画面にて、単純にマージ方法として「集計」を選ぶだけです!

B.一括計算

1.で商品コード毎に販売個数を集計したら、販売単価を乗じます

乗じる処理は、乗じる列を選択して計算方法を指定するだけです

2つのポイントを確認したところで、早速、詳細な解説に入りましょう!

1.条件付き集計

前回と同じ様に、クエリーを参照して新たなクエリーを作成するところから処理を開始します

➀参照するクエリーの指定

今回は商品台帳を参照元にします

商品台帳クエリー上で右クリックし、下の画像の様に「参照」が選択できるようになったら、参照のテキストをクリックしましょう!

➁クエリのマージ

Power Query Editorが開いたら、ホームタブの上にある「クエリのマージ」をクリックしましょう!

下の画像のようなマージ画面が開いたら、前回と同じく商品コードをキーにして売上台帳クエリをマージしましょう

③条件付き集計処理

事前に新たに作成したクエリの名前は変えておきましょう

クエリの名前を変更したら、マージした売上台帳の項目を、下の画像の黄色の箇所をクリックしましょう!

次に開いたマージ方法を詳細に設定する画面では、前回と違い「展開」ではなく「集計」をチェックしましょう

その際に”元の列名を・・・”はチェックを外しておきましょう

そして、本来であれば上の画像の一番下「Σ販売個数の合計」をチェックして指定してください、というべきところです

試しに、「Σ販売個数の合計」の一つ上、「Σ商品コードの合計」をチェックして指定してみてください

すると次の画像のように表示されます

もちろん、商品コードを合計しても意味はありません

もし、指定を間違えてしまった時の対処は、Power Queryであればどうするのか?ということを解説するため、敢えて本来とは違うものを指定してもらいました

間違いを回復するには、下の画像の「適用したステップ欄」の黄色の箇所をクリックしてください

すると前回行ったチェック指定がやり直せるようになります

Power Queryでは、上の画像のように「やり直し」を行う場合には「適用したステップ欄」を使用します

詳細は後日、解説を行います

それでは「やり直し」を行い、下の画像のように本来の”販売個数の合計”が表示されたところで2.一括計算の解説に移ります

2.一括計算

計算したいのはⅰ)販売単価とⅱ)販売個数の合計を乗じた売上金額です

➀計算対象の列、ⅰ)ⅱ)を選択する

Ctrlキーを押しながら、販売単価の列と販売個数の合計の列を選択しましょう

➁画面上のタブから「列の追加」を選択

画面上のタブから列の追加をクリックすると、右側に計算機のようなマークがついた「標準」が見えます

次に「標準」マーク下の▼をクリックしましょう!

③計算方法の指定

マークをクリックした後に表示された「各種計算方法」から乗算を選択してクリックしましょう!

すると上のGIFのように、一番右に新たな列が追加されます

この新たな列には、計算結果が出力されています

列の名称は”乗算”から”売上金額”に変更しておきましょう

④読込処理

列の追加タブからホームタブにもどり、エクセルシートへの読込処理を、下の画像の「閉じて読み込む」ボタンを通じて行いましょう

販売個数と売上金額が出力されているのを確認したら処理は終了です

<まとめ>

 今回は商品台帳と売上台帳をマージして、商品コード毎に「a:販売個数」を集計処理しました

 そして、集計した「a:販売個数」と「b:販売単価」とをPower Query Editor上で更に乗じて「c:売上金額」まで計算した表を作成しました!

 エクセル関数のVLOOKUP関数とSUMIF関数を使用しても同じことが行えますが、もっと楽に表を作成できているのではないでしょうか?

関数と違って、カーソルで対象を指定しながら直観的に行えるのもとてもうれしいです

 それに、もし、表の作成の仕方を間違えたとしても、間違いを起こした場所からやり直せます

さて、計算の元になる表の値が変わった場合にはどうすればいいのでしょうか?

関数で表を作成していれば、自動計算されています

 Power Queryの場合には、関数を使用した場合と違って、1つ余分な処理を行う必要があります

 この場合は下の画像の中にある「すべて更新」をクリックします

そうすれば、表の中で再計算が行われます

今回の解説は以上です

次回は、今回と同じように複数のクエリを組み合わせて扱う方法を解説します

但し、今回のように商品台帳と売上台帳といった、違う内容のクエリを組み合わせるのではありません

同じ項目から構成される複数のクエリを追加して、組み合わせる方法の解説になります

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

XLOOKUP関数を体で覚える

エクセル関数は、複数の箇所が一瞬で同時に動くことで結果が出力されます

ですので、エクセル初心者にはマンツーマンツによる導入指導が必要だと考えます

誰かが実際にエクセル画面上で動かすのを見て、自分も手を動かしてみることでエクセル関数の仕組みが身についていきます

ところが、昨今のリモートワークの浸透によりこの手の学習機会が少なくなってきました

そこで考えたのが、ご自身のPCにダウンロードして使用する学習ドリルです

自分でリストの値を選択・変更していくことで、関数の仕組みを体感したうえで自分でも関数の入力を試せるようになっています

今回はXLOOKUP関数のドリルです!

このXLOOKUP関数は、VLOOKUP関数からの進化版です

VLOOKUP関数より格段に使いやすくなっていますので、ぜひこの回についてはエクセル上級者にも見て頂きたいと思います

操作
XLOOKUP関数ドリル

1.XLOOKUP関数の解説

エクセルを操作する上で、複数の表を扱うことは避けられません

複数の表の間で「表の値」をやり取りするのがXLOOKUP関数です

①XLOOKUP関数の特徴

VLOOKUP関数と比べると著しい進歩があります

ⅰ)行検索だけでなく、列方向にも検索できます!

 従来はVLOOKUP関数とは別に、列方向に検索値を検索するHLOOKUP関数がありましたが、XLOOKUP関数では、検索方向は行列のどちらにも対応しています

行方向

ⅱ)抽出する値(戻り値)は、検索値の右にある必要はありません

 VLOOKUP関数では戻り値は検索値の右にある必要がありましたが、XLOOKUP関数では左右どちらにも対応しています(列方向の検索では上下)

ⅲ)検索値が表に無い場合の値を指定できる

 VLOOKUP関数では検索値が無い場合には、他の関数と組み合わせて、出力する値を指定していました

 XLOOKUP関数では、引数(指示)の4番目に指定することができます

②XLOOKUP関数の書き方

直接、セルに書き込むのであれば、”=XL”をセルに入力した段階で下のようなダイアログが出ます

ハイライトされている箇所をタブでクリックし、()の中に4つ引数(指示)を入力すれば関数が完成します

入力する引数は以下の内容になります

=XLOOKUP(A.検索値,B.検索値・範囲,C.戻り値範囲,D.検索値が見つからない場合に表示する値)

2.ドリルの操作

学習ドリルをダウンロードして開いてみてください

アイコン

XLOOKUP関数_ドリル 852.67 KB 22 downloads

...

注意事項のシートについては、事前に確認をお願いします

リストが2つ(検索値と戻り値)とボタンが3つあります(ヒント表示、解答表示、解答非表示)があります

それぞれの操作の仕方を以下に解説します

①リスト表示の変更

ドリルはリストの表示を変更する度に、XLOOKUP関数に関連する箇所が動きます

リストの表示を変えながら、XLOOKUP関数の仕組みを体感して頂く意図になっています

リスト表示

②ヒント

ヒントボタンを押すと、ヒントが数秒の間は表示されます

③解答表示・非表示

解答は表示・非表示を選択できます

必要に応じて、「解答表示」「解答非表示」ボタンを使い分けてください

解答については、セル上でF2キーを押して解答の中味を確認してみながら、数式の書き方を実感してみてください

③フリーアドインの活用

上記のドリルの操作方法については動画の解説もみれます

エクセルシート上のQRコードをスマホで読み込んで頂くか、フリーアドインのWeb Video Playerをエクセルシートにダウンロードしてください

Web Video Playerでは、エクセルシート上で動画を見ることができます

<まとめ>

XLOOKUP関数は、関数の中でもSUM関数に次いで一番使う機会の多い関数です

前述の「XLOOKUP関数の3つの特徴」を体感できるように、ドリルを使いこなして頂けると光栄です!

とにかく自身の手を動かすのが「関数の上達」の秘訣です!

ちなみに、ドリルで使用しているバージョンは以下です

XLOOKUP関数は、エクセルのバージョンによっては動かないことがあるので注意が必要です

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

XxXLookup関数で集計上手になる!

読者の皆さんがこれまで使用したエクセル関数の中で、一番使用頻度が高かったのはどの関数でしょうか?

多くの皆さんはVLOOKUP関数と答えるのではないでしょうか?

ところが、XLOOKUP関数の登場により、関数の使用シーンがこれから大きく変わっていきそうです

まだ使用できるエクセルバージョンは少ないかもしれませんが、それだけXLOOKUP関数の登場はインパクトがあります

特にXLOOKUP関数と他の関数を組み合わせることにより、データ集計の効率を格段に向上させることができそうです

今回は、XLOOKUP関数同士を組み合わせた例を2つ紹介します

どちらの例も、表を2次元で処理できるので、利用シーンが多そうです

①マトリクス表から2つの条件に合致するものを抽出する

条件を2つ(条件①部門x条件➁四半期間)をリストで指定し、2つの条件に一致する値を抽出する

➁マトリクス表でランク付けをする

事業の売上額と成長率、在庫商品の在庫額と回転率、などなど二次元でランク付けすることはよくあります

ところが、これまでは単に関数を使用するだけでは、ランク付けは自動ではできませんでした

尚、これからの解説は既にXLOOKUP関数を使用したことがある人を主として想定しています

但し、そうでない方もサンプルファイルをダウンロードすることで、そのままサンプルファイルの使用例を他の目的に利用していくことも想定しています!

1.そもそもXLOOKUP関数とは?

2つの事例を紹介する前に少しXLOOKUP関数について簡単におさらいをしておきましょう

サンプルファイルでは1シート目の内容です

サンプルの内容は部門毎に対応する部門長を別表から抽出するものです

XLOOKUP関数も、VLOOKUP関数と同じように検索値を基にして表からデータを抽出します(事例では北海道を検索値として、部門長の木村を抽出)

但し、VLOOKUP関数で行っていたような

「○○から右に何番目」

といった列数の指定が無く、検索する範囲(サンプルの部門)、戻り範囲(部門長)を指定しするだけです

つまり、 「○○から右に何番目」 の縛りがなく、検索値が参照表の左に無くてもいいし、列方向でなく行方向でもデータを抽出できます

引数は以下のような形で指定します

=XLOOKUP(検索値、検索値範囲、戻り範囲、【省略可】)

2.マトリクス表から2つの条件に合致するものを抽出する
XLOOUP関数の3番目の引数、戻り範囲にXLOOKUP関数を入れるのがポイントになります

仮に戻り範囲にXLOOKUP関数を入れなければ、いわゆるスピる(値があふれ出す)状態になります

以下が検索の軸を部門にし、戻り範囲を表の全範囲にした場合です

列方向でスピります

・=XLOOKUP(I3,B3:B9,C3:F9)*3番目の引数を全範囲

以下が期間を軸に、戻り範囲を表の全範囲にした場合です

行方向でスピります

・=XLOOKUP(I4,C2:F2,C3:F9)*3番目の引数を全範囲

この2つのスピりの交点が求まれば正解の値です

ですので、以下のように3段階でXLOOKUP関数を組み合わせます

A.部門の検索を行うXLOOKUP関数を作成

B.但し、3番目の引数に期間を検索するXLOOKUP関数を入れる

C.3番目の引数になるXLOOKUP関数は”期間の選択”に備えて戻り範囲=全範囲

最終的にはこのようになります

=XLOOKUP(I3,B3:B9,XLOOKUP(I4,C2:F2,C3:F9))

数式の内容はサンプルファイルにて、実際に入力されている内容を確認してくみてください

3.マトリクス表でランク付けをする

2.で行ったXLOOKUPによる関数では、4番目以降の引数を省略していました

ここで、5番目の引数を登場させます

VLOOKUP関数でも4番目の引数で検索方法を指定しています

FALSE=完全一致

TRUE≒完全一致

XLOOKUP関数では以下のように検索方法を指定できます

0⇒完全一致

-1⇒検索値の次に小さい値

1⇒検索値の次に大きい値

今回使用するの-1、検索値の次に小さい値です

以下の表で言えば、もし売上が30なら30より小さい値の0が該当し、前期比が15%であれば、10%が該当します

つまり、2.で行ったXLOOKUP関数の組み合わせに、検索方法-1を組み合わせれば2次元でのランク付けは、簡単に自動化できるのです

式は以下のようになります

=XLOOKUP(C5,$H$4:$H$6,XLOOKUP(D5,$I$3:$K$3,$I$4:$K$6,,-1),,-1)

企業ではABC分析の延長で、2つの変数(例:売上、利益率)で事業や商品を評価する機会が多いので、このXLOOKUP関数の組み合わせを行う機会は多いと思います

<まとめ>

今回は大きく分けて2つの事を解説しました

ⅰ)XLOOKUP関数を行列、双方向で組み合わせる(スピるの組み合わせ)

ⅱ)XLOOKUP関数の検索方法を工夫し、自動でランク付けをする

このⅰ)ⅱ)ができれば、データ集計がとても楽に行えます

ぜひ、有効活用していきましょう!

このブログではFILTER関数など、XLOOKUP関数と同じ新たなタイプの関数の解説も行っていますので、そちらもぜひ参照してみてください

尚、XLOOKUP関数が使えるエクセルバージョン(2020年9月19日時点)は限られており、OFFICE365ユーザー向けとなっています

2019バージョンユーザーなどは使用できないので、その点はまだ不便ではあります

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

最速集計の為のABC分析テンプレート

ABC分析で問題となるのはランク分けの手間です。IF関数主体でランク分けをするのでなく、VLOOKUP関数とランク表の組み合わせにより、最速でランク分けを実現します

特徴

・データ並べ替えの手間を省きました

・質(上位)と量のバランスを意識して設定できます(従来は集計してみないと質の状況がよくわからなかった)

・ランク分けの数を3から2、もしくは3から4などに手軽に変更できる

仕組み

・Dataシート(後でVLOOKUP関数について解説)とABC設定シート(後でPERCENTILE.INC関数について解説)の2シートで構成している

・”PERCENTILE.INC関数”を使用して引数1(ランク設定対象の数字が入力されている範囲)、引数2(下の画像であれば1-5%)ランク用販売個数を設定する(下の画像ではAランク:4729個以上、Bランク:280個以上、Cランク:7個以上、Dランク:7個未満)

・VLOOKUP関数(参照方法・近似値)にてPERCENTILE.INC関数にて設定した値を参照してランクを抽出する

アイコン

ABC分析テンプレート 109.00 KB 13 downloads

...

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

関数をマスターする4~VLOOKUP関数~

今回はVLOOKUP関数について解説します

VLOOKUP関数を活用する上で重要なのは、4つの引数の役割をしっかり理解することです

 *今回は最後の引数4については詳細な解説は割愛します

=VLOOKUP(引数1/参照値, 引数2/参照範囲, 引数3/参照列, 引数4/参照方法)

本題に入る前に下のGIF画像にて、基本的なVLOOKUP関数の動きを確認しましょう!

B4セルからB8セルにVLOOKUP関数が入力されいます

そして、VLOOKUP関数は各生徒の5教科の点数を、生徒別に下の表から参照して表示しています

GIF4

では、上記のGIF画像内でVLOOKUP関数が行っている内容を、もう少し詳細に解説します

11

①縦への検索

参照範囲(A12:F16)の一番左の列を縦に検索して参照値(太田)を見つけます

②横への検索

見つけた太田さんのセルから4列、右に動き太田さんの国語の点数を見つけます

この①②の内容を念頭に入れた上で、以下の各引数の解説をご覧ください

1.引数1/参照値

ここには、後述する参照範囲(表)の一番左の列に存在しうる値が入るように指定しましょう

前述の例ではB1のセルになります

13

2.引数2/参照範囲

この参照範囲は以下のように2つに分けることができます

参照値が入る範囲 + ②参照値に対応する値が水平に入る範囲

14

3.引数3/参照列

参照範囲にて、①参照値が入っている列から右に移動した数になります

14

4.引数4/参照方法

ここでは0もしくはFalseで指定するものと割り切って下さい

 以上でVLOOKUP関数の解説を終わりますが、もしVLOOKUP関数に苦手意識がある方は、手をうごかして直感的に引数の役割を感じ取ってみてください

 VLOOKUP関数は記事の冒頭で記述したように、関数の代表であり、ダッシュボード内でも使用機会が多いです

早めに習得しておきましょう!

次回はINDEX関数とMATCH関数を解説します

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

VLOOKUP関数を場合分けして使う

VLOOKUP関数を場合分けして使えたら、便利だと思ったことはありませんか?

4

例えば、レディースとメンズで価格が違う場合などは、都度、参照範囲を使い分けできたらとても便利です

2

アイコン

演習用 11.05 KB 4 downloads

...

アイコン

サンプル 11.05 KB 5 downloads

...

 

INDIRECT関数を使えば、”場合分け”を実現できます

条件は、以下のように、予め2つの範囲に名前をつけておくことです

3

では実際にVLOOKUP関数の場合分けをしましょう

1.各範囲に名前を付ける

該当する範囲を選択して、名前ボックスに”名前”入力しましょう

5

2.VLOOKUP関数の中にINDIRECT関数を組み入れる

セルG3(メンズかレディースが選択される)を参照することで、一致する名前の範囲を紐づけるようにします

6

以下のようになりましたでしょうか?

GIF1

INDIRECT関数は関数の中でも分かりずらい部類に入ると思いますが、慣れればとても便利です。まずは使う機会を増やしていきましょう!

ExcellentなDashboard作成演習#2 ~カラフルメーター編 1回目~

今回は下のエクセルダッシュボードを作成しましょう!

GIF1.gif

 

数字は見た目です

 

一目で”定量的”にも”定性的”にも数字の意味合いを示せるようなダッシュボードを作成できるようにしましょう!

 

1.今回のポイントは以下の3つです

 

①折れ線グラフを応用して”バー”が動くグラフを作成する

②特殊なフォントを活用する

③VLOOKUP関数を活用する

 

2.詳細なポイント

①折れ線グラフを応用して”バー”が動くグラフを作成する

-積み上げ棒グラフに折れ線グラフを追加し、事前に作成しておいた”バー”を折れ線グラフのマーカーにコピーします

 

②特殊なフォントを活用する

-Wingdingsフォントはイラストをフォントとして活用できるフォントです。覚えておくと”メリハリ”を付けるのに便利です

 

③VLOOKUP関数を活用する

-VLOOKUP関数の検索方法は完全一致だけではありません。近似一致でも検索できます。

点数に応じてランク付けする時など、とても便利です。この方法を知らなかったという方はぜひこの機にマスターしてください

以下の動画もぜひ参照してください

 

 

VLOOKUP関数とIF関数だけを使い倒す#4-1

【問題】

昨日の続きです。IF関数とIFERROR関数を組み合わせて次の条件で注文を3つに分類してください

1.会員住所=東京 且つ 注文商品=日本酒

2.会員住所=神奈川 且つ 注文商品=ウィスキー

3.その他、上記以外

キャプチャ#4_3

【回答】

複合キーを作るところまでは前回と一緒です

キャプチャ#4_1

そこからVLOOKUP関数の参照用に複合キーを検索値とした番号(1,2)の対応表を作成します

キャプチャ#4_2

既にお気づきの方もいらっしゃると思いますが、表にない複合キーの時にはエラーになってしまいます。

そこで”エラー”が出る点をうまく利用します

IFERROR関数ならば=IFERROR(関数、左記関数がエラーの場合の値)と指定できるので、エラーの場合は”3”と出力するようにします

IFERROR関数はとても便利なので有効活用しましょう!