Nguyên tắc cố định tọa độ khi viết công thức tính trong Excel

Trong quá trình làm việc và tính toán bằng Excel, chắc hẳn các bạn đã gặp phải trường hợp khi sao chép công thức tính toán sang các ô khác, kết quả lại không đúng nữa. Vậy chuyện gì đã xảy ra? Làm sao để có thể cố định toạ độ công thức đúng? Chúng ta sẽ cùng tìm hiểu trong bài viết này nhé.

Cố định toạ độ công thức trong Excel là gì?

Tham chiếu ô trong Excel

Khi bạn sử dụng công thức và các hàm tính toán trong Excel, bạn sẽ thấy mình cần sử dụng 1 công thức cho nhiều lần, bạn có thể sao chép công thức đó để dùng lại, hoặc đơn giản là kéo ô chứa công thức hàm đó xuống những ô mà bạn cũng cần tính toán tương tự, khi đó, Excel sẽ tự động thay số từ các ô tương ứng được kéo xuống, đó gọi là tham chiếu ô. Khi bạn sao chép hoặc kéo công thức đến các ô khác, Excel cũng sẽ thay đổi số liệu tính toán theo địa chỉ ô mới, tính toán lại theo tham chiếu ô đó. 

Tại sao cần cố định toạ độ khi viết công thức Excel?

Cố định toạ độ khi viết công thức hàm tính toán trong Excel chính là bạn đang sử dụng tham chiếu ô tuyệt đối. Khác với tham chiếu ô tương đối cho phép Excel thay đổi số liệu tính toán theo ô tham chiếu mới, tham chiếu ô tuyệt đối sẽ giúp bạn cố định một hoặc nhiều ô chứa dữ liệu bạn cần cố định. Điều này đặc biệt áp dụng nhiều với các tính toán có điều kiện kèm theo, và bạn cần giữ nguyên giá trị của điều kiện đó, không thay đổi.

Để sử dụng được tham chiếu ô tuyệt đối, các bạn sẽ cần cố định toạ độ của các ô chứa dữ liệu mà bạn muốn cố định bằng dấu Dollar ($). 

Dấu Dollar ($) giúp bạn cố định toạ độ ô nhất định được tham chiếu, sao cho dù bạn có sao chép công thức tới bất kỳ ô nào trong bảng Excel, toạ độ ô đó vẫn sẽ được giữ nguyên, đảm bảo công thức tính toán của bạn luôn sử dụng đúng số liệu bạn cần.

Xem thêm: Cách sử dụng ký hiệu Dollar ($) trong công thức Excel - Tham chiếu ô tương đối và tuyệt đối là gì?

Cố định toạ độ khi viết công thức trong báo cáo bằng Excel

Trong bài viết trước, chúng ta đang tính toán các số liệu trong một bản báo cáo 3 chiều, vậy chúng ta sẽ tiếp tục thực hành cố định toạ độ công thức trong bản báo cáo đó.

Như các bạn thấy trong bản báo cáo, công thức tính số lượng được tham chiếu với rất nhiều các điều kiện kèm theo. Nhưng khi chúng ta áp dụng công thức này với các ô khác, tham chiếu ô sẽ tự động bị xê dịch đi theo ô mới, vì vậy kết quả báo cáo của chúng ta sẽ không còn chính xác nữa. 

Để công thức tính toán nói chung và công thức tính toán của hàm SUMIFS trong báo cáo nói riêng luôn đúng, chúng ta có một nguyên tắc như sau: Khi viết hàm SUMIFS cho báo cáo 3 chiều, chúng ta phải cố định toạ độ của các điều kiện cũng như các vùng điều kiện cần tính toán.

Cụ thể trong báo cáo trên, chúng ta sẽ cần cố định các vùng dữ liệu như sau

Đây là các vùng dữ liệu lấy từ các sheet dữ liệu khác, vì vậy chúng ta bắt buộc phải cố định các vùng dữ liệu này để số liệu được chính xác nhé. Để cố định toạ độ công thức, bạn bôi đen vùng cần cố định và nhấn F4, các vùng này sẽ được thêm dấu Dollar ($) để cố định lại. 

Tiếp theo chúng ta cần cố định các vùng điều kiện được áp dụng cho báo cáo. Tuy nhiên, chúng ta sẽ cần căn cứ vào vị trí của điều kiện để xác định được chúng ta sẽ cần cố định công thức theo hướng nào. 

Trong báo cáo trên, tất cả các điều kiện nằm ở chiều thứ 1 của báo cáo, là chiều điều kiện nằm trên cùng một cột, thì chúng ta sẽ cố định cột, các bạn sẽ nhấn phím F4 3 lần để dấu $ cố định đúng vào phần cột trong toạ độ ô. 

Tiếp theo với vùng điều kiện thứ 2, là điều kiện nằm trên cùng 1 dòng, chúng ta sẽ cố định trước dòng của toạ độ này. Để cố định dòng, các bạn nhấn F4 2 lần nhé, sao cho dấu $ được đặt trước vị trí của dòng.

Với điều kiện thứ 3, điều kiện này nằm bên ngoài bảng báo cáo, vì vậy chúng ta sẽ cố định cả dòng và cột của vùng điều kiện này.

 

Sau đó bấm Enter và kéo công thức này đến những ô còn lại trong bảng báo cáo. Tất cả các cột về số lượng đều sẽ được tính toán chính xác theo vùng dữ liệu và điều kiện đã cho. 

Xem thêm: Các thành phần trong báo cáo và cách định dạng các phần báo cáo trong Excel

Tương tự như vậy, các bạn điều chỉnh cố định các vùng dữ liệu, vùng điều kiện phù hợp với công thức tính toán của các cột Giá trị nhé, lưu ý xác định kỹ các vùng cần cố định để kết quả báo cáo được chính xác.

Tổng kết

Vậy là qua bài viết này, chúng ta đã cùng tìm hiểu về cách Excel tham chiếu ô để tính toán, cũng như biết cách cố định toạ độ công thức của các ô dữ liệu để kết quả trả về chính xác nhất. Đây là kỹ thuật sẽ được thường xuyên sử dụng khi các bạn tính toán, làm báo cáo trên Excel, vậy nên hãy thực hành nhiều lần để nắm rõ hơn về cách cố định toạ độ khi viết công thức trong báo cáo bằng Excel nhé. 

Chúc các bạn học thật tốt và áp dụng thành công kiến thức này trong công việc. Đọc thêm các bài viết hay của chúng mình dưới đây: