[Excel]重複数のトップテン

質問

添付画像の赤枠ように、Excelの関数を使って重複したデータの「重複数のトップテン」
を作成したいのですが、COUNTIFを使って重複数を出したは良いものの、その先がわかりません。
（※画像内の赤枠は手動で作成したサンプルです）

実際には、「日付」「商品名」といったデータがあり、例として下記のようになっております。
20XX/10/01 みかん
20XX/10/01 みかん
20XX/10/01 りんご
20XX/10/02 みかん
20XX/10/03 りんご
20XX/10/03 りんご

上記データの「日付」部分をカウントし、「モノが売れた日トップテン」を取得したいのです。
上記の例では
1位：　3個売れた　20XX/10/01
2位：　2個売れた　20XX/10/03
3位：　1個売れた　20XX/10/02
となります。
実際には、データ数は膨大ですので、添付画像のように同じ個数売れた日が複数あります。
それを、赤枠のサンプルのように取得・表示したいのです。

現在は、COUNTIFを用いておりますが、更に簡単な方法がありましたら、COUNTIFを使用しない方法でも構いません。
良い方法、または参考になるURL等ありましたらご教示願います。

よろしくお願い致します。

回答No.1

tom04 · ベストアンサー

こんばんは！
一例です。

＞実際には、データ数は膨大ですので・・・

とありますので、これから説明する方法は不向きかもしれません。
（配列数式を使っています）

とりあえずやり方だけ！

↓の画像のように作業用の列を設けています。
そして、B列の数式を少し変更します。

B2セルに
=IF(COUNTIF($A$2:A2,A2)=1,COUNTIF(A:A,A2),"")

作業列C2セルに
=IF(B2="","",IF(COUNTIF($B$2:B2,B2)=1,RANK(B2,B:B),""))

という数式を入れ、B2・C2セルを範囲指定しC2セルのフィルハンドルでずぃ～～～！っと下へコピーしておきます。

H3セルに
=IF(COUNT(C:C)<ROW(A1),"",INDEX(B:B,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))
としれこれも下へコピー

I3セル（配列数式になってしまいます）に
=IF(OR($H3="",COUNTIF($B:$B,$H3)<COLUMN(A1)),"",INDEX($A$1:$A$1000,SMALL(IF($B$1:$B$1000=$H3,ROW($A$1:$A$1000)),COLUMN(A1))))

※　この画面からI3セルにコピー＆ペーストする場合はI3セルに貼り付け後、数式バー内で一度クリック！
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。

これを列方向と行方向にオートフィルでコピーすると
画像のような感じになります。

とりあえず1000行目まで対応できる数式にしていますが、
データ量によっては表のレイアウトを考え直すか、VBAでの方法が良いかもしれませんね。

今はこの程度しか思いつきませんが、
参考にならなかったらごめんなさい。m(_ _)m

お礼

回答ありがとうございます。
予想以上に複雑な関数式になるのですね。。。
まずは教えて頂きました方法を理解できるよう調べつつ、実環境で試してみたいと思います。
また、VBAを用いる方法も検討してみたいと思います。

アドバイス有難うございました。

回答No.3

imogasi

関数式でやろうとすると、
（１）理解しにくい「多数の関数の組み合わせ」になり式が長くなる
（２）配列数式というものになる
たりする。
それでもよければ（コピペして、うまく行きました、ありがとう式の質問者がほとんど）だが）下記は無視して。
ーーー
作業列を使うことを提案したい
例データ
日付 品物
20XX/10/01 みかん
20XX/10/01 みかん
20XX/10/01 りんご
20XX/10/02 みかん
20XX/10/03 りんご
20XX/10/03 りんご
ＩＩ１に日付と入れる
データーフィルターフィルタオプションの設定
指定した範囲　ＯＮ
リスト範囲　$A$1:$A$7
衆出範囲$H$1:$H$100
重複するレコードは無視する　ＯＮ
検索条件範囲は何も入れない。
ーー
結果
Ｈ列　と　Ｉ列（＝次のステップの結果）
日付
20XX/10/01 3
20XX/10/02 1
20XX/10/03 2
Ｉ２の式は
=COUNTIF($A$2:$A$100,H2)　＜－－Ａ１００の１００などは適宜増やして
下方向に式複写。
結果上記Ｉ列。
ーー
その中で一番件数が多いのは
=LARGE(I2:I100,1)　　＜－－Ｊ１０セルに入れるとする
その日付は
=INDEX($H$1:$H$100,MATCH(J10,$I$1:$I$100,0))
20XX/10/01
２番目以降もＬａｒｇｅの第２引数の１を２にしたりする。連続した順位なら、ＲＯＷ（）－９などで式複写で導出できる。

お礼

回答ありがとうございます。
私の考えていた抽出手順と違い、
1.オートフィルタで、日付の重複データを省いたデータを取得
2.１で取得したデータをもとにCOUNTIFで重複数を取得
3.1と2で取得したデータからランキングを表示
といった手順でしょうか？

用いる関数式が簡略化されるので、良さそうですね。
この方法についても、実環境で試してみたいと思います。

有難うございました。

回答No.2

kagakusuki

今仮に、A1セルとI1セルに「日付」、B1セルに「商品名」、　G1セルに「ランク」、　H1セルに「カウント」、と入力されているものとし、D列とE列を作業列として使用するものとします。
　まず、D2セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER(INDEX($A:$A,ROW())),COUNTIF($A$1:INDEX($A:$A,ROW()),INDEX($A:$A,ROW()))=1),COUNTIF($A:$A,INDEX($A:$A,ROW())),"")

　次に、E2セルに次の数式を入力して下さい。

=IF(ISNUMBER($D2),$D2+(MAX($A:$A)-INDEX($A:$A,ROW()))/MAX($A:$A),"")

　次に、D2～E2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
　次に、H2セルに次の数式を入力して下さい。

=IF(MIN(H$1:H1)=MIN($D:$D),"",LARGE($D:$D,COUNTIF($D:$D,">="&MIN(H$1:H1))*(ROWS($1:1)>1)+1))

　次に、G2セルに次の数式を入力して下さい。

=IF(ISNUMBER($H2),ROWS($2:2),"")

　次に、I2セルに次の数式を入力して下さい。（I2セルの書式設定はA列のセルと同じ設定にして下さい）

=IF(AND(ISNUMBER($H2),COLUMNS($I:I)<=COUNTIF($D:$D,$H2)),INDEX($A:$A,MATCH(LARGE($E:$E,COUNTIF($D:$D,">"&$H2)+COLUMNS($I:I)),$E:$E,0)),"")

　次に、G2～I2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
　次に、I列全体をコピーして、I列よりも右にある列の範囲に貼り付けて下さい。

お礼

回答ありがとうございます。
やはり関数式で表現すると、複雑なものになってしまうのですね。
お教えいただいた方法についても、実環境で試させて頂き、
検討してみたいと思います。

有難うございました。