As táboas dinámicas son boas para todos: calcúlanse rapidamente e configúranse de forma flexible e o deseño pódese enrolar con elegancia, se é necesario. Pero tamén hai algunhas mosca na pomada, en particular, a incapacidade de crear un resumo, onde a área de valor non debe conter números, senón texto.
Tentemos sortear esta limitación e inventemos un "par de muletas" nunha situación similar.
Supoñamos que a nosa empresa transporta os seus produtos en contedores a varias cidades do noso país e de Casaquistán. Os contedores envíanse non máis dunha vez ao mes. Cada recipiente ten un número alfanumérico. Como datos iniciais, hai unha táboa estándar que recolle as entregas, da que cómpre facer algún tipo de resumo para poder ver claramente o número de contedores enviados a cada cidade e cada mes:
Para comodidade, fagamos que a táboa cos datos iniciais sexa "intelixente" con antelación usando o comando Inicio - Formato como unha táboa (Inicio — Formato como táboa) e ponlle un nome Entregas aba Construtor (Deseño). No futuro, isto simplificará a vida, porque. poderase utilizar directamente nas fórmulas o nome da táboa e as súas columnas.
Método 1. O máis sinxelo: use Power Query
Power Query é unha ferramenta súper poderosa para cargar e transformar datos en Excel. Este complemento está integrado en Excel de forma predeterminada desde 2016. Se tes Excel 2010 ou 2013, podes descargalo e instalalo por separado (completamente gratuíto).
Todo o proceso, para máis claridade, analizei paso a paso no seguinte vídeo:
Se non é posible usar Power Query, pode ir por outros camiños: a través dunha táboa dinámica ou fórmulas.
Método 2. Resumo auxiliar
Engadimos unha columna máis á nosa táboa orixinal, onde utilizando unha fórmula sinxela calculamos o número de cada fila da táboa:
Obviamente, é necesario -1, porque temos unha cabeceira dunha liña na nosa táboa. Se a túa táboa non está ao principio da folla, podes usar unha fórmula un pouco máis complexa, pero universal que calcule a diferenza entre os números da fila actual e o encabezado da táboa:
Agora, dun xeito estándar, construiremos unha táboa dinámica do tipo desexado en función dos nosos datos, pero no campo de valor soltaremos o campo Número de liña en lugar do que queremos recipiente:
Dado que non temos varios colectores na mesma cidade no mesmo mes, o noso resumo dará, de feito, non a cantidade, senón os números de liña dos colectores que necesitamos.
Ademais, pode desactivar os totais e os subtotais na pestana Construtor – Totais xerais и Subtotais (Deseño: totais xerais, subtotais) e no mesmo lugar cambia o resumo a un deseño de táboa máis cómodo co botón Informar maqueta (Disposición do informe).
Así, xa estamos a metade do resultado: temos unha táboa onde, na intersección da cidade e o mes, hai un número de fila na táboa fonte, onde se atopa o código do contedor que necesitamos.
Agora copiemos o resumo (na mesma folla ou noutra) e peguemos como valores, e despois introduzamos a nosa fórmula na área de valores, que extraerá o código do recipiente polo número de liña que se atopa no resumo:
función IF (SI), neste caso, comproba que a seguinte cela do resumo non estea baleira. Se está baleiro, mostra unha cadea de texto baleira “”, é dicir, deixa a cela en branco. Se non está baleiro, extrae da columna Recipiente táboa de orixe Entregas contido da cela por número de fila usando a función Index (ÍNDICE).
Quizais o único punto non moi obvio aquí sexa a dobre palabra Recipiente na fórmula. Unha forma tan estraña de escribir:
Subministros[[Envase]:[Envase]]
... só é necesario para referenciar a columna Recipiente era absoluto (como unha referencia con signos $ para táboas ordinarias "non intelixentes") e non se deslizou ás columnas veciñas ao copiar a nosa fórmula á dereita.
No futuro, ao cambiar os datos da táboa de orixe Entregas, debemos lembrar de actualizar o noso resumo auxiliar cos números de liña facendo clic co botón dereito sobre el e escollendo o comando Actualizar e gardar (Actualizar).
Método 3. Fórmulas
Este método non require a creación dunha táboa dinámica intermedia nin a actualización manual, pero usa a "arma pesada" de Excel, a función SUMMESLIMN (SUMIFS). En lugar de buscar números de fila nun resumo, pode calculalos usando esta fórmula:
Con certo volume externo, de feito, este é un caso de uso estándar para a función de suma selectiva SUMMESLIMNA que suma os números de fila para a cidade e o mes dados. De novo, dado que non temos varios contedores na mesma cidade no mesmo mes, a nosa función, de feito, non dará a cantidade, senón o propio número de liña. E entón a función xa familiar do método anterior Index Tamén pode extraer códigos de contedores:
Por suposto, neste caso, xa non é necesario pensar en actualizar o resumo, senón en táboas grandes, a función SUMMESLI pode ser notablemente lento. Entón terás que desactivar a actualización automática de fórmulas ou usar o primeiro método: unha táboa dinámica.
Se a aparencia do resumo non é moi adecuada para o teu informe, podes extraer os números de fila del na táboa final non directamente, como fixemos nós, senón usando a función OBTER.DATOS.TÁBOA.PIVOT (OBTER.DATOS.PIVOT). Como facelo pódese atopar aquí.
- Como crear un informe usando unha táboa dinámica
- Como configurar cálculos en táboas dinámicas
- Reconto selectivo con SUMIFS, COUNTIFS, etc.