La funció SUBTOTALS en Excel ens ajuda a calcular el subtotal d’una llista de valors. L’interessant és que podem indicar a la funció SUBTOTALS el tipus d’operació que volem aplicar sobre els valors.
Sintaxi de la funció SUBTOTALS
• Núm_función (obligatori): Valor numèric (de l’1 al 11) que indica el tipus de funció que ha de ser utilitzada en el càlcul com SUMA, COMPTAR, MITJANA, etc.
• Ref1 (obligatori): El rang que conté els valors.
• Ref2 (opcional): Un segon rang amb valors addicionals a considerar. Aquest argument és opcional i fins l’argument 254 per proveir rangs addicionals.
A continuació la taula de valors numèrics que indiquen les operacions que volem fer amb la funció SUBTOTALS:
Exemple de la funció SUBTOTALS
Tinc una llista de valors en el rang A1: A10 i desitjo utilitzar la funció SUBTOTALS per calcular la mitjana per a això utilitzaré la fórmula següent:
Observa que en especificar el número 1 com el primer argument de la funció SUBTOTALS estem indicant que s’utilitzi la funció MITJANA. Si d’altra banda voldria obtenir la suma de dues llistes de nombres puc utilitzar la funció SUBTOTALS de la següent manera:
Files ocultes amb la funció SUBTOTALS
Un possible inconvenient de la funció SUBTOTALS és que al moment d’ocultar alguna de les files que tenen valors inclosos en l’operació es té un impacte en el resultat. A la següent imatge pots observar un exemple on faig un compte dels elements del rang A1: A10.
No obstant això, si ocultem les files 4 i 5, la funció SUBTOTALS no reflecteix cap canvi i segueix tenint en compte les files ocultes:
Si volem passar per alt els valors ocults podem fer ús d’un identificador de funció diferent per al primer argument de la funció SUBTOTALS:
Per l’exemple anterior, si utilitzem el valor 102 en lloc de 2 com el primer argument de la funció SUBTOTALS obtindrem el següent resultat:
En aquest cas la funció SUBTOTALS ha passat per alt els valors de les files ocultes. És important esmentar que aquesta funcionalitat de no prendre en compte els valors ocults només funcionarà amb files ocultes i no amb columnes ocultes.
Recorda que la funció SUBTOTALS a Excel sempre aplicarà la funció indicada sobre els rangs de cel·les proporcionats com a arguments.
SUBTOTALS en taules grans
Quan tens una taula de dades amb una gran quantitat d’informació, els subtotals en Excel ens poden ajudar a comprendre i interpretar millor la informació. Excel permet afegir subtotals d’una manera molt senzilla.
Suposem la següent taula de dades d’on vull obtenir els subtotals de vendes per cada mes:
El primer que he de fer és ordenar les dades per la columna sobre la qual s’obtindran els subtotals. Per a aquest exemple ordenaré les dades per la columna Mes. La columna s’ordena fent clic a qualsevol cel·la de la columna i posteriorment seleccionat la comanda Ordenar de més antics a més recents que es troba dins de la fitxa Inici.
La taula quedarà ordenada per mes.
El botó Subtotal en Excel
Per realitzar la inserció dels subtotals en Excel he de prémer la comanda Subtotal que es troba a la fitxa Dades dins del grup Esquema.
Excel mostrarà el quadre de diàleg Subtotals.
Ens em d’assegurar que la primera llista desplegable tingui seleccionada l’opció Mes ja que indica que per cada canvi de mes s’inserirà un subtotal. A més utilitzarém la funció Suma i la columna Vendes que apareixen seleccionades. En fer clic a D’acord s’inseriran els subtotals.
Observa com Excel ha inserit una fila nova que conté el subtotal per a cada mes. A més a l’esquerra del full de càlcul Excel col·loca controls addicionals que són útils per a amagar o mostrar els grups de dades d’acord amb els subtotals. En fer clic en algun d’ells el grup corresponent s’expandeix o es contreu:
