Monografias.com > Computación > Software
Descargar Imprimir Comentar Ver trabajos relacionados

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




Enviado por pabloguzman78



    1. Resumen
    2. Planteamiento del
      problema
    3. Aspectos a tener en
      cuenta
    4. Un tema de aplicación para
      el procedimiento
    5. 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.

    No.

    Departamento

    Municipio

    Localidad

    Latitud

    Longitud

    1

    Amazonas

    EL ENCANTO

    Alpena

    -1.0166

    -74.0666

    2

    amazonas

    EL ENCANTO

    Amacocha

    -1.4166

    -73.5500

    3

    Amazonas

    LA CHORRERA

    Abisinia

    -1.3833

    -72.2000

    4

    Amazonas

    LA PEDRERA

    Cachorras

    -1.2333

    -72.1833

    5

    Amazonas

    LA PEDRERA

    Cano Aupe

    -1.4744

    -69.4644

    6

    Antioquia

    ABEJORRAL

    Abejorral

    5.8000

    -75.4333

    7

    Antioquia

    ABEJORRAL

    Alto El Patio

    5.8666

    -75.4666

    8

    Antioquia

    ABEJORRAL

    Cerro El Buey

    5.8833

    -75.4333

    9

    Antioquia

    ABRIAQUI

    Alto Horqueta

    6.5666

    -76.1000

    10

    Boyaca

    AQUITANIA

    Alto del Gallo

    5.4666

    -72.8000

    11

    Boyaca

    AQUITANIA

    Aquitania

    5.5166

    -72.8833

    12

    Cauca

    ALMAGUER

    Almaguer

    1.9144

    -76.8550

    13

    Cauca

    ALMAGUER

    Cerro Jopias

    1.8625

    -76.8469

    14

    Cauca

    CAJIBIO

    Casas Viejas

    2.6500

    -76.7000

    15

    Cauca

    CAJIBIO

    Dinde

    2.7000

    -76.7333

    16

    Guajira

    BARRANCAS

    Alto Las Cabeceras

    10.8125

    -72.7344

    17

    Guajira

    BARRANCAS

    Alto Los Tocos

    10.9738

    -72.9133

    18

    Guajira

    RIOHACHA

    Arena

    11.1875

    -72.7263

    19

    Guajira

    RIOHACHA

    Arroyo Ahumada

    11.4666

    -73.0500

    20

    Quindio

    ARMENIA

    El Meson

    4.5333

    -75.7333

    21

    Quindio

    ARMENIA

    Estacion Ortega Diaz

    4.4833

    -75.7500

    22

    Quindio

    BUENAVISTA

    Buenavista

    4.3666

    -75.7666

    23

    Tolima

    ALPUJARRA

    Arada

    3.4333

    -74.8833

    24

    Tolima

    ALPUJARRA

    Buenos Aires

    3.4166

    -75.0166

    25

    Tolima

    COYAIMA

    Coyaima

    3.7833

    -75.2000

    26

    Tolima

    COYAIMA

    El Tambo

    3.6666

    -75.0833

    27

    Tolima

    COYAIMA

    Guaguarco

    3.7000

    -75.1500

    28

    Valle del Cauca

    ALCALA

    El Jazmin

    4.6500

    -75.8166

    29

    Valle del Cauca

    ANDALUCIA

    Boca Tulua

    4.1333

    -76.2166

    30

    Valle del Cauca

    ANDALUCIA

    Bocas de Tulua

    4.1333

    -76.2166

    31

    Valle del Cauca

    ANDALUCIA

    Campoalegre

    4.1833

    -76.2166

    32

    Sucre

    BUENAVISTA

    Buenavista

    9.3166

    -74.9666

    33

    Sucre

    CAIMITO

    Aguilar

    8.7666

    -75.1333

    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.

    Nota al lector: es posible que esta página no contenga todos los componentes del trabajo original (pies de página, avanzadas formulas matemáticas, esquemas o tablas complejas, etc.). Recuerde que para ver el trabajo en su versión original completa, puede descargarlo desde el menú superior.

    Todos los documentos disponibles en este sitio expresan los puntos de vista de sus respectivos autores y no de Monografias.com. El objetivo de Monografias.com es poner el conocimiento a disposición de toda su comunidad. Queda bajo la responsabilidad de cada lector el eventual uso que se le de a esta información. Asimismo, es obligatoria la cita del autor del contenido y de Monografias.com como fuentes de información.

    Categorias
    Newsletter