问题:

I have a dataset from range A1:T25 and I want this to be in a single row or column. There are 25 rows and 20 columns. I tried the following:

={A1:A25; B1:B25; C1:C25; D1:D25; E1:E25; F1:F25; G1:G5; H1:H25; I1:I25; J1:J25; K1:K25; L1:L25; M1:M25; N1:N25; O1:O25; P1:P25; Q1:Q25; R1:R25; S1:S25; T1:T25}

After pressing Enter a column appears with my data, but somehow there are 480 cells instead of the 500. I want to know which of the multiple ranges I made a mistake in, as well as a short, non-repetitive way of doing this.

回答1:

an alternative would be:

=ARRAYFORMULA(TRANSPOSE(SPLIT(JOIN(" ", 
 TRANSPOSE(QUERY(TRANSPOSE(A1:T25), , 10000))), " ")))

or for the row:

=ARRAYFORMULA(SPLIT(JOIN(" ", 
 TRANSPOSE(QUERY(TRANSPOSE(A1:T25), , 10000))), " "))

or column by column:

=ARRAYFORMULA(TRANSPOSE(SPLIT(JOIN(" ", 
 QUERY(A1:T25, , 10000)), " ")))

or column by column in the row:

=ARRAYFORMULA(SPLIT(JOIN(" ", 
 QUERY(A1:T25, , 10000)), " "))

回答2:

mistake spotted at G1:G5

try:

={A1:A25; B1:B25; C1:C25; D1:D25; E1:E25; 
  F1:F25; G1:G25; H1:H25; I1:I25; J1:J25; 
  K1:K25; L1:L25; M1:M25; N1:N25; O1:O25; 
  P1:P25; Q1:Q25; R1:R25; S1:S25; T1:T25}