Saltar al contenido
GridBelt
  • Prueba de Excel
  • Práctica
  • Programas
  • Precios
Iniciar sesión
Empezar la prueba gratuita
  • Prueba de Excel
  • Práctica
  • Programas
  • Precios
Iniciar sesiónCrear cuenta
Empezar la prueba gratuita
GridBelt

Practica Excel en tu navegador.

Prueba de Excel

  • Nivel 1: Principiante
  • Nivel 2: Intermedio
  • Nivel 3: Experto
  • Prueba de Excel en 7 días
  • Clasificación
  • Prueba de Excel para entrevistas
  • Evaluación de Excel de Indeed

Práctica

  • Todos los ejercicios
  • Programas
  • Para principiantes

Guías

  • Fórmulas de Excel
  • Atajos de Excel

Empresa

  • Acerca de
  • Precios
  • Cómo corregimos

© GridBelt. Todos los derechos reservados.

  • Política de privacidad
  • Términos de uso
  • Política de reembolso

Excel es una marca comercial de Microsoft Corporation. Indeed, TestGorilla, SHL y otras marcas son propiedad de sus respectivos titulares. GridBelt es un sitio de práctica independiente y no está afiliado a Microsoft, TestGorilla, SHL ni Indeed, ni respaldado ni patrocinado por ellos.

  1. Práctica
  2. /
  3. Pivot tables

GROUPBY practice: tickets per agent with a filter, largest first

  • Experto
  • ~9 min
  • Pivot tables
  • Excel 365

Enunciado en inglés

The service desk logged 220 tickets in October 2026 (A2:D221): ticket ID, opening date, the agent who handled it and its priority — Low, Normal, High or Urgent. The team lead wants the PivotTable she would build in Excel: Priority as the report filter set to High, Agent in Rows, Count of Ticket ID in Values, no grand total, and the busiest agent on top. This practice grid has no PivotTable command, so you build the report with one GROUPBY formula instead: the filter value sits in G1 and the report starts in F4, under the headings in F3:G3.

Qué tienes que hacer

  1. 1G1 is the report filter (Priority = High), like the Filters area above a PivotTable. Click F4, where the report starts.
  2. 2Start with the counting part: GROUPBY(C2:C221,A2:A221,COUNTA, … — the agents are the row field, the ticket IDs are the values and COUNTA counts them, which is a PivotTable's "Count of Ticket ID". COUNT would return 0 for every agent, because ticket IDs are text.
  3. 3Add the optional arguments in their order: field_headers 0 (the ranges have no header row), total_depth 0 (no Total row), sort_order 1 (A to Z for now) and filter_array D2:D221=G1 (only the rows whose priority equals G1): =GROUPBY(C2:C221,A2:A221,COUNTA,0,0,1,D2:D221=G1).
  4. 4Sort by the count, largest first, by wrapping it in SORT: =SORT(GROUPBY(C2:C221,A2:A221,COUNTA,0,0,1,D2:D221=G1),2,-1) — column 2 of the result, -1 for descending. Excel 365 can also sort inside GROUPBY with sort_order -2, but this practice grid ignores the column number in sort_order, so use SORT here; it works the same in Excel.
  5. 5Press Enter and keep the cells below and to the right of F4 empty, or the result shows #SPILL!. Type the 0 for total_depth rather than leaving it empty: in Excel an empty argument means "automatic", which brings the Total row back.
  6. 6Try the filter: change G1 to Urgent and watch the report re-rank itself, then put High back and click Check.

Celdas de respuesta

Corrección

Escribe tu fórmula en la cuadrícula, presiona Enter y luego haz clic en Comprobar. Evaluamos el valor de cada celda de respuesta y la fórmula que lo produce.

Tareas

  1. Sin comprobarIn F4, write one formula with GROUPBY that counts the tickets (the ticket IDs in A2:A221) per agent (C2:C221) for the priority in G1 only, with no total row, sorted by the count from largest to smallest.

Para las tareas de hoja de cálculo se recomienda una pantalla grande. La hoja se desplaza dentro de este recuadro.

StartInsertFormulasDataView
A1fxPreparando tu libro…
A
B
C
D
E
F
G
H
I
J
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Escribe tu fórmula en la hoja y luego comprueba.

Sobre este ejercicio

Un conjunto de datos de 220 filas en una hoja, una celda de respuesta, unos 9 minutos. Se corrige en tu navegador: el valor y la fórmula que lo produce, con comentarios que te dicen qué arreglar.

Cómo resolverlo

  • F4=SORT(GROUPBY(C2:C221,A2:A221,COUNTA,0,0,1,D2:D221=G1),2,-1)

Each optional argument of GROUPBY is a PivotTable setting: COUNTA as the function is "Count of Ticket ID", total_depth 0 switches the grand total off, and filter_array D2:D221=G1 is the report filter — only the rows whose priority matches G1 take part. SORT(…,2,-1) then does what "Sort Largest to Smallest" does on the count column. Because the filter value lives in a cell, typing another priority into G1 re-filters and re-ranks the whole report at once.

The PivotTable route in Excel: Priority to Filters (pick High), Agent to Rows, Ticket ID to Values (a text field is counted: "Count of Ticket ID"), Design → Grand Totals → Off for Rows and Columns, then right-click a count → Sort → Sort Largest to Smallest. Pitfall 1: sorting a GROUPBY that still has its Total row sorts the Total in with the agents — it lands on top. Switch the totals off first (total_depth 0) or drop them with DROP(…,-1). Pitfall 2: an empty argument is not a zero. In Excel, GROUPBY(…,COUNTA,0,,…) means "automatic" totals and shows the Total row; type the 0. Pitfall 3: COUNT counts numbers only, so it returns 0 for text IDs; COUNTA counts every non-empty value. Pitfall 4: ties. In this grid SORT keeps tied rows in the order GROUPBY gave them (A to Z); if the order of ties matters, give SORTBY a second key, e.g. =LET(g,GROUPBY(C2:C221,A2:A221,COUNTA,0,0,1,D2:D221=G1),SORTBY(g,CHOOSECOLS(g,2),-1,CHOOSECOLS(g,1),1)). Excel 365 can also sort inside GROUPBY: =GROUPBY(C2:C221,A2:A221,COUNTA,0,0,-2,D2:D221=G1) sorts by the 2nd column descending there, while this practice grid ignores the column number and sorts the names Z to A. Several conditions combine with *: (D2:D221=G1)*(B2:B221>=DATE(2026,10,15)). GROUPBY needs Excel for Microsoft 365.

Otras formas válidas

  • =SORT(GROUPBY(FILTER(C2:C221,D2:D221=G1),FILTER(A2:A221,D2:D221=G1),COUNTA,0,0),2,-1) — filter the rows first, then group
  • =LET(g,GROUPBY(C2:C221,A2:A221,COUNTA,0,0,1,D2:D221=G1),SORTBY(g,CHOOSECOLS(g,2),-1)) — SORTBY on the count column
  • =GROUPBY(C2:C221,A2:A221,COUNTA,0,0,-2,D2:D221=G1) — Excel 365 only: sorts by the count inside GROUPBY (this practice grid ignores the column number in sort_order)

Errores frecuentes

Counting with COUNT instead of COUNTA: the ticket IDs are text, so every agent shows 0 and the ranking is meaningless; a PivotTable's "Count" of a text field is COUNTA.

Cómo corregimos

Se acepta cualquier fórmula que produzca el resultado correcto, salvo que la tarea nombre una función: entonces otra función obtiene media puntuación. Comparamos los valores con una pequeña tolerancia y, en el texto, ignoramos mayúsculas y espacios. Un número correcto escrito a mano en lugar de una fórmula obtiene media puntuación. Las tareas de varias celdas obtienen la puntuación completa con el 100% de las celdas correctas y media puntuación desde el 50%. Cuando importa, también cambiamos una celda de entrada en una copia de tu hoja y comprobamos que tu fórmula se adapta.

Respuestas calculadas con las reglas de Excel 365 y comprobadas en nuestro corrector el 20 de noviembre de 2026. Autor: GridBelt team.

En estos programas

  • Excel test in 7 days, Plan del nivel 3Día 6
  • Excel for Data AnalystsDía 8

Ejercicios relacionados

  • A pivot-style sales report by region with GROUPBYIntermedio, ~6 min
  • Products ranked by sales with SORTBYExperto, ~8 min
  • Orders per region and month with COUNTIFSIntermedio, ~7 min

Referencia de la función

Documentación oficial

Siguiente ejercicio

Share of revenue by channel: % of Grand Total with formulas