Friday, November 7, 2008

Conditional Formatting - Các ví dụ và kỹ thuật nâng cao (tt)

B. CF nâng cao

a. Tô màu các ô chứa công thức


B1. Tạo name tên là CellHasFormula có Refers to là =GET.CELL(48,A1) (Lưu ý chọn ô A1 trước khi đặt Name).
B2. Chọn vùng A1:A10 là vùng có chứa các công thức
B3. Vào CF, chọn Formula Is và nhập vào công thức =CellHasFormula
B4. Chọn nút Format vào ngăn Patterns chọn màu nền xanh
B5. Nhấn nút OK hai lần để hoàn tất, khi đó các ô có chứa công thức sẽ được tô màu nền xanh



Sẽ trình bày cách dùng UDF sau.

b. Tô màu các ô đặt ở chế độ unlock

B1. Chọn vùng C1:C10
B2. Vào CF, chọn Formula Is và nhập vào công thức
=CELL(“protect”,C1)=0
B6. Chọn nút Format vào ngăn Patterns chọn màu nền xanh
B7. Nhấn nút OK hai lần để hoàn tất, khi đó các ô có thuộc tích unlock sẽ được tô màu nền xanh



c. Tìm các số có sai biệt lớn hơn khoảng ±5%

Ví dụ chúng ta có kết quả kinh doanh của 2 năm 2006 và 2007 với doanh số các tháng như bảng sau:

Hãy dùng CF tô màu nền xanh cho các ô bên cột doanh số năm 2007 có sự biến động lớn hơn khoảng ±5% so với doanh số của năm 2006.

B1. Chọn vùng G2:G13
B2. Vào CF, tại Formula Is nhập vào công thức sau:
=OR((G2/F2)-1>5%,(G2/F2)-1<-5%)
B3. Nhấn nút Format và Patterns và chọn nền màu xanh.
B4. Nhấn OK hai lần để hoàn tất



Hình này đã được thay đổi kích thước. Nhấp vào đây để xem hình đầy đủ. Kích thước hình gốc là 862x244.


d. Tìm nhóm 5 có giá trị cao nhất/ thấp nhất

B1. Chọn vùng J1:J20
B2. Vào CF, chọn Formula Is và nhập vào công thức sau
  • Để tìm nhóm 5 phần tử có giá trị cao nhất dùng: =LARGE(($J$1:$K$20),MIN( 5,COUNT($J$1:$K$20)))<=J1
  • Để tìm nhóm 5 phần tử có giá trị thấp nhất dùng; =SMALL(($L$1:$L$20),MIN( 5,COUNT($L$1:$L$20)))>=L1
B3. Vào Format, chọn Patterns và chọn nền màu tím
B4. Nhấn OK hai lần để hoàn tất





e. Tìm nhóm 10% có giá trị cao nhất/ thấp nhất

Cũng tương tự như câu d tuy nhiên số lượng phần tử tính theo dạng phần trăm (%).

B1. Chọn vùng N1:N20
B2. Vào CF, chọn Formula Is và nhập vào công thức sau
  • Để tìm nhóm 5 phần tử có giá trị cao nhất dùng: =IF(INT(COUNT($N$1:$N$20)*10%)>0,LARGE($N$1:$N$20, INT(COUNT($N$1:$N$20)*10%)),MAX( $N$1:$N$20))<=N1
  • Cách khác gọn hơn: =N1>=Percentile($N$1:$N$20,90%)
  • Để tìm nhóm 5 phần tử có giá trị thấp nhất dùng; =IF(INT(COUNT($P$1:$P$20)*10%)>0,SMALL($P$1:$P$20, INT(COUNT($P$1:$P$20)*10%)),MIN( $P$1:$P$20))>=P1
  • Cách khác gọn hơn: =N1<=Percentile($N$1:$N$20,10%)
B3. Vào Format, chọn Patterns và chọn nền màu tím
B4. Nhấn OK hai lần để hoàn tất



f. Tạo sơ đồ GANTT

Với ví dụ này sẽ giúp các bạn tạo được một sơ đồ GANTT đơn giản phục vụ cho phần báo cáo dự án của mình.

B1. Tạo vùng dữ liệu thô như hình minh hoạ R1:V13
B2. Tại ô V2 nhập vào công thức =U2-T2+1 để trả về số ngày
B3. Chọn ô W1 và nhập vào =T2
B4. Tại X1 nhập vào =W1+1 và sao chép công thức tại X1 cho vùng Y1:CW1
B5. Định dạng vùng W1:CW1 như sau:
  • Chọn vùng W1:CW1
  • Vào Format | Cell | chọn Custom tại khung Category và nhập vào khung Type dd sau đó nhấn OK
B6. Chọn vùng W2:CW13
B7. Vào CF chọn Formula Is và nhập vào công thức:
=AND(W$1>=$T2,W$1<=$U2)
B8. Chọn Format vào ngăn Patterns chọn màu nền xanh, vào ngăn Border chọn đường kẻ khung trên và dưới nằm ngang.
B9. Nhấn OK hai lần hoàn tất

Hình này đã được thay đổi kích thước. Nhấp vào đây để xem hình đầy đủ. Kích thước hình gốc là 1024x300.

g. CF khi có nhiều hơn 3 điều kiện

Bảng màu của Excel

Excel chỉ nhận biết màu theo tên của các màu từ Color 1 đến 8 (Black, White, Red, Green, Blue, Yellow, Magenta, và Cyan). Trong số 56 ô màu thì chỉ liệt kê có 40 màu trên bảng màu của Excel (chia làm 2 nhóm: nhóm trên 40 màu, nhóm dưới 16 màu thông dụng). Các cặp mã màu sau trên bảng màu là giống nhau: 11 & 25, 5 & 32, 14 & 31, 8 & 28, 9 & 30, 13 & 29, 18 & 54, 20 &34, 7 & 26, và 6 & 27.






Dưới đây là bảng quy định mã cho định dạng tùy biến: ĐỊNH DẠNG KIỂU CHO GIÁ TRỊ LÀ SỐ


Ví dụ: Thay đổi màu của giá trị trong ô dựa vào 6 điều kiện như sau:
  • Nếu giá trị nhỏ hơn hay bằng 0, font sẽ là màu đỏ (Red – Color 3)
  • Nếu gía trị lớn hơn 0 nhưng nhỏ hơn hay bằng 20, font sẽ là màu xanh lá cây (Green – Color 4)
  • Nếu giá trị lớn hơn 20 nhưng nhỏ hơn 31, font sẽ là màu xanh (Blue – Color 5)
  • Nếu giá trị giữa 31 và 40 thì font màu nâu vàng (Yellow – Color 6)
  • Nếu giá trị giữa 41 và 50 thì font màu hồng (Pink – Color 7).
  • Nếu giá trị lớn hơn hay bằng 51 thì font màu tím (Violet – Color 13).
B1. Để thực hiện được điều này bạn chọn vùng A30:C39, sau đó vào Format | Cells… | chọn ngăn Number | chọn Custom và nhập vào hộp Type như sau:

[Red][<=0]0;[Green][<=20]0;[Blue]0

B2. Sau khi nhấn OK chấp nhận định dạng tuỳ biến thì vào Format | chọn Conditional Formatting..
• Chọn Cell Value Is tại Condition 1 | chọn điều kiện là between nhập vào hai giá trị 31 và 40 vào hai ô trống kế bên. Nhấn nút Format | chọn ngăn Font | và chọn màu vàng tại Color. Nhấn OK hoàn tất CF1.
• Nhấn nút Add >> để thêm CF2. Chọn Cell Value Is tại Condition 2  chọn điều kiện là between nhập vào hai giá trị 41 và 50 vào hai ô trống kế bên. Nhấn nút Format | chọn ngăn Font | và chọn màu hồng tại Color. Nhấn OK hoàn tất CF2.
• Nhấn nút Add >> để thêm CF3. Chọn Cell Value Is tại Condition 3  chọn điều kiện là greater than or equal tonhập vào giá trị 51 vào ô trống kế bên. Nhấn nút Format | chọn ngăn Font | và chọn màu tím tại Color. Nhấn OK hoàn tất CF3.
B3. Nhấn OK hoàn tất



III. Định dạng theo điều kiện trong Excel 2007

Phần này trình bày cách định dạng theo điều kiện trong Excel 2007. Định dạng theo điều kiện đã được cải tiến rất nhiều trong phiên bản Excel 2007 và công cụ trở nên rất hữu ích trong việc mô hình hoá dữ liệu số. Trong một số trường hợp bạn có thể dùng CF thay cho đồ thị.

Danh mục định dạng theo điều kiện thiết lập sẵn:




Việc định dạng theo điều kiện có thể được thực hiện nhanh chóng thông qua các tập định dạng thông dụng được thiết lập trước hay người dùng có thể tạo qui luật định dạng mới tại New rules và quản lý các qui luật định dạng bằng công cụ Manage Rules…



Hộp thoại New Formatting Rule


Hộp thoại Conditional Formatting Rules Manager

Lựa chọn qui luật CF


Để áp dụng một qui luật CF cho cell (ô) hoặc range (vùng), trước tiên hãy chọn các ô sau đó vào
Home è Styles è Conditional Formatting è chọn lệnh CF. Có một số lựa chọn như:
  • Highlight Cell Rules: Chứa các qui luật định dạng làm nổi các ô chứa giá trị lớn hơn một giá trị xác định, nằm giữa hai giá trị, chứa một chuỗi xác định hoặc các giá trị trùng nhau,…
  • Top Bottom Rules:Chứa các qui luật định dạng làm nổi các ô chứa nhóm giá trị lớn nhất, nhóm 10% các giá trị lớn nhất và nhóm các giá trị trên trung bình của tập số liệu,
  • Data Bars: Áp dụng các đồ thị hình thanh trực tiếp trong các ô tương ứng với giá trị chứa trong ô.
  • Color Scales: Áp dụng màu nều tương ứng với giá trị chứa trong
  • Icon Sets: Hiển thị các icon trực tiếp trong các ô. Các icon hiển thị phụ thuộc vào giá trị chứa trong ô.
  • New Rule: Cho phép bạn chọn lựa các qui luật CF khác, bao gồm cả qui luật CF dựa vào kết quả luận lý của công thức.
  • Clear Rules: Xoá tất cả các CF đang áp dụng cho các ô được chọn
  • Manage Rules: Hiển thị hộp thoại Conditional Formatting Rules Manager, tại đó bạn có thể tạo mới, chỉnh sửa hoặc xoá các qui luật CF.

(còn tiếp)

1 comment:

Nguyen Thi Diem Quynh said...

Trong phần f. Tạo sơ đồ GANTT cac VD mới đề cập đến những ngày chẵn 1, 3 , 4 tác giả có thể cho thêm những VD về ngày lẻ vì đây là vấn đề người đọc quan tâm nhất.