Mapas por regiones en Excel

Frecuentemente nos encontramos con la necesidad de mostrar gráficamente ciertos valores representados sobre zonas geográficas: Países, Regiones de un país (Comunidades Autónomas, por ejemplo), provincias, etc...), y nos resulta complicada tal tarea. En muchos casos nos complicamos la vida con objetos manuales o similares.

Hoy aprenderemos, empleando los Mapas 3D de Excel a conseguir nuestro objetivo.

Lo primero los datos. En un rango disponemos las regiones y los importes a representar:

Se puede observar como reconoce igualmente los idiomas empleados (Castellano, Galego, Català, Valenciano o cualquier otro...)

Seleccionamos el rango B2:C10 y desde la ficha Insertar > grupo Paseos > botón Mapas 3D acedemos a la ventana de Mapas 3D.

En la ventana de Mapas 3D:
1- Seleccionamos el tipo de gráfico a Visualización por región (el icono de la derecha)
2- Arrastramos de la Lista de campos el campo 'Comunidad autónoma' al área de Ubicación.
Importante en este punto es indicar que el tipo es 'Estado o Provincia'

Si en este punto no reconoce las regiones prueba con otras posibilidades, por ejemplo, 'País o región').
3- Arrastraos al área de Valor el campo de 'Importes'.

El aspecto que tenemos en este momento:

Aunque ya tenemos el gráfico que deseábamos por regiones, podemos adornarlo un poco más...
Por ejemplo, me gusta ver el mapa en 2D.
Así pues, desde la cinta de opciones de la ventana de Mapas 3D: Inicio > grupo Mapa > botón Mapa plano.
Y también prefiero añadir las etiquetas de ciudades, países, etc... sobre el mapa: Inicio > grupo Mapa > botón Etiquetas de mapa

También podemos cambiar el color azul de la capa en que se muestran los importes.
Para ello desde el panel de capas (a la derecha normalmente) buscaremos las Opciones de capa.
Entre otras cosas podemos cambiar la:
- Escala de colores (me gusta ponerlo al 50%)
- La opacidad (al 100%)
- El color (en esta ocasión lo colorearemos en Rojo)

Listo.. Nuestro mapa 3D, nuestro 'paseo' esta acabado.
Podemos cerrar la ventana. Cuando necesitemos ver nuestro mapa bastará acceder, desde la hoja de cálculo, a la ficha Insertar > grupo Paseos > botón Mapas 3D, donde se mostrarán los Mapas 3D existentes.. podemos seleccionar uno de ellos para que se haga visible.

Seguir leyendo...

Calculando el cambio a devolver

Hoy toca algo sencillo pero muy práctico. Construiremos una calculadora de cambio.

Con una fórmula bastante simple sabremos en cada momento qué cambio devolver en una transacción y qué billetes y monedas en concreto dar.

En el ejemplo si el importe a pagar es de 2.895,35 euros y se ha entregado un importe de 4.000,00 euros, el importe a devolver es: 1.104,65 euros devuelto en la siguiente forma:
2 billetes de 500 euros
1 billetes de 100 euros
2 monedas de 2 euros
1 moneda de 50 céntimos
1 moneda de 10 céntimos
1 moneda de 5 céntimos

Precisamente ese reparto de monedas y billetes es el que vamos a calcular.

Disponemos del valor facial de billetes y monedas en el rango C7:C21, mientras en D5 tenemos el importe a devolver.
En ese caso en D7 añadimos, para el primer caso:
=ENTERO(D$5/C7)
mientras que en D8 y siguientes:
=ENTERO(($D$5-SUMAPRODUCTO($C$7:C7;$D$7:D7))/$C8)

El cálculo es simple. Sobre la cantidad pendiente a devolver, vemos respecto al valor de la moneda o billete correspondiente, las veces que cabe de forma completa dicho valor.
Por ejemplo, para el primer caso sobre lo pendiente de 1.104,65 euros caben dos billetes de 500 euros completos, pero no un tercero...
El siguiente valor pendiente sería de 1.104,65 menos los 1.000,00 anteriores (dos billetes de 500 euros), i.e., sobre 104,65 donde cabe 1 billete de 100 euros completo... y así sucesivamente.

Esto se consigue restando al valor original en D5 las entregas previas:
$D$5-SUMAPRODUCTO($C$7:C7;$D$7:D7)

El resto es simple, al dividir esa cantidad entre el valor facial de moneda o billete y quedarnos con su parte entera... asegurándonos que no se devuelve en exceso...
=ENTERO(($D$5-SUMAPRODUCTO($C$7:C7;$D$7:D7))/$C8)

Seguir leyendo...

Power Query: Cuadro amortización préstamo francés

Veremos hoy un ejercicio interesante (y un clásico) dentro de la matemáticas financieras, desarrollaremos el plan de amortización de un préstamo francés, pero empleando Power Query / Obtener y transformar.

Para hacer estos cálculos en el entorno del editor de consultas de Power Query necesitamos algunos de los conceptos o cálculos financieros básicos, que son especialmente estas dos fórmulas que nos permiten obtener el importe de la cuota periódica del préstamo francés:
Cuota = C₀ * i / (1-(1+i)^-n)
o para obtener la parte de capital amortizado dentro de una cuota para un periodo concreto k:
A_k = A₁ * (1+i)^(k-1)
siendo
A₁ = C₀ * i / ((1+i)ⁿ-1)

El primer paso será subir los datos de nuestra tabla de condiciones del préstamo al Editor de Power Query (desde la ficha Datos > grupo Obtener y transformar > Desde una tabla)

Una vez subida, con el fin de pasar como parámetros las tres condiciones:
Principal del préstamo
Tipo de interés anual
Plazo en años
En el editor duplicaremos la tabla cargada, aprovechando para cambiarles el nombre por:
principal
interes
plazo
Para duplicar las tablas basta hacer clic derecha sobre ella y presionar Duplicar

A continuación lo convertiremos en parámetros, para esto basta ir tabla por tabla haciendo clic derecho sobre el valor a convertir y Rastrear desagrupando datos:

En el siguiente paso generaremos dos funciones personalizadas dentro editor de Power Query (ver cómo).

Nuestras funciones replican los cálculos financieros comentados más arriba.

Así pues para obtener la cuota mensual de un préstamo francés la función sería (a la que he llamado: 'FxCuotaMensual'):

(Interes, Num_periodos) => let
  calculo1 = Interes*Number.Power(1+Interes, Num_periodos)/(Number.Power(1+Interes, Num_periodos)-1)

in
  calculo1

con dos variables necesarias: el tipo de interés y el número total de periodos en la vida del préstamo.


Por otra parte, la segunda función a definir, nos permitirá conocer de esa cuota, cuál es la cantidad de principal del préstamo amortizado; función a la que he llamado: 'FxPpalMensual':

(Capital, Interes, Num_periodos, Periodo) => let
  calculo1 = (Capital * Interes) / ((Number.Power(1+Interes, Num_periodos)-1)) *  Number.Power(1+Interes, Periodo-1)

in
  calculo1

Para crear nuestra consulta final necesitamos generarla como consulta en blanco (desde el editor, menú Inicio > grupo Nueva consulta > Nuevo origen > Otros orígenes > Consulta en blanco)... llamaré a mi consulta: 'Cuadro_Amortizacion'.
Dentro de la ventana del Editor avanzado escribiremos:

let  
// damos valor a las variables a emplear, 
// tomando los parámetros cargados desde la Tabla de condiciones de la hoja
ppal=principal,  
ti=interes/12,  
nper=plazo*12,  

//Generamos una lista desde 0 hasta el total de periodos (nper)
Mes= Table.RenameColumns(
        Table.FromList(
           List.Generate(  
               ()=>0,  
               each _ <=nper,  
               each _ +1),
           Splitter.SplitByNothing(), null, null, ExtraValues.Error),
       {{"Column1", "Mes"}}),

//Con la función pesonalizada FxCuotaMensual creada recuperamos el valor de la cuota para cada periodo
CuotaMensual = Table.AddColumn(Mes, "Cuota mensual",   each if [Mes] = 0 then 0 else ppal*(FxCuotaMensual(ti,nper)), type number),

//de igual forma, la función pesonalizada FxPpalMensual nos permite saber la parte amortizada en cada periodo
CapitalMensual = Table.AddColumn(CuotaMensual, "Capital mensual", each if [Mes] = 0 then 0 else FxPpalMensual(ppal,ti,nper,[Mes])),

//el cálculo de los intereses en cada periodo es fácil de obtene por diferencias
InteresMensual=Table.AddColumn(CapitalMensual , "Interes mensual",   
     each if [Mes] = 0 then 0 else [Cuota mensual]-[Capital mensual], type number),

//finalmente para conocer la deuda pendiente al final de cada perido (después el pago de la cuota)
//obtenemos un acumulado del principal devuelto, al que le restamos el principal solicitado
DeudaPendiente = Table.AddColumn(InteresMensual, "Deuda pendiente", 
            each if [Mes] = 0 then ppal else ppal-List.Sum(List.Range(InteresMensual[Capital mensual],0,[Mes]+1)))
in
DeudaPendiente

Al aceptar nuestra consulta vemos el resultado esperado...

Queda comentado en cada línea del código de la consulta el sentido de esos cálculos...

Una vez devuelto a la hoja de cálculo es fácil comprobar el correcto funcionamiento de la consulta.

Seguir leyendo...

Gráfico Anillo para mostrar avance

Construiremos un gráfico de anillo para representar el grado de avance o de consecución de un parámetro o indicador.
Supongamos que nuestro ratio (o KPI) nos mide el grado de ventas alcanzado respecto al objetivo presupuestado.
Nuestro gráfico tendrá esta forma:

Se observa como la parte alcanzada se muestra en azul más oscuro, dividida en tramos de 10%...
y en color más claro lo que queda pendiente de conseguir.

Empezamos disponiendo en el rango E1:N1 una serie de diez unos:

He repartido diez unos por que me interesa dividir el anillo en diez partes... tu puedes incorporar tantos unos como partes requieras.

Seleccionando el rango de unos E1:N1 insertamos un gráfico de anillo

Eliminamos la leyenda.

Damos algo de formato.
Aplicamos un mismo color de relleno para todos los puntos de la serie, y en las opciones de serie ajustamos:
- Tamaño de agujero del anillo al 60%
- Sección de los anillos al 5%

En el paso siguiente agregaremos una nueva serie de datos, con los valores del progreso en B2:C2.
Para esto presionamos Seleccionar datos > botón Agregar serie:

Marcamos el punto uno de la serie recién agregada, el que representa el porcentaje logrado (en el ejemplo el 33%) y le damos formato de relleno > Sin relleno

Y el punto dos de la última serie le cambiamos el color de relleno a relleno sólido a un azul clarito (Azul, Énfasis 1, Claro 60%).

Aprovechando está seleccionado ese punto presionaremos la opción de Cambiar tipo de gráfico de series... para llevar la última serie agregada al Eje secundario

el resto es decorar algo más el gráfico... cambiando el título del gráfico, añadiendo una forma con el dato de B2, etc...

Seguir leyendo...

Power Query: Cargar datos en el modelo de datos

Normalmente estamos acostumbrados, al trabajar con Power Query (Obtener y Transformar), a Cerrar y Cargar en la hoja de cálculo del libro donde trabajamos... pero existe otra posibilidad.

Y es que este complemento está (o puede estar) relacionado con otro Power de Excel: Power Pivot y su Modelo de datos..

De hecho podremos cambiar la opción por defecto para que nuestras consultas de Power Query se carguen en el Modelo de datos directamente.

Bastará entrar en las Opciones de la consulta de Power Query en el menú Archivo de ésta:

Y ya en la ventana de Opciones de consulta, en el menú GLOBAL > Carga de datos seleccionaremos Especifique la configuración de carga predeterminada personalizada > Cargar en Modelo de datos

Con este cambio general, las futuras acciones de Cerrar y cargar llevará el resultado de nuestras consultas al Modelo de datos.. tal como queríamos.

Otra posibilidad consiste en tener cuidado al ejecutar la acción de Cerrar y cargar y asegurarnos de elegir la opción de Cerrar y cargar en...

En la ventana siguiente de Cargar en elegiremos Crear solo conexión y la casilla de Agregar estos datos al Modelo de datos

Llegando al mismo resultado, i.e., llevando nuestra query resultante al Modelo de datos de Power Pivot.

Seguir leyendo...

Reparto unidades marginal

En las últimas semanas me han llegado dos o tres cuestiones bastantes similares relacionadas con los repartos marginales de cantidades, es decir, en cómo conseguir repartir en tramos diferentes ciertas cantidades.

Supongamos tenemos un contrato de ventas con un cliente donde según el número de unidades totales acumuladas en el tiempo las primeras 600 tienen un precio.
Las siguientes 650 otro precio.
Las siguientes 350 otro y
todas las demás un último precio.

Es decir tenemos cuatro tramos como los siguientes:

Repito las condiciones asociadas a los cuatro tramos:
Tramo I: desde 0 hasta 600
Tramo II: desde 600 hasta 1250
Tramo III: desde 1250 hasta 1600
Tramo IV: desde 1600 hasta 2850
indican que por las 600 primeras unidades vendidas en cualquier momento aplica un precio, por las siguientes 650 unidades vendidas en cualquier momento otro precio, las siguientes 350 un tercer precio y por las demás unidades, desde la 1600 en adelante un cuarto precio.

El trabajo consiste en montar una fórmula que nos permita distribuir linealmente, de manera marginal, sobre las ventas acumuladas en base a las ventas del mes...
Algo confuso, pero si revisamos el ejemplo de la imagen se entenderá mejor.

Disponemos nuestras unidades vendidas en el rango E1:M1.
Mientras que en E2:M2 insertamos el acumulado:
=SUMA($E$1:E1)

En el rango E7:M7 sumamos las unidades repartidas en nuestros cuatro tramos para cada periodo:
=SUMA(E3:E6)

En las celdas en verde (rango E8:M8) añadimos un control para verificar que las unidades repartidas por tramos en el periodo coinciden con las unidades vendidas:
=SI(E1=E7;"ok";"fallo")

De forma similar, para nuestro control en el rango N3:N6 tendremos la suma de la horizontal, es decir, de las unidades repartidas a lo largo de los periodos por cada tramo.
=SUMA(E3:M3)

Y en las celdas en verde de su derecha (rango O3:O6) un condicional que verifique coincida con las unidades máximas de cada tramo:
=SI(D3=N3;"ok";"fallo")

Con los datos y controles distribuidos en nuestro modelo, podemos comenzar a repartir por periodos y tramos...

Para el Tramo I en el rango E3:M3 añadimos:
=SI(MIN(E$1;E$1-E$2+$C3;$D3)<0;0;MIN(E$1;E$1-E$2+$C3;$D3))

Observa como para el rango del Tramo I, las unidades repartidas suman exactamente 600... i.e., las unidades que corresponden a este tramo.

Para el Tramo II en el rango E4:M4 añadimos:
=SI(SI(E$2>$B4;MIN(E$1-E$3;E$1-E$2+$C4;$D4);0)<0;0;SI(E$2>$B4;MIN(E$1-E$3;E$1-E$2+$C4;$D4);0))

Vamos a por el Tramo III en el rango E5:M5:
=SI(SI(E$2>$B5;MIN(E$1-E$3-E$4;E$1-E$2+$C5;$D5);0)<0;0;SI(E$2>$B5;MIN(E$1-E$3-E$4;E$1-E$2+$C5;$D5);0))

Y por fin el último, Tramo IV en el rango E6:M6:
=SI(E$2>$B6;MIN(E$1-E$3-E$4-E5;E$1-E$2+$C6;$D6);0)

Podemos probar a cambiar las unidades de cada mes en E1:M1.... pero si verificamos los distintos controles por periodo o por tramo, comprobaremos que el reparto es correcto, cumpliendo con lo necesario.

Como apunte al ejemplo dado, si nos fijamos en el cuarto periodo, donde disponíamos de 750 unidades a repartir, vemos como:
-van 50 primeras al Tramo I, lo que completarían las 600 uds de ese tramo
-van 650 al TramoII
-y las 50 restantes hasta completar las 750 del periodo acaban en el tercer tramo.

Este es un buen ejercicio para practicar con las funciones condicionales y en qué forma podemos construir pruebas lógicas apoyándose en funciones habituales como MIN.

Seguir leyendo...

Suma acumulada de números pares

Días atrás un lector me preguntaba por la manera de obtener la suma acumulada de los números pares de un rango de importes..

Veremos en el post de hoy algunas formas y trucos de conseguirlo.

En primer lugar para facilitar el trabajo, a nuestro rango de valores le asignamos un nombre definido: 'importes'
importes =Hoja1!$B$3:$B$16

Con el nombre creado veamos una primera posibilidad, muy clásica.
En D3 añadimos la fórmula matricial (validada presionando Ctrl+Mayusc+Enter):
=SUMA(SI(RESIDUO(importes;2)=0;importes))

En D4 también podemos insertar la función:
=SUMAPRODUCTO(--(RESIDUO(importes;2)=0);importes)

En ambos casos se emplea la función RESIDUO para determinar si los importes de estudio son pares, basándose en la regla matemática que nos dice que si el resto de una división de un número al dividirlo por dos es cero, implica que el número es PAR.
Con el rango de 1 y 0, tras multiplicarlos por los mismos importes tendremos la suma acumulada deseada.

Una tercera forma es la que muestro en la celda D5:
=SUMAPRODUCTO((--ES.PAR(importes+0));--(importes))
=SUMAPRODUCTO((--ES.PAR(importes*1));--(importes))

donde para descubrir si el importe es par o no aplicamos la función ES.PAR...
OJO al truco!!. Para obtener un vector de 0 y 1 debemos sumar 0 (o multiplicar por 1) el rango empleado dentro de ES.PAR.. solo así se comportará matricialmente como necesitamos.

Un último y rápido apunte... si queremos contar las veces que aparece un número par en nuestro rango podemos aplicar la siguiente fórmula:
=SUMAPRODUCTO(--(RESIDUO(importes;2)=0);--(importes<>""))

Muy similar en sintaxis a las anteriores... la clave está en multiplicar por:
--(importes<>"")
en lugar de solo por
--(importes)

Obviamente para obtener la suma de impares utilizaríamos la prueba lógica
--(RESIDUO(importes;2)=1)

o bien
(--ES.PAR(importes+0))

Nota general: el doble signo menos delante de los argumentos lógicos transforma los VERDADEROS y FALSOS ne 1 y 0.

Seguir leyendo...

VBA: Añadir controles desde una macro

A partir de la entrada del blog anterior, y siguiendo con la consulta de otro lector, expondré hoy una curiosidad de los formularios (UserForm) en Excel.
Vamos a añadir una serie de controles automáticamente desde la programación (NO manualmente), asociándoles además eventos a estos controles!!!.

Parece simple, pero requiere de altos conocimientos, que vamos a explicar.

La idea es añadir con programación seis TextBox que llamaremos:
'txtName1','txtName2',...,'txtName6'
a los que asociaremos uno eventos _Change.

Por otra parte añadiremos dos CommanButtton
'cmbSumaTotar','cmbCerrar'
a los que asociaremos sendos eventos _Click.

Insisto... todo esto, así como su ubicación dentro del UserForm y otras propiedades de estos controles, lo gestionaremos desde la propia programación...

El aspecto final a conseguir será:

Comenzaremos insertando un módulo de clase que renombraremos como 'ModClase1', y en esta ventana de código definiremos los eventos a emplear:

Public WithEvents txtEventos As MSForms.TextBox
Public WithEvents cmbEventos As MSForms.CommandButton
Public WithEvents cmbCerrar As MSForms.CommandButton
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub txtEventos_Change()
'gestionamos el evento Change asociado a nuestros TextBox
If Not IsNumeric(UserForm1.ActiveControl.Text) Then
    MsgBox "OJO!!, debe ser un número!!", vbCritical
    'vaciamos el textbox  y situamos el foco/cursor en ese textbox
    With UserForm1.ActiveControl
        .Text = ""
        .SetFocus
    End With
End If
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub cmbEventos_click()
'gestionamos el evento Click asociado a nuestro Botón de Suma
Dim ctrl As Control      'crea un objeto tipo Control

suma = 0
'recorre cada control dentro del formulario
For Each ctrl In UserForm1.Controls
    'comprueba si es un TextBox
    If TypeName(ctrl) = "TextBox" Then
        'y que el TextBox se llama "TxtCal..." y tiene dato
        If InStr(1, ctrl.Name, "txtName") > 0 And ctrl.Text <> "" Then
            'en ese caso acumulamos el valor del TextBox
            suma = suma + CDbl(UserForm1.Controls(ctrl.Name).Value)
        End If
    End If
Next ctrl

MsgBox "La suma es: " & suma
End Sub
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub cmbCerrar_click()
'gestinamos el evento Click asociado a nuestro Botón de Salida
Unload UserForm1
End Sub

Una vez creados estos eventos y sus acciones... nos insertaremos un UserForm, y dentro de su ventana de código añadiremos el siguiente código asociado al evento _Initialize

Public txtNombres As MSForms.TextBox        'para los controles TextBox
Public cmbSuma As MSForms.CommandButton     'para el control CommandButton
Public cmbSalir As MSForms.CommandButton    'para el control CommandButton

Dim objMyEventClass() As New ModClase1       'Creamos un objeto gestionado desde un modulo de clase
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub UserForm_Initialize()
'Creamos y añadimos nuestros 3 TextBox
For i = 1 To 6
    'creamos el objeto y le damos nombre
    Set txtNombres = Me.Controls.Add("Forms.TextBox.1", "txtName" & i)
    With txtNombres     'le damos propiedades de localización
        If i <= 3 Then
            .Top = 5
            .Left = 5 + (100 * (i - 1)) + 5
        Else
            .Top = 25
            .Left = 5 + (100 * (i - 1 - 3)) + 5
        End If
        
        .Width = 100
    End With
    'añadimos un evento a cada textbox
    ReDim Preserve objMyEventClass(1 To i)
    Set objMyEventClass(i).txtEventos = txtNombres
Next i
'''''''''''''''''''''''''''''''''''''
'Creamos el botón de registro
Set cmbSuma = Me.Controls.Add("Forms.CommandButton.1", "cmbSumaTotal")
With cmbSuma
    .Top = 50
    .Left = 5
    .Width = 50
    .Height = 25
    .Caption = "Sumar"
End With
'añadimos un evento al botón
Set objMyEventClass(1).cmbEventos = cmbSuma
'''''''''''''''''''''''''''''''''''''
Set cmbSalir = Me.Controls.Add("Forms.CommandButton.1", "cmbCerrar")
With cmbSalir
    .Top = 50
    .Left = 55
    .Width = 50
    .Height = 25
    .Caption = "Salida"
End With
'añadimos un evento al botón
Set objMyEventClass(1).cmbCerrar = cmbSalir
End Sub

Listo. Con esto conseguimos lo deseado... generar automáticamente los controles de nuestro formulario, asignádoles los eventos necesarios para su correcta gestión. Un aspecto clave en este código es la definición como Array de la clase creada: Dim objMyEventClass() As New ModClase1 que luego se utiliza para personalizar y asignar a cada TextBox el evento Change correspondiente. Otro punto relevante es el método .Add que se emplea para añadir los distintos controles... que tiene tres partes: SetControl = object. Add(tipo control, Nombre, Visible) Un trabajo delicado pero muy interesante sin duda...

Seguir leyendo...