タグ別アーカイブ: 数式

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

 資料を作成する度に、毎回毎回、同じ数式を入力していませんか?Power Queryではカスタム関数という仕組みにより、事前登録したカスタム関数を呼び出して使い回していくことができます

今回とあわせて2回に分けて、カスタム関数についての解説を行います

 私が昔、企画の仕事をしている時は、会議の前日は必ず数値資料を作成していました

 「前期比」「利益率」「単価」などを資料に盛り込むわけですが、毎回決まった数式を表に入力していました

毎回、同じ数式を入力するので体で覚えてしまったことをよく覚えています

 この記事を見て下さっている読者の方の中にも似たような経験のある方がいらっしゃるのでないでしょうか?

 カスタム関数をマスターして、「同じ数式の再入力」は自身で事前作成した「カスタム関数」の呼出し処理だけで代用できるようにしましょう!

 本格的な解説に入る前に、「今回のポイント」と「今回使用するデータと、作成するカスタム関数」について解説を行います

1.今回のポイント

中学数学の方程式

中学時代にこんな問題を数学の時間に出された経験はありませんか?

Y=aX+bの式に(X=2,Y=5)を代入するといった問題です

今回のカスタム関数を作成する時にも、同じような考え方をします

ただ、今回は代入するのが「列の名前」になります

空のクエリの作成

今回はA.の方程式を、空のクエリを作成してから指定します

「空のクエリ」はこれまでのPower Queryの解説では出てこなかった方法です

 更に空のクエリを作成した後、Power Queryエディター画面から詳細エディター画面を開き、下の画像の「let」と「in」の箇所に方程式を指定します

2.今回の使用データと作成内容

 今回のポイントを2点解説したので、次に今回使用するデータと作成するカスタム関数について解説します

アイコン

上級編8-9回_演習 19.39 KB 33 downloads

...

今回使用するデータは、次の画像にあるデータになります

 今回作成するカスタム関数は、黄色く塗られた列の値を使用して、下の画像の利益を計算する関数になります

ちなみに、上の画像の「利益」の列には以下の数式が入力されています

 使用するデータと作成するカスタム関数について解説したところで、本格的な解説に入ります

.空のクエリ作成

データタブの「データの取得」から「その他のデータソースから」を開きます

開いた一覧の一番下に「空のクエリ」があります

 上の画像の「空のクエリ」をクリックすると、Power Queryエディターが開きます

次にPower Queryエディターのホームタブから「詳細エディター」を開きます

すると、次のような画面が開きます

上の画像の「Let」の下と「in」の下に、ポイントAで解説した方程式を入力していきます

<まとめ>

 今回は、カスタム関数を作成するにあたってのポイントを2点解説した後、ポイントAで解説した、方程式を入力する画面を開くところまでを解説しました

 詳細エディターの画面は少しややこしいところもありますが、方程式の作成の仕方をイメージしてもらえればOKです

では次回は、カスタム関数を完成させます!

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


にほんブログ村

エクセルアドイン~数式をシートから取り除く~

エクセルシートの中に数式が入っていると、不都合なケースってありますよね?

他の人にメールをファイルで送ったり、数式ではなく数式の結果だけをコピーしたい時などがこのケースに当てはまると思います

私はそそっかしいので、よく数式をそのまま他のセルにコピーして「エラー」を起こしてしまい、値コピーからやり直したりします

今回は、数式が入っている不都合から解放してくれるアドイン(アプリ)を紹介します

1.アプリを入手

挿入タブの「アドインを入手」からアドインを入手します

検索するアドイン名は「Datasheet formula remover」になります

もし、アドインの入手の仕方、細かい操作方法でわからない点がありましたら、ぜひこちらの記事もご参照ください

2.アドインを起動

アドインを入手すると、専用のタブが出来ます

上のタブをクリックすると、下の画像から実際にアドインが起動できるようになります

アドインを起動すると、画面右に操作画面が現れます

3.数式を取り除く

数式が入ったセル(もしくは範囲)にカーソルを置きます

アドイン内のチェックボックス「バックアップを取ってある(Myfile backed up)」にチェックを入れます

チェックを入れたら、GOボタンを押します

すると、数式が入ったセルが文字で置き換わっています

もしもし問題を解決する~IFS関数&LET関数~

 エクセルの関数を覚えていくと、そのままエクセル作業の効率は上がっていきます

そして、エクセル関数の使用方法が習熟してくると必ず次の問題が起こります

関数を組み合わせている内に、長く複雑な数式になってします

 長く、複雑な数式で一番やっかいなことの1つは、IF関数の中に更にIF関数を入れていくもしもし問題です

 条件が増えれば増えるだけやっかいな数式になり、作った人以外は誰も直せなくなります

 今回は、この問題をIFS関数とLET関数を使って解決していきます

まず、今回の取り組むテーマについてお話します

下の表は個人の成績表です

 この表からMAX関数を使って、各人の最高得点を出力しておき、更にIF関数を使って、最高得点科目を出します

 このテーマを解決するのに、従来のIF関数を使用した場合には、下の画像のように2つのIF関数を組み合わせねばなりません

ちなみにセルF4では、前述のようにMAX関数を使用しています

 筆者も5科目でテーマを作りたかったのですが、数式が複雑すぎて説明に困ると考えたので3科目にしました

さて、ここでIFS関数をIF関数の代わりに使用してみます

こちらはIFS関数は1つ使用するだけですみます

IFS関数は複数の条件式があるときに便利な関数です

IF関数の場合ですと

 =IF(条件式,出力/条件式に合致,出力/条件式に合致しない)

という数式の書き方になりますが

IFS関数の場合ですと、

 =IFS(条件式1,出力/条件式1に合致, 条件式2,出力/条件式2に合致,・・・,TRUE,出力/左記の条件式にいずれも合致しない場合)

という数式の書き方になります

この数式のポイントは2つあります

①条件式と出力の並べ方

 条件式⇒条件式に合う場合の出力⇒出力⇒条件式に合う場合の出力

 という並び順になり、IF関数の場合の一)条件式、ⅱ)出力(合致)ⅲ)出力(合致しない)の場合と違うこと

➁条件式に合致しないもの

 これは英語で言えば、ELSEのことですが、IFS関数では

 「,TRUE,出力(いずれにも合致しない)」

と書きます

さて、ここでLET関数を使って、IFS関数をもっとシンプルにします

LET関数とは関数の中に変数を使うことができる関数です

変数とは方程式のXと考えて頂ければ幸いです

方程式もXを使うことで式がシンプルになっています

 今回は上の表でIFS関数の外で求める最高得点(MAX関数の出力結果)を方程式に組み込みます

まず、LET関数の文法について解説します

=LET(変数名(X),変数の値(X=?),実際に変数を使った式)

変数(X)を最初に宣言するのがポイントです

今回の事例では、最高得点をMaxの名で宣言して、実際にMAX関数で最高得点を算出しておきます

そして次に、IFS関数にて変数:Maxを条件式内に使います

セルを通じて参照するよりずっと分かり易い数式になっているはずです

LET関数を使わない場合には、上の数式のようにF4セルを参照し、F4に最高得点が入っている、という意味合いを確認せねばなりません

<まとめ>

今回は、IFS関数を使い、複数条件が存在する数式を分かり易くする方法について紹介しました

更に、LET関数でIFS関数の中味を方程式のXのように分かり易くする方法についても触れました

IFS関数もLET関数も最新の関数です

ぜひ、この新たな2つの関数を有効活用して、「エクセル業務」の効率を向上させていきましょう!

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

にほんブログ村

エクセル数式検証・機能

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

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

キャプチャ

例えば、上記の画像ではINDEX関数の中にMATCH関数が組み込まれています

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

INDEX関数が悪かったのか?

MATCH関数が悪かったのか?

が分かりません

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

キャプチャ2

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

詳細は動画にて確認しましょう

https://youtu.be/9dApAmcc2Hk

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

にほんブログ村