Excel2000：既存一覧のデータを任意に配列しなおすPart2

質問

以前、こちらで質問し、良回答いただきましたが、再び下のように修正したいのです。

○下記、元一覧に「分類コード」の新しい列を挿入するので、修正後一覧には、「分類コード」をキーにした昇順の並べ替えを行った表示にする

ちなみに、設定した関数は、下の過去ログの↓、No.5の方の回答をそのまま使用しています。

http://oshiete1.goo.ne.jp/kotaeru.php3?q=1273783

＜元一覧＞
　　　A　　　　　　B　　C　　D　　　　E　　　F　　　　　 G
1　部門CD　 部門　ID　内容　カテゴリ　分類　　(分類CD)
2　130　　　　経理　 1　　あ　　　ア　　　　a　　　　　010
3　130　　　　経理　 2　　い　　　イ　　　　b　　　　　110
4　130　　　　経理　 3　　う　　　ア　　　　a　　　　　020
5　110　　　　総務　 4　　え　　　ウ　　　　a　　　　　010
6　110　　　　総務　 5　　お　　　イ　　　　d　　　　　250
7　550　　　　営業　 6　　か　　　ウ　　　　c　　　　　370
8　550　　　　営業　 7　　き　　　オ　　　　c　　　　　370
9　550　　　　営業　 8　　く　　　 オ　　　　c　　　　　320

＜修正後一覧＞
　　　 A　　　　　 B　 C　D　 E　 F　　G
1　(分類CD)　分類　ア　イ　ウ　エ　オ
2 　 010　　　　　a　　あ　　　え
3 　 020　　　　　 　　う
4 　 110　　　　　b　　　　い
5　　250　　　　　d　　　　お
6　　320　　　　　c　　 　 　　　　　　　 く
7 　 370　　　　　 　　　　　 　 か　　　き

(　)が今後新しく入れる列です。
※元一覧には既にあります。
※「分類」が複数ある場合、一番上に配列されるセル以外、下のセル全ては非表示となる設定となっています。

過去ログで、No.5の方の回答の関数を壊さずに、どうやったら「分類CD」を昇順に並べ替えられますでしょうか。

回答No.4

moon_piyo · ベストアンサー

No2です
No3の「今回は...」以降の説明のなかででてくる「部門コード」は「分類CD」の間違いです。ごめんなさい

回答No.3

moon_piyo

No2です。こんにちは

H2, K2, N2に入れる式は表記のとおりでよいですよー
たとえば
H2: =VALUE(G2)+ROW()/1000000
です..

前回のH2とまるで違うのは並べる規則を変えているからです

前回は同じ部門コードをもつ行がひとかたまりになるように、また部門CDとしては上から出現順に並ぶような式になっています

今回は分類コードを3桁の整数とみなして昇順にソートすることにしました。ただこのとき重複するような値があると，厄介なので、同じ部門CDではあってもユニークな値になるようにROW()を使います
1000000で割っているのは整数部(部門コードを数値化したもの)にはあくまでも影響を与えないようにするためです
excelは65536行までですので，65536より大きな値でrow()を割れば0以上1未満の範囲でユニークな値を求めることができます
割る値は100000でも99999でもソート結果には影響ありません

お礼

moon_piyoさん、補足の回答ありがとうございます。

無事、分類コードでソート出来ました。
ただ、私の説明不足もあり、修正が必要なかった部分がありました。
そこは、以下の部分です。

＞K2: =INDEX(G$2:G$9,MATCH(ROW()-1,$I$2:$I$9,0))

これだと分類コードが分類名の代わりに表示されてしまうので、前回のままの数式にしてあります。
※これに気づくのに1時間要しました(^^;)

詳しい説明と根気のいる数式記述を何度もしていただきまして、本当にありがとうございました。
これで少しはお勉強も進んだ・・・かな？(笑)

回答No.2

moon_piyo

前No5です。こんにちは

えーと過去ログを基準にしまして

H2: =VALUE(G2)+ROW()/1000000
K2: =INDEX(G$2:G$9,MATCH(ROW()-1,$I$2:$I$9,0))
N2: =INDEX(F$2:F$9,MATCH(ROW()-1,$I$2:$I$9,0))

という修正をおこなってみてください。期待どおりの動きかどうかわからないですが...

それから、この場をお借りしまして、過去ログについて各列の補足をさせてください

H列
基準となる項目をある規則で数値化した値。この値にてソートします。前回は文字列でしたので
2行目から自分のセルの行までに自分と同じ内容のセルが1つだけあれば(つまり値が最初に出現した場合) ROW()
2つ以上あるなら同じ内容をもつ行のH列の値の整数部の平均(つまり最初に出現した行のROW()になる) + row()/1000000
これで同じ文字列をもつ列は整数部は共通で小数部は出現順に増えていくような値になります

I列
H列の各値の順位をrank関数にもとめています

K列～N列
もとデータから必要な列を取り出しI列の順位順にソートしたテーブルをつくります。同じ順位を返す行が2つ以上あると正しくとりだせないのでH列はユニークになるようにしています

K列に横の項目(部門CD)
L列に縦の項目(カテゴリ)
M例にデータの内容
N列以降はその他必要な列です

P列
同じ部門CDかつ同じカテゴリのデータが自分も含め今までにいくつあったかをカウントとしています
同じ条件のデータが複数あった場合、自分はそのなかで何番目なのか決定しています

Q列
各部門CDの縦の幅を決める値です。縦方向にいくつの行が入るのかを数えます

R列
各部門コードの開始位置オフセットです
この値にP列の値を加えた値の行がデータを表示する場所になります

S列
部門CDがどの位置で切り替わったかどうかをチェックする値です。見出しを出力するのに使います

T列
現在行をいれています。条件にあてはまる行番号を取得するときに使います

ようやく結果の表示です...

V～W列
P列の値 + R列の値 = 現在行-1 という条件をみたすような行をさがし、見出し項目を出しています。

X～AB列
P列の値 + R列の値 = 現在行-1 かつ カテゴリの内容が一致するという行をさがし対応するM列の値を出しています

補足

moon_piyoさん、再び回答いただけて感謝です！

前回の質問の時には、少々違う設定を求められていたので、ポイントも次点にしてしまいましたが、後でmoon_piyoさんの回答の方がExcelには都合が良いと、やっとウチのおじ様も気づいて、そのまま使用させていただいてます。
以前はホントにありがとうございました。

ところで、前回の回答の解説もしていただき、関数の勉強中の私にはとてもありがたいのですが、今回、「前回の回答をふまえて」との回答ですが、以下の場所はどこをどう直せば良いのでしょうか。

＞H2: =VALUE(G2)+ROW()/1000000

前回は、以下の式でしたよね。

H2: =IF(COUNTIF(F$2:F2,F2)=1,ROW(),SUMPRODUCT((F$1:F1=F2)*INT(H$1:H1))/SUMPRODUCT((F$1:F1=F2)*1)+ROW()/100000)

色々試して、H列の修正後記述をそのまま上書きした場所が、下の場所になります。
○(F$1:F1=F2)*1)+ROW()/100000
○+ROW()/100000
※最後の方の

解説していただいてるのですが、いまだ細かい修正までは出来ず困っています。
もう少しお付き合いいただけたら嬉しいです。

回答No.1

fly_moon

過去ログ見ました。
あの関数群を解読するのはかなりな手間です。
検証されたなら、その検証結果をのせて頂くと、
もう少し、みなさん回答できるのでは？

失礼ですが、もし検証なさっていないようなら、
この際、関数でなくVBAでも結果がよければよいのでは？
関数はVBAより、後のメンテナンスが大変です。この後もどんどんバージョンアップの可能性はあると思いますので、もし関数を熟知していらっしゃらなくて、このようにバージョンアップのとき他人に頼られるのであれば、やはりVBAをご使用になったほうがよろしいかと思います。

お礼

fly_moonさん、アドバイスありがとうございます。

＞このようにバージョンアップのとき他人に頼られるのであれば

そうなんですよ。
こういうサイトで、いちいちよそ様に聞かないと仕事にならないなんて、自分でもイヤだし能率は下がるしで、改善しないといけないんですが、なにぶん、急にVBAだの関数だのを使う仕事に就いてしまい、今ひたすら猛勉強中です。
・・・でも、人間、そうそうすぐには使えるようにはなりません(-_-;)

ただ、どちらも少々かじってる最中なので、解説していただけると、それを応用も出来るかとは思います。
それまで、もう少し助けていただきたいと思います。