BDEXTRAER.. otra forma de buscar información.

Hoy explicaré el uso de la función de base de datos BDEXTRAER, poco conocida, pero sin duda alguna de las más potentes en cuanto a búsqueda que nos ofrece Excel.

Como todas las funciones BD necesita un rango auxiliar donde incorporar los criterios o condiciones.
Con una sintáxis simple (e igual a todas las demás funciones BD):
=BDEXTRAER(base_datos; campo_de_donde_extraer_info; rango_criterios)

Muy importante es que debemos verificar, antes de usarla, ciertas condiciones en nuestro origen de datos:
1- OJO, por que la función extrae un único valor de nuestra base de datos cuando cumpla las condiciones especificadas.
2- Fundamental que la distribución de los datos en el origen nos aseguren, por tanto, un único valor posible como respuesta.

Notas:
+ Si ningún registro cumple los criterios, BDEXTRAER devuelve el valor de error #¡VALOR!.
+ Si más de un registro coincide con los criterios, BDEXTRAER devuelve el valor de error #¡NUM!.


Pongamos un primer ejemplo, sobre el origen de la imagen planteamos la siguiente búsqueda múltiple:

Nuestra fórmula:
=BDEXTRAER(A1:C7;"comercial";E1:F2)
nos devuelve el 'Comercial' que coincida de manera única con los criterios de 'Zona' y 'Producto'.
Notemos la importancia de la construcción del Rango de criterios, con exactitud máxima a la hora escribir los encabezados...

Otro ejemplo, sobre una base de datos distinta...

En este ejemplo, a modo de comparación, expongo tres formas de realizar esta búsqueda múltiple.
En la celda I3 tomando como rango de criterios las celdas de su izquierda, montamos la función de base de datos:
=BDEXTRAER(A1:D10;"Precio";F2:H3)

En la celda I7 empleamos la función SUMAR.SI.CONJUNTO apoyándonos en las celdas G7 y H7 como criterios de la suma condicionada:
=SUMAR.SI.CONJUNTO(D2:D10;A2:A10;H7;B2:B10;"<="&G7;C2:C10;">="&G7)

Finalmente en la celda I11 muestro el uso de una función matricial (recuerda validarla presionando Ctrl+mayusc+Enter):
=SUMA(SI(A2:A10=H11;SI(B2:B10<=G11;SI(C2:C10>=G11;D2:D10))))

En todos los casos, es imprescindible que se verifiquen las condiciones de partida comentadas, esto es, que la combinación de criterios de un resultado único!!

Seguir leyendo...

Excel y el reparto de escaños.. una aplicación del sistema D'Hondt.

Ayer día 20 de diciembre de 2015 se celebraron Elecciones a Cortes Generales y como siempre escuchamos el nombre de un tal 'Victor d'Hondt' para explicar la atribución o reparto de escaños en el parlamento.
Trataré de exponer aquí cuál es el modelo de reparto de acuerdo a la Ley electoral española (basada en el sistema d'Hondt).

Lo importante, al respecto, es el artículo 163 de la Ley Orgánica 5/1985, de 19 de junio, que dice:

1. La atribución de los escaños en función de los resultados del escrutinio se realiza conforme a las siguientes reglas: a) No se tienen en cuenta aquellas candidaturas que no hubieran obtenido, al menos, el 3 por 100 de los votos válidos emitidos en la circunscripción. b) Se ordenan de mayor a menor, en una columna, las cifras de votos obtenidos por las restantes candidaturas. c) Se divide el número de votos obtenidos por cada candidatura por 1, 2, 3, etc., hasta un número igual al de escaños correspondientes a la circunscripción, formándose un cuadro similar al que aparece en el ejemplo práctico. Los escaños se atribuyen a las candidaturas que obtengan los cocientes mayores en el cuadro, atendiendo a un orden decreciente. Ejemplo práctico: 480.000 votos válidos emitidos en una circunscripción que elija ocho Diputados. Votación repartida entre seis candidaturas: A(168.000 votos) B(104.000) C(72.000) D(64.000) E(40.000) F(32.000) División 1 2 3 4 5 6 7 8 A 168.000 84.000 56.000 42.000 33.600 28.000 24.000 21.000 B 104.000 52.000 34.666 26.000 20.800 17.333 14.857 13.000 C 72.000 36.000 24.000 18.000 14.400 12.000 10.285 9.000 D 64.000 32.000 21.333 16.000 12.800 10.666 9.142 8.000 E 40.000 20.000 13.333 10.000 8.000 6.666 5.714 5.000 F 32.000 16.000 10.666 8.000 6.400 5.333 4.571 4.000 Por consiguiente: la candidatura A obtiene cuatro escaños. La candidatura B dos escaños y las candidaturas C y D un escaño cada una. d) Cuando en la relación de cocientes coincidan dos correspondientes a distintas candidaturas, el escaño se atribuirá a la que mayor número total de votos hubiese obtenido. Si hubiera dos candidaturas con igual número total de votos, el primer empate se resolverá por sorteo y los sucesivos de forma alternativa. e) Los escaños correspondientes a cada candidatura se adjudican a los candidatos incluidos en ella, por el orden de colocación en que aparezcan.

La verdad es que el sistema está muy claramente explicado en el artículo anterior, y sin entrar en justicias-injusticias o distorsiones de éste, veremos cómo nos puede ayudar Excel a obtener el dato buscado: Reparto del número de escaños de una circunscripción.

Si te interesa puedes leer algo más del sistema d'Hondt aquí

Veamos un caso real según los datos de mi circunscripción electoral: Madrid.
Los datos de votos han sido (Fuente: Ministerio del Interior)

Como vemos el número de escaños a repartir es de 36. Por otro lado el número de partidos que verifican el artículo 163.1.a de la ley electoral (Ley Orgánica 5/1985, de 19 de junio) y superan el 3% de los votos se reduce a 5 partidos.
Por tanto, según la norma de reparto, siguiendo el modelo descrito en ese mismo artículo, montaremos una Tabla de referencia cruzada, tal como vemos en la imagen siguiente:

la fórmula de reparto en cada columna, para cada partido, consiste en dividir el número de votos obtenido entre el número de escaño que aparece en la columna E (en la imagen).

Como primera forma de ver el reparto podemos aplicar un Formato condicional sobre el rango de cálculos F3:J38, aplicando la regla condicional de los 36 valores superiores:

Observamos cómo quedan remarcados en 'verde' el número de escaños conseguido pro cada partido.

Otra forma de llegar al número de escaños consistiría en emplear las funciones K.ESIMO.MAYOR y COINCIDIR.

Calculamos y obtenemos en la celda L3 el valor que ocupa, dentro del rango de valores de nuestra matriz, la posición 36:
=K.ESIMO.MAYOR(F3:J38;36)
este será nuestro punto de referencia, ya que los cocientes obtenidos por encima de este valor serán los que nos interesen para descubrir el número de escaños de cada partido.

En las celdas N3:R3 aplicamos la función COINCIDIR, pero utilizando el tercer argumento 'Mayor que (-1)' sobre cada uno de los rangos de cada partido:
=COINCIDIR($L$3;F3:F38;-1)
con este argumento forzamos a COINCIDIR a encontrar el menor valor que es mayor o igual que el valor_buscado.
OJO, ya que los valores del argumento matriz_buscada deben estar en orden descendente... como en nuestro caso.

Con esta fórmula obtenemos la posición del valor/cociente buscado, y por ende, del número de escaños correspondiente.

Seguir leyendo...

La función M.C.M para convertir un número decimal en fracción.

Hace pocos días un lector planteaba la siguiente cuestión:

...como calcula Excel el formato ???/??? de manera que al escribir un número decimal, por ejemplo 52,8351 nos muestra 3540/67? es decir, cómo calcula a partir de un cociente, el dividendo y el divisor.

Me pareció interesante, ya que nunca me había preguntado cuál era tal mecanismo.. y cómo era posible que bastase aplicar un simple formato para forzar un cálculo a priori tan complejo.

El asunto es un tema sencillo matemático.
Pongamos un ejemplo corto: ¿qué fracción representa el valor decimal 0,75?
Si aplicamos el formato personalizado tipo ???/??? veremos rápidamente cuál es esa fracción: 3/4:

Muy potente, sin duda, ya que devuelve la fracción simplificada al máximo!!!

¿Pero qué calculo realiza de manera interna Excel para ser capaz de llegar a esta fracción??.
La respuesta la obtenemos empleando la función M.C.M (mínimo común múltiplo).

De que valores?.
1. del valor decimal multiplicado por 10 tantas veces sea necesario para que finalmente aparezca como un entero.
Por ejemplo, 0,75 lo multiplicamos por 10 x 10, hasta obtener 75
0,625 lo multiplicamos por 10 x 10 x 10 hasta llegar a 625, etc.
2. Un segundo valor será ese valor por el que multiplicamos nuestro decimal.
Por ejemplo, para 0.75 el segundo valor sería 100, i.e., 10 x 10
para 0,625 el valor será 1000, i.e., 10 x 10 x 10

Si aplicamos la función M.C.M sobre los valores 75 y 100 obtendremos:
=M.C.M(75;100) = 300

Lo vemos en la imagen:

Para conseguir el dato de nuestro Numerador dividiremos (ver celda E11 en la imagen) el MCM entre el segundo valor (en el ejemplo el 100):
=300/100 = 3
Para conseguir el dato de nuestro Denominador dividiremos (ver celda E12 en la imagen) el MCM entre el primer valor (en el ejemplo el 75):
=300/75 = 4

Así, Numerador/Denominador = 3/4 que corresponde al decimal de partida 0,75...

Una imagen de otro ejemplo, para el decimal 0,715 convertido en fracción como 143/200, aplicando el mismo cálculo:

Seguir leyendo...

VBA: Enum - enumeración de constantes y el cálculo de las principales festividades durante y después de la Pascua.

Al hilo de la entrada anterior (ver), mostraremos cómo calcular las restantes de festividades religiosas relevantes y variables según la Semana Santa.
Tomados los datos de la web www.divvol.org

Como aplicación especial haremos uso de la instrucción Enum para la enumeración y creación de diferentes constantes...
El sentido de esta instrucción es facilitar nuestro trabajo cuando necesitamos emplear bastantes constantes en nuestros procedimientos, principalmente por que se nos permite emplearlo como si fueran objetos, esto es, el tipo definido seguido del punto y luego del nombre de la constante...
Lo que sin duda evitará errores en la sintaxis y uso de dichas constantes.

Un ejemplo sencillo, si vamos a trabajar con tres constantes, podemos escribir:
Public Const rojo = 1
Public Const verde = 2
Public Const azul = 3

pero sería más práctico y seguro trabajar con la lista enumerada:
Public Enum colores
rojo = 1
verde = 2
azul = 3
End Enum

A lo largo de nuestro procedimiento, para recuperar el valor de la constante, podríamos emplear la forma:
colores.rojo

Para resolver nuestra cuestión en particular, crearemos una función personalizada en VBA para tal fin, insertando nuestro código dentro de un módulo estándar desde el editor de VB:

Public Enum dias
    DomPascua = 0
    MiéCeniza = -46
    DomRamos = -7
    Ascensión = 40
    DomAscensión = 43
    Pentecostés = 50
    SantísimaTrinidad = 57
    CorpusChristi = 61
    DomCorpusChristi = 64
End Enum

Function FechaSemanaSanta(año As Integer, FechaSanta As String) As Date
'otros días variables durante y después de la Pascua
'http://www.divvol.org/recursos/fecha_pascua.htm
Dim Ndias As dias

Application.Volatile

Select Case FechaSanta
    Case Is = "DomPascua": Ndias = dias.DomPascua
    Case Is = "MiéCeniza": Ndias = dias.MiéCeniza
    Case Is = "DomRamos": Ndias = dias.DomRamos
    Case Is = "Ascensión": Ndias = dias.Ascensión
    Case Is = "DomAscensión": Ndias = dias.DomAscensión
    Case Is = "Pentescostés": Ndias = dias.Pentecostés
    Case Is = "SantísimaTrinidad": Ndias = dias.SantísimaTrinidad
    Case Is = "CorpusChristi": Ndias = dias.CorpusChristi
End Select
FechaSemanaSanta = DomingoPascua(año) + Ndias

End Function

Como podemos ver en la imagen obtenemos en base a las distintas constantes los días concretos para cada año informado:

haz clic en la imagen

Seguir leyendo...

Computus: calculando la Semana Santa.

Cada año vemos (y leemos) cómo los días de Semana Santa varían y nunca terminos de comprender exactamente el motivo... bastante lioso, por cierto, al mezclar el calendario lunar con el solar y ciertas equivocaciones pasadas.

Hoy me levanté decidido a buscar la respuesta exacta a este 'misterio' y me fui documentando.
Vistas diferentes webs me quedé con esta explicación de la wikipedia.

La idea es mostrar una fórmula que nos devuelva el día correspondiente del Domingo de Pascua (o Resurrección) a partir de un año dado.

Desplegaremos algunos años para comprobar la fórmula:

Buceando encontré estas dos fórmulas para obtener la fecha del Domingo de Pascua a partir del año
1- en C2:
=MONEDA(("4/"&A2)/7+RESIDUO(19*RESIDUO(A2;19)-7;30)*14%;)*7-6
y en B2 para obtener el Jueves Santo:
=MONEDA(("4/"&A2)/7+RESIDUO(19*RESIDUO(A2;19)-7;30)*14%;)*7-6-3

2 - en C10 otra manera:
=MULTIPLO.INFERIOR(FECHA(A10;5;DIA(MINUTO(A10/38)/2+56));7)-34
y en B10:
=MULTIPLO.INFERIOR(FECHA(A10;5;DIA(MINUTO(A10/38)/2+56));7)-34-3

Otra vía es creando una función personalizada en VBA que replique el Algoritmo de Butcher, para tal fin, insertamos nuestro código dentro de un módulo estándar desde el editor de VB:

Function DomingoPascua(Año As Integer) As Date
'desarrollo del Algoritmo de Butcher
'válido para cualquier año posterior a 1583,
'sólo es válido para el calendario Gregoriano
Dim a As Integer, b As Integer, c As Integer, d As Integer, e As Integer
Dim f As Integer, g As Integer, h As Integer, i As Integer
Dim k As Integer, l As Integer, m As Integer, n As Integer
Dim mes As Integer, día As Integer
'A resto de la división año / 19,
a = Año Mod 19
'B cociente de la división año / 100, es decir, (año - (año mod 100)) / 100 = un número entero,
b = Int(Año / 100)
'C resto de la división año / 100,
c = Año Mod 100
'D cociente de la división B / 4,
d = Int(b / 4)
'E resto de la división B / 4,
e = b Mod 4
'F cociente de la división (B + 8) / 25,
f = Int((b + 8) / 25)
'G cociente de la división (B - F + 1) / 3,
g = Int((b - f + 1) / 3)
'H resto de la división (19A + B – D – G + 15) / 30,
h = (19 * a + b - d - g + 15) Mod 30
'I cociente de la división C / 4,
i = Int(c / 4)
'K resto de la división C / 4,
k = c Mod 4
'L resto de la división (32 + 2E + 2I – H – K) / 7,
l = (32 + 2 * e + 2 * i - h - k) Mod 7
'M cociente de la división (A + 11H + 22L) / 451,
m = Int((a + 11 * h + 22 * l) / 451)
'N = H + L – 7M + 114,
n = h + l - 7 * m + 114

'MES cociente de la división N / 31,
mes = Int(n / 31)
'DÍA = 1 + ( N mod 31 ) o bien 1 + (N - (MES × 31)).
día = 1 + n Mod 31

'Obtenemos el día...
DomingoPascua = DateSerial(Año, mes, día)
   
End Function

Como se puede comprobar en la imagen más arriba, en el rango E2:E7 el resultado es el mismo...

Seguir leyendo...

VBA: Una función para extraer caracteres de texto de un valor alfanumérico.

Un caso frecuente es querer trabajar únicamente con los caracteres de texto dentro de una cadena alfanumérica.
Por este motivo hoy veremos una sencilla UDF en VBA para Excel que nos permitirá extraer las letras de nuestra celda, dejando al lado los valores numéricos.

Hola tengo una duda existe formula para separar letras de números cuando estas están en una celda como por ejemplo "100:129-145S" y q solo me salga "S" pero la siguiente celda q la sigue es "100:129-13XL" y como hacer q me salga "XL". Ya que use una formula =EXTRAE(B4;HALLAR("-";B4;1)+1;1) Pero solo me funciona para la primera celda y no para la segunda.

En el caso propuesto por el lector tenemos un pequeño 'handicap' añadido, y es que contamos con dos caracteres tipo texto(: y -) que no queremos extraer, y tendremos que evitar...

Insertamos nuestro simple procedimiento Function en un módulo estándar de nuestro proyecto de VBA desde el editor de VB:

Function ExtraeTexto(celda As Range) As String
Dim txt As String

'recorremos cada caracter
For i = 1 To Len(celda.Value)
    'evaluamos si NO es numérico
    If Not IsNumeric(Mid(celda.Value, i, 1)) And _
        Mid(celda.Value, i, 1) <> ":" And _
        Mid(celda.Value, i, 1) <> "-" Then
        'en caso afirmativo (NO numérico) concatenamos con resultados anteriores...
        txt = txt & Mid(celda.Value, i, 1)
    End If
Next
'finalmente devolvemos los caracteres textuales...
ExtraeTexto = txt
End Function

Adicionalmente a la solución propuesta podemos obtener lo mismo empleando la función SUSTITUIR anidada sobre sí misma tantas veces como la necesitemos.
En un primer paso 'eliminaremos los caracteres de texto no deseados (: y -), en la celda E2:E3:
=SUSTITUIR(SUSTITUIR(A2;":";"");"-";"")

Para luego construir una función algo larga con 10 funciones SUSTITUIR anidadas entres sí (una por cada número 0-9), aplicada sobre las celdas anteriores E2 y E3:
=SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(E2;0;"");1;"");2;"");3;"");4;"");5;"");6;"");7;"");8;"");9;"")
Larga pero eficiente ;-)

Una segunda alternativa más técnica de nuestra función personalizada 'ExtraeTexto' vendría del uso del operador Like:

Function ExtraeTexto2(celda As Range) As String
Dim txt As String

'recorremos cada caracter
For i = 1 To Len(celda.Value)
    'evaluamos si está entre A y Z
    If Mid(celda.Value, i, 1) Like "[A-Z]" Then
        'en caso afirmativo concatenamos con resultados anteriores...
        txt = txt & Mid(celda.Value, i, 1)
    End If
Next
'finalmente devolvemos los caracteres textuales...
ExtraeTexto2 = txt
End Function

Fijémonos en la comparativa realizada:
Like "[A-Z]"
y como al probar la función deja automáticamente fuera los caracteres no deseados : y -

Es importante saber que por defecto está predefinido como método de comparación de cadenas la instrucción: Option Compare Binary; esto significa para nuestro ejemplo que nuestro intervalo de letras [A-Z] entiende y compara con A, B, C, D .. Z.

Para una coincidencia más concreta deberíamos haber empleado y definido al inicio de nuestro módulo la opción:
Option Compare Text, donde el intervalo [A-Z] buscaría coincidencias entre con A, a, À, à, B, b, C, c, D, d, E,e, etcétera.

Seguir leyendo...

VBA: Una suma acumulado con doble criterio según colores de fuente y fondo.

Hace un par de entradas en el blog escribía y explicaba la forma de obtener una suma o conteo acumulado según condiciones de color de relleno o de fuente en un rango de celdas...
Sobre esta cuestión se planteaba una segunda variante, ¿cómo conseguir sumar valores de un rango con una doble condición sobre rangos colocados en cualquier situación aplicando criterios de color sobre Fuente y Fondo-Relleno??
Este sería nuestro planteamiento:

Para tal fin, insertamos nuestro código dentro de un módulo estándar desde el editor de VB:

Function FxSumaColor(rngSuma As Range, _
    rngFondo As Range, CeldaFondo As Range, _
    rngFuente As Range, CeldaFuente As Range) As Variant

Dim ColorFuente As Long, ColorFondo As Long
Dim Resultado As Double, ColorCeldaFuente As Long, ColorCeldaFondo As Long
'rngSuma: será el rango donde se hallen los valores a sumar
'rngFondo: será el rango de estudio donde comparar el fondo de celda
'CeldaFondo: la celda donde compararemos el color de relleno/fondo
'rngFuente: será el rango de estudio donde comparar la fuente de celda
'CeldaFuente: la celda donde compararemos el color de la fuente

Application.Volatile
'Primero comprobamos que el número de celdas en cada rango
'rngSuma, rngFondo y rngFuente sea el mismo!!
If rngSuma.Cells.Count <> rngFondo.Cells.Count Or _
    rngSuma.Cells.Count <> rngFuente.Cells.Count Or _
    rngFuente.Cells.Count <> rngFondo.Cells.Count Then
        FxSumaColor = "verifica número de celdas en los rangos!!"
        Exit Function
End If

'Identificamos el número de celdas..
Dim num As Long
num = rngSuma.Cells.Count

For i = 1 To num
'Asignamos colores a las variables o condiciones de color de fuente y/o fondo
'Para el color del Fondo
ColorFondo = CeldaFondo.Interior.ColorIndex
ColorCeldaFondo = rngFondo.Item(i).Interior.ColorIndex
'Para el color de la Fuente
ColorFuente = CeldaFuente.Font.ColorIndex
ColorCeldaFuente = rngFuente.Item(i).Font.ColorIndex
    'verificamos la doble coincidencia sobre ambos rangos de estudio...
    If ColorCeldaFondo = ColorFondo And ColorCeldaFuente = ColorFuente Then
        'acumulamos valores sumados cuando coincida con color
        Resultado = WorksheetFunction.Sum(rngSuma.Item(i).Value, Resultado)
    End If
Next i
'devolvemos el valor a la función
FxSumaColor = Resultado
End Function

La base de este planteamiento es el uso de la propiedad .Item asociado al objeto Range, que nos permite identificar cada una de las celdas de los distintos rangos en el orden adecuado para posteriormente evaluar las doble coincidencia de color de fuente y fondo...

Como se observa en la imagen anterior el resultado es correcto...

Seguir leyendo...

Búsqueda aleatoria condicionada.

Planteaba un lector del blog la posibilidad de obtener un número de un a lista, de manera aleatoria, según una condición dada sobre un campo de nuestra tabla.

haz clic en la imagen

La idea es por tanto, según un Color elegido, obtener un valor aleatorio de alguno de los tres tramos (uno por día) que corresponda con ese color.
En el ejemplo tenemos tres tramos / tres días, y dentro de cada día cinco colores y un valor diferentes para cada día y color.
A partir de un color deseamos conseguir uno de los tres importes asociados a dicho color, de manera aleatoria.

Comenzamos indicando en una celda un color, por ejemplo en I4 escribimos 'amarillo' y en la celda de al lado, en J4 la siguiente fórmula matricial:
=BUSCARV(I4;DESREF(INDIRECTO("b"&K.ESIMO.MENOR({2;7;12};ALEATORIO.ENTRE(1;3)));0;0;5;2);2;0)

El resultado obtenido en J4 es un valor aleatorio del campo 'Cantidad', pero sólo de entre los 'Colores' amarillo, tal y como perseguíamos.

La clave o claves de esta fórmula es por un lado nuestra función ALEATORIO.ENTRE que incorpora el componente aleatorio, y nos dirige a uno de los tres tramos:
ALEATORIO.ENTRE(1;3)

Por otra parte con la función K.ESIMO.MENOR({2;7;12};ALEATORIO.ENTRE(1;3)) obtenemos un número de entre los indicados en la constante matricial {2;7;12}, que representa la fila de inicio de cada tramo por fecha.
Esta fila inicial es importante ya que a partir de ella construiremos un rango de cinco filas x una columna; esto lo conseguimos con la función DESREF en su forma matricial:
DESREF(INDIRECTO("b"&K.ESIMO.MENOR({2;7;12};ALEATORIO.ENTRE(1;3)));0;0;5;2)
me he apoyado en la función INDIRECTO para señalar el ancla.

El resto es sencillo, ya que sobre esa matriz de búsqueda, asociada aleatoriamente al rango o tramo por fecha, forzamos una BUSCARV para retornar la Cantidad asociada al color indicado en I4:
=BUSCARV(I4;DESREF(INDIRECTO("b"&K.ESIMO.MENOR({2;7;12};ALEATORIO.ENTRE(1;3)));0;0;5;2);2;0)

Seguir leyendo...