マクロ無しで転記と同時に自動並べ替え(種明かし)

マクロ無しで転記と同時に自動並べ替え(種明かし)

akashi-keirin.hatenablog.com

の種明かし……ですが、最初に断っておきます。

怒らないでくださいよ!

いや、ホントに。

おわび

実は、この画面。

f:id:akashi_keirin:20190330073510j:plain

一部、文字色をセルの背景と同じ(要するに、今回の場合は白)にしているところがあります。

その「一部」を黒にしてみましょう。

f:id:akashi_keirin:20190330073514j:plain

ははは。

まるで、一般大衆向けのチラシに、まるで一般大衆向けでない「‰」(パーミル)という単位を用いたグラフを掲載する某政党のようなことをしてしまいましたね!

種明かし

上掲画像で、察しの良い方はお気づきになったと思いますので、これで種明かしは終了! ……なんてことをすると、

察しの悪い方

が困ると思うので、続けます。

まず、画像のE列には、単なる数値が入っているのではありませぬ。

f:id:akashi_keirin:20190330073517j:plain

このように、数式が入っている。

たとえば、画像に表示されている2行目なら、

=RANK(F2,$F$2:$G$7,1)

こんなの。つまり、RANK関数というやつですな。

で、I列には、単に上から順番に数字が入っている。

あと、J列には、

f:id:akashi_keirin:20190330073520j:plain

こんな数式。

=IFERROR(VLOOKUP(I2,$E$2:$G$7,2,FALSE),"")

つまり、VLOOKUP関数(IFERROR関数も使っていますが)。

K列にも同じようにVLOOKUP関数を用いて、左側のE~G列の表から値を引っ張ってくるようにしている。

はい。というわけで、Kou (id:ExcelLover) さん、ご名答~!

……で終わろうと思ったのですが、別解を寄せてくださる方がありました。

別解

id:Z1000S さん曰く、

J2
=IF(F2<>"",SMALL(F$2:F$11,ROW()-1),""
K2
=IF(J2<>"",VLOOKUP(J2,F$2:G$11,2,FALSE) & "","")
3行目以降はフィル で出来そう。

とのこと。

ROW関数はともかく、SMALL関数なんて使ったことなかったから、調べてみた。

SMALL関数

[配列]の範囲のなかで、小さいほうから数えた[順位]の値を求めます。

入力方法と引数
SMALL(配列, 順位)
配列 検索範囲をセル範囲または配列で指定します。文字列や論理値の入力されているセル、空白のセルは無視されます。
順位 求めたい値の順位を指定します。小さいほうから数えて何番目かという値を指定します。
できるネットより

おお! これだと順位を指定するI列も、RANK関数もいらねーじゃん!

SUGEEEEEE!!!!!!!!

つまり、画像のJ2セルに

=IF(F2<>"",SMALL(F$2:F$7,ROW()-1),"")

が入っているとすると、ROW()は「2」を返すので、ROW()-1の値は「1」。SMALL関数によって、F$2:F$7の範囲内の「小さいほうから数え」て「1」番目の値(すなわち、一番小さな値)が返ることになるわけだ。

ROW()の値は下に行くほど増えていくので、順に2番目に小さな値、3番目に小さな値……と並ぶことにより、結果的に並べ替えられた値が並ぶ……。

いやあ、勉強になった! ありがとう、id:Z1000S さん!

おわりに

ちなみに、Kou (id:ExcelLover) さん、id:Z1000S さん、ともに言及されていた、〈同じ日付がある場合〉ですが、ともにid:Z1000S さんが仰せのように、

f:id:akashi_keirin:20190330073525g:plain

こうなりますw

私が職場で見た用例の場合、同じ日付が出てくることがあり得ないようなタイプの帳票だったので、これで良かった、ということです。

あと、〈一気に値が書き込まれた場合〉ですが、

f:id:akashi_keirin:20190330073913g:plain

当然、何の問題もなく動作します。