Top 15 # Hàm Là Gì Trong Excel Xem Nhiều Nhất, Mới Nhất 6/2023 # Top Trend

Cách sử dụng hàm Vlookup trong Excel, cú pháp cũng như các ví dụ sẽ được chúng tôi chia sẻ tất tần tật trong bài viết sau đây. Các bạn cùng tham khảo để sử dụng hàm Vlookup cho đúng.

Cách thực hiện này giúp bạn:– Dùng Vlookup hiệu quả– Biết cách sửa lỗi hàm VLOOKUP– Biết cách kết hợp hàm Vlookup với các hàm khác

Hàm VLOOKUP trong Excel là hàm tìm kiếm giá trị theo cột và trả về phương thức hàng dọc, đây có thể nói là hàm excel khá thông dụng và tiện lợi với những người hay sử dụng Excel để thống kê, dò tìm dữ liệu theo cột chính xác. Trong khuôn khổ thủ thuật , chúng tôi sẽ trình bày tới bạn đọc 2 cách dò tìm cơ bản của hàm này.

Ví dụ hàm Vlookup, bài tập hàm Vlookup trong Excel

cách sử dụng hàm Vlookup trong Excel 2016, 2013, 2010, 2007

I. Cú pháp hàm Vlookup

Lưu ý cú pháp hàm: VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

lookup_value: Giá trị dùng để dò tìm.

table_array: Bảng giá trị dò, để ở dạng địa chỉ Tuyệt đối (có dấu $ phía trước bằng cách nhấn F4).

col_index_num: Thứ tự của cột cần lấy dữ liệu trên bảng giá trị dò.

range_lookup: Phạm vi tìm kiếm, TRUE tương đương với 1 (dò tìm tương đối), FALSE tương đương với 0 (dò tìm tuyệt đối).

II. Ví dụ về hàm Vlookup trong Excel 1. Cách dò tìm tương đối:

VD: Căn cứ vào bảng quy định xếp loại tương ứng với điểm đã cho, tiến hành xếp loại học lực cho các sinh viên có tên trong danh sách dưới:

Ví dụ về hàm Vlookup excel

Ta sử dụng công thức ở cột D6 là: =VLOOKUP(C6,$C$16:$D$20,2,1)Kết quả thu được:

Kết quả khi dùng hàm vlookup

Với cách dò tìm tuyệt đối thì bạn sẽ tìm được chi tiết hơn cách dò tìm tương đối.VD: Điền thông tin Quê quán và trình độ của các nhân viên vào bảng căn cứ vào mã nhân viên đã cho tương ứng phía dưới.

Ví dụ dò tìm tuyệt đối khi dùng hàm vlookup

Để điền thông tin quê quán của nhân viên ta dùng công thức Vlookup cho ô E6 như sau: =VLOOKUP(A6,$D$12:$F$17,2,0)A6 là giá trị đem dò tìm$D$12:$F$17 là bảng dò tìm2 : số thứ tự cột dữ liệu trên bảng dò0 : Kiểu dò tìm chính xác

Cách sử dụng dò tuyệt đối với hàm Vlookup

Tương tự để điền vào ô trình độ của nhân viên ta làm như sau:

Với ô F6 có công thức : =VLOOKUP(A6,$D$12:$F$17,3,0)

Kết quả dùng hàm Vlookup dò giá trị tuyệt đối

III. Hàm Vlookup kết hợp hàm Hlookup, Left, Right, Match

Ở nội dung trên chúng tôi đã hướng dẫn bạn tìm hiểu cơ bản về hàm Vlookup, để giúp các bạn hiểu hơn và áp dụng thực tế hơn, bây giờ chúng tôi sẽ đưa ra một tình huống bài tập cụ thể như sau. Bài toán này là sự kết hợp giữa hàm Vlookup và các hàm trong Excel như hàm Hlookup, hàm Left, hàm Right và hàm Match.

Yêu cầu cần giải quyết:

Câu 1: Căn cứ vào 2 ký tự bên trái và 2 ký tự bên phải của Mã SP trong Bảng 1, hãy tra trong Bảng 2 đề điền giá trị cho cột Tên Hàng – Tên Hãng Sản xuất (Tính dữ liệu cho cột C5:C10).VD: KD-NĐ Tức là gạo Khang Dân – Nam Định.Câu 2: Hãy điền Đơn Giá cho mỗi mặt hàng đựa vào Mã SP ở Bảng 1 và tra ở Bảng 2. (Tính dữ liệu cho cột D5:D10).Câu 3: Tính thành tiền = Số lượng * Đơn giá

Câu 3: Tính thành tiềnỒ, câu hỏi này thường xuất hiện trong những bài toán tính toán dữ liệu như bảng lương, chi phí, tổng thành tiền, … Khá là dễ rồi phải không các bạn.Công thức tính thành tiền sẽ như sau, Tại ô F5 các bạn nhập E5=+D5*E5

IV. Sửa lỗi #NA hàm Vlookup Lỗi #NA là gì?

Lỗi #NA được trả về trong công thức excel khi không tìm thấy giá trị thích hợp

Khi sử dụng hàm Vlookup, chúng ta gặp lỗi #NA khi không tìm thấy điều kiệm tìm kiếm trong vùng điều kiến, chính xác ở đây là cột đầu tiên trong vùng điều kiện của hàm Vlookup

Cách sửa lỗi #NA trong hàm Vlookup

Trong Excel, hàm Vlookup được sử dụng phổ biến giúp bạn có thể thực hiện hàm tìm kiếm giá trị, trả về phương thức hàng dọc tốt, nhanh chóng. Hiện nay, bạn có thể áp dụng một trong hai cách dò hàm Vlookup.

Với hai cách dò tìm tương đối và tuyệt đối của hàm Vlookup, hàm dò tìm giá trị theo cột bạn dễ dàng thống kê dữ liệu trên bảng tính Excel. Vlookup giúp cho việc thống kê các chi tiết để làm báo cáo lọc ra các danh sách cần thiết, công việc của bạn sẽ chính xác và rút ngắn thời gian hơn. Đây là hàm được dùng khá nhiều trong quá trình thao tác trên bảng tính.

Để học tốt cũng như hiểu rõ hơn về hàm tìm kiếm bạn có thể tham khảo hàm Hlookup trong Excel đã được chúng tôi giới thiệu. Hàm Hllookup giúp bạn tìm kiếm và tham chiếu theo cột, hoặc theo dòng, Khi bạn sử dụng hàm này, nếu thỏa mãn điều kiện đặt ra.

Bên cạnh hàm tìm kiếm Vlookup trong Excel, bạn có thể tham khảo Hàm COUNTIF trong Excel, Hàm COUNTIF là một hàm đếm khá tiện lợi, với những điều kiện ban đầu, Hàm COUNTIF sẽ cho bạn những kết quả chính xác nhất.

Hàm tính toán cũng có khá nhiều hàm khác nhau trong Excel, và Hàm SUMIF là một trong những hàm được nhiều người sử dụng, Hàm SUMIF là hàm tính tổng có điều kiện, giúp bạn tính tổng các cột, các hàng riêng lẻ mà không phải tính tổng tất cả các dữ liệu trong file Excel

https://thuthuat.taimienphi.vn/huong-dan-su-dung-ham-vlookup-trong-excel-633n.aspx Sử dụng Excel không thể không nhắc tới Hàm MID, đây là hàm cơ bản trong Excel hỗ trợ cắt những chuỗi ký tự trong một dãy ký tự cho trước trong bảng tính Excel, hàm cơ bản MID được sử dụng rất nhiều trong các trường hợp cần lấy ra những ký tự đặc biệt nhất trong các chuỗi ký tự dài có thể lên tới hàng trăm ký tự

Hàm Vlookup trong Excel là hàm tìm kiếm giá trị theo cột và trả về phương thức hàng dọc (theo cột), nó giúp chúng ta thống kê, dò tìm dữ liệu theo cột một cách nhanh chóng và tiện lợi. Đây là một trong những hàm phổ biến và hữu ích nhất trong Excel, nhưng lại ít người hiểu về nó. Trong bài viết này, chúng tôi sẽ giúp bạn hiểu và sử dụng hàm Vlookup một cách thành thạo qua các ví dụ thực tế nhất.

1. Chức năng của hàm Vlookup trong Excel.

Chúng ta sử dụng hàm Vlookup để tìm kiếm dữ liệu trong bảng hoặc một phạm vi theo cột trong một bảng dò tìm đã định nghĩa trước. Như vậy, chức năng chính của hàm Vlookup là dùng để tìm kiếm giá trị trong một bảng giá trị cho trước.

2. Cú pháp hàm vlookup trong excel.

Lookup_value (bắt buộc): Giá trị cần tìm, có thể là ô tham chiếu, một giá trị hoặc chuỗi văn bản.

Table_array (bắt buộc): Bảng tìm kiếm giá trị gồm hai cột dữ liệu trở lên. Có thể là mảng thường, được đặt tên hoặc bảng Excel. Cột chứa giá trị tìm kiếm phải được đặt đầu tiên của Table_array.

Row_index_num (bắt buộc): Số thứ tự của cột chứa kết quả trả về trong Table_array.

Range_lookup (tuỳ chọn): Một giá trị logic (Boolean) cho biết hàm VLOOKUP cần phải tìm kết quả chính xác hay tương đối.

Nếu TRUE hoặc bỏ qua, kết quả khớp tương đối được trả về. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Vlookup của bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value.

Nếu FALSE, chỉ kết quả khớp chính xác được trả về. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếm, hàm Vlookup sẽ trả về lỗi #N/A.

3. Ví dụ về hàm VLOOKUP trong Excel.

3.1. Các trường hợp sử dụng hàm Vlookup trong Excel.

Hàm Vlookup trong Excel có 2 cách sử dụng. Khi khi lập công thức ta có thể lựa chọn 1 trong 2 bằng cách gán giá trị cho Range_lookup là TRUE (1) hoặc FALSE (0).

Hàm Vlookup để tìm kiếm chính xác (Range_lookup = FALSE). Cách tìm kiếm này được áp dụng trong những trường hợp cần tìm kiếm kết quả khớp chính xác. Nếu không giá trị nào trong hàng chỉ định khớp chính xác với giá trị tìm kiếm, hàm Vlookup sẽ báo lỗi. VD: Tìm kiếm tên, tuổi, quê quán, … với mã nhân viên khớp với mã nhân viên cho trước.

Hàm Vlookup để tìm kiếm tương đối (Range_lookup = TRUE). Cách tìm kiếm này được áp dụng trong những trường hợp tìm kiếm theo khoảng giá trị kết quả khớp tương đối. Nghĩa là nếu kết quả khớp chính xác không được tìm thấy, hàm Vlookup của bạn sẽ trả về giá trị lớn nhất kế tiếp nhỏ hơn look_up value. VD: Xếp loại học lực của học sinh theo điểm tổng kết.

3.2. Ví dụ về hàm Vlookup để tìm kiếm chính xác.

VD: Giả sử, bạn có một bảng dữ liệu nhân viên, lưu trữ mã nhân viên, họ tên, chức vụ. Một bảng khác lưu trữ mã nhân viên, quê quán, trình độ học vấn. Giờ bạn muốn điền thông tin quê quán, trình độ học vấn cho từng nhân viên thì phải làm như thế nào?

Để điển thông tin quê quán cho nhân viên, tại ô E4, ta nhập công thức dò tìm chính xác như sau: =VLOOKUP(B4,$B$17:$D$26,2,0).

Trong đó:

B4: Là giá trị cần đối chiếu.

$B$17:$D$26: Là bảng dò tìm, địa chỉ của bảng dò tìm phải là địa chỉ tuyệt đối.

2: Số thứ tự cột dữ liệu trên bảng dò tìm.

0: Kiểu tìm kiếm chính xác.

Sau khi điền xong công thức cho ô E4, tiếp tục kéo xuống copy công thức cho những nhân viên còn lại. Quan sát hình để hiểu rõ về công thức hơn.

Ảnh mình hoạ sử dụng hàm Vlookup để tìm kiếm chính xác trong Excel.

Chúng ta cũng thực hiện tương tự như vậy vậy để điền trình độ nhân viên, với công thức ở ô F4 là: =VLOOKUP(B4,$B$17:$D$26,3,0)

Trong đó:

Quan sát hình bên dưới để hiểu cách tìm kiếm và lấy giá trị của hàm Vlookup khi tìm kiếm chính xác.

Tìm kiếm tương đối chỉ có thể áp dụng khi giá trị cần dò tìm trong table_array đã được sắp xếp theo thứ tự (tăng dần hoặc giảm dần hay theo bảng chữ cái). Với những bảng như vậy bạn có thể dùng dò tìm tương đối, khi đó nó tương tự như dùng hàm IF vô hạn vậy.

VD: Căn cứ vào bảng quy định xếp loại tương ứng với điểm đã cho, tiến hành xếp loại học lực cho các sinh viên có tên trong danh sách:

Giờ ta sẽ sử dụng hàm VLOOKUP để nhập xếp loại cho các học sinh. Bạn để ý thấy rằng bảng Quy định xếp loại đã được sắp xếp theo thứ tự từ thấp đến cao (từ yếu đến giỏi) nên trong trường hợp này ta có thể dùng dò tìm tương đối.

Tại ô E4, ta nhập vào công thức là: =VLOOKUP(D4,$B$17:$C$20,2,1)

Trong đó:

Sau khi điền xong công thức cho ô E4, tiếp tục kéo xuống copy công thức cho những học sinh còn lại. Quan sát hình để hiểu rõ hơn về công thức và cách dò tìm tương đối.

Dễ thấy, khi không do tìm được kết quả nào trùng khớp với giá trị dò tìm thì hàm Vlookup sẽ lấy giá trị lớn nhất kế tiếp nhỏ hơn nó.

4. Những điều cần biết khi sử dùng hàm Vlookup trong Excel.

4.1. Một số lưu ý khi sử dụng hàm Vlookup trong Excel.

Khi tạo công thức hàm bạn cần cố định Table_array (vùng cần tìm kiếm) bằng cách thêm kí tự $ vào địa chỉ của vùng ( VD: $A$18:$B$21) để khi ta copy công thức xuống các hàng khác thì vùng cần tìm kiếm không bị thay đổi theo.

Hàm Vlookup trong Excel chỉ có thể tìm ở cột ngoài cùng bên trái của vùng ta chọn. Nếu bạn cần tìm ở những vị trí khác, hãy sử dụng công thức Index kết hợp Match.

Hàm Vlookup trong Excel không phân biệt được chữ hoa và thường.

Nếu range_lookup được đặt là TRUE hoặc bỏ trống (kết quả khớp tương đối), các giá trị ở hàng đầu của table_array phải được xếp theo thứ tự tăng dần (A-Z) từ trái sang phải.

Khi dùng hàm Vlookup để tìm kiếm tuyệt đối nhưng vùng cần tìm kiếm lại không chứa giá trị đó thì kết quả trả về sẽ là #N/A!.

4.2. Hướng dẫn sử dụng dấu chấm dấu phẩy.

Bạn có đang gặp vấn đề trong việc dùng dấu chấm “.” dấu dấu chấm phẩy “;” trong việc ngăn cách cách thành phần trong hàm, hay dấu chấm “.” dấu phẩy “,” trong việc phân cách chữ số thập phân?

Trong Excel người ta thường dấu phẩy “,” trong việc ngăn cách thành phần trong hàm và phân cách các hàng nghìn, triệu, tỷ trong số. Dấu chấm “.” để phân cách chữ số thập nhân. Nhưng trong ở một số bản phần mềm Excel khác lại dùng dấu phẩy “,” để phân cách hàng thập phân và dấu chấm “.” để phân cách các nhóm 3 chữ số (các hàng nghìn, triệu, tỷ), còn dấu “;” để phân cách các thành phần trong hàm. Điều này khiến người dùng dễ nhầm lẫn khi dùng dấu chấm và dấu phẩy trong Excel.

Trong Excel có 2 kiểu quy ước:

Quy ước kiểu phương Tây: Dấu chấm “.” là dấu để phân cách chữ số thập phân, còn phân cách các hàng nghìn, triệu, tỷ và các thành phần khi viết hàm là dấy phẩy “,”.

Quy ước của Việt Nam: Dấu phẩy “,” để phân cách hàng thập phân và dấu chấm “.” để phân cách các nhóm 3 chữ số (các hàng nghìn, triệu, tỷ), còn dấu “;” để phân cách các thành phần trong hàm.

Vậy làm thế nào để biết Excel của mình dùng tuần theo quy ước nào, bạn có thể thử cách sau:

Nhập thử một số thập phân tại ô nào đó, nếu Excel không dịch số đó về bên phải thì dấu ngăn cách thập phân là dấu “.” (chấm).

Nếu dấu ngăn cách thập phân là dấu “,” thì dấu ngăn cách phần ngàn là dấu “.” và dấu ngăn cách trong công thức là dấu “;” (chấm phẩy).

Khi Excel của bạn tuần theo quy ước của Việt Nam bạn cần đề ý hơn khi nhập dữ liệu, lập công thức, tra cứu công thức để phù hợp.

Nhưng để tiện sử dụng và tra cứu bạn nên đổi sang quy ước của phương Tây bằng cách:

4.3. Hướng dẫn sử dụng địa chỉ tuyệt đối khi dùng hàm Vlookup trong Excel.

Trong excel có 3 loại địa chỉ:

Địa chỉ tương đối: Là địa chỉ bị thay đổi tương ứng với mỗi dòng và cột khi chúng ta thực hiện sao chép công thức. (VD: B5 là địa chỉ của hàng 5 cột B).

Địa chỉ tuyệt đối: Là địa chỉ được cố định lại, không thay đổi khi ta copy công thức. (VD: $A$1- địa chỉ tuyệt đối của 1 ô, $B$17:$C$20 – địa chỉ tuyệt đối của 1 vùng)

Để tạo địa chỉ tuyệt đối, thì bạn nhấn phím F4, lúc này sẽ có dấu đô la ($) ở trước chỉ số cột và dòng.

Tóm lại nếu là địa chỉ tuyệt đối thì bạn thấy có dấu đô la ($) trước chỉ số cột và dòng.

Địa chỉ hỗn hợp: Địa chỉ hỗn hợp là địa chỉ chỉ cố định dòng hoặc cột mà thôi.

Cố định cột: Ví dụ: $A1, thì bạn thấy chỉ số cột được cố định, còn chỉ số dòng không được cố định.

Cố định dòng: Ví dụ: A$1 thì bạn thấy chỉ số cột không được cố định, còn chỉ số dòng cố định.

Khi sử dùng hàm Vlookup trong Excel bạn thường phải tìm kiếm cho cả cột nên việc copy công thức là không tránh khỏi. Lúc này bạn cần lưu ý để địa chỉ của vùng tìm kiếm là địa chỉ tuyệt đối để khi ta copy công thức cho những hàng khác thì vùng tìm kiếm của ta không bị thay đổi.

4.4. Đặt tên cho bảng trong Excel.

Địa chỉ các ô, các bảng, các vùng trong Excel được lưu bằng những kí tự và số rất khó nhớ, điều này gây khó khăn và tồn thời gian cho chúng ta khi phải gọi ra các bảng, các vùng dữ liệu từ nhiều nơi. Để đơn giản hoá việc này chúng ra có thể đặt tên cho các vùng dữ liệu. Khi cần sử dụng đến chúng ta chỉ cần gọi tới vùng đó thông qua tên đã đặt.

Đặt tên cho vùng dữ liệu dữ liệu bằng cách:

B1: Chọn vùng muốn đặt tên, vùng này có thể là 1 ô hoặc 1 vùng tuỳ theo mục đích sử dụng của bạn.

B2: Tại ô địa chỉ của vùng dữ liệu (góc bên trên phía bên tay trái, ngay trên cột A) ta nhập tên cho vùng dữ liệu và Enter.

Sau khi đặt tên cho vùng dữ liệu ta chỉ cần gọi vùng đó ra thông qua tên mà không lo địa chỉ bị sai khi copy công thức từ dòng này sang dòng khác.

Khi sử dụng hàm Vlookup ta có thể đặt tên cho cùng cần tìm kiếm mà không cần quan tâm tới địa chỉ tuyệt đối của nó.

VD: Ở ví dụ bên trên sau khi đã đặt tên cho bảng quy định xếp loại.

Từ công thức: =VLOOKUP(D4,$B$17:$C$20,2,1).

Cả 2 công thức này đều tương đương nhau nhưng với công thức mà vùng tìm kiếm được đặt tên giúp ta dễ dàng copy cho những hàng khác và đơn giản, dễ hình dung.

Video hướng dẫn:

Trọn bộ khoá học Excel cơ bản miễn phí: Học Excel cơ bản

Hàm VLOOKUP nâng cao trong ExcelHàm VLOOKUP 2 điều kiện trong ExcelHàm VLOOKUP kết hợp hàm IF trong ExcelHàm VLOOKUP ngược và cách dùng hàm VLOOKUP ngược trong Excel

5

/

5

(

1

bình chọn

)

Hàm LEN trong Excel là hàm đếm ký tự, hàm này hay được dùng khi bạn cần đếm chuỗi ký tự, chuỗi văn bản để xác định chiều dài của chuỗi ký tự đó. Để bạn thấy được ứng dụng thực tế thì mình cũng có 1 bài hướng dẫn tách họ tên mà trong đó hàm LEN được kết hợp với các hàm khác để tính toán.

Trong bài viết này, chúng tôi sẽ hướng dẫn bạn cách sử dụng hàm LEN trong Excel để đếm ký tự tự động trong Excel chứ bạn không cần phải đếm thủ công. Và cũng sẽ có ví dụ minh họa để bạn hiểu được cách dùng hàm LEN cũng như cách kết hợp hàm LEN với các hàm khác.

Hướng dẫn dùng hàm LEN trong Excel để đếm ký tự

Hàm LEN trong Excel là hàm cơ bản nên nó có thể sử dụng được với các phiên bản Excel từ 2003, 2007, 2010 cho đến 2013, 2016. Trong bài này mình hướng dẫn cách dùng hàm LEN trong Excel 2016 với các phiên bản khác bạn cũng có thể làm tương tự.

Cú pháp của hàm LEN

=LEN(text)

Trong đó

text: Là chuỗi ký tự, văn bản bao gồm cả chữ, số, ký tự đặc biệt và khoảng trống (dấu cách) hoặc tham chiếu tới ô chứa chuỗi ký tự, văn bản nào đó. Đây là giá trị bắt buộc.

Lưu ý: Trên thực tế khi làm việc, giá trị text thường là tham chiếu tới 1 ô nào đó chứ rất ít khi chúng ta nhập chuỗi ký tự vào hàm để đếm. Trong trường hợp nhập chuỗi ký tự vào hàm thì chuỗi ký tự đó phải đặt trong dấu nháy kép (” “).

Ví dụ về hàm LEN trong Excel

Như đã nói ở trên, khi dùng hàm LEN bạn có thể nhập chuỗi ký tự vào trong hàm hoặc tham chiếu đến 1 ô có chứa chuỗi ký tự nào đó. Ở đây mình sẽ lấy ví dụ về cả 2 trường hợp để bạn nắm rõ.

Ví dụ

Trường hợp nhập thẳng chuỗi ký tự vào hàm như sau:

Trường hợp tham chiếu tới 1 ô chứa chuỗi ký tự/văn bản

Ví dụ về sự kết hợp hàm LEN với các hàm khác trong Excel

Công thức như sau: =LEFT(A4,LEN(A4)-5)&” – “&MID(A4,5,3)&”.”&RIGHT(A4,2)

Giải thích về cú pháp hàm

Hàm LEN chúng ta có thể đếm được tổng số ký tự trong chuỗi đã cho

Hàm LEFT kết hợp với hàm LEN chúng ta sẽ cắt được chuỗi ký tự ở bên trái là 90B2

Hàm MID chúng ta sẽ cắt được chuỗi ký tự bất kỳ ở vị trí nào đó, ở đây nó sẽ lấy được 3 ký tự 445

Hàm RIGHT nó sẽ cắt chuỗi ký tự từ bên phải và được ký tự 73

Để kết hợp lại kết quả của các hàm và thêm các ký tự vào các vị trí cần thiết chúng ta sẽ dùng dấu & để nối chuỗi với cách bố trí như trong công thức trên.

Lời kết

Trong thực tế, khi làm việc với Excel nhất là dân kế toán thì hàm LEN được sử dụng rất nhiều dùng để tính toán, thống kê số liệu. Chính vì thế ngoài việc học cách sử dụng hàm LEN cơ bản thì bạn cần nhạy bén hơn trong việc kết hợp hàm LEN với các hàm khác để có thể tận dụng được hết sức mạnh của hàm này.

Trong Excel hàm AVERAGE dùng để tính trung bình cộng của một dãy số nào đó trong bảng tính giúp hỗ trợ người dùng tính toán nhanh chóng hơn trong những dãy số dài.

Cú pháp của hàm AVERAGE đó là: =AVERAGE(number1,[number2],…).

Number 1:là đối số thứ nhất có thể là số, ô tham chiếu hoặc phạm vi nào đó trong bảng tính chứa số mà các bạn muốn tính trung bình, bắt buộc.

Number2: là các số, ô tham chiếu hoặc phạm vi nào đó chứa số bổ sung mà các bạn muốn tính trung bình. Tối đa sẽ là 256 số, tùy chọn.

Lưu ý: Một số ô tham chiếu hoặc phạm vi nào đó có chứa giá trị logic, văn bản hay ô rộng thì những giá trị này sẽ bị bỏ qua. Trừ giá trị 0 được được nhập trực tiếp vào danh sách đối số đó. Nếu đối số là dạng văn bản hay các giá trị không thể chuyển đổi thành dạng số thì sẽ bị báo lỗi.

Cách sử dụng hàm AVERAGE trong Excel

Để sử dụng được hàm AVERAGE ta sẽ sử dụng cú pháp của hàm đã cho ở trên:

=AVERAGE(number1,[number2],…).

Number 1:là đối số thứ nhất có thể là số, ô tham chiếu hoặc phạm vi nào đó trong bảng tính chứa số mà các bạn muốn tính trung bình, bắt buộc.

Number2: là các số, ô tham chiếu hoặc phạm vi nào đó chứa số bổ sung mà các bạn muốn tính trung bình.

Ví dụ: Cho bảng điểm của danh sách một số học sinh cần tính điểm trung bình cộng các môn học của từng học sinh. Hãy tính điểm trung bình của học sinh qua các môn học.

Theo như cú pháp để tính của hàm AVERAGE trong Excel ta sẽ nhập công thức vào ô G6: =AVERAGE(D6,E6,F2)

Sau đó nhấn Enter là kết quả trung bình các môn học sẽ được hiện ra.

Nếu bạn muốn tính tiếp trung bình cộng của các dãy số tiếp theo thì chỉ cần đưa con trỏ chuột vào ô kết quả vừa thu được và bấm trái chuột đồng thời kéo xuống các ô phía dưới. Lúc này hệ thống sẽ tự động sao chép công thức và hiển thị kết quả vào các ô đó. Như vậy chỉ cần thực hiện công thức tính tại một ô mà ta đã có thể thu được kết quả trung bình của toàn bộ học sinh.

Và đây là kết quả:

Những lỗi thường gặp khi sử dụng hàm AVERAGE

Những lỗi phổ biến khi người dùng sử dụng hàm AVERAGE đó là:

#p/0!

Đây là lỗi xảy ra khi các giá trị được tính trung bình không phải là dạng số

#VALUE!

Lỗi này thường xảy ra nếu bất kỳ đối số nào được cung cấp trực tiếp mà hàm AVERAGE không thể diễn giải được các giá trị đó là giá trị số.

Cách khắc phục các lỗi trong khi sử dụng hàm AVERAGE

Còn để khắc phục lỗi #VALUE! và #p/0! thì bạn cần xây dựng một công thức để bỏ qua vùng tham chiếu chứa lỗi để tính trung bình của các giá trị đúng còn lại. Để thực hiện cách này bạn cần sử dụng kết hợp hàm AVERAGE với hàm IF và hàm ISERROR để xác định xem có xảy ra lỗi trong vùng tham chiếu cụ thể hay không.

Phương pháp này sẽ yêu cầu công thức bảng là:

=AVERAGE(IF(ISERROR(B2:D2),””,B2:D2))

Lưu ý: Do đây là công thức mảng array nên bạn cần phải nhập bằng cách nhấn các phím CTRL + SHIFT + ENTER. Excel sẽ tự động đóng công thức bằng dấu {}. Nếu bạn thử nhập các công thức này tự động thì Excel sẽ hiển thị công thức dưới dạng văn bản.

Cách khắc phục này không chỉ khắc phục được lỗi #p/0! và #VALUE! mà còn khắc phục được các lỗi khác như: #N/A, #NULL và một số lỗi khác.

Cách tính hàm AVERAGE có một điều kiện

Để có thể tính trung bình có điều kiện trong Excel thì ta cần phải kết hợp giữa hàm AVERAGE và hàm IF.

Qua bảng tính trên hãy tính số lượng bán trung bình của mặt hàng cam.

Để có thể tính được số lượng bán trung bình của mặt hàng Cam, chúng ta cần:

Xác định những mặt hàng Cam và xét riêng số lượng của mặt hàng này.

Tính trung bình mặt hàng Cam.

Cách thực hiện:

Tại ô cần tính chúng ta sử dụng công thức AVERAGEIF như sau:

=AVERAGEIF(A2:A11,”Cam”,B2:B11)

Vùng A2:A11: là vùng chứa tên các mặt hàng và là vùng chứa điều kiện tính trung bình

“Cam”: là điều kiện mà bạn cần tính trung bình, là mặt hàng Cam

Vùng B2:B11: sẽ là cột số lượng và vùng chứa kết quả cần tính trung bình

Kết quả thu được sẽ là 24.

Công thức tính trung bình cộng có nhiều điều kiện AVERAGEIFS

Cấu trúc của hàm AVERAGEIFS đó là: =AVERAGEIFS(average_range,criteria_range,criteria,…)

Average range: là vùng tính trung bình

Criteria range: là vùng điều kiện

Criteria: là điều kiện

Lưu ý: Nếu có nhiều điều kiện thì ta sẽ thêm vùng điều kiện và điều kiện tiếp theo sau.