入力された数字のうち、１０台の数字を若い順に別のセルに自動入力する方法 その２

以前、質問した際、下記URLのとおり、回答をいただき、解決したと思ったのですが、問題が起きましたので、再度、質問させていただきます。

以前の質問と回答のURL：
https://oshiete.goo.ne.jp/qa/11150170.html

回答を頂いたとおり、数式を入力すると、上手く結果が表示されたのですが、その後、A列の左に列を挿入すると、添付画像のとおり、間違った結果が表示されてしまいます。

数式を変更する必要があるようです。どのように変更すればよいか、回答願います。

No.3 ベストアンサー 回答者： tom04 回答日時： 2019/06/06 21:31

こんばんは！

前回のサイトのNo.2で回答した者です。
本来であれば数式が入っている列（または行）がある場合は極力行・列の挿入はしない方が良いと思いますが・・・

画像ではA列が挿入されていますが、A列はデータの対象外でよいのでしょうか？
とりあえず画像の配置だとX1～X6の6列を常に検索対象としてみました。

行挿入前のG2セルに
=IFERROR(SMALL(IF(INT($A2:$F2/10)=1,$A2:$F2),COLUMN()-COLUMN($F1)),"")

前回同様配列数式なので、Ctrl＋Shiftキーを押しながらEnterキーで確定してください。
G2セルのフィルハンドルで列・行方向にコピー！

これでA列を挿入しても検索対象が1列ずつずれるはずです。

※　画像では列方向に昇順のデータになっていますが、
バラバラでも小さい順に表示されます。m(_ _)m

この回答へのお礼

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

お礼日時：2019/06/07 17:42

No.2 回答者： fujillin 回答日時： 2019/06/06 12:42

こんにちは

前回回答者です。


＞間違った結果が表示されてしまいます。
どちらの式を利用しているのか不明ですが、前者の式を利用しているのならエクセルの自動修正で問題は出ないはずと思いますので、後者の式を利用なさっているものと想像しました。
出現順に抽出するという内容であるため、間接的な参照式になっているので、エクセルの自動修正では対応しきれない部分が残ってしまいます。

＞どのように変更すればよいか、回答願います。
セル範囲「$A2:$F2」が、エクセルによって「$B2:$G2」に修正されていると思いますが、このうちの一番最初のものだけ「$A2:$G2」とすれば同じ結果になるはずです。
（正しい意味合いで修正するならば、範囲はそのままにしておいてインデックス値を1減算するのが良いですが、上記の修正の方が簡単なので・・・）

なお、値の範囲の判定に関して、前回のNo4様の式がコンパクトですので、そちらを拝借して書き直してみると、H2セルの式は
=IFERROR(INDEX($A2:$G2,AGGREGATE(15,2,INDEX(COLUMN($B2:$G2)/(INT($B2:$G2/10)=1),,),COLUMN(A1))),"")
となるものと思います。

※ たいして長い関数式ではないので、分解して意味を理解しておけば、応用が効くようになることと思います。

この回答へのお礼

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

お礼日時：2019/06/07 17:40

No.1 回答者： Ogre7077 回答日時： 2019/06/06 11:01

> A列の左に列を挿入すると

セル参照が壊れている様です。
おそらくは COLUMN(A1) あたりかと思いますが...

通常は操作に合わせてセル参照を自動修正してくれる筈ですが、
複雑な数式を使用すると追従しきれない場合があるようです。

テーブルでの構造化参照を使って、セル参照を排除しては如何でしょうか。

操作例

B1:G5 の範囲でテーブルを作成し「テーブル1」とする
H2:K5 の範囲に、以下の配列数式を入力し Ctrl+Shift+Enter で入力
10台1番目 =IFERROR(SMALL(IF(FLOOR(テーブル1[@]/10,1)=1,テーブル1[@]),1),"")
10台2番目 =IFERROR(SMALL(IF(FLOOR(テーブル1[@]/10,1)=1,テーブル1[@]),2),"")
10台3番目 =IFERROR(SMALL(IF(FLOOR(テーブル1[@]/10,1)=1,テーブル1[@]),3),"")
10台4番目 =IFERROR(SMALL(IF(FLOOR(テーブル1[@]/10,1)=1,テーブル1[@]),4),"")

参考

テーブル
https://support.office.com/ja-jp/article/excel-% …
テーブルでの構造化参照
https://support.office.com/ja-jp/article/excel-% …
配列数式
https://support.office.com/ja-jp/article/%E9%85% …

この回答へのお礼

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

お礼日時：2019/06/07 17:40