タグ別アーカイブ: ピボットテーブル

1つのピボットテーブルから複数レポートを作成する方法

【今回はピボットテーブル・グループ化機能の裏技を解説します】

複数のレポートを作成するのに、1つのピボットテーブルをコピーして使い回したりしていませんか?

今回は、ワンクリックで1つのピボットテーブルから複数レポートをシート別に作成する裏技を紹介します

今回使用するデータ

今回使用するデータは次の画像のデータです

 カテゴリーには「かつ丼」の他に「牛丼」「親子丼」があり、日付別にカテゴリー別の売上個数のレポートを作成します

日付は20以上あるので、手動だと日付別のレポート作成はかなりの時間を要します

事前準備

今回の場合だと、下の画像のピボットテーブルから日付別のレポートを作成します

複数レポートを作成する準備として、フィルター欄に日付フィールドを配置します

複数レポート作成

レポートフィルターページの表示

まず、ピボットテーブル上にカーソルを置いたまま「ピボットテーブル分析タブ」を選択し、下の画像の「黄色の印の箇所/三角マーク」を上から順番にクリックします

すると「レポートフィルターページの表示」というテキストが見えますので、こちらをクリックします

実行

「レポートフィルターページの表示」をクリックすると、次のようなダイアログボックスが表示されます

こちらのダイアログボックスで「日付」が選択されているのを確認したらOKボタンを押します

すると、次のGIF画像のように日付別のレポートがシート別に作成されます

<まとめ>

今回は、1つのピボットテーブルから複数レポートを作成する方法を解説しました

ぜひ、実際に手を動かして試してみて頂きたいと思います

 先日紹介したグループをカスタマイズする方法を、今回紹介した「複数レポート作成」と組み合わせると、レポート作成作業がより一層効率化が図れると思います

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


にほんブログ村

ピボットテーブルでグループ化をカスタマイズする方法

【今回はピボットテーブル・グループ化機能の裏技を解説します】

ピボットテーブルにグループ化という機能があります

数値や日付でデータをまとめ直すことができるのでとても便利です

実は、文字列でもグループ化機能が使えます!

グループを文字列にて作成すれば、「マスタなし」のままでデータを独自にまとめ直すことができます

使用するデータ

地区別の注文金額をデータとして使用します

地区は上の画像のように「東京、名古屋、神戸・・・」という風に分かれています

こちらの地区の区分を東日本西日本にグループ化します

グループ化及び解除

グループ化

 グループ化を行う時には、数値や日付でグループ化する時とは違い、まずはグループ化の対象をCtrlキーを押しながら指定します

その後、右クリックして表示される「グループ化」をクリックします

すると、下の画像のようにグループ化対象として指定した箇所がグループ化されます

 上の作業で「東日本」にグループ化する「東京、仙台」をグループ1でまとめ直したので、次に「名古屋、神戸、大阪」をグループ2にまとめます

グループ解除

グループ化したものを解除する場合には、該当のグループ上で下の画像の「グループ解除」をクリックします

グループ名の変更

 前章のままだと、グループの名称がそれぞれ「グループ1」「グループ2」のままなので該当のグループにカーソルを置いて名称を変更します

尚、画面右の作業ウィンドウに目を移すと、グループ化したことにより新たに作成されたフィールドの名称が「地区2」となっています

こちらは、一旦、フィルター欄に配置します

その後、数式バーでフィールド名を変更します

<まとめ>

今回は、文字列から独自にカスタマイズしたグループを作成する方法を解説しました

この方法を知っておくと、わざわざマスタを作成してVLOOKUP関数で紐づけるなどの手間も不必要です

しかも、変更処理もグループ化の解除を行った後に、再度グループ化を行うだけなのでとても楽です

エクセルはとても進歩しているので、こういった裏技を知っておくと業務効率が違ってきます

また次回、ピボットテーブルの裏技を紹介していきます

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


にほんブログ村

ピボットテーブルに引いた罫線を維持する方法

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

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

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

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

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

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

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

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

デザインを新規設定

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

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

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

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

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

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

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

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

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

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

こちらは注意が必要です

<まとめ>

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

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

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

やはり、

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

とても勉強になります!


にほんブログ村

現場発のDXを応援!

実務・現場でのデータ活用を、豊富な経験とモダンエクセルを始めとした実践的なエクセル力で全力応援!

現場の言葉でデータを語ります

 コロナ禍をきっかけとして、ビジネスの現場はオフィスからリモート中心に移行しています。リモート・ワークへの働き方の移行は、実務・現場での「データ」との向き合い方を大きく変えようとしています。リモート・ワークでは、人間の勘ペーパー業務に依存せず、データを重視した働き方が求められています。実務の現場でデータ活用する際に問題となるのは、現場には企業のIT部門やトップコンサルタントの目が届かない、もしくは、理解が困難な世界があることです。Excellent仕事術は、これまでの豊富な経験を活かして実務の現場を明確に見える化し、データ活用の下地を作ります!

何よりもデータを最重視します

 データは事実を雄弁に語ります。データを有効に活用すれば、ビジネスは必ず成長し、個々の生活も豊かになるはずです。但し、データの活用の仕方では事実が正しく見えなくなったり、時間を膨大に浪費してしまい、成果が全くでないこともあります。Excellent仕事術ではデータを最重視し、実務の現場を正しい方向へと導きます!

これまでの豊富な経験を活かします

 Excellent仕事術・ガッツ鶴岡はこれまで、創業型のベンチャーから時代の寵児となった企業まで幅広い環境で働いてきました。業界も「ビックデータ重視」のEC通販から、「人間の勘重視」の食品業界まで、とても幅広く関わらせて頂きました。いずれの環境・業界でも重視してきたのは「データ」「エクセル」です。これまでの経験を活かして、新たなデータ活用の世界を生み出していきます!

新たなエクセル活用術を広めます

 エクセルが誕生してから、20年の月日が経ちます。エクセルはもはや表を作成するだけのツールではありません。データベース/パワークエリを作成したり、ダッシュボードなども作成することができます。

 Excellent仕事術では、これまで海外のエクセル活用事例も数多く研究してきました。海外ではエクセルの機能を実践的に活用している事例が沢山あります。Excellent仕事術では海外の事例を参考にしながら、モダンエクセルなども活用した「初心者から上級者」まで幅広く活用できる「実践的なエクセル術」を研究しながら広めていきます

全力で汗を搔きます

 実務の現場で重要なのは「汗を搔いて体験すること」です。理屈だけでは現場は簡単には変わりません。まずは自らが変わり、率先して「汗を搔く」を重視して実務・現場の「データ活用」を応援していきます!

お仕事の依頼

エクセル新機能一覧

モダンエクセル

モダンエクセルのメインは、Power Queryという技術になります!

 多くのプログラミング言語学習において、まずは最初に「Hellow Word!」を表示するとこから体験します!

Arduinoなどのマイコン工作では「Lチカ」体験です

Power Queryでは、次のような「ETL」体験からスタートです!

Extract ➡ データの抽出

Transform ➡ データの変換

Load ➡ データの読込

まずは、1度「ETL」を体験してみませんか!

必ず、今までは感じなかった新たな発見があるはずです!

初級講座 :データの取得(抽出など)、読込など

中級講座:データの結合などの変換

上級講座:縦横並びの変換などの特殊な変換処理

コラム:PDFファイルをエクセルに変換するなどのテクニックの紹介

M言語

エクセルダッシュボード

 読者の方には、エクセルで数値資料を作成する機会が多い方もいらっしゃると思います。

 実は、数字は見た目です。そして、第一印象で決まります。特に、一目で「数字が細かく詰まっている」と分かる資料はまともに見てもらえません。そして、その資料作成には、膨大な労力がかかってしまっています。

 では、最小の労力で、最大の効果を発揮する資料を作成するにはどうしらいいのか?、その答えは、このダッシュボード術の中にあります。このダッシュボード術は、まるで車や飛行機のコクピットの中にいるような感覚で、一目で【数字を魅せる資料】を作成します。

 このダッシュボード術は、VBAの技術はあまり必要としません。関数の使用も、ごく簡単な関数の組み合わせのみです。一番必要となるのは日本ではあまり紹介されたこなかった、新たなエクセル機能の活用です

エクセルダッシュボード

オプションボタンを活用したダッシュボード

グラフデザイン

 人は見た目が9割と言いますが、グラフも同じです。このコーナーでは、伝わりやすいグラフデザインを手軽に作成できる手法を解説します。グラフデザインの種類は「スピードメーター」「温度計」「画像入り」など、実に様々なデザインを揃えています!

手っ取り早くテンプレートを使って、グラフを作成したいという方のために、テンプレートを集めたコーナーも設けています!

 ⇒グラフデザイン

新ピボットテーブル術~レポート力向上~

 ピボットテーブルはデータを抽出する為の機能として良く知られています。表は別に作成しておき、ピボットテーブルで抽出した数字を、別途作成しておいた表に転記するような使い方をよく目にします。

 実は、ピボットテーブルから直接、魅力的な表作成を行うこともできるんです。下の画像のような、魅力的な表も、ピボットテーブルから直接作成できます。このコーナーでは、テーブル機能とあわせて、ピボットテーブル本来の力を活かした技術を紹介していきます。

新ピボットテーブル

エクセル裏技

 エクセルには日本ではまだあまり知られていない機能が沢山あります。下のGIF画像をご覧ください。実はエクセルでタイマーも作れてしまうのです。しかも、ほんの数行のコードを書くだけで作成することができます。この位はまだ序の口です。このコーナーでは、隠れたエクセルの裏技をどんどん紹介していきます。そして、どんどん業務の省力化と、差別化を行っていきましょう!

 ⇒裏技

VBA

いよいよプログラミング教育が日本でも本格化しています

このExcellent仕事術では、これまでになかった手法でエクセルVBAを広めていきます

RPA 

 RPA(ロボティクス・プロセス・オートメーション)が急速にビジネスの現場に浸透してきています。

 人間が行っていたエクセル作業をRPAが代わりに行ってくれるから⇒これからはあんまりエクセルの技術習得にそんなに力を入れなくていいや・・・そんな風に考えている方もいらっしゃるかと思います。

 実はRPAの浸透により、逆にエクセルの存在感が増してきています。その理由は、“RPAはデジタルデータにより動く労働者”、という点にあります。デジタル労働者はデジタルデータがないと働きません。

 多くの業務の現場では、デジタルデータは、エクセルにより作成されています。つまり、RPAをエクセルとどう連携するかが、RPA活用の鍵になっていきます。

 このコーナーではこの”RPAとエクセルとの連携”をテーマに、各種連携手法を紹介していきます。⇒RPA


アプリ/アドイン

 エクセルでもスマホのように、アプリ/アドインをダウンロードすることができます

 ダウンロードできるアプリの内容は、グラフを自動作成するものから、長い数式を解読するものまで多岐に亘ります

ぜひ、下の記事を参照してご自身のニーズにあったものを見つけてみてください

アプリ

エクセル分析

エクセルには、ソルバーをはじめとする優れた分析機能があります

 ソルバーは、人間が手動で計算したら2、3日は要するようなシミュレーションを一瞬で終了してくれる優れものです

 このブログでは、エクセルの便利機能を組み合わせて、「ABC分析を効率的に実施する方法」などの最新のエクセル分析術を紹介していきます!

エクセル分析

エクセルと英語力

実は、英語力とエクセル力は相関関係にあります

エクセルを作成したのは、英語圏の企業です

エクセルを使う上で、英語の概念が必ず登場します

ですので、英語ができた方がエクセルの上達も早いです

 そもそもエクセルと英語、どちらもビジネスをしていく上ではとても重要なスキルです

 このブログでは、英語力の向上について貢献できるような記事をアップしていきます

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

にほんブログ村

ピボットテーブル

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

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

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

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

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

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

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

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

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

   解説記事は⇒こちらから

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

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

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

   解説記事は⇒こちらから

3.複数資料の作成

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

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

   解説記事は⇒こちらから

4.更新処理

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

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

   解説記事は⇒こちらから

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

分析からダッシュボード作成を体験する講座

【実践的エクセル力向上】エクセル便利機能をうまく活用する力➡エクセル力をあげてワンランクアップ上の仕事ができるようになりましょう!

お申込み

  *ストリートアカデミーのページに遷移します

エクセルの進化は凄まじいものがあります

驚くほど便利な機能が備わっています

重要なのはそれらの便利機能を「知る」かどうかです

「便利機能を知り、うまく便利機能を組み合わせて実際の仕事を効率化する」、それがエクセル力です

 今回のレッスンでは目から鱗の便利機能に沢山触れて頂き、エクセル力を上げる勘所を掴んで頂きます

エクセル力事例

エクセル力の2つの例を紹介します

1つ目はABC分析のランク分けです

従来のABC分析の場合には、ランク分けは次の3つの手順で行っていました

①データを降順で並べ替え➡

②ランク分けの基準作成➡

③IF関数で基準毎にランク分け

 この方法の場合は「ランク分けの基準を変更したり」「データを追加」した場合には①~③をまた繰り返さねばなりません

 今回のレッスンでは「VLOOKUP関数の隠れた便利機能との組み合わせ」により、この面倒なランク分けを自動化します!

 上のGIF画像では、画像上の表にて、ランク別に上位の比率を変更すると、元データのランクが自動的に変更されていきます!

2つ目は双方向グラフです

 このグラフデザインだとABCのランク別に、販売個数と商品数の比較が行い易くなります

一見、難しそうですが、グラフの標準機能を組み合わせるだけで行えます

実は、グラフの軸は反転できるのです!

この「軸の反転」機能を中心に双方向グラフの作成を体験して頂きます

 最終的には、今紹介した2つの事例等を組み合わせて、下の画像の様なダッシュボードの作成を体験して頂きます(全てのコンテンツを一から作成するわけではないので、その点は予めご了承お願いします)

 ダッシュボードの作成では、「スライサー/選択」をはじめとするピボットテーブルの有効活用方法についても解説させて頂きます

レッスンの特徴

個別にマンツーマンでレッスンを行います

 事前に個別に課題や要望等をすり合わせさせて頂き、レッスンプログラムについては調整を行わせて頂きます

 尚、レッスンは体験という位置づけであり、ダッシュボードのコンテンツを全て一から作成・演習するわけではない旨を予めご了承お願いします

では、レッスンでお会いしましょう!

 このレッスンを通じて飛躍的にエクセル力を向上させる機会になるようにしましょう!

お申込み

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

にほんブログ村

複数データの組み合わせ/クエリのマージ~中級編1回目~

今回から、中級編をはじめます

初級編では、データを取得してエクセルシートに読み込むだけでした

中級編では、取得したデータを編集・加工していきます

このデータ編集・加工手法を身に付ければ、様々な箇所から集めた大量のデータを関数やVBAを使わないでも、サクッと一括処理ができるようになります!

次回から2回は、関数やVBAを使用した場合には作成に手間がかかりそうな表を サクッと2つの表を組み合わせるだけで作成する方法を紹介します

今回は、次の回から主に使用する技術の概要だけ紹介しておきます

1.クエリのマージ

イメージとしては下の図のようなことを行います

関数で言えば、VLOOKUP関数も同じような事ができますが、このPower Queryのマージには、VLOOKUP関数で処理する時と比べて次のようなメリットがあります

・他の表から抽出する項目が多く、データ自体が大量な場合

 ⇒ワンクリックで処理できる

・VLOOKUP関数の場合は参照する値の位置が左にないと使用できなかった

 ⇒位置は関係なく処理できる

ちなみに、Power Queryにて実際にマージを行う画面は、以下の画面になります

2.計算処理

Power Query上では、2つの表を組み合わせた上での計算処理が、とても簡単に行えます

計算処理がもし±X÷を行う四則演算であれば、対象となる列を指定するだけで計算処理が行えます

SUMIFやSUMIFSなどの、関数であれば条件設定が伴う、少しややこしい計算も、下の図のような画面にて、直感的に計算処理が行えるようになっています

では次回から2回に分けて、1.クエリのマージ、2.計算処理を中心に解説を行って行きます!

https://analytic-vba.com/power-query/intermediate/merge-query/
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

クエリの追加作成~中級編4回目~

こんにちは、Excellent仕事術ガッツ鶴岡です

前回は項目が違う2つのクエリをマージ(組み合わせ)ました。今回は、今回はあるクエリに、項目が同じだけど別のクエリを追加する方法を紹介します

加えて、実践的なPower Queryの活用方法として、ピポットテーブルとの連携方法も紹介します

ピボットテーブルは便利なのですが、取得元のデータに追加があった場合には少し面倒ですよね

例えば、売上の集計データを毎月報告するケースなどです

1月のデータに、2月のデータを足し、取得範囲を修正する・・・という処理が毎月続きます

今回紹介するクエリの追加機能とピボットテーブルを連携させれば、そんな悩みは一発で解決します!

解説する内容の概要ですが、下の図のようにサンプルファイルにある1月の売上データに、今回の解説の中心である「追加クエリ」機能を通じ、2月の売上を追加してピボットテーブルを新たに作成します

そして今回の次(中級編5日目)の回では、更に”追加クエリ”機能を通じて、ピボットテーブル側では特にデータ範囲の追加は行わないまま、3月の売上データを追加します

<今回のポイント>

 今回の解説の中心となる”追加クエリ”機能自体は特段難しいところはありません 

 ただし、追加クエリを行うパターンが以下の①②の2つに分かれる点は意識しておいてください

①と②の違いは、2つのクエリから新たなクエリーを生み出すか、そうでないかの違いになります

では、サンプルファイルをダウンロードしたら、具体的な解説に入りましょう

1.各シートからクエリーを作成

サンプルファイルの各シートが”各月”のデータになっています

画面上のデータタブから”テーブルまは範囲から”をクリックして、各シートのデータからクエリを作成しましょう

クエリ作成時は以下の2つを指定してください

➀読込先 ⇒接続専用

➁クエリ名 ⇒シート名

2.クエリの追加_1回目

1.が終了したらシートの右側が、以下の画面のような状態になっているはずです

もし、”クエリと接続”が出ていない場合には、画面上のデータタブから”クエリと接続”をクリックしましょう

画面の状態が確認できたら、以下➀⁻③の処理を通じてクエリ_2020年1月にクエリ_2020年2月を追加します

前述の<今回のポイント>の①のパターン(⇒新しいクエリを作成)で追加処理を行います

➀クエリ_2020年1月上で右クリック⇒クエリ追加

クエリ上で右クリックをすると”追加”が選択できます

➁追加するテーブルを選択 ⇒2 020年2月を選択

➀の処理を通じて追加画面が立ち上がったら、追加するテーブルを選択してOKボタンを押します

③読込先をピボットテーブルを指定する

➁の後に、Power Queryエディターが立ち上がります

画面の右のプロパティでクエリーの名前を”売上データ”に変更したら、今度は画面右上で”閉じて次に読み込む”を選択します

次に”データのインポート”画面が立ち上がりますので、”ピボットテーブルレポート”を選択してください

ピボットテーブルの読込先を既存のシートにするか、新規のシートにするかを選択できますが、ここでは新規のシートを選択します

2つの選択処理を行い、OKボタンを押すと、1月と2月のデータが含まれたピボットテーブルが作成されます

3.ピボットテーブルの設定

2020年1月と2月の両方の月のデータが含まれていることが分かるように設定しましょう!

行ボックスに出荷日、値ボックスに金額をドラックします

行ボックスに”出荷日”とあわせて”月”も出現したら、出荷日は行ボックスから外しましょう(エクセルのバージョンによって多少、違いがある可能性があります)

そうしたら、以下のようなピボットテーブルになっているはずです

<まとめ>

 今回はあるクエリに別のクエリを追加する処理を行いました。この時には2つ方法があり、新たなクエリを作成する方法と、そうでない方法があるのですが、今回は前者で行いました。

 また、クエリをPower Queryエディターから読み込む際に、読込先にピボットテーブルを選択しました

 このようにPower Queryとピボットテーブルを連携させることで、ピボットテーブルの元データを自由に操作できるようになります

 次回はPower Queryを通じて、ピボットテーブルに新たなデータを加えます

 ピボットテーブルについては「ピボットテーブルの役割を変える」と題し、別途シリーズで記事を書いています

ぜひ、そちらもご参考にしてください

        ⇒ピボットテーブル記事

 

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

にほんブログ村

スライサー特集

ピボットテーブルを使う上で、スライサーは欠かせない存在になりつつあります

ところが、参考文献は日本語では少ないのが現状です

ですので、これまでスライサーについて書いた記事を以下、紹介します

1.タイムライン&スライサー

2.スライサーの表示順を変える

.オシャレなスライサーの使い方

4.オシャレなスライサーの使い方2

5.スマホのようなダッシュボード作成

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

にほんブログ村