«Oh Dios mío» las fórmulas de excel no funcionan en mi informe.
¿Te suena familiar verdad?
Si es así, no te preocupes, eres uno de los muchos usuarios de excel que se enfrentan a este problema muy a menudo.
Sólo tiene que pensar en ello.
Ha construido un modelo de excel para la Previsión de Ventas utilizando varias lógicas con fórmulas bastante complicadas.
Y cuando intenta utilizar ese modelo, ve que algunas de sus fórmulas no funcionan o parecen rotas.
Básicamente, aquí pueden pasar dos cosas:
Una, que quieras decir algo fuerte.
La segunda, que pongas un esfuerzo extra y arregles todas las fórmulas.
Así que, para evitar toda esta molestia hemos escrito este artículo en profundidad que cubre la mayoría de las razones por las que las fórmulas de excel no funcionan.
Y cómo solucionarlas.
Las fórmulas de excel no funcionan: Por qué & Cómo solucionarlas
Según el artículo del Washington post sobre los errores de excel, «Los investigadores australianos descubrieron que aproximadamente 1 de cada 5 de estos trabajos incluían errores en sus listas de genes que se debían a que Excel convertía automáticamente los nombres de los genes en cosas como fechas del calendario o números aleatorios.»
La investigación original fue llevada a cabo por Journal Genome Biology encontró que hay una tendencia creciente año tras año en términos de un número de errores.
Puede descargar los materiales completos de la investigación desde el sitio web de SourceForge (Open Source) para una mayor lectura.
El trabajo de investigación anterior puede no estar directamente relacionado con fórmulas que no funcionan en excel, pero hay un punto aquí:
Un software muy bien construido & probado como MS Excel puede fallar en cuanto a la comprensión de nuestros datos ¿y tú & yo?
Entonces, aquí están nuestras 10 razones principales
#1 Opciones de cálculo
Cambiar la opción de cálculo a ‘manual’ es una buena práctica, pero hay que tener mucho cuidado mientras se utiliza.
Por defecto, la opción de cálculo se establecerá en automático en Excel.
Pero hay casos en los que podría haber convertido eso en ‘manual’ para acelerar el proceso.
Si activa esta opción ‘ON’ entonces sus fórmulas no se actualizarán automáticamente, esto a su vez aumenta la velocidad de procesamiento de la hoja de cálculo.
Una vez que haya terminado, debe hacer clic en Guardar o Actualizar libro de trabajo para volver a calcular. Si no lo ha hecho, entonces terminará con resultados incorrectos.
Cómo solucionar este problema:
Diríjase a Fórmulas → Cálculo → Haga clic en la opción Calcular ahora/Calcular hoja.
Alternativamente, puede utilizar la tecla de acceso directo F9 para actualizar el libro de trabajo.
Aquí tiene nuestra guía en vídeo sobre las opciones de cálculo de Excel.
Consejos:
- Acostúmbrate a pulsar Ctl+S
- No utilices esta opción para todos los libros de trabajo, a no ser que tengas un archivo enorme con muchas fórmulas
- Esta opción es específica del libro de trabajo por lo que debes activarla/desactivarla en cada libro de trabajo que quieras utilizar
#2 La fórmula de Excel no calcula solo muestra la fórmula
Mostrar fórmulas es una opción de excel para mostrar todas las fórmulas del libro de trabajo con un solo clic.
Esta opción es muy útil para encontrar todas las fórmulas de una hoja de trabajo.
Dicho esto, tenemos que tener un poco de cuidado mientras usamos la opción de mostrar fórmulas, de lo contrario, podrías terminar con algo como lo siguiente.
¿Puedes ver que la fórmula Hoy se está mostrando como una fórmula en lugar de mostrar la fecha real?
Lo mismo ocurre con las fórmulas Randbetween también.
Cómo solucionar este problema:
Es muy sencillo:
Dirígete a Fórmulas → En Auditoría de fórmulas → Haz clic en «Mostrar fórmulas» (Con el primer clic se mostrarán las fórmulas y el segundo clic se ocultarán detrás de las celdas y empezarán a funcionar)
También puedes leer: 51 mejores consejos y trucos de excel
#3 El espacio(s) extra es un dolor de cabeza extra
Has estado extrayendo datos usando el formato CSV (valores separados por comas), entonces tus datos están expuestos en su mayoría a espacios no deseados.
¿Sabes que los espacios extra causan muchos problemas al utilizar la fórmula Vlookup?
A veces es bastante difícil identificar estos espacios.
Por ejemplo, digamos que si quieres hacer Vlookup usando el nombre del empleado como base, entonces tu nombre de empleado se ve como el siguiente
¿Te has dado cuenta?
Aunque ambos nombres parecen similares a simple vista, hay un espacio en blanco en el segundo nombre.
Esto no cumpliría con los criterios de coincidencia exacta en la fórmula de Vlookup, por lo que da lugar a un error #N/A.
Cómo solucionar este problema:
El problema de los espacios extra puede solucionarse utilizando la fórmula de recorte.
En general, la fórmula de recorte encontrará más de un espacio entre las palabras y lo eliminará.
Consejos:
- Siempre es buena idea utilizar la fórmula Trim antes de Vlookup en ambas tablas (lookup & tabla de datos)
- La función Trim ignorará los espacios en blanco entre las letras (sólo funcionará con las palabras)
#4 Deshágase de los caracteres no imprimibles/ocultos
De forma similar a los espacios iniciales, los caracteres no imprimibles también son uno de los motivos por los que las fórmulas de excel no funcionan.
En caso de que tenga datos con estos caracteres, es mejor quitarlos antes de seguir trabajando.
Cómo solucionar este problema:
De forma similar a recortar, puede utilizar la fórmula CLEAN para eliminar estos caracteres.
#5 Formato de Excel – no intentes comparar Manzanas con Naranjas
Es muy importante tener un formato correcto antes de utilizar fórmulas como Vlookup, Hlookup y Match & Index.
Los números formateados como un texto es una de las razones comunes para que las fórmulas no funcionen en excel.
Aunque parezcan números, no lo son.
Por ejemplo, en el lado izquierdo, puede ver que el ID del cliente parece similar a los números.
Pero, si consulta la sección de formato anterior, podría ver que se han formateado como texto.
Esto, a su vez, provocará un error #N/A al añadir la fórmula Vlookup.
Lo peor es que es un poco difícil de identificar.
Cómo solucionar este problema:
Asegúrese de que todos sus números están formateados como números en lugar de texto. Vale la pena volver a formatear la columna de búsqueda antes de utilizar la fórmula Vlookup.
Consejos:
- En general, los números siempre estarán alineados a la derecha de la celda (pero en nuestro ejemplo anterior no lo están)
- A veces puede ver una pequeña punta verde en la esquina superior izquierda, que indica que algo está mal en esa celda
#6 Referencias circulares (Errores debidos a un círculo sin fin)
En términos simples, su rango de fórmulas está en un bucle sin fin o está en un círculo.
Según las reglas de cálculo de Excel, sus fórmulas no deben hacer referencia a las celdas de resultado como un rango. Si lo hace, aparecerán errores de referencia circular.
Personalmente, he luchado muchas veces con este tipo de errores.
Por ejemplo, en nuestra tabla de Presupuesto de Gastos de abajo, necesitamos añadir algún tapón (número de ajuste) para que coincida con los objetivos.
Así que, hemos calculado la cantidad diferencial en la fila 13, y ahora sólo tenemos que ponerlos en la fila 9.
Una forma sencilla es enlazando las celdas de la fila 13 en la fila 9.
Si lo haces así, tu fórmula de enlace no funcionará en su lugar, podrás ver el cero y posiblemente también podrías ver flechas de color azul señalando las referencias de las celdas.
En general, debería funcionar.
Pero técnicamente, será un error porque las referencias de las celdas de nuestra fórmula están en un bucle.
Cómo solucionar este problema:
Primero, tenemos que identificar las celdas de error.
Vaya a la pestaña Fórmulas → haga clic en la flecha desplegable Comprobación de errores → coloque el cursor en Referencias de celdas → ahora verá el rango o los rangos de error:
En segundo lugar, ahora que conoce el rango de referencias circulares, vaya a cada celda de error y arregle las fórmulas.
#7 Utilizar las comillas dobles de forma incorrecta – error humano
A menudo utilizamos las comillas dobles al escribir fórmulas.
Pero, es muy importante entender cuándo usar y cuándo no.
Por ejemplo, para unir dos números podemos usar una fórmula como la siguiente.
=1234&5678 = 12345678
En cambio, para unir dos cadenas de texto no se puede hacer lo mismo, hay que especificar el texto entre dobles Comillas.
=John&Smith: Forma incorrecta de usar
=»John»&»Smith» = Esta es la forma correcta.
Lo mismo ocurre con las fórmulas condicionales como IF, AND, OR. Es muy importante especificar el texto entre comillas dobles.
De lo contrario, esta es una de las razones por las que las fórmulas de excel no funcionan.
#8 BODMAS – regla básica para todos los cálculos
¿Sabes que todos los cálculos en excel se basan en la regla ‘BODMAS’?
Oh, espera… ¿qué es la regla BODMAS?
Es una regla matemática para determinar un orden de operaciones.
Bien, simplifiquemos más.
¿Puedes adivinar cuál es la respuesta de lo siguiente?
10+10*2 =?
Es 40, enhorabuena te equivocas.
¿Puedes intentar el mismo cálculo en excel?
Lo más probable es que veas 30, efectivamente esa es la respuesta correcta… pero ¿cómo?
Según la regla BODMAS, el cálculo se realizará en base a una secuencia es decir → B- Paréntesis, O- Orden, D- División, M- Multiplicación, A- Suma & S- Resta.
Así, la secuencia de la fórmula anterior es → 10*2 = 20 → 20+10 =30.
Tus fórmulas pueden salir mal si no tienes en cuenta BODMAS.
Cómo arreglar este problema:
Es muy sencillo, sólo tienes que cambiar la secuencia usando paréntesis como a continuación.
=(30+40)*2
Así, todo lo que esté entre paréntesis se calculará primero y luego pasará a la siguiente operación.
#9 Uso incorrecto de las referencias «absolutas»
Todos los que usamos Excel sabemos lo que son las referencias absolutas.
Si no es así, por favor, detente aquí mismo y aprende sobre ello. Aquí hay un tutorial rápido sobre las referencias de celdas.
Con la ayuda de la referenciación absoluta, puede fijar su rango de fórmulas para que pueda copiar de una celda a otra.
Esa es realmente una característica muy útil. Dicho esto, si no sabes cómo usarlo entonces tus fórmulas no funcionarán como pretenden.
Ciertamente, esta es una de las principales razones por las que la fórmula de Excel no funciona cuando se copia de una celda a otra.
Por ejemplo, en nuestra tabla anterior, hemos utilizado la referenciación absoluta de celdas para facilitarnos la copia de fórmulas.
Pero, hemos cometido un error.
En lugar de utilizar la congelación parcial, hemos utilizado la tecla de acceso directo F4 y hemos fijado todo el rango.
Eso sí ha dado lugar a resultados incorrectos.
Por lo tanto, la forma correcta debería haber sido utilizar la referencia absoluta sólo para las filas como esta =SUMA(D$3:D$6).
Cómo solucionar este problema:
Desgraciadamente, no hay soluciones rápidas para este tipo de errores. Hay que hacerlo manualmente analizando todas las fórmulas.
Pero, la mejor forma que he encontrado hasta ahora es cruzando las fórmulas & con los resultados en cuanto uso la referenciación absoluta & relativa.
Por favor, hazme saber tus soluciones en este punto.
Consejos:
- No utilice la tecla de acceso directo F4 para la congelación parcial
- Intente incluir un signo de dólar ($) manualmente para el rango parcial
#10 Argumentos de fórmula incorrectos
Los argumentos de fórmula incorrectos pueden tener un impacto negativo en los resultados de la fórmula.
Sólo para darle un poco de perspectiva, vamos a entender a continuación la sintaxis de vlookup especialmente la última .
El argumento de búsqueda de rango tiene dos opciones para seleccionar.
- Verdadero – Coincidencia aproximada
- Falso – Coincidencia exacta
Personalmente, siento que usar la opción «Coincidencia aproximada» es como suicidarse.
Porque la coincidencia aproximada nunca devolverá resultados precisos.
En nuestro ejemplo anterior, hemos añadido la fórmula de Vlookup en la celda F3 para averiguar la cantidad de ingresos correspondiente.
Si observa detenidamente, la cantidad de ingresos 25k para el id de cliente 6378993 es incorrecta. Debería haber sido 65k en su lugar.
Entonces, ¿qué ha salido mal?
Como he mencionado antes, he utilizado ‘Coincidencia aproximada’ bajo el argumento de búsqueda de rango.
La «coincidencia» es otra fórmula donde tiene una opción para seleccionar argumentos similares.
Cómo solucionar este problema:
Nunca utilices ‘Approximate match’ en tus fórmulas vlookup.
Ahora te toca a ti
¿Crees que tienes alguna otra razón para que las fórmulas de excel no funcionen?
O quizás tengas alguna pregunta que hacer.