タグ別アーカイブ: フィルタ

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

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

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

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

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

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

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

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

目次

今回のポイント

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

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

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

<まとめ>

今回のポイント

ステップ名変更

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

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

参照ステップ

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

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

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

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

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

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

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

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

演習データ

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

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

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

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

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

ステップ名を変更

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

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

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

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

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

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

ステップ名を変更

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

この点は後述します

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

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

={最小日..最大日}

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

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

Office365小技集~右クリックで一発処理~

 今回は、Office365で「右クリック」を使用して、エクセル処理を省力化する技を紹介します

1.Power Query

表の上で右クリックすると、次の画像の箇所がクリックできるようになります

表をテーブル化していない場合には、次の画像の画面でOKボタンを押せば、そのままPower Queryエディタが立ち上がります

もちろん、表をテーブル化してあればそのままPower Queryエディタが開きます

これでPower Queryがより簡単に処理できるようになりました!

2.テーブル

1.と同じ様に右クリックすれば、集計行を出したり範囲に変換することができます

これでわざわざ画面上のタブを操作しなくて済みます

3.フィルター

こちらもとても便利です

わざわざ画面上でフィルターをかけた後に、フィルターをかける値を選択しなくて済みます

今回は以上です

右クリックを活用して、エクセルの単純作業はぜひ爆速化していきましょう!

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


にほんブログ村