Hướng dẫn thực hành kế toán máy tính trên Excel 2010: Phần 2

Chia sẻ: Le Na | Ngày: | Loại File: PDF | Số trang:198

Thêm vào BST

Báo xấu

219
lượt xem 65
download

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

Phần 2 Tài liệu Thực hành kế toán máy tính trên Excel 2010 có nội dung trình bày các vấn đề như xoay dữ liệu, thêm dữ liệu từ những nguồn khác, thiết kế dữ liệu để xem và in, xuất dữ liệu sang những ứng dụng khác, phân loại và lọc dữ liệu và các nội dung khác.

Chủ đề:

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

Lưu

Nội dung Text: Hướng dẫn thực hành kế toán máy tính trên Excel 2010: Phần 2

162 Chương 6: Xoay dữ liệu: Xem dữ liệu bằng một cách khác XoQỤ dữ liệu: Xem dữ iệu bồng một cách khác Những điếm chính K Tim hiển một Pivoííable là qì M Tạu một Pivollable cơ bản M Mở rộíiỊị một PivotTabỉe M Hiển fbị dữ liộii bằng đồ ibị hàng cách sử clựìKị PwoiChart K hỏi đ ộ n g Câu hỏi 1. Khi nào bạn nên sử dụng một PivotTable? 2. Môi quan hệ giữa m ột PivotTable và m ột bảng (table) bình thường là gi? 3. Ba quy tắc để tổ chức đừ liệu được sử dụng trong mỗi PivotTable là gì? 4. Một d ata cube là gì? 5. Mối quan hệ giữa các ô giá trị và các ô tiêu đề trong m ột PivotTable là gì?
Chương 6: Xoay dữ liệu: Xem dữ liệu bằng một cách khác 163 6. Cách (lỗ n h ấ t để thêm các trường (field) vào một PivotTable là gì? 7. Làm th ế nào bạn tbav đối các tiêu đề hàng và cột trong một PivotTable? 8. Các công thức thông thường n h ất được sử dụng trong PivotTable là gì? 9. M ột P ivotC hart là gì? 10. Tâ’t cả loại biểu đồ có th ể được sứ dụng để tạo m ột PivotC hart hay không? Tăng tôc Nếu b ạn dă hoàn toàn hiếu một PivotTable là gì? Cách tạo một PivotTable tro n g Excel 2010, cách vẽ biếu đồ dừ liệu được xoay (piv- oted), và tại sao bạn muôn sử dụng một PivotC hart hoặc một PivotTable, hãy nhảy đố^n chương tiếp theo. ghiôn cứu đã cho thấy đầu óc con người có th ể xử lý mỗi lần chỉ rấ t nhiều m ảnh dữ liệu duy nhất. Nếu bạn cố tóm tắ t dừ liệu tro n g m ột bảng tính lớn đáng kể, khả năng là người xem báng sẽ bị quá tải thông tin , làm cho khó hiểu dừ liệu m ột cách có ý nghĩa. Làm th ế nào b ạn làm việc với các lượng lớn dữ liệu? Bạn có th ể làm m ột số việc: Tạo bằng tay m ột số sheet tổng k ết cố tín h toán dữ liệu; tạo m ột outline để k ế t nhóm các phần tử dữ liệu trờ th à n h các phần khả dụng và sau đó tổng k ết chúng; hoặc xem dữ liệu từ m ột cấp độ cao hơn đê’ bạn có th ể th ấy được bức tranh lớn hơn. Mặc dù bạn có thố' tạo bằng tay một khung xem câV độ cao hơn bằng viộc tạo một chuỗi các công thức cho mỗi loại phép tính, nhưng tiến trìn h cló m ất thời gian và dễ sai sót. Thay vào đó, trong chương này bạn học cách yêu cầu Excel tổng kêL dữ liệu một cách khá linh hoạt băng việc sử dụng các chức năng pivoting. Các PivotTablo cho phép bạn xem dữ liệu Excel m ột cách gọn hởn và dẽ hiểu hon. Chúng giúp bạn kê^t hợp dữ liệu và th ấy những môl quan hộ giừa các giá trị m ột cách nhanh chóng và ciễ dàng. Các PivotTable cho phép bạn xử lý dữ liệu bằng một cách mới m à bạn không cần phải làm một lượng lớn công việc. Excel có hai tín h năn g pivoting (xoay): » l^ivolTobỉe: 'iạo các iibÓDì (lữ Ìiệìi sao cho bạn có thể thấy dử ỉiệiì dưới dạniị các bảng (table). M Ị^ívolCbart: sửciinig kổ! cptả PivotTabìe đ ễ lạo trực tiếp một biềii đồ tử d ữ liộii được xoav-
164 Chương 6: Xoay dư liệu; Xem dữ liệu bằng m ột cách khác Trong chương này bạn b ắt đầu bằng việc học về m ột sô’ ứng dụng thông thường của dữ liệu được xoay. Sau đó sau Idii bạn hiểu bạn có thể sử dụng m ột PivotTable như th ế nào, bạn có th ể tạo m ột PivotTable cơ bản, cải tiến nó và vỗ biếu đồ nó trong m ột PivotC hart. G ió i th iệu c á c P ivotT able Một câu hỏi mà bạn thường nghe thấy khi được đưa cho dữ liệu để phân tích là "Dữ liệu này có nghĩa gì?" Nếu lượng dữ liệu m à bạn có đủ nhỏ, bạn có th ể tổng k ết dữ liệu bằng tay và tạo m ột biểu đồ để tr ả lời câu hỏi. Tuy nhiên, khi bạn làm việc với các lượng lớn dừ liệu, lượng dừ liệu có th ệ làm bạn choáng ngợp. Trong nhiều trường hợp c.ó nhiều dữ liệu đến nỗi tổng k ết nó một cách có ý nghĩa th ì Idiông th ể được. Một giải pháp là xây dựng m ột PivotTable Excel cho dữ liệu. Khi bạn thay dổi dữ liệu từ m ột file ílat th à n h m ột PivotTable, bạn có th ể tổng kết nhanh đừ liệu được bằng m ột scí cách b ấ t kỳ. Khi bạn pivot dữ liệu, bạn không quay nó sang m ột phía; thay vào đó bạn thay đổi góc mà bạn xem dừ liệu. Bạn "bước lùi lại" từ nó. Thay vì xem những gì mà mỗi dữ liệu phải th ể hiện, bạn lắp ghép các mẫu dữ liệu lại với nhau và để Excel phân tích nhừng gì m à các câu hỏi dữ liệu có th ể cho bạn biết. Dữ liệu mà sẽ được sử dụng trong m ột PivotTable được tổ chức sao cho Excel có th ể dễ hiểu dữ liệu nào có quan hệ. Giả sử bạn làm việc với dữ liệu hàng tồn kho cho m ột cửa hiệu v ật cưng và b ạn có dữ liộu sau đây: Item Namo D ate Sold C ustom cr Name Cc Cat 1-Jan Jane 45 Dog 1-Feb Janc 33 Bircl 1-Mar Jane 46 Cat 1-Jan Joe 45 Dog 1-Feb Joe 33 Bird 1-Mar Joe 46 Cat 1-Jan Mike 45 Dog 1-Feb Mike 33 Bird 1-Mar Mike 46 Cat l'Ja n Harry 45 Dog 1-Feb Harry 33 Bird 1-Mar Harry 46 Cat 1-Jan Sam 45 Dog 1-Feb Sam 33 Bird 1-Mar Sam 46
Chưđng 6: Xoay dữ liệu: Xem dữ liệu bằng một cách khác______________ 165 Thông tin cho m ột món hàng cụ thố được bán trên m ột khách hàng cụ th ể và m ột ngày cụ thể. D anh sách các rocord (bán ghi) râ t lớn với các giá trị trù n g lặp trong m ột số cột. Bây giờ bạn sẽ làm việc với chỉ 15 record này. Trong trường hợp này, bạn có thế pivot dữ liệu để b iết mỗi món hàng đó đã được bán khi nào, nó dược bán cho ai, và bạn kiếm được bao nhiêu. Để làm điều đó, sử dụng tính nàng Excel PivotTable để tạo một sô' bảng khác nhau từ cùng một dữ liệu. Báng đầu tiê n có th ể liệt kê các tên món hàng trong m ột cột và các tên khách hàn g trong m ột hàng ngang qua phần trê n cùng. Trong các ô riêng lẻ, bạn có th ể đ ặt một phép tính cho tổng lượng tiền mà một khách hàng chi cho món hàng đó trong một khoảng thời gian riêng biệt. Bảng được m inh họa trong hình 6-1. Sức m ạnh của PivotTablc là sau khi bạn tạo một PivotTable, bạn có th ể dễ dàng điều chỉnh nó để tìm số món hàng được mua mỗi ngày. Để làm điều này cho ví dụ cửa hàng vật cưng, bạn thay đổi các hàng để chứa các ngày tháng; các cột đè chứa tên của các món hàng, và các ò để chứa sô' lượng món hàng, tạo ra bảng được m inh họa trong hình 6.2. 2 3 S u m ồ f C c s t o f lỉe m ỉiowỉahpU * M ik » » tn ra l 5 46 46 4Ỏ 4Ố 46 223Ỉ0 0 5 C j 1 45 45 45 4 4 S 5 25 2 7 Pog 3 i 33 33 3J 33 165 s G r a n d T o tầ i Ĩ2fi 12A UA 620 9 10 ỉi 12 13 1 4 15 I< « » Ị Hình 6 .1 : MỘI ví dụ P iv o tĩa b le đơn giản C o u n t o f lic in N a rrc ’ I I R o w U b «K * b ir d c ã t õ c g G r^ n Q T o tn l Ỉ-Jâ ft s s 1 -F « b 5 5 1 M ac 5 5 cn»nứ r o u ỉ ỉ s i lỉ V Hình 6 . 2 : PivotTable Irông giông như bảng này sau ktií bạn Ihực hỉện các thay flổí
166 Chường 6: Xoay dữ liệu: Xem dữ liệu bằng một cách khác Nếu bạn muốn tạo hai báng này ngay từ đầu, tiên trìn h m â t thời gian và rô't cuộc bạn lặp lại mộL số công việc của mình. Bằng việc cho phép Excel tạo PivotTable cho bạn, bạn dành một cliút thời gian tạo bảng và chỉ một vài giây thay dối nó. Một tín h năng hữu dụng của một PivotTable là bạn có th ể th iết lộp nó với một mẫu dữ liệu nhỏ, chắng hạn như trong vi dụ cứa hàng vật cưng và sau đó mở rộng các record trong báng. Miễn là bạn giữ tấ t cả record trong bảng, Excel tự động cập nhật các PivoLTable để Idiớp với các rccord trong bảng dữ liệu. Bạn cũng có th ể sử dụng các PivotTable dể tạo các bảng tổng k ết có thế’ inở rộng của các tổ hợp đữ liệu. Ví dụ, nếu bạn muố^n b iết các món hàng dược bán mỗi ngày và ai đã mua chúng, bạn có th ể thêm thông tin khách hàng vào các hàng. Nếu ngày tháng được thể hiện trước tiên, dữ liệu được tín h tổng theo ngày tháng và sau đó được tín h tổng con theo khách hàng. Nếu thông tin khách hàng được liệt ké trước tiên, dữ liệu được tính tống theo cách kia. Thực t ế C ác P ivotTabỉe được sử dụng rộng rãi trong thế giới thương mại và nghiên cứu: M C á c n h ả ì ì C ị h ĩ ê i i c ứ u S ì i c ì ụ n g c h i i n g đ ố q u y ế t đ ị n h t o à n b ộ k ố l q i ú i i K ị h i ô i ! c i ĩ n v à c á c ( l o a i i h I i q h i ộ p s ử c l ụ i i q c h ú n g í í ể b i ế t a i dã IIIIU nbữnỊị I I'à khi nào. Bi iVhữiiíị n h à qiiảiì lý cũiiíisử clụiig các PiroiTahk’. Các kh ả ììãnq lổníị kổị của chiiiìíỊỉ>i!Íp các Iibà (Ịiiài! lýliiib loún dễ dàng hưìi c ú c c h i p h i C ÍII'Ự C l ậ j > n g â n Ẵ < ì c h r à c á c c h i p h í i b ự c t ế i'0 k h ó p Iihií ìbổ Iiào ỵo vúi mội ỉiíợiHị dữ liện rộii(ị lún. n Các Piì'0 iTabIe ^ÌIỈỊ) các \VưhiìUisicr biốt ÍIÌ dừ liệu theo cỉõi khách Ibciiiì (Ịiinn cúa bọ. Iỉũiif> riộc xoay d ữ liộii /heo dõi, bọ có Ihổ biốt a ìc Íiriiií> i/ào (ỉã (ỈIỈỢC xem nhiều Iihẩl rà các ỉim iq nào ll.ìậ/ìi ch í không clKỢc ÍÌIÌI Ihấv. C ũng có Ihể có hàng trăm ứng dụng khác, chỉ bị giới hạn b ô i trí tưỏng lượng của bạn và khả nãng bạn tạo nhiếu mối quan hệ giữa dữ liệu. Tạo P ivotT ab le c ơ b ản Trong chương này bạn sẽ làm việc với dừ liệu lịch sử từ m ột chi nhánh khách sạn. Chi nhánh có ba bất động sản khách sạn: ơackson Hotel, Miguel Ranch, và Pengueno Place. Mỗi bất động sản có 4 bộ phận: các phòng, n h à hàng, các khoảng cách tiện nghi cung cấp thực phẩm và hội nghị. Như bạn có th ể đoán, việc cố có được toàn bộ bức
Chường 6: Xoay dữ liệu: Xem dữ liệu bằng rnột cách khác______________ 167 tranh ìịch sử về thông tin tài chính của chi nhánh có th ể gây choáng ngỢp. Bạn đà dược cho (lừ liệu thu nhập và chi phí hàng quý cho các năm 2002 đốn 2006 trong một sheet, và tiếp đó có 300 record của dừ liệu trong nó. Bạn cú th ể tạo các công thức dô tìm và tổng k ết các lượng chi tiêu trong mỗi hạn g mục và trong mỗi nàm nhưng điều đó đòi hỏi một khoảng thời gian. Thay vào đó, hây tạo PivotTable để học mọi th ứ mà bạn cần biết. Có đưực d ữ liệu tr o n g đúng định dạng Bước đầu tiên trong việc tạo PivotTable là bảo đảm rằng dữ liệu có Jỉìột định dạng mà tính năng PivoíTable có t h ể hiểu. Trong trường hợp này, bạn phải tuân theo một vài quy tắc cơ bản: n Kbòiiíị có các (lòiiq irốnq có ílìổxiiâí hiệu ímtìg ciữ liệĩi. NỖII bạn có lìiội dòng /rổnq thì PiroỉTahh khú>ifị sửcỉựìiíị d ữ liộ n aaìi dòỉìg trống í/ó. Mồi CÔI p b â ĩ có Dìộí liéii cĩâ: Các Uén dể giúp bạn đặt d ữ liệu trong (V/c hànĩị rà cộ! củn Piiĩìtíahle. s J)ũ' liậ ii p h á i n b à l qìiá ii. 17 dụ. lìổ ii hạn d ậ l tôn cho các C]iiỳ ỉà Q ĩ, bạn p h á i liiòn Ihaiìi chiến chúng theo cách dó. Excel không hiổt rằng Q ỉ t>ịổiỉg u h K Q l hoặc Q naner 1. Tham kháo bẵng cửa hàng vật cưng (lược sử dụng trong các ví dụ trong phần trước. Chú ý rằng tấ t cả kết quả mà bạn nhận được là dây scí. Nếu bạn làm việc bằng số, bạn có thế thực hiện nhiều thao tác khác nhau trùn dữ liệu đế tương thích nó. Tuy nhiên, nếu bạn làm việc với dữ liệu dịnh dạng text, thao tác duy n h ất mà bạn có th ể thực hiện trê n dữ liệ u l à d ế m sô’ l ầ n m à m ộ t m ẫ u d ữ liệ u x u ấ t h i ệ n . Tạo PivotTable Sau khi bạn có dược dừ liệu theo (lúng định dạng cho m ột PivotTable, việc tạo PivotTable là khá dễ dàng. Bạn sử dụng các tín h năng rê và th ả cứa Excel đế xử lý các mẫu dữ liệu và tống kết chúng bằng nhiều cách khác nhau. Trong bài tập này, bạn tạo một PivotTable cơ bản từ dữ liệu từ Red Sunset Hotels và sử dụng PivolTable này đế quyê't định thu nhập và các chi phí hàng năm cho mỗi bộ phận khách sạn: 1. Mơ file RedSunsetliotelChain.xlsx. 2. Chọn ò A l. Chọn I n s e r t > T ab les > P iv o tT a b le . Ilộp thoại Create PivotTable xuất hiện như được m inh họa trong hình 6.3.
168 Chướng 6: Xoay dữ liệu: Xem dữ liệu bằng một cách ktiác C rea te P ivotT eblt Chôose Ihe ddU thât you want lo òOặĨỴie o d t ^ € or rânge Xb R « đ ley àfig : Ịỳie d n e x te r n d l d d t d ^ r c e CKoose w heíc vcu w ant th« PiVOtTôbíe repoít to be pỉaced o New VYorksheel Ễxistỉng V/o*ksheel tỡ C â b o n : OK CârtCêl Hình 6 .3 : Hộp thoại Create PivotTabie. Bởi vì ô m à bạn đã chọn (ô A l) nằm trong m ột bảng, trường Table/ Range được điền vào tên bảng. Ngoài ra, Excel đã chọn nút radio New W orksheet, nghĩa l à PivotTable sẽ xuất hiện trong m ột w orksheet mới. Nếu ô mà bạn đã chọn đã không nằm trong m ột bảng, có lẽ bạn đã sử dụng nút grid (nằm bên phải trường Table/Range) để chọn dây ô cho PÌvotTable. Đ iể u b ạ n c ẩ n b iế t Nếu bạn đang làm việc với dữ liệu từ một cơ sỗ dữ liệu SQL, bạii đang địuh nghĩa nguồn dĩí liệu bhng việc chọn nút radio thứ hai trong hộp choỊỊi Create PivocTable: Sử dụng một External Data Source (nguồn dữ liệu bèn ngoài). Tùy chọn này cho phép bạn chọn các recorđ từ một cơ sở dữ liệu bêu ngoài và import dữ liệu dưói dạng một OLAP (Online Analytucal Processing). Trong trường hỢp này, Excel xem xét dữ liệu bên ngoài nhưng làm việc với nó như tliể nó nằm trong bảng tính thực sự. (Để biết thêm thông tin về việc iàm việc với các cube OLAP, tìm chủ đề Help vò các công cụ OLAP trong nhóm Tools trên tab PivotTabỉe Tools). 3. N hấp OK để tạo sheet mới chứa PivotTable. Sheet mới mở ra và khung tác vụ PivotTable Field L ist xuất hiện ở phía m àn hìn h như được m inh họa trong hình 6.4.
Chương 6: Xoay dữ liệu: Xem dữ liệu bằng một cách khác 169 “ V** *« • « /* • '■ ; ii''f H " f i» . “ * -. . • '. ^r*l >3 " . V -^W ^ *• . I • « v * '* '>* " r»i»-*-i«i 1 *^ .(»* - — — Jni ' ‘ * . . . •( *%*C!f^ . t -V >•. »j • /. fĩ. ã'i .% L _ J cz=! >«»« )#v«Mwe '* « l}ul « ** lx v « ỉ ti« J e « t r o n 1*1» 9 " .i< lt« 6 (« Cw ế^«» »< c »«a Mwr / KAiv1«fl0 C « ^ i* (a ;; Li t 7
170 Chường 6; Xoay dữ liệu: Xem dữ liệu bằng một cách khác không muốn tính tổng các nàm. Thay vào đó bạn muốn phân tích dữ liệu thoo niím. B ạn cần di chuyển các giá trị năm lừ danh sách Values trong khung tác vụ đến danh sách Column Labels. 7. Chọn hộp Sum of Year trong danh sách Values, và sau đó rê nó đô'n vùng Column Labol. Bây giờ các năm dược th ể hiện ngang qua phần trên cùng cúa PivolTable. Cột cuối cùng trong bảng trìn h bày tổng lớn. Tiếp theo, bạn cho Excel biết bạn muốn để các giá trị dựa vào dừ liệu nào. 8. Chọn hộp kiếm Incomo. Chú ý rằn g các ô trong PivotTable bây giờ dược điền vào tổng của thu nhập cho mỗi nám , như được m inh họa trong hình 6.5. jsmn C b ls db * ÌO OÌ »04 ?U0I 2 »06 W G (J fl4 T 0 L « J IdC kvũn H M « I IMŨUỈ 1 SỈ6 9Ở4|Ị» 9ữỉl Ữ Ỉ2 Ế6 466S24; IS«>2 9ÒỈ 69 i&42đỉ 2 Ỉ5 C 5 : 527135 ÌO? liOt: «ĩ C 9íiUi ;» t7 ò :SỈỈ3S 162Ẩ9! 171 711 4 0ỉ ;ả « 4 5«27 ÌỈ776J W 1 18S377S U 7900 7ũâ9;e 49101? 6 1 ỈỈ7 1 W tĩì9 ĩ 11531 «9S75 iỉorỉs ? 6 i7 ẵ36Ci7 lỉT ứ a t IỈ2 4 4 2 U41M 69Cia? Â « ỉU jtỉr • Ĩ7 ữ ib í ÌOÌÌÌÌ 2 0 2 : jỉ IIỈÌC 9 Ỉ l l'.n 9 í 7«4ãỉ 1123ậ: S?7S^4 U 7 im ìtM 798149S ĩr.ỗU 2ÌM7Ỉ ĨV ữ 2?njj 3322C5 9^ 6 17C2Ỉ2Ỉ C < **r6 » o *> rc Iỉ7jac ỉ«èỉ: ỈJ Ỉ9 5 Ỉ nmì : « ư Ì ỈC ? ÌỈỈ79Ĩ 907» i ỉỉỉ jW M ! M7 S1 C U 5ns 9 5 1M ís : u i
Chương 6: Xoay dữ liệu; Xem dữ liệu bâng rnộ't cách Khác 171 Bạn có thồ’ học dược nhiỏu điồu h'ín tư (iữ liệu này. Bước đầu tiên m à bạn có th ể muôn thực hiộii ià bạn muốn loại bỏ việc phân h ạ n g mục theo n ă m sao cho l;íl Cíí nhữ ng gì b ạ n th ấ y là th u n h ậ p cố định so với các chi phí. 10. Hủy chọn hộp Iciểm Year Lrong ỉchung tác vụ PivotTable hoặc rê hộp Yoar từ vùng Column I^abels sang vùng Field List. Báng tống kct và layout Lrường cho nó rlược m inh họa trong hình 66 . . ỉ - . ■ ih ỉU i * * Ị i : - « « « 9 M 19 '■ » « * . * j ► • ) 1 . D « p aH fn p n l 1 I # b w )n H n lr l ** 1 C V íitr Ì é ii.ỉ. i> K « f n « ' ' S .^ U Ỉ > r « p « < iil4 v r D í ;• M g v e l A ỉM c U ' 1' Í 3 < : : J i ỉ \ ĩ 0 7 ' , . 't: lĩ 1 ' ' * »5•/?. i ị t, , iiii'-- ì « / „ c a » r-i> w * * 1 ICC ĩ ỉĩU J z :: W iìĩi'i ^ A d A iiiM ỉ r «««» 'm e ' ' .. v i ' ís a ii/íi.v i • / [ - 'I ,1 < » ................................................. j Ị ' J Hình 6.6; IV I bảng lổng kết thu nhập và chi phí cho ba khách sạn. IỘ Một th ay đối khác mà có thế bạn muốn thực hiện là xem thu nhập vã chi phi khách sạn theo bộ phận thay vì theo năm. 11. ơ cuối khung tác vụ, rô hộp Departm ent từ vùng Row Labels đến vùng Column Labols. Sự thay đối này tạo ra một báng ngán hơn nhiều nhưng rộng hơn. Thực ra thông tin trong biing này không dỗ hiểu khi nhìn thoáng qua. 12. Hoán đổi các giá trị Hotel trong các cột và các giá trị D epart m ent với các h àn g bằng việc rô mỗi nhãn từ m ột vùng này sang m ột vùng khác ở cuố’ khung tác vụ, i Bảng thu hẹp lại một phần như dược minh họa trong hình 6.7, bới vì tôn cua cúc trường cho các khách sạn ngắn hơn các tê n cho những bộ phận. Bây giờ bạn có th ể thấy các chi phí và thu nhập cho mồi bộ phận theo khách sạn.
172 Chướng 6: Xoay dữ liệu: Xem dữ liệu bằng m ột cách khác - c I '' ‘ l C o lu A in * S u m »1 S u in o l s > .d i l ì o u l Sui ia c x & o i t H o r « i ^ n iv o io M l£ u tl K ^ncn n o p C ỉc c n 9 2 rtìì íĩẾ O iĩ S ỈỈỈỈ2 4 S Ỉ7 it i2 ỹ ĩ2 9 i Ĩ6 6 3 9 6 t / / á r< li* ĩ i s m ( .9 0 1 4 7 9 1 Ĩ* U 9 5 7 0 Í3 Ĩ ị9 fi* u ĨĨU Ĩit 6 90è371 1 2 7 0 :9 ; S ^ ÌĨO S 2 iĩtìị) 9 iM ỉn i S IM $ Ì 6 Ỉ9 L S Ỉ ÌS !4 ĨS 9 «25^1 6 ÌS S O O K ỈH iỏ ỈC ũ n t i d TOUI 2«7e7W Ỉ9 đ n ư 4 4 ? 6 9 ft f0 9 íU d i I0 S & I9 S 11 l ỉ lỉ u In lí u / Ị >2 ii ỉ ĩ l ĩ í ' Hình 6 .7 ; P iv o tĩa b le này so sánh Ihư nhập và các chi phí trong m ồi bộ phận theo khách sạn. Mỏr r ộ n g c á c PivotT able Ngoài việc chỉ Lính tổng dữ liệu trong bảng, các giá trị PivoTable có th ể dựa vào hầu như bất kỳ công thức hoặc hàm m à bạn có th ể tưởng tượng. Trong bài tập trong phần này bạn thay đổi các phép tính chõ PivotTable bằng m ột số cách khác nhau để học từ dữ liệu này. Trong bài tập này, bạn điều chỉnh PivotTable để tìm các lượng chi phí và thu nhập trung bình thay vì các tổng: 1. Quay trở về biểu đồ mà bạn đã tạo trong phần trước, hoặc mở íìle RedSunsetH otelChainl.xlsx. 2. T rên sheet PivotTablc, chọn ô B4. Nhấp phải ô và chọn S u m m a- riz c D a ta By > A v e ra g e từ monu ngừ cảnh. Tiêu đề cột như được m inh họa trong ô B4 thay đổi từ Sum of Income th àn h Average of Oncome. Ngoài ra các giá tr ị cho các ô trong các cột Income thay đổi thành giá trị trung bình cho bộ phận đó cho mỗi khách sạn. Ngoài ra, những giá trị trong cột H được thay đổi để th ể hiện Tota! Average of Income. Nếu bạn xem các mục trong khung tác vụ trong vùng Values, nó cũng hiển thị Average Of thay vì Sum Of. 3. Chọn ô E4. Nhấp phải ô và chọn S u m m a riz e D a ta B y > A ver- a g e từ menu ngữ cảnh.
Chương 6: Xoay dữ liệu: Xem dừ liệu bằng một cách khác 173 Các ô giá trị còn lại trong bâng thay đổi để chứa các giá tr ị trung bình thay vì các tổng như được minh họa trong hình 6.8. C o ltB iin L i h e l ^ * li>CMn« A v K « ( e 0 fE x p e r> d ỉQ K 9 t ị * l«rli\04n H»T«I Ml|uelRỉiKh PtrDiit^íioPl^â V^gưeỊRỉnch pị 66Ỉ n 4 » 41 ẳỉứ ỉỉ7 Ỉ Ỉ Ỉ 7 7 0SỖ24 ỉ8 sỉ,&ỉỉỉ 4 d ã £ 7 Ỉ0 AHU ACS96 Ỉ4 } .> c ; 6 A 7 Ỉ } S 2 0 9 .$ Ỉ 8 4 4 |» s s 3 6 .8 te MS ỉ> ; ỉ3 4 1 4 .4 j: ìỉ 3iỉ98.2^12 3 4 9 S 6 .S & 4 6 2 ỉ7 5 3 & 0 2 d 4 ỉ Seons Ì6 U 1 .9 6 4 6 Ĩ 30:& đỉ|?6 ỉ 37O0S mĩ7.ĩ6ĩu T (» u t ÌÌW M 2 i} Ì iề M .iV iị ỈS Q 7 Ỉ4 4 7 Ữ 6 M Ì7 8 6 4 .9 4 Ỉ Ỉ4 7 « 0 .$ 2 A 4 1 Hình 6 .8 : Các ô thu nhập và chi phí chứa các giá Irị trung bình. Sau khi thực hiện sự thay đổi này, có thể th ây rõ định dạng mặc định cho PivotTable để lại nhiều điều không được hài lòng. Bạn giải quyết vấn đề đó ở bước tiếp theo. 4. Chọn ô B4. Chọn PivotTablc Tools Options > Active Field > Field Settings. Hộp thoại Valuo PiGld Scttings xuât hiện, như được m inh họa trong hìn h 6.9. V d ljtf S e ttiiv g s í":/ S ư rrie: Ir P ro d ix l íiu m t e t P tt r r a t o. Cnd ửc Hộp thoại này là một cách khác để thay đổi các giá trị trong PivotTabỉe. T rên tab Summarize By, bạn thay đổi phép tín h cần
174 Chương 6: Xoay dữ liệu; Xem dữ liệu bằng một cách khác thực hiện cho các ô vô’n được tạo trong phần này của bảng, đây là phán Average of Incomo (các ô B6 đến D9). Bên dưới tab, bạn thấv nút Numbor Form at. 5. N hấp nút Numbor Form at. Hộp thoại Form at Cells xuất hiện, ơ đó bạn có thể chọn b ất kỳ định dạng cho các số được tạo ra bởi PivotTable. T hực t ế Dữ liệu trong P ivotT able cũng có thể cho bạn thấy mối quan hệ giữa thông tin trong m ôi cột. Bạn có thể nhặn dược thông tin này bằng v iệ c sử dụng các m ối quan hệ được th iế t lập trên tab Ihử hai của hộp thoại V aíue Field S ettings. Đ ể th ấ y những mối quan hệ này, xem tab S how V aíues As tro n g hộp thoại V alue Field S ettings. Bạn có thể thấy các điểm khác biệt giữa những giá trị trong cộ t hiện hành và trong các cộ t khác. Ví dụ, khám phá thu nhập cho các bộ phận tại m ỗi khách sạn so với Ihu nhập cho P engueno Place như thế nào: 1. Trong khung tác vụ, nhấp nú t m ũi tên xổ xuống A vera ge of Incom e. Từ danh sách, chọn V alue Field S eltings. Hộp thoại V alue Field S elting s mở ra. 2. Trẽn tab S how V alues. chọn D ifference From Irong danh sách x ổ xuống S how V alues As- 3. Từ hộp đanh sách Base Field, chọn phép so sánh cơ bản; tro n g ví dụ này. là so sánh theo khách sạn. 4. Tử hộp danh sách Base Item nằm bên phải, xác định khách sạn nào mà bạn m uốn so sánh dữ liệu. T rong trường hợp này, chọn P engueno Place. 5. N hấp OK. Sự thay đổi đối với dữ liệu (các ô B6 đến D9) vừa có được được m inh họa trong hình. c&luntn Ljl>els * A v o M g p o f IIKOIIÌP R ow labP Ís • i,i
Chương 6; Xoay đũ liệu: Xem dử liệu bằng một cách khác 175 6. Chọn Currcmcy và nhấp OK, Khi bạn (ịuay trở về hộp thoại Value Kield Settings, nhấp OK lổn nữa. Các ô B6 đốn D9 được định dạng dưới dạng tiền tệ, tương tự đối với phần Total Averago ofIncome, như (lược m inh họa trong hình 610. I/Ị IhCttMti O(f»rcii0icu r w r .k liii( * 1*^ >»>li -• s ' ỉ «1 3 ''. í Ỉ . ' J * J J 0 / 6 S iỉ4 :iỉ.4 :9 « ỉv .. . I '. > ỉ•i.^ :c * ò i? » 5 .5 3 W é 3 Ỉ5 5 5 . ' 5 J 4 J r - i/W '« ; . 'í 5 3 Í Ỉ 4 1 .S .Í 11 > ì'ứ ịix 3 ú ỉỉ7 ,? D 5 t5 i Cm,»t»J ĩ o ì .1» ị ỉ I ỉ , lU I u $ Í» .,0 ? C 4 « ; J 4 7 M > ,S ? 0 4 t Hình 6 .1 0 : Tập hợp giá irị đẩu tiên được định dạng dưới dạng tiển tệ. Chú ý rằn g cúc ô E6 đến G9 và các ò 16 đến 19 cũng là các số thông thường. 7. Trong khung tác vụ, nhấp nút mũi tôn xổ xuông Average of Expenditures. Từ danh sách, chọn ValuG Field Settings. Ilộp thoại Valuc Field Sotlings xuâ't hiện lại cho phép bạn truy cộp thông tin phép tính và dịnli
176 Chương 6: Xoay dữ liệu; Xem dữ liệu bằng một cách khác Av«M lrK n«* ĩi^ldỉ Avet«ỉtữO Ex|i«iKlỉtui«» |ỉi?uf 'N Ỉ IdCk.utr> M tgu Pi H iív h V cogu^ n o S 5 3 .2 M .5 1 ịZ t/i5 ÌM ỈỈ«.S6Ỉ iĩA ,$ Ì2 .$ 9 S5J,*1Ổ.25 $ w . s . s s , l >0 ÌỈS .2 P 2 ,S V iiA iíg i lđ C ẵ ỉỉố Ễ ỉA .ã ỈỈ7 .S Ỉ< .0 3 S33 n ? . w ỉìì.ĩư .íi ỉỉỉ.tỉ3 .c « Ỉ1 S //V 0 > Ố M .RsVi.íiO ‘^ J 6 .K l ; .V Ỉ U ^ .in .a n Ỉ > 4 .7 Ỉ 0 ,Ỉ Ỉ $ ỉ6 .2 « .d 9 ỈỈS .O M .1 2 y s\ w Hình 6 .1 1 : T ấ t cả giá trị (hu nhập và chi phí được dịnh dạng dưới dạng tiền tệ. S ử d ụ n g c á c P iv o tC h a rts đ ể h iển ỉhỊ d ữ ỉiệ u b ằ n g một c ó c h m à n gư òi ta c ó th ể h iểu Các PivotTable là m ột cách hữu dụng để tổ chức dữ liệu, nhưng chúng không phái là m ột phương tiện có tính đồ họa cao để hiổn th ị dữ liệu đó. Tương tự như một biểu đồ trìn h bày rõ dữ liệu trong m ột bảng, mỗi PivotC hart trìn h bày rõ dữ liệu trong một PivotTable. Tạo m ột PivotChart Trong bài tập này, bạn sử dụng cùng m ột dữ liệu cơ bản như trong các bài tập trước. Trong trường hợp này, bạn sử dụng chỉ dữ liệu hàng nàm, không phải tổng dữ liệu thu nhập và chi phí. Bàng việc sử dụng chỉ dừ liệu hàng năm , bạn tạo các biểu đồ rõ hơn, dễ hiểu hơn. Làm theo nhừng bước sau đây để tạo m ột PivotC hart dựa vào PivotTable; 1. Mở nití RedSunsGtHotelChainCharts.xlsx. Chọn ô PivotTable!A6. Bạn sẽ sử dụng bảng này để tạo m ột chuỗi các P ivotC harts cơ bắn. 2. Chọn P iv o tT a b le T ools O p tio n s > T o o ls > P iv o tC h a r t. Hộp thoại In se rt C h art xuất hiện, như được m inh họa trong hình 6 . 12 . Hộp thoại đế tạo m ột PivotC hart giông y như đế tạo các biếu đồ thông thường. Sự khác biệt là việc sắp xếp và hiển th ị dữ liệu đi vào PivotChart.
Chương 6: Xoay dữ liệu: Xem dử liệu bằng rr ột cácli khác 177 - t * 1 J Tv i C T okyỉ> i •! . 'í ’ , ' 1 > ì - 1 •< f ỉ • 1 ( -è i ' , á i ■ • » i 1 ^ ' *. ' ......... * ■ i 1 ' ) \ 1 ’ J yề 1 « i Mk«‘ 1 1 1 , . '< v i * * _ i ; * * 1 1 J j < \ACsl ) í Te»TiC«áCi?S ịt: 4Ỉ Dè*3s.if ok’ * , Cdixei Ị - ......................... - • - • ' Hinh 6.12: Hộp thoại Insert Chart. 3. N hấp OK. Khung tác vụ PivotC hart Piltor P a n G xuất hiện, như được m inh họa trong hìn h 6.13. Khung tác vụ này có ba phần: 3! A sis rieU ls: ú p b ih i lìVìì cùiiỊị, hạn chọìi các tnỉờng lòm các hạng m ục hoặc cãc Iriioìiíị liyc X. Chúinị thường tà các nhóm d ữ liệu từ hiổu dồ. Theo m ạc cíịiìh. !ấi cả Q,iá Irị CÍUI m ỗi hàng m ục con đi(Ợc /hổ hiội/ cho Ii/ỗi hạníị mục . lỉại! cõ thố lọc các m ục n hìn thây cỉiíực bằnỉ> riệc P i v c t C h a r t * i l t a P ê n e nhấp níu 1 1 1 lêii.xố.xiiOiìịỉkờ hôiì Iníòiìtị A c t iv ê P r t l i l ; o n t ỉ i e 111 rà Ị)háiì loại hoặc lọc d ữ Hộu. n h ư hạn (ĩíl bọc Iriíớc íỉõ. V R e p o r t F H lC f B l.e ịịc u d fie l( ls : TroiiQ Ị>h(ìn iKÌy cíín ^ J A x is í t c M t ( C a t e g o r i^ s ) khiiiiiị ỉác rụ. bạii cỏ ibểchọii cácỊ>iá !rị tto te l i!ổ sử d ụ n g cho các Ibdiih lioiiíị hiếu dồ. repytmcrt 'rroníị inỉờiiỊị hợỊ) nảy. các íịiá trị /à lổng ' J L e g « fld r ie l d s ( S « r w s ) của Ihu Iibập rà chi p h i hộ phậii. Dổi vời V a I i \P ĩ m ồi bộ p h ậ n , các giá Irị cỉiỉợcphâii chúi Yà CT Iheo ìiâiii. V a lu e s oflnộ « cm Hình 6 .1 3 : PivotCharl Fj[ter Pane, E x p e n d ií u r e í Valiies: Phầii n à y chứa thông tin từ các ò irontị PivotTable. Tmiiq Irườiiq hợp lĩàỵ. các giá Irị là lổiìỊ’ thu nhập rà các chị Ị)hí cho các (Iiiỳ.
178 Chương 6; Xoay dữ liệu: Xem dữ liệu bằng một cảch khác 4. Nôu khung tác vụ đã không được noo sang bên phải biểu đồ, hây rê khung sang phía bôn phải m àn hình cho đến khi nó neo cô’ dinh. Biểu dồ được th ể hiện đằng sau khung tác vụ nhưng ph ía trước dữ liệu. Bởi vì kích cỡ của biếu đồ làm cho khó đọc, bạn sè di chuyên biốu đồ sang m ột tra n g riêng biệt sao cho bạn có th ể thấy nó rõ hơn. Trước khi làm điều đó, chú ý rằng một số tab ngữ cảnh bây giừ xuất hiện trê n Ribbon như được m inh họa trong hình 6.14. r .'.. -J ■• ' i> k « l P n3t'r> ir( t « : i ỉ H íH f i;* » • r in - i.i, Vr «- « tiđ I • •‘^ V / ĩ ĩ â Ì Hình 6 .1 4 : M ột Ribbon với nhiều lab ngữ cảnh. 5. Chọn P iv o tC h a r t T ools D e sig n > L o c a tio n > M ove C h a rt. Hộp thoại Move C hart xuất hiện. 6. Chọn nút radio New Sheet và đặt cho sheet mới tôn là PivotC hart 1. N hấp OK. Biểu đồ xuâ’t hiện trê n m ột sheot mới, như được m inh họa trong hình 6.15. ĩ « 05 Kz M í!ì:x u ,v i}> : n : X • iv " r . ỉ ' ? .ic ; • í* iỉx « • U i* i • 3XÀ ' Si,/^ ^ ; * ì ì :. Hinh 5 .1 5 : M ộ t PivolChart m ặc úịnh. Biêu đồ n ày có quá nhiều dừ liệu nên khó đọc. Mỗi b ất động sản có tống cộng 40 th a n h làm cho khó th ấy dữ' liệu. Tiếp theo, bạn
Chương 6: Xoay dữ liệu; Xem dữ liệu bằng một cách khác______________ 179 sẽ Lông k ết dữ liệu theo một bước này dê’ tổng k ết biếu đồ dễ đọc hơn. 7. T rong khung tác vụ PivotTabk' Field List, hủy chọn hộp kiểm Yoar. Sô’ th a n h trong biếu đồ được giam thành 8 th an h mỗi khách sạn như dược minh họa trong hình 6.16. n '.'i - -ị ề ĩí » » ' •3 -H Ềỉ V I -ả K-.i ■ t. ^ iv v t -« 1 : 5' * t ' ' Kình 6 .1 6 : PivotChart này dễ đọc hơn PivotChart trước. Biốu đồ này đơn gián hơn nhưng vần truyền đ ạt thông tin. Biểu đồ trìn h bày tóng thu nhập và chi phí cho mỗi bộ phận tạ i mỗi b ât động Síín. Khi bạn xem biếu clồ, một đặc diểm nổi bật: Mỗi b ất động sản chuyên vồ m ột bộ phận và kinh doanh ít hơn nhiều so với những bộ phận khác, Lĩnh vực chuyên ngành của Jackson HoLel là bộ phận Rooms; lĩnh vực chuyên ngành của Miguol Ilanch là bộ phận R estaurant; và lĩnh vực chuyên ngành của Pcngueno Place là bộ phận Catering, Trong thực tô', bạn sử dụng Lhông tin này để b ắt đầu tìm hiểu những bộ phận khác có thổ’ học dược như th ế nào từ những bộ phận chuyên về mỗi lĩnh vực. Bạn có th ể làm gì Uhác với một PivotChart? Bởi vì PivotC hart đựa vào dữ liệu trong PivotTable, bạn có thể xác định những giá trị nào được hiến th ị bàng việc di chuyển chúng vào, ra khỏi, và xung quanh các vùng trường. Bạn đã xem một ví dụ cơ bản, khi bạn đă tắ t việc hiển thị nãm trong biểu đồ. Bạn cũng có th ể thay đổi các phép tính được thực hiện cho mỗi giá trị bằng việc thay đổi thông tin trong phần PivotTable Values của PivotTable
180 Chương 6: Xoay đừ liệu: Xem dữ liệu bằng một cách khác Pield List. Đố thấy điều này ảnh hưởng đến biểu đồ như th ế nào, hủy chọn hộp kiểm Expenđitures và chú ý các thanh vốn th ế hiện các chi phí không còn xuất hiện trong biêu dồ nửa. Đ iể u b ạ n c ầ n b iế t K h i b ọ n l à m v i ệ c v ớ i m ộ t P i v o i C li a rt , b ạ u c ó t h ể x á c d ị n h c á c k h u n g t á c vụ n à o Uicn t h ị b ằ n g v i ệ c n h ấ p c á c lu ìt t r ơ n g n l i ó n i S h o w / H i d e t r ê n cab P i v o t C h a r t T o o l s A n a ly ze . N ế u m à u h ì n h c h i c h í t đ ế n n ỗ i b ạ n k h ô n g t h ể d ể d à n g h i ể u b i ể u đồ, sử d ụ n g h a i núc Iiày đ ể m ở r ộ n g k h ô n g g i a n c h o b i ể u đồ. L à m v i ệ c VỚI c á c l o ạ i b i ể u đ ồ k h á c Trong một số tình huông, bạn muôn xem PivotTable bằng n h to g cách khác. Trong vài bước tiếp theo, bạn thay đối loại biểu đồ được sử dụng để trìn h bày dữ liệu. Khi bạn hoàn tấ t những bước này, bạn có thế' quyết định các loại biểu đồ nào đê’ sử dụng hoặc không sử dụng với các PivotTables: 1. Mỏ' íilo R edSunsetH otelChainChartsl.xlsx. Sau đó chọn sheet có tên là Lines. Sheet này chứa m ột biểu đồ đường của dừ liệu đưỢc xoay, như Hình 6 .1 7 : Biểu dã' sường này có các m arker từ m ộ i P iv o t ĩa b le . Với dữ liệu được trìn h bày theo cách này, bạn có th ể thấy xu hướng chung của dữ liệu qua các bộ phận và b ất động sản. Xu hướng hơi rõ ràn g hơn từ biểu đồ đường so với biểu đồ thanh trong phần trước (xem hình 6.16).
Chương 6: Xoay dữ liệu: Xem dữ liệu bằng một cách khác 181 Xiii bạn xem dữ liệu dưới cKạng rr.ội biểu đồ đường, có thế’ bạn ;nuỏ’ri xcm chí các chi phí hoặc chỉ thu nhập. Nếu bạn không còn -.liâV các khung tác vụ nữa, nhấp biổu đồ. Các khung tác vụ sẽ 'íuất hiộn lại. Dế th ấy chí các chi phí, hủy chọn hộp kiếm Income. Để thấy chỉ vhu nhập, chọn hộp kiếm IncoraG, và sau đó hủy chọn hộp kiểm RxpencUturcs. y ----------------------------------------------------------------- r i t u t>ụii liù ij r.liụn cò íiộpỄ!x[>ci>cliiut'ưs và íiộp ỉncorne củnq m ội iúc, bọn k[iồnq lòm íiọi qì tỏ . 1 U I) nliiẽn, Pốl cuộc bgii có một biểu ẩổ kỉiârig c6 tJứ liệu. 2. Chọn shcot có tên là Circles. Sheet này chứa m ột biểu đồ tròn cúa clừ liệu được xoay, như đưỢc minh họa trong hình 6.18. Trong trường hợp này, nội dung biểu ãồ dã được th ay đổi để trình bày việc so sánh các chi phí theo mỏi bộ p h ận Qua toàn bộ khung thời gian. T oui • '. a n . í ; » t< rr> «(!«• « i»-7> C 4 *» tf • V J^ • . 4 ' U ‘ r « t rs*'-T.«í • « *4 .f d í* t> •' • r »r( 1 « •'(« :m I '> • . '* í t t : •» ' ' •*» Hình 5.18: M I biểu đồ Iròn từ ÍT I PivoỉTable. Ộ IỘ Biểu đồ này k h iến bạn tin rằng một biểu đồ tròn được tạo từ một PivotTable không hữu dụng. Tuy nhiên, với m ột chút thay đổi, nó trờ th à n h m ột biểu đồ rấ t hữu dụng. 3. Hủy chọn tấ t cả lựa chọn ngoại trừ Hotel và Expenditures. Bây giờ biểu đồ trò n trình bày chỉ toàn bộ các chi phí cho các khách sạn, được tách theo bất động sản như được m inh họa trong hình 6.19.