作者rjrjrj (阿爾傑)
看板Office
標題[算表] 使用match和index搜尋並回傳第二筆資料
時間Wed Feb 20 17:19:01 2019
軟體:excel
版本:2007
搜尋符合關鍵字有多筆資料的情況下
且資料庫在不同分頁
請問要如何呈現第二筆，甚至第三筆等等
這是我目前的寫法
https://i.imgur.com/dRRmUYa.png
資料庫大概是
https://i.imgur.com/kAluUc2.png
F2儲存格僅能呈現搜尋到的第一筆
是否一定要用陣列的寫法呢
另外想問陣列中不太了解ROW的位置到底要寫在哪邊
先謝謝了
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 1.160.16.221
※ 文章網址: https://webptt.com/m.aspx?n=bbs/Office/M.1550654346.A.13A.html
1F：→ soyoso: 不一定要用陣列寫法，但要看有沒有排序 02/20 17:22
2F：→ soyoso: 有排序列偏移配合row，無排序用陣列，如 02/20 17:29
4F：→ soyoso: row就看括號內是否有指定，有指定就依儲存格位置回傳列號 02/20 17:30
5F：→ soyoso: 無指定(省略)的話，就回傳公式所在的儲存格列號 02/20 17:31
謝謝前輩，我會再試試看，非常感謝
不知道S前輩還會不會看這篇文
今天我嘗試自己修改公式
想要用OR還判斷讓其中一條件達成就回傳資料
但如果判斷的格子為空白就會錯誤
https://i.imgur.com/aPGO7NC.png
以上圖來說[P2]的公式如果[N2]或[O2]為空格就會錯誤導致回傳空白
請問可以用什麼方式來迴避錯誤嗎
※ 編輯: rjrjrj (1.160.16.221), 02/21/2019 17:23:01
6F：→ soyoso: 如要以整欄(如i和h欄)做為判斷的話，原本row內的1:100則改 02/21 17:53
7F：→ soyoso: 為任一欄位(如a:a) 02/21 17:54
8F：→ soyoso: 若以1:100(或其他指定列號範圍)的話，判斷內範圍列數則相 02/21 17:55
9F：→ soyoso: 同於前述範圍的列數 02/21 17:56
瞭解了，謝謝S前輩
※ 編輯: rjrjrj (1.161.247.97), 02/21/2019 21:26:16
S前輩，謝謝你，我成功寫出我要的東西
但是有兩點想問一下
https://i.imgur.com/MwN2I7n.png
以昨天的寫法如果以I跟H欄判斷時，若為空格就會把資料庫有空格的部分也回傳
所以我寫了一個判斷如果N非空格再判斷O是否空格來回傳資料
但是覺得這寫法實在太蠢了，請問S前輩會怎麼寫
還有就是當陣列太多的時候會導致EXCEL讀取很慢，是否只能靠VB解決呢
※ 編輯: rjrjrj (1.169.110.237), 02/22/2019 17:43:24
10F：→ soyoso: 如果是以指定整欄的話，公式就是1048576個儲存格的判斷， 02/22 17:50
11F：→ soyoso: 這還是單一儲存格內的公式，拖曳後就是再乘於幾個公式所在 02/22 17:51
12F：→ soyoso: 儲存格的個數，這怎麼會不慢呢 02/22 17:51
13F：→ soyoso: 這也就是為什麼我回文的公式不會去以整欄方式來判斷 02/22 17:52
14F：→ soyoso: 所以請先限縮範圍；如果限縮後還是覺得效率上不符合預期 02/22 17:54
15F：→ soyoso: 再保留某些於要更新資料時下拉公式的儲存格，其他選擇性貼 02/22 17:55
16F：→ soyoso: 上(值)；如還是不符合的話，可以巨集來協助 02/22 17:56
因為資料庫會建到數萬筆以上，所以當時想說直接一次拉整欄比較快
看來不能這麼懶就是了，不過前輩的寫法我有些疑問
1.我對輔助欄的理解是先把判斷式獨立完成後排序，再用排序回傳
但是這樣判斷式一樣是在同一份檔案之中，如果指定整欄一樣是會很慢，是嗎
2.判斷方面不知道是不是我表達的不夠完善
我當初的想法是以[N8][O8]任一格符合就回傳資料
所以會變成 [N8=H][O8=""]
[N8=""][O8=I]
[N8=H][O8=I]
[N8=H][O8<>I]
[N8<>H][O8=I] 以上狀況皆回傳
那前輩的公式我試過了，最後兩點的情況會判斷為錯誤
又如果[N8][O8]都是空格時，會每一筆都回傳
因為我表達能力有點差，真是抱歉了，不過我也很希望可以搞懂公式在幹嘛
真是謝謝了
※ 編輯: rjrjrj (1.169.110.237), 02/23/2019 11:23:31
19F：→ soyoso: 輔助欄的用意為公式重新運算時所計算的次數為輔助欄內公式 02/23 11:53
20F：→ soyoso: 所拖曳的儲存格數量，為回文中的數萬筆。而陣式公式在重新 02/23 11:54
21F：→ soyoso: 運算時就為這數萬筆*所拖曳儲存格 02/23 11:54
22F：→ soyoso: 因此會覺得以輔助欄的方式可以改善，但還是有可能覺得效能 02/23 11:55
23F：→ soyoso: 是不符合期待的 02/23 11:55
25F：→ soyoso: 我回文打"陣式公式"有誤，是"陣列公式" 02/23 12:05
感謝解惑!
※ 編輯: rjrjrj (1.169.110.237), 02/23/2019 12:38:12
26F：→ soyoso: 陣列公式方面也可以選取範圍，鍵入公式，row(a1)方面就以 02/23 13:08
27F：→ soyoso: 選取範圍的列數如10列就1:10的方式，效率應會好一些 02/23 13:09
28F：→ soyoso: 測試如以判斷全欄效率上，輔助欄>上述陣列以1:10方式>陣列 02/23 13:20
29F：→ soyoso: 以a1方式；但各有優缺，如輔助欄要影響檔案大小，陣列方式 02/23 13:22
30F：→ soyoso: 不同調整上的自由度高低之類的 02/23 13:23
有，我後來只拉五萬筆明顯快很多
只不過前輩提供的輔助欄可能對我的幫助不大
因為我設計的算表會一次要抓取100甚至更多的項目
也可能是我還沒想到要怎樣運用，正在思考
很謝謝前輩喔
※ 編輯: rjrjrj (36.226.54.168), 02/24/2019 18:37:08