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

  • Thread starter Thread starter OverAC
  • Ngày gửi Ngày gửi
Liên hệ QC

Người dùng đang xem chủ đề này

OverAC

Đỗ Nguyên Bình
Thành viên BQT
Administrator
Tham gia
30/5/06
Bài viết
2,697
Được thích
15,143
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


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​
|
 
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?
 
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?
 

File đính kèm

Chỉnh sửa lần cuối bởi điều hành viên:
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
 
Lần chỉnh sửa cuối:
@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​
|
 
Đã 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
 
Sử dụng 1/countif(..,..) để đếm. Cách này em học được của anh ndu...
 

File đính kèm

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ì:

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!
 
Chữ b đó là một cái Name:
=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A$2:$A$11))​
 
Lần chỉnh sửa cuối:
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à:
PHP:
=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...
 
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à?
 

File đính kèm

ý , 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:
PHP:
=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:
PHP:
=COUNT(OFFSET($A$2,,,COUNTA($A$2:$A$11)))
Cũng kéo fill xuống và kiểm nghiệm KQ
 
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))
 
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
 
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
Mã:
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 ô)
 
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
 

File đính kèm

anh ThuNghi ơi,
File của anh đáp án đúng là 4 và đúng là như vậy mà.
 
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.
 
Đá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))
 
Lần chỉnh sửa cuối:
Anh ThuNghi ơi,
Chắc là anh hiểu nhầm ý của câu hỏi của em. Ý của câu hỏi là đếm số lượng này có tổng doanh số lớn hơn 1000. Nghĩa là phải cộng lại theo ngày rồi mới đếm. Còn cách mà anh tính là tính số ngày có hóa đơn trên 1000.
Vấn đề mà anh hướng tới cũng là một trong những vấn đề mà em đang thực hiện. Nhưng xem chừng là nó đơn giản hơn vấn đề gốc của chủ đề này.
Cảm ơn anh.
OverAC
 
Mã:
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))
cái b đó cũng chết ngắc trong phạm vi A2:A11, có động đâỵ gì đâu? Trừ khi countA(Sheet1!$A$2:$A$1000) hay hơn nữa là $A$65000

ngoài ra, làm như ndu: =SUMPRODUCT((1/COUNTIF(b,b))*(SUMIF(b,b,OFFSET(b,,1))>1000)) thì mới bảo đảm động trong, động ngoài, chứ name b động, mà công thức chết như vầy:

=SUMPRODUCT((1/COUNTIF($A$2:$A$11,b))*(SUMIF($A$2:$A$11,b,OFFSET(b,,1))>1000))

thì cũng như không.
 
Lần chỉnh sửa cuối:
Chỉ vì cậu "học trò" học vẹt mà làm các "thầy" bối rối

Tại sao lại đặt
b=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A$2:$A$11))
chứ không phải là
b=OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A))
Tại sao lại không viết công thức là
=SUMPRODUCT((1/COUNTIF(b,b))*(SUMIF(b,b,OFFSET(b,,1))>1000))
mà lại viết là
=SUMPRODUCT((1/COUNTIF(A2:A11,b))*(SUMIF(A2:A11,b,B2:B11)>1000))
Biết trả lời các anh sao nhỉ? Trả lời thì xấu hổ mà không trả lời thì bứt rứt. Bởi thực sự vì học vẹt => chưa sâu + vội vàng nên làm các bác bỡ ngỡ. Qua các bác trao đổi => hiểu ra nhiều vấn hơn. Name động + công thức động => KQ tuyệt vời. Cám ơn các bác nhiều.
 
Tại sao lại đặt
chứ không phải là
Tại sao lại không viết công thức là
mà lại viết là
Biết trả lời các anh sao nhỉ? Trả lời thì xấu hổ mà không trả lời thì bứt rứt. Bởi thực sự vì học vẹt => chưa sâu + vội vàng nên làm các bác bỡ ngỡ. Qua các bác trao đổi => hiểu ra nhiều vấn hơn. Name động + công thức động => KQ tuyệt vời. Cám ơn các bác nhiều.
Thực ra =OFFSET($A$2,,,COUNTA($A:$A)) cũng chưa chính xác (vì sẽ dư 1 dòng)
Sửa lại
Hoặc
=OFFSET($A$2,,,COUNTA($A:$A)-1)
Hoặc
=OFFSET($A$2,,,COUNTA($A$2:$A$65536))
Tôi thì thích công thức dưới hơn... vì lý do sau:
- Nếu dùng công thức trên nhưng khi bạn thay đổi dử liệu (chẳng hạn insert thêm dòng) thì bạn phải tính toán lại xem mình cần phải trừ bớt bao nhiêu cho vừa đủ (không dư dòng)
- Với công thức dưới thì khỏi cần làm gì cả
 

Bài viết mới nhất

Back
Top Bottom