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

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

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

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

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

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

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

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

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

今回のポイント

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

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

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

テーブル

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

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

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

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

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

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

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

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

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

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

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

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

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

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

アイコン

Power Query Data01 15.73 KB 100 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へ

にほんブログ村

魅せるピボットテーブル術3~転記・編集作業の改善_元データに無い項目追加~

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

9

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

!今回のポイント

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

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

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

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

フィールド

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

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

11

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

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

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

計算の種類
計算の修理

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

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

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

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

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

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

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

!詳細解説

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

アイコン

サンプル 28.28 KB 31 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へ

にほんブログ村