タグ別アーカイブ: excel

テーブル列操作関数

列結合

Table.NestedJoin

文法

キー列を基にして2つのテーブルの内容を結合します

=Table.NestedJoin(テーブル名1,キー,テーブル名2,キー,新しい列名,省略可|結合タイプ)

*結合タイプが省略された場合には、左外部結合が指定される

使用方法

列名をリストとして返す

Table.ColumnNames

文法

ハードコード/自動記録された列名リストをTable.ColumnNamesで置き換えると、列が増えても列名のリストが網羅される

使用前
使用後

実際の使用例

 ➡【豆知識】ピポット解除時の空欄の扱い

列変換(研究中)

Table.TransformColumns

文法

元データ
Table.TransformColumns
列変換後


にほんブログ村

クエリの高速化・関数

キー設定

Table.AddKey

文法

マージを行う際に、主キーを設定してクエリ処理を高速化します

*明細側(多)より集計側(1)で高速化の効果が高いです

Table.AddKey(テーブル名, 主キーにする列, 主キー有無)

使用方法

let
With.Addkey1=Table.AddKey(売上台帳,{“商品コード”},true),
With.Addkey2=Table.AddKey(商品台帳,{“商品コード”},true),
ソース = 売上台帳,
マージされたクエリ数 = Table.NestedJoin(With.Addkey1, {“商品コード”}, With.Addkey2, {“商品コード”}, “商品台帳”, JoinKind.LeftOuter),
#”展開された 商品台帳” = Table.ExpandTableColumn(マージされたクエリ数, “商品台帳”, {“販売単価”}, {“商品台帳.販売単価”}),
挿入された乗算 = Table.AddColumn(#”展開された 商品台帳”, “乗算”, each [商品台帳.販売単価] * [販売個数], Int64.Type),
#”名前が変更された列 ” = Table.RenameColumns(挿入された乗算,{{“乗算”, “販売金額”}})
in
#”名前が変更された列 “

バッファー化

マージ対象のクエリをバッファー化します

文法

=Table.Buffer(最終ステップ名)


にほんブログ村

文字列・操作関数

文字列を操作する為の関数です

文字列・型式への変換

Text.From

文法

値を文字列・型式に変換

*3であれば”3”を返す

=Text.From(値)

使用方法

実際の使用例

 ➡M言語に慣れる_2回目~カスタム列~

検索文字の位置抽出

Text.PositionOf

文法

文字列の中にある特定の文字の最初の出現位置を抽出します

注意点としては、M言語は0ベースなのでカウントが0からはじまります

例えば、1文字目に特定の文字列が見つかった場合には0を返します

この点はご注意ください

*最後の出現位置を抽出することもできます

*文字列が見つからない場合は-1を返します

= Text.PositionOf(“検索対象・文字列”,”検索・文字列”,省略可)

使用方法1
使用方法2

実際の使用例

 ➡M言語に慣れる_5回目~M関数を検索する方法~

検索文字の有無判定

Text.Contains

文法

検索対象の文字列に、検索文字が含まれているかどうかを判定

含まれている場合にはtrueを、そうでない場合にはfalseを返します

=Text.Contains(“検索対象・文字列”,”検索・文字列”)

使用方法

実際の使用例

 ➡【M言語に慣れる】_15回目_TEXT関数/文字列関数

文字列の置換

Text.Replace

文法

文字列内に出現する、指定文字を全て置き換えます

=Text(文字列,置き換える対象の文字列,置き換え後の文字列)

使用方法

実際の使用例

 ➡【M言語に慣れる】_15回目_TEXT関数/文字列関数

数値型式への変換

Number.From

文法

文字型式などの型式から数値型式に変換します

=Number.From(値)


にほんブログ村

お仕事の依頼

 実務を担う現場の方が使い慣れたエクセル。Excellent仕事術は、このエクセルを効果的に活用して、御社の業務改善やお困りごと解決の最適なスキームをご提案/ご提供します!

御社にこんな悩みはございませんでしょうか?

エクセル作業がブラックボックス化しており、膨大な人手がかかっている

分析資料は沢山あるが、業績改善に向けた課題や傾向が全く把握できない

プレゼン資料にどこかインパクトが無い

 Excellet仕事術では、これらの悩みに対して「現場重視」で現場の声を反映させながら解決策を提案します!

 ご相談自体は無料でうけたまわります。エクセルに関することであれば、どんなことでも幅広く「Excellent仕事術のガッツ鶴岡」へご相談ください!

プロトタイプ(試用版)の開発についても低価格で請負ます!

お問合先

Excellent仕事術の得意分野

VBA/マクロ開発

エクセル作業がブラックボックス化する理由は、大きく分けて3つあります。

エクセルシートのスパゲティ化

データのバケツリレー

エクセルファイルの共有

 Excellent仕事術では従来のIT部門では対応できなかったこの3つの問題を、これまで培ってきたマクロ/VBA開発力で解決します。

エクセルシートのスパゲティ化

 エクセルシートのスパゲティ化は、1シートに「データ」「計算」「作業スペース」を詰め込むことで起こります。

 異なる内容が詰め込まれている為、「どこの範囲がどの内容」で、「どの範囲とどの範囲がどのように絡み合っているのか?」が第3者に分かりにくくなります。

 スパゲティ化したエクセルシートは、作成した担当者しか扱えないだけでなく、データが不規則に蓄積されているため有効活用ができず効率化が行えません。

 スパゲティ化したエクセルシートを改善するポイントは、「データ」「計算」「作業スペース」を1シートに混ぜず、それぞれシート毎に集約することです。

 Excellent仕事術ではスパゲティ化したエクセルシートを、3つのステップで劇的に改善します。

・Firstステップ

   ➡ ヒアリング及びシートの中味の分析

・Secondステップ

   ➡ シートを「データ」「計算」「作業スペース」などの目的別に作成

・Thirdステップ

   ➡ 作業スペース・シートでの作業をVBAにより自動化

 改善したエクセルシートでの作業は効率化されているだけでなく、特定の担当者以外でも引継ぎの負担なく作業が行えるようになります。

データのバケツリレー

 エクセルファイルでデータを外部の会社や他部署とやり取りすることは、良く行われていると思います。

 データ活用の目的が外部の会社や他部署と違う為、「エクセルファイルの交換」はファイルやシート間での膨大な「データの転記処理」を発生させることになります。

 実は、VBA/マクロを活用すれば複数のファイルやシートの「データ取込み・変換処理」 を、一括で行うことができます。

一括処理のイメージ

 一括で「データ取込み・変換処理」が行えれば、膨大な転記処理から解放されるだけでなく、取り込んだデータを活用して「周辺業務」の効率化も行えるようになります。

 Excellent仕事術では、現場の声をお聞きして「データ取込み・変換処理」から「周辺業務の改善」まで徹底的にサポート致します。

エクセルの共有

 エクセル1ファイルで大量のデータを扱う場合によく出てくる問題として「共有」の問題があります。

 エクセルは表計算ソフトであり、本来は大量のデータを扱うには不向きです。1シートに蓄積できる行数に制限があり、蓄積データが大量だと動きが重たくなります。

 しかも、 1ファイルを複数の担当者で同時に共有して使用できない為、担当者に待ち時間が発生してしまい、どうしても業務効率が上がらなくなります。

 Microsoft社の製品では、データベースソフトとして「Access」があります。Accessであれば、行数に関係なく大量のデータを扱うことができ、複数の担当者で共有して使うことも可能です。

 ところが、Accessだと現場では行えない業務も中にはあります。特に「大きなクロス表での作業」などは、エクセルのような表計算ソフトを業務用にカスタマイズした表でないと効率があがりません。

 こういったエクセルでないと行えない業務を、複数担当者でエクセルファイルを共有しながら作業したい場合には抜け道があります。

エクセルとAccessをVBA技術により連携して使うという方法です。

 この方法であればエクセルとAccess、互いのメリットを組み合わせて業務を行うことが可能です。

 このエクセルとAccessn連携する仕組みでは、データはAccessに一元管理し、エクセルには都度、必要なデータだけ呼び出して活用します。

 このエクセルとAccessの連携を行う場合にポイントとなるのは「エクセルの役割」と「Accessの役割」の切り分けです。

 Excellent仕事術では、連携の仕組みを構築するだけでなく、業務を理解させて頂いた上で「連携のポイント」についても提案させて頂きます。

事例①

データ分析/統計処理

「数値/分析資料は沢山あるが、業績改善に向けた課題や傾向が全く把握できない」ことの主な原因は、鳥の目全体像を捉えることができていないことが考えられます。

 例えば「前期と比べて注文単価が何故下がったか?」について、注文単価自体の推移を見ても、単位荒すぎて「何故下がった」かはよく分からないはずです。

 逆に注文台帳を見ても、今度は単位細かすぎて「注文単価が前期と比べて何故下がったか?」はよく分からないはずです。

 では下に記述したように、注文を単価別にグループ分けして件数の推移を確認した場合はどうでしょう?

・低単価の注文件数の推移

・中単価の注文件数の推移

・高単価の注文件数の推移

 低単価の注文内容と高単価の注文内容では、注文に含まれる商品の違いや、注文した会員の行動や属性に違いが必ずあるはずです。

 低単価の注文件数の構成が高くなっているのか、もしくは高単価の注文件数の構成が低くなっているのか、を注視すれば「注文単価が下がった原因」はある程度見えてくるはずです。

 このように業績改善に向けた課題や傾向を捉えるには、必ず鳥の目でグループ別の推移を捉えることが必要です。

 このグループ別の推移を捉えるのに、必ずしも「データアナリスト」や「CRMソフト」が必要とは限りません。エクセルの集計機能と統計機能を駆使すれば、エクセルでも対応は可能です。

 Excellent仕事術では、これまで培ってきたエクセル集計技術と統計処理・技術を活用して、御社の数字分析のお手伝いをします。

会員分析

会員分析のポイントは大きく分けて3つあります。

・新規会員の獲得状況 ➡会員数自体の分母を増やせているか?

・新規会員の2回目のリピート状況 ➡新規会員が定着しているか?

・既存会員のリピート状況 ➡優良会員が確実に育成できているか?

 つまり、まずは「新規・既存」と「優良・非優良」の2つの切り口で会員のグループ分け(セグメント)を行った上で、会員のグループ間の移行状況を把握することが必要です。

 Excellent仕事術では上記の2つのグループの切り口から、更に深堀した切り口でグループ分けを行います。

そして、基本の「新規・既存」「優良・非優良」のグループに加えて、更に深堀りしたグループ間の移行率を把握するためのツールを提供します!

事例②

その他の分析

 Excellent仕事術では、発注・在庫分析や予算作成・中期計画のシミュレーションなども得意分野としております。

ぜひ、この分野についての悩み事もExcellent仕事術にお問い合わせしてみてください!

ダッシュボード化

 数字分析を行った内容については、社内で共有し、次の行動につなげていくことも大事なことです。

 Excellent仕事術では社内の効率的な業績報告、情報共有のために一目で現状を掴むことができるダッシュボードの作も行います 。

エクセルダッシュボード

プレゼン・資料作成

 競合他社とのプレゼン競争に勝つためには、「数字の魅せ方」に突破口があります。海外のエクセル活用事例をうまく取り入れた「数字の魅せ方」こだわりましょう。「数字なんてただの数でしょ?」。そんなことはありません。「目で見て、数字の変化がリアルにわかる」効率的な資料作成術を、Excellent仕事術/ガッツ鶴岡がご提供します

-シンプルで誰の目も釘付けにするグラフ関連資料の作成をいたします。

Excellent仕事術/ガッツ鶴岡の強み

業務分析の経験が豊富

-上場企業から創業期のベンチャー企業まで、様々な事業規模での業務分析の経験が豊富

-内部監査も含めた内部統制対策をとおして、業務フロー図等の作成・業務分析の経験多数あり。

数値管理の経験が豊富

-銀行や証券会社等の金融機関への数値説明・交渉の経験が豊富

-EC通販での予算管理や中期計画作成、各種分析などの経験が豊富

最新のエクセル技術

 Excellent仕事術/ガッツ鶴岡は、世界の最先端のエクセル技術を常にキャッチアップしています!

-海外では日本と違い、エクセルの新機能/便利機能をうまく使いこなして業務の効率化に成功している事例が沢山あります

-小難しいVBAコードや複雑な関数は必要ありません。Excellent仕事術/ガッツ鶴岡がお教えする世界最先端のエクセルの新機能を使って、効率よく簡単にエクセル作業ができるようになります。

-エクセルは元々、表計算ソフトです。データベースソフトではありません。しかしパワークエリという新機能を使えば、エクセルをデータベースソフトとしても使いこなせます

 Excellent仕事術のガッツ鶴岡がパワークエリとの連動を作成します/お教えします。

事例①

<商品管理システム/エクセルとAccessの連携>

 業務メンバー各自が業務に即したエクセルのクロス表を使用しながら、エクセルへの入力データはAccessファイルに蓄積して各自で共有する仕組みを構築

<改善前・課題>

★1つのエクセルシートに様々な業務データと入出力表が混在

・商品の特性上、入荷した商品を手動で分割・並べ替えて管理する必要があった

・商品の並べ替え順を各業務で使い回すため、1シート上に様々なデータが混在

データ蓄積型式が不規則になり、VBAなどの自動化手段の活用が不可能

・1ファイルをメンバー全員で共有して使用するため、業務メンバーに待ち時間が発生

<改善後・効果>

★業務データはAccessに一元管理し、エクセルの入出力画面を業務毎に分散

・データはAccessに一元管理し、ADOという技術を使用して、都度、必要なデータのみエクセルファイルに呼び出す仕組みを構築。このADOの技術により、1つ1つの業務に即した画面作成が可能になった。さらに他の業務メンバーがファイルを使っている間に発生していた待ち時間も削減できた。

・Accessに一元管理したデータを、RPAに連携して会計システムへの入力を自動化した。これにより、残業時間が1月あたり200時間減らすことに成功

事例②

EC通販における会員のセグメンテーション>

 会員の1年間の購買履歴とアンケートの集計結果を分析し、会員のセグメンテーションを実施。それまで一律にマーケティング施策を行っていたが、会員のセグメント毎の施策(例:セグメント別の割引率設定など)を実施できるようになった

実績

-商社・畜産部門向け商品管理システムの開発

 ➡エクセルとAccess及びRPAと連携処理

-客先マスタ登録業務用システム開発/エクセル・RPA連携

 ➡エクセルVBAにてRPA入力用データを成型

-EC通販会社での会員分析・マーケティング施策立案

 ➡エクセル分析ツールにて会員の購買履歴を統計処理にて分析

-EC通販会社向け在庫管理システム

 ➡適性発注率をエクセルを使用して分析

ストリートアカデミーで講師もしています

経歴

Excellent仕事術運営者:鶴岡 敦(ガッツ鶴岡)

 新卒で総合商社に入社後、ユニクロに転職。更にEC通販会社に経営企画室長として入社。

 EC通販会社にて膨大なデータ量に苦戦。必要に迫られて自らエクセル学習を重ねるうちに、 海外の活用例などを知るようになり、エクセルの可能性に目覚める

1993年慶應義塾大学商学部卒

2002年USCPA合格

大学在学中にプロボクサーとしてデビュー

現在もボクシングジムで練習を続けている

 

 

テーブル行操作関数

VBAで言えば、メソッドにあたるものです

行削除/先頭からの行数指定

Table.RemoveFirstN

文法

指定した行数分を先頭行から削除

Table.RemoveFirstN(テーブル名, 行数指定) 
Dataテーブル
使用方法

実際の使用例はこちらから

連番追加

Table.AddIndexColumn

文法

テーブルに新たな「連番」の列を追加します

連番の開始番号や増分は調整できます

Table.AddIndexColumn(テーブル名, 列名, 開始番号, 増分) 
画像に alt 属性が指定されていません。ファイル名: image-12.png
Dataテーブル
使用方法1
使用方法2

実際の使用例

 ➡ M言語に慣れる_10回目~グループ毎に連番作成~

先頭行から見出し/ヘッダー作成

Table.PromoteHeaders

文法

先頭行をテーブルの見出し/ヘッダーにします

画像に alt 属性が指定されていません。ファイル名: image-12.png
Dataテーブル
使用例

実際の使用例

 ➡不規則に散らばっているデータを一括取得~M関数~

行フィルター

Table.SelectRows

文法

抽出条件と一致する行を選択します

Table.SelectRows(テーブル名, 抽出条件)
画像に alt 属性が指定されていません。ファイル名: image-12.png
Dataテーブル
使用方法

実際の使用例

 ➡M言語に慣れる_8回目~EACHを使いこなしてM関数作成~

 ➡POWER QUERYエディタ上で複数条件付き合計処理~M関数使用


にほんブログ村

M言語に慣れる_3回目~ダイナミックフィルタリング~

M言語は難しくない!】今回は、数式バーを編集して「ダイナミックフィルタリング」を行ってみましょう!。ここで言う「ダイナミックフィルタリング」とはフィルタリングの値を固定せず、元データの追加や修正に応じてフィルタリングの値を自動で変動させていくものです。

例えば、受注金額・全体の「最高値」などは毎月、変動していくはずです

 ダイナミックフィルタリングでは、例えば「全体最高値」の半額未満だった担当者のリストを、毎月自動で抽出を行えるようにします

 今回のダイナミックフィルタリングを行うにあたっては、M言語を一から作成するといったことはしません

 あくまで、Power Queryエディタ(以降エディタ)にて、既に数式バーに記録されているM言語を修正するだけです

マクロ作成で言えば、記録マクロ修正するような内容です

記録されているM言語を修正するだけでも、かなりの内容が行えることを肌で感じて頂ければ幸いです!

目次

今回のポイント

今回使用するデータと行いたいこと

演習1/受注金額1位の担当者を抽出

演習2/最高値の半分未満の担当者抽出

<まとめ>

今回のポイント

ステップ名変更

実は、エディタのステップ名は自分好みに修正していけます

今回の内容とは直接関係ないですが、ステップ名を変更していけると、後々、M言語の管理がとても楽になります

参照ステップ

 前々回、ステップをスキップして参照することで「参照ステップ」を作成しました

前々回は平均値を参照しましたが、今回は「最高値」を参照します

 クエリを更新する毎に、参照ステップの「最高値」も更新してフィルタリングの値とします

数式バーへの参照ステップ組み入れ

一度、数式バーにてダミー値でフィルタリングを行います

ダミーで設定をした箇所に、前述の参照ステップを組み入れます

今回使用するデータと行いたいこと

今回は次のデータを使用します

演習データ

担当者別に受注金額を管理する表です

 こちらの表の中味が変更になっても、「最高受注金額」の担当者や「最高受注金額の半分未満」の担当者のリストを自動で抽出できるようにします

演習の中で、こちらの表にデータを新たに追加したりします

今回のポイントと使用するデータを確認したところで、本格的な演習に入ります!

演習1/受注金額1位の担当者を抽出

ステップ名を変更

まず、表をテーブル化してエディタを開きます

表の上で次の画像のように右クリックし、「テーブルまたは範囲からデータを取得」をクリックします

エディタが開いたら、「今回のポイント」で前述したステップ名の変更を行います

下の画像の「変更された型」を「DATA」に変更しましょう

クエリ名を変更するのと同じ要領でステップ名も変更できます

「変更された型」の上でF2キーを押せば、下のGIF画像のようにステップ名を変更することができます

ステップ名を変更

なお、前ステップを参照する時に「#”前ステップ名”」と自動的に表示される時があります

この点は後述します

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

M言語に慣れる_5回目~M関数を検索する方法~

【M言語は難しくない!】今回は使用方法に合ったM関数を、Power Queryエディタ(以降エディタ)上で検索する方法を紹介します

エクセル関数では、エクセルシート上で直接、関数を検索できます

実は、Power Queryでも同じような機能があります

エクセル関数と同じ様に検索した後に、そのまま使い回すことはできませんが、この機能を覚えておくととても便利です

目次

準備作業 / 空のクエリ作成

M関数一覧を取得

一覧をテーブル化

実際に検索してみる

<まとめ>

準備作業 / 空のクエリ作成

まずは準備として、空のクエリを作成します

「データタブ」➡「データの取得」の順でクリックします

次に「その他のデータソース」を選択し、「空のクエリ」をクリックします

M関数一覧を取得

空のクエリの数式バー上で=#sharedを入力し、エンターを押してください

すると、M関数の一覧が表示されます

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

UNIQUE関数~重複排除~

  今回は、1つのセルに関数を入力するだけで重複排除したリストが取得できる、「UNIIQUE関数」について解説します

尚、今回の解説で使用するデータは次の画像になります

上の画像のデータは、既にテーブル化してあります

ですので、関数の引数(中味)の指定については、過去の記事で解説した「テーブルとの連携」により指定する方法で解説します

詳細を知りたい方は過去の記事をご確認お願いします

1つの列での重複排除

このUNIQUE関数の書き方は至ってシンプルです

対象となるデータ範囲を指定するだけです

=UNIQUE(対象範囲)

今回はテーブルと連携するので、下のGIF画像のように対象範囲を指定します

上のGIF画像では、対象範囲を指定する際に、年度の列にてが出ていることにご注目ください

複数の列での重複排除

UNIQUE関数で複数の列を対象範囲に指定すると、指定された列の組み合わせの中から、重複しない組み合わせを出力します

行方向での重複排除

UQNIQUE関数の2つの引数を「TRUE」と指定すると、行方向での重複排除を行うことができます

1回登場する組み合わせを抽出

下の図のようにUNIQUE関数の3番目の引数を「TRUE」と指定した場合には、1回登場する組み合わせのみが抽出されます

=UNIQUE(Data[[年度]:[地域]],,TRUE)

下の図のように、画像左側の「TRUE」を指定していない場合と比較してみましょう

3番目の引数をTRUEと指定した右側では、1回登場している組み合わせのみが抽出されているので、左側より行数が少なくなっています

例えば、元のデータの黄色に印を付けた箇所は複数登場しているので、UNIQUE関数で抽出されていないのです

テーブルと連携しないケース

テーブルと連携しないケースでは、下の図のように対象範囲空欄が入る場合があります

この場合には、UNIQUE関数の出力に0が含まれます

この場合には、FILTER関数を使用して「空欄以外」の値を抽出するようにする方法があります

下の図のように、FILTER関数の抽出条件を「<>””」とします

こうすれば、0は出力されません

まとめ

今回は、UNIQUE関数により重複排除したリストを出力する方法を解説しました

省略可能ですが、2番目の引数をTRUEとすれば行方向での重複排除も行えます

最後にFILTER関数と連携して「0」を出力しないようにする方法も解説しました

 今回は他の新関数との組み合わせについては1パターンしか解説しませんでしたが、このUNIQUE関数の醍醐味は他の関数との組み合わせです

次回からは他関数との組み合わせについて解説を行って行きます!

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

にほんブログ村

M言語に慣れる_9回目~複数シートをコード1行で結合~

【M言語は難しくない】今回は、空のクエリM関数を1つ入力するだけで複数シートを一気に結合できるようにします!今回紹介するM関数を使いこなせば、面倒なシート間のデータのバケツリレーから抜け出すことができます

 Power Queryで複数シートを結合する方法として一般的に紹介されている方法では、次の画像の処理「クエリの追加」が必ず入ると思います

今回は、「クエリの追加」は使わず空クエリの数式バーに「=Excel.CurrentWorkbook()」を入力します

1つM関数を入力するだけで、ファイルに含まれるシート内のテーブルが全てエディタ内に反映されます

これで、シート間のバケツリレーの作業からは抜け出すことができます!

但し注意点もありますので、そちらもあわせてM関数/Excel.CurrenWorkBookの使用方法を解説します

目次

今回のポイント

今回使用するデータ

準備作業/空のクエリを作成

M関数の入力/Excel.CurrentWorkbook

読み込み処理

データの循環対策

シート追加テスト

<まとめ>

今回のポイント

今回はM関数を入力した後に、1点だけ注意点があります

それは、循環問題です

複数シートを1つにまとめた内容/クエリが、M関数に反映されてしまいます

ですので、クエリを更新すると読み込み行数が倍になります

対策として、適用したステップに1つのステップを追加します

今回使用するデータ

今回使用するデータには、シートが3つ含まれています

1シートが1か月分の出荷データになっています

各シートの内容は、事前に「シート名/Data_y年m月」をテーブル名にしてテーブル化してあります

次のファイルが今回、実際に使用するサンプルデータです

準備作業/空のクエリを作成

まずは空のクエリから作成します

「データタブ」➡「データの取得」の順でクリックします

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

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

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

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

【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つ目」として解説した方法は、次の数式を空のクエリの数式バーに入力するところからはじめました

={最小日..最大日}

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

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