Excel tìm lần xuất hiện cuối cùng của một giá trị trong một hàng

Bạn sẽ cần sử dụng Công thức mảng để đạt được điều này. Phần sau đây sẽ trả về giá trị thấp nhất "cao nhất" nếu bạn đang sử dụng cột B. R là cột dữ liệu và cột T là cột Tuần thấp điểm. Bạn cần nhập công thức này dưới dạng công thức mảng [CSE] bằng cách sao chép và dán nó vào ô T2 rồi nhấn Ctrl+Shift+Enter

=IFERROR[INDEX[$A$1:$R$1,1,SMALL[IF[B2:R2=MIN[B2:R2],COLUMN[B2:R2],FALSE],COUNTIF[B2:R2,MIN[B2:R2]]]],""]

Giải trình

Đầu tiên, COUNTIF[B2:R2,MIN[B2:R2]] sẽ đếm số lần giá trị nhỏ nhất xảy ra. Điều này là cần thiết vì một ______7 đơn giản sẽ chỉ lấy giá trị tối thiểu đầu tiên, không phải giá trị tối thiểu thứ n

Tiếp theo, IF[B2:R2=MIN[B2:R2],COLUMN[B2:R2],FALSE] là phần công thức mảng thực sự của phương trình [lý do tại sao bạn cần sử dụng Ctrl+Shift+Enter]. Nó sẽ chỉ trả về các tham chiếu khi bạn có giá trị tối thiểu và trả về FALSE cho tất cả các mục khác lớn hơn giá trị tối thiểu. Bây giờ, bạn có một mảng chỉ các cột đánh số các giá trị nhỏ nhất trong tập dữ liệu

Sau đó,

ROW[names]-MIN[ROW[names]]+1

0 sẽ lấy số cột của các giá trị nhỏ nhất và tìm lần xuất hiện mới nhất của giá trị này, xuất phát từ mã COUNTIF[B2:R2,MIN[B2:R2]]

Bây giờ, khi đã biết số cột của lần xuất hiện nhỏ nhất cuối cùng, bạn có thể sử dụng hàm

ROW[names]-MIN[ROW[names]]+1

2 để tìm giá trị trong hàng đầu tiên

ROW[names]-MIN[ROW[names]]+1

3

Cuối cùng,

ROW[names]-MIN[ROW[names]]+1

4 sẽ hiển thị hàng Tuần thấp điểm nếu hàm COUNTIF[B2:R2,MIN[B2:R2]]0 tìm thấy kết quả khớp, ngược lại sẽ hiển thị ô trống. Điều này có thể được sử dụng để sao chép công thức mảng này xuống dưới trang cho các hàng mà bạn chưa có người dùng

Ý chính của công thức này là chúng ta xây dựng một danh sách các số hàng cho một phạm vi nhất định, khớp với một giá trị, sau đó sử dụng hàm MAX để lấy số hàng lớn nhất, tương ứng với giá trị khớp cuối cùng. Chúng tôi đang sử dụng "tên" phạm vi được đặt tên chỉ để thuận tiện

Làm việc từ trong ra ngoài, phần này của công thức sẽ tạo ra một tập hợp các số hàng tương đối

ROW[names]-MIN[ROW[names]]+1

Kết quả của biểu thức trên là một mảng các số như thế này

{1;2;3;4;5;6;7;8}

Để ý ta được 8 số, tương ứng với 8 hàng trong bảng. Xem trang này để biết chi tiết về cách hoạt động của phần này trong công thức

Với mục đích của công thức này, chúng tôi chỉ muốn số hàng cho các giá trị phù hợp, vì vậy chúng tôi sử dụng hàm IF để lọc các giá trị như vậy

IF[names=H5,ROW[names]-MIN[ROW[names]]+1]

Điều này dẫn đến một mảng trông như thế này

{1;FALSE;FALSE;4;FALSE;FALSE;7;FALSE}

Lưu ý rằng mảng này vẫn chứa tám mục. Tuy nhiên, chỉ những số hàng có giá trị trong phạm vi được đặt tên là "tên" bằng với "amy" mới tồn tại [i. e. 1, 4, 7]. Tất cả các mục khác trong mảng là SAI, vì chúng không đạt kiểm tra logic trong hàm IF

Cuối cùng, hàm IF cung cấp mảng này cho hàm MAX. MAX trả về giá trị cao nhất trong mảng, số 7, tương ứng với số hàng cuối cùng có tên là "amy". Khi chúng tôi biết số hàng phù hợp cuối cùng, chúng tôi có thể sử dụng INDEX để truy xuất giá trị tại vị trí đó

Thứ hai đến cuối cùng, v.v.

Để có được vị trí thứ hai đến cuối cùng, thứ ba đến cuối cùng, v.v. bạn có thể chuyển từ chức năng MIN sang chức năng LỚN như thế này

{=LARGE[IF[criteria,ROW[rng]-MIN[ROW[rng]]+1],k]}

trong đó k đại diện cho "lớn thứ n". Ví dụ: để có được trận đấu thứ hai đến trận đấu cuối cùng trong ví dụ trên, bạn có thể sử dụng

MarkVMcCullagh

hội đồng quản trị thường xuyên

Tham gia ngày 22 tháng 10 năm 2002Messages72

• Ngày 23 tháng 10 năm 2002

• #1

Cách dễ nhất để tìm lần xuất hiện cuối cùng của một giá trị trong bảng bằng hàm là gì. Tôi muốn tránh VB nếu có thể và lưu ý số hàng và sử dụng nó trong hàm chỉ mục để báo cáo văn bản liền kề với lần xuất hiện cuối cùng đó. Tôi thường sử dụng đối sánh nhưng đối sánh chỉ ghi lại trận đấu đầu tiên chứ không phải trận đấu cuối cùng.

Cảm ơn trước

 

D

đk

MrExcel MVP

Tham gia ngày 15 tháng 2 năm 2002Messages2,942

• Ngày 23 tháng 10 năm 2002

• #2

Chào Mark,

Một cách để làm việc này là sử dụng công thức mảng. Công thức mảng phải được nhập bằng Ctrl-Shift-Enter, không chỉ nhập.

Giả sử bạn đang tìm kiếm A1. A20 cho hàng cuối cùng chứa 11, bạn có thể sử dụng công thức này. -

=MAX[ROW[1. 20]*[A1. A20=11]]

Rõ ràng, hãy thay đổi để phù hợp với nhu cầu của bạn.

Để biết thêm thông tin về công thức mảng, hãy xem tại đây. -

http. //www. thư điện tử. com/excel/mảng-bobumlas. html

 

MarkVMcCullagh

hội đồng quản trị thường xuyên

Tham gia ngày 22 tháng 10 năm 2002Messages72

• Ngày 23 tháng 10 năm 2002

• #3

Cảm ơn Dan

Lúc đầu, tôi nghĩ vì tôi chưa kể cho bạn toàn bộ câu chuyện nên sẽ không giải quyết được vấn đề của tôi nhưng tôi đã nghiên cứu và hóa ra là có. . 2]*[B$2. B3=C3]] nơi các hàng thay đổi khi tôi di chuyển xuống dưới bảng.

 

Jonmo1

MrExcel MVP

JoinedOct 12, 2006Messages44,061

• 30 Tháng chín 2013

• #5

Xem xét mục tiêu cuối cùng của việc trích xuất một giá trị trong cột liền kề dựa trên lần xuất hiện cuối cùng.

Bạn có thể bỏ qua Bước giữa của việc 'ghi chú' số hàng và sử dụng nó trong chỉ mục.

Thử
=LOOKUP[2,1/[B$2. B3=C3],A$2. A3]

Ở đâu A$2. A3 là cột liền kề mà bạn muốn trích xuất một giá trị từ đó.

 

C

gò má3

hội đồng quản trị thường xuyên

JoinedJun 13, 2007Messages85

• 3 Tháng hai, 2014

• #7

MarkVMcCullagh nói

Cảm ơn Dan

Lúc đầu, tôi nghĩ vì tôi chưa kể cho bạn toàn bộ câu chuyện nên sẽ không giải quyết được vấn đề của tôi nhưng tôi đã nghiên cứu và hóa ra là có. . 2]*[B$2. B3=C3]] nơi các hàng thay đổi khi tôi di chuyển xuống dưới bảng.

Bấm để mở rộng

Sắp xếp công việc cho tôi, với một cột bổ sung. Cảm ơn. Và vâng, vẫn có liên quan sau ngần ấy năm

 

V

vince42

Thành viên mới

JoinedMay 30, 2014Messages4

• 6 Tháng hai, 2015

• #số 8

Xin chào,

Jonmo1 đã nói.

=LOOKUP[2,1/[B$2. B3=C3],A$2. A3]

Bấm để mở rộng

I just wondered what the parameters "2" and "1/" actually do and whether there are other parameters to achieve the same result.

 

A

Agincourt2

Thành viên mới

JoinedMay 13, 2015Messages3

• 13 Tháng Năm, 2015

• #9

Vince42 anh ấy nói

Xin chào,



Tôi chỉ thắc mắc không biết thực tế các thông số "2" và "1/" có tác dụng gì và liệu có các thông số khác đạt được kết quả tương tự hay không.

Bấm để mở rộng

Tra cứu hoạt động tương tự như vlookup[mệnh đề đúng] trong đó nó tìm kiếm kết quả khớp đầu tiên, nếu không tìm thấy, công thức sẽ quay lại bước trả lời hợp lệ trước đó. "2" là câu trả lời mà công thức trên đang tìm kiếm, "1/" chia kết quả thành 2 nhóm [1] hoặc [#DIV/0. ]. Vì nhóm thứ hai không hợp lệ và "2" sẽ không được trả về trong kết quả, nên công thức sẽ trả về [1] hoặc lần xuất hiện cuối cùng đang được tìm kiếm.

Hy vọng điều này sẽ hữu ích và theo như tôi thấy thì chỉ có thể thay đổi tham số đầu tiên để đạt được kết quả tương tự.

 

L

l_eonandr

Thành viên mới

JoinedFeb 18, 2016Messages25

• 31 Tháng ba 2016

• #10

Không hiệu quả với tôi??
Bạn có thể giải thích lỗi "1/B$2. B3=C3" C3 là gì? Có giá trị nào phải nhập vào trong C3 không?
Tôi nhận được #N/A trong ô Tôi nhập công thức vào.
Tôi đã thử công thức như sau đó thay đổi nó để phù hợp với ứng dụng của tôi, "=LOOKUP[F1,[1/A$20. A16000]=D1,B$20. B16000]", trong đó tôi đã nhập giá trị mà tôi muốn tìm kiếm trong F1 và cột để tìm kiếm là A. A
và giá trị tôi muốn trả về nằm ở Cột B. B. Nhân tiện, dữ liệu bắt đầu ở Hàng 20 và hàng cuối cùng không được biết trong một số trường hợp, trường hợp thử nghiệm này nó đã được biết.
Bạn có thể giúp mình hiểu công thức này được không. Cảm ơn