Les taules pivotants són bones per a tothom: es calculen ràpidament i es configuren de manera flexible, i el disseny es pot enrotllar amb elegància, si cal. Però també hi ha algunes mosca a la pomada, en particular, la incapacitat de crear un resum, on l'àrea de valor no hauria de contenir números, sinó text.
Intentem eludir aquesta limitació i inventem un "parell de crosses" en una situació similar.
Suposem que la nostra empresa transporta els seus productes en contenidors a diverses ciutats del nostre país i Kazakhstan. Els contenidors s'envien no més d'un cop al mes. Cada contenidor té un número alfanumèric. Com a dades inicials, hi ha una taula estàndard que recull els lliuraments, a partir de la qual cal fer algun tipus de resum per tal de veure clarament el nombre de contenidors enviats a cada ciutat i cada mes:
Per comoditat, fem que la taula amb les dades inicials sigui "intel·ligent" per endavant mitjançant l'ordre Inici – Format com a taula (Inici — Format com a taula) i posa-li un nom Entregues llengüeta constructor (Disseny). En el futur, això simplificarà la vida, perquè. es podrà utilitzar el nom de la taula i les seves columnes directament a les fórmules.
Mètode 1. El més fàcil: utilitzeu Power Query
Power Query és una eina molt potent per carregar i transformar dades a Excel. Aquest complement s'ha integrat a Excel de manera predeterminada des del 2016. Si teniu Excel 2010 o 2013, podeu descarregar-lo i instal·lar-lo per separat (completament gratuït).
Tot el procés, per a més claredat, l'he analitzat pas a pas en el següent vídeo:
Si no és possible utilitzar Power Query, podeu anar d'altres maneres: mitjançant una taula dinàmica o fórmules.
Mètode 2. Resum auxiliar
Afegim una columna més a la nostra taula original, on amb una fórmula senzilla calculem el nombre de cada fila de la taula:
Òbviament, cal -1, perquè tenim una capçalera d'una línia a la nostra taula. Si la vostra taula no es troba al principi del full, podeu utilitzar una fórmula una mica més complexa però universal que calculi la diferència entre els números de la fila actual i la capçalera de la taula:
Ara, de manera estàndard, construirem una taula dinàmica del tipus desitjat a partir de les nostres dades, però al camp de valor deixarem anar el camp Número de línia en lloc del que volem contenidor:
Com que no tenim diversos contenidors a la mateixa ciutat en el mateix mes, el nostre resum, de fet, no donarà la quantitat, sinó els números de línia dels contenidors que necessitem.
A més, podeu desactivar els totals generals i subtotals a la pestanya Constructor – Totals generals и Subtotals (Disseny: totals generals, subtotals) i al mateix lloc canvieu el resum a un disseny de taula més còmode amb el botó Informe de maqueta (Disposició de l'informe).
Així, ja estem a mig camí del resultat: tenim una taula on, a la intersecció de la ciutat i el mes, hi ha un número de fila a la taula font, on es troba el codi contenidor que necessitem.
Ara copiem el resum (al mateix full o un altre) i l'engangem com a valors, i després introduïm la nostra fórmula a l'àrea de valors, que extreu el codi del contenidor pel número de línia que es troba al resum:
function IF (SI), en aquest cas, comprova que la següent cel·la del resum no estigui buida. Si està buit, emet una cadena de text buida “”, és a dir, deixeu la cel·la en blanc. Si no està buit, extreu-lo de la columna Contenidor taula font Entregues contingut de la cel·la per número de fila mitjançant la funció ÍNDEX (ÍNDEX).
Potser l'únic punt no gaire obvi aquí és la paraula doble Contenidor a la fórmula. Una forma d'escriptura tan estranya:
Subministraments[[Contenidor]:[Contenidor]]
... només es necessita per fer referència a la columna Contenidor era absolut (com una referència amb signes $ per a taules normals "no intel·ligents") i no es va lliscar a les columnes veïnes en copiar la nostra fórmula a la dreta.
En el futur, en canviar les dades de la taula d'origen Entregues, hem de recordar actualitzar el nostre resum auxiliar amb números de línia fent clic amb el botó dret sobre ell i escollint l'ordre Actualitza i desa (Actualització).
Mètode 3. Fórmules
Aquest mètode no requereix la creació d'una taula dinàmica intermèdia i l'actualització manual, però utilitza l'"arma pesada" d'Excel: la funció SUMMESLIMN (SUMIFS). En lloc de buscar números de fila en un resum, podeu calcular-los mitjançant aquesta fórmula:
Amb cert volum extern, de fet, aquest és un cas d'ús estàndard per a la funció de suma selectiva SUMMESLIMNA que suma els números de fila per a la ciutat i el mes donats. De nou, com que no tenim diversos contenidors a la mateixa ciutat en el mateix mes, la nostra funció, de fet, no donarà la quantitat, sinó el número de línia en si. I després la funció ja coneguda del mètode anterior ÍNDEX També podeu extreure codis de contenidors:
Per descomptat, en aquest cas, ja no cal pensar a actualitzar el resum, sinó a taules grans, la funció SUMMESLI pot ser notablement lent. Aleshores, haureu de desactivar l'actualització automàtica de fórmules o utilitzar el primer mètode: una taula dinàmica.
Si l'aspecte del resum no és molt adequat per al vostre informe, podeu extreure'n els números de fila a la taula final no directament, com vam fer nosaltres, sinó utilitzant la funció OBTENIR.DADES.TAULA.PIVOT (OBTENIR.DADES.PIVOT). Com fer-ho es pot trobar aquí.
- Com crear un informe mitjançant una taula dinàmica
- Com configurar càlculs en taules dinàmiques
- Recompte selectiu amb SUMIFS, COUNTIFS, etc.