contingut
Què són les matrius dinàmiques
El setembre de 2018, Microsoft va publicar una actualització que afegeix una eina completament nova a Microsoft Excel: Dynamic Arrays i 7 noves funcions per treballar-hi. Aquestes coses, sense exagerar, canvien radicalment tota la tècnica habitual de treballar amb fórmules i funcions i preocupen, literalment, a cada usuari.
Considereu un exemple senzill per explicar l'essència.
Suposem que tenim una taula senzilla amb dades sobre ciutat-mesos. Què passarà si seleccionem qualsevol cel·la buida a la dreta del full i hi introduïm una fórmula que enllaça no a una cel·la, sinó immediatament a un rang?
En totes les versions anteriors d'Excel, després de fer clic a Enter obtindríem només el contingut d'una primera cel·la B2. Com més?
Bé, o seria possible embolicar aquest rang en algun tipus de funció d'agregació com =SUMA(B2:C4) i obtenir-ne un total general.
Si necessitem operacions més complexes que una suma primitiva, com extreure valors únics o Top 3, hauríem d'introduir la nostra fórmula com a fórmula matricial mitjançant una drecera de teclat Ctrl+Canviar+Enter.
Ara tot és diferent.
Ara, després d'introduir aquesta fórmula, només podem fer clic a Enter – i obteniu com a resultat immediatament tots els valors uXNUMXbuXNUMXb als quals ens referim:
Això no és màgia, sinó les noves matrius dinàmiques que ara té Microsoft Excel. Benvinguts al nou món 🙂
Característiques de treballar amb matrius dinàmiques
Tècnicament, tota la nostra matriu dinàmica s'emmagatzema a la primera cel·la G4, omplint el nombre necessari de cel·les a la dreta i avall amb les seves dades. Si seleccioneu qualsevol altra cel·la de la matriu, l'enllaç de la barra de fórmules estarà inactiu, mostrant que estem en una de les cel·les "fills":
Un intent d'eliminar una o més cel·les "fills" no donarà lloc a res: Excel les tornarà a calcular immediatament i les omplirà.
Al mateix temps, podem referir-nos amb seguretat a aquestes cel·les "fills" en altres fórmules:
Si copieu la primera cel·la d'una matriu (per exemple, de G4 a F8), aleshores tota la matriu (les seves referències) es mourà en la mateixa direcció que en les fórmules normals:
Si hem de moure la matriu, n'hi haurà prou amb moure'l (amb el ratolí o una combinació de Ctrl+X, Ctrl+V), de nou, només la primera cel·la principal G4; després, es transferirà a un lloc nou i tota la nostra matriu s'ampliarà de nou.
Si necessiteu referir-vos a un altre lloc del full a la matriu dinàmica creada, podeu utilitzar el caràcter especial # ("lleura") després de l'adreça de la seva cel·la principal:
Per exemple, ara podeu crear fàcilment una llista desplegable en una cel·la que faci referència a la matriu dinàmica creada:
Errors de matriu dinàmica
Però què passa si no hi ha prou espai per expandir la matriu o si hi ha cel·les que ja estan ocupades per altres dades al seu camí? Coneix un tipus d'error fonamentalment nou a Excel: #TRANSFER! (#SPILL!):
Com sempre, si fem clic a la icona amb un diamant groc i un signe d'exclamació, obtindrem una explicació més detallada de l'origen del problema i podrem trobar ràpidament cel·les que interfereixen:
Es produiran errors similars si la matriu surt del full o arriba a una cel·la combinada. Si elimineu l'obstacle, tot es corregirà immediatament sobre la marxa.
Matrius dinàmiques i taules intel·ligents
Si la matriu dinàmica apunta a una taula "intel·ligent" creada per una drecera de teclat Ctrl+T o per Inici – Format com a taula (Inici — Format com a taula), llavors també heretarà la seva qualitat principal: la mida automàtica.
Quan s'afegeixen dades noves a la part inferior oa la dreta, la taula intel·ligent i el rang dinàmic també s'estiraran automàticament:
Tanmateix, hi ha una limitació: no podem utilitzar una referència de rang dinàmic en fòrums dins d'una taula intel·ligent:
Matrius dinàmiques i altres funcions d'Excel
D'acord, dius. Tot això és interessant i divertit. No cal, com abans, estirar manualment la fórmula amb una referència a la primera cel·la de l'interval original cap avall i cap a la dreta i tot això. I això és tot?
No del tot.
Les matrius dinàmiques no són només una eina més a Excel. Ara estan incrustats al cor (o cervell) de Microsoft Excel, el seu motor de càlcul. Això vol dir que altres fórmules i funcions d'Excel que ens coneixen ara també admeten el treball amb matrius dinàmiques. Vegem uns quants exemples per fer-vos una idea de la profunditat dels canvis que s'han produït.
Transposa
Per transposar un rang (intercanviar files i columnes), Microsoft Excel sempre ha tingut una funció integrada TRANSP (TRANSPOSICIÓ). Tanmateix, per utilitzar-lo, primer heu de seleccionar correctament l'interval dels resultats (per exemple, si l'entrada era un interval de 5×3, haureu d'haver seleccionat 3×5), després introduïu la funció i premeu el botó combinació Ctrl+Canviar+Enter, perquè només podria funcionar en mode de fórmula matriu.
Ara només podeu seleccionar una cel·la, introduir-hi la mateixa fórmula i fer clic a la normal Enter - la matriu dinàmica ho farà tot per si mateixa:
Taula de multiplicar
Aquest és l'exemple que solia donar quan em van demanar que visualitzés els avantatges de les fórmules de matriu a Excel. Ara, per calcular tota la taula pitagòrica, n'hi ha prou de situar-se a la primera cel·la B2, introduir-hi una fórmula que multipliqui dues matrius (conjunt de nombres vertical i horitzontal 1..10) i simplement fer clic a sobre Enter:
Encolat i conversió de caixa
Les matrius no només es poden multiplicar, sinó que també es poden enganxar amb l'operador estàndard & (ampersand). Suposem que necessitem extreure el nom i el cognom de dues columnes i corregir el cas de salt a les dades originals. Ho fem amb una fórmula breu que forma tota la matriu i després li apliquem la funció PROPNACH (PROPI)per endreçar el registre:
Conclusió Top 3
Suposem que tenim un munt de nombres dels quals volem derivar els tres primers resultats, ordenant-los en ordre descendent. Ara això es fa amb una fórmula i, de nou, sense cap Ctrl+Canviar+Enter com abans:
Si voleu que els resultats no es col·loquin en una columna, sinó en una fila, n'hi ha prou amb substituir els dos punts (separador de línies) en aquesta fórmula per un punt i coma (separador d'elements dins d'una línia). A la versió anglesa d'Excel, aquests separadors són punt i coma i comes, respectivament.
VLOOKUP extreu diverses columnes alhora
Funcions VPR (VISUALITZACIÓ) ara podeu extreure valors no d'una, sinó de diverses columnes alhora; només cal que especifiqueu els seus números (en qualsevol ordre desitjat) com a matriu al tercer argument de la funció:
Funció OFFSET que retorna una matriu dinàmica
Una de les funcions més interessants i útils (després de VLOOKUP) per a l'anàlisi de dades és la funció DISPOSICIÓ (OFFSET), a la qual vaig dedicar alhora un capítol sencer del meu llibre i un article aquí. La dificultat per entendre i dominar aquesta funció sempre ha estat que retornava una matriu (interval) de dades com a resultat, però no la vam poder veure, perquè Excel encara no sabia com treballar amb matrius fora de la caixa.
Ara aquest problema és en el passat. Vegeu com ara, utilitzant una fórmula única i una matriu dinàmica retornada per OFFSET, podeu extreure totes les files d'un producte determinat de qualsevol taula ordenada:
Fem una ullada als seus arguments:
- A1 - cel·la inicial (punt de referència)
- ПОИСКПОЗ(F2;A2:A30;0) – càlcul del desplaçament des de la cel·la inicial cap avall – fins a la primera col trobada.
- 0 – desplaçament de la “finestra” cap a la dreta en relació a la cel·la inicial
- СЧЁТЕСЛИ(A2:A30;F2) – càlcul de l'alçada de la “finestra” retornada – el nombre de línies on hi ha col.
- 4 — mida de la “finestra” horitzontalment, és a dir, sortir 4 columnes
Noves funcions per a matrius dinàmiques
A més de donar suport al mecanisme de matriu dinàmica en funcions antigues, s'han afegit diverses funcions completament noves a Microsoft Excel, afinades específicament per treballar amb matrius dinàmiques. En concret, aquests són:
- GRAU (ORDENAR) – ordena l'interval d'entrada i produeix una matriu dinàmica a la sortida
- SORTPO (ORDENAR PER) - pot ordenar un rang per valors d'un altre
- FILTRE (FILTRE) – recupera les files de l'interval d'origen que compleixen les condicions especificades
- UNIK (ÚNIC) – extreu valors únics d'un rang o elimina els duplicats
- SLMASSIVE (RANDARRAY) – genera una matriu de nombres aleatoris d'una mida determinada
- DESPRÉSPART (SEQÜÈNCIA) — forma una matriu a partir d'una seqüència de nombres amb un pas donat
Més sobre ells, una mica més tard. Valen un article a part (i no un) per a un estudi atent 🙂
Conclusions
Si heu llegit tot el que s'ha escrit anteriorment, crec que ja us adoneu de l'envergadura dels canvis que s'han produït. Tantes coses a Excel ara es poden fer més fàcil, més fàcil i més lògic. He de reconèixer que estic una mica sorprès de quants articles ara caldrà corregir aquí, en aquest lloc i als meus llibres, però estic disposat a fer-ho amb calma.
Resumint els resultats, avantatges matrius dinàmiques, podeu escriure el següent:
- Podeu oblidar-vos de la combinació Ctrl+Canviar+Enter. Excel ara no veu cap diferència entre "fórmules normals" i "fórmules de matriu" i les tracta de la mateixa manera.
- Sobre la funció SUMPRODUCT (SUMAPRODUCTE), que abans s'utilitzava per introduir fórmules de matriu sense Ctrl+Canviar+Enter també us podeu oblidar, ara és prou fàcil SUM и Enter.
- Les taules intel·ligents i les funcions familiars (SUM, IF, VLOOKUP, SUMIFS, etc.) ara també admeten totalment o parcialment matrius dinàmiques.
- Hi ha compatibilitat amb versions anteriors: si obriu un llibre de treball amb matrius dinàmiques en una versió antiga d'Excel, es convertiran en fórmules de matriu (entre claus) i continuaran treballant a l'"estil antic".
He trobat algun número inconvenients:
- No podeu eliminar files, columnes o cel·les individuals d'una matriu dinàmica, és a dir, viu com una sola entitat.
- No podeu ordenar una matriu dinàmica de la manera habitual Dades – Ordenació (Dades — Ordena). Ara hi ha una funció especial per a això. GRAU (ORDENAR).
- Un rang dinàmic no es pot convertir en una taula intel·ligent (però podeu crear un rang dinàmic basat en una taula intel·ligent).
Per descomptat, aquest no és el final, i estic segur que Microsoft continuarà millorant aquest mecanisme en el futur.
On puc descarregar?
I finalment, la pregunta principal 🙂
Microsoft va anunciar i va mostrar per primera vegada una vista prèvia de les matrius dinàmiques a Excel el setembre de 2018 en una conferència Ignite. En els propers mesos, es va fer una prova exhaustiva i un funcionament de noves funcions, primer els gats empleats de la mateixa Microsoft, i després en provadors voluntaris del cercle d'Office Insiders. Aquest any, l'actualització que afegeix matrius dinàmiques es va començar a implementar gradualment als subscriptors habituals d'Office 365. Per exemple, només el vaig rebre a l'agost amb la meva subscripció a Office 365 Pro Plus (orientació mensual).
Si el vostre Excel encara no té matrius dinàmiques, però realment voleu treballar-hi, hi ha les opcions següents:
- Si teniu una subscripció a Office 365, només podeu esperar fins que us arribi aquesta actualització. La rapidesa amb què passa això depèn de la freqüència amb què s'entreguen les actualitzacions a l'oficina (un cop a l'any, un cop cada sis mesos, un cop al mes). Si teniu un ordinador corporatiu, podeu demanar al vostre administrador que configuri les actualitzacions perquè es baixin més sovint.
- Podeu unir-vos a les files d'aquests voluntaris de prova d'Office Insiders; aleshores seràs el primer a rebre totes les noves característiques i funcions (però hi ha la possibilitat d'augmentar els errors a Excel, és clar).
- Si no teniu una subscripció, sinó una versió independent d'Excel en caixa, haureu d'esperar fins al llançament de la propera versió d'Office i Excel el 2022, almenys. Els usuaris d'aquestes versions només reben actualitzacions de seguretat i correccions d'errors, i tots els nous "bons" ara només es dirigeixen als subscriptors d'Office 365. Trist però cert 🙂
En qualsevol cas, quan apareguin matrius dinàmiques al vostre Excel, després d'aquest article, estaràs preparat per a això 🙂
- Què són les fórmules de matriu i com utilitzar-les a Excel
- Sumació de finestra (interval) mitjançant la funció OFFSET
- 3 maneres de transposar una taula a Excel