タグ別アーカイブ: ネスト

IF式を組み合わせて列作成~上級編10回目

こんにちは、Excellent仕事術のガッツ鶴岡(@atsushi1039)です

 Power Queryは関数やVBAの知識がなくても、直感的にクリックしていくことで処理を進めていけるのが魅力です

 ただ、どうしてもIF文をネスト(組み合わせ)し、複数の条件式を組み合わせた新たな列を挿入する必要がでる時もあります

 そんな時のためにPower Queryには「列の追加」タブに「カスタム列」というメニューがきちんとあります

 今回は、下の図の売上金額の集計表をもとにして、IF式を組み合わせて新たな列を作成する方法を解説します

売上金額
アイコン

上級10回目_演習 16.89 KB 41 downloads

...
 

上の図の表「売上金額」を元にして、来期の売上予測を2つのパターンで作成し、新たな列を2列追加します

来期の売上予測のパターンの1つ目は消極的な予測です

・地域がアメリカ ➡ 10%増加

・それ以外 ➡ 5%増加

2つ目は積極的な予測です

・地域がアメリカ ➡ 15%増加

・製品がトラック、且つ、地域がヨーロッパ ➡ 10%増加

・上記2つ以外 ➡ 5%増加

1つ目は1つのIF文により、2つの計算式を作成します

2つ目は2つのIF文により、3つの計算式を作成します

 しかも、2つ目のパターンの3番目の計算式は「且つ」なので「AND」を組み合わせます

作成する計算式を説明したところで、本格的な解説に入ります

消極的パターン

解説は、Power Queryエディターを開いたところから始めます

主な解説内容は、次の①~③があります

①はIF文を作成するメニューの場所

②IF文を実際に作成する画面の詳細

③②を行う上での注意点

では、まずは①から解説します

① IF文を作成するメニューの場所

まず「列の追加」タブの「カスタム列」をクリックします

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

②IFを実際に作成する画面の詳細

まず、この「カスタム列」画面の解説を3か所に分けて行います

・新しい列名➡IF文を使用して新たに作成する列の名称を記入

・カスタム列の式➡「=」から右にIF文を記入

・<<挿入➡上の「使用できる列」で指定された「列」を式の中に挿入

3番目の「<<挿入」の使い方については、以下、詳細に解説します

まずは上の「使用できる列」の中から該当する「列」をカーソルで選択します

次に画面右下の挿入ボタンを押すと、下のGIF画像のように前述の「カスタム列の式」の欄に選択された列が挿入されます

この「カスタム列画面」の3か所について解説したところで、実際にIF文を記入していきます

IF文の構文は以下の通りとなります

【if 条件式 then 条件式に合致する場合 else 条件式に合致しない場合

内容はVBAでIF文を書く場合と、ほぼ一緒です

最後に「End IF」を付けないところだけが違います

実際の消極パターンを書いた画面は、次の通りとなります

【if 条件式 then 条件式に合致する場合 else 条件式に合致しない場合

上の構文に、消極的パターンを当てはめて数式が書いてあります

if [地域]=”アメリカ” then [売上金額]*1.1 else [売上金額]*1.05】

③②を行う上での注意点

ここで1点、注意点があります

上の画像の下に「トークン Eof・・・」なるエラーが出ています

これは、式の構文の中に大文字が入っているからです

式の構文は、下のテキストのように、全て小文字で記入するようにしてください

IF、THEN、ELSE ➡ if、then、else

では、正しく数式を記入したところで下のOKボタンを押します

そうすると、Power Queryエディター画面に新たな列が挿入されます

では、念の為に新たに挿入された列が正しいかどうかをPower Queryエディター上で確認します

まず「売上金額」と「予測式_消極」の列をカーソルで入れ替えます

次に上の両列をカーソルで選択したまま「列のタブ」の「標準」から除算をクリックします

すると、次の様に「(左側)予測式_消去」÷「(右側)売上金額」の結果が出力されます

「標準」機能の計算は、列の位置が左にあるものから自動的に計算されてしまうので、列の位置を入れ替えることを事前に行いました

さて、出力結果は次の通り、正確に出力されていたので「積極パターン」の解説に移ります

積極パターン

まず、積極パターンの内容を振り返りましょう

・地域がアメリカ ➡ 15%増加(A

・製品がトラック、且つ、地域がヨーロッパ ➡ 10%増加(B)

・上記2つ以外 ➡ 5%増加(C)

では上記のA~Cを、「カスタム列を作成する画面」に当てはめると次のような式になります

まず、前述のA( 地域がアメリカ ➡ 15%増加 )はの式のように、1.の消極パターンと同様の書き方です

【if [地域]=”アメリカ” then [売上金額]*1.15】

次にBの箇所を書き始めるところが1.の消極パターンと違います

「else」の後にそのまま2つ目のIF文を「AND」を交えて、の式のように書きます

【else if [地域]=”ヨーロッパ” and [製品別]=”トラック” then [売上金額]* 1.1】

そして最後にCを「else」の後に書きます

else [売上金額]*1.05

上の3つの数式をつなげると次のようになります

if [地域]=”アメリカ” then [売上金額]*1.15 else if [地域]=”ヨーロッパ” and [製品別]=”トラック” then [売上金額]* 1.1 else [売上金額]*1.05

「カスタム列を設定する画面」の下のOKボタンを押すと、次の様に出力されます

1.の消極パターンでも行ったように、検算を行うと次のようになりました

 Bの 条件【製品がトラック、且つ、地域がヨーロッパ ➡ 10%増加】もしっかり反映されています

 上のPower Queryエディタの内容をエクセルシートに読込処理を行うと、次の画像のようになります

<まとめ>

今回は、IF文を使用した列を追加する方法を解説しました

IF文の構文の書き方はVBAとほぼ同じになっており、次の通りとなっています

【if 条件式 then 条件式に合致する場合 else 条件式に合致しない場合

 IF文を更に組み合わせる場合には、上記の「else」の後にまたIF文を追加すればOKです

 尚、構文を間違えていたり、前述した大文字で記入を行ったりすると 「トークン Eof・・・」なるエラーが出てしまいますので注意が必要です

 ちなみに、正確に数式を記入できている場合には、以下のメッセージが画面下にでます!

今回の解説は以上です

この記事の内容をマスターしたら、M言語を使用した「カスタム列」にもぜひチャレンジしてみてください!

https://analytic-vba.com/power-query/m-code/mcode-begin-custom/

長文に最後までお付き合い頂き、誠にありがとうございました

参考までに、今回の解説で使用したデータと完成したデータを添付しておきます

アイコン

上級10回目 24.44 KB 9 downloads

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


にほんブログ村

もしもし問題を解決する~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へ

にほんブログ村