Apa itu array formula pada Excel?

An array formula is a type of formula that performs an operation on multiple values instead of a single value. The final result of an array formula can be either one item or an array of items, depending on how the formula is constructed. For example, the following formula is an array formula that returns the sum of all characters in a range:

Table of Contents Show

What is an Array?
Special syntax
Without the Array Formula
Multi Cell Array Formula
Single Cell Array Formula
Array Functions are Risky
Apa itu rumus array di Excel?
Apa fungsi dari table array?
Apa itu Bins array?
Ctrl Shift Enter untuk apa?

To work correctly, many (but not all) array formulas need to be entered with control + shift + enter. When you enter with control + shift + enter, you'll see the formula wrapped in curly braces {} in the formula bar. Do not enter curly braces manually, or the formula won't work.

What is an Array?

An array is a collection of more than one item. Arrays in Excel appear inside curly brackets. For example, {1;2;3} or {"red","blue","green"}. The reason arrays are so common in Excel is that they map directly to cell ranges. Vertical ranges are represented as arrays that use semicolons, for example {100;125;150}. Horizontal ranges are represented as arrays that use commas, for example {"small","medium","large"}. A two dimensional range will use both semicolons and commas.

Examples

Array formulas are somewhat difficult to understand, because the terminology is dense and complex. But array formulas themselves can be very simple. For example, this array formula tests the range A1:A5 for the value "a":

The array operation is the comparison of each cell in A1:A5 to the string "a". Because the comparison operates on multiple values, it returns multiple results to the OR function:


=OR({FALSE;FALSE;FALSE;TRUE;FALSE})

If any item in the resultant array is TRUE, the OR function returns TRUE.

Sometimes array formulas supply multiple values as a function argument. For example, this array formula returns the total character count in the range B2:B11:

The LEN function is given multiple values in the range B2:B11 and returns multiple results in an array like this inside SUM:


=SUM({3;3;5;4;5;4;6;5;4;4})

where each item in the array represents the length of one cell value. The SUM function then sums all items and returns 43 as the final result.

Special syntax

In all versions of Excel except Excel 365, many array formulas need to be entered in a special way to work correctly. Instead of entering with the the "Enter" key, they need to be entered with Control + Shift + Enter. You'll sometimes see Control + Shift + Enter abbreviated as "CSE", as in "CSE formula". A formula entered in this way will appear with curly braces on either side:

These braces are displayed automatically by Excel. Make sure you do not enter the curly braces manually!

Not all array formulas need to be entered with Control + Shift + Enter. Certain functions, like SUMPRODUCT, are programmed to handle array operations natively and usually don't require Control + Shift + Enter. For example, both formulas below are array formulas that return the same result, but only the SUM version requires Control + Shift + Enter:

Excel 365

In Excel 365, array formula are native and do not require control + shift + enter. For a general introduction, see Dynamic Array Formulas in Excel.

Without the Array Formula

In the example below you find different fruits in (A2:A9), the amount sold per day (B2:B9) and the price per product (C2:C9). The calculation of the revenue per fruit is now performed in each cell of the revenue column (D2:D9). This can be done faster using a multi cell array formula.

Multi Cell Array Formula

The multi cell array formula will return multiple results within a row or column, using a single formula. In order to calculate all values of the column ‘Revenue per fruit’ in the example above at once, we start by selecting the cell range in which we want to publish the results and we press F2 to select the first cell in the range:

Then we enter the array formula in selected cell D2:

BUT HOLD YOUR HORSES! When you press ‘Enter’ after writing the formula, Excel will only calculate the result of the current row! This type of formula is also called a CSE formula, because it will only execute correctly when using CTRL+SHIFT+ENTER.

As you can see, the formula calculated all the values of the array at once. Excel added braces { } to the formula to indicate that it is an array function.

Single Cell Array Formula

A single cell array formula will return one result within one cell, while performing one or multiple calculations.

In our example, the total fruit revenue can of course be calculated using the SUM function on the values in the ‘Revenue per fruit’ column:

But when using the array function, we don’t need the intermediate results to calculate the grand total. We simply select the cell in which we want to publish the total fruit revenue and we enter the formula ‘=SUM(B2:B9*C2:C9)’:

Again, remember to click CTRL+SHIFT+ENTER to reveal your results! Only pressing Enter will result in a standard Excel error: ‘#VALUE!’. After executing the array function correctly, the grand total will show in the designated cell and again, braces { } will appear around the initial formula to indicate it concerns an array formula now.

Array Functions are Risky

We consider array functions a risk, because it makes cooperation with one spreadsheet very error prone. Your co-worker might not be familiar with array functions and change something without pressing CTRL+SHIFT+ENTER… That’s why PerfectXL detects all formulas of this type and marks them as a potential risk.

Apa itu rumus array di Excel?

Rumus array adalah rumus yang dapat melakukan beberapa perhitungan pada satu atau beberapa item dalam array. Anda dapat menganggap array sebagai baris atau kolom nilai, atau kombinasi baris dan kolom nilai.

Apa fungsi dari table array?

Adapun fungsi array atau rumus array (array formula) merupakan fungsi atau rumus excel (excel formulas) yang melibatkan array dalam kalkulasi datanya. Rumus array dapat melakukan beberapa perhitungan pada satu atau beberapa item dalam array dan menghasilkan satu atau beberapa hasil kalkulasi atau perhitungan.

Apa itu Bins array?

ArrayBins. Merupakan nilai interval pengelompokan data dalam bentuk referensi range atau alamat sel maupun nilai array. Argumen ArrayBins ini digunakan sebagai acuan untuk mengelompokkan nilai dalam ArrayData.

Ctrl Shift Enter untuk apa?

Saat Anda menekan Ctrl+Shift+Enter, Excel secara otomatis menyisipkan rumus antara { } (sepasang kurung kurawal buka dan tutup). Catatan: Jika Anda memiliki versi Microsoft 365 saat ini, maka Cukup masukkan rumus di sel kiri atas rentang output, lalu tekan ENTER untuk mengonfirmasi rumus sebagai rumus array dinamis.