Forum Discussion
flopez35
Mar 14, 2022Copper Contributor
Unir dos rangos en uno solo
Necesito saber como puedo unir dos o más rangos uno a continuación del otro utilizando fórmulas, he buscado en internet y no he encontrado una forma de hacerlo
- Mar 14, 2022
Using Excel 365 the content of the two ranges may be combined to give an array
= LET( m, ROWS(range1), n, ROWS(range2), k, SEQUENCE(m+n), IF(k<=m, INDEX(range1,k), INDEX(range2,k-m)) )
PeterBartholomew1
Apr 22, 2024Silver Contributor
It is still quite possible to use SEQUENCE and modify the formula to pickup data from the correct array but newer array shaping functions may offer a better solution. For example
= VSTACK(range1, range2, range3, range4)
If the number of ranges is variable, but they lie within an overall containing range then
= TOCOL(containingRange,1,TRUE)
would be preferable.
PakitoGARRIDO
Apr 22, 2024Copper Contributor
Gracias de nuevo, efectivamente he leído sobre esas funciones, la cuestión es que no dispongo de 365 y por tanto no las puedo utilizar.
saludos
- PeterBartholomew1Apr 22, 2024Silver Contributor
Bad luck! I would not consider using legacy versions of any spreadsheet software; they are terrible.
I think
= IFS( k <= n₁, INDEX(range1, k), k <= n₁ + n₂, INDEX(range2, k - n₁), k <= n₁ + n₂ + n₃, INDEX(range3, k - n₁ - n₂), TRUE, INDEX(range4, k - n₁ - n₂ - n₃) )
may work on a non-dynamic array version of Excel (older versions require nested IF rather than IFS) but I have no way of testing it. The intention is that it should work either as a CSE array formula or but using relative referencing.