Một cách để kiểm tra tập dữ liệu của bạn là công thức TỔNG KẾT kỳ diệu. Tôi phải nhấn mạnh rằng trước bài đăng không phải là về công thức TỔNG HỢP, mà là cách sử dụng nó để giải quyết một vấn đề cụ thể. Có rất nhiều bài viết hay về công thức này trên mạng. Để bắt đầu, tôi khuyên bạn nên đọc bài viết hỗ trợ MS
Mô tả tập dữ liệu
Tập dữ liệu tôi sẽ sử dụng là một bảng giá. Trong tập dữ liệu, chúng tôi có một
- trường loại giá (Cơ sở hoặc Quảng cáo),
- trường Mã khách hàng (tên hoặc id của khách hàng),
- trường id sản phẩm (mã định danh duy nhất của sản phẩm),
- trường 'hợp lệ từ' và 'hợp lệ đến' (phạm vi hợp lệ của giá)
- và cuối cùng là trường giá trị giá (giá thực tế của sản phẩm)
Mục tiêu để đạt được
Giả định của chúng tôi là chỉ có thể có một mức giá cho mỗi loại giá cho mỗi khách hàng, mỗi sản phẩm mỗi ngày. Tất nhiên, nếu nguồn dữ liệu của bạn là một hệ thống ERP và đã có các kiểm tra an toàn được tích hợp sẵn để ngăn chặn bất kỳ loại trùng lặp và chồng chéo nào thì bạn thật may mắn
Chúng tôi muốn xác định những hàng có phạm vi hiệu lực chồng chéo cùng một loại giá cho cùng một sản phẩm cho cùng một khách hàng. Trong bối cảnh này, chúng tôi không quan tâm đến giá cả. Ví dụ
Tốt
Xấu
Trong ví dụ 'xấu' ở trên, tùy thuộc vào cách bạn tra cứu giá cho 2020-06-15, bạn có thể nhận được 3 hoặc 4 dưới dạng Đơn giá, điều này không tốt
Giải pháp với TÓM TẮT
Bản thân công thức
= IF ( TÓM TẮT (([@ValidFrom
Trang hỗ trợ MS định nghĩa hàm SUMPRODUCT là “Hàm SUMPRODUCT trả về tổng các tích của các phạm vi hoặc mảng tương ứng. ”. Hãy xem từng bước nó hoạt động như thế nào trong trường hợp cụ thể này
Chúng tôi đang sử dụng 4 tiêu chí trong công thức của mình, một tiêu chí cho mỗi trường sau. ValidTo, ValidFrom, ProductID và Mã khách hàng. Các tiêu chí nằm trong ngoặc đơn và được phân tách bằng dấu hoa thị '*'
Mỗi tiêu chí được đánh giá riêng và trả về TRUE (1) hoặc FALSE (0). Nếu bạn chọn một trong các tiêu chí trong thanh công thức và nhấn F9, bạn có thể xem kết quả
Nếu bạn muốn xem 1 và 0, bạn có thể thêm — tiền tố trước tiêu chí của mình
Trong ngôn ngữ 'con người', chúng tôi yêu cầu công thức tính tổng các hàng đó trong tập dữ liệu ở đâu
- hàng thực tế của ValidFrom nhỏ hơn hoặc bằng ValidTo
- ValidFrom nhỏ hơn hoặc bằng ValidTo của hàng thực tế
- ProductID và Mã khách hàng và Loại giá giống hệt với các trường của hàng thực tế
Chi tiết hơn
TÓM TẮT của chúng tôi trong ví dụ trên trả về 3 do đó 3 trong số 4 dòng trùng nhau. Tôi đã lồng TÓM TẮT vào một hàm IF và vì 3 lớn hơn 1 nên kết quả cuối cùng là OVERLAP
Hy vọng rằng bạn có thể thấy công thức này hữu ích và tất nhiên hãy thoải mái tùy chỉnh nó theo nhu cầu của riêng bạn