カテゴリー別アーカイブ: 裏技

DAX使用時に罠に陥り易いところ~Power Pivot、Power BI~

今回の記事では、DAXを使用していて罠に陥り易いところを解説します

DAXは登場してまだ日が浅く、あまり事例が溜まっていないこともあるので、まだDAXを始めたばかりの方は

「あれ、なんでうまくいかないんだろう・・・」

ということも多いと思います

特にリレーション関連では不可解に感じることもあるかと思います

この記事では、自身の経験も含めて「思わぬところでハマった」箇所を解説していきます

ポイントは次の5つとなります

・データ変更時の更新処理

・DAX式の()の数の確認

・事業年度の月次の並び

・1対多の関係性の確認

・Power Query内のデータ形式の確認

更新漏れ

これは物凄い単純なのですが、急いでいる時などにハマる方もいらっしゃると思いますので紹介します

例えば、上の画像のように空欄があるデータがあり、後から元データの空欄を埋めたとします(✕✕物流

ところが「Power Pivot for Excel」の画面には反映していないので「なぜだ?」というケースがありました

元のデータを変えた場合には下の画像の黄色の箇所「最新の情報に更新」にて更新処理を必ず行いましょう。すぐに元データの変更が反映されます

【)】の抜け

意外とエラーの原因が分からないのが左括弧/に対応する右括弧/)の抜けです

特に変数を使うケースなどは分かりにくいです

赤字の波線が出たら、落ち着いて「赤字の波線」がではじめたところのの組み合わせを観察しましょう

下の画像では黄色の箇所でがぬけています

事業年度の月次表示

日本では多くの事業年度は4月から翌年の3月までです

その際、IF式を使って事業年度を調整する方も多いと思います

ところが、月の表示を組み合わせると下の画像のように「1~3月」が思うような位置に並びません

上の画像の場合で言えば、本来は2021年度は4月から始まり、1~3月は最後に回って欲しいのです

この場合は、事業月を「yyyy年MM月」を表示する月を作成した方が早いです

下の画像では「FORMAT」を使用して表示を「yyyy年MM月」に変えています

これで月の表示も狙い通りになります

リレーションが効いていない

DAXを使う際に、厄介なことの一つがリレーションが効いてない時の対処です

リレーションが効いていないと、全て同じ数字になるなどの状態になります

この場合は下の画像のようにアラームは出ています

この様なときは丁寧にリレーションの状況を確認して、の漏れを見つけましょう

リレーションの線が破線になるケース

しっかり抜け漏れなくリレーションを作成しても、リレーションの線が破線になり、リレーションが有効にならないケースがあります

中にはうっかり「非アクティブ化」をクリックしてしまい、波線になっていることもあるかと思います

これはDAXを始めた頃は解決方法が分かりにくいかと思います

後、原因が分かりにくいのが次の画像のケースです

上の画像では「▶」の向きがになっており、本来「1」は予定表の方についているべきなのです

この矢印の逆向きの話しについては「1対多」の関係を考慮する必要があります

特に管理画面ではなく、2つのテーブル間でクリック処理によりリレーションを作成する場合に注意が必要です

ちなみに上の画像内のは「多」という意味です

下の画像は注文データテーブルとカレンダーテーブル(グレーの線)の組み合わせになっています

カレンダーテーブルには「2019/04/02」はつしかありません

一方、注文データテーブルには「2019/04/02」が複数存在します

ですのでカレンダーテーブルの「2019/04/02」を「2019/04/02」が多数存在する注文データテーブルの方向に紐づける必要があります

上記の説明が、少し分かりにくいと感じた方は次のケースで考えてみてください

カレンダーテーブルに多数「2019/04/02」が存在し、紐づけようがなくなってしまう場合です

上の画像のように本来は1になっているべきカレンダーテーブル側に「2019/04/02」が複数ある場合、カレンダーテーブル内のABのどちらを注文テーブル側の「2019/04/02」と紐づけたらいいか分からないですよね

つまり、紐づける方向は多ではなくからになるということです

では、具体的にリレーションの線が破線になっているのを修正したいと思います

1になるカレンダーテーブルの予定表から注文データに線を引っ張ります

こうすることで正しいリレーションを作成することができます

正しくリレーションが作成できている場合にはリレーション管理画面は次のようになっています

1の方がにありますので、この点もご注意ください

リレーションの方向が一致しているのにリレーションが有効にならないケース

Power Queryで作成したクエリをデータモデルに追加した場合には、クエリ内のデータ形式が一致せずに2つのテーブルが紐付かず、リレーションが効かないケースがあります

このケースが一番、罠にハマるパターンの中で原因に気づけないケースです

但し、データ形式を変えればあっさりリレーションは有効になります

これはAccessと違い、テーブルを構築する際にPower PivotやPower Queryの場合にはデータ形式を設定する必要がないことと深く関係があります

下の画像は、Accessの画面ですがテーブルを作成する際の画面です

こちらは、Power Queryエディタの画面です

Power Queryではデータ形式は変更できますが、基本的には自動設定です

特に日付データの場合は型式が「テキスト」でも「日付」でも見た目が一緒なので、既存のデータモデルとの型式の違いに気づきにくいです

ですので、特にカレンダーテーブルとのリレーションを作成する時にはPower Query側の日付形式にはご注意ください

<まとめ>

今回はPower PivotやPower BIでDAXを使用する時にハマりやすいものを、小さいものから大きい物まで幅広く紹介しました

ポイントをまとめると次のようになります

・データ変更時の更新処理

・DAX式の()の数の確認

・事業年度の月次の並び

・1対多の関係性の確認

・Power Query内のデータ形式の確認

ぜひ上の5つを意識しながら、DAXを活用してみてください

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

にほんブログ村

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

グラフデータを非表示にしてもグラフは表示する方法

グラフを人に見せる際にデータを見せるのは格好悪いと思い、データを非表示にしたらグラフの中味も消えてしまった経験はないですか?

グラフは本来、表示されているデータを元にして作成されるので、元データを非表示にするとグラフの中味は消えてしまいます

ところが、エクセルは便利にできているので、回避する方法がちゃんとあるのです

非表示および空白のセル

グラフデータが消えてしまったグラフ上で右クリックをすると、「データの選択」が見えます

こちらをクリックすると「非表示および空白のセル」という表示が下に見えます

こちらをクリックすると別なダイアログボックスが表示されます

ダイアログボックスの内容は下の画像のようになっています

こちらで「非表示の行と列のデータを表示する」にチェックを入れてください

これで元通りに表示されます

今回は短いですが以上です

最後までお付き合い頂きありがとうございました

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

にほんブログ村

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

2つのシート/ファイルを詳細に比較~INQUIRE~

 エクセル作業で「辛抱」を要する作業の一つにシート間の検証があります。今回、紹介するアドインであればワンクリックで比較が詳細に行えます!

エクセルで作業をしていると、シート間でどこが変わっているのか?そしてどのように変わっているのか?この検証作業が重要になることがあります

数式を膨大に入力して検証しても、数式自体がミスしていることもありますので、この検証作業はかなり神経を使います

実は、無料のアドインでこの検証作業は一瞬で終わってしまいます

エクセルの進化は本当にすごいですね

今回はこの検証作業を一瞬で行うアドイン「INQUIRE」を紹介します

アドインのインストール

今回のアドインは「挿入タブ」ではなく「ファイルタブ」からインストールします

ファイルタブをクリックし、次に表示された「その他」の箇所からオプションボタンをクリックします

オプションボタンをクリックした後に開いた画面では、「アドイン」をクリックしてください

「COMアドイン」からインストールしますので、下の画像の「COMアドイン」をクリックした後に「設定」をクリックします

その後、「Inquire」にチェックを入れます

すると、エクセルの画面上に「検査」というタブが表示されます

検査の実施

準備

まずは比較を行うファイルを開いておきましょう

検査アドインの起動

検査タブから「ファイルの比較」をクリックします

すると、下の画像のような画面が開きます

比較するファイルの内容を確認した後、「比較」ボタンをクリックします

しばらくすると下の画像のような画面が開きます

こちらが検査結果の画面になります

検査内容の確認

検査結果の画面は左上が修正前、右上が修正後になっています

そして、色が付いているところが変更が行われている箇所になります

色は緑とパープルに分かれていますが、色の違いにきちんと意味があり、変更方法と紐づいています

画面右下を見ると、変更方法を確認できます

緑はセル入力、パープルは数式になっています

つまり、変更前のファイルからセルを参照した後、一部のセルは入力により数値を変えたという事まで分かるのです

変更した内容については、画面下の真ん中でより詳細に分かるようになっています

検査内容のエクスポート

検査内容については、下の画像の箇所からエクセルファイルにエクスポートできます

下の画像はエクスポートした内容になります

<まとめ>

今回は2つのファイルを比較するアドインを紹介しました

本来であれば、膨大な作業が必要な内容ですので、このアドインを知っているだけで事務作業は格段に効率化されます

このブログでは他にも便利な無料アドインを紹介していますので、興味のある方はぜひ記事をご覧になってみてください

なお、今回のアドインを使えるバージョンは次の通りとなっております(出典:Microsoft社HP)


にほんブログ村

予実対比のグラフを見やすくする

今回は積み上げ棒グラフを使用した予実対比のグラフを見やすくする「ちょっとした工夫」を紹介します

通常だと、下の画像のように棒グラフを実績と予算で並べる形になります

今回は予算に対して実績が「超過」と「未達」の場合で表示する色を変えます

とにかく棒グラフの表示数を減らしてシンプルに魅せます

ポイント

超過の場合(実績-予算がプラス)と未達(実績ー予算がマイナス)で表示を分けるのと、折れ線グラフの線を透明にしてマーカー(予算)だけ表示するのがポイントです

超過の場合

実績が予算を上回っている場合は、一番下のグラフの表示は「予算額」です

ちなみに、下の画像のグレーの横棒は予算額です

予算額の横棒の下が予算額です

未達の場合

実績が予算を下回っている場合は、一番下のグラフの表示は「実績」になり、赤の部分が「予算-実績」になります

折れ線グラフ

予算額を示す折れ線グラフの線は非表示(透明)にします

データの作成

グラフの表示は見た目は紺色と「薄い青」か「赤」かの2つだけですが、4つの項目をデータの元とします

画像上で一番左にある項目の「予算」はそのままだけですが、基本、超過、未達にはIF関数を入れます

基本

実績が予算を上回っていたら「予算のセル」、そうでない場合は「実績のセル」を表示できるようにIF関数を入力します

超過

実績が予算を上回っていたら「超過額」を、そうでなければ空欄を表示するようにします

未達

実績が予算を下回っていたら「未達額」を、そうでなければ空欄を表示するようにします

グラフ作成

上で作成したデータを基にして積上げ棒グラフを作成すると下の画像の状態になります

この一番下/青の箇所はグラフ種類を変更します

次に開いた画面で「折れ線グラフ」にします

折れ線グラフ線は「線なし」にします

マーカーについては、下の画像のように種類とサイズを調整します

後は、グラフの色を調整して終了です

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

<まとめ>

今回は、積上げ棒グラフを工夫して予実対比を見やすくする方法を解説しました

今回紹介したグラフは「グラフのシンプル」さを追及した一つの形であり、他にも調整方法があるので、好みに合わせて工夫していくと良いと思います

大事なのはシンプルに表現することです

シンプルさを追及することは忘れないようにしましょう!


にほんブログ村

ドロップダウンリスト活用による入力の省力化

以前、エクセルのレッスンをしている時に、ある企業でマネージャーをしている方から相談を受けました

「部下の行動を記録して報告する必要があるが、入力を省力化したい」という内容でした

文章の入力を省力化する場合には、固定文章をマスタ化した上での「ドロップダウンリスト」の活用をお勧めします

エクセルシートに文章を記入するのではなく、予めリストを作成しておいた「短い文章」の中から「選択」をすることにより、文章の記入業務を効率化します

*報告は自由フォーマットという前提での解説です

マスタ化

例えば、今回のマネージャーの方のケースで言えば、こんな風に入力していました

このケースの場合は、担当者と遅刻などの特筆事項は「固定的」な内容なので、下の画像のように別表を作成して管理しておきましょう!

ドロップダウンリスト作成

まずはデータタブから「データの入力規則」をクリックしましょう!

次に、入力値の種類から「リスト」を選択しましょう

上の画像のリストを選択した後は、「リストにする範囲/マスタ」を指定しましょう

これで、ドロップダウンリストが完成です

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

作成したドロップダウンリストはコピーもできます!

<まとめ>

今回はドロップダウンリストの作成により、文章の記入業務を効率化する方法を解説しました

ドロップダウンリストは簡単な操作で作成でき、コピーをすることもできます

ドロップダウンリストは入力時やエラー発生時のメッセージも設定できるので、そちらの機能もあわせて活用していくことをお勧めします


にほんブログ村

名前機能について

皆さんは、学生時代に友達にあだ名とかつけていませんでしたでしょうか?

例えば、佐藤という苗字の友人が複数いたら、呼ぶのに便利ですよね?

このあだ名はエクセルを使用する際にも結構、有効活用できます

エクセルのセミナーを行っていて、「あれ?あまり知られていないな・・」と感じたのが名前機能です

これは結構、便利なのでぜひ有効活用して頂きたいと思います

この名前機能を有効活用すると、次のGIF画像のようなこともできてしまいます!

名前機能とは何?

文字通り、セルや範囲に名前、すなわちあだ名をつけてあげる機能です

他人が作成したエクセルファイルは使いにくいですよね

実は自分自身でも「あれってこの数式ってどうなってたっけ?」と後から思うこともあります

常に重要なセルの箇所や範囲に名前をつけて管理できるようにしておけば、エクセルを使いやすくできます

ちなみに、付けた名前は「数式タブ」の「名前の管理」で一覧化されます

つけた名前の一覧は名前ボックスからも確認できます

名前の付け方

何種類か方法はありますが、今回は一番シンプルな方法だけ解説します

下の画像のように、「名前の管理」の右にある名前の定義から①名前と②参照範囲を設定します

上の画像の場合は、下の画像のペンギンを参照範囲として指定しています

もし、後から「ペンギン」とは何のことか忘れてしまった場合は、名前ボックスからペンギンの場所に移動することができます

関数内での名前の使用

名前は関数の中でも使用することができます

例えば、下の画像の表に「検索範囲」と名前を付けておきます

この検索用の範囲をVLOOKUP関数に使うのですが、名前で代用することが可能です

<まとめ>

短い内容でしたが、いかがでしたでしょうか?

名前機能についてのイメージは明確になりましたでしょうか?

この名前機能は様々な応用が効きます

このブログでも2つ事例を紹介しています

興味のある方はぜひそちらの記事もご参照ください

1.VLOOKUP関数の参照範囲を切り替える 記事

下のGIF画像では、VLOOKUP関数の2つの参照範囲に「レディース」「メンズ」と名前をつけています。

VLOOKUP関数内のINDIRECT関数が都度、2つの名前を切り替えながら参照しているイメージになります

2.表示画像の切り替え記事

スライサーなどで表示を切り替える際に、表示画像も変えて「何に切り替えたのか?」を分かり易くします

こちらも1と同じくINDIRECT関数を使用します


にほんブログ村

ピボットテーブルに引いた罫線を、更新しても維持する方法

 作成したピボットテーブルに、罫線を引いても更新すると消えてしまう・・・、または表を変更すると消えてしまう・・・こんな悩みはないですか?この悩みには2つの意外な抜け道があります!

1つ目は、ピボットテーブルオプションの設定を変える方法

2つ目は、ピボットテーブルのデザイン自体を新規に設定する方法です

2つ目の方法は簡単なのですが、1点だけ注意点があります

目次

ピボットテーブルオプションの設定を変更

デザインを新規設定

<まとめ>

ピボットテーブルオプションの設定を変更

ピボットテーブル上で右クリックをすると、下の画像の黄色い印を付けた箇所のように「ピボットテーブル オプション」のマークが出てきます

こちらをクリックすると、下の画像のように「更新時にセル書式を保持する」が表示されます

こちらにチェックを入れると、更新しただけでは罫線は消えなくなります

但し、これだけだとピボットテーブルの表自体を変更すると罫線は消えてしまいます

デザインを新規設定

ピボットテーブルのデザイン自体を「罫線」を引くように設定すると、表自体を変更しても罫線は消えません

このデザイン自体の設定を行うには、まずデザインタブ内で以下の画像の「黄色の箇所」をクリックしてください

すると様々なデザインが選択できるようになります

どれか自分のイメージに近いデザインを選択し、右クリックしましょう!

すると上の画像のように「複製」という文字が表示されますので、こちらをクリックします

これで、選択したデザインをテンプレートにして、自分なりのデザインが設定できるようになります

上の画像のように「テーブル全体」を選択したまま、画像下にある「書式」を選択すると罫線を引く画面が選択できるようになります

上の画面にて、罫線を引いた後は、元の画面に戻りますのでOKボタンを押します

通常であれば、これでピボットテーブルに罫線が引かれていそうですが、ここからがトリッキーです

上のGIF画像のようにデザインタブ内に戻り、設定したデザインを選択しないと罫線は反映されません

こちらは注意が必要です

<まとめ>

今回は、ピボットテーブルに引いた罫線を維持する方法を解説しました

こちらは元々は、ストアカの講座内で生徒様から質問を受けた内容です

 私のストアカのレッスンでは、ピボットテーブルをそのままレポートに使えるデザインにするレッスンも行っていましたが、「罫線」についてはあまり深く考えたことがありませんでした

やはり、

教えることとは、教えて頂く事

とても勉強になります!


にほんブログ村

図形の意外な使い方を2つ教えます!

 皆さんは、図形の中に文字列を入れる時はありますか?その作業、資料作成の度に毎回行っているのであれば手間を削減できる可能性があります

後、図形には別シートや別セルへのリンクも埋め込むことができます

図形にリンクを埋め込むことができるとクリックが行いやすくなり、とても便利です

文字列を図形に入れる

直接は、図形の中に数式は入れることができません

但し、数式バーを活用すれば「数式」を図形の中に入れることができます

こうしておけば、資料を作成する度に図形の中の文字を変えなくても、図形の中の文字列は変わります

注意点としては、数式バーの中であっても関数は使えません

ですので、文字列を組み合わせたり、桁区切りをする場合は他のセルを経由する工夫が必要です

上の画像のようにB2セルに関数を組み込んで置き、図形はB2セルを参照させればOKです

リンクを埋め込む

図形の上で右クリックをすると「リンク」という文字が表示されると思います

こちらから、他シートやセルへのリンクが設定できます

上の画像のリンクをクリックした後に表示されるボックスでは下に「リンクを挿入」という文字が見えますので、こちらをクリックしてください

すると、下の画像の画面が開きます

こちらでリンク先の「シート」「セル」を指定します

まずデフォルトでは、左の「ファイル、Webページ」が選択されていますので、こちらを下の「このドキュメント内」に選択し直してください

シートの指定についてですが、一見では分かりにくいですが、「セル範囲」の下に各シート(テーブル演習・・・)が表示されています

こちらでシートを選択します

次に、「セル参照を入力してください」の下にセル名の指定を入れてください

<まとめ>

教えることは教えてもらうこと!

最近、ストアカで講師をする機会が増えて感じるのはこのことです

「上の図形の工夫は意外と知られていない」という事は生徒さんとの話で気づきました

この図形の工夫をするだけでも、皆さんの業務は随分変わってくると思います

小さなことでも皆さんのお役に立てたら幸いです

今後も、新たな気づきを得たら積極的に発信していきたいと思います


にほんブログ村

INDIRECT関数を使いこなす!

5百あるエクセル関数の中でも、「ややこしい」が「使い勝手があり」「個性が強い」のがINDIRECT関数です

「INDIRECT関数って何ができるの?」と聞かれたら、少し返答に困ってしまいます

でも、確実に言えるのはこの関数は使えるということです!

今回は、INDIRECT関数の世界を覗いてみましょう!

基本形

今、下の画像のようにD2セルに「おやすみなさい」と入力されているとします

そして、B2セルには「D2」と「おやすみなさい」の文字が入力されているセルの名称が入力されています

上のGIF画像のようにINDIRECT関数の引数としてB2セルを指定すると、D2セルの内容が出力されます

名前参照

今度は、下の画像のようにD2セルに「挨拶」と名前を付けます

B3セルには「挨拶」の文字が既に入力されています

では、基本形と同じ様にようにINDIRECT関数の引数にB3セルを指定します

繰返しになりますが、「おはようございます」の挨拶が入力されたD2セルは「挨拶」という名前が設定されています

すると、下の画像のようにD2セルの内容が基本形と同じ様に出力されます

画像の抽出

INDIRECT関数をうまく使えば、画像の抽出も行えます

上の画像のように、東京タワーや自由の女神などの画像が入ったセルに名前をつけておきます

この画像をカメラで別な場所に映しだすと、下の画像のように数式バーでは「画像の入ったセル」を参照しています

なお、上の画像は実際にはトリミングを行っています

こちらの数式バーを先ほど名前をつけた「アメリカ」に変えても画像は同じです

では、名前と画像の関係をお話したので、前述のINDIRECT関数の仕組みを使用して画像を自由に変えられるようにしてみましょう

今回は、画像という名前の中にINDIRECT関数を設定します

下の画像のように「画像」という名前にて、上の画像のF1セルをINDIRECT関数を通じて参照するように設定します

こうしておけば、INDIRECT関数を通じて、F1セルに表示された名前の画像をカメラ機能で表示することができます

ちなみに、セルは絶対参照にしておきましょう!(セルの位置がズレていくことがあります)

<まとめ>

いかがだったでしょうか?INDIRECT関数の面白さを体感して頂けたと思います

今回は、最後、画像の抽出の事例を紹介しましたが、INDIRECT関数を使用してVLOOKUP関数の参照範囲を変えることもできます

つまり、INDIRECT関数を使って関数を二次元で使うことができるということです

興味のある方はそちらの記事も参照してみてください


にほんブログ村

関数で比率をN:N形式で自動出力

GCDというエクセル関数をご存知でしょうか?

こちらの関数をもし知っているという方がいたら、とても通ですね!

今回は、このGCD関数を使用して「比率/N:N形式」を自動出力します

上のGIF画像では、700と2100の2つの値の比率「1:3」を自動出力しています

では、ポイントを絞ってこの比率の出力方法を解説します

前述のGCD関数とは、引数に指定した値間の最大公約数を算出する関数です

今回は、まずはGCD関数を使用して2つの値間の最大公約数を計算します

次に、算出した最大公約数で「値1」と「値2」を割ります

例えば、上の画像でケース2の値1は21なので、算出された最大公約数で割ると7になります

一方、値2の方は1になります

このように、各値を最大公約数で割れば比率が出力できるようになります

今の最大公約数を算出してから、比率を出力する過程を数式にすると、次の画像のようになります

結構、シンプルな数式で比率が出力できたと思います

ちなみに、今回使用したエクセルのバージョンは次の通りです

今回の解説は以上です

最後まで記事を読んで下さり、誠にありがとうございます

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

にほんブログ村