GROUPBY
Descrição
GROUPBY é uma função avançada que muitas vezes pode substituir COUNTIF(S), AVERAGEIF(S) e SUMIF(S). GROUPBY agrega dados e ordena os resultados com base na ordem que você especificar. O agrupamento é baseado em uma chave que você pode predefinir na tabela ou definir usando colunas na pasta de trabalho. O resultado é semelhante a uma tabela dinâmica classificada. Essa função geralmente é útil como parte do planejamento de headcount.
Quando você agrupa uma matriz de células que representam alguns valores de instância como cadeias de caracteres e outros como instâncias (variáveis de um relatório do Workday), GROUPBY baseia o agrupamento no nome da instância, não na ID da instância. GROUPBY agrupa nomes idênticos, mesmo quando as IDs da instância são diferentes.
Sintaxe
GROUPBY(
join_by_matrix
, join_by_matrix_column_indexes
, group_by_matrix
, group_by_matrix_column_indexes
, group_by_matrix_aggregate_column_indexes
, [group_by_matrix_aggregate_types]
) - join_by_matrix: matriz unidimensional de IDs (chaves) exclusivas que identifica as linhas a serem incluídas no agrupamento.
- join_by_matrix_column_indexes: posição inicial das teclas nojoin_by_matrix, ou o conjunto de colunas que compõem a chave correspondente (os valores de coluna a serem concatenados para criar a chave principal).
- group_by_matrix: a matriz de células a ser agrupada.
- group_by_matrix_column_indexes: posição da coluna que contém as chaves nogroup_by_matrix.
- group_by_matrix_aggregate_column_indexes: as posições das colunas que contêm valores a serem agrupados.
- group_by_matrix_aggregate_types: a operação a ser executada.Valor do argumentoOperação0SUM (por padrão)1COUNT de valores não nulos2COUNT de números diferentes de zero3Número MIN4Número MAX5Número AVG
Exemplo
Uma pasta de trabalho tem duas planilhas chamadas Trabalho e Organização. Esta é a planilha Working:
A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
1 | Organização de supervisão
| Centro de custo
| Empresa
| Localização
| Nome
| Jan (HC) de 2017
| Fev (HC) de 2017
| Mar (HC) de 2017
| Chave da organização
|
2 | Finance | CC-1 Finance | GMS | Pleasanton | Tim | 1 | 1 | 1 | FinanceCC-1 FinanceGMSPleasanton |
3 | Product Management | CC-2 Product | GMS | Boulder | Josh | 0 | 0 | 1 | Product ManagementCC-2 ProductGMSBoulder |
4 | Development | CC-3 Development | GMS-CA | Toronto | Brian | 1 | 1 | 1 | DevelopmentCC-3 DevelopmentGMS-CAToronto |
5 | Product Management | CC-2 Product | GMS | Boulder | Scott | 0 | 1 | 1 | Product ManagementCC-2 ProductGMSBoulder |
6 | Development | CC-3 Development | GMS-CA | Toronto | Lenny | 1 | 1 | 1 | DevelopmentCC-3 DevelopmentGMS-CAToronto |
7 | Sales | CC-4 Sales | GMS-UK | London | Andy | 0 | 1 | 1 | SalesCC-4 SalesGMS-UKLondon |
8 | Finance | CC-1 Finance | GMS | Pleasanton | Ratna | 1 | 1 | 1 | FinanceCC-1 FinanceGMSPleasanton |
9 | Sales | CC-4 Sales | GMS-UK | Toronto | Aidan | 0 | 0 | 1 | SalesCC-4 SalesGMS-UKToronto |
10 | Sales | CC-4 Sales | GMS-UK | London | Katie | 1 | 1 | 1 | SalesCC-4 SalesGMS-UKLondon |
11 | Total | 5 | 7 | 9 |
A fórmula de matriz GROUPBY está nas células E2:G6 da planilha Organização:
=GROUPBY('Organization'!H2:H6,1,'Working'!A2:I10,9,{6, 7, 8})
Como alternativa, em vez de gerar uma chave separadamente, como mostrado na coluna I acima e H abaixo, você pode especificar as colunas que quer que GROUPBY use como a chave:
=GROUPBY('Organization'!A2:G6,{1,2,3,4},'Working'!A2:H10,{1,2,3,4},{6, 7, 8})
A fórmula retorna estes resultados na organização da planilha:
A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
1 | Organização de supervisão
| Centro de custo
| Empresa
| Localização
| Jan (HC) de 2017
| Fev (HC) de 2017
| Mar (HC) de 2017
| Chave da organização
|
2 | Finance | CC-1 Finance | GMS | Pleasanton | 2 | 2 | 2 | FinanceCC-1 FinanceGMSPleasanton |
3 | Product Management | CC-2 Product | GMS | Boulder | 0 | 1 | 2 | Product ManagementCC-2 ProductGMSBoulder |
4 | Development | CC-3 Development | GMS-CA | Toronto | 2 | 2 | 2 | DevelopmentCC-3 DevelopmentGMS-CAToronto |
5 | Sales | CC-4 Sales | GMS-UK | London | 1 | 2 | 2 | SalesCC-4 SalesGMS-UKLondon |
6 | Sales | CC-4 Sales | GMS-UK | Toronto | 0 | 0 | 1 | SalesCC-4 SalesGMS-UKToronto |
7 | 5 | 7 | 9 |
Observe que a linha 7 na planilha Organização é um cálculo simples do número total de headcount; está fora da área de entrada do plano.
Observações
- Durante uma operação com valores de data, a função GROUPBY converte a data em um número de série antes de fazer o cálculo e não restaura o formato de exibição para uma data. Para mostrar um formato de data, selecione .
Funções relacionadas
ARRAYAREA