Acabo de detectar que en las últimas 48 horas han sido enviados correos basura (spam) desde mi cuenta de Gmail. Por supuesto, estos mails han sido enviados sin mi consentimiento. Estoy intentando tomar medidas para que ésto ni vuelva a repetirse.
En ningún caso hay que activar ningún enlace que aparezca en el mail.
Lamento cualquier incoveniente que esta situación pueda haber generado.
lunes, marzo 07, 2011
martes, febrero 22, 2011
Listas desplegables con valores dependientes
Supongamos que queremos crear un formulario en Excel con dos celdas: hora de comienzo y hora de finalización. Las horas las elegimos de celdas desplegables, que creamos fácilmente con validación de datos. Pero en nuestro modelo queremos que las horas que aparezcan en la la celda “hora de finalización” dependan de la hora elegida en la celda “hora de comienzo
Para lograr que la lista desplegable de la celda “Hasta” se adapte a la hora introducida en la celda “De” tenemos que usar rangos dinámicos que crearemos con la función DESREF.
Los pasos para crear este modelo:
En el rango A3:A26 definimos las horas del día (en este ejemplo usamos una escala de una hora, pero podemos usar medias horas o cualquier otra escala). Definimos el nombre “rngHorarioSencillo” que se refiere a este rango.
Definimos el nombre “celdaDeSencillo” que se refiere a la celda E4.
En la celda C8 ponemos la fórmula
=COINCIDIR(celdaDeSencillo,rngHorarioSencillo,0)
y creamos el nombre “controlHora2Sencillo” que se refiere a la celda C8. Como alternativa podemos crear un nombre que contenga la fórmula en lugar de ponerla en una celda. En mi opinión, el uso de celdas nos permite crear modelos más claros y fáciles de administrar.
Para crear la lista desplegable de la celda “De” usamos el nombre “rngHorarioSencillo”
Para crear la lista desplegable en la celda “Hasta” usamos un rango dinámico definido por esta fórmula:
=DESREF(sencillo!$A$3,controlHora2Sencillo,,CONTARA(rngHorarioSencillo)-controlHora2Sencillo)
Otro detalle que podemos agregar es un evento que borre el contenido de la celda “Hasta” cuando cambiamos el valor de la celda “De”. En el módulo Vbe de la hoja ponemos este código
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("celdaDeSencillo").Address Then
Target.Offset(0, 1).ClearContents
End If
End Sub
Finalmente ocultamos las columnas A y B.
Un caso más complejo es cuando queremos que el modelo incluya varias líneas donde la hora de comienzo de una línea depende de la hora de finalización de la línea anterior
En este caso emplearemos prácticamente la misma técnica, pero en lugar de celdas de control incluiremos las fórmulas en nombres.
En la primer celda del formulario definimos la lista desplegable con el nombre “rngHorario”, que se refiere a todo el rango de horas
Ahora tenemos que definir los controles para las celdas “De” y “Hasta”. En el caso de “De” tenemos que referirnos a la celda “Hasta” inmediata anterior (la que se encuentra una fila por arriba). Esto lo hacemos definiendo la fórmula en un nombre pero asegurándonos que la referencia a la fila sea relativa (sin el signo $).
Al definir el control para el campo “De” seleccionamos la celda D5 y definimos el nombre “hora1Ref” con esta fórmula
=COINCIDIR(complejo!$E4,rngHorario,0)
Para definir el control para el campo “Hasta” seleccionamos previamente la celda E4 y definimos el nombre “hora2Ref” con esta fórmula
=COINCIDIR(complejo!$D4,rngHorario,0)
Estos controles nos sirven ahora para definir los rangos dinámicos de las listas desplegables.
Para las celdas D5:D9 definimos el nombre “rngHora1Comp” con la fórmula
=DESREF(complejo!$A$3,hora1Ref,,CONTARA(rngHorario)-hora1Ref)
Para las celdas E4:E9 definimos el nombre “rngHora2Comp” con la fórmula
=DESREF(complejo!$A$3,hora2Ref,,CONTARA(rngHorario)-hora2Ref)
El archivo con el ejemplo puede descargarse aquí.
Para lograr que la lista desplegable de la celda “Hasta” se adapte a la hora introducida en la celda “De” tenemos que usar rangos dinámicos que crearemos con la función DESREF.
Los pasos para crear este modelo:
En el rango A3:A26 definimos las horas del día (en este ejemplo usamos una escala de una hora, pero podemos usar medias horas o cualquier otra escala). Definimos el nombre “rngHorarioSencillo” que se refiere a este rango.
Definimos el nombre “celdaDeSencillo” que se refiere a la celda E4.
En la celda C8 ponemos la fórmula
=COINCIDIR(celdaDeSencillo,rngHorarioSencillo,0)
y creamos el nombre “controlHora2Sencillo” que se refiere a la celda C8. Como alternativa podemos crear un nombre que contenga la fórmula en lugar de ponerla en una celda. En mi opinión, el uso de celdas nos permite crear modelos más claros y fáciles de administrar.
Para crear la lista desplegable de la celda “De” usamos el nombre “rngHorarioSencillo”
Para crear la lista desplegable en la celda “Hasta” usamos un rango dinámico definido por esta fórmula:
=DESREF(sencillo!$A$3,controlHora2Sencillo,,CONTARA(rngHorarioSencillo)-controlHora2Sencillo)
Otro detalle que podemos agregar es un evento que borre el contenido de la celda “Hasta” cuando cambiamos el valor de la celda “De”. En el módulo Vbe de la hoja ponemos este código
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("celdaDeSencillo").Address Then
Target.Offset(0, 1).ClearContents
End If
End Sub
Finalmente ocultamos las columnas A y B.
Un caso más complejo es cuando queremos que el modelo incluya varias líneas donde la hora de comienzo de una línea depende de la hora de finalización de la línea anterior
En este caso emplearemos prácticamente la misma técnica, pero en lugar de celdas de control incluiremos las fórmulas en nombres.
En la primer celda del formulario definimos la lista desplegable con el nombre “rngHorario”, que se refiere a todo el rango de horas
Ahora tenemos que definir los controles para las celdas “De” y “Hasta”. En el caso de “De” tenemos que referirnos a la celda “Hasta” inmediata anterior (la que se encuentra una fila por arriba). Esto lo hacemos definiendo la fórmula en un nombre pero asegurándonos que la referencia a la fila sea relativa (sin el signo $).
Al definir el control para el campo “De” seleccionamos la celda D5 y definimos el nombre “hora1Ref” con esta fórmula
=COINCIDIR(complejo!$E4,rngHorario,0)
Para definir el control para el campo “Hasta” seleccionamos previamente la celda E4 y definimos el nombre “hora2Ref” con esta fórmula
=COINCIDIR(complejo!$D4,rngHorario,0)
Estos controles nos sirven ahora para definir los rangos dinámicos de las listas desplegables.
Para las celdas D5:D9 definimos el nombre “rngHora1Comp” con la fórmula
=DESREF(complejo!$A$3,hora1Ref,,CONTARA(rngHorario)-hora1Ref)
Para las celdas E4:E9 definimos el nombre “rngHora2Comp” con la fórmula
=DESREF(complejo!$A$3,hora2Ref,,CONTARA(rngHorario)-hora2Ref)
El archivo con el ejemplo puede descargarse aquí.
Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.
martes, febrero 15, 2011
Fórmulas con condiciones múltiples en Excel
Supongamos un formulario en Excel donde controlamos un proceso de aprobación de créditos compuesto de tres partes: presentación, proceso y aprobación
Al introducir una fecha en la celda A4, la celda Estatus (D4) muestra la etapa en que se encuentra el proceso; al introducir una fecha en B4, el valor de Estatus cambia a “En proceso” y finalmente al introducir una fecha en C4, Estatus muestra “Aprobado”
La solución más inmediata es crear una fórmula con la función SI
=SI(A4<>"",SI(B4<>"",SI(C4<>"","Aprobado","En proceso"),"Presentado"),"")
Esta fórmula tiene varios problemas. El primero es que el uso de SI anidado nos pone serias limitaciones a la cantidad de condiciones que podemos procesar. Si bien en Excel 2007 o 2010 podemos anidar hasta 64 niveles de SI (en comparación a los 7 niveles en Excel Clásico), por encima de las 3 o 4 condiciones la fórmula se vuelve compleja y difícil de manejar.
Podemos superar este problema creando una fórmula con la función ELEGIR
=ELEGIR(CONTARA(A4:C4)+1,"","Presentado","En proceso","Aprobado")
Otro problema con estas fórmulas es que no toman en cuenta errores. Por ejemplo, que introduzcamos una fecha en “Proceso” sin que hayamos puesta una fecha anterior en “Presentación”.
Para superar este inconveniente podemos usar esta fórmula que combina ELEGIR con MMULT y una matriz de constantes
=ELEGIR(MMULT((A4:C4<>0)*1,{4;2;1})+1,"","Error 3","Error 2","Error 2","Presentado","Error 1","En proceso","Aprobado")
MMULT multiplica los miembros de la matriz (A4:C4<>0) por los valores de la matriz {4;2;1} determinando así el valor de la variable de ELEGIR. Este video muestra el proceso de cálculo de la fórmula cuando sólo la celda A4 contiene una fecha
También esta fórmula tiene un inconveniente. Es difícil de mantener, por ejemplo, si queremos agregar condiciones o cambiar valores.
La mejor solución, y que está inspirada en una respuesta de Ron Coderre en uno de los foros en los que participo, es usar una tabla auxiliar para determinar los valores y SUMAPRODUCTO con BUSCARV (CONSULTAV en Excel 2010)
La fórmula es (o parece!) sencilla
=CONSULTAV(SUMAPRODUCTO(((A4:C4)<>"")*{1,10,100}),$I$4:$J$11,2,0)
Como en la fórmula anterior, multiplicamos dos matrices para obtener un valor que usamos en CONSULTAV (o BUSCARV en versiones anteriores a Excel 2010) para obtener el texto correspondiente en Estatus
Para determinar los valores de la columna Indicador, usamos un sistema “pseudo-binario”. Esta imagen explica el método
Cada fila en la tabla es una de las posibilidades; en cada posibilidad indicamos con un 1 si la celda contiene una fecha; luego sumamos las celdas de la fila donde la columna Fecha 1 le asignamos el valor 1 (unidades), la columna Fecha 2 el valor 10 (decenas) y a la columna Fecha 3 el valor 100 (centenas).
El archivo con los ejemplos se puede descargar aquí.
Al introducir una fecha en la celda A4, la celda Estatus (D4) muestra la etapa en que se encuentra el proceso; al introducir una fecha en B4, el valor de Estatus cambia a “En proceso” y finalmente al introducir una fecha en C4, Estatus muestra “Aprobado”
La solución más inmediata es crear una fórmula con la función SI
=SI(A4<>"",SI(B4<>"",SI(C4<>"","Aprobado","En proceso"),"Presentado"),"")
Esta fórmula tiene varios problemas. El primero es que el uso de SI anidado nos pone serias limitaciones a la cantidad de condiciones que podemos procesar. Si bien en Excel 2007 o 2010 podemos anidar hasta 64 niveles de SI (en comparación a los 7 niveles en Excel Clásico), por encima de las 3 o 4 condiciones la fórmula se vuelve compleja y difícil de manejar.
Podemos superar este problema creando una fórmula con la función ELEGIR
=ELEGIR(CONTARA(A4:C4)+1,"","Presentado","En proceso","Aprobado")
Otro problema con estas fórmulas es que no toman en cuenta errores. Por ejemplo, que introduzcamos una fecha en “Proceso” sin que hayamos puesta una fecha anterior en “Presentación”.
Para superar este inconveniente podemos usar esta fórmula que combina ELEGIR con MMULT y una matriz de constantes
=ELEGIR(MMULT((A4:C4<>0)*1,{4;2;1})+1,"","Error 3","Error 2","Error 2","Presentado","Error 1","En proceso","Aprobado")
MMULT multiplica los miembros de la matriz (A4:C4<>0) por los valores de la matriz {4;2;1} determinando así el valor de la variable de ELEGIR. Este video muestra el proceso de cálculo de la fórmula cuando sólo la celda A4 contiene una fecha
También esta fórmula tiene un inconveniente. Es difícil de mantener, por ejemplo, si queremos agregar condiciones o cambiar valores.
La mejor solución, y que está inspirada en una respuesta de Ron Coderre en uno de los foros en los que participo, es usar una tabla auxiliar para determinar los valores y SUMAPRODUCTO con BUSCARV (CONSULTAV en Excel 2010)
La fórmula es (o parece!) sencilla
=CONSULTAV(SUMAPRODUCTO(((A4:C4)<>"")*{1,10,100}),$I$4:$J$11,2,0)
Como en la fórmula anterior, multiplicamos dos matrices para obtener un valor que usamos en CONSULTAV (o BUSCARV en versiones anteriores a Excel 2010) para obtener el texto correspondiente en Estatus
Para determinar los valores de la columna Indicador, usamos un sistema “pseudo-binario”. Esta imagen explica el método
Cada fila en la tabla es una de las posibilidades; en cada posibilidad indicamos con un 1 si la celda contiene una fecha; luego sumamos las celdas de la fila donde la columna Fecha 1 le asignamos el valor 1 (unidades), la columna Fecha 2 el valor 10 (decenas) y a la columna Fecha 3 el valor 100 (centenas).
El archivo con los ejemplos se puede descargar aquí.
Suscribirse a:
Entradas (Atom)