Trong Microsoft Excel, khi tạo các công thức, bạn có thể tính toán bằng cách nhập trực tiếp các giá trị cụ thể – còn được gọi là hard-coding – hoặc bằng cách tham chiếu đến các ô khác trong bảng tính. Hướng dẫn này sẽ giải thích tại sao việc hard-code giá trị có thể gây ra nhiều vấn đề và lý do tại sao sử dụng tham chiếu ô lại là lựa chọn tốt hơn, giúp bạn tối ưu công thức Excel một cách hiệu quả.
Những Vấn Đề Khi Hard-Code Giá Trị Trong Công Thức Excel
Giả sử bạn đang tính toán giá sản phẩm trong cửa hàng của mình sau khi cộng thêm 20% thuế. Để làm điều này, ở cột C, bạn đã nhân các giá trị trong cột B với 1.2, như công thức hiển thị trên thanh công thức:
=B2*1.2Tuy nhiên, hãy hình dung rằng vào năm tới, thuế suất cho tất cả các mặt hàng giảm xuống còn 15%. Kết quả là 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 để giá trị trong cột B được nhân với 1.15.
Chắc chắn, bạn có thể thay đổi công thức trong ô C2 và tự động sao chép nó cho các ô còn lại bằng cách nhấp đúp hoặc kéo fill handle ở góc dưới bên phải của ô. Thế nhưng, cách tiếp cận này tốn nhiều thời gian hơn mức cần thiết và rất dễ dẫn đến lỗi sao chép hoặc tự động điền.
Bàn tay cầm logo Excel và bóng đèn, tượng trưng cho ý tưởng cải tiến công thức Excel
Hơn nữa, việc hard-code giá trị 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 từng ô để xem cấu tạo của phép tính trên thanh công thức. Một cách khác là nhấn Ctrl+` (dấu huyền) để xem tất cả công thức trong bảng tính, nhưng đây lại là một bước không cần thiết và có thể gây thêm 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ị hard-code trong công thức sẽ thiếu bất kỳ ngữ cảnh nào giải thích cách bạn đạt được con số đó. Điều này gây khó khăn cho người dùng mới khi muốn hiểu hoặc kiểm tra dữ liệu.
Tham Chiếu Ô (Cell Reference) – Lựa Chọn Tối Ưu Hơn
Để khắc phục những hạn chế của việc hard-code giá trị trong công thức Microsoft Excel, bạn có thể sử dụng tham chiếu ô.
Quay lại ví dụ trên, thay vì nhân từng giá trị trong cột B với 1.2 trong công thức, bạn có thể tham chiếu đến một ô chứa biến số – trong trường hợp này là ô E2 – như sau:
=B2*$E$2Điều quan trọng cần lưu ý là tham chiếu đến ô E2 là tham chiếu tuyệt đối, được biểu thị bằng ký hiệu đô la ($) trước tên cột và số hàng. Nếu bạn không làm điều này, khi bạn sao chép công thức sang các ô còn lại trong cột C, tham chiếu ô sẽ điều chỉnh tương đối với vị trí của ô hoạt động. Ví dụ, công thức trong ô C3 sẽ tham chiếu ô E3, công thức trong ô C4 sẽ tham chiếu ô E4, v.v. Khi tạo công thức, hãy nhấn phím F4 sau khi gõ tham chiếu ô để chuyển nó từ tham chiếu tương đối sang tham chiếu tuyệt đối.
Biểu tượng Microsoft Excel giữa các chữ cái, minh họa cho các loại tham chiếu ô
Giờ đây, khi thuế suất 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ẽ tự động cập nhật ngay lập tức.
Hơn nữa, vì ô chứa biến số luôn hiển thị, rất dễ dàng để xem xét và kiểm tra tính chính xác. Ngoài ra, vì ô E1 chứa từ “Thuế”, bất kỳ ai lần đầu truy cập bảng tính cũng có thể thấy toàn bộ ngữ cảnh về cách tính tổng giá trị trong cột C.
Việc sử dụng tham chiếu ô trong công thức thay vì giá trị hard-code đặ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 một ví dụ khác, hàm IFS được sử dụng trong cột C để xác định xem mỗi học sinh đã đậu hay trượt kỳ thi cuối kỳ, tùy thuộc vào việc họ đạt được điểm chuẩn (pass mark) được lưu trữ trong ô biến E2:
=IFS([@Score]>=$E$2,"ĐẬU",[@Score]<$E$2,"TRƯỢT")
Công thức IFS trong Excel sử dụng tham chiếu ô để xác định kết quả đậu/rớt của học sinh
Đối với học kỳ tiếp theo, bạn chỉ cần sao chép trang tính và thay đổi điểm chuẩn, mà không cần 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 đó.
Lưu ý: Có những trường hợp việc hard-code giá trị trong công thức có thể phù hợp hơn. Ví dụ, bạn có thể hard-code tổng số hàng tuần để chia cho bảy để tạo ra 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ị hard-code, 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 bảng tính giảm nhẹ – mặc dù không đáng kể.
Mẹo Chuyên Nghiệp: Đặt Tên Cho Vùng Dữ Liệu Chứa Biến (Named Range)
Cho đến nay, chúng ta đã thảo luận về việc tham chiếu các ô trong công thức Excel thay vì hard-code giá trị. Một cách để bảo vệ 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 có kế hoạch sử dụng nó trong toàn bộ bảng tính của mình.
Giả sử bạn có nhiều trang tính trong một bảng tính, mỗi trang chứa các sản phẩm khác nhau từ các 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ảng tính Microsoft Excel với nhiều trang tính (worksheet) cho dữ liệu sản phẩm của các cửa hàng khác nhau
Bạn sẽ nhận thấy trong ảnh chụp màn hình trên rằng cũng có một trang tính tên là “Tax”. Trang tính này chứa tỷ lệ thuế hiện hành và giá trị mà chi phí cần được nhân với để tính tổng chi phí.
Trang tính 'Tax' trong Excel chứa thông tin về tỷ lệ thuế và hệ số nhân tính toán
Thay vì phải nhớ vị trí của biến trong bảng tính mỗi khi 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. Để làm điều này, hãy chọn ô liên quan, và trong hộp tên (name box) ở góc trên bên trái màn hình, gõ một nhãn ô dễ nhớ. Trong ví dụ này, chúng ta sẽ dùng “THUE”. Sau đó, nhấn Enter để hoàn tất quá trình đặt tên.
Bảng Excel hiển thị cách đặt tên cho một ô dữ liệu (named range) trong Name Box
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*THUEBất cứ khi nào tỷ lệ thuế thay đổi, bạn chỉ cần điều chỉnh nó một lần trong trang tính “Tax” để cập nhật tất cả các chi phí của mình. Hơn nữa, vì các tham chiếu đến các vùng được đặt tên (named ranges) có bản chất tuyệt đối, bạn không cần phải nhớ thêm các dấu đô la vào công thức của mình!
Kết Luận
Việc chuyển từ hard-coding giá trị sang sử dụng tham chiếu ô và thậm chí là đặt tên vùng dữ liệu (named range) trong Excel không chỉ là một thủ thuật nhỏ, mà là một bước tiến lớn trong việc quản lý bảng tính hiệu quả, linh hoạt và ít lỗi hơn. Phương pháp này giúp bạn dễ dàng cập nhật thông tin, tăng cường tính minh bạch và nâng cao khả năng hợp tác khi chia sẻ dữ liệu. Bằng cách áp dụng những kỹ thuật này, bạn sẽ tiết kiệm thời gian đáng kể và giảm thiểu rủi ro sai sót trong công việc hàng ngày.
Hãy bắt đầu áp dụng tham chiếu ô và đặt tên vùng cho các biến số quan trọng trong bảng tính của bạn ngay hôm nay để trải nghiệm sự khác biệt. Bạn có thường xuyên sử dụng các mẹo này trong công việc không? Hãy chia sẻ những kinh nghiệm tối ưu công thức Excel của bạn trong phần bình luận bên dưới!
