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

エクセル新体験

エクセルをDXの主役に!

モダンエクセルの必要性

 読者の皆さんは使い慣れたエクセルで、データを有効活用していけるのが一番効率がいいと思っていませんか?

どんな大企業でも、システム部門が管理できないエクセルファイルが無数に存在するものです

エクセルで行う業務については、ユーザー自身で改革を進める必要があります

何より、エクセルはとても便利です!

 エクセルはデータを手軽に活用する為のツールが揃っており、しかも低コストです

但し、エクセルにてデータの有効活用を進める上で、1点だけ問題があります

それは、「データの一元管理」の問題です

この問題は長年、エクセルユーザーの頭を悩ませてきました

 ところが、新たに登場したモダンエクセルの仕組みを活用すれば、エクセルによるデータの一元管理の問題はクリアできるのです!

 モダンエクセルの仕組みを有効活用して、「エクセルでDXを進めていこう!」これがこのブログのメインテーマです!

モダンエクセルとは?

DXの基本は「データの一元管理」です

 そして、一元管理したデータを「見える化」した上で、「業務改善」「データ分析」に活かし、最終的には「新規ビジネスの創出」に活用できるのが理想です

 エクセルではデータを視覚化するグラフが作成でき、業務を自動化するマクロも作成できます

そして、統計処理の機能もありますので、データ分析もできます

データを魅せるダッシュボードだって作成可能です!

ところが

 エクセルで大量のデータを処理しようとすると、どうしても「シート間」にてデータのバケツリレーが発生するのです

 このシート間での「データのバケツリレー」はファイル間にまで拡大し、最終的にはフォルダ間にまで拡大してしまいます

 エクセルは本来は表計算ソフトであり、大量のデータを扱う、データベースソフトではありません

 エクセルの1シートに入る行数も104万行の制約があり、シートを分けながら使用する前提で作成されているのです

しかし、

このバケツリレーの問題はモダンエクセルの登場で状況は変わりました

 モダンエクセルでは、データの単位は「シート」ではなく「テーブル」になります

この「テーブル」は互いに結合可能な「ブロック」だと思ってください

 このテーブルというブロックは、子供のブロック遊びのように、直感的なクリック操作にて結合が可能です!

 しかも、このテーブルは「接続の作成のみ」という手法で管理することができます

 実は、エクセルはシート別に複数作成されたXML型式のファイルで構成されています

これが、モダンエクセルの重要なポイントの一つです

 エクセルのセルに入っているデータは表示されているだけであり、実はデータ自体は別の場所に保管されているのです

 この「接続の作成のみ」というデータ管理手法により、従来のエクセルでは不可能だった「104万行」以上のデータを、1か所で管理できるのです

 このモダンエクセルの仕組みを有効活用すれば、エクセルユーザーは「データの一元管理」の問題から解放されます

 そして、メインツールを「エクセル」にしてDXを推進していくことが可能になります!

エクセル新体験

エクセルが誕生してから、20年以上の月日が経過しています!

エクセルの進歩は、モダンエクセルだけではありません

 ガラケーに代わって、スマホが登場した時のような衝撃を受けるような機能が続々と登場しています

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

ところが、日本ではこれらのエクセルの新機能があまり紹介されていません

このブログでは、これらの新機能の素晴らしさを伝えていきます!

ところで、

読者の皆様はブルース・リーをご存知でしょうか?

ブルース・リーはカンフーの神様、先駆者と呼ばれ、ハリウッドで大活躍し、未だに多くのハリウッドスターに尊敬されている香港生まれのアクションスターです

そのブルース・リーがこんな言葉を残したそうです。
Don’t think.Feel!(考えるより、まずは感じること)

ぜひ、このブログでエクセル新機能の素晴らしさを体験してみてください!

ETL体験のススメ / パワークエリ

エクセル新機能一覧

モダンエクセル

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

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

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

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

Extract ➡ データの抽出

Transform ➡ データの変換

Load ➡ データの読込

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

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

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

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

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

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

M言語

エクセルダッシュボード

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

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

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

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

エクセルダッシュボード

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

グラフデザイン

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

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

 ⇒グラフデザイン

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

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

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

ピボットテーブル

エクセル裏技

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

 ⇒裏技

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へ

にほんブログ村

ピボットテーブルの役割を変えよう Part4~更新は不要3~

前回はファイル内にあるピボットテーブル他も含めて自動的に更新するイベントプロシージャ―(VBA)の書き方を紹介しました

今回は別の方法2種類紹介します

1.ファイルを開いた時の更新されるようにする

前回の手法だと、コードを短いとは言え、直接書かなくてはいけません

今回はチェックボックスにチェックを入れるだけで済む方法を紹介します

15

まず、ピボットテーブル上で右クリックして“ピボットテーブルオプション”をクリックします

16

そして、データの箇所をクリックし、”ファイルを開くときにデータを更新する”にチェックを入れます

こうしておけば、ファイルを開いた時にはデータ更新がされています

前回、紹介した方法でも”絶対確実”に更新されるとは言い切れません

(カーソルの操作の関係など)

こうしておけば、安心ですね

2.個別に自動更新するピボットテーブルを指定する

まず事前にピボットテーブルには分かり易い名前をつけておきましょう

17

そして、前回同様にシート名の箇所で右クリックしてコードを表示しましょう

19

以上です

前回までとあわせてピボットテーブルを自動更新する方法を解説しました

エクセルを活用する時に、意外な便利方法があるものです

貪欲に吸収して有意義な時間の使い方を行えるようにしていきましょう!

また次回!

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 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へ

にほんブログ村

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

 今回紹介するスライサーは、ピボットテーブルの補助機能として使われるのが一般的だと思います

 今回は、ピボットグラフとスライサーをうまく組み合わせた、これまでとは一味違うスライサーの使い方を紹介します 

上のGIFを見るとスライサーが使われているようには見えないですよね?

まるで通常のグラフの一部かのように使われています

実は、このGIFにあるようなグラフは、以下2つの工夫だけで作成できてしまいます

1.ピボットグラフのデザインを変える

 デフォルトでは、ピボットグラフを作成した場合、ピボットテーブルのボタンがグラフ上に表示されてしまいます

 ですので、まずはボタンをグラフ上から取り除きます

2.スライサのヘッダーを非表示にする

スライサーは通常、以下のようなヘッダーが存在するデザインになっています

 このデザインではグラフとは一体になりません

 スライサーのヘッダーを非表示にすると、スライサーのように見えず、グラフと一体かのように見えるようになります

➀スライサーの上で右クリックする

”スライサーの表示設定”が表示されたらこれをクリックする

➁ヘッダーの表示のチェックを外す

以上、2つの工夫だけで、ピボットグラフのデザインは随分見違えます

実は、スライサーのデザインについては様々な設定が行えるようになっています。機会があればまた詳細に紹介していきたいと思います!

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

にほんブログ村

魅せるピボットテーブル

 多くの読者の皆さんにとって、ピボットテーブルに期待する主な役割とは、データを集計することだと思います

 実は、ピボットテーブルには日本では、まだこれまで知られていない機能が沢山あり、十分、”魅せる表”も作成できるのです

 ピボットテーブルに付随する”スライサー”機能を使うと、魅せる表を作成するだけでなく、データを”見たい切り口”に集計し直しながら、データを魅せることができます

 

 単に、スライサーを作るだけなら、とても簡単です

 ピボットテーブル上にカーソルを置いた後、挿入タブから”スライサー”を押すだけです

 スライサー自体を利用するだけなら、とても簡単なのですが、スライサー自体も魅せたい場合には、様々なテクニックが必要です

 詳細を知りたい方は、ぜひ専門コーナーの記事を参照してください

知れば知るほど”スライサー”の奥の深さに気づくと思います!

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

にほんブログ村