タグ別アーカイブ: データ集計

ピボットテーブル

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

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

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

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

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

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

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

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

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

   解説記事は⇒こちらから

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

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

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

   解説記事は⇒こちらから

3.複数資料の作成

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

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

   解説記事は⇒こちらから

4.更新処理

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

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

   解説記事は⇒こちらから

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

Sで終わる関数は素晴らしい!~Countifs関数~

前回は、IFS関数を紹介しました

今回はCOUNTFIFS関数を紹介します

本当にSで終わる関数は素晴らしいです

数式の書き方はIFS関数とほぼ同じです

今回は下の事例で解説を行います

この表から最高得点・科目と最低得点・科目の組み合わせが当てはまる人数をCountif関数とCountifs関数、の両方で求めてみます

.Countif関数のケース

Countif関数は1つしか条件式を設定することができません

なので、一旦、複合キーを作成して検索条件にするしかありません

数式は次のような書き方になります

Countif関数では、検索条件範囲/H4:H8と検索条件/D12との組み合わせを、1つしか使えないのです

=COUNTIF(H4:H8,D12)

2.Countifs関数のケース

Countifs関数の場合は複合キーを使用する必要はありません

Counfifs関数の場合、複数の検索条件範囲と検索条件の組み合わせを設定できるからです

以下が、実際に上の画面で使用した数式です

=COUNTIFS(F4:F8,B16,G4:G8,C16)

数式の書き方は、前回に紹介したIFS関数と同じ様に、検索条件範囲⇒検索条件の記述を条件がある数だけ繰り返すだけで済みます

<まとめ>

Countif関数を使いたい時に、複数条件がある場合には複合キーを作成しなくてはならないので、いつも面倒くさい思いをしていました

Countif関数を使えば、条件がいくつあろうとあまり手間ではありません

Sで終わる関数は本当に便利ですね

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


にほんブログ村

複数シートを自動集計~3D参照~

筆者は昔、予算管理の仕事をしていましたが、いつも複数シートの集計に苦しんでいました

月ごとの数字を管理するのに、ひたすらシート名+シート名+シート名のつるべ打ちをしていたのです

この方法だと手間がかかるだけでなく、シートを追加したり、削除した時にミスが起こり易いです

一方、今回の回で紹介する3D参照では、一気に複数シートをSUM関数で集計することができます

シート追加や削除した時にも、追加・削除に連動して集計値が変わりますので、集計ミスも防げます

下のGIFではシート追加前は200だったセルが、追加後は追加したシートの数字20が自動的に追加されています

この3D参照は特に難しい技術は必要なく、あくまで文法の問題だけをクリアすれば誰でも活用できます

それでは3D参照についてのポイントを2つ、以下に解説します

A.文法

以下は複数シートをシート名のつるべ打ちで集計する数式です

シート名とセル名を除くと同じパターンで文字が出てくるのが分かりますでしょうか?

 =’シート名’!セル名+’シート名’!セル名+・・・

今回の3D参照では、+でシート名を足す代わりに「’と’!」の中で、通常のSUM関数と同じ様に”:”で範囲を指定するのです

こんな↓感じです

B.ワイルドカード*

シート名を工夫した上での前提ですが、*カードを使って更に複数シートの集計作業の効率化を行うことができます

例えば、シート名を四半期単位で集計できるように工夫しておきます 

そうすれば、ワイルドカードを使った集計が行えるようになります

例えば、シート名が1Q_4月、1Q_5月、といった具合に並んでいる場合は、1Q以降を*で置き換えます

このワイルドカードを使えば、SUM関数内に1つのシートでも指定すれば、後は必要箇所を*で置き換えるだけですので、とても効率的です

ポイントを2点、文法とワイルドカード*について解説したところで、ぜひ、以下のサンプルファイルをダウンロードして頂き、実際のデータで試してみてください

サンプルファイルは完成と演習の2つのシートがあります

演習のシートでは実際の操作の様子を参照できるように、2つの方法を用意してあります

どちらもフリーアドインを使用した方法です

①Web Video Player

このアドインはエクセルシート上で動画を開くことができます

演習シート上に記載されたURLをWeb Video Playerにコピーして使用してください

➁QRコード

演習シート上のQRコードをスマホで読み込んでください

動画が自動的にスマホ上で開きます

<まとめ>

今回は、複数シートの集計を、シート名+シート名のつるべ打ちではなく、3D参照によりSUM関数で一気に集計する方法を学びました

3D集計は複雑ではないのですが、ひとつでも記述を間違えただけでもエラーになってしまいます

ですから、’や!の位置をひとつひとつ確認して頂きながら作業を進めていけばスムースに作業を完了させることができます!

後は、サンプルファイルを基にして、とにかく手を動かしてみましょう!

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


にほんブログ村

XxXLookup関数で集計上手になる!

読者の皆さんがこれまで使用したエクセル関数の中で、一番使用頻度が高かったのはどの関数でしょうか?

多くの皆さんはVLOOKUP関数と答えるのではないでしょうか?

ところが、XLOOKUP関数の登場により、関数の使用シーンがこれから大きく変わっていきそうです

まだ使用できるエクセルバージョンは少ないかもしれませんが、それだけXLOOKUP関数の登場はインパクトがあります

特にXLOOKUP関数と他の関数を組み合わせることにより、データ集計の効率を格段に向上させることができそうです

今回は、XLOOKUP関数同士を組み合わせた例を2つ紹介します

どちらの例も、表を2次元で処理できるので、利用シーンが多そうです

①マトリクス表から2つの条件に合致するものを抽出する

条件を2つ(条件①部門x条件➁四半期間)をリストで指定し、2つの条件に一致する値を抽出する

➁マトリクス表でランク付けをする

事業の売上額と成長率、在庫商品の在庫額と回転率、などなど二次元でランク付けすることはよくあります

ところが、これまでは単に関数を使用するだけでは、ランク付けは自動ではできませんでした

尚、これからの解説は既にXLOOKUP関数を使用したことがある人を主として想定しています

但し、そうでない方もサンプルファイルをダウンロードすることで、そのままサンプルファイルの使用例を他の目的に利用していくことも想定しています!

1.そもそもXLOOKUP関数とは?

2つの事例を紹介する前に少しXLOOKUP関数について簡単におさらいをしておきましょう

サンプルファイルでは1シート目の内容です

サンプルの内容は部門毎に対応する部門長を別表から抽出するものです

XLOOKUP関数も、VLOOKUP関数と同じように検索値を基にして表からデータを抽出します(事例では北海道を検索値として、部門長の木村を抽出)

但し、VLOOKUP関数で行っていたような

「○○から右に何番目」

といった列数の指定が無く、検索する範囲(サンプルの部門)、戻り範囲(部門長)を指定しするだけです

つまり、 「○○から右に何番目」 の縛りがなく、検索値が参照表の左に無くてもいいし、列方向でなく行方向でもデータを抽出できます

引数は以下のような形で指定します

=XLOOKUP(検索値、検索値範囲、戻り範囲、【省略可】)

2.マトリクス表から2つの条件に合致するものを抽出する
XLOOUP関数の3番目の引数、戻り範囲にXLOOKUP関数を入れるのがポイントになります

仮に戻り範囲にXLOOKUP関数を入れなければ、いわゆるスピる(値があふれ出す)状態になります

以下が検索の軸を部門にし、戻り範囲を表の全範囲にした場合です

列方向でスピります

・=XLOOKUP(I3,B3:B9,C3:F9)*3番目の引数を全範囲

以下が期間を軸に、戻り範囲を表の全範囲にした場合です

行方向でスピります

・=XLOOKUP(I4,C2:F2,C3:F9)*3番目の引数を全範囲

この2つのスピりの交点が求まれば正解の値です

ですので、以下のように3段階でXLOOKUP関数を組み合わせます

A.部門の検索を行うXLOOKUP関数を作成

B.但し、3番目の引数に期間を検索するXLOOKUP関数を入れる

C.3番目の引数になるXLOOKUP関数は”期間の選択”に備えて戻り範囲=全範囲

最終的にはこのようになります

=XLOOKUP(I3,B3:B9,XLOOKUP(I4,C2:F2,C3:F9))

数式の内容はサンプルファイルにて、実際に入力されている内容を確認してくみてください

3.マトリクス表でランク付けをする

2.で行ったXLOOKUPによる関数では、4番目以降の引数を省略していました

ここで、5番目の引数を登場させます

VLOOKUP関数でも4番目の引数で検索方法を指定しています

FALSE=完全一致

TRUE≒完全一致

XLOOKUP関数では以下のように検索方法を指定できます

0⇒完全一致

-1⇒検索値の次に小さい値

1⇒検索値の次に大きい値

今回使用するの-1、検索値の次に小さい値です

以下の表で言えば、もし売上が30なら30より小さい値の0が該当し、前期比が15%であれば、10%が該当します

つまり、2.で行ったXLOOKUP関数の組み合わせに、検索方法-1を組み合わせれば2次元でのランク付けは、簡単に自動化できるのです

式は以下のようになります

=XLOOKUP(C5,$H$4:$H$6,XLOOKUP(D5,$I$3:$K$3,$I$4:$K$6,,-1),,-1)

企業ではABC分析の延長で、2つの変数(例:売上、利益率)で事業や商品を評価する機会が多いので、このXLOOKUP関数の組み合わせを行う機会は多いと思います

<まとめ>

今回は大きく分けて2つの事を解説しました

ⅰ)XLOOKUP関数を行列、双方向で組み合わせる(スピるの組み合わせ)

ⅱ)XLOOKUP関数の検索方法を工夫し、自動でランク付けをする

このⅰ)ⅱ)ができれば、データ集計がとても楽に行えます

ぜひ、有効活用していきましょう!

このブログではFILTER関数など、XLOOKUP関数と同じ新たなタイプの関数の解説も行っていますので、そちらもぜひ参照してみてください

尚、XLOOKUP関数が使えるエクセルバージョン(2020年9月19日時点)は限られており、OFFICE365ユーザー向けとなっています

2019バージョンユーザーなどは使用できないので、その点はまだ不便ではあります

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


にほんブログ村

エクセルにもテーブルマナーを!~関数不要の集計術~

 ビジネスにデータはつきものです。現在のような巷にデータ溢れている時代であれば、エクセルでデータを扱えて当然だと思われることも多いと思います。

 でも、エクセルが苦手な人には、データ集計を行えといっても苦痛だと思います。

 明日の会議までに資料を作成しろ、と言われても関数一つ知らねーよ!!

という方の為に、奥の手がエクセルにはあります。それがテーブル機能です。

エクセル内の表をテーブルにしておけば、色々と面倒な作業が簡単にできるのです。

 今回は、フィルター機能を使って抽出条件を変えながら、合計と平均を集計する方法を解説します。

 ちなみに、今回使用するデータは”年度別”、”製品別”、”地域別”、”売上金額”のデータですが、地域別の合計金額と平均を計算しましょう!

1.まずは表をテーブルに変換しましょう

 表のどこかにカーソルを置いて”Ctrl+T”を押しましょう

 すると次のようなポップアップが出るので”先頭行をテーブルの見出しとして使用する”にチェックを入れてOKボタンを押しましょう!これで表をテーブルとして使用できます

2.集計行を作成しましょう!

 画面上のデ―ブルデザインタブで”集計行”にチェックを入れましょう!

この集計行にチェックを入れる作業だけで、既に売上金額の合計(382,666,000)が計算されています!

3.フィルターで抽出範囲を変える、集計行を平均に変える

 フィルターで地域の抽出範囲を変えただけで合計金額が変わっているのが分かりますでしょうか?このテーブル機能の集計行は完全にフィルターの集計結果と連動していますので、様々な計算結果を得られます

 では最後に、”平均”を計算して見ましょう!

実は、集計行の▼マークをクリックして”平均”に変えるだけで計算できます

<まとめ>

 エクセルには、日本ではまだあまり知られていない便利機能が沢山あります!エクセルビギナーも、どんどん便利機能を活用して、せっかくのエクセルを有効活用しましょう!

 テーブル機能をマスターしたら、次はピボットテーブルです!テーブル機能からエクセル集計をはじめることで、集計技術の幅がよりいっそう広がっていきます。ぜひテーブル&ピボットテーブルの特集記事も参照してください

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 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~転記・編集作業の改善_元データに無い項目追加~

前の回で、ピボットテーブルで作成した表の見た目が随分変わりました

9

今回は導入の回で紹介した内容のうち「元データに無い項目の追加」を解説します!

!今回のポイント

「 元データに無い項目の追加 」では以下の2つの作業を行います

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

ピボットテーブルの表にカーソルを置くと、エクセル画面の上に「ピボットテーブル分析」タブが表示されます

このタブの中に「フィールド/アイテム/セット」ボタンがあります

フィールド

このボタンにて開いた画面で、売上金額を「百万円」にて割った値を表示するフィールドを作成します

ボタンを押して開いた「集計フィールドの挿入」画面の数式欄では、計算式(売上金額÷百万円)を入力できるようになっています

11

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

ピボットテーブルの表の上にカーソルを置き、右クリックすると「計算の種類」が表示されます

この「計算の種類」画面では、様々な種類の集計列を挿入することができます

計算の種類
計算の修理

今回は、計算の種類の中の「基準値との差分の比率」の集計列を表の中に挿入します

集計データが売上金額の2017年度と2016年度であれば、基準値を2016年度をとして差分の比率を算出します

ここで示す差分とは、基準値が2016年度であれば、売上金額の2017年度から2016年度を引いた値になります

実際の設定画面では、基準値については、データの中味(年度)が変わっても前期比が計算できるように「次の値」(もしくは前の値」と言う形で設定します

次の値とは、ピボットテーブルの表にて左から<2017年度>⇒<2016年度>の順で奈良でいれば<2016年度>が次の値です

左から<2016年度>⇒<2017年度>の順に並んでいれば<2017年度>が次の値です

こちらについては後程、詳細に解説します

!詳細解説

実際に解説に使用するデータのダウンロードをお願いします

アイコン

サンプル 28.28 KB 28 downloads

...


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

①集計フィールドの挿入画面を開く

前述の通り、ピボットテーブル画面のタブから集計フィールド画面を開きます

②作成するフィールドの名前を設定

名前欄に「売上金額(百万円)」を入力します

14
集計フィールドの挿入

③数式にフィールドの値をセット

該当のフィールドを選択し、画面下にあるフィールドの挿入を押すと、数式欄に「売上金額」がセットされます

GiF7
数式を設定

④新フィールドの追加

数式欄にて割り算を入力しましょう

割り算を入力したら追加ボタンを押して下さい

GiF8

①~④を行うと円⇒百万円に単位を変えた新フィールドを作成できます

新フィールドを作成したら、エクセル画面の右側(フィールドリスト)では、単位百万円の新フィールドが選択できるようになっているのを確認できます

GiF9


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

①計算の種類の作成画面を開く

前述の通り、ピボットテーブルの表上で右クリックし、「計算の種類」を開きます

② 基準値との差分の比率

基準値との差分の比率

③差分の比率の計算方法

次に以下の画面が出てきますが、基準フィールドは地域から「年度」に変更し、基準アイテムは「次の値」を指定します

15
計算方法の設定

①~③を行うと、2016年度を基準とした前期比が表に挿入されています

GiF10.gif
差分の比率の反映

ここで基準アイテムを「次の値」にする理由を説明しておきます

今まで紹介したピボットテーブル表の年度は降順で表示されています

つまり、左から前:2017⇒次:2016の順になっていますので、基準値になるのは2017年度の「次の値」の2016年度です

ですので「前の値」ではなく「次の値」にします

ちなみに、2016年度も以下の画面のように基準アイテム欄から選べるようになっています

基準アイテムの設定

ただ、データが変わった場合には、基準アイテムを固定しておくと色々と不都合が生じます

仮にデータが(2016年度と2017年度のセット)から(2017年度と2018年度のセット)になった場合には、2016年度がデータセットになくなるのでエラーになります

ここで、試しにピボットテーブルの表の年度表示を降順から昇順に変えてましょう!

つまり、左から前:2016⇒次:2017の順になっていますので、基準値になるのは2017年度の「前の値」の2016年度です

ですから、基準アイテムは「前の値」を指定します

GIF11

<まとめ>

今回は2つのことを行いました

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

   集計フィールドの挿入画面で売上金額の単位変更を行いました

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

   計算の種類画面で前期比の列を挿入しました

この2つの画面を有効活用になるとレポート力は格段に向上します

ピボットテーブルで集計したデータを他の表に転記して再編集したり、再計算するといった作業が必要なくなります

では次回はピボットテーブルの表に信号を挿入します!

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


にほんブログ村

新ピボットテーブル術1~転記・編集作業の改善_見た目向上~

前回は導入としてこの「転記・編集作業の改善シリーズ」で行うことの概要を解説しました

今回は前回紹介した内容の内、以下の内容に取り組みます

A.表の見た目を変える

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

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

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

いずれの内容も小難しい処理はなく、基本的にはクリックで処理を進めていくことができます!

ぜひ、サンプルファイルをダウンロードして実際に手も動かしてみましょう!

アイコン

サンプル 28.28 KB 28 downloads

...

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

下のGIFのようにピボット―テーブルの表の「「2017」にカーソルを置き、右クリックします

そして、「並べ替え」⇒「降順の順」でクリックしていきましょう

GIF2

年度の並び順(2016→2017)が(2017→2016)に変わっているはずです

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

ピボットテーブルの表をそのままレポートとして活用できない理由の一つがこのフィルターマーク▼の存在です

このマークは見栄えが悪いですが、簡単に取り除くことができます

カーソルをピボットテーブル表に上に置き「ピボットテーブル分析」タブをクリックしましょう

すると右側に「フィールドの見出し」というテキストが見えるはずです

この「フィールドの見出し」をクリックすれば▼マークは消えます!

GIF3

(参考)形式を表形式に変える

今回はあくまで参考としての説明になりますが、行などが2段になっている場合には、段の表示をどのようにするのかを選択することができます

今回は段をコンパクト型式から、列を分割して表示する「表型式」にしておきましょう

GIF4

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

ピボットテーブルの表の上にカーソルを置いたうえで、デザインタブの総計リボンに「列のみの集計」を選びましょう

GIF5.gif

<まとめ>

今回は、フィルターマーク▼を取り除くことをはじめとした、ピボットテーブルの表の見た目を変える操作について解説を行いました

今回は詳細に解説しませんでしたが、ピボットテーブルの値の表示形式を桁区切りをしておくことも念のため、付け加えておきます

実はまだ見た目を変えなくてはならないところがありますが、それはもう少し表全体が固まった後の4回目5回目の解説で行います!

それでは次回は、ピボットテーブルの売上金額の表示単位の変更(円→百万円)と前期比・列に追加について解説します!

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

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

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

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

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

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

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

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

ピボットテーブル

さて、

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

6

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

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

A.表の見た目を変える

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

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

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

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

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

⇒解説記事:4回目

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

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

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

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

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

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

⇒解説記事:3回目

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

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

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

ピボットテーブルの役割を変えよう

ID:lqt6mq

ピボットテーブルの使い方を覚えると、エクセルでの”データ集計”の光景が変わってきます

見出しがある表さえ作成すれば、見出しの項目を軸にしてあらゆる集計が可能になります

1

但し、あまりに機能が多すぎるので”ピボットテーブル”を活用し切れていないケースも多く見られています

・毎週や毎月の実績を報告するのにピボットテーブルでデータ集計を行った後に、転記して新たに表を作成していませんか?

2

・毎回、同じような資料(例:部門A向け、部門Bむけ、など)を何枚も、何シートも作成していませんか?

・毎週、もしくは毎月作成しているグラフのデータ元として、ピボットテーブルで集計したデータを転記していませんか?

・ピボットテーブルのデータソースが更新されたら、データ範囲を指定し直して更新処理を行っていませんか?

ぜひ、ピボットテーブルの役割自体を変えて”転記不要”、”更新不要”にしてエクセル処理の効率を飛躍的に向上しましょう!

それでけでなく、<数字の見た目>’も’向上させましょう!

やはり、数字も見た目なのです

ピボットテーブルに備わっている機能を使いこなせば、”役割の変更”はとても簡単に行えます

例えば、単位の変更もピボットテーブル上で行えます

3

では次回から詳細を解説していきます