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 bạn sử dụng cách cố định ô trong Excel 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ụ:
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.
>> Xem thêm: Cách so sánh dữ liệu 2 cột trong excel bằng Hàm chuẩ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:
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:
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 Worth:
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ị 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 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.
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:
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.
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:
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
– Ok 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.
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á.
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ả.
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.
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.
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 Dwelling -> Alignment -> click on vào mũi tên nhỏ ở góc bên phải.
Khóa giá trị trong Excel – Hình 1
– Bước 3: Trong cửa sổ Format Cells, bạn chọn Tab Safety. Sau đó bạn kích chuột vào vào hộp kiểm Locked. Sau đó nhấn OK để lưu lại.
Khóa giá trị trong Excel – Hình 2
– Bước 4: Trên thanh công cụ, tiếp tục chọn Evaluate và chọn Shield Sheet để khóa trang tính đang tháo tác.
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:
+ Choose locked cells: Chọn các ô khóa.
+ Choose 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.
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.
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 on-line 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 giảng viên Nguyễn Ngọc Linh đ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 cách cộng – trừ ngày tháng năm trong Excel
Tags: Excel