ID EN
…
#Dynamic array

PIVOTBY

Excel Functions 🇮🇩 Bahasa Indonesia

Meringkas data dengan mengelompokkan baris dan kolom

Syntax

EXCEL 
=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array], [relative_to])

Arguments

Parameter Deskripsi
row_fields The values to use when grouping rows.
col_fields The values to use when grouping columns.
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.
row_total_depth [optional] Enable or disable row totals. 0 = None, 1 = Grand Totals, 2 = Grand and Subtotals, -1 = Grand Totals at top, -2 = Both at top.
row_sort_order [optional] A column number indicating how rows should be sorted. Negative numbers = descending order.
col_total_depth [optional] Enable or disable column totals. 0 = None, 1 = Grand Totals, 2 = Grand and Subtotals, -1 = Grand Totals at top, -2 = Both at top.
col_sort_order [optional] A column number indicating how columns should be sorted. Negative numbers = descending order.
filter_array [optional] Logic to exclude specific rows.
relative_to [optional] Where to find values for the 2nd argument of a function. 0 = column totals (default), 1 = Row Totals, 2 = Grand Totals, 3 = Parent Col Total, 4 = Parent Row Total.

Return Value

Tabel ringkasan data

Details

Fungsi PIVOTBY dirancang untuk meringkas data dengan mengelompokkan baris dan kolom. Hasilnya adalah tabel ringkasan dinamis yang dibuat dengan satu rumus. Hasil dari fungsi PIVOTBY serupa dengan output dari Tabel Pivot, namun tanpa pemformatan. Tabel yang dikembalikan oleh fungsi PIVOTBY sepenuhnya dinamis dan akan segera dihitung ulang ketika data sumber berubah. Berikut adalah daftar singkat fitur dan batasan PIVOTBY: Fungsi PIVOTBY membutuhkan 11 argumen, namun hanya empat argumen pertama yang diperlukan. Pada lembar kerja di bawah ini, kami menggunakan fungsi PIVOTBY untuk merangkum penjualan berdasarkan kota dan tahun. Rumus di sel F5 adalah: Untuk contoh ini, PIVOTBY dikonfigurasikan dengan tiga argumen sebagai berikut: Dengan input di atas, fungsi PIVOTBY menjumlahkan penjualan menurut kota dalam baris dan penjualan menurut tahun dalam kolom

Contoh

Basic Example

The PIVOTBY function takes 11 arguments, but only the first four are required. In the worksheet below, we use the PIVOTBY function to summarize sales

EXCEL 
=PIVOTBY(B5:B28,C5:C28,E5:E28,SUM)
PIVOTBY rows vs. columns

Compared to the GROUPBY function (which groups only by row) the main benefit of PIVOTBY is the ability to group by row and by column. This behavior is

EXCEL 
=PIVOTBY(B4:B28,C4:C28,E4:E28,SUM)
PIVOTBY rows vs. columns

In the second table, we swap rows and columns. Now we group rows by Year and columns by City:

EXCEL 
=PIVOTBY(C4:C28,B4:B28,E4:E28,SUM)
PIVOTBY with field headers

The field_header argument is optional. When field_header is omitted, PIVOTBY will try to detect headers in the source data automatically by testing va

EXCEL 
PIVOTBY(B4:B28,,E4:E28,SUM,3) // display field headers
PIVOTBY calculation options

In the worksheet below, we have a list of meal preferences for 100 employees in different departments and the cost per meal. We can use the PIVOTBY fu

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

Next, let's change the calculation to generate a total cost by meal, by using the SUM function instead of COUNTA. In the worksheet below, the formula

EXCEL 
=PIVOTBY(D4:D104,,E4:E104,SUM,1)

See Also

GROUPBY HSTACK VSTACK PERCENTOF