COPY A FORMULA THAT CONTAINS RELATIVE REFERENCES SAO CHÉP CÔNG THỨC CÓ CHỨA THAM CHIẾU Problem: You have 5,000 rows of data. After entering a formula to calculate Gross Profit Percent for the first row, as shown in Fig. 167, how do you copy the formula down to other rows? Vấn đề: Bạn có 5.000 dòng dữ liệu. Sau khi tạo lập công thức tính tỷ lệ lãi gộp cho dòng thứ nhất, như hình 167, Làm sao bạn sao chép côngthức đó xuống cho dòng dưới?
Fig 167
Strategy: All of the cell references in the formula are known as relative references. The amazing thing about Excel is that when you copy a formula, all of the relative cell references are automatically adjusted. If you copy a formula from row 2 down to row 3, as shown in Fig. 168, then every reference pointing at row 2 will change to point at row 3. Giải quyết: mọi địa chỉ ô tham chiếu trong công thức được gọi là các tham chiếu tương đối. Sự tuyệt vời là khi bạn copy 1 công thức, các ô tham chiếu liên quan sẽ được Excel tự động điều chỉnh. Nếu bạn sao chép công thức dòng 2 xuống dòng dưới, như hình 168, mọi sự tham chiếu từ dòng 2 sẽ được đổi xuống dòng 3.
Fig. 168
So, the solution to the problem is simply to copy the formula down to all the other rows. A shortcut for doing this is to select the cell and then double-click the Fill handle to copy the formula down to all rows with values in the adjacent column. Vậy giải pháp cho vấn đề của bạn là đơn giản copy côngthức và paste xuống các dòng bên dưới. Một cách làm tắt là chọn ô cần copy rồi double-click vào cái dấu Fill Handle ở góc dưới bên phải ô, để sao chép công thức xuống mọi dòng có giá trị trong cột liền kề. Additional Details: Relative references will move in all four directions. In Fig. 169, if you copy the formula in cell F7 to E6, the referenced cell will change from D3 to C2. Nói thêm: Sự tham chiếu tương đối sẽ dịch chuyển ra cả 4 phía. Trong hình 169, nếu bạn sao chép công thức từ F7 đến E6, ô tham chiếu sẽ đổi từ D3 đến C2
Fig. 169 In Fig. 170, you can see how the formula copied from F7 to E6:G8 will change. Trong hình 170, bạn sẽ thấy công thức thay đổi như thế nào khi sao chép từ F7 đến dãy E6:G8
Fig. 170 was shot in Show Formula mode. To enter Show Formula mode, hit Ctrl+~. To toggle back to regular mode, hit Ctrl+~ again. Hình 170 chụp màn hình trong tình trạng Show Formula. Để chuyển sang tình trạng này, nhấn Ctrl+~. Để trở lại tình trạng cũ nhấn Ctrl+~ lần nữa.
Gotcha: It is possible to copy a formula so that it will point to a cell that does not exist. As shown in Fig. 171, what would happen if you copied C4 to B3? Có thể sao chép công thức sao cho nó tham chiếu đến 1 ô không tồn tại. Hình 171 cho thấy cái gì sẽ xảy ra khi sao chép công thức từ C4 đến B3.
Fig. 171
The reference to A1 would have to point to the cell one row above and one column to the left of A1. This cell does not exist, so Excel will return a #REF error, as shown in Fig. 172. Sự tham chiếu đến ô A1 lẽ ra phải đổi đến 1 ô trên A1 1 dòng và qua trái 1 cột. Ô này không tồn tại và Excel sẽ thể hiện bằng báo lỗi #REF như hình 172
Fig. 172
Summary: The miracle of Excel is that you can enter a formula in one place and copy it to many other places and it will still work. This is because a regular cell reference, such as B1, is a relative reference. Tóm tắt: Sự diệu kỳ của Excel cho phép bạn Tạo 1 công thức tại 1 ô và sao chép đến nhiều ô khác mà vẫn có giá trị đúng. Bởi vì 1 ô tham chiếu thông thường như B1, là 1 tham chiếu tương đối.
COPY A FORMULA WHILE KEEPING ONE REFERENCE FIXED SAO CHÉP CÔNG THỨC MÀ GIỮ NGUYÊN THAM CHIẾU (THAM CHIẾU TUYỆT ĐỐI) Problem: You have 5,000 rows of data. As shown in Fig. 173, each row contains a quantity and the unit price. The sales tax rate for all orders is shown in cell C1. After entering a formula to calculate the total with sales tax in the first row, how do you copy the formula down to other rows? Vấn đề: Bạn có 5.000 dòng dữ liệu. Như hình 173, mỗi dòng có 1 ô số lượng và 1 ô đơn giá. Thuế suất thuế Doanh thu áp dụng cho mọi đơn hàng nằm ở ô C1. Sau khi tạo côngthức tính thuế phải nộp cho dòng thứ nhất, làm sao sao chép công thức xuống các dòng bên dưới?
Fig. 173
If you copy the formula in F4 to F5, you get an invalid result, as shown in Fig. 174. nếu bạn sao chép công thức từ F4 xuống F5, bạn sẽ có kết quả sai như hình 174
Fig. 174
Look at the formula in the formula bar in Fig. 174. As you copied the formula, the references to D4 and E4 changed as expected. However, the reference to C1 moved to C2. You need to find a way to copy this formula and always have the formula reference C1. Frankly, this is the most important technique in the entire book. I once had a manager who would enter every formula by hand in the entire dataset. I didn’t have the heart to tell him there was an easier way. Xem công thức trong thanh công thức của hình 174. khi bạn sao chép công thức, sự tham chiếu đến D4 và E4 thay đổi thành D5 và E5 như mong muốn. nhưng tham chiếu đến C1 cũng bị dịch chuyển xuống C2. Bạn phải tìm 1 cách nào đó để sao chép công thức mà giữ nguyên tham chiếu đến ô C1.
Strategy: You need to indicate to Excel that the reference to C1 in the formula is Absolute. Do this by inserting a dollar sign before the C and before the 1 in the formula. The formula in F4 would change to =ROUND((D4*E4)*$C$1,2). As you copy this formula down to other rows in your dataset, the portion that refers to $C$1 will continue to point at $C$1, as shown in Fig. 175. Giải quyết: Bạn cần chỉ ra cho Excel biết tham chiếu đến ô C1 là tham chiếu tuyệt đối. Bạn sẽ làm bằng cách gắn ký hiệu $ vào trước chữ C và trước chữ số 1. Công thức ô F4 sẽ là: =ROUND((D4*E4)*$C$1,2). Khi bạn sao chép công thức này đến những ô bên dưới, phần công thức tham chiếu đến $C$1 sẽ tiếp tục tham chiếu đến $C$1 như hình 175.
Fig. 175
Additional Details: See the next chapter to understand the effect of using just one dollar sign in a reference instead of two. Read “Simplify Entry of Dollar Signs in Formulas” a few chapters after that to learn a cool shortcut for entering the dollar signs automatically. Nói thêm: Coi phần tiếp theo để hiểu tác dụng của việc dùng chỉ 1 dấu $ thay vì 2. Đọc phần Đơn giản là gắn ký hiệu $ vào công thức, để biết phím tắt để gắn ký hiệu $ 1 cách tự động.
Summary: Entering dollar signs in a reference will lock the reference and make it absolute. No matter where you copy the formula, it will continue to point to the original cell. Tóm tắt: Thêm vào tham chiếu ký tự $ sẽ cố định tham chiếu và tham chiếu trở thành tuyệt đối. Dù cho bạn sao chép công thức đi đâu, nó cũng sẽ chỉ đến ô tham chiếu ban đầu.
CREATE A MULTIPLICATION TABLE TẠO 1 BẢNG NHÂN DÒNG VÀ CỘT Simplify Entry of Dollar Signs in Formulas Đơn giản là gắn ký hiệu $ vào công thức
Problem: Create a multiplication table to help your kids in school. In Fig. 176, you want to enter a single formula in cell B2 that can be copied to the entire table. Vấn đề: Tạo 1 bảng nhân để giúp con bạn học bài ở trường. Trong hình 176, bạn muốn tạo 1 công thức ở ô B2 có thể sao chép cho cả bảng tính nhân.
Fig. 176
Strategy: In the last chapter, you learned how to use an absolute reference, such as $C$1, so that Excel would not change from column C or row 1 as it copied the formula. To create a multiplication table, you need to use a mixed reference. A mixed reference, such as $B1, will lock the formula to column B, while allowing the row to change. A mixed reference, such as B$1, will lock the row to row 1, while allowing the column to change. The formula that you need for the multiplication table is a formula that will multiply whatever is in row 1 above the cell by whatever is in column A to the left of the cell. To have a reference that always points to row 1, use something in the format of B$1. To have a reference that points to column A, use a reference in the format of $A2. 1) As shown in Fig. 177, the formula you want to enter in B2 is =$A2*B$1. Giải quyết: Ở phần trước, bạn đã biết cách sử dụng địa chỉ tuyệt đối như $C$1, để cho Excel không thay đổi dòng 1 và cột C tham chiếu trong công thức khi sao chép. Để tạo 1 bảng tính nhân, bạn cần 1 tham chiếu hỗn hợp. Một tham chiếu hỗn hợp như $B1, sẽ cố định tham chiếu của công thức vào cột B và cho phép số dòng (1) thay đổi. Một tham chiếu hỗn hợp khác: B$1, sẽ cố định tham chiếu vào dòng 1 và cho phép thay đổi tham chiếu cột. Công thức bạn cần cho bảng tính nhân là 1 công thức sẽ nhân bất cứ cái gì trong dòng 1 bên trên ô đó với cái gì đó trong cột A ben trái ô đó. Để có tham chiếu luôn luôn chỉ đến dòng 1, dùng B$1. Để có tham chiếu luôn luôn chỉ đến cột A, dùng $A2. 1. Như hình 177, công thức cho ô B2 là =$A2*B$1
Fig.177
2) Copy the formula in B2 to the entire range, and it will always properly multiply row 1 by column A as shown in Fig. 178. Sao chép công hức từ B2 đến tất cả các ô còn lại., bạn sẽ có kết quả của bảng tính nhân dòng 1 với cột A như hình 178.
Fig. 178
Summary: Using a single dollar sign in a cell reference will create a mixed reference. Only the row or column will be fixed as you copy the formula. Tóm tắt: Dùng dấu $ riêng lẻ trong việc tham chiếu đến các ô, sẽ tạo những tham chiếu hỗn hợp giữa tương đối và tuyệt đối. Chỉ có dòng hoặc cột bị cố định tham chiếu khi sao chép công thức.
CALCULATE A SALES COMMISSION TÍNH MỘT KHOẢN TIỀN HOA HỒNG Problem: The VP of Sales in your company dreamt up the most convoluted sales plan in the history of the world. Rather than just pay the reps a straight commission, this plan involves a base rate of 2 percent, bonuses based on the product sold, and the monthly profit sharing bonuses. For the spreadsheet shown in Fig. 179, using Relative, Mixed, and Absolute formulas, create a formula that can be copied to all rows and all months. Vấn đề: Bộphận kinh doanh trong công ty bạn mơ đến 1 kế hoạch mở rộng kinh doanh ra toàn thế giới. Thay cho việc chi trả tỷ lệ hoa hồng cố định, kế hoạch này đề ra 1 mức căn bản là 2%, cộng với tỷ lệ thưởng theo sản phẩm, và chia phần trăm lợi nhuận hàng tháng. Với bảng tính ở hình 179, dùng công thức với tham chiếu tương đối, tuyệt đối, và hỗn hợp để tạo công thức tính có thể sao chép đến các ô còn lại trong mọi dòng và cho mọi tháng.
Fig. 179
Strategy: This formula will contain all four reference types. While entering the first formula in H6, you will want to base the commission calculation on the January sales in E6. As you copy the formula from January to February, you will want the E6 reference to be able to change to F6. As you copy the formula down to other rows, you will want the E6 to change to E7, E8, etc. Thus, the E6 portion of the formula needs to be a relative reference and will have no dollar signs. You will multiply the sales times the base rate in B1. As you copy the formula to other months and rows, it always needs to point to B1. Thus, you need to use dollar signs to before the B and before the 1: $B$1. To incorporate the product bonus, you will need to multiply sales by the Product Rate in column C. All of the months in row 6 will have to refer to C6. All of the months in row 7 will have to refer to C7. Thus, you need a mixed reference where column C is locked. Use the address of $C6. Finally, the VP of Sales added the monthly profit sharing bonus. The entire commission calculation is multiplied by the bonus factor shown in row 1. The January commission calculation uses the factor in E1. The February factor is in F1. The March factor is in G1. In this case, you need to allow the formula to point to different columns but always to row.This requires a mixed reference of E$1. Now that you have the four components of the formula, you can enter this formula in E6, as shown in Fig. 180: =E6*($B$1+$C6)*E$1.
Giải quyết: Công thức này chứa cả 4 loại tham chiếu. Khi gõ công thức đầu tiên vào ô H6, bạn muốn căn cứ vào doanh thu tháng 01 trong ô E6. Khi copy công thức sang tháng 2, bạn muốn tham chiếu E6 sẽ chuyển sang F6. khi copy công thức xuống các dòng khác, bạn muốn E6 chuyển xuống E7, E8 … Vậy tham chiếu E6 phải là 1 tham chiếu tương đối và sẽ không có dấu $ nào. Bạn lại muốn nhân doanh thu với tỷ lệ chuẩn trong B1. Khi sao chép công thức qua tháng khác và dòng khác, tỷ lệ này phải không đổi. Vậy bạn phải dùng dấu $ để cố định cả dòng 1 và cột B của tham chiếu B1: $B$1. Khi tính đến mức thưởng thêm theo loại sản phẩm, bạn phải nhân doanh thu với tỷ lệ thưởng cho từng loại SP ở cột C. Doanh số của mọi tháng của mặt hàng ABC ở dòng 6 đều phải tham chiếu đến ô C6. Doanh số mọi tháng của mặt hàng DEF ở dòng 7 phải tham chiếu đến tỷ lệ ở ô C7. Vậy bạn phải có 1 tham chiếu hỗn hợp để cố định cột C và không cố định dòng 6: Địa chỉ tham chiếu là $C6. Cuối cùng, Huê hồng bán hàng mỗi tháng lại được cộng thêm khoản chia lợi nhuận thay đổi cho từng tháng. Tổng số huê hồng sẽ được nhân với tỷ lệ chia lợi nhuận trong dòng 1. Huê hồng tháng 2 sẽ nhân với tỷ lệ ở E1, Huê hồng tháng 2 nhân với tỷ lệ ở F1 … Trong trường hợp này, bạn cần tham chiếu đến dòng cố định và cột thay đổi. Ta sẽ cần 1 tham chiếu hỗn hợp E$1. Vậy cuồi cùng chúng ta có 1 công thức gồm 4 tham số, tham chiếu 4 loại: =E6*($B$1+$C6)*E$1
Fig.180
Result: As shown in Fig. 181, you have created one single formula that can be copied to all columns and rows of your dataset. Kết quả: Như hình 181, bạn đã tạo 1 công thức có thể sao chép đến mọi cột và mọi dòng dữ liệu.
Fig. 181
Summary: The concept of relative, absolute, and mixed references is one of the most important concepts in Excel. Being able to use the right reference will allow you to create a single formula that can be copied everywhere. Tóm tắt: Khái niệm về các tham chiếu tương đối, tuyệt đối và hỗn hợp là 1 trong những khái niệm quan trọng của Excel. Biết về nó bạn có thể tạo 1 công thức có thể sao chép đi khắp nơi.
SIMPLIFY ENTRY OF DOLLAR SIGNS IN FORMULAS MỘT CÁCH ĐƠN GIẢN ĐỂ CHÈN KÝ TỰ $ VÀO CÔNG THỨC. Problem: It is a pain to type the dollar signs in complex formulas such as the formula shown in Fig. 182. Vấn đề: Thật khổ sở khi phải gõ những ký tự $ vào đúng chỗ trong công thức phức tạp như hình 182
Fig. 182
Strategy: Use the F4 key as you are entering the formula. The F4 key will toggle a reference through the four possible reference types. As shown in Fig. 183, start to type the formula =E7*(B1. Biện pháp: Dùng phím F4 khi thiết lập công thức.Nhấn phím F4 sẽ luân chuyển loại tham chiếu qua 4 loại. Như hình 183, gõ 1 phần công thức: =E7*(B1
Fig. 183
Immediately after you type B1, hit the F4 key. Excel will insert both dollar signs in the B1 reference, as shown in Fig. 184. Ngay sau đó bạn nhấn F4. Excel sẽ chèn cả 2 dấu $ vào cả dòng và cột của B1
Fig. 184
As an illustration, hit the F4 key again. Excel changes from an absolute reference to a mixed reference, with the row portion of the reference locked, as shown in Fig. 185. Để tìm hiểu, nhấn F4 1 lần nữa. Excel sẽ dổi tham chiếu tuyệt đối thành tham chiếu hỗn hợp cố định dòng, tương đối cột.
Fig. 185
Hit the F4 key again. Excel changes to a mixed reference, with the column portion of the reference locked, as shown in Fig. 186. Nhấn lần nữa. Excel lại chuyển thành tham chiếu hỗn hợp, lần này là cố định cột, tương đối dòng.
Fig. 186
Hit the F4 key once more. Excel changes back to a relative reference, as shown in Fig. 187. Nhấn lần nữa. Excel lại chuyển trả lại tham chiếu tương đối.
Fig. 187
Here are the steps for entering the complex formula shown in Fig. 182. 1) Type =E7*(B1. 2) Hit the F4 key once. 3) Type +C7. 4) Hit the F4 key 3 times. Your formula will now appear as shown in Fig. 188. Các bước thực hiện công thức 182 - Gõ =E7*(B1 - Nhấn F4 1 lần - Gõ+ C7 - Nhấn F4 3 lần. B1 trở thành cố định cột, tương đối dòng.
Fig. 188 5) Type the parentheses, an asterisk for multiplication, and E1, as shown in Fig. 189. - Gõ dấu đóng ngoặc đơn, gõ dấu sao cho phép toán nhân, gõ E1
Fig. 189
6) Hit the F4 key twice to change E1 to a reference with the row locked, as shown in Fig. 190. Nhấn F4 2 lần để chuyển E1 thành cố định dòng, tương đối cột.
Fig. 190
7) Hit Ctrl+Enter to accept the formula without moving the cell pointer to the next cell, as shown in Fig. 191. - Nhấn Ctrl+Enter đển chấp nhận công thức mà không dời con trỏ ô.
SIMPLIFY ENTRY OF DOLLAR SIGNS IN FORMULAS MỘT CÁCH ĐƠN GIẢN ĐỂ CHÈN KÝ TỰ $ VÀO CÔNG THỨC. Tiếp theo
8) With the mouse, grab the Fill handle (the square dot in the lower right corner of the cell) and drag it to the right for two cells, as shown in Fig. 192. - Dùng chuột nắm vào nút Fill Handle, (chấm vuông đen nhỏ ở góc dưới bên trái ô) và kéo qua phải 2 ô
Fig. 192
This will copy the formula from January to the other two months, as shown in Fig. 193. Việc này sẽ sao chép công thức qua 2 cột từ cột tháng 01 đến 2 cột tháng 2 và 3.
Fig. 193
9) Double-click the Fill handle. This will copy the three cells down to all of the rows with data, as shown in Fig. 194. - Double click cái Fill Handle. Việc này sẽ sao chép 3 ô đang chọn xuống hết các dòng có dữ liệu.
Fig. 194 109 Additional Information:You might find mixed references confusing. As you work on building the first formula, you might know that you need to point to C7. Enter C7 in the formula and then use F4 to toggle between the various reference types. Say to yourself, “OK. There is a dollar sign before the C that will lock the column and let the row change is that what I need?”. As long as you say this to yourself without your lips moving, your officemates won’t think any less of you. Further Information: If you did not add the dollar signs as you typed the formula, you can still use the F4 trick later. Using the mouse, highlight the proper reference in the formula bar, as shown in Fig. 195. Nói thêm: Bạn có thể thấy tham chiếu hỗn hợp là khó hiểu. Khi bạn thực hành lập côngthức, bạn đã biết rằng bạn cần tham chiếu đến ô C7. Gõ C7 vào côngthức và nhấn F4 để chuyển dần đến loại tham chiếu bạn muốn. Hãy tự nói với chính mình: “Được rồi, chỉ có 1 dấu $ trước chữ C và nó sẽ cố định cột C không cho dịch chuyển cột, và cho phép dòng thay đổi khi sao chép công thức. Đó có phải là cái mình cần chưa?” Chừng nào bạn còn nói mà môi không mấp máy, các bạn đồng nghiệp trong văn phòng sẽ còn không nghĩ ít hơn về bạn. Nếu bạn đã quên không gõ dấu $ trong công thức, và bạn muốn sửa nó bằng F4, Dùng chuột tô đen địa chỉ tham chiếu như hình 195.
Fig. 195
After the reference is highlighted, you can hit the F4 key to toggle that particular reference through the four states, as shown in Fig. 196. Sau khi tô đen, bạn lại có thể gõ F4 và lập lại điệp khúcgây chú ý cho bạn đồng nghiệp cùng phòng ở trên.
Fig. 196
Summary: Master the F4 key to easily add dollar signs to a reference in order to toggle it from relative to absolute to mixed to mixed. Tóm tắt: Phím F4 giúp ta dễ dàng chèn dấu $ vào 1 địa chỉ tham chiếu để chuyển qua lại giữa 4 loại tham chiếu.
of 354
LEARN R1C1 REFERENCING TO UNDERSTAND FORMULA COPYING TÌM HIỂU VỀ THAM CHIẾU R1C1 ĐỂ HIỂU VỀ SAO CHÉP CÔNG THỨC Problem: All of a sudden, the column letters along the top of your spreadsheet have been replaced by numbers, as shown in Fig. 197. None of the formulas that you enter will work. Vấn đề: Tình cờ vào 1 lúc nào đó, những chữ cái tên cột ở trên cùng bảng tính của bạn bị thay thế bằng các con số. Thế là chẳng có công thức nào hoạt động.
Fig. 197
Strategy: Relax. There are two ways of naming cells. Someone has turned on the R1C1 style of addressing. To return to the normal A1 style of cell addressing, go to Tools – Options. On the General tab, uncheck the box for R1C1 Reference Style, as shown in Fig. 198. Sự tình: Thư giãn chút đi bạn. Có 2 cách đển gọi tên ô. Người nào đó đã đổi Excel sang tham chiếu R1C1. Để trở lại hiển thị A1 thông thường, bạn vào Menu Tools – Options. Trong tab General, bỏ chọn ô R1C1 Refernce style.
Fig. 198
But wait – while you are here, you can learn something fascinating about spreadsheets. In the topic “Copy a Formula That Contains Relative References”, I suggested it was miraculous that Excel could automatically change a formula as you copied it. If you take two minutes to learn about this other method of cell addressing, you will understand that it may not be so amazing after all. When Dan Bricklin and Bob Frankston invented VisiCalc, they used the A1 style of cell naming. When Mitch Kapor started selling Lotus 1-2-3, he used the same style. When Microsoft came out with their first spreadsheet product – Microsoft Multiplan – they used a very different method of cell addressing. This method is known as R1C1. In the Microsoft system, the rows are numbered just as in the A1 system. However, the columns are also numbered. Each cell is given a name, such as “R4C8”. This name stands for the cell at Row 4, Column 8. This is the cell that you and I know as H4. In the R1C1 style, the formulas are interesting. Look at this formula in cell D6, as shown in Fig. 199. Nhưng khoan hãy làm như vậy. Khi bạn còn đang trong tình trạng đó, hãy tìm hiểu 1 đôi điều lý thú. Trong bài “Sao chép công thức có tham chiếu tương đối”, tôi có đề cập đến sự tự động điều chỉnh tham chiếu của công thức khi bạn sao chép nó đến chỗ khác. Nếu bạn bỏ ra vài phút để tìm hiểu về cách hiển thị địa chỉ R1C1, bạn sẽ hiểu và thấy rằng chẳng có gì kỳ lạ cả. Khi Dan Bricklin và Bob Frankston viết VisiCalc, họ dùng loại địa chỉ ô A1. Khi Mitch Kapor bắt đầu bán Lotus 1-2-3, ông ta cũng làm tương tự. Đến khi Microsoft xuất hiện với sản phẩm bảng tính đầu tiên của mình, (Microsoft Multiplan), họ đã dùng 1 cách hoàn toàn khác. Đó là cách bây giờ gọi là R1C1. Trong hệ thống của Microsoft, các dòng được đánh số như hệ thống A1. Nhưng các cột cũng được đánh số nốt. Mỗi ô sẽ có địa chỉ ô như là R4C8. Địa chỉ này hiểu là ô nằm ở dòng 4, cột 8 và là ô mà tôi và bạn biết đến như là ô H4. Trong hệ địa chỉ R1C1, các công thức sẽ rất lý thú. Hãy xem công thức trong ô D6 của hình 199
Fig. 199
The formula in the formula bar says =D5+C6–B6. But when you think about this formula in plain language, what it is really means is “Take the cell just above me, add the interest in the cell just to the left of me, and subtract the payment in the cell two cells to the left of me”. Formulas in R1C1 style are more like the plain language description above. If you want to enter a formula in D6 that points to the cell just above, it would be =R[–1]C. The number in square brackets after the R indicates to how many rows ahead or back you are referring. In our case, row 5 is one row above row 6, so you would put a –1 in the square brackets. There is no number after the C portion of the address, which means that you are referring to the same column as the cell that contains the formula. If you want to refer to a cell that is two cells to the left of the cell with the formula, you would use =RC[–2]. As shown in Fig. 200, the formula from Fig. 199 can be restated in R1C1 style as follows: =R[–1]C+RC[–1]–RC[–2] Công thức trong ô đó là =D5+C6-B6. Hiểu theo ngôn ngữ của chúng ta thì là: Lấy ô ở trên ô hiện hành, cộng thêm tiền lãi ở ô bên trái ô hiện hành, và trừ bớt khoản thanh toán ở ô thứ hai phía trái. Công thức trong hệ R1C1 diễn giải ra giống như cách nói như vậy. Nếu bạn muốn thm chiếu ô D6 đến ô phía trên nó, tham chiếu sẽ là R[-1]C. Con số trong dấu ngoặc vuông sau chữ R sẽ cho biết ô tham chiếu ở trên hoặc dưới ô hiện hành bao nhiêu dòng. Trong thí dụ này, dòng 5 ở trên dòng 6 là 1 dòng, vậy số trong ngoặc vuông là -1. Không có con số nào ở sau chữ C, có nghĩa là tham chiếu đến 1 ô cùng cột. Nếu muốn tham chiếu đến ô thứ 2 về phía trái, bạn sẽ dùng RC[-2]. Như hình 200, côngthức hình 199 sẽ diễn giải lại như sau: =R[–1]C+RC[–1]–RC[–2]
LEARN R1C1 REFERENCING TO UNDERSTAND FORMULA COPYING TÌM HIỂU VỀ THAM CHIẾU R1C1 ĐỂ HIỂU VỀ SAO CHÉP CÔNG THỨC Tiếp theo So, all relative references in R1C1 style have a number in square brackets, either after the R or after the C or both. It is very interesting to see how this style does absolute addresses. As shown in Fig. 201, the formula in B6 is an absolute formula that always points to cell E2. The formula in A1 style is =$E$2.
Vậy là, các địa chỉ tương đối trong hệ R1C1 có 1 con số trong dấu ngoặc vuông, sau chữ R hoặc sau chữ C hoặc cả hai. Sẽ thú vị khi xét đến tham chiếu tuyệt đối: trong hình 201, công thức trng ô B6 là 1 công thức có tham chiếu tuyệt đối, nó luôn luôn chỉ đến ô E2. Côngthức trong hệ A1 là = $E$2
Fig. 201 To enter a similar absolute reference in R1C1 style, you do not include square brackets in the address. As shown in Fig. 202, a formula of =R2C5 will ALWAYS point to cell E2. Trong hệ R1C1, để nhập 1 địa chỉ tuyệt đối vào côngthức, bạn sẽ không đặt dấu ngoặc vuông vào. Như hình 202, côngthức như sau = R2C5 sẽ luôn luôn chỉ đến ô E2
Fig. 202 It is also possible to have mixed references. Flip back to Fig. 177 in the multiplication table topic. Fig. 203 shows that formula in R1C1 style:
Cũng có thể tạo tham chiếu hỗn hợp. Xem lại hình 177 của bài tính bảng nhân dòng cột. Côngthức của bảng này trong hệ R1C1 sẽ như sau: = RC1*R1C
Fig. 203
Additional Details: Now that you understand the basics of R1C1 style formulas, you can appreciate the amazing part. Remember that Microsoft invented this method for their Multiplan product. Lotus 1-2-3 was the dominant spreadsheet in the late 1980s and early 1990s. Microsoft was battling for market share. Everyone using spreadsheets was familiar with the A1 style. No one would want to learn the R1C1 style in order to switch to Microsoft. So, in their Microsoft Excel product, they developed an elaborate system to actually store the formulas in R1C1 style, but then to translate the R1C1 formulas to A1 style to make it easier for all the Lotus fans to understand. By default, Microsoft starts with the A1 style addressing. However, you can remember from Fig. 198 that you are just one checkmark away from switching back to R1C1 style addressing. Finally, here is the amazing part. Examine the amortization table example in Formula View mode. (Hit Ctrl+~ to toggle into Formula View mode.) The Formula View mode in A1 style can be seen in Fig. 204. Every formula in column D is different.
Nói rõ thêm: Bây giờ bạn đã hiểu thế nào là hệ và tham chiếu R1C1, bạn sẽ hiểu sự lạ thừơng về tự động điều chỉnh tham chiếu. Hãy nhớ rằng Microsoft tạo ra hệ R1C1 cho phần mềm MultiPlan. Lotus 1-2-3 là bảnh tính nổi trội trong những năm cuối thập niên 80, đầu thập niên 90 của thế kỷ 20. Microsoft đã chiến đấu để giành thị phần. Mọi người sử dụng các loại bảng tính đều đã quen với hệ A1. không ai muốn học R1C1 để chuyển qua dùng Microsoft. Do đó, sang phần mềm Excel, Microsoft đã phải phát triển 1 hệ thống phức tạp để lưu trữ dưới dạng R1C1, nhưng lại biên dịch để hiển thị dạng A1 để những người dùng Lotus 1-2-3 có thể hiểu được. Mặc định Excel bắt đầu với hiện dạng A1, nhưng nếu nhớ lại hình 198, bạn có 1 ô chọn để chuyển qua dạng R1C1. Sau cùng , đây là phần kỳ lạ nhất: Hãy xem bảng tính về khoản vay tiền trong view Formula mode (nhấn Ctrl+~). Các công thức ở các ô cột D đều khác nhau
Fig. 204
The Formula View Mode in R1C1 style can be seen in Fig. 205.
Thế nhưng các công thức đó trong hình 205 của view Formula mode dạng R1C1 thì sẽ giống nhau.
Fig. 205
In A1 style, it seems AMAZING that Excel can change a reference from D10 to D11 when the formula is copied down. However, look closely at the formulas in each row of rows 7 and higher in the R1C1 style shown in Fig. 205. Each formula in a column is identical to the formula located just above it! While VisiCalc and Lotus 1-2-3 made the formula replication seem amazing because of their A1 reference style, if the Multiplan invention of R1C1 style had taken hold, it would not seem amazing at all because, in fact, every formula is exactly identical as you copy it down through the rows. If you ever plan on writing VBA macros in Excel, it is important to understand the R1C1 style of formulas. For general use in Excel, you never really need to totally understand the R1C1 style, but it is interesting to see how Microsoft’s R1C1 style is actually superior to A1 when copying formulas in a spreadsheet.
Trong dạng địa chỉ A1, bạn lấy làm lạ khi Excel tự động chuyển tham chiếu D10 thành D11 khi sao chép 1 công thức xuống dưới. Tuy nhiên khi chuyển sang dạng địa chỉ R1C1, các côngthức trong cột lại giống nhau y hệt. Khi Visicalc và Lotus 1-2-3 thực hiện sự sao chép công thức, dường như rất lạ khi tự động điều chỉnh trong hệ A1. Đến khi Multi plan ra đời, với phát minh hệ R1C1, nó chẳng còn gì mới lạ nữa khi mà công thức sao chép đi sẽ giống y như cũ. Nếu có khi nào bạn sử dụng VBA trong Excel, bạn sẽ chẳng cần hiểu hết về hệ R1C1, nhưng sẽ rất lý thú khi xem hệ R1C1 hay hơn hệ A1 thế nào khi sao chép côngthức trong bảng tính.
Summary: Learn R1C1 style formulas to better understand how formulas are replicated across a worksheet. Tóm tắt: Tìm hiểu hệ địa chỉ R1C1 để hiểu rõ hơn việc sao chép công thức đến khắp nơi trong bảng tính.
