錦子老師

汪小敏：「錦子老師，在Excel內，在A1:J1資料範圍有一串數列{-1,1,-0.77,0.77,-0.6,0.6,-0.15,-0.15,-0.125,0.125}(原始值)，在B2儲存格有一個數字0.707(目標值)。

如果要在A1:J1中，找出最接近A2的數字，並顯示在A3，公式應該是怎麼樣的呢? 」

※現在只能用ABS(B1:K1-B2)的方式並且用MIN找出來最接近值，但是無法回推到原始值。

錦子老師：「A3儲存格公式如下：

公式1：ABS(A1:J1-A2) 將A1到J1儲存格內容皆與A2儲存格內容相減取其絕對值，即是將負值轉為正值。

公式2：MIN(公式1)*10000+COLUMN(A1:J1) 將公式1的各個儲存格值乘上10000(這是由於計算結果最多為3位小數要將其變為從10位數開始數值)，再加上各個儲存格欄編號(個位數)，若資料範圍超過9欄，則乘值變為100,000，若超過99欄，則乘值變為1,000,000，以此類推。

公式3：INT(公式2) 利用INT函數將公式2運算產生的莫明小數位數清除。

公式4：RIGHT(公式3) 抓取公式3結果右邊一位數，即最小值所在欄，若欄位超過9欄，則要抓2位數，並利用VALUE函數將其轉為數值。

公式5：INDEX(1:1,公式4) 傳回第一列中由公式4算出所在欄的儲存格內容。

最終公式：

=INDEX(1:1,RIGHT(INT(MIN(ABS(A1:J1-A2)*10000+COLUMN(A1:J1)))))

再按CTRL+SHIFT+ENTER鍵完成陣列公式輸入。

以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

更多相關影片教學：請點我

更多相關文章：請點我