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

[ptm0412]

PLAY BUNCO WITH EXCEL
CHƠI TRÒ BUNCO BẰNG EXCEL tiếp theo

7) Hit the F9 key until you get three dice that match, as shown in Fig. 310. Check that the scoring for This Score works as planned.
8) Hit F9 again until you have a setting where This Score is 0. This is the tricky part. Before you can enter the circular reference formula, you need to enter the initial value for the cells in the worksheet.
9) In cell D5, enter a –1.
10) In cell D7, enter the number 0, as shown in Fig. 311.

7. Nhấn F9 cho tới khi bạn có 3 hột xúc xắc bằng nhau, kiểm tra sự chính xác của công thức của D6.
8. Nhấn F9 cho tới khi D6 = 0. Cái này đòi hỏi phải khéo. Trước khi tạo 1 công thức lặp, bạn phải cho vào 1 giá trị ban đầu.
9. Gõ -1 vào D5
10. Gõ 0 vào D7

Fig. 310

Fig. 311

11) In cell D7, enter the formula =D6+D7. This formula will add the score from This Score to the Total Score.
12) In cell D5, enter the formula =IF(D6=0,1+D5,D5). This gives you a formula that will increment the round number any time that the dice produce zero points. When you enter this formula, because D6
is initially 0, the –1 will change to a 0.
13) Immediately Save and Close the game after entering these formulas. It is important that Excel NOT CALCULATE before saving. That is why you unchecked the Calculate Before Save option in Fig. 307.

11. Ở ô D7, sửa lại công thức thành =D6+D7. Công thức này nhằm cộng thêm điểm vòng chơi này vào tổng điểm đã có trước đó.
12. Ở ô D5, gõ công thức =IF(D6=0; D5+1;D5). Công thức này nhằm đưa bạn vào vòng chơi kế tiếp nếu điểm vòng này là 0. Ngay khi nhấn Enter, vì giá trị cũ là -1 nên đổi thành 0
13. Ngay lập tức Save và đóng file này lại. Điều quan trọng là Excel phải không tính toán lại trước khi lưu. Cho nên ta đã phải bỏ dấu chọn Calculate Before Save ở hình 307.

Fig 312

Result: To play a round of Bunco, hit the F9 key to roll the three dice, as shown in Fig. 312. When the game is complete, close the worksheet WITHOUT SAVING. Open the worksheet to start a new game.
Gotcha: Before entering the numbers in D5, make sure that the dice do not all match.
Summary: Using manual calculation mode, you can create simple dice games.

Kết quả: Để chơi trò này, nhấn F9 để thảy 3 hột xúc xắc, Khi trò chơi kết thúc, đóng file lại mà không lưu. Mở file lên lại để chơi lần sau.
Ghi chú: Khi gõ số vào D5, phải đảm bảo 3 con xúc xắc không được giống nhau.
Tóm tắt: Dùng phương thức tính toán Manual, bạn có thể tạo 1 trò chơi xúc xắc đơn giản.

 

[ptm0412]

PLAY CRAPS WITH EXCEL
CHƠI TRÒ CRAPS

Problem: How can you play a Craps simulation in Excel?
Strategy: Craps is a game played with two dice. On your first roll, if you roll a 7 or 11, you win. If you roll a 2, 3, or 12, you lose. If you roll a 4, 5, 6, 8, 9, or 10, then this number becomes your point. On subsequent rolls, you must roll your point number again before you roll a 7. If you roll your point, then you win. If you roll anything else, then you lose. Set up a worksheet with manual calculation and one iteration. The worksheet will require several circular references. Use the Random Dice example to generate two dice in cells B2 and D3. Use a series of IF formulas to keep track if you win or lose.
1) In columns B and C, enter the text values shown in Fig. 313.

Vấn đề: Làm thế nào để chơi trò Craps trong Excel?
Cách làm: Craps là 1 trò chơi với 2 hột xúc xắc. Nếu bạn dổ hột lần thứ nhất được 7 hay 11, bạn thắng. Nếu bạn đổ được 2, 3, 12, bạn thua. Nếu bạn đổ được các số 4, 5, 6, 8, 9, hoặc 10, đó là số điểm của bạn.
Trong lần đổ sau, bạn phải có được đúng số điểm bạn đang có trước khi đạt 7. Nếu đổ được bằng số điểm bạn đang có, bạn thắng. nếu đổ ra số khác, bạn thua. Tạo 1 bảng tính với phương thức tính Manual và số lần lặp là 1.

1. Ở cột B và C, gõ vào các tiêu đề như sau:

Fig. 313

2) In cell D5, enter the value TRUE until the rest of the formulas are set up. In the rest of column D, enter the formulas as shown in Fig. 314.
3) Finally, in cell D5, enter the circular reference =D18, as shown in Fig. 315.

2. Ở ô D5, gõ giá trị “TRUE” cho đến khi mọi công thức đã được xác lập. Ở các ô của cột D, gõ các công thức như hình 314.
3. Trở lại ô D5, sửa lạ 1 công thức lặp =D18 như hình 315

Fig. 314

Fig. 315

Cell D5 is a circular reference that keeps track of whether this is the first roll in each game. If the value in D5 is True, then the rules in D10 and D11 will determine if you win or lose on the first roll of the game.
The circular reference in D6 will keep track of the roll number. Once this cell is above 1, then the rules in D15 and D16 will keep watch to see if you roll a 7 or your point.
Rows 18 and 19 keep track if this dice roll ended the current game and the result of that game. Two circular formulas in D21 and D22 will keep track of your lifetime wins and losses in the game.
To play, press the F9 key to roll the dice.
Summary: Using intentional circular references, you can design simple games like Craps.

Ô D5 là 1 ô có công thức bị lặp nhằm xem có phải đây là lần đổ đầu tiên của mỗi lượt chơi hay không. Nếu ô D5 có giá trị True, luật chơi ở ô D10 và D11 sẽ kiểm tra xem bạn thắng hay thua ở lần đổ đầu tiên.
Công thức lặp ở ô D6 nhằm đếm số lần đổ. nếu ô này có giá trị lớn hơn 1, luật chơi ở ô 15 và 16 sẽ kiểm tra xem bạn đổ được 7 hay là số điểm của bạn.
Công thức ô D18 và D19 kiểm tra kết quả bạn đổ có kết thúc cuộc chơi và kết quả cuộc chơi. 2 công thức lặp ở D21 và D22 thống kê số lần thắng, thua của bạn.
Để chơi, nhấn F9 để đổ hột.
Tóm tắt: Dùng phương thức tính toán Manual, bạn có thể tạo 1 trò chơi xúc xắc đơn giản.

 

[ptm0412]

CREATE RANDOM LETTERS
TẠO CÁC CHỮ CÁI NGẪU NHIÊN

Problem: Instead of random numbers, you need to create random letters.
Strategy: The capital letter A is character number 65. You can use the =CHAR(65) function to produce an A. Thus, to produce a random character between A and Z, you would want to produce a random number
between 65 and 90 and use it as the argument to the CHAR function. To generate random numbers between 65 and 90, follow this logic. First, RAND()*26 will generate numbers between 0 and 25.9999. Taking the integer portion of that function with =INT(RAND()*26) will generate random integers between 0 and 25. Because you want the first character to be an A, you will have to add 65 to the preceding formula.
Tip If you forget that an “A” is character 65, you can always enter =CODE(“A”) in any cell to learn that the letter “A” is character 65.
As shown in Fig. 316, the formula to use is =CHAR(INT(RAND()*26)+ 65).

Vấn đề: Thay vì 1 số ngẫu nhiên, bạn cần 1 bộ chữ cái ngẫu nhiên.
Giảu quyết: Chữ A hoa có mã số 65, bạn có thể dùng hàm Char(65) để thể hiện chữ A. Vậy để có 1 chữ cái ngẫi nhiên trong khoảng từ A đến Z, bạn sẽ cần một số ngẫu nhiên từ 65 đến 90 và dùng làm đối số cho hàm CHAR. Để có số ngẫu nhiên theo ý này, hãy coi lý luận sau: Trước tiên, hàm RAND()*26 sẽ cho các số ngẫu nhiên trong khoảng từ 0 đến 25,9999. Lấy phần nguyên của số này, bằng hàm INT(RAND()*26 sẽ cho số ngẫu nhiên nguyên trong khoảng 0 và 25. Bởi bạn muốn chữ cái thứ nhất là A, bạn phải cộng 65 vào công thức trên.
Nếu bạn không biết A có mã là bao nhiêu, hãy gõ công thức CODE(“A”) tại 1 cell bất kỳ, bạn sẽ có 65.
Công thức cuối cùng là: =INT(RAND()*26)+65

Fig. 316

Additional Details: In many places in this book, I use customers in the form of ABC, Inc. In order to generate these names, I start with a random letter between A and X. Follow these steps.
1) In cell G2, enter the formula: =INT(RAND()*24)+65.
2) In cell H2, enter the formula: =CHAR(G2)&CHAR(G2+1)&CHAR( G2+2).
3) In a blank area of the worksheet, enter a table with some different company name suffixes.
My table is in K2:K8. Note that each suffix either begins with a comma or a space, as shown in Fig. 317.

Nói thêm: Trong nhiều bài trong sách này, tôi thường dùng các tên khách hàng trong các thí dụ như là ABC,Inc. Để có các tên này tôi thực hiện từ 1 chữ cái ngẫu nhiên từ A đến X (không phải đến Z):
- Ở ô G2, gõ công thức: =INT(RAND()*24)+65
- Ở ô H2 gõ công thức: = CHAR(G2)&CHAR(G2+1)&CHAR(G2+2)
- Ở 1 cùng trắng của bảng tính, tạo 1 bảng gồm các phần đuôi của tên công ty.
bảng này của tôi nằm ở K2:K8. Để ý rằng mỗi cái đuôi tên như vậy bắt đầu bằng 1 dấu phẩy, hoặc 1 khoảng trắng.

Fig. 317

There is a function called INDEX, which has three parameters. You might say: =INDEX(SomeRange, WhichRow, WhichColumn).
In this case, you always want SomeRange to be $K$2:$K$8. You always want WhichColumn to be 1. You want WhichRow to be a random integer between 1 and 7.
4) In cell I2, use this formula: =H2&INDEX(K$2:K$8,INT(RAND()*7) +1,1).
5) Copy G2, H2, and I2 down to as many rows as you need data.
Result: As shown in Fig. 318, you have a column of random company names. You can safely use these in a book without infringing any trademarks.
Summary: The RAND() function can generate random letters as well as numbers when it is used in conjunction with the CHAR function.

Có 1 hàm dò tìm là hàm INDEX, hàm này có 3 tham số. bạn có cấu trúc hàm: INDEX(vùng dữ liệu, Số thứ tự dòng, Số thứ tự cột)
Trong trường hợp này, vùng tìm kiếm là $K$2:$K$8. Bạn sẽ thấy số thứ tự cột là 1, số thứ tự dòng là 1 số ngẫu nhiên từ 1đến 7.
- Ở ô I3, dùng công thức sau: =H2&INDEX($K$2:$K$8,INT(RAND()*7)+1,1).
- sao chép công thứcxuống các ô bên dưới.
Kết quả: Bạn có 1 cột gồm các tên ngẫu nhiên các công ty. Bạn có thể an tâm sử dụng các tên công ty này mà không sợ vi phạm thương hiệu.
Tóm tắt: Hàm RAND có thể tạo những ký tự ngẫu nhiên cũng như các số ngẫu nhiên khi dùng kết hợp nới hàm CHAR

Fig. 318

 

[ptm0412]

CONVERT NUMBERS TO TEXT
CHUYỂN ĐỔI ĐỊNH DẠNG SỐ THÀNH DẠNG TEXT

Problem: You have a field that may contain numbers or text. You need the numeric entries to sort with the text entries. Instead, Excel always sorts the numeric entries to the top of the list, followed by the text entries, as shown in Fig. 319.
Strategy: This is a rare case where you need to convert numeric entries to text entries.
If you were building this spreadsheet from scratch, you could have selected column A, and from the Format – Cells dialog, you could have formatted the column as Text, as shown in Fig. 320. This would allow all
future entries to automatically be converted to text. However, converting cells to have a text format does NOT retroactively convert numbers to text.

Vấn đề: bạn có 1 cột dữ liệu có thể chứa cả số và chữ. bạn muốn các số sẽ được sắp xếp thứ tự như là các dữ lệu kiểu text. Excel luôn sắp thứ tự các con số lên trên cùng như hình 319
Biện pháp: Đây là 1 trong những trường hợp hiếm hoimà bạn phải làm.
Nếu bạn thực hiện bảng tính từ đầu, bạn có thể chọn cột A, sau đó trong hộp thoại Format Cell, bạn có thể chọn định dạng Text. Cách này làm cho mọi dữ liệu gõ vào đều hiểu và bị đổi thành text. Dù vậy, cách này không có hiệu lực với các dữ liệu đã nhập vào từ trước.

Fig. 319

Fig. 320

Another option would be to edit each cell that contains a number. Select the cell. Hit F2 to edit the cell. Hit Home to move to the beginning of the cell. Type an apostrophe. Hit Enter (or the Down Arrow) to move to the next cell. This could get very tedious if you have more than a few cells to change.
The good news is, there are two easy methods for converting all of the entries in a column to text.
Method 1: Select all of the data in a column. From the menu, select Data – Text to Columns. In Step 1 of the Wizard, indicate that your data is fixed width, as shown in Fig. 321.
In Step 2 of the Wizard, you may or may not have any vertical lines drawn in the Data Preview section, as shown in Fig. 322. If you do, double-click to remove them.
In Step 3 of the Wizard, choose Text as the Column data format, as shown in Fig. 323.

Một cách khác để sửa chữa những ô chứa dữ liệu số: Chọn ô đó. Nhấn F2 để vào Edit Mode. Nhấn Home để về đầu chuỗi số. Gõ vào 1 dấu nháy đơn. Cách này rất là chán nếu bạn có nhiều ô dữ liệu dạng này.

Cách khác: Chọn mọi ô trong cột. Mở Menu Data – Text to Columns.
Trong bước 1 của Wizard, chọn vào Fixed Width.
Trong bước 2, bạn sẽ có thể có hoặc không có lằn kẻ dọc phân cách trường trong vùng xem trước. nếu có, hãy click đôi vào nó để xoá bỏ nó đi.
Ở bước 3, trong ô định dạng dữ liệu cột (Column Data Format) chọn định dạng kiểu text.

Fig. 321

Fig. 322

Fig. 323

After you choose Finish, the column will be converted to text.

Sau khi nhấn Finish, nguyên cột của bạn sẽ bị đổi thành dạng text.


Gotcha: You cannot sort using the AZ button in this case. You must select Sort from the Data menu. After you choose OK to sort, you will get the Sort Warning dialog shown in Fig. 324. Choose to Sort Numbers and Numbers Stored as Text Separately.

Fig. 324

Alternate Strategy: You could also insert a temporary column with the following formula: =TEXT(A2,“@”).
Summary: Use Data – Text to Columns to convert a column to text.

Ghi chú: Bạn không thể dùng nút AZ trên thanh công cụ để sort. Bạn phải vào Menu Data – Sort, bạn sẽ thấy thông báo cảnh báo như hình 324. Chọn Sort Numbers and Numbers Stored as Text Separately.

Cách khác: Chèn vào 1 cột tạm và gõ vào đó công thức =TEXT(A2,”@”)




Tóm tắt: Dùng Data – Text to Columns để chuyển số thành định dạng Text.