Vấn đề: Ai đó đã đăng nhập một số dữ liệu. Đối với mỗi nhóm, dữ liệu bắt đầu theo hàng 5 và tiếp tục xuống một số hàng. Có một số điểm dữ liệu khác nhau trong mỗi cột. Tôi cần có được mục cuối cùng trong mỗi cột. Someone has logged some data. For each group, data starts in row 5 and continues down for some number of rows. There are a different number of data points in each column. I need to get the last entry in each column.
- Trả về số cuối cùng trong cột.
Chiến lược: Có nhiều giải pháp cho vấn đề này. Bạn có thể kết hợp phần bù khó sử dụng với đếm, nhưng chủ đề này sẽ chỉ cho bạn cách giải quyết vấn đề bằng phiên bản khớp gần đúng của Vlookup. There are multiple solutions to this problem. You could combine the unwieldy OFFSET with COUNT, but this topic will show you how to solve the problem using the approximate match version of VLOOKUP.
Lật trở lại Hình 402 và Hình 403 nơi bạn đã sử dụng phiên bản khớp gần đúng của VLookUP để tìm tỷ lệ hoa hồng. Bảng này có các mục như 1000, 5000, 10000 và 2000. Khi ai đó đã bán 12.345 đô la, VLookUP sẽ tìm thấy mức giá hoa hồng cho mức 10.000 đô la, vì 10.000 đô la chỉ thấp hơn 12.345 đô la mà bạn đang tìm kiếm.
Bạn có thể tận dụng một kẽ hở trong Excel. Nói rằng bạn đang tìm kiếm một số lớn hơn bất kỳ giá trị nào trong bảng. Khi điều đó xảy ra, Excel sẽ trả lại mục không trống cuối cùng trong bảng. Vì các nhà khoa học, kế toán viên và các đại lý IRS thường xuyên sử dụng vlookup gần đúng luôn sắp xếp dữ liệu tăng dần của họ, trả lại mục nhập không trống cuối cùng trong bảng hoạt động cho họ.
Trong trường hợp này, dữ liệu không được sắp xếp cũng như không nên sắp xếp. Tuy nhiên, nếu bạn yêu cầu Vlookup tìm kiếm một số lượng thực sự lớn, nó sẽ tự động trả lại mục không trống cuối cùng trong cột!
Một số người sẽ đề xuất rằng bạn nên sử dụng 9.9999999999999E+307 làm giá trị tra cứu. Đây là con số lớn nhất có thể trong Excel. Tuy nhiên, thay vì gõ tất cả các ký tự đó, bạn chỉ có thể sử dụng một số lớn hơn bất kỳ ai mong đợi. Ví dụ: nếu bạn làm việc cho một công ty có doanh thu 1 triệu đô la mỗi năm, thì không có cách nào doanh số trong một ngày sẽ vượt quá 100 nghìn đô la. Bạn có thể tìm kiếm một cách an toàn 99999.
Trong công thức dưới đây, tôi đã giữ chìa khóa 9 trong một giây và cuối cùng đã tìm kiếm 9,9 triệu. Không quan trọng chính xác những gì bạn đang tìm kiếm, miễn là nó lớn hơn bất kỳ số nào có thể trong danh sách. Sử dụng = vlookup [9999999, B5: B20,1, đúng].
- Vlookup này trả về giá trị số cuối cùng.
Đây là một cách sử dụng thực sự tuyệt vời của phiên bản hiếm hoi của Vlookup. Như bạn có thể thấy trong cột G, công thức không bị nhầm lẫn bởi các ô trống. Nó sẽ chỉ trả về các giá trị số, do đó, mục ZZZ Errant trong H8 bị bỏ qua. Lỗi #N/A trong F10 bị bỏ qua.
Nếu các mục trong cột là văn bản, thì bạn sẽ tìm kiếm một số văn bản sẽ xảy ra theo thứ tự bảng chữ cái sau bất kỳ văn bản nào bạn có thể mong đợi. Ví dụ: tìm kiếm "Zzzzzz".
- Tìm kiếm Zzzzzz để trả về mục văn bản cuối cùng.
Cột H ở trên minh họa một vấn đề với phương pháp này. Nếu các giá trị có thể chứa văn bản hoặc một số, vlookup sẽ không hoạt động.
Điều gì sẽ xảy ra nếu dữ liệu được bật sang một bên và bạn cần nhận được giá trị cuối cùng từ mỗi hàng? Sử dụng Hlookup thay vì Vlookup.
- Nhận mục cuối cùng từ mỗi hàng.
Chi tiết bổ sung: Bạn không phải đặt, đúng ở cuối bất kỳ công thức nào trong số này. Nếu bạn rời khỏi đối số thứ tư, Excel cho rằng bạn có nghĩa là đúng. Tuy nhiên, vì 99,9% các vlookups trên thế giới sử dụng sai ở cuối, tôi đã đưa ra sự thật ra để giúp tôi nhắc nhở rằng điều gì đó bất thường đang xảy ra với công thức này. You do not have to put the ,TRUE at the end of any of these formulas. If you leave off the fourth argument, Excel assumes that you mean TRUE. However, since 99.9% of the VLOOKUPs in the world use FALSE at the end, I put the TRUE out there to help remind me that something unusual is happening with this formula.
- Microsoft Excel 2019 VBA và Macros
- Mrexcel 2021 - exmasking Excel
- Power Excel với Mrexcel - Phiên bản 2019
Trong ví dụ này, mục tiêu là có được giá trị cuối cùng trong cột B, ngay cả khi & nbsp; dữ liệu có thể chứa các ô trống. Đây là một trong những câu đố xuất hiện thường xuyên trong Excel, bởi vì câu trả lời không rõ ràng. Một cách để làm điều đó là với chức năng tra cứu, có thể xử lý các hoạt động mảng tự nhiên & nbsp; và luôn luôn giả định một trận đấu gần đúng. Điều này làm cho công thức đơn giản và nhỏ gọn đáng ngạc nhiên:
Làm việc từ trong ra ngoài, chúng tôi sử dụng biểu thức logic để kiểm tra các ô trống trong cột B:
Phạm vi B: B là một tham chiếu cột đầy đủ & nbsp; đến mọi ô trong cột B. Ưu điểm chính của tham chiếu cột đầy đủ là nó vẫn còn & nbsp; không bị ảnh hưởng khi các hàng bị xóa hoặc thêm.
Lưu ý: & NBSP; Excel thường thông minh về việc đánh giá các công thức sử dụng các tài liệu tham khảo cột đầy đủ, nhưng bạn nên cẩn thận để quản lý & NBSP; phạm vi đã sử dụng. Trong trường hợp này, bởi vì chúng tôi tạo một "cột ảo" hơn 1 triệu hàng với biểu thức B: B "", phạm vi được sử dụng có thể không quan trọng như vậy, nhưng công thức chắc chắn chậm hơn nhiều so với cùng một công thức với cố định các dãy. Nếu bạn nhận thấy sự cố hiệu suất, hãy thử giới hạn phạm vi. Ví dụ: = Tra cứu [2,1/[B1: B100 ""], B1: B100]
Toán tử logic có nghĩa là không bằng và "" có nghĩa là & nbsp; chuỗi trống, do đó, biểu thức này có nghĩa là cột B không & nbsp; trống. Kết quả là một mảng của các giá trị đúng và sai, trong đó true đại diện cho các ô & nbsp; không trống và sai đại diện cho các ô trống. Mảng này bắt đầu như thế này:
{FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;FALSE...}Tiếp theo, chúng tôi chia số 1 cho mảng. Hoạt động toán học tự động ép buộc đúng với 1 và sai thành 0, vì vậy chúng tôi có:
1/{0;1;0;1;1;1;1;1;1;1;1;1;1;1;0;...}Vì việc chia cho Zero tạo ra một lỗi, & nbsp; kết quả là một mảng bao gồm các lỗi 1S và #div/0:
{#DIV/0!;1;#DIV/0!;1;1;1;1;1;1;1;1;1;1;1;#DIV/0!;...}Mảng này trở thành đối số Tra cứu_array trong Tra cứu. Lưu ý các 1S đại diện cho & nbsp; các ô không trống và lỗi & nbsp; đại diện cho & nbsp; các ô trống.
Tra cứu_value được đưa ra dưới dạng số 2. & nbsp; chúng tôi đang sử dụng 2 làm giá trị tra cứu để buộc tra cứu để quét đến cuối dữ liệu. Tra cứu & nbsp; tự động bỏ qua các lỗi, vì vậy hãy tra cứu sẽ quét qua 1s tìm kiếm 2 sẽ không bao giờ được tìm thấy. Khi nó đi đến cuối mảng, nó sẽ "bước trở lại" cuối cùng, tương ứng với ô không trống cuối cùng.
Cuối cùng, tra cứu trả về giá trị tương ứng trong result_vector [được đưa ra là b: b], do đó, kết quả cuối cùng là: ngày 30 tháng 6 năm 2020.
Khóa & nbsp; Để hiểu được công thức này là nhận ra rằng Lookup_Value của 2 có chủ ý lớn hơn bất kỳ giá trị nào sẽ xuất hiện trong Tra cứu_Vector. & NBSP; Khi không thể tìm thấy : & nbsp; the & nbsp; cuối cùng 1 trong mảng. Điều này hoạt động bởi vì Tra cứu giả định rằng các giá trị trong Lookup_Vector được sắp xếp theo thứ tự tăng dần và luôn thực hiện một trận đấu gần đúng. Khi tra cứu không thể tìm thấy một trận đấu, nó sẽ khớp với giá trị nhỏ nhất tiếp theo.
Nhận giá trị tương ứng
Bạn có thể dễ dàng điều chỉnh công thức tra cứu để trả về một giá trị tương ứng. Ví dụ: để có được giá liên kết với giá trị cuối cùng trong cột B, & NBSP; công thức trong F7 là:
=LOOKUP[2,1/[B:B""],C:C] // get priceSự khác biệt duy nhất là đối số result_vector đã được cung cấp là c: c.
Đối phó với lỗi
Nếu ô không trống cuối cùng chứa lỗi, lỗi sẽ bị bỏ qua. Nếu bạn muốn trả về một lỗi xuất hiện cuối cùng trong phạm vi & nbsp; bạn có thể điều chỉnh công thức để sử dụng isblank và không phải là các chức năng như thế này:
Giá trị số cuối cùng
Để có được giá trị số cuối cùng, bạn có thể thêm hàm isNumber như thế này:
Giá trị không trống cuối cùng, khác không
Để kiểm tra xem giá trị cuối cùng không trống chứ không phải bằng không, bạn có thể điều chỉnh công thức với logic boolean như thế này:
=LOOKUP[2,1/[[B:B""]*[B:B0]],B:B]Nếu bạn nhận thấy các vấn đề về hiệu suất, giới hạn phạm vi & nbsp; [nghĩa là sử dụng B1: B100, B1: B1000, thay vì B: B].
Vị trí của giá trị cuối cùng
Để có được số hàng của giá trị cuối cùng, bạn có thể sử dụng một công thức như thế này: ________ 100
Chúng tôi sử dụng chức năng hàng để cung cấp các số hàng cho cột B để tra cứu làm result_vector để lấy số hàng cho trận đấu cuối cùng.
Làm thế nào để bạn tìm thấy mục cuối cùng trong Excel?
Tìm lần xuất hiện cuối cùng - sử dụng hàm tối đa..Hàm Max được sử dụng để tìm số hàng của tên khớp cuối cùng. ....SumProduct được sử dụng để đảm bảo rằng bạn không phải sử dụng Control + Shift + Enter, vì SumProduct có thể xử lý các công thức mảng ..Chức năng chỉ mục hiện được sử dụng để tìm ngày cho tên khớp cuối cùng ..Làm thế nào để bạn trả về mục cuối cùng trong một cột trong Excel?
Bạn có thể sử dụng chức năng tra cứu để tìm ô không trống cuối cùng trong một cột.Hãy đi qua công thức này.Phần của công thức B: B Tiết trả về một mảng chứa các giá trị đúng và sai: {false, true, true,}, kiểm tra từng ô trong cột B là trống [sai].use the LOOKUP Function to find the last non-empty cell in a column. Let's walk through this formula. The part of the formula B:B”” returns an array containing True and False values: {FALSE, TRUE, TRUE,…}, testing each cell in column B is blank [FALSE].Làm thế nào để tôi tìm thấy lịch sử chỉnh sửa cuối cùng trong Excel?
Xem bảng tính lịch sử..Nhấp vào Đánh giá> Theo dõi thay đổi> Làm nổi bật các thay đổi.....Trong phần nổi bật thay đổi, chọn hộp kiểm khi nào và sau đó, trong danh sách khi nào, nhấp vào tất cả ..Xóa hộp kiểm ai và ở đâu ..Chọn danh sách thay đổi trên hộp kiểm Tờ mới ..Bấm OK ..Làm cách nào để tìm văn bản cuối cùng trong một cột trong Excel?
Nhận giá trị văn bản cuối cùng trong một cột..=INDEX[range,MATCH["*",range,-1]].=INDEX[range,MATCH["*",range,-1]].=INDEX[B4:B15,MATCH["*",B4:B15,-1]].