Bài giảng Tin học ứng dụng - Chương 3: Excel 2010 - Lê Hữu Hùng

ngày, giờ hoặc số không đúng với định dạng đã khai báo thì excel không nhận diện được sẽ tự động chuyển sang dữ liệu kiểu chuỗi.10:08 AMMicrosoft Excel 201019II.4 Khối dữ liệu và phạm vi thao tácII.4.1 Khái niệm khối- Khối là 1 Cell hoặc nhiều Cell liên tục- Địa chỉ khối được xác định bằng::VD: Khối dự liệu B2: E7 là gồm tất cả các Cell từ B2 đến E7 .Cách chọn khối+ Chọn một Cell + Chọn một cột+ Chọn một khối + Chọn một hàng- Ngoài ra có thể dùng Phím Shift + các phím mũi tên để chọn khối.10:08 AMMicrosoft Excel 201020II.4 Khối dữ liệu và phạm vi thao tácII.4.2 Khái niệm phạm vi- Gồm 1 khối hoặc nhiều khối.- Cách chọn phạm vi nhiều khối:Dùng chuột quét khối đầu tiên, bấm giữ phím Ctrl rồi quét chọn các khối khác10:08 AMMicrosoft Excel 201021II.5 Hiệu chỉnh dữ liệuII.5.1 Sửa dữ liệu- Gồm hai cách:Cách 1: nhấp đôi chuột tại Cell cần sửa để sửa sửa xong ấn EnterCách 2: đặt dấu nháy tại Cell cần sửa rồi ấn phím chức năng F2 để sửa xong nhấn Enter.2210:08 AMMicrosoft Excel 2010II.5.2 Xoá dữ liệu gồm hai cách - Cách 1: Chọn phạm vi cần xoá  nhấm phím Delete ( chỉ xoá nội dung) - Cách 2: Chọn phạm vi cần xoá  chọn Tab Home  chọn Editing  ClearClear All: Xoá tất cả nội dung và định dạng + Chú thíchClear Formats: Chỉ xoá định dạngClear Contents: Chỉ xoá nội dungClear Comments: Chỉ xoá chú thíchClear Hypelinks: Chỉ xoá các liên kết 23II.5 Hiệu chỉnh dữ liệu10:08 AMMicrosoft Excel 2010II.5.3 Sao chép dữ liệu - Cách 1: dùng chuột quét Chọn dữ liệu cần sao chép, nhấn và giữ phím Ctrl di chuyển chuột tới nơi cần sao chép ( di chuyển chuột tại vị trí đường viền của phạm vi). - Cách 2: Chọn phạm vi dữ liệu  Home  Clipboard  Copy( Ctrl + C) sau đó đưa con trỏ đến vị trí đích, Chọn Tab Home  Group Clipboard  Paste( Ctrl + V). Ngoài ra sử dụng nút Handle để sao chép cho các Cell liền kề theo 4 hướng.24II.5 Hiệu chỉnh dữ liệu10:08 AMMicrosoft Excel 2010II.5.4 Di chuyển dữ liệu - Cách 1: chọn dữ liệu cần sao chép dùng chuột di chuyển tới vị trí cần (di chuyển chuột tại vị trí đường viền của phạm vi). - Cách 2: Chọn phạm vi dữ liệu  Home  Clipboard  Cut(Ctrl + X) sau đó đưa con trỏ đến vị trí đích, Chọn Tab Home  Group Clipboard  Paste( Ctrl + V).25II.5 Hiệu chỉnh dữ liệu10:08 AMMicrosoft Excel 2010II.6 Lập công thức ExcelII.6.1 Khái niệm công thức - Công thức giúp bảng tính hữu ích hơn rất nhiều, chúng ta dùng công thức để tính toán từ các dữ liệu lưu trữ trên bảng tính, khi dữ liệu thay đổi các công thức này sẽ tự động cập nhật các thay đổi và tính ra kết quả mới. - Công thức trong Excel được nhận dạng là do nó bắt đầu là dấu = và sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm.2610:08 AMMicrosoft Excel 2010Microsoft Excel 201010:08 AMII.6 Lập công thức ExcelII.6.1 Khái niệm công thức- Các toán tử trong công thức- Thứ tự ưu tiên của các toán tử27Toán tử Chức năng Ví dụ Kết quả + Cộng =3+3 3 cộng 3 là 6 - Trừ =45-4 45 trừ 4 còn 41 * Nhân =150*.05 150 nhân 0.50 thành 7.5 / Chia =3/3 3 chia 3 là 1 ^ Lũy thừa =2^4 =16^(1/4) 2 lũy thừa 4 thành 16 Lấy căn bậc 4 của 16 thành 2 & Nối chuỗi =”Lê” & “Thanh” Nối chuỗi “Lê” và “Thanh” lại thành “Lê Thanh” = Bằng =A1=B1 Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE > Lớn hơn =A1>B1 Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE = Lớn hơn hoặc bằng =A1>=B1 Ví dụ ô A1=3, ô B1=6 Kết quả: FALSE Khác =A1B1 Ví dụ ô A1=3, ô B1=6 Kết quả: TRUE , Dấu cách các tham chiếu =Sum(A1,B1) Ví dụ ô A1=3, ô B1=6 Kết quả: 9 : Tham chiếu mãng =Sum(A1:B1) Ví dụ ô A1=3, ô B1=6 Kết quả: 9 khoảng trắng Trả về các ô giao giữa 2 vùng =B1:B6 A3:D3 Trả về giá trị của ô Toán tử Mô tả Ưu tiên : (hai chấm) (1 khoảng trắng) , (dấu phẩy) Toán tử tham chiếu 1 – Số âm (ví dụ –1) 2 % Phần trăm 3 ^ Lũy thừa 4 * và / Nhân và chia 5 + và – Cộng và trừ 6 & Nối chuỗi 7 =, , =, So sánh 8 28II.6.2 Các loại địa chỉ Cell (khối)- Địa chỉ tương đối: A1 * B1- Địa chỉ tuyệt đối : $A$1 * $B$1- Địa chỉ hỗn hợp : $A1 * B$1Thao tác đặt tên khối: - Cách 1: Quét khối vùng địa chỉ muốn đặt tên gõ tên khối vào hộp NameBox( trên thanh công thức) - Cách 2: Để quản lý tên các khối đã đặt vào Formulaschọn nhóm Defined Names Name Manager xuất hiện hộp hội thoại II.6 Lập công thức Excel10:08 AMMicrosoft Excel 2010II.6.3 Cách lập công thức- Công thức bắt đầu bằng dấu =- Sau đó là sự kết hợp của các toán tử, các trị số, các địa chỉ tham chiếu và các hàm.- Nên tránh nhập trực tiếp các giá trị, con số vào công thức mà nên dùng đến tham chiếu.- Nếu trong công thức có sử dụng tên khối có thể nhấn F3 để chèn tên khối.- Khi đưa các tham số vào công thức là chuỗi thì bắt buộc phải đặt trong dấu “ ”29II.6 Lập công thức Excel10:08 AMMicrosoft Excel 201030Lỗi Giải thích #DIV/0!Trong công thức có phép chia cho 0(Zero) hoặc chia cho rỗng #NAME?Đánh sai tên hàm, hay tham chiếu hoặc thiếu dấu nháy#N/ACông thực tham chiếu đến ô mà có dùng hàm NA để kiểm tra sự tồn tại của dữ liệu hoặc hàm không có kết quả#NULL?Hàm sử dụng dữ liệu giao nhau của hai vùng mà hai vùng này không có phần chung nên phần giao nhau rỗng#NUM!Vấn đề đối với giá trị, nhần số âm trong khi đúng phải là giá trị dương#REF!Tham chiếu bị lỗi, thường là do ô tham chiếu trong hàm bị xoá#VALUE!Công thức tính toán có chứa dữ liệu không đúngII.6.4 Các lỗi hay gặp trong công thứcII.6 Lập công thức Excel10:08 AMMicrosoft Excel 2010II.7 Định dạng dữ liệu Sử dụng các lệnh trong Tab Home để định dạng. Có các Group sau: Khi cần định dạng phức tạp hơn ta quét khối dữ liệu cần định dạng  Click chuột phải chọn Format Cells Xuất hiện hộp thoại Format Cells Gồm các Tab sau:3110:08 AMMicrosoft Excel 2010II.8 Hiệu chỉnh bảng tính( cell, Row, Column, sheet)Chèn Ô, cột, dòng, sheetChọn Tab Home Nhóm Cell Lệnh InsertShift Cells Right: chèn 1 ô đẩy 1 ô cùng hàng sang phải.Shift cells Down: Chèn 1 ô đẩy các ô cùng cột xuống phía dưới.Entire Row: chèn một hàng vào vị trí hiện tại.Entire Column: Chèn thêm một cột vào vị trí hiện tại.Ngoài ra có thể click chuột vào vị trí cần chèn  Insert3210:08 AMMicrosoft Excel 2010- Xoá Ô, cột, Dòng, Sheet : Chọn Tab Home Cells  lệnh DeleteShift Cells Left: Xoá ô kéo các ô cùng hàng sang tráiShift Cells Up: Xoá một Ô, kéo các ô cùng cột ở phía dưới lênEntire Row: Xoá một hàng ở vị trí hiện tạiEntire Column: Xoá cột ở vị trí hiện tại- Ngoài ra có thể Click chuột tại vị trí cần xoá  Delete33II.8 Hiệu chỉnh bảng tính( cell, Row, Column, sheet)10:08 AMMicrosoft Excel 2010Ẩn Cột, Dòng, Sheet : chọn 1 trong các cách sau- Cách 1:Chọn Tab Home  Cells  Format  Hide & UnHide- Cách 2: Kích Chuột trên thanh tiêu đề hàng\ Cột  chọn lệnh Hide/ UnHideĐổi tên Sheet, Màu Tab Sheet, sao chép, di chuyển Sheet Chọn Tab Home  Cells  Format  Rename sheet, Move or Copy sheet, Tab Color34II.8 Hiệu chỉnh bảng tính( cell, Row, Column, sheet)10:08 AMMicrosoft Excel 2010III.1 Khái Niệm Hàm Hàm là một thành phần của dữ liệu loại công thức và được xem là những công thức được xây dựng sẵn nhằm thực hiện các công việc tính toán phức tạpDạng Tổng Quát:(Tham số 1, Tham số 2, )Tên hàm: là tên quy ước của hàm, không phân biệt chữ hoa hay chữ thườngCác tham số: Đặt cách nhau bởi dấu “,” hoặc “;” tuỳ theo khai báo trong môi trường của hệ điều hành.35III. Hàm Trong Excel10:08 AMMicrosoft Excel 2010III.1 Khái Niệm Hàm (tt) - Cách Nhập Hàm: chọn một trong những cách sau - Cách 1: Chọn Tab Fomulas  Function Library  lệnh Insert Function hoặc chọn hàm trong nhóm hàm tương tự - Cách 2 : Gõ trực tiếp hàm từ bàn phím.3610:08 AMMicrosoft Excel 2010III.2.1 Nhóm Hàm toán học & lượng giác ( Math & Trig)INT (Number) Công dụng: làm tròn số Number đến số nguyên gần nhất. Ví dụ: = INT (5.9) kết quả trả về 5 = INT (- 4.3)  kết quả trả về - 5 37III.2 Các Hàm Thông Dụng 10:08 AMMicrosoft Excel 2010Hàm MOD ( Number, Divisor) Công dụng: trả về số dư của phép chia số nguyên ( Number) cho số bị chia (Divisor)Ví Dụ: = Mod(7,3) kết quả trả về là 138III.2 Các Hàm Thông Dụng 10:08 AMMicrosoft Excel 2010Hàm Round (Number, Num_Digits) Công dụng: làm tròn số ( Number) Đến vị trí xác định bởi (Num_Digits).Nếu Num_ Digits là số dương thì làm tròn phần thập phân.Nếu Num_ Digits là số âm thì làm tròn phần nguyên.Ví Dụ: = Round(2324.1234,0)  2324 = Round(2324.1354,2)  2324.14 = Round(2354.1354,-2)  2400 = Round(2354.1354, - 3)  200039III.2 Các Hàm Thông Dụng 10:08 AMMicrosoft Excel 2010Hàm SUM( Number 1, Number 2, ) Công dụng: Hàm trả về tổng của các Number 1, Number 2, . Ví dụ: = Sum (10,20,5 )  35 = Sum ( 100, 256, 24)  38040III.2 Các Hàm Thông Dụng 10:08 AMMicrosoft Excel 2010Hàm SUMIF( Range, Criteria, [ Sum_Range] Hàm dùng để tính tổng có điều kiện, với các đối số sau:+ Range: Khối chứa giá trị để kiểm tra điều kiện tính tổng+ Criteria: Điều kiện để kiểm tra từng giá trị trong khối điều kiện+ Sum _ Range: Khối chứa giá trị cần tính tổng ( nếu không có tham số Sum_ Range thì Excel sẽ coi Sum_Range = Range).41III.2 Các Hàm Thông Dụng 10:08 AMMicrosoft Excel 2010Hàm SumifS: Cú pháp SUMIFS(sum_range,criteria_range1,criteria1,criteri a_range2,criteria2)+Sum_range là các ô cần tính tổng, bao gồm các con số, tên vùng, mãng hay các tham chiếu đến các giá trị. Các ô trống hay chứa chuỗi (Text) sẽ bị bỏ qua.+Criteria_range1, criteria_range2, có thể khai báo từ 1 đến 127 vùng dùng để liên kết với các điều kiện cho vùng.+Criteria1, criteria2, có thể có từ 1 đến 127 điều kiện ở dạng con số, biểu thức, tham chiếu hoặc chuỗi. 42III.2 Các Hàm Thông Dụng 10:08 AMMicrosoft Excel 2010Hàm SumifS (tt) Lưu Ý khi sử dụng hàm - Mỗi ô trong vùng sum_range chỉ được cộng khi thoả tất cả các điều kiện Ô trong sum_range thoả điều kiện (True) tương đương với số 1, và không thoả điều kiện (False) tương đương với số 0 (zero)- Mỗi criteria_range phải có cùng kích thước và hình dạng giống với sum_range. Ví dụ kích thước của sum_range là 3x2 (3 dòng 2 cột) thì criteria_range phải có kích thước là 3x2 (3 dòng 2 cột) - Ta có thể sử dụng các ký tự thay thế như ?, * trong điều kiện. Dấu ? thay cho một ký tự nào đó và dấu * thay cho một chuỗi nào đó. Khi điều kiện sau lại là dấu ? hay * thì bạn đặt thêm dấu ~ phía trước nó.43III.2 Các Hàm Thông Dụng 10:08 AMMicrosoft Excel 2010Hàm SUMPRODUCT( ARRAY1, [ARRAY 2], [ARRAY 3], ) - Tính tổng của tích các phần tử trong các mảng tương ứng. Array 1, Array 2 có thể từ 2 cho đến 255 mảng và các mảng này phải cùng kích thước với nhau.SumProduct(A1:A3,B1:B3,C1:C3) kết Quả 27144ABC135921643278III.2 Các Hàm Thông Dụng 10:08 AMMicrosoft Excel 2010III.2.2 Hàm xử lý chuỗiHàm LEFT : Lấy n các ký tự bên trái của 1 chuỗi- Cú pháp: = LEFT(Text, [num_chars])VD: Tại ô B2 có lập công thức sau: = Left(A2,2)45AB1Mã HàngLoại Hàng2AB23DCAB3BP45FCBP4FAA23GFALưu Ý: Các khoảng trắng cũng được coi là 1 ký tự. III.2 Các Hàm Thông Dụng 10:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm Right: Lấy n các ký tự bên Phải của 1 chuỗi- Cú pháp: = Right(Text, [num_chars])VD: Tại ô B2 có lập công thức sau: = Right(A2,4)46AB1Mã HàngLoại Hàng2AB23DC23DC3BP45FC45FC4FAA23GA23GLưu Ý: Các khoảng trắng cũng được coi là 1 ký tự. 10:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm MID Lấy n các ký tự của 1 chuỗi, kể từ vị trí xác định- Cú pháp: = MID(Text, start_num, num_chars)VD: Tại ô B2 có lập công thức sau: = Mid(A2,3,2)47AB1Mã HàngLoại Hàng2AB23DC233BP45FC454FAA23GA210:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm PROPER: Đổi ký tự đầu của 1 từ thành chữ in- Cú Pháp = PROPER(Chuỗi ký tự)VD: Tại ô B2 có lập công thức sau: = PROPER(A2)48AB1Họ và tênKết Quả2trần thanh hoàngTrần Thanh Hoàng3nguyễn anh sơnNguyễn Anh Sơn4vũ thuý hườngVũ Thuý Hường10:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm TRIM: Trả về chuỗi ký tự sau khi đã cắt bỏ các ký tự dư thừa. Các ký tự dư thừa là các đầu chuỗi, cuối chuỗi hoặc là nhiều hơn 1 ký tự giữa hai từ- Cú Pháp = TRIM(Chuỗi ký tự)VD: Tại ô B2 có lập công thức sau: = Trim(A2)49AB1Họ và tênKết Quả2 Trần Thanh HoàngTrần Thanh Hoàng10:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm LEN: Trả về giá trị chiều dài của chuỗi ký tự - Cú Pháp = Len(Chuỗi ký tự)VD: Ô B2 lập công thức sau: = Len(A2)50AB1Họ và tênKết Quả2 Trần Thanh Hoàng1610:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm VALUE: Đổi giá trị chuỗi ra giá trị số- Cú Pháp = VALUE(Chuỗi ký tự)VD: Ô B2 lập công thức sau: = value(right(A2,4))51AB1Họ và tênKết Quả2 huia1023012310:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng III.2.3 Các Hàm Xử Lý Ngày Giờ (Date &Time)Hàm TODAY() Trả về giá trị ngày tháng năm của hệ thống Hàm NOW() Trả về giá trị ngày tháng năm và giờ phút của hệ thốngHàm DAY( Biểu thức ngày – tháng – năm)Trả về giá trị Phần Ngày của Biểu thứcHàm MONTH(Biểu Thức ngày - Tháng – Năm)Trả về phần giá trị Tháng của biểu thứcVí Dụ: Month(29/09/2011) 095210:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm YEAR(Biểu thức Ngày – Tháng – Năm)- Trả về giá trị Phần Năm trong biểu thứcHàm weekday(biểu thức ngày – tháng – năm, Kiểu trả về)- Trả về giá trị là số thứ trong tuần của biểu thức ngày tháng năm- Các giá trị kiểu trả có ý nghĩa như sau:Kiểu 1: chủ nhật là 1 và tuần tự cho đến thứ 7Kiểu 2: Thứ hai là 1 và tuần tự cho đến thứ 7 5310:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng III.2.4 Nhóm hàm luận lý Hàm AND: Trả về True nếu tất cả đối số là True, Trả về False nếu ít nhất một đối số là False- Cú pháp: AND(logical1,logical2,. . .)Ví dụ: =IF(AND(A2=“A”,B2>= 20),"Đậu","Rớt")54ABC1Khối ThiĐiểm ThiK.Quả2A24Đậu3A19Rớt4B20Rớt10:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm OR: Trả về True nếu ít nhất một đối số là True, Trả về False nếu tất cả các đối số là False- Cú pháp: OR(logical1,logical2,. . .)Ví dụ: =IF(OR(A2=“A”,B2>=20,"Đậu","Rớt")55ABC1Khối ThiĐiểm ThiK.Quả2A24Đậu3B20ĐẬU4D19RỚT10:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm NOT: Trả về giá trị True nếu Logical có giá trị FALSE và ngược lại - Cú pháp: = NOT(logical) VD: Tại Ô C2 = Not(A2>B2)56ABC1ToánvănK.Quả2105FALSE378TRUE494FALSE10:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm IF: Thực hiện một công việc nào đó dựa vào điều kiện so sánh- Cú pháp: = IF(logical_test, [value if true], [value if false]) VD: Tại Ô C2 = If(B2>20, “Đậu”, “Rớt”57ABC1Khối ThiĐiểm ThiK.Quả2A24Đậu3B20ĐẬU4D19RỚT10:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng III.2.5 Nhóm hàm dò tìm & Tham chiếuHàm VLOOKUP: Hàm này tìm kiếm 1 giá trị trong cột bên trái của vùng dữ liệu và trả về giá trị trên cột thứ n nếu tìm thấy, hoặc trả về #N/A nếu không tìm thấy- Cú pháp: =VLOOKUP(lookup_value, table_aray, col_index_num, [range_lookup])+lookup value: Là giá trị tìm kiếm trong cột bên trái của bảng dò+table aray: Là vùng chứa dữ liệu được tìm kiếm cho trước+col index num: Là số thứ tự các cột trong vùng dữ liệu nơi mà Hàm Vlookup sẽ lấy giá trị trả về, n=1 chỉ cột đầu tiên, n=2 là cột thứ 2,. . .+[x]: Là kiểu dò tìm , với x = 0 thì tìm kiếm chính xác, x = 1 thì tìm kiếm gần đúng. 5810:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Ví dụ : Tên hàng dựa vào mã hàng và bảng phụTại ô B2 đặt Công thức sau: = Vlookup(A2,$E$1:$F$4,2,0)59EFBảng phụ1Mã HTên2DUĐường3KEKẹo 4SUSữa AB1Mã HàngTên hàng 2KEKẹo 3DUĐường4SUSữa10:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm HLOOKUP: Hàm này tìm kiếm 1 giá trị trong hàng đầu tiên của vùng dữ liệu và trả về giá trị trên hàng thứ n nếu tìm thấy, hoặc trả về #N/A nếu không tìm thấy- Cú pháp: HLOOKUP( Trị dò, Bảng dò, N, [X])+Trị dò: Là giá trị tìm kiếm trong cột bên trái của vùng dữ liệu.+Bảng dò: Là vùng chứa dữ liệu được tìm kiếm cho trước (vùng dữ liệu chứa địa chỉ tuyệt đối)+N: Là số thứ tự các hàng trong vùng dữ liệu nơi mà Hàm Vlookup sẽ lấy giá trị trả về, n=1 chỉ hàng đầu tiên, n=2 là hàng thứ 2,. . .+[X]: Là kiểu dò tìm, với x = 0 thì tìm kiếm chính xác, x = 1 thì tìm kiếm gần đúng.6010:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Ví dụ: xếp loại dựa vào điểm số và bảng phụ = HLOOKUP(A2,$E$2:$J$9,2,1) Kiểu dò tìm không chính xác61EFGHIj1Bảng phụ 2ĐTB257893Xếp loạiKémT BìnhKháGiỏiXuất SắcAB1Điểm Xếp loại 23Kém 36T Bình49Xuất sắc10:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm MATCH( Trị dò, bảng dò, [Cách dò])-Cú Pháp: = Match(Lookup_Array, Lookup_Array, Match_type)-Hàm trả về số thứ tự của trị dò trong khối bảng dò, -Thông thường bảng dò chỉ là 1 hàng hay là 1 cột-Số thứ tự trị dò được tính từ trên xuống nếu bảng dò là 1 cột hoặc từ dưới lên nếu bảng dò là 1 hàng- Match_type = 1: tìm tương đối, danh sách của bảng dò phải sắp xếp theo thứ tự tăng dần. Nếu tìm không thấy sẽ trả vị trí của giá trị lớn nhất nhưng nhỏ hơn trị dò6210:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng - Match_type = 0: Tìm chính xác, nếu tìm không thấy sẽ trả về lỗi #N/A- Match_type = -1: Tìm tương đối, danh sách các giá trị dò tìm phải sắp xếp theo giá trị giảm dần. Nếu tìm không thấy sẽ trả về giá trị nhỏ nhất nhưng lớn hơn trị dòVí dụ: Match(“TH”,A1:A4,0) 363AB1CH#N/A2DT#N/A3TH34DL#N/A10:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm INDEX: Trả về giá trị trong của ô giao nhau giữa thứ tự hàng và thứ tự cột trong bảng dò-Cú pháp: Index(bảng dò, hàng dò, cột dò)Ví dụ : Index(A1:H4,2,3) kết quả 15 Index(A1:H4,4,6) kết quả 3764ABCDEFGH1101214161820222421113151719212325326283032343638404272931333537394110:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm OFFSET- Cú pháp OFFSET(reference, Rows, cols, [height], [width])+ Reference: Vùng địa chỉ gốc, khối gốc+ Rows: Số dòng dịch lên hay dịch xuống tính từ Reference+ Cols: Số cột dịch qua tính từ Reference + [Height]: Chỉ định số dòng muốn tham chiếu trả về, nếu bỏ qua tham số này thì số dòng của khối trả về = khối gốc ban đầu+ [width]: Chỉ định số cột muốn tham chiếu trả về, nếu bỏ qua tham số này thì số cột của khối mới = khối gốc ban đầu.6510:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Ví dụ : = Offset(B2,1,2)Kết quả : 32= Sum(offset(B2,1,2,2,3)Kết quả: 207= Count(Offset(B2,1,2,2,3)Kết quả: 6Hàm OFFSET có thể làm đối số bên trong các hàm khác như một vùng địa chỉ tham chiếu66ABCDEFGH1101214161820222421113151719212325326283032343638404272931333537394110:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm ROW([Reference]) : hàm trả về chỉ số dòng đầu tiên của ô đầu tiên trong vùng tham chiếu- Nếu không có đối số Reference, xem như trả về chỉ số của ô hiện tại, ô đang lập công thứcVí dụ 1: = ROW(D3:F4) 3Ví dụ 2 :Tại A4 đặt công thức sau: = ROW() 4Ví dụ 2: không có đối số referenceDo đó hàm row trả về trị số ở hàng Ở ngay ô đặt công thức – Coi và tìm hiểu hàm ROWS67ABCDEF11012141618202111315171921326283032343644293133353710:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm Column([Reference]) : hàm trả về chỉ số cột đầu tiên của ô đầu tiên trong vùng tham chiếu- Nếu không có đối số Reference, xem như trả về chỉ số của ô hiện tại, ô đang lập công thứcVí dụ 1: = Column(D3:F4) 4Ví dụ 2: Tại A4 đặt công thức sau: = Column () 1Ví dụ 2: không có đối số referenceDo đó hàm Column trả về trị số ở hàng Ở ngay ô đặt công thức – Coi và tìm hiểu hàm Columns68ABCDEF11012141618202111315171921326283032343644293133353710:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng III.2.6 Nhóm hàm thống kê (Statistical)Hàm Max( Number1, Number2,)- Hàm trả về số lớn nhất trong các đối số Number1, 2 nVí dụ: = Max(10,35,56,78,100) 100 = Max(A1:F4) 37 = Max(B1:B4)29 = Max(A2:F2) 2169ABCDEF11012141618202111315171921326283032343644293133353710:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng - Hàm MaxA( value1,[value2], [value3], )- Hàm trả về số lớn nhất trong các đối số value1, 2 N- Tính các ô trong đối số có giá trị True là 1- Tính các ô trong đối số có giá trị False là 0 Tính các ô trong đối số có giá trị chuỗi là 0 = MaxA(10,35,56,78,100)100 = MaxA(A1:D4) 32 = MaxA(A1:A4) ? = MaxA(A4:D4) ?70ABCD1cá-10True02111315173-12830324False-1Tên-3310:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm Min( Number1, Number2,)-Hàm trả về số nhỏ nhất trong các đối số Number1, 2 NVí dụ: = Min(10,35,56,78,100) 10 = Min(A1:F4) 4 = Min(B1:B4)12 = Min(A1:F3) 671ABCDEF1101214161820211131517192132628306343644293133353710:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm MinA( value1,[value2], [value3], )- Hàm trả về số nhỏ nhất trong các đối số value1, 2 N- Tính các ô trong đối số có giá trị True là 1- Tính các ô trong đối số có giá trị False là 0- Tính các ô trong đối số có giá trị chuỗi là 0 = MinA(10,35,56,78,100)10 = MinA(A1:D4)  -10 = MinA(A1:A4)  -1 = MinA(A4:D4)  ? = MinA(A2:D2) -> ?72ABCD1cá-10True02-1131517302830324False-1TênTrue10:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm AVERAGE (number1, number2,)- Hàm trả về giá trị trung bình các đối số number1, 2n- Từng đối số có thể là 1 nhóm các giá trị số hoặc 1 mảng Ví dụ:Tại ô E2 lập công thức Average(A2:C2) 73ABCDE1ToánHoáVănT.cộngT.bình25107227.3333956196.6664658196.33310:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm AVERAGEA(VALUE1, VALUE2,)- Hàm trả về giá trị trung bình của đối số - Đối số có thể là 1 nhóm các giá trị số hoặc 1 mảng- Tính các ô trong đối số có giá trị True là 1- Tính các ô trong đối số có giá trị False là 0- Tính các ô trong đối số có giá trị chuỗi là 0Ví dụ:Tại ô E1 lập công thức = AverageA(A1:D1)  kết quả = 5.2574ABCDE1truefalse20Test5.252trueTrue0true0.7510:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm AVERAGEIF(Range,Criteria, [Average_Range])- Hàm tính trung bình có điều kiện- Range: khối giá trị để kiểm tra điều kiện- Criteria: điều kiện để kiểm tra từng giá trị trong khối điều kiện. - Cách đặt điều kiện tương tự như Sumif- Average_range: khối giá trị để tính trung bình, nếu bỏ qua tham số này Excel tự hiểu là tính trung bình trên khối range(tham số đầu tiên)- Hàm AverageIF:không phân biệt chữ hoa hay thườngHàm trả về lỗi #DIV/0! Nếu không có giá trị thoả mãn đ/kiện hoặc tất cả các giá trị đều không phải kiểu số.7510:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng - Hàm AverageIf trả về lỗi #VALUE! Nếu tham số thứ 2( Criteria) là chuỗi có chiều dài > 255 ký tựVí dụ: Tính điểm trung bình khối A tại ô E5 lập công thức sau: AVERAGEIF(A2:A5,E3,B2:B5)76AB1KhốiĐiểm2A193B234A115A17EF1Vùng ĐK2Khối3A4Điểm T Binh515.6610:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm COUNT(Value1, Value2,)- Hàm chỉ đếm các đối số Value1, Value2 là giá trị số- Các đối số Value trong hàm có thể là bất kỳ giá trị nào hoặc là tham chiếu đến 1 dãy ô (mảng). Có từ 1  255 value Ví Dụ:Tính tổng số thí sinh dự thiTại Ô C5 tạo công thức sau:= Count(B2:B4)  kết quả là 3Tại Ô C4 tạo công thức sau:= Count(A2:A4) Kết quả là 0* Hàm count chỉ đếm các giá trị là số 77ABC1KhốiĐiểm2A193B234A1105Tổng số TS310:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm COUNTA(Value1, Value2,)- Hàm đếm tất cả các đối số Value1, Value2 Không rỗng Các đối số Value trong hàm có thể là bất kỳ giá trị nào hoặc là tham chiếu đến 1 dãy ô (mảng). Có từ 1  255 value Ví Dụ:Tính tổng các ô có dữ liệu tại bảng bênTại Ô C5 tạo công thức sau:= Counta(A1:C4)  kết quả là 12* Tất các đối số đều được hàm Counta đếm kể cả các giá trị logical78ABC1KhốiĐiểm2A19False3B23True4A1105Ts các ô 1210:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm COUNTBLANK(Value1, Value2,)- Hàm đếm tất cả các đối số Value1, Value2 là các ô rỗng- Các đối số Value trong hàm có thể là bất kỳ giá trị nào hoặc là tham chiếu đến 1 dãy ô (mảng). Có từ 1  255 value Ví Dụ:Tính tổng các ô có dự liệu tại bảng bênTại Ô C5 tạo công thức sau:= Counta(A1:C4)  kết quả là 379ABC1KhốiĐiểm2AFalse3BTrue4A05Ts ô rỗng 310:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm CountIF(Range, Criteria). Đếm các ô thoả điều kiệnRange: vùng mà điều kiện được so sánhCriteria: là chuỗi mô tả điều kiện Thí dụ: Tính ra số thí sinh dự thi là khối ATại Ô E4 lập công thức sau: Countif(A2:A5, E2) 38010:08 AMMicrosoft Excel 2010EF1Vùng ĐK2A3TS thí sinh khối A43AB1KhốiĐiểm2A193B234A115A17III.2 Các Hàm Thông Dụng - Cp: CountIFS(Range_Criteria1, Criteria1, Range_Criteria2, Criteria2,)- Hàm CountIFS : Đếm tổng các ô có từ 2 điều kiện trở lên Range_Criteria1,2: vùng mà các điều kiện được so sánh- Criteria1,2: là các điều kiện tương ứng với các Range_ Criteria1,2 - Các vùng Range_ Criteria1, 2n phải có cùng kích thước(như cùng số hàng và cột)Ví dụ: Trong bảng tính sau tính ra có bao nhiêu mặt hàng là nhớt của hãng sản xuất British Petro và có giá > 85000 8110:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng Hàm RANK( Number, Ref,[Order])  hàm xếp hạng- Hàm trả về thứ hạng của Number trong vùng Ref, với Order là cách xếp hạng+ Number: Là số để so sánh xếp hạng+ REF: Bảng để so sánh xem Number thuộc hạng nào+ Kết quả: Xếp hạng phụ thuộc vào đối số thứ 3 [Order]Nếu Order bằng 0 hoặc bỏ qua thì kết quả xếp hạng có thứ tự Lớn là hạng nhất ( xếp hạng điểm số học tập)Nếu Order = 1 hoặc 0 thì kết quả xếp hạng có thứ tự Nhỏ nhất sẽ là hạng nhất( xếp loại theo thể thao)8210:08 AMMicrosoft Excel 2010III.2 Các Hàm Thông Dụng III.2.7 Nhóm hàm thông tin (Information)Hàm ISNA(value): Hàm kiểm tra xem giá trị Value có phải là #N/A hay không, đúng trả về TRUE, sai trả về FALSEHàm ISERROR(value): Hàm kiểm tra xem value có phải là 1 trong các lỗi (#N/A, #Value!, #Ref!, #DIV/0!, #NUM!, #Name?, #Null) hay không. Đúng trả về TRUE, sai trả FALSEHàm ISBLANK(value): Hàm kiểm tra xem ô Value có phải là ô trắng hay không. Đúng trả về TRUE, sai trả về FALSEISNUMBER(Value): Hàm kiểm tra các giá trị có phải là số hay không. Đúng trả về TRUE, sai trả về FALSEISTEXT(Value): Hàm kiểm tra các giá trị có phải là chuỗi hay không. Đúng trả về TRUE, sai trả về FALSE8310:08 AMMicrosoft Excel 2010III.3 Công thức mảng:- Công thức mảng hỗ trợ thống kê và tính toán dựa trên nhiều điều kiện khác nhau và được thực hiện trên mảng dữ liệu- Cú pháp: = {Hàm Mục tiêu(điều kiện 1)*(điều kiện 2)**(cột tính toán)}+ Hàm mục tiêu: tuỳ theo yêu cầu (Sum, Count, Max, Min, Average,)+Điều kiện 1, 2,là các biểu thức điều kiện, thực hiện các biểu thức so sánh kết quả trả về là True(1), False(0)+ Cột tính toán : cột dữ liệu chính cho hàm mục tiêu thực hiện tính toánCông thức mảng kết thúc bằng tổ hợp Phín Ctrl +Shift+EnterCác mảng tính toán phải bằng nhau(cùng số cột số dòng bắt đầu và kết thúc)Các mảng không được có cột tiêu đề8410:08 AMMicrosoft Excel 2010III.3 Công thức mảng:Ví dụ: Tính tổng số điện thoại mà NV Thọ đã bán. Lập công thức sau:= {SUM(IF(A2:A7=“Điện thoại”,1,0)*if(B2:B7=“Thọ”,1,0)*C2:C7)}kết quả là: 19 85ABC 1Sản phẩm Người bánSố lượng2Máy lạnh Thọ23Máy lạnh Sơn54Điện thoại Minh75Điện thoạiThọ96Điện thoạiThọ107Ti viThọ7ABC SP N BánSố lượng0* 100* 001* 001* 191* 1100* 1010:08 AMMicrosoft Excel 2010IV. Chèn các đối tượng vào bảng tínhIV.1 Chèn các đối tượng đồ hoạ- Vào Tab Insert Group Illustrationchọn Picture,Clip Art, Shapes. Cách chèn tương tự như trong Microsoft WordIV.2 Chèn các đối tượng dạng chuỗi:- Vào Tab Insert  Group Text  chọn Text Box, Header & Footer, WordArt, Object. Cách chèn tương tự như trong Microsoft WordIV.3 Chèn ký tự đặc biệt và công thức toán học:- Chọn Tab Insert  Group Text chọn Symbol, EquationCách chèn tương tự như trong Microsoft Word8610:08 AMMicrosoft Excel 2010IV.4 Chèn các đối tượng là biểu đồ- Biểu đồ là sự biểu diễn các con số, dữ liệu bằng hình ảnh để người đọc nắm bắt thông tin một cách trực quan hơn.- Đồ thị có thể được dùng để biểu diễn sự biến động của chuỗi số liệu trong bảng tính cho phép khái quát và dự đoán trong tương lai. Các bước thực hiện:+ Quét chọn vùng dữ liệu (bao gồm cả dòng & cột tiêu đề): A1:B4+ Chọn Tab InsertChọn Charts chọn loại đồ thị tương ứng ngay lập tức ta có đồ thị. Và trên thanh Ribbon xuất hiện 3 Tab ngữ cảnh là : Design, Layout, Format để giúp chúng ta hiệu chỉnh đồ thị.10:08 AMMicrosoft Excel 201087AB1Bảng thống kê2Tên HàngLợi Nhuận3Đường 1,178,2084Kẹo1,436,1645Sữa1,200,000IV.4 Chèn các đối tượng là biểu đồ - Để hiệu chỉnh đồ thị ta nhấp chuột chọn đồ thị sai đó chọn từng thành phần trên đó để hiệu chỉnh( có thể dùng nhóm phím 4 mũi tên trên bàn phín để chọn)881. Chart Title7. Horizontal Axis2. Chart Area8. Data Table3. Plot Area9. Horizontal4. Data Label10. Verical Gridlines5. Legend11.Verical Axis6.Horizontal Gridlines12.Verical Axis Title10:08 AMMicrosoft Excel 2010IV.4 Chèn các đối tượng là biểu đồ 8910:08 AMMicrosoft Excel 2010IV.4 Chèn các đối tượng là biểu đồ Các lệnh tuỳ biến để hiệu chỉnh bản đồ gồm 3 TabDesign:Nhóm TypeChange Chart Type: thay đổi kiểu biểu đồ.Nhóm DataSelect Data: lựa chọn, thay đổi dữ liệu của biểu đồ.Switch Row/Column: chuyển đổi giữa cột và dòngNhóm Chart Layouts: lựa chọn các dạng bố cụ mẫu cho biểu đồ.Nhóm Chart Styles: lựa chọn các dạng định dạng mẫu cho biểu đồ.Nhóm Location: di chuyển biểu đồ sang Chart Sheet mới.9010:08 AMMicrosoft Excel 2010IV.4 Chèn các đối tượng là biểu đồ Layout:- Group Current Selection:Lựa chọn các thành phần trên biểu đồ  định dạng riêng từng phần.- Group InsertPicture: chèn hình vào biểu đồShapes: chèn các khối đơn giản vào biểu đồText Box: chèn textbox vào biểu đồ9110:08 AMMicrosoft Excel 2010IV.4 Chèn các đối tượng là biểu đồ Group LablesChart Title: thêm, xóa, di chuyển tiêu đề của biểu đồAxis Title: thêm, xóa, di chuyển tiêu đề của trục hoành (hoặc trục tung)Legend: thêm, xóa, di chuyển chú thích cho biểu đồData Lables: thêm, xóa, di chuyển nhãn dữ liệuData Table: thêm, xóa bảng dữ liệu của biểu đồNhóm AxesAxes: thay đổi định dạng và bố cục của trục tung, trục hoành.Gridlines: ẩn/ hiện đường lưới cho biểu đồ.9210:08 AMMicrosoft Excel 2010IV.4 Chèn các đối tượng là biểu đồ Tab Format:Shape StylesShape Fill: chọn màu nềnShape Outline: chọn màu đường viềnShape Effects: chọn hiệu ứngWordArt StylesText Fill: chọn màu chữText Outline: chọn màu viền quanh chữText Effects: chọn hiệu ứng cho chữ9310:08 AMMicrosoft Excel 2010IV.4 Chèn các đối tượng là biểu đồ Một số thao tác đối với biểu đồ:- Thay đổi dữ liệu, chú thích, giá trị trục hoànhNhấp chuột phải vào biểu đồ chọn Select Data  xuất hiện hộp thoại Select Data Source.Xóa bớt một chuỗi số liệu khỏi biểu đồ Chọn chuỗi số liệu trên đồ thị  nhấn Delete để xóa.9410:08 AMMicrosoft Excel 2010IV.4 Chèn các đối tượng là biểu đồ Thêm chuỗi dữ liệu mới vào biểu đồ- B1: Chọn đồ thị cần thêm chuỗi mới vào. - B2: Chart ToolsDesignDataSelect Data. Hộp thoại Select Data Source xuất hiện. B3: Nhấp nút Add, hộp thoại Edit Series xuất hiện B4: Đặt tên cho chuỗi mới tại Series Name (bằng tham chiếu hoặc nhập trực tiếp tên vào từ bàn phím) và chọn vùng chứa dữ liệu tại Series Values - B5: Ngoài ra ta có thể thêm nhanh chuỗi mới vào đồ thị bằng cách chép (Ctrl+C) dữ liệu của nó vào bộ nhớ, sau đó chọn đồ thị và dán (Ctrl+V) vào đồ thị.9510:08 AMMicrosoft Excel 2010IV.4 Chèn các đối tượng là biểu đồ Thêm chuỗi dữ liệu mới vào biểu đồ- B1: Chọn đồ thị cần thêm chuỗi mới vào. - B2: Chart ToolsDesignDataSelect Data. Hộp thoại Select Data Source xuất hiện. B3: Nhấp nút Add, hộp thoại Edit Series xuất hiện B4: Đặt tên cho chuỗi mới tại Series Name (bằng tham chiếu hoặc nhập trực tiếp tên vào từ bàn phím) và chọn vùng chứa dữ liệu tại Series Values - B5: Ngoài ra ta có thể thêm nhanh chuỗi mới vào đồ thị bằng cách chép (Ctrl+C) dữ liệu của nó vào bộ nhớ, sau đó chọn đồ thị và dán (Ctrl+V) vào đồ thị.9610:08 AMMicrosoft Excel 2010IV.4 Chèn các đối tượng là biểu đồ Thêm đường xu hướng vào biểu đồ- Khi vẽ các đồ thị với dữ liệu theo thời gian chúng ta thường vẽ thêm đường xu hướng để biết được xu hướng trong tương lai của tập dữ liệu. Để thêm đường xu hướng bạn vào:- Chart Tools Layout Analysis Trendline: chọn kiểu đường xu hướng từ danh sách hay vào More Trendline Options9710:08 AMMicrosoft Excel 2010V. Xử lý số liệuV.1 Sắp xếp dự liệu (Soft)- B1: Chọn phạm vi cần sắp xếp. VD: quét khối từ A1:I129810:08 AMMicrosoft Excel 2010V.1 Sắp xếp dự liệu (Soft)- B2: Vào tab Home  nhóm Editing  lệnh Sort & Filter  Hoặc tab Data  nhóm Sort & Filter  lệnh Sort- Sort Smallest to Largest: sắp xếp từ bé đến lớn theo cột đầu tiên.- Sort Largest to Smallest: sắp xếp từ lớn đến bé theo cột đầu tiên. Custom Sort: tùy chọn sắp xếp theo các cột.9910:08 AMMicrosoft Excel 2010V.1 Sắp xếp dự liệu (Soft)Khi chọn Custom Sort (hoặc lệnh Sort) sẽ xuất hiện màn hình sau:10010:08 AMMicrosoft Excel 2010V.1 Sắp xếp dự liệu (Soft)- Sort by: Chọn cột (Field) làm tiêu chí chính để sắp xếp.- Then by: Chọn cột làm tiêu chí sắp xếp phụ nếu có (Excel sẽ sắp xếp dựa vào tiêu chí phụ này khi tiêu chí chính trong mục Sort by bị trùng).- My data has headers: tích vào nếu vùng dữ liệu quét chọn để sort có dòng tiêu đề.10110:08 AMMicrosoft Excel 2010V.2 Trích lọc dữ liệu: - Chức năng này giúp trích ra những bản ghi vùng dữ liệu thoả mãn những yêu cầu đặt ra. Có 2 cách lọc: Trích lọc tự động và Trích lọc nâng cao.1/ Trích lọc tự động( Auto Filter)- B1: Chọn vùng dữ liệu muốn lọc.- B2: Vào tab Home  nhóm Editing  lệnh Sort & Filter  FilterHoặc tab Data  nhóm Sort & Filter  lệnh Filter- Lúc đó trên tiêu đề mỗi cột sẽ xuất hiện biểu tượng lọc là các Menu DropDown.10210:08 AMMicrosoft Excel 2010V.2 Trích lọc dữ liệu: 1/ Trích lọc tự động( Auto Filter)10310:08 AMMicrosoft Excel 2010V.2 Trích lọc dữ liệu: - B3: Muốn lọc theo điều kiện ở cột nào thì nhấp chuột trái vào biểu tượng mũi tên trên cột đó  tích chọn các giá trị muốn lấy (nhấp chuột lần nữa để bỏ chọn). 10410:08 AMMicrosoft Excel 2010V.2 Trích lọc dữ liệu: - Ngoài ra ta có thể chọn lệnh Number Filters để thiết lập điều kiện lọc tùy ý  màn hình sau sẽ xuất hiện10510:08 AMMicrosoft Excel 2010V.2 Trích lọc dữ liệu: Chọn phép toán so sánh cần dùng trong hộp danh sách bên trái:Nhập vào hoặc chọn giá trị làm điều kiện trong hộp danh sách bên phải. Lưu ý có thể sử dụng 2 ký tự đại diện để lập điều kiện:Dùng dấu ? để thay thế cho 1 ký tự bất kỳ.Dùng dấu * để thay thế cho dãy ký tự bất kỳ có chiều dài bất kỳ.Có thể kết hợp thêm một điều kiện lọc nữa bằng cách chọn tương tự trong hai hộp danh sách phía dưới nhưng phải thông qua hai phép toán And hoặc Or.Để huỷ lệnh lọc ta chọn lại lệnh Data - Filter - AutoFilter một lần nữa.10610:08 AMMicrosoft Excel 2010V.2 Trích lọc dữ liệu: Trích lọc nâng cao_Advanced FilterDùng để lọc dữ liệu theo nhiều điều kiện hoặc trích dữ liệu đến nơi khác. Với cách lọc này, ta phải lập một bảng điều kiện riêng trước khi gọi lệnh Advanced Filter. Ưu điểm là người dùng có thể tự thiết lập được vùng điều kiện trích lọc theo nhiều điều kiện phức tạp. Các bước thực hiện:Bước 1: Tạo bảng điều kiện (Criteria)Bảng điều kiện có thể đặt ở bất cứ vị trí nào và bao gồm:Hàng đầu tiên: tên các trường làm điều kiện trích lọc (Nên copy từ danh sách dữ liệu ra để tránh sai sót).Các hàng tiếp theo: là các giá trị cho điều kiện tương ứng.10710:08 AMMicrosoft Excel 2010V.2 Trích lọc dữ liệu: Các trường hợp điều kiện:Chỉ có một điều kiện chính xác: Vd: Trích lọc ra các sinh viên có giới tính là “Nữ”.10810:08 AMMicrosoft Excel 2010V.2 Trích lọc dữ liệu: Các trường hợp điều kiện (tt)Chỉ có một điều kiện gần đúngNếu dữ liệu điều kiện là số: Sử dụng các toán tử điều kiệnVd: Trích lọc ra các sinh viên viên có điểm Trung Bình lớn hơn hoặc bằng 7.10910:08 AMMicrosoft Excel 2010V.2 Trích lọc dữ liệu: Các trường hợp điều kiện(tt)Nếu dữ liệu điều kiện là chuỗi: Sử dụng các ký tự đại diện (? , *) Vd: Trích lọc ra các sinh viên có họ “Nguyễn”.11010:08 AMMicrosoft Excel 2010V.2 Trích lọc dữ liệu: Các trường hợp điều kiện(tt)- Phải thỏa mãn hai hay nhiều điều kiện cùng lúc.Vd: Trích lọc ra các sinh viên “Nam” có điểm Trung Bình lớn hơn 7.Lưu ý: Các điều kiện phải cùng thỏa mãn ta để các giá trị trên cùng dòng.11110:08 AMMicrosoft Excel 2010V.2 Trích lọc dữ liệu: Các trường hợp điều kiện(tt)- Chỉ cần thỏa mãn một trong các điều kiện.Vd: Trích lọc rac các sinh viên có điểm Toán >5 hoặc diểm Tin >7.Lưu ý: Nếu chỉ cần thỏa mãn 1 trong các điều kiện ta để các giá trị trên các dòng khác nhau.11210:08 AMMicrosoft Excel 2010V.2 Trích lọc dữ liệu: Bước 2: Chọn tab Data  nhóm Sort & Filter  lệnh Advanced Action: Filter the list, in-place: Kết quả lọc xuất hiện ngay trên CSDL gốc. Các dòng không thỏa mãn điều kiện sẽ bị ẩn.Copy to another location: Kết quả lọc sẽ được trích sang một vùng khác.11310:08 AMMicrosoft Excel 2010V.2 Trích lọc dữ liệu: - List range: Chọn vùng địa chỉ của bảng dữ liệu cần lọc. - Criteria range: Địa chỉ bảng điều kiện đã tạo trước đó (cách xác định tương tự List range).- Copy to: Mục này chỉ xuất hiện khi ở mục Action chọn “Copy to another location”. Xác định địa chỉ của một ô bất kỳ ngoài vùng trống dự kiến sẽ chứa kết quả.- Unique records only: Nhấp tích chuột vào nếu không muốn lọc lấy dữ liệu trùng điều kiện (Nếu có nhiều dòng dữ liệu trùng điều kiện thì chỉ lấy 1 dòng).11410:08 AMMicrosoft Excel 2010V.3 Tạo Tổng Cấp Dưới( Subtotals) - Lệnh dùng để nhóm dữ liệu theo từng nhóm đồng thời chèn vào cuối mỗi nhóm những dòng thống kê tính toán (gọi là các tổng con - Subtotals) và một dòng tổng kết ở cuối phạm vi (gọi là tổng lớn - GrandTotal). Các bước thực hiện:Bước 1: Sắp xếp CSDL theo cột làm khoá (muốn nhóm theo cột nào thì cột đó gọi là cột làm khoá)Bước 2: Quét chọn vùng CSDL, vào tab Data nhóm Outline lệnh Subtotat, xuất hiện hộp thoại sau:11510:08 AMMicrosoft Excel 2010V.3 Tạo Tổng Cấp Dưới( Subtotals) At each change in: Chọn trường làm khoá để sắp xếpUse Function: Chọn hàm sử dụng để thống kêAdd SubTotal to: Đánh dấu vào những cột cần thống kê giá trịReplace current Subtotals: Thay các hàng Subtotal tạo trước Page Break Between Groups: Tự động động tạo dấu ngắt trang giữa các nhóm dữ liệu.Sumary Below data: Tạo các dòng thống kê phía dưới các nhóm dữ liệu.11610:08 AMMicrosoft Excel 2010V.3 Tạo Tổng Cấp Dưới( Subtotals) Và cuối cùng chúng ta có kết quả như sau:11710:08 AMMicrosoft Excel 2010V.4 Tạo dữ liệu tổng hợp từ các dữ liệu chi tiết (Consolidate) Được sử dụng để tạo dữ liệu tổng hợp từ những dữ liệu chi tiết (được chọn lựa trên cùng một hoặc nhiều tập tin bảng tính khác nhau).Nếu dữ liệu chi tiết có cùng cấu trúc (có cùng số lượng trường, tên trường và kiểu dữ liệu từng trường hoàn toàn như nhau) thì dữ liệu tổng hợp sẽ có cấu trúc tương tự như các dữ liệu chi tiết và mỗi bản ghi của dữ liệu tổng hợp sẽ là dữ liệu tổng hợp từ các bản ghi trong các dữ liệu chi tiết.Nếu các dữ liệu chi tiết không có cùng cấu trúc thì nhất thiết phải có chung ít nhất trường ngoài cùng bên trái để làm khoá. Lúc đó dữ liệu tổng hợp sẽ có dạng gộp các dữ liệu chi tiết theo qui tắc:Các trường trùng tên sẽ được tổng hợp theo hàm người dùng chọn.Các trường không trùng tên sẽ được ghép nối.11810:08 AMMicrosoft Excel 2010V.4 Tạo dữ liệu tổng hợp từ các dữ liệu chi tiết (Consolidate) Các bước thực hiệnBước 1: Chuẩn bị các bảng dữ liệu chi tiếtBước 2: Đặt con trỏ chuột tại vị trí sẽ chứa kết quả tổng hợp. Vào tab Data  nhóm Data Tools  lệnh Consolidate  xuất hiện hộp thoại sau:11910:08 AMMicrosoft Excel 2010V.4 Tạo dữ liệu tổng hợp từ các dữ liệu chi tiết (Consolidate) Function: Chọn hàm cần dùng để tổng hợp.Reference: Nhập hoặc dùng chuột để quét chọn và ấn nút Add lần lượt địa chỉ các bảng dữ liệu chi tiết cần tổng hợp.Top Row: Tạo dòng tiêu đề cho bảng tổng hợp.Left Column: Tạo tiêu đề cột đầu tiên cho bảng tổng hợp.Create link to source data: Tạo mối liên kết từ bảng tổng hợp đến các bảng chi tiết nhằm mục đích nếu có sự thay đổi trong các bảng dữ liệu chi tiết thì các dữ liệu liên quan trong bảng tổng hợp cũng tự thay đổi theo.12010:08 AMMicrosoft Excel 2010V.5 Kiểm tra nhập liệu (Data validation)Khi xây dựng bảng tính trong Excel, một số trường hợp yêu cầu nhập liệu là những giá trị chỉ có thể là số nguyên giới hạn, số thập phân, ngày, giờ, trong danh sách có sẵn, hoặc chuỗi có độ dài nhất định,Làm sao để Excel biết được dữ liệu do người dùng nhập vào đó có đúng yêu cầu? Làm sao để Excel có thể thông báo lỗi và cảnh báo co người dùng biết họ cần phải nhập giá trị gì tại ô xác định.Tất cả các yêu cầu đó sẽ được thực hiện thông chức năng Data Validation của Excel.12110:08 AMMicrosoft Excel 2010V.5 Kiểm tra nhập liệu (Data validation)Các bước thực hiện:Bước 1: Quét chọn các ô trên bảng tính muốn Excel kiểm tra việc nhập liệu.Bước 2: Vào tab Data nhóm Data Tools lệnh Data Validation  xuất hiện hộp thoại sau:12210:08 AMMicrosoft Excel 2010V.5 Kiểm tra nhập liệu (Data validation)Tab Settings: cài đặt điều kiện nhập liệu (Validation criteria)Hộp Allow: chọn một trong các định dạng điều kiện. Có các dạng sau:Any value: dạng mặc định, cho phép nhập bất cứ giá trị gì vào ô.Whole number: chỉ cho phép nhập giá trị là số nguyên trong khoảng theo điều kiện so sánh tương ứng trong Data.Decimal: chỉ cho phép nhập giá trị là số nguyên hoặc số thập phân trong khoảng theo điều kiện so sánh tương ứng trong Data.List: chỉ cho phép nhập giá trị có tồn tại trong một danh sách cho trước.12310:08 AMMicrosoft Excel 2010V.5 Kiểm tra nhập liệu (Data validation)Tab Settings: cài đặt điều kiện nhập liệu (Validation criteria)Hộp Allow: chọn một trong các định dạng điều kiện. Có các dạng sau:Date: chỉ cho phép nhập giá trị ở dạng ngày tháng năm trong khoảng theo điều kiện so sánh tương ứng trong Data.Time: chỉ cho phép nhập giá trị ở dạng giờ phút giây trong khoảng theo điều kiện so sánh tương ứng trong Data.Text length: chỉ cho phép nhập vào chuỗi có chiều dài xác định (tính bằng số ký tự, kể cả khoảng trắng và dấu).Custom: cho phép người dùng tự thiết lập định dạng của dữ liệu được phép nhập. 12410:08 AMMicrosoft Excel 2010V.5 Kiểm tra nhập liệu (Data validation)Thẻ Input Message: Hiển thị thông báo yêu cầu khi nhập liệu12510:08 AMMicrosoft Excel 2010V.5 Kiểm tra nhập liệu (Data validation)Tab Error Alert: Hiển thị thông báo lỗi khi nhập không đúng dữ liệu được yêu cầu.12610:08 AMMicrosoft Excel 2010VI. IN VÀ ĐỊNH DẠNG TRANG INVI.1 Định dạng trang inĐể điều chỉnh các thông số thiết lập cho trang in ta vào tab File  Print  Page Setup :12710:08 AMMicrosoft Excel 2010VI.1 Định dạng trang inThẻ Page: chọn hướng in và khổ inOrientation: Thay đổi kiểu inPortrait: Định dạng kiểu in đứngLandscape: Định dạng kiểu in ngangPaper size: Chọn khổ giấy in12810:08 AMMicrosoft Excel 2010VI.1 Định dạng trang inThẻ Margins: Định dạng lề trangTop: Lề trên Left: Lề tráiBottom: Lề dưới Right: Lề phảiHeader: Tiêu đề trênFooter: Tiêu đề dướiCenter on page: Định nội dung in nằm giữa trang theo chiều ngang (Horizontally) hay theo chiều dọc (Vertically)12910:08 AMMicrosoft Excel 2010VI.1 Định dạng trang in13010:08 AMMicrosoft Excel 2010VI.1 Định dạng trang inThẻ Header/Footer: tạo tiêu đề đầu (Header) và chân (Footer) trang in.Custom Header: tạo tiêu đề đầu trang inCustom Footer: tạo tiêu đề chân trang in. 13110:08 AMMicrosoft Excel 2010VI.1 Định dạng trang inThẻ Sheet: chọn vùng inPrint area: xác định vùng inRows to repeat at top: chọn các dòng cần in lặp lại ở đầu mỗi trang.Columns to repeat at left: chọn các cột cần in lặp lại bên trái mỗi trang.Gridlines: In nội dung bảng tính có đường lưới.Row and column header: In cả tiêu đề cột và số thứ tự dòng.Black and white: Chỉ in trắng đen. Draft quality: Chế độ in lợt13210:08 AMMicrosoft Excel 2010VI.1 Định dạng trang inThẻ Sheet: chọn vùng inComments: In chú thích (None: Không in; At end of sheet: In chú thích ở cuối trang; As displayed on sheet: In như đang hiển thị trên sheet)Cell errors as: Chỉ định in các ô bị lỗi (Displayed: In như hiển thị lỗi; Blank: Để trống; -- Thay các ô lỗi bằng dấu gạch; #N/A: Các ô bị lỗi thì in chữ #N/A)Page order: Chỉ định thứ tự in các trangDown, then over: Từ trên xuống và từ trái qua phảiOver, then down: Từ trái qua phải và từ trên xuống13310:08 AMMicrosoft Excel 2010VI.1 Định dạng trang inThẻ Sheet: chọn vùng inComments: In chú thích (None: Không in; At end of sheet: In chú thích ở cuối trang; As displayed on sheet: In như đang hiển thị trên sheet)Cell errors as: Chỉ định in các ô bị lỗi (Displayed: In như hiển thị lỗi; Blank: Để trống; -- Thay các ô lỗi bằng dấu gạch; #N/A: Các ô bị lỗi thì in chữ #N/A)Page order: Chỉ định thứ tự in các trangDown, then over: Từ trên xuống và từ trái qua phảiOver, then down: Từ trái qua phải và từ trên xuống13410:08 AMMicrosoft Excel 2010VI.2 Các chế độ hiển thị trang in trong ExcelMS Excel 2010 hỗ trợ 5 kiểu xem Workbook:Normal: Đây là chế độ hiển thị thường xuyên trong quá trình nhập liệu, tính toán, và là chế độ mặc định của Excel.Page Layout: xem theo dạng phân trang in.Page Break Preview: Hiển thị bảng tính với các dấu phân trang, tại đây bạn có thể chia lại trang bằng cách kéo thả các đường chia cách trang.Custom Views: cài đặt chế độ xem theo ý người dùng.Full Screen: xem toàn màn hình. Nhấn phím esc để trở về bình thường.13510:08 AMMicrosoft Excel 2010VI.3 In bản tínhChọn tab File  Print lệnh Print (như hình bên dưới)13610:08 AMMicrosoft Excel 2010VI.3 In bản tínhName: Chọn tên máy inPrint range: chọn các trang cần in.All để in hết tất cả các trang.Page(s) để in từ trang (From)...đến trang(To)...Number of copies: Số bản in của mỗi trang13710:08 AMMicrosoft Excel 2010VI.3 In bản tínhPrint what: Selection: In các ô và đối tượng đang chọn.Active Sheet(s): In sheet hiện hành.Entire workbook: In tất cả các sheet.List: Chỉ in danh sách được chọn trên sheet.13810:08 AMMicrosoft Excel 2010VII. CÁC CHỨC NĂNG KHÁCVII.1 Thiết lập Freeze PanesThiết lập vùng cố định trên bảng tính khi lăn các thanh cuộn (Scroll Bar).Bước 1: chọn vị trí góc đánh dấu sẽ cố định (bắt đầu từ dòng phía trên và cột bên trái của ô được chọn sẽ được cố định).Bước 2: chọn tab View nhóm Window lệnh Freeze Panes 13910:08 AMMicrosoft Excel 2010VII.1 Thiết lập Freeze Panes14010:08 AMMicrosoft Excel 2010VII.1 Thiết lập Freeze Panes- Freeze Panes: thiết lập cố định tại vị trí đã chọn trong bước 1.- Freeze Top Row: cố định dòng đầu. Freeze First Column: cố định cột đầu.- Lưu ý: Mỗi bảng tính (sheet) chỉ có 1 vị trí Freeze Panes. Muốn bỏ hay thay đổi sang vị trí khác ta chọn tab View nhóm Window lệnh Unfreeze Panes.14110:08 AMMicrosoft Excel 2010VII.2 Kiểm tra đánh giá công thức- Trong quá trình lập công thức tính toán với bảng tính đặc biệt là các công thức phức tạp có nhiều phép toán cũng như sử dụng nhiều hàm. Do đó nếu cần kiểm tra việc thực hiện tính toán của Excel trong những công thức như vậy có đúng với ý đồ người dùng hay không một cách thủ công sẽ rất phức tạp. Nhưng với chức năng kiểm tra đánh giá công thức của Excel (Evaluate Formula) việc đó sẽ đơn giản hơn rất nhiều.Thao tác thực hiện:Chọn công thức cần kiểm tra, đánh giá.Chọn tab Formulas nhóm Formula Auditing lệnh Evaluate Formula:14210:08 AMMicrosoft Excel 2010VII.2 Kiểm tra đánh giá công thứcThao tác thực hiện:Chọn công thức cần kiểm tra, đánh giá.Chọn tab Formulas nhóm Formula Auditing  lệnh Evaluate Formula:14310:08 AMMicrosoft Excel 2010VII.3 Sao chép đặc biệtTrong quá trình sao chép, đôi khi chúng ta chỉ cần sao chép một số chọn lọc nào đó mà không phải là toàn bộ nội dung gốc. Khi đó thay vì dùng lệnh Paste ta sẽ sử dụng lệnh Paste Special...Các bước thực hiện:Bước 1: chọn vùng cần sao chép, ra lệnh CopyBước 2: chọn vị trí cần sao chép đến, chọn tab Home nhóm Clipboard  lệnh Paste  Paste Special14410:08 AMMicrosoft Excel 2010VII.3 Sao chép đặc biệt14510:08 AMMicrosoft Excel 2010VII.3 Sao chép đặc biệt146Tùy chọn dánÝ nghĩaAllDán cả giá trị và định dạng của vùng nguồnFormulasDán giá trị và công thức, không định dạngValuesChỉ dán giá trị và kết quả của công thức, không định dạngFormatsChỉ dán vào định dạng, bỏ qua tất cả giá trị và công thứcCommentsChỉ dán vào chú thích, bỏ qua tất cả giá trị và công thứcValidationChỉ dán vào các qui định xác thực dữ liệu cho vùng đíchAll except bordersDán vào mọi thứ chỉ loại bỏ khung viềnColumns widthsChỉ dán vào thông tin quy định độ rộng cộtFormulas and number formatsDán vào giá trị, công thức và các định dạng gốc của các con số, các định dạng khác bị loại bỏValues and number formatsDán vào giá trị, kết quả của công thức và các định dạng gốc của các con số10:08 AMMicrosoft Excel 2010VII.3 Sao chép đặc biệt147 Tính toánÝ nghĩaNoneKhông kèm theo việc tính toán nào trên dữ liệu sắp dán vàoAddCông các giá trị của vùng nguồn vào các ô tương ứng của vùng đíchSubtractCác ô mang giá trị của vùng đích sẽ trừ đi các ô tương ứng của vùng nguồnMultiplyCác ô mang giá trị của vùng đích sẽ nhân với các ô tương ứng của vùng nguồnDivideCác ô mang giá trị của vùng đích sẽ chia cho các ô tương ứng của vùng nguồnSkip blanksKhông dán đè ô rỗng ở vùng nguồn vào ô có giá trị ở vùng đíchTransposeDán vào và đảo dòng thành cột hoặc ngược lạiPaste LinkDán vào và tham chiếu ô đích đến ô nguồn10:08 AMMicrosoft Excel 2010VII.3 Định dạng theo điều kiện (Conditional Formatting)Conditional Formatting là công cụ cho phép bạn áp dụng định dạng cho một ô hay nhiều ô trong bảng tính và sẽ thay đổi định dạng tùy theo giá trị của ô hay giá trị của công thức. Ví dụ như bạn có thể tạo cho định dạng của ô đó là chữ in đậm màu xanh khi giá trị của nó lớn hơn 100. Khi giá trị của ô thoả điều kiện thì các định dạng bạn tạo ra ứng với điều kiện đó sẽ được áp dụng cho ô đó. Nếu giá trị của ô không thoả điều kiện bạn tạo ra thì định dạng của ô đó sẽ áp dụng định dạng mặc định (default formatting).Định dạng theo điều kiện khi áp dụng vào các ô (cell) nó sẽ đè lên các định dạng thông thường của ô về màu sắc, kiểu thể hiện văn bản và số Tuy nhiên nếu chúng ta xoá bỏ định dạng theo điều kiện của các ô thì định dạng đã có trước kia của các ô này sẽ được phục hồi.14810:08 AMMicrosoft Excel 2010VII.3 Bảo vệ bảng tínhConditional Formatting là công cụ cho phép bạn áp dụng định dạng choTrong quá trình làm việc với bảng tính, đôi khi chúng ta cần phải bảo vệ an toàn cho các công thức, dữ liệu, định dạng, cấu trúc của bảng tính, mà không muốn người dùng khác có thể xem, thay đổi hay xóa được.Chức năng bảo vệ bảng tính (Protect Sheet) của Excel sẽ giúp thực hiện các đòi hỏi đó một cách linh hoạt.Các bước thực hiện:Bước 1: Chọn toàn bộ bảng tính.Bước 1: Đánh dấu các ô dữ liệu và ô công thức trên bảng tính cần được bảo vệ hay che dấu công thức (nếu là ô công thức). (Xem mục 2.7 – Thẻ Protection).Bước 2: Vào Tab File Info Protect Workbook Protect Current Sheet 14910:08 AMMicrosoft Excel 2010VII.3 Bảo vệ bảng tính15010:08 AMMicrosoft Excel 2010VII.3 Bảo vệ bảng tínhBước 3: Nhập vào Password bảo vệ (nếu cần).Bước 4: Chọn các tác vụ cho phép người dùng được thực hiện đối với bảng tínhBước 5: Nhấn OK để hoàn tất, ngay lập tức Excel sẽ thực thi chức năng bảo vệ bảng tính.15110:08 AMMicrosoft Excel 2010