Hướng dẫn cách cố định hàm trong excel đơn giản, nhanh chóng

Trong quá trình làm việc với excel, bạn sẽ thường xuyên phải tham chiếu đến một vùng dữ liệu nào đó. Khi này, việc cố định ô đóng vai trò vô cùng quan trọng và cần thiết, nhất là đối với những bảng tính có nhiều hàng và cột. Để thuận tiện trong quá trình làm việc với bảng tính excel bạn cần phải biết cách cố định hàm trong excel. Trong bài viết này, Unica sẽ hướng dẫn bạn cách cố định công thức trong excel đơn giản và nhanh chóng nhất, cùng khám phá nhé.

1. Mục đích của việc cố định hàm trong Excel

Để cố định hàm, cột hay một vùng dữ liệu nào đó trong excel bạn sẽ dùng dấu $. Mục đích của việc cố định hàm trong quá trình làm việc với excel là để:

1.1. Bảo đảm tính chính xác khi sao chép công thức

Mục đích của việc cố định hàm để là để giữ nguyên tham chiếu đến một ô hoặc một vùng dữ liệu khi sao chép công thức sang các ô khác. Điều này giúp đảm bảo tính chính xác khi sao chép, không gây ra lỗi nếu bạn không muốn thay đổi tham chiếu.

Cố định hàm trong excel giúp đảm bảo tính chính xác cho trang tính khi sao chép công thức

1.2. Giảm thời gian và công sức

Việc cố định hàm excel khi sao chép cũng giúp giảm thời gian và công sức đáng kể trong quá trình làm việc với bảng tính excel. Bởi khi bạn cố định hàm, việc tính toán chính xác thì bạn sẽ không mất thời gian và công sức để làm lại. Điều này cực kỳ thuận tiện và tiện lợi.

1.3. Bảo đảm tính ổn định của các công thức phức tạp

Các công thức phức tạp thường bao gồm nhiều tham chiếu đến các ô hoặc vùng dữ liệu khác nhau. Nếu bạn không cố định hàm, Excel sẽ tự động thay đổi tham chiếu khi bạn sao chép công thức. Điều này dẫn đến các lỗi hoặc kết quả không chính xác.

Cố định hàm sẽ giúp giữ nguyên tham chiếu đến các ô hoặc vùng dữ liệu khi bạn sao chép công thức. Điều này sẽ giúp đảm bảo rằng các công thức phức tạp của bạn sẽ tiếp tục hoạt động chính xác khi bạn sao chép chúng sang các ô khác.

1.4. Đảm bảo tính toán chính xác khi dữ liệu thay đổi

Nếu bạn biết cách cố định trong excel thì bạn sẽ thực hiện các thao tác tính toán được dễ dàng, nhanh chóng, linh hoạt mà vẫn đảm bảo tính chính xác cao cao khi thay đổi dữ liệu. Điều này rất cần thiết và hữu ích trong quá trình làm việc với excel.

Cố định hàm giúp bảo tính toán chính xác khi dữ liệu thay đổi

1.5. Tránh lỗi khi dữ liệu được sắp xếp lại

Cố định dữ liệu trong Excel giúp tránh lỗi khi dữ liệu được sắp xếp lại. Khi bạn sắp xếp dữ liệu, Excel sẽ tự động thay đổi thứ tự của các ô trong vùng dữ liệu. Nếu hàm của bạn tham chiếu đến một ô hoặc một vùng dữ liệu không còn ở vị trí ban đầu, hàm sẽ trả về lỗi.

Cố định hàm sẽ giúp giữ nguyên tham chiếu đến các ô hoặc vùng dữ liệu khi bạn sắp xếp dữ liệu. Điều này sẽ giúp đảm bảo rằng hàm của bạn sẽ tiếp tục hoạt động chính xác sau khi dữ liệu được sắp xếp lại.

1.6. Bảo mật thông tin nhạy cảm

Khi bạn cố định hàm, Excel sẽ không tự động thay đổi tham chiếu khi bạn sao chép công thức. Điều này giúp ngăn chặn việc ai đó vô tình hoặc cố ý thay đổi thông tin nhạy cảm. Ngoài ra, việc cố định hàm trong excel còn giúp ngăn chặn việc truy cập trái phép vào các ô hoặc vùng dữ liệu chứa thông tin nhạy cảm. Cố định hàm là một kỹ năng quan trọng cần nắm vững để có thể sử dụng Excel hiệu quả.

Cố định hàm trong excel để bảo mật thông tin nhạy cảm

2. Cách cố định hàm trong excel

Để cố định hàm trong excel bạn dùng ký hiệu $. Có 3 kiểu cố định phổ biến nhất đó là:

- B$1: Ký tự $ nằm ở giữa số và chữ. Chức năng của nó là để cố định dòng nhưng không cố định cột. Khi bạn copy công thức trong Excel sang một cột khác thì địa chỉ cột sẽ thay đổi còn số thứ tự dòng thì sẽ không thay đổi.

- $B1: Ký tự $ nằm ở trước số và chữ. Chức năng của kiểu cố định này là để cố định cột nhưng không cố định dòng. Khi bạn copy công thức sang một dòng khác thì địa chỉ số thứ tự của dòng sẽ thay đổi nhưng địa chỉ cột thì không.

- $B$1: Ký tự $ nằm ở trước số và trước chữ. Chức năng cố định tất cả dòng và cột. Khi bạn thực hiện copy công thức sang một cột khác thì cả địa chỉ số thứ tự dòng và cột đều không thay đổi.

Tham khảo ví dụ sau để biết cách giữ ô tham chiếu cố định trong excel

Ta xét ví dụ:

Cách cố định hàm trong excel

Bạn có bảng dữ liệu như hình trên, yêu cầu:

- Tính thành tiền của các mặt hàng, biết: Giá niêm yết của tất cả các mặt hàng này đều là 230, vì vậy bạn cần cố định cột C dòng 4. 

- Bạn nhập công thức =PRODUCT(B4,$CD4), khi kéo thả chuột đến các ô bên dưới thì $CD4 vẫn cố định và không bị thay đổi, các giá trị khác như B4, B5,... sẽ thay đổi vậy đấy chính là cách cố định giá trị trong excel.

>>> Tất cả đều có trong cuốn sách "HƯỚNG DẪN THỰC HÀNH EXCEL TỪ CƠ BẢN ĐẾN NÂNG CAO"

ĐĂNG KÝ MUA NGAY

3. Cách cố định công thức trong excel

Trong bài viết này Unica sẽ hướng dẫn bạn 2 cách để cố định trong excel đó là:

Cố định một giá trị bất kỳ trong công thức

Để cố định một giá trị bất kỳ trong công thức bạn hãy tham khảo ví dụ và cách thực hiện sau

- Bước 1: Bảng dữ liệu cần tính số tiền cần thanh toán cho đại lý cấp 1 bằng cách lấy giá trị số lượng trong cột B x với đơn giá ở ô C5. Bạn nhập công thức = B5*C5:

Nhập công thức vào bảng

- Bước 2: Nhấn Enter chúng ta sẽ thu được kết quả tại ô C7, khi kéo công thức xuống các ô khác sẽ xảy ra lỗi #VALUE như sau:

Nhấn Enter xảy ra lỗi

- Nguyên nhân xảy ra lỗi #VALUE là 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:

Do C6 không có giá trị nên xảy ra lỗi

- Do bài toán chỉ yêu cần tính thành tiền cho đơn vị đại lý cấp 1 nên bạn cần cố định giá trị trong Excel của đơn giá ở ô C5, như vậy bạn đã cố định số trong excel.

- Bước 3: Cách cố định công thức trong Excel bạn cần chọn công thức cần cố định trong Excel rồi 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ông thức trong excel

- Bước 4: Cố định hàng 5 bằng cách nhấn phím F4 lần 2.

Cố định hàng 5

- Bước 5: Bảng dữ liệu trả về kết quả đúng khi bạn tính thành tiền cho đại lý cấp 1:

Kết quả trả về

- Để chuyển sang cố định cột C mà không cố định hàng thứ 5 của C5, bạn nhấn F4 lần 3. 

Cố định cột C

- Như vậy kết quả sẽ trả lại giá trị sai:

Kết quả trả về giá trị sai

Cố định các giá trị trong công thức

Vẫn như ví dụ phía trên mà chúng tôi cung cấp, yêu cầu của đề bài này là tính tiền cho đại lý các cấp với mỗi đại lý là một đơn giá khác nhau. 

Công thức chúng sử dụng là: Bx*K5.

Trong đó:

- X chạy từ 7 tới n là hàng

- K là cột tương tương với các đại lý. 

Cách thực hiện như sau:

- Bước 1: Cố định cột B của B7.

Nhập công thức =B7 vào ô cần tính tiền của đại lý cấp 1 (ô C7). Sau đó nhấn F4 3 lần để cố định cột B của B7.

Nhập công thức vào bảng

- Bước 2: Cố định hàng 5 của K5.

Đầu tiên bạn nhấn dấu nhân trong công thức, sau đó kích chọn giá trị ô C5. Cuối cùng nhấn F4 2 lần để khóa hàng của địa chỉ đơn giá. 

Cố định hàng 5

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

Nhấn enter

- Bước 4: Để nhận kết quả ở những ô tiếp theo, bạn chỉ cần sao chép công thức các giá trị trong cột đại lý cấp 1. 

Áp dụng cho những hàng sau

- Bước 5: Đặt trỏ chuột vào ô giá trị cuối cùng của đại lý cấp 1, sau đó nhấn giữ chuột và kéo sang phải để hiển thị kết quả cho các đại lý cấp 2, cấp 3. 

Cố định các giá trị

4. Hướng dẫn cách khóa giá trị trong Excel để bảo vệ dữ liệu không bị thay đổi

Để thực hiện cách khóa giá trị trong Excel để bảo vệ dữ liệu không bị thay đổi bạn làm theo các bước sau:

- Bước 1: Mở File Excel và chọn ô hoặc vùng dữ liệu mà bạn muốn xóa.

- Bước 2: Trên thanh công cụ, chọn Home -> Alignment -> click vào mũi tên nhỏ ở góc bên phải.

Thao tác đầu tiên để khóa giá trị trong Excel 

- Bước 3: Trong cửa sổ Format Cells, bạn chọn Tab Protection. Sau đó bạn kích chuột vào vào hộp kiểm Locked. Sau đó nhấn OK để lưu lại.

Chọn Locked

- Bước 4: Trên thanh công cụ, tiếp tục chọn Review và chọn Protect Sheet để khóa trang tính đang tháo tác. 

Chọn Protect Sheet

- Bước 5: Trong khung Password to unprotect sheet bạn đặt mật khẩu bảo vệ. Bạn có thể lựa chọn các hình thức khóa như sau:

+ Select locked cells: Chọn các ô khóa.

+ Select unlocked cells: Chọn các ô không khóa.

+ Delete columns: khóa không cho xóa các cột.

+ Delete rows: khóa không cho xóa các hàng.

Nhấn OK để tiếp tục.

Đặt mật khẩu bảo vệ

- Bước 6: Nhấn mật khẩu lần nữa rồi nhấn OK để hoàn tất. 

Nhấn OK để hoàn tất

5. Kết luận

Trên đây là hướng dẫn chi tiết cách cố định hàm trong excel đơn giản và nhanh chóng nhất cho các bạn tham khảo. Hy vọng với những chia sẻ này, các bạn đã biết cách cố định hàm để bảo đảm tính chính xác cho bảng tính của mình, từ đó có quá trình làm việc với excel nhanh chóng và thuận tiện hơn. Tham khảo thêm những khoá học Excel online nâng cao đến từ những chuyên gia hàng đầu Unica trực tiếp giảng dạy. Hoặc bạn cũng có thể tham khảo thêm cuốn sách Excel của đội ngũ giảng viên Unica đang được nhiều người quan tâm.

 Cảm ơn các bạn đã theo dõi bài viết và chúc thành công!

>>> Xem thêm: Hướng dẫn 3 cách cố định tiêu đề trong excel chi tiết nhất

>>> Xem thêm: Hướng dẫn cách cố định hàng trong excel chi tiết nhất