Biểu đồ động (Dynamic Chart)

[BNTT]

9.1. Biểu đồ động

Làm thế nào để tạo ra một biểu đồ từ một dãy dữ liệu mà kích thước dữ liệu trong đó thường xuyên thay đổi? Chẳng hạn như bạn cập nhật dữ liệu hằng tuần, và muốn biểu đồ của dữ liệu này cũng tự động mở rộng theo hằng tuần?

Loạt bài sau đây sẽ hướng dẫn cho bạn cách tạo ra những biểu đồ động như vậy:

1. Biểu đồ động dạng cột (Dynamic Column Chart)
2. Môt biểu đồ động khác (Another Dynamic Chart)
3. Biểu đồ động của 12 tháng vừa qua (Chart the Last 12 Months)
4. Biểu đồ động theo ngày (Dynamic Charting By Dates)
5. Vẽ biểu đồ động cho những dãy được quy chuẩn(Charting Dynamic Normalized Ranges)

​

 

[BNTT]

9.1.1. Biểu đồ động dạng cột (Dynamic Column Chart)

Giả sử tôi có bảng dữ liệu như sau đây, và đã tạo một biểu đồ dựa theo đó. Cho dù chẳng có số liệu nào trong tuần thứ 5 và thứ 6, nhãn của nó vẫn có trong trục x, bởi vì dãy nguồn dữ liệu của biểu đồ này là $A$1:$B$7. Có thể bạn không thích như vậy, bạn muốn biểu đổ chỉ hiển thị những mục có dữ liệu thôi, còn những mục chưa có dữ liệu (Week5 và Week6 trong ví dụ) thì ẩn đi; hoặc là khi bạn xóa dữ liệu của mục nào, thì mục đó biến khỏi biểu đồ. Phải làm sao?

Để giới hạn vùng dữ liệu cho biểu đồ, và làm cho nó tự động điều chỉnh kích thước, bạn có thể tạo ra các Name động.

Từ menu Insert, bạn chọn Name, rồi chọn Define. Hộp thoại sau đây sẽ mở ra:

Trong hộp Names in Workbook, nhập tên cho dãy, bắt đầu với SheetName, ví dụ: Sheet1!ChartValues

Excel 2007 sẽ báo lỗi nếu bạn bắt đầu một tên với từ "Chart", bạn hãy dùng tên khác, ChtValues chẳng hạn.​

Trong hộp Refers to, dùng hàm OFFSET để tạo một dãy có một cột, với số hàng bằng số ô có dữ liệu trong cột B trừ bớt đi 1 (bỏ đi cái Column Heading). Chẳng hạn như công thức sau đây:

=OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)

Tiếp theo, tạo Name động cho các nhãn của biểu đồ. Từ menu Insert, bạn chọn Name, rồi chọn Define. Trong hộp Names in Workbook, nhập tên cho dãy, bắt đầu với SheetName, ví dụ: Sheet1!ChartLabels

Excel 2007 sẽ báo lỗi nếu bạn bắt đầu một tên với từ "Chart", bạn hãy dùng tên khác, ChtLabels chẳng hạn.​

Trong hộp Refers to, dùng hàm OFFSET để tạo một dãy có một cột nằm ở bên trái của dãy ChartValues. Chẳng hạn như công thức sau đây:

=OFFSET(ChartValues,0,-1)

Cuối cùng, bạn thay đổi dữ liệu nguồn cho biểu đồ: Nhấn vào các cột trong biểu đồ và chọn nó, rồi thay đổi các tham chiếu trong công thức trên thanh Formula bằng các Name động mới tạo.

Công thức trong ví dụ trên là:

=SERIES(Sheet1!$B$1,Sheet1!ChartLabels,Sheet1!ChartValues,1)

Biểu đồ sẽ tự động điều chỉnh để chỉ hiển thị những dữ liệu đang có (của cột B), và các nhãn của nó.

Đó là biểu đồ động dạng cột của bạn, theo cách bạn muốn. Bạn thử nhập thêm hoặc xóa bớt dữ liệu vào trong cột B để thấy thành quả của mình.​

 

[BNTT]

9.1.2. Một Biểu đồ động khác (Another Dynamic Chart)

Chúng ta sẽ bắt đầu với dữ liệu sau đây, trong một Sheet có tên là "How To":

Yêu cầu trong bài này là tạo ra một biểu đồ có thể tự thay đổi theo các giá trị trong cột A và B, có thể bắt đầu tại một điểm tùy ý (First Point) và kết thúc tại một điểm tùy ý (Last Point). Ví dụ, theo như bảng tính mà bạn đang thấy, người ta muốn tạo một biểu đồ bắt đầu từ giá tri thứ 4 và kết thúc tại giá trị thứ 16, chứ không phải là một biểu đồ bình thường bắt đầu từ 1 cho đến giá trị cuối cùng (17).


1. Trước tiên, chúng ta hãy tạo những dãy động:

Nhấn Ctrl+F3 hoặc gọi Define từ menu Insert, Name. Ở đây tôi đã tạo một Name myXvalues và tạm thời chọn tham chiếu đến dãy A1:A18 bằng chuột. Thay vì dùng chuột, bạn có nhập trực tiếp tham chiếu của bạn vào.

Phụ thuộc vào yêu cầu của bạn, định nghĩa cho myXvalues có thể giống như sau:

=OFFSET('How To'!$A$2, 0, 0, COUNTA('How To'!$A:$A) - 1, 1)

Có nghĩa là bắt đầu từ ô A2 và đứng yên tại đó, tạo ra một dãy có một cột, với số hàng bằng kết quả của COUNTA('How To'!$A:$A)-1. Hàm COUNTA đếm tất cả những ô không rỗng trong một dãy cho trước, và chúng ta trừ bớt đi 1 để không tính ô tiêu đề. Hàm COUNT thì đếm tất cả những ô chứa giá trị số trong dãy, chúng ta cũng có thể dùng nó, nếu chắc chắn rằng trong dãy của chúng ta không có giá trị nào không phải là giá trị số. Khi có thêm dữ liệu nhập vào trong dãy này, kết quả của COUNTA tăng lên, và biểu đồ biểu diễn cho dãy này cũng sẽ thay đổi tương ứng.

Và chúng ta sẽ tạo thêm một dãy ChartX, với định nghĩa như sau:

=OFFSET('How To'!$A$1, 'How To'!$C$2,0, 'How To'!$D$2 + 1 - 'How To'!$C$2, 1)

Có nghĩa là từ ô A1, dời xuống 4 hàng (hoặc là giá trị nào khác của C2), dời sang phải 0 cột, và dãy tạo ra bao gồm 13 hàng (hoặc bằng giá trị nào khác trong D2 trừ đi giá trị trong C2 rồi cộng thêm 1) với 1 cột.

Để tạo dãy myYvalues và ChartY thì dễ hơn nhiều:

=OFFSET(myXvalues, 0, 1)

=OFFSET(ChartX, 0, 1)

Nghĩa là những dãy này giống như dãy có tên trong công thức và nằm ngay sát bên phải của dãy đó (từ dãy đó dời sang 1 cột).​

2. Tiếp theo, chúng ta tạo biểu đồ với bất kỳ dữ liệu nào hiện có, ví dụ như A1:B18:

Nhấn chuột vào đường biểu diễn, bạn sẽ thấy công thức mô tả cho đường biểu diễn này trên thanh formula như sau:

=SERIES('How To'!$B$1, 'How To'!$A$2:$A$18, 'How To'!$B$2:$B$18, 1)

Công thức này có nghĩa như sau: tên của đường biểu diễn là giá trị tại ô B1, giá trị cho trục X là A2:A18, giá trị cho trục Y là B2:B18, và nó bắt đầu với giá trị thứ nhất.

Biểu đồ này không có gì sai, nhưng chưa phải là cái chúng ta muốn.​

(Còn tiếp)​

 

[BNTT]

Một Biểu đồ động khác (tt)

3. Sửa lại công thức cho đường biểu diễn này.

Bạn nhấp chuột vào công thức trên thanh formula và sửa lại như sau:

=SERIES('How To'!$B$1, 'How To'!myXvalues, 'How To'!myYvalues, 1)

Sau khi bạn nhấn Enter, Excel có thể sẽ thay đổi công thức như sau đây, nhưng không làm cho nó thay đổi kết quả:

=SERIES('How To'!$B$1, DynamicCharts.xls!myXvalues, DynamicCharts.xls!myYvalues, 1)

Lúc này, biểu đồ sẽ vẫn y như cái bạn đang có, tuy nhiên nó sẽ tự động thay đổi nếu bạn thêm hoặc bớt các giá trị trong cột A và cột B.

Tốt hơn rồi! Nhưng mà nó vẫn bắt đầu tại giá trị thứ nhất, chưa bị ảnh hưởng bởi các ô First Point và Last Point.

Bạn chịu khó thay đổi công thức thêm lần nữa, như sau đây:

=SERIES(, DynamicCharts.xls!chartX, DynamicCharts.xls!chartY, 1)

Bạn để ý, có một cái dấu phẩy ngay sau dấu mở ngoặc đơn cho hàm SERIES, điều này có nghĩa biểu đồ của chúng ta không có tên tuổi gì hết.

Xong rồi đó. Tôi sẽ vẽ cho bạn xem 4 dạng khác nhau của biểu đồ này. Bạn cứ thử thêm bớt các giá trị trong cột A và B, thay đổi giá trị trong C2 (First Point) và D2 (Last Point)... bạn sẽ thấy công sức của chúng ta bỏ ra nãy giờ, kể cũng đáng!

​

 

[BNTT]

9.1.3. Biểu đồ động của 12 tháng vừa qua (Chart the Last 12 Months)

Bạn muốn tạo một biểu đồ minh họa dữ liệu của 12 tháng vừa qua. Nhưng làm thế nào để tạo một biểu đồ từ những tháng vừa qua trong một dãy đang còn tiếp tục, nghĩa là sẽ có thêm tháng này và những tháng sắp tới, mà không phải cập nhật dữ liệu nguồn cho biểu đồ hằng tháng? Và bất kỳ lúc nào, biểu đồ cũng chỉ hiển thị 12 tháng gần nhất? Bài này là một ví dụ minh họa cách tạo một biểu đồ động, tự động cập nhật.

Chúng ta sẽ bắt đầu với dữ liệu sau đây, trong một Sheet có tên là Sheet1:

Bạn có thể chọn dữ liệu cách bình thường và tạo một biểu đồ bằng công cụ Chart Wizard. Nhưng biểu đồ đó sẽ không phải là một biểu đồ động, và bạn phải bảo trì nó hằng tháng.

Để tạo một biểu đồ động, trước hết bạn phải định nghĩa một số Name động. Nhấn Ctrl+F3 (hoặc gọi Define... từ menu Insert, Names) để mở hộp thoại Define Name. Trong hộp Name, bạn gõ chtLen, và trong hộp Referts to bạn nhập vào =Sheet1!$D$2. Mảng có tên là chtLen sẽ quyết định độ dài của biểu đồ luôn bằng giá trị trong ô D2 (=12). Nhấn Add.

Nhập tiếp chtCats trong hộp Name. Trong hộp Referts to, nhập công thức sau đây rồi nhấn Add. Thay vì phải nhập một công thức dài, bạn có thể copy nó ở đây và dán vào hộp Refers to với phím tắt Ctrl+V:

=OFFSET(Sheet1!$A$1, COUNTA(Sheet1!$A:$A) - 1, 0, -MIN(chtLen, COUNTA(Sheet1!$A:$A) - 1), 1)

Công thức này định nghĩa một mảng trong cột A luôn luôn chứa 12 tháng sau cùng.

Nhập tiếp chtValA trong hộp Name, rồi nhập (hoặc Copy-Paste) =OFFSET(chtCats, 0, 1) vào hộp Referts to, và nhấn Add. Nhập chtValB trong hộp Name, rồi nhập =OFFSET(chtCats, 0, 2) vào hộp Referts to, và nhấn Add. Đây là những giá trị trong mỗi tháng mà chúng ta muốn theo dõi.

Cuối cùng, nhấn OK để đóng hộp thoại Define Name.

Nào, bây giờ bạn hãy chọn một ô trống ở bất kỳ đâu và bắt đầu tạo biểu đồ với công cụ Chart Wizard. Ở bước 1, bạn chọn một loại biểu đồ (trong ví dụ này tôi chọn loại Clustered Column). Sang bước 2, bạn nhấn vào tab Series, rồi nhấn Add để thêm series thứ nhất. Nhấp chuột vào hộp Name, rồi chọn ô B1 (nó sẽ hiển thị là =Sheet1!$B$1). Sửa lại giá trị đang có trong hộp Values là =Sheet1!chtValA. Nhấn hộp Category (X) asix Labels và nhập vào =Sheet1!chtCats.

Nhấn Add để thêm series thứ hai cho biểu đồ. Tương tự như vừa rồi, bạn nhấp chuột vào hộp Name, rồi chọn ô C1. Sửa lại giá trị đang có trong hộp Values là =Sheet1!chtValB. Nhấn hộp Category (X) asix Labels và nhập vào =Sheet1!chtCats.

Bước 3 và bước 4 của Chart Wizard chỉ là để tô điểm thêm cho biểu đồ. Bạn có thể nhấn Finish ngay khi xong bước 2. Kết quả bạn có là một bức tranh dữ liệu của bất kỳ 12 tháng nào:

Bây giờ chúng ta thêm 6 tháng nữa. Bạn nhập thêm những dữ liệu như sau đây nối vào dữ liệu đang có:

Biểu đồ tự động cập nhật mà không cần sự can thiệp của bạn. Nó minh họa cho 12 tháng sau cùng của dữ liệu, từ tháng 7/2002 đến tháng 6/2003:

Có một cái mẹo trong định nghĩa Name của chtCats nhằm tránh lỗi khi dữ liệu của chúng ta có ít hơn 12 tháng. Nếu chúng ta chắc chắn rằng dữ liệu luôn luôn có ít nhất 12 tháng (hay là số hàng dữ liệu luôn bằng giá trị của chtLen), chúng ta có thể định nghĩa cho chtCats như sau:

=OFFSET(Sheet1!$A$1, COUNTA(Sheet1!$A:$A) - 1, 0, -chtLen, 1)

Nhưng nếu dãy dữ liệu của chúng ta ngắn hơn, OFFSET() sẽ tham chiếu đến những hàng trước hàng 1, và đương nhiên là báo lỗi! Biểu thức MIN(chtLen, COUNTA(Sheet1!$A:$A) - 1) sẽ luôn lấy số hàng của dữ liệu nếu như nó nhỏ hơn giá trị của chtLen:

=OFFSET(Sheet1!$A$1, COUNTA(Sheet1!$A:$A) - 1, 0, -MIN(chtLen, COUNTA(Sheet1!$A:$A) - 1), 1)

Bạn thử xóa bớt dữ liệu, chỉ giữ lại 9 tháng, phạm vi của biểu đồ sẽ tự động thu ngắn lại mà không dư ra một lỗ hổng nào.

​

 

[BNTT]

9.1.4. Biểu đồ động theo ngày (Dynamic Charting By Dates)

Bài này mô tả những biểu đồ động cho phép người sử dụng có thể tùy ý thay đổi các giá trị ngày tháng trong biểu đồ.

Bạn đã biết rằng, biểu đồ động là một biểu đồ mà khi dữ liệu nguồn thay đổi, thì những điểm trong biểu đồ cũng tự động thay đổi tương ứng. Bài này trình bày một loại biểu đồ động tham chiếu đến những biểu đồ có dãy dữ liệu tự động điều chỉnh đề phù hợp với những thay đổi của các tham số. Dãy dữ liệu trong trường hợp này có thể giãn ra hay co lại, di chuyển qua lại trong bảng tính, thậm chí bỏ qua một số ô giữa những điểm nào đó. "Bộ máy" điều khiển những biểu đồ động kiểu này chính là các tên dãy (named range) của Excel.

Bài này sẽ khảo sát ba cấp độ của biểu đồ động, bao gồm những khó khăn và những chức năng sau:

• Một biểu đồ động đơn giản dựa trên những ngày bắt đầu và kết thúc đã được nhập trong bảng tính.
  
  
• Một biểu đồ động sử dụng thanh trượt để xác định những ngày bắt đầu và kết thúc.
  
  
• Một biểu đồ động sử dụng thanh trượt để xác định điểm kết thúc và những nút để lựa chọn khoảng cách cho các điểm dữ liệu.

​

1. Một biểu đồ động đơn giản

Chúng ta sẽ bắt đầu với một dạng biểu đồ động đơn giản: tự động thay đổi dựa theo những ngày tháng được nhập vào. Một phần của dữ liệu ngày tháng này được minh họa sau đây, trong một WorkSheet có tên là "Data" (nếu muốn xem đầy đủ dữ liệu và để tự thực hành, bạn tải về file ví dụ của bài này ở đây)

Đầu tiên, chúng ta sẽ định nghĩa một số tên dãy:

Ô A1 và A2 chứa ngày bắt đầu và ngày kết thúc để vẽ biểu đồ. Ô A1 được đặt tên là StartDate và ô A2 được đặt tên là EndDate. Dãy dữ liệu chứa toàn bộ những giá trị ngày tháng trong trường hợp này là A5:A63, được đặt tên là AllDates. Tuy nhiên, thay vì khóa cứng ngắt AllDates trong dãy A5:A63, bạn có thể dùng một Name động cho nó để sau này tùy ý thêm dữ liệu vào trong cột. Trong hộp thoại Define Name, bạn nhập AllDates trong ô Name và nhập công thức sau đây trong ô Refers to:

=Data!$A$5:INDEX(Data!$A:$A, MATCH(9.99999E+307, Data!$A:$A))

Bây giờ là lúc chúng ta đặt các tên để sử dụng ngày tháng được nhập vào. Nhấn Ctrl+F3 để mở hộp thoại Define Name. Nhập ChartDates trong hộp Name, và trong hộp Refers to nhập công thức sau đây:

=OFFSET(AllDates, MATCH(StartDate, AllDates, 1) - 1, 0, MATCH(EndDate, AllDates, 1) - MATCH(StartDate, AllDates, 1) + 1, 1)

Biểu thức MATCH(StartDate, AllDates, 1) chỉ ra vị trí của ngày bắt đầu trong dãy các ngày, và MATCH(EndDate, AllDates, 1) chỉ ra vị trí của ngày kết thúc trong dãy đó. Do đó, ChartDates sẽ là một tập hợp con của AllDates, nằm trong khoảng ngày bắt đầu và ngày kết thúc. ChartDates sẽ cung cấp giá trị cho trục X.

Bạn đặt thêm các tên sau đây cho các giá trị của trục Y trên biểu đồ:

ChartFirmA = OFFSET(ChartDates, 0, 1)

ChartFirmB = OFFSET(ChartDates, 0, 2)

ChartFirmC = OFFSET(ChartDates, 0, 3)

ChartFirmD = OFFSET(ChartDates, 0, 4)​

Tiếp theo, chúng ta xây dựng biểu đồ. Chọn một ô trống, và gọi chức năng Chart Wizard ra. Ở bước 1, chọn kiểu biểu đồ là Line Chart. Sang bước 2, chọn tab Series. Nhấn nút Add, nhấn vào hộp Name và chọn ô B4, là ô đang có chữ "Firm A". Nhấn vào hộp Values, gõ =Data!ChartFirmA. Cuối cùng, nhấn vào hộp Category (X) Axis Labels và gõ =Data!ChartDates.

Nhấn tiếp nút Add, nhấn vào hộp Name và chọn ô C4 (Firm B). Nhấn hộp Values, gõ =Data!ChartFirmB, nhấn vào hộp Category (X) Axis Labels và gõ =Data!ChartDates. Trong trường hợp bạn quên không gõ kèm tên Sheet vào trước tham chiếu, Excel sẽ làm chuyện đó dùm bạn.

Tiếp tục như các bước trên để tạo serie thứ ba (C) và thứ 4 (D) cho biểu đồ. Sau khi đã đủ 4 cái, nhấn vào Next và nhấn Finish sau khi bạn đã quy định thêm một vài định dạng cho biểu đồ.

Kết quả của chúng ta là một biểu đồ sẽ tự động thay đổi các điểm giá trị khi ngày tháng nhập vào có thay đổi.

(Còn tiếp)

 

[BNTT]

Biểu đồ động theo ngày (tt)


2. Thêm thanh trượt để điều chỉnh vùng chọn

Việc xác định dãy dữ liệu đầu vào cho đồ thị sẽ trở nên dễ dàng hơn nếu bạn thêm một vài công cụ điều khiển (Control) vào trong bảng tính.

Chúng ta bắt đầu với bảng tính vừa làm xong ở trên, chèn thêm hai thanh trượt (slider scrollbar) từ menu Forms: Nhấn nút phải chuột vào đâu đó trên khu vực các menu và chọn Forms, nếu toolbar này chưa được kích hoạt. Chọn Scrollbar và vẽ một hình chữ nhật ở chỗ mà bạn muốn có thanh trượt trong bảng tính.

Làm thêm một thanh trượt thứ hai, bằng cách nhấn Ctrl và rê thanh trượt thứ nhất đi, hoặc bạn có thể copy rồi paste. Trong bày này, vì muốn dùng thanh trượt để xác định StartDate và EndDate nên tôi sẽ để hai thanh trượt nằm ngang hàng với các ô này, ở hàng thứ 1 và hàng thứ 2, bắt đầu từ cột C.

Nhấp phải vào thanh trượt mới tạo rồi chọn Format control để định dạng nó. Tôi sẽ đặt Cell link lần lượt cho hai thanh trượt này là B1 và B2, đồng thời cũng thiết lập các thông số cho cả hai như sau: Minimum = 1, Maximum = 59 (cho tất cả các ngày của tháng 1 và tháng 2 năm 2003), Incremental Change = 1 (khi nhấn vào những mũi tên ở hai đầu thanh trượt, giá trị sẽ tăng/giảm 1 đơn vị), và Page Change = 10 (khi nhấn vào khoảng giữa trong thanh trượt, giá trị sẽ tăng/giảm 10 đơn vị).

Tại ô A1, tôi lập công thức: =INDEX(AllDates, MIN(B1:B2))

Và công thức này ở A2: =INDEX(AllDates, MAX(B1:B2))

Tôi dùng MIN() và MAX() để bảo đảm rằng A1 luôn luôn là ngày sớm hơn và A2 là ngày trễ hơn.

Những phần còn lại như định nghĩa tên cho các dãy, kiểu dáng biểu đồ, tôi để nguyên như đã làm ở bài trước. Phần trên của bảng tính bây giờ sẽ giống như sau:

Bạn thử nhấn vào hai cái thanh trượt này và nhìn vào biểu đồ, bạn sẽ thấy kết quả của những việc chúng ta làm nãy giờ.​

(Còn tiếp)

 

[BNTT]

Biểu đồ động theo ngày (tt)


3. Thêm các nút chọn để xác định các khoảng thời gian

Trong bài vừa rồi, bạn đã có những thanh trượt để xác định ngày bắt đầu và ngày kết thúc cho dữ liệu đầu vào của biểu đồ. Bây giờ chúng ta sẽ sử dụng thêm những nút lựa chọn (Option button) để cho người sử dụng có thể chọn xem đồ thị theo ngày, theo tuần, hay theo tháng, v.v...:

Tôi sẽ vẽ thêm 6 Option Button vào bảng tính và lần lượt đặt tên cho chúng như sau: "Daily" (hằng ngày), "Weekly" (hằng tuần), "Monthly" (hằng tháng), "Quarterly" (hằng quý), "Semiannually" (mỗi 6 tháng), and "Annually" (hằng năm). Việc đặt tên cho các nút này rất quan trọng, bởi vì nó ảnh hưởng đến giá trị trong ô liên kết (Cell link) của chúng. Tôi đặt Cell link cho cả 6 nút này tại G1. Tất cả các Option button trong một Worksheet đều có cùng một Cell link, ngay cả khi mỗi lần chỉ có một nút được chọn. Và ô Cell link này chỉ đơn giản là trả về số thứ tự của mỗi nút. (Vì quy định này, mà bạn chỉ cần đặt Cell link cho một nút, các nút còn lại trong Worksheet sẽ tự động có cùng Cell link).

Tôi dời bảng dữ liệu ngày tháng xuống dưới một vài hàng để lấy chỗ cho các Option button. Bạn có thể sắp xếp lại vị trí của các nút cho đẹp, tô nền cho chúng để không thấy các gridline của bảng tính, và vẽ một cái khung bao quanh chúng. Mô hình của chúng ta đại khái giống như hình sau đây:

Như đã làm ở bài trước, ô A1 có tên là StartDay, và chứa công thức: =INDEX(AllDates, MIN(B1:B2))

Ô A2 có tên là EndDay và có công thức là: =INDEX(AllDates, MAX(B1:B2))

Bỏ qua cột F để tránh nhầm lẫn giữa dữ liệu thô ban đầu và dữ liệu đã được tính toán cho biểu đồ, tôi sẽ thiết lập một bản sao của dữ liệu thô nằm song song với nó từ cột G đến cột K:

Ô G1 là Cell link cho các Option button đã nói ở trên, dãy G2:L4 bao gồm những phép tính cần thiết để thay đổi khoảng cách giữa các dữ liệu, như sau đây:

• Khi nút số 1 (Daily) được chọn, G2:G4 là mảng liên quan (cung cấp các giá trị cho 3 mảng đã được đặt tên là DayIncr, MonthIncr, và YearIncr như tôi sẽ trình bày dưới đây). Mảng này sẽ bao gồm 3 giá trị {1,0,0}, làm tăng giá trị của mỗi ngày lên 1 ngày, 0 tháng và 0 năm tính từ ngày trước đó.
• Khi nút số 2 (Weekly) được chọn, H2:H4 là mảng liên quan, bao gồm 3 giá trị {7,0,0}, làm tăng giá trị của mỗi ngày lên 7 ngày, 0 tháng và 0 năm tính từ ngày trước đó.
• Khi nút số 3 (Monthly) được chọn, I2:I4 là mảng liên quan, bao gồm 3 giá trị {0,1,0}, làm tăng giá trị của mỗi ngày lên 0 ngày, 1 tháng và 0 năm tính từ ngày trước đó.
• v.v...

Trước khi vẽ biểu đồ, chúng ta phải đặt tên cho một mảng chứa tất cả các ngày tháng từ dữ liệu nguồn (trong trường hợp này là dãy A7:A65, và đã đặt tên là AllDays), mảng này có tên là AllData, đồng thời định nghĩa thêm một số tên dãy sau để xử lý giải thuật tính khoảng cách nhảy cho ngày tháng (incrementing) :

AllData: = OFFSET(AllDates, , , , 5)

DayIncr: = INDEX(Data!$G$2:$L$2, Data!$G$1)

MonthIncr: = INDEX(Data!$G$3:$L$3, Data!$G$1)

YearIncr: = INDEX(Data!$G$4:$L$4, Data!$G$1)​

Tiếp tục, tôi sao chép dãy tiêu đề ở B6:F6 sang H6:K6, và nhập công thức sau đây ở G7: = StartDay

Chọn ô H8, nhấn Ctrl+F3 để tạo một Name là NewDate với định nghĩa như sau:

=DATE(YEAR(Data!$G7) + YearIncr, MONTH(Data!$G7) + MonthIncr, DAY(Data!$G7) + DayIncr)​

Đây là một công thức "ma thuật", xác định một giá trị mới cho ngày tháng từ giá trị ban đầu, dựa vào việc nhấn nút nào trong 6 Option button đã tạo ra.

Chọn dãy G8:G65 và điền công thức sau:

=IF(NewDate > EndDate, NA(), NewDate)​

Công thức sẽ điền vào một dãy ngày, bắt đầu từ ngày tiếp sau ngày ở ô G7, và kết thúc bằng EndDate. Sau ô chứa EndDate, những ô còn lại sẽ được điền bằng lỗi #NA. Bạn có thể thử lại công thức bằng việc nhấn các nút chọn, ví dụ, với nút chọn là Weekly, các ngày trong cột dãy G7:G65 này phải cách nhau mỗi 7 ngày.

Bây giờ ta chọn tiếp ô H7 và nhập công thức sau:

=IF($G7 > EndDate, NA(), VLOOKUP($G7, AllData, COLUMN() + 1 - COLUMN($G7)))​

Kéo công thức này sang phải để sao chép vào dãy H7:K7, rồi kéo nó xuống tiếp cho đến hết hàng 65 (nghĩa là điền công thức trên vào dãy H7:K65). Khi ở cột G có một ngày hợp lệ, công thức này trả về một giá trị tương ứng của ngày đó, còn nếu ở cột G là một lỗi #NA, thì giá trị trong các ô bên dãy này cũng là #NA.

Cuối cùng, bạn định nghĩa thêm một tên dãy NewDates, đồng thời sửa lại định nghĩa cho ChartDates như sau đây:

NewDates: = OFFSET(AllDates, , 6)

ChartDates: = OFFSET(NewDates, 0, 0, COUNT(NewDates) ,1)​

Hình sau đây minh họa một phần bảng tính và biểu đồ, khi chọn StartDay = 3, EndDay = 28, Option button là Weekly:

____

Nếu bạn thực hành chính xác như tôi đã hướng dẫn, bạn hãy nắm thanh trượt kéo qua, kéo lại, chọn lung tung trong 6 cái Option button, bạn sẽ thấy biểu đồ của bạn nhảy múa rất đẹp!
​

 

[BNTT]

9.1.5. Vẽ biểu đồ động cho những dãy được quy chuẩn (Charting Dynamic Normalized Ranges)

Có khi nào bạn ước mơ vẽ được một biểu đồ mà gái trị của các đường biểu diễn của nó được quy về một điểm chuẩn, chẳng hạn như 100 ? Chẳng hạn bạn cần so sánh sự biến động của giá cổ phiếu dựa trên một cơ sở tương đối nào đó? Bài này sẽ hướng dẫn bạn cách sử dụng các mảng động và các biểu đồ động để hiển thị những giá trị được quy chuẩn.​

1. Dữ liệu

Bạn xem bảng dữ liệu mẫu sau, cột A là các ngày trong một tháng, cột B và cột C chứa các giá trị tương ứng với từng ngày.

Để vẽ một biểu đồ động, ta sẽ tạo một mảng dữ liệu động từ những dữ liệu đã có, với tham số cho điểm khởi đầu của mảng này là giá trị ở ô E2, và tham số cho tổng số điểm trong mảng này là giá trị ở ô F2.​

2. Định nghĩa các tên

Một "Name" chính là một biến trong Excel. Một Name có hai phần: tên của Name và công thức mà định nghĩa cho Name tham chiếu đến. Đôi khi, Name còn được gọi là Named Range (dãy được đặt tên), nhưng chính xác hơn, thì phải gọi là Named Formula (công thức được đặt tên).

Nếu tôi muốn một Name tham chiếu đến một hằng số, tôi sẽ đặt cho nó một cái tên chẳng hạn như Two và tham chiếu đến công thức =2. Nếu tôi muốn một Name khác tham chiếu đến một dãy, tôi đặt tên là MyRange và gán cho nó công thức =Sheet1!$A$1:$C$10. Các Name có thể phức tạp hơn thế nhiều, chúng phối hợp các công thức và cho ra bất kỳ một kết quả nào. Ví dụ, các Name sẽ định nghĩa sau đây phối hợp hàm INDEX và OFFSET.

Tên của Name có thể là ký tự, là số, và một vài ký hiệu nào đó, nhưng bạn nên hạn chế sử dụng dấu gạch chân (_) và các dấu chấm câu (tôi thì không bao giờ sử dụng dấu chấm câu); và tên cho Name phải bắt đầu bằng một ký tự hoặc một dấu gạch chân. Thường thì, với các Name tham chiếu đến một dãy, tôi sử dụng một tiền tố "rng"; để chỉ ra các dãy sử dụng cho biểu đồ, tôi dùng tiền tố "cht". Sử dụng những quy ước sẽ giúp bạn dễ nhớ các Name cho dù là vài tháng sau mới thấy lại chúng. Ví dụ, trong bài này, tôi sử dụng "rngSelectedValue1Norm" để tham chiếu đến những giá trị quy chuẩn được chọn cho series 1.

Sau đây là các tên được sử dụng trong bài này:

rngDates: = OFFSET(DataSheet!$A$1,1,0,COUNT(DataSheet!$A:$A),1)

rngValue1: = OFFSET(rngDates,,1)

rngValue2: =OFFSET(rngDates,,2)

rngStart: = DataSheet!$E$2

rngLength: = DataSheet!$F$2

rngSelectedDates: =INDEX(rngDates,rngStart):INDEX(rngDates,rngStart+rngLength-1)

rngSelectedValue1: =OFFSET(rngSelectedDates,,1)

rngSelectedValue2: =OFFSET(rngSelectedDates,,2)

rngSelectedValue1Norm: =rngSelectedValue1/INDEX(rngSelectedValue1,1)*100

rngSelectedValue2Norm: =rngSelectedValue2/INDEX(rngSelectedValue2,1)*100​

(Còn tiếp)

 

[BNTT]

Vẽ biểu đồ động cho những dãy được quy chuẩn (tt)


3. Tạo các biểu đồ động

• Một biểu đồ tĩnh chứa tất cả các dữ liệu
  
  Biểu đồ này được tạo ra bằng cách chọn dãy A1:C32, và chọn loại Line Chart.
  
  
  
  
  Công thức cho hai serie của biểu đồ này như sau đây, chúng bao gồm các liên kết cố định tới các dãy tĩnh trong bảng tính.
  
  
  =SERIES(DataSheet!$B$1,DataSheet!$A$2:$A$32,DataSheet!$B$2:$B$32,1)
  
  =SERIES(DataSheet!$C$1,DataSheet!$A$2:$A$32,DataSheet!$C$2:$C$32,2)​
  
  
  
• Một biểu đồ động chứa tất cả các dữ liệu
  
  Để chuyển mỗi serie trên biểu đồ trên thành serie động, bạn thay đổi tham chiếu đến các dãy cố định trong công thức (ví dụ: $A$2:$A$32 hoặc $B$2:$B$32) thành tham chiếu đến các Name (rngDates hoặc rngValue1). Với tôi, cách dễ nhất để làm việc này là chọn các serie, và sửa trực tiếp công thức của nó trên thanh công thức. Tuy nhiên, bạn cũng có thể làm bằng cách chọn Source Data từ menu Chart, nhấn vào tab Serie, và sửa lại tham chiếu trong các text box của hộp thoại. Công thức của 2 seire trong biểu đồ trên sẽ được sửa lại như sau:
  
  
  =SERIES(DataSheet!$B$1,DataSheet!rngDates,DataSheet!rngValue1,1)
  
  =SERIES(DataSheet!$C$1,DataSheet!rngDates,DataSheet!rngValue2,2)​
  
  Bởi vì các Name này đã định nghĩa cho toàn bộ bảng tính, nên sau khi bạn nhấn Enter kết thúc việc sửa công thức, Excel sẽ tự động đổi tham chiếu đến Worksheet (DataSheet) thành tham chiếu đến Workbook (Normalized.xls):
  
  
  =SERIES(DataSheet!$B$1,Normalized.xls!rngDates,Normalized.xls!rngValue1,1)
  
  =SERIES(DataSheet!$C$1,Normalized.xls!rngDates,Normalized.xls!rngValue2,2)​
  
  Biểu đồ của chúng ta sẽ như sau đây:
  
  
  
  
  Biểu đồ mới này chẳng có gì khác với biểu đồ đang có? Đúng là thoạt nhìn thì nó chẳng có gì khác. Nhưng bạn thử thêm bớt dữ liệu trong các cột A, B, và C thử xem...

(còn tiếp)

 

[BNTT]

Vẽ biểu đồ động cho những dãy được quy chuẩn (tt)


3. Tạo các biểu đồ động (tiếp theo)

• Một biểu đồ động chứa một phần dữ liệu
  
  Để làm cho biểu đồ động ở trên chỉ chứa một phần dữ liệu đã định nghĩa bởi rngStart và rngLength, bạn chỉ cần đổi các Name trong công thức của các serie (rngDates, rngValue1) thành các Name tham chiếu đến những dãy được chọn (rngSelectedDates, rngSelectedValue1):
  
  
  =SERIES(DataSheet!$B$1,Normalized.xls!rngSelectedDates,Normalized.xls!rngSelectedValue1,1)
  
  =SERIES(DataSheet!$C$1,Normalized.xls!rngSelectedDates,Normalized.xls!rngSelectedValue2,2)​
  
  Biểu đồ của chúng ta bây giờ sẽ giống như sau:
  
  
  
  
  
  
• Một biểu đồ động chứa một phần dữ liệu, đã được quy chuẩn về 100 tại điểm bắt đầu
  
  Cuối cùng, để hiển các giá trị đã được quy chuẩn trên biểu đồ, một lần nữa bạn đổi các Name định nghĩa cho các giá trị được chọn (rngSelectedValue1 và rngSelectedValue2) thành các Name của các giá trị đã quy chuẩn (rngSelectedValue1Norm và rngSelectedValue2Norm):
  
  
  =SERIES(DataSheet!$B$1,Normalized.xls!rngSelectedDates,Normalized.xls!rngSelectedValue1Norm,1)
  
  =SERIES(DataSheet!$C$1,Normalized.xls!rngSelectedDates,Normalized.xls!rngSelectedValue2Norm,2)​
  
  
  
  
  Biểu đồ trên đây minh họa cho dữ liệu từ điểm khởi đầu là 5 và chứa 12 giá trị. Khi hai giá trị tại E2 và F2 là 13 và 19 (bắt đầu tại giá trị thứ 13 trong dữ liệu gốc, và vẽ 19 giá trị trong biểu đồ), biểu đồ của chúng ta sẽ tự động cập nhật như thế này: