Hướng dẫn cách cố định công thức trong Excel, cố định vùng dữ liệu trong Excel

trong excel khi sử dụng công thức có tham chiếu tới 1 vùng dữ liệu nếu bạn sử dụng địa chỉ tương đối công thức và vùng dữ liệu sẽ chỉnh sửa theo khi bạn di chuyển sang ô khác. Ở bài viết này giúp các bạn cố định công thức hoặc vùng dữ liệu được tham chiếu tới trong excel.

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

Để cố định công thức hoặc vùng dữ liệu trong excel sử dụng địa chỉ tuyệt đối hay chính là cách sử dụng dấu $ trong địa chỉ ô để cố định.

trong địa chỉ ô bao gồm chỉ số cột và chỉ số hàng, người chơi muốn cố định hàng hoặc cột người chơi chỉ cần khóa giá trị của địa chỉ ô tương ứng.

Bạn đang xem: Hướng dẫn cách cố định công thức trong Excel, cố định vùng dữ liệu trong Excel

1. cố định 1 rét mướt 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 người chơi chỉ nhập công thức đơn thuần = b5*c5:

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

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:

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

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:

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

– như vậy với công thức và bài toán này người chơi 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 người chơi 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.

– người chơi 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):

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

– với yêu cầu chỉ tính tiền đại lý cấp 1 nên bài người chơi có thể chỉ cần cố định hàng 5 mà không cần cố định cột c, game thủ chỉ cần nhấn phím f4 lần 2 là có thể cố định hàng 5:

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

– với 2 cách cố định trên đều đưa ra kết quả đúng khi game thủ tính thành tiền cho đại lý cấp 1:

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

– các game thủ 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:

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

– và như vậy kết quả tính toán sẽ trả lại rét mướt trị sai:

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

Xem thêm: Cách sửa lỗi ngày tháng trong Excel bị đảo lộn, đảo ngượ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 game thủ 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 bình thường để 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ị chỉnh sửa 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):

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

Xem thêm: Cách tính tỷ lệ phần trăm trong Excel – ví dụ công thức

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 game thủ đã khóa hàng của địa chỉ đơn giá:

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

bước 3: nhấn enter được kết quả.

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

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ả.

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

bước 5: khi kéo tới giá trị ô cuối cùng của cột đại lý cấp 1, game thủ 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ả.

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

game thủ 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 người chơi để ý 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 rét trị hàng của địa chỉ ô tương ứng.

– nhấn f4 lần 3: cố định lạnh lẽo trị cột của địa chỉ ô tương ứng.

trên đây là cách cố định công thức hay chính là việc cố định vùng dữ liệu trong công thức trong excel. chúc các bạn thắng lợi!

Nguồn: https://lava.com.vn
Danh mục: Excel