Hiểu về các tham chiếu ô tuyệt đối, tương đối và hỗn hợp trong MS Excel sẽ giúp bạn làm việc hiệu quả hơn và tiết kiệm rất nhiều thời gian. Hãy để chúng tôi đi sâu vào tìm hiểu tham chiếu ô là gì và các loại tham chiếu ô trong MS Excel hiện có để giúp chúng ta dễ dàng thực hiện công việc
Giới thiệu về tham chiếu ô
Trong Excel, một tham chiếu ô [ví dụ A1] là sự kết hợp của tên cột [i. e. , A] theo sau bởi một số hàng [i. e. , 1]. Theo cài đặt mặc định, mỗi ô trong Excel được biểu thị bằng ký tự cột và số hàng. Trong khi viết công thức, người dùng có thể tham chiếu đến các ô/dải ô, đây là sức mạnh cuối cùng của tham chiếu ô trong MS Excel. Ví dụ: giả sử chúng ta có dữ liệu sau trong ô A1 = 10 và ô A2 = 5. Giả sử bạn nhập công thức là =A1+A2. Sau đó, Excel sẽ tính tổng các giá trị có trong các ô đó là 10+5 = 15
Các loại tham chiếu ô trong MS Excel
Có ba loại tham chiếu ô trong MS Excel
1. Tham khảo tương đối
2. Tham khảo tuyệt đối
3. tham khảo hỗn hợp
1. Tham chiếu ô tương đối trong Excel
Tham chiếu ô tương đối thay đổi khi sao chép hoặc di chuyển công thức hoặc giá trị ô đến một vị trí khác trong trang tính. Các tham chiếu tương đối rất hữu ích khi bạn có một dải ô và bạn muốn các tham chiếu tương đối thay đổi khi bạn sao chép công thức xuống các ô khác
Hãy để chúng tôi hiểu điều này với một ví dụ nhỏ
Dưới đây là hình ảnh hiển thị ba bảng khác nhau. Cái đầu tiên chứa Dữ liệu gốc, cái thứ hai chứa Kết quả xuất hiện bằng công thức [i. e. tham chiếu ô] và bảng thứ ba mô tả công thức mà chúng tôi đã sử dụng [i. e. địa chỉ ô]
1. Trong ô G5 bên dưới, chúng tôi cố gắng liên kết nó với C5
2. Nó cho cùng một giá trị có trong ô C5 tôi. e. số 5
3. Khi bạn sao chép liên kết từ G5 SANG H6, tham chiếu sẽ thay đổi tương đối từ C5 SANG D6
2. Tham chiếu ô tuyệt đối trong Excel
Một tham chiếu ô tuyệt đối không thay đổi trong khi sao chép hoặc di chuyển công thức đến một vị trí khác trong trang tính. Các tham chiếu ô được cố định. Bây giờ bạn sẽ tự hỏi làm thế nào bạn có thể sửa tham chiếu ô?
Để cố định các tham chiếu ô, chúng ta cần thêm dấu Đô la [$] trước tên cột và số hàng bằng cách nhấn phím F4. Phím F4 sẽ cho phép bạn tự động thêm ký hiệu đô la trước tham chiếu ô
Khi một ký hiệu đô la được thêm vào trước tên cột và số hàng, nó sẽ sửa các tham chiếu, vì trong đó các tham chiếu sẽ không thay đổi khi sao chép công thức sang các ô khác
Hãy để chúng tôi hiểu điều này với một ví dụ
- Trong hình bên dưới, trong ô G5, chúng tôi liên kết ô C5. Nhưng chúng tôi muốn cố định tham chiếu ô để nó không thay đổi khi sao chép công thức sang các ô khác. Để sửa các ô, nhấn phím F4, thao tác này sẽ chèn ký hiệu đô la trước tên cột và số hàng
2. Nó trả về cùng một giá trị như trong ô C5. Bây giờ khi bạn sao chép công thức từ G5 sang H5, nó vẫn trả về cùng một giá trị như trong C5, vì tham chiếu hiện đã được cố định và sẽ không thay đổi khi sao chép công thức
3. Tham chiếu ô hỗn hợp trong Excel
Tham chiếu ô hỗn hợp cho phép sửa tên cột hoặc số hàng trong tham chiếu ô. Đó là một cái sẽ tương đối và một cái sẽ cố định
Có hai loại tham chiếu ô hỗn hợp
a. Cố định hàng và cột tương đối [i. e. tham chiếu hàng được cố định nhưng tham chiếu cột thay đổi khi công thức được sao chép sang vị trí khác]
b. Hàng tương đối và Cột cố định [i. e. tham chiếu hàng thay đổi nhưng tham chiếu cột được cố định khi công thức được sao chép sang vị trí khác]. Khi bạn sử dụng các tham chiếu hỗn hợp, ký hiệu đô la sẽ chỉ được cố định trước tên cột hoặc số hàng
Hãy để chúng tôi hiểu điều này với một ví dụ
a. Ví dụ về tham chiếu hỗn hợp- Cố định hàng và cột tương đối
1. Trong ô G5, chúng tôi cố gắng liên kết ô C5, nhưng chúng tôi muốn hàng 5 được cố định và không thay đổi khi chúng tôi sao chép công thức sang vị trí khác, do đó thêm tiền tố ký hiệu đô la trước hàng số 5
2. Bây giờ khi sao chép công thức vào ô H6, bạn sẽ nhận thấy rằng tham chiếu cột được cố định thành “C”, nhưng tham chiếu hàng đã thay đổi từ “5” thành “6”
Excel sử dụng hai loại tham chiếu ô để tạo công thức. Mỗi người đều có mục đích riêng. Đọc tiếp để xác định loại tham chiếu ô sẽ sử dụng cho công thức của bạn.
Tham chiếu ô tương đối
Đây là loại tham chiếu ô được sử dụng rộng rãi nhất trong công thức. Tham chiếu ô tương đối là tham chiếu ô cơ bản điều chỉnh và thay đổi khi được sao chép hoặc khi sử dụng Tự động điền
Thí dụ
=SUM[B5. B8], như hình bên dưới, thay đổi thành =SUM[C5. C8] khi được sao chép sang ô tiếp theo
Tham chiếu ô tuyệt đối
Các tình huống phát sinh trong đó tham chiếu ô phải giữ nguyên khi được sao chép hoặc khi sử dụng Tự động điền. Ký hiệu đô la được sử dụng để giữ hằng số tham chiếu cột và/hoặc hàng
Thí dụ
Trong ví dụ dưới đây, khi tính hoa hồng cho nhân viên kinh doanh, bạn sẽ không muốn ô B10 thay đổi khi copy công thức xuống. Bạn muốn giữ nguyên cả cột và hàng để tham chiếu đến ô chính xác đó. Bằng cách sử dụng $B$10 trong công thức, không thay đổi khi sao chép
Một ví dụ phức tạp hơn
Giả sử bạn cần tính giá các mặt hàng trong kho với hai mức giảm giá khác nhau. Hãy nhìn vào bảng tính dưới đây
Kiểm tra công thức trong ô E4. Bằng cách tạo tham chiếu ô đầu tiên $C4, bạn giữ cho cột không thay đổi khi sao chép ngang, nhưng cho phép hàng thay đổi khi sao chép xuống để phù hợp với giá của các mặt hàng khác nhau giảm xuống. Bằng cách đặt tham chiếu ô cuối cùng là A$12, bạn giữ cho số hàng không thay đổi khi sao chép xuống, nhưng cho phép cột thay đổi và phản ánh chiết khấu B khi sao chép qua. Bối rối?
Sao chép ngang
Đã sao chép xuống
Bây giờ, bạn có thể nghĩ, tại sao không chỉ sử dụng 10% và 15% trong các công thức thực tế? . Có nhiều khả năng là cuối cùng những tỷ lệ phần trăm đó sẽ cần phải được điều chỉnh. Bằng cách tham chiếu các ô chứa 10% và 15% chứ không phải số thực, khi tỷ lệ phần trăm thay đổi, tất cả những gì bạn cần làm là thay đổi tỷ lệ phần trăm một lần trong ô A12 và/hoặc B12 thay vì xây dựng lại tất cả các công thức của bạn. Excel sẽ tự động cập nhật giá chiết khấu để phản ánh thay đổi phần trăm chiết khấu của bạn
Tóm tắt cách sử dụng tham chiếu ô tuyệt đối
$A1Cho phép thay đổi tham chiếu hàng, nhưng không thay đổi tham chiếu cột. A$1Cho phép thay đổi tham chiếu cột nhưng không thay đổi tham chiếu hàng. $A$1Không cho phép thay đổi tham chiếu cột và hàng
Có một phím tắt để đặt tham chiếu ô tuyệt đối trong công thức của bạn
Khi bạn đang nhập công thức, sau khi bạn nhập tham chiếu ô - hãy nhấn phím F4. Excel tự động làm cho tham chiếu ô trở thành tuyệt đối. Bằng cách tiếp tục nhấn F4, Excel sẽ duyệt qua tất cả các khả năng tham chiếu tuyệt đối. Ví dụ: trong công thức tham chiếu ô tuyệt đối đầu tiên trong hướng dẫn này, =B4*$B$10, tôi có thể đã nhập =B4*B10, sau đó nhấn phím F4 để thay đổi B10 thành $B$10. Tiếp tục nhấn F4 sẽ dẫn đến B$10, sau đó là B10$ và cuối cùng là B10. Nhấn F4 chỉ thay đổi tham chiếu ô trực tiếp bên trái điểm chèn của bạn