Bạn muốn tìm hiểu cách cố định công thức trong excel,cố định vùng dữ liệu trong excel.Hãy cùng aptech đón đọc để khám phá cách cố định công thức và vùng dữ liệu trong Excel, giúp bạn làm việc hiệu quả hơn và tiết kiệm thời gian đáng kể.
Cách cố định công thức và cố định vùng dữ liệu trong Excel
1. Cố định 1 giá trị bất kì trong công thức
Ví dụ có bảng dữ liệu dưới đây cần tính số tiền cần thanh toán cho đại lý cấp 1. Số tiền cần thanh toán sẽ bằng giá trị số lượng trong cột B nhân với đơn giá ở ô C5. Nếu các bạn chỉ nhập công thức đơn thuần = B5*C5:
Khi nhấn Enter các bạn sẽ thấy giá trị chỉ đúng tại ô C7 khi copy công thức cho các ô khác các bạn sẽ thấy sẽ có ô nhận giá trị sai:
Lý do công thức sẽ sai cho các ô sau vì công thức nhân đơn giá được cố định ô C5, trong công thức B5*C5 ở dạng tuyệt đối nên khi sao chép công thức cho các giá trị khác địa chỉ ô C5 được thay đổi theo, ví dụ như ở đây ô C6 không có giá trị số nên giá trị 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á ở ô C5. Vì yêu cầu bài chỉ tính thành tiền cho đơn vị đại lý cấp 1 nên 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 kích chọn vào công thức nhấn phím F4 lần 1 là có thể đổi địa chỉ C4 thành địa chỉ tuyệt đối (cố định cả cột và hàng):
Với yêu cầu chỉ tính tiền đại lý cấp 1 nên bài bạn có thể chỉ cần cố định hàng 5 mà không cần cố định cột C, bạn chỉ cần nhấn phím F4 lần 2 là có thể cố định hàng 5:
Với 2 cách cố định trên đều đưa ra kết quả đúng khi bạn tính thành tiền cho đại lý cấp 1:
Các bạn lưu ý khi nhấn phím F4 lần 3 chúng 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ả lại giá trị sai:
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 ThuThuatPhanMem.vn 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 chung để 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 các đại lý với 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 của 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: Cố định cột B của B7.
Tại ô cần tính thành tiền của đại lý cấp 1 nhập công thức = B7 => thực hiện nhấn F4 ba lần( nhấn F4 lần 1 sẽ cố định cả hàng và cột của B7, nhấn F4 lần 2 cố định hàng 7 của B7, nhấn F4 lần 3 cố định cột B của B7):
Bước 2: Cố định hàng 5 của K5.
Tiếp tục nhấn dấu nhân của phép toán -> kích chọn giá trị ô C5 -> nhấn F4 hai lần bạn đã khóa hàng của địa chỉ đơn giá:
Bước 3: Nhấn Enter được kết quả.
Bước 4: Sao chép công thức cho các giá trị trong cột đại lý cấp 1 được kết quả
Bước 5: Khi kéo tới giá trị ô cuối cùng của cột đại lý cấp 1, bạn nhấn giữ chuột kéo sang bên phải cho các đại lý cấp 2, cấp 3 được kết quả.
Bạn có thể kiểm tra lại kết quả, chắc chắn sẽ không sai một giá trị nào. Với cách làm này bạn đã giảm bớt 2 lần tạo công thức cho đại lý cấp 2 và cấp 3. Như vậy bạn để ý tới cách cố định dữ liệu bằng cách sử dụng địa chỉ tuyệt đối và tùy vào giá trị cần cố định cột và hàng bạn có thể khóa chúng một cách dễ dàng.
Với mỗi địa chỉ ô cách nhấn phím F4 để cố định dữ liệu như sau:
– Nhấn F4 lần1: Cố định cả hàng và cột của địa chỉ ô tương ứng.
– Nhấn F4 lần 2: 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.
FAQ: CÂU HỎI THƯỜNG GẶP
1. Làm sao để cố định công thức trong Excel?
Để cố định công thức trong Excel, bạn chỉ cần sử dụng ký hiệu “$” trước các dòng hoặc cột mà bạn muốn cố định. Ví dụ, nếu bạn muốn cố định dòng 1 và cột A, bạn có thể sử dụng công thức “$A$1”. Khi bạn sao chép công thức này sang các ô khác, dòng 1 và cột A sẽ không thay đổi.
2. Làm sao để cố định vùng dữ liệu trong Excel?
Để cố định vùng dữ liệu trong Excel, bạn cũng có thể sử dụng ký hiệu “$”. Ví dụ, nếu bạn muốn cố định vùng dữ liệu từ ô A1 đến ô C5, bạn có thể sử dụng công thức “$A$1:$C$5”. Khi bạn sao chép công thức này sang các ô khác, vùng dữ liệu từ A1 đến C5 sẽ không thay đổi.
3. Tại sao nên cố định công thức và vùng dữ liệu trong Excel?
Cố định công thức và vùng dữ liệu trong Excel giúp bạn tránh sai sót khi sao chép công thức hoặc di chuyển dữ liệu. Bằng cách cố định, bạn đảm bảo rằng các liên kết và tính toán vẫn chính xác khi bạn làm thay đổi trong bảng tính của mình. Điều này giúp tăng tính chính xác và hiệu suất làm việc của bạn trong Excel.
TỔNG KẾT:
Chúng ta đã khám phá cách cố định công thức và vùng dữ liệu trong Excel để tối ưu hóa công việc và tiết kiệm thời gian. Với những bước đơn giản này, bạn có thể tránh sai sót và đảm bảo tính chính xác của công thức và dữ liệu trong bảng tính của mình. Hy vọng những kiến thức này sẽ giúp ích cho bạn trong công việc hàng ngày với Excel. Hãy chia sẻ bài viết này để người khác cũng có thể học hỏi và tiến bộ trong việc sử dụng Excel.
