Cách chuyển hóa tab thành space trong sub timeline text năm 2024
|
Bao tải dứa siêu bền | Sản Xuất & bán số lượng lớn Công ty băng dính Minh Sơn chuyển cung cấp các loại bao tải, bao dứa tại Hà Nội và các tỉnh lân cận. Với chất lượng tốt sản phẩm bao tải dứa luôn cam kết chất lượng cũng như số lượng và cách thức giao hàng đạt tiêu chuẩn. Bao tải dứa được làm từ hạt nhựa PP. Hạt nhựa PP là viết tắt của Polypropylen, được sản xuất từ dầu mỏ. Hạt nhựa PP có đặc tính chống thấm, trong suốt, dễ in ấn và không độc hại nên đượcMinh Sơn chế tạo thành sợi và dùng dệt thành bao tải dứa. Mục lục CHƯƠNG 1: PIVOT TABLE ............................................................................................ 5 I. PHẦN 1: PIVOT TABLE CĂN BẢN ............................................................................ 6 KHÁI NIỆM ...................................................................................................... 6 CÁC BƯỚC THỰC HIỆN: ................................................................................... 7 1. Tô chọn vùng dữ liệu, Vào Tab Insert - Pivot Table: ....................................... 7 2. Xuất hiện hộp thoại Create Pivot Table: .......................................................... 8 3. Kéo và thả:................................................................................................... 9 4. Định dạng báo cáo: ..................................................................................... 11 CÁC THỦ THUẬT TRONG PIVOT TABLE: .......................................................... 13 1. Xem từng phần báo cáo: ............................................................................. 13 2. Thay đổi cấu trúc bảng phân tích: ................................................................ 15 3. Sửa chữa, định dạng Những Sub Total: ........................................................ 19 4. Ẩn các dòng SubTotal Row và Column và Total: ........................................... 19 5. Thay đổi vùng dữ liệu nguồn: ...................................................................... 20 6. Điều chỉnh vị trí 2 trường dữ liệu dòng thành cột: ......................................... 21 II. PHẦN 2: PIVOT TABLE NÂNG CAO ........................................................................ 23 YÊU CẦU CƠ BẢN KHI THỰC HIỆN PIVOT TABLE ............................................. 23 1. Tiêu chí phân tích ....................................................................................... 23 2. Quy trình chuẩn tạo dữ liệu nguồn ............................................................... 23 CÁC PHƯƠNG PHÁP THỐNG KÊ ĐỐI VỚI TRƯỜNG DỮ LIỆU ............................ 24 1. Các phép thống kê thông dụng: ................................................................... 24 2. Các phép thống kê đặc biệt: ........................................................................ 27 CÁC CÔNG CỤ MẠNH ..................................................................................... 34 1. Công cụ Group: .......................................................................................... 34 2. Tạo trường tính toán dựa vào trường có sẵn: ............................................... 39 3. Slicer .......................................................................................................... 45 4. Time line .................................................................................................... 49 Dữ liệu từ nhiều nguồn : (Multi Consolidation Range): ..................................... 51 1. Phương pháp: ............................................................................................. 51 III. PHẦN III: ỨNG DỤNG PIVOT TABLE TRONG BÁO CÁO: ......................................... 56 Phân tích: ...................................................................................................... 56 1. Phân tích chi phí theo loại: .......................................................................... 56 2. Phân tích chi phí theo bộ phận: ................................................................... 58 TỔNG HỢP VÀ PHÂN TÍCH TỪNG CÔNG ĐOẠN SẢN XUẤT: .............................. 59 1. Tổng hợp theo công đoạn: .......................................................................... 61 2. Trình tự thực hiện từng đơn hàng theo sự kiện:............................................ 61 Biên soạn: Ptm0412 Trang 1 / 151 PIVOT TABLE giaiphapexcel.com 3. Tổng hợp theo khách hàng và đơn hàng: ..................................................... 62 4. Tổng hợp theo sản phẩm: ........................................................................... 62 5. Tổng hợp sản phẩm theo tháng, năm: ......................................................... 63 6. Tổng hợp theo tình trạng: ........................................................................... 63 7. Thống kê theo phân xưỡng và chuyền SX: ................................................... 64 8. Thống kê theo quy cách (màu và kích thước): .............................................. 65 IV. PIVOT OPTION VÀ LAYOUT.................................................................................. 65 Layout ........................................................................................................... 65 1. Ý nghĩa ...................................................................................................... 65 2. Thao tác ..................................................................................................... 66 Pivot option ................................................................................................... 69 V. 1. Ý nghĩa và cách mở..................................................................................... 69 2. Các tùy chọn .............................................................................................. 70 HÀM GETPIVOT DATA .......................................................................................... 73 Giới thiệu....................................................................................................... 73 Sử dụng ........................................................................................................ 73 VI. DASHBOARD VỚI PIVOT VÀ PIVOT CHART ........................................................... 75 Ý NGHĨA DASHBOARD.................................................................................... 75 Thực hiện trên Excel ...................................................................................... 75 Tham khảo: ................................................................................................... 75 CHƯƠNG 2: POWER PIVOT......................................................................................... 78 I. GIỚI THIỆU VÀ CÀI ĐẶT ...................................................................................... 78 Giới thiệu....................................................................................................... 78 Cài đặt .......................................................................................................... 78 II. DỮ LIỆU NGUỒN CHO POWER PIVOT ................................................................... 81 DỮ LIỆU TỪ BẢNG TÍNH EXCEL ...................................................................... 81 1. Excel 2010.................................................................................................. 81 2. Excel 2016.................................................................................................. 81 DỮ LIỆU TỪ CƠ SỞ DỮ LIỆU KHÁC................................................................. 81 1. Kết nối và lấy dữ liệu về Excel ..................................................................... 81 2. Tùy chọn chỉ lấy một số cột ......................................................................... 84 3. Tùy chọn chỉ kết nối không lưu xuống sheet ................................................. 86 4. Kết nối thông qua Power Pivot ..................................................................... 87 III. SỬ DỤNG POWER PIVOT ..................................................................................... 90 LIÊN KẾT BẢNG DỮ LIỆU................................................................................ 90 1. Điều kiện để liên kết ................................................................................... 90 2. Tạo liên kết ................................................................................................ 90 Biên soạn: Ptm0412 Trang 2 / 151 PIVOT TABLE giaiphapexcel.com THÊM TRƯỜNG TÍNH TOÁN CHO DỮ LIỆU ...................................................... 91 1. Thêm trên bảng tính ................................................................................... 91 2. Thêm trong Power pivot .............................................................................. 91 PIVOT TABLE TỪ NHIỀU BẢNG ....................................................................... 92 1. Tạo Pivot table............................................................................................ 92 2. Các thủ thuật chọn field .............................................................................. 93 3. Kết hợp Pivot table và Pivot chart ................................................................ 96 IV. POWER QUERY.................................................................................................... 97 GIỚI THIỆU ................................................................................................... 97 1. Giới thiệu.................................................................................................... 97 2. Cài đặt ....................................................................................................... 97 CHỨC NĂNG UNPIVOT ................................................................................... 97 1. Ý nghĩa ...................................................................................................... 97 2. Thực hiện ................................................................................................... 98 QUERY TỪ DỮ LIỆU TỪ BẢNG TÍNH............................................................... 102 1. Điều kiện để tạo query ............................................................................... 102 2. Tạo query từ 1 bảng .................................................................................. 102 HIỆU CHỈNH QUERY...................................................................................... 103 1. Sao chép, xóa Query .................................................................................. 103 2. Chọn cột ................................................................................................... 104 3. Xóa cột...................................................................................................... 105 4. Xóa dòng ................................................................................................... 105 5. Tách 1 cột thành nhiều cột ......................................................................... 106 6. Thêm cột tính toán ngoài dữ liệu ................................................................ 107 7. Thêm cột chứa giá trị theo điều kiện ........................................................... 108 8. Group by query .......................................................................................... 111 9. Bỏ thao tác hiệu chỉnh đã thực hiện ............................................................ 113 MERGE QUERY TỪ NHIỀU QUERY .................................................................. 113 1. Mở cửa sổ Query Editor .............................................................................. 114 2. Merge các query ........................................................................................ 114 3. Kết thúc merge và sử dụng ........................................................................ 116 APPEND QUERY ............................................................................................ 117 QUERY DỮ LIỆU TỪ FILE EXCEL KHÁC ........................................................... 121 1. Kết nối file Excel khác ................................................................................ 121 2. Tạo truy vấn từ các query .......................................................................... 122 COMBINE QUERY TỪ CÁC FILE EXCEL TRONG FOLDER ................................... 122 1. Ý nghĩa combine và điều kiện để combine ................................................... 122 Biên soạn: Ptm0412 Trang 3 / 151 PIVOT TABLE giaiphapexcel.com 2. Thực hiện .................................................................................................. 122 3. Đánh giá ................................................................................................... 125 TRUY VẤN BẰNG SQL VÀO DỮ LIỆU ACCESS .................................................. 125 1. Kết nối dữ liệu bằng truy vấn SQL ............................................................... 125 2. Hiệu chỉnh SQL của query........................................................................... 129 TRUY VẤN BẰNG SQL VÀO DỮ LIỆU TỪ CƠ SỞ DỮ LIỆU KHÁC ....................... 130 V. 1. Kết nối dữ liệu bằng câu truy vấn SQL ........................................................ 130 2. Hiệu chỉnh SQL của query........................................................................... 133 POWER VIEW ..................................................................................................... 136 Giới thiệu và cài đặt ...................................................................................... 136 1. Giới thiệu................................................................................................... 136 2. Fix lỗi khi không mở được Power view ......................................................... 137 THỰC HIỆN .................................................................................................. 138 1. Tạo mới 1 power view sheet ....................................................................... 138 2. Tạo biểu đồ ............................................................................................... 140 3. Tạo thanh điều khiển lọc biểu đồ ................................................................ 141 4. Tạo slicer điều khiển .................................................................................. 143 5. Tạo biểu đồ dạng map ............................................................................... 144 TRANG TRÍ POWER VIEW ............................................................................. 147 1. Định dạng nền ........................................................................................... 147 2. Định dạng bản đồ ...................................................................................... 148 3. Hiển thị các thành phần biểu đồ ................................................................. 149 4. Vẽ thêm và sắp xếp Power View sheet ........................................................ 149 Biên soạn: Ptm0412 Trang 4 / 151 PIVOT TABLE giaiphapexcel.com CHƯƠNG 1: PIVOT TABLE LỜI GIỚI THIỆU Pivot table là một công cụ rất mạnh của Excel, từ Office 97 đã có hiện diện của PivotTable rồi. Trải qua các phiên bản khác nhau của MSOffice, công cụ này ngày một cải tiến và trở thành công cụ phân tích hàng đầu trong Excel. Không biết PivotTable, người dùng có khi phải sử dụng những công thức tính tổng, đếm, trung bình, … có điều kiện, nếu 2 điều kiện trở lên, sẽ phải dùng công thức mảng hoặc hàm SumProduct. Khi dữ liệu lên đến vài trăm, vài ngàn dòng, thậm chí chục ngàn dòng, thì các công thức này khiến cho bảng tính chạy 1 cách ì ạch, khó chịu. Hơn nữa, có những dạng bảng phân tích dữ liệu 2 chiều, việc tách 1 phần dữ liệu ra thành tiêu đề 2 chiều dọc và ngang, có khi phải dùng VBA để giải quyết. Trong khi đó, nếu sử dụng PivotTable, bạn chỉ việc làm theo wizard, và dùng chuột kéo thả. Mỗi lần kéo thả vào các vị trí khác nhau, bạn sẽ có 1 dạng phân tích khác, và tuỳ theo nhu cầu bạn sẽ thả vào đâu. Một ưu điểm mạnh của Pivot table là có thể phân tích với nhiều cấp, tương tự tính tổng nhiểu điều kiện. Với PivotTable 2003, bạn có thể phân tích theo %, có thể tạo field tính toán và phân tích field mới này, và nhiều tính năng khác. Với Pivot Table 2007, 2010 và cao hơn, các tính năng lại được bổ sung thêm hơn nữa. PivotTable làm việc hiệu quả và nhanh chóng cho dù bạn có dữ liệu rất nhiều dòng. Pivotable có nhược điểm là không dùng nó để trình bày như 1 báo cáo chính thức. Kể cả với Office 2007 hay 2010, các dạng trình bày định dạng có sẵn rất đa dạng và phong phú, không ai muốn in nó ra như 1 báo cáo, vì không đúng mẫu (form of report) chính thống. Dù vậy, sức mạnh của PivotTable là chưa thể thay thế trong rất nhiều trường hợp. Trong tập tài liệu này, tôi xin giới thiệu các bạn PivotTable từ căn bản đến nâng cao, và 1 số ứng dụng của nó. Để lập ra báo cáo chính thống, Excel đã hỗ trợ hàm GetPivotTable, có thể tính toán với 14 điều kiện đối với Excel 2003, và 126 điều kiện đối với Excel 2007 và 2010. Một hàm mạnh như đại bác, và dùng để lấy số liệu trong bảng phân tích ra ngoài làm báo cáo. Đặc biệt lần này, tôi xin giới thiệu Pivot Table trong Excel 2007 và 2010, 2016, với những tính năng bổ sung mới nhất của từng phiên bản. Giới thiệu là việc của tôi, còn ứng dụng, là việc của các bạn, chúc thành công. Biên soạn: Ptm0412 Trang 5 / 151 PIVOT TABLE I. giaiphapexcel.com PHẦN 1: PIVOT TABLE CĂN BẢN KHÁI NIỆM 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. Thí dụ bạn có dữ liệu về doanh thu của nhiều cửa hàng, nhiều nhóm mặt hàng, ở nhiều tỉnh khác nhau, trong những khoảng thời gian khác nhau (thí dụ năm) như sau: Đại lý Tỉnh Mặt hàng Năm Doanh thu Lơi nhuận D An Giang Điện cơ 2001 34.590.000 8.459.000 C An Giang Điện cơ 2003 39.568.000 8.161.120 C An Giang Điện cơ 2005 32.417.000 3.824.800 D An Giang Điện cơ 2004 45.046.000 7.202.760 C An Giang Điện gia dụng 2002 24.231.000 2.507.720 D An Giang Điện gia dụng 2005 29.373.000 2.937.300 D An Giang Điện gia dụng 2006 31.811.000 3.017.320 D An Giang Điện gia dụng 2003 25.425.000 2.784.250 C An Giang Điện cơ 2002 25.491.000 24.124.520 E Hà nội Điện cơ 2004 42.154.200 1.825.520 F Hà nội Điện cơ 2006 54.546.000 1.736.380 F Hà nội Điện gia dụng 2001 41.352.000 6.202.800 F Hà nội Điện gia dụng 2005 46.373.000 4.964.760 E Hà nội Điện tử 2002 49.898.000 5.488.780 E Hà nội Điện tử 2003 43.131.000 4.419.650 I HCM Điện cơ 2003 49.433.000 4.943.300 A HCM Điện cơ 2004 42.091.000 3.283.640 H HCM Điện gia dụng 2001 43.949.000 2.197.450 B HCM Điện gia dụng 2006 26.739.000 1.069.560 H HCM Điện tử 2002 45.937.000 2.756.220 A HCM Điện tử 2004 49.071.000 3.907.100 B HCM Điện tử 2005 53.449.000 3.679.390 J Huế Điện cơ 2002 36.505.000 1.325.250 J Huế Điện cơ 2005 47.416.000 5.215.760 Biên soạn: Ptm0412 Trang 6 / 151 PIVOT TABLE giaiphapexcel.com G Huế Điện gia dụng 2006 33.126.000 993.780 J Huế Điện tử 2001 44.527.000 4.007.430 G Huế Điện tử 2003 48.149.000 5.170.430 G Huế Điện tử 2004 52.858.000 7.400.120 Bây giờ bạn muốn tổng hợp: 1. Doanh thu từng tỉnh 2. Doanh thu từng nhóm hàng 3. Doanh thu từng đại lý 4. Doanh thu từng năm Và bạn muốn phân tích: 5. Doanh thu từng tỉnh theo từng mặt hàng 6. Doanh thu từng đại lý theo từng mặt hàng 7. Doanh thu từng năm của từng tỉnh 8. Doanh thu từng đại lý theo năm 9. Doanh thu từng năm theo nhóm hàng 10. Chi tiết Doanh thu từng nhóm hàng cho từng đại lý, nhóm theo tỉnh. 11. Phân tích theo bất cứ kiểu nào bạn có thể nghĩ ra. Bạn có thể làm tất cả những bảng này chỉ bằng dùng chuột kéo thả với công cụ Pivot Table. CÁC BƯỚC THỰC HIỆN: 1. Tô chọn vùng dữ liệu, Vào Tab Insert - Pivot Table: Biên soạn: Ptm0412 Trang 7 / 151 PIVOT TABLE giaiphapexcel.com 2. Xuất hiện hộp thoại Create Pivot Table: - Chọn 1 ô trong Existing worksheet hoặc chọn New worksheet, Nhấn OK. Kết quả 2007 và 2010 cho ra 1 bảng Field list giống nhau: Biên soạn: Ptm0412 Trang 8 / 151 PIVOT TABLE giaiphapexcel.com 3. Kéo và thả: - Trường tổng hợp cấp cao nhất vào Report Filter (thí dụ Năm) 1 hoặc 2 Trường tổng hợp cấp thấp hơn vào Row Labels (thí dụ Tỉnh, sau đó là Đại lý, đừng làm ngược lại là cho Đại lý cấp cao hơn Tỉnh) 1 hoặc 2 Trường phân tích vào Column labels (thí dụ mặt hàng) Trường dữ liệu phân tích vào Data (thí dụ Doanh thu hoặc cả Lợi nhuận) Biên soạn: Ptm0412 Trang 9 / 151 PIVOT TABLE Biên soạn: Ptm0412 giaiphapexcel.com Trang 10 / 151 PIVOT TABLE giaiphapexcel.com 4. Định dạng báo cáo: a) Định dạng khung, màu sắc Excel 2007 và 2010 có sẵn những style định dạng, chỉ cần click chọn trong tab design, nếu muốn nhiều mẫu hơn thì click vào mũi tên bên phải của mục Pivot Table Styles.
Biên soạn: Ptm0412 Trang 11 / 151 PIVOT TABLE giaiphapexcel.com cửa sổ setting hiện ra như sau: Nhấn tiếp nút Number Format, và định dạng số có dấu phân cách hàng ngàn, số ký số thập phân, hoặc số có dạng %, text, ngày tháng, giờ, tiền tệ, dạng phân số, dạng Custom, … y như format cell. Biên soạn: Ptm0412 Trang 12 / 151 PIVOT TABLE giaiphapexcel.com CÁC THỦ THUẬT TRONG PIVOT TABLE: 1. Xem từng phần báo cáo: Kế bên mỗi tên trường của Pivot table, bạn sẽ thấy có 1 nút có hình tam giác, nhấn vào sẽ xổ xuống cho bạn chọn xem 1, hoặc vài nhóm dữ liệu: Không giống như 2003 chỉ cho phép xem mỗi lần 1 loại trong Page Field, Excel 2007 và 2010 cho phép xem 1 hoặc vài loại 1 lúc nếu click chọn vào Select Mutiple Items trong Report Filter fields. Tương tự, Row fields và Column fields có thể cho lọc theo các kiểu lọc khác nhau như Auto filter- Custom: Biên soạn: Ptm0412 Trang 13 / 151 PIVOT TABLE giaiphapexcel.com Đối với trường ngày tháng, ta còn có nhiều lựa chọn hơn nữa: Biên soạn: Ptm0412 Trang 14 / 151 PIVOT TABLE - giaiphapexcel.com Nhóm so sánh: bằng, trước ngày, trước hoặc bằng ngày, sau ngày, sau hoặc bằng ngày, trong khoảng thời gian, không trong khoảng thời gian. 2. Thay đổi cấu trúc bảng phân tích: Bạn có thể dùng chuột kéo thả: thêm, bớt, hoán vị, các trường trong Pivot table để cho ra 1 mẫu phân tích mới: - Kéo bỏ trường Đại lý ra ngoài, Kéo trường năm từ Page xuống Row: Biên soạn: Ptm0412 Trang 15 / 151 PIVOT TABLE giaiphapexcel.com - Kéo thả trường Năm từ trên xuống dưới trường Tỉnh trong vùng Row: - Kéo bỏ trường năm ra ngoài, kéo trường Mặt hàng từ vùng Column xuống trường Row: Biên soạn: Ptm0412 Trang 16 / 151 PIVOT TABLE giaiphapexcel.com - Hoán vị 2 trường Tỉnh và Mặt hàng: - Kéo trường Tỉnh bỏ lên Column: Biên soạn: Ptm0412 Trang 17 / 151 PIVOT TABLE giaiphapexcel.com - Kéo thêm trường Lợi nhuận vào Data Values - Và lập bất cứ mẫu phân tích nào bạn muốn, hoặc bạn nghĩ ra, chỉ bằng cách kéo thả: thêm, bớt, đổi chỗ, hoán vị. Tuy nhiên, đừng làm chuyện vô nghĩa như cho Đại lý nằm ở cấp cao hơn Tỉnh. Vì một tỉnh có thể có nhiều Đại lý, nhưng mỗi Đại lý chỉ nằm trong 1 tỉnh, rốt cuộc là liệt kê tất cả ra mà chẳng tổng hợp hơn được gì so với chỉ 1 trường Đại lý. Biên soạn: Ptm0412 Trang 18 / 151 PIVOT TABLE giaiphapexcel.com 3. Sửa chữa, định dạng Những Sub Total: Bạn rà chuột vào bên trái các Row Subtotal, hoặc bên trên những Column SubTotal, khi nào hiện lên mũi tên nhỏ xíu màu đen, thì click, ta sẽ chọn được toàn bộ các sub total. Bây giờ bạn có thể: - Tô màu dòng/ cột Sub Total - Sửa “An Giang Total” thành “ Cộng An Giang”, (chỉ sửa cho 1 ô, các ô còn lại tự động đổi theo) 4. Ẩn các dòng SubTotal Row và Column và Total: Click chọn những mục cần ẩn, nhấn chuột phải, bỏ chọn SubTotal Biên soạn: Ptm0412 Trang 19 / 151 PIVOT TABLE giaiphapexcel.com 5. Thay đổi vùng dữ liệu nguồn: Nếu bạn rành về Excel, tôi khuyên bạn nên dùng 1 Name động để đặt cho vùng dữ liệu nguồn cho Pivot Table. Khi bạn cập nhật dữ liệu nguồn theo thời gian, Name động sẽ tự tính lại kích thước vùng dữ liệu, và Pivot table sẽ cập nhật theo. Nếu bạn không biết tí gì về Name, hãy làm theo cách sau: - Vào Tab Pivot Table Tools – Option - Nhấn Change Data Source. - Chọn lại vùng dữ liệu mới. Biên soạn: Ptm0412 Trang 20 / 151 PIVOT TABLE giaiphapexcel.com 6. Điều chỉnh vị trí 2 trường dữ liệu dòng thành cột: Giả sử bạn muốn phân tích cả Doanh thu và Lợi nhuận, và khi bạn kéo thả Trường Lợi nhuận vào vùng Data bạn có được: Muốn chuyển về theo dạng cột như thế này: Row Labels Doanh thu Lơi nhuận An Giang 434.619.000 42.285.220 Hà nội 273.619.000 29.237.890 Bạn làm như sau: Nắm kéo cái ∑ Values từ Row thả vào Column Kết quả: Biên soạn: Ptm0412 Trang 21 / 151 PIVOT TABLE Biên soạn: Ptm0412 giaiphapexcel.com Trang 22 / 151 PIVOT TABLE II. giaiphapexcel.com PHẦN 2: PIVOT TABLE NÂNG CAO YÊU CẦU CƠ BẢN KHI THỰC HIỆN PIVOT TABLE 1. Tiêu chí phân tích Đã nói về Pivot Table là nói về tổng hợp và phân tích. Đã nói về phân tích là phải có tiêu chí phân tích. - Tiê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. Thí dụ tiêu chí loại chi phí, tiêu chí kỳ báo cáo, tiêu chí nhóm sản phẩm, tiêu chí vùng thị trường, tiêu chí loại khách hàng, thậm chí phân loại khách hàng cũng có nhiều cách phân loại: phân loại theo khách mua sỉ, lẻ; phân loại theo doanh số lớn nhỏ - Phân loại dữ liệu cần chính xác, không trùng lắp, không mơ hồ. 1 dòng dữ liệu chỉ có thể nằm trong 1 loại, không được hơn. Thí dụ nếu đã có loại chi phí Văn Phòng thì không được có chi phí Điện thoại. Muốn có chi phí Điện thoại thì phải tách chi phí Văn Phòng ra: chi phí Điện thoại, mực in, văn phòng phẩm, lương nhân viên Văn Phòng, cái gì không cần quá chi tiết thì gộp vào chi phí linh tinh. Đó là nói về phân tích, còn nói về Pivot Table: Pivot Table cũng yêu cầu chính xác y như vậy thậm chí nếu bạn 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. Thí dụ trong bảng Pivot Table trên nếu tất cả dữ liệu cột Thành phố Hồ Chí Minh trong cột tỉnh là “HCM”, lọt vào 1 ô “HCM ” (dư 1 khoảng trắng) thì sẽ bị tách riêng ra 1 dòng như sau: ô tô màu cam là ô gõ dư khoảng trắng, thể hiện dòng HCM thứ 2 trên Pivot Table. 2. Quy trình chuẩn tạo dữ liệu nguồn Như vậy để cho Pivot Table làm tốt bạn phải làm theo đúng cách. Tôi gợi ý 1 quy trình như sau: - Lấy dữ liệu thô Chọn tiêu chí phân tích Lập 1 danh sách các loại dữ liệu theo tiêu chí trên, phân bao nhiêu loại tùy bạn, nhưng tên từng loại phải chính xác và ngắn gọn. Nếu tiêu chí này có sẵn trong dữ liệu Biên soạn: Ptm0412 Trang 23 / 151 PIVOT TABLE giaiphapexcel.com thô: kiểm tra tính chính xác của các loại dữ liệu trong tiêu chí đó. Nếu việc kiểm tra khó khăn do dữ liệu nhiều, tốt nhất là làm 1 Danh sách mới. Thêm 1 cột vào dữ liệu thô. Trong cột này mỗi dòng dữ liệu phải chọn 1 loại trong Danh sách đã làm ở bước 3, chính xác từng ký tự. Tốt nhất là dùng validation để chọn. Nếu bạn cần phân tích nhiều tiêu chí, làm lại bước 3 và 4 cho mỗi tiêu chí. Tiến hành tạo Pivot Table. - - CÁC PHƯƠNG PHÁP THỐNG KÊ ĐỐI VỚI TRƯỜNG DỮ LIỆU 1. Các phép thống kê thông dụng: Pivot table không chỉ có thống kê tổng, nó có thể thống kê số lượng, số lớn nhất, nhỏ nhất, số trung bình, ... Thí dụ ta có bảng điểm thi 5 môn của học sinh 3 lớp như sau: STT Tên HS Lớp Môn Điểm 01 Nguyễn văn 01 6A1 Toán 8,3 02 Nguyễn văn 02 6A1 Văn 6,9 03 Nguyễn văn 03 6A1 Lý 9,7 04 Nguyễn văn 04 6A1 Hóa 6,9 05 Nguyễn văn 05 6A1 Sinh 6,5 06 Nguyễn văn 06 6A1 Toán 9,2 07 Nguyễn văn 07 6A2 Văn 8,1 08 Nguyễn văn 08 6A2 Lý 09 Nguyễn văn 09 6A2 Hóa 8,9 10 Nguyễn văn 10 6A2 Sinh 7,4 11 Nguyễn văn 11 6A2 Toán 8,0 12 Nguyễn văn 12 6A2 Văn 7,8 13 Nguyễn văn 13 6A3 Lý 8,7 14 Nguyễn văn 14 6A3 Hóa 7,2 15 Nguyễn văn 15 6A3 Sinh 8,1 10,0 Bây giờ ta muốn thố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ớp Biên soạn: Ptm0412 Trang 24 / 151 PIVOT TABLE giaiphapexcel.com Hãy tạo 1 Pivot table với cấu trúc như sau: Kéo trường Lớp vào Row, trường Tên HS và trường Điểm vào Values. 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ó 3 HS. Trường Điểm là số (number) nên Excel tự gán công thức Sum. Sửa trường Tên HS thành Số HS cho oai. Nhấn chuột phải vào trường Điểm trên Pivot Table, chọn Summarize Values by – Max: Kết quả: Biên soạn: Ptm0412 Trang 25 / 151 PIVOT TABLE giaiphapexcel.com Kéo thả trường Điểm vào Values 2 lần nữa, điều chỉnh lại: 1 lần Summarize by Min và 1 lần Summarize by Average: Nếu thay vì trường “Lớp”, ta kéo trường Môn vào Row, ta sẽ có: Biên soạn: Ptm0412 Trang 26 / 151 PIVOT TABLE giaiphapexcel.com 2. Các phép thống kê đặc biệt: Quay trở lại phần Doanh thu và Lợi nhuận của dữ liệu mẫu phần 1. a) Phân tích % Doanh thu mặt hàng trên tổng số của từng tỉnh: Kéo tỉnh và mặt hàng vào Row, Doanh thu kéo 2 lần vào Values: 1 lần là Sum, 1 lần là % of Parent Row Total. (Ở đây, nhận xét rằng Tỉnh ở mức tổng hợp cao hơn mặt hàng, nên Tỉnh là Parent). Kết quả: Row Labels Doanh thu % Doanh thu theo tỉnh An Giang 434.619.000 32,89% Điện cơ 234.204.000 53,89% Biên soạn: Ptm0412 Trang 27 / 151 PIVOT TABLE Điện gia dụng giaiphapexcel.com 200.415.000 46,11% 273.619.000 20,71% 104.865.000 38,33% Điện gia dụng 82.725.000 30,23% Điện tử 86.029.000 31,44% 320.669.000 24,27% 131.524.000 41,02% 70.688.000 22,04% 118.457.000 36,94% 292.581.000 22,14% Điện cơ 73.921.000 25,27% Điện gia dụng 33.126.000 11,32% 185.534.000 63,41% 1.321.488.000 100,00% Hà nội Điện cơ HCM Điện cơ Điện gia dụng Điện tử Huế Điện tử Grand Total Ta thấy An Giang có 2 mặt hàng là Điện cơ và Điện gia dụng, doanh thu mỗi mặt hàng chiếm lần lượt 53,89% và 46,11%, cộng 100%. Tổng doanh thu của tỉnh An Giang chiếm 32,89% của cả nước. Nếu chọn % of Row Total thì kết quả sẽ không giống mong muốn mà sẽ như sau: Doanh thu % Doanh thu theo tỉnh An Giang 434.619.000 100,00% Điện cơ 234.204.000 100,00% Điện gia dụng 200.415.000 100,00% 273.619.000 100,00% 104.865.000 100,00% Điện gia dụng 82.725.000 100,00% Điện tử 86.029.000 100,00% 320.669.000 100,00% 131.524.000 100,00% 70.688.000 100,00% 118.457.000 100,00% Row Labels Hà nội Điện cơ HCM Điện cơ Điện gia dụng Điện tử Biên soạn: Ptm0412 Trang 28 / 151 PIVOT TABLE Huế giaiphapexcel.com 292.581.000 100,00% Điện cơ 73.921.000 100,00% Điện gia dụng 33.126.000 100,00% 185.534.000 100,00% 1.321.488.000 100,00% Điện tử Grand Total
Row Labels An Giang Doanh thu Tỷ lệ so với cả nước 434.619.000 32,89% C 108.799.000 8,23% D 325.820.000 24,66% Hà nội 273.619.000 20,71% E 166.348.000 12,59% F 107.271.000 8,12% HCM 320.669.000 24,27% A 121.162.000 9,17% B 60.188.000 4,55% H 89.886.000 6,80% I 49.433.000 3,74% Huế 292.581.000 22,14% G 174.133.000 13,18% J 118.448.000 8,96% 1.321.488.000 100,00% Grand Total Ta thấy An Giang chiếm 32,89% Doanh thu cả nước, trong đó đại lý C chiếm 8,23% và đại lý D, 24,66%. (cộng 32,89%). Biên soạn: Ptm0412 Trang 29 / 151 PIVOT TABLE giaiphapexcel.com
Row Labels An Giang Doanh thu Mức tăng DT so với năm trước % tăng DT so với năm trước 434.619.000 2001 91.811.000 2002 113.821.000 22.010.000 23,97% 2003 79.568.000 -34.253.000 -30,09% 2004 70.046.000 -9.522.000 -11,97% 2005 79.373.000 9.327.000 13,32% Hà nội 273.619.000 2001 41.352.000 2002 49.898.000 8.546.000 20,67% 2003 36.131.000 -13.767.000 -27,59% 2004 80.319.000 44.188.000 122,30% 2005 65.919.000 -14.400.000 -17,93% HCM 2001 Biên soạn: Ptm0412 320.669.000 43.949.000 Trang 30 / 151 PIVOT TABLE giaiphapexcel.com 2002 45.937.000 1.988.000 4,52% 2003 49.433.000 3.496.000 7,61% 2004 121.162.000 71.729.000 145,10% 2005 33.449.000 -87.713.000 -72,39% 2006 26.739.000 -6.710.000 -20,06% Huế 292.581.000 2001 44.527.000 2002 26.505.000 -18.022.000 -40,47% 2003 121.275.000 94.770.000 357,56% 2004 52.858.000 -68.417.000 -56,41% 2005 47.416.000 -5.442.000 -10,30%
Row Labels An Giang Doanh thu Mức tăng DT so với năm 2001 % tăng DT so với năm 2001 434.619.000 2001 91.811.000 2002 113.821.000 22.010.000 23,97% 2003 79.568.000 -12.243.000 -13,34% 2004 70.046.000 -21.765.000 -23,71% 2005 79.373.000 -12.438.000 -13,55% Hà nội 273.619.000 2001 41.352.000 2002 49.898.000 8.546.000 20,67% 2003 36.131.000 -5.221.000 -12,63% 2004 80.319.000 38.967.000 94,23% 2005 65.919.000 24.567.000 59,41% 1.988.000 4,52% HCM 320.669.000 2001 43.949.000 2002 45.937.000 Biên soạn: Ptm0412 Trang 31 / 151 PIVOT TABLE giaiphapexcel.com 2003 49.433.000 5.484.000 12,48% 2004 121.162.000 77.213.000 175,69% 2005 33.449.000 -10.500.000 -23,89% 2006 26.739.000 -17.210.000 -39,16% Huế 292.581.000 2001 44.527.000 2002 26.505.000 -18.022.000 -40,47% 2003 121.275.000 76.748.000 172,36% 2004 52.858.000 8.331.000 18,71% 2005 47.416.000 2.889.000 6,49% Ta cũng có thể tính toán như vậy đối với trường lợi nhuận, hoặc cả 2 Doanh thu và lợi nhuận: Mức tăng DT so với năm trước % tăng DT so với năm trước Mức tăng lợi nhuận so với năm trước % tăng lợi nhuận so với năm trước Row Labels Doanh thu An Giang 434.619.000 42.285.220 2001 91.811.000 11.017.320 2002 113.821.000 22.010.000 23,97% 11.966.720 949.400 8,62% 2003 79.568.000 -34.253.000 -30,09% 7.161.120 -4.805.600 -40,16% 2004 70.046.000 -9.522.000 -11,97% 4.202.760 -2.958.360 -41,31% 2005 79.373.000 9.327.000 13,32% 7.937.300 3.734.540 88,86% Hà nội 273.619.000 29.237.890 2001 41.352.000 6.202.800 2002 49.898.000 8.546.000 20,67% 5.488.780 -714.020 -11,51% 2003 36.131.000 -13.767.000 -27,59% 5.419.650 -69.130 -1,26% 2004 80.319.000 44.188.000 122,30% 6.425.520 1.005.870 18,56% 2005 65.919.000 -14.400.000 -17,93% 5.701.140 -724.380 -11,27% Lợi nhuận
Trang 32 / 151 PIVOT TABLE giaiphapexcel.com Giả sử ta muốn tính tỷ lệ doanh thu các tỉnh so với doanh thu của TP HCM dùng làm mốc: Kéo thả trường doanh thu vào Data 2 lần, 1lần tính tổng, 1 lần tính “Show Values As % Of…” như hình: Based field, chọn tỉnh, Based Item, chọn HCM: Kết quả: HCM so với chính nó thì 100%, các tỉnh khác thì có tỷ lệ tương ứng với từng nhóm mặt hàng. Row Labels Doanh thu Tỷ lệ so với HCM An Giang 434.619.000 135,54% Điện cơ 234.204.000 178,07% Điện gia dụng 200.415.000 283,52% 273.619.000 85,33% 104.865.000 79,73% Điện gia dụng 82.725.000 117,03% Điện tử 86.029.000 72,62% 320.669.000 100,00% 131.524.000 100,00% 70.688.000 100,00% 118.457.000 100,00% 292.581.000 91,24% Điện cơ 73.921.000 56,20% Điện gia dụng 33.126.000 46,86% 185.534.000 156,63% Hà nội Điện cơ HCM Điện cơ Điện gia dụng Điện tử Huế Điện tử Grand Total Biên soạn: Ptm0412 1.321.488.000 Trang 33 / 151 PIVOT TABLE giaiphapexcel.com CÁC CÔNG CỤ MẠNH 1. Công cụ Group: Đối với 1 số trường của Pivot Table loại trường số và trường ngày tháng, ta có thể Group chúng lại. a) 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, chọn Group: Kết quả: - Group 2 năm: Row Labels Doanh thu Lợi nhuận 434.619.000 42.285.220 2001-2002 113.821.000 11.966.720 2003-2004 149.614.000 11.363.880 2005-2006 171.184.000 18.954.620 273.619.000 29.237.890 2001-2002 91.250.000 11.691.580 2003-2004 116.450.000 11.845.170 2005-2006 65.919.000 5.701.140 320.669.000 21.836.660 2001-2002 89.886.000 4.953.670 2003-2004 170.595.000 12.134.040 2005-2006 60.188.000 4.748.950 292.581.000 25.112.770 An Giang Hà nội HCM Huế Biên soạn: Ptm0412 Trang 34 / 151 PIVOT TABLE giaiphapexcel.com 2001-2002 71.032.000 5.332.680 2003-2004 141.007.000 13.570.550 2005-2006 80.542.000 6.209.540 Grand Total 1.321.488.000 118.472.540 - Group 3 năm: Row Labels Doanh thu Lợi nhuận 434.619.000 42.285.220 2001-2003 193.389.000 19.127.840 2004-2006 241.230.000 23.157.380 273.619.000 29.237.890 2001-2003 127.381.000 17.111.230 2004-2006 146.238.000 12.126.660 320.669.000 21.836.660 2001-2003 139.319.000 9.896.970 2004-2006 181.350.000 11.939.690 292.581.000 25.112.770 2001-2003 159.181.000 11.503.110 2004-2006 133.400.000 13.609.660 An Giang Hà nội HCM Huế Grand Total 1.321.488.000 118.472.540
Ngày Mặt hàng 02/01/09 A 39 10.000 390.000 07/01/09 C 10 28.000 280.000 12/01/09 B 45 18.000 810.000 17/01/09 D 32 40.000 1.280.000 22/01/09 D 40 40.000 1.600.000 Biên soạn: Ptm0412 Số lượng Đơn giá Thành tiền Trang 35 / 151 PIVOT TABLE giaiphapexcel.com 27/01/09 B 19 18.000 342.000 01/02/09 A 10 10.000 100.000 06/02/09 B 23 18.000 414.000 11/02/09 C 10 28.000 280.000 16/02/09 A 21 10.000 210.000 21/02/09 A 12 10.000 120.000 08/12/09 A 17 10.000 170.000 13/12/09 C 21 28.000 588.000 18/12/09 B 12 18.000 216.000 23/12/09 C 46 28.000 1.288.000 28/12/09 A 39 10.000 390.000 ... Sau khi tạo 1 Pivot table theo ngày như sau: Row Labels 02/01/09 A B C D Grand Total 150.000 150.000 07/01/09 280.000 12/01/09 280.000 594.000 594.000 17/01/09 2.000.000 2.000.000 22/01/09 1.840.000 1.840.000 27/01/09 01/02/09 900.000 900.000 490.000 06/02/09 490.000 864.000 11/02/09 864.000 420.000 420.000 16/02/09 340.000 340.000 21/02/09 180.000 180.000 26/02/09 270.000 270.000 Ta có thể group theo tháng, theo quý, … hoặc nhiều cấp như sau: Biên soạn: Ptm0412 Trang 36 / 151 PIVOT TABLE giaiphapexcel.com Kết quả lần lượt: - Group theo tháng: Sum of Thành tiền Column Labels Row Labels A B C th1 150.000 1.494.000 280.000 th2 1.010.000 1.134.000 420.000 D 3.840.000 Grand Total 5.764.000 2.564.000 ... th11 1.250.000 th12 660.000 396.000 8.370.000 Grand Total 3.920.000 5.170.000 2.240.000 1.720.000 5.016.000 6.624.000 12.684.000 29.160.000 56.838.000 B C - Group theo quý: Sum of Thành tiền Column Labels Row Labels A D Grand Total Qtr1 1.900.000 3.312.000 1.064.000 6.160.000 12.436.000 Qtr2 1.810.000 522.000 3.780.000 7.080.000 13.192.000 Qtr3 2.750.000 1.476.000 5.600.000 4.920.000 14.746.000 Qtr4 1.910.000 1.314.000 2.240.000 11.000.000 16.464.000 8.370.000 6.624.000 12.684.000 29.160.000 56.838.000 Grand Total Biên soạn: Ptm0412 Trang 37 / 151 PIVOT TABLE giaiphapexcel.com - Group theo quý và tháng: Sum of Thành tiền Row Labels Column Labels A B C D Grand Total Qtr1 1.900.000 3.312.000 1.064.000 6.160.000 12.436.000 th1 150.000 1.494.000 280.000 3.840.000 5.764.000 th2 1.010.000 1.134.000 420.000 th3 740.000 684.000 364.000 2.320.000 4.108.000 Qtr2 1.810.000 522.000 3.780.000 7.080.000 13.192.000 th4 940.000 2.564.000 1.344.000 2.284.000 ... th11 1.250.000 th12 660.000 396.000 8.370.000 6.624.000 Grand Total 3.920.000 5.170.000 2.240.000 1.720.000 5.016.000 12.684.000 29.160.000 56.838.000 Nếu muốn Group theo tuần thì Group theo ngày và chọn 7 ngày: Sum of Thành tiền Row Labels 01/01/09 - 07/01/09 Biên soạn: Ptm0412 Column Labels A 150.000 B C 280.000 D Grand Total 430.000 Trang 38 / 151 PIVOT TABLE giaiphapexcel.com 08/01/09 - 14/01/09 594.000 594.000 15/01/09 - 21/01/09 22/01/09 - 28/01/09 29/01/09 - 04/02/09 900.000 2.000.000 2.000.000 1.840.000 2.740.000 490.000 05/02/09 - 11/02/09 490.000 864.000 420.000 1.284.000 ... 03/12/09 - 09/12/09 240.000 1.720.000 10/12/09 - 16/12/09 17/12/09 - 23/12/09 24/12/09 - 30/12/09 Grand Total 396.000 1.232.000 1.232.000 1.008.000 1.404.000 420.000 8.370.000 1.960.000 420.000 6.624.000 12.684.000 29.160.000 56.838.000 2. Tạo trường tính toán dựa vào trường có sẵn: a) Tạo trường tính toán: Giả sử ta muốn tính % Lợi nhuận trên doanh thu dựa vào 2 trường này, ta phải Insert 1 trường tính toán (Calculation field): Trong hộp thoại hiện ra, đặt tên trường, lập công thức tính: = Lợi nhuận/ Doanh thu bằng cách double click vào tên trường, thêm toán tử chia và thêm trường khác. Biên soạn: Ptm0412 Trang 39 / 151 PIVOT TABLE giaiphapexcel.com Sau đó nhấn Add và nhấn OK, định dạng % cho trường mới: Row Labels % Lợi nhuận trên Doanh thu Doanh thu Lợi nhuận 91.811.000 42.285.220 9,73% 2001 91.811.000 11.017.320 12,00% 2002 84.590.000 11.966.720 10,51% 2003 79.568.000 7.161.120 9,00% 2004 70.046.000 4.202.760 6,00% 2005 79.373.000 7.937.300 10,00% Hà nội 80.319.000 29.237.890 10,69% 2001 41.352.000 6.202.800 15,00% 2002 49.898.000 5.488.780 11,00% 2003 36.131.000 5.419.650 15,00% 2004 80.319.000 6.425.520 8,00% 2005 41.373.000 5.701.140 8,65% 82.091.000 21.836.660 6,81% 2001 43.949.000 2.197.450 5,00% 2002 45.937.000 2.756.220 6,00% 2003 49.433.000 4.943.300 10,00% 2004 82.091.000 7.190.740 5,93% An Giang HCM Biên soạn: Ptm0412 Trang 40 / 151 PIVOT TABLE 2005 giaiphapexcel.com 33.449.000 4.748.950 7,89% 88.149.000 25.112.770 8,58% 2001 44.527.000 4.007.430 9,00% 2002 26.505.000 1.325.250 5,00% 2003 88.149.000 6.170.430 7,00% 2004 52.858.000 8.393.900 9,76% 2005 47.416.000 5.215.760 11,00% 91.811.000 118.472.540 8,97% Huế Grand Total Ta có thể tính tỷ lệ hoàn thành kế hoạch, nếu ta tạo 1 trường tính lợi nhuận kế hoạch bằng 10% doanh thu, và so sánh tỷ lệ giữa lợi nhuận thực với lợi nhuận kế hoạch: Tạo 2 trường tính toán: - Lợi nhuận KH = Doanh thu * 10% - Tỷ lệ hoàn thành KH = Lợi nhuận/ Lợi nhuận KH, định dạng % Row Labels Điện cơ Doanh thu Lợi nhuận KH (10% DT) Lợi nhuận thực hiện Tỷ lệ hoàn thành KH 544.514.000 54.451.400 41.752.730 76,68% An Giang 234.204.000 23.420.400 19.822.880 84,64% Hà nội 104.865.000 10.486.500 7.161.900 68,30% HCM 131.524.000 13.152.400 8.226.940 62,55% Huế 73.921.000 7.392.100 6.541.010 88,49% 386.954.000 38.695.400 37.890.690 97,92% 200.415.000 20.041.500 22.462.340 112,08% Hà nội 82.725.000 8.272.500 11.167.560 135,00% HCM 70.688.000 7.068.800 3.267.010 46,22% Huế 33.126.000 3.312.600 993.780 30,00% Điện tử 390.020.000 39.002.000 38.829.120 99,56% Hà nội 86.029.000 8.602.900 10.908.430 126,80% HCM 118.457.000 11.845.700 10.342.710 87,31% Huế 185.534.000 18.553.400 17.577.980 94,74% 1.321.488.000 132.148.800 118.472.540 89,65% Điện gia dụng An Giang Grand Total Hoặc dùng công thức phức tạp hơn để tính thưởng chẳng hạn: Biên soạn: Ptm0412 Trang 41 / 151 PIVOT TABLE giaiphapexcel.com Thưởng = Doanh thu * IF(Doanh thu >100.000.000, 5%, 3%) Nhóm theo tỉnh để theo dõi: Row Labels Doanh thu An Giang Thưởng 434.619.000 21.730.950 C 108.799.000 5.439.950 D 325.820.000 16.291.000 Hà nội 273.619.000 13.680.950 E 166.348.000 8.317.400 F 107.271.000 5.363.550 HCM 320.669.000 16.033.450 A 121.162.000 6.058.100 B 60.188.000 1.805.640 H 89.886.000 2.696.580 I 49.433.000 1.482.990 Huế 292.581.000 14.629.050 G 174.133.000 8.706.650 J 118.448.000 5.922.400 Grand Total 1.321.488.000 66.074.400 Với trường mới, ta có thể làm mọi phép thống kê như trường có sẵn. Thí dụ tính mức tăng giảm % lợi nhuận so với năm trước: Row Labels An Giang Doanh thu Sum of Lơi nhuận % Lợi nhuận Tăng giảm % Lợi nhuận so với năm trước 434.619.000 42.285.220 9,73% 2001 84.590.000 8.459.000 10,00% 2002 29.231.000 3.507.720 12,00% 2,00% 2003 79.568.000 7.161.120 9,00% -3,00% 2004 70.046.000 4.202.760 6,00% -3,00% 2005 79.373.000 7.937.300 10,00% 4,00% 2006 91.811.000 11.017.320 12,00% 2,00% Hà nội 273.619.000 29.237.890 10,69% Biên soạn: Ptm0412 Trang 42 / 151 PIVOT TABLE giaiphapexcel.com 2001 41.352.000 6.202.800 15,00% 2002 49.898.000 5.488.780 11,00% -4,00% 2003 36.131.000 5.419.650 15,00% 4,00% 2004 80.319.000 6.425.520 8,00% -7,00% 2005 41.373.000 4.964.760 12,00% 4,00% 2006 24.546.000 736.380 3,00% -9,00% 320.669.000 21.836.660 6,81% 2001 43.949.000 2.197.450 5,00% 2002 45.937.000 2.756.220 6,00% 1,00% 2003 49.433.000 4.943.300 10,00% 4,00% 2004 121.162.000 7.190.740 5,93% -4,07% 2005 33.449.000 3.679.390 11,00% 5,07% 2006 26.739.000 1.069.560 4,00% -7,00% 292.581.000 25.112.770 8,58% 2001 44.527.000 4.007.430 9,00% 2002 26.505.000 1.325.250 5,00% -4,00% 2003 88.149.000 6.170.430 7,00% 2,00% 2004 52.858.000 7.400.120 14,00% 7,00% 2005 47.416.000 5.215.760 11,00% -3,00% 2006 33.126.000 993.780 3,00% -8,00% 1.321.488.000 118.472.540 8,97% HCM Huế Grand Total
Biên soạn: Ptm0412 Trang 43 / 151 PIVOT TABLE giaiphapexcel.com Viết công thức: Doanh thu Column Labels Điện cơ Điện gia dụng An Giang 234.204.000 200.415.000 Hà nội 104.865.000 82.725.000 86.029.000 168.754.000 HCM 131.524.000 70.688.000 118.457.000 189.145.000 Row Labels Biên soạn: Ptm0412 Điện tử Nhóm gia dụng 200.415.000 Trang 44 / 151 PIVOT TABLE giaiphapexcel.com Huế Grand Total 73.921.000 33.126.000 185.534.000 218.660.000 544.514.000 386.954.000 390.020.000 776.974.000 Ta có thể filter dấu 2 mục con đi: Doanh thu Column Labels Row Labels Điện cơ Nhóm gia dụng An Giang 234.204.000 200.415.000 Hà nội 104.865.000 168.754.000 HCM 131.524.000 189.145.000 Huế 73.921.000 218.660.000 544.514.000 776.974.000 Grand Total 3. Slicer Slicer là 1 công cụ tương tự như bộ lọc (filter) nhưng trực quan hơn và dễ diều khiển hơn. Ngoài ra nếu nhiều pivot table có cùng 1 dữ liệu nguồn thì có thể liên kết để điều khiển chung. a) Tạo slicer - Click chọn 1 Pivot table, sau đó chọn insert slicer như hình : - Đánh dấu chọn trường muốn điều khiển, có thể đánh dấu 1 hoặc nhiều trường Biên soạn: Ptm0412 Trang 45 / 151 PIVOT TABLE giaiphapexcel.com Kết quả : Biên soạn: Ptm0412 Trang 46 / 151 PIVOT TABLE giaiphapexcel.com
Biên soạn: Ptm0412 Trang 47 / 151 PIVOT TABLE giaiphapexcel.com
- Đánh dấu chọn thêm những Pivot khác Kết quả: chọn trên slicer điều khiển chọn trên cả 2 Pivot table: Biên soạn: Ptm0412 Trang 48 / 151 PIVOT TABLE giaiphapexcel.com 4. Time line Tương tự slicer nhưng timeline chỉ dùng cho trường dữ liệu dạng ngày tháng, và chỉ 2016 mới có a) Tạo time line Nhấn nút và chọn field như hình Biên soạn: Ptm0412 Trang 49 / 151 PIVOT TABLE giaiphapexcel.com Kết quả :
Biên soạn: Ptm0412 Trang 50 / 151 PIVOT TABLE giaiphapexcel.com Dữ liệu từ nhiều nguồn : (Multi Consolidation Range): Trong Excel 2003, số dòng tối đa là 216 = 65.536 dòng. Đối với dữ liệu phát sinh trong năm của 1 doanh nghiệp lớn nhiều khi vượt quá số dòng trên. Người ta phải nhập liệu chia ra từng tháng, mỗi tháng 1 sheet hoặc mỗi tháng 1 file riêng lẻ. Hoặc đối với 1 doanh nghiệp có nhiều chi nhánh, mỗi chi nhánh sẽ có 1 file riêng và chúng ta phải tổng hợp lại để phân tích. Các trường hợp trên Excel sẽ có công cụ để phân tích từ nhiều nguồn. Excel 2010 đã tăng số dòng lên đến 220 = 1.048.576 dòng. Tuy vậy ta cũng cứ tìm hiểu phương pháp tổng hợp phân tích bằng Pivot Table từ nhiều nguồn. Giả sử ta có 2 vùng dữ liệu nằm ở 2 sheet khác nhau, 1 sheet dữ liệu 6 tháng đầu năm và 1 sheet dữ liệu 6 tháng cuối năm. Hai vùng dữ liệu này cùng cấu trúc: ngày Ctừ Loại chi phí TK chi phí TK đư số tiền 04/01/05 1A/PC Chi tiếp khách 642800 111 1.406.000 05/01/05 8/PC Chi VPP 642300 111 450.000 05/01/05 6/PC Chi VPP 642300 111 115.000 05/01/05 7/PC Chi VPP 642300 111 100.000 642300 111 75.000 06/01/05 16/PC Chi mua thiết bị 1. Phương pháp: Excel 2010 không có nút lệnh Pivot Table Wizard mà chỉ có nút lệnh insert thông thường. Bạn phải gắn nút lệnh vào thanh công cụ Quick Access Toolbar, hoặc dùng phím tắt Alt – D – P: Chọn Multiple consolidation ranges Biên soạn: Ptm0412 Trang 51 / 151 PIVOT TABLE giaiphapexcel.com Chọn create a single page field for me Chọn từng vùng dữ liệu và nhấn Add: Biên soạn: Ptm0412 Trang 52 / 151 PIVOT TABLE giaiphapexcel.com Chọn nơi đặt Pivot table: Excel tự dộng chèn 1 vùng dữ liệu vào Row label gọi là Row và 1 vùng dữ liệu vào Column labels gọi là Column Biên soạn: Ptm0412 Trang 53 / 151 PIVOT TABLE giaiphapexcel.com Sum of Value Column Labels Row Labels số tiền TK chi phí TK đư BHXH 18.683.000 0 0 BHYT 2.658.300 0 0 Chi mua thiết bị 3.598.050 0 0 103.473.000 0 0 248.360 0 0 3.314.545 0 0 230.000 0 0 6.696.908 0 0 Tiền lương Bao bì Chi đăng báo tuyển dụng Chi khác Chi mua quà Ta không cần trường nào thì dấu trường đó đi bằng filter: 2 trường TK đang có giá trị zero và chẳng có ý nghĩa gì: Biên soạn: Ptm0412 Trang 54 / 151 PIVOT TABLE giaiphapexcel.com Sum of Value Column Labels Row Labels số tiền BHXH 18.683.000 BHYT 2.658.300 Chi mua thiết bị 3.598.050 Tiền lương Bao bì Chi đăng báo tuyển dụng Chi khác Chi mua quà Chi Phí nhập hàng Chi quảng cáo triển lãm Chi quỹ an ninh quốc phòng 248.360 3.314.545 230.000 6.696.908 553.840 5.454.545 600.000 Chi sửa thiết bị 1.362.036 Chi tiếp khách 91.224.555 Chi VPP Biên soạn: Ptm0412 103.473.000 7.049.652 Trang 55 / 151 PIVOT TABLE giaiphapexcel.com III. PHẦN III: ỨNG DỤNG PIVOT TABLE TRONG BÁO CÁO: Phân tích: 1. Phân tích chi phí theo loại: Bạn biết rằng một số chi phí chẳng hạn chi phí tiền lương, văn phòng phẩm, điện, điện thoại . . . được sử dụng ở nhiều bộ phận khác nhau như bộ phận kế toán, bộ phận kinh doanh, bộ phận hành chánh . . . Nhà quản lý cần biết rằng chi phí tháng này so với tháng trước tăng hay giảm, tăng ở bộ phận nào, tăng có lý do chính đáng hay không? Tỷ lệ chi phí nào ở bộ phận nào là bao nhiêu, tỷ lệ trên tổng số là bao nhiêu? So với tháng trước là tăng hay giảm? và quan trọng nhất là có thể tiết giảm hay không? tiết giảm ở bộ phận nào? Cụ thể như Văn phòng phẩm bộ phận kế toán tăng có kèm theo doanh thu hoặc lượng chứng từ tăng không? chi phí điện thoại của bộ phận bán hàng tăng có kèm theo doanh thu tăng hay lượng khách hàng tăng hay không? Bạn phải có 1 cơ sở dữ liệu chuẩn dạng như sau: ngày Loại chi phí Bộ phận số tiền 04/01/2005 Chi tiếp khách Ban Gíam đốc 1.406.000 05/01/2005 Chi VPP Hành chánh NS 450.000 05/01/2005 Chi VPP Hành chánh NS 115.000 05/01/2005 Chi VPP Hành chánh NS 100.000 06/01/2005 Chi mua thiết bị Hành chánh NS 75.000 06/01/2005 lệ phí hải quan Kế hoạch 06/01/2005 Gửi xe Ban Gíam đốc 12.000 06/01/2005 Phí Bưu điện Ban Gíam đốc 1.112.727 08/01/2005 Phí vận chuyển Phòng Tổ chức 08/01/2005 Chi tiếp khách Ban Gíam đốc 438.000 08/01/2005 Chi tiếp khách Ban Gíam đốc 200.000 10/01/2005 tiền điện thoại Phòng Tổ chức 5.000 8.000 2.418.619 Với trường ngày tháng group theo tháng, tạo pivot table để phân tích chi phí theo tháng như sau: Biên soạn: Ptm0412 Trang 56 / 151 PIVOT TABLE giaiphapexcel.com Nếu muốn đẹp thì thêm 1 biểu đồ minh họa. Ghi chú: với Pivot chart ta có thể filter để xem lần lượt từng loại chi phí. Kết quả: Biên soạn: Ptm0412 Trang 57 / 151 PIVOT TABLE giaiphapexcel.com 2. Phân tích chi phí theo bộ phận: Cũng với bảng dữ liệu trên, có thể phân tích theo bộ phận: Biên soạn: Ptm0412 Trang 58 / 151 PIVOT TABLE giaiphapexcel.com TỔNG HỢP VÀ PHÂN TÍCH TỪNG CÔNG ĐOẠN SẢN XUẤT: Với cấu trúc dữ liệu chuẩn, mỗi công đoạn và mỗi sự kiện được ghi lại thành 1 record (dòng) thay vì tạo nhiều cột, ta có thể tạo pivot table theo dõi 1 sự việc theo từng sự kiện diễn ra, thí dụ như theo dõi 1 đơn hàng sản xuất từ khi bắt đầu nhận tới khi hoàn tất. Dữ liệu mẫu: Date 14/04/2012 14/04/2012 15/04/2012 15/04/2012 15/04/2012 16/04/2012 16/04/2012 16/04/2012 17/04/2012 Customer PO Number Product Café AS AS15 Handbag Fresh Fr01 Jacket Fresh Fr01 Jacket Rice Hotel RH01 Hat Rice Hotel RH02 Hat Cafe CMT8 FLW01 Jacket Rice Hotel RH01 Hat Cut Cutting 1.000 done Saitex CutS Finish Fresh Fr01 Jacket Sew Sewing 1.000 done BH02 Sew02 Washing 3- (Apr 16, 2012) done Sew Fresh Fr03 Jacket New order Order 1.000 receipt Biên soạn: Ptm0412 Color Code Size Order Quantity Event Event qty Order Status 250 New order Order 250 receipt 1.000 New order Factory Work shops Current status Detail BH01 Sewing done Order 1.000 receipt BH02 Washing 1- (Apr 14, 2012) done New order Cut Cutting 1.000 done BH02 1.000 New order Order 1.000 receipt Saitex Finish 1.000 New order Order 1.000 receipt Saitex Washing 1- (Apr 15, 2012) done New order 200 New order Order 200 receipt BH01 Washing 1- (Apr 16, 2012) done New order 1.000 Trang 59 / 151 BH02 Cut02 1- (Apr 14, 2012) New order Washing 2- (Apr 15, 2012) done Cut 1- (Apr 15, 2012) New order 2- (Apr 16, 2012) Cut Washing 1- (Apr 17, 2012) done New order PIVOT TABLE 17/04/2012 17/04/2012 19/04/2012 19/04/2012 giaiphapexcel.com Fresh Fr03 Jacket ELGA EG02 Hat HALOGEN Hlg01 Jacket HALOGEN Hlg01 Jacket Biên soạn: Ptm0412 cut Cutting 1.000 done BH02 1.000 New order Order 1.000 receipt Saitex Delay 1- (Apr 17, 2012) New order 200 New order Order 200 receipt BH01 Cutting done 1- (Apr 19, 2012) New order Cut Cutting 200 done BH01 Cutting done 2- (Apr 19, 2012) Cut Trang 60 / 151 Cut02 Cut01 Washing 2- (Apr 17, 2012) done cut PIVOT TABLE giaiphapexcel.com 1. Tổng hợp theo công đoạn: Sum of Event qty Column Labels Order Order Qty Cutting Sewing Washing Order Finish Delay done done done cancel AG12 600 608 608 608 Al001 100 102 102 102 AP05 1.200 1.220 1.220 1.210 250 248 AQ001 120 AS15 250 BC03 80 BR01 200 210 BR02 500 520 BR03 80 82 Packing & ship 600 102 1.200 520 2. Trình tự thực hiện từng đơn hàng theo sự kiện: Biên soạn: Ptm0412 Trang 61 / 151 PIVOT TABLE giaiphapexcel.com Sum of Event qty Row Labels AG12 1- (May/08/2012) - New order 600 2- (May/16/2012) - Cut 608 3- (May/17/2012) - Sewing done 608 4- (May/20/2012) - Wash done 608 5- (Jul/02/2012) - Packing & ship 600 Al001 1- (Apr/25/2012) - New Order receipt 100 2- (Apr/26/2012) - Cutting done 102 3- (Apr/29/2012) - Sewing done 102 4- (Jul/04/2012) - Wash finish 102 5- (Jul/05/2012) - Delay for waiting material 102 3. Tổng hợp theo khách hàng và đơn hàng: Sum of Order Quantity Customer Product Handbag Hat AG Co Jacket Trousers 600 AG12 600 Airport 1.200 AP05 1.200 Alex 100 Al001 100 AQ CAFÉ 120 AQ001 120 Bank Robber 80 500 BR01 200 200 BR02 500 BR03 80 4. Tổng hợp theo sản phẩm: Row Labels Sum of Order Quantity Biên soạn: Ptm0412 Trang 62 / 151 PIVOT TABLE giaiphapexcel.com Handbag 5.580 Hat 9.950 Jacket 7.800 Trousers 710 Grand Total 24.040 5. Tổng hợp sản phẩm theo tháng, năm: Sum of Order Quantity Year - Month Products Handbag Hat Jacket Trousers Grand Total 2012 Apr 1.500 3.450 5.050 100 10.100 May 4.000 6.300 2.750 210 13.260 200 480 200 200 710 24.040 Jun 80 200 Jul Grand Total 5.580 9.950 7.800 6. Tổng hợp theo tình trạng: Biên soạn: Ptm0412 Trang 63 / 151 PIVOT TABLE giaiphapexcel.com 7. Thống kê theo phân xưỡng và chuyền SX: Sum of Event qty Product Factory Workshops BH01 Cut01 Handbag 250 Hat Jacket 1.807 QC01 QCBH1 Sew01 330 Cut01 1.220 Cut02 2.030 CutA Biên soạn: Ptm0412 Grand Total 655 2.712 250 250 500 Wash01 BH02 Trousers QC02 1.200 Sew01 1.220 Sew02 2.020 500 1.807 455 102 2.694 1.002 452 102 1.556 312 1.532 2.014 2.520 6.564 520 520 500 1.700 1.220 2.520 4.540 Trang 64 / 151 PIVOT TABLE giaiphapexcel.com SewA Wash02 BH03 2.220 CutB 520 520 2.520 4.740 90 8. Thống kê theo quy cách (màu và kích thước): Nghĩa là với 1 bảng dữ liệu chuẩn, ta có thể ra rất nhiều những báo cáo khác nhau. IV. PIVOT OPTION VÀ LAYOUT Layout 1. Ý nghĩa Là các tùy chọn sắp xếp hiển thị của Pivot: Biên soạn: Ptm0412 Trang 65 / 151 90 PIVOT TABLE giaiphapexcel.com 2. Thao tác a) Sub total - Mục 1: Không hiển thị subtotal - Mục 2: Hiển thị subtotal thành 1 dòng tổng bên dưới của nhóm - Mục 3: Hiển thị sub total trên dòng đầu của nhóm Biên soạn: Ptm0412 Trang 66 / 151 PIVOT TABLE giaiphapexcel.com
- Mục Mục Mục Mục 1: 2: 3: 4: không hiển thị dòng tổng cho dòng và cột Hiển thị tổng cho cả dòng và cột Chỉ hiển thị tổng cho dòng Chỉ hiển thị tổng cho cột
Biên soạn: Ptm0412 Trang 67 / 151 PIVOT TABLE giaiphapexcel.com - Mục 1: Hiển thi thu gọn - Mục 2: Hiển thị mỗi trường thành 1 cột, tổng group cùng dòng với group - Mục 3: Dạng bảng, mỗi group 1 dòng tổng riêng - Mục 4: Lập lại tiêu đề nhóm Biên soạn: Ptm0412 Trang 68 / 151 PIVOT TABLE giaiphapexcel.com - Mục 5: ngược lại mục 4 Pivot option 1. Ý nghĩa và cách mở Pivot option là những tùy chọn cho Pivot table. Click chọn Pivot table, trong tab Design nhấn chọn option như hình: Hoặc nhấn chuột phải vào pivot chọn option: Biên soạn: Ptm0412 Trang 69 / 151 PIVOT TABLE giaiphapexcel.com 2. Các tùy chọn a) Tab Layout and format - Mục 1: Merge tiêu đề dòng hoặc cột khi ở dạng tabular Biên soạn: Ptm0412 Trang 70 / 151 PIVOT TABLE giaiphapexcel.com - Mục 2: Canh lề thụt vào bao nhiêu ký tự khi ở dạng compact - Mục 3 và 4: Tùy chọn ký tự hiển thị khi dữ liệu lỗi hay bằng 0 - Mục 5: tự động canh chiều rộng cột cho vừa với dữ liệu khi cập nhật - Mục 6: Bảo toàn định dạng khi cập nhật dữ liệu Biên soạn: Ptm0412 Trang 71 / 151 PIVOT TABLE giaiphapexcel.com
- Mục 1 và 2: tùy chọn hiển thị mục tổng cộng - Mục 3 cho phép lọc nhiều điều kiện c) Tab Display Biên soạn: Ptm0412 Trang 72 / 151 PIVOT TABLE giaiphapexcel.com - Mục 1: Hiển thị dấu +, - để mở, thu gọn các nhóm V. HÀM GETPIVOT DATA Giới thiệu Hàm GetPivotData dùng để lấy dữ liệu từ 1 bảng Pivot có sẵn, với những tham số đang lọc của những trường dữ liệu Sử dụng Từ 1 ô của báo cáo đang muốn lấy dữ liệu tổng hợp sẵn của Pivot, thí dụ doanh thu mặt hàng điện cơ của An Giang, đánh dấu = và click chọn ô có giá trị tương ứng của Pivot ta sẽ có ngay hàm getPivotData: Nếu Enter và copy ra những ô còn lại của báo cáo thì sẽ có kết quả như sau: Biên soạn: Ptm0412 Trang 73 / 151 PIVOT TABLE giaiphapexcel.com Đó là do Excel đang gắn cứng những tham số đầu vào của hàm: =GETPIVOTDATA("Doanh thu",$F$1,"Tỉnh","An Giang","Mặt hàng","Điện cơ") Ta phải sửa tham số động cho dòng và cột: =GETPIVOTDATA("Doanh thu",$F$1,"Tỉnh",$K2,"Mặt hàng",L$1) Kết quả: Để loại bỏ lỗi có thể dùng IfError: =IFERROR(GETPIVOTDATA("Doanh thu",$F$1,"Tỉnh",$K2,"Mặt hàng",L$1),0) Biên soạn: Ptm0412 Trang 74 / 151 PIVOT TABLE giaiphapexcel.com VI. DASHBOARD VỚI PIVOT VÀ PIVOT CHART Ý NGHĨA DASHBOARD Dashboard là 1 màn hình tóm lược tình hình hoạt động kinh doanh của doanh nghiệp, là biểu hiện cho sức khỏe của doanh nghiệp. Người có nhu cầu theo dõi chỉ cần nhìn duy nhất 1 màn hình dashboard cũng có thể thấy các thông tin cần thiết: Doanh số trong ngày, doanh số hàng ngày theo tháng, cấu trúc doanh số theo nhóm hàng, ngành hàng, doanh số phân tích theo khu vực, … Đối với sản xuất, tiến độ thực hiện của các lệnh sản xuất, tỷ lệ sản phẩm hỏng, thống kê các loại lỗi trong sản xuất, … cũng là các mục tiêu theo dõi của cấp lãnh đạo Đối với hành chánh nhân sự sẽ có nhu cầu theo dõi nhân sự vào, ra, danh sách nhân viên đến hạn ký hợp đồng, danh sách sinh nhật. Dashboard phải được cập nhật thường xuyên theo thời gian thực, nghĩa là thêm 1 đơn hàng bán ra, thêm doanh số, thêm dòng tiền thu chi, … đều thể hiện ngay lên dashboard. Thiết kế dashboard là một nghệ thuật sao cho trên 1 màn hình hiển thị tất cả thông tin như trên theo yêu cầu quản lý của cấp lãnh đạo. Thực hiện trên Excel Với tất cả các bảng dữ liệu liên quan đến hoạt động công ty, với sức mạnh của Pivot table, Power Pivot kết hợp với minh họa bằng Pivot chart, điều khiển bằng slicer, timeline, người dùng có thể vận dụng thiết kế dashboard cho riêng mình, cho các cấp lãnh đạo khác nhau. Tham khảo: Biên soạn: Ptm0412 Trang 75 / 151 PIVOT TABLE Biên soạn: Ptm0412 giaiphapexcel.com Trang 76 / 151 PIVOT TABLE Biên soạn: Ptm0412 giaiphapexcel.com Trang 77 / 151 PIVOT TABLE giaiphapexcel.com CHƯƠNG 2: POWER PIVOT I. GIỚI THIỆU VÀ CÀI ĐẶT Giới thiệu PowerPivot là một công cụ phân tích dữ liệu và lập mô hình dữ liệu mạnh mẽ trong Excel. PowerPivot là một Add-in mà Microsoft bổ sung vào Excel kể từ phiên bản Excel 2010 và được nhúng trực tiếp trong Excel 2013 và 2016. PowerPivot được sử dụng cho việc thêm và tích hợp một lượng lớn dữ liệu vào trong bảng tính Excel. Các dữ liệu mà bạn thêm vào bảng tính được lưu trữ trực tiếp trong file Excel và nó là một nguồn cơ sở dữ liệu cho PowerPivot. Bạn có thể sử dụng cơ sở dữ liệu này trong Excel hoặc trong các báo cáo của công cụ Power View kèm theo. Trong Excel, bảng tính cung cấp cho ta nhiều tính năng giúp trình bày dữ liệu một cách trực quan và tương tác. Sử dụng các tính năng tạo báo cáo của PivotTable, bạn có thể dễ dàng tạo ra nhiều kiểu báo cáo phức tạp dựa trên nguồn dữ liệu phân tích phong phú mà PowerPivot mang lại. Lợi ích của PowerPivot Một không gian làm việc (workspace) riêng biệt được mở ra trong Excel và được dùng để nhập dữ liệu từ nhiều nguồn, tạo liên kết cho các bảng dữ liệu, làm phong phú và làm sạch dữ liệu. Workspace lưu trữ các dữ liệu trong file .xlsx và chia sẻ các dữ liệu này với các bảng tính Excel. Xử lý dữ liệu trực tiếp trong bộ nhớ máy tính thông qua bộ máy Analysis Services xVelocity (VertiPaq) và chuẩn bị sẵn sàng các dữ liệu cho các tính năng như PivotTables, PivotChart, PowerView. Một Tab PowerPivot được thêm vào Ribbon của Excel để bạn có thể truy xuất nhanh chóng đến các lệnh. PowerPivot cập nhật các công nghệ kết nối dữ liệu mới nhất AMO, ADOMD.NET, và các nhà cung cấp dịch vụ phân tích OLE DB. Do vậy tăng khả năng kết nối với rất nhiều nguồn dữ liệu khác nhau cho PowerPivot. Cài đặt Office 2007 không hỗ trợ Power Pivot. PowerPivot được thêm vào Excel 2010 dưới dạng một Add-Ins. Do vậy bạn cần tải chương trình cài đặt từ website Microsoft. Sau khi cài đặt thành công thì Add-Ins nó vào sẽ được thêm Tab PowerPivot trên Ribbon như hình sau: Link tải phần mềm: https://www.microsoft.com/en-us/download/details.aspx?id=29074 Lưu ý: Đọc các yêu cầu và hướng dẫn cài đặt phần mềm trước khi cài. Cơ bản là máy cần có Windows XP trở lên, .Net Framework 4.0 trở lên và Office 2010. Biên soạn: Ptm0412 Trang 78 / 151 PIVOT TABLE giaiphapexcel.com Khi cài đặt xong có thể Excel sẽ yêu cầu cài đặt MS SilverLight, với link download sẵn trên hộp thoại thông báo. Đối với Excel 2013 trở lên thì PowerPivot được nhúng kèm theo bộ Office nên bạn chỉ cần cài đặt đầy đủ thì Excel sẽ có thêm tính năng này. Vào File | Options | Add-Ins | Com Add-Ins: Sau khi OK thì ta được: Biên soạn: Ptm0412 Trang 79 / 151 PIVOT TABLE Biên soạn: Ptm0412 giaiphapexcel.com Trang 80 / 151 PIVOT TABLE II. giaiphapexcel.com DỮ LIỆU NGUỒN CHO POWER PIVOT DỮ LIỆU TỪ BẢNG TÍNH EXCEL Với bảng dữ liệu có sẵn trên excel cũng có thể đưa vào Power pivot với điều kiện cấu trúc dữ liệu chuẩn 1. Excel 2010 Với 1 bảng dữ liệu đã định dạng table, nhấn nút Create linked table trong tab Power Pivot Cửa sổ Power Pivot mở ra và 1 sheet mới đã được tạo 2. Excel 2016 Với một bảng dữ liệu chuẩn có sẵn và đã định dạng table, chỉ cần vào Power pivot nhấn nút Add to Data Model Cửa sổ Power Pivot mở ra và 1 sheet mới đã được tạo. DỮ LIỆU TỪ CƠ SỞ DỮ LIỆU KHÁC 1. Kết nối và lấy dữ liệu về Excel Cách này mỗi table sẽ chiếm 1 sheet dữ liệu. Thí dụ lấy dữ liệu từ Access. a) Excel 2010 - Nhấn nút from Access và chọn file Biên soạn: Ptm0412 Trang 81 / 151 PIVOT TABLE giaiphapexcel.com - Chọn table và ok
Biên soạn: Ptm0412 Trang 82 / 151 PIVOT TABLE giaiphapexcel.com - Chọn file, Chọn table và chọn load to - Đánh dấu Add to Data model Biên soạn: Ptm0412 Trang 83 / 151 PIVOT TABLE giaiphapexcel.com 2. Tùy chọn chỉ lấy một số cột Chỉ có trong 2016 và 365 Nếu muốn chỉ lấy 1 số cột cần dùng trong table thì không chọn load to mà chọn Transform data Cửa sổ Power Query Editor hiện ra: Nhấn nút Choose columns Biên soạn: Ptm0412 Trang 84 / 151 PIVOT TABLE giaiphapexcel.com Chọn các cột cần thiết và OK: Kết quả Biên soạn: Ptm0412 Trang 85 / 151 PIVOT TABLE giaiphapexcel.com 3. Tùy chọn chỉ kết nối không lưu xuống sheet Với dữ liệu ít hơn giới hạn dòng của bảng tính (1 triệu dòng) ta có thể lưu xuống bảng tính như mục 1. Nếu không, ta chỉ lưu trữ trên Power Pivot chứ không cần lưu xuống sheet. Thay đổi tùy chọn này như sau: Vào tab Data của Excel chọn get data\ query option: Chọn specify custom default load settings và đánh dấu Load to Data Model. Biên soạn: Ptm0412 Trang 86 / 151 PIVOT TABLE giaiphapexcel.com 4. Kết nối thông qua Power Pivot Cách này dữ liệu được lưu trong cửa sổ Power pivot, không lưu dưới các sheet. a) Excel 2010 - Nhấn nút Power Pivot window - Cửa sổ mới hiện ra, nhấn nút from database và chọn Access - Chọn file Access và đánh dấu chọn các table Biên soạn: Ptm0412 Trang 87 / 151 PIVOT TABLE giaiphapexcel.com - Kết quả:
Trang 88 / 151 PIVOT TABLE giaiphapexcel.com - Cửa sổ mới hiện ra, thực hiện tương tự 2010 Biên soạn: Ptm0412 Trang 89 / 151 PIVOT TABLE giaiphapexcel.com III. SỬ DỤNG POWER PIVOT LIÊN KẾT BẢNG DỮ LIỆU 1. Điều kiện để liên kết Các table dữ liệu phải có cấu trúc đúng chuẩn: a) Yêu cầu - - Dữ liệu phải được tổ chức để khi truy xuất dữ liệu bằng các hàm dò tìm hoặc phương thức dò tìm không bị lỗi không tìm thấy Dữ liệu phải được tổ chức sao cho sau khi nhập liệu, không có những thông tin trùng lắp. Như vậy file dữ liệu của ta không bị phình to vì chứa thông tin thừa. Các báo cáo sẽ nhặt thông tin cần thiết từ các bảng dữ liệu tương ứng, không cần phải băn khoăn khi nhìn đâu cũng thấy cùng 1 thông tin. Dữ liệu phải được tổ chức để bảo đảm tính duy nhất của 1 số trường dữ liệu nhập vào, thí dụ như tránh việc nhập tên khác nhau cho cùng 1 mặt hàng. Dữ liệu phải được tổ chức để bảo đảm tính ràng buộc tham chiếu cho 1 trường dữ liệu, thí dụ như tránh việc nhập kho 1 mặt hàng chưa có trong danh mục. Dữ liệu phải được tổ chức để ràng buộc tính “yêu cầu bắt buộc nhập” đối với 1 số trường dữ liệu khi nhập liệu. Yêu cầu này (“không nhập thiếu dữ liệu”) bổ sung cho yêu cầu thứ 2 là “không nhập dư dữ liệu”.
- - - Các bảng dữ liệu gồm các trường dữ liệu, tên trường nên ngắn gọn, không nên có khoảng cách và không có dấu tiếng Việt. Nếu làm trong Excel thì tên trường phải được đặt ở dòng 1 và nguyên bảng không có merge cell, không có dòng trống giữa các dòng dữ liệu. Các bảng danh mục phải có 1 trường mã và làm trường khóa, trường này không có dữ liệu trùng. Mỗi mã là duy nhất trong toàn bảng. Các thông tin phụ chỉ thể hiện 1 lần trong 1 bảng, khi cần lập báo cáo sẽ dùng các biện pháp dò tìm dựa trên mã để thể hiện. Các bảng dữ liệu phát sinh khi có liên quan đến danh mục, chỉ nên dùng mã trong danh mục, không dùng tên hay thông tin phụ. Vì mã là duy nhất và có thể dùng biện pháp này nọ để chọn trong danh sách, nếu dùng tên thì tên có thể trùng và nguy cơ gõ sai là nhiều hơn. Ngoài ra điều quan trọng là chỉ cần 1 trường mã, không cần các trường phụ kèm theo. Nguyên tắc là dữ liệu càng gọn càng tốt, không chứa thông tin trùng lấp. Các bảng dữ liệu phát sinh cần có những mối quan hệ ràng buộc với những bảng mã, để tránh trường hợp sử dụng những mã không có trong danh mục Một số trường có tính bắt buộc nhập thí dụ như trường mã hàng hóa trong phiếu xuất kho, nhập kho, hoặc trường số chứng từ trong mọi loại chứng từ, phải thiết lập thuộc tính “bắt buộc nhập”. 2. Tạo liên kết - Mở cửa sổ Power pivot, nhấn nút view Diagram Biên soạn: Ptm0412 Trang 90 / 151 PIVOT TABLE - giaiphapexcel.com Dùng chuột kéo các trường liên kết từ bảng này qua bảng kia THÊM TRƯỜNG TÍNH TOÁN CHO DỮ LIỆU 1. Thêm trên bảng tính Các bảng dữ liệu trên bảng tính (đã tạo data model) có thể thêm trường tính toán. Sau khi thêm, vào cửa sổ Power Pivot nhấn nút refresh để cập nhật cột mới vào. 2. Thêm trong Power pivot - Trên cột Add Column gõ công thức như trong excel - Sửa tên cột Biên soạn: Ptm0412 Trang 91 / 151 PIVOT TABLE giaiphapexcel.com PIVOT TABLE TỪ NHIỀU BẢNG 1. Tạo Pivot table Nhấn nút Pivot table như hình Thao tác tạo Pivot table như bình thường. Sự khác biệt là có thể chọn field từ nhiều table hơn: Biên soạn: Ptm0412 Trang 92 / 151 PIVOT TABLE giaiphapexcel.com 2. Các thủ thuật chọn field Các bảng dữ liệu phát sinh chỉ chứa mã trong các bảng danh mục. Nhờ vào sự liên kết có thể lấy ra được tên trong bảng danh mục tương ứng - Có thể lấy tên khách hàng trong bảng danh mục khách hàng thông qua mã khách hàng trên đơn hàng Biên soạn: Ptm0412 Trang 93 / 151 PIVOT TABLE Biên soạn: Ptm0412 giaiphapexcel.com Trang 94 / 151 PIVOT TABLE giaiphapexcel.com - Có thể lấy tên mặt hàng trong danh mục mặt hàng thông qua mã mặt hàng trên đơn hàng - Tương tự, có thể lấy tên nhân viên bán hàng trong danh mục nhân viên, tên nhóm hàng trong danh mục nhóm hàng Biên soạn: Ptm0412 Trang 95 / 151 PIVOT TABLE giaiphapexcel.com 3. Kết hợp Pivot table và Pivot chart Để tăng tính minh họa cho bảng tính, có thể kết hợp vừa pivot table, vừa pivot chart cộng thêm 1 slicer điều khiển, báo cáo từ pivot table sẽ trở nên sinh động. Biên soạn: Ptm0412 Trang 96 / 151 PIVOT TABLE giaiphapexcel.com IV. POWER QUERY GIỚI THIỆU 1. Giới thiệu Power query là 1 tính năng rút trích dữ liệu (truy vấn) từ 1 hoặc nhiều bảng dữ liệu có liên kết với nhau bằng chức năng PowerPivot của 2016 trở lên. Power query có thể liên kết tới nhiều loại cơ sở dữ liệu khác nhau như: Table có sẵn trong file Excel hiện hành, file Excel khác đang đóng, file Access, các dạng lưu trữ database: MSFoxpro, Oracle, MySQL, SQLServer, PosgreSQL, … Để tiết kiệm bảng tính, Power query có thể chỉ liên kết dữ liệu mà không cần lưu trữ dữ liệu xuống bảng tính. Việc này còn có lợi khi số dòng dữ liệu sau khi truy vấn vượt quá giới hạn dòng của Excel (1 triệu dòng có khi cũng không đủ) 2. Cài đặt Office 2016 Pro và 365 có tích hợp sẵn. Nếu 2010, 2013 và 2016 không có sẵn thì vào link sau để tải, gồm 2 bản 32 bit và 64 bit: https://www.microsoft.com/en-us/download/confirmation.aspx?id=39379 CHỨC NĂNG UNPIVOT 1. Ý nghĩa Chức năng này chuyển dữ liệu từ dạng nhiều cột thành ít cột nhiều dòng, giống như chuyển 1 pivot table dạng phân tích thành dạng dữ liệu chuẩn ban đầu Biên soạn: Ptm0412 Trang 97 / 151 PIVOT TABLE giaiphapexcel.com Thí dụ dữ liệu đang có như sau: Khách hàng MHàng 01 MHàng 02 MHàng 03 190 114 105 196 187 26 125 254 2000 131 178 135 150 180 0 45 32 1800 178 141 184 124 109 184 214 14 2000 a v d f d v k j f 2. Thực hiện - Chọn bảng dữ liệu, ở tab Data, nhấn nút Get & Transform Data\ from Table/ Range - Cửa sổ Power Query Editor mở ra - Tô chọn 3 cột mặt hàng, Tab Transform, nhấn nút Unpivot columns Biên soạn: Ptm0412 Trang 98 / 151 PIVOT TABLE giaiphapexcel.com - Kết quả - Đóng cửa sổ và chọn Lưu, hoặc về tab Home nhấn nút Close and load Biên soạn: Ptm0412 Trang 99 / 151 PIVOT TABLE giaiphapexcel.com Power query sẽ tạo 1 bảng dữ liệu xuống sheet với sự liên kết đến bảng ban đầu - Nếu nhập liệu bảng gốc thêm 1 dòng như dòng 11 hình sau: - Qua bảng kết quả lúc trước refresh lại sẽ thấy được cập nhật thêm: Biên soạn: Ptm0412 Trang 100 / 151 PIVOT TABLE Biên soạn: Ptm0412 giaiphapexcel.com Trang 101 / 151 PIVOT TABLE giaiphapexcel.com QUERY TỪ DỮ LIỆU TỪ BẢNG TÍNH 1. Điều kiện để tạo query - Dữ liệu trên trang tính phải đúng chuẩn Database (CSDL): Tiêu đề, dữ liệu, Nếu là bảng danh mục, bảo đảm có cột mã và là những mã duy nhất Nếu là bảng dữ liệu và sử dụng mã trong danh mục, phải có sự đồng nhất và liên kết được giữa mã trong danh mục và mã trong dữ liệu Nếu 2 bảng dữ liệu có quan hệ cha con, phải bảo đảm ID cha phải duy nhất và bảng con có chứa ID cha Tất cả bảng dữ liệu phải format as table để query có thể cập nhật khi dữ liệu tăng lên 2. Tạo query từ 1 bảng Tất cả các bảng dữ liệu trên sheet đều có thể tạo query dù là danh mục hay dữ liệu cha, con. Thí dụ ta có bảng danh mục nhân viên bán hàng đã tạo thành table: EmployeeID LastName 1 Davolio 2 Fuller 3 Leverling 4 Peacock 5 Buchanan 6 Suyama 7 King 8 Callahan 9 Dodsworth FirstName Nancy Andrew Janet Margaret Steven Michael Robert Laura Anne FullName Title TitleOfCourtesy Nancy DavolioSales Representative Ms. Andrew FullerVice President, Dr. Sales Janet Leverling Sales Representative Ms. Margaret Peacock Sales Representative Mrs. Steven Buchanan Sales Manager Mr. Michael Suyama Sales Representative Mr. Robert King Sales Representative Mr. Laura CallahanInside Sales Ms. Coordinator Anne Dodsworth Sales Representative Ms. BirthDate 08/12/48 0:00 19/02/52 0:00 30/08/63 0:00 19/09/37 0:00 04/03/55 0:00 02/07/63 0:00 29/05/60 0:00 09/01/58 0:00 27/01/66 0:00 HireDate Address City Region 01/05/92 0:00 507 - 20th Ave. Seattle E. WA 14/08/92 0:00 908 W. Capital Tacoma Way WA 01/04/92 0:00 722 Moss Bay Kirkland Blvd. WA 03/05/93 0:00 4110 Old Redmond Redmond Rd.WA 17/10/93 0:00 14 Garrett Hill London 17/10/93 0:00 Coventry House London 02/01/94 0:00 Edgeham Hollow London 05/03/94 0:00 4726 - 11th Ave. Seattle N.E. WA 15/11/94 0:00 7 Houndstooth London Rd. PostalCode Country 98122 USA 98401 USA 98033 USA 98052 USA SW1 8JR UK EC2 7JR UK RG1 9SP UK 98105 USA WG2 7LT UK - Từ tab data của ribbon, chọn Get & transform data from table or range - Cửa sổ Power query editor hiện ra và query đã được tạo: HomePhone (206) 555-9857 (206) 555-9482 (206) 555-3412 (206) 555-8122 (71) 555-4848 (71) 555-7773 (71) 555-5598 (206) 555-1189 (71) 555-4444 Ta có thể nhấn Close & load để quay về hoặc có thể hiệu chỉnh query trước Biên soạn: Ptm0412 Trang 102 / 151 PIVOT TABLE giaiphapexcel.com HIỆU CHỈNH QUERY 1. Sao chép, xóa Query Mở task pane Query and connection, nhấn chuột phải vào query trên task pane và chọn Duplicate, sau đó có thể hiệu chỉnh và sửa tên. Nếu muốn xóa thì nhấn delete Hoặc ở cửa sổ Query Editor, nhấn chuột phải vào query muốn sao chép và chọn Duplicate. Nếu muốn xóa thì nhấn Delete Biên soạn: Ptm0412 Trang 103 / 151 PIVOT TABLE giaiphapexcel.com 2. Chọn cột - Chọn các cột muốn sử dụng, loại bỏ các cột không cần thiết: Nhấn nút Choose column - Đánh dấu các cột cần dùng trong hộp thoại sau: Biên soạn: Ptm0412 Trang 104 / 151 PIVOT TABLE - giaiphapexcel.com Kết quả: 3. Xóa cột Chọn cột cần xóa và nhấn nút Remove column 4. Xóa dòng - Nếu là danh mục và cần bảo đảm không trùng thì nhấn nút remove rows và chọn remove duplicate Nếu dữ liệu có dòng trống muốn xóa thì chọn remove blank rows Nếu dữ liệu lỗi và muốn xóa thì chọn remove errors Biên soạn: Ptm0412 Trang 105 / 151 PIVOT TABLE giaiphapexcel.com 5. Tách 1 cột thành nhiều cột Tô chọn cột cần tách và nhấn Split column - Tách cột dựa vào dấu hiệu phân cách: chọn By Delimiter và chọn ký tự làm dấu hiệu tách có thể tách thành nhiều dòng hay thành nhiều cột: Biên soạn: Ptm0412 Trang 106 / 151 PIVOT TABLE giaiphapexcel.com Kết quả tách thành nhiều cột, delimiter là khoảng trắng: Kết qủa tách thành nhiều dòng, delimiter là khoảng trắng 6. Thêm cột tính toán ngoài dữ liệu - Có thể thêm cột tính toán, cột dữ liệu đặc biệt cho query: mở tab Add column và nhấn custom column - Điền dữ liệu mong muốn vào hộp thoại, có thể là 1 công thức tính toán: Biên soạn: Ptm0412 Trang 107 / 151 PIVOT TABLE giaiphapexcel.com Kết quả: 7. Thêm cột chứa giá trị theo điều kiện Thêm cột chứa giá trị theo điều kiện từ cột khác (tương tự hàm if) Vào tab Add column và nhấn Conditional column Điền tên cột vào New column name, điền điều kiện vào If, giá trị mong muốn vào Output, giá trị nếu không thỏa điền vào Else. Giả sử ta muốn nếu nơi nhận hàng là USA thì có giá trị là Domestics (nội địa), ngược lại là xuất khẩu (Export) Biên soạn: Ptm0412 Trang 108 / 151 PIVOT TABLE giaiphapexcel.com Nếu muốn giá trị thứ 2 theo điều kiện 2 thì nhấn Add rule và điền tiếp điều kiện 2 và giá trị 2 Kết quả: Tùy chọn điều kiện: Điều kiện có các tùy chọn như sau: - Các tùy chọn toán tử so sánh: Biên soạn: Ptm0412 Trang 109 / 151 PIVOT TABLE - giaiphapexcel.com Tùy chọn đối tượng so sánh: so sánh với giá trị hoặc so sánh với 1 cột khác Nếu chọn Select a column thì xổ xuống chọn cột: - Tùy chọn giá trị nhận nếu thỏa (Output) là giá trị điền tay hay giá trị của 1 cột: - Tùy chọn giá trị nhận nếu không thỏa (Else) cũng tương tự Biên soạn: Ptm0412 Trang 110 / 151 PIVOT TABLE giaiphapexcel.com 8. Group by query a) Tính năng group by Group By là nhóm dữ liệu theo những cột này vá tính toán các cột khác. Các tính toán có thể là Sum, Count, … Tất nhiên nếu tạo ra pivot table thì dùng chứng năng tính toán của pivot table, nhưng nếu muốn tạo ra 1 sheet dữ liệu tính tổng sẵn với dữ liệu đã được rút gọn thì vẫn hay hơn, vì có thể dùng vào những việc khác nữa. Trường hợp muốn tính tổng 1 vài trường dữ liệu thì các cột còn lại cũng nên cất bớt, chỉ để lại 1 số trường rất cần thiết để group by mà thôi b) Thực hiện - Mở cửa sổ Query Editor Click chọn query muốn group by Xóa 1 số cột không cần thiết Tô chọn các trường cần group Nhấn nút Group By - Trên hộp thoại hiện ra ta thấy đã chọn các trường group, cần chọn thêm các trường tính toán và phương thức tính. Có thể nhấn Add Aggregation để thêm 1 trường tính toán, sau đó nhấn OK: Biên soạn: Ptm0412 Trang 111 / 151 PIVOT TABLE giaiphapexcel.com Kết quả dữ liệu đã được tính tổng và rút gọn các dòng dữ liệu, mỗi khách hàng, mỗi ngày chỉ còn 1 dòng. Tổng số dòng giảm xuống đáng kể Biên soạn: Ptm0412 Trang 112 / 151 PIVOT TABLE giaiphapexcel.com 9. Bỏ thao tác hiệu chỉnh đã thực hiện Mỗi thao tác hiệu chỉnh như trên đã tự động tạo 1 dòng step trên taskpane Query setting. (Có thể sửa tên query tại ô Name) Muốn xem lại từng thao tác thì click chọn dòng muốn xem. Muốn gỡ bỏ 1 thao tác thì nhấn nút chữ thập đỏ trên thao tác: Cuối cùng nhấn Load and close MERGE QUERY TỪ NHIỀU QUERY Khi đã tạo được những query từ những table đơn lẻ, có thể tạo query phức hợp từ 2 query, thí dụ từ danh mục và dữ liệu, từ cha và con bằng phương thức merge Biên soạn: Ptm0412 Trang 113 / 151 PIVOT TABLE giaiphapexcel.com 1. Mở cửa sổ Query Editor Mở cửa sổ query editor sẽ thấy các query đã tạo nằm bên trái, chọn 1 trong các query để thao tác 2. Merge các query - Nhấn nút merge queries, chọn Merge as new - xuất hiện hộp thoại: - Chọn query thứ 2 Biên soạn: Ptm0412 Trang 114 / 151 PIVOT TABLE giaiphapexcel.com - Chọn cột liên kết ở trên và ở dưới (liên kết cha và con, liên kết mã danh mục và mã dữ liệu). Nếu chọn đúng và dữ liệu hợp lệ sẽ thông báo thành công - Sau khi OK có thể merge thêm lần 2, lần 3 với query khác nữa Biên soạn: Ptm0412 Trang 115 / 151 PIVOT TABLE giaiphapexcel.com 3. Kết thúc merge và sử dụng Nhấn nút load and close. Sau đó có thể sử dụng query để tạo báo cáo, chẳng hạn tạo 1 pivot table: Biên soạn: Ptm0412 Trang 116 / 151 PIVOT TABLE giaiphapexcel.com APPEND QUERY Ở chương I, phần II Pivot tabe, mục D dữ liệu cho Pivot table từ nhiều nguồn cho thấy có thể lấy dữ liệu từ 2 hoặc nhiều bảng dữ liệu làm nguồn cho Pivot table. Tuy nhiên cách làm này có những hạn chế của nó, ngoài ra nếu dữ liệu các nguồn lỡ không có cùng cấu trúc cột thì không xài được. Thí dụ với 2 bảng dữ liệu 1 nhập 1 xuất ở 2 sheet khác nhau như sau, thậm chí không cùng cấu trúc: số lượng cột và tên cột: - Bảng dữ liệu nhập hàng: - Bảng dữ liệu xuất hàng Bây giờ với Power query ta có thể khéo léo tạo những query phù hợp, thậm chí có thể nối 2 hoặc nhiều bảng dữ liệu như thế với nhau thành 1 bảng duy nhất a) Tạo các query từ các bảng Với thí dụ như trên, khi tạo query cho 2 bảng cần đồng nhất cấu trúc cột cho 2 query, và nếu cần nối 2 query thành 1 thì sẽ cần 1 cột phân biệt dữ liệu Nếu các bảng dữ liệu lấy từ các chi nhánh, cửa hàng sẽ cần 1 cột phân biệt (dùng mã chi nhánh, cửa hàng) Nếu các bảng dữ liệu lấy từ các phân hệ hoạt động khác nhau như kho (nhập xuất), kế toán (thu chi), vật tư (mua), kinh doanh (bán) sẽ cần 1 cột phân biệt cho các hoạt động này Với thí dụ trên: - Query từ bảng nhập: Lấy 1 số cột cần thiết, đổi tên chuẩn lại và thêm 1 cột (Add Column) có giá trị = “In” đặt tên là Type Biên soạn: Ptm0412 Trang 117 / 151 PIVOT TABLE giaiphapexcel.com - Query từ bảng xuất: Lưu ý lấy đúng số lượng cột, đặt tên cột giống bảng 1, thêm cột Type giống bảng 1 và có giá trị “Out” - Kết quả: Biên soạn: Ptm0412 Trang 118 / 151 PIVOT TABLE giaiphapexcel.com Nếu có nhiều bảng cũng làm tuần tự như vậy b) Tạo Append query (nối các bảng lại thành 1) - Mở cửa sổ Query editor, chọn 1 bảng sau đó nhấn nút Append, chọn append as new - Nếu 2 bảng thì chọn thêm bảng thứ 2: Nếu nhiều bảng thì chọn Three or more và chọn query bên trái nhấn Add để thêm vào bên phải: Sau khi ok ta có kết quả: Biên soạn: Ptm0412 Trang 119 / 151 PIVOT TABLE giaiphapexcel.com
Biên soạn: Ptm0412 Trang 120 / 151 PIVOT TABLE giaiphapexcel.com QUERY DỮ LIỆU TỪ FILE EXCEL KHÁC 1. Kết nối file Excel khác Trên menu Data chọn Get external data from file\ from workbook: Chọn file excel chứa dữ liệu Đánh dấu các table hoặc sheet dữ liệu cần lấy và nhấn Transform Biên soạn: Ptm0412 Trang 121 / 151 PIVOT TABLE giaiphapexcel.com 2. Tạo truy vấn từ các query Thao tác edit query: merge, append giống như lấy dữ liệu từ file hiện hành COMBINE QUERY TỪ CÁC FILE EXCEL TRONG FOLDER 1. Ý nghĩa combine và điều kiện để combine a) Ý nghĩa Chức năng này lấy dữ liệu từ tất cả file excel trong 1 thư mục, nối chúng lại với nhau giống như 1 Append query mà không cần mở file cũng như không cần động tác tạo và append nhiều query thành phần b) Điều kiện - - Tất cả các file chỉ có 1 sheet, tên sheet giống nhau Cấu trúc dữ liệu giống nhau: số lượng cột, tên cột, thứ tự cột, loại dữ liệu từng cột Nếu định dạng table thì các table phải có cùng tên Nếu dữ liệu không bắt đầu từ dòng 1 thì tất cả các file cùng bắt đầu từ cùng 1 dòng (thí dụ dòng 3), phía trên dữ liệu không được có bất kỳ thứ gì, hoặc có ít nhất 1 dòng trống bên trên dòng tiêu đề Không được có bất kỳ dữ liệu dư thừa nào ở cột khác, dòng khác, nói chung là 4 bên của bảng dữ liệu chính.
Tất cả file đã tạo table, table trùng tên Các table bắt đầu từ các dòng khác nhau, nhưng có 1 dòng trống ngay trên table Một hoặc nhiều table có dư 1 số cột bên phải dữ liệu chuẩn bị combine Có dữ liệu thừa bên ngoài table nhưng cách ít nhất 1 cột hoặc 1 dòng 2. Thực hiện - Từ menu Data, nhấn Get Data\ From file\ from folder Biên soạn: Ptm0412 Trang 122 / 151 PIVOT TABLE giaiphapexcel.com - Chọn đường dẫn đến thư mục - Nhấn chọn combine and edit - Chọn 1 trong các file dùng làm mẫu và ok. Power query dùng file mẫu này để ép các file khác theo mẫu. Biên soạn: Ptm0412 Trang 123 / 151 PIVOT TABLE giaiphapexcel.com - Nếu thành công thì sẽ tự mở ra cửa sổ query editor với kết quả cuối như sau, nếu không thành công thì báo lỗi và phải xem lại điều kiện bên trên - Nhấn Close and load để kết thúc combine Mở cửa sổ Power Pivot để xem kết quả Biên soạn: Ptm0412 Trang 124 / 151 PIVOT TABLE giaiphapexcel.com 3. Đánh giá Chức năng combine query đặc biệt tiện lợi đối với những file trích xuất từ phần mềm, có cùng cấu trúc sẵn mười file như một. Các file có thể là dữ liệu của các tháng, của các chi nhánh, của các cửa hàng, … Và tổng dữ liệu của chúng vượt quá số dòng của Excel (1 triệu dòng) Tuy nhiên với những ràng buộc và điều kiện như trên, chức năng này sẽ khó xài đối với dữ liệu do người dùng tự nhập hàng ngày trên Excel. Chỉ cần sai 1 chút về cấu trúc, về kiểu dữ liệu, dư dữ liệu bất kỳ bên ngoài bảng đều bị lỗi không thực hiện được. Trường hợp này thì sử dụng chức năng Append sẽ tốt hơn, kể cả cấu trúc dữ liệu có hơi khác. TRUY VẤN BẰNG SQL VÀO DỮ LIỆU ACCESS 1. Kết nối dữ liệu bằng truy vấn SQL - Mở cửa sổ Power Pivot, trên tab Home nhấn From database và chọn Access Biên soạn: Ptm0412 Trang 125 / 151 PIVOT TABLE - Nhấn Browse chọn file và nhấn Open - Nhấn Next và đánh dấu chọn Write a Query Biên soạn: Ptm0412 giaiphapexcel.com Trang 126 / 151 PIVOT TABLE - giaiphapexcel.com Nhấn next và viết câu SQL vào khung hộp thoại kế tiếp. Có thể nhấn nút Validate để kiểm tra lỗi của câu SQL Câu SQL mẫu cho file NorthWind: Biên soạn: Ptm0412 Trang 127 / 151 PIVOT TABLE giaiphapexcel.com Select po.customerid, po.orderdate, Pod.productid, Pod.unitprice, pod.quantity, Pod.unitprice * pod.quantity as Amount, Pr.ProductName From (Orders PO Inner Join [Order details] Pod On PO.OrderId = Pod.OrderID) Inner join Products Pr On Pod.ProductID = Pr.ProductID Where Year(Po.orderdate)=1997 - Nhấn Finish và xác thực thành công Kết quả: Biên soạn: Ptm0412 Trang 128 / 151 PIVOT TABLE giaiphapexcel.com Lúc này ta có thể thực hiện Pivot table cho query như các table riêng lẻ, hoặc liên kết với 1 hoặc nhiều table khác như phần III (Sử dụng Power Pivot) Thí dụ query vừa tạo và 3 table độc lập khác: 2. Hiệu chỉnh SQL của query Khi cần hiệu chỉnh câu SQL của query, nhấn nút Table Property trong tab design của cửa sổ Power Pivot: Và chỉnh sửa trong Edit table property: Biên soạn: Ptm0412 Trang 129 / 151 PIVOT TABLE giaiphapexcel.com TRUY VẤN BẰNG SQL VÀO DỮ LIỆU TỪ CƠ SỞ DỮ LIỆU KHÁC Dữ liệu khác bao gồm các cơ sở dữ liệu Excel có sẵn driver liên kết như MySQL, SQLServer, Oracle. Với 1 số cơ sở dữ liệu như FoxPro, PosgreSQL thì phải cài đặt thêm. 1. Kết nối dữ liệu bằng câu truy vấn SQL Vào menu Data của Excel, nhấn Get data và chọn cơ sở dữ liệu: Biên soạn: Ptm0412 Trang 130 / 151 PIVOT TABLE - Điền tên server và tên Database - Nhấn mở Advanced Option và gõ câu SQL vào khung Biên soạn: Ptm0412 giaiphapexcel.com Trang 131 / 151 PIVOT TABLE giaiphapexcel.com - Nhấn OK và điền User name và password - Nhấn load để tạo sheet cho Power Pivot hoặc nhấn Transform data để hiệu chỉnh dòng, cột Biên soạn: Ptm0412 Trang 132 / 151 PIVOT TABLE - giaiphapexcel.com Kết quả: 2. Hiệu chỉnh SQL của query a) Mở cửa sổ Power Query Editor - Từ Menu Data, nhấn Get data và chọn Launch Power Query Editor Biên soạn: Ptm0412 Trang 133 / 151 PIVOT TABLE - Ở tab Data của Excel, nhấn query & Connection - Để chuột vào query cho xuất hiện nội dung sau và chọn Edit Biên soạn: Ptm0412 giaiphapexcel.com Trang 134 / 151 PIVOT TABLE - giaiphapexcel.com Hoặc nhấn chuột phải vào query và chọn Edit
Mở ra cửa sổ Power Query Editor: Biên soạn: Ptm0412 Trang 135 / 151 PIVOT TABLE - V. giaiphapexcel.com Ở tab View, nhấn nút Advanced Editor và sửa câu SQL trong dấu ngoặc vuông ([]) POWER VIEW Giới thiệu và cài đặt 1. Giới thiệu Power view là 1 phần của power Pivot khi cài đặt Office 2016 hoặc cài đặt gói Power Pivot cho 2010. Power view là những trang chèn thêm chứa dữ liệu và/ hoặc biểu đồ minh họa thông minh, có thể điều khiển sinh động và trực quan. Nếu thiết kế khéo, người dùng có thể coi nó là những dashboard trực quan và đẹp mắt. Biên soạn: Ptm0412 Trang 136 / 151 PIVOT TABLE giaiphapexcel.com KHông những Power view kết nối tới dữ liệu trên bảng tính mà còn kết nối tới dữ liệu Power pivot, và kết nối tới bản đồ thế giới của Bing, và bản đồ tự động zoom khi người dùng lọc dữ liệu trên Power view. 2. Fix lỗi khi không mở được Power view Đôi khi sau khi cài đặt không thấy nút Insert Power view, cũng như không thấy xuất hiện tab Power view để thao tác. Những file excel đã tạo Power view trước đây không mở xem được mà xuất hiện thông báo như sau: Nếu máy chưa có Silverlight thì office đã yêu cầu cài đặt ngay khi vừa mở excel lần đầu tiên sau khi cài đặt. Tuy nhiên có 1 giai đoạn Silverlight không an toàn nên bị khóa. Trường hợp này thì fix như sau: - - Vào trang web sau tải file EnableSilverLight.reg về chạy để chấp nhận chạy Silverlight: https://gallery.technet.microsoft.com/scriptcenter/Registry-keys-toreenable-7cd9f723 Nếu chạy xong Excel yêu cầu cập nhật Siverlight thì cập nhật phiên bản mới Nếu tab insert không thấy nút Insert power view thì làm theo hình: Vào Option\ Customize ribbon: Và ta có nút insert như sau: Biên soạn: Ptm0412 Trang 137 / 151 PIVOT TABLE giaiphapexcel.com THỰC HIỆN 1. Tạo mới 1 power view sheet a) Tạo power view sheet từ dữ liệu bảng tính Dữ liệu bảng tính phải đúng chuẩn, tốt nhất là đã tạo thành table. - Tô khối vùng dữ liệu City Hà nội Bắc Ninh Nam Định Ninh bình Hải Phòng Hà Tây Thanh hóa Hà nội Bắc Ninh Nam Định Ninh bình Hải Phòng Hà Tây Thanh hóa Hà nội Bắc Ninh Nam Định Ninh bình Hải Phòng Hà Tây Thanh hóa - Product category Revenue Food 12,500 Food 14,500 Food 17,000 Food 6,000 Food 8,000 Food 11,000 Food 14,000 beverage 10,000 beverage 13,000 beverage 13,000 beverage 5,000 beverage 9,000 beverage 9,000 beverage 15,000 Beer 8,000 Beer 13,000 Beer 12,000 Beer 9,000 Beer 11,000 Beer 6,000 Beer 8,000 Nhấn nút Insert power view, kết quả tạo 1 sheet mới và đã tự động tạo 1 table dữ liệu trên vùng 1 Biên soạn: Ptm0412 Trang 138 / 151 PIVOT TABLE giaiphapexcel.com - Vùng 1: là vùng sẽ hiển thị các dữ liệu, biểu đồ, công cụ lọc Vùng 2: là vùng Filter dùng để lọc tay nếu như không sử dụng slicer hay công cụ - lọc. Vùng này có thể đóng lại bằng nút hoặc thu nhỏ bằng nút mũi tên Vùng 3 liệt kê các trường dữ liệu của table và đã kéo vào trong vùng Fields Đồng thời trên ribbon cũng xuất hiện tab mới Power view:
- Vùng 1: là vùng sẽ hiển thị các dữ liệu, biểu đồ, công cụ lọc Vùng 2: là vùng Filter dùng để lọc tay nếu như không sử dụng slicer hay công cụ - lọc. Vùng này có thể đóng lại bằng nút hoặc thu nhỏ bằng nút mũi tên Vùng 3 liệt kê các table, query dữ liệu đã tạo thành Power Pivot table, các table này có thể đã tạo liên kết (relation) sẵn Biên soạn: Ptm0412 Trang 139 / 151 PIVOT TABLE giaiphapexcel.com 2. Tạo biểu đồ a) Tạo biểu đồ Từ bảng dữ liệu kéo các trường cần thiết thả vào Fields ta sẽ có bảng như hình: Chọn chuyển thành biểu đồ bằng cách nhấn chuột chọn vào trong bảng và nhấn nút chọn 1 trong các biểu đồ: Kết quả như hình sau, các dấu khoanh màu xanh là node để kéo tăng giảm kích thước: Lúc này taskpane chứa thông tin về biểu đồ: Là các trường thông tin Dữ liệu, trục, ký hiệu biểu đồ: Biên soạn: Ptm0412 Trang 140 / 151 PIVOT TABLE giaiphapexcel.com
Khi click chọn 1 cột, 1 bar, nói chung 1 giá trị trên biểu đồ thì mục chọn sáng lên và các mục khác mờ đi Khi rê chuột lên 1 giá trị biểu đồ thì hiển thị thông tin của giá trị đó 3. Tạo thanh điều khiển lọc biểu đồ Kéo thả 1 trường muốn dùng để điều khiển biểu đồ vào ô TILE BY: Biên soạn: Ptm0412 Trang 141 / 151 PIVOT TABLE giaiphapexcel.com Kết quả có 1 thanh điều khiển lọc xuất hiện bên trên biểu đồ: Kết quả khi lọc bằng cách click chuột vào các nhóm hàng khác nhau của thanh điều khiển: Nếu kéo trường City vào để điều khiển thì sẽ có: Biên soạn: Ptm0412 Trang 142 / 151 PIVOT TABLE giaiphapexcel.com 4. Tạo slicer điều khiển a) Tạo khung điều khiển bằng dữ liệu Click vào chỗ trống của vùng 1 và kéo 1 trường dữ liệu bỏ vào Field
Kết quả bảng nhóm hàng được dùng để lọc biểu đồ: Biên soạn: Ptm0412 Trang 143 / 151 PIVOT TABLE giaiphapexcel.com Có thể nhấn control hoặc shift và click để chọn hiển thị nhiều hơn 1 nhóm hàng: Nhấn nút clear filter ở góc trên bên phải để chọn hết: 5. Tạo biểu đồ dạng map a) Điều kiện Dữ liệu muốn vẽ biểu đồ dạng map phải chứa thông tin quốc gia hoặc tỉnh, thành phố theo đúng bản đồ hành chính và quy ước quốc tế, đặc biệt là theo quy ước của Bing Biên soạn: Ptm0412 Trang 144 / 151 PIVOT TABLE giaiphapexcel.com Map vì Excel sẽ liên kết đến tọa độ địa lý của tỉnh thành phố/ quốc gia để lấy Bing map về hiển thị. Thí dụ Huế sẽ không được chấp nhận mà phải là “Thừa Thiên Huế”. Tương tự nước Anh phải là United Kingdom chứ không phải England. b) Thực hiện Thực hiện kéo thả các trường vào fields, nhưng khi chọn biểu đồ thì chọn dạng map: Kết quả xuất hiện biểu đồ bản đồ với các nút điều khiển bản đồ đặc trưng:
Biên soạn: Ptm0412 Trang 145 / 151 PIVOT TABLE giaiphapexcel.com Đặc điểm slicer: Nếu 2 hoặc nhiều biểu đồ cùng lấy dữ liệu từ 1 bảng thì slicer tự động điều khiển hết các biểu đồ này. Biên soạn: Ptm0412 Trang 146 / 151 PIVOT TABLE giaiphapexcel.com TRANG TRÍ POWER VIEW 1. Định dạng nền Sử dụng nhóm nút lệnh sau: - Chọn theme màu - Chọn Font và cỡ chữ Chọn màu nền: Biên soạn: Ptm0412 Trang 147 / 151 PIVOT TABLE - giaiphapexcel.com Chọn ảnh nền 2. Định dạng bản đồ Dùng nút lệnh Map background Thí dụ Road map background Biên soạn: Ptm0412 Trang 148 / 151 PIVOT TABLE giaiphapexcel.com 3. Hiển thị các thành phần biểu đồ a) Hiển thị biểu tượng biểu đồ
4. Vẽ thêm và sắp xếp Power View sheet a) Vẽ cái gì và vẽ bao nhiêu Tùy nhu cầu quản trị, nhu cầu xem thông tin của người quản lý, sẽ thiết kế bao nhiêu biểu đồ, dạng gì, dữ liệu nào. Vẽ dạng gì cần phải chọn cho phù hợp với mục đích thuyết minh của biểu đồ Có thể vẽ thêm biểu đồ các loại khác nhau, dữ liệu khác nhau trên 1 sheet Có thể vẽ nhiều sheet cho mục đích quản lý khác nhau: 1 sheet doanh thu doanh số, lợi nhuận, 1 sheet chỉ tiêu và thực hiện của nhân viên, thông tin nhân sự, v.v… b) Sắp xếp trình bày Tùy thuộc vào khiếu thẩm mỹ của từng người, nhưng cần nhất là phải gọn gàng, thứ tự và khoa học. |
