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.

 

[ptm0412]

USE F9 IN FORMULA BAR TO TEST A FORMULA
SỬ DỤNG PHÍM F9 ĐỀ KIỂM TRA CÔNG THỨC.

Problem: You have a complex formula that does not appear to be providing the correct result. As shown in Fig. 404, the formula has multiple terms and you are not sure which part is not working correctly.
Strategy: Select the cell, press F2 to put the cell in edit mode, as shown in Fig. 405.

Vấn đề: Bạn có 1 công thức phức tạp và không cho kết quả đúng như mong muốn. Như hình 404, công thức có nhiều thành phần và bạn không biết chắc phần nào bị sai.
Giải pháp: Chọn ô đó, nhấn F2 để cho con trỏ vào trong thanh công thức và đặt ô này vào tình trạng edit.

Fig. 404

Fig. 405

In edit mode, the various cells are color-keyed. You can selectively calculate just a portion of the formula. As shown in Fig. 406, using the mouse, highlight a portion of the formula. Hit the F9 key. As shown in Fig. 407, that portion of the formula will be replaced with the current result of the formula.

Trong tình trạng edit, mọi địa chỉ ô đều được tô màu. Bạn có thể tính 1 cách có chọn lựa1 phần của công thức. Như hình 406, dùng chuột bôi đen 1 phần củacông thức rồi nhấn F9. Hình 407 cho thấy kết quả: phần bị bôi đen của công thức bị thay thế bởi giá trị tính được của phần công thức đó.

Fig. 406

Fig. 407

Additional information: If you hit F9 without selecting anything, it will calculate the entire formula and replace it in the result, as shown in Fig. 408.
Nói thêm: Nếu nhấn F9 mà không bôi chọn phần nào của công thức, Excel sẽ tính giá trị toàn bộ công thức như hình 408.

Fig. 408

Tip Note: Remember to press the Esc key to change back to the original formula after using this technique.
Ghi chú: Nhớ nhấn Escape để trả công thức ô lại như ban đầu


Summary: Placing a cell containing a formula in edit mode, selecting part of the formula, and hitting the F9 key will verify that the selected part of the formula is correct.
Tóm tắt: Đặt 1ô chứa công thức vào tình trạng Edit, chọn từng phần của công thức và nhấn F9, bạn sẽ thấy kết quả của phần công thức đó nhằm xác định chỗ tính toán sai của công thức.

 

[ptm0412]

QUICK CALCULATOR
TÍNH TOÁN NHANH
Problem: You need to find a quick answer to a mathematical problem and you don’t have a calculator.
Strategy: Follow these steps:
1) Go to a blank cell.
2) Type an Equal sign.
3) Enter a calculation, as shown in Fig. 409.
4) Hit F9 key. Note the result, as shown in Fig. 410.
5) Hit the Esc key to clear the cell.

Summary: Excel can provide a quick calculator in a blank cell by typing the Equal sign, typing in the problem, and then hitting F9.
Vấn đề: Bạn cần tính nhanh 1 bài tính nhưng lại không có máy tính tay.
Giải pháp: Làm theo các bước sau:
- Vài 1 ô trống
- Gõ dấu bằng
- Gõ 1 bài tính toán
- Nhấn F9. Xem kết quả và ghi nhớ nó.
- Nhấn Escape để xoá ô đó.
Tóm tắt: Dùng Excel, công thức và F9 để tính nhanh 1 bài toán số.

Fig. 409

Fig. 410

 

[ptm0412]

WHEN ENTERING A FORMULA, YOU GET THE FORMULA INSTEAD OF THE RESULT
KHI GÕ CÔNG THỨCVÀO 1 Ô, BẠN NHẬN ĐƯỢC CHÍNH CÁI CÔNG THỨC ĐÓ, CHỨ KHÔNG PHẢI KẾT QUẢ.

Problem: When entering a formula, you get the formula instead of the result, as shown in Fig. 411.
Vấn đề:Sau khi gõ 1 công thức vào ô, bạn chỉ nhận được cái công thức chứ không phải kết quả công thức

Fig. 411

Strategy: There are three possibilities.
Các biện pháp: Có thể có 3 khả năng

Possibility 1: As shown in Fig. 412, you forgot to start the formula with the Equal sign.

Fig. 412
1) Select the cell, hit F2 to edit the cell, as shown in Fig. 413.
2) Hit home to move cursor to start of formula, as shown in Fig. 414.
3) Hit Delete key to delete the leading apostrophe. Type the = sign, as shown in Fig. 415.
4) Hit Enter to enter the formula, as shown in Fig. 416.
Khả năng 1: Như hình 412, bạn đã quên đánh dấu bằng trước công thức.
- Chọn ô này, nhấn F2 vào edit mode như hình 413.
- Nhấn phím Home để về đầu công thức.
- Nhấn Delete để xoá dấu nháy ở đầu công thức. Đánh dấu bằng.
- Nhấn Enter để xác nhận công thức.

Fig. 413 Fig. 414

Fig. 415 Fig. 416

Gotcha: Typically, in a cell with general formatting, Excel will rightalign numbers and left-align text. Because you forgot to hit the Equal sign, Excel thinks you have a text cell and even after converting to a
valid formula, the result might be left-aligned. Hit the Align Right icon in the Formatting toolbar, as shown in Fig. 417.

Ghi chú: Thông thường, với định dạng General, Excel sẽ canh lề trái 1 chuỗi text và lề phải 1giá trị số. Và vì bạn quên đánh dấu bằng, Excel coi những gì bạn nhập vào là text và nó tự động canh trái, ngay cả khi bạn sửa công thức lại rồi, nó cũng canh trái. Bạn phải nhấn nút canh phải trở lại.

Fig. 417

 

[ptm0412]

WHEN ENTERING A FORMULA, YOU GET THE FORMULA INSTEAD OF THE RESULT (Continue)
KHI GÕ CÔNG THỨCVÀO 1 Ô, BẠN NHẬN ĐƯỢC CHÍNH CÁI CÔNG THỨC ĐÓ, CHỨ KHÔNG PHẢI KẾT QUẢ. (Tiếp theo)
Possibility 2: The cell might have been assigned a numeric format of “@”, which is the code for a text cell. The maddening part is that this format can get set even without you knowing it. A column can inherit a text
format if you import a text file and use the text setting for the import.
1) Here’s how to fix this. Select the cell. Hit Ctrl+1 to show the Format Cells dialog. Confirm that the cell has a Text format assigned, as shown in Fig. 418.
Khả năng thứ 2: Ô này có thể đã bị định dạng “@” vốn là định dạng cho text. Cái khó chịu của loại định dạng này là nó được gán cho ô mà bạn không hay biết. 1 cột sẽ thừa hưởng định dạng kiểu này sau khi bạn lấy dữ liệu từ 1 file text.
1. Đây là cách để gỡ bỏ: Chọn ô này. Nhấn Ctrl +1 để hiện hộp thoại Format cell. Xácđịnh rằng ô có đang bị định dạng text hay không như hình 418.

Fig. 418

2) As shown in Fig. 419, change to a numeric format.
3) This does not fix the formula! You can now edit the cell using the F2 key. As shown in Fig. 420, note that Excel added an apostrophe before the formula.
4) Hit the Home key to move to the beginning of the formula and the Delete key to delete the apostrophe. Hit Enter to accept the formula, as shown in Fig. 421.
5) The cell alignment is left-aligned because you entered a value while the cell was formatted as Text. Use the Right-Align icon on the Formatting toolbar to correct this.
2. Đổi định dạng lại kiểu số.
3&4. Bước trên vẫn chưa đổi được công thức. Nếu trên công thức bạn thấy dấu nháy đơn ở đầu, lại phải xoá bỏ nó.
5. Định dạng canh lề của ô vẫn còn là canh trái, vì bạn đã nhập dữ liệu vào ô khi ô đang định dạng text. Hãy sửa lại thành canh phải.

Fig. 419

Fig. 420

Fig. 421

Possibility 3: The third possibility, although the least likely, is that you are in Show Formulas mode, as shown in Fig. 422. In this mode, all of the cells that have formulas would be showing their formulas.

Fig. 422

Hit CTRL+~ to toggle in and out of Show Formulas mode.
Khả năng thứ 3: Hiếm khi xảy ra, đó là bạn đang để Excel trong chế độ xem công thức như hình 422. Trong chế độ này, mọi ô chứa công thức đều bị để nguyên là công thức. Hãy nhấn Crtl+~ đển ra khỏi hoặc vào lại chế độ này.
Summary: The three possible options to fix a formula that shows when you actually want the results to show in a cell are: (1) check to make sure you started the formula with an Equal (=) sign, (2) make sure the cell is formatted for numeric and, lastly, (3) make sure the worksheet is not in Show Formula mode.
Tóm tắt: Có 3 cách sửa lỗi chỉ thấy công thức trong ô mà không thấy kết quả của công thức.

 

[ptm0412]

CALCULATE A PERCENTAGE OF TOTAL
TÍNH TỶ LỆ PHẦN TRĂM TRÊN TỔNG SỐ.

Problem: Your spreadsheet lists a number of customers with revenue, as shown in Fig. 423. There is a total
at the bottom. You want to express each customer as a percentage of the total.
Vấn đề: Bảng tính của bạn có 1 danh sách các khách hàng với doanh số thực hiện cho mỗi khách hàng. Có 1 dòng tổng ở dưới. Bạn muốn thể hiện tỷ lệ % doanh số của mỗi khách hàng trên tổng số.

Fig. 423

Strategy: Follow these steps:
1) Select a cell next to the first revenue cell.
2) Type an Equal sign. Hit the Left Arrow.
3) Type the Forward Slash (/) sign. Hit the Left Arrow. Type the End key. Hit the Down Arrow. Your cell pointer should now be on the total cell. Type the F4 key. The formula bar should now show B2/ $B$17, as shown in Fig. 424.
4) Hit Ctrl+Enter to enter the formula and stay in the current cell. Format the calculation as a percentage using the % icon, as shown in Fig. 425.
5) If you wish to use a format of 9.2 percent instead of 9 percent, then choose the Increase Decimal button, as shown in Fig. 426.
6) In cell C2, double-click the Fill handle to copy the formula down to the other rows.
7) Add a heading for “% of Total”.

Làm theo các bước sau:
- Chọn ô kế bên ô doanh thu đầu tiên.
- Đánh 1 dấu bằng, nhấn phím mũi tên trái để chọn ô bên trái gán vào công thức.
- Đánh 1 dấu xuỵêc (“/”, dấu chia của Excel). Nhấn phím mũi tên trái, nhấn tiếp phím End. Tiếp theo nhân mũi tên xuống. Ô đang đánh dấu chọn là ô có số doanh thu tổng. Nhấn F4 1 phát. Thanh công thức hiện giờ là B2/$B$17 như hình 424.
- Nhấn Ctrl+Enter để xác lập công thức và ở nguyên vị trí ô này. Định dạng % ô này bằng cách nhấn nút % trên thanh công cụ. (hình 425)
- Nếu bạn muốn 1 tỷ lệ 9,2% thay vì 9%, nhấn nút Increase Decimal như hình 426.
- Nhấn đôi cái fill handle của ô C2 để fill công thức xuống dưới.
- Thêm tiêu đề cột.

Fig. 424

Fig. 425

Fig. 426

Additional Details: The key element of this procedure is hitting the F4 key to add dollar signs to the reference for the total row. As you copy the formula from C2 to C16, the formula is always going to compare the revenue in the current row to the total revenue in row 17, as shown in Fig. 427.
Nói thêm: Trong quy trình ở trên có 1 lần nhấn F4, đó là cách chèn các dấu $ vào tham chiếu tổng. Khi sao chép công thức từ C2 đến C16, Công thức luôn tham chiếu để so sánh doanh thu dòng hiện tại với tổng doanh thu ở dòng 17.

Fig. 427.

Summary: Creating a Percentage of Total is a common task in Excel. Being able to quickly enter an initial formula that can be copied to all cells is a good technique to have in your skill set.
Tóm tắt: Tạo 1 cột dữ liệu về phần trăm trên tổng số là thường xuyên trong Excel. Làm thế nào để tạo nhanh công thức 1 ô đầu tiên, và có thể sao chép nhanh xuống các ô còn lại, là 1 kỹ năng cần nắm vững và làm thuần thục.

 

[ptm0412]

CALCULATE A RUNNING PERCENTAGE OF TOTAL
TÍNH TỶ LỆ PHẦN TRĂM LUỸ KẾ TRÊN TỔNG SỐ.

Problem: You have a report of revenue by customer, sorted in descending order, as shown in Fig. 428. Management consultants will often argue that you should concentrate your best team on the 20 percent of the customers who provide 80 percent of your revenue. How can you calculate a cumulative running percentage of the total?

Vấn đề: Bạn có 1 báo cáo doanh thu của khách hàng, sắp thứ tự giảm dần theo doanh số như hình 428. Ông cố vấn giám đốc muốn bạn phải có danh sách nhóm 20% khách hàng chiếm 80% doanh thu. Làm thế nào để tính 1 tỷ lệ % luỹ kế trên tổng doanh thu?

Fig. 428

Strategy: I hate solutions that require two different formulas, but this is one of them. You will need one formula for cell C2 and a different formula for cells C3 and below.
1) In cell C2, enter a formula of =B2/$B$18. Format the result as a percentage with one decimal place, as shown in Fig. 429.

Giải pháp: Tôi thường ghét những giải pháp yêu cầu tới 2 công thức, nhưng đây lại là 1 trường hợp như vậy. Bạn sẽ cần 1 công thức cho ô C2 và 1 công thức khác cho các ô còn lại.
1. Ở ô C2, gõ công thức =B2/$B$18. Định dạng kiểu % với 1 số thập phân như hình 429.

Fig. 429

2) Copy C2 to just the next cell, as shown in Fig. 430. If you want, you can drag the Fill handle down one cell to copy the formula. Or, use Ctrl+C and then Ctrl+V.
3) Hit F2 to edit the cell, as shown in Fig. 431.
4) As shown in Fig. 432, type a Plus sign and touch cell C2. Hit Ctrl Enter.
5) Double-click the Fill handle in C3 to copy this formula down to all of the other cells.
2. Copy công thức C2 xuống B3 như hình 430, bạn có thể kéo cái Fill handle xuống 1 ô, hoặc dùng Ctrl+C và Ctrl+V.
3. Nhấn F2 để sửa công thức (hình 431).
4. Như hình 432, gõ phím dấu + và chọn ô C2. nhấnCtrl Enter.
5. Double click cái Hill handle 1 phát.

Fig. 430

Fig. 431

Fig. 432

Note that you do not want this formula to be added to your total row. As shown in Fig. 433, the dataset was purposely set up with the total row and the data separated by a blank row in order to prevent this formula
from copying to the total row.
Chú ý rằng bạn không muốn công thức này tự động Fill xuống cả dòng tổng. Như hình 433, bạn phải bố trí vùng dữ liệu và dòng tổng các nhau 1 dòng trống để khi fill tự động, công thức chạy đến đó sẽ ngưng.

Fig. 433

Additional Information: If you absolutely want to produce this total with a single formula, you could use this formula in C2 and copy it down, as shown in Fig. 434: =SUM(B2:B$2)/B$18. This works because the range B2:B$2 is an interesting reference. It says to add up everything from the current row to the top row. This formula seems a bit less intuitive, so you might prefer the method shown earlier.

Nói thêm: Nếu bạn muốn dùng chỉ 1 công thức cho mọi ô cột C, hãy dùng công thức sau ở C2 sau đó Fill xuống: =SUM(B2:B$2)/B$18. Công thức này xài tốt vì vùng tham chiếu B2:B$2 là 1 tham chiếu lý thú: Nó cho biết sẽ cộng mọi thứ từ dòng hiện tại đến dòng đầu của cột B. Công thức này kém trực quan và không dễ hiểu như công thức trên, và bạn có thể thích công thức trên hơn.

Fig. 434

Summary: The formula for a running percentage of the total is another common analysis tool. This technique offers two different options for calculating the formula.
Tóm tắt: Công thức tính 1 tỷ lệ % luỹ kế là 1 kỹ năng khác mà bạn nên biết. Kỹ năng này cho bạn 2 cách để tính ra kết quả.

 

[ptm0412]

USE ^ SIGN FOR EXPONENT
DÙNG DẤU ^ ĐỂ TÍNH LUỸ THỪA

Problem: You have a room that is 10 feet x 10 feet x 10 feet. What is the volume of the cube?
Vấn đề: Bạn có 1 căn phòng kích thước 10 ft x 10 ft x 10 ft. Thể tích căn phòng là bao nhiêu?

Strategy: The formula for volume is width x length x height. In this case, it is 10 x 10 x 10, or ten to the third power. In Excel, the carat symbol (also known as “the little hat”, or “the symbol when you press Shift
6”) is used to calculate exponents. In cell B2, enter 10; in cell C2, enter the formula =B2^3. The result will be 1000 cubic feet of volume in the room, as shown in Fig. 435.
Cách tính: Công thức tính thể tích hình hộp là dài x rộng x cao. Trong trướng hợp này, đó là 10 x 10 x 10 hay 10 luỹ thừa 3. Trong Excel, ký hiệu ^ (dấu mũ, dấu có được bằng cách nhấn Shift + 6) được dùng để tính luỹ thừa. Ở ô B2 gõ 10, ở ô C2 gõ =B2^3. Kết quả là 1.000 feet khối cho thể tích căn phòng.

Fig. 435

Summary: Use a carat to calculate exponents.
Tóm tắt: Dùng ký hiệu dấu mũ để tính luỹ thừa.

 

[ptm0412]

RAISE A NUMBER TO A FRACTION TO FIND THE SQUARE OR THIRD ROOT
LUỸ THỪA 1 SỐ VỚI BẬC LÀ 1 PHÂN SỐ ĐỂ TÍNH CĂN BẬC 2 HAY BẬC 3.

Problem: Excel offers a SQRT function to find the square root of a number. What if you need to figure out the third root or the fourth root?
Strategy: You can raise a number to a fraction to find a root. To find the square root of a number, you can raise the number to (1/2). To find the cube root of a number, you can raise the number to (1/3). To find the eighth root of a number, you can raise the number to (1/8). Here are several examples.
If you need to find the square root, you can use the SQRT function, as shown in Fig. 436.
As shown in Fig. 437, you can also raise the number to the one-half (1/2) power.
Or, you can raise the number to 0.5, as shown in Fig. 438.
To find the cube root of a number, you can raise the number to the onethird (1/3) power, as shown in Fig. 439.
Vấn đề: Excel cung cấp cho bạn 1 hàm SQRT để tìm căn bậc 2 của 1 số. Vậy làm thế nào để tính căn số bậc 3 hay bậc 4?
Giải pháp: Bạn có thể tính luỹ thừa 1 số với bậc là 1 phân số để tính căn số. Để tính căn bậc 2, hãy tính luỹ thừa bậc ½. Để tính căn bậc 3, hãy nghĩ đến tính luỹ thừa 1/3. Tương tự bạn tính luỹ thừa 1/8 thay cho tính căn số bậc 8. Sau đây là vài thí dụ:
Nếu bạn muốn tính căn bậc 2, dùng hàm SQRT như hình 436.
Bạn cũng có thể tính luỹ thừa ½ như hình 437
Hoặc bạn tính luỹ thừa 0,5 như hình 438.
Để tính căn số bậc 3 của 1 số, hãy lấy số đó tính luỹ thừa 1/3 như hình 439.

​

Fig. 436---------------- Fig. 437​

​

Fig. 438-------------------- Fig. 439​

To find the fourth root of a number, raise the number to either one-forth (1/4) or 0.25 power, as shown in Fig. 440 and Fig. 441, respectively.
To find the seventeenth root of a number, raise it to the one-seventeenth (1/17) power, as shown in Fig. 442.
Để tính căn số bậc 4, tính luỹ thừa số đó với bậc 1/4 hoặc 0,25 như hình 440 và 441.
Hoặc tính căn số bậc 17 bằng cách tính luỹ thừa bậc 1/17 như hình 442

Fig. 440

Fig. 441

Fig. 442

Summary: Although Excel only offers a function for a square root, you can use the technique of raising to a fractional power in order to determine any root of a number.
Tóm tắt: Mặc dù Excel chỉ cung cấp 1 hàm SQRT tính căn bậc 2, bạn có thể dùng phép tính luỹ thừa bậc là 1 phân số để tính căn số có mọi bậc lớn hơn 2.

 

[ptm0412]

CALCULATE A GROWTH RATE
TÍNH TỶ LỆ (TỐC ĐỘ) PHÁT TRIỂN.

Problem: You work for a fast growing company. In the first year, they had $970,000.00 in sales. In the fifth year, they had $6,175,000.00 in sales. What was the compounded annual growth rate?
Strategy: Sales in the fifth year are 6175/970 higher than the first year. The formula for growth is (year 5/year 1) – 100 percent. So, as shown in Fig. 443, the growth rate is 537 percent.
However, a compounded growth rate is a number, x, that will calculate out like this: Year 1 * (100% + x) *(100% + x) *(100% + x) * (100% + x)= Year 5
This is the same as:
Year 1 * (100% + x)^4 = Year 5
So, in order to calculate x, you have to be able to find the fourth root of (Year5/Year1). The formula to find the fourth root is to raise the number to the (1/4) power. Thus, as shown in Fig. 444, the formula to calculate the compounded growth rate is:
(Year 5/Year 1)^(1/4)–100% = x

Vấn đề: Bạn đang làm việc tại 1 công ty phát triển nhanh. Trong năm đầu, doanh thu là 970.000,00 USD. Vào năm thứ 5, doanh thu đã đạt 6.175.000,00 USD; vậy tỷ lệ tăng trưởng (kép) mỗi năm là bao nhiêu?
Giải quyết: Doanh thu năm thứ 5 lớn hơn doanh thu năm thứ nhất 6175/970 lần. Tỷ lệ tăng trưởng là (DT năm 5/DT năm 1) - 100%. Như hình 443, tỷ lệ là 537%.
Dù vậy, 1 tỷ lệ tăng trưởng hàng năm (x) là 1 số được tính bằng công thức sau:
DT1*(100%+x)*(100%+x)*(100%+x)*(100%+x) = DT 5
Tương đương với: DT1*(100% + x)^4 = DT5
Để tính x, bạn phải tính căn số bậc 4 của DT5/DT1. Công thức để tính căn số bậc 4 là tính luỹ thừa ¼. Trên hình 444, bạn thấy công thức tính là: =(B6/B2)^(1/4)-100%

Fig. 443

Fig. 444

The compounded growth rate is 59 percent.
Kết quả là 59%

Summary: Compounded growth rates are common calculations that require you to raise a number to a fractional power.
Tỷ lệ tăng trưởng (kép) đòi hỏi bạn phải tính luỹ thừa với số mũ là 1 phân số.

 

[ptm0412]

FIND THE AREA OF A CIRCLE
Tính diện tích hình tròn

Problem: You need to order pizza for the department staff meeting. The pizza joint has two deals. You can buy three Medium (12”) pizzas for $15 or two Large (16”) pizzas for $16. Which is the better deal?
Vấn đề: Bạn cần đặt mua bánh pizza cho cuộc họp mặt của phòng. Pizza thì có 2 phương án: loại vừa vừa 12 inches mua 3 cái 15$, và loại lớn 16 inches mua 2 cái 16$. Vậy mua loại nào lợi hơn?

Strategy: You will have to figure out the area of a 12” pizza vs. the area of a 16” pizza. The formula for the area of a circle is Pi times the square of the radius.
The radius of a pizza is one-half the diameter. If you enter the diameter of the pizza in B2, the radius is =B2/2.
Pi is a Greek letter that represents 3.141592654. Excel offers the PI function to return this number, as shown in Fig. 445. It is a lot easier to remember =PI() instead of 3.141592654.
1) Set up a worksheet. In cell B2, enter the diameter of the pizza.
2) In cell C2, calculate the radius as =B2/2, as shown in Fig. 446.

Bạn phải tính diện tích hình tròn của 2 loại bánh pizza. Công thức tính diện tích hình tròn là bình phương bán kính nhân Π. Bán kính là 1 nửa đường kính. Nếu bạn gõ đường kính vào B2, bán kính sẽ là B2/2. Π là 1 chữ cái Hy lạp có giá trị 3,1415926535. Excel cung cấp cho bạn hàm PI() để dùng thay cho số Pi như hình 445.
- mở 1 file Excel mới, trong ô B2 gõ đường kính cái pizza.
- ở ô C2, tính bán kính bằng B2/2 như hình 446

​

Fig. 445 ____________Fig. 446​

3) In cell D2, calculate the area of the pizza in square inches, using =PI()*C2^2, as shown in Fig. 447.
4) In column E, enter the quantity of pizzas.
5) Calculate the total square inches in column F using =E2*D2, as shown in Fig. 448.
6) Enter the cost for the special in column G. In column H, calculate the pennies per square inch of pizza using =G2/F2, as shown in Fig. 449.

- Ở ô D2 tính diện tích cái pizza với đơn vị tính là inch vuông: = PI*C2^2 như hình 447.
- Trong ô E2 gõ số lượng bánh định mua.
- Tính tổng diện tích của tổng số bánh: =È2*D2 như hình 448
- Gõ số tiền mua bánh vào ô G2, tính giá tiền cho 1 inch vuông vào ô H2: = G2/F2, cho kết quả hình 449

_______________________Fig. 447

_____________________Fig. 448

__________________________________Fig. 449
Result: From a purely mathematical point of view, the special with two large pizzas is a slightly better deal, pricing the pizza at 3.98 cents per square inch.
Additional Information: My seventh grade math teacher, Mr. Irwin, would like me to mention, for the sake of completeness, that the circumference of the pizza is Pi times the diameter. That would be =PI()*B2, as shown in Fig. 450.

Kết quả: Theo cách tính toán giá trung bình cho 1 inch vuông, phương án mua 2 bánh lớn có vẻ lợi hơn mua 3 bánh nhỏ.
Nói thêm: Để tính chu vi hình tròn, ta lấy đường kính nhân Pi như hình 450.

Fig. 450