Một trong những câu hỏi thường gặp tại Bảng tin là cách sử dụng hàm SumIf với hai điều kiện khác nhau. Thật không may, câu trả lời là SumIf không thể xử lý hai điều kiện khác nhau.
Để thực hiện hai điều kiện, bạn phải sử dụng một công thức mảng khá phức tạp. Phần bổ trợ thuật sĩ tính tổng có điều kiện cho phép bạn nhập các công thức phức tạp này một cách dễ dàng.
Đây là một bảng tính Excel với các cột cho sản phẩm, đại diện bán hàng và doanh số bán hàng. Dữ liệu nằm trong các ô A2: C29.
Nếu bạn muốn tính tổng doanh số, một hàm SUM () đơn giản sẽ hoạt động. =SUM(C2:C29).
Nhiều Exceler khám phá ra hàm SumIf. Sử dụng chức năng này, khá dễ dàng để tìm ra tổng doanh thu cho sản phẩm ABC.=SUMIF(A2:A29,E2,C2:C29)
Cũng dễ dàng tìm ra tổng doanh thu mà đại diện bán hàng Joe thực hiện với =SUMIF(B2:B29,E2,C2:C29).
Sau đó, bạn sẽ giả định rằng có thể tính được tổng doanh thu của sản phẩm ABC do Joe sản xuất. Tuy nhiên, không có cách nào để làm điều này với hàm SumIf. Nó chỉ ra rằng bạn cần sử dụng một mảng hoặc công thức CSE khá phức tạp.
Hãy đối mặt với nó - công thức Sum là Excel 101. Công thức SumIf không kém xa về độ phức tạp. Tuy nhiên, công thức CSE để tính tổng doanh số ABC do Joe thực hiện đủ khiến tôi quay cuồng.
Tin tốt lành - Microsoft cung cấp Trình hướng dẫn tính tổng có điều kiện cho phép ngay cả người mới làm quen cũng có thể nhập các công thức có điều kiện phức tạp dựa trên 1, 2 hoặc nhiều điều kiện. Thuật sĩ tính tổng có điều kiện là một phần bổ trợ. Để thêm chức năng này vào Excel, hãy chuyển đến menu Công cụ và chọn Phần bổ trợ. Trong hộp thoại Phần bổ trợ, chọn hộp kiểm bên cạnh Trình hướng dẫn Tổng có Điều kiện và chọn OK. Có thể bạn cần đĩa CD cài đặt của mình tại thời điểm này, bởi vì Microsoft không bao gồm trình hướng dẫn trong cài đặt mặc định.
Khi phần bổ trợ được bật thành công, sẽ có một lựa chọn Tổng điều kiện… ở gần cuối trình đơn Công cụ.
Chọn một ô trong tập dữ liệu của bạn và chọn Công cụ - Tổng có điều kiện. Giả sử rằng dữ liệu của bạn được định dạng độc đáo với một hàng tiêu đề, Excel sẽ đoán đúng phạm vi dữ liệu của bạn. Chọn Tiếp theo.
Ở bước 2, chọn cột để tính tổng. Trong trường hợp này, trình hướng dẫn đã đoán bạn muốn tính tổng cột số đầu tiên (và duy nhất) - Doanh số. Ở giữa hộp thoại là ba điều khiển thả xuống. Những điều này xảy ra đúng với điều kiện đầu tiên - Sản phẩm bằng ABC, vì vậy hãy chọn nút Thêm điều kiện.
Sau đó, bạn có thể thêm điều kiện thứ hai của mình. Trong trường hợp này, bạn muốn chỉ định rằng Đại diện bán hàng là Joe. Chọn mũi tên cho menu thả xuống đầu tiên. Excel cung cấp danh sách tên cột có sẵn theo thứ tự bảng chữ cái. Chọn Đại diện bán hàng.
Trình đơn thả xuống ở giữa là đúng, nhưng để đầy đủ ở đây, bạn có thể thấy rằng bạn có thể đã chọn bằng, nhỏ hơn, lớn hơn, nhỏ hơn hoặc bằng, lớn hơn hoặc bằng hoặc không bằng.
Từ menu thả xuống thứ ba, hãy chọn Joe.
Chọn nút Thêm điều kiện.
Bây giờ bạn đã sẵn sàng để chuyển sang Bước 3. Nhấn nút Tiếp theo.
Trong bước 3, bạn có hai lựa chọn. Trong lựa chọn đầu tiên, Wizard sẽ nhập một công thức duy nhất với các giá trị "ABC" và "Joe" được mã hóa cứng vào công thức. Nó sẽ cho bạn câu trả lời, nhưng sẽ không có cơ hội để dễ dàng thay đổi công thức. Với lựa chọn thứ hai, Excel sẽ thiết lập một ô mới với giá trị "ABC" và một ô mới có giá trị "Joe". Ô thứ ba sẽ chứa công thức tính tổng có điều kiện dựa trên hai giá trị đó. Với tùy chọn này, bạn có thể nhập các giá trị mới vào các ô để xem tổng số XYZ mà Adam đã bán.
Sau đó, trình hướng dẫn sẽ hỏi bạn muốn giá trị ở đâu cho ABC. Chọn một ô và chọn Tiếp theo. Lặp lại khi Trình hướng dẫn yêu cầu bạn chọn một ô cho Joe và công thức.
Khi bạn chọn Hoàn tất ở bước cuối cùng, Excel sẽ tạo một phiên bản công thức CSE hơi khác (nhưng hợp lệ).
Công thức này tính rằng Joe đã bán được 33.338 đô la ABC.
Nếu bạn thay đổi ô nhập sản phẩm từ ABC thành DEF, công thức sẽ tính lại để cho thấy rằng Joe đã bán được 24.478 đô la DEF.
Trình hướng dẫn tính tổng có điều kiện đặt các công thức phức tạp trong tầm tay của tất cả chủ sở hữu Excel.
Thông tin thêm:Nếu bạn muốn xây dựng một bảng hiển thị doanh số của từng sản phẩm theo từng đại diện bán hàng, có một số "chăm sóc và nuôi dưỡng" đặc biệt mà bạn sẽ cần biết về các công thức này. Nhập từng đại diện bán hàng trên đầu phạm vi. Nhập từng sản phẩm xuống cột bên trái của phạm vi. Chỉnh sửa công thức do trình hướng dẫn cung cấp. Trong hình ảnh bên dưới, công thức đang trỏ một sản phẩm trong ô E6. Tham chiếu này thực sự cần phải là $ E6. Nếu bạn để tham chiếu là E6 và sao chép công thức vào cột G, công thức sẽ nhìn vào F6 thay vì E6 và điều này sẽ sai. Thêm ký hiệu đô la trước chữ E trong E6 sẽ đảm bảo rằng công thức luôn xem sản phẩm trong cột E. Công thức cũng chỉ đến một đại diện bán hàng trong ô F5. Tham chiếu này thực sự cần phải là F $ 5. Nếu bạn để tham chiếu là F5 và sao chép xuống hàng 7,Tham chiếu F5 sẽ thay đổi thành F6 và điều này không đúng. Thêm một ký hiệu đô la trước số hàng sẽ khóa số hàng và tham chiếu sẽ luôn trỏ đến hàng 5.
Trong chế độ Chỉnh sửa (chọn ô và nhấn F2 để chỉnh sửa), nhập $ trước ký tự E. Nhập ký hiệu đô la trước số 5 trong F5. Đừng nhấn Enter!
Công thức này là một loại công thức đặc biệt. Nếu bạn nhấn Enter, bạn sẽ nhận được số 0, điều này không chính xác.
Thay vì nhập Enter, hãy giữ phím Ctrl và Shift trong khi nhấn Enter. Sự kết hợp kỳ diệu của C trl + S hift + E nter là lý do tại sao tôi gọi là các công thức CSE.
Có một cân nhắc cuối cùng trước khi sao chép công thức vào phần còn lại của bảng. Độ nghiêng của bạn có thể là sao chép F6 và dán vào F6: G8. Nếu bạn thử điều này, Excel sẽ đưa ra thông báo khó hiểu "Bạn không thể thay đổi một phần của mảng". Excel phàn nàn rằng bạn không thể dán công thức CSE vào một dải ô có chứa công thức CSE ban đầu.
Thật dễ dàng để giải quyết vấn đề này. Sao chép F6. Dán vào F7: F8.
Sao chép F6: F8. Dán vào G6: G8. Bạn sẽ có một bảng công thức CSE hiển thị tổng dựa trên hai điều kiện.
