Tổng hợp các hàm Excel thi tin học cơ bản

Một số lỗi thường gặp:

1. Lỗi hiễn thị: #####

Lỗi này thường được hiển thị khi ô tính nằm trong cột qua hẹp, không thể hiển thị hết dữ liệu trong ô. Thông báo lỗi này không ảnh hưởng đến giá trị ô tính mà chỉ gặp vấn đề trong hiển thị kết quả ô. Giải pháp: thay đổi chiều rộng cột có chứa ô báo lỗi. Luu ý: Trong một trường hợp ít gặp khác, giá trị trong ô là thời gian hoặc ngày tháng với kết quả âm hoặc giá trị quá lớn để hiển thị được ngày tháng. Khi đó việc thay đổi độ rộng của cột liên quan không có tác dụng. Bạn chỉ cần thay đổi định dang ô tính dưới dạng Number để hiện kết quả giá trị số của thời gian hay ngày tháng trong ô tính.

2. Lỗi sai ký tự: #NAME!

Đây là lỗi thường gặp khi nhập sai tên hàm, công thức, quên đặt chuỗi văn bản vào dấu kép đôi “” dẫn đến việc Excel không hiểu công thucwsbanj vừa nhập. Giải pháp: Kiểm tra lại cẩn thận từng ký tự trong công thức từ đầu đến cuối và sửa lại. Vid dụ: =SUN(12,13) | =SUM(A1B2) | =LEN(3hoc) hiển thị lỗi #NAME! Được chỉnh tương ứng thành =SUM(12,13) | =SUM(A1:B2) | =LEN(“3hoc”) Lưu ý: Một số hàm Excel chỉ sử dụng được ô những phiên bản MS Office mới hơn hoặc phải cài đặt thêm thì vẫn hiển thị lỗi này vì Excel không nhận diện được công thức bạn nhập.

3. Lỗi không tìm tháy dữ liệu: #N/A

Lỗi này thường xảy ra khi không tìm thấy hoặc mất dữ liệu, thường gặp khi sử dụng các hàm tìm kiếm VLOOKUP, HLOOKUP, MATCH... Giải pháp: Kiểm tra lại bảng dữ liệu đối chiếu trong các hàm tìm kiếm và sắp xếp dữ liệu so sánh trong bảng theo giá trị từ nhỏ đến lớn. Đảm bảo dữ liệu tìm kiếm và đối chiếu phải cùng một kiểu định dạng dữ liệu đồng thời giá trị của dữ liệu tìm kiếm không được nhỏ hơn giá trị nhỏ nhất của dữ liệu đối chiếu.

4. Lỗi sai giá trị: #VALUE!

Đây là lỗi phổ biến khi nhập giá trị không cùng kiểu dữ liệu (hàm sử dụng số nhưng lại nhập chuỗi văn bản), thừa giá trị (hàm yêu cầu đối số là giá trị nhưng chọn bảng có nhiều hơn 1 giá trị)... Giải pháp: Kiểm tra lại cú pháp hàm để sử dụng đúng từng giá trị tương ứng với đối số của hàm.

5. Lỗi không thể tham chiếu: #REF!

Lỗi này thường gặp khi công thức Excel có chứa dữ liệu tham chiếu hoặc liên kết nhưng không thể tìm thấy. Lý do thường là sai liên kết hoặc tham chiếu đến số hàng, cột vượt quá dữ liệu vùng tham chiếu. Giải pháp: Kiểm tra phần dữ liệu tham chiếu hoặc liên kết trong công thức và cập nhật lại.

6. Lỗi số: #NUM!

Khi tính toán số trong Excel, khi gặp kết quả quá lớn nằm ngoài khả năng tính toán hoặc giá trị số không phù hợp để sử dụng trong hàm thì Excel sẽ hiện giá trị #NUM! Giải pháp: sử dụng những tính toán với số quá lớn và chú ý điều kiện sử dụng số (Số nguyên, số âm hay dương...).

7. Lỗi dữ liệu rỗng: #NULL!

Trường hợp bạn chọn không đúng vùng dữ liệu, Excel không thể hiểu chính xác vùng bạn chọn thì kết quả sẽ hiện #NULL! Giải pháp: Kiểm tra vùng dữ liệu trong hàm và cập nhật lại. Vi dụ =SUM(A1 B2) cần sửa thành =SUM(A1,B2) hoặc =SUM(A1:B2).

8. Lỗi chi 0: #DIV/0!

Lỗi này rất dễ nhận thấy, đó là khi bạn thực hiện phép chia cho 0 hoặc không nhập số chia. Giải pháp: thay đổi số chia khác 0. Ví dụ: =MOD(3,0) phải thay bằng =MOD(3,x) với x là số khác 0.

Các hàm thông dụng

Hàm And(điều kiện 1, điều kiện 2, ...)

Hàm And trả về kết quả True khi tất cả các điều kiện đều đúng, ngược lại cho kết quả là False.

Hàm Or(Điều kiện 1, điều kiện 2, ...)

Hàm Or trả về kêt quả True khi từ 1 điều kiện trở lên đúng, ngược lại nếu tất cả sai thì cho kết quả False

Hàm If(điều kiện, công việc đúng, công việc sai)

- Điều kiện: Phải cho kết quả True/False (đa số là biểu thức so sánh).

- Công việc đúng: Nếu điều kiện cho về True thì thực hiện công việc đúng.

- Công việc sai: Nếu điều kiện cho về False thì thực hiện công việc sai.

- Ngoài ra hàm If còn có thể lòng vào nhau

=If(điều kiện, công việc đúng, If(điều kiện, công việc đúng, công việc sai))

... công việc sai có thể là 1 hàm if khác ...

Hàm Vlookup(mã, bảng mã, vị trí, 0)

Mã: Là kí hiệu cần dò tìm.

Bảng mã: Là bảng dữ liệu chứa dữ liệu cần tìm.

Vị trí: Cột dữ liệu cần lấy ra (trong bảng dữ liệu có thể có nhiều cột dữ liệu cần lấy).

- Nội dung: =VLOOKUP(A2,$E$2:$G$4,2,0) [vị trí là 2 do nội dung nằm cột 2 của bảng dữ liệu]

- Giá tiền: =VLOOKUP(A2,$E$2:$G$4,3,0) [vị trí là 3 do nội dung nằm cột 3 của bảng dữ liệu]

Do bảng mã sẽ di chuyển trong quá trình kéo thả kết quả. Nên phải nhấn F4 lại bảng để khóa không cho chạy.

Hàm Hlookup(mã, bảng mã, vị trí, 0)

- Đối với Vlookup dò trong bảng mã dọc thì Hlookup là do trong bảng mã ngang.

- Vị trí: Hàng dữ liệu cần lấy ra ( trong bảng dữ liệu có thể có nhiều cột cột dữ liệu cần lấy)

- Nội dung: =HLOOKUP(A2,$E$1:$H$3,2,0) [vị trí là 2 do nội dung nằm hàng 2 của bảng dữ liệu]

- Giá tiền: =HLOOKUP(A2,$E$1:$H$3,3,0) [vị trí là 2 do nội dung nằm hàng 2 của bảng dữ liệu]

Hàm vlookup/hlookup nâng cao

- Nếu mã dò tìm khác với mã trong bảng mã, thì tùy trường hợp mà học viên dùng hàm left, right, mid sao cho bảng mã dò tìm giống với bảng mã trong bảng mã (vd1)

- Nếu trường hợp có 2 bảng tra thì học viên dùng hàm if. Nếu điều kiện đúng kéo bảng 1 và f4 lại, nếu điều kiện sai thì kéo bảng 2 và f4 lại.

- Nếu trường hợp có 2 vị trí trả về trong bảng thì học viên dùng hàm if. Nếu điều kiện đúng thì trả về vị trí 1, ngược lại trả về vị trí 2 (vd2).

Vd1: Nội dung

Đề: Dựa vào kí tự đầu của mã và tra trong bảng tra.

Ta thấy mã ở ô A2 là L01 mà mã ở bảng dò tìm lại là L, C, D tức có nghĩa học viên phải sử dụng kí tự đầu tiên trong mã để tìm. Trường hợp này A2 là L01 vậy dùng hàm Left để lấy chữu L trong L01.

Vd2: Giá tiền

Đề: dựa vào kí tự đầu của mã và tra trong bảng tra. Nếu kí tự cuối của mã là 1 thì tra vào Giá 1, ngược lại tra vào Giá 2.

Ta tháy kí tự cuối của mã là 1, 2. Vậy sử dụng hàm if để trả về vị trí.

Phân tích hàm: if(right(A2,1)=”1”,3,4)

Ta đặt “1” trong ngoặc kép vì 1 nằm trong chuỗi “L01”, lấy 1 kí tự số ra thì kí tự đó là kí tư số dạng chuỗi.

Nếu lấy 1 kí tự bên phải =”1” thì trả về số 3 (tức là cột Giá 1) ngược lại trả về số 4 (tức là cột Giá 2).

Hàm Sumif

=SUMIF(range, criteria, [sum_range])

Range: Cột/ hàng điều kiện.

Criteria: Điều kiện.

Sum_range: Cột hàng cần tính.

Kết quả ra là 38, vì trong cột B1 đến B6 có 3 chữ A tưng ứng với 1, 3, 34. Tổng của 1, 3, 34 là 38.

Nếu Thay “A” thành “B” thì kết quả là 7 và “C” là 6

Giá trị dò tìm luôn luôn nằm trong ngoạc kép “” hoặc nó là 1 địa chỉ ô.

Hàm Countif(cột dữ liệu, dữ liệu cần đếm)

Hàm countif là hàm đếm trong cột/hàng dữ liệu có bao nhiêu ô = với dữ liệu cần đếm.

Nếu thay “A” thành “B” thì kết quả là 2, nếu “C” thì kết quả là 1.

Nâng cao hàm sumif, countif

Nếu mã và dữ liệu cần kiểm tra khác nhau ta ví dụ:

Thì ta sẽ dùng các kí tự thay thế ? và * để dò tìm.

Đếm có bao nhiêu mã có kí tự đầu là V: =COUNTIF($B$2:$B$12,"V*")

Tính tổng cột số lượng có kí tự đầu là V: =SUMIF($B$2:$B$12,"V*",$C$2:$C$12)

Ngoài ra nếu kí tự dò tìm nằm ở giữa thì ta kết hợp ? và * lại, ví dụ kí tự dò tìm là chữ “C” ở vị trí số 3 thì: “??C*, dò H ở vị trí số 2: “?H*”.

Sort(sắp xếp)

Bước 1. Các bạn bôi đen toàn bộ bẳng dữ liệu hoặc bôi đen tất cả các cột có chứa dữ liệu cần sắp xếp.

Sau đó các bạn vào ribbon Data trên thanh công cụ.

Bước 2. Các bạn chọn biểu tượng Sort.

Bảng hộp thoại chức năng Sort sẽ xuất hiện, các bạn thay đổi lựa chọn ở cột Column thành tên cột mà bạn muốn sắp xếp.

Còn ở cột Order, Nếu tại Column mà các bạn chọn cột dữ liệu là số thì các bạn sẽ có hai lựa chọn Smallest to Largest (nhỏ nhất đến lớn nhất) hoặc Largest to Smallest (lớn nhất đến nhỏ nhất).

Còn Column bạn lại chọn một cột dữ liệu chữ(hoặc ký tự) thì sẽ có hai lựa chọn là A to Z hoặc là Z to A.

Các bạn có thể tiếp tục tạo thêm sự sắp xếp khác ngoài cài đặt sắp xếp dữ liệu đầu tiên bằng cách click vào Add Level.

Thêm một dòng sắp xếp dữ liệu nữa sẽ xuất hiện, bạn cũng là, với phương cách tương tự như dòng trên.

Lưu ý rằng sự ưu tiên sắp xếp dữ liệu sẽ theo thứ tự từ dòng trên xuống dòng dưới. Nghĩa là sau khi sắp xếp dữ liệu theo cài đặt của dòng trên thì Excel mới tiếp tục sắp xếp thêm theo kiểu sắp xếp dữ liệu ở dòng dưới cho những dữ cụm dữ liệu giống nhau sắp xếp ở gần nhau.

Theo ví dụ ở trong ảnh thì Excel sẽ sắp xếp theo điểm tổng kết của các sinh viên, sau đó giữa các sinh viên có điểm giống nhau Excel sẽ tiếp tục sắp xếp họ theo thứ tự ABC của Họ và tên.

Bước 3. Bạn bấm vào OK để thực hiện sắp xếp dữ liệu và kết quả của bạn nhận được sẽ tương tự dưới đây:

Advanced Filter(Trích lọc dữ liệu có điều kiện)

a. Trích lọc với 1 điều kiện

Với lượng dữ liệu lớn bạn nên sử dụng cách này để trích lọc dữ liệu.

Ví dụ muốn trích lọc học sinh có Điểm thi toán lớn hơn 5.

Ta cần đặt điều kiện với tiêu đề cột và giá trị xác định điều kiện. Tên tiêu đề phải trùng với tiêu đề trong bảng cần trích lọc dữ liệu. Sau đó vào thẻ Data => chọn biểu tượng Advanced.

Hộp thoại Advanced xuất hiện, ta lựa chọn như sau:

Ÿ Tích chọn vào mục Copy to another location: để lựa chọn nội dung đã trích lọc sang vị trí mới.

Ÿ Mục List range: Kích chọn mũi tên để lựa chọn vùng dữ liệu chứa dữ liệu cần trích lọc.

Ÿ Mục Cirteria range: Lựa chọn điều kiện trích lọc dữ liệu

Ÿ Mục Copy to: Lựa chọn vị trí dán nội dung dữ liệu sau khi đã trích lọc được.

Cuối cùng kích chọn OK

Dữ liệu đã được trích lọc sang vị trí mới mà bạn đã chọn

b. Trích lọc dữ liệu với nhiều điều kiện

Với việc trích lọc dữ liệu có chứa nhiều điều kiện bạn cần chú ý,các điều kiện cùng nằm trên 1 hàng và tên tiêu đề cột chứa điều kiện trích lọc phải trùng với tên tiêu đề cột trong bảng dữ liệu nguồn.

Ví dụ cần trích lọc học sinh có Điểm thi toán và điểm thi văn lớn hơn 5. Ta thực hiện tạo cột điều kiện như hình dưới sau đó vào thẻ Data => chọn biểu tượng Advanced.

Tương tự việc lọc dữ liệu với một điều kiện, với nhiều điều kiện trong mục Cirteria range bạn lựa chọn toàn bộ điều kiện đã tạo:

Kết quả bạn đã trích lọc dữ liệu với 2 điều kiện là điểm thi toán và điểm thi văn lớn hơn 5