Google 스프레드 시트의 무한 동적 범위에 대한 평균의 ArrayFormula
예를 들어 :
A B C D E F G ∞
|======|=======|=====|=====|=====|=====|=====|=====
1 | |AVERAGE| | | | | |
|======|=======|=====|=====|=====|=====|=====|=====
2 | xx 1 | | 1 | 2 | 0.5 | 10 | |
|======|=======|=====|=====|=====|=====|=====|=====
3 | xx 2 | | 7 | 1 | | | |
|======|=======|=====|=====|=====|=====|=====|=====
4 | | | 0 | | | | |
|======|=======|=====|=====|=====|=====|=====|=====
5 | xx 3 | | 9 | 8 | 7 | 6 | |
|======|=======|=====|=====|=====|=====|=====|=====
6 | xx 4 | | 0 | 1 | 2 | 1 | |
|======|=======|=====|=====|=====|=====|=====|=====
7 | | | 1 | | 4 | | |
|======|=======|=====|=====|=====|=====|=====|=====
8 | xx 5 | | | | | | |
|======|=======|=====|=====|=====|=====|=====|=====
9 | | | | | | | 5 |
|======|=======|=====|=====|=====|=====|=====|=====
∞ | | | | | | | |
AVERAGE동적 용어의 의미에서 모든 유효한 행 을 얻는 가장 최적의 방법은 무엇입니까 (알 수없는 행 수 및 알 수없는 열 수)?
답변
질문
레벨 1:
C2 : G 범위의 5 개 셀 모두에 값이있는 경우 :
=QUERY(QUERY(C2:G, "select (C+D+E+F+G)/5"), "offset 1", )
그렇지 않은 경우 행을 건너 뜁니다.
빈 셀이 0으로 간주되는 경우 :
=INDEX(QUERY(QUERY({C2:G*1}, "select (Col1+Col2+Col3+Col4+Col5)/5"), "offset 1", ))
0 값을 제거하려면 IFERROR(1/(1/...))래핑 을 사용합니다 .
=INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1},
"select (Col1+Col2+Col3+Col4+Col5)/5"), "offset 1", ))))
Col참조를 동적 으로 만들기 위해 다음을 수행 할 수 있습니다.
=INDEX(IFERROR(1/(1/QUERY(QUERY({C2:G*1},
"select "&
"("&JOIN("+", "Col"&ROW(INDIRECT("1:"&COLUMNS(C:G))))&")/"&COLUMNS(C:G)),
"offset 1", ))))
2 단계:
빈 셀이 0으로 간주되지 않고 건너 뛸 수없는 경우 :
=INDEX(TRANSPOSE(QUERY(TRANSPOSE(E2:I),
"select "&TEXTJOIN(",", 1, IF(A2:A="",,
"avg(Col"&ROW(A2:A)-ROW(A2)+1&")")))),, 2)
이것은 A 열에 따라 달라 지므로 A 열의 누락 된 값은 결과를 상쇄합니다.
재미있는 사실 !! 우리가 바꿀 수 avg로 max또는 min:
A 열의 제한에서 해제하고 유효한 행에 대해 작동하도록 만듭니다.
=INDEX(IFERROR(1/(1/TRANSPOSE(QUERY(TRANSPOSE(
IF(TRIM(TRANSPOSE(QUERY(TRANSPOSE(C2:G),,9^9)))="", C2:G*0, C2:G)),
"select "&TEXTJOIN(",", 1,
"avg(Col"&ROW(A2:A)-ROW(A2)+1&")"))))),, 2)
범위 내에있는 0이 평균화되어서는 안되는 경우 작은 IF 문을 추가 할 수 있습니다.
=INDEX(IFERROR(1/(1/TRANSPOSE(QUERY(TRANSPOSE(
IF(TRIM(TRANSPOSE(QUERY(TRANSPOSE(
IF(C2:G>0, C2:G, )),,9^9)))="", C2:G*0,
IF(C2:G>0, C2:G, ))),
"select "&TEXTJOIN(",", 1,
"avg(Col"&ROW(A2:A)-ROW(A2)+1&")"))))),, 2)
여기서 우리 는 주어진 범위의 모든 값을 가져 와서 하나의 단일 열에 집중시키는 소위 "수직 쿼리 스매시" 를 사용했습니다. 여기서 각 행의 모든 셀은 빈 공간과 부산물로 결합됩니다.
=FLATTEN(QUERY(TRANSPOSE(C2:G),,9^9))
이 외에도 "수평 쿼리 스매시"도 있습니다 .
=QUERY(C2:G,,9^9)
또한 범위의 모든 셀을 하나의 단일 셀에 넣는 "ultimate 360 ° double query smash" :
=QUERY(FLATTEN(QUERY(TRANSPOSE(C2:G),,9^9)),,9^9)
마지막으로 행보다 열의 우선 순위를 지정하는 "악명 높은 음의 360 ° 역 이중 쿼리 스매시" :
=QUERY(FLATTEN(QUERY(C2:G,,9^9)),,9^9)
모든 쿼리 스매시 이름은 물론 저작권이 있습니다.
다시 주제로 ... 위에서 언급했듯이 범위의 모든 행당 셀은 빈 공간으로도 결합되어 있으므로 값 사이에 두 배 또는 여러 공간이 생기는 상황이 발생했습니다. 이를 수정하기 위해 주어진 범위에서 빈 행에 0 값을 할당 TRIM하는 간단한 IF문을 사용 하고 도입합니다 . 오프셋에 대응하려면 :
MMULT
레벨 3 :
MMULT덧셈, 뺄셈, 곱셈, 나눗셈을 수행 할 수있는 일종의 무거운 클래스 수식입니다. 배열 / 행렬에서 누계를 누계까지도 수행 할 수 있습니다.하지만 데이터 세트가 클수록 수식 계산이 느려집니다 ( MMULT빈 행에서도 + - × ÷작업 을 수행하는 데 시간이 걸리기 때문입니다. ) ... 양방향으로 무한한 다이나믹 레인지를 사용하지 않는 한 ...
주어진 범위의 값이있는 마지막 행을 얻으려면 :
=INDEX(MAX(IF(TRIM(FLATTEN(QUERY(TRANSPOSE(
INDIRECT("C2:"&ROWS(A:A))),,9^9)))="",,ROW(A2:A))))
주어진 범위의 값이있는 마지막 열을 얻으려면 :
=INDEX(MAX(IF(TRIM(QUERY(INDIRECT("C2:"&ROWS(A:A)),,9^9))="",,COLUMN(C2:2))))
이제 간단한 방법으로 구성 할 수 있습니다.
=INDIRECT("C2:"&ADDRESS(9, 7))
다음과 동일합니다.
=INDEX(INDIRECT("C2:"&ADDRESS(MAX(IF(TRIM(FLATTEN(QUERY(TRANSPOSE(
INDIRECT("C2:"&ROWS(A:A))),,9^9)))="",,ROW(A2:A))),
MAX(IF(TRIM(QUERY(INDIRECT("C2:"&ROWS(A:A)),,9^9))="",,COLUMN(C2:2))))))
또는 더 짧은 대안 :
=INDEX(INDIRECT("C2:"&ADDRESS(
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*ROW(A2:A)),
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2)))))
따라서 단순화 된 MMULT 공식은 다음과 같습니다.
=ARRAYFORMULA(IFERROR(
MMULT(N( C2:G9), ROW(INDIRECT("C1:C"&COLUMNS(C:G)))^0)/
MMULT(N(IF(C2:G9<>"", 1, )), ROW(INDIRECT("C1:C"&COLUMNS(C:G)))^0)))
범위에서 0 값을 제외하려는 경우 공식은 다음과 같습니다.
=ARRAYFORMULA(IFERROR(
MMULT(N( C2:G9), ROW(INDIRECT("C1:C"&COLUMNS(C:G)))^0)/
MMULT(N(IF(C2:G9>0, 1, )), ROW(INDIRECT("C1:C"&COLUMNS(C:G)))^0)))
레벨 4 :
위의 모든 것을 결합하여 무한히 역동적이고 유효한 데이터 세트로 제한됩니다.
=INDEX(IFERROR(
MMULT(N( INDIRECT("C2:"&ADDRESS(
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*ROW(A2:A)),
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2))))), ROW(INDIRECT("C1:C"&
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2))-(COLUMN(C2)-1)))^0)/
MMULT(N(IF(INDIRECT("C2:"&ADDRESS(
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*ROW(A2:A)),
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2))))<>"", 1, )), ROW(INDIRECT("C1:C"&
MAX((INDIRECT("C2:"&ROWS(A:A))<>"")*COLUMN(C2:2))-(COLUMN(C2)-1)))^0)))
다시 말하지만 범위에 0이있는 셀은 포함하지 않습니다.
명예로운 언급 :
@Erik Tyler 수준 :
이전 식의 정반대가 실행하는 것입니다 MMULT에
- 대신 총 면적
C2:?(all rows, all columns) - 대량 계산을 피하는 유효한 영역
C2:?(excluding empty rows and columns)0 × 0 = 0
0 포함 :
=INDEX(IFERROR(
MMULT( INDIRECT("C2:"&ROWS(C:C))*1, SEQUENCE(COLUMNS(C2:2))^0)/
MMULT(IF(INDIRECT("C2:"&ROWS(C:C))<>"", 1)*1, SEQUENCE(COLUMNS(C2:2))^0)))
0 제외 :
=INDEX(IFERROR(
MMULT( INDIRECT("C2:"&ROWS(C:C))*1, SEQUENCE(COLUMNS(C2:2))^0)/
MMULT(IF(INDIRECT("C2:"&ROWS(C:C))>0, 1)*1, SEQUENCE(COLUMNS(C2:2))^0)))
@kishkin 수준 :
고정 범위의 C2:G9경우 MMULT평균은 다음과 같습니다.
=INDEX(IFERROR(
MMULT( C2:G9*1, FLATTEN(COLUMN(C:G))^0)/
MMULT((C2:G9>0)*1, FLATTEN(COLUMN(C:G))^0)))
=INDEX(IFNA(VLOOKUP(ROW(C2:C),
QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&C2:J), "×"),
"select Col1,avg(Col2)
where Col2 is not null
group by Col1"), 2, )))
@MattKing 수준 :
=INDEX(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&OFFSET(C2,,,9^9, 9^9)), "×"),
"select avg(Col2)
group by Col1
label avg(Col2)''"))
0 제외 :
=INDEX(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&OFFSET(C2,,,9^9, 9^9)), "×"),
"select avg(Col2)
where Col2 <> 0
group by Col1
label avg(Col2)''"))
빈 셀 포함 :
=INDEX(IFERROR(1/(1/QUERY(SPLIT(FLATTEN(ROW(C2:C)&"×"&OFFSET(C2,,,9^9, 9^9)*1), "×"),
"select avg(Col2)
group by Col1
label avg(Col2)''"))))
여기에 많은 시간을 투자했습니다. 나는 사람들이 그것을 고맙게 생각하고 당신이 자신을 위해서가 아니라 다른 사람들을 위해 그것을 더 많이했으면합니다.
최종 공식을 살펴보면 다음과 같은 결과가 나와야합니다 (예제에서와 같이 C2 :?로 데이터 제공).
B2에서 (0 포함) :
=ArrayFormula(IFERROR(MMULT(INDIRECT("C2:"&ROWS(C:C))*1,SEQUENCE(COLUMNS(C1:1),1,1,0))/ MMULT(IF(INDIRECT("C2:"&ROWS(C:C))<>"",1,0),SEQUENCE(COLUMNS(C1:1),1,1,0))))
B2에서 (0 제외) :
=ArrayFormula(IFERROR(MMULT(INDIRECT("C2:"&ROWS(C:C))*1,SEQUENCE(COLUMNS(C1:1),1,1,0))/ MMULT(IF(INDIRECT("C2:"&ROWS(C:C))<>0,1,0),SEQUENCE(COLUMNS(C1:1),1,1,0))))
업데이트 : 원래 게시물에서 공식을 업데이트했습니다. 데이터의 누락 된 값이 분할에서 벗어나지 않도록 ROW ()가 항상 먼저 와야합니다.
=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(ROW(C2:C)&"|"&OFFSET(C2,,,9^9,9^9)),"|"),"select AVG(Col2) group by Col1 label AVG(Col2)''"))
내가 질문을 오해하지 않는 한 작동해야합니다.
vlookup, mmults 또는 필터 등이 필요하지 않습니다.
@ player0의 답변에 약간 추가하려고 노력할 것입니다. 그리고 이것을 최적화하는 것에 대한 의견을 정말 감사하겠습니다.
데이터 범위 내에 빈 행과 열이 많이있는 경우에서 제외 할 수도 있습니다 MMULT.
1 단계-빈 행 필터링
데이터 범위가 있습니다. C2아래에서 마지막 행까지, 오른쪽에서 마지막 열 (즉 J:J)까지입니다. 를 사용하겠습니다 C2:K. 자세한 설명은 아래를 참조하십시오.
이 수식은 비어 있지 않은 셀이 하나 이상있는 행 번호 배열을 제공합니다. 또한 0빈 행 이있는 경우가 있지만이 배열에서 검색하는 것은 중요하지 않거나 중요 할 때 필터링합니다.
=ARRAYFORMULA(
UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K)))
)
따라서 데이터 범위에서 빈 행을 필터링하기 위해 FILTER위의 배열에 행이 있는지 확인하고 그 경우에는 그대로 둡니다.
=ARRAYFORMULA(
FILTER(
C2:K*1,
MATCH(
ROW(C2:K),
UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
0
)
)
)
2 단계-빈 열 필터링
비어 있지 않은 열 번호 만 배열을 얻으려면 거의 동일한 공식을 사용할 수 있습니다.
=ARRAYFORMULA(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))))
)
마지막에 세부 정보를 보는 SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))대신 왜 사용됩니다 COLUMN(C2:K).
빈 열을 필터링하기 위해 조건 FILTER과 함께 사용 하여 MATCH배열에서 열 번호를 검색합니다.
=ARRAYFORMULA(
FILTER(
C2:K*1,
MATCH(
SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
0
)
)
)
빈 행과 빈 열을 필터링하려면 두 개의 FILTERs 만 사용합니다 .
=ARRAYFORMULA(
FILTER(
FILTER(
C2:K*1,
MATCH(
ROW(C2:K),
UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
0
)
),
MATCH(
SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
0
)
)
)
원래 데이터 범위는 내부적으로 다음과 같습니다.
3 단계-수행 MMULT
이제 MMULT해당 데이터 세트를 사용하여 평균을 계산할 수 있습니다 .
=ARRAYFORMULA(
MMULT(
FILTER(
FILTER(
C2:K*1,
MATCH(
ROW(C2:K),
UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
0
)
),
MATCH(
SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
0
)
),
SEQUENCE(
ROWS(
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)
),
1,
1,
0
)
) /
MMULT(
FILTER(
FILTER(
(C2:K <> "")*1,
MATCH(
ROW(C2:K),
UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
0
)
),
MATCH(
SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
0
)
),
SEQUENCE(
ROWS(
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)
),
1,
1,
0
)
)
)
원래 데이터 행과 관련하여 약간 떨어져 있습니다.
4 단계-AVERAGE 열 채우기
평균을 원래 데이터 행과 일치시키기 위해 VLOOKUP다음과 같이 사용할 수 있습니다 .
=ARRAYFORMULA(
IFNA(VLOOKUP(
SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2)),
{
QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0"),
MMULT(
...
) /
MMULT(
...
)
},
2,
0
))
)
어디
SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2))두 번째 행에서 비어 있지 않은 마지막 행 번호의 배열입니다. 모든 행을 빈 문자열로 채우지는 않을 것입니다.QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0")0필터링 된 항목이 검색 키로 사용되는 비어 있지 않은 행 번호의 배열입니다 .IFNA빈 데이터 행과 함께 배치 할 빈 문자열을 반환합니다.
최종 공식
함께 모아서:
=ARRAYFORMULA(
IFNA(VLOOKUP(
SEQUENCE(MAX((C2:K <> "") * ROW(C2:K)) - 1, 1, ROW(C2)),
{
QUERY(UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))), "WHERE Col1 <> 0"),
MMULT(
FILTER(
FILTER(
C2:K*1,
MATCH(
ROW(C2:K),
UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
0
)
),
MATCH(
SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
0
)
),
SEQUENCE(
ROWS(
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)
),
1,
1,
0
)
) /
MMULT(
FILTER(
FILTER(
(C2:K <> "")*1,
MATCH(
ROW(C2:K),
UNIQUE(FLATTEN((C2:K <> "") * ROW(C2:K))),
0
)
),
MATCH(
SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)),
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
0
)
),
SEQUENCE(
ROWS(
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)
),
1,
1,
0
)
)
},
2,
0
))
)
몇 가지 세부 사항
INDEXARRAYFORMULA간결함 대신 사용할 수 있지만 (@ player0에게 감사하며 몇 달 전에 가르쳐주었습니다), 저는ARRAYFORMULA.- 명확성을 위해
SEQUENCE열 또는1s 행을 명시 적으로 구성하는 데 사용 합니다 . 예를 들면 이건
SEQUENCE(
ROWS(
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)
),
1,
1,
0
)
대체 될 수있다
SIGN(
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)
)
조금 더 짧습니다. @ player0가 다음과 같은 힘으로 올리는 방법도 있습니다 0.
QUERY(
UNIQUE(FLATTEN((C2:K <> "") * SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2)))),
"WHERE Col1 <> 0"
)^0
그러나 (그것은 단지 나의 추측 일 뿐이다) 나는 SEQUENCE의 내부 구현이 권력을 올리는 작업보다 더 간단해야 한다고 생각 한다.
C2:K시트에 실제로 존재하는 것보다 한 열 더 많은 범위 를 사용합니다 . 오른쪽에있는C2모든 열과 그 아래에있는 모든 행 의 범위를 제공 할뿐만 아니라 시트 오른쪽에 다른 열을 추가하는 경우에도 업데이트됩니다 . 데모 . 강조되지는 않지만. 이것은C2:K거의 완벽하게 (ZZZ시트에 실제로 열 이있는 경우 문제가 있음 ) 이러한 접근 방식을 대체 할 수 있습니다.
INDIRECT("C2:" & ROWS(C:C))
OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))
- 사용시 작은 단점이 있습니다
C2:K. :=ARRAYFORMULA(COLUMN(C2:K))존재하지 않는 항목에 대해서도 열 번호 배열을 반환하므로=SEQUENCE(1, COLUMNS(C2:K), COLUMN(C2))대신 사용해야 합니다.
VLOOKUP및을 사용하는 행 단위 평균에 대한 간단한 대답이 있다고 생각 QUERY합니다.
이것은에 있습니다 B2:
=ARRAYFORMULA(
IFNA(
VLOOKUP(
ROW(B2:B),
QUERY(
{
FLATTEN(ROW(C2:J) + SEQUENCE(1, COLUMNS(C2:J),,)),
FLATTEN(C2:J)
},
"SELECT Col1, AVG(Col2)
WHERE Col2 IS NOT NULL
GROUP BY Col1"
),
2,
0
)
)
)
- 이것은 max, min, sum, count에 대해 쉽게 변경할 수 있습니다
QUERY. 문 내에서 집계 함수 만 변경하면 됩니다. - 열 단위 집계에 동일한 접근 방식을 사용할 수 있습니다.
FLATTEN(C2:J)다음과 같이 변경할 수 있습니다.FLATTEN(--C2:J)빈 세포를0s 로 처리합니다 .FLATTEN(IFERROR(1/(1/C2:J)))0평균에서 s 를 제외 합니다.
- 중간 빈 행이 없으면
VLOOKUP수식과 문Col1에서 제거 할 수 있습니다SELECT. VLOOKUP와 없는 짧은 버전 (@MattKing에게 감사합니다!)이 있습니다WHERE Col....
=ARRAYFORMULA(
QUERY(
{
FLATTEN(ROW(C2:J) + SEQUENCE(1, COLUMNS(C2:J),,)),
FLATTEN(IFERROR(1/(1/C2:J)))
},
"SELECT AVG(Col2)
GROUP BY Col1
LABEL AVG(Col2) ''"
)
)
C2:J열이있는 범위를 사용 I:I합니다.
C2:J시트에 실제로 존재하는 것보다 한 열 더 많은 범위 입니다. 오른쪽에있는C2모든 열과 그 아래에있는 모든 행 의 범위를 제공 할뿐만 아니라 시트 오른쪽에 다른 열을 추가하는 경우에도 업데이트됩니다 . 데모 . 강조되지는 않지만. 이것은C2:J거의 완벽하게 (실제로ZZZ시트에 열 이있는 경우 문제가 있음 ) 이러한 접근 방식을 대체 할 수 있습니다.
INDIRECT("C2:" & ROWS(C:C))
OFFSET(C2,,, ROWS(C2:C), COLUMNS(C2:2))
- 사용시 약간의 단점이 있습니다
C2:J. :=ARRAYFORMULA(0 * COLUMN(C2:J))존재하지 않는 열 번호 (를 곱한 값)에 대해서도 열 번호 배열을 반환0하므로=SEQUENCE(1, COLUMNS(C2:J),,)대신 사용해야 합니다.
@ player0, 이것에 대한 어떤 생각?