Part one function: two basics

We start from an interesting example, we will be in contact with various screen sizes in life, known as the retina screen of mobile phones, more than 10 inches of flat, 4K display, 8K TV. But this display screen whether it really good? Decided to display delicate effect of key parameters is called ppi, that is, how many pixels per inch can be arranged. Phone ppi values ​​are generally higher, we do not have to worry about, but like a 12.5-inch models, “thinking this” is the same years of 1366 * 768 resolution, the display is really powerless Tucao people. There are 27-inch 4k display has no 23-inch monitor 2K clear it?

函数篇之一:基础知识两条

Generally such display will identify its parameters: for example, 1920 * 1080,23 inch, it means there are 1920 pixels long side, wide-brimmed 1080 pixels, diagonal is 23 inches long. Although very small pixels, we might see it as a unit of length, we just use the Pythagorean theorem, you can know the value of the pixel diagonal, then divided by 23 inches, this value is ppi screen:

Common screen PPI value

iphone4 retina known as screen resolution, can be considered as a reference line, the line screen is substantially more than this entry will not grainy, below, particles have a certain sense of the entry feed line, 100 PPI upper and lower, substantially eye visible “small grid.” Admittedly, the resolution of the flagship phone or the best.

First, the absolute references and relative references.

The following return to our subject, form letters in horizontal and vertical digital, constitutes the coordinates of each grid, function excel in, is to tell the computer function where the cell’s value, which is equal to the grid through function calculation after obtaining. Column E, the PPI is the formula, E2 cell’s contents is “= SQRT (B2 * B2 + C2 * C2) / D2”, SQRT represents square root.

The formula in E2 we generally are entered manually, but with E3 …… E33 E2 formula is the same, so we just drag the fill on it.

In the process of dragging down, E3 cell formulas automatically becomes “= SQRT (B3 * B3 + C3 * C3) / D3”, apparently

With the changes in the coordinates of the cell where the formula, formula refers to coordinate changes with the relative position between the function and the cell that they are always the same. This situation is called a relative reference.

Let us consider another situation, we in column F, enter the formula to calculate the ranking of the paragraph PPI screen, we can enter the formula “= RANK (E2, E2: E33)”, which means seeking E2 cell’s value, in E2: E33 data in this ranking. We then drag the formula down, E2: E33 coordinates of the area have taken place this relative change, which is obviously not what we need, we fill the formula for all rows, this area is E2: E33, this time, we we need an absolute reference,

Absolute references is when you drag a formula, coordinate values ​​in the formula is always the same references

. There are two methods of operation:

1, to be added directly in front of the fixed coordinates “$” symbol in the English state, to be fixed on a column in front of column coordinates plus, add the line to be fixed on the front row coordinates, the ranks are fixed, they are added to the front ranks. We also fixed in the present embodiment ranks, the formula becomes “= RANK (E2, $ E $ 2: $ E $ 33)”, of course, the present embodiment only a fixed line, the correct result can be obtained.

2, when entering a formula, which is still the choice by entering the coordinates of its convenience still, when the coordinates of the cursor is still in the region of the formula, according to “F4” key, i.e., coordinates in an absolute reference completely, absolute reference row, column absolute reference, a relative reference mode switching between the four kinds of reference cycles.

The second is recommended, especially operating smoothly.

When will the use of absolute and relative references after,

In a region of relatively independent, in the same row, the same column or formula, to be able to drag the fill, not a row or column is set to the middle of different formulas,

This will reduce the efficiency of the inspection data.

Second, and more table references

When we have the same workbook format plurality of tables, we need to put on each table A10 summing cell, we can set this formula:

= SUM (Sheet2:! Sheet5 A10)

It represents between sheet2 to sheet5, A10 cells summing all tables, clearly more convenient than adding the table one by one, and is easy to check.

Method of operation is the first entry “= sum (“, and then use the mouse to point sheet2 cell A10, and then hold down the “shift” key without releasing, and then the table name sheet5 mouse click, and then input “).”

When we need to operate the plurality of tables based format, may be similarly used, such as we need to insert a table in the same position between all the sheet2 to Sheet5, we can switch to the first sheet2, then hold down the “shift” Do not release the button, and then click of the mouse sheet5 the table name, this time to sheet5 were tested sheet2 selected, then all operations on sheet2, will synchronize all the table between sheet2 and sheet5.

About the Author

You may also like these