Kết cấu dữ liệu & báo cáo trong Excel 2013

Chia sẻ: Hoài Capricorn | Ngày: | Loại File: PDF | Số trang:250

Thêm vào BST

Báo xấu

159
lượt xem 43
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

(BQ) Với kết cấu nội dung gồm 5 phần, tài liệu Dữ liệu & báo cáo trong Excel 2013 giới thiệu đến các bạn cách làm việc với dữ liệu, PivotTable và PivotChart, PowerPivot, báo cáo trong Excel 2013. Với các bạn chuyên ngành Công nghệ thông tin thì đây là tài liệu tham khảo hữu ích.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Kết cấu dữ liệu & báo cáo trong Excel 2013

Trần Thanh Phong & Phạm Thành Mỹ Tài liệu cần thiết cho người sử dụng bảng tính DỮ LI ỆU & BÁO C ÁO T R O N G E XC E L 2 0 1 3 Sort- AutoFilter - Advanced Filter - PivotTable - PowerPivot - Power View 07-2015
Dữ liệu & báo cáo trong Excel 2013 Giải Pháp Excel DỮ LIỆU & BÁO CÁO TRONG MICROSOFT EXCEL 2013 Sách này được Giải Pháp Excel giữ bản quyền, chúng tôi tặng thành viên và khuyến khích việc truyền đạt giới thiệu đến người sử dụng qua các hình thức sao chép nguyên bản. Chúng tôi nghiêm cấm tất cả các hành vi sử dụng việc phát tán sách để trục lợi, hoặc chỉnh sửa nội dung sách.
Giải Pháp Excel Các Nhà Tài Trợ Ban quản trị Giải Pháp Excel xin cảm ơn các nhà tài trợ cho diễn đàn trong việc phát hành quyển sách này đến đông đảo bạn đọc.
Các Nhà Tài Trợ Giải Pháp Excel NHÀ TÀI TRỢ VÀNG Trung Tâm Đào Tạo Smart Train Địa chỉ: Lầu 9, Giầy Việt Plaza, 180-182 Lý Chính Thắng, Q.3, TP.HCM. ĐT: 08 3931 3333 Website: www.smarttrain.edu.vn Cung cấp các khóa học và thi lấy chứng chỉ ACCA, CMA, CIA, FIA và ICFE Công ty TNHH Kiểm Toán Tư Vấn Sáng Lập Á Châu (FCA) Địa chỉ: Tầng 2, 130 Nguyễn Công Trứ, Q.1, TP.HCM. Điện thoại: 08.3822.0678 – Fax: 08.3821.5383 Website: www.sanglap.com.vn ; www.fcavn.com Chuyên cung cấp dịch vụ tư vấn và kiểm toán. Công ty Cổ Phần Nghiên Cứu và Hỗ trợ Doanh Nghiệp Hà Nội – Kế toán Hà Nội Địa chỉ trụ sở chính: Số 04, Ngõ 322, Lê Trọng Tấn, Thanh Xuân, Hà Nội. Điện thoại: 04.3566.8036 – 0974.089.926 Website: www.ketoanhanoi.vn Chuyên đào tạo kế toán thực tế mọi trình độ; Dịch vụ kế toán trọn gói cho các doanh nghiệp trên phạm vi toàn quốc.
Giải Pháp Excel Các Nhà Tài Trợ NHÀ TÀI TRỢ BẠC WEBKETOAN.VN ĐC: 196 Vạn Kiếp, P.3, Q.Bình Thạnh, TP.HCM ĐT: 08.66837342 – 0985 88 1339 Web: www.webketoan.vn Email: info@webketoan.vn Trang thông tin và diễn đàn chuyên ngành kế toán. NHÀ TÀI TRỢ ĐỒNG Công ty Cổ Phần Hệ thống 1-V (1VS) ĐC: Phòng 1507, tòa nhà Thành Công, 57 Láng Hạ, Ba Đình, Hà Nội Điện thoại: 04.3514.8550 – Fax: 04.3514.8551 Website: www.1vs.vn Chuyên: Cung cấp các giải pháp phần mềm kế toán và quản lý 1C- Ứng dụng tại hơn 1 triệu doanh nghiệp trên thế giới. Công Ty Cổ Phần Bluesofts ĐC: Số nhà 32/106, ngõ 79, đường Cầu Giấy, P. Yên Hòa, Q. Cầu Giấy, Hà Nội Điện thoại/ Fax: 04.3791.7200. MB: 0904.210.337 Website: www.bluesofts.net Email: sales@bluesofts.net Chuyên sản xuất và kinh doanh phần mềm doanh nghiệp. Overture Café ĐC: 109 Trần Quốc Thảo, P.7, Q.3, TP.HCM ĐT: 08.7307.8888 (đặt bàn), 0985.67.51.51 (tư vấn audio), 0908.444.111 (hợp tác) Website: www.facebook.com/dekhuc.overture Email: phucsinh72@gmail.com
Mục lục Giải Pháp Excel | i MỤC LỤC MỤC LỤC ..................................................................................... i LỜI GIỚI THIỆU ..........................................................................xx I.1. TỔNG QUAN.......................................................................1 I.2. VÙNG (RANGE) VÀ BẢNG (TABLE) TRONG EXCEL............3 I.2.1. Chuyển vùng (Range) thàng bảng (Table) ............... 5 I.2.2. Chuyển bảng (Table) thành vùng (Range) ............... 7 I.2.3. Thực hiện một thao tác trên bảng (Table) ................ 8 I.3. SẮP XẾP DỮ LIỆU ............................................................10 I.4. LỌC DỮ LIỆU CƠ BẢN .....................................................15 I.4.1. Lọc dữ liệu dạng văn bản (Text) ............................ 15 I.4.2. Lọc dữ liệu dạng số (Number) ................................ 20 I.4.3. Lọc dữ liệu dạng ngày và thời gian (Dates & Times) ............................................................... 23 I.4.4. Một số vấn đề khác khi lọc dữ liệu ......................... 24 I.5. LỌC DỮ LIỆU THEO ĐIỀU KIỆN NÂNG CAO .....................26 I.5.1. Nhiều điều kiện ở nhiều cột dữ liệu và lấy tất cả các dòng dữ liệu thõa điều kiện lọc ở một trong các cột ........ 26
ii | Giải Pháp Excel Mục lục I.5.2. Nhiều điều kiện trong một cột ................................ 28 I.5.3. Nhiều điều kiện ở nhiều cột lấy các dòng thoã mỗi bộ điều kiện ....................................................... 29 I.5.4. Nhiều điều kiện ở nhiều cột và có sử dụng ký hiệu thay thế * ? .......................................................... 30 I.5.5. Nhiều điều kiện ở nhiều cột và có sử dụng hàm trong điều kiện ......................................................... 31 II.1. GIỚI THIỆU PIVOTTABLE ................................................35 II.2. PIVOTTABLE CƠ BẢN .....................................................37 II.2.1. Khái niệm .............................................................. 37 II.2.2. Các bước thực hiện ................................................ 39 II.2.3. Các thủ thuật trong PivotTable.............................. 47 II.3. PIVOTTABLE NÂNG CAO ................................................62 II.3.1. Yêu cầu cơ bản khi thực hiện PivotTable ............. 62 II.3.2. Các phương pháp thống kê đối với trường dữ liệu 64 II.4. ỨNG DỤNG PIVOTTABLE TRONG BÁO CÁO ...................98 II.4.1. Phân tích ................................................................ 98 II.4.2. Tổng hợp và phân tích từng công đoạn sản xuất . 101 III.1. GIỚI THIỆU ..................................................................109 III.2. CÀI ĐẶT VÀ ADD-INS POWERPIVOT ............................111 III.3. TABLES VÀ POWERPIVOT ............................................115 III.3.1. Chuyển Range thành Tables .............................. 118 III.3.2. Đưa Tables vào Data Model của PowerPivot .... 121
Mục lục Giải Pháp Excel | iii III.3.3. Tạo mối quan hệ giữa các bảng trong PowerPivot ........................................................... 122 III.3.4. Tạo PivotTable từ dữ liệu của PowerPivot ........ 127 III.3.5. Cải tiến PowerPivot sử dụng Caculated Columns ........................................................ 131 III.4. SỬ DỤNG HÀM DAX TẠO CÁC CỘT TÍNH TOÁN .........135 III.4.1. Tạo thêm Calculated Fields cho PivotTable dùng nguồn dữ liệu từ PowerPivot ................................ 140 III.4.2. Hiệu chỉnh các Calculated Field ........................ 145 III.5. NHẬP DỮ LIỆU TỪ BÊN NGOÀI EXCEL VÀO POWERPIVOT ...............................................................146 III.5.1. Nhập dữ liệu từ MS SQL Server ........................ 146 III.5.2. Nhập dữ liệu từ MS Access ............................... 151 III.5.3. Nhập dữ liệu từ Ms Excel bên ngoài.................. 158 III.5.4. Nhập dữ liệu từ tập tin TEXT ............................ 161 III.5.5. Nhập dữ liệu từ bộ nhớ ...................................... 165 III.5.6. Nhập dữ liệu từ các nguồn khác......................... 166 III.6. LÀM MỚI DỮ LIỆU VÀ QUẢN LÝ KẾT NỐI DỮ LIỆU BÊN NGOÀI .............................................167 III.6.1. Cập nhật dữ liệu thủ công .................................. 167 III.6.2. Thiết lập tự động làm mới dữ liệu ..................... 168 III.6.3. Hiệu chỉnh kết nối dữ liệu .................................. 171 IV.1. ADD-INS POWER VIEW ...............................................175 IV.2. TẠO MỘT POWERVIEW ĐƠN GIẢN .............................176
iv | Giải Pháp Excel Mục lục IV.3. POWER VIEW VỚI KIỂU HIỂN THỊ ĐA CẤP .................185 IV.4. MAP TRONG POWER VIEW ..........................................193 IV.5. LỌC DỮ LIỆU TRÊN DASHBOARD ...............................198 V.1. POWER MAP PREVIEW FOR EXCEL 2013 ......................201 V.2. MICROSOFT POWER QUERY FOR EXCEL ......................203 V.3. CƠ SỞ DỮ LIỆU NORTHWIND ......................................204 V.3.1. Đưa CSDL Northwind vào MS SQL Server ....... 204 V.3.2. Cài đặt SQL Server ............................................. 204 V.3.3. Đưa CSDL NorthWind vào MS SQL Server ...... 205 V.3.4. Cấu trúc các bảng dữ liệu của NorthWind .......... 210 V.4. THAM KHẢO TRỰC TUYẾN VỀ BIỂU THỨC PHÂN TÍCH DỮ LIỆU (DAX) .................................................214 V.4.1. Hàm Ngày và Giờ ............................................... 214 V.4.2. Hàm Hiển thị Thời gian Thông minh .................. 215 V.4.3. Hàm Lọc dữ liệu ................................................. 217 V.4.4. Hàm Thống kê ..................................................... 218 V.4.5. Hàm Lô-gic ......................................................... 218 V.4.6. Hàm Toán và Lượng giác ................................... 219 V.4.7. Hàm Thông tin .................................................... 220 V.4.8. Hàm Văn bản ...................................................... 220 V.4.9. Các hàm khác ...................................................... 222
Lời Giới Thiệu Giải Pháp Excel | v LỜI GIỚI THIỆU Độc giả thân mến. Nếu có ai đó hỏi tôi về công cụ mạnh nhất trong Excel, tôi sẽ không ngần ngại trả lời ngay lập tức đó là Pivot Table. Pivot Table giúp giải quyết rất nhiều vấn đề về tổng hợp dữ liệu, thống kê và phân tích chỉ trong vòng vài cái click chuột đơn giản. Chính vì lẻ đó, diễn đàn Giải Pháp Excel luôn không ngừng truyền đạt kiến thức excel về Pivot Table cho người dùng cả online và offline. Vậy thì một tài liệu về Pivot là một tài liệu không thể thiếu trong quá trình truyền tải kiến thức này. Năm 2010 chúng tôi đã phát hành tài liệu online về chủ đề này trong phiên bản Excel 2003 (phiên bản vốn vẫn còn đang phổ biến, trong khi excel 2007 và excel 2010 còn chưa được nhiều người sử dụng). Nay, chúng tôi tiếp tục biên tập tài liệu này cho người dùng Excel 2013. Những năm qua, Microsoft đã cải tiến và nâng cấp liên tục ứng dụng Office quan trọng của mình cho cả máy tính để bàn và trực tuyến trên nền web. Một trong những tính năng nổi bật đó là PowerPivot, mở ra khả năng cho Office hỗ trợ “Doanh nghiệp thông minh – Business Intelligence” và công cụ PowerView mạnh mẽ giúp chúng ta tạo nên các báo cáo hay các trang thông tin doanh nghiệp tổng hợp một cách trực quan, hữu ích và cực kỳ sinh động.
vi | Giải Pháp Excel Mục lục Tài liệu này là kết quả của quá trình chọn lọc, đúc kết kiến thức mà các thành viên biên tập đã không ngừng nổ lực thực hiện. Mặc dù không thể truyền đạt đầy đủ nhất về Pivot Table và Power Pivot, Power View,… nhưng chúng tôi tin rằng nó phù hợp với đa số người dùng việt nam. Phạm Thành Mỹ (phần II), Trần Thanh Phong (phần I, III, IV, V), đồng tác giả của tài liệu này, là những thành viên ban quản trị diễn đàn Giải Pháp Excel, là những người được yêu mến vì có công lớn trong việc truyền đạt kiến thức tới cộng đồng. Tài liệu này sẽ tiếp tục minh chứng cho điều đó. Cuối cùng, dù chúng tôi đã rất cố gắng trong quá trình tổng hợp tài liệu này, nhưng cũng khó tránh khỏi những thiếu sót. Chúng tôi rất mong bạn đọc đóng góp ý kiến để tài liệu ngày càng hoàn thiện hơn ở các phiên bản sau. Mọi ý kiến đóng góp xin gửi về địa chỉ: info@giaiphapexcel.com Chân thành cảm ơn, TP. HCM, ngày 02 tháng 07 năm 2015 Đỗ Nguyên Bình Đại diện BQT Giải Pháp Excel Liên hệ hợp tác: Website: www.giaiphapexcel.com E-mail: info@giaiphapexcel.com Điện thoại: 848-6679 2225 ĐTDĐ: 0909.191.958 gặp Bình
Giải Pháp Excel | vii
Làm việc với dữ liệu Giải Pháp Excel | 1 LÀM VIỆC VỚI DỮ LIỆU I.1. TỔNG QUAN Quản trị dữ liệu bao gồm tất cả các phương pháp và quy tắc liên quan tới việc quản lý dữ liệu như một nguồn tài nguyên có giá trị. Cơ sở dữ liệu (viết tắt CSDL - database) được hiểu theo cách định nghĩa kiểu kĩ thuật thì nó là một tập hợp thông tin có cấu trúc. Tuy nhiên, thuật ngữ này thường dùng trong công nghệ thông tin và nó thường được hiểu rõ hơn dưới dạng một tập hợp liên kết các dữ liệu, thường đủ lớn để lưu trên một thiết bị lưu trữ như đĩa hay băng. Dữ liệu này được duy trì dưới dạng một tập hợp các tập tin trong hệ điều hành hay được lưu trữ trong các hệ quản trị cơ sở dữ liệu. Một số ưu điểm mà CSDL mang lại: - Giảm sự trùng lặp thông tin xuống mức thấp nhất. Do đó đảm bảo thông tin có tính nhất quán và toàn vẹn dữ liệu. - Đảm bảo dữ liệu có thể được truy xuất theo nhiều cách khác nhau. - Nhiều người có thể sử dụng chung một cơ sở dữ liệu.
2 | Giải Pháp Excel Làm việc với dữ liệu Cơ sở dữ liệu được phân chia ra nhiều loại khác nhau tùy theo mô hình dữ liệu mà nó hỗ trợ. - Cơ sở dữ liệu dạng file: dữ liệu được lưu trữ dưới dạng các file có thể là text, ascii, *.xls,… - Cơ sở dữ liệu quan hệ: dữ liệu được lưu trữ trong các bảng dữ liệu gọi là các thực thể, giữa các thực thể này có mối liên hệ với nhau gọi là các quan hệ, mỗi quan hệ có các thuộc tính, trong đó có một thuộc tính là khóa chính. Các hệ quản trị hỗ trợ cơ sở dữ liệu quan hệ như: MS Access, Foxpro, MySQL, PostgreSQL, Microsoft SQL Server, Oracle, Sybase và IBM DB2,… - Cơ sở dữ liệu hướng đối tượng: dữ liệu cũng được lưu trữ trong các bảng dữ liệu nhưng các bảng có bổ sung thêm các tính năng hướng đối tượng như lưu trữ thêm các hành vi, nhằm thể hiện hành vi của đối tượng. Mỗi bảng xem như một lớp dữ liệu, một dòng dữ liệu trong bảng là một đối tượng. Các hệ quản trị có hỗ trợ cơ sở dữ liệu hướng đối tượng như: MS SQL server, Oracle, PostgreSQL,… - Cơ sở dữ liệu bán cấu trúc: dữ liệu được lưu dưới dạng XML, với định dạng này thông tin mô tả về đối tượng thể hiện trong các tag. Đây là cơ sở dữ liệu có nhiều ưu điểm do lưu trữ được hầu hết các loại dữ liệu khác nhau nên cơ sở dữ liệu bán cấu trúc là hướng mới trong nghiên cứu và ứng dụng. Hệ quản trị cơ sở dữ liệu Một hệ quản trị cơ sở dữ liệu (Database Management System - DBMS) là một chương trình máy tính được thiết kế để quản lý một cơ sở dữ liệu, một tập hợp dữ liệu lớn có cấu trúc, phục vụ cho các yêu cầu về dữ liệu của một số lượng lớn người sử dụng. Ví dụ điển hình của hệ quản trị cơ sở dữ liệu bao gồm kế toán,
Làm việc với dữ liệu Giải Pháp Excel | 3 nguồn nhân lực và hệ thống hỗ trợ khách hàng. Đầu tiên, hệ quản trị cơ sở dữ liệu chỉ có ở các công ty lớn với đầy đủ phần cứng cần thiết hỗ trợ cho một tập hợp dữ liệu lớn. Gần đây DBMS đã trở thành một phần tiêu chuẩn của bất kỳ công ty nào. Bốn tính năng cơ bản của một DBMS: - Định nghĩa dữ liệu: tạo, hiệu chỉnh, xóa các định nghĩa về cấu trúc tổ chức dữ liệu - Cập nhật: thêm, hiện chỉnh và xóa dữ liệu - Truy xuất dữ liệu: cung cấp khả năng truy xuất các thông tin cho các ứng dụng khác - Quản trị: quản lý và phân quyền người dùng, bảo mật dữ liệu, kiểm soát hiệu suất, duy trì tính nguyên vẹn của dữ liệu, kiểm soát các tranh chấp dữ liệu, phục hồi thông tin khi có sự cố. Excel thuộc loại cơ sở dữ liệu dạng file. Các dữ liệu trong Excel được tổ chức thành các vùng (Range) dạng bảng hay là các bảng (Tables) trên bảng tính mà Excel sẽ không thể quản lý được các mối quan giữa các bảng với nhau. Công cụ PowerPivot mới được thêm vào Excel sẽ giúp nó thực hiện được điều này và biến Excel trở thành gần giống như là một cơ sở dữ liệu đơn giản và từ đó giúp cho việc tính toán và thao tác với dữ liệu được nhanh và mạnh hơn,… I.2. VÙNG (RANGE) VÀ BẢNG (TABLE) TRONG EXCEL Thông thường, chúng ta hay lưu dữ liệu trong một vùng bảng tính Excel, tuy nhiên để tận dụng các tính năng tính toán và quản lý dữ liệu thì chúng ta có thể chuyển vùng thành bảng. Về mặt hình thức trình bày trong bảng tính, chúng ta không thấy nhiều khác biệt giữa vùng và bảng dữ liệu:
4 | Giải Pháp Excel Làm việc với dữ liệu Vùng Bảng Khi trình bày các vùng hay bảng dữ liệu trong Excel, phải có ít nhất một dòng và cột trống ngăn cách giữa các vùng hay bảng dữ liệu. Khi đó, Excel mới có cơ sở nhận biết các vùng hay bảng dữ liệu độc lập với nhau. Hình bên dưới cho Excel biết có 4 bảng dữ liệu độc lập với nhau:
Làm việc với dữ liệu Giải Pháp Excel | 5 Một vùng hay bảng dữ liệu tốt phải có dòng đầu tiên là tiêu đề cột (không trùng tên, không nối các ô) và bên dưới là các dòng dữ liệu. Tiêu đề cột Để các bảng dữ liệu có mối quan hệ với nhau thì ngoài tiêu đề cột thì ở mỗi dòng dữ liệu còn cần có khóa (key) nhận biết (không trình bày trong tài liệu này). I.2.1. Chuyển vùng (Range) thành bảng (Table) Các bước thực hiện: B1. Chọn vùng cần chuyển sang bảng B2. Vào Insert  chọn Table
6 | Giải Pháp Excel Làm việc với dữ liệu B3. Đặt tên lại cho bảng để dễ tham chiếu sau này. Table Tools  Design  Table Name  Đặt tên cho bảng.
Làm việc với dữ liệu Giải Pháp Excel | 7 I.2.2. Chuyển bảng (Table) thành vùng (Range) Các bước thực hiện: B1. Chọn bảng cần chuyển sang vùng
8 | Giải Pháp Excel Làm việc với dữ liệu B2. Vào Table Tools  Design  chọn Covert to Range  xác nhận chuyển sang vùng. I.2.3. Thực hiện một số thao tác trên bảng (Table) Xem dữ liệu dạng Slicer B1. Chọn bảng cần thiết lập Slicer B2. Vào Table Tools  Design  Insert Slicer