メインコンテンツへスキップ
見出し画像

平均単価が実感より高く出るとき。数量を重みにした一つの式に替えると、本当の1個あたりが見えてきます

    平均単価を出したら、2,520円と出ました。

    でも、売れているのはほとんどが100円前後の文具です。2,520円は、どう考えても高すぎる。

    実際に「1個あたりいくらで売れたか」を計算し直したら、答えはたったの141円でした。差は2,000円以上。

    同じ表、同じ数字から出したのに、です。

    画像

    犯人は、平均の出し方でした。単価の列をそのまま平均すると、たくさん売れた安い商品も、数個しか売れていない高い商品も、同じ重さで扱われてしまうんですね。

    この記事では、その食い違いがなぜ起きるのか、そして数量を重みにした本当の平均単価をAIに出してもらう手順を、実際に動かした画面でたどっていきます。使ったのは架空の文具店の売上です。

    🔍 平均単価が、実感よりずっと高い

    やりたいことは単純でした。この店の平均単価、つまり「1個あたり、平均いくらで売れたか」を知りたい。

    単価の列は B列にあります。なので、=AVERAGE(B2:B6) と書いて平均を出しました。

    出てきた数字が、2,520円です。

    画像

    ここで手が止まりました。だって、売れ筋はノートやボールペンで、単価は80円から250円。いちばん安いのは80円です。

    それなのに平均が2,520円。いちばん高い単価より下、いちばん安い単価より上、という意味では範囲に入っているので、警告も、赤い記号も出ていません。

    数字としては正しく計算されている。でも、実感とは合わない。この気持ち悪さが、今回の入口でした。

    💡 数量を重みにして、AIに出してもらう

    なにが起きているのか、そのままAIに聞いてみました。

    「平均単価を知りたいけれど、AVERAGE で出したら高すぎる気がする。数量を反映した出し方を教えてほしい」と。

    画像

    返ってきたのが、この式でした。

    =SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)
    

    意味はシンプルです。SUMPRODUCT(B2:B6,C2:C6) が、行ごとに「単価 × 数量」を計算して全部足した売上の合計。SUM(C2:C6) が、売れた個数の合計。売上の合計を、売れた個数の合計で割る。これで「1個あたり、平均いくらで売れたか」が出ます。

    数字を当てはめると、売上の合計は466,000円。売れた個数は3,305個。466,000 ÷ 3,305 で、約141円。

    これが、数量を重みにした平均=加重平均です。

    画像

    同じ表から、2,520円と141円。17倍以上ちがう2つの数字が、どちらも「平均単価」として出てきました。

    なぜ単純平均は、数量を無視するのか

    AVERAGE は、1行を「1票」として数えます。

    高級万年筆は5個しか売れていません。でも AVERAGE から見れば、800個売れたノートと同じ「1票」なんですね。

    万年筆の単価は12,000円。この1票が、5つの単価の平均をぐっと引き上げていました。たった5個の商品が、3,305個ぜんぶの平均を支配してしまう。これが、2,520円の正体です。

    加重平均のほうは、数量ぶんだけ票を持つイメージです。800個売れたノートは800票、5個の万年筆は5票。だから、たくさん売れた安い商品に平均が引っぱられて、実態に近づきます。

    どちらも計算としては正しい。ちがうのは「何を平均しているか」なんです。単純平均は商品の種類を平均し、加重平均は売れた1個1個を平均している、というちがいですね。

    ⚠️ ひとつだけ、機械に決めさせないこと

    便利な式ですが、落とし穴もあります。そこもAIに聞いてみました。

    画像

    いちばん大事だったのは、「何を重みにするか」は計算の前に人が決める、という話でした。

    • ✅ 「1個あたりいくらで売れたか」を知りたいなら、数量で重みをつける(今回の式)。

    • ✅ 「売上の中心はどの価格帯か」を知りたいなら、売上金額で重みをつける。別の数字になります。

    • ⚠️ 「品ぞろえの価格はどのくらいか」なら、単純平均にも意味がある。

    目的がちがえば、正しい式も変わります。Excelは計算はしてくれますが、「あなたがいま何を知りたいのか」は教えてくれません。そこだけは、こちらで決める必要があるんですね。

    もう一つ、数量や単価が「見た目は数字でも中身は文字列」だと、その行だけ静かに計算から抜けることがあります。AVERAGE も SUMPRODUCT も、文字列をエラーにせず無視してしまうからです。心配なときは COUNT と COUNTA の件数を比べると、文字列が混ざっていないか分かります。

    出した平均単価を、そのまま信じない

    数量が空欄や0だと分母がおかしくなることもあるので、出た数字は一度、確かめておきたいところです。

    いちばん楽なのは、逆から戻してみること。加重平均に総数量をかければ、総売上に戻るはずです。

    画像

    141円(正確には140.998…円)に3,305個をかけると、466,000円。総売上とぴたりで、判定は「一致」。そして141円は、いちばん安い80円といちばん高い12,000円の間に、ちゃんと収まっています。確かめ方も、AIが手順で返してくれました。

    画像

    最後に、商品を1つ足して、ちゃんと追いかけるかも見ておきました。60円の消しゴムを1,000個、表に足してみます。

    画像

    加重平均は141円から122円に下がりました。安い商品がどっと増えたので、1個あたりが下がる。実態どおりの動きですね。いっぽう単純平均は2,520円から2,110円へ、ほとんど動きません。1,000個という数量を、まったく見ていないからです。

    この動き方のちがいを見ると、どちらが「1個あたり」を表しているかが、はっきりすると思います。

    🧰 使うときの、小さなこつ

    • ✅ 1個あたりの平均単価は、AVERAGE ではなく SUMPRODUCT(単価, 数量) / SUM(数量)。

    • ✅ 範囲を広めに取っておくと、商品が増えても空白行は計算に入らない。表を Ctrl+T でテーブルにすると、行を足したとき範囲も自動で広がります。

    • ⚠️ 単価と数量の範囲は、必ず同じ行数にする(B2:B6 と C2:C6)。ずれるとエラーになります。

    • ⚠️ 何を重みにするか、そもそも何を知りたいのかは、式を書く前に決める。

    平均が実感とずれたときは、たいてい「何を1票にしているか」がずれています。1個あたりを知りたいのに、商品の種類を平均していた。そこに気づけると、出した平均単価を、そのまま人に見せられるようになりますね。

    🛠️ 自分の売上でも同じことをしたいけれど、作る時間が無い。そういう時は、表をそのまま預かって組んでいます。

    この続きで読まれているのは、この2本です。

    同じ型のテンプレートを、会員向けにも配っています(月500円から)。

    ほかの記事は、テーマ別の目次にまとめています。

    作ったものは、全部ここにまとめています。

    📩 LINEはこちらです。よかったら、のぞいてみてください。

    画像

    https://lin.ee/UqFmzfB

    #Excel #仕事効率化 #加重平均 #平均単価 #SUMPRODUCT #エクセル関数 #データ分析 #AI活用 #ChatGPT #業務効率化

     
     
    \初心者OK!自動化・効率化できるExcel教室/ ◇AI×Excelの仕事時短術で自分の時間爆増! ◇自分だけの自動化ツールがつくれる! ◆元PC平凡▶半年で職場のExcel先生 ◆AIを使った自動化ツール作成数100超

    あなたへのおすすめ