ID EN
…
#Dynamic array

GROUPBY

Excel Functions 🇮🇩 Bahasa Indonesia

Ringkaslah data dengan mengelompokkan baris

Syntax

EXCEL 
=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array], [field_relationship])

Arguments

Parameter Deskripsi
row_fields The values for grouping.
values The values to aggregate.
function The calculation to run when aggregating.
field_headers [optional] 0 = No, 1 = Yes, don't show, 2 = No, generate, 3 = Yes, show.
total_depth [optional] Totals and subtotals. 0 = No, 1 = Grand Totals, 2 = Both, -1 = Grand Totals at top, -2 = Both at top.
sort_order [optional] Sort by index number. Negative numbers = descending order.
filter_array [optional] Logic to exclude specific rows.
field_relationship [optional] Field relationship when multiple columns are provided as row fields. 0 = Hierarchy (default), 1 = Table.

Return Value

Tabel ringkasan data

Details

Fungsi GROUPBY dirancang untuk meringkas data dengan mengelompokkan baris dan menggabungkan nilai. Hasilnya adalah tabel ringkasan dinamis yang dibuat dengan satu rumus. Keluaran dari fungsi GROUPBY serupa dengan keluaran dari Tabel Pivot, namun tanpa pemformatan. Ringkasan yang dikembalikan oleh fungsi GROUPBY sepenuhnya dinamis dan akan segera dihitung ulang ketika data sumber berubah. Berikut adalah daftar singkat fitur dan batasan GROUPBY: Fungsi GROUPBY membutuhkan delapan argumen, namun hanya tiga argumen pertama yang diperlukan. Pada lembar kerja di bawah ini, kami menggunakan fungsi GROUPBY untuk merangkum Penjualan berdasarkan Kota. Rumus di sel F5 adalah: Untuk contoh ini, GROUPBY dikonfigurasi dengan tiga argumen sebagai berikut: Dengan input di atas, fungsi GROUPBY menjumlahkan Penjualan berdasarkan Kota dan menghasilkan tabel di F4:

Contoh

GROUPBY basic example

The GROUPBY function takes eight arguments, but only the first three are required. In the worksheet below, we use the GROUPBY function to summarize Sa

EXCEL 
=GROUPBY(B5:B16,D5:D16,SUM)
GROUPBY with field headers

The field_headers argument is optional. When field_headers is omitted, GROUPBY will automatically detect headers in the source data by testing values.

EXCEL 
=GROUPBY(B4:B16,D4:D16,SUM,3) // field headers enabled
GROUPBY calculation options

The third argument in GROUPBY is function, which specifies the calculation to perform when values are grouped. Available calculations include Excel fu

EXCEL 
=GROUPBY(D4:D104,D4:D104,COUNTA,1)
GROUPBY calculation options

In the next example, we've changed the calculation to generate a total cost by meal, as seen below. The formula in G5 now looks like this:

EXCEL 
=GROUPBY(D4:D104,E4:E104,SUM,1)
GROUPBY with multiple calculations

It is possible to perform more than one calculation with GROUPBY, but it is not obvious how to do so, since the function argument accepts just one val

EXCEL 
=GROUPBY(D4:D104,E4:E104,HSTACK(COUNT,SUM),1)
GROUPBY with multiple calculations

Notice that the formula is now entered in cell G4, even though we have set field_headers to 1 (do not display). This is because GROUPBY automatically

EXCEL 
=GROUPBY(D4:D104,E4:E104,HSTACK(COUNT,SUM,PERCENTOF),1)

See Also

PIVOTBY HSTACK VSTACK PERCENTOF