タグ別アーカイブ: 関数

カスタム関数を自身で登録して使い回す2~上級編9回目

 今回はPower Queryでカスタム関数を作成した後、実際に作成した関数を呼び出す方法を解説します

まずは、前回の内容の「振り返り」から行いたいと思います

前回は、カスタム関数を作成する際の「ポイント」と「作成画面」について解説しました

 解説の対象となる、これから完成しようとしてる「カスタム関数」は次の画像の「販売単価」「製造原価」「販売個数」から「利益」を算出する関数です

 上の画像の数式バーにあるように、利益を算出するのに数式を入力していたところを、カスタム関数を呼び出すだけで「利益」を自動算出できるようにします

カスタム関数のポイントとしては、2点を解説しました

1点目は、中学時代に習った「方程式を思い出す」です

カスタム関数はこの方程式と同じ様な考えで作成します

そして、代入するのは「列の名前」になるのが特徴です

2点目は、カスタム関数の作成画面についてです

前回は、上の画面を開いたところまでを行いました

 では、この「詳細エディター」画面にてカスタム関数を設定するところから解説をはじめます

目次

1.関数の設定

2.カスタム関数の呼出し

<まとめ>

1.関数の設定

今回は、次の数式を詳細エディター上に関数として設定します

利益=(販売単価-製造原価)x 販売個数

ここで、詳細エディターへの設定方法を分かり易くするために、前回のポイントで解説した方程式を使って解説します

前回のポイントで、方程式の事例として解説したのは次の方程式です

Y=aX + b

上の方程式を詳細エディターに設定したとすると、次の画像のようなイメージになります

詳細エディター設定例

letの箇所は2段の設定になります

1段目:Y = (a,X,b) =>

2段目:ax + b

1段目で「変動する数」として使用する記号を、=を挟んで右辺と左辺に分けて設定した後に、「=>」の2文字で2段目に繋ぎます

そして、2段目では方程式の右辺を設定します

inの箇所はYを設定するだけです

方程式を例に取って、詳細エディターの設定イメージをお伝えしたところで、本題の関数を設定します

利益=(販売単価-製造原価)x 販売個数

上の数式の「変動する数」はそれぞれ、以下のように設定するものとします

・利益 ➡profit

・販売単価 ➡price

・製造原価 ➡cost

・販売個数 ➡quantity

では、上の「変動する数」を詳細エディターにあてはめると下の図のようになります

画面下に以下の文字が出ていたら、右下の「完了」を押します

すると、下のような画像に切り替わります

画面には「パラメーターの入力」と出ていますが、こちらは無視していいです

ここからは通常のクエリを作成した時と同じように、エクセルシートに読込処理を行うわけですが、画面右にてクエリー名を「利益」としておきます

読込処理を行った後は、通常のクエリーと同じ様に画面右に表示されています

マークは通常のクエリとは異なっています

ちなみに、後でこの「利益」の名前を使用して「関数呼び出し」を行います

これで、カスタム関数は完成です

続きを読む カスタム関数を自身で登録して使い回す2~上級編9回目

Sがつく関数は素晴らしい~MAXIFSとMINIFS~

これまで、IFS関数COUNTIFS関数AVERAGEIFS関数を紹介してきました

いずれの関数も複数条件を設定できるので便利です

今回紹介するのはMAXIFS関数とMINIFS関数です

日常的には使いみちはあまりないとは思いますが、エクセル分析をしたい方には便利だと思いますね

簡単に数字のツボがつかめます

私はこれまでピボットテーブルで、この条件つきの最大値と最小値は把握していました

ピボットテーブルまで作成しなくても、このMAXIFS関数とMINIFS関数では、関数1つで最大値と最小値を把握できるのでとても便利です

!MAXIFS関数とMINIFS関数の書き方

これまで紹介してきた、IFS関数やCOUNTIFS関数と書き方は同じです

  =MAXIFS/MINIFS(対象範囲,条件範囲,条件)

Sつき関数は共通の書き方なので、慣れれば慣れるほど使いやすくなりますね

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


にほんブログ村

Sで終わる関数は素晴らしい~AverageIfs関数~

エクセル関数はとても進化しています

XLOOKUP関数も同様ですが、新エクセル関数は絶対に活用した方がいいです!

IFS関数、COUNTIFS関数、そしてAVERAGEIFS関数は、複数条件を簡単に作成できるので便利です

上記3つの関数ともに関数の書き方は同様です

1.文法

AVERAGEIFS関数の書き方は以下になります

=AVERAGEIFS(平均算出範囲,条件範囲1,条 件1,条件範囲2,条件2,・・・)

2.事例解説

今回、AVERAGEIFS関数を試してみるデータは以下です

条件設定と平均値算出は以下の画面で行います

部門と受注日が抽出条件になります

数式は以下の様に設定してあります

複数条件はB列/条件範囲1,⇒C列/条件範囲2の流れで設定しています

この複数条件設定を他の方法で行おうとしたら、関数ではなくピボットテーブルで算出した方が早いですね

関数1つで複数条件を算出できるのは、もの凄い進歩です

最後に補足ですが、部門のリストデータはUNIQUE関数を活用しています

UNIQUE関数で部門の列(東京、東京・・、大阪、大阪)から重複を削除した上で、「#」にてリストとして再利用しています

UNIQUE関数とリストの組み合わせについて深く知りたい方はこちらを参照してください

  ⇒リストをUNIQUE関数でより便利に

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


にほんブログ村

Sで終わる関数は素晴らしい!~Countifs関数~

前回は、IFS関数を紹介しました

今回はCOUNTFIFS関数を紹介します

本当にSで終わる関数は素晴らしいです

数式の書き方はIFS関数とほぼ同じです

今回は下の事例で解説を行います

この表から最高得点・科目と最低得点・科目の組み合わせが当てはまる人数をCountif関数とCountifs関数、の両方で求めてみます

.Countif関数のケース

Countif関数は1つしか条件式を設定することができません

なので、一旦、複合キーを作成して検索条件にするしかありません

数式は次のような書き方になります

Countif関数では、検索条件範囲/H4:H8と検索条件/D12との組み合わせを、1つしか使えないのです

=COUNTIF(H4:H8,D12)

2.Countifs関数のケース

Countifs関数の場合は複合キーを使用する必要はありません

Counfifs関数の場合、複数の検索条件範囲と検索条件の組み合わせを設定できるからです

以下が、実際に上の画面で使用した数式です

=COUNTIFS(F4:F8,B16,G4:G8,C16)

数式の書き方は、前回に紹介したIFS関数と同じ様に、検索条件範囲⇒検索条件の記述を条件がある数だけ繰り返すだけで済みます

<まとめ>

Countif関数を使いたい時に、複数条件がある場合には複合キーを作成しなくてはならないので、いつも面倒くさい思いをしていました

Countif関数を使えば、条件がいくつあろうとあまり手間ではありません

Sで終わる関数は本当に便利ですね

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


にほんブログ村

Filter関数&テーブル機能でのデータ検索

前回、検索付きリストの中で紹介した”Filter関数”は 1つの関数で複数の値を出力できるのも魅力ですが、テーブル機能と連携など、他にも便利な点がいっぱいあります!

さらに、#とあわせて使うと驚くべき結果が出力されます!

<Filter関数を使うメリット>

!複数の条件指定をAND条件とOR条件にて、簡単に使い分けられる

 ピボットテーブルと違い、AND条件に加えてOR条件も使えます。しかも、フィルター機能等より、簡単に条件指定が行えます。但し、ANDやORという指定ではなくて、*と+にて指定します

 試しに、以下のデータからAND条件でデータを取り出してみます

()内の条件(製品別=トラック、売上金額<40000)を*で組み合わせています

 次にOR条件でやってみます。こちらは+で()内を結び付けます

<Filter関数をテーブル機能と組み合わせて使うメリット>

!カーソルの簡単な操作だけで、大量のデータでも一気に各種の指定ができます

➀範囲指定⇒に注目してください

テーブル範囲の左隅にカーソルを置くと↘がでてきますので、そこでクリックするだけで、全範囲をまるごと指定することができます

➁条件範囲指定

こちらは、テーブルの見出しを指定するだけです、今度は見出しの上、↓のところでクリックするだけで条件範囲を指定できます

最後に、個人的に最も驚いた機能は#です

Filter関数が入力されているセルを指定し、後ろに#をつけるだけで、Filter関数の出力を再利用できます!

 前述の検索付きリストはこの#による再出力データを利用しています!

これはなかなか便利です

今後、他の新たな関数ともあわせて講座を用意してみたいと思います!

最後までお読みくださりありがとうございました!

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


にほんブログ村

Power Queryって???合計を計算する

前回はPower Queryを使って区切り文字によりデータを分割する方法を紹介しました。今回はPower Query上で合計などを計算する方法を紹介します

<使用するデータ>

・年度別売上データ

<前提操作>

➀データタブから”テーブルまたは範囲から”クエリを作成するを選択します

②”テーブルの先頭行を見出しにする”にチェックを入れたままOKを押す

③Power Queryを立ち上げおく

1.合計値をグループ別(年度別)に算出する

➀売上金額の列にカーソルを置いたままグループ化をクリックする

②以下の画面のように各項目を設定する

③年度別に合計金額が算出されているのを確認する

④エクセルシートにクエリを読み込む

2.四則演算をする

➀カスタム列を追加する

売上金額の列にカーソルを置いたまま列の追加タブから”カスタム列”をクリックする

②計算したい列を追加する

事前に列名を”千円単位”に変えておく

③割り算(/1000)をする

④正しく値が算出されていることを確認する

<まとめ>

1.2.の事例ともに直感的に操作できている印象を持たれたのでしょうか?特に1についてはピボットテーブルの内容をより簡単に使えている印象です。エクセルが苦手な方でも抵抗なく活用できるのでないでしょうか?ぜひ有効活用してみてください

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


にほんブログ村

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