Hóa đơn được gửi trong câu hỏi Excel của tuần này.
Tôi có một cơ sở dữ liệu về các sự kiện trong Excel và sếp của tôi muốn tôi vẽ biểu đồ tần suất theo tháng. Tôi đã đọc mẹo của bạn để thay đổi ngày hàng ngày thành ngày hàng tháng và về các công thức Excel CSE. Tôi đã thử mọi tiêu chí mà tôi có thể nghĩ ra trong công thức Excel CountIf dưới đây để xem xét 2 tiêu chí.
Tình huống của bạn có thể được giải quyết dễ dàng bằng bảng xoay (XL95-XL2000) hoặc biểu đồ xoay (chỉ XL2000). Bây giờ, hãy giải quyết câu hỏi mà bạn đã đặt ra. Ở bên trái là bảng tính của bạn. Có vẻ như bạn sẽ muốn nhập công thức vào các ô B4406: D4415 để tính số sự kiện nhất định mỗi tháng.
Hàm CountIf là một dạng chuyên biệt của công thức mảng, rất hữu ích khi bạn có một tiêu chí duy nhất. Nó không hoạt động tốt khi bạn có nhiều tiêu chí. Các công thức mẫu sau sẽ đếm số hàng có Mưa và số sự kiện trong tháng 1 năm 97:
=COUNTIF(B2:B4403,"=Rain")
=COUNTIF(A2:A4403,"="&A4406)
Không có cách nào sử dụng CountIf để lấy giao điểm của hai điều kiện.
Đối với bất kỳ độc giả nào chưa quen với cách nhập công thức mảng, tôi thực sự khuyên bạn nên xem lại Sử dụng công thức CSE để tăng thêm tính năng cho Excel.
Bill không nêu nó trong câu hỏi của anh ấy, nhưng tôi muốn xây dựng một công thức mà anh ấy có thể nhập chỉ một lần vào ô B4406 mà có thể dễ dàng sao chép sang các ô khác trong phạm vi của anh ấy. Bằng cách sử dụng các tham chiếu tuyệt đối và hỗn hợp trong công thức, bạn có thể tránh được những rắc rối khi nhập công thức mới cho mỗi giao điểm.
Dưới đây là đánh giá nhanh về công thức tuyệt đối, tương đối và hỗn hợp. Thông thường nếu bạn nhập một công thức như =SUM(A2:A4403)trong D1 và sau đó sao chép công thức vào E2, công thức của bạn trong E2 sẽ thay đổi thành =SUM(B3:C4403). Đây là một tính năng thú vị của trang tính được gọi là "định địa chỉ tương đối", nhưng đôi khi chúng tôi không muốn điều đó xảy ra. Trong trường hợp này, chúng tôi muốn mọi công thức tham chiếu đến phạm vi A2: B4403. Khi chúng tôi sao chép công thức từ ô này sang ô khác, nó phải luôn trỏ đến A2: B4403. Trong khi nhập công thức, hãy nhấn F4 một lần sau khi nhập phạm vi và công thức của bạn sẽ thay đổi thành=SUM($A$2:$A$4403). Ký hiệu đô la cho biết rằng phần tham chiếu đó sẽ không thay đổi khi bạn sao chép công thức. Đây được gọi là địa chỉ tuyệt đối. Có thể chỉ khóa cột với $ và cho phép hàng tương đối. Đây được gọi là tham chiếu hỗn hợp và sẽ được nhập dưới dạng =$A4406. Để khóa hàng nhưng cho phép cột tương đối, hãy sử dụng =B$4405. Khi bạn nhập công thức, hãy sử dụng F4 để chuyển đổi giữa bốn loại tham chiếu tương đối, tuyệt đối và hỗn hợp.
Đây là công thức cho ô B4406:
=SUM(IF($C$2:$C$4403=$A4406,IF($B$2:$B$4403=B$4405,1,0),0))
Nhập công thức. Khi bạn hoàn thành công thức, hãy giữ Ctrl, Shift và sau đó nhập. Bây giờ bạn có thể sao chép công thức vào C4406: D4406 và sau đó sao chép ba ô đó xuống từng hàng trong bảng kết quả của bạn.
Công thức sử dụng cả ba dạng tham chiếu hỗn hợp và tuyệt đối. Nó có 2 câu lệnh if vì hàm AND () dường như không hoạt động trong công thức mảng. Để được giải thích rõ hơn về những gì đang xảy ra với chức năng mảng, hãy đọc lại Sử dụng công thức CSE để nạp thêm vào Excel được đề cập ở trên.
