Trung bình Cộng theo màu chữ của EXCEL

Nguyễn Phương Thảo fthaoabc@yahoo.com

    Trong Excel, ngoài các hàm có sẵn ta có thể xây dựng các hàm mới. Tôi xin giới thiệu một cách vận dụng Visual Basic để tính hàm trung bình cộng các ô có cùng màu chữ với ô mẫu, trong vùng EXCEL. Muốn sử dụng hàm ta phải truyền 2 biến: Tên vùng và ô làm mẫu.

    Từ EXCEL ta khởi động Visual Basic bằng nhiều cách, tôi chọn Alt+F11. Rồi gõ Ctrl +R để hiện Project EXplore.

    Ta Click để chọn tệp của mình làm dự án. Rồi Click Insert\Module để hiện cửa sổ soạn thảo hàm:

Đánh vào như sau:
Option Explicit
Function TBCONGMAU(VungDL, OMau)
Dim Tong, OHT, MauHT, MauChuan, dem, KQ

'Lấy màu chuẩn
MauChuan=OMau.Font.Color
'Xử lý từng ô một trong vùng
For Each OHT in VungDL

'Lấy màu của ô hiện tại
MauHT=OHT.Font.Color

'Nếu là ô số có màu chữ trùng màu thì cộng vào biến tong, và tăng biến dem
If IsNumeric(OHT) AND (MauHT=MauChuan) Then
Tong=Tong+OHT
dem = dem +1
End If
Next
if dem =0 then
KQ="Không có ô nào trùng màu ô mẫu"
Else
KQ=Tong/dem
End if
TBCONGMAU=KQ
End Function

    Tương tự, ta có thể thiết kế các hàm liên quan tới các thuộc tính của ô. Như cộng theo màu, đếm theo màu.... Sử dụng các thuộc tính của ô thông dụng như:
Màu chữ = Tên_Ô.Font.Color
Tên Font chữ = Tên_Ô.Font.Name
Cỡ chữ = Tên_Ô.Font.Size
Màu nền = Tên_Ô.Interior.Color
........

    Để sử dụng các hàm đã thiết kế, ta cần biên dịch thành các tệp Add-In thường có đuôi .XLA bằng cách cất luôn tệp thành dạng Add-In. (Khi cất, hộp File of Type: chọn kiểu Add-In). Sau đó tiến hành Add-In tệp vào Excel bằng cách chọn Tools\Add-In, rồi Browse để tìm tệp.

    Khi sử dụng hàm thì sử dụng như một hàm bình thường đã có của Excel.
Ví dụ: =CONGMAU($E$5:$F$200; $a$3)
Nếu trong vùng $E$5:$F$200 có ô số trùng màu chữ với ô $a$3 thì cộng để đưa ra kết quả.

Thiết lập tự động màu sắc cho các ô tuỳ thuộc giá trị của ô:

* Thường sử dụng một trong 2 cách:

    Cách 1: Sử dụng Code trong Format Cell:
Chọn ô, vào Format Cell\Number đánh trực tiếp vào hộp Code (Hoặc hộp Type tuỳ theo Version của Excel) theo mã Code thứ tự sau:

Code 1: Số dương ; Số âm ; Số 0; Chuỗi ký tự
Ví dụ: "Lãi " #.### " đồng" [red];"Lõm " #.### " đồng" [blue]; "Hoà vốn";"Nhập sai"
(Nếu giá trị nhập vào là số dương thì chữ màu đỏ, số âm, chữ màu xanh.)

Code 2: [> Mốc] Số lớn hơn mốc; Số âm ; Số từ 0 đến mốc ; Chuỗi ký tự
Ví dụ: [>=200] #.### "cái dư" [red]; "thiếu "#.### " cái"; #.### "cái dư" [blue]; "Nhập sai"
(Nếu số hơn 200 thì hiện màu đỏ, số dương nhỏ hơn 200 sẽ màu xanh...)

    Như vậy, ta chỉ việc nhìn lướt qua bảng tính từ màu sắc của các ô, ta sẽ biết được những vùng nào có số phù hợp mà mình đã đặt.

    Cách 2: Dùng cho những máy cài Excel 97 trở lên:
Bạn hãy lựa chọn ô rồi chọn từ Format\Conditional Formatting... tuỳ theo dấu so sánh và giá trin so sánh để ta Format lấy màu sắc của ô.

    Sử dụng các hàm thiết lập để tính đối với màu sắc các ô. Nếu máy sử dụng OFFICE2000 không thực hiện được xin bạn cài bổ sung OFFICE2000 ở chế độ FULL (Run All From My Computer)

Chúc các bạn thành công.

PcLeHoan 1996 - 2002
Mirror : http://www.pclehoan.com
Mirror : http://www.lehoanpc.net
Mirror : http://www.ktlehoan.com