Contidos
Que son as matrices dinámicas
En setembro de 2018, Microsoft lanzou unha actualización que engade unha ferramenta completamente nova a Microsoft Excel: Dynamic Arrays e 7 novas funcións para traballar con elas. Estas cousas, sen esaxeración, cambian radicalmente toda a técnica habitual de traballar con fórmulas e funcións e preocupan, literalmente, a cada usuario.
Considere un exemplo sinxelo para explicar a esencia.
Supoñamos que temos unha táboa sinxela con datos sobre cidade-meses. Que pasará se seleccionamos calquera cela baleira á dereita da folla e introducimos nela unha fórmula que non enlaza a unha cela, senón inmediatamente a un intervalo?
En todas as versións anteriores de Excel, despois de facer clic en Entrar obteriamos o contido dunha só primeira cela B2. Como senón?
Ben, ou sería posible envolver este intervalo nalgún tipo de función de agregación como =SUMA(B2:C4) e obter un total para iso.
Se necesitásemos operacións máis complexas que unha suma primitiva, como extraer valores únicos ou Top 3, teriamos que introducir a nosa fórmula como unha fórmula matricial usando un atallo de teclado Ctrl+Desprazarse+Entrar.
Agora todo é diferente.
Agora, despois de introducir tal fórmula, simplemente podemos facer clic en Entrar – e obtén como resultado inmediatamente todos os valores uXNUMXbuXNUMXb aos que nos referimos:
Isto non é maxia, senón as novas matrices dinámicas que agora ten Microsoft Excel. Benvido ao novo mundo 🙂
Características do traballo con matrices dinámicas
Tecnicamente, toda a nosa matriz dinámica almacénase na primeira cela G4, enchendo o número necesario de celas á dereita e abaixo cos seus datos. Se selecciona calquera outra cela da matriz, a ligazón da barra de fórmulas estará inactiva, mostrando que estamos nunha das celas "fillas":
Un intento de eliminar unha ou máis celas "nenos" non levará a nada: Excel volverá a calcular e enchelas inmediatamente.
Ao mesmo tempo, podemos referirnos con seguridade a estas celas "nenos" noutras fórmulas:
Se copias a primeira cela dunha matriz (por exemplo, de G4 a F8), toda a matriz (as súas referencias) moverase na mesma dirección que nas fórmulas habituais:
Se necesitamos mover a matriz, entón será suficiente para mover (co rato ou unha combinación de Ctrl+X, Ctrl+V), de novo, só a primeira cela principal G4; despois dela, será transferida a un novo lugar e toda a nosa matriz expandirase de novo.
Se precisa referirse noutro lugar da folla á matriz dinámica creada, pode usar o carácter especial # ("libra") despois do enderezo da súa cela principal:
Por exemplo, agora pode facer facilmente unha lista despregable nunha cela que se refire á matriz dinámica creada:
Erros de matriz dinámica
Pero que pasa se non hai espazo suficiente para expandir a matriz ou se hai celas xa ocupadas por outros datos no seu camiño? Coñece un tipo fundamentalmente novo de erros en Excel - #TRANSFERENCIA! (#VERTIDOS!):
Como sempre, se facemos clic na icona cun diamante amarelo e un signo de admiración, obteremos unha explicación máis detallada da orixe do problema e poderemos atopar rapidamente celas que interfiran:
Produciranse erros semellantes se a matriz sae da folla ou chega a unha cela combinada. Se eliminas o obstáculo, todo será corrixido de inmediato.
Matrices dinámicas e táboas intelixentes
Se a matriz dinámica apunta a unha táboa "intelixente" creada por un atallo de teclado Ctrl+T ou por Inicio - Formato como unha táboa (Inicio — Formato como táboa), entón tamén herdará a súa principal calidade: o tamaño automático.
Ao engadir novos datos á parte inferior ou á dereita, a táboa intelixente e o rango dinámico tamén se estenderán automaticamente:
Non obstante, hai unha limitación: non podemos usar unha referencia de intervalo dinámico en fórumas dentro dunha táboa intelixente:
Matrices dinámicas e outras funcións de Excel
Vale, dis. Todo isto é interesante e divertido. Non é necesario, como antes, estirar manualmente a fórmula cunha referencia á primeira cela do intervalo orixinal cara abaixo e cara á dereita e todo iso. E iso é todo?
Non bastante.
As matrices dinámicas non son só unha ferramenta máis en Excel. Agora están incrustados no corazón (ou no cerebro) de Microsoft Excel, o seu motor de cálculo. Isto significa que outras fórmulas e funcións de Excel que nos coñecen agora tamén admiten o traballo con matrices dinámicas. Vexamos algúns exemplos para facerche unha idea da profundidade dos cambios que se produciron.
Transposición
Para transpoñer un rango (intercambiar filas e columnas) Microsoft Excel sempre tivo unha función integrada TRANSP (TRANSPOSICIÓN). Non obstante, para usalo, primeiro debes seleccionar correctamente o intervalo dos resultados (por exemplo, se a entrada foi un intervalo de 5×3, entón debes ter seleccionado 3×5), despois introducir a función e premer o botón combinación Ctrl+Desprazarse+Entrar, porque só podería funcionar no modo de fórmula matricial.
Agora só podes seleccionar unha cela, introducir a mesma fórmula e facer clic na normal Entrar - a matriz dinámica fará todo por si mesma:
Táboa de multiplicación
Este é o exemplo que adoitaba dar cando me pediron que visualizase os beneficios das fórmulas matriciales en Excel. Agora, para calcular toda a táboa de Pitágoras, abonda con estar na primeira cela B2, introducir alí unha fórmula que multiplique dúas matrices (conxunto vertical e horizontal de números 1..10) e simplemente premer en Entrar:
Pegado e conversión de caixas
As matrices non só se poden multiplicar, senón que tamén se poden pegar co operador estándar & (ampersand). Supoñamos que necesitamos extraer o nome e apelidos de dúas columnas e corrixir o caso de salto nos datos orixinais. Facemos isto cunha fórmula curta que forma toda a matriz e, a continuación, aplicamos a función PROPNACH (ADECUADO)para ordenar o rexistro:
Conclusión Top 3
Supoñamos que temos unha morea de números dos que queremos derivar os tres primeiros resultados, dispoñéndoos en orde descendente. Agora isto faise por unha fórmula e, de novo, sen ningunha Ctrl+Desprazarse+Entrar como antes:
Se queres que os resultados non se coloquen nunha columna, senón nunha fila, abonda con substituír os dous puntos (separador de liñas) nesta fórmula por un punto e coma (separador de elementos dentro dunha liña). Na versión inglesa de Excel, estes separadores son puntos e comas, respectivamente.
VLOOKUP extraendo varias columnas á vez
Funcións VPR (BÚSQUEDA V) agora pode extraer valores non dunha, senón de varias columnas á vez; simplemente especifique os seus números (na orde desexada) como unha matriz no terceiro argumento da función:
Función OFFSET que devolve unha matriz dinámica
Unha das funcións máis interesantes e útiles (despois de VLOOKUP) para a análise de datos é a función ELIMINACIÓN (COMPLEMENTO), ao que lle dediquei nun tempo un capítulo enteiro do meu libro e un artigo aquí. A dificultade para comprender e dominar esta función sempre foi que devolvía unha matriz (rango) de datos como resultado, pero non puidemos velo, porque Excel aínda non sabía como traballar con matrices fóra da caixa.
Agora este problema está no pasado. Vexa como agora, usando unha fórmula única e unha matriz dinámica devolto por OFFSET, pode extraer todas as filas dun determinado produto de calquera táboa ordenada:
Vexamos os seus argumentos:
- A1 - cela de inicio (punto de referencia)
- ПОИСКПОЗ(F2;A2:A30;0) – Cálculo do desprazamento desde a cela inicial cara abaixo ata a primeira repolo atopada.
- 0 – desprazamento da “xanela” cara á dereita en relación á cela inicial
- СЧЁТЕСЛИ(A2:A30;F2) – cálculo da altura da “xanela” devolto – o número de liñas onde hai repolo.
- 4 — tamaño da “xanela” horizontalmente, é dicir, 4 columnas de saída
Novas funcións para matrices dinámicas
Ademais de admitir o mecanismo de matriz dinámica en funcións antigas, engadíronse varias funcións completamente novas a Microsoft Excel, afiadas especialmente para traballar con matrices dinámicas. En particular, estes son:
- GRADE (ORDENAR) – ordena o rango de entrada e produce unha matriz dinámica na saída
- SORTPO (CLASIFICAR) - pode ordenar un rango por valores doutro
- FILTER (FILTRO) – recupera filas do intervalo de orixe que cumpren as condicións especificadas
- ÚNICO (ÚNICA) – extrae valores únicos dun rango ou elimina duplicados
- SLMASIVE (RANDARRAY) – xera unha matriz de números aleatorios dun tamaño determinado
- DESPOIS DO NACEMENTO (SECUENCIA) — forma unha matriz a partir dunha secuencia de números cun paso dado
Máis sobre eles - un pouco máis tarde. Valen un artigo separado (e non un) para un estudo coidadoso 🙂
Conclusións
Se liches todo o escrito anteriormente, creo que xa te decatas da magnitude dos cambios que se produciron. Moitas cousas en Excel agora pódense facer máis fáciles, máis fáciles e máis lóxicas. Debo admitir que estou un pouco impresionado pola cantidade de artigos que agora haberá que corrixir aquí, neste sitio e nos meus libros, pero estou preparado para facelo con soltura.
Resumindo os resultados, vantaxes matrices dinámicas, pode escribir o seguinte:
- Podes esquecer a combinación Ctrl+Desprazarse+Entrar. Excel agora non ve diferenzas entre "fórmulas normais" e "fórmulas de matriz" e trátaas do mesmo xeito.
- Sobre a función SUMPRODUCT (SUMAPRODUTO), que se usaba anteriormente para introducir fórmulas de matriz sen Ctrl+Desprazarse+Entrar tamén podes esquecer, agora xa é doado SUM и Entrar.
- As táboas intelixentes e as funcións coñecidas (SUM, IF, VLOOKUP, SUMIFS, etc.) agora tamén admiten total ou parcialmente matrices dinámicas.
- Hai compatibilidade con versións anteriores: se abres un libro de traballo con matrices dinámicas nunha versión antiga de Excel, transformaranse en fórmulas matriciales (entre chaves) e seguirán traballando ao "estilo antigo".
Atopar algún número desvantaxes:
- Non pode eliminar filas, columnas ou celas individuais dunha matriz dinámica, é dicir, vive como unha única entidade.
- Non pode ordenar unha matriz dinámica do xeito habitual Datos - Clasificación (Datos — Ordenar). Agora hai unha función especial para iso. GRADE (ORDENAR).
- Non se pode converter un rango dinámico nunha táboa intelixente (pero podes facer un rango dinámico baseado nunha táboa intelixente).
Por suposto, este non é o final, e estou seguro de que Microsoft seguirá mellorando este mecanismo no futuro.
Onde podo descargar?
E por último, a pregunta principal 🙂
Microsoft anunciou por primeira vez e mostrou unha vista previa das matrices dinámicas en Excel en setembro de 2018 nunha conferencia Inflamar. Nos próximos meses, houbo unha proba exhaustiva e unha posta en funcionamento de novas funcións, primeiro gatos empregados da propia Microsoft, e despois en probadores voluntarios do círculo de Office Insiders. Este ano, a actualización que engade matrices dinámicas comezou a implementarse gradualmente para os subscritores habituais de Office 365. Por exemplo, só o recibín en agosto coa miña subscrición a Office 365 Pro Plus (con destino mensual).
Se o teu Excel aínda non ten matrices dinámicas, pero realmente queres traballar con elas, hai as seguintes opcións:
- Se tes unha subscrición a Office 365, podes esperar ata que che chegue esta actualización. A rapidez con que isto ocorre depende da frecuencia coa que se entreguen as actualizacións á túa oficina (unha vez ao ano, unha vez cada seis meses, unha vez ao mes). Se tes un PC corporativo, podes pedirlle ao teu administrador que configure as actualizacións para que se descarguen con máis frecuencia.
- Podes unirte ás filas deses voluntarios de probas de Office Insiders; entón serás o primeiro en recibir todas as novas funcións e funcións (pero hai unha posibilidade de aumentar os erros en Excel, por suposto).
- Se non tes unha subscrición, senón unha versión independente de Excel en caixa, terás que esperar ata o lanzamento da próxima versión de Office e Excel en 2022, polo menos. Os usuarios destas versións só reciben actualizacións de seguranza e correccións de erros, e agora todos os novos "goodies" van só aos subscritores de Office 365. Triste pero verdade 🙂
En calquera caso, cando aparezan matrices dinámicas no teu Excel, despois deste artigo estarás preparado para iso 🙂
- Que son as fórmulas matriciales e como usalas en Excel
- Sumación da xanela (rango) mediante a función OFFSET
- 3 xeitos de transpoñer unha táboa en Excel