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ẽ thay đổi 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ố đị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:
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:
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:
- 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]:
- 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:
- 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á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:
- Và như vậy kết quả tính toán sẽ trả lại giá trị sai:
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 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 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]:
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á:
Bước 3: Nhấn Enter được kết quả.
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ả.
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ả.
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.
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.
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!
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: 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:
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 Value:
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
- 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.
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 Home -> Alignment -> click 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 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.
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.
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.
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 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!
Tags: Excel