エクセル2003で質問です。オートフィルタで「トップテン」という表示が

質問

エクセル2003で質問です。オートフィルタで「トップテン」という表示ができますよね？
ということは、（10位以下も続けて）多いものから順に並べる、ということもできるのでしょうか？

できるようでしたら、やり方を教えていただきたいです。
その場合、オートフィルタを使わなくてもかまいません。
多いものから順に並べたデータが別のデータとして作成できればそれでも助かります。

回答No.10

KURUMITO · ベストアンサー

オートフィルタなどで行を表示されるとのことで、その際に最も出現回数の多いものを上の行に並べるとのことで答えをしてきましたが、そうではなく最も出現回数の多いものの順に品名を並べたいとのことでしたら方法は全く異なりますね。
例えばＡ1セルには品名とあり下の行にりんごなどのデータが入力されるとしたらＢ列は作業列としてＢ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,COUNTIF(A:A,A2)+(0.1-ROW(A1)/10000),""))

Ｂ列には小数点付きの数値などが並びますがこれは同じ回数がある品名がいくつかあるときにそれらを上から順に並べるための工夫です。

そこでお求めの表ですが例えばＤ1セルに品名、Ｅ1セルには出現回数とでも入力します。

Ｄ2セルには次の式を入力して下方にオートフィルドラッグしあす。

=IF(ROW(A1)>COUNT(B:B),"",INDEX(A:A,MATCH(LARGE(B:B,ROW(A1)),B:B,0)))

Ｅ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(D2="","",COUNTIF(A:A,D2))

お礼

たくさんご回答いただきありがとうございました。
作業としてはこれを検証しないままに必要な時期が終わってしまい
データも手元にもうないので、これでできたかどうかは正直わかりません。

ですが、みなさんが私のつたない説明で
いろんなパターンを試みてくださったことに感謝します。

いつまでもお礼が遅れてしまうのも恐縮ですので、一度締め切りたいと思います。
BAは一番多くご回答くださった方に。他のみなさまもありがとうございました。

回答No.9

KURUMITO

回答Ｎｏ7です。Ｂ列に小数点以下が表示されるのは何ら問題ありません。同じリンゴならリンゴの行がランクに応じて集団としてお互いの行が連続した状態で並ぶようにするために工夫をしているのです。

お礼

そうなのですね、ありがとうございます。

回答No.8

tom04

No.4・5です！

大きく外していたようでごめんなさい。

もう一度画像をアップしてみます。

↓の画像でA列にデータがありその出現回数の多い順に表示すれば良いわけですよね？

B列を作業用の列として、データの出現回数を表示させます。
B2セルに
=IF(COUNTIF($A$2:A2,A2)=1,COUNTIF(A:A,A2),"")
という数式を入れ、フィルハンドルの（＋）マークでダブルクリック、またはオートフィルで下へコピーします。

そして、D2セルに
=IF(F2="","",RANK(F2,$F$2:F2))

E2セルに
=IF(F2="","",INDEX($A$2:$A$1000,SMALL(IF($B$2:$B$1000=F2,ROW($A$1:$A$999)),COUNTIF($F$2:F2,F2))))
これは配列数式になってしまいますので、この画面からE2セルにコピー＆ペーストした後に、
F2キーを押す、まがはE2セルをダブルクリック、または数式バー内で一度クリックし、編集可能にしたあとで
Shift＋Ctrlキーを押しながらEnterキーで確定です。

F2セルには
=IF(COUNT($B$2:$B$1000)<ROW(A1),"",LARGE($B$2:$B$1000,ROW(A1)))
（これは配列数式ではありません）

最後にD2～F2セルを範囲指定し、F2セルのフィルハンドルで下へコピーすると
画像のような感じになります。
尚、数式は元データの1000行目まで対応できるようにしています。

以上、長々と失礼しました。m(__)m

補足

ありがとうございます！
たくさん書いていただいたのですが、
Eの項目に同じ項目が出てしまい、うまくできません…

B列で回数がでただけでも作業としては間に合いそうなので
とりあえずB列の数式で作業をすすめます！
お礼が遅くなるかもしれませんが、お許しください。

回答No.7

KURUMITO

回答Ｎｏ２，６です。
Ａ１セルに品名とか入力してＢ列およびＣ列は作業列でＢ２セルには次の式を入力して下方オートフィルドラッグします。

=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,COUNTIF(A:A,A2),INDEX(B$1:B1,MATCH(A2,A:A,0))-COUNTIF(A$2:A2,A2)/1000))

Ｃ１セルにはランクとでも入力しＣ２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",RANK(B2,B:B,0))

その後にＣ列を選択して「データ」メニューから「並べ替え」、さらに選択範囲を拡張するのままで「並べ替え」をクリックします。

ランクが最優先されるキーで「昇順」となっていることでしょうからそのままＯＫすればよいでしょう。
バナナ、りんご、パイナップルなどの順でＡ列には品名が並びますね。

補足

すみません、Ｂ列に入力してオートフィルドラッグをすると、なぜか小数点のある数値が…
これは特に気にしなくていいのでしょうか？

回答No.6

KURUMITO

回答Ｎｏ２です。文字列が入力されている列を対象にした場合、エクセル２００３のオートフィルタでの［トップテン］の操作は機能しません。
文字列が入力されている列を対象に文字列の長さが多い順にデータを並べるのでしたら次のようにされてはいかがでしょう。
例えばＡ列の２行目から下方にデータがあるとします。
Ｂ列やＣ列は作業列としてＢ１セルには文字数とでも入力し、Ｂ２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A2="","",LEN(A2))

次にＣ１セルにはランクとでも入力し、Ｃ２セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B2="","",RANK(B2,B:B,0))

これでＣ列には最も文字数の多い行に１が、その他の行についても文字数の量に応じて順位が表示されます。

そとでＣ列を選択してから「データ」メニューから「並び替え」を選択します。
「選択範囲を拡張する」にチェックがされたままで「並び替え」をクリックします。
次の表示の画面でＯＫボタンをクリックすることでＡ列には文字列の長い行が上の行に並んで表示されることになります。Ａ列の望みの範囲を選択してコピーし、別のシートなどに貼り付けを行うなどのことをすればよいでしょう。

お礼

たくさん書いていただいたのにすみません、下の方にもお伝えしたのですが、

文字列の多いものではなくて、…

たとえば、、りんご、バナナ、バナナ、バナナ、パイナップル、りんご、もも、バナナとあったら、

バナナ、りんご、もも、パイナップルと並んで欲しいという意味でした。

私がトップテンの機能をよく理解できていなかったために皆さんを混乱させてしまったようで
申し訳ありません。

回答No.5

tom04

No.4です！
たびたびごめんなさい。

投稿した後でもう一度質問文を読んでみると
＞多いものから順に並べたデータが別のデータとして作成できればそれでも助かります。

とありましたので、再びお邪魔しました。

前回の表はそのまま使わせてもらって
E2セルの数式だけを変更してみてください。

E2セルは
=IF(COUNT($C$2:$C$100)<ROW(A1),"",SMALL($C$2:$C$100,ROW(A1)))

F2セルは前回の数式そのままで前回同様配列数式ですので、Shift＋Ctrlキーを押しながらEnterキーで確定です。

E2・F2セルを範囲指定し、F2セルのフィルハンドルでオートフィルで下へずぃ～～～！っとコピーすると
全てのデータが表示されるはずです。

どうも何度も失礼しました。m(__)m

お礼

こちらこそたびたびすみません。
質問自体がおかしかったようで…
大変失礼いたしました。

回答No.4

tom04

こんばんは！
横からお邪魔します。

No.2さんの補足に
＞文字データでも多いものから順に並びますよね？＜トップテン

とありますが、これは文字数が多いものからトップテンを表示したい！ということでしょうか？

一応そういうことだとして・・・
文字数での順位づけをした場合は同順位（同じ文字数のセル）も出てくるかと思いますので、
それに対応できる方法です。
（同順位の場合は上側の行のデータが最初に表示されます。）

↓の画像のように作業用の列を2列設けています。

作業列B2セルに
=LEN(A2)

C2セルに
=RANK(B2,$B$2:$B$100)

として、B2・C2セルを範囲指定し、C2セルのフィルハンドルの（＋）マークでダブルクリック、またはオートフィルで下へコピーします。

そして、表示させたいセルE2に
=IF(COUNTIF($C$2:$C$100,"<=10")<ROW(A1),"",SMALL($C$2:$C$100,ROW(A1)))

F2セルに
=IF(E2="","",INDEX($A$2:$A$100,SMALL(IF($C$2:$C$100=E2,ROW($A$1:$A$99)),COUNTIF($E$2:E2,E2))))

このF2セルの数式は配列数式になってしまいます。
F2セルにこの画面からコピー＆ペーストしただけではエラーになるか、まともなデータを返しませんので
F2セルに貼り付け後、F2キーを押す、またはF2セルをダブルクリック、または数式バー内で一度クリックします。
編集可能になりますので、Shift＋Ctrlキーを押しながらEnterキーで確定します。
数式の前後に｛　｝マークが入り配列数式になります。

最後にE2・F2セルを範囲指定し、F2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

尚、数式は元データの100行目まで対応できるようにしていますが、データ量によって範囲指定の領域はアレンジしてください。

以上、参考になればよいのですが
的外れなら読み流してくださいね。m(__)m

お礼

ああ、たくさん書いていただいたのにすみません…
「＞文字データでも多いものから順に並びますよね？＜トップテン
とありますが、これは文字数が多いものからトップテンを表示したい！ということでしょうか？」

いえ、そうではなくて、たとえば、、りんご、バナナ、バナナ、バナナ、パイナップル、りんご、もも、バナナとあったら、

バナナ、りんご、もも、パイナップルと並んで欲しいという意味でした。
お手数をおかけしたのに本当にすみません。

回答No.3

conanthe

文字列の場合、「トップテン」は機能しません。数字が文字列として入力してあるのなら、オートフィルタの▼をクリックしたときに出るメニューの上から2番目の「降順」をクリックすれば、文字列であっても大きい順に並べ替えられます。

お礼

そうなんですね。たまたま出てくるトップテンはあまり意味がないものだったのか…
無知で本当にすみません、ありがとうございました。

回答No.2

KURUMITO

数値を大きいものから下方にデータをならべたい列を選択してから、ツールバー上の「降順で並べ替え」のボタンをクリックすることでよいでしょう。

お礼

説明不足ですみませんでした。ありがとうございました。

補足

質問の説明不足ですみません。数値ではなく文字データです。
文字データでも多いものから順に並びますよね？＜トップテン

回答No.1

Cupper

ピボットテーブル

お礼

ありがとうございます、しかしやり方がわかりません、ごめんなさい