TRƯỜNG HƯỚNG DẪN
- Tại sao bạn cần công thức và chức năng?
- Định nghĩa và tạo công thức
- Tham khảo và định dạng ô tương đối và tuyệt đối
- Các chức năng hữu ích mà bạn nên biết
- Tra cứu, biểu đồ, thống kê và bảng tổng hợp
Chú thích: chúng tôi sẽ giả định rằng bạn đã biết rằng một ô là một trong các ô 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ằng chữ cái và số chạy theo chiều ngang và chiều dọc.
Tham chiếu ô là gì?
"Tham chiếu ô" có nghĩa là ô mà ô khác đề cập đến. Ví dụ, nếu trong ô A1 bạn có = A2. Sau đó A1 đề cập đến A2.
Các ô trong bảng tính được tham chiếu theo hàng và cột. Cột dọc và được gắn nhãn bằng chữ cái. Hàng nằm ngang và được gắ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 là ô nằm ở 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 tôi viết chúng thành 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 ô đến 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 ô, để thay đổi và ô còn lại thì không. Ví dụ: bạn có thể neo tham chiếu hàng rồi 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 thêm về vấn đề này bên dưới.
Tham khảo tương đối
Hãy tham khảo ví dụ trước đó - giả sử trong ô A1, chúng ta có công thức chỉ đơn giản là nói = A2. Điều đó có nghĩa là đầu ra Excel trong ô A1 bất kỳ được nhập vào ô A2. Trong ô A2 chúng ta đã gõ “A2” để Excel hiển thị giá trị “A2” trong ô A1.
Khi bạn di chuyển ô sang bên phải, số cột sẽ tăng lên. Khi bạn di chuyển xuống, số hàng tăng lên. Các tế bào mà nó trỏ đến, tham chiếu ô, cũng thay đổi. Điều này được minh họa dưới đây:
Chúng tôi đã sao chép ô hai cột bên phải và bốn cột. Điều này có nghĩa là chúng tôi đã thay đổi ô 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 tham chiếu đến ô 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 tôi phải tách một tên đầy đủ thành họ và tên? Điều gì xảy ra khi chúng ta sao chép công thức này?
Bạn có thể chỉnh sửa nội dung của ô ở đầu bảng tính trong hộp bên cạnh vị trí "fx". Hộp đó dài hơn ô rộng, do đó dễ chỉnh sửa hơn.
Bây giờ chúng tôi có:
Sử dụng con trỏ để tô sáng các ô C2, C3 và C4. Trỏ chuột đến ô B2 và dán nội dung. Hãy nhìn vào những gì đã xảy ra - chúng tôi gặp lỗi: “#REF.” Tại sao lại như vậy?
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ủa cột A. Vì vậy, máy tính không biết ý bạn là gì.
Công thức mới trong B2 chẳng hạn, là = RIGHT (#REF!, LEN (#REF!) - FIND (",", # 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 và tham chiếu được cập nhật.Điều này tránh phải chỉnh sửa từng ô để đảm bảo nó trỏ đến đúng vị trí.
Theo "phạm vi", chúng tôi có nghĩa là nhiều hơn một ô. 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 phạm vi kết hợp 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à khu vực hình vuông có các hàng và cột và không chỉ là một phần của một cột hoặc một phần của một hàng.
Như bạn thấy, mỗi 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:
Tham chiếu 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 ta sử dụng ký hiệu $ để tạo ra một tham chiếu tuyệt đối - để nhớ rằng, hãy nghĩ về một ký hiệu đô la như một neo.
Ví dụ: nhập công thức = $ A $ 1 vào bất kỳ ô nào. Các $ ở phía trước của cột A có nghĩa là không thay đổi cột, $ ở phía trước của hàng 1 có nghĩa là không thay đổi cột khi bạn sao chép hoặc di chuyển ô đến bất kỳ ô nào khác.
Như bạn có thể thấy trong ví dụ dưới đây, 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 thành ô bên trái, vì vậy B2 trở thành A2, B3 trở thành A3, vv Những tế bào này rõ ràng không có giá trị đầu vào, do đó, đầu ra là số không.
Tuy nhiên, nếu chúng ta sử dụng = $ A1 $ 1, chẳng hạn như trong C1 và chúng ta 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.
Ngân sách mới trông như sau:
Excel làm nổi bật các ô có tham chiếu công thức. 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 làm cho 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:
Đây là một ví dụ điển hình khi bạn có thể sử dụng “tên” để chỉ một ô. Tên là một tham chiếu tuyệt đối. Ví dụ: để gán tên "lãi suất" cho ô B1, hãy nhấp chuột phải vào ô và sau đó chọn "xác định tên".
Tên có thể tham chiếu đến 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ư viết = $ B $ 1 * 8.
Tham khảo hỗn hợp
Tham chiếu 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 tạo ra một 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í cho mỗi mẫu Anh" = "giá mỗi pound" * "pound hạt trên mỗi mẫu Anh" - đó là những gì bạn sẽ chi phí để trồng một mẫu Anh.
Nhập chi phí cho mỗi mẫu Anh dưới dạng $ B2 * C2 trong ô D2. Bạn đang nói rằng bạn muốn neo cột giá mỗi pound. Sau đó sao chép công thức đó vào các hàng khác trong cùng một cột:
Chúng tôi thêm hai cột: "pound hạt giống trong hàng tồn kho" và sau đó "giá trị của hàng tồn kho." Bây giờ, sao chép ô D2 đến F4 và lưu ý rằng tham chiếu hàng trong phần đầu tiên của công thức ban đầu ($ B2) được cập nhật thành hàng 4 nhưng cột vẫn cố định vì $ neo nó vào “B.”
Tham khảo Thông tư
Tham chiếu vòng tròn là khi công thức tham chiếu đế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à "lặp" nghĩa là nó lặp lại chính nó. Excel không hỗ trợ lặp lại vì nó tính toán mọi thứ chỉ một lần.
Nếu bạn thử làm điều này bằng cách gõ SUM (B1: B5) trong ô B5:
Tài liệu tham khảo cho các bảng tính khác
Một “sổ làm việc” là một tập hợp các “bảng tính”. Nói một cách đơn giản, điều này có nghĩa là bạn có thể có nhiều bảng tính (bảng tính) trong cùng một tệp Excel (bảng tính). Như bạn có thể thấy trong ví dụ bên dưới, sổ làm việc mẫu của chúng tôi có nhiều trang tính (màu đỏ).
Cú pháp cho tham chiếu trang tính là = trang 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 trang tính, ví dụ có thể là:
- Ngày hôm nay
- Tỷ giá quy đổi tiền tệ từ Đô la sang Euro
- Bất kỳ thứ gì có liên quan đến tất cả các trang tính trong sổ làm việc
Dưới đây là một ví dụ về bảng tính "lãi suất" tham chiếu đến bảng tính "khoản vay", ô B1.
Sắp tới …
Chúng tôi hy vọng bây giờ bạn có một nắm bắt vững chắc các tài liệu tham khảo di động 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 của ngày hôm nay, trong Bài 4, chúng tôi sẽ thảo luận một số chức năng hữu ích mà bạn có thể muốn biết để sử dụng Excel hàng ngày.
