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

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

Mục lục

Bạn thường nhìn thấy trong Excel các công thức sẽ có ký tự $ được sử dụng rất nhiều. Mục đích của nó dùng để cố định ô hay hàm. Ví dụ bạn muốn nhân toàn bộ bảng tính với ô A1 thôi chẳng hạn. Bài viết sau trong chuỗi bài giảng về tin học văn phòng từ Unica sẽ giúp bạn biết cách cố định hàm, ô trong Excel nhé.

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

Để cố định ô hay cả vùng dữ liệu trong bảng tính Excel

Một ô trong Excel có địa chỉ: Tên cột _ thứ tự của dòng

- Ví dụ A5 là cột A dòng 5

Một vùng dữ liệu gồm: Địa chỉ ô bên trái đầu tiên _ địa chỉ ô bên phải cuối cùng của vùng dữ liệu

- Ví dụ: A1:D7 tức là vùng dữ liệu bắt đầu từ ô A dòng 1, kết thúc tại ô D dòng 7.

Ý nghĩa:

Khi bạn sao chép công thức tính toán tại ô hay vùng dữ liệu, việc chúng ta cố định ô hay vùng dữ liệu đó sẽ không làm thay đổi vùng dữ liệu trong công thức.

2. Các kiểu cố định

Cố định dòng và cố định cột

Ví dụ: $A$1: Cố định dòng 1 cột A. Khi bạn di chuyển công thức đến các vùng hay ô khác để tính toán thì dòng 1 cột A sẽ được cố định và không thay đổi.

Cố định dòng, không cố định cột

 Ví dụ: A$1: Khi copy công thức sang 1 ô khác thì chỉ có giá trị cột thay đổi còn giá trị hàng được cố định.

Cố định cột không cố định dòng

Ví dụ: $A1 Khi copy công thức sang 1 ô khác thì chỉ có giá trị hàng thay đổi còn giá trị cột được cố định.

Ta xét ví dụ:

cach-co-dinh-ham-trong-excel.png
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.

>> Xem thêm: 4 Cách đọc số thành chữ trong Excel đơn giản nhất

3. Hướng dẫn cố định một giá trị bất kỳ trong công thức

Bạn tham khảo ví dụ mà Unica cung cấp như 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:

Co-dinh-mot-gia-tri-bat-ky-trong-cong-thuc

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

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

Co-dinh-mot-gia-tri-bat-ky-trong-cong-thuc

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

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

Co-dinh-mot-gia-tri-bat-ky-trong-cong-thuc

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

- 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ị đơn giá ở ô C5.

- Bước 3: Chọn công thức cần cố định rồi nhấn phím F4 lần 1 là có thể đổi địa chỉ C4 thành địa chỉ tuyệt đối.

Co-dinh-mot-gia-tri-bat-ky-trong-cong-thuc

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

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

Co-dinh-mot-gia-tri-bat-ky-trong-cong-thuc

Cố định một giá trị bất kỳ trong công thức - Hình 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:

Co-dinh-mot-gia-tri-bat-ky-trong-cong-thuc

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

- Để 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. 

Co-dinh-mot-gia-tri-bat-ky-trong-cong-thuc

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

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

Co-dinh-mot-gia-tri-bat-ky-trong-cong-thuc

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

2. 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 ô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.

co-dinh-cac-gia-trị-trong-cong-thuc

Cố định các giá trị trong công thức - Hình 1

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

co-dinh-cac-gia-trị-trong-cong-thuc

Cố định các giá trị trong công thức - Hình 2

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

co-dinh-cac-gia-trị-trong-cong-thuc

Cố định các giá trị trong công thức - Hình 2

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

co-dinh-cac-gia-trị-trong-cong-thuc

Cố định các giá trị trong công thức - Hình 4

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

co-dinh-cac-gia-trị-trong-cong-thuc

Cố định các giá trị trong công thức - Hình 4

- Để tìm hiểu chi tiết hơn nữa về các thao tác cố định hàm hay cố định ô trong Excel, bạn đọc có thể tham khảo Video bài giảng dưới đây:

Hướng dẫn thực hiện cách cố định ô trong Excel

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

Bạn thực hiện như 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.

cach-khoa-gia-tri-trong-Excel

Khóa giá trị trong Excel - Hình 1

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

cach-khoa-gia-tri-trong-Excel

Khóa giá trị trong Excel - Hình 2

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

cach-khoa-gia-tri-trong-Excel

Khóa giá trị trong Excel - Hình 3

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

cach-khoa-gia-tri-trong-Excel

Khóa giá trị trong Excel - Hình 4

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

cach-khoa-gia-tri-trong-Excel

Khóa giá trị trong Excel - Hình 5

Trên đây chúng ta đã nắm được cách cố định hàm trong Excel rồi. 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. 

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

Đánh giá :

Tags: Excel