Consejo: Otros idiomas son traducidos por Google. Puedes visitar el English versión de este enlace.
Iniciar sesión
x
or
x
x
Suscríbete
x

or

¿Cómo comparar si las celdas múltiples son iguales en Excel?

Como todos sabemos, para comparar si dos celdas son iguales, podemos usar la fórmula A1 = B1. Pero, si desea verificar si varias celdas tienen el mismo valor, esta fórmula no funcionará. Hoy, hablaré sobre algunas fórmulas para comparar si las celdas múltiples son iguales en Excel.

Compare si las celdas múltiples son iguales con las fórmulas


Compara dos rangos y encuentra si las celdas son iguales o no:

Con Kutools for Excel, Comparar celdas utilidad, puede encontrar rápidamente los mismos o diferentes valores entre dos celdas.

doc-check-if-equal-cells-6

Kutools for Excel: con más de 200 útiles complementos de Excel, de prueba sin límite en días 60. Descargar y prueba gratis ahora!


flecha azul burbuja derecha Compare si las celdas múltiples son iguales con las fórmulas


Supongamos que tengo el siguiente rango de datos, ahora necesito saber si los valores en A1: D1 son iguales, para resolver esta tarea, las siguientes fórmulas lo ayudarán.

doc-check-if-equal-cells-1

1. En una celda en blanco además de sus datos, ingrese esta fórmula: = Y (EXACTA (A1: D1, A1)), (A1: D1 indica las celdas que quiere comparar, y A1 es el primer valor en su rango de datos) ver captura de pantalla:

doc-check-if-equal-cells-2

2. Entonces presione Ctrl + Shift + Enter para obtener el resultado, si los valores de la celda son iguales, se mostrará VERDADEROde lo contrario, se mostrará FALSO, mira la captura de pantalla:

doc-check-if-equal-cells-3

3. Y seleccione la celda, luego arrastre el controlador de relleno al rango en el que desea aplicar esta fórmula, obtendrá el resultado de la siguiente manera:

doc-check-if-equal-cells-4

Notas:

1. La fórmula anterior es sensible a mayúsculas y minúsculas.

2. Si necesita comparar los valores sin distinción entre mayúsculas y minúsculas, puede aplicar esta fórmula: = COUNTIF (A1: D1, A1) = 4, (A1: D1 indica las celdas que quiere comparar, A1 es el primer valor en su rango de datos, y el número 4 se refiere a la cantidad de celdas que desea verificar si), luego presione entrar clave, y obtendrá el siguiente resultado:

doc-check-if-equal-cells-5


Artículo relacionado:

¿Cómo verificar si el número es entero en Excel?


Kutools for Excel: la mejor herramienta de productividad de Office aumenta su productividad en un 80%

  • Super Formula Bar (edite fácilmente varias líneas de texto y fórmula); Diseño de lectura (lee y edita fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas y mantener datos; Contenido de celdas divididas; Combinar filas duplicadas y suma / promedio... Prevenir células duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Celdas múltiples sin cambiar la referencia de fórmula; Crear referencias automáticamente a múltiples hojas; Insertar viñetas, Casillas de verificación y más ...
  • Fórmulas favoritas e insertadas rápidamente, Gamas, cuadros y cuadros; Cifrar celdas con contraseña Crear una lista de correo y enviar correos electrónicos ...
  • Extracto del texto, Agregar texto, Eliminar por posición, Eliminar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtro a otras hojas); Clasificación avanzada por mes / semana / día, frecuencia y más; Filtro especial por negrita, cursiva ...
  • Combinar libros de trabajo y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes xls, xlsx y PDF...
  • Funciona con Office 2007-2019 y 365, y es compatible con todos los idiomas. Es fácil de implementar en su empresa. Funciones completas de prueba gratuita de 60-day.
pestaña kte 201905

Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilitar la edición y lectura con pestañas en Word, Excel, PowerPoint, Editor, Acceso, Visio y Proyecto.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta tu productividad en un 50% y reduce cientos de clics de ratón por ti todos los días!
fondo officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    AG · 7 months ago
    =COUNTIF(A1:D1,A1)=4 ---> mind = blown
  • To post as a guest, your comment is unpublished.
    M.Irfan · 9 months ago
    how can i find the matching numbers in a column
  • To post as a guest, your comment is unpublished.
    ar · 9 months ago
    hi, i want to display the value / text of a table if a particular value is below 100. is there any option
  • To post as a guest, your comment is unpublished.
    Dal · 10 months ago
    Hi
    I need to highlight multiple numbers that appear three times or more in one column.
    Can anyone assist?
    Thanks
    • To post as a guest, your comment is unpublished.
      skyyang · 10 months ago
      Hello, Dal,
      To highlight the cell values that appear three times or more, the Conditional Formatting can help you. Please enter this formula into the Conditional Formatting: =COUNTIF($F$2:$F$500,F2)>2 (Note: Change the cell references to your need),and then choose one color for highlighting the cells.
  • To post as a guest, your comment is unpublished.
    Bob Olanski · 10 months ago
    The "And(Exact...." works great if you have a set range. In my case, It is multiple cells within a range of columns.

    Basically need the comparison to evaluate A7, B7, M7, AD7, AE7, AX7, BC7, and BQ7, and return "TRUE" if they are identical text.
  • To post as a guest, your comment is unpublished.
    sonia · 11 months ago
    i want to know part number from in one column is duplicated in any other cells of adjacent column
  • To post as a guest, your comment is unpublished.
    Paul · 1 years ago
    I have Cells A1=35, A2-45, A3=50, A4=75, A5=80, B1=40, B2=19, B3=55, B4=31, B5=22. In this, A1 IS 35 AND B1 IS 40. What I want to find out is find out whether anywhere else in the range the this combination is appear or repeated and how many times it is repeated i need to know. Can anyone help me out in this. If there is no formula, a vba would be helpful, thanks in advance.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Paul,
      Can you give your problem more clearly and you can upload an attachment screenshot here.
      Thank you!
  • To post as a guest, your comment is unpublished.
    Jim T · 1 years ago
    Interesting, this fails for me in 2016 version
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Jim,
      The above formulas work well in Excel 2016, when you apply the first formula, please remeber to press Shift + Ctrl + Enter keys together to get the correct result.
      Please try again, thak you !
  • To post as a guest, your comment is unpublished.
    jignesh · 2 years ago
    i have six column a b c d e f, in column e i want to put higher value among a & c, and in column f it is dependent on e, like if e is a than b, if e is c than d. how could i solve this???
  • To post as a guest, your comment is unpublished.
    Holly · 2 years ago
    This worked great for me I did =And(exact(A2:C2,D2:F2)) to validate that cells A2-C2 exactly matched D2-F2.

    Thanks so much!
  • To post as a guest, your comment is unpublished.
    Shan · 2 years ago
    I wanted to compare the alphabets with 4 columns for every 4 rows. For example,
    A = [aabc;dbba;a] B = [aabc;baba;c] C = [aabc;dbba;b] D = [bcac;bcbc;a]

    If we look at the table above shows first four rows, "aabc" repeated in 3 columns. So, out of 4, 3 gets correct matching.it is 3/4. for the second cycle, we can see "dbba" repeated so it 2/4.This is for 4 rows vs 4 rows, now the same matching method for taking one line down, two line down and three line down comparing with other string. Means, the rows will be constant and just the alphabets changing. The formula can be in the new column say E and results will be there. Any idea?
  • To post as a guest, your comment is unpublished.
    Shan · 2 years ago
    How about comparing multiple rows with certain columns?
    I wanted to compare the alphabets with 4 columns for every 4 rows.For Example,

    A = [aabc;dbba;a] B = [aabc;baba;c] C = [aabc;dbba;b] D = [bcac;bcbc;a]

    If we look at the table above shows first four rows, "aabc" repeated in 3 columns. So, out of 4, 3 gets correct matching.it is 3/4. for the second cycle, we can see "dbba" repeated so it 2/4.This is for 4 rows vs 4 rows, now the same matching method for taking one line down, two line down and three line down comparing with other string. Means, the rows will be constant and just the alphabets changing. The formula can be written in a new cell may be the next column E. Any Idea?
  • To post as a guest, your comment is unpublished.
    Frances H · 2 years ago
    Useful formula - works great thanks. I'm curious why do you have to use Ctrl + Shift + Enter ?
  • To post as a guest, your comment is unpublished.
    Archana Maru · 2 years ago
    Hi there!! I am very new to this excel validation and hence do not know much. I am trying to write a function in which if the values in A1 to A10 is True then the result in A11 should show Accepted and if they are not same then the result should prompt Rejected. Can you please help me write a forumula for the same.
    Thanks
  • To post as a guest, your comment is unpublished.
    Mimi · 2 years ago
    The cells that i want to compare value are not next to each other. So when i separate them in the formula with a "," it doesn't work.
  • To post as a guest, your comment is unpublished.
    EricMartinson · 2 years ago
    This is a great tip and did exactly what I needed. Very helpful! Thanks!
  • To post as a guest, your comment is unpublished.
    Sergey · 2 years ago
    This doesn't work for me, the result is returned as #VALUE!
    • To post as a guest, your comment is unpublished.
      Damo · 2 years ago
      You are not doing the "Ctrl + Shift + Enter keys together to get the result" part... I had the same issue, then read further in the article
  • To post as a guest, your comment is unpublished.
    imran · 2 years ago
    What about the empty cells?
    I have data on multiple columns and the column count is not same
    data is spread across columns upto 17
    some have data up to 4 columns