エクセル 数値を抜き取って並べたい

質問

エクセルでの質問です。お願いします。

1行目に次のようなデータが並んでいるとします。
51 64 44 37 65 78 34 45 53 66 6 18 26 8 11 30

2行目には77以上の数を抜き取って並べる、
3行目には51以上77未満の数を抜き取って並べる
4行目には36以上51未満の数を抜き取って並べる
5行目には36未満の数を抜き取って並べる
というように自動的に数を抜きとって並べるようにしたいのです。

＜このようにしたい＞
78
51 64 65 53 66
44 37 45
34 6 18 26 8 11 30

＜こうではない（--は空白セル）＞
-- -- -- -- -- 78
51 64 -- -- 65 -- -- -- 53 66
-- -- 44 37 -- -- -- 45
-- -- -- -- -- -- 34 -- -- --　6 18 26　8 11 30

1行目のデータによって2行目以降を自動的に作成させるには、
各セルにどのような数式を入れれば良いのでしょうか。
マクロを作るとか複雑な方法はなるべく避けたいのですが。
よろしくお願いします。

回答No.1

Wendy02 · ベストアンサー

こんにちは。

＞マクロを作るとか複雑な方法はなるべく避けたいのですが。

マクロというよりも、この種のものは、ユーザー定義関数で作るのだと思いますが、関数のほうが、私は、ずっと難しいと思いますね。

式の作り方として、A2まは、A3から、フィルダウン・ドラッグして作ればよいです。

A2:→
=IF(COLUMN(A$1)>COUNTIF($A$1:$P$1,">=77"),"",INDEX($A$1:$P$1,1,SMALL(INDEX(($A$1:$P$1>=77)*COLUMN($A$1:$P$1),,),COUNTIF($A$1:$P$1,"<77")+COLUMN(A1))))

A3:→
=IF(COLUMN(A$1)>COUNTIF($A$1:$P$1,">=51")-COUNT($A$2:P2),"",INDEX($A$1:$P$1,1,SMALL(INDEX(($A$1:$P$1>=51)*($A$1:$P$1<77)*COLUMN($A$1:$P$1),,),COUNTIF($A$1:$P$1,"<51")+COLUMN(A1)+COUNT($A$2:$P2))))

A4:→
=IF(COLUMN(A$1)>COUNTIF($A$1:$P$1,">36")-COUNT($A$2:P3),"",INDEX($A$1:$P$1,1,SMALL(INDEX(($A$1:$P$1>=36)*($A$1:$P$1<51)*COLUMN($A$1:$P$1),,),COUNTIF($A$1:$P$1,"<36")+COLUMN(A2)+COUNT($A$2:$P3))))

A5:→
=IF(COLUMN(A$1)>COUNT($A$1:$P$1)-COUNT($A$2:P4),"",INDEX($A$1:$P$1,1,SMALL(INDEX(($A$1:$P$1<=36)*COLUMN($A$1:$P$1),,),COLUMN(A3)+COUNT($A$2:$P4))))

自動的とはいきませんが、試してみてください。

回答No.5

Wendy02

#1のWendy02です。

統一式を考えてみました。

これは、サンプルですから、分りやすいように、
A列に、

A2 ～A6 まで、
100
76
50
35
0

と、範囲の数値を置きます。

B2から、

=IF(COLUMN(A1)>SUM(($A$1:$P$1<$A2)*($A$1:$P$1>$A3)),"",INDEX($A$1:$P$1,,SMALL(IF(($A$1:$P$1<$A2)*($A$1:$P$1>$A3),COLUMN($A$1:$P$1),""),COLUMN(A1))))

として、縦横にフィルハンドルコピーをします。
配列数式なので、一旦式を入力したら、F２を押して、『ShiftとCtrlを押しながらEnterキー』を押して、再確定してください。

なお、A2～A6の範囲の数値は、じゃまなら、どこか別のところに置いてよいと思います。

また、この式の、COLUMN(A1)は、どこのセルから始めても、初期値は、A1です。お間違えにならないように。

お礼

すいません、まとめてお礼です。
丁寧な解説ありがとうございます。
式の内容がとても複雑ですぐには理解できませんが、努力してみます。
もうしばらく締め切らずにおきます

回答No.4

cockerel

追加です（Excelを使い慣れている場合には無視してください）：
1.　上のセルを参照する式を入力して「こうではない」を作っている場合には、値をコピーしてください（「式」は空白セルではありません。）
2.　ジャンプの前にアクティブ領域を選択する必要があります。
3.　Excelは値ペーストの空白を空白セルとして認識できない場合があります。その場合には、置換で空白を空白に一括置換すれば認識します。

回答No.3

cockerel

＞こうではない（--は空白セル）
の作り方はわかるわけですね。
それを作り、編集＞ジャンプ＞空白セルを選択します。
削除＞左方向へシフト、で終わりです。
たぶん（Macでしか検証していません）。

回答No.2

noname#204879

[フィルタオプションの設定]で４回ほどコツコツと実行した結果を、参考までに、下に示します。
セル C1、G1 にそれぞれ抜取最大値、抜取最小値を入力しています。
セル C3 に式 =AND($A2<C$1,$A2>=D$1) を入力して、此れを右方にズズーッと複写しています。
ただし、ご所望の「自動的に作成させる」方法には程遠いので、此処では一切解説をやめておきます。使い物になりそうで、かつ、解説をご希望なら、その旨をお知らせください。

　　　A　　B　　C　　 D　　 E　　　F　　　G
1　　dat　　　　100　　77　　 51　　 36　 1
2　　 51
3　　 64　　　FALSE　TRUE　FALSE　FALSE
4　　 44　　　dat　　dat　 dat　　　dat
5　　 37　　　　 78　　51　　 44　　 34
6　　 65　　　　　　　 64　　 37　　　6
7　　 78　　　　　　　 65　　 45　　 18
8　　 34　　　　　　　 53　　　　　　26
9　　 45　　　　　　　 66　　　　　　 8
10　　53　　　　　　　　　　　　　　 11
11　　66　　　　　　　　　　　　　　 30
12　　 6
13　　18
14　　26
15　　 8
16　　11
17　　30
18