皆さんこんにちは、イニシャルKです。
前回の記事で、Excelのオートフィルタや昇順／降順の方法ついて紹介しました。
▼前回紹介した記事
その時、説明用に作ったファイルが、
「Excelで何か管理表を作る時に参考になるかも？」と思ったので、
今回は、私が作った売上管理表（サンプル）をもとに、簡単な管理表の作り方を紹介したいと思います。
必要な情報をテーブル化して、別シートに準備
まず、管理表に必要な情報を列挙してください。
今回サンプルとした売上管理表の例では、
伝票No.・日付・担当者・商品ID・商品名・単価・販売数・売上、
という9つの情報を列挙しました。
列挙した情報の中で、規則性のあるデータは別シートにまとめておいて、そっちを参照する事で入力の統一化や簡略化を図りましょう。
連番はROW関数を用いて簡略化
今回のサンプルA列に記載している伝票No.は連番（通し番号）です。連番を毎回入力するのも面倒なので、ROW関数を用いて簡略化していきます。
ROW関数はセルの行番号を返す関数です。この特性を用いて 「=ROW() – □」としておくと、以降下に続く行は、このセルをコピー＆ペーストするだけでOKです。（※□は基準とする行番号）
今回のサンプルでは、6行目以降に売上データを入力するように表を作成してるので、A6セルを 「=ROW() – 5」とし、以降のセルは6行目をコピー＆ペーストして、連番になるよう作りました。
入力規則を用いてデータ入力の統一化・簡略化
担当者や商品に関する情報は規則性があるのでリスト化して、選択式にしていきます。
リスト化には入力規則を用います。[データ] → [データの入力規則] へ進み、[設定] から「入力値の種類」をリストに設定すると、入力セルのデータがリスト表記になります。
リストにするデータは範囲指定できますので、今回のサンプルでは、規則性のある担当者名と商品情報を別シートに準備しておき、そっちを参照するようにしてます。
▼リスト化する情報（別シート：担当者・商品情報）
▼データの入力規則（担当者）
▼データの入力規則（商品ID）
VLOOKUP関数を用いて別シートからデータを自動取得
さらに、商品IDと商品名・単価はセットになるデータですので、商品IDを入力すると、商品名・単価もセットで自動入力されるようにしておきましょう。
こういう時に便利な関数が、VLOOKUP関数です。
VLOOKUP関数は、指定された範囲の1行目で特定の値を検索し、指定した列と同じ行にある値を返してくれる関数です。言葉で説明すると少し難しく感じますので、サンプルを例に理解を深めましょう。
VLOOKUP関数で指定するデータは、検索値・範囲・行番号・[検索方法] です。
それぞれ、今回のサンプルに当てはめると下記になります。
| 検索値 | 商品ID（$D6セル） |
|---|---|
| 範囲 | 担当者・商品情報シートのB列～D列（担当者・商品情報!$B:$D） |
| 行番号 | 商品IDから数えて何行目のデータか？
（商品名＝2行目、単価＝３行目） |
| [検索方法] | 完全に一致する場合にデータを返すか否か？
※基本的に、完全一致と覚えておいて問題ありません。 完全一致＝FALSEと指定します。 |
※ちなみに、VLOOKUP関数で検索し、検索値が見つからなかった場合は #Ｎ/Ａ とエラー値が返ってきます。（エラー値をそのまま表示してしまうと、見た目が悪いです。）
サンプルでは、商品IDが空白の場合、VLOOKUP関数で商品名・単価を検索する必要はないため、VLOOKUP関数の実行前に、IF文を用いて、商品ID（$D6）が空白の場合は商品名・単価も空白になるようにしました。
商品単価まで分かれば、売上＝単価×販売数で計算できますので、販売数が入力されれば自動的に売上が計算されるように計算式を組んでおきましょう。
※売上の計算も、商品IDが空白の場合は計算する必要は無いので、IF文を用いて商品ID（$D6）が空白の場合は売上も空白となるようにしてます。
日付を集計したい単位に変換
ここまでで、管理表の各データに関して統一化や簡略化ができました。
ここからは2020年2月の総売上であったり、2020年2月の担当者別の売上が集計できるような仕組みを作ります。
まず、月別に集計できるように加工していきます。
B列に売上が発生した日付を入力してますが、今のままだと年月日がすべて入力されてるので、月別の集計は面倒です。なので、B列で入力している年月日に対して、年月のみになるように数式を使って少し加工します。
サンプルでは、（実は）I列に年月に変換するよう計算式を組んでます。（※集計用の計算式なので、目立たないよう文字の色を白っぽくしてます。）
日付から年月のみに変換するにはいくつか方法がありますが、私は個人的に簡単だと思うTEXT関数を用いて変換してます。TEXT関数は、指定したセルの表示形式（書式）を自分が表示したい形式へ変換する関数です。
今回は、月別に集計したいため、B列の日付を、”yyyy/mm”（○○○○年/○○月）へ変換してます。
あとは、集計したい年月（J3セル）の書式も”yyyy/mm”となるよう、書式の表示形式を変更し見栄えを揃えておきます。
これで、J3列のデータとI列のデータを比較し、条件にマッチした場合、SUMIIF関数を用いて指定した年月の売上を集計する準備が整いました。
※本記事内では、SUMIF関数の事は詳しく説明はしませんが、次で紹介しているSUMIFS関数を理解すれば、SUMIF関数も容易に理解できると思います。
SUMIFS関数を用いて条件に合致するデータを集計
ここまで出来たら、あとは仕上げです。
指定した月の担当者別の売上を集計できるようにしていきます。（サンプルファイルの「○○営業部_売上管理」シートのJ3セル～L3セルの部分です。）
J3セルに対象年月を、そしてK3セルに担当者を指定します。あとは、対象年月と担当者をもとにSUMIFS関数を用いて集計します。
SUMIFS関数とは、指定する複数条件にマッチした場合にその合計値を集計してくれる関数です。SUMIFS関数で指定するデータは、「合計対象範囲」・「条件対象範囲１」・「条件１」・「条件対象範囲２」・「条件２」…です。それらをL3列に記載します。
| 合計対象範囲 | 売上（H列） |
|---|---|
| 条件対象範囲１ | 〇〇〇〇年/○○月（I列） |
| 条件１ | 検索したい対象月（ TEXT(J3,”yyyy/mm”) ） |
| 条件対象範囲２ | 担当者列（C列） |
| 条件２ | 担当者（K3） |
Excelにはワイルドカード（*）という予約文字があり、ワイルドカードを用いて「なんでも良い文字列を含む」という条件が作れます。
※ただし、ワイルドカードは単純なIF文などには使用できませんので注意してください。今回紹介したSUMIFS関数やCOUNTIF、SUMIF関数には使用可能です。
ワイルドカードを少し応用して、「*さん」を条件に記載することも可能です。
この場合、先頭の文字列は何でも良いけど、最後の文字は「さん」で終わらないと条件として一致しません。
サンプルの例だと、条件２が「*さん」であれば条件として成立し合算しますが、条件２を「*君」や「*ちゃん」などと記述した場合は、条件として不成立となり合算しません。
これで、基本的な管理表の作り方は終わりです。
いかがでしたでしょうか？
今回紹介した管理表（サンプル）で使用している関数は、比較的簡単で、かつ利用シーンが多い関数で構成してますので、他にも様々な事に応用できるかと思います。
是非、色々使ってみて下さいね。
それでは、また次回の記事でお会いしましょう。
