Những tính năng hiệu quả nhất của hàm INDEX trong Excel
Những tính năng hiệu quả nhất của hàm INDEX trong Excel
Trong tất cả các chức năng của Excel mà sức mạnh của chúng thường bị đánh
giá thấp và không được sử dụng đúng cách, thì hàm Index trong excel 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 được nhiều vấn đề đặt ra.
1. Nguồn dữ liệu (SourceData) của hàm Index trong excel
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.
Nguồn dữ liệu (SourceData) của hàm Index trong excel
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.
2. 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 trong Excel 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à 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ể 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:
Để 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_num là 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:
3. 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 trong Excel 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.
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.
4. Sử dụng hàm Index trong excel vớ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 trong Excel.
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))
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.
có thể bạn quan tâm
Những tính năng tốt nhất trên Excel 2013
5
774
229
Tài liệu CNTT khác
5
(New)
Tổng hợp tính năng tốt nhất trong Excel 2013
7
778
309
Tài liệu CNTT khác
7
(New)
Một số tính năng hữu ích nhưng ít được biết đến trong Word, PowerPoint...
12
994
277
Tài liệu CNTT khác
12
(New)
Những tính năng tiện ích nhất trong Ubuntu Unity Quicklists
15
671
323
Lập trình
15
(New)
Hướng dẫn sử dụng tính năng Find and Replace trong Excel
8
1.453
297
Tài liệu CNTT khác
8
(New)
Những tính năng tốt nhất của Windows Server 2016 mà bạn nên biết
6
837
315
Cơ sở dữ liệu
6
(New)
Làm thế nào để Google Index website của bạn nhanh nhất có thể
5
612
257
Tài liệu CNTT khác
5
(New)
Hướng dẫn sử dụng chức năng lọc nâng cao trong excel 2007
3
991
290
Tài liệu CNTT khác
3
(New)
thông tin tài liệu
Trong tất cả các chức năng của Excel mà sức mạnh của chúng thường bị đánh giá thấp và không được sử dụng đúng cách, thì hàm Index trong excel 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 được nhiều vấn đề đặt ra.
Mở rộng để xem thêm
tài liệu mới trong mục này
tài liệu hot trong mục này
tài liệu giúp tôi
Nếu bạn không tìm thấy tài liệu mình cần có thể gửi yêu cầu ở đây để chúng tôi tìm giúp bạn!
xem nhiều trong tuần
Giáo trình Quản trị học của Đại học kinh tế quốc dân
Mẫu slide chủ đề âm nhạc
Tâm lý xã hội VÀ Hệ tư tưởng
Tham khảo 12 bài tập nguyên lý thống kê có lời giải
4 đề thi học sinh giỏi Toán lớp 2, có đáp án kèm theo
Các hiệu ứng HTML cho trang web
yêu cầu tài liệu
Giúp bạn tìm tài liệu chưa có
×
