作者dogs1231992 (河豚)
看板Office
標題[算表] EXCEL 不使用迴圈下求最接近值
時間Thu Apr 20 17:32:28 2017
軟體:EXCEL
版本:2016
小弟目前有好幾組實驗數據，分成N個EXCEL檔，每個檔案的架構都一樣，只差在數據內容
我希望針對每個EXCEL檔內的數據做以下分析：
PIPE_M工作頁中有50行數據，分別儲存在D到BA中，我想要找出每行中最接近0的值
(絕對值後的最接近)
以AN行數據為例:
......
......
0.540504782
0.465468571
0.391963991
0.318428045
0.244860409
0.171260652
0.097628138
-0.074149919
-0.156326294
-0.238038925
-0.319741636
-0.40143503
-0.483119526
-0.566541229
......
......
以此範例來看，-0.074149919 比 0.097628138 更接近0
因此我希望EXCEL回傳 -0.074149919
若在表格內新增 0.0001 則回傳 0.0001 這個數值
我目前所使用的方式為：
{=MIN(ABS(INDIRECT("PIPE_M!AN2:AN"&COUNT(PIPE_M!AN:AN)+1)))}
雖然上式可以成功求得 -0.074149919 ，但由於迴圈要自己按 Ctrl+Shift+Enter
因此我擔心未來在複製方程式到其他EXCEL檔內處理相同資料時需要全部重按一次
(不知我個人認知是否有誤? 或者根本不用擔心?)
此外，先前曾處理過超大的EXCEL，在進入迴圈內之後會出現速度嚴重變慢的情形
(例如，從100萬筆資料中找到最小值)
關於目前的問題，我曾經嘗試過 =VLOOKUP(0,PIPE_M!AN:AN,1)
結果回傳的數據是 -13.03032375 (AN行中最後一個數據，也是最小的一個)
若將此方法套用到AM行的話會出現#N/A，因為AM行內全部都大於0
連回傳最小值都不會
我有想過直接將PIPE_M裡面的數據另存到PIPE_M_ABS中，並直接將其絕對值化
但由於全部數據有數十個EXCEL檔，要一個一個弄的話會耗費太多時間
不知此種問題是否有更簡單的解法?
非常感謝各位的幫忙 Orz
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 140.112.23.14
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Office/M.1492680750.A.7D4.html
1F：→ soyoso: 測試如是複製該陣列公式儲存格到其他excel檔內處理的話， 04/20 17:46
2F：→ soyoso: 測無需重新按下組合鍵；但如是從資料編輯列(公式列)複製該 04/20 17:47
3F：→ soyoso: 公式的話則是需要再次按組合鍵 04/20 17:48
4F：→ soyoso: 如重覆、反覆性處理數十個excel檔內的數據，也可看巨集是 04/20 18:15
5F：→ soyoso: 否可協助 04/20 18:15