タグ別アーカイブ: 使い方

ピボットテーブル

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

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

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

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

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

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

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

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

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

   解説記事は⇒こちらから

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

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

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

   解説記事は⇒こちらから

3.複数資料の作成

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

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

   解説記事は⇒こちらから

4.更新処理

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

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

   解説記事は⇒こちらから

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

ソルバーによるシミュレーション4~実践編・バイナリ設定(0,1)~

前回はソルバーの解答を整数で指定する方法を解説しました

今回は違う指定方法を解説します

プロジェクトなどで人をアサインする場合、前回のような”整数”で指定してシミュレーションを行うとどうなるでしょうか?

シミュレーションする内容はプロジェクトを組成する際に、目安にする予算金額に応じて参加メンバーを決める場合です

詳細な条件は以下です

・目安は4百万円

・人数は5人(2人は決定済み)

・候補者の単価はそれぞれ違う

前回の整数指定で行うとこんな結果になります

鈴木さんが2人アサインされてしまっています

これではシミュレーションとして成り立ちません

では、具体的な解説に入りましょう

1.条件に応じて数式を設定する

 ・人数の指定:=SUM(F5:F14)

 ・人件費の計算:=SUMPRODUCT(E5:E14,F5:F14)

・差額の計算:=(G15-G16)^2

2.ソルバーに目的セルと変数セルを設定する

3.ソルバーに制約条件を設定する

まずは5人という条件を設定します

では次にどうしたらいいでしょうか?

こんな時には整数ではなくバイナリで制約条件を指定します

つまり1(アサイン有)か0(アサイン無)で解答が出るようにします

”bin”を指定すれば”バイナリ”が自動的にセットされます

では解答を計算しましょう

これできっちりとした解答が出ました!

最後に今回のポイントですが、以下の2点です

➀人件費のシミュレーションなどで有無(1か0)で解答が必要になるケースがある

②①のケースのように有無でシミュレーションの解答が必要な場合にはソルバーの制約条件をバイナリで指定する

 

今回は以上です

次回からはソルバーをマクロで自動で動かす方法を解説します

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


にほんブログ村

スライサーのリスト表示順を変える

 ピボットテーブルの表示を操作しやすくするスライサーはとても貴重な存在です。スライサーを使うだけでエクセル画面が華やかになります。

  ただし、スライサーの使い方について、困ったことが起こっても日本語での解説はあまりないのが現状です(⇒スライサーについての詳細記事あります) 

 スライサーを使っていて困ることの一つは、スライサーで自動設定されている並び順を変えたい時です。そんな時にはリスト自体を自分で設定して独自の並び順を作成しましょう

それでは、独自の並び順を作成する手順を3段階で説明します!

1.並び順を決める

 下の画面では、スライサー上では中村さんが1番上の左に来ていますので、

田中さんの表示を1番上・左に移動したいとします

後で詳細を解説しますが、リスト全体を含めて並び順を設定するので、リスト全ての並び順を事前に決めおく必要があります

2.オプションの詳細設定でリストを設定する

 エクセル画面の左上、ファイルタブから⇒オプション⇒詳細設定をクリックして、下までスクロールします

 全般という文字が見えたら、右下に「ユーザー設定リストの編集」のボタンが見えますのでこちらをクリックします

立ち上がったダイアログの右側に 「1.並び順を決める」 で決めた順番でリストを記入しましょう

リストを記入する場合には、必ず1リスト毎にENTERを押しましょう!

リストの記入が終了したら、下の画面の「追加ボタン」をクリックしましょう

すると左のユーザー設定リストの一番下に、追加した内容が反映されています

3.並び順を変える

スライサー上にカーソルを置いて並び順の箇所(↓↑)をクリックしましょう

 どうです?並び順は変わりましたでしょうか?うまくいかないと思ったら、試しに3.出てくる(↑↓)の昇順と降順を交互にクリックしてみてください!

 昇順と降順の切り替えについては、下の画像にあるようにスライサーの設定画面でも指定できます

スライサーの設定画面は、スライサー上にカーソルを置いて右クリックすると開くことができます 

ここで、問題が1つがあります

今、並び順を設定したリストは5つしかありませんでした

ですから、手動で並び順を設定できました

リストの項目設定の画面は、エクセルシートから直接コピーができません

仮に50も100もリストがある場合には、ご紹介した方法だと並び順の設定だけで膨大な時間を割いてしまいます

ですので、ここでリストが多い場合のテクニックを2つ紹介したいと思います

①メモ帳を活用する

リストを1度、メモ帳にコピーしてください

その後、メモ帳からコピーをした内容を更に前述のリストの項目画面にコピーすれば手入力不要で並び順の設定が可能です

②リストのインポート

ちなみに、リストの元データをインポートすることで、ユーザー設定リストに新たな並び順を反映する方法もあります

また、もっとスライサーの活用の仕方について知りたい方は

 ⇒こちらの記事をぜひ参照してください

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

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


にほんブログ村

スパークライン~折れ線・縦棒~

スパークラインという機能はあまり馴染みがないと思いますが、分析・プレゼンに十分活用できます。見た目はグラフと一緒ですが、関数と同じ感覚でセル内に手軽に作成できて、大量に並べて比較できる点が違います

一番効果が出やすいのが、下のような数字が詰め込まれた表を視覚的にする時です。

上の画像の表は実際には30日まであります。表が細かすぎて、一目では何も掴めません。ではスパークラインにするとどうなるでしょう?

少なくとも商品別や日別で特徴みたいなものは見えてきそうな気がしてきたのではないかと思います。ぜひ下のサンプルをダウンロードして実際に見てみてください[

1.取り合えずスパークラインを作成してみる

 スパークラインの良さは関数と同じ感覚で、作成できるところです。グループ単位(複数のセル)でも作成できてしまいます。

 まずは作成したい複数セルにカーソルを置きましょう。


挿入タブからスパークラインの折れ線をクリックしましょう

するとスパークラインの基となるデータを指定できるようになります。

ちなみに、一つのスパークラインに対して複数行のデータは指定できないようになっています。もし、指定元が10個あり、対応する範囲が10行の場合はOKです。これが10個に対して11行とかになるとエラーになってしまいます

縦棒の方も同じように作成することができます

2、山(最大値)と谷を指定する

スパークラインではグラフと違って山と谷を指定することができます。この機能を使えば、上昇傾向や下降傾向などの”数時の動きの特徴”がより掴みやすくなります

スパークライン上にカーソルを置いてデザインタブをクリックします。すると以下のチェックボックスが出てきます

両方クリックして見てください

山の箇所と谷の箇所のマーカーが変わります

上の画像のように谷だけ色を変えたい場合には以下の箇所を修正します

では次回、更にスパークラインを深堀していきます

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


にほんブログ村

テーブルを作成しよう~数式を自動入力・詳細~

前回はいきなり解答から入りましたので、今回は詳細について解説します

<解答詳細>

・まず、テーブルを作成する

・条件1の列と条件2の列を組み合わせて複合キーにする(例:AA、ABなど)

・置換表(AA⇒◯)を別途作成し、複合キーを検索値として、VLOOKUP関数により置換表で検索値を置き換える

GIFを見ると、テーブルにしてあるので数式が一気に入力されていくのがよく分かると思います

これだけで物凄い省力化になっています

4

ここからはテーブル機能以外の説明になります

テーブルを作成した後は”&”を使って複合キーを作ります

そして、次に置換表を作成します

5

最後はVLOOKUP関数で複合キーを置き換えます

6

但し、複合キーがAA以外だとエラーが出力されるので、=IFERROR関数を組み合わせ、エラーの時には空白(””)を出力するようにします

ここまで説明した事を別な表現で言い変えると”テーブルと関数の効果的な組み合わせ”ということになります

これで、<AAは✕にする>、というルールが出来ても一気に置換が出来ます

GIF5

置換表を追加しても同じように一気に置換ができます

GIF6

最後に、

置換表を追加する場合の為にVLOOKUP関数の検索範囲は列のみ指定するようにしておく必要があることを付け加えておきます

7

それではまた次回

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

カメラ機能について

ダッシュボードでは複数のグラフや表を1か所で表示する必要があります

ところが全て1か所で作成するにはいきません

ですので、カメラ機能により別の場所で作成したグラフなどを表示させることが必要になってきます(下のGIFの右上の東京タワー、自由の女神の画像がカメラ機能で映される画像です。別のシートの画像が映し出されています)

このカメラ機能はダッシュボード作成以外の用途でも有効に活用できます。ぜひ、この機会に習得してください

今回はカメラ機能・活用のポイントを2つだけ解説します

1.カメラ機能の設定

エクセルはデフォルトでは”カメラ”のタブがありません。自身で設定する必要があります。この設定には下の動画を見て頂くのが早いかもしれません

設定の入り口は2種類あり、ファイルタブからオプションを選んで設定する方法と、エクセル画面上のクイックアクセスツールバーから設定する方法があります

キャプチャ1

今回はクイックアクセスツールバーを入り口にする方法を紹介しています

上の画像の一番右のボタンを右クリックし、”その他のコマンド”を選択します。この後の操作は2種類の設定方法ともに共通です

左上の”コマンドの選択”から”リボンにないコマンド”を選択し、下にスクロールしていくと”カメラ”のボタンが出てくるので、選択して真ん中の”追加”を押します。

そうすると、下の画像のように”カメラ”がクイックアクセスツールバーに設定されています

キャプチャ2

これでカメラの設定が終わりました

2.元画像の指定方法

カメラの実際の使用方法は、映したい画像があるセルにカーソルを置き、カメルのタブをクリックした後に、好きな場所でクリックします。するとクリックした場所で画像が映し出されます

動画の後半にあるように、画像が乗っているセルを参照する場合にはあくまでセルを選択してください。画像をクリックしてもカメラ機能が働きません

キャプチャ3

確かにセルに画像が乗っていると範囲を設定しにくいので、この場合(B2セル)は一度、A1セルを選び、それから右に移動すると範囲設定がしやすくなります

次回はカメラで参照した画像(映像)を修正する技術を紹介します

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


にほんブログ村