有讀者遇到這么個問題：有一個二維表，其中的單元格并未全部填充滿，如何根據(jù)行標題，向右查找出最新的值？

微軟Office LTSC 2021專業(yè)增強版 簡體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大?。?/span>2.2GB
• 語言：簡體中文
• 時間：2024-09-12

查看詳情

案例 一：

某公司近期因準備 IPO，要給員工配股，因此統(tǒng)計了 2016 年至今在職員工的職級，規(guī)則如下，如下圖 1 的 A 至 F 列數(shù)據(jù)表所示：

統(tǒng)計每個人自 2016 年起的職級，如果職級發(fā)生變化，則連續(xù)向右填充至最新的狀態(tài)，如果之后職級無更新，則留空不需要再填，根據(jù) H2 單元格中填入的姓名，在 I2 單元格查找出該員工的最新職級。

效果如下圖 2 所示。

解決方案：

1. 將 J 列設(shè)置為輔助列，在 J2 單元格中輸入以下公式：

=COUNTA(INDEX(B2:F8,MATCH(H2,A2:A8,0),))

公式釋義：

MATCH(H2,A2:A8,0)：在 A2:A8 區(qū)域查找 H2，并返回其在區(qū)域中的位置

INDEX(B2:F8,...,)：

返回 B2:F8 區(qū)域中指定行、列的值；

行數(shù)為上述 match 函數(shù)的查找結(jié)果；

列數(shù)為空，即不限定列，表示查找出區(qū)域中姓名與 H2 內(nèi)容相同的所有單元格；

COUNTA(...)：

COUNTA函數(shù)功能是返回參數(shù)列表中非空的單元格個數(shù)；

即統(tǒng)計出 H2 單元格的員工在區(qū)域中的職級共有幾個非空單元格；

本例中，“諸葛鋼鐵”的職級共有 3 個單元格

2. 在 I2 單元格中輸入以下公式：

=INDEX(B2:F8,MATCH(H2,A2:A8,0),J2)

公式釋義：

MATCH(H2,A2:A8,0)：再次使用 match 函數(shù)匹配出所需查找的姓名在 A2:A8 區(qū)域中的位置，并將其用作行號；

J2：將 J2 單元格，即上述輔助列的結(jié)果作為列號，即區(qū)域中的最后一列非空單元格；

INDEX(B2:F8,...,)：用 index 函數(shù)在 B2:F8 區(qū)域中查找出行、列交叉位置的值

如果在 H2 中輸入其他姓名，I2 單元格的結(jié)果也會隨之變化。

案例二

一、使用MAX函數(shù)找到非空單元格的位置

MAX函數(shù)可以用來找到一列或一行所在的最大值，因此可以用它來找到非空單元格所在的位置。

=MAX(A:A)

上述公式將返回列A中最后一個非空單元格所在的行號。

同樣地，如果要查找最后一個非空單元格所在的列號，可以使用以下公式：

=MAX(1:1)

二、結(jié)合INDEX函數(shù)返回非空單元格的值

一旦找到了最后一個非空單元格的位置，就可以使用INDEX函數(shù)返回該單元格的值。

=INDEX(A:A,MAX(A:A))

上述公式將返回列A中最后一個非空單元格的值。

同樣地，如果要返回最后一個非空單元格所在的列的值，可以使用以下公式：

=INDEX(1:1,MAX(1:1))

三、使用VBA宏實現(xiàn)自動查找最后一個非空單元格

通過編寫VBA宏，可以實現(xiàn)在大型工作簿中自動查找最后一個非空單元格。

可以使用以下代碼實現(xiàn)：

Sub LastNonBlankCell()
    Dim LastRow As Long
    Dim LastCol As Long
    Dim LastCell As Range
    '查找最后一個非空行
    LastRow = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    '查找最后一個非空列
    LastCol = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    '找到最后一個非空單元格
    Set LastCell = Cells(LastRow, LastCol)
    '選中最后一個非空單元格
    LastCell.Select
End Sub

上述代碼中，F(xiàn)ind函數(shù)代表查找函數(shù)，它可以對表格、行或列進行查找。SearchDirection參數(shù)表示查找的方向，SearchOrder參數(shù)則表示查找的順序。

四、結(jié)合OFFSET函數(shù)實現(xiàn)動態(tài)查找

如果需要動態(tài)查找最后一個非空單元格，可以結(jié)合OFFSET函數(shù)實現(xiàn)。

可以使用以下公式實現(xiàn)在列A中動態(tài)查找：

=OFFSET(A1,COUNTA(A:A)-1,0)

上述公式中，COUNTA函數(shù)可以用來計算列A中的非空單元格數(shù)量，-1操作可以確定最后一個非空單元格所在的行號（基于A1作為起始點）。OFFSET函數(shù)可以用來定位該單元格并返回其值。

同樣地，如果要動態(tài)查找最后一個非空單元格所在的列的值，可以使用以下公式：

=OFFSET(A1,0,COUNTA(1:1)-1)

推薦閱讀：excel如何篩選最晚時間的記錄? 一對多匹配結(jié)果中查找出日期最晚的技巧

最新評論