データの並び替えについて

質問

添付図左【元データ】のような系列ごとに並んだデータ（1つの系列に何種類かデータがあります）を、右図の【変更後】のように種類順で並び替えるにはどのようにするのが良いですか？
教えてください。
EXCEL2010とＥＸＣＥＬ2003を使用しています。
よろしくお願いいたします。

回答No.3

kagakusuki · ベストアンサー

回答番号:ANo.2です。

　今仮に、元データの表と、変更後の表が存在しているシートはSheet1であるものとし、Sheet2のA列とB列を作業列として使用するものとします。
　又、種類を表す文字列は、同じものが複数現れる事は無いものとします。

　まず、Sheet2のA1セルに次の数式を入力して下さい。

=IF(Sheet1!B5="","",VALUE(SUBSTITUTE(Sheet1!B5,"-",)))

　次に、Sheet2のA1セルをコピーして、Sheet2のA1～B10の範囲に貼り付けて下さい。

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

=IF(ROWS($5:5)>COUNT(Sheet2!$A:$B),"",TEXT(SMALL(Sheet2!$A:$B,ROWS($5:5)),"0-0-0"))

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

=IF($F5="","",INDEX($A:$A,MATCH($F5,OFFSET($B:$B,,COUNTIF($C:$C,$F5)),0)))

　次に、Sheet1のF5～G5の範囲をコピーして、同じ列の6行目以下に貼り付けて下さい。

　以上です。

　尚、上記の数式は、種類欄に同一の文字列が複数存在する場合や、元データの表の幅が3列を上回る様な場合には、対応しておりませんから、もし、その様な事も有り得る場合には、補足等で御知らせ下さい。（元データの表が15行目以下にまで続いて行く場合には、単純にSheet2のA1セルのコピーを貼り付ける範囲を、下方に向かって延長するだけで対応出来ます）

補足

投稿ありがとうございました。
上記のとおりシートに入力してみましたが、F5の列のセル・G5の列のセルともに（変更後の表すべて）空白になってしまいます...。
あと、実際に使用するときには元データの表が10列程度に設定しています。
申し訳ありませんが、解決方法があればどうか教えていただけないでしょうか。
よろしくお願いいたします。

回答No.5

kagakusuki

ANo: 3の数式は、あくまで3列の表に対応しているもので、10列の表には対応してはおりませんし、ちょっとした応用だけで、対応させる事が可能となるとも思えません。

　実際に使用する際に、元データの表が、何という名前のシートに存在していて、データが入力されている部分は、最大で何列の何行目から何列の何行目にかけての範囲なのかと言う事と、変更後の表が、何という名前のシートに存在していて、何列の何行目から、並べ替え結果を表示させれば良いのかが不明なため、
今仮に、Sheet1のA4セルに「系列」と入力されていて、Sheet1の5行目以下の、A列には系列の名前、B列～K列の10列には種類の名前が入力されていて、Sheet3のA列～J列を作業列として使用して、Sheet2のA列とB列の5行目以下に、変更後の表を表示するものとします。

　まず、Sheet3のA1セルには、ベストアンサーにおいて、Sheet2のA1セルに入力したものと同じ数式である

=IF(Sheet1!B5="","",VALUE(SUBSTITUTE(Sheet1!B5,"-",)))

という数式を入力して下さい。
　次に、Sheet3のA1セルをコピーして、Sheet3のA1～J1の範囲に貼り付けて下さい。
　次に、Sheet3のA1～J1の範囲をコピーして、Sheet3の2行目以下に貼り付けて下さい。

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

=IF(ROWS(A$5:A5)>COUNT(Sheet3!$A:$J),"",TEXT(SMALL(Sheet3!$A:$J,ROWS(A$5:A5)),"0-0-0"))

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

=IF($A5="","",INDEX(Sheet1!$A:$A,MATCH($A5,OFFSET(Sheet1!$A:$A,,SUMPRODUCT((COLUMN(Sheet1!$B$4:$K$4)-COLUMN(Sheet1!$A$4))*(COUNTIF(OFFSET(Sheet1!$A:$A,,COLUMN(Sheet1!$B$4:$K$4)-COLUMN(Sheet1!$A$4)),$A5)))),0)))

　次に、Sheet2のA5～B5の範囲をコピーして、同じ列の6行目以下に貼り付けて下さい。

　これで、元の表がA列～K列の範囲内にある場合には対応できます。

　尚、元の表がK列を超えて、制限無く右側に延長する可能性がある場合や、元の表を変更する際に、セルの切り取りや削除や挿入が行われる可能性がある場合には、数式を以下の様に変更されると良いと思います。
　但し、Sheet2のB5セルの数式は、行数に比例して計算時間が長くなります。
　又、Sheet1の元の表以外の部分には、種類の部分名前と同じ内容の文字列データが存在したりはしない様にして下さい。（例えば、元の表と変更後の表を１つのシートに纏めたりはしないで下さい）

【Sheet3のA1セルの数式】
=IF(OFFSET(Sheet1!$A$4,ROWS($A$1:A1),COLUMNS($A$1:A1))="","",VALUE(SUBSTITUTE(OFFSET(Sheet1!$A$4,ROWS($A$1:A1),COLUMNS($A$1:A1)),"-",)))

【Sheet2のA5セルの数式】
=IF(ROWS(A$5:A5)& gt;COUNT(OFFSET(Sheet3!$1:$1,,,MATCH("゛",Sheet1!$A:$A,-1)-ROW(Sheet1!$A$4))),"",TEXT(SMALL(OFFSET(Sheet3!$1:$1,,,MATCH("゛",Sheet1!$A:$A,-1)-ROW(Sheet1!$A$4)),ROWS(A$5:A5)),"0-0-0"))

【Sheet2のB5セルの数式】
=IF($A5="","",INDEX(Sheet1!$A:$A,SUMPRODUCT(ROW(INDIRECT(ROW(Sheet1!$A$4)& amp;":"&MATCH("゛",Sheet1!$A:$A,-1)))* (COUNTIF(OFFSET(INDIRECT("Sheet1!1:1"),ROW(INDIRECT(ROW(Sheet1!$A$4)&":"&MATCH("゛",Sheet1!$A:$A,-1)))-1,$A5)))))

お礼

kagakusiki様

本当にありがとうございます。このような複雑な関数になるとは思っていなくて...。
元データの表をkagakusikiさんに教わった数式に合うように、『3列の表に加工するか...と』浅はかな考えをしておりました。

また、変更の場合の数式まで教えていただけて...ただただ感謝です!!

上記のとおり入力してみたら上手くいったので、実際のデータでチャレンジしてみようと思います。

長い間時間を取らせてしまい申し訳ありませんでした。
今後のご活躍を期待しております。

　　　　　　izavera　より

回答No.4

kagakusuki

>上記のとおりシートに入力してみましたが、F5の列のセル・G5の列のセルともに（変更後の表すべて）空白になってしまいます...。

　ANo.3の数式は、投稿前に動作を確認済みで、再チェックも致しましたが、投稿時の入力ミスも見つける事が出来ませんでした。

>あと、実際に使用するときには元データの表が10列程度に設定しています。

と言う事は、元データの表は少なくとも、A列～J列の範囲を占めていて、変更後の表もF4～G14の範囲には無い訳ですね。
　おそらく、その辺りが表示されない原因だと思われますので、元データの表が、何という名前のシートに存在していて、データが入力されている部分は、最大で何列の何行目から何列の何行目にかけての範囲なのかと言う事と、変更後の表が、何という名前のシートに存在していて、何列の何行目から、並べ替え結果を表示させれば良いのかを、御知らせ願います。

お礼

ただただ...ありがとうございました。
私一人では全然解決できなくて...。

"kagakusiki"さんの今後の活躍を祈っています。
お世話になりました。

補足

ありがとうございます。

おかしいな...と思いつつ、Sheet2のA1に"kagakusiki"さんの数式をコピー後、貼り付け直してみたら今までTRUEと表示されていたものが数値（115　etc...）になりました。どうやら貼り付けの形式を間違えていたようです。
その後、Sheet1のB5~C14の範囲の入力文字が大文字で入力されていたので（投稿用にあわてて入力していました）小文字に入力しなおしました。

すると...成功しました！！
ただ私の入力ミスだったようです。たくさん時間を取らせてしまい申し訳ありませんでした。

実際のデータは、種類のセルが10個（ex．B5~K5）に設定しています。
"kagakusiki"さんに教えていただいた数式をよく理解して、自分で設定してみようと思います。

本当に...どうもありがとうございました。

回答No.2

kagakusuki

添付されている写真が小さ過ぎる上、ピンボケであるため、どの様なデータが入力されているのかが、写っていません。
　そのため、どの様に並べ替えているのかも判別出来ませんので、このままでは誰にも回答する事が出来ません。
　ですから、以下の様な形式で、各セルに、どの様な文字列や数値が入力されているのかを、補足欄に記入して頂く様、御願い致します。（補足のやり方は、ログインを済まされてから、回答の近くにある[補足する]ボタンをクリックして下さい）

A列
A4=■■
A5=○○
A6=○○
A7=○○
A8=○○
A9=○○
A10=○○
A11=○○
A12=○○
A13=○○
A14=○○

B列
B4=■■
B5=○-○-○
B6=○-○-○
B7=○-○-○
B8=（空欄）
B9=○-○-○
B10=○-○-○
B11=（空欄）
B12=○-○-○
B13=○-○-○
B14=（空欄）

C列
C4=■■
C5=（空欄）
C6=（空欄）
C7=○-○-○
C8=（空欄）
C9=（空欄）
C10=○-○-○
C11=（空欄）
C12=（空欄）
C13=○-○-○
C14=（空欄）

F列
F4=■■
F5=○-○-○
F6=○-○-○
F7=○-○-○
F8=○-○-○
F9=○-○-○
F10=○-○-○
F11=○-○-○
F12=○-○-○
F13=○-○-○
F14=○-○-○

G列
G4=■■
G5=○○
G6=○○
G7=○○
G8=○○
G9=○○
G10=○○
G11=○○
G12=○○
G13=○○
G14=○○

補足

初めての投稿で使用方法がよくわからず...大変失礼いたしました。
詳細は以下の通りです。

A列
A4=系列
A5=A1
A6=A2
A7=A3
A8=A4
A9=A5
A10=A6
A11=A7
A12=A8
A13=A9
A14=A10

B列
B4=種類
B5=1-1-5
B6=1-2-5
B7=1-1-4
B8=（空欄）
B9=1-1-1
B10=1-1-2
B11=（空欄）
B12=1-2-4
B13=1-2-2
B14=（空欄）

C列
C4=種類
C5=（空欄）
C6=（空欄）
C7=1-2-1
C8=（空欄）
C9=（空欄）
C10=1-1-3
C11=（空欄）
C12=（空欄）
C13=1-2-3
C14=（空欄）

F列
F4=種類
F5=1-1-1
F6=1-1-2
F7=1-1-3
F8=1-1-4
F9=1-1-5
F10=1-2-1
F11=1-2-2
F12=1-2-3
F13=1-2-4
F14=1-2-5

G列
G4=系列
G5=A5
G6=A6
G7=A6
G8=A3
G9=A1
G10=A3
G11=A9
G12=A9
G13=A8
G14=A2

どうぞよろしくお願いいたします。　　　（izabera）

回答No.1

O次郎（@stokesia）

コピーもしくはカットをして、順番にペーストしていけばOkです！