Cách đánh địa chỉ tuyệt đối và địa chỉ tương đối trong Excel
Duy Nguyễn Bá 01/11/2021
Địa chỉ tuyệt đối, địa chỉ tương đối trong Excel là 1 khái niệm còn khá xa lạ đối với nhiều người. Vậy nên ở bài viết này mình sẽ giúp các bạn giải đáp thắc mắc cũng như cách đánh địa chỉ tuyệt đối và tương đối trong Excel 1 cách chính xác và hiệu quả nhất nhé. Còn chần chờ gì nữa hãy bắt đầu thôi nào!!
Bài viết được thực hiện trên laptop hệ điều hành Windows với phiên bản Excel 2016, ngoài ra bạn cũng có thể thực hiện được trên các phiên bản Excel 2003, 2007, 2010, 2013, 2019 và cả trên MacOS với thao tác tương tự.
I. Địa chỉ tuyệt đối, địa chỉ tương đối trong Excel là gì và cách nhận biết
Địa chỉ tuyệt đối trong Excel: Là địa chỉ được cố định khi bạn thực hiện sao chép công thức. Thường có ký hiệu là “$” trước mỗi địa chỉ cột và dòng.
Địa chỉ tương đối trong Excel: Là địa chỉ không được cố định và sẽ bị thay đổi khi sao chép công thức.
- Cả 2 loại địa chỉ này này rất cần thiết trong việc thực hiện các công thức, các hàm.
- Việc hiểu rõ cách hoạt động của 2 loại địa chỉ này sẽ giúp bạn thực hiện các công thức và hàm chính xác, hiệu quả hơn rất nhiều.
II. Cách đánh địa chỉ tuyệt đối trong Excel
Để đánh được địa chỉ tuyệt đối trong Excel bạn hãy sử dụng phim F4 trên bàn phím nhé!
Đối với MacOS bạn hãy sử dụng tổ hợp phím Command + T nhé!
Một số trường hợp của địa chỉ tuyệt đối trong Excel:
| Số lần nhấn F4 | Địa chỉ tuyệt đối |
| Nhấn 1 lần F4 | Tuyệt đối cho cả cột và dòng [$A$3] |
| Nhấn 2 lần F4 | Chỉ tuyệt đối cho cột [A$3] |
| Nhấn 3 lần F4 | Chỉ tuyệt đối cho dòng [$A3] |
| Nhấn 4 lần F4 | Trở lại địa chỉ tương đối [A3] |
III. Cách áp dụng địa chỉ tuyệt đối và tương đối trong Excel vào công thức, hàm
Địa chỉ tuyệt đối và địa chỉ tương đối thường sẽ được áp dụng trong các hàm mà cần trích xuất dữ liệu từ những bảng liên quan như hàm tìm kiếm HLOOKUP, VLOOKUP, INDEX, MATCH, hàm tham chiếu SUMIF, COUNTIF, RANK,….
Ví dụ: Dùng hàm Hlookup để tìm kiếm dữ liệu bảng có sẵn sử dụng địa chỉ tuyệt đối.
Như bạn có thể thấy trong hình thì công thức sẽ là =HLOOKUP[$A$2;$A$7:$C$8;2]
Trong đó:
- $A$2 [Mũi tên đỏ]: địa chỉ A2 là địa chỉ tuyệt đối để khi copy hay áp dụng xuống bên dưới sẽ không bị thay đổi.
- $A$7:$C$8 [Mũi tên vàng]: Bảng chứa địa chỉ A2 cần tìm kiếm cũng phải là địa chỉ tuyệt đối để khi copy xuống các hàng bên dưới sẽ không bị thay đổi địa chỉ dẫn đến lỗi và sai xót.
- Số 2 [Mũi tên xanh lá]: Là vị trí của dòng thứ 2 trong bảng.
20.355 lượt xem
Câu 30: Chọn cụm từ bên dưới để điền vào chỗ trống sao cho đúng: “Trên trang tính, khi sao chép một ô có nội dung là công thức chứa địa chỉ, các địa chỉ sẽ được ……………..để giữ nguyên vị trí tương đối giữa các ô chứa công thức và ô địa chỉ trong công thức”.
A. giữ nguyên B. xóa mất C. thay thế bằng giá trị mới D. điều chỉnh
Câu 31: Chọn cụm từ bên dưới để điền vào chỗ trống sao cho đúng: “Trên trang tính, khi di chuyển nội dung các ô có công thức chứa địa chỉ bằng các lệnh Cut và Paste, các địa chỉ trong công thức……………..; nghĩa là công thức được sao chép y nguyên”.
A. không bị điều chỉnh B. bị xóa mất
C. được thay thế bằng giá trị mới D. bị điều chỉnh
Câu 32: Trên trang tính, tại ô A1=5; B1=10; tại ô C1=A1+B1, sao chép công thức tại ô C1 sang ô D1, thì công thức tại ô D1 là gì?
A. =A1+B1 B. =B1+C1 C. =A1+C1 D. =C1+D1
Câu 33: Trên trang tính, tại ô A1=10; B2=5; tại ô C3=A1+B2, sao chép công thức tại ô C3 sang ô E5, thì công thức tại ô E5 là gì?
A. =A2+B3 B. =C3+D3 C. =C3+D4 D. =B3+C4
Câu 34: Để đóng trang tính, ta thực hiện lệnh nào?
A. File, Save B. File, Open C. File, Close D. File, New
Hãy nêu lợi ích của việc sử dụng địa chỉ ô tính trong công thức?
Địa chỉ trong excel là một khái niệm được sử dụng để xác định một ô [Cell] trong Excel. Ví dụ A2 là địa chỉ của ô ở thuộc cột A và dòng 2.
Đây là một phần rất quan trọng khi làm việc với Excel. Vì chúng ta đều biết Excel rất mạnh để xử lý bảng tính. Chúng ta lập công thức để tính toán thì phải dựa vào địa chỉ này.
Địa chỉ trong excel – Phân loại
Trong Microsoft Excel có 3 loại địa chỉ là địa chỉ tương đối, địa chỉ tuyệt đối và địa chỉ hỗn hợp. Tùy theo yêu cầu cụ thể mà chúng ta sẽ lựa chọn loại địa chỉ cho phù hợp.
Địa chỉ trong excel – Địa chỉ tương đối trong Excel
Là địa chỉ bị thay đổi khi chúng ta thực hiện copy công thức. Đây là địa chỉ mặc định khi chúng ta lập công thức.
Hình bên dưới có 2 địa chỉ tương đối là E63 và D63 được sử đụng dể tính số ngày ở. Trong đó E63 xác định ngày đến và D63 xác định ngày đi của người có stt là 1.
Khi chúng ta thực hiện sao chép công thức thì công thức tính số ngày ở cho người có stt 2 sẽ là E64-D64. Như vậy, chúng ta thấy rằng địa chỉ đã bị thay đổi.
Địa chỉ trong excel – Địa chỉ tuyệt đối trong Excel
Nếu như địa chỉ tương đối bị thay đổi khi chúng ta sao chép công thức thì địa chỉ tuyệt đối LUÔN LUÔN cố định.
Để tạo địa chỉ tuyệt đối, chúng nhấn phím F4; lúc này sẽ có dấu thăng [$] trước và sau số thứ tự cột. Hình biến dưới cho biết F68 là địa chỉ tuyệt đối $F$68.
Khi chúng ta thực hiện sao chép công thức để tính cột thành tiền cho người có stt 2 thì công thức lúc này sẽ là F64-$F$68; nghĩa là F63 thành F64 [địa chỉ tương đối] nhưng $F$68 thì không thay đổi [địa chỉ tuyệt đối]
Địa chỉ trong excel – Địa chỉ hỗn hợp trong Excel
Nếu như địa chỉ tương đối sẽ cố định cột và cố định dòng thì địa chỉ hỗn hợp hoặc cố định cột hoặc cố định dòng. Hình bên dưới thể hiện 2 địa chỉ hỗn hợp, một là cố định dòng [D$21] và một là cố định cột [$C22].
Như vậy đối với địa chỉ đã cố định cột thì khi thực hiện sao chép công thức, cột sẽ không thay đổi. Còn đối với địa chỉ cố định dòng thì dòng sẽ không thay đổi.
Hình sau đây trình bày cách thiết lập các loại địa chỉ trong Excel, cũng như cách chuyển đỗi giữa chúng
Địa chỉ trong excel – Bài tập thực hành
Bài thực hành số 1. Lập công thức tính Số ngày ở và Thành tiền của khách hàng tại một khách sạn ABC.
Bài thực hành số 2. Lâp công thức tại một ô [cell] duy nhất sao cho khi thực hiện sao chép công thức kết quả hiển thị theo đúng yêu cầu của bảng cửu chương.
Lưu ý: Chúng ta chỉ nhập nội dung cho vùng màu vang. Đối với vùng màu xanh, chúng ta sẽ lập công thức.
Trong Exel có hai loại địa chỉ đó là địa chỉ tương đối và địa chỉ tuyệt đối đây là khái niệm không quá xa lạ đối với những người học Excel cũng như làm việc. Trong bài viết dưới đây chúng tôi chia sẻ đến bạn đọc hai khái niệm địa chỉ tương đối và địa chỉ tuyệt đối và công thức. Mời bạn đọc cũng theo dõi.
Có hai loại địa chỉ : tương đối và tuyệt đối . Địa chỉ tương đối và tuyệt đối khác nhau khi được sao chép và điền vào các ô khác. Địa chỉ tương đối thay đổi khi một công thức được sao chép sang một ô khác. Các tài liệu tham khảo tuyệt đối, mặt khác, vẫn không đổi cho dù chúng được sao chép ở đâu.
1. Ý nghĩa của địa chỉ tương đối và tuyệt đối trong Excel
- Địa chỉ tương đối và tuyệt đối trong Excel đóng vai trò vô cùng quan trọng khi bạn thực hiện các công thức, hàm.
- Khi hiểu rõ cách hoạt động của hai hàm này sẽ giúp cho bạn tiết kiệm được nhiều thời gian, công thức để thực hiện các công thức hàm chính xác và hiệu quả nhất.
- Địa chỉ tuyệt đối và địa chỉ tương đối trong Excel thường được áp dụng trong các hàm cần trích xuất dữ liệu như hàm tìm kiếm HLOOKUP, INDEX, VLOOKUP, hàm tham chiếu SUMIF, COUNTIF, RANK.
2. Địa chỉ tương đối trong Excel
Địa chỉ tương đối trong Excel địa chỉ không được cố định và sẽ bị thay đổi khi sao chép công thức.
Theo mặc định, tất cả các địa chỉ tương đối, khi được sao chép trên nhiều ô, chúng thay đổi dựa trên vị trí tương đối của các hàng và cột. Ví dụ: nếu bạn sao chép công thức = A1 + B1 từ hàng 1 sang hàng 2, công thức sẽ trở thành = A2 + B2. Địa chỉ tương đối đặc biệt thuận tiện bất cứ khi nào bạn cần lặp lại phép tính tương tự trên nhiều hàng hoặc cột.
Ví dụ:
- Hình bên dưới có 2 địa chỉ tương đối là C4 và D4 được sử dụng để tính tổng tiền lương cho nhân viên. Trong đó C4 xác định số ngày công và D4 xác định số tiền lương trong một ngày.[ ta dùng hàm nhân]
Ví dụ địa chỉ tương đối và địa chỉ tuyệt đối trong excel
Mô tả về địa chỉ tương đối trong excel
Chúng ta tiến hành nhập công thức nhân bằng hàm tại ô tổng tiền lương. Khi bạn kéo chuột xuống các ô khác thì địa chỉ của các ô thay đổi. Vẫn là hàm nhân nhưng địa chỉ thay đổi thành C5,D5 tương ứng.
Ví dụ địa chỉ tương đối và địa chỉ tuyệt đối trong excel
3. Địa chỉ tuyệt đối trong Excel
Địa chỉ tuyệt đối là địa chỉ mà cả cột và dòng đều không thay đổi khi sao chép công thức. Ký hiệu của địa chỉ này là: $tencot$tendong [ví dụ: $C$3 là địa chỉ tuyệt đối cả cột C và dòng 3].
Ví dụ: Bạn muốn cộng tiền thưởng tháng là 500 nghìn cho nhân viên, trong đó thì 500 nghìn là cố định và không thay đổi. Vậy bạn sẽ sử dụng tham chiếu giá trị tuyệt đối cho cột D dòng 11, sau đó cộng giá trị tuyệt đối này với tổng số tiền tại cột F.
Bạn làm theo công thức: =$D$11+E4.
Sau đó bạn kéo thả chuột xuống các ô tại cột E, thì giá trị tuyệt đối $D11 không hề thay đổi mà vẫn giữ nguyên theo công thức, chỉ có các ô của cột E thay đổi.
Một số trường hợp của địa chỉ tuyệt đối trong Excel được thể hiện như sau:
Địa chỉ tuyệt đối trong Excel
4. Cách áp dụng địa chỉ tuyệt đối với tương đối trong Excel vào công thức hàm
Quan sát ví dụ mà Unica cung cấp, bạn có thể sử dụng hàm Hlookup để tìm kiếm dữ liệu bảng có sẵn sử dụng địa chỉ tuyệt đối.
Công thức áp dụng: =HLOOKUP[$A$2;$A$7:$C$8;2]
Trong đó:
- $A$2 [Mũi tên đỏ]: A2 là địa chỉ tuyệt đối mà khi copy xuống sẽ không bị thay đổi.
- $A$7:$C$8 [Mũi tên vàng]: Bảng chứa địa chỉ A2 cũng là địa chỉ tuyệt đối để không bị thay đổi địa chỉ khi thực hành thao tác copy xuống hàng bên dưới.
- Số 2 [Mũi tên xanh lá]: Vị trí dòng thứ 2 trong bảng Excel.
Ví dụ về địa chỉ tuyệt đối trong hàm Hlookup
Như vậy thông qua bài viết trên đây, Unica đã cùng bạn tìm hiểu về địa chỉ tương đối, tuyệt đối trong Excel.
Hy vọng bài viết trên đây sẽ giúp ích cho bạn.
Chúc bạn thành công!
>> Xem thêm: Cách đổi giờ ra phút trong excel đơn giản và chi tiết nhất
Tags: Excel