Excel array formula from getting started to proficient

I. Memory of the course

What is an array formula? As the name implies is the formula contains the array, the detailed meaning see the previous article. But the important thing to note here is that if you want to use an array formula, you must press the "Ctrl+shift+enter" key combination in the formula bar to make the formula in "{}".

Second, the array formula continues to deepen

The impression is several years ago, at that time read a literacy post, the author cited the example is very practical. The details are not very clear, the general meaning is to use the function to calculate 1 to 100 of the and. This is also the case here.

1. Seek 1 to 100 of the and

Before you look down, let's think about what you can do to deal with it. Using only one function to solve 1 to 100 of the and, of course, can be 1000, 10000 or more. It doesn't make much sense to discuss a specific number, but here's a good example of how the array formula is used.

Answer: {=sum (ROW (1:100))}

Problem analysis: 1 to 100 of the and, the answer is 5050 (elementary school students know ^), but Excel must be you told it the right way, it can know. The computation from 1 to 100 is actually calculated 1+2+3+4+......+98+99+100, well, the answer comes out, enter "=sum (1+2+3+4+......+98+99+100)" in the formula bar. Believe that the smart must not be satisfied with the answer, although the correct results can be obtained, but it is obviously a "wrong" approach.

The easiest way to get the correct number of numbers from 1 to 100 is to use the row () or column () function, and I'm more accustomed to row () because of my personal habits, so take the row () function for example.

Familiarize yourself with the Row () function: Enter "=row ()" In the A1 cell and fill the A5 with the fill handle, what do you see? Is that the value in each cell is its corresponding number of rows.

Surprise: Row () represents a single forward, and if you use Row (1:100) to represent an array that contains the line numbers from the first to the 100th row, that is, 1, 2, 3 、......、 98, 99, 100, we now apply the array to the formula.

Enter "=sum (1:100)" In any cell in the worksheet and press Ctrl+shift+enter, and you'll be pleasantly surprised to find that the results we're about to show up.