Bài viết này trình bày các công thức trả về giá trị cuối cùng trong một phạm vi ô hoặc cột nhất định. Hình ảnh trên cho thấy một công thức trong ô D3 trích xuất giá trị cuối cùng từ phạm vi ô B3. B12
Phạm vi ô B3. B12 được điền các giá trị và các ô trống theo thứ tự ngẫu nhiên, điều này cho thấy công thức hoạt động tốt với các ô trống
Mục lục
1. Trích xuất giá trị cuối cùng trong một cột nhất định
Công thức trong ô D3 cho phép bạn lấy giá trị cuối cùng trong cột B, nó hoạt động tốt với các ô trống trong danh sách của bạn
Công thức trong ô D3
=LOOKUP[2,1/[B. B""],B. b]
Công thức này khá tốn CPU vì nó đang xử lý tất cả các ô trong cột B, có hơn một triệu ô trong một cột trong Excel 2007 và các phiên bản mới hơn
Bài viết được đề xuất
Bài viết được đề xuất
Tìm giá trị khớp cuối cùng trong danh sách chưa sắp xếp
Bài viết này trình bày một công thức trả về giá trị khớp cuối cùng dựa trên một điều kiện nhất định. Hình ảnh trên cho thấy […]
Bạn có thể thay đổi tham chiếu ô nếu bạn biết mình sẽ có một danh sách nhỏ hơn, công thức này sẽ dễ dàng hơn để Excel xử lý
=LOOKUP[2,1/[B1. B10000""],B1. B10000]
1. 1 Giải thích công thức
Sau đó, nó chia 1 với mảng tạo lỗi cho tất cả các ô trống
Hàm LOOKUP bỏ qua lỗi và cố gắng tìm kết quả khớp. Nếu mọi kết quả khớp là 1 và giá trị tra cứu là 2, thì hàm LOOKUP sẽ trả về giá trị cuối cùng trong phạm vi ô. Thông thường danh sách phải được sắp xếp tăng dần để hàm LOOKUP hoạt động, tuy nhiên vì mọi giá trị đều là 1 nên không cần sắp xếp danh sách
Tôi sẽ sử dụng công thức sau vì nó sẽ dễ chứng minh hơn
TÌM KIẾM[2,1/[B3. B12""],B3. B12]
Bước 1 - Kiểm tra xem các ô trong cột B có không bằng không
Các dấu nhỏ hơn và lớn hơn là các toán tử logic, chúng kiểm tra xem các giá trị ô trong cột B có trống không. Kết quả là một mảng chứa TRUE hoặc FALSE
B3. B12""
trở thành
{"ZM";"TY";"NX";0;
và trả về
{TRUE;TRUE;TRUE;TRUE;
Bước 2 - Chia 1 với mảng
1/[B3. B12""]
trở thành
1/[{TRUE;TRUE;TRUE; FALSE;TRUE;TRUE;FALSE; TRUE;TRUE;FALSE}]
và trả về
{1;1;1;#DIV/0. ;1;1; . ;1;1;#DIV/0. }
Giá trị Boolean TRUE bằng 1 và FALSE bằng 0. Bạn không thể chia một giá trị bằng 0 để Excel trả về lỗi [#DIV/0. ]
Bước 3 - Giá trị trả về
TÌM KIẾM[2,1/[B3. B12""],B3. B12]
trở thành
LOOKUP[2,{1;1;1;#DIV/0. ;1;1; . ;1;1;#DIV/0. },B3. B12]
trở thành
LOOKUP[2,{1;1;1;#DIV/0. ;1;1; . ;1;1;#DIV/0. },{"ZM";"TY";"NX";0;
và trả về XH trong ô D3
Bài viết được đề xuất
Bài viết được đề xuất
Cách sử dụng hàm LOOKUP
Tìm giá trị trong phạm vi ô đã sắp xếp và trả về giá trị trên cùng một hàng.
1. 2 Còn lỗi thì sao?
Tôi đã thêm một vài lỗi trong cột B, xem hình trên
Tôi đang làm việc với Excel 2016 và các lỗi trong cột B dường như không phải là vấn đề
2. Trả về một siêu kết nối đến giá trị cuối cùng trong một cột
Công thức trong ô D3, được minh họa trong hình trên, tạo một siêu liên kết đến ô cuối cùng trong cột B. Nhấn bằng nút chuột trái trên siêu liên kết sẽ đưa bạn đến giá trị cuối cùng trong cột B
Công thức trong ô D3
=HYPERLINK[MID[CELL["filename", A1], SEARCH["[", CELL["filename", A1]], LEN[CELL["filename", A1]]]&". $B$"&TÌM KIẾM[2, 1/[B. B""], HÀNG[B. B]], TÌM KIẾM[2, 1/[B. B""], B. B]]
2. 1 Giải thích công thức
Bước 1 - Nhận đường dẫn và tên sổ làm việc
Hàm CELL lấy thông tin về định dạng, vị trí hoặc nội dung của một ô
CELL["tên tệp", A1]
trả về C. \temp\[Tìm giá trị cuối cùng trong listv2. xlsx]Siêu liên kết
Bước 2 - Tìm vị trí của ký tự [
Chúng tôi không cần đường dẫn để tạo siêu liên kết hoạt động. Bước này trả về ký tự của chuỗi đầu tiên mà chúng ta cần
Hàm TÌM KIẾM trả về một số biểu thị vị trí của ký tự mà tại đó một chuỗi văn bản cụ thể được tìm thấy khi đọc từ trái sang phải
TÌM KIẾM["[", CELL["tên tệp", A1]]
trở thành
TÌM KIẾM["[", "C. \temp\[Tìm giá trị cuối cùng trong listv2. xlsx]Siêu liên kết"]
và trả về 35
Bước 3 - Trích xuất bảng tính và tên bảng tính
Hàm MID trả về một chuỗi con từ một chuỗi dựa trên vị trí bắt đầu và số lượng ký tự bạn muốn trích xuất
MID[văn bản, start_num, num_chars]
MID[CELL["tên tệp", A1], TÌM KIẾM["[", CELL["tên tệp", A1]], LEN[CELL["tên tệp", A1]]]
trở thành
MID[CELL["tên tệp", A1], 9, LEN[CELL["tên tệp", A1]]]
Hàm LEN tính toán số lượng ký tự trong một chuỗi đã cho
MID[CELL["tên tệp", A1], 9, LEN[CELL["tên tệp", A1]]]
trở thành
MID[CELL["tên tệp", A1], 9, 49]
và trả về
"[Tìm giá trị cuối cùng trong listv2. xlsx]Siêu liên kết"
Bước 4 - Nối tên sổ làm việc, tên trang tính và cột
Ký tự dấu và & cho phép bạn nối chuỗi trong công thức Excel
MID[CELL["tên tệp", A1], TÌM KIẾM["[", CELL["tên tệp", A1]], LEN[CELL["tên tệp", A1]]]&". $B$"
trở thành
"[Tìm giá trị cuối cùng trong listv2. xlsx]Siêu liên kết"&". $B$"
và trả về "[Tìm giá trị cuối cùng trong listv2. xlsx]Siêu liên kết. $B$"
Bước 5 - Tính số hàng của giá trị cuối cùng trong một cột
Hàm ROW cho phép bạn tính số hàng của giá trị cuối cùng
MID[CELL["tên tệp", A1], TÌM KIẾM["[", CELL["tên tệp", A1]], LEN[CELL["tên tệp", A1]]]&". $B$"&TÌM KIẾM[2, 1/[B. B""], HÀNG[B. B]]
trở thành
"[Tìm giá trị cuối cùng trong listv2. xlsx]Siêu liên kết. $B$"&TÌM KIẾM[2, 1/[B. B""], HÀNG[B. B]]
trở thành
"[Tìm giá trị cuối cùng trong listv2. xlsx]Siêu liên kết. $B$"&11
và trả về "[Tìm giá trị cuối cùng trong listv2. xlsx]Siêu liên kết. $B$11"
Bước 6 - Trả về giá trị cuối cùng
TÌM KIẾM[2, 1/[B. B""], B. b]
trả về "XH"
Bước 7 - Tạo một siêu liên kết
Hàm HYPERLINK cho phép bạn tạo một liên kết trong một ô trỏ đến một thứ khác như tệp, sổ làm việc, ô, phạm vi ô hoặc trang web
HYPERLINK[MID[CELL["tên tệp", A1], TÌM KIẾM["[", CELL["tên tệp", A1]], LEN[CELL["tên tệp", A1]]]&". $B$"&TÌM KIẾM[2, 1/[B. B""], HÀNG[B. B]], TÌM KIẾM[2, 1/[B. B""], B. B]]
trở thành
HYPERLINK["[Tìm giá trị cuối cùng trong listv2. xlsx]Siêu liên kết. $B$"&11, "XH"]
và trả về một siêu liên kết đến ô B11
3. Trả về số hàng của giá trị cuối cùng trong một cột
Công thức trong ô D3 trả về một số đại diện cho số hàng của giá trị cuối cùng trong cột B
Công thức trong ô D3
=LOOKUP[2, 1/[B. B""], HÀNG[B. B]]
4. Trả về giá trị liền kề
Tập dữ liệu này có hai cột B và C. Công thức trong ô E3 trả về giá trị tương ứng của giá trị cuối cùng trong cột B từ cột C
Công thức trong ô E3
=LOOKUP[2,1/[B. B""],C. C]
Công thức trả về một giá trị liền kề của giá trị cuối cùng. Thực ra nó không cần liền kề, bạn có thể thay đổi tham chiếu ô [C. C] miễn là điểm bắt đầu và điểm kết thúc của nó giống với tham chiếu ô đầu tiên [B. b]
5. Tìm ô không trống cuối cùng bằng phím tắt
Các bước sau mô tả cách chọn ô không trống cuối cùng trong cột B bằng các phím trên bàn phím
- Nhấn chuột trái vào ô A1 để chọn
- Nhấn và giữ phím CTRL
- Nhấn phím mũi tên xuống một lần. Điều này đưa bạn đến ô cuối cùng trong cột A, nếu tất cả các ô trong cột A đều trống
- Thả tất cả các phím. Nhấn phím mũi tên phải để di chuyển đến ô cuối cùng trong cột B
- Nhấn và giữ phím CTRL
- Nhấn phím mũi tên lên một lần. Thao tác này sẽ đưa bạn đến ô không trống cuối cùng trong cột B. Xem hình trên
Lấy tệp Excel
Tìm-giá-trị-cuối-trong-listv2. xlsx
danh mục tra cứu
Tìm giá trị gần nhất
Bài viết này trình bày các công thức trích xuất số gần nhất trong một phạm vi ô theo một điều kiện. Hình ảnh trên cho thấy […]
Cách thực hiện tra cứu hai chiều
Câu hỏi. Làm cách nào để tôi tìm kiếm dữ liệu trong bảng tham chiếu chéo. Tôi có hàng tiêu đề [tôi. e. 24] […]
Tìm giá trị khớp cuối cùng trong danh sách chưa sắp xếp
Bài viết này trình bày một công thức trả về giá trị khớp cuối cùng dựa trên một điều kiện nhất định. Hình ảnh trên cho thấy […]
Tra cứu hai cột chỉ mục
Công thức trong B14. =INDEX[D3. D6, TÓM TẮT[--[C10=B3. B6], --[C11=C3. C6], HÀNG[D3. D6]-MIN[ROW[D3]. D6]]+1]] Công thức mảng thay thế #1 trong B15. =INDEX[D3. D6, TRẬN ĐẤU[C10&"-"&C11, B3. B6&"-"&C3. C6, 0]] Công thức mảng thay thế […]
Tra cứu với bất kỳ số lượng tiêu chí nào
Bài viết này trình bày một công thức cho phép bạn tìm kiếm một tập dữ liệu bằng bất kỳ số lượng điều kiện nào, tuy nhiên, một […]
Tìm mục nhập dựa trên các điều kiện
Bill Truax yêu cầu. Xin chào Oscar, tôi đang xây dựng một bảng tính để theo dõi các cuộc gọi cho sở cứu hỏa địa phương của mình. Tôi có một […]
Nhận bản sửa đổi mới nhất
Cột B chứa tên tài liệu, nhiều tên trùng lặp. Cột C liền kề có phần sửa đổi tài liệu […]
Các chức năng trong bài viết này
TRA CỨUSIÊU LIÊN KẾTMIDCELLTÌM KIẾMLENROW
Hơn 1300 công thức Excel
Danh mục công thức Excel
danh mục excel
trang chủ
Bài viết cập nhật mới nhất
Hàm Excel
Hơn 300 hàm Excel với thông tin chi tiết bao gồm cú pháp, đối số, giá trị trả về và ví dụ cho hầu hết các hàm được sử dụng trong công thức Excel
Công thức Excel
Hơn 1300 công thức được sắp xếp trong các danh mục con
Bảng Excel
Bảng Excel đơn giản hóa công việc của bạn với dữ liệu, thêm hoặc xóa dữ liệu, lọc, tính tổng, sắp xếp, nâng cao khả năng đọc bằng cách sử dụng định dạng ô, tham chiếu ô, công thức, v.v.
Bộ lọc nâng cao
Cho phép bạn lọc dữ liệu dựa trên giá trị đã chọn , văn bản nhất định hoặc tiêu chí khác. Nó cũng cho phép bạn lọc dữ liệu hiện có hoặc di chuyển các giá trị đã lọc sang vị trí mới
Xác nhận dữ liệu
Cho phép bạn kiểm soát những gì người dùng có thể nhập vào một ô. Nó cho phép bạn chỉ định các điều kiện và hiển thị thông báo tùy chỉnh nếu dữ liệu đã nhập không hợp lệ
Danh sách thả xuống
Cho phép người dùng làm việc hiệu quả hơn bằng cách hiển thị danh sách mà người dùng có thể chọn một giá trị từ đó. Điều này cho phép bạn kiểm soát những gì được hiển thị trong danh sách và nhanh hơn so với nhập vào một ô
Phạm vi được đặt tên
Cho phép bạn đặt tên cho một hoặc nhiều ô, điều này giúp bạn dễ dàng tìm ô bằng cách sử dụng hộp Tên, đọc và hiểu các công thức chứa tên thay vì tham chiếu ô
Trình giải Excel
Bộ giải Excel là một phần bổ trợ miễn phí sử dụng các ô mục tiêu, các ràng buộc dựa trên các công thức trên trang tính để thực hiện phân tích giả định và các vấn đề quyết định khác như hoán vị và kết hợp
biểu đồ
Một tính năng của Excel cho phép bạn trực quan hóa dữ liệu trong biểu đồ
Định dạng có điều kiện
Định dạng ô hoặc giá trị ô dựa trên điều kiện hoặc tiêu chí, có nhiều công cụ Định dạng có điều kiện tích hợp sẵn mà bạn có thể sử dụng hoặc sử dụng công thức định dạng có điều kiện tùy chỉnh
Bảng tổng hợp
Cho phép bạn nhanh chóng tóm tắt lượng dữ liệu khổng lồ theo cách rất thân thiện với người dùng. Tính năng Excel mạnh mẽ này cho phép bạn phân tích, sắp xếp và phân loại dữ liệu quan trọng một cách hiệu quả
VBA
VBA là viết tắt của Visual Basic for Applications và là ngôn ngữ lập trình máy tính do Microsoft phát triển, nó cho phép bạn tự động hóa các tác vụ tốn thời gian và tạo các chức năng tùy chỉnh
macro
Một chương trình hoặc chương trình con được xây dựng trong VBA mà bất kỳ ai cũng có thể tạo. Sử dụng trình ghi macro để nhanh chóng tạo macro VBA của riêng bạn