VietNetwork.Vn

Tham chiếu đến các ô hoặc phạm vi được đặt tên là giải pháp khả thi nhất.

Khi tạo công thức Microsoft Excel, bạn có thể tạo phép tính bằng các giá trị cụ thể—còn được gọi là mã hóa cứng—hoặc tham chiếu đến các ô khác trong sổ làm việc. Trong hướng dẫn này, tôi sẽ giải thích lý do tại sao mã hóa cứng các giá trị có thể gây ra sự cố và tại sao sử dụng tham chiếu ô thường là lựa chọn tốt hơn.


1. Các vấn đề gây ra bởi các giá trị mã hóa cứng trong công thức Excel

Giả sử bạn đang tính toán chi phí sản phẩm trong cửa hàng của mình khi thuế 20% được thêm vào. Để thực hiện việc này, trong cột C, bạn đã nhân các giá trị trong cột B với 1,2, như được thấy trong thanh công thức ở đầu cửa sổ Excel:

=B2*1,2

Tuy nhiên, bây giờ hãy tưởng tượng rằng, năm sau, thuế đối với tất cả các mặt hàng được giảm xuống còn 15%. Do đó, bạn sẽ phải quay lại các ô và thay đổi tất cả các công thức trong cột C để các giá trị trong cột B được nhân với 1,15.

Có, bạn có thể thay đổi công thức trong ô C2 và tự động sao chép công thức đó trong các ô còn lại bằng cách nhấp đúp hoặc nhấp và kéo tay cầm điền ở góc dưới bên phải của ô. Tuy nhiên, cách tiếp cận này mất nhiều thời gian hơn mức cần thiết và có thể dễ dàng dẫn đến lỗi sao chép hoặc tự động điền.

Hơn nữa, các giá trị mã hóa cứng trong công thức Excel khiến việc phát hiện lỗi trở nên khó khăn hơn vì bạn phải chọn một ô để xem thành phần của phép tính trong thanh công thức. Ngoài ra, bạn có thể nhấn Ctrl+` (dấu huyền) để xem tất cả các công thức trong bảng tính, nhưng—một lần nữa—đây là một bước không cần thiết và có thể gây nhầm lẫn.

Cuối cùng, nếu bạn chia sẻ bảng tính Excel của mình với người khác, các giá trị được mã hóa cứng trong công thức sẽ thiếu bất kỳ ngữ cảnh nào giải thích cách bạn đưa ra con số đó.

2. Tại sao tham chiếu đến các ô biến đổi là một lựa chọn tốt hơn

Để khắc phục nhược điểm của việc mã hóa cứng các giá trị trong công thức Microsoft Excel, bạn có thể sử dụng tham chiếu ô.

Sử dụng ví dụ trên, thay vì nhân mỗi giá trị trong cột B với 1,2 trong công thức của bạn, bạn có thể tham chiếu đến một ô chứa biến—trong trường hợp này là ô E2—thay vào đó:

=B2*$E$2

Điều quan trọng cần lưu ý là tham chiếu đến ô E2 là tuyệt đối, được biểu thị bằng ký hiệu đô la ($) trước cột và hàng. Nếu bạn không làm như vậy, khi bạn sao chép công thức vào các ô còn lại trong cột C, tham chiếu ô sẽ điều chỉnh theo vị trí của ô đang hoạt động. Ví dụ, công thức trong ô C3 sẽ tham chiếu đến ô E3, công thức trong ô C4 sẽ tham chiếu đến ô E4, v.v.

Khi soạn công thức, hãy nhấn F4 sau khi nhập tham chiếu ô để chuyển từ tham chiếu tương đối sang tham chiếu tuyệt đối.

Bây giờ, khi thuế đối với các mặt hàng thay đổi thành 15%, bạn chỉ cần thay đổi giá trị trong ô E2 thành 1,15 và tất cả các công thức tham chiếu đến ô đó sẽ được cập nhật ngay lập tức.


Hơn nữa, vì ô chứa biến luôn hiển thị nên dễ dàng xem lại độ chính xác. Ngoài ra, vì ô E1 chứa từ "Thuế", bất kỳ ai truy cập bảng tính lần đầu đều có thể thấy toàn bộ bối cảnh về cách tính tổng giá trị trong cột C.

Tuyên bố miễn trừ trách nhiệm: Có những trường hợp khi mã hóa cứng các giá trị trong công thức có thể phù hợp hơn. Ví dụ, bạn có thể mã hóa cứng tổng số hàng tuần để chia cho bảy để tạo tổng số hàng ngày, vì số ngày trong một tuần không bao giờ thay đổi. Ngoài ra, các công thức có tham chiếu ô sử dụng nhiều bộ nhớ hơn một chút so với các công thức có giá trị được mã hóa cứng, vì vậy nếu bạn thực hiện điều này thường xuyên, bạn có thể thấy hiệu suất của sổ làm việc giảm xuống—mặc dù không đáng kể.

Sử dụng tham chiếu ô trong công thức thay vì các giá trị được mã hóa cứng đặc biệt hữu ích nếu các tham số tính toán có khả năng thay đổi trong tương lai. Trong ví dụ này, hàm IFS được sử dụng trong cột C để xác định xem mỗi sinh viên có đỗ hay trượt kỳ thi cuối kỳ hay không, tùy thuộc vào việc họ có đạt được biến điểm đỗ trong ô E2 hay không:

=IFS([@Score]<$E$2,"FAIL",[@Score]>=$E$2,"PASS")

Đối với học kỳ tiếp theo, bạn chỉ cần sao chép bảng tính và thay đổi biến điểm đỗ mà không cần phải lo lắng về việc điều chỉnh công thức để tạo ra kết quả chính xác cho dữ liệu đó.

3. Mẹo chuyên nghiệp: Đặt tên cho biến

Cho đến nay, tôi đã nói về việc tham chiếu các ô trong công thức Excel thay vì mã hóa cứng các giá trị. Một cách để bảo mật công thức của bạn hơn nữa là đặt tên cho ô chứa biến, điều này đặc biệt hữu ích nếu bạn định sử dụng nó trong toàn bộ sổ làm việc của mình.

Giả sử bạn có nhiều trang tính trong một sổ làm việc, mỗi trang tính chứa nhiều sản phẩm khác nhau từ nhiều cửa hàng khác nhau và bạn cần hiển thị tổng chi phí của từng mặt hàng, bao gồm cả thuế.


Bạn sẽ thấy trong ảnh chụp màn hình ở trên cũng có một bảng tính có tên là "Thuế". Bảng tính này chứa mức thuế hiện tại và giá trị mà chi phí cần được nhân với để tính tổng chi phí.


Thay vì phải nhớ vị trí của biến trong sổ làm việc của bạn mỗi lần bạn muốn tính tổng chi phí của một sản phẩm, bạn có thể đặt tên cho ô này. Để thực hiện việc này, hãy chọn ô có liên quan và trong hộp tên ở góc trên cùng bên trái màn hình, hãy nhập nhãn ô dễ nhớ. Trong trường hợp của tôi, tôi đã chọn "TAX". Sau đó, nhấn Enter để hoàn tất quá trình đặt tên.


Bây giờ, bất kể bạn đang ở đâu trong bảng tính, bạn có thể dễ dàng tham chiếu biến này trong công thức của mình:

=B2*TAX

Bất cứ khi nào tỷ lệ thuế thay đổi, bạn chỉ cần điều chỉnh một lần trong bảng tính Thuế để cập nhật tất cả chi phí của mình. Hơn nữa, vì các tham chiếu đến phạm vi được đặt tên có bản chất tuyệt đối, bạn không cần phải nhớ thêm dấu đô la vào công thức của mình!

Tham chiếu đến các ô thay vì mã hóa cứng các giá trị trong công thức không phải là cách duy nhất để tiết kiệm thời gian trong Microsoft Excel. Ví dụ, bạn có thể thêm các lệnh được sử dụng nhiều nhất vào Thanh công cụ truy cập nhanh hoặc tìm hiểu một số phím tắt hữu ích nhất của Excel để giúp bạn không phải chuyển đổi giữa bàn phím và chuột.