Các câu hỏi về hàm SUMIF

[anhtuan1066]

Không thể nói là sumif nhanh hơn Sumproduct được, nếu chỉ căn cứ vào cấu trúc hàm. Ở đây theo yêu cầu của Nguyễn Thị Thơm thì dùng Sumif và Sumproduct đều được, nhưng hàm Sumif sẽ giới hạn về điều kiện tính tổng còn hàm sumproduct thì có thể tính tổng theo rất nhiều điều kiện

Đương nhiên SUMIF phải nhanh hơn SUMPRODUCT rồi... Bạn có thể làm thí nghiệm với dử liệu cở 20.000 dòng, trích xuất 2 điều kiện theo NGAY và theo MÃ sẽ biết liền!
Còn việc SUMIF bị giới hạn điều kiện uh? Đồng ý, nhưng ta có thể dùng name động để phá bỏ giới hạn này, đưa 2 điều kiện về 1 điều kiện!
Loại bài toán này có rất nhiều trên diển đàn, bạn thử tham khảo tại đây:
http://www.giaiphapexcel.com/forum/showthread.php?t=9851&page=2
Trích xuất 2 điều kiện nhưng lại dùng SUMIF
Bài #11 nhé

 

[TrungChinhs]

B18=SUMIF($A$2:$A$11,A5,$F$2:$F$11) bạn phải =SUMIF($A$2:$A$11,A18,$F$2:$F$11)

Trong hàm SUMIF hình như địa chỉ ô cuối cùng của vùng cộng "không có ý nghĩa" vì
hình như hai công thức SUMIF($A$2:$A$11,A5,$F$2:$F$11) và SUMIF($A$2:$A$11,A5,$F$2) cho kết quả như nhau trong mọi tình huống ?

Chú ý: Vùng cần cộng không phải là một vùng ($F$2:$F$11) mà chỉ là một ô đầu tiên của vùng ($F$2).
Nếu hiểu theo định nghĩa vùng có thể là một ô hoặc một dãy ô thì tội gì phải chọn một dãy ô cho công thức dài dòng và nhiều khi cũng mệt ?

Nhờ ThuNghi hoặc bạn nào biết giải thích giùm. Thanks !

 

[cadafi]

Trong hàm SUMIF hình như địa chỉ ô cuối cùng của vùng cộng "không có ý nghĩa" vì
hình như hai công thức SUMIF($A$2:$A$11,A5,$F$2:$F$11) và SUMIF($A$2:$A$11,A5,$F$2) cho kết quả như nhau trong mọi tình huống ?

Chú ý: Vùng cần cộng không phải là một vùng ($F$2:$F$11) mà chỉ là một ô đầu tiên của vùng ($F$2).
Nếu hiểu theo định nghĩa vùng có thể là một ô hoặc một dãy ô thì tội gì phải chọn một dãy ô cho công thức dài dòng và nhiều khi cũng mệt ?

Nhờ ThuNghi hoặc bạn nào biết giải thích giùm. Thanks !

Bạn xem lại cú pháp của hàm SUMIF (vào phần Help của excel, hoặc vào phần Insert/Function) sẽ rõ. Hình như bạn ít chịu khó đọc phần Help thì phải:

SUMIF(Range,Criteria,Sum_Range)
Trong đó:
Range: is the range of cells you want evaluated (là vùng chứa các cell ta cần lọc/đặt điều kiện)

Criteria: is the condition or criteria in the form of a number, expression, or text, that define which cells will be added (là điều kiện/tiêu thức mang dạng thức kiểu số, một biểu thức, hoặc dạng text, mục đích để xác định cell nào sẽ được cộng vào)

Sum_Range: are the actual cells to sum. If omitted, the cells in range are used (là các cell thực tế được cộng, nếu bỏ qua, tất cả các cell trong Range sẽ mặc định được dùng).
Ví dụ: Range A1:A10 mang các giá trị {1,2,3,4,5,6,7,8,9,10}
Lúc này: SUMIF(A1:A10,2,A1:A10) = SUMIF(A1:A10,2)==> nếu bỏ qua Sum_Range, excel tự hiểu Sum_Range = Range

Giải thích như vậy được chưa bạn Trung Chinh ơi!

 

[BNTT]

...Giải thích như vậy được chưa bạn Trung Chinh ơi!

Chưa! Và người ta hỏi một đằng, lại đi trả lời một nẻo!

Tôi xin giải thích vấn đề này như sau:

Đúng như bạn Trung Chinh đã thắc mắc, chúng ta chỉ cần xác định ô đầu tiên của vùng tính tổng (sum_range), và Excel sẽ tự động xác định vùng tính tổng dựa theo vùng chứa các ô đặt điều kiện (range).

Ví dụ, vùng range của chúng ta là A1:B15, và nhập công thức như sau:

=SUMIF(A1:B15, "abc", C10)​

Thì Excel sẽ tự động hiểu rằng sum_range chính là C1024, là một dãy có cùng "kích thước" với A1:B15, mà bắt đầu bằng ô C10.

Nói cho rõ hơn, bởi vì A1:B15 là một dãy ô gồm có 15 hàng và 2 cột, do đó bắt đầu từ ô C10, Excel sẽ tự động tạo ra một dãy để tính tổng cũng có 15 hàng và 2 cột, đó chính là C1024.


Nếu Ca_dafi đã trích dẫn Help của Excel, thì phải trích dẫn cho hết, còn một đoạn sau nữa:

The sum_range argument does not have to be the same size and shape as the range argument. The actual cells that are added are determined by using theupper leftmost cell in the sum_range argument as the beginning cell, and then including cells that correspond in size and shape to the range argument.​

Sum_range không nhất thiết phải cùng kích thước với range. Vùng thực sự để tính tổng được xác định bằng ô đầu tiên phía trên bên trái của sum_range, và bao gồm thêm những ô tương ứng với kích thước của range.​

Nhưng cũng chính bởi điều này, mà chúng ta phải cẩn thận khi đặt sum_range. Ví dụ:

- Có một chuỗi những ký tự lập lại A, B, C trong dãy A1:A9 (9 ô).
- Và một dãy chứa các con số từ 1 đến 10 trong dãy B1:B10.​

Khỏi cần lập bảng tính, bạn cũng sẽ tự biết là A sẽ tương ứng với 1, 4, 7; B sẽ tương ứng với 2, 5, 8; và C tương ứng với 3, 6, 9... phải không.
Bây giờ, bạn dùng SUMIF để tìm tổng của những chữ C là bao nhiêu. Bình thường, có lẽ bạn dùng cái này:

=SUMIF(A1:A9, "C", B1:B9)​

Và bạn có kết quả là 18 (chính xác).

Có khi nào bạn nghĩ rằng, mắc gì phải đi tìm trong cả dãy B1:B10, tui biết chắc chắn là C bắt đầu từ ở hàng thứ 3, vậy tui bắt đầu tính tổng từ hàng thứ 3 của cột B là đủ rồi... Nghe cũng có lý! Và bạn dùng công thức:

=SUMIF(A1:A9, "C", B3:B9)​

Theo bạn thì kết quả mà bạn nhận được là số mấy? Có phải là 18 không?

 

[thaibinh_excel]

Chưa! Và người ta hỏi một đằng, lại đi trả lời một nẻo!

Tôi xin giải thích vấn đề này như sau:

Đúng như bạn Trung Chinh đã thắc mắc, chúng ta chỉ cần xác định ô đầu tiên của vùng tính tổng (sum_range), và Excel sẽ tự động xác định vùng tính tổng dựa theo vùng chứa các ô đặt điều kiện (range).

Ví dụ, vùng range của chúng ta là A1:B15, và nhập công thức như sau:

=SUMIF(A1:B15, "abc", C10)​

Thì Excel sẽ tự động hiểu rằng sum_range chính là C1024, là một dãy có cùng "kích thước" với A1:B15, mà bắt đầu bằng ô C10.

Nói cho rõ hơn, bởi vì A1:B15 là một dãy ô gồm có 15 hàng và 2 cột, do đó bắt đầu từ ô C10, Excel sẽ tự động tạo ra một dãy để tính tổng cũng có 15 hàng và 2 cột, đó chính là C1024.


Nếu Ca_dafi đã trích dẫn Help của Excel, thì phải trích dẫn cho hết, còn một đoạn sau nữa:

Sum_range không nhất thiết phải cùng kích thước với range. Vùng thực sự để tính tổng được xác định bằng ô đầu tiên phía trên bên trái của sum_range, và bao gồm thêm những ô tương ứng với kích thước của range.​

Nhưng cũng chính bởi điều này, mà chúng ta phải cẩn thận khi đặt sum_range. Ví dụ:

- Có một chuỗi những ký tự lập lại A, B, C trong dãy A1:A9 (9 ô).
- Và một dãy chứa các con số từ 1 đến 10 trong dãy B1:B10.​

Khỏi cần lập bảng tính, bạn cũng sẽ tự biết là A sẽ tương ứng với 1, 4, 7; B sẽ tương ứng với 2, 5, 8; và C tương ứng với 3, 6, 9... phải không.
Bây giờ, bạn dùng SUMIF để tìm tổng của những chữ C là bao nhiêu. Bình thường, có lẽ bạn dùng cái này:

=SUMIF(A1:A9, "C", B1:B9)​

Và bạn có kết quả là 18 (chính xác).

Có khi nào bạn nghĩ rằng, mắc gì phải đi tìm trong cả dãy B1:B10, tui biết chắc chắn là C bắt đầu từ ở hàng thứ 3, vậy tui bắt đầu tính tổng từ hàng thứ 3 của cột B là đủ rồi... Nghe cũng có lý! Và bạn dùng công thức:

=SUMIF(A1:A9, "C", B3:B9)​

Theo bạn thì kết quả mà bạn nhận được là số mấy? Có phải là 18 không?

Như vậy theo trích dẫn ở trên, trong trường hợp cộng 1 vùng có pham vy bằng pham vy chứa đối tượng tham chiếu, chúng ta chỉ cần cố định dòng đầu, cột đầu của vùng cần cộng là được rồi, không cần dòng cuối kết thúc nữa nhỉ? . Công thức gọn hơn chút xíu

 

[TrungChinhs]

Chưa! Và người ta hỏi một đằng, lại đi trả lời một nẻo!...

Có khi nào bạn nghĩ rằng, mắc gì phải đi tìm trong cả dãy B1:B10, tui biết chắc chắn là C bắt đầu từ ở hàng thứ 3, vậy tui bắt đầu tính tổng từ hàng thứ 3 của cột B là đủ rồi... Nghe cũng có lý! Và bạn dùng công thức:

=SUMIF(A1:A9, "C", B3:B9)​

Theo bạn thì kết quả mà bạn nhận được là số mấy? Có phải là 18 không?

___Cảm ơn 2 bạn Ca_dafi và BNTT!
___Việc xem cú pháp của hàm trong phần Help của excel mình đã biết (do bạn Boong mách bảo) nhưng mình chỉ xem mỗi mục Example (giống như là ví dụ) và chỉ xem số thôi, phần chữ thì bỏ qua "không thèm xem" (có biết chữ Anh đâu mà xem) hì hì... nên không đúng như bạn nói, mình có xem đấy chứ tuy không nhiều nhưng chả hiểu họ viết gì ? do đó phần trích dẫn và dịch của các bạn rất bổ ích và lý thú.
___Còn phần giải thích của bạn BNTT quả là thấu đáo (tâm phục, khẩu phục). Tuy nhiên có trường hợp nào bắt buộc phải =SUMIF(A1:A9, "C", B3:B9) mà không thể =SUMIF(A3:A9, "C", B3) không nhỉ ???
___Nhiều người có thói quen dùng chuột để xác định vùng (có thể do không biết hoặc ít sử dụng các cách khác) nên khi bảng tính dài quá trang màn hình thường phải kéo lên, kéo xuống cho vừa khít vùng định chọn, khi đến phần không có dữ liệu "chuột chạy nhanh như bị mèo đuổi"... mất thời gian và mỏi vai, mỏi gáy ghê...

 

[trung1984]

Có thể kết hợp hàm sumif và countif, được không?

mình có 1 bảng danh sách phòng của 1 hotel và ngày trong tuần (trong 1 tháng).

như bảng sau (chỉ tham khảo không phải toàn bộ trong tháng): với x là phòng đã có người dùng

Room ------------101 ---- 102 ---- 103 ---- 201 ---- 202 ---- 303
tuần 1-----Thứ 2--X-----------------X--------X----------------X
-----------thứ 3--X--------X---------------------------X
-----------thứ 4-----------X-----------------X
tuần 2-----thứ 2--------------------X--------------------------X
-----------thứ 3-----------------------------X---------X
-----------thứ 4-----------X

Làm sao để tìm được là trong ngày thứ 2, thứ 3, thứ 4, mỗi ngày có nhiêu người đặt phòng trong vòng 1 tháng đó.

Nếu dùng hàm countif thì phải làm tay là Trích:
countif( array của row thứ 2 tuần 1, "thứ 2") + countif (array thứ 2 tuần 2, "thứ 2")
Làm vậy thì thủ công quá.
Còn dùng hàm sumif thì chỉ lọc ra được thứ 2 và nó chỉ sum lại những giá trị của phòng 101 thôi mà kô sum lại giá trị của các phòng khác. Đặt biệt là khi x là letter thì nó cũng bó tay.

=================
không biết post vậy có sai quy định không??
có cần phải up cái bài excel mẫu lên luôn không?

 

[ptm0412]

Thứ hai =SUMPRODUCT(($B$2:$B$7=B2)*($C$2:$H$7="X"))
Với đk dữ liệu y chang câu hỏi bài 1 và bắt đầu từ A1
Fill xuống cho thứ ba, thứ tư.

 

[boyxin]

mình có 1 bảng danh sách phòng của 1 hotel và ngày trong tuần (trong 1 tháng).

như bảng sau (chỉ tham khảo không phải toàn bộ trong tháng): với x là phòng đã có người dùng

Room ------------101 ---- 102 ---- 103 ---- 201 ---- 202 ---- 303
tuần 1-----Thứ 2--X-----------------X--------X----------------X
-----------thứ 3--X--------X---------------------------X
-----------thứ 4-----------X-----------------X
tuần 2-----thứ 2--------------------X--------------------------X
-----------thứ 3-----------------------------X---------X
-----------thứ 4-----------X

Làm sao để tìm được là trong ngày thứ 2, thứ 3, thứ 4, mỗi ngày có nhiêu người đặt phòng trong vòng 1 tháng đó.

Nếu dùng hàm countif thì phải làm tay là Trích:
countif( array của row thứ 2 tuần 1, "thứ 2") + countif (array thứ 2 tuần 2, "thứ 2")
Làm vậy thì thủ công quá.
Còn dùng hàm sumif thì chỉ lọc ra được thứ 2 và nó chỉ sum lại những giá trị của phòng 101 thôi mà kô sum lại giá trị của các phòng khác. Đặt biệt là khi x là letter thì nó cũng bó tay.

=================
không biết post vậy có sai quy định không??
có cần phải up cái bài excel mẫu lên luôn không?

THẾ NÀY CÓ ĐƯỢC KHÔNG?​

 

[trung1984]

=SUMPRODUCT(($B$2:$B$7=B2)*($C$2:$H$7="X"))
hàm này kô làm đươc khi khác sheet. dù đã quotation chính xác từ sheet khác
cac nào để giúp khác sheet kô

 

[ndu96081631]

=SUMPRODUCT(($B$2:$B$7=B2)*($C$2:$H$7="X"))
hàm này kô làm đươc khi khác sheet. dù đã quotation chính xác từ sheet khác
cac nào để giúp khác sheet kô

Khác sheet thì thêm tên sheet vào... Trời à!
Ví du:
=SUMPRODUCT((Sheet1!$B$2:$B$7=B2)*(Sheet1!$C$2:$H$7="X"))

 

[ptm0412]

hàm này kô làm đươc khi khác sheet. dù đã quotation chính xác từ sheet khác

Báo giá chính xác mà không được thì đúng rồi, phải định vị chính xác như ndu96... thì mới được.

Trời à!

Câu này nghe quen quen! Cám ơn Đức Huy!

 

[ptm0412]

Được! Được! Được! Phải có file mới tin sao đây? Trời ạ!
Mình thì kiểm chứng trước khi trả lời, mà người nghe chưa kiểm chứng đã đánh giá. Chắc phải kêu trời 3 tiếng nữa quá.
Mượn file của boyxin nhé, thanks.

 

[trung1984]

vẫn không thể được. Hình như do bảng đã protect nên không làm được.
Đã test thử trên khác sheet thì không ra, nhưng khi copy y chang cái formular đó qua cùng sheet với table thì nó lại đếm được

vô xem nó báo lỗi gì thì ra đúng là do cell đã lock nên khi dùng hàm

=SUMPRODUCT((Sheet1!$B$2:$B$31=A1)*(Sheet1!$C$2:$K$31="X"))

phải thay chữ A1 thành Chính xác điều kiện thì nó mới đếm được (ví dụ "thứ 2").

Vậy cho hỏi có thể dùng sumif và countif trong trường hợp này không??
Vì yêu cầu của người ta là dùng 2 cái quỷ này. Mà lại bảo là không được countif nhiều cái rồi cộng lại mới đau.

 

[boyxin]

Chú ý khi dùng SUMPRODUCT

SUMPRODUCT(--(...),--(...)) so với SUMPRODUCT((...)*(...)) nhiều khi cho kết quả khác nhau

VD: với file

TheodoiPtm.xls (30.0 KB)

=SUMPRODUCT((Sheet1!$B$2:$B$32=A1)*(Sheet1!$C$2:$K$32="x")) thì cho kết quả
=SUMPRODUCT(--(Sheet1!$B$2:$B$32=A1),--(Sheet1!$C$2:$K$32="x")) thì báo lỗi #VALUE!

 

[BNTT]

Hồi trước thì không hiểu, nhưng sau khi Ca_Dafi giải thích cách dùng mấy cái -- trong SUMPRODUCT, thì tôi hiểu rồi, nhưng... tôi vẫn không dùng! Mà một trong những lý do là cái mà Boyxin vừa nói đến.
Tôi thì cứ cách truyền thống mà làm, bỏ hết mấy cái điều kiện vào trong ngoặc đơn, nối nó lại bằng dấu *, chẳng bao giờ gặp trục trặc gì...

 

[ptm0412]

Đây là trường hợp khá đặc biệt vì 2 mảng không cùng kích thước: mảng thứ nhất 1 chiều, mảng thứ hai 2 chiều.
Còn tại sao công thức 1 (dấu *) ra kết quả, mà công thức 2 (dấu phẩy) bị lỗi thì không biết.
Công thức 1 hiểu là nhân 2 mảng, công thức 2 theo tính chất hàm Sumproduct cũng là nhân 2 mảng.
Nhờ các cao thủ giải thích hộ.

 

[trung1984]

bài toán Hotel, sumif + countif hay sumproduct?

Trong loạt bài trả lời về topic countif và sumif, mọi người có đề cập dùng sumproduct!!
Đúng là nó có thể đếm được số lần trong từng thứ của tất cả các phòng được dùng trong 1 tháng.
Nhưng vấn đề nảy sinh là các phòng có giá khác nhau nên không biết sao để tính doanh thu trong từng ngày của 1 tháng.

Mẫu excel đính kèm do ptm làm từ file của boyxin. tui đã thêm 1 table giá phòng vào.

Giờ nhờ bà con vọc thử xem tính doanh thu từng phòng trong tháng, theo từng lầu, và theo từng ngày trong tuần.

Người ta đề nghị dùng hàm countif và sumif để làm cái này. Nhưng tui vẫn không biết làm sao để dùng sumif cho vụ này. Còn nếu dùng countif thì phải countif chục lần rồi cộng chúng nó lại. làm vậy thì thủ công quá.


Sumproduct thì tuyệt quá trong việc đếm nhưng mà chả biết sao cho vụ tính doanh thu.

 

[ditimdl]

Theo mình sumif không thể đáp ứng được, sumif không thể đáp ứng 2 điều kiện, không thể vận dụng offset vào được nên đành dùng sumproduct thôi. sumproduct không những đếm mà còn tính tổng nữa bạn à.
Phần cuối cùng vẫn phải sumproduct từng phòng ứng với từng thứ và hlookup từng phòng rồi + lại thôi.

 

[ptm0412]

- Tiền thu từng phòng trong tháng: countif() x đơn giá phòng
- Tiền thu từng tầng trong tháng: tổng các phòng trong tầng (Sum đơn giản)
- Tiền thu từng ngày trong tuần: Sumproduct như cũ nhân thêm dãy đơn giá (trong ngoặc của Sumproduct => 3 thành phần nhân với nhau)
- Tỷ lệ phần trăm doanh thu (3 dòng): Đừng nói là không biết làm. (Thực tế đã làm 1 dòng và sai)
- Ditimdl làm 2 dòng % cũng sai. Tổng các % doanh thu phải = 100% = 1 mới đúng.