TEMA 53 HOJAS DE CÁLCULO: ESTRUCTURA Y FUNCIONES

DISEÑO Y FORMATO DE LAS HOJAS. FUNCIONES Y FÓRMULAS. REFERENCIA A OTRAS CELDAS. MACROS.

1. INTRODUCCIÓN.

2. HOJAS DE CÁLCULO: ESTRUCTURA Y FUNCIONES.

2.1. CONCEPTO DE HOJA DE CÁLCULO.

2.2. ESTRUCTURA Y FUNCIONES EN MICROSOFT EXCEL.

3. DISEÑO Y FORMATO DE HOJAS.

3.1. FORMATO DE CELDAS.

3.2. FUENTES Y BORDES.

3.3. TAMAÑOS DE FILAS Y COLUMNAS.

3.4. AUTOFORMATOS.

4-FUNCIONES Y FÓRMULAS

4.1. FUNCIONES.

4.2. FÓRMULAS.

5. REFERENCIA A OTRAS CELDAS.

5.1. REFERENCIAS RELATIVAS.

5.2. REFERENCIAS ABSOLUTAS.

5.3. REFRENCIAS MIXTAS.

6. MACROS.

6.1. CONCEPTO DE MACRO.

6.2. MACROS AUTOMÁTICAS.

6.3. MACROS NO AUTOMÁTICAS.

BIBLIOGRAFÍA:

-Aplicaciones Informáticas. Ciclo Formativo de Grado Medio de Gestión Administrativa. Ed. Thomsom Paraninfo.

– Office 2.000 Profesional y Premium Guía Completa. Ed. InforBook’s.

– Aplicaciones Informáticas y Operatoria de Teclados. Ciclos Formativos XXI. Ed. Mc­graw hill

1. INTRODUCCIÓN.

Una hoja de cálculo permite realizar cálculos sencillos y complejos sobre datos que se introducen previamente basados en un modelo. Con una hoja de cálculo, por tanto, podremos almacenar, calcular y analizar diferentes resultados a partir de información introducida, ya sea numérica, texto o gráficos.

A lo largo del tema vamos a proceder al estudio de la hoja de cálculo de Microsoft Excel, pues es la aplicación más extendida en el campo del trabajo, así como la más utilizada en la mayor parte de los LE.S. en los Ciclos Formativos de Grado Medio y Superior de Gestión Administrativa y Administración y Finanzas, respectivamente. Es una aplicación que, una vez instalada en el ordenador, permite manejar números y fórmulas organizados en filas y columnas.

2. HOJAS DE CÁLCULO: ESTRUCTURA Y FUNCIONES.

2.1. CONCEPTO DE HOJA DE CÁLCULO.

Como hemos comentado en el apartado introductorio, una hoja de cálculo, es un programa que nos permite realizar cálculos, tanto complejos, como sencillos sobre los datos que se introducen previamente.

A través de una hoja de cálculo, e introducidos una serie de datos, podremos calcular una serie de resultados y analizar los mismos, mediante la utilización de las funciones establecidas en Excel y la introducción de fórmulas que faciliten los distintos caLculos. Asimismo, la hoja de cálculo (excel) nos permitirá elaborar textos y dar formatos a los mismos y también la elaboración e inserción de gráficos.

2.2. ESTRUCTURA Y FUNCIONES DE MICROSOFT EXCEL.

Excel es la hoja de cálculo más extendida y utilizada en la actualidad. Los documentos en Excel se denominan libros.

Un libro se compone de una varias hojas de cálculo que son, como si dijésemos, las páginas de ese libro. Las hojas contienen información relativa a un aspecto, mientras que los libros agruparán hojas de temas afines.

Cada hoja de cálculo contiene información organizada en FILAS Y COLUMNAS. Las filas son las horizontales y se denominan por un número (l, 2, 3,…). Las columnas son las verticales y se denominan por una letra (A, B, C,…).

Cada una de las intersecciones entre filas y columnas se denomina CELDA y se nombra por la letra de la columna y el número de la fila que intersectan, por ejemplo A4 ó C7.

Cada celda puede almacenar números, textos, datos, referidos a otras celdas, fórmulas, gráficos, listas y macros y herramientas de análisis y resolución de problemas.

AL conjunto de celdas que contiene una información se le denomina RANGO DE DATOS.

En la base de la ventana de cada libro existen distintos controles que simulan pestañas y que al pulsarlos nos permiten situarnos en la hoja deseada del libro.

En ocasiones, podemos limitar o modificar ese número de hojas que tiene un libro, para ello, elegiremos del menú Herramientas/Opciones/Pestaña General y allí podemos poner el número de hojas que pensemos que vamos a necesitar.

Para abrir un libro de una hoja de cálculo tendremos que ir a: Inicio/Programas/Microsoft Excel.

3- DISEÑO Y FORMATO DE LAS HOJAS.

3.1- FORMATO DE CELDAS.

Cuando se introducen datos en una celda de la hoja de cálculo, Excel alinea los números a la derecha de las celdas y los textos a la izquierda. A esto se le llama formato estándar.

Para aplicar un formato distinto a una celda o rango, primero hay que seleccionarlo. Después pulsamos sobre la barra de menús, en la opción Formato y elegimos en el desplegable la opción Celdas.

Del cuadro de diálogo elegimos la pestaña Número si el formato es de número, o Alineación para colocarlo a uno u otro lado de la celda, o Fuentes, Bordes, etc. y seleccionamos la opción de formato que nos interese haciendo clic sobre la misma y luego Aceptar. Para eliminar este formato, seleccionaremos de nuevo la celda o rango y del menú Edición elegimos la opción Borrar y luego, en el siguiente desplegable Formatos.

Existen formatos de fecha y hora, porcentaje, fracción y científico. Cualquier formato es posible personalizarlo.

Desde los botones en la barra de formatos se puede seleccionar el de Moneda, Porcentaje, Millares, aumentar decimales y reducirlos.

Dentro del menú Formato se nos presenta la opción de Formato Condicional. Esta opción nos permite que las celdas o el rango que seleccionamos adopten un determinado formato para unas condiciones y otro diferente para otras.

Si lo que queremos es alinear el texto, podemos hacerlo con los botones de alineación de la barra de herramientas que permiten alinear centrado, justificado a la derecha o justificado a la izquierda.

La alineación de texto y números en sentido horizontal, puede ser a la izquierda, a la derecha y centrada. Podemos llenar una celda, justificar o centrar dentro de una selección.

Verticalmente se puede alinear al lado superior, inferior, centrar o justificar. La orientación puede ser vertical u horizontal (apaisada).

Todas las opciones de alineación pueden controlarse des del menú:

Formato/Celdas,… en la pestaña de alineación.

3.2. FUENTES Y BORDES.

Desde el menú Formato/Celdas,… haciendo clic en la pestaña de Fuente, observamos todas las características de las fuentes a elegir, el nombre, tamaño, color y apariencia, en el caso de subrayado de qué tipo, así como el estilo de la fuente: Normal, Negrita, Cursiva, etc.

Desde los botones de (N), (K) y (S), en la barra de herramientas, así como los botones tamaño y tipo de fuente del menú formato, obtenemos los mismos resultados.

Los botones se seleccionan directamente desde la regla de bordes y se aplican a una celda o rango previamente seleccionada, al igual que en Word. Para elegir un diseño determinado desde el menú Formato/Celdas,… tenemos la opción de elegir un color para los fondos de celdas y la trama de fondo que queremos mostrar.

3,3. TAMAÑOS DE FILAS Y COLUMNAS.

Los tamaños de filas y columnas también pueden ser modificados a necesidad del usuario.

Para realizarlo basta seguir los siguientes pasos:

1. Para ajustar el Alto de una fila, se señala la línea que separa dos filas y cuando el símbolo del cursor cambie se arrastra hasta el tamaño necesario. También desde Formato/Fila/Alto de Fila se ajusta exactamente a una medida.

2. Para ajustar el Ancho de una Columna, se señala la línea que separa dos columnas y cuando el cursor cambien se arrastra hasta el tamaño necesario. Desde Formato/Columna se ajusta exactamente a una medida.

3.4. AUTOFORMATOS.

Excel tiene una serie de formatos automáticos que podemos utilizar.

– Para usar el autoformato, seleccionamos el rango al que lo queremos aplicar, abrimos el menú Formato/Autoformato. Aparece una ventana de diálogo en la que, seleccionando en el desplegable Formato de Tabla nos va mostrando un ejemplo de cómo quedaría, elegimos uno haciendo clic con el ratón y pulsamos Aceptar.

Si queremos deshacer el autoforn7ato, deberemos deshacer con la opción primera del menú Edición (también botón de la barra de herramientas). Si esto no nos está permitido porque no es la última operación realizada, deberemos seleccionar el rango al que lo hemos aplicado y seleccionar la opción Ninguno del menú fo^rmato/autoformato.

4. FUCIONES Y FÓRMULAS.

4.1. FUNCIONES.

Las Funciones son fórmulas abreviadas que realizan varias operaciones a la vez y devuelven el resultado.

Para sumar podemos escribir en la celda los sumandos separados por el signo (+), o para multiplicar podemos utilizar el signo (*).

Todas las funciones se pueden escribir manualmente, poniendo en la barra de fórmulas su nombre, precedido del signo igual (Ej: =PROMEDIO), para indicar que es una fórmula y luego introducir el rango sobre el que se desea elaborar la fórmula. También podemos utilizar el Asistente para Funciones.

La mayoría de las funciones pueden necesitar uno o más argumentos. Si la función contiene más de un argumento, éstos han de ir separados por el carácter punto y coma (;). Al escribir una función no se han de incluir espacios en blanco, a no ser que dichos espacios en blanco vayan entre comillas.

A primera vista el uso de funciones puede parecer difícil, especialmente por las diversas posibles formas disponibles de argumentos. El Asistente para Funciones facilita este trabajo y sirve de guía a través de todo el proceso de introducción de una función y proporciona una breve explicación tanto de la función como de cada uno de sus argumentos.

Para insertar una función con sus argumentos en una hoja de cálculo se puede seguir el siguiente proceso:

1. Seleccionar la celda en la que se desea introducir la función (celda activa).

2. Hacer clic sobre el botón Asistente para funciones f- para mostrar el cuadro de diálogo à Asistente para funciones. En este cuadro de diálogo se debe elegir la función que se desea utilizar. En cuadros de diálogo posteriores se podrán elegir los argumentos.

3. El cuadro de diálogo muestra las distintas categorías de funciones disponibles en excel 2000. Estas categorías clasifican el gran número de funciones disponibles en grupos más reducidos. Excel2000 muestra también una lista de las funciones utilizadas recientemente. Si la función deseada no aparece entre las recientemente utilizadas y no se está seguro de la categoría a la que pertenece, se selecciona la categoría Todas y se busca en el conjunto de todas las funciones que están disponibles. En la parte inferior del cuadro de diálogo aparece una.breve descripción de la función seleccionada, así como de sus argumentos. Si se desea más información sobre esa función se puede hacer clic en el botón Ayuda obteniéndose una breve descripción de la misma y de sus argumentos. .

4. Después de seleccionar la función deseada se pulsa sobre el botón Aceptar y se siguen las instrucciones del Asistente para Funciones en el siguiente cuadro de diálogo que aparece, que permite definir los argumentos.

5. A1 completar este cuadro, pulsamos sobre el botón Aceptar para completar la función e insertarla en la celda activa.

Los grupos (categorías) de funciones que hay disponibles son los siguientes:

• Funciones Usadas Recientemente:

Colección de las 10 últimas funciones utilizadas. Pinchando sobre cualquiera de ellas, accedemos directamente a sus argumentos.

• Todas las funciones disponibles (Todas).

Colección de todas las funciones disponibles por el programa que son un total de 207 ordenadas alfabéticamente.

– Funciones Financieras (Financieras).

Funciones cuya principal aplicación son las finanzas, contabilidad, balances,… Dentro de éstas destacamos el Pago, Tasa de interés,…

-Funciones para manejo de fechas y horas (Fecha & Hora).

Funciones tales como Día, Mes, Año,… cuyo manejo facilita la introducción de fechas en la hoja de cálculo.

• Funciones matemáticas y trigonométricas (Matemáticas & Trigonométricas).

Funciones de uso matemático, científico, tales como. Seno, Coseno, Logaritmos, Exponenciales,..,

– Funciones estadísticas (Estadísticas).

Cálculo de Desviaciones, Promedio, Mediana, Moda, Contar, Contar.Si, Contara,…

• Funciones de búsqueda y referencia (Búsqueda y Referencia).

Funciones tales como BUSCARV, BUSCARH,…

• Funciones para usar bases de datos (Base de Datos).

– Funciones para manipulación de texto (Texto).

Operaciones con caracteres alfanuméricos, tales como conversión de letras a números o viceversa, longitud de la cadena, formato de moneda,…

• Funciones lógicas (Lógicas).

Funciones lógicas o Booleanas que son los operadores lógicos Falso, Verdadero, O, Y, Si, No.

– Funciones para obtener información (Información).

Funciones que nos informan sobre el contenido de las celdas, algunas de las cuales devuelven el valor booleano (verdadero o falso) sobre la celda, tales como es Estexto, Esnúmero, Esnotexto,…

• Funciones definidas por el usuario (Definidas Usuario).

Tales funciones deben ser definidas aunque generalmente no es necesario, ya que las necesidades generales son cubiertas con las anteriores.

Si las funciones tienen varios parámetros de definición, éstas pueden ser introducidas vía teclado, pero mus veces los datos vienen dados a partir de otras celdas, para lo cual se debe pulsar ` _c: que aparece al seleccionar una función; como consecuencia surge un rectángulo debajo de la barra de estado para introducir los argumentos; este proceso se debe repetir hasta tener todos los argumentos de la función.

El Asistente para Funciones, ayuda a introducir funciones en una hoja de cálculo. Los usuarios expertos, que conocen los argumentos de definición de las funciones más usadas por ellas, pueden encontrar más fácil el teclear directamente la función con sus argumentos en la fórmula correspondiente. Por supuesto, es también posible que el usuario cree sus propias funciones en Exce12000.

4.2. FÓRMULAS.

Las fórmulas son ecuaciones matemáticas, que sirven para hacer cálculos.

Las fórmulas se emplean para evitar que haya que estar cambiando constantemente las fórmulas cada vez que cambiemos un dato numérico.

Si colocamos una fórmula dentro de una hoja y cambiamos algún dato relacionado con la fórmula, ésta hará que se recalculen automáticamente todos los resultados. Las fórmulas pueden realizar operaciones matemáticas, como suma y multiplicación, o pueden comparar los valores de una hoja de cálculo o combinar texto.

Para introducir una fórmula en una celda, tendremos que pulsar sobre el signo = al principio. A continuación del signo = habrá que introducir los datos necesarios para que se calcule el resultado.

Ejs : FÓRMULA RESULTADO

=A 1 +A2 Suma las celdas A 1 + A2.

=Al-A2 Resta A 1 y A2.

=A 1 +A2-A3 Suma A 1 y A2 y al resultado le resta A3.

=A1*A2 Multiplica Al y A2.

=A1/A2 Divide A1 y A2.

=SUMA (Al :A4) Multiplica el rango comprendido entre A1:A4.

Si queremos copiar una fórmula a lo largo de un rango, lo haremos señalando el cuadro inferior derecho; cuando el ratón se convierta en cruz, hacemos clic y arrastramos.

5. REFERENCIA A OTRAS CELDAS.

Cuando se utilizan fórmulas, generalmente se hace referencia a otras celdas. Estas referencias pueden ser relativas, absolutas o mixtas. La diferencia entre las tres tipos estriba en el modo en que se copian las fórmulas que contiene la celda.

5.1. REFERENCIAS RELATIVAS.

Sabemos que Excel distingue una fórmula de un texto en que la fórmula siempre comenzará con el operador del signo (=).

Cuando se copia de una celda que contiene referencias relativas, las referencias de filas y columnas se incrementan o decrementan en función de la distancia entre la celda origen y la de destino.

Cuando una fórmula se copia en otra celda, el resultado se modifica, adaptándose al rango de las celdas copiadas. Ejemplo: la fórmula =C2+B3 copiada hacia abajo dará como resultado =C3+B4. Esto se conoce como REFERENCIA RELATIVA y es el formato predeterminado.

5.2. REFERENCIAS ABSOLUTAS.

Pongamos un ejemplo: en la columna C, se introduce en cada celda una fórmula para multiplicar la correspondiente celda de la columna B por la celda A1, es decir:

Las referencias a la columna B son relativas mientras que a la celda A1 son absolutas, pues siempre se refieren a la celda A1 estén en la fila en la que estén. Para establecer una referencia absoluta hay que señalar la celda con el signo $. En el ejemplo anterior debemos poner $A$ 1, ya que si no lo hacemos así, al copiar se convertirían en A2, A3, etc.

Por lo tanto el resultado de la fórmula sería =B 1 *$A$ 1, de forma que al arrastrar y llenar hacia abajo, siempre se multiplicaría por la celda fija $A$1 y sólo nos varía B 1, B2, B3, 134.

5.3. REFERENCIAS MIXTAS.

Cuando uno de los datos (fila o columna) de la referencia es absoluta, pero la otra es relativa se denomina referencia mixta.

En este caso, para fijar referencias mixtas, tendremos que situar el símbolo de $, delante de la fila o columna que nosotros queramos mantener como fija (sólo delante de una de ellas, la otra por lo tanto sería una referencia relativa).

Ejemplo: $C1: Fija la columna, pero no la fila. C$1: Fija la fila, pero no la columna.

6. MACROS.

6.1. CONCEPTO DE MACRO.

Se pueden automatizar aquellas tareas que suelen ser muy repetitivas y que además se usan con frecuencia.

Una macro es como un pequeño programa que realiza una o varias tareas de forma automática. Una macro se suele utilizar para automatizar labores que se realizan con bastante frecuencia. Por supuesto, es el propio usuario quien decide qué hará una determinada macro.

Las macros pueden crearse, modificarse y eliminarse.

Además deben estar escritas de forma que Excel entienda lo que dicen, es decir, deben utilizar un lenguaje, que en este caso es Visual Basic.

Visual Basic es un lenguaje de programación que permite desarrollar aplicaciones para Windows de forma muy sencilla.

6.2. MACROS AUTOMÁTICAS.

Los complementos o macros automáticas son programas que vienen con Excel y que añaden comandos y funciones opcionales.

Excel incorpora una serie de macros que facilitan las tareas repetitivas o que contengan algún tipo de cálculo con fórmulas que tiene almacenadas. Seleccionamos el menú Herramientas/Complementos.

Marcando las opciones se habilitan las que correspondan en cada caso y nos indica una descripción en el pie de la ventana, Como ejemplo, si activamos la de Guardado automático cada cierto periodo de tiempo, excel lo guarda automáticamente.

Cuando queramos que una macro automática deje de ejecutarse seleccionaremos el menú Herramientas/Complementos y desactivaremos la casilla correspondiente a la macro que queramos desactivar.

6.3. MACROS NO AUTOMÁTICAS.

Este tipo de macros se controlan con el lenguaje Visual Basic para Excel. A estas opciones se accede desde el menú Herramientas/Macros y es preciso grabarlas con un nombre determinado, y conveniente guardarlas con algún comentario.

Grabar una macro consiste en grabar en un módulo de Visual Basic una serie de acciones. Es decir, hay que iniciar el grabador de macros, mediante la opción Herramientas/Macro/Grabar nueva macro…

Se abrirá el cuadro de diálogo Grabar macro, y a continuación ejecutar nosotros exactamente lo que queramos que haga la macro. A1 terminar hay que detener el grabador y la macro quedará almacenada.

Para que sea verdaderamente útil la creación de macros, es necesario estudiar con detenimiento cada una de las acciones a ejecutar, seleccionar el rango sobre el que se va a trabajar y situarse en el libro u hoja adecuados.

Se puede crear un botón y ponerlo en la barra de herramientas, así al pulsar sobre él, se ejecutará la macro correspondiente.

Ejemplos típicos de macros en Excel pueden ser los siguientes:

– Asignar a una celda o rango de ellas un conjunto de opciones y características de formato.

– Realizar una o varias copias de seguridad en una o varias unidades distintas

– Automatizar series de tareas, como por ejemplo grabar un libro y, al mismo tiempo, hacer una copia de seguridad.

Excel, permite crear macros tan complejos que podrían considerarse verdaderos programas.

Ejemplo:

Si quiero por ejemplo que una macro me ejecute lo siguiente: Poner un rango de datos previamente seleccionado en color Rojo y en Cursiva, tendremos que seguir los siguientes pasos:

1. Herramientas/Macro/Grabar nueva macro.

2. Nombre Macro: RojoCursiva

3. Descripción: Macro que pone en color Rojo y Cursiva un rango previamente seleccionado:.Guardar En Este-libro

4. Aceptar. Comienza grabación.

5. Ejecuto acciones. Selecciono rango: Botón K. Color de Fuente: Rojo.

6. Detener Grabación.

7. Ejecutar Macro. Herramientas/Macro/Macros/Ejecutar.