【EXCELの神様へのお願い】データをなんとか表にしてまとめたいです。

EXCELについての質問です。

EXCELでデータをなんとかして、表にまとめたいのですがやり方が見当もつかないです。
方法を教えていただけると幸いです。
やりたいことが3つ（ⅰ、ⅱ、ⅲ）あります。

補足するところ？に画像をもう1枚添付しておきます。

【やりたいこと】
実際に持っているデータは、“添付した写真の①”のようなデータとなります。
（ⅰ）①のデータを人間に注目して、②を作りたいです。
見やすくしたいからです。

（ⅱ）②のデータから、【果物別】の表を作りたいです。
・人間が “どの果物” を “どんな組み合わせで買った” のかを順位化したいです。
・得点というのは、“りんごとりんご”を買った人が Aさん、Cさん、Fさん、Iさんの4人で
　4点という意味です。
・対象となる組み合わせは、果物を2つ以上の組み合わせとなります。

（ⅲ）②のデータから、【果物・売店別】の表を作りたいです。
・ある人間が、”どこの売店“ で “どんな果物“ を買ったのかの組み合わせを順位化したいです。
・A店で“りんご”を買った人は、B店でも”りんご“を買う傾向があるといったことを表したいです。
　（現実で、りんごを2つの店で買い分けることはあまりないと思いますが、スルーしてください...）
・得点は、”りんご（A店）“ と ”りんご（B店）“ を買った人が、Aさん、Fさん、Iさんの3人で
　3点という意味です。

EXCEL初心者で、関数とかよくわかってないですが、
なんとかしてデータを表にまとめないといけないので、EXCELの神様、どうかお助けください！

【EXCELのver】
ofiice365なので最新？だと思います。
win 10

教えてgooではじめて質問するので、おかしな点があれば何でも仰ってください。

質問者からの補足コメント

• 2枚目の画像となります。

  
    補足日時：2020/02/13 13:50

No.4 ベストアンサー 回答者： fujillin 回答日時： 2020/02/13 17:36

No1です

＞色を扱わなければ、関数でもできそうでしょうか？
関数で②の表をまとめることは可能だと思います。
それ以降は、（せっかくご説明いただきましたが）よく理解できないので、可能/不可能の判断もできません。

ひとまず、元のデータから②を関数で求める方法をご参考までに以下に示しておきます。
ご例示の表の上のものを利用するとして・・・、

Ｆ4セルに
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$4:A$29)/(COUNTIF(OFFSET(A$4,0,0,ROW(A$4:A$29)-3,1),A$4:A$29)=1),ROW(A1))),"")
の式を入力して、下方に適当な範囲までフィルコピー。

さらに、Ｇ4セルに
=IF($F4="","",IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW(A$4:A$29)/($A$4:$A$29=$F4),COLUMN(A1))),""))
の式を入力して、右方向、下方向へ必要な範囲までフィルコピー。
で、②の表が得られるものと思います。

※ ご提示のサンプルでは30行目に異種の値があるので、データ対象範囲を4～29行に設定してあります。
実際には、式中の範囲を必要な範囲に修正する必要があります。
＞②の行は最大１１行ではなく、約2万行となります。
とのことですので、（上記の計算は配列計算となっているため）エクセルが計算するのに時間が掛かるかも知れません。


ご質問の後半に関しては、内容を理解できる方からの回答にお願いいたします。

この回答へのお礼

ご回答ありがとうございます。
②の表ですが、作ることができました。ありがとうござます。
少し、疑問に思うことがあるのですが、表を作り終えた後も、再計算という形で何度も更新されています。再計算の時にexcelが重くなってしまうのですが、これは仕様でしょうか？

説明が不十分で、申し訳ありませんでした。的を射てない長文を最後まで読んでいただき、お手数をおかけし、申し訳ありませんでした。

お礼日時：2020/02/13 19:04

No.9 回答者： fujillin 回答日時： 2020/02/14 17:30

No4です

再度じっくりと説明を読んだら、各人の組み合わせ数を求めるところまでは理解できたかもしれません。
でも、そっから先は、何をしたいのか やはり闇の中のままです。

各人のリストで品物の種類がｍ種類あって、それぞれの個数がｎ(m)個であるとするなら、組み合わせ数は、
　(n(1)+1)*(n(2)+1)*(n(3)+1)* ・・・ *(n(m)+1)-1-m
になる、という意味に捉えましたが、この解釈で合っているのでしょうか？？

でも、これって、ここまではエクセルとは無関係で、「何をしたいか」の説明の範囲とも言えます。
式を示して「これで算出したい」と明示してくれた方が、長く複雑な説明をされるよりも皆さん勘違いすることなく理解できるはずと思います。（仮に意味が理解できなくても計算はできるので問題がない）
ってな調子で、その先で何を求めたいのかを示していただけるとわかりやすくなると思うのですが・・・


＞再計算の時にexcelが重くなってしまうのですが、これは仕様でしょうか？
No4にも記しておきましたが、仕様というよりも、計算量が多くて時間（=負荷）が掛かっているだけと想像します。
配列計算の場合、範囲を大きくするとべき乗的に計算量が増加するので時間が掛かるようになります。
対処法としては、一気に結果を求めようとせずに、作業列などを利用して分割して計算させ、配列計算を行わないようにすれば大幅な速度アップが期待できます。

まぁ、マシンのスペックを上げることでも速くなる可能性は十分にありますけれど。
別の対処法として、「自動計算」はさせずに「個別計算」にしておくことで、操作中に計算が始まって重くなるようなことは起こらなくなるはずです。

この回答へのお礼

ご回答ありがとうございます。お返事が遅くなり、申し訳ありません。

＞各人のリストで品物の種類がｍ種類あって、それぞれの個数がｎ(m)個で・・・
この解釈が、各人の組み合わせの種類を表すものなら考え方はお間違いないと思います。組み合わせの数の合計をカウントしたいということなのですが、恐れ入りますが自分で数式に表すのは、難しいです。
皆さまのご回答をみて、時間はかかりますが、手作業で細分化して、頑張った方がいいことに気づくことができました。
別の方法で、解析を試みた結果、ある程度、似た目標の結果が出ましたのでそれで対応したいと思います。
色々と説明に問題があり、何がしたいのかをお伝えできない状態で申し訳ありません。この質問では、②の表などのやり方を教えていただいたということで、ベストアンサーにさせていただきます。

＞別の対処法として、「自動計算」はさせずに「個別計算」にしておくことで、操作中に計算が始まって重くなるようなことは起こらなくなるはずです。
こちらの方法で何とかなりました。ありがとうございます。

お礼日時：2020/02/17 18:26

No.8 回答者： mike_g 回答日時： 2020/02/14 12:17

初心者といへども色々アラーナです。

「EXCEL初心者で、関数とかよくわかってない」けど、「この部分なら出来る」といふ箇処があれば、其れはどの部分か教へてください。
それとも所謂“マルナゲ”ですか？

この回答へのお礼

ご回答ありがとうございます。ご指摘いただき、ありがとうございます。
マルナゲになりますよね。やり方が本当に、見当もつかないので、どんな関数を使うのか等、何かご教授いただけないかと質問させていただきました。

お礼日時：2020/02/14 14:58

No.7 回答者： Qchan1962 回答日時： 2020/02/14 00:08

#6です。

＃１＃２の補足を読んでいませんでした。
かぶった部分、お許しください。ただ、やはり言葉ばかりでうまく理解できていません。
＞計算の仕方は、このようなご説明でお分かりいただけますでしょうか？
数式にしていただいた方が、よろしいかと思います。
たとえ出来ても、22万データ、（2万行）さらに順列計算となると、VBAにしても、メモリスタックなどを気にしないといけないかも知れません。
処理的には、Excel,VBAは不向きかも知れませんね。

この回答へのお礼

ご回答ありがとうございます。お返事が遅くなりすみません。

数式にすることが、難しくて、文字の説明しかできないもので、申し訳ないです。
ご回答していただいた、皆さまのご指摘通り、順位付けの定義が曖昧で、計算方法がよくわからないということから、一つの作業で完結さそうとするのではなく、条件を細分化（果物が2個の人、3個の人...）（りんごとみかんを買った人だけを集めて、売店ごとに順位化...）して、地道に処理していこうと思います。

＞処理的には、Excel,VBAは不向きかも知れませんね。
そうなんですね。簡単に計算ができるなら...と思っていましたが、地道に頑張ります。
お手数をおかけして、申し訳ありませんでした。丁寧に全回答を読んでいただき、ありがとうございました。

お礼日時：2020/02/14 14:49

No.6 回答者： Qchan1962 回答日時： 2020/02/13 23:37

こんばんは、

疑問があるので、補足願います。ⅰに関しましては、表にまとめるだけかと思いますし、
すでに回答があるかと思います。
ⅱに関して、、＞対象となる組み合わせは、果物を2つ以上の組み合わせとなります。

ⅲに繋がる部分でもあるかと思いますが、例の果物自体の順位は無いのですか？
例では、リンゴ、リンゴと買っている方が4人ですが、りんご、りんご、みかん、みかん、、ばなな、ばなな　共い一人の場合
どのように順位付けしますか、すべて1位で良いですか？その中の人が、3店舗で同じものを買っていたら、その人が1位ですか？
ⅲにある
＞ある人間が、”どこの売店“ で “どんな果物“ を買ったのかの組み合わせを順位化したいです。
（基準がうまく読み解けません。すみません）
＞・A店で“りんご”を買った人は、B店でも”りんご“を買う傾向があるといったことを表したいです。
（C店、D店など各店舗の順位基準もイマイチ理解できません）

なんとなくでしか、伝わらず、すみません。ご希望を数式化出来たら、示してください。

そうすれば、VBAや関数を使ってエクセル表を作る事は、難しい事ではないと思いますので

No.5 回答者： tom04 回答日時： 2020/02/13 21:30

こんばんは！

横からお邪魔します。
本来であれば(i)~(iii)まで回答できれば良いのですが、
(ii)と(iii)については理解できませんので、
とりあえず(i)だけ・・・
VBAになりますが一例です。
元データはSheet1にあり、Sheet2に表示するとします。
どちらも1行目は項目行でデータは2行目以降にあるという前提です。

標準モジュールにしてください。

Sub Sample1()
　Dim myDic As Object
　Dim i As Long, k As Long
　Dim lastRow As Long, lastCol As Long
　Dim wS As Worksheet
　Dim myKey, myItem, myR, myAry

　　Set myDic = CreateObject("Scripting.Dictionary")
　　Set wS = Worksheets("Sheet2")
　　　lastRow = wS.Cells(Rows.Count, "A").End(xlUp).Row
　　　lastCol = wS.UsedRange.Columns.Count
　　　　If lastRow > 1 Then
　　　　　Range(wS.Cells(2, "A"), wS.Cells(lastRow, lastCol)).ClearContents
　　　　End If
　　　　　With Worksheets("Sheet1")
　　　　　　lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
　　　　　　myR = Range(.Cells(2, "A"), .Cells(lastRow, "B"))
　　　　　　　For i = 1 To UBound(myR, 1)
　　　　　　　　If Not myDic.exists(myR(i, 1)) Then
　　　　　　　　　myDic.Add myR(i, 1), myR(i, 2)
　　　　　　　　Else
　　　　　　　　　myDic(myR(i, 1)) = myDic(myR(i, 1)) & "_" & myR(i, 2)
　　　　　　　　End If
　　　　　　　Next i
　　　　　　myKey = myDic.keys
　　　　　　myItem = myDic.items
　　　　　　　For i = 0 To UBound(myKey)
　　　　　　　　wS.Cells(i + 2, "A") = myKey(i)
　　　　　　　　myAry = Split(myItem(i), "_")
　　　　　　　　　For k = 0 To UBound(myAry)
　　　　　　　　　　wS.Cells(i + 2, k + 2) = myAry(k)
　　　　　　　　　Next k
　　　　　　　Next i
　　　　　End With
　　　　　　Set myDic = Nothing
　　　　　　　MsgBox "完了"
End Sub

とりあえずこんな感じではどうでしょうか？m(_ _)m

この回答へのお礼

ご回答ありがとうございます。お返事が遅くなりすみません。
せっかく作っていただいたのに、申し訳ありません。
No.1の方が関数での方法を教えていただきまして、色は特にこだわりはなかったため、関数でできる方でさせていただきました。（見やすいかな...という理由であるためです）

(ⅱ）（ⅲ）の説明が下手で申し訳なかったです。

お礼日時：2020/02/14 14:26

No.3 回答者： 銀鱗 回答日時： 2020/02/13 16:45

なんか面倒なんで、ピボットテーブルで仕訳けてみてはいかがですか。

で、出てきたデータをコピーして「値」で貼り付け直す。

この回答へのお礼

ご回答ありがとうございます。
ピボットテーブルで仕分けるという方法ですが、挑戦してみます。

お礼日時：2020/02/13 22:43

No.2 回答者： yoreyore 回答日時： 2020/02/13 15:31

疑問なことがあります。

画像には、｢2つ以上の種を買う、上限はない｣とありますが、例えば、ＡさんがA店リンゴ、B店リンゴで終わらず、C店バナナ、D店ミカン、D店リンゴ、C店キウイ、B店バナナ、A店ミカンというように全店で、2つの購入をしたようなデータもあり得るのでしょうか。　
｢上限はない｣と言うのはＨさんが、A店で３つ、B店で４つ、Ｃ店で5つ、D店で6つということもあるのでしょうか。　その場合、②の横列は18列になります。
【果物別】の得点も、購入数が増えると、高い得点のものが出てきます。
Ｋさんまでの11名ではなく、14名、18名を対象にすることもあるのでしょうか。
質問では、購入種は5種となっていますが、6種、10種というようなこともあるのでしょうか。
店数も4店のようですが、7店になると、どうなるのか、かなり気になります。

画像にある状態だけをカウントするということではないのだと思います。
質問が、組合せをカウントするということのようなので、対象が固定しているのと、変動があるのとでは、やり方が変わるように思います。
組合せは要素が増えると対象が膨大になりますから、条件を明確にしないと、方法が決まらないように思います。

この回答へのお礼

ご回答ありがとうございます。
画像は、やりたいことを表す一例となっております。

>画像には、｢2つ以上の種を買う、上限はない｣とありますが、例えば～～
もちろん、ございます。同じお店で何種類も買う場合もございます。

まとめようとしているデータの、②の行は最大１１行ではなく、約2万行となります。
列も最大３列ではなく、１０列以上になっております。
また、店の種類も最大４店だけでなく、１０種類以上となります。

＞質問が、組合せをカウントするということのようなので、～～
行のほうは、件数を数えることができるため、対象を固定できるとは思います。
しかし、列のほうは、数えることができない（工夫をすればできるかもしれないですが）ため変動するものとお考えいただければと思います。

お礼日時：2020/02/13 16:17

No.1 回答者： fujillin 回答日時： 2020/02/13 14:11

こんにちは

＞関数とかよくわかってないですが、～～
作業手順などは読んでいませんが、ご提示のままの結果を求めているのであれば、関数だけでは実現できません。（関数では色を扱えないので）
自動化したいのであれば、ロジックの整合が取れていることを条件にできれば、VBAで可能かと思います。

でも、
＞なんとかしてデータを表にまとめないといけないので、～
とのことなので、一回だけの処理なら、プログラムなど作ってもあまり意味がないでしょう。
（失礼ながら、質問者様が作成している間に、手作業で充分に処理が完了する）


なんとなくの雰囲気で、まず元のデータのコピーを作成しておいて、「氏名」-「種類」-「店」の順でキーとしてソートすれば大半の作業が終わりそうに思います。
（ソートの機能はエクセルの「並べ替え」を利用すればできます）
これで、Ａさんだけまとまるので順に処理してゆけば良い。

次に、Ｃ列の色をＢ列にコピーしておきます。
（Ｃ列をコピーして「書式のペースト」をすればまとめて色だけをコピーできます）

あとは、「点数」をどう計算するのかさっぱりわかりませんが、最終形の表にコピペしながら埋めてゆけば良いのではないでしょうか。

この回答へのお礼

ご回答ありがとうございます。こんにちは！

>関数だけでは実現できません。（関数では色を扱えないので）
色を扱わなければ、関数でもできそうでしょうか？（VBAより、関数のほうが簡単そうだなという安直な考えです。）

＞一回だけの処理なら、プログラムなど作ってもあまり意味がないでしょう。
実は、件数が2万件ほどあるデータが数種類ありまして、それを表にしてまとめようとしているため、プログラムで自動化できるなら頑張りたいです。

＞あとは、「点数」をどう計算するのかさっぱりわかりませんが、～～
②を見ていただきながら、ご説明いたします。
得点は、全データの中（画像ではA~Kさん）で、ある組み合わせが何回出てくるかの数になります。

【果物別】
Aさんは、1.”りんご と りんご”　で1通りの組み合わせを持っています。
Cさんは、1."りんご と りんご”　2.”りんご と ばなな”　3.”りんご と りんご と ばなな”で3通りの組み合わせを持っています。
Fさんは、1.”りんご と りんご”　2.”りんご と りんご と りんご”で2通りの組み合わせを持っています。
Jさんは、1.”りんご と みかん”　2.”りんご と ばなな”　3.”みかん と ばなな”　4.”りんご と みかん と ばなな”で4通りの組み合わせを持っています。

全てのデータの中（画像ではA~Kさん）で、”りんご と りんご”　の組み合わせは、Aさん、Cさん、Fさん、Iさんの4人なので点数が４点となります。


【果物、売店別】
Aさんは、”りんご（A店）と りんご（B店）”で１通りの組み合わせを持っています。
Cさんは、1.”りんご（A店）と りんご（C店）” 2.”りんご（A店） と ばなな（D店）" 3."りんご（C店） と ばなな（D店）" 4."りんご（A店）と りんご（C店）と ばなな（D店）”の4通りの組み合わせを持っています。

全てのデータの中（画像はA~Kさん）が"りんご（A店）と りんご（B店）”の組み合わせは、Aさん、Fさん、Iさんの3人なので点数が3点となります。

計算の仕方は、このようなご説明でお分かりいただけますでしょうか？
もしプログラムでできるので自動化あれば、やり方をご教授いただければ幸いです。

長文で、申し訳ありません。

お礼日時：2020/02/13 15:17