Bài giảng Tin học đại cương: Phần 4

Email: phungthk@ueh.edu.vn Website: mis.ueh.edu.vn/blog/phungthk

Tin học đại cương ThS. Thái Kim Phụng Hệ thống thông tin kinh doanh

PHẦN 4 MICROSOFT EXCEL 2010

Chương 1: Tổng quan về MS Excel 2010

• Giới thiệu MS Excel 2010 • Các khái niệm cơ bản • Nhập và chỉnh sửa dữ liệu • Địa chỉ tham chiếu

Giới thiệu Excel 2010 • Excel phần mềm tạo bảng tính điện tử được sử dụng

rộng rãi trong công tác văn phòng và quản l{.

• Chức năng chính của bảng tính là thực hiện các tính

• Bảng tính còn được sử dụng để thao tác trên các dữ liệu – tổ chức lưu trữ dữ liệu và phân tích dữ liệu theo yêu cầu của người sử dụng.

toán.

• Bảng tính còn có các công cụ đồ họa để biểu diễn dữ

liệu dưới dạng các đồ thị.

• Khởi động Excel:

– Start  All Programs  Microsoft Office 

Microsoft Excel 2010

• Thoát Excel:

– Cách 1: Lệnh File  Exit – Cách 2: Nhấn tổ hợp phím Alt+F4 – Cách 3: Nhấp vào nút Close – Cách 4: Nhấp đôi vào biểu tượng ở góc trái

trên của cửa sổ.

Workbook

Các khái niệm cơ bản

Địa chỉ ô hiện hành Nội dung ô hiện hành

Column: cột (A,B,…XFD: 214=16.384)

cell: ô (C7)

Row: dòng (1,2,…,220)

Sheet: trang bảng tính (255)

Các khái niệm cơ bản

• Các thao tác cơ bản với workbook

– Tạo mới workbook – Mở workbook đang có – Lưu workbook – Đóng workbook

Các khái niệm cơ bản

• Các thao tác cơ bản với worksheet

– Thêm sheet – Xóa sheet – Di chuyển, sao chép sheet – Đổi tên sheet – Ẩn / hiện sheet – Đổi màu tab sheet

Nhập liệu và chỉnh sửa

• Các kiểu dữ liệu

– Văn bản (Text): canh trái ô. Dữ liệu bắt đầu hoặc

– Số (Number): canh phải ô. Dữ liệu toàn là các k{ số (0, 1, ... , 9), không có khoảng trắng ở giữa các k{ số, có thể bắt đầu là dấu dương (+), dấu âm (-), dấu chấm thập phân (.), dấu phần trăm (ví dụ 5% nghĩa là 0.05), chữ E (ví dụ 3E+5 nghĩa là 3*105 ).

bên trong dữ liệu là k{ tự chữ, dấu gạch dưới ( _ ), khoảng trắng

Nhập liệu và chỉnh sửa

• Các kiểu dữ liệu

– Kiểu ngày giờ (date/time): thật chất là kiểu số, lấy

mốc từ 1/1/1900 00:00:00

– Luận lý (Logic): canh giữa ô, có hai giá trị là TRUE và FALSE, có thể là kết quả của công thức. Dữ liệu kiểu luận l{ cũng có giá trị số tương ứng: TRUE là 1 và FALSE là 0.

Nhập liệu và chỉnh sửa

• Các kiểu dữ liệu

– Kiểu Công thức/biểu thức (Formula): bắt đầu là dấu bằng (=), rồi đến biểu thức gồm các hàm, toán tử và các số liệu. Các số liệu có thể là số liệu cụ thể, địa chỉ ô, địa chỉ vùng trong một bảng hoặc nhiều bảng. Kết quả của công thức sẽ là 1 trong 5 kiểu dữ liệu nói trên.

Nhập liệu và chỉnh sửa

Insert Function

Cancel

Name Box

Enter

Formula Bar

Nhập liệu và chỉnh sửa

• Nhập, sửa dữ liệu – Cơ chế autofill – Sửa dữ liệu:

• Formula bar • F2

Địa chỉ tham chiếu

Ô hoặc vùng

Địa chỉ

Ô ở cột A và dòng 1

A5:D10

Vùng gồm các ô trong các cột từ A tới D và các hàng từ 5 đến 10

A5:A10

Vùng gồm các ô trong cột A và các hàng từ 5 đến 10

B5:G5

Vùng gồm các ô trên hàng 5 và từ cột B đến cột G

Vùng gồm tất cả các ô trên hàng 5

5:5 (thay vì A5:IV5)

Vùng gồm các ô từ hàng 5 đến hàng 10 5:10 (thay vì A5:IV10)

Vùng gồm tất cả các ô trong cột C

C:C (thay vì C1:C1048576)

Vùng gồm tất cả ô từ cột C đến cột F

C:E (thay vì C1: E1048576)

Địa chỉ tham chiếu

• Địa chỉ tham chiếu tương đối (relative references): có dạng

, chẳng hạn B2. Khi sao chép công thức, Excel sẽ tự điều chỉnh địa chỉ tham chiếu trong công thức tùy thuộc vào vị trí nguồn và vị trí đích.

• Địa chỉ tham chiếu tuyệt đối (absolute references): dạng

$$ Khi sao chép công thức, địa chỉ tham chiếu này không thay đổi.

• Địa chỉ tham chiếu hỗn hợp (mixed cell referencing): có 2 dạng: – Tham chiếu hỗn hợp cố định cột: dạng $ như

$B5

– Tham chiếu hỗn hợp cố định dòng: dạng $, như

C$4

Địa chỉ tham chiếu

phím F4, địa chỉ ô vứa gõ trong công thức sẽ thay đổi xoay vòng:

• Ngoài cách gõ trực tiếp k{ tự $, bạn có thể nhấn

“Tương đối  Tuyệt đối  Hỗn hợp cố định hàng  Hỗn hợp cố định cột  Tương đối”

F4

Địa chỉ tham chiếu

Tham chiếu giữa các sheet và book Địa chỉ tham chiếu của ô hiện hành tuân theo qui tắc: *TênBook+TênSheet!ĐịaChỉThamChiếu Ví dụ: [Book1.xlsx]Sheet2!A3: ô A3 thuộc Sheet2 của Book1.xlsx (Book1.xlsx đang mở)

Tên Ô/Vùng

Đặt tên cho ô/vùng: • Cách 1: dùng Name Box

– Chọn ô/vùng cần đặt tên – Gõ tên vào Name Box, rồi nhấn Enter

Tên Ô/Vùng

– Chọn Formulas  Defined

Đặt tên cho ô/vùng: • Cách 2: Dùng hộp thoại

Names  Define Name, xuất hiện hộp thoại New Name

• Name: gõ tên vào • Scope: chọn phạm vi của tên: Workbook hay sheet nào đó.

• Comment: chú giải • Refers to: chọn ô/ vùng mà tên này tham chiếu đến.

Tên Ô/Vùng

của cột đầu tiên trong bảng – Chọn cả bảng dữ liệu Chọn Formulas 

Đặt tên cho ô/vùng: • Cách 3: định nghĩa tên từ tên cột hay dữ liệu

Defined Names  Create from Selection • Top row: vùng cột dữ liệu của cột nào sẽ

có tên là tên cột đó.

• Left column: vùng hàng dữ liệu của hàng nào sẽ có tên là dữ liệu của cột đầu tiên của hàng đó.

• Tương tự cho Bottom row và Right

colum

Tên Ô/Vùng

xuất hiện hộp thoại Name Manager

Quản lý tên ô/vùng: • Chọn lệnh Formulas  Defined Names  Name Manager,

Chương 2: Hàm và công thức

• Các thông báo lỗi • Các hàm thông dụng – Hàm chuỗi (Text) – Hàm ngày, giờ (Date & Time) – Hàm toán học và lượng giác học (Math & Trig) – Hàm thống kê (Statistical) – Hàm dò tìm (Lookup & Reference) – Hàm luận lý (Logical) – Hàm thông tin (Information) – Hàm tài chính (Financial)

• Các thông báo lỗi khi nhập sai công thức:

– #DIV/0! : Lỗi do chia một số cho số 0 – #N/A : Lỗi do không tìm thấy dữ liệu phù hợp.

Thường xuất hiện trong các hàm dò tìm khi không tìm thấy giá trị cần tìm

– #NAME? : Lỗi do sử dụng tên chưa được định nghĩa. Thường do nhập sai tên hàm hoặc kiểu chuỗi trong công thức không đặt trong dấu nháy kép (“ ”)

• Các thông báo lỗi khi nhập sai công thức:

– #NULL! : Lỗi do kết hợp 2 vùng dữ liệu mà không

thể kết hợp

không hợp lệ

– #REF! : Lỗi do tham chiếu đến vùng dữ liệu

– #VALUE! : Lỗi do sử dụng sai kiểu dữ liệu

Hàm chuỗi (Text)

• LEFT(chuỗi, [n]): trả về n k{ tự bên trái của tham số chuỗi. Lưu {, nếu ta bỏ qua n thì mặc định n=1.

• RIGHT(chuỗi, [n]): trả về n k{ tự bên phải của tham số

chuỗi. Lưu {, nếu ta bỏ qua n thì mặc định n=1.

Hàm chuỗi (Text)

• MID(chuỗi, m, n): trả về n k{ tự kể từ k{ tự thứ m của

chuỗi

• TRIM(chuỗi): trả về chuỗi đã cắt bỏ những khoảng trống củachuỗi, khi đó mỗi từ chỉ cách nhau một khoảng trắng.

Hàm chuỗi (Text)

• LOWER(chuỗi): trả về chuỗi chữ thường tương ứng

với chuỗi đã cho

• UPPER(chuỗi): trả về chuỗi chữ hoa tương ứng với

chuỗi đã cho

Hàm chuỗi (Text)

• PROPER(chuỗi): trả về một chuỗi mà k{ tự đầu của mỗi từ đều là chữ hoa tương ứng với chuỗi đã cho.

• LEN(chuỗi): trả về chiều dài của chuỗi, nghĩa là số k{

tự kể cả khoảng trắng trong chuỗi đã cho

Hàm chuỗi (Text)

• FIND(chuỗi_1, chuỗi, [n]): trả về vị trí xuất hiện đầu tiên của chuỗi_1 trong chuỗi, bắt đầu tìm từ vị trí thứ n bên trái chuỗi trở đi (mặc định n = 1). Có phân biệt chữ hoa chữ thường

• SEARCH(chuỗi_1, chuỗi, [n]): trả về vị trí xuất hiện đầu tiên của chuỗi_1 trong chuỗi, bắt đầu tìm từ vị trí thứ n bên trái chuỗi trở đi (mặc định n = 1). Không phân biệt chữ hoa chữ thường

Hàm chuỗi (Text)

• REPLACE(chuỗi, i, n, chuỗi_mới): thay thế n k{ tự trong

chuỗi bắt đầu từ k{ tự thứ i bằng chuỗi_mới.

• SUBSTITUTE(chuỗi, chuỗi_cũ, chuỗi_mới, [i]): thay

chuỗi_cũ thứ i trong chuỗi bằng chuỗi_mới, nếu bỏ qua i nghĩa là thay thế tất cả chuỗi_cũ bằng chuỗi_mới. • TEXT(số, định_dạng): đưa số từ kiểu số về kiểu chuỗi

với định_dạng

• VALUE(chuỗi-số): trả về số tương ứng với chuỗi-số đã

cho

Hàm ngày giờ (Date&Time)

máy đang lưu giữ

• NOW(): trả về ngày-tháng-năm và giờ hiện hành mà

• TODAY(): trả về ngày-tháng-năm hiện hành mà máy

• DATE(năm, tháng, ngày): trả về ngày-tháng-năm

đang lưu giữ

tương ứng

ngày tương ứng với tham số ngày-tháng-năm

• DAY(ngày-tháng-năm): trả về một con số (1  31) chỉ

Hàm ngày giờ (Date&Time)

tháng tương ứng với tham số ngày-tháng-năm

• MONTH(ngày-tháng-năm) : trả về một số (1  12) chỉ

• 1 (mặc định): hàm trả về 1 là chủ nhật  7 là thứ bảy. • 2 : hàm trả về 1 là thứ hai  7 là chủ nhật. • 3 : hàm trả về 0 là thứ hai  6 là chủ nhật.

• YEAR(ngày-tháng-năm): trả về một con số (gồm 4 chữ số) chỉ năm tương ứng với tham số ngày-tháng-năm • WEEKDAY(ngày-tháng-năm,[kiểu]): trả về số thứ tự (nghĩa là Thứ) của ngày-tháng-năm trong tuần. Các kiểuthông dụng:

Hàm ngày giờ (Date&Time)

tham số: giờ, phút, giây đã cho

• TIME(giờ, phút, giây): trả về thời gian tương ứng với 3

• HOUR(btgiờ): trả về một số chỉ giờ (0  23) tương

• MINUTE(btgiờ): trả về một số chỉ phút (0  59) tương

ứng với btgiờ

• SECOND(btgiờ): trả về một số chỉ giây (0  59) tương

Hàm toán học và lượng giác

• ABS(x): trả về giá trị tuyệt đối (ABSolute) của x • INT(x): trả về số nguyên (INTeger) lớn nhất còn nhỏ

hơn hoặc bằng x

• MOD(x, y): trả về số dư của phép chia nguyên x cho y

theo qui tắc: MOD(x, y) = xy* INT(x/y)

• ROUND(x, n): làm tròn số x tùy theo n; với n > 0: làm tròn với n vị trí thập phân, n = 0: làm tròn đến hàng đơn vị, n = 1 : làm tròn đến hàng chục, n = 2: làm tròn đến hàng trăm, ...

Hàm toán học và lượng giác • PRODUCT(x1 , x2 , … , xn): trả về tích các số x1 , x2 , … , xn

• SUM(x1 , x2 , … , xn): trả về tổng các số x1 , x2 , … , xn

Hàm toán học và lượng giác

• SUMIF(vùng1, điều-kiện, vùng2): trả về tổng các ô trong vùng2 tương ứng theo thứ tự với các ô trong vùng1 thỏa điều-kiện. Nếu bỏ qua tham số vùng_2 nghĩa là vùng_2 = vùng_1.

Hàm toán học và lượng giác

• SUMIFS(vùng-tính-tổng, vùng-chứa-điều-kiện-1,điều- kiện-1, *vùng-chứa-điều-kiện-2,điều-kiện-2+,…) : trả về tổng các ô trong vùng-tính-tổng ứng với thứ tự các ô trong vùng-chứa-điều-kiện-1 thỏa điều-kiện-1 và các ô trong vùng-chứa-điều-kiện-2 thỏa điều-kiện-2,…

Hàm toán học và lượng giác

• SUMPRODUCT(vùng1, vùng2, …): trả về tổng của tích

các ô tương ứng trong vùng1, vùng2, …

Hàm toán học và lượng giác

• RAND( ): trả về 1 số ngẫu nhiên trong khoảng từ 0 đến 1 • RANDBETWEEN(số_nhỏ, số_lớn): trả về một số ngẫu

nhiên trong khoảng từ số_nhỏ đến số_lớn.

Hàm thống kê

trong danh-sách

• MIN(danh-sách) : trả về giá trị nhỏ nhất của các số

• MAX(danh-sách) : trả về giá trị lớn nhất của các số

• AVERAGE(danh-sách) : trả về giá trị trung bình của các

trong danh-sách

số trong danh-sách

• AVERAGEIF(vùng_1, điều_kiện[, vùng_2]): trả về giá trị trung bình của các ô trong vùng2 tương ứng theo thứ tự với các ô trong vùng1 thỏa điều-kiện. Nếu bỏ qua tham số vùng_2 nghĩa là vùng_2 = vùng_1.

Hàm thống kê

• COUNT(danh-sách) : trả về số lượng các ô có kiểu số

trong danh-sách

• COUNTA(danh-sách) : trả về số lượng các ô có nội dung

khác rỗng trong danh-sách.

• COUNTBLANK(danh-sách) : trả về số lượng các ô trống

trong danh-sách.

• COUNTIF(vùng, điều-kiện) : trả về số lượng các ô trong

• RANK(ô, vùng, kiểu) : trả về thứ tự xếp hạng của ô trong vùng; kiểu = 1: xếp hạng tăng dần, kiểu = 0 hoặc lờ đi: xếp hạng giảm dần

vùng thỏa điều-kiện

Hàm dò tìm • VLOOKUP(giá-trị-dò, bảng-dò, cột, kiểu-dò): dùng giá-

trị-dò để dò tìm trên cột đầu tiên của bảng-dò. Có 2 TH: – Kiểu dò là False hay 0: nghĩa là dò tìm chính xác

• Nếu tìm thấy đúng giá-trị-dò thì trả về giá trị trên cùng dòng

ở ô tương ứng trên cột của bảng-dò.

– Kiểu dò là True, hoặc 1, hoặc lờ đi: nghĩa là dò tìm xấp xỉ • Cột đầu tiên của bảng-dò nên được sắp xếp tăng dần • Excel dò đến một giá trị lớn nhất còn nhỏ hơn hay bằng giá- trị-dò và trả về giá trị trên cùng dòng ở ô tương ứng trên cột của bảng-dò.

Hàm dò tìm

Dò tìm tương đối

Dò tìm chính xác

Hàm dò tìm

• Nếu tìm thấy đúng giá-trị-dò thì trả về giá trị trên cùng cột ở

ô tương ứng trên dòng của bảng-dò.

– Kiểu-dò là True, hoặc 1, hoặc lờ đi: nghĩa là dò tìm xấp xỉ • Dòng đầu tiên của bảng-dò nên được sắp tăng dần. • Excel dò đến một giá trị lớn nhất còn nhỏ hơn hay bằng giá- trị-dò và trả về giá trị trên cùng cột ở ô tương ứng trên dòng của bảng-dò.

• HLOOKUP(giá-trị-dò, bảng-dò, dòng, kiểu-dò): dùng giá- trị-dò để dò tìm trên dòng đầu tiên của bảng-dò. Có 2 trường hợp: – Kiểu-dò là False hay 0: nghĩa là dò tìm chính xác

Hàm dò tìm

Hàm dò tìm • MATCH(giá-trị-dò, bảng-dò, kiểu-dò): Trả về thứ tự vị trí của giá-trị-dò trong bảng-dò. Chú { bảng-dò chỉ là 1 dòng hoặc 1 cột. – Kiểu-dò là 0 (nếu muốn dò chính xác và bảng-dò được sắp

xếp tùy {),

– Kiểu-dò là 1 hoặc lờ đi (MATCH tìm đến giá trị lớn nhất còn nhỏ hơn hay bằng giá-trị-dò và bảng-dò phải được sắp xếp tăng dần),

– Kiểu-dò là 1 (MATCH tìm đến giá trị nhỏ nhất còn lớn hơn

hay bằng giá-trị-dò và bảng-dò phải được sắp xếp giảm dần).

Hàm dò tìm

• CHOOSE(i, x1, x2, ...): trả về xi trong một danh sách

các giá trị x1, x2,…

• INDEX(bảng-dò, dòng, cột): trả về giá trị trong bảng-

dò tương ứng với tọa độ dòng, cột.

Hàm dò tìm

Dò tìm 2 chiều: kết hợp Index và Match

Hàm luận lý • AND(btLogic1, btLogic2, …): trả về TRUE nếu tất cả các btLogic đều có giá trị TRUE, trả về FALSE nếu có ít nhất một btLogic có giá trị FALSE

• OR(btLogic1, btLogic2, …) : trả về FALSE nếu tất cả các btLogic đều có giá trị FALSE, trả về TRUE nếu có ít nhất một btLogic có giá trị TRUE

• NOT(btLogic): trả về giá trị phủ định của btLogic • IF(btLogic1, bt1, bt2): Nếu btLogic có giá trị TRUE thì trả về bt1, ngược lại btLogic có giá trị FALSE thì trả về bt2

Hàm luận lý

Hàm thông tin

ngược lại trả về FALSE

• ISBLANK(tham_số): Trả về TRUE nếu tham_số là ô rỗng,

• ISERR(tham_số): Trả về TRUE nếu tham_số là các báo

lỗi: #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!, ngược lại trả về FALSE

• ISERROR(tham_số): Trả về TRUE nếu tham_số là các báo lỗi: #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!, ngược lại trả về FALSE

• ISLOGICAL(tham_số): Trả về TRUE nếu tham_số là giá trị

logic (TRUE, FALSE), ngược lại trả về FALSE

Hàm thông tin

• ISNA(tham_số): Trả về TRUE nếu tham_số là báo lỗi #N/A,

ngược lại trả về FALSE

• ISNONTEXT(tham_số): Trả về TRUE nếu tham_số không là

kiểu chuỗi, ngược lại trả về FALSE

• ISTEXT(tham_số): Trả về TRUE nếu tham_số là kiểu chuỗi,

ngược lại trả về FALSE

• ISNUMBER(tham_số): Trả về TRUE nếu tham_số là kiểu số,

ngược lại trả về FALSE

Hàm thông tin

• ISODD(tham_số): Trả về TRUE nếu tham_số là số lẻ, ngược lại

trả về FALSE

• ISREF(tham_số): Trả về TRUE nếu tham_số là tham chiếu (địa

chỉ ô, vùng), ngược lại trả về FALSE

• TYPE(tham_số): Trả về số cho biết kiểu của tham_số (trả về 1:

kiểu số; 2: kiểu chuỗi; 4: kiểu logic; 16: báo lỗi; 64: vùng)

Hàm tài chính

• Rate: Lãi suất vay tại từng thời điểm, hoặc phần trăm chiết khấu

hoặc lợi tức trên vốn đầu tư

• Nper (Number of PERiod): Số lượng các kz (như tổng số kz thanh

toán trong mỗi năm, mỗi tháng, mỗi ngày...)

• Pmt (PayMenT): Số tiền trả cho mỗi kz • Pv (Present Value): Giá trị hiện tại, là giá trị tiền gộp cho việc đầu

tư hay cho vay

• Fv (Future Value): Giá trị tương lai, là số tiền của việc đầu tư trong

một khoảng thời gian ở tương lai.

• Type: Hình thức thanh toán - khi kết thúc hoặc bắt đầu kz. Có thể

có giá trị là 0 hoặc là 1. Nếu lờ đi thì mặc định là giá trị 0.

Hàm tài chính

• FV(Rate, Nper, Pmt, Pv, Type): trả về giá trị tương lai của các

khoản đầu tư giống nhau trong nhiều kz.

• PV(Rate, Nper, Pmt, Fv, Type): trả về giá trị hiện tại của các

khoản đầu tư giống nhau trong nhiều kz.

• RATE(Nper, Pmt, Pv, Fv, Type): tính lãi suất tiền gửi / tiền vay • PMT(Rate, Nper, Pv, Fv, Type): tính số tiền phải trả dòng kz cho những khoản nợ vay / số tiền nhận được dòng kz của những khoản cho vay.

• NPER(Rate, Pmt, Pv, Fv, Type): trả về số kz phải trả lãi / nhận

lãi của các khoản đầu tư.

Chương 3: Định dang dữ liệu và bảng tính

• Định dạng dữ liệu • Các thao tác trên dữ liệu • Định dạng các thành phần của bảng tính

ĐỊNH DẠNG DỮ LIỆU

Định dạng font chữ • Dùng nút lệnh tắt

1

2

3

4

5

6

7

8

9

10 ĐỊNH DẠNG DỮ LIỆU

Định dạng font chữ • Dùng hộp thoại

Nhấp nút Dialog Box Launcher, xuất hiện hộp thoại Format Cells, chọn thẻ Border, thẻ Font , thẻ Fill

ĐỊNH DẠNG DỮ LIỆU

Canh lề ô chữ • Dùng nút lệnh tắt

1

2

3

4

5

6

7

8

9

10

11 ĐỊNH DẠNG DỮ LIỆU

Canh lề ô chữ • Dùng nút hộp thoại

ĐỊNH DẠNG DỮ LIỆU

Định dạng số • Dùng nút lệnh tắt

1

2

3

4

5

6 ĐỊNH DẠNG DỮ LIỆU

Định dạng số • Dùng hộp thoại

CÁC THAO TÁC TRÊN DỮ LIỆU

• Xóa dữ liệu • Sao chép và dán • Di chuyển và dán • Sao chép và dán đặc biệt • Sao chép nhanh định dạng • Tìm kiếm • Tìm kiếm và thay thế • Di chuyển • Di chuyển đặc biệt

CÁC THAO TÁC TRÊN DỮ LIỆU

– Chọn vùng nguồn (các ô/vùng cần sao chép),

– Chọn Home  Clipboard  Copy hoặc nhấn hoặc nhấn

• Sao chép và dán đặc biệt

Ctrl+C

– Chuyển con-trỏ-ô đến vùng đích (vùng cần sao chép đến)

– Chọn Home  Clipboard, nhấn nút ở biểu tượng Paste,

xuất hiện một menu với các biểu tượng của các kiểu dán đặc biệt, trỏ chuột vào biểu tượng sẽ hiển thị lời nhắc hoặc bạn nhấp mục Paste Special

CÁC THAO TÁC TRÊN DỮ LIỆU

• Sao chép và dán đặc biệt

ĐỊNH DẠNG THÀNH PHẦN BẢNG TÍNH

• Chèn cột, hàng, ô, sheet • Xóa cột, hàng, ô • Định dạng cột • Định dạng hàng • Định dạng có điều kiện một vùng • Kiểu bảng (Table Style) • Kiểu ô

ĐỊNH DẠNG THÀNH PHẦN BẢNG TÍNH

• Định dạng có điều kiện một

vùng

– Chọn vùng cần định dạng có

điều kiện

– Chọn Home  Styles  Conditional Formatting

ĐỊNH DẠNG THÀNH PHẦN BẢNG TÍNH

• Cột Windows: tô màu các ô có điểm > 4

– Chọn Home  Styles  Conditional Formatting 

Hightlight Cells Rules  Greater Than, xuất hiện một hộp thoại, gõ vào số 4 với Light Red Fill with Dark Red Text (nền đỏ nhạt, chữ đỏ đậm)

• Cột Loại: tô màu xanh lá cây đậm những ô loại A

– Chọn Home  Styles  Conditional Formatting 

Hightlight Cells Rules  Text that Contains, xuất hiện một hộp thoại, gõ vào k{ tự A; về màu sắc, bạn chọn Custom Format và chọn Green trong thẻ Font của hộp thoại Format Cells.

ĐỊNH DẠNG THÀNH PHẦN BẢNG TÍNH

• Cột Word: thanh màu hồng (từ đậm đến nhạt) thay đổi chiều

ngang theo giá trị số – Chọn Home  Styles  Conditional Formatting  Data

Bars, xuất hiện một menu, chọn mẫu thứ 3 trong Gradient Fill.

• Cột Excel: dùng thang 3 màu (Xanh lá cây – Trắng – Đỏ) chuyển

màu dần dần theo giá trị số. – Chọn Home  Styles  Conditional Formatting  Color

Scales, xuất hiện một menu, chọn mẫu thứ 3.

ĐỊNH DẠNG THÀNH PHẦN BẢNG TÍNH

• Cột PowerPoint: dùng các biểu tượng (5 mũi tên màu), mỗi biểu tượng tượng trưng cho một giá trị. – Chọn Home  Styles  Conditional Formatting  Icon Sets, xuất hiện một menu, chọn mẫu thứ 4 trong khung Directional.

• Cột ĐTB: màu đỏ cho điểm dưới trung

bình (< 5) – Chọn Home  Styles  Conditional Formatting  New Rules, xuất hiện hộp thoại New Formatting Rule,

Chương 4: Cơ sở dữ liệu

• Khái niệm • Sắp xếp dữ liệu • Trích lọc dữ liệu • Hàm cơ sở dữ liệu • Tách nội dung dữ liệu • Kiểm tra dữ liệu nhập • Hợp nhất dữ liệu • Tổng con • Tổng hợp dữ liệu đa chiều

KHÁI NIỆM CƠ SỞ DỮ LIỆU

A5:G21 gồm nhiều trường (field) và nhiều hàng với các qui ước: – Tên trường là duy nhất trong sheet, phải nhập trong từng ô

riêng và không là ô trống.

– Không để dòng trống và trường trống trong cơ sở dữ liệu. – Cơ sở dữ liệu phải tách biệt so với những phần khác trong

sheet.

• Trong Excel, một cơ sở dữ liệu là một bảng như bảng

KHÁI NIỆM CƠ SỞ DỮ LIỆU

SẮP XẾP THỨ TỰ

– Đặt con-trỏ-ô vào một ô trong cột muốn xếp thứ tự – Chọn thẻ Data, nhóm Sort & Filter, nhấp nút 1 hay nút 2 tùy

theo bạn muốn xếp theo thứ tự tăng hay giảm.

• Cách 1: Thực hiện các bước sau:

3

1

4

2

5 SẮP XẾP THỨ TỰ

– Đặt con-trỏ-ô vào một ô trong cột muốn xếp thứ tự, – Chọn lệnh Home  Editing  Sort & Filter  Sort A to Z hoặc Sort Z to A tùy theo bạn muốn xếp theo thứ tự tăng hay thứ tự giảm

• Cách 2: Thực hiện các bước sau:

SẮP XẾP THỨ TỰ

– Chọn cơ sở dữ liệu chứa các cột muốn xếp thứ tự, – Chọn lệnh Data  Sort & Filter  Sort hoặc lệnh Home 

• Cách 3: Thực hiện các bước sau:

Editing  Sort & Filter  Custom Sort

LỌC DỮ LIỆU

– Trỏ chuột vào bất kz ô nào trong vùng cơ sở dữ liệu hoặc chọn

cả vùng cơ sở dữ liệu,

– Chọn lệnh Data  Sort & Filter  Filter

hoặc lệnh Home  Editing  Sort & Filter  Filter, xuất hiện nút xổ xuống (Dropdown) ở mỗi cột trong cơ sở dữ liệu

• Lọc tự động

LỌC DỮ LIỆU

sở dữ liệu,

• (2) Thiết lập vùng trích ghi (nếu cần) • (3) Chọn lệnh Data  Sort & Filter 

Lọc nâng cao • (1) Thiết lập vùng tiêu chuẩn bên dưới cơ

Advanced, xuất hiện hộp thoại Advanced Filter

LỌC DỮ LIỆU

– Các tiêu chuẩn trên cùng một hàng nghĩa là “và” – Các tiêu chuẩn trên các hàng khác nhau nghĩa là “hoặc” • Nếu tiêu chuẩn là ô trống thì “tất cả các mẩu tin đều thỏa” • Nếu tiêu chuẩn thuộc loại tiêu chuẩn so sánh thì không xóa tên cột

trong vùng tiêu chuẩn. Nếu tiêu chuẩn thuộc loại tính toán – nghĩa là kết quả sau khi ban hành công thức là TRUE hoặc FALSE – thì sử dụng địa chỉ ô của mẩu tin đầu tiên trong vùng danh sách; đồng thời xóa trắng hay thay tên cột tương ứng của vùng tiêu chuẩn bằng một từ nào đó.

Các qui ước về vùng tiêu chuẩn: • Các tên cột chứa tiêu chuẩn được sao chép vào vùng tiêu chuẩn. • Tiêu chuẩn được nhập vào dưới tên cột tương ứng của vùng tiêu chuẩn:

LỌC DỮ LIỆU

Các ví dụ về vùng tiêu chuẩn: (1) Lọc ra các nhân viên thuộc phòng A, B hay P

LỌC DỮ LIỆU

Các ví dụ về vùng tiêu chuẩn: (2) Lọc ra các nhân viên thuộc phòng A, B hay P và có LCB > 500

LỌC DỮ LIỆU

Các ví dụ về vùng tiêu chuẩn: (3) Lọc ra các nhân viên sinh năm 1965

HÀM CƠ SỞ DỮ LIỆU

• DAVERAGE(vùng danh sách, cột, vùng tiêu chuẩn): Trả về giá trị trung bình của các ô trong tham số cột trong tham số vùng danh sách, các ô này thỏa điều kiện của tham số vùng tiêu chuẩn.

• DCOUNT(vùng danh sách, cột, vùng tiêu chuẩn): Trả về số lượng các ô kiểu số trên cột trong vùng danh sách, các ô này thỏa tiêu chuẩn.

• DCOUNTA(vùng danh sách, cột, vùng tiêu chuẩn): Trả về số lượng các ô khác rỗng trên cột trong vùng danh sách, các ô này thỏa tiêu chuẩn.