Tin học 88 - chia sẻ kiến thức
ADS

Bài 10: Khai thác xử lý dữ liệu trong excel 2010 - phần 2

Bài viết giới thiệu đến các bạn phương pháp khai thác cơ sở dữ liệu trong excel 2010 ( phần 2 ).

Mục lục

     3. Tìm hiểu dữ liệu nguồn của PivotTable

    Chúng ta có thể sử dụng nhiều loại dữ liệu để làm nguồn cho một PivotTable:

    • Sử dụng dữ liệu từ một bảng tính Excel trong cùng một Workbook
    • Sử dụng dữ liệu từ một bảng tính Excel trong một Workbook khác
    • Sử dụng dữ liệu kết hợp từ nhiều nguồn khác nhau nhưng có cấu trúc giống nhau (consolidate ranges)
    • Sử dụng dữ liệu từ một báo cáo PivotTable khác

    Những bài sau đây sẽ lần lượt điểm qua từng loại dữ liệu này.

    3.1. Sử dụng dữ liệu của cùng một bảng tính Excel

    Có lẽ cách dễ nhất và cũng được nhiều người sử dụng nhất là sử dụng dữ liệu của một bảng tính Excel để làm nguồn cho PivotTable. Dữ liệu này có thể nằm trong cùng một bảng tính, như đã được trình bày ở bài 1.

    Mọi việc sẽ trở nên rất đơn giản khi bạn tạo một PivotTable trong cùng bảng tính chứa dữ liệu nguồn. Dữ liệu này phải được thiết kế theo dạng một List (trong Excel 2003), một Table (Excel 2010), với những yêu cầu sau:

    • Hàng đầu tiên chứa tên các Field (hoặc tên của các cột)
    • Hàng thứ hai và những hàng tiếp theo chứa dữ liệu
    • Không có hàng bỏ trống, nhưng có thể có những ô trống

    Để tìm hiểu kỹ hơn về cấu trúc của một Table, bạn có thể tham khảo ở bài "Phân tích dữ liệu với các Table".

    Nếu có một ô nằm ở đâu đó trong vùng dữ liệu nguồn này được chọn khi bạn mở hộp thoại Create PivotTable, Excel sẽ tự động xác định địa chỉ của toàn bộ vùng dữ liệu và nhập nó vào trong mục Table/Range của hộp thoại. Nếu bạn muốn xác định chính xác địa chỉ của vùng dữ liệu nguồn, bạn có thể chọn một trong 3 cách sau:

    1. Chọn một ô trong vùng dữ liệu trước khi mở hộp thoại. Địa chỉ của vùng dữ liệu sẽ được nhập vào tự động
    2. Nhập trực tiếp địa chỉ của vùng dữ liệu trong mục Table/Range của hộp thoại
    3. Sử dụng nút Select trong hộp thoại để chọn vùng dữ liệu

    khai thác xử lý dữ liệu trong excel

    Có một cách rất hay và rất nên làm để chỉ cho Excel biết vùng dữ liệu nằm ở đâu, là tạo một cái tên (Name) cho vùng dữ liệu. Sử dụng Name thì tiện lợi hơn việc nhập địa chỉ hoặc chọn vùng dữ liệu mỗi khi bạn muốn tham chiếu đến chúng. Để tìm hiểu thêm về Name cho vùng dữ liệu, xin đọc bài: "Sử dụng tên cho dãy".

    Tuy nhiên, nếu bạn sử dụng một Table của Excel 2010 để làm dữ liệu nguồn cho PivotTable, thì bạn khỏi cần quan tâm đến Name, bới chính bản thân Table đã có sẵn một cái tên, khi bạn tạo nó. Và khi đã có Name của dữ liệu nguồn, bạn chỉ việc nhập trực tiếp cái tên này vào trong mục Table/Range của hộp thoại Create PivotTable. Ví dụ, ở hình sau đây, dữ liệu nguồn của PivotTable là một dãy có tên là SalesData:

    khai thác xử lý dữ liệu trong excel

    3.2. Sử dụng dữ liệu nguồn từ một bảng tính Excel khác

    Nếu dữ liệu mà bạn sẽ dùng trong PivotTable nằm tron một bảng tính khác, các bước thể tạo PivotTable hơi khác một tí. Bạn sẽ phải xác định dãy ô (địa chỉ) chứa dữ liệu nguồn, và tên của workbook chứa dữ liệu nguồn.

    Cách dễ nhất để thực hiện việc này, là mở đồng thời hai bảng tính. Môt cái sẽ chứa PivotTable, một cái chứa dữ liệu nguồn. Rồi làm theo các bước sau:

    1. Chọn (kích hoạt) workbook sẽ tạo PivotTable.
    2. Nhấn nút PivotTable trong tab Insert của Ribbon để mở hộp thoại Create PivotTable.
    3. Hãy chắc chắn rằng tùy chọn Select a Table or Range đang được chọn.
    4. Nhấn nút Select nằm ở phía bên phải của khung Table/Range, một hộp thoại Create PivotTable "nhỏ" như sau đây sẽ xuất hiện:

    khai thác xử lý dữ liệu trong excel

    5. Nhấn Alt+Tab hoặc nhấn vào workbook chứa dữ liệu nguồn để kích hoạt nó. Bạn sẽ vẫn thấy cái hộp thoại Create PivotTable "nhỏ".

    6. Chọn dãy chứa dữ liệu nguồn cho PivotTable. Địa chỉ của nó, bao gồm cả tên của workbook, sẽ tự động nhập vào trong hộp thoại Create PivotTable "nhỏ":

    khai thác xử lý dữ liệu trong excel

    7. Nhấn vào nút Select trên cửa sổ Create PivotTable "nhỏ" để chấp nhận vùng chọn và đóng nó lại, quay về hộp thoại Create PivotTable "lớn".

    8. Nhấn OK để quay về bảng tính với PivotTable vừa tạo.

    Khi một PivotTable được "link" tới một bảng tính bên ngoài, bạn chỉ có thể cập nhật (update) PivotTable nếu đường link này tới bảng tính nguồn còn tồn tại. Khi bảng tính chứa dữ liệu nguồn bị đổi tên, bị dời đi chỗ khác, hoặc bị xóa, bạn sẽ không thể cập nhật PivotTable. Excel sẽ hiển thị một thông báo lỗi nếu bạn cố làm việc đó.

    Có thể bạn thắc mắc là tại sao có nhiều người không làm một PivotTable trong cùng một bảng tính, mà lại làm PivotTable trong một bảng tính khác? Việc này, thật ra rất hữu ích, nhất là đối với những dữ liệu khổng lồ, cồng kềnh. Có lẽ bạn đã biết những bảng tính chứa một bảng dữ liệu lớn thì chạy chậm như thế nào. Khi đặt PivotTable trong một bảng tính khác, bạn vẫn có thể có được những thống kê, mà không cần thiết phải mở dữ liệu nguồn ra. Hoặc khi bạn muốn thống kê dữ liệu từ nhiều nguồn khác nhau, bạn có thể tạo một workbook trung gian, chứa nhiều PivotTable, mà mỗi PivotTable thì "link" đến một dữ liệu nguồn riêng.

    Có một điều bạn cần nên biết, là bản thân PivotTalbe không bao giờ tự "refresh" dữ liệu, nghĩa là nó không thể tự động cập nhật khi dữ liệu nguồn thay đổi, bất kể dữ liệu nguồn nằm trong cùng một workbook với PivotTable hay nằm trong một worbook khác. Việc tính toán lại bảng tính (recalculated) cũng không cập nhật lại dữ liệu cho PivotTable.

    Bạn phải thực hiện chuyện "refresh" bằng tay. Bằng hai cách:

    • Nhấn chuột phải vào PivotTable và chọn lệnh Refresh Data
    • Nhấn vào nút Refresh nằm trong tab PivotTable|Option trên Ribbon (bạn chỉ thấy tab này khi bạn kích hoạt PivotTable).

    khai thác xử lý dữ liệu trong excel

    3.3. Sử dụng nhiều dãy dữ liệu có cùng cấu trúc (Multiple Consolidation Ranges)

    Thuật ngữ "Multiple Consolidation Ranges" (nhiều dãy dữ liệu có cùng cấu trúc), thật ra không phức tạp như cái tên của nó. Tất cả chỉ đơn giản là tạo một PivotTable dựa trên hai hoặc nhiều bảng dữ liệu (là Table, hay là List trong các version trước). Những nguồn dữ liệu này có thể nằm trong cùng một bảng tính, hoặc nằm trong những bảng tính khác nhau. Nhưng, chức năng Create PivotTable của Excel 2010 mà chúng ta đã làm quen từ đầu đến giờ, lại không hỗ trợ Multiple Consolidate Ranges, và không có tùy chọn này. Để sử dụng được Multiple Consolidation Ranges, chúng ta phải cầu viện đến chức năng PivotTable and PivotChart Wizard, một chức năng có trong những version trước của Excel. Trong Excel 2010, chức năng này vẫn còn, nhưng bị ẩn đi. Để gọi nó ra, bạn phải sử dụng những phím tắt dùng để gọi PivotTable and PivotChart Wizard trong Excel 2003, đó là: Alt+D+P.

    Nếu như bạn thuộc tất cả các phím tắt để gọi lệnh trên menu của Excel 2003, thì bạn có thể sử dụng chúng trong Excel 2010, cho dù Ribbon của Excel 2010 thì chẳng giống gì Menu Bar của Excel 2003.

    Để sử dụng được Multiple Consolidation Ranges, bắt buộc mỗi danh sách mà bạn muốn dùng làm dữ liệu nguồn cho PivotTable phải có cùng cấu trúc, có nghĩa là tất cả những tiêu đề cột (nằm trên hàng đầu tiên) và tiêu đề dòng (nằm trong cột đầu tiên bên trái) phải giống hệt nhau. Nếu như có một hàng nào, hoặc cột nào nằm riêng lẻ, thì chúng sẽ không được tích hợp vào trong báo cáo PivotTable.

    Đây là một ví dụ về các nguồn dữ liệu có thể sử dụng với chức năng Multiple Consolidation Ranges:

    khai thác xử lý dữ liệu trong excel

    Để khởi động "Wizard", bạn hãy nhấn Alt+D rồi nhấn thêm phím P. Và tại Step 1, nhấn vào tùy chọn Multiple Consolidation Ranges:

    khai thác xử lý dữ liệu trong excel

    Sau khi nhấn Next để sang Step 2a, bạn sẽ có 2 tùy chọn:

    • Create a single page field for me — Để Excel tự động tạo ra một Page Field đơn, với mỗi vùng dữ liệu nguồn là môt mục (item) trong Page Field đó. Page Field có thể tạm hiểu như là chức năng lọc "cấp 1" của PivotTable. Trong cửa sổ PivotTable Field Lists, Page Field chính là những mục nằm trong khung Report Filter. Nếu không có, xem như Page Field = All (xem tất cả các dữ liệu).
    • I will create the page fields — Dùng tùy chọn này để tự tạo các Page Field của riêng bạn. Bạn có thể tạo tối đa là 4 Page Field, mỗi Page Field lọc ra một thành phần nào đó của các dữ liệu.

    3.3.1 Tạo một trang tổng hợp duy nhất với chức năng Create a single page field for me

    Bạn xem lại bảng tính EmployeeData mà tôi vừa trình bày ở bài trước. Bạn sẽ thấy có 3 danh sách, mỗi danh sách là một phương án tuyển dụng nhân viên cụ thể cho các bộ phận. Và cả 3 danh sách đều có cùng cấu trúc, cũng như tiêu đề của các hàng, cột này là giống nhau. Để tạo một PivotTable dựa vào 3 danh sách này, bạn hãy sử dụng PivotTable and PivotChart Wizard theo các bước tôi đã trình bày ở bài trước, và khi đang ở trong Step 2a, hãy đánh dấu vào tùy chọn Create a Single-Page Field, rồi nhấn Next để sang Step 2b, rồi theo các bước sau:

    1. Nhấn vào nút Select ở bên phải hộp Range
    2. Chọn dãy A3:B9 trong bảng tính.
    3. Nhấn lại vào nút Select để đưa dãy vừa chọn vào trong khung Range.

    Ghi chú: Nếu bạn bỏ qua bước 1, mà chỉ cần dùng chuột chọn trực tiếp dãy A3:B9 trong bảng tính, Excel sẽ tự hiểu là bạn đã nhấn nút Select rồi, nên cũng sẽ bỏ qua luôn bước 3 này. Khi bạn kết thúc việc chọn dãy bằng chuột, Excel sẽ đưa bạn vào thẳng bước 4 sau đây.

    4- Nhấn nút Add để thêm dãy vừa chọn vào trong danh sách All Ranges.

    5- Lập lại bước 1 đến bước 4 để chọn và thêm vào danh sách All Ranges hai dãy D3:D9 và G3:G9. Tới đây, màn hình của bạn sẽ giống như sau:

    khai thác xử lý dữ liệu trong excel

    6- Nhấn Next để sang Step 3.

    7- Tại đây, bạn có thể chọn New Worksheet để tạo PivotTable trong một Sheet mới, hoặc chọn Existing worksheet và nhấn chuột vào nơi bạn muốn tạo PivotTable ngay trong bảng tính này.

    8- Click Finish.

    Bạn sẽ có một PivotTable như sau:

    khai thác xử lý dữ liệu trong excel

    Trong PivotTable này, nhãn Column Labels có một mũi tên xổ xuống, bạn có thể dùng nó để lọc ra cột muốn xem. Trong trường hợp này, do các dữ liệu nguồn chỉ có một cột (Employees), nên nút này không cần dùng đến, nhưng nếu các dữ liệu nguồn của bạn có nhiều cột, bạn sẽ cần dùng đến nó. Nhãn Row Labels cũng có một mũi tên xổ xuống, dùng để lọc ra những hàng muốn xem. Những nút xổ xuống này là điểm đặc trưng mà bạn có thể thấy trong tất cả các PivotTable.

    Còn nút xổ xuống của nhãn Page1 thì có gì đặc biệt? Nó cho phép bạn lọc ra dữ liệu nguồn nào muốn xem, như bạn thấy ở hình sau đây, khi nhấn vào nó. Các mục Item1, Item2 và Item3 tham chiếu đến 3 dữ liệu nguồn mà bạn đã dùng làm dữ liệu nguồn cho PivotTable. Bạn có thể chọn tổng hợp dữ liệu theo tất cả các nguồn (All), hoặc chỉ 1 nguồn nào đó. Nếu muốn tổng hợp nhiều hơn 1 nguồn dữ liệu, bạn phải nhấn vào tùy chọn Select Multiple Items.

    khai thác xử lý dữ liệu trong excel

    Bạn hãy thử sử dụng các nút xổ xuống bên cạnh các nhãn, để xem chúng làm việc như thế nào. Lọc là một trong chức năng quan trọng nhất của một PivotTable, bạn cần hiểu rõ cách lọc của một PivotTable, để có thể dễ dàng tiếp cận với những chức năng cao cấp hơn.

    Trong PivotTable mà chúng ta đã thực hành trong bài này, có cột Grand Total, là một mặc định của PivotTable. Tuy nhiên, bởi vì các dữ liệu nguồn của chúng ta chỉ có 1 cột, nên không cần thiết đến cột Grand Total, tôi sẽ nói về cách bỏ cái cột này đi trong bài sau.

    3.3.2. Tạo một PivotTable có nhiều trang tổng hợp từ những dữ liệu nguồn có cùng cấu trúc

    Trong công việc hằng ngày, nhất là khi phải thường xuyên tổng hợp những dữ liệu kiểu như: Báo cáo doanh thu quý I, Báo cáo doanh thu quý II, Báo cáo doanh thu quý III, v.v..., bạn nên sử dụng PivotTable, với tùy chọn thứ 2 khi đang ở trong Step 2a của PivotTable and PivotChart Wizard: I will create the page fields.

    Tùy chọn này rất thích hợp khi tổng hợp từ nhiều nguồn dữ liệu có cùng cấu trúc, mà những nguồn dữ liệu đó có cùng tiêu đề, chỉ khác vài con số dùng để tổng hợp (theo quý, theo năm, theo tháng...), chẳng hạn như bảng tính này:

    khai thác xử lý dữ liệu trong excel

    Đây là 4 bảng tổng hợp số học sinh đạt yêu cầu theo từng môn, và theo từng học kỳ trong hai năm học 2005, 2006; cả 4 đều có chung tiêu đề Year ... Semester ....

    Chúng ta hãy thử. Bạn hãy sử dụng PivotTable and PivotChart Wizard theo các bước tôi đã trình bày ở bài 2.3, và khi đang ở trong Step 2a, hãy đánh dấu vào tùy chọn I will create the page fields, rồi nhấn Next để sang Step 2b, rồi theo các bước sau:

    1. Nhập khối dữ liệu nguồn thứ nhất (A2:B8) vào trong khung Range
    2. Nhấn nút Add để thêm nó vào trong danh sách All Ranges
    3. Lập lại hai bước trên ba lần, để thêm 3 khối dữ liệu còn lại (D2:D8, A11:B17, và D11:E17) vào trong danh sách All Ranges. Tại thời điểm này, màn hình của bạn sẽ giống như hình sau:

    khai thác xử lý dữ liệu trong excel

    4- Nhấn vào tùy chọn 2 ở phần How many page fields do you want?

    5- Bạn sẽ thấy phần đang mờ mờ ở dưới cùng của hộp thoại PivotTable and PivotChart Wizard mở ra 2 cái: Field oneField two (bạn chỉ có thể có tối đa 4 trang tổng hợp).

    6- Tới đây, hãy tạm dừng một chút để vạch ra một vài ý tưởng cho việc thống kê dữ liệu. Bạn cần có bao nhiêu trang tổng hợp? Hay nói cách khác, bạn cần thống kê dữ liệu theo mấy tiêu chí? Hãy nhìn vào tiêu đề của 4 dữ liệu nguồn. Ở đó chúng ta có hai năm học: 2005 và 2006, và hai học kỳ (semester): học kỳ 1 và học kỳ 2. Vậy, ý tưởng hay nhất, có lẽ là thống kê theo năm để lọc ra dữ liệu của từng năm, hoặc cả 2 năm; và đồng thời cũng thống kê theo từng học kỳ, để lọc ra dữ liệu của từng học kỳ hoặc cả 2 học kỳ. Như vậy, chúng ta cần có hai trang tổng hợp, đó là lý do tại sao ở bước 4 trên đây, tôi nói bạn nhấn vào tùy chọn 2. Mời bạn tiếp tục:

    7- Trong danh sách All Ranges, chọn dãy đầu tiên: A2:B8, dãy này là số liệu của học kỳ 1 năm 2002.

    8- Nhập Year 2002 vào trong Field one.

    9- Nhập Semester 1 vào trong Field two.

    10- Chọn tiếp dãy thứ hai: D2:E8, dãy này là số liệu của học kỳ 1 năm 2003.

    11- Nhập Year 2003 vào trong Field one.

    12- Bởi vì trong Field two đã có sẵn Semester 1, nên thay vì nhập, bạn có thể chọn nó từ nút mũi tên xổ xuống.

    13- Chọn dãy thứ ba: A11:B17, dãy này là số liệu của học kỳ 2 năm 2002.

    14- Chọn Year 2002 từ nút mũi tên xổ xuống cho Field one.

    15- Nhập Semester 2 vào trong Field two.

    khai thác xử lý dữ liệu trong excel

    16- Chọn dãy cuối cùng: D11:E17, chọn Year 2003 cho Field one và Semester 2 cho Field two từ các nút mũi tên xổ xuống.

    Nhấn Next để hoàn tất PivotTable và đặt nó ở trong cùng Sheet hay trong một Sheet khác, tùy bạn. Thành quả của chúng ta giống như sau, là một PivotTable tổng hợp tất cả số học sinh đạt yêu cầu của cả hai năm học 2002 và 2003, thống kê chi tiết theo từng môn học:

    khai thác xử lý dữ liệu trong excel

    Nếu bạn nhấn nhãn Page 1, bạn sẽ có hai lựa chọn: Year 2002 và Year 2003; cũng vậy, khi nhấn nhãn Page 2, bạn sẽ có hai lựa chọn: Semester 1 và Semester 2.

    Bởi vì trong PivotTable này chỉ có 1 cột, nên bạn có thể bỏ cái Grand Total đi bằng cách: nhấn phải chuột vào ngay nhãn Grand Total, và chọn Remove Grand Total từ menu mở ra.

    khai thác xử lý dữ liệu trong excel

    4. Tạo một PivotTable sử dụng dữ liệu nguồn từ một báo cáo PivotTable đã có

    Lựa chọn cuối cùng cho việc tạo một PivotTable là dựa vào một PivotTable hoặc một PivotChart đã có. Nói cách khác, ta sử dụng Summary Data của một PivotTable đã có làm Raw Data cho báo cáo thứ hai.

    Có hai cách để tạo một PivotTable là dựa vào một PivotTable đã có:

    • Nếu bạn sử dụng Excel 2010, bạn chỉ việc gọi lệnh tạo một PivotTable như vẫn thường làm, và chọn một PivotTable có sẵn làm dữ liệu nguồn cho nó.
    • Nếu bạn sử dụng PivotTable and PivotChart Wizard, đánh dấu vào tùy chọn Another PivotTable or PivotChart Report ở bước 1 (tùy chọn này chỉ xuất hiện khi bảng tính của bạn đã có chứa ít nhất một PivotTable). Sau khi bạn nhấn Next, bạn sẽ thấy một danh sách các PivotTable hoặc PivotChart đang có, rồi bạn chọn một cái để làm dữ liệu nguồn.

    Tại sao lại sử dụng một PivotTable đã có để làm dữ liệu nguồn cho một PivotTable khác? Trong nhiều trường hợp, ít nhất là lúc ban đầu, bản PivotTable thứ hai sẽ có cùng một dữ liệu nguồn với bản PivotTable thứ nhất. Nhưng với việc định dạng cho bản PivotTable thứ hai, bạn sẽ tạo ra được hai cách xem cho cùng một dữ liệu nguồn, rất hữu dụng cho một số giải pháp xử lý dữ liệu. Ví dụ, bạn đã có một PivotTable tổng hợp dữ liệu, nhưng có lẽ vẫn còn một ít rắc rối (vì dữ liệu có nhiều vấn đề cần xử lý). Bằng cách tạo thêm một PivotTable nữa dựa vào PivotTable đã có, bạn sẽ có được một cái nhìn cụ thể hơn, dễ xem hơn về dữ liệu nguồn.

    Thêm nữa, việc tạo một PivotTable sử dụng dữ liệu nguồn từ một PivotTable đã có, sẽ ít chiếm dụng bộ nhớ hệ thống hơn là tạo 2 PivotTable từ một dữ liệu nguồn. Thiết nghĩ đây cũng là một vấn đề cần cân nhắc khi chúng ta phải xử lý những dữ liệu lớn.

    5. Sử dụng các công cụ của PivotTable và định dạng PivotTable

    Excel có một số công cụ được thiết kế đặc biệt cho các báo cáo PivotTable. Bạn sẽ thấy những công cụ này trên Ribbon OptionsDesign, xuất hiện khi có một PivotTable được kích hoạt.

    các hàm xử lý dữ liệu trong excel

    Khi kích hoạt một PivotTable, bạn sẽ thấy có thêm Ribbon OptionsDesign

    6. Tìm hiểu PivotTable Field List

    Có lẽ công cụ mà bạn sẽ phải đụng đến thường xuyên nhất khi làm việc với PivotTable là Field List. Bạn dùng Field List để chỉ ra loại dữ liệu nào sẽ được đưa vào báo cáo PivotTable, và sắp xếp chúng trong báo cáo.

    Khi bạn vừa ra lệnh tạo mới một PivotTable, bạn sẽ có một báo cáo rỗng. Khi đó, PivotTable Field List hiển thị tất cả những Field có trong dữ liệu nguồn. Đây là một ví dụ:

    xử lý dữ liệu trong excel

    Một PivotTable mới tinh, phía bên phải là PivotTable Field List, chưa có field nào được chọn

    Để thêm một field nào vào trong báo cáo PivotTable, bạn chỉ cần đánh dấu vào hộp kiểm ngay bên cạnh tên field. Excel sẽ thêm field đó vào một trong 4 vùng nằm ở phần dưới của PivotTable Field List.

    Bốn vùng này là:

    1. Report Filter: Những Field nào được thêm vào trong vùng này, sẽ được dùng để lọc toàn bộ dữ liệu nguồn. Nói cách khác, PivotTable chỉ hiển thị những dữ liệu nào thỏa mãn điều kiện của Report Filter.
    2. Columns Label: Những Field nào được thêm vào trong vùng này, sẽ được dùng làm tiêu đề cột cho báo cáo PivotTable.
    3. Row Label: Những Field nào được thêm vào trong vùng này, sẽ được dùng làm tiêu đề hàng cho báo cáo PivotTable.
    4. Values: Những Field nào được thêm vào trong vùng này sẽ được tính tổng theo mặc định và hiển thị trên vùng nội dung chính (main body) của PivotTable, nói cách khác, đây chính là những thống kê từ dữ liệu nguồn, mà bạn muốn có khi sử dụng PivotTable.

    Khi bạn đánh dấu kiểm vào một field nào đó, làm sao Excel biết bạn muốn đem field đó vào vùng nào ? Đơn giản thôi: Nếu field chỉ chứa những dữ liệu là số, thì nó sẽ được đem vào trong vùng Values, còn nếu field có chứa những loại dữ liệu khác (text, ngày tháng...) thì nó sẽ được đem vào trong vùng Row Labels. Tuy nhiên, việc Excel đem field vào trong vùng nào thì không quan trọng. Bởi vì bạn có thể di chuyển các field từ vùng này sang vùng khác, nếu thấy cần thiết.

    Xem ví dụ sau đây, minh họa một PivotTable cùng với Field List của nó. Bạn sẽ biết cách tạo ra PivotTable này trong bài sau. Còn bây giờ, hãy lướt qua các vùng mà tôi đã đánh số trong hình, tương ứng với 4 vùng của Field List mà tôi vừa trình bày ở trên.

    1. Region được đặt trong vùng Report Filter, và nó nằm trong vùng Filter của báo cáo (ở trên cao, góc trái), có chức năng lọc toàn bộ dữ liệu nguồn.
    2. Date được đặt trong vùng Row Labels, và nó là các tiêu đề hàng của báo cáo PivotTable.
    3. Store được đặt trong vùng Column Labels, và nó là các tiêu đề cột của báo cáo PivotTable.
    4. Camping (chính xác hơn, là Sum of Camping) được đặt trong vùng Values, và nó là các dữ liệu đã được lọc ra trong báo cáo PivotTable.

    xử lý dữ liệu trong excel

    pivotTable trong excel

    PivotTable Field List, với các field đã được thêm vào trong 4 vùng bên dưới

    7.  Sử dụng PivotTable Field List

    Ở bài trước, bạn đã biết rằng khi đưa một Field vào trong một vùng của Field List, thì nó sẽ nằm ở đâu trong PivotTable, hay nói cách khác, việc đưa Field vào một trong bốn vùng của Field List sẽ ảnh hưởng thế nào đến cấu trúc của PivotTable. Bài này sẽ trình bày cho bạn thay đổi cấu trúc đó bằng cách sử dụng Field List.

    Trước hết, mời bạn mở file SportingGoodsRawData.xlsx (ở cuối bài này). Yêu cầu đặt ra là thống kê lượng hàng phục vụ cho dã ngoại (Camping) đã bán được theo cửa hàng (Store) và theo ngày (Date) của từng vùng (Region).

    Bạn theo các bước sau:

    1. Nhấn con trỏ vào bất kỳ ô nào trong vùng dữ liệu.
    2. Nhấn nút PivotTable từ Ribbon Insert để mở hộp thoại Create PivotTable.
    3. Nhấn OK. Excel sẽ tạo một PivotTable "rỗng" như hình sau:

    xử lý dữ liệu trong excel

     PivotTable, lúc ban đầu

    4- Trong Field List, chọn (click vào hộp kiểm ở ngay bên trái) các Field Store, Region, Camping, và Date.

    Tới lúc này ta sẽ có:

    xử lý dữ liệu trong excel

    PivotTable với cấu trúc theo mặc định, chưa chỉnh sửa

    Bạn thấy đấy. Field CampingStore đều được đặt trong vùng Values, bởi Excel thấy chúng chứa toàn các dữ liệu kiểu số. Field RegionDate được đem vào trong vùng Row Labels bởi chúng có chứa những dữ liệu kiểu ngày tháng và text.

    Dĩ nhiên, cấu trúc này của PivotTable không phải là thứ chúng ta cần, chính xác hơn, về cơ bản thì nó chả có ích lợi gì, bởi vì mục đích của chúng ta là thống kê lượng hàng phục vụ cho dã ngoại (Camping) đã bán được theo cửa hàng (Store) và theo ngày (Date) của từng vùng (Region). Tất cả những gì cần làm để đạt được mục đích, là sắp xếp lại các field trong các vùng của Field List.

    Bạn có thể di chuyển một field sang vùng khác bằng hai cách:

    • Click vào field muốn di chuyển và rê (drag) nó vào trong vùng khác.
    • Nhấn vào nút mũi tên xổ xuống bên cạnh tên field (ở trong một vùng nào đó), rồi chọn lệnh Move to XXXX (với XXXX là tên của vùng bạn muốn dời field này đến).

    xử lý dữ liệu trong excel

    Dùng nút mũi tên xổ xuống để dời field Region vào vùng Report Filter

    Và thực hiện những việc sau:

    • Dời field Region vào vùng Report Filter
    • Dời field Store (đang mang tên là Sum of Store) vào vùng Column Labels

    PivotTable của bạn lúc này sẽ giống như hình sau:

    xử lý dữ liệu trong excel

    PivotTable sau khi được chỉnh sửa

    Cuối cùng, bạn hãy định dạng cho giá trị trong PivotTable theo dạng tiền tệ:

    • Nhấn vào nút mũi tên xổ xuống cạnh field Sum of Camping trong vùng Values
    • Chọn Value Field Settings trong danh sách mở ra, để mở hộp thoại Value Field Settings.

    xử lý dữ liệu trong excel

    Chọn Value Field Setting cho field Sum of Camping

    • Nhấn vào nút Number Format để mở hộp thoại Format Cells quen thuộc.

    xử lý dữ liệu trong excel

    Nhấn nút Number Format để mở hộp thoại Format Cells

    • Chọn loại định dạng Currency (tiền tệ), rồi nhấn OK hai lần để trở về PivotTable.

    PivotTable của bạn cuối cùng sẽ giống như sau, và đó cũng là tất cả những gì chúng ta muốn.

    xử lý dữ liệu trong excel

    PivotTable đã hoàn chỉnh theo yêu cầu

    8. Thiết lập các tùy chọn cho PivotTable Field List

    Hộp thoại Field List sẽ tự động hiện ra mỗi khi bạn nhấn chuột vào bất kỳ chỗ nào trong PivotTable, nghĩa là khi PivotTable được kích hoạt thì hộp thoại Field List sẽ tự động mở ra. Tuy nhiên, bạn có thể đóng hộp thoại Field List bằng cách nhấn vào nút Field List trên Ribbon Options (và nhấn vào một lần nữa để mở nó ra lại).

    xử lý dữ liệu trong excel

    Nhấn vào nút này để đóng hoặc mở hộp thoại Field List

    Cũng giống như mọi hộp thoại khác, bạn có thể di chuyển Field List tới bất kỳ nơi nào bạn thích bằng cách nhấn vùng tiêu đề của hộp thoại và rê chuột đi, hoặc thu nhỏ nó lại hay mở rộng nó ra bằng cách nhấn và kéo các cạnh biên.

    Bình thường, hộp thoại Field List hiển thị danh sách các Field ở phía trên, và sắp xếp 4 vùng dữ liệu sẽ hiển thị trong PivotTable ở bên dưới. Bạn có thể thay đổi sự sắp xếp này bằng cách nhấn vào cái nút nằm ở phía trên bên phải của hộp thoại Field List, và chọn 1 trong 5 kiểu hiển thị từ menu mở ra:

    xử lý dữ liệu trong excel

    Chọn các kiểu hiển thị khác cho hộp thoại Field List

    Hộp thoại Field List còn có một tùy chọn nữa, nằm ở góc dưới bên trái: Defer Layout Update, giúp bạn tạm ngưng việc tự động cập nhật dữ liệu trong PivotTable.

    xử lý dữ liệu trong excel

    Nhấn vào nút này để tạm thời ngưng chức năng tự động cập nhật cho PivotTable

    Bình thường, tùy chọn này không được bật. Bất kỳ những thay đổi nào của bạn trong hộp thoại Field List (di chuyển, thêm, xóa các field, thiết lập định dạng số cho một field nào đó, v.v...) sẽ được cập nhật ngay tức khắc trong PivotTable. Nếu bạn có môt PivotTable lớn, phức tạp, nhất là khi PivotTable sử dụng nguồn dữ liệu từ bên ngoài Excel, việc tự động cập nhật này sẽ diễn ra khá chậm chạp và mất thời gian. Bạn có thể bật tùy chọn Defer Layout Update để tạm ngưng việc tự động cập nhật, và sau khi đã hài lòng với cách bố trí các Field vào các vùng dữ liệu, bạn nhấn nút Update để tất cả những thay đổi của bạn được cập nhật cùng một lúc.





    Bài viết khác

    Phím Tắt Và Thủ Thuật trong excel 2010 - phần 2

    Bài viết sau đây chúng tôi xin giới thiệu tới các bạn các phím tắt thông dụng và các thủ

    Phím Tắt Và Thủ Thuật trong excel 2010 - phần 2

    Phím Tắt Và Thủ Thuật trong excel 2010 - phần 1

     Bài viết sau đây chúng tôi xin giới thiệu tới các bạn các phím tắt thông dụng và các

    Phím Tắt Và Thủ Thuật trong excel 2010 - phần 1

    Bài 12: Định Dạng Trang Và In Bảng Tính trong excel 2010

    Bài viết giới thiệu đến các bạn phương pháp định dạng trang và cách in bảng tính trong

    Bài 12: Định Dạng Trang Và In Bảng Tính trong excel 2010

    Bài 11: Đồ Thị Trong Excel

    Bài viết giới thiệu tới các bạn cách tạo và xử lý đồ thị trong excel 2010.

    Bài 11: Đồ Thị Trong Excel

    Bài 9: Khai thác xử lý dữ liệu trong excel 2010 - phần 1

    Như các bạn đã biết Excel là phần mềm chuyên về xử lý dữ liệu là chính lên bài viết

    Bài 9: Khai thác xử lý dữ liệu trong excel 2010 - phần 1

    Bài 8: Hàm xử lý văn bản và dữ liệu excel

    Bài viết giới thiệu về các hàm xử lý văn bản và dữ liệu trong excel 2010. Chúng ta hãy

    Bài 8: Hàm xử lý văn bản và dữ liệu excel

    Bài 7: Hàm toán học và lượng giác trong excel

    Bài viết giới thiệu về các hàm toán học và hàm lượng giác trong excel 2010. Chúng ta hãy

    Bài 7: Hàm toán học và lượng giác trong excel

    Bài 6: Các Hàm Quản lý Cơ sở dữ liệu, ngày tháng và Danh sách

    Bài viết giới thiệu với các bạn về danh mục các hàm quản lý cơ sở dữ liệu và danh

    Bài 6: Các Hàm Quản lý Cơ sở dữ liệu, ngày tháng và Danh sách

    Bài 5: Các hàm trong excel 2010

    Bài viết giới thiệu về các hàm trong excel 2010 với các hàm cụ thể: hàm thống kê, hàm phân

    Bài 5: Các hàm trong excel 2010

    Bài 4: Giới thiệu công thức và hàm trong excel 2010

    Cũng giống như excel 2007, excel cũng có các công thức và hàm.Bài viết sau đây sẽ giới thiệu

    Bài 4: Giới thiệu công thức và hàm trong excel 2010

    Tự học Office Word

    Bài 7: Một số mẹo hay trên word 2010
    Bài 7: Một số mẹo hay trên word 2010
    Cũng giống như word 2007, word 2010 cũng có những mẹo vặt rất hay mà chúng ta hãy cùng nhau tìm hiểu ngay sau đây.
    Bài 6: Cách sử dụng phím tắt trong Word 2010
    Bài 6: Cách sử dụng phím tắt trong Word 2010
    Tuy không khác về word 2007, nhưng chúng ta hãy điểm qua về cách sử dụng các phím tắt trong microsoft word 2010.
    Bài 5: Hỗ trợ xử lý trong Word 2010
    Bài 5: Hỗ trợ xử lý trong Word 2010
    Bài viết sau đây sẽ giúp các bạn làm quen với các hỗ trợ xử lý trong  microsoft word 2010
    Bài 4: Thao tác với bảng biểu trong word 2010
    Bài 4: Thao tác với bảng biểu trong word 2010
    Bài viết giới thiệu về các thao tác làm quen, thực hành với bảng biểu trong word 2010
    Bài 3: Thực hiện chèn các đối tượng trong microsoft word 2010
    Bài 3: Thực hiện chèn các đối tượng trong microsoft word 2010
    Thực hiện chèn được các đối tượng về các ký tự đặc biết, cách chèn hình ảnh và hiệu chỉnh hình vẽ, hiệu
    Bài 2: Thực hiện định dạng văn bản trên word 2010
    Bài 2: Thực hiện định dạng văn bản trên word 2010
    Để tạo ra một văn bản đẹp, thẩm mỹ, tính chính xác cao, chúng ta hãy thực hiện các bước sau về định dạng văn bản
    Bài 1: Thao tác căn bản trên Word 2010
    Bài 1: Thao tác căn bản trên Word 2010
    Tuy không có nhiều thay đổi so với phiên bản word 2007 nhưng chúng ta hãy lướt qua nhưng thao tác cơ bản trên word 2010
    Microsoft Word 2010 sự khác biệt
    Microsoft Word 2010 sự khác biệt
    Có lẽ nhắc đến office thì người dung không còn xa lạ về bộ công cụ này của Microsoft nữa nhưng mỗi một phiên bản office
    Bài 15: Chèn các đối tượng đồ họa trong Office Word 2019
    Bài 15: Chèn các đối tượng đồ họa trong Office Word 2019
    Trên bản office word 2019 hỗ trợ rất nhiều các đối tượng để chèn vào văn bản, như các đối tượng Shapes từ các phiên
    Dùng công thức tính toán trong bảng Office word
    Dùng công thức tính toán trong bảng Office word
    Mặc dù Office word không phải chuyên về bảng tính như Office excel nhưng vẫn hỗ trợ chúng ta một vài hàm tính toán cơ bản
    Bài 14: Bảng biểu trong Office word 2019
    Bài 14: Bảng biểu trong Office word 2019
    Bảng biểu là một dạng không thể thiếu trong soạn thảo văn bản, chúng ta sẽ đi tìm hiểu và làm việc về bảng biểu trên
    Bài 13: Chèn ký tự đặc biệt trong Office word 2019
    Bài 13: Chèn ký tự đặc biệt trong Office word 2019
    Việc soạn thảo văn bản nhiều khi chúng ta cần phải chèn mốt số ký tự đặc biệt để văn bản của chúng ta đẹp và
    Bài 12: Tính năng tự động chuyển đổi văn bản (AutoCorrect) trong Office Word 2019
    Bài 12: Tính năng tự động chuyển đổi văn bản (AutoCorrect) trong Office Word 2019
    Đây là tính năng tự động chuyển đổi văn bản trong word (AutoCorrect Office Word 2019), nó giúp bạn soạn thảo văn bản nhanh
    Bài 11: Tìm kiếm và thay thế văn bản (Find & Replace)
    Bài 11: Tìm kiếm và thay thế văn bản (Find & Replace)
    Chức năng Find & Replace giúp bạn tìm kiếm văn bản hoặc có thể tìm kiếm văn bản và thay thế nó bằng một văn bản khác
    Bài 10: Tạo chữ cái lớn đầu đoạn văn bản (Drop Cap) trong Office Word 2019
    Bài 10: Tạo chữ cái lớn đầu đoạn văn bản (Drop Cap) trong Office Word 2019
    Chức năng này của office word 2019 giúp tạo chữ cái đầu đoạn văn bản lơn có thể bằng 2 hoặc 3 dòng văn bản giống với
    Bài 9: Chia cột văn bản trong office word 2019
    Bài 9: Chia cột văn bản trong office word 2019
    Office word 2019 cũng giống phiên bản cũ có tính năng Columns để giúp người dùng dễ dàng chia văn bản của mình thành nhiều
    Bài 8: Soạn thảo công thức toán học trong office word 2019
    Bài 8: Soạn thảo công thức toán học trong office word 2019
    Trong văn bản nhiều trường hợp phải soạn thảo các công thức liên quan đến toán học như các biểu thức tính toán, hàm
    Bài 7: Thiết lập Tab trong office 2019
    Bài 7: Thiết lập Tab trong office 2019
    Để định dạng một văn bản nhiều khi chúng ta cần phải dùng Tab để văn bản được đẹp hơn cũng như chuyên nghiệp hơn
    Bài 6: Kiểu chữ (style) trong Office Word 2019
    Bài 6: Kiểu chữ (style) trong Office Word 2019
    Khi các bạn có các định dạng khác nhau và muốn sử dụng lại những định dạng đó mà không cần phải thực hiện lại
    Bài 5: Định dạng đoạn văn bản trong Office Word 2019
    Bài 5: Định dạng đoạn văn bản trong Office Word 2019
    Phần này các bạn phải chọn một đoạn (vùng) văn bản để thực hiện các chức năng, đầu tiên chúng ta tìm hiểu các chức
    Bài 4: Định dạng văn bản trên office word 2019
    Bài 4: Định dạng văn bản trên office word 2019
    Các bạn hãy nhìn hình ảnh bên trên để hiểu rõ hơn về các vị trí mới của các thanh menu, thanh công cụ … trong office word
    Bài 3: Soạn thảo văn bản trên Office Word 2019
    Bài 3: Soạn thảo văn bản trên Office Word 2019
    Vì là phần mềm soạn thảo văn bản lên chủ yếu là văn bản (Text) rất nhiều, khi đã nhập văn bản rồi các bạn sẽ tiến
    Bài 2: Tìm hiểu Office word 2019
    Bài 2: Tìm hiểu Office word 2019
    Tuy office word 2019 có thay đổi về giao diện nhưng về cơ bản nó vẫn giống với các phiên bản về trước chỉ là cách bố
    Bài 1: Giới thiệu OFFICE WORD 2019
    Bài 1: Giới thiệu OFFICE WORD 2019
    Phần mềm Microsoft office Word là một trong những phần mềm khá phổ biến hiện nay, còn được biết đến với tên khác là

    Tự học Powerpoint

    Tùy biến, hiệu chỉnh bài thuyết trình trong PowerPoint 2010 - phần 1
    Tùy biến, hiệu chỉnh bài thuyết trình trong PowerPoint 2010 - phần 1
    Bài viết trình bày một số tuỳ biến và hiệu chỉnh bài thuyết trình sao cho linh hoạt và đẹp mắt hơn bằng cách sử dụng
    Bài 5: Xây dựng nội dung bài thuyết trình trong PowerPoint
    Bài 5: Xây dựng nội dung bài thuyết trình trong PowerPoint
    Bài viết trình bày từng bước xây dựng một bài trình diễn với đầy đủ tất cả các thành phần thông dụng
    Bài 4: Tạo bài thuyết trình cơ bản trong powerpoint 2010 (phần 2)
    Bài 4: Tạo bài thuyết trình cơ bản trong powerpoint 2010 (phần 2)
    Bài viết trình bày cách tạo một bài thuyết trình mới của PowerPoint, và một số thao tác cơ bản đối với bài thuyết trình
    Bài 3: Tạo bài thuyết trình cơ bản trong powerpoint 2010 (phần 1)
    Bài 3: Tạo bài thuyết trình cơ bản trong powerpoint 2010 (phần 1)
    Bài viết trình bày cách tạo một bài thuyết trình mới của PowerPoint, và một số thao tác cơ bản đối với bài thuyết trình
    Bài 2: Giới thiệu và làm quen với Office PowerPoint 2010 (Phàn 2)
    Bài 2: Giới thiệu và làm quen với Office PowerPoint 2010 (Phàn 2)
    Bài viết giới thiệu tới các bạn về powerpoint và các ứng dụng. Chúng ta hãy cùng
    Bài 1: Giới thiệu và làm quen với Office PowerPoint 2010 (Phàn 1)
    Bài 1: Giới thiệu và làm quen với Office PowerPoint 2010 (Phàn 1)
    Tuy không khác cơ bản với powerpoint 2007, nhưng chúng ta hãy cùng nhau tìm hiểu về powerpoint

    Thủ thuật máy tính

    Tập thể thao nâng cao sức khỏe