Cách cố định một ô trong công thức Excel

Thông thường, người dùng phải ghim một ô trong công thức. Ví dụ: nó xảy ra trong các tình huống mà bạn muốn sao chép một công thức, nhưng liên kết không di chuyển lên và xuống cùng một số ô như nó đã được sao chép từ vị trí ban đầu của nó.

Trong trường hợp này, bạn có thể sửa tham chiếu ô trong Excel. Và điều này có thể được thực hiện theo nhiều cách cùng một lúc. Chúng ta hãy xem xét kỹ hơn làm thế nào để đạt được mục tiêu này.

Liên kết Excel là gì

Trang tính được tạo thành từ các ô. Mỗi người trong số họ chứa thông tin cụ thể. Các ô khác có thể sử dụng nó trong tính toán. Nhưng làm thế nào để họ hiểu được nguồn dữ liệu từ đâu? Nó giúp họ tạo liên kết.

Mỗi liên kết chỉ định một ô có một chữ cái và một số. Một chữ cái đại diện cho một cột và một số đại diện cho một hàng. 

Có ba loại liên kết: tuyệt đối, tương đối và hỗn hợp. Cái thứ hai được đặt theo mặc định. Tham chiếu tuyệt đối là tham chiếu có địa chỉ cố định của cả một cột và một cột. Theo đó, hỗn hợp là một trong đó một cột hoặc một hàng riêng biệt được cố định.

Phương pháp 1

Để lưu cả địa chỉ cột và hàng, hãy làm theo các bước sau:

1. Bấm vào ô chứa công thức.
2. Bấm vào thanh công thức cho ô mà chúng ta cần.
3. Nhấn F4.

Do đó, tham chiếu ô sẽ thay đổi thành tuyệt đối. Nó có thể được nhận ra bởi ký hiệu đô la đặc trưng. Ví dụ: nếu bạn nhấp vào ô B2 và sau đó nhấp vào F4, liên kết sẽ giống như sau: $ B $ 2.

Ký hiệu đô la có ý nghĩa gì trước phần địa chỉ trên mỗi ô?

1. Nếu nó được đặt trước một chữ cái, nó chỉ ra rằng tham chiếu cột vẫn giữ nguyên, bất kể công thức đã được di chuyển đến đâu.
2. Nếu ký hiệu đô la ở phía trước số, nó cho biết rằng chuỗi đã được ghim. 

Phương pháp 2

Phương pháp này gần giống như cách trước, chỉ cần bạn nhấn F4 hai lần. ví dụ, nếu chúng ta có ô B2, thì sau đó nó sẽ trở thành B $ 2. Nói một cách dễ hiểu, bằng cách này, chúng tôi đã sửa được dòng. Trong trường hợp này, ký tự của cột sẽ thay đổi.

Nó rất thuận tiện, ví dụ, trong các bảng mà bạn cần hiển thị nội dung của ô thứ hai từ trên xuống trong ô dưới cùng. Thay vì thực hiện một công thức như vậy nhiều lần, chỉ cần sửa hàng và để cột thay đổi.

Phương pháp 3

Cách này hoàn toàn giống với phương pháp trước, bạn chỉ cần nhấn phím F4 ba lần. Sau đó, chỉ tham chiếu đến cột sẽ là tuyệt đối và hàng sẽ vẫn cố định.

Phương pháp 4

Giả sử chúng ta có một tham chiếu tuyệt đối đến một ô, nhưng ở đây cần phải làm cho nó tương đối. Để thực hiện việc này, hãy nhấn phím F4 nhiều lần để không có dấu $ trong liên kết. Sau đó, nó sẽ trở thành tương đối và khi bạn di chuyển hoặc sao chép công thức, cả địa chỉ cột và địa chỉ hàng sẽ thay đổi.

Ghim các ô cho một phạm vi lớn

Chúng tôi thấy rằng các phương pháp trên không có bất kỳ khó khăn nào để thực hiện. Nhưng các nhiệm vụ là cụ thể. Và, ví dụ, phải làm gì nếu chúng ta có hàng chục công thức cùng một lúc, các liên kết trong đó cần được chuyển thành các liên kết tuyệt đối. 

Thật không may, các phương pháp Excel tiêu chuẩn sẽ không đạt được mục tiêu này. Để làm điều này, bạn cần sử dụng một addon đặc biệt có tên là VBA-Excel. Nó chứa nhiều tính năng bổ sung cho phép bạn thực hiện các tác vụ thông thường với Excel nhanh hơn nhiều.

Nó bao gồm hơn một trăm chức năng do người dùng xác định và 25 macro khác nhau, và nó cũng được cập nhật thường xuyên. Nó cho phép bạn cải thiện công việc với hầu hết mọi khía cạnh:

1. Tế bào.
2. Vĩ mô
3. Chức năng của các loại khác nhau.
4. Liên kết và mảng.

Đặc biệt, phần bổ trợ này cho phép bạn sửa các liên kết trong một số lượng lớn công thức cùng một lúc. Để làm điều này, bạn phải thực hiện các bước sau:

1. Chọn một phạm vi.
2. Mở tab VBA-Excel sẽ xuất hiện sau khi cài đặt. 
3. Mở menu "Chức năng", nơi có tùy chọn "Khóa công thức".
   

   

4. Tiếp theo, một hộp thoại sẽ xuất hiện trong đó bạn cần chỉ định tham số cần thiết. Addon này cho phép bạn ghim một cột và một cột riêng biệt với nhau và cũng có thể loại bỏ ghim đã tồn tại bằng một gói. Sau khi thông số bắt buộc được chọn bằng nút radio tương ứng, bạn cần xác nhận hành động của mình bằng cách nhấp vào “OK”.

Ví dụ

Hãy lấy một ví dụ để làm rõ hơn. Giả sử chúng ta có thông tin mô tả giá vốn, tổng số lượng và doanh thu bán hàng. Và chúng tôi phải đối mặt với nhiệm vụ lập bảng, dựa trên số lượng và chi phí, tự động xác định số tiền chúng tôi quản lý để kiếm được mà không khấu trừ lỗ.

Trong ví dụ của chúng tôi, bạn cần nhập công thức =B2 * C2. Nó khá đơn giản, như bạn có thể thấy. Rất dễ dàng sử dụng ví dụ của cô ấy để mô tả cách bạn có thể sửa địa chỉ của một ô hoặc từng cột hoặc hàng của nó. 

Tất nhiên, trong ví dụ này, bạn có thể cố gắng kéo công thức xuống bằng cách sử dụng điểm đánh dấu tự động điền, nhưng trong trường hợp này, các ô sẽ được tự động thay đổi. Vì vậy, trong ô D3 sẽ có một công thức khác, trong đó các số sẽ được thay thế tương ứng bằng 3. Hơn nữa, theo sơ đồ - D4 - công thức sẽ có dạng = B4 * C4, D5 - tương tự, nhưng với số 5 và như vậy.

Nếu nó là cần thiết (trong hầu hết các trường hợp là nó), thì không có vấn đề gì. Nhưng nếu bạn cần cố định công thức trong một ô để nó không thay đổi khi kéo, thì việc này sẽ khó hơn một chút. 

Giả sử chúng ta cần xác định doanh thu bằng đô la. Hãy đặt nó vào ô B7. Hãy có một chút hoài cổ và cho biết chi phí 35 rúp mỗi đô la. Theo đó, để xác định doanh thu bằng đô la, cần phải chia số tiền bằng đồng rúp cho tỷ giá hối đoái của đồng đô la.

Đây là những gì nó trông giống như trong ví dụ của chúng tôi.

Nếu chúng ta, tương tự như phiên bản trước, cố gắng đưa ra một công thức, thì chúng ta sẽ thất bại. Tương tự, công thức sẽ thay đổi thành công thức thích hợp. Trong ví dụ của chúng tôi, nó sẽ như thế này: = E3 * B8. Từ đây chúng ta có thể thấy. rằng phần đầu tiên của công thức đã chuyển thành E3 và chúng tôi tự đặt cho mình nhiệm vụ này, nhưng chúng tôi không cần phải thay đổi phần thứ hai của công thức thành B8. Do đó, chúng ta cần biến tham chiếu thành tuyệt đối. Bạn có thể thực hiện việc này mà không cần nhấn phím F4, chỉ bằng cách đặt một ký hiệu đô la.

Sau khi chúng tôi chuyển tham chiếu đến ô thứ hai thành tham chiếu tuyệt đối, nó được bảo vệ khỏi các thay đổi. Giờ đây, bạn có thể kéo nó một cách an toàn bằng cách sử dụng tay cầm tự động điền. Tất cả dữ liệu cố định sẽ được giữ nguyên, bất kể vị trí của công thức và dữ liệu không được cam kết sẽ thay đổi linh hoạt. Trong tất cả các ô, doanh thu tính bằng đồng rúp được mô tả trong dòng này sẽ được chia cho cùng một tỷ giá hối đoái đô la.

Bản thân công thức sẽ giống như sau:

= D2 / $ B $ 7

Chú ý! Chúng tôi đã chỉ ra hai dấu hiệu đô la. Bằng cách này, chúng tôi hiển thị chương trình rằng cả cột và hàng cần được sửa.

Tham chiếu ô trong macro

Macro là một chương trình con cho phép bạn tự động hóa các hành động. Không giống như chức năng tiêu chuẩn của Excel, macro cho phép bạn đặt ngay một ô cụ thể và thực hiện các hành động nhất định chỉ trong một vài dòng mã. Hữu ích cho việc xử lý hàng loạt thông tin, ví dụ, nếu không có cách nào để cài đặt tiện ích bổ sung (ví dụ: máy tính của công ty được sử dụng, không phải máy tính cá nhân).

Trước tiên, bạn cần hiểu rằng khái niệm quan trọng của macro là các đối tượng có thể chứa các đối tượng khác. Đối tượng Workbooks chịu trách nhiệm về sách điện tử (nghĩa là tài liệu). Nó bao gồm đối tượng Trang tính, là một tập hợp tất cả các trang tính của một tài liệu đang mở. 

Theo đó, các ô là một đối tượng Cells. Nó chứa tất cả các ô của một trang tính cụ thể.

Mỗi đối tượng đủ điều kiện với các đối số trong ngoặc đơn. Trong trường hợp ô, chúng được tham chiếu theo thứ tự này. Số hàng được liệt kê đầu tiên, tiếp theo là số cột hoặc chữ cái (cả hai định dạng đều được chấp nhận).

Ví dụ: một dòng mã chứa tham chiếu đến ô C5 sẽ trông giống như sau:

Sổ làm việc (“Book2.xlsm”). Trang tính (“List2”). Ô (5, 3)

Sổ làm việc (“Book2.xlsm”). Trang tính (“List2”). Ô (5, “C”)

Bạn cũng có thể truy cập một ô bằng cách sử dụng một đối tượng Ngăn nắp. Nói chung, nó nhằm cung cấp một tham chiếu đến một phạm vi (nhân tiện, các phần tử của nó cũng có thể là tuyệt đối hoặc tương đối), nhưng bạn có thể chỉ cần đặt tên ô, ở định dạng giống như trong tài liệu Excel.

Trong trường hợp này, dòng sẽ như thế này.

Sổ làm việc (“Book2.xlsm”). Trang tính (“List2”). Phạm vi (“C5”)

Có vẻ như tùy chọn này thuận tiện hơn, nhưng lợi thế của hai tùy chọn đầu tiên là bạn có thể sử dụng các biến trong dấu ngoặc và đưa ra một liên kết không còn tuyệt đối nữa, mà giống như một liên kết tương đối, điều này sẽ phụ thuộc vào kết quả của tính toán.

Do đó, macro có thể được sử dụng hiệu quả trong các chương trình. Trên thực tế, tất cả các tham chiếu đến ô hoặc phạm vi ở đây sẽ là tuyệt đối và do đó cũng có thể được sửa với chúng. Đúng, nó không phải là thuận tiện như vậy. Việc sử dụng macro có thể hữu ích khi viết các chương trình phức tạp với một số lượng lớn các bước trong thuật toán. Nói chung, cách tiêu chuẩn sử dụng tham chiếu tuyệt đối hoặc tương đối thuận tiện hơn nhiều. 

Kết luận

Chúng tôi đã tìm ra tham chiếu ô là gì, nó hoạt động như thế nào, dùng để làm gì. Chúng tôi đã hiểu sự khác biệt giữa tham chiếu tuyệt đối và tương đối và tìm ra những gì cần phải làm để biến một loại này thành một loại khác (nói một cách đơn giản là sửa địa chỉ hoặc bỏ ghim địa chỉ đó). Chúng tôi đã tìm ra cách bạn có thể làm điều đó ngay lập tức với một số lượng lớn các giá trị. Giờ đây, bạn có thể linh hoạt sử dụng tính năng này trong những tình huống phù hợp.