Cách cố định công thức trong Excel, cố định vùng dữ liệu trong Excel

Bạn đang xem: Cách cố định công thức trong Excel, cố định vùng dữ liệu trong Excel tại vietabinhdinh.edu.vn

Trong Excel, khi sử dụng công thức có tham chiếu đến một vùng dữ liệu, nếu bạn dùng địa chỉ tương đối thì công thức và vùng dữ liệu sẽ thay đổi khi bạn di chuyển sang ô khác. Trong bài viết này, bạn có thể chỉnh sửa công thức hoặc phạm vi dữ liệu được tham chiếu trong Excel.

Để chỉnh sửa công thức hoặc phạm vi dữ liệu trong Excel, hãy sử dụng địa chỉ tuyệt đối hoặc chỉ cần sử dụng ký hiệu $ trong địa chỉ ô để chỉnh sửa.

Trong địa chỉ ô bao gồm chỉ số cột và chỉ số hàng, nếu muốn cố định hàng hoặc cột bạn chỉ cần nhập giá trị khóa của địa chỉ ô tương ứng.

1. Cố định bất kỳ giá trị nào trong công thức

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

Khi nhấn Enter bạn chỉ thấy giá trị đúng ở ô C7, khi sao chép công thức sang các ô khác bạn sẽ thấy có những ô nhận giá trị sai:

Sở dĩ các ô sau công thức sẽ bị sai là do công thức nhân đơn giá cố định tại ô C5, trong công thức B5*C5 ở dạng tuyệt đối nên khi copy công thức lấy giá trị khác sẽ bị mất địa chỉ ô C5. thay đổi theo, ví dụ ở đây ô C6 không có giá trị số nên trả về lỗi Value:

– Như vậy với công thức và bài toán này bạn cần cố định giá trị đơn giá tại ô C5. Vì một yêu cầu đăng bài chỉ được tính cho các đại lý cấp 1, nên bạn có thể:

+ Sửa dòng 5 của ô C5: C$5.

+ Hoặc sửa cả hàng 5 và cột C của ô C5:$C$5.

– Bạn chỉ cần click vào công thức và nhấn phím F4 lần đầu tiên để thay đổi địa chỉ C4 thành địa chỉ tuyệt đối (sửa cả cột và hàng):

– Với yêu cầu chỉ tính phí cho đại lý cấp 1, bạn chỉ sửa được hàng 5 chứ không sửa được cột C, bạn chỉ cần nhấn F4 2 lần là có thể sửa được hàng 5:

– Với 2 cách cố định trên đều cho kết quả chính xác khi bạn nạp cho đại lý cấp 1:

– Lưu ý khi nhấn phím F4 lần thứ 3 ta chuyển sang cố định cột C mà không cố định hàng thứ 5 của C5:

– Và như vậy kết quả tính toán sẽ trả về giá trị sai:

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

– Với cách tính như phần 1 bạn đã tính cho đại lý cấp 1. Với đại lý cấp 2, 3 bạn cũng thực hiện thao tác tương tự để đưa ra đáp án. Nhưng cách tính đó chưa phải là tối ưu, trong bài viết này Trung Tâm Đào Tạo Việt Á sẽ giới thiệu đến các bạn cách tính nhanh bằng cách sửa số liệu.

– Bài toán tí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 vấn đề.

+ Công thức chung cho 3 đại lý: Bx*K5.

Trong đó: x chạy từ 7 đến n là hàng, K là cột tương ứng với các đại lý có K bằng 1 trong 3 giá trị C, D, E.

=> Như vậy trong công thức giá trị thay đổi sẽ là: x giá trị hàng của địa chỉ Bx và K là giá trị cột của địa chỉ K5. Giá trị cố định là cột B và giá trị hàng 5.

=> Trong công thức cần cố định cột B và hàng 5 của K => lấy địa chỉ tuyệt đối của cột B và hàng 5:

Bước 1: Chỉnh sửa cột B của B7.

Tại ô cần tính số lượng đại lý cấp 1, nhập công thức =B7 => nhấn F4 3 lần (nhấn F4 lần 1 sửa cả hàng và cột của B7, nhấn F4 lần 2 sửa hàng 7 của B7, Nhấn F4 3 lần để sửa cột B của B7):

Bước 2: Sửa hàng 5 của K5.

Tiếp tục nhấn dấu nhân của phép toán -> click chọn giá trị ô C5 -> nhấn F4 2 lần là bạn đã khóa hàng địa chỉ đơn giá:

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

Bước 4: Copy công thức các giá trị ở cột đại lý cấp 1 để được kết quả.

Bước 5: Khi kéo đến giá trị ô cuối cùng của cột đại lý cấp 1, bạn nhấn giữ chuột và kéo sang phải đối với đại lý cấp 2, 3 để lấy kết quả.

Bạn có thể kiểm tra lại kết quả, sẽ không có giá trị sai. Với cách làm này bạn đã giảm được 2 lần lập công thức cho đại lý cấp 2 và cấp 3. Vì vậy các bạn chú ý cách sửa dữ liệu theo địa chỉ tuyệt đối và tùy theo giá trị để sửa cột nhé. và các hàng bạn có thể khóa chúng dễ dàng.

Đối với mỗi địa chỉ ô, nhấn phím F4 để đóng băng dữ liệu như 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 2 lần: Cố định giá trị hàng của địa chỉ ô tương ứng.

– Nhấn F4 lần 3: Cố định giá trị cột của địa chỉ ô tương ứng.

Trên đây là cách chỉnh sửa công thức hay chỉnh sửa vùng dữ liệu trong công thức trên Excel. Chúc may mắn!

Bạn thấy bài viết Cách cố định công thức trong Excel, cố định vùng dữ liệu trong Excel có đáp ướng đươc vấn đề bạn tìm hiểu không?, nếu không hãy comment góp ý thêm về Cách cố định công thức trong Excel, cố định vùng dữ liệu trong Excel bên dưới để vietabinhdinh.edu.vn có thể thay đổi & cải thiện nội dung tốt hơn cho các bạn nhé! Cám ơn bạn đã ghé thăm Website: vietabinhdinh.edu.vn

Nhớ để nguồn bài viết này: Cách cố định công thức trong Excel, cố định vùng dữ liệu trong Excel của website vietabinhdinh.edu.vn

Chuyên mục: Kiến thức chung