En esta ocasión revelaremos cuatro funciones que tenemos disponibles en Excel que por alguna razón son casi desconocidas. Al terminar este articulo te preguntaras cómo habías pasado tanto tiempo sin saber de ellas puesto que solucionan problemas que nos encontramos en la oficina con una facilidad increíble. Las funciones que describiremos específicamente son: Días netos de trabajo, predicciones, reemplazo de SI y BD suma. Estos trucos están dirigidos no solamente a principiantes, sino que también pueden ser de mucha utilidad para personas que ya dominan Excel. Si te consideras un experto, descubre cuales ya conocías o cuales se añadirán a tu arsenal de fórmulas.
Días Netos de Trabajo.
Esta fórmula nos permite calcular cuantos días laborales hubo entre dos fechas. Por lo general si tenemos dos fechas en el calendario y queremos saber cuántos días hay entre ellos solo tenemos que restar, pero si queremos saber cuántos días laborales hubo no podemos hacer esto. Deberíamos tener en cuenta los fines de semana que normalmente no son laborables, pero con esta función podemos hacer este cálculo sin problemas.
Digamos entonces que tenemos una tabla con la información de nuestros empleados, en ella tenemos la fecha en la que empezaron a trabajar y la fecha en la que terminaron. Además, sabemos que trabajan de lunes a viernes y no tienen días festivos. En un caso con estas características podemos obtener el resultado inmediatamente con nuestra formula.
Entonces la función que necesitamos para hacer esta operación es =días.lab(), esta función solo nos pide la fecha inicial en la que empezaron a trabajar y la fecha final, automáticamente Excel hará las cuentas tomando en consideración que los días libres son los fines de semana y obtendremos nuestro resultado sin nada más que esta simple función.
La funcionalidad de esta fórmula no termina aquí. Existen empresas que no tienen un horario laboral de lunes a viernes, sino que además trabajan los sábados. No hay problema, con la función =días.lab.intl() podemos hacer la excepción del sábado y hacer nuestro cálculo sin problemas. Esta versión de la formula está diseñada para que podamos aplicarla tomando en cuenta cualquier variable en los días laborables que nos podamos encontrar.
La diferencia principal entre esta variable de la formula original es que =dias.lab.intl() nos pide un tercer argumento en el cual definiremos cuales son los días libres que tenemos. Como en este caso hablamos de una empresa que trabaja de lunes a sábado indicamos que el día libre de los empleados es solo el domingo.
Esta herramienta es particularmente útil en contextos en los que los empleados no siguen un horario de trabajo normal, ya sea por cuestión de turnos o por tener horarios diferentes, como los restaurantes que suelen tomar sus días libres entre semana. Dentro de esta función podemos encontrar cada variable que necesitemos independientemente de nuestro caso particular.
Una cosa adicional que debemos tomar en cuenta es que además de que existen los fines de semana, también tenemos días festivos que no son laborables. La función =dias.lab.intl() también nos ayuda a hacer estas excepciones particulares según sea el caso de nuestro país.
Para esto solo tenemos que crear una pequeña tabla en la que escribiremos la totalidad de días festivos que tenemos en nuestro calendario. El punto de esto es tener a la mano las fechas exactas que le indicaremos a la función que tome en cuenta, por lo que si queremos tomar en consideración las fechas de más de un año debemos escribir la fecha particular de cada efeméride.
Una vez sabemos qué fechas con exactitud tendremos libres además de los días de descanso solo tenemos que escribir nuestra formula. En este ejemplo la empresa trabaja cinco días a la semana por lo que asumimos que los días libres son sábado y domingo. Para incluir los días festivos la función nos deja añadir un cuarto argumento en el cual seleccionaremos nuestra tabla, pero sombrearemos solo las celdas que contienen las fechas especificadas.
Esta herramienta soluciona muchos problemas que podrían presentarse en caso de necesitar resolver estas incógnitas. Si por alguna razón queremos añadir más días no laborados además de los días festivos de no haber laborado por razones externas, entonces solo tenemos que indicar estas fechas en nuestra tabla y Excel hará todo el trabajo por nosotros.
Predicción.
Excel cuenta con una herramienta de predicción con la que podemos analizar un conjunto de datos y realizar una predicción basándonos en un algoritmo. Sinceramente estas predicciones no suelen ser muy acertadas ya que el algoritmo de predicción de Excel sigue siendo bastante básico, pero esta herramienta puede ser de ayuda cuando necesitamos tener una noción aproximada de las cosas que pueden suceder.
Para este ejemplo utilizaremos los precios con los que cotizó la bolsa de valores estadounidense en el 2018. Con los datos que tenemos recolectados hasta cierta fecha probaremos la función de predicción de Excel y después la compararemos con datos reales para ver el índice de efectividad de esta herramienta.
Para utilizar esta función solo necesitamos dos elementos, una fecha y un valor que serán procesados por el algoritmo. En este caso nuestra tabla tiene ambos elementos por lo que solo tenemos que seleccionar cualquier celda de nuestra tabla y dirigirnos al menú superior y buscar en la pestaña de “Datos” el botón de previsión.
Una vez hagamos esto aparecerá una nueva ventana para crear una hoja de cálculo de pronóstico.
Básicamente los datos que encontramos en esta ventana nos indican que, en base a los datos introducidos, representados en color azul. Excel creará una predicción simbolizada en color naranja. Podemos ver que Excel en realidad no toma muchos riesgos puesto que además nos presenta un límite superior y un límite inferior. Dentro de esta ventana también podemos especificar hasta que día queremos que se realice el pronóstico, en este punto debemos recordar que Excel interpreta los días enumerándolos desde el 1/1/1990, es por esto por lo que vemos grandes cifras en vez de una fecha con formato normal.
Esta herramienta además nos proporciona funciones de estacionalidad, aunque debemos aclarar una vez más que Excel no suplanta bajo ningún concepto un buen trabajo estadístico, mucho menos un análisis de lo que realmente puede pasar en la bolsa.
Al crear nuestra hoja de predicción se abrirá una nueva pestaña en nuestro libro de trabajo con una copia de los datos que introducimos previamente además de las predicciones realizadas por Excel y un gráfico con toda nuestra información. Al hacer esto nuestra columna de fechas probablemente aparezca sin formato, para transformar las fechas a un formato más amigable solo tenemos que pulsar Ctrl + 1 y seleccionar el formato de fecha corta.
Entonces, Excel se basó en datos recolectados desde el 29/03/2018 hasta el 14/11/2018 para predecir las posibles cotizaciones desde el 15/11/2018 hasta el 11/01/2019. Para corroborar qué tan efectivos son estos pronósticos compararemos el precio real con el que cerró la bolsa el 28/11/2019 y la predicción.
Como podemos observar Excel pronosticó que la bolsa cerraría en 2781.02, con un límite inferior de 2666.25 y uno superior de 2895.81. Si lo comparamos con el cierre real, 2854 podemos ver que, a pesar de estar dentro del rango predicho por el software, esta no es para nada una herramienta fiable a la hora de adelantarse a sucesos con tantas variables como lo es la bolsa.
Esta herramienta puede venir de ayuda en situaciones más simples como el cálculo de presupuestos con poca tendencia a aumentar o disminuir. Otro punto para tomar en consideración es que las versiones más antiguas de Excel pueden no contar con esta función dentro de ellas.
Reemplazar Función Si.
La función =si() es una de las herramientas más prácticas que nos presenta Excel, desafortunadamente hay ocasiones en las que caemos en uso excesivo de esta fórmula cuando tenemos una alternativa mucho más sencilla.
Para dar un poco de perspectiva tomaremos el ejemplo de una hoja con datos del coeficiente intelectual de un grupo de personas. Además de esto tenemos una tabla que categoriza los coeficientes según su inteligencia. Entonces, para no clasificar cada uno manualmente podríamos realizar una concatenación de funciones =si(), pero en mi tabla tengo siete categorías distintas. Esto hace que no solo termináramos con una formula larga y enredada, sino que también nos arriesgamos a cometer un error humano más fácilmente.
Como podemos observar en el ejemplo, no hemos ni siquiera terminado con la formula y ya tiene un tamaño considerable. Nos podemos ahorrar esto utilizando la función =buscarv() de forma aproximada. Particularmente, esta es una de esas herramientas que nos puede solucionar muchos problemas similares pero pocas personas saben cómo utilizarla.
En caso de no saber cómo utilizar la función =buscarv() recomendamos los múltiples materiales que tenemos al respecto, además de este uso especifico es una de esas funciones que todos deberíamos saber usar. Entonces, abrimos nuestra función y en el argumento de búsqueda seleccionaremos el IQ, la matriz en la que buscaremos será nuestra tabla, el indicador de columnas será el número 2 y el rango de búsqueda es una búsqueda aproximada. Con esta simple formula ya podemos resolver este problema sin mayor complicación.
La clave para que el =buscarv() aproximado funcione es organizar los valores de nuestra tabla de menor a mayor, de lo contrario no funcionará. También debemos tomar en cuenta que no podemos procesar un valor menor a nuestro límite inferior, es decir, si en este caso nuestro limite es 50, si procesáramos a alguien con un IQ de 49 probablemente nuestra formula no funcionaría. Después de tener los requisitos mínimos el =buscarv() lo que hace es comparar nuestro valor buscado con la matriz, y en caso de no coincidir con los datos de la esta, procederá a buscar el valor más aproximado. De esta forma nos libramos de utilizar la función =si() en tareas con muchos intervalos.
BD Suma.
BD Suma es por alguna razón una de las herramientas más infravaloradas que nos proporciona Excel. Funciona de forma similar a la función =sumar.si(), pero BD Suma es una fórmula con mucha más utilidad a la hora de sumar valores específicos dependiendo de criterios. Las siglas BD significan Base de Datos por lo que como su nombre lo indica con ella podemos sacar el máximo provecho al trabajar con bases de datos con una gran cantidad de valores.
Esta función está compuesta por tres argumentos: La base de datos, el nombre de campo y los criterios. Para este ejemplo utilizaremos una tabla con datos de las ventas de distintas oficinas y departamentos de una empresa.
Un pequeño detalle respecto a esta función es que antes de poder escribirla debemos preparar ciertas cosas dentro de nuestra hoja para que funcione. Elementalmente, usaremos nuestra tabla como base de datos, pero además debemos crear un par de tablas más para definir nuestro nombre de campo y criterios.
Una vez ubicamos en nuestra hoja los criterios que vamos a utilizar podemos escribir nuestra formula.
Como ya preparamos nuestros argumentos solo tenemos que referenciar los elementos correspondientes de cada uno y tendremos nuestra función lista. La razón por la que BD Suma es una función tan buena es que al pedirnos como único requisito el escribir nuestros criterios y nombre de campo de forma idéntica a como está en la base de datos podemos modificar nuestros criterios a conveniencia sin tener que volver a tocar la formula. Esta función, a diferencia del =sumar.si() nos permite filtrar los valores deseados de una manera mucho más flexible y práctica.
Por ejemplo, si quisiéramos calcular el total de ventas de todas las oficinas excepto las de México, que además tengan más de 75 facturas solo tendríamos que indicarlo en nuestros criterios. En Excel al escribir estos símbolos <> junto a cierto valor hacemos que Excel no le tome en cuenta para la formula.
Con solo un par de clics podemos encontrar los valores que queramos rápidamente, esto convierte a BD Suma en una opción muchísimo más provechosa que =sumar.si(), además de esto Excel cuenta con otro conjunto de funciones que empiezan con BD y funcionan de una forma similar. El punto de Excel es hacernos la vida más fácil, especialmente cuando trabajamos con grandes bases de datos. Es por eso por lo que estamos seguros de que estos trucos no pasaran desapercibidos entre los entusiastas de Excel.
Las cuatro herramientas presentadas en este articulo más allá de ser realmente secretas son simplemente desconocidas. A pesar de los esfuerzos de Microsoft por popularizar la totalidad de sus herramientas por alguna razón funciones con tanta utilidad como estas no llegan a ser usadas por la mayoría de los usuarios del software por simple desconocimiento. ¿Conocías todas estas funciones? ¿Cuál es la más útil de todas?
Deja una respuesta