INFORMATICA II
Clase 8 Unidad 2
CLASE 8
IMPORTANTE:
SOLO PUBLICAR EN LOS FOROS LAS DUDAS DE
LOS EJERCICIOS DE PRÁCTICA.
OBJETIVO:
FILTROS AVANZADOS / BUSCARV / CONSULTAV
FILTROS AVANZADOS
Por medio de filtros seleccionamos un subconjunto de filas de una base de datos EXCEL fijando
criterios por las columnas. La clase anterior habíamos visto que con Autofiltros sólo podíamos
establecer hasta 2 criterios por columna.
Si necesitamos más de 2 criterios en alguna columna debemos utilizar la funcionalidad de EXCEL
“Filtros avanzados”.
Los criterios se definen en la misma hoja de cálculo,
pueden incluir varias condiciones aplicadas a una sola columna,
varios criterios aplicados a varias columnas y
condiciones creadas como resultado de una fórmula.
Los criterios deben estar escritos dentro de la misma hoja.
¿Cómo crear la tabla de criterios?
Se debe crear una tabla con:
Las columnas por las que se agregarán condiciones llevando exactamente el mismo rótulo
que en la base a filtrar salvo cuando se compare contra una fórmula;
Se pondrán en una misma fila, para cada columna de criterio, todas las condiciones que
deban cumplirse a la vez (EXCEL une las condiciones con un Y)
Así se podrán agregar tantas filas de condiciones como sean necesarias. Cada dila puede
tener sólo algunas columnas completas. EXCEL analizará cada fila de citerios e irá
agregando los resultados de cada fila (une cada resultado fila por fila).
INFORMATICA II 1 / 12
INFORMATICA II
Clase 8 Unidad 2
GUÍA PRÁCTICA PARA FILTROS AVANZADOS
Abrir [Link] de la carpeta de la Biblioteca
Hoja Empresa
Se quiere mostrar toda la información de todas las ventas realizadas por Buchanam, García y López,
para lo cual, como son más de dos criterios, se deberá hacer por Filtros Avanzados.
1. Crear tabla de criterios
Los rótulos de las columnas deberán ser copiados de la base para que sean exactamente iguales,
las filas situadas abajo, deberán tener los criterios que deseamos buscar ( es preferible copiar los
datos desde la base también si se busca exactamente ese valor para no cometer errores de
tipeo) . Tener la precaución que esta tabla de criterios no debe estar unida a la base de datos.
Por lo menos dejar siempre una columna y una fila en blanco de separación.
Vendedor
Buchanan
García
Lopez
2. Dinámica del comando:
o Pararse en la base de datos
o Menú Datos / Filtros / Filtros avanzados
o Ver que selecciona la base de datos a filtrar por lo que el casillero de Rango de la lista
automáticamente se autocompleta indicando el rango de las celdas de la base de datos.
Si así no lo hiciera ó ya hubiese algo escrito en el momento de seleccionar el comando,
borrar lo que hay en ese casillero y seleccionar las celdas de la base de datos a filtrar
hasta que quede bien indicado el rango a filtrar.
o Pararse en el casillero de Rango de criterios y completarlo directamente seleccionando con
el mouse la tabla de criterios
o Seleccionar la opción de Acción Copiar a otro lugar
o Pararse en el casillero de Copiar a y completarlo directaentes eleccionando la celda a
partir de la cual se mostrarán los resultados. También los resultados deben colocarse
dejando siempre por lo menos una fila y una columna en blanco tanto de la base de
datos como de la tabla de criterios.
o Aceptar y EXCEL colocará las filas de la Base de Datos que cumplan esas condiciones,
en este caso a partir de la celda I6.
INFORMATICA II 2 / 12
INFORMATICA II
Clase 8 Unidad 2
Tabla de Criterios
Resultado
Base a Filtrar
Filtros Avanzados con más de 1 columna con criterios
Hoja Ventas.
Creemos la tabla de criterios para poder filtrar las Ventas de Verduras que superen los 1000$ del
Vendedor Buchanan.
Consignas
1. Crear tabla de criterios
Para cumplir con este filtro debemos identificar porque columnas debemos aplicar las
condiciones. Esas serán las columnas de nuestra tabla de criterios.
Como dijimos antes los rótulos y los valores pueden ser copiados de la base para que sean
exactamente iguales (para no cometer errores de tipeo) . Recordar dejar por lo menos una
columna y una fila en blanco de separación.
Vendedor Producto Ventas
Buchanan Verduras > 1000
En la columna Ventas para indicar que queremos las ventas que superen los 1000$, utilizamos
el símbolo “>” que es mayor. Es decir, sino comparamos por igual, debemos anteponer al valor
el símbolo de comparación que corresponda.
2. Dinámica del comando:
o Pararse en la base de datos
INFORMATICA II 3 / 12
INFORMATICA II
Clase 8 Unidad 2
o Menú Datos / Filtros / Filtros avanzados
o Ir completando cada casillero como se indicó en la guía.
o Seleccionar la opción de Acción Copiar a otro lugar. Pararse en el casillero de Copiar a y
completarlo directamente seleccionando la celda a partir de la cual se mostrarán los
resultados, en este caso a partir de la celda H5.
Presenta todas las filas que cumplen con esas tres condiciones a la vez pues están en una
misma fila Vendedor = Buchanan Y Producto = Verduras Y Ventas > 1000
NOTAR que:
Si creamos una tabla de criterios colocando cada condición en una fila aparte
Vendedor Producto Ventas
Buchanan
Verduras
> 1000
Mostrará todas las filas (registros) que cumplen con cualquiera de estos criterios, pues
EXCEL filtra cada fila y va uniendo cada uno de esos resultados.
Vendedor = Buchanan O Producto = Verduras O Ventas > 1000
Más Filtros Avanzados con más de 1 columna con criterios
Hoja Productos.
Creemos las tablas de criterios y luego apliquemos la dinámica de filtrado.
1. Crear tabla de criterios para poder seleccionar lo que vendió Pérez en la región
Norte.
Para cumplir con este filtro identificamos porque columnas debemos aplicar las condiciones:
Vendedor y Región
Vendedor Region
Perez Norte
INFORMATICA II 4 / 12
INFORMATICA II
Clase 8 Unidad 2
Luego aplicar la dinámica:
NOTAR que:
Si creamos una tabla de criterios colocando nuevamente cada condición en una fila aparte
Vendedor Region
Perez
Norte
Mostrará todas las filas (registros) que cumplen con cualquiera de estos criterios, pues
EXCEL filtra cada fila y va uniendo cada uno de esos resultados.
Vendedor = Perez O Region = Norte
Hoja Producto 2
2. Crear tabla de criterios para poder seleccionar las ventas de García que superen los
$ 1000
Vendedor Ventas
García > 1000
3. Crear tabla de criterios para poder seleccionar las ventas de García entre los $ 1000
y los $ 3000
Para cumplir con este filtro identificamos que debemos poner 2 condiciones en la misma fila
para la columna Ventas pues nos piden “entre”. En este caso la tabla de Criterios será:
Vendedor Ventas Ventas
García >= 1000 <= 3000
INFORMATICA II 5 / 12
INFORMATICA II
Clase 8 Unidad 2
Hoja Producto 3
4. Crear tabla de criterios para poder mostrar todas las ventas de García mayores al
promedio de ventas de García.
Para cumplir con este filtro identificamos que lo 1ero que debemos hacer es calcular el
promedio de ventas de García. Para ello vamos a ordenar la base por Vendedor.
o Nos paramos en la base de datos en cualquier celda de la columna Vendedor, elegimos
el botón de Orden ascendente.
o Calcular el Promedio de ventas de García, por ejemplo en H1: H1 =
Promedio(C2:C25)
o Ahora como el criterio compara contra una fórmula, pues debemos seleccionar las
celdas cuya venta sea mayor a lo calculado en H4, es el caso en que el rótulo justo no
debe ser igual al de la columna con la que se compara. En este caso la columna es
Ventas pero, por ejemplo, podemos nuestra tabla de criterios como se indica aquí:
Ventas mayores al promedio ventas de Garcia
=C2 > $H$1
o Es distinto el rótulo de la tabla de criterios al de la columna con que se compara
o El criterio es una fórmula:
Empieza con el símbolo =
Se indica la 1er celda de la columna a comparar, en este caso la 1er celda de
Ventas es C2
Se indica cómo se comparará (=, >, <, >=, <=, <>)
Se indica la dirección absoluta de la celda que contiene la fórmula contra la que
se compara
Según la comparación se cumpla ó no para el contenido de la 1er celda dará
FALSO ó VERDADERO. No reparar en ese resultado. Simplemente saber
que se verá alguno de esos valores lógicos.
INFORMATICA II 6 / 12
INFORMATICA II
Clase 8 Unidad 2
5. Crear tabla de criterios para poder mostrar todas las ventas de López y Pérez
mayores al promedio de ventas de García.
Para cumplir con este filtro identificamos porque columnas debemos aplicar las condiciones:
Vendedor y Ventas. Pero Ventas como en el ejercicio anterior. Así nuestra tabla de criterios
será:
Vendedor Ventas > promedio de García
Lopez =C2 > $H$1
Perez =C2 > $H$1
INFORMATICA II 7 / 12
INFORMATICA II
Clase 8 Unidad 2
FUNCION BUSCARV() / CONSULTAV()
Cuando en EXCEL contamos con una base de datos, muchas veces debemos
repetir valores que tenemos allí cargados en otros lugares del mismo libro, ó
también quizás en otros.
Imaginémonos por ejemplo si tenemos una base de datos de clientes de nuestra
empresa, donde tenemos para cada uno de ellos su nombre completo, dirección TE,
CUIT, etc.
Si tenemos el formulario de Factura de nuestra empresa, cada vez que debamos
elaborar una factura para uno de nuestros clientes deberíamos completar siempre los
mismos datos, aún ya teniéndolos cargados.
Existe la función BUSCARV y/o CONSULTAV() dependiendo la versión del
Office que, lo que nos permite, es extraer información de nuestra base de datos para
no tener que retipearla cada vez que la debamos repetir.
NOTAR que:
Si cargo los datos en mi base de datos y luego los extraigo utilizando la
función BUSCARV, sólo allí la actualizo. Imagínense si repetí la dirección de
un cliente en distintos lugares y ahora la debo actualizar. Lo debería hacer en
cada uno de los lugares en que la escribí lo cual puede llevar errores de tipeo
y también que me olvide de actualizar algún lugar en que la haya usado.
Supongamos que tenemos la siguiente base de datos bajo el nombre
“Personas”:
Paciente Nombre Apellido TE
Pac1 Ana Pérez 4555-2134
Pac2 Inés Beltrán 5603-2843
Pac3 Adrián Crazana 6454-8967
Utilizando la función BUSCARV podríamos recuperar del bloque “Personas”, para
la fila que tenga en su 1er columna el valor “Pac1”, la columna 3 ya que deseamos
obtener su apellido. Así la deberíamos llamar en EXCEL:
=BUSCARV(“Pac1”;Personas;3;FALSO)
Si quisiéramos recuperar para el paciente “Pac1” su TE deberíamos utilizar la
siguiente llamada:
=BUSCARV(“Pac1”;Personas;4;FALSO)
INFORMATICA II 8 / 12
INFORMATICA II
Clase 8 Unidad 2
Normalmente en el llamado a la función en lugar de indicar directamente el valor a
buscar (en nuestro ejemplo es “Pac1”) se indica la celda que contiene ese valor. Por
ejemplo si en la celda B3 escribimos “Pac1”, nos quedaría:
=BUSCARV(B3;Personas;3;FALSO)
=BUSCARV(B3;Personas;4;FALSO)
GUÍA PRÁCTICA PARA
FUNCION BUSCARV
=BUSCARV(Valor a buscar; bloque; nro. de columna a extraer; FALSO)
Veremos uno a uno los parámetros que utiliza la función BUSCARV:
Valor a buscar: nosotros allí indicaremos la celda que contiene el valor a ser
buscado en el bloque de base de datos. Ese valor se busacará en la 1er columna
de la base de datos y de la 1er fila que coincida es de donde se extraerá el
resultado.
Bloque: es el nombre al bloque de la base de datos en donde se buscan los valores
y de donde se extraen también. Se puede indicar el rango de celdas del bloque,
pero se lo debe hacer utilizando direcciones absolutas
Nro de columna a extraer: el valor de que columna (2da, 3era, …) devolverá la
función. Por ejemplo si ponemos un 3 devolverá (de la fila que corresponda) el
valor de la 3era columna.
FALSO: este parámetro toma este valor pues trabajaremos con bases de datos
que no necesitan estar ordenadas por su 1er columna ya que buscaremos valores
exactamente iguales.
Completar una planilla utilizando la función BUSCARV
Abrimos el archivo [Link] que se adjunta
Tenemos en la hoja Clientes el bloque Datos. Ese bloque lo utilizaremos para extraer información
de nuestros clientes e ir completando el bloque Resumen que se encuentra en la hoja Ventas.
Consignas
1. Bloque Resumen
Vamos a cargar los números de clientes para los que tenemos que completar el resumen.
También completaremos para cada uno de ellos el subtotal de facturación. Nos deberá
quedar así:
INFORMATICA II 9 / 12
INFORMATICA II
Clase 8 Unidad 2
2. Extraer datos de la Razón Social con la función BUSCARV
Para completar la Razón Social usaremos la función BUSCARV.
En la fila 8, el número de cliente a buscar está en la celda F8. Así entonces completamos:
G8=BUSCARV(f8;Datos;2;FALSO)
NOTAR que:
Para escribir esta función conviene hacer lo siguiente:
Escribir =BUSCARV(
Para completar el 1er parámetro no escribir la dirección sino indicarla
con el mouse: clic en F8 y EXCEL lo escribirá;
Tipear el separador de argumentos (puede ser , ó ;)
Para indicar el bloque desde donde se toman los datos, para acceder a
la lista de todos los nombres de bloques que están definidos en el
libro, presionar la tecla F5 y luego seleccionar el nombre Datos,
Aceptar. Automáticamente se cambia al bloque y se seguirá tipeando
como si no hubiese cambios (se irá viendo lo tipeado en la barra de
fórmulas)
Tipear el separador de argumentos (puede ser , ó ;)
Tipear el número de columna a extraer, en nuestro caso el 2
Tipear la palabra FALSO)
Terminar con ENTER
Luego podemos estirar esa fórmula: desde el manejador de celda de G8 , estiramos hasta
G15. Nos quedará así:
INFORMATICA II 10 / 12
INFORMATICA II
Clase 8 Unidad 2
En el caso del número de cliente 15, que no tiene su correspondiente en el bloque
buscado, la función nos devuelve el valor “#N/A” que significa “Información No
disponible” (Not Available). En el caso del cliente número 12 devuelve “0” pues si bien
en la base de datos está ese cliente no tiene información cargada.
3. Extraer datos de IVA con la función BUSCARV
Para completar la Condición frente al IVA de cada cliente también usaremos la función
BUSCARV trayendo la columna 4 del bloque datos.
En la fila 8, el número de cliente a buscar está en la celda F8. Así entonces completamos:
H8=BUSCARV(f8;Datos;4;FALSO)
Hacer más ancha la columna H para que se puedan ver los datos extraídos (fijarse que se ve
un ### que nos indica que hay que aumentar el ancho)
INFORMATICA II 11 / 12
INFORMATICA II
Clase 8 Unidad 2
4. Extraer el CUIT con la función BUSCARV
Para completar la Condición frente al IVA de cada cliente también usaremos la función
BUSCARV trayendo la columna 3 del bloque datos.
Completamos:
I8=BUSCARV(f8;Datos;3;FALSO)
En la clase siguiente completaremos las columnas faltantes.
INFORMATICA II 12 / 12
