UT 7: Hojas de cálculo, operaciones avanzadas

Introducción

Una hoja de cálculo es un programa informático que permite manipular y realizar cálculos complejos con datos numéricos almacenados en tablas. También permite automatizar tareas mediante el uso de fórmulas y macros, y crear gráficos como histogramas, curvas, cuadros de sectores, etc.

La hoja de cálculo es una herramienta multiuso que sirve tanto para actividades de oficina, que implican la organización de grandes cantidades de datos, como para niveles estratégicos y de toma de decisiones al crear representaciones gráficas de la información sintetizada.

Las más importantes compañías de software han desarrollado hojas de cálculo, que suelen incluirlas en los paquetes ofimáticos que ofrecen. Entre las principales hojas de cálculo se encuentran Microsoft Excel (Microsoft Office), LibreOffice Calc (LibreOffice -The Document Foundation), OpenCalc (OpenOffice -Apache-).

7.2 Fórmulas en Excel

Excel permite manejar fórmulas de gran complejidad como, por ejemplo, cálculo de valores estadísticos y financieros, análisis de datos, etc.

A la hora de realizar las fórmulas, hay que tener en cuenta:

las fórmulas comienzan con signo igual (=)
para separar operadores ariméticos deben de utilizarse paréntesis
si una fórmula contiene errores, estos se representan así: #¿NOMBRE?; #¡REF!; #¡DIV/0!; #¡VALOR!; #¡NUM!; #¡NULO!; #N/A
cada fórmula tiene su propio asistente para ayudar al su manejo, para ello hay que pulsar en el botón insertar fórmula

Fórmulas con referencias

Las referencias identifican una celda o un grupo de celdas en una hoja de cálculo.

Con ellas se indica a Excel las celdas en las que debe buscar los valores que se utilizarán en la fórmula.

Hay 4 tipos de referencias:

relativas (por ej. =A1*A5, no hay símbolo de $ -es decir, no están fijadas-)
absolutas (por ej. =B2*$D$2, el símbolo $ está fijando a la celda D2)
mixtas (por ej. =$B6, el símbolo de $ solo fija una de ellas -columna en este caso-)

Si: =$B6, se fija la columna en Excel

Si: =B$6; se fija la fila en Excel

entre hojas (vínculo con otra hoja)

Nota: como vemos, el símbolo de $ bloquea o fija lo que tiene a continuación

Podemos introducir fácilmente $ en la función pulsando la tecla F4

108%2520referencias%2520relativas%2520y%

7.1 Importar y exportar datos

Cuando trabajamos con hojas de calculo es muy frecuente recurrir a la importación y exportación de datos.

Importar datos

Se pueden importar datos desde el portapapeles, es muy útil utilizar Ctrl + C (copiar) y Ctrl + P (pegar).

Exportar datos

Para exportar nos vamos a:

Pestaña Archivo / Exportar

106%20exportar%20desde%20excel%202_edite

106%20exportar%20desde%20excel_edited.jp

7.3 Funciones avanzadas

Excel permite añadir cálculos predeterminados a las fórmulas utilizando funciones. De esta forma, la realización de operaciónes es más simple y rápida.

Funciones de fecha y hora

Algunos tipos son: AHORA, AÑO, FECHA HOY, DIAS360, etc.

Pestaña Archivo / grupo "Biblioteca de funciones" / Fecha y hora

109%20funcion%20fecha%20y%20hora_edited.

Función SI

Es una función lógica que comprueba si se cumple una condición y devuelve un valor si se evalúa como VERDADERO y otro valor si se evalúa como FALSO.

Podemos buscar esta función o escribir directamente su fórmula:

Pestaña Archivo / grupo "Biblioteca de funciones" / Lógicas
=SI( Prueba_lógica ; Valor_verdadero ; Valor_falso )

La prueba lógica es la condición que queremos evaluar.
El valor si verdadero es el valor que aparecerá como resultado si se cumple la condición
El valor si falso es el valor que aparecerá si no se cumple.

Para la prueba lógica usamos los símbolos de comparación:

> mayor que

< menor que

>= mayor o igual que

<= menor o igual que

<> distinto de

109_funciones_l%C3%83%C2%B3gicas_edited.

Ejemplo:

Si deseamos evaluar si la nota de un alumno situada en la celda A1 está aprobada (es mayor o igual a 5) escribiríamos lo siguiente en la celda donde queremos que aparezca el resultado:

=SI( A1>=5 ; "Apto" ; "No Apto" )

El resultado será la palabra "apto" si se cumple la condición y "no apto" si no se cumple.

Importante: Cabe notar que al escribir estas palabras en la fórmula se han utilizado comillas. Estas son necesarias siempre que utilicemos texto dentro de una fórmula.

Podemos ampliar el uso de esta función teniendo en cuenta que podemos incluir como argumento de la función SI otras funciones.

Se recomienda visualiza este vídeo del canal de YouTube "Saber Programas".

Funciones Y, O

Estas funciones permiten evaluar varias condiciones a la vez. Ideales para utilizar junto con la función SI.

Ambas funciones tienen sus argumentos separados por ; (punto y coma).

Y( prueba_lógica1 ; prueba_lógica2 ; prueba_lógica3 ; ...)

O( prueba_lógica1 ; prueba_lógica2 ; prueba_lógica3 ; ...)

La función Y devuelve como resultado VERDADERO si todos los argumentos son VERDADEROS
La función O devuelve como resultado VERDADERO si alguno de los argumentos son VERDADEROS

Veamos algunos ejemplos junto con la función SI:

=SI( Y( A1>=7 ; A<9 ) ; "Notable" ; "otro" )

según este ejemplo si la celda A1 tiene un valor mayor o igual a 7 Y menor a 9 aparecerá como resultado la palabra "notable".

=SI( O( A1>=5 ; B1>=5 ) ; "Apto" ; "No apto" )

según este ejemplo si en la celda A1 o en la celda B1 aparece un valor superior o igual a 5 el resultado será la palabra "Apto". Con que ocurra en alguno de los casos se evalúa como VERDADERO

=SI( Y( A1>=5 ; B1>=5 ) ; "Apto" ; "No apto" )

según este ejemplo si en la celda A1 Y en la celda B1 aparece un valor superior o igual a 5 el resultado será la palabra "Apto". Debe ocurrir en los dos casos para ser evaluado como VERDADERO

Se recomienda visualizar el siguiente vídeo explicativo del canal de YouTube "Saber Programas"

Función SI anidados

En los argumentos Valor_verdadero y Valor_falso de la función SI también podemos incluir cualquier función, incluso podemos incluir la función SI. Esto nos lleva a utilizar SI anidados, es decir un SI dentro de otro SI.

La estructura sería:

=SI( Prueba_lógica1 ; Valor_Verdadero ; SI( Prueba_lógica2; Valor_Verdadero; Valor_falso ))

Hemos anidado un si dentro de otro, pero podríamos continuar con la idea y anidar tantos como necesitáramos.

Con SI anidados, por ejemplo, podríamos evaluar la nota de un alumno pero no quedarnos sólo con las situaciones de "aprobado" y "suspenso", sino evaluar los casos para "bien", "notable", "sobresaliente",...

Observa este ejercicio práctico: se ha utilizado una función SI anidada

=SI( C2>=2000 ;500; SI( C2>=1000 ; 200 ; 0 ))

Primero evalúa si las ventas>=2000

si se cumple se le asignan 500€,

si no se cumple se evalúa si ventas>=1000

si se cumple se le asignan 200€

si no cumple ninguna de las condiciones la comisión es 0

También se puede combinar los SI anidados con las funciones Y , O.

En este caso la estructura genérica quedará de la siguiente manera:

=SI( Y( Prueba_lógica1 ; Prueba_lógica2 ) ; Valor_Verdadero ; SI( Y( Prueba_lógica3 ; Prueba_lógica4 ) ; Valor_Verdadero ; Valor_falso ))

Se recomienda visualizar el siguiente vídeo explicativo del canal de YouTube "Saber Programas"

Función BuscarV (en algunas versiones, ConsultaV)

Esta función busca un valor específico en la primer columna de una tabla y devuelve, en la misma fila, un valor de otra columna de dicha tabla.

Su estructura es la siguiente:

BUSCARV( valor_buscado ; matriz_buscar_en ;indicador_columnas ; ordenado )

Recuerda: en algunas versiones de Excel Buscarv no funciona, entonces:

CONSULTAV( valor_buscado ; matriz_buscar_en ;indicador_columnas ; ordenado )

Valor_buscado: es el valor que se va a buscar en la primera columna de la tabla
Matriz_buscar_en: es la tabla de datos donde hay que buscar.
Indicador_columnas: es el nº de columna desde la cual debe devolverse el valor coincidente.
Ordenado: según si queremos buscar una coincidencia exacta o aproximada, pondremos,

VERDADERO: si no localiza ninguna coincidencia exacta, devolverá el siguiente valor aproximado. Para ello los valores de la primera columna de matriz_buscar_en deben estar ordenados ascendentemente.
FALSO: sólo buscará una coincidencia exacta (es la opción más usada).

Ejemplo de uso de esta función:

Imaginemos las siguientes tablas

NOTA:

La función BUSCARV en Excel 2010 cambió de nombre: Con el afán de tener un nombre más descriptivo de la función BUSCARV en Excel 2010, el equipo de Microsoft decidió efectuar un cambio de nombre a esta famosa y útil función. En Excel 2010 la podrás encontrar con el nombre de CONSULTAV.

El regreso de la función BUSCARV a Excel 2010: De acuerdo a un comunicado oficial de Microsft la función BUSCARV en Excel 2010 había cambiado su nombre a CONSULTAV. Este era un cambio real, sin embargo hubo tanta confusión y comentarios de parte de los usuarios de Excel 2010 que se decidió regresar el nombre de la función a BUSCARV a partir del lanzamiento del Service Pack 1 de Office.

Se recomienda visualizar el siguiente vídeo explicativo del canal de YouTube "Saber Programas"

En la tabla principal, para conocer el título, género y precio de la película podemos usar la función BUSCARV.

Se trata de buscar el código en la tabla inferior y que nos devuelva el valor deseado.

Para el Título:

BUSCARV( A9 ; A23:D36 ; 2 ; 1 )

Para el género:

BUSCARV( A9 ; A23:D36 ; 3 ; 1 )

Para el Precio:

BUSCARV( A9 ; A23:D36 ; 4 ; 1 )

Función CONTAR.SI

La función CONTAR.SI cuenta el número de celdas dentro de un rango que cumplen un criterio especificado por el usuario.

La función CONTAR.SI se presenta del siguiente modo:

= CONTAR.SI( rango ; criterio)

Donde,

rango: es el rango de celdas que se van a contar.
criterio: es la condición que determina las celdas que se van a contar. Por ejemplo, los criterios pueden expresarse como 32, ">32", B4, "manzanas" o "32".

NOTA: En los criterios se puede utilizar los caracteres comodín — signo de interrogación (?) y asterisco (*) —. El signo de interrogación corresponde a un solo carácter cualquiera y el asterisco equivale a cualquier secuencia de caracteres.

Ejemplo: imagina que queremos contar la cantidad de comedias que hay en el siguiente listado.

La función quedaría de la siguiente forma: = CONTAR.SI( C25:C38 ; "Comedia" )

Se recomienda visualizar el siguiente vídeo explicativo del canal de YouTube "Generación aprende"

Función SUMAR.SI

La función SUMAR.SI sirve para sumar los valores de un rango de celdas que cumple unos criterios que se especifican.

La función SUMAR.SI, se expresa de la siguiente forma: SUMAR.SI( rango ; criterio ; rango_suma)

Siendo:

rango: es el rango de celdas que se desea evaluar según los criterios especificados.
criterios: es la condición que determina las celdas que se van a sumar. Por ejemplo, los criterios pueden expresarse como 32, ">32", B5, 32, "32", "manzanas".
rango_suma: son las celdas que deseamos sumar si se cumple el criterio. Si se omite el argumento rango_suma, Excel suma las celdas que están especificadas en el argumento rango.

Siguiendo con la tabla anterior como ejemplo, imaginemos que queremos sumar el precio de las películas infantiles. La función quedaría de la siguiente forma:

=SUMAR.SI( C25:C38 ; "infantil" ; D25:D28 )

Relacionado con SUMAR.SI, también podemos encontrar

SUMAR.SI: permite sumar valores que coinciden con un único criterio.
SUMAR.SI. CONJUNTO: permite sumar valores que cumplen varios criterios.

Se recomienda visualizar el siguiente vídeo explicativo del canal de YouTube "Saber Programas"

7.4 Ordenar datos en Excel

Para ordenar los registros de una base de datos tenemos dos opciones:

Nos colocamos sobre la columna o campo por el que queremos ordenar la tabla y pulsamos en el botón orden ascendente o en la opción orden descendente, dependiendo del orden deseado (pestaña Datos / grupo Ordenar y filtrar / botones orden ascendente - descendente).

Si deseamos ordenar por varios campos, debemos acceder al botón "Ordenar" (pestaña Datos / grupo Ordenar y filtrar / botón "Ordenar"). Nos aparecerá un cuadro de diálogo donde podremos definir distintos niveles de ordenación. Imaginemos que queremos ordenar el siguiente cuadro 1º por "nombre" y en 2º lugar por el criterio "fórmula", sería algo así,

Se recomienda visualizar el siguiente vídeo explicativo del canal de YouTube "Saber Programas"

7.5 Filtrar datos

Filtrar los registros o filas de una tabla implica mostrar únicamente las filas deseadas, las que cumplan una determinada condición. Para filtrar los datos nos vamos a:

Pestaña Datos / grupo "Ordenar y filtrar" / Filtro

En cada una de los encabezados de las columnas aparecerán una flechas que nos permite acceder a un menú asociado a cada una de las columnas. Desde este menú podremos ordenar los datos y filtrarlos. Podemos filtrar los datos seleccionando aquellos que deseamos visualizar o eligiendo entre los distintos filtros (diferentes dependiendo del tipo de datos de la columna) que nos permiten definir condiciones más complejas.

7.6 Subtotales

Otra opción interesante para las bases de datos en Excel es el cálculo de subtotales.

Accedemos al cuadro de diálogo Subtotales aquí: Pestaña Datos / grupo "Esquema" / Subtotal

Voy a explicar como funciona a través de este ejemplo:

Supongamos tenemos el siguiente cuadro y queremos saber el subtotal de salarios por departamento

1º. Ordenamos los datos por departamento

2º. Marcamos la matriz (cuadro) y nos vamos a: pestaña Datos / grupo Esquema / botón Subtotal

3º. Veremos un desplegable y completamos, en nuestro ejemplo sería:

Para cada cambio en: Departamento
Agregar subtotal a: Sueldo anual

4º. Pulsamos el botón "Aceptar". Se muestra la información a modo de esquema, que puede expandirse o contraerse.

Expandido

Contraido

Se recomienda visualizar el siguiente vídeo explicativo del canal de YouTube "Saber Programas"

7.7 Tablas y gráficos dinámicos

Una tabla dinámica nos permite resumir los datos de la tabla y organizarlos de tal manera que sea más sencillo su estudio.

Esta opción la encontramos en: pestaña Insertar / grupo Tabla dinámica

Voy a explicar como se hace una tabla dinámica sencilla con este ejemplo:

Supongamos que tenemos la tabla de arriba y quisieramos hacer una Tabla dinámica para mostrar la información sobre las Ventas de forma más resumida y sencilla.

1º. Nos vamos a la pestaña Insertar / grupo Tabla dinámica / Tabla dinámica

2º. Selecciona la tabla con la que quieres hacer la Tabla dinámica

3º. Elegimos donde queremos poner el informe

4º. Pulsamos el botón "Aceptar"

5º. Completamos los datos que nos piden pinchando y arrastrando: rótulos de columnas, rótulos de fila y valores.

Esta tabla se puede modificar cambiando los campos elegidos o su posición como rótulo de fila, columna o valores. Además se pueden filtrar los datos desde la propia tabla.

Por otra parte, podríamos realizar un gráfico a partir de la tabla dinámica.

Se recomienda visualizar el siguiente vídeo explicativo del canal de YouTube "Generación aprende"