- Picture and text skills
In table calculations, you can use copied formulas to quickly perform batch calculations.
When copying a formula, three reference modes will be involved: absolute reference, relative reference, and mixed reference.
Three types of references have different usages. If used incorrectly, incorrect results will be obtained.
￭ Relative reference, this is the most common reference method. When you copy a cell formula, the formula changes as the position of the referenced cell changes.
For example, enter =sum(B2:C2) in the cell, and then pull down the fill handle to copy the formula.
You can see that the formula of each cell will not remain sum(B2:C2), but as the position of the cell changes, the copied formula also changes.
￭ Absolute reference, the copied formula will not change as the position of the referenced cell changes.
For example, if you want to convert a fraction into ten points, you can absolutely reference the content of cell B15.
For example, enter the formula =D2/B15. At this time, when you pull down to copy, you will find that the formula is wrong.
This is because no absolute reference is added, and the formula will change as the position of the cell changes.
￭ If you want the formula to keep D3 divided by B15, you need to select B15 and press F4 to add an absolute reference.
When you see the absolute reference symbol in the formula, it means the addition is successful.At this time, pull down to copy the formula and find that cell B15 is fixedly referenced.
￭ Mixed Reference, part of the formula changes when you copy it.
Before understanding mixed references, we need to understand the four switching types of references.
Press F4 on the keyboard to quickly switch between relative and absolute references.
Wherever the reference symbol is, it means where it is fixed.
D2 means not fixed. This is the relative reference we just talked about.
$D$2 A symbol is added before the row number and column label, indicating that the row and column are fixed. This is an absolute reference.
The D$2 symbol is only added before the line number, indicating that only the line is fixed.
The $D2 symbol is only added before the column number, indicating that only the column is fixed.
These two situations are mixed references.
￭ For example, to calculate the score ratio of multiple-choice questions and quiz questions, enter the formula =B2/D2.
How to set the formula in column D to change when the cell changes when the data in column D is pulled down and copied, but does not change when the data in column D is pulled down to the right?
We need to set the formula that refers to the data in column D to a fixed symbol and only add it before the column label, indicating that the column is fixed.
At this time, copy the formula to the right and you can see the referenced column data. Column D will not become column E.
When you pull down the fill handle to copy the formula, the row data will change as the position of the cell reference changes.
Relative references and absolute references are relatively simple, while mixed references are relatively complex.
You can do it a few times to help your understanding.
Articles are uploaded by users and are for non-commercial browsing only. Posted by: Lomu, please indicate the source: https://www.daogebangong.com/en/articles/detail/biao-ge-xin-shou-jiao-cheng-han-shu-ji-chu-zhi-shi-xiang-dui-jue-dui-hun-he-yin-yong.html
支付宝扫一扫 
评论列表（196条）
测试