Excel intermedio

y desarrollo
de capacidades

analíticas

Unidad 1: Manejo de nombres de rangos,

validación, listas desplegables y filtros avanzados
Autor: Ricardo Luque Ferrucho
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

Imagina que trabajas en “Distribuidora Excel SAS” que es una

empresa de ventas de productos en línea, allí eres responsable de
gestionar el inventario de una amplia gama de productos, desde
teléfonos móviles hasta ropa y accesorios.
El desafío es que el inventario es grande y diverso, lo que
dificulta realizar un seguimiento efectivo de los productos. Por otro
lado, los empleados necesitan realizar tareas diarias como digitar
los productos que ingresan y sus características, así como registrar
la información de venta, pero ¿Cómo asegurarse de que los datos
sean precisos?
Adicionalmente, cada semana debes enviar un reporte del estado
del inventario, rendimiento de vendedores y ganancias.

¿Cómo crees que podemos resolverlo en Excel?

2
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

¡Bienvenido/a!
Tu trabajo cada día en “Distribuidora Excel SAS” te
trae un reto y un aprendizaje nuevo, el día de hoy te
encargaron crear un archivo de Excel desde el cual
se permita hacer validación de datos, crear listas y
crear reportes con filtros avanzados.

¿Aceptas el reto?
A continuación, encontrarás la teoría y práctica que te
permitirán desarrollar este reto, para eso te invitamos a
revisar todos los videos, pdfs y archivos que componen
esta unidad.

3
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

Tema 1:
Manejo de nombres
de rangos
Los nombres de rango hacen que las fórmulas sean
más legibles y menos propensas a errores, ya que
puedes usar nombres significativos.

4
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

En Excel, los nombres de rango se utilizan para asignar un

nombre descriptivo a un conjunto de celdas. Esto facilita la
identificación de dichas celdas para aplicarlas en fórmulas
y funciones en lugar de usar las referencias de celdas
tradicionales como A1, B2, etc.

Cuadro de
nombres

Veamos a continuación cómo se utilizan:

La forma de hacerlo es: seleccionamos el rango, vamos al
cuadro de nombres, colocamos el nombre y Enter.

Veamos cómo hacer una operación con rangos:

Vamos a sumar los números del ejemplo:
La forma de hacerlo es: una vez que ya hemos asignado el
nombre del rango, nos ubicamos en una celda y escribimos
la fórmula:
=SUMA(nombre del rango)

Al comenzar a
escribir, aparece el
nombre asignado al
rango

La ventaja más grande es que podemos usar el nombre de

un rango, para hacer operaciones en cualquier hoja.
5
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

En el botón Administrador de Nombres que se encuentra

en la pestaña Fórmulas, podemos crear un rango y ¡Recuerda!
modificar o eliminar uno ya creado: En un mismo libro, no
puede haber dos nombres
de rango iguales, no
importa la cantidad de
hojas.

6
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

¡Prueba tus conocimientos!

A continuación, encontrarás 3 preguntas sobre los temas vistos,
estas no son calificables, tienen como fin que puedas verificar tu
aprendizaje.
¡Discute las respuestas con tus compañeros y docente!

¿Cuál es el propósito de colocar nombre a los rangos?

o Hacer que el uso de fórmulas o funciones sea más fácil.
o Ordenar los datos.
o Crear atajos para mejor manejo de los datos.

¿Puede haber dos nombres de rangos iguales en un libro de

Excel?
o Verdadero
o Falso

Para modificar el rango de un nombre asignado debo:

o Agregar los datos, por defecto Excel los reconoce.
o Agregar los datos e ir a la pestaña Fórmulas, botón
Administrador de nombres y modificar.
o Borrar el nombre que tiene, agregar los datos y volver a
nombrar el rango.

7
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

Tema 2:
Validación de datos y
listas desplegables
La validación de datos en Excel, es importante porque
previene errores, ahorra tiempo, mantiene la
integridad de los datos, facilita la identificación de
problemas y promueve el trabajo en equipo eficiente y
preciso.

8
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

Validar datos en Excel

Es una opción de Excel que nos permite que se pueda
digitar o no un dato en una celda.
Por defecto en todas las celdas de Excel podemos escribir
el dato que queramos: un número, un texto, con ortografía,
sin ortografía.
Supongamos que tenemos una columna que se llama
edad, donde cualquier persona puede agregar datos, si
esta no se valida, entonces pueden escribir valores como
50, 100, 234, 34500, catorce, etc., como vemos algunos
son datos errados o no unificados en la columna edad.
Para evitar esos problemas se usa validación de datos:

1 Se seleccionamos las celdas vacías donde se aplicará

la validación de datos, vamos a la pestaña Datos y
escogemos el botón Validación de Datos.

2
Se abre una ventana, por
defecto, esta tiene activa la
opción “cualquier valor”,
razón por la cual podemos
escribir cualquier dato en las
celdas.

9
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

Al consultar la lista desplegable, aparecen las opciones de

validación:

Número entero: Solo puede escribir en las celdas

seleccionadas números enteros.
Decimal: Números decimales.
Lista: Se pueden hacer listas desplegables.
Fecha: Únicamente digitar fechas.
Hora: Solamente horas.
Longitud de Digitar un número de caracteres.
texto:
Personalizada: Cuando se está en un nivel más avanzado,
se puede validar datos con fórmulas.

En este ejemplo, las celdas seleccionadas se validan, para

que solamente se pueda digitar números enteros entre
18 y 60. Cualquier otro número o texto no será permitido
colocar, aparecerá un aviso indicando que el valor digitado
no coincide con la restricción de validación que tiene la
celda.

10
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

Listas desplegables
Usando la validación podemos hacer listas desplegables,
hay dos formas de hacerlas, veamos:

1 A. La lista de datos que queremos desplegar se digita a

un lado o en otra hoja.
B. Hacemos clic en la celda donde queremos que se
despliegue la lista.
C. Abrimos la ventana de Validación de datos y
seleccionamos Lista.
D. Hacemos clic en Origen y seleccionamos la lista de
opciones a desplegar, en este caso es el rango de
ciudades de la columna E.
E. Clic en aceptar.

¡RECUERDA!
Si quiere que las opciones
de la lista aparezcan en
orden alfabético, debe
ordenar los datos de
las ciudades en orden
alfabético.

11
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

2 La lista de datos que vamos a desplegar se puede ubicar

también dentro de la opción Validación de datos.
A. Hacemos clic en la celda donde queremos la lista
desplegable.
B. Abrimos la ventana Validación de datos.
C. En Origen, digitamos las opciones de la lista
separadas por punto y coma (;) o coma (;) según la
configuración de tu Excel.

12
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

¡Prueba tus conocimientos!

A continuación, encontrarás 3 preguntas sobre los temas vistos,
estas no son calificables, tienen como fin que puedas verificar tu
aprendizaje.
¡Discute las respuestas con tus compañeros y docente!

La validación de datos permite que una persona pueda o no

colocar cierta información en una celda, esa información puede
ser:
o Números enteros, texto, lista
o Números enteros, números decimales, lista
o Números decimales, texto, lista

Las dos formas de crear una lista desplegable son:

o Las opciones en una lista aparte y las opciones dentro de la
ventana Validación de datos
o Las opciones en una lista aparte y las opciones dentro de la
ventana Formato
o Las opciones dentro de la ventana formato y una lista de
opciones en otra hoja.

Se usa validación de datos para:

o Para que los datos queden ordenados
o Para que no se pueda colocar cualquier dato en una celda
o Para utilizar fórmulas y funciones de una manera más eficiente.

13
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

Tema 3
Filtros avanzados
Los filtros avanzados en Excel, son una herramienta
poderosa que permite realizar búsquedas y filtrar
datos complejos de manera eficiente. Su uso puede
ahorrar tiempo y mejorar la precisión al buscar
información específica dentro de grandes conjuntos de
datos.
14
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

Los filtros avanzados se diferencian de los autofiltros (filtros

normales), por 3 razones específicas:

1. El resultado del filtro se puede colocar a un lado, es decir sin

modificar los datos de origen.
2. Podemos hacer que en el resultado del filtro no se coloquen
todas las columnas que originalmente tiene los datos de
origen (es decir, vemos la información que realmente me
interesa).
3. Como su nombre lo indica, podemos hacer filtros más
complejos que con auto filtros no podría hacer.

Para generar reportes utilizando filtros avanzados seguimos

los siguientes pasos:
A. Escribimos por separado el o los encabezados por
los cuales vamos a filtrar la información.
B. Debajo de los encabezados escribimos el criterio de
búsqueda.
C. Seleccionamos el lugar donde queremos que
aparezca el reporte.

En el ejemplo, en la celda Y2, va el encabezado Segmento,

debajo el criterio por el que vamos a filtrar la columna
Segmento, en este caso Estrategico y la celda Y5 es el lugar
donde queremos que aparezca el reporte.
15
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

D. Vamos a la pestaña Datos, escogemos Avanzadas, y

aparece la siguiente ventana:

E. Marcamos la opción Copiar a otro lugar, para que

el resultado no “filtre” los datos de origen, sino que
queden en otro lugar.
F. En Rango de lista, se escoge el rango de los datos
de origen.
G. En Rango de criterios, se escogen los criterios
por los cuales se va a realizar el filtro, es decir, el
encabezado y debajo el criterio de filtro.
H. En Copiar a, se selecciona la celda a partir de donde
quiero quede el resultado del filtro.

16
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

Al dar Aceptar el resultado es el siguiente:

A la izquierda vemos la tabla de origen, a la derecha el

resultado de haber filtrado la columna Segmento, con el criterio
Estrategico. Se creó una nueva tabla únicamente con las filas
donde se encuentra dicho criterio.

Filtro con varios criterios

Si quiero colocar más criterios de filtro a la derecha
significa Y, para ubicarlos hacia abajo significa O.
Veamos un ejemplo:

Al colocar un nombre debajo En este ejemplo al estar datos

de otro significa O. Se lee de la a la derecha se leen con la Y.
siguiente manera:
Se interpreta de la siguiente
Se va a filtrar la columna manera.
Vendedor por los nombres Sonia
Se va a filtrar por Vendedor,
O Fernanda.
Sonia Y Ciudad Bogotá, es
Se pueden colocar debajo todos decir, el resultado mostraría
los nombres que uno desee. todos los registros que tengan a
Sonia y Bogotá.
17
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

En este ejemplo hay datos debajo y al lado. Se interpreta

de la siguiente manera:
Se va a filtrar por vendedor Sonia y Ciudad Bogotá O
vendedor Fernanda y Ciudad Cali.
El resultado mostraría todos los registros que tenga a Sonia
y Bogotá O Fernanda y Cali.
¡Este tipo de filtros, autofiltro no lo podría hacer!

Por último, si queremos que el resultado del filtro no

muestre todas las columnas de la tabla de origen, se
indican los encabezados que quiero que me muestre en
Copiar a.
Veamos.

En el ejemplo, solo quiero que se muestre la información de las

columnas Curso, Modalidad, Segmento.

¡Recuerda!
Las columnas no necesitan
estar en el mismo orden
que en los datos de origen.

18
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

Y al darle aceptar, la Solamente nos muestra

respuesta es: la información de los
3 encabezados que
seleccionamos que son
cursos Tácticos y virtuales.

19
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

¡Prueba tus conocimientos!

A continuación, encontrarás 3 preguntas sobre los temas vistos,
estas no son calificables, tienen como fin que puedas verificar tu
aprendizaje.
¡Discute las respuestas con tus compañeros y docente!

Los filtros avanzados se diferencian de los autofiltros por una de

estas tres opciones.
o No hay diferencia, ambos filtran la información.
o El resultado del filtro se puede colocar en otro lado sin afectar
la tabla de origen.
o El autofiltro puede mostrar los datos duplicados.

Falso o Verdadero - Los criterios que se colocan hacia abajo se

leen como Y, y los que van a la derecha se leen como O
o Falso
o Verdadero

¿Cuál de las siguientes afirmaciones NO es correcta?

o Los filtros avanzados permiten hacer reportes más complejos
que utilizando autofiltros.
o Los filtros avanzados permiten en la respuesta del informe
colocar únicamente las columnas que desee, es decir no
aparecen todas las columnas de los datos de origen.
o Los filtros avanzados permiten ordenar la información para
manipular los datos de una mejor forma.

20
 Unidad 1: Manejo de nombres de rangos,
validación, listas desplegables y filtros avanzados

Videos de referencia
Tema 1. Nombres de rango
Tema 2. Validación de datos
Tema 3. Filtros avanzados
Tema 3. Reportes con filtros avanzados

CET COLSUBSIDIO
EDUCACIÓN PARA LA PRODUCTIVIDAD
2024

AUTOR: Ricardo Luque Ferrucho

DISEÑO Y DIAGRAMACIÓN: Carolina López Ponce
IMÁGENES Y RECURSOS VISUALES: https://www.freepik.com/

Esta obra está bajo una licencia internacional Creative Commons

Atribución-NoComercial-SinDerivadas 4.0.
21