01 / 14Once Líneas Valoradas a Cero

CompatibilidadBUSCARXGestión de Errores

Error #¿NOMBRE? en Excel: BUSCARX en versiones antiguas

El Pedido de Subcontrata Llegó 38.415,60 € Corto y Todas las Tarifas de Nuestra Copia Eran Correctas, Porque su Excel No Había Oído Hablar de BUSCARX

24 oct 202615 min de lectura

UsaBUSCARXINDICE COINCIDIRSI.ERRORSI.ND

Thorneley Shopfitting valoró 48 líneas de carpintería en 412.860,00 €. El pedido que emitió Cadogan Build tres semanas después decía 374.444,40 €. Las dos cifras salen del mismo libro y nadie había cambiado una tarifa.

La diferencia son 38.415,60 €, y toda ella son once líneas que volvieron a cero. Las once buscaban su tarifa con una función que el Excel de la contrata principal no tiene.

La hoja se escribió en Microsoft 365 y se recalculó en Excel 2016. Una versión que no tiene una función la guarda con el prefijo _xlfn. y devuelve #¿NOMBRE?, y el SI.ERROR de cada búsqueda convirtió cada uno de esos errores en una tarifa de nada.

La ofertaImporte
Valorado en nuestra copia412.860,00 €
Pedido en la suya374.444,40 €
Diferencia38.415,60 €
Líneas valoradas a cero11 de 48
Recuperable como variación11.380,00 €

Alcance. BUSCARX, COINCIDIRX, LET, FILTRAR, DIVIDIRTEXTO, APILARV, SI.ERROR, SI.ND y FORMULATEXTO, en Excel 2016, 2019, 2021, 2024 y Microsoft 365, en Windows y Mac.

Los números de versión de aquí son las licencias perpetuas, las que se compran una vez y se conservan. Microsoft 365 avanza por canales, así que dos compañeros con 365 pueden discrepar sobre si una función existe.

En todo el artículo, la columna A guarda el código de línea, la columna B la cantidad y la columna C la tarifa; Codigos y Tarifas son las dos columnas de la tabla de tarifas.

01Once Líneas Valoradas a Cero

Thorneley valora el trabajo con una tabla de tarifas: una hoja de códigos e importes que mantiene el responsable comercial y que leen todas las hojas que preparan los presupuestadores. En esta oferta salieron cuarenta y ocho líneas.

El presupuestador había reescrito la celda de tarifa aquella primavera. =SI.ERROR(BUSCARX(A2;Codigos;Tarifas);0) sustituyó a un BUSCARV que exigía tener la columna de códigos a la izquierda, y en su máquina era mejor fórmula en todo lo que importaba.

Treinta y siete líneas seguían con el BUSCARV antiguo, con una tarifa escrita a mano o con una pareja INDICE y COINCIDIR heredada de una hoja anterior. Once se habían convertido, y la conversión es lo único que separa las líneas que valoraron de las que no.

El aparejador de Cadogan abrió el archivo en Excel 2016, cambió cuatro cantidades tras una reducción de alcance y leyó el nuevo total al pie de la hoja. Los 374.444,40 € pasaron al resumen de la oferta y al pedido de subcontrata.

En su pantalla no había ni un error. Once celdas de tarifa ponían 0,00, en una hoja donde una tarifa nula es algo que un subcontratista escribe a veces a propósito.

Once Líneas que Valoraron a Cero en el Excel de Otro

La hoja de tarifas valorada de Thorneley Shopfitting para la obra de Cadogan Build, en A1:F16. La columna C es el total de la línea tal como volvió en la copia de la contrata principal, recalculada en Excel 2016; la columna D es la misma línea en el Microsoft 365 del presupuestador, donde siempre ha estado bien. La columna B nombra la función de la celda de tarifa, y cada fila en la que las dos copias discrepan es una fila cuya tarifa se buscó con una función que Excel 2016 nunca ha tenido. Las dos filas del final son el total de la oferta y la única línea aún recuperable como variación.

ABCDEF
1
Line
Rate cell uses
On their copy
On ours
Short by
What happened
2
L-02 Softwood studwork
VLOOKUP
7709.6
7709.6
0
419 metres at £18.40. The oldest formula on the schedule and the only kind the contractor's Excel could read: a VLOOKUP down a sorted rate card, written in 2019 and never touched since.
3
L-04 Fire doors FD30
XLOOKUP
0
11380
11380
40 doors at £284.50, the largest line on the tender and the largest single loss. It was converted to XLOOKUP because the rate column had been moved to the left of the code column, which VLOOKUP cannot do.
4
L-05 Ironmongery sets
XLOOKUP
0
4837.5
4837.5
50 sets at £96.75. Priced in the same afternoon as the fire doors and converted in the same pass, so the two lines that carry a third of the loss failed for exactly the same reason.
5
L-07 Reception desk
INDEX/MATCH
7420
7420
0
One bespoke desk at £7,420.00, lifted from a schedule written three years earlier where the lookup was an INDEX and MATCH pair. The oldest formula on the sheet is the one that still worked.
6
L-09 Wall panelling
LET + XLOOKUP
0
9345
9345
150 square metres at £62.30. The LET held the rate once and used it twice, for the panel and for the beading, so the line needed two functions the contractor's build does not have.
7
L-13 Vanity units
XLOOKUP
0
5016
5016
12 units at £418.00. A plain XLOOKUP with no fallback argument, wrapped in IFERROR by the template the estimator copied the row from.
8
L-17 Window boards
XMATCH
0
2012.4
2012.4
86 metres at £23.40. XMATCH found the row and INDEX returned the rate; the INDEX would have been fine on its own and the XMATCH is what broke the pair.
9
L-21 Skirting, MDF
XLOOKUP
0
1503.5
1503.5
310 metres at £4.85. A small rate over a long run, which is the shape that hides best: nobody reading the schedule expects skirting to be one of the expensive lines.
10
L-26 Door frames, hardwood
LET + XLOOKUP
0
1713.6
1713.6
28 frames at £61.20. Converted in the same session as the panelling, and the LET was there to keep the formula readable rather than to do anything the sheet needed.
11
L-30 Shelving, birch ply
FILTER
0
831.6
831.6
44 metres at £18.90. A FILTER returning the one rate that matched the code, which is a dynamic array: on the contractor's build it has no function and nowhere to spill even if it had.
12
L-34 Handrail, oak
XLOOKUP
0
1002.25
1002.25
19 metres at £52.75. One of four lines added the evening before the tender went out, all of them written in the estimator's current style rather than the schedule's.
13
L-39 Access panels
XMATCH
0
350.4
350.4
24 panels at £14.60. The smallest line that failed, and the one that proves the fault is not about money: the formula was converted, so the rate went to zero.
14
L-45 Making good
XLOOKUP
0
423.35
423.35
A lump sum of £423.35 for making good after the second fix. A line that reads like a rounding allowance and was never going to be queried at nil.
15
The 48 lines
11 came back zero
374444.4
412860
38415.6
What the subcontract order said against what the schedule priced. Thirty-seven lines were read correctly by an Excel five years older than the formulas; the eleven that were converted this spring all priced at nothing.
16
Recoverable
L-04 re-measured
0
11380
11380
The fire doors were re-measured under a later variation, so that one line can be priced again at the rate on the card. The other £27,035.60 is inside a signed order.

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

02Qué Hace un Excel Antiguo con una Función que No Tiene

Un libro guarda el nombre de cada función que usa. Cuando Excel guarda una función que las versiones anteriores nunca tuvieron, escribe ese nombre con el prefijo _xlfn., para que una versión antigua pueda abrir el archivo sin inventarle un significado.

Abre ese archivo en la versión antigua y la celda muestra =_xlfn.XLOOKUP(A2;Codigos;Tarifas). Ahí ese nombre no es una función, así que la celda devuelve #¿NOMBRE?: el mismo error que da una falta de ortografía.

Nada está corrupto. Tu copia sigue calculando, la suya conserva la fórmula entera, y al guardar desde la versión antigua el prefijo se mantiene, así que el archivo vuelve a valorar bien en cuanto regresa a una máquina con 365.

En tu Microsoft 365En su Excel 2016
=BUSCARX(A2;Codigos;Tarifas)=_xlfn.XLOOKUP(A2;Codigos;Tarifas)
284,50#¿NOMBRE?
=SI.ERROR(BUSCARX(A2;Codigos;Tarifas);0)0,00

Escenario: Pide a la próxima persona a la que envíes una hoja valorada una captura de una celda de tarifa con la barra de fórmulas visible. Un _xlfn. en esa barra significa que su Excel no está valorando tu archivo.

Pruébalo en la cuadrícula

03Por Qué el Archivo Parecía Correcto al Principio

Excel guarda el último valor calculado de cada celda con fórmula junto a la fórmula misma. Abre el libro en una versión que no puede evaluarla y lo que ves son esos valores guardados.

Por eso un vistazo rápido a un archivo recibido no demuestra absolutamente nada. Los valores no duran: un libro guardado por última vez con otra versión de Excel suele recalcularse por completo al abrirse.

Y lo que edites después recalcula todas las celdas que dependen de ello. El aparejador de Cadogan hizo las dos cosas: abrió un archivo de 365 en Excel 2016 y cambió cuatro cantidades, que es lo más normal que se le hace a una hoja valorada.

Un archivo recibido que sigue pareciendo correcto no está probado. Lo ha abierto alguien que todavía no ha escrito en él.

Escenario: Abre una copia de tu propia hoja y pulsa Ctrl+Alt+F9 para forzar un recálculo completo. Todo lo que se mueva es una celda cuyo valor estaba guardado en lugar de calculado.

Pruébalo en la cuadrícula

04SI.ERROR Convirtió #¿NOMBRE? en una Tarifa de Cero

SI.ERROR captura todos los errores de Excel: #N/D, #¡VALOR!, #¡REF!, #¡DIV/0! y #¿NOMBRE? por igual. Se escribió alrededor de la búsqueda para resolver un código que no está en la tabla, y resolvió igual una función que falta.

SI.ND captura #N/D y nada más. Cambia uno por el otro y un código desconocido sigue devolviendo tu cero, mientras que una función que la versión no tiene sale como el error que realmente es.

=SI.ND(BUSCARX(A2;Codigos;Tarifas);0)

Ese es todo el arreglo de la mitad silenciosa de este fallo. Las once celdas habrían puesto #¿NOMBRE? en la pantalla del aparejador, y una columna de #¿NOMBRE? en una hoja de tarifas se resuelve con una llamada en menos de una hora.

EnvoltorioCódigo que no estáFunción que no tiene
=BUSCARX(A2;Codigos;Tarifas)#N/D#¿NOMBRE?
=SI.ND(BUSCARX(A2;Codigos;Tarifas);0)0,00#¿NOMBRE?
=SI.ERROR(BUSCARX(A2;Codigos;Tarifas);0)0,000,00

TIPO.DE.ERROR nombra lo que capturó un envoltorio. =TIPO.DE.ERROR(C2) devuelve 7 para #N/D y 5 para #¿NOMBRE?, y es la celda que conviene poner al lado de un valor alternativo del que no estás seguro.

Escenario: Busca cada SI.ERROR de tu hoja que envuelva una búsqueda y cambia a SI.ND los que solo esperan fallar por un código que falta. Escribe luego un código malo y comprueba que el cero sigue llegando.

Pruébalo en la cuadrícula

05Qué Función Pide Qué Versión

Las once líneas que Thorneley perdió usaban cuatro funciones, y solo una era BUSCARX. COINCIDIRX valoró dos, LET valoró otras dos y un FILTRAR que devolvía una sola tarifa valoró la última.

Lo que escribisteEl Excel más antiguo que lo tiene
SUMA, BUSCARV, BUSCARH, INDICE, COINCIDIR, REDONDEARExcel 97
SUMAR.SI.CONJUNTO, CONTAR.SI.CONJUNTO, SI.ERROR, CONTARAExcel 2007
SI.ND, FORMULATEXTOExcel 2013
SI.CONJUNTO, CAMBIAR, UNIRCADENAS, CONCAT, MAX.SI.CONJUNTOExcel 2019
BUSCARX, COINCIDIRX, LET, FILTRAR, ORDENAR, UNICOSExcel 2021
LAMBDA, DIVIDIRTEXTO, TEXTOANTES, APILARV, TOMARExcel 2024
AGRUPARPOR, DINAMIZARPORSolo Microsoft 365

Una licencia perpetua no avanza. Excel 2016 nunca va a tener un BUSCARX, y un cliente con un ciclo de renovación de cinco años va tres versiones por detrás de lo que tú instalaste esta mañana.

Microsoft 365 tampoco es una sola versión. Una función del canal actual puede tardar meses en llegar al canal semestral que despliegan la mayoría de los departamentos de informática.

Escenario: Escribe en una nota al principio de la hoja las funciones que usan tus celdas de tarifa y compruébalas una a una contra la tabla. La fila más antigua en la que caiga algo de tu hoja es el Excel más antiguo que puede valorarla.

Pruébalo en la cuadrícula

06La @ y la # También Viajan Mal

Dos símbolos que 365 escribe en las fórmulas son funciones por debajo, y reciben el mismo trato. La @ de la intersección implícita se guarda como _xlfn.SINGLE, y la referencia de desbordamiento E2# se guarda como _xlfn.ANCHORARRAY(E2).

Una fórmula de matriz dinámica tiene un segundo problema además del nombre que falta. Las versiones antiguas no desbordan, así que un FILTRAR o un UNICOS que llena diez celdas en tu máquina no tiene dónde poner nueve de ellas en la suya.

Lo que escribe 365Lo que lee Excel 2016
@A2:A9_xlfn.SINGLE(A2:A9)
E2#_xlfn.ANCHORARRAY(E2)
=FILTRAR(Tarifas;Codigos=A2)#¿NOMBRE? en una celda

Todo lo que desborda es un rediseño, no una sustitución. Una fórmula por fila, arrastrada hacia abajo por la columna, es la forma que funciona en las dos máquinas.

Escenario: Busca una # o una @ en las fórmulas de una hoja que estés a punto de enviar. Cada una es una celda que hay que reescribir como una fórmula por fila antes de que salga el archivo.

Pruébalo en la cuadrícula

07Una Columna Audita la Hoja Entera

FORMULATEXTO devuelve una fórmula como texto, y eso convierte una fórmula en algo que se puede buscar. =ESNUMERO(HALLAR("XLOOKUP";FORMULATEXTO(C2))) es VERDADERO en cada celda cuya tarifa venga de una búsqueda que Excel 2016 no tiene.

Arrástralo por la columna de tarifas y las once líneas se señalan solas. La cuenta va en una celda sobre la columna, y es el número que hay que leer antes de enviar nada:

=ESNUMERO(HALLAR("XLOOKUP";FORMULATEXTO(C2)))
=SUMAPRODUCTO(--ESNUMERO(HALLAR("XLOOKUP";FORMULATEXTO(C2:C49))))
=CONTAR.SI(C2:C49;0)

El nombre que se busca es el inglés, porque FORMULATEXTO devuelve el nombre interno de la función. Una búsqueda por función es la versión honesta del control: una columna con los nombres que has adoptado este año cuesta cinco minutos y los pilla todos, no solo el famoso.

La tercera línea es el control desde el otro lado. =CONTAR.SI(C2:C49;0) cuenta las tarifas que valoraron a nada, y en una hoja donde ninguna línea es gratis de verdad, cualquier respuesta por encima de cero es este artículo.

Escenario: Pon =ESNUMERO(HALLAR("XLOOKUP";FORMULATEXTO(C2))) al lado de tu columna de tarifas y arrástralo. Cada VERDADERO es una línea que valorará lo que diga SI.ERROR en un Excel anterior a 2021.

Pruébalo en la cuadrícula

08El Comprobador Mira el Formato, No su Excel

Archivo ▸ Información ▸ Comprobar si hay problemas ▸ Comprobar compatibilidad es la función que la gente espera que detecte esto, y no lo hace. Compara tu libro con formatos de archivo antiguos, con lo que un .xls no puede llevar, y no con la versión instalada en la mesa de otro.

Guarda como .xlsx, envíalo a alguien con 2016 y el comprobador no tiene nada que decir, porque el formato está bien. El problema es la función, y un formato de archivo no registra qué Excel está a punto de abrirlo.

Así que mejor pregunta. "¿Qué Excel tienes?" es una pregunta que cualquier aparejador, auditor o responsable financiero sabe contestar, y la respuesta decide qué funciones te puedes permitir en el archivo que le mandas.

Escribe la respuesta en la hoja. Una nota que diga "valorado para Excel 2016" al principio de la hoja es todo el control de versiones que va a tener un libro que viaja.

Escenario: Envía una celda antes de enviar la hoja: un libro con =BUSCARX("a";{"a"};{1}) en A1 y nada más. Si la respuesta dice #¿NOMBRE?, ya sabes a qué estás valorando cuando todavía no hay nada en juego.

Pruébalo en la cuadrícula

09Equivalentes que Valoran Bien en Cualquier Sitio

Cada línea de la hoja de Thorneley tiene una versión que funciona en Excel 2016, y la mayoría son la fórmula que el presupuestador sustituyó.

En lugar deEscribe
=BUSCARX(A2;Codigos;Tarifas)=INDICE(Tarifas;COINCIDIR(A2;Codigos;0))
=LET(t;BUSCARX(A2;Codigos;Tarifas);t*B2)Una columna de tarifa y luego =C2*B2
=FILTRAR(Tarifas;Codigos=A2)=INDICE(Tarifas;COINCIDIR(A2;Codigos;0))
=TEXTOANTES(A2;" ")=IZQUIERDA(A2;ENCONTRAR(" ";A2)-1)
=APILARV(Ene:Dic!A2:F99)Una anexión en Power Query

=INDICE(Tarifas;COINCIDIR(A2;Codigos;0)) es la que hay que aprender. Mira a la izquierda igual de bien que a la derecha, sobrevive a una columna insertada y está en todos los Excel desde los noventa.

Power Query es el ganador silencioso frente a las funciones que apilan. Obtener y transformar viene integrado en Excel para Windows desde 2016, así que una consulta de anexión se actualiza en una versión que no sabe escribir APILARV.

Escenario: Reescribe una celda de tarifa con =INDICE(Tarifas;COINCIDIR(A2;Codigos;0)) y compara las dos por toda la columna. Cualquier fila en la que discrepen depende de un modo de coincidencia que aún no has sustituido.

Pruébalo en la cuadrícula

10Envía Cifras Cuando lo que Importa son las Cifras

Una oferta valorada es un conjunto de números, no un modelo. Si quien la recibe va a leerla, revisarla y pedir con ella, mándale números: copia la hoja, Pegado especial ▸ Valores sobre sí misma, y envía esa copia.

Guarda la versión con las fórmulas. Dos archivos, uno que calcula y otro que se envió, son además la única forma de contestar "¿qué valoramos exactamente?" seis meses después, con el pedido hecho y la tabla de tarifas ya cambiada dos veces.

Cuando de verdad tengan que trabajar en la hoja, manda la versión escrita con las funciones que su Excel tiene. Ese es un coste de trabajar con una versión de 2016, y sale mucho más barato que 38.415,60 €.

Un PDF no es un sustituto, es un registro. Manda uno junto al libro cuando la cifra importe, porque un PDF es la única copia de una hoja que no puede recalcularse y convertirse en otra cosa.

Lo que se firme debería ser el PDF. Lo que se trabaje debería ser el libro cuyas funciones tiene de verdad el Excel de quien lo recibe.

Escenario: Coge tu última hoja valorada, copia la hoja y pégala sobre sí misma como valores. Comprueba el total contra la copia viva y mira después qué dejó el pegado especial en la columna de tarifas.

Pruébalo en la cuadrícula

11Mac, la Web y los Viajes de Ida y Vuelta

Excel para Mac con Microsoft 365 tiene las mismas funciones que Windows en el mismo canal, así que el Mac no es el riesgo. Las versiones perpetuas de Mac se comportan como la versión de Windows del mismo número.

Excel en la web lleva la lista de 365 y recalcula al abrir, lo que lo convierte en un buen banco de pruebas. Si un archivo valora bien en el navegador y mal en el escritorio de un compañero, la diferencia es la versión del compañero.

Un viaje de ida y vuelta por Google Sheets es una segunda traducción. Sheets tiene su propia lista de funciones y sus propios nombres para parte de ella, así que un libro que sale como .xlsx, se edita allí y vuelve no es el libro que enviaste.

Escenario: Sube una copia de tu hoja a Excel en la web y lee el total. Una cifra que coincide con tu escritorio pero no con el de quien la recibe te dice por qué máquina hay que preguntar.

Pruébalo en la cuadrícula

12Ocho Trampas

  1. SI.ERROR alrededor de una búsqueda. No distingue un código que falta de una función que falta y contesta a las dos con tu alternativa.
  2. Fiarse de los valores de un archivo recibido. Son el último cálculo de quien lo envió y solo duran hasta que algo recalcula.
  3. Esperar que el fallo sea ruidoso. Una tarifa de 0,00 en una hoja de tarifas es un número, no un error, y además cuadra.
  4. Leer _xlfn. como corrupción. El archivo está intacto; el prefijo es cómo Excel conserva tu fórmula en una versión que no puede ejecutarla.
  5. Confiar en el comprobador de compatibilidad. Lee el formato del archivo, no la versión de Excel que va a abrirlo.
  6. Modernizar fórmulas en una hoja que viaja. La mejora que aclara tu copia es lo que hace que la suya esté mal.
  7. Enviar una fórmula que desborda. Las versiones antiguas no desbordan, así que nueve de cada diez resultados no tienen dónde caer.
  8. Dar por hecho que Microsoft 365 es una sola versión. Los canales se separan meses, y el semestral es el que despliega la mayoría de las flotas controladas.

13Cinco Ejercicios

  1. Reprodúcelo. Valora diez líneas con =SI.ERROR(BUSCARX(A2;Codigos;Tarifas);0), escribe mal BUSCARX como BUSCARXX y lee el total. Esos ceros son lo que calcula Excel 2016.
  2. Hazlo ruidoso. Cambia el envoltorio por SI.ND y mira cómo esas mismas diez líneas devuelven #¿NOMBRE? en lugar de una tarifa.
  3. Audítalo. Arrastra =ESNUMERO(HALLAR("XLOOKUP";FORMULATEXTO(C2))) por la columna de tarifas y cuenta los VERDADERO con SUMAPRODUCTO.
  4. Tradúcelo. Reescribe cada celda señalada como =INDICE(Tarifas;COINCIDIR(A2;Codigos;0)) y comprueba que todas las líneas valoran igual.
  5. Envía cifras. Pega la hoja valorada sobre sí misma como valores y confirma que el total sobrevive a un recálculo completo con Ctrl+Alt+F9.

Lo que Hay que Llevarse

La copia de Thorneley de esta hoja nunca ha valorado mal una línea. Hoy sigue sumando 412.860,00 € en la máquina en la que se escribió y contra la tabla de tarifas con la que se escribió.

La cifra que importaba era la que produjo el Excel de Cadogan. Esa versión no había oído hablar de tres de las cuatro funciones que el presupuestador adoptó aquella primavera, y dijo 374.444,40 € sin poner un solo error en la pantalla.

Un archivo que sale de la oficina lo ejecuta el Excel de otra persona. Las funciones que lleva son una promesa sobre su máquina, no sobre la tuya, y _xlfn. es Excel anotando que la promesa no se cumplió.

SI.ND en lugar de SI.ERROR no habría costado nada y habría convertido once ceros silenciosos en once celdas con #¿NOMBRE? en la pantalla del aparejador. De los 38.415,60 €, hay 27.035,60 € dentro de un pedido firmado que no van a volver.

Comparte este artículo:
Volver al Blog
Reto diario · Día 107

Suma los kilómetros de un periodo de liquidación con SUMAR.SI.CONJUNTO

Un ejercicio nuevo cada día, resuelto en una cuadrícula real.

Resolver el reto de hoy