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

Liên hệ QC
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:
Mã:
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
 

File đính kèm

  • Vi du.xlsx
    13.7 KB · Đọc: 14
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:
Mã:
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.
 
Lần chỉnh sửa cuối:
Chào anh Hiệp và các anh. Tình cờ em copy được đoạn công thức này áp vào thấy kết quả cũng đúng

=SUMPRODUCT(IFERROR(IF(I$1<$B$2:$B$10,0,IF(I$1>=$C$2:$C$10,1,(I$1-$B$2:$B$10)/($C$2:$C$10-$B$2:$B$10+1)))*$F$2:$F$10,0)) Ctr+Shift+Enter
Có thể là đầu bài ban đầu của em chưa đủ rõ ràng về yêu cầu nên đôi khi lại làm khó người giúp đỡ.
Chia sẻ lên đây không có ý gì khác ngoài việc học hỏi thêm vì em không hiểu đoạn này "(I$1-$B$2:$B$10)/($C$2:$C$10-$B$2:$B$10+1)" và em nghĩ đây chính là chìa khóa để giải quyết vấn đề. Các anh chị giải thích giùm em được không ạ.
 

File đính kèm

  • Vi du (8).xlsx
    14.2 KB · Đọc: 10
Chào anh Hiệp và các anh. Tình cờ em copy được đoạn công thức này áp vào thấy kết quả cũng đúng
Tuy hai kết quả tính đến ngày 19/12/2019 là giống nhau, nhưng giải thuật của hai bài khác nhau:
  1. Công thức của bạn: Kết quả đúng là đương nhiên! vì công thức đó là công thức tính tỉ lệ %: "Số ngày thể hiện" từ I1: U1 so với "Tổng số ngày thực hiện" các hoạt động (từ 1 đến 10 hoạt động trên hai cột B và C). Cụ thể:
    • Nó tính các "Ngày" theo mốc I1: U1:
      • Nếu nhỏ hơn các "Ngày" ở cột B, thì không tính (giá trị 0)
      • Nếu lớn hơn các "Ngày" ở cột B:
        1. nếu lớn hơn hoặc bằng các "Ngày" ở cột C: thì tính là 100% "Số giờ cho phép (tức 1 x F2: F10),
        2. Ngược lại, và nếu nhỏ hơn các "Ngày" ở cột C: thì tính tỉ lệ: (I$1-$B$2:$B$10) / ($C$2:$C$10-$B$2:$B$10+1), với:
          • (I$1-$B$2:$B$10): "Tổng các ngày" mà I1 > "Các ngày tại cột B", tức lấy "Tổng số ngày" mà các ngày từ lúc "Bắt đầu" đến "Ngày mốc I1" có bao nhiêu ngày, chia cho "Tổng số ngày thực tế (kể cả ngày CN)" theo từng hoạt động".
          • Ví dụ: J1= 07/10/2019, vậy chỉ có 1 dòng của "Hoạt động 1" có ngày "Bắt đầu" nhỏ hơn J1: 30/09/2019; Lấy 07/10/2019 - 30/09/2019 được 7 ngày, lấy 7 ngày chia cho "Tổng số ngày thực tế (kể cả ngày CN) của từng hoạt động", là mảng con số cố định từ công thức: ($C$2:$C$10-$B$2:$B$10+1)={73,55,51,33,33,33,28,34,23} (Xem công thức D13: D21 giải thích ý nghĩa). Do chỉ có dòng "Hoạt động 1" thỏa điều kiện nên nó lấy 7 ngày chia cho 73 ngày của dòng hoạt động đó= 9.59% x 1.309 (Số giờ cho phép cột F) = 125,5205 giờ thực hiện.
    • Tóm lại, nó tính tỉ lệ: "Tổng số ngày thực hiện" đến "Ngày" nhỏ hơn mốc tại dòng 1 (I1: U1), chia cho "Tổng số ngày thực tế (kể cả ngày CN)" theo từng hoạt động", rồi nhân tỉ lệ đó với "Số giờ cho phép".
  2. Công thức của tôi dựa vào yêu cầu của bạn ghi:
    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
    và theo công thức bạn tính đính kèm trong file bạn gửi: cứ 1 cột là 6 ngày kể từ "Mốc" đang đứng. Tức công thức tính ra số ngày thực hiện (trừ ngày CN) từ "Mốc" đang đứng cho đến ngày nhỏ hơn "Mốc liền kề tiếp cột sau", rồi cộng lũy kế các ngày đã thực hiện.
  3. Nói tóm lại, cái khác nhau của hai cách tính là:
    • Công thức bạn sưu tầm tính "Tỉ lệ" các "Ngày trước cho đến "Mốc" yêu cầu (tại dòng 1 I1: U1)" so với "Ngày thực tế theo dòng hoạt động" (Công thức tính Ngày thụt lùi so với ngày Mốc)
    • Công thức của tôi phân định "Số Ngày thực hiện" từ "Mốc yêu cầu này" đến "Mốc yêu cầu kế tiếp". (Công thức tính Ngày tiến tới so với ngày Mốc)
Thân
 
Lần chỉnh sửa cuối:
Cảm ơn anh nhiều, vậy em mới nói là có thể do cách diễn dải câu hỏi không rõ ràng ạ. Chúc anh ngày vui nhé.
 
Em chào anh chị, em muốn biết sâu thêm về hàm sumproduct nên đã lội lại từ đầu topic này nhưng có công thức này ở page 18 em nghĩ mãi mà không hiểu được huhu . có ai có thể giải thích cho em logic ở đây không ạ. em có note lại trong file
 

File đính kèm

  • Dem duy nhat co dieu kien1.xls
    29 KB · Đọc: 6
Lần chỉnh sửa cuối:
Em chào anh chị, em muốn biết sâu thêm về hàm sumproduct nên đã lội lại từ đầu topic này nhưng có công thức này ở page 18 em nghĩ mãi mà không hiểu được huhu . có ai có thể giải thích cho em logic ở đây không ạ. em có note lại trong file
Công thức bạn hỏi đâu có liên quan gì đến hàm Sumproduct()!?
Tuy nhiên, có thể giúp bạn tìm hiểu: đó là công thức "Đếm loại trùng theo điều kiện".
Bạn có thể dùng công thức sau ngắn gọn và dễ hiểu hơn:
Mã:
M2=COUNT(1/(MATCH($B$2:$B$10,IF($A$2:$A$10=L2,$B$2:$B$10),)=ROW($1:$10)))
Kết thúc bằng Ctrl+Shift+Enter.

Bạn tham khảo file kèm, có giải thích chi tiết kết quả từng phần hành công thức.

Thân
 

File đính kèm

  • Dem duy nhat co dieu kien1.xls
    34 KB · Đọc: 23
Công thức bạn hỏi đâu có liên quan gì đến hàm Sumproduct()!?
Tuy nhiên, có thể giúp bạn tìm hiểu: đó là công thức "Đếm loại trùng theo điều kiện".
Bạn có thể dùng công thức sau ngắn gọn và dễ hiểu hơn:
Mã:
M2=COUNT(1/(MATCH($B$2:$B$10,IF($A$2:$A$10=L2,$B$2:$B$10),)=ROW($1:$10)))
Kết thúc bằng Ctrl+Shift+Enter.

Bạn tham khảo file kèm, có giải thích chi tiết kết quả từng phần hành công thức.

Thân
Ôi em hiểu rồi ạ:(( . Thank pro đã chỉ giáo chi tiết. hic hic em thấy hàm ROW và hàm offset được sử dụng trong mảng rất là ảo diệu, nhưng em k biết logic chung để sử dụng nó. em thấy hàm offset được áp dụng để " trượt" dòng theo chiều dọc trên mảng vậy ạ. còn row thì tạo ra 1 mảng theo cột dọc để check điều kiện, có đúng không ạ. e nghĩ là học được hướng tư duy thì mình sẽ tiến bộ nhanh hơn .Trình độ có hạn nên diễn đạt lung tung, mong cả nhà chỉ giáo ạ :((
 
Lần chỉnh sửa cuối:
Chào các anh em Excel Master, mình là thành viên mới, đang mày mò tìm hiểu để học thêm Excel.
Nhờ mọi người giúp giùm mình cái này với:
Mình có cột A là các giá trị a, b, c, x, y, z và có lặp lại. Cột B, C, D là giá trị 0,1,2. Mình cần
1) count các dòng ở cột A (ko tính các dòng lặp lại). Vd: mình có 100 dòng, 30 dòng lặp lại giá trị thì công thức sẽ cho ra kết quả 70.
2) tương tự câu 1 nhưng thêm điều kiện là phải thoả các điều kiện ở cột B, C, D.
Vd: mình countifs cả B, C, D = 0, thì ra được 50. Nhưng trong 50 dòng này có 10 dòng trùng nhau ở cột A, vì vậy kết quả mình cần là 40.
Mình đọc thấy Sumproduct có vẻ giải quyết được nhưng thật sự không biết phải làm sao.
Cảm ơn mọi người rất nhiều.
 
Lần chỉnh sửa cuối:
........mình là thành viên mới, đang mày mò tìm hiểu để học thêm Excel.
Nhờ mọi người giúp giùm mình cái này với:
Mình có cột A là các giá trị a, b, c, x, y, z và có lặp lại. Cột B, C, D là giá trị 0,1,2. Mình cần
1) count các dòng ở cột A (ko tính các dòng lặp lại). Vd: mình có 100 dòng, 30 dòng lặp lại giá trị thì công thức sẽ cho ra kết quả 70.
2) tương tự câu 1 nhưng thêm điều kiện là phải thoả các điều kiện ở cột B, C, D.
Vd: mình countifs cả B, C, D = 0, thì ra được 50. Nhưng trong 50 dòng này có 10 dòng trùng nhau ở cột A, vì vậy kết quả mình cần là 40.
Mình đọc thấy Sumproduct có vẻ giải quyết được nhưng thật sự không biết phải làm sao.
Cảm ơn mọi người rất nhiều.
Tham khảo các công thức file đính kèm tại:

Thân
 
1. Hàm Countifs, ví dụ:
COUNTIFS(A1:A10,"01",B1:B10,"<>"&"*[text]*")

2. Hàm Sumproduct, ví dụ:
SUMPRODUCT((A1:A10="01")*(B1:B10<>"*[text]*"))

=> *[text]* hàm Sumproduct ko hiểu được như hàm Countifs: phương thức nào diễn giải *[text]* trong hàm Sumproduct nhờ các bạn chỉ giúp mình ?
 
1. Hàm Countifs, ví dụ:
COUNTIFS(A1:A10,"01",B1:B10,"<>"&"*[text]*")

2. Hàm Sumproduct, ví dụ:
SUMPRODUCT((A1:A10="01")*(B1:B10<>"*[text]*"))

=> *[text]* hàm Sumproduct ko hiểu được như hàm Countifs: phương thức nào diễn giải *[text]* trong hàm Sumproduct nhờ các bạn chỉ giúp mình ?
Bạn cho ví dụ kết quả các phép tính mình xem thử?
 
Bạn cho ví dụ kết quả các phép tính mình xem thử?
Ý mình muốn hỏi chỗ *[text]* trong hàm SUMPRODUCT diễn giải như thế nào để hàm hiểu được.
Ví dụ: B1:B10 <> "Cam" thì hàm OK còn B1:B10 <> "*Cam*" thì hàm NOK
Chỗ * đại diện cho các ký tự trước và sau "Cam"
 
1. Hàm Countifs, ví dụ:
COUNTIFS(A1:A10,"01",B1:B10,"<>"&"*[text]*")

2. Hàm Sumproduct, ví dụ:
SUMPRODUCT((A1:A10="01")*(B1:B10<>"*[text]*"))

=> *[text]* hàm Sumproduct ko hiểu được như hàm Countifs: phương thức nào diễn giải *[text]* trong hàm Sumproduct nhờ các bạn chỉ giúp mình ?
Hàm Sumproduct không dùng được ký tự thay thế "*", "?"
 
Ý bạn đó là có giải pháp thay thế thì dùng hàm search và iserror kết hợp là được.
Mình có làm cái vd cụ thể như thế này

1580456655038.png

=SUMPRODUCT($B$2:$B$13,$C$2:$C$13*($A$2:$A$13="Cam")*($D$2:$D$13<>"*loại 1*"))

mình muốn diễn giải chỗ chỉ lấy loại 2 còn tất cả loại 1 ko lấy: dùng hàm search và iserror như thế nào nhờ bạn chỉ giúp.
 
Em chào các bác, em có hàm này ông anh bàn giao sang mà k hiểu ý nghĩa nó dịch như thế nào, nhờ các bác trợ giúp em với ạ
(SUMPRODUCT((A19<=$C$4:$C$11)*(A19>$B$4:$B$11)*(A19-$B$4:$B$11)*$F$4:$F$11) + SUMPRODUCT((A19>$C$4:$C$11)*($C$4:$C$11-$B$4:$B$11)*$F$4:$F$11))/10^9
 
Web KT
Back
Top Bottom