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

[Nguyễn Duy Tuâ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"



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!

 

[khuongan]

Thế còn hàm DOTPRODUCT và QUADPRODUCT cũng là một hàm tương tự như SUMPRODUCT nhưng có đối số linh hoạt hơn, hình như cũng là một Function nằm trong Add-in Solver, nhưng mình không hiểu rõ lắm hoạt động của hàm này, TuanVNUI và các bạn khác, ai biết về 2 hàm này thì Share cho mọi người hiểu thêm nhé

 

[hanhdhyd]

Hàm Sumproduct cho phép tính toán trên các mảng cùng chiều, cùng kích thước với nhau, còn hàm Dotproduct và Quadproduct cho phép tính toán trên nhiều vùng lựa chọn khác hướng nhau, khác kích thước, tuy vậy đây là hàm trong Add-in Solver Preminium, nên không phải MS-Excel nào cũng có sẵn, nên cũng không thông dụng, và không có điều kiện để tìm hiểu kỹ. Bạn nào biết thêm về các hàm này cùng trao đổi thêm.
Tôi cũng muốn biết thêm về ma trận và các hàm ma trận trong Excel, nhờ các bạn hướng dẫn giùm

 

[Hatmit]

Mình dùng hàm này nhưng không thể kết hợp được 2 điều kiện được. Bạn nào giúp mình với nhe
- Tôi muốn có một công thức thế này: nếu A1:A10 = "X" va B1:b10="Y" thì kq sẽ là : tổng từ c1:C10 thoả mãn 2 đk trên

 

[yeudoi]

Bạn hãy xem lại hướng dẫn ở trên rất kỹ, nếu vẫn chưa hiểu bạn hãy download file 173 công thức trong thư viện của diễn đàn.Bài toán của bạn đưa ra rất dễ (dùng sumproduct), chúc bạn thành công

 

[hoaloaken]

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

 

[ThuNghi]

Bạn đặt name như sau:
seni: =Sheet2!$A$3:$A$7
tại O4 sheet1
=INDEX(Seni,MATCH(DAYS360($N4,$M$2)/30/12,{0;1;3;5;7},1),0)
Copy xuống.

 

[hoaloaken]

Cám ơn bạn ThuNghi đã trả lời. Nhưng ý mình không phải thế
Có lẽ do mình viết câu hỏi không được rõ ràng cho lắm.
ý mình là lấy cột thâm niên mình đã làm ở sheet 1, để tổng hợp sang sheet 2.
Mình đã ghi chú lại trong file đính kèm.
ThuNghi giúp mình làm lại nhé
Cảm ơn bạn nhiều

 

[ThuNghi]

Đặt lại Seni2 là =Sheet1!$O$4:$O$10
Line: =Sheet1!$I$4:$I$10
Tại B3 (sheet2)
=SUMPRODUCT((MATCH(SeNi2,{0;1;3;5;7},1)=ROW()-2)*(Line=B$2))
Copy xuống và qua phải.

 

[kietbui]

Trong các ứng dụng về công thức mãng mình được biết thì dạng công thức mãng dạng sum nhiều điều kiện là mình tâm đắc nhất vì cú pháp đơn giản, khi kết hợp với những hàm khác thì nó rất linh hoạt , giải quyết rất nhiều tình huống trước đây mình xử lý bằng hàm thông thường rất vất vã, hình như nó tính hơi lâu nhưng kết quả rất ưng ý
Ví dụ
1/ sum nhiều if - thay thế một macro rút trích rồi tính tổng
vd:tính tiền thu được do bán mặt hàng là "sơn" thời gian từ 01/06/07 ( tungay) đến 30/06/07( denngay)
{sum(if(ngay=>tungay,1,0)*if(ngay<=denngay,1,0)*if(mat_hang="son",1,0)*tien)}
2/ Sumproduct nhiều if
vd:tính tổng diện tích các thửa ruộng ấp A, xã B, Huyện C
{sum(if(ap="a",1,0)*if(xa="b",1,0)*if(huyen="c",1,0)*dai*rong)}
3/ count nhiều if
vd: đếm số sv điểm trên trung bình là nam,lớp B, khoa C, Trường D
{sum(if(diem>=5,1,0)*if(gioitinh="nam",1,0)*if(lop="b",1,0)*if(khoa="c",1,0)*if(truong="d",1,0)}
4/max nhiều if
vd:tự động nhảy số hóa đơn thứ mấy xuất cho đơn vị A, chứ không phải là STT hóa đơn, stt hóa đơn =max(stt_hd)
{max(if(dv="a",1,0)*shd_dv)}
5/ Vlookup_if
vd: tìm trong vùng dữ liệu cột 1 mã máy bằng AAA, cột ngày di chuyển=gần nhất, xem nơi đến là nơi nào ( tìm giá trị cột noi_den )
- kết hợp hàm findtwoconditions trên giaiphapexcel và hàm max_if trên

 

[hoaloaken]

hỏi về công thức mảng

Chào bạn ThuNghinh
Bài trước bạn đã giúp mình lọc theo thâm niên công tác bằng công thức mảng. Cột thâm niên được tính theo công thức DAYS360. Mình đã lập được công thức rồi. Nhưng theo sự gợi ý của bạn anhtuan1606 công thức này là không chính xác, mình đã sửa lại cột năm riêng và cột tháng riêng.
Khi áp dụng công thức mảng của bạn của lọc thâm niên (lấy tiêu chí cột năm) thì không hiểu sao công thức của mình lại thành sai. Bạn thử check lại hộ mình nhé. Mặt khác trong quá trình làm việc khi xoá dòng hoặc insert một số dòng thêm vào danh sách thì bảng tổng hợp thường bị lỗi, làm mình lại phải sửa lại define name. Vậy có cách nào khắc phục được không bạn???
Cảm ơn bạn nhiều.

(Ah, bạn anhtuan cũng gợi ý mình dùng dùng VLOOKUP để lọc nhưng lại phải sắp sếp cột thâm niên theo thứ tự tăng dần hoặc giảm dần, như vậy mình thấy không tiện lắm. vì chủ yếu mình để danh sách theo tầng hoặc theo tổ. )

 

[anhtuan1066]

Công thức bạn làm có sử dụng hàm ROW theo tôi cũng sáng tạo đấy! Tuy nhiên tôi nghĩ ko nên sử dụng vì nó khá nguy hiểm nếu như 1 ngày nào đó bạn Insert row!
Bạn xem bài giải của tôi có đúng ý ko nha! Trong này chỉ sử dụng 2 Name và 1 hàm SUMPRODUCT đơn giản là dc mà
ANH TUẤN

 

[hoaloaken]

bạn làm vào file mình thì rất đúng, không hiểu sao khi mình mở file của mình ra làm thì nó lại hiện ở dòng B3 danh sách của cả tổ luôn. Chứ nó không phân loại ra cho mình. Mình đặt đi đặt lại name mà vẫn không được.
Đến lúc copy công thức từ file bạn làm ra, và sửa lại vùng của name thì lại được. Nhân tiện cho mình hỏi luôn: dùng công thức nào mình có thể lọc được những người sinh nhật trong tháng 12 nhỉ? Mình toàn phải insert thêm 1 cột phụ tháng sinh, sau đó mới lọc được.
CẢM ƠN BAN NHIEU

 

[anhtuan1066]

Khi bạn làm trên file tôi thì đúng, copy công thức sang file bạn lại sai... hi... hi... Cốt lổi vấn đề là ở chổ Format Custom trong các cell A3 đến A7... bạn nhìn thấy nó là text nhưng thực chất nó là number (chọn vào 1 trong các cell A3 đến A7 rồi nhìn lên thanh Formula sẽ rõ)
Tóm lại tình trạng hiện giờ trong sheet TH Tham nien là:
A2 = 0
A3 = 1
A4 = 3
A5 = 5
A6 = 7
A7 = 1000
.....
Còn vấn đề lọc ra người nào sinh trong tháng 12 tôi nghĩ bạn dùng Conditonal Formating là tiện nhất... Quét chọn vùng từ L5 đến cuối... vào conditional formating, Conditon1 chọn Formula Is và gỏ vào công thức =MONTH($L5)=12 rồi vào Format tô màu gì tùy bạn...
Mến
ANH TUẤN

 

[hoaloaken]

chào bạn anh tuấn.
Thì ra công thức là như vậy. Mình thì không có được hiểu biết cơ bản, bản chất của công thức nên chỉ học lỏm, học mót của mọi người thôi....... hiiiiiii nên nhiều lúc cũng thấy mình ngô ngố .......hiiiiiiiiiiiiiiiiii..
Nếu sử dụng conditional formating thì chỉ để nhìn phân biệt với các tháng khác. Nhưng mục đích của mình là để lọc ra được 1 list những người đó. Vì hàng tháng mình phải lập 1 list công nhân sinh nhật trong tháng để làm phiếu tặng quà mà. Bạn có cách nào khác không.
Thân........

 

[Mr Okebab]

Các bạn thắc mắc rằng dùng Sumproduct kết hợp với OR sẽ như thế nào nhỉ ?? Vì kết hợp với AND thì dễ rồi, còn với OR ??

Các bạn xem File VD nhé :

Thân!

 

[anhtuan1066]

Hình như chưa đúng thì phải... Sửa cell D8 thành 311 lý ra nó phải cho kết quả khác, thế mà nó vẫn y nguyên!
Hay ý Bắp muốn nói rằng chỉ cần trong cột có sự hiện diện của 111 hoặc 411 thì lấy hết cả cột? Vì tôi thay hết tất cả các cell thành 311 chỉ chừa C2 = 111 và D2 = 411 mà kết quả vẫn = 850,000,000 ???
Tôi lại đang nghĩ ý bạn phải giống như kết quả của tôi chứ:
=SUMPRODUCT((C2:C18<>311)*(D218<>311)*(E2:E18))
Xin cho biết ý tưởng!
ANH TUẤN

 

[Mr Okebab]

Hình như chưa đúng thì phải... Sửa cell D8 thành 311 lý ra nó phải cho kết quả khác, thế mà nó vẫn y nguyên!
Hay ý Bắp muốn nói rằng chỉ cần trong cột có sự hiện diện của 111 hoặc 411 thì lấy hết cả cột? Vì tôi thay hết tất cả các cell thành 311 chỉ chừa C2 = 111 và D2 = 411 mà kết quả vẫn = 850,000,000 ???
Tôi lại đang nghĩ ý bạn phải giống như kết quả của tôi chứ:
=SUMPRODUCT((C2:C18<>311)*(D218<>311)*(E2:E18))
Xin cho biết ý tưởng!
ANH TUẤN

Xin lỗi bác vì vội nên em tải nhầm File. Em đã sửa lại, bác xem lại nhé.
Thanks!!

Thân!

 

[Mr Okebab]

Sửa ở chổ nào đâu? Sao tôi down về thấy vẩn vậy mà... tức là khi tôi sửa hết các cell thành 311 chỉ chừa 2 cell trên cùng thì kết quả vẩn là 850,000,000 ???
ANH TUẤN

Oái, em đã cho file đúng rồi, sao vẫn là File cũ.
Tức thật.
Bác xem nhé :

Thân!

 

[anhtuan1066]

Vẫn sai! Theo như tôi hiểu thì ý bạn là: Nếu trong cột C hoặc D có giá trị 111 hoặc 411 thì lấy, chỉ khi nào cả 2 cột cùng khác cả 2 giá trị này mới bỏ qua...
Bạn thử thay D8=311 xem, đúng ra kết quả phải thay đổi, nhưng thật tế là u như kỹ...

 

[funnyzui]

Còn theo em hiểu thì: 2 cột C và D cùng có giá trị là 111 & 411 (hoặc 411 & 111) thì mới lấy, còn k đồng thời có 2 giá trị này thì k lấy.
Bác Tuấn xem lại coi có fải vậy ko?
Thân!

 

[Mr Okebab]

Còn theo em hiểu thì: 2 cột C và D cùng có giá trị là 111 & 411 (hoặc 411 & 111) thì mới lấy, còn k đồng thời có 2 giá trị này thì k lấy.
Bác Tuấn xem lại coi có fải vậy ko?
Thân!

Ở đây mình dùng kết hợp cả OR và AND :
- Hoặc (C=111 và D= 411)
- Hoặc (C=411 và D= 111)
Thì mới nhận giá trị

Theo dân Kế toán nói thì đây là tổng đối ứng giữa 111 và 411 .

Như vậy nhận xét của bạn là đúng đấy.

Thân!

 

[Mr Okebab]

Tôi muon khi nhap so < 5 thi mau xanh nguoc lai thi mau den
nho cac cao thu giup dum

Đây là cách dàng Format Condition. Bạn xem File VD và tham khảo theo Link bên dưới nhé :
Tham Khảo


Thân!

 

[anhtuan1066]

Tôi muon khi nhap so < 5 thi mau xanh nguoc lai thi mau den
nho cac cao thu giup dum

Có 1 cách cũng khá đơn giản đễ làm dc điều này... Đầu tiên bạn quét chọn những cell muốn gán điều kiện về màu, click phải chuột chọn Format Cell, trong tab Number bạn chọn mục Custom và gõ vào mục Type code sau: [Blue][<5];[Black][>=5]
OK rồi thử gõ vào cell ấy vài giá trị đễ kiểm tra...
Mến
ANH TUẤN

 

[muigele]

Sử dụng hàm sumproduct như thế nào?

Chị Handung oi em đã đọc bài mảng và công thức mảng của chị, thấy rất hay. Chị cho em hỏi trường hợp này dùng cthức Sumproduct có giải quyết được ko:
Em có sheet1 thể hiện chi tiết nhập xuất từng ngày trong tháng, gồm 3 cột:
cột 1: mã hàng
cột 2: diễn giải ( nhập và xuất)
cột 3: số lượng
Sheet2 tổng hợp lượng hàng xuất trong tháng theo mã hàng:
cột1: mã hàng (có sẵn)
cột 2: tổng hợp lượng hàng xuất theo mã hàng. Nếu mã hàng ở sheet1 thỏa mãn mã hàng sheet2 và là "xuất" thì trả về tổng SL hàng xuất theo từng mã. (mỗi mã chỉ 1 hàng).
Chị giúp em nhé!

 

[ThuNghi]

Chị Handung oi em đã đọc bài mảng và công thức mảng của chị, thấy rất hay. Chị cho em hỏi trường hợp này dùng cthức Sumproduct có giải quyết được ko:
Em có sheet1 thể hiện chi tiết nhập xuất từng ngày trong tháng, gồm 3 cột:
cột 1: mã hàng
cột 2: diễn giải ( nhập và xuất)
cột 3: số lượng
Sheet2 tổng hợp lượng hàng xuất trong tháng theo mã hàng:
cột1: mã hàng (có sẵn)
cột 2: tổng hợp lượng hàng xuất theo mã hàng. Nếu mã hàng ở sheet1 thỏa mãn mã hàng sheet2 và là "xuất" thì trả về tổng SL hàng xuất theo từng mã. (mỗi mã chỉ 1 hàng).
Chị giúp em nhé!

Bạn xem ví dụ và HD bên trong.

 

[stulipig]

Dùng Validation + với hàm lookup gì... để lấy dữ liệu từ 1 workbook khác

Chào các bạn. Mình đang làm 1 tool nhỏ để quản lý báo cáo tài chính của 1 số công ty.
Ý tưởng của Tool này như sau:
- Mình sử dụng Validation: trong đó bao gồm danh sách của nhiều công ty, khi mình chọn 1 trong số các công ty trên thì báo cáo tài chính (bảng cân đối kế toán) của công ty đó, từ 1 workbook khác sẽ được hiển thị trên workbook hiện tại (workbook có Validation)
- Mình đã thử kết hợp Validation với hàm Transpose, VLookup nhưng xem ra không khả thi lắm.
Mình mới sử dụng Excel nên không thông hiểu nhiều, mong các bạn giúp đở.

 

[Mr Okebab]

Chào các bạn. Mình đang làm 1 tool nhỏ để quản lý báo cáo tài chính của 1 số công ty.
Ý tưởng của Tool này như sau:
- Mình sử dụng Validation: trong đó bao gồm danh sách của nhiều công ty, khi mình chọn 1 trong số các công ty trên thì báo cáo tài chính (bảng cân đối kế toán) của công ty đó, từ 1 workbook khác sẽ được hiển thị trên workbook hiện tại (workbook có Validation)
- Mình đã thử kết hợp Validation với hàm Transpose, VLookup nhưng xem ra không khả thi lắm.
Mình mới sử dụng Excel nên không thông hiểu nhiều, mong các bạn giúp đở.

Điều này là . . . không thể (nếu theo suy nghĩ của mình)
Bạn làm tài chính thì thừa hiểu rằng Báo cáo Tài chính là một bảng tổng hợp ở một mức độ cao phụ thuộc vào rất nhiều chỉ tiêu. Vì vậy không thể dùng mấy hàm tham chiếu như ở trên mà làm ra được.
Có 3 cách (theo mình)

1. Tạo ra một cơ sở dữ liệu, trong Sheet DATA đó phải có thêm một cột là Company dùng để phân định Cty. Khi đó ở các báo cáo sẽ căn cứ là công ty nào để truy xuất dữ liệu công ty đó theo cột Company
2. Tạo một CSDL mà mỗi công ty là 1 Sheet, muốn truy xuất đến Cty nào thì sẽ lấy Data của Cty đó
3. Mỗi công ty là một chương trình riêng (File riêng), không liên quan gì đến nhau.

Nếu những điều trên làm bạn khó hiểu thì e rằng bạn . . chưa làm tài chính được.

Thân!

 

[stulipig]

Load dữ liệu từ 1 workbook khác!!!

Cảm ơn bạn đã giúp đỡ.
Hiện tại mình đang làm như sau:
- BCTC của công ty bao gồm 3 phần: Bản cân đối kế toán, Lưu chuyển tiền tệ, và kết quả hoạt động kinh doanh. Mỗi công ty là 1 file gồm 3 sheet với 3 phần BCTC như trên.
- BCĐKT, Lưu chuyển tiền tệ và báo cáo KQKD được là theo 1 form chuẩn (áp dụng cho tất cả các ngành nghề), có cấu trúc hoàn toàn giống nhau.
- Mình có 1 workbook điều khiển, khi nào cần load dữ liệu của công ty nào thì chỉ cần nhập tên công ty (được mã hóa) thì dữ liệu của công ty đó sẽ hiển thị, từ đó mình có thể xử lý dữ liệu.
Phương pháp thì mình nghĩ như vậy, nhưng do mới sử dụng Excel nên không được thông thạo lắm, mong bạn nghĩ cách giúp mình với.

 

[kylyku]

xin chào tất cả các bạn mình hiện là thanh viên mới củ diễn đàng. mình đang gặp vấn đề nan giai trong EXCEL 2003. vấn đề như thế này:
làm sao thống kê đơn hàng và sản phẩm của đơn hàng đó đủ hay chưa. mà trong khi có rất nhiều đơn hàng có sản phẩm giống nhau. LÀM SAO CHO EXCEL biết được sản phẩm đó là của đơn hàng đó thì mới cộng. MÌNH THẬT TÌNH CẦU MONG CÁC BẠN GIÚP. XIN CÁC BẠN VUI LÒNG GIÚP MÌNH. các bạn nào không hiểu thì mail cho mình kylyku@gmail.com

Bạn nên up file và nêu yêu cầu cụ thể: đơn hàng có mấy dòng, mã hàng tên hàng thế nào .... Chắc chưa ai hiểu bạn.

 

[be bong]

Bạn đặt name như sau:
seni: =Sheet2!$A$3:$A$7
tại O4 sheet1
=INDEX(Seni,MATCH(DAYS360($N4,$M$2)/30/12,{0;1;3;5;7},1),0)
Copy xuống.

ThuNghi oi,
Bạn có thể giải thích cho mình bít Hàm SENI là hàm gì ko? Có tác dụng như thế nào?Thanks

 

[handung107]

Đặt lại Seni2 là =Sheet1!$O$4:$O$10
Line: =Sheet1!$I$4:$I$10
Tại B3 (sheet2)
=SUMPRODUCT((MATCH(SeNi2,{0;1;3;5;7},1)=ROW()-2)*(Line=B$2))
Copy xuống và qua phải.

Như vậy, seni2 là dãy $O$4:$O$10 tại sheet1, rõ quá, sao bạn còn hỏi đó là hàm gì ?

Để đặt tên cho dãy, bạn vào menu Insert / Name / Define, bạn có thể tìm thêm trên diễn đàn các bài khác nói về cách đặt tên cho một dãy...

 

[DiepCocYTien]

Bài toán của mình là thế này:
Có 02 nhà cung cấp A, B, cung cấp nhiều mặt hàng a1, a2, a3 ..., bạn nào giúp mình tổng hợp trong tháng số liệu tổng của từng mặt hàng đối với từng nhà cung cấp được không (có thể trùng ý với ban kylyku)

 

[tklshb]

Bài toán của mình là thế này:
Có 02 nhà cung cấp A, B, cung cấp nhiều mặt hàng a1, a2, a3 ..., bạn nào giúp mình tổng hợp trong tháng số liệu tổng của từng mặt hàng đối với từng nhà cung cấp được không (có thể trùng ý với ban kylyku)

Bạn phải gửi file lên thì mọi người mới biết ý tưởng muốn trình bày file của bạn như thế nào để còn giúp đỡ chứ.

 

[connhangheo]

các bác cho em hỏi rút ngắn công thức này như thế nào ạ
=SUMPRODUCT((dv="xuong 1")*(cv="fo fong")*(hd="ouc"))+SUMPRODUCT((dv="xuong 1")*(cv="fo fong")*(hd="sc"))
thanks

 

[ThuNghi]

các bác cho em hỏi rút ngắn công thức này như thế nào ạ
=SUMPRODUCT((dv="xuong 1")*(cv="fo fong")*(hd="ouc"))+SUMPRODUCT((dv="xuong 1")*(cv="fo fong")*(hd="sc"))
thanks

=SUMPRODUCT((dv="xuong 1")*(cv="fo fong")*((hd="ouc")+(hd="sc")))

 

[buioanhvn]

Em chào anh chị,chúc anh chị 1 ngày tốt lành.
Em đang vướng ở 1 bài toán là tính tổng có điều kiện ràng buộc là thỏa mãn 1 khoảng thời gian nhất định.
Ví dụ thế này,
Sheet 1 có cột A là các ngày trong tháng 3/08, Cột B là doanh thu bán được theo ngày
Sheet 2 là pivot sheet 1 theo 2 chỉ tiêu ngày tháng và doanh thu tương ứng cột a và b.
Sheet 3 là chi tiết sheet 1 theo doanh thu của từng loại sản phẩm theo ngày.
Bây giờ, em muốn tính tổng số doanh thu từng loại sản phẩm ở sheet 3 bị ràng buộc theo ngày được chọn trong sheet 2
Em đã nghĩ đến công thức mảng và hàm sumproduct nhưng chưa biết xử lý việc để hàm logic <, > của ngày tháng như thế nào, đồng thời vì ngày được chọn trong sheet2 có thể thay đổi, muốn mặc nhiên công thức tự link vào mà không fải sửa đổi điều kiện nữa.
Từ hôm qua đến giờ em cứ quay đi quẩn lại với bài toán này mà vẫn chưa ra, anh chị giúp em với nhé
Thankyou & best regards.
Oanh

 

[ThangAcc]

Chào các bạn, tôi đang muốn hỏi về vấn đề điều kiện để cộng khác 1 ô trắng thì làm thế nào?

VD: Cột A: SL, Cột B: Thành Tiền, Cột C: Phân Loại 1, cột D: Phân Loại 2
G/S tôi đã sort và subtotal() theo cột C, bây giờ tôi muốn tính tổng giá trị theo cột D. Cột D có 10 loại khác nhau D1, D2,..., D10, trong đó có 2 loại chính là D1 và D2.

Vậy tôi sẽ tính các hàm
Sumproduct(B1:B10*(D110="D1"))
Sumproduct(B1:B10*(D110="D2"))
Sumproduct(B1:B10*(D110<>"D1")*(D110<>"D2")*(D110<>"(chỗ này là khoảng trắng)"))

Ở công thức thứ 3 máy sẽ cộng cả những giá trị đã subtotal ở cột C, bây giờ tôi muốn loại những giá trị này (cột D= khoảng trắng) thì làm thế nào? Xin chỉ giúp

 

[giap_engineer]

Em nghĩ anh nên dùng thêm hàm sumif vào thì hay hơn.

 

[thanguyenchien]

Cũng tùy vào nhu cầu sử dụng mỗi người mà dùng ham sumif hay Dsum hay sumprduct. Mỗi cái đều có điểm ưu và nhược riêng. VD, các bạn sử dụng ham dsum hay sumproduct thì phải trả giá cho việc chạy số liệu rất lâu mỗi khi có số liệu được cập nhập mới, sumif thì đơn giản nhưnng không đáp ứng được với nhiều điều kiện

 

[uthoiemve]

Hàm Sumproduct

Em chào anh chị,chúc anh chị 1 ngày tốt lành.
Em đang vướng ở 1 bài toán là tính tổng có điều kiện ràng buộc là thỏa mãn 1 khoảng thời gian nhất định.
Ví dụ thế này,
Sheet 1 có cột A là các ngày trong tháng 3/08, Cột B là doanh thu bán được theo ngày
Sheet 2 là pivot sheet 1 theo 2 chỉ tiêu ngày tháng và doanh thu tương ứng cột a và b.
Sheet 3 là chi tiết sheet 1 theo doanh thu của từng loại sản phẩm theo ngày.
Bây giờ, em muốn tính tổng số doanh thu từng loại sản phẩm ở sheet 3 bị ràng buộc theo ngày được chọn trong sheet 2
Em đã nghĩ đến công thức mảng và hàm sumproduct nhưng chưa biết xử lý việc để hàm logic <, > của ngày tháng như thế nào, đồng thời vì ngày được chọn trong sheet2 có thể thay đổi, muốn mặc nhiên công thức tự link vào mà không fải sửa đổi điều kiện nữa.
Từ hôm qua đến giờ em cứ quay đi quẩn lại với bài toán này mà vẫn chưa ra, anh chị giúp em với nhé
Thankyou & best regards.
Oanh

Đưa ví dụ (file excel) nên cho mọi người biết ý của bạn./.

 

[buttri]

Các hướng dẫn giúp về SUMPRODUCT

Tôi muốn lập công thức SUMPRODUCT sẳn để khi cập nhật data.DDH mới vào nó sẽ tự động ra kết quả.có kèm file
CÁM ƠN NHIỀU NHIỀU

 

[ndu96081631]

Tôi muốn lập công thức SUMPRODUCT sẳn để khi cập nhật data.DDH mới vào nó sẽ tự động ra kết quả.có kèm file
CÁM ƠN NHIỀU NHIỀU

SUM theo cái gì?
Theo Slnhan ?
Theo Tngay ?
Theo Slcon ?
Theo Gia ?
Hay ???

 

[buttri]

Chào bạn ndu96081631

SUM theo cái gì?
Theo Slnhan ?
Theo Tngay ?
Theo Slcon ?
Theo Gia ?
Hay ???

Cụ thể như vầy lấy cột "Slcon" nhân cột "Gia" theo diều kiện
thứ 1 là tính theo tháng
thứ 2 là tính theo LINE
thứ 3 là trên cột ORDER không tính những số oder có kí tự đầu là KD,D
Bạn xem bên sheet "tong tôi có ghi rõ yêu cầu
Mong bạn giúp đỡ.cám ơn nhiều

 

[cadafi]

Cụ thể như vầy lấy cột "Slcon" nhân cột "Gia" theo diều kiện
thứ 1 là tính theo tháng
thứ 2 là tính theo LINE
thứ 3 là trên cột ORDER không tính những số oder có kí tự đầu là KD,D
Bạn xem bên sheet "tong tôi có ghi rõ yêu cầu

Tôi có hai ý kiến muốn chia sẻ với bạn như sau:
1. Sử dụng Sumproduct trong trường hợp của bạn là một bất lợi:
a. Sheet báo cáo, bạn phải lọc ra dang sách GR và Line bằng tay (advance filter).
b. Với dữ liệu nhiều (theo như bạn mô tả trong sheet data.DDH), nếu dùng sumproduct nhiều điều kiện, máy sẽ chạy rất chậm (nếu không muốn nói là rất rất chậm).

2. Tôi sẽ cho bạn thấy công cụ Pivotalble mạnh mẽ như thế nào trong trường hợp xử lý báo cáo phân tích của bạn:
a. Tôi thiết kế thêm ba cột nữa cho sheet data.DDH của bạn, đó là [Thanhtien], [Reference] và [Period]

[Thanhtien]: đơn giản là lấy [Slcon] nhân với [Gia]
[Reference]: là cột tham chiếu điều kiện đơn hàng,

[Reference]=IF(OR(LEFT([ORDER],2)="KD",LEFT([ORDER],1)="D"),"KD-D","Others")

[Period] là kỳ báo cáo,

[Period]=CONCATENATE(RIGHT("00"&MONTH([Ngayxuat]),2),"/",RIGHT("0000"&YEAR([Ngayxuat]),4))

b. Đặt Name vùng dữ liệu Range("A:O")=Data
c. Dùng Pivotable. bạn xem file đính kèm.
d. Để hiểu vể Pivotable, bạn xem theo đường link này.
http://www.giaiphapexcel.com/forum/showthread.php?t=6216
Khi cập nhật dữ liệu vào data.DDH xong, chỉ cần qua sheet Pivotable click chuột phải vào vùng chứa Pivot và chọn Refresh Data là xong!

e. Với cơ sở dữ liệu thiết kế lại như tôi, bạn hoàn toàn có thể sử dụng sumproduct, sumif hoặc thậm chí SUM để giải quyết bài toán này! Tuy nhiên với thiển ý của tôi, dùng Pivotable trong trường hợp này là tối ưu nhất.

 

[buttri]

Chào bạn ca_dafi;77094

Tôi có hai ý kiến muốn chia sẻ với bạn như sau:
1. Sử dụng Sumproduct trong trường hợp của bạn là một bất lợi:
a. Sheet báo cáo, bạn phải lọc ra dang sách GR và Line bằng tay (advance filter).
b. Với dữ liệu nhiều (theo như bạn mô tả trong sheet data.DDH), nếu dùng sumproduct nhiều điều kiện, máy sẽ chạy rất chậm (nếu không muốn nói là rất rất chậm).

2. Tôi sẽ cho bạn thấy công cụ Pivotalble mạnh mẽ như thế nào trong trường hợp xử lý báo cáo phân tích của bạn:
a. Tôi thiết kế thêm ba cột nữa cho sheet data.DDH của bạn, đó là [Thanhtien], [Reference] và [Period]

b. Đặt Name vùng dữ liệu Range("A:O")=Data
c. Dùng Pivotable. bạn xem file đính kèm.
d. Để hiểu vể Pivotable, bạn xem theo đường link này.
http://www.giaiphapexcel.com/forum/showthread.php?t=6216
Khi cập nhật dữ liệu vào data.DDH xong, chỉ cần qua sheet Pivotable click chuột phải vào vùng chứa Pivot và chọn Refresh Data là xong!

e. Với cơ sở dữ liệu thiết kế lại như tôi, bạn hoàn toàn có thể sử dụng sumproduct, sumif hoặc thậm chí SUM để giải quyết bài toán này! Tuy nhiên với thiển ý của tôi, dùng Pivotable trong trường hợp này là tối ưu nhất.

Rất cám ơn đã hướng dẫn tôi sẽ tìm hiểu về Pivotable coi sao
Chúc bạn sức khỏe và thành công

 

[micarazavuska]

Sử dụng hàm SUMPRODUCT.

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"

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!

Chào anh TuanVNUNI! Em cũng đang học Ex, còn rất ngố.Em vừa đọc bài của anh viết nhưng chưa hiểu nhiều lắm. Khi em làm ví dụ anh đưa ra, sử dụng hàm SUMPRODUCT:
1.Em sử dụng bằng tay, tức là tự đánh lệnh hàm SUM, sau đó Ex đưa ra một danh sách các hàm bắt đầu bằng lệnh SUM, trong đó có SUMPRODUCT.Em chọn và thực hiện đúng như công thức của anh đưa ra SUMPRODUCT(A1:A3,B1:B3*(C1:C3="Cam"))
Thì kết quả là =140
2.Em ấn Shift+F3 để dò tìm hàm SUMPRODUCT thi hiện ra bảng có array 1,2,3 .Em kéo tại array 1=A1:A3 , array2=B1:B3, Còn array3 =C1:C3=C1 nhưng kết quả lại cho =0
Vậy, tại array3 phải kéo cells như thế nào cho đúng ạ?
Thanks!

 

[cadafi]

1.Em sử dụng bằng tay, tức là tự đánh lệnh hàm SUM, sau đó Ex đưa ra một danh sách các hàm bắt đầu bằng lệnh SUM, trong đó có SUMPRODUCT.Em chọn và thực hiện đúng như công thức của anh đưa ra SUMPRODUCT(A1:A3,B1:B3*(C1:C3="Cam"))
Thì kết quả là =140
2.Em ấn Shift+F3 để dò tìm hàm SUMPRODUCT thi hiện ra bảng có array 1,2,3 .Em kéo tại array 1=A1:A3 , array2=B1:B3, Còn array3 =C1:C3=C1 nhưng kết quả lại cho =0
Vậy, tại array3 phải kéo cells như thế nào cho đúng ạ?

Như vậy công thức của bạn là: =SUMPRODUCT(A1:A3,B1:B3,C1:C3=C1)
Khi đã ra được công thức như trên, không phải là do bạn kéo cells gì cả, mà bạn phải sửa lại công thức một chút:

=SUMPRODUCT(A1:A3,B1:B3[B]*[/B](C1:C3=C1))

Thay dấu "," bằng dấu "*".

 

[micarazavuska]

Cách sử dụng hàm Sumproduct

Như vậy công thức của bạn là: =SUMPRODUCT(A1:A3,B1:B3,C1:C3=C1)
Khi đã ra được công thức như trên, không phải là do bạn kéo cells gì cả, mà bạn phải sửa lại công thức một chút:

=SUMPRODUCT(A1:A3,B1:B3[B]*[/B](C1:C3=C1))

Thay dấu "," bằng dấu "*".

Cho iem hỏi lại.
Anh coi image em up nhé.
Thanks!

 

[cadafi]

Cho iem hỏi lại.
Anh coi image em up nhé.
Thanks!

Không cần Array3 bạn à, tại ô Array2 bạn gõ vào:

B1:B3*(C1:C3=C1)

Kéo chuột từ B1 xuống B3, xong gõ dấu "*" rồi "(", rồi kéo chuột từ C1 đến C3, rồi gõ "=", chỉ vào ô C1, rồi gõ ")"

[COLOR="Gray"]Còn không thì gõ hết vào cho nhanh.[/COLOR]

Còn nếu vẫn muốn tách bạch điều kiện vào array3 thì bạn gõ vào:

Array1= A1:A3
Array2= B1:B3
Array3= --(C1:C3=C1)

 

[micarazavuska]

Không cần Array3 bạn à, tại ô Array2 bạn gõ vào:

B1:B3*(C1:C3=C1)

Kéo chuột từ B1 xuống B3, xong gõ dấu "*" rồi "(", rồi kéo chuột từ C1 đến C3, rồi gõ "=", chỉ vào ô C1, rồi gõ ")"

[COLOR=Gray]Còn không thì gõ hết vào cho nhanh.[/COLOR]

Còn nếu vẫn muốn tách bạch điều kiện vào array3 thì bạn gõ vào:

Array1= A1:A3
Array2= B1:B3
Array3= --(C1:C3=C1)

Thanks anh nhé ! Em khoái code thứ 2 hơn!

 

[minhquan299]

View attachment bai17.rar

xin mọi người hứong dẫn mình làm câu số 9, cám ơn mọi người rất nhiều

 

[cadafi]

View attachment 11789

xin mọi người hứong dẫn mình làm câu số 9, cám ơn mọi người rất nhiều

Bạn xem file đính kèm nhé! Dùng SUMPRODUCT.

 

[fun.tiger]

file này có cả hàm sumproduct luôn

các pác chỉ giúp mình thực hiện hàm Vlook up để làm tồn đầu cuối nhé,mình cần gấp lắm,cảm ơn các pác trước nhé

 

[ThuNghi]

các pác chỉ giúp mình thực hiện hàm Vlook up để làm tồn đầu cuối nhé,mình cần gấp lắm,cảm ơn các pác trước nhé

Lấy dữ liệu đâu mà biết tồn đầu kỳ.

 

[Nancy Nguyen]

Xin các đại ca chỉ cho em tại sao trong công thức tính hàm SUMPỎDUCT lại có dấu (--..) đằng trước là gì vậy

Cám un các đại ca

 

[cadafi]

Xin các đại ca chỉ cho em tại sao trong công thức tính hàm SUMPỎDUCT lại có dấu (--..) đằng trước là gì vậy

Cám un các đại ca

Bạn xem bài giải thích tại đây, bài số #7
http://www.giaiphapexcel.com/forum/showthread.php?t=11165

 

[fun.tiger]

@ThuNghi

Lấy dữ liệu đâu mà biết tồn đầu kỳ.

đầu kỳ tháng này =cuối kỳ của tháng trước,giúp mình copy thêm một sheet xem như là báo cáo tháng trước rồi đặt công thức mình xem là minh hiểu hà,cảm ơn nhiều

 

[buivankiet]

Theo tôi nghĩ dấu -- trong hàm sumproduct là để chuyển giá trị logic true, false sang giá trị 1,0 để thực hiện các tích vô hướng

 

[hongnhung4206]

Help me! Tính tổng với 2 điều kiện.

Chào cả nhà! Xin các anh chị chỉ giúp em cách tính tổng với 2 điều kiện. Hiện em đang xài Office 2003 nên ko thể xài hàm SUMIFS đề tính tổng có 2 đk, các anh chị biết hàm nào tương tự hàm SUMIFS trong Office 2003 chi em với. Em ko biết xài VBA

 

[cadafi]

Chào cả nhà! Xin các anh chị chỉ giúp em cách tính tổng với 2 điều kiện. Hiện em đang xài Office 2003 nên ko thể xài hàm SUMIFS đề tính tổng có 2 đk, các anh chị biết hàm nào tương tự hàm SUMIFS trong Office 2003 chi em với. Em ko biết xài VBA

Chào bạn,
Bạn nên đọc kỹ các bài bên dưới một chút. Topic này đang nói về sum với nhiều điều kiện dùng hàm Sumproduct đấy thôi!

 

[Culonnhao]

Xin lỗi, có thể dùng hàm sumproduct để lấy riêng giá trị số trong một mảng vừa có số, vừa có chữ, vừa có các ký hiệu (#N/A; Null,#Value)...không?

Cám ơn nhiều.
Chúc mạnh khỏe

 

[cadafi]

Xin lỗi, có thể dùng hàm sumproduct để lấy riêng giá trị số trong một mảng vừa có số, vừa có chữ, vừa có các ký hiệu (#N/A; Null,#Value)...không?

Cám ơn nhiều.
Chúc mạnh khỏe

Vấn đề của bạn có thể dùng hàm sumproduct để làm được. Bạn hãy đưa file với dữ liệu của bạn lên xem nhé!

 

[ask1610]

không bít đây có phải là một hướng đi đúng hông nữa! (Cho "bài" test ý mà!). Mong anh em chỉ giáo giùm!

 

[hoangminhtien]

không bít đây có phải là một hướng đi đúng hông nữa! (Cho "bài" test ý mà!). Mong anh em chỉ giáo giùm!

Bạn xem file có được không nhé!

 

[l0ve0fevil]

Ai Giúp Em Với

Em thảo luận bài tập này mà ko bít làm sao
Ai pro cho em hỏi cách lam với.
Tìm sl tồn kho nhé ( sd công thức mảng )

 

[cadafi]

Em thảo luận bài tập này mà ko bít làm sao
Ai pro cho em hỏi cách lam với.
Tìm sl tồn kho nhé ( sd công thức mảng )

Theo File của bạn, về mặt công thức thì vẫn làm được, bạn xem file đính kèm.

Tuy nhiên, mình góp ý tí (nếu bạn còn đi học thì xin bỏ qua góp ý này): Bạn nên thiết kế lại data cho nó khoa học hơn! Dữ liệu của bạn may là còn ít, nếu phát sinh nhiều record thì file sẽ rất chậm!

Thêm vào đó, báo cáo tồn kho của bạn không có thiết kế để thể hiện tồn kho đầu kỳ, cho nên việc tính tồn kho hiện giờ chẳng qua là lấy tổng nhập trừ tổng xuất.

Tôi nghĩ bạn nên tham khảo một số file mẫu nhập xuất tồn kho tại đây!

 

[tuongth1]

Mấy cao thủ chỉ em cái này với em làm mãi mà không được
Em có một combobox (có giá trị là : Tiếng Việt, English) mỗi khi chọn Tiếng việt thị nội dung của sheet hiển thị Tiếng Việt còn nếu chọn Tiếng Anh thì nội dung sẽ chuyển sang Tiếng Anh
===>Vui lòng chỉ dùm em với

 

[ndu96081631]

Mấy cao thủ chỉ em cái này với em làm mãi mà không được
Em có một combobox (có giá trị là : Tiếng Việt, English) mỗi khi chọn Tiếng việt thị nội dung của sheet hiển thị Tiếng Việt còn nếu chọn Tiếng Anh thì nội dung sẽ chuyển sang Tiếng Anh
===>Vui lòng chỉ dùm em với

Excel là của bác Bill, bác ấy đâu có rành tiếng Việt đâu mà chuyển giúp bạn chứ
Nên điều bạn cần làm là tạo 1 danh mục gồm 2 cột: 1 cột tiếng Việt và 1 cột tiếng Anh với nghĩa tương ứng với cột tiếng Việt ---> Tiếp theo thì chỉ là... INDEX, CHOOSE hay 1 hàm tìm kiếm gì đó là ra thôi...

 

[ask1610]

Tính tồn kho

Em thảo luận bài tập này mà ko bít làm sao
Ai pro cho em hỏi cách lam với.
Tìm sl tồn kho nhé ( sd công thức mảng )

Với số liệu bạn đưa ra, thì có thể làm trên một sheet thôi mà, trên đó bạn tính cả nhập, xuất và tồn kho. Tất nhiên trường hợp này chỉ dùng để làm bài tập thui, thực tế tính tồn kho thì hơi khác một chút! Tham khảo và chỉ giáo nha! Thân ái!

 

[PIT]

Làm ơn chỉ giùm em công thức này nhé :
Nhân viên sẽ được thưởng 1,5% doanh số đạt được nếu đủ đkoanh số đạt được phải từ 85%-> 95,9 % và tỷ lệ đạt của NPP đó là 100% trở lên
Nhân viên sẽ được thưởng 2% doanh số đạt được nếu đủ 2 đkoanh số đạt được phải từ 95,9 % trở lên và tỷ lệ đạt của NPP đó là 100% trở lên

Giúp em nhé, em đang cần gấp ah

 

[cop_kh]

Làm ơn chỉ giùm em công thức này nhé :
Nhân viên sẽ được thưởng 1,5% doanh số đạt được nếu đủ đkoanh số đạt được phải từ 85%-> 95,9 % và tỷ lệ đạt của NPP đó là 100% trở lên
Nhân viên sẽ được thưởng 2% doanh số đạt được nếu đủ 2 đkoanh số đạt được phải từ 95,9 % trở lên và tỷ lệ đạt của NPP đó là 100% trở lên

Giúp em nhé, em đang cần gấp ah

Gởi giúp cái File lên đi bạn nhé.

 

[PIT]

file của em đây ah, các bác giúp em gấp với

 

[cop_kh]

file của em đây ah, các bác giúp em gấp với

o4=if(and(i4>95.9%;$n$6>100%);2%*h4;if(and(i4>85%;$n$6>100%);1.5%*h4;0))

 

[PIT]

Ủa sao em vào cthức như của bác mà không được, bác xem lại giúp em nhé .

 

[cop_kh]

Ủa sao em vào cthức như của bác mà không được, bác xem lại giúp em nhé .

Thôi gởi File lại cho chắc ăn nhé, có thể do các dấu của máy bạn khác máy mình.

 

[cadafi]

Ô O4 bạn gõ công thức:

O4 = H4*($N$6>100%)*CHOOSE(1+(I4>85%)+(I4>95.9%),0%,1.5%,2%)

Sau đó copy công thức xuống dưới!

 

[PIT]

Ô O4 bạn gõ công thức:

O4 = H4*($N$6>100%)*CHOOSE(1+(I4>85%)+(I4>95.9%),0%,1.5%,2%)

Sau đó copy công thức xuống dưới!

Bác ơi công thức của bác em vào nó báo lỗi không được, bác xem lại giúp em nhé

 

[PIT]

Thôi gởi File lại cho chắc ăn nhé, có thể do các dấu của máy bạn khác máy mình.

Công thức của bác cop_kh em đã làm được, cám ơn bác nhiều

 

[cadafi]

Bác ơi công thức của bác em vào nó báo lỗi không được, bác xem lại giúp em nhé

Báo lỗi là báo lỗi gì, bạn nói rõ chút được không!? Còn không thì bạn xem file đính kèm! Mình có thấy lỗi gì đâu!

 

[cop_kh]

Báo lỗi là báo lỗi gì, bạn nói rõ chút được không!? Còn không thì bạn xem file đính kèm! Mình có thấy lỗi gì đâu!

Chắc máy của PIT dùng các dấu khác nhau trong hàm, bạn ấy copy công thức trên GPE rồi dán vô luôn nên không chạy Bác ạ.

 

[PIT]

Chắc máy của PIT dùng các dấu khác nhau trong hàm, bạn ấy copy công thức trên GPE rồi dán vô luôn nên không chạy Bác ạ.

Bác ơi không phải em copy luôn đâu mà em tự đánh vào mà, em làm lại thử lần nữa lại thấy được.
hihi........các bác trên diễn đàm giỏi quá, xin đa tạ cảm ơn các bác, hy vọng vẫn được sự giúp đỡ từ các bác .

 

[suvival198]

em có bài này muốn hỏi,

muốn tính số ô bắt dầu = chữ com thì co phải dùng hàm sumproduct ko và viết câu lênh như thế nào

 

[cop_kh]

em có bài này muốn hỏi,

muốn tính số ô bắt dầu = chữ com thì co phải dùng hàm sumproduct ko và viết câu lênh như thế nào

Bạn có thể dùng 2 hàm thông dụng sau để giải quyết vấn đề:

1. =SUMPRODUCT(--(LEFT(A2:E8;3)="COM"))
2. =COUNTIF($A$2:$E$8;"COM*")

Ngoài ra còn nhiều nhiều cách khác nữa...

 

[quynhthuhn]

Nhờ các chuyên gia excel giải giúp mình 2 vấn đề trong file đính kèm với. Mình biết là nên sử dụng công thức nào nhưng mà vận dụng không được. Không ra kết quả.

 

[hoangminhtien]

Nhờ các chuyên gia excel giải giúp mình 2 vấn đề trong file đính kèm với. Mình biết là nên sử dụng công thức nào nhưng mà vận dụng không được. Không ra kết quả.

File của bạn password là gì vậy?

 

[quynhthuhn]

Sorry nhé. Quên mất lại để password. File cua minh pass là 12062008.

 

[tungnguyen_kt]

Nhờ các chuyên gia excel giải giúp mình 2 vấn đề trong file đính kèm với. Mình biết là nên sử dụng công thức nào nhưng mà vận dụng không được. Không ra kết quả.

Yêu cầu của bạn ở BÊN NÀY

 

[quynhthuhn]

Yêu cầu của bạn ở BÊN NÀY

Yêu cầu bên này của mình đã được sửa lại đầy đủ, có 2 vấn để mà mình loay hoay mãi không ra. Bạn mở file rồi giải giúp mình với. Mình đang làm việc ở nước ngoài nên chẳng biết hỏi ai. Mong các bạn giúp đỡ sớm giúp mình.

 

[Nguyen Thien Dan]

Cảm ơn TUANVNUNI nhiều nhiều, Nhưng mình chỉ muốn tính tổng số "cam"; "khong lai" thì làm thế nào?, mình không cần tính tiền đâu!!!!!!!!!!!!!!!
giúp mình với
Cám ơn trước nhé

 

[cop_kh]

Cảm ơn TUANVNUNI nhiều nhiều, Nhưng mình chỉ muốn tính tổng số "cam"; "khong lai" thì làm thế nào?, mình không cần tính tiền đâu!!!!!!!!!!!!!!!
giúp mình với
Cám ơn trước nhé

Bạn dùng hàm sau:
=SUMPRODUCT((C1:C3)="Cam")*(D13="Không"))

 

[thuanduc08]

Xin cảm ơn bạn rất nhiều về bài viết này. Nếu tính tổng số học sinh giỏi là nữ thì làm thế nào?

 

[QuocPhong]

...Nếu tính tổng số học sinh giỏi là nữ thì làm thế nào?

Tương tự như bài trên mà bạn.

Bạn dùng hàm sau:
=SUMPRODUCT((C1:C3)="Cam")*(D13="Không"))

 

[thuanduc08]

Tớ làm không được, kết quả =0

 

[QuocPhong]

Bạn xem file đính kèm.

 

[tubu16]

Bạn có thể dùng 2 hàm thông dụng sau để giải quyết vấn đề:

1. =SUMPRODUCT(--(LEFT(A2:E8;3)="COM"))
2. =COUNTIF($A$2:$E$8;"COM*")

Ngoài ra còn nhiều nhiều cách khác nữa...

ở đây tại sao lại có "*"vậy??
thanks!!

 

[tubu16]

cảm ơn bạn về bài viết!! đúng lúc tôi đang muốn tìm hiểu về hàm này>< nhưng mà đọc xong tôi vẫn thấy cứ lơ mơ, và tôi đã thử vd nhưng không ra kết quả như bài viết??!! không biết mình sai chỗ nào??!! mong được bạn chỉ giáo !!!
thanks!

 

[Nguyễn Duy Tuân]

cảm ơn bạn về bài viết!! đúng lúc tôi đang muốn tìm hiểu về hàm này>< nhưng mà đọc xong tôi vẫn thấy cứ lơ mơ, và tôi đã thử vd nhưng không ra kết quả như bài viết??!! không biết mình sai chỗ nào??!! mong được bạn chỉ giáo !!!
thanks!

Công thức bạn làm là đúng rồi. Kết quả bằng 0 vì giá trị của bạn không thỏa mãn. Trong ô C1, bạn đã gõ là "cam " (có dấu cách/Space), bạn hãy sửa lại là "cam" được.

 

[tubu16]

ok rồi bạn ạh!!!không ngờ chỉ một lỗi nhỏ như vậy mà cũng gây khó khăn,,, thanks bạn!!

 

[giang_nhg]

Bạn hãy xem lại hướng dẫn ở trên rất kỹ, nếu vẫn chưa hiểu bạn hãy download file 173 công thức trong thư viện của diễn đàn.Bài toán của bạn đưa ra rất dễ (dùng sumproduct), chúc bạn thành công

Mình tìm file này của bạn mãi mà không thấy. Bạn có thể viết lại công thức cho mình được ko? Mình thử cả sum(if...) và sumproduct mà không được