Công thức chung
(=MAX(FREQUENCY(IF(rng>0,COLUMN(rng)),IF(rng=0,COLUMN(rng)))))
Tóm lược
Để đếm các đơn đặt hàng hàng tháng liên tiếp, bạn có thể sử dụng công thức mảng dựa trên hàm FREQUENCY với sự trợ giúp từ COLUMN và MAX.
Trong ví dụ được hiển thị, công thức trong I5 là:
(=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5)),IF(C5:H5=0,COLUMN(C5:H5)))))
Lưu ý: đây là công thức mảng và phải được nhập bằng Control + Shift + Enter.
Giải trình
Đây là một công thức khó hiểu, vì vậy hãy thắt dây an toàn!
Chìa khóa của công thức là biết rằng FREQUENCY tập hợp các số vào "thùng" theo một cách cụ thể. Mỗi thùng đại diện cho một giới hạn trên và tạo ra tổng số tất cả các số trong tập dữ liệu nhỏ hơn hoặc bằng giới hạn trên và lớn hơn số thùng trước đó. Sau đó, mẹo là tạo data_array với điều kiện bạn muốn kiểm tra (số thứ tự lớn hơn 0 trong trường hợp này) và bins_array sử dụng điều kiện ngược lại.
Để tạo bin data_array, chúng tôi sử dụng như sau:
IF(C5:H5>0,COLUMN(C5:H5))
Chúng tôi kiểm tra số lượng đơn đặt hàng trong mỗi tháng và nếu dương, trả về số cột trong đó số đơn đặt hàng> 0. Mảng kết quả trông giống như sau:
(3, FALSE, FALSE, 6,7,8)
Lưu ý rằng chỉ những cột có số thứ tự> 0 mới được đưa vào mảng này.
Mảng thùng được tạo bằng cách này:
IF(C5:H5=0,COLUMN(C5:H5))
Điều này đặt số cột cho số đơn hàng = 0 vào một mảng kết thúc như thế này:
(FALSE, 4,5, FALSE, FALSE, FALSE)
Chỉ các cột có số lượng đơn hàng = 0 mới được đưa vào mảng này trong đó, theo hành vi FREQUENCY tiêu chuẩn, chúng trở thành các thùng chức năng kiểm đếm các đơn hàng khác 0. Các trận thắng được dịch thành FALSE và không thu thập bất kỳ số nào từ mảng dữ liệu, vì các giá trị FALSE bị bỏ qua.
Với mảng dữ liệu và mảng bin ở trên, tần số trả về một mảng số đếm trên mỗi bin trong một mảng như sau:
(1; 0; 3)
Bây giờ chúng ta chỉ cần quấn hàm MAX xung quanh mảng được trả về bởi FREQUENCY. MAX sau đó trả về số cao nhất trong mảng là kết quả cuối cùng.
Các giá trị liên tiếp khác
Để đếm các lần xuất hiện liên tiếp của các giá trị khác, chỉ cần điều chỉnh logic nếu cần theo cùng một mẫu: điều kiện đầu tiên kiểm tra thứ bạn muốn đếm, điều kiện thứ hai kiểm tra điều ngược lại.
