早いエクセル編集の仕方

質問

図１の表を図２に並べ替えをしたいです。

当方初心者で、テーブルがよくわかっておりません。

並べ替えをした後、列を増やしてまだ編集が必要です。
フィルター機能は最終的に使うつもりですが、とにかく並べ替えが必要です。

どうすれば早くこのようになるか教えていただければ有難いです。
よろしくお願いいたします。

回答No.2

kagakusuki · ベストアンサー

>Sheet２のA3セルへの式の貼り付けで、
>『指定した数式でしようされている入れ子のレベル数が、現在のファイル形式で許容される上限を超えているため数式を入力できません』
>となってしまいます。。
>今家のpcで2007だからでしょうか。。

　いいえ、もし使用しているExcelのバージョンのせいであれば、

>入力した数式は正しくありません。

>・「ヘルプ」をクリックすると、数式を修正するための情報が表示されます。
>・関数の作成に関するヘルプを表示する場合は、[OK]をクリックし、[挿入]-[関数]をクリックします。
>・数式を入力しない場合は、等号（=）やマイナス記号（-）を使用しないか、又は入力した値の先頭にシングル　クォーテーションマーク（'）を入力します。

と表示される筈です。
　警告表示に「現在のファイル形式で」とある様に、そのファイルの保存形式がExcel2007以降のバージョン用の.xlsx形式ではなく、Excel2003以前バージョン用の.xls形式で保存されたファイルであったためだと思われます。

>得意先を縦に貼り付けるのはできないでしょうか。

　その場合、どの様な順番で並べるのでしょうか?
　得意先順で並べ、同じ得意先の案件同士であれば品番順に、同じ品番の案件同士であればサイズ順に並べれば宜しいのでしょうか?
　もしそれで宜しければ以下の様な方法となります。

　どのデータが何というシートの何というセル番号のセルに入力されているのか情報がありませんので、仮の話として図1の表が存在しているシートがSheet1であり、その中で「得意先」と入力されているのがA1セル、得意先が「aaa1」で、品番が「123123」となっている行が３行目、「39」というサイズが入力されているセルがC2セルであるものとします。
　又、並べ替え後の表はSheet2上に表示させ、その中のA1セルに「得意先」、B1セルに「品番」、C1セルに「サイズ」、D1セルに「数量」と入力されていて、並べ替えたデータを２行目以下に表示させるものとします。
　また、Sheet3を作業用のシートとして使用するものとします。

　まず、Sheet3のB3セルに次の関数を入力して下さい。

=IF(OR(INDEX(Sheet1!$A:$A,ROW())="",INDEX(Sheet1!$B:$B,ROW())=""),"",LEN(INDEX(Sheet1!$A:$A,ROW())&INDEX(Sheet1!$B:$B,ROW())))

　次に、Sheet3のC1セルに次の関数を入力して下さい。

=MAX($B:$B)

　次に、Sheet3のC3セルに次の関数を入力して下さい。

=IF(OR(INDEX(Sheet1!$A:$A,ROW())="",INDEX(Sheet1!$B:$B,ROW())=""),"",INDEX(Sheet1!$A:$A,ROW())&REPT(CHAR(6),$C$1-$B3)&INDEX(Sheet1!$B:$B,ROW()))

　次に、Sheet3のD3セルに次の関数を入力して下さい。

=IF($C3="","",(COUNTIF($C:$C,"<"&$C3)-COUNTIF($C:$C,"<*")+1)*100000)

　次に、Sheet3のE3セルに次の関数を入力して下さい。

=IF(OR($D3="",INDEX(Sheet1!$C:$J,ROW(),COLUMNS($E:E))=""),"",$D3+COLUMNS($E:E))

　次に、Sheet3のA3セルに次の関数を入力して下さい。

=IF(OR(ISNUMBER(A2),ROW()=ROW(A$3)),IF(COUNTIF($E:$L,">"&SUM(A2)),LARGE($E:$L,COUNTIF($E:$L,">"&SUM(A2))),""),"")

　次に、Sheet3のE3セルをコピーして、Sheet3のF3～L3の範囲に貼り付けて下さい。
　次に、Sheet3のB3～L3の範囲をコピーして、Sheet3のB列～L列の4行目以下に(Sheet1の表の行数を上回るのに十分な行数となるまで)貼り付けて下さい。
　次に、Sheet3のA3セルをコピーして、Sheet3のA4以下に(「得意先の数」×「品番の種類の数」×「サイズの種類の数」を上回るのに十分な行数となるまで)貼り付けて下さい。

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

=IF(ISNUMBER(Sheet3!$A3),INDEX(Sheet1!$A:$B,MATCH(ROUNDDOWN(Sheet3!$A3,-5),Sheet3!$D:$D,0),COLUMNS($A:A)),"")

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

=IF(ISNUMBER(Sheet3!$A3),INDEX(Sheet1!$C$2:$J$2,MOD(Sheet3!$A3,100000)),"")

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

=IF(ISNUMBER(Sheet3!$A3),SUMIF(Sheet3!$E:$L,Sheet3!$A3,Sheet1!$C:$J),"")

　次に、Sheet2のA2セルをコピーして、Sheet2のB2セルに貼り付けて下さい。
　次に、Sheet2のA2～D2の範囲をコピーして、Sheet2のA列～D列の3行目以下に(「得意先の数」×「品番の種類の数」×「サイズの種類の数」を上回るのに十分な行数となるまで)貼り付けて下さい。

　以上です。

お礼

Excel2007以降のバージョン用の.xlsx形式ではなく、Excel2003以前バージョン用の.xls形式で保存されたファイルであったためだと思われます。

仰る通りでした。
この書式に得意先コードを足したりしたかったのですが、かなり重たいファイルになったので、一度この形になったものを値で貼り付け編集することができました。

本当に有難うございました。

回答No.1

kagakusuki

どのデータが何というシートの何というセル番号のセルに入力されているのか情報がありませんので、仮の話として図1の表が存在しているシートがSheet1であり、その中で「得意先」と入力されているのがA1セル、得意先が「aaa1」で、品番が「123123」となっている行が３行目、「39」というサイズが入力されているセルがC2セルであるものとします。
　同様に図2の表が存在しているシートがSheet2であり、その中で「品番」と入力されているのがA1セル、品番が「123123」で、サイズが「40」となっている行が３行目、「aaa1」という得意先が表示されているセルがC2セルであるものとします。
　また、Sheet3を作業用のシートとして使用するものとします。

　まず、Sheet3のA3セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$A:$A,ROW())="","",IF(COUNTIF(Sheet1!$A$2:INDEX(Sheet1!$A:$A,ROW()-1),INDEX(Sheet1!$A:$A,ROW())),"",ROW()))

　次に、Sheet3のB3セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$B:$B,ROW())="","",(COUNTIF(Sheet1!$B:$B,"<="&INDEX(Sheet1!$B:$B,ROW()))+COUNT(Sheet1!$B:$B)*ISTEXT(INDEX(Sheet1!$B:$B,ROW())))*100000)

　次に、Sheet3のC3セルに次の関数を入力して下さい。

=IF(OR(INDEX(Sheet1!$B:$B,ROW())="",INDEX(Sheet1!$C:$J,ROW(),COLUMNS($C:C))=""),"",$B3+COLUMNS($C:C))

　次に、Sheet3のC3セルをコピーして、Sheet3のD3～J3の範囲に貼り付けて下さい。
　次に、Sheet3のA3～J3の範囲をコピーして、Sheet3のA列～J列の4行目以下に貼り付けて下さい。

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

=IF(COLUMNS($C:C)>COUNT(Sheet3!$A:$A),"",INDEX(Sheet1!$A:$A,SMALL(Sheet3!$A:$A,COLUMNS($C:C))))

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

=IF(AND(A2="",ROW()-ROW($A$3)),"",IFERROR(INDEX(Sheet1!$B:$B,MATCH(FLOOR(LARGE(Sheet3!$C:$J,COUNTIF(Sheet3!$C:$J,">"&IF(ROW()-ROW($A$3),INDEX(Sheet3!$B:$B,MATCH($A2,Sheet1!$B:$B,0))+MATCH($B2,Sheet1!$C$2:$J$2,0),0))),100000),Sheet3!$B:$B,0)),""))

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

=IF($A3="","",INDEX(Sheet1!$C$2:$J$2,MOD(LARGE(Sheet3!$C:$J,COUNTIF(Sheet3!$C:$J,">"&IF(ROW()-ROW($A$3),INDEX(Sheet3!$B:$B,MATCH($A2,Sheet1!$B:$B,0))+MATCH($B2,Sheet1!$C$2:$J$2,0),0))),100000)))

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

=IF(OR($A3="",$B3="",C$2=""),"",IF(COUNTIFS(INDEX(Sheet1!$C:$J,,MATCH($B3,Sheet1!$C$2:$J$2,0)),">0",Sheet1!$A:$A,C$2,Sheet1!$B:$B,$A3),SUMIFS(INDEX(Sheet1!$C:$J,,MATCH($B3,Sheet1!$C$2:$J$2,0)),Sheet1!$A:$A,C$2,Sheet1!$B:$B,$A3),""))

　次に、Sheet2のC2～C3のセル範囲をコピーして、Sheet2の２行目～３行目のC列セルより右側にあるセル範囲に貼り付けて下さい。
　次に、Sheet2の３行目全体をコピーして、Sheet2の4行目以下に貼り付けて下さい。

　以上です。

補足

回答ありがとうございます。
Sheet２のA3セルへの式の貼り付けで、
『指定した数式でしようされている入れ子のレベル数が、現在のファイル形式で許容される上限を超えているため数式を入力できません』
となってしまいます。。
今家のpcで2007だからでしょうか。。

図１から図２の書式を作る際、
得意先A列
品番B列
サイズC列
個数D列
得意先,品番,サイズ,個数
aaa1,123123,39,0
aaa1,123123,40,1
aaa1,123123,41,2
aaa1,123123,42,2
aaa1,123123,43,1
aaa1,123123,44,0
aaa1,123123,45,0
aaa1,123123,46,0
bbb1,123123,39,0
bbb1,123123,40,0
bbb1,123123,41,1
bbb1,123123,42,3
........
という感じにはすぐできます。
その流れで得意先を縦に貼り付けるのはできないでしょうか。
再度回答いただけたら有難いです。

よろしくお願いいたします。