Để lấy tên tiêu đề cột chứa giá trị lớn nhất trong Google Sheets, chúng ta có thể sử dụng công thức Hlookup kết hợp với Max. Tuy nhiên, điều gì xảy ra khi bạn muốn lặp lại cùng quy trình trên mỗi hàng trong bảng của bạn?
Tất nhiên, Hlookup có thể trả về kết quả trong nhiều hàng, nhưng đồng thời, Max không thể làm điều đó. Để làm cho vấn đề phức tạp hơn, chúng ta cần kết quả từ hàng tiêu đề. Điều đó khiến hy vọng sử dụng Hlookup tan biến.
Vậy chúng ta cần phải dựa vào công thức kéo xuống để lấy tên tiêu đề cột của giá trị lớn nhất trong mỗi hàng trong Google Sheets?
Không đúng! Tôi có một công thức mảng để lấy tên tiêu đề cột của giá trị lớn nhất trong Google Sheets. Trong bài viết này, tôi sẽ chia sẻ với bạn công thức đó. Trước tiên, để tôi bắt đầu với công thức kéo xuống.
Sử dụng công thức kéo xuống kết hợp Max và Hlookup
Giả sử một công ty giao dịch có ba kho và duy trì số lượng hàng hóa theo từng kho trong một Bảng tính Google đơn giản. Cột A chứa tên hàng hóa và các cột B đến D chứa số lượng hàng hóa được lưu trữ trong từng kho.
Sử dụng tập dữ liệu này, bạn có thể dễ dàng tìm giá trị lớn nhất của từng mặt hàng được lưu trữ trong kho nào. Chúng ta chỉ cần tìm tiêu đề cột của giá trị lớn nhất.
Trong ví dụ trên, chỉ có năm mặt hàng trong các hàng 2 đến 6.
Ở đây, chúng ta có thể sử dụng công thức không dùng mảng để trả về tên kho dựa trên số lượng hàng hóa lớn nhất. Trong ô F2, hãy chèn công thức MAX sau đây để trả về giá trị lớn nhất (số lượng) trong hàng đó.
=max(B2:D2)
Ở ô G2, chèn công thức Hlookup sau đây để trả về tiêu đề (số kho) tương ứng với giá trị lớn nhất (số lượng).
=hlookup(F2,{A2:D2;$A$1:$D$1},2,0)
Sao chép hoặc kéo xuống cả hai công thức để có được tiêu đề cột của giá trị lớn nhất trong mỗi hàng trong Google Sheets.
Lưu ý: Nếu bạn không muốn thấy các số lớn nhất trong cột F, hãy bao gồm công thức F2 trong công thức Hlookup như sau.
=hlookup(max(B2:D2),{A2:D2;$A$1:$D$1},2,0)
Công thức mảng để trả về tiêu đề cột của giá trị lớn nhất trong Google Sheets
Tôi biết, có lẽ bạn đang tìm kiếm công thức mảng để lấy tên tiêu đề cột của giá trị lớn nhất trong mỗi hàng của một bảng. Bạn có thể đã gặp hai khó khăn – Mở rộng công thức Max trên và sau đó là Hlookup. Hãy gọi nó là Phần I và Phần II cho mục đích giải thích.
Cập nhật: Đã thêm một giải pháp lambda mới ở cuối hướng dẫn này dưới tiêu đề “Lambda để trả về Tiêu đề Cột tối đa trong Mỗi Hàng.”
Phần I – Giải pháp sử dụng Dmax
Để mở rộng kết quả lớn nhất, chúng ta có thể sử dụng DMAX như đã giải thích ở đây – Trả về Hai Giá Trị Lớn Nhất trong Mỗi Hàng trong Google Sheets. Sau khi làm trống F1:F, nhập công thức DMAX sau đây vào ô F1.
= {“Max”; ArrayFormula( if(len(A2:A), DMAX( transpose({{“Row”;sequence(rows(A2:A1000),1)},B1:D1000}), sequence(1000,1,2), transpose({“Row”,B1:D1}) ), ) ) }
Tôi đã viết công thức để bao gồm tới hàng số 1000.
Khi bạn có nhiều bản ghi hơn, hãy đừng quên thay đổi 1000 trong phạm vi ô A2:A1000 và D1000 thành 2000 (theo yêu cầu).
Hãy nhớ rằng, bộ dữ liệu lớn có thể gây ra các vấn đề về hiệu suất trong công thức mảng trên.
Phần II – Công thức mảng để Lấy Tiêu đề Cột của Giá trị Lớn nhất trong Google Sheets
Tương tự như phần I, làm trống ô G1:G. Sau đó, nhập công thức sau vào ô G1.
= {“Header”; ArrayFormula( ifna( if(len(F2:F1000), vlookup( row(A2:A1000)&”~”&F2:F1000, Query(split(flatten(row(A2:A1000)&”~”&B2:D1000&”🐟”&B1:D1),”🐟”), “Select * where Col1 is not null and Col2 is not null”) ,2,0 ), ) ) ) }
Nó sẽ trả về tiêu đề cột của giá trị lớn nhất trong Google Sheets (đọc tiêu đề cột như số kho) lên đến hàng 1000. Điều đó có nghĩa là đoạn cuối trong tiêu đề trên cũng áp dụng ở đây.
Tôi không muốn để bạn trong bóng tối mà không chia sẻ cách công thức hoạt động (chúng ta có thể gọi nó là logic công thức) hoặc cách tôi đã kết hợp chúng và mục đích của chúng.
Phần II – Giải thích công thức
Cách tốt nhất để hiểu công thức trên là hạn chế mở rộng công thức đến hàng số 6, vì đây là hàng cuối cùng chứa bất kỳ giá trị nào. Hành động đó sẽ giúp chúng ta loại bỏ một số chuỗi không mong muốn khỏi công thức và từ đó rút ngắn nó.
Dưới đây là công thức cần trả về tiêu đề cột của các giá trị lớn nhất từ hàng thứ hai đến hàng thứ sáu. Vì nó không chứa nhãn trường, khi kiểm tra, bạn nên chèn nó vào ô G2, không phải ở ô G1 như đã nêu ở trên.
=ArrayFormula( vlookup( row(A2:A6)&”~”&F2:F6, Query(split(flatten(row(A2:A6)&”~”&B2:D6&”🐟”&B1:D1),”🐟”), “Select * where Col1 is not null and Col2 is not null”),2,0 ) )
Trên là một công thức VLOOKUP. Cú pháp và các đối số được sử dụng như sau.
Cú pháp
VLOOKUP(search_key, range, index, [is_sorted])
Các đối số
Search_key: row(A2:A6)&”~”&F2:F6
Công thức này đơn giản chỉ thêm số ROW với các giá trị lớn nhất trong cột F. Dấu ngã là được chèn làm phân tách giữa số ROW và các giá trị lớn nhất.
range – Query(split(flatten(row(A2:A6)&”~”&B2:D6&”🐟”&B1:D1),”🐟”),”Select * where Col1 is not null and Col2 is not null”)
Kết quả trên là phần hồn của công thức trả về tiêu đề cột của các giá trị lớn nhất trong mỗi hàng trong Google Sheets. Hãy xem kết quả.
Bạn có thể thấy công thức thêm số ROW vào từng giá trị trong B2:D6 (đã sử dụng dấu ngã tương tự). Hơn nữa, nó có một cột bổ sung chứa các tiêu đề cột tương ứng.
Thực ra, tôi đã sử dụng hàm FLATTEN để chuyển dữ liệu từ B1:D6. Bạn có thể tìm thêm thông tin tại đây – Một Công thức Đơn giản để Chuyển Dữ liệu thành Dạng Không gập chéo trong Google Sheets.
QUERY không bắt buộc trong trường hợp trên. Bạn có thể sử dụng Luôn luôn cả mình như dưới đây.
=ArrayFormula(split(flatten(row(A2:A6)&”~”&B2:D6&”🐟”&B1:D1),”🐟”))
Query được bao gồm để loại bỏ các giá trị trống khi chúng ta sử dụng phạm vi như A2:A1000 và B2:D1000.
index – 2
is_sorted – 0 (phạm vi không được sắp xếp)
Vlookup tìm kiếm search_key trong phạm vi trên và sẽ trả về giá trị khớp từ cột thứ hai (index # 2).
Lambda để trả về Tiêu đề cột tối đa trong Mỗi Hàng
Công thức trên có hai nhược điểm.
Nó là một công thức hơi phức tạp, nhưng điều đó chấp nhận được vì nó hoạt động trong mọi hàng.
Nhược điểm thứ hai là khá rõ ràng.
Ví dụ: Tôi có cùng số lượng hàng hóa lớn nhất trong hai hoặc nhiều kho cho một mặt hàng. Tôi muốn tất cả các tên của những kho đó. Làm thế nào để lấy chúng?
Công thức trên sẽ không hỗ trợ loại kết quả này. Đây là công thức mảng tốt nhất hoạt động mà không phụ thuộc vào một hoặc nhiều giá trị lớn nhất.
=byrow(B2:D,lambda(row, if(counta(row)=0,,join(“, “,filter($B$1:$D$1,row=max(row))))))
Nhập công thức này vào ô I2 của cột I trống.
Công thức trợ giúp Lambda BYROW này sẽ trả về tiêu đề cột tối đa trong mỗi hàng.
Làm thế nào công thức này hoạt động?
Tôi đã sử dụng tên row trong công thức lambda trên để xác định các hàng trong B2:D (phạm vi). Trong mỗi hàng, nó lấy hàng tương ứng.
- Công thức ROW trả về giá trị lớn nhất của hàng đầu tiên.
- Công thức FILTER lọc tiêu đề bằng cách sử dụng các giá trị trên trả về trong điểm 1 như tiêu chí.
- JOIN kết hợp kết quả của điểm 2.
- BYROW Lambda hoạt động trong mọi hàng. Vì vậy, hàng sẽ là B2:D2 trong hàng đầu tiên, B3:D3 trong hàng thứ hai và cứ tiếp tục như vậy.
Như vậy, chúng ta có thể sử dụng BYROW để trả về tiêu đề cột của các giá trị lớn nhất trong mỗi hàng trong Google Sheets.
Sample_Sheet_21521
[Tham khảo]{Crawlan.com}:
- Lookup và Lấy Tiêu đề Cột trong Google Sheets.
- Tìm N giá trị Lớn Nhất trong Mỗi Hàng và Trả về Tiêu đề trong Google Sheets.
- MIN theo từng Hàng sử dụng DMIN trong Google Sheets.
