並び替え機能を使わずに関数で並び替える

質問

A列　B列　C列　
1行目　甲　　９　　商品G
2行目　乙　　４　　商品F

上の表をB列の昇順で並びかえ、下のように再配置するにはどのような関数を書けば良いでしょうか。並び替え機能を使わず、関数で上の表の下に下の表を配置します。

　　　　　A列　B列　C列　
1行目　４　　乙　　商品F
2行目　９　　甲　　商品G

回答No.3

bunjii

元データから目的のデータを得るには次の手順が良いでしょう。

１．元のデータと同じ配列で別シートへ抽出する。
Sheet1が元データとし、目的のデータはSheet2とすればSheet2のA1セルへ下記の数式をセットして右へC1セルまでコピーする。
=IFERROR(INDEX(Sheet1!A:A,SUMPRODUCT(MOD(LARGE((Sheet1!$B$1:$B$9000)*10000+ROW(A$1:A$9000),COUNT(Sheet1!$B:$B)-ROW()+1),10000)),1),"")

2．A1からC1セルを一括して下へ必要数をコピーする。
3．A列を「切り取り」を実行し、C列を選択した状態から列の挿入で「切り取ったセルの挿入」を実行して目的の並列に調整する。

数式に使用しているIFERROR関数はExcel 2007以降で使用可能ですがExcel 2003以前のバージョンでは代替の関数で処理する必要があります。
また、提示した数式は元データが最大9000行までを対象にしていますので実情に合わせて数式を変更してください。

回答No.2

ryo_ Deathscythe（@Deathscythe）

やっと思いついた^^;

まずShhet1のD列にA列をコピーします
次にE列に通し番号を記入
Fは一度飛ばしてG~I列にVLOOKUP関数を入れます
G1=IFERROR(E1,$B$1:$D$500,2,FALSE),"")
H1=IFERROR(E1,$B$1:$D$500,3,FALSE),"")
I1=IFERROR(E1,$B$1:$D$500,4,FALSE),"")
※500はテキトウなので記載範囲の下限に変えてください

さらにF列にIF関数
F1=IF(G1="","",1)
F2=IF(G2="","",COUNT(F$1:F1)+1)
F2を下限までコピーします

で、Sheet2はA列を新たに作って(1行ずらして)再度通し番号　※記載さえあれば見えなくて良い
さらにB～D列にVLOOKUP関数
B1=IFERROR(A1,Sheet1!$F$1:$I$500,2,FALSE),"")
C1=IFERROR(A1,Sheet1!$F$1:$I$500,3,FALSE),"")
D1=IFERROR(A1,Sheet1!$F$1:$I$500,4,FALSE),"")

コレで完成

お礼

ありがとうございました。

回答No.1

tom04

こんにちは！
Sheet1のデータをSheet2に表示するとします。
Sheet1のB列に同順位があっても表示できるようにしてみました。

↓の画像で左側がSheet1・右側がSheet2とします。

Sheet2のA1セルに
=IFERROR(SMALL(Sheet1!B:B,ROW(A1)),"")

B1セルに
=IF($A1="","",INDEX(Sheet1!$A$1:$C$1000,SMALL(IF(Sheet1!$B$1:$B$1000=$A1,ROW($A$1:$A$999)),COUNTIF($A$1:$A1,$A1)),COLUMN(A1)*2-1))
これは配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　Sheet2のB1セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。
B1セルを隣のC1セルまでコピー！
最後にA1～C1セルを選択　→　C1セルのフィルハンドルで下へコピーすると
画像のような感じになります。m(_ _)m

お礼

ありがとうございました。