La interactividad puede lograrse de diversas formas pero una de las más sencillas para el usuario final es ir seleccionando distintas celdas y utilizar su contenido como parámetro para que produzca distintos resultados. Este proceso es muy fácil ya que el usuario solo debe seleccionar distintas celdas para ver como se actualiza el contenido. Para lograrlo utilizaremos la función llamada CELDA, que nos permite obtener información de una celda de nuestra planilla de cálculo.
La función CELDA (CELL para la versión en Inglés), tiene dos parámetros. El primero indica el tipo de información que se quiere obtener y el segundo es la celda sobre la cual buscaremos información.
Si queremos saber si la celda B8 está protegida podemos usar la siguiente fórmula =CELDA("proteger",B8), si el valor devuelto es 1 entonces está protegida y si el valor es 0 entonces no está protegida. Hacer clic en http://office.microsoft.com/es-es/starter-help/celda-funcion-celda-HP010342226.aspx?CTT=1 para ver los distintos parámetros de la función CELDA.
Si omitimos el segundo parámetro, la función CELDA devolverá información de la celda activa. Por ejemplo la fórmula =CELDA("DIRECCION") devuelve la dirección de la celda activa.
Veamos el ejemplo
Tenemos un listado de ventas de productos por país y queremos que al seleccionar una celda de la tabla nos informe el total del producto y del territorio incluido en esa línea.
Para el caso del territorio podemos usar la fórmula =SUMAR.SI(A2:A14,INDIRECTO(CELDA("direccion")),C2:C14). Utilizamos la función INDIRECTO para que el valor devuelto por la función CELDA sea transformado a una referencia que luego es utilizada por SUMAR.SI como la condición para realizar la suma.
Para realizar el total del producto simplemente cambiamos el rango que se usa como primer parámetro de la función SUMAR.SI quedando de la siguiente forma =SUMAR.SI(B2:B14,INDIRECTO(CELDA("direccion")),C2:C14).
Esta fórmula solo funciona si estamos en la columna correcta, para solucionarlo haremos algunos cambios en las formulas.
En primer lugar vamos a definir nombres para que la formula no sea tan larga y difícil de entender. Por un lado definimos el nombre UltCelda al que le asignamos la fórmula =CELDA("dirección") y luego nombre UltCol a la fórmula =CELDA("columna") y UltFila a la fórmula =CELDA("fila").
Para saber si la celda activa se encuentra dentro del rango de la tabla usamos la función SI y chequeamos que los valores de UltFila y UltCol estén dentro de los límites de la tabla de la siguiente forma =SI(O(UltCol>2,UltFila>14),"Fuera de rango",…).
Para obtener el nombre del producto sin importar en que columna de la tabla me encuentro, utilizamos =DESREF(INDIRECTo(UltCelda),0,2-UltCol), lo que nos permite ajustar la celda con el cálculo 2-UltCol. Si estoy en la columna A entonces 2-Ultcol será igual a 1 y eso nos desplazará una columna devolviendo la referencia a la columna B.
La fórmula final para los países es =SI(O(UltCol>2,UltFila>14),0,SUMAR.SI(A2:A14,DESREF(INDIRECTO(UltCelda),0,1-UltCol),C2:C14)) y para los productos =SI(O(UltCol>2,UltFila>14),0,SUMAR.SI(B2:B14,DESREF(INDIRECTO(UltCelda),0,2-UltCol),C2:C14)).
El último paso consiste en hacer que Excel recalcule la hoja cuando cambiamos de celda. Seleccionar una nueva celda no es suficiente para que Excel recalcule toda la hoja por lo tanto tenemos que agregar una pequeña macro para hacerlo.
La macro debe agregarse en la hoja que contiene las fórmulas antes explicadas.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub
Esta macro se ejecuta cada vez que se cambia la celda activa y lo que hace es forzar un recalculo de la hoja.
Para acceder al archivo de ejemplo hacer clic aquí.
