Để thực hiện các phép tính chính xác trong Excel, điều cần thiết là phải hiểu cách hoạt động của các loại tham chiếu ô khác nhau.
Tham chiếu A1 so với R1C1
Trang tính Excel chứa nhiều ô và (theo mặc định) mỗi ô được xác định bởi cột chữ cái tiếp theo là số hàng của nó. Đây được gọi là tham chiếu kiểu A1. Ví dụ: A1, B4, C6
Kiểu tham chiếu A1
Theo tùy chọn, bạn có thể chuyển sang Chế độ tham chiếu R1C1 để tham chiếu đến hàng của ô & số cột. Thay vì tham chiếu đến ô A1, bạn sẽ tham chiếu đến R1C1 (hàng 1, cột 1). Ô C4 sẽ được gọi là R4C3.
Kiểu tham chiếu R1C1
Tham chiếu kiểu R1C1 cực kỳ không phổ biến trong Excel. Trừ khi bạn có lý do chính đáng, bạn có thể nên sử dụng chế độ tham chiếu kiểu A1 mặc định. Tuy nhiên, nếu bạn sử dụng VBA, bạn có thể sẽ gặp phải kiểu tham chiếu này.
Chuyển sang Kiểu tham chiếu R1C1
Để chuyển kiểu tham chiếu, hãy chuyển đến Tệp> Tùy chọn> Công thức. Chọn hộp bên cạnh Kiểu tham chiếu R1C1.
Dãy được đặt tên
Một trong những tính năng ít được sử dụng nhất của Excel là tính năng Dải ô đã đặt tên. Thay vì tham chiếu đến một ô (hoặc nhóm ô) theo vị trí ô của nó (ví dụ: B3 hoặc R3C2), bạn có thể đặt tên cho dải ô đó và chỉ cần tham chiếu tên dải ô trong công thức của mình.
Để đặt tên cho một dải ô:
- Chọn ô hoặc các ô bạn muốn đặt tên
- Nhấp vào Bên trong Hộp Tên Phạm vi
- Nhập tên mong muốn của bạn
- Nhấn Enter
Bây giờ, bạn có thể tham chiếu ô A1 bằng cách nhập = range_name1. Điều này rất hữu ích khi làm việc với các sổ làm việc lớn với nhiều trang tính.
Phạm vi ô
Khi sử dụng Hàm dựng sẵn của Excel, bạn có thể cần phải tham chiếu đến phạm vi ô. Phạm vi ô xuất hiện như thế này ‘A1: B3’. Tham chiếu này đề cập đến tất cả các ô ở giữa A1 và B3: ô A1, A2, A3, B1, B2, B3.
Để chọn một dải ô khi nhập công thức:
- Nhập phạm vi (phân tách phạm vi bắt đầu và kết thúc bằng dấu chấm phẩy)
- Sử dụng chuột để nhấp vào tham chiếu ô đầu tiên, giữ nút chuột và kéo phạm vi mong muốn của bạn.
- Giữ phím shift và điều hướng bằng các phím mũi tên để chọn phạm vi của bạn
Tham chiếu Tuyệt đối (Đông lạnh) và Tương đối
Khi nhập tham chiếu ô trong công thức, bạn có thể sử dụng tham chiếu tương đối hoặc tuyệt đối (cố định). Các tham chiếu tương đối sẽ di chuyển theo tỷ lệ khi bạn sao chép công thức vào một ô mới. Tham chiếu tuyệt đối sẽ không thay đổi. Hãy xem một số ví dụ:
Tham chiếu tương đối
Tham chiếu tương đối trong Excel trông như thế này
= A1
Khi bạn sao chép và dán một công thức với các tham chiếu tương đối, các tham chiếu tương đối sẽ di chuyển theo tỷ lệ. Trừ khi bạn chỉ định khác, các tham chiếu ô của bạn sẽ là tương đối (không đóng băng) theo mặc định.
Ví dụ: Nếu bạn sao chép ‘= A1’ xuống một hàng, tham chiếu sẽ thay đổi thành ‘= A2’.
Tham chiếu đến ô tuyệt đối (Đông lạnh)
Nếu bạn không muốn các tham chiếu ô của mình di chuyển khi sao chép công thức, bạn có thể "cố định" các tham chiếu ô của mình bằng cách thêm ký hiệu đô la ($ s) vào trước tham chiếu mà bạn muốn cố định. Bây giờ khi bạn sao chép và dán công thức, tham chiếu ô sẽ không thay đổi. Bạn có thể chọn cố định tham chiếu hàng, tham chiếu cột hoặc cả hai.
A1: Không có gì bị đóng băng
$ A1: Cột được cố định, nhưng hàng không được cố định
A $ 1: Hàng được cố định, nhưng cột không được cố định
$ A $ 1: Cả hàng và cột đều được cố định
Lối tắt tham chiếu tuyệt đối
Việc thêm ký hiệu đô la ($ s) vào công thức của bạn theo cách thủ công không thực tế lắm. Thay vào đó, trong khi tạo công thức của bạn, hãy sử dụng phím F4 để chuyển đổi giữa các tham chiếu ô tuyệt đối / tương đối.
Ví dụ tham chiếu ô tuyệt đối
Khi nào bạn thực sự cần đóng băng một tham chiếu ô? Một ví dụ phổ biến là khi bạn có các ô đầu vào được tham chiếu thường xuyên. Trong ví dụ dưới đây, chúng tôi muốn tính thuế bán hàng cho mỗi số lượng các món trong thực đơn. Thuế bán hàng là không đổi trên tất cả các mặt hàng, vì vậy chúng tôi sẽ tham chiếu nhiều lần ô thuế bán hàng.
Để tìm tổng số thuế bán hàng, hãy nhập công thức ‘= (B3 * C3) * $ C $ 1’ vào cột D và sao chép công thức xuống dưới.
Tham chiếu hỗn hợp
Bạn có thể đã nghe nói về tham chiếu ô hỗn hợp. Tham chiếu hỗn hợp là khi tham chiếu hàng hoặc cột bị khóa (nhưng không phải cả hai).
Tham chiếu hỗn hợp
Hãy nhớ rằng, bằng cách sử dụng phím “F4”, bạn có thể chuyển qua các tham chiếu ô tương đối, tuyệt đối của mình.
Tham chiếu ô - Chèn & Xóa Hàng / Cột
Bạn có thể tự hỏi điều gì sẽ xảy ra với các tham chiếu ô của bạn khi bạn chèn hoặc xóa các hàng / cột?
Tham chiếu ô sẽ tự động cập nhật để tham chiếu đến ô ban đầu. Đây là trường hợp bất kể tham chiếu ô có bị đóng băng hay không.
Tài liệu tham khảo 3D
Đôi khi, bạn có thể cần phải làm việc với một số trang tính có các mẫu dữ liệu giống hệt nhau. Excel cho phép bạn tham khảo nhiều trang tính cùng một lúc mà không cần nhập từng trang tính theo cách thủ công. Bạn có thể tham chiếu một loạt các trang tính tương tự như cách tham chiếu một loạt các ô. Ví dụ ‘Sheet1: Sheet5! A1’ sẽ tham chiếu đến các ô A1 trên tất cả các trang tính từ Sheet1 đến Sheet5.
Hãy xem qua một ví dụ:
Bạn muốn cộng tổng số đơn vị đã bán cho từng sản phẩm trên tất cả các cửa hàng. Mỗi cửa hàng có trang tính riêng và tất cả các trang tính đều có định dạng giống nhau. Bạn có thể tạo một công thức tương tự như sau:
Điều này không quá khó với chỉ bốn trang tính, nhưng nếu bạn có 40 trang tính thì sao? Bạn có thực sự muốn thêm từng tham chiếu ô theo cách thủ công không?
Thay vào đó, bạn có thể sử dụng tham chiếu 3D để tham chiếu nhiều trang tính cùng một lúc một cách dễ dàng (tương tự như cách bạn có thể tham chiếu một loạt ô).
Hãy cẩn thận! Thứ tự các trang tính của bạn rất quan trọng. Nếu bạn di chuyển một trang tính khác vào giữa các trang tính được tham chiếu (StoreA và StoreD) thì trang tính đó sẽ được đưa vào. Ngược lại, nếu bạn di chuyển một trang tính ra ngoài phạm vi trang tính (trước StoreA hoặc sau StoreD), nó sẽ không còn được bao gồm nữa.
Tham chiếu ô tròn
Tham chiếu ô tròn là khi một ô tham chiếu trở lại chính nó. Ví dụ: nếu kết quả của ô B1 được sử dụng làm đầu vào cho ô B1 thì một tham chiếu vòng tròn sẽ được tạo. Ô không cần tham chiếu trực tiếp đến chính nó Có thể có các bước trung gian.
Thí dụ:
Trong trường hợp này, công thức cho ô B2 là “A2 + A3 + B2”. Bởi vì bạn đang ở ô B2, bạn có thể không sử dụng B2 trong phương trình. Điều này sẽ kích hoạt tham chiếu vòng tròn và giá trị trong ô “B2” sẽ tự động được đặt thành “0”.
Thông thường, các tham chiếu vòng tròn là do lỗi của người dùng, nhưng trong một số trường hợp, bạn có thể muốn sử dụng tham chiếu vòng tròn. Ví dụ chính của việc sử dụng tham chiếu vòng tròn là tính toán các giá trị theo cách lặp lại. Để làm điều này, bạn cần phải đi tới Tệp> Tùy chọn> Công thức và Tính toán lặp lại đã bật:
Tài liệu tham khảo bên ngoài
Đôi khi khi tính toán dữ liệu, bạn có thể cần tham chiếu đến dữ liệu bên ngoài sổ làm việc của mình. Đây được gọi là tham chiếu bên ngoài (liên kết).
Để chọn tham chiếu bên ngoài trong khi tạo công thức, hãy điều hướng đến sổ làm việc bên ngoài và chọn tham chiếu của bạn như bạn thường làm.
Để điều hướng đến sổ làm việc khác, bạn có thể sử dụng phím tắt CTRL + TAB hoặc đi tới Xem> Chuyển Windows.
Khi bạn đã chọn ô, bạn sẽ thấy rằng một tham chiếu bên ngoài trông giống như sau:
Lưu ý rằng tên sổ làm việc được bao bởi dấu ngoặc [].
Khi bạn đóng sổ làm việc được tham chiếu, tham chiếu sẽ hiển thị vị trí tệp:
Khi bạn mở lại sổ làm việc có chứa liên kết bên ngoài, bạn sẽ được nhắc bật cập nhật tự động các liên kết. Nếu bạn làm như vậy, thì Excel sẽ mở giá trị tham chiếu với giá trị hiện tại của sổ làm việc. Ngay cả khi nó đã đóng cửa! Hãy cẩn thận! Bạn có thể muốn hoặc không muốn điều này.
Phạm vi được đặt tên và Tham chiếu bên ngoài
Điều gì xảy ra với tham chiếu ô bên ngoài của bạn khi hàng / cột được thêm hoặc xóa khỏi sổ làm việc tham chiếu? Nếu cả hai sổ làm việc đang mở thì tham chiếu ô sẽ tự động cập nhật. Tuy nhiên, nếu cả hai sổ làm việc không mở thì tham chiếu ô sẽ không cập nhật và sẽ không còn hợp lệ. Đây là một mối quan tâm lớn khi liên kết với các sổ làm việc bên ngoài. Nhiều lỗi là do điều này gây ra.
Nếu bạn thực hiện liên kết đến sổ làm việc bên ngoài, bạn nên đặt tên cho tham chiếu ô bằng một phạm vi đã đặt tên (xem phần trước để biết thêm thông tin). Bây giờ công thức của bạn sẽ tham chiếu đến phạm vi được đặt tên bất kể thay đổi nào xảy ra trên sổ làm việc bên ngoài.
