Google Sheets có nhiều tùy chọn công thức để lấy tiêu đề cột có giá trị nhỏ nhất trong một hàng. Trong bài viết này, bạn sẽ được biết về ba công thức tốt nhất: một công thức không sử dụng mảng (Filter) và hai công thức mảng (Vlookup và Lambda). Chúng hoạt động khác nhau một chút.
Nếu trong một hàng có nhiều giá trị nhỏ nhất, công thức không sử dụng mảng (Filter) và một trong hai công thức mảng (Lambda) sẽ trả về nhiều tiêu đề cột. Trong khi đó, công thức mảng khác (Vlookup) chỉ trả về tiêu đề cột đầu tiên.
Ưu điểm của công thức mảng là chúng sẽ trả về tiêu đề(s) giá trị nhỏ nhất trong mỗi hàng.
Vui lòng lưu ý rằng đây đề cập đến tiêu đề cột, có nghĩa là nhãn trường ở hàng trên cùng của bảng của bạn.
Công thức để trả về tiêu đề cột có giá trị nhỏ nhất trong Google Sheets
Giả sử bạn có năm nhân viên đang làm việc dưới quyền của bạn. Bạn muốn đánh giá doanh số bán hàng hàng ngày của họ và tìm ra ai là người biểu hiện kém nhất mỗi ngày.
Đây là dữ liệu mẫu trong tay.
Bạn có thể tìm thấy tên của nhân viên trên tiêu đề cột (hàng tiêu đề) và doanh số hàng ngày của họ trong các cột tương ứng.
Chúng ta có thể sử dụng công thức Filter sau đây trong ô H2. Kéo xuống đến ô H10 để lấy tiêu đề cột có giá trị nhỏ nhất trong mỗi hàng.
=ifna( filter( $B$1:$F$1, B2:F2=minifs(B2:F2,B2:F2,">0"), B2:F2<>" ) )
Bạn có thể giải thích công thức Google Sheets này được không?
Ừ, đây là cách nó hoạt động!
- MINIFS trả về giá trị nhỏ nhất của số lượng hàng hóa trong B2:F2, loại bỏ số 0 và giá trị trống.
- Công thức FILTER lọc các tiêu đề giá trị nhỏ nhất (tên nhân viên) khớp với kết quả MINIFS.
- Trong trường hợp hàng trống hoặc chỉ chứa 0, MINIFS sẽ trả về 0. Điều kiện thứ hai trong FILTER, tức là B2:F2<>””, là để trả về #N/A trong trường hợp đó.
- IFNA loại bỏ lỗi trên.
- Khi bạn kéo công thức xuống, nó được áp dụng cho các hàng khác.
Xem ví dụ này trực tiếp (GIF).
Nếu bạn là người đam mê Google Sheets, bạn có thể muốn biết về ‘quy tắc’ mà tôi đã sử dụng để định dạng phạm vi B2:F10. Đây là nó.
=and(len($H2),$B2:$F2=minifs($B2:$F2,$B2:$F2,">0"))
Bạn có thể sử dụng quy tắc này trong Format > Conditional formatting.
Công thức mảng 1 để lấy tiêu đề cột có giá trị nhỏ nhất – VLOOKUP
Chúng ta có thể sử dụng công thức mảng Vlookup để lấy tiêu đề cột của giá trị nhỏ nhất trong Google Sheets. Bây giờ có một tùy chọn Lambda tốt hơn. Chúng ta sẽ thử nó sau.
Nó có một nhược điểm. Trong ví dụ trên, công thức lọc sẽ trả về các tên “Malcolm” và “Edgar” ở hàng #6 với giá trị nhỏ nhất, tức là 1, lặp lại hai lần.
Công thức mảng của tôi sau đây chỉ trả về tiêu đề nhỏ nhất đầu tiên, tức là “Malcolm”.
=ArrayFormula( ifna( if(len(A2:A), vlookup( row(A2:A)&"~"& dmin( transpose(if(A2:F>0,A2:F,)), sequence(rows(A2:A),1), {if(,,);if(,,)} ), Query(split(flatten(row(A2:A)&"~"&if(B2:F>0,B2:F,)&"🐟"&B1:F1),"🐟"), "Select * where Col1 is not null and Col2 is not null") ,2,0 ), ) ) )
Nếu bạn muốn sử dụng công thức mảng này để lấy tiêu đề cột có giá trị nhỏ nhất trong mỗi hàng trong Google Sheets, bạn có thể muốn đọc giải thích công thức.
Lưu ý: nếu có nhiều giá trị nhỏ nhất tồn tại trong một hàng, công thức mảng sẽ chỉ trả về tiêu đề nhỏ nhất của sự xuất hiện đầu tiên từ trái sang phải.
Giải thích công thức
Công thức chung: vlookup(row_nos&min_each_row,unpivot_table,2,0)
**Cú pháp Vlookup: VLOOKUP(search_key, range, index, [is_sorted])`
Chúng ta đã sử dụng VLOOKUP để lấy tiêu đề cột của giá trị nhỏ nhất, loại trừ 0, trong mỗi hàng. Đây là các biểu thức/tham chiếu được sử dụng trong đối số.
SEARCH_KEY: row_nos&min_each_row
RANGE: unpivot_table
INDEX: 2
IS_SORTED: 0
Tôi biết là tôi phải giải thích khóa tìm kiếm và phạm vi trong Vlookpup cho bạn hiểu cách nó hoạt động.
SEARCH_KEY
Khóa tìm kiếm ở đây là sự kết hợp của giá trị nhỏ nhất và số hàng (row_nos&min_each_row) trong mỗi hàng. Đây là một phần của công thức.
row(A2:A10)&"~"& dmin( transpose(if(A2:F10>0,A2:F10,)), sequence(rows(A2:A10),1), {if(,,);if(,,)} )
Nó sẽ trả về kết quả sau nếu bạn sử dụng nó như một ArrayFormula.
Trong phần này của công thức, tôi đã sử dụng DMIN theo logic chi tiết ở đây – MIN hàng theo hàng bằng DMIN trong Google Sheets.
RANGE
Phạm vi là dữ liệu không xoá B1:F10. Khi làm như vậy, số hàng từ A2:A10 được kết hợp với các giá trị (số lượng hàng hóa) trong B2:F10.
Đây là phần liên quan từ công thức (các phạm vi đã đóng ở đây từ A2:A và B2:B đến A2:A10 và B2:B10). Hàm FLATTEN kết hợp với hàm SPLIT đã làm công việc đó.
Query(split(flatten(row(A2:A10)&"&" &if(B2:F10>0,B2:F10,)&"🐟" &B1:F1),"🐟"), "Select * where Col1 is not null and Col2 is not null")
Nó sẽ hoạt động với công thức mảng như một phần mềm độc lập trong Sheets. Đây là kết quả đó.
Công thức mảng 2 để lấy tiêu đề cột có giá trị nhỏ nhất – LAMBDA
Google Sheets hiện có khả năng mở rộng các công thức không sử dụng mảng cho mỗi hàng. Điều này có thể thực hiện bằng cách sử dụng các chức năng trợ giúp LAMBDA (LHF).
Chúng tôi sẽ sử dụng LHF BYROW để mở rộng công thức không sử dụng mảng dựa trên bộ lọc của chúng tôi. Điều đó có nghĩa là chúng ta có thể lấy tiêu đề cột có giá trị nhỏ nhất trong mỗi hàng trong Google Sheets.
=byrow(B2:F,lambda(r, join(", ",ifna(filter($B$1:$F$1,r=minifs(r,r,">0"),r<>"")))))
Phần này kết hợp tên cột (tiêu đề) nếu có nhiều sự xuất hiện của giá trị nhỏ nhất (ví dụ: hàng số 6).
Vậy là tất cả. Cảm ơn đã đọc. Chúc bạn vui vẻ!
Liên quan: Tiêu đề cột có giá trị lớn nhất trong Google Sheets bằng công thức mảng.
