SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện
- Thread starter Nguyễn Duy Tuân
- Ngày gửi
echo07
Nguyệt Hà
- Tham gia
- 24/8/07
- Bài viết
- 436
- Được thích
- 318
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 ạ.
=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 ạ.
Phan Thế Hiệp
-⍟-
- Tham gia
- 10/10/17
- Bài viết
- 3,558
- Được thích
- 12,171
- Giới tính
- Nam
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:
- 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:
- Và 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),
- 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".
- Nó tính các "Ngày" theo mốc I1: U1:
- Công thức của tôi dựa vào yêu cầu của bạn ghi:
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. - 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)
Lần chỉnh sửa cuối:
hvhung1602
Thành viên mới
- Tham gia
- 15/2/15
- Bài viết
- 17
- Được thích
- 3
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
Lần chỉnh sửa cuối:
Phan Thế Hiệp
-⍟-
- Tham gia
- 10/10/17
- Bài viết
- 3,558
- Được thích
- 12,171
- Giới tính
- Nam
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)))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
hvhung1602
Thành viên mới
- Tham gia
- 15/2/15
- Bài viết
- 17
- Được thích
- 3
Ô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
- 14/11/19
- Bài viết
- 1
- Được thích
- 0
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.
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:
Phan Thế Hiệp
-⍟-
- Tham gia
- 10/10/17
- Bài viết
- 3,558
- Được thích
- 12,171
- Giới tính
- Nam
Tham khảo các công thức file đính kèm tại:
Nhờ tìm lỗi trong công thức đếm (loại bỏ đếm trùng)
Chào mọi người, Mình có tham khảo công thức đếm nhưng loại bỏ đếm trùng tại Link này, đặc biệt là công thức ở cuối thread đó nhưng khi áp dung vào bài của mình thì không ra kết quả. Nhờ các anh/chị hỗ trợ giải thích bị sai ở điểm nào, nghĩ mãi mà vẫn không biết tại sao Rất cám ơn
www.giaiphapexcel.com
Thân
dunghtgiga
Thành viên chính thức
- Tham gia
- 31/1/15
- Bài viết
- 81
- Được thích
- 11
- Giới tính
- Nam
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 ?
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
- 28/2/17
- Bài viết
- 2,880
- Được thích
- 4,039
- Giới tính
- Nam
Bạn cho ví dụ kết quả các phép tính mình xem thử?
dunghtgiga
Thành viên chính thức
- Tham gia
- 31/1/15
- Bài viết
- 81
- Được thích
- 11
- Giới tính
- Nam
Ý 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"
Hàm Sumproduct không dùng được ký tự thay thế "*", "?"
dazkangel
<New Horizons>
- Tham gia
- 28/2/17
- Bài viết
- 2,880
- Được thích
- 4,039
- Giới tính
- Nam
Ý bạn đó là có giải pháp thay thế thì dùng hàm search và iserror kết hợp là được.
dunghtgiga
Thành viên chính thức
- Tham gia
- 31/1/15
- Bài viết
- 81
- Được thích
- 11
- Giới tính
- Nam
Mình có làm cái vd cụ thể như thế này
=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.
=SUMPRODUCT(ISERR(FIND(1,$D$2:$D$500))*$B$2:$B$500*$C$2:$C$500)
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
(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
hongquan85army
Thành viên mới
- Tham gia
- 13/9/10
- Bài viết
- 16
- Được thích
- 0
E nhờ bác giúp sửa lại công thức hàm pro này với. Cụ thể như sau:
1/nếu e để dòng 13 thì ô F14 nó ko hiện kết quả, nếu xóa dòng 13 đi nó mới cho lên kết quả.
2/ Nếu e dùng data lọc dữ liệu tại cột E thì dùng công thức gì kết hợp với công thức Pro tại F14 theo dữ liệu lọc được không
Lần chỉnh sửa cuối:
Công thức khá phức tạp, đặt tên cho gọn
SoPhieu = 'vât tư Chùa'!$F$4:$F$13
DK =SUBTOTAL(109,OFFSET(SoPhieu,ROW(INDIRECT("1:"&ROWS(SoPhieu)))-1,,1,))
Mã:
F14 =SUM(--(FREQUENCY(IF(DK>0,MATCH(DK,DK,0),""),ROW(INDIRECT("1:"&ROWS(SoPhieu))))>0))dazkangel
<New Horizons>
- Tham gia
- 28/2/17
- Bài viết
- 2,880
- Được thích
- 4,039
- Giới tính
- Nam
Tóm lại là để tính gì vậy anh ?
Bài viết mới nhất
- Question
- Question
- Đã giải quyết
- Question
