Part III: Creating Charts and Graph

Part III: Creating Charts and Graphics

New Feature
The data bars feature is improved significantly in Excel 2010. Data bars now display proportionally (just like a bar chart), and there is now an option to display data bars in a solid color (no more forced color gradient) and with a border. In addition, negative values can now display in a different color, and to the left of an axis. n

A simple data bar
Figure 20.4 shows an example of data bars. It’s a list of tracks on Bob Dylan albums, with the length of each track in column D. I applied data bar conditional formatting to the values in column
D. You can tell at a glance which tracks are longer.

On the CD
The examples in the section are available on the companion CD-ROM. The workbook is named data bars examples.xlsx.


The length of the data bars is proportional to the track length in the cell in column D.



Tip
When you adjust the column width, the bar lengths adjust accordingly. The differences among the bar lengths are more prominent when the column is wider. n

Excel provides quick access to 12 data bar styles via Home ➪ Styles ➪ Conditional Formatting ➪ Data Bars. For additional choices, click the More Rules option, which displays the New Formatting Rule dialog box. Use this dialog box to


486

Chapter 20: Visualizing Data Using Conditional Formatting

l Show the bar only (hide the numbers).
l Specify Minimum and Maximum values for the scaling.
l Change the appearance of the bars.
l Specify how negative values and the axis is handled.
l Specify the direction of the bars.

Note
Oddly, the colors used for data bars are not theme colors. If you apply a new document theme, the data bar colors do not change. n

Using data bars in lieu of a chart
Using the data bars conditional formatting can sometimes serve as a quick alternative to creating a chart. Figure 20.5 shows a three-column table of data (created by using Insert ➪ Tables ➪ Table), with data bars conditional formatting applied in the third column. The third column of the table contains references to the values in the second column. The conditional formatting in the third col- umn uses the Show Bars Only option, so the values are not displayed.


This table uses data bars conditional formatting.


Figure 20.6 shows an actual bar chart created from the same data. The bar chart takes about the same amount of time to create and is a lot more flexible. But for a quick-and-dirty chart, data bars are a good option — especially when you need to create several such charts.

Using color scales
The color scale conditional formatting option varies the background color of a cell based on the cell’s value, relative to other cells in the range.




487

Part III: Creating Charts and Graphics


A real Excel bar chart (not conditional formatting data bars).


A color scale example
Figure 20.7 shows a range of cells that use color scale conditional formatting. It depicts the num- ber of employees on each day of the year. This is a 3-color scale that uses red for the lowest value, yellow for the midpoint, and green for the highest value. Values in between are displayed using a color within the gradient.


A range that uses color scale conditional formatting.



On the CD
This workbook, named color scale example.xlsx, is available on the companion CD-ROM. n


488

Chapter 20: Visualizing Data Using Conditional Formatting

Excel provides four 2-color scale presets and four 3-color scales presets, which you can apply to the selected range by choosing Home ➪ Styles ➪ Conditional Formatting ➪ Color Scales.

To customize the colors and other options, choose Home ➪ Styles ➪ Conditional Formatting ➪ Color Scales ➪ More Rules. This command displays the New Formatting Rule dialog box, shown in Figure 20.8. Adjust the settings, and watch the Preview box to see the effects of your changes.


Use the New Formatting Rule dialog box to customize a color scale.



An extreme color scale example
It’s important to understand that color scale conditional formatting uses a gradient. For example, if you format a range using a 2-color scale, you will get a lot more than two colors. You’ll get colors with the gradient between the two specified colors.

Figure 20.9 shows an extreme example that uses color scale conditional formatting on a range of 10,000 cells (100 rows x 100 columns). The worksheet is zoomed down to 20% to display a very smooth three-color gradient. The range contains formulas like this one, in cell C5:

=SIN($A2)+COS(B$1)

Values in column A and row 1 range from 0 to 4.0, in increments of 0.04.

When viewed onscreen, the result is stunning; it loses a lot when converted to grayscale.

On the CD
This workbook, named extreme color scale.xlsx, is available on the companion CD-ROM. n



489

Part III: Creating Charts and Graphics


This worksheet, which uses color scale conditional formatting, displays an

Kết quả (Việt) 1:

Phần III: Tạo bảng xếp hạng và đồ họaTính năng mớiTính năng thanh dữ liệu được cải thiện đáng kể trong Excel 2010. Dữ liệu thanh bây giờ hiển thị tương ứng (giống như một biểu đồ bar), và đó là một tùy chọn để hiển thị dữ liệu thanh trong màu (bắt buộc không có nhiều màu sắc gradient) và với một biên giới. Ngoài ra, giá trị tiêu cực có thể hiển thị trong một màu khác nhau, và để lại một trục. nMột thanh dữ liệu đơn giảnCon số 20.4 cho thấy một ví dụ của các dữ liệu thanh. Nó là một danh sách các bài nhạc vào album Bob Dylan, với chiều dài mỗi ca khúc trong cột D. Tôi áp dụng dữ liệu bar có điều kiện định dạng các giá trị trong cộtMất bạn có thể cho biết sơ lược về bài hát mà lâu hơn.Trên đĩa CDVí dụ trong phần có sẵn trên đồng hành đĩa CD-ROM. Bảng tính được đặt tên theo dữ liệu thanh examples.xlsx.Chiều dài của các thanh dữ liệu là tỷ lệ thuận với chiều dài theo dõi trong các tế bào trong cột D. MẹoKhi bạn điều chỉnh độ rộng cột, quầy bar dài điều chỉnh cho phù hợp. Sự khác biệt giữa các thanh dài là nổi bật hơn khi các cột là rộng lớn hơn. nExcel cung cấp truy cập nhanh vào phong cách bar 12 dữ liệu thông qua trang chủ ➪ phong cách ➪ định dạng có điều kiện ➪ dữ liệu thanh. Để lựa chọn bổ sung, bấm vào tùy chọn thêm quy tắc, Hiển thị hộp thoại quy tắc định dạng mới. Sử dụng hộp thoại này để486 Chương 20: Visualizing dữ liệu bằng cách sử dụng định dạng có điều kiệnl Hiển thị thanh duy (ẩn số).l chỉ định tối thiểu và tối đa giá trị cho việc mở rộng quy mô.l thay đổi sự xuất hiện của các quán bar.l chỉ định cách phủ định giá trị và các trục được xử lý.l chỉ định hướng của các quầy Bar.Lưu ýKỳ quặc, các màu sắc được sử dụng cho dữ liệu thanh không phải là chủ đề màu sắc. Nếu bạn áp dụng một chủ đề tài liệu mới, màu sắc thanh dữ liệu không thay đổi. nBằng cách sử dụng dữ liệu thanh thay cho biểu đồBằng cách sử dụng dữ liệu quán bar có điều kiện định dạng có thể đôi khi phục vụ như là một thay thế nhanh chóng để tạo ra một biểu đồ. Con số 20.5 cho thấy một bảng ba cột dữ liệu (tạo ra bằng cách sử dụng chèn ➪ bảng ➪ bảng), với dữ liệu quán bar có điều kiện định dạng được áp dụng trong cột thứ ba. Cột thứ ba của bảng chứa các tham chiếu đến các giá trị trong cột thứ hai. Định dạng có điều kiện trong col-umn thứ ba sử dụng các chỉ của thanh hiển thị tùy chọn, vì vậy, các giá trị không được hiển thị.Bảng này sử dụng dữ liệu thanh định dạng có điều kiện. Con số 20.6 cho thấy một biểu đồ thanh thực tế tạo ra từ các dữ liệu tương tự. Biểu đồ thanh mất khoảng cùng một lượng thời gian để tạo ra và linh hoạt hơn rất nhiều. Nhưng đối với một biểu đồ quick-and-dirty, dữ liệu thanh một lựa chọn tốt-đặc biệt là khi bạn cần để tạo ra một số bảng xếp hạng như vậy.Bằng cách sử dụng màu sắc quy môMàu quy mô có điều kiện định dạng tùy chọn thay đổi màu nền của một tế bào dựa trên giá trị của tế bào, liên quan đến các tế bào khác trong phạm vi.487 Phần III: Tạo bảng xếp hạng và đồ họaMột thực tế Excel biểu đồ thanh (không có điều kiện định dạng dữ liệu thanh). Một ví dụ quy mô màuCon số 20.7 cho thấy một loạt các tế bào sử dụng màu sắc quy mô định dạng có điều kiện. Nó mô tả num-ber của nhân viên vào mỗi ngày trong năm. Đây là một quy mô 3 màu sử dụng màu đỏ cho các giá trị thấp nhất, màu vàng cho các trung điểm, và màu xanh lá cây cho giá trị cao nhất. Các giá trị ở giữa được hiển thị bằng cách sử dụng một màu trong các gradient.Phạm vi sử dụng màu sắc quy mô định dạng có điều kiện. Trên đĩa CDBảng tính này, đặt tên là màu quy mô example.xlsx, có sẵn trên CD-ROM. n đồng488 Chương 20: Visualizing dữ liệu bằng cách sử dụng định dạng có điều kiệnExcel cung cấp bốn quy mô 2 màu cài đặt trước và bốn màu sắc 3 vảy presets, bạn có thể áp dụng cho các lựa chọn phạm vi bằng cách chọn Home ➪ phong cách ➪ ➪ định dạng có điều kiện màu vảy.Tùy chỉnh màu sắc và các tùy chọn, nhà chọn phong cách ➪ ➪ định dạng có điều kiện ➪ màu vảy ➪ nhiều quy tắc. Lệnh này sẽ hiển thị hộp thoại quy tắc định dạng mới, Hiển thị trong hình 20.8. Điều chỉnh các cài đặt, và xem hộp xem trước để xem hiệu ứng thay đổi của bạn.Sử dụng hộp thoại quy tắc mới định dạng tùy chỉnh quy mô màu. Một ví dụ cực màu quy môNó là quan trọng để hiểu rằng quy mô màu có điều kiện sử dụng định dạng một gradient. Ví dụ, nếu bạn định dạng một phạm vi bằng cách sử dụng một quy mô 2 màu sắc, bạn sẽ nhận được nhiều hơn hai màu sắc. Bạn sẽ nhận được màu sắc với độ dốc giữa hai màu được chỉ định.Con số 20.9 cho thấy một ví dụ cực sử dụng màu sắc quy mô có điều kiện định dạng trên một khoảng 10.000 tế bào (100 hàng x 100 cột). Bảng tính thu nhỏ xuống đến 20% để hiển thị một gradient three-color rất trơn tru. Dãy núi này có công thức như thế này, ở ô C5:=SIN($A2)+COS(B$1)Giá trị trong cột A và dòng 1 nằm trong khoảng từ 0 đến 4.0, trong từng bước của 0,04.Khi xem trên màn hình, kết quả là cảnh quan tuyệt đẹp; nó sẽ mất rất nhiều khi chuyển đổi sang màu xám.Trên đĩa CDBảng tính này, đặt tên là màu cực scale.xlsx, có sẵn trên CD-ROM. n đồng489 Phần III: Tạo bảng xếp hạng và đồ họaBảng này, sử dụng màu sắc quy mô định dạng có điều kiện, Hiển thị một

Kết quả (Việt) 2:

Phần III: Tạo Charts và đồ họa

New Feature
Các thanh dữ liệu tính năng được cải thiện đáng kể trong Excel 2010. Data Bar bây giờ hiển thị tương ứng (giống như một biểu đồ thanh), và bây giờ có một tùy chọn để hiển thị các thanh dữ liệu trong một màu (không nhiều màu gradient buộc) và với một biên giới. Ngoài ra, giá trị âm có thể hiển thị trong một màu sắc khác nhau, và bên trái của một trục. n

Một thanh dữ liệu đơn giản
Hình 20.4 cho thấy một ví dụ về thanh dữ liệu. Đó là một danh sách các bài hát trong album của Bob Dylan, với chiều dài của mỗi ca khúc trong cột D. Tôi áp dụng thanh định dạng dữ liệu có điều kiện để các giá trị trong cột
D. Bạn có thể biết trong nháy mắt mà theo dõi được lâu hơn.

Trên CD
Các ví dụ trong phần này đều có sẵn trên đĩa CD-ROM đồng. Các bảng tính được đặt tên là thanh dữ liệu examples.xlsx.


Chiều dài của các thanh dữ liệu là tỷ lệ thuận với chiều dài theo dõi trong các tế bào trong cột D.



Mẹo
Khi bạn điều chỉnh độ rộng cột, chiều dài thanh điều chỉnh cho phù hợp. Sự khác biệt giữa độ dài thanh là nổi bật hơn khi cột là rộng lớn hơn. n

Excel cung cấp truy cập nhanh đến 12 phong cách thanh dữ liệu thông qua Home ➪ Styles ➪ Conditional Formatting ➪ liệu Bars. Đối với lựa chọn bổ sung, bấm Quy tắc lựa chọn hơn, nó sẽ hiển thị New Formatting hộp thoại Rule. Sử dụng hộp thoại này để


486

Chương 20: Hình dung dữ liệu Sử dụng Conditional Formatting

. L Hiển thị thanh duy (ẩn số)
. L Chỉ định tối thiểu và giá trị tối đa cho các nhân rộng
. L Thay đổi sự xuất hiện của các thanh
l Chỉ rõ cách tiêu cực giá trị và các trục được xử lý.
l Xác định hướng của các thanh.

Lưu ý
kỳ lạ, màu sắc sử dụng cho các thanh dữ liệu không phải là màu sắc chủ đề. Nếu bạn áp dụng một chủ đề tài liệu mới, màu sắc thanh dữ liệu không thay đổi. n

Sử dụng thanh dữ liệu thay cho một biểu đồ
Sử dụng các dữ liệu thanh định dạng có điều kiện đôi khi có thể phục vụ như là một thay thế nhanh chóng để tạo ra một biểu đồ. Hình 20.5 cho thấy một bảng có ba cột của dữ liệu (được tạo ra bằng cách sử dụng Insert ➪ Bàn ➪ Table), với những thanh định dạng dữ liệu có điều kiện áp dụng trong cột thứ ba. Cột thứ ba của bảng chứa tham chiếu đến các giá trị trong cột thứ hai. Các định dạng có điều kiện trong umn col thứ ba sử dụng Hiện Bars Chỉ lựa chọn, vì vậy các giá trị không được hiển thị.


Bảng này sử dụng thanh định dạng dữ liệu có điều kiện.


Hình 20.6 cho thấy một biểu đồ thanh thực tế được tạo ra từ cùng một dữ liệu. Các biểu đồ thanh mất khoảng cùng một lượng thời gian để tạo ra và có rất nhiều linh hoạt hơn. Nhưng đối với một biểu đồ nhanh chóng-và-bẩn, thanh dữ liệu là một lựa chọn tốt -. Đặc biệt là khi bạn cần phải tạo ra nhiều bảng xếp hạng như

Sử dụng màu sắc quy mô
Các tùy chọn định dạng có điều kiện quy mô màu thay đổi màu nền của một tế bào dựa trên giá trị của tế bào, tương đối với các tế bào khác trong phạm vi.




487

Phần III: Tạo Charts và đồ họa


. một biểu đồ thanh Excel thực (không có điều kiện thanh dữ liệu định dạng)


một ví dụ thang màu
Hình 20.7 cho thấy một loạt các tế bào sử dụng thang màu dạng có điều kiện. Nó miêu tả num lượng nhân viên trên mỗi ngày trong năm. Đây là một quy mô 3 màu có sử dụng màu đỏ cho giá trị thấp nhất, vàng trung điểm, và màu xanh lá cây cho giá trị cao nhất. Giá trị ở giữa được hiển thị bằng cách sử dụng màu sắc trong gradient.


Một loạt sử dụng thang màu dạng có điều kiện.



Trên CD
bảng tính này, được đặt tên màu quy mô example.xlsx, có sẵn trên đĩa CD-ROM đồng. n


488

Chương 20: Hình dung dữ liệu Sử dụng Conditional Formatting

Excel cung cấp bốn mẫu thiết lập quy mô 2 màu và bốn 3 màu quy mô cài đặt trước, mà bạn có thể áp dụng cho vùng chọn bằng cách chọn Home ➪ Styles ➪ Conditional Formatting ➪ Màu Cân.

Để tùy chỉnh màu sắc và các tùy chọn khác, chọn Home ➪ Styles ➪ Conditional Formatting ➪ Màu Cân ➪ More Rules. Lệnh này sẽ hiển thị New Formatting hộp thoại Rule, thể hiện trong hình 20.8. Điều chỉnh các thiết lập, và xem các hộp Preview để xem ảnh hưởng của những thay đổi của bạn.


Sử dụng New Formatting hộp thoại Rule để tùy chỉnh một thang màu.



Một ví dụ thang màu cực kỳ
quan trọng là phải hiểu rằng quy mô màu dạng có điều kiện sử dụng một gradient. Ví dụ, nếu bạn định dạng một phạm vi sử dụng thang 2 màu, bạn sẽ nhận được nhiều hơn hai màu. Bạn sẽ có được màu sắc với gradient giữa hai màu nhất định.

Hình 20.9 cho thấy một ví dụ cực đoan sử dụng thang màu dạng có điều kiện trên một phạm vi 10.000 tế bào (100 hàng x 100 cột). Các bảng tính được thu nhỏ xuống 20% để hiển thị một màu ba màu rất mịn. Phạm vi chứa công thức như thế này, trong ô C5:

= SIN ($ A2) + COS (B $ 1)

. Các giá trị trong cột A và hàng 1 khoảng 0-4,0, trong số gia của 0.04

Khi xem trên màn hình, kết quả là tuyệt đẹp ; nó mất rất nhiều khi chuyển sang gam màu xám.

Trên CD
bảng tính này, được đặt tên scale.xlsx màu cực, có sẵn trên đĩa CD-ROM đồng. n



489

Phần III: Tạo Charts và đồ họa


bảng tính này, trong đó sử dụng thang màu dạng có điều kiện, sẽ hiển thị một