CURSO EXCEL
Nivel Intermedio
MANUAL
Feliz comienzo al Curso de Excel Intermedio, el presente documento es el manual del módulo, está
diseñado para aquellos que ya poseen conocimientos básicos de Excel y buscan llevar sus
habilidades al siguiente nivel, a lo largo de cinco videos instructivos y este manual de paso a paso,
exploraremos herramientas y técnicas más avanzadas que le permitirán aprovechar al máximo el
potencial de esta poderosa herramienta, abarcaremos temas desde fórmulas complejas hasta la
creación de tablas dinámicas, aprenderás cómo analizar, organizar y presentar sus datos de manera
eficiente y efectiva, al finalizar este curso, tendrás una comprensión sólida de las funciones
intermedias de Excel y estarás listo para enfrentarse a desafíos más avanzados con confianza y
habilidad. ¡Iniciemos!
Video 1 - Introducción a los rangos y utilización en formulas y funciones
Comprensión y uso de referencias de celda (Absolutas, relativas y mixtas)
Introducción a los rangos y utilización en
formulas y funciones
Uno de los aspectos fundamentales para trabajar con datos en Excel es la capacidad de utilizar
rangos y rangos con nombre en fórmulas y funciones, exploraremos los conceptos básicos de los
rangos y rangos con nombre y cómo pueden simplificar y optimizar su trabajo en Excel.
Un rango en Excel es un conjunto de celdas contiguas que pueden ser seleccionadas y manipuladas
como un grupo, los rangos pueden ser de cualquier tamaño y forma, siempre y cuando las celdas
estén seguidas entre sí, el rango seleccionado se resalta en color gris, los rangos son útiles para
realizar cálculos en un conjunto específico de datos y para mantener la coherencia en la
organización de la hoja de cálculo.
Los rangos con nombre son rangos a los que se les ha asignado un nombre específico para
facilitar su identificación y uso en fórmulas y funciones, estos nombres pueden ser más
descriptivos y significativos que las referencias de celda estándar, lo que facilita la
comprensión de las fórmulas y evita errores al realizar cálculos, además, los rangos con
nombre son absolutos por defecto, lo que significa que su referencia no cambiará al copiar o
mover la fórmula.
Utilizar rangos y rangos con nombre en fórmulas y funciones es una habilidad esencial para
cualquier usuario de Excel, estos permiten una mayor claridad en la hoja de cálculo, facilitan
la actualización y modificación de los datos y mejoran la eficiencia al realizar cálculos en
grandes conjuntos de datos, cuando se domina el uso de rangos y rangos con nombre se
aumenta la productividad en la gestión y análisis de datos.
Rangos rectangulares: Son los rangos más comunes y consisten en un conjunto de celdas
contiguas organizadas en un rectángulo. Por ejemplo, un rango rectangular podría incluir las
celdas B2 a G6, que se denotaría como A1:D8.
Rangos de una sola fila o columna: Estos rangos incluyen todas las celdas en una única fila
o columna. Por ejemplo, un rango de una sola fila sería A3:G3, mientras que un rango de una
sola columna sería D2:D63.
Rangos con nombre: Estos son rangos a los que se les ha asignado un nombre descriptivo
para facilitar su uso en fórmulas y funciones, ejemplo, si se tiene un conjunto de datos en las
celdas B1 a B10 que representan las ventas de un producto en el mes de enero, se podría
asignar el nombre "Enero" al rango B1:B10, se puede utilizar "Ventas" en lugar de B1:B10
en las fórmulas.
Ahora para observar la lista de rango nombrados en la hoja de trabajo, se debe hacer clic en
la flecha del extremo derecho del cuadro de nombres.
Seleccionar varios rangos en Excel : Con la tecla “Ctrl” podemos dar formato a varios rangos
que no estén contiguos, para ello primero seleccionas el rango de celdas, luego sostienes la
tecla “Ctrl” y con el mouse vas seleccionando otro rango, para terminar sueltas la tecla “Ctrl”
Rangos en tablas: Cuando se crea una tabla en Excel 365, los rangos dentro de la tabla se
vuelven más fáciles de manejar, puede hacer referencia a columnas y filas específicas de la
tabla utilizando nombres estructurados, ejemplo, si tiene una tabla llamada "Ventas" con una
columna "cantidades”, puede hacer referencia a la columna de ingresos con la siguiente
notación: Ventas[Cantidades].
Cuando se requiera copiar o mover rangos se puede realizar pulsando la tecla “Ctrl + C” para copiar
y “Ctrl + V” para pegar en la celda destino, quedando los datos tanto en la celda original como
destino, igualmente, para mover rangos de las celdas de origen a nuevas de destino lo puedes
realizar pulsando la tecla “Ctrl + X” conocido como el comando cortar, es decir los datos serán
removidos a la nueva posición.
“Ctrl + C” para copiar y “Ctrl + V” pegar
“Ctrl + X” Cortar
Comprensión y uso de referencias de celda
(Absolutas, relativas y mixtas)
Uno de los fundamentos de trabajar con éxito en Excel es comprender y utilizar
correctamente las referencias de celdas, las cuales son esenciales en la creación y
modificación de fórmulas y funciones, puesto que proporcionan la base para realizar
cálculos precisos y eficientes.
Las referencias de celda en Excel permiten identificar y acceder a valores específicos en una
hoja de cálculo, se compone de la combinación de una letra de columna y un número de
fila, como "A1" o "B3", existen tres tipos principales de referencias de celda: relativas,
absolutas y mixtas.
Referencias de celda relativas: Las referencias de celda relativas son el tipo más común de
referencia en Excel, estas referencias cambian automáticamente cuando se copia una
fórmula de una celda a otra, en lugar de apuntar a una celda específica de forma fija, una
referencia relativa se actualiza en función de la posición de la celda en la que se copia la
fórmula, lo que permite reutilizar fácilmente fórmulas en diferentes partes de la hoja de
cálculo, ajustándose en función de la posición de la celda donde se copia la fórmula,
ejemplo, si se tiene la fórmula "=A1+B1" en la celda C1 y se copia a la celda C2, la referencia
de celda se ajustará automáticamente a "=A2+B2".
Otro ejemplo, Imaginemos que se desea calcular el impuesto (IVA) aplicado a diferentes
precios de productos en una columna, supongamos que los precios de los productos están
en la columna A, y la tasa de IVA es del 19%.
En la celda B1, puede ingresar la fórmula "=A1 * 0.19" para calcular el impuesto aplicado al
precio del primer producto, al copiar la fórmula de la celda B1 a las celdas B2, B3, etc., las
referencias de celda se ajustarán automáticamente para calcular el impuesto aplicado a los
precios de los siguientes productos, por ello al copiar la fórmula a la celda B2, la fórmula se
actualizará a "=A2 * 0.15", y así sucesivamente.
Referencias de celda absolutas: Las referencias de celda absolutas se utilizan para fijar una
referencia a una celda específica, de modo que no cambie cuando se copia la fórmula a
otras celdas, para crear una referencia absoluta, se coloca un signo de dólar ($) o presiona
F4 una vez antes de la letra de la columna y/o el número de la fila, ejemplo, la fórmula
"=A1+$B$1" en la celda C1, al copiarse a la celda C2, se ajustará solo la referencia relativa,
quedando como "=A2+$B$1".
Referencias de celda mixtas: Las referencias de celda mixtas son una combinación de
referencias relativas y absolutas, en una referencia mixta, solo se fija la columna o la fila,
pero no ambas, ejemplo, en la fórmula "=A1+$B1" en la celda C1, al copiar la fórmula a la
celda C2, se ajustará la referencia relativa, mientras que la columna en la referencia
absoluta permanecerá constante, resultando en "=A2+$B2", para crear una referencia
mixta, se coloca un signo de dólar ($) o presiona F4 dos veces antes de la letra de la columna
y/o el número de la fila, las referencias de celda absolutas permiten mantener una
referencia constante a una celda específica mientras se copia la fórmula a lo largo de la hoja
de cálculo.
Dominar el uso de referencias de celda en Excel es fundamental para realizar cálculos
precisos y eficientes, comprender la diferencia entre referencias relativas, absolutas y
mixtas, y cómo aplicarlas en distintos escenarios, puede marcar una gran diferencia en la
organización y el análisis de datos en hojas de cálculo.
Video 2 - Realización de cálculos con funciones VLOOKUP, IF e INDEX/MATCH.
Agrupar y desagrupar datos en hojas de trabajo para facilitar el análisis.
Realización de cálculos con funciones
VLOOKUP, IF e INDEX/MATCH.
Algunas de las funciones más poderosas y versátiles de Excel para realizar cálculos y
manipular datos son: VLOOKUP, IF e INDEX/MATCH, estas funciones permiten buscar,
comparar y analizar datos de manera eficiente en hojas de cálculo de gran tamaño y con
múltiples condiciones.
La función VLOOKUP (búsqueda vertical) o BUSCARV es una herramienta valiosa para buscar
datos dentro de una tabla o matriz, busca un valor en la primera columna de un rango y
devuelve el valor en la misma fila desde una columna especificada, la función VLOOKUP
resulta especialmente útil al trabajar con grandes conjuntos de datos cuando necesitamos
extraer información específica basada en un criterio de búsqueda.
Se presentan algunas limitaciones con el uso de la función BUSCARV porque solo se puede
buscar un valor de izquierda a derecha, es decir que la columna que contiene el valor a
buscar debe estar siempre situado a la izquierda de la columna, por consiguiente si la hoja
de trabajo no se encuentra de dicha manera es mejor que realice la combinación de las
funciones INDICE Y COINCIDIR / INDEX y MATCH
Ejemplo de la función VLOOKUP (búsqueda vertical) o BUSCARV, Un analista de datos
trabaja en el departamento de recursos humanos de una empresa y necesita buscar
información específica sobre los empleados, como su salario, en función de su número de
identificación, el analista crea una hoja de cálculo con tres columnas Número de
Identificación (A), Nombre del Empleado (B) y Salario (C), el analista pega los números de
identificación en la columna A, los nombres de los empleados en la columna B y los salarios
en la columna C, en otra hoja de cálculo o en una celda vacía, el analista ingresa el número
de identificación del empleado del cual desea obtener información sobre el salario.
Ahora, utilizando la función VLOOKUP o BUSCARV, el analista crea una fórmula para buscar
el salario del empleado en función del número de identificación ingresado, la fórmula sería:
=BUSCARV(F2, A:C, 3, FALSO), donde F2 contiene el número de identificación del empleado.
Al presionar “Enter”, la función VLOOKUP o BUSCARV devuelve el salario del empleado
correspondiente al número de identificación ingresado, proceso de la formula es el
siguiente:
Ubicarse en la celda:
Buscar la función BUSCARV y hacer doble clic
Seleccionar el número de identificación a buscar que se encuentra en la celda F2, luego
seleccionar el rango de la matriz A2:C10 y asignar el número del indicador de columna, para
este caso la columna C tendrá el número 3 porque la A es 1 y B es 2, lo anterior separado
por ;
Para buscar coincidencias exactas se escribe hace clic en FALSO
La función IF (si) es una de las funciones lógicas más empleadas en Excel, se usa para
comparar valores, nos permite realizar cálculos condicionales en función de criterios
establecidos, así mismo, evalúa una condición y devuelve un valor, si la condición es
verdadera y otro valor si la condición es falsa, esta función es de gran utilidad en situaciones
donde se deben tomar decisiones según ciertos criterios o condiciones.
Ejemplo de la función IF o SI con BUSCARV, Una persona trabaja en una empresa de
comercio electrónico y necesita comparar los precios de los productos ofrecidos por dos
proveedores diferentes, la empresa dispone de una lista de códigos de productos en una
tabla de Excel y desea encontrar el proveedor con el precio más bajo para cada producto.
Para ello se puede crear una hoja de cálculo con tres columnas Código del Producto (A),
Precio Proveedor 1 (B) y Precio Proveedor 2 (C), para encontrar el proveedor con el precio
más bajo para cada producto, se crea una nueva columna (D) y utiliza la función VLOOKUP
o BUSCARV junto con la función IF o SI, la fórmula sería: =SI(BUSCARV(A2; A:C; 2; FALSO) <
BUSCARV(A2; A:C; 3; FALSO); "Proveedor 1"; "Proveedor 2") lo anterior separado por ;
Seguidamente, se puede copiar la fórmula hacia abajo en la columna D para aplicarla a
todos los productos en la lista, con este enfoque, se puede identificar rápidamente el
proveedor con el precio más bajo para cada producto.
Las funciones INDEX y MATCH - o INDICE Y COINCIDIR trabajan conjuntamente para llevar
a cabo búsquedas avanzadas en tablas y matrices, a diferencia de la función VLOOKUP o
BUSCARV, que solo puede buscar en la primera columna de un rango, la combinación de
INDEX y MATCH nos permite buscar en cualquier columna y fila, brindando mayor
flexibilidad, la función MATCH busca un valor en un rango y devuelve la posición relativa del
valor dentro del rango, es decir posición en donde se encuentra el valor, mientras que la
función INDEX retorna el valor de una celda dentro de un rango especificado, basándose en
el número de fila y columna proporcionados.
Ejemplo La función INDEX y MATCH o INDICE Y COINCIDIR, en el departamento de ventas
de una empresa se necesita encontrar las ventas realizadas para un producto específico en
una ciudad específica, la empresa cuenta con una tabla que muestra las ventas de cada
producto en diferentes ciudades.
Se crea una hoja de cálculo con la tabla de ventas, donde las ciudades están en las filas y los
productos en las columnas. La primera fila (A1:E1) contiene los nombres de los productos,
y la primera columna (A2:A6) contiene las ciudades.
En una celda vacía, se ingresa la ciudad y el producto para los que desea encontrar las ventas
correspondientes, por ejemplo, en F2 ingresa "Ciudad 1" y en G2 ingresa "Producto A"
Utilizando las funciones INDICE y COINCIDIR, el analista crea una fórmula para buscar las
ventas del producto y la ciudad seleccionados. La fórmula sería: =INDICE(B2:E6,
COINCIDIR(F2, A2:A6, 0), COINCIDIR(G2, B1:E1, 0)).
Al presionar Enter, la función INDICE y COINCIDIR devuelve el valor de ventas
correspondiente al producto y la ciudad ingresados.
Este ejemplo muestra cómo un analista de datos puede utilizar las funciones INDICE y
COINCIDIR para buscar información en una tabla en función de dos criterios de búsqueda,
en este caso, el producto y la ciudad.
Otro ejemplo sería el departamento de ventas de una empresa y necesita encontrar las
ventas realizadas para un producto específico en una región específica. La empresa cuenta
con una tabla que muestra las ventas de cada producto en diferentes regiones.
El analista crea una hoja de cálculo con la tabla de ventas, donde las regiones están en las
filas y los productos en las columnas. La primera fila (A1:E1) contiene los nombres de los
productos, y la primera columna (A2:A6) contiene las regiones.
En una celda vacía, el analista ingresa la región y el producto para los que desea encontrar
las ventas correspondientes (por ejemplo, en F2 ingresa "Región 1" y en G2 ingresa
"Producto A"), utilizando las funciones INDEX y MATCH, se crea una fórmula para buscar las
ventas del producto y la región seleccionados. La fórmula sería: =INDEX(B2:E6, MATCH(F2,
A2:A6, 0), MATCH(G2, B1:E1, 0)), al presionar Enter, la función INDEX y MATCH devuelve el
valor de ventas correspondiente al producto y la región ingresados.
Agrupar y desagrupar datos en hojas de
trabajo para facilitar el análisis
Agrupar y desagrupar datos en Excel es una técnica esencial para facilitar el análisis y la
comprensión de grandes conjuntos de datos, estas herramientas permiten organizar y
resumir la información de manera eficiente, facilitando la identificación de tendencias y
patrones en los datos, aprenderemos cómo agrupar y desagrupar datos en hojas de trabajo
de Excel, utilizando ejemplos.
Agrupar y desagrupar filas y columnas, para ello selecciona las filas o columnas que deseas
agrupar, haz clic en el número de la primera fila (o letra de la primera columna) y
manteniendo presionada la tecla Shift, haz clic en el número de la última fila (o letra de la
última columna) que deseas incluir en el grupo, desplazarse a la pestaña "Datos" en la cinta
de opciones de Excel y haz clic en el botón “Esquema” y luego en "Agrupar", se creará un
grupo con las filas o columnas seleccionadas.
Para desagrupar las filas o columnas, selecciona el grupo y haz clic en el botón "Desagrupar"
en la pestaña "Datos".
También puedes expandir o contraer el grupo utilizando los botones "+" y "-" que
aparecerán junto a las filas o columnas agrupadas.
Ejemplo, Imagina que tienes una base de datos de ventas que contiene información
detallada sobre las ventas de diferentes productos en diferentes regiones a lo largo de
varios años, la base de datos tiene las siguientes columnas: Año, Región, Producto, Cantidad
y Precio, para facilitar el análisis de las ventas por año, podrías agrupar los datos según el
año:
Primero, ordena los datos por la columna "Año" en orden ascendente, para ello, selecciona
la columna, ve a la pestaña "Datos" y haz clic en "Ordenar de A a Z", a continuación,
selecciona las filas que corresponden al mismo año, ejemplo, si las filas 2 a 10 corresponden
al año 2020, selecciona las filas 2 a 10, ve a la pestaña "Datos" y haz clic en "Agrupar". Ahora,
las filas 2 a 10 estarán agrupadas bajo el año 2020, utiliza los botones "+" y "-" para expandir
o contraer los grupos según sea necesario, agrupar y desagrupar datos utilizando la función
de "Esquema automático" o “Shift” + “Alt” + Flecha a la derecha agrupar y flecha izquierda
para desagrupar.
Excel también ofrece la posibilidad de crear automáticamente esquemas y agrupaciones
basados en la estructura de los datos en la hoja de cálculo, Para utilizar esta función
Selecciona el rango de datos que deseas agrupar, incluyendo las etiquetas de fila y columna,
Ve a la pestaña "Datos" y, en el grupo "Esquema", haz clic en el botón "Esquema
automático", Excel analizará la estructura de los datos y creará grupos y esquemas
automáticamente según las características de los datos, Utiliza los botones "+" y "-" para
expandir o contraer los grupos según sea necesario.
Video 3 Filtrar y ordenar datos en hojas de trabajo, incluida la ordenación
personalizada.
Creación y modificación de tablas dinámicas para resumir y analizar datos.
Filtrar y ordenar datos en hojas de trabajo
Filtrar y ordenar datos en hojas de trabajo de Excel es una habilidad esencial para analizar
datos de manera efectiva, permite a los usuarios organizar y revisar rápidamente los datos
en función de criterios específicos, exploraremos cómo filtrar y ordenar datos en Excel, así
como cómo aplicar la ordenación personalizada.
Filtrar datos en Excel implica mostrar solo las filas que cumplen ciertos criterios, para aplicar
un filtro a una tabla de datos:
Selecciona una celda dentro del rango de datos que deseas filtrar, ejemplo, si tienes datos
en el rango A1:D10, haz clic en cualquier celda dentro de ese rango, ve a la pestaña "Datos"
en la cinta de opciones de Excel, haz clic en "Filtro" en el grupo "Ordenar y filtrar",
observarás que se han agregado flechas desplegables en las celdas del encabezado de
columna, ahora realizar clic en la flecha desplegable en la columna por la que deseas filtrar,
selecciona los criterios de filtro deseados en la lista de opciones y haz clic en "Aceptar".
Ejemplo, si tienes una tabla de ventas con las columnas "Producto", "Región", "Ventas" y
"Fecha" y deseas filtrar solo las ventas del producto A, haz clic en la flecha desplegable en
la columna "Producto" y selecciona "Producto A".
Ordenar datos en Excel implica organizar las filas en función de los valores de una o más
columnas, para ordenar datos realiza:
• Selecciona una celda dentro del rango de datos que deseas ordenar.
• Ve a la pestaña "Datos" en la cinta de opciones de Excel.
• Haz clic en "Ordenar A-Z" para ordenar de forma ascendente o "Ordenar Z-A" para
ordenar de forma descendente, según la columna seleccionada.
También puedes utilizar la opción "Ordenar personalizado" para ordenar por múltiples
columnas o aplicar una ordenación personalizada:
• Haz clic en "Ordenar" en el grupo "Ordenar y filtrar" en la pestaña "Datos".
• En el cuadro de diálogo "Ordenar", puedes agregar niveles de ordenación y elegir
el orden (ascendente o descendente) para cada columna.
• Para aplicar una ordenación personalizada, selecciona la columna que deseas
ordenar y haz clic en "Ordenar según" o “Orden”.
• Elige "Orden personalizado" y selecciona la lista personalizada que deseas aplicar.
Por ejemplo, si tienes una tabla de ventas y deseas ordenar primero por "Región"
(ascendente (A-Z) ) y luego por "Ventas" (descendente (Z-A)), en el cuadro de diálogo
"Ordenar", agrega dos niveles: primero "Región" (ascendente) y luego "Ventas"
(descendente).
Al dominar el filtrado y la ordenación de datos en Excel, podrás analizar y comprender
rápidamente los datos, facilitando la toma de decisiones y la identificación de tendencias y
patrones en la información.
Creación y modificación de
tablas dinámicas para resumir y
analizar datos
Las tablas dinámicas son una herramienta excelente en Excel que permite resumir, analizar
y presentar grandes conjuntos de datos de manera efectiva, ayudan a identificar patrones
y tendencias y pueden adaptarse fácilmente para mostrar diferentes perspectivas de los
datos.
Para crear una tabla dinámica en Excel, sigue los siguientes pasos:
• Selecciona una celda dentro del rango de datos que deseas analizar, es importante
asegúrate de que los datos estén organizados en columnas con encabezados claros.
• Ve a la pestaña "Insertar" en la cinta de opciones de Excel.
• Haz clic en "Tabla dinámica" en el grupo "Tablas".
En el cuadro de diálogo "Crear tabla dinámica", verifica que el rango de datos seleccionado
sea el correcto, también puedes seleccionar dónde deseas colocar la tabla dinámica que
puede ser en una nueva hoja de cálculo o en una celda específica de la hoja de cálculo
actual, haz clic en "Aceptar" para crear la tabla dinámica.
Ejemplo, supongamos que tienes una tabla de ventas con las columnas "Producto",
"Región", "Cantidad", "Precio" y "Fecha", deseas analizar las ventas totales por producto y
región, selecciona una celda dentro del rango de datos y crea una tabla dinámica, de la
siguiente forma ve a la pestaña "Insertar" en la cinta de opciones de Excel, haz clic en "Tabla
dinámica" en el grupo "Tablas", elige para este caso que se va a crear una tabla dinámica
desde una tabla o rango, selecciona el rango y luego eliges a donde la quieres , si en una
nueva hoja o la existente.
Los campos entre las áreas de la tabla dinámica son “Filtros”, “Columna”, “Filas” y “Valores”.
En el panel "Campos de tabla dinámica", arrastra el campo "Producto" al área "Filas", el
campo "Región" al área “Filas" y el campo "Cantidad" al área "Valores", esto generará una
tabla dinámica que muestra la cantidad total de ventas por producto y región.
Si deseas analizar las ventas en términos de ingresos en lugar de cantidad, multiplica la
cantidad por el precio en una nueva columna en tus datos (por ejemplo, "Ingresos") y luego
arrastra el campo "Ingresos" al área "Valores" en lugar del campo "Cantidad". Esto te
mostrará los ingresos totales por producto y región.
Para eliminar o retirar un campo de la tabla dinámica se debe desplegar el campo haciendo
clic sobre “Quitar campo”
Para analizar las ventas por año, asegúrate de que la columna "Fecha" tenga un formato de
fecha reconocible en Excel, luego, arrastra el campo "Fecha" al área "Columnas" junto al
campo "Región". Haz clic en la flecha desplegable en el encabezado "Fecha" en la tabla
dinámica y selecciona "Agrupar". En el cuadro de diálogo "Agrupar", selecciona "Años" y
haz clic en "Aceptar".
Para conocer el origen de lo datos de algún campo de la tabla dinámica se debe hacer doble
clic sobre el campo que se requiera, enseguida Excel crea una nueva hoja de trabajo y ahí
se observa la información a detalle.
Para ocultar algunas filas puede hacer clic sobre “-“ o “+” para mostrar.
Para ingresar a la tabla dinámica una vez esté cerrado la lista de campos, se puede volver a
visualizarlos, haciendo clic derecho sobre cualquier celda de la tabla dinámica y escoger la
opción “Mostrar lista de campos” o hacer clic en la cinta de “Analizar tabla dinámica”,
seguido de “Mostrar” y “Lista de Campos”
Puedes aplicar filtros y segmentaciones de datos para analizar diferentes aspectos de la
tabla dinámica, se podría filtrar para mostrar otros datos de ser necesario.
Al realizar cambios en el origen de los datos de la base se puede actualizar la tabla dinámica
al hacer clic derecho y seleccionar “Actualizar” o desplazarse hasta la cinta de opción
“Analizar tabla dinámica” y “Actualizar”.
Para cambiar el diseño de la tabla dinámica se debe ubicar en cualquier celda de la tabla y
hacer clic en la cinta de “Diseño”, se puede cambiar estilos y diseño e incluso de informe
para la respectiva tabla.
Excel presenta la opción de tablas dinámicas recomendadas, haciendo clic sobre la opción
“Insertar” “ Tablas dinámicas recomendadas”, escoger la que se adapte a los datos que se
requiere.
Además, se puede realizar un gráfico dinámico de la tabla dinámica haciendo clic “Insertar”,
“Grafico dinámico”, al ser dinámico se puede filtrar por las diferentes opciones que
presenta el grafico, para luego aplicar diseño.
INGRESOS
1500
1000
500
0 Total
Este
Este
Norte
Norte
Este
Norte
Oeste
Oeste
Oeste
Sur
Sur
Sur
Producto A Producto B Producto C
Video 4 Utilización de la función Flash Fill para separar y formatear datos rápidamente.
Creación y uso de tablas para gestionar y analizar datos.
Utilización de la función Flash Fill para
separar y formatear datos rápidamente.
En Excel la función Flash Fill se conoce también como "Relleno automático" o "Relleno
rápido", el Relleno automático ayuda a separar, combinar o dar formato a los datos en las
celdas de manera rápida y sencilla sin necesidad de usar fórmulas, es especialmente útil
cuando se trata de datos como nombres, direcciones, números de teléfono, creación de
correos, entre otros, que requieren una manipulación o formato específico.
Ejemplo, supongamos que tenemos una lista de nombres completos en una columna y
queremos separar los nombres y apellidos en dos columnas diferentes.
Ahora, supongamos que tenemos una lista de direcciones de correo electrónico y queremos
extraer el nombre de usuario y el dominio en columnas separadas.
• Coloca el cursor en la celda C1 escribir NOMBRE Y APELLIDO.
• Cursor en la C2 y escribir "ana.lopez" (sin comillas) y presiona enter.
• Cursor en la celda C3 y escribir "juan.perez" Excel detectará el patrón y mostrará
una vista previa de los nombres de usuario restantes en la columna C. para ello
presiona enter para aceptar la sugerencia, repetir lo mismo con el correo
De esta manera, hemos utilizado la función "Relleno automático" para separar
rápidamente los nombres de usuario y los dominios de las direcciones de correo
electrónico.
Otro ejemplo tenemos na lista de direcciones completas y queremos separar la
calle, el número, la ciudad y el código postal en columnas diferentes.
Pasos para utilizar la función Flash Fill:
• Escribe "Calle" en la celda C2 y presiona enter.
• Haz clic en la celda C3 y comienza a escribir "Avenida primavera".
• Excel detectará automáticamente el patrón y mostrará una lista de calles en gris en
las celdas C3 a C5 presiona enter para aceptar las sugerencias, repetir para las
otras opciones.
Creación y uso de tablas para gestionar y
analizar datos.
Las tablas en Excel son una herramienta flexible que permite gestionar y analizar
datos de manera eficiente, al convertir un rango de datos en una tabla Excel aplica
automáticamente estilos de formato y facilita la aplicación de funciones y fórmulas,
además, las tablas en Excel ofrecen características como la clasificación y filtrado de
datos, así como la adición de totales de fila.
Entre algunos beneficios encontramos:
Organización automática de datos porque proporcionan una estructura ordenada y
coherente para los datos, facilitando su lectura y análisis.
Al convertir un rango de datos en una tabla, Excel aplica automáticamente un
formato atractivo y uniforme, incluyendo colores de fondo alternos y estilos de
encabezado.
Las tablas en Excel permiten el uso de referencias estructuradas en fórmulas, lo que
significa que puedes referirte a columnas y celdas por su nombre en lugar de sus
direcciones de celda, esto mejora la legibilidad y la comprensión de las fórmulas.
Las tablas en Excel se expanden automáticamente para incluir nuevas filas y
columnas cuando se agregan datos, lo que facilita la actualización de la tabla sin
tener que ajustar manualmente las fórmulas.
El filtrado y ordenamiento es fácil, puesto que las tablas en Excel ofrecen
herramientas de filtrado y ordenamiento incorporadas en los encabezados de las
columnas, lo que permite una rápida clasificación y filtrado de los datos sin
necesidad de aplicar filtros adicionales.
Las tablas en Excel permiten agregar fácilmente totales de fila y columna,
proporcionando sumas, promedios, conteos y otros cálculos útiles en un solo clic.
La Integración con tablas dinámicas es un excelente beneficio, siendo la base para
la creación de tablas dinámicas, debido a que simplifican el proceso de resumir,
analizar y presentar datos.
Las tablas en Excel facilitan la navegación a través de los datos, puedes desplazarte
rápidamente entre celdas y columnas utilizando las teclas de flecha y la tecla Tab.
En Las tablas en Excel pueden incluir reglas de validación de datos que ayudan a
garantizar la coherencia y la precisión de los datos ingresados.
Las tablas en Excel se integran perfectamente con una amplia gama de funciones y
características de Excel, incluyendo fórmulas, gráficos y análisis de datos, lo que
permite aprovechar al máximo las capacidades de análisis y presentación de Excel.
Pasos para formato tabla:
• Ubicarse dentro de cualquier celda de la tabla que tienes
• Presiona “Control + T”
• Se selecciona el área de la tabla con líneas punteadas y se muestra en la tabla
en la pregunta ¿Dónde están los datos de la tabla?
• Debes seleccionar que la tabla tiene encabezados pocos casos no lo
requieren
• Luego aceptar
Para cambiar el estilo de la tabla debes ubicarte dentro de cualquier celda de la
tabla, se activa la ficha “Diseño de tabla” y en la categoría de “Estilos de tabla”
despliegas la ventana de dialogo y escojas la que requieras.
Otra forma de aplicar el formato tabla es ubicarse en cualquier parte de la hoja de
trabajo, dirigirse a la ficha “Inicio” buscar la categoría o grupo “Estilos”, seleccionar
“Dar formato como tabla”, desplegar la ventana de dialogo y escoger el formato que
se requiera, luego enter y se despliega la ventana donde se selecciona los rangos y
que la tabla tiene encabezados.
Para agregar una columna se escribe en la continuación de la tabla el encabezado, se da
clic en enter y se expande la tabla, lo mismo se realiza para las filas.
Las tablas en Excel permiten la formulación de relleno automático indicando en la primera
celda.
Las tablas en Excel permiten el uso de referencias estructuradas en fórmulas, permitiendo
así referirte a columnas y celdas por su nombre, facilitando la consulta posterior.
Se puede agregar fila de totales a la tabla, donde al desplegar la fila de total se puede
seleccionar una función como promedio, contar, contar números, Máx, Mín, Suma, etc. Para
ello debes ubicarte en la última celda de la tabla que contenga datos y dirigirte a la ficha
“Diseño de Tabla” luego categoría “Opciones de estilo de tabla” y seleccionar “Fila de
totales”
Utilizar la validación de datos para controlar
los datos que los usuarios introducen en las
hojas de trabajo.
La validación de datos es una herramienta importante en Excel que permite controlar y
restringir los datos que los usuarios pueden ingresar en las hojas de trabajo, ayudando a
garantizar la calidad y la precisión de los datos, evitando errores y posibles problemas en
los análisis y cálculos realizados con esos datos.
Los pasos para utilizar la validación de datos y algunos ejemplos resueltos para ilustrar su
uso son:
a) Seleccionar la celda o el rango de celdas a los que desea aplicar la validación.
b) Realizar clic en la pestaña "Datos" en la cinta de opciones.
c) Realizar clic en "Validación de datos" en el grupo "Herramientas de datos".
d) En el cuadro de diálogo "Validación de datos", seleccionar la pestaña
"Configuración".
e) En el menú desplegable "Permitir", elegir el tipo de validación que desea aplicar, por
ejemplo: número entero, decimal, lista, fecha, hora, entre otros.
f) Establecer las condiciones y restricciones para la validación según el tipo
seleccionado.
g) Hacer clic en "Aceptar" para aplicar la validación de datos a las celdas seleccionadas.
Ejemplo: Se tiene una hoja de trabajo de ventas y desea restringir las entradas en la columna
"Región" a una lista predefinida de regiones, se puede realizar a través de utilizar la
validación de datos.
En el momento de agregar otro dato en la columna de origen se despliega el mensaje de
error porque no coincide con la restricción de validación de datos.
Otro ejemplo, se está trabajando en una hoja de trabajo y se desea limitar las entradas de
cantidad a números enteros entre 1 y 100.
• Seleccionar la columna "Cantidad" o las celdas donde desea aplicar la validación.
• Realizar clic en la pestaña "Datos" en la cinta de opciones.
• Realizar clic en "Validación de datos" en el grupo "Herramientas de datos".
• En el cuadro de diálogo "Validación de datos", seleccionar la pestaña
"Configuración".
• Elegir "Número entero" en el menú desplegable "Permitir".
• Establezca el "Data" en "entre" y especifique "1" como "Mínimo" y "100" como
"Máximo".
• Haga clic en "Aceptar".
Con esta validación, solo se podrá ingresar números enteros entre 1 y 100 en las celdas
seleccionadas.
Estadística descriptiva en Excel: Medidas de
tendencia central, histogramas y tablas de
frecuencia
La estadística descriptiva es una rama de la estadística que se centra en resumir y describir
los datos para facilitar su comprensión y análisis, Excel ofrece varias funciones y
herramientas que pueden ayudar a realizar un análisis estadístico descriptivo, como calcular
medidas de tendencia central, crear histogramas y generar tablas de frecuencia
Las medidas de tendencia central son la media, mediana y moda, estos valores que
describen la ubicación central de un conjunto de datos, Excel proporciona funciones para
calcular cada una de estas medidas, ubicándose en la celda donde se va hallar el cálculo, la
media se calcula a través del “Promedio”, la mediana de la función “MEDIANA” y la moda
usando la función “MODA.UNO”
Para hallar la moda, mediana y media se realiza los siguientes pasos:
• Ubicarse en la celda donde se realiza el cálculo
• Presiona “=” seguido de la palabra MODA.UNO, MEDIANA O PROMEDIO
• Presionar doble clic en la función se despliega Excel
• Seleccionar los datos
• Cerrar la función con el paréntesis de cierre “)”
• Dar enter
Una tabla de frecuencia es una tabla que muestra la frecuencia de cada valor o intervalo en
un conjunto de datos, para crear una tabla de frecuencia se debe:
• Organizar los datos en una columna
• Crear una columna para los intervalos
• Usar la función FRECUENCIA para calcular la frecuencia de cada intervalo
• Seleccionar los “datos” los cuales quedaran fijos con “F4”
• Seleccionar los “grupos” cerrar con paréntesis “)” y dar enter.
Un histograma es un gráfico que muestra la distribución de frecuencias de un conjunto de
datos en intervalos, para crear un histograma se realiza:
• Seleccionar el rango de datos
• Dirigirse a la ficha “Insertar” categoría “Gráficos”
• Desplegar y seleccionar el grafico de histograma
• Cuando se observe el histograma se da doble clic al gráfico parte inferior en el eje y
se selecciona en opciones de eje “Ancho del Rango” se asigna la cantidad el rango
de acuerdo con lo que quiere observar en el histogramo.
Igualmente, en el histograma se puede indicar si se desea observar la distribución de
frecuencias para ventas superiores, para ello se debe seleccionar “opciones de eje”
seleccionar “desbordar rango” y asignar el valor superior, es decir la cantidad de
frecuencia por encima de dicho valor.
Además, en el histograma se puede indicar si se desea observar la distribución de
frecuencias para ventas inferiores, para ello se debe seleccionar “opciones de eje”
seleccionar “Subdesbordar rango” y asignar el valor inferior, es decir la cantidad de
frecuencia por debajo de dicho valor.
Los histogramas son una herramienta valiosa en el análisis de datos y tienen varios
beneficios, entre ellos:
Los histogramas proporcionan una forma rápida y fácil de visualizar la distribución de un
conjunto de datos, muestran la frecuencia de los datos en diferentes intervalos, lo que
permite identificar tendencias, patrones y anomalías.
Así mismo, ayudan a identificar la forma general de la distribución de los datos, esto puede
revelar si los datos siguen una distribución normal (en forma de campana), si están sesgados
hacia la izquierda o hacia la derecha, o si presentan una distribución multimodal (con varios
picos).
Un histograma puede revelar valores atípicos o extremos en los datos, estos valores pueden
indicar errores en la recopilación de datos, la necesidad de realizar ajustes en un proceso o
la presencia de eventos excepcionales que deben investigarse.
También facilitan la comparación de dos o más conjuntos de datos, al visualizar las
distribuciones de diferentes conjuntos de datos en el mismo gráfico, es posible comparar
rápidamente sus características generales y determinar si existen diferencias significativas
entre ellos.
La información obtenida a partir de un histograma puede usarse para tomar decisiones
informadas en una variedad de campos, desde el control de calidad en un departamento
hasta la toma de decisiones financieras y la evaluación del rendimiento del grupo de
estudio, al comprender cómo se distribuyen los datos, los tomadores de decisiones pueden
identificar áreas de mejora y establecer metas realistas para el rendimiento futuro.
REFERENCIAS
Microsoft, (2023), https://support.microsoft.com/es-es/excel
