Google 스프레드시트에서 배열 수식을 사용하는 방법

2023년 초, Google은 배열 작업을 위한 8가지 기능을 포함하여 Sheets에 몇 가지 새로운 기능을 도입했습니다. 이러한 함수를 사용하면 배열을 행이나 열로 변환하거나, 행이나 열에서 새 배열을 만들거나, 현재 배열을 추가할 수 있습니다.

배열 작업에 더 많은 유연성을 제공하고 기본 ARRAYFORMULA 함수를 넘어서 Google 스프레드시트의 수식 과 함께 이러한 배열 함수를 사용하는 방법을 살펴보겠습니다 .

목차

팁 : Microsoft Excel을 사용하는 경우 이러한 기능 중 일부가 익숙해 보일 수 있습니다.

배열 변환: TOROW 및 TOCOL

단일 행이나 열로 변환하려는 데이터세트에 배열이 있는 경우 TOROW 및 TOCOL 함수를 사용할 수 있습니다.

각 함수의 구문은 TOROW(array,ignore, scan) 및 TOCOL(array,ignore,scan)과 동일하며 둘 다 첫 번째 인수만 필요합니다.

• Array : "A1:D4" 형식으로 변환하려는 배열입니다.
• Ignore : 기본적으로 무시되는 매개변수는 없지만(0) 공백을 무시하려면 1, 오류를 무시하려면 2, 공백과 오류를 무시하려면 3을 사용할 수 있습니다.
• Scan : 이 인수는 배열의 값을 읽는 방법을 결정합니다. 기본적으로 이 함수는 행별로 검색하거나 False 값을 사용하여 검색하지만 원하는 경우 True를 사용하여 열별로 검색할 수 있습니다.

TOROW 및 TOCOL 함수와 해당 공식을 사용하여 몇 가지 예를 살펴보겠습니다.

이 첫 번째 예에서는 배열 A1부터 C3까지를 가져와서 다음 수식과 함께 기본 인수를 사용하여 행으로 변환합니다.

=TOROW(A1:C3)

보시다시피 배열은 이제 한 행에 있습니다. 기본 스캔 인수를 사용했기 때문에 함수는 왼쪽에서 오른쪽(A, D, G)으로 읽은 다음 아래로 읽은 다음 완료될 때까지 다시 왼쪽에서 오른쪽(B, E, H)으로 행별로 스캔합니다.

행 대신 열별로 배열을 읽으려면 스캔 인수 에 True를 사용할 수 있습니다 . 무시 인수를 비워 두겠습니다 . 공식은 다음과 같습니다.

=TOROW(A1:C3,,TRUE)

이제 함수가 배열을 위에서 아래로(A, B, C), 위에서 아래로(D, E, F), 위에서 아래로(G, H, I) 읽는 것을 볼 수 있습니다.

TOCOL 함수는 동일한 방식으로 작동하지만 배열을 열로 변환합니다. A1부터 C3까지 동일한 범위를 사용하여 기본 인수를 사용하는 수식은 다음과 같습니다.

=목차(A1:C3)

다시 한번, scan 인수 에 대한 기본값을 사용하여 함수는 왼쪽에서 오른쪽으로 읽고 결과를 제공합니다.

행 대신 열별로 배열을 읽으려면 다음과 같이 스캔 인수 에 True를 삽입하십시오.

=TOCOL(A1:C3,,TRUE)

이제 함수가 대신 위에서 아래로 배열을 읽는 것을 볼 수 있습니다.

행이나 열에서 새 배열 만들기: CHOOSEROWS 및 CHOOSECOLS

기존 배열에서 새 배열을 생성할 수도 있습니다. 이를 통해 다른 셀 범위의 특정 값만 포함하는 새 셀 범위를 만들 수 있습니다. 이를 위해 CHOOSEROWS 및 CHOOSECOLS Google 스프레드시트 기능을 사용합니다 .

각 함수의 구문은 CHOOSEROWS(array, row_num, row_num_opt) 및 CHOOSECOLS(array, col_num, col_num_opt)와 비슷합니다. 여기서 처음 두 인수는 둘 다에 필요합니다.

• Array : "A1:D4" 형식의 기존 배열입니다.
• Row_num 또는 Col_num : 반환하려는 첫 번째 행 또는 열의 번호입니다.
• Row_num_opt 또는 Col_num_opt : 반환하려는 추가 행 또는 열의 번호입니다. Google에서는 아래에서 위로 행을 반환하거나 오른쪽에서 왼쪽으로 열을 반환하려면 음수를 사용할 것을 제안합니다.

CHOOSEROWS 및 CHOOSECOLS와 해당 공식을 사용하는 몇 가지 예를 살펴보겠습니다.

이 첫 번째 예에서는 A1부터 B6까지의 배열을 사용합니다. 우리는 행 1, 2, 6의 값을 반환하려고 합니다. 수식은 다음과 같습니다.

=선택행(A1:B6,1,2,6)

보시다시피, 우리는 새 배열을 만들기 위해 이 세 개의 행을 받았습니다.

또 다른 예로 동일한 배열을 사용하겠습니다. 이번에는 1, 2, 6행을 반환하되 2, 6행은 역순으로 반환하려고 합니다. 양수 또는 음수를 사용하여 동일한 결과를 얻을 수 있습니다.

음수를 사용하면 다음 수식을 사용합니다.

=CHOOSEROWS(A1:B6,1,-1,-5)

설명하자면 1은 반환할 첫 번째 행이고, -1은 반환할 두 번째 행(맨 아래에서 시작하는 첫 번째 행), -5는 맨 아래에서 다섯 번째 행입니다.

양수를 사용하면 다음 공식을 사용하여 동일한 결과를 얻을 수 있습니다.

=선택행(A1:B6,1,6,2)

CHOOSECOLS 함수는 행 대신 열에서 새 배열을 만들 때 사용한다는 점을 제외하면 유사하게 작동합니다.

A1부터 D6까지의 배열을 사용하면 다음 수식으로 열 1(열 A)과 4(열 D)를 반환할 수 있습니다.

=CHOOSECOLS(A1:D6,1,4)

이제 우리는 두 개의 열만 포함하는 새로운 배열을 갖게 되었습니다.

또 다른 예로, 열 4부터 시작하는 동일한 배열을 사용합니다. 그런 다음 먼저 열 1과 2에 2(열 B)를 추가합니다. 양수 또는 음수를 사용할 수 있습니다.

=CHOOSECOLS(A1:D6,4,2,1)

=CHOOSECOLS(A1:D6,4,-3,-4)

위 스크린샷에서 볼 수 있듯이 수식 입력줄이 아닌 셀에 수식이 있으면 두 옵션을 모두 사용하여 동일한 결과를 얻을 수 있습니다.

참고 : Google 에서는 결과 배치를 반대로 하기 위해 음수를 사용할 것을 제안하므로 양수를 사용하여 올바른 결과를 받지 못하는 경우 이 점을 염두에 두십시오.

새 배열을 만들기 위한 래핑: WRAPROWS 및 WRAPCOLS

기존 배열에서 새 배열을 만들고 각각의 특정 수의 값으로 열이나 행을 래핑하려는 경우 WRAPROWS 및 WRAPCOLS 함수를 사용할 수 있습니다.

각 함수의 구문은 WRAPROWS(범위, 개수, 패드) 및 WRAPCOLS(범위, 개수, 패드) 와 동일합니다 . 여기서 처음 두 인수는 둘 다에 필요합니다.

• 범위 : 배열에 사용하려는 기존 셀 범위("A1:D4" 형식)입니다.
• 개수 : 각 행 또는 열의 셀 수입니다.
• Pad : 이 인수를 사용하여 빈 셀에 텍스트나 단일 값을 배치할 수 있습니다. 이는 빈 셀에 대해 표시되는 #N/A 오류를 대체합니다. 텍스트나 값을 따옴표 안에 포함하세요.

WRAPROWS 및 WRAPCOLS 함수와 해당 공식을 사용하여 몇 가지 예를 살펴보겠습니다.

이 첫 번째 예에서는 A1부터 E1까지의 셀 범위를 사용합니다. 각 행에 세 개의 값이 포함된 행을 래핑하는 새 배열을 만듭니다. 공식은 다음과 같습니다.

=WRAPROWS(A1:E1,3)

보시다시피 각 행에 세 개의 값이 있는 올바른 결과를 가진 새로운 배열이 있습니다. 배열에 빈 셀이 있으므로 #N/A 오류가 표시됩니다. 다음 예에서는 pad 인수를 사용하여 오류를 "None" 텍스트로 대체합니다. 공식은 다음과 같습니다.

=WRAPROWS(A1:E1,3,"없음")

이제 Google Sheets 오류 대신 단어를 볼 수 있습니다.

WRAPCOLS 함수는 기존 셀 범위에서 새 배열을 생성하여 동일한 작업을 수행하지만 행 대신 열을 래핑하여 이를 수행합니다.

여기서는 A1부터 E3까지 동일한 배열을 사용하여 각 열의 세 가지 값으로 열을 래핑합니다.

=랩콜(A1:E1,3)

WRAPROWS 예제와 마찬가지로 올바른 결과를 받았지만 빈 셀로 인해 오류도 발생했습니다. 이 수식을 사용하면 pad 인수를 사용하여 "Empty"라는 단어를 추가할 수 있습니다.

=WRAPCOLS(A1:E1,3,"비어 있음")

이 새로운 배열은 오류 대신 단어를 사용하면 훨씬 더 좋아 보입니다.

결합하여 새 어레이 생성: HSTACK 및 VSTACK

우리가 살펴볼 마지막 두 가지 함수는 배열을 추가하는 것입니다. HSTACK 및 VSTACK을 사용하면 두 개 이상의 셀 범위를 함께 추가하여 수평 또는 수직으로 단일 배열을 형성할 수 있습니다.

각 함수의 구문은 HSTACK(range1, range2,...) 및 VSTACK(range1, range2,...) 과 동일하며 첫 번째 인수만 필요합니다. 그러나 첫 번째 범위와 다른 범위를 결합하는 두 번째 인수를 거의 항상 사용하게 됩니다.

• Range1 : 배열에 사용하려는 첫 번째 셀 범위로, "A1:D4" 형식입니다.
• Range2,… : 배열을 만들기 위해 첫 번째 셀 범위에 추가하려는 두 번째 셀 범위입니다. 세 개 이상의 셀 범위를 결합할 수 있습니다.

HSTACK 및 VSTACK과 해당 공식을 사용하는 몇 가지 예를 살펴보겠습니다.

첫 번째 예에서는 다음 수식을 사용하여 A1~D2 범위와 A3~D4 범위를 결합합니다.

=HSTACK(A1:D2,A3:D4)

데이터 범위가 결합되어 단일 수평 배열을 형성하는 것을 볼 수 있습니다 .

VSTACK 기능의 예를 들어 세 가지 범위를 결합합니다. 다음 수식을 사용하여 A2~C4, A6~C8, A10~C12 범위를 사용합니다.

=VSTACK(A2:C4,A6:C8,A10:C12)

이제 단일 셀의 수식을 사용하여 모든 데이터가 포함된 하나의 배열이 생겼습니다.

쉽게 배열 조작

SUM 함수나 IF 함수와 같은 특정 상황에서는 ARRAYFORMULA를 사용할 수 있지만 이러한 추가 Google Sheets 배열 수식을 사용하면 시간을 절약할 수 있습니다. 단일 배열 수식을 사용하여 원하는 대로 정확하게 시트를 정렬하는 데 도움이 됩니다.

배열이 아닌 함수를 포함하는 이와 같은 더 많은 튜토리얼을 보려면 Google 스프레드시트에서 COUNTIF 또는 SUMIF 함수를 사용하는 방법을 살펴보세요 .