Excel rất tuyệt vời khi tạo biểu đồ dựa trên dữ liệu trong bảng dữ liệu. Trình hướng dẫn Biểu đồ có thể nhanh chóng xác định toàn bộ bảng dữ liệu hoặc bạn có thể chọn một phần của bảng dữ liệu và sử dụng Trình hướng dẫn Biểu đồ để tạo biểu đồ chỉ dựa trên phần đó.

Nếu bạn thay đổi phạm vi dữ liệu cho biểu đồ của mình khá thường xuyên, có thể khiến bạn mệt mỏi khi liên tục kéo Trình hướng dẫn Biểu đồ lên và thay đổi tham chiếu phạm vi dữ liệu. Ví dụ: nếu bạn có một bảng dữ liệu bao gồm nhiều năm dữ liệu, bạn có thể muốn xem một biểu đồ dựa trên dữ liệu của năm năm đầu tiên, sau đó thay đổi phạm vi dữ liệu để biểu đồ tham chiếu đến một tập hợp con khác của dữ liệu. Thực hiện các thay đổi đủ thường xuyên trong Trình hướng dẫn Biểu đồ và bạn sẽ bắt đầu truyền về các cách thực hiện các thay đổi dễ dàng hơn (và đáng tin cậy hơn) so với trình hướng dẫn.

Một cách để làm điều này là sử dụng các phạm vi đã đặt tên và một số hàm trang tính. Giả sử rằng biểu đồ của bạn được nhúng trên một trang tính, nhưng trang tính khác với biểu đồ nơi chứa dữ liệu nguồn. Trên cùng một trang tính như biểu đồ, hãy tạo hai ô đầu vào sẽ đóng vai trò là chỉ báo “từ” và “đến”. Đặt tên cho hai ô này là FromYear và ToYear.

Trên trang tính dữ liệu của bạn (trang không có biểu đồ; tôi sẽ gọi nó là “Dữ liệu nguồn”), dữ liệu được sắp xếp theo từng năm trong một cột riêng biệt và một loạt các yếu tố chi phí trong mỗi hàng. Bắt đầu bảng của bạn trong cột F và đặt các năm của bạn vào hàng 2. Đặt các yếu tố chi phí vào cột E, bắt đầu từ hàng 3. Phía trên các năm đặt một chữ cái in hoa giống như chữ cái cột và trong cột D đặt một số giống với số hàng của dữ liệu. (Xem Hình 1.)

image

Hình 1. Giai đoạn đầu của quá trình chuẩn bị dữ liệu.

Trong ví dụ này, biểu đồ được nhúng trên trang tính khác dựa trên dữ liệu trong phạm viF2: I5. Không có gì đặc biệt về biểu đồ, nhưng những thay đổi bạn chuẩn bị thực hiện sẽ làm cho nó trở nên năng động và do đó hữu ích hơn nhiều.

Bắt đầu bằng cách đặt công thức sau vào ô B1:

="Trends For " & IF(FromYear=ToYear,FromYear,FromYear & " to " & ToYear)

Công thức này cung cấp một tiêu đề động mà sau này bạn sẽ sử dụng cho biểu đồ của mình. Đặt tên addrTitle cho ô B1, sau đó đặt công thức sau vào ô B2:

="'Source Data'!$" & INDEX($F$1:$I$1,1,MATCH(FromYear,$F$2:$I$2)) & "$" & D2 & ":$" & INDEX($F$1:$I$1,1,MATCH(ToYear,$F$2:$I$2)) & "$" & D2

Sao chép công thức trong B2 vào các ô B3: B5. Công thức trả về các chuỗi địa chỉ đại diện cho các phạm vi mong muốn cho các giá trị trục X và chuỗi dữ liệu. Các phạm vi thực tế do công thức trả về sẽ khác nhau, dựa trên các giá trị bạn nhập vào các ô FromYear và ToYear trên trang tính khác. Để làm cho mọi thứ rõ ràng hơn, bạn có thể nhập một số nhãn vào cột A. (Xem Hình 2)

image

Hình 2. Giai đoạn chuẩn bị dữ liệu thứ hai.

Bây giờ bạn cần đặt tên cho từng ô trong phạm vi B2: B5. Chọn B2 và trong Hộp Tên (ngay phía trên cột A) nhập tên “addrXVal” (không có dấu ngoặc kép). Tương tự, đặt tên B3 là addrCost1, B4 là addrCost2 và B5 là addrCost3.

Bước tiếp theo là tạo một vài công thức được đặt tên mà bạn có thể sử dụng để tạo biểu đồ. Chọn Chèn | Tên | Xác định để hiển thị hộp thoại Xác định Tên. (Xem Hình 3.)

image

Hình 3. Hộp thoại Xác định Tên.

Trong vùng tên, ở đầu hộp thoại, nhập “rngXVal” (không có dấu ngoặc kép), sau đó nhập nội dung sau vào hộp Tham khảo:

=INDIRECT(addrXVal)

Sử dụng cùng một hộp thoại, xác định các tên bổ sung (rngCost1, rngCost2 và rngCost3) sử dụng cùng một loại công thức INDIRECT để tham chiếu đến các phạm vi tương ứng addrCost1, addrCost2 và addrCost3.

Bây giờ cuối cùng bạn đã sẵn sàng để cập nhật các tham chiếu trong biểu đồ của mình.

Bấm chuột phải vào biểu đồ và chọn Dữ liệu Nguồn, sau đó đảm bảo rằng tab Chuỗi được hiển thị. (Xem Hình 4.)

image

Hình 4. Tab Chuỗi của hộp thoại Dữ liệu Nguồn.

Đối với mỗi chuỗi dữ liệu được liệt kê ở phía bên trái của hộp thoại, hãy nhập Tên và Giá trị theo tên bạn đã xác định. Do đó, đối với chuỗi Cost1, bạn sẽ nhập Tên là = ‘Dữ liệu nguồn’! AddrCost1 và Giá trị là = ‘Dữ liệu nguồn’! RngCost1. Bạn cũng sẽ sử dụng các tham chiếu và tên tương tự cho từng chuỗi dữ liệu khác.

Lưu ý rằng bạn phải bao gồm tên trang tính của mình (Dữ liệu nguồn), trong dấu nháy đơn, trong các tham chiếu bạn nhập. Thuộc danh mục (X)

Tham chiếu Nhãn Axis bạn có thể nhập = ‘Dữ liệu Nguồn’! RngXVal.

Sau khi hoàn tất việc này, bạn có thể thay đổi năm bắt đầu và năm kết thúc trong các ô FromYear và ToYear, đồng thời Excel cập nhật tự động và ngay lập tức biểu đồ để đại diện cho dữ liệu bạn đã chỉ định.

Để có thêm thông tin, nếu bạn chưa thêm tiêu đề biểu đồ, hãy tiếp tục và làm như vậy. Bấm chuột phải vào biểu đồ và chọn Tùy chọn biểu đồ, sau đó hiển thị tab Tiêu đề. (Xem Hình 5.)

image

Hình 5. Tab Tiêu đề của hộp thoại Tùy chọn Biểu đồ.

Nhập bất kỳ thứ gì bạn muốn vào hộp Tiêu đề biểu đồ (bạn sẽ thay thế nó trong giây lát), sau đó nhấp vào OK. Tiêu đề biểu đồ nên đã được chọn, nhưng nếu chưa, hãy nhấp vào nó một lần. Bạn sẽ thấy hộp lựa chọn xung quanh tiêu đề. Trong thanh Công thức, hãy nhập như sau:

='Source Data'!addrTitle

Tiêu đề biểu đồ bây giờ được liên kết trở lại ô chứa chuỗi tiêu đề, đến lượt nó được cập nhật động mỗi khi bạn thay đổi các giá trị FromYear và ToYear.

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

Mẹo này (2376) áp dụng cho Microsoft Excel 97, 2000, 2002 và 2003. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện ribbon của Excel (Excel 2007 trở lên) tại đây: