Como crear un icono para quitar Autofiltro

En la nota sobre los comandos personalizados para Autofiltro, mostré como agregar ciertos iconos que nos permitan ahorrar tiempo a quienes usamos Autofiltro con frecuencia.

Uno de estos iconos nos permite agregar el autofiltro con un solo clic

Para poner autofiltro a una tabla apretamos este icono. Sería muy conveniente que al apretar nuevamente este icono, quitemos el autofiltro. Es decir, que funcione como un interruptor. Eso es lo que sucede en Excel 2007, pero no en las versiones anteriores. Si queremos quitar el autofiltro tenemos que ir al menú Datos y hacer clic a Filtro.

En esta nota mostraré como crear una macro para cancelar el autofiltro y cómo ligarla a un icono, que pondremos en la misma barra de herramientas del icono de autofiltro.
Empecemos por la macro, que es muy sencilla. En un módulo, preferentemente del cuaderno Personal.xls, ponemos este código

Sub filter_off()
ActiveSheet.AutoFilterMode = False
End Sub

Ahora lo ligaremos a un icono, que luego pondremos en la barra de herramientas correspodiente. Empezamos por abrir el menú Herramientas-Personalizar y en el formulario ir a la pestaña Comandos y elegir la opción Macros

Arrastramos el botón con el “smiley” y lo ubicamos al lado del icono de Autofiltro (el embudo con el símbolo “=” a la izquierda)



Señalamos el icono con el mouse y pulsamos el botón derecho para abrir el menú



Le asignamos la macro, en la ventanilla Nombre ponemos “Cerrar Autofiltro” y seleccionamos la opción “Sólo texto (siempre)”



El resultado es un icono de texto. Al apretar este icono, si en la hoja hay una tabla con Autofiltro, éste será quitado.



Pero sería muy bueno si en lugar del icono de texto apareciera uno similar al de Autofiltro, por ejemplo, un embudo con un signo “=” pero cruzado por una X, es decir, esto



Excel nos provee con las herramientas necesarias para crear el icono. Empezamos por abrir el menú Herramientas-Personalizar y seleccionar (un solo clic) el icono del embudo. Abrimos el menú y apretamos Copiar imagen del botón



Ahora seleccionamos el icono de texto “Cerrar Autofiltro” y en el menú del botón apretamos Pegar imagen



A esta altura del partido tenemos la imagen del embudo junto al texto. Para deshacernos del texto usamos la opción “Estilo predeterminado” y luego elegimos la opción “Modificar imagen del botón”



Esto abre la esta ventanilla, donde podemos modificar la imagen.

En nuestro caso seleccionamos el color rojo (o cualquier otro que encuentren conveniente) en el cuadro de colores (negro es la opción por defecto) y marcamos una X señalando con el mouse los cuadrados correspondientes a la diagonal del cuadro de la imagen



Una vez concluida la tarea apretamos Aceptar. Ahora tenemos dos iconos, uno para poner el Autofiltro y otro para quitarlo



Esta nota tiene un cierto tinte de nostalgia anticipada, ya que las barras de herramientas y los botones han desaparecido de Excel 2007. Supongo que en no mucho tiempo, esta nota será caduca, pero mientras tanto podemos seguir disfrutando de la funcionalidad de las barras de herramientas y la posibilidad de crear botones personalizados.

Technorati Tags: MS Excel

Macro para intercambiar valores de un rango unidimensional

Excel permite transponer con facilidad los valores de un rango. Por ejemplo, si tenemos un rango vertical de valores como éste

y queremos convertirlo en un rango horizontal, usamos Copiar Pegado Especial-Transponer



¿Pero cómo hacemos para intercambiar los valores? Una posibilidad es arrastrando las celdas mientras mantenemos apretada la tecla Mayúsculas. Pero esto puede volverse muy tedioso y hasta irritante si se trata de un número de celdas considerable. Así que la solución que propondremos será con una macro.
Lo que queremos es que dado el rango A1:A5, obtener el rango C1:C5



La macro es la siguiente (nota: el uso de esta macro es sólo para rangos unidimensionales)

Sub swap_range()
Dim rngToSwap As Range, rngTarget As Range, cell As Range
Dim iX As Long, Counter As Long, HorVert As Integer
Dim arrval()

On Error GoTo err

Set rngToSwap = Application.InputBox _
(prompt:="seleccionar el rango", Type:=8)
Set rngTarget = Application.InputBox _
(prompt:="seleccione la primer celda para pegar", Type:=8)

HorVert = Application.InputBox( _
prompt:="Horizontal = 1;Vertical = 2", Type:=1)


Counter = rngToSwap.Count

ReDim arrval(Counter)

For iX = Counter - 1 To 0 Step -1
arrval(iX) = rngToSwap(Abs(iX - Counter)).Value
Next iX

Select Case HorVert
Case 1
Range(rngTarget, rngTarget.Offset(0, Counter)).Value = arrval
Case Else
Range(rngTarget, rngTarget.Offset(Counter, 0)).Value = _
WorksheetFunction.Transpose(arrval)
End Select

err:
Exit Sub

End Sub

Definimos tres variables que el usuario debe introducir usando Application.InputBox: el rango a intercambiar, la primer celda del rango objetivo y el sentido del rango a obtener (horizontal o vertical)



No sé hasta qué punto encontrarán útil esta macro. Sencillamente, me topé con este problema en una de mis tareas lo que me llevó a escribir la macro.

Technorati Tags: MS Excel

Ocultar #N/A en rótulos de datos en gráficos Excel

Supongamos que hemos representado los datos de la tabla en un gráfico, donde uno de los datos de la serie es un error #N/A

En nuestro gráfico hemos agregado rótulos para mostrar los valores de los puntos de la serie (las columnas). El problema es que no queremos que el resultado #N/A aparezca en el gráfico.

Si se trata de un gráfico “estático”, es decir que los datos no cambiarán, sería suficiente con seleccionar el rótulo del punto de la serie y borrarlo. Pero si se trata de un gráfico “dinámico”, es decir que los datos irán cambiando, tendremos que encontrar una solución también dinámica.

El contenido del rótulo está ligado a los datos de la tabla, por lo cual deberemos actuar sobres éstos. Una posibilidad es agregar una columna auxiliar para hacer una pequeña transformación de los datos: cuando un dato de #N/A lo transformamos en vacío (“”). Para eso usamos la fórmula

=SI(ESNUMERO(B2),B2,"")

Ahora cambiamos la referencia de la serie de datos al rango C2:C6

Si bien evitamos el #N/A, aparece en su lugar un 0, cuando nuestro objetivo es que no aparezca nada




Para lograrlo tendremos que cambiar el formato de número en el rango C2:C6. Usamos el formato personalizado

#;-#;;




Los formatos de números tienen la siguiente estructura:

Formato de números positivos; formato de números negativos; formato de ceros; formato de texto.

No hemos definido ningún formato para los ceros (no hay ningún parámetro entre el segundo y tercer “;”), por lo que los valores cero (y los vacíos) no son representados. Los valores en el gráfico “heredan” esta característica.

Technorati Tags: MS Excel

Ocultar filas con valor cero en tablas dinámicas.

Al trabajar con tablas dinámicas puede suceder que queramos ocultar filas cuyo valor es 0 (cero). La forma de hacerlo depende de la estructura de los datos que alimentan la tabla dinámica.

Si no hay valores repetidos en el área de las filas, como en este caso



Seguimos los siguientes pasos:

1 – Creamos la tabla dinámica y agregamos el campo “Valor” en el área de página



2 – Seleccionamos el campo de página y abrimos el menú de configuración de campo



3 – En la ventanilla “ocultar elementos” marcamos el 0 y apretamos “Aceptar”. Las filas de los elementos con valor 0 no aparecen en la tabla.



Esta técnica no funciona si hay elementos repetidos en el área de las filas, como en este caso



En este caso agregamos una columna auxiliar con esta fórmula

=--SI(SUMAR.SI($A$24:$A$35,A24,$B$24:$B$35)<>0,1)



Esta fórmula evalúa el total de cada grupo de valores para cada línea del grupo que totalice 0 da 0; en caso contrario 1.

Arrastramos el campo Auxiliar al área de página y seleccionamos 1



Las filas que totalicen 0 desaparecen.

Technorati Tags: MS Excel

Hipervínculo a una dirección de correo Outlook

Siguiendo en el tema de los hipervínculos, respondemos hoy a la consulta de un de mis lectores: ¿Cómo puedo hacer para crear un hipervínculo a una dirección de correo Outlook dependiendo del valor de una celda con una lista validada?

EL tema es más sencillo de lo que parece en principio, pero hay un pequeño problema por resolver, como en casi todos los aspectos de nuestras vidas.

Empecemos por notar que cuando ponemos en una celda de Excel un texto que sigue el patrón de una dirección de correo electrónico, Excel agrega automáticamente un hipervínculo de tipo “mailto:xxx@yyy.zzz”





Como ya sabemos, podemos crear una lista desplegable usando validación de datos. Para eso creamos una lista de direcciones de mail y le damos un nombre definido. Para más comodidad definimos un rango dinámico con DESREF



Luego creamos la lista desplegable con validación de datos-lista



Ponemos la lista desplegable en la celda B1, de manera que podamos elegir la dirección



El problema con este método, es que a diferencia de cuando introducimos la dirección manualmente, Excel no crea el hipervínculo



Existen dos remedios a esta situación:

1 – después de seleccionar el nombre apretamos F2 (edición) e inmediatamente después Enter.

2 – Creamos un evento de manera que las acciones señaladas en 1 sucedan automáticamente. En el módulo Vba de la hoja1 (en nuestro ejemplo) ponemos este código

Private Sub Worksheet_Change(ByVal Target As Range)

If Union(Target, [B1]).Address = [B1].Address Then
SendKeys "{F2}"
SendKeys "{Enter}"
Application.EnableEvents = False
End If
Application.EnableEvents = True

End Sub

La sentencia SendKeys imita el tecleado de F2 y Enter si la variable Target coincide con la celda que hemos definido como celda que contiene la dirección.





Technorati Tags: MS Excel

Hipervínculo a una celda específica en Excel.

En la nota sobre hipervínculos en Excel vimos como crearlos en forma dinámica. En ese ejemplo mostrábamos como hacer que al elegir un valor de una lista desplegable, el hipervínculo se dirigiera a la primer celda vacía de una columna en una hoja determinada.

También podemos hacer que el vínculo se ligue a una celda determinada de un rango. Por ejemplo, supongamos que tenemos un cuaderno con una lista de clientes en una hoja (para el caso, “clientes”). En otra hoja ponemos una lista desplegable (con validación de datos) que muestre todos los nombres de los clientes. Queremos que al elegir un cliente, la celda que contiene el hipervínculo se actualice y que al pulsarlo nos lleve a la fila de cliente.

Como en el caso anterior, usamos la función HIPERVINCULO. La fórmula es sencilla, pero aprovecharemos la ocasión para mostrar también una técnica para construir fórmulas no tan sencillas.

En la hoja “clientes” tenemos esta lista





Empezamos por crear un rango dinámico con un nombre definido





La fórmula del nombre definido es
=DESREF(Clientes!$A$2,0,0,CONTARA(Clientes!$A:$A)-1,1)

Esta fórmula nos permite que el rango definido por el nombre se vaya adaptando a medida que agregamos o quitamos clientes de la lista.

En otra hoja creamos la lista desplegable basándonos en el nombre definido




En la celda contigua queremos poner la fórmula que cree el hipervínculo al cliente según se haya elegido en la lista desplegable.

Para encontrar en fila del rango de los clientes se encuentra el elegido usamos la función COINCIDIR de esta manera

=COINCIDIR(A1,Clientes,0)+1





Usamos el nombre definido “clientes” pero agregamos 1 al resultado ya que el nombre no toma en cuenta la primer fila del rango (el encabezamiento de la columna A).

Ahora podemos crear con facilidad la dirección de la celda buscada con la función DIRECCION

=DIRECCION(B1,1,,,"clientes")




Podemos usar el resultado en la celda B4 como argumento en la función HIPERVINCULO que pondremos en la celda B1

=HIPERVINCULO("#"&B4,"Ver "&A1)



Pero esto nos obligaría a usar celdas auxiliares. Sencillamente podemos reemplazar la referencia en la función por las fórmulas que hemos usado, quedando

=HIPERVINCULO("#"&DIRECCION(COINCIDIR(A1,Clientes!A:A,0),1,,,"clientes"),"Ver "&A1)






La pregunta que se impone ahora es: ¿cómo hacemos para que al elegir el cliente en la celda A1 Excel se dirija automáticamente a la celda correspondiente sin necesidad de pulsar en B1?

Bien, este será el tema de una futura nota.


Technorati Tags: MS Excel

Como contactarse con JLD Excel Blog

Este blog se nutre, entre otras cosas, de los comentarios a las notas y en particular de las consultas que recibo en mi casilla de correo.

Hoy en día (Febrero de 2009) este blog recibe aproximadamente más 40 mil visitas por mes (entre 80000 a 100000 páginas vistas por mes) y espero que el tráfico siga creciendo. Como seguramente comprenderán, la cantidad de mails que recibo crece en relación al tráfico en el blog. Si bien esto me llena de satisfacción, no tengo hoy en día ninguna posibilidad de responder a todas las consultas.

Antes de enviar un mail, les ruego tomar en cuenta estos criterios:

• Muchas veces es más eficiente consultar un foro en la red, de donde recibirán muchas respuestas y podrán elegir la que mejor resuelva la consulta. El tiempo que puedo dedicar a cada consulta es muy limitado, tomando en cuenta que este blog es mi pasatiempo.

• Si deciden enviarme una consulta ésta debe ser precisa, no formulada en términos generales (“¿cómo hacer un sistema de contabilidad para una pequeña empresa en Excel?”). La consulta debe centrarse sobre un único problema que no logran resolver.

• De ser posible hay que adjuntar un archivo con el ejemplo de lo que quieren resolver. La descripción del problema en el cuerpo del mail debe ser lo más concisa posible.

• Los archivos adjuntos deben ser Excel; las explicaciones pueden agregarse en cuadros de texto. No enviar imágenes o archivos PDF.

• El mail tiene que estar escrito de manera inteligible e invirtiendo algún esfuerzo en evitar errores de ortografía. Por favor evitar abreviaciones como “k” en lugar de “que” y otras por el estilo.

• Ayuda en desarrollo de modelos y todo lo que exceda una cantidad razonable de tiempo para su respuesta, será considerado como un pedido de presupuesto para el desarrollo profesional del modelo. Por supuesto, pueden dirigirse a mí sin ningún compromiso.

La dirección de mail para contactarme: jorgedun@gmail.com

Catálogo de imágenes en Excel 2007

En el pasado hemos mostrado cómo crear un catálogo de imágenes con Excel. El modelo fue desarrollado usando Excel 2003.

Al tratar de crear el modelo usando Excel 2007 vimos que el paso 5, ligar el nombre definido “imagen” en la barra de las fórmulas a la imagen no era posible.

Una solución posible es crear el modelo en Excel 2003 y luego abrir el archivo en Excel 2007. Pero era necesario dar una solución más directa.

En esta nota mostraremos dos soluciones posibles.

Empecemos por describir el problema. Si seguimos las instrucciones de la nota mencionada llegamos al #5 donde ligamos el nombre definido “imagen” a la imagen que pegamos en una celda. Esto lo hacíamos seleccionando la imagen y luego haciendo un clic en la barra de fórmulas para poner “=imagen”. Excel 2007 no nos permite hacer esto. La barra de las formulas no responde cuando tenemos seleccionada una imagen.

La forma de hacerlo en Excel 2007 es con una de estas dos técnicas:

# Usando el objeto Image Bitmap (propuesta por el lector Percy Herrera en uno de los comentarios de la nota). Los pasos son:

1 – en lugar de pegar la imagen en la celda, abrimos el menú Objeto en la pestaña Insertar de la cinta






El interfaz de Excel cambia



Todo lo que hacemos es un clic en cualquier lugar de la hoja, fuera del área de la imagen que Excel acaba de crear.

2 – Ajustamos el cuadro que Excel ha creado para que coincida con los bordes de la celda.

3 – En la barra de fórmulas aparece “=INCRUSTAR(…”




Reemplazamos esta fórmula por “=imagen”






Al apretar Enter, aparecerá la imagen cuya referencia (número de catálogo en nuestro caso) aparece en la celda A3





# Usando el control Imagen de la barra de controles.

1 – En la pestaña Programador de la cinta elegimos el control Imagen y lo ubicamos sobre la celda B3





El resultado es similar al de la técnica anterior





2 – Como en el caso anterior, reemplazamos “=INCRUSTAR(…” por “=imagen”, con el mismo resultado.





Technorati Tags: MS Excel

Referencias remotas en validación de datos

Una de las consultas frecuentes que recibo es si se puede crear una validación de datos en una hoja de un cuaderno de Excel usando una lista (es decir, un rango) que se encuentra en otro cuaderno.

La respuesta es si, con un poco de trabajo manual. Veamos este ejemplo: tenemos un cuaderno que hemos guardado con el nombre "clientes.xls" donde tenemos una lista de clientes. En un segundo cuaderno, "validación.xls", queremos crear una lista desplegable con validación de datos, basándonos en la lista del primer cuaderno.
Para facilitar la visualización he puesto ambos cuadernos en una ventana usando Ventana-Comparar en Paralelo




En el cuaderno clientes.xls definimos el nombre "clientes" que contiene el rango A2:A92



Ahora pasamos al cuaderno validación.xls y creamos el nombre "clientes" con esta definición

=clientes.xls!Lista_de_Clientes



Hay que prestar atención al signo de exclamación entre el nombre del cuaderno remoto y el nombre que hemos definido en él.

Ahora creamos la lista desplegable en la hoja1 del cuaderno validación.xls usando el nombre que acabamos de definir ("clientes")



Ahora podemos ver que la lista desplegable en validación.xls usa los valores del rango definido en el cuaderno clientes.xls.



Para que esta lista funcione ambos cuadernos deben estar abiertos.

Technorati Tags: MS Excel