Como extraer una lista de registros no repetidos de una lista con registros repetidos en bases de datos de Excel

2. Resumen
3. Planteamiento del problema
5. Aspectos a tener en cuenta
6. Un tema de aplicación para el procedimiento
7. Referencias citadas

Resumen

Con frecuencia se presentan registros repetidos en columnas de Excel y a menudo se requiere saber cuantos y cuales registros diferentes hay en una columna determinada. Este artículo presenta un procedimiento que resuelve este problema. El procedimiento se explica con un ejemplo en cuatro sencillos pasos y combina la aplicación de la función estadística CONTAR.SI, el copiado de formulas con referencias de celda absoluta y relativas y la herramienta de Filtro del menú Datos. El procedimiento resulta bastante útil dado que no hay una herramienta de Excel que hago esto de forma directa. Un ejemplo de aplicación se encuentra en Bases de datos de colecciones de organismos.

Palabras clave: Excel, Formulas de Excel, Filtro, CONTAR.SI, Bases de Datos, Aplicaciones. Colecciones biológicas.

Planteamiento del problema

Con frecuencia se utiliza la hoja de cálculo de Excel para almacenar información. Así mismo, Excel cuenta con algunas herramientas para manipular, y analizar de una manera relativamente rápida la información contenida. Para citar algunas de estas se puede nombrar los elementos del menú Datos como Ordenar, Filtro, Texto a columnas, Validación, etc., además también son de mucha utilidad, las funciones de Estadística, de Texto, de Búsqueda y Referencia, de Información, Lógicas, etc.

En ciertas ocasiones, el empleo directo de una sola herramienta (ya sea alguna opción del menú Datos ó alguna Función) nos arroja exactamente el resultado que deseamos. No obstante, en otros casos es necesaria la combinación de varias de estas herramientas para obtener el resultado que realmente queremos. Supóngase que se tiene una Base de datos de localidades de un País, por ejemplo Colombia (Figura 1). Cada fila corresponde a una localidad distinta, y en cada columna hay un tipo diferente de información: Departamento, Municipio, Localidad, Latitud y Longitud. Ahora, supóngase que se quiere obtener de la manera más rápida posible todas las localidades del Departamento del Cauca. Esto se hace fácilmente y de una forma directa con la opción Filtro del menú Datos (véase por ejemplo, Rivera-Gutiérrez, 2004). Nótese en la Base de datos de la Figura 1 que como hay varias localidades de un mismo municipio, entonces, los nombres de los municipios y de los departamentos se repiten más de una vez en sus respectivas columnas. Teniendo en cuenta esto, supóngase que ahora nos han pedido obtener una lista de todos los Departamentos diferentes que hay en nuestra Base de datos. Una lista con todos los departamentos contenidos pero que no se repitan.

Figura 1. Base de datos en Excel con localidades de diferentes departamentos de Colombia.

Esta situación de registros repetidos en una o varias columnas de las bases de datos se presenta comúnmente, y a menudo nos interesa conocer cuantos y cuales ítems distintos (sin que se repitan) hay en una columna determinada. Este requerimiento no lo cumple ninguna herramienta de Excel directamente. Y a menos que se quiera hacer de forma manual, lo cual puede ser muy tedioso si nuestra base de datos tiene muchas filas, se puede emplear el procedimiento descrito en este artículo. Este procedimiento combina básicamente dos herramientas: la Función Estadística CONTAR.SI y la opción Filtro del menú Datos. Por tanto, para su compresión y aplicación se debe saber ingresar y copiar formulas en las celdas de la hoja de calculo y diferenciar las referencias absolutas y relativas a celdas, además saber utilizar la opción Filtro del menú Datos. Si no se tiene un conocimiento sobre estos temas, se puede consultar la ayuda de Excel. En Internet también se pueden conseguir otros sitios con cursos buenos de Excel (por ejemplo: http://www.saprica.com.ar/Documentos/Main/ExcelIntroProd.php. Consultada el 30 de Diciembre de 2004; http://www.monografias.com/ consultada el 30 de Diciembre de 2004). García de Jalón, Fernández-Caballero & García-Martos (2001) es otro curso completo. Rivera-Gutiérrez (2004) ilustra aplicaciones de la herramienta de Filtro (menú Datos) entre otras funciones.

Las imágenes de las Figuras de este articulo son de Excel con Windows XP y ha algunas se les ha suprimido las barras de estado y de formulas para una mayor cobertura de la imagen.

Procedimiento

El procedimiento será ilustrado con el ejemplo de la Figura 1 y el problema planteado en la Introducción. Se requiere obtener una lista con cada uno de los departamentos que tiene la base de datos, pero sin que se repitan. El procedimiento consiste en contar de arriba hacia abajo (ó de abajo hacia arriba, no importa el orden) el número de veces que se repite cada registro (de la columna deseada) excluyendo los registros ya contados. Después se utiliza el Filtro (menú Datos) para filtrar los registros que tengan una sola repetición. La lista resultante de dicho filtro es la deseada. El procedimiento se puede dividir en cuatro pasos:

1) Se escoge una columna vacía para introducir la formula CONTAR.SI. En nuestro ejemplo, dicha columna será la G y le pondremos como titulo "Conteo" (Obsérvese que todas las columnas en la base de datos tienen un titulo). La función CONTAR.SI tiene una sintaxis con dos tipos de información: un RANGO de celdas y un CRITERIO para contar. Esta función cuenta las celdas en el rango que cumplen con el criterio asignado. En el caso de nuestro ejemplo, el rango es B2:B34 que corresponde a la columna de los departamentos y el criterio es la información contenida en cada una de las celdas del mismo rango; en el caso de formula ingresada en la celda G2, el criterio es la celda B2 (Figura 2). Como se debe saber, las formulas se pueden ingresar ya sea con la ayuda del mismo Excel o manualmente desde el teclado. La primera es recomendada para personas poco expertas; cualquier formula se ingresa desde el menú Insertar (Barra de herramientas) y escogiendo la opción Función.

Volviendo a la Figura 2, después de haber introducido la función CONTAR.SI con su rango y su criterio, y al teclear ENTER, arrojará como resultado en la celda G2, el número de veces que se repite la palabra "Amazonas" en el rango de celdas especificado (B2:B34). Esto es 5 (Figura 3).

Figura 2. Ingreso de la Formula CONTAR.SI, y asignación del rango.

Para ver el gráfico seleccione la opción "Descargar" del menú superior

Figura 3. Resultado de la función CONTAR.SI para la primera celda.

2) Ahora se desea copiar la formula ingresada hacia abajo pero antes se debe tener en cuenta que al copiar una formula las referencias de celda pueden cambiar dependiendo de la referencia de celda que se utilice. Si las referencias de celda son absolutas, al copiar la formula a otras celdas, no cambian las celdas a las que se refiriere la formula. Pero si se usan referencias relativas, si cambian.

En nuestro caso se debe usar un tipo de referencia que logre que la formula copiada en cada celda excluya de su conteo las celdas que están arriba de ella e incluya tanto la celda misma como las celdas que están debajo de ella. De esta forma, el contenido de cada celda solo será contado una sola vez de arriba hacia abajo. Esto se obtiene agregando un signo de pesos ($) entre la B y el 34 en la sintaxis de la formula de la celda G2, de la siguiente manera:

=contar.si(B2:B$34;B2)

Para escribir el signo de pesos ($) después de haber ingresado la formula, nos paramos en la celda deseada, hacemos clic en la barra de formulas y escribimos el signo $ en la ubicación requerida. (Figura 4).

El signo $ mantiene fija la celda B34 a medida que se copia la formula hacia abajo; así mismo, este signo se omite deliberadamente en la posición B2 del rango de la función CONTAR.SI para que al copiar la formula de la celda G2 hacia abajo, la primera celda del rango de la función también se vaya moviendo una celda hacia abajo.

Figura 4. Adicionar el signo de $ a la formula ya ingresada.

Para ver el gráfico seleccione la opción "Descargar" del menú superior

 3) Después de agregado el signo ($) a la formula en la celda G2, se copia el contenido de esta celda hacia abajo, hasta la celda G34 (Figura 5). Como resultado, en cada celda del rango G2:G34, se obtendrá el número de veces que se repite cada registro de la columna B de cada fila del rango B2:B34, excluyendo del conteo las celdas (o registros) antecedentes (Figura 6).

Para corroborar que la función CONTAR.SI y el copiado hayan hecho bien su trabajo, nos ubicamos en alguna celda del rango G2:G34 que no sea la primera, por ejemplo la G10. La función copiada en esta celda debe contener el siguiente rango:

=contar.si(B10:B$34;B10)

es decir, que esta contando cuantas veces esta la palabra "Antioquia" en el rango B10:B34, lo cual es correcto para nuestros intereses (Figura 7). El lector puede verificar que esto se cumple para cualquier celda. Por ejemplo, si nos ubicamos en la celda G30, el rango de la formula en esa celda será:

=contar.si(B30:B$34;B30)

Figura 5. Después de que a la Formula se le haya escrito el signo $ en la posición correspondiente, se copia la formula de la celda G2 hasta la celda G34.

Para ver el gráfico seleccione la opción "Descargar" del menú superior

 Figura 6. Resultado final después de copiar la Formula CONTAR.SI de la celda G2 hasta la celda G34.

Para ver el gráfico seleccione la opción "Descargar" del menú superior

 Figura 7. Demostración de que el copiado de la formula ha operado de la manera esperada.

 Para ver el gráfico seleccione la opción "Descargar" del menú superior

4) El paso siguiente es hacer uso del Filtro (menú Datos). Al activar, esta opción (Figura 8), aparece una pestaña en la primera fila de todas las columnas que tienen información. Si nos ubicamos en la celda G1 (que contiene el titulo Conteo) y desplegamos la pestaña, inmediatamente podemos observar una lista de todos los números que aparecen en la columna G, entonces seleccionamos el número 1 (Figura 9), y con esto habremos filtrado nuestra base de datos para el dicho numero. La lista resultante (Figura 10) contiene en la columna B todos los departamentos sin repetirlos. Esta lista la podemos copiar y pegarla en otra hoja (puede ser del mismo libro o de otro libro) y así hemos obtenido el resultado deseado.

Figura 8. Activación de la opción de filtrado (menú Datos).

Figura 9. Filtrado por la columna G, con el número 1.

Para ver el gráfico seleccione la opción "Descargar" del menú superior

 Figura 10. Lista resultante del Filtro por columna G, número 1. Esta lista contiene todos los departamentos (columna B) sin repetirlos.

Para ver el gráfico seleccione la opción "Descargar" del menú superior

 Aspectos a tener en cuenta

1) Este procedimiento también es valido si se empieza desde la ultima celda hasta la primera (es decir de abajo hacia arriba). Para esto, en el caso del ejemplo planteado, en el paso 1 nos ubicamos en la celda G34 e introducimos la siguiente formula:

=contar.si(B$2:B34;B34)

Ahora, la celda fija (con el signo $) es la B2, de manera que a medida que copiamos la formula hacia arriba, se moverá la celda final del rango y no la primera. El resultado es diferente, de hecho es inverso al obtenido al principio, pero al filtrar por el número 1, se obtendrá la misma lista.

2) El resultado es el mismo si le agregamos un signo $ antes de la letra B, es decir, en caso de copiar de arriba hacia abajo:

=contar.si(B2:$B$34;B2)

ó en caso de copiar de abajo hacia arriba :

=contar.si($B$2:B34;B34)

Este signo $ antes de la letra B fija el rango y lo protege de movimientos de copiado horizontales, pero como el movimiento de copiado que se hace es vertical, este signo $ antes de la letra B no es necesario.

3) No es necesario que el texto en cada una de las celdas de la columna que se quiere examinar este en algún orden. El procedimiento funciona igual si la información esta en total desorden.

4) La lista final generada por el procedimiento descrito no se presenta organizada en orden alfabético (en caso de que sea texto) a menos que la columna original este organizada en este orden. Es importante tener en cuenta que esta lista final esta filtrada de modo que si se quiere ver en algún orden NO se puede simplemente seleccionar y activar la herramienta Ordenar (menú Datos) porque esta herramienta no funciona cuando el filtro esta activo. Para ordenar la lista, se debe seleccionar, copiar y pegar en otra hoja de cálculo.

5) El procedimiento descrito aquí, también puede servir para hacer una revisión de errores de escritura del texto contenido en la columna que se desea examinar. Por ejemplo si el registro Amazonas se repite 5 veces, pero en dos ocasiones, la palabra esta escrita de manera diferente (por ejemplo "Amasonas" ó "Amazona"), la función CONTAR.SI contara "Amasonas" y "Amazona" como registros distintos y en la lista final aparecerán. La función CONTAR.SI no discrimina entre mayúsculas y minúsculas, de manera que "Amazonas" y "amazonas" serán contadas como lo mismo. La función CONTAR.SI no tiene en cuenta las celdas en blanco.

Un tema de aplicación para el procedimiento.

El diseño de este procedimiento fue motivado por el trabajo con una Base de Datos de una colección de ejemplares de Organismos de aproximadamente 13000 registros. En dicho trabajo se nos pedía obtener y analizar cierta información de la Base de datos. Por ejemplo, cuantas especies distintas existen, cuantas localidades distintas existen, etc. De modo que el procedimiento aquí ilustrado fue de mucha utilidad para tal fin. Con la lista de nombres sin repetir, se pueden obtener otra serie de datos de una forma rápida con funciones como CONTAR.SI, BUSCAR, etc. A continuación se anexa la base de datos de la Figura 1 para que el lector la copie en Excel y practique el procedimiento expuesto.

Referencias citadas

García de Jalón, J., Fernández-Caballero, D. & García-Martos, C. (2001). Aprenda Excel 2000

como si estuviera en primero. Escuela Técnica Superior de Ingenieros Industriales; Universidad Politécnica de Madrid. Madrid. Extraído el 30 de diciembre de 2004 de: http://mat21.etsii.upm.es/ayudainf/aprendainf/Excel2000/Excel2000.pdf.

Rivera-Gutiérrez, H. F. (2004). Guía básica para integrar conjuntos de datos de referencia para

validación de información utilizando Microsoft Excel. Instituto de Investigación de Recursos Biológicos Alexander von Humboldt, Bogotá, Colombia. Extraído el 30 de diciembre de 2004 de http://www.humboldt.org.co/sib/content.jsp?doc=documentos.

Datos del Autor

Pablo Andrés Guzmán González

Estudios realizados: Biología. Universidad del Valle, Cali, Colombia.

Experiencia profesional: Manejo de Bases de Datos de Colecciones Biológicas.

Categoría para este trabajo: Computación.

Otra Categoría sugerida: Bioinformática.