・Excel関数でデータを集計したい
・集計どのようなテクニックがあるか知りたい
今回はExcel関数をつかった集計テクニックを紹介します。
皆さんはExcelを使って集計したことがあるでしょうか。企業とかだと数字を集計することもあり、それによる今後の予測できるようにするなどがあります。
表を扱うといえば以前、マクロをつかって表を取得して集計にするテクニックを紹介しました。
ただマクロを作るのはスキルが必要であり、人によっては抵抗を感じる場合があります。なるべくなら数式だけで解決できるのがいいです。
今回は機器のレンタル費用を題材とした、数式の書き方をいろいろ紹介した内容となっています。
数式の利用ケース
企業とかだと備品を借りたり買ったりすることもあり、何をどれだけ使うかで費用が予測することがあります。そこから総額や今後の手配の仕方が変わったりすると思います。
今回は以下のレンタル一覧表を使った基本的な集計の仕方を数式でできるように紹介します。
どこの期間誰にどれだけお金がかかるか、いつ何台レンタル品を返すのかとかが数式でわかると便利だと思います。それを以降で紹介します。
基本的な集計方法
管理者ごとの月額を集計する
まずはシンプルに管理者ごとの月額を集計してみます。これにはExcelの関数であるSUMIFをつかいます。
SUMIFは検索対象の範囲を指定して、検索条件に合うものに対して合計値を算出します。構文は以下です。
＝SUMIF（範囲, 検索条件, 合計範囲）これを利用して管理者ごとに月額を出すには以下のように記述します。
① 「範囲」として表の中の管理者の列（C4:C111）を指定します。
② 「検索条件」としてB16に記載の「A」またはB16セルを指定します。
③ 「合計範囲」として、金額の合計値を出すために月額の列（D4:D11）を指定します。
これで管理者ごとの月額を出せるようになりました。
管理者ごとの機器保持数を算出する
次に管理者ごとの月額を集計してみます。これにはExcelの関数であるCOUNTIFをつかいます。
COUNTIFは検索対象の範囲を指定して、検索条件に合うものの数を算出します。構文は以下です。
＝COUNTIF（範囲, 検索条件）これを利用して管理者ごとに機器保持数を出すには以下のように記述します。
① 「範囲」として表の中の管理者の列（C4:C11）を指定します。
② 「検索条件」としてE16に記載の「A」またはE16セルを指定します。
これで管理者ごとの機器保持数を出せるようになりました。
実際のケースを想定した集計方法
前章で基礎的なことは分かったとして、実際にレンタル期間等を考慮した集計方法本章で述べます。SUMIFやCOUNTIFでは条件を一つしか選べないため、レンタル期間を考慮できませんでした。
レンタル期間考慮して合計値を算出する
まずは管理者ごと、かつ、月ごとの費用を集計してみます。これにはExcelの関数であるSUMIFSをつかいます。
SUMIFSは検索対象の範囲と検索条件を複数指定して、条件すべてにに合うものに対して合計値を算出します。構文は以下です。
＝SUMIFS（合計範囲, 条件範囲1, 条件1,2 [条件範囲2, 条件2・・・]）これを利用して管理者ごと、かつ、月ごとの費用を出すには以下のように記述します。
① 合計範囲として、金額の合計値を出すために月額の列（D4:D11）を指定します。
② 条件範囲1として管理者ごとに振り分けるため管理者の列（C4:C11）、
条件1としてB23に記載の「A」またはB23セルを指定します。
③ 条件範囲2として開始月と比較するためレンタル開始月の列（E4:E11）、
条件2としてC22に記載の「2023年4月」より大きいことを示す、「”<=”&C22」を指定します。
比較を示す「<=」や「>=」は文字列のため、ダブルクォーテーションで囲う必要があります。そして文字とセル値の結合のため「&」でつなぐ必要があります。
④ 条件範囲3として終了月と比較するためレンタル終了月の列（F4:F11）、
条件3としてC22に記載の「2023年4月」より小さいことを示す、「”>=”&C22」を指定します。
③、④の比較について、正確には2023年4月30日が、2023年4月1日以降で2023年4月30日以前という日付ので比較になります。Excelの使用で日付が出てこないことがあるため表作成の際に注意ください。
これで管理者ごと、かつ、月ごとの費用を出せるようになりました。
レンタル期間考慮して合計台数を算出する
次に管理者ごと、かつ、月ごとの台数を集計してみます。これにはExcelの関数であるCOUNTIFSをつかいます。
COUNTIFSは検索対象の範囲と検索条件を複数指定して、条件すべてにに合うものに対して数を算出します。構文は以下です。
＝COUNTIFS（条件範囲1, 条件1, [条件範囲2, 条件2・・・]）これを利用して管理者ごと、かつ、月ごとの台数を出すには以下のように記述します。
① 条件範囲1として管理者ごとに振り分けるため管理者の列（C4:C11）、
条件1としてB31に記載の「A」またはB23セルを指定します。
② 条件範囲2として開始月と比較するためレンタル開始月の列（E4:E11）、
条件2としてC30に記載の「2023年4月」より大きいことを示す、「”<=”&C30」を指定します。
③ 条件範囲3として終了月と比較するためレンタル終了月の列（F4:F11）、
条件3としてC30に記載の「2023年4月」より小さいことを示す、「”>=”&C30」を指定します。
まとめ
本記事では、Excelを使用して集計を行うための数式テクニックについて紹介しました。日常業務で頻繁に必要とされる費用集計の技術は、企業の資産管理や将来の予算計画を立てる際に非常に有効です。
我々が詳しく掘り下げたのは以下の2つの基本関数と1つの応用関数です。
- SUMIFを使った管理者ごとの月額集計
- 特定の管理者に関連する金額だけを合計します。
- COUNTIFを用いた管理者ごとの機器保持数の計算
- 特定の管理者が持っている機器の数量をカウントします。
- SUMIFSを使って複数の条件（管理者、レンタル期間）を考慮した月額計算
- 複数条件に基づいた合計値を算出し、より複雑な集計を可能にします。
これらの関数を使用することで、複雑な集計を行うことができ、費用予測を正確に行うことができます。集計においては、適切な条件の設定や、適切な列の選定が鍵となります。また、日付を使った条件では、月末や月初を考慮する必要があり、詳細な設定が求められます。
集計と予測の精度を高めるためには、これらの関数に精通し、適切に活用することが重要です。さらに慣れてきたら、より高度な関数や機能を駆使してデータの分析を深めることも可能になるでしょう。
参考文献
- できるネット – SUMIF関数の使い方(https://dekiru.net/article/19911/)
コメント