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.


sábado, 5 de diciembre de 2015

Obtener datos externos desde Web

Puede descargar el archivo de Excel siguiente.



En Excel podemos importar datos e información publicada en páginas web utilizando una de las opciones del menú DATOS, denominada Obtener datos Externos desde Web.


Al pulsar sobre esta opción descubriremos que dentro de Excel se encuentra un sencillo navegador Web que nos permitirá ir a la página deseada y obtener de ella la información que necesitamos.

Es más que copiar y pegar, se trata de vincular con los datos de la página web seleccionada y así al pedir a Excel que actualice el vínculo tendremos los datos nuevos, siempre actualizados.

Hoja1

Vamos a importar los datos del IBEX que se encuentran en las páginas del periódico económico Expansión.

En el navegador de Excel ponemos la dirección web de la página del periódico que deseamos importar. La URLes:


Observamos unas marcas de un cuadrado amarillo con unas flechas. Estas marcas nos indican las zonas de la página web que podemos importar. En ocasiones es toda la página, otras veces nos deja importar una zona concreta, por ejemplo una tabla de datos.


En nuestro caso deseamos importar la tabla con las cotizaciones de las empresas del IBEX. Al acercar el cursor a la marca amarilla cambia el color y se torna en verde, marcando con un recuadro azul la zona que se podría importar.


 Al hacer clic con el ratón sobre la marca desaparece la flecha y queda en verde con un símbolo que indica que está seleccionada.


Al pulsar sobre el botón Importar aparece una ventana donde podemos indicar la celda sobre la que importaremos.


Pasan unos segundos y la tabla seleccionada quedará importada a nuestra hoja de cálculo.


Para actualizar debemos pulsar con el botón derecho del ratón sobre la tabla y en el menú contextual elegir la opción denominada "Actualizar". De esta forma si la tabla de cotizaciones bursátiles se actualizara con gran frecuencia y estuviéramos en horario de cotización veríamos como la tabla de nuestra hoja de cálculo se modifica refrescándose con los nuevos datos. Por eso decimos que es más que copiar y pegar. Lo que hace es un vínculo con la página web que hemos seleccionado y se refresca la información cada vez que pedimos que se actualice la información.


Los datos del ejemplo anterior se corresponden con el cierre de la bolsa española a fecha del viernes 4 de diciembre de 2015.

Hoja2


En la segunda hoja hemos importado la tabla del IBEX de la página de Invertia.



Cuando nos sale la ventana en la que podemos indicar la celda en la que deseamos que quede la tabla importada aparece también un botón de propiedades. Al pulsar sobre las propiedades veremos una ventana donde entre otras cosas podemos programar que la tabla importada se actualice cada 60 minutos o el tiempo que establezcamos.


Otra opción interesante consiste en pedir que se actualice la tabla al abrir el documento.

martes, 24 de noviembre de 2015

Promedio condicional

Puede descargar el archivo de Excel siguiente.
Disponemos en Excel de las funciones siguientes.
  • SUMAR.SI
  • SUMAR.SI.CONJUNTO
  • CONTAR.SI
  • CONTAR.SI.CONJUNTO
Pero no disponemos de la función PROMEDIO.SI

Vamos a ver un caso donde realizamos un promedio condicional donde varía el rango que deseamos promediar y además eliminamos los valores que no son numéricos. Lo vamos a resolver por tres métodos.



Deseamos calcular el promedio anual del Euribor a un año. La información la obtenemos del Banco de España.

En el punto 1.7 disponemos de un histórico de Series Temporales, concretamente en el siguiente enlace podremos descargar el archivo csv.

Método 1

Consiste en crear cada rango de forma manual. Para ayudarnos creamos la columna E.


Método 2

Sin usar fórmulas matriciales podemos obtener el promedio usando SUMAR.SI y CONTAR.SI.CONJUNTO.

Veamos la celda I6.
=SUMAR.SI(Años;G6;Porcentaje)/CONTAR.SI.CONJUNTO(Años;G6;Porcentaje;">0")/100

Método 3

En este caso usamos una función matricial. Recuerde que este tipo de funciones se validan no pulsando ENTER, sino pulsando simultáneamente tres teclas: CONTRO+SHIFT+ENTER.

La celda J6 contiene la siguiente expresión.
=SUMA((--(Años=G6))*SI.ERROR(VALOR(Porcentaje);0))/SUMA((Años=G6)*ESNUMERO(Porcentaje))/100

Para saber más ...

En el siguiente post se resuelve un caso similar pero que se caracteriza porque los rangos son todos del mismo tamaño. Lo interesante del caso que hemos visto en esta ocasión es que los rangos no son todos iguales, ya que existen años con 365 días y años con 366 días. Además teníamos el handicap de contar con días en los que no existía Euribor y se marcaban con un guión bajo (_).