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

ちょっぴり紹介したい技~信号機~

よく人間、見た目が重要だといいます

 これまで、あらゆる機会で数値関連のプレゼンに関わってきた経験から、数字も見た目が重要だと思っています

 それは、例えどんなにいい資料でも、見てもらえないと資料の中の数字は全く意味をなさないからです

 人間、誰でも数字を見るのは苦痛なものです

 数字を見てもらうには工夫が必要なんです!

 今回は、そんな苦い経験をもとにして、数字の大きさに応じ、表示が変わる信号機を作成しました

 信号機の作成の仕方自体はとても簡単です

ポイントは2つだけです

1.IF関数で信号機の色表示を作成する 

赤、黄、青、それぞれの色表示用のセルを作成し、値に応じて表示が変わるようにIF関数を仕込んでおきます

2.各セルをテキストボックスで信号機上に表示します

 テキストボックスの中に数式を入れても機能しません。数式バーで数式を入れます

 ちなみに下のGIFでは、ハイライトする月を選択し、ハイライトされた月の数字に応じて信号機の表示が変わるようになっています

 これらのハイライト関連の過去記事は↓です

 ・INDEX関数とMATCH関数の組み合わせ

 ・条件付き書式の意外な使い方

ぜひご参考にしてください!

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


にほんブログ村

関数をマスターする5~MATCH関数&INDEX関数1~

今回からINDEX関数とMATCH関数の説明に移ります

 この2つの関数の組み合わせを覚えると下のGIF画像のように、スクロールバーの動きに合わせて、別に用意した表からデータを抽出するような機能を作成することができます(ダッシュボード演習編はこちらから)

GIF2

 ダッシュボードを作成するに限らず、様々な場面で活用できますので、ぜひサンプルファイルも参照しながらしっかり覚えておきましょう

アイコン

MATCH関数&INDEX関数 10.79 KB 17 downloads

...

1.MATCH関数

このMATCH関数は、検索しようとする値、もしくは文字列がどの”位置”にあるのかを教えてくれる関数です

 下のGIF画像で言えば、地区が東京・神奈川・・と並んでいる中で検索する値(地区)が何番目の位置にあるのかを教えてくれます

GIF5

MATCH関数を使うのに必要な引数(入力するもの)は以下の3つです

①検索値、②検索範囲、③検索型式

上のGIF画像のMATCH関数には、下の画像のように①~③の引数が設定されています

16

①検索値

セルC5(東京)を指定してどの地区を検索するかを指定します

②検索範囲

セルC2:H2(東京・神奈川・・)の検索範囲を指定します

③検索型式

今回は検索値と完全一致、0を指定します

2.INDEX関数

”指定した範囲”にて”指定した位置”にある値、もしくはテキストを抽出します

GIF8

 上のGIF画像で言えば、MATCH関数で抽出された位置/1~3を基にして、各地区の温度をINDEX関数にて抽出しています

INDEX関数を使うのに必要な引数(入力するもの)は以下の2つです

①検索範囲、②検索位置

下の画像で言えば、検索範囲の左端から3番目にある温度を検索しています

17

①検索範囲

セルC3:H3(10℃・8℃・・)を指定

②検索位置

セルC6(3)を指定

1.2.のMATCH関数とINDEX関数ともに検索範囲は列方向で指定しましたが、行方向でも検索は可能です

今回は以上です

単なる説明だと分かりにくいかもしれませんので、ぜひ、サンプルファイルをダウンロードして実際に確かめてみてください

では次回はMATCH関数とINDEX関数を組み合わせてみましょう!

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

関数をマスターする6~MATCH関数&INDEX関数2~

今回は前回に引き続き、MATCH関数とINDEX関数に取り組みます

今回は、名前管理の機能を使ってより実践的な活用方法に取り組んでみましょう!

もし、分かりづらい部分があれば下記のサンプルファイルをダウンロードして自分で触って動かしてみましょう!

関数を理解するための一番の近道は”手を動かす”ことです

ところで、

読者の中には「MATCH関数とINDEX関数の組み合わせはVLOOKUP関数と似ている」と感じた方もいらっしゃると思います

MATCH関数とINDEX関数の組み合わせがどう”VLOOKUP関数”と違うかという点も合わせて解説していきます(VLOOKUP関数は行方向に検索しますが、列方向には検索できませんので、その点は省略させて頂きます)

では、

 下の画像にある表から、選手の名前に応じて学年から出場回数を抽出する関数を作成していきましょう!!

18

INDEX関数とMATCH関数は下の画像の黄色の印を付けた箇所に入力をします

1.まずは表の見出しに対応した名前をつけよう!

選手~出場回数までの各列に、エクセル画面上の名前ボックスにて、そのまま見出し/列名を名前につけましょう!

GIF9

各列に名前を付ける理由について、1つ解説します

もし、名前を付けない場合は、選手の学年を抽出する関数はどんな風になるかというと以下のようになります

=INDEX(C$2:C$11,MATCH(C$13,B$2:B$11,0))

一方、名前をつけると下記のようにセル範囲の部分が置き換わります

=INDEX(学年,MATCH($C$13,選手,0))

名前を付けたほうが一目見ただけで意味が分かりますよね

”Cの列は何だっけ?”などと表を参照する必要はありません

では、以下の画像の表の空欄”=INDEX(学年,MATCH($C$13,選手,0))”と記入して、赤字の選手名に対応して「選手の学年」を関数で抽出できるようにしましょう

20

2.関数を下まで(出場回数まで)コピーして、関数の名前を修正

関数をコピーした後、関数の名前を抽出対象にあわせて修正しましょう

21

22

もし、1.の段階で名前を付けていなかったら、セルの列や行位置などを修正する必要があった(もしくは1.の段階で絶対参照を設定しておく必要があった)ことを考えると、名前を付けることが省力化につながることが良く分かると思います

3.表に列や行を追加してみましょう!

VLOOKUP関数でも同じように名前をつけて表から抽出することができます

23

ここで、表に行データ/5行目を追加してみましょう!

24

VLOOKUP関数でも行追加に対応できています

今度は列を追加してみましょう!

25

するとVLOOKUP関数の列参照が狂ってしまいます・・・

なぜなら、VLOOKUP関数の列参照は整数で設定しているからです

対して、INDEX関数とMATCH関数は、あくまで参照する値に応じて検索が行えます

この点はMATCH関数とINDEX関数の組み合わせとVLOOKUP関数の大きな違いです

この”違い”を理解した上で、場面に応じてMATCH関数とINDEX関数の組み合わせてとVLOOKUP関数を使い分けていきましょう!

次回OFFSET関数について解説します


にほんブログ村

ExcellentなDashboard作成演習#3 ~ボダン活用 4回目~

前回はこちらから

アイコン

サンプル#3 477.55 KB 19 downloads

...

今、計算シートで番号(オプション値)が出力されるようになっていますでしょうか?

8

出力されることが確認できたら、今回はINDEX関数を使って”オプション値”をキーにして”DATAシート”から各数値(データ)を引き出せるようにしましょう

INDEX関数によりB列からF列、それぞれの範囲で”キー”を基にして上から指定の値(キーが指定する順番)を抽出できるようにします

9

例えばオプションボタンにより出力されたキーが2の時、B列で指定するなら、上から2番目の”RIZAP”が抽出されるようにします

10

こうして、計算シートのB列からF列までINDEX関数を入力してオプションボタンの選択とともに、選択される値を変更できるようにします

GIF5

それでは5回目にお会いしましょう
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ

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

【問題】

使用金額に応じて注文のランク付けをしたいが、VLOOKUP関数だけを使用し、ランク表に応じて自由自在に可変できるようにしておきたい
#1GIF
【正解】

ランクの列にVLOOKUP関数を参照方法を1にして入力しておく

VLOOKUP関数の参照方法には実は2つあります

キャプチャ#1_1

上記のようにエクセルのヘルプにも記述されています

参照方法を0でなく1にした場合には、検索値が一致するものでなく、検索値に一致するか、一致しないもので、検索値より小さく、且つ、用意された設定値の中で最大なものを検索してくれます

キャプチャ#1_2

例えば上記の表において5,000の場合はどうなるかというと、一致するものはありません。ですから、5,000より小さい値、0のランクDが該当します

この”1”を使う時のコツ、注意点をそれぞれ説明します

VLOOKUP関数の参照範囲はK:Lなどのように行番号を入れないようにしましょう。そうすれば参照表の行数が増えても漏れなく対応できます

後、上記の表でいえば、金額の列は昇順にしておきましょう。そうでないと正確に検索してくれません

サンプルで確認したい人は以下↓をダウンロードして下さい

ではまた#2でお会いしましょう

 

スマホのように魅せるエクセルダッシュボード

数字を見るのは苦痛です・・・

という方は多いと思います。

ましてや会議で虫メガネを使わないと読めないような小さい文字が詰まった資料を見せられるのはとにかく苦痛です

その点、このダッシュボードはスクロールバーで表示内容を表示のボリュームを一定に保ったまま、変えていていけるので便利です!

動画を見て頂くと分かると思いますが、スクロールさせるだけでなく表示内容(販売月)も変えていけます。

全く同じ通りにダッシュボードを作成して仕事で使用したら上司に怒られそうですが、大事なのは”直感的に数字を分かり易く示す”ということです

例えば、右上は表示されていないものも含め、最終的に表示されるのは何行あり、今現在表示されているのはどの辺なのかを”一目”で分かるようになっています

電話でも”用件は3つ”です、と最初に言われると時間を調整しながら会話をしやすくできます

後、重要なのはこれらが全てエクセルの基礎的な技術で完成できることです

たかがエクセル、されどエクセル、エクセルだけでも様々な工夫ができます

そんな工夫が詰まった”エクセルダッシュボード術”を今後も紹介、解説していきます