PTT數位生活區 / Office

[算表] Excel公式請益

看板Office作者 (鄉民愛發呆)時間7年前 (2019/03/21 17:08), 7年前編輯推噓0(0推 0噓 31→)
留言31則, 2人參與, 7年前最新討論串1/1
軟體:excel 版本:2010 公式:可從部分名稱內找出完整名稱是什麼 F2={IFERROR(OFFSET($A$1,SMALL(IF(IFERROR(FIND($E2,$A$2:$A$68),0)>0, ROW($A$2:$A$68),""),ROW($1:$1))-1,0),"")} A欄為我要搜尋的完整名稱 E欄為部分名稱，要從E欄的部分名稱找出他在A欄的完整名稱 是哪個 ex A欄:中華人民共和國 E欄:共和,F欄輸入以上公式， 即可找到他是中華人民共和國 想請教的是SMALL內的公式邏輯，因為拆解裡面的公式 都跑不出資料出來 還有後面ROW(1:1)的用意是什麼 感謝 -- Arguing on the Internet is like running in the Special Olympics: even if you win, you're still retarded." Jeriah -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 210.242.212.205 ※ 文章網址: https://www.ptt.cc/bbs/Office/M.1553159300.A.F0E.html ※ 編輯: abc9gad (210.242.212.205), 03/21/2019 17:10:05
→
03/21 17:12, 7年前 , 1F
先說公式一定不會是原文內的={IFERROR(...)}
03/21 17:12, 1F
→
03/21 17:12, 7年前 , 2F
大括號是按下組合鍵ctrl+shift+enter所產生的，且產生的地
03/21 17:12, 2F
→
03/21 17:13, 7年前 , 3F
方是{=IFERROR(...)}
03/21 17:13, 3F
→
03/21 17:16, 7年前 , 4F
改以index且不減1 https://i.imgur.com/ZGwqdOh.jpg
03/21 17:16, 4F
→
03/21 17:17, 7年前 , 5F
原本用於offset cols的,0也可刪除
03/21 17:17, 5F
→
03/21 17:18, 7年前 , 6F
公式row(1:1)為回傳第1列的列號，就是1
03/21 17:18, 6F
→
03/21 17:19, 7年前 , 7F
但用$列絕對參照，也就是下拉拖曳都是會是回傳1
03/21 17:19, 7F
→
03/21 17:20, 7年前 , 8F
因此可改為1，結果是一致的
03/21 17:20, 8F
請問s大FIND($E2,$A$2:$A$68) find公式我單獨拉出來都是錯誤是為什麼，加IFERROR ,0 結果不就都是0嗎 (IF(IFERROR(FIND($E2,$A$2:$A$68),0)>0,ROW($A$2:$A$68),"") 不太懂這段的意思 如果在A2:A68找到E2的值是TRUE,則ROW(A2:A68) 那是要呈現什麼呢 ※ 編輯: abc9gad (210.242.212.205), 03/21/2019 17:35:15
→
03/21 17:29, 7年前 , 9F
small的邏輯為find儲存格e2的值於a2:a68，有搜尋到的話就
03/21 17:29, 9F
→
03/21 17:31, 7年前 , 10F
會回傳>=1的數值，判斷當大於0時，則回傳row(a2:a68)列號
03/21 17:31, 10F
→
03/21 17:33, 7年前 , 11F
這裡有元素對應，當a5是符合時，第4個元素就會是列號5
03/21 17:33, 11F
→
03/21 17:35, 7年前 , 12F
iferror將find未搜尋到回傳錯誤值改為0值，判斷上未大於0
03/21 17:35, 12F
→
03/21 17:36, 7年前 , 13F
就都回傳為假的空字串""
03/21 17:36, 13F
→
03/21 17:37, 7年前 , 14F
=find(e2,a2:a68)於儲存格內，所回傳的結果是搜尋e2於a2
03/21 17:37, 14F
→
03/21 17:38, 7年前 , 15F
也就是說如果a2沒有找到就會回傳錯誤值
03/21 17:38, 15F
→
03/21 17:38, 7年前 , 16F
但如果a2就有符合的話，就會回傳>=1的數值
03/21 17:38, 16F
→
03/21 17:41, 7年前 , 17F
要呈現的是列號，當為真true就是在該元素內寫入對應的列號
03/21 17:41, 17F
所以我擷取=IF(IFERROR(FIND($E2,$A$2:$A$68),0)>0,ROW($A$2:$A$68),"") 也跑不出資料，是因為沒有用small，他在find A2的時候找不到值就直接判斷錯誤 要加入small讓它做範圍搜尋列的意思嗎? ※ 編輯: abc9gad (210.242.212.205), 03/21/2019 17:46:28
→
03/21 17:56, 7年前 , 18F
應該是這麼說find(e2,a2:a68)，也會去搜尋除了a2外的a3:
03/21 17:56, 18F
→
03/21 17:56, 7年前 , 19F
a68的部分，但find會回傳的是單一元素的結果，也就是搜尋
03/21 17:56, 19F
→
03/21 17:56, 7年前 , 20F
於a2所回傳的結果，加上其他函數如small，則會在這些元素
03/21 17:56, 20F
→
03/21 17:56, 7年前 , 21F
內回傳指定要的值
03/21 17:56, 21F
→
03/21 17:59, 7年前 , 22F
可以說這些元素在find時就存在了，再於單獨使用find時所回
03/21 17:59, 22F
→
03/21 17:59, 7年前 , 23F
傳的就是單一元素(第一筆)的結果，配合其他函數的變化，可
03/21 17:59, 23F
→
03/21 17:59, 7年前 , 24F
取得符合所需要的元素結果
03/21 17:59, 24F
→
03/21 18:46, 7年前 , 25F
補充一下，第一筆方面為測試公式find(e2,a2:a68)於第二列
03/21 18:46, 25F
→
03/21 18:46, 7年前 , 26F
，為find(e2,a2)，當於第三列時會是find(e2,a3)；但如果公
03/21 18:46, 26F
→
03/21 18:46, 7年前 , 27F
式鍵於第二列而公式是find(e2,a3:a68)，就算a3是有符合也
03/21 18:46, 27F
→
03/21 18:46, 7年前 , 28F
會回傳錯誤值，這應該是當搜尋範圍為複數時，會找對應的儲
03/21 18:46, 28F
→
03/21 18:46, 7年前 , 29F
存格a2，但a2並不在a3:a68內所以產生錯誤值，因此當搜尋範
03/21 18:46, 29F
→
03/21 18:46, 7年前 , 30F
圍為複數時，也會依公式所在的列來找出對應的儲存格
03/21 18:46, 30F
S大謝謝您，雖然還沒有辦法完全參透，但已經有所提升了一些概念 ※ 編輯: abc9gad (210.242.212.205), 03/22/2019 15:56:29
→
03/22 23:51, 7年前 , 31F
Find()最好不要單獨取出，可以標選，然後按F9顯示陣列值
03/22 23:51, 31F
‣ 更多 abc9gad 的文章
文章代碼(AID): #1SarI4yE (Office)
Office 近期熱門文章
更多 近期熱門文章 >>
PTT數位生活區 即時熱門文章
更多 即時熱門文章 >>
‣ 更多 abc9gad 的文章
文章代碼(AID): #1SarI4yE (Office)