【Excel】ドロップダウンリストの設定セルを明示するため、当該セルにコメントを表示

（１） やりたいこと

人に入力してもらうためのExcelフォーマットにドロップダウンリストを設定してリストから選択してもらいたい場合の話です。

下図の例はD4セルにデータの入力規則のリストを設定しています。

ただ、その項目（セル）にドロップダウンリストが設定されているかどうかは、何も書いていない場合そのセルを選択しないとわかりません。

そこで、リストから選択してもらうことを明示するためにメモ等で説明を加えたりします。

例えば、下図のようにセルにメモ（コメント）をいれてみたり、

データの入力規則の入力時メッセージでセルを選択時に表示されるようにしたり、

隣のセルに説明書きを入れたり、まあこれでもよいのですが、、

 

今回やりたいのは、下図のように入力対象のセルにコメントを表示させて、

リストから選択した後は元のコメントを消すというものです（当然、リストの項目のひとつにコメントいれるというのはなし）。今回の表のようにシンプルなものであればどの方法でもよいのですが、もっとごちゃごちゃしたフォームやリスト選択の箇所が多いものの場合、メモや別セルへの入力だと見にくくなると思い、直接セルに表示させるのがよいかなと思いました。次項からやり方を説明します。

 

（２） 方法

まず、D4セルにはデータの入力規則からリストの設定がされている状態からスタートします。

条件付き書式ルールの管理を開き（Alt＋O,D）、新規ルールをクリックします。

「指定した値を含むセルだけを書式設定」を選択し、下図の通りルールを設定します。

数式のところは、＝" " （半角スペース）です。次に「書式」をクリックします。

表示形式タブでユーザー定義を選択し、種類に @ に続けて表示したいコメントを入力します。入力される文字列（半角スペース）に続けて@に続く文字列が表示されるようにするためです。

その他に設定したい書式を設定します。

OKをクリックします。

 

次からがポイントです。D4セルに半角スペースを入力しようとするとデータの入力規則の設定により下図のようにエラーとなります。

これを回避するため、データの入力規則を開き（Alt＋D、L）、エラーメッセージタブの「無効なデータが入力されたらエラーメッセージを表示する」のチェックを外します。

これでD4セルに直接入力ができるようになりますので、半角スペースを入力すると条件付き書式の設定により、下図の通りの表示となります。ただ、プルダウンリスト以外の入力を許可するのであればこれでよいのですが、そうでない場合はこの状態から先ほどの「無効なデータが入力されたらエラーメッセージを表示する」にチェックを入れる必要があります。

これで完成です。


















 

 

 

 

 

 

【Excel】ピボットテーブルで挿入したスライサーで選択中の項目を取得する。

（１） やりたいこと

データテーブルからピボットテーブルで集計し、ピボットグラフを作成。これにスライサーを設定して項目の絞込みと連動するグラフを作る時の話です。

スライサーでどの項目を選択したかはスライサーのボタンの色を見ればわかりますが、最終的なアウトプットにスライサー自体を表示したくない場合、現在スライサーで選択している項目を取得してセルやテキストボックスできればよいと思い考えてみました。

下図が概要をまとめたものです。

（２） 事前準備

まず、（データがテーブルの場合）テーブルデザインタブの中の「ピボットテーブルで集計」からピボットテーブルを作成します。

データの列項目は複数ありますが、今回は月別の売上高推移の棒グラフを作りたいので、ピボットテーブルの行に「年月」、値に「売上高」をセットします。

ピボットグラフとスライサーを追加した状態（説明は省略します）が下図となります。スライサーは分類としました。

この状態で例えばスライサーの「果物」を選択するとピボットテーブル、グラフともに連動して「果物」だけの数値に絞り込まれます。

（３） スライサー選択項目の取得方法

（以下、説明用にすべて一つのシートで進めていきますが、実務ではグラフ・スライサーとピボットテーブル、数式は別シートに分けるのが無難だと思います。）

 

まず、元データからピボットテーブルを追加します。行にはスライサーと同じ項目「分類」を、値は「売上高」をセットします。（値はなんでも構いません）

スライサーを選択した状態で右クリックし「レポートの接続」をクリックします。

二つのピボットテーブルの左端のチェックボックスを選択してＯＫをクリックします。

これで二つのピボットテーブルが接続されます。

試しにスライサーで「果物」と「肉類」を選択すると、下のピボットテーブル2も連動して果物と肉類に絞り込まれます。

ここからセルに数式を入れていきます。E26、F26の「分類_一意」「ｽﾗｲｻｰ選択判定」は手入力した見出しです。

まず、E27セルに元データの分類が一意となるように次の数式を入力します。

=SORT(UNIQUE(data[分類]))

 

UNIQUE関数でdata（テーブル名）の分類列の一意の値を取得した結果をSORT関数で降順に並び替えています。

下図の通り表示されました。スピルされますのでテーブルに新しい分類が追加された場合も自動的にこの数式の結果に追加されます。

次にとなりのF27セルにスライサー選択判定用の数式を入力します。

=IF(ISERROR(GETPIVOTDATA("売上高",$B$25,"分類",E27)),"",E27)

 

GETPIVOTDATA関数でピボットテーブル2の分類にE27の値がなければエラーとなるので、IFとISERROR関数を使いエラーの場合はブランクとし、値がある場合はE27の値を返すようにします。

果物は選択されているので「果物」が返ります。

F27の数式を下にコピーします。分類数は3つですが、データに新規分類が追加されても対応するように適当なところまでコピーしています。（が、ここはもっとよい方法があるのではないかと・・納得できていません）

これでスライサーで選択中の項目を取得することができました。

試しに「魚介類」に絞り込むと下図の通り正しく表示されました。

最後に、取得した判定結果を一つのセルに表示するための数式をE1セルに入れてみます。

=TEXTJOIN(",",TRUE,INDIRECT("F27:F"&26+COUNTA(E27#)))

やりたいことはF27～F29に表示された文字列をTEXTJOIN関数で「,」で区切って繋げたいだけなのですが、新規分類が発生した場合にセル参照範囲が変わってしまうので、COUNTA関数でE27以下に表示された分類数を数え、INDIRECT関数でF列の参照に変換しています。（OFFSET関数等を使って別の書き方ができるかもしれません）

数式の結果がE1セルに表示されました。

複数の項目を選択すると下図のようになります。

あとは、先ほどの数式にタイトルなど任意のテキストを追加します。

下図のように表示できます。

（４） 最後に

今回はひとつのスライサー項目のみとしましたが、複数のスライサーの項目を取得する場合はもうひと手間必要です。

試行錯誤しながら考えましたが、実はもっと簡単にピボットテーブルのスライサーの選択項目を取得する方法があるかもしれません。



















【Excel】２段階ドロップダウンリストの設定

２段階のドロップダウンリストの設定方法について説明します。様々なやり方があると思いますが、個人的には今回紹介する方法が一番スッキリしました。

（１） やりたいこと

下図に記載の通り、ドロップダウンリスト用のリストを作成し、一つ目のドロップダウンリストから選択したものに対応して、二つ目のドロップダウンリストの選択項目が変わるようにします。

（２） 方法

まず、適当なリストをセルに入力します。

 

作成したリストをテーブルにします。

テーブル名はデフォルトで「テーブル1」とつきますが、今回は「リスト」という名称に変更しました。

入力用のセルに「データの入力規則」でドロップダウンリストを設定していきます。

通常は先に作成したリストと入力用はシートを分けますが、今回は説明しやすいよう同じシートにしています。

まず、種別の項目から設定します。データの入力規則の設定タブに次の通り選択・入力します。

 

入力値の種類　：　リスト

元の値　：　=INDIRECT("リスト[#見出し]")

 

設定した種別のセルでリストの見出しに設定された項目が選択できるようになりました。

次に、品名のドロップダウンリストを設定します。

入力値の種類　：　リスト

元の値　：　=INDIRECT("リスト["&B15&"]")

 

すると、ドロップダウンリストは設定できましたが、テーブルの空白行までドロップダウンリストに表示されてしまいました。これでも使うことはできますが、空白行が増えると使いにくくなりますので、先ほど入力規則の元の値に入力した数式を見直します。

元の値を次の通り、修正します。

元の値：　

=OFFSET(INDIRECT("リスト["&B15&"]"),0,0,COUNTA(INDIRECT("リスト["&B15&"]")))

 

つまり、OFFSET関数で各項目のデータ数分だけに絞込んであげます。

今度は空白行を除いた品名だけを選択できるようになりました。

以上で完成です。

 

（３） 補足：一つ目の項目が増えた場合

一つ目の項目が後から増えた場合も、下図の通りリストに追加で入力するとテーブルが拡張され、ドロップダウンリストの選択項目にも反映されます。

 

【Excel】LET関数を使った計算の事例

Microsoft 365の新関数「LET関数」を使った事例をいくつか紹介したいと思います。

（１） LET関数について

LET関数は、中間の計算式や値に名前（変数）を割り当てることができ、その名前を用いて計算式を書くことができます。

＝LET( 変数名[name] , 変数の値[name value] , 変数を使った計算[calculation] )

 

引数は３種類あります。まず、「変数名」に変数の名前をセットし、次にその変数名にたいする「変数の値」をセットします。この「変数の値」は文字列、数値、特定のセル、数式等を指定できます。この「変数名」と「変数の値」の引数は必ずセットになります。最大126の組合せが設定可能です。最後に「変数を使った計算」を書きます。

 

＝LET( テスト,100,テスト+10)

であれば、「テスト」という変数名に 100 を設定し、テスト(100)+10 の計算によって、110 が返ることになります。

ポイントは、「LET関数を使っても使わなくても最終的な計算結果は変わらない。」ということです。上記のような計算例であれば使う必要は全くありません。LET関数を使うメリットは大きく次の２つとなります。

 

１．数式の可読性の向上

長く複雑な数式になる場合、どこで何を計算しているかわかりにくくなることがあります。中間の計算に変数を使うことで可読性が向上し、数式を変更する際等にわかりやすくなります。

２．パフォーマンスの向上

ひとつの数式内に同じ式を複数回記述した場合、Excelは記述した回数分の計算を行います。LET関数で変数に数式を設定した場合、その計算は1回のみとなりますので、処理速度が速くなります。

 

次項より３つの例を説明していきますが、必ずしも上記メリットを享受できた事例とはなっていませんのでご理解ください。

 

【公式のリンク】

support.microsoft.com

 

（２） LET関数の使用例① 経営指標の計算

最初の例は、試算表数値等のデータをもとに経営指標の計算を行う場合に途中経過のまとまりを変数にいれて見易くなるようにしたものです。

下図はＣＣＣ(キャッシュ・コンヴァージョン・サイクル）を算出するため３つの指標を計算したものです。下図①(C14セル）の売上債権回転日数を例に以下説明します。

※LET関数は各変数のセット毎に数式内で改行(Alt+Enter）した方が見易くなりますので以下すべて改行した表記にしています。

f:id:shusoshin:20220324112703g:plain

売上債権回転日数 = 売上債権 ÷ 売上高 × 365(日)※

※1年間で計算する場合

となりますので、各項目を変数名,変数の値に設定していきます。

売上高はC2セルにありますので、「 売上高,C2 」   とします。

売上債権は売掛金+受取手形-前受金で計算しますので「売上債権,C5+C6-C12」とします。

日数はF2セルに入力した値を参照していますので絶対参照で「日数,$F$2」とします。

「日数,365」としても問題ありませんが、期中で計算する場合、例えば第2四半期で計算する場合はF2セルを「=365/2」等とする想定でセル参照としました。

最後の変数を使った計算は設定した変数を用いて「ROUND(売上債権/売上高*日数,0)」とします。これらを合わせたものが以下数式となり、返り値の83（日）が計算されます。

=LET(
売上高,C2,
売上債権,C5+C6-C12,
日数,$F$2,
ROUND(売上債権/売上高*日数,0)
)

 

②棚卸資産回転日数、③仕入債務回転日数 についての説明は省略しますが考え方は上記同様です。

下図は上記と同じ指標を計算しているものですが、計算過程を変えたものです。変数を用いた計算を２つに分割し、最初に売上債権回転率を売上高÷売上債権 で計算した後に、日数÷売上債権回転率 で最終的な売上債権回転日数を計算しています。

f:id:shusoshin:20220324112715g:plain

 

（３） LET関数の使用例② 費用の配賦計算

下図は、A部門で発生した固定費 150（C3セル）をB列の売上高を基準として各部門に配賦した計算シートです。この配賦計算にLET関数を使うことで可読性を高めています。

LET関数の内容は前項と類似していますので詳細説明は省略します。

※D4セルに入力した数式をD5～D9セルにコピーしています。

f:id:shusoshin:20220324112721g:plain

 

（３） LET関数の使用例③ 年間カレンダーデータの作成

最後は、Excelで１年間のカレンダーデータを作る場合です。（3月決算の会社の前提）。LET関数を使っていない項目もあわせて以下個別に説明していきます。

f:id:shusoshin:20220324210525g:plain

①日付（SPILL）

=LET(
基準日,DATEVALUE("2022/4/1"),
日数,365,
SEQUENCE(日数,1,基準日,1)
)
LET関数の変数を２つ設定します。基準日 には始まりの日付をシリアル値で設定します。わかりやすいようにDATEVALUE関数で日付形式の文字列をシリアル値に変換しています。日数 は1年間のカレンダーですので 365 （日）を設定します。変数を使った計算で、連続した数値の一覧を生成するSEQUENCE関数を使って1年間の日付を作ります。SEQUENCE関数の基本構文は、SEQUENCE( 行, 列, 開始, 目盛り )ですので、この行の部分に 日数を入れます。列は1列なので1、開始は2022/4/1なので 基準日を設定、目盛りは1日ずつなので 1 を設定します。

 

②曜日（SPILL)

=SEQUENCE(COUNTA(A3#),1,A3,1)

結果的に①日付と同じ計算結果を入れて、セルの書式設定→表示形式タブ→ユーザ定義を aaa として曜日にしています。ですので①日付と全く同じ数式でも問題ありませんが、A3セルの日付を参照して自動で計算されるようにしました。数式は、SEQUENCE関数のみで、第1引数の行の部分に、COUNTA(A3#) を入れることでA3セルから始まる配列の数が返りますので結果的に日付列の数と合うようになります。第3引数の開始はA3セルの日付の開始日を参照しています。

 

③年度

=VALUETOTEXT(YEAR(EOMONTH(A3,-3)))

EOMONTH関数でA列の日付に対する3ヶ月前の年月日を取得し、YEAR関数でその年のみと取得したものを年度とし、最後にVALUETOTEXTで年の数値を文字列に変換しています。※C3セルに入力した数式を最下行までコピーしています。

日付との関係の例は以下の通り。

　日付 2022/04/01　・・　EOMONTH→ 2022/01/01　 YEAR→ 2022

　日付 2023/03/31　・・　EOMONTH→ 2022/12/31　 YEAR→ 2022

　日付 2023/04/01　・・　EOMONTH→ 2023/01/31　 YEAR→ 2023

 

④四半期

=LET(
月,MONTH(A3),
IFS(
OR(月=4,月=5,月=6),"1Q",
OR(月=7,月=8,月=9),"2Q",
OR(月=10,月=11,月=12),"3Q",
OR(月=1,月=2,月=3),"4Q"
))

A列の日付の月でどの四半期に該当するか判定します。LET関数の変数は1つで、変数名　月 にMONTH関数でA列の日付から月の数値を取得します。変数を使った計算は、IFS関数とOR関数で各四半期毎に判定しています。ここは変数の月を多用していますのでLET関数を使用することによるパフォーマンス向上が効いている気がします。

※IF関数やSWITCH関数でも代用できますが各四半期毎にまとめた記述の方が可読性が高いと思いましたのでIFS関数を使用しました。

 

⑤期

=LET(
四半期,D3,
上期判定,OR(四半期="1Q",四半期="2Q"),
IF(上期判定,"上期","下期")
)

D列 四半期の結果によって、上期であるか下期であるかを判定します。LET関数の変数は２つですが、ここはこれまでと少し違った使い方をしています。まず、最初の変数：四半期はD3の値を設定、次の変数：上期判定のOR関数の中で最初の変数：四半期 を使用しました。四半期が1Q又は2Qであった場合にTRUEが返ります。最後の変数を使った数式は、IF関数で、上期判定 がTRUEであった場合は「上期」をFALSEの場合は「下期」を返すようにしました。

 

以上です。

 

 

 

【Excel関数/VBA】データ手入力時、 既入力済データをドロップダウンリストで選択して入力する

（１） やりたいこと

何でもよいのですが、例えば下図のようなExcelの手入力シートがあります。

部門コードと部門名、取引先コードと取引先名、商品コードと商品名はそれぞれ対になっており、新規の行を入力する際に下図のように過去に入力されたデータをドロップダウンリストから選択して入力できるようにします。ドロップダウンリストは各コードと名称を重複なく、コード順に表示して、選択（確定）した際にコードと名称を分割してそれぞれが各セルに入力されるようにします。

f:id:shusoshin:20211221203217g:plain

 

 

（２） 実践： ドロップダウン用のリストを関数で作成 

ここが一番のポイントとなります。ドロップダウンリスト用に参照する各リストをワークシート上に作るのですが、シートは入力シートと別シートでも構いません。今回はわかりやすいように入力シートの右側に作成します。

下図の通り、A列からH列までが入力データとなっています。部門のリストをK2セル以下に作成します。K1セルの「部門リスト」は文字列ですので無くても構いません。

K2セルに次の数式を入力します。(説明用に色を付けています）

 

=SORT(UNIQUE(INDIRECT("B2:B"&COUNTA(B:B)))&" "&UNIQUE(INDIRECT("C2:C"&COUNTA(C:C))))

 

数式の説明をします。

まず、INDIRECT("B2:B"&COUNTA(B:B))　の部分。

INDIRECT関数は、指定した文字列の参照を返す関数です。引数に”B2:B”という文字列が入っており、これにB列の入力データ数を返す、COUNTA(B:B) を＆でくっつけていますので、結果的に「B2セルからB列で入力がされているセルまで」が返されます。つまり、参照範囲が可変となります。B10セルまで入力されていれば、B2セルからB10セルを返し、B100セルまで入力されていればB2セルからB100までを返します。（途中に空白セルが入った場合はズレますが今回は考慮していません）

なぜ、B列全体を返す B:B とせずにINDIRECT関数を使うかというと、B列全体を参照すると不要な見出し行(1行目）や空白行が含まれてしまうこと（これはFILTER関数で除くことは可）、何より列全体をみにいくことでかなり重くなってしまうことが一番の理由です。

 

次に前述の式をUNIQUE関数で囲います。

UNIQUE(INDIRECT("B2:B"&COUNTA(B:B)))

UNIQUE関数は、一覧または範囲内の一意の一覧を返します。重複するデータが除かれたユニークなデータとなります。

次は単純に、半角スペースでつなぎます。

UNIQUE(INDIRECT("B2:B"&COUNTA(B:B)))&" "&

次のUNIQUE関数以下は前述の式と同じで列をC列に変えただけのものです。

UNIQUE(INDIRECT("B2:B"&COUNTA(B:B)))&" "&UNIQUE(INDIRECT("C2:C"&COUNTA(C:C)))

ここまでＢ、Ｃ列に入力された部門コードと部門名を半角スペースで繋いだ一意のリストとなります。最後にSORT関数で囲ってあげることでコードの昇順に並べ替えられた結果が返されることとなります。

=SORT(UNIQUE(INDIRECT("B2:B"&COUNTA(B:B)))&" "&UNIQUE(INDIRECT("C2:C"&COUNTA(C:C))))

出力結果は下図K2セル以下の通りです。この一覧は、スピルした配列数式として返されます。

f:id:shusoshin:20211221203226g:plain

次にB2セル以下を選択した状態で、データの入力規則（Alt+D+L)を開きます。

設定タブの「入力値の種類」で リスト を選択し、元の値には　=$K$2#　と入力します。この末尾の # がポイントです。スピル配列が入力されたK2セルに対して#を付けることで配列全体をリスト範囲にすることができます。つまり、リスト範囲の縮小・拡大が可変となります。

f:id:shusoshin:20211221203234g:plain

エラーメッセージタブの「無効なデータが入力されたらエラーメッセージを表示する」のチェックは外しておきます。これを外しておかないとリスト範囲以外の値の入力が不可となってしまいます。OKボタンをクリックします。

f:id:shusoshin:20211221203241g:plain

取引先、商品についても前述の記載と同様になりますので説明は省略し、以下画像のみ貼り付けておきます。

f:id:shusoshin:20211221203248g:plain

【補足】新規行に新たなコードと名称の組合せが入力されると、右側のリスト（数式の結果）に追加されます。コード、名称どちらかがブランクの場合はエラー（#N/A）となり、ドロップダウンリストも#N/Aが追加された状態となります。

f:id:shusoshin:20211221203254g:plain

ちなみに、ここまでの状態でドロップダウンリストから選択すると、リストの値がそのままコードのセルに入ってしまいます。（次項の設定が必要です）

f:id:shusoshin:20211221203301g:plain

 

（３） 実践： VBA  Worksheet_Changeイベントでセルの値を分割

最後はVBAで設定します。VBEを起動します。（Alt+F11）

データが入力されたシートを選択してコードを書きます。

f:id:shusoshin:20211221203308g:plain

WorksheetChangeのイベントマクロを用いて設定します。簡単に説明すると、このワークシートの特定のセルの値が変更され場合に、

対象セルの値の半角スペースを区切りとして分割し、分割した値の一つ目(半角スペースより前）を対象セルに入力、分割した値の二つ目（半角スペースより後）を対象セルの右隣のセルに入力する。

マクロを実行します。対象の値に半角スペースが含まれていない場合は入力値がそのまま元のセルに入力されるようにしています。

コードは下図の通りです。

f:id:shusoshin:20211221203314g:plain

上記の設定後、ワークシートに戻りプルダウンリストから選択すると、下図の通り各コードと名称が分割されて入力されます。

f:id:shusoshin:20211221203321g:plain

（４） 終わりに

VBAはまだまだ勉強不足で自信がないのですが、とりあえず書いてみました。

今回はここまでとしますが、更に追加するとしたら、別シートに予め各種コードと名称のマスタを用意しておいて、入力セルにコードのみが入力された場合（半角スペースがない）は、入力値からマスタを検索して合致するものがあれば対応する名称を入力するとするのも良いかもしれません。

 

【PowerQuery】生年月日から基準日時点の年齢を出力するカスタム関数

PowerQueryカスタム関数の練習で、生年月日から基準日(又は現在日付)時点の年齢を出力するものを考えてみました。

（１） 条件

  • ExcelのDATEDIF関数と同じ結果が得られること。
  • 関数に設定する引数は、birthdate(生年月日)、basedate(基準日)の二つ。但し、基準日は省略可能とし、省略した場合は現在日付を設定する。

（２） 作成手順(要約)

　①生年月日から基準日間の日数を取得

　②期間の日付リストを作成

　③年齢の計算(②から生年月日の数をカウント、但しうるう年2/29生まれの場合は

　　2/28でカウント）

 

（３） コード

空のクエリを挿入して、詳細エディターに以下を貼り付けたらそのまま使えると思います。関数名は Age としています。（決まりはありません）

(birthdate  as date, optional basedate as date ) =>
let
   // ①生年月日から基準日間の日数を取得
   durationx = if basedate <> null
                // basedate入力の場合= basedate～birthdateの日数
               then  Duration.Days( DateTime.Date(basedate)-birthdate ) 
                // basedate未入力の場合= 現在日付～birthdateの日数
               else  Duration.Days( DateTime.Date(DateTime.LocalNow())-birthdate ) ,
 

   

// ②期間の日付リストを作成
   dateList =  List.Generate(
                             ()=> [ days=0, dateYmd = birthdate ],
                             each [days]<= durationx,
                             each [days=[days]+1, dateYmd = Date.AddDays([dateYmd],1)],
                             each [dateYmd]
                       　　         　),
       
   // ③年齢の計算(②から生月日の数をカウント、但しうるう年2/29生まれの場合は2/28でカウント）
   Ages =  if Date.Month(birthdate)<>2 and Date.Day(birthdate)<>29 
            
           then List.Count(
               　　List.Select( 
                      List.Select(dateList,each Date.Month(_)=Date.Month(birthdate))
                               ,each Date.Day(_)=Date.Day(birthdate)
                                       )
                                    )-1
                           
            // うるう年の対応
           else List.Count(
                     List.Select(
                       List.Select(dateList,each Date.Month(_)=Date.Month(birthdate))
                              ,each Date.Day(_)= Date.Day(birthdate)-1　// 2/28をカウント
                                     )
                                   )
in
    Ages
 

 

エディターに貼り付けた関数は下図のよう表示されます。

f:id:shusoshin:20211203175748g:plain

（４） 関数の実行

作成した関数が想定通りの結果となるかテストしてみます。

テスト①生年月日：1981/10/28　基準日：2021/4/1　として呼び出しをクリック。

f:id:shusoshin:20211203175955g:plain

39歳となり、正しく計算されました。

f:id:shusoshin:20211203180002g:plain

テスト②生年月日：1981/10/28　基準日：省略　として呼び出しをクリック。

f:id:shusoshin:20211203180008g:plain

40歳となりました。基準日を省略した場合は現在日付(テスト時点は2021/12/2）で計算されますのでこちらもＯＫです。

f:id:shusoshin:20211203180015g:plain

 

（５） 実際の使用例

下図は、ワークシートからエディターに取り込んだデータです。今回は検証のためにDATEDIF関数で計算【 =DATEDIF(生年月日,基準日),"Y") 】した結果をdatedif列に置いています。

f:id:shusoshin:20211203180020g:plain

列の追加タブ → カスタム列 をクリックします。

f:id:shusoshin:20211203180027g:plain

作成したカスタム関数 Age を使用します。ageと入力すると下図のように候補に出てきます。

f:id:shusoshin:20211203180035g:plain

下図の通り数式を入力します。生年月日 列は右の使用できる列でクリックすることで選択できます。今回ワークシート上のDATEDIFの基準日を2021/4/1としましたので合わせて、#date(2021,4,1) とします。省略する場合は、null を入力する必要があります。

f:id:shusoshin:20211203180042g:plain

 

年齢列が追加されました。datedifの結果と一致することが確認できました。最下行の山田四郎の生年月日は2012/2/29 （うるう年）としていますが、これも正しく計算されています。

f:id:shusoshin:20211203180048g:plain

（６） さいごに

下図のようにカスタム関数クエリ上で右クリック→コピーした後に、他のブックのエディターへ貼り付けることが可能です。汎用的に使用するカスタム関数をひとつのブックに集めておくと便利かもしれません。

f:id:shusoshin:20211203180056g:plain

以上です。

【PowerQuery】大量データから特定の項目毎の金額上位○件のみ抽出する

（１） やりたいこと

下図例の通り、元データから特定の項目（勘定科目）毎に金額上位３件の明細を抽出します。今回は金額上位３件としましたが、絞り込む条件は金額下位〇件や金額〇〇円以上などある程度自由設定可能です。また、今回サンプルのレコード数は5万行としましたが、件数が増えても極力処理時間がかからないよう関数の使い方にも気を付けました。（もっと良い方法があるかもしれませんが）

f:id:shusoshin:20211130210531g:plain

（２）コード

詳細エディターのコードは以下の通りです。

元データからキーとなる勘定科目の抽出リストを一旦作成後、List.Accumulate関数で抽出リスト分の抽出処理を行い一つのテーブルとして出力しています。

 

let
    
    ソース = Excel.Workbook(File.Contents("C:\test\testData.XLSX"), null, true),
    Sheet1_Sheet = ソース{[Item="Sheet1",Kind="Sheet"]}[Data],
    昇格されたヘッダー数 = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
 
   
tableBuff = Table.Buffer(昇格されたヘッダー数),
    
    抽出リスト = List.Buffer( List.Sort( List.Distinct( tableBuff[勘定科目])
                             ,Order.Ascending)),
    

    抽出結果 = 
              List.Accumulate(
                 {1.. List.Count(抽出リスト)-1 },  
                       Table.FirstN(
                             Table.Sort(
                                Table.SelectRows(tableBuff,
                                                each [勘定科目]= 抽出リスト{0}
                                                            )
                                       ,{"金額",Order.Descending}
                                              )
                                        ,3),


                (x,y) => x &
                       Table.FirstN(
                             Table.Sort(
                                Table.SelectRows(tableBuff,
                                                each [勘定科目]= 抽出リスト{y}
                                                            )
                                       ,{"金額",Order.Descending}
                                              )
                                       ,3)

                                        )


in
    抽出結果

 

（３）適用したステップ毎の説明

元データのExcelファイルを取り込んで自動で一行目がヘッダーとして昇格されたところから簡単に説明します。（型の変更は省略）

f:id:shusoshin:20211130210540g:plain

前ステップ「昇格されたヘッダー数」テーブルに対してTable.Bufferを入れています。これで処理時間が若干短縮されます。

f:id:shusoshin:20211130210546g:plain

次に抽出リストを作ります。

前ステップ「tableBuff」の勘定科目列に対して、List.Distinct関数で重複を削除し、List.Sort関数で昇順に並び替え、最後にList.Buffer関数で囲っています。ここにList.Bufferを入れることで処理時間が大分短縮されます。

f:id:shusoshin:20211130220550g:plain

最後にList.Accumulate関数です。第一引数にList、第二引数にseed（初項）、第三引数にaccumulator（関数）を設定してList分の繰り返し処理を行います。

以下関数を説明します。（見にくくてすみません）

 

List.Accumulate(
                 {1.. List.Count(抽出リスト)-1 },   // ←Listは1から抽出リストの個数-1
                       Table.FirstN(　　　//←以下seed、tableBuffに対し抽出リストの
                             Table.Sort(　　//　1番目の勘定を降順Sortした結果の上位3明細
                                Table.SelectRows(tableBuff,
                                                each [勘定科目]= 抽出リスト{0}
                                                              )
                                       ,{"金額",Order.Descending}
                                               )
                                         ,3),


                (x,y) => x &                //←以下accumulator：seed(x)+繰り返し処理(y)の
                       Table.FirstN(　　//　結果を出力　
                             Table.Sort(
                                Table.SelectRows(tableBuff,
                                                each [勘定科目]= 抽出リスト{y}
                                                              )
                                       ,{"金額",Order.Descending}
                                               )
                                         ,3)

                             )

 

 

下図は抽出結果の画面です。

f:id:shusoshin:20211130210557g:plain

以下、最終行までスクロールした画面です。

f:id:shusoshin:20211130210604g:plain

以上となります。