Các Hàm Cơ Bản Trong Excel Và Bài Tập / TOP 11 # Xem Nhiều Nhất & Mới Nhất 6/2023 # Top View

Vlookup là một trong những hàm phổ biến nhất trong Excel nhưng bạn không biết hàm Vlookup là gì? Bài tập hàm Vlookup? Trong bài viết này, chúng tôi sẽ cung cấp hướng dẫn từng bước về cách sử dụng, chức năng, các ví dụ và lời khuyên về cách sửa các lỗi Vlookup phổ biến mà bạn hay gặp phải. Từ đó, cho phép người dùng tận dụng tối đa lợi ích của việc sử dụng hàm Vlookup trong việc tìm kiếm tìm kiếm cũng như ghi chép.

Đôi nét về hàm Vlookup trong Excel

– Hãy tưởng tượng rằng bạn đang giữ một bảng tính về mức lương theo giờ của nhân viên (xem bảng tính ‘Trả theo giờ’ bên trái). Vào cuối mỗi tuần, người quản lý nhóm bán hàng sẽ gửi cho bạn một danh sách các giờ làm việc của mỗi nhân viên trong tuần (xem bảng tính ‘Giờ của đội bán hàng’ bên phải). Công việc của bạn là hoàn thành bảng tính ‘Giờ của đội bán hàng’, để hiển thị số tiền phải trả cho từng thành viên.

Cú pháp và quy tắc của hàm Vlookup

Để làm quen với hàm Vlookup là gì? Bài tập hàm Vlookup, bạn cần nắm được cú pháp của hàm Vlookup trong Microsoft Office Excel. Cú pháp của công thức Vlookup chứa bốn đối số hoặc tham số cần thiết để nó hoạt động:

VLOOKUP (lookup_value, table_array, col_index_num, [Range_lookup])

Trong đó các đối số của hàm bao gồm:

lookup_value: Giá trị mà bạn muốn tìm kiếm trong cột đầu tiên của table_array và muốn các giá trị hàng tương ứng được trả về. Nếu Excel tìm thấy giá trị tra cứu trong table_array, nó sẽ trả về giá trị tương ứng của cột đã cho. Nếu không, nó sẽ trả về lỗi # N / A.

table_array: là một phạm vị trong bảng tính Excel chứa các giá trị mà người dùng muốn tìm kiếm.

col_index_num: Là thứ tự của cột cần lấy dữ liệu trên bảng giá trị table_array mà bạn muốn tìm kiếm. Cột đầu tiên của vùng dữ liệu được tính là 1.

[Range_lookup]: trả về kết quả khớp gần đúng hoặc chính xác. Ở đây, ta có:

TRUE tương ứng với 1 là tìm kiếm giá trị tương đối hoặc bằng giá trị này

FALSE tương ứng với 0 là tìm kiếm chính xác.

Quy tắc của hàm Vlookup

Để sử dụng thành thạo hàm Vlookup, bên cạnh việc hiểu rõ công thức bạn cần phải nắm kỹ những quy tắc sau:

Dữ liệu trong table_array được cung cấp phải tổ chức theo cột.

Hàm xem văn bản chữ hoa và chữ thường là bằng nhau. Nghĩa là tra cứu chuỗi “Văn bản” sẽ khớp với “văn bản”.

Hàm Vlookup không nhận các giá trị số và văn bản là bằng nhau.

Khi kiểm tra xem hai ký tự có khớp nhau hay không, hàm Vlookup sẽ so sánh tất cả các kí tự bao gồm cả khoảng trắng ở đầu hoặc cuối. Do đó, với hai chuỗi ký tự là “văn bản” (không dấu cách) và “văn bản ” (có dấu cách)không phải là hai giá trị chính xác.

(?): phù hợp với bất kỳ ký tự đơn nào

(*): khớp với bất kỳ chuỗi ký tự nào

Ví dụ: lookup_value “t*” sẽ khớp với bất kì chuỗi văn bản nào bắt đầu bằng ký tự “t”

Ví dụ về Vlookup với giá trị chính xác

Trong ví dụ này, hàm Vlookup của Excel được sử dụng để tra cứu số tiền phải trả cho mỗi thành viên của nhóm bán hàng trong tuần trước.Bên dưới là mức lương hàng giờ của nhân viên được lưu trữ trong bảng tính thứ nhất và một danh sách các giờ làm việc được lưu trữ trong bảng tính thứ hai.

Kết quả tìm kiếm được chèn ở vào ô tương ứng ở cột C

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

Công thức được sử dụng trong bảng tính trên là:

= VLOOKUP (A2, ‘Hourly Pay’! A:B, 2, FALSE)

Trong đó:

A2: Ô A2 là lookup_value tức là giá trị cần tìm chính là chuỗi văn bản “Benson, Paul”.

‘Hourly Pay’! A:B: table_array được tạo thành từ các cột A và B của bảng tính “Lương theo giờ”. Hàm Vlookup sẽ tìm kiếm xuống cột ngoài cùng bên trái (tức là cột A) của table_array này để tìm kiếm chuỗi văn bản “Benson, Paul”.

2: Đối số col_index_num được đặt thành giá trị 2 cho biết, giá trị được trả về phải được lấy từ cột 2 của table_array được cung cấp (nghĩa là từ cột B của bảng tính “Lương theo giờ “).

FALSE: Đối số [range_lookup] được đặt thành FALSE, cho biết hàm Vlookup tìm kiếm giá trị chính xác với lookup_value .

Nếu không tìm thấy kết quả khớp chính xác, thì hàm Vlookup sẽ trả về lỗi.

Ví dụ về Vlookup với giá trị tương đối

Hãy tưởng tượng một tài khoản ngân hàng có lãi suất thay đổi phụ thuộc vào số dư của nó được hiển thị theo bảng bên dưới:

Kết quả “Tài khoản ngân hàng” sau khi áp dụng hàm Vlookup

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

Công thức được sử dụng trong bảng tính trên là:

= VLOOKUP (B2, ‘Interest Rates’! A2:C5, 3, TRUE)

Trong đó:

B2: Ô B2 là lookup_value tức là giá trị cần tìm chính là giá trị 5.69.

‘Interest Rates’! A2:C5: table_array là phạm vi được tạo thành từ A2 và C5 của bảng tính “Lãi suất”. Hàm Vlookup sẽ tìm kiếm xuống cột bên trái (tức là cột A) của table_array, để tìm kiếm kết quả bằng hoặc khớp gần nhất.

3: : Đối số col_index_num được đặt thành giá trị 3 cho biết giá trị được trả về bởi hàm VLOOKUP nên được lấy từ cột 3 của table_array (tức là từ cột C của bảng tính Lãi suất”).

TRUE: Đối số [range_lookup] được đặt thành TRUE, cho biết hàm Vlookup để tìm kết quả khớp gần nhất hoặc bằng với lookup_value. Tức là nếu một kết hợp chính xác không được tìm thấy, thì hàm sẽ tự động trả về giá trị gần nhất.

Khắc phục lỗi Vlookup

Lỗi thường gặp

Nguyên nhân

Xảy ra khi hàm Vlookup không tìm thấy kết quả khớp với lookup_value được cung cấp.

Nguyên nhân của điều này thường phụ thuộc vào đối số [phạm vi_lookup] được cung cấp:

● Giá trị nhỏ nhất trong cột bên trái của table_array lớn hơn lookup_value được cung cấp

● Cột bên trái của table_array không theo thứ tự tăng dần.

● Không tìm thấy kết quả khớp chính xác với lookup_value trong cột bên trái của table_array

#REF!

Xảy ra nếu một trong hai:

● Đối số col_index_num được cung cấp lớn hơn số lượng cột trong table_array được cung cấp

● Tham chiếu các ô không tồn tại.

#VALUE

Xảy ra nếu một trong hai:

Đối số col_index_num được cung cấp là <1 hoặc không được nhận dạng dưới dạng giá trị số.

Đối số [Range_lookup] được cung cấp không được công nhận là một trong các giá trị logic của TRUE hoặc FALSE.

Trả về giá trị không chính xác

Nếu hàm Vlookup trả về giá trị sai, hãy kiểm tra các mục sau:

Các giá trị bạn đang tìm kiếm phải nằm ở cột bên trái của table_array

Nếu đối số [range_lookup] được đặt thành TRUE (hoặc bị bỏ qua), hàm sẽ trả về kết quả khớp gần nhất bên dưới lookup_value. Để điều này hoạt động chính xác, cột bên trái của table_array phải theo thứ tự tăng dần.

Kiểm tra xem đối số col_index_num có tính từ cột đầu tiên của table_array . Nó không nhất thiết giống như số cột của bảng tính.

Nếu đối số [range_lookup] được đặt thành FALSE, hàm Vlookup yêu cầu giá trị chính xác. Kiểm tra xem chỉ có một kết quả khớp với lookup_value trong cột bên trái của table_array. Lưu ý rằng nếu có nhiều hơn một giá trị trùng khớp, chức năng Vlookup sẽ nhận giá trị đầu tiên mà nó gặp.

Bài tập hàm Vlookup

Hàm Vlookup là gì? Bài tập hàm Vlookup? Trong phần này, chúng tôi sẽ gửi đến bạn hai bài tập cơ bản dành cho người mới bắt đầu biết đến hàm Vlookup:

Bài tập 1

Trong đó:

Đối số đầu tiên cho hàm tức là ‘ lookup_value’ = E4 (Emueluel).

Đối số thứ hai tức là ‘ table_array’ = A3:C16 (Phạm vi bảng học sinh).

Đối số thứ ba tức là ‘ column_index’ = 2 (số cột có giá trị mà hàm VLOOKUP sẽ trả về).

Đối số thứ tư tức là ‘ range_lookup’ = FALSE (Biểu thị rằng chúng tôi chỉ muốn giá trị chính xác).

Bài tập 2

Trong đó:

Đối số đầu tiên cho hàm tức là ‘ lookup_value’ = A * (Bất kỳ từ nào bắt đầu bằng bảng chữ cái ‘A’)

Đối số thứ hai tức là ‘ table_array’ = A3: C16 (Phạm vi bảng học sinh)

Đối số thứ ba tức là ‘ column_index’ = 3 (số cột có giá trị mà hàm tra cứu dọc sẽ trả về)

Đối số thứ tư tức là ‘ range_lookup’ = FALSE (Biểu thị rằng chúng tôi chỉ muốn giá trị khớp tương ứng)

Bài tập Excel từ cơ bản đến nâng cao sẽ giúp bạn luyện tập từ những bước đầu làm quen cho đến việc thành thạo công cụ tính toán, thống kê Excel tuyệt vời này. Để nắm vững kiến thức sử dụng Excel bạn cần luyện tập thường xuyên với bài tập Excel tổng hợp.

Để thành thạo bất cứ kỹ năng gì thì cũng cần phải trải qua quá trình luyện tập, đối với Excel cũng không ngoại lệ. Ngược lại, đây là một công cụ rất khó để thành thạo, sử dụng tốt những công thức, hàm tính toán. Vì vậy, bài tập Excel từ cơ bản đến nâng cao là nguồn tài nguyên quý giá để bạn có cơ hội thành thạo Microsoft Excel.

1. Bài mẫu hàm DURATION 2. Bài mẫu hàm DDB 3. Bài mẫu hàm DB 4. Bài mẫu hàm CUMPRINC 5. Bài mẫu hàm CUMIPMT 6. Bài mẫu hàm COUPPCD 7. Bài mẫu hàm COUPNUM 8. Bài mẫu hàm COUPNCD 9. Bài mẫu hàm COUPDAYSNC 10. Bài mẫu hàm COUPDAYS 11. Bài mẫu hàm UPPER 12. Bài mẫu hàm TRIM 13. Bài mẫu hàm SUBSTITUTE 14. Bài mẫu hàm NORMINV 15. Bài mẫu hàm NORMDIST 16. Bài mẫu hàm DAVERAGE 17. Bài mẫu hàm COUPDAYBS 18. Bài mẫu hàm CHITEST 19. Bài mẫu hàm AMORDEGRC 20. Bài mẫu hàm ACCRINTM 21. Bài mẫu hàm POISSON 22. Bài mẫu hàm PV 23. Bài mẫu hàm FTEST 24. Bài mẫu hàm TEXT 25. Bài mẫu hàm TDIST 26. Bài mẫu hàm STDEVP 27. Bài mẫu hàm STDEV 28. Bài mẫu hàm RANK 29 Bài mẫu hàm QUARTILE 30. Bài mẫu hàm PROPER 31. Bài mẫu hàm PERCENTRANK 32. Bài mẫu hàm PERCENTILE 33. Bài mẫu hàm MODE 34. Bài mẫu hàm LOGINV 35. Bài mẫu hàm HYPGEOMDIST 36. Bài mẫu hàm GAMMAINV 37. Bài mẫu hàm GAMMADIST 38. Bài mẫu hàm FINV 39. Bài mẫu hàm FDIST 40. Bài mẫu hàm EXPONDIST 41. Bài mẫu hàm CRITBINOM 42. Bài mẫu hàm COVAR 43. Bài mẫu hàm CONFIDENCE 44. Bài mẫu hàm CHIINV 45. Bài mẫu hàm CHIDIST 46. Bài mẫu hàm BINOMDIST 47. Bài mẫu hàm YEAR 48. Bài mẫu hàm TODAY 49. Bài mẫu hàm SUMIF 50. Bài mẫu hàm SUM 51. Bài mẫu hàm RIGHT 52. Bài mẫu hàm PRODUCT 53. Bài mẫu hàm POWER 54. Bài mẫu hàm OR 55. Bài mẫu hàm NOT 56. Bài mẫu hàm MONTH 57. Bài mẫu hàm AND 58. Bài mẫu hàm ABS 59. Bài mẫu hàm MOD 60. Bài mẫu hàm MIN 61. Bài mẫu hàm MAX 62. Bài mẫu hàm LEFT 63. Bài mẫu hàm INT 64. Bài mẫu hàm DAY 65. Bài mẫu hàm COUNTIF 66. Bài mẫu hàm COUNT 67. Bài mẫu hàm AVERAGE 68. Bài mẫu hàm thống kê trong Excel 2. Tổng hợp bài tập Excel các hàm nâng cao

Khi bạn đã thành thạo, giải quyết hết các bài tập cơ bản thì những bài tập nâng cao sẽ giúp kỹ năng Excel của bạn phát triển toàn diện, bổ sung thêm một số hàm khó, nâng cao đồng thời rèn luyện khả năng kết hợp giữa các hàm, công thức hợp lý.

Các hàm cơ bản trong Excel và cách sử dụng. Với các hàm cơ bản trong Excel này bạn sẽ sử dụng Excel tốt hơn trông thấy đó.

Hãy ghi nhớ những hàm Excel này, chúng sẽ rất hữu dụng cho bạn đó. Bạn hãy copy chúng về máy tính rồi đợi khi nào rảnh mang ra nghiền ngẫm nha.

II. NHÓM HÀM TOÁN HỌC. 1. Hàm ABS:Lấy giá trị tuyệt đối của một sốCú pháp: ABS(Number)Đối số: Number là một giá trị số, một tham chiếu hay một biểu thức.Ví dụ:=ABS(A5 + 5)2. POWER:Hàm trả về lũy thừa của một số.Cú pháp: POWER(Number, Power)Các tham số: – Number: Là một số thực mà bạn muốn lấy lũy thừa.– Power: Là số mũ.Ví dụ= POWER(5,2) = 25

3. Hàm PRODUCT:Bạn có thể sử dụng hàm PRODUCT thay cho toán tử nhân * để tính tích của một dãy.Cú pháp:PRODUCT(Number1, Number2…)Các tham số: Number1, Number2… là dãy số mà bạn muốn nhân.

4. Hàm MOD:Lấy giá trị dư của phép chia.Cú pháp: MOD(Number, Divisor)Các đối số: – Number: Số bị chia.– Divisor: Số chia.

IV. NHÓM HÀM CHUỖI. 1. Hàm LEFT:Trích các ký tự bên trái của chuỗi nhập vào.Cú pháp: LEFT(Text,Num_chars)Các đối số: – Text: Chuỗi văn bản.– Num_Chars: Số ký tự muốn trích.Ví dụ:=LEFT(Tôi tên là,3) = “Tôi”2. Hàm RIGHT:Trích các ký tự bên phải của chuỗi nhập vào.Cú pháp: RIGHT(Text,Num_chars)Các đối số: tương tự hàm LEFT.Ví dụ:=RIGHT(Tôi tên là,2) = “là”3. Hàm MID:Trích các ký tự từ số bắt đầu trong chuỗi được nhập vào.Cú pháp:MID(Text,Start_num, Num_chars)Các đối số:– Text: chuỗi văn bản.– Start_num: Số thứ tự của ký tự bắt đầu được trích.– Num_chars: Số ký tự cần trích.4. Hàm UPPER:Đổi chuỗi nhập vào thành chữ hoa.Cú pháp: UPPER(Text)5. Hàm LOWER:Đổi chuỗi nhập vào thành chữ thường.Cú pháp: LOWER(Text)6. Hàm PROPER:Đổi ký từ đầu của từ trong chuỗi thành chữ hoa.Cú pháp: PROPER(Text)Ví dụ: =PROPER(phan van a) = “Phan Van A”7. Hàm TRIM:Cắt bỏ các ký tự trắng ở đầu chuỗi và cuối chuỗi.Cú pháp: TRIM(Text)

V. NHÓM HÀM NGÀY THÁNG. 1. Hàm DATE:Hàm Date trả về một chuỗi trình bày một kiểu ngày đặc thù.Cú pháp: DATE(year,month,day)Các tham số:– Year: miêu tả năm, có thể từ 1 đến 4 chữ số. Nếu bạn nhập 2 chữ số, theo mặc định Excel sẽ lấy năm bắt đầu là: 1900.(Ví dụ)– Month: miêu tả tháng trong năm. Nếu month lớn hơn 12 thì Excel sẽ tự động tính thêm các tháng cho số miêu tả năm.(Ví dụ)– Day: miêu tả ngày trong tháng. Nếu Day lớn hơn số ngày trong tháng chỉ định, thì Excel sẽ tự động tính thêm ngày cho số miêu tả tháng.(Ví dụ)Lưu ý:– Excel lưu trữ kiểu ngày như một chuỗi số liên tục, vì vậy có thể sử dụng các phép toán cộng (+), trừ (-) cho kiểu ngày.(Ví dụ)2. Hàm DAY:Trả về ngày tương ứng với chuỗi ngày đưa vào. Giá trị trả về là một số kiểu Integer ở trong khoảng từ 1 đến 31.Cú pháp: DAY(Serial_num)Tham số:Serial_num: Là dữ liệu kiểu Date, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác.(Ví dụ)3. Hàm MONTH:Trả về tháng của chuỗi ngày được mô tả. Giá trị trả về là một số ở trong khoảng 1 đến 12.Cú pháp: MONTH(Series_num)Tham số: Series_num: Là một chuỗi ngày, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác. (Ví dụ)4. Hàm YEAR:Trả về năm tương ứng với chuỗi ngày đưa vào. Year được trả về là một kiểu Integer trong khoảng 1900-9999.Cú pháp: YEAR(Serial_num)Tham số:Serial_num: Là một dữ liệu kiểu ngày, có thể là một hàm DATE hoặc kết quả của một hàm hay công thức khác.(ví dụ)5. Hàm TODAY:Trả về ngày hiện thời của hệ thống.Cú pháp: TODAY()Hàm này không có các đối số.6. Hàm WEEKDAY:Trả về số chỉ thứ trong tuần.Cú pháp:WEEKDAY(Serial, Return_type)Các đối số: – Serial: một số hay giá trị kiểu ngày.– Return_type: chỉ định kiểu dữ liệu trả về.

VI. HÀM VỀ THỜI GIAN. 1. Hàm TIME:Trả về một chuỗi trình bày một kiểu thời gian đặc thù. Giá trị trả về là một số trong khoảng từ 0 đến 0.99999999, miêu tả thời gian từ 0:00:00 đến 23:59:59.Cú pháp:TIME(Hour,Minute,Second)Các tham số: Được tính tương tự ở hàm DATE.– Hour: miêu tả giờ, là một số từ 0 đến 32767. – Minute: miêu tả phút, là một số từ 0 đến 32767.– Second: miêu tả giây, là một số từ 0 đến 32767.2. Hàm HOUR:Trả về giờ trong ngày của dữ liệu kiểu giờ đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 (12:00A.M) đến 23 (11:00P.M).Cú pháp: HOUR(Serial_num)Tham số:Serial_num: Là dữ liệu kiểu Time. Thời gian có thể được nhập như:– Một chuỗi kí tự nằm trong dấu nháy (ví dụ “5:30 PM”)– Một số thập phân (ví dụ 0,2145 mô tả 5:08 AM)– Kết quả của một công thức hay một hàm khác.3. Hàm MINUTE:Trả về phút của dữ liệu kiểu Time đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 đến 59.Cú pháp: MINUTE(Serial_num)Tham số:Serial_num: Tương tự như trong công thức HOUR.4. Hàm SECOND:Trả về giây của dữ liệu kiểu Time đưa vào. Giá trị trả về là một kiểu Integer trong khoảng từ 0 đến 59.Cú pháp: SECOND(Serial_num)Tham số:Serial_num: Tương tự như trong công thức HOUR.5. Hàm NOW:Trả về ngày giờ hiện thời của hệ thống.Cú pháp: NOW()Hàm này không có các đối số.

VII. NHÓM HÀM DÒ TÌM DỮ LIỆU. 1. Hàm VLOOKUP:Tìm ra một giá trị khác trong một hàng bằng cách so sánh nó với các giá trị trong cột đầu tiên của bảng nhập vào.

Cú pháp:VLOOKUP(Lookup Value, Table array, Col idx num, [range lookup])Các tham số:– Lookup Value: Giá trị cần đem ra so sánh để tìm kiếm. – Table array: Bảng chứa thông tin mà dữ liệu trong bảng là dữ liệu để so sánh. Vùng dữ liệu này phải là tham chiếu tuyệt đối.Nếu giá trị Range lookup là TRUE hoặc được bỏ qua, thì các giá trị trong cột dùng để so sánh phải được sắp xếp tăng dần.– Col idx num: số chỉ cột dữ liệu mà bạn muốn lấy trong phép so sánh. – Range lookup: Là một giá trị luận lý để chỉ định cho hàm VLOOKUP tìm giá trị chính xác hoặc tìm giá trị gần đúng. + Nếu Range lookup là TRUE hoặc bỏ qua, thì giá trị gần đúng được trả về.Chú ý:– Nếu giá trị Lookup value nhỏ hơn giá trị nhỏ nhất trong cột đầu tiên của bảng Table array, nó sẽ thông báo lỗi #N/A.Ví dụ:=VLOOKUP(F11,$C$20:$D$22,2,0)Tìm một giá trị bằng giá trị ở ô F11 trong cột thứ nhất, và lấy giá trị tương ứng ở cột thứ 2. 2. Hàm HLOOKUP:Tìm kiếm tương tự như hàm VLOOKUP nhưng bằng cách so sánh nó với các giá trị trong hàng đầu tiên của bảng nhập vào.Cú pháp:HLOOKUP(Lookup Value, Table array, Col idx num, [range lookup])Các tham số tương tự như hàm VLOOKUP.3. Hàm INDEX:Trả về một giá trị hay một tham chiếu đến một giá trị trong phạm vi bảng hay vùng dữ liệu.Cú pháp:INDEX-(Array,Row_num,Col_num)Các tham số:– Array: Là một vùng chứa các ô hoặc một mảng bất biến.Nếu Array chỉ chứa một hàng và một cột, tham số Row_num hoặc Col_num tương ứng là tùy ý.Nếu Array có nhiều hơn một hàng hoặc một cột thì chỉ một Row_num hoặc Col_num được sử dụng.– Row_num: Chọn lựa hàng trong Array. Nếu Row_num được bỏ qua thì Col_num là bắt buộc.– Col_num: Chọn lựa cột trong Array. Nếu Col_num được bỏ qua thì Row_num là bắt buộc.

Nguồn: Sưu tầm

Tìm trên Google:

cách sử dụng các hàm trong excel

CÁC HÀM CƠ BẢN TRONG EXCEL VÀ CÁC VÍ DỤ MINH HỌA

Excel cung cấp một số lượng lớn các chức năng để phân tích, kiểm toán và tính toán dữ liệu. Một trong số những chức năng này được sử dụng bởi nhiều người sử dụng Excel hàng ngày và cũng có một số người chỉ sử dụng những phép toán này trong trường hợp cụ thể.

Trong hướng dẫn này, chúng ta sẽ không khám phá từng chức năng trong Microsoft Excel mà chỉ đưa ra cho bạn cái nhìn tổng quan về những chức năng hữu ích trong Excel được sử dụng nhiều nhất.

CHỨC NĂNG VĂN BẢN TRONG EXCEL (CÁC CHỨC NĂNG CHUỖI)

CHỨC NĂNG TEXT

TEXT(value, format_text) được sử dụng để chuyển đổi một số hoặc một ngày thành một chuỗi kiểm tra ở định dạng được chỉ định, trong đó:

value là một giá trị số bạn muốn chuyển đổi sang văn bản.Format_text là định dạng mong muốn.Các công thức sau đây thể hiện hoạt động của chức năng Excel TEXT:

=TEXT(A1,”mm/dd/yyyy”) – chuyển ngày tháng trong ô A1 sang một chuỗi văn bản theo định dạng ngày truyền thống của Hoa Kỳ, chẳng hạn như “01/01/2015” (tháng / ngày / năm).

VÍ DỤ CHO CÔNG THỨC TEXT:

Hàm TEXT dùng để chuyển đổi một ngày sang định dạng văn bảnCông TEXT dùng để chuyển một số thành dạng văn bản

HÀM CONCATENATE

HÀM SUBSTITUTE

SUBSTITUTE(text, old_text, new_text, [instance_num]) sẽ thay thế một bộ ký tự bằng một ký tự khác trong một ô được chỉ định hoặc một chuỗi văn bản. Cú pháp của hàm SUBSTITUTE như sau:

Text – chuỗi văn bản ban đầu hoặc tham chiếu đến ô mà bạn muốn thay thế một số ký tự nhất định.Old_text – các ký tự bạn muốn thay thế.New_text – các ký tự mà bạn mới bạn muốn dùng để thế chỗ văn bản cũ.Nth_appearance – một tham số tùy chọn cho old_text mà bạn muốn thay thế bằng new_text. Sau đó, mỗi lần xuất hiện của văn bản cũ sẽ được thay thế bằng văn bản mới.Ví dụ: công thức SUBSTITUTE sau thay thế tất cả các dấu phẩy trong ô A1 bằng dấu chấm phẩy:

CÔNG THỨC SUBSTITUTE:

– Loại bỏ ngắt dòng trong một ô– Chuyển đổi chuỗi văn bản với dấu phân cách tùy chỉnh thành ngày

VALUE(text) – chuyển đổi một chuỗi văn bản thành một số.

Hàm này thực sự hữu ích khi chuyển đổi các giá trị định dạng văn bản đại diện cho các con số thành các con số có thể được sử dụng trong các công thức Excel khác và tính toán.

VÍ DỤ CHO HÀM VALUE:

Chuyển đổi chữ số định dạng văn bản thành sốHàm VALUE dùng để chuyển đổi văn bản thành ngàyHàm EXACTEXACT(text1, text2) so sánh hai chuỗi văn bản và trả về giá trị TRUE nếu cả hai giá trị đều giống nhau, và trả về FALSE nếu không giống nhau.

Ví dụ: nếu A2 là “apples” và B2 là “Apples”, công thức =EXACT(A2, B2) sẽ trả về FALSE vì chúng không khớp chính xác.

Hàm EXACT hiếm khi được sử dụng riêng, nhưng lại hữu ích khi kết hợp với những hàm khác để tạo nên tác vụ phức tạp hơn, ví dụ như kết hợp với Vlookup dùng thao tác với văn bản trong Excel.

CÁC HÀM SỬ DỤNG ĐỂ CHUYỂN ĐỔI VĂN BẢN (UPPER, LOWER, PROPER)

Microsoft Excel cung cấp 3 hàm văn bản để chuyển đổi giữa UPPER, LOWER, PROPER.

UPPER(text) – chuyển đổi tất cả các ký tự trong một chuỗi ký tự được chỉ định sang chữ hoa.

LOWER(text) – thay đổi tất cả các chữ in hoa trong một chuỗi văn bản thành chữ thường.

PROPER(text) – viết hoa chữ cái đầu tiên của mỗi từ và chuyển đổi tất cả các chữ cái khác sang chữ thường.

TRÍCH XUẤT CÁC KÝ TỰ VĂN BẢN (LEFT, RIGHT, MID)

Nếu bạn cần một công thức để trả về một số ký tự nhất định từ một chuỗi văn bản, sử dụng một trong các hàm Excel sau.

LEFT(text, [num_chars]) – trả về một số ký tự nhất định bắt đầu từ đầu chuỗi văn bản.

RIGHT(text,[num_chars]) – trả về một số ký tự được chỉ định bắt đầu từ cuối chuỗi văn bản.

MID(text, start_num, num_chars) – trả về một số ký tự nhất định từ một chuỗi văn bản, bắt đầu ở bất kỳ vị trí nào bạn chỉ định.

Trong các hàm này, bạn cung cấp các đối số sau:

CÁC HÀM LOGIC TRONG EXCEL

Microsoft Excel cung cấp một số ít các chức năng logic đánh giá một điều kiện xác định và trả về giá trị tương ứng.

AND, OR, XOR chức năngAND(logical1, [logical2], …) – trả về TRUE nếu tất cả các đối số đánh giá TRUE, FALSE nếu ngược lại.

OR(logical1, [logical2], …) – trả về TRUE nếu ít nhất một trong các đối số là TRUE.

NOT(logical) – đảo ngược giá trị đối số của nó, nghĩa là nếu đánh giá lôgic thành FALSE, hàm NOT sẽ trả về TRUE và ngược lại.

Ví dụ, cả hai công thức sau đây sẽ trả về FALSE:

=NOT(TRUE)

=NOT(2*2=4)

Hàm IF trong Excel đôi khi được gọi là “hàm điều kiện” vì nó trả về một giá trị dựa trên điều kiện mà bạn chỉ định. Cú pháp của hàm IF như sau:

IF(logical_test, [value_if_true], [value_if_false])Công thức IF kiểm tra các điều kiện được biểu diễn trong đối số logical_test và trả về một giá trị (value_if_true) nếu điều kiện được đáp ứng và một giá trị khác (value_if_false) nếu điều kiện không được đáp ứng.

Và đây là một ví dụ về công thức hàm IF kết hợp thể hiện điểm thi trong ô A2:

HÀM IFERROR VÀ IFNA

Cả hai hàm đều được sử dụng để kiểm tra lỗi trong một công thức tính nhất định, và nếu có lỗi xảy ra, các hàm MS Excel trả lại một giá trị được chỉ định thay thế.

IFERROR(value, value_if_error) – kiểm tra lỗi của công thức hoặc biểu thức đánh giá. Nếu có lỗi, công thức trả về giá trị được cung cấp trong đối số value_if_error, nếu không, kết quả của công thức sẽ được trả về. Hàm này xử lý tất cả các lỗi Excel có thể xảy ra, bao gồm VALUE, N/A, NAME, REF, NUM và những lỗi khác. Nó có sẵn trong Excel 2007 và những phiên bản cao hơn.

IFNA(value, value_if_na) – được giới thiệu trong Excel 2013, nó hoạt động tương tự như IFERROR, nhưng chỉ xử lý lỗi #N/A.

CÁC CÔNG THỨC TOÁN HỌC TRONG EXCEL

TÌM TỔNG CỦA CÁC Ô

SUM(number1,[number2],…) trả về tổng các đối số của nó. Các đối số có thể là các số, các tham chiếu ô hoặc các giá trị số bằng công thức.

Ví dụ, công thức toán học đơn giản =SUM(A1:A3, 1) cộng các giá trị trong ô A1, A2 và A3, và thêm 1 vào kết quả.Các hàm SUMIF và SUMIFS (tổng điều kiện)Cả hai hàm này dùng để cộng các ô trong một phạm vi xác định đáp ứng một điều kiện nhất định. Sự khác biệt là SUMIF có thể đánh giá chỉ một tiêu chuẩn, trong khi SUMIFS, được giới thiệu trong Excel 2007, cho phép sử dụng nhiều tiêu chí. Hãy lưu ý rằng thứ tự của các đối số là khác nhau trong mỗi chức năng:

HÀM SUMPRODUCT

SUMPRODUCT(array1,array2, …) là một trong số ít các chức năng của Microsoft Excel dùng để xử lý mảng. Nó kết hợp với các thành phần mảng được cung cấp và trả về tổng sản phẩm.

Bản chất của hàm SUMPRODUCT có thể khó nắm bắt vì vậy các bạn có thể theo dõi các ví dụ cụ thể của hàm này trong bài viết dành riêng cho hàm SUMPRODUCT để có được hiểu biết đầy đủ.

Tạo các số ngẫu nhiên (RAND và RANDBETWEEN)Microsoft Excel cung cấp 2 chức năng để tạo ra các số ngẫu nhiên. Cả hai đều là các hàm không ổn định, có nghĩa là một số mới được trả về mỗi khi bảng tính thực hiện tính toán.

RAND() – trả về một số thực (số thập phân) ngẫu nhiên giữa 0 và 1.

RANDBETWEEN(bottom, top) – trả về một số nguyên ngẫu nhiên giữa số dưới cùng và số trên cùng mà bạn chỉ định.

CHỨC NĂNG LÀM TRÒN

Có một số chức năng để làm tròn số trong Excel, và Hướng dẫn làm tròn của Excel của chúng tôi đã giúp bạn giải thích cách sử dụng những chức năng đó dựa trên tiêu chí của bạn. Vui lòng nhấp vào tên của chức năng để tìm hiểu cú pháp và ví dụ về sử dụng.

ROUND – làm tròn số đến số chữ số được chỉ định.

ROUNDUP – làm tròn lên, đến số ký tự được chỉ định.

ROUNDDOWN – làm tròn xuống, đến số ký tự được chỉ định.

MROUND – làm tròn số đến một bội số được chỉ định.

FLOOR – làm tròn số xuống, đến bội số được chỉ định.

CEILING – tròn số lên, đến bội số được chỉ định.

INT – làm tròn số xuống số nguyên gần nhất.

TRUNC – cắt bớt số số đến số nguyên gần nhất.

EVEN – làm tròn số đến số nguyên chẵn gần nhất.

ODD – làm tròn số lên đến số nguyên lẻ gần nhất.

TRẢ VỀ SỐ DƯ SAU KHI CHIA (HÀM MOD)

MOD(number, divisor) trả về số sư sau khi chia.

Hàm này thực sự rất hữu ích trong nhiều trường hợp khác nhau, ví dụ như:

Tính tổng các giá trị trong mỗi hàng, hoặc một hàng được chỉ địnhThay đổi màu sắc của hàng trong Excel

CÁC HÀM THỐNG KÊ TRONG EXCEL

Trong số nhiều hàm thống kê của Excel, có một số hàm có thể áp dụng để sử dụng cho những công việc mang tính chuyên nghiệp cao.

TÌM CÁC GIÁ TRỊ LỚN NHẤT, NHỎ NHẤT VÀ TRUNG BÌNH

MIN(number1, [number2], …) – trả về giá trị nhỏ nhất từ danh sách các đối số.

MAX(number1, [number2], …) – trả về giá trị lớn nhất từ danh sách các đối số

AVERAGE(number1, [number2], …) – trả giá trị trung bình của các đối số.

SMALL(array, k) – trả về giá trị nhỏ nhất thứ k trong mảng.

LARGE (mảng, k) – trả về giá trị lớn nhất thứ k trong mảng.

COUNT(value1, [value2], …) – trả về số lượng các giá trị số (số và ngày) trong danh sách các đối số.

COUNTA(value1, [value2], …) – trả về số ô có chứa dữ liệu trong danh sách các đối số. Nó đếm các ô có chứa bất kỳ thông tin, bao gồm các giá trị lỗi và các chuỗi văn bản rỗng (“”) được trả về bởi các công thức khác.

COUNTBLANK(range) – đếm số ô trống trong một phạm vi xác định. Các ô có chuỗi văn bản trống (“”) cũng được tính là các ô trống.

COUNTIF(range, criteria) – tính số ô trong phạm vi đáp ứng các tiêu chí được chỉ định.

COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…) – đếm số ô đáp ứng được tất cả các tiêu chí được chỉ định.

HÀM LOOKUP TRONG EXCEL VÀ CÁC HÀM LIÊN QUAN

Các hàm dạng này của MS Excel có ích khi bạn cần tìm thông tin nhất định trong một bảng dựa trên một giá trị trong một cột hoặc trả về một tham chiếu đến một ô nhất định.

HÀM VLOOKUP

Hàm VLOOKUP tìm một giá trị được chỉ định trong cột đầu tiên và kéo dữ liệu phù hợp từ cùng một hàng trong một cột khác.

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])Lookup_value – giá trị cần tìm kiếm.Table_array – hai hoặc nhiều cột dữ liệu.Col_index_num – số cột để kéo dữ liệu.Range_lookup – xác định tìm kiếm là hoàn toàn chính xác (FALSE) hay chỉ tương đối chính xác (TRUE hoặc bỏ qua).Ví dụ: công thức =VLOOKUP(“apples”, A2:C10, 3) tìm kiếm “apples” trong ô A2 đến A10 và trả về giá trị phù hợp từ cột C:

INDEX(array, row_num, [column_num]) – trả về một tham chiếu đến một ô bên trong mảng dựa trên số hàng và cột mà bạn chỉ định.

Đây là một công thức INDEX đơn giản: =INDEX(A1:C10, 3 ,4) hàm này tìm trong ô A1 đến C10 và trả về giá trị tại giao điểm của hàng thứ 3 và cột 4, đó là ô D3.

MATCH(lookup_value, lookup_array, [match_type]) – tìm kiếm lookup_value trong lookup_array, và sau đó trả về vị trí tương đối của mục đó trong dãy.

Sự kết hợp giữa các hàm MATCH và INDEX có thể được sử dụng như là một thay thế mạnh mẽ và linh hoạt hơn cho Vlookup của Excel.

HÀM INDIRECT

INDIRECT(ref_text, [a1]) – trả về một tham chiếu ô hoặc dải ô được chỉ định bởi một chuỗi văn bản.

OFFSET(reference, rows, cols, [height], [width]) – trả về một tham chiếu đến một dãy ô được tính từ một ô bắt đầu hoặc một dãy các ô theo số hàng và cột được chỉ định.

Ví dụ, =OFFSET(A1, 1, 2) trả về giá trị trong ô C2 vì nó là 1 hàng xuống và 2 cột bên trái tính từ A1.

HÀM TRANSPOSE

TRANSPOSE(array) – biến đổi một khoảng nằm ngang của các ô thành một dải đứng và ngược lại, tức là chuyển hàng thành các cột và cột thành hàng.

HÀM HYPERLINK

HYPERLINK(link_location, [friendly_name]) – Tạo một siêu liên kết đến một tài liệu được lưu trữ trên mạng nội bộ hoặc Internet.

CÁC HÀM TÀI CHÍNH TRONG EXCEL

FV(rate, nper ,pmt ,[pv], [type]) – tính giá trị tương lai của một khoản đầu tư dựa trên lãi suất cố định.

HÀM NGÀY THÁNG TRONG EXCEL

Đối với những người sử dụng Excel thường xuyên, các hàm ngày tháng ngày càng trở nên quen thuộc hơn và được sử dụng trong rất nhiều trường hợp khác nhau.

TẠO LẬP NGÀY THÁNG

DATE – trả về một ngày được chỉ định dưới dạng số sê ri.DATEVALUE – chuyển đổi một chuỗi văn bản đại diện cho ngày để định dạng ngày.

NGÀY VÀ GIỜ HIỆN THỜI

TODAY – trả về ngày hiện tại.NOW – trả về ngày và thời gian hiện tại.

TRÍCH RA NGÀY THÁNG VÀ CÁC THÀNH PHẦN NGÀY THÁNG

DAY – trả về ngày trong tháng.MONTH – trả về tháng của một ngày được chỉ định.YEAR – trả về năm của một ngày nhất định.EOMONTH – trả về ngày cuối cùng của tháng.WEEKDAY – trả về ngày trong tuần.WEEKNUM – trả về số tuần của một ngày.

TÍNH CHÊNH LỆCH NGÀY

DATEDIF – trả về sự khác biệt giữa hai ngày.EDATE – trả về một ngày nằm trong tháng đã định trước, có thể đứng trước hoặc sau ngày bắt đầu.YEARFRAC – trả về tỷ lệ của một khoảng thời gian trong một năm.

TÍNH CÁC NGÀY LÀM VIỆC

WORKDAY – Trả về một số tuần tự thể hiện số ngày làm việc, có thể là trước hay sau ngày bắt đầu làm việc và trừ đi những ngày cuối tuần và ngày nghỉ (nếu có) trong khoảng thời gian đó.WORKDAY.INTL – tính ngày tháng là số ngày trong tuần được chỉ định trước hoặc sau ngày bắt đầu, với thông số cuối tuần tùy chỉnh.NETWORKDAYS – trả về số ngày làm việc giữa hai ngày được chỉ định.NETWORKDAYS.INTL – trả về số ngày làm việc giữa hai ngày được chỉ định với ngày cuối tuần tùy chỉnh.

CÁC HÀM THỜI GIAN TRONG EXCEL

TIME(hour, minute, second) – trả về thời gian dưới dạng số sê ri.

TIMEVALUE(time_text) – chuyển đổi một thời gian nhập dưới dạng một chuỗi văn bản thành một số thể hiện thời gian dưới dạng số sê ri.

NOW() – trả về số tương ứng với ngày và thời gian hiện tại dạng số sê ri.

HOUR(serial_number) – chuyển đổi một số thành một giờ dạng số sê ri.

MINUTE(serial_number) – chuyển đổi một số thành phút dưới dạng số sê ri.

SECOND(serial_number) – chuyển đổi một số thành giây dưới dạng số sê ri.

ĐẾM VÀ TÍNH TỔNG CÁC Ô THEO MÀU SẮC (CHỨC NĂNG DO NGƯỜI DÙNG XÁC ĐỊNH)

GetCellColor(cell) – trả về mã màu của màu nền thuộc một ô xác định.GetCellFontColor(cell) – trả về mã màu của màu phông chữ thuộc một ô xác định.CountCellsByColor(range, color code) – đếm các ô có màu nền được chỉ định.CountCellsByFontColor(range, color code) – đếm các ô có màu phông chữ được chỉ định.SumCellsByColor(range, color code) – tính tổng của các ô có màu nền nhất định.SumCellsByFontColor(range, color code)) – trả về tổng của các ô với một màu chữ nhất định.WbkCountCellsByColor(cell) – tính các ô có màu nền được chỉ định trong toàn bộ bảng tính.WbkSumCellsByColor(cell) – tính tổng các ô với màu nền được chỉ định trong toàn bộ bảng tính.

Tất nhiên, Microsoft Excel có nhiều hàm hơn những hàm được liệt kê ở đây rất nhiều, tuy nhiên đây là những hàm vô cùng thiết yếu và phục vụ nhiều cho công việc của bạn.

Để có thể ứng dụng tốt Excel vào trong công việc, chúng ta không chỉ nắm vững được các hàm mà còn phải sử dụng tốt cả các công cụ của Excel. Những hàm nâng cao giúp áp dụng tốt vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH… Những công cụ thường sử dụng là Data validation, Conditional formatting, Pivot table…

CHÚC CÁC BẠN THÀNH CÔNG!

ĐỐI TÁC CỦA IIG VIỆT NAM TẠI MIỀN BẮC TRONG ĐÀO TẠO VÀ ĐĂNG KÝ LUYỆN THI MOS – IC3

Địa chỉ học tin văn phòng, học autocad, luyện thi mos, luyện thi IC3 uy tín nhất tại Hà Nội

TIN HỌC – KẾ TOÁN TRI THỨC VIỆT

Cơ sở 1: Số 3 Phố Dương Khuê – Mai Dịch, Cầu Giấy, Hà Nội

(gần nhà sách Tri Tuệ – ĐH Thương Mại Đường Hồ Tùng Mậu)

Cơ sở 2: Đối diện cổng chính Khu A, ĐH Công Nghiệp, Nhổn, Bắc Từ Liêm, Hà Nội

Tư vấn: 024.6652.2789 hoặc 0976.73.8989

Comments