If you want to perform batch operations, you must learn array formulas.
Baidu looked at what an array formula is, "An array is a collection of units $#%@%$..."
I can't understand the concept, classification, and usage. Today I will talk about arrays in the most down-to-earth way.
1. Falling in love with array formulas
I believe that many people who want to learn array formulas will ask Du Niang what "array formula" is. After watching it for a long time, I still didn’t know why. Now put all those awkward concepts out of your mind so that you can re-understand array formulas.
follows the idea and imagines that she is a grocery shopping lady on the street, selling 4 kinds of vegetables. Due to the long-term good quality of vegetables, there are many repeat customers, so I want to increase the price of each vegetable by 0.5 yuan to make more money for my son and a wife. So the question is, how much will each vegetable cost after the price increase?
After reading this question, you will definitely show contempt and affection. This is too simple! The answer to the question can be calculated by verbal arithmetic.
Then the problem escalates. You are the son of a grocery store aunt and you open a small supermarket selling 200 kinds of products. Because I want to make more money to buy a house, I want to increase the price of each commodity by 0.5 yuan. What is the current price of these 200 commodities?
If you want to increase the selling price of these 200 products by 1, it will be difficult to calculate verbally. But in the Excel table, if you add 1 to all the product selling price cells and use an array formula, you can get the result in 3 seconds. Isn’t it so cool?
2. How to write
for arrays, so array formulas are formulas for batch operations. So how to do batch operations? Don't worry, learn to crawl before you learn to run. If you don’t know how to write arrays and array formulas, how can you use them?
Excel uses cells to record data. Therefore, array formulas are used in Excel, which performs batch operations on cell data. So, how can you enter data in Excel?
can input a row of data horizontally, which is a one-dimensional horizontal array.
can also input a column of data vertically, which is a one-dimensional vertical array.
can also input data horizontally and vertically, which is a two-dimensional array.
After knowing the type of array, it is easy to clarify the calculation possibilities of the array, as shown in the figure below, which is the array calculation that may be used in Excel. So next, we only need to understand what the operation rules are for these different situations, and then we can learn to use array formulas! So easy!
3. How to use arrays
Regardless of the operation situation, you need to understand that you need to press the [Ctrl+Shift+Enter] key combination after entering the array formula. Let’s take a look at what to do in different operation situations. In the first case of
, let the cell data and a constant be subjected to batch operations. The principle is that each cell data can be calculated separately with the constant. Note that the result area must be the same size as the cell range involved in the operation.
As shown in the figure below, select the area with the same size as the data area, multiply the area data by 5, and press the [Ctrl+Shift+Enter] key combination to complete the batch operation.
The second case is batch operation between one-dimensional data in the same direction. That is, the operation of one-dimensional horizontal array and one-dimensional horizontal array, or the operation of one-dimensional vertical array and one-dimensional vertical array. The operation rules are the same, that is, the data at the corresponding positions of the two arrays are operated separately to generate a new array with unchanged size and direction. For example,
multiplies the data of two horizontal one-dimensional data, and multiplies the data at the corresponding positions in the array to generate a new array.
The third case is batch operation of one-dimensional data in different directions. For example, a one-dimensional horizontal array of M columns is multiplied by an array of N rows. The operation rule is that each data in the vertical array is separately operated with each array in the horizontal array to generate a new M*N array.
The fourth case is the operation between one-dimensional data and two-dimensional data. The premise is that the two-dimensional array must be the same size in the same direction as the one-dimensional data. The operation rule is that the data of one-dimensional data is operated with the two-dimensional array respectively to generate an array with the same size as the two-dimensional array.
is shown in the figure below. The one-dimensional array on the right has 4 rows of data, and the two-dimensional array on the left also has 4 rows of data vertically. If these two arrays are added, then the data 15 of the one-dimensional array on the right is added to the first row of the two-dimensional array, and the data 24 of the one-dimensional data is added to the second row of the two-dimensional array... This generates the new array below.
The fifth case is the operation between two-dimensional data and two-dimensional data. This operation requires that the two arrays have the same size. The operation rules are very simple. The data at the corresponding positions of the arrays can be operated together to generate a new two-dimensional array with the same size.
For example, the following two two-dimensional data are added, and the data at the corresponding positions in the array are added to generate a new array.
