スポンサーリンク
ExcelのPowerQueryでは、自分で計算式など入れたり、条件次第で結果が変わるようにした列を、追加することができる。
下図では、会社ごとに当期・前期の実績が並んでいる。
これに、当期が前期に対し何%増減したかの比率＝対前期比率を、新しい列として追加したい。
それってマイナス値とかはどう処理するのかなどという問題があるが、詳細は後で述べる。
カスタム列の追加
ではまず、「対前期比率」を求める式は、次のようになる。
対前期比率＝100×(当期実績÷前期実績－1)
これを新しい列として追加するには、
リボン「列の追加」タブ
→「カスタム列」
とする。
この「カスタム列」は、計算式などを入れたオリジナルの列を追加できるわけで、慣れればM言語ってやつの様々な関数を入れたりもできる。
まず、「新しい列名」のところに項目名を、「比率」とでも入れよう。
式の中に、項目「当期実績」を使いたいが、それは右端の「使用できる列」の中にある項目「当期実績」をダブルクリックすれば、式の欄に挿入される。
「挿入」ボタンを押しても良いけど、ダブルクリックがやりやすいだろう。
同様の手順で項目「前期実績」も挿入して、前述の数式
対前期比率＝100×(当期実績÷前期実績－1)
を再現するために、式の欄に
= 100*(( [当期実績] / [前期実績] )-1)
と入れよう。
[当期実績]
[前期実績]
以外の文字は、普通に手入力するしかない。
入力し終わったら「OK」を押す。
すると、項目「比率」の列が追加された。
なんか、0で割り算したものは「Infinity」とか表示されてるのもあるが、ひとまず気にせずいこう。
四捨五入の列を追加
いま追加した項目「比率」について、小数になっているが、これを
小数第一位未満を四捨五入し、小数第一位まで記載する
ルールとする。
ひとまず、この「比率」列はそのまま消さず残しておいて、四捨五入した後の列を新規追加したい。
リボン「列の追加」タブ
→「丸め」
→「四捨五入」
としよう。
今回は小数第一位まで記載する方針なので、桁数に「1」を入れ「OK」を押す。
四捨五入した列が追加された。
条件列の追加
「対前期比率」の正式な求め方(決算資料のルールに準拠)
それでは、今回求める「対前期比率」の詳細な条件だが、経理が世間に開示する決算書類における公式ルールによると、次のようになる。
の式で求めた値を、小数第一位未満を四捨五入し、小数第一位まで記載する。ただし
- 前期実績≦0なら、結果は「-」表示する。
- 当期実績＜ 0なら、結果は「-」表示する。(正式には「当期・前期の一方もしくは両方がマイナス」とルールに書かれているが、少しアレンジした)
- 式で求めた結果が1,000以上の場合も、結果は「-」表示する。
条件列の設定
ということで、条件によって結果を「-」表示することもあるのだが、こういう条件分けする列を追加するには
リボン「列の追加」タブ
→「条件列」
とする。
次の画面で、まず列名に「結果」などと入力する。
その下に、条件入力欄がある。
最初の「列名」「演算子」は一覧から選択するので、それぞれ「前期実績」「次の値以下」を選ぼう。
次の「値」欄には「0」を、「出力欄には「-」を入れる。
これで、先述の条件の一つ
前期実績≦0なら、結果は「-」表示する。
をセットした。他にもセットする条件があるので、次に「句の追加」を押そう。
そして、条件「当期実績＜ 0なら、結果は「-」表示する。」として
「当期実績」「次の値より小さい」「0」「-」
と条件を入れて「句の追加」を押す。
次に、条件「式で求めた結果が1,000以上の場合も、結果は「-」表示する。」として
「比率」「次の値以上」「1000」「-」
と条件を入れる。
特殊な条件に該当しない場合の設定
ここまで入れたのは、結果を「-」表示する特殊な条件に関するものだが、これら特殊条件に該当しなければ通常は列「四捨五入」の値をそのまま結果表示すれば良い。
そこで、下の「それ以外の場合」で、左側のボタンを押して「列の選択」を押そう。
そして選択肢から、列「四捨五入」を選ぶ。
これで、特殊な条件に当てはまらない通常の場合は、列「四捨五入」の値がそのまま適用されることになった。
そのまま「OK」を押せば、列「結果」が追加され、想定した通りの結果が返されるようになる。
任意の値や、他の列の値と比較できる
「条件列の追加」で、「前期実績が0以下」「当期実績が0より小さい」など、上の方の画面で条件設定したが、このときは「値」欄に「0」などの値を直接入力した。
ここでも、
項目「前期実績」が項目「当期実績」より大きい
といった条件を、「列の選択」によって設定することもできる。
「条件列の追加」ではこのように、各列の値を、手入力した任意の値と比較することもできるし、何か他の列の値と比較することもできる。
判定の優先順位変更
項目「比率」が1,000以上の場合、結果は「-」表示する。
という条件を設定したが、その下に、
項目「比率」が2,000以上の場合、結果は「big」と表示する。
という条件を、試しに追加してみる。
しかしこの場合、図の「会社004」における項目「比率」は2,000をオーバーしているが、「結果」は「big」ではなく「-」のままだ。
これは、
これは、
項目「比率」が1,000以上の場合、結果は「-」表示する。
条件が上にセットされており、そっちの方が優先されているからだ。
その優先順位を変えるには、「出力」欄の右にあるボタンを押して「上へ移動」を押せば良い。
これで「比率が2000以上」条件が上に移動し、優先順位が上がった。
会社004の「結果」列に「big」と表示されるようになり、ひとまず上手く行った。
スポンサーリンク
