Chồng chéo ngày trong Excel

Tìm phạm vi ngày chồng chéo trong tập dữ liệu dường như là một nhiệm vụ dễ dàng. Mặc dù đôi khi nó có thể khiến bạn hơi đau đầu. Các khoảng thời gian chồng chéo có thể dẫn đến kết quả không chính xác, tất nhiên, điều này có thể làm hỏng các báo cáo hoàn hảo của bạn. May mắn thay, có nhiều cách để giảm thiểu rủi ro của tình huống khá bất tiện và rủi ro này

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

  1. trường loại giá (Cơ sở hoặc Quảng cáo),
  2. trường Mã khách hàng (tên hoặc id của khách hàng),
  3. trường id sản phẩm (mã định danh duy nhất của sản phẩm),
  4. trường 'hợp lệ từ' và 'hợp lệ đến' (phạm vi hợp lệ của giá)
  5. 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

  1. hàng thực tế của ValidFrom nhỏ hơn hoặc bằng ValidTo
  2. ValidFrom nhỏ hơn hoặc bằng ValidTo của hàng thực tế
  3. 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

Chủ đề