구글 시트 ARRAYFORMULA로 반복 작업 끝내기 – 자동화 핵심 함수

엑셀보다 더 강력한 구글 시트의 자동화 기능으로 업무 효율성을 높이는 방법

안녕하세요 여러분! 혹시 이런 적 있으시죠?!
구글 시트에서 같은 공식을 수백 개의 셀에 일일이 복사-붙여넣기 하느라 시간을 낭비하고 계신가요?
아니면 새 데이터가 추가될 때마다 공식을 다시 입력해야 하는 번거로움에 지치셨나요?
저도 예전에는 그랬어요! 방대한 데이터를 관리하면서 매번 반복 작업에 시간을 허비했죠.
하지만 ARRAYFORMULA라는 마법 같은 함수를 알게 된 후로는 작업 시간이 절반으로 줄었습니다.
오늘은 구글 시트의 숨겨진 보석 같은 기능, ARRAYFORMULA에 대해 알려드릴게요.
이 함수 하나만 제대로 알아도 여러분의 데이터 작업 방식이 완전히 바뀔 거예요!

📋 목차

ARRAYFORMULA의 기본 개념과 작동 원리 🔄

ARRAYFORMULA의 실용적인 활용 사례 💼

고급 ARRAYFORMULA 테크닉 ⚙️

자주 발생하는 오류와 해결 방법 🛠️

엑셀 vs 구글 시트: ARRAYFORMULA의 우위성 🏆

자주 묻는 질문 ❓

ARRAYFORMULA의 기본 개념과 작동 원리 🔄

ARRAYFORMULA는 구글 시트에서 가장 강력한 함수 중 하나로, 단일 공식으로 여러 셀에 계산을 적용할 수 있게 해줍니다.
쉽게 말해서, 여러 행이나 열에 같은 공식을 반복해서 입력하는 대신 한 번만 입력하면 되는 마법 같은 함수죠!
이 함수의 기본 구문은 =ARRAYFORMULA(범위 또는 수식)입니다.

"ARRAYFORMULA를 배운 후, 우리 팀의 주간 보고서 작성 시간이 3시간에서 30분으로 줄었어요. 이제는 데이터가 들어오는 즉시 모든 계산이 자동으로 업데이트됩니다." - 김민석, 마케팅 데이터 분석가

ARRAYFORMULA의 핵심 장점

ARRAYFORMULA를 사용하면 다음과 같은 놀라운 이점을 얻을 수 있습니다:

✅ 시간 절약: 수백, 수천 개의 셀에 동일한 공식을 반복 입력할 필요가 없습니다.
✅ 자동 확장: 새 데이터가 추가되면 공식이 자동으로 확장됩니다.
✅ 오류 감소: 복사-붙여넣기 과정에서 발생할 수 있는 실수를 방지합니다.
✅ 파일 용량 최적화: 단일 공식으로 여러 계산을 처리하여 시트의 성능을 향상시킵니다.
✅ 유지 관리 용이성: 공식을 한 곳에서만 수정하면 전체가 업데이트됩니다.

기본 사용법: 첫 번째 ARRAYFORMULA 만들기

가장 간단한 예시부터 시작해볼까요?
A열에 숫자가 있고, 각 숫자를 2배로 만들고 싶다고 가정해봅시다.
일반적인 방법: B1에 =A1*2를 입력한 후, B열의 모든 셀에 복사-붙여넣기
ARRAYFORMULA 방법: B1에 =ARRAYFORMULA(A:A*2) 단 한 번만 입력!
이렇게 하면 A열에 있는 모든 숫자가 자동으로 2배로 계산되어 B열에 표시됩니다.
심지어 나중에 A열에 새 데이터를 추가해도 B열의 값이 자동으로 업데이트됩니다!

    =ARRAYFORMULA(A2:A100*2)  // A2부터 A100까지의 모든 값을 2배로
    =ARRAYFORMULA(A2:A*2)     // A2부터 A열의 끝까지 모든 값을 2배로
    

ARRAYFORMULA의 실용적인 활용 사례 💼

이론은 충분히 살펴봤으니, 실제로 업무에서 ARRAYFORMULA를 어떻게 활용할 수 있는지 알아볼까요?
다음은 다양한 비즈니스 상황에서 ARRAYFORMULA가 어떻게 시간을 절약해주는지 보여주는 실용적인 예시들입니다.

1. 판매 데이터 자동 계산

온라인 쇼핑몰을 운영한다고 가정해봅시다.
A열에는 상품 이름, B열에는 단가, C열에는 판매 수량이 있습니다.
총 판매액(단가 × 수량)을 계산하고, 여기에 10%의 부가가치세를 더한 최종 금액을 구하려면:

    // D1에 "총 판매액" 헤더를 입력하고
    // D2에 다음 공식을 입력:
    =ARRAYFORMULA(IF(ROW(B2:B)=2, "총 판매액", IF(B2:B="",,B2:B*C2:C)))

    // E1에 "세금 포함 금액" 헤더를 입력하고
    // E2에 다음 공식을 입력:
    =ARRAYFORMULA(IF(ROW(D2:D)=2, "세금 포함 금액", IF(D2:D="",,D2:D*1.1)))
    

이 공식의 장점은 무엇일까요?
새로운 판매 데이터를 추가하면 총 판매액과 세금 포함 금액이 자동으로 계산됩니다.
헤더를 보존하면서 데이터가 있는 행에만 계산을 적용합니다.
빈 셀에는 공백을 유지하여 시트가 깔끔하게 유지됩니다.

2. 이메일 주소 자동 생성

A열에 직원 이름(예: 홍길동), B열에 성(예: 홍), C열에 회사 도메인(예: company.com)이 있다면,
다음과 같은 ARRAYFORMULA로 모든 직원의 이메일 주소를 자동으로 생성할 수 있습니다:

    // D2에 다음 공식을 입력:
    =ARRAYFORMULA(IF(LEN(A2:A), LOWER(SUBSTITUTE(A2:A, " ", ".")) & "@" & C2:C, ""))
    

이 공식은 직원 이름의 공백을 마침표로 바꾸고, 소문자로 변환한 다음 "@"와 회사 도메인을 붙여 이메일 주소를 생성합니다.
예를 들어, "홍 길동"이라는 이름은 "홍.길동@company.com"으로 변환됩니다.

이름	성	도메인	자동 생성된 이메일
홍 길동	홍	company.com	홍.길동@company.com
김 철수	김	company.com	김.철수@company.com
이 영희	이	company.com	이.영희@company.com

3. 조건부 계산 자동화

ARRAYFORMULA는 IF 함수와 결합하여 강력한 조건부 계산을 수행할 수 있습니다.
예를 들어, 학생들의 시험 점수(A열)에 따라 등급을 자동으로 부여하는 공식을 만들어 봅시다:

    =ARRAYFORMULA(
      IF(A2:A="", "",
        IF(A2:A>=90, "A",
          IF(A2:A>=80, "B",
            IF(A2:A>=70, "C",
              IF(A2:A>=60, "D", "F")
            )
          )
        )
      )
    )
    

이 공식은 시험 점수가 90점 이상이면 "A", 80점 이상이면 "B" 등으로 등급을 자동 부여합니다.
새로운 학생 점수가 추가되면 등급도 자동으로 계산됩니다.
이처럼 ARRAYFORMULA는 단순 계산뿐만 아니라 복잡한 조건부 로직도 처리할 수 있습니다.

고급 ARRAYFORMULA 테크닉 ⚙️

기본 사용법에 익숙해졌다면, 이제 ARRAYFORMULA의 고급 테크닉을 알아볼 차례입니다.
이 섹션에서는 더 복잡한 데이터 처리와 자동화 작업을 위한 고급 기술을 소개합니다.

여러 열 함께 처리하기

ARRAYFORMULA는 여러 열의 데이터를 동시에 처리할 수 있습니다.
예를 들어, 이름(A열)과 성(B열)을 결합하여 전체 이름을 만드는 공식:

    =ARRAYFORMULA(IF(LEN(A2:A), B2:B & " " & A2:A, ""))
    

이 공식은 이름이 있는 행에만 적용되고, 빈 행은 건너뜁니다.
더 복잡한 예로, 세 개의 열(제품명, 단가, 수량)을 사용하여 주문 내역 요약을 자동 생성해 봅시다:

    =ARRAYFORMULA(
      IF(ROW(A2:A)=2, "주문 요약",
        IF(AND(LEN(A2:A), LEN(B2:B), LEN(C2:C)),
          A2:A & " - " & C2:C & "개 (₩" & TEXT(B2:B*C2:C, "#,##0") & ")",
          ""
        )
      )
    )
    

이 공식은 "아이폰 13 - 2개 (₩2,400,000)"와 같은 형식으로 주문 요약을 자동 생성합니다.

💡 TIP: ARRAYFORMULA 내에서 여러 열을 처리할 때는 각 열의 범위가 같은 크기여야 합니다. 예를 들어 A2:A10과 B2:B10은 같은 크기지만, A2:A10과 B2:B15는 다른 크기입니다.

자주 발생하는 오류와 해결 방법 🛠️

ARRAYFORMULA는 강력하지만, 사용하다 보면 몇 가지 흔한 오류에 직면할 수 있습니다.
여기서는 가장 자주 발생하는 문제와 그 해결책을 알아보겠습니다.
이 섹션을 통해 ARRAYFORMULA 사용 시 발생하는 대부분의 문제를 해결할 수 있을 것입니다.

1. #VALUE! 오류

가장 흔히 볼 수 있는 오류 중 하나로, 주로 데이터 유형이 맞지 않을 때 발생합니다.
예를 들어, 숫자와 텍스트를 곱하려고 할 때 이 오류가 나타납니다.

💎 해결 방법:
IF 문을 사용하여 데이터 유형을 확인하고 처리하세요. 예:

=ARRAYFORMULA(IF(ISNUMBER(A2:A), A2:A*2, "숫자가 아님"))
            

이 공식은 A열의 값이 숫자인 경우에만 2를 곱하고, 그렇지 않으면 "숫자가 아님"을 표시합니다.

2. 계산이 일부 셀에만 적용됨

때로는 ARRAYFORMULA가 예상보다 적은 수의 셀에만 적용되는 경우가 있습니다.
이는 주로 범위 지정에 문제가 있거나, 빈 셀 처리 방식 때문에 발생합니다.

💎 해결 방법:
더 넓은 범위를 지정하거나, 열 전체를 참조하세요. 예:

=ARRAYFORMULA(A2:A*2)  // 대신
=ARRAYFORMULA(A2:A1000*2)  // 또는
=ARRAYFORMULA(A:A*2)  // 열 전체 참조
            

열 전체를 참조할 때는 성능에 영향을 줄 수 있으니 주의하세요.

3. #N/A 오류

VLOOKUP이나 MATCH 같은 함수와 ARRAYFORMULA를 함께 사용할 때 자주 발생합니다.
이는 찾고자 하는 값이 없거나, 조건이 충족되지 않을 때 발생하는 오류입니다.

💎 해결 방법:
IFERROR 함수를 사용하여 오류를 처리하세요:

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, D2:E100, 2, FALSE), "찾을 수 없음"))
            

이 공식은 VLOOKUP이 값을 찾지 못할 경우 "찾을 수 없음"을 반환합니다.

⚠️ 주의: ARRAYFORMULA는 시트의 성능에 영향을 줄 수 있습니다. 특히 매우 큰 데이터셋에 복잡한 공식을 적용할 때는 시트가 느려질 수 있습니다. 가능하면 필요한 범위만 지정하고, 너무 복잡한 중첩 공식은 피하세요.

엑셀 vs 구글 시트: ARRAYFORMULA의 우위성 🏆

많은 사람들이 엑셀에서 구글 시트로 전환하면서 가장 큰 차이점 중 하나가 바로 ARRAYFORMULA입니다.
엑셀에도 비슷한 기능이 있지만, 구글 시트의 ARRAYFORMULA는 몇 가지 면에서 확실한 우위를 보입니다.

구글 시트 ARRAYFORMULA vs 엑셀 배열 공식

기능	구글 시트 ARRAYFORMULA	엑셀 배열 공식
구문	간단하고 직관적: =ARRAYFORMULA(A1:A10*2)	복잡한 입력 방식: ={A1:A10*2} 또는 Ctrl+Shift+Enter 필요
자동 확장	데이터가 추가되면 자동으로 확장됨	동적 배열 공식(Excel 365)에서만 가능, 이전 버전에서는 불가
호환성	모든 구글 시트 버전에서 동일하게 작동	Excel 버전에 따라 작동 방식이 다름
협업	여러 사용자가 동시에 작업 가능	공유 환경에서 제한적 기능
사용 편의성	단일 함수로 간단하게 구현	복잡한 LAMBDA 또는 여러 함수 조합이 필요할 수 있음

최근 Excel 365에서는 동적 배열 공식과 LAMBDA 함수를 통해 ARRAYFORMULA와 유사한 기능을 구현할 수 있게 되었습니다.
하지만 구글 시트의 ARRAYFORMULA는 여전히 사용 편의성과 직관적인 구문에서 우위를 보입니다.
특히 클라우드 기반 협업 환경에서는 구글 시트의 ARRAYFORMULA가 훨씬 효율적입니다.

구글 시트만의 독특한 장점

구글 시트의 ARRAYFORMULA는 다음과 같은 독특한 장점을 제공합니다:

✅ 실시간 업데이트: 여러 사용자가 동시에 데이터를 수정해도 계산이 실시간으로 업데이트됩니다.
✅ 서버 측 처리: 계산이 구글 서버에서 처리되므로 로컬 컴퓨터의 성능에 덜 의존합니다.
✅ 구글 앱스 스크립트 연동: 자동화 스크립트와 함께 사용하여 더 강력한 자동화를 구현할 수 있습니다.
✅ 모바일 호환성: 모바일에서도 복잡한 ARRAYFORMULA가 정상적으로 작동합니다.

엑셀에서 구글 시트로 전환하기

엑셀에서 구글 시트로 파일을 가져올 때 배열 공식이 ARRAYFORMULA로 자동 변환되지 않을 수 있습니다.
이 경우, 다음과 같은 단계로 공식을 변환할 수 있습니다:

1. 기존 엑셀 배열 공식 파악하기엑셀에서 사용 중인 배열 공식을 확인합니다.
2. 구글 시트에 ARRAYFORMULA 적용하기엑셀의 공식을 구글 시트의 ARRAYFORMULA로 변환합니다.
3. 범위 조정하기필요에 따라 범위를 동적으로 조정합니다.
4. 테스트 및 최적화공식이 올바르게 작동하는지 확인하고 성능을 최적화합니다.

💡 TIP: 엑셀에서 구글 시트로 전환할 때는 기존 파일을 가져온 후, 하나씩 공식을 확인하고 최적화하는 것이 좋습니다. 특히 COUNTIFS, SUMIFS 같은 복잡한 조건부 함수를 사용할 때는 구글 시트에서의 동작 방식을 확인해야 합니다.

자주 묻는 질문 ❓

ARRAYFORMULA에 대해 가장 자주 묻는 질문들과 그에 대한 답변을 정리했습니다.
여러분의 궁금증을 해결하는 데 도움이 되길 바랍니다.

ARRAYFORMULA와 일반 공식의 차이점은 무엇인가요?

일반 공식은 단일 셀에만 적용되지만, ARRAYFORMULA는 여러 셀에 동시에 적용됩니다. 일반 공식을 여러 셀에 적용하려면 복사-붙여넣기 해야 하지만, ARRAYFORMULA는 한 번만 입력해도 모든 데이터에 적용되며 새로운 데이터가 추가되면 자동으로 확장됩니다.

ARRAYFORMULA가 시트 성능에 미치는 영향은 어떤가요?

ARRAYFORMULA는 여러 셀에 동시에 계산을 적용하기 때문에 시트의 성능에 영향을 줄 수 있습니다. 특히 대용량 데이터나 복잡한 중첩 함수를 사용할 때 시트가 느려질 수 있습니다. 성능 최적화를 위해서는 필요한 범위만 지정하고(열 전체보다는 필요한 부분만), 너무 복잡한 중첩 함수를 피하며, 여러 개의 단순한 ARRAYFORMULA를 사용하는 것이 좋습니다.

ARRAYFORMULA에서 빈 셀을 어떻게 처리하나요?

ARRAYFORMULA에서 빈 셀 처리는 중요한 부분입니다. IF 함수를 활용하여 빈 셀을 제어할 수 있습니다. 예를 들어, '=ARRAYFORMULA(IF(A2:A="","",A2:A*2))' 공식은 A열이 비어있으면 결과도 비어있게 하고, 값이 있을 때만 계산을 수행합니다. 이렇게 하면 결과 열이 불필요한 0이나 오류 값으로 채워지는 것을 방지할 수 있습니다.

ARRAYFORMULA로 고유값만 추출할 수 있나요?

네, ARRAYFORMULA와 UNIQUE 함수를 조합하여 고유값을 추출할 수 있습니다. 예를 들어, '=ARRAYFORMULA(UNIQUE(A2:A))' 공식은 A열의 모든 고유값을 반환합니다. 더 복잡한 조건으로 필터링하려면 FILTER 함수와 함께 사용할 수도 있습니다: '=ARRAYFORMULA(UNIQUE(FILTER(A2:A, B2:B>100)))' 이 공식은 B열 값이 100보다 큰 경우의 A열 고유값만 반환합니다.

ARRAYFORMULA로 여러 시트의 데이터를 결합할 수 있나요?

직접적으로는 어렵지만, IMPORTRANGE 함수와 ARRAYFORMULA를 조합하여 여러 시트의 데이터를 처리할 수 있습니다. 예를 들어, '=ARRAYFORMULA(IMPORTRANGE("시트URL", "시트1!A2:A")&" - "&IMPORTRANGE("시트URL", "시트2!A2:A"))' 이런 방식으로 여러 시트의 데이터를 결합하여 처리할 수 있습니다. 다만, 너무 많은 IMPORTRANGE를 사용하면 성능이 저하될 수 있으니 주의하세요.

ARRAYFORMULA와 QUERY 함수를 함께 사용할 수 있나요?

네, ARRAYFORMULA와 QUERY 함수를 함께 사용하면 매우 강력한 데이터 분석이 가능합니다. 예를 들어, '=QUERY(ARRAYFORMULA({A2:A&" "&B2:B, C2:C*D2:D}), "select Col1, sum(Col2) where Col2 > 0 group by Col1")' 이런 식으로 ARRAYFORMULA로 데이터를 가공한 후 QUERY로 분석할 수 있습니다. 이는 SQL과 유사한 기능을 제공하여 복잡한 데이터 분석을 단일 공식으로 수행할 수 있게 해줍니다.

마무리: ARRAYFORMULA로 업무 효율성 높이기

지금까지 구글 시트의 ARRAYFORMULA에 대해 자세히 살펴보았습니다.
기본 개념부터 고급 테크닉, 자주 발생하는 오류와 해결책, 그리고 엑셀과의 비교까지 다양한 내용을 다루었습니다.
ARRAYFORMULA는 단순한 함수가 아닌, 구글 시트 사용자의 업무 방식을 완전히 바꿀 수 있는 게임 체인저입니다.

💎 핵심 정리:
ARRAYFORMULA는 반복적인 공식 입력을 제거하고, 데이터가 추가되어도 자동으로 확장되며, 공식 유지 관리를 단순화합니다.
이를 통해 시간을 절약하고, 오류를 줄이며, 더 유연한 스프레드시트를 만들 수 있습니다.
단순히 공식을 쓰는 방식이 아니라, 스프레드시트를 바라보는 관점 자체를 바꾸는 기능입니다.

여러분도 ARRAYFORMULA를 활용하여 반복적인 데이터 작업에서 벗어나 보세요.
처음에는 익숙해지는데 시간이 걸릴 수 있지만, 한번 익숙해지면 스프레드시트 작업 방식이 완전히 달라질 것입니다.
마치 엑셀에서 VLOOKUP을 처음 배웠을 때의 그 놀라움을 다시 경험하게 될 거예요!

여러분의 구글 시트 작업에 ARRAYFORMULA가 큰 도움이 되길 바랍니다.
이 글에서 다룬 내용을 바탕으로 실제 업무에 적용해보시고, 어떤 변화가 있었는지 댓글로 공유해주세요!
혹시 ARRAYFORMULA 관련해서 더 알고 싶은 내용이나 특정 사용 사례에 대한 궁금증이 있으시면 언제든 댓글로 질문해주세요.
제가 아는 한도 내에서 최대한 도움 드리겠습니다.

마지막으로, 구글 시트의 다른 유용한 함수들(QUERY, FILTER, UNIQUE 등)과
ARRAYFORMULA를 조합하면 엑셀을 뛰어넘는 강력한 자동화를 구현할 수 있습니다.

이런 함수들에 대한 내용도 추후에 포스팅할 예정이니 관심 있으시면 블로그를 팔로우해주세요!
여러분의 데이터 작업이 더 쉽고 효율적으로 이루어지길 바랍니다.
지금까지 긴 글 읽어주셔서 감사합니다. 모두 행복한 하루 되세요! 😊

구글시트 ARRAYFORMULA