ARRAYFORMULA로 열에 수식 적용하기

일반적으로 모든 행에서 계산하려면 수식 하나를 작성한 뒤 아래로 끌어내립니다. 이 방법도 작동하지만, 새 데이터가 들어올 때마다 늘려야 하는 복사된 수식이 수백 개 남습니다.

Google 스프레드시트에는 더 나은 방법이 있습니다. 바로 ARRAYFORMULA입니다. 셀 하나에 수식 하나만 입력하면 열 전체를 한 번에 계산하고 자동으로 확장합니다.

ARRAYFORMULA는 스프레드시트에 수식을 단일 셀이 아니라 전체 범위에 적용하도록 지시합니다. 결과는 입력 범위의 행 수만큼 아래로 펼쳐집니다.

따라서 =A2*B2를 100번 반복하는 대신, A2:A와 B2:B를 참조하는 수식 하나만 작성하면 결과가 자동으로 채워집니다.

A열에 수량이 있고 B열에 가격이 있다고 가정해 보겠습니다. 행별 합계를 계산하려면 C2에 이 수식 하나를 입력합니다.

이 수식은 아래로 내려가며 각 A값에 해당하는 B값을 곱하므로 C3부터는 직접 입력할 필요가 없습니다.

A2:A100 대신 A2:A를 사용한 점에 주목하십시오. 끝이 정해지지 않은 형식은 2행부터 아래의 모든 행을 의미하므로 새로 추가된 데이터도 자동으로 포함합니다.

이것이 ARRAYFORMULA의 진정한 장점입니다. 시트를 앞으로 추가될 데이터에도 대응할 수 있게 합니다. 새 주문을 추가하면 별도의 작업 없이 합계가 나타납니다.

끝이 정해지지 않은 범위에는 데이터 아래에 있는 수천 개의 빈 행도 포함되므로, 수식이 이 행들까지 계산하려고 합니다. 그 결과 0이나 불필요한 값이 표시되는 경우가 많습니다.

행이 비어 있는지 확인하는 IF로 계산을 감싸십시오. A가 비어 있으면 빈 문자열을 반환하고, 그렇지 않으면 계산을 수행하게 합니다.

깔끔한 방법이 하나 있습니다. 열 헤더를 배열 수식 안에 포함하면 셀 하나로 열 전체를 관리할 수 있습니다. 이 수식을 C1에 입력하십시오.

첫 번째 부분은 헤더 텍스트를 출력하고 나머지 부분은 각 데이터 행을 계산합니다. ;는 두 부분을 세로로 쌓아 하나의 펼쳐지는 열로 만듭니다.

ARRAYFORMULA는 계산에만 사용되지 않습니다. 텍스트 함수에도 사용할 수 있습니다. 이름과 성으로 전체 이름 열을 만들려면 두 범위를 연결합니다.

이렇게 하면 각 행에서 A열의 이름과 B열의 성 사이에 공백을 넣어 한 번에 결합합니다.

일부 함수는 배열을 자체적으로 처리하므로 ARRAYFORMULA가 필요하지 않습니다. SUMIF, FILTER, UNIQUE, QUERY는 이미 범위에 대해 작동합니다.

일반적으로 단일 셀에서 작동하는 연산(예: *, &, LEFT)을 모든 행에서 실행해야 할 때 주로 ARRAYFORMULA를 사용합니다.

LEFT, UPPER, TRIM과 같은 함수는 일반적으로 한 셀에 적용됩니다. ARRAYFORMULA 안에서는 전체 범위에 적용됩니다.

이렇게 하면 A열의 모든 이메일을 한 번에 대문자로 바꿀 수 있습니다. 감싸지 않으면 첫 번째 셀만 변환됩니다.

함수 이름을 항상 직접 입력할 필요는 없습니다. Google 스프레드시트에서 범위 참조가 포함된 수식을 입력한 다음 Ctrl+Shift+Enter를 누르십시오(Mac에서는 Cmd+Shift+Enter).

스프레드시트가 수식을 자동으로 ARRAYFORMULA로 감쌉니다. 끌어내린 수식을 하나의 자동 확장 수식으로 빠르게 바꾸는 방법입니다.

요소별 연산에서는 범위의 높이가 같아야 합니다. A2:A와 B2:B50을 섞어 사용하면 오류가 발생하거나 결과가 서로 맞지 않을 수 있습니다.

두 범위를 모두 끝이 정해지지 않은 형태(A2:A와 B2:B)로 사용하거나, 둘 다 같은 크기로 고정하십시오. 일관성을 유지하면 출력이 행마다 정확히 맞습니다.

ARRAYFORMULA에 대한 지식을 테스트해 보세요.

하나의 수식으로 전체 열을 계산하는 방법을 배웠습니다:

• ARRAYFORMULA는 전체 범위에 걸쳐 연산을 적용합니다
• A2:A와 같은 열린 범위는 새 행을 자동으로 포함합니다
• 빈 셀을 건너뛰려면 IF(A2:A = "", "", ...)로 감싸세요
• 수학 연산, 텍스트 결합, UPPER와 같은 함수에도 사용할 수 있습니다
• Ctrl/Cmd와 Shift와 Enter를 함께 누르면 이 래퍼가 자동으로 추가됩니다

셀 하나로 살아 움직이는 열을 만들 수 있습니다.