Các hàm trong Excel giúp bạn tính toán nhanh chóng và hiệu quả. Nếu các bạn biết thêm thủ thuật cố định tọa độ tham chiếu nữa thì việc sử dụng hàm sẽ càng hiệu quả hơn. Hãy cùng chúng mình tìm hiểu ngay trong bài viết dưới đây nhé.
Cố định tọa độ tham chiếu trong hàm Excel
Tại sao cần cố định tọa độ tham chiếu trong hàm Excel?
Trong hầu hết các trường hợp sử dụng hàm để tính toán trên Excel thì chúng ta đề nạp giá trị vào hàm bằng tham chiếu ô. Khi chúng ta copy công thức từ dòng phía trên xuống dòng phía dưới thì tọa độ sẽ tự động tăng dần.
Ví dụ, ban đầu chúng mình có một bảng tính với công thức ở ô C2 là: = IF[B2 = E2, 500, 300].
Khi công thức này được sao chép xuống ô C3 thì tọa độ sẽ tự động tăng lên, biến công thức thành: = IF[B3 = E3, 500, 300].
Tuy nhiên, điều này là không đúng vì chúng ta cần so sánh giá trị của ô B3 với ô E2, không phải ô E3. Do đó, chúng mình cần cố định tọa độ của ô E2 để nó không thay đổi.
Chúng ta có thể rút ra lý do cần cố định tọa độ tham chiếu là: Cố định tọa độ giúp hàm Excel hoạt động đúng theo mong muốn của người dùng.
Xem thêm: 4 điều cần biết về tham chiếu tuyệt đối trong Excel
Cách cố định tọa độ tham chiếu của hàm Excel
Trong Excel, khi muốn cố định tham chiếu trong công thức hàm thì chúng ta sẽ sử dụng ký tự đặc biệt là: $. Bạn có thể gọi ký tự này là dấu đô la cho dễ nhớ vì nó cũng là ký hiệu của đồng USD.
Quay lại với ví dụ ở trên, để cố định tọa độ của ô E2 trong công thức hàm IF thì các bạn làm rất đơn giản như sau:
Bôi đen ký tự E2 trong công thức hàm rồi bấm phím F4. Có một số máy tính sẽ cần phải biết Fn + F4 thì mới thực hiện được thao tác này. Nếu bạn bấm F4 mà không thấy có gì thay đổi thì hãy thử cách bấm Fn + F4 nhé. Trong trường hợp cả 2 cách đều không được thì các bạn có thể nhập trực tiếp ký hiệu này từ bàn phím máy tính nhé.
Khi đó công thức sẽ chuyển thành: = IF[B2 = $E$2, 500, 300].
Trong hình ảnh trên các bạn thể thấy: Dấu $ đặt trước chữ E có nghĩa là cố định cột E, còn chữ $ đặt trước số 2 có nghĩa là cố định dòng 2. Bây giờ khi các bạn sao chép công thức xuống các dòng dưới thì chỉ có tọa độ cột B tăng còn tọa độ cột E được giữ nguyên ở ô E2. Các bạn có thể thấy công thức ở các dòng dưới sẽ lần lượt thay đổi thành:
- = IF[B3 = $E$2, 500, 300]
- = IF[B4 = $E$2, 500, 300]
- = IF[B5 = $E$2, 500, 300]
Các bạn chỉ cần nhớ nguyên tắc rất đơn giản khi cố định tọa độ tham chiếu trong Excel là: Đặt dầu $ trước đối tượng nào thì đối tượng đó được cố định. Trong đó tọa độ cột được ký hiệu bằng chữ cái, tọa động hàng được ký hiệu bằng số.
Về việc bấm phím F4 để cố dịnh tọa độ tham chiếu trong công thứ hàm Excel thì các bạn cần nhớ như sau:
- Sau khi bôi đen tọa độ cần cố định, bấm F4 một lần thì Excel sẽ cố định cả dòng và cột. Điều đó có nghĩa là tọa độ ô sẽ có dạng tương tự như: $E$2.
- Sau khi bôi đen tọa độ ô, bấm phím F4 hai lần thì Excel sẽ chỉ cố định hàng. Khi đó tọa độ ô trong công thức sẽ được chuyển thành dạng tương tự như: E$2.
- Sau khi bôi đen tọa độ ô, bấm phím F4 ba lần thì Excel sẽ cố định cột. Lúc này tọa độ ô trong công thức sẽ được chuyển thành dạng là: $E2.
- Nếu vẫn tiếp tục bôi đen tọa độ tham chiếu rồi bấm phím F4 đến lần thứ tư thì các dầu $ sẽ được bỏ đi hết có nghĩa là không cố định tọa độ nữa.
Nói chung, phím F4 là một phím tắt Excel rất hữu dụng trong việc cố định tọa độ tham chiếu cho hàm Excel. Phím này còn nhiều công dụng khác mà các bạn có thể tìm hiểu trong bài viết trước đây của chúng mình.
Xem thêm: Hướng dẫn sử dụng 5 phím tắt cho phím F4 trong Excel
Kết luận
Như vậy, qua bài viết này các bạn đã nắm được kiến thức về nguyên tắc cố định tọa độ tham chiếu trong Excel. Các bạn mới bắt đầu học Excel nên nhớ kỹ những quy tắc này để sử dụng hàm hiệu quả hơn nhé.
Nếu các bạn muốn học riêng về các hàm trong Excel theo từng nhóm hàm với ứng dụng cụ thể thì hãy đăng ký khóa học dưới đây:
Khóa học Thành thạo HÀM trong Excel cho công việc
Khóa học sẽ giúp các bạn thành thao 75+ hàm thông dụng trong Excel, tiết kiệm được 35% thời gian làm việc mỗi ngày. Các bạn không chỉ được học lý thuyết, công thức hàm mà còn được giải thích rõ về cách sử dụng và ứng dụng của từng hàm qua bài tập thực hành.
Giảng viên sẽ hỗ trợ, giải đáp mọi thắc mắc của các bạn xuyên suốt khóa học. Khóa học có giá trị sử dụng trọn đời nên bạn có thể học bất cứ khi nào bạn muốn. Ngay cả khi đã học xong rồi bạn vẫn có thể học lại nhiều lần mà không mất thêm bất kỳ chi phí nào khác. Chúc các bạn luôn họp tập hiệu quả và thành công trong công việc!
Cùng tham gia cộng đồng hỏi đáp về chủ đề Excel Cơ Bản
Ký tự $ trong các công thức Excel có ý nghĩa như thế nào.
Đây là một trong những ký tự phổ biến nhất khi áp dụng các công thức của Excel, mục đích của nó là để cố định một ô hay một vùng dữ liệu trong excel.
Thông thường ứng dụng của việc cố định công thức áp dụng khi viết hàm.
Bạn viết hàm, bạn kéo công thức, muốn một vài chỉ tiêu cố định, một vài chỉ tiêu di chuyển. Vậy càng phải hiểu và dùng linh hoạt thao tác này.
Một số hàm thường xuyên dùng đến việc cố định dòng cột
- Hàm Vlookup: Áp dụng trong trường hợp tự động tên hàng, DVT hay tên KH, Địa chỉ khi đã có mã. Chúng ta sẽ áp dụng việc cố định bảng dữ liệu tìm kiếm
- Hàm Sumif/ Sumifs: Cố định vùng dữ liệu tính tổng/ Vùng điều kiện
- Hàm Match: Cố định điều kiện tham chiếu và vùng tham chiếu
- Hàm If: Cố định điều kiện so sánh
- Rất nhiều hàm khác tại: //excel.webkynang.vn
Hầu hết các ô, vùng không cần cố định khi công thức ấy là duy nhất. Chỉ cần công thức ấy cần phải copy, di chuyển đến các vị trí khác, có thể áp dụng thì cần cố định
Phím tắt sử dụng để cố định ô/ vùng dữ liệu trong excel:
Cho bảng dữ liệu từ A1:D10, Tính tổng số liệu cột D [cột thành tiền]
Tại D11 =sum[D1:D10
+ F4 lần 1 công thức sẽ tự động cô định cả dòng cả cột thành =sum[$D$1:$D$10
+F4 lần 2 công thức sẽ tự động cố định dòng thành =sum[D$1:D$10
+ F4 lần 3 công thức sẽ tự động cố định cột thành =sum[$D1:$D10
Để kết thúc công thức, ta đóng ngoặc đơn lại và nhấn enter.
Phân tích
Một ô trong Excel có địa chỉ:
Tên Cột_Số Thứ Tự Dòng : [A1]
Một Vùng dữ liệu gồm: [A1:A100]
Địa chỉ ô bên trái đầu tiền : Địa chỉ ô cuối cùng bên phải
Ví dụ:
– A1: ô A1 sẽ cho chúng ta biết nó thuộc Cột A và dòng số 1
– AZ1000 : ô AZ1000 sẽ cho chúng ta biết nó thuộc Cột AZ và dòng số 1000
– B2:D15: Đây là một vùng dữ liệu được xác định bằng địa chỉ 2 ô đầu tiên bên trái [B2] và địa chỉ ô cuối cùng bên phải [D15]
Như vậy, khi viết công thức có sử dụng địa chỉ một ô hay một vùng dữ liệu để làm tham chiếu hoặc làm điều kiện thì chúng ta cần phải cố định vùng dữ liệu hoặc ô đó để có thể copy công thức sang các ô khác mà không làm thay đổi vùng dữ liệu/ ô trong công thức.
Các kiểu cố định vùng dữ liệu
Có 3 kiểu cố định ô/ vùng dữ liệu:
– A$1: Cố định dòng mà không cố định cột. Khi copy công thức sang một cột khác thì chỉ có địa chỉ cột thay đổi, trong khi số thứ tự dòng không thay đổi.
– $A1: Cố định cột mà không cố định dòng. Khi copy công thức sang một dòng khác thì địa chỉ cột không thay đổi, trong khi số thứ tự dòng thay đổi.
– $A$1: Cố định cả dòng và cột. Khi copy công thức sang một cột khác thì cả địa chỉ cột và số thứ tự dòng đều không thay đổi.
Để cố định một vùng dữ liệu ta áp dụng nguyên tắc tương tự:
B$2:D$15
$B2:$D15
$B$2:$D$15