SUMPRODUCT và Công thức mảng – Phép tính có nhiều điều kiện
Mình xin phân tích cách dùng SumProduct và Công thức mảng.
Hàm SumProduct:
Cấu trúc SUMPRODUCT(array1,array2,array3, …)
Array – Mảng dữ liệu là một tập hợp dãy giá trị liên tiếp trong một khảng nào đó. VD A1:C1 hoặ A1:A10,…
Phép tính này cho phép chúng ta tính tổng của tích array1*array2*array3* …array30.
VD: A: Số lượng; B: Đơn giá
A1 =2 B1=20 C1="Cam" D1="Giống lai"
A2 =3 B2=10 C2="Bưởi" D2="Không"
A3 =4 B3=25 C3="Cam" D3="Không"
1560
Bây giờ cần tính doanh thu của các loại hoa quả
array1=A1:A3
array2=B1:B3
Công thức =SumProduct(A1:A3, B1:B3) = 170
Bản chất công thức làm việc như thế này =A1*B1+A2*B2+A3*B3 kết quả là 170
Nhắc lại về phép tính logic:
Giá trị kiểu logic chỉ cho ra 1 trong 2 giá trị là TRUE/1, FALSE/0
Phép toán logic:<, >, <>, =, >=, <=, Not()
VD:
2>3=False
3>1=True
4>3=True
*) Logic và – AND
=(2>3)*(3>1)*(4>3)=False*True*True=0*1*1=False/0 tương đương với hàm AND(2>3,3>1,4>3). Ít nhất một logic=False thì kết quả sẽ là False hay 0.
* Logic hoặc – OR
=(2>3)+(3>1)+(4>3)=False+True+True=0+1=True/1 tương đương với hàm OR(2>3,3>1,4>3). Ít nhất một logic=True thì kết quả sẽ là True hay 1.
Lưu ý tổng của các giá trị là True=True=1).
*) Tính tổng có nhiều điều kiện:
Cách 1: dùng SUMPRODUCT
Tính tổng doanh thu của loại là "Cam"
=SUMPRODUCT(A1:A3,B1:B3*(C1:C3="Cam")) hoặc =SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) = 140
Công thức tính như sau:
=A1*B1*(C1="Cam")+A2*B2*(C2="Cam")+A3*B3*(C3="Cam")
=2*20*True+3*10*False+4*25*True
=2*20*1+3*10*0+4*25*1= 140
Cách 2: dung Công thức mảng – "Formula Array"
=Sum(IF(C1:C3="Cam",A1:A3*B1:B3,0))
Kết thức nhẫn tổ hợp CTRL+SHIFT+ENTER. Với cách làm này EXCEL sẽ phân tích như sau:
Xét trên từng dòng trong mảng (array)
dòng1: (c1="Cam")=true nên lấy A1*B1=2*20
dòng2: (c2="Cam")=false nên lấy 0 (theo cách của lấy của hàm IF)
dòng3: (c3="Cam")=true nên lấy A3*B3=4*25
Sau khi chạy hết các dòng, EXCEL sẽ dùng hàm SUM để tính tổng kết quả tính được ở từng dòng=2*20+0+2*25=140. Nếu trong công thức là hàm khác hàm SUM thì cách tính sẽ theo hàm đó.
Như vậy có 2 cách tính:
=SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) và
=Sum(IF(C1:C3="Cam",A1:A3*B1:B3,0))
*) Vậy tại sao không dùng là =SUM(A1:A3*B1:B3*(C1:C3="Cam"))
mà phải dùng hàm =SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) ?
Các bạn nhớ lại cấu trúc của SUM là
SUM(number1,number2, …)
Còn SUMPRODUCT là
SUMPRODUCT(array1,array2,array3, …)
number <> array
Nếu SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) rồi ENTER là đúng vì đối số của nó phải là mảng – Array.
Nếu công thức =SUM(A1:A3*B1:B3*(C1:C3="Cam")) rồi ENTER kết quả là #VALUE! -lỗi vì A1:A3 là một array chứ không phải là một number.
Nếu nhấn tổ hợp CTRL+SHIFT+ENTER. Với cách làm này EXCEL sẽ phân tích như sau:
Xét trên từng dòng trong mảng (array)
dòng1: A1*B1*(c1="Cam")=2*20*True=2*20*1
dòng2: A2*B2*(c2="Cam")=3*10*False=3*10*0
dòng3: A3*B3*(c3="Cam")=2*25*True=4*25*1
Sau khi chạy hết các dòng, EXCEL sẽ dùng hàm SUM để tính tổng kết quả tính được ở từng dòng=2*20*1+3*10*0
+4*25*1=140.
Vậy vẫn dùng được =SUM(A1:A3*B1:B3*(C1:C3="Cam")) với điều kiện nhấn tổ hợp phím CTRL+SHIFT+ENTER
Như vậy đến đây chúng ta có có 3 cách tính:
=SUMPRODUCT(A1:A3*B1:B3*(C1:C3="Cam")) nhấn phím ENTER
=SUM(IF(C1:C3="Cam",A1:A3*B1:B3,0)) nhấn phím CTRL+SHIFT+ENTER
=SUM(A1:A3*B1:B3*(C1:C3="Cam")) nhấn phím CTRL+SHIFT+ENTER
Chúng có thể kết hợp rất nhiều điều kiện vào trong hàm thông qua phép toán logic nhân-và- And, cộng – hoặc – Or.
*) Dùng hàm SUMPRODUCT hay dùng SUM kết hợp CTRL+SHIFT+ENTER đều cho ra được kết quả như nhau chính là do phép toán logic của bạn.
*) Hàm SUMPRODUCT chỉ có thể tính tổng theo nhiều điều kiện
*) Công thức mảng – Formula Array ngoài việc tính tổng có nhiều điều kiện còn làm rất nhiều phép tính khác do cách sử dụng hàm mà thôi.
Thân chào!
www.giaiphapexcel.com/diendan/threads/sumproduct-v%C3%A0-c%C3%B4ng-th%E1%BB%A9c-m%E1%BA%A3ng-ph%C3%A9p-t%C3%ADnh-c%C3%B3-nhi%E1%BB%81u-%C4%91i%E1%BB%81u-ki%E1%BB%87n.59/
Khóa học Power PI – Ứng dung trong Nhân sự
TỔNG QUAN KHÓA HỌC: POWER BI CHO NGÀNH NHÂN SỰ Khóa học Power BI cho Nhân sự được thiết kế dành riêng cho các...
Xem khóa học
Híc, các bạn đang làm bài này nên ấn Ctrl+F trong sheet BN tìm ngày 30/89/13 rồi sửa lại cho đúng. Chỉ vì cái lỗi này mà tối qua mình mất 2 tiếng đồng hồ mà không làm được bài này.
Mình xin mạo muội chỉ ra cách làm này cho bạn nvuphi:
Trước tiên bạn đặt tên cho các mảng như sau:
DiaChi=BN!$I$5:$I$422
ThoiGian=BN!$J$5:$J$422
Tại ô C8 sheet TungPhuong nhập:
Tương tự, tại ô D8 bạn nhập:
E8 thì
…
Các tham chiếu phải có cùng kích thược, ở đây bạn tới dòng 5, bạn thử sửa hết tới dòng 15 thử xem.
độ dài các mảng phải bằng nhau chứ bạn
Số tiền, E18:
Kéo xuống, còn lại quy đổi ra VND chắc là bạn biết rồi.
SumìfS() cho nó lành
Bài này sửa lại ngày tháng cho đúng rồi dùng PivotTable cho nó khỏe, vừa dễ làm, dễ cập nhật, nhẹ file.
đổi mấy cái T1,T2,T3,T4 ra 1,2,3,4
excel 2003 thì làm như vậy
excel 2007 về sau thì làm như vậy (tôi làm trên máy 2003 nên chưa kiểm tra kết quả)
Mấy chỗ có "C*" đều sai hết. Ký tự đại diện "*" chỉ dùng cho hàm SUMIF và COUNTIF
Vậy phải sửa lại thành:
hoặc dùng COUNIF:
Thử công thức này đi bạn:
=SUMPRODUCT((Data!$A$2:$A$20=A2)*(Data!$F$2:$F$20>=F2)*(Data!$F$2:$F$20<=G2)*Data!$G$2:$G$20)
Ngày bắt đầu là rỗng thì lấy ngày nào?
Ngày kết thúc rỗng thì lấy ngày nào?
Cả 2 cùng rỗng thì làm sao?
Bạn nên nói rõ hơn đi.
Thử với cái này xem:
=SUMIFS(Data!$G$2:$G$20,Data!$A$2:$A$20,A2,Data!$B$2:$B$20,B2,Data!$C$2:$C$20,C2,Data!$D$2:$D$20,D2,Data!$E$2:$E$20,E2,Data!$F$2:$F$20,IF(F2,">=" & F2,"<=" & G2),Data!$F$2:$F$20,IF(G2,"<=" & G2,">=" & F2))
Công thức tổng quát cho nó:
=IF(AND(F2="",G2=""),SUMIFS(Data!$G$2:$G$20,Data!$A$2:$A$20,A2,Data!$B$2:$B$20,B2,Data!$C$2:$C$20,C2,Data!$D$2:$D$20,D2,Data!$E$2:$E$20,E2),SUMIFS(Data!$G$2:$G$20,Data!$A$2:$A$20,A2,Data!$B$2:$B$20,B2,Data!$C$2:$C$20,C2,Data!$D$2:$D$20,D2,Data!$E$2:$E$20,E2,Data!$F$2:$F$20,IF(F2,">="&F2,"<="&G2),Data!$F$2:$F$20,IF(G2,"<="&G2,">="&F2)))
Sao bạn không dùng Pivot Table cho nó tiện, công thức mà cho từng ô như thế thì không thể làm hết đâu.
cái này countifs được mà???
Nếu dữ liệu như mô tả của bạn thì chỉ cần sử dụng hàm SUMIF thôi, bạn xem file đính kèm nhé. Kết quả bạn mong muốn nằm ở vùng bôi màu vàng trong file.
Nếu em muốn thực hành bài này với Sumproduct thì có thể thay chỗ * đó thành hàm Left lấy số ký tự ra và so sánh với điều kiện em cần là OK, vì bài toán em không nói rõ phải dùng Sumproduct nên "anh" dùng SumIF cho nó thân thiện, hehehee
Ừ, cái hàm Sumproduct này tính….buồn cười thật
Híc +-+-+-++-+-+-++-+-+-+
h11 =sumproduct(($a$2:$a$39=$g11)*(month($c$2:$c$39)=h$10)*$e$2:$e$39)
Kết thúc bằng ctrl+shift+enter
Không được vì trong File có cả dầu . phân cách giữa các ngày chứ không phải chỉ có dấu , như ví dụ bạn đưa ra ban đầu
Bạn sửa công thức thành:
Paste công thức vào rồi, phải nhớ nhấn tổ hợp phím CTRL+SHIFT+ENTER
vì đây là công thức mảng (^.^ ặc ặc ^.^ ) … nếu lần này mà không ra nữa thì mình bó tay nhé .
bạn có làm như Phong hướng dẫn?
Nếu chèn thêm 1 dòng trống nữa thì công thức thế này:
Chỉ cần:
H3=SUMPRODUCT((A3:A6=F3)*(C3:C6=G3))
Hoặc đơn giản
H3=COUNTIFS(A3:A6,F3,C3:C6,G3)
Chúc bạn ngày vui.
Vậy thì thử vầy:
H3=SUMPRODUCT((A3:A6=F3)*(C3:C6=G3)/(COUNTIFS(B3:B6,B3:B6,A3:A6,A3:A6,C3:C6,C3:C6)))Enter
Chúc bạn ngày vui.
=LEFT(A1,FIND("-",A1)-1)
Dò trùng bạn có thể sử dụng CF là ra mà, hoặc có thể dùng hàng index(match(match()))
Điều chỉnh:
AM7=SUM(ISNUMBER(–G7:AK7)*(G7:AK7>8)*($G$6:$AK$6<>"CN")*(IFERROR(–G7:AK7,0)-8))Kết thúc bằng Ctrl+Shift+Enter, rồi fill xuống.
Chúc bạn ngày vui.
Điều chỉnh tiếp:
AL7=COUNTIFS($G$6:$AK$6,"<>CN",$G7:$AK7,">=8")+ROUND(SUMIFS($G7:$AK7,$G$6:$AK$6,"<>CN",$G7:$AK7,"<8")/8,1)chỉ Enter, rồi fill xuống.
Chúc bạn ngày vui.
Ghi nhận các trường hợp nghỉ nửa ngày bằng: Xx/2
Ví dụ: K/2; Ro/2; Cô/2; Ô/2; P/2.
Rồi điều chỉnh công thức trong các cột tương ứng:
Ví dụ: =COUNTIF($H7:$AM7,"Ro*")–COUNTIF($H7:$AM7,"Ro/2")/2
Tương tự cho các cột còn lại.
Chúc bạn ngày vui.
Thay đổi điều chỉnh các ngày nghỉ theo ký tự, có hoặc không thêm "/2".
AP7=SUM(COUNTIF($H7:$AM7,"Ro"&{"*","/2"})*{1,-0.5})
AQ7=SUM(COUNTIF($H7:$AM7,"K"&{"*","/2"})*{1,-0.5})
AR7=SUM(COUNTIF($H7:$AM7,"P"&{"*","/2"})*{1,-0.5})
AS7=SUM(COUNTIF($H7:$AM7,"Cô"&{"*","/2"})*{1,-0.5})Enter fill xuống
Ý bạn muốn nửa giờ còn lại tính vào công đi làm!?
AM7=COUNTIFS($H$6:$AL$6,"<>CN",$H7:$AL7,">=8")+ROUND(SUMIFS($H7:$AL7,$H$6:$AL$6,"<>CN",$H7:$AL7,"<8")/8,1)+COUNTIF($H7:$AL7,"*/2")/2Enter fill xuống
Bạn tham khảo file kèm.
Chúc bạn ngày vui
Chờ lâu không thấy trả lời cũng có lý do đó: bài này thuộc chuyên đề SUMPRODUCT, có quá nhiều bài viết trong đó nên không thu hút sự chú ý. Lần sau nhớ mở topic mới sẽ dễ thu hút hơn.
Dùng công thức này tại E2 rồi copy xuống:
Công thức mảng nên kết thúc bằng Ctrl-shift-enter thay vì enter nhé. Thấy có cặp móc nhọn {}bao quanh công thức là OK.
Mình cũng muốn tầm sư học đạo bạn về code.
Để bạn giaiphap ra giải pháp nhanh, mình chia sẽ thuật toán như sau:
LEN(A2)/2-COUNT(SEARCH($F$2:$F$18,A2)) ra 0,1,2,3,4 đếm số lượng KHÔNG tìm thấy (gọi là n). Cộng thêm 1 để dùng CHOOSE
Theo thứ tự:
Nếu n+1 =1,2,3,4,5 trả về mảng tương ứng {3,8,11,77,111},{0,0,0,3,77},{0,0,0,0,3},{0,0,0,0,0},{0,0,0,0,0}
Sau đó dùng =LOOKUP(D2,{"tb2","tb3","tb4","tb5","tb6"},CHOOSE(LEN(A2)/2-COUNT(SEARCH($F$2:$F$18,A2))+1,{3,8,11,77,111},{0,0,0,3,77},{0,0,0,0,3},{0,0,0,0,0},{0,0,0,0,0}))
VD: dòng đầu tiên "th3", n+1=1 (n=0 do tìm thấy 3 cặp trùng), CHOOSE trả về mảng {3,8,11,77,111}, LOOKUP "th3" trả về 3
Lỗi là chắc rồi với dữ liệu không bất nhất. Trong ví dụ chỉ là 1ký số+1 ký tự, (1a,2b) bây giờ lại ngược lại (a1,b2). Bạn đưa hết các trường hợp vào và post lại xem sao
– Tải file về, cho phép chạy macro
–
1696
=-PV(0.00628,2*12,3000000*0.22,,1)
Bạn dùng hàm này nha:
=-FV(0.00826,120,3000000*0.22,,1)
Mấy hàm này trong toán tài chánh cơ bản có hết, bạn nên chủ động tìm hiểu thì tốt hơn.
Chúc bạn vui vẻ :p
Bạn xóa cell 90,91 là hết lỗi.
2 cell đó không biết bạn thao tác copy dán từ đâu: nó đang là kiểu dữ liệu text chứ không phải Number: nên Sum không được.
( bạn thử dùng Istext() là thấy ak)
Bạn bôi đen A90:C91 =>delete =>enter
Tự động kết quả sẽ đúng
🙂
Bạn tìm hiểu thử nha
Chúc mọi người cuối tuần vui vẻ
Dữ liệu của bạn gửi tuy tương đồng kết cấu, nhưng phần chi tiết yêu cầu "cộng phân khoản" không phức tạp như file theo đường link mà tôi đề cập trên.
Do vậy, tôi thực hiện cho bạn cùng giải pháp nhưng đơn giản hơn chút, với một vài lưu ý như sau:
Công thức áp dụng:
Tính số thứ tự:
Fill xuống.
Tính Cộng Phân Vùng hoặc Tính Giá trị dòng Chi tiết:
Fill xuống.
Xem file đính kèm.
Chúc bạn học tập vui với GPE.
hihi ^o^
Trong công thức có dùng Iferror() nếu xài phiên bản trước 2010 thì sửa cùng lúc luôn.
Nếu như lỗi #Value như bài #603 đề cập do định dạng trong Control Panel về dấu phân cách hàng đơn vị và hàng ngàn…, thì bạn có thể thay đổi dấu "." trong công thức A4 thành dấu "," như sau:
Fill xuống là xong.
Tôi có gửi thêm cách điền ký tự "_" thay cho dấu "." hay "," thì không ngại bạn định dạng ".," trong Control Panel nữa, và công thức tổng ngắn gọn hơn để bạn tham khảo:
Fill xuống
Fill xuống. Bạn cũng có thể thay "_??" trong SUMIF(A5:$A$1000,A4&"_??",H5:$H$1000) thành "_*", nhưng tôi thì thích "_??" hơn.
Xem file đính kèm nhen! hihi ^o^
Bạn nên download file tại bài #604, sau đó muốn chèn thêm 4 cột trắng nữa vào giữa G và H, thì nên chọn nguyên cột H rồi nhấn Ctrl+"+" (dấu "+" của bàn phím số bên tay phải) nhấn thêm 3 lần như vậy nữa thì công thức không bị biến đổi.
Lúc đó công thức tại cột L sau khi đã chèn 4 cột thành như sau:
H4=IF(A4<>"",ROUND(IF(B4<>"",SUMIF(A5:$A$1000,A4&"_??",L5:$L$1000),PRODUCT(E4,F4,G4)*IF(OR(C4={"Vật liệu khác","Máy khác"}),SUMIF($A$3:A3,A4,$L$3:L3)%,1)),),"")
hihi ^o^
hihi ^o^
Tôi làm cho bạn hai cách, tùy ý bạn chọn:
Chỉ Enter, Fill xuống.
Xem file kèm. hihi ^o^
=SUMPRODUCT((MONTH($C$2:$C$720)=5)*($G$2:$G$720="PF10"))
Công thức như trên thì tính cả tháng 8/2017 và tháng 8/2018 (nếu có).
Có bao nhiêu điều kiện thì bạn vẫn dùng SumIfs() được mà.
Hổng biết có đúng theo ý bạn chưa ?
Số máy ngưng hoạt động
O7=ROUNDUP((M7-N7)/B7,0)
Bạn thử thêm cột O chứa kết quả tổng kết "Số máy thiếu vật tư":
Enter fill xuống.
Muốn theo dõi số lượng máy thiếu theo từng ngày trả kết quả tại các ô: E2, G2, I2, K2, bạn làm như sau:
Copy cho các ô còn lại.
Xem file kèm.
Thân.
Công thức:
SUMIF( OFFSET($F$5,,,,{1,3,5,7}), "SL" , OFFSET($F$7,ROW(1:7)-1,,,{1,3,5,7}) )
giống Sumif( Vùng so, Tiêu chí so, Vùng cộng) bình thường, nhưng nhờ Mảng {1,3,5,7} mà hàm Offset() sẽ nhảy qua cách cột, để làm nhiệm vụ cộng dồn số liệu, ví dụ:
[*]Sau đó nó đem Mảng này ra so với cột Tồn vật tư N để tạo ra Mảng hai chiều chứa giá trị thỏa hay không cột nào bị thiếu vật tư.
[*]Hàm Index(Mảng, ,'Thứ tự cột cần lấy') sẽ lấy ra Mảng cột, Vd: hình dạng cột F là {0;0;0;0;1;0;0} tức dòng 5 có giá trị 1 báo dòng đó bị thiếu vật tư. Tùy theo Thứ tự cột cần lấy, Ví dụ đứng tại F là 1, qua cột H là 2….., mà lấy ra Mảng điều kiện phù hợp (xem các cột 0,1 như hình trên).
[*]Cuối cùng, nó nhân 2 Mảng: 'Mảng điều kiện vừa tìm ra trên' x 'Cột số máy tương ứng', Ví dụ: cột F gồm hai Mảng: {0;0;0;0;1;0;0}*{6;2;5;4;5;2;6} –> kết quả cuối ={0;0;0;0;5;0;0}
[*]Sumproduct() làm nhiệm vụ cộng lại các số trong Mảng kết quả trên, tức = 5.
Chúc bạn học tập vui với anh em GPE.
Thân
Thử:
Enter, fill qua phải, rồi copy cả dòng xuống.
Nếu muốn linh động về niên độ tính thuế thì cập nhật tại ô nào đó Vd: A1=01/01/2017, và thực hiện công thức trong file kèm.
Thân.
p/s: Bạn chắc đang làm công tác quản lý thuế tại Chi Cục à!?
Tùy theo bạn thấy cái nào tiện cho công việc của bạn:
D4=SUMIFS(Data!$F$2:$F$13,Data!$B$2:$B$13,$B4,Data!$E$2:$E$13,"17Q"&COLUMN(A$1))
D4=SUMIFS(Data!$F$2:$F$13,Data!$B$2:$B$13,$B4,Data!$E$2:$E$13,TEXT($A$1,"yy")&"Q"&RIGHT(D$3))
Thân.
Thử:
Thân.
OFFSET(Sheet1!$D$1:$D$1000,,MATCH($B2,Sheet1!$E$1:$Z$1,))
Sẽ lấy cột size thích hợp với B2 (Vd: là size "XS"), thì Offset() sẽ trả về Vùng dữ liệu với địa chỉ là L1: L1000, để cung cấp Vùng cộng cho SUMIF(), tức có thể hiểu lúc sau cùng công thức có dạng là:
=SUMIF(Sheet1!$B$1:$B$1000,$A2,Sheet1!$L$1:$L$1000)
Vận hành của Offset() như sau: đứng tại mốc Sheet1!$D$1:$D$1000, sử dụng Macth() để tìm size "XS" có cột 'thứ mấy' trong Vùng Sheet1!$E$1:$Z$1, khi tìm được nó trả về stt cột, tức cách D1 bao nhiêu cột nhằm cung cấp thông số 'cột' cho Offset() để nhảy đến lấy cả vùng đó.
Cũng có thể thay thế bằng =OFFSET(Sheet1!$D$1,,MATCH($B2,Sheet1!$E$1:$Z$1,1000)) cũng cùng ý nghĩa.
Dùng hàm này là phù hợp với 'chủ đề' ở đây, tuy nhiên tôi muốn lưu ý với bạn dùng Sumproduct() là tập họp 1 mảng 2 chiều, do đó nó chiếm khá nhiều bộ nhớ. Nếu dữ liệu càng lớn, thì bạn sẽ thấy nó làm trì trệ tốc độ xử lý của máy tính.
Bạn thử hình dung như sau với công thức của bạn có khoảng 50 dòng x 14 cột = 700 ô dữ liệu, nếu dòng dữ liệu khoảng 1000 (như công thức Sumif() tôi đưa Vùng vào) thì nó vào khoảng 14.000 ô dữ liệu chiếm trong bộ nhớ.
Với Sumif() do dữ liệu được ghi nhận bởi Vùng (vùng so khớp, và vùng cộng đã ghi nhận vào ô hiện hữu trên bảng tính, ước lượng cao lắm cũng thao tác trong khoảng 2000 ô mà thôi), sau khi xác định rõ Vùng thì máy tính xử lý theo dòng dữ liệu nên nó ít chiếm bộ nhớ của máy tính, vì vậy tốc độ xử lý sẽ nhanh hơn.
Cũng là một dịp chia sẻ để biết cái lợi và hại của Sumproduct().
Thân.
Sheet "THĐH":
Enter, fill sang phải đến AE, rồi fill cả hàng xuống.
Thân
Nếu "Hàng trọng điểm" được phân loại là 1 và 2 (nói chung nhỏ hơn 10), thử:
1/ Theo dõi số khách hàng:
Kết thúc bằng Ctrl+Shift+Enter, rồi fill xuống.
2/ Theo dõi "Hàng trọng điểm":
Kết thúc bằng Ctrl+Shift+Enter, copy qua phải 1 cột, rồi fill xuống.
Xem file kèm.
Thân
Nếu tôi hiểu ý và không nhầm lẫn 😀 thì …
1. Tôi đổi tên sheet thành csdl và nguon.
2. Công thức cho B2
Kết thúc bằng Ctrl+Shift+Enter, và copy xuống dưới.
Công thức cho C2
Kết thúc bằng Ctrl+Shift+Enter, và copy sang phải tới cột D rồi xuống dưới.
1/ Doanh số lớn hơn 10: thì thêm 1 chút công thức vào phía sau công thức cũ, như dười đây:
=COUNT(1/(MATCH(Input!$B$2:$B$100,IF(Input!$A$2:$A$100=$A3,Input!$B$2:$B$100),)=ROW($1:$100))/(SUMIFS(Input!$E$2:$E$31,Input!$A$2:$A$31,Input!$A$2:$A$31,Input!$B$2:$B$31,Input!$B$2:$B$31)>10))
Sau này, nếu có thêm điều kiện gì khác thì cứ nối vào tương tự.
Hàm T() dành để hiện những giá trị chuỗi của vùng trong đối số của nó. Thường thì các mã SP không dùng để cộng trừ nhân chia, nên bạn nên định dạng là chuỗi (text), ngay cả nếu nó mang dạng số, Vd như số CMND chẳng hạn, bạn cũng nên định dạng cột đó về 'text'.
Riêng trong bài này, nếu Mã SP của bạn là dạng "số" thì bạn thay thế hàm T() bằng hàm N().
Xem file kèm.
Thân
Thử:
Enter.
Thân
Vậy, dùng thử:
Thân
Nếu lấy ký tự chuỗi thì:
Thay vì = MATCH(—LEFT($C$4:$J$4),{1,2,3},)
Điều chỉnh thành = MATCH( LEFT($C$4:$J$4) , {"1","2","3"}, )
Thân
Tôi lại hiểu có xét và không xét Vùng A9:A10 như trong file kèm chứ! 🙂
Thân
Dùng:
=SUMPRODUCT(–RIGHT(C2:C5,3)) Enter.
Thân
Dựa vào toán học:
Vận dụng điều này để "ép" a đang là dạng "chuỗi số" (Vd: chuỗi '12345) về thành số tính toán (12,345).
Có nhiều cách đưa "chuỗi số" về dạng số, gồm:
Thân
Bạn giải thích cách tính toán chi tiết thêm:
Thân
À! tôi hiểu ý bạn rồi, phải xem cả bảng thời gian "bắt đầu – kết thúc" và vì nó không sắp thứ tự theo ngày nên ban đầu tôi nhầm chút.
Thử công thức sau:
Enter, fill qua phải, rồi copy cả dòng xuống
Thân
Đã giải cho bạn ở trên rồi đó.
Thân
Đây, cho bạn "ngâm cứu" về "người đẹp" ấy. 🙂
Thân
bạn dùng thử: =sumifs(C1:C10,A1:A10,"X",B1:B10,"Y")
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
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
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ụ
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)
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:
Chỉ Enter, rồi fill qua phải.
Thân
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
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:
Do các yếu tố trên, nên công thức sẽ dài hơn:
Kết thúc bằng Ctrl+Shift+Enter.
Thân
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
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:
[*]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".
[*]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à:
Thân
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:
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
Tham khảo các công thức file đính kèm tại:
http://www.giaiphapexcel.com/diendan/threads/nh%E1%BB%9D-t%C3%ACm-l%E1%BB%97i-trong-c%C3%B4ng-th%E1%BB%A9c-%C4%91%E1%BA%BFm-lo%E1%BA%A1i-b%E1%BB%8F-%C4%91%E1%BA%BFm-tr%C3%B9ng.138038/#post-883771
1. Hàm Countifs, ví dụ:
COUNTIFS(A1:A10,"01",B1:B10,"<>"&"**")
2. Hàm Sumproduct, ví dụ:
SUMPRODUCT((A1:A10="01")*(B1:B10<>"**"))
=> ** hàm Sumproduct ko hiểu được như hàm Countifs: phương thức nào diễn giải ** trong hàm Sumproduct nhờ các bạn chỉ giúp mình ?
Thân
Ý mình muốn hỏi chỗ ** 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"
Mình có làm cái vd cụ thể như thế này
1736
=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.
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,))
Ctrl+Shift+Enter
Đếm loại trùng, có xét lọc dữ liệu bằng filter
Không đơn giản được :p
Công thức hay quá, rút gọn lại
Hình như công thức đó áp dụng với dạng số, trường hợp của bạn thì thử thế này xem sao.
Với nội dung bổ sung của bạn thì nên đăng bài mới, giúp cho những thành viên khác có nhu cầu tương tự nội dung thêm của bạn dễ tìm kiếm và tham khảo.