エクセルで、重複データを除外して小さい値順に並べ替える関数

質問

Ａ列に、
５００
８０
２００
２００
１００
３００
と、データがあります。このデータのうち、重複しているデータ（２００）を一つだけ表示させ、かつ、小さい順に並べ替えて、下記のようにしたいのですが…。
８０
１００
２００
３００
５００

調べてみて重複データをチェックする方法や削除する方法などはありましたが、並べ替える方法が見つかりません。フィルタオプションかと思いましたが使い方がよくわかりません。配列数式のこともわかりません。オートフィルタやマクロは使いたくありません。よろしくお願いします。

回答No.5

EL-SUR · ベストアンサー

C列1行目から表示させるとします。

C1セルに
=IF(COUNT(A:A),MIN(A:A),"")

C2セルに
=IF(OR(C1="",C1=MAX(A:A)),"",SMALL(A:A,COUNTIF(A:A,"<="&C1)+1))

と入れて、必要なだけ下にフィルコピーします。

お礼

素晴らし～い！！できました。しかも、すっきりしていてとてもいいですね。どうもありがとうございました。

回答No.8

imogasi

自称imogasi方式の、変型判で解ける。
例データ
下記A列
A列　B列　　　C列　　　　D列　　E列　
500 500 1 500
80 80 5 300
200 200 3 200
200 100
100 100 4 80
300 300 2 #N/A
B1に
=IF(COUNTIF($A$1:A1,A1)=1,A1,"")
を入れて下方向に式を複写。
C列C1に
=IF(B1="","",RANK(B1,$B$1:$B$100))
を入れて下方向に式を複写。
E列E1に
=INDEX($A$1:$A$100,MATCH(ROW(),$C$1:$C$100,0)*1)
を入れて下方向に式を複写。
（１）B列では重複データがないので、安心してRANK関数が使える。同順位がない。
（２）＄１００などは最多見込み行数に変えてください。
B、C列は使わない目立たないところを使ってください。
D列を使っていないのは意味はありません。
（３）該当行を超えるとエラーがでるが、防止策は省略＞

お礼

回答どうもありがとうございます。実は、ほかの質問を見ていて、imogasiさん方式の回答を期待してました(^O^)。
試してみましたが、E列が一部＃N/Aのエラーになってしまいました。やっぱり私のやり方が悪いのでしょうか。ともあれ皆様の回答のおかげで解決いたしました。お世話になりましてどうもありがとうございました。また何かありましたらよろしくお願いします。

回答No.7

noname#204879

A　　 B　　 C
1　list　list　list
2　 500　　80　　80
3　　80　 100　 100
4　 200　 200　 200
5　 200　 200　 300
6　 100　 300　 500
7　 300　 500

１．セル B2 に式 =SMALL(A$2:A$7,ROW(1:1)) を入力して、此れを下方
　　にズズーッとＡ列のデータの下端と同じ行まで複写
２．[データ]→[フィルタ]→[フィルタオプションの設定]を実行
３．“指定した範囲”に目玉入れ
　　[リスト範囲]　 B1:B7
　　[検索条件範囲] (空白のままにしておく)
　　[抽出範囲]　　 C1
　　“重複するレコードは無視する”にチェック入れ
４．[OK]をクリック

お礼

回答どうもありがとうございます。
フィルタオプションて、こういう風に使うんですね。
ご丁寧に教えていただいたおかげで一応できました！
ただ、[抽出範囲]が希望するセルにはできませんでした(>_<)
でも、また別のところで応用して使いたいと思います。どうもありがとうございました。

回答No.6

misatoanna

＃４です。新しい回答が出ているようですが、とりあえず補足を.......。

まず、B2に入れる数式内の $A$2:$A$20 は、A:A に書き換えてください。

> B列は４番目に小さい数値が表示されます。
もしかしたら、数式を入れる先頭セルは ５行目なのではないですか？
その場合 B5 に入れる数式は　=SMALL(A:A,ROW()-4)　です。
ROW() は、数式を入力したセルの行何号が返します。
ですから、５行目に"１"番小さい数値を出す場合は、ROW()-4 なのです。

> D列は＃Num！とエラー表示になります。
Ｄ列に入れる式内のデータ範囲は、C:C の形にはしないでください。
また ROW()-1 の部分も、実際のセル位置に合わせて変えてください。

> [Ctrl]と[Shift]を押しながら[Enter]しますと、数式に｛ ｝が表示される
そうなります。
{　}は、配列関数であることを示しています。

お礼

度々の回答、どうもありがとうございます。
> 数式を入れる先頭セルは ５行目なのではないですか？
その通りです！ROW()-4 にしたらできました！
配列関数などもご丁寧に教えてくださり、本当にどうもありがとうございました！

回答No.4

misatoanna

＃２です。
私の勘違いでした。
次の数式に変更してください。
　B2： =SMALL($A$2:$A$20,ROW()-1)
　C2： =IF(B2=B1,"",B2)
　D2： =INDIRECT(ADDRESS(SMALL(IF($C$2:$C$20="","",ROW($C$2:$C$20)),ROW()-1),3))
　　　　D2だけは、数式入力後に [Ctrl]と[Shift]を押しながら[Enter]してください。（配列数式です）
その後、B2:D2 を選択して、20行目までドラッグコピーします。

お礼

重ねての回答どうもありがとうございます。残念ながらできませんでした。
まず、B列は４番目に小さい数値が表示されます。
また、D列は＃Num！とエラー表示になります。
私のやり方が悪いのでしょうか？
それから、[Ctrl]と[Shift]を押しながら[Enter]しますと、数式に｛｝が表示されるのですが、いいのでしょうか？
せっかく教えていただいたのに済みません。

回答No.3

noname#141205

削除したデータ200は、表示だけさせないのですか？
それとも実際に削除するのですか？
ここでは、削除する方法をやってみましょう。
データ列Ａに付きソートをして昇順にします。
上記データの右横Ｂ列に以下の関数を入力します。このとき
データ２行目(B3)から入力すれば良いです。

Ａ１に表題、Ａ２にデータ一番目、データはＡ○○行目まで。
Ｂ１は表題、Ｂ２は空白のまま、Ｂ３に下記関数。
　=exact(a2,a3)
つまり、自己と一つ上の数字を同一かどうか比べる。
Ａ○○行目まで、関数をドラッグして入れます。
すると各データにつき、自分より一つ上のデータと同一ならTRUE、
違うときは、FALSEが表示されます。
Ａ１からＢ○○まで範囲を選択し、オートフィルターを選択します。
Ｂ列に付き、FALSEのデータを選択すると、重複していないものを
見ることが出来ます。
重複データを削除したいのなら、TRUEを選択してそのデータを
削除します。Ｂ列につき、FALSEのデータだけとなります。

お礼

回答どうもありがとうございます。
ソートは使いたくなかったのです。。。
そして、実際に削除すると、表が崩れてしまうので、表示だけさせたくないのです。
でも、exact関数は使えそうですね。ありがとうございました。

回答No.2

misatoanna

> オートフィルタ、マクロ、配列関数を使わない.

作業列を使うことは、どうなのでしょうか。

１行目は項目行にして（または空白）、A2:A20 にデータが並んでいるとして、
次のように入力します。
Ｂ,Ｃ列が作業用です。
　B2： =SMALL($A$2:$A$20,ROW()-1)
　C2： =IF(B2=B1,0,COUNTIF($B$2:$B$20,B2)-1)
　D2： =OFFSET(B2,SUM($C$2:C3),0)
その後、B2:D2 を選択して、20行目までドラッグコピーします。

お礼

回答どうもありがとうございます！
ご提示の方法を試してみましたができませんでした(>_<)
ROW()-1　にはどんな意味があるんですか？
それから、配列関数は使わないということではなく、何のことかわからないだけなんです。
でも、教えていただいた、「IF(B2=B1…」　の辺りにヒントがありそうな気がします。もうちょっと頑張ってみます。どうもありがとうございました。

回答No.1

STICKY2006

個人的には、並べ替えが一番楽じゃないかと思いましたが（汗
関数も何もなく、メニューに出てますよ？

メニューバーの
データ→並べ替え。
でどうでしょうか？

>>オートフィルタやマクロは使いたくありません。
ちょっと無理じゃないでしょうか。
重複データをチェックする方法や削除する方法、並べ替える方法が見つかったら。。。それを応用するしかないかと思いますが。。。

ザッと探した感じでは、「重複を削除して並べ替える」という機能を持った関数はなさそうでしたので。

使用頻度が高く、システムとして使用するのでしたら、マクロで作成してもいいでしょうし、ただ単に、このようなことがしたい～。という感じで投稿されているのでしたら、マクロ登録などする必要もないので、重複削除→データ並べ替え。の作業を行いましょう。

お礼

回答どうもありがとうございます。
実は、Ａ列の数字は、別表から抽出しているものです。それで、その数字をsmall関数を使って、並べ替えるところまではできているのです。ただ重複したデータがあり、それらが表示されると非常に見づらく、次の操作が間違いやすくなってしまうため、安全のためにその重複データは一つだけ表示させたいのです。
何だか分かりづらくてすみません。よろしくお願いします。