|
前一篇推文中，我教大家如何自動(dòng)更新打印區(qū)域的時(shí)候，用到了一個(gè)公式，如何查找行和列的最后一個(gè)非空單元格。 這個(gè)知識(shí)點(diǎn)引起了很多同學(xué)的熱烈討論，如何查找最后一個(gè)非空元格？查找文本和數(shù)值的公式是否一樣？找到后是返回單元格內(nèi)容還是返回行號(hào)列標(biāo)？ 對(duì)于這些問(wèn)題，今天我就來(lái)好好捋一捋如何查找最后一個(gè)非空單元格。 案例： 下圖 1 是某公司銷售的獲客數(shù)總表，比較有代表性的是 A 列是文本，B 列為數(shù)值。 我們就以此表來(lái)詳述查找文本和數(shù)值的最后一個(gè)單元格，分別返回值和行號(hào)，有哪些適用的公式。 效果如下圖 2 所示。   解決方案： 我把查找需求分為以下幾種大類，分別來(lái)看有哪些公式： 查找文本： 返回行號(hào)或列號(hào) 返回單元格值 查找數(shù)值： 返回行號(hào)或列號(hào) 返回單元格值 以本例來(lái)看，D 列是查找文本，E 列查找數(shù)值，返回的結(jié)果類型寫在 F 列。 第一類：查找文本或數(shù)值，返回行號(hào) 1. 在 D2 單元格中輸入以下公式 --> 回車（O365 直接回車即可，O365 以下版本需要按 Ctrl+Shift+Enter 生成數(shù)組公式）： =MATCH(1,0/(A:A"")) 公式釋義： A:A""：會(huì)生成一組 true 和 false 組成的數(shù)組，最后一個(gè)非空單元格返回最后一個(gè) true 值； 0/...：用 0 除以上述數(shù)組，得到由 0 或錯(cuò)誤值組成的數(shù)組； MATCH(1,...)：在上述數(shù)組中查找 1，找不到，就會(huì)一直往下找，直至最后一個(gè)接近的值，并返回其在區(qū)域內(nèi)的序列號(hào)   2. 同理，在 E2 單元格中輸入以下公式 --> O365 直接按回車，低版本按 Ctrl+Shift+Enter： =MATCH(1,0/(B:B"")) 公式釋義： 跟上述公式原理一樣，查找并返回區(qū)域內(nèi)最后一個(gè)非空單元格的行號(hào)    第二類：查找文本或數(shù)值，返回值 1. 在 D3 單元格中輸入以下公式 --> 回車： =LOOKUP(1,0/(A:A""),A:A) 公式釋義： lookup 函數(shù)用于模糊查找，1,0 的用法原理跟前面一個(gè)公式一樣，唯一不同的是 lookup 返回的是單元格的值   2. 在 E3 單元格中輸入以下公式 --> 回車： =LOOKUP(1,0/(B:B""),B:B) 公式釋義： 與上一個(gè)例子同理   第三類：只能查找文本，返回行號(hào) 1. 在 D4 單元格中輸入以下公式 --> 回車： =MATCH("々",A:A) 公式釋義： “々”在漢字中是一個(gè)編碼很大的字符，可以通過(guò)小鍵盤的 Alt+41385 輸入； MATCH("々",A:A)：在 A 列中查找“々”，當(dāng)匹配不到，也沒(méi)有更大編碼的值時(shí)，就會(huì)返回區(qū)域內(nèi)最后一個(gè)文本單元格的序列號(hào) 第四類：只能查找文本，返回值 1. 在 D5 單元格中輸入以下公式 --> 回車： =LOOKUP("々",A:A) 公式釋義： 與前一個(gè)公式同理，所不同的是 lookup 函數(shù)返回的是單元格的值 第五類：只能查找數(shù)值，返回值 1. 在 E5 單元格中輸入以下公式 --> 回車： =LOOKUP(9E+307,B:B) 公式釋義： 9E+307 通常表示 Excel 能處理的最大數(shù)值； LOOKUP(9E+307,B:B)：在 B 列中查找這個(gè)最大數(shù)值，找不到則返回最后一個(gè)單元格的值 第六類：只能查找數(shù)值，返回行號(hào) 1. 在 E4 單元格中輸入以下公式 --> 回車： =MATCH(MAX(B:B)+1,B:B) 公式釋義： MAX(B:B)+1：查找 B 列中的最大值并加上 1； MATCH(...,B:B)：在 B 列中查找這個(gè)比最大值還大的值，找不到就一直找到最后一個(gè)單元格，并返回其在區(qū)域中的序列號(hào) 2. 在 E6 單元格中輸入以下公式 --> 回車： =MATCH(9E+307,B:B) 公式釋義： 9E+307 的作用跟上一個(gè)公式的參數(shù)一樣，也是找一個(gè)比區(qū)域內(nèi)最大數(shù)值還大的數(shù) 3. 在 E7 單元格中輸入以下公式 --> 回車： =LOOKUP(MAX(B:B)+1,B:B,ROW(B:B)) 公式釋義： ROW(B:B)：生成一個(gè) B 列的行號(hào)數(shù)組，數(shù)組內(nèi)是從 1 開始，以 1 遞增的自然數(shù) LOOKUP(MAX(B:B)+1,B:B,...)：在 B 列中查找比最大數(shù)還要大的數(shù)值，找不到就查找到最后一個(gè)非空單元格，返回第三個(gè)參數(shù)中同等位置的值，即最后一個(gè)單元格的行號(hào) 最終結(jié)果如下。 |
|
|
