listas desplegabl

Download Listas Desplegabl

Post on 20-Jun-2015

652 views

Category:

Documents

1 download

Embed Size (px)

TRANSCRIPT

JLD Excel en Castellano - Usar Microsoft Excel eficientemente: Listas Desplegables

Page 1 of 17

JLD EXCEL EN C ASTELLANO - USAR MICROSOFT EXCEL EFICIENTEMENTESugerencias y ayuda para Excel en espaol. Ejemplos de funciones y frmulas, grficos y automatizacin de hojas de clculo con Vba.Inicio Acerca de JLD ExcelAnuncios Google

AyudaExcel

ConsultoraFacturas En Excel

Apoyar JLD ExcelHoja De Excel Excel 2007 Excel

TRADUCIR ESTA PGINAAnuncios Google Excel 2010 Excel Formulas Realizar Hoja Crear Hoja Autofiltro Comparar Listas Consolidar Datos Dashboards Excel 2007 Excel

2010

Fechas y

Seleccionar idiomaCon la tecnologa de

hora Formato Condicional Funciones y Formulas Graficos Importar Datos Listas Desplegables Macros Tablas Dinamicas Validacion de Datos Varios

Traductor de Google

BUSCAR EN EL BLOGBuscar

Mostrando las entradas ms recientes con la etiqueta Listas Desplegables Mostrar las entradas ms antiguas

Bsqueda personalizada

Listas desplegables dependientes rangos en filasSBADO, MAYO 01, 2010

Entre su e-mail para suscribirse:

http://jldexcelsp.blogspot.com/2008/08/listas-desplegables-dependientes.htmlEl tema de crear listas desplegables dependientes ha sido tratado abundantemente en este blog. Ni por casualidad ni por capricho, sino por ser uno de los temas ms consultados por mis lectores. Un lector, cuyo mail he borrado por error y espero que lea esta nota en algn momento, me comentaba que no lograba crear listas dependientes cuando los valores estaban ubicados en un rango horizontal (filas) en lugar de vertical (columnas) tal como muestro en el ejemplo de esta nota. Como en el caso anterior, el problema reside en el hecho que la funcin INDIRECTO slo funciona con texto. Cuando queremos crear una lista de valores desplegable en Excel, la opcin Lista de validacin de datos es la alternativa ms fcil. Veamos el caso. Los nombres de los agentes de cada zona de una empresa aparecen en esta hoja en rangos horizontalesSuscribirse a un lector de feeds

SuscribirseDelivered by FeedBurner

PUBLICIDAD

Empezamos por crear el nombre que contiene el rango vertical de las zonas

ARCHIVO DEL BLOGArchivo del blog

Ahora asignamos la primer celda de la cada celda del la columna C al nombre de cada zona. Es decir

RECOMENDADO SEconoma Excel - en

http://jldexcelsp.blogspot.com/search/label/Listas%20Desplegables

03/05/2010

JLD Excel en Castellano - Usar Microsoft Excel eficientemente: Listas Desplegables

Page 2 of 17

Castellano

Norte = $C$3 Sur=$C$4 Este=$C$5 Oeste=$C$6

Soluciones para hojas Excel Contextures Chip Pearson Decision Models

ENLAZAR

Enlaza mi Blog

ESTADSTICAS

En la celda B9 de la misma hoja ponemos la lista desplegable de las zonas

En la celda B10 creamos la lista desplegable dependiente de los agentes con esta frmula =DESREF(INDIRECTO($B$9),0,0,1,CONTARA(INDIRECTO(FILA(INDIRECTO($B$9)) &":"&FILA(INDIRECTO($B$9))))-1)

http://jldexcelsp.blogspot.com/search/label/Listas%20Desplegables

03/05/2010

JLD Excel en Castellano - Usar Microsoft Excel eficientemente: Listas Desplegables

Page 3 of 17

Como ven, combinamos INDIRECTO y FILA para "construir" el texto que representa el rango de los nombres de cada zona, en forma dinmica. Esta solucin tiene un grave problema: si ponemos las listas desplegables en otra hoja, la frmula no funcionar correctamente. Aqu nos enfrentamos con dos problemas: 1 tenemos que crear una referencia a la hoja que contiene los valores de las listas (en nuestro ejemplo, la Hoja1) 2 en Validacin de datos no podemos usar referencias a hojas remotas directamente; tenemos que "encapsularlas" en un nombre. Por estos motivos creamos el nombre "lista_dependientes" que se refiere a esta frmula: =DESREF(INDIRECTO(Hoja2!$B$3),0,0,1,CONTARA(INDIRECTO("Hoja1!"&FILA(INDIRECTO (B3))&":"&FILA(INDIRECTO(B3))))-1) En esta frmula creamos el texto de la referencia en la funcin CONTARA poniendo en forma explcita el nombre de la hoja que contiene las listas de valores (Hoja1). Ahora podemos ser fieles al principio de separar los datos de los reportes y usar la lista dependiente en la Hoja2

El archivo del ejemplo se puede descargar aquSeguir leyendo...

Posted by Jorge L. Dunkelman at 4:20 PM , Links to this post , 4 comments Labels: Listas Desplegables, Validacion de Datos

Rangos dinmicos con la funcin INDIRECTO de Excel.SBADO, ENERO 02, 2010

Excel permite construir rangos dinmicos, tema que ya hemos tratado en diversas oportunidades en este blog. Rangos dinmicos son aquellos cuya referencia (direccin) se expande o contrae con los cambios en el nmero de miembros del rango. Estos rangos se definen con frmulas, por lo general con la funcin DESREF. Tambin hemos visto que podemos referirnos dinmicamente a un rango usando su nombre como argumento de la funcin INDIRECTO, por ejemplo cuando creamos listas desplegables dependientes. Pero esto genera un problema con los rangos dinmicos: INDIRECTO no acepta frmulas como argumentos, slo texto. En esta nota veremos un rodeo sencillo a este problema, sin usar macros o funciones definidas por el usuario.

Seguir leyendo...

Posted by Jorge L. Dunkelman at 10:04 PM , Links to this post , 15 comments Labels: Listas Desplegables, Validacion de Datos

Listas desplegables en Excel con ajuste automtico.SBADO, OCTUBRE 17, 2009

http://jldexcelsp.blogspot.com/search/label/Listas%20Desplegables

03/05/2010

JLD Excel en Castellano - Usar Microsoft Excel eficientemente: Listas Desplegables

Page 4 of 17

En Excel es my fcil crear listas desplegables. El mtodo ms prctico es usando Validacin de Datos Listas. En este blog hemos tratado el tema de diversas oportunidades y hemos mostrado tambin como crear listas desplegables dependientes. En relacin a este tema recibo a menudo consultas sobre cmo ir eliminando de la lista los valores que ya han sido seleccionados. Supongamos que estamos organizando en que mesa se sentarn los invitados a una cena o recepcin. Para el caso supongamos una lista de 16 invitados que habr que sentar en cuatro mesas, cuatro en cada una. Empezamos por poner la lista de invitados en una hoja y una tabla que representa las mesas y sillas en otra

El objetivo es crear una lista desplegable que pondremos en cada celda del rango C3:F6 (rango que hemos puesto en el nombre mesas) de manera que cada vez que ubiquemos un comensal en la tabla ste desaparezca de la lista. Mostraremos dos posibilidades: con columnas auxiliares y con frmulas matriciales. Con columnas auxiliares. En la hoja que contiene la lista de invitados creamos una columna auxiliar con el rtulo Por ubicar

http://jldexcelsp.blogspot.com/search/label/Listas%20Desplegables

03/05/2010

JLD Excel en Castellano - Usar Microsoft Excel eficientemente: Listas Desplegables

Page 5 of 17

El objetivo de esta columna es controlar que invitado ya ha sido ubicado en alguna mesa. Para eso usamos la frmula =SI(CONTAR.SI(mesas,C2)=1,"",C2) Por ejemplo, si ubicamos a Carla y a Federico en la matriz de las mesas, obtenemos

Ahora agregamos una nueva columna auxiliar (Aux1)

http://jldexcelsp.blogspot.com/search/label/Listas%20Desplegables

03/05/2010

JLD Excel en Castellano - Usar Microsoft Excel eficientemente: Listas Desplegables

Page 6 of 17

La frmula =CONTAR.SI($D$2:$D$17,"0")

Ahora creamos la columna auxiliar Aux2 en el rango F2:F17 con esta frmula =K.ESIMO.MAYOR($E$2:$E$17,$J$1-FILA()+2)

Esta columna nos sirve como argumento para la columna final, en el rango G2:G17, donde obtenemos los valores de la lista desplegable

http://jldexcelsp.blogspot.com/search/label/Listas%20Desplegables

03/05/2010

JLD Excel en Castellano - Usar Microsoft Excel eficientemente: Listas Desplegables

Page 7 of 17

Para evitar que en la lista desplegable aparezcan los valores #NUM de las ltimas dos celda, usamos el valor de la celda control para crear un rango dinmico que las excluya con la frmula =DESREF(invitados!$G$2,0,0,invitados!$J$1,1)

Aplicamos esta validacin de datos al rango mesas.

A medida que vamos ubicando a los invitados, la lista se va ajustando y mostrando slo aquellos que quedan por ubicar

http://jldexcelsp.blogspot.com/search/label/Listas%20Desplegables

03/05/2010

JLD Excel en Castellano - Usar Microsoft Excel eficientemente: Listas Desplegables

Page 8 of 17

Con frmulas matriciales. Al igual que en el modelo anterior creamos la lista de los invitados a ubicar en el rango D2:D17. La lista de invitados a ubicar la creamos en el rango E2:E17 con esta frmula matricial ={INDICE($D$2:$D$17,COINCIDIR(K.ESIMO.MAYOR(CONTAR.SI ($D$2:$D$17,"