タグ別アーカイブ: 集計

【豆知識】ピボット/縦縦並び➡縦横に並び替え集計

 これまで何度か縦横並びを縦縦並びに変える「ピボット解除」については解説を行ってきました

 今回は、逆に縦縦並びのデータを「縦横」並びに変える「列のピボット」について解説します

 今回解説する「列のピボット」は、「集計」というステップも必要な場合には、相当便利な処理になります!

解説に使用する「元データ」は次の画像のデータです

こちらのデータを「地域」を軸にして、横展開します

実は、元データには「重複」データが含まれていますので、横展開する際には「合計」処理も必要になります

1.Power Queryエディタを開く

元データの上で右クリックし、Power Queryエディタ(以降、エディタ)を開きます

2.ピボットする列を選択

今回は、記事の冒頭で紹介したように「地域」を横展開します

3.「列のピボット」を実行

「列のピボット」はピボット解除のように右クリックではなく、「変換タブ」の「列のピボット」から指定します

上の画像の「列のピボット」をクリックすると次の画像が開きます



要は表の中味となるものを指定するわけですが、こちらは「売上金額」になります

値を指定して「OKボタン」を押すと次の画像のように、記事の冒頭で紹介した重複データも「合計」され、「列のピボット」が完成しています

4.合計以外の計算

前述の3.では「合計」処理を行いましたが、「平均」や「カウント」も計算できます

「列のピボット」ダイアログで「詳細設定オプション」の左横にある▼マークをクリックすると「合計」以外の方法も指定できます

平均を選択すると次の画像のように表示されます

もし、エクセルシートに読込んだ際に、エラーが発生した場合には、自動追加された「変更された型」ステップが不適切に行われている可能性があります

その場合には、上の画像の「変更された型」を削除するか、データ形式を変更するなどの処理が必要になります

今回の解説は以上です

最後まで記事を読んで下さり、誠にありがとうございました

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


にほんブログ村

各種集計/合計から四捨五入まで~上級編4回目

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

エクセルは表計算ソフトなので、エクセル作業に集計処理はつきものです

必要な関数を調べて入力したり、ピボットテーブルにして処理したりする作業はちょっとガッツが必要なものです

Power Queryでは、様々な切り口による集計処理を直感的なクリック操作で行えるのが、大きなメリットです

Power Queryエディター内の「変換タブ」と「列の追加タブ」では、集計処理のためのメニューが豊富に揃っています

集計処理メニュー

今回の解説では、上の画像の左のメニューから順にポイントを絞って解説します

尚、三角関数と情報メニューについては、解説を今回は割愛させて頂きます(指数メニューについても概要だけの解説になります)

今回、解説する上で分かりにくい点が1点あります

変換タブ列の追加タブには、統計以外は同じメニューがあります

違う点は、集計した列を追加するかどうかだけです

今回の解説では、極力、変換タブに絞って解説を行います

目次

1.統計処理メニュー

2.標準メニュー

3.指数メニュー

4.数字を丸める

<まとめ>

1.統計処理メニュー

こちらのメニューは列単位での集計処理メニューになります

「列単位での集計処理」という意味は、下の画像を例にとれば「販売個数の列」を行方向に一括で集計するという意味です

ですから、集計した値は1つだけ出力されます

統計ボタンの右下にある▼マークをクリックすると、様々な集計切り口が出てきます

統計

今回は、合計だけ出力してみます

集計する列をカーソルで指定し「合計」を押します

合計処理

すると、列にある値を合計した値「1011」が出力されました

ちなみに、Power Queryコラムでも書きましたが、こちらの統計処理メニューには、「個別の値のアカウント」という集計方法があります

こちらは、列内にある重複しないリスト数を集計するという優れた機能です

従来のエクセルの使い方であれば、複数回のステップが必要な集計処理でした

「個別の値のアカウント」について興味のある方は、こちらの記事もぜひご参照ください

2.標準メニュー

こちらのメニューでは2つの集計方法を紹介します

集計単位は指定した列の各行単位になります

①四則演算

今回は四則演算の内、「割り算」についてのみ解説します

実際に集計する前に、集計する列をコピーしておいて、集計結果を集計前と比較できるようにします

この列のコピー処理がPower Queryエディターでは、どうやるのかが分かりにくいので注意が必要です

このコピー処理は「列の追加」タブの「重複する列」で行います

では、コピーした列の列名を「販売個数ー割り算」に変更した上で、「割り算」による集計を行います

まず、集計する列をカーソルで選択した上で、「除算」を押します

その後に、除算する値を入力するダイアログが出てきます

ここでは、「2」を入力しました

結果、列全体が「2で割り算された値」に置き換わっています

以降は有料ページ/課金サービスへ

Power Queryって何??11~ユニークな数の集計~

今回は、Power Queryならではの集計処理について1点だけ紹介します

ある列に存在する文字列、もしくは値のユニークな数を集計するのは面倒ですよね

上の画像内の「商品コード」を例に取れば

「商品コードが10001、10002・・・10006は4つあるので1個にカウントして、全部で6個あります」

と目で数える人はさすがにいないとは思いますが、関数を使ったとしても、複数回の処理ステップが必要ですよね

ところが、Power Queryでは直観的にクリック操作していけば、このユニークな数を集計できます

解説には上の図にある表を使用します

1.Power Queryエディターを起動

2.「統計」タブの「個別の値のカウント」をクリック

①変換タブに移動

Power Queryエディターが起動したら、変換タブに移動します

②「統計」ボタンから「個別の値のカウント」をクリック

「統計」ボタン横にある▼マークをクリックすると、一番下に「個別の値のカウント」が出てきますので、こちらをクリックします

すると、ユニークな数が出力されます

出力結果は、テーブルに変換してエクセルシートに出力することもできます

今回の解説は以上です

Power Query集計機能はかなり充実していますので、別途、まとめて記事をアップする予定です!

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


にほんブログ村

クエリのマージ/計算処理~中級編3回目~

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

前回は2つのクエリーをマージして、項目が追加されたクエリーを作成しました

今回は、追加した項目を更に深堀活用し、本来であれば関数やVBAの使用が必要な処理をサクッと行う方法を習得しましょう!

解説は前回と同じサンプルファイルを使用して行います

今回使用するデータ

今回の解説では、商品台帳と売上台帳の2つの表から、下の表を作成します

前回、売上台帳に商品台帳の「商品名」と「販売単価」項目を追加しましたが、今回は、更に「販売個数の合計」と「売上金額」も算出した表にします

<今回のポイント>

A.条件付き集計

今回の集計処理では、同じ商品コードに紐付く「複数の値」を集計します

エクセル関数でいえば、SUMIFS関数と同じ処理です

しかも、SUMIFS関数よりももっとシンプルな方法で集計します

前回、クエリの項目を追加する時に使用した画面と同じ画面にて、単純にマージ方法として「集計」を選ぶだけです!

B.一括計算

1.で商品コード毎に販売個数を集計したら、販売単価を乗じます

乗じる処理は、乗じる列を選択して計算方法を指定するだけです

2つのポイントを確認したところで、早速、詳細な解説に入りましょう!

1.条件付き集計

前回と同じ様に、クエリーを参照して新たなクエリーを作成するところから処理を開始します

➀参照するクエリーの指定

今回は商品台帳を参照元にします

商品台帳クエリー上で右クリックし、下の画像の様に「参照」が選択できるようになったら、参照のテキストをクリックしましょう!

➁クエリのマージ

Power Query Editorが開いたら、ホームタブの上にある「クエリのマージ」をクリックしましょう!

下の画像のようなマージ画面が開いたら、前回と同じく商品コードをキーにして売上台帳クエリをマージしましょう

③条件付き集計処理

事前に新たに作成したクエリの名前は変えておきましょう

クエリの名前を変更したら、マージした売上台帳の項目を、下の画像の黄色の箇所をクリックしましょう!

次に開いたマージ方法を詳細に設定する画面では、前回と違い「展開」ではなく「集計」をチェックしましょう

その際に”元の列名を・・・”はチェックを外しておきましょう

そして、本来であれば上の画像の一番下「Σ販売個数の合計」をチェックして指定してください、というべきところです

試しに、「Σ販売個数の合計」の一つ上、「Σ商品コードの合計」をチェックして指定してみてください

すると次の画像のように表示されます

もちろん、商品コードを合計しても意味はありません

もし、指定を間違えてしまった時の対処は、Power Queryであればどうするのか?ということを解説するため、敢えて本来とは違うものを指定してもらいました

間違いを回復するには、下の画像の「適用したステップ欄」の黄色の箇所をクリックしてください

すると前回行ったチェック指定がやり直せるようになります

Power Queryでは、上の画像のように「やり直し」を行う場合には「適用したステップ欄」を使用します

詳細は後日、解説を行います


それでは「やり直し」を行い、下の画像のように本来の”販売個数の合計”が表示されたところで2.一括計算の解説に移ります

2.一括計算

計算したいのはⅰ)販売単価とⅱ)販売個数の合計を乗じた売上金額です

➀計算対象の列、ⅰ)ⅱ)を選択する

Ctrlキーを押しながら、販売単価の列と販売個数の合計の列を選択しましょう

➁画面上のタブから「列の追加」を選択

画面上のタブから列の追加をクリックすると、右側に計算機のようなマークがついた「標準」が見えます

次に「標準」マーク下の▼をクリックしましょう!

③計算方法の指定

マークをクリックした後に表示された「各種計算方法」から乗算を選択してクリックしましょう!

すると上のGIFのように、一番右に新たな列が追加されます

この新たな列には、計算結果が出力されています

列の名称は”乗算”から”売上金額”に変更しておきましょう

④読込処理

列の追加タブからホームタブにもどり、エクセルシートへの読込処理を、下の画像の「閉じて読み込む」ボタンを通じて行いましょう

販売個数と売上金額が出力されているのを確認したら処理は終了です

<まとめ>

 今回は商品台帳と売上台帳をマージして、商品コード毎に「a:販売個数」を集計処理しました

 そして、集計した「a:販売個数」と「b:販売単価」とをPower Query Editor上で更に乗じて「c:売上金額」まで計算した表を作成しました!

 エクセル関数のVLOOKUP関数とSUMIF関数を使用しても同じことが行えますが、もっと楽に表を作成できているのではないでしょうか?

関数と違って、カーソルで対象を指定しながら直観的に行えるのもとてもうれしいです

 それに、もし、表の作成の仕方を間違えたとしても、間違いを起こした場所からやり直せます

さて、計算の元になる表の値が変わった場合にはどうすればいいのでしょうか?

関数で表を作成していれば、自動計算されています

 Power Queryの場合には、関数を使用した場合と違って、1つ余分な処理を行う必要があります

 この場合は下の画像の中にある「すべて更新」をクリックします

そうすれば、表の中で再計算が行われます

今回の解説は以上です

次回は、今回と同じように複数のクエリを組み合わせて扱う方法を解説します

但し、今回のように商品台帳と売上台帳といった、違う内容のクエリを組み合わせるのではありません

同じ項目から構成される複数のクエリを追加して、組み合わせる方法の解説になります

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


にほんブログ村

Sで終わる関数は素晴らしい!~Countifs関数~

前回は、IFS関数を紹介しました

今回はCOUNTFIFS関数を紹介します

本当にSで終わる関数は素晴らしいです

数式の書き方はIFS関数とほぼ同じです

今回は下の事例で解説を行います

この表から最高得点・科目と最低得点・科目の組み合わせが当てはまる人数をCountif関数とCountifs関数、の両方で求めてみます

.Countif関数のケース

Countif関数は1つしか条件式を設定することができません

なので、一旦、複合キーを作成して検索条件にするしかありません

数式は次のような書き方になります

Countif関数では、検索条件範囲/H4:H8と検索条件/D12との組み合わせを、1つしか使えないのです

=COUNTIF(H4:H8,D12)

2.Countifs関数のケース

Countifs関数の場合は複合キーを使用する必要はありません

Counfifs関数の場合、複数の検索条件範囲と検索条件の組み合わせを設定できるからです

以下が、実際に上の画面で使用した数式です

=COUNTIFS(F4:F8,B16,G4:G8,C16)

数式の書き方は、前回に紹介したIFS関数と同じ様に、検索条件範囲⇒検索条件の記述を条件がある数だけ繰り返すだけで済みます

<まとめ>

Countif関数を使いたい時に、複数条件がある場合には複合キーを作成しなくてはならないので、いつも面倒くさい思いをしていました

Countif関数を使えば、条件がいくつあろうとあまり手間ではありません

Sで終わる関数は本当に便利ですね

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

にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

XxXLookup関数で集計上手になる!

読者の皆さんがこれまで使用したエクセル関数の中で、一番使用頻度が高かったのはどの関数でしょうか?

多くの皆さんはVLOOKUP関数と答えるのではないでしょうか?

ところが、XLOOKUP関数の登場により、関数の使用シーンがこれから大きく変わっていきそうです

まだ使用できるエクセルバージョンは少ないかもしれませんが、それだけXLOOKUP関数の登場はインパクトがあります

特にXLOOKUP関数と他の関数を組み合わせることにより、データ集計の効率を格段に向上させることができそうです

今回は、XLOOKUP関数同士を組み合わせた例を2つ紹介します

どちらの例も、表を2次元で処理できるので、利用シーンが多そうです

①マトリクス表から2つの条件に合致するものを抽出する

条件を2つ(条件①部門x条件➁四半期間)をリストで指定し、2つの条件に一致する値を抽出する

➁マトリクス表でランク付けをする

事業の売上額と成長率、在庫商品の在庫額と回転率、などなど二次元でランク付けすることはよくあります

ところが、これまでは単に関数を使用するだけでは、ランク付けは自動ではできませんでした

尚、これからの解説は既にXLOOKUP関数を使用したことがある人を主として想定しています

但し、そうでない方もサンプルファイルをダウンロードすることで、そのままサンプルファイルの使用例を他の目的に利用していくことも想定しています!

1.そもそもXLOOKUP関数とは?

2つの事例を紹介する前に少しXLOOKUP関数について簡単におさらいをしておきましょう

サンプルファイルでは1シート目の内容です

サンプルの内容は部門毎に対応する部門長を別表から抽出するものです

XLOOKUP関数も、VLOOKUP関数と同じように検索値を基にして表からデータを抽出します(事例では北海道を検索値として、部門長の木村を抽出)

但し、VLOOKUP関数で行っていたような

「○○から右に何番目」

といった列数の指定が無く、検索する範囲(サンプルの部門)、戻り範囲(部門長)を指定しするだけです

つまり、 「○○から右に何番目」 の縛りがなく、検索値が参照表の左に無くてもいいし、列方向でなく行方向でもデータを抽出できます

引数は以下のような形で指定します

=XLOOKUP(検索値、検索値範囲、戻り範囲、【省略可】)

2.マトリクス表から2つの条件に合致するものを抽出する
XLOOUP関数の3番目の引数、戻り範囲にXLOOKUP関数を入れるのがポイントになります

仮に戻り範囲にXLOOKUP関数を入れなければ、いわゆるスピる(値があふれ出す)状態になります

以下が検索の軸を部門にし、戻り範囲を表の全範囲にした場合です

列方向でスピります

・=XLOOKUP(I3,B3:B9,C3:F9)*3番目の引数を全範囲

以下が期間を軸に、戻り範囲を表の全範囲にした場合です

行方向でスピります

・=XLOOKUP(I4,C2:F2,C3:F9)*3番目の引数を全範囲

この2つのスピりの交点が求まれば正解の値です

ですので、以下のように3段階でXLOOKUP関数を組み合わせます

A.部門の検索を行うXLOOKUP関数を作成

B.但し、3番目の引数に期間を検索するXLOOKUP関数を入れる

C.3番目の引数になるXLOOKUP関数は”期間の選択”に備えて戻り範囲=全範囲

最終的にはこのようになります

=XLOOKUP(I3,B3:B9,XLOOKUP(I4,C2:F2,C3:F9))

数式の内容はサンプルファイルにて、実際に入力されている内容を確認してくみてください

3.マトリクス表でランク付けをする

2.で行ったXLOOKUPによる関数では、4番目以降の引数を省略していました

ここで、5番目の引数を登場させます

VLOOKUP関数でも4番目の引数で検索方法を指定しています

FALSE=完全一致

TRUE≒完全一致

XLOOKUP関数では以下のように検索方法を指定できます

0⇒完全一致

-1⇒検索値の次に小さい値

1⇒検索値の次に大きい値

今回使用するの-1、検索値の次に小さい値です

以下の表で言えば、もし売上が30なら30より小さい値の0が該当し、前期比が15%であれば、10%が該当します

つまり、2.で行ったXLOOKUP関数の組み合わせに、検索方法-1を組み合わせれば2次元でのランク付けは、簡単に自動化できるのです

式は以下のようになります

=XLOOKUP(C5,$H$4:$H$6,XLOOKUP(D5,$I$3:$K$3,$I$4:$K$6,,-1),,-1)

企業ではABC分析の延長で、2つの変数(例:売上、利益率)で事業や商品を評価する機会が多いので、このXLOOKUP関数の組み合わせを行う機会は多いと思います

<まとめ>

今回は大きく分けて2つの事を解説しました

ⅰ)XLOOKUP関数を行列、双方向で組み合わせる(スピるの組み合わせ)

ⅱ)XLOOKUP関数の検索方法を工夫し、自動でランク付けをする

このⅰ)ⅱ)ができれば、データ集計がとても楽に行えます

ぜひ、有効活用していきましょう!

このブログではFILTER関数など、XLOOKUP関数と同じ新たなタイプの関数の解説も行っていますので、そちらもぜひ参照してみてください

尚、XLOOKUP関数が使えるエクセルバージョン(2020年9月19日時点)は限られており、OFFICE365ユーザー向けとなっています

2019バージョンユーザーなどは使用できないので、その点はまだ不便ではあります

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

にほんブログ村

SORT関数によるエクセルデータの並び替え~列の並び替えも可

前回は1つの関数に対して複数の出力がある関数(ダイナミック関数)として、FILTER関数を紹介しました

今回は、同じようにダイナミック関数のひとつである、SORT関数を紹介します

エクセルで大量のデータを扱う時に、注意しなくてはいけないことの一つが並べ替えです

間違って並び替えると、列間で食い違いが生じて大規模なミスにつながります。SORT関数では並べ替えのキーが複数あっても、正確に且つ、簡単に並べ替えの処理が行えます

加えて、SORT関数では列の並べ替えが行えるのが大きな利点です!

以前、列フィルター機能を紹介しましたが、従来のエクセルでは列の扱いが困難だったのです

では、今回は、SORT関数の概要だけ紹介します!

1.複数の条件による並び替え

=SORT(並び替え範囲,{列位置,列位置・・・},{並び替え方法,並び替え方法・・・})

並び替え範囲/引数1を指定した後、並び替えの条件になる列の位置/引数2(例:{2,3})を指定し、更に並べ替え方法/引数3を指定します(例:{1,-1})

並べ替え方法/引数3で指定する1、-1とは、<1が昇順>、<-1が降順>の意味にになります

ちなみみに、引数2と引数3は指定の仕方がセットになっています

前述の例で言えば、引数2を{2,3}と指定し、引数3を{1,-1}と指定したので

・引数2の1番目(2)は引数3の1番目(1)とセット

・引数3の2番目(3)は引数3の2番目(-1)とセット

となります

実際の例として、以下の表/テーブル2を並べ替え範囲として、地域(列:2、昇順:1)、売上金額(列:3、降順-1)を並び変えてみます

元データ-テーブル2

数式は以下の通りとなります

SORT関数による並べ替え
SORT関数事例
SORT関数

2.列の並び替え

エクセルは行フィルター機能があるのが便利なのですが、業務によっては在庫表のようなクロス表形式で作業することがあります

以前から、列の並び替え機能もあると便利だとは思っていました

列の並び替えを指定する場合には、 前述の「1.複数の条件による並び替え」 で指定した3つの引数に加えて、4つ目の引数【列方向で並び替えを行う】をTRUEで指定する必要があります

ちなみに、「1.複数の条件による並び替え」にて、列位置で指定した 引数2は行位置で指定します

実際の例として、以下の表の3行目を並べ替え条件に設定し、降順にて列の並べ替えを行います

以下が数式です。4番目の引数をTRUEで指定しています

列並び替え
SORT関数ー列並び替え

但し、列の並び替えができるといっても元データ自体を並び替えできるわけではありません

利用機会を増やすには、別シートで並び変えた内容を編集して表示するなどの工夫が必要になります

ちなみに、FILTER関数と同じく、関数を入力したセル位置に「#」をつけることにより、出力データの再利用は行えます

下のGIFでは、SORT関数が入力された位置(F12)に#をつけることにより、表の右横にSORT関数の出力データを再出力しています

SPILL再利用
#によるデータ再利用

<まとめ>

今回はSORT関数により、データの並べ替えを行う方法を解説しました

SORT関数では、{}の文字を組み合わせることにより、複数の並べ替え条件に1(昇順)、-1(降順)をセットで指定することができます

後、SORT関数では列の並び替えを指定できるという特徴があります

こちらは使い方を研究して見て、ぜひ日常業務の中で活用してみてください

尚、実際のデータでSORT関数の動きを確認したい方は以下のファイルをダウンロードしてください!

アイコン

SORT関数 18.85 KB 4 downloads

...

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

にほんブログ村

グループ化1~中級編11回目~

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

前回は、Power Queryのエディター画面(Power Query Editor)にて条件列とインデックス列を作成しました

今回は前回の「列の追加タブ」ではなく、「HOME」タブにてデータのグループ化に取り組みます

ここで言うグループ化とは、単にデータをグループに分けるだけでなく、データをある切り口でグループ化した上で、グループ毎に合計金額などを集計することです

このグループ化の処理が行えると、関数の入力もピボットテーブルの利用もないので、数字の集計処理が格段に早くなります

グループ化はスマホのように直感的に行えるようになっていますので、ぜひサンプルファイルを使用して実際に試してみてください

Power-Query-Editor
グループ化

今回、サンプルに使用するデータに5列あり、列の構成はⅰ)年度別、ⅱ)製品別、且つ、ⅲ)Region、ⅳ)地域、a:売上金額となっています

使用データ

このデータをグループ化して、ⅱ)製品別のa:売上金額を集計します

使用データ

1.エディターを開く

①「テーブルまたは範囲から」をクリック

データ上にカーソルを置き、、「テーブルまたは範囲から」をクリックします

②エディターが開く

今回は、既にテーブル化してあるので、①の操作でそのままエディターが開きます

2.グループ化

グループ化の処理は、前述したようにエディター内のHOMEタブで行います

①グループ化をクリック

グループ化

②グループ化設定 ⇒ OKボタン

今回は、前述のようにグループ化するのは「製品別列」、合計するのは「売上金額列」になります

グループ化画面にて最初に設定するのは、グループ化する列です

グループ化する対象列は▼マークをクリックすると選択できるようになります

後は、画面の表示に従って設定しています

グループ化する列を設定したら、画面下の3項目を設定します

グループ化・設定

「新しい列名」については、今回は「製品別・売上金額」としました

「操作」では、合計や平均などの集計方法を選択できます

今回は「合計」を集計方法として選択します

最後は集計対象の列、「売上金額」を指定します

指定が終わり、OKボタンを押せば、以下のようにグループ化が行われています

グループ化・結果

<まとめ>

今回は、エディター画面でグループ毎に合計金額を集計する方法について解説しました

記事の冒頭でグループ化は「スマホのように直感的に行える」と述べました

グループ化設定する項目の位置については、集計する対象の列と混同しやすいので、この点だけは注意が必要です

まず最初に設定するのが、グループ化する列です

そして、一番最後に設定するのが「集計する対象の列」となります

今回の解説は以上です

次回は、グループ化・機能を更に有効活用するためにピボットテーブルと連携します

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

にほんブログ村

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

<まとめ>

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

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

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

にほんブログ村

テーブルを作成しよう

エクセルの表で何か作業をするときには、まずテーブルを作成しましょう

1

とにかくこの”テーブル”機能を活用するかしないかで、作業効率が大きく違ってきます

作業する表のどこかにカーソルを置き、Ctrl+Tを押しましょう

そして、下のGIFのように開いたダイアログボックスにて、”先頭行をテーブルの見出しとして使用する”にチェックを入れておきましょう

GIF1

表の色が変わったら、表が「テーブル」になったということです

試しに、色の変わった表のすぐ下に何かを文字や数字を入力をしてみでください

GIF2

自働的に表の範囲が拡大していきます

つまり、表の管理をエクセルが自動的に行ってくれるのです

次回から詳細を解説していきます
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 IT技術ブログ VBAへ