タグ別アーカイブ: 条件付き書式

ピボットテーブル

ピボットテーブルはとても便利です!

様々な切り口でデータを集計することができます

読者の中には「ピボットテーブルを使わないと、毎月の会議は無事に乗りきれない」という方もいらっしゃると思います

実はピボットテーブルの機能は「データの集計」だけではありません

データ集計以外の機能も有効活用すれば、集計後の業務を大幅に改善しつつ、表自体もプレゼンにそのまま使える「見やすい表」にすることができます!

1.転記・編集作業の改善

 【改善前】集計データを別表に転記し、編集作業

 【改善後】集計データから会議用の資料をそのまま作成

集計データから表作成
新ピボットテーブルデザイン

   解説記事は⇒こちらから

2.グラフ作成作業の改善

 【改善前】集計データを再度編集してグラフを作成

 【改善後】集計データからそのままグラフを作成

   解説記事は⇒こちらから

3.複数資料の作成

 【改善前】集計データから部門A、B用など同じような資料を作成

 【改善後】スライサーで集計結果をフィルタリング

   解説記事は⇒こちらから

4.更新処理

 【改善前】データ更新の度にピボットテーブルを更新

 【改善後】データ更新時にピボットテーブルを自働更新

   解説記事は⇒こちらから

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

条件付き書式で背景を変更

 コロナ疲れで「桜」が恋しくなってきましたので、エクエルシートの中に桜を咲かせてみました

仕組みはとても単純です!

 条件付き書式を使用して、各セルに入力されてある値が「参照セル」の値以下だと「セルの背景」が白になるようにしてあります

つまり、上の画像の黄色の箇所(セルK1)がちいさくなる

   ➡各セルの背景の色が「白」から「元の背景の色」に変わる

という仕組みになっています

では、桜をエクセルシート内に咲かせる仕組みについて解説します

但し、桜の画像については事前に準備する必要があります

1.背景の設定

ページレイアウトタブから準備した画像を背景に設定します

上の画像の「背景」をクリックすると、画像を指定できるようになります

背景を設定したら、1列は白にして後の列は非表示にします

下の画像で詳細を解説すると、K列は白色に設定し、L列より右は「非表示」に設定します

次に表示タブにて、目盛線も非表示にします!

2.数字をランダムにセルに入力

RANDBETWEEN関数を使用し、「1~100」の数字を各セルに散りばめます

フォントの色は以下の画像のように、「白」で設定します

更に、フォンの文字が目立たないように「フォントサイズ」を極小にします

3.数字を変動させるセルを設定

条件付き書式で参照するセルは「スピンオフ」ボタンで設定します

スピンオフをはじめ、「フォームコントロール」の詳細は過去記事をご参照ください

スピンオフボタンをエクセルシートに設定したら、スピンオフボタン上で右クリックします

次に開いた画面で、2.で各セルに設定した「1~100」の値を「リンクするセル」のK1セルに出力できるようにします

4.条件付き書式の設定

記事の冒頭で紹介したように、3.で設定したセルK1の値以下の場合には、セルの色が白になるように設定します

次の画像が、設定する条件式です

上の画像の条件に合致する場合には、セルの色が白になるように書式を設定します

上記の1~4を設定すれば完成です

今回紹介した条件付き書式の使い方は下のGIF画像の「ワッフルチャート」でも使用しています

興味のある方はこのワッフルチャートにも、ぜひ取り組んでみてください

では、長文に最後までお付き合い頂き誠にありがとうございました

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


にほんブログ村

ちょっぴり紹介したい技~信号機~

よく人間、見た目が重要だといいます

 これまで、あらゆる機会で数値関連のプレゼンに関わってきた経験から、数字も見た目が重要だと思っています

 それは、例えどんなにいい資料でも、見てもらえないと資料の中の数字は全く意味をなさないからです

 人間、誰でも数字を見るのは苦痛なものです

 数字を見てもらうには工夫が必要なんです!

 今回は、そんな苦い経験をもとにして、数字の大きさに応じ、表示が変わる信号機を作成しました

 信号機の作成の仕方自体はとても簡単です

ポイントは2つだけです

1.IF関数で信号機の色表示を作成する 

赤、黄、青、それぞれの色表示用のセルを作成し、値に応じて表示が変わるようにIF関数を仕込んでおきます

2.各セルをテキストボックスで信号機上に表示します

 テキストボックスの中に数式を入れても機能しません。数式バーで数式を入れます

 ちなみに下のGIFでは、ハイライトする月を選択し、ハイライトされた月の数字に応じて信号機の表示が変わるようになっています

 これらのハイライト関連の過去記事は↓です

 ・INDEX関数とMATCH関数の組み合わせ

 ・条件付き書式の意外な使い方

ぜひご参考にしてください!

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


にほんブログ村

アイコンを使おう

 資料の中に表やグラフをいくら詰め込んでも、どこか資料に”インパクト”がないと感じる時がありませんか?

 そんな時は、資料の中でキーとなる数字にアイコンを使って資料にインパクトをつけてみましょう! 

 実は、アイコンは条件付き書式の中の機能です

とても簡単に作成することができます!

書式ルールの編集で”書式スタイル”をアイコンセットにするだけです

今回はアイコン作成のポイントを2つに絞って解説します

アイコン

アイコン 16.60 KB 1 downloads

...

1.種類、値の指定

 値に応じて表示されるアイコンに変化がつくようにしましょう!

アイコンスタイルを選ぶと、表示されるアイコンに対応した値と種類が選択できるようになります

 書式ルールの編集画面を読み込んだ際に、種類はパーセントがデフォルトでセットされていますが、数値にしておいた方が値が設定し易いです

2.アイコンのみ表示

 書式ルールの編集画面を読み込んだ 際にはセルの値も自動表示されるようになっています

セルの値も表示すべきかどうかはケースによりますが、アイコンのみ表示のチェックを外すと、セルの値は表示されません

 最後にアイコンの使用例を紹介します

これはピボットテーブルに信号のアイコンを組み込んだ例です!

興味のある方はぜひこちらの記事を参考にして下さい

後、こちらはスマホのダッシュボードです

全部エクセルで出来ています!

こちらも解説記事があります。ぜひご参考にしてください!

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


にほんブログ村

条件付き書式の意外な使い方

 読者の皆さんが、条件付き書式の使い方としてすぐに思いつくのは、沢山データが並んだ表から、条件が合うものだけ色を変える機能だと思います

 確かにデータの整理には便利です・・・でも今回紹介するのは、プレゼン時の使い方です

例えば、会議の時、資料の説明をしていたのに「どこを説明してます?」って聞かれたことはないですか?

 そんな時のために条件付き書式を使います 

説明している箇所の色を、リストと条件付き書式を組み合わせることで自動的に変え、明確に”説明個所”を示すのです

 こんな風に説明したら結構、新鮮でないですか?このブログでは条件付き書式の意外な使い方として、他にもグラフとして使う、アイコンを表示するなどを紹介しています!

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


にほんブログ村

複数のグラフを一括表示

読者の皆さんの中には、資料の中にグラフが多すぎて辟易した経験がある方も多くいらっしゃると思います

今回、提供するテンプレートでは、HyperLInkとマクロ、並びに、条件付き書式を使い、1つのグラフで複数のグラフを提供できるようにします

アイコン

HyperLinkグラフ 20.21 KB 2 downloads

...

このテンプレートが多くの人の業務の効率化に役立つことを願っております

ちなみに詳細な仕組みを知りたい方は、ぜひこの記事をご参照ください!

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

条件付き書式2

前回は条件付き書式を使ってセルの中にグラフを作成する方法について解説しました

今回はセルの値に応じてグラフィックなアイコンを表示する方法を解説します

1~6まで順に作業していきましょう!

1.アイコンを表示する範囲を選択しておく

2.条件付き書式リボンをクリックして、”新しいルール”を選択する

3.書式スタイルから”アイコン”を選択する

4.アイコンスタイルを指定する

実に色々な種類がありますが、今回は信号を選択します

5.値範囲を設定しましょう

*種類を数値にしておくのを忘れないようにしましょう!

6.アイコンのみ表示にチェックを入れましょう!

これで完成です!

アイコンの種類は信号以外にも様々なものがあります

ぜひ色々と試してみてください!

次回からはタイムライン&スライサーについて解説していきます

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


にほんブログ村

新ピボットテーブル術1~転記・編集作業の改善_条件付き書式~

 

 今回は、ピボットテーブルの表の中に、条件付き書式のアイコンを挿入して、ダッシュボード化しましょう!

 条件付き書式のアイコンとピボットテーブルを組み合わせることで、見た目にメリハリがつき、1目見たときの印象が格段によくなります!

前回でピボットテーブルの表に元データに無い項目を追加する方法を解説しました

売上金額を百万円単位で表示にし、前期比の表示方法もあわせて解説しました

条件付き書式

 今回は下の図のように、条件付き書式の信号のアイコンを条件にあわせて表に挿入します

18

今回のポイントは3つあります

A.値の表示順と元データ

値の表示順は以下の画像のように左から

 ⅰ)売上金額(百万円)

 ⅱ)前期比

 ⅲ)信号

となります

但し、信号は前期比を元にして作成します

条件付き書式では「アイコンのみ表示」が指定できますので、上の表では信号のアイコンのみ表示し、前期比の数字は表示しません

B.フィールド名

A.の解説にて、信号は「前期比」から作成すると解説しました

前期比も前回の解説通り、フィールドリスト上は売上金額(百万円)から作成します

ですので、3つの「売上金額(百万円)」フィールドが必要になります

フィールドリスト上に同じフィールドを並べると末尾に番号が振られますので、縦に番号順に並ぶように調整しておくと便利です

順番の調整は、値欄を大きく表示できるようにしておいてからの方がよいでしょう

そうでないと、なかなかうまく順番が変えられません

フィールドの並び順を変える

値欄を大きくする表示については、以下の2つを参考にしておこなってください

a:フィールドセレクションを適切に設定する

フィールドセレクションを上にした方が値欄を大きく使えます

b:エリアを横に拡大する

下の画像の円部分を横に広げてください

ポイントを解説したところで、詳細な解説に入ります

下のサンプルファイルを事前にダウンロードしておいて頂けると幸いです

アイコン

サンプル 28.28 KB 28 downloads

...

1.値欄を再設定

話しを分かり易くするために、一度、値欄は空欄にしてください

その後、フィールドリストに「売上金額(百万円)」を3つ設定しましょう

 

2.条件付き書式の設定

条件付き書式の基本設定を行いましょう

まずはピボットテーブル表上の「合計/売上金額(百万円)3」の上にカーソルを置き、ホームタブから「条件付き書式」をクリックした後、「新しいルール」をクリックしましょう

GIF12

次にルールの適用対象には、1番上の「選択したセル範囲」でなく、2番目に表示されている「合計/売上金額(百万円)3」を指定しましょう

セル範囲で指定した場合にはピボットテーブルの「更新」処理を行った時に設定が消えてしまいます

19

3.ルールを設定する

ここからは機械的にルールを設定していきましょう!

・前期比が5%以上なら青信号

・0%以上なら黄色信号

・それ以外(0%未満)は赤信号

になるようにします

①アイコン種類の選択

各種内容を以下のように設定しましょう

 ⅰ)書式スタイル ⇒ アイコンセット

 ⅱ)アイコンスタイル ⇒ 信号

 ⅲ)アイコンのみ表示 ⇒ チェック

20

②信号表示の条件を設定する

デフォルトでは種類のところに”パーセント”が設定されていますが、数値に変更しておきましょう

21
各種設定

さて、①②を通じて以下のような形になりましたでしょうか?

<まとめ>

今回は条件付き書式を使用してピボットテーブル表上に、信号のアイコンを設定する処理を行いました

条件付き書式での設定箇所で注意して欲しい点を、もう一度おさらいします

・アイコンのみ表示 ⇒ チェック

チェックを入れることで、セルに数字は表示されずに信号のみが表示されます

・種類(数値or%)⇒数値

デフォルトでは%が入っていますが、数値にすることで信号の色を指定する条件(例:5%)を設定できます

上記の2点を注意して頂ければ、条件付き書式の設定はOKだと思います

 

次回からはいよいよ総仕上げになります

 

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

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

新ピボットテーブル術1~転記・編集作業の改善_導入~

今回から6回に分けて、「新ピボットテーブル術の1」を解説します

集計したデータから「転記処理」や金額の表示単位変更(円⇒百万円など)、前期比などの「集計列追加」などの作業を不要にする方法を解説します

ピボットテーブル
ピボットテーブル集計表

最終的には、集計対象のデータが変更になっても「更新ボタン」をワンクリックをするだけで資料を自働作成できるようにします

表を自動作成する光景が「イメージしにくい」という方は、ぜひ下のGIFをご覧になってください

集計対象データ(画面右)を「2017年対2016年」から「2018年対2017年」に変更したとしても、表(画面左)もそのまま「2017年対・」から「2018年対・」の表示になります

ピボットテーブル

さて、

ピボットテーブルから作成される表は通常、以下の画像のようなイメージです

6

記事の冒頭でお見せした表と同じデータを使用していますが、全く別の表のような感じですよね

記事冒頭の表にするため、次の回から大きく分けて以下の3つのことを行います

A.表の見た目を変える

ⅰ)データの並び順を変える

ⅱ)表上の「フィルターマーク▼」を取り除く

ⅲ)表の右「総計の列」を非表示にする

ⅰ)~ⅲ)⇒ 解説記事:2回目

ⅳ)信号のアイコンを挿入する

⇒解説記事:4回目

ⅴ)表の見出し「合計 / 売上金額」から合計~を除く

ⅵ)データ更新時も列の幅を維持する

ⅴ)~ⅵ)⇒解説記事:5回目

B.元データに無い項目を作成する

ⅰ)売上金額を百万円単位で表示する

ⅱ)前期比を集計、表示する列を作成する

⇒解説記事:3回目

そして、最終6回目はピボットテーブルで作成した表をより有効に活用するための「裏技」を解説します!

では次回から具体的な内容を解説していきます

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

意外なエク条件条件付き書式をグラフのように活用する

先日、村田諒太チャンプの防衛戦を見ました。

プレッシャーのかかる初防衛戦を見事なKOで飾りました

前途洋々な村田チャンプですが、去年の世界初挑戦した時の判定はひどいものでした

採点表を見て、いかにおかしい採点だったかを分析しようとしたところ、改めてボクシングの採点が分かりにくいということを感じました

ボクシングの採点は10-9、8-10といった形で数字を振り分けるのですが、この振分けの羅列を見てもなかなかピンと来るものがないので、条件付き書式を使い、以下のような形にしました

10

条件付き書式のデータバーを活用し、グラフの様な形でラウンド毎の数字の振分けを、1、-1,2、-2という形でデータ化した上で視覚的に分かるようにしています

8

ポイントは値がマイナスの時です

9

マイナスの時に色を変えておかないと、+との違いが視覚化できません

上記の仕組みはスパークラインの勝敗と同様のものですが、スパークラインよりカスタマイズが効きます

では、ぜひ、データを視覚化するプロセスを楽しんでください