Forum Discussion

flopez35's avatar
flopez35
Copper Contributor
Mar 14, 2022

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
Excel
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Mar 14, 2022

    flopez35 

    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's avatar
PeterBartholomew1
Silver Contributor
Apr 22, 2024

PakitoGARRIDO 

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's avatar
PakitoGARRIDO
Copper Contributor
Apr 22, 2024

PeterBartholomew1 

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

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Apr 22, 2024

    PakitoGARRIDO 

    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.

Resources