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.