Giữ cột không đổi trong công thức Excel

Khi bạn sao chép công thức, các tham chiếu ô trong công thức sẽ thay đổi tùy thuộc vào vị trí bạn dán. Nếu bạn muốn giữ các ô biến không đổi, bạn có thể khóa chúng trước khi sao chép công thức. Trong ví dụ này, Cột C là kết quả của phép nhân các giá trị từ A2 [2] và Cột B

Nếu bạn sao chép công thức xuống Cột C, cả hai tham chiếu ô sẽ thay đổi [A2 thành A3 và B2 thành B3, v.v. ]

Giả sử bạn muốn giữ ô A2 không đổi. Để làm điều đó, hãy nhấp vào tham chiếu ô trong thanh công thức [A2] và nhập $ trước cột và hàng [$A$2]. Bạn cũng có thể nhấn F4 trên bàn phím để biến ô thành hằng số

Do đó, công thức của Cột C sử dụng ô A2 làm hằng số và các giá trị từ Cột B được nhân với 2 trong mỗi hàng

Ghi chú. Trong phiên bản Excel mới hơn, có các công thức mảng mới tự động đưa các giá trị vào phạm vi và không cần bất kỳ khóa ô nào. Các công thức này bao gồm SORT, UNIQUE, SORTBY và XLOOKUP

Cách giữ giá trị ô không đổi bằng phím F4

• Giả sử chúng tôi dự định tính tổng các giá trị trong Cột C trong khi vẫn giữ nguyên “giá trị” trong Cột D là D4 trong công thức, chúng tôi sẽ . Figure 4. Chúng ta cũng có thể nhấn Phím F4 khi chúng ta di chuyển con trỏ to the positions before the Column Letter [D] and the Column Number [4].

Công thức =SUM[C4,$D4]

Lưu ý – Nếu chúng ta có công thức như =SUM[$C$4,$D$4] , that will tell Excel to maintain the same cell reference [the formula], no matter the cell it is copied to.

Kết nối tức thì với một chuyên gia Excel

Hầu hết thời gian, vấn đề bạn cần giải sẽ phức tạp hơn một ứng dụng đơn giản của công thức hoặc hàm. Nếu bạn muốn tiết kiệm hàng giờ nghiên cứu và sự thất vọng, hãy dùng thử dịch vụ Excelchat trực tiếp của chúng tôi. Các chuyên gia Excel của chúng tôi luôn sẵn sàng 24/7 để trả lời bất kỳ câu hỏi nào về Excel mà bạn có thể có. Chúng tôi đảm bảo kết nối trong vòng 30 giây và giải pháp tùy chỉnh trong vòng 20 phút.

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