Learn Excel With Mr Excel Part 2 -Calculating with Excel - Sub 3 (1 người xem)

[ptm0412]

CALCULATE A LOAN PAYMENT
TÍNH TOÁN 1 KHOẢN VAY
(từ trang 186)
Problem: You are considering buying a car. You want to calculate the loan payment, as shown in Fig. 325.

Vấn đề: Bạn dự định mua 1 xe hơi. Bạn muốn tính toán 1 khoản tiền vay như hình 325
Strategy: Use the PMT function. Follow these steps.

Biện pháp: Dùng hàm PMT và theo các bước sau:

Fig. 325

1) Enter Price, Term in Months, and Annual Percentage Rate in cells B2:B4, as shown in Fig. 325.
The PMT function has three required arguments. The first argument is the interest rate. The second argument is the number of payments in the loan. The final argument is the original loan amount.
Gotcha: The interest rate must be entered as a percentage. If you are planning on monthly payments (which is normal), then you have to di­vide the annual percentage rate by twelve.
Gotcha: In financial terms, the bank is loaning you $25,000 – a positive amount coming to you. Thus, the payments that you make to the bank are really a negative amount – it is money leaving your wallet. For this reason, the result of the PMT function will be negative. However, you can precede the PMT function with a minus sign in order to return a positive payment amount.
2) Enter this formula in cell B6: =–PMT(B4/12,B3,B2). See Fig. 326.

1. Nhập vào giá tiền, thời gian vay theo tháng, lãi suất vay theo năm vào các ô B2:B4. Hàm PMT đòi hỏi 3 tham số: Tham số thứ nhất là lãi suất vay. Tham số thứ hai là số lần trả cho khoản vay. Tham số cuối cùng là tổng số tiền vay.
Ghi chú: Lãi suất vay cần nhập vào dưới dạng phần trăm (%). Nếu bạn dự định trả hàng tháng (thường là thế), bạn phải chia lãi suất vay (năm) cho 12
Trong kỳ hạn vay, ngân hàng sẽ chi cho bạn 1 khoản tiền 25.000 USD, 1 số tiền dương. Do vậy, các khoản bạn trả cho ngân hàng sẽ là số âm - khoản tiền chui ra khỏi cái bóp của bạn. nghĩa là kết quả của hàm PMT sẽ là 1 số âm. Dù cậy bạn cũng có thể uýnh 1 dấu trừ trước hàm PMT để kết quả công thức sẽ cho 1 số dương.
2. Gõ công thức sau vào ô B6: =-PMT(B4/12,B3,B2)

Fig. 326

Summary: The PMT function is great at calculating house or car loans.
Tóm tắt: hàm PMT dùng rất tuyệt để tính các khoản vay mua nhà hay mua xe.


CALCULATE MANY SCENARIOS FOR LOAN PAYMENTS
TÍNH TOÁN KHOẢN TIỀN VAY VỚI CÁC THAM SỐ KHÁC NHAU

Problem: You are considering buying a car. You used the previous tip to calculate a loan payment, as shown in Fig. 326. You want to do some “what-if” scenarios in order to see various options of increasing or de­creasing the term or price.
Strategy: Follow the same setup that you used for the previous tip, as shown in Fig. 327.
1) Enter Price, Term in Months, and Annual Percentage Rate in cells B2:B4. See Fig. 327.
2) Enter this formula in cell B6: =–PMT(B4/12,B3,B2). See Fig. 327.

Vấn đề: Bạn muốn mua 1 xe hơi. Bạn dùng hàm PMT như bài trên để tính các khoản phải trả hàng tháng. Bạn lại muốn tính với các lựa chọn khác nhau để thấy các cơ hội khác nhau khi tăng hoặc giảm thời hạn vay hoặc khoản tiền vay.
Biện pháp: THực hiện các bước của bài trên: Gõ vào giá tiền, thời gian vay theo tháng, lãi suất vay theo năm vào các ô B2:B4, nhập công thức sau vào ô B6: =-PMT(B4/12,B3,B2)

Fig. 327

3) Copy cells B2:B6 over for several columns. Plug in different num­bers for the price and/or term, as shown in Fig. 328.

Sao chép vùng B2:B6 sang vài cột. Thay đổi 1 vài con sồ về khoản vay và/ hoặc thời hạn vay như hình 328

Fig. 328
Summary: This is where Excel shines. After you have entered the for­mulas for one loan model, you can easily copy and create many more loan models.

Tóm tắt: Đây là chỗ mà Excel thể hiện. Sau khi đã lập côngthức tính cho 1 kiểu vay, bạn có thể sao chép và thử với nhiểu kiểu vay khác nhau.

 

[ptm0412]

GET HELP ON ANY FUNCTION WHILE ENTERING A FORMULA
TRỢ GIÚP VỀ BẤT KỲ HÀM TÍNH TOÁN NÀO KHI ĐANG GÕ 1 CÔNG THỨC.

Problem: There are hundreds of functions available in Excel. You might remember that you need to use a particular function, but you cannot re­member the sequence of the arguments in the function.
Strategy: In Excel 2002 and later, type the function and the opening parenthesis. A tooltip will appear to remind you of the order of the argu­ments. Any arguments in square brackets are optional. As shown in Fig. 329, the argument in bold is the argument that you need to type now.

Vấn đề: Có hàng trăm hàm tính toán hiện hữu trong Excel. Bạn phải nhớ cần sử dụng hàm nào để tính cho việc của bạn, nhưng bạn không nhất thiết phải nhớ tên và trình tự các tham số bên trong mỗi hàm.
Phương pháp: Trong Excel 2002 và sau đó, khi gõ tên 1 hàm và mở 1 dấu ngoặc đơn, 1 dòng trợ giúp sẽ xuất hiện để nhắc bạn các tham số của hàm. Các tham số nằm trong dấu ngoặc vuông là tuỳ chọn và có thể bỏ qua. Trong hình 329, tham số tô đậm là tham số bạn đang phải gõ vào.

Fig. 329

Alternate Strategy: If you are using Excel 2000 or if you need more help than the abbreviations of “pmt” and “pv”, then use the Function Arguments dialog. Type the function name and the opening parenthe­sis. Hit Ctrl+A to display the Function Arguments dialog, as shown in Fig 330

Một cách khác: Nếu bạn đang dùng Excel 2000 và bạn cần trợ giúp nhiều hơn cái chữ viết tắt “pmt” hoặc “pv”, hãy dùng hộp thoại Function Arguments. Gõ tên hàm và 1 dấu mở ngoặc đơn. Nhấn Ctrl+A để hiển thị hộp thoại này.

Fig. 330


The Function Arguments dialog shows the order of the arguments. Ar­guments in bold are required. The other arguments are optional. As you click into each text box in the dialog, the text at the bottom describes that argument in detail.
If you still need more help, there is a hyperlink to the complete help topic for this function. As you enter the value for each argument, the Function Arguments dia­log will calculate the numeric results of that argument. After you have entered all of the required arguments, the Function Wizard will display the result of the function, as shown in Fig. 331. You can consider wheth­er this result is a reasonable number before accepting the formula.

Hộp thoại Function Arguments cho thấy thứ tự của các tham số, tham số in đậm là bắt buộc, tham số khác là tuỳ chọn. Khi bạn click chọn vào ô nhập của tham số nào, thì thông tin chi tiết hướng dẫn cho tham số đó hiện ra ở bên dươi.
Nếu bạn muốn trợ giúp nhiều hơn, có 1 liên kết đến 1 chủ đề trợ giúp đầy đủ. Khi bạn gõ giá trị vào 1 ô tham số, hộp thoại sẽ tính ra giá trị số của tham số. Sau khi bạn điền đầy đủ các tham số cần thiết, hộp thoại sẽ thể hiện kết quả của hàm như hình 331. Bạn có thể xem trước kết quả có hợp lý không trước khi nhấn nút chấp nhận nó.

Fig. 331


In Fig. 331, the formula result indicates that if you want to pay $425 a month for a $25,000 car, you will be paying for 69.6 months. This seems about right, so choose OK.
Result: As shown in Fig. 332, you’ve entered a fairly complex function without having to remember its details.

Trong hình 331, kết quả của công thức cho thấy nếu bạn muốn trả 425 USD 1 tháng cho 1 khoản vay mua xe 25.000 USD, bạn sẽ phải trả trong 69,6 tháng. Kết quả này có vẻ đúng, nên bạn nhấn OK.
Kết quả: Như hình 332, bạn đã thực hiện 1 hàm tài chính phức tạp mà không cần thuôc lòng các tham số của hàm.

Fig. 332

Summary: Use Ctrl+A shortcut after entering the opening parenthesis of a function to display the Function Arguments dialog box.

Tóm tắt: Dùng phím tắt Ctrl+A sau khi gõ dấu mở ngoặc đơn cho 1 hàm, sẽ hiện ra hộp thoại Function Arguments

 

[ptm0412]

DISCOVER NEW FUNCTIONS USING THE fxBUTTON
KHÁM PHÁ CÁC HÀM MỚI BẰNG CÁCH SỬ DỤNG NÚT fx

Problem: There are hundreds of functions available in Excel. You know that you want to find a function for a geometric mean but have no clue which function might do this.
Strategy: As shown in Fig. 333, next to the formula bar, there is an ƒx button. Choose this button to bring up the Insert Function Wizard.

Vấn đề: Có hàng trăm hàm tính toán hiện hữu trong Excel. Bạn biết rằng bạn muốn tìm 1 hàm để tính trung bình nhân nhưng không có manh mối nào để chọn hàm phù hợp.
Giải pháp: Xem hình 333, kế bên thanh công thức là 1 nút có chữ ƒx. Click nút này để mở ra Insert Function Wizard

Fig. 333

The Insert Function dialog lists the most recently used functions by de­fault. All of Excel’s functions are categorized into areas such as Finan­cial, Date & Time, Math & Trig, Statistical, Lookup & Reference, Data­base, Text, Logical, Information, and Engineering. If you can guess that the geometric mean function would be in the Statistical category, select this category from the dropdown, as shown in Fig. 334.

Hộp thoại Insert Function liệt kê 1 danh sách các hàm mói sử dụng gần đây nhất. Mọi hàm của Excel được phân theo loại và nhóm như là hàm tài chính, hàm ngày giờ, hàm toán và lượng giác, hàm thống kê, hàm dò tìm và tham chiếu, hàm dữ liệu, hàm về chuỗi, hàm luận lý, hàm thông tin, và hàm Kỹ thuật. Nếu bạn đồ chừng hàm tính trung bình nhân có thể nằm trong nhóm hàm thống kê, hãy chọn nhóm này trong danh sách xổ xuống như hình 334

Fig. 334


Additional Information: Selecting Statistical provides you with over 100 choices. To narrow the choices down, type geometric mean in the search box and choose Go. This will result in one possible choice, as shown in Fig. 335.

Thông tin thêm: Chọn nhóm hàm thống kê, Excel sẽ cung cấp cho bạn khoảng 100 hàm. Để thu hẹp phạm vi tìm kiếm, hãy gõ geometric mean (trung bình nhân) vào ô tìm kiếm và nhấn Go. Kết quả là chỉ còn 1 hàm duy nhất như hình 335.

Fig. 335

Summary: Use the ƒx button to help find a function.
Tóm tắt: Dùng nút fx để tìm kiếm 1 hàm chưa biết.

 

[ptm0412]

THREE METHODS OF ENTERING FORMULAS
3 PHƯƠNG PHÁP TẠO CÔNG THỨC: Phương pháp 1:

Problem: There are three basic ways of entering simple calculations in Excel. Knowing all three ways will allow you to enter formulas faster, according to the situation. Consider the worksheet shown in Fig. 336. In Fig. 336, you want to calculate total cost in E3 as the Case Quantity in B3 times the Unit Cost in C3.

Vấn đề: Có 3 cách căn bản để nhập những công thức tính toán đơn giản vào Excel. Biết cả 3 phương pháp sẽ giúp bạn thực hành nhanh hơn tùy trường hợp. Hãy xem bảng tính trong hình 336. Trong bảng tính đó bạn muốn tính tổng giá vốn ở ô E3 bằng số lượng ở B3 nhân Đơn giá vốn ở C3

Fig. 336
Strategy: You can simply type the formula.
1) Put the cell pointer in E3 and type =b3*c3, as shown in Fig. 337, and then hit Enter.
bạn có thể gõ côngthức trực tiếp: chọn ô E3 và gõ =b3*c3 như hình 337 và Enter.

Fig. 337

2) The formula will calculate. You will see the original formula in the formula bar above E1. The worksheet itself will show the result of the calculation, as shown in Fig. 338.
Công thức đã đượcc tính. Bạn có thể thấy công thức trong thanh công thức như hình 338

Fig. 338

Advantage: If you are a good typist, you only need to type seven key- strokes.
Disadvantage: This method gets complicated when you are dealing with complex formulas.
Tiện lợi: Nếu bạn là người đánh máy giỏi, bạn sẽ chỉ cần gõ phím 7 lần.
Bất tiện: Phương pháp này sẽ gây khó khăn khi xử những công thức phức tạp.

 

[ptm0412]

THREE METHODS OF ENTERING FORMULAS
3 PHƯƠNG PHÁP TẠO CÔNG THỨC: Phương pháp 2:

Alternate Strategy: Use the arrow keys. Anyone who was using spreadsheets in the days of Lotus 1-2-3 often used this method. Once you have mastered this method, it is very fast and very intuitive.
1) Move the cell pointer to E3. As shown in Fig. 339, type an Equal sign to let Excel know that you are about to enter a formula.

Một cách khác: Dùng phím mũi tên. Những ai đã sử dụng bảng tính thời Lotus 1-2-3 ưa dùng cách này. Một khi bạn dùng thành thạo, phương pháp này cũng rất nhanh và có tính trực quan hơn.
1. Di chuyển con trỏ ô đên ô E3. Đánh 1 dấu bằng để cho Excel biết bạn đang bắt đầu 1 cong thức.

Fig. 339

2) Hit the Left Arrow. As shown in Fig. 340, a dotted border surrounds the cell to the left of E3. Excel starts to build a formula of =D3.

2. Dùng phím mũi tên trái, bạn sẽ thấy 1 khung đường chấm sẽ bao quanh ô bên trái của E3 (ô D3), Excel bắt đầu công thức =D3.

Fig. 340

3) Hit the Left Arrow key two more times. Your provisional formula is now =B3, as shown in Fig. 341.

3. Nhấn phím mũi tên 2 lần nữa. Công thức tạm thời trên thanh công thức se là =B3, như hình 341

Fig. 341

4) On the keyboard, hit the * key. You can either hit Shift+8 or use the Asterisk key on the numeric keypad. The dotted border will disappear from B3 and be replaced by a solid-colored border, as shown in Fig. 342. Hitting any operator key, such as Plus, Minus, Asterisk, or Slash, tells Excel that you are moving on to the next part of the formula.

Trên bàn phím, nhấn phím *. Bạn có thể nhấn Shift+8 hoặc cũng có thể dùng phím dấu hoa thị ở nhóm phím số.Khung đường chấm chấm sẽ không bao quanh B3 nữa, thay vào đó là 1 khung liền nét có màu. Nhấn 1 trong các phím toán tử như cộng, trừ, nhân (*), chia là báo cho Excel bạn đang chuyển tới số hạng tiếp theo của công thức.

Fig. 342
197 of 354
5) Next, hit the Left Arrow key. The dotted border reappears. You now have a provisional formula of =B3*D3, as shown in Fig. 343. This isn’t quite right, yet, but you’re getting close.

5. Nhấn phím mũi tên trái. Cái khung chấm chấm xuất hiện lại. Bạn có côngthức tạm là =B3*D3 như hình 343. Côngthức này chưa đúng, nhưng bạn đang tới gần kết quả.

Fig. 343

6) Hit the Left Arrow key one more time. As shown in Fig. 344, the provisional formula is now correct.

Nhấn phím mủi tên trái 1 lần nữa. Công thức tạm kia đã trở thành công thức đúng.

Fig. 344

7) Next, hit Enter. The formula will calculate. You will see the original formula in the formula bar above E1. The worksheet itself will show the result of the calculatio.

Nhấn Enter. Công thức sẽ được tính. Bạn sẽ thấy lại côngthức nguyên trên thanh công thức. bản thân bảng tính sẽ hiện kết quả tính toán ở ô E3.

Advantage: You never have to type cell references with this method. You merely point to them, using the arrow keys. If you are building formulas that are based on cells near the formula cell, formulas can be entered very quickly with this method.

Tiện lợi: Bạn bạn không phải gõ địa chỉ tham chiếu vào công thức. Bạn chỉ đơn thuần chỉ vào nó bằng cách dùng phím mũi tên. Nếu bạn tạo côngthức với các ô tham chiếu gần với ô đang tính, phương pháp này sẽ rất hiệu quả và nhanh.

 

[ptm0412]

THREE METHODS OF ENTERING FORMULAS
3 PHƯƠNG PHÁP TẠO CÔNG THỨC: Phương pháp 3:

Alternate Strategy: Use the mouse. Follow these steps.
1) As shown in Fig. 345, type an Equal sign. This tells Excel that you are about to enter a formula.

Cách thứ 3: Dùng chuột.
1. Như hình 35, gõ dấu bằng để cho Excel biết bạn đang bắt đầu 1 công thức.

Fig. 345

2) Using the mouse, touch cell B3. Excel starts to build your formula, as shown in Fig. 346.
Dùng chuột, nhấn vào B3

Fig. 346

3) Using the keyboard, hit the Asterisk key on the numeric keypad or the Shift+8 keys. See Fig. 347
Dùng bàn phím, gõ dấu hoa thị (nhân).

Fig. 347

4) Using the mouse, touch cell C3. The provisional formula now looks correct, as shown in Fig. 348.

Dùng chuột nhấn ô C3. Công thức đã đúng
1

99 of 354
Fig. 348

5) Hit the Enter key. The formula will calculate. You will see the original formula in the formula bar above E1. The worksheet itself will show the result of the calculation.

Nhấn Enter, công thức sẽ được tính. Bạn sẽ thấy lại côngthức nguyên trên thanh công thức. bản thân bảng tính sẽ hiện kết quả tính toán ở ô E3.

Advantages of the Mouse: It is easy to use the mouse to directly touch the cells you need in the formula.
Disadvantage of the Mouse: It takes a long time to move your hands back and forth from the keyboard to the mouse. To enter the above formula, you have to hit a key, use the mouse, hit a key, use the mouse, and hit a key again. That is four movements back and forth from the keyboard to the mouse and back.
Summary: There are three basic methods for entering formulas in Excel.
Using the right method for the situation can radically improve your efficiency.
Tiện lợi của chuột: Dùng chuột sẽ dễ dàng chọn trực tíêp ô bạn cần trong công thức.
Bất tiện: Sẽ mất thời gian khi buông bàn phím cầm chuột và ngược lại. Để tạo công thức trên, bạn phải gõ 1 phím, click chuột 1 cái, nhấn 1 phím nữa, click chuột 1 cái thứ hai, và lại nhấn 1 phím Enter. Đó nghĩa là phải buông cái này, cầm cái kia 4 lần.

Tóm tắt: Có 3 cách cơ bản để tạo công thức trong Excel. Dùng cách phù hợp nhất cho trường hợp cụ thể sẽ cải thiện kỹ năng của bạn.

 

[ptm0412]

USE AUTOSUM TO QUICKLY ENTER A TOTAL FORMULA
DÙNG AUTOSUM ĐỂ CÓ NHANH 1 CÔNGTHỨC TÍNH TỔNG.

Problem: You have data in Excel, as shown in Fig. 349. You need to total the rows quickly.

Vấn đề: bạn có dữ liệu trong Excel như hình 349, và bạn cần tính tổng nhanh.

Fig. 349

Strategy: Use the AutoSum button on the Standard toolbar. The Auto- Sum button looks like the Greek letter sigma, as shown in Fig. 350.

Biện pháp: Dùng nút Auto-Sum trên thanh công cụ. Nút Auto-Sum có chữ Sigma
hoa của Hy lạp như hình 350.

Fig. 350

1) Place the cell pointer in cell B10. Touch the AutoSum button, as shown in Fig. 351.

Đặt ô chọn vào ô B10. Nhấn nút Auto-Sum.

Fig. 351

2) Excel analyzes your data and predicts that you want to total the range of numbers above the cell pointer. As shown in Fig. 352, Excel enters a provisional formula of =SUM(B2:B9). Hit Enter to accept this formula.

Excel tự đánh giá dũ liệu bảng tính và cho rằng bạn muốn tính tổng các con số phía trên của B10. Như hình 352, Excel cấp cho bạn công thức tạm =SUM(B2:B9). Nhấn Enter để chấp nhận nó.

Fig. 352

3) The square dot in the lower right corner of the cell pointer is the AutoFill handle. With the mouse, drag the Fill handle to the right to include cells C10 through F10. Release the mouse button and the formula will be copied to all five columns.

Cái chấm nhỏ hình vuông ở góc dưới bên phải ô là cái AutoFill Handle. Dùng chuột níu cái nút đó và lôi qua bên phải trùm lên các ô từ C10 đến F10. Buông chuột ra bạn sẽ được các công thức đã sao chép cho 5 cột.

Summary: The AutoSum button on the Standard toolbar is a powerful tool for quickly entering a total formula.

Tóm tắt: Nút Auto-Sum trên thanh công cụ chuẩn dùng để tạo nhanh côngthức tổng.

 

[ptm0412]

AUTOSUM DOESN’T ALWAYS PREDICT MY DATA CORRECTLY
AUTOSUM KHÔNG PHẢI LÚC NÀO CŨNG ĐOÁN ĐÚNG
Problem: When using the AutoSum button, Excel sometimes predicts the wrong range of data to total. In Fig. 353, the AutoSum worked fine in F2 and F3, but in cell F4, Excel gets fooled into thinking that you want to total the rows above F4.
Vấn đề: Khi sử dụng nút Auto-Sum, Excel không phải lúc nào cũng dự doán đúng cái gì bạn cần. Trong hình 353, Excel làm tố`t ở F2 và F3, nhưng đến F4, Excel lại cho rằng bạn muốn tính tổng các ô nằm trên F4.

Fig. 353

Strategy: After hitting the AutoSum button, the provisional range address is highlighted in the provisional formula. Using your mouse, highlight the right range. As shown in Fig. 353, AutoSum will work correctly in F2 and F3. It will predict that you want to sum the data in that row. However, in cell F4 Excel has a choice: do you want to sum the two cells in that column or the four cells in the row? Excel always chooses to sum the two cells above in this situation. After hitting the AutoSum button, note that F2:F3 is highlighted in the formula. This allows you to enter the correct range. There are three methods:
1) With the mouse, highlight B4:E4 and hit Enter.
2) With the keyboard, type B4:E4.
3) Using the arrow keys, hit the Left Arrow to move to E2. Hit the Down Arrow twice to move to E4. While holding down the Shift key, hit the Left Arrow three times to highlight B4:E4, as shown in Fig. 354.
Biện pháp: Sau khi nhấn AutoSum, khối ô tính tổng trong côngthức tạm được bao quanh bởi 1 đường biên chấm chấm. Dùng chuột tô lại khối ô đúng. Như hình 353, Khi tính tổng cho ô F2 và F3, Excel đã làm đúng. Nó đoán rằng bạn muốn tính tổng cho dữ liệu trên dòng. Nhưng đến dòng 4, có thêm 1 lựa chọn: bạn muốn tính tổng theo dòng hay tổng 2 ô phía trên? Excel luôn ưu tiên chọn tổng theo cột và sẽ tính tổng 2 ô phía trên của F4. Sau khi nhấn AutoSum, để ý rằng khối ô F2:F3 có 1 đường viền và chữ F2:F3 trên côngthức bị bôi đen. Việc này giúp bạn chọn lại khối ô đúng. Có 3 cách:
- Dùng chuột chọn lại B4:E4 và Enter.
- Dùng bàn phím, gõ B4:E4
- Dùng phím mũi tên, dùng phím mũi tên ngang di chuyển ô chọn đến E2. Nhấn phím mũi tên xuống, du chuyển đến E4. Trong khi giữ phím Shift, dùng phím mũi tên ngang 3 lần di chuyển ô chọn sang trái đến B4.

Fig. 354

Additional Details: The problem described in this section will always happen in the third and fourth rows of the data. When you try using the AutoSum button in F6 and beyond, Excel will correctly sum all the data in that row. AutoSum can also fail when one number in your range contains a SUM formula. The provisional formula will offer to sum a formula extending up to but not including the previous SUM formula.
Alternate Strategy: You can choose to enter all of the totals at one time by using the AutoSum button. This is faster and will eliminate the problem described above. Follow these steps.
203 of 354
1) Highlight the entire range that needs a SUM formula as shown in Fig. 355.

Nói thêm: Vấn đề như nói ở trên luôn xảy ra với các dòng thứ 3 và thứ tư của dữ liệu. Khi tính cho dòng thứ 5 tức ô F6, Excel lại đoán đúng là bạn muốn cộng hàng ngang. AutoSum còn có khả năng nhận biết 1 ô nào đó có chứa côngthức Sum. Công thức tạm hiện ra sẽ đề nghị 1 cách tính tổng hướng lên trên nhưng chừa ô có côngthức Sum lại.
Cách khác: Bạn có thể chọn cách tính tổng nhanh cho nhiều 6 1 lúc bằng AutoSum. Cách này nhanh hơn và loại trừ được rắc rối trên. Làm theo các bước sau:
1. Đánh dấu khối ô cần gán côngthức tổng như hình 355

Fig. 355

2) Hit the AutoSum button. Excel makes a prediction and fills in the total formulas automatically, as shown in Fig. 356. Excel does not show the provisional formula. So, check one formula to see that it is correct.

2. Nhấn nút AutoSum. Excel tính toán dự đoán và nhét đầy các côngthức Sum vào vùng chọn như hình 356. Nó không cho bạn côngthức tạm nữa, vậy kiểm tra 1 côngthức trong nhóm xem có đúng hay không.

Fig. 356
204 of
Summary: The AutoSum function does not always correctly predict the range to be totaled. It is easy to use the mouse or keyboard to show Excel the correct range.

Tóm tắt: Auto Sum không phải lúc nào cũng dự đoán dúng các ô cần tính tổng. Nhưng ta có thể dễ dàng sửa lại bằng chuột hoặc bàn phim.

 

[ptm0412]

USE AUTOSUM BUTTON TO ENTER AVERAGES, MIN, MAX, AND COUNT
DÙNG AUTOSUM TÍNH TRUNG BÌNH, NHỎ NHẤT, LỚN NHẤT, VÀ ĐẾM.

Problem: Instead of totals, you need to enter an Average formula quickly, as shown in Fig. 357.

Vấn đề: Thay vì tính tổng, bạn lại cần 1 côngthức tính trung bình, như hình 357.

Fig. 357

Strategy: Use the dropdown arrow located next to the AutoSum button, as shown in Fig. 358. Instead of selecting Sum, use the Average option.

Biện pháp: Nhấn mũi tên xổ xuống kế bên nút AutoSum. Thay vì chọn Sum, hãy chọn Average.

Fig. 358

Excel enters a provisional Average formula, as shown in Fig. 359.
Excel cung cấp 1 côngthức tạm tính trung bình

Fig. 359

If Excel correctly predicted your data, as shown in Fig. 360, hit Enter to accept the formula.

Nếu Excel đoán đúng dữ liệu của bạn về hướng tính, hãy nhấn Enter.

Fig. 360

Additional Details: Excel does NOT remember the last setting of the AutoSum button. If you do an Average and then use just the AutoSum button, it will return to using a SUM formula.
Additional Details: The Max option will use the MAX function to find the largest numeric value. The Min option will use the MIN function to return the smallest numeric value. The Count option will count the number of numeric entries in the list using the COUNT function.
Summary: The dropdown arrow next to the AutoSum function offers access to finding the Average, Min, Max, or Count of a range.


Nói thêm: Excel không nhớ cài đặt cuối sau khi bạn dùng nó. Nếu bạn tính trung bình rồi sau đó lại nhấn nó 1 lần nữa, nó lại quay về tính tổng.
Nói thêm nữa: Nếu bạn chọn MAX, Excel sẽ dùng hàm MAX() để tìm giá trị số lớn nhất. Tương tự là lựa chọn MIN sẽ dùng hàm MIN () để tìm giá trị số nhỏ nhất. Lựa chọn Count sẽ dùng hàm COUNT() đếm các giá trị số trong khối tính toán.

Tóm tắt: Nhấn mũi tên xổ xuống kế bên nút AutoSum để tìm giá trị trung bình, giá trị lớn nhất, nhỏ nhất, hoặc đếm chúng.

 

[ptm0412]

THE COUNT OPTION OF THE AUTOSUM DOESN’T APPEAR TO WORK
HÀM COUNT() NHẬN TỪ AUTOSUM DƯỜNG NHƯ KHONG CHẠY TỐT.

Problem: You are using the Count option from the dropdown next to the AutoSum button on the toolbar. It does not appear to provide consistent results. In Fig. 361, cells B11 and C11 both contain a count of the cells in rows 2 through 10 of each column. One function indicates that there are nine entries; the other function indicates there are only two. Clearly, both columns have nine entries.

Vấn đề: Bạn chọn mục Count trong phần xổ xuống của AutoSum. Dường như nó không thể hiện đúng kết quả cần tìm. (không tính hết các ô gộp trong tham chiếu - Ptm0412). Ở hình 361, ô B11 và C11 đều chứa côngthức đếm những gì có trong 2 cột B và C từ dòng 2 đến dòng 10. Một côngthức cho kết quả chỉ có 2 ô có dữ liệu, còn công thức kia thì 9. Thực sự cả 2 cột 9 ô đều có đủ dữ liệu.

Fig. 361

Strategy: The COUNT function will only count numeric entries. If you need to count all entries, you have to use the COUNTA function. One solution is to edit the formula in B2 and add an A after the T in COUNT. The other method is to enter the formula correctly in the first place.
1) Put the cell pointer in B11. Choose the dropdown arrow next to the AutoSum button. From the list, select More Functions…, as shown in Fig. 362.

Biện pháp: Hàm COUNT() chỉ đếm các giá trị số. nếu bạn muốn đếm mọi ô có dữ liệu, hãy dùng COUNTA. Một giải pháp là sửa côngthức bằng cách thêm chữ A vào sau chữ T của COUNT. Cách khác là nhập vào công thức đúng ngay từ đầu.
1. Chuyển ô chọn đến B11, trong danh sách xổ xuống từ nút mũi tên kế bên nút AutoSum, chọn More Function.

Fig. 362

2) There are hundreds of functions available. You can never remember if COUNTA is in the Math & Trig section or somewhere else. Type the word “count” in the search box and choose Go, as shown in Fig. 363.

- Excel có sẵn hàng trăm côngthức. Bạn sẽ chả nhớ nổi COUNTA là hàm thuộc nhóm Toán học hay nhóm nào khác. Vậy hãy gõ Count vào hộp tìm kiếm và nhấn Go:

Fig. 363

Excel will return a list of all functions related to the COUNT function. A description of the selected function appears below the list, as shown in Fig. 364.

Excel sẽ liệt kê tất cả các hàm có nội dung hoặc tên liên quan đến count. Một đoạn mô tả hàm sẽ xuất hiện bên dưới cái danh sách vừa tìm được.

Fig. 364
3) You might need to scroll through the list to find the COUNTA function. As shown in Fig. 365, when you find COUNTA, choose OK

- Bạn sẽ phải kép thanh trượt xuống để tìm hàm COUNTA. Khi tìm thấy, chọn nó và nhấn OK.

Fig. 365

You will now see the Function Arguments dialog box. Excel has analyzed your data and predicted the range that you want to use. However, Excel is not good at predicting data when the range contains numeric and alphanumeric entries. In this particular case, as shown in Fig. 366, Excel assumes we only want to COUNTA the range B9:B10.

Bạn sẽ thấy hộp thoại tham số hàm. Excel lại đọc dữ liệu của bạn và đoán các ô tham chiếu bạn muốn dùng. Dù vậy, Excel không giỏi dự đoán lắm khi khối ô của bạn lẫn lộn các dữ liệu kiểu số và dữ liệu vừa số vừa chữ. Trong trường hợp này, Excel cho rằng bạn chỉ muốn CountA khối ô B9:B10.

Fig. 366

4) If you can see the data on the worksheet, use the mouse and highlight the correct range, as shown in Fig. 367.

Nếu bạn ngó thấy dữ lliệu trên bảng tính, dùng chuột tô đúng vùng cần tính

Fig. 367
1 of 354
5) Release the mouse. Choose OK in the function arguments dialog to accept the formula.
Result: As shown in Fig. 368, the COUNTA function returns the proper value.

Buông chuột ra, nhấn OK trong hộp thoại tham số hàm để chấp nhận công thức. Kết quả: CountA đã đếm đúng.

Fig. 368

Summary: The COUNT function does not count text entries in a list. Use the COUNTA function instead.

Tóm tắt: Hàm COUNT không đếm các giá trị text trong dãy. Hãy dùng hàm COUNTA thay vào.

 

[ptm0412]

AUTOMATICALLY NUMBER A LIST OF EMPLOYEES
TỰ ĐỖNG ĐÁNH SỐ THỨ TỰ
Problem: You work in Human Resources. You have a list of employees separated by department. As shown in Fig. 369, you have a numerical sequence in column A and the employees’ names in column B. Every time that you hire or fire an employee, you have to manually renumber all of the employees.

Vấn đề: Bạn làm việc cho bộ phận nhân sự. Bạn có 1 danh sách nhân viên phân ra các phòng ban. Như hình 369, bạn có cột A đánh số thứ tự và danh sách tên nhân viên ở cột B. Mỗi khi bạn thuê thêm hay sa thải 1 công nhân, bạn phải đánh số thứ tự lại cái danh sách này.

Fig. 369

Strategy: Replace the numbers in column A with a formula that will count the entries in column B. The formula should count from the current row all the way up to row 1. The COUNT function will not work, because it only counts numeric entries. You need to use the COUNTA function.
• The range that should be counted should extend from B1 to the current row.
• The notation to always use B1 is B$1.
1) As shown in Fig. 370, enter this formula in cell A2: =COUNTA(B$1:B2). When you copy this formula down a row, the range that is counted will extend from B1 to B3, as shown in Fig. 371. This is because the B2 portion of the above formula is a relative reference that is allowed to change as the formula is copied. The dollar sign in the B$1 reference tells Excel that no matter where you copy the formula, it should always refer to cell 1.

Biện pháp: Thay các số thứ tự ở cột A bằng 1 công thức đếm các dữ liệu nhập ở cột B. Công thức này phải đếm từ dòng hiện tại chứa công thức lên đến dòng 1. Hàm Count đơn thuần sẽ không giúp gì vì nó chỉ đếm những giá trị số. Bạn phải dùng hàm CountA.
- Vùng cần đếm phải kéo dài từ B1 đến dòng có ô chứa công thức.
- Ký hiệu để luôn dùng B1 để bắt đầu đếm là B$1.
1. Như hình 370, gõ công thức ở ô A2: =COUNTA(B$1:B2). Khi sao chép công thức này xuống 1 ô, vùng đếm sẽ được mở rộng thành B$1:B3, như hình 371. Đó bởi vì ô tham chiếu B2 của công thức bên trên là 1 địa chỉ tương đối nên cho phép thay đổi khi sao chép công thức. Dấu $ ở B$1 cho biết đây là 1 tham chiếu tuyệt đối dòng và khi sao chép đến bất kỳ đâu, Excel luôn tham chiếu đến dòng 1.

Fig. 370

Fig. 371

2) Copy the formula down to all of the names in your list. They will be numbered 1 through 19, just as when you typed the names in manually. When an employee leaves the company, you can simply delete the row, All of the other numbers will change to indicate that you now have a total of 18 employees, as shown in Fig. 372.

2. Sao chép công thức xuống đến mọi tên trong danh sách. Nó sẽ cho các số từ 1 đến 19, như khi bạn gõ các tên tuần tự. Khi 1 nhân viên nghỉ việc, bạn chỉ cần xoá dòng chứa tên người đó, mọi số thứ tự sẽ thay đổi tự động và cho biết bạn còn 18 nhân viên như hình 372.

Fig. 372

When you hire a new marketing person, insert a blank row, enter his or her name, and then copy any formula from A to the new row. As shown in Fig. 373, all of the subsequent employees will be renumbered.
Khi bạn tuyển dụng thêm 1nhân viên bộ phận Marketing, chèn thêm 1 dòng, điền tên nhân viên mới vào, và copy công thức của cột A cho nó. Như hình 373, mọi số thứ tự sẽ tự đếm lại.

Fig. 373

Summary: While this is a specific example, the concept of using a range as an argument where only one portion of the range contains an absolute reference is a common solution to keeping a running total of all cells above the current row.

Tóm tắt: Với cái thí dụ đơn giản này, sự linh hoạt khi sử dụng địa chỉ tuyệt đối dòng cho 1 trong 2 phần của vùng chọn, là 1 giải pháp để tính tổng cho mọi ô nằm phía trên ô đang tính.

 

[ptm0412]

RANK SCORES
THỨ HẠNG THEO ĐIỂM.

Problem: You have four writers working on a project. Each week, you report how many pages they have written towards their goal, as shown in Fig. 374. You wish to have a formula that ranks them in high-to-low order.

Vấn đề: Bạn có 4 nhân viên đánh máy làm việc cho 1 dự án. Mỗi tuần, bạn lập 1 báo cáo xem mỗi người đã đánh được bao nhiêu trang, như hình 374. Bạn muốn có 1 công thức sắp hạng họ theo số trang đó theo thứ tự từ nhiều đến ít.

Fig. 374

Strategy: If you are not concerned about ties, then use the RANK function. The function requires two arguments. In plain language, you are asking the function to assign a rank to the value in H23 amongst all
values in H23:H26. As shown in Fig. 375, in cell F23, use =RANK(H23,$H$23:$H$26). Note that the H23:H26 range in the second parameter is in absolute reference style due to the dollar signs. This allows the formula to be easily copied to each name in the list.

Biện pháp: Nếu bạn không quan tâm đến các ràng buộc cho lắm, hãy dùng hàm Rank. Hàn này đòi hỏi 2 tham số. Như ngôn ngữ thường dùng, bạn yêu cầu hàm cho biết thứ hạng của giá trị ô H23 trong số các giá trị trong dãy H23:H26. Như hình 375, trong ô F23, công thức là = RANK(H23:$H$23:$H$26). Chú ý rằng tham số thứ 2 là 1 tham số tuyệt đối căn cứ vào các dấu $ đánh vào nó. Việc này cho phép sao chép công thức đến mọi tên trong bảng tính.

Fig. 375

Additional Details: The above version of the function will rank the values in high-to-low order. Sometimes you might need to rank in a low to - high fashion. Golf is one such instance. You can specify an optional third parameter to specify the order. Using a third parameter of 1 will force the rank results to be reported in low-to-high order, as shown in Fig. 376.

Nói thêm: Cách đánh thứ hạng củahàm như trên sẽ cho hạng từ điểm cao đến điểm thấp. Đôi khi bạn muốn sắp hạng ngược lại theo cách từ điểm thấp đến điểm cao. Môn đánh gôn là 1 thí dụ. bạn sẽ cần 1 tham số thứ ba cho hàm. Tham số thứ 3 này quy định sự sắp hạng, và nếu bằng 1 sẽ buộc Excel sắp hạng theo thứ tự từ điểm thấp đến điểm cao.

Fig. 376

Summary: The RANK function is useful for producing a ranking by using formulas.
Tóm tắt: Hàm Rank dùng để sắp hạng.

 

[ptm0412]

SORTING WITH A FORMULA
SẮP THỨ TỰ VỚI CÔNG THỨC.

Problem: In the previous example, you used the RANK function to find the relative rank order of four writers, as shown in Fig. 377. Now you want to use a formula to produce a sorted list of the writers in high-to low sequence.

Vấn đề: Trong thí dụ trước, bạn dùng hàm Rank để tìm thứ hạng cho 4 nhân viên, như hình 377. Giờ thì bạn muốn dùng công thức tạo 1 danh sách mới sắp thứ tự các nhân viên này theo thứ tự số trang từ lớn đến nhỏ.

Fig. 377

Strategy: In cells F28 through F31, enter the numbers 1 through 4. Use the VLOOKUP function to return the name in column G and the pages in column H. Here is how this function works. VLOOKUP stands for vertical lookup. There are four parameters to the VLOOKUP function. In plain language, you are asking Excel to look for the value in F28 in the first column of F23:H26. When Excel finds an exact matching value, it returns the name in the second column of the lookup range. The first parameter is the value that you are trying to match. In the case of cell G28, you would be looking for the value in F28. Write this as $F28 so that you can copy the formula to column H without rewriting that parameter.
The second parameter is the database range containing rows and columns of data. The key value that you are looking up must be in the first column of the range. In this case, it would be $F$23:$H$26. Note that you use dollar signs before both the column letters and row numbers in order to keep the database range absolute as you copy the formula. The third parameter tells Excel the column from which you want to return the answer. For the name in column G, it is column 2 of the range F23:H26. For the page count in column H, it is column 3 of the range F23:H26.
The fourth parameter tells Excel if you will allow a close match. If your original data is not sorted, you are required to specify an exact match. For the fourth parameter, use TRUE for a close match and FALSE for
an exact match. Follow these steps:

Giải pháp: Trong các ô từ F28 đến F31, nhập vào các số từ 1 đến 4. Dùng hàm VLookup để lấy những cái tên từ cột G và những con số trang ở cột H. Đây là cách hàm VLookup làm: VLookup là hàm dùng để tìm kiếm theo hàng dọc. Hàm này có 4 tham số. Với ngôn ngữ thông thường, bạn đang yêu cầu Excel tìm giá trị của ô F28 trong cột đầu tiên của vùng F23:H26. Khi Excel tìm thấy giá trị bằng chính xác F28, nó phải lấy giá trị tên tương ứng trong cột thứ 2 của vùng tìm kiếm. Vậy tham số thứ nhất là giá trị cần tìm. Đối với ô G28, giá trị cần tìm là ô F28. Hãy dùng địa chỉ tuyệt đối cột $F28 để có thể sao chép công thức sang cột H mà không phải sửa lại công thức.
Tham số thứ 2 là vùng dữ liệu gồm những dòng và cột chứa dữ liệu. Dữ liệu mà bạn hy vọng Excel tìm thấy trong đó, phải nằm ở cột đầu tiên của vùng này. Với trường hợp này, đó là vùng $F$23:$H$26M. Chú ý hãy dùng những dấu $ để cố định cả dòng và cột của vùng dữ liệu khi sao chép công thức.
Tham số thứ 3 là số thứ tự cột mà bạn muốn Excel lấy dữ liệu tương ứng từ đó. Với tên nằm ở cột G, đó là cột thứ 2 của vùng, với số trang mỗi người thực hiện, đó là cột thứ 3 của vùng.
Tham số thứ 4 cho Excel biết bạn có muốn tìm chính xác không. Nếu vùng dữ liệu chưa được sắp xếp, bạn cần 1sự tìm kiếm chính xác. Dùng giá trị True cho tham số thứ 3 để tìm 1 giá trị gần đúng và False để tìm chính xác. Làm theo các bước sau:

1) As shown in Fig. 378, enter the following formula in G28: =VLOOKUP($F28,$F$23:$H$26,2,FALSE).
2) Copy cell G28 to H28. The result in H28 will also be Josh, as shown in Fig. 379.
3) Edit the formula in the formula bar to change the third parameter from column 2 to column 3, as shown in Fig. 380. The result in H28 will now contain the number of pages written by Josh, as shown in Fig. 381.

1. Xem hình 378, gõ công thức sau vào ô G28: =VLOOKUP($F28,$F$23:$H$26,2,FALSE).
2. Sao chép công thức này qua ô H28. Kết quả cũng là Josh như hình 379.
3. Sửa tham số thứ 3 từ 2 thành 3 như hình 380. Kết quả ô H28 sẽ như hình 381, cho biết số trang mà Josh đã viết.

Fig. 378

Fig. 379

Fig. 380

Fig. 381

4) Copy G28:H28 down to the next three rows. You will now have a sorted list of the data, as shown in Fig. 382.
4. Saochép công thức từ G28:H28 xuống 3 dòng kế. bạn sẽ có 1 danh sách được sắp thứ tự như hình 382.

Fig. 382

 

[ptm0412]

SORTING WITH A FORMULA (continue)
SẮP THỨ TỰ VỚI CÔNG THỨC. (tiếp theo)

Additional Details: Your goal is to always enter one formula that you can copy to the entire data range. In this case, your formula in G28 could be copied to anywhere in column G, but when you copied it to column H, the third parameter had to be manually edited. You needed to plan ahead to use the proper combination of dollar signs in the references in order to ensure that three of the four parameters were correct when you
copied the formula to column H. If you find that you have only a few columns of data in an example like this, you can edit the third parameter manually. If you have many columns of data, this could get tedious. The =CELL(“Col”,G28) function would return a column number of cell G28. This would return a 7 for column G and an 8 for column H. You could have used the following formula in G28:
=VLOOKUP($F28,$F$23:$H$26,CELL(“Col”,G28)–5,FALSE)
If you enter this formula in G28, you can copy it to all rows and columns of your results table.

Nói thêm: Bạn vẫn muốn tạo chỉ 1 công thức và sao chép được cho cả vùng dữ liệu (cả cột và dòng). Theo thí dụ trên, công thức ở ô G28 có thể sao chép đến bất kỳ ô nào trong cột G, nhưng khi sao chép sang cột H, tham số thứ 3 phải sửa lại bằng tay. Bạn cần xây dựng 1 công thức với các dấu $ bằng cách nào đó cho các tham chiếu, sao cho 3 tham số tham chiếu trong số 4 tham số đều đúng khi sao chép công thức đến bất cứ đâu. Nếu dữ liệu của bạn ít dòng như thí dụ trên, bạn có thể sửa bằng tay. Nhưng nếu dữ liệu của bạn nhiều cột, sẽ rất là chán.
Hàm =Cell(“Col”,G28) hoặc = Column(G28) sẽ cho số thứ tự của cột G tính từ cột A. Đó là số 7 cho cột G và 8 cho cột H. Vậy bạn có thể dùng công thức sau cho G28:
=VLOOKUP($F28,$F$23:$H$26,CELL(“Col”,G28)–5,FALSE)
hoặc =VLOOKUP($F28,$F$23:$H$26,COLUMN(G28)–5,FALSE)
Thế là bạn có thể sao chép công thức từ G28 đến mọi dòng và cột trong bảng kết quả.

Summary: After using a RANK function to assign rank values to a list, use a second table with the numbers 1 through n and a series of VLOOKUP formulas in order to return a sorted list of the data.

Tóm tắt: Sau khi dùng hàm Rank để sắp hạng cho 1 danh sách, bạn có thể tạo 1 bảng thứ 2 với các số từ 1 đến n và dùng các công thức chứa hàm VLookup để sắp thứ tự bảng này theo kết quả sắp hạng.

 

[ptm0412]

RANK A LIST WITHOUT TIES
SẮP HẠNG 1 DANH SÁCH KHÔNG CÓ ĐỒNG HẠNG
Problem: The RANK function behaves strangely when there are ties. It is possible to have a list where two people are ranked second and no one is ranked third. In Fig. 383, Dora and Jerry are ranked second with 90
units produced. Next is Harry with 86 units. Harry will receive a rank of 4. This behavior is by design. However, if you are later going to use VLOOKUPs to sort the employees by productivity, having two people ranked as #2 and no one ranked as #3 is not a good situation. In the formulas in columns F and G, the spreadsheet designer counted on there being one employee at each rank from 1 to 10. Since Excel did not assign anyone to a rank of #3 or #7, Jerry and Bill do not show up in the list, as shown in Fig. 384.
Vấn đề: Hàm Rank cư xử khá kỳ lạ khi có mhững giá trị đồng hạng. Rất có thể sẽ có 1 danh sách trong đó có ít nhất 2 người được sắp hạng nhì và không có hạng 3. Ở hình 383, Dora và Jerry cùng được sắp hạng nhì với 90 đơn vị sản phẩm. Kế đến là harry với 86 sản phẩm. Harry bị sắp hạng tư. Cái này là do thiết kế của hàm Rank.
Dù vậy khi dùng VLookup để sắp thứ tự lại như bài trước, 2 người đồng hạng 2 và không có người nào hạng 3 không phải là trường hợp tốt. Trong công thức ở cột G và H, người dùng muốn tìm thấy tên của mỗi nhân viên trong dãy thứ hạng từ 1 đến 10. Nhưng Excel không tìm thấy ai có thứ hạng 3 và 7, nên Jerry và Bill không được thể hiện lên danh sách như hình 384.

Fig. 383

Fig. 384

Strategy: In this case, you absolutely want the list in A4:A13 to be ranked without ties. The generally accepted solution may seem rather convoluted, but it works. In plain language, the formula in column C
will say, “Give me the RANK of this value, plus 1 for every row above me that has an identical score.” As shown in Fig. 385, this can be accomplished with the following formula:
=RANK(B4,$B$4:$B$13)+COUNTIF(B$3:B3,B4)

Giải pháp: Trong trường hợp này, bạn muốn sắp hạng danh sách trong cột A từ A4 đến A13 không có đồng hạng. Giải pháp chấp nhận được như sau có vẻ không công bằng, nhưng giải quyết được chuyện này. Theo ngôn ngữ thông thường, công thức trong cột C muốn nói: Cho tôi thứ hạng của số liệu này trong dãy, và công 1 cho mỗi dòng ở trên nếu dòng đó có cùng số liệu với dòng này. Xem hình 385, câu này được thể hiện bằng công thức:
=RANK(B4,$B$4:$B$13)+COUNTIF(B$3:B3,B4)

Fig. 385

As you copy this formula down, the first parameter of COUNTIF will expand to include B3 down to the row above the current row. Thus, in cell C13, the formula will be as follows:
=RANK(B13,$B$4:$B$13)+COUNTIF(B$3:B12,B13)
The COUNTIF portion of the formula counts how many rows above the current row have an identical score. For each row above that is a tie, 1 gets added to the current row. This causes Bill to be ranked seventh instead of sixth. It may not be fair that Ashley appears before Bill, but in the summary report, anyone can notice that they have a tie.
Khi sao chép công thức này xuống, tham số thứ nhất của hàm countif sẽ mở rộng bao gồm từ B3 đến B12 là ô ở dòng ngay trên ô đang tính. Thế nên công thức ô C13 là:
=RANK(B13,$B$4:$B$13)+COUNTIF(B$3:B12,B13)
Phần Countif của công thức đếm xem có bao nhiêu dòng phía trên dòng hiện hành có cùng số liệu như dòng hiện hành. Với mỗi dòng đếm được, nó sẽ cộng 1 vào kết quả của phần Rank của công thức. Kết quả là Bill sẽ được xếp hạng 7 thay vì hạng 6 đồng hạng. Sẽ không công bằng nếu Ashley có thứ hạng cao hơn Bill, nhưng coi toàn thể báo cáo, người đọc cũng hiểu rằng họ cùng số điểm và cùng hạng.

Summary: Add a COUNTIF function to the RANK function in order to prevent ties.
Tóm tắt: Cộng 1 Countif() vào hàm rank() để tránh chuyện cùng hạng.

 

[ptm0412]

ADD COMMENTS TO A FORMULA
CHO GHI CHÚ VÀO TRONG CÔNG THỨC

Problem: You spent a great deal of time perfecting a formula, as shown in Fig. 386. You would like to leave yourself notes about it.
Strategy: There is an old Lotus 1-2-3 function that is still in Excel. This function is the N function. It turns out that N of a number is the number and N of any text is zero. Thus, you can add several N functions to a formula without changing the result, provided that they contain text. Thus, if you have figured out some obscure formula, you can leave yourself notes about the formula, as shown in Fig. 387.
Vấn đề: Bạn phải tốn thời giờ hoàn chỉnh 1 công thức như hình 386. Bạn muốn gắn những ghi chú vào trong công thức về cách tạo ra nó.
Biện pháp: Có 1 hàm cũ của Lotus 1-2-3 hiện vẫn còn trong Excel, đó là hàm N(). Nó trả về giá trị N của 1 số là số đó, và giá trị N của 1 chuỗi text là số 0. Thế nên bạn có thể nhét đầy những hàm N vào công thức mà không làm thay đổi kết quả, nếu chúng là N của các chuỗi text. Và để làm rõ nghĩa những đoạn công thức mù mờ, bạn cho thêm những ghi chú vào ngay trong công thức như hình 387.

Fig. 386

Fig. 387

Summary: For particularly complicated formulas, leave yourself detailed comments right in the formula.
Tóm tắt: Đối với những công thức đặc biệt phức tạp, hãy cho những ghi chú diễn giải vào trong công thức dưới dạng hàm N()

 

[ptm0412]

CALCULATE A MOVING AVERAGE
TÍNH TOÁN 1 TRUNG BÌNH ĐỘNG.

Problem: You have 36 months of sales data, as shown in Fig. 388. In order to create a Prediction of sales, you want to calculate a three-month moving average. You will then later create a trendline from the moving average.

Vấn đề: Bạn có dữ liệu doanh thu của 36 tháng, như hình 388. Nhằm tạo ra 1 dự đoán cho doanh thu, bạn muốn tính 1 trung bình động doanh thu 3 tháng một. Sau dó bạn sẽ tạo 1 đường hồi quy từ cái trung bình động này.

Fig. 388

Strategy: You need two months of history before you can begin calculating a three-month moving average.
1) In cell C4, use the formula =AVERAGE(B2:B4). Note that when you enter this formula, Excel will be concerned because your formula ignores similar data in cell B5. In this case, you are smarter than Excel, so you can use the dropdown on the Exclamation sign to tell Excel to ignore the error, as shown in Fig. 389.

Biện pháp: Bạn cần 2 tháng doanh thu trước khi bắt đầu tính cái trung bình 3 tháng đầu tiên.
1. Ở ô C4, dùng công thức = AVERAGE(B2:B4). Chú ý rằng khi gõ công thức này, Excel sẽ bị đụng chạm, vì công thức của bạn không đếm xỉa đến dữ liệu từ B5 trở xuống. Vì bạn thông minh hơn Excel, và bạn đã tính đúng, nên bạn nhấn vào dấu chấm than và trong danh sách lệnh xổ xuống, bạn yêu cầu Excel bỏ qua không đươc nhắc nhở chuyện này như hình 389.

Fig. 389

2) Double-click the Fill handle in C4 to copy the formula down to the rest of your dataset.
2. Double-click cái FIll handle của ô C4 để sao chép công thức xuống các dòng dưới.

Result: Moving averages are good if your underlying data has spikes in the sales. It is hard for an automatic system to predict spikes. The moving average smoothes these spikes out of the system, as shown in Fig.390. A forecast based on the moving average line may be more accurate than a forecast based on the original data.

Kết quả: Trung bình động doanh thu rất hiệu quả nếu bạn gắn chặt nó với doanh thu. Hệ thống tự động sẽ khó tính con số dự đoán. Một dự báo dựa trên trung bình động sẽ chính xác hơn là dựa vào bản thân dữ liệu gốc.

Fig. 390

Summary: Use the AVERAGE function to create a three-month moving average to be used for forecasting.

Tóm tắt: Dùng hàm AVERAGE để tạo trung bình động của doanh thu 3 tháng một, để dùng cho mục đích dự báo.

 

[ptm0412]

CALCULATE A TRENDLINE FORECAST
TÍNH TOÁN 1 ĐƯỜNG HỒI QUY ĐỂ DỰ BÁO.

Problem: You have historical sales data by month, as shown in Fig. 391. You want to predict future sales by month.
Vấn đề: bạn có thống kê doanh thu theo tháng như hình 391. Bạn muốn dự báo doanh thu cho tháng tiếp theo.

Fig. 391

Strategy: You want to use the least-squares method to fit the sales data to a trendline. Excel offers a function called LINEST that will calculate the formula for the trendline. You might remember from math that a trendline is represented by this formula: y = mx + b.
Y is the revenue for the month, M is the slope of the line, X is the month number, and B is the y intercept. If you were to look at the data, you might guess that the prediction for a given month is $10,000 + the month
number times $400. In this case, the value for b would be 10000 and the value for m would be 400. This is just my wild guess – Excel can calculate the number exactly.
However, LINEST is a very special function. Instead of returning one number, it actually returns two (or more) numbers as the result.
Fig. 392, shows the wrong way to enter the LINEST function. If you select a single cell and enter =LINEST(C2:C35), it will return a single number.

Biện pháp: bạn muốn dùng phương pháp bình phương bé nhất để gán doanh thu vào 1 đường hồi quy tuyến tính. Excel cung cấp cho bạn 1 hàm LINEST tính ra các hệ số cho đường biểu diễn hàm hồi quy tuyến tính. Bạn có thể nhớ rằng trong toán học, 1 đường tuyến tính có dạng y = ax + b.
Y là doanh thu từng tháng, a là hệ số góc của đường thẳng, x là biến số tháng, và b là giá trị chặn bởi trục y. Nếu bạn nhìn vào bảng dữ liệu, bạn có thể đoán dự báo doanh thu của 1 tháng bất kỳ là 10.000$ cộng với số chỉ tháng đó nhân với 400$. Trong trường hợp đó, b là 10.000 và a là 400. Đây chỉ là đoán chừng, Excel có thể tính chính xác hơn.
Hình 392 cho thấy cách dùng sai của hàm LINEST: Nếu bạn chọn 1 ô đơn lẻ và gõ công thức = LINEST(C2:C35), nó sẽ chỉ cho 1 giá trị.

Fig. 392

Entering the formula the wrong way returns a single answer of 204.8133, as shown in Fig. 393. The first time that you do this, you might wonder how the number 204.81 could describe a line. It turns out that Excel really wants to return two numbers from the function. The trick is to first select two cells that are side-by-side.

Nhập công thức cách sai này sẽ cho 1 giá trị duy nhất là 204.8133 như hình 393. Khi lần đầu tiên làm như vậy, bạn sẽ tự nhủ với 1 consố 204.81 làm sao có thể mô tả đủ 1 đường thẳng. Excel sẽ đòi hỏi để đưa ra 2 kết quả cho hàm LINEST. Nghĩa là bạn trước tiên phải click chọn 2 ô kế nhau trên cùng hàng ngang.

Fig. 393

1) Begin to enter the function in the first cell, as shown in Fig. 394.
2) After you type the closing parenthesis, hold down Ctrl+Shift while you hit Enter. Excel returns both the slope and the y-intercept, as shown in Fig. 395.

1. Bắt đầu gõ công thức vào thanh công thức như hình 394.
2. Sau khi đánh xong dấu đóng ngoặc, nhấn giữ 2 phím Ctrl +Shift trong khi gõ Enter. Excel sẽ trả về cho bạn cả hệ số góc a của đường thẳng và tham số cộng b như hình 395.

Fig. 394

Fig. 395

3) Fill in headings, as shown in Fig. 396. In column D, enter a formula to calculate the predicted sales trendline. You can then graph columns B to show how well the prediction matches the historical actuals.

3. Điền tiêu đề cột D là Predict. như hình 396. Ở cột D gõ 1 công thức để tính doanh thu dự báo theo tháng. Bạn có thể vẽ biểu đồ cho dữ liệu trong vùng B để xem sự dự báo khớp với dữ liệu thực chặt chẽ như thế nào.

Fig. 396

4) Clear cell B1. Select B147 and select Insert – Chart. Select a line chart and hit Finish.
4. Xoá ô B1. Chọn vùng B147 vào chọn Insert – Chart. Chọn loại biểu đồ đường kẻ và nhấn Finish.

When data along the x-axis of your chart contains dates, it is best to delete the upper left corner cell of your dataset before creating the chart.
Khi dữ liệu thuộc trục x của biểu đồ là dữ liệu ngày tháng, tốt nhất là xoá ô góc trên bên trái của vùng dữ liệu trước khi vẽ biểu đồ.

As shown in Fig. 397, the resulting chart shows that the predicted trendline comes fairly close to the actuals. You can also see that the formula predicts that you will be selling almost $20K a month one year from now.
Như hình 397, biểu đồ chỉ cho ta đường hồi quy ôm sát vào đường biểu diễn doanh số thực như thế nào. Nó cũng cho thấy ta sẽ đặt doanh thu 20K 1 tháng sau 12 tháng nữa kể từ bây giờ.

Fig. 397

Gotcha: When you select two cells for the LINEST function, they must be side by side. If you try to select two cells that are one above the other, you will just get two copies of the slope.

Ghi chú: Khi chọn 2 ô để gõ hàm LINEST, nhớ chọn 2 ô kế nhau trên hàng ngang. Nếu bạn chọn 2 ô theo hàng đứng, bạn sẽ có kết quả 2 ô đều là hệ số góc a.

 

[ptm0412]

CALCULATE A TRENDLINE FORECAST (CONTINUE)
TÍNH TOÁN 1 ĐƯỜNG HỒI QUY ĐỂ DỰ BÁO (tiếp theo).
Alternate Strategy: A different method is to use the INDEX function to pluck a specific answer from the array.
=INDEX(LINEST(C2:C35),1,1) will return the first element from the array, as shown in Fig. 398. This is the slope.
=INDEX(LINEST(C2:C35),1,2) will return the second element from the array. This is the y-intercept.

Fig. 398

Cách khác để sử dụng hàm LINEST: 1 cách khác là dùng hàm Index để lấy ra 1 giá trị nào đó của mảng:
=INDEX(LINEST(C2:C35),1,1) sẽ cho giá trị phần tử thứ nhất của mảng như hình 398, đó là hệ số góc a.
=INDEX(LINEST(C2:C35),1,2) sẽ cho giá trị phần tử thứ hai của mảng, đó là tham số cộng b.

Summary: The LINEST function will automate the process of performing a least-squares method to fit a line to a series of actual sales. Because the function returns multiple values, you have to use care when
entering. Either enter it in multiple cells with the Ctrl+Shift+Enter key combination or use the INDEX function to extract values.

Tóm tắt: Hàm LINEST sẽ tự động tính theo phương pháp bình phương bé nhất để tính tham số cho hàm hồi quy tuyến tính của 1 dãy các doanh số thực. Bởi đây là 1 hàm mảng, nên cẩn thận khi gõ công thức hàm. Dùng Ctrl Shift Enter để có đủ 2 kết quả của hàm, hoặc dùng hàm Index để trích lấy 1 phần tử của mảng.

 

[ptm0412]

BUILD A MODEL TO PREDICT SALES BASED ON MULTIPLE REGRESSION
XÂY DỰNG 1 PHƯƠNG PHÁP DỰ BÁO DOANH THU DỰA TRÊN HỒI QUY ĐA PHƯƠNG.

Problem: You run an ice cream stand. After 10 days of sales, you discover that you either make a lot of money or nearly go broke. As you analyze sales, you feel that temperature and rain might be two important
determining factors. On rainy or cool days, fewer people buy ice cream. As shown in Fig. 399, you set up this table showing sales, temperature, and whether or not it rained.
You want to determine the relationship between sales, temperature, and rainfall.

Vấn đề: Bạn đang bán cà rem. Sau 10 ngày, bạn khám ra rằng bạn có thể kiếm được rất nhiều tiền và cũng có thể phá sản. Khi phân tích doanh thu, bạn có cảm giác rằng nhiệt độ và mưa là 2 yếu tố quan trọng ảnh hưởng doanh thu cà rem. Vào ngày mưa hoặc trời mát lạnh, ít người ăn cà rem. Như hình 399, bạn thiết lập 1 bảng tính cho thấy doanh thu, nhiệt độ và trời có mưa hay không mưa.
Bạn muốn tìm ra , mối tương quan giữa doanh thu cà rem với nhiệt độ, và trời mưa.

Fig. 399

Strategy: You need to do a multiple regression. After a multiple regression, you will have a formula that predicts sales like this: Y = m1x1 + m2x2 + b
Sales = Temperature x M1 + Rain x M2 + b
The LINEST function can return the values M1, M2, and b that best describe your sales model.
1) LINEST is going to return three values. Select a range of three cells that are side by side, as shown in Fig. 400. The first argument is the range of known sales figures. The second argument is the range
of temperatures and rainfall.
2) Hit Ctrl+Shift+Enter to calculate the array formula. As shown in Fig. 401, enter a prediction formula in column D to see how well the formula describes sales.

Giải pháp: Bạn cần 1 hàm hồi quy đa phương. Sau khi hồi quy bạn sẽ có 1 công thức dự báo doanh thu đại khái như: y = a1x1 +a2x2 + b
Doanh thu = Nhiệt độ x a1 + mưa x a2 +b
Hàm LINEST có thể tính cho bạn các hệ số a1, a2 và tham số cộng b khá sát với doanh thu thực của bạn.
1. NINEST sẽ trả về 3 kết quả. CHọn 1 vùng gồp 3 ô liên tiếp cùng 1 dòng như hình 400. Tham số thứ nhất là vùng chứa doanh thu. Tham số thứ 2 là vùng chứa 2 dữ liệu nhiệt độ và mưa.
2. Nhấn Ctrl Shift Enter để tính công thức mảng. Như hình 401, gõ 1 công thức tính dự báo doanh thu theo các giá trị vừa tìm được để xem sự dự báo của hàm hồi quy

Fig. 400

Fig. 401

The results are so-so. The prediction in D6 is right on the mark. The predictions in D11 and D12 are off by $20 each – an error of 10 percent.
3) The LINEST function can return additional statistics that will tell you how well the results match reality. To get the statistics, add a fourth argument of TRUE. Enter the function in a five-row range, as shown in Fig. 402.

Kết quả chỉ là tương đối vậy vậy. Con số dự báo ở ô D6 cùng giá trị với doanh thu thực. COn số dự báo ở ô D11 và D12 chênh lệch tới 20$, 1 sai lệch 10%.
3. Hàm LINEST còn có thể tính ra những thông số thống kê cho biết kết quả đúng đến mức nào. Gõ công thức cho 1 vùng 5 dòng 3 cột như hình 402.

Fig. 402

4) As shown in Fig. 403, after hitting Ctrl+Enter, you get the results. Personally, I only somewhat paid attention in statistics class. One of the key indicators that I look at is the R-Squared value. This ranges from 0 to 1, where 1 is a perfect match and 0 is a horrible match. The 0.88 value here confirms that the rediction model is pretty good, but not perfect.

4. Hình 403 cho thấy kết quả, sau khi nhấn Ctrl Enter. Cá nhân tôi quan tâm đến các thông số thống kê. Một trong các thông số cơ bản là độ chặt chẽ của hàm hồi quy R2. Con số này nhận các giá trị từ 0 đến 1, với 1 là sự hồi quy hoàn hảo vào 0 là chả đâu vào đâu. Con số 0,88 cho thấy hàm hồi quy là khá tốt, nhưng không hoàn hảo.

Fig. 403
Additional Details: Regression models try to force actual results into a straight-line formula. The fact is that life may not fit in a straight-line formula. Since I created the spreadsheet, I know that the actual data in the ice cream model uses a formula of (Temperature – 50) x $2 if raining and (Temperature – 50) x $6 if not raining. In this example, Sally was correct that ice cream sales are dependent on rain and temperature, but even a powerful regression engine could not predict the absolutely correct formula.
Summary: The LINEST function will automate the process of performing a least-squares method to fit a line to a series of actual sales using a multifactor regression.

Nói thêm: Hàm hồi quy tuyến tính cố gài các giá trị thực vào 1 hàm số của 1 đường thẳng. Sự thực là trên đời chả có cái gì tuyệt đối thẳng. Khi thiết kế bảng tính này, tôi đã dự trù rằng doanh thu bán cà rem của tôi sẽ là (Nhiệt độ - 50) x 2$ nếu trời mưa và (Nhiệt độ - 50) x 6$ nếu không mưa. Trong thí dụ này, Sally đã đúng khi cho rằng doanh thu bán cà rem lệ thuộc vào nhiệt độ và việc mưa hay tạnh. Nhưng 1 hàm công cụ mạnh để tính hồi quy cũng chẳng thể tính chính xác 100%.

Tóm tắt: Hàm LINEST sẽ tự tính bằng phương pháp bình phương bé nhất để gán 1 đường thẳng cho 1 dãy số liệu với 1 hàm hồi quy đa phương.