カテゴリー別アーカイブ: 関数

関数をマスターする1

今回からエクセルダッシュボードを活用する上で必須な技術である関数について解説していきます。

ちなみに、

関数という言葉をよく耳にしますが、読者の方の中には「関数とはところで何だろう?」と疑問をお持ちの方もいらっしゃると思います

今回は、そもそも関数とは何か?から始めたいとおもいます

関数とは?

中学生の時に、数学の時間にこんな事を学びませんでしたか?

Y=3X+1

上の数式にXを3にして代入したりもしたと思います

これも関数の一種です

Xさえ代入すれば、Yが求まります

エクセル関数の場合には、このXに数字文字を代入したり、セル名を代入したりします

私はエクセル関数いついては、単なる自動販売機と割り切ればいいとおもっています

欲しいジュースの値段を見て、指定されたお金を入れ、ボタンを押せば欲しいジュースは出てきます

この一連の行動と一緒です

 関数の場合にはこの”お金”、”ボタンを押す”が引数という呼び方になり、()の中に”,“で引数間の区切りを入れながら、引数を設定していくのです

そうすれば、自動販売機のジュースと同じ様に、欲しい値が出てきます

引数について

関数では、この引数の指定がとても重要になります

今回はEXACT関数でこの引数というものを、具体的に説明したいと思います!

EXACT関数では2つ引数が必要です

2

上の図を見てください

”,”をはさんだ2つの「セル名/B3、B4」を通じでの2つの引数が()の中に設定されています

この2つの引数の設定だけでTRUE(2つの値が一致している)、もしくは、FALSE(一致していない)の値が出てきます

うまく引数を設定する

うまく引数を設定するのに利用して欲しいツールが3つあります

名前タブ

エクセルシートのA列の上に名前ボックスという表示があります

カーソルで選択している位置がセル名で表示されます

数式バー

関数マークの隣に数式バーというものがあります

数式バーでは、カーソルで選択しているセルに入っている関数の中味を確認することができます

F2(関数参照)

関数が入力されているセルにて「F2」を押すと、下の画像のように引数の設定内容が視覚的に表示されます

このF2ボタンで引数に設定しているセル名を具体的に確認できます

<まとめ>

今日は「関数とは何か?」を入り口にして、関数の基本を解説しました

実際には、関数を習得する場合に大事になるのは「実感」です

関数が苦手な人は、関数独特の「複数の箇所が同時に動く」動きが実感できていないケースが多いはずです

 ですので、関数が苦手な人が実感を得るには、本来は座学ではなく、エクセルの上級者にPCで関数を操作するところを見せてもらうのがいいです

この関数の座学学習の欠点を補うために開発したのが、関数ドリルです

 この関数ドリルでは、エクセルシート上で忍者が関数を操作し、関数の動きを実感できるようにする仕組みになっています

関数ドリル内で下の画像の「解説」を押すと、忍者が登場します

忍者は関数を作成しながら、名前ボックスや数式バーの動きも表示していきます

この関数ドリルには複数の関数の解説がありますが、関数が苦手な人は合計処理だけでも試してみてください

合計処理だけでも4つシート(4種類)があります

4種類を試してみると、新たな発見とともに関数独自の動きを実感できるはずです

今日はこれで解説は終わりです

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


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

関数をマスターする2~IF関数~

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

IFですから、日本語に直すと”もし・・・”になります

このもし、に対応したIF関数をうまく活用すればエクセル活用の仕方は大分深まります

本来は人間がルールに従って、判断しなければならない処理をエクセルに任せることができます

ここからは前回のサンプルファイルをベースに活用の仕方を解説します

1.IF関数の書き方

前回解説したEXACT関数を思い出してください

このEXACT関数に引数を2つ入れると”TRUE”、”FALSE”、の2種類の結果を取り出すことができました

IF関数は、このEXACT関数で行われる処理の更にその先を処理してくれます

IF関数で必要になる引数は、次に記述したように3つになります

<=IF(1/条件式,2/条件式がTRUEの場合のアクション,3/FALSEの場合のアクション)

2.EXACT関数の結果をIF関数で書き換える

IF関数を使って前回のEXACT関数の結果を書き換えてみましょう!

4

条件式にC3セルのEXACT関数を使い、TRUEの場合「一致」、FALSEの場合「不一致」となるようにIF関数を記入してみてください

結果は次のようになるはずです

GIF2

ちなみに、””のマークで囲むことでExcelは文字列で表示するという処理になります

逆に””で囲まない場合にはエラーになります

ではまた次回、IF関数を更に深く解説していきます

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

関数をマスターする3~IF関数2~

今回は前回に引き続き、IF関数を解説します

繰返しになりますが、IF関数を覚えるとエクセル活用の仕方はだいぶ深くなりますので

しっかり取り組みましょう!

今回取り組む内容は以下です

①IF関数の中にIF関数を更に加えて、判断条件を複数組み合わせる

②前回は判定条件にEXACT関数を入れたが、今回は直接、判断条件を入力する

この内容が出来れば、更にIF関数を有効に活用することができます

では前回と同じサンプルファイルを使用して取り組みましょう!

5

上記の画面で3つのセルの値が一致していたら、”全一致”を出力しましょう

一方、一つのセルのでも違っていたら”不一致”を出力しましょう

つまり、3つの値を比較しますが、最終的には全一致、不一致の2つに分けるのです

では2つのステップで完成させましょう

1.まずは1つ目のIF関数を作成しましょう!

B3セルとB4セルの内容が=(イコール)の時に”全一致”を出力しましょう

一方、それ以外の時には”不一致”を出力します

6

では次に考えるのは、ここから更に条件が分かれるのは次のどちらでしょう

”全一致”の場合

”不一致”の場合

少し立ち止まってから考えてみてから、次に進みましょう

2.2つめのIF関数を入力しましょう!

3つの値を比較しますが、最終的には全一致と不一致の二つに分かれます

ですから、1.で更に2つに分かれるのは全一致のケースです

それでは、場合分けをしながら、もう少し解説しましょう

●全一致のケース

①B3=B4 AND B4=B10

●不一致のケース

②B3<>B4 AND B4=B10

③B3=B4 AND B4<>B10

注)<>は不一致の意味

ここで1.のIF関数に当てはまると②のケースは既に不一致として出力されるようになっています

後は①と③の違いの判定です

ですから1.IF関数の”全一致”の出力には、③/B4セルとB10セルが不一致 のケースが一緒に組み込まれてしまっています

ですので、IF関数を入れて更に分岐させます

すると以下のような動きになります

GIF3

3つのセルが全て0になった時に「全一致」が出力されます

<まとめ>

今回はここで解説は終わりです

今回は、IF関数を複数組合わせて使うケースを解説しました

尚、IF関数内の条件の書き方については、「<」や「>」などの不等式も使えます

IF関数内で不等式も活用できると、更にIF関数を有効活用できます

では、また次回!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 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へ

関数をマスターする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へ

関数をマスターする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関数について解説します


にほんブログ村

関数をマスターする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へ

にほんブログ村

関数をマスターする10~順位付け関数~

今回は数字の順位付けを行う関数を解説します

これらの関数は意外な使い道があり、例えば以下の様なグラフでも活用しています(実は2つのグラフを組み合わせています:詳細

1.最大値(MAX関数関数)と最小値(MIN関数)を抽出する

MAX関数は参照範囲から最大値を抽出します

=MAX(参照範囲)

MAX関数

MIN関数は参照範囲から最小値を抽出します

=MIN(参照範囲)

MIN関数

2.上の順位(LARGE関数)、下の順位(SMALL関数)で抽出する

LARGE関数は参照範囲から、指定した順位の値を抽出します

=LARGE(参照範囲,順位)

下の画像では、LARGE関数の参照範囲としてC列の点数が記入された範囲を指定し、順位としてはE7セルに記入されている「2位」からLEFT関数にて「2」を抽出して指定しています

LARGE関数

SMALL関数は参照範囲から、指定した順位の値を抽出します

=SMALL(参照範囲,順位)

SMALL関数

<まとめ>

今回は、最大値などの順位付けを行う関数を解説しました

今回解説した関数は単独ではあまり使用機会が少ないですが、他の関数との組み合わせにより有効活用できますので知っておくとても便利です

次回は関数の検証機能について解説します

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

にほんブログ村