• ベストアンサー
  • すぐに回答を!

EXCELクロス集計(双方向が複数選択項目)

(先日質問を出したのですが回答が付かないので改めて) アンケート集計の結果をEXCELに落とし込んであります。 例: 質問A:好きな食べ物は?(次から選択)⇒ケーキ、アイスクリーム、チョコレート 質問B:好きな観光地は?(次から選択)⇒京都、鎌倉、上高地 回答は複数選択可能です。 回答者1:ケーキ/京都、鎌倉 回答者2:アイスクリーム、チョコレート/上高地 回答者3:鎌倉 ・・・ 回答者n:チョコレート/京都、上高地 この様なデータ結果に対して、 「ケーキ」選択者の嗜好する観光地は? 「アイスクリーム」選択者の嗜好する観光地は? 「チョコレート」選択者の嗜好する観光地は? という集計を行いたいです。 ピボットテーブルでは集計が「1対多」の場合は可能ですが、このように「多対多」の場合はどのようにしたら効率よくできるでしょうか?

共感・応援の気持ちを伝えよう!

  • 回答数4
  • 閲覧数1742
  • ありがとう数4

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.4
  • keithin
  • ベストアンサー率66% (5278/7939)

>関数を駆使して自在に結果を出すのは難しい あなたがご自分の手を動かすのをメンドクサがらないなら、実際にやってることは ケーキの京都: =CORREL($B2:$B8,E2:E8) と記入して右にコピー アイスの京都: =CORREL($C2:$C8,E2:E8) と記入して右にコピー チョコの京都: =CORREL($D2:$D8,E2:E8) と記入して右にコピー のようにするだけです。 実際には「たくさんのデータ処理をしたい」というお話だったようですし、こちらも説明のためにこれだけ数式をいちいち説明するのは大変なので、一括で計算できるよう余計な関数を組み合わせました。ムズカシく感じたなら、説明が悪かったですね。ごめんなさい。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

補足のご説明ありがとうございます。難しく感じたのはOFFSETやMATCH等の関数に慣れていないという私のレベルが原因ですが、より便利な方法を教えていただきまして感謝しております。

関連するQ&A

  • Excelのピボットテーブルで複数選択項目集計

    (Excel2010です) ピボットテーブルを使い、次の様なアンケート集計を行いたいです。 行ラベル:業種 列ラベル:アンケートの選択項目 ⇒業種/アンケート選択項目 の各組み合わせの数を集計する。 行ラベルの方は問題なく配列できていますが、列ラベルが1列=1項目のようにきれいに配列されません。 元のSheetの状態は、行(アンケート回答者)には業種属性があり、列にはアンケート項目を配列してあり、回答者が該当するものについてはセルに"1"を入力しています。 業種毎に、アンケート項目で選択した数を集計したいです。

  • Accessのクロス集計について

    お願いします アクセスで次のようなアンケートCSVをインポートして集計しています。質問1・2に入力されるのは選択肢番号です <アンケート結果テーブル> 名前   回答者 質問1  ========================== 20080710 Aさん   1   20080710 Bさん   2    20080710 Cさん   5    上記をクロス集計を行い日別の集計を行っております。 <クロス集計> 日付   1 2 5 =========================== 20080710 1 1 1  上記の結果を回答がなかった選択番号は下記のように0を表示させたいのですがどうすればよろしいでしょうか? 日付   1 2 3 4 5 =========================== 20080710 1 1 0 0 1 

  • 飛騨高山&上高地

    4月下旬に飛騨高山と上高地に2泊3日で行く予定です。 夫婦&5歳の子連れです。 観光地と日程で悩んでいます。 確定していることは、 1日目 電車で高山着13:00→高山でレンタカーを借りて上高地へ向かう→上高地温泉ホテル泊 2日目 ホテル発→レンタカーで高山へ戻り→高山観光ホテル泊 3日目 ホテル発→高山発13時過ぎの電車で帰ります 最低限行きたい観光地は、新穂高ロープウェイ、上高地散策(場所は未定)、飛騨の里、飛騨高山まつりの森、朝市(宮川、陣屋前のどちらか未定)、夢工場飛騨などです。 所要時間などがわからないので、観光地の行程をどのようにしたら良いか教えてください。 他にもお勧めがあれば教えていただけるとありがたいです。 ロープウェイや上高地の散策を1日目にするか2日目にするか、2日目の高山では、レンタカーで終日移動するのが良いか、先にレンタカーを返して「さるばばバス」を利用したほうが便利なのか?そのあたりも併せて教えてください。 また、上高地では4月下旬でもダウンジャケット並みの服装は必要ですか?高山ではどうですか? 長くなりましたが、よろしくお願いいたします。

その他の回答 (3)

  • 回答No.3
  • keithin
  • ベストアンサー率66% (5278/7939)

添付図: 好きな欄に1を、1でない欄にはゼロを記入する B12: =CORREL(OFFSET($A$2:$A$8,0,MATCH($A12,$B$1:$G$1,0)),OFFSET($A$2:$A$8,0,MATCH(B$11,$B$1:$G$1,0))) 右にコピー、下にコピー。 解説: 相関係数(CORREL)は、2つのデータの傾向が「似ている」か「逆である」かを計算します。 数値が1に最も近い「チョコ-上高地」の組み合わせが、1とゼロの並びが一番よく似ている(共通している=チョコが好きな人は上高地も好き)という事を示します 結果が-1に最も近い「ケーキ-上高地」の組み合わせが、1とゼロの並びが最も逆になっている(反対の傾向にある=ケーキが好きな人は上高地が嫌い)という事を示します。 結果が1にも-1にも近くない組み合わせは、好きとも嫌いとも言えない(相関関係が認められない)ことを示します。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ありがとうございます! Correl関数、素晴らしいですね。使えそうです。 ところで、中でOFFSETとMATCHを使っており、これを理解するのに時間が掛かりました。 言葉で整理しますと、ケーキ×京都の場合、 CORREL関数の引数として、 配列1:元データからケーキ列全体データを取得する 配列2:元データから京都列全体データを取得する を行っているのですね。 このように関数を駆使して自在に結果を出すのは難しいですね・・・

  • 回答No.2
  • MackyNo1
  • ベストアンサー率53% (1521/2850)

どのような方法を利用するにしろ、最終的にどのようなリストを作成したいのかを説明しないと具体的な回答は得られないと思います。 そもそも、ピボットテーブルを含めて、二次元のリストではすべての項目を同時には分析できないので、3次元目の項目はページフィールドに配置するなどして、そこから分析項目を絞り込んだリストを作成することになります。 まず、基本としてお尋ねしますが、現在Excel上にどのような集計データがあるのでしょうか? >回答者1:ケーキ/京都、鎌倉 >回答者2:アイスクリーム、チョコレート/上高地 >回答者3:鎌倉 上記の例示は1つのセルにデータが入力されているのでしょうか? ピボットテーブルや関数を使用する場合は、それぞれのデータを質問系列ごとにあるいは回答項目ごとに列を作成して入力する必要があります。 >この様なデータ結果に対して、 「ケーキ」選択者の嗜好する観光地は? 「アイスクリーム」選択者の嗜好する観光地は? 「チョコレート」選択者の嗜好する観光地は? という集計を行いたいです。 この部分だけなら観光地と嗜好品のクロス集計を基本とした分析を行うことになります。 これ以外の集計項目があるなら、ピボットテーブルを使用する場合は、これらをページフィールドに配置するのが最も簡便な対応です。 質問には「集計が「1対多」の場合は可能ですが」とピボットテーブルで解析できるような説明がされていますが、現在のデータベースで本当にピボットテーブルが作成できているのでしょうか? #最終的にどのような集計リストが作成したいのか不明なので、具体的に回答しにくいのですが、ピボットテーブルや関数を使用して集計リストを作成する場合は、それに対応した元データの形式が必要になりますので(おそらく現在のデータベースでは集計できないので)、改善策を含めた具体的な回答が得たい場合は、どのような解析をしたいのか最終目的を整理して(解析の切り口を明示して)補足質問するようにしてください。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ありがとうございます! 現在考えついている集計結果は、先の回答No.1で回答いただいた添付画像の下の表の形です。元データも上の表のように、既に1つのSheetに落としています。 最後に各観光地の比率を%で表示すれば傾向が表現できるのではと思っています。 ケーキ好物の人が嗜好する観光地:京都60%、鎌倉20%、上高地10% というかたちです。ひとまず現在の実現可能です。 最終目的は、現在のデータを用いて、2つの項目の相関関係を効果的に表現することです。表からグラフ作成も想定しています。効果的、つまり資料を見た人がより直感的にわかるような表現方法が他にあればそれを採用したいと思っております。 なお、「1対多」のケースは、男・女、年齢区分(10代、20代、30代…)のように一つのみ選択される項目と複数選択可能な項目をクロス集計することです。このようなケースではピボットテーブル作成の1回操作で期待通りの結果が得られております。 ページフィールドの機能、調べてみました。今のところはしなくてもよさそうですが勉強になりました。ありがとうございます。

  • 回答No.1
  • nishi6
  • ベストアンサー率67% (869/1280)

ピボットテーブルは便利なんですが、実務では適用できるケースは少ないように思います。更新が必要だったりして気を付ける点もあります。 ピボットテーブルが使えそうもない場合は、大体Sumproduct関数を使っています。うまく作れば更新不要で大体のことはできます。(当方、Excel2010です) この質問の場合、添付図の上のような集計表を想定してみました。算式を簡単にするためにセル範囲名を付けます。 セルB1:G8を選択して、数式タブ>選択範囲から作成>上端行>Ok これで、データの縦列に表題の範囲名が付きます。 添付図の下で、セルB12に =SUMPRODUCT((INDIRECT($A12)=1)*(INDIRECT(B$11)=1)) とします。後は上下にコピーします。添付図の下では表頭、表側が算式に組み込まれています。 実際、項目数はこれよりも多いと思いますが、範囲名の設定などは簡単でしょう。 また、算式を工夫すれば、複数選択している人など、大体集計できます。算式を工夫してみてください。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ありがとうございます。 Sumproduct、INDIRECT関数、「セル範囲名」も勉強になりました。今までピボットテーブルばかりに頼ってきましたが中でどのように計算しているかは意識してなかったですが、Sumproductを理解することで仕組みが分かってきました。 ところでピボットテーブルで、ケーキ、アイスリーム、チョコレートで1行ずつ観光地数を集計して、切り貼りしても同じ表ができることが分かりました。 引き続き勉強していきます。

関連するQ&A

  • 意外と知らない『わが街の有名・無名』!!

    私の住む村は、お蕎麦が美味しい店が結構あり、 お蕎麦屋さんが連なる通りもあるのですが、この村に 越してくるまでその事実を知らなかったので、大して有名ではないと思っていました。 (せいぜい県内で知れてる程度) しかし、県外に行った時、『○○村と言う所に住んでいる』 と言うと、『あ~、お蕎麦の有名なあの村ですか』とか 『○○蕎麦有名だよねー』とか言われます。 蕎麦通には、かなり全国区らしかったのです。 また、隣の市には、『上高地』という全国でも有数の観光地があるのですが、 当然、全国的に知られてると思っていたら、隣県の友人が 『上高地?何それ?知らない!』と言いました。 あなたの住む地域の『実は凄く有名だった事』と 『実は意外と知られていなかったこと』を教えて下さい。 どちらか一方の回答でもOKです。

  • 複数回答×複数回答のクロス集計について

    複数回答×複数回答のクロス集計を行いたく、質問番号:1198621で回答者様が提案されているEXCEL関数「SUMPRODUCT」「INDIRECT」を用いた方法を行ったのですが上手くいきません。 どうかお知恵をお貸しください。 エクセルの作業画面を添付します。 問題点 「問A-1×問B-1」や「問A-2×問B-2」というように同じ選択番号の組み合わせは数値が出るのですが「問A-1×問B-2」といったものは♯VALUEと表示されます。 数式は「=SUMPRODUCT(INDIRECT(K$1&"2:"&K$1&"50"),INDIRECT($J2&"2:"&$J2&"50"))」 となっています

  • 上高地,白川郷,金沢について!。

    こんにちは! いま9月中に上高地、白川郷、金沢観光を考えてる者です。 そこで質問なのですが! 1.松本~大正池、高山~白川郷~金沢までバスで行くのですが道中乗り物酔いとかしますか? 普段はあまり酔わないのですが少し心配でして不安なら薬持ってこうか悩んでます。 2.大正池~上高地まで歩いてまわろうと思っていて、あまり箇所箇所で立ち止まらずに歩いたら何時間くらいで上高地着きますか? また服装は上着に長袖一枚あれば大丈夫でしょうか? 3.白川郷に一時間半はきつきつですか? もし取るなら何時間くらい必要でしょうか? 4.金沢市内もかるく散策する予定なのですが時間が半日程度と限られてるので、その範囲内で行ける観光地をご存知でしたら教えてください!。 アバウトな質問だらけで申し訳ないですがよろしくお願いします。

  • 飛騨高山と上高地

    先日東北旅行について質問しましたが、東北は冬にして、夏休みは上高地へ行こうということになりました。 先日ご回答いただいた件はその時まで持ち越します。ありがとうございました。 改めて上高地・飛騨高山について質問させてください。 東京から1泊旅行です。 高山駅前のホテルに泊まり、1日目は飛騨高山観光で2日目に新幹線の時間まで観光しようと思っています。 希望は上高地・馬籠・妻籠・白川郷のうち2ヶ所ぐらい行きたいです。 新幹線は夜発にするつもりです。 高山から上高地+その他の観光をすることは可能でしょうか? 調べてみたのですが、移動手段や所要時間が良くわかりません。 どうしたら効率よく回れるでしょうか? 宜しくお願いします。

  • 長野県の観光に詳しい方教えて下さい!!

    神奈川県から長野に行きます。1泊2日しかありません。 行きたい希望地 (1)河童橋(上高地) (2)奥飛騨にある「クマさんのテーマパーク」 (3)飛騨にある「大鍾乳洞」 (4)松本城 (5)善光寺 上記のコースを1泊2日で神奈川から車で回るにはちょっと厳しいでしょうか? イメージとしては朝6時半に出て、昼前に上高地に着いて2時間ほどいたあと奥飛騨と大鍾乳洞に行き、 松本市内かその近くにホテルをとっといて2日目に松本城と善光寺を考えています。 時間をもてあましたら他にどこかありますでしょうか。ホテルのオススメも教えてほしいです。 低予算ですが・・・。 長野県に行こうということだけは決めたのですが、なかなかしっくりくる観光地が無く とりあえずという感じで決めました。 伊豆にあるようなアニマルキングダムや若者向けのスポット観光地は長野県には無いのでしょうか? オススメのコースがありましたら教えて下さい。 また上記の観光地を巡る場合、1日目に上高地などにいくなら泊まるのは夜遅くなっても松本市内がいいですよね?

  • 10月中旬 名古屋から一泊二日でおすすめの旅行先

    タイトルどおりなのですが・・ 10月に、名古屋に遊びに行って、名古屋を起点に 一泊二日で旅行に行きたいです。 私は四国在住者なので、あちらの観光地が よく分からないのですが、いい旅先があれば 教えてください。 旅行会社のバスツアーを利用したいので、 バス(車)でいける距離内でお願いします。 自分で調べた範囲では、上高地があったのですが、 10月の上高地って、気候的に厳しい気がして・・ もし、オススメの観光地などありましたら、 教えてください。 よろしくお願いいたします。

  • 富山から長野への観光地は?

    熊本から富山まできて、立山黒部や長野県の上高地など写真がきれいに撮れるところで、観光地を巡って、2泊3日ぐらいの旅を計画中です。どのル-トが良いか又宿泊施設で良いところがあったら教えてください

  • 6.7月 上高地、松本、信州 プラン 大阪から

    6月か7月に3日間で旅行したいと思ってます。木曜夜、大阪発、日曜着。実質3日間。上高地着のバスがあるみたいなので。 希望 1日目 上高地 2日目 松本または霧が峰、蓼科などの高原 3日目 高原、湖等 基本的に景色のいいとこを見に行きたいと思ってます。松本は見るとこがありそうなんで行く価値ありなら2日目に行きたいです。 帰りは鉄道などを考えています。 宿泊は1日目が上高地か次の観光地と思ってます。 規制などもいろいろあるのでどうやってまわったらいいか?効率的、またはぜひいったほうがいいおすすめがあったらおしえてください。 プランの組み方教えてください。 また帰りは何で帰るのが便利でしょうか?

  • 愛知から上高地、安曇野までのプランを立てたのですが・・・

    8月12日~14日にかけて、愛知県(知多地域)から上高地、安曇野に観光に行きます。 他の上高地関連の質問&回答を参考にプランを立ててみたのですが、初めて行く場所なので、本当にこのプランで大丈夫か心配です。 上高地、または上高地までの交通情報にお詳しい方がいましたら、アドバイス等いただけないでしょうか。 ちなみに、安曇野のプランは他で質問し良回答を得ましたので、割愛させていただきます。 プラン 第一日目(8月12日) ■午前1:30愛知出発(自動車で行きます)…■午前6:00頃岐阜県平湯温泉郷アカンダナ駐車場到着(車を停めます)… 無料バスか徒歩でバスターミナルへ…■午前6:30頃上高地行シャトルバスに乗り出発…■午前7:00頃上高地大正池バス停下車… 大正池~自然研究路(林間コースか梓川コース)■午前8:30頃上高地観光センター:地図や昼ご飯入手… ■上高地郵便局:家族にハガキを出す…■河童橋は渡らずに、梓川左岸コースで明神(明神池には行かずに)~徳沢~ 午後4:00明神に戻り宿(明神館)にチェックイン (※愛知から平湯までの渋滞の可能性、平湯でスムーズに駐車出来るか、早朝からシャトルバスは混んでいるのか、 上高地での散策コースに無理が無いか、もしくは時間が余りすぎないか、が一日目の心配な要素です。 朝ご飯・昼ご飯は平湯までの道中や、観光センターで買って自然の中で摂ろうと考えています。晩ご飯は宿で出ます。) 第二日目(8月13日) ■早朝:明神池(朝もやが見たいです)…■遅くても午前8:00までに宿出発~梓川右岸コース~河童橋~5千尺5HORUN:お茶、ケーキ! …■午前中に上高地バスターミナル出発…■平湯:自動車に乗り換える…安房トンネル~沢渡経由して松本方面へ …■出来れば午後2:00までに安曇野到着 (※二日目は明神池の朝もやと宿で出される朝食のタイミングに合わせるので特に細かい時間設定はしていません。 遅くても正午までには上高地を出発し、午後2時には安曇野に到着出来れば、と考えています。 やはり、この時間帯だとバスは既に混んでいるのでしょうか・・・また、平湯~安房トンネル~沢渡は 渋滞がすごいことになっているのでしょうか。覚悟はしていますが、心の準備をして置きたくて・・・) ご回答よろしくお願いいたします。

  • エクセルピボットテーブルで、該当はないが項目を出してゼロという表示にしたい

    初めて質問させて頂きます。よろしくお願いします。 今アンケートの集計をピボットで集計していました。しかし、アンケートの回答選択肢が「1、○○ 2、△△、3、×× 4、■■」とあっても実際には2と3しか回答されていない質問項目ができてしまいました。 ピボットテーブルでグラフを作る際に、該当ゼロでもグラフ上で選択肢項目は作りたいのですが、どうすればいいのでしょうか 回答者番号 問1 問2 1        2  2 2        3  4 3        2  4 4        2  3 5        3  2 回答者1は問1に2、問2に2と答え 回答者2は問1に3、問2に4と答え… というふうに表はつくってあります。 このままグラフをつくると、最初から選択肢が2と3と4しかないようなグラフができてしまいます。 わかりにくくて申し訳ないですが、よろしくお願いします。 過去の質問を検索したのですが、見つけられませんでした。もし既に質問されていたらすみません。