Microsoft Excel có một số thủ thuật thú vị và sử dụng các công thức dãy động SUM và INDIRECT chỉ là hai cách để dễ dàng thao tác dữ liệu bạn có.
Tổng quan về công thức SUM - INDIRECT
Sử dụng hàm INDIRECT trong các công thức Excel giúp dễ dàng thay đổi phạm vi tham chiếu ô được sử dụng trong công thức mà không phải chỉnh sửa chính công thức đó.
INDIRECT có thể được sử dụng với một số hàm chấp nhận tham chiếu ô làm đối số, chẳng hạn như hàm OFFSET và hàm SUM.
Trong trường hợp thứ hai, việc sử dụng hàm INDIRECT làm đối số cho hàm SUM có thể tạo một phạm vi tham chiếu ô động mà hàm SUM sau đó sẽ tăng lên.
INDIRECT thực hiện điều này bằng cách tham chiếu đến dữ liệu trong các tế bào gián tiếp thông qua một vị trí trung gian.
Ví dụ: Công thức SUM - INDIRECT được sử dụng để Tổng số giá trị động
Ví dụ này dựa trên dữ liệu được hiển thị trong hình trên.
Công thức SUM - INDIRECT được tạo ra bằng cách sử dụng các bước hướng dẫn dưới đây là:
= SUM (INDIRECT ("D" & E1 & ": D" & E2))
Trong công thức này, đối số của hàm INDIRECT lồng nhau chứa tham chiếu đến các ô E1 và E2. Các số trong các ô đó, 1 và 4, khi được kết hợp với phần còn lại của đối số INDIRECT, tạo thành tham chiếu ô D1 và D4.
Kết quả là, phạm vi các số được tổng bằng hàm SUM là dữ liệu chứa trong phạm vi ô từ D1 đến D4 - là 50.
Bằng cách thay đổi các số nằm trong các ô E1 và E2; tuy nhiên, phạm vi được tổng cộng có thể dễ dàng thay đổi.
Ví dụ này trước tiên sẽ sử dụng công thức trên để tổng dữ liệu trong các ô D1: D4 và sau đó thay đổi phạm vi tổng hợp thành D3: D6 mà không sửa công thức trong ô F1.
01/03
Nhập công thức - Tùy chọn
Các tùy chọn để nhập công thức bao gồm:
- gõ công thức trên trực tiếp vào ô F1 và nhấn phím Enter trên bàn phím
- nhập hàm INDIRECT làm đối số bằng cách sử dụng hộp thoại hàm SUM
Hầu hết các hàm trong Excel có một hộp thoại, cho phép bạn nhập mỗi đối số của hàm trên một dòng riêng biệt mà không phải lo lắng về cú pháp .
Trong trường hợp này, hộp thoại hàm SUM có thể được sử dụng để đơn giản hóa công thức đến một mức độ nhất định. Vì hàm INDIRECT đang được lồng vào bên trong hàm SUM, hàm INDIRECT và các đối số của nó vẫn phải được nhập thủ công.
Các bước bên dưới sử dụng hộp thoại SUM để nhập công thức.
Nhập dữ liệu hướng dẫn
Dữ liệu di động D1 - 5 D2 - 10 D3 - 15 D4 - 20 D5 - 25 D6 - 30 E1 - 1 E2 - 4- Nhập dữ liệu sau vào ô D1 đến E2
Bắt đầu công thức SUM - INDIRECT - Mở hộp thoại SUM Function
- Nhấp vào ô F1 - đây là nơi kết quả của ví dụ này sẽ được hiển thị
- Nhấp vào tab Công thức của trình đơn ribbon
- Chọn Math & Trig từ ruy-băng để mở danh sách thả xuống chức năng
- Nhấp vào SUM trong danh sách để mở hộp thoại của hàm
02/03
Nhập hàm INDIRECT - Nhấn vào đây để xem ảnh lớn hơn
Công thức INDIRECT cần được nhập làm đối số cho hàm SUM.
Trong trường hợp các hàm lồng nhau, Excel không cho phép mở hộp thoại của hàm thứ hai để nhập các đối số của nó.
Do đó, hàm INDIRECT phải được nhập thủ công trong dòng Number1 của hộp thoại Hàm SUM.
- Trong hộp thoại, nhấp vào dòng Number1
- Nhập hàm INDIRECT sau: INDIRECT ("D" & E1 & ": D" & E2)
- Nhấn OK để hoàn thành chức năng và đóng hộp thoại
- Số 50 sẽ xuất hiện trong ô F1 vì đây là tổng số cho dữ liệu nằm trong ô D1 đến D4
- Khi bạn bấm vào ô F1 công thức hoàn chỉnh = SUM (INDIRECT ("D" & E1 & ": D" & E2)) xuất hiện trong thanh công thức phía trên trang tính
Phá vỡ chức năng INDIRECT
Để tạo một phạm vi động trong cột D bằng cách sử dụng INDIRECT, chúng ta phải kết hợp chữ D trong đối số của hàm INDIRECT với các số có trong các ô E1 và E2.
Điều này được thực hiện bằng cách sau:
- dấu và ( & ) được sử dụng để nối hoặc ghép lại với nhau dữ liệu văn bản (trong trường hợp này là chữ D) với tham chiếu ô (E1 và E2)
- hơn nữa, dữ liệu văn bản được ghép nối với tham chiếu ô phải được bao quanh bởi dấu ngoặc kép ( "" )
- cuối cùng, các điểm kết thúc của dải ô được phân tách bằng dấu hai chấm (:)
Do đó, điểm bắt đầu của phạm vi được xác định bởi các ký tự: "D" & E1 .
Tập ký tự thứ hai: ": D" & E2 kết hợp dấu hai chấm với điểm kết thúc. Điều này được thực hiện bởi vì dấu hai chấm là một ký tự văn bản và, do đó, phải được bao gồm bên trong dấu ngoặc kép.
Dấu thứ ba ở giữa được sử dụng để nối hai phần vào một đối số :
"D" & E1 & ": D" & E203/03
Tự động thay đổi phạm vi chức năng SUM
Toàn bộ điểm của công thức này là để dễ dàng thay đổi phạm vi tổng cộng bằng hàm SUM mà không cần phải chỉnh sửa đối số của hàm.
Bằng cách bao gồm hàm INDIRECT trong công thức, việc thay đổi các số trong các ô E1 và E2 sẽ thay đổi phạm vi ô được đọc bởi hàm SUM.
Như có thể thấy trong hình trên, điều này cũng dẫn đến câu trả lời của công thức nằm trong ô F1 thay đổi khi nó tổng cộng phạm vi dữ liệu mới.
- Nhấp vào ô E1
- Nhập số 3
- Nhấn phím Enter trên bàn phím
- Nhấp vào ô E2
- Nhập số 6
- Nhấn phím Enter trên bàn phím
- Câu trả lời trong ô F1 sẽ thay đổi thành 90 - là tổng số các số trong ô D3 đến D6
- Kiểm tra thêm công thức bằng cách thay đổi nội dung của ô B1 và B2 thành bất kỳ số nào trong khoảng từ 1 đến 6
INDIRECT và #REF! Giá trị lỗi
#REF! giá trị lỗi sẽ xuất hiện trong ô F1 nếu đối số của hàm INDIRECT:
- không phải là tham chiếu ô hợp lệ
- chứa tham chiếu bên ngoài vào sổ làm việc khác và sổ làm việc không mở
- đề cập đến phạm vi ô bên ngoài giới hạn của trang tính (hàng 1.048,576 hoặc cột XFD)