Listas dependientes de validación de datos de Excel con tablas e INDIRECTO (2023)

Introducción

Ahora, nunca he sido fanático de ninguna de las funciones volátiles de Excel, como Indirect(), Offset(), etc. Son extremadamente poderosas, pero en hojas de trabajo muy grandes con muchas funciones volátiles en uso, he conocido casos en los que hacer cualquier la entrada de datos o la modificación de la hoja prácticamente pueden "poner de rodillas a Excel", ya que el motor de cálculo se activa para recalcular todas las dependencias de estas funciones volátiles.

A veces, la única forma de trabajar, en estos escenarios, es configurar el modo de cálculo en Manual y, después de realizar todas las entradas o modificaciones, presionar F9 para forzar la secuencia de cálculo solo una vez.

Como resultado, mi preferencia personal es mantenerme alejado de estas funciones volátiles por completo, y rara vez no he podido encontrar una manera de lograr lo mismo que Offset(), por ejemplo, usando una combinación de Index() con Coincidencia() o Cuenta().

Sin embargo, para las hojas pequeñas estuve jugando recientemente con la Validación de datos, usando Tablas para contener las listas de validación, y encontré un escenario en el que Indirect() se puede usar con gran efecto.

Uso de tablas

El advenimiento de Tables en Excel 2007 como sucesor del objeto List de Excel 2003, y su posterior desarrollo en Excel 2010 y 2013, significa que siempre que sea posible, ahora siempre uso Tables en lugar de listas de datos ordinarias. La única excepción es cuando quiero que los encabezados de mis columnas sean dinámicos y se basen en una fórmula, en lugar de texto escrito, y esto no está permitido con las tablas.

Con las tablas, puede tener tantas como desee en la misma hoja de cálculo. Siendo este el caso, puede tener sus entradas de validación de datos en una serie de tablas de una sola columna en una hoja, en lugar de tenerlas como columnas separadas en una sola tabla.

Mesa individual

La tabla anterior se ha configurado como una sola tabla con un tamaño de $D$7:$R$18, pero esto, por supuesto, se ajustará si agregamos más filas y/o columnas. Esta tabla la nombré como tblVal en lugar del nombre predeterminado de Table1.

Mesas Individuales

En la segunda ilustración, cada columna es una tabla por derecho propio. Puede dejar espacios entre las columnas si lo desea, como se resalta con la columna F, o pueden ser todas contiguas como las columnas J a S.

Cuando configuré cada tabla, le di el mismo nombre que el encabezado en la primera fila de la tabla, por lo que la primera tabla se llama Región y tiene un tamaño de $E $ 7: $ E $ 10, la cuarta tabla I llamado Europa, y tiene un tamaño de $J$7:$J$15

Ahora, la gran ventaja de tener una Tabla de una sola columna, es que el número de filas es independiente en cada tabla, mientras que cada columna de la tabla combinada tiene que tener la misma longitud, por lo que hay entradas en blanco en algunas columnas.

Cuando tenemos una lista de validación de datos, queremos que solo muestre la lista de entradas válidas y no muestre filas en blanco. Usando una sola tabla, necesitamos determinar cuántas filas se usan en cada columna, para definir el rango que queremos presentar en nuestra lista de Validación de datos. (Cubro esto enotro articulo.)

Desafortunadamente, cuando Microsoft diseñó Tables, en mi opinión, se perdieron una gran característica de diseño, ya que no puede usar solo el encabezado de la columna para referirse a un rango para una lista de validación de datos. Dentro de tblVal, no puede referirse directamente a Europa, por ejemplo, para obtener la lista de entradas de J7:J15.

Sin embargo, puede devolver ese rango, si usa Indirecto, escribiendo= INDIRECTO ("tblval [Europa]")

Alternativamente, debe crear un rango con nombre con Name Manager of Europe con Refers to:=tblval[Europa]

Tal vez esto se aborde en futuras versiones de Excel.

Pero en este artículo quiero concentrarme en el uso de varias tablas de una sola columna como nuestros datos de origen, en lugar del enfoque de múltiples columnas de una sola tabla (como se discutióen este articulo)

Tablas de una sola columna

Con las tablas de una sola columna, ya hemos discutido el hecho de que cada una tendrá sus propias dimensiones (siempre una sola columna) pero un número variable de filas.

Pero hay otra gran ventaja, y si fue por diseño, o es simplemente una "peculiaridad" de las tablas, y es que si nombra la tabla con el mismo nombre que el texto en la celda de encabezado de la columna de tablas, puede consultar a esto directamente y devolver el rango de celdas que pertenecen a ese nombre. Puede hacer esto sin tener que crear un rango con nombre y sin tener que hacer referencia a= INDIRECTO (nombre de tabla [Encabezado])

Con la tabla de una sola columna=Europadevolverá exactamente el mismo rango que= INDIRECTO ("Europa [Europa]")

Entonces, usar una tabla de una sola columna nos aleja de un nivel de uso Indirecto, nos permite usar directamente el texto del encabezado de nuestra columna para referirnos al rango y nos brinda un rango dinámico que solo incluirá entradas válidas sin celdas en blanco (a menos que introducir deliberadamente una celda en blanco en una tabla).

Podemos hacer clic en el encabezado de la columna y ordenarlo de forma ascendente o descendente en cualquier momento, y ordenará esa lista de validación de datos de forma bastante independiente de cualquier otra lista.

Usar tablas en la validación de datos

Veamos cómo usar estas tablas en la validación de datos, especialmente en la validación de datos dependientes.

Tratemos primero con el caso en el que nuestra entrada de datos está en una hoja en una lista ordinaria (a diferencia de una tabla). Por ejemplo. En una hoja llamada Entrada de datos, podríamos tenerla configurada con encabezados como los que se muestran a continuación, con la validación de datos aplicada a las celdas donde vamos a ingresar datos. En este ejemplo, comencé en A1 de la hoja de ingreso de datos, podría comenzar en cualquier parte de la hoja, pero necesitaría ajustar las fórmulas que mostraré en breve para tener esto en cuenta.

Ahora tenemos que usar la función Indirecta, no para crear nuestra lista de validación de datos, ya que ya lo hemos logrado como se muestra arriba, sino para proporcionar una forma de que la lista de validación de datos que aparece en cualquier celda dependa de la anterior. entradas en esa fila.

La validación de datos se ha aplicado a las celdas de la columna A con la fórmula= INDIRECTO ($ A $ 1)tenga en cuenta que esta es una referencia absoluta, por lo que siempre se referirá a la celda A1. En otras palabras, las celdas de esta columna serán la lista que es igual al texto que se encuentra en A1 - Región. Podríamos haber usado =Region fácilmente en este caso, y no usar el método Indirecto, pero el método que se muestra permitirá que cualquier cosa que elija tenga como encabezado en la primera columna de su libro de trabajo.

En las demás celdas siempre tendrá que utilizar la función Indirecta, tomando como argumento el valor que ya se ha introducido en la celda anterior de esa fila. Entonces, para todas las demás celdas en este ejemplo, la validación de datos se habrá configurado seleccionando todo el rango de celdas y luego configurando la validación para que sea Lista y= INDIRECTO (A2)

Como la referencia aquí es relativa, se ajustará automáticamente, de modo que cuando el cursor estaba en la celda D2, se estaría refiriendo a= INDIRECTO (C2)y proporcione la lista de validación para el nombre de Inglaterra.

Si su validación de datos está en una parte diferente de la hoja, no comenzando en la primera columna ni en la primera fila, entonces asegúrese de configurar esta fórmula indirecta relativa para que siempre haga referencia a la celda inmediatamente debajo de su primer encabezado.

Incluso si está utilizando una tabla para su entrada de datos, debido a que tiene que usar Indirecto para que este método funcione, no tiene mucho sentido molestarse con las referencias estructuradas, y puede usar la misma fórmula con su tabla.

En el siguiente ejemplo, la tabla de entrada de datostblDataestá ubicado en J8:M12 y la entrada de Validación de datos en la columna J se ha establecido en= INDIRECTO ($ J $ 8)y para las columnas K, L y M se ha establecido como=INDIRECTO(SUSTITUIR(J9," ","_"))

No use espacios en los nombres

Excel no permite el uso de espacios en los rangos con nombre, ni en los nombres de las tablas, por lo que debe tener esto en cuenta al configurar sus tablas de validación de datos.

Por ejemplo, en la lista de MEA, están Turquía, Sudáfrica, Jordania, Egipto e Israel. Cuando configure la tabla para Sudáfrica, deberá insertar un guión bajo en lugar del espacio y llamarlo South_Africa, y en la fila del encabezado de esa tabla deberá ser South_Africa. Incluso si ha insertado el guión bajo en el nombre de la tabla, pero deja el espacio en el encabezado, Excel no devolverá el rango usando solo el texto del encabezado.

Entonces, en caso de que haya espacios en cualquiera de los datos de su lista, p. Columbia Británica en la lista de Canadá, podemos asegurarnos de que Excel usa el guión bajo para corresponder con los nombres que ha usado en la tabla de validación usando la función Sustituir.

Si la celda A2 contenía Columbia Británica, entonces=SUSTITUIR(A2," ","_")devolvería British_Columbia La misma fórmula si la celda A2 contuviera Ontario, devolvería Ontario, ya que no hay espacios para sustituir.

Por lo tanto, es mejor cambiar nuestra fórmula de validación.

de= INDIRECTO (A2)

a=INDIRECTO(SUSTITUIR(A2," ","_"))

VBA para crear tablas de una sola columna

Si ya tiene una hoja con una gran cantidad de datos ya ingresados ​​en forma estándar, y no desea crear manualmente cada una de las tablas de una sola columna que necesita para este método, entonces el código que se muestra a continuación creará todas las tablas para y se asegurará de que se eliminen los espacios al configurar los nombres de las tablas y las entradas de encabezado en la primera fila de las tablas creadas.

Este código asume que lo único en su hoja son las entradas que desea usar para la Validación de datos. Utiliza el objeto UsedRange para determinar dónde existe el cuerpo de datos en la hoja que desea convertir en tablas de una sola columna y que todos los elementos del encabezado están en la misma fila.

Sub CreateTables()Dim firstCol As Long, lastCol As LongDim firstRow As Long, lastRow As LongDim c As Long, x As LongDim TableExist As StringDim TableName As StringDim shRange As RangeDim ws As Worksheet' cambie esto si es necesario al nombre de su conjunto de hojas Ws = Sheets("Lists")Set shRange = ws.UsedRangefirstCol = shRange.ColumnfirstRow = shRange.RowlastCol = Cells(firstRow, Columns.Count) _ .End(xlToLeft).ColumnWith Ws For c = firstCol To lastCol If Cells(firstRow, c ) <> "" Then lastRow = Cells(Rows.Count, c).End(xlUp).Row x = x + 1 On Error Resume Next TableExist = Cells(firstRow, c).ListObject.Name On Error GoTo 0 .Cells (firstRow, c) = _ WorksheetFunction.Substitute _ (.Cells(firstRow, c), " ", "_") TableName = .Cells(firstRow, c).Value If TableExist <> TableName Then ws.ListObjects.Add( xlSrcRange, _ Range(Cells(firstRow, c), _ Cells(lastRow, c)), , _ xlYes).Name = TableName End If End If Next cEnd WithOn Error GoTo 0End Sub

Si sus datos de validación no son lo único en la hoja, siempre que sus datos estén en un bloque contiguo Y tenga celdas en blanco que rodeen la región de estos datos, entonces puede modificar el código anterior, cambiando la línea

Establecer shRange = ws.UsedRange

A

Establezca shRange=ws.Range("E5").CurrentRegion

Reemplazo de Rango ("E5") con la ubicación de la celda para la primera fila y columna de los datos que desea convertir.

Borrar entradas de celda

Dado que la validación de datos se trata de garantizar que el usuario solo ingrese datos válidos en las celdas, las "ruedas pueden salirse del carro" si el usuario pasa por el proceso de selección y elige, por ejemplo, América > Canadá > Ontario > Toronto y luego regresa y cambia las Américas por Europa. Claramente, los valores que siguen a América serán todos incorrectos, a menos que el usuario vaya y realice cada uno de los cambios.

Si puede aceptar un poco de VBA en su libro de trabajo, la versión .xlsm, como se muestra a continuación, incluye el siguiente código de evento para borrar las entradas en la misma fila, a la derecha del valor modificado. Como bien puede haber más datos en la tabla que son datos numéricos y no deben alterarse incluso si los valores anteriores cambian, debemos informar al código hasta qué punto debe borrar los datos.

He usado una variable llamadamaxcolsen el código a continuación, que se ha establecido en 6 para este ejemplo, pero deberá modificarlo según su propia situación. El siguiente código se ha copiado en la hoja Entrada de datos

NOTA: Esta macro utiliza elMétodo EnviarTeclas

Option ExplicitPrivate Sub Worksheet_Change(ByVal Target As Range)Dim tr As Long, tc As Long, maxCols As LongOn Error GoTo exit_subtr = Target.Row: tc = Target.Column'//// establecer el número de columna para la última columna'/ /// desea que se borre automáticamente'//// cuando se cambia una celda anterior'//// A menudo tendrá otros'//// datos numéricos en una tabla que'//// no necesita ser borrado'//// si se modifican otros valoresmaxCols = 6'//// En este ejemplo, hay validaciones dependientes'//// en la columnac a F, por lo que maxcols se ha establecido en 6If Target.Count > 1 Then GoTo exit_subIf Not Intersect(Target, _ Me.ListObjects(1).Range) Is Nothing Then'/// Asume que hay una sola tabla en esta hoja,'/// por lo tanto, se puede usar ListObjects(1),'/// no tiene que ser nombrado.'/// Puede ser específico y usar el formulario'/// If Not Intersect(Target, _'/// Me.ListObjects("tblData").Range) Is Nothing Then If tc = maxCols Then GoTo exit_sub Application.EnableEvents = False If tc < maxCols Then Range(Cells(tr, tc + 1), _ Cells(tr, maxCols)).ClearContents End If '//// Esta siguiente sección verifica si' //// la celda ha tenido una entrada realizada,'//// y si es así usa Sendkeys para automáticamente'//// mostrar la lista desplegable de posibles entradas,'//// sin tener que hacer clic en la flecha desplegable .If Target <> vbNullString Then Target.Offset(0, 1).Select If ActiveCell.Validation.Type = 3 Then Application.SendKeys ("%{DOWN}") End If End IfEnd Ifexit_sub: Application.EnableEvents = TrueEnd Sub

Además de borrar las entradas listas para que se seleccionen nuevos valores, el código verifica si se ha ingresado un nuevo valor en la celda (a diferencia de solo una eliminación de valor) y, de ser así, selecciona la celda una columna a la bien.

Luego se realiza una verificación para ver si esta celda recién seleccionada contiene Validación de datos, y si es así, produce la lista de valores desplegables apropiados listos para que el usuario los seleccione, sin la necesidad de que primero haga clic en la flecha en la celda para producir la lista. Esto puede acelerar considerablemente la entrada de datos.

Para usar esta versión, descargue el archivo de muestra CON macros, a continuación.

Obtenga el archivo de muestra

Descargue uno de los siguientes libros de trabajo de muestra comprimidos:

• Listas dependientes con tablas e INDIRECTO -- SIN macros
• Listas dependientes con tablas e INDIRECTO -- CON macros

Sobre el desarrollador

Roger Govier es un MVP de Excel con sede en el Reino Unido que realiza tareas en Excel y VBA para clientes de todo el mundo. Si bien disfruta del desafío intelectual de resolver problemas con funciones de hojas de trabajo, Roger afirma ser intrínsecamente perezoso, por lo que siempre busca una forma rápida y sencilla de brindar soluciones sólidas y viables.

Encuentre más tutoriales y archivos de muestra de Roger aquí:Archivos de muestra - Roger Govier

Puede ponerse en contacto con Roger en: roger@technology4u.co.uk

roger govier

Tutoriales relacionados

Conceptos básicos de validación de datos

Función INDIRECTA

Tablas de Excel

Crear listas dependientes

Menús desplegables dependientes de la lista ordenada

Listas dependientes ÍNDICE

Tablas de listas dependientes

Archivos de muestra - Roger Govier