Bí quyết chi tiết để cố định công thức trong Excel từng bước

Khi làm việc với Excel, việc tham chiếu đến dữ liệu là điều thường xuyên xảy ra. Để tối ưu hóa quá trình làm việc, việc biết cách cố định công thức là vô cùng quan trọng. Trong bài viết này, HR Inside sẽ hướng dẫn bạn cách cố định công thức trong Excel một cách đơn giản và nhanh chóng nhất. Hãy khám phá chi tiết.

Mục đích của việc cố định công thức trong Excel

Mục tiêu chính của việc cố định công thức trong Excel là:

• Đảm bảo tính chính xác khi sao chép công thức

Sử dụng cách cố định công thức trong Excel giúp duy trì tham chiếu không thay đổi đến ô hoặc vùng dữ liệu khi sao chép công thức sang các ô khác. Điều này đảm bảo tính chính xác của công thức được sao chép và ngăn chặn sự thay đổi tham chiếu không mong muốn.

• Tiết kiệm thời gian và công sức

Áp dụng cách cố định công thức trong Excel khi sao chép giúp giảm bớt thời gian và công sức đáng kể trong quá trình làm việc với bảng tính. Công thức đã được cố định sẽ tự động tính toán chính xác mà không đòi hỏi thêm thời gian và công sức. Điều này mang lại sự tiện lợi và thuận tiện.

• Đảm bảo ổn định cho công thức phức tạp

Các công thức phức tạp thường chứa nhiều tham chiếu đến các ô hoặc vùng dữ liệu khác nhau. Nếu không cố định công thức, Excel sẽ tự động điều chỉnh tham chiếu khi bạn sao chép công thức, dẫn đến lỗi hoặc kết quả không chính xác.

Cách cố định trong Excel giữ nguyên tham chiếu đến ô hoặc vùng dữ liệu khi sao chép công thức, đảm bảo rằng các công thức phức tạp vẫn hoạt động chính xác khi sao chép sang các ô khác.

• Đảm bảo tính chính xác khi dữ liệu thay đổi

Hiểu biết về cách cố định công thức trong Excel giúp thực hiện các tính toán một cách dễ dàng, nhanh chóng, linh hoạt và đảm bảo tính chính xác cao khi dữ liệu thay đổi. Điều này rất quan trọng và hữu ích khi làm việc với Excel.

• Tránh lỗi khi sắp xếp dữ liệu lại

Thực hiện cách cố định trong Excel giúp tránh được lỗi khi dữ liệu được sắp xếp lại. Khi bạn sắp xếp dữ liệu, Excel tự động điều chỉnh thứ tự của các ô trong vùng dữ liệu. Nếu hàm của bạn tham chiếu đến một ô hoặc một vùng dữ liệu không còn ở vị trí ban đầu, hàm sẽ trả về lỗi.

Cố định hàm giúp duy trì tham chiếu không thay đổi đến các ô hoặc vùng dữ liệu khi bạn sắp xếp dữ liệu. Điều này đảm bảo rằng hàm của bạn vẫn hoạt động chính xác sau khi dữ liệu được sắp xếp lại.

• Bảo mật thông tin

Khi bạn cố định công thức, Excel không tự động thay đổi tham chiếu khi sao chép công thức. Điều này giúp ngăn chặn nguy cơ ai đó vô tình hoặc có chủ ý thay đổi thông tin nhạy cảm. Thực hiện cách cố định trong Excel cũng ngăn chặn việc truy cập trái phép vào các ô hoặc vùng dữ liệu chứa thông tin nhạy cảm. Việc nắm vững kỹ năng cố định hàm là quan trọng để sử dụng Excel một cách hiệu quả và an toàn.

Cách cố định công thức trong Excel

Để cố định công thức trong Excel, bạn sử dụng ký hiệu $. Có ba kiểu cố định phổ biến nhất là:

• B$1: Ký tự $ nằm ở giữa số và chữ. Chức năng của nó là cố định dòng nhưng không cố định cột. Khi sao chép công thức sang một cột khác, số dòng sẽ không thay đổi.
• $B1: Ký tự $ nằm ở trước số và chữ. Chức năng của nó là cố định cột nhưng không cố định dòng. Khi sao chép công thức sang một dòng khác, số thứ tự dòng sẽ thay đổi.
• $B$1: Ký tự $ nằm ở trước số và chữ. Chức năng của nó là cố định cả dòng và cột. Khi sao chép công thức sang một cột khác, cả số dòng và cột đều không thay đổi.

Xem ví dụ sau để hiểu cách cố định ô tham chiếu trong Excel.

Ví dụ:

Giả sử bạn có bảng dữ liệu như hình dưới đây và yêu cầu:

• Tính tổng tiền cho các mặt hàng, giả sử: Giá niêm yết của tất cả các mặt hàng là 230, vì vậy bạn cần cố định cột C dòng 4.
• Bạn nhập công thức =PRODUCT(B4,$CD4). Khi kéo thả chuột đến các ô bên dưới, $CD4 vẫn cố định và không bị thay đổi, các giá trị khác như B4, B5,… sẽ thay đổi. Điều này minh họa cách cố định giá trị trong Excel.

Cách cố định công thức trong Excel

Dưới đây là hướng dẫn về 2 cách cố định trong Excel:

Cách 1: Cố định một giá trị bất kỳ trong công thức

Cách cố định trong Excel với một giá trị bất kỳ như sau:

• Bước 1: Tính tổng tiền thanh toán cho đại lý cấp 1 bằng cách nhân giá trị số lượng trong cột B với đơn giá ở ô C5. Nhập công thức =B5*C5:
• Bước 2: Nhấn Enter để có kết quả tại ô C7. Khi sao chép công thức xuống các ô khác, có thể xảy ra lỗi #VALUE nếu địa chỉ ô C5 không được cố định.
• Bước 3: Để cố định công thức trong Excel, chọn công thức cần cố định, sau đó nhấn phím F4 lần 1 để đổi địa chỉ C4 thành địa chỉ tuyệt đối.
• Bước 4: Cố định hàng 5 bằng cách nhấn phím F4 lần 2.
• Bước 5: Bảng dữ liệu trả về kết quả đúng khi tính tổng tiền cho đại lý cấp 1:
• Bước 6: Để cố định cột C mà không cố định hàng thứ 5 của C5, nhấn F4 lần 3. Tuy nhiên, kết quả sẽ trả về giá trị sai.

Cách 2: Cố định các giá trị trong công thức

Hướng dẫn cách cố định các giá trị trong Excel như sau:

• Bước 1: Cố định cột B của B7. Nhập công thức =B7 vào ô cần tính tiền của đại lý cấp 1 (ô C7), sau đó nhấn F4 3 lần để cố định cột B của B7.
• Bước 2: Cố định hàng 5 của K5. Nhấn dấu nhân trong công thức, sau đó kích chọn giá trị ô C5. Cuối cùng, nhấn F4 2 lần để khóa hàng của địa chỉ đơn giá.
• Bước 3: Nhấn Enter để nhận kết quả.
• Bước 4: Để nhận kết quả ở những ô tiếp theo, bạn chỉ cần sao chép công thức các giá trị trong cột đại lý cấp 1.
• Bước 5: Đặt trỏ chuột vào ô giá trị cuối cùng của đại lý cấp 1, sau đó nhấn giữ chuột và kéo sang phải để hiển thị kết quả cho các đại lý cấp 2, cấp 3.

Cách khóa giá trị trong Excel để dữ liệu không bị thay đổi

Khi đã áp dụng cách cố định trong Excel hoàn tất và bạn muốn khóa giá trị trong Excel, bảo vệ dữ liệu khỏi sự thay đổi thì thực hiện theo các bước sau:

• Bước 1: Mở File Excel và chọn ô hoặc vùng dữ liệu mà bạn muốn khóa.
• Bước 2: Trên thanh công cụ, chọn Home -> Alignment -> nhấn vào mũi tên nhỏ ở góc bên phải.

• Bước 3: Chọn tab Bảo vệ trong hộp thoại Định dạng Ô. Đánh dấu vào ô Locked. Kết thúc bằng việc nhấn OK.

• Bước 4: Chọn tab Kiểm tra rồi nhấn Protect Sheet để khóa trang tính.

• Bước 5: Nhập mật khẩu vào ô Password to unprotect sheet. Chọn các tùy chọn bảo vệ như Chọn ô bị khóa, Chọn ô không khóa, Xóa cột, Xóa hàng. Sau đó nhấn OK.

• Bước 6: Nhập lại mật khẩu và nhấn OK để hoàn tất quá trình.

Đó là cách cố định trong Excel một cách đơn giản và hiệu quả nhất. Hy vọng bạn có thể áp dụng thành công để tăng tính bảo mật và sự chính xác cho bảng tính của mình.