Trả về mảng excel

Trong bài viết này, Học Excel Online sẽ lấy ví dụ về hàm INDEX trong Excel, qua đó mô tả cho bạn cách sử dụng hiệu quả công cụ này

Trong tất cả các chức năng của Excel mà sức mạnh của họ thường bị đánh giá thấp và không được sử dụng đúng cách, thì INDEX chắc chắn sẽ xếp hạng ở đâu đó trong top 10. Bởi hàm này rất nhạy và có thể kết hợp với các hàm khác nữa để giải quyết nhiều vấn đề đặt ra

Vậy hàm INDEX trong Excel là gì? . Nói cách khác, bạn sử dụng INDEX khi bạn biết [hoặc có thể tính toán] vị trí của một phần tử trong dải và bạn muốn nhận giá trị thực của phần tử đó

Điều này nghe có vẻ tầm thường, nhưng khi bạn nhận ra tiềm năng thực sự của hàm INDEX, thì nó có thể tạo ra những thay đổi quan trọng đối với cách bạn tính toán, phân tích và trình bày dữ liệu trong các bảng

Hàm INDEX – cú pháp và sử dụng cơ bản

Có hai phiên bản của hàm INDEX trong Excel – định dạng dải và định dạng tham chiếu. Và cả hai mẫu đều có thể được sử dụng trong tất cả các phiên bản của Microsoft Excel 2013, Excel 2010, Excel 2007 và 2003

Xem ngay. Tài liệu hướng dẫn sử dụng excel 2003

INDEX  làm việc với mảng

Array INDEX trả về giá trị của một phần tử trong một bảng hoặc một mảng dựa trên số hàng và cột mà bạn chỉ định

Cấu trúc chức năng

INDEX [mảng, hàng_số, [số_cột]]

• mảng – là một tia nắng các ô, có tên sọc hoặc bảng
• row_num – là hàng thứ mấy trong mảng mà bạn muốn được trả về một giá trị. If row_num bị bỏ qua, column_num bị bắt buộc
• column_num – là nhiều cột để trả về một giá trị. If column_num bị bỏ qua, row_num bị bắt buộc

Ví dụ, công thức “= INDEX[A1. D6, 4, 3]” sẽ trả về giá trị tại giao điểm của hàng thứ 4 và cột thứ 3 trong mảng A1. D6, is value in ô C4

Để có ý tưởng về cách hàm INDEX hoạt động trên dữ liệu thực tế, hãy xem ví dụ sau

Thay vì nhập số hàng và cột trong công thức, bạn có thể cung cấp ô tham chiếu để có được công thức. “= CHỈ SỐ [$B$2. $D$6,G2,G1]”

Lưu ý. Việc sử dụng tài liệu tham khảo tuyệt đối [$B$2. $D$6] thay vì tham chiếu tương đối [B2. D6] trong mảng đối số giúp bạn dễ dàng sao chép công thức lên các ô khác. Ngoài ra, bạn có thể chuyển đổi một dải thành một bảng [Ctrl + T] và tham khảo nó bằng tên bảng

Array và INDEX – điều cần nhớ

1. Nếu mảng đối số chỉ bao gồm một hàng hoặc cột, thì bạn có thể xác định hoặc không xác định đối số row_num hoặc cột_num tương ứng
2. Nếu mảng đối số bao gồm nhiều hơn một dòng và row_num bị bỏ qua hoặc bằng 0, thì hàm INDEX trả về một mảng của toàn bộ cột. Tương tự, nếu mảng bao gồm nhiều cột và đối số column_num bị bỏ qua hoặc bằng 0, công thức INDEX sẽ trả về toàn bộ hàng. Dưới đây là một ví dụ về công thức minh họa
3. Các đối số row_num và column_num phải tham chiếu đến một ô trong mảng; . lỗi

INDEX format tham chiếu

Định dạng tham chiếu của hàm INDEX trả về tham chiếu ô tại giao điểm của hàng và cột đã xác định

Cấu trúc chức năng

INDEX [tham chiếu, row_num, [column_num], [area_num]]

• tham chiếu – là một hoặc nhiều dải

Nếu bạn nhập nhiều hơn một phạm vi, hãy tách các khoảng cách bằng dấu phẩy và kèm theo các đối số tham chiếu trong trích dẫn đơn, ví dụ [A1. B5,D1. F5]

Nếu mỗi dãy trong tài liệu tham khảo chỉ chứa một hàng hoặc một cột, các đối số row_num hoặc column_num tương ứng là tùy chọn

• row_num – số thứ tự của hàng nằm trong khoảng mà từ đó trả về một ô tham chiếu, nó tương tự như dạng mảng
• column_num – cột số thứ tự để trả về một ô tham chiếu, hoạt động cũng tương tự như dạng mảng
• area_num – một tham số tùy chọn chỉ được đặt trong một mảng từ tham số tham chiếu để sử dụng. Nếu bỏ qua, công thức INDEX sẽ trả lại kết quả cho mảng đầu tiên được liệt kê trong danh sách tham khảo

Ví dụ, công thức = INDEX [[A2. D3, A5. D7], 3, 4, 2] Trả về giá trị của ô D7, tại điểm giao của hàng thứ 3 và cột thứ 4 trong khu vực thứ hai [A5. D7]

Định dạng tham chiếu INDEX – những điều cần nhớ

1. Nếu đối số row_num hoặc column_num được đặt thành 0, thì một công thức Excel INDEX trả về tham chiếu cho toàn bộ cột hoặc hàng tương ứng
2. Nếu cả hai row_num và column_num bị bỏ qua, hàm INDEX trả về vùng được chỉ định trong đối số area_num
3. Tất cả các đối số _num [row_num, column_num và area_num] đều phải tham khảo một ô trong tham chiếu; . lỗi

Cả hai công thức INDEX chúng ta đã thảo luận cho đến nay đều rất đơn giản và chỉ minh hoạ cho khái niệm. Công thức thực tế của bạn có thể phức tạp hơn nhiều so với điều đó, vì vậy, hãy cùng khám phá một vài cách sử dụng hiệu quả nhất của INDEX trong Excel

Các hàm thường dùng trong excel

Cách sử dụng hàm INDEX trong Excel- ví dụ, công thức

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

Nguồn dữ liệu [SourceData]

Tất cả các công thức Excel INDEX của chúng tôi [ngoại trừ công thức cuối cùng], chúng tôi sẽ sử dụng dữ liệu dưới đây. Để thuận tiện, nó được tổ chức trong một bảng có tên SourceData

Việc sử dụng các bảng hoặc mảng được đặt tên có thể làm cho công thức dài hơn một chút, nhưng nó cũng làm cho chúng linh hoạt và dễ đọc hơn. Để điều chỉnh bất kỳ công thức INDEX nào cho các bảng tính của bạn, bạn chỉ cần chỉnh sửa một cái tên duy nhất

Tất nhiên, không có gì ngăn cản bạn sử dụng mảng thông thường nếu bạn muốn. Trong trường hợp này, bạn chỉ cần thay thế tên bảng SourceData bằng tham chiếu mảng giải thích

1. Lấy dữ liệu ở vị trí thứ N từ danh sách

Đây là cách sử dụng cơ bản và là ví dụ về hàm INDEX dễ hiểu nhất. Để lấy một dữ liệu được xác định rõ nhất trong danh sách, bạn chỉ cần viết = INDEX[range, n], trong phạm vi đó là một dải ô hoặc dải ô được đặt tên, và n là vị trí của dữ liệu mà bạn muốn nhận

Khi làm việc với bảng Excel, bạn có thể sử dụng con trỏ chuột chọn cột và Excel sẽ đặt tên của cột cùng với tên của bảng vô công thức

Để nhận giá trị của ô tại điểm giao của một hàng và cột đã chọn, bạn sử dụng cách tiếp cận tương tự với sự khác biệt duy nhất là bạn sử dụng cả hai – số hàng và cột số

Và đây là một ví dụ khác. Trong bảng tính mà chúng tôi lấy làm ví dụ để tìm ra hành tinh lớn thứ 2 trong hệ mặt trời, bạn sắp xếp bảng theo cột Đường kính [Đường kính] và sử dụng công thức INDEX như sau

= INDEX [Dữ liệu nguồn, 2, 3]

• Mảng là bảng tên, hoặc một dải tham chiếu, tương ứng với SourceData trong ví dụ này
• Row_num là 2 vì bạn đang tìm kiếm mục thứ hai trong danh sách
• Column_num là 3 vì Đường kính ở vị trí cột thứ 3 trong bảng

Nếu bạn muốn trả lại tên của hành tinh thay vì đường kính, thì bạn phải thay đổi cột_num thành 1. Đương nhiên, bạn có thể sử dụng một ô tham chiếu trong các đối số row_num và/hoặc cột_num để làm cho công thức INDEX của bạn linh hoạt hơn, như có thể hiển thị trong hình bên dưới

1. Đã nhận tất cả các giá trị trong một hàng hoặc cột

Ngoài việc lấy ra một ô duy nhất, hàm INDEX có thể trả về một dải các giá trị từ toàn bộ các hàng hoặc các cột. Để lấy tất cả các giá trị từ một cột nhất định, bạn phải bỏ qua đối số row_num hoặc đặt nó là 0. Tương tự, để có được toàn bộ hàng, bạn bỏ qua hoặc 0 trong cột_num

Công thức INDEX như vậy khó có thể được sử dụng bởi vì Excel không thể phù hợp với dải giá trị được trả về bởi công thức trong một ô duy nhất, mà thay vào đó thì bạn sẽ nhận được #VALUE. lỗi. Tuy nhiên, nếu bạn sử dụng INDEX kết hợp với các hàm trong Excel khác, chẳng hạn như SUM hoặc AVERAGE, bạn sẽ thu được kết quả tuyệt vời

Ví dụ, bạn có thể sử dụng công thức Index Excel sau để tính nhiệt độ trung bình của hành tinh trong hệ mặt trời

= TRUNG BÌNH [INDEX [SourceData,, 4]]

Trong công thức trên, đối số column_num là 4 vì Temperature là cột thứ 4 trong bảng của chúng ta. row_num thong số bị bỏ qua

Tương tự như vậy, bạn có thể tìm được nhiệt độ tối thiểu và tối đa

= MAX[INDEX[SourceData,,4]]

= MIN[INDEX[SourceData,,4]]

Và tính toán tổng khối lượng hành động [Khối lượng là cột thứ 2 trong bảng]

= SUM[INDEX[SourceData,,2]]

Từ quan điểm thực tế, hàm INDEX trong công thức trên là không cần thiết. Bạn chỉ cần viết = AVERAGE [dải] hoặc = SUM [dải] và cũng nhận được kết quả tương tự

Khi làm việc với dữ liệu thực tế, tính năng này có thể hữu ích như là một phần của các công thức phức tạp hơn mà bạn sử dụng để phân tích dữ liệu

1. Sử dụng INDEX với các hàm Excel khác [SUM, AVERAGE, MAX, MIN]

Từ ví dụ trước, bạn có thể nghĩ rằng công thức INDEX trong Excel trả về các giá trị, nhưng thực tế là nó trả về một tham chiếu đến ô có chứa giá trị. And ví dụ này có thể hiện thực chất của hàm INDEX

Với kết quả của một công thức INDEX mang tính tham chiếu, chúng ta có thể sử dụng nó trong các hàm khác để tạo ra một dải động. Khó hiểu ư?

Giả sử bạn có một công thức = AVERAGE[A1. A10] return the normal value of the A1. A10. Thay vì viết dải trực tiếp như trong công thức, thì bạn có thể thay thế cả A1 hoặc A10 hoặc cả hai bằng các hàm INDEX như sau

= TRUNG BÌNH[A1. CHỈ SỐ [A1. A20,10]]

Cả hai công thức trên sẽ mang lại kết quả tương tự do hàm INDEX cũng trả về một tham chiếu đến ô A10 [row_num được đặt là 10, col_num bỏ qua]. Sự khác biệt là công thức AVERAGE / INDEX sử dụng cho dải động và khi bạn thay đổi đối số row_num trong INDEX, thì dải mà được xử lý bởi hàm AVERAGE sẽ thay đổi và công thức sẽ trả về một kết quả khác

Rõ ràng, tuy nhiên cách công thức INDEX vận hành thể hiện ra quá phức tạp, nhưng nó có các ứng dụng thực tế, như trong các ví dụ sau đây

Ví dụ 1. Tính trung bình của các mục trong danh sách n mục đứng đầu

Giả sử bạn muốn biết đường kính trung bình của n hành tinh lớn nhất trong hệ thống của chúng tôi. Vì vậy, bạn sắp xếp bảng theo Đường kính cột từ lớn nhất đến nhỏ nhất và sử dụng công thức Trung bình / Chỉ số sau

= TRUNG BÌNH[C5. CHỈ SỐ [SouceData [Đường kính], B1]]

Ví dụ 2. Tính tổng các giá trị giữa 2 mục cụ thể

Trong trường hợp bạn muốn xác định giới hạn trên và giới hạn dưới trong công thức của mình, chỉ cần sử dụng hai hàm INDEX để trả về mục đầu tiên và mục cuối cùng bạn muốn

Ví dụ. công thức sau trả về tổng giá trị trong cột Đường kính [đường kính] giữa hai dữ liệu được chỉ định trong ô B1 và ​​B2

= SUM[INDEX[SourceData [Đường kính], B1]. CHỈ SỐ [Dữ liệu nguồn [Đường kính], B2]]

1. 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 không thể biết có bao nhiêu mục bạn sẽ có

Dù sao thì, nếu bạn có số mục trong một cột đã 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 thời điểm đó, 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 được 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.

Một cách để 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]

Một giải pháp có thể khác là sử dụng Excel INDEX cùng với COUNTA

= Sheet_Name. $A$1. INDEX [Sheet_Name. $A. $A, COUNTA [Sheet_Name. $A. $A ]]

Trong cả hai công thức, A1 là ô 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 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 việ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 trố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 danh sách tùy chọn

mẹo. Cách dễ nhất để tạo tùy chọn danh sách đượ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

1. 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 thực sự tỏa sáng. Nếu bạn đã từng thử sử dụng hàm VLOOKUP, bạn sẽ nhận thức được nhiều hạn chế của nó, giả sử 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á . Và vì thế cho nên kết hợp chỉ số và trận đấu là giải pháp mà nhiều người lựa chọn để thay thế

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

• Không có vấn đề gì với việc tìm kiế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 yêu cầu [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 đã tự động thêm 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 làm mất các 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

You use INDEX / MATCH – hàm index kết hợp khớp theo cách sau

= INDEX [cột để trả về một giá trị từ, [MATCH [giá trị tra cứu, cột để tra cứu, 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 [tên hành tinh] 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ỳ kỳ hạn nào.

Khóa học excel kế toán trực tuyến

1. Sử dụng hàm INDEX để 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ể lấy đượ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;

Tuy nhiên, bảng Mặt trăng vẫ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, Sao Mộc 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 dãy số, bạn có thể sử dụng công thức Index sau để tính giá trị trung bình của giá trị trong dải được chọn

= TRUNG BÌNH [CHỈ SỐ [[PlanetsD, MoonsD],,, B1]]

Xin 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 [ô 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 tham số tham chiếu

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 để làm điều này cho mình

= AVERAGE [INDEX [[Các hành tinh, Mặt trăng],,, OF [B1 = “các hành tinh”, a1, IF [B1 = “mặt trăng”, 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 #

Ngoài ra, để công thức thân thiện hơn, bạn có thể sử dụng Xác thực dữ liệu Excel để tạo danh sách tùy chọn để tránh lỗi mô tả chính xác và lỗi trang trong

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=“hành tinh”, 1, IF[B1=“mặt trăng”, 2]]]], “Vui lòng chọn danh sách. ”]

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 sẽ cho bạn một 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. Thank you have read