miércoles, 29 de abril de 2009

JERARQUIA de un valor dentro de un rango o matriz

Hoy, con vuestro permiso, resolveremos una consulta que me habéis planteado. Partiendo de una tabla en la que tenemos información relativa a los empleados de la empresa y su sueldo anual bruto, queremos generar una Entrada de Datos que nos permita seleccionar, de una lista desplegable, a uno de dichos empleados y que me indique qué puesto ocupa por ganancias dentro de la empresa. La tabla de partida es la que se puede ver a continuación:

La solución, como veremos enseguida, es bastante sencilla. Para ello utilizaremos, entre otras, la función JERARQUIA, que paso a explicar brevemente:

=JERARQUIA(Número;Referencia;Orden)

* El argumento Número    hace referencia a la cifra concreta (en nuestro caso será el sueldo bruto) cuya jerarquía deseamos conocer.
* El argumento Referencia    es una matriz de una lista de números o una referencia a una lista de números (en nuestro caso será el rango de sueldos).
* Y finalmente el argumento Orden es un número que especifica cómo clasificar el argumento número. Los valores que puede tomar este argumento son básicamente dos: cero o cualquier valor distinto de cero. Si ponemos cero (o lo omitimos) la referencia de orden será descendente, mientras que en el caso contrario la referencia de orden será ascendente.

Vamos a utilizar dos soluciones distintas aunque muy similares.
Solución 1: En el rango G3:G17 resolvemos el orden para todos y cada uno de los empleados de la tabla. Seguimos estos pasos:
1. Nos situamos en G2 y escribimos el rótulo Orden.
2. Seleccionamos el rango E3:G17 y hacemos clic en el cuadro de nombres (a la izquierda de la barra de fórmulas) y escribimos el nombre tabla2 y pulsamos enter.
3. Seleccionamos el rango E2:F17 y vamos al menú Insertar/Nombre/Crear y dejamos marcado sólo nombres en la columna superior. De esta manera ya habremos dado nombre a los distintos rangos que luego utilizaremos en nuestras fórmulas.
4. Nos situamos en la celda B2 y preparamos los rótulos como se ve en la imagen. Vamos a C2 y abrimos el menú Datos/Validación de datos y seleccionamos Permitir/Lista y en Origen escribimos =Empleado. De esta manera ya tendremos nuestra lista desplegable en C2 para elegir el empleado del que queramos la información (evidentemente en C5 no debe escribir nada por ahora ya que es donde resolveremos la fórmula):

5. Nos situamos en G3 y escribimos la siguiente fórmula:
=JERARQUIA(F3;Sueldo)
Esta fórmula nos devolverá el orden que ocupa el sueldo que aparece en F3 dentro del rango Sueldo. Si quiere que aparezca como "6º" en vez de "6" sólo tiene que añadir a la fórmula la función CONCATENAR, o lo que es lo mismo, el operador &:
=JERARQUIA(F3;Sueldo)&"º"
6. Copiamos la fórmula de G3 en el resto de celdas del rango hasta G17. De esta manera ya tenemos el orden que ocupa cada uno según su sueldo:



7. Nos situamos en C5 y escribimos la siguiente fórmula:
=BUSCARV(C2;tabla2;3;FALSO)
De esta manera estaremos buscando el puesta que ocupa solamente el empleado elegido en C2 en nuestra lista desplegable.

Solución2: Resolvemos directamente en C8 el problema sin calcular el orden de todos y cada uno de los empleados. Hacemos lo siguiente:
1. Si no hemos desarrollado la primera solución ejecutamos los pasos 2, 3 y 4 de la misma.
2. Seleccionamos el rango E3:F17 y le damos el nombre tabla1.
3. Nos situamos en C8 y escribimos la siguiente fórmula:
=JERARQUIA(BUSCARV(C2;tabla;2;FALSO);Sueldo)&"º"
Fíjese que hemos anidado la función BUSCARV como primer argumento de la función JERARQUIA. Esto nos proporcionará la búsqueda del sueldo del empleado que introduzcamos en C2. Evidentemente, el resultado obtenido es el mismo que en la solución 1.
 Si desea añadir una Salida de Datos como la que se muestra en la imagen en B11, sólo tiene que añadir la siguiente fórmula:
="Es el "&C8&" que más gana"

Fíjese que de nuevo hemos utilizado la función CONCATENAR (&) para unir varios textos y una referencia al valor de la celda C8, además de decorar un poco el contorno. En este sentido (en el de la decoración) permitidme que os recomiende que jamás utilicéis la opción de formato combinar y centrar, que tenéis en vuestra barra de formato y que es el icono que podéis ver en la imagen:
este tipo de formato da muchísimos problemas, como sabréis. Es mucho mejor que seleccionéis, en este caso, B11 y C11 y vayáis al menú Formato/Celdas/Alineación y en la opción Horizontal seleccionéis Centrar en la selección. El resultado es el mismo pero esta opción no os dará ningún problema.

Como apunte final indicaros que la función JERARQUIA es como la "inversa" de K.ESIMO.MAYOR (o MENOR). Para que se comprenda correctamente con K.ESIMO.MAYOR estaríamos preguntando (en nuestro ejemplo) quién es el 6º que más gana, mientras que con JERARQUIA lo que calculamos es precisamente el orden que ocupa un determinado empleado.

martes, 28 de abril de 2009

Análisis de Sensibilidad con Controles de Número

Cuando concluimos un modelo en nuestra hoja de cálculo llega el momento de empezar a "exprimirlo" para obtener toda la información que estimemos oportuna. Una tarea muy habitual es realizar cambios en uno o en varios datos de nuestra Entrada para comprobar cómo y en qué medida afecta a nuestro resultado.

Aunque en próximos artículos abordaré con más detenimiento este tipo de análisis, y veremos cómo utilizar la herramienta Tabla (menú Datos/Tabla), vamos a ver ahora cómo llevar a cabo esta tarea de manera rápida, sencilla y "estética". Para ello, vamos a aprovechar nuevamente el ejemplo del artículo "Cálculo de la cuota hipotecaria". En la Entrada de Datos de dicho modelo vamos a introducir Controles de Número, concretamente en el Plazo en Años y en Pre/Postpagable, como se puede observar en la siguiente imagen: 
¿Cómo introducimos estos Controles de Número y para qué?
Empezando por el final, estos controles nos permitirán, por medio de "clics" en las flechas hacia arriba o hacia abajo, incrementar o disminuir los números que aparezcan en las celdas B4 y B6. Si, por ejemplo, quisiéramos calcular la cuota a pagar para distintos plazos en años, bastará con hacer unos cuántos clics en el Control de Número y trabajo terminado (y no habrá tenido que teclear diversas cifras en la celda B4).  Lo mismo ocurrirá si quiere comprobar la diferencia en la cuota a pagar con pagos prepagables o postpagables. Cada vez que quiera realizar este cálculo deberá introducir en la celda B6 un uno o un cero, mientras que tras agregar el Control de Número bastará con hacer un clic hacia arriba o uno hacia abajo.

1. Abrimos el menú Ver/Barras de herramientas/Formularios. En la barra que se abre hacemos un clic encima del icono Control de Número, como puede ver en la imagen:
2. Nos desplazamos a la celda B6 y hacemos clic en el botón izquierdo del ratón y sin soltar el clic arrastramos hacia la derecha. Al soltar el clic habrá creado el Control de Número. Ahora tenemos que "programarlo" para que funcione correctamente.
3. Hacemos clic con el botón DERECHO del ratón encima del Control de Número y seleccionamos la opción Formato de control. Se abrirá la siguiente ventana:
En Valor mínimo escribimos 1, ya que será el plazo mínimo en años que contemplemos. En Valor máximo escribimos, por ejemplo, 40. En Incremento escribimos 1 ya que cada vez que pulsemos el control de número hacia arriba o hacia abajo querremos que la cifra (plazo en años) se incremente o disminuya en uno. Y, finalmente, en Vincular con la celda seleccionamos B4. Pulsamos Aceptar.

Fíjese que ahora tendrá incorporado en B4 un "estético" botón que al pulsarlo hacia arriba incrementará la cifra de Plazo en Años y, en consecuencia, modificará la cuota a pagar.

4. Para incorporar el Control de Número en B6 debe seguir los mismos pasos para crearlo y debe "programarlo" como sigue: en Valor mínimo escribimos 0. En Valor máximo escribimos 1 (son los dos valores posibles que puede tomar este argumento). En Incremento escribimos 1. Y, finalmente, en Vincular con la celda seleccionamos B6. Pulsamos Aceptar.

Como indiqué al principio, esta es una manera sencilla, rápida y "estética" de comprobar cómo afecta a nuestro resultado (en este caso la cuota a pagar) cambios en alguna/s variable/s. Pero no se trata de un de un análisis de sensibilidad "serio" porque, entre otras cosas, no nos permite guardar automáticamente los resultados obtenidos para cada prueba realizada. Para conseguir este objetivo, como también ya indiqué, tendremos que utilizar la herramienta Tablas. No se preocupe... lo veremos.

lunes, 27 de abril de 2009

Mandamiento 2: Pensando el Modelo

Continuando con nuestro pequeño manual de buenas prácticas en Excel, hoy abordaré el "segundo mandamiento" de nuestra particular Biblia. Vaya por delante que la cuestión que trataremos no es tan tangible como muchas otras que nos ocuparán más adelante, pero espero que sepa darle la importancia que tiene que, créame, es mucha.

Segundo Mandamiento: ANTES DE TECLEAR ME DEDICARÉ A PENSAR
Evidente ¿no? Pues lo cierto es que este es el segundo pecado grave que cometen muchísimos usuarios de Excel. Antes de empezar a desarrollar cualquier modelo en una hoja de cálculo debemos emplear el tiempo que sea necesario para tener muy claro en dónde estamos y a dónde queremos llegar. En caso contrario, y teniendo en cuenta la ley de Murphy, acabaremos realizando el planteamiento menos flexible y que más nos complique la existencia cuando queramos sacar partido a nuestro modelo.

Muchos expertos recomiendan la técnica denominada 80/20, que significa dedicarle el 80% del tiempo a pensar y el 20% a "ejecutar". Sin ánimo de entrar en mayores discusiones y mucho menos de ofender a nadie (y menos a los expertos...) esta teoría me parece eso... una teoría. Cualquier profesional que utilice diariamente las hoja de cálculo sabe que, entre otras cosas, es muy difícil, por no decir imposible, estimar el tiempo que le llevará desarrollar un modelo y, en consecuencia, más difícil todavía calcular el 80%-20% de una cifra que desconozco (aun siendo un fenómeno con la hoja de cálculo). En cualquier caso, me gustaría que nos quedásemos con la idea de fondo implícita en esta teoría en el sentido de otorgarle verdadera importancia, tanto en calidad como en cantidad, al tiempo prestado para plantear lo que quiero hacer y a dónde quiero llegar. Sé que muchos pensaréis que ya os gustaría tener tiempo para pensar... pero si volvéis a leer esta última frase os daréis cuenta de que entonces algo no funciona...

Como siempre, permitidme que utilice un par de ejemplos muy sencillos pero que creo que ilustran de manera clara el problema del que estamos hablando (y recordad que si en lo sencillo ya se complica la cosa, cuando ésta sea compleja qué os voy a contar...).

a) Supongamos que deseamos realizar una estimación de la evolución de nuestras ventas en los próximos cuatro años. Partiendo de la cifra de ventas del año cero vamos a aplicar un porcentaje de crecimiento de ventas distinto para cada año. La salida de datos deseamos que sea en horizontal, es decir, el rótulo de "ventas estimadas" en fila y los rótulos de los años, con sus correspondientes cifras, en varias columnas:
Si me pongo a teclear inmediatamente, sin pensar siquiera un segundo, lo más normal es que plantee la entrada de datos de la siguiente forma:
Con este planteamiento enseguida advertiremos que necesitaremos plantear 5 fórmulas para obtener nuestra estimación cuando, en realidad, con 2 es más que suficiente. Puedo comprobarlo planteando esta otra entrada de datos:

b) Para el siguiente ejemplo vamos a recuperar la entrada de datos que planteamos en el artículo "Cálculo de cuotas hipotecarias y Buscar Objetivo", y que puede ver a continuación:
jese que en esta Entrada de Datos contemplamos, entre otros, el concepto "Nº de Pagos al Año" ¿Por qué? Porque aunque la periodicidad de pago más habitual es la mensual (12 pagos al año) podría interesarnos calcular la cuota resultante para pagos bimestrales, trimestrales, semestrales o incluso anuales. Como hemos sido buenos "fieles", y cumplido por tanto con el segundo mandamiento, nos hemos anticipado y pensado previamente qué análisis o información podría requerir de este modelo. Y es por ello que realizar este tipo de cálculos sólo nos supondrá el "cansino" trabajo de modificar el número existente en la celda B5. Por desgracia (por no pararse a pensar un poco), en la mayoría de los casos los usuarios habrán planteado el modelo dividiendo y multiplicando (dependiendo del argumento en cuestión) por 12.

Saber de antemano todo lo que voy a necesitar de un modelo es imposible. Pero entre saberlo todo y dedicarle un tiempo razonable a plantearnos cuestiones tales como ¿qué información voy a necesitar? ¿Con que detalle? ¿Con que periodicidad? ¿Cuántas personas van a "manipular" este modelo? etcétera, hay una diferencia muy importante. Y créame que la experiencia demuestra que ese será un tiempo muy bien invertido y que le ahorrará muchos problemas y, por supuesto, tiempo. Y es que pensar, al final, no va a ser tan malo...

sábado, 25 de abril de 2009

Cálculo de cuotas hipotecarias y Buscar Objetivo

En el artículo de hoy desarrollaré un modelo sencillo para calcular las cuotas a pagar de un préstamo. Una vez desarrollado, procederemos a realizar análisis donde conocemos todos los datos excepto uno, la incógnita, que queremos calcular. Por ejemplo:

* Dado un tipo de interés y sabiendo que el máximo que puedo pagar mensualmente son 800€ ¿Qué importe máximo podría financiar?
* Dado un importe a financiar y el plazo en años ¿qué tipo de interés me permitiría pagar esta cuota (800€)?
* Dado un tipo de interés y un importe a financiar ¿a cuántos años debería financiarme para pagar la cuota indicada?

Para ello utilizaremos, básicamente, la función PAGO y la herramienta Buscar Objetivo. Los pasos a seguir son los siguientes:

1. Preparamos el "esqueleto" del modelo tal y como se muestra en la imagen, y que corresponde a la Entrada de Datos (rango A2:B7) y a la Salida de Datos (celda B10). 
2. Nos situamos en la celda B10 y escribimos la siguiente fórmula:
=-PAGO(B3/B5;B4*B5;B2;B7;B6)

La función PAGO calcula la cuota a pagar basándose en pagos constantes y tipo de interés fijo (método de amortización francés). Presenta la siguiente sintaxis y argumentos:
=PAGO(Tasa;Nper;Va;Vf;Tipo) donde:
Tasa: es el tipo de interés
Nper: es el número total de pagos
Va: es el importe a financiar
Vf: saldo en efectivo que se desea lograr tras efectuar el último pago (en nuestro ejemplo lo equipararemos a una posible opción de compra).
Tipo: Si el pago es al principio del periodo tomará el valor 1 (prepagable) y si el pago es al final del periodo tomará el valor 0 (postpagable)
La función PAGO devuelve por defecto un valor con signo negativo. Si prefiere trabajar con la cifra en positivo sólo tiene que añadirle un signo "-" delante de la función:  -PAGO.

El modelo que planteo es sencillo pero bastante completo. Si desea practicar algunas de las herramientas comentadas en este blog puede probar a añadir botones de opción o cuadros combinados para el argumento Tipo en la entrada de datos (no olvide que tendrá que realizar pequeñas modificaciones en la fórmula introduciendo un condicional en el argumento Tipo de la función PAGO).

Como hemos hecho correctamente "los deberes" al separar la entrada de la salida de datos, ahora será muy sencillo realizar distintas pruebas para comprobar que resultado obtenemos simplemente modificando los datos en el rango B2:B7. Pero qué ocurre si queremos calcular, por ejemplo, lo siguiente:

a) ¿Cuál es el importe máximo que podría financiar si la cuota máxima que puedo pagar es de 800€?
Para dar respuesta a esta pregunta podríamos empezar a probar datos en la celda B2 hasta obtener la cuota indicada (y esto siempre que haya respetado el modelo Entrada-Salida de Datos...). Pero nos podría llevar un buen rato que, en cualquier caso, es innecesario. Para resolver este tipo de problemas vamos a utilizar la herramienta Buscar Objetivo:
3. Vamos al menú Herramientas/Buscar objetivo y se abrirá la siguiente ventana:
4. En el cuadro Definir la celda seleccionamos B10. Definir la celda debe hacer siempre referencia a una única celda y que contenga una fórmula.
5. En el cuadro Con el valor escribimos 800. Este cuadro, por desgracia, no nos permite vincular con celdas de la hoja, por lo que tendremos que escribir el valor dentro del cuadro.
6. En el cuadro Para cambiar la celda seleccionamos B2. En este cuadro sólo podemos hacer referencia a una celda y que contenga un dato (no una fórmula):
Fíjese que con lo que hemos hecho le estamos pidiendo que pruebe valores en B2 hasta conseguir que B10 valga 800, que es precisamente lo que estamos buscando. El resultado, tras pulsar Aceptar será el siguiente: 

Fíjese que la celda B2 nos presenta el resultado del objetivo buscado. Es decir, que para una cuota de 800€ el máximo que podríamos financiar (manteniendo el resto de datos -tipo de interés, plazo, etcétera- constantes) sería 124.165€. Para terminar, debe pulsar Cancelar , en la ventana que tiene abierta y que puede ver en la imagen, si quiere mantener el importe a financiar original y Aceptar en caso que quiera modificarlo e incorporar el resultado de Buscar Objetivo.

Con los datos originales, pruebe a calcular lo siguiente:
b) ¿Cuál sería el Tipo de Interés Nominal Anual (TIN Anual) que debería ofrecerme el banco para que mi cuota fuera de 800€ -resto de datos constantes-?
c) ¿Cuál sería el plazo en años si quisiera pagar 800€ de cuota -resto de datos constantes-?

Solución b: TIN Anual del 4,10% (más concretamente 4,09927032812685%)
Solución c: Plazo en Años igual a 46 (más concretamente 46,325239162993 años)

Búsquedas basadas en varias columnas a la vez

En el artículo de hoy vamos a ver cómo podemos realizar búsquedas dentro de una tabla basadas en elementos de varias columnas a la vez. Supongamos que tenemos la siguiente tabla:
Queremos introducir en la ENTRADA DE DATOS la Referencia y el Tipo para, en la SALIDA DE DATOS, obtener información sobre el Coste, el PVP y el Descuento asociado (Dcto), información con la que, además, realizaremos algunos cálculos:
Como parece evidente necesitamos realizar una búsqueda basada en dos elementos de distintas columnas (Referencia y Tipo). Para solucionar este problema (con el menor esfuerzo posible) debemos seguir los siguientes pasos:
1. Seleccionamos el rango D2:H11 y vamos al menú Insertar/Nombres/Crear. Por defecto, en el menú que aparecerá, vienen marcadas las opciones nombres en columna superior y nombres en columna izquierda. Dejamos seleccionada solamente nombres en columna superior y aceptamos. De esta manera habremos creado los nombres de cada columna de nuestra tabla (puede comprobarlo abriendo el Cuadro de Nombres que se encuentra a la izquierda de la barra de fórmulas) que se corresponderán precisamente con los rótulos de cada columna de dicha tabla.
2. El siguiente paso será preparar la entrada de datos con sendas listas desplegables para que seleccionemos de éstas la Referencia y Tipo que nos interese. Para ello nos situamos en la celda B2 y vamos al menú Datos/Validación de datos y seleccionamos Permitir/Lista. En el cuadro Origen escribimos =Referencia  ,que es el nombre del rango que contiene los elementos de nuestra lista.
3. Nos situamos en la celda B3 y vamos al menú Datos/Validación de datos y seleccionamos Permitir/Lista. En el cuadro Origen seleccionamos el rango E3:E5 (fíjese que en este rango están los tres tipos que se repiten para las distintas referencias y por ello no hace falta seleccionar el rango E3:E11 o utilizar el Nombre de Rango Tipo). Aceptamos.

Ya tenemos la entrada de datos preparada para seleccionar, por medio de listas desplegables, la referencia y tipo con las que nos interese trabajar. Ya sólo nos queda formular... 

Para resolver "el problema" de buscar en varias columnas (en nuestro caso dos) vamos a utilizar las funciones INDIRECTO, COINCIDIR, CONCATENAR e INDICE, con entrada matricial (las funciones INDIRECTO y COINCIDIR han sido utilizadas en numerosos ejemplos de este blog que puede consultar si lo necesita). La función CONCATENAR nos permite lo que su nombre indica, es decir, unir varios argumentos en uno solo. El operador "&" hace las mismas funciones que CONCATENAR. Puede probar esta función situándose, por ejemplo, en la celda D14 de nuestro modelo y escribiendo la siguiente fórmula: ="El Coste de "&D3&E3&" es de "&F3    , y el resultado que obtendrá será: El Coste de MontañaGTX es de 452
La función INDICE, por otro lado, nos proporciona el dato que se encuentra en una fila y columna determinada de una matriz.

4. Nos situamos en B6 y escribimos la siguiente fórmula:
=INDICE(INDIRECTO(A6);COINCIDIR($B$2&$B$3;Referencia&Tipo;0))
PERO NO PULSAMOS ENTER. Como estamos trabajando con matrices dentro de la función COINCIDIR, tenemos que realizar una entrada matricial y para ello debemos pulsar las teclas Ctrl+Shift+Enter. El resultado será la misma fórmula pero con llaves:
{=INDICE(INDIRECTO(A6);COINCIDIR($B$2&$B$3;Referencia&Tipo;0))}

Fíjese que hemos utilizado "&" (que es lo mismo que CONCATENAR) dentro de la función COINCIDIR. De esta manera le estamos pidiendo que una B2 y B3 y que los busque en el rango llamado Referencias y Tipo, que también hemos unido con "&". El resultado de esta parte de la fórmula será el número de fila de nuestra tabla que ocupa la unión de B2 y B3. Conseguido esto sólo nos queda indicarle de qué columna queremos la información y asunto resuelto. Fíjese que en B6 queremos obtener el Coste. Para ello sería suficiente con poner el nombre de este rango (que es precisamente Coste) como primer argumento de la función INDICE. Pero en B7 y en B8 necesitaremos el PVP y el Descuento respectivamente. Para resolverlo con una sola fórmula he utilizado INDIRECTO(A6) en vez del nombre de rango correspondiente (y, por supuesto, referencias absolutas en B2 y B3). 

5. Copiamos la fórmula escrita en B6 en las celdas B7 y B8. Problema resuelto.
6. Las fórmulas de B10 y B11 no tienen ningún misterio, a saber:
    en B10:   =B7*(1-B8)        para calcular el precio menos el descuento.
    en B11:   =(B10-B6)/B6   para calcula el porcentaje de margen tras el descuento.


jueves, 23 de abril de 2009

Mandamiento 1: Modelo Entrada-Salida de Datos

El problema fundamental de una grandísima mayoría de usuarios de Excel es que comienzan mal desde el principio, con lo que el resultado final ya os lo podéis imaginar...
Sé que parece una sentencia un poco fuerte como entrante, pero os propongo que abramos apetito rápidamente para atacar al plato sin miramientos...

Tal y como prometí en "Búsqueda de elementos idénticos en distintas tablas", voy a dedicar, con vuestro permiso y espero que interés, varios artículos a explicar lo que, en mi opinión, son cuestiones fundamentales a la hora de ponerse a trabajar con la hoja de cálculo. El objetivo es muy sencillo: obtener el máximo rendimiento con el menor esfuerzo posible. Para alcanzar esta meta es necesario respetar algunas reglas que poco a poco intentaré desgranar. Probablemente -seguro- algunas de ellas ya las estaréis aplicando. Otras os parecerán evidentes o una pérdida innecesaria de tiempo (a éstas, por cierto, son a las que más temo...). En cualquier caso, voy a tratar de elaborar un pequeño manual de buenas prácticas de Excel que nos ayude a sacarle mucho más jugo a una de las herramientas más útiles que existen y, sin embargo, tan desaprovechada. Manos a la obra...

Primer Mandamiento: JAMÁS INTRODUCIRÉ DATOS EN LAS FÓRMULAS
Este es el primer mandamiento porque, en mi opinión, es el más importante de todos y uno de los menos respetados. En mis más de trece años de experiencia docente en las aulas del IE Business School he constatado que este "pecado mortal" lo cometen todo tipo de usuarios: los principiantes, los de nivel medio y los que piensan que son usuarios avanzados... Y lo más curioso de este pecado es que muy frecuentemente se cobija bajo el triste argumento "es que voy con prisas y no puedo perder el tiempo con virguerías innecesarias... Si eso, ya luego lo corrijo..." ¿Virguerías? ¿Innecesarias? ¿Luego lo corrijo? El principio del fin...

La clave de partida para el buen uso de la hoja de cálculo es utilizar el Modelo Entrada-Salida de Datos. Y dice así: Los datos deben estar separados de las fórmulas y las fórmulas de los datos. Nuestra hoja debe de estar planteada por un lado con una zona (o varias) donde tengamos los datos -valores- (cifras, porcentajes, nombres de productos, nombres de empleados, etcétera) y, por otro lado, con otra/s zona/s donde desarrollemos nuestras fórmulas que nos proporcionarán los resultados finales buscados. Dentro de estas fórmulas jamás debe aparecer un dato. Dicho dato deberá contemplarse en la zona de Entrada mientras que en las fórmulas haremos referencia a la celda donde se encuentre. A continuación propongo algunos ejemplos muy sencillos pero que creo que ilustran perfectamente lo que se debe y lo que no se debe hacer:



Evidentemente, los modelos que manejamos a diario son más complicados y/o extensos que estos sencillos casos ¡Razón de más para respetar este mandamiento hasta la exageración! Para que lo vea de una forma práctica plantéese las siguientes situaciones en nuestro segundo ejemplo:

1. He confundido la cifra de ventas del año cero y debo modificarla. Si ha respetado el primer mandamiento sólo tendrá que ir a la celda B10 y sustituir por la cifra correcta. Trabajo terminado en un par de segundos. Si ha introducido la cifra 1.850€ en una o varias celdas en las fórmulas ¿qué deberá hacer? Efectivamente, buscarlas y modificarlas una a una (y que no se le olvide ninguna...)
2. Quiero comprobar como afectaría al margen bruto del año 1 una reducción del CMV hasta el 55% sobre la cifra de ventas. Con el Modelo Entrada-Salida de Datos vamos a B12 y simplemente escribimos la nueva cifra (55%). Si por el contrario ha decidido, por aquello de no perder tiempo y todo ese rollo, escribir el 67% dentro de las fórmulas... mal asunto.
3. Quiero plantear distintos escenarios para mi estimación de ventas. Si lo ha hecho correctamente podrá utilizar de una manera tremendamente sencilla la herramienta Escenarios. En caso contrario, simplemente no podrá utilizarla y, probablemente, optará por empezar a insertar nuevas hojas; copiar y pegar el modelo; sustituir valores; etcétera (y esto no es perder el tiempo, claro...).

Y así podríamos seguir con infinidad de situaciones cotidianas que hacen que Excel, más que un amigo, se acabe convirtiendo en una pesadilla.

Este mal uso de la hoja de cálculo es absolutamente frecuente y lo he comprobado tanto en los modelos que me presentan mis alumnos como en muchos de los trabajos de consultoría que he realizado en diversas empresas. El resultado son horas y horas dedicadas a la búsqueda de errores; desconfianza en nuestros propios modelos (es muy típico encontrarse usuarios de Excel con una calculadora al lado para comprobar que los resultados están bien); tener que empezar desde cero nuevamente un modelo porque ya no sabemos ni lo que contempla ni lo que calcula ni cómo lo hace; etcétera. En resumen, horas y más horas innecesarias e ineficientes cuando la solución, como ha visto, es muy sencilla. Por favor, hacedme caso, cumplid con el primer mandamiento y seréis recompensados...

martes, 21 de abril de 2009

Búsqueda con tablas e Indicador de Columnas Variable

Ampliando el modelo del artículo ”búsqueda de elementos idénticos en distintas tablas” ¿Qué podemos hacer cuando tenemos que buscar un elemento en distintas tablas y que, además, me devuelva información de distintas columnas (tablas e indicadores de columna variables )?

En este ejemplo vamos a manejar las tablas dentro de una misma hoja. Supongamos que nuestra tabla de salarios ahora tiene dos campos: Sueldo Bruto y Bruto mes. Por otro lado supongamos que nuestra tabla de ventas no tiene una única columna sino cuatro, correspondientes al desglose por trimestres. 

 

Queremos realizar un cuadro resumen con la información de nuestros comerciales pero con la posibilidad de elegir entre los distintos campos disponibles para cada tabla, como se puede ver en la siguiente figura: 

Como se puede apreciar en la imagen, dentro de la opción de la tabla de salarios podremos elegir que información queremos por medio de una lista desplegable. Lo mismo ocurrirá para la tabla de ventas, donde podremos escoger el trimestre que nos interese. Hay varias formas de solucionar este problema. Nosotros lo resolveremos nuevamente con las funciones BUSCARV e INDIRECTO. 

1. Nos situamos en E17 y escribimos las siguientes tablas: 

 Lo que estamos haciendo es establecer la relación entre cada campo de cada tabla y el número de columna que dentro de estas tablas ocupa cada campo. Por ejemplo, el campo Sueldo_Bruto ocupa la segunda columna dentro de la tabla Salarios. El trim3, dentro de la tabla ventas, ocupa la cuarta columna, etcétera.

2. Seleccionamos el rango E17:F18 y vamos al menú Insertar/Nombre/Crear. Por defecto nos propone nombres en columna izquierda: aceptamos.

3. Seleccionamos el rango E21:F24 y seguimos el paso anterior. Con estos dos pasos ya hemos conseguido que el nombre de campo de cada tabla se corresponda con el número de columna que ocupa dentro de dicha tabla.

4. Nos situamos en B3 y vamos al menú Datos/Validación de datos y marcamos Permitir/Lista y en el recuadro origen seleccionamos E17:E18. Aceptamos.

5. Nos situamos en C3 y vamos al menú Datos/Validación de datos y marcamos Permitir/Lista y en el recuadro origen seleccionamos E21:E24. Aceptamos.

Con los pasos 4 y 5 hemos generado las listas desplegables con las opciones para cada tabla.

6. Nos ponemos en B4 y escribimos la siguiente fórmula:

=BUSCARV($A4;INDIRECTO(B$2);INDIRECTO(B$3);FALSO) 

La primera parte de la fórmula  =BUSCARV($A4;INDIRECTO(B$2);  ya ha sido explicada en el artículo “búsqueda de elementos idénticos en tablas variables”. La segunda parte de la fórmula   INDIRECTO(B$3);FALSO)   significa lo siguiente. INDIRECTO B$3 leerá el contenido de B3 y lo transformará en referencia válida. En B3 tendremos, por ejemplo, el nombre Bruto_mes. Como hemos creado previamente estos nombres (ver pasos 2 y 3) INDIRECTO “cambia” B$3 por Bruto_mes y, por lo tanto, nos devolverá el valor que tiene asignado dicho nombre, que es dos. Y dos es precisamente el número de columna que ocupa Bruto_mes en la tabla Salarios.

7. Sólo nos queda copiar esta fórmula en el rango B4:C13 y trabajo terminado.