¿Cómo crear referencias estructuradas en Excel?
Las referencias estructuradas comienzan con tablas de Excel. Tan pronto como las tablas creadas en Excel, automáticamente crea referencias estructuradas para usted.
Ahora eche un vistazo a la imagen de abajo.
- Paso 1: le había dado un enlace a la celda B3, en lugar de mostrar el enlace como B2, se muestra como Tabla1 [@Ventas]. Aquí Table1 es el nombre de la tabla y @Sales es la columna a la que nos referimos. Todas las celdas de esta columna se denominan con un nombre de tabla y seguidas del nombre de encabezado de columna.
- Paso 2: Ahora cambiaré el nombre de la tabla a Data_Table y cambiaré el encabezado de la columna a Amount .
- Paso 3: Para cambiar el nombre de la tabla, coloque un cursor dentro de la tabla> vaya a Diseño> Nombre de la tabla.
- Paso 4: menciona el nombre de la tabla como Data_Table.
- Paso 5: Ahora cambie y dé una referencia a la celda B3.
Así que hemos entendido que la referencia estructurada tiene dos partes: Nombre de tabla y Nombre de columna.
Ejemplos
Puede descargar esta plantilla de Excel de referencias estructuradas aquí - Plantilla de Excel de referencias estructuradasEjemplo 1
Usando referencias estructuradas puede hacer que su fórmula sea dinámica. A diferencia de las referencias de celda normales, permite que la fórmula esté activa en caso de adición y eliminación en el rango de datos.
Permítanme aplicar la fórmula SUM tanto para el rango normal como para la tabla de Excel.
Fórmula SUM para rango normal.
Fórmula SUM para la tabla de Excel.
Permítanme agregar algunas líneas a los datos de la tabla normal y de Excel. He agregado 2 líneas de pedido a los datos, ahora vea la diferencia.
La referencia estructurada en la tabla de Excel muestra el valor actualizado, pero el rango de datos normal no muestra los valores actualizados a menos que realice algunos cambios en la fórmula manualmente.
Ejemplo # 2
Ahora, mire un ejemplo más. Tengo información sobre el nombre del producto, la cantidad y el precio. Con esta información necesito llegar al valor de venta.
Para obtener el valor de venta, la fórmula es Cant. * Precio . Apliquemos esta fórmula en la tabla.
La fórmula dice [@QTY] * [@PRICE]. Esto es más comprensible que la referencia normal de B2 * C2. No obtenemos el nombre de la tabla si colocamos la fórmula dentro de la tabla.
Problemas con las referencias estructuradas de Excel
Al utilizar referencias estructuradas, nos enfrentamos a algunos problemas que se enumeran a continuación.
Problema # 1
Las referencias estructuradas también tienen sus propios problemas. Todos estamos familiarizados con aplicar la fórmula de Excel y copiarla o arrastrarla a las otras celdas restantes. Este no es el mismo proceso en las referencias estructuradas, funciona de manera un poco diferente.
Ahora, mire el siguiente ejemplo. He aplicado la fórmula SUM en Excel para el rango normal.
Si quiero sumar el precio y el valor de venta, simplemente copiaré y pegaré o arrastraré la fórmula actual a las otras dos celdas y me dará el valor SUMA del precio y el valor de venta.
Ahora aplique la misma fórmula para la tabla de Excel para la columna Cant.
Ahora tenemos la suma de la columna Cant. Como el rango normal, la fórmula copie la fórmula actual y péguela en la columna Precio para obtener el total de Precio.
¡¡¡Oh Dios mío!!! No muestra el total de la columna Precio, sino que sigue mostrando solo el total de la columna Cant. Por lo tanto, no podemos copiar y pegar esta fórmula en la celda adyacente o en cualquier otra celda para hacer referencia a la columna o fila relativa.
Arrastre la fórmula para cambiar la referencia
Ahora que conocemos su limitación, ya no podemos hacer el trabajo de copiar y pegar con referencias estructuradas. Entonces, ¿cómo superamos esta limitación?
La solución es muy simple, solo necesitamos arrastrar la fórmula en lugar de copiarla. Seleccione la celda de fórmula y use el controlador de relleno y arrástrelo a las dos celdas restantes para cambiar la referencia de la columna a Precio y Valor de venta.
Ahora tenemos fórmulas actualizadas para obtener los respectivos totales.
Problema # 2
Hemos visto un problema con las referencias de estructura y también encontramos la solución, pero tenemos un problema más aquí, no podemos hacer la llamada como referencia absoluta si estamos arrastrando la fórmula a otras celdas.
Echemos un vistazo al siguiente ejemplo ahora. Tengo una tabla de ventas con múltiples entradas y quiero consolidar los datos usando la función SUMIF en Excel.
Ahora aplicaré la función SUMIF para obtener los valores de ventas consolidados para cada producto.
He aplicado la fórmula para el mes de enero, ya que es una referencia estructurada, no podemos copiar y pegar la fórmula en las dos columnas restantes, no cambiará la referencia a febrero y marzo, por lo que arrastraré la fórmula.
¡¡Oh!! No obtuve ningún valor en la columna de febrero y marzo. ¿¿¿Cuál sería el problema??? Mira de cerca la fórmula.
Hemos arrastrado la fórmula del mes de enero. En la función SUMIF, el primer argumento es Criteria Range Sales_Table [Product] ya que arrastramos la fórmula que tiene cambios a Sales _Table [Jan].
Entonces, ¿cómo lo manejamos? Necesitamos hacer el primer argumento, es decir, la columna Producto como absoluta y otras columnas como referencia relativa. A diferencia de la referencia normal, no tenemos el lujo de usar la tecla F4 para modificar el tipo de referencia.
La solución es que necesitamos duplicar la columna de referencia como se muestra en la siguiente imagen.
Ahora podemos arrastrar la fórmula a otras dos columnas. El rango de criterios será constante y otras referencias de columna cambiarán en consecuencia.
Consejo profesional: para hacer que la FILA sea una referencia absoluta, necesitamos hacer una entrada doble de FILA, pero debemos poner el símbolo @ antes del nombre de la FILA.
= Sales_Table [@ [Producto]: [Producto]]
¿Cómo desactivar la referencia estructurada en Excel?
Si no eres fanático de las referencias estructuradas, puedes apagarlo siguiendo los pasos a continuación.
- Paso 1: Vaya a ARCHIVO> Opciones.
- Paso 2: Fórmulas> Desmarque Usar nombres de tablas en fórmulas.
Cosas para recordar
- Para hacer la referencia absoluta en la referencia estructurada, necesitamos duplicar el nombre de la columna.
- No podemos copiar la fórmula de la referencia estructurada, sino que debemos arrastrar la fórmula.
- No podemos ver exactamente a qué celda nos referimos en las referencias estructuradas.
- Si no está interesado en las referencias estructuradas, puede desactivarlas.
