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

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

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

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

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

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

 「カスタム列」のメニューの中でIF文を効果的に使えば、一見、複雑に思える「この場合はこう処理して、この場合はこういう風に処理する」といった処理も一定のルールを覚えれば簡単に行えます!

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

売上金額

この記事を最後まで読み終えた時には、一段上のレベルでPower Queryを深く使いこなせるようになっているはずです!

*IF式内にAND条件やOR条件を設定する場合はこちらの記事を参照してください!

アイコン

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

...
 

*記事の最後に完成版のサンプルファイルを添付しています

 今回の記事では「IF文」を活用しながら、上の図の表「売上金額」を元にして来期の売上予測を2つのパターンで作成し、新たな列を2列追加します

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

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

ⅱ)それ以外 ➡ 5%増加

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

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

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

ⅲ)上記2つ以外 ➡ 5%増加

1つ目は1つのIF文にて、2つの計算式を組み合わせます

2つ目は2つのIF文を組み合わせることで、3つの計算式を組み合わせます

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

さて、

今回作成するIf文を使った計算式を説明しました

次に今回のポイントを解説させて頂きます

ポイント

構文

今回のポイントは何と言っても、IF文を使った構文を的確に記述できるかどうかです

If文を1つ使う場合の構文は次の通りとなります

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

次が2つのIF文を組み合わせた時の構文となります

if 条件式1 then 条件式1に合致する場合 else if 条件式2 then 条件式2に合致する場合 else いずれの条件式にも合致しない場合】

カスタム列

今回のIf文は「列の追加タブ」のカスタム列の画面で作成します

こちらの画面では「新しい列名」「カスタム列の式」を指定します

カスタム列の式には「<<挿入」ボタンにより列を挿入できます

こちらの詳細は以降の記事内で詳細に解説させて頂きます

大文字と小文字の区別

 こちらはM言語のシリーズで本格的に解説する内容なのですが、Power Queryの言語であるM言語では大文字と小文字を厳格に区別します

上記の構文の解説にあるように、「if」「then」「elseif」「else」はいずれも小文字で記入する必要があります

それでは、今回のポイントの3点を解説させて頂いたので、本格的な解説に入りたいと思います

目次

1つのIf文作成:消極的パターン

2つのIf文作成:積極的パターン

<まとめ>

1つのIf文作成:消極的パターン

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

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

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

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

③②を行う上での注意点

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

If文を記入したら、画面右下のOKボタンを押せばPower Queryエディタ画面に反映されます

上のGIF画面で消極パターンのIf文を書いた画面は、次の通りとなります

上の画像の内容をテキストにしたのが次の数式です

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

③②を行う上での注意点

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

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

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

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

IF、THEN、ELSE ➡ if、then、else

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

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

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

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

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

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

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

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

2つのIf文作成:積極的パターン

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

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

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

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

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


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

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


にほんブログ村