Bài viết này mình muốn chia sẻ cho những bạn muốn học về excel từ căn bản. Công ty dịch vụ kế toán Đông Nam Á sẽ viết lại tất cả các hàm trong excel và ví dụ minh họa cho từng hàm trong phạm vi lấy bằng tin học B. Bài viết còn là một hướng dẫn để những bạn chưa biết gì về Excel có thể hiểu và thực hiện nó một cách tốt hơn. Cùng tham khảo tổng hợp các hàm trong excel từ cơ bản đến nâng cao nhé!
Tổng hợp tất cả các hàm cơ bản và một vài hàm nâng cao trong Excel
Cách dùng các hàm trong Excel cơ bản và ví dụ dùng để tính toán và đo đếm số
Mình đã tìm hiểu và tổng hợp tất cả các hàm trong excel để có thể trình bày lại sau đây những gì dễ hiểu và cần thiết nhất cho các bạn, đặc biệt là dân văn phòng. Vậy sau đây mình xin đi vào nội dung chính các hàm trong excel và ví dụ luôn nhé!
a/ Các hàm excel cơ bản và ví dụ thông dụng trong kế toán
Hàm sum
Hàm sum (hàm tính tổng) là một hàm mà bất kì ai cũng sẽ biết khi mới nhập môn excel. Đây là hàm tính tổng các số lại với nhau. Hàm có cú pháp là “=sum(giá trị 1, giá trị 2,…)”. Khi bắt đầu 1 hàm hay 1 phép tính bất kì nào, bạn phải để dấu bằng (=) phía trước thì phép tính.
Đang xem: Các hàm nâng cao trong excel 2010
Ví dụ như muốn tính tổng từ A1 tới B5 ta làm như sau: =sum(A1:B5) là ra kết quả.
Các hàm trong excel
Bạn cũng có thể làm từng số kiểu như vầy: =sum (A1,A2,A3,A4,A5,B2,B3) cũng ra kết quả tương tự.
Các hàm trong excel 2
Những hàm cơ bản kiểu giống hàm sum này thì bạn có thể áp dụng cả 2 cách trên. Nhưng để thuận tiện trong việc tính toán, cải thiện tốc độ nên nhiều người chọn làm cách 1. Vậy mình sẽ giới thiệu cách 1 cho bạn!
Hàm Count
Hàm Count là hàm đếm kí tự trong một đống những ô trống và ô chứa chữ số. Nếu bạn muốn biết trong vùng nào đó có bao nhiêu chữ số thì thay vì đếm từng ô bạn có thể dùng hàm này để tính rất nhanh. Hãy nhớ rằng hàm này là hàm đếm và đếm gì thì hãy dùng hàm này nhé!
Cú pháp hàm như sau: = Count(vùng giá trị cần đếm). Ví dụ mình muốn đếm từ vùng A1 đến B6 có bao nhiêu ô có chứa chữ số thì dùng công thức =Count(A1:B6).
Kết quả như trong hình. Bạn thấy đó, dù trong vùng giá trị có kí tự “a” nhưng vẫn không được tính. Vì hàm Count chỉ tính giá trị chữ số mà thôi.
Hàm Counta
Hàm Counta cũng là hàm đếm nhưng hàm này đếm cả số lẫn chữ la tinh. Cấu trúc nó cũng như hàm Count thôi nhé!
Các hàm trong excelHàm Countblank
Hàm Countblank cũng là hàm đếm nhưng hàm này đếm ô trống. Nó chỉ đếm mỗi ô trống thôi. Cấu trúc nó cũng như hàm Count.
Hàm Average
Hàm Average là hàm tính trung bình các số. Đây là hàm này được dùng rất nhiều trong việc tính trung bình điểm số học sinh trong các trường học. Cú pháp của hàm này là =Avarage(giá trị 1, giá trị 2,….).
Ví dụ nếu bạn muốn tính trung bình cho một học sinh với số điểm các môn thì từ B2 tới E2. Thì bạn làm như sau: =Average(B2:E2).
Bạn cần lưu ý vấn đề sau đây trong tính hàm này. Trong trường hợp yêu cầu là Toán hệ số 2 thì bạn phải làm cú pháp khác cụ thể như thế này: =Avegare(B2:E2,B2). Lúc này mới tính Toán là hệ số 2 vì như vậy hàm sẽ nhận thêm 1 giá trị nữa ở ô B2 nhé. Nếu hệ số 3 thì bạn lại thêm một giá trị B2 nữa nhé!
Hàm Min
Hàm Min cũng là một trong các hàm cơ bản trong Excel vì hàm này rất đơn giản. Nhiệm vụ của nó là lấy giá trị nhỏ nhất trong vùng giá trị mà bạn chọn. Nếu có yêu cầu tìm giá trị nhỏ nhất, chắc chắn hàm này chính là sự lựa chọn tốt nhất cho bạn.
Cú pháp của hàm là: =Min(vùng giá trị). Ví dụ nếu như bạn muốn tìm giá trị nhỏ nhất từ vùng B1 đến B7 thì cú pháp sẽ là =Min(B2:B7).
Bạn có thể thấy trong các giá trị trong vùng giá trị trên, kết quả sẽ cho ra con số nhỏ nhất.
Hàm Max
Hàm Max là hàm đi đôi với hàm Min. Ngược lại với hàm Min, hàm Max có nhiệm vụ tìm giá trị lớn nhất trong vùng dữ liệu. Nếu có yêu cầu tìm giá trị lớn nhất trong vùng dữ liệu thì bạn hãy dùng tới hàm này nhé.
Cú pháp của hàm là: =Max(vùng giá trị). Ví dụ như nếu bạn muốn tìm giá trị lớn nhất từ vùng B2 đến B7 thì cú pháp sẽ là =Max(B2:B7).
Kết quả cho ra giá trị lớn nhất trong vùng giá trị như trong hình trên.
Lưu ý rằng cả 2 hàm Min và Max chỉ nhận diện giá trị là các con số thôi nhé. Sẽ không thể tìm giá trị lớn nhất hay nhỏ nhất nếu là nó chữ La tinh được nhé bạn.
5 hàm trên là 5 hàm tính toán dính đến những con số. Các hàm này rất cơ bản và bạn hãy nắm rõ nó để sắp tới chúng ta đi sâu vào những hàm nâng cao của các hàm đó nhé.
Hàm Subtotal
Hàm Subtotal là hàm tổng quát của các hàm trên. Nhưng có một vấn đề là phải chọn số cấu trúc nên ít khi được nhiều người dạy. Nhưng hàm Subtotal này lại được ứng dụng nhiều trong văn phòng vì tính tiện lợi của nó. Trong kế toán, hàm này dùng để tính tổng phát sinh trong kì hay tính tổng tiền tồn cuối ngày.
Trước tiên là cấu trúc của hàm: =Subtotal(số cấu trúc tính toán, vùng tính toán).
Với số cấu trúc tính toán, bạn có thể tham khảo bảng sau để rõ về nó:
|
Số cấu trúc tính toán |
Hàm tính toán thực sự | |
| Tính cả các giá trị gồm cả giá trị ẩn |
Tính cả các giá trị nhưng không tính giá trị ẩn |
|
| 1 | 101 | AVERAGE |
| 2 | 102 | COUNT |
| 3 | 103 | COUNTA |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 6 | 106 | PRODUCT |
| 7 | 107 | STDEV |
| 8 | 108 | STDEVP |
| 9 | 109 | SUM |
| 10 | 110 | VAR |
| 11 | 111 | VARP |
Hàm Subtotal khác gì hàm Sum?
Việc dùng hàm Subtotal có thể dùng cả hàm Sum lẫn hàm Count, CountA, Average,… tức là nó bao gồm nhiều hàm chứ không như hàm Sum chỉ dùng để tính tổng. Bảng trên đã cho bạn thấy rõ sự khác nhau của 2 hàm này.
Để hiểu rõ hàm hơn, các bạn có thể xem ví dụ sau đây là rõ. Ví dụ về mèo gà heo chó và thức ăn của nó, bạn có thể tính thành tiền bằng công thức sau: =SUBTOTAL(9,C2:C8). Trong đó 9 là hàm Sum như trong bảng trên nhé! Mình dùng số 9 này để tính tổng. Và nếu bạn cần đếm thì có thể dùng số 2 hoặc 3 tùy thích nhé!
Lưu ý Hàm Subtotal này không tính các vùng tính toán mà người dùng chọn lồng nhau. Những vùng lồng nhau hoặc đã tính rồi thì hàm sẽ bỏ qua.Khi có các giá trị ẩn bởi lệnh Filter, hàm này sẽ không tính dữ liệu đó.Hàm chỉ tính theo chiều dọc không tính theo chiều ngang nên bạn nhớ lưu ý.Hàm chỉ tính trong một trang (Sheet). Nếu qua trang khác thì nó sẽ báo lỗi đó nhé.Một lưu ý mà bạn cần phải “thực sự quan tâm”. Đó là nếu con số của bạn “bị” chuyển qua dạng “Text” thì bạn hãy chuyển nó lại thành dạng “Number” hoặc “General” để chuyển nó lại thành con số nhé! Nếu con số đó dạng “Text” thì nó sẽ nhảy qua phía bên trái đấy!
b/ Tổng hợp các hàm excel nâng cao và ví dụ
Hàm Sumif
Hàm Sumif là hàm tính tổng nhưng là tính tổng có điều kiện kèm theo. Nếu có yêu cầu tính tổng mà đi kèm với một điều kiện nào đó. Nhưng chỉ có một điều kiện thôi thì bạn sử dụng hàm sumif này. Đây là một trong các hàm excel nâng cao được dùng nhiều nhất
Cú pháp của hàm này như sau: =Sumif(phạm vi điều kiện, điều kiện, vùng tính tổng). Trong trường hợp phạm vi điều kiện và vùng tính tổng trùng nhau thì cú pháp nó sẽ đơn giản hơn là =Sumif(phạm vi điều kiện, điều kiện).
Mình sẽ lấy ví dụ đầu tiên đó là khi phạm vi điều kiện và vùng tính tổng trùng nhau. Nếu bạn có một vùng điều kiện để tính từ B2 đến B7 và điều kiện tính của bạn là các giá trị đó phải >20. Thì cú pháp của hàm này sẽ là =Sumif(B2:B7,”>20”). Và kết quả như hình dưới.
Còn nếu vùng điều kiện của bạn không trùng với vùng tính thì mình làm như sau. Ví dụ bạn có một hóa đơn tiền thức ăn cho chó mèo gà vịt và bạn muốn tính hóa đơn thức ăn của chó thì bạn sẽ làm thế này. Phạm vi điều kiện chính là vùng chó mèo gà vịt từ cột A2 đến A7, điều kiện mình muốn tính đó là chỉ tính thức ăn của chó thôi và vùng tính tổng là lượng tiền mà các động vật đã ăn từ cột B2 đến B7. Vậy cú pháp sẽ là =Sumif(A2:A7,”chó”,B2:B7).
Một lưu ý quan trọng là phần điều kiện bạn phải luôn để nó trong ngoặc kép (“ điều kiện“) nhé! Nếu không Excel sẽ báo lỗi và bạn không thể nào thực hiện được phép tính đó.
Hàm Sumifs
Hàm Sumifs cũng gần giống như hàm Sumif là một hàm tính tổng có điều kiện. Nhưng Sumifs là làm tính tổng nhiều điều kiện, khác với sumif chỉ tính tổng một điều kiện mà thôi. Đây cũng là một trong các hàm nâng cao trong excel được dùng khá nhiều.
Cú pháp của hàm này như sau: =Sumifs(vùng cần tính tổng, vùng điều kiện 1, điều kiện 1, vùng điều kiện 2, điều kiện 2, vùng điều kiện 3, điều kiện 3….). Vì đây là hàm tính tổng nhiều điều kiện nên bạn có thể thêm vùng điều kiện và điều kiện vào hàm để phù hợp với yêu cầu nhé!
Sau đây mình sẽ lấy ví dụ về 2 điều kiện thôi. Vẫn lấy ví dụ về thức ăn cho chó mèo lợn gà những có thêm cột số lượng. Nếu yêu cầu đề là tính thành tiền của thức ăn chó với số lượng là >3 thì bài này có 2 điều kiện là phải là “chó” và số lượng phải “>3”. Cú pháp sẽ là như sau: =SUMIFS(C2:C8,A2:A8,”chó”,B2:B8,”>3″).
Như các bạn thấy kết quả sẽ ra như trong hình trên.
Hàm Sumproduct
Hàm Sumproduct là hàm đơn giản. Nó chỉ là hàm tính tổng các sản phẩm của bạn mà thôi. Cú pháp của hàm là =Sumproduct(Vùng muốn nhân và tính tổng, vùng đối số muốn nhân và tính tổng 1, vùng đối số muốn nhân và tính tổng 2,….). Để hiểu hàm này hơn mình nghĩ nên lấy ví dụ để nói cho bạn mới rõ được.
Như trong hình cho ra kết quả là 1020. Có kết quả này là vì hàm này tính bằng cách lấy từ số ở cột số lượng x cột đơn giá đối ứng rồi cộng lại. Tức là 3×15 + 4×30 + 6×50 + 6×25 + 5×45 + 2×70 + 2×20 =1020. Và hàm này tính tổng theo cách đó. Nó được dùng nhiều trong kế toán hơn là trong học tập bạn nhé!
Hàm Countif
Hàm Countif cũng giống như hàm Sumif và hàm Count là vẫn là đếm giá trị nhưng đếm này có điều kiện (1 điều kiện). Ở phần Sumif và Sumifs mình đã chia sẻ rất kỹ rồi nên sang phần này và phần hàm Countifs tiếp theo mình sẽ nói sơ qua thôi vì nó tương tự nhau bạn nhé!
Cú pháp hàm Countif như sau: =Countif(vùng đếm, điều kiện). Ví dụ như để đếm từ ô C2 đến ô C8 với điều kiện là >30 thì ta có cú pháp =COUNTIF(C2:C8,”>30″).
Hàm Countifs
Hàm Countifs là hàm đếm với nhiều điều kiện. Cũng giống như sumifs, hàm này cũng có thể đếm với nhiều điều kiện khác nhau. Cú pháp của hàm là =Countifs(Vùng cần đếm 1, điều kiện 1, vùng cần đếm 2, điều kiện 2,…).
Ví dụ nếu mình muốn đếm cột thành tiền có điều kiện là >25 và điều kiện kèm là cột số lượng đó phải >3 thì mới đếm thì ta có cú pháp như sau: =COUNTIFS(C2:C8,”>25″,B2:B8,”>3″).
Một lưu ý mà bạn cần quan tâm đó là số ô ở ô mà bạn muốn đếm (ở ví dụ này là ô Thành Tiền) phải trùng với số ô ở điều kiện kèm theo ( mà ở đây là ô Số lượng), tức là trùng 7 ô. Là các điều kiện không nhất thiết phải trùng kế bên nhau như mình đã cho ví dụ nha.
Cấu trúc các hàm trong Excel (2010) về ngày tháng năm giờ phút giây
Cấu trúc các hàm trong Excel dạng này khá đơn giản nhưng lại dễ sai. Trước khi làm phần này các bạn cần chú ý những điều sau:
Nếu điền ngày tháng, các bạn hãy dùng dấu “/” hoặc dấu “-“ để phân biệt ngày tháng năm.Nếu điền thời gian thì bạn hãy sử dụng dấu “:”.Để chỉnh sửa các dấu này hoặc bạn muốn chỉnh lại theo ý mình thì mình sẽ hướng dẫn bạn luôn và chính bạn tự thống nhất kí hiệu cho mình để tránh bị sai về vấn đề đồng nhất trong quá trình làm Excel nhé!
Vào Start chọn Control Panel. Chổ “View by” chọn lại “Large icon” (Nếu bạn đã chọn rồi thì thôi không cần làm nhé). Sau đó bạn vào phần “Region and Language”.
Ở phần Short day, Long day, short time và long time bạn có thể chỉnh lại theo ý mình. Riêng mình thì mình thích chọn kiểu dd/mm/yyyy hoặc chọn mặc định như trong hình cũng được. Vì như vậy sẽ thuận tay hơn.
Sau khi chỉnh xong ta bước vào phần chính là các hàm và ví dụ nhé!
Hàm Year, Month và Day
Ba hàm này đều có chung một cú pháp là =Year(ngày tháng năm), =Month(ngày tháng năm) hay =Day(ngày tháng năm). Những hàm nay cơ bản là dùng để tách lấy ngày, tháng hoặc năm ra từ trong một chuỗi ngày tháng năm mà thôi.
Thực đơn giản phải không nào!
Hàm Date
Hàm Date là hàm mà giúp bạn có thể thêm số ngày, tháng hoặc là năm. Theo mình thấy hàm này cũng ít dùng nhưng nếu dùng thì nó hay dùng trong các nghiệp vụ phát sinh gia hạn ngày nọ trong báo cáo. Cơ bản tác dụng của nó là thêm ngày tháng năm thôi. Cú pháp của hàm là =date(năm, tháng,ngày). Xem ví dụ minh họa cho dể hiểu nhé!
Bạn xem kết quả ở hình trên nhé!
Hàm Days360
Hàm Days360 là hàm lấy số ngày giữa 2 mốc thời gian với nhau. Cấu trúc của hàm cũng đơn giản =Days360(mốc thời gian 1, mốc thời gian 2).
Và kết quả như hình trên. Nếu không tin các bạn có thể tự nhẩm xem đúng hay không nhé!
Hàm Hour, Minute và Second
Để có kết quả trả về là giờ, phút hoặc giây, bạn có thể dùng hàm Hour, Minute hoặc Second. Cấu trúc 3 hàm này tương tự như nhau là =Hour(thời gian), Minute(thời gian) và Second(thời gian).
1 ví dụ minh họa là bạn sẽ thấy nó dễ dàng như thế nào nhé. Mình lấy 1 mốc thời gian là 5:15:59 tức là 5 giờ 15 phút 59 giây thì dùng 3 hàm trên có thể tách ra kết quả như sau.
Hàm Time
Hàm Time nó tựa tựa như hàm Date vậy. Dùng để thêm giờ phút giây cho mốc thời gian ban đầu. Cú pháp của nó là =Time(giờ,phút,giây).
Ví dụ như vẫn mốc thời gian là 5 giờ 15 phút 59 giây ta muốn thêm vào đó 2 giờ 30 phút và 21 giây thì dùng cú pháp sau: =TIME(HOUR(B2)+2,MINUTE(B2)+30,SECOND(B2)+21).
Và kết quả sẽ là như trong hình. Lưu ý rằng nếu đủ 60s thì sẽ tính 1p vào số phút và tương tự nhé.
Tổng hợp tất cả các hàm trong Excel mang tính logic và ví dụ
Phần tổng hợp tất cả các hàm trong excel mà mang tính logic này không có phần nâng cao. Nhưng đây là những hàm mà bạn dùng rất nhiều trong khi làm Excel nên các bạn hãy chú ý nhé.
Hàm If
Nếu mình có 1 con số là 70 là số kí của một người cao 1m7 thì nếu ai 70 kí hoặc lớn hơn thì là người mập, còn thấp hơn 70 kí thì là người ốm thì mình sẽ dùng cú pháp sau để tính: =IF(A1>=70,”Mập”,”Ốm”).
Các bạn thấy đó, nếu mình để 65 thì theo cú pháp này kết quả sẽ đưa ra là “Ốm” vì nó nhỏ hơn 70 kí.
Hàm If lồng nhau
Hàm IF tuy được dùng nhiều nhưng ít khi nó đi một mình như ví dụ trên cả mà thường là sẽ những chuỗi các hàm IF lồng nhau tạo nên một sự logic nhất định.
Thường thì các bạn sẽ gặp các bài toán kiểu như cho điểm TB học kì của các học sinh và bạn hãy phân loại nếu >=9 thì là loại Giỏi, nếu >=7.5 thì là loại Khá còn lại là loại Yếu chẳng hạn. Gặp trường hợp này bạn phải làm như sau: =IF(B2>=9,”Giỏi”,IF(B2>=7.5,”Khá”,”TB”)).
Với bài trên mình đã dùng 2 hàm IF lồng nhau để tìm ra kết quả. Các bạn có thể áp dụng nhiều hàm IF lồng nhau nữa để phù hợp với bài toán nhé!
Hàm Or
Hàm Or là hàm trả về kết quả đúng hoặc sai (TRUE hoặc FALSE). Cú pháp của hàm là =OR(điều kiện 1, điều kiện 2). Một lưu ý khi dùng hàm này là nếu cả 2 điều kiện này có một cái đúng thì OR vẫn trả về kết quả đúng (TRUE). Chỉ khi nào cả 2 điều kiện đều sai mới trả về kết quả FALSE thôi nhé!
Cũng lấy ví dụ về điểm trung bình lúc nãy như dùng làm OR để lấy giá trị. Ta sẽ cho điều kiện TB của học sinh A >10 và TB của học sinh B 10,B3Hàm Value
Như đã nói tác dụng của hàm là chuyển chữ thành số nên cú pháp nó cũng đơn giản. Cú pháp là =Value(kí tự). Như thế là chuyển chữ thành số rồi nhé! Bây giờ quay lại ví dụ về hàm lồng nhau Index và Match thôi nào.
Ví dụ
Các bạn xem qua hình nhé!
Bây giờ chúng ta thấy ta có cột mã hàng từ A2 đến A5. Bảng màu đỏ là bảng tham chiếu mã hàng với đơn hàng. Các số 0,1,2 là những số thứ 3 ở cột mã hàng màu cam. Và bây giờ chúng ta phải lấy đơn giá từ bảng màu đỏ tương ứng với bảng màu cam.
Với hàm Index(bảng tham chiếu, dòng, cột) thì bảng tham chiếu ta lấy ô màu nâu. Dòng và cột thì trích từ bảng màu cam đưa xuống bằng cú pháp Match.
Sử dụng các cú pháp
Để trích dẫn dòng ta phải dùng hàm Match như sau: =MATCH(LEFT(A2,2),$C$9:$C$11,0).
Để trích dẫn cột ta phải dùng hàm Match như sau: = MATCH(VALUE(MID(A2,3,1)),$D$8:$F$8,0). Như các bạn thấy, mình phải dùng hàm Value để chuyển kí tự thứ 3 trong bảng màu cam mà cụ thể là ô A2 từ kí tự “1” sang số “1”.
Xem thêm: Hướng Dẫn Sử Dụng Phần Mềm Gimp Cho Người Mới Bắt Đầu: Học Những Điều Cơ Bản
Sau khi đã biết cách lấy dòng và cột ta được cú pháp lồng nhau của 2 hàm: =INDEX($D$9:$F$11,MATCH(LEFT(A2,2),$C$9:$C$11,0),MATCH(VALUE(MID(A2,3,1)),$D$8:$F$8,0)).
Và kết quả ra 40. Bạn nhìn vào cũng thấy đúng phải không nè!
Trên đây là tổng hợp tất cả các hàm trong Excel và ví dụ cụ thể mà có thể giúp cho những bạn chưa biết về Excel hay những bạn học kế toán, làm kế toán tham khảo. Còn nhiều hàm nữa và nếu rảnh mình sẽ tiếp tục tổng hợp các hàm trong excel để các bạn tham khảo nhé! Hãy bookmark bài viết để tiện theo dõi bạn nhé!