Bài giảng Tin học đại cương - Phần 4 Microsoft Excel 2010

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 toán. • 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. • 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ổ. Các khái niệm cơ bản Workbook Sheet: trang bảng tính (255) Column: cột (A,B,XFD: 214=16.384) Row: dòng (1,2,,220) cell: ô (C7) Địa chỉ ô hiện hành Nội dung ô hiện hành 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 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 Các khái niệm cơ bản 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 bên trong dữ liệu là ký tự chữ, dấu gạch dưới ( _ ), khoảng trắng – 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 ). • 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 Cancel Enter Insert Function Name Box 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 Nhập liệu và chỉnh sửa Địa chỉ tham chiếu Ô hoặc vùng Địa chỉ Ô ở cột A và dòng 1 A1 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:D10 Vùng gồm các ô trong cột A và các hàng từ 5 đến 10 A5:A10 Vùng gồm các ô trên hàng 5 và từ cột B đến cột G B5:G5 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 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 • Ngoài cách gõ trực tiếp ký tự $, bạn có thể nhấn phím F4, địa chỉ ô vứa gõ trong công thức sẽ thay đổi xoay vòng: “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ở) Địa chỉ tham chiếu 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 tên cho ô/vùng: • Cách 2: Dùng hộp thoại – Chọn Formulas  Defined 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 Đặt tên cho ô/vùng: • Cách 3: định nghĩa tên từ tên cột hay dữ liệu của cột đầu tiên trong bảng – Chọn cả bảng dữ liệu Chọn Formulas  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 Quản lý tên ô/vùng: • Chọn lệnh Formulas  Defined Names  Name Manager, xuất hiện hộp thoại Name Manager Tên Ô/Vùng 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 – #REF! : Lỗi do tham chiếu đến vùng dữ liệu không hợp lệ – #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. • 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 chuỗi (Text) Hàm ngày giờ (Date&Time) • NOW(): trả về ngày-tháng-năm và giờ hiện hành mà máy đang lưu giữ • TODAY(): trả về ngày-tháng-năm hiện hành mà máy đang lưu giữ • DATE(năm, tháng, ngày): trả về ngày-tháng-năm tương ứng • DAY(ngày-tháng-năm): trả về một con số (1  31) chỉ ngày 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ỉ tháng tương ứng với tham số ngày-tháng-năm • 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: • 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. Hàm ngày giờ (Date&Time) • TIME(giờ, phút, giây): trả về thời gian tương ứng với 3 tham số: giờ, phút, giây đã cho • HOUR(btgiờ): trả về một số chỉ giờ (0  23) tương ứng với btgiờ • 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 ứng với btgiờ Hàm ngày giờ (Date&Time) 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ê • MIN(danh-sách) : trả về giá trị nhỏ nhất của các số trong danh-sách • MAX(danh-sách) : trả về giá trị lớn nhất của các số trong danh-sách • AVERAGE(danh-sách) : trả về giá trị trung bình của các 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 vùng thỏa điều-kiện • 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 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 • 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 • 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ò. 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 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 • ISBLANK(tham_số): Trả về TRUE nếu tham_số là ô rỗng, ngược lại trả về FALSE • 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 kỳ (như tổng số kỳ 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 kỳ • 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 kỳ. 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 kỳ. • 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 kỳ. • 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 kỳ cho những khoản nợ vay / số tiền nhận được dòng kỳ của những khoản cho vay. • NPER(Rate, Pmt, Pv, Fv, Type): trả về số kỳ 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 65 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 • Sao chép và dán đặc biệt – 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 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 ĐỊ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 • Trong Excel, một cơ sở dữ liệu là một bảng như bảng 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. KHÁI NIỆM CƠ SỞ DỮ LIỆU SẮP XẾP THỨ TỰ • Cách 1: Thực hiện các bước sau: – Đặ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. 1 2 3 4 5 SẮP XẾP THỨ TỰ • Cách 2: Thực hiện các bước sau: – Đặ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 SẮP XẾP THỨ TỰ • Cách 3: Thực hiện các bước sau: – 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  Editing  Sort & Filter  Custom Sort LỌC DỮ LIỆU • Lọc tự động – Trỏ chuột vào bất kỳ ô 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 DỮ LIỆU Lọc nâng cao • (1) Thiết lập vùng tiêu chuẩn bên dưới cơ 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  Advanced, xuất hiện hộp thoại Advanced Filter LỌC DỮ LIỆU 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: – 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 đó. 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. HÀM CƠ SỞ DỮ LIỆU • DGET(vùng danh sách, cột, vùng tiêu chuẩn): Trả về một giá trị của một ô trên cột trong vùng danh sách, ô này thỏa tiêu chuẩn. • DMAX(vùng danh sách, cột, vùng tiêu chuẩn): Trả về giá trị lớn nhất của các ô trên cột trong vùng danh sách, các ô này thỏa tiêu chuẩn. • DMIN(vùng danh sách, cột, vùng tiêu chuẩn): Trả về giá trị nhỏ nhất của các ô trên cột trong vùng danh sách, các ô này thỏa tiêu chuẩn. • DSUM(vùng danh sách, cột, vùng tiêu chuẩn): Trả về tổng giá trị của các ô trên cột trong vùng danh sách, các ô này thỏa tiêu chuẩn. TÁCH NỘI DUNG 1 Ô THÀNH NHIỀU CỘT KIỂM TRA DỮ LIỆU NHẬP HỢP NHẤT DỮ LIỆU(CONSOLIDATE) TỔNG CON (SUBTOTAL) PIVOT TABLE