Môn: Thực hành quản trị trên máyMỤC TIÊU• Học xong phần này sinh viên sẽ

Môn: Thực hành quản trị trên máy

MỤC TIÊU

• Học xong phần này sinh viên sẽ

• Hiểu và biết được đặc điểm, công dụng, ích lợi và

sự cần thiết của các loại báo cáo

• Tạo được báo cáo tổng hợp nhóm (SubTotal)

• Biết cách tổng hợp, phân tích và tổ chức dữ liệu

GVHD: Ths. Lê Nhị Lãm Thúy Email: thuylnl@gmail.com

trong bảng tính để tạo báo cáo PivotTable

NỘI DUNG

1. Khái niệm

2. Trình tự tạo báo cáo

3. Khai thác thông tin trên báo cáo

I. Báo cáo tổng hợp nhóm (SubTotal)

BÀI 2

1. Tổng quan

2. Trình tự tạo PivotTable

3. Tổ chức dữ liệu

4. Hoàn thiện PivotTable

GVHD: Lê Nhị Lãm Thúy

II. Báo cáo PivotTable

Môn: Thực hành quản trị trên máy

I. Báo cáo tổng hợp nhóm (SubTotal)

① Khái niệm

??

Grand & Sub Total Grand & Sub Total 1. Tổng giá trị đặt hàng? Giá trị đặt hàng theo từng

nhân viên ??

2. Số lượng đơn đặt hàng? Số lượng đơn hàng mỗi

Phần I. Báo cáo phân nhóm SubTotal

nhân viên đã thực hiện ??

Order List Order 1 Order List Order 1

1. Khái niệm 2. Trình tự báo cáo 3. Khai thác thông tin trên báo cáo

Order n Order n

Order 2 Order 2

I. Báo cáo tổng hợp nhóm (SubTotal)

① Trình tự

ChuChuẩẩn bn bịị

SubTotals dùng tổng hợp dữ liệu theo nhóm

Yêu cầu tổ chức dữ liệu: danh sách

– Nhãn mỗi cột đặt ở dòng đầu tiên.

– Mỗi cột có cùng kiểu dữ liệu

– Không có dòng, cột rỗng chen giữa danh sách

Xác định cột phân nhóm và giá trị cần tổng hợp

VVíí ddụụ: gi : giáá trtrịị đ đặặt ht hààng theo t ng nhân viên ng theo từừng nhân viên





t phân nhóómm

 ccộột phân nh  ccộột tt tổổng hng hợợpp

GVHD: Lê Nhị Lãm Thúy

Môn: Thực hành quản trị trên máy

I. Báo cáo tổng hợp nhóm (SubTotal)

① Trình tự

Ra lRa lệệnhnh

ThThựực hc hàànhnh

Cột phân nhóm

SubTotals dùng tổng hợp dữ liệu theo nhóm trong Tạo báo cáo SubTotal danh sách 1. Doanh số của nhân viên

Hàm tổng hợp

2. Doanh số bán được theo các

Cột chứa giá trị tổng hợp

quốc gia

1.1. SSắắp thp thứứ ttựự theo c

t phân theo cộột phân

3. Số lượng đơn đặt hàng mỗi nhân viên đã thực hiện

nhnhóómm

4. … … … …

2.2. ChChọọn danh s n danh sááchch Data, SubTotals 3.3. Ra lRa lệệnh nh Data, SubTotals

I. Báo cáo tổng hợp nhóm (SubTotal)

① Khai thác thông tin

Drill down Drill down

Outline symbol

Phần II. Báo cáo PivotTable

Show and Hide details

GVHD: Lê Nhị Lãm Thúy

1. Tổng quan 2. Trình tự 3. Tổ chức dữ liệu 4. Hoàn thiện PivotTable

Môn: Thực hành quản trị trên máy

1. Tổng quan

Doanh sốố nhân viên m Doanh s

nhân viên mỗỗi năm

i năm & & năm c

a nhân viên năm củủa nhân viên

Kaleidoscope ??? Kaleidoscope ???

NămNăm

NhânNhân viênviên

Nhân viên  Năm Năm Nhân viên

Nhân viên Năm  Nhân viên Năm

1. Tổng quan

Công cụụ ttổổ chchứức vc vàà ttổổng hng hợợp đa chi Công c

PivotTable PivotTable

p đa chiềềuu YearYear

PivotTable

Kaleidoscope

• Vật liệu: data

• Vật liệu: Giấy vụn

• Kết quả: thông tin (view)

• Kết quả: hoa văn

• Cách làm: xoay bảng

• Cách làm: xoay kính

• Hoạt động: chủ định theo

• Hoạt động: ngẫu nhiên

yêu cầu

PivotTable … present comparisons, reveal patterns and relationships, and analyze trends.

Countries Countries

Salesperson Salesperson

Doanh sốố nhân viên m Doanh s

nhân viên mỗỗi nămi năm theo t

theo từừng qu

c gia ng quốốc gia

GVHD: Lê Nhị Lãm Thúy

Môn: Thực hành quản trị trên máy

2. Trình tự tạo PivotTable

② Làm việc với wizard

How To? How To?

PivotTable Wizard Công cụụ PivotTable Wizard Công c

11 11 22 1. Đặt câu hỏi. Sau khi đã sẵn sàng, hãy làm

1.Ask yourself questions.

theo các bước sau: 2. Sử dụng PivotTable Wizard. 33 3. Dàn dựng PivotTable. 1. Click chọn trong danh sách. 33 2. Ra lệnh Data, PivotTable and

2.Use the wizard.

PivotChart Report.

3.Lay out the PivotTable

22 3. Sau khi cửa sổ wizard được

report.

The PivotTable and PivotChart Wizard

mở, click nút Finish.

2. Trình tự tạo PivotTable

① Đặt câu hỏi

② Làm việc với wizard

Hãy xHãy xáác đc địịnh cnh cáái mi màà bbạạn cn cầần bin biếếtt

ThiThiếết lt lậập mp mặặc đc địịnhnh Wizard sẽ sử dụng các thiết lập

mặc định bao gồm Hãy bắt đầu bằng cách tự hỏi xem bạn cần biết cái gì:

• Giá trị đặt hàng mỗi nhân

viên đã thực hiện? • Sử dụng dữ liệu trong danh sách của Excel hoặc trong database.

The PivotTable and PivotChart Wizard

Sales figures that go on and on

GVHD: Lê Nhị Lãm Thúy

• Giá trị đặt hàng theo từng • Tạo thêm 1 trang tính mới. quốc gia? • Chuẩn bị vùng “layout area” • …… để “tổ chức” PivotTable.

Môn: Thực hành quản trị trên máy

2. Trình tự tạo PivotTable

③ Dàn dựng PivotTable

Layout Area Layout Area

PivotTable Layout PivotTable Layout PivotTable Layout là “bản thiết kế” của báo cáo PivotTable.

Wizard tạo trang tính mới có sẵn 3 đối tượng: 4 khu vực:

– Page Field

• Danh sách PivotTable Field

– Row Field

– Column Field

List.

– Data Items

The new worksheet, ready for a PivotTable report

Use drop areas to control the report organization.

• Khu vực PivotTable layout.

• Thanh công cụ PivotTable.

Bạn drag các field từ Field List vào khu vực Layout để xác định cấu trúc báo cáo

2. Trình tự tạo PivotTable

③ Dàn dựng PivotTable

Field List Field List

c Row vs Column Field Khu vựực Row vs Column Field Khu v

Nếu bạn drag vùng Salesperson

vào trong

• Khu vực Drop Row Fields

Column headings become fields.

PivotTable Field List chứa tất cả tên các cột của bảng dữ liệu gốc như: Country, Salesperson, Order Amount, Order Date, và OrderID. Here  mỗi nhân viên được bố trí trên 1 hàng

GVHD: Lê Nhị Lãm Thúy

Lưu ý: Mỗi cột dữ liệu  vùng • Khu vực Drop Column Fields Here  mỗi cột báo cáo chứa dữ liệu của một nhân viên. (field) có cùng tên.

Môn: Thực hành quản trị trên máy

2. Trình tự tạo PivotTable

③ Dàn dựng PivotTable

c Data Items Khu vựực Data Items Khu v

i linh hoạạtt

Thay đổổi linh ho Thay đ Khi yêu cầu thay đổi  bạn có

thể

• Thêm field mới vào báo cáo Để trả lời câu hỏi “Giá trị đặt hàng mỗi nhân viên đã thực hiện?” cần phải sử dụng dữ liệu của 2 vùng:

• Bỏ bớt field không cần thiết. 1. Salesperson  tên nhân viên

Creating the PivotTable report view

The PivotTable report

 vùng Row field

2. Order Amount  giá trị đặt hàng  vùng Data Items • Thay đổi cấu trúc (pivot) bằng cách drag các field từ vị trí này sang vị trí khác.

2. Trình tự tạo PivotTable

③ Dàn dựng PivotTable

c Data Items Khu vựực Data Items Khu v

• Chỉ được drop field “số ” vào khu

ThThựực hc hàànhnh 1. Tạo mẫu báo cáo ”report

vực Data Item view”.

2. Bố trí dữ liệu trong PivotTable

Layout. • Kết quả báo cáo sẽ được hiển thị ngay sau khi khai báo khu vực Data Item. 3. “Pivot” báo cáo.

4. Tạo “page views”.

Bài thực hành (Pivot1.xls)

5. Drag fields ra khỏi báo cáo. • Các field trong khu vực Data Item được xử lý mặc định bằng phép cộng.

GVHD: Lê Nhị Lãm Thúy

• Bảng Pivot có thể in ra giấy, copy vào Word, hoặc xuất lên trang Web.

Môn: Thực hành quản trị trên máy

3. Tổ chức dữ liệu

Yêu cầầu đu đốối vi vớới di dữữ liliệệu gu gốốcc Yêu c

ĐĐểể ddààn dn dựựng th

nh công PivotTable ng thàành công PivotTable

1. Chuẩn bị tốt dữ liệu

1. Hàng đầu tiên chứa tiêu đề cột và không trùng lắp.

2. Xác định đúng câu hỏi  chọn đúng field cần dùng 2. Không có hàng/ cột rỗng.

3. Thả các các field vào đúng 3. Mỗi cột chỉ chứa cùng 1 kiểu chỗ dữ liệu

4. Phải xóa các subtotal và

grand totals đã tạo trước đó (nếu có) Và cuối cùng: đừng sợ làm sai khi tổ chức khu vực layout.

3. Tổ chức dữ liệu

ĐĐịịnh vnh vịị ccáác field trong v

ng Layout c field trong vùùng Layout

1. Row Fields trình bày từ trên

ThThựực hc hàànhnh 1. Tạo báo cáo PivotTable

xuống, mỗi giá trị chiếm 1 dòng report

2. Column Fields trình bày theo 2. Thay đổi cách dàn dựng

3. Thao tác trên Page fields. hàng ngang, mỗi giá trị chiếm 1 cột. 4. Cách dùng lệnh để di 3. Page Fields trình bày theo

Bài thực hành (Pivot2.xls)

chuyển các fields vào khu vực dàn dựng trang, để phân tổ hay tách dữ liệu

4. Data Items  nơi số liệu được

GVHD: Lê Nhị Lãm Thúy

hiển thị và tổng hợp

Môn: Thực hành quản trị trên máy

4. Hoàn thiện PivotTable

② Sử dụng Toolbar

PivotTable toolbar PivotTable toolbar

Always display Items Always display Items

Fields setting Fields setting

Include hidden Items in Totals Include hidden Items in Totals

Hide/Show Field List Hide/Show Field List

Các xử lý thường gặp

ReFresh Data ReFresh Data

Show Detail Show Detail

Hide Detail Hide Detail

Chart Wizard Chart Wizard

Format Report Format Report

4. Hoàn thiện PivotTable

③ Sắp thứ tự ① Xử lý ô màu xám

Sort and Top 10 Sort and Top 10

a tên field Ô mÔ mààu xu xáám chm chứứa tên field

1. Click vào ô bất kỳ chứa tên

2. Click nút PivotTable trên 1. PivotTable tự tạo ra các ô có nền xám khi thả các field vào vùng layout. PivotTable toolbar.

2. Nội dung trong các ô này 3. Chọn mục Sort and Top 10.

Excel inserts gray boxes containing field names.

4. … được tạo tự động dựa theo tên field và nơi bố trí

3. Có thể thay đổi nội dung để

Salespersons sorted by name, alphabetically.

Salespersons sorted by

amount sold.

GVHD: Lê Nhị Lãm Thúy

báo cáo rõ ràng hơn

Môn: Thực hành quản trị trên máy

4. Hoàn thiện PivotTable

③ Sắp thứ tự ⑤ Cập nhật dữ liệu

Refresh Data Refresh Data

Manual AutoSort options –– Manual AutoSort options 1. Click ô trên row/column field

muốn đổi chỗ

2. Drag đường biên ô đó sang

Original sales figures for

Peacock.

Updated sales figures for

Peacock after refreshing data.

vị trí mới • Click nút Refresh Data trên PivotTable toolbar để cập nhật báo cáo khi có sự thay đổi

4. Hoàn thiện PivotTable

④ Định dạng số ⑥ Tổng hợp dữ liệu

Format Number Format Number

Không chỉỉ ccóó SUMSUM Không ch

1. Click 1 ô bất kỳ trong vùng số

liệu.

1. Click 1 ô trong vùng số liệu.

2. Click nút Field Settings trên

PivotTable toolbar.

3. Chọn phương thức xử lý trong

3. Click mục Number và chọn

mục Summarize by

Amounts sold without

dạng currency.

Sum is just one of the

formatting.

Amounts sold with

currency formatting.

ways PivotTable reports can summarize numerical data.

GVHD: Lê Nhị Lãm Thúy

Môn: Thực hành quản trị trên máy

4. Hoàn thiện PivotTable

⑦ Group & Show Detail

o Quý TTạạo bo bááo co cááo Quý

ThThựực hc hàànhnh

Để bổ sung thông tin thời gian vào

báo cáo

1. Đổi tên vùng.

1. Hiển thị lại PivotTable Field List

2. Thay đổi thứ tự sắp xếp

(nếu cần)

3. Định dạng lại số.

2. Drag vùng Order Date vào vị trí

4. Thay đổi cách thức tổng hợp

trước Salesperson trong vùng row field

số liệu.

3. Đổi tên vùng Order Date thành

5. Hủy bỏ báo cáo.

Qtr

Display the PivotTable Field List by clicking/Right Click inside the report.

Bài thực hành (Pivot3.xls)

Lưu ý: giá trị trong cột Order Date là ngày bán, chưa được quy đổi ra quý.

4. Hoàn thiện PivotTable

⑦ Group & Show Detail

Phân nhóómm Phân nh

1. Right-click vùng Qtr

2. Chọn mục Group and Show

Detail

3. Chọn Group, sau đó chọn

Quarters.

Pivot Table với nhiều Row Field

 Sales are displayed for each salesperson by quarter

GVHD: Lê Nhị Lãm Thúy

Môn: Thực hành quản trị trên máy

4. Hoàn thiện PivotTable

⑦ Group & Show Detail ⑧ Inner–Outer Row Field

TrưTrướớc vc vàà sausau

1. Doanh thu mỗi quý của

nhân viên

Công dụụng ng Công d Group and Show Detail cho phép phân tích báo cáo ở mức tổng hợp hay chi tiết hơn

• Có khả năng group dữ liệu thời gian theo seconds, minutes, hours, days, …

• Để hủy nhóm: Right—Click vào field, chọn 2. Doanh thu mỗi nhân viên theo quý Group and Show Detail, rồi chọn Ungroup

4. Hoàn thiện PivotTable

⑧ Inner–Outer Row Field ⑧ Inner–Outer Row Field

i niệệmm

KhKháái ni Khi có nhiều row fields thì:

Row & Column Row & Column Để hiển thị thời gian, bạn cần thêm vùng Order Date mới.

• Duy nhất 1 “inner row field” ở

sát vùng dữ liệu.

Vấn đề là để vùng Order Date vào vị trí row hay column? • Còn lại là các “outer row

– Trước “Salesperson”? hay

– Sau “Salesperson”?

fields”. Nếu đưa vào vị trí “row” thì:

A PivotTable report before and after the Order Date field is added to it.

 Các giá trị Outer field ngoài

– Bất kỳ?

Whether a row field is an inner or outer field determines how the data is displayed (repeated).

cùng xuất hiện 1 lần.

 Các giá trị ở field khác sẽ xuất

GVHD: Lê Nhị Lãm Thúy

hiện lặp lại khi cần.

Môn: Thực hành quản trị trên máy

4. Hoàn thiện PivotTable

⑨ Thêm Data Item

ThThựực hc hàànhnh

1. Sử dụng 2 row fields

VVấấn đn đềề Báo cáo trước cho biết giá trị đặt hàng của nhân viên.

2. Tạo báo cáo quý

3. Pivot báo cáo để quan sát

thông tin khác

PivotTable reports can do

more than add.

Bài thực hành (Pivot4.xls)

Bài tham khảo (Acrobat Reader)

4. … Yêu cầu  xem cùng lúc thông tin về doanh số và số lượng đơn đặt hàng của nhân viên

4. Hoàn thiện PivotTable

⑨ Thêm Data Item

Đưa nhiềều field v Đưa nhi

u field vàào khu v

c Data Items o khu vựực Data Items

Đưa thêm vùng Order Amount vào

khu vực row field

Khi đưa vùng Order Amount vào

When the Order Amount field is dragged onto the data area again

lần thứ 2, Excel sẽ:

PivotTable với nhiều Data Items

Cộng giá trị đặt hàng 1 lần nữa

và đặt tên Sum of Order Amount2.

Tạo thêm tiêu đề “Data” cho cả 2 vùng trong khu vực số liệu

GVHD: Lê Nhị Lãm Thúy

Môn: Thực hành quản trị trên máy

4. Hoàn thiện PivotTable

⑨ Thêm Data Item ⑪ Đổi cách xử lý

ĐĐếếm sm sốố đơn h

đơn hààngng

TTíính tnh tỷỷ trtrọọng gi

ng giáá trtrịị đ đặặt ht hààng cng củủa a

1. Click vào 1 trong các ô Sum

of Order Amount2.

nhân viên nhân viên 1. Chọn 1 ô trong vùng Order # 2. Click nút Field Settings trên

Show data as a

Changing the summary

2. Click nút Field Settings trên PivotTable toolbar. PivotTable toolbar.

percentage of the total instead of as the number of orders.

from Sum to Count in the PivotTable Field dialog box.

3. Chọn Count trong danh sách Summarize by. 3. Chọn Sum thay vì Count. 4. Click mục Options, chọn % of total trong danh sách Show data as.

4. Hoàn thiện PivotTable

⑩ Format bảng Pivot

AutoFormat AutoFormat

ThThựực hc hàànhnh 1. Tổng hợp 1 vùng dữ liệu

1. Click vào trong báo cáo. theo nhiều cách khác nhau

2. Click nút Format Report trên PivotTable toolbar 2. Đếm số đơn hàng và thể hiện ở dạng tỷ trong.

PivotTable with Autoformat

3. Định dạng report.

type Report 5

Bài thực hành (Pivot5.xls)

Bài tham khảo (Acrobat Reader)

3. Chọn 1 trong các định dạng thích hợp trong cửa số AutoFormat.

ChChỉỉ ddùùng Autoformat sau ng Autoformat sau khi đã Pivot xong bááo co cááoo khi đã Pivot xong b

GVHD: Lê Nhị Lãm Thúy

Môn: Thực hành quản trị trên máy

4. Hoàn thiện PivotTable

⑫ Calculated field ⑬ SubTotal

Thông tin không cóó trong d Thông tin không c

trong dữữ liliệệu gu gốốcc

On / Off On / Off 1. Click vào bên trong vùng muốn tắt/ mở subtotal

Yêu cầu thêm thông tin về hoa hồng cho nhân viên bán hàng 2. Click mục PivotTable trên

The report with

PivotTable toolbar, và đánh dấu hay xóa dấu √

subtotals.

The report with

How much bonus should each salesperson receive?

automatic subtotals turned off.

 Field công thức dùng để tính toán và hiển thị các giá trị không có trong dữ liệu gốc

4. Hoàn thiện PivotTable

⑫ Calculated field

How to? How to?

ThThựực hc hàànhnh

1. Click nút PivotTable trên

PivotTable toolbar

2. Chọn Formulas, và click 1. Pivot báo cáo để thấy rõ khi nào nhân viên hưởng tỷ lệ hoa hồng là 10% hoặc 7% Calculated Field. 2. Tính tiền hoa hồng 3. Nhập công thức vào hộp thoại 3. Sử dụng giá trị hoa hồng Insert Calculated Field

The Fields list in the dialog box contains the fields from the report, which you can use in your formula.

Bài thực hành (Excel 2003)

trong công thức bên ngoài bảng Pivot Lưu ý: Sử dụng tên field trong

Bài tham khảo (Acrobat Reader)

GVHD: Lê Nhị Lãm Thúy

bảng dữ liệu gốc thay vì tên đã đổi. Ví dụ công thức =IF('Order Amount'>150000,12%,7%) =IF('Order Amount'>150000,12%,7%)

Môn: Thực hành quản trị trên máy