Logotipo horizontal de Diego C Martín colores invertidos, blanco y naranja para findo negro

Funciones, filtros y macros en hojas de cálculo

Categorías:  Autor: Diego C Martin 
Funciones en hojas de cálculo

Introducción al uso de funciones en hojas de cálculo como excel

Las funciones no son más que funciones más complejas que vienen preestablecidas y que por lo tanto no tenemos que inventar, sino que las podemos reutilizar.

Para interactuar con ellas y que puedan funcionar, normalmente deberemos introducir algunos valores que le pasamos a la aplicación de hoja cálculo como parámetro. Estos parámetros pueden ser, al igual que en las fórmulas, unos caracteres o números específicos o referencias a celdas o rangos.

Un ejemplo de una función muy común es la suma. A la función suma le debemos indicar el conjunto de valores que deseamos sumar separados por el símbolo punto y coma ‘;’ (sin las comillas).

Los parámetros de una función siempre los pondremos entre paréntesis y separados por el ‘;’. Además, cada función tiene un nombre y, al igual que en el caso de las fórmulas, siempre hay que comenzarlas con el signo ‘=’.

Un ejemplo sería:

=SUMA(A1:A4)

La función anterior daría como resultado la suma de los valores de las celdas comprendidas en el rango A1:A4.

En todas las aplicaciones de hoja de cálculo, las funciones son iguales con el fin de facilitar la compatibilidad. Lo único que puede ocurrir es que cambie el idioma, y aunque los programas sí que las entenderán, nosotros las veremos distintas. Por ejemplo, a función suma en inglés será SUM.

Hay que tener en cuenta que, en las hojas de cálculo de Google, las funciones siempre son con la nomenclatura inglesa, aunque tengamos la interface en español.

También, todos los programas de hoja de cálculo tienen un sistema de ayuda integrado en las fórmulas, de forma que conforme la escribimos nos va a ayudar a completar los parámetros necesarios para que funcione.

A continuación, vamos a ver cómo nos ayuda en este sentido cada una de las herramientas que estamos viendo.

Construcción de funciones con el asistente de funciones

Este método es igual en cualquiera de las aplicaciones clásicas. No lo tenemos disponible en las hojas de cálculo de Google.

En la imagen a continuación podemos ver cómo hay un botón fx a la izquierda de la barra de fórmulas, marcado en rojo en la esquina superior izquierda.

Este es el botón de funciones, y abre la ventana que aparece delante. En ella en primer lugar disponemos de un cuadro de búsqueda que nos ayudará a localizar la función deseada.

Justo debajo tenemos un desplegable que nos permite filtrar por categorías y como se puede observar, en la parte inferior obtenemos una breve descripción de lo que hace la función.

Esta información a podemos ampliar si hacemos clic en el enlace de ayuda de la esquina inferior izquierda.

funciones con el asistente
Funciones con el asistente de Libre Office

Si seleccionamos la función suma y presionamos aceptar, veremos que aparece otra ventana solicitando los parámetros o argumentos:

Funciones con el asistente de Libre Office 02
Funciones con el asistente de Libre Office 02

En el caso de esta función SUMA, los parámetros a introducir son infinitos. En principio aparecen dos huecos para introducir números (o celdas, como en la imagen), pero conforme vamos agregando, aparecerán más. De esto modo, podríamos agregar también rangos de cedas.

Como podemos ver en la vista anterior, sobre la marcha vamos viendo tanto el valor de las celdas referenciadas, como el valor total que va resultando con la función, además de una explicación.

Si pulsamos Aceptar, obtendremos el resultado y en la barra de fórmulas de esa celda podemos visualizar la fórmula resultante. En el ejemplo,

=SUMA(C8;C9)

Esta función podría haberse representado también como:

=SUMA(C8:C9)

Dando el mismo resultado.

Construcción de funciones en la barra de fórmulas

Otro método que podemos usar es comenzar a escribir directamente la función en la barra de fórmulas.

Este método sí que aparece en las hojas de cálculo de Google y por ello lo vamos a ver por ahí, pero es análogo al de cualquier otra aplicación de las que estamos estudiando.

Agregar función mediante la barra de fórmulas
Agregar función mediante la barra de fórmulas

Como podemos ver en la imagen anterior, nada más comenzar a escribir una función, aparecen todas las funciones que comienzan por las letras escritas colocadas por orden alfabético.

Además, si pasamos el ratón por encima, veremos información breve a cerca de lo que conseguimos con la función. En este caso el puntero del ratón está posado encima de la función SUM, que como adelantamos previamente, es la versión en inglés que utilizan las hojas de cálculo de Google.

Barra de fórmulas en inglés en las hojas de cálculo de Google
Barra de fórmulas en inglés en las hojas de cálculo de Google

Si en ese momento hacemos clic sobre la propuesta del autocompletar, vemos cómo el asistente nos termina de escribir la función seleccionada y ahora nos muestra información actualizada a cerca del funcionamiento general de la función y sobre el parámetro o argumento que debemos introducir.

Siguiendo en ejemplo anterior, la fórmula quedaría de la siguiente manera:

=SUM(C8;C9)

Es interesante percatarse de que mientras estamos en el modo edición de una función previamente introducida, la aplicación nos marcada las celdas de las que está cogiendo los datos para facilitarnos la lectura.

Además, podemos fácilmente sustituir un argumento si lo seleccionamos y volvemos a hacer clic en otro cualquiera.

Ten en cuenta que no es necesario escribir los nombres de las referencias, sino que también podemos seleccionarlas con el ratón. Lo que nos sea más cómodo según el momento.

Ejercicio: Utiliza la función suma referenciando celdas que se encuentren en otras hojas.

Funciones más usadas en hojas de cálculo

Ya hemos visto que podemos encontrar funciones financieras, de fecha y hora, matemáticas y trigonométricas, estadísticas, de búsqueda y referencia, de texto, de base de datos, lógicas, y otras categorías más muy específicas.

Lo cierto es que hay muchas fórmulas, pero en la mayoría de los casos usaremos unas pocas y muchas de ellas también son variantes de las más básicas, con lo que, si sabemos manejar estas, no tendremos problemas con las demás.

  • Ya conocemos la función SUMA o SUM, hemos visto varios ejemplos con ella. Muy sencilla pero muy útil, seguro que la usas constantemente.
  • Máximo y mínimo. Estas funciones sirven para obtener los valores máximos y mínimos de un conjunto de valores que introducimos por parámetro. Es tan fácil de usar como la función suma. Simplemente introducimos los valores de entre los cuales queremos obtener el mínimo o máximo. Son dos funciones distintas. Para calcular el mínimo usamos MIN(rango) y para el máximo MAX(rango). No tiene en cuenta celdas con valores lógicos o de texto.
  • Esta función devuelve el número de elementos que hay en un rango de celdas dado. Sólo tiene en cuenta valores numéricos. Se expresa como CONTAR(rango)
  • Devuelve la fecha actual en formato de fecha y se expresa como HOY(). Esta función no tiene ningún parámetro y siempre mostrará la fecha actualizada. Es decir, que si la utilizamos y hoy por ejemplo es 28 de enero, si abrimos el documento mañana, mostrará 29 de enero.
  • Concatenar, izquierda, derecha y longitud. En realidad, se trata de varias funciones útiles relativas a texto. Vamos por partes:
    • Concatenar: se expresa como CONCAT(rango), y lo que hace es unir las cadenas de caracteres introducidas por parámetro, una detrás de otra. Una cosa a tener en cuenta a la hora de concatenar cadenas de caracteres es que la función no introduce espacios en blanco entre una y otra cadenas de las que une, con lo cual, habrá que tener en cuenta concatenar espacios también para que el texto resultante tenga sentido si fuera necesario.
    • Izquierda y derecha: Estas funciones extraen la parte izquierda o derecha de un texto dado respectivamente. Se expresan como IZQUIERDA() y DERECHA() y requieren dos parámetros de entrada. El texto de partida sobre el cual vamos a aplicar la extracción y el número de caracteres a extraer.

Un ejemplo sería el siguiente:

Tenemos en la celda A1 el texto:

Hola, soy Diego

Si usamos la función

=IZQUIERDA(A7;3)

Obtendremos:

Hol

Si usamos:

=DERECHA(A7;5)

Obtendremos:

Diego

Longitud: Esta función devuelve el número de caracteres de una cadena de texto. Es muy sencilla de utilizar porque sólo hay que introducir o indicar una cadena de texto y devolverá un valor numérico. Se expresa de la forma LARGO(celda). Es simple y de gran utilidad en conjunto a las dos anteriores, ya que hay muchos casos en los que queremos extraer cadenas de caracteres de largo variable.

Caso de uso:

Tenemos un listado de páginas web dispuestas en columna. Un ejemplo de dirección web sería:

https://diegocmartin.com

Sin embargo, queremos obtener sólo los nombres de dominio, que sería la parte final. Es decir, diegocmartin.com.

El problema radica en que queremos obtener la parte derecha de la cadena de texto, pero esta es de longitud variable. Sin embargo, la parte izquierda es fija, http://www es la parte que queremos desechar, y esta tiene un total de 10 caracteres.

Por lo tanto, podemos obtener la parte variable de la cadena restando la parte fija que queremos desechar a la longitud total. Suponiendo que la dirección web se encuentra en la celda A1, la fórmula resultante sería:

DERECHA(A1;LARGO(A1)-10)

Función SI (IF)

Esta función es un poco más compleja pero extremadamente útil, ya que se puede aplicar en un sinfín de casos y además hay otras que surgen a través de esta como contar si, o sumar si.

La función SI() tiene un mínimo de dos parámetros y un máximo de tres con la siguiente forma:

SI(prueba_lógica;valor_si_verdadero;[valor_si_falso])

Lo que hace es que si el resultado de una condición, la prueba lógica, es verdadero, el resultado de la celda en la que usamos la función será lo que especificamos en valor si verdadero; y si el resultado de la condición es falso aparecerá el valor si falso.

El valor si falso no es obligatorio, por ello lo que comentábamos de dos o tres parámetros.

La prueba lógica es una comparación que hacemos con operadores lógicos, que son menor que <, mayor que >, igual =, menor o igual <= y mayor o igual >=.

Ejemplo:

Siguiendo el ejemplo que hacíamos anteriormente sobre la ropa, imaginemos que tenemos otra tabla en la que almacenamos el stock disponible de cada producto de tal forma que tenemos en una columna el nombre del producto y en otra un valor numérico con el stock disponible.

Vamos a agregar una tercera columna que nos alerte cuando el stock esté por debajo de 5, para que realicemos un pedido.

En este caso podemos utilizar la función SI ya que con una operación lógica podemos saber si el dato de a columna con el stock es menor que 5.

Esta será la prueba lógica y el valor si verdadero será el mensaje que queremos mostrar, por ejemplo: Realizar pedido. Así quedaría el resultado:

Ejemplo de función SI
Ejemplo de función SI

Por tanto, suponiendo que el valor numérico del stock de camisas se encuentre en la celda C12, la fórmula a introducir en la celda D12 sería:

=SI(C12<5;”Realizar pedido”)

Que, al auto rellenar hacia abajo, se generaría ese FALSO como resultado al ser el stock de pantalones mayor o igual que 5. Si hubiéramos introducido otro mensaje para el valor si falso, en la celda D13 aparecería ese mensaje en lugar de FALSO.

Funciones lógicas Y (AND) y O (OR)

Las funciones Y y O son funciones lógicas que nos permiten validar varias comparaciones entre sí. En consecuencia, podemos hacer funciones SI que tengan en cuenta más de una prueba lógica.

Estas funciones tienen como parámetro el número de pruebas lógicas que queramos.

La función Y devuelve verdadero si los resultados de todas las condiciones en su interior son verdaderos.

La función O devuelve verdadero si alguna de las condiciones en su interior es verdadero.

Ejemplo:

Dando continuidad al ejemplo anterior, ahora vamos a considerar que para que podamos hacer un pedido de ropa, aparte de tener el stock por debajo de 5, sea necesario que exista disponibilidad de proveedor. Esto lo vamos a controlar indicando el valor 1 en la columna A.

Para resolver este problema, podemos incorporar una nueva condición dentro de la función SI anterior de forma que además se compruebe si el valor de la celda AX es igual a 1. Como se deben cumplir ambas condiciones para proceder a realizar un pedido, usaremos la función Y.

Ejemplo de función SI con Y
Ejemplo de función SI con Y

Ahora hemos puesto el valor del stock tanto de pantalones como de camisas por debajo de 5, pero sólo tenemos proveedor disponible para las comisas, por ello, sólo podemos realizar pedido de camisas. La fórmula para la fila 12 sería:

=SI(Y(C12<5;A12=1);”Realizar pedido”)

Análisis sobre bases de datos

En las hojas de cálculo disponemos de una serie de herramientas para analizar y visualizar bases de datos de forma cómoda. Son los filtros.

Uso de filtros en Excel (Es igual en otras herramientas de hoja de cálculo)

Con los filtros podemos ordenar tablas de datos por columnas específicas por orden alfabético o por orden creciente o decreciente si se trata de números.

También podemos mostrar sólo los elementos que cumplan una determinada condición en una columna y ocultar el resto para ganar legibilidad.

Existen varios tipos de filtro que podemos aplicar en cualquier herramienta. Los filtros automáticos y los filtros avanzados. Vamos a centrarnos en los primeros.

Para crear un filtro automático debemos seleccionar una tabla con los datos sobre los que deseemos aplicar el filtro. Esta tabla debe contener una primera fila con las cabeceras de cada columna de datos, a las cuales pondremos un nombre descriptivo.

Para aplicar el filtro en MS Office, una vez seleccionada la tabla, nos dirigimos a la pestaña Datos, sección ordenar y filtrar de la cinta y hacemos clic sobre el icono grande del embudo.

En cualquier aplicación clásica suele estar en Datos –> Filtro. Aunque en general también lo podemos identificar por el icono del embudo.

Una vez aplicado el filtro, aparecerán unas flechas con menús desplegables en las cabeceras de las columnas. Con ellas podemos aplicar esas ordenaciones o filtrado que hemos mencionado.

Ejemplo:

Siguiendo el ejemplo anterior de la venta de ropa, disponemos de una tabla en la que tenemos datos sobre las ventas en unidades y valor que ha realizado cada vendedor en cada categoría de producto y por meses.

Para poder visualizar la información de forma cómoda aplicamos un filtro, quedando de la siguiente manera:

Uso de filtros en Excel
Uso de filtros en Excel

Ahora vamos a visualizar sólo las ventas de Susana. Hacemos clic en la flecha al lado de la cabecera Vendedor y en la parte inferior deseleccionamos a Diego y a Carlos.

Filtrando la tabla con excel
Filtrando la tabla con excel

Tras hacer clic en aceptar, este es el resultado:

Resultado de Tabla filtrada
Resultado de Tabla filtrada

Ahora vamos a ordenar los valores de la columna ventas en orden creciente. Para ello, desplegamos la flecha del filtro en la columna Ventas y hacemos clic sobre el primer icono, Ordenar de menor a mayor..

Este sería el resultado:

Tabla filtrada y ordenada
Tabla filtrada y ordenada

Una vez que teneos aplicado un filtro, es interesante fijarse en que aparece un embudo en lugar de la flecha en la columna por la que hemos filtrado la información y además los números del margen en las filas no son correlativos.

Esto nos está indicando que estamos viendo una vista filtrada y que por lo tanto no estamos viendo todos los datos.

Para volver a ver todos los elementos simplemente hacemos la operación a la inversa.

Esto es sólo un poquito de lo que podemos hacer sobre bases de datos. Adicionalmente podemos diseñar tablas cruzadas y dinámicas, que son mucho más potentes.

Adorable automatización con Macros

Las macros sirven para automatizar tareas. Se trata de una secuencia de pasos que podemos grabar o programar y que podremos ejecutar en otros documentos.

Los documentos sobre los que ejecutamos las macros deben de ser compatibles. Es decir, que si tenemos una macro que por ejemplo aplica un formato de fuente sobre la cabecera de una tabla con 4 columnas, si tratamos de ejecutarla sobre otra que tenga cinco columnas, no funcionará correctamente.

Por tanto, sirven para aplicar tareas rutinarias.

Caso de uso:

Imagina que, en la empresa de venta de ropa, todos los meses alguien del departamento de venta descarga de la aplicación informática de gestión de ventas una base de datos con las ventas realizadas en el último período.

Esa base de datos no tiene formato alguno, por tanto, debemos hacerla más legible para que en los encargados puedan visualizar la información más cómodamente.

Como la base de datos que obtenemos es un archivo csv que siempre tiene la misma estructura, podemos crear una macro para aplicar de forma automática esos ajustes de formato sobre el archivo exportado en la aplicación.

En las aplicaciones clásicas hay dos maneras de crear macros.

  1. Mediante una opción que nos permite grabar lo que hacemos y guardarlo para reproducirlo posteriormente.
  2. Utilizando un lenguaje de programación, que en el caso de Excel es VBA. Se trata de una adaptación de Visual Basic que Microsoft ha desarrollado especialmente para programar con Excel.

En cambio, en Google Docs, tenemos disponible el Editor de secuencias de Comando, que no sólo se puede usar para crear macros en las hojas de cálculo, sino también para cualquiera de las otras aplicaciones de Google Docs y muchas más.

La forma de desarrollar estas funcionalidades en este caso es un lenguaje que Google ha puesto a disposición de la comunidad y está basado en lenguajes de programación web como Java Script. El editor está disponible en el menú Herramientas.

En las páginas de ayuda para desarrolladores encontrarás información adicional de Google sobre como programar apps con esta herramienta.

También te invito a echar un vistazo a mis artículos sobre JavaScript, lo que te dará la base necesaria para poder hacer cosas sorprendentes.

Crear una macro con el grabador de macros de MS Excel

Para grabar una macro, nos dirigimos a Vista à Macros –> Grabar Macro…

Aparecerá una ventana en la que ponemos un nombre a nuestra macro, podemos asignarle una tecla rápida, una descripción, y en el desplegable sobre a descripción podemos especificar si guardarla sólo para este libro, si creamos un libro de macros con un nombre que queramos o bien, si lo guardamos en el libro de macros personal.

Las dos últimas opciones permiten la utilización de la macro desde otros archivos de Excel en el mismo equipo.

Una vez hecho esto, cualquier acción que hagamos sobre el documento abierto quedará grabado en la macro. Para detener la grabación, nos dirigimos a la misma ubicación Vista –> Macros –>, pero en esta ocasión aparecerá a opción Detener grabación en lugar de Grabar Macro…

Ejecutar una macro en MS Excel

Para ejecutar una macro disponible nos dirigimos a Vista à Macros à Ver macros.

Una vez allí aparecerá una ventana como esta:

Ejecutar macro en Excel
Ejecutar macro en Excel

En esa ventana aparecen las macros que tenemos disponibles. Como podemos observar, en la parte inferior disponemos de un desplegable que nos permite seleccionar los orígenes donde encontrar macros. En este caso dice, Todos los libros abiertos, pero podríamos especificar un libro de macros específico.

En el listado principal aparecen las macros disponibles actualmente y en los botones laterales podemos aplicar la acción a seguir. Ejecutar aplicará la macro sobre el documento activo.

Es decir, todas las acciones que han sido grabadas serán ejecutadas una tras otra sin detenerse. Si hay algún error, se mostrará una alerta, pero aun así, Excel continúa ejecutando las acciones programadas siempre que pueda.

Para tratar de detectar los posibles errores que puedan aparecer, podemos hacer la ejecución paso a paso.

Como se puede ver en esa misma ventana, con el resto de botones podemos eliminar, acceder a las opciones de la macro, que os permitirá editar la tecla de acceso rápido o la descripción y modificar la macro.

Esto último es tanto una ventaja como una desventaja, puesto que al modificar la macro no hay otra forma que editar el código fuente en VBA que se ha generado cuando grabábamos las acciones.

La ventaja de esto es que, si estamos aprendiendo VBA y teneos dudas sobre cómo programar algo, vamos a poder hacerlo con la grabadora de macros y posteriormente editar y ver el código.

En la siguiente captura podemos ver el entorno de trabajo para programar las macros y el código de una sencilla macro llamada Macro2 que realiza tres acciones: Seleccionar un rango, aplicar negrita, seleccionar otra celda.

Programación de macros con VBA
Programación de macros con VBA

Relacionar datos con otras aplicaciones

Excel, y en general las aplicaciones de hoja de cálculo, permiten la importación de datos desde muchas y muy diversas posibles fuentes de datos.

Ya hemos visto previamente que un formato común que podemos abrir con cualquier aplicación de este tipo son los archivos csv.

Además, casi cualquier aplicación de otra índole va a tener la capacidad de exportar sus datos en este formato, con lo cual, con esto ya contamos con posibilidades casi ilimitadas de trabajar con datos procedentes de otras fuentes.

Pero además, concretamente Excel, dispone de asistentes para la importación de una gran cantidad de aplicaciones y formatos. Si nos dirigimos a Datos –> Obtener datos externos veremos lo siguiente:

Conectar fuentes de datos en Excel
Conectar fuentes de datos en Excel

Como se puede observar, podemos importar datos desde MS Access, que es la aplicación de la suite de Microsoft que nos permite trabajar con bases de datos de forma visual.

Desde un repositorio en Internet, desde un archivo de texto (csv), desde SQL Server, es el sistema gestor de bases de datos para entorno profesional de Microsoft, desde archivos XML, que es un formato estándar web, desde otras bases de datos en otros sistemas gestores de base de datos como MySQL, muy usual en páginas web u Oracle, la empresa proveedora de servicios de base de datos más poderosa que alberga las bases de datos más grandes del mundo.

Esas y otras opciones que no hemos nombrado son las posibilidades que tenemos. Además, ya sabemos que mediante las extensiones o componentes podríamos ampliar esta lista si fuera necesario.

¡Recibe un cupón de un 10% sólo por darte de alta!

Usaré la info para informarte de novedades y ofertas sobre mis cursos y servicios.

* Más info en el Aviso legal.

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Artículos de la misma categoría

crossmenu
linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram