PART 2:CALCULATING WITH EXCEL CREATE EASIER-TO-UNDERSTAND FORMULAS WITH NAMED RANGES TẠO NHỮNG CÔNG THỨC DỄ HIỂU VỚI TÊN RANGE Problem: As shown in Fig. 206, your worksheet contains several different for­mulas. It would be easier to understand the results if each component of every formula were named for what it repre­sented and not just for the cell it came from. Vấn đề: Như hình 206, bảng tính của bạn bao gồm rất nhiều công thức. Sẽ dễ hiểu hơn cái kết quả của các công thức này nếu mỗi thành phần của công thức được đặt tên theo nội dung của nó.
Fig. 206
Strategy: Use named ranges to make formulas easier to understand. Give cell B3 a name of Revenue. Select cell B3. In the Name box (the area to the left of the formula bar), type Revenue and press Enter, as shown in Fig. 207. Giải pháp: Sử dụng các range được đặt tên làm cho công thức dễ hiểu hơn. Đặt tên cho cell B3 là Revenue (DoanhThu): Chọn cel B3, Trong hộp tên, (là ô ngoài cùng phía trái của thanh công thức), gõ Revenue và Enter như hình 207
Fig. 207
Give cell B4 a name of COGS. Select cell B4. Click in the name box, type COGS and hit Enter. Đặt tên cho cell B4 là COGS (Giá vốn COGS = Cost Of Goods Saled): Chọn cell B4, trong hộp Name Box, gõ COGS, và Enter. Clear the formula in B6. Re-enter the formula and use the mouse to select the cells. Type an Equal sign. Using the mouse, touch B3. Type a Minus sign. Using the mouse, touch B4. This will enter the formula as =Revenue–COGS, as shown in Fig. 208. This is easier to understand than a typical formula. Xoá công thức trong cell B6, Nhập lại công thức: Gõ dấu bằng (=), Dùng chuột chọn ô B3, gõ dấu trừ, dùng chuột chọn ô B4. Công thức sẽ là: = Renenue – COGS như hình 208. Sẽ dễ hiểu hơn là công thức thông thường.
Fig. 208
Gotcha: You need a lot of foresight to use this technique. In order to have this work automatically, you are supposed to be smart enough to create the range names before you enter the formula. However, most people get the formula first and then decide to make the worksheet eas­ier to understand. 1) If you want to assign names after the formulas are created, use In­sert – Name – Apply to apply names to existing formulas, as shown in Fig. 209. Ghi chú: Bạn phải có tầm nhìn rộng khi sử dụng kỹ thuật này. Nhằm mục đích cho các tên này hoạt động tự động, bạn phải đủ ý thức để tạo những tên trước khi viết công thức. Dù vậy, người ta thường tạo công thức trước, sau đó mới tính đến chuyện làm cho bảng tính trở nên dễ hiểu. Nếu bạn muốn áp các tên mới đặt vào các cộng thức có sẵn, hãy dùng Menu Insert – Name – Apply để áp các tên vào công thức có sẵn như hình 209
Fig. 209
2) As shown in Fig. 210, select all of the names that you want to apply. Xem hình 210, chọn mọi cái tên mà bạn muốn áp vào công thức.
Fig. 210
Result: A formula like =B6–B11 will be updated to =GrossProfit–Expenses, as shown in Fig. 211.
Kết quả: 1 công thức như là =B6-B11 sẽ được cập nhật thành =GrossProfit-Ex_Pense như hình 211 (Lãi gộp – Chi tiêu khác)
Fig. 211
Summary: To create plain language formulas, first assign a range name to each cell in your formula. Use the mouse when entering the formula. To assign range names to a formula after the fact, use Insert – Name – Apply. Tóm tắt: Muốn tạo các công thức biết nói, trước tiên đặt tên cho các cell trong các công thức. Dùng chuột chọn cell khi tạo công thức. Muốn cập nhật các tên mới đặt cho các công thức có sẵn, dùng Menu Insert – Name – Apply.
USE NAMED CONSTANTS TO STORE NUMBERS DÙNG TÊN HẰNG ĐỂ LƯU TRỮ SỐ Problem: You’ve seen how you can assign a name to a cell. It is also possible to assign a name to a constant. This could be useful if you have a number, such as a local sales tax rate, that changes once a year. Vấn đề: Bạn đã thấy đặt tên cho 1 cell. Cũng có thể đặt tên cho 1 hằng số. Sẽ hữu ích nếu bạn có 1 con số chẳng hạn như thuế suất thuế Doanh thu, chỉ thay đổi 1 lần mỗi năm.
Strategy: From the menu, use Insert – Name – Define. Type a name like SalesTax. In the Refers to box, type =0.065 and click Add, as shown in Fig. 212. Giải quyết: Mở Menu Insert – Name – Define. Gõ 1 cái tên chẳng hạn ThueSuat. Trong ô Refers to gõ =0.065 và click Add, như hình 212
Fig. 212
In this workbook, you can now use a formula such as =SalesTax*D2, as shown in Fig. 213. Trong bảng tính hình 213, bạn có thể thấy 1 công thức chẳng hạn như =SalesTax*D2.
Fig. 213
If the tax rate changes later use Insert – Name – Define to change the constant assigned to the name. Nếu sau này thuế suất thay đổi, vào lại Menu Insert – Name – Define đổi lại giá trị của tên đã đặt.
Summary: To name a constant in a workbook use Insert – Name – De­fine, type in the name of the constant, and then define the constant in the Refers to: box. Tóm tăt: Muốn đặt tên cho 1 giá trị số, vào Menu Insert – Name – Define, đặt tên trong ô tên và giá trị trong ô Refers to.
BUILD A FORMULA USING LABELS INSTEAD OF CELL ADDRESSES TẠO 1 CÔNG THỨC SỬ DỤNG TÊN CỘT THAY CHO ĐỊA CHỈ Ô. Problem: You hate using cell references such as B2 in formulas. Vấn đề: bạn ghét dùng địa chỉ ô như B2 trong công thức.
Strategy: Use natural language formulas. These formulas are fairly amazing. Excel has offered support for natural language formulas for many versions. With these formulas, you can use the headings in a worksheet to describe which cells you want to reference. By default, natural language formulas are turned off in later versions of Excel. To enable them, go to Tools – Options – Calculation and select Accept Labels in Formulas, as shown in Fig. 214. Giải pháp: Dùng công thức với ngôn ngữ đời thường. Các công thức này sẽ làm bạn sửng sốt. Excel cung cấp tính năng sử dụng công thức bằng ngôn ngữ tự nhiên trong nhiều phiên bản. Với các công thức này, bạn có thể dùng tiêu đề cột trong bảng tính để diễn tả ô mà bạn muốn tính toán. Mặc định chức năng này bị tắt trong các phiên bản sau này của Excel. Để sử dụng chúng, vào Menu Tools – Option – Calculation và chọn Accept Labels in Formulas, như hình 214.
Fig. 214 As shown in Fig. 215, you can now enter formulas such as these: =SUM(East) and =SUM(ABC) Trong hình 215, bạn thấy có thể dùng công thức như thế này: = Sum(East) và Sum (ABC)
Fig. 215
It is important to note that there are no named ranges in this work­sheet! Excel is simply looking at the labels in the first column and row of the spreadsheet. To refer to sales of ABC in the West, you would enter =West ABC, or =ABC West, as shown in Fig. 216. Điều quan trọng là chẳng có cái tên nào được đặt cả! Excel đơn giản chỉ đi tìm tiêu đề trong các dòng, cột đầu mà thôi. Để tính Doanh thu mặt hàng ABC trong khu vực West, bạn sẽ nhập công thức = ABC West hoặc =West ABC như hình 216.
Fig. 216
If you wanted to find the ratio of East ABC to West GHI, you would use this formula =ABC East/GHI West, as shown in Fig. 217. Nếu bạn muốn tính tỷ lệ giữa East ABC và West GHI, bạn sẽ dùng công thức =ABC East/GHI West, như hình 217
Fig. 217
Additional Information: Amazingly, these formulas can even handle labels with space in them. As shown in Fig. 218, the formula of =ABC Gross Profit will work. Nói thêm: Thật đặc biệt, các công thức này thậm chí làm việc với cả các tiêu đề có khoảng trắng ở giữa. Trong hình 218, côngthức = ABC Gross ProFit cũng chạy tốt.
Fig. 218
Also, if you’ve ignored all of my pleas to have headings take up only a single row of cells, and have a spreadsheet with “Gross” in E1 and “Profit” in E2, you can still use the formula =Gross Profit ABC, as shown in Fig. 219. Important: You must refer to the headings in the order that they appear from top to bottom. Còn nữa, nếu bạn coi thường các việc tôi khẩn cầu nên để tiêu đề trong các cell chỉ 1 dòng, mà đặt thí dụ Gross trong cell E1 và Profit trong cell E2, bạn vẫn có thể dùng công thức = ABC Gross ProFit như hình 219. Nhưng quan trọng là bạn phải dùng cái tiêu đề cột theo thứ tự từ dòng trên xuống dòng dưới.
Fig. 219
You can even figure out the Gross Profit on a Gross quantity, as shown in Fig. 220. Bạn cũng có thể tính Gross Profit trên dòng Gross như hình 220 (tham chiếu cả cột và dòng)
Fig. 220
Gotcha: If your data set is missing labels, Excel may have trouble calcu­lating formulas that are in cells that are not adjacent to your dataset. Nếu dữ liệu của bạn không có tiêu đề dòng, cột, Excel sẽ không tính được các công thức trong các cell không tìm thấy tên liên quan trong bảng dữ liệu Summary: Natural Language Formulas present an alternative to typi­cal formulas in Excel. They might be great for your boss’ boss who can’t quite get the hang of using cell references in Excel. Tóm tắt: Công thức bằng ngôn ngữ tự nhiên, là 1 cách thể hiện khác của Excel. Nó giúp cho chủ của sếp bạn đọc bảng tính mà chả cần biết các địa chỉ cell.
USE NATURAL LANGUAGE FORMULAS TO REFER TO THE CURRENT ROW SỬ DỤNG CÔNG THỨC VỚI NGÔN NGỮ TỰ NHIÊN ĐỂ TÍNH VỚI DÒNG HIỆN TẠI CỦA BẢNG TÍNH Problem: In the previous example, a natural language formula referred specifically to one cell by indicating a row name and a column name. It is also possible to have natural language formulas that refer to a specific column in the current row. Vấn đề: Trong thí dụ trước, công thức ngôn ngữ tự nhiên tham chiếu đến 1 ô bằng các tiêu đề dòng và tiêu đề cột. Loại ngôn ngữ này cũng có thể chỉ tới 1 cột trong dòng hiện tại.
Strategy: As shown in Fig. 221, you have a census of employees and their benefit selections. Như hình 221, bạn có 1 danh sách các nhân viên và lựa chọn thu nhập của họ.
Fig. 221
The company buys life insurance for anyone under the age of 65. The rate for life insurance is $4 per dependent. As shown in Fig. 222, you can write a natural language formula in E2: =IF(Age<65,Dependants*4,0). Công ty sẽ mua bảo hiểm nhân thọ cho những ai có tuổi dưới 65. Mức mua bảo hiểm là 4$ cho mỗi người phụ thuộc của nhân viên đó. Xem hình 222, bạn có thể dùng công thức =If(Age<65,Dependants*4,0)
Fig. 222
As you copy this formula down the column, Excel will properly calculate the formulas based on the age and dependents in the current row. Nếu bạn copy và fill xuống các ô dưới của cột đó, Excel sẽ tính toán công thức dựa trên tuổi và số người phụ thuộc trong mỗi dòng tính.
Summary: Again, natural language formulas allow non-technical peo­ple to write formulas in plain language. Tóm tắt: 1 lần nữa, công thức với ngôn ngữ tự nhiên cho phép người dùng yếu kỹ năng Excel có thể viết công thức bằng ngôn ngữ đời thường.
ASSIGN A FORMULA TO A NAME THIẾT LẬP CÁC CÔNG THỨC CHO NAME
Problem: You have thousands of identical formulas on 20 worksheets, as shown in Fig. 223. Every time that you want to change the formula, you have to edit all 20 sheets. Is there a way to make a formula be variableand change it in just one place?
Vấn đề: Bạn có hàng ngàn côngthức giống nhau trong 20 sheet như hình 223. Mỗi khi bạn thay đổi công thức, bạn phải sửa 20 sheet. Có cách nào tạo 1 công thức có thể tự điều chỉnh khi có thay đổi?
Strategy: Use a Name, but assign a formula to the name.
Dùng 1 tên, và tạo công thức cho tên đó.
Fig. 223
Think about what you do when you set up a named range. For instance,you could assign a name of MyData to cell D1, as shown in Fig. 224.However, when you look at this in the Define Name dialog (Insert –Name – Define…), you will see that MyData is really equal to a formula called =Sheet1!$D$3, as shown in Fig. 225. If you understand this, then it is easy to make the leap that any formula can be assigned a name. You could define a name called GlobalFormula. When you enter =GlobalFormula in the thousand cells on each of the 20 sheets, it will inherit the formula from GlobalFormula.
Bạn hãy nghĩ về việc sẽ làm khi tạo 1 cái tên. Thí dụ bạn đặt tên cho ô D1 là MyData. Khi mở Menu Insert – Name – Define… bạn sẽ thấy tên MyData sẽ gắn với công thức =Sheet1!$D$3, như hình 225 Nếu bạn hiểu, bạn sẽ thấy dễ dàng rằng mỗi công thức đều có thể dùng để đặt tên. Bạn có thể đặt 1 tên như GlobalFormula. Nếu bạn cùng công thức =GlobalFormula trong hàng ngàn ô trong 20 sheet, nó sẽ thừa hưởng công thức của chính GlobalFormula khi bạn đặt tên.
Fig. 224
Fig. 225
Gotcha: Do you remember the topic about R1C1 style addressing? If you were using R1C1 style addressing, then all of the formulas in column C would be identical, as shown in Fig. 226, and this would be an easy task. However, I realize that no one uses R1C1 style references, so you will have to build this formula the hard way. In A1 style references, each formula is different in each cell, as shown in Fig. 227.
Ghi chú: Bạn có nhớ bài về địa chỉ ô dạng R1C1? Nếu bạn dùng cách này, mọi công thức cột C sẽ giống hệt nhau như hình 226, và mọi chuyện sẽ dễ dàng. Dù vậy tôi nhận thấy chả ai dùng loại địa chỉ R1C1 và bạn sẽ phải làm cách khó hơn. Đối với dạng địa chỉ A1, công thức mỗi ô đều khác nhau như hình 227
Fig. 226
Fig. 227
If you want GlobalFormula to =COS(A2), you cannot just write that. You need to develop a formula that takes the COS function of the cell two cells to the left of the current cell. This is possible, but it requires a whole bunch of new functions that you might never have used before. First, look at the InDirect function. =INDIRECT(“A2”) will return the value that is in A2. When you try to use the results of an INDIRECT function in another calculation, it always helps to put the INDIRECT function inside of a SUM function, as shown below. =SUM(INDIRECT(“A2”)) So, if you wanted to take the COS of A2, you could use the following formula. =COS(SUM(INDIRECT(“A2”))) The next trick to figure out is how to return the text of A2 to refer to a cell. To do this, use the ADDRESS function. =ADDRESS(2,1) will return the text “A2” because A2 is in the 2nd row, fi rst column. =ADDRESS(52,26) would return “Z52” because this is the fifty-second row, twenty-sixth column. Is there a function that will return the row number of the cell containing the formula? Yes, the ROW function will return the row number of the cell that contains the formula, as shown in Fig. 228.
Nếu bạn muốn 1 công thức cho tên GlobalFormula là = COS(A2), bạn không thể viết như vậy. bạn cần phát triển công thức sao cho hàm COS tính cho 1 ô nằm phía trái ô hiện tại 2 ô. Chuyện này là có thể, nhưng nó đòi hỏi bạn phải dùng tới 1 hàm mà bạn chưa bao giờ dùng tới. Trước tiên hãy xem hàm InDirect. Công thức =INDIRECT(“A1”) sẽ trả về giá trị của ô A2. Nếu bạn muốn dùng kết quả của hàm này trong tính toán, sẽ có ích khi bạn nhét hàm InDirect vào trong hàm Sum như sau: = SUM(INDIRECT(“A2”) Rồi khi bạn muốn tính Cos của A2, bạn dùng công thức sau: =COS( SUM(INDIRECT(“A2”)). Kế tiếp là làm sao cho chuỗi trong ô A2 tham chiếu đến 1 ô. Để làm điều này, dùng hàm ADDRESS. =ADDRESS(2,1) sẽ cho giá trị A2 vì ô A2 nằm ở dòng 2, cột 1. =ADDRESS(52,26) sẽ cho giá trị “Z52” vì ô này nằm ở dòng 52, cột 26. Vậy có hàm nào cho số thứ tự dòng của 1 ô trong côngthức? Có, và đó là hàm ROW. Hàm ROW cho số thứ tự dòng của 1 ô như hình 228.
Fig. 228
Similarly, =COLUMN() will return the column number of the cell containing the formula, as shown in Fig. 229.
Tương tự, =COLUMN() sẽ trả về số thứ tự cột của ô hiện tại.
Fig. 229
So, you could write a formula that returns the name of the cell, like this one shown in Fig. 230:
Vậy bạn có thể viết 1 công thức trả về tên của 1 ô, như hình 230,
Fig. 230
To return the address of a cell two columns to the left of the current cell, add a “–2” after the COLUMN() function, as shown in Fig. 231.
Để có 1 ô ở phía trái ô hiện tại 2 cột, hãy trừ 2 vào tham số cột như hình 231.
ASSIGN A FORMULA TO A NAME THIẾT LẬP CÁC CÔNG THỨC CHO NAME (tiếp theo)
Therefore, the formula that you need to take the COS of the cell two cells to the left of the cell containing the formula is: =COS(SUM(INDIRECT(ADDRESS(ROW(),COLUMN()–2)))). The actual current formula is =COS(A2)/SIN(B2). This is the formula that you would use: =COS(SUM(INDIRECT(ADDRESS(ROW(),COLUMN()–2))))/ SIN(SUM(INDIRECT(ADDRESS(ROW(),COLUMN()–1)))) From the menu, choose Insert – Name – Define. As shown in Fig. 232, in the Define Name box, type a name, type the formula, and choose Add.
Vậy thì, công thức cần tính COS của 1 ô nằm bên trái ô đang tính 2 ô, là: =COS(SUM(INDIRECT(ADDRESS(ROW(),COLUMN()–2)))). Công thức cần tính là =COS(A2)/SIN(B2) và bạn có thể dùng công thức sau: =COS(SUM(INDIRECT(ADDRESS(ROW(),COLUMN()–2))))/ SIN(SUM(INDIRECT(ADDRESS(ROW(),COLUMN()–1)))) Mở Menu Insert – Name – Define. Trong hộp thoại Define Name, gõ công thức trên vào ô Refered to, chọn tên và nhấn nút Add.
Fig. 232
Result: A name is added to the Workbook Names. The name is assigned your formula, as shown in Fig. 233. Kết quả: 1 cái tên đã được thêm vào danh sách các tên của Workbook. Cái tên này được gán 1 công thức.
Fig. 233
Now, in any cell in the workbook, you can use the formula =GlobalFormula, as shown in Fig. 234.
Bây giờ ở bất kỳ nơi nào bạn gõ công thức = GlobalFormula, bạn sẽ có kết quả của công thức đã gán cho tên GlobalFormula
Fig. 234
Additional Details: Any time that you need to change the formula, simply redefine it in the Defi ne Name box and choose Add. Gotcha: The Refers to: box in the Define Name dialog is one of the most maddening things in all of Excel. If you want to edit a formula in that box, it is sometimes impossible. For example, if you want to change this 2 to a 1 (in order to refer to B instead of A), you would have to highlight the 2, as shown in Fig. 235, and type a 1.
Nói thêm: Mỗi khi muốn sửa công thức, bạn chỉ cần sửa công thức của name. Ghi chú: ô Referers to: trong hộp thoại đặt tên là 1 trong những thứ kỳ lạ của Excel. Nếu muốn sửa công thức trong đó, thí dụ sửa 2 thành 1 để cho công thức tham chiếu đến B thay vì A, bạn phải tô đen số 2 rồi gõ 1. (hình 235)
Fig. 235
If you attempt to use the Left or Right Arrow key to move through the formula, Excel will insert a cell address to the left or right of the current cell. Watch, I’ve placed the cursor after the word Indirect, as shown in Fig. 236.
Nếu bạn dùng phím mũi tên trái hoặc phải, Excel sẽ điền ngay địa chỉ của ô bên trái hoặc bên phải ô hiện hành vào đó. Nhìn xem, tôi đang để dấu nháy ngay sau chữ InDirect như hình 236.
Fig. 236
One press of the Left Arrow key will insert +Sheet1!$B$2 in the formula, as shown in Fig. 237.
Khi nhấn phím mũi tên trái, sẽ chèn +Sheet1!$B$2 vào công thức như hình 237.
Fig. 237
The main problem with this is that sometimes the formula is so long that you cannot see the end of the formula in the Refers to box. When you try to click near the end of the field and hit the Right Arrow, you end up adding references to the formula. The only solution that I have found is to click early in the formula and drag all of the way to the end. This will force Excel to scroll to the end of the formula, as shown in Fig. 238. Có 1 vấn đề nữa là đôi khi công thức quá dài và không thể thấy hết trong ô Refers to. Khi bạn cố click vào cuối ô và nhấn phím mũi tên phải, bạn sẽ kết thúc việc đặt tên. Giải pháp duy nhất tôi đã làm là click vào 1 chỗ khoảng giữa ô đó, dùng chuột kéo về phía cuối công thức, cho nó hiện ra. Như hình 238.
Fig. 238
You can then click and drag to isolate the portion of the formula that you need to change, as shown in Fig. 239. Bạn cũng có thể kéo đến phần của công thức mà bạn muốn sửa.
Fig. 239
Summary: Named Formulas can save the day, although they can be incredibly complex. Tóm tắt: Gán công thức cho tên ô, sẽ giúp tiết kiệm thời gian, dù cho nó phức tạp đến mấy.
TOTAL WITHOUT USING A FORMULA TÍNH TỔNG KHÔNG CẦN CÔNG THỨC
Problem: Your manager calls on the telephone and asks for the total sales of a particular product. You need to quickly find a total. Is there a faster way than entering a formula?
Vấn đề: Sếp bạn gọi điện và muốn biết tổng doanh số bán của 1 mặt hàng cụ thể. Bạn cần 1 cách tìm số tổng cực nhanh. Có cách nào nhanh hơn gõ công thức?
Strategy: The QuickSum indicator in the status bar will show the total of the highlighted cells. With your manager on the phone, highlight the numbers in question, as shown in Fig. 240.
Giải pháp: ô QuickSum ở thanh trạng thái sẽ hiện con số tổng của 1 khối ô được đánh dấu. Với ống nghe và sếp bên tai, tô chọn các con số được yêu cầu.
Fig. 240
Result: As shown in Fig. 241, the QuickSum indicator in the status bar at the bottom of the screen will show that the total of those three cells is 691.
Kết quả: Như hình 241, ô QuickSum ở thanh trạng thái dưới đáy màn hình hiện lên kết quả tổng của 3 ô chọn là 691.
Fig. 241
Additional Information: It is possible that you have turned off the status bar. If the status bar is not visible at the bottom of your screen after selecting a range of numeric cells, select View – Status Bar from the menu, as shown in Fig. 242.
Nói thêm: Bạn cũng có thể đã tắt cái thanh trạng thái này. Nếu bạn không thấy thanh trạng thái, hãy vào Menu – View – Status bar.
Fig. 242
Gotcha: If one of the values that you select is text, the status bar will show the total of just the numeric entries, as shown in Fig. 243.
Ghi chú: Nếu 1 trong các ô chọn là văn bản text, thanh trạng thái chỉ cho tổng của những ô có dữ liệu số
Fig. 243
However, as shown in Fig. 244, if one of the highlighted cells is an error such as #N/A, then the status bar total will not appear.
Dù vậy nếu 1 trong các ô chọn bị lỗi như #N/A, số tổng trên thanh trạng thái sẽ không hiện ra.
Fig. 244 Summary: The QuickSum feature in the status bar will show the total of selected cells. Tóm tắt: ô QuickSum trên thanh trạng thái sẽ hiện tổng của các ô chọn.
COUNT, AVERAGE, ETC. WITHOUT USING A FORMULA ĐẾM, TÍNH TRUNG BÌNH, … KHÔNG DÙNG CÔNGTHỨC
Problem: Your manager calls on the telephone and asks for the average price of a particular product. You need to quickly find an average of the prices in Fig. 245. Is there a faster way than entering a formula?
Vấn đề: Sếp bạn lại gọi điện và lần này muốn biết giá bán trung bình của 1 mặt hàng cụ thể. Bạn lại cần 1 cách tìm số trung bình cực nhanh. Có cách nào nhanh hơn gõ công thức?
Fig. 245
Strategy: The QuickSum feature in the status bar can be customized to show Average, Min, Max, Count, or Count Numbers. Highlight a range of numbers. Look for the QuickSum value in the status bar at the bottom of the window. As shown in Fig. 246, right-click the QuickSum and a menu pops up allowing you to choose Average, Count, Count Numbers, Max, Min, or Sum. Select Average.
Giải pháp: Cái của quỷ QuickSum trong thanh trạng thái có thể tuỳ ý đổi thành trung bình, Min, Max, Đếm và Đếm số. Đánh dấu chọn 1 khối ô. Nhìn vào Cái QuickSum, bấm chuột phải vào đó và chọn Average trong danh sách xổ xuống.
Fig. 246
Result: The QuickSum changes to show an average, as shown in Fig. 247.
Kết quả: Cái QuickSum đã chuyển sang hiển thị giá trị trung bình.
Fig. 247
Additional Information: Excel will remember the setting of the QuickSum area from session to session. If you change from Sum to Average, it will continue to show averages of the selected cells until you change this back to a Sum. Count will count all non-blank cells in the selection. Count Nums will only count cells that contain numeric or date values. Summary: You can quickly find the average, count, min, or max of a range by using the right-click menu to customize the QuickSum information in the status bar.
Nói thêm: Excel sẽ nhớ thiết lập này. Nếu bạn đổi Sum thành Average, Excel sẽ hiển thị số trung bình cho đến khi bạn thay đổi thiết lập trở lại Sum. Nếu bạn chọn Count, Excel sẽ đếm mọi ô không rỗng. Nếu bạn chọn Count Numbers, Excel sẽ chỉ đếm các ô có giá trị số. Tóm tắt: Bạn có thể tính nhanh số trung bình, Min, Max, Đếm và Đếm số 1 khối ô bằng cách click chuột phải vào cái QuickSum trên thanh trạng thái.
ADD TWO COLUMNS WITHOUT USING FORMULAS CỘNG 2 CỘT LẠI KHÔNG DÙNG CÔNG THỨC
Problem: You prepared a summary of sales by rep for the month. Due to an accounting glitch, someone gave you a similar file with additional sales made on the last day of the month, as shown in Fig. 248. You need to add the new sales to the old sales. There is no need to keep the original two columns of partial month’s sales.
Vấn đề: Bạn đang chuẩn bị 1 báo cáo doanh số bán của tháng. Bởi tính chất của báo cáo kế toán, người ta lại đưa cho bạn 1 bảng doanh số bán của ngày cuối tháng như hình 248, bạn đã đưa vào cột H. bạn cần cộng doanh số này vào tổng doanh số trước đó ở cột B. Không được phép để doanh số bán trong tháng thành 2 phần nằm ở 2 cột như vậy.
Fig. 248
Strategy: Copy the new values in column H and use Paste Special – Add to add the values to column B. Follow these steps. 1) Select H4:H22. From the menu, select Edit – Copy to copy the cells to the clipboard. 2) Move the cell pointer to B4. From the menu, select Edit – Paste Special. 3) As shown in Fig. 249, in the Paste Special dialog box, choose the Add option in the Operation section. Optionally, also choose Values in the Paste section in order to preserve the formatting in column B. Choose OK.
Giải pháp: Copy doanh số bán ở cột H và sử dụng Paste – Special –Add để cộng vào cột B, làm theo các bước sau: - Chọn vùng H4:H22, Copy dữ liệu vùng này vào clipboard (vùng nhớ đệm) - Chọn ô B4. CHọn Menu Edit – Paste Special. - Trong hộp thoại hiện ra, chọn mục chọn Add trong nhóm Operation. Đồng thời có thể chọn thêm mục chọn Value trong nhóm Paste nhằm duy trì định dạng của cột B, nhấn OK.
Fig. 249
Result: As shown in Fig. 250, the new sales data from column H is added to the values in column B. You can safely delete column H.
Kết quả: như hình 250, doanh số bán thêm ở cột H đã được tự cộng thêm vào doanh số bán trước đó ở cột B. Bạn có thể xoá cột H 1 cách an toàn.
Fig. 250
Gotcha: If column B is properly formatted and the temporary data in H is not formatted, the default option of Paste All will cause the formats in column B to be lost. This is why you should consider choosing both Values and Add in the Transpose dialog. Additional Information: The Paste Special – Add technique has an interesting effect if you add cells to a range that contains a formula. Amazingly, Excel does handle this correctly. Cell D4 contains a formula, as shown in Fig. 251.
Ghi chú: Nếu cột B đã được định dạng và cột dữ liệu tạm H thì chưa, mặc định Paste All sẽ làm cho định dạng của cột B bị mất. Đó là lý do bạn nên đánh dấu chọn thêm mục Value trong hộp thoại Paste Special. Nói thêm: Kỹ thuật Paste Special – Add có hiệu ứng đặc biệt hay nếu bạn thực hiện cộng thêm cho 1 ô có chứa công thức. Excel làm hết sức chính xác chuyện này: ô D4 đang chứa 1 công thức như hình 251
Fig. 251
If you Paste Special – Add a value to this formula, Excel changes the formula to add the value, as shown in Fig. 252.
Nếu bạn thực hiện Paste Special – Add 1 giá trị vào ô D4, Excel sẽ sửa công thức D4 lại như hình 252.
Fig. 252
Summary: Using the Paste Special Add technique is useful to add two columns of numbers without using a formula.
Tóm tắt: Sử dụng kỹ thuật Paste Special – Add để cộng 2 cột dữ liệu mà không cần gõ công thức.
HOW TO CALCULATE SALES OVER QUOTA TÍNH DOANH SỐ VƯỢT MỨC
Problem: In the spreadsheet shown in Fig. 253, enter a formula to calculate the excess of sales over quota on a record-by-record basis.
Vấn đề: Trong hình 253, tạo 1 công thức tính doanh số bán vượt mức của từng dòng.
Fig. 253
Strategy: There are a couple of functions that would work in this situation. For instance, you could use an IF function. Give the IF function a logical test and specify one calculation if the test is true and one calculation if the test is false. If the Sales value is greater than the Quota, the IF function would return Sales (D2) – Quota (C2). If the sales did not exceed quota, then the IF function would return 0. The syntax for the IF function is =IF(logical test, value if true, value if false). Thus, as shown in Fig. 254, the formula would be: =IF(D2>C2,D2–C2,0) Giải pháp: Có 1 số cách khác nhau để giải quyết vấn đề này. Thí dụ: dùng hàm IF. Cấp cho hàm If 1 biểu thức luận lý, 1 phép tính nếu biểu thức luận lý đúng và 1 phép tính nếu biểu thức luận lý sai. Nếu doanh số bán lớn hơn định mức, hàm If sẽ cho kết quả của phép tính Doanh số bán (D2) - Định mức (C2). Nếu Doanh số bán chưa đạt, Hàm If sẽ cho giá trị zero. Cấu trúc hàm IF là IF(biểu thức luận lý, giá trị nếu đúng, giá trị nếu sai). Do đó, như hình 254, công thức sẽ là: =IF(D2>C2,D2–C2,0)
Fig. 254
Alternate Solution: Use the MAX function. One parameter to the MAX function will be D2–C2. This number will be either positive or negative, as shown in column F in Fig. 255. You can ask Excel to calculate the larger of either the calculation in F or a zero. If you think about it, the Max of 0 and a positive number is the positive number. The Max of 0 and a negative number is 0. As shown in Fig. 256, using =MAX() is slightly shorter and quicker than entering the =IF() function.
Giải pháp thứ 2: Dùng hàm MAX: 1 giá trị dùng để so sánh là D2 – C2. Số này có thể là dương và cũng có thể là âm như cột F trên hình 255. bạn có thể yêu cầu Excel tìm số lớn hơn giữa giá trị tính toán ở cột F và số zero. Một khi bạn nghĩ đến số lớn hơn trong 2 số, Max của 1 số dương và 0 là số dương. Max của zero và 1 số âm là zero. Như hình 256, dùng hàm Max() thì ngắn hơn và nhanh hơn là dùng hàm IF()
Fig. 255
Fig. 256
Summary: To find only positive results of a calculation, you can use either the =IF() or =MAX(0,Calc) function. Either will work. The =MAX is slightly better. Use =MIN(0,Calc) to find only negative values.
Tóm tắt: Nếu muốn tìm chỉ kết quả số dương của 1 phép tính, có thể dùng hàm IF() hoặc hàm MAX(). Cả hai đều thực hiện đúng nhưng hàm Max ngắn gọn hơn. Ngược lại dùng hàm Min(0, Phép tính) để tìm kết quả âm của phép tính
Problem: As shown in Fig. 257, you have data with First Name in column A and Last Name in column B. You want to merge these into one column.
Vấn đề: Bạn có dữ liệu là tên (tiếng Anh) ở cột A và Họ ở cột B. Bạn muốn kết hợp chúng lại với nhau.
Fig. 257
Strategy: Use the ampersand (&) as a concatenation operator in a formula in column C. Change the formulas in column C to values before deleting columns A and B. Follow these steps. 1) In cell C2, enter the formula =A2&B2, as shown in Fig. 258.
Giải pháp: Sử dụng dấu & như là 1 toán tử nối chuỗi trong công thức ở cột C. Đổi công thức cột C này thành giá trị trước khi xoá cột A và B. 1. Ở ô C2, gõ công thức = A2&B2
Fig. 258
2) You need to insert a space between the first name and last name. If you join cell A2, a space in quotes, and cell B2, the answer will look acceptable. The formula is =A2&“ ”&B2. Copy this formula down to all of the cells in your range, as shown in Fig. 259.
2. Bạn cần 1 khoảng trắng giữa tên và họ. nếu bạn nối ô A2 với 1 khoảng trắng bao bởi dấu nháy kép, rồi tới ô B2, kết quả coi mới tạm được. Công thức là =A2&” “&B2. Sao chép công thức xuống dưới.
Fig. 259
Additional Information: To convert BRITNEY SPEARS to Britney Spears, use the PROPER function. =PROPER(A2&“ ”&B2) will convert the names to Proper Case, as shown in Fig. 260. This will work for all of your names except names with an interior capital such as “Paul Mc- Cartney” or “Dave VanHorn”. After using the PROPER function, you will have to manually fix any names with an interior capital letter.
Nói thêm: Muốn chuyển chữ in hoa thành chữ hoa đầu tên như BRITNEY SPEARS thành Britney Spears, dùng hàm PROPER; = PROPER( A2&” “&B2). Công thức này xài được cho hầu hết các tên trừ trường hợp đặc biệt như Paul Mc- Cartney hoặc Dave VanHorn có những chữ hoa giữa từ. Sau khi dùng hàm Proper, bạn có thể sửa tay những chữ cái này.
Fig. 260
Gotcha: If you delete columns A and B while column C still contains formulas, then all of the formulas will change to #REF! errors, as shown in Fig. 261. This tells you that you have a formula that points to cells(s) that are no longer there. Immediately hit Ctrl+Z to undo the delete.
Ghi chú: Nếu bạn delete cột A và B trong khi cột C còn nguyên dạng công thức, cột C sẽ có lỗi #REF! do công thức tham chiếu đến các ô không còn tồn tại. Hãy nhấn Ctrl+Z để huỷ lệnh xoá đó.
Fig. 261
To work around this situation, first convert all of the formulas in column C to values. Follow these steps: 1) Select the data in column C. 2) Select Ctrl+C to copy the data to the clipboard. 3) Without changing the selection, select Edit – Paste Special…. 4) In the Paste Special dialog box, choose Values and then OK, as shown in Fig. 262. 5) This converts column C from live formulas to static values. You can now delete columns A and B.
Muốn làm việc này an toàn hãy chuyển đổi công thức cột C thành giá trị. Làm theo các bước sau: - Chọn dữ liệu cột C - Nhấn Ctrl+C để sao chép dữ liệu vào clipboard - Đừng thay đổi vùng chọn, mở Menu Edit – Paste Special… - Trong hộp thoại Paste Special chọn Value và nhấn OK - Công thức cột C sẽ bị đổi thành dữ liệu thô. Bây giờ bạn có thể xoá cột A và B.
Fig. 262
Summary: The ampersand (&) is the concatenation character used to join text cells with other text cells or with literal values in a formula.
Tóm tắt: Dấu & là ký tự nối dùng để nối chuỗi với chuỗi hoặc nối với giá trị chuỗi trong công thức.
HOW TO SORT ON ONE SEGMENT OF AN ACCOUNT ID LÀM THẾ NÀO ĐỂ SẮP XẾP THỨ TỰ THEO 1 PHẦN CỦA MÃ KHÁCH HÀNG
Problem: Your company assigns an account ID to every customer. One segment of the account ID contains useful information, such as a parent company code. You want to be able to sort on the basis of a portion of the account ID. In Fig. 263, the first three digits of the account are used to identify an office.
Vấn đề: Công ty bạn gán cho mỗi khách hàng 1 mã KH. Một phần của mã này là thông tin hữu ích như là thông tin về công ty mẹ của khách hàng. Bạn muốn sắp thứ tự khách hàng theo tiêu chí này. Trong hình 263, 3 số đầu của mã được dùng để xác định 1 công ty.
Fig. 263
Strategy: Insert a new column and use the LEFT function to isolate the necessary digits from the account field. 1) In the blank column, enter a heading such as the word “Key”. In cell G2, enter the formula =LEFT(A2,3), as shown in Fig. 264. This indicates that the new field should contain just the three characters leftmost of the ID field.
Giải pháp: Chèn vào 1 cột và dùng hàm LEFT để cắt ra các con số cần thiết trong cột mã. - Trong 1 cột trống, đặt tên cột là Key, ở ô kế tiếp (G2) gõ công thức: =LEFT(A2,3) như hình 264. Công thức này sẽ lấy 3 ký tự đầu bên trái của cột mã.
Fig. 264
2) Double-click the Fill handle in cell G2 to copy the formula down to all of the rows in your dataset. The Fill handle is the black square dot in the lower right corner of the cell pointer. 3) Change the formulas in column G to values. Highlight all of the cells in column G. Use Ctrl+C to copy. As shown in Fig. 265, from the menu, select Edit – Paste Special – Values – OK.
- Doubled-click vào nút Fill Handle của ô g2 nhằm sao chép công thức xuống mọi dòng dữ liệu. - Đổi công thức trong cột G thành giá trị. Tô khối toàn bộ cột G, gõ Ctrl+C. Sau đó vào Menu Edit – Paste Special – Value – OK
Fig. 265
Result: A certain portion of the Account field is now available in a new column. You can now use data tools, such as Sort, Filter, or Subtotal, to isolate certain offices. Summary: When you need to isolate a portion of the characters in another column, creating a temporary column is the solution.
Kết quả: 1 phần của mã nay được đặt vào cột G, bạn có thể dùng các công cụ của Excel như Sort, Filter, SubTotal để làm việc với trường mới này.
HOW TO ISOLATE THE CENTER PORTION OF AN ACCOUNT ID LÀM SAO ĐẨ TÁCH RA 1 PHẦN NẰM GIỮA CỦA MÃ Problem: Your company assigns an account ID in the format of SSSXX- YYYY as shown in Fig. 266. You need to isolate the XX portion of the account ID in order to subtotal or sort the data. Strategy: Insert a new column and use the MID function to isolate the necessary digits from the account field. The MID function takes three arguments. The first argument is a cell containing a text value. The second argument is the character number where you want the result to start. The final argument is the length of the result. In a well-formed account number, like 123-45-6789, you can predict that the start of the second segment will always be in the fifth character position. The length of the second segment is always two characters.
Vấn đề: Công ty bạn đặt mã khách hàng với cấu trúc SSSXX-YYYY như hình 266. Bạn cần tách ra phần XX của mã nhằm sắp thứ tự hoặc tính subtotal. Biện pháp: Chèn vào 1 cột và dùng công thức MID để tách 1 phần giữa của mã. Hàm NID có 3 tham số: Tham số thứ nhất là ô có chứa chỗi dữ liệu. Tham số thứ 2 là 1 số cho biết sẽ tách bắt đầu từ lý tự thứ mấy. Tham số thứ 3 là 1 số cho biết độ dài chuỗi cần lấy ra. Trong 1 chuỗi số cấu trúc tốt như là 123-45-6789, bạn có thể thấy nhóm số thứ hai luôn bắt đầu từ ký tự thứ 5 và độ dài của nhóm số này luôn bằng 2.
Fig. 266
In a blank column, enter a heading, such as the word “Key”. In cell G2, enter the formula MID(A2,5,2). Copy the formula down to all rows, as shown in Fig. 267.
- Trong 1 cột trống, đặt tên cột là Key, ở ô kế tiếp (G2) gõ công thức: =MID(A2,5,2) như hình 265. Sao chép công thức xuống các dòng còn lại của dữ liệu.
Fig 267
Additional Details: In order to capture the final four digits of the account number, you could either use the =MID(A2,8,4) or the =RIGHT(A2,4) function to isolate the final four digits of the account number. Result: You can now sort by the new column and add subtotals by this field. Summary: When you need to isolate a portion of the characters in another column, creating a temporary column is the solution.
Nói thêm: Nhằm tách ra nhóm 4 số cuối cùng của chuỗi trên, bạn có thể dùng hoặc MID(A2,8,4) hoặc RIGHT(A2,4) Kết quả: giờ thi bạn có thể sắp thứ tự dữ liệu theo cột mới này, hoặc chèn vào các dòng SubTotal theo cột này. Tóm tắt: Khi cần tách ra 1 phần của 1 chuỗi ra 1 cột khác, tạo ra 1 cột phụ là 1 trong những giải pháp.
HOW TO ISOLATE EVERYTHING BEFORE A DASH IN A COLUMN BY USING FUNCTIONS LÀM THẾ NÀO ĐỂ TÁCH MỌI KÝ TỰ PHÍA TRƯỚC DẤU GẠCH NGANG BẰNG HÀM EXCEL
Problem: A vendor provides an Excel worksheet. As shown in Fig. 268, one field has a manufacturer code, a dash, and a part number. The manufacturer codes are not always the same length. You need to isolate the manufacturer code in a new column.
Vấn đề: Một nhà cung cấp có 1 bảng tính. Như hình 268, 1 trường có dữ liệu là mã nhà sản xuất, 1 dấu gạch ngang, và 1 số mã hàng. Mã nhà sản xuất không có cùng độ dài. Bạn cần tách mã nhà SX ra 1 cột khác.
Fig. 268
Strategy: Use the FIND function to locate the dash in the item number. The FIND function will return the character position of the dash. You can then use that result minus one in the LEFT function to isolate the manufacturer code. The FIND function requires two arguments. The first argument is the text that you are trying to locate. In this case, you are trying to locate a dash, so you should include the dash in quotes. The second argument is the location of the cell containing the text to search. 1) Enter =FIND(“-”,A2) in cell F2. Copy down to all of the other cells, as shown in Fig. 269.
Giải pháp: Dùng hàm FIND để xác định vị trí của dấu gạch ngang. Hàm FIND sẽ trả về vị trí của ký tự dấu gạch ngang. Bạn có thể dùng số thứ tự này trừ 1 và đưa vào hàm LEFT lấy ra số ký tự nằm bên trái dấu gạch. Hàm FIND cần 2 tham số: Tham số thứ nhất là ký tự hoặc chuỗi ký tự cần tìm vị trí. Trong thí dụ này, đó là dấu gạch ngang nằm trong cặp nháy kép. Tham số thứ hai là vị trí của ô chứa chuỗi có chứa ký tự cần tìm. - Gõ công thức =FIND(“-“,A2) vào ô F2. Sao chép công thức này xuống dưới.
Fig. 269
The 3 in cell F2 indicates that the dash is located in the third character position of cell A2. The 6 in cell F3 indicates that the dash is in the sixth position of cell A3. If you want to isolate the manufacturer code, you will want to grab one less than this number from the left of the code. 2) In cell G2, enter the formula =LEFT(A2,F2–1), as shown in Fig. 270. Double-click the Fill handle to copy this formula down to all cells.
Số 3 trong ô F2 cho biết dấu gạch ngang nằm ở vị trí thứ 3 trong chuỗi ở ô A2. Số 6 ở ô F3 cho biết dấu gạch ngang nằm ở vị trí thứ 6 trong chuỗi ở ô A3. Nếu bạn muốn tách mã nhà SX, bạn sẽ cần lấy số ký tự bên trái ô A2, số này ít hơn số trong cột F 1 ký tự. - Ở ô G2, gõ công thức LEFT(A2,F2-1), như hình 270. Sao chép công thức này xuống.
Fig. 270
Alternate Strategy: You do not need to enter the formulas in two different columns. You could easily have used this formula in cell F2: =LEFT(A2,FIND(“-”,A2)–1) Additional Details: Once the formula has isolated the manufacturer code, change the formulas to values. See the Cross Reference. Result: You can now sort by the new column and add subtotals by this field. Summary: When you need to isolate a portion of the characters in another column, creating a temporary column is the solution.
Cách khác: Bạn không cần thiết phải làm ra 2 cột như trên, bạn có thể dùng ngay công thức này ở ô F1: =LEFT(A2,FIND(“-“,A2)-1) Khi đã tách được mã nhà SX, đổi cộng thức trên thành giá trị. Kết quả: Giờ thì bạn có thể sắp thứ tự theo cột này và gán subtotal dựa vào cột này. Tóm tắt: Khi bạn cần tách 1 phần các ký tự ra khỏi 1 chuỗi, có 1 giải pháp là tạo ra 1 cột phụ.
HOW TO USE FUNCTIONS TO ISOLATE EVERYTHING AFTER A DASH IN A COLUMN LÀM NHƯ THẾ NÀO ĐỂ TÁCH MỌI KÝ TỰ PHÍA SAU DẤU GẠCH NGANG
Problem: A vendor provides an Excel worksheet. One field has a manufacturer code, a dash, and a part number, as shown in Fig. 271. The manufacturer codes are not always the same length. You need to isolate the part number in a new column.
Vấn đề: Một nhà cung cấp có 1 bảng tính. Một nhà cung cấp có 1 bảng tính. Như hình 268, 1 trường có dữ liệu là mã nhà sản xuất, 1 dấu gạch ngang, và 1 số mã hàng. Mã nhà sản xuất không có cùng độ dài. Bạn cần tách mã mặt hàng ra 1 cột khác.
Fig. 271
Strategy: Use the MID function to extract a portion of text from the middle of the text. The MID function requires three arguments: =MID(Cell with Text, Character Number to Start, Number of Characters). You can use the FIND function to locate the dash in the item number and start at one character to the right. You can use the LEN function to figure out how long the text is. The FIND function requires two arguments. The first argument is the text that you are trying to locate. In this case, you are trying to locate a dash, so you should include the dash in quotes. The second argument is the location of the cell containing the text to search. 1) Enter =FIND(“-”,A2) in cell F2. Copy down to all of the other cells, as shown in Fig. 272.
Giải pháp: Dùng hàm MID để tách 1 phần của chuỗi. Hàm này có 3 tham số: = MID( ô chứa chuỗi, số thứ tự ký tự bắt đầu tách, chiều dài chuỗi con cần tách). Bạn cũng cần hàm FIND() để xác dịnh vị trí dấu gạch ngang và bắt đầu trích từ ký tự kế ký tự đó. Bạn có thể dùng thêm hàm LEN để tìm độ dài chuỗi. Hàm FIND cần 2 tham số: Tham số thứ nhất là ký tự hoặc chuỗi ký tự cần tìm vị trí. Trong thí dụ này, đó là dấu gạch ngang nằm trong cặp nháy kép. Tham số thứ hai là vị trí của ô chứa chuỗi có chứa ký tự cần tìm. - Gõ công thức =FIND(“-“,A2) vào ô F2. Sao chép công thức này xuống dưới.
Fig. 272
The 3 in cell F2 indicates that the dash is located in the third character position of cell A2. If you want to isolate the part number, you will want to start one character to the right of this position. So, you know the start of your formula will be =MID(A2,F2+1. The hard part is figuring out how many characters there are in the part number. 2) In cell G2, enter the formula =LEN(A2). Copy this formula down to all rows, as shown in Fig. 273. The LEN function will tell you the total number of characters in cell A2. Cell G2 tells you that there are 10 characters in A2. If the dash is in the third position, then you want to grab (G2–F2) characters or (10–3). This will give you 7 characters.
Số 3 trong ô F2 cho biết dấu gạch ngang nằm ở vị trí thứ 3 trong chuỗi ở ô A2. Nếu bạn muốn tách phần mã mặt hàng, bạn cần tríach chuỗi từ vị trí bên phải ký tự này 1 ký tự. Vậy bạn bắt đầu công thức của bạn với =MID(A2,F2+1,… Còn phải xác định số ký tự cần trích từ vị trí đó. - Ở ô G2, gõ công thức =LEN(A2). Sao chép công thức này xuống các ô phía dưới. Hàm LEN sẽ cho bạn biết chiều dài (tổng số các ký tự) của 1 chuỗi. Như ô G2 cho biết chuỗi trong ô A2 có 10 ký tự. Nếu dấu gạch ngang ở vị trí số 3, bạn sẽ cần trích (G2-F2) ký tự hay là (10-3) ký tự, nghĩa là 7.
Fig. 273
3) In cell H2, enter the formula =MID(A2,F2+1,G2–F2), as shown in Fig. 274. In plain language, this tells Excel to extract characters from A2. Excel should start at the character after the result in F2 and continue for a length of (G2–F2) characters. In this case, it is much harder to enter the formula in one column instead of three columns. This is because the result of the FIND function is used twice in the H2 formula. In order to build a single formula, you will be entering the FIND function twice in that formula. As shown in Fig. 275, you could have entered the following formula in H2: =MID(A2,FIND(“-”,A2)+1,LEN(A2)–FIND(“-”,A2)).
- Ở ô H2, gõ vào công thức =MID(A2,F2+1,G2–F2), như hình 274. Theo ngôn ngữ thường, công thức này lệnh cho Excel trích 1 số ký tự từ ô A2. Excel sẽ bắt đầu từ ký tự kế sau kết quả tính được trong F2, và lấy tiếp số ký tự là G2-F2. Trong trường hợp này sẽ khó khi muốn gộp chúng vào 1 công thức duy nhất thay vì 3 công thức trong 3 cột. Bởi vì kết quả hàm FIND được sủ dụng 2 lần trong công thức ô H2 như hình 275: =MID(A2,FIND(“-”,A2)+1,LEN(A2)–FIND(“-”,A2)).
Fig. 274
Fig. 275
Alternate Strategy: You might be tempted to use a formula such as =MID(A2,F2+1,50). The third parameter, 50, will ensure that it can grab a part number of any conceivable length. This approach will work, but the resulting answer will have forty-three blanks appended to the end of the part number. This makes it impossible to use successfully in a VLOOKUP formula as it stands. However, you can use =TRIM(MID(A2,F2+1,50)) to remove the trailing spaces. Summary: Use a combination of LEN and FIND functions as an aide to help you locate the proper start or end position for the LEFT, MID, and RIGHT functions.
Cách khác: bạn có thể dùng 1 công thức như sau: =MID(A2,F2+1,50). Tham số thứ 3 bạn gán số 50, bảo đảm rằng sẽ trích được mọi mã hàng với mọi chiều dài có thể của mã cần trích. Công thức này cũng hoạt động, nhưng nó sẽ nối vào sau mã hàng 43 ký tự trắng. Việc này làm cho các công thức như VLOOKUP không hoạt động. Vậy thì bạn dùng hàm TRIM để xoá bỏ những khoảng trắng vô ích: =TRIM(MID(A2,F2+1,50)) . Tóm tắt: Dùng kết hợp hàm LEN, FIND để xác định vị trí bắt đầu và kết thúc của các hàm LEFT, MID, RIGHT.
HOW TO USE FUNCTIONS TO ISOLATE EVERYTHING AFTER THE SECOND DASH IN A COLUMN LÀM THẾ NÀO ĐỂ TÁCH CHUỖI PHÍA SAU DẤU GẠCH NGANG THỨ 2?
Problem: A vendor file contains a three-segment part number, as shown in Fig. 276. Each segment is separated by a dash. The length of each segment could be any number of characters. Find the second or third segment.
Vấn đề: 1 file Excel có chứa dữ liệu trong 1 cột là mã gồm 3 phần, cách nhau bởi các dấu gạch ngang. Chiều dài mỗi phần có thể là bất kỳ số nào.
Fig. 276
Strategy: There is an optional third argument in the FIND function. This tells Excel to start looking after a certain character position in the text. In this case, to find the second dash, you want Excel to start looking after the location of the first dash. 1) As in the prior examples, use =FIND(“-”,A2) in cell F2 to locate the first dash. 2) Enter =FIND(“-”,A2,F2+1) in cell G2, as shown in Fig. 277. The F2+1 parameter tells Excel that you want to find a dash starting in the fourth character position of cell A2. 3) Enter =LEFT(A2,F2–1) in H2. The formula in H2 locates the first segment of the part number. 4) Enter =MID(A2,F2+1,G2–F2) in I2. The formula in I2 locates the middle segment of the part number. 5) To get the right segment of the part number, you can use the RIGHT function. Just like the LEFT function, the RIGHT function requires a cell and the number of characters from the right side of the item number. To find the number of characters, use =LEN(A2)–G2. Enter the resulting formula, =RIGHT(A2,LEN(A2)–G2), in J2. See Fig. 278.
Giải pháp: Có 1 tham số thứ 3 tuỳ chọn trong hàm FIND. Tham số này cho Excel biết phải tìm từ vị trí thứ mấy trong chuỗi. Trong trường hợp này, bạn múôn Excel tìm từ bên phải dấu gạch ngang thứ nhất. 1. Như thí dụ bài trên, dùng công thức =FIND(“-“,A2) ở ô F2 để xác định vị trí dấu gạch ngang thứ nhất -2. Gõ công thức =FIND(“-“,A2,F2+1) cho ô G2, F2+1 là tham số thứ 3, nó cho Excel biết bắt đầu tìm từ ký tự thứ 4 của chuỗi trong ô A2. 3. Gõ công thức = LEFT(A2,F2-1) trong ô H2. Công thức này tách phần thứ nhất của mã. 4. Gõ công thức =MID(A2,F2+1,G2–F2) ở ô I2. Công thức này tách phần thứ 2 của mã. 5. Để lấy phần thứ 3 của mã, dùng hàm RIGHT. Giống như hàm LEFT, hàm FIGHT đòi 1 địa chỉ ô chứa chuỗi gốc cần tách, và số ký tự cần tách từ bên phải. Để tính số ký tự cần tách này, lấy LEN(A2)-G2. Đây là công thức đủ: =RIGHT(A2,LEN(A2)–G2), trong ô J2
Fig. 277
Fig. 278
Gotcha: All of these formulas are trusting that the vendor always included two dashes in the item number. If an item number exists without a second dash, the second FIND function would return a #VALUE! error, leading to errors in the calculation for the second and third items. Before converting formulas to values and deleting the original part number, sort the data by column F and then sort descending by column G. As shown in Fig. 279, any #VALUE! errors will sort to the top of the dataset, where you can locate and correct the errors in the part number. Summary: Using combinations of FIND, LEN, MID, LEFT, and RIGHT, it is possible to parse nearly any data imaginable.
Ghi chú: Mọi công thức trên đây đáng tín cậy nếu như bảng mã luôn có 2 dấu gạch ngang phân cách 3 phần của mã. Nếu có 1 mã nào chỉ có 2 phần, và không có dấu gạch ngang thứ 2, Hàm FIND thứ 2 sẽ bị lỗi #VALUE, kéo theo là lỗi cho công thức thứ 2 và thứ 3. Trước khi chuyển công thức thành giá trị để xoá cột mã gốc, hãy sắp xếp dữ liệu theo cột F, và thứ tự giảm dần theo cột G. Như hình 279, mọi ô bị lỗi #VALUE sẽ nằm trên cùng và bạn có thể chỉnh sửa công thức.
Fig. 279
Tóm tắt: Sử dụng hỗn hợp các hàm FIND, LEN, MID, LEFT, và RIGHT, bạn có thể phân tích hầu hết dữ liệu thành các phần riêng rẽ.
HOW TO SEPARATE A PART NUMBER INTO THREE COLUMNS LÀM CÁCH NÀO ĐÊ TÁCH 1 MÃ HÀNG RA 3 CỘT RIÊNG BIỆT.
Problem: A vendor file contains a three-segment item number, as shown in Fig. 280. Each segment is separated by a dash. The FIND function makes your head hurt. Break the Part Number into three columns. Vấn đề: Một bảng tính trong đó có 1 cột mã hàng gồm 3 phần. Mỗi phần ngăn cách bởi dấu gạch ngang. Dùng hàm FIND như bài trên làm cho bạn đau đầu. Nhưng bạn vẫn muốn tách ra 3 phần riêng biệt.
Fig. 280
Strategy: Use the Text to Columns command on the Data menu to parse the item number. Follow these steps. 1) Copy the item number to the right side of your data in column F. The Text To Columns command will fill several columns to the right of the original column. Make sure you have plenty of blank columns. 2) Select the entire range of data in column F. Place the cell pointer in F2. Hit the End key. While holding down the Shift key, hit the Down Arrow key to select the entire range. 3) From the menu, select Data – Text to Columns. The Wizard will work on either data that is delimited or on data that has a fixed width to each segment. Our data is delimited by a dash. As shown in Fig. 281, in Step 1, leave the radio button on the Delimited setting. Click Next. By default, Step 2 assumes that the data is delimited by a tab. As shown in Fig. 282, other choices are commas, spaces, and semicolons. Note that dash is not in the list. 4) Uncheck the Tab checkbox. Check the Other checkbox. In the Other textbox, enter a dash. As shown in Fig. 283, the data preview window will show the data in three columns. Click Next.
Giải quyết: Dùng chức năng Text to Column có trong Menu Data. Làm theo các bước sau: - sao chép cột mã hàng ra phía sau bảng dữ liệu (cột F). Công cụ Text to Column sẽ chèn dữ liệu sau khi tách vào các cột bên phải, nên hãy bảo đảm rằng bạn có 1 số cột trống phía sau. - Chọn vùng dữ liệu trong cột F: Chọn vào ô F2, trong khi nhấn giữ phím Shift, nhấn phím mũi tên xuống. - Mở menu Data – Text to Column… Công cụ này sẽ làm việc với cả dữ liệu có ký hiệu phân cách lẫn dữ liệu có thể xác định độ dài chuỗi ký tự. Dữ liệu ta đang có là dữ liệu có dấu hiệu phân cách. Trong hộp thoại hình 281, click chọn Delimited, rồi Next. Mặc định bước 2 sẽ cho bạn ký hiệu phân cách là ký hiệu Tab. Những chọn lựa khác là dấu phẩy, khoảng trắng và dấu chấm phẩy. Chả có dấu gạch ngang. - Bỏ chọn ô Tab. Click chọn ô Other. Trong textbox Pther, gõ dấu gạch ngang. Như hình 283, màn hình xem trước cho thấy đã tách ra 3 cột, nhấn Next
Fig. 281
Fig. 282
Fig. 283
5) In Step 3, you can optionally specify the data type of the columns. Unless you have dates, the General type is OK. Note that if you want to preserve the leading zeroes in the second segment of the item number, you should choose the heading of that fi eld and change from General to Text, as shown in Fig. 284. Click Finish.
- Ở bước 3, bạn có thể chọn loại dữ liệu cho mỗi cột. Trừ khi bạn có dữ liệu ngày tháng, mọi cột sẽ có loại mặc định là General. Nhưng bạn lại muốn giữ lại các số zero ở đầu các đọan 2 của mã, vậy hãy nhấn chuột chọn vào tiêu đề cột đó và sửa định dạng từ General thành text. Nhấn Finish.
Fig. 284
Result: The original column F has been overwritten with the first portion of the result. New columns G and H contain the second and third segments of the item number, as shown in Fig. 285. Gotcha: A very strange anomaly will appear for the remainder of this Excel session. If you later open a Notepad file, copy data that contains dashes, and attempt to paste to Excel, Excel will automatically split the data into columns where the dash is located, as shown in Fig. 286 and Fig. 287. This can be a very handy feature if you are expecting it, or a very puzzling situation if you are not.
Kết quả: Cột F sẽ bị chép đè bởi dữ liệu phần 1 của mã mới được tách. Hai cột và H sẽ là dữ liệu 2 phần còn lại.
Fig. 285
Ghi chú: Thật lạ thường là sự ứng xử sau đó của Excel. Nếu sau này bạn có copy dữ liệu từ Notepad có những dấu gạch ngang không phải là dấu phân cách , Excel cũng sẽ tự động tách chúng ra thành các phần riêng rẽ theo dấu hiệu này. Đây là điểm hữu ích cho bạn nếu bạn mong đợi, và là 1 tình huống bất ngờ nếu bạn không muốn.
Fig. 286
Fig. 287 Summary: If your data is set up with consistent delimiters, the Text to Columns Wizard is a fast way to parse data.
Tóm tắt: Nếu dữ liệu của bạn có nhiều phần và có những ký hiệu phân cách, và bạn muốn tách chúng ra thì hãy dùng phương thức Text to Column.
AVOID #REF! ERRORS WHEN DELETING COLUMNS TRÁNH LỖI #ERROR KHI XOÁ CỘT.
Problem: Excel is great at computing results. Sometimes, you only need to keep the results of a formula and you want to erase the columns that the calculation was based upon. If you delete a column used in the formula, the formula result changes to #REF!. How can you prevent the #REF! Error? Here is a true story. When I was working at my last day job, someone in our marketing department was given a list of leads from a trade show. The Excel file had the area codes in one column and the telephone numbers in another column. There were several thousand leads. Someone in Marketing was retyping all of the telephone numbers in order to get the area code and phone number in one cell, as shown in Fig. 288.
Vấn đề: Excel là một phần mềm ứng dụng mạnh. Đôi khi bạn cần giữ kết quả của 1 công thức và muốn xoá những cột chứa dữ liệu nguồn của công thức. Nếu bạn xoá ngay thì công thức sẽ bị lỗi và hiện #REF!. Làm sao để tránh lỗi #REF!? Đây là 1 chuyện có thực. Trong giờ làm việc của 1 ngày gần đây nhất, 1 vài nhân viên phòng Marketing đưa cho tôi 1 danh sách các ghi chép trong 1 buổi trình diễn quảng cáo. Cái file Excel của họ có mã khu vực ở 1 cột và số điện thoại ở 1 cột khác. Có khoảng nhiều ngàn dòng ghi chép như vậy. người nào đó trong phòng đã kỳ công ngồi gõ lại số điện thoại vào cùng ô với mã khu vực như hình 288
Fig. 288
As shown in Fig. 289, I showed this person how to use a formula to quickly convert all five thousand telephone numbers to the format that she wanted. I started to walk away, whistling a little tune because I had just saved someone a whole lot of typing. I had just about reached the door of the marketing department when I heard a scream. Since column J contained the information that she needed, she had deleted columns H and I. However, since column J was still a live formula, Excel no longer knew how to calculate the results in J. And, because cells referenced in the formula had been deleted, all of the telephone numbers that were in column J now showed the #REF! error, as shown in Fig. 290.
Xem hình 289, tôi chỉ cho anh ta cách dùng công thức để chuyển hơn 5000 số điện thoại về định dạng mà cô ta mong muốn. Tôi đi khỏi chỗ đó, vừa đi vừa huýt sáo vì đã giúp 1 người tiết kiệm vô khối thời gian ngồi gõ gõ. Nhưng tôi chỉ mới vừa đi tới cửa phòng thì đã nghe tiếng la hoảng. Bởi vì khi thấy cột J đã có đủ thông tin mà cô ấy cần, cô ấy bèn xoá phứt 2 cột I và H. Và bởi vì cột J hãy còn là 1 công thức sống, Excel không làm sao có thể tính ra kết quả của công thức, thế là toàn bộ cột J trở thành các ô chứa #REF! như hình 290
Fig. 289
Fig. 290
Strategy: Immediately hit Ctrl+Z to undo the delete and get the source columns back. Next, you will copy the formulas and use Paste Special to paste the cells as values instead of formulas. 1) With the cell pointer in cell J2, hold down the Shift key while you tap the End key and then the Down Arrow key. This will select the entire contiguous range of cells in column J. 2) From the menu, select Edit – Copy to copy the cells to the clipboard. 3) Without changing the selection, select Edit – Paste Special from the menu. In the Paste Special dialog box, choose the Values option, as shown in Fig. 291. Choose OK. Result: The telephone numbers in column J are converted from formulas to the results of those formulas, as shown in Fig. 292.
Giải pháp: ngay lập tức bạn phải nhấn Ctrl+Z (Undo) để huỷ bỏ lệnh xoá cột. Sau đó dùng lệnh copy và Paste Special – Value để sao chép các công thức trở thành giá trị, chứ không phải công thức nữa. - Với ô chọn là J2, Nhấn giữ phím Shift và nhấn End sau đó nhấn mũi tên xuống. Việc này sẽ chọn 1 khối toàn bộ các ô trong cột J. - Mở menu Edit - chọn Copy để sao chép khối dữ liệu vào vùng nhớ. - Không thay đổi vùng chọn, mở lại menu Edit, chọn Paste Special. Trong hộp thoại hiện ra, click chọn mục Value và OK. Kết quả: Số điện thoại trong cột J đã trở thành kết quả của côngthức, chứ không còn là bản thân công thức.
Fig. 291
Fig. 292 You can now safely delete columns H and I and the telephone numbers in column J will not change to #REF! errors. Summary: Using the Paste Special Values technique is essential to becoming proficient in Excel. It will convert a range of formulas to the current value of the formula. You can then safely delete the source columns.
Bây giờ bạn có thể an tâm xoá bỏ cột H và cột I, cột J sẽ không còn bị lỗi #REF! nữa. Tóm tắt: Dùng Paste Special – Value là công cụ hữu dụng trong Excel. Nó sẽ đổi công thức thành giá trị của công thức, và bạn có thể an tâm xoá dữ liệu nguồn của công thức.
Problem: You want to create a range of random numbers in order to illustrate a concept in a class. As shown in Fig. 293, you want to fill in random numbers in columns C through E. Strategy: Use the RAND function. This function will return a random number between 0 and 0.99999.
Vấn đề: Bạn muốn tạo 1 chuỗi các số ngẫu nhiên để minh hoạ 1 vài vấn đề của 1 nhóm dữ liệu. như hình 293, bạn muốn tạo 1 loạt các số ngẫu nhiên vào các cột từ C đến E. Giải pháp: Hãy dùng hàm Rand(). Hàm này sẽ cho các số ngẫu nhiên từ 0 đến 0,99999
Fig. 293
You will often need to create random numbers between a range of digits. In Fig. 294, you might want sales between 1 and 100 for each cell. Use a formula such as =RAND()*100 to return figures between 0 and 99.999. To return just integers, use the INT function to return the integer portion. =INT(RAND()*100) will return numbers such as these shown in Fig. 295.
Bạn sẽ luôn cần những số ngẫu nhiên trong phạm vi 1 số giá trị. Trong hình 294, bạn muốn có các con số từ 1 đến 100 cho mỗi ô. Dùng hàm Rand() sẽ chỉ cho các số thập phân từ 0 đến 0,99999. Muốn có số nguyên, bạn dùng hàm INT(): Công thức = INT(RAND()*100) sẽ đáp ứng yêu cầu của bạn.
Fig. 294
Fig. 295
Note that the formula is returning values from 0 to 99. If you really want values from 1 to 100, then use =INT(RAND()*100)+1. See Fig. 296.
Nhận xét rằng do sử dụng hàm INT() nên bạn sẽ chỉ có các số từ 0 đến 99. Nếu bạn muốn các số từ 1 đến 100, hãy cộng 1 vào công thức: =INT(RAND()*100)+1
Fig. 296 Additional Information: Every time that you hit F9 or enter a new value in the worksheet, the random numbers will change. You might want to change the formulas to values to freeze the random numbers. Select the range of random numbers. Use Edit – Copy, and then use Edit – Paste Special – Values to convert formulas to numbers. Summary: The RAND function can be used to return random numbers.
Nói thêm: Mỗi lần nhấn F9 hay mỗi khi có thay đổi thêm dữ liệu vào bảng tính, các số ngẫu nhiên sẽ bị thay đổi. Bạn sẽ muốn cố định các số ngẫu nhiên sau khi tạo ra. Vậy hãy chuyển chúng thành giá trị bằng copy- Paste value. Tóm tắt: Hàm RAND được sử dụng để tạo các số ngẫu nhiên.
CREATE RANDOM NUMBERS TO SEQUENCE A CLASS OF STUDENTS TẠO SỐ NGẪU NHIÊN ĐỂ SẮP XẾP CÁC HỌC SINH TRONG LỚP.
Problem: The students in your class must present an oral book report. Rather than have them go alphabetically, as shown in Fig. 297, you want to randomly sequence them. Vấn đề: Các gọc sinh trong lớp bạn phải làm 1 bài tập thuyết trình về sách. Thay vì sắp xếp danh sách lớp theo thứ tự ABC, bạn muốn sắp theo 1 thứ tự ngẫu nhiên.
Fig. 297
Strategy: Use the RAND function in column B and then sort by column B. Follow these steps. 1) Enter a heading of Rand in B1. 2) Select cells B2:B22. Enter =RAND() and press Ctrl+Enter. Each student will be assigned a random decimal between 0 and 1, as shown in Fig. 298. 3) Select a single cell in column B and choose the AZ button on the Standard toolbar. The list will be sorted in a random sequence. Gotcha: The data is sorted and then column B is recalculated. It will appear that the new figures in column B are not in ascending order, as shown in Fig. 299. This is because the sort was based on the previous values in column B. 4) You can now delete column B. Biện pháp: Dùng hàm RAND() trong cột B và sắp xếp theo cột này: - Cho 1 tiêu đề cột là Rand ở ô B1. - Chọn khối B2:B22, Gõ công thức =RAND() và nhấn Ctrl+Enter. Mỗi tên học sinh sẽ nhận 1 số ngẫu nhiên từ 0 đến 1 như hình 298. - Chọn 1 ô trong cột B và nhấn nút AZ trên thanh công cụ. Danh sách sẽ được sắp xếp theo trình tự ngẫu nhiên. Ghi chú: Dữ liệu được sắp xếp đồng thời cột B được tính toán lại. Nhìn qua thì thấy rằng cột B chẳng hề được sắp cếp tăng dần. Bởi vì sự sắp xếp đựa vào giá trị trước đó của cột B. - Bây giờ bạn có thể xoá cột B.
Fig. 298
Fig. 299
Additional Information: If you want to fill column B with sequential numbers, then enter 1 in B2 and 2 in B3. Highlight these two cells and double-click the Fill handle to extend the series to your entire dataset. Summary: The RAND function can be used to provide a column of data to fairly and randomly sort a list of students. Nói thêm: Nếu bạn muốn điền vào cột B với các số thứ tự từ 1 đến hết, hãy gõ 1 vào B2, 2 vào B3, chọn 2 ô B2:B3, rồi doubled-click cái Fill Handle.
Tóm tắt: Hàm RAND() có thể dùng để tạo 1 cột số ngẫu nhiên nhằm sắp xếp 1 danh sách theo thứ tự này.
Problem: Your Monopoly set is missing the dice. Create a spreadsheet that will simulate randomly rolling two dice. Strategy: Use the RAND function and clever spreadsheet formatting to simulate two or more dice. Follow these steps.
Vấn đề: 1 bộ trò chơi xúc xắc của bạn bị thiếu con xúc xắc. tạo 1 bảng tính có thể giả lập 2 con xúc xắc đổ cùng lúc. Biện pháp: Dùng hàm RAND() và 1 bảng tính định dạng giả lập 2 hoặc nhiều hột xúc xắc. làm theo các bước sau:
1) Select cell A2. From the menu, select Format – Row – Height. Set the row height to 41, as shown in Fig. 300. 2) In cell B2, enter the formula =INT(RAND()*6)+1, as shown in Fig. 301. - Chọn ô A2, mở menu Format Row Height. Gán chiều cao dòng là 41. - Ở ô B2, gõ công thức =INT(RAND()*6)+1
Fig. 300
Fig. 301
3) With cell B2 selected, hit Ctrl+1 to open the Format Cells dialog. On the Alignment tab, change both Text Alignment fields to Center, as shown in Fig. 302. 4) On the Font tab, choose Bold and 24 point, as shown in Fig. 303.
- Khi B2 đang được chọn, nhấn phím Ctrl+1 để mở hộp thoại Format Cell. Trong tab Alignment, đổi cả 2 ô Alignment thành Center như hình 302. - Trong tab Font, Chọn chữ đậm và cỡ chữ 24.
Fig. 302
Fig. 303
5) On the Border tab, first choose the thick border style on the left side of the dialog, as shown in Fig. 304. 6) Next, select the Outline Preset in the top of the dialog, as shown in Fig. 305. 7) Choose OK to close the Format Cells dialog. 8) Copy cell B2 and paste it to cell D2. As shown in Fig. 306, you will have the two dice required for monopoly.
- Trong tab Border, chọn đường kẻ đậm trong ô bên trái hộp thoại - Chọn nút Outline ở phía trên hộp thoại. - nhấn OK để chấp nhận việc định dạng. - Copy ô B2 và Paste vào ô D2. Như hình 306, bạn đã có 2 con xúc xắc để chơi trò chơi của bạn.
Fig. 304 Fig. 305
Fig. 306
Results: You will have one die in cell B2 and another in cell D2. Every time that you hit the F9 key, you will have a new roll of the dice. Summary: Use the RAND function and worksheet formatting to create a dice simulation.
Kết quả: Bạn đã có 1 hột xúc xắc ở ô B2 và 1 hột khác ở ô D2. Mỗi khi bạn nhấn F9, bạn sẽ có kết quả như 1 lần đổ hột. Tóm tắt: Dùng hàm RAND và bảng tính để tạo hột xúc xắc.
Problem: Your kids are bored waiting somewhere. Pull out your laptop and build a quick Bunco game to keep them occupied. Strategy: Without using macros, you will have to use the manual calculation mode and a single iteration mode to allow the worksheet to keep track of scoring. Follow these steps. 1) From the menu, select Tools – Options – Calculation. 2) In the Calculation section, choose Manual. Uncheck the option to Recalculate Before Save. In the Iteration section, choose the Iteration checkbox. Change both parameters to 1, as shown in Fig. 307.
Vấn đề: Đám trẻ nhà bạn đang chán và lẩn quẩn đâu đó. Hãy mở máy tính lên và tạo 1 trò Bunco cho chúng có việc làm. Cách làm: Không dùng Macro, bạn dùng phương thức tính toán Manual và phương thức tính lặp 1 lần để bảng tính Excel tính điểm. Làm theo các bước sau: 1. Mở Menu Tools –Option – Calculation 2. Trong phần Calculation, chọn Manual. Bỏ chọn Recalculate before Save. Trong phần Iteration, đánh dấu chọn vào Iteration. Chỉnh tham số Iteration thành 1 và Maximum change thành 1
Fig. 307
Manual calculation with iteration will allow spreadsheets with circular references to actually work. Every time that you hit the F9 key to calculate the worksheet, the cell with a circular reference will recalculate on the basis of the previous value of the cell. 3) Follow the steps in the previous chapter, Play Dice Games with Excel, to build three dice in B2, D2, and E2, as shown in Fig. 308.
Manual Calculation với 1 vòng lặp sẽ làm cho bảng tính có công thức tham chiếu vòng có thể tính toán. Mỡi lần bạn nhấn F9, bảng tính sẽ tính lại đựa vào giá trị trước đó của ô bị tham chiếu lòng vòng. 3. Làm lại như bài trước để tạo 3 con xúc xắc ở ô B2, D2 và E2.
Fig. 308
4) In cell C5 enter the label “Round”. In cell C6, enter the label “This Score”. In cell C7, enter the label “Total Score”. Right-justify each label using the button on the Standard toolbar, as shown in Fig. 309. Bunco is played in six rounds. During your turn, you roll three dice. For each dice that matches the Round #, you get 1 point. If you happen to roll three matching dice that do not match the round number, you get 5 points. If you roll three dice that match the round number, you get 21 points. On any turn where you get no points, you then advance to the next round.
4. Ở ô C5 gõ Round (vòng chơi). Ở ô C6, gõ vào “This Score” (số điểm lần này). Ở ô C7, gõ “Total Score” (tổng điểm). Canh lề phải cho các ô có chữ này bằng nút nhấn trên thanh công cụ. Bunco sẽ chơi trong 6 lượt. Đến phiên bạn chơi, bạn sẽ đổ 3 hột xúc xắc. Với mỗi hạt hiện số bằng với số thứ tự lượt chơi, bạn được 1 điểm. Nếu 3 hột có số bằng nhau, bạn được 5 điểm. Nếu cả 3 hột bằng nhau và bằng số thứ tự vòng chơi, bạn được 21 điểm. Nếu vòng nào bạn không có điểm, bạn sẽ được vào vòng sau.
Fig. 309
5) For now, enter the number 1 in D5. The formula for the score in D6 is fairly complex. You can break down the rules as follows: • If B2, D2, and F2 all match, and they match the round number in D5, then you get 21 points. • Otherwise, if B2, D2, and F2 all match then you get 5 points. • Otherwise, add one point if B2 matches D5. Add one point if D2 matches D5. Add one point if F2 matches D5. You will have to nest a couple of IF functions. The syntax of the IF function is: =IF(Some Test, Value if True, Value if False). If the test portions of the IF statements have several conditions that must be met, then you should include all of the conditions as arguments of the AND function.
5. Bây giờ gõ 1 vào ô D5. Công thức ô D6 khá phức tạp. bạn phải phân tích các tình huống sau: - Nếu B2, D2, F2 bằng nhau và bằng D5, bạn có 21 điểm, - Nếu B2, D2, F2 bằng nhau bạn có 5 điểm. - Ngoài ra, thêm 1 điểm nếu B2 bằng D5, thêm 1 điểm nếu D2 bằng D5, thêm 1 điểm nếu F2 bằng D5. Bạn sẽ phải dùng 1 số vòng của hàm If. Cú pháp hàm IF là: =IF(Điều kiện kiểm tra, Giá trị nếu đúng, Giá trị nếu sai). Nếu điều kiện cần kiểm tra gồm nhiều điều kiện, phải dùng hàm AND gộp chúng lại với nhau.
Our first scoring rule is “If B2, D2, F2, and D5 all match, then 21 points”. The AND statement for this is =AND(B2=D2,D2=F2,F2=D5). The IF statement to give 21 points would be =IF(AND(B2=D2,D2= F2,F2=D5),21,___). The next scoring rule replaces the ___ in the third parameter of the IF function. This scoring rule says that if B2, D2, and F2 match, then you get 5 points. The AND statement would be AND(B2=D2,D2=F2). This portion of the IF statement would be =IF(AND(B2=D2,D2=F2), 5,___). The final scoring rule says that for any of the dice that match the round number, you get 1 point. This actually requires three new IF functions, added together: IF(B2=D5,1,0)+IF(D2=D5,1,0)+IF(F 2=D5,1,0). 6) Nesting all of these together, you get the following formula. Enter it in cell D6. =IF(AND(B2=D2,D2=F2,B2=D5),21,IF(AND(B2=D2,D2=F2),5,IF(B2=D5,1,0)+IF(D2=D5,1,0)+IF(F2=D5,1,0)))
- Luật thứ nhất của trò chơi là: Nếu B2, D2, F2 bằng nhau và bằng D5, bạn có 21 điểm. Hàm AND sẽ dùng là: =AND(B2=D2,D2=F2,F2=D5). Hàm IF cho điều kiện này là: =IF(AND(B2=D2,D2=F2,F2=D5);21;xxx). Luật chơi thứ 2 sẽ điền vào chỗ xxx còn lại là tham số thứ 3 trong cấu trúc hàm IF. - Điều luật thứ 2 là: nếu B2, D2, F2 bằng nhau bạn có 5 điểm. Hàm AND lại được dùng: AND(B2=D2,D2=F2). Phần này củ điều luật sẽ là 1 hàm IF nữa: =IF(AND(B2=D2,D2=F2), 5,xxx) - Luật thứ 3 của trò chơi là với mỗi hột xúc xắc có số bằng số thứ tự vòng chơi, bạn sẽ được cộng 1 điểm. Điều này đòi hỏi phải có 3 cái If cộng với nhau: IF(B2=D5,1,0)+IF(D2=D5,1,0)+IF(F 2=D5,1,0)
6. Kết nối những điều kiện trên với nhau, ta có công thức của ô D6: =IF(AND(B2=D2,D2=F2,B2=D5),21,IF(AND(B2=D2,D2=F2),5,IF(B2=D5,1,0)+IF(D2=D5,1,0)+IF(F2=D5,1,0)))
