Las cartas de renovación de Fernside Software toman cada valor de contrato del CRM por el ID de cuenta. A Tamsin Kitchens se le ofrecieron 22.500 £ en vez de 64.800 £, firmó, y se llevó 42.300 £ al año de los ingresos.
| ID de cuenta | Cuenta | Carta | Valor en el CRM |
|---|---|---|---|
| 0015g00000Lm2Xp | Corran Foods | 22.500 £ | 22.500 £ |
| 0015g00000Lm2XP | Tamsin Kitchens | 22.500 £ | 64.800 £ |
Alcance.
IGUAL,INDICE,COINCIDIR,SUMAPRODUCTOyCONTAR.SIfuncionan en todas las versiones de Excel.BUSCARXnecesita Excel 2021 o Microsoft 365.Antes de Excel 365,
INDICEconCOINCIDIR(VERDADERO;IGUAL(...);0)hay que confirmarla con Ctrl+Mayús+Intro. La versión conSUMAPRODUCTOde la sección 5 nunca lo necesita.
01BUSCARV Ignora las Mayúsculas y Dos ID Fueron Uno
El CRM asigna ID de cuenta de 15 caracteres, y en ellos mayúsculas y minúsculas son caracteres distintos. Lm2Xp y Lm2XP son dos clientes. Las funciones de búsqueda de Excel no opinan lo mismo.
La exportación del CRM está en una hoja llamada CRM, con los ID en A2:A9 y los valores en C2:C9. La hoja de renovaciones buscaba cada ID con esta fórmula en C2:
=BUSCARV(A2;CRM!$A$2:$C$9;3;FALSO)
Renovaciones de Fernside Software, valor del contrato por ID de cuenta
Ocho cuentas en A2:F9 de la hoja Renovaciones. A es el ID de cuenta del CRM, de 15 caracteres y sensible a mayúsculas, y B el nombre de la cuenta. La exportación del CRM está en una hoja llamada CRM, con los mismos ID en A2:A9 y el valor del contrato en C2:C9, 234.000 £ en total. C es lo que devolvió BUSCARV, D la búsqueda con IGUAL, E el CONTAR.SI de cada ID en la hoja CRM y F el recuento con IGUAL.
fxLas celdas con fórmulas están resaltadas en verde
Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas
FALSO pide una coincidencia exacta, y la obtuvo, en el sentido de Excel. Coincidencia exacta en BUSCARV significa las mismas letras, en mayúsculas o minúsculas.
Para Tamsin Kitchens, en la fila 6, encontró antes Lm2Xp en la fila 3 y se detuvo ahí.
Escenario: Pon los ocho ID y valores en una hoja llamada CRM, con los mismos ID en A2:A9 de otra hoja. Escribe =BUSCARV(A6;CRM!$A$2:$C$9;3;FALSO) y comprueba que devuelve 22.500 para Lm2XP.
02Gana la Primera Coincidencia, en Ambos Sentidos
Una búsqueda que ignora mayúsculas devuelve la que aparezca primero de cada pareja. La segunda cuenta de cada pareja recibe el valor de la primera, sea mayor o menor.
| Cuenta | BUSCARV | Valor en el CRM | Error |
|---|---|---|---|
| Pellow & Daughters | 48.000 £ | 9.600 £ | +38.400 £ |
| Tamsin Kitchens | 22.500 £ | 64.800 £ | −42.300 £ |
| Las ocho cuentas | 230.100 £ | 234.000 £ | −3.900 £ |
Los dos errores casi se anulan. Un total de renovaciones 3.900 £ por debajo del CRM parece un redondeo o un cambio tardío, así que nadie lo investigó.
El daño estaba en las cartas de cada cuenta. Pellow & Daughters recibió una carta por 48.000 £, cinco veces su contrato, y llamó. Esa llamada fue la primera señal de que algo fallaba.
Escenario: Añade =SUMA(C2:C9) bajo la columna de BUSCARV y =SUMA(CRM!C2:C9) al lado. Comprueba que la diferencia es solo 3.900 aunque dos cuentas fallen en más de 38.000 cada una.
03IGUAL Es la Única Comparación Que Ve Mayúsculas
Todas las comparaciones de texto normales de Excel ignoran las mayúsculas. =A2=A4 compara KqRtA con KqRta y devuelve VERDADERO.
Lo mismo hacen COINCIDIR, BUSCARX, CONTAR.SI, SUMAR.SI.CONJUNTO y el = dentro de SI. IGUAL compara dos textos carácter a carácter, mayúsculas incluidas:
=A2=A4 → VERDADERO
=IGUAL(A2;A4) → FALSO
=IGUAL(A2;A2) → VERDADERO
Con un rango en vez de una celda, IGUAL(CRM!$A$2:$A$9;A2) devuelve ocho valores VERDADERO/FALSO, uno por fila del CRM. En un CRM limpio, exactamente uno es VERDADERO si el ID existe. Todas las soluciones que siguen buscan ese VERDADERO.
Escenario: En una celda vacía, escribe =A2=A4 y después =IGUAL(A2;A4). Comprueba que la primera devuelve VERDADERO y la segunda FALSO para KqRtA y KqRta.
04Busca VERDADERO en la Matriz de IGUAL
En vez de buscar el ID, busca VERDADERO en la lista de resultados de IGUAL. La posición del único VERDADERO es la fila con las mayúsculas correctas.
Con BUSCARX, en D2:
=BUSCARX(VERDADERO;IGUAL(CRM!$A$2:$A$9;A2);CRM!$C$2:$C$9;"No encontrado")
La forma corta, =BUSCARX(VERDADERO;IGUAL(CRM!$A$2:$A$9;A2);CRM!$C$2:$C$9), devuelve #N/D si falta el ID en vez del texto. Las dos devuelven 64.800 para Tamsin Kitchens.
En versiones anteriores, INDICE y COINCIDIR hacen el mismo trabajo:
=INDICE(CRM!$C$2:$C$9;COINCIDIR(VERDADERO;IGUAL(CRM!$A$2:$A$9;A2);0))
El 0 de COINCIDIR no es opcional: sin él, COINCIDIR hace una búsqueda binaria en una lista de VERDADERO y FALSO y cae en cualquier sitio.
| Cuenta | BUSCARV | Búsqueda con IGUAL |
|---|---|---|
| Brightline Studios | 48.000 £ | 48.000 £ |
| Pellow & Daughters | 48.000 £ | 9.600 £ |
| Tamsin Kitchens | 22.500 £ | 64.800 £ |
Escenario: Escribe =BUSCARX(VERDADERO;IGUAL(CRM!$A$2:$A$9;A2);CRM!$C$2:$C$9) en D2 y cópiala hasta D9. Comprueba que D4 devuelve 9.600 y D6 devuelve 64.800.
05SUMAPRODUCTO Funciona en Todas las Versiones
SUMAPRODUCTO trabaja con matrices de forma nativa, así que no necesita Ctrl+Mayús+Intro en ningún Excel. Multiplica la matriz de IGUAL por los valores y suma:
=SUMAPRODUCTO(--IGUAL(CRM!$A$2:$A$9;A2);CRM!$C$2:$C$9)
El -- convierte VERDADERO y FALSO en 1 y 0. Solo la fila con las mayúsculas correctas conserva su valor; las demás quedan en cero.
Tiene dos costumbres que una búsqueda no tiene. Un ID que falta devuelve 0, no un error, y un ID que aparece dos veces devuelve la suma de los dos valores.
Por eso conviene acompañarla de un recuento, =SUMAPRODUCTO(--IGUAL(CRM!$A$2:$A$9;A2)), y fiarse del valor solo donde ese recuento sea 1.
Solo sirve para números; para un nombre o una fecha usa INDICE y COINCIDIR.
Escenario: Escribe =SUMAPRODUCTO(--IGUAL(CRM!$A$2:$A$9;A2);CRM!$C$2:$C$9) junto a la columna de BUSCARX. Cambia A6 por un ID que no esté en el CRM y comprueba que devuelve 0.
06Encuentra Cada ID Que Choca
Las renovaciones necesitaban un aviso antes de enviar las cartas. CONTAR.SI ignora las mayúsculas e IGUAL no, así que comparar los dos recuentos encuentra cada ID que tiene un gemelo.
En E2 y F2, copiadas hasta la fila 9:
E2: =CONTAR.SI(CRM!$A$2:$A$9;A2)
F2: =SUMAPRODUCTO(--IGUAL(CRM!$A$2:$A$9;A2))
| ID de cuenta | CONTAR.SI | Recuento IGUAL | Choque |
|---|---|---|---|
| 0015g00000KqRtA | 2 | 1 | Sí |
| 0015g00000Lm2Xp | 2 | 1 | Sí |
| 0015g00000KqRta | 2 | 1 | Sí |
| 0015g00000Nw7Hc | 1 | 1 | No |
Cuatro de los ocho ID chocan. Brightline Studios es uno de ellos, aunque BUSCARV devolvió su propio valor: acertó solo porque iba primero.
Para una sola celda que responda por toda la hoja CRM, =SUMAPRODUCTO(--(CONTAR.SI(CRM!$A$2:$A$9;CRM!$A$2:$A$9)>1)) devuelve 4. Cualquier valor mayor que 0 significa que una búsqueda sin mayúsculas no es segura en esta tabla.
Escenario: Copia =CONTAR.SI(CRM!$A$2:$A$9;A2) en E2:E9 y =SUMAPRODUCTO(--IGUAL(CRM!$A$2:$A$9;A2)) en F2:F9. Comprueba que cuatro filas muestran 2 en E y 1 en F.
07El Resto de Excel También Ignora Mayúsculas
La búsqueda no es el único sitio donde se funden las dos cuentas. Casi todo Excel trata Lm2Xp y Lm2XP como un mismo valor:
| Herramienta | Qué hace con la pareja |
|---|---|
| Quitar duplicados | Borra la segunda fila |
| Tabla dinámica | Una fila, valores sumados |
UNICOS | Devuelve solo una |
SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO | Suma las dos en un total |
| Combinar en Power Query | Las mantiene separadas |
Power Query es la excepción: al combinar compara el texto distinguiendo mayúsculas por defecto. Una tabla dinámica hecha con la exportación del CRM habría mostrado una sola cuenta de 87.300 £.
Si el sistema de origen puede exportar una clave que no dependa de las mayúsculas, pídela. Si no, deja IGUAL en cada fórmula que toque el ID.
Nota de versión. Ninguna versión de Excel tiene un
UNICOSque distinga mayúsculas.Para listar los ID distintos respetando mayúsculas, quédate con las filas donde
=SUMAPRODUCTO(--IGUAL($A$2:A2;A2))devuelve 1, o hazlo en Power Query.
Escenario: Copia la hoja CRM, selecciona A1:C9 y ejecuta Datos > Quitar duplicados sobre la columna A. Comprueba que Excel informa de dos duplicados eliminados y que Tamsin Kitchens ha desaparecido.
Pruébalo en la cuadrícula08Cinco Trampas
- Fiarse de FALSO en BUSCARV. Significa mismas letras, no mismas mayúsculas. Lm2Xp y Lm2XP coinciden.
- Fiarse del total. Los errores opuestos se anulan: 80.700 £ de fallos movieron el total 3.900 £.
COINCIDIRsin el 0.COINCIDIR(VERDADERO;IGUAL(...))sin tipo hace una búsqueda binaria y devuelve una fila equivocada.SUMAPRODUCTOcon un ID que falta. Devuelve 0, que parece un valor de contrato real. Comprueba el recuento conIGUAL.- Limpiar los ID con
MAYUSC. Hace que todas las búsquedas coincidan convirtiendo dos cuentas en una para siempre.
09Cuatro Ejercicios
- Reprodúcelo. Monta la hoja CRM y la columna con BUSCARV, y confirma que Pellow & Daughters devuelve 48.000.
- Compara. Pon
=A2=A4e=IGUAL(A2;A4)una junto a otra, y confirma VERDADERO y FALSO. - Arréglalo. Sustituye BUSCARV por la fórmula con
BUSCARXeIGUAL, y confirma que la columna suma 234.000. - Protégelo. Añade los recuentos con
CONTAR.SIeIGUAL, y confirma cuatro choques con la comprobación de una celda conSUMAPRODUCTO.
Qué Llevarse
El CRM estaba bien y la fórmula no tenía erratas. Las búsquedas de Excel comparan el texto sin mayúsculas, así que dos cuentas que se distinguen por una sola letra compartieron un valor de contrato, y una carta salió a un tercio del precio.
Donde los ID distinguen mayúsculas, busca VERDADERO en una matriz de IGUAL, y cuenta cada ID con CONTAR.SI y con IGUAL antes de que nada salga de la empresa.