Cách thức cố định công thức và vùng dữ liệu trong Excel một cách hiệu quả.

Khi áp dụng công thức tham chiếu đến vùng dữ liệu trong Excel, nếu sử dụng địa chỉ tương đối, công thức và vùng dữ liệu sẽ thay đổi khi bạn di chuyển ô. Bài viết này sẽ chỉ ra cách cố định công thức và vùng dữ liệu tham chiếu để duy trì sự ổn định trong Excel.

Để cố định công thức hay vùng dữ liệu trong Excel, bạn có thể sử dụng địa chỉ tuyệt đối bằng cách thêm dấu $ vào ô để ngăn không cho địa chỉ thay đổi.

Trong địa chỉ ô Excel, có chỉ số cột và chỉ số hàng. Nếu bạn muốn cố định một trong hai giá trị này, chỉ cần thêm dấu $ vào phần tương ứng của địa chỉ ô cần cố định.

1. Cách cố định một giá trị trong công thức Excel

Giả sử bạn có một bảng dữ liệu và cần tính toán số tiền thanh toán cho đại lý cấp 1. Số tiền thanh toán sẽ được tính bằng cách nhân số lượng trong cột B với đơn giá tại ô C5. Nếu bạn chỉ sử dụng công thức đơn giản = B5*C5,

Khi nhấn Enter, bạn sẽ thấy công thức chính xác ở ô C7, nhưng khi sao chép công thức sang các ô khác, kết quả sẽ sai ở các ô sau đó.

Nguyên nhân là do công thức nhân giá trị với ô C5 không được cố định. Khi sao chép công thức, ô C5 (vốn ở dạng tham chiếu tuyệt đối) sẽ thay đổi theo địa chỉ của ô mới, dẫn đến kết quả sai, ví dụ như ô C6 không có giá trị số và gây lỗi Value.

- Để giải quyết bài toán này, bạn cần cố định giá trị đơn giá tại ô C5. Vì yêu cầu chỉ tính thành tiền cho đại lý cấp 1, bạn có thể:

+ Cố định hàng 5 của ô C5: C$5.

+ Hoặc cố định cả hàng 5 và cột C của ô C5: $C$5.

- Bạn chỉ cần chọn công thức và nhấn phím F4 lần đầu để thay đổi địa chỉ C4 thành địa chỉ tuyệt đối, cố định cả cột và hàng.

- Do yêu cầu chỉ tính tiền cho đại lý cấp 1, bạn có thể chỉ cần cố định hàng 5 mà không cần cố định cột C. Chỉ cần nhấn phím F4 hai lần để cố định hàng 5.

- Cả hai phương pháp cố định trên đều cho kết quả chính xác khi tính thành tiền cho đại lý cấp 1.

- Lưu ý rằng khi nhấn phím F4 lần thứ ba, bạn sẽ chuyển sang cố định cột C mà không cố định hàng 5 của ô C5.

- Khi đó, kết quả tính toán sẽ sai và không còn chính xác.

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

- Với cách tính như phần 1 các bạn đã tính thành tiền cho đại lý cấp 1. Với đại lý cấp 2, 3 bạn thực hiện thao tác tương tự là có thể đưa ra đáp án. Nhưng cách tính đó chưa tối ưu, ở bài viết này Tripi sẽ giới thiệu các bạn cách tính nhanh bằng cách cố định dữ liệu.

- Bài toán đặt ra tính thành tiền cho đại lý các cấp với mỗi đại lý có 1 đơn giá khác nhau.

- Phân tích bài toán.

+ Công thức tính thành tiền cho 3 đại lý có dạng: Bx*K5.

Trong đó: x là số hàng, chạy từ 7 đến n, và K là cột tương ứng với các đại lý, trong đó K có thể là một trong ba giá trị C, D hoặc E.

=> Vì vậy, trong công thức, giá trị thay đổi sẽ là: x (số hàng của Bx) và K (cột của K5). Các giá trị cố định bao gồm cột B và hàng 5.

=> Để cố định công thức, bạn cần đảm bảo cột B và hàng 5 của K được giữ nguyên. Sử dụng địa chỉ tuyệt đối cho cột B và hàng 5.

Bước 1: Cố định cột B tại ô B7.

Tại ô cần tính thành tiền cho đại lý cấp 1, nhập công thức = B7. Sau đó, nhấn F4 ba lần: F4 lần 1 cố định cả cột và hàng của B7, F4 lần 2 chỉ cố định hàng 7, và F4 lần 3 cố định cột B.

Bước 2: Cố định hàng 5 của ô K5.

Tiếp theo, nhấn dấu nhân trong công thức, chọn ô C5 và nhấn F4 hai lần để cố định hàng của địa chỉ đơn giá.

Bước 3: Nhấn Enter để nhận kết quả ngay lập tức.

Bước 4: Sao chép công thức và áp dụng cho các ô trong cột đại lý cấp 1 để nhận kết quả tương ứng.

Bước 5: Kéo chuột đến ô cuối cùng trong cột đại lý cấp 1, sau đó giữ chuột và kéo sang phải để áp dụng cho đại lý cấp 2 và cấp 3.

Bạn có thể kiểm tra kết quả để đảm bảo chính xác tuyệt đối. Với phương pháp này, bạn đã giảm bớt công thức cho các đại lý cấp 2 và cấp 3. Hãy chú ý cách cố định dữ liệu qua địa chỉ tuyệt đối, giúp khóa cột và hàng một cách dễ dàng theo yêu cầu.

Để cố định dữ liệu tại mỗi ô, hãy nhấn phím F4 theo các bước sau:

- Nhấn F4 lần 1: Cố định đồng thời cả hàng và cột của ô đã chọn.

- Nhấn F4 lần 2: Cố định giá trị của hàng tại ô đã chọn.

- Nhấn F4 lần 3: Cố định giá trị của cột tại ô đã chọn.

Đây là phương pháp cố định công thức, hay chính xác hơn là cố định vùng dữ liệu trong Excel. Chúc các bạn đạt được thành công như mong đợi!