Google Sheets – Đánh dấu giá trị lớn nhất trong mỗi nhóm

GiGiKing·Tháng tư 2, 2024·0 comments

Bạn muốn tìm hiểu cách tự động đánh dấu giá trị lớn nhất trong mỗi nhóm trên Google Sheets? Nếu việc viết công thức có vẻ khó khăn đối với bạn, đừng lo! Bài viết này sẽ giúp bạn. Tôi có công thức để sử dụng trong việc định dạng có điều kiện trên Google Sheets để đánh dấu giá trị lớn nhất trong mỗi nhóm.

Với công thức này, chúng ta có thể thay đổi màu sắc ô, màu chữ hoặc gạch ngang giá trị lớn nhất theo từng nhóm.

Bạn có thể áp dụng định dạng ô này ở đâu?

Dưới đây là một số ví dụ:

  1. Giả sử bạn nhận được nhiều mục nhập Form vào mỗi ngày. Với quy tắc này, bạn có thể đánh dấu mục cuối cùng được gửi vào mỗi ngày bằng cách sử dụng cột timestamp.

  2. Ví dụ khác là báo cáo doanh số bán hàng. Trong báo cáo doanh số tổng hợp, chúng ta có thể tìm người bán hàng có doanh số bán hàng cao nhất trong tên của anh ấy vào mỗi ngày.

  3. Trong một tập dữ liệu liên quan đến một số sự kiện thể thao, ví dụ như đua xe ô tô, chúng ta có thể đánh dấu người dẫn đầu trong mỗi vòng đua.

Đó là một ý tưởng hay để tìm hiểu cách đánh dấu giá trị lớn nhất trong mỗi nhóm.

Với thông tin trong tay, tôi sử dụng hình ảnh dưới đây để minh họa quy tắc định dạng điều kiện trong phạm vi B2: B.

Hình ảnh

Tôi đã sử dụng hai quy tắc công thức tùy chỉnh cho việc này. Quy tắc đầu tiên dùng để giữ nguyên các ô trống trong cột B không được định dạng bởi quy tắc thứ hai, quy tắc chính.

Quy tắc 1:

=isblank($B2)=true

Quy tắc 2:

=isna(vlookup(A2&B2,ArrayFormula(index(sortn(sort({$A$2:$A,$B$2:$B},1,0,2,0),9^9,2,1,0),0,1)&index(sortn(sort({$A$2:$A,$B$2:$B},1,0,2,0),9^9,2,1,0),0,2)),1,0))=false

Tôi biết bạn càng muốn biết cách sử dụng công thức này trong tệp Google Sheets của bạn để định dạng điều kiện giá trị lớn nhất theo từng nhóm.

Ngoài ra, có một số bạn có thể muốn biết cách giải thích công thức cũng như logic. Vì vậy, hãy tiếp tục!

Cách áp dụng công thức như một quy tắc định dạng điều kiện trong Google Sheets

Hãy thảo luận về việc áp dụng hai quy tắc trên. Điều này cũng giúp bạn tìm hiểu cách áp dụng công thức làm quy tắc tùy chỉnh trong định dạng điều kiện.

Điều quan trọng đầu tiên là thứ tự quy tắc tùy chỉnh. Nếu bạn sử dụng quy tắc tùy chỉnh (nhiều hơn một công thức), hãy giữ nguyên thứ tự “được cung cấp” của chúng!

Bước:

  1. Chọn phạm vi B2: B, nghĩa là từ B2 đến ô cuối cùng trong cột đó. Trên bảng tính của tôi, nó là B2: B11.
  2. Tiếp theo, chúng ta mở bảng điều khiển định dạng điều kiện từ menu Format> Conditional formatting.
  3. Do bạn đã chọn B2: B11 là phạm vi, các thiết lập mặc định sẽ giống như dưới đây.
  4. Điều này đặt ra một câu hỏi. Liệu chúng ta có thể sử dụng phạm vi vô hạn trong định dạng điều kiện trong Google Sheets không?
  5. Đúng! Tất nhiên. Để chọn một phạm vi vô hạn, chỉ sử dụng các hàng hiện có như tôi đã làm (B2: B11).
  6. Không sử dụng B2: B (phạm vi mở) vì không được yêu cầu. Google Sheets sẽ tự động mở rộng phạm vi B2: B11 dựa trên các hàng mới được thêm vào.
  7. Nhấp vào menu thả xuống “Không trống” và chọn “Công thức tùy chỉnh là”. Bạn sẽ thấy một trường trống. Sao chép và dán quy tắc 1 ở trong trường trống.
  8. Dưới trường, bạn có thể thấy “Phong cách định dạng”. Nhấp vào biểu tượng hình thuộc về (màu sắc nền) và chọn “Không có”.
  9. Khi hoàn thành, nhấp vào “Thêm một quy tắc khác” và nhập quy tắc 2 vào trường công thức tùy chỉnh.
  10. Lần này, hãy chọn màu ưa thích của bạn (ngoại trừ màu trắng / không có) để đánh dấu giá trị lớn nhất và nhấp vào “Hoàn thành”.

Thay đổi cột nhóm và cột giá trị – Làm thế nào để sửa các quy tắc công thức?

Giả sử cột nhóm là B thay vì cột A và cột giá trị lớn nhất là cột E thay vì cột B. Đây là những thay đổi trong công thức:

Quy tắc sửa đổi 1:

=isblank($E2)=true

Quy tắc sửa đổi 2:

=isna(vlookup(B2&E2,ArrayFormula(index(sortn(sort({$B$2:$B,$E$2:$E},1,0,2,0),9^9,2,1,0),0,1)&index(sortn(sort({$B$2:$B,$E$2:$E},1,0,2,0),9^9,2,1,0),0,2)),1,0))=false

Trong các quy tắc sửa đổi 1 và 2, “Áp dụng cho phạm vi” trong các thiết lập định dạng điều kiện sẽ thay đổi thành E2: E11 thay vì B2: B11.

Làm thế nào công thức Quy tắc 2 Đánh dấu Giá trị Lớn nhất trong Mỗi Nhóm?

Dưới đây là công thức chính, tức là quy tắc số 2, được giải thích từng bước.

Công thức SORT sắp xếp các mục và giá trị theo thứ tự giảm dần. Vì vậy, giá trị lớn nhất của mỗi nhóm sẽ nằm trên hàng đầu tiên của mỗi nhóm.

=sort({$A$2:$A,$B$2:$B},1,0,2,0)

Công thức SORTN xóa bỏ các bản sao từ nhóm và giữ lại hàng đầu tiên trong mỗi nhóm.

=sortn(sort({$A$2:$A,$B$2:$B},1,0,2,0),9^9,2,1,0)

Để hiểu tính năng SORTN này (loại bỏ các bản sao từng nhóm) bạn có thể đọc bài viết của tôi; SORTN Tie Modes trong Google Sheets – Bốn Tiêu chí Loại trừ.

Bây giờ, chúng ta có giá trị lớn nhất của mỗi nhóm và tên mục tối đa tương ứng.

Bằng hai công thức Index, chúng ta có thể tách các cột (tên mục và các cột giá trị lớn nhất).

Trích xuất Tên Mục (cột đầu tiên):

=index(sortn(sort({$A$2:$A,$B$2:$B},1,0,2,0),9^9,2,1,0),0,1)

Trích xuất Giá Trị Lớn nhất (cột thứ hai):

=index(sortn(sort({$A$2:$A,$B$2:$B},1,0,2,0),9^9,2,1,0),0,2)

Sau đó, với sự trợ giúp của toán tử &, kết hợp hai cột (hai công thức Index) như sau.

Ngoài ra, tôi đã bọc các công thức Index này với ArrayFormula vì toán tử & được sử dụng trong một mảng.

=ArrayFormula(index(sortn(sort({$A$2:$A,$B$2:$B},1,0,2,0),9^9,2,1,0),0,1)&index(sortn(sort({$A$2:$A,$B$2:$B},1,0,2,0),9^9,2,1,0),0,2))

Những giá trị trên là ‘phạm vi’ trong một tìm kiếm theo chiều dọc (Vlookup) là công thức cuối cùng của chúng tôi mà đánh dấu giá trị lớn nhất trong mỗi nhóm trong tệp bảng tính Google của chúng tôi.

Cú pháp:

VLOOKUP(search_key, range, index, [is_sorted])

Bây giờ hãy xem dữ liệu của chúng tôi trong A2: B11 và tìm kiếm theo chiều dọc ‘search_key’ ở dưới.

=A2&B2

Điều này trả về “Apple100” làm ‘search_key’. ‘index’ (số cột đầu ra trong Vlookup) là 1 vì chỉ có 1 cột trong ‘phạm vi’.

Vlookup sẽ không tìm thấy ‘search_key’ ở trên trong ‘phạm vi’ và vì vậy nó sẽ trả về FALSE.

Tiếp theo, Vlookup sẽ sử dụng ‘search_key’ “Apple255” (A3&B3) trong ‘phạm vi’ và sẽ trả về giá trị TRUE.

Vì giá trị là TRUE, ô tương ứng trong phạm vi B2: B11 sẽ được đánh dấu. Quá trình này tiếp tục xuống hàng.

Đó là tất cả về cách đánh dấu giá trị lớn nhất trong mỗi nhóm trên Google Sheets.

Bài viết khác về Định Dạng Điều Kiện

  1. Định dạng điều kiện Multiple OR bằng Regex trong Google Sheets
  2. Quy tắc định dạng điều kiện liên quan đến ngày trong Google Sheets
  3. Làm thế nào để định dạng điều kiện trên các tab trang tính trong Google Sheets
  4. Đánh dấu một hàng hoàn chỉnh trong định dạng điều kiện trên Google Sheets
  5. Làm thế nào để đánh dấu tất cả các hàng lỗi trên Google Sheets
  6. Làm thế nào để đánh dấu 3 giá trị lớn nhất trong mỗi hàng trên Google Sheets
  7. Tìm tất cả các ô có định dạng điều kiện trong Google Sheets
  8. Làm thế nào để đánh dấu ô chứa chức năng cụ thể trong Google Sheets
  9. Đánh dấu các nhóm khi tổng nhóm vượt quá mục tiêu trên Google Sheets
  10. Làm thế nào để đánh dấu giá trị kết quả Vlookup trên Google Sheets
  11. Đánh dấu những bản sao khớp một phần trong Google Sheets

Hy vọng rằng bài viết này sẽ giúp bạn hiểu rõ hơn về cách đánh dấu giá trị lớn nhất trong mỗi nhóm trên Google Sheets. Đừng quên áp dụng những gì bạn đã học vào các tệp bảng tính của mình để tối ưu hóa quy trình làm việc!

GiGiKing

Related posts