ピボットテーブルはとても便利です！
様々な切り口でデータを集計することができます
読者の中には「ピボットテーブルを使わないと、毎月の会議は無事に乗りきれない」という方もいらっしゃると思います
実はピボットテーブルの機能は「データの集計」だけではありません
データ集計以外の機能も有効活用すれば、集計後の業務を大幅に改善しつつ、表自体もプレゼンにそのまま使える「見やすい表」にすることができます！
１．転記・編集作業の改善
【改善前】集計データを別表に転記し、編集作業
【改善後】集計データから会議用の資料をそのまま作成
解説記事は⇒こちらから
２．グラフ作成作業の改善
【改善前】集計データを再度編集してグラフを作成
【改善後】集計データからそのままグラフを作成
解説記事は⇒こちらから
３．複数資料の作成
【改善前】集計データから部門Ａ、Ｂ用など同じような資料を作成
【改善後】スライサーで集計結果をフィルタリング
解説記事は⇒こちらから
４．更新処理
【改善前】データ更新の度にピボットテーブルを更新
【改善後】データ更新時にピボットテーブルを自働更新
解説記事は⇒こちらから
エクセルでもビジュアルはかなりカスタマイズできます！でも、Power BIだと更にカスタマイズができます！！
今回は、DAX式と条件付き書式を組み合わせて平凡な縦棒グラフを、周囲を「あっと」言わせるグラフにしましょう！
上の画像が今回の工夫をする前の、通常の縦棒グラフです
こちらのグラフの横棒の色を、値に応じて変更できるようにします！
BeforeとAfterのグラフを見比べてみていかがでしょうか？
Afterだと広大な棒グラフの砂漠に「良いところ」「悪いところ」のメリハリがつきました
エクセルではここまでできません
しかも、Power BIのDAX式と条件付き書式を使えばそんなに難しくありません
ぜひ試してみてください！
ポイントは２つあります
色指定を行うメジャー/ColorをDAX式を使い作成するのが一点目です
二点目は、条件付き書式に一点目の色指定を行うメジャーを組み込みます
今回の場合だと、月の売り上げ金額に応じて色を変更します
ですので、色指定の対象となる値は売上金額です
売上金額に応じて複数の条件を設定し、条件に対応する色を指定します
関数はSWITCH関数を使用します
SWITCH(対象の値,条件式1,結果1,条件式2,結果2･･･)
上の式で結果/色指定はどうしましょう？
“RED”などで指定する方法もあります
今回はHEX値を使用します
こちらのHEX値はエクセルシートから抽出できます
HEX値を反映したメジャーは次のようになります
Color = SWITCH(TRUE(),’テーブル'[当月売上]<25000,”#FF0000″,’テーブル'[当月売上]<30000,”#FFFF00″,”#0099FF”)
条件付き書式は次の画像の箇所から設定します
データ形式スタイルは「フィールド」を指定します
基準にするフィールドには、前述の色指定のメジャーを設定します
これで、値に応じて色が変更になる棒グラフが作成できました
今回はグラフの色を値に応じて変更できるようにしました
これで、平凡な棒グラフにメリハリがつきます
実はエクセルでも最高値の色を変える方法は紹介していました
ただエクセルでは2色が限界でした
Power BIでは値に応じて色を臨機応変に変更できます
中には今回紹介した「SWITCH関数がどうも馴染めない」という方もいらっしゃると思います
その場合はエクセルのワークシート関数「SUMIFS関数」などで慣れを作成してみてください
長文を最後までお読み頂き誠にありがとうございました
【カオスな現場経験 x 分析力を組み合わせた、少し尖ったマクロのレシピ集】
とてつもなく大きい表を使っているときに迷子になることがありませんか？
「今、どの行とどの列のセルを扱っているんだろう・・・」
しまいには、ミスが怖いので表を印刷して「定規」を当てたりしたことはありませんか？
今回、この迷子対策として参考にしたのがこの記事です
⇒参考記事
今回は迷子対策として、上の記事を基にした「動く条件付き書式」の事例を紹介します
この動く条件付き書式では、カーソルを置いた位置の行列の色が変わるので、「迷子対策」にはバッチリです！
紹介する事例は２つあります！
1つ目の事例は、CELL関数とROW関数、そしてCOLUMN関数を使用して条件付き書式を設定します
こちらは、手動で条件付き書式を設定する内容になっています
＊イベントプロシージャ―に１行コードを書く必要があります
この方法の場合には、重たいファイルの場合だと「条件付き書式」の動きがかなり重たくなります
２つ目の内容は、イベントプロシージャ―を使用して、カーソルが動く都度「条件付き書式」をマクロで自動設定します
こちらは条件付き書式の範囲を面で指定するわけではなく、「行列」双方向の2本の線で範囲を設定しますので、あまり動きを重くしないですみます
後、２つ目の方法の場合には色をシート上で設定できるようにするなどの、実践的に使えるようにする為の工夫を複数行っています
こちらは、サンプルファイルをダウンロードして、実際の動きを確認できます！！
まずは、条件付き書式を設定する範囲を手動で指定します
その後、条件付き書式の設定画面を開き、ルールの種類の選択を「数式を使用して、書式設定する設定を決定」にします
「次の数式を満たす場合に値を書式設定」には、画像のような数式を設定します
数式：=OR(CELL(“ROW”)=ROW(),CELL(“COL”)=COLUMN())
*上の数式をそのままコピーする際には、”が半角になっていることをご確認ください
これで条件付き書式の設定は終了です
実はもう一つ、仕掛けが必要です
イベントプロシージャ―を使用し、セルの選択が変更になった場合に「更新処理」を行う必要があります
更新処理を設定するには、まずは、上の画像のように該当のシートで右クリックをして「コード表示」を選択します
次に開いたVBE（Visual Basic Editor）画面では、下の画像の黄色の印の箇所がそれぞれ「Worksheet」「SelectionCange」になっていることを確認します
プロシージャ―には1行だけコード＊を入れれば、条件付き書式の更新処理が設定されます
*Application.ScreenUpdating = True
こちらは、前述のようにこの「動く条件付き書式」を実践的に使えるように様々な工夫を行っています
各種設定シートというシートを設けてあり、様々な設定が行えます
各種設定シートにて、色を指定すれば、条件付き書式で設定する色を変更できます
条件付き書式の設定を行う開始行と開始列はシート上で調整できます
上の画像では開始行が6行目、開始列が1列目ですので下の画像のように色が変わります
この動く条件付き書式は「停止」したい時があると思います
この際は、停止ボタンを押せば停止ができる仕掛けになっています
停止処理を行った場合には、停止前に設定された色が残ってしまっています
ですので、上の画像の消去ボタンを押せば「設定残り」の色は消去できます
こちらは、ごく簡単なポイントだけを解説させて頂きます
この事例は、前述のように条件付き書式の範囲が拡大したり縮小する場合も想定しつつ、処理が重たくならないようにします
こちらの場合は、色の付け方が、1番目の方法と変えてあります
カーソルがある位置が、色のつく範囲の最終行と最終列になります（1番目の方法も可能ではありますが、今回は極力色設定する範囲を小さくするという趣旨でこの方法にしました）
カーソルを移動させた際は、イベントプロシージャ―にてカーソル位置を取得します（Targetを引数にして取得）
カーソル位置を取得したら、２つ範囲を設定します
１つ目は行方向です。範囲の開始列は1列目などを固定で指定して範囲設定します
２つ目は列方向です。範囲の開始行は1行目などを固定で指定して範囲設定します
重複して条件付き書式が設定されないように、設定済みの条件付き書式はクリアしておきます
ポイント３ ⇒マクロ処理の途中でイベントプロシージャ―は止めておきます
１つの範囲に条件付き書式を設定した際に、更にイベントが起動してしまいますので、次の1行のコードでイベントを中止しておきます（後で再開が必要です）
Application.EnableEvents = False
今回はとてつもなく大きい表を扱う際、迷子にならないようにカーソルがある行と列の色を変える方法を紹介しました
但し、
特に2番目の方法などが当てはまるのですが、実際に業務で活用する場合は、さらに一工夫が必要です
例えば、2番目の方法は設定済みの条件付き書式をクリアしてしまいますので、「迷子対策」以外の条件付き書式の設定が消えてしまうケースも想定されます
ですので、別途「迷子対策以外」の条件付き書式も自動設定する必要があります
ちなみに、2番目の事例のサンプルファイルですが、こちらは複数のシートで「動く条件付き書式」を試せるようになっています
ぜひ、各種設定シートで「シート名」を変えながら試してみてください！
なお、コードについては非公開とさせて頂いていますので、予めご了承ください
それでは、最後まで長文にお付き合い頂きありがとうございました！
今回紹介した仕組みは、実務では更に工夫の余地があると思いますが、エクセルVBAならではの便利さが生み出せると思います！
少しでも、大きい表を扱う際の「イライラ」を消すことに貢献できたら幸いです
もし、質問や要望等がありましたら「お問合せ」からご連絡をお願い致します
コロナ疲れで「桜」が恋しくなってきましたので、エクエルシートの中に桜を咲かせてみました
仕組みはとても単純です！
条件付き書式を使用して、各セルに入力されてある値が「参照セル」の値以下だと「セルの背景」が白になるようにしてあります
つまり、上の画像の黄色の箇所（セルK1）がちいさくなる
➡各セルの背景の色が「白」から「元の背景の色」に変わる
という仕組みになっています
では、桜をエクセルシート内に咲かせる仕組みについて解説します
但し、桜の画像については事前に準備する必要があります
１．背景の設定
ページレイアウトタブから準備した画像を背景に設定します
上の画像の「背景」をクリックすると、画像を指定できるようになります
背景を設定したら、1列は白にして後の列は非表示にします
下の画像で詳細を解説すると、K列は白色に設定し、L列より右は「非表示」に設定します
次に表示タブにて、目盛線も非表示にします！
２．数字をランダムにセルに入力
RANDBETWEEN関数を使用し、「１～１００」の数字を各セルに散りばめます
フォントの色は以下の画像のように、「白」で設定します
更に、フォンの文字が目立たないように「フォントサイズ」を極小にします
３．数字を変動させるセルを設定
条件付き書式で参照するセルは「スピンオフ」ボタンで設定します
スピンオフをはじめ、「フォームコントロール」の詳細は過去記事をご参照ください
スピンオフボタンをエクセルシートに設定したら、スピンオフボタン上で右クリックします
次に開いた画面で、２．で各セルに設定した「１～１００」の値を「リンクするセル」のK1セルに出力できるようにします
４．条件付き書式の設定
記事の冒頭で紹介したように、３．で設定したセルK1の値以下の場合には、セルの色が白になるように設定します
次の画像が、設定する条件式です
上の画像の条件に合致する場合には、セルの色が白になるように書式を設定します
上記の１～４を設定すれば完成です
今回紹介した条件付き書式の使い方は下のGIF画像の「ワッフルチャート」でも使用しています
興味のある方はこのワッフルチャートにも、ぜひ取り組んでみてください
では、長文に最後までお付き合い頂き誠にありがとうございました
よく人間、見た目が重要だといいます
これまで、あらゆる機会で数値関連のプレゼンに関わってきた経験から、数字も見た目が重要だと思っています
それは、例えどんなにいい資料でも、見てもらえないと資料の中の数字は全く意味をなさないからです
人間、誰でも数字を見るのは苦痛なものです
数字を見てもらうには工夫が必要なんです！
今回は、そんな苦い経験をもとにして、数字の大きさに応じ、表示が変わる信号機を作成しました
信号機の作成の仕方自体はとても簡単です
ポイントは２つだけです
１．IF関数で信号機の色表示を作成する
赤、黄、青、それぞれの色表示用のセルを作成し、値に応じて表示が変わるようにIF関数を仕込んでおきます
２．各セルをテキストボックスで信号機上に表示します
テキストボックスの中に数式を入れても機能しません。数式バーで数式を入れます
ちなみに下のGIFでは、ハイライトする月を選択し、ハイライトされた月の数字に応じて信号機の表示が変わるようになっています
これらのハイライト関連の過去記事は↓です
ぜひご参考にしてください！
資料の中に表やグラフをいくら詰め込んでも、どこか資料に”インパクト”がないと感じる時がありませんか？
そんな時は、資料の中でキーとなる数字にアイコンを使って資料にインパクトをつけてみましょう！
実は、アイコンは条件付き書式の中の機能です
とても簡単に作成することができます！
書式ルールの編集で”書式スタイル”をアイコンセットにするだけです
今回はアイコン作成のポイントを２つに絞って解説します
１．種類、値の指定
値に応じて表示されるアイコンに変化がつくようにしましょう！
アイコンスタイルを選ぶと、表示されるアイコンに対応した値と種類が選択できるようになります
書式ルールの編集画面を読み込んだ際に、種類はパーセントがデフォルトでセットされていますが、数値にしておいた方が値が設定し易いです
２．アイコンのみ表示
書式ルールの編集画面を読み込んだ 際にはセルの値も自動表示されるようになっています
セルの値も表示すべきかどうかはケースによりますが、アイコンのみ表示のチェックを外すと、セルの値は表示されません
最後にアイコンの使用例を紹介します
これはピボットテーブルに信号のアイコンを組み込んだ例です！
興味のある方はぜひこちらの記事を参考にして下さい
後、こちらはスマホのダッシュボードです
全部エクセルで出来ています！
こちらも解説記事があります。ぜひご参考にしてください！
読者の皆さんが、条件付き書式の使い方としてすぐに思いつくのは、沢山データが並んだ表から、条件が合うものだけ色を変える機能だと思います
確かにデータの整理には便利です・・・でも今回紹介するのは、プレゼン時の使い方です
例えば、会議の時、資料の説明をしていたのに「どこを説明してます？」って聞かれたことはないですか？
そんな時のために条件付き書式を使います
説明している箇所の色を、リストと条件付き書式を組み合わせることで自動的に変え、明確に”説明個所”を示すのです
こんな風に説明したら結構、新鮮でないですか？このブログでは条件付き書式の意外な使い方として、他にもグラフとして使う、アイコンを表示するなどを紹介しています！
読者の皆さんの中には、資料の中にグラフが多すぎて辟易した経験がある方も多くいらっしゃると思います
今回、提供するテンプレートでは、HyperLInkとマクロ、並びに、条件付き書式を使い、１つのグラフで複数のグラフを提供できるようにします
このテンプレートが多くの人の業務の効率化に役立つことを願っております
ちなみに詳細な仕組みを知りたい方は、ぜひこの記事をご参照ください！
前回は条件付き書式を使ってセルの中にグラフを作成する方法について解説しました
今回はセルの値に応じて、グラフィックなアイコンを表示する方法を解説します
では１～６まで順に解説していきます！
前回と同じ様にまずは、条件付書式を設定する範囲を選択します
条件付書式を設定する範囲を選択したら、ホームタブで「条件付書式」のマークをクリックします
次に下の画像のように「新しいルール」をクリックします
次に表示された画面では、書式スタイルを「アイコンセット」に指定します
アイコンスタイルは実に色々な種類がありますが、今回は信号を選択します
値によってアイコンの表示を変えれるように、下の画像の値を設定します
上の画像では、０．５以上で緑の信号が表示できるようになります
ちなみに、上の画像の「数値」の箇所に黄色の印を付けておきました
こちらは通常では「％」の表示になっていますので、必ず「数値」に変えておきましょう
５で値を設定したら、下の画像のようになっています
セルの中に値がまだ表示されていますので、下の画像の「アイコンのみ表示」のチェックを外します
これで完成です！
今回は、条件付書式でアイコンを表示する方法を解説しました
アイコンの種類は信号以外にも様々なものがあります
ぜひ色々と試してみてください！
次回からはカメラ機能について解説していきます
今回から5回に分けて、ピボットテーブルをそのまま報告用の表に変える「新ピボットテーブル術」を解説します
集計したデータから「転記処理」や金額の表示単位変更（円⇒百万円など）、前期比などの「集計列追加」などの作業を不要にする方法を解説します
最終的には、集計対象のデータが変更になっても「更新ボタン」をワンクリックをするだけで資料を自働作成できるようにします
表を自動作成する光景が「イメージしにくい」という方は、ぜひ下のGIF画像をご覧になってください
集計対象データ（画面右）を「2017年対2016年」から「2018年対2017年」に変更したとしても、表（画面左）もそのまま「2017年対・」から「2018年対・」の表示になります
さて、
ピボットテーブルから作成される表は通常、以下の画像のようなイメージです
記事の冒頭でお見せした表と同じデータを使用していますが、全く別の表のような感じですよね
記事冒頭の表にするため、次の回から大きく分けて以下の３つのことを行います
A．表の見た目を変える
ⅰ）データの並び順を変える
ⅱ）表上の「フィルターマーク▼」を取り除く
ⅲ）表の右「総計の列」を非表示にする
ⅰ）～ⅲ）⇒ 解説記事：2回目
ⅳ）信号のアイコンを挿入する
⇒解説記事：4回目
ⅴ）表の見出し「合計 / 売上金額」から合計～を除く
ⅵ）データ更新時も列の幅を維持する
ⅴ）～ⅵ）⇒解説記事：5回目
B．元データに無い項目を作成する
ⅰ）売上金額を百万円単位で表示する
ⅱ）前期比を集計、表示する列を作成する
⇒解説記事：3回目
では次回から具体的な内容を解説していきます
