jueves, 13 de diciembre de 2018

VBA: Las formas a modo de evaluación

Hoy trabajaremos con los objetos Shapes para dar una posible solución a un lector que preguntaba:
[...]estoy haciendo un pequeño juego de preguntas para mi hijo pequeño, y a la hora de responder afirmativamente a una cuestión desearía que saliera un signo o dibujo con un smile verde sonriente y en caso negativo que saliera un smile rojo triste. No soy capaz de vincular esas imágenes con la respuesta, sólo consigo que me ponga correcto o incorrecto[...]


Lo primero consiste en añadir unas imágenes o formas en mi caso con el aspecto deseado...
Para nuestro ejemplo dispondremos de cuatro preguntas a responder, por lo que añadimos cuatro muñecos verdes y cuatro rojos superpuestos dos a dos por cada pregunta...
A estas formas las he llamado:
'bien1' 'mal1'
'bien2' 'mal2'
'bien3' 'mal3'
'bien4' 'mal4'

VBA: Las formas a modo de evaluación



Por otra parte, para controlar que las respuestas dadas en las celdas C17,C20,C23,C26 son correctas, las cruzamos con las respuestas correctas de control en G17,G20,G23,G26, cosa que comprobamos en H17,H20,H23,H26 con una simple prueba lógica:
=C17=G17

VBA: Las formas a modo de evaluación



Tenemos todo lo necesario:
1-las ocho formas
2-las preguntas planteadas
3-y su corrección

solo nos falta controlar las formas (rojo o verde) según corresponda, lo que haremos con un evento 'Worksheet_Change' de hoja.


Así pues, insertamos el siguiente evento 'Worksheet_Change' en la ventana de código de la hoja:

Private Sub Worksheet_Change(ByVal Target As Range)
'comenzamos controlando qué celdas están supervisadas
If Not Intersect(Target, Range("C17,C20,C23,C26")) Is Nothing Then
    'asociamos número de filas a preguntas
    Select Case Target.Row
        Case Is = 17: pregunta = 1
        Case Is = 20: pregunta = 2
        Case Is = 23: pregunta = 3
        Case Is = 26: pregunta = 4
    End Select
    'comprobamos se ha rellenado con algo las celdas de respuesta
    'si se ha completado con algo se evalua
    If Target.Value <> "" Then
        'se verfica con la celda control de la col H
        'si es VERDADERO se ha acertado
        If Cells(Target.Row, "H") = True Then
            'y entonces sacamos el muñeco verde y ocultamos el rojo
            ActiveSheet.Shapes("bien" & pregunta).Visible = msoTrue
            ActiveSheet.Shapes("mal" & pregunta).Visible = msoFalse
        Else
            'en caso contrario sacamos el muñeco rojo y ocultamos el verde
            ActiveSheet.Shapes("bien" & pregunta).Visible = msoFalse
            ActiveSheet.Shapes("mal" & pregunta).Visible = msoTrue
        End If
    Else
        'si no se ha rellenado nada se ocultan ambas figuras
        ActiveSheet.Shapes("bien" & pregunta).Visible = msoFalse
        ActiveSheet.Shapes("mal" & pregunta).Visible = msoFalse
    End If
End If

End Sub



Y podemos probar el cuestionario y comprobar como funciona según lo esperado.

martes, 11 de diciembre de 2018

Power Query: Desapilando columnas de datos

Hace algunos días expuse como 'desapilar' columnas de datos empleando unas funciones estándar de nuestra hoja de cálculo favorita (ver post aquí).

La idea de hoy es conseguir el mismo resultado empleando Power Query para Excel (Obtener y transformar).


Supondremos nuestro listado de datos apilado, el cuál hemos convertido en Tabla...

Power Query: Desapilando columnas de datos



El objetivo a lograr es disponer por columnas los conceptos listados...

Para ello usaremos algunas funciones M de Power Query, como:
List.Sum(listado): que nos devuelve una suma a partir de los elementos del listado indicado.
Table.SelectRows(tabla_de_trabajo, condición as function)): Devuelve una tabla que contiene únicamente las filas que responden a la condición dada.
List.Distinct(listado, optional equationCriteria as any, criteria as any): Filtra un listado abajo mediante la eliminación de duplicados. Podemos especificar un valor de criterio de ecuación de manera opcional para controlar la comparación de igualdad. Se elige el primer valor de cada grupo de igualdad.
Y alguna otra más simple ya utilizada anteriormente... o que sencillamente requiere el uso del Asistente de Power Query.


El primer paso consiste, como siempre, en cargar nuestra Tabla de información.

El código completo de la consulta sería:
let
Source = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"concepto", type text}, {"valores", type text}}),
Columna1 = Table.AddColumn(ChangedType, "Cond1", each 1),
AddIndex = Table.AddIndexColumn(Columna1, "Fila", 1, 1),
RunTotal = Table.AddColumn(AddIndex, "Id creciente", each let concepto=[concepto],Fila=[Fila] in
List.Sum(Table.SelectRows(AddIndex, each [Fila]<=Fila and [concepto]=concepto)[Cond1])), BorraColumnas = Table.RemoveColumns(RunTotal,{"Cond1","Fila"}), Pivoted_Column = Table.Pivot(BorraColumnas, List.Distinct(BorraColumnas[concepto]), "concepto", "valores") in Pivoted_Column


Que desgranamos en los pasos relevantes...
Las dos primeras líneas de código son las básicas que nos da directamente el asistente de consultas:
Source = Excel.CurrentWorkbook(){[Name="Tabla1"]}[Content],
ChangedType = Table.TransformColumnTypes(Source,{{"concepto", type text}, {"valores", type text}}),


con ellas se cargan los datos de la tabla 'Tabla1' del libro actual, y posteriormente forzamos el tipo de datos para las dos columnas.


La siguiente línea añade una columna que rellenamos con unos...
Columna1 = Table.AddColumn(ChangedType, "Cond1", each 1),

Estos unos los usaremos en pasos siguientes para acumular y obtener un orden...


Otro paso consiste en añadir una columna tipo Índice que comenzará desde 1:
AddIndex = Table.AddIndexColumn(Columna1, "Fila", 1, 1),

columna que emplearemos para construir nuestras condiciones...

Hasta ahora tenemos en nuestra consulta este aspecto:

Power Query: Desapilando columnas de datos



Siguiente paso, muy importante. Ahora toca obtener una acumulado creciente condicionado a nuestros conceptos...
Esto se consigue con la línea de código:
RunTotal = Table.AddColumn(AddIndex, "Id creciente", each let concepto=[concepto],Fila=[Fila] in
List.Sum(Table.SelectRows(AddIndex, each [Fila]<=Fila and [concepto]=concepto)[Cond1])),


Añade la columna nueva 'Id creciente', donde se incrementa/acumula el valor de la columna 'Cond1' cuando el 'concepto' coincide y el número de fila del 'Indice' creado va aumentando...

Power Query: Desapilando columnas de datos


Este paso es fundamental para alcanzar nuestra meta, ya que nos permite tener un nexo de unión común para cada pack de datos ('empresa', 'país', 'facturación').

Los pasos últimos son sencillos..
Uno nos permite eliminar columnas que ya no necesitamos ver ("Cond1" y "Fila")
BorraColumnas = Table.RemoveColumns(RunTotal,{"Cond1","Fila"}),

Y el último transforma la disposición de datos a modo de tabla dinámica... siendo desde ahora los conceptos nuevas columnas de información:
Pivoted_Column = Table.Pivot(BorraColumnas, List.Distinct(BorraColumnas[concepto]), "concepto", "valores")

tomando como referencia de fila única nuestra columna acumulada...


Conseguido... tenemos nuestros datos 'desapilados'.

miércoles, 5 de diciembre de 2018

Desapilando columnas de datos en Excel.

Hoy estaba viendo algunos vídeos (sobre Excel claro) y me he fijado en una explicación del gran Oz du Soleil en su canal de You Tube (que te recomiendo!) - ver aquí -

En concreto el vídeo explicaba como usando las nuevas funciones dinámicas de la última versión de Excel se podía desapilar columnas de datos ('Unstack a Column of Records').
En mi caso pensé en la forma de conseguir lo mismo con las funciones de toda la vida ;-)


Imaginemos tenemos unos datos como siguen, dispuestos en una única columna...

Desapilando columnas de datos en Excel.



Y queremos llegar, mediante fórmulas a:

Desapilando columnas de datos en Excel.



El primer paso consiste es obtener el patrón de repetición: Empresa, País, Facturación
así en el rango D3:D32 añadimos la fórmula:
=RESIDUO(FILAS($C$3:C3);3)

con FILAS($C$3:C3) tendremos la numeración de las filas 1,2,3.. 26,27
y al aplicarle la función RESIDUO tendremos 1, 2 y 0 como resto de dividir el número de fila entre tres, lo que nos proporciona el patrón 1,2,0

Desapilando columnas de datos en Excel.



Siguiente paso intermedio. En el rango E3:E32 añadimos la fórmula:
=CONTAR.SI($D$3:D3;D3)

consiguiendo una ordenación de lo que deberían ser los registros si estuvieran bien situados.

Desapilando columnas de datos en Excel.



Casi listos. Falta componer el listado por columnas.
Disponemos encabezados por filas y columnas.

Desapilando columnas de datos en Excel.



Y acabamos insertando, en el rango H4:J13, la fórmula:
=INDICE($C$3:$C$32;SUMAPRODUCTO(($E$3:$E$32=$G5)*($D$3:$D$32=H$2)*(FILA($B$3:$B$32)-2)))

Desapilando columnas de datos en Excel.



La función descrita recupera los datos de la columna de 'valores' con la función INDICE.. basándose en la posición de fila dentro del rango seleccionado... cosa que hacemos con la función SUMAPRODUCTO y su estructura matricial condicionada...

Obviamente la forma descrita por Oz du Soleil es mucho más dinámica y ágil.. pero si aún no has dado el salto de versión, esta es una buena alternativa ;-)