SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện

[tieuthubuongbinh]

Đây, cho bạn "ngâm cứu" về "người đẹp" ấy.

Thân

Em đã hiểu và ứng dụng được rồi, cám ơn anh lắm

 

[hue xoan]

giúp lập công thức

giúp mình lập công thức thống kê tổng số người theo từng loại thâm niên công tác,theo từng bộ phận nữa. Bảng dữ liệu ở sheet1, bảng tổng hợp ở sheet 2 . mình đã ghi chú trong file đính kèm
hepl meeeeeeeeeeeeeee
Xin cảm ơn

Em dùng countifs, anh xem hữu ích và đơn giản hơn ko:

 

[echo07]

Chào các bạn.
Tôi có vấn đề mới cần hỗ trợ chỗ tính thời gian lũy kế cho các hạng mục khác nhau, đã sử dụng công thức mảng nhưng kết quả không như ý muốn, rất mong anh chị em giúp đỡ. Chi tiêt vấn đề được nêu trong file đính kèm. Cảm ơn và chúc cả nhà cuối tuần vui vẻ.

 

[Phan Thế Hiệp]

Chào các bạn.
Tôi có vấn đề mới cần hỗ trợ chỗ tính thời gian lũy kế cho các hạng mục khác nhau, đã sử dụng công thức mảng nhưng kết quả không như ý muốn, rất mong anh chị em giúp đỡ. Chi tiêt vấn đề được nêu trong file đính kèm. Cảm ơn và chúc cả nhà cuối tuần vui vẻ.

Vấn đề quá hay! Truy lục ngày trùng theo từng mảng, nhưng với số giờ khác nhau, rồi tổng cộng cho 6 ngày, hoặc tính cho cả quá trình hoạt động.
Công thức tính số giờ của ngày trong tuần của bạn chưa đúng, nên kết quả sai.

Nhưng sáng nay tôi bận rồi, bạn chịu khó chờ nha, sẽ có nhiều anh em hỗ trợ bạn nhiều cách thú vị.

Chúc bạn ngày vui

 

[echo07]

Vấn đề quá hay! Truy lục ngày trùng theo từng mảng, nhưng với số giờ khác nhau, rồi tổng cộng cho 6 ngày, hoặc tính cho cả quá trình hoạt động.
Công thức tính số giờ của ngày trong tuần của bạn chưa đúng, nên kết quả sai.

Nhưng sáng nay tôi bận rồi, bạn chịu khó chờ nha, sẽ có nhiều anh em hỗ trợ bạn nhiều cách thú vị.

Chúc bạn ngày vui

Vâng, cảm ơn bác. Vấn đề là nó sai ở chỗ nào đó nên kết quả mới không đúng ạ.

 

[leonguyenz]

Vấn đề quá hay! Truy lục ngày trùng theo từng mảng, nhưng với số giờ khác nhau, rồi tổng cộng cho 6 ngày, hoặc tính cho cả quá trình hoạt động.
Công thức tính số giờ của ngày trong tuần của bạn chưa đúng, nên kết quả sai.

Nhưng sáng nay tôi bận rồi, bạn chịu khó chờ nha, sẽ có nhiều anh em hỗ trợ bạn nhiều cách thú vị.

Chúc bạn ngày vui

Gãi đúng chỗ ngứa của thầy rồi
Mà em thấy không ổn ở chỗ thời gian cột B, C so không cố định, tính toán kiểu quy hồi, gần với bài tính thời gian định cư liên tục 5 năm của anh huuthangbd

 

[echo07]

Gãi đúng chỗ ngứa của thầy rồi
Mà em thấy không ổn ở chỗ thời gian cột B, C so không cố định, tính toán kiểu quy hồi, gần với bài tính thời gian định cư liên tục 5 năm của anh huuthangbd

Không cố định có thể hiểu là mình có một hợp đồng gồm nhiều gói, mỗi gói có thời gian thực hiện khác nhau và số giờ công cho phép khác nhau nhưng tổng thời gian thực hiện và tổng số giờ được phép sử dụng thì cố định.

 

[tam888]

Gãi đúng chỗ ngứa của thầy rồi
Mà em thấy không ổn ở chỗ thời gian cột B, C so không cố định, tính toán kiểu quy hồi, gần với bài tính thời gian định cư liên tục 5 năm của anh huuthangbd

Bài này excel công thức thường giải quyết tốt và dễ hiểu, nhưng phải bảng phụ
Khó ở đây là do ta muốn công thức khủng, mà khủng là tự làm khó rồi
Công thức khủng đó chỉ trông ngắn gọn, còn tốc độ tính toán thì chắc sẽ mệt.
Vẫn muốn ngắn gọn thì nên dùng VBA

 

[leonguyenz]

Bài này excel công thức thường giải quyết tốt và dễ hiểu, nhưng phải bảng phụ
Khó ở đây là do ta muốn công thức khủng, mà khủng là tự làm khó rồi

Bài này em nghĩ là dạng khó.
Dữ liệu có trùng ngày, tuần (cột B, C), không biết chọn dòng nào phù hợp (đưa ra 1 tập hợp số, cột E), phân bổ tập hợp số đó thỏa điều kiện thời gian tăng dần (theo tuần) và tổng bằng số cho trước (được tính ra ở F11).

 

[tam888]

Bài này em nghĩ là dạng khó.
Dữ liệu có trùng ngày, tuần (cột B, C), không biết chọn dòng nào phù hợp (đưa ra 1 tập hợp số, cột E), phân bổ tập hợp số đó thỏa điều kiện thời gian tăng dần (theo tuần) và tổng bằng số cho trước (được tính ra ở F11).

Cứ tính từng Hoạt động theo tuần (cộng dồn), thì được bảng các thời gian tuần cho các hoạt động, tổng cột, rồi tổng tất cả là được kết quả mong muốn.

Tính từng hoạt động theo tuần (liệt kê) thì dễ dàng rồi xét khoảng thời gian hiệu theo số ngày trong tuần (min, max ngày) thì bạn tính tốt dễ dàng.

==> cần 1 bảng phụ

 

[echo07]

Cứ tính từng Hoạt động theo tuần (cộng dồn), thì được bảng các thời gian tuần cho các hoạt động, tổng cột, rồi tổng tất cả là được kết quả mong muốn.

Tính từng hoạt động theo tuần (liệt kê) thì dễ dàng rồi xét khoảng thời gian hiệu theo số ngày trong tuần (min, max ngày) thì bạn tính tốt dễ dàng.

==> cần 1 bảng phụ

Em nghĩ cũng không quá khó với các bác đâu ạ. Nếu thử kéo dài thời gian kết thúc ra đến ngày 7/2/20 thì sai số theo em tính chỉ là 0.26%. Đấy là phải tính thêm số giờ theo ngày và theo tuần rồi mới cộng lũy kế lại.
Vấn đề em muốn là chỉ cần dựa vào cột F (Số giờ cho phép) và các cột C và D để rải vào các ngày từ lúc bắt đầu đến lúc kết thúc theo dạng lũy kế như ở dòng số 4 thôi và mục tiêu cuối cùng là đường biểu đồ vừa lên đúng với số giờ cho phép.

 

[tam888]

Em nghĩ cũng không quá khó với các bác đâu ạ. Nếu thử kéo dài thời gian kết thúc ra đến ngày 7/2/20 thì sai số theo em tính chỉ là 0.26%. Đấy là phải tính thêm số giờ theo ngày và theo tuần rồi mới cộng lũy kế lại.
Vấn đề em muốn là chỉ cần dựa vào cột F (Số giờ cho phép) và các cột C và D để rải vào các ngày từ lúc bắt đầu đến lúc kết thúc theo dạng lũy kế như ở dòng số 4 thôi và mục tiêu cuối cùng là đường biểu đồ vừa lên đúng với số giờ cho phép.
View attachment 217951

Xem file kèm
Với File thứ 2 thì bạn tự làm theo, và lưu ý kéo ngày đúng tuần +7 (file 2 phải kéo đến tháng 3)

 

[Phan Thế Hiệp]

Gãi đúng chỗ ngứa của thầy rồi
Mà em thấy không ổn ở chỗ thời gian cột B, C so không cố định, tính toán kiểu quy hồi, gần với bài tính thời gian định cư liên tục 5 năm của anh huuthangbd

Sáng nhìn vội, nên cũng hình dung trong đầu y vậy. Nhưng vừa về xem lại thì có lẽ nó nhẹ nhàng hơn tí!

Vấn đề em muốn là chỉ cần dựa vào cột F (Số giờ cho phép) và các cột C và D để rải vào các ngày từ lúc bắt đầu đến lúc kết thúc theo dạng lũy kế như ở dòng số 4 thôi và mục tiêu cuối cùng là đường biểu đồ vừa lên đúng với số giờ cho phép.

Chắc là công thức này:

I4=SUMPRODUCT((I1+COLUMN($A:$F)-1>=$B$2:$B$10)*(I1+COLUMN($A:$F)-1<=$C$2:$C$10)*$E$2:$E$10)

Chỉ Enter, rồi fill qua phải.

Thân

 

[HieuCD]

Sáng nhìn vội, nên cũng hình dung trong đầu y vậy. Nhưng vừa về xem lại thì có lẽ nó nhẹ nhàng hơn tí!

Chắc là công thức này:

I4=SUMPRODUCT((I1+COLUMN($A:$F)-1>=$B$2:$B$10)*(I1+COLUMN($A:$F)-1<=$C$2:$C$10)*$E$2:$E$10)

Chỉ Enter, rồi fill qua phải.

Thân

Chắc không phải rùi, dòng 4 là số lũy kế cộng dồn các tuần trước
Chúc bạn 1 tối vui

 

[Phan Thế Hiệp]

Chắc không phải rùi, dòng 4 là số lũy kế cộng dồn các tuần trước
Chúc bạn 1 tối vui

Vậy, cộng với ô đằng trước chắc "hợp với dáng em".
Cảm ơn anh @HieuCD.

I4=SUMPRODUCT((I1+COLUMN($A:$F)-1>=$B$2:$B$10)*(I1+COLUMN($A:$F)-1<=$C$2:$C$10)*$E$2:$E$10)+N(H4)
Enter, rồi fill qua phải.

Chúc anh ngày vui

 

[echo07]

Vậy, cộng với ô đằng trước chắc "hợp với dáng em".
Cảm ơn anh @HieuCD.

I4=SUMPRODUCT((I1+COLUMN($A:$F)-1>=$B$2:$B$10)*(I1+COLUMN($A:$F)-1<=$C$2:$C$10)*$E$2:$E$10)+N(H4)
Enter, rồi fill qua phải.

Chúc anh ngày vui

Cảm ơn các bác nhiều. Tối muộn vẫn hỗ trợ anh em. Chúc các bác một tuần mới nhiều niềm vui và hạnh phúc.

 

[echo07]

Vậy, cộng với ô đằng trước chắc "hợp với dáng em".
Cảm ơn anh @HieuCD.

I4=SUMPRODUCT((I1+COLUMN($A:$F)-1>=$B$2:$B$10)*(I1+COLUMN($A:$F)-1<=$C$2:$C$10)*$E$2:$E$10)+N(H4)
Enter, rồi fill qua phải.

Chúc anh ngày vui

Hay quá bác ạ. Vấn đề phát sinh là dữ liệu của em có thể thay đổi nên các cột cũng vì thế thay đổi theo. Với "COLUMN($A:$F)" như này em thêm hoặc bớt cột ở trong khoảng đó đều cho kết quả bị sai. Em đã thừ thay thế bằng
=SUMPRODUCT((J1+{1,2,3,4,5,6}-1>=$A$2:$A$10)*(J1+{1,2,3,4,5,6}-1<=$B$2:$B$10)*$F$2:$F$10)+N(I5) thì có thể thay đổi số cột thoải mái. Vậy mình có thể thay thế dạng COLUMN($A:$F) này bằng dạng khác để không bị ảnh hưởng bởi số cột trong đó không ạ.
Một vấn đề nhỏ nữa là trong ví dụ này thì ngày cuối cùng kết thúc là 19/12 nhưng kết quả tính đến ngày 16/12 đã hết khối lượng rồi do đó 3 ngày sau không được tính ạ.

 

[Phan Thế Hiệp]

Hay quá bác ạ. Vấn đề phát sinh là dữ liệu của em có thể thay đổi nên các cột cũng vì thế thay đổi theo. Với "COLUMN($A:$F)" như này em thêm hoặc bớt cột ở trong khoảng đó đều cho kết quả bị sai. Em đã thừ thay thế bằng
=SUMPRODUCT((J1+{1,2,3,4,5,6}-1>=$A$2:$A$10)*(J1+{1,2,3,4,5,6}-1<=$B$2:$B$10)*$F$2:$F$10)+N(I5) thì có thể thay đổi số cột thoải mái. Vậy mình có thể thay thế dạng COLUMN($A:$F) này bằng dạng khác để không bị ảnh hưởng bởi số cột trong đó không ạ.
Một vấn đề nhỏ nữa là trong ví dụ này thì ngày cuối cùng kết thúc là 19/12 nhưng kết quả tính đến ngày 16/12 đã hết khối lượng rồi do đó 3 ngày sau không được tính ạ.

View attachment 218011

Nếu bạn muốn tùy biến "Ngày bất kỳ", thì tôi đưa ra các trường hợp sau:

1. Phải xử lý "Ngày" bạn ghi nhận vào hàng 1 (I1: U1) là ngày bất kỳ trong tuần. Ví dụ: I1 có thể bắt đầu từ Chủ nhật (29/09/2019). Công thức sẽ loại ra các Ngày Chủ nhật cho bạn, đồng thời chỉ lấy những ngày có trong khoảng thời gian "Ngày bắt đầu - Ngày kết thúc" của bảng C2: D10
2. Công thức sẽ lấy các ngày trong khoảng từ giá trị "Ngày" của cột đang thực hiện cho đến "Nhỏ" hơn "Ngày" của cột liền kế sau. Vd: công thức tại I4 thì nó lấy các ngày "thỏa điều kiện" từ ngày 30/09 đến ngày 05/10 (như dữ liệu trong file kèm thể hiện tại I1 và J1). Nhờ điều này, bạn không cần phải buộc giữa các cột là 6 ngày cố định, mà có thể tăng giảm tùy thích, (do đó không còn ngại Column(A:F))
3. Nếu cột "Ngày" liền kế sau trống rỗng, thì chỉ lấy duy nhất "Ngày" của cột mà công thức đang thực hiện.

Do các yếu tố trên, nên công thức sẽ dài hơn:

I4=SUM((TRANSPOSE(IFERROR(ROW(INDIRECT(I$1&":"&J$1-1)),I$1)*(WEEKDAY(IFERROR(ROW(INDIRECT(I$1&":"&J$1-1)),I$1))>1))>=$B$2:$B$10)*(TRANSPOSE(IFERROR(ROW(INDIRECT(I$1&":"&J$1-1)),I$1)*(WEEKDAY(IFERROR(ROW(INDIRECT(I$1&":"&J$1-1)),I$1))>1))<=$C$2:$C$10)*$E$2:$E$10)+N(H4)

Kết thúc bằng Ctrl+Shift+Enter.

Thân

 

[echo07]

Nếu bạn muốn tùy biến "Ngày bất kỳ", thì tôi đưa ra các trường hợp sau:

1. Phải xử lý "Ngày" bạn ghi nhận vào hàng 1 (I1: U1) là ngày bất kỳ trong tuần. Ví dụ: I1 có thể bắt đầu từ Chủ nhật (29/09/2019). Công thức sẽ loại ra các Ngày Chủ nhật cho bạn, đồng thời chỉ lấy những ngày có trong khoảng thời gian "Ngày bắt đầu - Ngày kết thúc" của bảng C2: D10
2. Công thức sẽ lấy các ngày trong khoảng từ giá trị "Ngày" của cột đang thực hiện cho đến "Nhỏ" hơn "Ngày" của cột liền kế sau. Vd: công thức tại I4 thì nó lấy các ngày "thỏa điều kiện" từ ngày 30/09 đến ngày 05/10 (như dữ liệu trong file kèm thể hiện tại I1 và J1). Nhờ điều này, bạn không cần phải buộc giữa các cột là 6 ngày cố định, mà có thể tăng giảm tùy thích, (do đó không còn ngại Column(A:F))
3. Nếu cột "Ngày" liền kế sau trống rỗng, thì chỉ lấy duy nhất "Ngày" của cột mà công thức đang thực hiện.

Do các yếu tố trên, nên công thức sẽ dài hơn:

I4=SUM((TRANSPOSE(IFERROR(ROW(INDIRECT(I$1&":"&J$1-1)),I$1)*(WEEKDAY(IFERROR(ROW(INDIRECT(I$1&":"&J$1-1)),I$1))>1))>=$B$2:$B$10)*(TRANSPOSE(IFERROR(ROW(INDIRECT(I$1&":"&J$1-1)),I$1)*(WEEKDAY(IFERROR(ROW(INDIRECT(I$1&":"&J$1-1)),I$1))>1))<=$C$2:$C$10)*$E$2:$E$10)+N(H4)

Kết thúc bằng Ctrl+Shift+Enter.

Thân

Cảm ơn bác rất nhiều. Em sẽ nghiên cứu thêm để áp dụng cho thuận tiện nhất ạ. Chúc bác và gia đình sức khỏe và hạnh phúc.

 

[Phan Thế Hiệp]

Chúc bác và gia đình sức khỏe và hạn phúc.

Vừa bị "Hạn", lại vừa được "Phúc", thì kể như "huề vốn"
A1= -9 + 9 = 0

Vậy, nên ghi "Chúc huề vốn"
Thân