【Excel】【Power Query】日付:CHOOSE関数で事業年度の処理をする

今年度からレポートの形式が変更となったので、これまで使っていた途中計算用ファイルの更新をしている。面倒くさがりなので、できるだけ一括管理をしたい。 少数のファイルでの管理は別に難しくはない。ただ、マクロなどを使わずに頑張ると、ファイルのサイズが大きくなっていく。大量のセルに数式が埋め込まれるからだ。私のこれまで使っていたファイルもその例にもれずに段々と肥えてきている。 今回、レポート提出まで時間がなかったこともあって、【Power Query】を使って、今回変更となった部分をカバーする一時的な改良を加えることにした。もちろん、小手先の解決法でしかないため、後日全部キレイにする予定。

まさかの「CHOOSE関数がない」ー Power Query

作業をしていて今日躓いたのは、年度を設定するカラムの追加方法。【Power Query】で関数を使うときには、いつもレファレンスを参照するのだけど、なんとなんと《CHOOSE関数》がない! IFでネストして頑張ってみようかとも一瞬思ったけれど、さすがに多すぎるので諦めて、関数を作ることにした。後になってみれば、よく使うから作っておいて損はない、とは思うのだけど、《関数を作る》というフレーズは難解な、そして面倒くさい作業をする印象。 Power QueryでのCHOOSE関数の作り方については改めて書くとして、まずはとっても簡単なエクセル関数のCHOOSEの使い道について。

事業年度を反映させたい

集計に欠かせないピボットテーブル。標準の機能だと、年・四半期ともにカレンダーイヤーで表示される。気にせずに使う、というのももちろんアリだけど、やっぱり気になるし、比較分析をするときのデータの作り方も面倒。 ピボットテーブルでも【Power Query】でも、初めの段階でできるだけデータを整理しておくのがいい。 取引日を使って四半期、上期・下期、年度を分ける方法はいろんなサイトで紹介されている。参考まで。

四半期の場合:CHOOSE関数

https://www.relief.jp/docs/003420.html 例: A1セルに2018年6月25日と入力されていて、以下の式をB1セルに入力した場合、 =CHOOSE(MONTH(A1),4,4,4,1,1,1,2,2,2,3,3,3,4,4,4) & "Q" B1セルには「1Q」と表示される。

上期・下期の場合:IF関数とAND関数

https://www.relief.jp/docs/003377.html 例: A1セルに2018年6月25日と入力されていて、以下の式をB1セルに入力した場合、 =IF(AND(MONTH(A1)>=4,MONTH(A1)<=9),"1H","2H") B1セルには「1H」と表示される。

年度の場合:IF関数

例: A1セルに2018年6月25日と入力されていて、以下の式をB1セルに入力した場合、 =IF(MONTH(A1)<=3,"FY" & YEAR(A1)-1,"FY" & YEAR(A1)) B1セルには「FY2018」と表示される。

【Power Query】CHOOSE関数の作り方

https://www.excelguru.ca/blog/2014/09/03/multi-condition-logic-in-power-query/ 私の愛読書の著者がブログで回答を掲載。1回作って、使ってみると、「あーこんな簡単なのか」と思うのだけど、初回はいろいろと迷って、結構時間がかかってしまった。

関数を自作する以外の方法についても考えてみた

Advertisements

This site uses Akismet to reduce spam. Learn how your comment data is processed.