BellCurve 統計WEB

ブログ

エクセル統計によるコンジョイント分析

2018/12/20

カテゴリ:

「エクセル統計でコンジョイント分析はできますか?」というお問い合わせをいただくことがあります。

残念ながら、2018年現在、エクセル統計には「コンジョイント分析」というメニューはありません。しかし、最もベーシックな方法で行うコンジョイント分析は、エクセル統計の機能を組み合わせることで実行できます。

以下では、エクセル統計でコンジョイント分析を行う方法を解説します。

目次

  1. 「直交配列表の作成」によるプロファイル作成
  2. 「数量化1類」による効用値の算出
  3. 補足1. Rパッケージ「conjoint」との比較
  4. 補足2. 「ダミー変数への変換」によるダミー変数化
  5. 補足3. 「重回帰分析」による効用値の算出

1. 「直交配列表の作成」によるプロファイル作成

ある食品について、次のような属性と水準の商品の組み合わせを考え、被験者に回答してもらいます。回答結果から、それぞれの水準がどのような効用をもつかを調べます。

属性と水準の表

まず、プロファイルを作成します。この表のラベル行を選択し、メニューより[エクセル統計]→[分散分析・多重比較]→[直交配列表の作成]を選択します。

エクセル統計の「直交配列表の作成」ダイアログ

ダイアログが表示されたら、[OK]をクリックすると直交配列表が出力されます。

出力された直交配列表

この出力の各行が、一つずつのプロファイルになります。例えば、1番目の製品は「辛く、透明」な属性を持っています。このようにして作成したプロファイルを、被験者に提示して点数を付けてもらいます。

2. 「数量化1類」による効用値の算出

先ほど作成した6パターンのプロファイルについて、3名の被験者に10点満点で点数を付けてもらいました。数量化1塁で分析するために3名の評価を縦に積み上げています。

各プロファイルの評価を縦に積み上げた表(一部抜粋)

このデータから、属性ごとの効用値を数量化1類により計算します。ラベル行をドラッグして選択し、メニューより[エクセル統計]→[多変量解析]→[数量化1類]を選択します。

「数量化1塁」ダイアログ[変数]タブ

ダイアログが表示されたら、[OK]をクリックし分析を実行します。出力結果の「カテゴリースコア」が、求める効用値です。

出力されたカテゴリースコア

味の中では「甘い > 普通 > 辛い」の順に効用が高く、色の中では「透明 > にごり」の順に効用が高いことがわかります。甘く、透明な製品を作れば、人気が高くなるかもしれません。

補足1. Rパッケージ「conjoint」との比較

統計解析ソフトRでは、「conjoint」というパッケージでコンジョイント分析を行うことができます。このパッケージ内の「tea」というデータは、次の属性と水準を持った13のプロファイルを100人に採点してもらった結果となっています。

属性と水準の表

Rでコンジョイント分析を行った結果は次の通りです。

Rの結果(各水準の効用値)

この「tea」というデータを、先ほどのようにエクセル統計の数量化1類で分析し、結果を比較してみましょう。データをエクセル統計で分析できる状態に整形すると次のようになります。

数量化1類で分析するためのデータ(一部抜粋)

これを数量化1類で分析した結果の効用値(カテゴリースコア)は次のようになります。

エクセル統計の結果(各水準のカテゴリースコア)

Rの結果と比較すると、結果が一致していません。これは、Rではダミー変数化した重回帰分析で計算しており、それが数量化1類の計算とは異なるためです。

ここで、数量化による結果とRによる結果を属性ごとに比較してみます。各属性の効用値のグラフは次の通りです。

「price」の各水準の効用値の横棒グラフ 「vriety」の各水準の効用値の横棒グラフ
「kind」の各水準の効用値の横棒グラフ 「aroma」の各水準の効用値の横棒グラフ

よく見ると、エクセル統計の結果は、Rの結果に対して一定の数値でずれています。例えば「price」の場合、エクセル統計の結果はRの結果よりもどの水準も約 0.0369 大きくなっています。これは偶然ではなく、両手法の性質の違いによるものです。

補足2. 「ダミー変数への変換」によるダミー変数化

では、エクセル統計の重回帰分析でRと同じ結果を求める方法について解説します。

まず、元のデータの属性をダミー変数化します。「price」から「aroma」までのラベル行をドラッグして選択し、メニューより[エクセル統計]→[ユーティリティ]→[ダミー変数への変換]を選択します。

ダイアログが表示されたら、[最後のカテゴリーをダミー変数化しない]のチェックをオフにして、[OK]を選択して実行します。

エクセル統計の「ダミー変数への変換」ダイアログ

ダミー変数化した結果が出力されました。

ダミー変数への変換の出力結果

次に、ダミー変数化したデータを変形します。以下の手順で、ダミー変数化しなかったカテゴリーに該当する行の「0」を「-1」に置き換えます。

まず、ラベル行を選択し、[データ]タブ→[フィルター]をクリックします。

ラベルの行にフィルターを設置した状態

「price」の水準の中で名前順で最後に位置している「price:medium」のセルをクリックし、「昇順」を選択してソートします。

「price:middle」の列で昇順にソートした結果

画面をスクロールして「price:medium」の値が「1」の行を表示します。

「price:medium」の値が「1」の行の表示

「price:medium」の値が「1」の行について、その行の同じ属性内の他のすべての水準で「0」を「-1」に書き換えます。この場合は、「price:high」と「price:low」の列の「0」を「-1」に書き換えます。わかりやすくするため、書き換えた部分を赤字にしています。

「price:high」と「price:low」の「0」を「-1」にする

オートフィルなどを用いて、他の行も書き換えます。書き換えが終わると、次のようになります。

対象のすべての行で「price:high」と「price:low」の「0」を「-1」にする

この手順を、「variety」、「kind」、「aroma」の変数に対しても行います。

書き換えが終わったら、「No.」で昇順にソートし、フィルターを解除します。ここまで行うと、データは次のようになります。

各属性で最後のカテゴリーに該当する行で「0」を「-1」に書き換えた状態

次に、評価値「Y」をコピーして貼り付けます。このデータでは、一番左の列に挿入しています。

ダミー変数のデータに評価値を付け加えた状態

以上でダミー変数の変換は終了です。

補足3. 「重回帰分析」による効用値の算出

重回帰分析で効用値を計算しましょう。「Y」のラベルをクリックし、[Ctrl]キーを押したまま「price:high」から「aroma:yes」までの全てのダミー変数のラベル行ををドラッグして選択し、メニューより[エクセル統計]→[多変量解析]→[重回帰分析]を選択します。

ダイアログが表示されたら、「説明変数」のリストから「price:medium」、「variety:red」、「kind:leafy」、「aroma:yes」の変数を説明変数から除きます。各属性の最後のカテゴリーのダミー変数を分析に用いないということです。

エクセル統計の「重回帰分析」ダイアログの[変数]タブ

「OK」をクリックし、分析を実行します。

効用値は偏回帰係数として推定されます。「回帰式に含まれる変数(偏回帰係数・信頼区間等)」の結果に出力されます。

出力結果の「回帰式に含まれる変数(偏回帰係数・信頼区間等)」

この出力結果には、「price:medium」、「variety:red」、「kind:leafy」、「aroma:yes」の偏回帰係数(=効用値)がありません。しかし、先ほど行ったダミー変数化の作業により、「属性内の水準の効用の総和が0である」と言えます。つまり、属性内で他の水準の効用値がわかれば、残りの1つの水準の効用値も求めることができます。

例えば、「price」について言えば、、「price:medium」=-(「price:high」+「price:low」)となります。それでは、「price:medium」、「variety:red」、「kind:leafy」、「aroma:yes」の効用値を求めましょう。

まず、「variety:black」、「kind:bags」、「aroma:no」、「定数項」の上に空白行を挿入し、それぞれ「price:medium」、「variety:red」、「kind:leafy」、「aroma:yes」と入力します。

出力結果に空白行を挿入した状態

次に、「price:medium」の右のセルに「=-SUM(B75:B76)」と入力します。この値が、「price:medium」の効用値となります。

同様に、「variety:red」の右のセルに「=-SUM(B78:B79)」、「kind:leafy」の右のセルに「=-SUM(B81:B82)」、「aroma:yes」の右のセルに「=-SUM(B84)」と入力します。これで、すべての水準の効用値を求めることができました。

各属性の最後のカテゴリーの効用値を数式で求めた結果

実際に結果が一致していることを確かめてみましょう。見やすくするため、順番を並べ替えています。

エクセル統計での結果

エクセル統計での結果

Rでの結果

水準効用値
low0.2402
medium-0.1431
high-0.0971
black0.6149
green0.0349
red-0.6498
bags0.1369
granulated-0.8898
leafy7529
yes0.4108
no-0.4108

このようにして、エクセル統計でもコンジョイント分析ができました。


統計学やデータ分析を学ぶなら、大人のための統計教室 和(なごみ) [業務提携]


統計WEBを運営するBellCurveは、統計解析ソフト「エクセル統計」を開発・販売しています! 統計解析ソフト「エクセル統計」をインストール後のExcel上のタブとメニュー エクセル統計ジャケット画像


【BellCurve監修】統計検定®2級対策に最適な模擬問題集1~3を各500円(税込)にて販売中!

Kindleストアで配信中

統計検定®2級 模擬問題集1

500円(税込)

統計検定®2級 模擬問題集2

500円(税込)

統計検定®2級 模擬問題集3

500円(税込)


【PR】