[算表] 使用match和index搜尋並回傳第二筆資料

[rjrjrj (阿爾傑)]

軟體:excel

版本:2007


搜尋符合關鍵字有多筆資料的情況下

且資料庫在不同分頁

請問要如何呈現第二筆，甚至第三筆等等

這是我目前的寫法
https://i.imgur.com/dRRmUYa.png

資料庫大概是
https://i.imgur.com/kAluUc2.png

F2儲存格僅能呈現搜尋到的第一筆
是否一定要用陣列的寫法呢

另外想問陣列中不太了解ROW的位置到底要寫在哪邊

先謝謝了

--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 1.160.16.221
※ 文章網址: https://www.ptt.cc/bbs/Office/M.1550654346.A.13A.html
→ soyoso: 不一定要用陣列寫法，但要看有沒有排序
→ soyoso: 有排序列偏移配合row，無排序用陣列，如
→ soyoso: https://i.imgur.com/r9YshOX.jpg
→ soyoso: row就看括號內是否有指定，有指定就依儲存格位置回傳列號
→ soyoso: 無指定(省略)的話，就回傳公式所在的儲存格列號
    謝謝前輩，我會再試試看，非常感謝

    不知道S前輩還會不會看這篇文
    今天我嘗試自己修改公式
    想要用OR還判斷讓其中一條件達成就回傳資料
    但如果判斷的格子為空白就會錯誤

    https://i.imgur.com/aPGO7NC.png
    以上圖來說[P2]的公式如果[N2]或[O2]為空格就會錯誤導致回傳空白
    請問可以用什麼方式來迴避錯誤嗎
※ 編輯: rjrjrj (1.160.16.221), 02/21/2019 17:23:01
→ soyoso: 如要以整欄(如i和h欄)做為判斷的話，原本row內的1:100則改
→ soyoso: 為任一欄位(如a:a)
→ soyoso: 若以1:100(或其他指定列號範圍)的話，判斷內範圍列數則相
→ soyoso: 同於前述範圍的列數
    瞭解了，謝謝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
→ soyoso: 如果是以指定整欄的話，公式就是1048576個儲存格的判斷，
→ soyoso: 這還是單一儲存格內的公式，拖曳後就是再乘於幾個公式所在
→ soyoso: 儲存格的個數，這怎麼會不慢呢
→ soyoso: 這也就是為什麼我回文的公式不會去以整欄方式來判斷
→ soyoso: 所以請先限縮範圍；如果限縮後還是覺得效率上不符合預期
→ soyoso: 再保留某些於要更新資料時下拉公式的儲存格，其他選擇性貼
→ soyoso: 上(值)；如還是不符合的話，可以巨集來協助
→ soyoso: 回文的判斷方面 https://i.imgur.com/CPtPTOL.jpg
→ soyoso: 效率上也可用輔助欄協助 https://i.imgur.com/nv9SM8H.jpg
    因為資料庫會建到數萬筆以上，所以當時想說直接一次拉整欄比較快
    看來不能這麼懶就是了，不過前輩的寫法我有些疑問

      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
→ soyoso: 輔助欄的用意為公式重新運算時所計算的次數為輔助欄內公式
→ soyoso: 所拖曳的儲存格數量，為回文中的數萬筆。而陣式公式在重新
→ soyoso: 運算時就為這數萬筆*所拖曳儲存格
→ soyoso: 因此會覺得以輔助欄的方式可以改善，但還是有可能覺得效能
→ soyoso: 是不符合期待的
→ soyoso: 判斷方面則是改為 https://i.imgur.com/7JwcKAu.jpg
→ soyoso: 我回文打"陣式公式"有誤，是"陣列公式"
    感謝解惑!
※ 編輯: rjrjrj (1.169.110.237), 02/23/2019 12:38:12
→ soyoso: 陣列公式方面也可以選取範圍，鍵入公式，row(a1)方面就以
→ soyoso: 選取範圍的列數如10列就1:10的方式，效率應會好一些
→ soyoso: 測試如以判斷全欄效率上，輔助欄>上述陣列以1:10方式>陣列
→ soyoso: 以a1方式；但各有優缺，如輔助欄要影響檔案大小，陣列方式
→ soyoso: 不同調整上的自由度高低之類的
    有，我後來只拉五萬筆明顯快很多
    只不過前輩提供的輔助欄可能對我的幫助不大
    因為我設計的算表會一次要抓取100甚至更多的項目
    也可能是我還沒想到要怎樣運用，正在思考
    很謝謝前輩喔
※ 編輯: rjrjrj (36.226.54.168), 02/24/2019 18:37:08