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

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

Trong Excel, khi bạn sử dụng công thức có tham chiếu, địa chỉ ô sẽ tự điều chỉnh khi bạn di chuyển đến một vùng khác hoặc sao chép công thức đến ô khác. Trong nhiều trường hợp, nếu như bạn có thể muốn cố định ô trong excel mà không biết phải làm sao. Đừng lo lắng, trong bài viết này, PKMacbook.com sẽ hướng dẫn cách cố định công thức trong Excel, cố định vùng dữ liệu trong Excel.

✅ Tham chiếu tương đối ⭐ Là chiếu mặc định khi chọn vùng dữ liệu trong Excel. Với tham chiếu tương đối thì giá trị tham chiếu sẽ thay đổi theo hàng hay cột, tức là khi bạn thay đổi vị trí theo hàng hay cột thì giá trị tham chiếu cũng sẽ thay đổi theo.
 Tham chiếu tuyệt đối – Là tham chiếu sẽ cố định giá trị tham chiếu theo hàng, cột hoặc cả hàng và cột. Có nghĩa là khi bạn đã cố định giá trị tham chiếu theo hàng, cột hoặc cả hàng và cột thì khi thay đổi vị trí nó vẫn giữ nguyên giá trị tham chiếu đó.

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, bạn muốn cố định hàng hoặc cột bạn chỉ cần khóa giá trị của địa chỉ ô tương ứng.

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:

Xem thêm :  50+ Hình xăm chữ La Mã đẹp nhất

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 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):

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 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:

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 bạn 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 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:

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 giá trị sai:

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

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 PKMacBook.com sẽ giới thiệu các bạn cách tính nhanh bằng cách cố định dữ liệu.

Xem thêm :  Top 5 phần mềm OCR hỗ trợ tiếng Việt tốt nhất

– 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):

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

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á:

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

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

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.

Xem thêm :  Thêm dấu chấm vào dãy số trong Excel

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.

Cố định ngăn để khóa các hàng và cột

Để duy trì hiển thị một khu vực của trang tính trong khi bạn cuộn tới một khu vực khác của trang tính, hãy đi đến tab Xem, nơi bạn có thể Cố định Ngăn để khóa vị trí các hàng và cột cụ thể hoặc bạn có thể Tách ngăn để tạo các cửa sổ riêng của cùng một trang tính.
Cố định hàng hoặc cột
Cố định cột đầu tiên
Chọn Xem > Cố định ngăn > Cố định cột đầu tiên.
Đường mờ xuất hiện giữa Cột A và B cho thấy rằng cột đầu tiên được cố định.
Cố định hai cột đầu tiên
Chọn cột thứ ba.
Chọn Xem > Cố định ngăn > Cố định ngăn.
Cố định cột và hàng
Chọn ô bên dưới hàng và bên phải cột bạn muốn hiển thị khi cuộn.
Chọn Xem > Cố định ngăn > Cố định ngăn.

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ành công!

Nguồn tham khảo từ Internet

Xem thêm bài viết thuộc chuyên mục: Thủ Thuật Tại Website Pkmacbook.com