Si uses l'Excel cada dia, segur que alguna vegada t'has barallat amb fórmules infinites, referències creuades i llistes que es queden curtes quan afegeixes una fila nova. Amb l'arribada de les matrius dinàmiques i les fórmules de matriu moderna , tot aquest embolic se simplifica: una única fórmula pot generar molts resultats, ajustar-se sola a la mida de les dades i recalcular-se sense que hagis de tocar res més.
Les fórmules amb matrius a Excel i les funcions dinàmiques han canviat completament la manera d'analitzar dades: ara pots filtrar, ordenar, crear seqüències, detectar valors únics o generar nombres aleatoris en blocs sencers de cel·les, sense macros ni combinacions rares de tecles. Anem a veure amb calma què són, en què es diferencien les matrius clàssiques de les dinàmiques, quines funcions noves tens a la teva disposició i com esprémer-les de debò en el teu dia a dia.
Què és una fórmules amb matrius a Excel (model clàssic)?
En la seva versió tradicional, una fórmula de matriu és una fórmula especial que treballa amb conjunts de valors en comptes d'una sola cel·la. En lloc d'operar sobre A1 o B2 de manera aïllada, podeu processar rangs complets i tornar un únic resultat o diversos, segons com l'hagis plantejat.
Aquestes fórmules es caracteritzen perquè operen amb matrius de dades internes : prenen múltiples valors d'entrada, fan càlculs en bloc (sumes, comparacions, condicions, etc.) i tornen una sortida que també pot ser un conjunt de valors. Tradicionalment, l'Excel mostrava aquestes fórmules amb claus { } a la barra de fórmules, indicant que era una fórmula de matriu.
Per introduir una fórmula d'aquest tipus a les versions antigues, no n'hi havia prou amb prémer Intro. Calia confirmar la fórmula amb la combinació Ctrl + Maj + Entrar . En fer-ho, l'Excel entenia que havia de tractar l'expressió com una matriu i envoltava la fórmula amb claus automàticament. Si escrivies tu mateix les claus, no funcionava, havien d'aparèixer en fer servir aquesta combinació de tecles.
Les fórmules de matriu clàssiques han estat sempre molt útils per a càlculs complexos en múltiples cel·les : operacions matemàtiques avançades, filtres condicionals sense funcions especials, comparacions massives entre rangs o resums d'informació sense necessitat de columnes auxiliars. Això sí, també tenen la cara B: són més difícils d'entendre, de depurar i, si se n'abusa en llibres grans, poden alentir el rendiment del full.
Un exemple típic de fórmula de matriu tradicional seria {=SUMA(A1:A5*B1:B5)} , on es multipliquen element a element els rangs A1:A5 i B1:B5, i després se sumen tots els productes. Un altre exemple clàssic seria {=MAX(A1:A5+B1:B5)} , en què se sumen les dues columnes i es pren el valor màxim del resultat.
Què és una fórmules amb matrius dinàmiques a Excel?
Amb les versions més recents (Excel per a Microsoft 365 i Excel 2021), Microsoft va introduir les fórmules de matriu dinàmica , que són l'evolució natural de les matrius clàssiques. La gran diferència és que aquestes fórmules admeten que el resultat sigui un conjunt de cel·les i, en lloc de forçar-te a seleccionar el rang de sortida i fer servir Ctrl + Maj + Entrar, es desborden automàticament cap a les cel·les contigües.
Quan parlem de matriu dinàmica ens referim a qualsevol fórmula que pot tornar un rang de mida variable i que l'Excel és capaç de bolcar (o vessar) sobre el full sense que hagis de fer res més. Aquestes fórmules s'escriuen en una sola cel·la i, segons el càlcul, omplen totes les cel·les necessàries amb el resultat complet.
Les matrius dinàmiques es recolzen en noves funcions dissenyades per treballar amb aquest comportament de desbordament: FILTRAR, ORDENAR, UNICOS, SEQÜÈNCIA, ORDENARPOR i MATRIZALEAT o RANDARRAY , entre d'altres. Totes poden tornar diversos valors d'una tacada i estan pensades just per manejar matrius de forma natural, sense combinacions de tecles especials.
Una diferència clau davant de les fórmules de matriu de tota la vida és que ja no necessites Ctrl + Maj + Entrar . Escrius la fórmula normalment, prems Intro i Excel s'encarrega de tot: calcula la mida de la sortida i la vessa en tantes files i columnes com calguin, marcant visualment el rang de desbordament quan selecciones la cel·la original.
Un altre avantatge important és que aquestes fórmules ajusten la seva mida automàticament : si canvien les dades d'origen (afegeixes o lleves files, modifiques criteris, etc.), el rang vessat creix o es redueix sol, sense que hagis d'arrossegar fórmules o tornar a definir rangs.
Què vol dir el desbordament (spill) d'una fórmula?
El desbordament és el comportament pel qual una fórmula genera diversos resultats i Excel els col·loca en cel·les veïnes . Imagina que a la cel·la F2 escrius una fórmula com =ORDENAR(D2:D11,1,-1) per ordenar un llistat de 10 elements en ordre descendent: el resultat no es queda només en F2, sinó que es vessa des de F2 fins a F11 , ocupant tot el rang que necessita.
En aquest context, a les fórmules que poden tornar un nombre canviant d'elements se les coneix com a fórmules de matriu dinàmica . I quan el resultat ja s'està expandint correctament cap a diverses cel·les, acostumem a parlar de fórmula de matriu desbordada . La fórmula en si sol existeix a la cel·la superior esquerra del rang, però el seu efecte es veu a tot el bloc de sortida.
Quan confirmeu una fórmula d'aquest tipus, Excel calcula la mida del rang de sortida i reparteix els resultats per totes les cel·les que entrin en aquesta àrea. Si seleccioneu qualsevol cel·la del rang de desbordament, l'aplicació ressalta tot el bloc amb una vora especial, perquè veieu clarament que aquestes dades depenen d'una única fórmula matriu. La vora desapareix en seleccionar una altra cel·la aliena al rang.
Hi ha un detall important: només pots editar la primera cel·la del rang vessat . Si seleccioneu una altra cel·la dins del desbordament, veureu la fórmula a la barra de fórmules, però atenuada, com a text «fantasma», i no podreu modificar-la des d'aquí. Per canviar-la, haureu d'anar a la cel·la superior esquerra, editar la fórmula i prémer Intro; L'Excel actualitzarà automàticament totes les cel·les del desbordament.
Quant a les taules d'Excel, heu de tenir en compte que no admeten fórmules de matriu desbordades dins de la mateixa taula . Les fórmules dinàmiques s'han de col·locar a la quadrícula normal, fora de les taules estructurades, ja que aquestes estan pensades per gestionar dades per files i columnes, no perquè una fórmula s'expandeixi lliurement.
Error #DESBORDAMENT! (#SPILL!) i problemes habituals
Una limitació clau de les matrius dinàmiques és que necessiten espai lliure per bolcar resultats . Si hi ha qualsevol valor, fórmula o format que interfereixi en alguna de les cel·les on l'Excel hauria d'escriure el rang vessat, la fórmula donarà un error del tipus #DESBORDAMENT! (#SPILL! en anglès). Aquest error indica que hi ha un bloqueig al rang de sortida.
Visualment, l'Excel sol mostrar l'àrea on s'hauria d'expandir la fórmula marcada amb una vora de punts sobre les cel·les ocupades . És una manera ràpida de veure exactament quins valors estan impedint el desbordament. Per solucionar-ho, només cal esborrar o moure les dades que destorben; així que el rang queda lliure, la fórmula es vessa correctament.
Un altre aspecte important és que, si fas referència des d'una altra fórmula a una matriu dinàmica, convé fer servir l'operador de rang desbordat . Per exemple, si la teva fórmula original està en A2 i es vessa cap avall, hauries d'escriure A2# en referenciar-la, de manera que l'Excel entengui que ha de prendre tot el rang generat, no només la cel·la A2 aïllada.
També és important recordar que les matrius dinàmiques entre llibres tenen suport limitat : l'Excel només les maneja bé si tots dos arxius estan oberts. Si es tanca el llibre d'origen, les fórmules que depenen d'aquesta matriu dinàmica poden tornar un error #REF! en recalcular-se, ja que no tenen accés al rang complet.
Diferències entre fórmules de matriu clàssiques i dinàmiques
Excel manté les fórmules de matriu tradicionals (les de Ctrl + Maj + Entrar) per motius de compatibilitat amb llibres antics , però la filosofia actual passa per utilitzar les noves matrius dinàmiques sempre que sigui possible. Les diferències entre ambdós models són força clares.
D'una banda, les fórmules de matriu clàssiques s'introdueixen seleccionant per endavant el rang de sortida i confirmant amb Ctrl+Maj+Entrar. No es desborden de forma automàtica, no canvien la mida de sortida per si mateixes i, en molts casos, el resultat està limitat a un únic valor , encara que internament treballin amb diverses cel·les. Si la zona de sortida és massa petita, poden truncar resultats o tornar errors com #N/A.
A l'altre extrem, les fórmules de matriu dinàmica s'escriuen en una sola cel·la, es confirmen amb Intro normal i l'Excel vessa la sortida cap a tantes cel·les com necessiti. Si canvieu les dades d'entrada, el rang de sortida s'engrandeix o redueix sense que hàgiu d'editar la mida. A més, qualsevol fórmula nova que retorni diversos resultats es desborda automàticament , sense passos extra.
Un problema històric de les fórmules CSE era l'anomenada “ruptura CSE” : quan diverses cel·les d'una mateixa matriu depenien entre si, l'Excel intentava calcular-les de forma individual, i en alguns escenaris es podien generar resultats inconsistents. Les matrius dinàmiques no es comporten així; quan hi ha una referència circular o un problema similar, l'Excel ho avisa com a tal perquè puguis corregir-lo, en lloc d'intentar «salvar» el càlcul.
A més, les noves fórmules de matriu dinàmica es modifiquen de manera més senzilla : només heu de tocar la cel·la d'origen. En canvi, amb les CSE clàssiques, si volies canviar alguna cosa al rang de matriu, havies de seleccionar tot el bloc i editar el conjunt. També es prohibeix inserir o eliminar files i columnes que travessen un rang actiu de fórmula CSE; primer cal treure aquesta fórmula per poder modificar lestructura.
Intersecció implícita i operador @ al nou Excel
Abans que existissin les matrius dinàmiques, quan una fórmula podia tornar diverses cel·les però s'introduïa en una única cel·la, l'Excel aplicava silenciosament una «intersecció implícita» . És a dir, reduïa la matriu de sortida a un sol valor, normalment el que coincidia amb la fila o columna de la cel·la on hi havia la fórmula, sense que l'usuari en fos gaire conscient.
Amb l'arribada de les matrius dinàmiques, l'Excel fa més explícit aquest comportament mitjançant l' operador @ . Quan una funció que en versions anteriors tornava una matriu s'obre en un Excel modern, pot aparèixer automàticament precedida de @ per indicar que s'està prenent només la intersecció implícita i no tota la matriu. Això ajuda a entendre on es podria produir un desbordament si s'eliminés aquest operador.
De la mateixa manera, si creeu una fórmula amb matrius dinàmiques en la versió actual i després l'obreu en un Excel antic, és possible que es representi com a fórmula de matriu heretada si no s'usa l'operador @. La idea és que els llibres segueixin sent compatibles entre versions, però que en la nova experiència quedi clar quines fórmules realment treballen amb rangs complets i quins s'han reduït a una intersecció puntual.
Funcions clau de matrius dinàmiques: FILTRAR, RANDARRAY, SEQÜÈNCIA, ORDENAR i ORDENARPOR
L'autèntic salt de qualitat ve de la mà de les funcions noves que treballen amb matrius dinàmiques de manera nativa. Cadascuna resol problemes que abans exigien fórmules molt enrevessades o macros , i ara se solucionen amb una instrucció força llegible.
FILTRAR: extreure només el que necessites
La funció FILTRAR (FILTER en anglès) permet obtenir un subconjunt de dades que compleixi certs criteris sense necessitat d'aplicar filtres manuals, fer servir taules dinàmiques o programar res. Pots indicar-li que mostri només els clients amb una puntuació determinada, les vendes d'un mes concret o els empleats per sobre d'una edat, i el resultat es vessa en un rang que s'actualitza només en canviar les dades originals.
Una forma típica d'usar-la seria una cosa com =FILTRAR(A2:C15,(A2:A15=F4)*(C2:C15=G4),»») , on es combinen condicions en diverses columnes. El resultat és una matriu dinàmica de files que compleixen els criteris establerts. A més, pots combinar-la amb altres funcions, per exemple ORDENARPOR, perquè el filtratge i l'ordenat succeeixin dins d'una única fórmula.
RANDARRAY / MATRIZALEAT: números aleatoris a granel
Amb RANDARRAY (MATRIZALEAT en espanyol), pots generar de cop una matriu de números aleatoris de tantes files i columnes com necessitis. Ja no cal copiar fórmules amb ALEATORI() o ALEATORI.ENTRE() per mitjà full: una sola instrucció crea tot el bloc.
Aquesta funció permet escollir si vols nombres enters o decimals, definir límits mínim i màxim i especificar dimensions exactes (nombre de files i columnes). És una eina molt útil per a simulacions, models financers, exercicis de mostreig o fins i tot per generar identificadors temporals i dades de prova en qüestió de segons.
SEQÜÈNCIA: llistes i calendaris automàtics
La funció SEQÜÈNCIA genera sèries de números (o dates, si les combines amb altres funcions) que s'expandeixen i es contrauen de manera dinàmica. És ideal per numerar registres, construir calendaris, planificar setmanes laborals o crear llistes correlatives sense arrossegar el farciment manualment.
Amb SEQÜÈNCIA podeu indicar quantes files i columnes voleu, el valor inicial i l'increment. Per exemple, podeu crear una sèrie de dates de dilluns a divendres integrant SEQÜÈNCIA amb funcions de data, de manera que la llista es generi d'una sola vegada i s'adapti si afegiu o traieu files associades a les vostres dades.
ORDENAR i ORDENARPOR: dades sempre en ordre
La funció ORDENAR (SORT) us permet reordenar un rang per una o diverses columnes , en sentit ascendent o descendent, sense modificar la taula original. El resultat es bolca en un rang a part que s'actualitzarà quan canviïn les dades d'origen, per la qual cosa sempre pots tenir un llistat ordenat «en mirall».
Per la seva banda, ORDENARPOR (SORTBY) va un pas més enllà: permet ordenar un conjunt de dades segons els valors duna altra columna o dun altre rang . Per exemple, podries ordenar una llista de productes per les vendes registrades a una altra taula, o un llistat d'alumnes per la nota obtinguda en un camp diferent, tot plegat en una matriu dinàmica que es manté al dia.
Combinant ORDENAR o ORDENARPOR amb FILTRAR i SEQÜÈNCIA, pots aconseguir efectes de nivell avançat: taules que s'autoordenen i autofiltren en funció de criteris canviants, sense necessitat de macros ni d'actualitzar res a mà.
Exemples pràctics de fórmules de matriu avançades (model clàssic)
Tot i que les matrius dinàmiques són l'aposta actual, moltes tècniques clàssiques continuen sent útils, i s'entenen millor veient exemples concrets de fórmules de matriu avançades que s'han fet servir durant anys.
Un cas típic és sumar un rang que conté errors. La funció SUMA, per si sola, falla si el rang inclou un valor com a #N/A. Per evitar-ho, es pot fer servir una fórmula de l'estil =SUMA(SI(ESERROR(Dades),»»,Dades)) , que crea internament una matriu en què se substitueixen els errors per cadenes buides i es deixen intactes els valors vàlids. SUMA treballa llavors sobre aquesta matriu filtrada i obté el total sense ensopegar amb els errors.
Un altre exemple pràctic és explicar quants errors hi ha en un rang . Una fórmula clàssica seria =SUMA(SI(ESERROR(Dades),1,0)), que genera una matriu de 1 i 0 (un per cada cel·la amb error, zero per cada cel·la correcta) i després suma aquests valors. Es pot simplificar ometent el tercer argument de SI: =SUMA(SI(ESERROR(Dades),1)), ja que quan la condició és falsa, SI torna FALS, que després es pot avaluar com a 0. Fins i tot hi ha una versió més compacta: =SUMA(SI(ESERROR(Dades)*1)) , aprofita 0.
Les fórmules de matriu també són molt potents per sumar valors segons condicions . Per exemple, =SUMA(SI(Vendes>0;Vendes)) suma només els valors positius del rang Vendes, ignorant automàticament els que no compleixen la condició. Per combinar diverses condicions del tipus «i», es pot utilitzar alguna cosa com =SUMA((Vendes>0)*(Vendes<=5)*(Vendes)), que suma únicament els valors més grans que 0 i menors o iguals que 5, sempre que la cel·la tingui dades numèriques.
Si el que cal és una lògica de tipus «o», es pot recórrer a expressions com =SUMA(SI((Vendes<5)+(Vendes>15);Vendes)) , que sumen els valors menors que 5 i més grans que 15 en un sol càlcul. Tot i que Excel té funcions Y i O, a les fórmules de matriu solen ser més útils aquestes combinacions de multiplicacions i sumes de condicions, perquè generen matrius de resultats en lloc d'un únic VERITABLE o FALS.
Un altre ús molt estès és calcular una mitjana excloent zeros . Amb =PROMEDI(SI(Vendes<>0;Vendes)) es crea una matriu només amb els valors diferents de 0 i es passa a PROMEDIO, que fa el càlcul sense que les cel·les amb zero distorsionin el resultat.
Les fórmules de matriu permeten també comparar rangs complets. Per exemple, comptar quantes diferències hi ha entre dues àrees de la mateixa mida , MisDatos i TusDatos, amb =SUMA(SI(MisDatos=TusDatos,0,1)). Aquesta fórmula genera una matriu de 0 i 1 (zero quan coincideixen, un quan hi ha diferència) i torna la suma d'aquestes diferències. Es pot simplificar escrivint =SUMA(1*(MisDades<>TusDatos)), que es recolza de nou en l'equivalència VERITABLE*1=1 i FALS*1=0.
També és possible localitzar la posició del valor màxim en un rang . Una fórmula típica seria =MIN(SI(Dades=MAX(Dades),FILA(Dades),»»)), que construeix una matriu amb els números de fila on es troba el valor màxim i cadenes buides a la resta. MIN torna la fila més petita, és a dir, la primera aparició del màxim. Si a més vols la direcció de la cel·la, pots recórrer a =DIRECCIÓ(MIN(SI(Dades=MAX(Dades),FILA(Dades),»»)),COLUMNA(Dades)) , que combina aquesta fila amb la columna del rang Dades.
Rang de desbordament: com es veu al full
Quan introduïu una funció que admet matrius dinàmiques, com =MAYUSC(E7:E19) , veureu que el resultat es vessa automàticament pel rang corresponent . La fórmula només es troba realment a la primera cel·la on la vas escriure, però totes les cel·les del rang de sortida mostren el resultat transformat (en aquest cas, el text en majúscules).
Si seleccioneu qualsevol cel·la del rang de desbordament, comprovareu que la fórmula apareix atenuada a la barra de fórmules , indicant que en aquesta cel·la no hi ha una fórmula pròpia, sinó un resultat calculat per la matriu dinàmica original. Per canviar alguna cosa, haureu d'editar sempre la cel·la d'origen; la resta del rang s'actualitzarà automàticament quan confirmeu el canvi.
Bones pràctiques i compatibilitat entre versions
Als llibres moderns, el recomanable és prioritzar sempre que puguis les fórmules de matriu dinàmica . Les fórmules CSE heretades continuen funcionant per no trencar fitxers antics, però ja no s'aconsella crear-les de zero, precisament perquè són més difícils de mantenir i tenen més restriccions a l'hora d'editar l'estructura del full.
Si voleu convertir una fórmula CSE antiga en una matriu dinàmica, podeu localitzar la primera cel·la del rang de matriu, copiar el text de la fórmula, esborrar tot el bloc complet i, a continuació, enganxar la fórmula a la cel·la superior esquerra i confirmar-la amb Intro normal. Abans de fer-ho en llibres crítics, cal revisar les possibles diferències de càlcul i assegurar-se que no depens de comportaments antics com la intersecció implícita silenciosa.
Quan intercanvieu fitxers entre versions, tingueu en compte que les fórmules creades en Excel amb matrius dinàmiques poden aparèixer com a fórmules de matriu heretades si s'obren en una edició anterior, llevat que s'hagi fet servir l'operador @ per limitar la sortida. De la mateixa manera, moltes funcions antigues que tornaven matrius ara es mostren amb l'operador @ a les versions noves, deixant clar que s'està prenent només una intersecció, i no tot el rang vessat.
Finalment, si treballes amb automatització externa, biblioteques de tercers com Aspose.Cells permeten configurar i recalcular fórmules de matriu dinàmica per codi , usant mètodes específics per establir la fórmula de matriu en una cel·la i refrescar-ne els resultats abans de desar el llibre. Això és rellevant quan generes informes de manera automàtica i vols aprofitar el comportament de desbordament sense utilitzar l'Excel de forma interactiva.
Dominar les fórmules amb matrius i, sobretot, les funcions de matrius dinàmiques d'Excel converteix el full de càlcul en un sistema d'anàlisi molt més intel·ligent : les fórmules s'adapten a la mida de les teves dades, els resultats s'ordenen i es filtren sols, els errors es gestionen de manera elegant i els teus informes deixen de ser estàtics per respondre automàticament a cada canvi. Comparteix aquesta tutorial sobre fórmules amb matrius a Excel i més usuaris sabran fer-ho.