sábado, 6 de febrero de 2016

BUSCARV acumulado

Puede descargar el archivo de Excel: TotalBuscarV.xlsm

La función BUSCARV realiza una búsqueda vertical en una tabla. Puede ver una entrada donde se habla de este tema clásico en Excel.

Ahora lo que deseamos es buscar varios valores y crear una función que nos de la suma de esas celdas buscadas.

Disponemos de una tabla con productos, e importes por meses. Queremos diseñar una función similar a BuscarV (Vlookup) que proporcione el acumulado. Deseamos que nos de el importe acumulado de los meses, hasta el mes indicado inclusive. La función así creada se llama BuscarVV. Si deseamos acumular entre dos meses, el de inicio y el de final, incluidos ambos, lo que haremos es restar una BuscarVV de otra. Ver ejemplo.


Los valores de la tabla se generan con números aleatorios, al igual que las celdas de color naranja donde se elijen los siguientes valores:

  • Celda Q9. Se establece el producto entre 1 y 20
  • Celda Q11: Mes de inicio
  • Celda Q12: Mes de final
Deseamos acumular los valores correspondientes al Producto seleccionados entre los meses de inicio y final ambos inclusive. Puesto que estos valores se eligen de forma aleatoria, al pulsar la tecla de función F9 se produce un recálculo manual que hace que los valores cambien.

Mediante formato condicional seleccionamos con fondo verde las celdas de la tabla que deseamos acumular. La fórmula que podemos ver en el formato condicional sobre la celda C10 es el siguiente.

=Y(VALOR(+DERECHA(C$9;2))<=$Q$12;VALOR(+DERECHA(C$9;2))>=$Q$11;$B10=$Q$10)

Disponemos de tres métodos para obtener el acumulado.

Método 1

El la celda Q13 figura la siguiente expresión.

=buscarvv(Q10;tabla;Q12+1)-buscarvv(Q10;tabla;Q11)

La función BuscarVV es una función que hemos programado usando Macros de Excel. Se trata de una función (function) que es la siguiente.



Function BuscarVV(Valor, Tabla, Hasta_Columna, Optional exacto)
    Dim i As Byte
    Dim Total
    For i = 2 To Hasta_Columna
        Total = Total + WorksheetFunction.VLookup(Valor, Tabla, i, exacto)
    Next i
    BuscarVV = Total
End Function

La función lo que hace es llamar a la función BUSCARV que en inglés es VLOOKUP. La llamada se efectúa usando la expresión:

WorksheetFunction.Función en ingles(parámetro1,parámentro2,...)

La función BuscarVV lo que hace es sumar los valores del Producto indicado, del rango Tabla, hasta la columna indicada).

El nombre de rango Tabla se corresponde con el rango B10:N29.

Método 2

El la celda Q14 figura la siguiente expresión.

=SUMA(INDIRECTO("F"&COINCIDIR(Q10;B10:B29;0)+9&"C"&Q11+2&":F"&COINCIDIR(Q10;B10:B29;0)+9&"C"&Q12+2;0);0)

Esta fórmula no utiliza macros y se basa en las funciones INDIRECTO y COINCIDIR.

Método 3

El la celda Q15 figura la siguiente expresión.

=SUMA(DESREF(B9;COINCIDIR(Q10;B10:B29;0);Q11;1;Q12-Q11+1))

Esta fórmula no utiliza macros y se basa en las funciones DESREF y COINCIDIR.




viernes, 5 de febrero de 2016

Adivinar el número secreto

Puede descargar el archivo de Excel: adivina.xlsm


Vamos a programar un juego sencillo con una Macro de Excel para ilustrar los bucles Do ... Loop. Puede ver la información sobre estos bucles en el siguiente post.

Juego de adivinar el número secreto



Se trata de un juego donde el ordenador piensa un número secreto entre 1 y 100, sin decimales, y nosotros tenemos que adivinar qué número ha pensado.


El ordenador nos pide que introduzcamos por teclado un número y luego nos informa con una de las siguientes posibilidades:
  • El número el menor
  • El número el mayor
  • Felicidades, ha adivinado el número secreto
  • Si superamos los 10 intentos nos avisa de ello y nos muestra el número secreto. 

Veamos la macro que hemos utilizado.


Sub adivina()
    Dim zona As String
    Dim x As Byte, n As Byte
    Dim tirada As Byte
    Randomize
    x = Fix(Rnd * 101): tirada = 1   'FIX=INT=parte entera
    Do
        If zona = "" Then
            n = InputBox("Introduzca un número entero del 0 al 100" & vbCrLf _
            & "Dispone de 10 tiradas para lograrlo", "Tirada número " & tirada)
        Else
            n = InputBox("El número secreto es " & zona & vbCrLf & _
            "Introduzca otro", "Tirada número " & tirada)
        End If
        If n = x Then
            MsgBox "Felicidades!!!" & vbCrLf & "Ha adivinado el número secreto " _
            & x & ", en " & tirada & " tiradas"
            Exit Sub
        End If
        If x < n Then
            zona = "Inferior"
        Else
            zona = "Superior"
        End If
        tirada = tirada + 1
    Loop Until tirada > 10
    MsgBox "Ha agotado las 10 tiradas disponibles" & vbCrLf _
    & "El número secreto es " & x
End Sub


Ejercicio propuesto


Modifique el código anterior para emplear alguno de los siguientes tipos de bucles:

  • Do While ... Loop
  • Do ... Loop While

Otra pregunta: ¿Se podría emplear un bucle Do ... Loop sin usar ningún While, ni usar Until?

TIR calculada con Macro

Dispone del un archivo de Excel que puede descargar: tirmacro.xlsm

TIR es la Tasa Interna de Retorno. Se define como la tasa que hace el VAN igual a cero, y se interpreta como la rentabilidad interna de la operación. En inglés IRR (Internal Rate of Return). En este blog ya hemos comentado ampliamente este concepto y sus fórmulas de cálculo: TIR, TIR.NO.PER, TIRM. Ahora se trata de comprobar que efectivamente el cálculo de la TIR que hace Excel es correcto. Para ello, vamos a crear una macro muy didáctica que nos permitirá ver, paso a paso, cómo converge el valor de la TIR a la tasa que hace el VAN igual a cero.



Este algoritmo es el típico que se utiliza para calcular las raices de un polinomio, puesto que las raices son los puntos de corte con el eje horizontal. El procedimiento es el siguiente:

  1. Se toma un valor por defecto para la estimación de la TIR. Este valor lo denominaremos Inferior e inicialmente será cero.
  2. Se toma un valor por exceso para la estimación de la TIR. Este valor lo denominaremos Superior e inicialmente será 1, que en tanto por ciento equivale al 100%.
  3. Con estos dos valores así elegidos calculamos el VAN para cada uno de ellos. El VAN a tipo cero se supone que ha de ser positivo, y al tipo Superior del 100% se supone que el VAN ha de ser negativo. Si esto no se cumpliera se ha de elegir otro valor aún mayor al 100% hasta que se cumpla.
  4. Entre el valor Inferior y el Superior calculamos otro valor denominado Medio. El valor Medio es la media entre el Inferior y el Superior. Medio=(Inferior+Superior)/2. Y calculamos el VAN correspondiente a este tipo.
  5. Si el VAN al tipo Medio es POSITIVO, entonces el nuevo tipo Inferior será igual al tipo Medio actual.
  6. Si el VAN al tipo Medio es NEGATIVO, entonces el nuevo tipo Superior será igual al tipo Medio actual.
  7. Repetir este análisis (ir al punto 4) mientras se cumpla que el VAN al tipo Medio difiera en valor absoluto de cero en menos de un cierto error admitido.
Hemos creado dos botones que lanzan la macro, ya que si no se relentiza un poco la ejecución, es tan rápida que no se aprecian los cambios que se producen en las celdas.