domingo, julio 30, 2006

Valores unicos en una lista de Excel

En la entrada de ayer mostrábamos como generar una lista de valores únicos con validación de datos. La lista de validación de datos se actualizaba a medida que se agregaban nuevos datos en el rango seleccionado. Para lograrlo usamos fórmulas bastante complicadas y tablas auxiliares.

En realidad hay una solución mucho más sencilla para esta tarea. Excel produce este tipo de listas en forma automática. Mostraré esto con un ejemplo.

Supongamos la lista de frutas del ejemplo de ayer




Si después de seleccionar la celda A11 pulsamos simultáneamente las teclas ALT y flecha hacia abajo, se abrirá una lista desplegable que contiene valores únicos de la lista



Utilizando las flechas o moviendo mouse podemos elegir el valor deseado. Para aceptar el valor podemos pulsar el botón derecho del mouse o apretar Enter.




Categorías: Varios_

Technorati Tags: ,

sábado, julio 29, 2006

Validación de Datos en Excel - Agregar valores únicos a la lista desplegable

Todo usuario que lleve algún tiempo trabajando con Excel, conoce la funcionalidad Validación de Datos.
Esta funcionalidad permite controlar el tipo de datos que son introducidos en un rango determinado. Existen varias posibilidades






Las más interesantes, en cuanto a posibilidades y flexibilidad, son Lista y Personalizada.
Lista permite generar una lista desplegable de la cual puede el usuario elegir valores válidos.
Ya hemos mostrado diferentes técnicas para poblar la lista, en especial haciendo uso de nombres, para referirnos a rangos que se encuentran en otra hoja y para establecer referencias dinámicas.
En una entrada anterior mostraba como crear listas desplegables dependientes. Es decir, que los valores de una lista de validación de datos dependan de la elección de un determinado valor en otra lista.

Hoy veremos como agregar valores únicos a una lista desplegable de validación de datos.
El archivo con el ejemplo se puede descargar aquí.
La técnica consiste en generar una lista de valores únicos en un rango de alguna hoja, que será la referencia de la lista de validación de datos.
Para lograrlo usamos una tabla auxiliar con dos rangos.


En el rango "Valores únicos" usamos la fórmula =SI(CONTAR.SI($A$5:A6,A6)=1,A6,"")
Esta fórmula nos permite establecer si un valor aparece más de una vez. Es importante prestar atención a la definición del rango $A$5:A6, donde la primer celda tiene una referencia absoluta y la segunda una referencia relativa.

En el rango "No. De Orden" generamos un número que nos servirá de argumento en la fórmula que generará la lista de valores únicos.
=SI(CELDA("contents",C5)="","",FILA(C5))
El número es el número de fila sólo par los valores que aparecen por primera vez. Esta fórmula usa como argumento el resultado de la fórmula del rango "Valores únicos".

En el rango "Lista" usamos la fórmula

INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25))

para hacer aparecer los valores que tiene número de orden.
Para evitar resultados #¡NUM!, agregamos una condición (en color verde) para evaluar resultados de error

=SI(ESERROR(INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25))),"",INDICE($C$5:$C$25,COINCIDIR(K.ESIMO.MENOR($D$5:$D$25,FILA()-4),$D$5:$D$25)))

Una explicación detallada de las fórmulas se puede leer aquí.


Categorías: Funciones&Formulas_, Varios_

Technorati Tags:

viernes, julio 21, 2006

Ocultar hojas de calculo en un cuaderno Excel

A veces queremos ocultar una de las hojas de un cuaderno Excel. La forma corriente de hacerlo es mediante el menú Formato---Hoja---Ocultar.




Si queremos prevenir que algún usuario vuelva a hacer visible la hoja, podemos proteger el cuaderno (libro) con el menú de protección y usar una contraseña.



Es sabido que las contraseñas de Excel son muy fáciles de romper. Por lo tanto, este método de ocultar hojas no es muy seguro.

De hecho no existe ningún método seguro, pero si podemos aprovechar una característica que la mayoría de los usuarios de Excel desconocen.


La Hoja de Excel cuenta con una serie de propiedades. Una de ella es la propiedad "Visible". Esta propiedad puede tener uno de tres valores: "xlSheetVisible", "xlSheetHidden" o "xlSheetVeryHidden".



Hojas ocultas con la propiedad xlSheetHidden (la que aplica Excel cuando usamos el menú Formato) pueden hacerse visible con el menú Formato---Hojas---Mostrar. Hojas ocultas con la propiedad xlSheetVeryHidden no aparecen en este menú y por lo tanto serán invisibles para la mayoría de los usuarios regulares de Excel.

Para cambiar la propiedad de la hoja debemos acceder al cuadro de propiedades de esta.
Ya hemos visto que uno de los caminos de hacer visible el cuadro de propiedades es hacer visible la barra de herramientas del Cuadro de Controles y allí pulsar el botón Propiedades




Otra variante es abrir el editor de Visual Basic (Herramientas---Macros---Editor de Visual Basic) y allí cliquear la Hoja1 (o la que queramos ocultar) para activar la ventana de Propiedades



si la ventana no aparece usamos el menú View---Properties Window en el menú del editor.

En ambos casos procedemos de la misma manera. Abrimos la lista desplegable en la ventanilla de la propiedad Visible, y señalamos el valor "2 – xlSheetVeryHidden"




Para mostrar nuevamente la hoja, sólo podemos utilizar la ventanilla de propiedades del editor de Visual Basic y devolver el valor de la propiedad Visible a xlSheetVisible nuevamente.


Categorías: Varios_

Technorati Tags: