ピボットテーブルはとても便利です！
様々な切り口でデータを集計することができます
読者の中には「ピボットテーブルを使わないと、毎月の会議は無事に乗りきれない」という方もいらっしゃると思います
実はピボットテーブルの機能は「データの集計」だけではありません
データ集計以外の機能も有効活用すれば、集計後の業務を大幅に改善しつつ、表自体もプレゼンにそのまま使える「見やすい表」にすることができます！
１．転記・編集作業の改善
【改善前】集計データを別表に転記し、編集作業
【改善後】集計データから会議用の資料をそのまま作成
解説記事は⇒こちらから
２．グラフ作成作業の改善
【改善前】集計データを再度編集してグラフを作成
【改善後】集計データからそのままグラフを作成
解説記事は⇒こちらから
３．複数資料の作成
【改善前】集計データから部門Ａ、Ｂ用など同じような資料を作成
【改善後】スライサーで集計結果をフィルタリング
解説記事は⇒こちらから
４．更新処理
【改善前】データ更新の度にピボットテーブルを更新
【改善後】データ更新時にピボットテーブルを自働更新
解説記事は⇒こちらから
【Power Queryのパラメーターの仕組みを使い、複数のファイルから特定のシートのデータを可変で取得しよう】
Power Queryのフォルダにある複数ファイルから一括でデータを取得する仕組みは驚くほど便利です
この仕組みで大量の転記処理から解放された人も多いのではないでしょうか？
ただこの仕組みには一つだけ問題があります
ファイル内に複数シートがある場合や、シート名が変更になる場合です
今回の記事では、必要に応じてファイル内で取得するシート名を変更できる方法を解説します
今回使用するファイルには次の画像のように複数のシートが入力されています
このような複数シートがある複数ファイルを一つのフォルダに格納しておきます
こちらのフォルダを指定して複数ファイルのデータを一括で取得します
今回は過去の記事で解説したパラメーターを使用してシート名を指定します
今回は上の画像にある「sheetName」というパラメーターを作成し、こちらでシート名を可変で指定できるようにします
こちらも過去記事で紹介した方法です
簡単に可変の値でフィルターをかける方法です
一度、ダミーの値でフィルターをします
その後、前述のようにフィルターをかけた値に可変にするパラメーターを組み入れます
今回の処理では、不要な列の削除と行のフィルターを行う箇所が随所にでてきます
従来の「フォルダからデータを取得してエディタを開くフロー」と一部違う点があります
今回は上の画像の箇所ではなく、次の箇所から直接エディタを開き、シート選択のステップは省略します
エディタを開いて「適用したステップ」を確認すると、次の画像のようにステップが１つしかありません
エディタの中味はというと、次の画像のようにまだファイル内のデータを取得していない状態になっています
こここから、カスタム列作成画面でM関数を使用してデータを成型していきます
その前に、不要なファイルや列を削除していきます
～で始まるファイルはフィルター条件を指定して除いておきます
その後、「Content」「Name」以外の列を削除します
不要なファイルや列を削除したら、カスタム列作成画面でM関数を使用します
M関数：Excel.Workbookでファイル内のデータを取得できるようになります
次の画像が上記の「M関数を含むカスタム列」を挿入した後の画面です
ファイル内のデータを取得した内容を展開できるようになっています
こちらを展開処理します
ただ1回展開処理しても、まだ次の画像のようにデータ成型したと言える状態になっていません
ここから再び不要な列を削除した後、データ/Data列を展開処理します
ここから、データの1行目をヘッダーにします
その後は不必要な列は削除し、不必要な行はフィルターしておきましょう
*シート名の列は残しておきましょう
ここからはポイントの項で前述したパラメーターを作成します
上の画像の「新しいパラメーター」をクリックして次の画像の画面を開きます
現在の値には取得したいシート名を設定します
これでシート名を可変にするパラメーターは作成できました
その後、ダミーの値でシートをフィルターします
ここで数式バーに注目してください
ダミーでフィルターした値が数式の中に組み込まれています
こちらを設定したパラメーターで置き換えます
フィルターされたシートがData_202202➡Data_202201に変わりました
これで処理は終了です
試しにパラメーターの設定値を他の値に変えてみます
パラメーターの値に合わせてフィルターされるシート名が変更になっています
今回は複数ファイルの中にある複数シートの中から、特定のシートのデータのみを一括で取得する方法を解説しました
パラメーターとダミーでフィルターを行う仕組みを組み合わせると、意外と簡単にシート名を可変で取得する仕組みが構築できます
ただ、無駄な列の削除や不要な行のフィルター処理が多い点が難点です
この点さえクリアーすれば、一度作成したパラメーターを何度でも使い回せますので、シートの管理がとても楽になります
ぜひ試してみてください
【今回はピボットテーブル・グループ化機能の裏技を解説します】
複数のレポートを作成するのに、１つのピボットテーブルをコピーして使い回したりしていませんか？
今回は、ワンクリックで１つのピボットテーブルから複数レポートをシート別に作成する裏技を紹介します
今回使用するデータは次の画像のデータです
カテゴリーには「かつ丼」の他に「牛丼」「親子丼」があり、日付別にカテゴリー別の売上個数のレポートを作成します
日付は20以上あるので、手動だと日付別のレポート作成はかなりの時間を要します
今回の場合だと、下の画像のピボットテーブルから日付別のレポートを作成します
複数レポートを作成する準備として、フィルター欄に日付フィールドを配置します
まず、ピボットテーブル上にカーソルを置いたまま「ピボットテーブル分析タブ」を選択し、下の画像の「黄色の印の箇所/三角マーク」を上から順番にクリックします
すると「レポートフィルターページの表示」というテキストが見えますので、こちらをクリックします
「レポートフィルターページの表示」をクリックすると、次のようなダイアログボックスが表示されます
こちらのダイアログボックスで「日付」が選択されているのを確認したらOKボタンを押します
すると、次のGIF画像のように日付別のレポートがシート別に作成されます
今回は、１つのピボットテーブルから複数レポートを作成する方法を解説しました
ぜひ、実際に手を動かして試してみて頂きたいと思います
先日紹介したグループをカスタマイズする方法を、今回紹介した「複数レポート作成」と組み合わせると、レポート作成作業がより一層効率化が図れると思います
ちなみに、今回解説にしようしたエクセルのバージョンは次の通りです
【今回はピボットテーブル・グループ化機能の裏技を解説します】
ピボットテーブルにグループ化という機能があります
数値や日付でデータをまとめ直すことができるのでとても便利です
実は、文字列でもグループ化機能が使えます！
グループを文字列にて作成すれば、「マスタなし」のままでデータを独自にまとめ直すことができます
地区別の注文金額をデータとして使用します
地区は上の画像のように「東京、名古屋、神戸・・・」という風に分かれています
こちらの地区の区分を東日本と西日本にグループ化します
グループ化を行う時には、数値や日付でグループ化する時とは違い、まずはグループ化の対象をCtrlキーを押しながら指定します
その後、右クリックして表示される「グループ化」をクリックします
すると、下の画像のようにグループ化対象として指定した箇所がグループ化されます
上の作業で「東日本」にグループ化する「東京、仙台」をグループ1でまとめ直したので、次に「名古屋、神戸、大阪」をグループ2にまとめます
グループ化したものを解除する場合には、該当のグループ上で下の画像の「グループ解除」をクリックします
前章のままだと、グループの名称がそれぞれ「グループ1」「グループ2」のままなので該当のグループにカーソルを置いて名称を変更します
尚、画面右の作業ウィンドウに目を移すと、グループ化したことにより新たに作成されたフィールドの名称が「地区2」となっています
こちらは、一旦、フィルター欄に配置します
その後、数式バーでフィールド名を変更します
今回は、文字列から独自にカスタマイズしたグループを作成する方法を解説しました
この方法を知っておくと、わざわざマスタを作成してVLOOKUP関数で紐づけるなどの手間も不必要です
しかも、変更処理もグループ化の解除を行った後に、再度グループ化を行うだけなのでとても楽です
エクセルはとても進歩しているので、こういった裏技を知っておくと業務効率が違ってきます
また次回、ピボットテーブルの裏技を紹介していきます
ちなみに解説で使用したエクセルのバージョンは次の通りです
今回は、エクセルの特質を利用した裏技を紹介します！
下の図のように、1列にデータが規則的に並んでいるとします
この1列のデータを見やすいように複数列に展開します
この展開作業には関数やマクロは使用しません。エクセルの特徴を利用するだけです
１．セルの位置を2行だけ展開する
セル位置を、見出しの下に2行だけ記入していきます
「=」から始まる数式で記入しないのがポイントです
２．複数行をカーソルで指定して下にドラッグする
１．でセル位置を記入した2行をドラッグし、下にドラッグします
実は、この時点できちんと各セルの「セル位置」の指定がパターン化されています
３．「セル位置」を「=」で置換
ショートカットキー：Ctrl+Fにて置換え画面を立ち上げましょう！
置換を次のように実行しましょう
「セル位置」⇒「=」
すると２でパターン化された「セル位置」の指定に従って数式が展開されます
ちなみに、指定範囲の書式は「#,###」で指定してあります
この指定を行っておかないと、空欄を指定した数式が０を返します
試しに、参照もとの1列にデータを追加してみましょう！
０ではなく、追加された値を反映します
＜まとめ＞
今回は、文字列をパターン化してコピーしたものを、後から＝で置き換えることとにより、1列のデータを規則正しく複数列に展開しました
日常業務で使う場面が多いテクニックだとは思いませんが、「エクセルは工夫次第で様々なことができる」と感じて頂ければ幸いです
長文を最後まで読んで頂きまことにありがとうございました
今回は、Power Queryを使用してWebスクレイピングを行う方法について解説します。この方法を習得すれば、Webページの内容をエクセルシートに転記する手間が省けるようになります！
以前、Power Queryはスマホと同じだという記事を書きました
スマホではネットで好きなレストランを検索、つまり、レストランの情報を取得し、そしてスマホからそのまま予約の電話をすることができます
Power Queryも様々な箇所からデータを取得して、そのまま取得したデータを活用できます
実は、Power QueryではWebページの内容も取得できてしまうんです！
Webページに接続して、直接データを取得出来たら、次のようなWebページにある情報をエクセルシートに転記するような作業を削減できます！
・他社のIR情報の取得
・株価の取得
・その他のニュース記事
今回はある会社のIRページからPower Queryを使い、月次の数字を エクセルシート上に直接読み込んでみましょう！
Power Queryでは取得したいWebページのURLを入力しさえすれば、Webページのデータが取得できてしまいます
皆さんも馴染みのあるHPで試してみてください
今回の解説には、次ページを使用します
解説に使うページを確認したところで、詳細な解説をはじめます
では、まず最初にエクセル画面の以下の箇所をクリックします
すると、HPのURLを入力できるようになります
上の画面でOKボタンを押すと、ナビゲーター画面が立ち上がります
ナビゲーター画面に「10月月次データ」というテキストが表示されるので、そのテキストをクリックするとHP上の表が画面上に再現されます
HPから表を読み込めそうなことが確認できたので、このまま処理をすすめます
画面下で読みこみ先ボタンをクリックします
データのインポート画面が開くので、2か所指定します
・どのように表示するか⇒テーブル
・データを返す先⇒新規ワークシート
OKボタンを押し、実際に読み込まれたデータはこんな状態でした
見出しが2行だったのが1行になってしまっています
Power Queryでは必ずしも完璧に読み込めるということではなさそうです
ですが、一から手でHPから転記するよりは断然早いですよね！
Power Queryを覚える価値はありそうです！
今回はPower Queryを使用してHPのデータをエクセルシートに読込ましたが、前回はPDFファイルからも読み込んでいます
ぜひそちらも参照してみてください
こんにちは、Excellent仕事術のガッツ鶴岡です
この記事は、Power Queryというエクセルのスゴイ新機能をまだ知らない人向けに書いている記事です
「エクセルで大量のデータを扱っているけど、何とか作業を効率化したい」
「エクセルをぜひ得意分野にしたい」
という方はぜひページの最後までお付き合いください
ところで、読者の皆さんは、スマホを初めて操作した時にどんな印象をもちましたでしょうか？
「ネットでレストランを見つけてそのまま予約電話ができる！！」
「直観的で操作が分かり易い」
筆者はPower Queryを登場した時には同じぐらい、いや、それ以上の衝撃を受けました
今回は筆者が衝撃を受けたPower Queryの機能の一つ、PDFファイル内のデータをエクセルファイルにダウンロードする方法を、簡単に紹介していきたいと思います
ちなみに今回、紹介する内容は、今までPower Queryを使ったことが無い人でも取り組める内容になっています
本題に入る前に、サンプルで使用するPDFの中味について紹介させてください
サンプルのPDFファイルは2ページに分かれており、以下のように2ページ内に３つの表があります
・1ページ目⇒２つの表データ
・2ページ目⇒１つの表データ
今回の解説では、PDFファイルのデータをまるごとエクセルシートにダウンロード（読込）するのではなく、ファイル内の３つの表をそれぞれ３つのエクセルシートにダウンロードします！
それでは、本格的な操作について解説を始めます
解説に使用するPDFファイルはぜひ、事前にダウンロードしておいてください
データタブの「データ取得」から「ファイルから」⇒「PDFから」を指定します
次にファイル自体を指定します
取得するファイルを指定すると、２．で解説する「ナビゲーター画面」が開きます
（注）もし、「PDFから」のボタンが無い場合
PDFのデータをPower Queryで取得する場合には、２つの方法を選択できます
・PDFのページ全部を取得する
・ページ内のテーブルデータ（表データ）を取得する
上の２つの方法の選択は、取得するファイルを指定した後に開く、この「ナビゲーター画面」で行います
ナビゲーター画面では、ページ単位やテーブルデータ単位にて、取得するものを取捨選択することもできます
ナビゲーター画面では、左側にリストで表示される各Table（テーブルデータ）、もしくは各ページの内容を、下のGIFのように確認することができます
実際に取得する内容を決めたら、ナビゲーター画面の左上「複数のアイテムの選択」にチェックを入れた上で、取得対象にもチェックを入れます
今回の解説では、下の画像のように３つのTable（表データ）にチェックを入れます
取得対象データをナビゲーター画面で３つ指定しましたので、今度はダウンロード先を指定します
ナビゲーター画面下の「読み込みボタン」の横にある▼ボタンをクリックし、クリック後に表示された「読み込み先」のテキストをクリックします
「読み込み先」のテキストをクリックした後は実際の読込先、つまりダウンロード先を指定する画面が開きます
この解説では、読込先を指定する画面にて「テーブル」「新規ワークシート」の２つを指定します
この読込先の指定については、詳細な内容を解説した記事がありますので、そちらを参照してください
読込先として既存シートに読込を行ったり、ピボットテーブルで出力することも可能です
ここまで行えば、PDF内にあった３つの表データが下のGIFのように、３つのエクセルシートに分かれてダウンロードされています
ちなみに画面右に「クエリ」というものが３つできていますが、これはスマホ内のブックマークのようなものだと思ってください
バージョンによっては「PDFから」のボタンが出てきません
筆者は昔、予算管理の仕事をしていましたが、いつも複数シートの集計に苦しんでいました
月ごとの数字を管理するのに、ひたすらシート名＋シート名＋シート名のつるべ打ちをしていたのです
この方法だと手間がかかるだけでなく、シートを追加したり、削除した時にミスが起こり易いです
一方、今回の回で紹介する３D参照では、一気に複数シートをSUM関数で集計することができます
シート追加や削除した時にも、追加・削除に連動して集計値が変わりますので、集計ミスも防げます
下のGIFではシート追加前は200だったセルが、追加後は追加したシートの数字20が自動的に追加されています
この３D参照は特に難しい技術は必要なく、あくまで文法の問題だけをクリアすれば誰でも活用できます
それでは３D参照についてのポイントを２つ、以下に解説します
A.文法
以下は複数シートをシート名のつるべ打ちで集計する数式です
シート名とセル名を除くと同じパターンで文字が出てくるのが分かりますでしょうか？
＝’シート名’！セル名＋’シート名’！セル名＋・・・
今回の３D参照では、＋でシート名を足す代わりに「’と’！」の中で、通常のSUM関数と同じ様に”：”で範囲を指定するのです
こんな↓感じです
B.ワイルドカード＊
シート名を工夫した上での前提ですが、＊カードを使って更に複数シートの集計作業の効率化を行うことができます
例えば、シート名を四半期単位で集計できるように工夫しておきます
そうすれば、ワイルドカードを使った集計が行えるようになります
例えば、シート名が１Q_4月、1Q_5月、といった具合に並んでいる場合は、１Q以降を＊で置き換えます
このワイルドカードを使えば、SUM関数内に１つのシートでも指定すれば、後は必要箇所を＊で置き換えるだけですので、とても効率的です
ポイントを2点、文法とワイルドカード＊について解説したところで、ぜひ、以下のサンプルファイルをダウンロードして頂き、実際のデータで試してみてください
サンプルファイルは完成と演習の２つのシートがあります
演習のシートでは実際の操作の様子を参照できるように、２つの方法を用意してあります
どちらもフリーアドインを使用した方法です
①Web Video Player
このアドインはエクセルシート上で動画を開くことができます
演習シート上に記載されたURLをWeb Video Playerにコピーして使用してください
➁QRコード
演習シート上のQRコードをスマホで読み込んでください
動画が自動的にスマホ上で開きます
＜まとめ＞
今回は、複数シートの集計を、シート名＋シート名のつるべ打ちではなく、３D参照によりSUM関数で一気に集計する方法を学びました
３D集計は複雑ではないのですが、ひとつでも記述を間違えただけでもエラーになってしまいます
ですから、’や！の位置をひとつひとつ確認して頂きながら作業を進めていけばスムースに作業を完了させることができます！
後は、サンプルファイルを基にして、とにかく手を動かしてみましょう！
エクセルも新時代になり、スマホと同じ様にフリーのアプリを使えるようになりました
今回は筆者がおすすめのアプリ（アドイン）の使い方を解説します
なお、このページでは使い方の「概要」についてだけ紹介します
詳細な使い方については「フリーアドイン紹介」と題したエクセルファイルをぜひダウンロードして頂き、以下の方法のいずれかでYOUTUBE動画を参考にしながら、実際のデータで解説させてください
①QRコード⇒YOUTUBE動画
スマホをお手元にご用意頂き、エクセルシート上のQRコードをスマホで読み込みます
するとスマホ上にYOUTUBE動画が開きます
➁Web Video Player⇒YOUTUBE動画
QRコードを使ってフリーアドインの入手方法を解説した後に、フリーアドイン・Web Video Playerを入手して、エクセルシート上で動画を参照します
ダウンロードして頂くファイルは次のファイルです
では、お勧めアドインの概要解説をはじめます
１．フリーアドイン入手方法
ダウンロードしたファイルの1番左のシートを参照して解説します
まず最初にQRコードをスマホで読み込んで動画を開いてください
２．Web Video Prayer
ダウンロードしたファイルの2番左のシートを参照して解説します
まず最初にQRコードをスマホで読み込んでください
３．Bing マップ
3番目のシートを参照して解説します
まず最初にQRコードをスマホで読み込んでください
もしくは、URLコードをWeb Video Playerにコピー（Ctrl+V)して動画を立ち上げてください
このアドインは表にある地域別のデータを活用して、自動的に地図上でグラフを作成します
４．People Graph
4番目のシートを参照して解説します
まず最初にQRコードをスマホで読み込んでください
もしくは、URLコードをWeb Video Playerにコピー（Ctrl+V)して動画を立ち上げてください
このアドインで作成するグラフは、アイコンを使用しているので、聞き手に直観的に訴えかける良さがあります
＜まとめ＞
昔はPCの前に本を置いてエクセルの使い方をしていましたが、とうとうシート上に動画を展開して学習できる世の中になりました
紹介したアドインはいずれも無料ですので、ご自身でも使い方を研究して頂き、ぜひ有効活用していってください
ちなみに筆者が今回紹介したエクセルのバージョンは以下です
なお、これらのアドインも将来的に有料になる可能性も否定できませんので、その点は注意が必要です
今回、グラフを作成するアドインを紹介しましたが、グラフについてはこのブログ内で特集記事を作成していますので、ぜひそちらも参考にしてください
以前、Power Queryとは何か？について、スマホを例にして解説をしました
ガラケーではネットにある電話番号のデータを、キーボードに打ち込んで電話をしていました
一方、スマホではネットで検索した電話番号をそのままダイヤルすることができます
Power Queryも同じです！
エクセルファイル内の各シート、もしくは、他のファイル、フォルダ内のエクセルデータをそのまま活用できます！
今回はエクセルデータでなく、Access内のデータを活用してみましょう！
Accessというデータベースソフトと表計算ソフトのエクセルを直接、連携できれば今よりもデータを有効活用できることは間違いなしです！
１．取得先のAccessファイルを指定
今回は以下のAccessファイルからデータを取り込みます
①データ取得種類の指定
データタブから、データの取得⇒Microsoft Accessデータベースから
の順に指定
➁取込むファイルの指定
２．Access内の取得データを指定
取得するファイルを指定したらナビゲーター画面が立ち上がります
①取込みデータの指定
今回は複数のテーブル、もしくは選択クエリを取り込む方法で行います
上の画像のように、ナビゲーター画面の左上にある”複数のアイテムの選択”にチェックを入れ、取得するデータの横にチェックを入れます
➁読込先の指定
ナビゲーター画面の右下で”読込み先”をクリックします
読込み先のクリック後にデータのインポート画面が立ち上がります
今回は読込先にテーブルを指定します
すると、新たなシートにAccess内・２つのデータが読み込まれます
ちなみに、右画面にクエリが２つできましたが、これはスマホ内のブックマークと同じようなものです
更新すればAccess内の最新のデータが表示されます
＜まとめ＞
エクセルはとても便利なのですが、表計算ソフトなので大量のデータを各自で共有しながら作業するのにはあまり向いていません
一方、Accessは大量のデータ管理には向いていますが、不慣れな人が多いうえ、あまり細かい作業は行えません
このPower Queryをうまく使えば、エクセルとAccessのそれぞれの長所をうまくいいとこどりすることができます
ちなみに、Accessのエクスポート機能を使えばエクセルファイルをAccessから出力できますが、Power Queryであれば、前述の通りAccess内の最新データがエクセル内からいつでも取得することができます！
