Vincular columnas con filas en Excel

Este es el caso opuesto al que vimos al tratar de vincular las celdas de una fila a los datos de una columna. Ahora queremos vincular las celdas de una columna a los datos de una fila.

El ejemplo es el que vemos en la imagen siguiente. En la fila 1 y 2 tenemos una serie de años y ventas. Ahora queremos vincular esos datos con los datos presentados en columnas. En nuestro caso debajo de las celdas A5 y B5. Debajo de A5 tenemos la serie de años, y debajo de B5 queremos vincular los datos de las ventas.

Lo haríamos casi igual que en el caso de vincular filas a columnas, con una función de búsqueda, pero en este caso con la función BUSCARH.

La diferencia entre BUSCARV y BUSCARH es que en el primer caso se busca un valor en la primera columna de una matriz y devuelve el valor que aparece en la columna de la matriz que le indiquemos.

En BUSCARH se busca un valor en la primera fila de una matriz y devuelve el valor que aparece en la fila de la matriz que le indiquemos.


En el ejemplo utilizado, se usaría en la celda B6 la siguiente función:

=BUSCARV(A6;$B$1:$J$2;2;FALSO)

Es decir, le estamos indicando: búscame el valor de la celda A6 (2014) en la matriz B1:J2 (lo coloco en valores absolutos para poder copiar la fórmula en las demás celdas) y, si lo encuentras, devuélveme el valor de la segunda fila (100).

Después copiamos la fórmula para los demás años y obtenemos el siguiente resultado:

Vincular filas con columnas en Excel

En ocasiones queremos vincular las celdas de una fila a los datos de una columna.

En la siguiente imagen, por ejemplo, en las columnas A y B tenemos una serie de años y ventas. En otra fila (en el ejemplo es la fila 1, pero podría ser en otra hoja o libro) queremos vincular con datos de la fila de ventas (fila 2) con las ventas que tenemos en columnas (columna B).

Si quisiéramos vincular una fila con otra, sería tan fácil como vincular una celda y copiar a las demás. Pero eso no es válido para vincular filas a columnas.

¿Cómo se podría hacer eso en Excel?

Una opción muy buena es con la función de búsqueda (BUSCARV).

Esta función busca un dato en la primera columna de una matriz y, en caso en encontrarlo, devuelve el valor que se encuentra en la columna que le indiquemos.

En el ejemplo utilizado, se usaría en la celda E2 la siguiente función:

=BUSCARV(E1;$A$3:$B$14;2;FALSO)

Es decir, le estamos indicando: búscame el valor de la celda E1 (2014) en la matriz A3:B14 (lo coloco en valores absolutos para poder copiar la fórmula en las demás celdas) y, si lo encuentras, devuélveme el valor de la segunda columna (100).

Después copiamos la fórmula para los demás años y obtenemos el siguiente resultado:

Salto de línea en celda de Excel

En muchas ocasiones deseamos dar un salto de línea en una celda de excel. 

La forma "chapucera" de hacerlo es insertar espacios en blanco hasta que salte de línea en el punto en que nosotros queramos, pero esa forma no es adecuada ya que si en alguna ocasión cambiamos el tamaño de la letra o el ancho de la columna tendríamos que ajustar de nuevo los espacios en blanco.

Hay una forma para hacerlo: Pulsar Alt + Enter

De esa forma sencilla le indicamos que salte de línea.

Veámoslo en un ejemplo:

En este ejemplo ficticio tenemos un grupo de empresas y su cifra de ventas. Para el caso de la empresa Juan Lozano (Frutas Juan) nos interesa que aparezca en dos líneas (Juan Lozano por un lado y Frutas Juan por otro)

 Nos colocamos en el punto en que queremos se que produzca el salto de línea y pulsamos Alt + Enter.

 

El resultado es el que deseábamos.

Evitar el uso de valores duplicados en Excel

Mejor que tratar de identificar o eliminar datos duplicados en Excel es evitar que esto pueda ocurrir de antemano.

Para ello podemos usar la opción de Validación, que nos permite usar un criterio sobre un rango de celdas para admitir sólo valores que cumplen un criterio.

Ese criterio puede ser "que no esté duplicado"

La forma de conseguir establecer el criterio es el siguiente:

Seleccionamos el rango de los valores donde escribiremos los datos.

Pulsamos Datos / Validación

En la ficha Configuración, dentro de la pestaña Permitir, seleccionamos la opción Personalizada y escribimos la fórmula
=CONTAR.SI(A$2:A2;A2)=1

Le damos a Aceptar.

Con esto conseguimos que si se cuenta en el rango hasta esa celda una vez el valor introducido permite su introducción, pero ya apareció anteriormente, al introducir el valor la fórmula contará dos apariciones, por lo que no permitirá su introducción.

También podemos personalizar el mensaje de error para que nos indique que en caso de estar intentando introducir un nombre duplicado nos avise de este hecho.

Para ello, pulsamos Datos / Validación y seleccionamos la pestaña Mensaje de error.
Ahí podemos indicar el mensaje que deseemos dentro de la opción Título y le damos a Aceptar.

Eliminar datos duplicados en Excel 2010

Ya vimos algunas formas de eliminar datos duplicados en Excel para versiones anteriores a la de Excel 2010.

A partir de la versión 2010 tenemos una opción directa para eliminar los datos duplicados. Se localiza esta función en la ficha Datos, y el comando se denomina Quitar duplicados, dentro de Herramientas de datos.

Este comando elimina las filas con datos duplicados, según se encuentre el cursor.

De forma que si tenemos la siguiente tabla de datos con nombres y fechas de naciemiento:

Si nos situamos sobre la algún datos de la tabla de datos y le damos a Quitar duplicados:

Nos aparece el siguiente cuadro:

Nos identifica la tabla y nos pregunta qué campos (columnas) debe de comprobar.

Si marcamos Nombre y Nacimiento nos elimina las filas donde se repite nombre y nacimiento con datos iguales.
Es decir, eliminaría una vez Miguel 1972 ya que se repite en la fila 3 y 10, pero no repite Ana al tener el año 1972 en la fila 6 y el año 1976 en la fila 16.

Si sólo marco por ejemplo Nombre y desmarco Nacimiento, me eliminaría una fila de Miguel y otra de Ana sin preocuparse por comprobar el año de nacimiento.

Una vez eliminadas las filas duplicadas nos aparece un cuadro que nos informa de las acciones realizadas:

Eliminar datos duplicados en Excel (versiones anteriores a Excel 2010)

Ya vimos en nuestro anterior artículo cómo podemos identificar los datos duplicados en una lista de Excel. Puede que nos interese eliminar esos datos. Para ello lo más fácil es ordenar y eliminar, de la siguiente forma. Para Excel 2010 ya existe función que quita los valores duplicados directamente y que explicaremos en un artículo posterior.

La hoja que habíamos obtenido aplicando la fórmula
=SI(CONTAR.SI(A$2:A3;A3)>1;"Duplicado";"") es la que se muestra a continuación:

Si ordenamos en función de la columna B, ya tenemos todos los duplicados juntos al final (o principio, según el criterio de ordenación) de los datos. Marcaríamos las filas duplicadas y las eliminamos. Ya tenemos los datos sin duplicidades.



Hay muchas posibilidades de marcar las filas duplicadas. Con la fórmula
=CONTAR.SI(A$2:A2;A3) estamos indicando al lado de cada nombre cuantas veces a aparecido anteriormente en la lista.

En nuestro ejemplo obtenemos el siguiente resultado:

La diferencia es que nos indica las veces que ha aparecido cada nombre anteriormente. La solución es la misma que antes: ordenamos, señalamos las filas y eliminamos.

Comprobar si hay datos duplicados

En ocasiones tenemos listas en Excel y queremos comprobar si hay repeticiones. Si la lista es pequeña se comprueba fácilmente a simple vista, pero en ocasiones son listas muy largas y es imposible hacer tal comprobación.

Una manera que yo usaba para comprobar si en una lista hay duplicados era ordenar la lista (que podía estar en una columna) y posteriormente indicar una condicional según la cual SI(A2=A1;"Duplicado";"").
Según esta fórmula comprobaba si el dato era igual que el de la fila anterior y en caso de ser así me lo indica con el tecto "Duplicado". En caso contrario me deja la celda en blanco. Como previamente hemos ordenado todos los datos, en caso de haber datos duplicados, necesariamente se encontraran en filas sucesivas.

Sin embargo hay una forma mucho más sencilla de hacer la comprobación de datos duplicados y que, además, no requiere ordenar los datos. Es la siguiente:

Imaginémonos que tenemos la siguiente lista de nombres, donde deseamos comprobar si hay duplicidades:

Supongamos que en la celda B2 queremos saber si el nombre de la fila 2 (Juan) se repite en la lista de nombres. Pondríamos la fórmula =CONTAR.SI(A:A;"Juan").

¿Con eso qué conseguimos?

Que nos cuente las veces que aparece el nombre "Juan" en la lista y nos lo indica en la celda B2. En este caso 1, lo que significa que no está duplicado.

Para mejorar la fórmula pondremos  =CONTAR.SI(A:A;A2). Esto  nos permite no tener que escribir el nombre y arrastrar la fórmula hacia abajo.

Ahora tratemos de mejorar la forma de indicar las duplicidades. Supongamos que en lugar de aparecer al lado de los nombres el número con las apariciones del mismo en la lista, queremos que aparezca si está duplicado o no.

Para ello usaremos la fórmula condicional.

=SI(CONTAR.SI(A:A;A2)>1;"Duplicado";"")

Con esta fórmula condicional al lado de cada nombre aparecerá el texto "Duplicado" si el nombre aparece más de una vez en la lista y en caso contrario el contenido de la celda estará en blanco.

Claro que puede ser más interesante que a los nombres les aparezca "Duplicado" cada vez que salga a partir de la segunda vez. Es decir, la primera vez que aparezca el nombre no salga la etiqueta de "Duplicado" y cuando salga por segunda vez ya nos indique "Duplicado".

Eso lo conseguimos de la siguiente forma, después de colocarnos por ejemplo en la celda B3
=SI(CONTAR.SI(A$2:A3;A3)>1;"Duplicado";"")

¿Qué le indicamos con esa fórmula? Pues que cuente las veces que el nombre de la fila en cuestión aparece en el rango desde el primer nombre hasta el nombre de esa fila. (Al fijar la fila del inicio del rango A$2) y si aparece más de una vez indica que está "Duplicado".