Buzz
Trong Excel, khi áp dụng công thức với tham chiếu đến vùng dữ liệu, việc sử dụng địa chỉ tương đối sẽ làm thay đổi công thức và vùng dữ liệu khi bạn chuyển đến ô khác. Bài viết này sẽ hướng dẫn bạn cách giữ nguyên công thức và vùng dữ liệu trong Excel.
Để cố định công thức hoặc vùng dữ liệu trong Excel, bạn có thể sử dụng địa chỉ tuyệt đối, đơn giản bằng cách sử dụng ký tự $ trong địa chỉ ô.
Trong địa chỉ ô, bao gồm chỉ số cột và hàng, để cố định hàng hoặc cột, bạn chỉ cần khóa giá trị tương ứng của địa chỉ ô.
1. Giữ nguyên giá trị quan trọng trong công thức
Ví dụ, trong bảng dữ liệu dưới đây, chúng ta cần tính tổng số tiền thanh toán cho đại lý cấp 1. Tổng số tiền này được tính bằng cách nhân số lượng trong cột B với đơn giá tại ô C5. Đừng chỉ sử dụng công thức đơn giản như = B5*C5:
Khi bạn nhấn Enter, bạn sẽ thấy giá trị đúng chỉ tại ô C7. Nhưng khi sao chép công thức cho các ô khác, bạn sẽ gặp phải vấn đề với các ô nhận giá trị sai:
Lí do công thức sai cho các ô sau là do việc cố định giá trị đơn giá tại ô C5. Trong công thức B5*C5 với dạng tuyệt đối, khi sao chép công thức cho các giá trị khác, địa chỉ ô C5 sẽ thay đổi theo. Ví dụ, ô C6 không có giá trị số, dẫn đến lỗi Value:
- Để giải quyết bài toán và công thức này, hãy cố định giá trị đơn giá tại ô C5. Vì yêu cầu chỉ tính tiền cho đại lý cấp 1, bạn có thể:
+ Giữ nguyên hàng 5 của ô C5: C$5.
+ Hoặc giữ nguyên cả hàng 5 và cột C của ô C5: $C$5.
- Chỉ cần chọn công thức và nhấn phím F4 một lần là có thể biến địa chỉ C4 thành địa chỉ tuyệt đối (cố định cả cột và hàng):
- Đối với yêu cầu chỉ tính tiền cho đại lý cấp 1, bạn chỉ cần cố định hàng 5 mà không cần cố định cột C. Nhấn phím F4 lần 2 để cố định hàng 5:
- Cả hai cách cố định đều mang lại kết quả chính xác khi tính tiền cho đại lý cấp 1:
- Lưu ý khi nhấn phím F4 lần 3, chúng ta chỉ cố định cột C mà không cố định hàng thứ 5 của C5:
- Kết quả tính toán sẽ trả về giá trị không chính xác:
2. Tối ưu hóa việc cố định các giá trị trong công thức
- Sau khi tính thành tiền cho đại lý cấp 1 theo hướng dẫn ở phần trước, bạn đã có kết quả. Đối với đại lý cấp 2, 3, bạn có thể áp dụng cùng phương pháp để đạt được kết quả. Tuy nhiên, có một cách tính hiệu quả hơn được giới thiệu trong bài viết này của Mytour.
- Bài toán yêu cầu tính thành tiền cho đại lý các cấp, với mỗi đại lý có một đơn giá khác nhau.
- Phân tích bài toán.
+ Công thức tổng quát để tính thành tiền cho 3 đại lý: Bx*K5.
Trong đó: x chạy từ 7 tới n là hàng, K là cột tương ứng với đại lý, với K có thể là C, D hoặc E.
=> Đối với công thức này, giá trị thay đổi là x của giá trị hàng tại địa chỉ Bx và K là giá trị cột tại địa chỉ K5. Cần cố định cột B và hàng 5.
=> Để cố định cột B và hàng 5 trong công thức, chúng ta lấy địa chỉ tuyệt đối của cột B và hàng 5:
1. Cố định cột B của B7.
Tại ô tính thành tiền của đại lý cấp 1, nhập công thức = B7 => nhấn F4 ba lần (lần 1: cố định cả hàng và cột của B7, lần 2: cố định hàng 7 của B7, lần 3: cố định cột B của B7):
2. Cố định hàng 5 của K5.
Tiếp tục nhấn dấu nhân trong phép toán -> chọn giá trị của ô C5 -> nhấn F4 hai 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: Sao chép công thức cho tất cả giá trị trong cột đại lý cấp 1.
Bước 5: Kéo chuột tới giá trị cuối cùng của cột đại lý cấp 1, giữ chuột và kéo sang bên phải cho các đại lý cấp 2, cấp 3.
Kiểm tra kết quả để đảm bảo không có giá trị nào bị sai. Bằng cách này, bạn giảm đến 2 lần tạo công thức cho đại lý cấp 2 và cấp 3. Lưu ý cách cố định dữ liệu bằng cách sử dụng địa chỉ tuyệt đối, tùy thuộc vào giá trị cần cố định cột và hàng, bạn có thể dễ dàng khóa chúng.
Với mỗi địa chỉ ô, sử dụng phím F4 để cố định dữ liệu theo các bước sau:
- Nhấn F4 lần 1: Cố định cả hàng và cột của địa chỉ ô tương ứng.
- Nhấn F4 lần 2: Giữ nguyên giá trị hàng của địa chỉ ô tương ứng.
- Nhấn F4 lần 3: Giữ nguyên giá trị cột của địa chỉ ô tương ứng.
Dưới đây là cách cố định công thức, chính là việc cố định vùng dữ liệu trong Excel. Chúc các bạn đạt được thành công lớn!
