Deshabilitando #SPILL! Errores (Microsoft Excel)

La compañía de Jan se actualizó recientemente a Office 365 ProPlus. ¡Con él vino un #DESCARGO! error al usar BUSCARV, que usa MUCHO. Ahora no puede usar BUSCARV con un filtro. Jan sabe que sería fácil ordenar y / o eliminar el # N / A que vino de la VLOOKUP anterior, pero este error le está causando mucho tiempo y esfuerzo extra. Se pregunta si hay alguna forma de desactivar esta «función».

En un pasado muy cercano, Microsoft cambió la forma en que calcula las hojas de trabajo.

Este fue un cambio ENORME, y es posible que haya leído sobre él en otra parte. Aquí hay un artículo que brinda gran información sobre el cambio:

El artículo es bastante largo; querrá reservar algo de tiempo para digerir la información que contiene. Sin embargo, si crea muchas fórmulas en Excel, querrá hacerlo; el cambio en el programa significa que debe comprenderlo, eventualmente.

Esencialmente, Microsoft eliminó el concepto de funciones de matriz (aunque seguirán funcionando), en lugar de permitir que casi todas las funciones, incluida VLOOKUP, devuelvan una matriz de valores. Si la matriz de valores devueltos no cabe en el espacio disponible, ¡obtienes el nuevo #SPILL! error.

Sinceramente, ¡la respuesta no es desactivar #SPILL! errores; realmente no hay forma de hacerlo. La respuesta es comprender lo que Excel está haciendo ahora mientras calcula y luego modificar sus fórmulas en consecuencia.

Veamos un ejemplo. Supongamos que tiene una hoja de trabajo que enumera los artículos y sus precios en un conjunto simple de datos de dos columnas. Luego, a la derecha de este, ingresa algunos artículos y usa una función BUSCARV para devolver los precios asociados con cada uno de esos artículos. Cuando abre este libro en una versión anterior de Excel (2019 o anterior), obtiene excelentes resultados. (Ver figura 1)

Figura 1. Una fórmula de BUSCARV simple en Excel 2010.

Esta captura de pantalla se tomó con un sistema Excel 2010, pero funcionaría igual si la mirara en Excel 2016 o incluso Excel 2019. Tenga en cuenta en este ejemplo que la fórmula BUSCARV en la celda F2 (que se muestra en la barra de fórmulas porque la celda F2 está seleccionado) se copia en el rango F2: F8. Obtiene los resultados deseados porque la función BUSCARV devuelve un solo valor de la tabla.

Ahora, veamos qué sucede si crea el mismo libro de trabajo, utilizando las mismas fórmulas, en la versión de Excel proporcionada con Office 365. En este caso, verá errores. (Ver figura 2)

Figura 2. La misma fórmula VLOOKUP simple en la última versión de Excel.

¡Fíjate en el #DERRAME! errores. Este error se produce porque la fórmula VLOOKUP ahora puede devolver más de un valor. De hecho, cuando usa un rango de celdas en el primer parámetro de BUSCARV, ahora devolverá un valor para cada celda en ese rango. Por lo tanto, usar el rango E2: E8 para el primer parámetro significa que BUSCARV devuelve 7 valores. En otras palabras, devuelve automáticamente una matriz de valores. Si esos valores no se pueden mostrar todos, entonces obtiene el #SPILL! error. ¡Es por eso que está viendo el #SPILL! error en las celdas F2: F7; hay cosas debajo de ellos que impiden que se muestren todos los valores devueltos en esas fórmulas. No ve el error en la celda F8 porque no hay nada debajo de esa celda que detiene la pantalla.

¿Entonces cómo lo arreglas? En realidad, hay tres formas de solucionarlo.

En este ejemplo en particular, la forma más sencilla sería simplemente eliminar todo en las celdas F3: F8. Esto permite que la fórmula en la celda F2 se «derrame»

correctamente al resto de las celdas debajo de él.

El segundo enfoque sería cambiar la fórmula en la celda F2, por lo que se ve así:

Observe el uso del símbolo @ justo antes del primer parámetro. Esto le dice a Excel que desea que la fórmula BUSCARV devuelva solo un valor. La otra forma de ajustar la fórmula sería hacer que se vea así en la celda F2:

Ahora puede copiar cualquiera de las fórmulas desde la celda F2 al rango completo de F2: F8 y no tendrá ningún problema. ¿Por qué? Porque (nuevamente) VLOOKUP en estos casos devuelve solo un valor, no una matriz de valores.

El resultado es que la mejor manera de cambiar sus fórmulas es (1)

asegúrese de que no haya nada que bloquee la visualización de la matriz completa de valores que está solicitando que VLOOKUP devuelva o (2) modifique el primer parámetro para que haga referencia a una sola celda.

ExcelTips es su fuente de formación rentable en Microsoft Excel.

Este consejo (13750) se aplica a Microsoft Excel Excel en Office 365.