SUM(Index+Match:Index+Match) :lấy tổng số lượng còn lại trừ dần theo số lượng đơn hàng, theo nhiều điền kiện hàng và cột

 

SUM(Index+Match:Index+Match):Sum từ ô X đến ô Y theo điều kiện A và B ở cột Z. 

Trong đó:X-Y-A-B có giá trị thay đổi, Sumif và Sumifs không đáp ứng được.

Bài toán đặt ra như sau:

Mỗi ngày bạn đều nhận được 1 bảng gồm sản lượng của hàng chục/hàng trăm mã với vô số size. Trước khi lập được kế hoạch, việc đầu tiên bạn cần làm là số lượng sản phẩm bạn có, có thể đáp ứng bao nhiêu đơn hàng/size nào(đơn hàng FIFO), còn thiếu bao nhiêu/size nào, như bảng dưới (ví dụ sản phẩm là giày-bởi vì cách làm này mình lấy ở công ty giày ) để báo cáo cho các bộ phận khác tiến độ đơn hàng.

Như bảng trên ta có thể thấy item 1 size 4 có sản lượng 80 cái, có thể đáp ứng đơn 1+đơn 2 và thiếu 24 cái của đơn 3. Nếu lọc ra và làm riêng từng item sẽ mất nửa buổi chỉ để xác định sản xuất đáp ứng được bao nhiêu đơn hàng. Tùy số lượng Item sẽ sản xuất trong ngày, chưa kể phải xét đến các yêu tố khác như việc đồng bộ hóa khuôn/nguyên liệu để giảm downtime khi ngưng chuyền, Tuy nhiên ở bài này ta chỉ nói đến ứng dụng của SUM kết hợp Index/Match.

Cách làm như sau:

Ta thiết lập 3 bảng với thứ tự cột size giống nhau cho :Tổng số lưọng ta có/Số lượng cần/Và số lượng còn thiếu

• Bảng 1:Tổng số lượng ta có trong ngày:số lượng có chỉ phân theo Item, không theo đơn hàng, số lượng này có thể dựa vào các báo cáo sản lượng+tồn kho+kế hoạch dự kiến có.

Như ví dụ bảng dưới,ta có thể thấy tổng sản lương sản xuất cho một ngày lên tới 70 loại sản phẩm+hàng chục loại khuôn size với những số lượng có phần nhỏ lẻ, nguyên nhân có thể do hàng hư cần làm bù, do đơn hàng số lượng ít nhưng ETD đã cận kề mà không thể gộp đơn hàng, hoặc sản phẩm chưa có đơn hàng nhưng dự đoán sẽ có và sản xuất để lấy sản lượng.

• Bảng 2:Bảng chi tiết đơn hàng cần:Bảng này chia ra theo Item/size/đơn hàng đã được sắp xếp theo thứ tự ETD , Trong bảng này có 131 sản phẩm , 288 đơn hàng.

• Bảng 3:Bảng Chi tiết số lượng còn thiếu cho đơn hàng

• Thêm cột định danh :Ở cuối bảng 2 và 3 ta thêm 1 cột với công thức như dưới đây, do 1 item có nhiều đơn hàng nên phải định danh cho chúng, ở ví dụ dưới mình thêm ở cột AV

Bảng 3:Bảng Chi tiết số lượng còn thiếu cho đơn hàng ta điền công thức như sau:

=MIN(0,MAX(INDEX(bang1!V$7:V$800,MATCH($I7,bang1!$I$7:$I$800,0))-(SUM(INDEX(bang2!V$7:V$800,MATCH("1_"&$I7,bang2!$AV$7:$AV$800,0)):INDEX(bang2!V$7:V$800,MATCH($AV7,bang2!$AV$7:$AV$800,0)))),-INDEX(bang2!V$7:V$800,MATCH($AV7,bang2!$AV$7:$AV$800,0))))

**Lưu ý cố định

Trong đó:

• INDEX(bang1!V$7:V$800,MATCH($I7,bang1!$I$7:$I$800,0)):Tổng số lượng ta có lấy từ bảng1 
• SUM(INDEX(bang2!V$7:V$800,MATCH("1_"&$I7,bang2!$AV$7:$AV$800,0)):INDEX(bang2!V$7:V$800,MATCH($AV7,bang2!$AV$7:$AV$800,0)): Tổng số lượng từ đơn đầu tiên của item tới đơn thứ N của item, N ở đây là số đơn theo dòng. Ví dụ Item71 có 3 đơn thì đơn đầu tiên sẽ là tổng số lượng đơn 1, nếu đơn thứ hai sẽ là số lượng đơn 1+số lượng đơn 2.

** Sum (index/match:Index/match) ở đây có nghĩa là sum từ ô ..đến ô. Index match trong công thức này sẽ trả về Địa chỉ của ô chứ không trả về Gía trị của ô đó.

=>MIN(0,MAX(INDEX(bang1!V$7:V$800,MATCH($I7,bang1!$I$7:$I$800,0))-(SUM(INDEX(bang2!V$7:V$800,MATCH("1_"&$I7,bang2!$AV$7:$AV$800,0)):INDEX(bang2!V$7:V$800,MATCH($AV7,bang2!$AV$7:$AV$800,0)))),-INDEX(bang2!V$7:V$800,MATCH($AV7,bang2!$AV$7:$AV$800,0)))) 

=>Lấy tổng số lượng trừ dần theo số lượng đơn hàng, nếu số lượng có lớn hơn hoặc số đơn cần thì trả kết quả bằng 0 (để biết là đã xong), nếu ít hơn thì để số còn thiếu, số còn thiếu này không được nhỏ hơn Âm của Sản lượng cần.

Sau khi tìm ra mỗi đơn hàng/item/size còn thiếu số lượng bao nhiêu, ta có thể copy sang 1 bảng khác và dùng paste special để chuyển số âm thành dương và báo cáo cho các phòng ban khác về tiến độ đơn hàng.