ATC CAM KẾT ĐÀO TẠO THÀNH THẠO

3 TÍNH NĂNG HIỆU QUẢ NHẤT CỦA HÀM INDEX TRONG EXCEL

trung tâm tin học văn phòng tại thanh hóa

Hàm INDEX là gì?

Hàm INDEX sẽ trả về một ô tham chiếu trong một mảng hoặc một dải nhất định. 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.

trung tâm tin học văn phòng tại thanh hóa Hàm INDEX là gì?Hàm INDEX sẽ trả về một ô tham chiếu trong một mảng hoặc một dải nhất định. Nói cách

trung tâm tin học văn phòng tại thanh hóa

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.

Dạng mảng 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 hàm

INDEX (array, row_num, [column_num])

  • array– là một dãi các ô, có tên dải 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ị. Nếu row_num bị bỏ qua, column_num là bắt buộc.
  • column_num– là cột thứ mấy để trả về một giá trị. Nếu column_num bị bỏ qua, row_num là 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, là giá trị trong ô C4.

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

trung tâm tin học văn phòng tại thanh hóa Hàm INDEX là gì?Hàm INDEX sẽ trả về một ô tham chiếu trong một mảng hoặc một dải nhất định. Nói cách

lop tin hoc tai thanh hoa

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: “= INDEX ($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 đối số mảng 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.

Dạng mảng và INDEX – điều cần nhớ

  1. Nếu đối số mảng 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_numhoặc column_num tương ứng.
  2. Nếu đối số mảng bao gồm nhiều hơn một dòng và row_numbị 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ự,
  3. 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.

4. Các đối số row_num và column_num phải tham chiếu đến một ô trong mảng; Nếu không, công thức INDEX sẽ trả về #REF!lỗi.

Định dạng tham chiếu INDEX

Đị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 hàm

INDEX (reference, row_num, [column_num], [area_num])

  • reference– 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 bằng dấu phẩy và kèm theo đối số tham chiếu trong ngoặc đơ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 cột, đố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 –số thứ tự cột để trả về một ô tham chiếu, cũng hoạt động tương tự như dạng mảng.
  • area_num –một tham số tùy chọn chỉ ra mảng từ đối 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 giao điểm của hàng thứ 3 và cột thứ 4 trong khu vực thứ hai (A5: D7).

trung tâm tin học văn phòng tại thanh hóa Hàm INDEX là gì?Hàm INDEX sẽ trả về một ô tham chiếu trong một mảng hoặc một dải nhất định. Nói cách

trung tam tịn hoc o thanh hoa

Đị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 _num arguments (row_num, column_num và area_num) phải tham khảo một ô trong tham chiếu; Nếu không, công thức INDEX sẽ trả về #REF! 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 sự của bạn có thể phức

tạp hơn nhiều so với đó, vì vậy, hãy cùng khám phá một vài 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 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 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.

hoc tin hoc van phong o thanh hoa

Việc sử dụng các bảng hoặc các mảng được đặt tên có thể làm cho công thức dài hơn một 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 thích hợp.

  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 nhất định trong danh sách, bạn chỉ cần viết = INDEX (range,

n), trong đó range là một dải ô hoặc dải ô được đặt tên, và 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ể dùng trỏ chuột chọn cột và Excel sẽ đưa tên của cột cùng với tên của bảng vô công thức:

trung tâm tin học văn phòng tại thanh hóa

Để nhận giá trị của ô ở giao điểm của một hàng và cột đã cho, 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à số cột.

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 (Diameter) và sử dụng công thức INDEX như sau:

= INDEX (SourceData, 2, 3)

  • Array là tên bảng, hoặc một tham chiếu dải, tương ứng với SourceData trong ví dụ này.
  • Row_numlà 2 vì bạn đang tìm kiếm mục thứ hai trong danh sách
  • Column_num là 3 vì Diameter ở 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 column_num thành 1. Và tự nhiên, bạn có thể sử dụng một tham chiếu

ô trong đối số row_num và/hoặc column_num để làm cho công thức INDEX của bạn linh hoạt hơn, như được thể hiện trong hình bên dưới:

hoc tin hoc o thanh hoa  Hàm INDEX là gì?Hàm INDEX sẽ trả về một ô tham chiếu trong một mảng hoặc một dải nhất định. Nói cách khác, bạn sử dụng 

  1. Nhận được 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 column_num.

Các 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 các 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:

= AVERAGE (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. Thông số row_num bị bỏ qua.

trung tam tin hoc van phong o thanh hoa

hoc tin hoc o thanh hoa

hoc tin hoc o thanh hoa  Hàm INDEX là gì?Hàm INDEX sẽ trả về một ô tham chiếu trong một mảng hoặc một dải nhất định. Nói cách khác, bạn sử dụng 

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 tổng khối lượng hành tinh (Mass 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í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 INDEXvới các hàm Excel khác (SUM, AVERAGE, MAX, MIN)

Từ các 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ị.

Và ví dụ này thể hiện bản chất thực sự 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 ư? Công

thức sau đây sẽ làm rõ mọi thứ:

Giả sử bạn có một công thức = AVERAGE (A1: A10) trả về giá trị trung bình của các ô 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:

= AVERAGE(A1: INDEX (A1: A20,10))

Cả hai công thức trên sẽ mang lại kết quả tương tự bởi vì 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 cách thức công thức INDEX vận hành tỏ 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 cột Diameter từ lớn

nhất đến nhỏ nhất và sử dụng công thức Average / Index sau:

= AVERAGE(C5: INDEX (SouceData [Diameter], 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ả lại 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 các giá trị trong cột Diameter (đường kính) giữa hai dữ liệu được chỉ định trong ô B1 và ​​B2:

= SUM (INDEX (SourceData [Diameter], B1): INDEX (SourceData [Diameter], B2)) 

 

hoc tin hoc o thanh hoa

  1. Để học thêm những kiến thức tuyệt vời của tin học văn phòng, mời bạn tham gia khóa học của chunsgg tôi nhé!

TRUNG TÂM ĐÀO TẠO KẾ TOÁN THỰC TẾ – TIN HỌC VĂN PHÒNG ATC

DỊCH VỤ KẾ TOÁN THUẾ ATC – THÀNH LẬP DOANH NGHIỆP ATC

Địa chỉ:

Số 01A45 Đại lộ Lê Lợi – Đông Hương – TP Thanh Hóa

( Mặt đường Đại lộ Lê Lợi, cách bưu điện tỉnh 1km về hướng Đông, hướng đi Big C)

Tel: 0948 815 368 – 0961 815 368

lop tin hoc o thanh hoa

học tin học ở thanh hóa

Lớp dạy tin học tại Thanh Hóa

Lớp dạy tin học ở Thanh Hóa

Lop day tin hoc tai Thanh Hoa

Lop day tin hoc o Thanh Hoa

Lớp dạy tin học thực hành tại Thanh Hóa

Lớp dạy tin học thực hành ở Thanh Hóa

Lop day tin hoc thuc hanh tai Thanh Hoa

Lop day tin hoc thuc hanh o Thanh Hoa

Lớp dạy tin học thực hành tốt nhất tại Thanh Hóa

Lớp dạy tin học thực hành tốt nhất ở Thanh Hóa

Lop day tin hoc thuc hanh tot nhat tai Thanh Hoa

Lop day tin hoc thuc hanh tot nhat o Thanh Hoa

Trung tâm tin học văn phòng uy tín tại Thanh Hóa

Trung tâm tin học văn phòng uy tín ở Thanh Hóa

Trung tam tin hoc van phong uy tin tai Thanh Hoa

Trung tam tin hoc van phong uy tin o Thanh Hoa

Trung tâm tin học văn phòng thực tế tại Thanh Hóa

Trung tâm tin học văn phòng thực tế ở Thanh Hóa

Trung tam tin hoc van phong thuc te tai Thanh Hoa

Trung tam tin hoc van phong thuc te o Thanh Hoa

Lớp tin học văn phòng thực hành chất lượng tại Thanh Hóa

Lớp tin học văn phòng thực hành chất lượng ở Thanh Hóa

Lop tin hoc van phong thuc hanh chat luong o Thanh Hoa

Lop tin hoc van phong thuc hanh chat luong tai Thanh Hoa

 

 


Trả lời

Email của bạn sẽ không được hiển thị công khai. Các trường bắt buộc được đánh dấu *

0961.815.368 0948.815.368 Chat facebook Z Chat zalo