Skip to content

DNET_DataMiningExcel

Jump to bottom
nishi_74322014 edited this page Sep 11, 2026 · 1 revision

データマイニング（DM）- Excel

概要

Excel。専用ツールではないので機能としては少ない。

詳細

インストレーション

Excelのインストール

基本操作

初期化

Excelアドイン「ソルバー」を追加など。

データ読込

説明変数と目的変数の表をワークシートに読み込む。

CRISP-DM上で利用

CRISP-DMの各フェーズを Excel 上で行う場合。

データの理解

  • 説明変数と目的変数の表を用意し、
  • CRISP-DM のデータの理解（CRISP-DMの該当節を参照）を行う

データの準備

  • データのクリーニング
  • データの構築
  • データの統合

モデリング

評価

自身で評価値を計算する必要がある。

展開

  • Excelを配れば良い？
  • ただし、
    • カナリ手の込んだワークシートが必要。
    • ワークシートの使い方のマニュアルが必要。

手順（モデリング）

単回帰分析

単回帰分析については統計解析の該当節を参照。

  • まずデータから散布図を作成する。

  • 散布図中の1つのプロット上で右クリックしメニューを表示し、
    [近似曲線の追加]を選択し[近似曲線のオプション]で線形近似を選択、

  • 続いて、

    • [グラフに数式を表示する]と
    • [グラフにR-2乗値を表示する]に

    チェックを入れる。

  • 回帰式と決定係数（R2乗値）を表示できる。

  • 外れ値は、外れ値のプロットを選択し右クリックして、
    [データの吹き出しを追加]を選択しX, Y座標値を表示し、
    座標値を確認して、今回の当該値を元データから削除する。

重回帰分析

重回帰分析については統計解析の該当節を参照。

  • [データ分析]から[回帰分析]を選択
  • [入力Y範囲]と[入力X範囲]を指定して[OK]ボタンを押下。

決定木分析

決定木分析については統計解析の該当節を参照。

  • はじめに属性毎に結果をクロス集計する。

    • 実測値の表
    • 列総計列を持つ
    • 行総計行を持つ
    • データの総計セルを持つ

  • 上記のクロス集計表を変換する。

    • 期待度数の表（統計解析の該当節を参照）
    • カイ二乗（X^2）値の表（統計解析の該当節を参照）

  • 自由度を算出する。
    = (COUNTA(行要素名の範囲) - 1) * (COUNTA(列要素名の範囲) - 1)

  • P値（有意確率）（統計解析の該当節を参照）を算出する。
    以下のいずれかの方法で算出可能。

    • = CHITEST(実測値の範囲, 期待度数値の範囲)
    • = CHIDIST(SUM(X^2値の範囲), 自由度)

  • P値が小さい（≒ X^2値の大きい）属性で分岐させ、
    「分岐後のデータ群」に対し別々に上記の処理を繰り返し、木を成長させて行く。

ロジスティック回帰分析

ロジスティック回帰分析については統計解析の該当節を参照。

※ 情報量については機械学習（machine learning）の該当節を参照。

  • 事前に表を組んでおく（列追加）。

    • 定数項　　：オール１に設定しておく。

    • 推定値　　：ロジスティック関数 p(z)
      = 1 / (1 + 𝑒^−𝑧)
      = 1 / (1 + EXP(-SUMPRODUCT(偏回帰係数の範囲, 説明変数の範囲)))

    • 対数尤度　：
      = (結果 * LN(推定値)) + ((1 - 結果) * LN(1 - 推定値))

  • 表外の項目に関しては以下。

    • 逸脱度：対数尤度の合計 * -2
    • （変数個の）偏回帰係数（統計解析の該当節を参照）：初期値は０に設定しておく。

  • [データ分析]から[ソルバー]を選択

    • 目的セル

      • 「逸脱度」を選択する
      • 目標の値を最小値にする（ような「偏回帰係数」を求める）。

    • 変化させるセルに「偏回帰係数」の範囲を選択する

    • [解決]をクリックする

主成分分析

主成分分析については統計解析の該当節を参照。

  • 事前に表を組んでおく（列追加）。

    • 平均との差　：= 標本の項目毎、平均値との差を算出。
    • 平均との距離：= (平均との差1^2 + 平均との差2^2 + ...)^0.5
    • 主成分得点　：= SUMPRODUCT(固有ベクトルの範囲, 平均との差の範囲) / 固有ベクトルの和
    • 実測値から新しい軸への垂線長の2乗（≒ 失われる情報量）：= 平均との距離^2 - 主成分得点^2

  • 表外の項目に関しては以下。

    • 失われる情報量の合計値　：= SUM(失われる情報量の範囲)
    • （変数個の）分散　　　　：= VARP(標本の項目毎の範囲)
    • 主成分得点の分散　　　　：= VARP(主成分得点の範囲)
    • （変数個の）固有ベクトル：初期値は１に設定しておく。
    • 固有ベクトルの和　　　　：= (固有ベクトル1^2 + 固有ベクトル2^2 + ...)^0.5
    • 固有ベクトルの和の目標値：１
    • 累積寄与率　　　　　　　：= 主成分得点の分散 / SUM(（変数個の）分散)

    ※ 分散については統計解析の該当節を参照。

  • [データ分析]から[ソルバー]を選択

    • 目的セル
      • 「失われる情報量の合計値」を選択する
      • 目標の値を最小値にする（ような「固有ベクトル」を求める）。
    • 変化させるセルに「固有ベクトル」の範囲を選択する
    • 制約条件の対象に「固有ベクトルの和 = 固有ベクトルの和の目標値」を指定。
    • [解決]をクリックする

クラスタ分析

クラスタ分析については統計解析の該当節を参照。

  • 手順
    ① データを準備する。
    ② クラスタ数を指定する。
    ③ クラスタ初期値をランダムに計算する。
    ④ 各標本に計算したクラスタ値を指定する。
    ⑤ 各クラスタの中心を算出する。
    ⑥ 各標本と中心からの距離を算出する。
    ⑦ 各標本を最も近いクラスタに再分類する。
    ⑧ 再分類で変化のあった標本数を集計する。
    ⑨ 変化が収束するまで④～⑧を繰り返す。

  • 数式の例（k平均法の場合）

    • 重心　の計算：=AVERAGEIF(グループ値の範囲, 当該グループ番号, データの範囲)
    • 距離　の計算：=SUMXMY2(データの範囲, 重心値の範囲)
    • 再分類の計算：=MATCH(MIN(距離値の範囲), 距離値の範囲, 0)
      ※ 0は等しい最初の値を検索

  • 最適なグループ数はエルボー法などで求める。

    • クラスタ内距離二乗和：計算した距離の二乗した値をクラスタ毎に合計
    • 上記を折れ線グラフにしたとき、肘の部分が最適なクラスタ数になる。

移行メモ

  • 自由度の式 = (COUNTA(行要素名の範囲) - 1) * ((COUNTA(列要素名の範囲) - 1) と = CHIDIST (SUM(X^2値の範囲), 自由度)) は括弧の対応が崩れていたため整えた。
  • クラスタ分析の手順「⑨ 変化が収束するまで④～⑨を繰り返す」は、 ⑨ 自体が繰り返しの指示であるため「④～⑧を繰り返す」に修正した。
  • 「目標の値を最小値にする（ような「偏回帰係数」を求める。」など、 閉じ括弧の欠落を補った。
  • 本文の無い見出し「基本操作」「データ読込」（元は「...」のみ）には、 前後の文脈から分かる範囲の説明を補った。
  • 元 Wiki 末尾の「参考」は見出しのみで内容が無かったため除去した。
  • PukiWiki のページ内アンカ（#xxxxxxxx）は GitHub Wiki では再現できないため、 他ページのアンカを指すリンクは「〜（ページ名 の該当節を参照）」の形に置き換えた。 同一ページ内のアンカは見出しから生成されるアンカに張り替えた。

Tags: 移行, データマイニング, Excel, ソルバー, 回帰分析, 決定木, 主成分分析, クラスタ分析, CRISP-DM

このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。

NetDevInfraWiki

マイクロソフト系技術情報 Wiki
Open 棟梁 Wiki

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally