Computer >> Máy Tính >  >> Phần mềm >> Office

Cách sử dụng hàm gián tiếp trong Excel

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, 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ực hành để hiểu rõ, 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 ra 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 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á theo thời gian thực, do đó, tham chiếu luôn chính xác với dữ liệu mà nó lấy ra 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 các dãy được đặt tên

Các phạm vi được đặ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:

  1. Mở tài liệu Excel với các phạm vi được đặ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 sản phẩm.

    Cách sử dụng hàm gián tiếp trong Excel

  2. 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 Bánh mì kẹp thịt . Thêm các chức danh chỉ định khác và màu nếu bạn muốn.

    Cách sử dụng hàm gián tiếp trong Excel

  3. Chọn một ô khác mà bạn muốn xuất đầ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)

  4. Điều này chỉ định một hàm SUM, 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 khác và kết quả đầu ra sẽ thay đổi thành tổng SUM của chúng.

    Cách sử dụng hàm gián tiếp trong Excel

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 đó.

Đây là cách di chuyển xung quanh và giữa các tab trang tính trong Excel

  1. Mở tài liệu Excel của bạn với nhiều trang tính hoặc tạo chúng với tất cả thông tin cần thiết.

  2. 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 .

  3. Vì chúng tôi muốn lấy thông tin từ FoodSales của chúng tôi trang tính tổng số Bánh mì kẹp thịt đã bán, chúng tôi đã nhập như sau (thay thế (các) phạm vi ô và (các) tên trang tính bằng của riêng bạn):

    =SUM (INDIRECT (B4 &"! B4:B10"))

    Cách sử dụng hàm gián tiếp trong Excel

  4. Đ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 số. 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 lại với nhau, theo sau là dấu ngoặc kép và dấu chấm than, sau đó là phạm vi ô mà chúng tôi muốn lấy dữ liệu từ đó. B4 thông qua B10 .

  5. Đầu ra là tổng doanh thu của Burger trong tuần đó. Khi chúng tôi tạo trang tính 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 hàng của Burger trong tuần đó.

Sử dụng chức năng 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.

  1. Mở tài liệu Excel với tất cả dữ liệu bạn muốn lấy từ đó 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.

  2. Trong ví dụ của chúng tôi, công thức có dạng như sau:

    =INDIRECT ("R12C" &COUNTA (12:12), FALSE)

    Cách sử dụng hàm gián tiếp trong Excel

  3. Hàm gián tiếp đang sử dụng R12 (hàng 12) theo sau là C để biểu thị một cột, được đặt trong dấu ngoặc kép. & nối hai phần của chức năng với nhau. Chúng tôi đang sử dụng COUNTA hàm đếm tất cả các ô không trống trong hàng 12 (chọn hàng hoặc gõ 12:12), theo sau là dấu phẩy. FALSE chỉ định đây là tham chiếu R1C1.

  4. Đầu ra sau đó là mục nhập cuối cùng trong bảng của chúng ta, trong trường hợp này là 8102, hoặc $ 8,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.

    Cách sử dụng hàm gián tiếp trong Excel