[[20240730170950]]　『1から10の数字』（さめさる）　ページの最後に飛ぶ

『1から10の数字』（さめさる）

質問失礼します。
下の図で基本的に下から（番号の低いものから）使わなければならない消耗品1から10があるとします。この消耗品の使う順番を間違えたときに、「間違えた時」の列に数字を入力することによって「間違えた後の使う順番」を表示するような関数やマクロの方法はないでしょうか？
自分なりにRANK関数やCOUNTIF関数を使ってみたのですが、なかなかすべてのパターンに対応するのが難しく諦めてしまいました。
見づらくて申し訳ありませんが「間違えた後の使う順番」の列の数字がずれてしまいました。よろしくお願いします。

品目　　　　使う順番　　間違えた時　　　間違えた後の使う順番
　
消耗品10　　　　10 　　　　 7　　　　　　　　 7
消耗品9　　　　　9 　　　　　　　　　　　　10
消耗品8　　　　　8 　　　　　　　　　　　　9
消耗品7　　　　　7 　　　　　　　　　　　　8
消耗品6　　　　　6　　　　　2 　　　　　　　　 2
消耗品5　　　　　5　　　　　1　　　　　　　　 1
消耗品4　　　　　4　　　　　　　　　　　　　　6
消耗品3　　　　　3　　　　　　　　　　　　　　5
消耗品2　　　　　2　　　　　　　　　　　　　　4
消耗品1　　　　　1　　　　　　　　　　　　　　3

＜使用 Excel：Excel2013、使用 OS：Windows11 ＞

パソコンで投稿したのですが、今スマホで確認したところ文字のズレがかなりありました。
また修正して投稿すると思いますが、ひとまずこのままとさせて頂きます。
（さめさる） 2024/07/30(火) 18:20:59

 　　　 A　　 B　　 C　　　 D 
 1　品目　順番　間違　最終順 
 2　　あ　　10　　 7　　　 7 
 3　　い　　 9　　　　　　10 
 4　　う　　 8　　　　　　 9 
 5　　え　　 7　　　　　　 8 
 6　　お　　 6　　 2　　　 2 
 7　　か　　 5　　 1　　　 1 
 8　　き　　 4　　　　　　 6 
 9　　く　　 3　　　　　　 5 
10　　け　　 2　　　　　　 4 
11　　こ　　 1　　　　　　 3

 D2
=IF(C2="",AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),COUNTBLANK(C$2:C2)),C2)
下方向・↓

（んなっと） 2024/07/30(火) 19:53:33

 上の式はB列が必ず下から上に大きくなっている場合です。
そうでないときは
　　　 A　　 B　　 C　　　 D 
 1　品目　順番　間違　最終順 
 2　　あ　　 5　　 7　　　 7 
 3　　い　　 9　　　　　　 9 
 4　　う　　10　　　　　　10 
 5　　え　　 7　　　　　　 6 
 6　　お　　 4　　 3　　　 3 
 7　　か　　 3　　　　　　 5 
 8　　き　　 1　　　　　　 2 
 9　　く　　 2　　　　　　 4 
10　　け　　 6　　 1　　　 1 
11　　こ　　 8　　　　　　 8

 D2
=IF(C2="",AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),COUNT(INDEX(0/(B$2:B$11>B2)/(C$2:C$11=""),))+1),C2)
下方向・↓

（んなっと） 2024/07/30(火) 20:34:14

んなっと様
ご丁寧なご回答ありがとうございます。
申し訳ありませんが/(1-countif からの内容が自分のレベルでは理解できませんでした。何度も申し訳ありませんが簡単で結構ですので解説していただけませんでしょうか？
（さめさる） 2024/07/31(水) 16:43:59

結果はどうでしたか?
（んなっと） 2024/07/31(水) 18:07:56

2つめのINDEX関数を使った方が私の求めていたものでした。おそらく動作的にも問題ないと思います。１つ目の方だと、間違の列に7だけを入力すると最終順の列に1がなくなると思います。大変申し訳ないのですが、2つ目の式は長いので自分には理解が追いつかないので、解説をして頂ければと思います。私、夜勤の仕事をしておりまして今日は返信できないと思いますが、何卒よろしくお願いします。
（さめさる） 2024/07/31(水) 18:41:53

 =IF(B2="","",IF(C2="",AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),SUMPRODUCT((B$2:B$11>B2)*(C$2:C$11=""))+1),C2))

 [1] COUNTIF でB列の番号がC列にあるかどうか調べて

 　　 B　　 C　　　　D 
 1　順番　間違　COUNTIF 
 2　　10　　 7　　　　0 ←COUNTIF(C$2:C$11,B$2:B$11)
 3　　 9　　　　　　　0 
 4　　 8　　　　　　　0 
 5　　 7　　　　　　　1 
 6　　 6　　 2　　　　0 
 7　　 5　　 1　　　　0 
 8　　 4　　　　　　　0 
 9　　 3　　　　　　　0 
10　　 2　　　　　　　1 
11　　 1　　　　　　　1

 [2] 1-COUNTIF で0と1を反転させる

 　　 B　C　　　　D　　　　　E 
 1　順番　　 COUNTIF　1-COUNTIF 
 2　　10　　　　　 0　　　　　1 ←1-COUNTIF(C$2:C$11,B$2:B$11)
 3　　 9　　　　　 0　　　　　1 
 4　　 8　　　　　 0　　　　　1 
 5　　 7　　　　　 1　　　　　0 
 6　　 6　　　　　 0　　　　　1 
 7　　 5　　　　　 0　　　　　1 
 8　　 4　　　　　 0　　　　　1 
 9　　 3　　　　　 0　　　　　1 
10　　 2　　　　　 1　　　　　0 
11　　 1　　　　　 1　　　　　0

 [3] B列/E列 で E列 が 1 のときはB列そのままの番号、
 　　　　　　　 E列 が 0 のときはエラーになって除外できるようにする

 　　 B　C　D　　　　　E　　　　　　　　F 
 1　順番　　　　1-COUNTIF　B列/(1-COUNTIF) 
 2　　10　　　　　　　　1　　　　　　　 10 ←B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11))
 3　　 9　　　　　　　　1　　　　　　　　9 
 4　　 8　　　　　　　　1　　　　　　　　8 
 5　　 7　　　　　　　　0　　　　　#DIV/0! ←除外
 6　　 6　　　　　　　　1　　　　　　　　6 
 7　　 5　　　　　　　　1　　　　　　　　5 
 8　　 4　　　　　　　　1　　　　　　　　4 
 9　　 3　　　　　　　　1　　　　　　　　3 
10　　 2　　　　　　　　0　　　　　#DIV/0! ←除外
11　　 1　　　　　　　　0　　　　　#DIV/0! ←除外

 [4] AGGREGATE(14,6,F列,) はエラーを無視して大きい方の番号から並べてくれる

 　　 B　C　D　E　　　　　　　　F　　　　　　　 G 
 1　順番　　　　　 B列/(1-COUNTIF)　AGGREGATE(14,6 
 2　　10　　　　　　　　　　　　10　　　　　　　10 ←AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),1)
 3　　 9　　　　　　　　　　　　 9　　　　　　　 9 ←AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),2)
 4　　 8　　　　　　　　　　　　 8　　　　　　　 8 ←AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),3)
 5　　 7　　　　　　　　　 #DIV/0!　　　　　　　 6 ←AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),4)
 6　　 6　　　　　　　　　　　　 6　　　　　　　 5 ←AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),5)
 7　　 5　　　　　　　　　　　　 5　　　　　　　 4 ←AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),6)
 8　　 4　　　　　　　　　　　　 4　　　　　　　 3 ←AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),7)
 9　　 3　　　　　　　　　　　　 3　　　　　 #NUM! 
10　　 2　　　　　　　　　 #DIV/0!　　　　　 #NUM! 
11　　 1　　　　　　　　　 #DIV/0!　　　　　 #NUM!

 [5] 最後にC列が空欄のところにG列を上から割り込ませていく

 　　 B　　 C　D　E　F　　　　　　　 G　　　 H 
 1　順番　間違　　　　　 AGGREGATE(14,6　最終順 
 2　　10　　 7　　　　　　　　　　　 10　　　 7 
 3　　 9　　　　　　　　　　　　　　　9　　　10 ←AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),1)
 4　　 8　　　　　　　　　　　　　　　8　　　 9 ←AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),2)
 5　　 7　　　　　　　　　　　　　　　6　　　 8 ←AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),3)
 6　　 6　　 2　　　　　　　　　　　　5　　　 2 
 7　　 5　　 1　　　　　　　　　　　　4　　　 1 
 8　　 4　　　　　　　　　　　　　　　3　　　 6 ←AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),4)
 9　　 3　　　　　　　　　　　　　#NUM!　　　 5 ←AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),5)
10　　 2　　　　　　　　　　　　　#NUM!　　　 4 ←AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),6)
11　　 1　　　　　　　　　　　　　#NUM!　　　 3 ←AGGREGATE(14,6,B$2:B$11/(1-COUNTIF(C$2:C$11,B$2:B$11)),7)

（んなっと） 2024/07/31(水) 21:08:50

度々ありがとうございます。書いて頂いた文書の[4]までは理解できました。わざとエラーにさせるというアプローチは初めてでとても勉強になります。今現在そのあとの文(COUNTやSUMPRODUCTから始まる部分)を調べているところです。理解できなかった場合は今日明日にまた返信致しますのでお時間ある時に教えて頂けないでしょうか？よろしくお願いします。
（さめさる） 2024/08/01(木) 14:38:49

んなっと様
やはり理解が難しそうなのでCOUNT関数とSUMPRODUCT関数の部分教えて頂けないでしょうか？
（さめさる） 2024/08/01(木) 20:41:08

 [追加]B列が7である行のSUMPRODUCTの意味
SUMPRODUCT((B$2:B$11>B5)*(C$2:C$11=""))
→(B列がB5の7より大きい)かつ(C列が空白)の個数
→B列の 9,8 の【2個】
SUMPRODUCT((B$2:B$11>B5)*(C$2:C$11=""))+1
→2+1
→B列の7は、C列が空白であるグループの大きい方から【3番目】とわかる

 　　 B　　 C　D　E　F　G　H　　　　　 I 
 1　順番　間違　　　　　　　　 SUMPRODUCT 
 2　　10　　 7　　　　　　　　　　　　　　
 3　　 9　　　　　　　　　　　　　　　　1 
 4　　 8　　　　　　　　　　　　　　　　2 
 5　　 7　　　　　　　　　　　　　　　【3】←SUMPRODUCT((B$2:B$11>B5)*(C$2:C$11=""))+1
 6　　 6　　 2　　　　　　　　　　　　　　
 7　　 5　　 1　　　　　　　　　　　　　　
 8　　 4　　　　　　　　　　　　　　　　4 
 9　　 3　　　　　　　　　　　　　　　　5 
10　　 2　　　　　　　　　　　　　　　　6 
11　　 1　　　　　　　　　　　　　　　　7

 [3]の一部を修正。0→1に直しました。
（んなっと） 2024/08/01(木) 21:37:11

 AGGREGATE(14,6,[数値]/(条件),〔何番目〕) で
(条件)を満たす[数値]の中で〔何番目〕に大きい数 を返します。

 AGGREGATE(15,6,[数値]/(条件1)/(条件2)...,〔何番目〕)　とすれば
(条件1),(条件2),...を満たす[数値]の中で〔何番目〕に小さい数 を返します。

（んなっと） 2024/08/01(木) 21:48:23

ご返信ありがとうございます。ほぼほぼ理解できてはいると思うのですが一晩確認してみます。何度も申し訳ありません。
（さめさる） 2024/08/01(木) 23:33:30

んなっと様
書いていただいた内容が理解できたのでご報告致します。他にINDEX関数の方や「そもそもなぜこの発想になったのか」などの疑問がありますが、自分がレベルアップした時に自然と解ることもあると思いますので今回は考えるのはここまでにしたいと思っております。お忙しい中自分のような初心者にご丁寧な返信をして頂き、とても感謝しております。また何かありましたらよろしくお願いします。
（さめさる） 2024/08/02(金) 12:55:35

コメント返信：

[ 一覧(最新更新順) ]

[[20240730170950]] 『1から10の数字』（さめさる） ページの最後に飛ぶ

[[20240730170950]]　『1から10の数字』（さめさる）　ページの最後に飛ぶ