Usos de Autofiltro – Copiar a rangos no continuos

Hace unos días atrás, Rob van Gelder plantea el siguiente problema en Daily Dose of Excel:

tenemos una tabla con líneas en blanco que separan distintos grupos de datos. Queremos agregar una columna que contiene una fórmula, pero al copiar la fórmula a todo el rango se producen resultado "error". Esta imagen ejemplifica el problema:



La solución más inmediata es seleccionar las celdas con resultados erróneos y borrar el contenido.
Pero supongamos que se trata de una tabla con 1000 filas. Evidentemente la tarea llevaría muchísimo tiempo (1).
La misión es crear una selección lo continua de celdas en una columna dónde copiaríamos la fórmula.
Rob van Gelder propone una macro como solución. Nosotros mostraremo como hacer esto con Autofiltro.

Partimos de la tabla original, sin fórmulas en la columna E. Si aplicamos Autofiltro sin más trámite, Excel mostrará solamente los valores hasta la primer fila en blanco.
Para "obligar" a Excel a considerar toda la tabla, empezamos por seleccionar todo el rango de las columnas A hasta E y luego aplicamos Autofiltro



Ahora filtramos la tabla de acuerdo a la columna B, "no vacías"



Excel oculta las filas vacías



Seleccionamos el rango relevante en la columna E y aplicamos la fórmula



Todo lo que nos queda por hacer es anular el autofiltro



Obtenemos el mismo resultado, sin aplicar macros.

(1) En realidad podríamos seleccionar de una vez todas las celdas con resultado #DIV/0! usando Ir A --Especial, como ya hemos mostrado, y borrar el contenido en una sola operación. Si usamos alguna fórmula que no divida por una celda vacía no recibiremos resultado #DIV/0 y el método mostrado en esta nota sería el ideal.

Technorati Tags: MS Excel

Vinculos entre hojas Excel

Esta nota viene a colación de una pregunta de un compañero de trabajo sobre los vínculos entre hojas de distintos cuadernos en Excel.

Excel permite crear vínculos entre celdas de distintos cuadernos. Esto permite usar cuadernos de Excel en forma similar a una base de datos. Por ejemplo, en un cuaderno tenemos una lista de productos con sus respectivos precios; en una hoja de otro cuaderno manejamos el inventario de los productos. Podemos crear un vínculo en el cuaderno del inventario de manera que cada vez que actualicemos los precios, el valor del inventario se actualice.

Estos vínculos pueden funcionan también si el libro de origen (la lista de pecios en nuestro caso) está cerrado. Pero Excel también nos permite decidir si queremos que está actualización se haga en forma automática o no.

Veamos todo esto con un ejemplo. Supongamos esta lista de precios (de la base da datos NorthWind que viene con el paquete Office)



En una hoja de otro cuaderno tenemos una lista de inventario, como esta


Para crear vínculos a la lista de precios podemos:

1 – copiar el precio del producto y pegarlo en la lista de inventario con Pegar Vínculos


2 – o sencillamente seleccionar la celda dónde queremos crear el vínculo, en la barra de fórmulas introducir el signo "=", pasar al cuaderno de origen (Lista de precios), apuntar a la celda correspondiente y aceptar.

En ambos casos Excel crea un vínculo al cuaderno de origen, que aparece en la barra de fórmulas entre corchetes:


Cuando el libro de origen está cerrado, la referencia en la barra de fórmulas incluirá también la ubicación del archivo


La forma en que Excel maneja la actualización de los vínculos puede ser controlada a través del menú Herramientas—Opciones—Modificar


Si marcamos la opción Consultar al actualizar vínculos automáticos, cuando abramos el cuaderno que contiene el vínculo, veremos este mensaje


En caso contrario, Excel actualiza los vínculos automáticamente.

Cuando ambos cuadernos están abiertos, cambios en la celda de origen se reflejan inmediatamente en la celda que contiene el vínculo.

También podemos manejar los vínculos mediante el menú Edición--Vínculos


Por ejemplo, si queremos romper todos los vínculos en una sola operación, usamos el botón Romper vínculos. Al usarlo recibimos esta advertencia, ya que no es posible deshacer esta operación


Para romper el vínculo de una celda solamente, podemos usar Pegado Especial--Valores

Si esto sucede estando el cuaderno vinculado cerrado, al abrirlo recibimos un mensaje que el cuaderno no puede actualizarse


Si elegimos actualizar los vínculos, recibimos otro mensaje


Al apretar el botón Modificar se abrirá el diálogo de Modificar vínculos que nos permitirá crear el vínculo al archivo relevante.


Technorati Tags: MS Excel

Autofiltro avanzado en Excel

Supongo que la gran mayoría de mis lectores conocen la funcionalidad Autofiltro de Excel. Autofiltro nos permite "filtrar" una lista de acuerdo a uno o más criterios que podemos aplicar a una o más columnas de una lista.
Para ejemplificar el uso he importado una de las tablas de la base de datos Northwind que viene con el paquete Office de Microsoft.



Si queremos filtrar la lista para que se vean sólo los productos clasificados como bebidas (Beverage), seleccionamos una de las celdas de la tabla, y en menú Datos seleccionamos Autofiltro



Para filtrar la lista seleccionamos de la lista desplegable el criterio requerido



El resultado es



También podemos filtrar usando criterios "personalizados". Por ejemplo, todos los productos cuyo precio por unidad sea superior a 20. Abrimos la lista desplegable y elegimos Personalizar



En el diálogo que se abre elegimos "es mayor que" y fijamos el valor 20 en la ventanilla correspondiente



y pulsamos Aceptar



Podemos extender el poder de esta funcionalidad combinando filtro de varias columnas. Por ejemplo todas las bebidas cuyo precio sea mayor a 20




Para eliminar la selección podemos pulsar la flecha de la columna filtrada (que aparece en azul) o usar el menú Datos—Autofiltro.

La posibilidad de personalizar el filtro nos provee de una gran flexibilidad para filtrar nuestras listas.
Supongamos que queremos, por algún oscuro motivo, filtrar la lista para que muestre sólo las filas donde la cantidad por unidad (la columna QuantityPerUnit) empieza con "12". Usamos Personalizar con este criterio



El resultado



Como ven, Personalizar en Autofiltro ofrece muchas posibilidades. De



hasta



También podemos aplicar "wildcards" como * o ?. Por ejemplo, mostrar sólo las filas que tengan el número 12 en la columna QuantityPerUnit en la posición 6 y7. Usamos Personalizar con 5 signos ?, el numero 12 y un asterisco



También podemos usar



Obtenemos



Por último (en lo que respecta a esta nota) podemos combinar criterios dentro de una misma columna usando las posibilidades Y y O del diálogo. Es de notar que sólo podemos combinar dos condiciones.
Otra limitación de Autofiltro es que sólo puede mostrar 1000 registros en las listas desplegables de los encabezamientos.





Technorati Tags: MS Excel