タグ別アーカイブ: 計算

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

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

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

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

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

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

集計処理メニュー

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

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

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

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

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

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

目次

1.統計処理メニュー

2.標準メニュー

3.指数メニュー

4.数字を丸める

<まとめ>

1.統計処理メニュー

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

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

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

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

統計

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

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

合計処理

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

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

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

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

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

2.標準メニュー

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

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

①四則演算

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

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

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

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

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

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

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

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

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

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

フリーアドイン~範囲計算を自動化~

エクセルを使っていて、苦痛な作業のひとつは「%で算出」する数式を入力する作業です

%の代わりに小数点を使用することもできますが、それも手間ですよね

この「%」の入力作業を複数セルにわたって行うのは更に苦痛です

ところが、このアドインは「範囲」を指定して、「整数」を指定するだけで「%で算出した値」を指定範囲に反映してくれます

範囲計算
Range_Calculations

計算するのに小数点は入力が全く必要がありません

このアドインは%に関する計算だけでなく、+-X÷の四則演算も行ってくれます!

では、詳細に解説を行って行きます

1.アドインを入手

入手するアドインは「Range Calculations」です

「アドインを入手」の画面にて「Range Calculations」を検索・入手してください

アドインの入手や細かな操作方法で不明な点があればこちらの記事をぜひご確認ください

2.計算範囲を指定

1.でアドインを入手したら、エクセル画面の右側が以下のようになっているはずです

この画面が現れたら、アドインが使用できる状態になっていますので、計算対象の範囲をカーソルで指定しましょう

範囲指定

3.計算方法を指定

計算方法は全部で7種類から選択して指定できます

7種類の計算方法は、大きく分けて「四則演算」と「%に関する計算」関連に分かれます

四則演算は「足し算→Add」「引き算→Subtract」「Multiply→掛け算」「Divide→割り算」それぞれのボタンを選択すれば指定できます

四則演算

「%に関する計算」については3種類が選択できます

Calculate percentage」は元の値が100、指定値が20の場合

   ⇒100 x 20% = 20を算出します

Add percentage」は元の値が100、指定値が20の場合

   ⇒100 x (100% + 20%) = 120を算出します

「Subtract percentage」は元の値が100、指定値が20の場合

   ⇒100 x (100% – 20%) = 80を算出します

4.計算方法を実行

今回は「%に関する計算」にて、「Add percentage」だけ実例を解説します

「Add percentage」を指定したら、指定値を下の画像に入力します

20%ではなく、20と入力するのがポイントです!

%や小数点を入力する必要がなくて、とても便利です!

指定値を入力できたらCalculateをクリックしましょう!

Calculatteボタンを押すと、下のようなメッセージが出ます

これは、指定範囲に数式が入っていたら値で上書きされますよ!という警告メッセージです

上書きされてマズイものがなければ、そのまま「Yes」ボタンをクリックしてください

範囲計算
計算結果

これで計算範囲とした6つのセル、全ての値が100⇒120に変換されました!

<まとめ>

今回は、アドイン「Range Calculations」を紹介させて頂きました

このアドインは範囲計算をするアドインですが、特に「%に関する計算」については、%や少数点を入力しないで済むので便利です

このアドインを入手する時には規約等の確認を求められますので、そちらについては確認の上でご使用をお願いします

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


にほんブログ村