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

Tham gia ngày
10 Tháng mười 2017
Bài viết
2,885
Được thích
8,534
Điểm
360
Nơi ở
Sài Gòn
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

echo07

Nguyệt Hà
Tham gia ngày
24 Tháng tám 2007
Bài viết
265
Được thích
193
Điểm
695
Nơi ở
Đối diện Gpe
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:

echo07

Nguyệt Hà
Tham gia ngày
24 Tháng tám 2007
Bài viết
265
Được thích
193
Điểm
695
Nơi ở
Đối diện Gpe
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

Tham gia ngày
10 Tháng mười 2017
Bài viết
2,885
Được thích
8,534
Điểm
360
Nơi ở
Sài Gòn
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:

hvhung1602

Thành viên mới
Tham gia ngày
15 Tháng hai 2015
Bài viết
16
Được thích
3
Điểm
165
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

Lần chỉnh sửa cuối:
Tham gia ngày
10 Tháng mười 2017
Bài viết
2,885
Được thích
8,534
Điểm
360
Nơi ở
Sài Gòn
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

hvhung1602

Thành viên mới
Tham gia ngày
15 Tháng hai 2015
Bài viết
16
Được thích
3
Điểm
165
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:

phattranvn

Thành viên mới
Tham gia ngày
14 Tháng mười một 2019
Bài viết
1
Được thích
0
Điểm
13
Tuổi
30
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:
Tham gia ngày
10 Tháng mười 2017
Bài viết
2,885
Được thích
8,534
Điểm
360
Nơi ở
Sài Gòn
........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
 

dunghtgiga

Thành viên mới
Tham gia ngày
31 Tháng một 2015
Bài viết
23
Được thích
5
Điểm
365
Nơi ở
Vũng Tàu
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 ?
 

dazkangel

<New Horizons>
Tham gia ngày
28 Tháng hai 2017
Bài viết
2,840
Được thích
3,746
Điểm
360
Nơi ở
Đồng Nai
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ử?
 

dunghtgiga

Thành viên mới
Tham gia ngày
31 Tháng một 2015
Bài viết
23
Được thích
5
Điểm
365
Nơi ở
Vũng Tàu
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"
 

HieuCD

Chuyên gia GPE
Tham gia ngày
14 Tháng chín 2010
Bài viết
7,009
Được thích
13,677
Điểm
1,560
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ế "*", "?"
 

dunghtgiga

Thành viên mới
Tham gia ngày
31 Tháng một 2015
Bài viết
23
Được thích
5
Điểm
365
Nơi ở
Vũng Tàu
Ý 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.
 

HieuCD

Chuyên gia GPE
Tham gia ngày
14 Tháng chín 2010
Bài viết
7,009
Được thích
13,677
Điểm
1,560

teutamteu

Thành viên hoạt động
Tham gia ngày
11 Tháng chín 2007
Bài viết
140
Được thích
12
Điểm
670
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
 
Top Bottom