VBA: Detectando la resolución de nuestra pantalla

Hoy veremos una forma de descubrir desde Excel cuál es la resolución de nuestra pantalla.
Para ello emplearemos la función API 'GetSystemMetrics'.

De igual modo con la función de VBA 'Shell' ejecutaremos el Panel de Control de Windows desde donde modificar esta resolución de pantalla

Insertamos el siguiente código dentro de un módulo estándar del explorador de proyectos del Editor de VB:

Option Explicit
'Llamamos a la función API 'GetSystemMetrics'
Private Declare Function GetSystemMetrics Lib "user32.dll" (ByVal nIndex As Long) As Long

'damos valor a las constantes de la función API
Const SM_CXSCREEN = 0
Const SM_CYSCREEN = 1
 
Sub MuestraResolucionPantalla()
     
Dim ancho  As Long, alto  As Long
Dim Mensaje As String
Dim Respuesta As VbMsgBoxResult
 
ancho = GetSystemMetrics(SM_CXSCREEN)
alto = GetSystemMetrics(SM_CYSCREEN)

Mensaje = "La resolución actual de pantalla es de " & ancho & " x " & alto & vbCrLf & _
        "Quieres cambiar la resolución de pantalla??"
    
Respuesta = MsgBox(Mensaje, vbYesNo, "Resolución de pantalla!")


If Respuesta = vbYes Then
    'si la respuesta es afirmativa lanzamos un proceso para modificar la resolución de pantalla
    'con la función Shell de VBA
    
    'la siguiente función abre el Panel de Control (de Windows) para poder modificar dicha resolución
    Call Shell("rundll32.exe shell32.dll,Control_RunDLL desk.cpl,,3")
End If
     
End Sub

Al ejecutarla veremos como con el MsgBox recuperamos nuestra resolución.. y si aceptamos (presionamos el botón de Sí) se abrirá: Panel de control\Todos los elementos de Panel de control\Pantalla\Resolución de pantalla

Seguir leyendo...

La Función INDICE y su forma de Referencia.

Aunque no es una función muy empleada (por suerte cada vez más) la función INDICE siempre tiene guardado un as bajo la manga, y siempre nos sorprenderá.
Hoy veremos un ejemplo de la segunda sintaxis de nuestra función INDICE y su forma de referencias:
=INDICE(referencias, núm_fila, [núm_columna], [núm_área])

Veamos el siguiente planteamiento, donde se pretende recuperar un dato similar desde dos origenes diferentes.

haz clic en la imagen

Nuestra fórmula añadida en la celda B6 será:
=INDICE((B10:D19;E10:G19);COINCIDIR(B3;PAIS;0);COINCIDIR(B5;$B$9:$D$9;0);COINCIDIR(B4;{2014;2015};0))

Simplemente seguimos la sintaxis indicada.
Primer argumento: Referencias:= (B10:D19;E10:G19)
Notemos que se ha añadido entre paréntesis!!! (esto es importante recordarlo, en especial si los rangos son no adyacentes).
Segundo argumento: Número de fila:= Se recupera en el ejemplo apoyándonos en la función COINCIDIR:= COINCIDIR(B3;PAIS;0)
Tercer argumento: Número de columna:= igualmente y de forma similar al Número de fila, empleamos COINCIDIR:= COINCIDIR(B5;$B$9:$D$9;0)

Finalmente, cuarto argumento, una vez detectada fila y columna, nos faltaría dirigirnos a una de las dos áreas de nuestro primer argumentos: (B10:D19;E10:G19)
lo que de nuevo hacemos con el socorrido COINCIDIR sobre una constante matricial:
COINCIDIR(B4;{2014;2015};0)

El resultado es el deseado, una búsqueda sobre tres variables.

Seguir leyendo...

Contando Registros Únicos en un Rango de Excel.

Hace ya tiempo subí una explicación sobre un tema relacionado (ver.
En ese post veíamos como realizar conteos de registros únicos sobre un rango.

Hoy ampliaremos la técnica y conseguiremos contar registros únicos con varias condiciones.
Veamos nuestro rango de trabajo:

El asunto es claro, necesitas conocer para cada fecha (01/06/2016 y 02/06/2016) con cuántos clientes únicos hemos tratado.
La función que buscamos, referenciándola a la fecha buscada (celda F3) sería:
=SUMA(SI.ERROR(1/CONTAR.SI.CONJUNTO($B$2:$B$19;$B$2:$B$19;$A$2:$A$19;F3);0))
(ejecutada matricialmente!!! - presionando Ctr+Mayusc+Enter).

Nuestra fórmula en un primer momento realiza un conteo para cada cliente en una misma fecha, este sería su cálculo normal conseguido:

Seguir leyendo...

Parte decimal de un número con Excel

Analizaremos hoy una curiosidad en cuanto a la precisión de Excel al operar con ciertos números decimales.
Se trata de dar solución a un usuario del blog:

[...] necesito que en una celda se puedan poner números con un solo decimal, he logrado poner la formula de validación pero cuando pongo por ej XX,0 me da error en cambio si pongo xx,1 funciona, osea puedo poner cualquier numero menos algún número que sea ,0 o entero[...]

Sin duda los primeros intentos podrían ser empleando las funciones ENTERO, TRUNCAR o incluso RESIDUO (todas válidas en teoría).
Fijémonos en los siguientes ejemplos:
=B2-TRUNCAR(B2;0)
=B2-ENTERO(B2)-1
=RESIDUO(B2;1)-1

A priori estas formas deberían devolvernos la parte decimal, sin problemas... en nuestro ejemplo 0,89
Sin embargo vemos como a partir de la posición decimal 15, aparece un valor!!!, haciendo nuestro intento vano.

Se trata del error conocido como de 'punto flotante'.
En realidad no es un error de Excel; si no que se produce porque el estándar de 'punto flotante' 754 Institute of Electrical and Electronics Engineers (IEEE) requiere que los números se almacenan en formato binario.

Antes de contar soluciones posibles, veremos qué función es la que nos permitirá dar solución directa:
=SI.ERROR(VALOR(EXTRAE(B2;ENCONTRAR(",";B2)+1;LARGO(B2)));0)<10

Básicamente esta función trata el número como un texto, extrayendo la parte a la derecha de la coma, i.e., la parte decimal.
Una vez separada como texto lo convertimos en valor numérico de nuevo, y para dar respuesta a nuestro lector, evaluamos si el número es inferior a 10, lo que nos asegura que la parte decimal tiene un único decimal.

Para completar el ejercicio, seleccionamos la celda B2 a validar, accedemos a la herramienta Validación de datos > Personalizado y en el campo Fórmula añadimos:
=SI.ERROR(VALOR(EXTRAE(B2;ENCONTRAR(",";B2)+1;LARGO(B2)));0)<10 es decir, nuestra fórmula comentada.

Ampliaré el post de hoy con otras posibles soluciones...
Una solución simple es, conociendo la posición 15 de ese 'punto flotante', emplear la función REDONDEAR(VALOR;14).
Por ejemplo:
=REDONDEAR(B2-TRUNCAR(B2;0);14)
=REDONDEAR(B2-ENTERO(B2)-1;14)
=REDONDEAR(RESIDUO(B2;1)-1;14)

Otra solución más elaborada, y menos práctica, es emplear la opción avanzada: Establecer precisión de pantalla, con la que indicamos a Excel que opere con los decimales/datos visibles según formato en ese momento en pantalla !!!
(OJO con esta forma de actuar que podría tener efectos secundarios en nuestras operaciones).

Seguir leyendo...

La función RESIDUO para repartos periódicos.

Es frecuente al construir nuestros modelos financieros tener que 'desplegar' o repartir ciertos gastos/ingresos de manera periódica a lo largo de nuestro horizonte temporal.

Hoy nos apoyaremos en la función RESIDUO para conseguir un reparto periódico de una cantidad:

La fórmula a emplear es bien sencilla, en la celda D· insertamos:
=SI(RESIDUO(MES(C3);$D$2)=0;$D$1;0)
es un sencillo condicional que marca la pauta de donde incorporar el importe de la celda D1.

La condición o prueba lógica es:
RESIDUO(MES(C3);$D$2)=0
que muestra VERDADERO, en nuestro ejemplo, cada mes divisible por 4 (meses abril, agosto y diciembre)... tal como queríamos.

Recordemos que la función RESIDUO devuelve el residuo o resto de la división entre número y divisor (el resultado tiene el mismo signo que divisor); y tiene una sintaxis:

RESIDUO(número, divisor)

La peculiaridad es que el RESTO/RESIDUO será cero cuando el número sea divisible por el divisor... situación que aprovechamos para nuestro reparto.

Seguir leyendo...

Conteo condicionado de elementos filtrados.

Consultaba un lector la manera de realizar un conteo de registros según una condición dada sobre una base de datos filtrada.

[...] La consulta es: ¿Cómo hago para que cuando filtre por un Departamento concreto me indique la cantidad exacta en cada uno de los estados: Alto, Medio o Bajo? [...]

Lo complejo es realizar el conteo únicamente sobre los elementos visibles, i.e., de aquellos que responden a unos criterios definidos.
Veamos la base de datos a analizar:

La idea es clara, el usuario filtrará por el campo 'Departamento', y sobre los elementos filtrados desea conocer el número de registros que corresponden a cada Estado (celdas A3, B3 y C3).

Emplearemos una técnica similar a la expuesta en este post, en el cual se hace uso de la función SUBTOTALES combinado con DESREF.

En concreto en la celda A3 insertamos matricialmente:
=SUMA(SI(SI(SUBTOTALES(103;DESREF($B$5;FILA($B$5:$B$19)-FILA($B$5);;1));SI($B$5:$B$19=A$2;$B$5:$B$19))=A$2;1;0))
con el que conseguimos el número de estados tipo 'Alto' de la base de datos.

Veamos la explicación sobre un ejemplo, en este caso filtramos el campo Departamento por su elemento 'Depto C':

Apreciamos el comportamiento de nuestra función en el rango de celdas F21:H35 en la imagen anterior.

La primera parte de nuestra función
SUBTOTALES(103;DESREF($B$5;FILA($B$5:$B$19)-FILA($B$5);;1))
nos permite averiguar qué filas están filtradas y cuales no.. un cero para las NO visibles y un uno para las visibles...
Fundamental para poder trabajar sobre los registros que nos interesan (ver rango G21:G35 en la imagen).

Si a la fórmula anterior añadimos
SI($B$5:$B$19=B$2;$B$5:$B$19)
como parte de un condicional:
=SI(SUBTOTALES(103;DESREF($B$5;FILA($B$5:$B$19)-FILA($B$5);;1));SI($B$5:$B$19=A$2;$B$5:$B$19))

obtenemos un rango de valores con datos relevantes solo para los registros visibles!; en concreto, un rango con los Estados de esos registros (ver rango H21:H35 en la imagen).

Nuestra fórmula matricial final:
=SUMA(SI(SI(SUBTOTALES(103;DESREF($B$5;FILA($B$5:$B$19)-FILA($B$5);;1));SI($B$5:$B$19=A$2;$B$5:$B$19))=A$2;1;0))
compendia todo lo anterior, añadiendo una SUMA de unos para las coincidencias de Estado 'Alto'.. o lo que es lo mismo, contando el estado 'Alto' de los registros visibles...

Seguir leyendo...

Suma condicionada en Excel empleando el criterio O.

Una de las limitaciones de la función SUMAR.SI.CONJUNTO (a pesar de sus miles de ventajas) es que únicamente trabaja empleando el operador Y.
Hoy veremos un ejemplo de cómo, matricialmente, podemos emplear una función SUAMR.SI.CONJUNTO para acumular datos de una condición con el criterio O.
Partimos de la siguiente Tabla:

haz clic en la imagen

La meta es conseguir acumular los importes cuando la Región sea 'Sur' y además el color sea o bien 'Rojo' o bien 'Verde'.

Una manera indirecta de llegar al dato es aplicar dos funciones SUMAR.SI.CONJUNTO con la condición Región 'Sur' y Color 'Rojo', una (celda H7):
=SUMAR.SI.CONJUNTO(Datos[Importe];Datos[Color];G7;Datos[Región];F7)
y otra con la condición Región 'Sur' y Color 'Verde' (celda H8):
=SUMAR.SI.CONJUNTO(Datos[Importe];Datos[Color];G8;Datos[Región];F8)

finalmente en H9 sumaríamos ambas cantidades, llegando a nuestro objetivo.

Si bien, de una manera más profesional, emplearíamos la matricial (celda I4):
=SUMA(SUMAR.SI.CONJUNTO(Datos[Importe];Datos[Región];F4;Datos[Color];G4:H4))
(recuerda validarla presionando Ctrl+Mayusc+Enter).

Donde vemos cómo el argumento de criterio que corresponde al rango de Color, es el rango de celdas G4:H4 !! (o el que necesitáramos)... al ser un rango de valores, y trabajar de manera matricial, la fórmula detecta y entiende que debe aplicarse para cada una de las celdas de dicho rango.
Acabamos anidando el resultado matricial para cada valor con la función SUMA, asegurándonos el acumulado deseado.

Seguir leyendo...

VBA: Descomponer Nombres y Apellidos compuestos

Muchas veces en las formaciones que imparto me preguntan por la manera de conseguir obtener, a partir de un Nombre completo escrito en una celda, por separado, Nombre y Apellidos...
El problema es siempre el mismo: Los Nombres y Apellidos compuestos

Por suerte existen una serie limitada de combinaciones de palabras que se emplean en los nombres y apellidos compuestos (ordenados en sentido ascendente):
"de", "del", "el", "la", "las", "los", "san", "y"

Estas palabras son muy importantes, ya que nos permitirán identificar los inicios de nuestros apellidos o nombres compuestos.

He generado una función personalizada con VBA, empleando matrices (Arrays) que a partir de un Nombre completo (nombre y dos apellidos) compuesto o no, nos devuelve las tres partes de manera individual, cada una en una celda... ejecutando nuestra UDF matricialmente.

haz clic en la imagen

Para resolver nuestra cuestión en particular, insertamos el siguiente código dentro de un módulo estándar del explorador de proyectos del Editor de VB:

Function NombreApellidos(NombreCompuesto As String)
Dim aApellido() As String, N_Apellido As String

'separamos las partes de los apellidos
aApellido = Split(NombreCompuesto, " ")

'recorremos cada palabra...
For elto = 0 To UBound(aApellido)
    'homogeneizamos todo en minúscula
    Select Case LCase(aApellido(elto))
        Case "de", "del", "el", "la", "las", "los", "san", "y"
            N_Apellido = N_Apellido & aApellido(elto) & " "
        Case Else
            'si es la última parte del apellido
            If elto = UBound(aApellido) Then
                'no añadimos ningún separador al final.
                N_Apellido = N_Apellido & aApellido(elto)
            Else
                'en caso contrario añadimos una barra vertical |
                N_Apellido = N_Apellido & aApellido(elto) & "|"
        End If
    End Select
Next elto

'contamos partes del nombre
Dim nNombreCompleto() As String
Dim NombreFinal() As String
Dim partes As Integer, Apellido As String

'Obtenemos una matriz con las partes descompuestas
nNombreCompleto = Split(N_Apellido, "|")
'conteo de las partes del Nombre y Apellidos descompuesto
'debe ser mínimo 3 partes := Nombre + Apellido1 + Apellido2
'pero podría devolvernos 4 partes o más en caso de Nombre compuesto!
partes = UBound(nNombreCompleto) + 1

'Recomponemos Nombre y Apellidos
ReDim NombreFinal(0 To partes) As String
If partes > 3 Then  'esto nos indica que el Nombre es Compuesto!
    'Juntamos las dos primeras partes como un único elemento
    NombreFinal(0) = nNombreCompleto(0) & " " & nNombreCompleto(1)
    contador = 1
    'el resto los cargamos tal cual...
    For x = 2 To UBound(nNombreCompleto)
        NombreFinal(contador) = nNombreCompleto(x)
        contador = contador + 1
    Next x
Else    'si el Nombre NO es compuesto
    'cargamos uno a uno las partes del nombre
    For x = 0 To UBound(nNombreCompleto)
        NombreFinal(contador) = nNombreCompleto(x)
        contador = contador + 1
    Next x
End If
'devolvemos el nombre completo a la funcion
'OJO!! EJECUTARLA MATRICIALMENTE PARA OBTENER EN CADA CELDA EL DATO CORRESPONDIENTE!!
NombreApellidos = NombreFinal
End Function

El resultado se puede ver en la imagen anterior... ejecutando nuestra función matricialmente, conseguimos nuestro objetivo...

Seguir leyendo...