martes, 31 de agosto de 2010

La función pago


Dentro de las funciones financieras, nos permiten realizar cálculos de tipo económico, que podemos usar con Excel, tenemos la función pago. Cuando solicitamos un crédito en un banco, debemos ir pagándolo con mensualidades, anualidades... Pago nos calcula esas cantidades.

Pago tiene cinco argumentos. Los dos últimos son opcionales. Hacen referencia a si vamos a hacer el pago al principio o final de cada periodo, y el otro es si con el crédito vamos a financiar o no todo el capital prestado.

Supongamos que deseamos financiar 25000 €, a una tasa anual del 5,25% en 6 años.


Vamos a hallar la cantidad mensual que hay que pasar al banco en estas condiciones. Tecleamos la función donde queremos tener el resultado.


Como deseamos hacer el cálculo mensual, la tasa debe ser también mensual. La tasa la dividimos entre doce pues el dato que tenemos es anual. El argumento nper es el número de pagos que vamos a realizar en los 6 años. El dato será multiplicar el número de años por 12 meses. Tenemos ya así calculado el número de pagos total. Va hace referencia al capital que financiamos. Por tanto, escribimos la función con sus argumentos:


Pulsamos en aceptar, y tenemos el dato que buscamos:

Se observa que el resultado aparece en rojo, esto es así por que es un pago, salida de dinero, y Excel lo coloca en rojo. Si queremos que sea positivo y que no esté en rojo, solo hay que introducir el argumento capital (el tercero Va) con un signo menos -. Editamos la fórmula y colocamos el signo menos:



Así, el resultado será positivo:



Si se desea devolver el dinero por anualidades, un solo pago cada año, para el mismo préstamo, el cálculo del mismo será así:


El resultado es:

Con una función sencilla podemos aproximarnos un poco a los pagos que hacemos a los bancos, hipotecas, coches o vacaciones que deseamos financiar. Nos va a dar una idea de lo que vamos a pagar mientras dure el préstamo.

jueves, 26 de agosto de 2010

Gráficos SmartArt

En Excel 2007, y también en PowerPoint aparece un nuevo elemento. Pretende que los usuarios puedan comunicar información de forma visual. Podemos crear gráficos SmartArt para mostrar listas o procesos o insertar un organigrama. En esta entrada vamos a ver como crear un gráfico SmartArt en PowerPoint 2007.

En una diapositiva de una presentación (o archivo) de PowerPoint insertaremos el nuevo gráfico. Pulsamos en la pestaña Insertar y después en SmartArt:



Aparece la ventana Elegir un gráfico SmartArt. Contiene tres zonas. En la primera podemos elegir el tipo de SmartArt que queremos construir. En la segunda zona seleccionamos el que nos interesa con un simple clic. Y en la última zona aparece una descripción más detallada del elemento seleccionado en la segunda zona.


Pulsamos en aceptar y ya podemos introducir la información que nos interesa. Seleccionamos antes el gráfico Lista de bloques verticales antes de aceptar. El resultado que nos muestra es el siguiente:


Para rellenar no tenemos más que escribir en el panel que aparece a la izquierda. Cada vez que escribimos un nuevo elemento se va agregando al gráfico. El título lo colocamos en el cuadro de la diapositiva dando un clic. Si deseamos añadir una nueva sección en el último elemento pulsamos en intro y después pulsamos en la tecla tabulador (debajo de la tecla del uno) con la tecla shift (flecha hacia arriba) pulsada.

Trabajando con estos elementos podemos obtener algo como:



El resultado final aparece simplemente pulsando fuera:


Como vemos, este SmartArt ha permitido construir una lista resumen de los distintos elementos que nos podemos encontrar en Excel.

miércoles, 25 de agosto de 2010

Como introducir información y recibir respuesta en Excel


Vamos a trabajar un poco con código en Excel de forma que nos pida información, y de acuerdo con esa información recibiremos una respuesta. En código VBA usaremos dos elementos:

  • InputBox
  • MsgBox

Cuando ejecutemos la macro que contiene inputbox, aparece un cuadro de dialogo pidiendo datos. Con msgbox ejecutándose muestra una ventana con unos datos. Vamos a crear una macro que nos pida unos datos y de acuerdo con ellos nos dará una respuesta.

Pinchamos en Programador y después en Visual Basic:


Como muestra la imagen, también podemos pulsar la combinación de teclas Alt+F11 para poder introducir el código de la macro. Introducimos un nuevo módulo con Insertar / Módulo. Ahora vamos a introducir un módulo con los elementos de los que hablamos. Escribiremos:

Sub introducir_datos()

  Dim Autor As String
  Dim titulo As String
  Dim Mensaje As String
  autor = InputBox("Introducir autor:", "Datos del libro")
  titulo = InputBox("Introducir Título:", "Datos del libro")
  Mensaje = "Bienvenido, el título es: " & titulo & " y el autor es: " & autor
  MsgBox Mensaje
End Sub


Con dim definimos 3 variables, autor, titulo y mensaje. Con cada inputbox pedimos al usuario de Excel que introduzca datos, el primero le pide el autor del libro, y el segundo el título del libro. Los datos se almacenan en las variables autor y titulo respectivamente. Ahora solo queda visualizar los datos. Unimos en primer lugar las variables titulo y autor en la variable mensaje con el símbolo & e incluyendo texto entrecomillado. Ahora msgbox muestra la variable mensaje al completo.

Cerramos la ventana de VBA. Vamos ahora a insertar un botón que ejecute la macro. pinchamos en la cinta del programador en insertar:


y seleccionamos el botón de los controles de formulario (el primer botón). Encima de las celdas arrastramos el ratón pinchando para ubicar el botón:



Aparece automáticamente la ventana:



Donde solo tenemos que seleccionar la macro que se ejecutará al pinchar en el botón. Como solo hay una macro la seleccionamos y pinchamos en aceptar. También podemos cambiar el nombre del botón. Hacemos un clic al comienzo del texto y ponemos el nuevo nombre y hacemos clic en una celda cualquiera que da como resultado el botón:



Ya solo queda ver el resultado, hacemos un clic en el botón aparece el siguiente resultado donde introduciremos el primer dato:



al pulsar en aceptar, aparece el otro cuadro de dialogo pidiendo el título del libro, que lo escribimos:


Por último, al pulsar en aceptar aparece el cuadro de dialogo que genera msgbox mostrando el resultado que buscamos:


Hemos visto como crear una macro que nos pide datos, y después visualiza información con ayuda de dos comandos inputbox, para dar información a Excel, y con msgbox muestra información almacenada en una variable.

Además hemos asociado a la macro a un botón que permite ejecutar la macro con un simple clic.

viernes, 20 de agosto de 2010

Truco para añadir texto en Word 2007

Cuando interesa colocar texto rápidamente en un documento, sin importarnos su contenido, podemos usar un pequeño truco. Consiste en escribir lo siguiente seguido de un enter:

=rand()

De esta forma, aparecen de forma automática tres párrafos. El resultado se muestra a continuación:


Si con esto, no nos parece suficiente, podemos hacer que aparezcan, por ejemplo, 8 párrafos, en ese caso deberemos escribir:

=rand(8)

Es decir, entre paréntesis ponemos el número de párrafos que nos interesan que aparezcan. Podemos llenar una o varias hojas en función de los párrafos que deseemos. El contenido, es repetir los 3 primeros párrafos una y otra vez hasta completar el número de párrafos asignados.

Así podemos llenar hojas rápidamente, marcando simplemente el número de párrafos que interesa que aparezcan.

viernes, 13 de agosto de 2010

Contabilizar solo determinados elementos

En más de una ocasión tendremos la necesidad de sumar determinadas cantidades. La función suma nos permite obtener el resultado de adicionar esas cantidades que nos interesen. En este post vamos un poco más adelante. Vamos a plantearnos una situación en la que interesa sumar es solo determinadas cantidades en función de si se verifica o no determinado criterio.

Nos vamos a ayudar de una función de Excel llamada sumar.si. Su sintaxis es la siguiente:



Tenemos tres argumentos:
  1. El primero es rango, contiene las celdas que deben cumplir el criterio para que se pueda sumar.
  2. El segundo es criterio, es el elemento que marca la condición que deben verificar las celdas que participarán en la suma.
  3. El tercero es el rango_suma que marca las celdas que contienen los datos correspondientes a los de rango que se han de sumar si su correspondiente de rango cumple con el criterio.
Como rango_suma va entre paréntesis, significa que es un argumento prescindible, es decir, es un argumento que puede no aparecer, en ese caso, el argumento rango también será el rango_suma (serán valores numéricos, por supuesto).

Partimos de los siguientes datos:





Tenemos las ventas de varias oficinas de varios países. Ahora nos interesa ver las ventas de cada país. Por lo tanto, vamos a sumar solo las cantidades de cada país para obtener el total por países. Para colocar los resultados vamos a colocarlos así.


En primer lugar nos ubicamos en la celda G4, lugar donde tenemos que hallar la suma de ventas de todas las ciudades de Francia. Escribiremos el signo igual e introducimos sus argumentos de la siguiente forma:



El primer argumento corresponde al rango A2:A15 que contiene el nombre de los países. El segundo argumento hace referencia a la celda F4, que contiene el nombre del país Francia. También podíamos haber escrito como 2º argumento a "Francia". También así se obtiene el mismo resultado. El tercer argumento hace referencia a las celdas que hay que sumar. En este rango solo se sumarán aquellas que correspondan a ciudades francesas. El resto de cantidades no las tiene en cuenta en la suma.

Observando los argumentos de la función, el primero y el tercero están fijados con la tecla de función F4 que fija las referencias a las celdas, y al arrastrar la fórmula, los argumentos no son modificados.

Al teclear el paréntesis de cierre pulsamos la tecla Intro, y se muestra el resultado:


Para obtener el resultado de los demás países solo tenemos que copiar la fórmula. Nos colocamos, otra vez, en G4, y copiamos la fórmula contenida. Solo hacemos doble clic en la esquina inferior derecha de la celda y automáticamente obtenemos los resultados de todos los países.

 


La suma de las columnas Ventas de la primera imagen y la columna de Ventas totales de la tabla final son y deben ser lo mismo. En el segundo caso hemos agrupado las cantidades iniciales de las oficinas por países. Esto también podemos hacerlo usando tablas dinamícas. En posts anteriores hemos hablado de dichas tablas.

Sumar.si es una función que siempre podemos hacer uso de la misma. Es útil y va a sumar cantidades de acuerdo con determinados criterios.

viernes, 6 de agosto de 2010

Formato condicional en Excel 2007

El formato condicional en Excel 2007 presenta variaciones interesantes con respecto a 2003. Por ejemplo, antes podíamos establecer hasta 3 formatos distintos en una hoja. Ahora con la versión 2007 podemos establecer más condiciones si nos hace falta. Simplemente iremos añadiendo las reglas que nos interese establecer. A cada una de ellas le asignaremos un color, o un borde o fondo distinto, según la regla que cumplan. Podemos también aplicar escalas de colores, barras de datos o conjuntos de iconos.

En esta entrada vamos a ver como podemos aplicar un formato condicional y a una serie de datos veremos como le sienta unas barras de colores.

Formato condicional

Partimos de una serie de datos numéricos tal como los que siguen:




Vamos a establecer un formato condicional sobre estos datos. Todos los números que cumplan el criterio que establezcamos presentarán otro formato distinto al que tienen ahora. El primer paso es seleccionar todas las celdas a las que queremos aplicar el formato condicional. Seleccionamos todos los números. Seleccionamos la cinta de inicio y hacemos clic en Formato condicional:



En el menú que se despliega vamos a hacer clic en Nueva regla..., para decidir que valores nos interesa destacar con esta herramienta.



Aparece una ventana que nos permite seleccionar entre varios tipos de reglas. Seleccionamos la regla Aplicar formato únicamente a las celdas que contengan. En Editar una descripción de regla marcamos los valores que deseamos que tengan un formato distinto:


Hemos establecido que las celdas que deben cambiar de formato sean aquellas que tengan valores comprendidos entre el valor de la celda A7 y el valor 84. Ahora nos queda establecer el color de los valores de las celdas que cumplan el criterio recién establecido.




Pulsando en aceptar volvemos a la imagen anterior, pero mostrándonos el formato que hemos determinado.



Pulsamos en aceptar para ver el resultado,  si modificamos algunos valores su formato puede variar si entra o sale dentro del rango de datos que muestran el nuevo formato:


En cualquier momento podemos editar la regla bien para modificarla, o para eliminarla. En este caso vamos a formato condicional, y marcamos Administrar reglas...


Ahora podemos, desde aquí, modificar la regla con el botón Editar regla... o eliminarla con Eliminar regla e incluso añadir nuevas reglas.

Barras de datos

A partir de unos datos obtenidos vamos a insertar barras que nos van mostrar la importancia de un dato dentro del rango seleccionado. Partiremos de los siguientes datos.


Ahora seleccionamos los datos de la columna total. Vamos a la cinta de inicio y pinchamos en formato condicional, donde elegimos Barras de datos:



Como queremos que las barras sean de color azul, hacemos clic en la primera de las mismas. El resultado es el siguiente:



Dependiendo del valor la barra es más larga o más corta lo que ilustra mejor los datos obtenidos con los cálculos realizados.

El formato condicional en Excel 2007 ofrece cambios interesantes, y más opciones que las que ofrecían versiones anteriores. El número de reglas que podemos establecer no se limita a tres, podemos marcar más reglas si nos parece conveniente. Además, nos ofrece la posibilidad de usar barras de datos, que nos dan la importancia de un dato con respecto a los demás.

jueves, 5 de agosto de 2010

Agrupar fechas en tablas dinamicas

Como en anteriores entradas seguimos trabajando con los mismos datos, a partir de los cuales vamos a agrupar los movimientos de piezas teniendo en cuenta la fecha de entrada de los mismos. También veremos como añadir mas totales. Podríamos ver el promedio, máximo o mínimo además de la suma total.


Agrupar fechas

Para ver como podemos hacerlo, creamos una tabla donde introducimos, entre otros campos, el campo Fecha entrada. La visualización junto con los campos usados es la que se muestra a continuación:


La tabla nos muestra la suma de unidades de piezas clasificadas por fecha y por el país de origen. Ahora vamos a trabajar para ver la misma tabla, pero las fechas aparecerán como trimestres. En la primera fecha hacemos clic con el botón dcho y elegimos agrupar...  para que se abra la ventana Agrupar.


En la ventana podemos agrupar las fechas desde segundos hasta por años. Podemos mostrar varios a la vez. Pero solo vamos a agrupar las fechas por trimestres, como hemos mencionado, como muestra la figura. Si quisiésemos agrupar también por meses también, solo bastaría con hacer clic en meses.


Pulsamos en aceptar y las fechas, tal como aparecían hasta ahora, desaparecen y son sustituidas por trimestres:

Ahora podemos hacer que aparezcan todos los datos de cada trimestre, u ocultarlos. En la imagen mostramos los datos de trimestre 1 y del resto de trimestres se ocultan.

 

Configuración de campo

También para cada trimestre podemos obtener más totales de los que se muestran. Para añadir otros totales, botón dcho sobre uno de los trimestres, y hacemos clic en Configuración de campo...


En la ventana seleccionamos la opción Personalizado, y marcamos las funciones que interesa que aparezcan ahora. En nuestro ejemplo, añadimos el promedio a la suma (que aparece por defecto):

 

Pulsamos en aceptar, y ahora al final de cada trimestre tenemos los totales correspondientes de suma y promedio de unidades:


Resumiendo, hemos visto como se pueden agrupar las fechas, para visualizarlas de otra forma, y poder ver un resumen de los datos clasificados por tiempo. También hemos añadido a nuestra tabla mas totales, en concreto promedio, de forma que la tabla nos proporciona más información.

Actualizar tablas en Excel

En el siguiente ejercicio vamos a ver como actualizar tablas en excel. Nos centraremos en modificar unos precios con código VBA. Modificare...