[Case Study] Tất cả các hàm trong excel và ví dụ minh họa

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

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 đã khám phá và tổng hợp tổng thể các hàm trong excel để hoàn toàn có thể trình diễn lại sau đây những gì dễ hiểu và thiết yếu nhất cho các bạn, đặc biệt quan trọng 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 mở màn 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 .

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

Bạn cũng hoàn toàn có thể làm từng số kiểu như vầy : = sum ( A1, A2, A3, A4, A5, B2, B3 ) cũng ra hiệu quả tựa như .Những hàm cơ bản kiểu giống hàm sum này thì bạn hoàn toàn có thể vận dụng cả 2 cách trên. Nhưng để thuận tiện trong việc giám sát, cải tổ vận tốc nên nhiều người chọn làm cách 1. Vậy mình sẽ ra mắt 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 hoàn toà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é !

Hà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 viên 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 viên 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 chú ý quan tâm yếu tố sau đây trong tính hàm này. Trong trường hợp nhu yếu là Toán thông số 2 thì bạn phải làm cú pháp khác đơn cử như thế này : = Avegare ( B2 : E2, B2 ). Lúc này mới tính Toán là thông số 2 vì như vậy hàm sẽ nhận thêm 1 giá trị nữa ở ô B2 nhé. Nếu thông 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 thuầ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ó nhu yếu tìm giá trị nhỏ nhất, chắc như đinh 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 hoàn toàn có thể thấy trong các giá trị trong vùng giá trị trên, tác dụng sẽ cho ra số lượng nhỏ nhất .

Hàm Max

Hàm Max là hàm song song với hàm Min. trái lại với hàm Min, hàm Max có trách nhiệm tìm giá trị lớn nhất trong vùng tài liệu. Nếu có nhu yếu tìm giá trị lớn nhất trong vùng tài 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 số lượng thôi nhé. Sẽ không hề 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 giám sát dính đến những số lượng. Các hàm này rất cơ bản và bạn hãy nắm rõ nó để sắp tới tất cả 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 yếu tố 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 thuận tiện 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 đo lường và thống kê, vùng đo lường và thống kê ) .Với số cấu trúc thống kê giám sát, bạn hoàn toàn có thể tìm hiểu thêm 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

1101AVERAGE2102COUNT3103COUNTA4104MAX5105MIN6106PRODUCT7107STDEV8108STDEVP9109SUM10110VAR11111VARP

Hàm Subtotal khác gì hàm Sum?

Việc dùng hàm Subtotal hoàn toàn có thể dùng cả hàm Sum lẫn hàm Count, CountA, Average, … tức là nó gồm có 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 hoàn toà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 hoàn toà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ì hoàn toàn 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 kèm theo. Nếu có nhu yếu tính tổng mà đi kèm với một điều kiện kèm theo nào đó. Nhưng chỉ có một điều kiện kèm theo 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ấtCú pháp của hàm này như sau : = Sumif ( khoanh vùng phạm vi điều kiện kèm theo, điều kiện kèm theo, vùng tính tổng ). Trong trường hợp khoanh vùng phạm vi điều kiện kèm theo và vùng tính tổng trùng nhau thì cú pháp nó sẽ đơn thuần hơn là = Sumif ( khoanh vùng phạm vi điều kiện kèm theo, điều kiện kèm theo ) .Mình sẽ lấy ví dụ tiên phong đó là khi khoanh vùng phạm vi điều kiện kèm theo và vùng tính tổng trùng nhau. Nếu bạn có một vùng điều kiện kèm theo để tính từ B2 đến B7 và điều kiện kèm theo 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à hiệu quả như hình dưới .

Còn nếu vùng điều kiện kèm theo 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 kèm theo chính là vùng chó mèo gà vịt từ cột A2 đến A7, điều kiện kèm theo 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 hoang dã đã ăn từ cột B2 đến B7. Vậy cú pháp sẽ là = Sumif ( A2 : A7, ” chó ”, B2 : B7 ) .

Một chú ý quan tâm quan trọng là phần điều kiện kèm theo bạn phải luôn để nó trong ngoặc kép ( “ điều kiện kèm theo “ ) nhé ! Nếu không Excel sẽ báo lỗi và bạn không thể nào thực thi đượ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 kèm theo. Nhưng Sumifs là làm tính tổng nhiều điều kiện kèm theo, khác với sumif chỉ tính tổng một điều kiện kèm theo 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 kèm theo 1, điều kiện kèm theo 1, vùng điều kiện kèm theo 2, điều kiện kèm theo 2, vùng điều kiện kèm theo 3, điều kiện kèm theo 3 …. ). Vì đây là hàm tính tổng nhiều điều kiện kèm theo nên bạn hoàn toàn có thể thêm vùng điều kiện kèm theo và điều kiện kèm theo vào hàm để tương thích với nhu yếu nhé !Sau đây mình sẽ lấy ví dụ về 2 điều kiện kèm theo 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 nhu yế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 kèm theo 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 tác dụng sẽ ra như trong hình trên .

Hàm Sumproduct

Hàm Sumproduct là hàm đơn thuần. Nó chỉ là hàm tính tổng các mẫu 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 hiệu quả là 1020. Có hiệu 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 kèm theo ( 1 điều kiện kèm theo ). Ở phần Sumif và Sumifs mình đã san 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ự như nhau bạn nhé !Cú pháp hàm Countif như sau : = Countif ( vùng đếm, điều kiện kèm theo ). Ví dụ như để đếm từ ô C2 đến ô C8 với điều kiện kèm theo 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 kèm theo. Cũng giống như sumifs, hàm này cũng hoàn toàn có thể đếm với nhiều điều kiện kèm theo khác nhau. Cú pháp của hàm là = Countifs ( Vùng cần đếm 1, điều kiện kèm theo 1, vùng cần đếm 2, điều kiện kèm theo 2, … ) .Ví dụ nếu mình muốn đếm cột thành tiền có điều kiện kèm theo là > 25 và điều kiện kèm theo 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 chú ý quan tâm mà bạn cần chăm sóc đó 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 kèm theo ( mà ở đây là ô Số lượng ), tức là trùng 7 ô. Là các điều kiện kèm theo 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 thuần nhưng lại dễ sai. Trước khi làm phần này các bạn cần quan tâm 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 hoàn toà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 hoàn toà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 nhiệm vụ phát sinh gia hạn ngày nọ trong báo cáo giải trình. Cơ bản công 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 hạn 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 tác dụng ở 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 hạn với nhau. Cấu trúc của hàm cũng đơn thuần = Days360 ( mốc thời hạn 1, mốc thời hạn 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à hiệu quả như hình trên. Nếu không tin các bạn hoàn toàn có thể tự nhẩm xem đúng hay không nhé !

Hàm Hour, Minute và Second

Để có tác dụng trả về là giờ, phút hoặc giây, bạn hoàn toà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ư như nhau là = Hour ( thời hạn ), Minute ( thời hạn ) và Second ( thời hạn ) .1 ví dụ minh họa là bạn sẽ thấy nó thuận tiện như thế nào nhé. Mình lấy 1 mốc thời hạn là 5 : 15 : 59 tức là 5 giờ 15 phút 59 giây thì dùng 3 hàm trên hoàn toàn có thể tách ra tác dụng 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 hạn khởi đầu. Cú pháp của nó là = Time ( giờ, phút, giây ) .Ví dụ như vẫn mốc thời hạn 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à hiệu quả sẽ là như trong hình. Lưu ý rằng nếu đủ 60 s thì sẽ tính 1 p vào số phút và tương tự như nhé .

>> > Kế toán thiết kế 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 toàn bộ 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 quan tâm nhé .

Hàm If

Hàm If là một hàm mà chắc rằng các bạn sẽ đụng tới nó rất nhiều. Đây là hàm xét điều kiện kèm theo nếu đúng thì nó trả về một tác dụng A mà sai thì trả về một tác dụng B. Công thức của nó cũng khá đơn thuần = IF ( điều kiện kèm theo, hiệu quả nếu đúng, tác dụng nếu sai ). Để minh họa cho hàm này dể hiểu hơn bạn hoàn toàn có thể xem thêm ví dụ sau đây .Nếu mình có 1 số lượng là 70 là số kí của một người cao 1 m7 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 hiệu 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 viên 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 ví dụ điển hình. 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 hiệu quả. Các bạn hoàn toàn có thể vận dụng nhiều hàm IF lồng nhau nữa để tương thích với bài toán nhé !

Hàm Or

Hàm Or là hàm trả về tác dụng đúng hoặc sai ( TRUE hoặc FALSE ). Cú pháp của hàm là = OR ( điều kiện kèm theo 1, điều kiện kèm theo 2 ). Một quan tâm khi dùng hàm này là nếu cả 2 điều kiện kèm theo này có một cái đúng thì OR vẫn trả về tác dụng đúng ( TRUE ). Chỉ khi nào cả 2 điều kiện kèm theo đều sai mới trả về hiệu 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 kèm theo TB của học viên A > 10 và TB của học viên B < 7 thì hiệu quả nó sẽ trả về là TRUE vì điều kiện kèm theo 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ựa như như hàm OR cũng là cú pháp = AND ( điều kiện kèm theo 1, điều kiện kèm theo 2 ) nhưng cách trả về tác dụng ở đây có khác hàm OR. Nó chỉ trả về tác dụng là đúng ( TRUE ) khi cả 2 điều kiện kèm theo là đúng hết. Dù chỉ 1 điều kiện kèm theo sai cũng trả về hiệu quả là sai ( FALSE ) bạn nhé .Ví dụ minh họa đơn thuần bạn hoàn toà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 nhu yế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 thuần. Cú pháp = MID ( chuỗi ký tự, vị trí khởi đầ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ựa như 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 hoàn toàn có thể dùng dấu “ và ” nhé. Ví dụ như mình muốn nối 2 chữ là Kế Toán và Khu vực Đông Nam Á thành Kế Toán Khu vực Đông Nam Á thì mình sẽ làm như cú pháp sau : = A1 và ” “ và B1 .

Như vậy là hoàn toàn có thể nối được. Chú ý khi nối bạn nên thêm 1 khoảng chừng là “ “ và để 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ẽ hoàn toàn có thể mắc vài lỗi. Vì vậy hãy vận 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 thuận tiện 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 nhu cầu điều kiện kèm theo thì nó sẽ trả tác dụng 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 tài liệu, số hàng trong bảng mà bạn muốn lấy tác dụng 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, USD G USD 4 : USD I USD 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 chú ý quan tâm 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 và thắt chặt bảng lại nhé. Vì nhấn F4 nên cú pháp hàm có dạng USD G USD 4 : USD I USD 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ó tính năng gần tựa như. Đây là hàm giúp bạn thuận tiện tham chiếu và tìm kiếm theo các “ cột theo các dòng ”. Nếu thỏa mãn nhu cầu điều kiện kèm theo thì nó sẽ trả hiệu 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 tài liệu, số cột trong bảng mà bạn muốn lấy hiệu 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 đổi khác 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, USD G USD 4 : USD H USD 6,2, TRUE ) .

Và tác dụng 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ú ý quan tâm 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 công dụ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ị đúng mực hay gần đúng ). Ở phần giá trị đúng chuẩn hay gần đúng bạn hoàn toàn có thể chọn 1,0 hoặc – 1 nhé. Thường mọi người chọn 0 vì 0 lấy hiệu quả đúng mực nhất .Ví dụ cho hàm này cũng đơn thuầ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 khoanh vùng 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ó hoàn toàn có thể phong phú 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 hoang dã và mình muốn lấy con vịt ra trong bảng tham chiếu đó. Vịt trong bảng bị số lượng 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 thuần phải không nào .Nhưng hàm Match và hàm Index không chỉ là như vậy. Trong giám sát í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ó công dụng sửa chữa thay thế cho các hàm như Hlookup hay Vlookup. Những hàm Hlookup hay Vlookup có số lượng 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 tích hợp nó hoàn toàn có thể tham chiếu và trích dẫn bất kỳ giá trị nào trong bất kỳ bảng tham chiếu nào. Trong tổng thể 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 đúng mực nhé .Nhưng thứ nhất 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ó công dụng chuyển chữ thành số. Ví dụ sau nếu không làm hàm này chắc như đinh bạn sẽ bị báo lỗi, đặc biệt quan trọng là phiên bản Excel 2010 trở đi .

Hàm Value

Như đã nói tính năng của hàm là chuyển chữ thành số nên cú pháp nó cũng đơn thuầ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ờ tất cả 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à giờ đây tất cả 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 ), USD C USD 9 : USD C USD 11,0 ) .Để trích dẫn cột ta phải dùng hàm Match như sau : = MATCH ( VALUE ( MID ( A2, 3,1 ) ), USD D USD 8 : USD F USD 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à đơn cử là ô A2 từ kí tự “ 1 ” sang số “ 1 ” .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 ( USD D USD 9 : USD F USD 11, MATCH ( LEFT ( A2, 2 ), USD C USD 9 : USD C USD 11,0 ), MATCH ( VALUE ( MID ( A2, 3,1 ) ), USD D USD 8 : USD F USD 8,0 ) ) .

Và hiệu 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 xuất sắc và hẹn gặp lại !

Source: http://wp.ftn61.com
Category: Hỏi Đáp