Elija las matemáticas con la función ELEGIR de Excel

¿Alguna vez ha querido permitir que el usuario del libro elija las matemáticas que debe usar una fórmula? Esta publicación explora la función ELEGIR, que, entre otras cosas, nos permite permitir que el usuario elija las matemáticas.

ELEGIR

Antes de pasar directamente a la sintaxis de la función, veamos un poco de contexto. Digamos que tenemos una hoja de trabajo de presupuesto. Contiene valores reales del año anterior y queremos que el usuario (nuestro director de departamento) prepare el presupuesto para el próximo año. Esto se puede visualizar con la captura de pantalla a continuación.

Queremos que este proceso sea lo más eficiente posible y, por lo tanto, queremos que sea rápido y fácil para nuestro usuario. En lugar de exigir que nuestro gerente escriba los montos del presupuesto para cada cuenta, decidimos permitirle seleccionar un método de presupuesto para cada cuenta y dejar que Excel realice los cálculos relevantes. Por ejemplo, queremos proporcionar un método presupuestario que tome los montos reales anteriores y los distribuya uniformemente durante el año presupuestario. Este método es perfecto para cuentas pequeñas. También queremos un método que iguale exactamente los importes del año anterior, porque algunas de las cuentas son de naturaleza cíclica, por ejemplo, la feria comercial anual. Por último, queremos proporcionar un método que reduzca los montos del año anterior en un 10%.

Queremos que el administrador de presupuesto revise cada cuenta y asigne el método de presupuesto más apropiado. Según el método de presupuesto seleccionado, queremos que Excel calcule los cálculos.

Este tipo de configuración se simplifica con la función ELEGIR. La sintaxis de la función es la siguiente:

= ELEGIR (núm_índice, valor1, [valor2],…)

Dónde:

• index_num se expresa como un número entero y le dice a la función qué argumento evaluar y devolver. Si index_num es igual a 1, entonces se devuelve el argumento valor1. Si núm_índice es igual a 2, entonces se devuelve el argumento valor2, y así sucesivamente.
• valor1 es el argumento que se devolverá si núm_índice es igual a 1. Tenga en cuenta que este argumento se puede expresar como un valor, referencia de celda, función o fórmula.
• [valor2] es opcional y se devolverá si index_num es igual a 2
• … y así sucesivamente, hasta unos 254 argumentos

Métodos presupuestarios

La idea entonces es permitir al usuario controlar el argumento index_num y, dependiendo de su valor, la función ELEGIR calculará la cantidad adecuada. Por ejemplo, digamos que el usuario puede ingresar el método de presupuesto en la celda A1. Supongamos que el método de presupuesto diferencial del año anterior (SpreadPY) es el código 1, que el método de igualación del año anterior (MatchPY) es el código 2 y que el método de disminución del año anterior en un 10% (Decrease10) es el código 3. Conceptualmente, podríamos establecer Cree una fórmula para calcular el monto del presupuesto adecuado según el método del presupuesto:

= ELEGIR (A1, SpreadPY, MatchPY, Disminuir10)

Dónde:

• A1 es la celda que almacena el método de presupuesto ingresado por el usuario.
• SpreadPY representa la fórmula que calcula la cantidad adecuada
• MatchPY representa la fórmula que calcula la cantidad adecuada
• Decrease10 representa la fórmula que calcula la cantidad adecuada

Esta idea se ilustra en la captura de pantalla siguiente, donde el usuario ingresa el método de presupuesto en la columna H y las celdas del presupuesto se calculan mediante la función ELEGIR.

La fórmula para I17, que se completa en todo el rango de presupuesto (I17:L:19), es la siguiente:

= ELEGIR ($H17,$G17/4,C17,C17*0.9)

Dónde :

• $H17 es la celda que contiene el método de presupuesto deseado
• $G17/4 es la fórmula del código 1, el método SpreadPY. Esto toma la suma del año pasado y la divide por 4.
• C17 es la fórmula para el código 2, el método MatchPY. Esto toma el valor de la celda del año anterior para que coincida con el valor de cada trimestre.
• C17*0.9 es la fórmula para el código 3, el método Decrease10. Esto toma el valor de la celda del año anterior y lo multiplica por 90%, lo que efectivamente le da una disminución del 10%.

La variedad de métodos presupuestarios que podrían establecerse sólo está limitada por nuestra imaginación. Podríamos configurar un método cero que ponga cada celda en cero. Podríamos establecer un método que prepare el presupuesto basándose en la plantilla, básicamente tomando los valores reales del año pasado divididos por la plantilla del año pasado y multiplicando el resultado por la plantilla del año presupuestario. También podríamos configurar un método manual. Donde el usuario ingresa las partidas presupuestarias manuales en una hoja de trabajo separada, y el método simplemente recupera los valores de esta área de entrada manual mediante una función de búsqueda o suma condicional. Dado que la fórmula del método presupuestario es simplemente cualquier fórmula de Excel, las posibilidades son infinitas.

Siguiente nivel

Sin duda, podrías aprovechar esta idea y llevarla al siguiente nivel. Por ejemplo, si se siente cómodo con la Validación de datos y la función Coincidencia, podría permitir que el usuario elija el método de presupuesto de un menú desplegable en la celda, aliviando la necesidad de que el usuario memorice los códigos del método de presupuesto, como se ilustra en el archivo adjunto. archivo.

Si se siente cómodo con fórmulas con nombre, puede crear un nombre para cada fórmula de método de presupuesto, de modo que los argumentos de la función ELEGIR sean nombres en lugar de fórmulas, simplificando el libro y haciéndolo más elegante y modular.

Bueno, esas son algunas ideas sobre cómo incorporar la función ELEGIR en sus libros de trabajo. Aquí, dejamos que el usuario elija las matemáticas para un libro de presupuesto, pero esta idea se puede utilizar más allá de la ilustración del presupuesto presentada.

Si lo desea, no dude en consultar el libro Elegir libro utilizado para crear las capturas de pantalla anteriores, ya que contiene las fórmulas.

¡Reglas de Excel!