Làm cách nào để tham chiếu ô cuối cùng trong bảng excel?

Patty đã hỏi về một tình huống phổ biến, trong đó cột B chứa khá nhiều dữ liệu và thông tin có thể được thêm vào các ô trong cột bất kỳ lúc nào. Trong một công thức ở ô C4, Patty muốn xem giá trị ở cuối các ô đó trong cột B có chứa các giá trị. Như vậy, nếu các ô B1. B27 chứa dữ liệu, sau đó trong ô C4 Patty muốn xem giá trị trong ô B27. Nếu thêm ba phần dữ liệu nữa vào cột B, thì giá trị trong C4 phải chứa giá trị trong B30

Giải pháp cho vấn đề này phụ thuộc vào việc bạn có thể tin tưởng vào dữ liệu trong cột B có chứa các ô trống hay không. Nếu dữ liệu liền kề—nó không chứa bất kỳ ô trống nào—thì bạn có thể sử dụng công thức sau trong C4

=INDIRECT["B"&COUNTA[B:B]]

Thao tác này tạo địa chỉ dựa trên ô cuối cùng trong cột, sau đó sử dụng hàm INDIRECT để trả về giá trị tại địa chỉ đó

Nếu có thể có khoảng trống trong cột B, thì công thức sau sẽ hoạt động

=INDIRECT["B"&MAX[ROW[1:65535]*[B1:B65535""]]]

Một lần nữa, hàm INDIRECT được sử dụng để tìm nạp giá trị thực, nhưng địa chỉ được sử dụng bởi INDIRECT được đặt lại với nhau theo cách khác

Một cách tiếp cận khác là sử dụng hàm VLOOKUP để trả về giá trị. Nếu cột B bao gồm các giá trị số, thì công thức sau trong C4 sẽ hoạt động bình thường

Hoàn toàn ấn tượng với tất cả những thứ tuyệt vời đến từ Ablebits. Điều này cũng không ngoại lệ. Vấn đề là, và tôi ghét phải nói điều này, đơn giản là tôi không có thời gian hoặc không có khuynh hướng học lại một cách hoàn toàn mới để viết và thực hiện cách mới này để tạo công thức cũng như tất cả các quy tắc và ngoại lệ liên quan. Tôi là một bộ đếm thời gian cũ đã học các bảng tính trên SuperCalc, 123 và Lotus, sau đó cuối cùng Microsoft đã đưa ra thứ mà tôi coi là từ cuối cùng trong những gì tôi cần cho các tính toán khá đơn giản của tôi liên quan đến giao dịch trong ngày chứng khoán của tôi

Đáng buồn thay, một vài năm trước, tôi đã quyết định tạo một Sổ làm việc tinh vi, theo đó tôi đã tạo một trang tính chính bao gồm 365 bảng tổng hợp cho mỗi ngày trong năm. Mỗi bảng có tất cả các vị trí chứng khoán của tôi được chia thành các lô cụ thể và từ đó, tôi tiếp tục chạy tổng số tất cả các loại dữ liệu thống kê mà tôi đã tạo bằng các công thức tiêu chuẩn. Sổ làm việc có tất cả các loại trang tính chứa đầy dữ liệu thống kê mà tôi tham khảo và sử dụng trong trang tính chính

Vì vậy, một ngày nọ, tôi loay hoay với một trong các tùy chọn đặt tên vì nghĩ rằng việc sử dụng tên tiêu đề thay vì tham chiếu ô tiêu chuẩn sẽ giúp mọi việc dễ dàng hơn. Kể từ ngày đó, các tham chiếu có cấu trúc bắt đầu len lỏi vào các công thức của tôi khi tôi thêm các thứ. Tôi không biết nó sẽ phát triển thành một vấn đề như vậy. Nó đặc biệt trở nên phổ biến khi tôi nhập tệp CSV và tạo bảng tổng hợp mới trong các trang tính khác. Tôi cũng có rất nhiều trang tính trong sổ làm việc này với các tên phức tạp dài mà tôi phải lấy dữ liệu từ đó. Tên Tiêu đề cột cũng dài nhưng bằng cách đặt chúng theo cách đó, tôi biết chính xác nơi cần trích xuất dữ liệu. Tôi đã học cách đọc trôi chảy các công thức của mình ngay cả khi gặp những trở ngại này và di chuyển xung quanh một cách nhanh chóng và chính xác trong một khoảng thời gian. Bây giờ, tôi liên tục phải xóa các tên có cấu trúc theo cách vật lý khi chúng liên tục xuất hiện như bỏng ngô cháy và viết lại các công thức một lần nữa. Cuối cùng tôi đã tìm ra cách quay lại vị trí thích hợp để tắt tên bảng trong công thức, nhưng tôi không tìm được cách nào để buộc excel chuyển đổi tất cả các tên có cấu trúc trở lại danh pháp Cột, Hàng cũ và tôi cũng không thể tìm ra

Xin lỗi vì lời giải thích dài dòng nhưng tôi hy vọng một trong số các bạn, những người tốt bụng có thể giúp tôi với các đề xuất về cách tiến hành hoặc hướng dẫn tôi đến một nơi nào đó trên web đến một nơi có thể

Quay lại danh sách công thức Excel

Tải xuống Sổ làm việc Ví dụ

Tải xuống sổ làm việc ví dụ

Hướng dẫn này sẽ hướng dẫn bạn cách tra cứu giá trị cuối cùng trong cột hoặc hàng trong Excel

Giá trị cuối cùng trong cột

Bạn có thể sử dụng Hàm LOOKUP để tìm ô không trống cuối cùng trong một cột

=LOOKUP[2,1/[B:B""],B:B]

Hãy xem qua công thức này

Phần công thức B. B”” trả về một mảng chứa các giá trị Đúng và Sai. {FALSE, TRUE, TRUE,…}, kiểm tra mỗi ô trong cột B đều trống [FALSE]

=LOOKUP[2,1/[{FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;...],B:B]

Các giá trị Boolean này chuyển đổi thành 0 hoặc 1 và được sử dụng để chia 1

=LOOKUP[2,{#DIV/0!;1;1;1;1;1;#DIV/0!;,B:B]

Đây là lookup_vector cho Hàm LOOKUP. Trong trường hợp của chúng ta, lookup_value là 2, nhưng giá trị lớn nhất trong lookup_vector là 1, vì vậy Hàm LOOKUP sẽ khớp với 1 cuối cùng trong mảng và trả về giá trị tương ứng trong result_vector

Nếu bạn chắc chắn rằng bạn chỉ có các giá trị số trong cột của mình, dữ liệu của bạn bắt đầu từ hàng 1 và phạm vi dữ liệu của bạn liên tục, bạn có thể sử dụng công thức đơn giản hơn một chút với các hàm INDEX và COUNT

=INDEX[B:B,COUNT[B:B]]

Hàm COUNT trả về số lượng ô chứa đầy dữ liệu trong phạm vi liên tục [4] và Hàm INDEX do đó đưa ra giá trị của ô trong hàng tương ứng này [thứ 4]

Để tránh các lỗi có thể xảy ra khi phạm vi dữ liệu của bạn chứa hỗn hợp các giá trị số và không phải số hoặc thậm chí một số ô trống, bạn có thể sử dụng Hàm LOOKUP cùng với các hàm ISBLANK và NOT

=LOOKUP[2,1/[NOT[ISBLANK[B:B]]],B:B]

Hàm ISBLANK trả về một mảng chứa các giá trị Đúng và Sai, tương ứng với 1 và 0. Hàm NOT thay đổi True [i. e. 1] thành Sai và Sai [i. e. 0] thành Đúng. Nếu chúng ta đảo ngược mảng kết quả này [khi chia 1 cho mảng này], chúng ta sẽ nhận được một mảng kết quả chứa lại #DIV/0. lỗi và 1, có thể được sử dụng làm mảng tra cứu [lookup_vector] trong Hàm LOOKUP của chúng tôi. Khi đó, chức năng của Hàm LOOKUP giống như trong ví dụ đầu tiên của chúng tôi. nó trả về giá trị của véc tơ kết quả ở vị trí số 1 cuối cùng trong mảng tra cứu

Khi bạn cần trả về số hàng có mục nhập cuối cùng, bạn có thể sửa đổi công thức được sử dụng trong ví dụ đầu tiên của chúng tôi cùng với Hàm ROW trong result_vector của bạn

=LOOKUP[2,1/[B:B""],ROW[B:B]]

Giá trị cuối cùng trong hàng

Để lấy giá trị của ô không trống cuối cùng trong một hàng chứa đầy dữ liệu số, bạn có thể muốn sử dụng một cách tiếp cận tương tự nhưng với các chức năng khác nhau. Hàm OFFSET cùng với hàm MATCH và MAX

=OFFSET[Reference, Rows, Columns]

=OFFSET[B2,0,MATCH[MAX[B2:XFD2]+1,B2:XFD2,1]-1]

Hãy xem công thức này hoạt động như thế nào

Hàm MATCH

Chúng ta sử dụng Hàm MATCH để “đếm” xem có bao nhiêu giá trị ô nhỏ hơn 1 + giá trị lớn nhất của tất cả các giá trị trong hàng 2 bắt đầu từ B2

=INDIRECT["B"&MAX[ROW[1:65535]*[B1:B65535""]]]

=INDIRECT["B"&MAX[ROW[1:65535]*[B1:B65535""]]]

Lookup_value của Hàm MATCH là giá trị lớn nhất trong tất cả các giá trị trong hàng2 + 1. Vì giá trị này rõ ràng không tồn tại trong hàng2 và match_type được đặt thành 1 [nhỏ hơn hoặc bằng lookup_value], nên Hàm MATCH sẽ trả về vị trí của ô được “kiểm tra” cuối cùng trong mảng, tức là số lượng ô được lấp đầy . XFD2 [XFD là cột cuối cùng trong các phiên bản Excel mới hơn]

Chức năng OFFSET

Sau đó, chúng tôi sử dụng Hàm OFFSET để lấy giá trị của ô này, vị trí được trả về bởi Hàm MATCH