Không biết giải thích với bạn sao nữa: Cấu trức của Countifs là (Mảng1, điều kiện mảng 1,Mảng2, điều kiện mảng 2....), CT COUNTIFS($B$2:$B$15,$B$2:$B$15,$A$2:$A$15,$A$2:$A$15,$A$2:$A$15,G$2) sẽ tạo ra 1 mảng ...
Ví dụ: Mảng={1,2},Countif(Mảng,1)=1, countif(Mảng,mảng) sẽ tạo ra mảng là {1,1}, tại sao ra mảng {1,1} vì countif(Mảng,mảng) sẽ tạo ra 2 cặp so sánh là {1,2}=1 và {1,2}=2 và kết quả là {1:1}, tương tự hàm countifs, hic kho hiểu thật, bạn hiểu được nhiêu thì hiểu nhe, bạn muốn hiểu cái này bạn nên tìm hiểu về mảng trước bạn ạ.
Chào vitinhvnbmt, LightStar252.
Tôi cũng giống như các bạn thời gian đầu tiếp xúc với công thức mảng và nghe lời giải thích cũng như vịt nghe sấm vậy, nhưng sau mới sáng tỏ dần nhờ truy ngược những bài học từ đâu "diễn đàn". Do vậy, để hiểu rõ bạn nên nghe lời khuyên của lenongree nên tìm hiểu về mảng trước. Bạn có thể tham khảo link:
http://www.mediafire.com/?3u02ppf9f3dvwb9 để tha hồ mà tìm hiểu, đây là kỳ công của ban quản trị trang "Giải Pháp Excel" hiến tặng cho anh chị em muốn tìm hiểu về Excel cơ bản-nâng cao.
Còn giờ, mạn phép lenongree tôi sẽ giải thích cho hai bạn theo kiểu người mới vừa "chạm đến" công thức mảng.
Nghe đến "công thức mảng", các bạn hình dung như là Excel sẽ tạo trong bộ nhớ 1 sheet trắng để làm nháp, cũng có cột có hàng như sheet bình thường, và khi bạn thấy ai đó ghi như: mảng {1,0,1,0,0,1,1,1,1,0}, thì bạn cứ coi như từng thành phần: 1 và 0 sẽ được ghi lên sheet nháp như sau: ô A1 lưu số 1, ô A2 lưu số 0, ô A3 lưu số 1, ô A4 lưu số 0, ô A5 lưu số 0, ô A6 lưu số 1, ô A7 lưu số 1, ô A8 lưu số 1, ô A9 lưu số 1, ô A10 lưu số 0, vậy nha.
Tức là, bất kỳ công thức bạn tạo cũng được Excel "tính nháp" trong bộ nhớ, sau khi đi hết câu lệnh sẽ trả kết quả ra tại vị trí ô bạn lập lệnh.
Trở lại vấn đề chính, bạn yêu cầu "ĐẾM" xem có bao nhiêu mã hàng:
"đã loại bỏ trùng lắp" và
"theo từng ngày" và khớp với
"ngày quy định" (F3: 02/07 đến M3: 26/07), ta có tất cả: 3 điều kiện
(khoan vội đề cập đến a,b,c,d,e nha, cuối cùng sẽ thêm vào)
Công thức của lenongree như sau:
{=SUM(IFERROR(1/COUNTIFS($B$2:$B$20,$B$2:$B$20,$A$2:$A$20,$A$2:$A$20,$A$2:$A$20,F$2),0))}
Then chốt của lệnh là:
1/COUNTIFS($B$2:$B$20,$B$2:$B$20,$A$2:$A$20,$A$2:$A$20,$A$2:$A$20,F$2), mà cấu trúc lệnh: 1/Countif() hoặc 1/Countifs() là 1 trong những ứng dụng của công thức mảng để loại bỏ những giá trị trùng lắp
(xem hướng dẫn trong tài liệu theo link trên).
Ta phân "phân số" này là tử số "1" và mẫu số "Countifs()" để phân tích như sau:
*Mẫu số:
COUNTIFS($B$2:$B$20,$B$2:$B$20,$A$2:$A$20,$A$2:$A$20,$A$2:$A$20,F$2)
Nó sẽ thi hành từng lệnh 1 như dưới đây:
COUNTIFS($B$2:$B$20,B2,$A$2:$A$20,A2,$A$2:$A$20,F$2)
COUNTIFS($B$2:$B$20,B3,$A$2:$A$20,A3,$A$2:$A$20,F$2)
COUNTIFS($B$2:$B$20,B4,$A$2:$A$20,A4,$A$2:$A$20,F$2)
COUNTIFS($B$2:$B$20,B5,$A$2:$A$20,A5,$A$2:$A$20,F$2)
COUNTIFS($B$2:$B$20,B6,$A$2:$A$20,A6,$A$2:$A$20,F$2)
...................................................................................... tương tự đến
COUNTIFS($B$2:$B$20,B20,$A$2:$A$20,A20,$A$2:$A$20,F$2)
(bạn có thể thử nhập các công thức này vào ô N2 đến N20 sẽ thấy kết quả tương tự như mảng dưới)Các lệnh này sẽ cho ra kết quả và được lưu trong sheet nháp của bộ nhớ 1 mảng sau:
{2,1,2,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0} (hình dung lưu trong sheet nháp từ A1:A20)
Tức là, mặc dù hàm thì ghi COUNTIFS($B$2:$B$20,$B$2:$B$20....) "$B$2:$B$20" lặp lại 2 lần, nhưng ý nghĩa khác nhau hoàn toàn: vế đầu là vùng (dãy ô) để đếm các mã, vế thứ hai là
"tiêu chí"(hay nôm na là điều kiện đếm) để lọc, tiêu chí này là từng ô bắt đầu từ A2 cho đến ô A20 thì hết. Tương tự bạn luận cho ...,$A$2:$A$20,$A$2:$A$20.
Nói chung, bạn ra lệnh cho excel hãy đếm có bao nhiêu mã từ B2:B20 có mã tại B2, tương ứng với cột ngày từ A2:A20 giống số ngày tại ô A2, và giống với "ngày quy định" tại ô F2. Excel sẽ thực thi từng câu lệnh như trên.
Ráp với tử số "1" ta có:
1/COUNTIFS(....) = 1/{2,1,2,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0} (bạn hình dung như ghi thêm vào sheet nháp như sau: B1= 1/A1, B2= 1/A2, B3= 1/A3.........B20= 1/A20)
ta có kết quả
={0.5,1,0.5,1,#DIV0!,#DIV0!,#DIV0!,#DIV0!,#DIV0!,#DIV0!,#DIV0!,#DIV0!,#DIV0!,#DIV0!,#DIV0!,#DIV0!,#DIV0!,#DIV0!,#DIV0!,#DIV0!,}
Để ý rằng "X1450001" có giá trị đếm là 2 tức là có 2 mã "X145001" trong dãy B2:B20 thỏa các điều kiện đã nêu, do đó khi lấy 1 đem chia cho 2 và cộng lại, ta có 1/2 + 1/2 =1
Giả sử thêm, như có 1 mã hàng nào đó có tổng số đếm là 3, tức là có 3 mã trùng nhau, khi đem 1 chia, ta có: 1/3 +1/3 +1/3 = 1; tương tự: có 4 mã hàng thì: 1/4 + 1/4 + 1/4 + 1/4 = 1
Nhờ động thái lấy 1 chia cho tổng số lần xuất hiện rồi cộng lại các thành phần, excel loại bỏ trùng lắp chỉ đếm là
1 mã hàng.
Ráp với
IFERROR( 1/COUNTIFS(...)
, 0) Iferror(,0) sẽ đổi những giá trị lỗi #DIV0! thành 0, do vậy nó sẽ ra kết quả:
{0.5,1,0.5,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}
Sau cùng ráp với SUM(...) ta có: SUM(
{0.5,1,0.5,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}) = 3
Đến đây, nếu không tính thêm các điều kiện như a,b,c,d,e, thì bạn có thể hình dung con đường mà câu lệnh đưa ra kết quả, để bạn hiểu được rõ và thực hiện cho những trường hợp tương tự khác mà bạn gặp phải sau này.
Để ráp thêm điều kiện xét theo a,b,c,d,e thì bạn phải hình dung thêm như sau:
Bạn sẽ đồng ý rằng, biểu thức: 1/n/m sẽ bằng 1/(n*m)
(vd: 1/2/5/9 sẽ bằng 1/90). Trong công thức mảng cũng vậy bạn sẽ thường thấy điều này xãy ra trong các câu lệnh. Chẳng qua, đây là nối thêm 1 điều kiện vào điều kiện trước đó: hễ toán nhân "*" (n*m) được hiểu là phải thực hiện: điều kiện n
VÀ điều kiện m
(giống AND(n,m) vậy), còn khi sử dụng: n+m được hiểu là thực hiện: điều kiện n
HOẶC điều kiện m
(giống OR(n.m) vậy).
Đại để giải thích cho bạn câu lệnh, còn bạn nên tiếp tục bằng việc trở ngược lại tìm hiểu "căn cơ" hơn về 'CÔNG THỨC MẢNG" "kỳ quan của thế giới EXCEL".
Chúc các bạn ngày vui
)
