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

ndu96081631 · 13/8/09

huemtd đã viết:
Kính gửi các anh chị,
Emcó một bài toán trong một dãy số có số dương và số âm, nhưng em chỉ cần tính tổng âm, còn tổng dương không tính,em đã thử làm nhưng chưa làm được mong chỉ dẫn giúp.

Xin cảm ơn

Cách tính mà bạn đánh dấu "đúng" quả thật lạ! Bạn nói rằng:

Làm sao để tổng của H8:I8 sẽ trừ đi tổng của C8:G8, với điều kiện là số âm (-) thì cộng vào thành tổng âm, và số dương thì không cộng vào, coi như không tính số dương

Vậy xem lại dòng 8, từ C8 đến G8 ta có 2 số âm: -3 và -5644 ---> Nếu tôi cộng bằng tay thì sẽ là:
=SUM(H8:I8) - (-3) -(-5644) = 15953 +3+5644=21600
Hoặc:
=SUM(H8:I8) -3 -5644 = 15953 -3 -5644 = 10306
Cách nào cũng không ra được kết quả = 9540 như bạn đã tính cả
Không hiểu
--------
Cách tính của bạn tại cell L8 có vẽ giống như công thức này:

PHP:

=SUM(H8:I8)-SUM(C8:G8)-SUMIF(C8:G8,">0")

Hơi lạ!

huemtd · 13/8/09

Chắc em diễn giải khó hiểu nên gây cho anh hiểu nhầm. Thực ra trong dòng từ C8:G8 thì cứ là số dương thì nó không tính vào tổng, mà nó chỉ cộng các số âm lại với nhau thôi. Sau đó em lấy tổng của H8:I8 từ đi cái tổng đó. Từ công thức của anh em đã làm được rồi ạ. Cảm ơn Anh nhiều.

thanh_tks · 13/8/09

huemtd đã viết:
Kính gửi các anh chị,
Emcó một bài toán trong một dãy số có số dương và số âm, nhưng em chỉ cần tính tổng âm, còn tổng dương không tính,em đã thử làm nhưng chưa làm được mong chỉ dẫn giúp.

Xin cảm ơn

Theo mình hiểu là như thế này:
File đính kèm

ChanhTQ@ · 15/8/09

Trong excel đã có HÀM SUMIF(), COUNTIF(), sao chưa có MINIF() hay AVERAGEIF()?

Bạn nào viết gùm mình các hàm tương tự như vậy giúp nha, Xin cảm ơn trước!

Dữ liệu mẩu:

|A|B|C|D|E|
1| Ngày | HTen | DGia | SoLg | TTien
2|7/1/09|Thơm|2.5|70|175
3|7/1/09|Hồng|3|65|195
4|7/1/09|Nga|3.5|60|210
5|7/1/09|An|4|55|220
6|7/1/09|Hồng|4.5|50|225
7|7/1/09|Thơm|5|45|225
8|7/2/09|Hồng|5.5|40|220
9|7/2/09|Nga|6|35|195
10|7/2/09|An|6.5|30|195
11|7/2/09|Hồng|7|25|175
12|7/2/09|Thơm|7.5|20|15 0
13|7/2/09|Thơm|8|15|120
14|7/2/09|Nhung|8.5|10|85
15|7/2/09|Hồng|9|5|45
16|7/2/09|Nga|9.5|10|95
17|7/2/09|An|10|1|10
||.|||

Các bác viết giúp hàm trung bình cô Thơm mua số lượng hàng bao nhiêu;
Ngày 2/7 cô này mua với đơn giá thấp nhất là bao nhiêu?

Thân ái!

cadafi · 15/8/09

Tham khảo hàm mảng bên dưới nhé!
PS: Với yêu cầu của bài toán này không thể so sánh với SUMIF hay COUNTIF được, rất khập khiễn, bởi SUMIF và COUNTIF thuần túy thì chỉ dùng với 1 điều kiện (còn trong trường hợp khác, nghĩa là không thuần túy thì vẫn phải chế biến thêm).

| A
| B
| C
| D
| E
|
1
| Tên:
| Thơm
| SLg TB
| {=AVERAGE(IF(($A$4:$A$19=$B$2)*($B$4:$B$19=$B$1),($D$4:$D$19),""))}
||
2
| Ngày: |

02/07/2009

| DG thấp nhất |

{=MIN(IF(($A$4:$A$19=$B$2)*($B$4:$B$19=$B$1),($C$4:$C$19),""))}

||
3
| Ngày | HTen | DGia | SoLg | TTien |
4
|

01/07/2009

|Thơm|

2.5

|

70

|

175

|
5
|

01/07/2009

|Hồng|

3

|

65

|

195

|
6
|

01/07/2009

|Nga|

3.5

|

60

|

210

|
7
|

01/07/2009

|An|

4

|

55

|

220

|
8
|

01/07/2009

|Hồng|

4.5

|

50

|

225

|
9
|

01/07/2009

|Thơm|

5

|

45

|

225

|
10
|

02/07/2009

|Hồng|

5.5

|

40

|

220

|
11
|

02/07/2009

|Nga|

6

|

35

|

195

|
12
|

02/07/2009

|An|

6.5

|

30

|

195

|
13
|

02/07/2009

|Hồng|

7

|

25

|

175

|
14
|

02/07/2009

|Thơm|

7.5

|

20

|15 0|
15
|

02/07/2009

|Thơm|

8

|

15

|

120

|
16
|

02/07/2009

|Nhung|

8.5

|

10

|

85

|
17
|

02/07/2009

|Hồng|

9

|

5

|

45

|
18
|

02/07/2009

|Nga|

9.5

|

10

|

95

|
19
|

02/07/2009

|An|

10

|

1

|

10

|

MinhCong · 15/8/09

Nếu thêm cột như Bạn thì sao không dung hàm Dmin thay cho hàm min(if..) cho gọn?

ndu96081631 · 15/8/09

ca_dafi đã viết:
Tham khảo hàm mảng bên dưới nhé!
PS: Với yêu cầu của bài toán này không thể so sánh với SUMIF hay COUNTIF được, rất khập khiễn, bởi SUMIF và COUNTIF thuần túy thì chỉ dùng với 1 điều kiện (còn trong trường hợp khác, nghĩa là không thuần túy thì vẫn phải chế biến thêm).

------------------

MinhCong đã viết:
Nếu thêm cột như Bạn thì sao không dung hàm Dmin thay cho hàm min(if..) cho gọn?

Mình nghĩ sư phụ đưa bài vào box này là muốn đề cập đến VBA, tức hàm tự tạo chứ không phải muốn nói đến các giải phải công thức thường hay mãng

BNTT · 15/8/09

Hàm AVERAGEIF có sẵn mà? Thậm chí là AVERAGEIFS (tính trung bình theo nhiều điều kiện)...

Cái bảng tính đó, nếu muốn tính trung bình cô Thơm mua bao nhiêu hàng, dùng AVERAGEIF (cách dùng y chang SUMIF):

=AVERAGEIF(B2:B17, "Thơm", D2) = 37.5

Nếu muốn tính trong ngày 01/7/2009, cô Thơm đã mua trung bình bao nhiêu hàng, dùng AVERAGEIFS (cách dùng y chang SUMIFS):

=AVERAGEIFS(D2

17, A2:A17, DATE(2009,7,1), B2:B17, "Thơm") = 57.5

Nhưng mấy hàm này... chỉ có trong Excel 2007, và Excel 2010! Bởi vậy, không nâng cấp theo thời đại, âu cũng là thiệt thòi!

ndu96081631 · 15/8/09

BNTT đã viết:
Hàm AVERAGEIF có sẵn mà? Thậm chí là AVERAGEIFS (tính trung bình theo nhiều điều kiện)...

Cái bảng tính đó, nếu muốn tính trung bình cô Thơm mua bao nhiêu hàng, dùng AVERAGEIF (cách dùng y chang SUMIF):
=AVERAGEIF(B2:B17, "Thơm", D2) = 37.5
Nếu muốn tính trong ngày 01/7/2009, cô Thơm đã mua trung bình bao nhiêu hàng, dùng AVERAGEIFS (cách dùng y chang SUMIFS):
=AVERAGEIFS(D217, A2:A17, DATE(2009,7,1), B2:B17, "Thơm") = 57.5
Nhưng mấy hàm này... chỉ có trong Excel 2007, và Excel 2010! Bởi vậy, không nâng cấp theo thời đại, âu cũng là thiệt thòi!

Cũng chưa hẳn đâu! Vì thực chất đây chỉ là bài toán do sư phụ đưa ra thôi, chứ thực tế để giải quyết bài này 1 cách nhanh nhất và toàn diện nhất (tổng hợp mọi thứ) thì tôi chả thèm dùng mấy hàm này chi cho mất công ---> PivotTable là ra tất tần tật!
........
Thêm nữa MINIF và MAXIF vẫn chưa có trong bất cứ version nào đâu nha!

BNTT · 15/8/09

ndu96081631 đã viết:
Cũng chưa hẳn đâu! Vì thực chất đây chỉ là bài toán do sư phụ đưa ra thôi, chứ thực tế để giải quyết bài này 1 cách nhanh nhất và toàn diện nhất (tổng hợp mọi thứ) thì tôi chả thèm dùng mấy hàm này chi cho mất công ---> PivotTable là ra tất tần tật!
........
Thêm nữa MINIF và MAXIF vẫn chưa có trong bất cứ version nào đâu nha!

Ẹc ẹc... em nói tới MINIF với MAXIF hồi nào? Em chỉ nói AVERAGEIF và AVERAIFS thôi mừ!

Còn nếu nói như anh, thì em cũng chả xài PivotTable! Thiệt! Em dùng SUBTOTAL cơ!
Dùng Autofilter lọc, xong dùng SUBTOTAL là biết ngay AVERAGE, MIN và MAX, hì hì... (chẳng lẽ SUBTOTAL nó có cả mớ tham số trong đó mà chỉ xài mỗi cái số 9, uổng lắm)

ndu96081631 · 15/8/09

BNTT đã viết:
Ẹc ẹc... em nói tới MINIF với MAXIF hồi nào? Em chỉ nói AVERAGEIF và AVERAIFS thôi mừ!
Còn nếu nói như anh, thì em cũng chả xài PivotTable! Thiệt! Em dùng SUBTOTAL cơ!
Dùng Autofilter lọc, xong dùng SUBTOTAL là biết ngay AVERAGE, MIN và MAX, hì hì... (chẳng lẽ SUBTOTAL nó có cả mớ tham số trong đó mà chỉ xài mỗi cái số 9, uổng lắm)

Vâng! Đương nhiên, nhưng SUBTOTALS chỉ là "vừa đủ" cho bài này ---> Ý tôi muốn nói đến việc TỔNG HỢP 1 CÁCH TOÀN DIỆN cơ!
-----------
Quay lại vấn đề về hàm tự tạo: E rằng cũng khó nhai khi phải xử lý ParamArray ---> Vì nếu muốn viết hàm MINIF này 1 cách tổng quát, ta phải viết cho nó dò được với nhiều điều kiện (tương tự SUMIFS trong Excel 2007)

rollover79 · 15/8/09

ChanhTQ@ đã viết:
Bạn nào viết gùm mình các hàm tương tự như vậy giúp nha, Xin cảm ơn trước!

Dữ liệu mẩu:

|A|B|C|D|E|
1| Ngày | HTen | DGia | SoLg | TTien
2|7/1/09|Thơm|2.5|70|175
3|7/1/09|Hồng|3|65|195
4|7/1/09|Nga|3.5|60|210
5|7/1/09|An|4|55|220
6|7/1/09|Hồng|4.5|50|225
7|7/1/09|Thơm|5|45|225
8|7/2/09|Hồng|5.5|40|220
9|7/2/09|Nga|6|35|195
10|7/2/09|An|6.5|30|195
11|7/2/09|Hồng|7|25|175
12|7/2/09|Thơm|7.5|20|15 0
13|7/2/09|Thơm|8|15|120
14|7/2/09|Nhung|8.5|10|85
15|7/2/09|Hồng|9|5|45
16|7/2/09|Nga|9.5|10|95
17|7/2/09|An|10|1|10
||.|||

Các bác viết giúp hàm trung bình cô Thơm mua số lượng hàng bao nhiêu;
Ngày 2/7 cô này mua với đơn giá thấp nhất là bao nhiêu?

Thân ái!

Tôi xin viết thử 1 hàm MinIf với nhiều điều kiện thử, tuy nhiên hàm này không thực hiện được yêu cầu "Ngày 2/7 cô này mua với đơn giá thấp nhất là bao nhiêu?" được, vì nó có nhiều hơn 1 vùng điều kiện, bản thân hàm SumIf của excel cũng không có được 2 vùng điều kiện để thực hiện điều này.

Mã:

Function MinIf(rngCondition As Range, rngValue, ParamArray Condition() As Variant) As Double
    Dim ret
    Dim i As Long
    Dim iRow As Long
    Dim iCol As Long
    Dim bValid As Boolean
    Dim bPass As Boolean
    bPass = False
    For iRow = 1 To rngCondition.Rows.Count
        For iCol = 1 To rngCondition.Columns.Count
            bValid = True
            For i = LBound(Condition) To UBound(Condition)
                If WorksheetFunction.CountIf(rngCondition(iRow, iCol), Condition(i)) = 0 Then
                    bValid = False
                    Exit For
                End If
            Next
            If bValid Then
                If Not bPass Then
                    ret = rngValue(iRow, iCol)
                    bPass = True
                Else
                    If ret > rngValue(iRow, iCol) Then ret = rngValue(iRow, iCol)
                End If
            End If
        Next
    Next
    If Not bPass Then ret = ""
    MinIf = ret
End Function

Hàm này có tham số thứ nhất là vùng điều kiện, tham số thứ 2 là vùng cần tính Min, tiếp theo là danh sách các điều kiện(Vẫn bị hạn chế số lượng bởi ParamArray)

ChanhTQ@ · 15/8/09

Phương án củ chuối nước của mình định như sau:

PHP:

Function MINIF( DatRange As Range, Cot As Byte, Optional Dat As Date)
 
1' Tìm Giá trị lớn nhất tong cột cần tìm giá trị min nhờ hàm 
 WorksheetFunction.MAX ( DataRange.Cells(1,1).Resize( DataRange.Rows.Count, Cot)'
 
2' Tạo vòng lặp duyệt từ đầu chí cuối với 2 điều kiện Dat <>0 & Dat = 0
    Nếu tại Cot mà anh chàng nào < trị MAX thì lấy nó làm MAX '
    . . . . .
 
End Function

Đây mới là í tưởng & ước mơ thôi!

tvbtvb1 · 15/8/09

các bạn ơi mình có thắc mắc muốn hỏi đây, phiền các bạn down cái file về để hiểu rõ hơn nhé ^^

mình đã làm được cái để tính tổng tất cả chữ A là 2100, còn bây giờ mình muốn tính tổng của tất cả các chữ A NGOẠI TRỪ số 1 (nghĩa là tính tổng của các A gồm 2,3,4 kết quả là 1300)

sau đó mình muốn tính tổng của các A NGOẠI TRỪ 1 VÀ 2 thì làm thế nào luôn ạ (tính tổng của A 3,4 kết quả là 1100)

salam · 15/8/09

tvbtvb1 đã viết:
các bạn ơi mình có thắc mắc muốn hỏi đây, phiền các bạn down cái file về để hiểu rõ hơn nhé ^^

mình đã làm được cái để tính tổng tất cả chữ A là 2100, còn bây giờ mình muốn tính tổng của tất cả các chữ A NGOẠI TRỪ số 1 (nghĩa là tính tổng của các A gồm 2,3,4 kết quả là 1300)

sau đó mình muốn tính tổng của các A NGOẠI TRỪ 1 VÀ 2 thì làm thế nào luôn ạ (tính tổng của A 3,4 kết quả là 1100)

Bạn xem trong file kèm nhé.

rollover79 · 15/8/09

ChanhTQ@ đã viết:
Phương án củ chuối nước của mình định như sau:

PHP:

Function MINIF( DatRange As Range, Cot As Byte, Optional Dat As Date) 1' Tìm Giá trị lớn nhất tong cột cần tìm giá trị min nhờ hàm WorksheetFunction.MAX ( DataRange.Cells(1,1).Resize( DataRange.Rows.Count, Cot)' 2' Tạo vòng lặp duyệt từ đầu chí cuối với 2 điều kiện Dat <>0 & Dat = 0 Nếu tại Cot mà anh chàng nào < trị MAX thì lấy nó làm MAX ' . . . . . End Function

Đây mới là í tưởng & ước mơ thôi!

Tôi chưa rõ lắm về í tưởng của bạn, thứ nhất là ý nghĩa các tham số và thứ 2 là trong trường hợp cụ thể trong ví dụ đầu tiên bạn đưa ra thì bạn sẽ sử dụng gọi hàm này như nào?

cuongnavn · 18/8/09

Hỏi về SUMIF

Xin chào mọi người, mọi người gỡ rối em công thức của Sumif này cái (có file đính kèm).
Nếu chuyển sang dạng chữ A,B,C thì cho kết quả đúng còn để ký tự * thì làm sao dùng được Sumif theo đúng yêu cầu của đề bài đây?

Cảm ơn mọi người.

ndu96081631 · 18/8/09

cuongnavn đã viết:
Xin chào mọi người, mọi người gỡ rối em công thức của Sumif này cái (có file đính kèm).
Nếu chuyển sang dạng chữ A,B,C thì cho kết quả đúng còn để ký tự * thì làm sao dùng được Sumif theo đúng yêu cầu của đề bài đây?

Cảm ơn mọi người.

Do SUMIF hiểu dấu * là ký tự đại diện nên bạn sẽ gặp rắc rối to khi dùng mã kiểu này
Nếu vẫn quyết định không sửa lại dử liệu nhập thì bạn nên dùng SUMPRODUCT thay cho SUMIF
Cụ thể, tại cell B15 gõ vào công thức:

PHP:

=SUMPRODUCT(($A$6:$A$11=A15)*($B$6:$B$11))

Kéo fill xuống

duongdv · 18/8/09

Ngoài ra bạn cũng có thể sử dụng hàm Dsum, nhưng cách này không hay bằng cách dùng công thức mảng như của bạn ndu96081631. Bạn tham khảo nhé

BNTT · 18/8/09

cuongnavn đã viết:
Xin chào mọi người, mọi người gỡ rối em công thức của Sumif này cái (có file đính kèm).
Nếu chuyển sang dạng chữ A,B,C thì cho kết quả đúng còn để ký tự * thì làm sao dùng được Sumif theo đúng yêu cầu của đề bài đây?

Cảm ơn mọi người.

Bạn dùng cái này thử xem:

=SUMIF($A$6:$A$11, SUBSTITUTE(A15,"*","~*"), $B$6)

Công thức tại ô B15. Copy xuống đến B17.

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

Huyền thoại GPE

Thành viên mới

Khó khăn biết dũng khí.

0901452không62

Hành động từ trái tim

Thành viên gắn bó

Huyền thoại GPE

Bùi Nguyễn Triệu Tường

Huyền thoại GPE

Bùi Nguyễn Triệu Tường

Huyền thoại GPE

Thành viên tiêu biểu

0901452không62

Thành viên mới

File đính kèm

Thành viên gắn bó

File đính kèm

Thành viên tiêu biểu

Thành viên mới

File đính kèm

Huyền thoại GPE

Thành viên tích cực

File đính kèm

Bùi Nguyễn Triệu Tường

Facebook

Group