sábado, agosto 12, 2006

Usando funciones XLM (Excel 4) en hojas de cálculo.

Con anterioridad a la versión 5, Excel utilizaba un lenguaje macro llamado XLM (eXceL Macro, no confundirse con XML - eXtensible Markup Language).
Este lenguaje cuenta con una colección de funciones que pueden ser usadas en hojas de cálculo de Excel. Estas funciones nos permiten realizar cálculos que de otra manera sólo serían posibles utilizando Vba (Visual Basic for Applications, el lenguaje macro de Excel desde la versión 5).

Para utilizar estas funciones debemos incluirlas en nombres. Veamos algunos ejemplos.

Ejemplo 1 – Sumando de acuerdo al color de relleno de la celda

Fuente: JKP Application Development Services

Usamos la función XLM INDICAR.CELDA (Get.Cell en inglés) para extraer el número de índice del color del relleno.





Creamos el nombre Numero_de_Color que contiene esta fórmula: =INDICAR.CELDA(63,Hoja1!A2)

Copiamos el nombre a la celda B2 y al rango E2:E11. En la celda B3 introducimos esta fórmula: =SUMAR.SI($E$2:$E$11,B2,$D$2:$D$11)
Como puede verse, el resultado es la suma de todos los valores con color de relleno verde.

Dado que el cambio de color no fuerza a Excel a recalcular la hoja, debemos pulsar F9 para forzar a Excel a recalcular la fórmula cada vez que cambiemos el color en la celda B2.
Otro método es convertir a nuestra fórmula en volátil agregándole una función volátil que no afecte el resultado: =INDICAR.CELDA(63,Hoja1!A2)+AHORA()*0

Algunos otros parámetros de INDICAR.CELDA
14: da VERDADERO si la celda está bloqueada, o FALSO si no lo está
18: nombre de la fuente (font) usada en la celda
19: tamaño de la fuente, en puntos
48: da VERDADERO si la celda contiene una fórmula o FALSO si contiene una constante
62: da el nombre del cuaderno y la hoja activos en la forma

Al final de la entrada hay un enlace para descargar el archivo de ayuda con todas las fórmulas XLM.

Ejemplo 2 – Evaluar texto como fórmula

Fuente: aporte de KL en el foro Exceluciones

Supongamos el siguiente texto en la celda A1




Usaremos la fórmula XLM EVALUAR (Evaluate en inglés) para usar ese texto como fórmula.
Creamos el nombre LeerFormula que contiene la fórmula =EVALUAR(A1&T(HOY()))

Ponemos el nombre en la celda B1 y obtenemos el resultado



Como en el caso anterior, la expresión "&T(HOY()" fuerza a Excel a recalcular la fórmula.

Actualización - Algunas aclaraciones importantes de KL:

1 - Es ESENCIAL subrayar que antes de definir el nombre se debe seleccionar la celda [B1]. Es decir, estamos jugando con la relatividad de la referencia A1 (!!! sin el dolar) con respecto a la B1.

2 - Ademas, si se quiere usar el truco en todas las hojas del mismo libro, la formula deberia introducirse en el siguiente formato (o sea, precediendo la referencia de la celda de un signo de exclamacion): =EVALUAR(!A1&T(HOY()))


Aquí se puede encontrar información sobre el lenguaje de macros Excel 4.0 (XLM) y descargar el archivo con la lista de funciones.


Categorías: Funciones&Formulas_


Technorati Tags:

21 comentarios:

  1. Hola Jorge!
    mi post es simplemente para dar las gracias por darte el tiempo de explicar cosas de Excel que no mucha gente (creo) lo haría, y así poder aprovechar de mejor manera esta herramienta.
    Saludos

    ResponderBorrar
  2. Estimado Jorge.

    Trabajo con office 2003 (evito usar pc con office 2007), no he podido integrar las herramientas de macros acá expuestas. Que puedo hacer. Lo que acá recomiendas es para office 2000.

    Felicidades por el BLOG.
    Manuel

    pesquerox@gmail.com

    ResponderBorrar
  3. Hola,
    el ejemplo lo he desarrollado en XL2003.
    Tienes los macros habilitados?
    También puedes enviarme el archivo con el ejemplo que no funciona para tratar de entender dónde está el problema.

    Gracias por los conceptos.

    ResponderBorrar
  4. Hola:

    No he podido desarrolaar la funcion EVALUAR siempre el programa de dice error y me sombre la palabra evaluar

    ResponderBorrar
  5. ¿Que tipo de error recibes? ¿Qué es lo que estás tratando de calcular?

    ResponderBorrar
  6. HOLA COMO VAMOS

    MIRA CUANDO DIGITO =EVALUAR(A1&T(HOY()))
    EL SISTEMA ME MUESTRA NUEVAMENTE LA FORMULA EN EL ESPACIO DE FORMULAS CON LA PALABRA EVALUAR RESALTADA, ES DECIR NO ME ADMITE EL COMANDO, SI COPIO DEL ARCHIVO DEL FIXTURY 40 LA HOJA AUXILIAR DE RESULTADOS ELLA NO ME FUNCIONA..
    GRACIAS

    BUEN DIA¡¡¡¡¡

    ResponderBorrar
  7. ¿Qué idioma estás usando en Excel? Si tienes el Excel en inglés tendrías que usar EVALUATE.

    ResponderBorrar
  8. Hola buen día!!

    Uso español, estoy en Bogotá Colombia.
    La función EVALUAR o EVALUATE, no aparecen cuendo se solicita que se muestren todas las funciones, tanto en el cuadro de dialogo como en el asistente de funciones.
    Si tu tienes el archivo de dicha funcion y me lo puedes facilitar te lo agradezco.
    O si existe otro comando o la convinacion de comandos que realice la misma función, por favor digamelo
    Atento cordial saludo
    PabloA

    ResponderBorrar
  9. Pablo, creo entender donde está tu problema. Como pongo al principio de la nota, estas funciones no pueden ser usadas directamente en una celda. Para usarlas hay que incluirlas en un nombre. También tienes que asegurarte que las macros puedan correr (nivel de seguridad medio o bajo).

    ResponderBorrar
  10. Hola Jorge Buen día!

    Al generar el nombre se debe digitar la funcion evaluar o evaluate y en ese momento tambien me genera el error.
    En conclusión no tengo estas funciones en mi equipo. A proposito uso excel 2010

    Atento cordial saludo
    PabloA

    ResponderBorrar
  11. Pablo, finalmente pude sentarme y probar el uso de las funciones XLM (Excel 4) en mi versiòn de Excel 2010. Funcionan perfectamente. Es más, Microsoft le ha dado un trato muy especial a antiguo XLM.
    As[i que no tengo ni idea sobre cual pueda ser tu problema (las funciones XLM no se instalan por separado, vienen con Excel por motivos de compatibilidad).
    Si quieres puedes mandarme el archivo para que le de un vistazo.

    ResponderBorrar
  12. Hola Jorge Buen día!!

    Gracias por la ayuda, cuando me dices "las funciones XLM no se instalan por separado" me distes la pista necesaria en mi caso.
    Reinstale EXCEL 2010 y funcionó.
    Ahora a seguir estudiando EXCEL, con base en estos ejemplos tuyo.

    Gracias mil
    PabloA

    ResponderBorrar
  13. Hola Jorge!
    tengo un libro de 68 hojas con formato igual cada una contiene la informacion de un colegiado.
    Pregunto es posible generar un proceso para llevar a una sola hoja todo lo digitado en las 68 hojas.
    la informacion diferente o que se requiere llebar inicia en la sexta linea.

    Atento cordial saludo

    PabloA

    ResponderBorrar
  14. Pablo,
    hay varias técnicas en Excel para consolidar datos. Te sugiero mirar las notas de la etiqueta Consolidar Datos en mi blog.

    ResponderBorrar
  15. Hola Jorge!
    En consolidar datos encontre soluci{on a mi caso muchas gracias.
    Ahora quiero hacer lo siguiente y te pido me digas si es posible y como lo puedo realizar

    Voy a iniciar un libro con varias hojas de igual estructura pregunto es posible crear una hoja resumen o totalizadora donde se acumule automaticamente lo que vaya digitando en cada hoja y no hacer el proceso de consolidación de datos. que explicas sino que se vaya actualizando la hoja resumen o totalizadora.

    Gracias por la ayuda
    Atento cordial saludo

    PabloA

    ResponderBorrar
  16. Antes que nada, muchas gracias. Hace tiempo que quería usar una fórmula que calculara otra fórmula, cualquier fórmula. Esto para crear varias fórmulas en una sola celda en poco espacio. Pensé que tal vez Microsoft debería crear una fórmula parecida a Indirecto, pero para fórmulas, que se llamara por ejemplo: Indirectoformula. No obstante, Tuve algunas opciones como utilizar la función elegir, quise crear una función de usuario en visual basic, pero no me quedaba. Utilice la instrucción evaluate, pero en VBA. Y luego para calcular automáticamente utilice el evento Worksheets_calculate, pero era más complicado. Utilice la fórmula indirecto, pero se limita a rangos de celda. Pero ahora se ha abierto una posibilidad gracias a tí, ahora puedo crear listas de nombres de fórmulas y hacerlas funcionar con la función que me proporcionaste. De verdad muchas gracias.

    Otra pregunta, ¿Cómo se podría crear un detector de macros?, Yo no he encontrado la manera. supongo que es algo sumamente difícil, pero pensando locamente se me ocurría una instrucción que dijera ActiveMacro o Macroexecuted y que me devolviera el nombre de la macro en ejecución. Esto para desencadenar una serie de eventos. Y si no es mucha molestia, ¿podrías publicar más macrofunciones?
    Me declaro Excelfilo.

    ResponderBorrar
  17. Hola, mis disculpas por la demora en publicar tu comentario.
    Al final de la nota hay un enlace para descargar el archivo que contiene todas las macrofunciones.
    n cuanto a detectar macros, ¿te referís a generar una lista de las macros en el cuaderno activo o mostrar que macro se esta ejecutando?

    ResponderBorrar
  18. Buenas Estimado Jorge, pienso hacer de cero la plantilla para ir aprendiendo, me gustaría saber a que función equivale esta macrofuncion EVALUAR en Excel 2010 y 2013?
    De antemano gracias por la ayuda y muy bueno su blog

    ResponderBorrar
  19. Lo mismo, EVAUAR o EVALUATE si usas la versión en inglés.

    ResponderBorrar
  20. Yo estoy buscando hacer un grafico y aplicar colores según el codigo de cada color, se puede?

    ResponderBorrar
  21. Fijate en esta nota, donde hay enlaces a otras notas relacionadas con el tema.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.