Curso de Excel, La hoja de Cálculo. Uso Básico

1 Introducción

1.1 Conceptos

Una hoja de cálculo es una rejilla de datos formada por un conjunto de cuadriculas distribuidas en filas y en columnas. Cada cuadrito se llama celda estas forman parte de una fila y a una columna. La fila esta identificada por su número de fila, y la columna está identificada por la letra.

En cada celda se puede escribir un texto, un número, una fecha, una fórmula y otros valores…

1.2 Historia y evolución

El origen de las hojas de cálculo está en las hojas de papel cuadriculado en la que manualmente se anotaban datos, como textos números y resultados matemáticos. A este tipo de documentos se le llama cuadrante.

En los inicios de los 70, aparece la primera hoja de cálculo, para los grandes ordenadores de aquella época, a finales de los 70 debido al tamaño y la complejidad de los de ordenadores, junto con la popularización de los lenguajes de programación se favoreció el desarrollo de las hojas de calculo, ejm. Visical-es la primera hoja de calculo comercial, solamente funcionaba en las calculadoras HP.

En los años 80 se produjo un hecho fundamental que fue la aparición del PC. Esto dio lugar a que apareciera la primera hoja de cálculo para PCla Lotus1 2 3 creada por IBM. Actualmente gracias al éxito del sistema operativo Windows Microsoft desarrolló una hoja de calculo para el, esta se llama hoja de calculo Excel y en sus distintas versiones a conseguido ser la hoja de cálculos más usada.

Hay otros programas de este tipo: Lotus Symphony, calc de open office.

2 Microsoft Excel

2.1 Características principales

Una hoja de cálculo es una rejilla de celdas distribuidas en filas y columnas

El máximo de filas es 65536, columnas 256

Los documentos de Excel se les llaman libros, se almacena en un archivo con extensión .xls. Documento Excel o libro esta formado por varias hojas de cálculo, por defecto cada documento Excel muestra 3 hojas de cálculo dicho valor se puede modificar pudiendo añadir nuevas hojas o eliminando algunas de ellas. En una celda de la hoja de cálculo podremos introducir números, formulas, texto… El aspecto de una hoja de calculo es totalmente configurable, se puede cambiar: tamaño fuente, tipo, fondo, bordes, tamaño de la columna, tamaño de la fila, insertar grafico.

Excel posibilita la exportación e importación de datos, permite convertir una tabla de Word en una hoja de cálculo Excel, también permite convertir una hoja de cálculo de Excel en una base de datos Access, estas operaciones son fáciles entre las herramientas de la misma suite.

Entorno de trabajo

Descripción de la pantalla inicial del entorno Excel

• En la barra de título muestra el nombre del documento que estamos editando
• Barra de menú que contiene las barras de Excel, agrupadas en menús agrupables. Existen 3 tipos de menú, inmediato, desplegable, con ventanas, opción
• Barras de herramientas, contienen iconos para ejecutar de forma inmediata de para ejecutar las operaciones más habituales. Existen un total de 21 herramientas disponibles. El usuario podrá modificarlas e incluso crear barras de herramientas propias, al igual que Microsoft Word.
• Barra de fórmulas: Muestra la dirección de la celda activa, en la parte derecha muestra el contenido de la celda activa. Sobre esa línea se puede modificar el contenido de cualquier celda además en ese lugar es donde se introducirán las fórmulas.
• Rejilla de datos, es la parte principal de la hoja de cálculo y es donde se muestran los datos, gráficos y el resto de elementos introducidos en la hoja.
• Barra de desplazamiento: Vertical y horizontal
• Barra de hojas: Muestra una pestaña por cada una de las hojas del libro, sobre esa pestaña puedo realizar las siguientes operaciones insertar hoja, eliminar, y mover.
• Barra de estado: proporciona información sobre el documento y el modo de edición actual

2.2 Configuración y personalización

Al igual que Word, Excel puede configurarse y adaptarse a mis necesidades. Para ello disponemos del menú opciones que se encuentra dentro de herramientas/opciones.

• Ver: Permite configurar aspectos visuales
• Calcular: Permite configurar el modo de trabajo de las formulas, normalmente está en modo automático
• Modificar: Permite cambiar el modo de edición
• General: Donde se configuran las cosas generales
• Transición: Forma en la que se guardarán los archivos
• Listas personalizadas: Muestra las listas ya existentes y permite al usuario introducir las suyas propias
• Operaciones para visualizar los gráficos: Colores y paleta
• Internacional: Permite establecer el separador de miles(.)
• Guardar: Opciones de grabación y recuperación automáticas
• Comprobación de errores: Se puede modificar
• Reglas de comprobación de errores:
• Ortografía: Reglas, diccionario, cifrado

3 Primeros pasos de Excel

3.1 Introducción de datos

En cada celda de la hoja de cálculo es posible introducir textos, nº, fechas o formulas. Para introducir datos en cualquier celda podemos usar cualquier método de los siguientes:

• INTRO: No se valida el valor introducido en la celda
• Teclas de movimiento: Validan el valor introducido, pero la celda activa cambiará dependiendo de la flecha que hayamos probado.
• Cuadro de aceptación: Permite validar el dato introducido pulsando V de Excel

Para cancelar la introducción de un valor en una celda

• Darle a la tecla “ESC”
• O pulsar el símbolo X de Excel

3.2 Modificar datos

Una vez validada la entrada de datos en una celda, si se desea modificar podemos utilizar los siguientes métodos:

• F2: Permite editar el contenido de la celda
• Barra de fórmulas: Cosiste en colocar el punto de inserción en el lugar adecuado en la barra de fórmulas

3.3 Tipos de datos

Los tipos de datos que podemos incluir en una hoja de cálculo son:

• Valores constantes: Pueden ser un número, fecha, hora o texto
• Formulas

3.3.1 Números

Para introducir un número utilizamos los caracteres numéricos del 1al 9, también podemos incluir los operadores +, -, €, %, (, ).

Si yo introduzco un número seguido del operador %, automáticamente le asigna el formato porcentaje.

Si a un nº le asignamos el signo €, se le asigna el formato monetario.

Si quiero introducir fracciones, para evitar que lo considere como una fecha es añadir un 0+un espacio “0 “. Si el número no cabe en la celda, mostrará una cadena de almohadillas.

3.3.2 Fechas y horas

Normalmente para introducir una fecha se utiliza el siguiente formato dd/mm/aa, dd-mm-aa… Para introducir una hora en el siguiente formato 14:30:00. Tanto una fecha como una hora se alinean a la derecha de la celda, en el caso de que se alinee a la izquierda querrá decir que hemos utilizado un formato incorrecto, y que por lo tanto lo ha considerado como un texto.

3.3.3 Texto

Puede contener letras, números y caracteres especiales, el texto por defecto se va a alinear a la izquierda de la celda a diferencia de los números fecha y hora, que se alinean a la derecha.

Cuando quiero que un valor numérico, sea reconocido como un texto previo a la introducción de un número le ponemos el apostrofe “´”.

3.3.4 Formulas

Una formula es una secuencia que puede estar formada por

• valores constantes
• referencias a otras celdas
• nombres
• funciones
• operadores

La regla fundamental es el =

=A1*16%   Multiplica la celda A1*16

=A1*0´16

3.3.5 Operadores

En una formula se pueden incluir los siguientes tipos de operadores

• Operadores aritméticos +, -, /, *, ^, %
• Operador de texto es el &: lo que hace es que concatena (une) dos cadenas de caracteres.
• Operadores: se utilizan para comparar valores y proporcionan un valor lógico <, >, A1>3 (TRUE O FALSE)
• Operadores de referencia indican el valor producido en la celda de referencia que debe ser utilizado en la fórmula
• Rango (:), unión (,), selección (;)

Los paréntesis se utilizan para cambiar el orden de precedencia de las operaciones.

La precedencia de los operadores, establece la prioridad con la que se ejecutarán las operaciones cuando en una formula aparecen varios operadores distintos.

Si se pode entre paréntesis (  ) se le da prioridad a la operación

4 Funciones

Una función es una fórmula especial de Excel que operan con uno o más valores y que devuelve un resultado que aparecerá directamente en un lugar donde se ha introducido la función. La sintaxis se escribe el nombre de la función, hay que tener en cuenta hay que tener en cuenta las siguientes reglas, se colocará el símbolo = al principio siempre y cuando la formula comience por el nombre de la función =.

Una función se caracteriza porque puede llevar parámetros o argumentos, puede ocurrir que haya funciones que no necesiten ningún parámetro, en estos casos es obligatorio colocar los paréntesis. Los parámetros o argumentos siempre se colocan entre paréntesis fechas o textos, referencias a otras celdas rango de celdas.

Los parámetros de una función tienen que estar separados por un . o ,.

4.1 Funciones básicas

Las más empleadas son:

• Suma
• Promedio
• Máximo
• Contar

4.2 Tipos de funciones

• Financieras: Permiten realizar cálculos relacionados con las finanzas, sobre todos con la gestión y análisis de préstamos. Mediante este tipo de funciones dado el importe de un préstamo, el interés del préstamo y el número de cuotas y a partir de las funciones financieras obtenemos el cuadro de amortización del préstamo.
• Fecha y hora: Este grupo de funciones permiten el manejo  de datos de tipo fecha, ej: La función ahora, introduce en la celda la fecha y hora actual. Ej: la función AÑO extrae el año de un valor de tipo fecha.
• La función DIAS360 calcula el número de días transcurridos entre dos fechas =DIAS360(A8;AHORA())/365
• Trigonometrica y matemáticas: Incluye funciones para calculo científico como logaritmos, raíz cuadrada, factorial, potencias productos, también permite usar funciones trigonometricas (coseno, seno, tangente). También funciones de tipo aleatorio como número aleatorio… ej: generar un número aleatorio del 1 al 10, se multiplica y se obtiene el entero mediante la funcion entero.
• Máximo , mínimo, frecuencia, perfentil, varianza … este tipo de funciones son las mas usadas. Ejm. Utilizar o crear una formula que permita contar el nº de alumnos que son mayores de edad, utilizaremos la funcion CONTAR.SI permite contar un elementó siempre y cuando cumpla una función. Esta función tiene dos parámetros: en una se ha de especificar el rango de celdas donde se efectúa una función y criterio.

La función quedaría así =CONTAR.SI

5 Fórmulas

5.1 Nombres

Las formulas están formadas por el signo = y luego la fórmula, existe un comando en Excel que permite asignar un nombre a una celda o a un rango de celdas, la ventaja que tiene esta operación es que dicho nombre lo podré utilizar más fácilmente en una formula.

1. Asignar a una celda
2. Seleciono la celda
3. Asignar nombre/opción de filas
4. Insetar /celda/definir
5. Cuando se copie la formula en otra celda se actualiza la referencia incluidas en la fórmula, pero no el nombre. Por lo tanto el uso de los nombres se utiliza para fijar la dirección a una celda dentro de una formula.
6. Se puede asignar nombre a un rango de celdas, se selecciona el rango y se selecciona a rango definido y ahora puedo utilizar ese nombre en formulas y funciones

5.2 Referencias

Existen 3 tipos de referencias, existen 3 formas de hacer referencia a una celda en una fórmula o en la función.

Las regencias relativas son la que hemos utilizado hasta ahora, la referencia de la letra de columna del número de la fila.

Ejm. A7, B90

Cuando utilizo referencias relativas en una fórmula, actualiza dicha referencia a la posición de la celda, donde he copiado dicha fórmula.

=SUMA(A1;J9)

Una referencia absoluta de una celda implica que la dirección de dicha celda, no se va a actualizar cuando la fórmula sea copiada en otra celda. Las direcciones absolutas permiten fijar, la dirección a una celda en una fórmula, para especificar la dirección absoluta se le antepone el símbolo $ a la columna y a la fila (esta manera es capaz de fijar la referencia a una celda en una fórmula)

Mixtas: Permiten referenciar una celda fijando bien la fila o bien la columna, para fijar la fila $ ej. $AD  y columna, cuando copio una fórmula que utiliza referencias mixtas solamente se podrá actualizar la parte de la referencia no fijada.

6 Manipulación de celdas

Antes de realizar cualquier operación sobre una celda o rango, tendremos que seleccionar las celdas implicadas.

6.1 Selección de una celda

Seleccionar una celda hacemos clic en ella

• Para seleccionar un rango activo la casilla con un clic y la celda final manteniendo pulsado la tecla Shift.
• Para seleccionar una columna pinchamos en el encabezado de la columna
• Para seleccionar un a fila pinchamos en el encabezado de una fila
• Seleccionar una hoja entera pulsamos control + e o en la esquina de las reglas.

6.2 Ampliar o reducir selección

Lo hacemos mediante la tecla Control, para agregar celdas a la selección activa utilizamos el botón control (cuando las nuevas celdas de la selección no son contiguas) si son contiguas utilizo la tecla mayúscula.

Anclar el inicio de una selección: para anclarlo se pulsa la tecla F8, para desanclar volvemos a pulsar F8.

Con mayuscula F8 permite añadir celdas o conjunto de celdas como se quiera.

6.3 Copiar Celdas

Normalmente para copiar celdas se utiliza el portapapeles

En Excel tenemos una opción que se llama pegado especial: que permite seleccionar determinados elementos de la celda.

Hay otra opción de copiar celdas que es utilizando solamente el ratón y en celdas adyacentes arrastrada la esquina inferior derecha llamada controlador.

Cuando se copia una fórmula en otra celda las direcciones relativas de la fórmula se adaptan a la nueva posición, a veces no interesa la modificación de las direcciones por lo que en dicho caso utilizaremos las referencias mixtas o las absolutas

Aparte:

Validación de introducción de datos

Excel tiene la posibilidad de validar la introducción de datos en una celda mediante la selección de una opción de una lista desplegable.

• Para crearla en primer lugar en una parte vacía en una hoja de cálculo se escriben los valores de la lista
• Nos situamos en la celda donde quiero establecer la lista desplegable.
• En tercer lugar le doy a datos à Validación
• En permitir seleccionamos Lista
• Selecciono el origen de la lista

Cuando las opciones a introducir en una celda son limitados, para darle mayor calidad a la hoja de cálculo utilizaré una validación de datos mediante una lista desplegable.

Función Fecha y Hora se multiplica por 30

Funciones condicionales:

Funcion CONTAR.SI cuenta solamente si se cumple una condición

=CONTAR.SI(A4:A20;”PEDRO”)

La funcion SUMAR.SI suma mientras se cumpla una condición

=SUMAR.SI(A4:A20;”PEDRO”;D4:D20)

En rango especificamos el rango de celdas donde se busca la condición

En rango suma específico el rango de celdas en el que se encuentran los valores que quiero sumar

7 Formato condicional

Permite establecer un formato especial a partir del cumplimiento de una condición de forma que si se cumple la condición se establece unas opciones de formato y si no se cumple se puede establecer otras opciones de formato distintas aunque esta opción no es obligatoria.

1. Selecciono el rango de celdas a las que les quiero asignar el formato condicionar.
2. Nos vamos a formato/formato condicional
3. establezco el formato y la primera condición
4. establezco la segunda condición

8 Funciones de bases de datos

Permite realizar consultas sobre los datos introducido en una hojas de cálculo tal y como si fueran.

Cada fila de la hoja se considera un registro.

Función BDMIN devuelve el valor mínimo de una columna

R1-> Valor que se desea buscar

R2-> rango donde se desea buscar

R3-> Valor a devolver

Curso realizado por www.mancera.org