Khi tạo công thức trong Excel hoặc hầu hết các chương trình bảng tính khác, đôi khi chúng ta cần một trong các giá trị là động [e. g. chúng ta có thể thay đổi nó ở một nơi và có tác dụng ở mọi nơi mà nó được tham chiếu] nhưng không đổi khi chúng ta sao chép công thức đó trên các hàng hoặc cột
Để giữ một giá trị không đổi trong Excel, hãy sử dụng các bước sau
- Tạo một ô có giá trị không đổi mà bạn muốn tham chiếu
- Tạo một công thức trong một ô thực hiện phép tính của bạn
- Trong công thức mà bạn tham chiếu đến giá trị bạn đã tạo ở bước 1, hãy thêm “$” trước chữ cái [đại diện cho cột] và số [đại diện cho hàng]
Ví dụ về Giữ giá trị không đổi trong Excel
Chúng tôi có một ô, giả sử ô A1 chỉ chứa văn bản “Tỷ lệ lãi suất”, đây chỉ đơn giản là một nhãn để chúng tôi biết những gì chúng tôi đang xử lý
Tiếp theo, chúng tôi có một ô B1 chứa lãi suất thực tế, chúng tôi sẽ biểu thị tỷ lệ phần trăm của mình dưới dạng số thập phân để 10% trở thành. 1
Bây giờ bên dưới chúng ta có ba cột
- Số tiền gốc
- Điều quan tâm đơn giản
- Tổng [Số tiền gốc + Tiền lãi đơn]
trong A3, chúng tôi đặt 1000 đô la, trong A4, chúng tôi đặt 2000 đô la, sau đó tô sáng cả hai ô và lấy góc dưới cùng bên phải và kéo xuống thêm 8 hàng để bạn có các giá trị từ 1000 đô la – 10.000 đô la
Tiếp theo trong B3, chúng ta nhập một công thức để tính tiền lãi bằng cách nhân giá trị đầu tiên [$1000 trong ô A3] với lãi suất [. 1 hoặc 10% trong B1]
=[A3*B1]
Ô sẽ hiển thị $100
Bây giờ, khi tạo 10 hàng với gia số $1000, chúng ta có thể lấy góc dưới cùng bên phải và kéo xuống. Vấn đề là cả hai tham chiếu sẽ thay đổi các ô nhưng chúng tôi chỉ muốn ô đầu tiên [số tiền gốc] tính từ ô tiếp theo trở xuống [A4, A5, v.v.]. chúng tôi muốn ô lãi suất luôn được tính từ B1 [không phải B2, B3, v.v. như bảng tính sẽ giả định một cách tự nhiên]
Vì vậy, để khắc phục điều này, chúng tôi thêm “$” trước chữ cái Cột và số Hàng cho ô lãi suất của chúng tôi, thay đổi công thức trong B3 thành như thế này
=[A3*$B$1]
Bây giờ khi chúng ta lấy và kéo lãi suất sẽ luôn được tính từ giá trị ở B1
Cuối cùng, để hoàn thiện mọi thứ, hãy thực hiện một công thức khác trong C3 để thêm Tiền gốc vào Tiền lãi bằng công thức đơn giản =[A3+B3] trong C3
Bây giờ, hãy lấy và kéo C3 xuống và bạn sẽ thấy số gia tăng lên và bây giờ chúng tôi biết chúng tôi sẽ kiếm được bao nhiêu tiền lãi đơn giản trong một năm trên $1000 – $10.000
Bạn muốn thấy mọi thứ thay đổi? . 1 trong ô B1 đến. 05 [5%] và mọi thứ sẽ thay đổi trên diện rộng
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ủa 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ạo 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