Bạn đang sử dụng Google Sheets để ghi lại chi tiết bán hàng hoặc mua hàng của mình đúng không? Sau đó, sẽ có nhiều hàng dọc có cùng các mục gọi là nhóm.
Trong dữ liệu như vậy, với định dạng điều kiện, bạn có thể kiểm tra xem tổng doanh số hoặc mua hàng có đáp ứng mục tiêu hay không.
Bạn có thể tính tổng nhóm trong Google Sheets bằng cách sử dụng hàm Sumif. Nếu bạn muốn bao gồm một số điều kiện trước khi tính tổng nhóm, bạn có thể sử dụng Sumifs hoặc Query.
Nhưng nếu bạn muốn nhấn mạnh các nhóm dựa trên tổng nhóm? Bài viết này sẽ giới thiệu hai công thức định dạng có điều kiện tùy chỉnh mà bạn có thể sử dụng để làm điều đó trong Google Sheets. Không nghi ngờ, hàm Sumif sẽ là chìa khóa trong những quy tắc đó.
Tôi đã bao gồm hai loại công thức với định dạng dữ liệu của bạn trong tâm trí. Dữ liệu của bạn có thể không được sắp xếp theo mẫu dữ liệu mẫu của tôi bên dưới. Vì vậy, tôi hy vọng sẽ đưa ra hai tùy chọn giúp bạn linh hoạt hơn.
Công thức Sheets để nhấn mạnh các nhóm khi tổng nhóm vượt quá mục tiêu
Tôi có hai ví dụ thực tế (để bao gồm hai tùy chọn đã nêu ở trên).
-
Nhấn mạnh các hàng khi tỷ lệ phân bổ hàng tháng vượt quá mục tiêu.
-
Định dạng có điều kiện khi tổng doanh số đáp ứng mục tiêu.
Nhấn mạnh các hàng khi tỷ lệ phân bổ hàng tháng vượt quá mục tiêu trong Google Sheets
Tôi có một mẫu dữ liệu chiến dịch mẫu trong Google Sheets bắt đầu với cột Chiến dịch, sau đó là Ngân sách Tổng và Phân bổ hàng tháng (vui lòng xem hình ảnh bên dưới).
Thêm một cột thứ tư vào phạm vi dữ liệu này mang tên “Tỷ lệ phân bổ”. Trong đó, trong ô D2 nhập vào công thức sau.
= Mảng Công thức (nếu (len (B2: B), C2: C / B2: B,))
Sau đó, định dạng ô D2: D thành Phần trăm từ menu Định dạng> Số> Phần trăm.
Ở đây, công thức định dạng có điều kiện của tôi sẽ nhấn mạnh các nhóm A và C vì tổng tỷ lệ phân bổ hàng tháng của những nhóm này vượt quá 100%.
Dưới đây là công thức tùy chỉnh dựa trên Sumif. Bạn sẽ áp dụng quy tắc này như thế nào? Tôi sẽ giải thích sau.
= sumif ($ A $ 2: $ A, $ A2, $ D $ 2: $ D)> 100%
Để hiểu quy tắc nhấn mạnh, hãy cộng tổng “Tỷ lệ phân bổ hàng tháng” của nhóm A như sau.
= sum (D2: D)
Kết quả sẽ là 101%. Điều đó có nghĩa là tổng nhóm vượt quá mục tiêu.
Trong công thức Sumif trên, phạm vi là “Chiến dịch” (nhóm) và sum_range (cột tổng) là “Tỷ lệ phân bổ hàng tháng”. Tiêu chí trong công thức là tên “Chiến dịch”.
Quy tắc kiểm tra xem đầu ra Sumif (Tổng Sumif theo nhóm) theo “Tỷ lệ phân bổ hàng tháng” lớn hơn 100%.
Tham khảo cú pháp Sumif dưới đây sẽ giúp bạn hiểu rõ các đối số và phạm vi, tiêu chí và sum_range tương ứng được sử dụng trong công thức Sumif của tôi.
SUMIF (phạm vi, tiêu chí, [sum_range])
Làm thế nào để áp dụng công thức Sumif trên vào định dạng có điều kiện để nhấn mạnh các hàng dựa trên tổng nhóm trong Google Sheets?
Bước 1: Nhấp vào ô A2.
Bước 2: Đi đến Định dạng> Định dạng có điều kiện.
Bước 3: Áp dụng các thiết lập bên dưới.
Công thức này nhấn mạnh toàn bộ hàng trong phạm vi. Ví dụ: A2: D5 trong nhóm đầu tiên thay vì A2: A5.
Để giới hạn việc nhấn mạnh đó đến một cột duy nhất, hãy loại bỏ dấu đô trong tham chiếu tiêu chí trong công thức. Sau đó, công thức sẽ như sau.
= sumif ($ A $ 2: $ A, A2, $ D $ 2: $ D)> 100%
Bạn Có Thể Thích: Sumif trong Định dạng có điều kiện trong Google Sheets.
Định dạng có điều kiện hàng khi tổng doanh số đáp ứng mục tiêu
Trước hết, hãy xem dữ liệu mẫu. Như bạn có thể thấy, định dạng dữ liệu hoàn toàn khác so với ví dụ trước.
Hãy tạm thời bỏ qua phần định dạng dữ liệu và tập trung vào việc nhấn mạnh các ô.
Theo dữ liệu được cung cấp, chỉ có sản phẩm 2 đáp ứng mục tiêu. Hãy cộng tổng khoảng B5: B8 để kiểm tra tổng.
= sum (B5: B8)
Bạn sẽ nhận được $ 615.00 là kết quả của công thức trên. Tổng này lớn hơn mục tiêu doanh số của Chế độ 2, là $ 500.00 (ô E3). Vì vậy trong trường hợp này, tổng số doanh số đáp ứng mục tiêu.
Hãy kiểm tra hai sản phẩm khác và bạn sẽ thấy rằng cả hai sản phẩm này đều không đáp ứng mục tiêu đã đặt.
Tạo quy tắc tùy chỉnh để nhấn mạnh các nhóm khi tổng doanh số vượt quá mục tiêu trong Google Sheets
Tương tự như ví dụ đầu tiên, ở đây chúng tôi cũng muốn nhấn mạnh các hàng khi tổng nhóm vượt quá mục tiêu. Nhưng có một điều khác ở đây. Đó là định dạng dữ liệu.
Bạn có thể sử dụng cùng một công thức Sumif (khi điều chỉnh phạm vi, tiêu chí và sum_range) nếu bạn có thể sắp xếp lại dữ liệu như sau.
Công thức:
= sumif ($ A $ 2: $ A, A2, $ C $ 2: $ C)> B2
Ở đây thay vì sử dụng> 100% là mục tiêu trong công thức đầu tiên, hãy sử dụng> B2 là mục tiêu ở đây.
Nhưng một số người trong số bạn có thể muốn giữ định dạng dữ liệu, đúng không? Tôi có nghĩa là doanh số thực tế trong A1: B và mục tiêu trong D1: E.
Trong trường hợp đó, chúng ta có thể sử dụng Vlookup với Sumif như là công thức kết hợp.
= vlookup (A2, $ D $ 2: $ E $ 4,2,0) <sumif ($ A $ 2: $ B $ 10, A2, $ B $ 2: $ B $ 10)
Giải thích công thức:
Ở đây để định dạng có điều kiện các sản phẩm dựa trên sản lượng bán hàng và mục tiêu của chúng, chúng ta có thể sử dụng Sumif như ở các ví dụ trước. Nhưng bên cạnh đó, chúng ta có thể dựa vào Vlookup.
Trong khi công thức Sumif tổng hợp số tiền bán hàng (cột B), Vlookup trả về giá trị mục tiêu tương ứng từ cột E.
Công thức kiểm tra các hàng như sau.
Đối với sản phẩm đầu tiên;
Mục tiêu Sản phẩm 1 (E1) <để tổng của Sản phẩm 2 Sales (B2: B4)
Điều này tương đương với = 1000 <925 trả về LỖI.
Ở đây, mục tiêu Sản phẩm 1 (E2) được trả về bởi Vlookup và tổng Sản phẩm 1 Sales (B2: B4) được trả về bởi Sumif.
Điều đó có nghĩa là công thức kiểm tra xem giá trị mục tiêu nhỏ hơn tổng số doanh số. Nếu công thức trả về TRUE, nhóm tương ứng đã được nhấn mạnh.
