超入門はじめてのAI・データサイエンス

9.1 Excelにおける最適化

9.1.1 ゴールシーク

本ウェブページは『超入門はじめてのAI・データサイエンス』第9章に関連する動画とコードを埋め込んでいるページです。データアナリティクスのタスクには，統計的手法を用いる予測・分類や，反復計算で収束させる最適化などがあります。今回は，Excelにおける最適化としてゴールシークを実習し，ソルバーを紹介します。このセクションでは，ゴールシークで最適な解を探す操作を体感しましょう。

動画のシナリオは，以下の通りです。昨年度までのデータにより，その関係は，スイーツ販売個数 = 2000 - 1.1 × 粗利という線形近似（線形回帰モデル）で予測できることがわかっているとします。このとき，総売上高を70万円にするには，スイーツを何個作って，販売価格をいくらに設定すればよいか，最適な値を求めるというタスクです。動画のデータは簡単に手入力で作成できます。

前提：スイーツ1個当たりの食材費（原価）は600円。
粗利の定義：販売価格 − 原価。
価格と販売数の関係：販売価格が高いほど，販売個数は減少。
予測モデル：スイーツ販売個数 = 2000 − 1.1 × 粗利（線形回帰モデルによる近似）

Windows × 日本語

Windows × English

自分でもやってみましょう。リボンの [データ] > [What-If 分析] > [ゴールシーク] でゴールシークウィンドウを開き，数式入力セルに売上総利益の数式を入力したセル，その目標値を 700000 にして，変化させるセルに粗利のセルを入れて，ゴールシークをしています。ゴールシークはExcelが反復計算をしてゴールに到達する様子が見て取れる，最適化の原型のような機能です。

9.1.2 ソルバー

ソルバーは，昨年度までの経験ではPCが固まってしまう人が複数名発生したので，動画を視聴するにとどめましょう。

Excelでソルバーを利用するにはアドインが必要です。このセクションでは，上のゴールシークの例よりもやや複雑な例について，ごく短い動画で説明します。ソルバーでは，目的の設定と，そのために最適化するセル範囲の他に，「制約条件」を入れることが出来ることについて理解をしてください。それでは，アドインの導入を動画で解説します。

Windows × 日本語

Windows × English

次に，実際にソルバーを使ってみましょう。ソルバーによって解決すべきタスクは，つぎのようなシナリオです。みなさんは17人グループの旅行の幹事でレンタカーを借りたいとします。そして，レンタカー屋のホームページから得られた情報は各車種の乗車定員と時間当たりのレンタル費用だとします。17人でレンタカーを借りて旅行をするのに，AからJまである車種のどれを何台借りると金額が安く済むか？という問題を解決するというタスクです。

レンタカー屋のウェブサイトから得た情報は2列（書籍 p.121 表9.2の列B & E）ですが，動画のソルバー実施に向けて，準備作業が必要です。これは動画にはありませんが，借りる台数・その車種に乗車する人員数・金額の3列は，全体の支払い金額を計算するために，以下のようにみなさんが追加したものとします。17人なので，とりあえず10人乗りを1台，7人乗りを1台と手入力してみると，支払金額は時間当たり 10,100 円となりました。もっと安くすることはできるでしょうか？ここからが動画の内容で，ソルバーの登場です。

乗車人員数:「何人乗り × 台数」の数式を入力
金額:「時間当たり金額 × 台数」の数式を入力
乗車人員数の列と金額の列には，合計の関数を入力

Windows × 日本語

Windows × English

ソルバーは，与えられた条件での最適解を返してくれます。この場合，緑のセルで17人以上が乗車可能となることが条件で，黄色のセルの合計金額を最小にするための最適解を，赤の範囲で調整して求めてくれます。

動画ではつぎの手順を実施して最適解を得ています。最適解が返されて，支払金額は 7,500円になりました。Cを3台，AとBを1台ずつ借りると，最も安く済むことがわかりました。これでソルバーを使って問題を解決することができました。

リボンの「データ」からソルバーをクリック。「目的セルの設定」で黄色のセルを設定します。金額を最小にしたいので「目標値」を「最小値」にします。
「変数セルの変更」で，最適解を求めたい赤い範囲を設定します。
「制約条件の対象」の欄をクリックして「追加」ボタンを押します。セル参照を赤い範囲にし，それを整数（int）に指定して「追加」ボタンをクリック。
再び「追加」ボタンで条件を追加。セル参照を緑のセルにし，それを「17以上」に指定して「OK」→「解決」ボタンを押します。
ソルバーの結果が出たら「OK」をクリック。

9.2 Pythonによる最適化

9.2.1 ソルバー

ここでは，PuLP ライブラリを使って，上述のソルバー問題を Python を用いて実行する方法を紹介します。コード1 で必要な PuLP ライブラリをインストールして読み込みます。

コード 1

!pip install pulp
import pulp

下記コード2 はモデルを作成しています。# 車種 以下で，上の Excel ソルバーと同じ条件を与えています。

コード 2

# モデル /　Model
model = pulp.LpProblem('rent', sense = pulp.LpMinimize)
# 車種 /  Car models
car = ["a", "b", "c", "d", "e", "f", "g", "h", "i", "j"] 
# 人乗り /  Capacities
capacity = [2, 3, 4, 4, 6, 7, 8, 8, 8, 10]
# 金額 / Prices
price = [1200, 1500, 1600, 2400, 3000, 3300, 3800, 4600, 5000, 6800]

コード3 は，最適化したい（各車種からレンタルする）「台数」を xs として変数を定義します。

コード 3

# 変数の定義 / Define the variable
xs = [pulp.LpVariable("{}".format(x), cat = "Integer", lowBound = 0) for x in car]

コード4 でモデルを完成させます。

コード 4

# 目的関数 / Objective (function)
model += pulp.lpDot(price, xs)
# 制約条件 / Conditional constrains
model += pulp.lpDot(capacity, xs) >= 17

print(model)

コード5 でソルバーを起動すれば，上の Excel のソルバーで得たものと同じ解が返ることが確認できます。

コード 5

status = model.solve(pulp.PULP_CBC_CMD(msg = 0))
print("Status", pulp.LpStatus[status])
print([x.value() for x in xs])   
print(model.objective.value())

9.2.2 主成分分析の考え方

「最適化」そのものからは逸れますが，その考え方の実用的な応用例として，主成分分析による次元削減を Python で実行してみましょう。

いくつもの特徴量があるところから何かを見分けるときに，特徴量の数すべての n 次元の中から見分けようとするよりも，個体の違いを見分けやすい次元に削減したい場合があります。個体の違いを見分けやすくするために，分散を最大化する軸に次元を削減するのが主成分分析です。

たとえば書籍 p.114 図8.2の散布図では，ペンギンの種を見分けることは困難です。この散布図で3つのペンギンの種がどのような散らばりを見せていたのか，下記コード6 で種ごとに色を付けて見てみましょう。書籍 p.125 の図9.1 を表示します。

コード 6

import seaborn as sns
import matplotlib.pyplot as plt
import numpy as np
df = sns.load_dataset('penguins')

X = df['flipper_length_mm'].values
Y = df['body_mass_g'].values

sns.set_style("white")
color = ["#87CEEB", "#2E8B57", "#F4A460"]
sns.set_palette(color)
sns.scatterplot(data = df, x = X, y = Y, hue = "species")  
plt.xlabel("flipper length [mm]")
plt.ylabel("body mass [g]")

Figure 9.1 種ごとに色分けしたヒレの長さと体重の散布図

別の軸を加えて3次元にすると，種がもっと見分けやすくなります。それが下記コード7 で，書籍 p.125 の図9.2 を表示します。

コード 7

fig = plt.figure(figsize = (8, 6))
ax = fig.add_subplot(projection = "3d")

x = df["flipper_length_mm"].values
y = df["body_mass_g"].values
z = df["bill_depth_mm"].values

axes = plt.axes(projection = "3d") 

colors = np.where(df["species"] == "Adelie", "#87CEEB", "-")
colors[df["species"] == "Chinstrap"] = "#2E8B57"
colors[df["species"] == "Gentoo"] = "#F4A460"

ax.set_xlabel("flipper_length [mm]")
ax.set_ylabel('body_mass [g]')
ax.set_zlabel('bill_depth [mm]')

axes.scatter3D(x, y, z, c = colors)
ax.set_box_aspect(aspect = None, zoom = 0.8)

Figure 9.2 三次元散布図

いよいよ主成分分析に向けた準備です。主成分分析を行うためには欠損値を処理しておかなければならないので，コード8 で欠損値を削除し，その結果を確認しています。欠損値がなくなっているとともに，ペンギンの量的データの特徴量が4つあったことも再確認できたでしょうか。主成分分析では，この4次元を2次元に削減します。

コード 8

df3 = df.dropna()
df3.head()

コード9 では，df3 の3列目から6列目までの量的データのスケールを統一するために，標準化（平均を引いて標準偏差で割ること）したデータフレーム df4 を作成します。

コード 9

df4 = df3.iloc[:, 2:6].apply(lambda x:(x-x.mean())/x.std(), axis = 0)
df4.head()

下記コード10 で主成分分析を実行し，各個体の成分得点を feature に格納します。

コード 10

import sklearn
from sklearn.decomposition import PCA 
pca = PCA(n_components = 2) 
pca.fit(df4)
feature = pca.transform(df4)

下記コード11 では，成分得点をデータフレームにしたものを df5 にしています。すでに pandas ライブラリが読み込まれていることが想定されています。

コード 11

df5 = pd.DataFrame(feature, columns = ["PC{}".format(x + 1) for x in range(2)]) 
df5.head()

下記コード12 がつくるのは，2次元に削減した散布図（書籍 p.127 図9.4）です。同じ2次元でも図9.1に比べてどれほど種が見分けやすくなっているか，いわば答え合わせのために色付けをしています。色付けのために，種の列の残っている df3 を呼び出して各ペンギン種に色を与えています。

コード 12

colors = np.where(df3["species"] == "Adelie", "#87CEEB", "-")
colors[df3["species"] == "Chinstrap"] = "#2E8B57"
colors[df3["species"] == "Gentoo"] = "#F4A460"

plt.figure(figsize = (6, 6))
plt.scatter(feature[:, 0], feature[:, 1], alpha = 0.8, c = colors)  
plt.grid()
plt.xlabel("PC1")
plt.ylabel("PC2")
plt.show()

Figure 9.3 主成分分析による二次元散布図

この例では特にジェンツー種が見分けやすくなっていますが，同じような手法で，病変した組織や，人間には知られていなかった種類の違いを見分けることに役立てることができることが想像できたでしょうか。

Pythonコードを書いていくことを学ぶことはそれを目的としている別科目に譲るとして，ここでは，分散を最大にする主成分分析と，それが可能にする次元削減の可能性を理解してもらえればと思います。

About Website

お問合せ先