The little offer function makes everyone in the clouds, cares a few times but never fully understands its essence. From complex data summaries to PivotTables to high-level dynamic charts, the offer function is indispensable. No matter how complex these applications are, as long as we understand the essence of offer, everything will be solved!
Let's use a simple and simple illustration to understand the offer.
1. Clone the contents of cell a1 to cell d1, enter =offset(a1,0,0) in cell d1, and d1 displays "Product"
2. Change the second parameter of offset (a1, 0, 0) to 1, that is, change it to =offset(a1,1,0), write it to cell d1, and cell d1 displays "a", as shown in the following figure
Promotion should not be missed: Tmall settled - Hu Jun endorsement brand to help you enter the major e-commerce platforms and achieve Internet +
3. If you change the second parameter of =offset(a1,0,0) to 2, think about what cell d1 will display?
The answer is shown in the following figure
I believe you have seen that the second parameter of offset (a1, 2, 0) is to move down a few cells based on a1. The first parameter is the base cell. In addition, the third parameter writes a few, that is, to move a few cells to the right with the base cell.
Think about how to clone the contents of b3 in cell d1, and use a1 as the benchmark offer parameter to be written.
Cell d1 = offset(a1,2,1) as shown below:
Promote the on-line gifts, super s explosion frank true legend!
Now, everyone fully understands the role of these 3 parameters of the offset (a1, 2, 1) function, offset (base cell, longitudinal offset, horizontal offset).
But the offer has 5 parameters, we just used the first 3 parameters, the remaining two are what to use, some partners are not starting to have headaches again. It's actually very simple, keep looking down.
We took the first legend at the beginning of the article down, still cloned the contents of cell a1 to cell d1, but this time we want to modify the parameters, change the three parameters of offer (a1, 0, 0) to offline (a1, 0, 1, 1) five parameters, write to cell d1, 3 parameters and 5 parameters Of the same output result, d3 shows "product", as shown in the following figure:
Seeing this, everyone thinks that since the output results of 3 parameters and 5 parameters are the same, how simple is it to directly use 3 parameters? 5 parameters is exactly the essence of offer.
As an example, the output function is used to clone a1 and b1 to d1 and e1 at once
Now change the fifth argument of the offset from offset (a1,0,0,1,1) to offside(a1,0,0,1,2) to write cells d3 and e3, and write cells d3 and e3 at the same time? Right! You read that right, and I didn't write it wrong! The key is here, how to write?
Select cells d3 and e3 and write =offset(a1,0,0,1,2). note!!! Note: Press ctrl+shift+enter after writing for the function to take effect!!! That's why some partners always go wrong!!!
Note: The curly braces on both sides of the function are not written on it, and are automatically generated by pressing ctrl+shift+enter! This is the concept of arrays, which will be introduced to you later. Let's figure out offset first!
At this point, everyone should understand the true meaning of the fifth parameter, the fifth parameter is 2, that is, to return the first parameter a2 cell as the benchmark, the content of the horizontal two cells, the output cell should also select the horizontal two cells at the same time. Otherwise, if the output only selects one cell, it cannot be put down, and it can only be reported as wrong!
So, what is the fourth parameter used for? that is, the clone displays the number of cells in portrait orientation.
Consider the following figure, how to use offer to clone a1:b2 region to d1:e2 region at once.
Some partners should be able to do it, offset (based on cells a1, 0, 0, return 2 ranges of cells in landscape, 2 ranges of cells in portrait orientation). This is the offer(), 5-parameter principle! Change the parameters arbitrarily to experience it, and don't forget to ctrl+shift+enter when the output shows multiple cells at the same time.
After understanding the principle, let's look at an application.
Using the first 3 parameters of the offer, the multiple columns are transposed to multiple rows.
Write the function =offset($a$1, column(a1)-1, row(a1)-1) in cell a10, and fill a10:e11, you will see the effect of the above figure. Here columu(a1) returns "1" and row(a1) returns "1", so parse the a10 cell function offset(a1,0,0) so that you can easily understand it.
Offset can complete the task far more than that, I will introduce more applications to you