Filtro avanzado en Excel 2016

En Excel puedes realizar un filtrado de datos totalmente personalizado proporcionando los criterios que deseas aplicar a la información. Este tipo de filtrado es conocido como Filtro avanzado y en esta ocasión te mostraré cómo utilizarlo.

Supongamos la siguiente tabla de datos.

Tabla de datos para filtro avanzado

Los criterios por los cuales se realizará el filtrado deben especificarse dentro de celdas de la misma hoja. Supongamos que deseo filtrar los registros del departamento de Finanzas.

Colocando los criterios para el filtro avanzado

Para este ejemplo coloqué los criterios por arriba de la tabla de datos aunque realmente su ubicación no es de importancia.

Aplicar un filtro avanzado a los datos

Antes de aplicar el filtro avanzado debo seleccionar la tabla de datos (A4:D13) y posteriormente ir a la ficha Datos y pulsar el botón Avanzadas que se encuentra en el grupo Ordenar y filtrar. Se mostrará el cuadro de diálogo Filtro avanzado.

Cuadro de diálogo de Filtro avanzado

Observa cómo en el cuadro de texto para Rango de criterios he seleccionado el rango que contiene las condiciones del filtro avanzado necesarias. Solamente resta pulsar el botón Aceptar para aplicar el filtro.

Datos filtrados

El resultado es precisamente lo que necesito, así que el filtrado avanzado ha sido exitoso. Pero ahora tengo un desafío mayor ¿Cómo podría hacer para especificar condiciones para una segunda columna? Supongamos que deseo filtrar la información de las personas que tienen apellido Hernández y que además pertenecen al departamento de Finanzas. Para filtrar los datos por estos criterios debo agregar dichas condiciones en otra celda.

Filtros para dos columnas

Observa cómo el Rango de la lista es el mismo que en el caso anterior ya que son los mismos datos, pero el Rango de criterios ahora abarca también las celdas que contienen el criterio para el Apellido. Al aceptar los cambios Excel aplicará el filtro avanzado adecuadamente.

Datos filtrados por criterios en dos columnas

De la misma manera puedes especificar un criterio para cada columna. Hasta ahora solamente he especificado una sola condición por columna,  pero ahora deseo agregar a los resultados del filtro  la información del departamento de Informática. Para ello solamente agregaré una fila adicional al rango de criterios de la siguiente manera.

Más de una condición en el rango de criterios

Al aceptar los cambios Excel filtrará la información de los empleados de Finanzas con apellido Hernández y además mostrará la información de los empleados del departamento de Informática sin importar el apellido que tengan.

Información filtrada por varios criterios

Anuncios

Funciones de base de datos en Excel 2016

Las funciones de base de datos en Excel facilitan nuestro trabajo con información que está organizada como una bases de datos, es decir, que se encuentra en un rango de celdas donde la primera fila tiene los títulos de cada columna. Estas funciones nos permiten contar, sumar, multiplicar los valores de una columna que cumplen con los criterios especificados e inclusive podremos extraer un registro que cumpla con ciertas condiciones.

Consulta la sintaxis de cada función haciendo clic sobre su nombre.

FUNCIÓN INGLÉS DESCRIPCIÓN
BDCONTAR DCOUNT Cuenta las celdas que contienen números en el campo (columna) de registros de la base de datos que cumplen las condiciones especificadas.
BDCONTARA DCOUNTA Cuenta el número de celdas que no están en blanco en el campo (columna) de los registros de la base de datos que cumplen las condiciones especificadas.
BDDESVEST DSTDEV Calcula la desviación estándar basándose en una muestra de las entradas seleccionadas de una base de datos.
BDDESVESTP DSTDEVP Calcula la desviación estándar basándose en la población total de las entradas seleccionadas de una base de datos.
BDEXTRAER DGET Extrae de una base de datos un único registro que coincide con las condiciones especificadas.
BDMAX DMAX Devuelve el número máximo en el campo (columna) de registros de la base de datos que coinciden con las condiciones especificadas.
BDMIN DMIN Devuelve el número menor del campo (columna) de registros de la base de datos que coincide con las condiciones especificadas.
BDPRODUCTO DPRODUCT Multiplica los valores del campo (columna) de registros en la base de datos que coinciden con las condiciones especificadas.
BDPROMEDIO DAVERAGE Obtiene el promedio de los valores de una columna, lista o base de datos que cumplen las condiciones especificadas.
BDSUMA DSUM Suma los números en el campo (columna) de los registros que coinciden con las condiciones especificadas.
BDVAR DVAR Calcula la varianza basándose en una muestra de las entradas seleccionadas de una base de datos.
BDVARP DVARP Calcula la varianza basándose en la población total de las entradas seleccionadas de una base de datos.

Formato condicional en una tabla dinámica de Excel

Es posible aplicar Formato condicional asociándolo a campos de una tabla dinámica.
Partiremos de un origen de datos en una hoja de cálculo de Excel, convertida en Tabla, por comodidad, a partir de la cual hemos construido una tabla dinámica. Quedando como podemos ver en la imagen:

El paso siguiente consiste asignar un formato condicional que agregue un icono según el Importe de las ventas y el Comercial. Para ello seleccionaremos el campo ‘Suma de Importe’, y desde la Ficha Inicio > Estilos > Formato Condicional > Conjunto de iconos > 5 flechas (de color):

Formato condicional en una tabla dinámica de Excel 2010.

Como era de esperar al haber seleccionado el campo ‘Suma de Importe’ y haber aplicado un formato condicional sobre él, se muestran inmediatamente los iconos seleccionados.
Lo importante viene ahora, y es que aparece en la selección de celdas, un botón de opciones de formato condicional:

Formato condicional en una tabla dinámica de Excel 2010.

En estas tres opciones es donde está la utilidad de la herramienta, ya que podremos optar por asociar el Formato condicional dado bien a:

  • las celdas seleccionadas
  • al campo marcado, ‘Suma de Importe’ en nuestro ejemplo.
  • o bien a Todas la celdas que muestran valores de ‘Suma de Importe’ para ‘Comercial’

Nosotros marcaremos la última opción:
Todas la celdas que muestran valores de ‘Suma de Importe’ para ‘Comercial’
ya que nuestro objetivo es asociar la variabilidad de los iconos siguiendo esa regla precisamente, esto es, según la importancia de las ventas (‘Importes’) entre todos los ‘Comerciales’.
Tras aplicar la Regla, podemos acceder al Administrador de reglas, seleccionar la regla creada y Editarla, para ver lo especial de esta edición:

Formato condicional en una tabla dinámica de Excel 2010.

El resultado es el siguiente:

Formato condicional en una tabla dinámica de Excel 2010.

Mientras mantengamos los campos ‘Importe’ y ‘Comercial’ (los dos campos asociados mediante la regla de formato condicional) los iconos configurados se mostrarán en la tabla dinámica de Excel, adaptándose al dinamismo de ésta. Es decir, añadiendo los iconos al cruce de los campos Comercial-Importe:

Formato condicional en una tabla dinámica de Excel 2010.

Cómo buscar valores repetidos en Excel?

Buscar valores repetidos en Excel

  1. Fórmula para buscar valores repetidos en Excel

    La “Lista1” contiene la base de datos donde se realizará la búsqueda mientras que la “Lista2” contiene la lista de valores a ser buscados, se trata de determinar si cada uno de los valores de esta “Lista” aparece en la “Lista1”.

    Para ello, en la celda E3 podemos encontrar la fórmula:

    =SI(ESERROR(BUSCARV(D3,$B$3:$B$7,1,FALSO)),0,1)

    De esta forma la función BUSCARV lo que esta realizado es buscar el valor que contiene la celda D3 (“Juan”) dentro del rango B3:B7, el tercer parámetro (1) indica que si se encuentra el valor buscado se retorne el valor que se encuentra en la primera columna de la base de datos, y finalmente el último parámetro “FALSO” provoca que se realice una búsqueda exacta del valor D3.

    Si la función BUSCARV no encuentra el valor buscado, entonces devolverá el error #N/A, este valor no puede formar parte de una operación matemática, por ello se introduce el uso de la función ESERROR, el cual evalúa si lo recibido es un error o no, es decir, si se obtiene #N/A, al función ESERROR retornará VERDADERO, en todos los demás casos se devolverá el valor FALSO.

    Finalmente la función SI convertirá los valores VERDADERO en cero y FALSO en uno, es decir si el valor buscado existe se obtendrá el nombre buscado, ESERROR lo convertirá en FALSO, y la función SI lo convertirá en 1, caso contrario si el valor no existe se obtendrá 0.

  2. Contar valores repetidos en Excel

    El caso anterior es conveniente cuando necesitamos distinguir uno a uno si los valores empleados existen en la otra lista, en caso no se requiera esto y solo se necesite identificar la cantidad completa de valores de la “Lista2” que aparecen en la “Lista1”, podemos añadir simplemente una sumatoria sobre lo obtenido con la fórmula anterior.

    O bien emplear la siguiente fórmula:

    =SUMAPRODUCTO(CONTAR.SI(B3:B7,D3:D5))

    Tomemos en cuenta que en caso utilicemos una sumatoria estaremos obteniendo “Cantidad de elementos de la lista 2 que aparecen en la lista 1 “, mientras que al emplear esta última fórmula estaremos obteniendo “Cantidad de veces que los elementos de la lista 2 aparecen en la lista 1”.