タグ別アーカイブ: SUM関数

【M言語に慣れる】_13回_ダイナミックに連続した日付作成

【M言語は難しくない】今回は、M関数を使用して連続した日付を作成する方法を解説します。2つ連続した日付を作成する方法を解説した後に、ダイナミックに日付を作成する方法を解説します。下のGIF画像のように、ある表の最小・最大日と連動させて、連続した日付が入った表を作成する方法を解説します。

上のGIF画像では、画像左の最大値を「2021/04/04」から「2021/04/06」で入力し直しています

その後、「更新処理」を行います

すると、画像右にある表の最終行が「2021/04/04」から「2021/04/06」に拡大します

この回は、過去の記事で解説した波括弧:{}を使用した「連続データを作成する技術」をフルに活用します!

目次

今回のポイント

今回使用するデータ

連続した日付の作成

ダイナミックに連続データを作成

<まとめ>

今回のポイント

今回も前述のように、過去記事で紹介した括弧の使い方が最大のポイントになります

後、これまで紹介してこなかったM関数も使用します

括弧の使い方

過去記事にて、1から10まで連続した数字を空のクエリに作成する方法を解説しました

今回はこの波括弧 / {}(最小..最大)から作成される連続データを使用して、連続した日付を作成します

フィルタリング

日付が入ったテーブルは下の図のようにフィルタをかけて、最小日と最大日は特定できるようにしておきあす

上の図のようにフィルタリングを行うと、下の図のようなテーブルができます

1行目が最小日、2行目が最大日です

こちらは、連続した日付を作成するときに使用します

M関数

今回使用するM関数はこちらです

事前に概要を紹介しておきます

#date

「年、月、日」から日付を作成します

#duration

「日、時間、分、秒」から期間値を作成します

List.Dates

「開始日、リスト個数、増分」から日付のリストを作成します

#dateと#durationと組み合わせて作成します

Number.From

値を数値型式にして返します

今回使用するデータ

 今回は、記事の冒頭でも前述したように、まずは2つ連続した日付を作成する方法を解説します

その後、次の画像のデータを使用します

上の画像の「最小と最大の日付」を変えたら、連動して連続した日付を入れた表が作成されるようにします

連続した日付の作成

 これから2つ作成方法を解説しますが、どちらもまずは空のクエリから作成します

  エクセル画面の上にある「データタブ」➡「データの取得」の順でクリックします

その後、下の画像にて黄色に印をつけた箇所を上から順にクリックします

そして、下の画像にある「空のクエリ」をクリックします

するとエディタが開き、空のクエリが立ち上がります

M関数 / List.Datesで作成する方法

既に今回のポイントで解説済みの内容になります

前述の通り、#dateと#durationを組み合わせて作成します

では、以下の条件で連続データを作成してみます

・開始日 / 2021/05/01

・個数 / 5個

・増分 / 7日

関数は次のように記述します

=List.Dates(#date(2021,5,1),5,#duration(7,0,0,0))

上の関数を数式バーに入力すると、下のGIF画像のように2021年5月1日から始まる日付リストが「7日」おきに「5個」作成されます

波括弧 / {} を使用して作成する方法

前述の通り、波括弧 / {}(最小..最大)から作成された連番を利用して連続した日付を作成します

この方法の場合には、作成した連番を一旦テーブル形式に変換した後、日付形式に直すのがポイントです

ちなみに、エディタ画面からも「空クエリ」は作成できます

まず、下の画像のようにエディタ画面の左で右クリックします

その後に、上の画像の「新しいクエリ」右横にある▶マークをクリックします

すると「空のクエリ」を選択する画面がでます

さて、話を連続データの作成に戻します

まずは、次のように数式バーに入力して連続データを作成します

= {Number.From(#date(2021,4,1))..Number.From(#date(2021,4,30))}

数値型式を返す「Number.From関数」の中は、「#date関数」にて日付を作成しています

上の数式を入力した段階では、ただの「値」の羅列になっています

こちらはあくまで「リスト型式」なので、このままだと日付形式に変換できません

ですので、こちらをテーブルに変換します

すると次の画面のようになり、データ形式を指定できるようになります

こちらの画面で列名と合わせて、データ形式を直します

これで、連続した日付が作成できました

こちらのクエリは、後で表と連動させますので、クエリ名を「連続日付2」として保存します

この連続したデータを作成する方法の「2つ目」として解説した方法は、次の数式を空のクエリの数式バーに入力するところからはじめました

={最小日..最大日}

この数式は表と連動させてダイナミックに連続データを作成する際、応用して活用します

以降は有料ページ/課金サービスへ

複数シートを自動集計~3D参照~

筆者は昔、予算管理の仕事をしていましたが、いつも複数シートの集計に苦しんでいました

月ごとの数字を管理するのに、ひたすらシート名+シート名+シート名のつるべ打ちをしていたのです

この方法だと手間がかかるだけでなく、シートを追加したり、削除した時にミスが起こり易いです

一方、今回の回で紹介する3D参照では、一気に複数シートをSUM関数で集計することができます

シート追加や削除した時にも、追加・削除に連動して集計値が変わりますので、集計ミスも防げます

下のGIFではシート追加前は200だったセルが、追加後は追加したシートの数字20が自動的に追加されています

この3D参照は特に難しい技術は必要なく、あくまで文法の問題だけをクリアすれば誰でも活用できます

それでは3D参照についてのポイントを2つ、以下に解説します

A.文法

以下は複数シートをシート名のつるべ打ちで集計する数式です

シート名とセル名を除くと同じパターンで文字が出てくるのが分かりますでしょうか?

 =’シート名’!セル名+’シート名’!セル名+・・・

今回の3D参照では、+でシート名を足す代わりに「’と’!」の中で、通常のSUM関数と同じ様に”:”で範囲を指定するのです

こんな↓感じです

B.ワイルドカード*

シート名を工夫した上での前提ですが、*カードを使って更に複数シートの集計作業の効率化を行うことができます

例えば、シート名を四半期単位で集計できるように工夫しておきます 

そうすれば、ワイルドカードを使った集計が行えるようになります

例えば、シート名が1Q_4月、1Q_5月、といった具合に並んでいる場合は、1Q以降を*で置き換えます

このワイルドカードを使えば、SUM関数内に1つのシートでも指定すれば、後は必要箇所を*で置き換えるだけですので、とても効率的です

ポイントを2点、文法とワイルドカード*について解説したところで、ぜひ、以下のサンプルファイルをダウンロードして頂き、実際のデータで試してみてください

サンプルファイルは完成と演習の2つのシートがあります

演習のシートでは実際の操作の様子を参照できるように、2つの方法を用意してあります

どちらもフリーアドインを使用した方法です

①Web Video Player

このアドインはエクセルシート上で動画を開くことができます

演習シート上に記載されたURLをWeb Video Playerにコピーして使用してください

➁QRコード

演習シート上のQRコードをスマホで読み込んでください

動画が自動的にスマホ上で開きます

<まとめ>

今回は、複数シートの集計を、シート名+シート名のつるべ打ちではなく、3D参照によりSUM関数で一気に集計する方法を学びました

3D集計は複雑ではないのですが、ひとつでも記述を間違えただけでもエラーになってしまいます

ですから、’や!の位置をひとつひとつ確認して頂きながら作業を進めていけばスムースに作業を完了させることができます!

後は、サンプルファイルを基にして、とにかく手を動かしてみましょう!

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


にほんブログ村