Bài giảng Tin học ứng dụng trong kinh doanh - Chương 4: Tổng hợp, phân tích và thống kê số liệu

ươ

Ch Ổ

ng 6.  Ợ

T NG H P, PHÂN TÍCH

Ố Ệ

Ố

VÀ TH NG KÊ S  LI U

Ứ

CH C NĂNG SUB TOTAL

ừ

ị

ỉ

ườ

ng đ

 Ý nghĩa: Th ng kê, tính toán theo t ng nhóm d  li u trên nh ng  ữ ệ ố ủ ơ ở ữ ệ c ch  đ nh c a c  s  d  li u. Excel s  t

ượ ữ ệ

ẽ ự ộ ầ

ả ổ

ợ

ố

ỗ

ệ

ữ  đ ng phân tách  tr ặ ế nhóm d  li u, tính toán và chèn k t qu  t ng h p và đ u ho c cu i  m i nhóm.  Cách th c hi n: ự ắ ướ

ườ ầ ế ơ ở ữ ệ  S p x p c  s  d  li u theo tr ng nào c n  B c 1:

phân nhóm đ  th ng kê, tính toán;

ể ố ọ ơ ở ữ ệ ướ ở ụ ọ Ch n c  s  d  li u sau đó m  m c ch n  B c 2:

2

Data\ Sub Total;

Ứ

CH C NĂNG SUB TOTAL

Xác đ nh các thông s  sau: B c 3:

ọ ể ng dùng đ  phân nhóm;

ườ ọ ự ố ng nào mu n th c ố ị ướ  At each change in: Ch n tr ườ  Use Function: Ch n hàm đ  th ng kê; ể ố ọ  Add Sub Total to: Ch n nh ng tr ữ

ệ ố hi n th ng kê trên đó;

ế ữ  Replace current subtotals: Thay th  nh ng giá tr   ị

3

ệ subtotal hi n có;

Ứ

CH C NĂNG SUB TOTAL

 Page Break between

ế

ố ữ

ọ groups: ch n, n u mu n  ắ ấ ạ t o d u ng t trang gi a các  nhóm;

ế

ở

4

ữ  Summary below data:  ổ ọ ố ch n, mu n dòng t ng k t  ỗ ượ  sau m i nhóm; đ c chèn   Remove All: Xóa t ấ ả t c   ệ nh ng Subtotal hi n có;

Ứ

CH C NĂNG CONSOLIDATE

ữ ệ ươ ứ ế ợ ố ng  ng Ý nghĩa: Th ng kê, k t h p trên các d  li u t

ấ ế ế ề ặ ả ủ c a nhi u Sheet ho c Book và k t xu t k t qu  vào 1

ợ ổ Sheet t ng h p nào đó.

ệ Cách th c hi n: ự

ướ ọ ơ ở ữ ệ  Ch n c  s  d  li u; c 1:

ở ụ  B  B c 2: ọ Data\ Consolidate; xác đinh

5

ướ M  m c ch n  ố các thông s  sau:

Ứ

CH C NĂNG CONSOLIDATE

ữ ệ

ế

ể ạ ấ ả t c  các ph m vi d  li u tham gia k t

t kê t

 Function: Ch n hàm đ  tính; ọ  Reference: Xác đ nh ph m vi d  li u; ữ ệ ị  All reference: Li ạ ệ

h p.ợ

 Use label in:

ầ

ữ ệ ộ ữ ệ

ầ

 Top Row: Dùng dòng d  li u đ u tiên bên trên là tiêu đ  c t; ề ộ  Left Column: Dùng c t d  li u đ u tiên bên trái là tiêu đ   ề

dòng;

6

Ứ

CH C NĂNG CONSOLIDATE

ố ạ ế ọ Create links to source data: ch n, n u mu n t o liên

ồ ế ớ ữ ệ

ạ t kê danh sách các file Workbook. ượ c ch n t ọ ừ Reference vào

k t v i d  li u ngu n; Browse: li ệ Add: B  sung 1 ph m vi đ ổ All reference.

7

ạ ị  xác đ nh sai). Delete: Xóa 1 ph m vi( N u l ế ỡ

Ứ

CH C NĂNG CONSOLIDATE

ọ Ch n hàm  ể đ  tính

li

t kê DS

ệ các file

ệ

ị

Thêm

Xác đ nh  ạ ph m vi  ữ ệ d  li u

t

Li

Dùng dòng d  ữ ầ li u đ u tiên  bên trên là tiêu  ề ộ đ  c t ộ ữ ệ Dùng c t d  li u  ầ đ u tiên bên trái  ề là tiêu đ  dòng

Xóa ạ  ph m vi

ệ ấ t kê t ả c  các  ữ ạ ph m vi d   ệ li u tham  ế ợ gia k t h p

8

ế ạ t o liên k t  ớ ữ ệ v i d  li u  ngu nồ

Ứ

CH C NĂNG CONSOLIDATE

Chú ý

ể ạ ề ố

ườ ủ ả ng và các

ệ ệ ả ố

9

Ta có th  t o khung c a b ng th ng kê tr ki n là các tên tr li u này ph i hoàn toàn gi ng h t các tên tr ủ ơ ở ữ ệ (nhãn) c a c  s  d  li u(tr t t ớ ướ c, v i đi u  ủ ơ ở ữ Label(nhãn) c a c  s  d   ệ ườ ng và các  ể ậ ự  có th  khác).

Ổ

Ố

Ợ T NG H P TH NG KÊ VÀ  Ố Ệ Ớ PHÂN TÍCH S  LI U V I PIVOTTABLE

Ý nghĩa:

ộ ụ ể ổ ợ

m t c  s  d  li u.  ả ạ ộ

10

ề ạ ạ Pivot Table là m t công c  đ  t ng h p và phân tích  ố ệ ừ ộ ơ ở ữ ệ s  li u t ữ ệ Ngoài kh  năng nhóm d  li u theo d ng hàng c t  (2D) Pivot Table còn t o báo cáo d ng 3 chi u.(3S)

Ổ Ố

Ợ T NG H P TH NG KÊ VÀ  Ố Ệ Ớ PHÂN TÍCH S  LI U V I PIVOTTABLE

Ch c năng: ứ

ạ

ổ

 T o ra b ng t ng k t ế ả

ổ ứ ả

ữ ệ

ằ

ươ

 T  ch c b ng d  li u b ng ph

ng pháp

kéo thả

ọ

 L c và nhóm d  li u ữ ệ

ồ ừ

 V  bi u đ  t ẽ ể

PivotTable

11

Ổ Ố

12

Ợ T NG H P TH NG KÊ VÀ  Ố Ệ Ớ PHÂN TÍCH S  LI U V I PIVOTTABLE

Ổ Ố

Ợ T NG H P TH NG KÊ VÀ  Ố Ệ Ớ PHÂN TÍCH S  LI U V I PIVOTTABLE

ệ Th c hi n: ự

ố ệ ố  Quét kh i ch n vùng s  li u  ọ

 Data\ Pivot Table and PivotChart Report ­>xu t ấ

ệ ộ hi n h p tho i ạ Pivot Table and PivotChart

13

Wizard:

Ổ

Ố

Ợ T NG H P TH NG KÊ VÀ  Ố Ệ Ớ PHÂN TÍCH S  LI U V I PIVOTTABLE

ầ ượ

ừ

ướ ủ

ả

ạ

L n l

t làm theo t ng b

ụ c c a quá trình t o b ng. C

 B

ướ ể ư

c 2:  th  nh  sau:

ạ ữ ệ

ọ

ể Step 1: Ch n lo i d  li u dùng đ  phân tích trong vi c t o b ng.

ữ ệ

ệ ạ ả ơ ở ữ ệ ủ

 Microsoft Excel List or Database: d  li u là c  s  d  li u c a

Excel.

ơ ở ữ ệ ủ

ộ

ươ

ng

 External data source: D  li u là c  s  d  li u c a m t ch ữ ệ

trình khác.

ữ ệ

ề

 Multiple consolidation ranges: D  li u là nhi u vùng c  s  d   ơ ở ữ

li u.ệ

ế

 Another Pivot Table: d  li u là b ng Pivot Table khác. ữ ệ  Nh n nút  ướ ấ

ả c ti p theo:

ể Next đ  sang b

14

Ổ

Ố

Ợ T NG H P TH NG KÊ VÀ  Ố Ệ Ớ PHÂN TÍCH S  LI U V I PIVOTTABLE

ạ

ả

ữ ệ

ị

ọ

 Step 2: Xác đ nh ph m vi d  li u dùng đ  t o b ng ị  Step 3: Xác đ nh v  trí đ t c a  ị  Ch n nút Layout (có th  th c hi n sau khi t o Pivot table)  ố ổ

ợ ị

ể ạ ặ ủ Pivot Table,  ạ ệ ể ự  Xác đ nh Field nào ch a d  li u mà ta mu n t ng h p  ứ ữ ệ ầ ộ ớ

ồ

ho c phân tích r i kéo vào ph n DATA ế ặ ọ ị ắ ằ  Ch n giá tr  s p x p theo c t v i nhãn n m phía trên,

kéo vào vùng Column

ắ ế

15

ả ả ố  S p x p theo hàng, kéo th  vào Row  Th ng kê theo vùng, kéo th  vào Page

Ổ

Ố

Ợ T NG H P TH NG KÊ VÀ  Ố Ệ Ớ PHÂN TÍCH S  LI U V I PIVOTTABLE

ộ ố ố ị Step 3: Options: Xác đ nh thêm m t s  thông s  khác. Ta

ố quan tâm các thông s  sau:

Name: đ t tên cho  ặ Pivot table. Format options: g m các thông s : ố ồ

 Grand total for columns: Ch n, n u mu n hi n th   ị

ộ ổ ọ ở dòng t ng c ng cho các dòng ể ố ướ ả i b ng. ế  bên d

ự ệ ệ ố ọ

ự ộ ạ ế  đ ng cho b ng. ị đ nh d ng t

16

ữ ủ  AutoFormat table: ch n, n u mu n th c hi n vi c  ả  Merge labels: canh gi a các nhãn c a Pivot Table.

Ổ

Ố

Ợ T NG H P TH NG KÊ VÀ  Ố Ệ Ớ PHÂN TÍCH S  LI U V I PIVOTTABLE

ữ ạ ậ ự ạ ả ị i tr t t l các đ nh d ng c u  Preserve formatting: gi

b ng.ả

c a b  c c b ng.

ổ ậ ự ủ ố ụ ả ậ ắ ộ ẽ ể ị t n i dung s  hi n th

ỗ khi có l

ậ ắ ộ ẽ ể ị t n i dung s  hi n th  khi  Page layout: thay đ i tr t t  For error values; show: b t/t ị i sai tr .  For empty cells; show: b t/t

17

có ô r ng.ỗ

Ổ

Ố

Ợ T NG H P TH NG KÊ VÀ  Ố Ệ Ớ PHÂN TÍCH S  LI U V I PIVOTTABLE

Data Option:

ư

ồ ố

ạ ữ ệ External lên

ệ ả b ng hi n hành.

ị

ể

t tính năng  ộ

ậ ắ ế ạ t t

i m t ô th ng kê nào đó trên b ng

drilldown­ tính năng  ố ả i đó.

• Enable drilldown: b t/t hi n th  thông tin chi ti ấ Pivot Table khi ta nh n kép chu t t ậ

ở

i d  li u khi m  ra.

• Refresh on Open: c p nh t l

 Data source options: g m các thông s  sau • Save datawith table layout: l u lo i d  li u

ộ ạ ậ ạ ữ ệ ộ ố

ố  External data options: m t s  thông s  khác đ i v i  ố ớ

18

ạ ữ ệ lo i d  li u External.

Ổ

Ố

Ợ T NG H P TH NG KÊ VÀ  Ố Ệ Ớ PHÂN TÍCH S  LI U V I PIVOTTABLE

ệ ỉ Hi u ch nh PivotTable:

ổ ấ ổ

19

ế ụ ể ệ ử ụ ỉ  Thay đ i c u trúc PivotTable  Thay đ i hàm t ng k t cho Field ổ  S  d ng thanh công c  đ  hi u ch nh

Ổ

Ố

Ợ T NG H P TH NG KÊ VÀ  Ố Ệ Ớ PHÂN TÍCH S  LI U V I PIVOTTABLE

ổ ấ Thay đ i c u trúc PivotTable

ử ụ 

ệ

ừ ả  S  d ng PivotTable Wizard: Ch n nút PivotTable  ế ế t k  PivotTable ọ ầ ố ươ ứ ng  ng t  Kéo th  Field: Kéo th  Field t

ươ ứ ng  ng trên PivotTable và

20

ượ ạ ế ự PivotTable Wizard, th c hi n gi ng ph n thi ả Field List vào các vùng t ỏ ng ố i n u mu n b  đi. c l

Ổ

Ố

Ợ T NG H P TH NG KÊ VÀ  Ố Ệ Ớ PHÂN TÍCH S  LI U V I PIVOTTABLE Thay đ i hàm t ng k t cho Field ổ

ổ

ủ ọ ộ ế ả ạ i vùng Data c a PivotTable, ch n

 Kích chu t ph i t Field Setting..

21

ọ  Ch n hàm tính toán