¿Cómo transponer una referencia al arrastrarla automáticamente hacia abajo o hacia la derecha en Excel?
Al trabajar con fórmulas y el autorrelleno en Excel, el comportamiento predeterminado es que las referencias de columna se incrementen al arrastrar el controlador de relleno horizontalmente hacia la derecha, y que las referencias de fila se incrementen al arrastrar verticalmente hacia abajo. Sin embargo, en ocasiones puede necesitar transponer esas referencias; por ejemplo, hacer referencia a un rango vertical mientras rellena horizontalmente, o viceversa. Esta técnica resulta especialmente útil al reorganizar diseños de datos o crear informes dinámicos que requieren referencias cruzadas con datos transpuestos. En este artículo exploramos diversos métodos prácticos para lograr un autorrelleno transpuesto, incluidas fórmulas, macros VBA y herramientas eficaces como Kutools. Consulte la ilustración siguiente para ver un escenario típico de autorrelleno transpuesto:
Transponer la referencia al rellenar hacia abajo o a la derecha
Fórmula de Excel: use ÍNDICE u DESPLAZAMIENTO para construir manualmente referencias transpuestas
Transponer la referencia al rellenar hacia abajo o a la derecha
Para transponer una referencia al rellenar filas o columnas, puede utilizar la función TRANSPONER de Excel. Este método es ideal cuando desea convertir datos organizados en una columna en una fila (o viceversa), ya que ajusta automáticamente la dirección de la referencia al usar el autorrelleno.
Seleccione el rango donde desee aplicar la referencia transpuesta. Introduzca la siguiente fórmula en la Barra de fórmulas:=TRANSPONER(Hoja2!$B$1:B12) y pulse a continuación Ctrl + Mayús + Intro simultáneamente (en versiones anteriores a Microsoft 365 / Excel 2021). En versiones más recientes compatibles con matrices dinámicas, basta con pulsar simplemente Intro. Consulte la captura de pantalla siguiente:
En esta fórmula, B1:B12 representa el rango que desea transponer de una disposición vertical a una horizontal. Puede ajustar el nombre de la hoja y el rango específico según su rango de origen real.
Consejo: Para evitar errores en las fórmulas, asegúrese de que el rango de destino para los datos transpuestos tenga las dimensiones adecuadas (por ejemplo, si el origen tiene una columna con 12 filas, el rango de destino debe tener una fila con 12 columnas).
Fórmula de Excel: use ÍNDICE u DESPLAZAMIENTO para construir manualmente referencias transpuestas
A veces no es preferible utilizar fórmulas matriciales como TRANSPONER, o bien necesitas un control más preciso sobre cómo se transponen las referencias, especialmente con datos no contiguos o al trabajar en versiones antiguas de Excel. Con las funciones ÍNDICE y DESPLAZAMIENTO, puedes hacer referencia directamente a datos transpuestos mediante operaciones estándar de relleno, sin necesidad de usar Ctrl + Mayús + Intro.
Escenarios aplicables: Ideal cuando necesitas una referencia transpuesta celda por celda; por ejemplo, referenciar la celda A1 de forma vertical en B1 de forma horizontal (o viceversa). Perfecto para evitar fórmulas matriciales o para crear referencias dinámicas en hojas de cálculo complejas.
Ventajas: No requiere entrada matricial; las fórmulas son sencillas de arrastrar y rellenar.
Inconvenientes potenciales: Puede resultar algo más complejo configurar la fórmula inicial; preste especial atención a cómo ajusta los índices de fila y columna.
Cómo usar ÍNDICE para referencias transpuestas:
1. Supongamos que tiene datos en A1:A5 (vertical) y desea rellenarlos transpuestos en B1:F1 (horizontal). Seleccione la celda B1 e introduzca la siguiente fórmula:
=INDEX($A$1:$A$5,COLUMN(A1))2. Pulse Intro y arrastre la fórmula hacia la derecha hasta la celda F1. La fórmula utiliza COLUMNA(A1) para incrementarse al moverse a la derecha, haciendo así referencia a A1, A2, ..., A5 en el rango B1:F1.
Cómo usar DESPLAZAMIENTO para referencias transpuestas:
1. Supongamos que sus datos están en la fila B1:F1 (horizontal) y desea rellenarlos verticalmente en la columna G1:G5. En la celda G1, introduzca:
=OFFSET($B$1,0,ROW(A1)-1)2. Pulse Intro y arrastre la fórmula hacia abajo hasta G5. Al rellenar hacia abajo, FILA(A1)-1 se incrementará desde 0, desplazando el índice de columna de la función DESPLAZAMIENTO para obtener B1, C1, D1, etc.
Ajuste el rango de la fórmula o la posición inicial según sea necesario para adaptarlo al diseño de sus datos. Asegúrese siempre de que las referencias sean correctas, especialmente al copiar o mover fórmulas, y evite así incoherencias en las referencias.
Resolución de problemas: Si obtiene un error #¡REF!, revise cuidadosamente los rangos de referencia y asegúrese de que la fórmula no intente hacer referencia fuera del conjunto de datos objetivo. Asimismo, en conjuntos de datos grandes, estas fórmulas pueden aumentar la carga de cálculo del libro.
En resumen, la elección entre usar fórmulas, VBA o un complemento de Excel depende de la estructura de sus datos, la frecuencia con la que realiza esta operación y su nivel de comodidad con funciones avanzadas de Excel. Si un enfoque no se adapta a sus necesidades, pruebe otro de los métodos descritos anteriormente. Si persisten problemas con fórmulas o referencias, revise cuidadosamente el uso de referencias absolutas (símbolos)$) y ajústelas según sea necesario para garantizar la direccionalidad correcta al transponer.
Descubra la magia de Excel con KUTOOLS AI
- Ejecución inteligente: Realice operaciones en celdas, analice datos y cree gráficos con comandos sencillos.
- fórmulas personalizadas: Cree fórmulas a medida para optimizar sus flujos de trabajo.
- Programación en VBA: Escriba e implemente código VBA con facilidad.
- Interpretación de fórmulas: Entienda las fórmulas complejas con facilidad.
- Traducción de texto: Rompa las barreras del idioma directamente en sus hojas de cálculo.
Las mejores herramientas de productividad para Office
Potencie sus habilidades en Excel con Kutools para Excel y experimente una eficiencia como nunca antes.Kutools para Excel ofrece más de 300 funciones avanzadas para aumentar su productividad y Ahorrar tiempo.Haga clic aquí para obtener la función que más necesita...
Office Tab aporta una interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Active la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas dentro de la misma ventana, en lugar de hacerlo en ventanas separadas.
- ¡Aumente su productividad en un 50 % y elimine cientos de clics del ratón cada día!
Todos los complementos de Kutools en un solo instalador.
Kutools for Office es la suite que incluye complementos para Excel, Word, Outlook y PowerPoint, además de Office Tab Pro, ideal para equipos que trabajan en distintas aplicaciones de Office.
- Suite integral— complementos para Excel, Word, Outlook y PowerPoint + Office Tab Pro
- Un instalador, una licencia— configuración en minutos (compatible con MSI)
- Rendimiento mejorado en conjunto— productividad optimizada en todas las aplicaciones de Office
- Prueba gratuita de 30 días con todas las funciones— sin registro ni tarjeta de crédito
- La mejor relación calidad-precio— ahorre frente a la compra individual de complementos