Manual Microsoft Excel

Universidad Estatal de Bolívar

Maestría en Gerencia Educativa Ing. Gina Valencia M. 32

Introducción. Una hoja de cálculo es una aplicación que permite la introducción de datos y la obtención de

resultados a partir de su análisis, de forma que nos ayuden a la hora de tomar decisiones en la empresa.

La posibilidad de incorporar fórmulas complejas, de representar gráficamente los datos introducidos y de realizar el análisis de qué sucedería si variamos algún parámetro de nuestro

modelo, hacen de una hoja de cálculo una herramienta indispensable en entornos económicos o administrativos.

Excel 2007 es una hoja de cálculo potente y sencillo. Su principal cualidad es la de incorporar elementos que permiten un análisis de los datos desde casi cualquier punto de vista sin que ello

implique dificultad en su manejo. Tareas como la creación de presupuestos, cálculos de facturas, impuestos y otras más complejas, como el análisis de inventarios, análisis de ventas o análisis

estadísticos son más sencillas en este entorno.

Interfaz de usuario orientada a los resultados

La nueva interfaz de usuario

orientada a los resultados facilita el trabajo en Microsoft Office Excel. Los

comandos y funciones que solían

estar ocultos tras complejos menús y barras de herramientas ahora son

más fáciles de encontrar en las fichas orientadas a tareas que contienen

grupos lógicos de comandos y

características. Muchos cuadros de diálogo han sido reemplazados por

galerías desplegables que muestran las opciones disponibles. Además, se

ofrece información descriptiva sobre herramientas y vistas previas de

ejemplo para ayudarle a elegir la

opción adecuada.

Independientemente de la actividad que esté llevando a cabo en la nueva interfaz de usuario, ya sea aplicar formato a datos o analizarlos, Excel presenta las herramientas que resultan más útiles

para completar dichas tareas correctamente.

Más filas y columnas y otros límites nuevos

Para permitirle explorar grandes cantidades de datos en hojas de cálculo, Office Excel 2007 admite

hasta 1 millón de filas y 16.000 columnas por hoja de cálculo. Específicamente, la cuadrícula de Office Excel 2007 tiene 1.048.576 filas por 16.384 columnas, lo que ofrece un Office

Excel2007 admite asimismo hasta 16 millones de colores.

U N I D A D



Personalizar la barra de herramientas de acceso rápido

La barra de herramientas de acceso rápido es una barra de herramientas personalizable que

contiene un conjunto de comandos independientes de la ficha que se esté mostrando. Puede agregar a esta barra botones que representen comandos y moverla a dos posibles ubicaciones.

Mover la Barra de herramientas de acceso rápido

La Barra de herramientas de acceso rápido es una barra de herramientas personalizable que contiene un conjunto de comandos independientes de la ficha que se está mostrando.

La Barra de herramientas de acceso rápido puede ubicarse en uno de estos dos lugares:

Esquina superior izquierda junto al botón de

Microsoft Office (ubicación predeterminada)

Debajo de la Cinta de opciones

Si no desea que la Barra de herramientas de acceso rápido se muestre en su ubicación actual,

puede moverla a la otra ubicación. Si observa que la ubicación predeterminada situada junto al

botón de Microsoft Office está demasiado lejos del área de trabajo para que su uso sea cómodo, quizá desee acercarla al área de trabajo. La ubicación situada debajo de la Cinta de

opciones invade el área de trabajo. Por lo tanto, si desea maximizar el área de trabajo, puede que desee mantener la Barra de herramientas de acceso rápido en su ubicación predeterminada.

Rellenar la celda activa con el contenido de una celda adyacente

1. Seleccione una celda vacía debajo, a la derecha, por encima o a la izquierda de la celda que contiene los datos que va a utilizar para rellenar dicha celda.

2. En la ficha Inicio, en el grupo Modificación, haga clic en Rellenar y, a

continuación, en Abajo, Derecha, Arriba o Izquierda.

Arrastrar el controlador de relleno para rellenar datos en celdas adyacentes

1. Seleccione las celdas que contienen los datos con los que va a rellenar las celdas adyacentes.

2. Arrastre el controlador de relleno por las celdas que desea rellenar.

3. Para elegir la forma de rellenar la selección, haga clic en Opciones de autorrelleno y, a continuación, haga clic en la opción que desea usar.

NOTA Si arrastra el controlador de relleno hacia arriba o hacia la izquierda de una selección y

se detiene en las celdas seleccionadas sin pasar por la primera columna o por la fila superior de la selección, Excel eliminará los datos que haya dentro de la selección. Deberá arrastrar el

controlador de relleno fuera del área seleccionada antes de soltar el botón del mouse (ratón).

Rellenar fórmulas en celdas adyacentes

1. Seleccione la celda que contiene la fórmula con la que desea rellenar las celdas adyacentes.

2. Arrastre el controlador de relleno (control de relleno: pequeño cuadrado negro situado en la esquina superior derecha de la selección. Cuando se sitúa el puntero del mouse (ratón) sobre

el controlador de relleno, el puntero cambia a una cruz negra.) por las celdas que desea rellenar.



3. Para elegir la forma de rellenar la selección, haga clic en Opciones de autorrelleno y, a continuación, haga clic en la opción que desea usar.

Rellenar una serie de números, fechas u otros elementos de serie integrados

Con el controlador de relleno. Cuando se sitúa el puntero del mouse (ratón) sobre el controlador

de relleno, el puntero cambia a una cruz negra. Puede llenar rápidamente las celdas de un rango

con una serie de números o fechas, o con una serie integrada para días, días de la semana, meses o años.

1. Seleccione la primera celda del rango que desea rellenar.

2. Escriba el valor inicial de la serie. 3. Escriba un valor en la siguiente celda para establecer un modelo.

Por ejemplo, si desea utilizar la serie 1, 2, 3, 4, 5,..., escriba 1 y 2 en las primeras dos celdas. Si desea usar la serie 2, 4, 6, 8,..., escriba 2 y 4. Si desea que la serie sea 2, 2, 2, 2,..., puede dejar

en blanco la segunda celda. Otros ejemplos de series que se puede rellenar

Selección inicial Serie extendida

1, 2, 3 4, 5, 6,...

9:00 10:00, 11:00, 12:00,...

lun mar, mié, jue,...

Lunes Martes, miércoles, jueves,...

ene feb, mar, abr,...

ene, abr jul, oct, ene,...

Ene-99, Abr-99 jul-99, oct-99, ene-00,...

1999, 2000 2001, 2002, 2003,...

Trim3 (o T3 o Trimestre3) Trim4, Trim1, Trim2,...

texto1, textoA texto2, textoA, texto3, textoA,...

1er período 2do período, 3er período,...

Producto 1 Producto 2, Producto 3,...

4. Seleccione la celda o las celdas que contengan los valores iniciales.

5. Arrastre el controlador de relleno por el rango que desea rellenar.

Para rellenar en orden ascendente, arrastre hacia abajo o hacia la derecha. Para rellenar en orden descendente, arrastre hacia arriba o hacia la izquierda.

Insertar o eliminar celdas, filas y columnas

Puede insertar celdas en blanco por encima o a la izquierda de la celda activa, desplazando hacia abajo las demás celdas de la misma columna o hacia la derecha las de la misma fila. Asimismo,

puede insertar filas por encima de una fila y columnas a la izquierda de una columna. También puede eliminar celdas, filas y columnas.

Insertar celdas en blanco en una hoja de cálculo

1. Seleccione la celda o el rango (rango: dos o más celdas de una hoja. Las celdas de un rango pueden ser adyacentes o no adyacentes.) de celdas en las que

desee insertar las nuevas celdas en blanco. Seleccione el mismo número de



celdas que desea insertar. Por ejemplo, para insertar cinco celdas en blanco, debe seleccionar cinco celdas.

2. En la ficha Inicio, en el grupo Celdas, haga clic en la flecha situada junto a Insertar y, después, en Insertar celdas.

Eliminar celdas, filas o columnas

1. Seleccione las celdas, filas o columnas que desea eliminar. 2. En la ficha Inicio, en el grupo Celdas, siga uno de los procedimientos

siguientes:

Para eliminar celdas seleccionadas, haga clic en la flecha que aparece junto a Eliminar y,

a continuación, haga clic en Eliminar celdas. Para eliminar filas seleccionadas, haga clic en la flecha que aparece junto a Eliminar y, a

continuación, haga clic en Eliminar filas de hoja.

Para eliminar columnas seleccionadas, haga clic en la flecha que aparece junto a Eliminar

y, a continuación, haga clic en Eliminar columnas de hoja.

Ajustar el ancho de columna

1. Haga clic en la celda cuyo ancho de columna desea cambiar.

2. En la ficha Inicio, en el grupo Celdas, haga clic en Formato. 3. En Tamaño de celda, realice uno de estos procedimientos:

Para ajustar todo el texto de la celda, haga clic en Ajustar ancho de columna.

Para especificar un ancho de columna mayor, haga clic en Ancho de columna y, a

continuación, escriba el ancho que desea en el cuadro Ancho de columna.

Ajustar el alto de fila

1. Seleccione las celdas cuyo alto de fila desea ajustar. 2. En la ficha Inicio, Celdas, clic en Formato.

3. En Alto de fila, realice uno de estos procedimientos:

Para ajustar automáticamente el alto de fila, haga clic en Autoajustar alto de fila.

Para especificar un alto de fila, haga clic en Alto de fila y, a continuación, especifique el

valor deseado en el cuadro Alto de fila.

Ajustar ancho de columna y alto de fila al mismo tiempo

Otra forma de ajustar automáticamente el alto de fila o ancho de

columna, simplemente se lo hace desde las etiquetas, activando el

botón seleccionar todo



Una vez seleccionado, y arrastrado en ambos casos, las filas y columnas quedaran del tamaño especificado en la selección. Desactive la selección haciendo clic en cualquier celda

Ajustar texto automáticamente

1. En la hoja de cálculo, seleccione las celdas a las que desea dar formato.

2. En la ficha Inicio, en el grupo Alineación, haga clic en

Ajustar texto . Deshacer, rehacer o repetir una acción

En Microsoft Office Excel se puede deshacer y rehacer hasta 100 acciones, incluso después de

haber guardado la hoja de cálculo. Puede repetir acciones tantas veces como desee.

Deshacer la última acción o acciones realizadas

Para deshacer una acción, siga uno o varios de estos procedimientos:

Haga clic en Deshacer en la barra de herramientas de acceso rápido.

Rehacer las acciones que se han deshecho

Para rehacer una acción deshecha, haga clic en Rehacer en la barra de herramientas de acceso rápido.

Método abreviado de teclado También puede presionar CTRL+Y.

Combinar celdas o dividir celdas combinadas

Cuando se combinan dos o más celdas verticales u horizontales adyacentes, se convierten en una

celda de gran tamaño que se muestra en varias columnas o filas. El contenido de una de las celdas aparece en el centro de la celda combinada (celda combinada: celda creada al combinar dos o más

celdas seleccionadas. La referencia de celda para una celda combinada es la celda superior

izquierda del rango original seleccionado.), como se puede ver en el ejemplo siguiente.

Una celda combinada se puede dividir de nuevo en celdas independientes, pero no se puede dividir

una sola celda que no se haya combinado.

Combinar celdas

1. Seleccione las celdas adyacentes que desee combinar.

2. En la ficha Inicio, en el grupo Alineación, haga clic en Combinar y centrar.

NOTA Si el botón Combinar y centrar

no está disponible, es posible que las celdas seleccionadas estén en modo de edición. Para cancelar este modo, presione ENTRAR.

3. Para cambiar la alineación del texto de la celda combinada, seleccione la celda y haga clic en cualquier botón de alineación del grupo Alineación de la ficha Inicio.

Dividir una celda combinada

1. Seleccione la celda combinada. Al seleccionar una celda combinada, también aparecerá

seleccionado el botón Combinar y centrar en el grupo Alineación de la ficha Inicio.

2. Para dividir la celda combinada, haga clic en Combinar y centrar .



El contenido de la celda combinada aparecerá en la celda superior izquierda del rango de

celdas de divididas.

Combinar o dividir el contenido de celdas

Se puede combinar el contenido de varias celdas y mostrarlo en una sola. Asimismo, el contenido

de una celda se puede dividir y distribuir como partes individuales en otras celdas.

Combinar el contenido de varias celdas en una sola

Para combinar el texto de varias celdas en una sola se puede utilizar una fórmula con el operador

"y" comercial (&).

1. Seleccione la celda en la que desea combinar el contenido de otras celdas.

2. Para empezar la fórmula, escriba =( 3. Seleccione la primera celda que incluya el texto que desea combinar, escriba &" "& (con un

espacio entre las comillas) y, a continuación, seleccione la celda siguiente que contenga el texto que vaya a combinar, la fórmula le quedaría así =(A3&" "&A7)

4. Para ver el resultado de la fórmula, presione ENTRAR.

Sugerencia También puede utilizar la función CONCATENAR para combinar el texto de varias celdas en una sola.

Agregar, modificar o eliminar un comentario

En Microsoft Office Excel puede agregar una nota a una celda insertando un comentario. Puede

modificar el texto de los comentarios y eliminar los comentarios que ya no necesite.

Para ello debe hacer clic en Revisar, comentarios, nuevo comentario y escriba el comentario sobre la celda, o el trabajo, este no se visualiza estando fuera de dicha celda donde coloco el comentario.

Desde la misma ficha, puede modificar o eliminar el comentario

Mover o copiar celdas y contenido de las celdas

Utilizando los comandos Cortar, Copiar y Pegar de Microsoft Office Excel, puede mover o copiar celdas completas o su contenido. También puede copiar contenido o atributos específicos de las

celdas. Por ejemplo, puede copiar el valor resultante de una fórmula sin copiar la propia fórmula, o

bien, copiar sólo la fórmula.

1. En la ficha Inicio, en el grupo Portapapeles, siga uno de los procedimientos siguientes:

Para mover celdas, haga clic en Cortar . Método abreviado de teclado También puede presionar CTRL+X.

Para copiar celdas, haga clic en Copiar . Método abreviado de teclado También puede presionar CTRL+C.

2. Seleccione la celda donde va a realizar el pegado.

3. En la ficha Inicio, en el grupo Portapapeles, haga clic en Pegar . Método abreviado de teclado También puede presionar CTRL+V y el contenido será

pegado automáticamente.

ms-help://MS.EXCEL.12.3082/EXCEL/content/HP10062562.htm



Mover o copiar el contenido de una celda

1. Haga doble clic en la celda que contiene los datos que desea mover o copiar.

2. En la celda, seleccione los caracteres que desea mover o copiar. 3. En la ficha Inicio, en el grupo Portapapeles, siga uno de los

procedimientos siguientes:

Para mover la selección, haga clic en Cortar .

Método abreviado de teclado También puede presionar CTRL+X.

Para copiar la selección, haga clic en Copiar . Método abreviado de teclado También puede presionar CTRL+C.

4. En la celda, haga clic donde desee pegar los caracteres o haga doble clic en otra celda para

mover o copiar los datos.

5. En la ficha Inicio, en el grupo Portapapeles, haga clic en Pegar . Método abreviado de teclado También puede presionar CTRL+V.

6. Presione ENTRAR.

Copiar sólo valores de celdas, formatos de celdas o fórmulas

Al pegar datos copiados, puede hacer lo siguiente:

Convertir las fórmulas de la celda en valores calculados sin sobrescribir el formato existente.

Pegar sólo el formato de celdas, como el color de fuente o el color de relleno (pero no el

contenido de las celdas). Pegar sólo las fórmulas (pero no los valores calculados).

1. Seleccione la celda o el rango de celdas que contiene los valores, formatos

de celda o fórmulas que desea copiar.

2. En la ficha Inicio, en el grupo Portapapeles, haga clic en Copiar . Método abreviado de teclado También puede presionar CTRL+C.

3. Seleccione la celda superior izquierda del área de pegado o la celda en la que desea pegar el

valor, el formato de celda o la fórmula.

4. En la ficha Inicio, en el grupo Portapapeles, haga clic en la flecha situada bajo Pegar y, a continuación, siga uno de los procedimientos siguientes:

Para pegar sólo los valores, haga clic en Pegar valores. Para pegar sólo los formatos de celda, haga clic en Pegado especial y, a continuación,

haga clic en Formatos bajo Pegar.

Para pegar sólo las fórmulas, haga clic en Fórmulas.

NOTA Si las fórmulas copiadas contienen referencias de celda relativas, Excel ajusta las

referencias (y las partes relativas de las referencias de celda mixtas) en las fórmulas duplicadas. Por ejemplo, supongamos que la celda B8 contiene la fórmula =SUMA(B1:B7). Si copia la fórmula

en la celda C8, la fórmula duplicada hará referencia a las celdas correspondientes de esa columna:

=SUMA(C1:C7). Si las fórmulas copiadas contienen referencias de celda absolutas (referencia de celda absoluta: en una fórmula, dirección exacta de una celda, independientemente de la posición

de la celda que contiene la fórmula. Una referencia de celda absoluta tiene la forma $A$1.), las referencias de las fórmulas duplicadas no cambian. Si no obtiene los resultados deseados, también

puede cambiar las referencias de las fórmulas originales a referencias de celda relativas o

absolutas y volver a copiar las celdas.



Insertar Formas

1. Haga clic en la ficha Insertar, luego en la opción Formas

2. Seleccione la forma que desea según su necesidad

Agregar o eliminar un relleno de sombra o un efecto de sombra

Puede cambiar el aspecto de una forma cambiando su relleno o

agregando efectos, por ejemplo, sombras, resplandores, reflejos, bordes suaves, biseles y giros tridimensionales (3D).

Agregar o cambiar un relleno de forma

1. Haga clic en la forma a la que desea agregar un relleno. Para agregar el mismo relleno a varias formas, haga clic en la primera forma y, a continuación, presione la tecla MAYÚSCULAS

y manténgala presionada mientras hace clic en las demás formas.

2. En Herramientas de dibujo, en la ficha Formato, en el grupo Estilos de forma, haga clic

en la fecha situada junto a Relleno de forma y siga uno de los procedimientos siguientes:

Para agregar o cambiar un color de relleno, haga clic en el color que desee; si no desea

ningún color, haga clic en Sin relleno.

Para cambiar a un color que no está en los colores del tema (colores del tema: conjunto de colores que se utiliza en un archivo. Los colores del tema, las fuentes del tema y los efectos del tema

constituyen un tema.), haga clic en Más colores de relleno y haga clic en el color que desee en

la ficha Estándar o mezcle los suyos propios en la ficha Personalizado. Los colores personalizados y los colores de la ficha Estándar no se actualizan si después cambia el tema del

documento.

Agregar o cambiar un efecto de forma

1. Haga clic en las formas a la que desea agregar un efecto.

2. En Herramientas de dibujo, en la ficha Formato, en el grupo Estilos de forma, haga clic

en Efectos de forma y siga uno de los procedimientos siguientes:

Para agregar o cambiar una combinación de efectos integrada, elija Preestablecer y haga

clic en el efecto que desee.



Para personalizar el efecto integrado, haga clic en Opciones 3D y elija las opciones que desee.

Para agregar o cambiar una sombra, elija Sombra y

haga clic en la sombra que desee. Para personalizar la sombra, haga clic en Opciones de

sombra y elija las opciones que desee. Para agregar o cambiar un reflejo, elija Reflejos y haga

clic en la variación de reflejo que desee.

Para agregar o cambiar un resplandor, elija Resplandor y haga clic en la variación que desee.

Eliminar un relleno de forma Para estos programas de versión Office 2007: Excel.

1. Haga clic en la forma de la que desea eliminar un relleno. Para eliminar el mismo relleno de varias formas, haga clic en la primera forma y, a

continuación, presione la tecla MAYÚSCULAS y manténgala presionada mientras hace clic en las demás formas.

2. En Herramientas de dibujo, en la ficha Formato, en el grupo Estilos de formas, haga

clic en la flecha situada junto a Relleno de forma y, a continuación, en Sin relleno.

Eliminar un efecto de forma

Para estos programas de versión Office 2007: Excel

1. Haga clic en la forma de la que desea eliminar el efecto.

Para eliminar el mismo efecto de varias formas, haga clic en la primera forma y, a continuación,

presione la tecla MAYÚSCULAS y manténgala presionada mientras hace clic en las demás formas.

2. En Herramientas de dibujo, en la ficha Formato, en el grupo Estilos de Forma, haga clic

en Efectos de forma y siga uno de los procedimientos siguientes:

Para eliminar una combinación de efectos integrada de la forma, elija Preestablecer y, a

continuación, haga clic en Sin valores predeterminados.

Para eliminar una sombra de la forma, elija Sombra y, después, haga clic en Sin sombra. Para eliminar un reflejo de la sombra, elija Reflejos y, después, haga clic en Sin reflejos.

Para eliminar un resplandor de la forma, elija Resplandor y, después, haga clic en Sin

resplandor. Para eliminar bordes suaves de la forma, elija Bordes suaves y, a continuación, haga clic en

Sin bordes suaves. Para eliminar un borde de la forma, elija Bisel y, después, haga clic en Sin bisel.

Para eliminar de la forma un giro 3D, elija Giro 3D y, a continuación, haga clic en Sin giro.



Gráficos en Excel

En Microsoft Office Excel 2007, es fácil crear gráficos de aspecto profesional. Sólo con seleccionar

un tipo, un diseño y estilo de gráfico (opciones de fácil acceso en la nueva cinta de opciones de Office Excel 2007), obtendrá resultados profesionales inmediatos cada vez que cree un gráfico.

Crear gráficos en Excel

Para crear un gráfico básico en Excel que pueda modificar y dar formato más adelante, debe especificar, en primer lugar, los datos

del gráfico en una hoja de cálculo. A continuación, sólo tiene que seleccionar los datos y elegir el tipo de gráfico que desea utilizar

en la cinta de opciones (ficha Insertar, grupo Gráficos).

Selleccionar los Datos de la hoja de cálculo

Ficha Insertar, grupo Gráfico y elegir el tipo de grafico

Excel ofrece varios tipos de gráficos para mostrar datos ante una audiencia de forma elocuente. Cuando cree un gráfico o modifique uno

existente, puede elegir entre distintos tipos de gráfico (gráfico de

columnas o circular) y subtipos (gráfico de columnas apiladas o gráfico 3D). Puede crear también un gráfico combinado utilizando varios tipos de

gráficos en uno solo.

Ejemplo de gráfico combinado

que usa un tipo de gráfico de

columnas y líneas.

Tipos de gráficos disponibles

Microsoft Office Excel 2007 admite varios tipos de gráficos para ayudarle a mostrar datos de forma

comprensible para su audiencia. Cuando crea un gráfico o cambia uno existente, puede elegir entre una amplia gama de subtipos de gráficos disponibles para cada uno de los tipos de gráficos

siguientes.

Gráficos de columnas

Se pueden trazar datos que se organizan en columnas o filas de una hoja de cálculo en un gráfico de columnas. Este tipo de gráfico es útil para

mostrar cambios de datos en un período de tiempo o para ilustrar comparaciones entre elementos.

En los gráficos de columnas, las categorías

normalmente se organizan en el eje horizontal y los valores en el eje vertical.

Los gráficos de columnas tienen los siguientes

subtipos de gráfico:

Columna agrupada y columna agrupada en 3D

Columna apilada y columna apilada en 3D



Columna 100% apilada y columna 3D apilada 100% Columnas 3D

Cilindro, cono y pirámide

Gráficos de líneas

Se pueden trazar datos que se organizan en columnas o filas de una hoja de cálculo en un gráfico de líneas.

Los gráficos de línea pueden mostrar datos continuos

en el tiempo, establecidos frente a una escala común y, por tanto, son ideales para mostrar tendencias en

datos a intervalos iguales. En un gráfico de líneas, los datos de categoría se distribuyen uniformemente en

el eje horizontal y todos los datos de valor se distribuyen uniformemente en el eje vertical.

Los gráficos de líneas tienen los siguientes subtipos de gráfico:

Línea y línea con marcadores

Línea apilada y línea apilada con

Línea 100% apilada y línea 100% apilada con marcadores Líneas 3D

Gráficos circulares

En un gráfico circular se pueden representar datos contenidos en una columna o una fila de

una hoja de cálculo. Los gráficos circulares

muestran el tamaño de los elementos de una serie de datos (serie de datos: puntos de datos

relacionados que se trazan en un gráfico. Cada serie de datos de un gráfico tiene una trama o

color exclusivo y se representa en la leyenda

del gráfico. Puede trazar una o más series de datos en un gráfico. Los gráficos circulares

sólo tienen una serie de datos.), en proporción a la suma de los elementos. Los puntos de

datos (puntos de datos: valores individuales trazados en un gráfico y representados con barras,

columnas, líneas, sectores, puntos y otras formas denominadas marcadores de datos. Los marcadores de datos del mismo color constituyen una serie de datos.) de un gráfico circular se

muestran como porcentajes del total del gráfico circular.

Piense en utilizar un gráfico circular cuando: Sólo tenga una serie de datos que desee trazar.

Ninguno de los valores que desea trazar son negativos.

Casi ninguno de los valores que desea trazar son valores cero. No tiene más de siete categorías.

Las categorías representan partes de todo el gráfico circular.

Gráficos de barras Se pueden trazar datos que se organizan en

columnas o filas de una hoja de cálculo en un gráfico de barras. Los gráficos de barras muestran

comparaciones entre elementos individuales.

Piense en utilizar un gráfico de barras cuando:



Las etiquetas de eje son largas. Los valores que se muestran son duraciones.

Los gráficos de barras tienen los siguientes subtipos de gráfico:

Barra agrupada y barra agrupada en

Barra apilada y barra apilada en 3D Barra 100% apilada y barra 100% apilada en

Pirámide, cono y cilindro horizontal

Gráficos de área

Se pueden trazar datos que se organizan en columnas o filas de una hoja de cálculo en un

gráfico de área. Los gráficos de área destacan la magnitud del cambio en el tiempo y se pueden

utilizar para llamar la atención hacia el valor total

en una tendencia. Por ejemplo, se pueden trazar los datos que representan el beneficio en el tiempo

en un gráfico de área para destacar el beneficio total. Al mostrar la suma de los valores trazados,

un gráfico de área también muestra la relación de

las partes con un todo.

Los gráficos de área tienen los siguientes subtipos de gráfico: Área y área en 3D

Área apilada y área apilada en 3D Área 100% apilada y área 100% apilada en 3D

Áreas

Gráficos de cotizaciones Se pueden trazar datos

que se organizan en

columnas o filas en un orden específico en

una hoja de cálculo en un gráfico de

cotizaciones. Como su

nombre implica, un gráfico de cotizaciones

se utiliza con mayor frecuencia para mostrar la fluctuación de los precios de las acciones. Sin embargo, este gráfico

también se puede utilizar para datos científicos. Por ejemplo, podría utilizar un gráfico de cotizaciones para indicar la fluctuación de las temperaturas diarias o anuales. Debe organizar los

datos en el orden correcto para crear gráficos de cotizaciones.

Gráficos de superficie

Se pueden trazar datos que se organizan en columnas o filas de una hoja de cálculo en un gráfico

de superficie. Un gráfico de superficie es útil cuando busca combinaciones óptimas entre dos

conjuntos de datos. Como en un mapa topográfico, los colores y las tramas indican áreas que están en el mismo rango de valores.

Puede utilizar un gráfico de superficie cuando ambas categorías y series de datos sean valores

numéricos.



Funciones en Excel

Definición Una función es una fórmula ya preparada por Excel, que permite ahorrar tiempo

y cálculos, y que produce un resultado. Por ejemplo, imaginemos que tenemos que sumar una columna de datos numéricos:

En el ejemplo de la izquierda podríamos colocar en la celda A10 la fórmula: =A3+A4+A5+A6+A7+A8,

pero esto mismo resultaría horrible si en lugar de 5 celdas hubiese que sumar 100.

En lugar de esa fórmula, utilizamos la función =SUMA(A3:A8) que realizará exactamente la misma

operación; sumar el rango de celdas A3:A8.

Las funciones aceptan unos valores (en este caso el

rango de celdas) llamados argumentos.

Sintaxis Observa la sintaxis de una función:

Las funciones las podemos introducir de dos formas:

Mediante teclado

Mediante el asistente para funciones.

MEDIANTE EL TECLADO.

Es decir que las funciones más

comunes se pueden escribir directamente desde el teclado

siempre y cuando el usuario tenga bien claro la sintaxis de

cada función, aquí veremos

varios ejemplos:



Funciones: Promedio, Máxima, Mínima, Moda, Contar, Contar.si y Mediana

Vamos a realizar un nuevo ejercicio que nos servirá para estudiar 5 nuevas funciones de Excel.

Elaboraremos una supuesta tabla con los alumnos de una escuela. Los datos que tendremos son las notas de los tres trimestres. A partir de ahí, realizaremos una serie de cálculos utilizando las

funciones que vamos a estudiar. Vamos primero a ver sus sintaxis, y a continuación su aplicación en el ejemplo:

=PROMEDIO(Número1;Número2;......)

Función que nos devolverá la media

aritmética de los números o el rango encerrado entre paréntesis

Ejemplos:

=PROMEDIO(12;12;13) devolverá 12,33333

=PROMEDIO(A1:D13) devolverá el promedio del

rango A1:D13

=MAX(Números) =MIN(Números)

Estas funciones devuelven los valores máximo y mínimo respectivamente de una lista de números.

=MODA(Números)

Valor que más se repite en un rango



=CONTAR(Rango)

Cuenta las veces que aparece un elemento

númerico en una lista.

=CONTARA(Rango)

Cuenta las veces que aparece un elemento

de texto en una lista

=CONTAR.SI(Rango)

Cuenta las celdas no vacías de un rango

=MEDIANA(Números)

Número que se encuentra en medio de un conjunto de números, es decir, la mitad de los números es mayor que la mediana y la

otra mitad es menor.

A continuación observa la siguiente tabla. Las celdas en color rosa contendrán las fórmulas.

Hemos de calcular lo siguiente:

En la columna E la nota final que será la media de los tres trimestres. En la columna F la evaluación en forma de texto. Debemos utilizar la función =SI para lo

siguiente:

Si la nota media es de 0 a 4,99, aparecerá la palabra Insuficiente Si la nota media es de 5 a 6,99, aparecerá la palabra Aprobado Si la nota media es de 7 a 9,5, aparecerá la palabra Notable Si la nota media es de 9,6 a 10, aparecerá la palabra Excelente



En las celdas inferiores calcularemos la Mediana, Notas máxima y mínima, y moda Finalmente, colocaremos unas celdas que nos informarán de:

El número de alumnos que hay El número de insuficientes, aprobados, notables y excelentes que hay Qué porcentaje representa cada uno de los anteriores

La hoja debe quedar de la siguiente manera:

Comentarios a las fórmulas:

Celda FÓRMULA ACCIÓN

E2 =PROMEDIO(B2:D2) (y copiar hacia abajo) Halla la media de los

números a su izquierda

F2 =SI(E2<4,99;"Insuficiente";SI(E2<6,99;"Aprobado";

SI(E2<9,5;"Notable";SI(E2<10;"Excelente"))))

Comprueba la nota para

colocar un texto

B13, B14,

B15,B16

=MAX(E2:E11) =MIN(E2:E11) =MODA(E2:E11) =MEDIANA(E2:E11)

Halla la nota máxima,

mínima, moda y mediana

de la lista de notas finales

B19 =CONTAR.SI($F$2:$F$11;"Insuficiente") Cuenta el número de

insuficientes. Igual para

las fórmulas de abajo C19 =B19/$F$17 Halla el porcentaje

F17 =CONTARA(A2:A11) Cuenta el número de alumnos de la lista de

nombres

La función =SI( )

Definición y sintaxis La función =SI( ) es una de las más potentes que tiene Excel. Esta función comprueba si se

cumple una condición. Si ésta se cumple, da como resultado VERDADERO. Si la condición no se cumple, da como resultado FALSO. Observa la sintaxis:



=SI(Condición;Verdadero;Falso)

Esta es la forma más simple de representar esta función, porque la misma se puede complicar que

no veas. De momento vamos a proponer un ejemplo:

Observa la hoja de la derecha. Se trata del último ejemplo de la lección anterior. Observa que

hemos añadido nuevas celdas y hemos modificado algunas. Ahora se trata de colocar una función =SI( ) en la celda E15 (celda rosa del descuento).

Ejemplo Vamos a hacer que la factura del ejemplo anterior nos haga un descuento del

10% sólo en el caso de cobrar al contado. La fórmula se colocará en la celda E15 y será la siguiente:

=SI(A17="Contado";E14*10%;0;)

Esta fórmula mirará si en la casilla A17 (celda amarilla) existe la palabra Contado. En tal caso,

ejecutará una fórmula (10% de descuento), en caso contrario, colocará simplemente un cero en la celda E15, es decir, no realizará ningún cálculo. Observa el resultado:

En el siguiente ejemplo vamos a ver una variación de la función =SI

En el ejemplo anterior vimos que la función =SI debía cumplir una condición, que era la de

controlar si en una celda determinada había un texto. Pero, ¿qué pasaría si se tuviesen que cumplir más de una condición? Supongamos que la función =SI debe tener en cuenta dos condiciones.

Estas dos condiciones podrían ser:

Que la función =SI hiciese algo sólo si se tuvieran que cumplir las dos Que la función =SI hiciese algo si se cumpliese una de las dos

Controlaremos una u otra forma con dos operadores lógicos: el Y y el O

La sintaxis de la orden sería la siguiente

=SI(Y(Condición1:Condición2............ Caso en el que se deban cumplir todas las condiciones =SI(O(Condición1:Condición2............Caso que se deba cumplir sólo una

En el siguiente ejemplo hemos diseñado una hoja de control de flujo de caja en un hipotético caso en el que debamos controlar entradas y salidas además del saldo.



En las columnas C y D introduciremos las cantidades según sea un gasto

(extracción) o un ingreso (depósito).

Sería muy fácil colocar en la celda E5 (saldo) la siguiente fórmula: =E4+C5-

D4, que calcularía el saldo anterior, más la cantidad de la celda del depósito,

menos la cantidad de la celda de la

extracción. El problema viene cuando copiamos la fórmula varias celdas hacia

abajo. A partir de la celda del último saldo, siempre nos mostraría el saldo anterior, hubiésemos o no, introducido cantidades en las

celdas de depósito o extracción.

Observa el resultado que se obtendría: Evidentemente, no queda muy estético a la vista. En la

ilustración superior utilizamos la función =SI, la cual ha de

controlar que se cumplan dos

condiciones: que introduzcamos una cantidad en la celda del

depósito o de la extracción. Sólo en uno de los dos casos se

ejecutará la función. De esa

forma, si todavía no hemos introducido nada en las celdas

de la izquierda, la función no se ejecutará. Observa a continuación las partes de la fórmula:

=SI(O La letra O controla que se cumpla una de las dos condiciones (C5>0: Primera condición: que en C5 haya algo mayor de cero, es decir, un número positivo D5>0) Separada por dos puntos, la segunda condición controla lo mismo: que en D5 haya algún

número. ; E4+C5-D5 caso de cumplirse una de las dos condiciones, se ejecutará esta fórmula. ;"") Caso de no cumplirse ninguna condición, no saldrá nada. Las dos comillas quieren decir carácter nulo.

MEDIANTE EL ASISTENTE PARA FUNCIONES Son las mismas funciones con la diferencia que se pueden elegir de entre una lista:

Desde el botón fx de la barra de fórmulas Insertar Función

o Desde el botón Autosuma, más funciones

Luego debe elegir la función que va a utilizar de entre la lista

Una vez que elije la función en este caso SUMA

Realiza el cálculo, señalando la celda, o el rango a

evaluar



Finalmente clic en Aceptar y

sale la respuesta Debe rellenar las celdas

OTRO EJERCICIO DE APLICACION

Utilizando las funciones que correspondan dentro

de la categoría Matemática y

Trigonométricas

Con los resultados que acabamos de obtener,

realice un gráfico tipo barras, identificando

cada una de las operaciones realizadas



COMBINACION DE FUNCIONES ------ EXCEL FINANCIERO

Ejercicio desarrollado vinculando las Funciones Financieras, con Funciones de: Lógica,

e Información respectivamente de Excel.

El ejercicio, referido al ejemplo tratado respecto a un Préstamo Hipotecario, tiene la particularidad

de poder desarrollar todo el cuadro de Rentas hasta un plazo de 30 años, es decir 360 cuotas sin

ingresar valores, e introducirlos, según interesen, los Montos, Intereses, Plazo etc. en su máxima extensión o cualquier plazo intermedio permitiendo analizar la variación de la cuota, interés,

amortización, etc. Su ejecución es posible combinando las Funciones Financieras conocidas con otras Funciones de

Excel, que permiten condicionar los vacíos de celdas evitando que se produzcan errores, y realizar

los cálculos correspondientes al producirse el ingreso de valores en la planilla de entrada de Datos respectiva.

Las Funciones complementarias que se emplearon fueron: Función Lógica SI: de vuelve un único valor si una condición se evalúa como Verdadero y otro

valor si se evalúa como Falso.

Función de Información ESERR: Devuelve Verdadero, cualquier valor de error de tipo #¡NUM! Función de Información ESERROR: Devuelve Verdadero, cualquier valor de error de tipo

#¡VALOR! Tipo y Funciones empleadas para realizar el trabajo

Funciones Financieras PAGO PAGOINT

PAGOPRIN

TASA NPER

Funciones Lógicas SI

Funciones Fecha y Hora FECHA.MES

Funciones de Información ESERR ESERROR

Desarrollo de cada Función empleada

Función PAGO. Calcula el pago de un préstamo basándose en pagos constantes y una tasa de interés constante.

Sintaxis PAGO(tasa;nper;va;vf;tipo) Tasa: es la tasa de interés del préstamo

Nper: es el número total de pagos del préstamo Va: es el valor actual

Vf: es el valor futuro. Si el argumento vf se omite, se asume que es 0 (o el valor futuro de un

préstamo es cero) Tipo: es un número 0 o 1 e indica el vencimiento de pagos

Tipo: 0 al final del periodo Tipo: 1 al inicio del periodo

Observaciones: El pago devuelto incluye el capital y el interés

Ejemplo

A B

1 Préstamo $ 13.000,00

2 Tasa anual 10,50%

3 Cantidad de cuotas (meses) 15

4 Vf 0

5 Tipo 0

6 Cuota -$ 928,57

Celda B6=PAGO(B2/12;B3;B1;B4;B5)



En el caso de producirse el pago al inicio del periodo

A B

1 Préstamo $ 13.000,00

2 Tasa anual 10,50%


4 Vf 0

5 Tipo 1

6 Cuota -$ 920,51

Celda B6=PAGO(B2/12;B3;B1;B4;B5)

Función PAGO para producir un ahorro en un tiempo determinado

La función PAGO, también puede determinar los pagos anuales que deberían efectuarse para producir un ahorro de $ 20.000 en 10 años a una tasa anual del 6,5% de interés en una cuenta

de ahorro

A B

1 Ahorro Monto $ 20.000,00

2 Tasa anual 6,50%

3 Años 10

6 Pagos Mensuales -$ 227,10

Celda B6=PAGO(B2/12;B3*12;B1) Si se depositan $ 227,10, cada mes en una caja de ahorro, que paga el 6,5% de interés, al final de 10 años se abra ahorrado $ 20.000

Función PAGOINT. Calcula el interés pagado en un periodo especificado por una inversión basándose en una tasa de interés constante y pagos en periodos constantes.

Sintaxis PAGOINT(tasa;periodo;nper;va;vf;tipo)

Tasa: es la tasa de interés del periodo Periodo: es el periodo para el que se desea calcular el interés y deben estar entre 1 y el

argumento nper

Nper: es número total de pagos del préstamo Va: es el valor actual de una serie de pagos futuros

Vf : es el valor futuro de una serie de pagos futuros. Si se omite se calcula como cero Tipo: es un numero 0 o 1 e indica el vencimiento de pagos


Ejemplo

A B

1 Préstamo $ 13.000,00

2 Tasa anual 10,50%

3 Calculo interés en cuota n° 1


5 Vf 0

6 Tipo 0

7 Interés -$ 113,75

Celda B7= PAGOINT=(B2/12;B3;B4;B1;B5;B6)

En este caso se puede apreciar en la celda B7 que se calcula la parte correspondiente a intereses

que incluye la cuota calculada anteriormente de $ 928, 57 en el primer pago es decir en el mes 1



A B

1 Préstamo $ 13.000,00

2 Tasa anual 10,50%



5 Vf 0

6 Tipo 0

7 Interés -$ 8,06

En este caso se calcula en la celda B7 los intereses que integran la cuota de pago en el último

mes es decir el mes 15

Función PAGOPRIN

Calcula el pago sobre el capital de una inversión durante un periodo determinado, basándose en una tasa de interés constante y pagos periódicos constantes

Sintaxis PAGOPRIN(tasa;periodo;nper;va;vf;tipo)

Tasa: es la tasa de interés del periodo

Periodo: es el periodo para el que se desea calcular la amortización y deben estar entre 1 y el argumento nper

Nper: es número total de pagos del préstamo Va: es el valor actual de una serie de pagos futuros

Vf: es el valor futuro de una serie de pagos futuros. Si se omite se calcula como cero Tipo : es un numero 0 o 1 e indica el vencimiento de pagos


Ejemplo

A B

1 Préstamo $ 13.000,00

2 Tasa anual 10,50%



5 Vf 0

6 Tipo 0

7 Amortización -$ 814,82

Celda B7= PAGOPRIN=(B2/12;B3;B4;B1;B5;B6)

En este caso se puede apreciar en la celda B7 que se calcula la parte correspondiente a amortización que incluye la cuota calculada anteriormente de $ 928, 57 en el primer pago es decir

en el mes 1

A B

1 Préstamo $ 13.000,00

2 Tasa anual 10,50%



5 Vf 0

6 Tipo 0

7 Amortización -$ 920,51

En este caso se calcula en la celda B7 la amortización que integra la cuota de pago en el último

mes es decir el mes 15



Como puede apreciarse produciendo la suma de amortización e interés en ambos casos se obtiene el valor de la cuota a pagar

$113,75+$814,82=$ 928,57 $8,06+$920,51= $ 928,57

El sistema desarrollado para calcular el préstamo es según el sistema francés donde el valor de la cuota es constante.

Función TASA Calcula la tasa de interés por periodo de una anualidad

Sintaxis TASA(nper;pago;va;vf;tipo;estimar) Nper es él número total de periodos de pago en una anualidad Pago es el pago que se efectúa en cada periodo y que no puede cambiar durante la vida de

anualidad. Generalmente el argumento pago incluye el capital y el interés, pero no incluye ningún otro arancel o impuesto.

Va es el valor actual de la cantidad total de una serie de pagos futuros

Vf es el valor futuro o saldo en efectivo que desea lograr después de efectuar el último pago. Si el argumento vf se omite, se asume que el valor es cero( por ejemplo el valor futuro de un

préstamo es cero) Tipo: es el valor debe ser 0 o 1 e indica el vencimiento de los pagos


Estimar es la estimación de la tasa de interés, si el argumento estimar se omite se supone que es

10% Ejemplo

A B

1 Cantidad de cuotas 15

2 Cuota -928,57

3 Préstamo $ 13.000,00

4 Vf 0

5 Tipo 0

6 Estimar

7 Tasa Mensual 0,88%

8 Tasa Anual (B7*12) 10,50%

En la celda B7 la Función Tasa calcula la tasa de interés mensual, que en la celda B8 al multiplicarla por 12 periodicidad anual devuelve la tasa anual.

Celda B7=TASA(B1;B2;B3;B4;B5;B6) Se trabajo sobre el mismo ejemplo anterior para verificar que los valores obtenidos son los

mismos

Función NPER

Calcula el número de pagos de un préstamo, basado en pagos constantes, periódicos y a una tasa de interés constante

Sintaxis NPER(tasa;pago;va;vf;tipo) Tasa es la tasa de interés por periodo

Pago es el pago efectuado en cada periodo, debe permanecer constante durante la vida de la

anualidad Va es el valor actual o la suma total de una serie de futuros pagos

Vf es el valor futuro o saldo en efectivo que desea lograr después de efectuar el último pago. Si el argumento vf se omite, se asume que el valor es cero (por ejemplo el valor futuro de un

préstamo es cero) Tipo: es el valor debe ser 0 o 1 e indica el vencimiento de los pagos

Tipo: 0 al final del periodo

Tipo: 1 al inicio del periodo



A B

1 Tasa Anual 10,50%

2 Cuota -$ 928,57

3 Préstamo $ 13.000,00

4 Vf

5 Tipo

6 Cantidad de cuotas 15

La celda B6 calcula la cantidad de cuotas necesarias para saldar el préstamo según las

características del mismo (Interés, cuota, monto). Se mantiene el mismo ejemplo para

verificar los datos. Celda B6= NPER(B1/12;B2;B3;B4;B5)

Funciones Complementarias

Las siguientes funciones a analizar, Lógica SI, de Fecha y hora, FECHA.MES, y de Información ESERR y ESERROR, actúan combinadas entre sí, y con las funciones financieras permitiendo

tener la Planilla General, mediante las formulas incorporadas, preparada para ser utilizada en su totalidad 30 años o en forma parcial, 5, 10, 20 años etc.

Función SI

Devuelve un valor si la condición especificada es Verdadero y otro valor si dicho argumento es Falso.

Se utiliza para realizar pruebas condicionales en valores y formulas.

Sintaxis SI(prueba_logica;valor_si_verdadero;valor_si_falso) Prueba_logica es cualquier valor o expresión que puede evaluarse como VERDADERO o FALSO

Valor_si_Verdadero es el valor que se devolverá si prueba_logica es VERDADERO Valor_si_falso es el valor que se devolverá si prueba_logica es FALSO

Observaciones: Es posible anidar hasta siete funciones SI como argumento Valor_si_verdadero y

Valor_si _ falso para construir formulas más elaboradas Ejemplo:

En el ejemplo se determina que se pagaran comisiones para un determinado monto de ventas realizadas, y en algún caso ninguno según la siguiente tabla

Ventas inferiores a $ 20.000 no se paga comisión Ventas superiores a $20.000 pero inferiores a $ 30.000 un 1,8% de comisión

Ventas superiores a $30.000 un 4% de comisión

A B

1 Ventas Comisión

2 $ 21.000,00 $ 378,00

3 $ 15.500,00

4 $ 34.000,00 $ 1.360,00

La formula a introducir en la celda B2 para ser trasladada al resto es la siguiente Celda B2= SI(A2<20000;” “;SI(A2<30000;A2*1,8%;SI(A2>30000;A2*4%)))

Función FECHA.MES

Devuelve el número de serie que representa la fecha que indica el número de meses anteriores o posteriores a la fecha especificada.

Usar FECHA.MES para calcular las fechas de vencimiento que caen en el mismo día del mes, que el día de emisión.

Sintaxis FECHA.MES(fecha_inicial;meses)

Fecha_inicial es el número de serie que representa la fecha inicial



Meses es el numero de meses antes o después del argumento fecha_inicial. Si meses es un valor positivo, corresponde a fecha futura, si el valor es negativo corresponde a fecha pasada

Observaciones: Si el argumento fecha_inicial no es una fecha valida, FECHA.MES devuelve el valor

de error #¡NUM!

Ejemplo:

A B

1 Fecha inicial Vencimiento

2 10/11/01 10/12/01

Celda B2= FECHA.MES(A2;1)

El formato que se adopto en las celdas corresponden a Formato-Celda-fecha

Adoptando el formato Formato-Celda-General, se obtiene el valor de serie correspondiente a cada fecha, como se puede apreciar a continuación

A B

1 Fecha inicial Vencimiento

2 37205 37235

Función ESERR Devuelve como VERDADERO si VALOR es cualquier valor de error excepto #N/A (valor no disponible)

SintaxisESERR(Valor)

Ejemplo Caso común, un numero cualquiera dividido por cero, donde Excel devuelve error #¡DIV/0!

A B C

1 4 0 #¡DIV/0!

Celda C1= A1/B1 En este caso una de las soluciones posibles es:

A B C

1 4 0

En la celda C1 se aplica una función combinada en este caso la función que sé está tratando con la

función Lógica SI Celda C1=SI(ESERR(A1/B1);” “;(A1/B1) Se especifica con las funciones aplicadas que en caso de producirse el error mencionado reconozca

como verdadero “ Celda vacía” caso contrario el resultado que de la operación

Función ESERROR

Devuelve como VERDADERO si Valor es cualquier valor de error ( #N/A, #¡VALOR!, #¡REF!, #¡DIV/0!, #¡NUM!, #¿NOMBRE? o #NULO)

Ejemplo

A B C H I

1 Color Cantidad Costo total Costo unitario

2 Rojo 7 $84 Rojo $ 12

3 Verde 12 #N/A

Sencillo ejercicio, que consiste en obtener el costo total, multiplicando la cantidad columna B por el precio unitario columna I

Para resolverlo se aplica la función de búsqueda y referencia (Buscar en tabla) BUSCARV.

Celda C2= B2*(BUSCARV(A2;$H$2:$I$3;2;FALSO), obteniendo en la Celda C2 el costo total $ 84, al trasladar la formula a la celda C3 como consecuencia de no encontrar los datos de

“Verde” por carecerse en ese momento de los mismos, se produce el error #N/A (valor no disponible)



Para solucionar el problema se recurre nuevamente a la combinación de funciones, empleando

en este caso tres funciones, Lógica SI, BUSCAV, y ESERROR

A B C

1 Color Cantidad Costo total

2 Rojo 7 84

3 Verde 12

CeldaC2=SI(ESERROR(B2*(BUSCARV(A2;$H$2:$I$3;2;FALSO)));” ” ;

(B2*(BUSCARV(A2;$H$2:$I$3;2;FALSO))))

La presente formula combinada devuelve como verdadero el error #N/A y coloca celda vacía, en este caso la celda C3

PRESTAMO HIPOTECARIO

El Ejercicio consiste en el desarrollo de un cuadro de Rentas referente a un préstamo hipotecario.

En el se aplicaran las Funciones: PAGO, PAGOPRIN, PAGOINT. El cuadro debe contener la siguiente información:

*Numero de cuotas, * Calendario de Pagos, *Cantidad a pagar(cuota o renta), *Cantidad de

principal amortizada en cada cuota,* Interés devengado en cada cuota, * Principal pendiente de amortizar, * Comisión de cancelación de cada periodo, * Valor de rescate

Aplicando distintas funciones de las que cuenta Excel, en este caso como auxiliares de las funciones Financieras, se prepara la planilla para que pueda ser utilizada con un plazo máximo de

30 años, permitiendo analizar los resultados ante cualquier variación intermedia, de Plazo, Préstamo, Interés etc.

Diseño de Planilla

A B C

Entrada de Datos

20 Importe 55.000

21 Entrada 10.000

22 TIN 9,50%

23 Plazo 30

24 C.a. 1,50%

25 C.c.a. 2,50%

26 Pre / Post

27 VF 0

28 Periodicidad 12

Celda C 20 Valor del inmueble Celda C 21 Cantidad a integrar inicialmente

Celda C 22 Tasa de interés nominal

Celda C 23 Plazo en años Celda C 24 Tasa en concepto de la formalización del préstamo

Celda C 25 Comisión por cancelación anticipada ( en porcentaje) Celda C 26 Prepagable 1- Prepagable 0

Celda C 27 Valor futuro del inmueble

Celda C 28 Números de pago a realizar cada año ( 12, 6, 4, 1 )



Salida de Datos

(Se ocultaron filas para disminuir la extensión de la planilla)

A B C D E F G H

32 N° de cuotas

Fecha Principal Intereses Cuota P.pal. Pdte Comis cancel

Coste cancel

33 1 15/12/2008

$ -22,13 $ -356,25 $ -378,38 $ 44.977,87 $ 1.124,45 $ 46.102,31

34 2 15/01/2009

$ -22,31 $ -356,07 $ -378,38 $ 44.955,56 $ 1.123,89 $ 46.079,44

35 3 15/02/2009

$ -22,49 $ -355,90 $ -378,38 $ 44.933,07 $ 1.123,33 $ 46.056,40

36 4 15/03/2009

$ -22,66 $ -355,72 $ -378,38 $ 44.910,41 $ 1.122,76 $ 46.033,17

37 5 15/04/2009

$ -22,84 $ -355,54 $ -378,38 $ 44.887,56 $ 1.122,19 $ 46.009,75

92 60 15/11/2013

$ -35,25 $ -343,14 $ -378,38 $ 43.308,44 $ 1.082,71 $ 44.391,15

93 61 15/12/2013 $ -35,53 $ -342,86 $ -378,38 $ 43.272,91 $ 1.081,82 $ 44.354,74

94 62 15/01/2014 $ -35,81 $ -342,58 $ -378,38 $ 43.237,11 $ 1.080,93 $ 44.318,03

95 63 15/02/2014

$ -36,09 $ -342,29 $ -378,38 $ 43.201,02 $ 1.080,03 $ 44.281,04

96 64 15/03/2014

$ -36,38 $ -342,01 $ -378,38 $ 43.164,64 $ 1.079,12 $ 44.243,76

97 65 15/04/2014

$ -36,66 $ -341,72 $ -378,38 $ 43.127,98 $ 1.078,20 $ 44.206,17

152 120 15/11/2018

$ -56,57 $ -321,81 $ -378,38 $ 40.593,47 $ 1.014,84 $ 41.608,31

153 121 15/12/2018

$ -57,02 $ -321,36 $ -378,38 $ 40.536,45 $ 1.013,41 $ 41.549,86

154 122 15/01/2019

$ -57,47 $ -320,91 $ -378,38 $ 40.478,98 $ 1.011,97 $ 41.490,95

155 123 15/02/2019

$ -57,93 $ -320,46 $ -378,38 $ 40.421,05 $ 1.010,53 $ 41.431,58

156 124 15/03/2019

$ -58,38 $ -320,00 $ -378,38 $ 40.362,67 $ 1.009,07 $ 41.371,74

157 125 15/04/2019

$ -58,85 $ -319,54 $ -378,38 $ 40.303,82 $ 1.007,60 $ 41.311,42

212 180 15/11/2023

$ -90,80 $ -287,59 $ -378,38 $ 36.235,92 $ 905,90 $ 37.141,82

213 181 15/12/2023 $ -91,52 $ -286,87 $ -378,38 $ 36.144,40 $ 903,61 $ 37.048,01

214 182 15/01/2024 $ -92,24 $ -286,14 $ -378,38 $ 36.052,16 $ 901,30 $ 36.953,46

215 183 15/02/2024

$ -92,97 $ -285,41 $ -378,38 $ 35.959,19 $ 898,98 $ 36.858,17

332 300 15/11/2033

$ -233,90 $ -144,48 $ -378,38 $ 18.016,71 $ 450,42 $ 18.467,13

335 303 15/02/2034

$ -239,50 $ -138,88 $ -378,38 $ 17.303,84 $ 432,60 $ 17.736,43

336 304 15/03/2034

$ -241,40 $ -136,99 $ -378,38 $ 17.062,44 $ 426,56 $ 17.489,00

337 305 15/04/2034

$ -243,31 $ -135,08 $ -378,38 $ 16.819,13 $ 420,48 $ 17.239,61

386 354 15/05/2038

$ -358,06 $ -20,32 $ -378,38 $ 2.208,70 $ 55,22 $ 2.263,92

387 355 15/06/2038

$ -360,90 $ -17,49 $ -378,38 $ 1.847,81 $ 46,20 $ 1.894,00

388 356 15/07/2038

$ -363,76 $ -14,63 $ -378,38 $ 1.484,05 $ 37,10 $ 1.521,15

389 357 15/08/2038

$ -366,64 $ -11,75 $ -378,38 $ 1.117,41 $ 27,94 $ 1.145,35

390 358 15/09/2038

$ -369,54 $ -8,85 $ -378,38 $ 747,88 $ 18,70 $ 766,57

391 359 15/10/2038 $ -372,46 $ -5,92 $ -378,38 $ 375,41 $ 9,39 $ 384,80

392 360 15/11/2038 $ -375,41 $ -2,97 $ -378,38

393 $ -45.000,00 $ -91.218,38 $ -136.218,38



Desarrollo

Columna A - N° de Cuotas- Celda A 33= SI(1<=(C 23*C 28);1;0). Limita que la primer

cuota se establezca al introducir el plazo respectivo en el ingreso de datos, en caso contrario devuelve valor cero.

Celda A 34 = SI(A 33=0;0;SI(1+A 33<=($A 23*$C$28);1+A 33;0) En ambos casos se aplico la función lógica SI, en este último caso limita el número de cuotas, al total que surge de

multiplicar los años de plazo con la periodicidad

Función para ser trasladada al resto de la columna

Columna B –Fecha de vencimiento de las cuotas. En la primera celda en este caso B33, se introduce la fecha de vencimiento de la primera cuota. En la celda B34 se emplea la función SI

donde se considera la celda anterior y la cuota correspondiente, combinándose con la función

Fecha.Mes que determina el mes siguiente. Celda B34= SI(B33=0;” “;FECHA.MES(B33;1)))

En la celda siguiente, para trasladar al resto de la planilla varia solamente, cero por “ “. Celda B35= SI(B34=” “;” “;SI(A35=0;” “;FECHA.MES(B34;1)))

Esto permite que al colocar la fecha de la primera cuota, el resto de las fechas se ubiquen automáticamente, hasta la cuota correspondiente

Columna C –Principal. Función a colocar en la celda C424, para ser trasladada al resto de la columna

CeldaC33=SI(ESERR(PAGOPRIN($C$22/$C$28;A33;$C$23*$C$28;$C$20-$C$21;$C$27;$C$26));0;(PAGOPRIN($C$22/$C$28;A33;$C$23*$C$28;$C$20-

$C$21;$C$27;$C$26)))

Calcula la cantidad de cuota correspondiente a amortización en cada periodo. En este caso se combinan tres funciones, SI, ESERR, y PAGOPRIN, condicionando la función SI que a través de

la función ESERR coloque cero en caso de no existir numero de cuota.

Columna D – Intereses: Función a colocar en la celda D33, para ser trasladada al resto de la columna

CeldaD33=SI(ESERR(PAGOINT($C$22/$C$28;A33;$C$23*$C$28;$C$20-

$C$21;$C$27;$C$26);0;(PAGOINT($C$22/$C$28;A33;$C$23*$C$28;$C$20-$C$21;$C$27;$C$26)))

Calcula que cantidad de cuota corresponde a intereses en cada periodo. El mismo criterio del caso

anterior combinando las funciones SI, ESERR y PAGOINT

Columna E –Cuota: Función a colocar en la celda E33 para ser trasladada al resto de la columna

Celda E33 = SI(A33=0;0;PAGO($C$22/$C$28;$C$23*$C$28;$C$20-$C$21;C$27;$C$26))

Calcula la cuota por periodo, siendo el mismo monto para todos. Idéntico resultado se obtiene, sumando los montos obtenidos en las dos columnas anteriores. (PAGOPRIN+PAGOINT). En

este caso se utilizo otra variante, empleando la función lógica SI, condiciona que el resultado en

cero a igual valor de la cuota

Columna F –P.pal pdte: Función a colocar en la celda F33 para ser trasladada al resto de la columna

Celda F 33 = SI($C$20-$C$21+SUMA($C$33:C33)<=0;” “;$C$20-$C$21+SUMA($C$33:C33)

Consiste en la cantidad del préstamo pendiente de amortización. Mediante la función SI, se

condiciona que si la cantidad de préstamo pendiente de amortización es <=0, de cómo resultado, “ “ ( celda en blanco)

Columna G – Comis cancel : Función a colocar en la celda G33 para ser trasladada al resto de

la columna

Celda F 33 = SI(F33 =” “;” “;F33*$C$25). Comisión a cobrar por la entidad financiera en caso de querer cancelar anticipadamente el préstamo concedido. La función SI condiciona a valor de



celda en blanco en caso que la celda de préstamos pendientes de amortizar se encuentre en blanco

Columna H – Coste cancel: Función a colocar en la celda H33 para ser trasladada al resto de la

columna Celda H 33 = SI(ESERROR(F33+G33); “ “;(F33+G33)

Valor de rescate del préstamo para los periodos de la vida del mismo. En caso de que se produzca la suma de dos celdas en blanco, el valor resultante sería #¡VALOR!, ese es el motivo de optar

con la combinación de funciones SI y ESERROR, esta ultima considera ese error como

verdadero, condicionando con la función SI que en ese caso deje la celda en blanco ;” “;

Función TIR

Calcula la tasa interna de retorno de una inversión, o las ganancias por reinversión representadas

por los números del argumento valores.

Estos flujos de caja no tienen porqué ser constantes, como es el caso de una anualidad. Pero si los

flujos de caja deben ocurrir en intervalos regulares, como meses o años. La tasa interna de retorno

equivale a la tasa producida por un proyecto de inversión con pagos (valores negativos) e ingresos (valores positivos) que ocurren en periodos regulares.

Sintaxis: TIR(valores;estimar)

Valores: es una matriz o referencia a celda que contengan los números para los cuales se quiere

calcular la tasa interna de retorno.

El argumento valores debe contener al menos un valor positivo y uno negativo para calcular la

tasa interna de retorno. TIR interpreta el orden de los flujos de caja siguiendo el orden del argumento valores. Deben

introducirse valores de los pagos e ingresos en el orden correcto.

Estimar: es un número que se estima que se aproxima al resultado TIR.

En la mayoría de los casos no se necesita proporcionar el argumento estimar, se supone que es

0,1 (10%)

Proyecto de Inversión

A B C D E F G H

1 Ingresos

2 Inversión Inicial

1° Año 2° Año 3° Año 4° Año 5° Año TIR TIR 4 Año

3 -$ 90.000,00 $ 17.300,00 $ 23.650,00 $ 25.600,00 $ 26.589,00 $ 27.800,00 9,97% 1,30%

4 -$ 75.000,00 $ 14.365,00 $ 16.100,00 $ 20.200,00 $ 22.560,00 $ 23.525,00 8,34% -0,88%

5 -$ 81.000,00 $ 16.700,00 $ 18.450,00 $ 21.500,00 $ 23.200,00 $ 25.450,00 8,75% -0,54%

Como se puede apreciar el primer proyecto de inversión es él más ventajosa, en las celdas G3, G4

y G5 se calcula la tasa interna de retorno de la inversión, equivalente a la tasa de interés producida, al quinto año. Como referencia se efectúa el mismo calculo pero al cuarto año.

Celda G3=TIR(A3:F3), Celda H3= TIR(A3:E3)



Función VA

Calcula el valor actual de una inversión. El valor actual es el valor que tiene actualmente la suma

de una serie de pagos que se efectúan en el futuro.

Sintaxis VA(tasa;nper;pago;vf;tipo)

Tasa: es la tasa de interés por periodo.



Nper: es él número total de periodos en una anualidad. Pago: es el pago que se efectúa en cada periodo y que no cambia durante la vida de la anualidad.

Vf: es el valor futuro o saldo en efectivo que se desea lograr después de efectuar el último pago.

Si el argumento vf se omite, se considera que el valor es cero. ( un préstamo por ejemplo) Tipo: es él numero 0 (vencimiento de los pagos al final del periodo), o 1 (vencimiento al inicio del

periodo)

Ejemplo

Se estudia la compra de una póliza de seguros que pague $ 650 al final de cada mes durante los próximos 15 años.

El costo es de $ 50.000, y el dinero pagado devenga un interés anual del 11,50%. Para determinar si la compra de la póliza es una buena inversión, se emplea la función VA, para

calcular el valor actual de la anualidad.

A B

1 Tasa 11,50%

2 Años 15

3 pago $ 650

4 vf

5 tipo 0

6 VA -$ 55.641,64

Celda B6= VA(B1/12;B2*12;B3;B4;B5)

El resultado en la celda B6 es negativo, ya que muestra el dinero que pagaría (flujo de caja

negativo).

El valor actual de la anualidad, ($55.641,64) es mayor que lo que se pagaría ($50.000)

Función VF

Calcula el valor futuro de una inversión conformada por pagos periódicos constantes y con una

tasa de interés constante. Sintaxis VF(tasa;nper;pago;va;tipo)

Tasa:es la tasa de interés por periodo

Nper: es él número total de pagos de una anualidad Pago: es el pago que se efectúa cada periodo y que no puede cambiar durante la vigencia de la

anualidad. Va: es el valor actual de la cantidad total de una serie de pagos futuros. Si el argumento se omite,

se considera 0 (cero)

Tipo: es el numero 0 o 1 por el cual se indica cuando vencen los pagos. Si el argumento tipo se omite, se considera cero

Tipo Los pagos vencen

0 al final del periodo

1 al inicio del periodo

Observaciones

Si se realizan pagos mensuales de un préstamo a 3 años con interés anual del 14%, usar

14%/12 para tasa y 3*12 para nper. Si se realizan pagos anuales del préstamo usar 14% para

tasa y 3 para nper. Para los argumentos, el efectivo que se paga, por ejemplo en depósitos, están representados

por números negativos, el efectivo que se recibe, están representados por números positivos.



Pagos con vencimiento: Pagos con vencimiento:

Al inicio del periodo Al final del periodo

A B A B

1 tasa 7,50% 1 tasa 7,50%

2 Nper 10 2 Nper 10

3 Pago -130 3 Pago -130

4 Va -1500 4 Va -1500

5 Tipo 1 5 Tipo 0

6 VF $ 2.941,97 6 VF $ 2.933,61

El presente ejemplo consiste en un proyecto de ahorro, para aplicar dentro de 10 meses a partir de

la fecha. Se deposita $ 1.500 en una cuenta de ahorro que devenga un interés anual del 7,50%, que se capitaliza mensualmente (interés mensual). Se planea depositar $ 130 el primer día de cada

mes durante los próximos 10 meses.

El ejercicio planteado, calcula cuanto se acumula de capital en la cuenta al final de los 10 meses.

Se resolvió de las dos maneras, según los depósitos se abonen al inicio del periodo (tipo 1), o al

final del periodo (tipo 0)

Celda B6= VF((B1/12);B2;B3;B4;B5)

Education

Manual Microsoft Excel