‹
Tin học đại cươngChương 6 · CHƯƠNG 6 MICROSOFT EXCEL CƠ BẢN — BẢNG TÍNH, CÔNG THỨC VÀ HÀM

CHƯƠNG 6 MICROSOFT EXCEL CƠ BẢN — BẢNG TÍNH, CÔNG THỨC VÀ HÀM

Mục tiêu chương

Sau khi học xong chương này, người học có thể:

• Làm việc với workbook, worksheet, ô và vùng; nhập dữ liệu đúng kiểu (chữ, số, ngày) và hiểu vì sao Excel hiển thị khác với giá trị thật.

• Định dạng bảng tính dễ đọc: định dạng số, tiền, ngày, căn lề, đường viền, màu nền; sao chép định dạng; cố định tiêu đề; kiểm soát dữ liệu nhập bằng Data Validation.

• Viết công thức đúng, phân biệt địa chỉ tương đối, tuyệt đối, hỗn hợp; đặt tên vùng; tham chiếu giữa các sheet; đọc và sửa các lỗi công thức thường gặp.

• Dùng thành thạo nhóm hàm cơ bản: toán học, thống kê, chuỗi, ngày tháng, logic (IF, AND, OR, IFERROR) và hàm có điều kiện (SUMIF, COUNTIF, AVERAGEIF).

• Sắp xếp, lọc, chuyển vùng dữ liệu thành Table; thiết lập trang in, xuất PDF, đưa bảng sang Word đúng Quy định; áp dụng vào dự toán, bảng điểm, lịch quay.

Vị trí của chươngChương 6 là phần cơ bản của Excel (đề cương: Chương Microsoft Excel, buổi 9–13). Chương 7 tiếp tục với Excel nâng cao: hàm tra cứu, hàm điều kiện nhiều tiêu chí, biểu đồ, PivotTable. Trong chương này, mọi hình và bảng đều rộng không quá 75% bề rộng phần chữ theo Quy định hình thức trình bày của Trường (xem Chương 5).

6.1. Tổng quan về Microsoft Excel

6.1.1. Bảng tính trong công việc của người làm sáng tạo

Bảng tính (spreadsheet) là phần mềm để nhập, lưu trữ, tính toán và trình bày dữ liệu dạng bảng. Microsoft Excel là bảng tính phổ biến nhất. Người làm sân khấu, điện ảnh, mỹ thuật dùng Excel nhiều hơn họ tưởng:

• Dự toán và quyết toán kinh phí: phim ngắn, vở diễn, triển lãm — hạng mục, số lượng, đơn giá, thành tiền, thuế.

• Lịch quay, lịch tập, lịch diễn: ngày bắt đầu, kết thúc, địa điểm, người phụ trách; tính số ngày quay, hạn giao sản phẩm.

• Quản lý thiết bị, đạo cụ, trang phục: danh sách, tình trạng, người mượn, ngày trả.

• Thống kê: phòng vé, lượt xem, điểm số của người học, khảo sát khán giả.

• Nguồn dữ liệu cho tài liệu khác: danh sách người nhận cho trộn thư ở Chương 5, số liệu cho biểu đồ ở Chương 7.

Google Sheets (Chương 2) cũng là bảng tính, làm việc nhóm trực tuyến rất tiện và có phần lớn hàm giống Excel. Chương này dạy trên Excel; kiến thức về ô, công thức, hàm áp dụng gần như nguyên vẹn cho Google Sheets.

6.1.2. Giao diện Excel

Hình minh hoạ

Hình 6.1 — Giao diện Microsoft Excel 365 (bản tiếng Anh): Ribbon, Name Box, Formula Bar, ô hiện hành, tiêu đề cột và dòng, thẻ trang tính, thanh trạng thái.

Bảng 6.1 — Các thành phần giao diện Excel

Thành phầnMô tảGhi chú
RibbonThanh lệnh gồm các tab: File, Home, Insert, Page Layout, Formulas, Data, Review, ViewCtrl + F1 thu gọn hoặc mở rộng
Name BoxHộp tên: hiện địa chỉ ô đang chọn, nhập địa chỉ để nhảy tớiCũng dùng để đặt tên vùng
Formula BarThanh công thức: xem và sửa nội dung thật của ôF2 sửa ngay trong ô
Ô hiện hànhÔ đang chọn, viền đậmMọi thao tác nhập tác động vào ô này
Tiêu đề cột, dòngChữ cái A, B, C… ở trên và số 1, 2, 3… ở bên tráiNhấp để chọn cả cột, cả dòng
Worksheet tabThẻ tên các trang tính ở dưới cùngDấu + thêm trang tính mới
Status barThanh trạng thái: khi chọn vùng số, hiện Sum, Average, CountXem nhanh không cần công thức
Fill handleChấm vuông nhỏ ở góc dưới phải ô chọnKéo để điền tự động

6.1.3. Workbook, worksheet, ô và vùng

• Workbook (sổ tính): một file Excel (.xlsx), chứa một hoặc nhiều worksheet.

• Worksheet (trang tính): một bảng gồm 1.048.576 hàng (dòng) và 16.384 cột; cột được đặt tên A, B, …, Z, AA, …, XFD.

• Ô (cell): giao của một hàng và một cột, có địa chỉ gồm chữ cột và số hàng, ví dụ B3 là cột B, hàng 3.

• Vùng (range): nhóm ô liền nhau, viết bằng ô đầu và ô cuối cách nhau dấu hai chấm, ví dụ A1:C5 (3 cột × 5 hàng). Vùng rời rạc dùng dấu phẩy (hoặc chấm phẩy theo máy), ví dụ A1:A5,C1:C5.

• Tham chiếu sang trang tính khác: dạng 'Tên sheet'!A1, ví dụ 'DuToan'!B5.

6.1.4. File Excel và quản lý trang tính

Bảng 6.2 — Các định dạng file thường gặp trong Excel

ĐuôiLà gìDùng khi
.xlsxSổ tính chuẩn hiện nay, không macroLưu làm việc hằng ngày
.xlsĐịnh dạng cũ (Excel 97–2003)Chỉ khi người nhận dùng phần mềm rất cũ
.xlsmSổ tính có macroCần chạy macro; cẩn thận khi mở (Chương 5)
.xltxMẫu bảng tínhLàm khuôn cho bảng tính mới
.csvVăn bản thuần, cột phân cách bằng dấu phẩy hoặc chấm phẩyTrao đổi dữ liệu giữa phần mềm; chọn CSV UTF-8 để không mất dấu tiếng Việt
.pdfBản in cố địnhNộp bài, gửi người không cần sửa

• Lưu file: Ctrl + S; Save As (F12) để đặt tên theo quy ước ở Chương 1, ví dụ DuToan_PhimNgan_2026-11-02_v01.xlsx. Sao lưu theo quy tắc 3-2-1.

• Trang tính: chuột phải vào thẻ để Rename (đặt tên có nghĩa: "BangDiem", "DuToan"), Insert, Delete, Move or Copy, Tab Color. Kéo thẻ để đổi thứ tự.

• Cẩn thận khi chọn nhiều trang tính cùng lúc (Group): thao tác nhập, xóa sẽ xảy ra trên tất cả; nhấp vào thẻ khác để bỏ nhóm.

6.2. Dữ liệu và thao tác với dữ liệu

6.2.1. Các kiểu dữ liệu

Mỗi ô chứa một giá trị thuộc một kiểu; kiểu quyết định cách Excel hiển thị và tính toán. Nhìn cách căn lề mặc định có thể đoán kiểu: chữ căn trái, số căn phải.

Bảng 6.3 — Các kiểu dữ liệu trong Excel

KiểuĐặc điểmVí dụ
Văn bản (Text)Chữ, hoặc số được xử lý như chữ; căn tráiNguyễn Văn A; SV26101
Số (Number)Dùng để tính; căn phải8,5; 2500000
Ngày, giờ (Date, Time)Thực chất là số: 1 là ngày 01/01/1900, mỗi ngày tăng 102/11/2026
Logic (Boolean)Chỉ có hai giá trịTRUE; FALSE
Công thức (Formula)Bắt đầu bằng dấu =; ô hiện kết quả, thanh công thức hiện công thức=C2*D2
Lỗi (Error)Kết quả công thức không tính được#DIV/0!; #NAME?
Ngày thực chất là sốVì ngày là số nên tính được hiệu hai ngày (số ngày giữa hai mốc), cộng thêm ngày, so sánh ngày trước, sau. Nếu một ô ngày bỗng hiện số như 46328, đó là ngày nhưng ô đang định dạng Number; đổi sang Date để xem ngày. Ngược lại, ngày gõ sai kiểu (ví dụ 2.11.2026) có thể bị Excel hiểu thành chữ và không tính được.

6.2.2. Nhập dữ liệu

• Enter xuống ô dưới, Tab sang ô phải, Esc hủy nhập, F2 sửa nội dung ô, Alt + Enter xuống dòng trong cùng một ô.

• Ctrl + Enter: nhập xong, nội dung điền vào mọi ô đang chọn (chọn vùng trước, gõ, rồi Ctrl + Enter).

• Ctrl + ; chèn ngày hôm nay; Ctrl + Shift + ; chèn giờ hiện tại (đây là giá trị cố định, không tự đổi).

• Mã và số điện thoại có số 0 đầu (0912…, 00123): đặt ô dạng Text trước khi nhập (Home > Number > Text) hoặc gõ dấu nháy đơn ' ở đầu. Nếu không Excel coi là số và bỏ số 0 đầu — lỗi này làm hỏng trộn thư ở Chương 5.

• Ngày: gõ theo kiểu máy đang dùng, thường là dd/mm/yyyy ở máy cài vùng Việt Nam, ví dụ 02/11/2026.

Dấu phẩy, dấu chấm và dấu chấm phẩy tùy cài đặt vùngExcel đọc dấu thập phân và dấu phân cách đối số hàm theo cài đặt Region của Windows. Máy cài vùng Việt Nam thường dùng dấu phẩy làm dấu thập phân (8,5) và dấu chấm phẩy ; để phân cách các đối số hàm: =SUM(A1;A5). Máy cài vùng Mỹ dùng dấu chấm thập phân (8.5) và dấu phẩy phân cách đối số: =SUM(A1,A5). Trong chương này công thức viết theo kiểu dấu phẩy; nếu Excel của người học báo lỗi công thức, hãy thử đổi dấu phẩy thành dấu chấm phẩy. Kiểm tra ở Control Panel > Region > Additional settings.

6.2.3. Chọn ô, vùng và di chuyển nhanh

Bảng 6.4 — Phím tắt chọn và di chuyển

Thao tácPhím tắt
Chọn toàn bộ trang tínhCtrl + A
Chọn cả cột, cả dòngCtrl + Space; Shift + Space
Nhảy đến cuối vùng dữ liệu theo hướng mũi tênCtrl + phím mũi tên
Chọn từ ô hiện hành đến cuối vùng dữ liệuCtrl + Shift + phím mũi tên
Về ô A1; đến ô cuối cùng có dữ liệuCtrl + Home; Ctrl + End
Đến ô hoặc vùng theo địa chỉF5 hoặc Ctrl + G; hoặc gõ vào Name Box
Chọn nhiều vùng rời rạcGiữ Ctrl khi nhấp hoặc kéo

6.2.4. Sao chép, cắt, dán và Paste Special

Ctrl + C sao chép, Ctrl + X cắt, Ctrl + V dán, Ctrl + Z hoàn tác. Khi dán, nút Paste Options hiện lên cho chọn cách dán. Với kiểu dán đặc biệt, dùng Home > Paste > Paste Special (Ctrl + Alt + V):

• Values: chỉ dán giá trị (bỏ công thức) — dùng khi muốn "chốt" kết quả.

• Formats: chỉ dán định dạng.

• Formulas: chỉ dán công thức.

• Transpose: đổi hàng thành cột và ngược lại.

• Column widths: dán độ rộng cột.

6.2.5. Điền tự động (AutoFill)

Hình minh hoạ

Hình 6.2 — Vùng A2:B9 được điền tự động từ hai ô đầu: dãy số thứ tự và dãy ngày; chấm vuông ở góc dưới phải vùng chọn là Fill handle.

• Kéo Fill handle: kéo chấm vuông ở góc ô xuống hoặc sang ngang. Gõ 1, 2 ở hai ô rồi chọn cả hai và kéo để điền dãy 3, 4, 5… Gõ "Thứ Hai" hoặc "January" rồi kéo để điền dãy ngày, tháng theo danh sách có sẵn.

• Nhấp đôi vào Fill handle: điền xuống đến hết vùng dữ liệu bên cạnh — nhanh nhất khi sao công thức xuống hàng trăm dòng.

• Nút Auto Fill Options: hiện sau khi kéo, chọn Copy Cells (chép nguyên), Fill Series (tạo dãy), Fill Formatting Only…

• Home > Fill > Series: tạo dãy số hoặc ngày với bước nhảy và giá trị cuối tự chọn.

• Flash Fill (Ctrl + E): từ Excel 2013 trở đi, gõ vài ô mẫu (ví dụ tách tên từ họ tên), Excel đoán quy luật và điền phần còn lại; luôn kiểm tra lại kết quả.

6.2.6. Chèn, xóa, ẩn dòng cột và cố định tiêu đề

• Chèn, xóa: chuột phải vào tiêu đề dòng hoặc cột > Insert / Delete. Ctrl + Shift + + (dấu cộng) để chèn, Ctrl + - (dấu trừ) để xóa.

• Độ rộng, chiều cao: kéo đường biên giữa hai tiêu đề; nhấp đôi vào đường biên để tự khít với nội dung (AutoFit). Ô hiện ###### nghĩa là cột quá hẹp để hiện số hoặc ngày.

• Ẩn, hiện: chuột phải vào tiêu đề > Hide / Unhide. Dòng, cột ẩn vẫn tham gia tính toán.

• Wrap Text (Home > Wrap Text): chữ dài tự xuống dòng trong ô, thay vì tràn sang ô bên cạnh.

• Freeze Panes (View > Freeze Panes > Freeze Top Row): cố định hàng tiêu đề khi cuộn bảng dài.

• Merge & Center: gộp ô để làm tiêu đề bảng báo cáo thì được; không gộp ô trong vùng dữ liệu vì làm hỏng sắp xếp, lọc, công thức. Thay bằng Center Across Selection (Format Cells > Alignment > Horizontal).

6.2.7. Kiểm soát dữ liệu nhập: Data Validation

Gõ "Điện ảnh" ở ô này, "Dien anh" ở ô khác thì bảng thống kê và lọc sẽ coi là hai ngành. Data Validation (Data > Data Validation) hạn chế nội dung được nhập vào ô:

• Allow > List: ô thành danh sách chọn; nguồn gõ trực tiếp (Điện ảnh,Mỹ thuật,Sân khấu — phân cách bằng dấu phẩy hoặc chấm phẩy theo máy) hoặc chọn một vùng ô.

• Whole number, Decimal, Date: giới hạn khoảng giá trị, ví dụ điểm từ 0 đến 10.

• Input Message: hiện gợi ý khi chọn ô. Error Alert: hiện cảnh báo khi nhập sai (kiểu Stop chặn hẳn, Warning cho chọn tiếp).

• Xóa quy tắc: chọn ô > Data Validation > Clear All.

6.3. Định dạng dữ liệu

6.3.1. Hộp thoại Format Cells

Chọn ô hoặc vùng rồi bấm Ctrl + 1 (hoặc chuột phải > Format Cells) để mở hộp thoại định dạng đầy đủ với sáu tab: Number (số), Alignment (căn lề), Font (chữ), Border (đường viền), Fill (màu nền), Protection (bảo vệ).

Hình minh hoạ

Hình 6.3 — Hộp thoại Format Cells (Ctrl + 1), tab Number; các tab Alignment, Font, Border, Fill, Protection nằm cùng hàng.

6.3.2. Định dạng số, tiền và ngày

Định dạng chỉ đổi cách hiển thị, không đổi giá trị thật trong ô. Ô chứa 8,456 định dạng một chữ số thập phân sẽ hiện 8,5 nhưng khi tính vẫn dùng 8,456. Nếu cần đổi giá trị thật, dùng hàm ROUND (6.5.2).

Bảng 6.5 — Các kiểu định dạng số

Kiểu (Category)Hiển thịDùng cho
GeneralNhư gõ vàoMặc định
NumberSố có số chữ số thập phân và dấu phân cách hàng nghìnĐiểm, số lượng, tiền không ký hiệu
Currency, AccountingSố kèm ký hiệu tiền tệBáo giá, dự toán
PercentageNhân 100 và thêm %: 0,1 hiện 10%Tỷ lệ, thuế
Date, TimeNgày, giờ theo mẫu chọnLịch quay, hạn nộp
TextGiữ nguyên như chữ, không tínhMã, số điện thoại số 0 đầu
CustomMẫu tự đặt, ví dụ #,##0 hoặc dd/mm/yyyyĐịnh dạng riêng

Một số mẫu Custom hay dùng: #,##0 (số nguyên có dấu phân cách hàng nghìn), 0.0 (một chữ số thập phân), dd/mm/yyyy (ngày kiểu Việt Nam), 0%. Dấu phân cách trong hộp thoại hiển thị theo cài đặt vùng của máy.

6.3.3. Định dạng chữ, căn lề, viền, màu nền

• Chữ: Home > Font: phông, cỡ, đậm (Ctrl + B), nghiêng (Ctrl + I), gạch chân (Ctrl + U), màu chữ.

• Căn lề: ngang (trái, giữa, phải) và dọc (trên, giữa, dưới); tăng giảm thụt lề; xoay chữ.

• Đường viền, màu nền: Home > Borders, Fill Color. Dùng ít màu, có chủ đích.

• Format Painter (chổi sơn): chọn ô mẫu, bấm chổi, tô lên vùng cần áp dụng; nhấp đôi vào chổi để tô nhiều lần.

• Cell Styles: bộ định dạng có tên (Heading, Good, Bad, Input…) áp dụng bằng một lần nhấp, giống ý tưởng style trong Word (Chương 4).

6.3.4. Nguyên tắc trình bày bảng tính dễ đọc

1. Mỗi bảng dữ liệu là một vùng liền, hàng 1 là tiêu đề, không có dòng hoặc cột trống xen giữa, không gộp ô trong vùng dữ liệu.

2. Hàng tiêu đề in đậm, có màu nền nhạt; cố định bằng Freeze Panes khi bảng dài.

3. Chữ căn trái, số căn phải; cùng một cột dùng cùng một kiểu định dạng.

4. Ghi đơn vị ngay ở tiêu đề cột (đồng, ngày, %) thay vì gõ đơn vị vào từng ô số.

5. Tách rõ dữ liệu nhập (do người dùng gõ) với kết quả tính (do công thức); có thể tô nền khác nhau.

6. Không tô quá nhiều màu; in ra trắng đen vẫn phải đọc được.

7. Đặt tên trang tính có nghĩa; thông số dùng chung (thuế, tỷ giá) để ở một ô riêng có nhãn, đừng gõ cứng trong công thức.

6.4. Công thức và địa chỉ

6.4.1. Công thức là gì

Công thức (formula) là biểu thức tính toán bắt đầu bằng dấu =; ô hiện kết quả, thanh công thức hiện công thức. Khi dữ liệu trong các ô được tham chiếu thay đổi, kết quả tự cập nhật — đây là sức mạnh chính của bảng tính. Ví dụ ô D2 chứa =B2*C2 (số lượng nhân đơn giá).

Bảng 6.6 — Toán tử trong công thức

Toán tửÝ nghĩaVí dụ
+ - * /Cộng, trừ, nhân, chia=A1+B1
^Lũy thừa=A1^2
%Phần trăm=A1*10%
&Nối chuỗi="SV"&A1
= <> < > <= >=So sánh (cho kết quả TRUE hoặc FALSE)=A1>=5

Thứ tự ưu tiên: ngoặc đơn trước, rồi lũy thừa, rồi nhân chia, rồi cộng trừ. Ví dụ =2+3*4 cho 14; =(2+3)*4 cho 20. Cần chắc chắn thì thêm ngoặc.

6.4.2. Địa chỉ tương đối, tuyệt đối và hỗn hợp

Khi sao chép công thức xuống dòng khác, Excel tự điều chỉnh địa chỉ theo vị trí mới. Đó là hành vi mặc định (địa chỉ tương đối) và thường là điều ta muốn. Nhưng có trường hợp cần giữ cố định một ô, ví dụ ô chứa mức thuế VAT; khi đó dùng địa chỉ tuyệt đối với dấu $.

Bảng 6.7 — Ba loại địa chỉ

LoạiCách viếtKhi sao chép
Tương đốiA1Cả cột và hàng đều đổi theo vị trí mới
Tuyệt đối$A$1Cột và hàng đều cố định
Hỗn hợp$A1 hoặc A$1Cố định cột (hoặc hàng), phần còn lại đổi

Đặt con trỏ trong địa chỉ khi đang gõ công thức rồi bấm F4 để chuyển vòng A1 → $A$1 → A$1 → $A1.

Ví dụ: bảng dự toán có cột SoLuong (C), DonGia (D); ô G1 chứa mức thuế 10%. Thành tiền ở E2 là =C2*D2; thuế ở F2 là =E2*$G$1. Khi kéo công thức F2 xuống F3, F4…, phần E2 đổi thành E3, E4… còn $G$1 giữ nguyên. Nếu quên dấu $ (viết =E2*G1), kéo xuống F3 sẽ thành =E3*G2, trỏ vào ô trống và cho kết quả 0 hoặc sai.

Hình minh hoạ

Hình 6.4 — Chế độ Show Formulas (Ctrl + `): công thức =E2*$G$1 sao xuống các dòng, địa chỉ $G$1 giữ nguyên còn E2, E3… đổi theo dòng.

6.4.3. Đặt tên vùng

Thay vì nhớ $G$1 là thuế, hãy đặt cho ô đó cái tên VAT; công thức thành =E2*VAT, dễ đọc và không sợ sai khi sao chép.

1. Chọn ô hoặc vùng cần đặt tên.

2. Gõ tên vào Name Box (bên trái thanh công thức) rồi Enter; hoặc Formulas > Define Name.

3. Quản lý tên: Formulas > Name Manager (Ctrl + F3) để sửa, xóa, xem phạm vi của từng tên.

4. Tạo hàng loạt từ tiêu đề cột: chọn bảng có tiêu đề > Formulas > Create from Selection (Ctrl + Shift + F3).

Quy tắc đặt tên: bắt đầu bằng chữ hoặc dấu gạch dưới; không có dấu cách (dùng gạch dưới: Thue_VAT); không trùng địa chỉ ô (không đặt tên A1 hay SV1); nên viết không dấu và ngắn gọn.

Hình minh hoạ

Hình 6.5 — Hộp thoại Name Manager (Ctrl + F3) với tên vùng VAT trỏ tới ô $G$1 của trang tính DuToan.

6.4.4. Tham chiếu giữa các trang tính

Công thức có thể lấy dữ liệu từ trang tính khác: =DuToan!E12 hoặc =SUM(DuToan!E2:E11). Tên trang tính có dấu cách hoặc dấu tiếng Việt thì đặt trong dấu nháy đơn: ='Du toan'!E12. Cách dễ nhất là gõ dấu =, nhấp sang thẻ trang tính kia rồi nhấp ô cần lấy; Excel tự viết địa chỉ đúng.

6.4.5. Công cụ kiểm tra công thức

• Show Formulas (Ctrl + `, phím nằm trên phím Tab): hiện toàn bộ công thức thay cho kết quả; bấm lại để quay về.

• F2: vào sửa công thức; các ô được tham chiếu hiện khung màu tương ứng với màu địa chỉ trong công thức.

• Formulas > Trace Precedents / Trace Dependents: vẽ mũi tên chỉ ô nào đưa dữ liệu vào, hoặc ô nào dùng kết quả của ô này.

• Formulas > Evaluate Formula: chạy từng bước tính của công thức dài.

• AutoSum (Alt + =): tự điền hàm SUM cho vùng số liền kề.

• Status bar: chọn vùng số, đọc ngay Sum, Average, Count ở cuối cửa sổ.

6.4.6. Các lỗi công thức thường gặp

Bảng 6.8 — Các giá trị lỗi thường gặp

LỗiNguyên nhânCách xử lý
#DIV/0!Chia cho 0 hoặc ô trốngKiểm tra mẫu số; dùng IFERROR
#NAME?Sai tên hàm hoặc tên vùng; chữ không có dấu nháy képSoát chính tả; kiểm tra dấu phân cách
#VALUE!Phép tính với kiểu dữ liệu không hợp: cộng chữ với sốKiểm tra ô là Text hay Number
#REF!Tham chiếu tới ô đã bị xóaSửa lại tham chiếu; hoàn tác xóa
#N/AHàm tra cứu không tìm thấy giá trịKiểm tra dữ liệu tra cứu (Chương 7)
#NUM!Số không hợp lệ (căn bậc hai của số âm…)Kiểm tra đối số
######Cột quá hẹp để hiện số hoặc ngàyNhấp đôi biên cột để mở rộng
Tham chiếu vòng (Circular Reference)Nếu công thức trong ô tham chiếu chính ô đó (ví dụ gõ =A1+1 vào ô A1), Excel báo "circular reference". Sửa lại công thức để không trỏ vào ô chứa nó.

6.5. Các hàm cơ bản

6.5.1. Cách sử dụng hàm

Hàm (function) là công thức dựng sẵn. Cấu trúc chung: =TÊN_HÀM(đối số 1, đối số 2, …); đối số có thể là số, địa chỉ ô, vùng, chuỗi trong dấu nháy kép hoặc một hàm khác (hàm lồng nhau). Ba cách nhập hàm:

• Gõ trực tiếp: gõ =SU, Excel gợi ý danh sách hàm; Tab để chọn, rồi gõ đối số.

• Nút fx trên thanh công thức hoặc Formulas > Insert Function: tìm hàm theo mô tả; hộp Function Arguments hướng dẫn từng đối số.

• Formulas > Function Library: chọn hàm theo nhóm (Math & Trig, Statistical, Text, Date & Time, Logical…).

Hình minh hoạ

Hình 6.6 — Hộp thoại Function Arguments của hàm IF: ba đối số và kết quả xem trước.

Quy tắc: chuỗi chữ trong công thức luôn đặt trong dấu nháy kép ("Giỏi"); mở ngoặc nào phải đóng ngoặc đó; dấu phân cách đối số theo cài đặt vùng (xem 6.2.2).

6.5.2. Hàm toán học

Bảng 6.9 — Các hàm toán học thường dùng

HàmCông dụngVí dụ
SUMTổng các số=SUM(E2:E11)
ROUNDLàm tròn đến n chữ số thập phân=ROUND(8.456,1) cho 8,5
ROUNDUP, ROUNDDOWNLàm tròn lên, làm tròn xuống=ROUNDUP(8.41,1) cho 8,5
INTPhần nguyên (làm tròn xuống số nguyên)=INT(8.9) cho 8
MODPhần dư của phép chia=MOD(10,3) cho 1
ABSGiá trị tuyệt đối=ABS(-5) cho 5
SQRT, POWERCăn bậc hai, lũy thừa=SQRT(16) cho 4; =POWER(2,3) cho 8

6.5.3. Hàm thống kê

Bảng 6.10 — Các hàm thống kê thường dùng

HàmCông dụngVí dụ
AVERAGETrung bình cộng (bỏ qua ô trống và chữ)=AVERAGE(G2:G11)
MAX, MINGiá trị lớn nhất, nhỏ nhất=MAX(G2:G11)
COUNTĐếm ô chứa số=COUNT(E2:E11)
COUNTAĐếm ô không trống (cả chữ)=COUNTA(C2:C11)
COUNTBLANKĐếm ô trống=COUNTBLANK(C2:C11)
MEDIANTrung vị=MEDIAN(G2:G11)
LARGE, SMALLGiá trị lớn thứ k, nhỏ thứ k=LARGE(G2:G11,2)
RANK.EQXếp hạng (RANK vẫn dùng được)=RANK.EQ(G2,$G$2:$G$11)

Lưu ý với RANK: vùng so sánh phải dùng địa chỉ tuyệt đối ($G$2:$G$11) để khi sao chép xuống, vùng không bị trượt.

6.5.4. Hàm chuỗi

Bảng 6.11 — Các hàm chuỗi thường dùng

HàmCông dụngVí dụ
LEFT, RIGHT, MIDLấy ký tự bên trái, bên phải, ở giữa=LEFT(B2,2); =MID(B2,3,4)
LENĐếm số ký tự=LEN(B2)
UPPER, LOWER, PROPERĐổi hoa, thường, hoa đầu từ=PROPER(B2)
TRIMXóa dấu cách thừa=TRIM(B2)
CONCAT, TEXTJOIN, &Nối chuỗi (CONCAT, TEXTJOIN từ Excel 2019)=B2&" - "&C2
FIND, SEARCHTìm vị trí của chuỗi con=FIND(" ",B2)
SUBSTITUTEThay một chuỗi bằng chuỗi khác=SUBSTITUTE(B2,"a","A")
VALUEĐổi chữ số thành số=VALUE("123")
TEXTĐổi số hoặc ngày thành chữ theo mẫu=TEXT(A2,"dd/mm/yyyy")

Ví dụ chuẩn hóa họ tên: ô chứa " nGUYỄN vĂN a " (dấu cách thừa, chữ hoa thường lộn xộn). Công thức:

Liệt kê 6.1 — Chuẩn hóa họ tên bằng hàm lồng nhau

=PROPER(TRIM(B2))

Kết quả: Nguyễn Văn A

TRIM xóa dấu cách thừa đầu, cuối và giữa các từ; PROPER viết hoa chữ đầu mỗi từ. Cùng cách làm này dùng để làm sạch danh sách trước khi trộn thư (Chương 5). Hàm trong ô lồng nhau: hàm trong cùng chạy trước.

6.5.5. Hàm ngày tháng

Bảng 6.12 — Các hàm ngày tháng thường dùng

HàmCông dụngVí dụ
TODAY, NOWNgày hiện tại; ngày và giờ hiện tại (tự cập nhật)=TODAY()
DAY, MONTH, YEARLấy ngày, tháng, năm=YEAR(C2)
DATEGhép ngày từ năm, tháng, ngày=DATE(2026,11,2)
DATEDIFKhoảng cách hai ngày theo "Y" (năm), "M" (tháng), "D" (ngày)=DATEDIF(C2,TODAY(),"Y")
WEEKDAYThứ trong tuần dạng số (kiểu 2: thứ Hai = 1)=WEEKDAY(C2,2)
EDATE, EOMONTHNgày sau n tháng; ngày cuối tháng=EDATE(C2,1)
NETWORKDAYSSố ngày làm việc giữa hai ngày=NETWORKDAYS(B2,C2)

Vì ngày là số, số ngày giữa hai mốc chỉ cần lấy hiệu: =C2-B2. Số ngày quay gồm cả ngày đầu và ngày cuối là =C2-B2+1. Hàm DATEDIF hoạt động tốt dù không xuất hiện trong danh sách gợi ý của Excel; gõ tay đúng tên hàm.

6.5.6. Hàm logic

IF cho phép bảng tính "ra quyết định": =IF(điều kiện, giá trị nếu đúng, giá trị nếu sai). Điều kiện là phép so sánh cho kết quả TRUE hoặc FALSE. Ví dụ =IF(G2>=5,"Đạt","Không đạt").

Bảng 6.13 — Các hàm logic

HàmCông dụngVí dụ
IFChọn một trong hai kết quả theo điều kiện=IF(G2>=5,"Đạt","Không đạt")
ANDTRUE khi tất cả điều kiện đúng=AND(E2>=5,F2>=5)
ORTRUE khi ít nhất một điều kiện đúng=OR(E2<5,F2<5)
NOTĐảo TRUE thành FALSE và ngược lại=NOT(E2>=5)
IFERRORTrả giá trị khác nếu công thức báo lỗi=IFERROR(C2/D2,0)
IFSNhiều điều kiện liên tiếp (Excel 2019 trở lên)=IFS(G2>=8,"Giỏi",G2>=5,"Đạt",TRUE,"Yếu")

Muốn chia nhiều mức, lồng các hàm IF vào nhau, nhớ rằng Excel kiểm tra từ trái sang phải và dừng ở điều kiện đúng đầu tiên, nên xếp điều kiện từ mức cao xuống mức thấp. Ví dụ xếp loại theo quy ước của bài tập chương này (điểm từ 8 là Giỏi, từ 6,5 là Khá, từ 5 là Trung bình, còn lại là Yếu):

Liệt kê 6.2 — Xếp loại bằng hàm IF lồng nhau (điểm tổng kết ở ô H2)

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

Dấu thập phân trong công thức theo cài đặt vùng (máy Việt Nam: 6,5; máy Mỹ: 6.5). Điều kiện kép dùng AND: "được học bổng khi tổng kết từ 8 và chuyên cần từ 8" viết =IF(AND(H2>=8,E2>=8),"Có","Không").

6.5.7. Hàm có điều kiện cơ bản

Bảng 6.14 — Các hàm có điều kiện cơ bản

HàmCông dụngVí dụ
COUNTIFĐếm số ô thỏa một điều kiện=COUNTIF(I2:I11,"Giỏi")
SUMIFTính tổng các ô thỏa điều kiện=SUMIF(D2:D11,"Điện ảnh",H2:H11)
AVERAGEIFTrung bình các ô thỏa điều kiện=AVERAGEIF(D2:D11,"Điện ảnh",H2:H11)

Cấu trúc: SUMIF(vùng cần xét, điều kiện, vùng cộng); COUNTIF(vùng cần xét, điều kiện). Điều kiện có thể là chữ ("Giỏi"), số, phép so sánh trong nháy kép (">=8") hoặc ký tự đại diện ("*phim*"). Trường hợp nhiều điều kiện cùng lúc (SUMIFS, COUNTIFS), tra cứu (VLOOKUP, XLOOKUP) và tổng hợp bằng PivotTable học ở Chương 7.

6.6. Sắp xếp, lọc và Table

6.6.1. Chuẩn bị dữ liệu dạng bảng

Sắp xếp, lọc, Table đều cần dữ liệu tổ chức như một cơ sở dữ liệu nhỏ: hàng 1 là tên cột (mỗi cột một tên khác nhau), từ hàng 2 mỗi dòng là một bản ghi, không có dòng hoặc cột trống xen giữa, không gộp ô, mỗi cột chỉ một kiểu dữ liệu. Đặt con trỏ vào một ô bất kỳ trong bảng là Excel tự nhận ra toàn bộ vùng.

6.6.2. Sắp xếp

• Nhanh: đặt con trỏ vào một ô của cột cần sắp, Data > Sort A to Z (tăng dần) hoặc Sort Z to A (giảm dần).

• Nhiều cấp: Data > Sort > Add Level: ví dụ sắp theo Ngành (A đến Z), rồi theo Điểm tổng kết (giảm dần). Nhớ tick "My data has headers".

• Sắp theo màu hoặc danh sách riêng: cũng ở hộp thoại Sort (Sort On, Order > Custom List).

Cảnh báo khi sắp xếpNếu chỉ chọn một cột rồi sắp xếp, Excel hỏi "Expand the selection"; luôn chọn Expand the selection để các cột khác đi cùng. Nếu để Excel chỉ sắp một cột, dữ liệu các cột còn lại bị lệch hàng và không hoàn tác được sau khi lưu. Công thức dùng địa chỉ tuyệt đối hoặc trỏ ra ngoài vùng sắp xếp cũng dễ sai khi hàng đổi chỗ; kiểm tra lại sau khi sắp.

6.6.3. Lọc dữ liệu

Đặt con trỏ trong bảng, Data > Filter (hoặc Ctrl + Shift + L): mỗi tên cột hiện mũi tên thả xuống. Nhấp mũi tên để tick giá trị cần xem, dùng ô tìm kiếm, hoặc chọn Text Filters, Number Filters, Date Filters (bằng, lớn hơn, chứa, giữa hai giá trị, tuần này…). Dòng không thỏa điều kiện chỉ bị ẩn, không bị xóa; số hàng hiện ở thanh trạng thái. Xóa lọc: Data > Clear; tắt lọc: bấm Filter lần nữa.

• Lọc nhiều cột: các điều kiện ở các cột khác nhau kết hợp theo nghĩa "và".

• Sao chép kết quả lọc: chọn vùng đã lọc rồi Ctrl + C, dán sang nơi khác chỉ chép các dòng đang hiện.

• Xóa dòng trùng: Data > Remove Duplicates, chọn cột dùng để so sánh; nên sao lưu trước vì thao tác xóa thật.

6.6.4. Chuyển vùng dữ liệu thành Table

Đặt con trỏ trong bảng dữ liệu, bấm Ctrl + T (Insert > Table), tick "My table has headers". Vùng trở thành Table có nhiều ưu điểm:

• Tự có mũi tên lọc và sắp xếp, hàng xen kẽ màu (đổi kiểu ở tab Table Design).

• Tự mở rộng khi gõ thêm dòng ở dưới; công thức và định dạng tự điền cho dòng mới.

• Công thức theo tên cột (structured reference), ví dụ =[@SoLuong]*[@DonGia]; tổng cả cột: =SUM(Table1[ThanhTien]). Tên bảng đổi ở Table Design > Table Name.

• Total Row (Table Design > Total Row): thêm hàng tổng ở cuối với lựa chọn Sum, Average, Count…

• Biểu đồ và danh sách chọn lấy dữ liệu từ Table tự mở rộng theo.

Hình minh hoạ

Hình 6.7 — (a) Hộp thoại Sort hai cấp: Nganh (A đến Z), rồi TongKet (lớn đến nhỏ); (b) bảng sau khi sắp xếp, tab Data có nút Filter và các mũi tên lọc ở hàng tiêu đề.

6.7. Định dạng trang in và xuất PDF

6.7.1. Thiết lập trang in

Bảng tính thường rộng hơn một trang giấy; không chỉnh trước khi in sẽ bị cắt giữa chừng. Các thiết lập nằm ở tab Page Layout và hộp thoại Page Setup:

• Size, Orientation: khổ A4; bảng nhiều cột nên chọn Landscape (ngang).

• Margins: lề; chọn Narrow cho bảng rộng.

• Scale to Fit: Width = 1 page, Height = Automatic: ép bề ngang vừa một trang, chiều dài tự chia trang. Cách nhanh nhất để bảng không bị cắt cột.

• Print Area: chọn vùng cần in rồi Page Layout > Print Area > Set Print Area (không in các ô ghi chú ngoài).

• Print Titles: Page Layout > Print Titles > Rows to repeat at top: chọn hàng tiêu đề để lặp lại ở đầu mỗi trang.

• Header & Footer: tên bảng, ngày in, số trang (Page &[Page] of &[Pages]).

• View > Page Break Preview: xem và kéo các đường ngắt trang xanh để chia trang theo ý.

Hình minh hoạ

Hình 6.8 — Hộp thoại Page Setup: (a) tab Page — A4, Landscape, Fit to 1 page wide; (b) tab Sheet — Rows to repeat at top ($1:$1).

6.7.2. Xem trước, in và xuất PDF

• Xem trước: Ctrl + P hiện bản in thử và số trang; kiểm tra bảng có bị cắt cột, trang trống thừa không.

• Gridlines: mặc định đường lưới không in; muốn có viền cho bảng phải đặt Borders cho vùng dữ liệu.

• Xuất PDF: File > Save As > PDF (hoặc File > Export); chọn Options để xuất đúng vùng đã chọn hoặc toàn sổ tính. PDF gửi cho người khác không sửa được và không lộ công thức (xem 6.8.4).

6.7.3. Đưa bảng từ Excel sang Word

Chọn vùng bảng trong Excel, Ctrl + C; mở Word, bấm chuột phải ở chỗ cần dán và chọn kiểu dán trong Paste Options:

• Keep Source Formatting: giữ định dạng Excel, bảng thành bảng Word có thể sửa tiếp (thường dùng nhất).

• Use Destination Styles: áp định dạng bảng của tài liệu Word đích.

• Picture: dán thành ảnh, không sửa được; phù hợp khi chỉ cần minh họa.

• Link & Keep Source Formatting: giữ liên kết, tự cập nhật khi file Excel đổi; dễ vỡ khi đổi vị trí file, hạn chế dùng cho tài liệu nộp.

Theo Quy định của Trường (Chương 5): bảng dán vào luận văn phải rộng không quá 75% bề rộng chữ, có chú thích đặt trên bảng bằng Insert Caption (nhãn "Bảng", đánh số theo chương) và nên nằm trọn trong một trang. Bảng Excel quá rộng thì thu gọn cột, giảm cỡ chữ trong bảng, hoặc để trên trang ngang (Landscape) bằng Section Break (Chương 4).

6.8. Ứng dụng trong ngành và an toàn dữ liệu bảng tính

6.8.1. Dự toán kinh phí phim ngắn

Dự toán là bài toán điển hình: mỗi hạng mục có số lượng và đơn giá, thành tiền bằng tích hai số, rồi cộng, rồi tính thuế. Dữ liệu mẫu nằm ở sheet DuToan của file Chuong6_BangTinh_Mau.xlsx (dữ liệu giả).

Bảng 6.15 — Cấu trúc bảng dự toán và công thức

CộtNội dungCông thức ở dòng 2
AHạng mụcNhập
BĐơn vị tínhNhập
CSố lượngNhập
DĐơn giá (đồng)Nhập; định dạng #,##0
EThành tiền=C2*D2
G1Thuế VAT (đặt tên vùng VAT)10%

Dưới bảng, ô tổng tiền hàng dùng =SUM(E2:E11); thuế =E12*VAT; tổng cộng =E12+E13. Với dữ liệu mẫu, tổng tiền hàng là 36.800.000 đồng, thuế 10% là 3.680.000 đồng và tổng cộng là 40.480.000 đồng. Dùng các con số này để kiểm tra bài làm của mình.

Cách mở rộng: khi cần thay mức thuế hay đổi giá, chỉ sửa một ô VAT hoặc đơn giá, mọi thành tiền, tổng, thuế tự cập nhật. Đó là ưu điểm so với tính tay hoặc gõ số cứng vào công thức.

6.8.2. Bảng điểm và xếp loại

Bài toán thứ hai: bảng điểm theo trọng số. Theo đề cương môn Tin học, điểm tổng kết gồm chuyên cần 10%, giữa kỳ 30%, thi kết thúc môn 60%. Sheet BangDiem có dữ liệu giả của 10 người học: các cột STT, MaSV, HoTen, Nganh, ChuyenCan, GiuaKy, CuoiKy.

Liệt kê 6.3 — Điểm tổng kết (ô H2) và xếp hạng (ô J2)

H2: =ROUND(E2*10%+F2*30%+G2*60%,1)

I2: =IF(H2>=8,"Giỏi",IF(H2>=6.5,"Khá",IF(H2>=5,"Trung bình","Yếu")))

J2: =RANK.EQ(H2,$H$2:$H$11)

Với dữ liệu mẫu: điểm trung bình lớp là 7,57, điểm cao nhất 9,3, thấp nhất 4,8, và có 5 người xếp loại Giỏi (đếm bằng =COUNTIF(I2:I11,"Giỏi")). Quy ước xếp loại ở đây chỉ để luyện hàm IF, không phải quy chế đào tạo của Trường.

6.8.3. Lịch quay và tính ngày

Sheet LichQuay có các cảnh quay với ngày bắt đầu và ngày kết thúc. Số ngày quay mỗi cảnh = ngày kết thúc − ngày bắt đầu + 1; tổng cộng các cảnh mẫu là 11 ngày. Hạn giao bản dựng một tháng sau cảnh cuối: =EDATE(MAX(C2:C5),1). Thứ trong tuần của ngày bắt đầu: =TEXT(B2,"dddd").

6.8.4. An toàn và bảo mật bảng tính

• Dữ liệu cá nhân: bảng điểm, danh sách người học, số điện thoại là dữ liệu cá nhân (Chương 2 và Chương 3). Không gửi cả file cho người không cần biết; gửi PDF hoặc bản đã bỏ cột nhạy cảm.

• Mật khẩu mở file: File > Info > Protect Workbook > Encrypt with Password. Mất mật khẩu thì không mở lại được; ghi nhớ cẩn thận.

• Khóa trang tính: Review > Protect Sheet khóa ô công thức, chỉ cho sửa ô nhập; chỉ để tránh sửa nhầm, không phải bảo mật tuyệt đối.

• Ẩn không phải là xóa: dòng, cột, trang tính ẩn vẫn nằm trong file; trước khi gửi, dùng File > Info > Check for Issues > Inspect Document để xóa thông tin ẩn.

• Công thức và giá trị: gửi kết quả cho bên ngoài thì Paste Special > Values hoặc xuất PDF để không lộ công thức, đơn giá gốc.

• Sao lưu 3-2-1 cho các file bảng tính quan trọng (Chương 1); đặt tên có phiên bản, đừng ghi đè bản cũ.

6.8.5. Danh sách kiểm tra một bảng tính tốt

1. Hàng 1 là tiêu đề; không dòng, cột trống trong vùng dữ liệu; không gộp ô trong vùng dữ liệu.

2. Mã, số điện thoại là Text; ngày là Date; số là Number.

3. Công thức dùng địa chỉ đúng loại; các hằng số dùng chung nằm ở ô riêng hoặc có tên vùng.

4. Không còn lỗi #DIV/0!, #NAME?, #REF!… (hoặc đã xử lý bằng IFERROR có chủ đích).

5. Định dạng số thống nhất; có đơn vị ở tiêu đề.

6. Có Freeze Panes cho bảng dài; trang in thiết lập Fit to 1 page wide và lặp tiêu đề.

7. Tên trang tính có nghĩa; tên file theo quy ước; lưu .xlsx.

8. Đã kiểm tra bằng vài số tính tay hoặc tổng kiểm tra.

9. Không để dữ liệu cá nhân thật khi chia sẻ; đã đặt mật khẩu nếu cần.

10. Đã sao lưu theo 3-2-1.

Tóm tắt chương

• Workbook gồm nhiều worksheet; mỗi worksheet có 1.048.576 hàng × 16.384 cột; ô có địa chỉ cột + hàng (B3); vùng viết A1:C5.

• Kiểu dữ liệu: văn bản (căn trái), số (căn phải), ngày giờ (thực chất là số), logic, công thức (bắt đầu =), lỗi. Mã và số điện thoại số 0 đầu phải để Text.

• Dấu thập phân và dấu phân cách đối số hàm theo cài đặt Region (Việt Nam: dấu phẩy thập phân, dấu chấm phẩy phân cách đối số).

• Định dạng chỉ đổi cách hiển thị, không đổi giá trị; dùng ROUND để đổi giá trị thật. Không gộp ô trong vùng dữ liệu; Freeze Panes cho bảng dài; Data Validation giữ dữ liệu nhập nhất quán.

• Công thức bắt đầu bằng =; địa chỉ tương đối (A1), tuyệt đối ($A$1), hỗn hợp ($A1, A$1); F4 đổi loại; đặt tên vùng thay cho địa chỉ khó nhớ.

• Hàm toán (SUM, ROUND), thống kê (AVERAGE, MAX, MIN, COUNT, RANK.EQ), chuỗi (LEFT, MID, TRIM, PROPER, &), ngày (TODAY, DATEDIF, EDATE), logic (IF, AND, OR, IFERROR), có điều kiện (COUNTIF, SUMIF, AVERAGEIF).

• IF lồng nhau kiểm tra từ trái sang phải, xếp điều kiện từ cao xuống thấp; chuỗi chữ trong công thức đặt trong nháy kép.

• Sắp xếp phải chọn Expand the selection; lọc chỉ ẩn dòng; Ctrl + T chuyển vùng thành Table tự mở rộng, có Total Row và công thức theo tên cột.

• In: A4, Landscape, Scale to Fit (Width 1 page), Print Titles; xuất PDF để gửi; dán sang Word theo Quy định (≤ 75%, chú thích trên bảng).

• Bảo mật: dữ liệu cá nhân, mật khẩu mở file, Protect Sheet, ẩn khác xóa, sao lưu 3-2-1.

Thuật ngữ cần nhớ

Bảng 6.16 — Thuật ngữ cần nhớ

Thuật ngữGiải nghĩa
WorkbookSổ tính: một file Excel gồm nhiều trang tính
WorksheetTrang tính: một bảng gồm hàng và cột
Cell, RangeÔ; vùng gồm nhiều ô liền nhau (A1:C5)
Name Box, Formula BarHộp tên (địa chỉ ô); thanh công thức (nội dung thật của ô)
Fill handleChấm vuông ở góc ô để kéo điền tự động
AutoFill, Flash FillĐiền tự động theo quy luật; điền theo mẫu Excel đoán
Paste SpecialDán đặc biệt: chỉ giá trị, định dạng, công thức, chuyển vị
Format CellsHộp thoại định dạng ô (Ctrl + 1)
Data ValidationGiới hạn nội dung được nhập vào ô
Freeze PanesCố định hàng hoặc cột khi cuộn
FormulaCông thức: biểu thức bắt đầu bằng dấu =
FunctionHàm: công thức dựng sẵn, dạng =TÊN(đối số)
ArgumentĐối số: dữ liệu đưa vào hàm
Relative referenceĐịa chỉ tương đối (A1): đổi theo vị trí khi sao chép
Absolute referenceĐịa chỉ tuyệt đối ($A$1): giữ cố định
Mixed referenceĐịa chỉ hỗn hợp ($A1 hoặc A$1)
Named rangeVùng được đặt tên, dùng thay địa chỉ
IF, AND, ORHàm logic: chọn kết quả theo điều kiện; kết hợp điều kiện
IFERRORHàm trả giá trị khác khi công thức lỗi
COUNTIF, SUMIFHàm đếm, tính tổng theo điều kiện
Circular referenceTham chiếu vòng: công thức trỏ vào chính ô chứa nó
SortSắp xếp dữ liệu theo một hoặc nhiều cột
FilterLọc: chỉ hiện dòng thỏa điều kiện, dòng khác bị ẩn
Table (Ctrl + T)Bảng tự mở rộng, có lọc, Total Row, công thức theo tên cột
Print TitlesHàng, cột lặp lại ở đầu mỗi trang in
Scale to FitThu nhỏ trang in cho vừa số trang chọn
CSV UTF-8Tệp văn bản phân cách bằng dấu, mã hóa giữ được tiếng Việt

Câu hỏi ôn tập

1. Phân biệt workbook, worksheet, ô, vùng. Địa chỉ ô B3 và vùng A1:C5 nghĩa là gì?

2. Kể 5 kiểu dữ liệu trong Excel. Vì sao ngày tháng được coi là số và điều đó giúp gì cho tính toán?

3. Vì sao mã người học hoặc số điện thoại phải đặt dạng Text? Hậu quả với trộn thư ở Chương 5 nếu để dạng Number?

4. Ô hiện 8,5 nhưng thanh công thức hiện 8,456. Vì sao? Muốn giá trị thật cũng là 8,5 phải làm gì?

5. Vì sao không nên gộp ô trong vùng dữ liệu? Thay thế bằng cách nào?

6. Phân biệt địa chỉ tương đối, tuyệt đối, hỗn hợp; cho ví dụ cần dùng mỗi loại. F4 làm gì?

7. Công thức thuế =E2*G1 sao chép xuống dòng 3 cho kết quả sai. Chỉ ra lỗi và sửa bằng hai cách (địa chỉ tuyệt đối và tên vùng).

8. Giải thích lỗi #DIV/0!, #NAME?, #REF!, #VALUE!, ######. Mỗi lỗi sửa thế nào?

9. Viết công thức xếp loại: từ 9 trở lên là Xuất sắc, từ 8 là Giỏi, từ 6,5 là Khá, từ 5 là Trung bình, còn lại là Yếu. Vì sao thứ tự điều kiện quan trọng?

10. Khác nhau giữa COUNT, COUNTA, COUNTBLANK, COUNTIF. Cho ví dụ cho từng hàm.

11. Cho ô B2 chứa " trần THỊ b ". Viết công thức cho ra "Trần Thị B" và giải thích từng hàm.

12. Tính số ngày quay giữa hai ngày (gồm cả ngày đầu và cuối) và số ngày làm việc (không tính thứ Bảy, Chủ nhật). Dùng hàm nào?

13. Vì sao khi sắp xếp phải chọn Expand the selection? Điều gì xảy ra nếu chỉ sắp một cột?

14. Lọc khác xóa ở điểm nào? Ưu điểm của Table (Ctrl + T) so với vùng dữ liệu thường là gì?

15. Để in bảng 12 cột vừa một trang ngang A4 và lặp tiêu đề ở mỗi trang, cần thiết lập những mục nào?

Bài tập tìm hiểu (làm ở nhà, nộp trước buổi sau)

Chọn 2 trong 7 bài, trình bày 1–2 trang Word có liên kết nguồn, bài nộp phải định dạng đúng Quy định của Trường (Chương 5): style, chú thích hình và bảng, trích dẫn. Được dùng AI hỗ trợ nhưng phải ghi rõ.

1. Lịch sử bảng tính. Tìm hiểu VisiCalc, Lotus 1-2-3 và Excel: ai tạo ra, mốc thời gian, tại sao bảng tính được coi là "ứng dụng sát thủ" của máy tính cá nhân.

2. Excel và Google Sheets. Lập bảng so sánh Excel và Google Sheets về hàm, làm việc nhóm, dung lượng dữ liệu, giá; nêu trường hợp chọn từng loại cho một nhóm làm phim 5 người.

3. Lỗi bảng tính nổi tiếng. Tìm một vụ việc thực tế sai sót do lỗi Excel (ví dụ lỗi định dạng ngày làm hỏng tên gen, lỗi địa chỉ tham chiếu trong báo cáo tài chính): nguyên nhân, hậu quả, bài học.

4. Dự toán thực tế. Tìm hoặc tự dựng một mẫu dự toán kinh phí cho một chương trình sân khấu hoặc phim ngắn có ít nhất 15 hạng mục; mô tả công thức và nêu các khoản dễ bỏ sót.

5. Phím tắt Excel. Lập danh sách 20 phím tắt Excel hữu ích nhất cho công việc của chuyên ngành người học, kèm ví dụ dùng; thử bấm và chụp màn hình minh họa 5 phím.

6. Dấu phân cách và vùng ngôn ngữ. Tìm hiểu vì sao công thức Excel khác dấu phẩy, chấm phẩy giữa các máy; cách đổi cài đặt Region; ảnh hưởng khi gửi file cho người ở nước ngoài.

7. Bảo mật bảng tính. Tìm hiểu Protect Sheet, Encrypt with Password, Inspect Document; thử trên một file mẫu và nêu giới hạn của từng biện pháp.

Bài tập thực hành

BT1–BT3 làm trên lớp (3 tiết thực hành); BT4–BT6 làm ở nhà. Dùng file Chuong6_BangTinh_Mau.xlsx (dữ liệu giả; các sheet BangDiem, DuToan, DanhSach, LichQuay). Nộp cả .xlsx và .pdf, nén TinHoc_Buoi06_HoTen.zip.

1. Nhập và định dạng dữ liệu (trên lớp, 25 phút). Tạo sổ tính mới, đặt tên TinHoc_BT1_HoTen.xlsx, sheet "NhapLieu". Nhập 8 dòng gồm: STT, MaSV (Text), HoTen, Nganh, NgaySinh (Date dd/mm/yyyy), Diem (một chữ số thập phân). Đặt hàng tiêu đề in đậm, màu nền nhạt; Freeze Top Row; Data Validation dạng danh sách cho cột Nganh (Điện ảnh, Mỹ thuật, Sân khấu) và giới hạn Diem từ 0 đến 10 kèm cảnh báo. Chụp màn hình hộp thoại Data Validation.

2. Dự toán và địa chỉ tuyệt đối (trên lớp, 30 phút). Mở sheet DuToan. Tính ThanhTien (cột E) cho mỗi hạng mục; đặt tên ô G1 là VAT; ở dưới bảng tính tổng tiền hàng, thuế bằng tên vùng VAT và tổng cộng; định dạng #,##0. Kiểm tra: tổng tiền hàng phải là 36.800.000 đồng; tổng cộng 40.480.000 đồng. Làm lại thuế bằng địa chỉ tuyệt đối $G$1 và so sánh hai cách.

3. Bảng điểm và hàm logic (trên lớp, 35 phút). Mở sheet BangDiem. Thêm cột TongKet (=ROUND(CC×10%+GK×30%+CK×60%,1)), XepLoai (IF lồng nhau: từ 8 Giỏi, từ 6,5 Khá, từ 5 Trung bình, còn lại Yếu), XepHang (RANK.EQ với địa chỉ tuyệt đối). Ở dưới tính trung bình, cao nhất, thấp nhất của TongKet và số người Giỏi bằng COUNTIF. Kiểm tra: trung bình lớp 7,57; cao nhất 9,3; thấp nhất 4,8; 5 người Giỏi. Thêm AVERAGEIF cho riêng ngành Điện ảnh (kiểm tra: 8,15).

4. Hàm chuỗi và ngày tháng (ở nhà). Sheet DanhSach: dùng TRIM và PROPER chuẩn hóa họ tên vào cột mới; dùng RIGHT lấy 3 ký tự cuối của MaSV; dùng DATEDIF tính tuổi tính đến hôm nay. Sheet LichQuay: thêm cột SoNgay (kết thúc trừ bắt đầu cộng 1; tổng 11 ngày), cột Thu (TEXT(...,"dddd")) và ô hạn giao bản dựng một tháng sau cảnh cuối (EDATE, MAX).

5. Sắp xếp, lọc, Table (ở nhà). Với bảng điểm: sắp xếp nhiều cấp theo Nganh (A–Z) rồi TongKet (giảm dần); lọc chỉ những người Giỏi và sao chép kết quả sang sheet mới; chuyển bảng thành Table (Ctrl + T), đặt tên bảng tbBangDiem, thêm Total Row tính trung bình TongKet. Nộp ảnh chụp trước và sau khi sắp xếp, lọc.

6. In, xuất PDF và đưa sang Word (ở nhà). Với bảng điểm: A4 ngang, Scale to Fit Width 1 page, lặp hàng tiêu đề, Header ghi tên lớp, Footer ghi số trang; xuất PDF. Sau đó dán bảng vào một file Word theo Quy định Chương 5: bảng rộng không quá 75% bề rộng chữ, chú thích "Bảng" đặt trên bảng bằng Insert Caption. Nộp file Word, PDF và ảnh chụp Print Preview.

Tiêu chí đánh giá bài tậpĐúng và đủ yêu cầu kỹ thuật, kết quả khớp số kiểm tra (40%) · Dùng đúng loại địa chỉ, hàm hợp lý, không gõ cứng số vào công thức (30%) · Định dạng, trình bày rõ ràng, ảnh chụp đầy đủ, có nhận xét cá nhân (20%) · Nộp đúng hạn, đúng định dạng file (10%). BT2 và BT3 là bài trọng tâm. Bài tập tìm hiểu và thực hành của Chương 6 tính vào điểm Bài tập thường xuyên.

— Hết Chương 6 —