AcreditaPro
← Excel Avanzado: Datos, Fórmulas y Tablas Dinámicas

Temario: Excel Avanzado: Datos, Fórmulas y Tablas Dinámicas

Descargar manual en PDF

Excel Avanzado: Datos, Fórmulas y Tablas Dinámicas — Material de estudio

Este curso acredita competencias avanzadas en Microsoft Excel, conforme al Marco Europeo de Competencias Digitales DigComp 3.0 (Área 3: Creación de Contenido Digital). Es una acreditación de competencias profesionales, no una obligación normativa de formación.

1. Introducción y objetivos del curso +

Este curso va más allá del uso básico de Excel y se centra en las herramientas que permiten trabajar de forma eficiente con grandes volúmenes de datos, automatizar procesos y extraer información útil de forma rápida.

Al finalizar este curso, serás capaz de:

  • Usar funciones avanzadas de búsqueda, lógica y texto.
  • Gestionar y filtrar datos con eficacia.
  • Crear y analizar Tablas Dinámicas.
  • Automatizar tareas básicas con macros.
  • Aplicar buenas prácticas en el manejo de datos empresariales.
2. Repaso: la hoja de cálculo y sus elementos +

Una hoja de cálculo de Excel está formada por celdas organizadas en filas y columnas. Cada celda se identifica por su dirección (columna + fila, por ejemplo C5). Un libro puede contener múltiples hojas, accesibles desde las pestañas en la parte inferior.

La barra de fórmulas muestra el contenido real de la celda seleccionada. Los nombres de rango (Fórmulas → Asignar nombre) permiten dar un nombre descriptivo a un conjunto de celdas para usarlo en fórmulas, lo que mejora la legibilidad.

3. Referencias relativas, absolutas y mixtas +

Referencias en Excel: relativa, absoluta y mixta

Entender las referencias es fundamental para trabajar con fórmulas complejas:

  • Relativa (A1): se ajusta automáticamente al copiar la fórmula. Si copias =A1+B1 una fila abajo, se convierte en =A2+B2.
  • Absoluta ($A$1): no cambia al copiar. Si copias =$A$1+B1 una fila abajo, sigue siendo =$A$1+B2.
  • Mixta ($A1 o A$1): solo fija la columna o la fila. $A1 fija la columna A pero la fila se ajusta; A$1 fija la fila 1 pero la columna se ajusta.

Para convertir una referencia de relativa a absoluta o mixta, selecciona la referencia en la barra de fórmulas y pulsa F4 — cada pulsación cicla por los cuatro tipos.

4. Funciones de búsqueda: BUSCARV y BUSCARH +

BUSCARV(valor_buscado; tabla; núm_columna; [ordenado]) busca un valor en la primera columna de una tabla y devuelve el dato de la columna indicada. El parámetro [ordenado] debe ser FALSO para búsqueda exacta.

Ejemplo: =BUSCARV(A2; $D$2:$F$100; 2; FALSO) busca el valor de A2 en la primera columna del rango D2:F100 y devuelve el valor de la segunda columna.

BUSCARH funciona igual pero busca en la primera fila en vez de en la primera columna.

Limitación importante de BUSCARV: solo puede buscar en la primera columna de la tabla y devolver datos de columnas a la derecha, nunca a la izquierda. Para búsquedas más flexibles, usa la combinación INDICE+COINCIDIR.

5. Funciones lógicas: SI, Y, O +
  • =SI(condición; valor_si_verdadero; valor_si_falso): devuelve un valor u otro según si la condición se cumple. Se pueden anidar varios SI para evaluar múltiples condiciones.
  • =Y(condición1; condición2; ...): devuelve VERDADERO solo si todas las condiciones son verdaderas.
  • =O(condición1; condición2; ...): devuelve VERDADERO si al menos una condición es verdadera.

Ejemplo combinado: =SI(Y(A1>10;B1<5);"Cumple ambas";"No cumple") devuelve "Cumple ambas" solo si A1 es mayor que 10 Y B1 es menor que 5.

6. Funciones condicionales: SUMAR.SI y CONTAR.SI +
  • =SUMAR.SI(rango; criterio; [rango_suma]): suma las celdas de rango_suma solo donde el rango cumple el criterio. Si rango_suma se omite, suma el propio rango.
  • =SUMAR.SI.CONJUNTO(rango_suma; rango1; criterio1; rango2; criterio2; ...): igual pero con múltiples criterios.
  • =CONTAR.SI(rango; criterio): cuenta cuántas celdas del rango cumplen el criterio.
  • =CONTAR.SI.CONJUNTO(...): igual con múltiples criterios.

Ejemplo: =SUMAR.SI(B2:B100;"Madrid";C2:C100) suma los valores de C solo donde B contiene "Madrid".

7. Funciones de texto +

Las funciones de texto permiten manipular y limpiar datos de tipo cadena:

  • =TEXTO(número; formato): convierte un número a texto con un formato específico. Ejemplo: =TEXTO(A1;"DD/MM/AAAA").
  • =CONCATENAR(texto1; texto2; ...) o el operador &: une varias cadenas de texto.
  • =IZQUIERDA(texto; núm_caracteres) / =DERECHA / =EXTRAE: extrae parte de una cadena.
  • =LARGO(texto): devuelve el número de caracteres.
  • =MAYUSC / MINUSC / NOMPROPIO: convierte a mayúsculas, minúsculas o primera letra mayúscula.
  • =ESPACIOS(texto): elimina espacios dobles y espacios al inicio/final (muy útil al importar datos externos).
8. Funciones de fecha y hora +

Excel almacena las fechas como números enteros, lo que permite hacer cálculos con ellas:

  • =HOY(): devuelve la fecha actual (se actualiza automáticamente).
  • =AHORA(): devuelve la fecha y hora actuales.
  • =DIAS(fecha_fin; fecha_inicio): calcula la diferencia en días.
  • =SIFECHA(fecha_inicio; fecha_fin; unidad): calcula la diferencia en años ("Y"), meses ("M") o días ("D").
  • =AÑO(fecha) / =MES(fecha) / =DIA(fecha): extrae la parte de la fecha.
  • =DIASEM(fecha; tipo): devuelve el día de la semana (1=domingo o 1=lunes según el tipo).
9. Gestión de datos: ordenar y filtrar +

Ordenar: selecciona cualquier celda del rango y ve a Datos → Ordenar. Puedes ordenar por múltiples columnas con prioridades.

Autofiltro: selecciona una celda del encabezado y ve a Datos → Filtro. Aparecen desplegables en cada columna para filtrar por valor, condición (mayor que, contiene...) o color.

Filtro avanzado: permite filtrar con criterios más complejos definidos en una zona separada de la hoja, y copiar los resultados a otra ubicación.

Quitar duplicados: Datos → Quitar duplicados elimina las filas con valores idénticos en las columnas seleccionadas — muy útil para limpiar bases de datos importadas.

10. Validación de datos +

La validación de datos (Datos → Validación de datos) restringe qué se puede escribir en una celda, evitando errores de entrada:

  • Lista desplegable: solo permite elegir entre valores predefinidos.
  • Número entero / decimal: solo acepta números dentro de un rango.
  • Fecha: solo acepta fechas dentro de un rango.
  • Longitud del texto: limita el número de caracteres.

Se puede configurar un mensaje de entrada (que aparece al seleccionar la celda) y un mensaje de error personalizado cuando el usuario introduce un valor no permitido.

11. Tablas estructuradas de Excel +

Convertir un rango en una tabla estructurada (Insertar → Tabla o Ctrl+T) tiene varias ventajas:

  • Los encabezados siempre están visibles al desplazarse.
  • Las fórmulas se extienden automáticamente al añadir filas.
  • Las referencias en fórmulas usan nombres de columna en vez de direcciones, lo que las hace más legibles.
  • El filtrado y ordenación están integrados.
  • Las tablas se expanden automáticamente al añadir datos junto a ellas.

Cada tabla tiene un nombre (por defecto Tabla1, Tabla2...) que puede personalizarse desde la pestaña Diseño de tabla.

12. Introducción a las Tablas Dinámicas +

Estructura de una Tabla Dinámica

Una Tabla Dinámica es una herramienta que permite resumir, analizar y explorar grandes volúmenes de datos sin escribir ninguna fórmula. Con unos pocos clics, puedes obtener totales, porcentajes, comparativas y tendencias de cientos o miles de filas de datos.

Son especialmente útiles cuando necesitas responder preguntas del tipo: ¿Cuánto vendió cada vendedor por mes? ¿Qué producto tuvo mayor margen por región?

13. Crear y configurar una Tabla Dinámica +

Para crear una Tabla Dinámica: haz clic en cualquier celda de tus datos y ve a Insertar → Tabla Dinámica. Elige si quieres colocarla en una hoja nueva o en la misma hoja.

El panel de campos (a la derecha) tiene cuatro zonas:

  • Filtros: los campos aquí actúan como filtros globales para toda la tabla.
  • Columnas: los distintos valores del campo se muestran como columnas.
  • Filas: los distintos valores del campo se muestran como filas.
  • Valores: aquí va el campo que quieres resumir (suma, media, cuenta, máximo...).

Para cambiar el cálculo de un campo en Valores: clic derecho sobre él → Configuración de campo de valor.

14. Filtros y segmentaciones en Tablas Dinámicas +

Además de los filtros integrados en las filas y columnas, las Tablas Dinámicas permiten añadir Segmentaciones (Analizar → Insertar Segmentación): botones visuales que filtran la tabla con un solo clic, mucho más intuitivos que los filtros desplegables tradicionales.

Las Escalas de tiempo son segmentaciones especiales para campos de fecha, que permiten filtrar por año, trimestre, mes o día con un control deslizante.

15. Gráficos Dinámicos +

Un Gráfico Dinámico está vinculado a una Tabla Dinámica y se actualiza automáticamente cuando se cambian los campos o filtros de la tabla. Para crearlo: haz clic en la Tabla Dinámica y ve a Analizar → Gráfico dinámico.

Los gráficos dinámicos tienen los mismos tipos de gráfico que los normales (columnas, líneas, circular...) pero incluyen botones de campo que permiten filtrar directamente desde el gráfico.

16. Formato condicional avanzado +

Más allá de los colores básicos, el formato condicional permite:

  • Barras de datos: muestra una barra proporcional al valor dentro de la celda — sin crear un gráfico de barras separado.
  • Escalas de color: colorea las celdas en un degradado según su valor (verde los altos, rojo los bajos, por ejemplo).
  • Conjuntos de iconos: muestra flechas, semáforos u otros iconos según el valor.
  • Fórmula personalizada: aplica el formato cuando una fórmula devuelve VERDADERO — permite condiciones muy complejas que no están disponibles en las opciones predefinidas.
17. Protección de hojas y libros +

Para evitar que alguien modifique partes del libro por error:

  • Proteger hoja (Revisar → Proteger hoja): bloquea todas las celdas de la hoja. Previamente, desbloquea las celdas que sí quieres que sean editables (clic derecho → Formato de celdas → Protección → desmarcar Bloqueada).
  • Proteger libro (Revisar → Proteger libro): impide añadir, eliminar o mover hojas.
  • Ambas protecciones admiten contraseña opcional.

La protección de hoja no cifra el fichero — si el objetivo es impedir el acceso al contenido, usa Archivo → Info → Proteger libro → Cifrar con contraseña.

18. Importar y exportar datos +

Excel puede importar datos desde múltiples fuentes:

  • Desde texto/CSV: Datos → Obtener datos → Desde archivo → Desde texto/CSV. El asistente de importación permite definir el delimitador y el tipo de cada columna.
  • Desde web: Datos → Obtener datos → Desde otras fuentes → Desde web. Permite importar tablas directamente de páginas web.
  • Power Query (Datos → Obtener y transformar datos): herramienta integrada para importar, limpiar y transformar datos de forma repetible, guardando los pasos de transformación.

Para exportar a CSV: Archivo → Guardar como → CSV (delimitado por comas). Ten en cuenta que al guardar como CSV se pierde todo el formato, las fórmulas y las hojas adicionales.

19. Herramientas de análisis: Buscar objetivo y Escenarios +

Buscar objetivo (Datos → Análisis de hipótesis → Buscar objetivo): responde a la pregunta "¿qué valor necesita tener esta celda para que la fórmula de aquella otra dé un resultado concreto?". Por ejemplo: ¿cuántas unidades hay que vender para alcanzar 50.000 € de ingresos?

Escenarios (Datos → Análisis de hipótesis → Administrador de escenarios): permite guardar diferentes conjuntos de valores de entrada y comparar sus resultados — útil para análisis "qué pasa si" con presupuestos o previsiones.

20. Macros: automatizar tareas repetitivas +

Una macro es una secuencia de acciones grabadas que se puede reproducir con un solo clic. Para grabar una: Vista → Macros → Grabar macro. Realiza las acciones y luego detén la grabación. La macro queda guardada y se puede ejecutar desde Vista → Macros → Ver macros.

Las macros se guardan en el libro actual (extensión .xlsm, que habilita macros) o en el libro de macros personal (disponible en cualquier libro). Las macros se escriben en VBA (Visual Basic for Applications) — aunque no es necesario saber programar para grabarlas, entender el código básico ayuda a editarlas.

21. Buenas prácticas con datos en Excel +
  • Una tabla, una hoja: no mezcles varias tablas de datos independientes en la misma hoja.
  • Los datos van en filas, los encabezados en la primera fila: sin celdas combinadas en los encabezados, que rompen los filtros y las tablas dinámicas.
  • Usa el formato correcto desde el principio: los números como números, las fechas como fechas — no como texto.
  • Documenta las fórmulas complejas con comentarios (clic derecho → Insertar comentario).
  • Haz copias de seguridad antes de ejecutar macros o transformaciones masivas.
22. Resumen de puntos clave +
  • Las referencias absolutas ($A$1) no cambian al copiar fórmulas; las relativas sí.
  • BUSCARV busca en la primera columna de una tabla y devuelve datos de las columnas a la derecha.
  • Las Tablas Dinámicas resumen grandes volúmenes de datos sin fórmulas.
  • El formato condicional facilita la identificación visual de datos relevantes.
  • Las macros automatizan tareas repetitivas sin necesidad de programar.
23. Anexo normativo: marco de referencia de este curso +
  • DigComp 3.0 — Marco Europeo de Competencias Digitales (Comisión Europea, diciembre 2025): Área 3 (Creación de Contenido Digital) y Área 1 (Alfabetización en Información y Datos).
  • Plan Nacional de Competencias Digitales — España Digital 2026.

El certificado que acredita la superación de este curso es un documento privado de formación, no un certificado oficial de la Administración.

24. Glosario de términos +
  • Referencia absoluta ($A$1): referencia que no cambia al copiar la fórmula; siempre apunta a la misma celda.
  • Referencia mixta ($A1 o A$1): referencia que fija solo la columna o solo la fila al copiar la fórmula.
  • BUSCARV: función de Excel que busca un valor en la primera columna de una tabla y devuelve el dato de la columna indicada.
  • Tabla Dinámica: herramienta de Excel que resume y analiza grandes volúmenes de datos sin necesidad de escribir fórmulas.
  • Segmentación: botón visual en una Tabla Dinámica que permite filtrar los datos con un solo clic.
  • Formato condicional: formato que se aplica automáticamente a las celdas según su valor (colores, iconos, barras de datos).
  • Macro: secuencia de acciones grabadas en Excel que se pueden reproducir automáticamente para agilizar tareas repetitivas.
  • VBA: Visual Basic for Applications, lenguaje de programación usado para escribir y editar macros en Excel.
  • Power Query: herramienta integrada en Excel para importar, limpiar y transformar datos de forma repetible.
  • Tabla estructurada: rango de datos de Excel convertido en tabla con nombre propio, filtrado integrado y fórmulas que se extienden automáticamente.
  • Validación de datos: restricción que controla qué tipo de información puede introducirse en una celda (lista, número, fecha, etc.).
  • Buscar objetivo: herramienta de Excel que calcula qué valor de entrada produce un resultado concreto en una fórmula.
  • Escenario: conjunto de valores de entrada guardado en Excel para comparar diferentes hipótesis de un modelo.
  • Gráfico Dinámico: gráfico vinculado a una Tabla Dinámica que se actualiza automáticamente al cambiar los filtros o campos.

¿Listo para comprobarlo?

Empieza el examen cuando quieras

Formas más ligeras de repasar antes del examen: