các hàm trong excel và ví dụ

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

>>> Kế toán bán hàng là gì?

>>> Kế toán doanh thu là gì?

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 trong excel và ví dụ

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

1101AVERAGE2102COUNT3103COUNTA4104MAX5105MIN6106PRODUCT7107STDEV8108STDEVP9109SUM10110VAR11111VARPHà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!

>>> Nghiệp vụ kế toán sản xuất cần nắm

>>> Kế toán thanh toán là gì?

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.

>>> Kế toán xuất nhập khẩu làm gì?

>>> Kế toán tài chính là gì?

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.

Ví dụ như trong ô của mình có chứa ngày tháng là 4/11/2018. Tức là ngày 11 tháng 4 năm 2018 (vì phần short time mình chọn kiểu M/d/YYYY) thì khi dùng 3 hàm trên mình tách ra được ngày tháng năm như hình bên dướ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é!

Vẫn là mình có thời gian là 4/11/2018 là ngày 11 tháng 4 năm 2018. Giờ mình muốn thêm 5 ngày 4 tháng và 2 năm nữa vào giá trị hiện tại kia thì mình dùng hàm Date. Và mình sẽ làm với cú pháp là =DATE(YEAR(B2)+2,MONTH(B2)+4,DAY(B2)+5).Tác dụng của nó thế thôi!

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í dụ nếu bạn muốn đếm số ngày từ 4/11/2018 đến 5/20/2018 thì bạn làm cú pháp sau: =DAYS360(B2,B3).

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).

Xem thêm: Đồ Án Chi Tiết Máy Đề Đồ Án Chi Tiết Máy Toàn Tập, Hướng Dẫn Đồ Án Chi Tiết Máy

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é.

>>> Kế toán xây dựng làm gì?

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

Hàm If là một hàm mà chắc hẳn các bạn sẽ đụng tới nó rất nhiều. Đây là hàm xét điều kiện nếu đúng thì nó trả về một kết quả A mà sai thì trả về một kết quả B. Công thức của nó cũng khá đơn giản =IF(điều kiện, kết quả nếu đúng, kết quả nếu sai). Để minh họa cho hàm này dể hiểu hơn bạn có thể xem thêm ví dụ sau đây.

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 <7 thì kết quả nó sẽ trả về là TRUE vì điều kiện 2 đúng. Cú pháp của hàm sẽ là =OR(B2>10,B3<7).

Hàm And

Cấu trúc hàm AND tương tự như hàm OR cũng là cú pháp =AND (điều kiện 1, điều kiện 2) nhưng cách trả về kết quả ở đây có khác hàm OR. Nó chỉ trả về kết quả là đúng (TRUE) khi cả 2 điều kiện là đúng hết. Dù chỉ 1 điều kiện sai cũng trả về kết quả là sai (FALSE) bạn nhé.

Ví dụ minh họa đơn giản bạn có thể xem hình là hiểu!

Các hàm liên quan đến văn bản và chuỗi văn bản

Hàm Left

Là hàm giúp lấy chuỗi ký tự từ bên trái. Đơn giản là tách chuỗi văn bản ra và lấy kí tự theo yêu cầu từ trái qua phải thôi. Cú pháp của hàm này là =LEFT(chuỗi kí tự, số kí tự cần lấy từ trái qua).

Ví dụ nếu mình có chuỗi ký tự là “smile” và muốn lấy 3 ký tự “smi” thì cú pháp sẽ là =LEFT(A1,3) như hình sau:

Hàm Mid

Hàm Mid lấy ký tự ở giữa chuổi nên có cú pháp phức tạp hơn chút nhưng cũng đơn giản. Cú pháp =MID(chuỗi ký tự, vị trí bắt đầu lấy kí tự, số kí tự cần lấy).

Cũng với ví dụ là từ smile nếu muốn lấy chữ mil thì bạn dùng cú pháp sau: =MID(A1,2,3).

Hàm Right

Hàm Right cũng giống hàm Left nhưng nó lấy kí tự từ phía bên phải. Cú pháp cũng nó cũng tương tự như là hàm Left: =RIGHT(chuỗi kí tự, số kí tự cần lấy từ phải qua).

Cũng ví dụ với chữ smile mình muốn mấy chữ le thì làm như sau: =RIGHT(A1,2).

Cách nối chuỗi không dùng hàm

Để nối các chuỗi với nhau mà không phải dùng đến hàm, bạn có thể dùng dấu “&” nhé. Ví dụ như mình muốn nối 2 chữ là Kế Toán và Đông Nam Á thành Kế Toán Đông Nam Á thì mình sẽ làm như cú pháp sau: =A1& ” “&B1.

Như vậy là có thể nối được. Chú ý khi nối bạn nên thêm 1 khoảng là “ “ & để có khoảng cách giữa 2 từ nối nhé!

Các hàm Excel nâng cao về trích dẫn và tham chiếu

Các hàm Excel nâng cao này là những hàm trích dẫn và tham chiếu nâng cao. Bạn sẽ hơi khó hiểu trong cấu trúc cũng như việc ứng dụng sẽ có thể mắc vài lỗi. Vì vậy hãy áp dụng theo đúng những gì mà mình hướng dẫn sau đây nhé.

Hàm Hlookup

Hàm Hlookup là hàm giúp bạn dễ dàng tham chiếu và tìm kiếm theo các “dòng của các cột.” Nếu thỏa mãn điều kiện thì nó sẽ trả kết quả về. Vì là hàm excel nâng cao nên khá là khó xài.

Cú pháp của nó là =Hlookup(giá trị cần tìm ở hàng thứ 1, bảng tra để tham chiếu và trích dẫn dữ liệu, số hàng trong bảng mà bạn muốn lấy kết quả trả về, chọn giá trị đúng (TRUE) hoặc gần đúng (FALSE)).

Ví dụ như mình có một bảng kí hiệu gồm 3 số từ 1 đến 3 và ứng với nó là các con vật chó, mèo hoặc heo. Vậy làm thế nào để trích xuất nó vô bảng giá trị. Cú pháp của hàm sẽ là =HLOOKUP(A2,$G$4:$I$5,2,TRUE). Hãy xem hình để hiểu rõ hơn nhé!

Các bạn thấy đó, hàm này sẽ đối chiều theo dòng và lấy các giá trị trích dẫn về bảng. Một lưu ý là vì bạn làm 1 dòng và copy nó nên ở phần bảng tham chiếu sau khi chọn xong bạn hãy nhấn F4 để cố định bảng lại nhé. Vì nhấn F4 nên cú pháp hàm có dạng $G$4:$I$5 như cú pháp ở trên nha bạn!

Hàm Vlookup

Hàm Vlookup là cũng giống như hàm Hlookup, là một hàm excel nâng cao có chức năng gần tương tự. Đây là hàm giúp bạn dễ dàng tham chiếu và tìm kiếm theo các “cột theo các dòng”. Nếu thỏa mãn điều kiện thì nó sẽ trả kết quả về.

Cú pháp của nó là =Vlookup(giá trị cần tìm ở hàng thứ 1, bảng tra để tham chiếu và trích dẫn dữ liệu, số cột trong bảng mà bạn muốn lấy kết quả trả về, chọn giá trị đúng (TRUE) hoặc gần đúng (FALSE)).

Cũng sẽ lấy ví dụ như ở Hlookup nhưng lúc này bảng tham chiếu thay đổi từ dòng sang cột. Để tham chiếu và trích dẫn các giá trị về ta làm cú pháp như sau: =VLOOKUP(A2,$G$4:$H$6,2,TRUE).

Và kết quả như trong hình. Bạn thấy đó, bảng trích dẫn lúc này chuyển sang dạng cột. Vì vậy, khi trích dẫn bạn hãy chú ý xem bảng trích dẫn tham chiếu giá trị này là theo dòng hay theo cột để chọn ra hàm cần làm nhé!

Hàm Match

Hàm Match này chức năng của nó là trả về cho bạn một “vị trí” của một giá trị trong một dãy nhất định. Hãy nhớ rằng nó trả về vị trí bạn nhé. Vì sắp tới mình sẽ hướng dẫn bạn cách sử dụng tốt hàm Match này.

Cú pháp hàm này là: =Match(Giá trị muốn lấy vị trí, bảng tham chiếu để lấy vị trí, giá trị chính xác hay gần đúng). Ở phần giá trị chính xác hay gần đúng bạn có thể chọn 1,0 hoặc -1 nhé. Thường mọi người chọn 0 vì 0 lấy kết quả chính xác nhất.

Ví dụ cho hàm này cũng đơn giản. Bạn có giá trị là con chó và bạn muốn tra trong bảng tham chiếu nó nằm ở số mấy thì cú pháp nó sẽ là =MATCH(A2,H4:H6,0).

Như các bạn thấy, giá trị trong bảng với chó là 1 vì mình để nó ở đầu bảng tham chiếu mà.

Hàm Index

Hàm Index này lấy giá trị trong phạm vi 2 chiều bằng cách lấy vị trí số hàng và cột để đem về một giá trị tương ứng với hàng và cột đó. Về cơ bản hàm này giống Vlookup nhưng nó có thể đa dạng và linh động hơn hàm Vlookup rất nhiều.

Cú pháp của hàm là =Index(bảng tham chiếu, hàng để lấy giá trị, cột để lấy giá trị). Để hiểu rõ về hàm này ta cùng xem qua ví dụ nhé!

Như trong hình ta thấy có nhiều loài động vật và mình muốn lấy con vịt ra trong bảng tham chiếu đó. Vịt trong bảng bị giới hạn bởi ô đỏ mình bôi thì nó nằm ở dòng 2 và cột 2, nên mình sẽ lấy nó với cú pháp =INDEX(D3:E5,2,2). Thật đơn giản phải không nào.

Nhưng hàm Match và hàm Index không chỉ là như vậy. Trong tính toán ít khi 2 hàm này làm như mình đã ví dụ mà thường 2 hàm đó sẽ đi kèm với nhau. Người ta gọi nó là hàm Index và hàm Match lồng nhau. Hãy xem tiếp ở dưới nhé!

Hàm Match kết hợp với hàm Index

Hàm Index và Match lồng nhau sẽ có tác dụng thay thế cho các hàm như Hlookup hay Vlookup. Những hàm Hlookup hay Vlookup có giới hạn là nó chỉ làm trong một bảng tham chiếu duy nhất và “hơi khuôn khổ”. Nhưng với hàm Match và Index thì lại khác. Hai hàm này khi kết hợp nó có thể tham chiếu và trích dẫn bất cứ giá trị nào trong bất cứ bảng tham chiếu nào. Trong tất cả các hàm nâng cao trong excel thì đây là hàm rất khó sử dụng. Xem kỹ hướng dẫn phía dưới để sử dụng chính xác nhé.

Nhưng trước tiên mình muốn các bạn biết về một hàm nữa, đó là hàm Value. Hàm này có tác dụng chuyển chữ thành số. Ví dụ sau nếu không làm hàm này chắc chắn bạn sẽ bị báo lỗi, đặc biệt là phiên bản Excel 2010 trở đi.

Hà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: Cách Tính Quẻ Biến Số Điện Thoại Và Ứng Dụng Cho Từng Mục Đích Công Việc

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é!

Chúc bạn thành công và hẹn gặp lại!

>>> Hướng dẫn cách tẩy mức dấu đỏ trên hóa đơn chuẩn nhất

>>> Điều kiện thi chứng chỉ hành nghề kế toán kế toán thuế nên biết