タグ別アーカイブ: 関数

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

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

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

GIF2

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

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へ

関数をマスターする7~OFFSET関数~

今回、次回OFFSET関数に取り組みます

OFFSET関数はある指定範囲を起点とし、指定した行数もしくは列数を移動して参照する関数です

この関数は単体だと地味な機能しかありませんが、他の関数と組み合わせると高度な事ができるようになります

次回ではOFFSET関数とSUM関数、そしてMATCH関数と組み合わせて以下のようなダッシュボードを作るところまで解説します

このダッシュボードでは、ラウンドの指定に応じて採点表から指定のラウンドまでの採点・合計を表示しています

今回はOFFSET関数の単体での機能を確認しておきましょう!

引数

関数を作成するのに必要な引数は次の3つ/引数1~3です

=OFFSET(引数1:参照,引数2:行数,引数3:列数)

演習

まずはサンプルファイルの演習シートにて、OFFSET関数で左の表から木村選手の学年を求めてみましょう       

 *但し、参照はA1セルにします

まず引数1:参照を設定しましょう

上の画像ではみずらいですが、絶対参照でA1セルを参照するようにしました

次に引数2の行数を設定しましょう

木村選手の行はA1セルの1つ下なので1を入力します

最後は引数3を設定して完成です

学年の列はA1セルから2列目ですので、2列目を設定します

次は仕上げとして、鈴木選手の出場回数もOFFSET関数で求めましょう!

参照値は上と同様にA1セルです

行数は3、列数は4にします!

<まとめ>

今回はOFFSET関数・単体の使い方について解説しました

この関数では、まずは起点を作成するところがポイントです

OFFSET関数では、なかなか使い道が見えてこないと思います

ところが、

 この関数は記事の冒頭で述べたように、他の関数と組み合わせると絶大な効果を発揮します

では次回は他の関数との組み合わせ方法について解説します

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

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

にほんブログ村

関数をマスターする8~OFFSET関数~

今回はOFFSET関数を他の関数や機能と組み合わせ、ダッシュボードの中で実践的に使ってみましょう

 赤コーナーと青コーナーの採点合計をラウンド毎に可変で算出するのですが、OFFSET関数をうまく活かしてSUM関数の合計範囲を可変にします

サンプルファイルでは”OFFSET関数サンプル”のシート名になります

まずは何故、可変で算出できるのか、仕組みの概要を解説します

概要

1.MATCH関数を使用して表から該当ラウンドの位置を算出する

 例えばボードに示されたラウンドが4Rならば4を算出するようにします

MATCH関数を使用して、上の画像の画像の「1R~10R」が入力された範囲を参照して4Rの位置を抽出します

2.OFFSET関数で1.で算出した値に応じて合計範囲を算出する

 実は前回はOFFSET関数の引数を3つまでしか解説しませんでしたが、実はもう2つあります

4つ目の引数は「範囲の高さ」、5つ目の引数は「範囲の幅」になります

この2つの引数を使って可変で範囲を設定します

3.2.の関数に名前をつけてSUM関数の中に組み込む

名前機能を利用することで複雑な関数の組み合わせ式をシンプルに示すことができます

以上の3つを踏まえて実際に作業をしていきましょう!

演習

➀MATCH関数で位置を算出する

 検索値はボードがあるF2セル、検索範囲は採点表でラウンドが記載されているM2からV2までの範囲になります(採点は前ラウンドまでなので-1で調整します)

 =MATCH($F$2,$M$2:$V$2,0)

②OFFSET関数で合計範囲を算出す

前回、解説したOFFSET関数の引数は3つでした  

=OFFSET(引数1:参照,引数2:行数,引数3:列数)

実は前述のようにもう2つ追加できます

 
  =OFFSET(引数1:参照,引数2:行数,引数3:列数, _

    引数4:高さ、引数5:幅)

今回の場合は高さをに固定した上で、幅をで算出したMATCH関数の値により可変にします

これまでの内容を組み込んだ数式は以下になります(赤コーナー分)

=OFFSET(a:OFFSET関数サンプル!$M$4,b:0,c:0,d:1, e:MATCH(OFFSET関数サンプル!$F$2,OFFSET関数サンプル!$M$2:$V$2,0)-1)

a:参照・起点/赤コーナーなのでM4セル

b:行数

c:列数

d:高さ

e:合計範囲の幅⇒MATCH関数でボードのラウンドより算出(4Rがボードに示されていれば3になります)

③②の関数に名前をつけた上でSUM関数に組み込む

では数式タブから名前管理をクリックして②の数式に名前をつけましょう!

数式は下の画像の参照範囲のところにコピーしてしまいましょう!

(下の例では赤コーナー、青コーナーと名前をつけています)

名前をつけたら下の画像のようにSUM関数に組み込みましょう!

  

これで完成です

<まとめ>

今回は、OFFSET関数と他の関数を組み合わせて合計範囲を可変にして計算する方法を解説しました

 ポイントは2つあり、まず1つ目はOFFSET関数の4つ目と5つ目の引数をうまくMATCH関数と組み合わせることです

OFFSET関数の4つ目の引数は高さですので、今回のように合計範囲が行方向の場合には1で固定して考えて構いません

5つ目の引数は幅ですので、こちらはMATCH関数と組み合わせて可変にします

2つめはOFFSET関数を名前で設定してSUM関数に組み込む点です

このように複雑な関数を名前で組み込むことで、数式の可読性を向上させることができます

 範囲を可変にする技術はダッシュボードではよく使うので、実際に手を動かしてみてしっかり身につけておきましょう!

ではまた次回

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

にほんブログ村

 

 

関数をマスターする9~文字列操作関数・一式~

今回は文字列操作関連の関数を一式で解説します

この文字列操作関連の関数は一つ一つ覚えるより、組み合わせ方を覚えていくととても実践的な活用の仕方が可能になります

中でもMID関数FIND関数LEN関数の組み合わせは鉄板です

とにかく、”習うより慣れろ”を意識して取り組んでいきましょう!

1.氏名から空白を介して名前を抽出

サンプルファイルに演習シートがあります。このシートで「A列の氏名」から「名前/姓」を関数の組み合わせにて自動抽出します

➀LEFT関数、RIGHT関数

本題に入る前にLEFT関数とRIGHT関数でウォーミングアップをしましょう

対象の文字列の左、もしくは右側から指定した文字数を抽出します

=LEFT(対象文字列,文字数)、=RIGHT(対象文字列,抽出文字数)

上のGIF画像では、B2セルに「=LEFT(A2,1)」が入力されており、氏名の「田中 敦」の左から1文字目の「田」を抽出しています

C2セルには「=RIGHT(A2,1)」が入力されており、氏名の「田中 敦」の右から1文字目の「敦」を抽出しています

②MID関数

ここからが本題です

対象の文字列の途中から文字を抽出します

=MID(対象文字列,抽出開始位置,文字数)

ちなみに木村と浩の間には空白が入っています

上のGIF画像では、D2セルに「=MID(A2,2,2)」が入力されており、氏名の「田中 敦」の左から2文字目を起点として長さ2文字「中 」を抽出しています

D4セルは「=MID(A4,2,2)」が入力されており、氏名の「奈良原 功」の左から2文字目を起点として長さ2文字「良原 」を抽出しています

③FIND関数

FIND関数は、文字列の中から指定した文字の位置を見つけます。

=FIND(検索文字列,検索対象文字)

上のGIF画像のE2セルには「=FIND(“ ”,A2)」が入力されており、A2セルの「田中 敦」から空白の位置を見つけています

④LEN関数

このLEN関数は文字の長さを返してくれます

=LEN(対象文字列)

⑤MID&FIND&LEN関数

②~④を組み合わせて空白を介して氏名から名前/姓を抽出します

STEP1:FIND関数で空白(全角)の位置を見つける

STEP2:(LEN関数-FIND関数)で名前/姓の長さを抽出する

STEP3:STEP1、STEP2の値を使ってMID関数を作成する

2.桁区切りや小数点表示を指定する

桁区切りや小数点の表示桁数は、TEXT関数を使って自由自在に指定できます

=TEXT(対象文字列,書式)

桁区切り

桁区切りは、TEXT関数の2番目の引数を次のように”#,000″と指定します

=TEXT(A8,”#,000″)

小数点の表示桁数

小数点の表示桁数は、第2位まで表示させる場合はTEXT関数の2番目の引数を次のように”=TEXT(A9,”#.00″)”と指定します

=TEXT(A9,”#.00″)

以上です。いかがでしたか?関数の便利さを実感できたのでないでしょうか?

ではまた次回お会いしましょう!

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

にほんブログ村

関数をマスターする11~エクセル数式検証・機能

エクセルダッシュボードでは関数の中に関数を組み込む(ネスト)機会がとても多くなります

つまり、関数の結果を直接的には検証できないことになります

下の画像のようにINDEX関数の中にMATCH関数を使用しているケースなどは典型です

こんな時に”東京”という出力結果が想定した結果でなかった場合には

INDEX関数が悪かったのか?

MATCH関数が悪かったのか?

が分かりません

そんな時に、MATCH関数を書き直すところから検証を行っていたら非効率です

キャプチャ2

そんな時には数式タブにある”数式の検証”機能を使いましょう

この機能では、段階的に数式の内容を表示してくれます

下の画像は検証画面を開いた状態です

ここから黄色の印をつけた「検証」を押してみます

すると、MATCH関数の中味の一部が表示されます

更に、「検証」を押してみます

今度は、完全にMATCH関数の中味が表示されます

そして、もう一度「検証」を押すと完全に数式の中味が表示されます

このように、数式の検証・機能を活用すると段階的に数式の内容を表示してくれるので、数式の検証が行いやすくなります

関数を組み合わせている場合などは、とても便利なのでぜひ活用してください

詳細は動画にて確認できます

https://youtu.be/9dApAmcc2Hk

次回は特殊フォントついて解説します

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

にほんブログ村

N関数~長い数式を分かりやすくする~

N関数はあまり知られていない関数です

ただ、人に分かりにくい「長い数式」「複雑な数式」を書くときにはとても便利です

この関数は数式の中にコメントを挿入できるのです!

N関数の使い方はとても簡単です

=コメント対象の数式+N(“コメント”)

N関数の中は””で囲んでコメントを記述するだけで済みます

注意点は1点だけあります

N関数は値(0)を出力します

ですので、文字列を出力する関数に対して使うとエラーになります

上記の画像では、本来の数式からの出力は「英語/国語」です

つまり、文字型式で出力されるので、N関数によって数字の0を足してしまうとエラーになります

さて、

エクセルにはセルにコメントを入れる機能が元々あります

ただ、コメントを使用しすぎるとシートの見た目が悪くなったりします

それに、誰もがコメントを見れるようになると具合が悪い時もありますよね

22

こんな時にも、N関数の使い道があるのです

23

N関数で数式の中にコメントを入れておけば、数式の中味をみる必要がある人にだけしか見れません

ぜひ有効活用してみてください

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

ExcellentなDashboard作成演習#1 ~スマホ編 5回目~

前回はこちらから

いよいよ本格的に作りこんでいきます

今回はダッシュボードの一歩手前のシート(計算シート)を作成していきます。このシートをダッシュボードのシートにカメラで映していくことになります

今回は3つのことを実施して頂きます

1.スクロールバーからNOを抽出する位置を指定する“値”を出力する

2.ピボットテーブルシートからNOの抽出とNOと関連したデータの抽出

3.NOと関連したデータのエラー修正

1と2ともに前回の復習が前提になります

1は2回目で説明した開発ツールを活用します。

開発タブ

そして2.は3回目で説明したINDEX関数とMATCH関数の組み合わせを使用します

#1 3回目ずらし表示2

固定指定

特に2.は3回目のところで”躓いている”と分かりにくいので、不安な方は復習ををお願いします

それでは後は動画をご確認下さい

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

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

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

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

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

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

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

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

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

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

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

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