Excel ô cuối cùng trong phạm vi

Truy xuất giá trị cuối cùng trong một cột là một nhiệm vụ dễ dàng trong cơ sở dữ liệu, nhưng Excel không phải là cơ sở dữ liệu (mặc dù nhiều người sử dụng nó như một cơ sở dữ liệu). Hoàn thành nhiệm vụ này khó hơn một chút trong Excel, nhưng chỉ một chút. Trong bài viết này, tôi sẽ chỉ cho bạn một công thức hoạt động với đối tượng Bảng và phạm vi dữ liệu thông thường. Tôi cũng sẽ chỉ cho bạn các VBA tương đương

Tìm hiểu thêm về phần mềm

• Sở hữu Microsoft Office Pro và quyền truy cập trọn đời vào 5 ứng dụng hàng đầu với giá $59. 99
• Cách quay lại lần đầu tiên và lần cuối cùng từ dấu thời gian trong Microsoft Excel
• Danh mục. Triển khai dịch vụ và ứng dụng Microsoft 365 trên máy Mac
• Google Workspace so với. Microsoft 365. Phân tích song song

Tôi đang sử dụng Excel 2016 trên hệ thống Windows 10 64-bit. Để thuận tiện cho bạn, bạn có thể tải xuống bản trình diễn. tệp xlsx và CLS. Công thức tập dữ liệu sẽ không hoạt động trong các phiên bản menu cũ hơn và macro không hoạt động trong phiên bản trình duyệt. Phiên bản trình duyệt không nhận ra đối tượng Bảng, nhưng công thức vẫn hoạt động–Excel đủ thông minh để sử dụng tham chiếu cấu trúc của đối tượng Bảng nếu bạn mở tệp trong trình duyệt

THẤY. Xây dựng kỹ năng Excel của bạn với 10 mẹo hiệu quả này (TechRepublic PDF)

Sử dụng bảng

Nhờ có đối tượng Bảng của Excel, loại tác vụ này trở nên đơn giản hơn trước đây. Công thức sử dụng các tham chiếu cấu trúc, do đó, nó dài, nhưng công thức chỉ tham chiếu Bảng. Điều đó có nghĩa là bạn có thể định vị công thức ở hầu hết mọi nơi và bạn có thể nhập dữ liệu bên trên và bên dưới cột được đề cập. Ngoài ra, tham chiếu có cấu trúc dễ đọc hơn tham chiếu ô;

Hình A hiển thị một Bảng dữ liệu hóa đơn đơn giản và một công thức (E2) trả về ngày cuối cùng trong cột C, Ngày lập hóa đơn. Như bạn có thể thấy, công thức và Bảng chứa Tổng số Hàng của Bảng và dữ liệu liền kề

Hình A

Công thức sử dụng các tham chiếu có cấu trúc đến Bảng và cột Ngày lập hóa đơn

=INDEX(Invoices[Invoice Date],COUNTA(Invoices[Invoice Date]))

Để hiểu cách thức hoạt động của công thức này, bạn cần biết cách thức hoạt động của các hàm INDEX() và COUNTA()

• INDEX() trả về một giá trị hoặc tham chiếu của ô tại giao điểm của một hàng và cột cụ thể trong một phạm vi nhất định bằng cú pháp INDEX(mảng, hàng, [cột])
• COUNTA() đếm số ô không trống trong một phạm vi cụ thể bằng cú pháp COUNTA(value1, [value2])

Để tìm hiểu thêm về các hàm đếm trong Excel, hãy đọc Tin sốt dẻo về các hàm đếm trong Excel

Hàm COUNTA() trả về 30 vì có 30 hàng trong cột Ngày hóa đơn, không bao gồm tiêu đề và tổng số hàng. (Bạn có thể bao gồm các hàng này trong một số lượng bằng cách bao gồm mã định danh [#All]. ) Vì mảng chỉ có một cột nên không cần tham số cột. Vì hàm COUNTA() trả về 30 nên hàm INDEX() trả về giá trị trong Dữ liệu hóa đơn ở hàng 30, 7/8/2017. Công thức tiếp tục hoạt động nếu bạn di chuyển Bảng, nếu bạn thêm một hàng phía trên Bảng và nếu bạn thêm dữ liệu phía trên hoặc phía dưới Dữ liệu Hóa đơn

THẤY. 10 mẹo tiết kiệm thời gian để tăng tốc công việc của bạn trong Excel (TechRepublic)

Mặc dù linh hoạt, nhưng công thức có một lỗ hổng - nó không thể xử lý một ô trống. COUNTA() đánh giá tất cả các giá trị, bao gồm cả văn bản và thậm chí là một chuỗi rỗng, “”. Tuy nhiên, nó không tính các ô trống. Do đó, số lượng được trả về sẽ là tổng số hàng trừ đi số ô trống. Nếu một bản ghi không có giá trị, hãy nhập 0 hoặc NA

Bạn có thể sử dụng VBA để thực hiện điều tương tự bằng cách sử dụng mã trong Liệt kê A. Thuộc tính End(xlDown) đề cập đến ô cuối cùng trong cột Ngày lập hóa đơn. Như trước đây, các tham chiếu Bảng biết vị trí của hàng cuối cùng, vì vậy văn bản bên dưới Bảng không phải là vấn đề. Tuy nhiên, tham chiếu này bao gồm hàng Tổng mà thuộc tính Offset chứa

Ghi chú. Đừng cố sao chép mã trực tiếp từ trang web này. Thay vào đó, hãy tải xuống các tệp CLS hoặc nhập mã theo cách thủ công

Liệt kê A

Function ReturnLastValueTable()
   'Return last value in specified column.
    ReturnLastValueTable = Range("Invoices[Invoice Date]"). _
       End(xlDown).Offset(-1, 0).Value
    Debug.Print ReturnLastValueTable
End Function

Sử dụng một phạm vi dữ liệu

Công thức chung tương tự hoạt động mà không cần Bảng, nhưng nó kém linh hoạt hơn. Trong Hình B, bạn có thể thấy công thức có dạng tương tự nhưng sử dụng tham chiếu ô

=INDEX(C:C,COUNTA(C:C))

Lần này, hàm COUNTA() trả về 31 vì nó đếm ô tiêu đề ở hàng 1. Do đó, công thức trả về giá trị ở hàng 31 cột C, 7/8/2017. Nếu bạn có một hàng tổng, bạn cũng phải chứa nó

Hình B

Công thức hoạt động với phạm vi dữ liệu bình thường, nhưng nó kém linh hoạt hơn. Bạn không thể di chuyển tập dữ liệu vì tham chiếu cột sẽ không cập nhật. Chèn các hàng phía trên tập dữ liệu có thể làm sai phép tính vì COUNTA() sẽ không đánh giá các hàng trống phía trên tập dữ liệu–COUNTA() trả về số lượng ô chứa dữ liệu. Tuy nhiên, nếu các ô phía trên tập dữ liệu trong cột C chứa dữ liệu, thì công thức vẫn hoạt động. Tôi không khuyên bạn nên sắp xếp như vậy vì bạn phải nhớ chi tiết này. Bạn không thể nhập dữ liệu bên dưới tập dữ liệu vì tham chiếu cột trong COUNTA() đánh giá toàn bộ cột;

Bạn có thể thực hiện điều tương tự bằng cách sử dụng mã trong Liệt kê B. Thuộc tính End(xlDown) dừng ở ô trống đầu tiên. Điều đó có nghĩa là bạn có thể nhập dữ liệu bên dưới tập dữ liệu trong cột C nếu có ít nhất một ô trống giữa hàng cuối cùng và dữ liệu mới

Liệt kê B

Function ReturnLastValueDataSet()
   'Return last value in specified column.
    ReturnLastValueDataSet = Range("C1").End(xlDown).Value
    Debug.Print ReturnLastValueDataSet
End Function

Cuộc gọi cuối

Nghiên cứu nhanh này không toàn diện, nhưng nó sẽ phù hợp với hầu hết các tình huống. Công thức Bảng linh hoạt hơn, nhưng không có công thức nào phù hợp với ô trống. Nếu không có giá trị hợp lệ cho một bản ghi, hãy nhập 0

Gửi cho tôi câu hỏi của bạn về Office

Tôi trả lời câu hỏi của độc giả khi có thể, nhưng không có gì đảm bảo. Không gửi tệp trừ khi được yêu cầu; . Bạn có thể gửi ảnh chụp màn hình dữ liệu của mình để giúp làm rõ câu hỏi của bạn. Khi liên hệ với tôi, càng cụ thể càng tốt. Ví dụ: “Vui lòng khắc phục sự cố sổ làm việc của tôi và sửa lỗi sai” có thể sẽ không nhận được phản hồi, nhưng “Bạn có thể cho tôi biết tại sao công thức này không trả về kết quả như mong đợi không?” . Vui lòng đề cập đến ứng dụng và phiên bản bạn đang sử dụng. Tôi không được TechRepublic hoàn trả cho thời gian hoặc chuyên môn của mình khi giúp đỡ độc giả, tôi cũng không yêu cầu một khoản phí từ những độc giả mà tôi giúp đỡ. Bạn có thể liên hệ với tôi tại susansalesharkins@gmail. com

Cũng đọc…

• Mẹo Office 365. Sử dụng Add To AutoCorrect để giúp bạn làm việc hiệu quả hơn (ZDNet)
• Trở thành Chuyên gia VBA Excel cho người mới bắt đầu (Học viện TechRepublic)
• Cách sử dụng định dạng có điều kiện để đánh dấu các hàng tổng phụ trong Excel (TechRepublic)
• Hai cách để tăng tốc macro Excel (TechRepublic)
• hỏi đáp văn phòng. Lấy lại các tùy chọn cũ và nhanh chóng tìm thấy các tin nhắn liên quan (TechRepublic)

Bản tin nội bộ công nghệ hàng ngày

Luôn cập nhật những thông tin mới nhất về công nghệ với Daily Tech Insider. Chúng tôi mang đến cho bạn tin tức về các công ty, sản phẩm và con người hàng đầu trong ngành, cũng như các bài viết nổi bật, nội dung tải xuống và các tài nguyên hàng đầu. Bạn sẽ nhận được tài liệu hướng dẫn về các chủ đề công nghệ nóng hổi sẽ giúp bạn dẫn đầu cuộc chơi