Nos vemos en la necesidad de solicitar a una entidad bancaria un préstamo de 60.000 euros para realizar unas reformas en nuestra vivienda. El banco nos proporciona el dinero a un interés anual fijo del 3%.
Se pide: Calcular la cantidad mensual que deberemos pagar al banco si deseamos devolver el préstamo más los intereses, para los siguientes periodos de tiempo:
- 1 año.
- 3 años.
- 5 años.
Solución: La hoja de cálculo con los datos de nuestro problema podría ser la que mostramos en la Figura 8.2. En ella podemos observar la cantidad solicitada para el préstamo en la celda C2, el interés fijo anual que nos cobra el banco en la C3, y los tres periodos de tiempo en las celdas B9, B10 y B11, respectivamente.
Las soluciones, que obtendremos utilizando la función PAGO(...), estarán situadas en las celdas D9, D10 y D11. Antes de usar la función estudiemos detenidamente su sintaxis y el significado de sus argumentos.
Sintaxis
PAGO(tasa;nper;va;vf;tipo)
En todas las funciones, los argumentos en negrita indican que son obligatorios y los otros que son opcionales.
Fig. 8.2. Hoja de cálculo de ejemplo.
domingo, 21 de diciembre de 2008
Caso práctico 1
Funciones financieras, matemáticas y trigonométricas
Funciones financieras
Existen un total de 55 funciones financieras predeterminadas, que realizan toda clase de cálculos relacionados con valores bursátiles, amortizaciones, préstamos, inversiones, letras del tesoro, etcétera.
Aunque el mundo de las finanzas pueda parecer un poco alejado de nuestra vida cotidiana, es posible que en el futuro nos veamos en la necesidad de realizar alguno de estos cálculos, bien sea para conocer la cuota de una hipoteca, los intereses a pagar por un préstamo, etcétera.
Comenzaremos viendo un sencillo ejemplo de utilización de la función PAGO(...), que calcula el pago de un préstamo basándose en pagos constantes y en una tasa de interés constante.
Lo haremos mediante el primer caso práctico de la unidad.
Funciones matemáticas y trigonométricas
El grupo de las funciones matemáticas y trigonométricas está compuesto por un total de 60 funciones predeterminadas.
Las que no se pueden considerar trigonométricas realizan operaciones como logaritmo, potencia, redondeo, valor absoluto, máximo común divisor y mínimo común múltiplo, división entera, operaciones con matrices (producto, determinante, inversa, etc.), raíz cuadrada, números aleatorios, y un largo etcétera que seguro nos puede ayudar a la hora de realizar cálculos con las celdas.
De entre las segundas, las trigonométricas, disponemos de un gran número de funciones que calculan las típicas operaciones con ángulos, como por ejemplo el seno, coseno, tangente, etc., valor del número Pi, y funciones de conversión de grados a radianes y viceversa.
Aprenderemos a utilizar algunas de estas funciones en el Caso práctico 2.
Uso de fórmulas complejas
A. Funciones y expresiones
Excel ofrece al usuario un total de 329 funciones de hoja de cálculo predeterminadas, agrupadas en las siguientes categorías: financieras, matemáticas y trigonométricas, fecha y hora, estadísticas, búsqueda y referencia, base de datos, texto y datos, lógicas, información e ingeniería. Por suerte para nosotros, podemos simplificar su utilización mediante el Asistente de inserción de funciones, que podemos activar haciendo clic en el botón Insertar función de la barra de fórmulas, seleccionando la opción de menú Insertar - Fórmula, o tecleando a la vez Mayús+F3, estando situados en la celda deseada.
Según la definición que ofrece la ayuda de Excel, las funciones son fórmulas predefinidas que ejecutan cálculos utilizando valores específicos, denominados argumentos, en un orden determinado o estructura. Las funciones pueden utilizarse para ejecutar operaciones simples o complejas.
La estructura de una función comienza por el nombre de la función, un paréntesis de apertura, los argumentos de la función separados por puntos y coma, y un paréntesis de cierre. Los argumentos de una función pueden ser números, texto, valores lógicos como VERDADERO o FALSO, matrices, valores de error como #N/A o referencias de celda. El argumento que se designe deberá generar un valor válido para el mismo. Los argumentos pueden ser también constantes, fórmulas u otras funciones.
Los argumentos de una función pueden ser a la vez funciones. Cuando sucede esto hablamos de funciones anidadas. Excel soporta un máximo de siete niveles de anidamiento.
Cuando creemos una fórmula que contenga una función, el asistente de inserción de funciones nos ayudará a introducir las funciones de la hoja de cálculo. A medida que se introduzca una función en la fórmula, el asistente irá mostrando el nombre de la función, cada uno de sus argumentos, una descripción de la función y de cada argumento, el resultado actual de la función y el resultado actual de toda la fórmula. También tenemos la posibilidad de insertar las funciones directamente mediante el teclado. En este caso, tras teclear el paréntesis de apertura, aparece una ayuda contextual que nos muestra los diferentes argumentos de la función y en negrita el que hemos de introducir en cada instante (véase la Figura 8.1).
Las fórmulas permiten utilizar funciones para realizar sus cálculos, pero además ofrecen la posibilidad de combinarlas con otros operandos y operadores formando lo que se conoce con el nombre de expresión. Ya estudiamos en la Unidad 7 un ejemplo de expresión que combinaba funciones, referencias a celdas y valores constantes. Además, mostramos en sendas tablas los principales operadores aritméticos y de comparación que permiten la construcción de expresiones, así como la prioridad de cada uno de ellos. También comentamos que, cuando las operaciones efectuadas en las expresiones no están delimitadas mediante paréntesis, el orden de evaluación de las mismas es de izquierda a derecha. A continuación efectuaremos un repaso de cada una de las categorías de funciones, mostrando el uso de alguna de ellas mediante casos prácticos.
Fig. 8.1. Ayuda contextual de la función financiera NPER.
Uso de fórmulas complejas
A. Funciones y expresiones
Excel ofrece al usuario un total de 329 funciones de hoja de cálculo predeterminadas, agrupadas en las siguientes categorías: financieras, matemáticas y trigonométricas, fecha y hora, estadísticas, búsqueda y referencia, base de datos, texto y datos, lógicas, información e ingeniería. Por suerte para nosotros, podemos simplificar su utilización mediante el Asistente de inserción de funciones, que podemos activar haciendo clic en el botón Insertar función de la barra de fórmulas, seleccionando la opción de menú Insertar - Fórmula, o tecleando a la vez Mayús+F3, estando situados en la celda deseada.
Según la definición que ofrece la ayuda de Excel, las funciones son fórmulas predefinidas que ejecutan cálculos utilizando valores específicos, denominados argumentos, en un orden determinado o estructura. Las funciones pueden utilizarse para ejecutar operaciones simples o complejas.
La estructura de una función comienza por el nombre de la función, un paréntesis de apertura, los argumentos de la función separados por puntos y coma, y un paréntesis de cierre. Los argumentos de una función pueden ser números, texto, valores lógicos como VERDADERO o FALSO, matrices, valores de error como #N/A o referencias de celda. El argumento que se designe deberá generar un valor válido para el mismo. Los argumentos pueden ser también constantes, fórmulas u otras funciones.
Los argumentos de una función pueden ser a la vez funciones. Cuando sucede esto hablamos de funciones anidadas. Excel soporta un máximo de siete niveles de anidamiento.
Cuando creemos una fórmula que contenga una función, el asistente de inserción de funciones nos ayudará a introducir las funciones de la hoja de cálculo. A medida que se introduzca una función en la fórmula, el asistente irá mostrando el nombre de la función, cada uno de sus argumentos, una descripción de la función y de cada argumento, el resultado actual de la función y el resultado actual de toda la fórmula. También tenemos la posibilidad de insertar las funciones directamente mediante el teclado. En este caso, tras teclear el paréntesis de apertura, aparece una ayuda contextual que nos muestra los diferentes argumentos de la función y en negrita el que hemos de introducir en cada instante (véase la Figura 8.1).
Las fórmulas permiten utilizar funciones para realizar sus cálculos, pero además ofrecen la posibilidad de combinarlas con otros operandos y operadores formando lo que se conoce con el nombre de expresión. Ya estudiamos en la Unidad 7 un ejemplo de expresión que combinaba funciones, referencias a celdas y valores constantes. Además, mostramos en sendas tablas los principales operadores aritméticos y de comparación que permiten la construcción de expresiones, así como la prioridad de cada uno de ellos. También comentamos que, cuando las operaciones efectuadas en las expresiones no están delimitadas mediante paréntesis, el orden de evaluación de las mismas es de izquierda a derecha. A continuación efectuaremos un repaso de cada una de las categorías de funciones, mostrando el uso de alguna de ellas mediante casos prácticos.
Fig. 8.1. Ayuda contextual de la función financiera NPER.
viernes, 19 de diciembre de 2008
decimal
Esta función nos devuelve en una nueva celda un número o valor de una celda con un número determinado de decimales, además de la posibilidad de poderlo mostrar con separaciones de millares.
Estructura: DECIMAL(Valor;Número de decimales;No separación de miles) dónde Valor es un número o una celda la cual contiene un número, Número de decimales es el número total de decimales que se desea obtener y No separación de miles es un argumento que puede tener dos posibles valores VERDADERO o FALSO para indicar si deseamos ver o no ver el nuevo valor con el separador decimal.
Ejemplo: En la celda A1 introduciremos el valor: 2345,678 con el que trabajaremos. Si en la celda A3 quisiéramos obtener el valor 2.345,68 tendremos que introducir la siguiente función =DECIMAL(A1;2;FALSO). Hay que observar que el número original tenía tres decimales 678 y al mostrar solo 2, se redondea el tercero al alza. Si el tercer decimal es menor de 5 al reducirlo a 2 decimales este no se redondea al alza.
esnumero - estexto - esnotexto
ESNUMERO
Esta función nos permite saber si el valor de una celda es un número.
Estructura: ESNUMERO(Valor), donde Valor es la celda o valor que deseamos conocer si es un número o no.
Ejemplo: La celda A1 la reservaremos para introducir el valor que deseamos conocer si es o no es un número. Por ejemplo, en la celda A1 introduciremos el valor 2345 y para saber si es un número introducimos en la celda A3 la fórmula =ESNUMERO(A1), en este caso obtendremos como resultado VERDADERO.
ESTEXTO
Con esta función podremos conocer si el valor de una celda es texto.
Estructura: ESTEXTO(Valor), donde el argumento Valor lo substituiremos por una celda o por un valor para conocer si es texto o no.
Ejemplo: En la celda A1 introduciremos, por ejemplo, el texto abcdef y en la celda A1 la fórmula =ESTEXTO(A1). El resultado de la función será VERDADERO ya que abcdef es una cadena de texto.
ESNOTEXTO
Utilizando esta función podremos saber si el valor de una celda no es un texto.
Estructura: ESNOTEXTO(Valor), donde Valor es la celda o el valor que deseamos saber si es o no es texto.
Ejemplo: La celda A1 la reservaremos para introducir el valor que deseamos conocer si es o no es texto. Por ejemplo introduce el valor 1234 y en la A3 la función =ESNOTEXTO(A1) la cual nos dará como resultado VERDADERO, ya que el contenido de la celda es un número.
martes, 16 de diciembre de 2008
ES.PAR
Esta fórmula nos devolverá el valor VERDADERO si un número es par.
Estructura: ES.PAR(Número), dónde Número es el valor que se desea saber si es par.
Ejemplo: En la celda A1 introduce cualquier número, por ejemplo 1234 y en la celda A3 escribe la función =ES.PAR(A1) y obtendremos como resultado VERDADERO. Si el valor fuera impar obtendremos como resultado FALSO.
ES.IMPAR
Con esta función sabremos si un valor es impar.
Estructura: ES.IMPAR(Número), donde Número es el valor que deseamos saber si es impar.
Ejemplo: En la celda A1 introduciremos como ejemplo el número 2345 y en la celda A3 escribiremos la función =ES.IMPAR(A1) y obtendremos como resultado VERDADERO. Si en este caso el valor fuera par obtendremos como resultado FALSO.
fin.mes
Al utilizar esta formula obtendremos el último día del mes a partir de la fecha inicial y contabilizando una cantidad de meses.
Estructura: FIN.MES(Fecha inicial;Número de meses)
Ejemplo: En la celda A1 escribiremos la fecha de partida "12/01/2007" y en la celda A3 introduciremos la celda =FIN.MES(A1;0) y de esta forma obtendremos el 31/01/2007. El 0 del segundo argumento indica que deseamos conocer el último día del mismo mes de la fecha inicial. Si cambiáramos el 0 por un 1 obtendríamos como fecha final el 28/2/2007.
dia.lab
Esta función nos devuelve un día laborable que se calcula a partir de una fecha inicial que se obtiene al sumar un número determinados de días dados a esta fecha inicial. Por ejemplo si partimos del día 1/1/2004 y le sumamos 8 días laborables obtendremos como día laborable más cercano a esta fecha inicial el 13 de enero, ya que no se contabilizará ni sábado, ni domingo. En esta función al igual que en DIAS.LAB se podrá añadir una lista de festivos que no serán contabilizados como días laborables.
Estructura: DIA.LAB(Fecha inicial;Días laborables;Festivos), dónde días laborables es el número de días laborables a contabilizar a partir de la fecha inicial y Festivos será el rango de festivos que queremos que no se consideren como laborables.
Ejemplo: En la celda A1 escribiremos la fecha inicial, por ejemplo 01/01/2004 y en la celda A2 pondremos el número de días laborables que deseamos sumar al que aparece en la celda A1, en este caso pondremos un 8 para intentar sumar 8 días laborables a partir de 1 de enero.
En la celda B4 escribiremos esta función =DIA.LAB(A1;A2) y obtendremos como resultado el 13/01/2004, observaremos que sólo se han contabilizado días laborables.
Si deseamos añadir fechas que no sean laborables tenemos que realizar una pequeña modificación en esta función. En las celdas A7 y A8 introduciremos el día 01/01/2004 y el 06/01/2004. En la función de la celda B4 deberemos añadir un tercer argumento quedando la fórmula de la siguiente forma =DIA.LAB(A1;A2;A7:A8), ahora la fecha resultante será el día 14 de enero.
dias.lab
La función realiza la diferencia de días que hay entre la fecha inicial y la fecha final contabilizando solamente los días laborables, no tiene en cuenta los sábados y domingos.
Si deseamos que la función tampoco tenga en cuenta otros festivos podremos incorporar un rango de fechas en el argumento Festivos.
Estructura: DIAS.LAB(Fecha inicial;Fecha final;Festivos) Festivos será el rango de fechas que queremos considerar como festivos y no queremos que el ordenador contabilice como laborables.
Ejemplo: En este ejemplo utilizaremos dos celdas en las que pondremos, en una la fecha inicial y en la otra la fecha final para calcular el número de días laborales que hay entre las dos. En la celada A1 escribiremos "01/01/2004" y en la B1 "12/01/2004". En la celda B2 introduciremos la siguiente función: =DIAS.LAB(A1;A2), la cual nos devolverá un 8. Si consultamos un calendario podemos ver que el día 3, 4, 10 y 11 caen en fin de semana con lo que la formula no los contabiliza.
Si quisiéramos contabilizar el número de días entre las dos fechas simplemente deberíamos introducir una simple resta de esta forma =A2;A1, la cual nos devolvería un 11 que son los días que hay entre una y otra fecha. Recuerda que al ser una resta el ordenador realiza la diferencia que hay entre los días al igual que si hiciéramos una resta entre el número 12 y el número 1.
Vamos a ampliar la función =DIAS.LAB(A1;A2) en la que incluiremos los días festivos que hay en el mes de enero, que sería el día 1 y el día 6 para que no se contabilicen como días laborables.
Para ello realizaremos una lista de días festivos (en este ejemplo sólo pondremos los días festivos de enero, si quisiéramos podríamos hacerlo con todos los días del año). En la celda A5 introduciremos el 01/01/2004 y en la A6 el 06/01/2004.
Ahora para que la función tenga en cuenta estos nuevos días festivos realizaremos una modificación en la función e introduciremos el rango de festivos en el tercer argumento de la función. La función quedará de la siguiente forma: =DIAS.LAB(A1;A2;A5:A6) con lo que el resultado de la misma será 6.
domingo, 14 de diciembre de 2008
DEC.A.BIN
Esta función nos convierte un número Decimal a Binario.
Recuerda que los números Binarios se representan solamente utilizando como base dos, están compuestos sólo con los dígitos 0 y 1. En esta función el número a convertir a decimal no puede tener más de 10 dígitos.
Más información de Sistema Binario en Wikipedia
Se tiene que destacar que Excel no convertirá números más grandes del 511 que en binario es el 111111111. En caso de ser valores negativos no convertirá números más pequeños del 512. Si ponemos números que no cumplan estas condiciones nos aparecerá el mensaje: #¡NUM!
Estructura: DEC.A.BIN(Número; Caracteres) En el argumento Número pondremos el valor entero y decimal que deseamos convertir. Puedes introducir un número o una celda en la que se encuentre el valor en decimal a convertir. En Caracteres deberás introducir el número de caracteres que quieras que se muestre. Excel introducirá tantos ceros a la izquierda como sea necesario como para llegar al número de caracteres que deseas ver.
Ejemplo: En la celda [A1] escribe el valor 20. En la celda [A2] escribe la función =DEC.A.BIN(A1;7). Esta función nos devolverá como resultado el número: 0010100. Observa que a la izquierda aparecen 2 ceros para llegar a los 7 caracteres que le hemos indicado deseamos obtener en el resultado.
Si esta misma función la escribiéramos así =DEC.A.BIN(A1) obtendríamos como resultado: 10100
Error: Esta celda nos devolverá #¡NUM! Si el valor que introducimos dentro de la función no es un valor Decimal.
BIN.A.OCT()
Esta función nos convierte un número Binario a Octal.
Recuerda que los números Binarios se representan solamente utilizando como base dos, están compuestos sólo con los dígitos 0 y 1. En esta función el número a convertir a decimal no puede tener más de 10 dígitos.
El sistema de numeración Octal se basa en 8 dígitos.
Más información de Sistema Binario en Wikipedia
Más información de Sistema Octal en Wikipedia
Estructura: BIN.A.OCT(Número; Caracteres) En Número pondremos el valor en base 10 que deseemos convertir en Octal. Puedes introducir un número, o una celda en la que se encuentre un valor en formato decimal. En Caracteres deberás introducir el número de caracteres que quieras que se muestre. Excel introducirá tantos ceros a la izquierda como sea necesario como para llegar al número de caracteres que deseas ver.
Ejemplo: En la celda [A1] escribe el valor 1001. En la celda [A2] escribe la función =BIN.A.OCT(A1;5). Esta función como resultado nos devolverá el valor 00011.
Error: Esta celda nos devolverá #¡NUM! si el valor que introducimos es negativo o no es un valor Binario.
miércoles, 10 de diciembre de 2008
bin.a.hex
Esta función nos convierte un número Binario a Hexadecimal.
Recuerda que los números Binarios se representan solamente utilizando como base dos, están compuestos sólo con los dígitos 0 y 1. En esta función el número a convertir a decimal no puede tener más de 10 dígitos.
El sistema de numeración Hexadecimal se basa en 16 dígitos. El sistema Decimal que utilizamos normalmente sólo consta de 10 dígitos con lo que tenemos que utilizar letras de la A a la F para así conseguir poder trabajar con 16 dígitos.
Más información de Sistema Binario en Wikipedia
Más información de Sistema Hexadecimal en Wikipedia
Estructura: BIN.A.HEX(Número; Caracteres) En Número pondremos el valor en base 10 que deseemos convertir en Hexadecimal. Puedes introducir un número, o una celda en la que se encuentre un valor en formato decimal. En Caracteres deberás introducir el número de caracteres que quieras que se muestre. Excel introducirá tantos ceros a la izquierda como sea necesario como para llegar al número de caracteres que deseas ver.
Ejemplo: En la celda [A1] escribe el valor 11111011. En la celda [A2] escribe la función =BIN.A.HEX(A1;5). Esta función como resultado nos devolverá el valor 000FB.
Error: Esta celda nos devolverá #¡NUM! si el valor que introducimos es negativo o no es un valor Binario.
bin.a.dec
Esta función nos convierte un número Binario a Decimal.
Recuerda que los números Binarios se representan solamente utilizando como base dos, están compuestos sólo con los dígitos 0 y 1. En esta función el número a convertir a decimal no puede tener más de 10 dígitos.
Más información de Sistema Binario en Wikipedia
Estructura: BIN.A.DEC(Número) En Número pondremos el valor en base 10 que deseemos convertir en Decimal. Puedes introducir un número, o una celda en la que se encuentre un valor en formato decimal.
Ejemplo: En la celda [A1] escribe el valor 10010. En la celda [A2] escribe la función =BIN.A.DEC(A1). Esta función como resultado nos devolverá el número 18.
Error: Esta celda nos devolverá #¡NUM! Si el valor que introducimos dentro de la función no es un valor Binario.
Aleatorio.entre
Utilizando esta función podremos obtener un número aleatorio entero entre dos números que especifiquemos.
Es importante hacer notar que cada vez que se calcula la hoja de cálculo se devuelve un número nuevo. El recálculo de la hoja se hace cada vez que introducimos un cambio en la hoja lo que obliga a realizar los cálculos nuevamente o cada vez que pulsamos la tecla F9.
Estructura: ALEATORIO.ENTRE(Valor inferior; Valor superior)
El valor inferior será el valor del mínimo que podrá conseguir nuestro número aleatorio y el valor superior será el máximo que podremos obtener.
Ejemplo: en la celda A1 escribiremos el valor mínimo y en la celda A2 el valor máximo para el intervalo en el que buscaremos un número aleatorio. Es importante saber que los dos números tanto el máximo como el mínimo pueden aparecer en el resultado.
En la celda A3 escribe la siguiente función =ALEATORIO.ENTRE(A1;A2) al realizar el cálculo de nuestra hoja aparecerá un número al azar entre el 1 y el 10, ambos incluidos. La función también la puedes hacer =ALEATORIO.ENTRE(1;10) pero al utilizar celdas es mucho más fácil modificar los valores para obtener resultados diferentes.
domingo, 7 de diciembre de 2008
Crear botones en la barra de herramientas personalizada
En la lección anterior hemos visto cómo crear una barra de herramientas personalizada para colocar nuestros botones, los cuales ejecutarán una macro.
En esta lección veremos cómo crear estos botones.
5.- Dentro de la ventana Personalizar deberemos activar la pestaña: Comandos.
6.- En la lista de Categorías deberíamos hacer un clic sobre: Macros.
En la parte derecha de esta misma ventana aparecerán dos opciones: Personalizar elemento de menú y Personalizar botón, en esta ocasión utilizaremos la segunda de las opciones.
7.- Deberemos hacer un clic sobre la opción Personalizar botón para seleccionarla.
8.- Seguidamente deberemos desplazar este botón a la nueva barra de herramientas que hemos creado. Para ello simplemente deberemos situarnos encima, pulsaremos el botón izquierdo del ratón y mientras lo tenemos pulsado nos desplazaremos hasta situarnos encima de la barra de herramientas que hemos creado anteriormente.
9.- Una vez situado el cursor dentro de la barra de herramientas soltaremos el botón.
Podremos ver como dentro de la barra: Mis macros aparece el botón que hemos arrastrado.
Ahora que ya tenemos creado el botón en nuestra barra de herramientas, vamos a personalizarlo para que cumpla con su función.
Observa como en la ventana actual está activado el botón: Modificar selección.
10.- Pulsa este botón, desplázate hasta la opción: Cambiar imagen del botón.
11.- De la lista, selecciona el icono que más te guste.
Observa cómo la imagen del botón de nuestra barra de herramientas ha cambiado.
12.- Pulsa nuevamente el botón: Modificar selección.
Ahora lo que cambiaremos es el texto que aparecerá en el momento en el que pongamos el ratón encima de este botón, así de esta forma siempre podremos recordar que función hace dicho botón.
13.- Selecciona la opción Nombre: y escribe: Repetir con informe.
En la siguiente lección asignaremos la macro a este botón.
Barra de herramientas personalizada
Hasta esta lección hemos visto cómo copiar, guardar y poner en funcionamiento una macro utilizando el menú de herramientas Herramientas - Macro.
En muchas ocasiones puede ser que tengamos una macro la cual necesitemos poner en funcionamiento muchas veces con lo que el hecho de abrir el menú y buscar dicha macro nos puede ser un poco engorroso.
En esta lección vamos a ver una forma de crear una nueva barra de herramientas en la que pondremos un botón para que al pulsarlo se ponga en funcionamiento la macro.
Explicaremos los pasos necesarios para conseguir esto.
Crear una barra de herramientas nueva
1.- Abrir el menú Ver - Barras de herramientas y seleccionar la opción: Personalizar.
A continuación aparecerá una nueva ventana llamada: Personalizar.
2.- De esta ventana pulsar sobre el botón: Nueva...
Nos aparecerá una ventana llamada: Nueva barra de herramientas en la que deberemos especificar el nombre que le deseamos dar a nuestra barra para poderla diferenciar del resto.
3.- Para nuestro ejemplo podríamos poner como nombre: Mis macros.
4.- A continuación pulsar Aceptar.
Podremos observar como en la lista de barras de herramientas disponibles aparece el nombre que acabamos de poner. A la derecha del nombre aparecerá una indicación conforme esta barra está visible. Si no la ves puedes mover la ventana Personalizar.
Ahora ya tenemos nuestra nueva barra de herramientas creada. En la siguiente lección aprenderemos cómo podemos crear un botón dentro de esta barra para que al pulsarlo se ejecute una de nuestras macros.
Macro Comparación entre columnas - Explicación primeros pasos
En esta lección realizaremos la explicación paso a paso de la macro que hemos escrito anteriormente para la comparación entre dos columnas.
Línea 2: con esta instrucción nos situamos en la primera celda de la columna B para empezar a buscar los datos que deseamos borrar.
Línea 3: creamos una variable llamada Posición para controlar en que fila nos encontramos de la segunda columna
Línea 4: aquí creamos un bucle que se repetirá hasta que no se terminen los elementos a buscar.
Línea 5: creamos una variable llamada valorcomparacion con la que trabajaremos para ir comparando los elementos de la primera y segunda columna. El valor de la celda en la que nos encontramos en la columna B, pasa a estar en la variable valorcomparacion.
Línea 6: cambiamos a la columna A para iniciar el proceso de comparación.
Línea 7: creamos una nueva variable llamada Salir con la que controlaremos si debemos salir del bucle o no. Solamente saldremos en el momento en el que se encuentre un elemento que está en la segunda y primera columna.
Línea 8: aquí iniciamos un bucle que se repetirá hasta que se llegue al final de la primera columna, (caso que se dará cuando no existan elementos comunes) o hasta que se encuentre un elemento común, (esto nos lo indicará la variable Salir).
Línea 9: en esta línea preguntaremos si la celda en la que nos encontramos (primera columna) es igual que el valor que tenemos dentro de la variable: valorcomparacion.
Línea 10: si se ha producido la coincidencia de elementos procedemos a preguntar si realmente deseamos borrar este elemento.
Línea 11: miramos si la respuesta del usuario ha sido afirmativa.
Seguiremos la explicación paso a paso en la siguiente lección.
jueves, 4 de diciembre de 2008
Macro - Comparación entre columnas
En las lecciones anteriores hemos visto como eliminar elementos repetidos existentes en una misma fila. En este ejemplo realizaremos comparaciones entre diferentes columnas, eliminando de la primera los elementos que existan en la segunda.
En la primera columna están todos los elementos que forman parte de la lista y en la segunda columna se van colocando los elementos que se desean buscar para eliminar de la primera.
A continuación escribiremos la macro completa y después explicaremos que realiza cada una de las líneas. El número que aparece al principio de cada una de las líneas nos servirá como guía en la explicación, no debes copiarlos en el Editor.
1 Sub Repetidos()
2 Range("B1").Select
3 Posicion = 1
4 While ActiveCell.Value <> ""
5 valorcomparacion = ActiveCell.Value
6 Range("a1").Select
7 Salir = "no"
8 While ActiveCell.Value <> "" And Salir = "no"
9 If ActiveCell.Value = valorcomparacion Then
10 respuesta = MsgBox("¿Deseas borrar esta entrada?", 4, "¡¡Encontrado!!")
11 If respuesta = vbYes Then
12 Selection.Delete Shift:=xlUp
13 End If
14 Salir = "si"
15 Else
16 ActiveCell.Offset(1, 0).Range("A1").Select
17 End If
18 Wend
19 Posicion = Posicion + 1
20 Range("b1").Select
21 ActiveCell.Offset(Posicion - 1, 0).Range("a1").Select
22 Wend
23 End Sub
Antes de realizar la explicación tenemos que dejar muy claro que la columna A, será donde tenemos todos los datos de nuestra lista y la columna B, los datos que deseamos buscar y eliminar de la primera columna.
Elementos repetidos en lista con registro
En esta lección seguiremos con la creación de macros que nos servirán para eliminar elementos repetidos de una lista.
Eliminar elementos repetidos en una lista y realizar un registro: en la primera macro que hemos mostrado en esta página eliminamos todos los elementos que aparecen repetidos dentro de una lista quedando uno solo de todos los elementos repetidos. Según para qué realizásemos este proceso nos podría interesar llevar un control de la cantidad de elementos que estaban repetidos, por esto sería conveniente crear en una hoja nueva, un registro de cual es el elemento repetido y el número de veces que aparecía dentro de la lista.
La macro siguiente necesita que los elementos estén ordenados en una primera hoja y que la segunda hoja esté vacía, ya que será aquí donde se realizará el registro de los elementos repetidos.
Sub EliminarRepetidosYRegistro()
contador = 1
valor = ActiveCell.Value
ActiveCell.Offset(1, 0).Range("A1").Select
While ActiveCell.Value <> ""
If ActiveCell.Value = valor Then
ActiveSheet.Next.Select
If ActiveCell.Value <> valor Then
ActiveCell.Offset(1, 0).Range("a1").Select
ActiveCell.Value = valor
End If
ActiveSheet.Previous.Select
Selection.Delete Shift:=xlUp
contador = contador + 1
Else
If contador <> 1 Then
ActiveSheet.Next.Select
ActiveCell.Offset(0, 1).Range("a1").Select
ActiveCell.Value = contador
ActiveCell.Offset(0, -1).Range("a1").Select
ActiveSheet.Previous.Select
End If
contador = 1
valor = ActiveCell.Value
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Wend
If contador <> 1 Then
ActiveSheet.Next.Select
ActiveCell.Offset(0, 1).Range("a1").Select
ActiveCell.Value = contador
ActiveCell.Offset(0, -1).Range("a1").Select
ActiveSheet.Previous.Select
End If
End Sub
miércoles, 3 de diciembre de 2008
Elementos repetidos
En lecciones anteriores hemos visto cómo movernos por una lista y cómo eliminar algunos de ellos según nos conviniera.
Ahora vamos a ver cómo podríamos utilizar estas dos cosas vistas en lecciones anteriores para poder eliminar elementos repetidos de una lista.
Eliminar elementos repetidos en una lista: vamos a realizar una macro la cual nos vayamos desplazando por una lista, hasta encontrar un elemento vacío e ir comprobando si un elemento es igual al anterior, si esto es así lo eliminaremos y "subiremos" un lugar la lista para que así no existan lugares vacíos en la lista
El único requisito que hay que cumplir para realizar esta macro es que la lista debe estar completamente ordenada.
Sub EliminarRepetidos()
contador = 0
valor = ActiveCell.Value
ActiveCell.Offset(1, 0).Range("A1").Select
While ActiveCell.Value <> ""
If ActiveCell.Value = valor Then
Selection.Delete Shift:=xlUp
contador = contador + 1
Else
valor = ActiveCell.Value
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Wend
Respuesta = MsgBox("Se han encontrado " & contador & " elementos repetidos", 1, "Número de repetidos")
End Sub
En esta macro estamos utilizando una variable llamada contador la cual nos servirá para contabilizar el número de elementos repetidos que encontramos en la lista. Este valor lo mostramos al finalizar la macro.
Movernos por un libro realizando una acción
En esta lección continuaremos con las macros que nos permiten movernos por nuestro documento.
Buscar una entrada en una lista y borrar toda la fila: vamos a imaginar que tenemos una tabla de datos en la cual nos interesa buscar un elemento determinado y borrar todos los datos que hay en la misma fila. Pongamos el caso que tenemos una tabla con nombre, población y teléfono y deseamos buscar todos los datos de una población determinada y borrar el nombre y la población Para hacer la búsqueda deberemos situarnos en la fila que se encuentra la población.
Sub BorrarFilas()
While ActiveCell.Value <> ""
If ActiveCell.Value <> "Barcelona" Then
ActiveCell.Offset(1, 0).Range("A1").Select
Else
Selection.EntireRow.Delete
End If
Wend
End Sub
Es importante hacer notar que si ejecutamos esta Macro no se podrá deshacer el borrado de las filas que se han eliminado con la Macro.
Buscar el final de una lista con espacios en blanco dentro de ella: imaginemos que tenemos una lista de datos dentro de la que hay espacios en blanco, como mucho un espacio en blanco entre dato y dato. Si utilizásemos la primera macro que hemos creado esta interpretaría como final de la lista el primer espacio en blanco que encontrara. Realizaremos una macro que interpretará el final de la lista cuando encuentre 2 espacios en blanco seguidos.
Sub FinalListaEspecial()
Salir = "No"
While Salir = "No"
While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Range("A1").Select
Wend
ActiveCell.Offset(1, 0).Range("A1").Select
If ActiveCell.Value <> "" Then
Salir = "No"
Else
Salir = "Si"
End If
Wend
End Sub
jueves, 27 de noviembre de 2008
Movernos por un libro
En esta lección veremos algunas macros que nos permitirán mover por las celdas que contiene un libro. Es recomendable trabajar con libros que contengan datos para así poder ver el efecto que la macro hace sobre la hoja.
Buscar el final de una lista: podemos utilizar esta Macro para podernos situar al final de una lista de elementos. Interpretaremos que el final de la misma está en el momento en el que encontramos una celda vacía.
Sub Final()
While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Range("A1").Select
Wend
End Sub
Deberemos observar que con esta Macro siempre queda activada la primera celda que está en blanco después de toda la lista.
Buscar el final de una lista y situarnos en la última entrada: esta Macro es exactamente igual que la anterior pero con la única diferencia que la celda activada es la última entrada de la lista.
Sub FinalLista()
While ActiveCell.Value <> ""
ActiveCell.Offset(1, 0).Range("A1").Select
Wend
ActiveCell.Offset(-1, 0).Range("A1").Select
End Sub
En esta Macro se realiza el bucle hasta que se encuentra la celda vacía y después subimos una posición para situarnos en la última entrada de la tabla.
Macros con libros
A partir de este momento vamos a ver pequeñas macros que realizan tareas muy concretas. Para poder ver cómo funcionan simplemente tienes que copiarlas en nuestro Editor de Visual Basic, según vimos en la primera lección y después ejecutarla, según vimos en la segunda.
Vamos a ver tres Macros que trabajarán directamente con libros. Con ellas podremos abrir un libro existente, activar un libro y por último crear un libro nuevo.
Abrir un libro existente: para abrir un libro utilizaremos la siguiente Macro.
Sub AbrirLibro()
Workbooks.Open ("C:\Mis documentos\Ejemplo.xls")
End Sub
Observa cómo entre paréntesis y comillas dobles hemos escrito el camino y el nombre del archivo que deseamos abrir. Este archivo debe existir, si no fuera así esta Macro nos daría error.
Activar un libro ya abierto: con esta Macro podemos cambiar el libro activo. Es muy importante que el libro ya esté abierto, si no fuera así, la Macro daría error.
Sub ACtivarLibro()
Workbooks("Ejemplo.xls").Activate
End Sub
Observa como en esta Macro no indicamos el camino donde se encuentra el archivo, no hace falta ya que este debe estar abierto.
Crear un libro nuevo: con la siguiente Macro podremos crear un libro nuevo. Esta Macro es exactamente igual que si activásemos la opción Archivo - Nuevo.
Sub NuevoLibro()
Workbooks.Add
End Sub
Si modificásemos este libro después Excel nos preguntaría si deseamos guardar los cambios.
Los nombres de las Macros no tienen porqué ser necesariamente los mismos que hemos puesto nosotros.
Cómo escribir una macro utilizando el Editor de Visual Basic
Las macros que vamos a ver a lo largo de este curso las vamos a ir programando directamente con el Editor de Visual Basic que viene incorporado con el mismo Excel.
Para ponerlo en funcionamiento deberemos realizar los siguientes pasos:
1.- Dentro del menú Herramientas - Macro deberemos escoger la opción: Editor de Visual Basic.
Una vez seleccionada se abrirá una nueva ventana con dos ventanas acopladas a la izquierda. La superior es la ventana que llamamos: de Proyecto y la inferior la de Propiedades. En la primera aparecerán los elementos que forman parte de cada Proyecto (grupo de macros y hojas de Excel) y la segunda son las propiedades de los objetos que se pueden incorporar a nuestras macros. Más adelante veremos como se utilizan estas dos ventanas y todas sus características.
Vamos a prepararnos para poder escribir nuestra primera macro
2.- Escoge la opción Módulo del menú Insertar.
Observa como en la ventana superior aparece nos aparece una nueva carpeta llamada Módulos y en su interior un nuevo elemento llamado Módulo1. Dentro de este módulo será donde guardemos las macros que creemos.
También podrás ver como la parte derecha de la ventana ahora es completamente blanca. Aquí es donde podemos escribir las instrucciones que formarán parte de nuestra Macro.
Vamos a crear una Macro y esta nos servirá de ejemplo para ver como se deben escribir.
Crearemos una Macro muy sencilla la cual nos servirá para que la página activa pase a ser la segunda.
3.- En la página en blanco de la derecha escribe lo siguiente:
Sub Cambiardehoja()
Worksheets(2).Activate
End Sub
El comando Sub indica el principio de la Macro, mientras que End Sub marca el final de esta. A continuación del comando Sub hemos puesto el nombre que deseamos dar a nuestra Macro. Observa como después del nombre aparece un paréntesis que se cierra y otro que se abre.
Entre el principio y el final de la Macro escribimos las instrucciones que deseamos se realicen en esta Macro.
Si deseamos guardar la Macro simplemente deberemos guardar el libro activo con el nombre que deseamos. Ambos elementos se guardarán juntos.
En la siguiente lección veremos como podemos poner en funcionamiento una Macro creada por nosotros.
miércoles, 26 de noviembre de 2008
Calendario de Pagos
En este capitulo haremos un "Calendario de Pagos", por el Sistema Francés, el caso es el siguiente:
Juan Quiroga, solicito un préstamo por la suma de $5000, con un interés del 30% anual en 10 cuotas. Juan quiere un calendario de pagos para llevar un control de sus amortizaciones.
| A | B | C | D | E | |
| 1 | Datos | ||||
| 2 | Préstamo (Va) | 5000 | |||
| 3 | Interés (Tasa) | 30% | |||
| 4 | Plazo (Nper) | 10 | |||
| 5 | Cuota | =PAGO(B3/12;B4;B2) | |||
| 6 | Nº de Cuota | Interés | Amortización | Cuota | Saldo |
| 7 | =SI(B4>=1;1;"") | =PAGOINT($B$3/12;A7;$B$4;$B$2) | =PAGOPRIN($B$3/12;A7;$B$4;$B$2) | =SUMA(B7:C7) | =B2+C7 |
| 8 | =SI(A7<$B$4;A7+1;"") | -114 | -457 | -571 | =E7+C8 |
| 9 | 3 | -102 | -469 | -571 | 3627 |
| 10 | 4 | -91 | -481 | -571 | 3147 |
| 11 | 5 | -79 | -493 | -571 | 2654 |
| 12 | 6 | -66 | -505 | -571 | 2149 |
| 13 | 7 | -54 | -518 | -571 | 1632 |
| 11 | 8 | -41 | -531 | -571 | 1101 |
| 14 | 9 | -28 | -544 | -571 | 557 |
| 15 | 10 | -14 | -557 | -571 | -0 |
Como pueden observar aquí usamos la función lógica "SI" y todas las funciones que ya hemos visto a lo largo del curso.
También tenemos los sujetadores (Ej. $A$7) que sirven para "sujetar" los datos constantes, ya que al copiar algunas formulas los datos se mueven y crean errores, hay radica la importancia de los sujetadores.
Amortización por el Sistema Americano
En la Amortización por el Sistema Americano los intereses son constantes y pago del capital único (en la ultima cuota), por lo que, las cuotas varia solo en la ultima
Ej.
| Cuota Nº | Interés | Amortización | Cuota |
| 1 | 50 | 50 | |
| 2 | 50 | 50 | |
| 3 | 50 | 50 | |
| 4 | 50 | 50 | |
| 5 | 50 | 1000 | 1050 |
Caso Práctico
Ana López solicita un préstamo del Banco de la República de Luque, el monto es de $1000, le informan que el interés será de 36% anual y en 4 cuotas iguales (Sistema Alemán), cual es el monto de la cuota?
| A | B | C | D | E | |
| 1 | Préstamo (Va) | 1000 | |||
| 2 | Interés (Tasa) | 36% | |||
| 3 | Plazo (Nper) | 4 | |||
| 4 | |||||
| 5 | Cuota Nº | Interés | Amortización | Cuota | Saldo |
| 6 | 1 | =B1*(B2/12) | 0 | 30 | 1000 |
| 7 | 2 | 30 | 0 | 30 | 1000 |
| 8 | 3 | 30 | 0 | 30 | 1000 |
| 9 | 4 | 30 | 1000 | 1030 | 0 |
Como veras el interés es anual, por lo tanto dividimos entre 12, este Sistema no termina aquí.
Por convenio entre las partes (Banco-Cliente) el cliente puede depositar en una cuenta de ahorro algún monto para que al momento de la cancelación de la deuda no sea tan forzosa la cuota final, por ese ahorro el cliente recibe también un interés, que por razón obvias, nunca va a ser igual que al que él paga por el dinero prestado.
O sea que, al momento de cancelar la deuda, el cliente amortiza su préstamo con el ahorro que realizo en el banco, más los intereses que este genero y si este ahorro no es suficiente tendrá completar el faltante.
martes, 25 de noviembre de 2008
Amortización por el Sistema Alemán
Ej.
| Cuota Nº | Interés | Amortización | Cuota |
| 1 | 50 | 50 | 100 |
| 2 | 40 | 50 | 90 |
| 3 | 30 | 50 | 80 |
| 4 | 20 | 50 | 70 |
| 5 | 10 | 50 | 60 |
Caso Práctico
Pedro Rios solicita un préstamo del Banco de la República de Luque, el monto es de $1000, le informan que el interés será de 36% anual y en 4 cuotas iguales (Sistema Alemán), cual es el monto de la cuota?
| A | B | |
| 1 | Préstamo (Va) | 1000 |
| 2 | Interés (Tasa) | 36% |
| 3 | Plazo (Nper) | 4 |
| 4 | Interés | =B1*(B2/12) |
| 5 | Capital | =1000/B3 |
| 6 | Cuota | =B4+B5 |
Si procediste correctamente:
El interés es 30
El Capital es 250
El Monto de la Cuota es 280
Como veras la tasa de interés la volvimos a dividir entre 12, por lo que el anual.
Para el calculo de la segunda cuota debemos restas la amortización de 250 de la primera y así obtendremos el interés que seguro va a ser mas bajo que el primero
| A | B | |
| 1 | Préstamo (Va) | =1000-250 |
| 2 | Interés (Tasa) | 36% |
| 3 | Plazo (Nper) | 4 |
| 4 | Interés | =B1*(B2/12) |
| 5 | Capital | =1000/B3 |
| 6 | Cuota | =B4+B5 |
Si procediste correctamente:
El interés es 22,50
El Capital es 250
El Monto de la Cuota es 272,50
Y así sucesivamente hasta cancelar el préstamo.
Amortizaciones por el Sistema Francés
En la Amortización por el Sistema Francés los intereses son decrecientes y pago del capital es creciente, pero manteniendo las cuotas constante.
Ej.
| Cuota Nº | Interés | Amortización | Cuota |
| 1 | 80 | 20 | 100 |
| 2 | 70 | 30 | 100 |
| 3 | 60 | 40 | 100 |
| 4 | 50 | 50 | 100 |
| 5 | 40 | 60 | 100 |
Caso Práctico
Juan González solicita un préstamo del Banco Real de Luque, el monto es de $1000, le informan que el interés será de 36% anual y en 4 cuotas iguales (Sistema Francés), cual es el monto de la cuota?
| A | B | |
| 1 | Préstamo (Va) | 1000 |
| 2 | Interés (Tasa) | 36% |
| 3 | Plazo (Nper) | 4 |
| 4 | Cuota | =PAGO(B2/12;B3;B1) |
Si procediste correctamente el resultado es -269,03.
Es negativo por que el monto del préstamo es positivo y por lo tanto al ir pagado hay que restar del mismo. Y B2 que es el interés porque se divide entre 12, es por que el interés es anual, si es semestral hay que dividirlo entre 2, cuatrimestral entre 3 y bimestral entre 6.
Pero como saber cuanto de interés y cuanto de capital estamos pagando?
Para realizar este calculo primero tienes que hacer Click en Herramientas, Click en Complementos, marcas la opción Herramientas para Análisis y Click en Aceptar.
Ahora podemos calcular cuanto es interés de la cuota.
| A | B | |
| 1 | Préstamo (Va) | 1000 |
| 2 | Interés (Tasa) | 36% |
| 3 | Plazo (Nper) | 4 |
| 4 | Cuota | -269,03 |
| 5 | Numero de Cuota (periodo) | 1 |
| 6 | Interés de la Cuota | =PAGOINT(B2/12;B5;B3;B1) |
Si procediste correctamente el resultado es -30,00
Acuérdate siempre el interés es divido 12, por que el anual. Ahora puedes cambiar el "Numero de Cuota" con lo que cambiara el monto del "Interés de la Cuota" ya que en el Sistema Francés las cuotas decrecen.
Calculemos cuanto amortizamos el capital
| A | B | |
| 1 | Préstamo (Va) | 1000 |
| 2 | Interés (Tasa) | 36% |
| 3 | Plazo (Nper) | 4 |
| 4 | Cuota | -269,03 |
| 5 | Numero de Cuota (periodo) | 1 |
| 6 | Interés de la Cuota | -30,00 |
| 7 | Capital de la Cuota | =PAGOPRIN(B2/12;B5;B3;B1) |
Si procediste correctamente el resultado es -239,03
Por lo que la suma del capital + interés = 269,03 que es el monto de la cuota constante.
lunes, 24 de noviembre de 2008
Funciones útiles
Entre las matemáticas esta la conocida función =Suma(desde:hasta), esta es una de las más utilizadas e incluso existe un icono de Autosuma, esta permite realizar como su nombre lo dice suma automática, solo debemos posicionarnos en la celda que deseamos lleve el resultado de la suma y hacer doble Click sobre el icono.
La función lógica que utilizaremos a lo largo del curso es la función =SI, esta actúa de la siguiente manera:
=si(prueba lógico - comprueba si esta se cumple la condición - ejemplo: 2>3; si esto es cierto entonces dar` VERDADERO; sino dará FALSO)
Ejemplo, en
A1 escribimos el 2
B1 escribimos el 3
C1, escribimos la función =si(a1>b1;"Bien";"Que mal")
El resultado será Que mal, ya que 2 no es mayor que 3
Si cambiamos el valor de la celda A1, por una mayor a 3, ej. 4 o 9, cambiara el resultado, y dirá Bien.
La función de Búsqueda que también usaremos mas adelante en los próximos capítulos es BUSCARV, esta función busca el valor de una celda dada, en una base de datos, para lo cual tenemos que indicarle la columna en la que esta el dato que queremos, si este dato queremos exacto debemos usar un ordenador, que en este caso será la palabra FALSO, si solo queremos un dato aproximado usaremos la palabra VERDADERO.
Para usar la función BUSCARV, debemos tener una base de datos que tiene que tener un primera columna de código numérico y de ser posible en una hoja separada. Ej.
En la Hoja1, tenemos la siguiente Base de Datos de Empleados
| A | B | C | D | |
| 1 | Código | Nombre | Apellido | Edad |
| 2 | 1000 | ANA | LOPEZ | 32 |
| 3 | 1001 | JOSE | ROA | 33 |
| 5 | 1002 | PEDRO | TORRES | 20 |
| 6 | 1003 | MARIA | RIOS | 18 |
| 7 | 1004 | LUIS | ROJAS | 35 |
| 8 | 1010 | ANGEL | GOMEZ | 22 |
En la Hoja2 escribiremos la función BUSCARV, para buscar un "nombre" en nuestra base de datos, pero primero te explicare como funciona:
Escribimos =BUSCARV abrimos paréntesis (indicamos la celda en la cuan esta el código que vamos a buscar, luego un punto y coma (;) técnicamente llamado separador de lista. Después del ; indicamos donde esta la base de datos, otro ; e indicamos el numero de columna en la cual esta el dato que queremos estirar y para culminar otro; y escribimos la palabra FALSO y cerramos el paréntesis.)
| A | B | C | |
| 1 | Código | Nombre | Apellido |
| 2 | 1001 | =BUSCARV(A2;Hoja1!$A$1:$D$8;2;FALSO) | |
| 3 |
Como vez en la celda A2 esta el código de la persona que queremos obtener su nombre.
En la celda B2 escribimos el nombre de la función siempre precedida por el signo "=" luego, abrimos paréntesis e indicamos la celda (A2)que contiene el valor a buscar; marcamos la solapa de la Hoja1, marcamos toda la base de datos, presionamos la tecla F4 para poner los sujetadores; indicamos que nuestro dato "nombre" esta en la columna "2"; escribimos el ordenador "FALSO" y cerramos paréntesis.
Si realizamos bien el procedimiento deberá aparecer el nombre JOSE.
Estas funciones que vimos en este capitulo, las utilizaremos a lo largo de nuestro curso.
Ejemplo varias funciones simultaneas
En España desde hace un tiempo se modificó el DNI, número que nos identifica a cada uno de nosotros por el NIF. Este nuevo número incorpora una letra la cual sirve para identificar que el número es correcto y corresponde a la relación número y letra.
Para conseguir la letra del NIF a partir del DNI, simplemente se tiene que realizar un cálculo matemático y conseguir la letra de una lista de valores utilizando el número conseguido del cálculo como índice.
Para esta práctica solamente necesitaremos que el usuario escriba su DNI en una celda, por ejemplo la C5, y calcularemos la letra n la C7.
En un principio nos interesa obtener a partir del número de DNI un índice que nos marcará la posición de una lista de letras. Este índice lo obtendremos con el Residuo del número del DNI entre 23. Según el número obtenido nos situaremos en la posición de la tabla para obtener la letra del NIF.
La función con la que trabajaremos será la siguiente: =ELEGIR(RESIDUO(C5;23)+1;"T";"R";"W";"A";"G";"M";"Y";"F";"P";"D";"X";"B";"N"; "J";"Z";"S";"Q";"V";"H";"L";"C";"K";"E")
La función residuo nos dará el resto de dividir el DNI por 23. A este resultado le sumamos 1, ya que uno de los valores resultados que podremos obtener sería 0 y la función Elegir empieza a contar el primer registro en la posición 1.
De esta forma la función elegir, nos devolverá una letra la cual nos indica el índice según el residuo.
En estas dos últimas lecciones hemos visto como trabajar con funciones dentro de funciones. Observa la importancia de los paréntesis para trabajar con ellas y también la existencia de las separaciones entre argumentos.
domingo, 23 de noviembre de 2008
Ejemplo de funciones Tiempo Internet
Vamos a realizar una pequeña práctica con la que podremos ver como trabajar con varias funciones dentro de la misma celda.
En esta práctica vamos a calcular, lo que desde un tiempo se está llamando "Tiempo Internet". El Tiempo Internet es una idea de la empresa Swatch la cual quiere convertir el día en una división de 1000 .beats, en lugar de utilizar horas, minutos y segundos. De esta forma el día empieza en el .beat 000 y termina en el 1000. A estos números se el antepone el signo @.
Veamos como podemos calcular el momento en el que nos encontramos en un .beat con una pequeña función en Excel.
Para empezar tenemos que tener presente y realizando una simple regla de tres que 1 Swatch .beat equivale a 1 minuto y 26,4 segundos, lo que es igual a 86,4 segundos.
En la celda C9 introduciremos la función =AHORA() la cual nos devolverá la hora actual del sistema. En la celda D9 introduciremos la función ="@"&ENTERO((HORA(C9)*60*60+MINUTO(C9)*60)/86,4)
Veamos la explicación de esta función. Partiendo de la hora actual, lo que nos interesa es pasar este momento a segundos. Para ello sólo tendremos que pasar las horas y los minutos a segundos, simplemente deberemos multiplicar las horas por 120 y los minutos por 60.
Para conseguir la hora de la función AHORA(), tendremos que utilizar la función HORA() y dentro del argumento de esta pondremos la celda C9, donde está la hora actual del sistema.
Para conseguir los minutos utilizaremos la función MINUTO() y dentro del argumento pondremos la celda C9.
Una vez tenemos pasad la hora actual a segundos , simplemente tendremos que dividir por 86,4 y quedarnos con el número entero. Para ello utilizaremos la función ENTERO().
Para terminar de redondear la función y tener la presentación correcta de lo que es el Tiempo Internet tendremos que hacer que delante del número aparezca el signo @, para ello escribiremos @ entre comillas y a continuación el sigo de unión o concatenación &.
Redondear.mas - Truncar
Redondear.mas
Estructura: REDONDEAR.MAS(Valor; Número decimales)
Ejemplo: Pongamos el mismo ejemplo: =REDONDEAR(4,6365;2) en este caso como resultado tendremos un valor con 2 decimales, pero redondeado hacía arriba: 4,64
Truncar
Con esta función convertiremos un número decimal a uno entero quitando los números decimales. También podemos ver una cantidad de decimales que deseemos, pero en ningún momento redondea, simplemente recorta.
Estructura: TRUNCAR(Valor; Número decimales)
Ejemplo: Si escribimos la función =TRUNCAR(4,56) obtendremos como resultado: 4. Si tenemos la función =TRUNCAR(4,56;1), obtendremos como resultado 4,5 observa que no se ha producido ningún tipo de redondeo.
sábado, 22 de noviembre de 2008
Aleatorio - Redondear - Redondear.menos
Aleatorio
Estructura: ALEATORIO()
Ejemplo: Si deseamos encontrar un valor aleatorio entre 1 y 10, deberemos escribir esta función: =ENTERO(ALEATORIO()*10)+1. Tenemos que tener en cuenta que la función aleatorio sólo nos encuentra valores decimales entre el 0 y el 1. Si lo multiplicamos por 10, de esta forma podremos tener valores entre el 0 y el 9, al sumarle uno obtendremos valores entre el 1 y el 10. Para quitar los decimales utilizamos la función Entero.
Redondear
Nos devuelve un número con una cantidad de decimales que nosotros le indiquemos. Este valor estará redondeado.
Estructura: REDONDEAR(Valor; Número decimales)
Ejemplo: Si escribimos esta función: =REDONDEAR(4,6365;2) obtendremos como resultado: 4,64. Observa que el tercer seis ha hecho redondear el 3 a un 4. Es importante hacer notar que esta función recorta decimales y a la vez redondea.
Redondear.menos
Esta función realiza el mismo "trabajo" que la función anterior, pero redondea hacia el valor menor, no redondea a la alza.
Estructura: REDONDEAR.MENOS(Valor; Número decimales)
Ejemplo: Pongamos el mismo ejemplo que el caso anterior: =REDONDEAR(4,6365;2) en este caso como resultado también tendremos un valor con 2 decimales, pero esta vez tendremos como resultado: 4,63.