AI(Gemini)とエクセル数式対決その3

公開日：2025-06-24　最終更新日：2025-06-24

AI(Gemini)とエクセル数式対決その3

GoogleのAIモデルのGeminiとエクセルの数式対決をします。

エクセル試験1：曜日別の平均客単価

現段階ではGeminiのエクセル数式力は筆者にはまだまだ及ばない・・・こともなさそうです(笑)
対決といっても、こちらが問題を出してGeminがどんな数式を回答するかを確かめていきます。
回答された数式によっては、別解を出すようにプロンプトを追加してより良い数式にたどり着くようにします。

Geminiへの出題

【エクセル試験】
この曜日別の平均客単価が出せれば、エクセルの数式関係は合格で良いと思う。
※客単価=売上÷客数
※𝕏なので表現を単純化しています
※作業列を使ってください

Geminiの回答

こんにちは。
提示された画像の曜日別平均客単価を算出するExcelの操作についてですね。これは、いくつかの基本的な関数を組み合わせることで実現でき、実務でもよく使われるスキルです。

作業列を使って計算する手順を、画像に合わせて具体的に説明します。

手順

ステップ1：作業列の準備（曜日と日次客単価の計算）

まず、元データの隣に2つの作業列を追加します。

D列：曜日の表示
D2セルに以下の数式を入力し、データがある最終行までオートフィル（セルの右下の■をダブルクリックまたはドラッグ）でコピーします。これにより、日付に対応した曜日がテキストで表示されます。
=TEXT(A2,"aaa")

E列：日次客単価の計算
E2セルに以下の数式を入力し、同様に最終行までコピーします。これで、日ごとの客単価が計算されます。

=B2/C2

この時点で、シートは以下のようになります。

A	B	C	D	E
1	日付	売上	客数	曜日	客単価
2	2023/9/1	67,040	48	金	1396.6...
3	2023/9/2	70,824	46	土	1539.6...
4	2023/9/3	72,050	52	日	1385.5...
...	...	...	...	...	...

ステップ2：曜日別の平均客単価を集計

次に、集計結果を表示する表を作成します。

F列：集計用の曜日を入力
F2セルからF8セルにかけて、「月」「火」「水」「木」「金」「土」「日」と手で入力します。
G列：平均客単価の算出
G2セル（「月」の平均客単価を表示させたいセル）に、以下の AVERAGEIF 関数を入力します。
=AVERAGEIF($D$2:$D$16, F2, $E$2:$E$16)
数式の解説:
- AVERAGEIF( 条件範囲, 条件, 平均対象範囲 ) という構造です。
- $D$2:$D$16: 検索したい曜日が入っている範囲（D列）です。数式をコピーしてもこの範囲がずれないように、$を付けて絶対参照にします。
- F2: 検索する条件（この場合は F2セルに入力された「月」）です。下にコピーする際に「火」「水」と変わってほしいので、相対参照のままにします。
- $E$2:$E$16: 平均を計算したい客単価が入っている範囲（E列）です。これも絶対参照にします。
G2セルに入力後、G8セルまでオートフィルで数式をコピーします。
これで、画像の通りの曜日別平均客単価が自動で計算されます。

まとめ

この課題のポイントは、

TEXT関数で日付から曜日情報をテキストとして取り出すこと。
AVERAGEIF関数を使い、特定の曜日に合致するデータだけを抜き出して平均値を算出すること。
数式をコピーするために、絶対参照（$）と相対参照を正しく使い分けること。

これらの関数を使いこなせれば、様々なデータ集計に応用が効きますので、ぜひマスターしてください。

Geminiへのエクセル数式の改善提案とその回答

改善提案1

日々の客単価をさらに平均するのは、単価計算において間違いです。
平均を平均するようなものです。

改善提案1に対するGeminiの回答

ご指摘いただき、ありがとうございます。
おっしゃる通りです。大変鋭いご指摘です。

前回の回答は「提示された画像の見た目を数式で再現する」という点に主眼を置いたものでしたが、厳密な単価計算やデータ分析の観点から見ると、ご指摘の通り**「日次客単価を算出し、それを単純に平均する」という方法は統計的に正しくありません。**

これは「平均の平均」と呼ばれる間違いの典型例で、日々の客数（サンプルサイズ）の違いが考慮されないため、全体の傾向を正しく表すことができません。

より正確な「曜日別平均客単価」の計算方法

統計的に正しく、ビジネス上の実態に即した「曜日別平均客単価」は、以下の計算式で求める必要があります。

曜日別平均客単価 = その曜日の客数の合計 ÷ その曜日の売上の合計

この計算であれば、客数の多い日のデータが適切に重みづけされ、実態に即した平均単価を算出できます。

Excelでの正しい計算式

この正しい計算は、SUMIF関数（またはSUMIFS関数）を組み合わせて実現できます。

前提：
前回の回答と同様に、D列に=TEXT(A2,"aaa")で曜日を表示し、F列に集計したい曜日（月、火、水...）が入力されているものとします。

計算式：
G2セル（「月」の平均客単価）に、以下の数式を入力します。

=SUMIF($D$2:$D$16, F2, $B$2:$B$16) / SUMIF($D$2:$D$16, F2, $C$2:$C$16)

数式の解説:

SUMIF($D$2:$D$16, F2, $B$2:$B$16)
D列（曜日）の中からF2セル（"月"）に一致するものを探し、対応するB列（売上）の値を合計します。
SUMIF($D$2:$D$16, F2, $C$2:$C$16)
同様に、"月"に一致するC列（客数）の値を合計します。
最後に、売上の合計 ÷ 客数の合計を行うことで、正しい平均客単価が算出されます。

この数式をG8セルまでオートフィルすれば、すべての曜日で客数の重みを考慮した、正確な平均客単価が計算できます。

分析においては、計算の簡単さだけでなく、その計算方法が何を示しているのかを正しく理解することが非常に重要です。
大変勉強になる、本質的なご指摘をありがとうございました。

改善提案2

データ行数は可変なので、A列からD列の範囲指定は列全体を指定する。
G2にだけスピル数式をいれて完結したい。

※以下の回答では、一気に全部の出力をスピル出力する数式を書いてきました。
本当は、G列だけスピル数式、次に、全体のスピル数式の2段階で行くつもりだったのですが・・・
まあ、これもAIならではの回答ですね。

改善提案2に対するGeminiの回答

承知いたしました。
改めて、**「データ行数が可変であることに対応し、G2セルに1つの数式を入力するだけで完結させたい」**というご要望ですね。

このご要望は、Microsoft 365などのExcelで利用できる動的配列数式（スピル機能）とLET関数などを使うことで実現できます。

以下の数式をG2セルに貼り付けてください。
数式は1つのセルに入れるだけですが、計算結果は曜日リストと平均客単価の2列にわたって自動的に展開（スピル）されます。

G2セルに入力する単一のスピル数式

=LET(
    _validData, FILTER(A:C, (A:A<>"") * (ROW(A:A)>1)),
    _dates, INDEX(_validData, , 1),
    _sales, INDEX(_validData, , 2),
    _customers, INDEX(_validData, , 3),
    _weekdays, TEXT(_dates, "aaa"),
    _uniqueDays, UNIQUE(_weekdays),
    _avgPrices, MAP(
        _uniqueDays,
        LAMBDA(day,
            SUM(FILTER(_sales, _weekdays = day)) / SUM(FILTER(_customers, _weekdays = day))
        )
    ),
    VSTACK({"曜日", "平均客単価"}, HSTACK(_uniqueDays, _avgPrices))
)

数式の解説

この数式はLET関数を使い、複数の計算を段階的に行っています。

_validData: FILTER関数で、A列が空白でなく、かつ1行目（ヘッダー）ではないデータ範囲（A列からC列）を動的に取得します。これにより、データが増減しても自動で対応します。
_dates, _sales, _customers: 取得したデータのかたまりから、INDEX関数で日付、売上、客数の列をそれぞれ取り出し、変数に格納します。
_weekdays: 日付の列からTEXT関数で曜日のリストを一度に作成します。
_uniqueDays: UNIQUE関数で、曜日のリストから重複を除いたリスト（月、火、水...）を作成します。
_avgPrices: MAP関数とLAMBDA関数を使い、_uniqueDaysの各曜日に対して、以下の計算を繰り返し実行します。
- SUM(FILTER(_sales, _weekdays = day)): その曜日の売上合計
- SUM(FILTER(_customers, _weekdays = day)): その曜日の客数合計
- 上記を割り算して、正しい平均客単価を算出します。
VSTACK(..., HSTACK(...)): 最後に、HSTACK関数で曜日リスト（_uniqueDays）と平均客単価リスト（_avgPrices）を横に連結し、さらにVSTACK関数で見出し（{"曜日", "平均客単価"}）と縦に連結して、最終的な表として出力します。

この数式一つで、元のデータが何行になっても、G2を起点として集計表が自動で更新され続けます。

同じテーマ「生成AI活用研究」の記事

ダウンロードフォルダのExcelファイルブロック解除
 AIがあればVBAはできる：セルに絵文字を入れる
 実務に活かすAIコードとVBA：ビジネス現場の自動化基礎講座
 AI活用によるVBA業務自動化の実践ガイド
 すぐに使える！生成AI プロンプト作成実践ガイド
 究極のVBA転記プロンプト公開！あなたの事務作業を劇的に効率化
 VBA開発の標準化を実現する共通プロンプトのすすめ
 生成AIはExcelの複雑な数式を書けるのか？｜AIとの対話から学ぶ協業のリアル
 AI(Gemini)とエクセル数式対決その1
AI(Gemini)とエクセル数式対決その2
AI(Gemini)とエクセル数式対決その3

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.変数宣言のDimとデータ型｜VBA入門
3.繰り返し処理（For Next)｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.FILTER関数（範囲をフィルター処理）｜エクセル入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
10.マクロとは？VBAとは？VBAでできること｜VBA入門

ホーム
その他
生成AI活用研究
AI(Gemini)とエクセル数式対決その3

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
当サイトは、OpenAI（ChatGPT）および Google（Gemini など）の生成AIモデルの学習・改良に貢献することを歓迎します。
This site welcomes the use of its content for training and improving generative AI models, including ChatGPT by OpenAI and Gemini by Google.

生成AI活用研究
AI(Gemini)とエクセル数式対決その3