売上を予測できれば仕入れを適正化でき利益を伸ばすことができます。なぜなら廃棄や機会損失を大幅に削減できるからです。
コンビニのような大手企業は強力なシステムを構築して売上を予測して業績を伸ばしています。しかし、これは大企業にしか出来ないことなのでしょうか？
そんな疑問を持つ方に朗報です。技術の進歩により Excel だけで簡単に AI を使って 売上予測ができるようになりました。
その名も Python in Excel という機能で、Excel上で Python のコードを実行できる機能です。
Python を使うには、通常なら Python のインストール、環境構築、エディタでのコード作成、コマンドプロンプトからコードの実行 など、一般ユーザにはハードルの高い操作が必要ですが、Python in Excel なら Excel 上の操作だけで完結します。
この記事では Python in Excel の導入、使い方、そして売上予測の具体的方法 までを解説します。
プログラミングなちょっと難しいという方にも取り組めるように、プログラムはコピー&ペーストするだけで売上予測出来る手順にしていますので、是非トライしてみて下さい。
この機会に新しい技術に触れてみては如何でしょうか？
Python in Excel とは？
概要
Python in Excel は Excel のセル内に入力した Python のプログラム・コードを実行できる機能です。
VBAとは異なり Python のコードは Microsoft クラウドで実行されます。そのため使っているPCには Python をインストールする必要がありません。
しかも Python in Excel では Python の大きな魅力でもある 豊富なライブラリが最初から使える ようになっており、様々な処理が可能になっています。
Python in Excel は何に使うべきか？
さて、Excel もかなり優秀なデータ処理ツールですから、Python と機能的にかぶる部分があります。では、Python in Excel は何に使うのが良いでしょうか？
Python にあって Excel に無いもの を考えてみるとやはり 強力なAI機械学習 ライブラリ が思い当たります。AI機械学習は、今までの Excelでは逆立ちしても実現できなかった機能です。
Python in Excel で AI機械学習を簡単に使えるようになったということです。これは画期的なことです。
Python in Excel の導入方法
本記事執筆時点で、Python in Excel の機能はEnterpriseユーザ と Business ユーザ つまり会社で使うユーザーに正式リリース され、個人ユーザにはベータ版が提供 されています。
ここでは、個人向けのベータ版の導入方法を解説します。手順は以下のようになります。
- Excel のメニューから、ファイル ⇒ アカウント をクリック
- Microsoft 365 Insider ⇒ Channel を変更します をクリック
- ベータ チャネル を選択して OK をクリック
- ベータ版のダウンロード＆インストールが実行される
ベータ版が正しくインストールされればもう Python in Excel を使えるようになっているはずです。
どこか適当なセルに =py( と入力してみましょう。セルに PY と緑色に表示 されたら成功です。ここは エスケープキーを押して入力をキャンセルしておきましょう。
Python in Excel の使い方
Python セルの作成
先ほど説明したように、セルに =py( と入力すると、セルが Pythonセル になります。
または、メニューから 数式 ⇒ Pythonの挿入 をクリックすることで、セルが Pythonセル になります。
Excel シート上の値を Python に取り込む
Excel シート上の値を Pythonに取り込むには xl() 関数を使います。
範囲、テーブル、Power Query、名前付きの範囲 などからデータを取り込むことが可能です。それぞれ、下の図のようにして指定します。
範囲
テーブル
Power Query
Python のコード確定は Ctrl + Enter
Python のプログラムを Python セルに入力し終わったら、最後に Ctrl + Enter を入力してコードを確定します。
するとコードが実行されます。プログラム事項中は、Busy! という表示で、実行が終わると何らかの表示になります。エラー表示の意味は次のようになっています。
| #PYTHON! | Python の構文エラーです。 |
| #CALC! | xl()関数で参照する先に問題があります。RAND関数を使用したセルや、ブック内のテーブルを参照する Power Query など、値が変化してしまう参照先は読み込むことができません。 |
| #接続! | Microsoft Cloud との接続に問題があります。Ctrl + Alt + Shft + F9 キーで接続を更新してみて下さい。 |
| #スピル! | Python 出力範囲内のセルに既にデータがあるため、出力できません。 |
| #TIMEOUT! | Python の最大実行時間を超えています。 [ファイル > オプション] > [詳細] > [このブックを計算するときに] に移動し、必要に応じて [Python 数式のタイムアウト] を設定します。 |
Python の処理結果を表示
Python セルの値は入力した コードの最終行の値 になります。単一の値で無い限り Python のオブジェクト（DataFrame や プロットなど）の型名がセルに表示され、中身は表示されません。
内容を見るためには 図のように、Python セルの右に表示されるアイコンをクリックして出てきた メニューから選択します。すると、Python セルの右に情報が表示されます。
- arrayPreview：データフレームの一部を プレビュー表示 します（これが便利）
- Python_str：オブジェクトの先頭部分を文字列で表示します
- Python_type：オブジェクトの情報を表示します
- Python_typeName：オブジェクトの型名を表示します
実はこの操作で Python セルの 右隣りのセルに関数式が入力 されています。これは別のセルに入力することも可能です。
プロット
matplotlib を使ってプロットを作成する場合も、コードを入力した時点ではセルに [py] Image と表示されるだけです。
プロットの描画オブジェクトを表示させるためには、Excelの値 に変換した後、参照の作成 をします。
試しに以下の matplotlib を使ったコードを表示してみましょう。Python セルに下記コードをコピー＆ペーストしてみて下さい。（コードの右上の四角のアイコンをクリックするとコード全体がコピーされます）
import numpy as np
import matplotlib.pyplot as plt
from mpl_toolkits.mplot3d import Axes3D
# x, y の範囲とメッシュグリッドを作成
x = np.linspace(-5, 5, 100)
y = np.linspace(-5, 5, 100)
X, Y = np.meshgrid(x, y)
# z = cos(sqrt(x^2 + y^2) / pi) の計算
Z = np.cos(np.sqrt(X**2 + Y**2) / np.pi)
# 3Dプロットの作成
fig = plt.figure()
ax = fig.add_subplot(111, projection='3d')
# サーフェスプロット
ax.plot_surface(X, Y, Z, cmap='viridis')
# ラベルを追加
ax.set_xlabel('X axis')
ax.set_ylabel('Y axis')
ax.set_zlabel('Z axis')
# グラフの表示
plt.show()上記で説明した2ステップの手順で操作すると図のようにプロットが表示されました。
このような高度なグラフのプロットは Excel にはできないので、高度なプロットを作成したい方にとっては Python in Excel はかなり便利な便利な機能と言えるでしょう。
AI機械学習とは
AIは Artificial Interigence /人工知能 のことです。人口知能は人間の代わりに考えて答えを出してくれるもの と言えます。
機械学習というのは AI を実現するための手法の一つで、データを学習して何らかのアウトプットを出す仕組みになります。
今回は天候、気温、イベント有無などの情報から 売上を自動で予測するAI を作ります。
従来であれば、ベテランの方が 経験と勘 によって売上予想をして仕入れの量などを決めていたかもしれません。これを科学的なアプローチで データを計算評価して売上を予測するのです。
適切なデータを用意することで従来の 経験と勘に頼った予測よりも高精度な予測 ができることが分かっています。
Python はAI機械学習の機能を持っています。更に今回 Python in Excel という機能で Excel から簡単に Python を扱えるようになったという訳です。つまり、Excel ユーザー がAI機械学習を扱えるようになった ということです。
これは使わない手はありませんね。
今回はこの売上予測を簡単にできる手順をご紹介します。では、早速いってみましょう！
手順の概要
実はAI機械学習の手順はとてもシンプルです。ですので、今回の手順も以下のような流れで完成となります。
- 学習用の売上データを作成
- 予測用のデータを作成
- Python コードを Python セルにコピー＆ペースト して予測値を計算
当然、AI機械学習は奥の深い世界ではありますが、構図としては上記の3ステップ程度のものになるのです。
では、それぞれのステップを詳しく解説していきます。
なお、以下のステップを全て処理した サンプルファイル を以下のリンクから入手できますので、よろしければ参考にされて下さい。
学習用データの作成
AI がどんなに賢くてもインプットが無ければ何もできません。AI に学習して貰うためにデータを準備して与える必要があります。
ではどんなデータを与えれば良いのでしょうか？
売上
まず、最初に 売上のデータ です。日時、品目、売上額 など売上に関連したデータを準備します。レジにデータがあるのであればそれをそのまま持ってくれば良いでしょう。
紙の帳簿しか無いのであれば、日付と１日の売上額というシンプルなデータでも十分使い物になると思います。
売上に影響しそうなもの
おそらくベテランの方が経験と勘で予測するときも、天気や気温、近隣のイベントなどから予測するのではないでしょうか？
AI機械学習にとっても同じです、売上に影響しそうなデータを与えれば正確な予測ができます。例えば以下のようなデータ項目が考えられます。
- 曜日
- 祝日
- 季節 や 月
- 天気
- 気温
- 広告キャンペーン
- インスタ投稿
- 競合店の広告キャンペーン
- 近隣のお祭りなどのイベント
気象の情報は気象庁のホームページなどインターネット上に情報が公開されているのでそれらを活用すると良いでしょう。
それ以外のデータは地道に自分で集めて来るしか無いかもしれません。しかし、正確な予測にはこれらのデータが重要なので、頑張って集めてみましょう。
データが集まったら Excel のシートにデータを記入して下さい。記入したデータは次の２つの処理をします。
テーブル化する
- 重要！Puthon のプログラムを正しく動かすために、売上額のデータ列の名前は「売上」と設定して下さい。
- 記入したデータ範囲のセルを 全て選択
- メニューから ホーム ⇒ テーブルとして書式設定 ⇒ 適当にスタイルを選択
- 出てきたダイアログで、「先頭行を見出しとして使用する」 にチェック を入れる
- OKボタン を押す
テーブル名を 「テーブル1」 とする
- 書式設定されたテーブル範囲の 任意のセルを選択
- メニューから テーブルデザイン を選択
- 一番右のテーブル名の欄に テーブル1 と記入（先に「テーブル1」というテーブルがあると設定できないので注意）
予測用データの作成
予測用データというのは前項で作成したデータのうち 売上額だけが無いデータ です。天気は天気予報で分かりますし、イベントの有無や曜日、祝日もあらかじめ決まっています。
これらのデータを与えると AI は売上額を予測してくれるというハナシです。
作成したデータは学習用データと同じようにして、以下の設定をして下さい。
- 予測用データの テーブル化
- テーブル名を「テーブル2」とする
Python によるAI機械学習
さて、いよいよ Python によるAI機械学習です。プログラムが苦手な方もご安心下さい、コードをコピー＆ペーストするだけです。
どこで良いので Excel シート上のセルで =py( と入力して Python セルにします。
そこに、下記のコードをコピー＆ペーストして下さい。（コードの右上にある四角いアイコンをクリックするとコード全てがクリップボードにコピーされますので活用して下さい。）
コピー＆ペーストでももちろんOKですが、コードの中身を勉強してみたい方 に向けて コード内のコメントで解説していますので、是非ご一読下さい。
# 必要なライブラリをインポート
# pandas => データの操作 numpy => 数値計算 sklearn => 機械学習モデルの構築と評価
import pandas as pd
import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.preprocessing import StandardScaler
from sklearn.ensemble import RandomForestRegressor
from sklearn.metrics import mean_absolute_error, mean_squared_error
# データの読み込み
# xl() 関数を使用して、Excelの「テーブル1」からデータを読み込みます。
# xl() 関数は Python in Excel 固有の関数です。これで Excel 上のデータを Python に取り込めます。
# このコードでは、すべての行・列を読み込むよう指定されています。
df = xl("テーブル1[#すべて]", headers=True)
# 特徴量 X と ターゲット Y に分割
# 予測したい「売上」列（ターゲット変数）と、予測のために使用する特徴量（その他のデータ）に分けています。
# Xには特徴量を、yにはターゲット変数を保持します。
X = df
X = X.drop(columns=['売上'])
y = df['売上']
# 学習用データセットとテスト用データセットに分割（80%:20%）
# このように分割することで、モデルが学習に使用されなかったデータでテストされ、予測の精度を検証できます。
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, random_state=42)
# 特徴量のスケーリング（標準化）を行います。
# これにより、各特徴量が平均0、標準偏差1の分布に変換され、モデルの精度が向上します。
scaler = StandardScaler()
X_train_scaled = scaler.fit_transform(X_train)
X_test_scaled = scaler.transform(X_test)
# モデルの初期化と学習（ランダムフォレストを使用）
model = RandomForestRegressor(n_estimators=100, random_state=42)
model.fit(X_train_scaled, y_train)
# テストセットを使って予測
y_pred = model.predict(X_test_scaled)
# モデルの評価（RMSE: Root Mean Square Error 誤差値2乗の平均の平方根）
rmse = np.sqrt(mean_squared_error(y_test, y_pred))
error_rate = rmse / y_test.mean()すると Microsoft Cloud上での処理が動いて何か数値が表示されたと思います。この数値は、予測精度を表す数値で割合を示しています。メニューから ホーム ⇒ % ボタン を押すとパーセント表示なります。
サンプルファイルでは 予測精度が 4.34% となっています。これはかなり高い精度ですね。
予測と実績の比較を確認
予測精度の値は出ましたが、イマイチピンと来ないという方は売上実績データと AI機械学習で予測したデータの比較を見てみましょう。
以下のコードを Python セルに入力して、上述の Pythonのプロットを表示した手順と同様に以下の操作をして、Python出力 を Excelの値 に設定して実績と予測のテーブルを表示します。
- Python セルを右クリック
- サブメニューから Python 出力 ⇒ Excelの値 をクリックします
import pandas as pd
# テストデータのインデックスをリセットして、予測値と正解値を並べる
df_comparison = pd.DataFrame({
'実績値': y_test.reset_index(drop=True), # 正解（実際の売上）の値
'予測値': y_pred # 予測された売上の値
})
# データフレームの先頭10行をサンプルとして表示
df_comparison.head(10)これを Excel のグラフにすると視覚的に実績と予測を比べることができます。サンプルファイルでは下の図のようになりました。かなりの精度で予測できていることが目で見ても良く分かりますね。
学習モデルで売上を予測
AI機械学習で学習したものを 学習モデル といいます。この学習モデルに聞けば未来の売上額を予測してくれます。
予測に用いるのは先ほど作成した予測用データになります。これは売上以外の未来のデータになります。
以下のコードを Python セルにコピー＆ペースト しましょう。
# 予測用のデータの読み込み。売上額以外のデータが入っています。ここから売上を予測します。
Xp = xl("テーブル2[#すべて]", headers=True)
# 特徴量のスケーリング（標準化）前の工程と同じです。
scaler = StandardScaler()
Xp_scaled = scaler.fit_transform(Xp)
# 予測用データを使って予測。ypに売上予測値が代入される。
yp = model.predict(Xp_scaled)この Pythonセルも先ほどと同じようにして Python出力 を Excelの値 に設定します。すると、見事に予測データが表示されました。
当然ですが、この予測データをどのように活用するかはご自身の判断になります。
予測精度を上げるには
ここで使った Python のAI機械学習は確立されたもので、その質は世界でも認められています。しかし、だからと言って正確な予測ができる訳ではありません。
どんな道具も使い方次第で結果は大きく変わります。AI機械学習の世界ではこの方式が絶対に良いというものは無く、使う場面に合わせて試行錯誤する必要があったりします。
では、予測精度を上げるにはどうしたら良いのでしょうか？
予測精度を上げるには、AI機械学習に与えるデータの質を良くすることが最も重要 です。人間も間違った教育を受けてしまっては賢くならないのと同じです。
データの質はより売上に関連したデータを揃えることで向上します。売上と全然関係の無いデータをいくら与えても仕方ありません。
例えばサンプルファイルでは日付のデータは無く、月、曜日、祝日のデータにしています。日付そのものはあまり意味は無く、そこから別の情報を取り出してデータとして与えています。
月であれば季節に関係しており、季節に連動した人の動きを捉えることができるかもしれません。
また、曜日の情報があれば、ファミリー向けの週末に利用の多いお店 や逆に ビジネス街の平日に利用の多いお店 の売上を的確にとらえることができると考えられます。
難しい計算処理は Python を使ってコンピュータが計算してくれます。人間はその上で「どんなデータが売上に影響するだろうか？」ということに頭を使えば良いということです。
是非、色々と試してより精度の高い売上予測の仕組みを構築されて下さい。
まとめ
Excel は次々と機能が向上して一般ユーザはより高度な機能を簡単に使えるようになっています。
手間のかかる計算処理はコンピュータが処理してくれるようになり、人間はより創造的な仕事をすることが求められているとも考えれます。
新しい機能を使いこなせるようになると仕事もより高度になって楽しさも増してきますので、是非みなさんも新しいことにチャレンジしてみて下さい。
