Ham Index Trong Excel La Gi / Top 10 Xem Nhiều Nhất & Mới Nhất 10/2023 # Top Trend

Bao gồm các hàm xử lý chuỗi văn bản như trích lọc, tìm kiếm, thay thế, chuyển đổi chuỗi văn bản trong Excel.

HàmCông dụngHàmCông dụng

Công dụng

Chuyển đổi một mã số trong bộ mã ANSI có miền giá trị từ 1 – 255 sang ký tự tương ứng

Công thức

= CHAR( number) number là một mã số trong bảng mã ANSI.

Ví dụ

Tham khảo Hàm T

==============

Công dụng

Trả về mã số của ký tự đầu tiên trong chuỗi ký tự

Công thức

text là chuỗi ký tự.

Để dễ hiểu hơn, bạn áp dụng các công thức sau vào bất kỳ ô nào trong bảng tính.

Công thức Giải thích

= CODE(“ABC”)

Trả về 65, Mã số của ký tự A.

= CODE(“VIETNAM”)

Trả về 86, Mã số của ký tự V.

Công dụng

Chuyển đổi số thành dạng tiền tệ, với số thập phân được chỉ định để làm tròn số đó.

Công thức

= DOLLAR( number,decimals) number là số cần chuyển sang định dạng tiền tệ. decimalslà số số thập phân. Nếu decimals < 0 thì hàm sẽ làm tròn về bên trái số. Mặc định là 2.

Lưu ý!

Điểm khác biệt chính giữa một ô tiền tệ được định dạng bằng lệnh Format – Cells – Numbers từ menu và định dạng một số với hàm DOLLAR là hàm DOLLAR chuyển đổi kết quả của nó sang dạng văn bản ( text) trong khi định dạng với lệnh Cells vẫn là số. Bạn có thể tiếp tục dùng số được định dạng với hàm DOLLAR trong công thức, bởi vì Microsoft Excel đổi số được nhập ở dạng giá trị text sang dạng số khi nó được tính.

Ví dụ

Để thử công thức, bạn có thể copy các giá trị bên trong bảng vào bảng tính và nhập công thức vào.

Công thức Giải thích

= DOLLAR(A1)

Trả về $1050.50 – làm tròn với 2 số thập phân

= DOLLAR(A2,0)

Trả về $1050 – làm tròn thành số nguyên.

= DOLLAR(A3,-2)

Trả về $1100 – làm tròn về bên trái 2 số.

Tìm chuỗi find_text bên trong chuỗi within_text, và trả về vị trí bắt đầu của within_text trong find_text.

= FIND( find_text,within_text,start_num) find_text là chuỗi cần tìm.within_text là chuỗi chứa chuỗi cần tìm. start_num: vị trí bắt đầu trong chuỗi within_text để tìm kiếm. Nếu để trống, start_num là 1.

Lưu ý!

Nếu không tìm thấy text_find trong within_text hàm trả về lỗi #VALUE!Nếu start_num (vị trí bắt đầu tìm kiếm) nhỏ hơn 0 hàm trả về lỗi #VALUE! Nếu start_num lớn hơn chiều dài chuỗi cần tìm find_text hàm trả về lỗi #VALUE!

Ví dụ

Công thức

Giải thích

= FIND(“N”,A1)

Trả về 6 – vị trí xuất hiện N trong Việt Nam

= FIND(A1,A2)

Trả về lỗi #VALUE! do không tìm thấy Việt Nam trong Hà Nội

Trích bên trái một chuỗi một hoặc nhiều ký tự dựa vào số ký tự mà bạn chỉ định.

Công thức

= LEFT( text,num_chars) text là chuỗi cần trích ký tựnum_chars là ký tự mà bạn cần trích bên trái chuỗi text..

Lưu ý!

num_chars không phải là số âm

num_charsnếu lớn hơn độ dài của chuỗi thì sẽ trả về toàn bộ chuỗi text.num_chars nếu bỏ qua thì mặc định là 1.

Ví dụ

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức vào.

A

B

1

SBD Họ và tên

QSA0001

Nguyễn Hoài An

QSA0002

Nguyễn Tấn Anh

2

3

Công thức Giải thích

=LEFT(A2,3)

Trả về QSA

QSA là mã trường dự thi của thí sinh

Công dụng

Đổi tất cả các ký tự trong chuỗi sang chữ thường.

Công thức

= LOWER( text) text là chuỗi, hoặc tham chiếu đến chuỗi cần chuyển định dạng.

Ví dụ

Để thử công thức, bạn có thể copy dữ liệu bên trong bảng bên dưới vào bảng tính và nhập công thức vào.

A

B

1

SBD Họ và tên

QSA0001

Nguyễn Hoài An

QSA0002

Nguyễn Tấn Anh

2

3

Công thức Giải thích

= LOWER(A2)

Trả về qsa0001

= LOWER(B2)

Trả về nguyễn hoài an

Tham khảo Hàm UPPER, Hàm PROPER

Công dụng

Trích một chuỗi con từ một chuỗi text, bắt đầu từ vị trí start_num với số ký tự được chỉ định num_chars

Công thức

= MID( text,start_num,num_chars) text là chuỗi hoặc tham chiếu đến chuỗi.start_num: vị trí bắt đầu trích lọc chuỗi con trong textnum_chars: số ký tự của chuỗi mới cần trích từ chuỗi text

Lưu ý!

start_num: lớn hơn chiều dài chuỗi textthì hàm trả về chuỗi rỗng “” start_num: nhỏ hơn 1 hàm trả về lỗi #VALUE!num_chars: âm MID trả về lỗi #VALUE!

Ví dụ

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức vào.

1

Mã hàng Tên Hàng Đơn vị tính

MC010A

Bàn mica loại A

cái

PL012B

Bàn plastic loại B

cái

2

3

Công thức Giải thích

=MID(A2,3,3)

Trả về 010

================

Công dụng

Trích bên phải một chuỗi văn bản một hoặc nhiều ký tự dựa vào số ký tự mà bạn chỉ định.

Công thức

= RIGHT( text, num_chars) text là chuỗi cần trích ký tựnum_chars là ký tự mà bạn cần trích bên phải chuỗi text.

Lưu ý!

num_chars không phải là số âm num_charsnếu lớn hơn độ dài của chuỗi thì sẽ trả về toàn bộ chuỗi text.num_chars nếu bỏ qua thì mặc định là 1.

Ví dụ

Để dễ hiễu hơn, bạn hãy copy các giá trị bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức vào.

Thay thế một chuỗi cụ thể bên trong chuỗi bằng chuỗi khác. Dùng SUBSTITUTE khi muốn thay thế một chuỗi cụ thể.

= SUBSTITUTE( text,old_text,new_text,instance_num) text: chuỗi văn bản cần thay thế nội dung.old_text: nội dung bên trong chuỗi text cần thay thế.. new_text: chuỗi văn bản mới để thay chuỗi cũ

instance_num: chỉ định thay thế ở lần mà tìm thấy chuỗi old_texttrong chuỗi text. Nếu bỏ qua thì sẽ thay thế tất cả các old_text được tìm thấy trong chuỗi text.

Ví dụ

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức vào.

1

Mã hàng Số lượng

CD001

10

DVD002

20

2

3

Chuyển một giá trị số sang văn bản với kiểu định dạng số được chỉ định.

Công thức

= TEXT( value,format_text)

value giá trị số, hoặc tham chiếu đến giá trị số cần chuyển đổi.

format_text kiểu định dạng bạn muốn chuyển đổi. Có thể tham khảo các kiểu định dạng trong Format – Cells, thẻ Number, trong danh sách Category.

format_text không đựơc có dấu *kết quả của TEXT không được tính toán ở kiểu số nữa.

Ví dụ

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức vào.

A

B

C

1

Doanh thu tháng 12 Tên hàng Số tiền Ghi chú

2

3

4

5

6

Công dụng

Xóa tất cả các ký tự trắng của chuỗi trừ những khoảng trắng đơn dùng để làm khoảng cách bên trong chuỗi.

Công thức

= TRIM( text)text chuỗi cần xóa các ký tự trắng.

Ví dụ

= TRIM(” Microsoft Excel”). Trả về Microsoft Excel sau khi loại bỏ các khoảng trắng đầu chuỗi này.

Đổi chuỗi text đại diện cho một số thành dữ liệu kiểu số

text là chuỗi văn bản đại diện cho một số.

Lưu ý!

text là định dạng số, ngày tháng, hoặc thời gian bất kỳ được Microsoft Excel công nhận. Nếu không phải định dạng trên sẽ trả về lỗi #VALUE!

Ví dụ

Để thử công thức, bạn có thể copy các giá trị bên trong bảng vào bảng tính và nhập công thức vào.

A

B

1

SBDHọ và tên

QSA0100

Nguyễn Tấn Minh

QSA0101

Nguyễn Văn Minh

2

3

= VALUE(RIGHT(A2,4) ). Trả về 100. Kết quả của hàm RIGHT chỉ trả về một chuỗi muốn chuyển nó thành số phải dùng hàm VALUE.

Công dụng

Xóa những ký tự không hiển thị và in được trong Worksheet được đưa từ các ứng dụng khác.

Công thức

= CLEAN( text) text là vùng dữ liệu cần xóa những ký tự không cần thiết.

Công dụng

Dùng để kết nối các chuỗi văn bản thành một chuỗi

Công thức

= CONCATENATE( text1,text2,…)text1, text2 là các chuỗi văn bản con cần kết nối thành một chuỗi duy nhất.

Lưu ý!

Có thể dùng toán tử & để kết nối các chuỗi thay cho hàm CONCATENATE.

Ví dụ

Để thử công thức, bạn có thể copy các giá trị bên trong bảng vào bảng tính và nhập công thức vào.

Công dụng

So sánh hai chuỗi văn bản. Trả về TRUE nếu cả hai chuỗi giống nhau hoàn toàn, FALSE nếu ngược lại. EXACT phân biệt chữ thường và chữ hoa.

Công thức

= EXACT( text1,text2) text1 là chuỗi văn bản thứ nhất.text2 là chuỗi văn bản thứ hai.

Ví dụ

Để thử công thức, bạn có thể copy các giá trị bên trong bảng vào bảng tính và nhập công thức vào.

Công thức Giải thích

= EXACT( A1 , A2)

Trả về TRUE

= EXACT( A2 , B2)

Trả về FALSE

Công dụng

Chuyển đổi một số sang dạng văn bản (text) đồng thời làm tròn nó với số số thập phân được chỉ định.

Công thức

= FIXED( number, decimals,no_commas) number là dữ liệu kiểu cần chuyển đổi

decimals là số số thập phân chỉ định để làm tròn số. Nếu decimals âm thì sẽ làm tròn về bên trái number.

no_commas là cờ hiệu có giá trị (TRUE hoặc FALSE). Nếu TRUE thì kết quả trả về không có dấu phân cách hàng nghìn.

Lưu ý!

Số trong Excel không lớn hơn 15 ký số, nhưng phần thập phân có thể tới 127 ký số.

decimals nếu bỏ qua thì có giá trị mặc định là 2.co_commas nếu bỏ qua thì có giá trị mặc định là FALSE.

Ví dụ

Công thức Giải thích

=FIXED(A1,0,TRUE)

Trả về 1025

=FIXED(A2,,)

Trả về 5,005.56

=FIXED(A3,-2)

Trả về 707,800

Công dụng

Tính độ dài (số ký tự) của mỗi chuỗi.

Công thức

= LEN( text) text là nội dung mà bạn cần xác định độ dài.

Ví dụ

Để dễ hiểu, bạn có thể copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mớ và nhập công thức vào.

A

B

1

SBD Họ và tên

QSA0001

Nguyễn Hoài An

QSA0002

Nguyễn Tấn Anh

2

3

Công thức Giải thích

= LEN(A2)

Trả về 7

= LEN(C3)

Trả về 0

Công dụng

Chuyển ký tự đầu tiên của mỗi từ thành chữ hoa, và các ký tự còn lại thành chữ thường.

Công thức

= PROPER( text) text là chuỗi văn bản cần chuyển định dạng

Ví dụ

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào trang bảng tính mới và nhập công thức vào.

A

B

1

nguyễn an nhiên

TRẦN NHẬT NAM

2

3

Công thức Giải thích

= PROPER(A2)

Trả về Nguyễn An Nhiên

= PROPER(A3)

Trả về Trần Nhật Nam

Tham khảo Hàm LOWER, Hàm UPPER

Công dụng

Thay thế một phần của chuỗi text bằng một chuỗi khác dựa vào số ký tự bạn chỉ định.

Công thức

= REPLACE( old_text,start_num,num_chars,new_text)

old_text: chuỗi cũ cần thay thế

start_num: vị trí bắt đầu thay thế

num_chars: số ký tự của chuỗi cũ bắt đầu từ vị trí start_num sẽ bi6 thay bằng chuỗi mới.

new_text: chuỗi mới dùng để thay thế một phần chuỗi cũ.

Ví dụ

Để dễ hiễu hơn,bạn có thể copy dữ liệu bên trong bảng dưới vào một trang bảng tính mới và nhập công thức vào.

A

B

1

Địa chỉ web tìm kiếm

www.google.com.vn

www.vinaseek.com

2

3

Công dụng

Lặp lại một chuỗi với số lần do bạn đưa ra.

Công thức

= REPT( text,number_times) text: chuỗi cần lặp lạinumber_times: số lần lặp lại

Lưu ý!

number_times: bằng 0 REPT trả về chuỗi rỗng “” text: không phải là số nguyên sẽ được làm tròn Kết quả REPT không được quá 32.767 ký tự, nếu lớn hơn trả về lỗi #VALUE!

Ví dụ

Để dễ hiểu hơn, bạn hãy copy dữ liệu bên trong bảng dưới vào bảng tính mới và nhập công thức vào.

Công thức Giải thích

=REPT(A1,5)

Trả về $$$$$

=REPT(A2,4)

Trả về 9999

=REPT(A3,50000)

Trả về #VALUE!

Trả về vị trí đầu tiên của ký tự cần tìm bên trong chuỗi.

Công thức

= SEARCH( find_text,within_text,star_num) find_text: chuỗi cần tìm. Có thể dùng ký tự ? để đại diện một ký tự đơn, dùng * để đại diện một nhóm ký tự. Nếu muốn tìm dấu ? hoặc dấu * thì gõ dấu ~ trước ký tự đó.within_text: chuỗi chứa chuỗi mà bạn muốn tìm.star_num: vị trí bắt đầu tìm kiếm.

Lưu ý!

SEARCH không phân biệt chữ thường, chữ hoa

SEARCH tương tự như FIND nhưng FIND phân biệt chữ thường và chữ hoa khi tìm kiếm

SERACH tìm không có kết quả sẽ trả về lỗi #VALUE!

Ví dụ

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức vào.

1

SBD Họ và tên

QSA0010

Nguyễn Minh

QSA0210

Nguyễn Văn Chương

2

3

Trả về chuỗi nếu giá trị được tham chiếu là một chuỗi.

Công thức

value tham chiếu tới giátrị cần kiểm tra.

Lưu ý!

value tham chiếu đến chuỗi text thì T trả về chuỗi, ngược lại trả về chuỗi rỗng “”.

Không dùng hàm T trong công thức của bạn vì Microsoft Excel có khả năng tự chuyển đổi các kiểu dữ liệu phù hợp khi cần thiết. Hàm T được Excel hỗ trợ để tương thích với các ứng dụng bảng tính khác.

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào một trang bảng tính mới và nhập công thức vào.

1

Quốc gia HCV HCB HCĐ

Việt Nam

120

50

60

Thái Lan

98

60

56

2

3

Công dụng

Chuyển tất cả các ký tự trong chuỗi thành ký tự hoa.

= UPPER( text) text là chuỗi văn bản cần chuyển định dạng

Ví dụ

Để dễ hiểu hơn, bạn có thể copy dữ liệu bên trong bảng bên dưới vào trang bảng tính mới và nhập công thức vào.

= UPPER(“Việt Nam”) Trả về “VIỆT NAM”

Tham khảo Hàm LOWER, Hàm PROPER

#NUM! : Lỗi này do kết quả công thức quá lớn hoặc quá nhỏ mà Excel không thể tính được.

#N/A! : Lỗi này xảy ra khi công thức được sao chép đến ô mới mà tại ô đó không có giá trị nào.CÁC KHÁI NIỆM CƠ BẢN TRONG EXCELVII. Các thông báo lỗi thông thường :#NAME? : Lỗi này thông báo khi bạn nhập sai tên hàm hoặc tên biến.

#REF! : Lỗi này thông báo công thức đang sử dụng một ô không có thực.

#VALUE! : Lỗi này thông báo khi thức xuất hiện các phép toán không cùng kiểu dữ liệu.

##### : Lỗi này thông báo khi dữ liệu bị tràn do cột không đủ độ rộng để hiển thị dữ liệu. CÁC KHÁI NIỆM CƠ BẢN TRONG EXCELMột số hàm tính toán thông dụngI. Nhóm hàm Logic :AND(Logical1 , Logical2 , . . .) – Trả về kết quả True hoặc False của các biểu thức logic. – Nếu tồn tại ít nhất một biểu thức (Logical) có giá trị là False thì hàm AND cho kết quả False, nếu không thì cho kết quả True.

– Ví dụ : OR(3<2 , 5< 8) = True. Một số hàm tính toán thông dụngI. Nhóm hàm Logic :NOT (Logical) – Trả về kết quả là True, khi biểu thức có giá trị là False.Và ngược lại, trả về kết quả là False khi biểu thức có giá trị True.

– Ví dụ : ROUND (35123.374 , 2) = 35123.37 ROUND (35123.374 , 1) = 35123.4 ROUND (35123.374 , -3) = 35000 II. Nhóm hàm số học :Một số hàm tính toán thông dụngSQRT(Number) – Trả về căn bậc hai của số Number. – Ví dụ : SQRT (16) = 4. II. Nhóm hàm số học :Một số hàm tính toán thông dụngMAX(Number1 , Number2 , . . .) – Trả về giá trị lớn nhất của các số liệt kê Number1 , Number2 , . . . – Ví dụ : Dữ liệu trong các ô B1, B2, B3 là 4, 8, 3.Công thức trong ô B4 là MAX(B1 : B3) = 8.MIN(Number1 , Number2 , . . .) – Trả về giá trị nhỏ nhất của các số liệt kê Number1 , Number2 , . . . – Ví dụ : Dữ liệu trong các ô B1, B2, B3 là 4, 8, 3.Công thức trong ô B4 là MIN(B1 : B3) = 3. III. Nhóm hàm thống kê :Một số hàm tính toán thông dụngIII. Nhóm hàm thống kê :AVERAGE(Number1 , Number2, . . . ) – Trả về kết quả là trung bình cộng của các số đã liệt kê Number1 , Number2 , . . .

– Ví dụ : Dữ liệu trong các ô : B1, B2, B3 là 4, 8, 3 Công thức ô B4 là AVERAGE(B1 : B3) = 5 Một số hàm tính toán thông dụngCOUNT(Value1 , Value2 , . . .) – Trả về kết quả là số các phần tử kiểu số.

Ví dụ : COUNT(2 , “ab” , 5 , 4) = 3.Dữ liệu trong các ô B1 , B2 , B3 , B4 là SN , 4 , 8 , 3.Công thức trong ô B5 là COUNT(B1 : B4) = 3.III. Nhóm hàm thống kê :Một số hàm tính toán thông dụngCOUNTA(Value1 , Value2 , . . .) – Trả về kết quả là số các phần tử khác trống (ô trống là ô không có dữ liệu) SUM(Number1 , Number2, . . .) – Trả về giá trị là tổng của các số liệt kê Number1 , Number2 , . . . – Ví dụ : Dữ liệu trong các ô B1, B2, B3 là 4, 8, 3.Công thức trong ô B4 là SUM(B1 : B3) = 15.III. Nhóm hàm thống kê :Một số hàm tính toán thông dụngRANK(Number,Ref,[Order])– Dùng để xếp hạng của một địa chỉ dữ liệu so với một vùng dữ liệu.

– Number : Số hoặc ô cần xếp hạng.– Ref : Vùng dữ liệu so sánh.– [order] : Điều kiện xếp hạng (0 : Xếp hạng theo thứ tự giảm dần; 1 Xếp hạng theo thứ tự tăng dần). III. Nhóm hàm thống kê :Một số hàm tính toán thông dụngVí dụ hàm RANKKhi thứ thự xếp bằng 1Khi thứ thự xếp bằng 0IV. Nhóm hàm về chuỗi :LEFT(Text , Num_char) – Trả về chuỗi con của chuỗi Text được trích từ trái sang phải Num_char ký tự

Ví dụ : LEFT(“Pham Van Tuan” , 8) = ” Pham Van”.Một số hàm tính toán thông dụngRIGHT(Text , Num_char) – Trả về chuỗi con của chuỗi Text được trích từ phải sang trái Num_char ký tự

Ví dụ : RIGHT(“Pham Van Tuan” , 4) = ” Tuan”IV. Nhóm hàm về chuỗi :Một số hàm tính toán thông dụngMID (Text , Start_num , Num_char) – Trả về chuỗi con của chuỗi Text được trích từ vị trí Start_num và dài Num_char ký tự

Ví dụ : MID(“Pham Van Tuan” , 6 , 3) = “Van”IV. Nhóm hàm về chuỗi :Một số hàm tính toán thông dụngLEN(Text): Trả về kết quả là chiều dài của chuỗi Text.

Vi dụ : LEN(“Trung Tam Tin Hoc”) = 17.UPPER(Text): Trả về chuỗi Text đã được đổi sang dạng chữ in.

Ví dụ : UPPER(“hoa hong”) = “HOA HONG”IV. Nhóm hàm về chuỗi :Một số hàm tính toán thông dụngLOWER(Text): Trả về chuỗi Text đã được đổi sang dạng chữ thường.

Ví dụ : LOWER(“HOA HONG”) = “hoa hong”PROPER(Text): Trả về chuỗi Text trong đó ký tự đầu tiên mỗi từ đã được đổi sang dạng chữ in. Ví dụ : PROPER(“le nguyen anh thu”) = “Le Nguyen Anh Thu”. IV. Nhóm hàm về chuỗi :Một số hàm tính toán thông dụngTRIM(Text): Trả về chuỗi Text trong đó các ký tự trắng ở đầu và cuối chuỗi được cắt bỏ.

Ví dụ : TRIM(” hoa hong “) = “hoa hong”.CONCATENATE(Text1,Tex2,Text3,…) : Trả về kết quả là một chuỗi kết nối các Text1, Text2, …

Ví dụ : CONCATENATE(“dem”,”dong”) = “demdong”.IV. Nhóm hàm về chuỗi :Một số hàm tính toán thông dụngDATE(Year, Month, Date) – Trả về kết quả ứng với ngày tháng năm.

Ví dụ : DATE(93, 08, 25) = 25/08/93 (nếu ô chứa hàm định dạng hiển thị số lớp Date và dạng DD/MM/YY).V. Nhóm hàm ngày, giờ :Một số hàm tính toán thông dụngNOW(): Trả về kết quả là ngày, giờ hiện hành.

Ví dụ : NOW() = 09/12/2009 10:25 PMV. Nhóm hàm ngày, giờ :TODAY(): Trả về kết quả là ngày hiện hành.

Ví dụ : TODAY() = 09/12/2009.Một số hàm tính toán thông dụngDAY(serial_number): Trả về kết quả là một ngày, ứng với giá trị serial_number.

Ví dụ : DAY(32156) = 14.V. Nhóm hàm ngày, giờ :MONTH(serial_number) : Trả về kết quả là một tháng, ứng với giá trị serial_number.

Ví dụ : MONTH(32156) = 01. Một số hàm tính toán thông dụngYEAR(serial_number) : Trả về kết quả là một năm, ứng với giá trị serial_number.

Ví dụ : YEAR(32156) = 1988.V. Nhóm hàm ngày, giờ :Một số hàm tính toán thông dụngTEXT (Value , Format_Text) : Đổi số thành chuỗi (không còn giá trị tính toán).

Ví dụ : TEXT (1.256, “###.###”) = “1.256” TEXT (1.256, “###.##”) = “1.26”VI. Nhóm hàm đổi dữ liệu :VALUE (Text) : Hàm này đổi một chuỗi dạng số thành số.

Ví dụ : VALUE (“265”) = 265 Một số hàm tính toán thông dụngIF (Logical_test,[Value_if true], [Value _if false]) – Hàm này trả về : – Giá về giá trị Value_if true nếu Logical_test là trueGiá về giá trị Value_if false nếu Logical_test là false

 Hàm này trả về giá trị Lookup_val trong bảng Table (tại cột đầu tiên của Table), tìm được tại dòng nào sẽ trả về giá trị ở dòng đó tại cột thứ Col_index (cột đầu tiên đếm là thứ 1). VIII. Nhóm hàm tìm kiếm :Một số hàm tính toán thông dụngVí dụ hàm VLOOKUPHLOOKUP (Lookup_value, Table_array, Row_index_num, [Range_lookup])

Hàm này trả về giá trị Lookup_val trong bảng Table (tại dòng đầu tiên của Table), tìm được tại cột nào sẽ trả về giá trị ở cột đó tại dòng thứ row (dòng đầu tiên đếm là thứ 1). VIII. Nhóm hàm tìm kiếm :Một số hàm tính toán thông dụngVí dụ hàm HLOOKUP

Cách dùng Hàm INDEX trong Excel – Công thức và ví dụ minh họa

Hàm INDEX trong Excel trả về một giá trị trong bảng dựa trên dựa trên số hàng và số cột được cung cấp trong bảng dữ liệu.

Bạn có thể hình dung như nó giống như việc sử dụng tọa độ để xác định vị trí trên bản đồ. Dựa vào số hàng và số cột được cung cấp, hàm INDEX sẽ trả về giá trị khớp với vị trí trong bảng.

Trước khi bắt đầu bài hướng dẫn, tôi cần phải cho bạn biết rằng hàm INDEX riêng lẻ không thực sự hữu ích hay thú vị gì cả. Nó chỉ trở nên hữu dụng khi bạn sử dụng kết hợp nó với các hàm chức năng khác ví dụ như hàm MATCH. Lấy ví dụ, bằng cách kết hợp 2 hàm INDEX và MATCH, bạn sẽ có thể khắc phục những điểm yếu của hàm VLOOKUP.

Để bạn có thể dễ dàng tìm hiểu về chức năng của hàm INDEX, chúng tôi sẽ để cập đến chức năng của hàm MATCH trong hướng dẫn sau.

Ghi chú: Các hướng dẫn trong bài viết này có thể áp dụng cho Microsoft Excel 2023, Excel 2023, Excel 2013, Excel 2010, and Excel của Microsoft 365.

Nội dung chính:

Có mấy dạng hàm INDEX trong Excel?

Hàm INDEX dạng mảng – Công thức và đối số

Ví dụ minh họa của hàm INDEX dạng mảng trong Excel

Cách khởi động hộp thoại hàm INDEX dạng mảng

Cách nhập các đối số của hàm INDEX dạng mảng

Hàm INDEX dạng tham chiếu – Công thức và đối số

Ví dụ của hàm INDEX dạng tham chiếu trong Excel

Cách mở hộp thoại INDEX dạng tham chiếu

Cách nhập dữ liệu của hàm INDEX dạng tham chiếu

Cách khởi động hộp thoại hàm INDEX dạng mảng

Cách nhập các đối số của hàm INDEX dạng mảng

Cách mở hộp thoại INDEX dạng tham chiếu

Cách nhập dữ liệu của hàm INDEX dạng tham chiếu

Có mấy dạng hàm INDEX trong Excel?

Có hai dạng biểu mẫu có sẵn của hàm INDEX trong Excel.

Sự khác biệt chính của hai dạng hàm INDEX này là:

Hàm INDEX dạng mảng trả về giá trị nằm ở điểm giao nhau của một hàng và cột dữ liệu cụ thể.

Hàm INDEX dạng tham chiếu trả về tham chiếu ô của điểm giao nhau của một hàng và cột dữ liệu cụ thể.

Cú pháp hàm sẽ đề cập đến cách bố trí của hàm và bao gồm tên hàm, dấu ngoặc, dấu phẩy phân tách và đối số).

Công thức của hàm INDEX dạng mảng là:

Array: Phạm vi ô được tìm kiếm theo hàm với thông tin bạn mong muốn.

Row_num (tùy chọn): Số hàng trong mảng để trả về giá trị. Nếu đối số này bị bỏ qua, thì Column_num là đối số bắt buộc.

Column_num (tùy chọn): Số cột trong mảng để trả về giá trị. Nếu đối số này bị bỏ qua, Row_num là sẽ là đối số bắt buộc.

Đối với cả đối số Row_num và Column_num, bạn có thể nhập số hàng và số cột thực tế hoặc sử dụng tham chiếu ô đến vị trí của thông tin này trên bảng tính.

Ví dụ minh họa của hàm INDEX dạng mảng trong Excel

Vì có hai dạng khác nhau của hàm INDEX – mỗi dạng có một bộ đối số riêng nên dạng biểu thức của nó cũng yêu cầu một hộp thoại riêng.

Bước 1: Bạn tạo một bảng tính Excel mới và nhập các dữ liệu như trong hình bên dưới.

Bước 3: Trên thanh Menu chính, bạn chọn tab Formulas.

Bước 2: Trong bảng tính, bạn hãy tô đen phạm vi các ô từ A2 đến C4 để nhập phạm vi này vào hộp thoại.

Kết quả trả về ” Máy rửa chén” sẽ xuất hiện trong ô B8 vì đây là giá trị của ô giao với dòng thứ ba và cột thứ hai của phạm vi dữ liệu mà bạn đã lựa chọn.

#VALUE!: Xảy ra nếu đối số Row_num hoặc Column_num không phải là số.

#REF!: Xảy ra nếu đối số Row_num lớn hơn số lượng hàng trong phạm vi đã chọn hoặc đối số Col_num lớn hơn số lượng cột trong phạm vi đã chọn.

Hàm INDEX dạng tham chiếu – Công thức và đối số

Dạng tham chiếu của hàm trả về giá trị dữ liệu của ô nằm ở điểm giao nhau giữa một hàng và cột dữ liệu cụ thể. Mảng tham chiếu có thể bao gồm nhiều phạm vi dữ liệu không liền kề với nhau.

Cú pháp và đối số của hàm INDEX dạng tham chiếu là:

Reference (bắt buộc): là tham chiếu ô cho phạm vi ô được tìm kiếm theo hàm cho thông minh bạn mong muốn. Nếu như bạn có nhiều phạm vi dữ liệu không liền kề và muốn nhập thông tin vào đây, các phạm vi phải được bao quanh bởi một bộ dấu ngoặc tròn riêng biệt như được hiển thị trong hàm INDEX bên dưới:

Row_num: là số hàng trong mảng dữ liệu để trả về giá trị.

Column_num: là số cột trong mảng dữ liệu để trả về giá trị.

Area_num (tùy chọn): được sử dụng khi đối số Reference chứa nhiều phạm vi không liền kề với nhau. Đối số này chọn phạm vi ô nào để trả về dữ liệu. Nếu được bỏ qua, hàm INDEX sẽ sử dụng phạm vi đầu tiên được liệt kê trong đối số Reference. Phạm vi được nhập trong đối số Reference sẽ được đánh số là 1, thứ hai là 2, thứ ba là 3…

Ví dụ của hàm INDEX dạng tham chiếu trong Excel

Bởi vì hàm INDEX có hai dạng khác nhau, nên bạn phải sử dụng hộp thoại với các đối số riêng biệt.

Bước 1: Nhập những dữ liệu được hiển thị trong bảng tính Excel này.

Bước 3: Chuyển đến Formulas.

Bước 6: Trong bảng tính, chọn ô B8 để nhập tham chiếu ô đó vào trong hộp thoại.

Bước 8: Trong bảng tính, lựa chọn ô B9 để nhập tham chiếu ô đó vào hộp thoại.

Kết quả Tháng 6 sẽ xuất hiện trong ô B10 vì đó là tháng trong ô giao với hàng đầu tiên và cột thứ hai của phạm vi từ ô A1 đến D5.

Những lỗi thường gặp xuất hiện trên hàm INDEX dạng tham chiếu là:

#VALUE!: Xảy ra nếu các đối số Row_num, Column_num, hoặc Area_num không phải là số.

#REF!: Xảy ra khi đối số Row_num lớn hơn số lượng hàng trong phạm vi đã chọn hoặc đối số Col_num lớn hơn số lượng cột trong phạm vi đã chọn hoặc đối số Area_num lớn hơn số vùng trong phạm vi đã chọn.

Vậy là vừa rồi bạn đã tìm hiểu qua hàm INDEX với hai dạng khác nhau đi kèm với công thức, cách sử dụng và ví dụ minh họa của từng dạng hàm. Trong bài viết sau, chúng ta sẽ cùng tìm hiểu chức năng của hàm MATCH trong Excel để dùng kết hợp với hàm INDEX để mang lại hiệu quả tối ưu nhất.

Hướng dẫn cách sử dụng hàm COUNT để đếm dữ liệu trong Excel

Hướng dẫn cách sử dụng hàm SUMIF để tính tổng có điều kiện trong Excel

Hướng dẫn sử dụng hàm PRODUCT để thực hiện phép nhân trong Excel

Hàm index trong Excel là gì

#1. Tổng quan về hàm INDEX #1.1. Hàm INDEX là gì?

Hàm INDEX là hàm trả về mảng, giúp lấy các giá trị tại một ô nào đó giao giữa cột và dòng. Có thể hiểu, trong 1 mảng cho trước, nếu biết được vị trí (dòng và cột) của một phần tử thì hàm này sẽ giúp lấy giá trị tại ô đó.

#1.2. Hàm INDEX trong Excel là gì?

Hàm INDEX trong Excel là một Function (chức năng) được sử dụng nhằm mục đích 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 (bảng tính Excel) và bạn muốn nhận giá trị thực của phần tử đó.

#1.3. Hàm INDEX nâng cao

Hàm INDEX nâng cao giúp chúng ta có thể tạo một vùng tham chiếu rộng hơn, bao gồm nhiều vùng tham chiếu, công thức tổng quát như sau:

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

#2. Cú pháp và cách sử dụng hàm INDEX trong Excel #2.1. Cú pháp hàm INDEX cơ bản

Công thức một hàm INDEX cơ bản như sau:

=INDEX(array;row_num;[column_num])

Trong đó:

– array: vùng ô hoặc một hàng số mảng nào đó;

– row_num: chọn hàng trong mảng từ đó trả về một giá trị;

– column: chọn cột trong mảng từ đó trả về một giá trị.

Bước 1: Tại ô G8 chúng ta viết công thức:

=INDEX(B8:F18;3;2)

Bước 2: Bấm phím Enter, ta có kết quả như sau: Phần tử ở dòng thứ 3, cột thứ 2 trong mảng là “Phạm Thanh Hồng”

Lưu ý: Lỗi #REF xảy ra khi các bạn chọn địa chỉ của phần tử không nằm trong mảng ban đầu.

#2.1. Cú pháp hàm INDEX nâng cao

Vượt ra phạm vi của 1 mảng, chúng ta có thể tìm phần tử của một vùng tham chiếu (có thể có 1 hoặc nhiều mảng khác nhau) thông qua công thức tổng quát như sau:

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

Trong công thức này:

– reference: vùng tham chiếu (có thể kết hơp nhiều mảng với nhau);

– row_num: chỉ số hàng từ đó trả về một tham chiếu;

– column_num: chỉ số cột từ đó trả về một tham chiếu;

– area_num: số của vùng ô sẽ trả về giá trị trong reference (tức là số thứ tự của mảng trong vùng tham chiếu). Nếu area_num được bỏ qua thì hàm INDEX mặc định dùng vùng 1.

Về cú pháp, không có sự khác nhau nhiều so với hàm cơ bản. Điểm khác nhau của công thức này là vùng giá trị có thể có nhiều hơn một mảng và kết quả trả về phụ thuộc vào mảng mà bạn chọn.

Để dễ hình dung, chúng ta cùng xem ví dụ sau.

Lúc này, chúng ta áp dụng công thức hàm INDEX nâng cao, để tìm kết quả theo điều kiện đề ra như sau:

=INDEX((B8:F12;B13:F18);3;2;2)

Ở trong công thức này, chúng ta có 2 mảng [B8:F12] và [B13:F18] (tức là vùng tham chiếu); phần tử dòng thứ 3, cột thứ 2 và nằm ở mảng thứ 2 do đó ta điền lần lượt là 3,2,2 trong công thức.

Sau đó, bấm phím Enter, kết quả của phép tính là:

Vậy, phần tử dòng thứ 3, cột thứ 2 của mảng thứ 2 là “Bùi Xuân Thắng”

Qua ví dụ trên, các bạn đã phân biệt được cách dùng và sự khác nhau giữa hàm INDEX cơ bản và nâng cao chưa nào.

#2.3. Kết hợp hàm INDEX MATCH

Thực tế, việc sử dụng hàm INDEX đơn thuần thường không tận dụng được hết sức mạnh của hàm này. Người ta thường kết hợp giữa hàm này và hàm MACTCH để thực hiện một chuỗi tính toán phức tạp hơn rất nhiều.

Như chúng ta đã biết, trong cú pháp hàm INDEX có 2 giá trị là vị trí dòng (row_num) và vị trí cột (column_num). Ta có thể thay thế 2 giá trị trên bằng cách sử dụng phép toán của hàm MATCH.

Như vậy, muốn tìm được chức vụ của nhân viên trong bảng đó, ta phải biết số dòng và số cột của phần tử đó để áp dụng công thức hàm INDEX như hướng dẫn phía trên.

Khi đó, để tìm được số dòng và số cột ta áp dụng thêm công thức MATCH.

Bước 1: Để tìm số dòng, ta phải tìm địa chỉ của mã “NV04” trong dãy mã nhân viên, công thức tìm như sau: =MATCH(“NV04”;B7:B17;0). Kết quả ra số 4. Vậy mã NV04 nằm ở dòng thứ 4 trong dãy mã NV

Bước 2: Để tìm số cột, ta phải tìm địa chỉ của “Chức vụ” trong dãy từ C6 đến F6, công thức tìm như sau: =MATCH(“Chức vụ”;C6:F6;0). Kết quả ra số 2. Vậy cụm từ “chức vụ” nằm ở vị trí thứ 2 trong dãy.

Bước 3: Ta kết hợp hàm INDEX và MATCH như sau:

=INDEX(C7:F17;MATCH(“NV04”;B7:B17;0);MATCH(“Chức vụ”;C6:F6;0))

Bước 4: Kết quả trả về, nhân viên có mã số NV04 làm chức vụ “Văn phòng”

Hỏi: Ý nghĩa của hàm INDEX?

Trả lời: Hàm này trả về giá trị của 1 phần tử trong mang tại một ô nào đó giao giữa cột và dòng.

Hỏi: Trong cú pháp hàm INDEX, vị trí dòng hay vị trí cột ở trước?

Hỏi: Phím tắt Filter trong Excel là gì?

Hỏi: Muốn tải file hướng dẫn các công thức ở trên thì tải ở đâu?

Lượt Xem:11656

Hướng dẫn Excel này giải thích cách sử dụng hàm Excel INDEX với cú pháp và các ví dụ.

Hàm Microsoft Excel INDEX trả về một giá trị trong một bảng dựa trên giao điểm của một hàng và vị trí cột trong bảng đó. Hàng đầu tiên trong bảng là hàng 1 và cột đầu tiên trong bảng là cột 1.

Hàm INDEX là một hàm dựng sẵn trong Excel được phân loại là hàm tra cứu / tham chiếu . Nó có thể được sử dụng như một hàm trang tính (WS) trong Excel. Là một hàm trang tính, hàm INDEX có thể được nhập như một phần của công thức trong một ô của trang tính.

Cú pháp cho hàm INDEX trong Microsoft Excel là:

INDEX( table, row_number, column_number )

Một dải ô chứa bảng dữ liệu.

Vị trí hàng trong bảng nơi có giá trị mà bạn muốn tra cứu. Đây là vị trí hàng tương đối trong bảng chứ không phải số hàng thực tế trong trang tính.

Vị trí cột trong bảng nơi có giá trị mà bạn muốn tra cứu. Đây là vị trí cột tương đối trong bảng chứ không phải số cột thực tế trong trang tính.

Hàm INDEX trả về bất kỳ kiểu dữ liệu nào như một chuỗi, số, ngày tháng, v.v.

Excel 2023, Excel 2013, Excel 2011 cho Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Ví dụ (dưới dạng hàm bảng tính)

Hãy khám phá cách sử dụng INDEX như một hàm trang tính trong Microsoft Excel.

Dựa trên bảng tính Excel ở trên, các ví dụ INDEX sau đây sẽ trả về:

Result: 10247 ‘Intersection of row1 and col1 (cell A2)

Result: “Apples” ‘Intersection of row1 and col2 (cell B2)

Result: $14.00 ‘Intersection of row1 and col3 (cell C2)

Result: 12 ‘Intersection of row1 and col4 (cell D2)

Result: 10249 ‘Intersection of row2 and col1 (cell A3)

Result: Grapes ‘Intersection of row5 and col2 (cell B6)

Bây giờ, hãy xem ví dụ =INDEX(A2:D6,1,1)t rả về giá trị 10247 và xem xét kỹ hơn tại sao.

Tham số đầu tiên trong hàm INDEX là bảng hoặc nguồn dữ liệu nơi tra cứu sẽ được thực hiện.

Trong ví dụ này, tham số đầu tiên là A2: D6 xác định phạm vi ô chứa dữ liệu.

Tham số thứ hai là số hàng được sử dụng để xác định vị trí giao nhau trong bảng. Giá trị 1 cho biết hàng đầu tiên trong bảng, giá trị là 2 là hàng thứ hai, v.v.

Trong ví dụ này, tham số thứ hai là 1 vì vậy chúng ta biết rằng giao lộ của chúng ta sẽ xuất hiện ở hàng đầu tiên trong bảng.

Tham số thứ ba là số cột được sử dụng để xác định vị trí giao nhau trong bảng. Giá trị 1 cho biết cột đầu tiên trong bảng, giá trị của 2 là cột thứ hai, v.v.

Trong ví dụ này, tham số thứ ba là 1 vì vậy chúng ta biết rằng giao lộ của chúng ta sẽ xuất hiện trong cột đầu tiên trong bảng.

Vì bây giờ chúng ta có các giá trị hàng và cột, chúng ta biết rằng chúng ta đang tìm kiếm giao điểm của row1 và col1 trong bảng dữ liệu. Điều này làm cho điểm giao nhau xảy ra tại ô A2 trong bảng để hàm INDEX trả về giá trị 10247.

Tags: Chua co du lieu