Sau khi bạn biết cách sử dụng hàm gián tiếp trong Excel, bạn có thể lấy thông tin từ các trang tính khác, các phạm vi được đặt tên tham chiếu và kết hợp nó với các hàm khác để tạo ra một công cụ thực sự linh hoạt. Có thể mất một chút thời gian thực hành để nắm bắt, nhưng với chức năng gián tiếp, bạn có thể làm được nhiều hơn những gì bạn có thể nghĩ.
Hướng dẫn trong bài viết này áp dụng cho Excel for Microsoft 365, Excel 2019 và Excel 2016.
Chức năng Gián tiếp là gì?
Hàm gián tiếp là một cách để chuyển đổi một chuỗi văn bản thành một tham chiếu. Nghĩa là, nó lấy thông tin từ một tham chiếu đến một ô hoặc dải ô khác. Nó tạo một tham chiếu từ văn bản và không thay đổi khi các ô, hàng hoặc cột được thay đổi, thêm vào hoặc xóa khỏi phạm vi được trích dẫn. Các tham chiếu mà nó tạo ra được đánh giá trong thời gian thực, vì vậy, tham chiếu luôn chính xác với dữ liệu mà nó lấy từ đó.
Nếu điều đó cảm thấy hơi khó hiểu, đừng băn khoăn. Công thức gián tiếp có thể dễ hiểu hơn với các ví dụ hợp lệ và trong thực tế. Nếu nghi ngờ, hãy làm theo các bước bên dưới, và bạn sẽ sớm hiểu được nó.
Sử dụng Chức năng Gián tiếp Với Phạm vi Được Đặt tên
Dải ô đã đặt tên trong Excel là một cách tuyệt vời để thu thập dữ liệu dưới một tham chiếu duy nhất và hàm gián tiếp giúp lấy thông tin từ chúng dễ dàng hơn một chút. Đây là cách thực hiện:
-
Mở tài liệu Excel với các dải ô đã đặt tên đã được áp dụng. Trong ví dụ của chúng tôi, chúng tôi có thông tin bán hàng từ các loại thực phẩm và đồ uống khác nhau, với số tiền kiếm được qua mỗi ngày trong tuần được thu thập theo các phạm vi được đặt tên có tiêu đề sau các sản phẩm.
Image -
Chọn một ô cho phạm vi đã đặt tên của bạn và nhập một trong số chúng vào đó. Trong ví dụ của chúng tôi, chúng tôi đã sử dụng Burgers. Thêm các chức danh chỉ định khác và màu nếu bạn muốn.
Image -
Chọn một ô khác mà bạn muốn sử dụng đầu ra gián tiếp. Vì chúng tôi đang tìm cách cộng tất cả doanh số bán hàng trong tuần cho một loại thực phẩm cụ thể, trong trường hợp này là Burgers, chúng tôi sẽ nhập thông tin sau vào ô:
=SUM (INDIRECT (G5)
-
Hàm này chỉ định một hàm SUM, hàm này sẽ sử dụng hàm gián tiếp để lấy thông tin từ phạm vi được đặt tên trong ô G5, trong trường hợp này là Burgers. Sản lượng là 3781, tổng doanh thu trong tuần của Burgers.
Trong ví dụ của chúng tôi, chúng tôi có thể thay thế Burgers trong ô G5 bằng Lemonade hoặc Desserts, hai phạm vi được đặt tên còn lại và kết quả đầu ra sẽ thay đổi thành tổng SUM của chúng.
Image
Sử dụng Chức năng Gián tiếp trên Nhiều Trang tính
Công thức gián tiếp thậm chí còn mạnh mẽ hơn khi bạn sử dụng nó để lấy thông tin từ các trang tính khác. Bạn cũng không cần phải sử dụng các dải ô đã đặt tên để làm điều đó.
- Mở tài liệu Excel của bạn bằng nhiều trang tính hoặc tạo chúng với tất cả thông tin cần thiết.
- Trong trang tính mà bạn muốn xuất đầu ra gián tiếp, hãy tạo một ô có tên trang tính mà bạn muốn lấy thông tin từ đó. Trong ví dụ của chúng tôi, đó là FoodSales.
-
Vì chúng tôi muốn lấy thông tin từ tờ FoodSalescủa chúng tôi để tổng số Burgersđã bán, chúng tôi đã nhập như sau (thay thế phạm vi ô và (các) tên trang tính của riêng bạn):
=SUM (INDIRECT (B4 &"! B4: B10"))
Image - Điều này chỉ định nó là một hàm SUM, vì chúng tôi đang cố gắng tìm tổng. Sau đó, nó chỉ định ô B4 làm văn bản tham chiếu cho hàm gián tiếp. &tập hợp các phần tử của hàm này, theo sau là dấu ngoặc kép và dấu chấm than, sau đó là phạm vi ô mà chúng ta muốn lấy dữ liệu từ đó. B4qua B10
- Đầu ra là tổng doanh số bán Burger trong tuần đó. Khi chúng tôi tạo một trang FoodSales2 mới cho một tuần mới với các số khác nhau, chúng tôi chỉ cần điều chỉnh ô B4 để nói FoodSales2để nhận dữ liệu về doanh số bán bánh Burger trong tuần đó.
Sử dụng Hàm Gián tiếp Với Tham chiếu Kiểu R1C1
Đối với các trang tính liên tục mở rộng, trong đó tham chiếu bạn muốn sử dụng không phải lúc nào cũng nằm trong cùng một ô, có thể sử dụng tham chiếu Kiểu R1C1 với công thức gián tiếp để cung cấp cho bạn thông tin bạn cần. Chúng tôi sẽ tiếp tục sử dụng các ví dụ về bán hàng thực phẩm của chúng tôi ở đây, nhưng hãy tưởng tượng nó dành cho một bảng tính cấp cao hơn xem xét tổng doanh số hàng tuần.
- Mở tài liệu Excel với tất cả dữ liệu bạn muốn lấy ra và chọn một ô cho đầu ra hàm gián tiếp của bạn. Trong ví dụ của chúng tôi, chúng tôi đang xem xét tổng doanh số bán thực phẩm hàng tháng và chúng tôi muốn biết tổng doanh số bán hàng gần đây nhất trong tháng.
-
Trong ví dụ của chúng ta, công thức có dạng như sau:
=INDIRECT ("R12C" & COUNTA (12:12), FALSE)
Image - Hàm gián tiếp đang sử dụng R12 (hàng 12) theo sau là dấu C để biểu thị một cột, được đặt trong dấu ngoặc kép. &nối hai phần của hàm lại với nhau. Chúng tôi đang sử dụng hàm COUNTA để đếm tất cả các ô không trống trong hàng 12 (chọn hàng hoặc nhập 12:12), theo sau là dấu phẩy. FALSE chỉ định đây là tham chiếu R1C1.
-
Đầu ra sau đó là mục nhập cuối cùng trong bảng của chúng tôi, trong trường hợp này là 8102, hoặc 8 đô la, 102. Cuối cùng, khi chúng tôi thêm dữ liệu bán hàng của tháng 4, số bán hàng mới nhất sẽ tự động cập nhật theo thời gian thực.
Image
