miércoles, 4 de agosto de 2010

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.

17 comentarios:

  1. Excelente apunte sobre esta formula, para considerarla enlos trabajos de oficina.
    muchas gracias

    ResponderEliminar
  2. Felicitaciones por la explicación...Muy bien detallada....
    Tengo un caso parecido por resolver, por ejemplo la celda M15 en lugar de tener 7 8 1 2 9 tiene 1 2 9 7 8. Como haría para que la fórmula de coincidencia me considere cualquier permuta de esta serie? Es decir, además de 78129, cualquier otra permuta que se pueda presentar a partir de esta combinación.

    Gracias de antemano

    ResponderEliminar
  3. Hola Sigoaprendiendo.

    Un método para conseguir lo que pretendes consiste en utilizar la función K.ESIMO.MENOR. Con ella puedo obtener los valores, ordenados, ya que le pido el k.esimo.menor primero, luego el segundo, y asía hasta el último.
    En el ejemplo que pones sería:
    1 2 7 8 9. Estos valores se podrían concatenar y quedaría así: 12789, o bien, separados por comas, si ello fuera preciso. De esta forma, conseguimos que todas las posibles permutaciones queden iguales, y ahora ya puedo buscar si ese valor se encuentra o no repetido en la otra tabla.

    Un cordial saludo.

    ResponderEliminar
  4. Lo intentaré de la manera que me sugieres....Gracias por tu oportuna respuesta...

    Cuando compartes conocimientos seguro que recibes más a cambio...

    Felicitaciones por tu dominio en el tema...

    Suerte y que Dios te bendiga...

    ResponderEliminar
  5. Hola buenos días: he visto este blog y me ha parecido muy interesante, yo la duda que tengo es la siguiente. Si tengo en una columna una serie de número por ejemplo
    lunes 1
    martes 1.5
    miercoles 2
    jueves 3.5
    viernes 2
    me gustaria saber como podría saber cuantos días seguidos lleva subiendo o bajando el valor.

    una función que me diga que el valor lleva dos días seguidos subiendo.
    Muchas gracias lo he estado intentando hacer y no se me ha ocurrido algo.

    ResponderEliminar
  6. Si alguien sabe como carajos se multiplica por -1 en esa pichera de Excel 2010 les agradecería de antemano me corrigieran:

    =celda*(-1)
    =-1*celda

    Gracias por su colaboración.

    ResponderEliminar
  7. Hola Adolfo:
    Desearia saber que tengo que hacer para que en una fila o columna sume los numeros que encuentre iguales y cuando no encuentre uno igual vuelva a empezar a sumar y ponga el resultado en orden de aparicion de los diferentes numeros.
    ejemplo:
    1 2 2 2 3 3 1 1
    el resultado seria: 1=1, 2=3, 3=2, 1=2
    Gracias

    ResponderEliminar
  8. Hola buenas tardes agradecería si me pueden colaborar necesito comparar dos columnas en la columna a tengo valores 1 y 0 y en la columna b valores diferentes, necesito que en la columna c me salga el valor mayor de la columna b y que este sea a su vez 0 en la a.

    ResponderEliminar
    Respuestas
    1. Se puede hacer de muchas formas. Un método es usando la función SI. Otro método es usando Filtro que está en Datos.
      Un saludo.

      Eliminar
  9. Hola buen dia, Tengo una lista de nombre de personas, su puesto y su sueldo, necesito saber a que personas les pagan mas de lo autorizado de acuerdo a un tabular. Ejemplo una hoja: Juan Perez, Pintor, 2500; otra hoja: pintor sueldo minimo 1500, sueldo maximo 2000; como hago la comparación y que me diga que esa persona gana más de los autorizado; Help!!! gracias

    ResponderEliminar
    Respuestas
    1. Hola Vero.
      Puedes utilizar la función lógica SI. Con ella obtendrás un valor lógico (Verdadero o Falso) que te indicará si el salario de una persona sobrepasa cierto límite.
      Incluso podrías ponerlo con Formato Condicional y así obtendrías colores que te marcarían los que sobrepasan el nivel que indiques.
      Un saludo.

      Eliminar
  10. Buenas tardes,
    Estoy intentando averiguar cómo se puede hacer para comparar varios textos en excel.
    Tengo en una tabla: fecha, nombre y datos varios
    En otra tabla; fecha, nombre y datos
    Quiero que me ponga de un color si coincide la fecha con el nombre y los datos.
    Me está resultando imposible, si saben como ayudarme se lo agradecería enormemente.
    Un saludo y gracias de antemano.
    Rosa.

    ResponderEliminar
  11. Hola Rosa.
    Para lo que necesitas el ejemplo que se desarrolla en este post puede ser la base para que construyas tu propio sistema de comparación. La idea como ves consiste en crear una columna que se obtiene concatenando varios campos, y luego usas esta columna para compararla con otra que se ha formado de la misma manera. Por este método podrás comparar dos tablas que contengan varias columnas (campos) que deseas confrontar.
    Un saludo.

    ResponderEliminar
  12. Hola Bueno noche me gustaria saber que formula utilize para compar una matrix con varies celdas Es decir son 12 respuestas de un examen con las respuestas de los alumnos

    ResponderEliminar
  13. Al introducir un número en la celda A1, en A2 aparecerá el numero de celdas que en el rango C1 a C30, sean superiores al valor introducido en A1. Cual es la formula de A2, gracias

    ResponderEliminar
  14. buenos dias. Una pregunta, quiero realizar una formula que dada una variable busque su valor asignado en una matriz y ese valor me lo multiplique por un número que va a ser constante? gracias

    ResponderEliminar
  15. Hola me podían ayudar....?
    quiero concatenar varias celdas con nombres repetidos pero los datos no son los mismos

    ResponderEliminar