Expande tus horizontes en Excel: Consejos para acomodar una gama en crecimiento

Las hojas de cálculo de Excel crecen, a veces añadiendo nuevas funcionalidades, pero la mayoría de las veces añadiendo nuevos datos. Un buen diseño permitirá acomodar la mayoría de los crecimientos y cambios, pero a menudo te encuentras trabajando con un conjunto de datos que no se ajusta bien a lo que necesitas hacer. Afortunadamente, con un poco de conocimiento, puedes conseguir lo que quieres. Cuando no puedes, es posible que tengas que considerar restringir los datos. En este ejemplo, veremos tres formas de hacer lo mismo: actualizar automáticamente las funciones dependientes cuando se amplía un rango de datos. Específicamente, contaremos las ocurrencias de cadenas en varias columnas en lugar de una sola. Puedes trabajar con un conjunto de datos simple propio o descargar el archivo de demostración .xlsx.

Contar es fácil

Figura A muestra un conjunto de datos simple de varias columnas y filas. Dado que los nombres aparecen en varias columnas y filas, haremos referencia al rango. Con ese fin, he utilizado una simple función CONTAR.SI() que evalúa el rango completo en lugar de una sola fila o columna, como podrías estar más acostumbrado a ver.

Figura A

CONTAR.SI() devuelve el número de veces que cada nombre aparece en el rango.

Esta función utiliza la sintaxis:

=CONTAR.SI(rango, criterio)

Después de introducir la primera función, =CONTAR.SI($B$2:$E$9,$H2), en la celda I2, la copié al resto de la lista de nombres únicos. Observa que la referencia del rango es absoluta, pero sólo la referencia de la columna es absoluta en el criterio. La referencia absoluta es necesaria para obtener un recuento preciso al evaluar un rango en lugar de una fila o columna.

Cómo resaltar detalles importantes en una presentación de PowerPoint

Añadir datos complica el recuento

Si estás trabajando con un rango estable donde las dimensiones no aumentan, esta solución funciona bien. Sin embargo, tan pronto como añadas una nueva fila o columna, tendrás problemas porque las funciones CONTAR.SI() no se actualizarán automáticamente para incluir los nuevos datos. Como puedes ver en Figura B, los recuentos permanecen iguales incluso después de añadir una nueva fila y columna.

Figura B

Las funciones CONTAR.SI() no se actualizan cuando se añaden nuevos datos.

La referencia absoluta en la referencia de rango es necesaria en este caso. Debido a que la referencia es absoluta, la función no se actualizará automáticamente para incluir nuevos datos añadidos cada día.

Una solución sencilla

Probablemente sepas que puedes insertar una fila o columna en un rango para aprovechar el referenciado automático. Figura C muestra el resultado de añadir una nueva fila. Como puedes ver, las funciones se actualizan, pero la nueva fila introduce nuevos problemas:

• El conjunto de datos ya no está ordenado por fecha. Normalmente, esto no importa porque puedes ordenar tus datos.
• La fila recién insertada crea un espacio vacío en la lista de nombres únicos. Si puedes mover la lista única y la columna de funciones CONTAR.SI() a otra hoja, este problema desaparecerá.

Figura C

Insertar una fila o columna es una solución sencilla si no introduce nuevos problemas.

Aunque no lo he mostrado usando el conjunto de datos de ejemplo, existe el mismo potencial problemático al insertar una columna. Sin embargo, estos pequeños problemas se solucionan fácilmente. No almacenes nada más en la hoja con tu conjunto de datos y la inserción se convierte en una solución fácil. Incluso podrías crear una macro que inserte nuevas columnas y filas para tus usuarios.

Cómo crear un UserForm en Excel para facilitar la entrada de datos

Rescate de tablas

Dependiendo del nivel de experiencia de tus usuarios, puede que quieras considerar la conversión del conjunto de datos a un objeto de Tabla (compatible con Excel 2007 y versiones posteriores). Cuando insertas columnas y filas, las referencias se actualizarán automáticamente.

Para convertir un conjunto de datos en una tabla, sigue estos pasos:

1. Haz clic dentro del conjunto de datos.
2. En la pestaña Insertar, haz clic en Tabla en el grupo Tablas.



3. Haz clic en Aceptar.

Con el nuevo objeto de Tabla en su lugar, puedes insertar una nueva fila fácil y rápidamente. Simplemente selecciona la primera celda vacía debajo de la tabla y comienza a introducir los datos. Excel extenderá automáticamente el objeto de Tabla para incluir la nueva fila y actualizará la referencia en tus funciones CONTAR.SI(), como se muestra en Figura E.

Figura E

El objeto de Tabla de Excel es lo suficientemente flexible como para incluir nuevos datos y actualizar funciones dependientes.

Para aprovechar al máximo el arreglo de la Tabla, utiliza una referencia estructurada en las funciones CONTAR.SI(). Específicamente, reemplaza la referencia de rango absoluta por el nombre de la nueva Tabla, como se muestra en Figura F. Entonces, cuando insertes una nueva columna a la derecha, las funciones CONTAR.SI() se actualizarán en consecuencia.

Cómo utilizar un UserForm para facilitar la entrada de datos en Excel

Figura F

Después de convertir un conjunto de datos en una Tabla, actualiza las referencias dependientes.

Cuando creas un objeto de Tabla, Excel le asigna un nombre; utiliza ese nombre o dale un nombre más significativo. Haz clic en cualquier lugar dentro de la Tabla y luego haz clic en la pestaña de Diseño contextual. El nombre de la Tabla aparecerá en el control de Nombre de Tabla en el extremo izquierdo, como se muestra en Figura G. Para cambiar el nombre de la tabla, simplemente haz clic dentro y escribe un nuevo nombre.

Figura G

Utiliza el nombre predeterminado de Excel o renómbralo.

Reestructuración

La solución de la Tabla es mi favorita, pero puede que no sea práctica para ti. El objeto de Tabla de Excel, aunque potente, tiene algunas limitaciones. Por esa razón, me gustaría presentar una posibilidad más, una que es más difícil de implementar después del hecho.

Tal como está, la estructura del rango de datos es un poco ambigua. Una estructura más estable se muestra en Figura H. Al añadir los nombres en una columna larga, en lugar de distribuirlos en varias columnas, facilita un poco la implementación de otras funciones de Excel (porque puedes ordenar fácilmente, añadir nuevos registros para fechas pasadas no es un problema).

Cómo usar la función de vista de tareas en Windows 10

Figura H

En lugar de ir hacia los lados, ve hacia abajo.

La referencia de columna mostrada en Figura I maneja las filas nuevas tal como están. La hoja debe estar libre de otros datos debajo del rango de datos, porque la referencia de columna evalúa la columna completa (incluyendo la celda de encabezado).

Figura I

La referencia de columna estructurada evalúa filas nuevas.

La reestructuración es incómoda, especialmente si tienes muchos datos. Si puedes implementar esta estructura inicialmente, puedes evitar problemas a medida que tu libro de trabajo crece.

Envíame tu pregunta sobre Office

Respondo a las preguntas de los lectores cuando puedo, pero no hay garantía. Al contactarme, sé lo más específico posible. Por ejemplo, "Por favor, soluciona mi libro de trabajo y arregla lo que está mal" probablemente no obtendrá una respuesta, pero "¿Me puedes decir por qué esta fórmula no está devolviendo los resultados esperados?" podría hacerlo. No estoy retribuido por Newsmatic por mi tiempo o experiencia, ni solicito una tarifa a los lectores. Puedes contactarme en [email protected].

Cómo crear y utilizar campos de búsqueda en Microsoft Access

En Newsmatic nos especializamos en tecnología de vanguardia, contamos con los artículos mas novedosos sobre Microsoft, allí encontraras muchos artículos similares a Expande tus horizontes en Excel: Consejos para acomodar una gama en crecimiento , tenemos lo ultimo en tecnología 2023.