Những tính năng hiệu quả nhất của hàm Index trong Excel (P2)

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

Những tính năng hiệu quả nhất của hàm Index trong Excel (P2)

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Có thể hàm Index trong Excel không có nhiều ứng dụng thực tế, nhưng nếu hàm Index trong excel kết hợp với các hàm khác như MATCH hay COUNTA, nó có thể tạo các công thức rất mạnh.

5. Công thức Index để tạo các dải động và danh sách theo mục

Điều này rất thường xảy ra. Khi bạn bắt đầu tổ chức dữ liệu trong một bảng tính, bạn có thể không biết có bao nhiêu mục bạn sẽ có.

Dù sao thì, nếu bạn có số các mục trong một cột đã cho thay đổi, từ A1 đến An, và bạn có thể muốn tạo ra một dải được đặt tên – bao gồm tất cả các ô có dữ liệu. Tại đó, bạn muốn dải được điều chỉnh tự động khi bạn thêm mục mới hoặc xóa một số mục hiện có. Ví dụ: nếu bạn hiện có 10 mục, dải được đặt tên của bạn sẽ là A1: A10. Nếu bạn thêm một mục nhập mới, phạm vi tên sẽ tự động mở rộng lên A1: A11 và nếu bạn thay đổi ý định và xóa dữ liệu mới được thêm vào, phạm vi sẽ tự động quay trở lại A1: A10.

Lợi ích chính của cách tiếp cận này là bạn không phải liên tục cập nhật tất cả các công thức trong bảng tính của bạn để đảm bảo chúng tham chiếu đến các dải một cách chính xác.

5.1. Xác định một dải động được sử dụng hàm OFFSET

= OFFSET (Sheet_Name! $ A $ 1, 0, 0, COUNTA (Sheet_Name! $A :$A ), 1)

5.2. Sử dụng hàm Index trong Excel cùng với hàm Counta

= Sheet_Name! $ A $ 1: INDEX (Sheet_Name! $ A: $ A, COUNTA (Sheet_Name! $A :$A ))

Trong cả hai công thức, A1 là ô có chứa mục đầu tiên của danh sách và dải động được tạo ra bởi cả hai công thức sẽ giống hệt nhau.

Sự khác biệt ở đây chính là cách tiếp cận. Hàm INDEX tìm thấy một ô ở giao điểm của một hàng và cột cụ thể, trong khi hàm OFFSET di chuyển từ điểm làm mốc bởi một số hàng và / hoặc các cột nhất định. Hàm COUNTA, được sử dụng trong cả hai công thức, tính số lượng các ô không rỗng trong cột và đảm bảo rằng chỉ những ô có dữ liệu được bao gồm trong dải đã đặt tên.

Hình sau đây sẽ cho bạn thấy cách bạn có thể sử dụng công thức Index để tạo một danh sách tùy chọn.

Sử dụng hàm Index trong Excel cùng với hàm Counta

Sử dụng hàm Index trong Excel cùng với hàm Counta

Mẹo: Cách dễ nhất để tạo một danh sách tùy chọn được cập nhật tự động trong Excel là tạo một danh sách (đã đặt tên) và được liệt kê dựa trên một bảng. Trong trường hợp này, bạn sẽ không cần bất kỳ công thức phức tạp nào vì các bảng Excel là dải động.

6. Sức mạnh của các hàm Vlookup khi kết hợp với INDEX / MATCH

Thực hiện các hàm Vlookup theo chiều dọc trong Excel – đây là nơi mà hàm Index trong Excel thực sự tỏa sáng. Nếu bạn đã từng thử sử dụng hàm Vlookup trong Excel, bạn sẽ nhận thức được nhiều hạn chế của nó, chẳng hạn như không có khả năng kéo giá trị từ các cột qua bên trái của cột tra cứu hoặc giới hạn 255 ký tự cho một giá trị tra cứu.

Hàm INDEX / MATCH tốt hơn VLOOKUP ở nhiều khía cạnh:

  • Không có vấn đề với dò tìm bên trái.
  • Không giới hạn kích thước giá trị tra cứu.
  • Không yêu cầu phân loại (VLOOKUP với kết quả tương đối khi yêu cầu sắp xếp cột tra cứu theo thứ tự tăng dần).
  • Bạn được tự do chèn và loại bỏ các cột trong một bảng mà không cần cập nhật mỗi công thức liên quan.

Và cuối cùng nhưng không kém phần quan trọng, INDEX / MATCH không làm chậm Excel của bạn như nhiều hàm Vlookup đã làm.

Bạn sử dụng INDEX / MATCH theo cách sau:

= INDEX (column to return a value from, (MATCH (lookup value, column to lookup against, 0))

Ví dụ: nếu chúng ta mở bảng dữ liệu gốc của chúng ta ra với tên hành tinh (planet name) trở thành cột đầu tiên bên phải, thì hàm công thức INDEX / MATCH vẫn lấy giá trị phù hợp từ cột bên trái mà không gặp bất kì trở ngại nào.

7. Sử dụng hàm Index trong Excel để lấy 1 dải từ danh sách các dải

Một cách sử dụng thông minh và hữu hiệu hàm Index trong Excel là bạn có thể có được một dải từ một danh sách các dải.

Giả sử, bạn có nhiều danh sách với số lượng các mục khác nhau.

Trước hết, bạn đặt tên cho mỗi dải trong mỗi danh sách; Ví như có thể đặt là PlanetsD và MoonsD trong ví dụ này:

Tuy nhiên, bảng Moons còn chưa hoàn chỉnh, có 176 mặt trăng tự nhiên được biết đến trong Hệ Mặt trời của chúng ta, Jupiter có 63 và vẫn còn nhiều hơn nữa. Đối với ví dụ này, tôi chọn 11 cái ngẫu nhiên.

Giả sử rằng PlanetsD là dải 1 của bạn và MoonsD nằm trong dải 2 và ô B1 là nơi bạn đặt số dãy, bạn có thể sử dụng công thức Index sau để tính giá trị trung bình của các giá trị trong dải ô được chọn:

= AVERAGE (INDEX ((PlanetsD, MoonsD),,, B1))

Hãy lưu ý rằng bây giờ chúng ta đang sử dụng định dạng tham chiếu của hàm INDEX, và số trong đối số cuối cùng (area_num) cho công thức biết phạm vi dải nào được lựa chọn.

Trong hình bên dưới, area_num (cell B1) được đặt thành 2, trong công thức tính đường kính trung bình của Moons vì dải MoonsD đứng thứ 2 trong đối số tham chiếu.

8. Sử dụng hàm Index trong excel kết hợp với hàm If trong Excel

Nếu bạn làm việc với nhiều danh sách và không muốn nhớ các số liên quan, bạn có thể sử dụng thêm hàm IF trong Excel để làm điều này cho bạn:

= AVERAGE (INDEX ((PlanetsD, MoonsD),,, IF (B1 = “planets”, 1, IF (B1 = “moon”, 2))))

Trong hàm IF, bạn sử dụng một số tên danh sách đơn giản và dễ nhớ mà bạn muốn người dùng nhập vào ô B1 thay vì số. Xin lưu ý rằng, để công thức làm việc chính xác, văn bản trong B1 phải chính xác như nhau (không phân biệt chữ hoa chữ thường) như trong các thông số của IF, nếu không công thức Index của bạn sẽ xuất ra #VALUE lỗi.

Thậm chí, để công thức thân thiện hơn, bạn có thể sử dụng Excel Data Validation để tạo danh sách tùy chọn để tránh lỗi chính tả và lỗi trang in:

Cuối cùng, để làm cho công thức INDEX của bạn hoàn toàn hoàn hảo, bạn có thể đặt nó trong hàm IFERROR sẽ nhắc người dùng chọn một mục từ danh sách theo mục, nếu không có lựa chọn nào được đưa ra:

= IFERROR (AVERAGE (INDEX ((PlanetsD, MoonsD),,, IF (B1 = “planet”, 1, IF (B1 = “moon”, 2)))), “Please select the list!”)

Trên đây là hướng dẫn cách bạn sử dụng công thức Index trong Excel. Tôi hy vọng những ví dụ này cho bạn vài cách để khai thác tiềm năng của hàm Index trong Excel trong bảng tính của bạn.

Chúc bạn thực hiện thành công.

Tham khảo thêm 2 khóa học hữu ích do giảng viên quốc tế trường FPT Arena giảng dạy: