La útil de nervio de datos de Microsoft Excel le permite añadir una repertorio desplegable a una celda basada en los datos existentes en una columna. Sin secuestro, cómo funciona esto depende de si los datos de origen son parte de una tabla de Excel formateada. Echemos un vistazo a algunos ejemplos.

Usando una columna en una tabla de Excel formateada

Imagine que tiene esta tabla de Excel formateada emplazamiento “puntajes” que contienen nombres de jugadores, naciones y puntajes, y necesita extraer algunos datos de compendio. Específicamente, en la celda H2, desea crear una repertorio desplegable de todas las naciones enumeradas en la columna B, y en las celdas I2 y J2, mostrar los nombres de los jugadores y la puntuación promedio, respectivamente, según la nación seleccionada.

Para crear una repertorio desplegable, seleccione la celda donde desee que esté (en este caso, Cell I2) y en la pestaña Datos en la cinta, haga clic en “Brío de datos” en la opción desplegable con el mismo nombre.

Relacionado

Cómo añadir una repertorio desplegable a una celda en Excel

Valienta la escritura en las mismas opciones 200 veces diferentes manualmente.

Luego, en el campo Permitir la pestaña Configuración, seleccione “Inventario”.

El subsiguiente paso implica ingresar las opciones que desea aparecer en la repertorio desplegable en el campo de origen. Para hacer esto, puede escribir las opciones manualmente, clasificar las celdas relevantes en su hoja de cálculo o usar una fórmula.

Idealmente, podrías escribir:

=Scores(Nation)

En el campo de origen, exactamente como lo haría al hacer narración a esa columna en una fórmula, donde “puntajes” representa el nombre de la tabla, y la “nación” representa el encabezado de la columna. Sin secuestro, desafortunadamente, esto devuelve un mensaje de error, ya que la útil de nervio de datos solo reconoce las referencias de celdas, las fórmulas y los rangos nombrados como fuentes de datos, no encabezados de columna de tabla.

Por fortuna, hay dos formas de evitar esto.

Método 1: Remisión a las celdas en la tabla

Primero, active el campo “Fuente” en el cuadro de diálogo Brío de datos para que el cursor parpadee, y pasee sobre el encabezado de la columna relevante en la fila 1 hasta que vea una pequeña flecha negra en torno a debajo. Cuando lo haga, haga clic una vez para clasificar todas las celdas de datos en esa columna.

Ahora, posteriormente de hacer clic en “Aceptar”, cuando selecciona Cell H2, aparece un brote desplegable, que puede hacer clic para clasificar una opción de la fuente. Observe, igualmente, cómo se muestran los títulos únicos en la repertorio; en otras palabras, Excel reconoce los títulos duplicados y solo los muestra una vez.

La belleza de usar este método es que si agrega más filas a la tabla de Excel formateada, la fuente de nervio de datos se ajusta automáticamente para incluir las celdas o celdas adicionales.

Ahora, puede usar la función de filtro en la celda i2 para enumerar los jugadores de la nación seleccionada:

=FILTER(Scores(Player),Scores(Nation)=H2)

Relacionado

Cómo usar la función de filtro en Microsoft Excel

Hay más de una forma de filtrar sus datos.

Finalmente, use la función promedio en la celda J2 para mostrar el puntaje promedio de jugadores de esta nación:

=AVERAGEIF(Scores(Nation),H2,Scores(Total))

Relacionado

Cómo usar las funciones promedio y promedio en Excel

Sea selectivo sobre qué incluir en sus cálculos promedio.

Método 2: Nombra el rango

La segunda forma de crear una repertorio desplegable a partir de una columna en una tabla formateada implica nombrar el rango de origen.

Antaño de iniciar el cuadro de diálogo de nervio de datos, seleccione todas las celdas en la tabla que contienen los títulos que desea incluir en la repertorio desplegable, escriba un nombre para el rango en el cuadro de nombre en la arista superior izquierda de la ventana Excel y presione Entrar.

Luego, en el campo de origen del cuadro de diálogo Brío de datos, escriba un signo igual (=), seguido del nombre que acaba de asignar al rango, y presione Entrar. En este ejemplo, debe escribir:

=Nation

Al igual que con el método antecedente, Excel reconoce que los datos están en una tabla formateada, por lo que expande automáticamente el rango si los datos se agregan a la subsiguiente fila.

El uso de rangos con nombre en Excel tiene muchos beneficios. Por ejemplo, los rangos de nombres hacen que su manual de trabajo sea más accesible para las personas que usan lectores de pantalla. Adicionalmente, puede navegar rápidamente a los rangos con nombre escribiéndolos en el cuadro de nombre o seleccionándolos de las opciones cuando hace clic en la flecha en torno a debajo, y igualmente se pueden usar en fórmulas.

Relacionado

Siempre nombro rangos en Excel, y tú igualmente deberías

Ordene su manual de trabajo de Excel.

Ahora, como con el Método 1, puede usar las funciones Filter y promedio para completar la linaje de datos.

Sin secuestro, esta vez, las fórmulas pueden ser más directas, ya que puede hacer narración al rango que llamó “nación” sin hacer narración a la tabla en la que se encuentra el rango.

Entonces, para el filtro en la celda i2, es:

=FILTER(Scores(Player),Nation=H2)

Y para el promedio de la celda J2, es:

=AVERAGEIF(Nation,H2,Scores(Total))

Uso de una columna en un conjunto de datos sin formato

Ciertos tipos de datos, como las matrices derramadas, no se forman como una tabla de Excel, por lo que puede poseer momentos en que necesite encontrar una forma de crear una repertorio desplegable desde una columna internamente de un conjunto de datos sin formato.

Luego de clasificar la celda que contendrá la repertorio desplegable y hacer clic en “Brío de datos” en la pestaña Datos en la cinta, seleccione “Inventario” en el campo Permitir.

A continuación, en el campo de origen, use las funciones indirectas y counta juntas para decirle a Excel dónde encontrar las opciones para la repertorio desplegable.

En este caso, escribiendo:

=INDIRECT("B2:B"&COUNTA(B:B))

Incluye todos los títulos en la columna B desde la celda B2 hasta la celda B22 como fuente.

Rompamos esta fórmula de origen para ver cómo funciona.

• = Indirecto (: Esto le dice a Excel que desea que se determine la fuente utilizando una narración dinámica.
• “B2: B”: Específicamente, la narración dinámica comienza en la celda B2 y termina en otra celda en la columna B.
• & Countta (b: b)): Esto cuenta todas las celdas en la columna B que no están en blanco y agrega el total a la narración. En este caso, 22 celdas en la columna B contienen títulos, por lo que esto convierte B2: B en B2: B22.

Relacionado

Cómo usar la función indirecta en Excel

Use una dependencia de texto para crear una narración.

Entonces, si agrega otra fila de datos en la parte inferior, la función Countta elegirá esto e informará la narración indirecta en la regla de nervio de datos que la fuente se ha expandido en torno a debajo por una fila adicional.

Puede realizar esto seleccionando la celda que aloja la repertorio desplegable y reabriendo el cuadro de diálogo Brío de datos. Cuando selecciona el campo de origen, aunque la fórmula no ha cambiado, la semirrecta punteada en la hoja de cálculo confirma que recoge los nuevos títulos en la fila adicional.

Ahora que su repertorio desplegable está repertorio para funcionar, use las funciones indirectas y countas conexo con la función de filtro en la celda I2 y la función promedio en la celda J2 para completar la hoja de cálculo.

Las listas desplegables agregadas a través de la útil de nervio de datos son increíblemente potentes y versátiles, y pueden estilarse en muchos escenarios en Excel. Por ejemplo, puede usar listas desplegables para hacer que los gráficos regulares sean dinámicos, una forma segura de impresionar a sus amigos y compañeros de trabajo.