Công nghệ số & AI – Buổi 7: Excel và các hàm cơ bản

Địa chỉ tương đối, tuyệt đối và các hàm SUM, AVERAGE, IF, AND, RANK, LEFT, MID, VLOOKUP, HLOOKUP, COUNTIF, SUMIF, FILTER qua bảng lương, bảng điểm, tuyển sinh.

Công nghệ số & AI – Buổi 7: Excel và các hàm cơ bản
Nội dung này có sự hỗ trợ của công cụ trí tuệ nhân tạo (AI).

Mục tiêu buổi 7: thao tác cơ bản trong Excel (nhập, định dạng, thêm bớt dòng cột, lưu tệp); hiểu và dùng các hàm SUM, AVERAGE, MAX, MIN, COUNT, COUNTIF, IF, AND, OR, RANK, LEFT, RIGHT, MID, VLOOKUP, HLOOKUP, SUMIF, FILTER; xây dựng bảng tính thực tế như bảng lương, bảng điểm, kết quả tuyển sinh; vẽ biểu đồ.

Video hướng dẫn

Bài thực hành 7, Bài 1: Bảng lương
▶ Video: Bài thực hành 7, Bài 1: Bảng lương
Bài thực hành 7, Bài 4: Kết quả tuyển sinh
▶ Video: Bài thực hành 7, Bài 4: Kết quả tuyển sinh
Hàm LEFT, RIGHT, MID
▶ Video: Hàm LEFT, RIGHT, MID

1. Kiến thức nền

  • Công thức luôn bắt đầu bằng dấu =. Kéo Fill Handle (ô vuông nhỏ góc phải dưới ô) để sao chép công thức xuống các dòng.
  • Địa chỉ tương đối (A3) thay đổi khi sao chép; tuyệt đối ($H$13) giữ nguyên. Nhấn F4 để thêm dấu $. Ô tỷ giá, điểm chuẩn, bảng tra cứu luôn để tuyệt đối.
  • Dấu phân cách đối số là , hoặc ; tuỳ cài đặt Region của máy.
  • Chuỗi trong công thức đặt trong ngoặc kép: "BV", "Đậu".
  • STT tự động: =ROW()-ROW($A$2) (A2 là ô tiêu đề cột TT).

2. Các hàm cần thuộc

HàmCông dụngVí dụ
SUM, AVERAGETổng, trung bình=AVERAGE(D3:G3)
MAX, MINLớn nhất, nhỏ nhất=MAX(H3:H10)
COUNTĐếm ô chứa số=COUNT(H3:H10)
COUNTIFĐếm ô thoả điều kiện=COUNTIF(K3:K10,"Đậu"), =COUNTIF(J3:J10,">=20")
SUMIFTổng theo điều kiện=SUMIF(B3:B11,"Intel",G3:G11)
ROUNDLàm tròn=ROUND(AVERAGE(D3:G3),1)
IFChọn giá trị theo điều kiện=IF(C3="BV",500000,0)
AND, ORKết hợp nhiều điều kiện=IF(AND(H3>=8,F3>=8),220000,0)
RANKXếp hạng (0: giảm dần)=RANK(H3,$H$3:$H$10,0)
LEFT, RIGHTLấy n ký tự đầu, cuối=LEFT(C3,2), =RIGHT(A3,3)
MIDLấy ký tự ở giữa=MID(B3,4,1) (ký tự thứ 4)
VLOOKUPTra cứu bảng dọc=VLOOKUP(D3,$B$13:$C$18,2,0)
HLOOKUPTra cứu bảng ngang=HLOOKUP(LEFT(B3,3),$B$14:$D$15,2,0)
FILTERLọc dữ liệu ra vùng mới=FILTER(A3:J10,J3:J10="Đậu")

IF lồng nhau: xếp loại

=IF(H3>=8,"Giỏi",IF(H3>=7,"Khá",IF(H3>=5,"Trung bình","Yếu")))

Xét điều kiện từ cao xuống thấp; mỗi IF mở thì cuối công thức phải đóng đủ ngoặc.

VLOOKUP và HLOOKUP

=VLOOKUP(giá trị tìm, bảng tra, số thứ tự cột lấy, 0)

  • Giá trị tìm phải nằm ở cột đầu tiên (VLOOKUP) hoặc dòng đầu tiên (HLOOKUP) của bảng tra.
  • Đối số cuối 0 (FALSE) là tìm chính xác, gần như luôn dùng.
  • Kết hợp với LEFT/RIGHT/MID khi mã chứa thông tin: =VLOOKUP(LEFT(B3,2),$A$15:$E$17,2,0).
  • Đơn giá theo loại hàng: chọn số cột bằng IF, ví dụ =VLOOKUP(C3,$B$15:$E$17,IF(E3="1",2,IF(E3="2",3,4)),0).
Lỗi #N/A: không tìm thấy giá trị (sai chính tả, thừa dấu cách, quên $ khiến bảng tra bị trượt). Kết quả của LEFT/MID là chữ, nên "1" khác số 1.

FILTER

Có trong Excel 2021 và Microsoft 365 (bản cũ dùng Data → Filter hoặc Advanced Filter). Nhiều điều kiện "và" thì nhân, "hoặc" thì cộng:

=FILTER(A3:G11,(B3:B11="ECS")*(D3:D11>=100))

3. Sắp xếp và biểu đồ

  • Sắp xếp: chọn bảng (không chọn dòng tiêu đề lớn) → Data → Sort → Add Level: sắp theo Tên, trùng tên thì theo Họ.
  • Biểu đồ: chọn vùng dữ liệu → Insert → Charts. Cột để so sánh, tròn để thể hiện tỷ lệ phần trăm (ví dụ tỷ lệ đậu/trượt).
  • Định dạng số: dấu phân cách hàng nghìn, tiền tệ, số chữ số thập phân (Home → Number).

Bài thực hành

  • Xem tại bài thực hành đã in và file PDF giảng viên cung cấp.

Buổi tiếp theo: Excel nâng cao với ngày giờ, INDEX/MATCH, Subtotal và PivotTable.