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

コンテキスト遷移~DAXの何故こうなるのか?を解決する~

【このコンテキスト遷移の理解がDAX自体の理解の近道です】

DAXを使用していて「何故こうなるの?」ということに出会うことがあります

「何故こうなるの?」の不思議の一つが「コンテキスト遷移」です

早速、事例で見てみましょう

上の画像の2つの列は「大阪」で値が2300と1200と言う風に違いがあります

実際にメジャーの中身を見ると些細な違いしかありません

・MAXX_NoCal

  MAXX_NoCal = MAXX(‘担当者’,SUMX(‘売上’,’売上'[金額]))

・MAXX_withCal

  MAXX_withCal = MAXX(‘担当者’,CALCULATE(SUMX(‘売上’,’売上'[金額])))

MAXXの第二引数にCALCULATE関数があるかないかだけです

ところが、このCALCULATE関数のあるなしがとても大きいのです

CALCULATE関数があるメジャーは2300⇒1200となり、何らかの形でデータの絞り込み(フィルター)が行われています

今回の記事ではこの絞り込みが有効になる「コンテキスト遷移」の仕組みについて解説していきます

DAXで計算したいこと

今回は2つのテーブルを使用します

1つ目は担当者のテーブル、2つ目は売上テーブルです

売上テーブルは同一担当者が複数登場しています

DAXを使用して、この2つのテーブルを組み合わせて、東京と大阪で最大の売上をあげた担当者の金額を計算します

MAX関数での計算

「東京と大阪で最大の売上・・・」というように「最大」というキーワードが出てくるので、MAX関数を使用するのでは・と思った読者の方も多いと思います

では早速、MAX関数で計算してみましょう

MAX = MAX(‘売上'[金額])

そうすると上の画像のように、思ったよりも小さい金額算出されます

これはそもそも集計の単位が違っています

ですので、まずは担当者テーブルで新しい列を作成し、担当者別に売上金額を算出してみます

新しい列で担当者別の売上を算出した上で、MAX関数を使用してみます

これで意図とした通りの結果が出ました

ポイントは新しい列を作成する時、CALCULATE関数を使用していることです

画像に alt 属性が指定されていません。ファイル名: image-63.png

仮に、上の画像のCALCULATE関数を取り除いたらどうなるでしょうか?

全ての行の合計が算出されてしまいます

つまり、CALCULATE関数を使うことで遷移(全ての行⇒行別)が起こっているのです

MAXX関数/行評価

上のMAX関数の事例ではメジャーを作成するまえに、新しい列を作成しました

今度はMAXX関数を使用して、直接メジャーで担当者ごとの最高金額を大阪と東京で算出したいと思います

MAXX関数では単純に最大値を算出するのではなく、行別に式を評価した上で最大値を算出します

他の言葉で言い換えると、条件に合う「行の中」で最大値を算出してくれます

ちなみに、大阪と東京の合計金額は次の画像のようになっています

では、話を戻してMAXX関数を実際に使用して大阪と東京の担当者別の最大値を算出してみましょう

通常の合計金額と同じ結果になってしまっています

つまり、大阪もしくは東京という「行別」だけが考慮された計算金額になってしまっています

MAXX関数の第一引数はMAXX(’担当者’となっているのに「担当者の行別」は考慮されていません

これは、前に例に挙げた下の画像と同じ現象が起こっています

画像に alt 属性が指定されていません。ファイル名: image-65.png

この時はCALCULATE関数を使用して「行別への遷移」を起こしました

実は、今回も同じようにCALCULATE関数を使用すれば正しく算出されます

CALCULATE関数を使用することで、今回もMAXX関数内で「行別への遷移」を起こしているわけです

これで、DAXは何故こうなるのか?の謎が一つが解けたと思います

メジャー使用による行評価

番外編として、次の事例も紹介したいと思います

上の画像の一番右の式はCALCULATE関数を使用していません

MAXX_NoCal_withMEASURE = MAXX(‘担当者’,[売上合計])

その代わり、第二引数に[売上合計]というメジャーを使用しています

ですが、CALCULATE関数を使用したのと同じ結果がでています

これはメジャーを使うこと自体で、CALCULATE関数を使用するのと同等の効果があることを意味します

たとえメジャーの中身と同じ内容を関数の引数で使用しても、違う結果が出てしまうのはこの為です

<まとめ>

今回はCALCULATE関数を使用して、行別の評価を関数内で受け渡す「コンテキスト遷移」について解説しました

この概念を理解すると、格段にDAXの活用の幅が広がります

ぜひマスターしておきましょう!!

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

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

【パワークエリ・チャレンジ】行ではなく、列方向でフィルター・置き換えを行う方法

【列方向ではフィルターは行えないと思っていませんか?実は3クリックをプラスすれば可能です】

Power Queryエディタ画面にて、空欄だけの列は処理したくない・と思ったことはありませんか?

実は「ヘッダーの上げ下げ」と「行列の入れ替え」を組み合わせれば可能です

上記のGIF画像では、空欄になっている「」「」の列をフィルターしています

ポイント

一番重要なのは2回にわたる「行列の入れ替え」です

行列を入れ替えれば、列方向のデータを行方向に変換できます

フィルター処理をした後は再度、行列を入れ替えます

処理の実際

行列を入れ替える前にまずは「ヘッダー行」を下げておきます

ヘッダー行を下げておくことで、見出しも2回にわたる行列入れ替え操作の対象となります

行列を入れ替えた後にフィルター処理により空欄行を除きます

フィルター処理をした後は、再度行列を入れ替えます

そのままだと、ヘッダーに本来配置されるべき列名が反映されていません

ですので、1行目をヘッダーに上げます

そうすれば完成です

<まとめ>

一見、列方向ではフィルター処理ができないように思えますが「行列入れ替え」処理を組み合わせれば実は簡単です

列フィルターが行えるだけで特殊なデータ整理が行えるようになります

今回紹介したのは空欄のフィルタでしたが、列名の置き換えなども一括で行えます

ぜひ実際に手を動かして試してみてください

最後に、ヘッダーの上げ下げを組み合わせることだけは忘れないようにしましょう!

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

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

SORT関数によるエクセルデータの並び替え~列の並び替えも可

前回は1つの関数に対して複数の出力がある関数(ダイナミック関数)として、FILTER関数を紹介しました

今回は、同じようにダイナミック関数のひとつである、SORT関数を紹介します

エクセルで大量のデータを扱う時に、注意しなくてはいけないことの一つが並べ替えです

間違って並び替えると、列間で食い違いが生じて大規模なミスにつながります。SORT関数では並べ替えのキーが複数あっても、正確に且つ、簡単に並べ替えの処理が行えます

加えて、SORT関数では列の並べ替えが行えるのが大きな利点です!

以前、列フィルター機能を紹介しましたが、従来のエクセルでは列の扱いが困難だったのです

では、今回は、SORT関数の概要だけ紹介します!

1.複数の条件による並び替え

=SORT(並び替え範囲,{列位置,列位置・・・},{並び替え方法,並び替え方法・・・})

並び替え範囲/引数1を指定した後、並び替えの条件になる列の位置/引数2(例:{2,3})を指定し、更に並べ替え方法/引数3を指定します(例:{1,-1})

並べ替え方法/引数3で指定する1、-1とは、<1が昇順>、<-1が降順>の意味にになります

ちなみみに、引数2と引数3は指定の仕方がセットになっています

前述の例で言えば、引数2を{2,3}と指定し、引数3を{1,-1}と指定したので

・引数2の1番目(2)は引数3の1番目(1)とセット

・引数3の2番目(3)は引数3の2番目(-1)とセット

となります

実際の例として、以下の表/テーブル2を並べ替え範囲として、地域(列:2、昇順:1)、売上金額(列:3、降順-1)を並び変えてみます

元データ-テーブル2

数式は以下の通りとなります

SORT関数による並べ替え
SORT関数事例
SORT関数

2.列の並び替え

エクセルは行フィルター機能があるのが便利なのですが、業務によっては在庫表のようなクロス表形式で作業することがあります

以前から、列の並び替え機能もあると便利だとは思っていました

列の並び替えを指定する場合には、 前述の「1.複数の条件による並び替え」 で指定した3つの引数に加えて、4つ目の引数【列方向で並び替えを行う】をTRUEで指定する必要があります

ちなみに、「1.複数の条件による並び替え」にて、列位置で指定した 引数2は行位置で指定します

実際の例として、以下の表の3行目を並べ替え条件に設定し、降順にて列の並べ替えを行います

以下が数式です。4番目の引数をTRUEで指定しています

列並び替え
SORT関数ー列並び替え

但し、列の並び替えができるといっても元データ自体を並び替えできるわけではありません

利用機会を増やすには、別シートで並び変えた内容を編集して表示するなどの工夫が必要になります

ちなみに、FILTER関数と同じく、関数を入力したセル位置に「#」をつけることにより、出力データの再利用は行えます

下のGIFでは、SORT関数が入力された位置(F12)に#をつけることにより、表の右横にSORT関数の出力データを再出力しています

SPILL再利用
#によるデータ再利用

<まとめ>

今回はSORT関数により、データの並べ替えを行う方法を解説しました

SORT関数では、{}の文字を組み合わせることにより、複数の並べ替え条件に1(昇順)、-1(降順)をセットで指定することができます

後、SORT関数では列の並び替えを指定できるという特徴があります

こちらは使い方を研究して見て、ぜひ日常業務の中で活用してみてください

尚、実際のデータでSORT関数の動きを確認したい方は以下のファイルをダウンロードしてください!

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

にほんブログ村

列フィルター

エクセルの便利機能の一つにフィルター機能があります

フィルターをかけさえすれば、誰でも手軽に検索が行えます

でも、列方向にフィルターを行うにはどうしたらいいでしょう?

実は、リストとIF文、そして、列を非表示にするVBAのhiddenプロパティを組み合わせて使えば手軽に実現できます

つまり、フィルターの対象でない列を非表示にしていくのです

以下、列フィルターを行うポイントをかいつまんで解説していきます

まず、フィルターをする対象の文字をリストで網羅して表示できるようにします

ちなみにエクセルの画面はR1C1型式にしていおいた方が便利です

次に、列フィルターを行う対象範囲の最終列を取得しておきましょう

 例:LastC = .Cells(7, Columns.Count).End(xlToLeft).Column

最後に、最終列まで繰り返しを行うFor ~ Next文の中に、If文とHiddenプロパティの”組み合わせ文”を入れます。

リストの文字と一致しない場合には、該当する列を非表示にするようにします

 例:If .Cells(7, i).Value <> str Then

     Columns(i).Hidden = True

   End If

たったこれだけで、最終列がはるか遠くまで膨らんだ表でも手軽に検索が行えます

最後に注意点ですが

フィルターを解除する際には、非表示になっている列は最終列として取得できません。ですから、”セルが空欄でない場合には表示する”などのLoop文で解除するなどの工夫を行いましょう!

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

にほんブログ村