Concatenar para comparar

Descargar el fichero: concatenaycompara.xlsx

Comparar un valor con otro es algo sencillo (Hoja1). Comparar un valor con otros, es algo más complicado (Hoja2). Y comparar un conjunto de valores con otros conjuntos requiere algún truco o idea más elaborada (Hoja3). En este caso lo que realizaremos es la comparación de un conjunto de 5 valores de una fila, con una matriz compuesta por varias filas. Lo que queremos es que Excel nos diga si nuestra fila está en la matriz, y en caso afirmativo que nos indique en qué fila se encuentra.

Comparar dos valores (Hoja1)

Si queremos comparar el valor que está en la celda A1 con el valor que está en la celda B1, podemos escribir en la celda C1 la siguiente expresión:

=A1=B1

Excel responderá con la palabra VERDADERO si la igualdad es cierta, y con la palabra FALSO en caso contrario.

En informática los verdaderos se representan por el número 1, y los falsos por el número 0. Esto se ve muy bien al convertir los verdaderos y falsos en unos y ceros. Para realizar esta conversión a números disponemos de dos trucos:

Método 1

Multiplicamos la igualdad por 1. Así forzamos a que el valor se convierta en numérico. En la celda D1 utilizamos la expresión:

=+(A1=B1)*1

Método 2

A la expresión que compara ambas celdas (=A1= B1) la precedemos de dos signos menos. Así la celda E1 es:

=--(A1=B1)

El primer singo menos equivale a multiplicar por -1. Con esto ya hemos convertido la expresión en numérica. Pero para que no queden números negativos volvemos a multiplicar por -1, de ahí el segundo signo menos.

En la imagen observamos un caso en el que comparamos los elementos de la columna A con los elementos de la columna B. Con los métodos comentados anteriormente obtenemos en las columnas D o E una serie de ceros y unos.

En ciertas ocasiones necesitaremos conocer cuantos elementos de la columna A no coinciden con su pareja correspondiente de la columna B. Este dato viene dado por el número de ceros de la columna E, que en la imagen son 2. Como contar ceros es algo más complicado que contar unos lo que hacemos en la columna F es introducir el operador lógico NO que convierte lo VERDADERO en FALSO, y lo FALSO en VERDADERO. Así la celda F1 será:

=--NO(A1=B1)

Al copiar hacia abajo nos proporciona la columna F. Pulsando con el ratón sobre el indicador de columna F, queda seleccionada toda la columna F, y Excel nos proporciona, abajo a la derecha, la suma de todos los valores de esta columna. Como podemos comprobar en la imagen la suma es 2, lo que indica que son dos los elementos de la columna A que no coinciden con los elementos de la columna B.

En el fichero (Hoja1) los valores pueden cambiar ya que se obtienen con números aleatorios. De hecho al pulsar la tecla F9 de recálculo manual los valores cambiarán.


Comparar con los anteriores (Hoja2)

Supongamos que en la columna A estamos introduciendo datos y no deseamos que al introducir un nuevo dato ya esté introducido con anterioridad en la lista. Vamos a introducir la referencia de una serie de canciones dando el grupo y el título de la canción. Al lado introducimos una fórmula que no indicará con un 0 si la canción no esta repetida y con un 1 si ya está incluida con anterioridad y por tanto está repetida.

La fórmula de la celda B44 es:

=CONTAR.SI($A$1:A43;A44)

Observar dónde van los dólares. Van en A1 para que quede fija al copiar hacia abajo la fórmula. Esto se hace para que se pueda comparar con todos los anteriores.

En el ejemplo la canción introducida en la fila 44 está repetida, lo que se indica con un 1 en la celda B44.


Comparar varias celdas con las filas de una matriz (Hoja3)

Si comparamos varias celdas podemos obtener lo siguiente:

En la tabla de la izquierda tenemos un matriz con 25 filas (de A1 a E25). Cada fila esta compuesta de 5 valores numéricos. En la tabla de la derecha (H1:L17) disponemos de varias filas compuestas también por 5 valores numéricos. Deseamos saber si para cada una de las 17 filas de la derecha esa combinación de números se encuentra alguna fíla de la matriz de la izquierda.

Para ello vamos a crear la columna F (color purpura) y la columna M (color gris). Las columnas F y M se obtienen concatenando los 5 valores de cada fila. En este caso los hemos separado por comas, pero en la mayoría de los casos no sería necesario. Para concatenar utilizamos el signo & que aplicado a celdas, o a texto entrecomillado nos permite concatenar caracteres alfanuméricos. Así la fórmula de la celda F1 es:

=+A1&","&B1&","&C1&","&D1&","&E1

Observar que por tratarse de una fórmula de Excel comienza por el signo "=". Si no ubiéramos separado con comas la fórmula sería:

=+A1&B1&C1&D1&E1

Para comparar si una fila de la segunda tabla está en la matriz de la izquierda empleamos la función:

=CONTAR.SI(rango;criterios)

Ejemplo

=CONTAR.SI(B2:B25,"Nancy")

En el ejemplo se cuenta el número de veces que aparece el texto "Nancy" en el rango B2:B25.

En nuestro caso la fórmula de la celda N15 es:

=CONTAR.SI(datos;M15)

siendo datos el rango F1:F25 (de color purpura).

Lo que hace la función es contar cuantas veces aparece el contenido de la celda M15 dentro del rango datos.

En la columna O deseamos que en caso de coincidencia nos diga en que fila de la matriz se encuentran los números que se repiten. Para ello introducimos el condicional SI. La fórmula de la celda O15 es:

=SI(CONTAR.SI(datos;M15);COINCIDIR(M15;datos;0);0)

Cuando la función CONTAR.SI proporciona el valor cero la condición se entiende que no se cumple (equivale a un FALSO), y cuando la función CONTAR.SI proporciona un valor 1 o superior se entiende que la condición si se cumple (equivale a un VERDADERO).

Cuando la condición se cumple introducimos la función COINCIDIR que nos dice en que lugar del rango datos se encuentra el valor de la celda M15.

Si en la columna N encontramos un valor superior a 1, indica que en la matriz de la izquierda existe una o varias filas repetidas. En caso de existir una fila repetida dentro de la matriz, la columna O nos daría la primera fila que se repite.