Power Pivot: Gráfico dinámico (sin tabla dinámica asociada)

Hoy veremos algo interesante, aprovechándonos del complemento Power Pivot.
Lo interesante es que podemos construir un gráfico dinámico sin necesidad de tener como apoyo detrás una tabla dinámica!!.

Supongamos dos tablas con las ventas para 2018 y 2019 de unos pocos países... y queremos generar un gráfico dinámico que muestre la información por país de las venta de los dos años:

El trabajo comienza cargando ambas tablas al modelo de datos, desde la ficha Power Pivot > grupo Tablas > botón Agregar al modelo de datos.

Con las tablas ya en Power Pivot gestionaremos la relación entre las dos empleando el campo País en común... y añadiremos un campo nuevo en la tabla el 2018 con la función
=RELATED(Tabla2[ventas_19])

Para más detalle sigue los pasos aquí descritos.

Con las tablas relacionadas en modelo de datos y el campo relacionado añadido podemos insertar directamente un gráfico dinámico.
Ficha Insertar > grupo Gráficos > botón Gráfico dinámico > Gráfico dinámico (solo!!)

Y listo... tenemos nuestro gráfico dinámico sobre las dos tablas origen sin necesidad de tener como respaldo la tabla dinámica de costumbre
;-)

Seguir leyendo...

Gráfico con bandas

Hace unos días navegando por la web encontré en un site (pakaccountants.com) un gráfico en Excel que me pareció interesante.
Me permito compartirlo con vosotros.

Se trata de un gráfico mixto (líneas con columnas apiladas), que con unos mínimos ajustes representaría una variable moviéndose entre unos valores parametrizados.

En primer lugar veamos la disposición de nuestros datos:

Veremos en los pasos siguientes que el rango B2:B13 será la serie con tipo de gráfico de línea, que representa la variación en la consecución de un objetivo (con valores posibles de 0% a 100%).
Los otros rangos C2:F13 corresponden a la división en cuatro partes de ese objetivo conseguido...
Observa que para los doce meses el reparto es igual y siempre suma 100 !!!.

Para generar el gráfico seleccionamos el rango completo: A1:F13 e insertamos un gráfico de columnas apiladas:

Por estética quitamos el título del gráfico y la leyenda.

Seleccionamos la serie azul, que represente el porcentaje de cumplimiento, y con el botón derecho del ratón cambiamos el tipo de gráfico de la serie a tipo línea:

El siguiente y último paso relevante consiste en hacer clic derecho sobre cualquiera de las columnas apiladas, y desde su formato cambiaremos el ancho del rango a 0%... lo que provoca que todas las columnas queden pegadas... consiguiendo el efecto deseado: la sensación de un línea moviéndose sobre cuatro tramos...

A partir de aquí es 'poner bonito' el gráfico, cambiando colores de las columnas o de la línea; o añadiendo las etiquetas de datos a un solo punto de cada serie.

Seguir leyendo...

Power Query: Funciones M de texto

Al hilo del post publicado aquí, donde con algunas matriciales y funciones de texto en la hoja de cálculo recuperábamos información por la derecha y/o izquierda de una cadena de texto, un lector (Daniel Herce) a través de un comentario compartía la misma operación empleado una consulta de Power Query.

Me pareció interesante la perspectiva y poder explicar de paso algunas de las funciones M de texto en Power Query.

Decir nuevamente que en Power Query existen bastantes más funciones de texto de las que se nos ofrece en la hoja de cálculo.. por lo que las posibilidades para recuperar, tratar o modificar nuestros textos son casi infinitas.
A modo de ejemplo comentaré algunas de las funciones M de texto más frecuentes:
Text.Upper(texto) - equivale a la función MAYUSC
Text.Lower(texto) - equivale a la función MINUSC
Text.Proper(texto) - equivale a la función NOMPROPIO
Text.Start(texto, Num caracteres) - equivale a la función IZQUIERDA
Text.End(texto, Num caracteres) - equivale a la función DERECHA
Text.Length(Texto) - equivale a la función LARGO
Text.Middle(texto, inicio, Num caracteres) - equivale a la función EXTRAE
Text.Contains(texto principal, texto buscado) - equivale a la función ENCONTRAR, HALLAR, pero devuelve VERDADERO/FALSO !!
Text.Replace (texto, texto_viejo, texto_nuevo) - - equivale a la función SUSTITUIR

y otras sin equivalencia:
Text.Reverse(text) - devuelve la cadena de texto al revés...
Text.PositionOfAny(string as text, list as list, optional occurrence as nullable number)
las opciones de ocurrencia:
Occurrence.First - devuelve un único valor
Occurrence.Last - devuelve un único valor
Occurrence.All - devuelve una matriz de coincidencias
Text.Split(texto, separador) - devuelve particionada la cadena de texto de acuerdo al delimitador indicado
Text.Remove(texto, caracter a eliminar) - elimina de la cadena de texto el caracter indicado
Text.Range(texto, inicio, opcional Num caract) - similar a EXTRAE
Devuelve la subcadena de la texto encontrado en la posición de inicio dada. Se puede incluir un parámetro opcional, 'Num caract', para especificar cuántos caracteres devolver...

y muchas más...

Sobre nuestro listado de matrículas expuesto en el post indicado generaremos la siguiente consulta personalizada.

Cargamos nuestra Tabla llamada 'TblMatriculas' que tiene un solo campo '[Matrículas]'.
Así como siempre desde la ficha Datos > grupo Obtener y transformar > Desde una tabla creamos nuestra consulta, y accederemos al Editor avanzado donde añadiremos el siguiente código:
let

Source = Excel.CurrentWorkbook(){[Name="TblMatriculas"]}[Content],
/* definimos la matriz de valores numéricos*/
Numeros = {"0","1","2","3","4","5","6","7","8","9"},

/*identificamos las posiciones de los primeros y últimos números...*/
PosIni= each Text.PositionOfAny([Matrículas],Numeros,Occurrence.First),
PosUlt= each Text.PositionOfAny([Matrículas],Numeros,Occurrence.Last) + 1,
/*y la longitud o número de caracteres total*/
NumCaract= each Text.Length([Matrículas]),

/*recuperamos las cadenas por la izquierda*/
primero=each Text.Start([Matrículas], Text.PositionOfAny([Matrículas],Numeros,Occurrence.First)),
/*y desde en medio hasta el final*/
ultimo= each Text.Range([Matrículas], Text.PositionOfAny([Matrículas],Numeros,Occurrence.Last) + 1),
/*Una alternativa al anterior para recuperar datos por la derecha*/
por_dcha=each Text.End([Matrículas],
Text.Length([Matrículas])-Text.PositionOfAny([Matrículas],Numeros,Occurrence.Last)-1),

/*las cadenas recuperadas las llevamos a nuevas columnas*/
Izqda = Table.AddColumn(Source,"Texto_izda", primero),
Dcha = Table.AddColumn(Izqda,"Texto_dcha", ultimo),
Final= Table.AddColumn(Dcha,"por dcha", por_dcha),

Interior = Table.AddColumn(Final,"Texto_interior", each Text.Range([Matrículas],
Text.PositionOfAny([Matrículas],Numeros,Occurrence.First),
Text.PositionOfAny([Matrículas],Numeros,Occurrence.Last) -
Text.PositionOfAny([Matrículas],Numeros,Occurrence.First) + 1 )),

/*A modo estadístico trasladamos las posiciones relevantes a columnas nuevas */
largo = Table.AddColumn(Interior,"num caract total", NumCaract),
primer= Table.AddColumn(largo,"primer num", PosIni),
ult= Table.AddColumn(primer,"ult num", PosUlt)

in
ult

Hay una breve explicación en cada línea, añadida con comentarios...
Aunque se puede observar que las funciones empleadas, y ya comentada, son:
Text.Start
Text.End
Text.Range
y Text.PositionOfAny

y las acciones necesarias para ir añadiendo columnas (Table.AddColumn) a la consulta e incorporar los datos tratados.

El resultado se puede visualizar en la primera imagen del post... como era de esperar el resultado es correcto... y como decía nuestro lector, algo más intuitivo que las funciones matriciales.

Seguir leyendo...

Identificando la posición del último número

Hace unos días publiqué un post donde exponía una manera de extraer los primeros y últimos caracteres no numéricos de una cadena de texto (ver aquí).
Si bien al ponerla en práctica y probarla en real salto un fallo... y es que cuando se repetían los dígitos en la cadena de texto o había otras letras intercaladas, mis fórmulas, no recuperaban correctamente los caracteres no numéricos finales.
:'(

Así pues me puse a buscar alternativas.

Una primera 'solución' la encontré para aquellos casos que la estructura de la cadena de texto era:
letras dígitos (repetidos o no) letras
Para este caso la fórmula matricial necesaria era:
=DERECHA(B8;LARGO(B8)-(MIN(SI.ERROR(ENCONTRAR(digitos;B8);LARGO(B8)))+SUMAPRODUCTO(LARGO(B8)-LARGO(SUSTITUIR(B8;digitos;""))))+1)
siendo B8 una cadena de texto como:
MJA23112223PK

Recuerda que digitos era un nombre definido que representa los valores de 0,1,2,3,4,5,6,7,8 y 9. Revisa el link comentado al inicio.

La clave de esta fórmula es descubrir cuantos dígitos existen en la cadena.. lo que conseguimos con la fórmula:
SUMAPRODUCTO(LARGO(B8)-LARGO(SUSTITUIR(B8;digitos;"")))
que identifica el número total de números en la matrícula anterior...
Luego a ese número le sumamos la primera posición de dígito encontrado y listo, ya tendríamos el dato buscado, i.e., la posición del último dígito dentro de la cadena de texto.

Y esta fórmula solucionaba el problema encontrado inicialmente... pero como no hay dos sin tres, surgió una nueva situación.. un nuevo revés.
Resulta que era posible que nuestra matrícula tuviera más letras intercaladas entre los dígitos!!!
Y obviamente la fórmula anterior, de nuevo, no era válida.
Tiempo de repensar la fórmula.

La solución definitiva, para cualquier caso, incluso para matrículas con patrón:
letras dígitos (repetidos o no) letras dígitos (repetidos o no) letras
por ejemplo, en la celda B11
M4212AB3434PKY
fue:
=DERECHA(B11;LARGO(B11)-MAX(SI(ESNUMERO(VALOR(EXTRAE(B11;FILA(INDIRECTO("1:"&LARGO(B11)));1)));FILA(INDIRECTO("1:"&LARGO(B11))))))
(ejecutada, por supuesto, matricialmente)

Para este caso, opté por identificar caracter a caracter, y buscar aquellos que fueran numéricos.. para obtener la posición únicamente de éstos.
Si revisamos la fórmula, la primera parte
EXTRAE(B11;FILA(INDIRECTO("1:"&LARGO(B11)));1)
descompone caracter a caracter la cadena de texto.

Con la función VALOR convertimos a número los dígitos... ya que con la función EXTRAE los números eran tratados como texto.
ESNUMERO convierte a valor lógico (VERDADERO o FALSO) lo que sea número, lo cual aprovechamos para con el SI condicional obtener su posición.

El resto es como siempre, aplicamos la función DERECHA y LARGO para obtener los caracteres finales (por la derecha) hasta el último dígito encontrado.

Conseguido!!!... tres intentos confirman el dicho: A la tercera va a la vencida
:D

Una última curiosidad, en el proceso de búsqueda, entre prueba y error, salto una fórmula curiosa que me permitía sumar los valores de los dígitos...
Si en B15 tenemos
MJA23B112223PK
la fórmula matricial sería:
=SUMAPRODUCTO(SI.ERROR(VALOR(EXTRAE(B15;FILA(INDIRECTO("1:"&LARGO(B15)));1));0))
con resultado, para el ejemplo, de 16 (2+3+1+1+2+2+2+3).

Curioso... no sé para que puede ser útil, pero ahí queda.

Seguir leyendo...

Power Query: funciones M tipo fecha.

Aprenderemos hoy algunas de las funciones M de Power Query más habituales en cuanto a trabajar con fechas.
En concreto listaré:
Date.EndOfMonth(["Campo Fecha"]): devuelve la fecha del último día del mes de la fecha informada.

Date.DaysInMonth(["Campo Fecha"]): devuelve el número de días del mes de la fecha dada.

Date.StartOfWeek(["Campo Fecha"]): vemos el primer día de la semana (lunes por defecto), pero con un segundo parámetro en la función
Day.Sunday = 0
Day.Monday = 1
Day.Tuesday = 2
Day.Wednesday = 3
Day.Thursday = 4
Day.Friday = 5
Day.Saturday = 6
podemos dirigirlo al día deseado.

Date.EndOfWeek(["Campo Fecha"]): similar a la función anterior. Vemos el último día de la semana.

Date.WeekOfMonth(["Campo Fecha"]): número de la semana dentro de un mes, esto semana 1, 2, 3, 4 o 5 del mes.

Date.WeekOfYear(["Campo Fecha"]): número de la semana del año.

y un muy largo etcétera... de hecho, hay muchísimas más posibilidades de trabajar con funciones de fecha en Power Query (en su lenguaje M) que directamente en la hoja de cálculo con las funciones estándar de Excel.

Veamos algunos ejemplos a partir de un listado de fechas:

Comenzamos cargando la tabla al editor de consultas de Power Query.
Desde la Ficha Datos > grupo Obtener y transformar > Desde una tabla, y ya en el editor de consultas, por estética, cambiamos el tipo de dato a tipo Fecha (Date) en lugar de Fecha/Hora.

Ahora podemos ir añadiendo nueva columnas personalizadas desde la ficha de Agregar columna, y en ella hacer uso de las funciones comentadas.

por ejemplo, añadimos una nueva columna para obtener el último día del mes...
=Date.EndOfMonth([Campo Fecha])

Así podríamos añadir el resto de funciones comentadas
=Date.DaysInMonth([Campo Fecha])
=Date.StartOfWeek([Campo Fecha])
=Date.EndOfWeek([Campo Fecha])
=Date.WeekOfMonth([Campo Fecha])
=Date.WeekOfYear([Campo Fecha])

Una alternativa más simple consistiría es ir duplicando el '[Campo fecha]' y luego haciendo clic derecho sobre ese nuevo campo duplicado buscar la opción de Transformar... lo que nos ofrece categorizado (Año, Trimestre, Mes, Semana y Día) la gran mayoría de funciones de fecha.

Esta opción es muy simple y no requiere ningún tipo de conocimiento del lenguaje y funciones M de Power Query.. tan solo algo de paciencia.

Finalmente una alternativa más sería acceder al Editor avanzado de la consulta y escribir todo el código necesario:
let
Source = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Campo Fecha", type date}}),

#"Fin_mes" = Table.AddColumn(#"Changed Type", "Fin de mes", each Date.EndOfMonth([#"Campo Fecha"]), type date),

#"Dias_mes" = Table.AddColumn(#"Fin_mes", "Días mes", each Date.DaysInMonth([#"Campo Fecha"]), Int64.Type),

#"Inicio_semana" = Table.AddColumn(#"Dias_mes", "Inicio semana", each Date.StartOfWeek([#"Campo Fecha"]), type date),

#"Fin_semana" = Table.AddColumn(#"Inicio_semana", "Fin semana", each Date.EndOfWeek([#"Campo Fecha"]), type date),

#"Semana_mes" = Table.AddColumn(#"Fin_semana", "Semana mes", each Date.WeekOfMonth([#"Campo Fecha"]), Int64.Type),

#"Semana_año" = Table.AddColumn(#"Semana_mes", "Semana año", each Date.WeekOfYear([#"Campo Fecha"]), Int64.Type)

in
#"Semana_año"

Esto nos permite ajustar el número de líneas / acciones del código de nuestra consulta, así como personalizar más ágilmente los tipos de datos de los campos o, simplemente, los nombres elegidos para estas nuevas columnas.

El resultado buscado quedaría:

Seguir leyendo...

VBA: FileDateTime abrir el fichero más reciente

Hoy toca aprender algo de una función VB poco conocida: FileDateTime.
Función que nos permite dar respuesta a un usuario:

[...]  ¿Cómo puedo abrir el archivo más reciente? Es que en la carpeta donde va a buscarlo va a haber varios que se llaman muy parecido, sólo cambia el final del nombre que será la hora de creación (*o7_35.xlsm, *10_28.xlsm, *18_13.xlsm)[...]

Lo interesante de esta función de VB es que nos devuelve un dato tipo Variant que indica la fecha y hora cuando se creó o modificó por última vez un archivo... precisamente el dato relevante que necesitamos para resolver nuestro problema: Abrir el fichero más reciente.

Insertamos el siguiente procedimiento 'Sub' en un módulo estándar:

Sub AbrirFicheroMasReciente()

Dim Ruta As String, archivo As String, ultArchivo As String
Dim ultFecha As Date, UFM As Date 'fecha última modificación

Ruta = "E:\excelforo\"
'controlamos la ruta es´te correctamente escrita, con el último separador \
If Right(Ruta, 1) <> "\" Then Ruta = Ruta & "\"
'definimos el tipo de fichero sobre el que trabajar
'cualquier fichero de Excel
archivo = Dir(Ruta & "*.xls*", vbNormal)

If Len(archivo) = 0 Then
    'verificamos que existe algú nfichero de Excel en la carpeta
    MsgBox "Mala suerte... No existe ningún archivo de Excel en esta carpeta", vbExclamation
    Exit Sub    'salimos del procedimiento
End If

'recorremos todos los ficheros de Excel existentes
Do While Len(archivo) > 0
    'la función de VBA FileDateTime
    'Devuelve una Variant(Date) que indica la fecha y hora cuando se creó o modificó por última vez un archivo.
    UFM = FileDateTime(Ruta & archivo)
    'comparamos con la última fecha revisada
    'este condicional nos conduce a la fecha mayor, i.e., la más reciente
    If UFM > ultFecha Then
        ultArchivo = archivo
        ultFecha = UFM
    End If
    'liberamos la variable y preparamos para el siguiente archivo
    archivo = Dir
Loop

'acabamos abriendo el fichero con la fecha de modificación más reciente...
Workbooks.Open Ruta & ultArchivo

End Sub

Y listo, podemos ejecutar nuestra macro, que abrirá el fichero con la mayor fecha de modificación, es decir, la más reciente.

Seguir leyendo...

Extraer info de una cadena de texto

En una reciente formación me plantearon un problema con el que se encontraban en su día a día...
Trabajaban con unas matriculas sin un patrón claro.. solo tenían seguro que hay 1,2 o 3 letras al inicio, seguidos de varios dígitos,
y finaliza con otras 1,2 o 3 letras; por ejemplo:
M1234PK
MJ12345P
MJ123456PK
MJA1234567PK
MJ123PKK
MJA12PKK

El objetivo del ejercicio es recuperar la parte inicial y final de las matrículas, que corresponden a los caracteres de texto, en cualquier caso.

Lo primero que haremos es escribir la secuencia numérica básica 0,1,2,3,4,5,6,7,8 y 9 en un rango H1:H10 y le asignaremos un nombre definido que llamaré 'digitos'
digitos =Hoja1!$H$1:$H$10

Esto facilitará la explicación.

Si nuestras matrículas se encuentran en el rango B2:B7, para recuperar el inicio de la matrícula en la celda C2 escribiremos y ejecutaremos matricialmente (presionando Ctrl+Mayusc+Enter):
=IZQUIERDA(B2;MIN(SI.ERROR(ENCONTRAR(digitos;B2);LARGO(B2)))-1)

De igual forma para recuperar la parte final de la matricúla en D2 escribiremos y ejecutaremos matricialmente (presionando Ctrl+Mayusc+Enter):
=DERECHA(B2;LARGO(B2)-MAX(SI.ERROR(ENCONTRAR(digitos;B2);0)))

La explicación de estas fórmulas se basa en el comportamiento matricial de la función ENCONTRAR, que localiza la posición dentro de la matrícula de los dígitos; por ejemplo ENCONTRAR(digitos;B2), siendo B2= M1234PK , devuelve la matriz:
{#¡VALOR!;2;3;4;5;#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!}

es decir, nos indica que los valores numéricos están en la posición 2, 3, 4 y 5; mientras que los demás son valores tipo texto.
Aprovechándonos del error devuelto, gestionamos con la función SI.ERROR para 'cambiar los errores' por el número total de caracteres.
SI.ERROR(ENCONTRAR(digitos;B2);LARGO(B2))
La matriz ahora será:
{7;2;3;4;5;7;7;7;7;7}

Ahora recuperamos el valor mínimo, que devuelve obviamente el primer caracter numérico, en el ejemplo el segundo caracter.
MIN(SI.ERROR(ENCONTRAR(digitos;B2);LARGO(B2)))-1

Finalmente aplicamos IZQUIERDA para extraer el inicio de la matrícula.

De forma análoga operamos para obtener el final de la matrícula.
ENCONTRAR localiza la posición dentro de la matrícula de los dígitos; por ejemplo ENCONTRAR(digitos;B2), siendo B2= M1234PK , devuelve la matriz:
{#¡VALOR!;2;3;4;5;#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!;#¡VALOR!}

es decir, nos indica que los valores numéricos están en la posición 2, 3, 4 y 5; mientras que los demás son valores tipo texto.
Aprovechándonos del error devuelto, gestionamos con la función SI.ERROR para 'cambiar los errores' por ceros.
SI.ERROR(ENCONTRAR(digitos;B2);0)
La matriz ahora será:
{0;2;3;4;5;0;0;0;0;0}

Ahora recuperamos el valor máximo, que devuelve obviamente el primer caracter numérico, en el ejemplo el segundo caracter.
MAX(SI.ERROR(ENCONTRAR(digitos;B2);0))

Finalmente aplicamos DERECHA para extraer el final de la matrícula.

Como se puede comprobar para los distintos casos listados en B2:B7, las fórmulas retornan los valores deseados...

Seguir leyendo...

Power Query: Dividir por Separadores

Hoy usaremos algunas funcionalidades de Power Query (Obtener y transformar) en nuestras hoja de cálculo Excel, así como un par de funciones interesantes.

El asunto es que nos han enviado un listado con unos conceptos agregados (aaa, bbb, ccc y ddd ) en un solo campo de nuestra tabla.. siendo nuestro objetivo obtener información individualizada de cada uno de esos conceptos.
Importante:!!: Sabemos que el criterio de reparto es proporcional, a partes iguales...
Esta sería nuestra tabla:

El primer paso es, como siempre, cargar en nuestro Editor de consultas de PQ nuestra tabla.
Desde la ficha de Datos > grupo Obtener y transformar > botón Desde una tabla.

Con la consulta a la vista, desde la ficha Agregar columna > grupo General > botón Columna personalizada configuraremos la siguiente fórmula:
=[Importe]/List.Count(Text.Split([Concepto],"|"))

Fijémonos en las dos funciones de Power Query:
Text.Split - función de texto
devuelve una lista a partir de una cadena, teniendo en cuenta el separador indicado
Text.Split([Concepto],"|")

List.Count - función de lista
devuelve un conteo de elementos en una lista existente
List.Count(listado)

En nuestro caso hemos aprovechado para operar y obtener el reparto del 'Importe' correspondiente.

En el siguiente paso vamos a generar cuatro columnas condicionales nuevas, una por cada elemento existente: aaa, bbb, ccc y ddd.
Podemos hacerlo una a una desde la ficha Agregar columna > grupo General > botón Columna condicional

Prestemos atención a la opción de Salida por 'Columna' y no por 'Valor'.

Repetiríamos esta configuración para los demás elementos... bbb, ccc y ddd

También podríamos trabajarlo con el Editor Avanzado con el código:
let
Source = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
#"Changed Type1" = Table.TransformColumnTypes(Source,{{"Fecha Registro", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Num Eltos.", each [Importe]/List.Count(Text.Split([Concepto],"|"))),
#"Added Conditional Column" = Table.AddColumn(#"Added Custom", "aaa", each if Text.Contains([Concepto], "aaa") then [#"Num Eltos."] else 0),
#"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "bbb", each if Text.Contains([Concepto], "bbb") then [#"Num Eltos."] else 0),
#"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "ccc", each if Text.Contains([Concepto], "ccc") then [#"Num Eltos."] else 0),
#"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "ddd", each if Text.Contains([Concepto], "ddd") then [#"Num Eltos."] else 0)
in
#"Added Conditional Column3"

Observa como cada nueva línea es idéntica a la anterior en cuanto a estructura, por lo que se hace muy cómodo copiar y pegar, modificando obviamente los datos correspondientes.

Nuestra consulta, en este momento queda:

LLegados a este punto, con la información dividida por columnas, solo nos quedan un par de pasos simples.
De manera voluntaria quitaremos los campos 'originales' de 'Concepto', 'Importe' y 'Num Eltos.'.
Basta seleccionarlos y con el ratón, botón derecho, y Quitar columnas.

El último paso es igual de sencillo, seleccionaremos las cuatro columnas condicionales creadas (aaa, bbb, ccc y ddd) y desde la ficha Transformar > grupo Cualquier columna > botón Anular dinamización de columnas, quedando nuestra consulta:

Ya podemos devolver a la hoja de cálculo nuestra consulta resultante, así presionamos Cerrar y cargar en... y elegimos destino.
Solo nos falta crear una tabla dinámica sobre la consulta y trabajarla para obtener algo como lo siguiente:

Meta alcanzada.

Seguir leyendo...