Quan el vostre full de treball d'Excel conté files amagades, dades filtrades o dades agrupades, feu servir la funció SUBTOTAL d'Excel. La funció SUBTOTAL pot incloure o excloure valors ocults en els càlculs. A més de trobar el total d'un grup de dades, Excel pot calcular la mitjana, màxima, mínima, desviació estàndard i variància de les vostres dades. A continuació s'explica com inserir subtotals a Excel.
Les instruccions d'aquest article s'apliquen a Excel per a Microsoft 365, Excel 2019 i Excel 2016.
Sintaxi de la funció SUBTOTAL
Utilitzeu la funció SUBTOTAL a Excel per resumir els valors d'un full de treball de diferents maneres. És especialment útil quan el vostre full de treball conté files amagades que voleu incloure al càlcul.
La sintaxi de la funció SUBTOTAL és: SUBTOTAL(núm_funció, ref1, ref2, …)
L'argument function_num és obligatori i especifica el tipus d'operació matemàtica que cal utilitzar per al subtotal. La funció SUBTOTAL pot afegir nombres, calcular el valor mitjà dels nombres seleccionats, trobar els valors màxim i mínim d'un interval, comptar el nombre de valors d'un interval seleccionat i molt més.
La funció SUBTOTAL ignora les cel·les que no contenen dades i les cel·les amb valors no numèrics.
Aquest argument és un número i depèn de si voleu incloure files amagades al resultat o excloure les files amagades del resultat. Aquestes files es poden amagar o ocultar manualment mitjançant un filtre.
Els arguments function_num inclouen:
| Tasca de funció | function_num | function_num |
|---|---|---|
| (inclou valors ocults) | (exclou els valors ocults) | |
| MITJANA | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| PRODUCTE | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUMA | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Els arguments de referència function_num de l'1 a l'11 només inclouen valors a les files amagades quan s'utilitza l'ordre Amaga per amagar les files. Quan s'utilitza l'ordre Filtre, els càlculs del SUBTOTAL no inclouen els resultats del filtre ocults.
L'argument ref1 és necessari. Aquestes són les cel·les que s'utilitzen per calcular els resultats de l'argument num_funció seleccionat. Aquest argument pot ser un valor, una sola cel·la o un rang de cel·les.
Els arguments ref2, … són opcionals. Aquestes són cel·les addicionals que s'inclouen al càlcul.
Utilitza la funció SUBTOTAL amb files amagades
Les funcions Excel es poden introduir manualment o amb l'ajuda del quadre de diàleg Arguments de funció. Per il·lustrar com introduir la funció manualment mitjançant la barra de fórmules, l'exemple següent utilitza l'argument COUNT_núm_funció per comptar el nombre de valors a les files visibles i tant a les files visibles com a les ocultes.
Per utilitzar la funció SUBTOTAL per comptar el nombre de files d'un full de treball:
- Comenceu amb un full de treball que conté diverses files de dades.
-
Seleccioneu la cel·la que contindrà el recompte de files visibles.
-
A la barra de funcions, introduïu =SUBTOTAL. Mentre escriviu, Excel suggereix una funció. Feu doble clic a la funció SUBTOTAL.
Per utilitzar el quadre de diàleg Arguments de funció per entrar a la funció SUBTOTAL, aneu a Fórmules i seleccioneu Matemàtiques i activació >SUBTOTAL.
Image -
Al menú desplegable que apareix, feu doble clic a l'argument 102 – COUNT function_num.
Image -
Escriviu una coma (,).
Image -
Al full de treball, seleccioneu les cel·les que voleu incloure a la fórmula.
Image -
Premeu Retorn per veure el resultat a la cel·la que heu seleccionat al pas 2.
Image - Seleccioneu la cel·la que contindrà el recompte de les files visibles i ocultes.
- A la barra de funcions, introduïu =SUBTOTAL. Mentre escriviu, Excel suggereix una funció. Feu doble clic a la funció SUBTOTAL.
- Al menú desplegable que apareix, feu doble clic a l'argument 2 – COUNT function_num i, a continuació, escriviu una coma (,).
-
Al full de treball, seleccioneu les cel·les que voleu incloure a la fórmula i, a continuació, premeu Retorn.
Image -
Amaga diverses files de dades. En aquest exemple, s'han amagat les files amb només vendes per sota dels 100.000 $.
Image
Utilitza la funció SUBTOTAL amb dades filtrades
L'ús de la funció SUBTOTAL a les dades filtrades ignora les dades de les files que s'han eliminat pel filtre. Cada vegada que canvia el criteri de filtre, la funció es torna a calcular per mostrar el subtotal de les files visibles.
Per utilitzar la funció SUBTOTAL per veure les diferències en els resultats del càlcul mentre es filtren les dades:
-
Crea fórmules de SUBTOTAL. Per exemple, creeu fórmules per determinar el subtotal i els valors mitjans de les dades filtrades.
No importa si utilitzeu l'argument function_num per a files visibles o ocultes. Tots dos arguments proporcionen el mateix resultat en dades filtrades.
Image - Seleccioneu qualsevol cel·la del conjunt de dades.
-
Vés a Inici i, a continuació, selecciona Ordenar i filtrar > Filtre.
Image -
Feu servir les fletxes desplegables per filtrar les dades del full de treball.
Image -
Observeu com canvien els valors cada vegada que seleccioneu criteris de filtre diferents.
Image
Utilitza la funció SUBTOTAL amb dades agrupades
Quan les dades s'agrupen, hi ha una manera d'aplicar la funció SUBTOTAL a cada grup individual i després calcular el total general per a tot el conjunt de dades.
- Seleccioneu qualsevol cel·la del conjunt de dades.
-
Seleccioneu Dades > Subtotal per obrir el quadre de diàleg Subtotal.
Image - Seleccioneu la fletxa desplegable A cada canvi dei trieu l'agrupació a la qual es calcularà cada subtotal.
- Seleccioneu la fletxa desplegable Utilitza la funció i trieu una funció_num.
- A la llista Afegeix un subtotal a, seleccioneu la columna on s'aplicarà la fórmula.
-
Seleccioneu D'acord.
Image -
S'insereixen subtotals per a cada grup de dades i s'insereix un total general a la part inferior del conjunt de dades.
Image - Per canviar la funció_num, ress alteu qualsevol cel·la del conjunt de dades i seleccioneu Data > Subtotal. A continuació, feu les vostres eleccions al quadre de diàleg Subtotal.
