Chúng ta thường hỏi:
"Làm thế nào để tính toán với những ô đã được tô một màu cụ thể nào đó?"
Câu hỏi này thường được nêu ra, bởi vì Excel không có một hàm bình
thường nào để thưc hiện được nhiệm vụ này; tuy nhiên, nó có thể được
thực hiện bằng một hàm tự tạo.
Vấn đề duy nhất xảy ra với việc sử dụng hàm tự tạo, là nó không thể lọc
ra bất kỳ một loại định dạng nào đã được áp dụng bởi việc định dạng có
điều kiện (conditional formatting). Tuy nhiên, suy nghĩ một tí, bạn vẫn
có thể có được kết quả tương tự mà không phải cần đến một hàm tự tạo.
Giả sử rằng bạn có một danh sách dài những con số trong dãy $A$2:$A$100.
Và bạn đã áp dụng định dạng có điều kiện cho dãy đó: đánh dấu những ô
nào có giá trị nằm trong khoảng từ 10 đến 20. Bây giờ, bạn muốn lấy ra
giá trị của những ô thỏa mãn điều kiện mà bạn đã thiết lập, và tính tổng
của những ô đã được áp dụng định dạng đó. Không có gì khó! Bạn đừng để
những kiểu định dạng đã được áp dụng chi phối bạn, nói cách khác, bạn không cần quan tâm những ô đó được định dạng kiểu gì. Bạn chỉ cần quan tâm đến điều kiện để áp dụng định dạng cho chúng (trong trường hợp này, là những ô có giá trị trong khoảng từ 10 đến 20).
Bạn có thể dùng hàm SUMIF()
để tính tổng của những ô thỏa mãn điều kiện nào đó, nhưng chỉ một điều
kiện mà thôi! Nếu muốn có nhiều điều kiện, bạn phải dùng hàm SUMIFS()
trong Excel 2007, hoặc là dùng một công thức mảng. Ở đây tôi sẽ nói đến
công thức mảng, vì nó có thể sử dụng trong hầu hết các phiên bản của
Excel.
Với trường hợp đã ví dụ trong bài này, bạn sử dụng một công thức mảng giống như sau:
=SUM(IF($A$2:$A$100>10, IF($A$2:$A$100<20, $A$2:$A$100)))
Khi nhập một công thức mảng, bạn đừng nhấn Enter, hãy nhấn Ctrl+Shift+Enter. Khi đó, Excel sẽ tự động thêm một cặp dấu ngoặc ở hai đầu công thức, giống như vầy:
{=SUM(IF($A$2:$A$100>10, IF($A$2:$A$100<20, $A$2:$A$100)))}Nếu bạn tự gõ cặp dấu ngoặc đó, thì công thức sẽ không chạy. Bạn phải để Excel làm việc này cho bạn.
Và bạn cũng nên biết điều này: sử dụng công thức mảng có thể làm cho
Excel tính toán chậm hơn, nếu như có quá nhiều tham chiếu đến những dãy
lớn.Trên diễn đàn này có rất nhiều các bài viết về công
thức mảng, bạn có thể tham khảo thêm. Hoặc nếu bạn giỏi tiếng Anh và
thích nghiên cứu sâu hơn về công thức mảng, bạn hãy ghé thăm trang web
này: http://www.ozgrid.com/Excel/arrays.htm.
Một cách khác
Ngoài việc sử dụng công thức mảng, bạn có
thể dùng một cột phụ để tham chiếu đến những ô bên cột A. Những tham
chiếu này sẽ trả về những giá trị của cột A mà thỏa mãn điều kiện bạn đã
đặt ra (ví dụ: > 10, < 20). Để làm điều này, bạn theo các bước
sau:
Chọn ô B2 và nhập vào đó công thức:
=IF(AND(A2>10, A2<20), A2, "")Kéo
công thức này xuống cho đến ô B100. Khi các công thức đã được điền vào,
bạn sẽ có những giá trị nằm trong khoảng 10 đến 20 (xuất hiện trong cột
B).
Thêm một chiêu phụ: Để nhanh
chóng "kéo" các công thức vào trong một cột xuống đến ô cùng hàng với ô
cuối cùng đã được sử dụng của cột ngay bên cạnh (trong trường hợp này,
là "kéo" từ ô B2 đến ô B100, là ô tương ứng với ô cuối cùng đã được sử
dụng trong cột A, ô A100), sau khi nhập công thức trong ô đầu tiên (ô B2), hãy chọn ô đó, rồi nhấp đúp chuột (double click) vào cái Fill handle (là cái núm chút xíu nằm ở góc dưới bên phải của ô được chọn, mà bạn vẫn thường dùng để "kéo" công thức)Bây
giờ, bạn có thể chọn bất kỳ một ô nào mà bạn muốn xuất hiện tổng của
những giá trị thỏa mãn điều kiện đã đề ra, và sử dụng một hàm SUM bình
thường (=SUM(B2:B100) chẳng hạn). Bạn có thể ẩn (Hide) cột B đi nếu bạn
muốn.
Một cách khác nữa
Cách dùng cột phụ như tôi vừa nói, chắc
chắn là chạy tốt rồi. Nhưng, Excel còn có một hàm cho phép bạn sử dụng
hai hoặc nhiều điều kiện cho một dãy. Đó làm hàm DSUM().
Để thử nó, bạn dùng lại ví dụ ở trên: tính tổng của những giá trị trong
dãy $A$2:$A$100 thỏa mãn điều kiện lớn hơn 10 và nhỏ hơn 20. Bạn hãy
chọn các ô C1:D2, đặt tên cho nó là SumCriteria. Rồi chọn ô C1, nhập vào
đó công thức: =$A$1, tham chiếu đến ô đầu
tiên của Sheet. Copy công thức đó sang ô D1, bạn sẽ có hai bản sao cho ô
tiêu đề của cột A, và những ô này (C1, D1) sẽ được dùng như những ô
tiêu đề của vùng điều kiện của hàm DSUM, vùng mà bạn đã đặt tên là
SumCriteria (C1:D2).
Trong ô C2, nhập vào biểu thức >10. Trong ô D2, nhập vào biểu thức <20. Rồi tại ô mà bạn muốn có kết quả là tổng của những giá trị thỏa mãn điều kiện vừa nêu, nhập vào công thức sau:
=DSUM($A$1:$A$100, $A$1, SumCriteria)DSUM là một hàm có hiệu quả nhất khi bạn làm việc với những ô thỏa mãn nhiều điều kiện; và không giống như mảng, các Hàm cơ sở dữ liệu
được thiết kế riêng cho những trường hợp này. Thậm chí khi chúng tham
chiếu đến những dữ liệu rất lớn, làm việc với những con số lớn, thì ảnh
hưởng của chúng đến tốc độ tính toán là rất nhỏ so với việc dùng công
thức mảng.
Thêm một cách khác nữa
Cách này, tôi học được trên Giải pháp Excel: Dùng hàm SUMPRODUCT().
Cũng với bài toán tính tổng của những giá trị trong dãy $A$2:$A$100 thỏa
mãn điều kiện lớn hơn 10 và nhỏ hơn 20. Bạn hãy chọn ô mà bạn muốn có
kết quả là tổng của những giá trị thỏa mãn điều kiện vừa nêu, nhập vào
công thức sau:
=SUMPRODUCT(($A$2:$A$100>10) * ($A$2:$A$100<20) * $A$2:$A$100)Hoặc:
=SUMPRODUCT(--($A$2:$A$100>10), --($A$2:$A$100<20), $A$2:$A$100)Diễn
một cách bình dân, thì hàm SUMPRODUCT sẽ copy khối $A$2:$A$100 ra thành
3 mảng (trong bộ nhớ máy tính): Mảng thứ nhất, nếu giá trị trong một ô
mà > 10, ô đó sẽ có giá trị là 1 (TRUE), còn không thì bẳng 0
(FALSE); Mảng thứ hai, cũng tương tự như vậy, nhưng áp dụng cho những ô
có giá trị <20; và Mảng thứ 3 có giá trị trong mỗi ô bằng các giá trị
tương ứng trong $A$2:$A$100.
Tiếp theo, SUMPRODUCT sẽ nhân từng nhóm 3 giá trị tương ứng trong mỗi
mảng với nhau. Bạn sẽ thấy, chỉ khi nào giá trị trong mảng 1 và mảng 2
là 1, thì giá trị được nhân ra mới bằng giá trị tương ứng trong
$A$2:$A$100; còn nếu có một giá trị nào đó trong mảng 1 hoặc mảng 2 mà
bằng 0, thì kết quả của phép nhân này sẽ bẳng 0. Nói cách khác, chỉ
những giá trị nào trong $A$2:$A$100 thỏa mãn điều kiện >10 và <20
thì mới được lấy ra.
Cuối cùng, SUMPRODUCT sẽ cộng hết các kết quả của phép nhân ở trên (SUM
là phép tính tổng, PRODUCT là phép tính nhân, SUMPRODUCT là tổng của các
tích), và đó chính là kết quả mà ta muốn có.
Tiêu đề: Đếm hoặc cộng những ô đã được định dạng có điều kiện 
