Learn Excel with Mr. Excel Part 3: WRANGLING DATA (1 người xem)

[ptm0412]

PART III: WRANGLING DATA
ADD SUBTOTALS TO A DATASET
THÊM CÁC DÒNG CỘNG TỪNG PHẦN VÀP BẢNG DỮ LIỆU
Problem: You have a lengthy report with invoice detail by customer, as shown in Fig. 664. You need to add a subtotal at each change in customer.
Vấn đề: bạn có 1 báo cáo dài dằng dặc như hình 664 với các hoá đơn tính tiền cho khách hàng, bạn muốn có 1 dòng cộng riêng cho mỗi hoá đơn.

Fig. 664

Strategy: A gift from Microsoft in Excel 95, the Automatic Subtotals feature will solve this problem in seconds instead of minutes.
By design, the dataset shown above is optimized for using the Subtotals command. Be sure to read the remaining examples for more details.
1) Start with data in list format. Select a single cell in the dataset.
From the menu, select Data – Subtotals, as shown in Fig. 665.
2. The Subtotals dialog assumes that you want to subtotal by the field in the leftmost column of your data. It also assumes that you want to total the rightmost field. In the present example, shown in Fig. 666, this is correct, so you can choose OK.
Giải pháp: Như 1 quà tặng từ Excel 95, chức năng Auto SubTotals sẽ giải quyết vấn đề này chỉ trong vài giây thay vì nhiều phút. Theo thiết kế, bảng dữ liệu như trên được thiết lập để có thể sử dụng SubTotals. Hãy xem kỹ thí dụ sau đây:
1. bắt đầu với bảng dữ liệu có định dạng List. Chọn 1 ô trong vùng dữ liệu. Chọn Menu – Data – Sub Totals như hình 665

Fig. 665

Fig. 666

In 1-2 seconds, subtotals will be inserted at each change in customer, as shown in Fig. 667.
Chỉ trong vài giây, Các dòng cộng sẽ được chèn vào mỗi lần thay đổi tên khách hàng.

Fig. 667
If you scroll to the end of the dataset, you will notice that Excel added a grand total of all customers, as shown in Fig. 668.
Nếu bạn kéo bảng tính xuống chót dưới, bạn sẽ thấy Excel chèn cả 1 dòng tổng cộng.

Fig. 668

As shown in Fig. 668, the inserted rows are using the somewhat new SUBTOTAL function. This function will total all of the cells in the range except for cells that contain other SUBTOTAL functions
Như hình 668, các dòng được chèn vào sử dụng công thức với hàm SubTotal. Hàm này sẽ tính tổng mọi ô trong cột ngoại trừ các ô có chứa hàm SubTotal ở phía trên.
Additional Details: In order to remove subtotals, select a cell in the dataset. From the menu, select Data – Subtotals. In the Subtotals dia­log, choose the Remove All button, as shown in Fig. 669.
Nói thêm: Để xoá bỏ các dòng cộng này, chọn 1 cell trong vùng dữ liệu, Menu – Data – Sub Totals. Trong hộp thoại hiện ra click nút Remove All.

Fig. 669

Gotcha: This example worked because the data was sorted by Account Number. If the data had been sorted by Invoice Number instead, the result would have been fairly meaningless, as shown in Fig. 670.
Ghi chú: Thí dụ này sử dụng được Sub Total vì đã được sắp theo thứ tự cột Account Number. Nếu dữ liệu sắp xếo thứ tự theo cột Invoice Number, thì kết quả chả có ý nghĩa gì cả như hình 670.

Fig. 670

Summary: The Subtotals command on the data menu quickly auto­mates the monotonous job of adding subtotals. Commands Discussed: Data – Subtotals
Tóm tắt; Lệnh subTotals trong menu Data tự động tính các dòng tổng từng phần và tổng cộng toàn bảng tính dữ liệu.

 

[ptm0412]

USE GROUP & OUTLINE BUTTONS TO COLLAPSE SUBTOTALED DATA
DÙNG CHỨC NĂNG GROUP AND OUTLINE ĐỂ ĐÓNG GỌN DỮ LIỆU ĐÃ SUBTOTAL

Problem: After using the Subtotal command in the previous example, you want to just print the total rows in order to create a summary report for your manager
Vấn đề: Sau khi sử dụng Subtotal trong thí dụ trước, bạn muốn chỉ in những dòng cộng để tạo 1 báo cáo tóm tắt cho sếp .
Strategy: Look to the left of column A, above cell A1. A series of three small numbers appear, as shown in Fig. 671. These are the Group & Outline buttons
Giải quyết: nhìn bên phải cột A, phía trên ô A1. Một nhóm các ô vuông có số từ 1 đến 3 xuất hiện như hình 671. Đây là các nút Group And Outline..

Fig. 671

Using the mouse, choose the small 2 button. You will see just the cus­tomer totals, as shown in Fig. 672.
Dùng chuột, nhấn nút có số 2, bạn sẽ chỉ thấy các dòng cộngcho mỗi khách hàng

Fig. 672

As shown in Fig. 673, choose the small 1 button to see just the grand total (which seems a bit pointless).
Nếu nhấn nút có số 1, sẽ chỉ thấy dòng tổng cộng.

Fig. 673

Choose the 3 button to go back to the detail view, with all records, as shown in Fig. 674.
Nhấn nút có số 3 sẽ thấy tất cả chi tiết.

Fig. 674

Additional Details: In the 2 button view, you can explode a single cus-tomer’s detail records by clicking the + next to the customer total, as shown in Fig. 675.
Nói thêm: Khi nhấn nút có số 2, Bạn có thể mở rộng chi tiết của chỉ 1 khách hàng bằng cách nhấn vào dấu + kế bên dòng Total của khách hàng đó.

Fig. 675

Use the minus sign next to A32, as shown in Fig. 675, to collapse the detail again. Or, select the 2 button to collapse all.
Nhấn vào dấu trừ kế bên ô A32, để đóng các chi tiết lại. Hoặc nhấn nút có số 2 để đóng tất cả.
Summary: After using the Subtotals command, the Group & Outline buttons allow you to create multiple views of the data.
Tóm tắt: Sau khi sử dụng Chức năng SubTotals, các nút Group And Outline cho phép bạn xem dữ liệu với nhiều kiểu khác nhau.

 

[ptm0412]

COPY JUST TOTALS FROM SUBTOTALED DATA
CHỈ COPY CÁC DÒNG CỘNG CỦA SUBTOTAL

Problem: Your manager wants just the total rows sent to him in a file. You’ve added subtotals and then chosen the #2 Group & Outline button to see just the data that you want, as shown in Fig. 676.
Vấn đề: Sếp của bạn xem xong báo cáo tóm tắt của bạn và đòi file có cái tóm tắt đó. (Bạn đã nhấn nút có số 2 để in cái bào cáo đó.

Fig. 676

However, when you copy this view and paste to a new workbook, all of the detail rows come along as well, as shown in Fig. 677.
Dù vậy khi bạn copy cái tóm tắt đó và paste vào 1 bảng tính khác, mọi dòng dữ liệu sẽ bị đi theo.

Fig. 677

Strategy: There is an obscure command on the Go To Special dialog box to assist with this task. Follow these steps.
1. Choose the #2 Group & Outline button to put the data in subtotal view
2. Select everything from the headings to the grand total by selecting one cell with data and hitting Ctrl+* (you can use the * key on the numeric keypad).
3) Bring up the Go To dialog by choosing Edit – Go To or by hitting the F5 key on the keyboard. As shown in Fig. 678, choose the Special button in the lower left corner of the Go To dialog.
Giải quyết: Có 1 lựa chọn trong hộp thoại GoTo Special có thể giải quyết trường hợp này.
1. Nhấn nút Group and Outline có số 2.
2. Chọn mọi cell từ dòng tiêu đề đến hết dòng tổng cộng, bằng cách chỉ chọn 1 cell trong vùng dữ liệu rồi nhấn phím Ctrl + * (có thể dùng phím * bên nhóm phím số).
3. Mở hộp thoại GoTo bằng cách vào Menu Edit – Go To hoặc nhấn F5. Như hình 678, nhấn nút Special ở góc dưới bên trái.

Fig. 678

4) In the Go To Special dialog, select Visible Cells Only and choose OK, as shown in Fig. 679.
4. Trong hộp thoại Special, click chọn Visible Cells Only và OK

Fig. 679

The change will be almost imperceptible and may not even be noticeable in the printed resolution of this book. Amongst the blue highlighting, you will see fine white lines above and below each subtotal, as shown in Fig. 680. This is Excel’s way of saying, “Hey – there are hidden rows back there which are not part of the selection.”
Sự thay đổi hầu như khó nhận ra trong kết quả in của trang này. Tuy nhiên bên trong vùng chọn màu canh bạn sẽ thấy các đường kẻ trắng ở trên và dưới mỗi dòng cộng. Đây là các Excel bảo chúng ta: “Nè, Có 1 số những dòng ẩn ở đó, nó không được chọn đâu nhé!”

Fig. 680

5. Next, copy with Ctrl+C. Switch to a new workbook. Paste with Ctrl+V. You will have just the subtotal rows, as shown in Fig. 681.
Sau đó, copy bằng Ctrl +C. Mở 1 file mới, Paste bằng Ctrl + V. Bạn sẽ chỉ có các dòng cộng.

Fig. 681

You might think that you would have to do a Paste Special – Values instead of just a paste. However, the Paste command works OK. As you can see in the formula bar above, the SUBTOTAL function, which used to be in the cell, is converted to a value.
Bạn có thể nghĩ rằng nên dùng Paste Special – Value thay vì Paste. Dù vậy lệnh Paste thường cũng vẫn được. Bạn có thể thấy trên thanh công thức, cái công thức SubTotal hồi nãy, nay chỉ còn là 1 giá trị số.

Additional Details: The process of selecting Go To – Special – Visible Cells Only can be reduced to holding down Alt while hitting EGSY and then Enter. Alt+E opens the Edit menu. Alt+G selects Go To. Alt+S presses the Special button. Alt+Y selects Visible Cells Only (note that the “y” is underlined in the GoTo Special dialog in Fig. 679). Enter se­lects the default OK button.
Nói thêm: Quá trình chọn Goto Special Visible cells Only có thể thay thế bằng nhóm phím tắt Alter và lần lượt EGSY và Enter. Alter + E mở menu Edit. Alter +G chọn GoTo, Alter + S tức là nhấn nút Special, Alter + Y là chọn Visible cells Only (chữ cái y được gạch dưới trong hộp thoại Goto Special). Cuối cùng Enter là mặc định nhấn nút OK.

Summary: This is a fairly obscure trick. I think it is more obscure be­cause no one in his or her right mind uses the Go To dialog. If you are at cell A10 and need to go to cell A100, it is pretty easy to just hit PgDn a few times. Considering how seemingly useless the Go To dialog is, the fact that it houses the Special button is ironic.
Tóm tắt: Đây là 1 biện pháp khá là ít người biết. Tôi nghĩ vậy vì ít ai nghĩ tới việc sử dụng nó trong tâm trí. Nếu bạn đang ở ô A10 và muốn tới ô A100, thật dẽ dàng khi nhấn nút Page Down 1 số lần. Cứ xem cái hộp thoại Go to dường như vô dụng và việc sử dụng nút Special trong đó là buồn cười.

 

[ptm0412]

ENTER A GRAND TOTAL OF DATA MANUALLY SUBTOTALED
TÍNH DÒNG TỔNG CỘNG CHO DỮ LIỆU ĐÃ TÍNH CỘNG TỪNG PHẦN BẰNG TAY.

Problem: Your manager doesn’t know the trick for doing automatic sub-totals. In the example below, he manually entered blank lines between each customer and entered SUM formulas for each customer, as shown in Fig. 682. How can you produce a grand total of all customers?
Vấn đề: Sếp của bạn không biết xài SubTotals tự động. Trong thí dụ sau đây, ổng chèn những dòng trống vào rồi dùng công thức Sum() bìnnh thường cho mỗi khách hàng. Làm sao bạn có thể tính tổng cộng cả bảng dữ liệu được đây?

Fig 682

Strategy: Think about this dataset. Every sale is actually in there twice. As shown in Fig. 683, the $4,060 for invoice 1077 is in cell D18 and also totaled into D19. Thus, the formula to enter in D21 is =SUM(D2: D19)/2.
Giải quyết: Hãy suy nghĩ 1 chút về bảng dữ liệu hiện tại. Mọi khoản doanh thu được hiện lên 2 lần. Như hình 683, số tiền 4.060$ của hoá đơn 1077 thể hiện trong ô D18 đồng thời được tính tổng con trong ô D19. vạy công thức cho ô D21 là = Sum(D219)/2

Fig 683

This method works! It is one of those old accounting tricks (taught to me by an old accountant). It is not intuitive, especially if you hated algebra. Try it for yourself a few times, comparing the results to the method of =D19+D13+D7. You will see that you get the same result.
Gotcha: This method only works if every customer is totaled. A manag-er who doesn’t know how to use subtotals might be the kind of manager who doesn’t total the customers with only one detail line. In Fig. 684, line 9 will cause the total to not work.
Phương pháp này coi bộ được. Nó là 1 trong những thủ thuật (bí quyết) của 1 ông kế toán già truyền lại cho tôi. Nó không giống như trực giác, đặc biệt nếu bạn ghét môn đại số. Bạn hãy thử vài lần, so sánh nó với kết quả của phép tính thông thường = D19+ D13 + D7. Bạn sẽ thấy các kết quả là bằng nhau.

Ghi chú: Phương pháp này hiệu nghiệm nếu mọi khách hàng đều được tính tổng. Một ông Sếp không biết gì về SubTotals sẽ thuộc loại sếp không tính tổng cho khách hàng chỉ mua hàng 1 lần và chi tiết về khách hàng đó chỉ có 1 dòng. Trong hình 684, dòng 9 sẽ làm cho kết quả tổng cộng (chia 2) bị sai.

Fig 684
Summary: =SUM()/2 is a great method for quickly determining the to-tals of a dataset with “manual” subtotals.
Tóm tắt: côngthức = Sum()/2 là 1 phương pháp hay để tính nhanh tổng cộng của bảng dữ liệu nếu trước đó đã tính các tổng con bằng tay.

 

[ptm0412]

WHY DO SUBTOTALS COME OUT AS COUNTS?
VÌ SAO SUBTOTALS LẠI LÀ ĐẾM?

Problem: You added automatic subtotals to this dataset. As shown in Fig. 685, the subtotals of four for Air Canada and five for Ainsworth are clearly not correct. What went wrong?
Vấn đề: Bạn sử dụng Subtotals cho bảng dữ liệu này. (trong hình 685). Dòng tổng cho khách hàng Air Canada là 4 và cho Ainsworth là 5 rõ ràng là sai. Vậy chuyện gì đã xảy ra?

Fig 685

Strategy: The first time that you subtotal a dataset, Excel assumes that you want to subtotal the final column in the dataset. If this column contains text data, then the subtotals dialog will default to a Count in-stead of a Sum, as shown in Fig. 686.
Sự tình: Lần đầu bạn sử dụng SubTotal cho 1 bảng tính, Excel sẽ cho rằng bạn muốn tính subtotal cho cột cuối cùng. Nếu cột này có dữ liệu dạng text, nó sẽ chọn hàm mặc định cho text là Count thay vì Sum, như hình 686

Fig 686

Gotcha: This problem will also happen even if your final column con-tains mostly numbers but includes one blank cell.
To correct the problem once it appears, open the Subtotals dialog again. As shown in Fig. 687, change the Use Function dropdown from Count to Sum. Choose OK.
Ghi chú: Sự cố này thậm chí xảy ra với cột cuối có dữ liệu toàn số chỉ trừ 1 ô bị trống.
Để sửa lỗi này khi nó xuất hiện, mở hộp thoai SubTotals lần nữa, chọn lại hàm Sum trong danh sách xổ xuống của ô Use Function. Rồi nhấn OK.

Fig 687

To avoid the problem in the first place, remember to double-check the Use Function field in the Subtotals dialog, particularly if your data has text in the rightmost column.
Summary: Excel’s intellisense often gives you the correct choices, so you may get lulled into the habit of rarely paying attention to the Use Function field in the Subtotals dialog. When you see Counts instead of Sums, you will know how to correct it.
Để tránh lỗi này, cần phải kiểm tra kỹ ô Use Function trong hộp thoại, nhất là khi bạn có dữ liệu dạng text ở cột ngoài cùng bên phải.
Tóm tắt: Sự thông minh của Excel thường đưa ra cho bạn các giải pháp tốt nhất, và bạn sẽ bị ru ngủ trong cái thói quen để mặc cho Excel tự làm. Vậy nhớ kiểm tra ô Use Function, và khi thấy Count thay vì Sum, bạn sẽ chỉnh sửa lại trước khi OK.


SUBTOTAL MANY COLUMNS AT ONCE
TÍNH TỔNG NHIỀU CỘT.

Problem: As shown in Fig. 688, you have data with 12 months going across the columns. You need to add subtotals to all 12 columns.
Vấn đề: như hình 688, bạn có dữ liệu của 12 tháng dàn hàng ngang trên bảng tính. bạn muốn tính tổng cho cả 12 cột này.

Fig 688

Strategy: You can subtotal all of the columns at once.
1. In the Subtotal dialog, use the scroll bar to scroll through all fields. You can only display three fields at a time. Checkmark the last three fields, as shown in Fig. 689.

Fig 689

2. Scroll up to show the previous three fields, as shown in Fig. 690.
3. Checkmark those three fields. Continue this routine, scroll-ing to reveal three fields, then checkmarking three fields. It gets particularly tedious when you have 36 months’ data, but it is still infinitely faster than doing subtotals manually.
Giải quyết: Bạn có thể tính tổng cả 12 cột cùng lúc.
1. Trong hộp thoại Subtotals, kéo thanh trượt xuống để thấy lần luợt các tên cột, bạn sẽ chỉ thấy 1 lần có 3 tên (!) và đánh dấu mỗi lần 3 cột. Sẽ rất tẻ nhạt nếu bạn có 36 tháng dữ liệu cần tính tổng, nhưng vẫn còn hơn là tính tổng từng cột bằng tay.

Fig 690

Result: Subtotals are added to all of the columns at once, as shown in Fig. 691.
Kết quả: Các dòng tổng sẽ tính tổng đủ 12 cột cho bạn.

Fig 691

Summary: You can add subtotals to many columns in one pass of the Subtotal command. The downside is that the Excel dialog for Subtotal only lets you checkmark three fields before you have to use the scroll-bar.
Tóm tắt: Bạn có thể tính tổng cho nhiều cột một lúc bằng cách sử dụng lệnh Data – SubTotals. Chỉ mỗi việc đáng chán là Excel chỉ cho bạn đánh dấu tính tổng 1 lần có 3 tên cột.