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!