Tính số lượng ngày có tổng doanh số thỏa mãn điều kiện

[OverAC]

Nhờ các anh chị các bạn giúp dùm trường hợp này, tui loay hoay hoài không nghĩ ra cách nào tối u.


Dữ liệu ban đầu của tôi là doanh số theo ngày và theo hóa đơn như bảng 1:
Điều tôi muốn tính là, tính xem có bao nhiêu ngày có tổng doanh số lớn hơn 1000.

Số hóa đơn​

​

|

Ngày​

​

|

Doanh số​

​

|

1​

|

01/12/2008​

|

601​

|

2​

|

01/12/2008​

|

96​

|

3​

|

02/12/2008​

|

409​

|

4​

|

02/12/2008​

|

125​

|

5​

|

03/12/2008​

|

696​

|

6​

|

03/12/2008​

|

731​

|

7​

|

04/12/2008​

|

902​

|

8​

|

04/12/2008​

|

967​

|

9​

|

05/12/2008​

|

970​

|

10​

|

05/12/2008​

|

417​

|

11​

|

06/12/2008​

|

76​

|

12​

|

06/12/2008​

|

864​

|

13​

|

07/12/2008​

|

662​

|

14​

|

07/12/2008​

|

47​

|

15​

|

08/12/2008​

|

81​

|

16​

|

08/12/2008​

|

698​

|

17​

|

09/12/2008​

|

560​

|

18​

|

09/12/2008​

|

7​

|

19​

|

10/12/2008​

|

967​

|

20​

|

10/12/2008​

|

15​

|

21​

|

11/12/2008​

|

493​

|

22​

|

11/12/2008​

|

362​

|

23​

|

12/12/2008​

|

74​

|

24​

|

12/12/2008​

|

670​

|

25​

|

13/12/2008​

|

781​

|

26​

|

13/12/2008​

|

482​

|

27​

|

14/12/2008​

|

113​

|

28​

|

14/12/2008​

|

146​

|

29​

|

15/12/2008​

|

534​

|

30​

|

15/12/2008​

|

691​

|

 

[cadafi]

Vấn đề này có cho phép dùng Cột phụ kèm chức năng Subtotal không!? Nếu dùng Pivotable thì sao?

 

[cop_kh]

Dùng DCOUNT đi Sếp, chắc do nghĩ cao siêu nên đơn giản quá làm Sếp rối...hihihi

Phải thế này không Sếp?

 

[cadafi]

Dùng DCOUNT đi Sếp, chắc do nghĩ cao siêu nên đơn giản quá làm Sếp rối...hihihi

Sai rồi bạn ơi!
- Ngày có thể trùng nhau! Cùng một ngày mà có hai nghiệp vụ phát sinh thỏa điều kiện thì bạn đếm là bao nhiêu! 1 hay 2

- Cùng một ngày mà có hai nghiệp vụ phát sinh có thể không thỏa điều kiện, nhưng tổng các phát sinh của cùng ngày đó thỏa điều kiện thì bạn đếm là mấy nếu dùng Dcount!???? 1 hay 2 hay 0

Lưu ý đề bài là:

có bao nhiêu ngày có tổng doanh số lớn hơn 1000

 

[OverAC]

@anh Ca_dafi: nếu dùng thêm cột phụ thì dể
còn nếu dùng thêm subtotal hoặc pivot là có kết quả có điều ở đây em muốn có công thức để dể nhân rộng ra nhiều trường hợp.

@Cop_kh: Có lẻ bạn hiểu chưa đúng ý mình:
Hiện tại để tính cái đó mình phải có bảng sơ kết như sau, rồi mới dùng hàm Sumif, countif hay Dcount được

ngày​

​

|

Tổng doanh số​

​

|

01/12/2008​

|

697​

|

02/12/2008​

|

534​

|

03/12/2008​

|

1,427​

|

04/12/2008​

|

1,869​

|

05/12/2008​

|

1,387​

|

06/12/2008​

|

940​

|

07/12/2008​

|

709​

|

08/12/2008​

|

779​

|

09/12/2008​

|

567​

|

10/12/2008​

|

982​

|

11/12/2008​

|

855​

|

12/12/2008​

|

744​

|

13/12/2008​

|

1,263​

|

14/12/2008​

|

259​

|

15/12/2008​

|

1,225​

|

 

[OverAC]

Đã tìm ra được 1 cách nhưng công thức dài và lòng thòng quá.
{=SUMPRODUCT(--((COUNTIF(INDIRECT("B3:B" & ROW($C$2:$C$31)),$B$2:$B$31)=1)*SUMIF(B2:B31,B2:B31,C2:C31)>1000))}

Tối em về nhà sẽ coi lại, mong nhờ các bác khác giúp rút gọn cộng thức.
OverAC

 

[npv]

Sử dụng 1/countif(..,..) để đếm. Cách này em học được của anh ndu...

 

[ndu96081631]

Sử dụng 1/countif(..,..) để đếm. Cách này em học được của anh ndu...

Ái chà... lại thêm 1 chiêu mới
Không nói đến giãi thuật, chỉ nói đến cách biểu diển hàm thì:

COUNTIF(A2:A11,b)

và

SUMIF(A2:A11,b,B2:B11)

Cái chử b ở trong hàm này mang ý nghĩa gì vậy bạn?
Hơi bị lạ ---> Cứ như là KỸ XẢO vậy!

 

[BNTT]

Chữ b đó là một cái Name:

=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A$2:$A$11))​

 

[ndu96081631]

Chữ b đó là một cái Name:

=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A$2:$A$11))​

Ôi trời... làm hết hồn... tưởng nó là KỸ XẢO (sơ xuất thật)
Vậy sao không viết công thức là:

=SUMPRODUCT((1/COUNTIF(b,b))*(SUMIF(b,b,OFFSET(b,,1))>1000))

(nếu viết thế thì tôi đâu có bị "sa bẩy")
Ẹc... Ẹc...

 

[BNTT]

Còn công thức này:

Đã tìm ra được 1 cách nhưng công thức dài và lòng thòng quá.

=SUMPRODUCT(--((COUNTIF(INDIRECT("B3:B" & ROW($C$2:$C$31)), $B$2:$B$31) =1) * SUMIF(B2:B31, B2:B31, C2:C31) >1000))
​

Đâu cần là công thức mảng, nhấn Enter nó cũng chạy mà?

 

[vumian]

Chữ b đó là một cái Name:

=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A$2:$A$11))​

ý , sao em đem cái b ra ngoài, thì kết quả trả về của offset kỳ kỳ vậy. chắc em phải học lại hàm offset ?

 

[ndu96081631]

ý , sao em đem cái b ra ngoài, thì kết quả trả về của offset kỳ kỳ vậy. chắc em phải học lại hàm offset ?

Đâu có gì kỳ...
=OFFSET($A$2,,,COUNTA($A$2:$A$11)) là giá trị của 1 VÙNG chứ có phải của 1 CELL đâu
Hảy bôi đen công thức trên thanh Formula rồi bấm F9 sẽ thấy ---> Cả 10 công thức ấy đều giống nhau
Thử nghiệm chứng minh:
Gõ vào 1 cell nào đó công thức:

=SUM(OFFSET($A$2,,,COUNTA($A$2:$A$11)))

Xem nó ra bao nhiêu ---> Kéo fill xuống và xem các kết quả khác có giống với cell đầu không?
Thử nghiệm tiếp ---> Gõ vào 1 cell nào đó công thức:

=COUNT(OFFSET($A$2,,,COUNTA($A$2:$A$11)))

Cũng kéo fill xuống và kiểm nghiệm KQ

 

[OverAC]

Em thắc mắc là vì sao phải dùng đến b là một name, vì b này trả ra kết quả là A2:A11, em lại dùng A2:A11 thế vào thay cho b thì kết quả vẫn đúng.
Vậy tại sao dùng b

=SUMPRODUCT((1/COUNTIF(A2:A11,A2:A11))*(SUMIF(A2:A11,A2:A11,B2:B11)>1000))

 

[Nguyễn Duy Tuân]

Nếu dùng công thức mà thấy khó khăn quá hay chậm máy thì nên dùng SQL.
Chỉ đơn giản thế này:

SELECT COUNT(NGAYCT) FROM DOANHSO GROUP BY NGAYCT HAVING SUM(SOTIEN)>600

 

[boyxin]

Em thắc mắc là vì sao phải dùng đến b là một name, vì b này trả ra kết quả là A2:A11, em lại dùng A2:A11 thế vào thay cho b thì kết quả vẫn đúng.
Vậy tại sao dùng b

b =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A$2:$A$11))

Nhưng cái name b đó nó linh động hơn ở chỗ khi thêm dữ liệu thì tự cập nhật do có: COUNTA(Sheet1!$A$2:$A$11))
Nếu thay trực tiếp như vậy thì khi thêm dữ liệu lại phải sửa công thức (thất là bất tiện nếu cần phải sửa công thức ở nhiều ô)

 

[ThuNghi]

Em thắc mắc là vì sao phải dùng đến b là một name, vì b này trả ra kết quả là A2:A11, em lại dùng A2:A11 thế vào thay cho b thì kết quả vẫn đúng.
Vậy tại sao dùng b

Sao tôi vận dụng ct trên mà chưa được, chả hiểu sai chỗ nào.
Đáp án theo file sau là 2, mà vẫn ra KQ 4

 

[OverAC]

anh ThuNghi ơi,
File của anh đáp án đúng là 4 và đúng là như vậy mà.

 

[hoangvuluan]

SumIf

Sao tôi vận dụng ct trên mà chưa được, chả hiểu sai chỗ nào.
Đáp án theo file sau là 2, mà vẫn ra KQ 4

Đáp án 4 của bạn là đúng rồi, có gì sai đâu? Cả 4 ngày (mỗi ngày có từ 2 đến 3 dòng) tổng cộng đều lớn hơn 1000 thì hàm count nó đếm bằng 4 là đúng.

 

[ThuNghi]

Đáp án 4 của bạn là đúng rồi, có gì sai đâu? Cả 4 ngày (mỗi ngày có từ 2 đến 3 dòng) tổng cộng đều lớn hơn 1000 thì hàm count nó đếm bằng 4 là đúng.

Nhưng mà cần duy nhất, ngày 01/12/08 có 3 dòng => lấy 1.
Chớ không chỉ cần
=SUMPRODUCT(--((A2:A11>0)),--((B2:B11)>1000))