Liên kết trong Excel - tuyệt đối, tương đối và hỗn hợp. Lỗi khi làm việc với các liên kết tương đối trong Excel

Để tạo điều kiện tính toán bằng cách sử dụng công thức ở chế độ tự động, các tham chiếu đến ô được sử dụng. Tùy thuộc vào loại chữ viết, chúng được chia thành ba loại chính:

1. Các liên kết tương đối. Dùng để tính toán đơn giản. Sao chép công thức đòi hỏi phải thay đổi tọa độ.
2. Các liên kết tuyệt đối. Nếu bạn cần tạo ra các phép tính phức tạp hơn, tùy chọn này là phù hợp. Để sửa chữa, hãy sử dụng biểu tượng “$”. Ví dụ: $ A $ 1.
3. liên kết hỗn hợp. Kiểu địa chỉ này được sử dụng trong tính toán khi cần cố định một cột hoặc một dòng riêng biệt. Ví dụ: $ A1 hoặc A $ 1.

Nếu cần sao chép dữ liệu của công thức đã nhập, các tham chiếu có địa chỉ tuyệt đối và hỗn hợp sẽ được sử dụng. Bài báo sẽ tiết lộ với các ví dụ về cách tính toán được thực hiện bằng cách sử dụng các loại liên kết khác nhau.

Tham chiếu ô tương đối trong Excel

Đây là một tập hợp các ký tự xác định vị trí của một ô. Các liên kết trong chương trình được viết tự động với địa chỉ tương đối. Ví dụ: A1, A2, B1, B2. Di chuyển đến một hàng hoặc cột khác sẽ thay đổi các ký tự trong công thức. Ví dụ, vị trí bắt đầu A1. Di chuyển theo chiều ngang sẽ thay đổi chữ cái thành B1, C1, D1, v.v. Theo cách tương tự, các thay đổi xảy ra khi di chuyển dọc theo một đường thẳng đứng, chỉ trong trường hợp này, số thay đổi - A2, A3, A4, v.v. Nếu cần thiết phải nhân đôi một phép tính cùng loại trong một ô liền kề, một phép tính được thực hiện bằng cách sử dụng tham chiếu tương đối. Để sử dụng tính năng này, hãy làm theo một số bước:

1. Ngay sau khi dữ liệu được nhập vào ô, di chuyển con trỏ và nhấp chuột. Đánh dấu bằng một hình chữ nhật màu xanh lá cây cho biết sự kích hoạt của tế bào và sự sẵn sàng cho công việc tiếp theo.
2. Bằng cách nhấn tổ hợp phím Ctrl + C, chúng ta sao chép nội dung vào khay nhớ tạm.
3. Chúng tôi kích hoạt ô mà bạn muốn chuyển dữ liệu hoặc công thức đã viết trước đó vào.
4. Bằng cách nhấn tổ hợp Ctrl + V, chúng tôi chuyển dữ liệu được lưu trong khay nhớ tạm thời của hệ thống.

Lời khuyên chuyên gia! Để thực hiện cùng một kiểu tính toán trong bảng, hãy sử dụng một bản hack mạng. Chọn ô chứa công thức đã nhập trước đó. Di con trỏ qua một hình vuông nhỏ xuất hiện ở góc dưới cùng bên phải và giữ nút chuột trái, kéo đến hàng dưới cùng hoặc cột trên cùng, tùy thuộc vào hành động được thực hiện. Bằng cách nhả nút chuột, phép tính sẽ được thực hiện tự động. Công cụ này được gọi là công cụ đánh dấu tự động điền.

Ví dụ về liên kết tương đối

Để làm rõ hơn, hãy xem xét một ví dụ về phép tính sử dụng công thức có tham chiếu tương đối. Giả sử chủ một cửa hàng thể thao sau một năm làm việc cần tính lợi nhuận từ việc bán hàng.

Thứ tự của các hành động:

1. Ví dụ cho thấy rằng cột B và C được sử dụng để điền vào số lượng hàng hóa đã bán và giá của nó. Theo đó, để viết công thức và nhận câu trả lời, hãy chọn cột D. Công thức có dạng như sau: = B 2 *C

Chú ý! Để quá trình viết công thức diễn ra thuận lợi, hãy sử dụng một mẹo nhỏ. Đặt dấu “=”, nhấp vào số lượng hàng hóa bán ra, đặt dấu “*” và nhấp vào giá của sản phẩm. Công thức sau dấu bằng sẽ được viết tự động.

2. Để có câu trả lời cuối cùng, hãy nhấn “Enter”. Tiếp theo, bạn cần tính tổng lợi nhuận nhận được từ các loại sản phẩm khác. Vâng, nếu số dòng không lớn, thì tất cả các thao tác có thể được thực hiện bằng tay. Để điền vào một số lượng lớn các hàng cùng một lúc trong Excel, có một hàm hữu ích giúp bạn có thể chuyển công thức sang các ô khác.
3. Di chuyển con trỏ qua góc dưới bên phải của hình chữ nhật có công thức hoặc kết quả đã hoàn thành. Sự xuất hiện của một chữ thập đen đóng vai trò như một tín hiệu rằng con trỏ có thể được kéo xuống. Do đó, một tính toán tự động về lợi nhuận nhận được cho từng sản phẩm riêng biệt được thực hiện.
4. Bằng cách thả nút chuột đã nhấn, chúng tôi nhận được kết quả chính xác trong tất cả các dòng.

Bằng cách nhấp vào ô D3, bạn có thể thấy rằng tọa độ ô đã được tự động thay đổi và bây giờ trông giống như sau: =B 3 *C3. Sau đó là các liên kết là tương đối.

Các lỗi có thể xảy ra khi làm việc với các liên kết tương đối

Không nghi ngờ gì nữa, hàm Excel này đơn giản hóa rất nhiều các phép tính, nhưng trong một số trường hợp, các khó khăn có thể phát sinh. Hãy xem xét một ví dụ đơn giản về tính toán hệ số lợi nhuận của từng mặt hàng:

1. Tạo bảng và điền: A - tên sản phẩm; B - số lượng đã bán; C - chi phí; D là số tiền nhận được. Giả sử chỉ có 11 mặt hàng trong loại này. Do đó, có tính đến mô tả của các cột, 12 dòng được điền và tổng số tiền lợi nhuận là D
2. Nhấp vào ô E2 và nhập =D2 /D13.
3. Sau khi nhấn nút “Enter”, hệ số của tỷ trọng doanh thu tương đối của mặt hàng đầu tiên sẽ xuất hiện.
4. Kéo căng cột xuống và chờ kết quả. Tuy nhiên, hệ thống đưa ra lỗi “# DIV / 0!”

Lý do cho lỗi là việc sử dụng một tham chiếu tương đối để tính toán. Do sao chép công thức, tọa độ thay đổi. Tức là, đối với E3, công thức sẽ như thế này =D3 /D13. Bởi vì ô D13 không được điền và về mặt lý thuyết có giá trị bằng không, chương trình sẽ đưa ra lỗi với thông tin rằng phép chia cho số không là không thể.

Quan trọng! Để sửa lỗi, cần phải viết công thức sao cho tọa độ D13 được cố định. Địa chỉ tương đối không có chức năng như vậy. Để làm điều này, có một loại liên kết khác - tuyệt đối. 

Làm cách nào để bạn tạo một liên kết tuyệt đối trong Excel

Nhờ việc sử dụng ký hiệu $, có thể sửa tọa độ ô. Làm thế nào điều này hoạt động, chúng tôi sẽ xem xét thêm. Vì chương trình sử dụng địa chỉ tương đối theo mặc định, do đó, để làm cho nó tuyệt đối, bạn sẽ cần thực hiện một số hành động. Hãy phân tích lời giải cho lỗi "Cách tìm hệ số từ việc bán một số mặt hàng", thực hiện phép tính bằng cách sử dụng địa chỉ tuyệt đối:

1. Nhấp vào E2 và nhập tọa độ của liên kết =D2 /D13. Vì liên kết là tương đối, một ký hiệu phải được đặt để sửa dữ liệu.
2. Cố định tọa độ của ô D Để thực hiện hành động này, hãy đặt trước chữ cái chỉ cột và số hàng bằng cách đặt dấu “$”.

Lời khuyên chuyên gia! Để thuận tiện cho công việc nhập, chỉ cần kích hoạt ô để chỉnh sửa công thức và nhấn phím F4 nhiều lần. Cho đến khi bạn nhận được những giá trị thỏa đáng. Công thức đúng như sau: = D2 / $ D $ 13.

3. Nhấn nút "Enter". Theo kết quả của các hành động đã thực hiện, kết quả chính xác sẽ xuất hiện.
4. Kéo điểm đánh dấu xuống dòng dưới cùng để sao chép công thức.

Do việc sử dụng địa chỉ tuyệt đối trong tính toán, kết quả cuối cùng trong các hàng còn lại sẽ đúng.

Cách đặt một liên kết hỗn hợp trong Excel

Đối với các phép tính công thức, không chỉ sử dụng các tham chiếu tương đối và tuyệt đối mà còn sử dụng các tham chiếu hỗn hợp. Đặc điểm nổi bật của chúng là chúng cố định một trong các tọa độ.

• Ví dụ, để thay đổi vị trí của một dòng, bạn phải viết dấu $ trước ký hiệu chữ cái.
• Ngược lại, nếu ký hiệu đô la được viết sau ký hiệu chữ cái, thì các chỉ số trong dòng sẽ không thay đổi.

Từ đó, để giải quyết vấn đề trước đây về việc xác định hệ số bán hàng bằng cách sử dụng địa chỉ hỗn hợp, cần phải sửa số dòng. Nghĩa là, dấu $ được đặt sau ký tự cột, vì tọa độ của nó không thay đổi ngay cả trong một tham chiếu tương đối. Hãy lấy một ví dụ:

1. Để tính toán chính xác, hãy nhập = D1 / $ D $ 3 và nhấn "Enter". Chương trình đưa ra câu trả lời chính xác.
2. Để di chuyển công thức đến các ô tiếp theo trong cột và nhận được kết quả chính xác, hãy kéo phần điều khiển xuống ô dưới cùng.
3. Kết quả là chương trình sẽ đưa ra các phép tính chính xác.

Chú ý! Nếu bạn đặt dấu $ phía trước chữ cái, thì Excel sẽ thông báo lỗi “# DIV / 0!”, Có nghĩa là không thể thực hiện thao tác này.

Địa chỉ "SuperAbsolute"

Cuối cùng, hãy xem xét một ví dụ khác về liên kết tuyệt đối - địa chỉ “SuperAbsolute”. Tính năng và sự khác biệt của nó là gì. Lấy số gần đúng 30 và nhập nó vào ô B2. Chính con số này sẽ là chính, cần phải thực hiện một loạt các hành động với nó, chẳng hạn để nâng nó lên thành lũy thừa.

1. Để thực hiện đúng tất cả các hành động, hãy nhập công thức sau vào cột C: = $ B $ 2 ^ $ D2. Trong cột D chúng ta nhập giá trị của các độ.

2. Sau khi nhấn nút "Enter" và kích hoạt công thức, chúng tôi kéo điểm đánh dấu xuống cột.
3. Chúng tôi nhận được kết quả chính xác.

Điểm mấu chốt là tất cả các hành động được thực hiện đều tham chiếu đến một ô cố định B2, do đó:

• Sao chép công thức từ ô C3 sang ô E3, F3 hoặc H3 sẽ không thay đổi kết quả. Nó sẽ không thay đổi - 900.
• Nếu bạn cần chèn một cột mới, tọa độ của ô có công thức sẽ thay đổi, nhưng kết quả cũng không thay đổi.

Đây là điểm đặc biệt của liên kết “SuperAbsolute”: nếu bạn cần di chuyển, kết quả sẽ không thay đổi. Tuy nhiên, có những tình huống khi dữ liệu được chèn từ các nguồn của bên thứ ba. Do đó, các cột được chuyển sang một bên và dữ liệu được đặt theo cách cũ trong cột B2. Điều gì xảy ra trong trường hợp này? Khi được trộn, công thức sẽ thay đổi theo hành động được thực hiện, tức là nó sẽ không còn trỏ đến B2 nữa mà đến C2. Nhưng vì việc chèn được thực hiện ở B2 nên kết quả cuối cùng sẽ sai.

Tài liệu tham khảo! Để có thể chèn macro từ các nguồn của bên thứ ba, bạn cần bật cài đặt dành cho nhà phát triển (chúng bị tắt theo mặc định). Để thực hiện việc này, hãy đi tới Tùy chọn, mở cài đặt ruy-băng và chọn hộp ở cột bên phải đối diện “Nhà phát triển”. Sau đó, quyền truy cập vào nhiều chức năng mà trước đây bị ẩn khỏi mắt của người dùng bình thường sẽ mở ra.

Điều này đặt ra câu hỏi: liệu có thể sửa đổi công thức từ ô C2 để số ban đầu được thu thập từ ô B, bất chấp việc chèn các cột dữ liệu mới không? Để đảm bảo rằng các thay đổi trong bảng không ảnh hưởng đến việc xác định tổng số khi cài đặt dữ liệu từ các nguồn của bên thứ ba, bạn phải thực hiện các bước sau:

1. Thay vì tọa độ của ô B2, hãy nhập các chỉ số sau: = INDIRECT (“B2”). Kết quả là, sau khi di chuyển thành phần công thức sẽ trông như thế này: = INDIRECT (“B2”) ^ $ E2.
2. Nhờ chức năng này, liên kết luôn trỏ đến ô vuông có tọa độ B2, bất kể cột được thêm vào hay bớt đi trong bảng.

Cần phải hiểu rằng một ô không chứa bất kỳ dữ liệu nào luôn hiển thị giá trị “0”.

Kết luận

Nhờ việc sử dụng ba loại liên kết được mô tả, rất nhiều cơ hội xuất hiện giúp làm việc với các phép tính trong Excel dễ dàng hơn. Do đó, trước khi bạn bắt đầu làm việc với các công thức, trước tiên hãy đọc các liên kết và quy tắc cài đặt chúng.