Tham chiếu ô tương đối và tuyệt đối và định dạng

Trong bài học này, chúng tôi thảo luận về các tham chiếu ô, cách sao chép hoặc di chuyển một công thức và định dạng các ô. Để bắt đầu, hãy làm rõ ý nghĩa của chúng ta bằng cách tham chiếu ô, làm nền tảng cho phần lớn sức mạnh và tính linh hoạt của các công thức và hàm. Nắm bắt cụ thể về cách hoạt động của các tham chiếu ô sẽ cho phép bạn tận dụng tối đa các bảng tính Excel của mình!

CHUYỂN ĐỔI TRƯỜNG

1. Tại sao bạn cần công thức và hàm?
2. Xác định và tạo công thức
3. Tham chiếu ô tương đối và tuyệt đối và định dạng
4. Các chức năng hữu ích bạn nên biết
5. Tra cứu, Biểu đồ, Thống kê và Bảng Pivot

Chú thích: chúng ta sẽ giả sử rằng bạn đã biết rằng một ô là một trong những ô vuông trong bảng tính, được sắp xếp thành các cột và hàng được tham chiếu bởi các chữ cái và số chạy theo chiều ngang và chiều dọc.

Tham chiếu di động là gì?

Một tham chiếu ô của người Viking có nghĩa là ô mà một ô khác tham chiếu. Ví dụ: nếu trong ô A1 bạn có = A2. Sau đó, A1 đề cập đến A2.

Hãy xem lại những gì chúng ta đã nói trong Bài 2 về các hàng và cột để chúng ta có thể khám phá thêm các tham chiếu ô.

Các ô trong bảng tính được gọi bằng các hàng và cột. Các cột là dọc và được dán nhãn bằng các chữ cái. Hàng nằm ngang và được dán nhãn bằng số.

Ô đầu tiên trong bảng tính là A1, có nghĩa là cột A, hàng 1, B3 đề cập đến ô nằm trên cột thứ hai, hàng thứ ba, v.v..

Đối với mục đích học tập về tham chiếu ô, đôi khi chúng ta sẽ viết chúng dưới dạng hàng, cột, đây không phải là ký hiệu hợp lệ trong bảng tính và chỉ đơn giản là để làm cho mọi thứ rõ ràng hơn.

Các loại tham chiếu ô

Có ba loại tham chiếu ô.

Tuyệt đối - Điều này có nghĩa là tham chiếu ô vẫn giữ nguyên nếu bạn sao chép hoặc di chuyển ô sang bất kỳ ô nào khác. Điều này được thực hiện bằng cách neo hàng và cột, vì vậy nó không thay đổi khi được sao chép hoặc di chuyển.

Tương đối - Tham chiếu tương đối có nghĩa là địa chỉ ô thay đổi khi bạn sao chép hoặc di chuyển nó; tức là tham chiếu ô có liên quan đến vị trí của nó.

Hỗn hợp - Điều này có nghĩa là bạn có thể chọn neo hoặc hàng hoặc cột khi bạn sao chép hoặc di chuyển ô, để cái này thay đổi và cái kia thì không. Ví dụ: bạn có thể neo tham chiếu hàng sau đó di chuyển một ô xuống hai hàng và trên bốn cột và tham chiếu hàng vẫn giữ nguyên. Chúng tôi sẽ giải thích điều này dưới đây.

Tài liệu tham khảo tương đối

Hãy tham khảo ví dụ trước đó - giả sử trong ô A1, chúng ta có một công thức đơn giản chỉ nói = A2. Điều đó có nghĩa là đầu ra Excel trong ô A1 bất cứ thứ gì được nhập vào ô A2. Trong ô A2, chúng tôi đã nhập vào bản A2 A2, do đó Excel sẽ hiển thị giá trị của bản A2 A2 trong ô A1.

Bây giờ, giả sử chúng ta cần tạo khoảng trống trong bảng tính để có thêm dữ liệu. Chúng ta cần thêm các cột ở trên và các hàng ở bên trái, vì vậy chúng ta phải di chuyển ô xuống và sang phải để tạo khoảng trống.

Khi bạn di chuyển ô sang phải, số cột tăng lên. Khi bạn di chuyển nó xuống, số hàng tăng lên. Các ô mà nó trỏ đến, tham chiếu ô, cũng thay đổi. Điều này được minh họa dưới đây:

Tiếp tục với ví dụ của chúng tôi và nhìn vào hình bên dưới, nếu bạn sao chép nội dung của ô A1 hai sang phải và bốn xuống, bạn đã di chuyển nó sang ô C5.

Chúng tôi đã sao chép ô hai cột bên phải và bốn cột xuống. Điều này có nghĩa là chúng tôi đã thay đổi ô mà nó đề cập hai và bốn xuống. A1 = A2 bây giờ là C5 = C6. Thay vì đề cập đến A2, bây giờ ô C5 đề cập đến ô C6.

Giá trị hiển thị là 0 vì ô C6 trống. Trong ô C6, chúng tôi gõ vào Tôi là C6, và bây giờ, C5 hiển thị, Tôi là C6.

Ví dụ: Công thức văn bản

Hãy thử một ví dụ khác. Hãy nhớ từ Bài 2 nơi chúng ta phải chia một tên đầy đủ thành tên và họ? Điều gì xảy ra khi chúng tôi sao chép công thức này?

Viết công thức = RIGHT (A3, LEN (A3) - TÌM (Mạnh, Lần, A3) - 1) hoặc sao chép văn bản vào ô C3. Không sao chép ô thực tế, chỉ văn bản, sao chép văn bản, nếu không nó sẽ cập nhật tham chiếu.

Bạn có thể chỉnh sửa nội dung của một ô ở đầu bảng tính trong hộp bên cạnh nơi nói là fx .x Hộp đó dài hơn một ô rộng, do đó dễ chỉnh sửa hơn.

Bây giờ chúng tôi có:

Không có gì phức tạp, chúng tôi vừa viết một công thức mới vào ô C3. Bây giờ sao chép C3 vào các ô C2 và C4. Quan sát kết quả dưới đây:

Bây giờ chúng ta có tên đầu tiên của Alexander Hamilton và Thomas Jefferson.

Sử dụng con trỏ để tô sáng các ô C2, C3 và C4. Trỏ con trỏ đến ô B2 và dán nội dung. Hãy nhìn vào những gì đã xảy ra - chúng tôi nhận được một lỗi: Lần #REF. Tại sao điều này là?

Khi chúng tôi sao chép các ô từ cột C sang cột B, nó đã cập nhật một cột tham chiếu sang trái = RIGHT (A2, LEN (A2) - TÌM (Lỗi, Lỗi, A2) - 1).

Nó thay đổi mọi tham chiếu đến A2 sang cột bên trái của A, nhưng không có cột bên trái cột A. Vì vậy, máy tính không biết ý của bạn là gì.

Ví dụ, công thức mới trong B2 là = RIGHT (#REF !, LEN (#REF!) - TÌM (cảm, gợi, # REF!) - 1) và kết quả là #REF:

Sao chép một công thức vào một phạm vi ô

Sao chép các ô rất tiện dụng vì bạn có thể viết một công thức và sao chép nó vào một vùng rộng lớn và tham chiếu được cập nhật. Điều này tránh việc phải chỉnh sửa từng ô để đảm bảo nó trỏ đến đúng vị trí.

Theo phạm vi của phạm vi, chúng tôi có nghĩa là nhiều hơn một tế bào. Ví dụ: (C1: C10) có nghĩa là tất cả các ô từ ô C1 đến ô C10. Vì vậy, nó là một cột của các tế bào. Một ví dụ khác (A1: AZ1) là hàng trên cùng từ cột A đến cột AZ.

Nếu một phạm vi vượt qua năm cột và mười hàng, thì bạn chỉ ra phạm vi đó bằng cách viết ô trên cùng bên trái và dưới cùng bên phải, ví dụ: A1: E10. Đây là một khu vực hình vuông cắt ngang các hàng và cột và không chỉ là một phần của cột hoặc một phần của hàng.

Dưới đây là một ví dụ minh họa cách sao chép một ô vào nhiều vị trí. Giả sử chúng tôi muốn hiển thị chi phí dự kiến ​​của mình trong tháng trong bảng tính để chúng tôi có thể lập ngân sách. Chúng tôi tạo một bảng tính như thế này:

Bây giờ, sao chép công thức trong ô C3 (= B3 + C2) vào phần còn lại của cột để tạo số dư hoạt động cho ngân sách của chúng tôi. Excel cập nhật tham chiếu ô khi bạn sao chép nó. Kết quả sẽ được hiển thị dưới đây:

Như bạn có thể thấy, mỗi bản cập nhật ô mới quan hệ đến vị trí mới, vì vậy ô C4 cập nhật công thức của nó thành = B4 + C3:

Ô C5 cập nhật lên = B5 + C4, v.v.

Tài liệu tham khảo tuyệt đối

Một tham chiếu tuyệt đối không thay đổi khi bạn di chuyển hoặc sao chép một ô. Chúng tôi sử dụng dấu $ để tạo tham chiếu tuyệt đối - để nhớ rằng, hãy nghĩ về ký hiệu đô la như một mỏ neo.

Ví dụ: nhập công thức = $ A $ 1 vào bất kỳ ô nào. $ Phía trước cột A có nghĩa là không thay đổi cột, $ ở phía trước hàng 1 có nghĩa là không thay đổi cột khi bạn sao chép hoặc di chuyển ô sang bất kỳ ô nào khác.

Như bạn có thể thấy trong ví dụ bên dưới, trong ô B1, chúng ta có một tham chiếu tương đối = A1. Khi chúng ta sao chép B1 vào bốn ô bên dưới nó, tham chiếu tương đối = A1 thay đổi sang ô bên trái, do đó B2 trở thành A2, B3 trở thành A3, v.v ... Những ô đó rõ ràng không có giá trị được nhập vào, vì vậy đầu ra bằng không.

Tuy nhiên, nếu chúng tôi sử dụng = $ A1 $ 1, chẳng hạn như trong C1 và chúng tôi sao chép nó vào bốn ô bên dưới nó, tham chiếu là tuyệt đối, do đó nó không bao giờ thay đổi và đầu ra luôn bằng giá trị trong ô A1.

Giả sử bạn đang theo dõi mối quan tâm của mình, chẳng hạn như trong ví dụ dưới đây. Công thức tính theo C4 = B4 * B1 là lãi suất cao

Bây giờ, bạn đã thay đổi ngân sách của mình và đã tiết kiệm thêm 2.000 đô la để mua quỹ tương hỗ. Giả sử nó là một quỹ có lãi suất cố định và nó trả cùng một mức lãi suất. Nhập tài khoản mới và cân bằng vào bảng tính rồi sao chép công thức = B4 * B1 từ ô C4 sang ô C5.

Ngân sách mới trông như thế này:

Quỹ tương hỗ mới kiếm được 0 đô la tiền lãi mỗi năm, điều này không thể đúng vì lãi suất rõ ràng là 5%.

Excel làm nổi bật các ô mà một công thức tham chiếu. Bạn có thể thấy ở trên rằng tham chiếu đến lãi suất (B1) được chuyển đến ô trống B2. Chúng ta nên tạo tham chiếu đến B1 tuyệt đối bằng cách viết $ B $ 1 bằng cách sử dụng ký hiệu đô la để neo tham chiếu hàng và cột.

Viết lại phép tính đầu tiên trong C4 để đọc = B4 * $ B $ 1 như hình dưới đây:

Sau đó sao chép công thức đó từ C4 sang C5. Bảng tính bây giờ trông như thế này:

Vì chúng tôi đã sao chép công thức một ô xuống, tức là tăng hàng lên một, công thức mới là = B5 * $ B $ 1. Hiện tại, lãi suất quỹ tương hỗ được tính toán chính xác, vì lãi suất được neo vào ô B1.

Đây là một ví dụ điển hình về việc khi nào bạn có thể sử dụng một tên tên Cameron để chỉ một ô. Một cái tên là một tài liệu tham khảo tuyệt đối. Ví dụ: để gán tên Tỷ lệ lãi suất Điên vào ô B1, nhấp chuột phải vào ô và sau đó chọn Tên xác định.

Tên có thể chỉ một ô hoặc một phạm vi và bạn có thể sử dụng tên trong công thức, ví dụ = Interest_rate * 8 giống như cách viết = $ B $ 1 * 8.

Tài liệu tham khảo hỗn hợp

Tài liệu tham khảo hỗn hợp là khi hoặc hàng hoặc là cột được neo.

Ví dụ: giả sử bạn là một nông dân làm ngân sách. Bạn cũng sở hữu một cửa hàng thức ăn và bán hạt giống. Bạn sẽ trồng ngô, đậu nành và cỏ linh lăng. Bảng tính dưới đây cho thấy chi phí cho mỗi mẫu Anh. Chi phí trên mỗi mẫu Anh = giá trên mỗi pound Anh * £ pound hạt giống trên mỗi mẫu Anh - đó là những gì nó sẽ chi phí cho bạn để trồng một mẫu đất.

Nhập chi phí cho mỗi mẫu Anh là = $ B2 * C2 trong ô D2. Bạn đang nói rằng bạn muốn neo giá mỗi cột pound. Sau đó sao chép công thức đó vào các hàng khác trong cùng một cột:

Bây giờ bạn muốn biết giá trị của hàng tồn kho hạt giống của bạn. Bạn cần giá mỗi pound và số pound trong kho để biết giá trị của hàng tồn kho.

Chúng tôi thêm hai cột: pound pound hạt giống trong kho Hàng và sau đó là giá trị của hàng tồn kho. Bây giờ, sao chép ô D2 sang F4 và lưu ý rằng tham chiếu hàng trong phần đầu tiên của công thức gốc ($ B2) được cập nhật thành hàng 4 nhưng cột vẫn cố định vì $ neo nó vào

Đây là một tham chiếu hỗn hợp vì cột là tuyệt đối và hàng là tương đối.

Tài liệu tham khảo thông tư

Một tham chiếu tròn là khi một công thức đề cập đến chính nó.

Ví dụ, bạn không thể viết c3 = c3 + 1. Kiểu tính toán này được gọi là Cách lặp đi lặp lại có nghĩa là nó lặp lại. Excel không hỗ trợ phép lặp vì nó chỉ tính toán mọi thứ một lần.

Nếu bạn thử làm điều này bằng cách nhập SUM (B1: B5) vào ô B5:

Một màn hình cảnh báo bật lên:

Excel chỉ cho bạn biết rằng bạn có một tham chiếu tròn ở cuối màn hình để bạn có thể không nhận thấy nó. Nếu bạn có tham chiếu hình tròn và đóng bảng tính và mở lại, Excel sẽ cho bạn biết trong cửa sổ bật lên rằng bạn có tham chiếu hình tròn.

Nếu bạn có một tham chiếu hình tròn, mỗi khi bạn mở bảng tính, Excel sẽ cho bạn biết với cửa sổ bật lên rằng bạn có một tham chiếu hình tròn.

Tài liệu tham khảo cho các bảng tính khác

Một sổ làm việc của một người khác là một tập hợp các bảng tính. Như bạn có thể thấy trong ví dụ dưới đây, sổ làm việc mẫu của chúng tôi có nhiều bảng tính (màu đỏ).

Các trang tính theo mặc định được đặt tên là Sheet1, Sheet2, v.v. Bạn tạo một cái mới bằng cách nhấp vào tên + + ở cuối màn hình Excel.

Bạn có thể thay đổi tên bảng tính thành một cái gì đó hữu ích như cho vay trực tiếp.

Hoặc bạn có thể chỉ cần nhấp đúp vào tab và đổi tên nó.

Cú pháp cho một tham chiếu trang tính là = bảng tính! Ô. Bạn có thể sử dụng loại tham chiếu này khi cùng một giá trị được sử dụng trong hai bảng tính, ví dụ về điều đó có thể là:

• Ngày hôm nay
• Tỷ giá chuyển đổi tiền tệ từ Đô la sang Euro
• Bất cứ điều gì có liên quan đến tất cả các bảng tính trong sổ làm việc

Dưới đây là một ví dụ về bảng tính Quan tâm trực tuyến làm cho tham chiếu đến bảng tính Cho vay, tế bào B1.

Nếu chúng ta nhìn vào bảng tính vay vay trực tuyến, chúng ta có thể thấy tham chiếu đến số tiền vay:

Sắp tới tiếp theo

Chúng tôi hy vọng bạn đã nắm vững các tham chiếu ô bao gồm cả tương đối, tuyệt đối và hỗn hợp. Chắc chắn có rất nhiều.

Đó là bài học hôm nay, trong Bài 4, chúng ta sẽ thảo luận về một số hàm hữu ích mà bạn có thể muốn biết để sử dụng Excel hàng ngày.