Nora đã giữ một bản ghi thời tiết trong nhiều năm. Mỗi trang của sách bài tập là một năm riêng biệt, với cột A trên mỗi trang là ngày tháng trong năm và cột B là lượng mưa trong ngày đó, nếu có. Nora muốn tạo một biểu đồ hiển thị lượng mưa năm nay so với lượng mưa năm ngoái, cho đến nay. Cô ấy tự hỏi liệu có cách nào để biểu đồ này tự động tham chiếu các giá trị lượng mưa chính xác cho cả hai năm dựa trên ngày hôm nay hay không.

Có một số cách để đáp ứng nhu cầu này, tùy thuộc vào chính xác những gì bạn muốn đạt được. Nếu bạn chỉ muốn so sánh lượng mưa năm nay với lượng mưa năm ngoái, theo ngày, thì bạn có thể dễ dàng làm điều đó bằng cách thiết lập một số phạm vi được đặt tên động xác định dữ liệu bạn muốn sử dụng.

Để bắt đầu, hãy giả sử rằng dữ liệu của bạn cho năm 2015 nằm trong một trang tính có tên là 2015 và dữ liệu của bạn cho năm 2016 (cho đến nay) nằm trong một trang tính có tên là 2016.

Trên mỗi trang tính, hàng 1 chứa các tiêu đề, có nghĩa là ngày tháng của bạn thực sự bắt đầu ở ô A2 và kết quả đo lượng mưa của bạn trong ô B2.

Làm theo các bước sau để thiết lập phạm vi:

  1. Hiển thị tab Công thức của ruy-băng.

  2. Trong nhóm Tên đã xác định, hãy bấm vào công cụ Trình quản lý tên. Excel sẽ hiển thị hộp thoại Trình quản lý tên.

  3. Nhấp vào nút Mới. Excel sẽ hiển thị hộp thoại Tên mới. (Xem Hình 1.)

  4. Trong trường Tên, hãy nhập tên Hiện tại (lưu ý rằng đây là một từ đơn, không có khoảng trắng).

  5. Trong trường Tham chiếu đến, hãy nhập công thức sau:

  6. Nhấp vào nút OK để hoàn tất việc tạo dải ô đã đặt tên. Phạm vi mới sẽ xuất hiện trong hộp thoại Trình quản lý tên.

  7. Nhấp lại vào nút Mới. Excel (một lần nữa) hiển thị hộp thoại Tên Mới.

  8. Trong trường Tên, hãy nhập tên BeforeYear (lưu ý rằng đây là một từ đơn lẻ, không có khoảng trắng).

  9. Trong trường Tham chiếu đến, hãy nhập công thức sau:

  10. Nhấp vào nút OK. Phạm vi mới sẽ xuất hiện trong hộp thoại Trình quản lý tên.

  11. Nhấp vào nút Mới lần thứ ba. Excel sẽ hiển thị hộp thoại Tên mới.

  12. Trong trường Tên, hãy nhập tên Ngày.

  13. Trong trường Tham chiếu đến, hãy nhập công thức sau:

  14. Nhấp vào nút Đóng để loại bỏ hộp thoại Trình quản lý tên.

Với các phạm vi được xác định, bây giờ bạn có thể tạo biểu đồ bằng các phạm vi đó:

  1. Hiển thị tab Chèn của ruy-băng.

  2. Trong nhóm Biểu đồ, hãy nhấp vào tùy chọn Cột. Excel sẽ hiển thị danh sách thả xuống.

  3. Bấm một lần vào định dạng Cột Cụm. (Đây là tùy chọn đầu tiên trong tiêu đề Cột 2-D.) Excel tạo một đối tượng biểu đồ trống trong trang tính của bạn.

  4. Bấm chuột phải vào đối tượng biểu đồ. Excel sẽ hiển thị menu Ngữ cảnh.

  5. Chọn Chọn Dữ liệu từ menu Ngữ cảnh. Excel sẽ hiển thị hộp thoại Chọn Nguồn Dữ liệu, hộp thoại này sẽ hoàn toàn trống vì bạn chưa thêm bất kỳ phạm vi dữ liệu nào vào biểu đồ.

  6. Ở bên trái của hộp thoại, bên dưới Mục Chú giải (Dòng)

, nhấp vào nút Thêm. Excel sẽ hiển thị hộp thoại Chỉnh sửa chuỗi. (Xem Hình 2.)

  1. Trong trường Tên chuỗi, nhập 2015.

  2. Trong trường Giá trị chuỗi, nhập sau: ‘2015’! Trước đó Năm.

  3. Nhấp vào nút OK. Chuỗi dữ liệu hiện xuất hiện trong hộp thoại Chọn Nguồn Dữ liệu.

  4. Nhấp lại vào cùng một nút Thêm mà bạn đã nhấp trong bước 6. Excel lại hiển thị hộp thoại Chỉnh sửa Chuỗi Dữ liệu.

  5. Trong trường Tên chuỗi, nhập 2016.

  6. Trong trường Giá trị chuỗi, nhập sau: ‘2016’! CurrentYear.

  7. Nhấp vào nút OK. Chuỗi dữ liệu thứ hai này hiện xuất hiện trong hộp thoại Chọn Nguồn Dữ liệu.

  8. Ở phía bên phải của hộp thoại, trong Ngang (Danh mục)

Tiêu đề Nhãn trục, nhấp vào nút Chỉnh sửa. Excel sẽ hiển thị hộp thoại Nhãn Trục. (Xem Hình 3.)

  1. Trong Dải Nhãn Trục, hãy nhập sau: ‘2016’! Ngày.

  2. Nhấp vào nút OK. Hộp thoại Chọn Nguồn Dữ liệu phải được điền đầy đủ các thông tin cần thiết. (Xem Hình 4.)

  3. Bấm OK để loại bỏ hộp thoại Chọn Nguồn Dữ liệu.

Biểu đồ cập nhật của bạn, chỉ hiển thị các ngày cho đến ngày hôm nay, bây giờ sẽ hiển thị. Bạn có thể tiếp tục định dạng biểu đồ, như mong muốn.

(Ví dụ: bạn có thể muốn định dạng ngày trong biểu đồ để chúng không bao gồm năm.) Hơn nữa, biểu đồ là động, để khi bạn mở sổ làm việc vào ngày mai, nó sẽ phản ánh nhiều hơn một ngày so với hôm nay.

Một cách khác để xử lý là xem xét lại cách bạn đang lưu trữ dữ liệu của mình.

Thay vì lưu trữ tất cả các kết quả đo lượng mưa của bạn trên các trang tính riêng biệt (theo năm), hãy đặt tất cả chúng vào một trang tính duy nhất. Vì Excel có thể xử lý hơn một triệu hàng dữ liệu trong một trang tính nên bạn sẽ không gặp phải bất kỳ giới hạn thực tế nào. (Một triệu hàng đại diện cho hơn 2.700 năm.)

Bây giờ, trên một trang tính khác, bạn có thể sử dụng hai công thức mảng để tính lượng mưa tích lũy cho cả hai năm, cho đến nay. Công thức mảng sau đây sẽ cung cấp lượng mưa của năm trước:

=SUM(Data!B2:B1000*IF(Data!A2:A1000>=DATE(YEAR(NOW())-1,1,1),IF(Data!A2:A1000<=EDATE(NOW(),-12),1,0)))

Điều này giả định rằng các kết quả đọc về lượng mưa ban đầu nằm trên một trang tính có tên là Dữ liệu và chúng không vượt quá 1000 hàng. (Bạn có thể sửa đổi một trong hai cách này, nếu cần.) Để có được lượng mưa cập nhật cho năm nay, bạn có thể sử dụng công thức mảng sau:

=SUM(Data!B2:B1000*IF(Data!A2:A1000>=DATE(YEAR(NOW()),1,1),IF(Data!A2:A1000<=NOW(),1,0)))

Hãy nhớ: Đây là cả hai công thức mảng, vì vậy chúng phải được nhập bằng Ctrl + Shift + Enter. Giá trị duy nhất được trả về bởi mỗi công thức đại diện cho lượng mưa tích lũy từ mỗi năm cho đến nay. Hai giá trị này sau đó có thể được sử dụng trong bất kỳ biểu đồ nào bạn muốn.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (13427) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.