Tổng Hợp Kiến Thức Về Excel

Kiến thức Excel Cơ bản cho người mới

Hàm đếm và tính tổng

• Hàm đếm COUNT
• Hàm đếm ô trống COUNTBLANK
• Hàm đếm ô không trống COUNTA
• Hàm đếm COUNTIF với điều kiện cụ thể
• Hàm hàm đếm COUNTIFS với nhiều điều kiện
• Hàm tính tổng SUM
• Hàm tính tổng SUMIF có điều kiện
• Hàm tính tổng SUMIFS với nhiều điều kiện
• Hàm tính giá trị trung bình AVERAGE
• Hàm tính tổng giá tiền sản phẩm SUMPRODUCT
• Hàm MIN, MAX

Hàm Logic

• Hàm IF
• Hàm AND
• Hàm OR
• Hàm IF lồng nhau
• Hàm IFS

Hàm ngày tháng

• Hàm YEAR, MONTH, DAY
• Hàm DATE
• Hàm NOW hiển thị thời gian hệ thống
• Hàm HOUR, MINUTE, SECOND
• Hàm TIME
• Hàm DATEIF
• Hàm WEEKDAY
• Hàm TEXT
• Hàm NETWORKDAYS tính số ngày làm việc
• Hàm EOMONTH

Hàm làm việc với chuỗi văn bản

• Nối chuỗi văn bản
• Hàm LEFT
• Hàm RIGHT
• Hàm MID
• Hàm LEN
• Hàm FIND
• Hàm SUBSTITUTE thay thế văn bản- Hàm
• Cắt các khoảng trống TRIM
• Hàm LOWER, UPPER, PROPER để chuyển đổi chữ hoa, chữ thường
• Hàm EXACT để so sánh hai cột
• Hàm CONCATENATE kết hợp nội dung

Hàm tra cứu và tham chiếu

• Hàm dò tìm VLOOKUP
• Dò tìm theo hàng và cột excel
• Hàm MATCH
• Hàm INDEX
• Hàm CHOOSE

Các dạng bài tập về hàm Excel cơ bản

• Bài tập hàm Vlookup Bài tập hàm IF Bài tập hàm SUMIFS
• Bài tập hàm COUNTIF
• Bài tập hàm Mid, Right, Left
• Bài tập hàm Min, Max, Average Bài tâp hàm lấy số nguyên (Int) và số dư (mod)
• Bài tập hàm thời gian: Day, week, month
• Bài tập hàm xếp hạng Rank
• Bài tập về lập báo cáo tình hình doanh thu Câu lạc bộ
• Bài tập hàm excel cơ bản: Toán tử, Logic và Số học
• Bài tập về thời gian: Tách số tuần và số ngày
• Bài tập Lập bảng kê nhập xuất nông sản
• Bài tập Lập báo cáo doanh thu trên excel
• Bài tập nâng cao: Lập bảng kê nhập hàng/ nguyên vật liệu
• Bài tập về Quản lý cho thuê phòng khách sạn, nhà nghỉ
• Bài tập Tính điểm trung bình và xếp loại học sinh
• Bài tập vẽ biểu đồ trong excel
• Bài tập về tính toán trên nhiều sheet: Tính tổng đơn giản

Các dạng bài tập về hàm Excel nâng cao

• Bài tập hàm excel nâng cao: Kết hợp nhiều hàm Vlookup Hlookup Match Index IF
• Bài tập hàm vlookup nâng cao ứng dụng cho việc lập báo cáo bán hàng thực tế
• Bài tập về Hàm hlookup kết hợp hàm left, value
• Bài tập hàm IF lồng nhiều hàm IF
• Bài tập hàm IF kết hợp hàm And và hàm OR Bài tập nâng cao hàm xử lý chuỗi trong excel
• Bài tập số 8 Tổng hợp tiêu thụ nhiên liệu

Bài tập excel cơ bản cho người mới bắt đầu với hàm IF, ROUND, SUM, MIN, MAX, AVERAGE

Cho đề bài và bảng dữ liệu như sau

Tính phụ cấp chức vụ tại bài tập excel lần này, cần sử dụng hàm IF

Điều kiện phụ cấp chức vụ là: NV thêm 100, KT thêm 150, TP thêm 300, PGĐ thêm 350, GĐ thêm 500. Thay vì lần theo danh mục chức vụ rồi điền vào, chỉ cần sử dụng hàm IF theo cú pháp sau: =IF(D2=”NV”,100,IF(D2=”KT”,150,IF(D2=”TP”,300,IF(D2=”PGĐ”,350,IF(D2=”GĐ”,500))))) 

Sau khi áp dụng công thức cho ô đầu tiên, bạn kéo chuột để sao chép cú pháp hàm IF xuống các ô còn lại để có được kết quả.

Để tính lương, thực hiện công thức: =F2*G2 sau đó kéo chuột để copy công tức này áp dụng cho các hàng sau.

Tính được tạm ứng trong bài tập excel kết hợp HÀM ROUND và hàm IF

Để tính được tạm ứng ta thực hiện cú pháp như sau: =ROUND(IF((G2+H2)*2/3<70000,(G2+H2)*2/3,70000),-3).

Sau khi áp dụng công thức cho ô đầu tiên, bạn kéo chuột để sao chép cú pháp xuống các ô còn lại để có được kết quả.

Như vậy, để tính phần còn lại, thực hiện công thức: = G2 + H2 – I2 sau đó kéo chuột để copy công tức này áp dụng cho các hàng sau.

Tính tổng cộng cần sử dụng hàm SUM

Công thức để thực hiện tính tổng là: =SUM(H2:H12). Sau khi áp dụng công thức, bạn copy công thức qua 2 cột kế bên nhé!

Tính trung bình cần sử dụng hàm AVERAGE

Để tính trung bình, bạn áp dụng công thức: =AVERAGE(H2:H12). Tương tự như tinih1 tổng, bạn kéo công thức sang 2 cột ngang để cùng áp dụng cú pháp này.

Tìm giá trị lớn nhất và nhỏ nhất cần dùng hàm MIN và hàm MAX

• Giá trị cao nhất sẽ được tính theo công thức: =MIN(H2:H12)
• Giá trị thấp nhất sẽ được tính theo công thức: =MAX(H2:H12)

Hướng dẫn giải bài tập Excel với hàm IF và hàm RANK

Cho đề bài và bảng dữ liệu sau:

• Sử dụng phương ph
• áp lồng nhiều hàm IF trong một công thức để xếp loại học sinh: =IF(C3>=9;”Giỏi”;IF(AND(C3<9;C3>=7);”Khá”;IF(AND(C3<7;C3>=5);”Trung Bình”;”Yếu”))) 

• Dùng hàm xếp hạng RANK: =RANK(C3;$C$3:$C$9;0), với dãy truy xuất được cố định lại $…$ bằng cách nhấn F4.

Bài tập Excel phân biệt hàm VLOOKUP và hàm HLOOKUP

Công dụng của hàm VLOOKUP và hàm HLOOKUP

Điểm chung của 2 hàm này là đều dùng để tìm kiếm thông tin, rút trích dữ liệu. Tuy nhiên, dù cùng một công dụng nhưng chúng khác nhau ở cách sử dụng:

• Hàm VLOOKUP được sử dụng khi tìm kiếm dữ liệu ở các cột dọc của Excel
• Hàm HLOOKUP được sử dụng để tìm kiếm các dữ liệu ở hàng ngang của Excel

Cú pháp hàm VLOOKUP và hàm HLOOKUP

Cú pháp:

• Hàm VLOOKUP: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]).
• Hàm HLOOKUP:  =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup]). 

Trong đó:

• lookup_value là giá trị cần dò tìm
• table_array là bảng giới hạn để dò tìm
• row_index_num là số thứ tự của hàng lấy dữ liệu trong bảng cần dò tìm, tính từ trái qua phải
• range_lookup là giá trị Logic (TRUE=1, FALSE=0) xác định Excel cần so sánh, tìm kiếm chính xác hay so sánh, tìm kiếm tương đối với bảng giới hạn.

Ví dụ bài tập Excel phân biệt hàm VLOOKUP và HLOOKUP

Cho bài tập Excel sau. Tìm kiếm thông tin TÊN NGÀNH bằng hàm HLOOKUP để đưa thông tin tương ứng từ bảng NGÀNH HỌC vào ô trống.

Hướng dẫn giải

Cú pháp đầy đủ của hàm HLOOKUP: =HLOOKUP(LEFT(C3,1),$B$12:$E$13,2,0)

Để hoàn thành cột CỘNG ĐIỂM bằng tổng 3 môn toán hóa sinh, thực hiện đơn giản như sau: CỘNG ĐIỂM = TOÁN + SINH + HÓA. Khi đó, cú pháp hàm VLOOKUP là: =VLOOKUP(VALUE(RIGHT(C3,1)),$B$17:$C$20,2,0)

Để hoàn thành cột TỔNG CỘNG của bài tập Excel bằng tổng điểm cộng và điểm ưu tiên. Khi đó, công thức sẽ là = H1+I1

Bài tập hàm VLOOKUP và HLOOKUP

Cho bảng dữ liệu và đề bài sau:

Hướng dẫn giải:

• Cú pháp tra cứu đơn giá: =VLOOKUP(A2,$A$12:$C$18,3,0)
• Tính lượng sản phẩm được khuyến mãi: =IF(C2<=100,0,IF(C2<=230,25,IF(C2<=305,30,IF(C2<=410,35))))
• Cú pháp cột thành tiền: =(D2-E2)*C2
• Cú pháp cột tổng tiền: =SUM(F2:F7)