Để 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
A1 so với. Tài liệu tham khảo R1C1
Trang tính Excel chứa nhiều ô và [theo mặc định] mỗi ô được xác định bằng ký tự cột theo sau là số hàng của ô đó. Điều 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 số hàng và cột của mộ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ỳ hiếm trong Excel. Trừ khi bạn có lý do chính đáng, có lẽ bạn 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, rất có thể bạn sẽ gặp kiểu tham chiếu này
Chuyển sang kiểu tham chiếu R1C1
Để chuyển đổi 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
Phạm vi đượ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 Phạm vi được đặ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 phạm vi
- Chọn ô hoặc các ô mà 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 bạn muốn
- Nhấn Enter
Bây giờ bạn có thể tham chiếu ô A1 bằng cách gõ =range_name1 thay thế. Điều này rất hữu ích khi làm việc với sổ làm việc lớn có nhiều trang tính
Phạm vi ô
Khi sử dụng Hàm tích hợp sẵn của Excel, bạn có thể cần tham chiếu các 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. các ô A1,A2,A3,B1,B2,B3
Để chọn một dãy ô 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 băng] 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 sang một ô mới. Tham chiếu tuyệt đối sẽ không thay đổi. Hãy xem xét một số ví dụ
Tham khảo tương đối
Một 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 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 cố định] theo mặc định
Thí 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 ô tuyệt đối [đông lạnh]
Nếu bạn không muốn tham chiếu ô của mình di chuyển khi sao chép công thức, bạn có thể "đóng băng" tham chiếu ô của mình bằng cách thêm ký hiệu đô la [$s] 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 đóng băng 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 bị cố định, nhưng hàng không bị cố định
1 đô la Úc. Hàng bị đóng băng, nhưng cột không bị đóng băng
$A$1. Cả hàng và cột đều bị đóng băng
Phím 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, 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 ô? . Trong ví dụ bên dưới, chúng tôi muốn tính thuế bán hàng cho từng số lượng món trong thực đơn. Thuế bán hàng không đổi trên tất cả các mặt hàng, vì vậy chúng tôi sẽ liên tục tham chiếu đến ô thuế bán hàng
Để tìm tổng 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
tham khảo 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 [chứ không phải cả hai]
tham khảo hỗn hợp
Hãy nhớ rằng, bằng cách sử dụng phím “F4”, bạn có thể duyệt qua các tham chiếu ô tuyệt đối, tương đối của mình
Tham chiếu ô – Chèn và xóa hàng/cột
Bạn có thể tự hỏi điều gì sẽ xảy ra với tham chiếu ô của mình khi bạn chèn hoặc xóa 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
Tham chiếu 3D
Đôi khi, bạn có thể cần phải làm việc với một số trang tính có mẫu dữ liệu giống hệt nhau. Excel cho phép bạn tham khảo nhiều sheet cùng lúc mà không cần nhập thủ công từng sheet. Bạn có thể tham chiếu một dải trang tính tương tự như cách tham chiếu một dải ô. Ví dụ 'Trang tính 1. Tờ5. A1’ sẽ tham chiếu các ô A1 trên tất cả các trang tính từ Trang tính 1 đến Trang tính 5
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 hệt nhau. Bạn có thể tạo một công thức tương tự như thế này
Điều này không quá khó chỉ với bốn trang tính, nhưng nếu bạn có 40 trang tính thì sao?
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 dải ô]
Hãy cẩn thận. Thứ tự của bảng tính của bạn 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], trang tính đó sẽ không được đưa vào 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 dùng làm đầu vào cho ô B1 thì tham chiếu vòng đượ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”. Vì bạn đang ở ô B2 nên bạn không thể 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 tham chiếu vòng là kết quả của lỗi 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. Ví dụ chính về việc sử dụng tham chiếu vòng là tính toán lặp lại các giá trị. Để thực hiện việc này, bạn cần vào 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 khảo 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 một 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ình thường
Để đ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 đổi Windows
Khi bạn đã chọn ô, bạn sẽ thấy một tham chiếu bên ngoài giống như thế này
Lưu ý rằng tên sổ làm việc được đặt trong dấu ngoặc vuông []
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 chứa liên kết bên ngoài, bạn sẽ được nhắc bật tự động cập nhật liên kết. Nếu bạn làm như vậy thì Excel sẽ mở giá trị tham chiếu bằng 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