Quản trị kinh doanh - Chương 2: Hàm và cơ sở dữ liệu trong excel

Chương 2.HÀM VÀ CƠ SỞ DỮ LIỆU TRONG EXCELCác toán tử trong ExcelCác hàm trong ExcelCơ sở dữ liệu trong ExcelĐồ thịBảng tổng hợp đa chiềuNỘI DUNG 1. CÁC TOÁN TỬ TRONG EXCELHàm luận lýHàm số họcHàm thống kêHàm điều kiệnHàm chuỗiHàm thời gianHàm dò tìm2. HÀMAND (đối 1, đối 2,, đối n): phép VÀ, là hàm logic, chỉ đúng khi tất cả các đối số có giá trị đúng. Các đối số là các hằng, biểu thức logic. VD: = AND (B3>=23,B3=25,D30) Ví dụ: SQRT(25) kết quả 52.2. HÀM SỐ HỌCHàm lấy phần nguyên INT(N) N là số / biểu thức số Ví dụ: =INT(236.26) kết quả 236Hàm lấy phần dư phép chia nguyên N cho M MOD(N, M) Ví dụ: = MOD(10,3) kết quả 1Hàm lấy giá trị PI : PI() Ví dụ: = PI()*2^2 kết quả 12.566..Hàm mũ: POWER(x , y) Trả về x mũ y Ví dụ: =power(4 , 2) kết quả 162.2. HÀM SỐ HỌCHàm làm tròn lên ROUND(biểu thức số, N)N>0: làm tròn bên phải cột thập phânN=5,“Đỗ”,“Trượt”)- Hàm IF có thể viết lồng nhau. VD: = IF(C6400,3,2)) - Hàm trên cho kết quả của phép thử sau: nếu [dữ liệu trong ô C6]  300 nếu 300 400 2.4. CÁC HÀM ĐIỀU KIỆNSUMIF (miền_đ/k, đ/k, miền_tổng): hàm tính tổng có điều kiện 2.4. CÁC HÀM ĐIỀU KIỆNCOUNTIF(miền_đếm, điều_kiện): đếm số lượng các ô trong miền đếm thoả mãn điều kiện.Ví dụ 1Ví dụ 2 2.4. CÁC HÀM ĐIỀU KIỆNLEFT(“Chuỗi ký tự”, n): Cho n ký tự bên trái của chuỗi.VD: =LEFT(“Gia Lâm – Hà Nội”,7) cho kết quả là chuỗi “Gia Lâm”RIGHT(“Chuỗi ký tự”, n): Cho n ký tự bên phải của chuỗi.VD: =RIGHT(“Gia Lâm – Hà Nội”,6) cho kết quả là chuỗi “Hà Nội”MID(“Chuỗi ký tự”, m, n): Cho n ký tự tính từ ký tự thứ m của chuỗi.VD: =MID(“Gia Lâm–Hà Nội”,9,2) cho kết quả là chuỗi “Hà”2.5. HÀM CHUỖIHàm tính chiều dài của chuỗi: LEN (“chuỗi”) Trả là chiều dài trong chuỗi, tức là số ký tự có trong chuỗi Ví dụ: =LEN (“Excel”) kq: 5Hàm đổi chuỗi thường: LOWER (“chuỗi”) Ví dụ: =LOWER(“KINH TẾ HỌC”) kq: “kinh tế học”2.5. HÀM CHUỖIHàm đổi chuỗi hoa: UPPER (“chuỗi”) Ví dụ: =UPPER(“kinh tế học”) kq: “KINH TẾ HỌC”Hàm đổi các ký tự đầu của “word” thành hoa PROPER (“chuỗi”) Ví dụ: =PROPER(“kinh tế học”) kq: “Kinh Tế Học”2.5. HÀM CHUỖI Hàm xoá khoảng trống thừa trong chuỗi TRIM (“chuỗi”) Ví dụ: =TRIM (“ Xin Chào ”) kq: “Xin chào”Hàm đổi chuỗi số thành trị số VALUE (“chuỗi số”) Ví dụ: =VALUE (“123”) kq: 1232.5. HÀM CHUỖIYEAR (“chuỗi ngày”) Trả về số năm tương ứng (1900 đến 2078) Ví dụ: =YEAR(“24/12/2004”) kq: 2004DAYS360(“ngày bắt đầu”;”ngày kết thúc”) Trả về tổng số ngày từ “ngày bắt đầu” đến “ngày kết thúc” 2.6. HÀM KIỂU NGÀYVLOOKUP (trị_tra_cứu, bảng_tra_cứu, cột_lấy_dữ_liệu, [True/False]): tra cứu g/t với các g/t trong cột đầu tiên của bảng và hiển thị dữ liệu tương ứng trong bảng tra cứu nằm trên cột ở đối số 3. VD: = VLOOKUP(E3, $E$12:$F$16, 2, True)HLOOKUP(g/t, bảng_g/t, hàng_lấy_d.liệu, [1/0]): hàm tra cứu theo hàng, tương tự hàm VLOOKUPISNA(value): Trả về giá trị TRUE nếu gặp lỗi #N/A, ngược lại trả về FALSE. 2.7. HÀM DÒ TÌMMatch(lookup_value, lookup_ref, type)lookup_value: giá trị cần tìm, giá trị này có thể là: chuỗi, số của một ô chứa dữ liệu là số hay chuỗilookup_ref: phạm vi vùng dữ liệu (chỉ một dòng hay cột)type: các kiểu so sánh1: Tìm giá trị lớn nhất nhỏ hơn hay bằng lookup_valueLookup_ref: phải được sắp theo thứ tự tăng dầnNếu tất cả trong lookup_ref đều nhỏ hơn lookup_value thì hàm trả về #NA-1:Tìm giá trị nhỏ nhất lớn hơn hay bằng lookup_valueLookup_ref: phải được sắp theo thứ tự giảm dầnNếu tất cả trong lookup_ref đều nhỏ hơn lookup_value thì hàm trả về #NA0: dò tìm chính xác đúng, nếu không trả về #N/A 2.7. HÀM DÒ TÌM 2.7. HÀM DÒ TÌMINDEX(array,row_num,column_num): Trả về giá trị của ô tại dòng và cột trong một dãy ôPear 2.7. HÀM DÒ TÌMKhái niệmSắp xếpTìm kiếmSubtotal3. CƠ SỞ DỮ LIỆU CỦA EXCELCSDL gồm các trường (field) và bản ghi (record).Trường là một cột CSDL, mỗi trường biểu thị một thuộc tính của đối tượng và có kiểu dữ liệu nhất định.Bản ghi là một hàng dữ liệu.Dòng đầu của miền CSDL chứa các tên trường, các dòng tiếp sau là các bản ghi3.1. KHÁI NIỆMKhi xếp thứ tự 1 danh sách (CSDL), phải chọn tất cả các cột để tránh sự mất chính xác dữ liệu.DS không có tên trường thì tên cột sẽ thay thế.Trường quy định cách xếp thứ tự gọi là khoá. Cách làm: B1: Chọn miền. B2: Chọn Menu Data sau đó chọn biểu tượng Sort3.2. SẮP XẾPChọn khoá thứ nhất[Chọn khoá thứ hai][Chọn khoá thứ ba]Sắp xếp tăng dần (hoặc giảm dần)Dòng đầu là tên trường (ko sắp xếp)Thêm khoá mới3.2. SẮP XẾPMục đích: Lấy ra những bản ghi (thông tin) thoả mãn điều kiện nhất định.Các bước thực hiện:B1: Chọn miềnB2: Vào Menu Data  chọn biểu tượng Filter3.3. TÌM KIẾMChọn miền CSDL gồm cả dòng tên trườngMenu Data/Filter/AutoFilter, ô tên trường có đầu mũi tên thả xuống của hộp danh sáchKích chuột vào đó, có danh sách thả xuống:All: để hiện lại mọi bản ghiTop 10: các giá trị lớn nhấtCustom: tự định điều kiện lọcCác giá trị của cộtAUTOFILTERChọn biểu tượng ở góc phải bên dưới của ô tiêu đề  Number/Text Filters  Custom Filter để thiết lập điều kiện lọc:AUTOFILTERDSUM(Database, Field, Criteria)DMAX(Database, Field, Criteria)DMIN(Database, Field, Criteria)DCOUNT(Database, Field, Criteria)DCOUNTA(Database, Field, Criteria) Gần giống hàm DCOUNTDAVERAGE(Database, Field, Criteria)CÁC HÀM LIÊN QUAN ĐẾN CSDLSubtotal dùng để tổng hợp dữ liệu theo nhóm trong danh sáchVùng dữ liệu ban đầu đã được sắp xếp theo thứ tự Phòng ban.3.4. Tạo các dòng tổng (Subtotal)3.4. Tạo các dòng tổng (Subtotal)Vào Menu Data chọn Subtotal(Có thể click các nút phân cấp để thu gọn hoặc bung ra từng cấp subtotal)123Mục tiêuChọn đúng kiểu đồ thịTạo đồ thịThay đổi đồ thị4. ĐỒ THỊĐồ thị giúp trình bày quan điểm của bạn một cách nhanh chóng. Với đồ thị bạn chuyển dữ liệu trong bảng tính thành hình ảnh, và cho phép bạn so sánh và thấy hướng tăng trưởng chỉ trong chớp mắt.4.1. MỤC TIÊUMỗi đồ thị đều chứa đựng ý nghĩa bên trong của nó – đó là thông điệp mà người tạo đồ thị muốn gửi đến người xem. Vì thế đặc điểm đánh giá chính yếu là thông điệp phải truyền đi hiệu quả nhất.Các kiểu đồ thị khác nhau có thể truyền đi các thông điệp khác nhau của cùng 1 đối tượng dữ liệu.4.2. CHỌN ĐÚNG KIỂU ĐỒ THỊĐồ thị cột (column)Đồ thị cột là lựa chọn mặc định của wizard. Đồ thị cột thích hợp để so sánh trực tiếp các giá trị với nhau.4.2. CHỌN ĐÚNG KIỂU ĐỒ THỊPie chart so sánh từng phần tử của 1 tổng thểĐồ thị bánh để so sánh các phần tử trong 1 tổng thể với nhau để làm rỏ mức độ đóng góp của từng phần tử. Đây là đồ thị lý tưởng để biểu diễn cơ cấu, tỷ trọng của 1 đối tượng4.2. CHỌN ĐÚNG KIỂU ĐỒ THỊLine so sánh các giá trị qua thời gianĐồ thị đường (Line) – phù hợp biểu diễn xu hướng biến động theo thời gian như sản lượng, doanh thu, và lợi nhuận. Sử dụng đồ thị Line khi trục hoành là thời gian.4.2. CHỌN ĐÚNG KIỂU ĐỒ THỊXY Scatter – mối quan hệ 2 đại lượngXY Scatter – phù hợp để thể hiện mối quan hệ giữa các giá trị dữ liệu khoa học hay thống kê. Thể hiện 2 thang đo trong 1 đồ thị qua 2 trục X và Y4.2. CHỌN ĐÚNG KIỂU ĐỒ THỊB1: Chọn miền dữ liệu vẽ đồ thị, chú ý chọn cả 1 tiêu đề hàng và 1 tiêu đề cột đối với các đồ thị kiểu Column, Line và Pie.B2: Vào Menu Insert chọn loại biểu đồ cần thực hiệnB3: Sử dụng Chart Layout, Select Data hoặc nhấp vào các vùng muốn điều chỉnh để định dạng các kiểu thể hiện như:Định kiểu đồ thị Định dữ liệuCác lựa chọn: tiêu đề, các trục, chú giải 4.3. TẠO ĐỒ THỊKhi đồ thị đã được tạo, có thể:Chuyển đồ thị tới vị trí mới bằng phương thức Drag & Drop.Thay đổi kích thước đồ thị Thay đổi các thuộc tính của đồ thị (tiêu đề, chú giải, )Thay đổi các thuộc tính của các thành phần đồ thị (font chữ, tỷ lệ các trục, màu sắc nền,)4.4. THAY ĐỔI ĐỒ THỊKhái niệmTạo lập pivot tableTiêu chí phân tíchCác phương pháp thống kêCông cụ Group5. BẢNG TỔNG HỢP ĐA CHIỀU (PIVOT TABLE)Pivot Table là 1 công cụ của Excel dùng tổng hợp và phân tích dữ liệu với nhiều góc độ và nhiều cấp khác nhau. 5.1. KHÁI NIỆMBÀI TẬP ỨNG DỤNGĐại lýTỉnhMặt hàngNămDoanh thuBHCMĐiện tử200122,449,000BHCMĐiện gia dụng200226,739,000AHCMĐiện cơ200282,091,000AHCMĐiện tử200339,071,000DAn GiangĐiện gia dụng200551,881,000CAn GiangĐiện gia dụng200570,568,000CAn GiangĐiện cơ200559,568,000FHà Nội Điện cơ200424,546,000FHà Nội Điện gia dụng200541,352,000FHà Nội Điện gia dụng200541,373,000Tổng hợp:Doanh thu từng tỉnhDoanh thu từng nhóm hàngDoanh thu từng đại lýDoanh thu từng nămPhân tích:Doanh thu từng tỉnh theo từng mặt hàngDoanh thu từng đại lý theo từng mặt hàng Doanh thu từng năm của từng tỉnhDoanh thu từng đại lý theo nămDoanh thu từng năm theo nhóm hàngChi tiết Doanh thu từng nhóm hàng cho từng đại lý, nhóm theo tỉnh.BÀI TẬP ỨNG DỤNGBước 1: Chỉ định nguồn dữ liệu- Bôi đen cơ sở dữ liệu cần tạo- Menu Insert  chọn biểu tượng PivotTable5.2. TẠO LẬP PIVOT TABLEBước 2: Chỉ định dữ liệuBước 3: Chỉ định nơi đặt PivotTable5.2. TẠO LẬP PIVOT TABLEBước 4: Kéo và thảTrường tổng hợp cấp cao nhất vào Report Filter (Năm)1 hoặc 2 Trường tổng hợp cấp thấp hơn vào Row (Tỉnh, Đại lý)1 hoặc 2 Trường phân tích vào Column (Mặt hàng)Trường dữ liệu phân tích vào Values (Doanh thu)5.2. TẠO LẬP PIVOT TABLETiêu chí phân tích là bất kỳ tiêu chí nào có thể dùng để phân loại dữ liệu....Nếu gõ sai chính tả, dư dấu space, thiếu dấu sắc, dấu huyền; Pivot cũng coi như 1 loại mới và tách riêng ra.Phân loại dữ liệu cần chính xác, không trùng lắp, không mơ hồ. 5.3. TIÊU CHÍ PHÂN TÍCHThống kêSố học sinh mỗi lớp dự thiĐiểm cao nhất của mỗi lớpĐiểm thấp nhất của mỗi lớpĐiểm trung bình mỗi lớp5.4. CÁC PHƯƠNG PHÁP THỐNG KÊHãy tạo 1 Pivot table với cấu trúc như sau: Kéo thả Lớp vào Rows, Tên HS và điểm vào Data.Vì trường tên HS là text, nên Excel tự gán công thức là count: 2 lớp có 6 HS và 1 lớp có5 HS. Trường Điểm là số(number) nên Excel tự gán công thức Sum. 5.4. CÁC PHƯƠNG PHÁP THỐNG KÊNhấn chuột phải vào trường “Sum of Điểm”, chọn field setting. Chọn lại là Max thay vìSum, sửa tên field thành Điểm lớn nhất5.4. CÁC PHƯƠNG PHÁP THỐNG KÊKéo thả Trường điểm vào 2 lần nữa, một lần là Min và 1 lần là Average ta sẽ có:Nếu thay vì trường “Lớp”, ta kéo trường Môn vào Row, ta sẽ có:5.4. CÁC PHƯƠNG PHÁP THỐNG KÊGroup trường loại số: Giả sử với dữ liệu Doanh thu như trên ta có thể Group trường Năm thành nhóm 2 năm, hoặc 3 năm như sau:Bấm chuột phải vào trường Năm trên Pivot Table, Vào Menu Data  Group: 5.5. CÔNG CỤ GROUPBài tập ứng dụng:Group trường loại ngày thángDateUSAJapanUKVNJan 2, 201365674255Jan 3, 201323576467Jan 4, 201367432457Mar 5, 201364566431Mar 6, 201324346742May 7, 201376765764Jun 5, 201342424324Jul 6, 201365675776Jul 7, 201324648642Aug 11, 201356766742Aug 12, 201334426764Oct 13, 201324645624Oct 14, 201377223476Dec 15, 2013245543425.5. CÔNG CỤ GROUPPivot Table còn được sử dụng trong việc thực hiện các thao tác và ứng dụng cao cấp khác như: Lấy dữ liệu từ nhiều nguồn Ứng dụng PivotTable trong báo cáo tài chính5.6. Các ứng dụng khác từ Pivot Table