この記事には広告・アフィリエイトリンクが含まれます。
商品別の売上を見たいのに、記録は日付順。ノートの行を探して、足して、今度は通販だけ探して……。
あああ、同じ表を何度も見てる！となりそうな場面を想像してみて。
そんなとき、Excelのピボットテーブルなら、元の販売記録を使い、商品別や販路別に集計する方法があります。最初は「商品名を行、売上金額を値」に置くところからでいいよ。
ただし、数字が出たから正しい集計、とは限りません。何を一行にした表なのか、金額を足したのか件数を数えたのか、追加した記録まで含んでいるのか。ここを一緒に見よう。
この記事ではMicrosoftの公式案内をもとに、Windows版Excelの基本操作を中心に説明します。実際の店の実績ではない、小さな販売記録の仮の例を使います。Excelの版や使用環境で画面名は異なるため、自分の環境の公式ヘルプも確認してね。
練習用に選ぶ書籍は、木村幸子さんの『Excelピボットテーブル データ集計・分析の「引き出し」が増える本 第2版』です。出版社の紹介と目次で、基本集計、データの不備、集計方法、更新を扱うと確認しています。書籍全文を読んだ書評や、この記事の例が本に載っているという紹介ではありません。
集計ボタンの前に、一行が何を表すか決めよう
まず、集計したいことを一つ書いてみて。「商品ごとの売上金額を知りたい」なら、その金額のもとになる記録が必要です。
今回は、紙ものを売る仮のお店を考えます。商品はノートとポストカード、販路は店頭と通販です。以下の金額は説明用の仮の数字で、実在商品の価格ではありません。
列は、日付、商品名、販路、数量、売上金額の五つ。最初の行に、その列名を一つずつ入れます。
– 10月1日／ノート／店頭／数量2／売上金額1,000
– 10月1日／ポストカード／店頭／数量3／売上金額450
– 10月2日／ノート／通販／数量1／売上金額500
– 10月2日／ポストカード／通販／数量4／売上金額600
– 10月3日／ノート／店頭／数量1／売上金額500
ここで、一行は「商品別の販売明細」とします。五行あるから五人が買った、とは決めません。一人が二商品を買う場合もあり、今回の例には購入者や注文番号を入れていないからです。
数量2と書かれた行は、一行だけれど二個分。ここ、あとで件数との違いに戻ってくるよ。
売上金額は、その行に記録する金額として統一します。単価の列ではありません。数量が2で売上金額1,000なら、この例では金額をそのまま集計するので、さらに2を掛けて2,000にはしません。
実際の店へ移すときは、値引き、送料、返品などが金額にどう含まれているかを、自分の記録の定義で確認してね。税込の金額と税抜の金額、注文時と返品後の金額などを、説明せず一列へ混ぜないようにします。
会計上の確定した売上をこの記事の例だけで決める話ではなく、何を集計している表かをそろえる準備です。今回は返品や送料のない、五つの明細だけで進めます。
Microsoftの作成案内では、列ごとにデータをまとめ、一行の見出しを付けるよう説明しています。二行にまたがる見出しや結合セルは避ける案内です。
表を見やすくしたいから、同じ日付のセルを大きく結合したくなるかもしれません。けれど、集計する元の表は、各明細に必要な情報がある形へ。きれいな報告用の見た目は、集計する表と別に考えられます。
月の途中に空行を入れて区切ったり、末尾に自分で計算した合計行を混ぜたりする前にも、集計対象を確認しよう。販売明細とその合計を両方足してしまえば、同じ数字を二度数えることになります。
紙の帳簿から写す場合も、元の記録は残しておきます。表を作り直す作業と、原本を変更する作業は分けて、分からなくなった金額へ戻れるようにしてね。
元データの作り方も学ぶ候補として、Excelピボットテーブル第2版を楽天市場で確認する
最初は商品名を行へ、数量と金額を値へ置いてみよう
元の表ができたら、いよいよ集計です。ここでは、元データを保存したうえで、練習用のコピーで進める提案にします。
Windows版の公式手順では、元の表のセルを選び、［挿入］から［ピボットテーブル］を選びます。配置場所は、最初なら新しいワークシートにする方法があります。
別のシートへ集計表を作れば、元の販売記録と、結果を見比べやすいね。元のデータがどこかを分かる名前にしておくと、後の確認にも戻れます。
作成画面の範囲が、見出しと五つの明細を含むかを見てください。表の途中で選択が終わっていたら、全部の売上を集計したことにはなりません。
今回の例では、見出し一行と明細五行の範囲です。セルの場所は自分が表を置いた位置で変わるので、この記事のために特定の番地へ合わせる必要はないよ。
ピボットテーブルのフィールドが出たら、「商品名」を［行］へ置きます。「数量」と「売上金額」は、それぞれ［値］へ。
フィールドは、元の表の列名を、集計のどこへ使うかを指定するものと考えると追いやすくなります。商品名を縦に並べたいから行。数値を計算したいから値。この対応です。
チェックを付けたときの自動配置だけで終えず、目的の場所に入っているかを見よう。間違えたら、フィールドを移動して置き直せます。
五行の仮の例を合計すると、ノートは数量4、売上金額2,000。ポストカードは数量7、売上金額1,050になります。
おっ、商品ごとに見られる形になった！ただし、これは例の数字を足した結果。Excelを使えば売上が増える、という結果ではありません。
元の明細でも、ノートの数量は2と1と1、金額は1,000と500と500です。最初の練習は、このように少ない行数で、集計と元の記録を照合してみよう。
違う結果なら、何かを追加する前に、対象範囲、商品名の表記、集計方法を見ます。「ノート」と「ノート 」のような余分な空白や、別の名前で記録したものがないかも確認したいところです。
似た名前の商品を全部同じにしてよい、という話ではありません。サイズや品番の異なる商品を本当に別商品として扱うなら、その違いが分かる名前やコードを残してね。表記の揺れを直すことと、商品を勝手に一つへまとめることは分けます。
行と値の基本配置を練習する本として、同じISBNの第2版を確認する
合計が3になった？金額と件数の取り違えを見てみよう
ノートの売上は2,000のはずなのに、集計表に3と出たら？
あっ、売上の大小を考える前に、何を数えているかを見てほしい！ノートが出てくる明細は三行です。金額の合計ではなく、データの個数を数えている可能性があります。
Microsoftは、値に置いた数値は通常SUMで集計される一方、テキストとして解釈されるとCOUNTになると説明しています。金額の列に、数値と文字列が混在していないか確認する理由はここです。
画面に［売上金額の合計］などと出ているか、それとも［売上金額の個数］などの設定なのかを見よう。版や表示言語によって名称は変わるため、計算の意味で区別します。
Windows版の案内では、値のフィールドから［値フィールドの設定］を開き、集計方法を変更できます。合計したいなら、合計になっているかを確認してください。
ただし、設定を合計へ変えるだけで、元の列の問題が全部直るわけではありません。文字として入力された金額などがあるなら、どのセルがどう記録されているかを確認し、元の数値の意味に合わせて整える必要があります。
たとえば、金額の欄に「まだ確認中」と書いてある行があったら、その言葉を0へ置き換えれば解決、とはしません。0円の明細なのか、金額が分からないのかは違うからね。
未確認の明細はどこかを分かる状態にし、金額を確認してから集計へ反映する方法があります。表示をそろえるために、分からない数字を勝手に作らないようにします。
今回の五行では、ノートの明細数は3、販売数量は4、売上金額は2,000。ポストカードの明細数は2、販売数量は7、売上金額は1,050です。
三つの数字には、それぞれ役割があります。「ノートは3売れた」とだけ言うと、三個なのか三明細なのか分かりません。報告するときも、数量と明細数の名前を残してね。
さらに、この表では購入者数も注文数も確認できません。明細数を客数へ読み替えないことが大事です。客数を見たいなら、何を一人と数えるのか、別の記録が必要になります。
数字の見た目は、桁区切りなどで整えられます。ただし、カンマを付けてきれいに見せても、計算方法は変わりません。表示の調整は、集計の意味を確かめたあとでよいよ。
この例の売上金額2,000が、利益2,000という意味でもありません。仕入れや経費の列がないからです。金額の列の名前を省略せず、表が答えられる範囲で読むようにしよう。
販路を列へ置くと、同じ商品を店頭と通販で見比べられる
商品別の合計が分かったら、今度はどこで売れたかも見たくなるでしょう。
そのときは、「商品名」を行へ置いたまま、「販路」を［列］へ。値には売上金額の合計を置く方法があります。
行に商品、列に店頭と通販が並び、それぞれが交わる場所へ金額を集計する形です。見出しが二方向になるクロス集計として考えられます。
今回の仮の例なら、ノートの店頭分は1,500、通販分は500。ポストカードの店頭分は450、通販分は600です。
商品別ではノートの金額が大きい。でも、販路を分けると、ポストカードはこの五明細では通販分の方が大きい。ああ、合計だけでは見えなかった並び方があるんだね。
もちろん、これだけで「ポストカードは通販へ全部移そう」とは決めません。例は五明細だけですし、売った期間、並べた在庫、発送の手間、売り切れなどを記録していないからです。
集計表ができたことと、販路の変更を決められる材料がそろったことは別。まずは、次に何を確かめたいかを一つ選びます。
たとえば、通販へ載せていた日数も同じなのか。店頭に置いた数量が違っていなかったか。売り切れた期間があったのか。実際の記録へ戻れる問いを作ろう。
「通販の方が好まれる」と読者や顧客の気持ちまで決めるのではなく、この記録では通販の金額が大きかった、と書く。そのあと、どんな条件でそうなったかを確認できます。
数量も見たいなら、値へ数量の合計を追加する方法があります。二つの値を並べたときは、金額と個数の見出しが分かるようにしてね。金額だけ見ていた表に数量を足したのに、何の列か省略すると読みにくくなります。
一度に商品、販路、日付、担当者などを全部詰め込むと、最初の問いを見失うことがあります。今回は商品と販路の二つで十分。期間を変えたいときは、対象の期間を明示してから、比較したい形へ広げよう。
最初に集計した総額は、2,000と1,050を合わせて3,050です。販路で分けると、店頭1,950と通販1,100。この二つを合わせても3,050になります。
表の置き方が変わっても、同じ五明細なら総額は変わらない。ここまで元の記録と照合できれば、見せ方だけで判断が動いていないかを確かめられます。
単純集計とクロス集計を学べる第2版を、ISBNで楽天市場で探す
一行足したら、範囲と更新を確認。次に見る問いも残してね
集計表ができた！では、明日の記録を足したら、同じ表で続きを見られるかな？
ここで大事なのは、元データの追加と、ピボットテーブルへの反映を分けて確かめることです。
仮の例に、10月4日、ノート、通販、数量2、売上金額1,000という明細を一行追加してみます。これでノートの数量は6、売上金額は3,000。全体の数量は13、売上金額は4,050になる計算です。
追加したのに3,050のままなら、まずその行が集計対象の範囲へ入っているかを見よう。セル範囲を固定していた場合、下に書き足しただけでは、指定範囲の外に残ることがあります。
Microsoftは、Excelのテーブルを元データとして使うと、追加した行が更新時に集計へ含まれると案内しています。普通のセル範囲を使う場合は、必要に応じてソースデータの範囲を変更する案内もあります。
テーブルとして管理する方法を選ぶなら、書き足した行がそのテーブルの一部になっているかも、自分の画面で見てね。下のどこかへ数字を置いたことと、表へ追加したことを同じにはしません。
次は、集計を更新します。公式の更新案内では、対象のピボットテーブルを選び、［更新］を使う方法が説明されています。Windowsでは、集計内を右クリックして更新する方法もあります。
自動更新は版や設定で扱いが異なり、公式にはMicrosoft 365 Insider向け機能の注記もあります。自分の画面で、書き足せば全部自動だと決めつけず、対象範囲と反映した数字を確かめよう。
更新後は、ノートの店頭1,500、通販1,500、合わせて3,000になるかを見ます。全体の4,050も照合できれば、足した明細がどこへ反映されたかを追えます。
今回は、実際のExcel画面を操作した結果ではなく、仮の明細からの計算例です。練習するなら、自分の画面で元の行と集計を一緒に確認してみてね。
もっと操作を練習したい人へ、翔泳社の書籍案内では、基本操作、データの不備、集計方法、更新などの目次を確認できます。練習用Excelファイルも案内されていますが、この記事ではファイルを使った動作確認はしていません。
対象は第2版、ISBN9784798178738、2023年7月10日発売です。出版社ではA5・352ページ、確認したbookfanでは351Pと表示されていました。ページ数の差の理由は未確認なので、版とISBNを先に照合してください。
紙の本と電子版、使うExcelの版や操作画面も、買う前に確認したいね。価格、在庫、配送条件は注文時の販売表示へ戻ります。
最初は、商品別の合計が元の明細と合うところまで。次は、販路を分ける。記録を足したら更新する。
全部の分析を今日やらなくてもいいよ。まず、自分の表で一つの数字を説明できるようにしよう。その数字から次に確かめたい問いが残ったら、集計表を作る作業が、お店の判断へつながる入口になります！
