BellCurve 統計WEB

【BellCurveシリーズ】 統計解析ソフト「エクセル統計」をAmazonにて好評発売中!

エクセル統計ジャケット画像 5%ポイント還元

■エクセル統計が選ばれている3つの理由

  • 1. Excel上のデータをそのまま分析できる
  • 2. オール・イン・ワンだから買い足し不要
  • 3. 使いやすさとコストパフォーマンスがNo.1

ブログ

エクセル統計でコンジョイント分析

2018/12/20

カテゴリ:

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

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

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

分析の目的

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

項目

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

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

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

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

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

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

3名の被験者に、先程作成した6パターン全てプロファイルに10点満点で点数を付けてもらった結果が次のようになりました。

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

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

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

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

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

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

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

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

よく見ると結果が一致していません。 これは計算方法の違いが原因で、Rではダミー変数化した重回帰分析で計算しており、それが数量化1類の計算とは異なることが理由です。

では、この結果は全く信用できない程異なる結果となっているのでしょうか。 数量化による分析結果と、Rによる結果を属性ごとに比較しましょう。 4属性の効用のグラフは次の通りです。

よく見ると、重回帰で計算した効用と、数量化1類で計算した効用には、数値のズレはあるが、大小関係や差の大きさについては、全く一緒であることが分かります。

例えば、aromaのyesとnoの差は重回帰の結果を用いると0.4108-(-0.4108)=0.8216ですが、数量化による結果では0.5056-(-0.3160)=0.8216となり、確かに差は一致していることが分かります。 これは偶然ではなく、両手法の性質によるものです。 ただし、異なる属性間での比較においては、この法則が成立するとは限りません。 この点には注意が必要です。

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

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

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

ダイアログが表示されたら、「最後のカテゴリをダミー変数化しない」にチェックを入れ、「OK」を押して処理を実行します。

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

次に、ダミー変数化したデータを変形します。この変形は少し難しいですが、非常に重要です。 ラベル行を選択し、[データ]タブ→[フィルター]をクリックします。

「price:medium」のセルをクリックし、「昇順」を選択してソートします。

「price:medium」の値が「1」になっているセルを画面を下にドラッグして見つけます。

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

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

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

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

次に、評価値「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つ以外の水準が分かれば、最後の1つも求めることができます。 具体的には、出力されている「price:high」と「price:low」の効用を計算することにより、「price:medium」の効用を求めることができる、ということです。

それでは、「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)」と入力します。 これで、全ての効用値を求めることができました。

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

水準効用
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

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

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


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

Kindleストアで配信中