Quitar duplicados y hacer una tabla dinámica

Quitar duplicados y hacer una tabla dinámica.

Vamos a realizar un ejemplo de cómo quitar duplicados.

Si lo hacemos con una tabla dinámica, los duplicados los quita ella, pero nosotros lo vamos a realizar de una forma manual, como si no pudiéramos usar las tablas dinámicas.

Ejemplo:

Tenemos una serie de empresas, de diferentes provincias. Cada empresa ha realizado unas ventas, pero sucede que una empresa se repite varias veces a lo largo de la tabla.

Vamos a quitar las empresas que se repiten y dejaremos solo una de ellas, es decir, si se repite “El corte ingles” seis veces, dejaremos solo uno, y calcularemos el total de las ventas de dicho establecimiento, como una tabla dinámica.

La tabla de datos es la siguiente.

Lo primero que vamos a realizar es crear la formula que nos detectara los registros duplicados.

La formula a usar será la siguiente:

=CONTAR.SI($A$5:A5;A5)

¿ Qué significa ?

La función CONTAR.SI, cuenta las celdas que cumplen con una condición.

La sintaxis de la función es la siguiente:

Rango. Es el rango de celdas donde vamos a realizar la comparación.

Criterio. El criterio el cual será comparado con el rango de celdas.

¿ Que significa entonces la función ?

La función CONTAR.SI, va a comparar el valor del rango de celdas desde A5 a A5, lo va a comparar con el valor de A5, si dicha condición se cumple, lo contara, es decir, dará como resultado 1, en caso de no haber coincidencia, seguirá contando, o sea, 2, 3, 4…

Esta función o expresión lo iremos copiando hacia abajo, entonces, en este caso solo la primera referencia a una celda A5, la vamos a convertir en absoluta, porque no queremos que cambie.

La traducción de la fórmula en la tabla seria de la siguiente manera:

El valor de A5 es Servicio Andaluz de Salud.

Según la fórmula, vamos a comparar el rango de celdas de Servicio Andaluz de Salud a Servicio Andaluz de Salud.

O lo que es lo mismo $A$5:A5.

Dicho valor lo va a comparar consigo mismo, o sea, con Servicio Andaluz de Salud.

En este caso, la condición se cumple, entonces lo cuenta, pondría un 1.

Lo siguiente es copiar la formula, hasta el último registro.

Para ello, pinchamos en la parte derecha inferior de la celda, y, la copiamos hacia abajo.

En la siguiente celda pondrá la siguiente fórmula:

=CONTAR.SI($A$5:A6;A6)

En esta fórmula, el rango de A5 a A6, lo compara con el valor del celda A6, si coincide, lo cuenta, es decir, pone un uno, en caso contrario sigue contando 2, 3, 4…

Después, compararía el valor de A7, después de A8 y así sucesivamente.

Bueno, el primer paso ya esta echo. Los registros duplicados aparecen con el numero 1.

Ahora, tenemos que sacar de la tabla aquellos elementos que contengan el valor 1, que son los nombres sin duplicar.

Para ello, usaremos la función FILA.

FILA

Nos devuelve el número de fila que ocupa la referencia que proporcionemos como argumento. Si omitimos el argumento, la función FILA supondrá que nos referimos a la celda donde se encuentra actualmente.

La sintaxis que vamos a usar, es la siguiente:

=SI($D5=1; FILA($D5:$D$49))

¿ Que significa ?

Compara el valor de la celda D5 con el valor 1, si el resultado es afirmativo, nos dará su posición (FILA).

Hemos usado las referencias relativas y absolutas, para cuando la vayamos a copiar, solo modifique las celdas que queremos.

Hemos puesto el primer argumento, como relativa, $D5, porque cuando la copiemos, queremos que vaya cambiando solo el numero de fila, o sea D6, D7D8….

El segundo argumento, que es lo que pasara si la condición se cumple, FILA($D5:$D$49), hemos puesto la primera referencia del rango en relativa, para que igual que en el caso anterior, cuando la copiemos vaya cambiando solo el numero de fila.

D49, que es el final del rango, lo hemos puesto como absoluto, eso es debido a que no queremos que cambie, porque es la celda final, no contara mas de esa celda.

Ya podemos copiarla hasta la celda D49.

Este será el resultado.

Ahora, lo siguiente, es que los números obtenidos habrá que ordenarlos de menor a mayor. Recuerdo que los números obtenidos corresponden a números de filas, que serán los que después usemos para la captura de datos.

Para ello, usaremos la función K.ESIMO.MENOR.

Antes de usar la función, vamos a crear un contador, ya que el argumento K, se refiere a la posición que queremos obtener. Como en este caso, son varias celdas, usaremos el contador, para no tener que ponerlo a mano, pues se haría un trabajo un poco pesado.

¿Cómo crear un contador?

Nos posicionamos en la celda donde queremos que comience el contador.

En este caso, F5.

Colocamos en la celda F5, el valor 1.

Seguidamente, copiamos el numero hasta la celda D49, pero antes de soltar el botón del ratón, pulsamos <CTRL>, y a continuación, soltamos.

La sintaxis de la función K.ESIMO.MENOR que usaremos es la siguiente:

=K.ESIMO.MENOR($E$5:$E$49;$F5)

Hemos puesto en absoluto el rango de búsqueda, porque dicho rango no va a cambiar, y, relativa el contador, porque dicho contador si debe de cambiar, debe de ir de 1 en adelante.

Este sería el resultado.

Ya tenemos ordenados los números de filas, ahora ya solo queda sacar la información de dichos registros.

Para ello, usaremos la función INDICE.

Nosotros usaremos para este ejemplo la forma matricial.

L a sintaxis de la función es la siguiente:

=INDICE($A$1:$C$49;$G5;1)

¿ Qué significa ?

Con esta función, estamos indicándole, que dentro del rango de celdas A1:C49, empiece a contar en la celda A1, que baje el numero de celdas indicados por la celda G5, en este caso, 5, y una vez llegado a la posición me devuelva el valor del campo 1.

Teniendo en cuenta que la hoja de cálculo está formada por los siguientes campos.

El resultado sería el siguiente:

Si nos fijamos en la tabla no veremos ningún nombre duplicado.

Ya tenemos echa la tabla.

Lo siguiente será crear un cuadro combinado, donde aparezcan las empresas sin duplicar.

Para ello, a las empresas no duplicadas, vamos a darle un nombre de rango.

Para ello, seleccionamos las empresas no duplicadas, pinchamos en:

Ponemos el nombre que queramos ponerle al rango, en este caso, le pondré EMPRESAS.

Se nos abrirá la siguiente pantalla.

Este es el editor de Visual Basic.

Vamos a crear un formulario, y a continuación, creamos un COMBOBOX, que es una lista desplegable.

Para crear el formulario, pinchamos con el botón derecho del ratón en:

Se nos abrirá la siguiente pantalla o ventana.

Seleccionamos “Insertar”.

Se nos abrirá un submenú.

Elegimos “Userform”.

Nos aparecerá la siguiente pantalla:

Dentro del cuadro de herramientas, pinchamos en “cuadro combinado”.

Posicionamos el cuadro combinado donde queramos dentro del formulario.

Ahora, pinchamos en el cuadro combinado con el botón derecho del ratón, y, elegimos “propiedades”.

Se nos abrirá a la izquierda el cuadro de propiedades del cuadro combinado.

Dentro de las propiedades, podremos cambiar la configuración del cuadro combinado.

Lo primero que vamos a realizar, es decirle al cuadro combinado donde debe de cargar los datos de la lista.

Para ello, dentro de “propiedades”, vamos “RowSource”, y es ahí donde debemos de poner el nombre de rango creado, es decir, “EMPRESAS”.

Ya tenemos el cuadro combinado cargado con los nombres de empresas no duplicados. Para poder probar el formulario, debemos de poner un control ActiveX dentro de la hoja de cálculo, que llame al formulario.

Para ello, dentro de la hoja de cálculo, elegimos donde queremos poner el botón de comando.

Dentro de la pestaña programador o desarrollador.

Nos vamos al menú de “Controles”, pinchamos en la pestaña “Insertar”, y, elegimos dentro de Comandos Contr

Lo colocamos dentro de la hoja de cálculo, donde queramos.

Pulsamos dos veces con el ratón dentro del botón de comando.

Se nos abrirá el editor de visual basic, como la siguiente imagen:

Todo lo que pongamos dentro de la subrutina CommanButton1_Click((), se ejecutara cuando pulsemos el botón de comando.

En este caso, vamos a llamar al formulario, para ello, debemos de poner la siguiente sintaxis :

Userform2.show

¿ Que significa ?

Show en ingles significa mostrar.

Userform2 es el nombre de nuestro formulario.

Entonces, le estamos diciendo, “mostrar formulario userform2”

Quedaría de la siguiente

Debemos dejar desactivada la Vista D

Ahora, vamos a pulsar sobre el botón de comando, después, saldrá el formulario, como aparece en la siguiente imagen.

Si pulsamos sobre el cuadro de lista, deben de aparecer la lista de empresas no duplicadas.

Ahora, vamos a cambiarle el aspecto al formulario.

Lo primero que voy hacer es cambiar el color del formulario.

Dentro del formulario, si no estuvieses las propiedades visibles en el lado izquierdo, pinchamos con el botón derecho del ratón, y, elegimos propiedades.

Ya debe de aparecer en la parte izquierda, la ventana de propiedades.

Ahora, me voy a la pestaña “BackColor”, y pincho en la flecha hacia abajo.

Se nos abrirá una ventana como la siguiente:

Elegimos Paleta.

Aparecerá la siguiente ventana:

Ahora, seleccionamos el color que más nos guste. Yo voy a elegir el siguiente color.

Quedaría el formulario de la siguiente manera:

Ahora, voy a cambiar el nombre del formulario.

También voy a cambiarle el color de fondo al cuadro combinado. Para ello, dentro de propiedades del cuadro combinado, en la pestaña “BackColor”, elegimos el color que más nos guste.

Elegimos Paleta.

Ahora, voy a cambiarle el nombre al formulario. Para ello, en propiedades del formulario, en la ventana:

cambiamos el nombre.

En este caso, le voy a llamar EMPRESAS.

Quedaría como la imagen siguiente:

Lo siguiente va a ser, cambiar el color de la letra, y, color del cuadro combinado.

Para ello, pincharemos con el botón derecho del ratón sobre el formulario, elegiremos “propiedades”.

Dentro de propiedades, nos vamos a la ventana “Font”.

Pinchamos en los tres puntitos que aparecen en la parte de debajo de la derecha.

Aparecerá la siguiente ventana.

Voy a elegir como estilo de fuente “Negrita” y como tamaño “14”.

También voy alinear el texto al centro. Para ello, en la ventana:

elegimos el numero 2.

Cerramos el editor de Visual Basic.

Vamos a probar el formulario, para ver como esta de momento. Para ello, pulsamos en el botón de comando creado anteriormente en la hoja de cálculo.

Debe de aparecer la siguiente imagen.

Si pinchamos en la flecha hacia abajo, debe de aparecer la siguiente imagen.

Vemos que quizás debamos de agrandar un poco el cuadro combinado, ya que no entran bien todos los nombres de empresas no duplicados.

Para ello, seleccionamos el cuadro combinado dentro del formulario, y lo agrandamos hasta que puedan entrar bien los nombres de empresas.

También he agrandado el formulario. Vamos a volver a probar.

Este es el nuevo resultado, parece que ahora está bien.

Vamos a ponerle una etiqueta al cuadro combinado.

En el cuadro de herramientas, elegimos.

Lo posicionamos encima del cuadro combinado.

Pinchamos dentro del botón de etiqueta, borramos lo que ya está puesto, y ponemos un nombre para poder identificar el cuadro combinado.

Le voy a poner “Elija una empresa”.

Pinchamos con el botón derecho dentro del botón Etiqueta, elegimos, propiedades. Vamos a cambiarle el tamaño y color.

Aparece la siguiente ventana.

Lo dejo en estilo de fuente “Negrita” y Tamaño en 14.

A continuación, pulsamos en:

Aparecerá la siguiente ventana.

Elegimos el color que deseemos, yo vo

Ahora, el formulario queda de esta manera.

Lo siguiente va a hacer poner un botón de comando.

Dicho botón de comando, debe de cerrar el formulario y dentro de la hoja de cálculo, donde nosotros queramos, pegaremos el valor del cuadro combinado.

Pero, lo vamos a realizar de la siguiente manera. Queremos una hoja de cálculo clara y limpia, que el usuario no vea todos los datos que aparecen en la hoja.

Bien, en otra hoja de cálculo nueva, vamos a poner un encabezamiento.

Ahora, vamos a crear un botón de comando que llame al formulario.

Dentro de la hoja de cálculo, pinchamos en la pestaña programador o desarrollador, y, elegimos dentro del menú Controles, botón de comando de Contro ActiveX.

Pinchamos con el botón derecho del ratón en el botón de comando, elegimos propiedades.

Vamos a cambiarle el nombre. En la ventana llamada “Caption”, vamos a ponerle el nombre de Empresas.

Vamos a cambiarle también el color.

Aparecerá la siguiente ventana.

Elegimos un color.

Desmarcamos la casilla de Vista Diseño. Así se vera de momento la hoja de cálculo.

Vamos a configurar el botón para que cuando pinchemos en él se abra el formulario. Esto ya lo hemos hecho anteriormente.

Marcamos Vista Diseño.

Pinchamos dos veces en el botón de comando.

Aparece la siguiente ventana.

Dentro de la subrutina vamos a poner lo siguiente:

Empresas.show

Que significa, que cuando pinchemos en el botón de comando, debe de aparecer el formulario.

Desmarcamos Vista Diseño, y probamos.

Pinchamos en el botón de comando y deb

Si pinchamos en el cuadro combinado, deben de aparecer las empresas.

Lo siguiente que vamos a realizar es asignar a una celda de la hoja de cálculo, el valor que seleccionemos en el cuadro combinado.

Para ello, debemos de poner un botón de comando dentro del formulario.

Pinchamos en el cuadro de herramientas, elegimos botón de comando y lo colocamos dentro del formulario.

Pulsamos sobre el botón de comando con el botón derecho del ratón, elegimos propiedades.

Vamos a cambiarle el nombre y el color.

En la ventana “Caption”, ponemos el nombre

Vamos a poner “ACEPTAR”.

Ahora, cambiaremos el color de la letra y de fondo. Para ello, nos posicionamos en la ventana:

Elegimos “paleta”

Seleccionamos el color que deseamos.

Seguidamente, nos posicionamos en la ventana:

Seleccionamos el color que más nos guste.

De momento, el formulario queda de la siguiente manera.

Ahora, debemos de configurar el botón de comando, para que cuando se pinche, se oculte el formulario y a una celda de la hoja de cálculo, el valor elegido del cuadro combinado.

Lo haremos de la siguiente manera:

Pinchamos dos veces en el botón de comando.

Aparece la siguiente ventana:

Dentro de la subrutina pondremos lo siguiente.

EMPRESAS.hide

RANGE(“C9”).VALUE=EMPRESAS

¿ Que significa ?

En la primera fila, estamos indicando que al pulsar en el botón de comando, oculte el formulario, y, a continuación, en la celda C9 de la hoja de cálculo, debe de poner el valor del cuadro combinado.

Si pinchamos en el botón de comando de la hoja de cálculo.