Bài giảng Excel trong quản lý dữ liệu

*EXCEL TRONG QUẢN LÝ DỮ LiỆU*Yêu cầu kiến thức Hiểu biết cơ bản về phần mềm Microsoft Excel.Nắm được các hàm cơ bản trong Excel.Giải quyết được các bài liên quan.*Nội dung bàiÔn lại các kiểu dữ liệu, các phép toán trong Excel.Hiểu và sử dụng các hàm thông dụng trong Excel.Bảo mật và ràng buộc nhập liệu trong Excel.Chức năng phân tích dữ liệu Pivot Tables.*Kiểu dữ liệu trong ExcelNumberKiểu dữ liệu dạng sốTextKiểu dữ liệu dạng chữDateKiểu dữ liệu dạng ngàyTimeKiểu dữ liệu dạng giờ.*MS Excel đưa ra 4 loại phép toán chính:Phép toán số học: thực hiện các phép toán cơ bản như cộng (), trừ (), nhân (*), chia (), phần trăm (), lũy thừa (^).Phép toán so sánh: dùng để so sánh 2 giá trị và trả về kiểu luận lý (đúng/ sai). Phép toán so sánh gồm: , >=, =, Các phép toán trong Excel (1)*Phép toán chuỗi (): dùng để nối các chuỗi và tạo ra chuỗi mới.Phép toán tham chiếu: dùng chỉ phạm vi các Ô hay khối (block) cần truy xuất.Các phép toán trong Excel (2)*Loaïi Pheùp ToaùnPheùp ToaùnYÙ nghóaVí duïPheùp toaùn soá hoïc +--*/%^Coäng TröøSoá aâmNhaân ChiaPhaàn traêmLuyõ thöøa3+33-1-13*33/320%3^2Pheùp toaùn so saùnh >==Nhoû hônNhoû hôn hoaëc baèngLôùn hôn Lôùn hôn hoaëc baèngBaèng KhaùcB1 B2B1>= B2B1= B2B1 B2Pheùp toaùn chuoãi Noái chuoãi“Tin hoïc” “Quaûn lyù”ùPheùp toaùn tham chieáu:,Vuøng HoäiB5: B15SUM(B5:B15,D5:D15)*Các hàm thông dụng trong Excel*HAØM CHUOÃI HAØMCHÖÙC NAÊNG – VÍ DUÏLEFT(chuoãi, n)traû veà n kyù töï beân traùi cuûa chuoãi=LEFT(“ABCDE”,2)  “AB”RIGHT(chuoãi, n)traû veà n kyù töï beân phaûi cuûa chuoãi=RIGHT(“ABCDE”,2)  “DE”MID(chuoãi, m, n)traû veà n kyù töï keå töø kyù töï thöù m cuûa chuoãi=MID(“ABCDE”,3,2)  “CD”TRIM(chuoãi)traû veà chuoãi ñaõ caét boû nhöõng khoaûng troáng thöøaTRIM(“TT tin hoïc”)  “TT tin hoïc”*HAØM CHUOÃI (1)HAØMCHÖÙC NAÊNG – VÍ DUÏLOWER(chuoãi)traû veà chuoãi chöõ thöôøng töông öùng vôùi chuoãi=LOWER(“Tin HoÏc”)  “tin hoïc”UPPER(chuoãi)traû veà chuoãi chöõ hoa töông öùng vôùi chuoãi=UPPER(“Tin HoÏc”)  “TIN HOÏC”PROPER(chuoãi)traû veà chuoãi chöõ hoa ñaàu moãi töø töông öùng vôùi chuoãi=PROPER(“tin hoïc”)  “Tin Hoïc”LEN(chuoãi)traû veà chieàu daøi cuûa chuoãi=LEN(“ABCDE”)  5VALUE(chuoãi)traû veà soá töông öùng vôùi chuoãi soá=VALUE(“120”)  120*HAØM LUAÄN LYÙ (1)AND(BtLogic1, BtLogic2, ): traû veà True neáu taát caû caùc BtLogic coù giaù trò True, False neáu coù ít nhaát moät BtLogic coù giaù trò False.OR(BtLogic1, BtLogic2, ): traû veà False neáu taát caû caùc BtLogic coù giaù trò False, True neáu coù ít nhaát moät BtLogic coù giaù trò True.NOT(btLogic): traû veà giaù trò phuû ñònh cuûa BtLogic*HAØM LUAÄN LYÙ (2)IF(BtLogic, Giaù trò_1, Giaù trò_2)Neáu BtLogic coù giaù trò ñuùng thì traû veà Giaù trò_1, ngöôïc laïi traû veà Giaù trò_2.*HAØM NGAØY GIÔØ (1)*HAØM NGAØY GIÔØ (2)HAØMCHÖÙC NAÊNG – VÍ DUÏNOW()traû veà ngaøy, giôø hieän haønh maø maùy ñang löu giöõDAY(ngaøy)traû veà soá töông öùng vôùi ngaøy=DAY(“15/3/95”)  15MONTH(ngaøy)traû veà soá chæ thaùng töông öùng vôùi ngaøy=MONTH(“15/3/95”)  3YEAR(ngaøy)traû veà soá chæ naêm töông öùng vôùi ngaøy=YEAR(“15/3/95”)  95*HAØM SOÁ HOÏCHAØMCHÖÙC NAÊNG – VÍ DUÏABS(x)traû veà giaù trò tuyeät ñoái cuûa x=ABS(-2)  2INT(x)traû veà phaàn nguyeân cuûa x =INT(3.154)  3MOD(x,y)traû veà soá dö cuûa pheùp chia nguyeân x cho y=MOD(5,3)  2ROUND(x, n)laøm troøn soá x vôùi n kyù soá laøm troøn=ROUND(3.154,2)  3.15=ROUND(3.154,1)  3.2*HAØM THOÁNG KEÂ (1)HaømChöùc naêngMIN(danhsaùch)traû veà giaù trò nhoû nhaát trong danh saùchMAX(danhsaùch)traû veà giaù trò lôùn nhaát trong danh saùchAVERAGE(danhsaùch)traû veà giaù trò trung bình trong danh saùchCOUNT(danh saùch)traû veà soá caùc OÂ coù kieåu soá trong danh saùchCOUNTA(danhsaùch)traû veà soá löôïng caùc OÂ coù noäi dung khaùc roãng trong danh saùchCOUNTIF(vuøng,ñieàu kieän)traû veà soá caùc OÂ trong vuøng thoûa ñieàu kieän*HAØM THOÁNG KEÂHaømChöùc naêngSUM (x1, x2,, xn)traû veà toång caùc soá x1, x2,, xnSUMIF(vuøng,ñieàu kieän)traû veà toång caùc OÂ trong vuøng thoûa ñieàu kieänSUMIF(vuøng1,ñieàu kieän,vuøng2)traû veà toång caùc OÂ trong vuøng 2 töông öùng vôùi caùc OÂ trong vuøng 1 thoûa ñieàu kieänRANK(oâ,vuøng,kieåu)traû veà thöù töï xeáp haïng cuûa OÂ trong vuøng. Kieåu =1: xeáp haïng taêng daàn, Kieåu =0 hoaëc khoâng khai baùo: xeáp haïng giaûm daàn*HAØM DOØ TÌM (1)Haøm doø tìm vaø tham chieáu baûng 1 chieàuVLOOKUP haøm doø tìm theo COÄT;HLOOKUP haøm doø tìm theo DOØNG;*HAØM DOØ TÌM (2)VLOOKUP (giaù trò doø, baûng doø, coät giaù trò, kieåu doø): doø tìm giaù trò doø treân coät ñaàu tieân cuûa baûng doø. Neáu tìm thaáy thì traû veà giaù trò ôû OÂ töông öùng treân coät chæ ra trong haøm.Neáu khoâng tìm thaáy thì traû veà # N/A (Not Available)Kieåu doø laø: 0 (hoaëc khoâng chæ roõ): doø tìm xaáp xæ, = 0: doø tìm chính xaùc *HAØM DOØ TÌM (3)*HAØM DOØ TÌM (4)HLOOKUP (giaù trò doø, baûng doø, haøng giaù trò, kieåu doø): doø tìm giaù trò doø treân haøng ñaàu tieân cuûa baûng doø. Töông töï nhö haøm VLOOKUP nhöng doø theo haøng ñaàu cuûa baûng doø.Neáu tìm thaáy thì traû veà giaù trò ôû OÂ töông öùng treân haøng chæ ra trong haøm. Neáu khoâng tìm thaáy thì traû veà # N/A.*Bảo mật và ràng buộc nhập liệu*Bảo mật trong Excel (1)Các mức bảo mật trong ExcelBảo vệ bằng mật khẩuBảo vệ bảng tínhBảo vệ các sheetBảo vệ ô dữ liệu*Bảo vệ bằng mật khẩu (1)Giúp bảo vệ bảng tính Excel tránh truy cập trái phépCó 2 mức bảo vệ bằng mật khẩu:Mật khẩu đọc nội dung fileMật khẩu sửa nội dung file*Bảo vệ bằng mật khẩu (2)Các bước thiết lập mật khẩu cho file Excel:*Bảo vệ bằng mật khẩu (3)Chọn Tools ở góc trên bên phải. Trong menu thả xuống, chọn General Options: *Bảo vệ bảng tính (1)Chức năng bảo vệ bảng tính cho phép thiết lập các bảo vệ về cấu trúc (Structure) và kích thước cửa sổ (Window)Structure: không cho thay đổi cấu trúc bảng tính như thêm, xóa, sửa các sheet. Windows: ngăn cấm việc thay đổi kích thước, phóng to, thu nhỏ v.v.*Bảo vệ bảng tính (2)Tools > Protection > Protect Workbook *Bảo vệ sheetGiúp bảo vệ không cho thao tác trên sheetChọn menu Tools > Protection > Protect Sheet *Bảo vệ ô dữ liệuKết hợp với bảo vệ sheet, bảo vệ ô dữ liệu không cho thay đổi.Click chuột phải vào ô dữ liệu tương ứng và chọn Format cells  Protection *Ràng buộc nhập liệu (1)Tên tiếng anh: Data validation Giúp kiểm tra khi người dùng nhập liệu vào bảng tínhBảo đảm được tính chính xác dữ liệuHỗ trợ tốt cho người dùng khi làm việc với dữ liệu lớn và phức tạp như:Bán hàngBảng lươngBảng chấm công.*Ràng buộc nhập liệu (2)Thiết lập ràng buộc nhập liệu:Chọn Menu Data  Validation *Ràng buộc nhập liệu (3)Any value: Là giá trị mặc định của ô, cho phép đưa vào giá trị bất kỳ.Whole number và Decimal: Chỉ cho phép nhập các giá trị số.List: Dữ liệu nhập phải nằm trong một danh sách các dữ liệu cho trước nào đó. *Ràng buộc nhập liệu (4)Date: Dữ liệu nhập phải là kiểu ngày.Time: Dữ liệu nhập phải là kiểu thời gian.Text length: Kiểm tra độ dài của xâu ký tự nhập Khi người dùng nhập sai dữ liệu với những qui tắc đã thiết lập, Excel sẽ báo lỗi và hiện ra thông báo nhắc nhở. *Phân tích dữ liệu Pivot Tables*Khái niệm PivotBảng Pivot - Pivot table - là một bảng làm việc sử dụng để:Tổng kết, thống kê Phân tích số liệu dựa trên một nguồn dữ liệu có sẵn nào đó. Khi sử dụng bảng Pivot, dữ liệu ban đầu vẫn được giữ nguyên không bị thay đổi gì.*Sử dụng bảng Pivot (1)Bảng Pivot có thể được tạo từ các nguồn dữ liệu khác nhau như:Dữ liệu từ chính bảng tính Excel.Dữ liệu từ Cơ sở dữ liệu bên ngoài.Các đoạn dữ liệu được hợp nhất trong Excel.Một bảng Pivot khác trong cùng một trang làm việc.*Sử dụng bảng Pivot (1)Cần cho biết:Xác định thông tin cần báo cáoXác định trường nào theo cột, trường nào theo dòng*Tạo PivotTablesThực hành:Tổng kết doanh thu bán hàng của Siêu thị điện máy Toàn Thắng*Câu hỏi và bài tậpQuản lý điểmQuản lý hàng hoáQuản lý nhân sự