タグ別アーカイブ: テーブル機能

シートからデータ取得~ 初級講座2回目~

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

前回はPower Queryとは何か?について、スマホを例えにして解説しました

「スマホで好みのレストランを検索(データ取得)」⇒

⇒「レストランのページ内にある電話番号を活用(変換)」⇒

⇒「そのまま予約電話をかける(読込)」

 今回は操作のウォーミングアップとして、実際にエクセルシート内にあるデータを、Power Queryで取得しましょう!

 そして、取得したデータの変換処理⇒読込までの一連の流れを行ってみましょう!

今回のポイント

ところで、読者の皆さんは、テーブル機能を普段、活用されてますでしょうか?  

Power Queryでは、特にテーブル機能の理解が必須です

テーブルは「見出し」と「データ」の2つの箇所から構成されます

テーブル

 Power Queryではこのテーブルをブロック遊びのように結合したり、分解したりします

 エクセルの最新バージョンの機能は、このテーブルと連携する前提で作成されています

テーブル機能を覚えておいて損はありません

シート上にあるデータを、一括で処理できるようになります

まだ知らないという方は、ぜひこの機会に習得してみませんか?⇒関連記事

 テーブルの解説記事を読む時間が無い方は「テーブルとは、データを扱いやすくする機能」という風に割り切って覚えましょう!

 とにかくシート上で、データを見たら「見出し」があるのを確認してショートカットキー「Ctrl+T」を押しましょう!

 下の画像のように右クリックをしたら表示される、下の画像の箇所をクリックするのでもOKです!

 上の処理により、データの右下には下の画像のようなマークがついているはずです

これだけでテーブルが完成です

後はテーブル上で様々な事が行えます

 行列を自動的に拡大したり、1つのセルに関数を入力するだけで「1列」に関数を反映したりします

今回はPower Queryを使用して、シート上のデータを「テーブルに変換した上で取得」し、別シートへそのまま読み込んでみましょう!

尚、サンプルデータは以下をご活用ください!

アイコン

Power Query Data01 15.73 KB 79 downloads

...

1.シート上のデータを取得する

➀まず、サンプルデータにある表のどこかにカーソルを置きましょう!

②それから、データタブを選択後、下の画像上で黄色のマークをつけた箇所をクリックしましょう!

③すると、下の画像のようなダイアログボックスが開きます

「先頭行をテーブルの見出しとして使用する」にチェックを入れたまま、OKボタンをクリックします!

④しばらくすると、Power Queryエディターが開きます!

Power Queryエディター

Power Queryエディターが開いた時には、シート上の表はテーブル化されています

.クエリに名前をつける

Power Query Editorの右に「クエリ」の設定という欄があります

ここで、名前だけ変更しましょう!

今回は受注データとします

3.Power Queryエディターの内容を読み込む

では、Power Queryエディターの左上にある「閉じて読み込む」の右下の▼マークをクリックしましょう!

すると、以下のように「閉じて読み込む」というテキストが見えますので、こちらをクリックしましょう!

すると、データが蓄積されてあったシート(Dataシート)とは別のシートにデータが読み込まれます

読み込み

ここでシートの右側を確認してみてください!

受注データというクエリが見えるはずです!


受注データ・クエリーにカーソルを置いて右クリックするとダイアログボックスが表示されますので、一番下に見えるプロパティをクリックし、てみてください!

次に表示されたダイアログボックスの内容を見てみると、このクエリが「どこかに接続されている」ということがなんとなく分かると思います

前回、クエリーについては「スマホ内に設定するブックマーク」みたいなものと解説しました

実際、このクエリーを通じてデータにアクセスすることができています

試しに、取得元のデータに1行追加してみましょう!

その後クエリーを右クリックし、表示された「最新の情報に更新」をクリックしてみてください

するとクエリーの行数が増えているはずです

これはクエリーがデータに接続されているため、行数の増加が反映されたのです

さて、クエリーが「どういうものか?」について概要を理解できたところで、まとめに移りましょう!

<まとめ>

 今回は、シート上にあるデータをテーブルに変換した上で「データ取得~別シートにデータ読込」までを行いました

 読込まで行って作成されたクエリーは、後日解説するマージ技術を組み合わせると、複数シートに分かれたデータを1シートでまとめて処理できるようになります

 もし、テーブル機能をこれまで使ったことがなかったという方がいらっしゃいましたら、これを機会に有効活用していきましょう!

 データをエクセルで扱おうと思ったら、このテーブル機能はとにかく便利です。そして、Power Queryを操作する上でとても重要な機能です!

 ところで、Power Queryを初めて操作された感想はいかがでしょうか?スマホと同じような直感的な操作で一連の操作ができたはずです

直感的な操作のみでデータを扱えるのが、このPower Queryの良さです!

どんどん手を動かして慣れていきましょう!

次回は、他のファイルからデータを取得してみます!

それでは3回目でまたお会いしましょう!

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


にほんブログ村

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

<まとめ>

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

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

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

にほんブログ村 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へ

にほんブログ村

テーブルを作成しよう~自動データ集計~

今回から2回に分けてテーブルの集計機能を活用してデータ集計を自由自在に行う方法を学びましょう!

クリックするだけであらゆる種類のデータ集計が行えます!

テーブルによる集計は、ピボットテーブルのように表にまとめるような作業にはあまり向いていませんが

データ分析などデータをあらゆる角度で確認しながら作業を進めていきたい場合などに適しています

仮に、分析を行わない方でも”テーブル”に慣れる為に、まずはとにかく手を動かしてみてください!

この回は全く関数を使わない方法を行います(2回目で関数を使いますが、IF関数のみです)

まずサンプルファイルからテーブルを作成しましょう(まずはCtrl+Tです)

8

1.テーブルのスタイルオプションについて

テーブルのどこかにカーソルを置いたまま一番右の”デザイン”タブをクリックしましょう

すると色々なカスタマイズが出来るようになっています

GIF7

2.合計を算出する、条件を付けて合計を算出する

単なる合計なら、集計行をクリックするだけで算出できます

9

では、フィルター機能を活用して条件付きで合計を集計しましょう!

下の例は3地区から一つの地区の売上金額を集計しています

GIF8

3.その他の集計

上の例は合計の集計でした

実は他の種類の合計もできます!

GIF9

”平均”や”数値の個数”など様々な種類の集計がクリックだけでできます

では次回はIF関数を使い、テーブルを使ったデータ集計を深堀していきます!

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

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

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

<解答詳細>

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

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

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

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

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

4

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

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

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

5

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

6

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

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

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

GIF5

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

GIF6

最後に、

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

7

それではまた次回

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

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

エクセルで表を見たらCtrl+Tを押してテーブルを作成しましょう

そうすれば、あらゆる作業が自動化できます

今回から2回に分けて以下の問題に、テーブル機能を使って取り組んでみましょう

アイコン

サンプル2 237.45 KB 15 downloads

...

12

サンプルの行数は見出しを除き、1万もあります

いちいち目で判断して入力していたら日が暮れてしまいますが、

このような作業は日常業務において、意外に多いのではないでしょうか?

・在庫商品の在庫数が○○以上で回転率が××以下の時はランクは▼とする

・部門がAでスマホ経由の時には”スマホ販売”として売上集計する

などなど、例を上げたらキリがないですが、今回を機会に自動化する方法を覚えましょう

まず、いきなり回答から入ります

<解答>

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

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

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

実際にはVLOOKUP関数にIFERROR関数を組み合わせてます(VLOOKUP関数がエラーの場合は””を出力する) 

とにかくテーブルを前回のように作成しておけば、テーブル範囲内で数式を1行でも入力すれば、たとえ1万行あってもテーブル範囲内の全ての行に数式が反映されます

GIF4

では次回、もう少し詳細に解説します
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ