구글 스프레드시트 ARRAYFORMULA 자동 채우기: #REF!·빈 행·헤더 처리
ARRAYFORMULA로 반복 수식 열을 자동 계산하려면 결과 열의 맨 위 셀에 수식을 한 번만 넣고, 아래 결과 범위는 비워 두면 됩니다. 상품·수량·단가 예제에서는 D2 수식 하나가 아래 행의 금액을 모두 계산했습니다.
새 행까지 이어지게 하려면 A2:A처럼 끝 행을 열어 두고, 빈 행의 0을 막으려면 IF(A2:A="","",...)로 입력 기준 열을 먼저 검사하세요.
#REF!가 보이면 수식부터 고치기보다 결과 범위의 수동값을 먼저 찾습니다. 아래 재현에서는 D4의 값 하나가 배열 확장을 막았고, 그 값을 옮기자 원래 결과가 복구됐습니다.
ARRAYFORMULA는 언제 쓰면 되나요?
한 행마다 같은 계산을 아래로 복사하는 열이라면 ARRAYFORMULA가 잘 맞습니다. 수량×단가, 날짜에서 월 추출, 코드 조합처럼 규칙이 같은 계산을 한 셀에서 관리할 수 있습니다.
| 업무 상황 | 권장 방식 | 이유 |
|---|---|---|
| 모든 행에 같은 계산 | ARRAYFORMULA | 한 수식으로 새 행까지 이어서 계산 |
| 일부 행을 사람이 덮어써야 함 | 개별 수식 또는 예외 열 | 수동값이 배열 결과 범위를 막을 수 있음 |
| FILTER·SEQUENCE·IMPORTRANGE처럼 이미 배열을 반환 | 먼저 함수 자체만 사용 | 공식 도움말처럼 명시적 ARRAYFORMULA가 불필요할 수 있음 |
결과 열에 승인값·메모·수정값을 직접 입력해야 한다면 ARRAYFORMULA와 같은 열을 공유하지 마세요. 계산 열과 수동 예외 열을 분리한 뒤 최종값을 다른 열에서 선택하는 구조가 안전합니다.
한 번 입력해 금액 열을 자동 계산하려면?
D2에 아래 수식을 한 번 입력하면 됩니다. A열의 상품명이 비어 있지 않은 행만 B열 수량과 C열 단가를 곱합니다.
=ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))| 상품 | 수량 | 단가 | 계산 결과 |
|---|---|---|---|
| 무선 마우스 | 2 | 18,000 | 36,000 |
| 키보드 | 1 | 42,000 | 42,000 |
| USB 허브 | 3 | 15,000 | 45,000 |
| 노트북 거치대 | 2 | 28,000 | 56,000 |
웹캠 행을 새로 추가해 수량 2, 단가 51,000을 입력하자 D열에는 102,000이 자동으로 표시됐습니다. D2를 아래로 복사하지 않아도 A2:A, B2:B, C2:C의 열린 범위가 새 행을 포함했습니다.
빈 행에 0이 나오지 않게 하려면?
숫자 열만 곱한 =ARRAYFORMULA(B2:B8*C2:C8)을 시험하자 입력이 없는 7행과 8행에 0이 표시됐습니다. 입력 여부를 대표하는 열을 IF로 먼저 검사하면 빈 행은 빈 문자열로 남길 수 있습니다.
=ARRAYFORMULA(IF(A2:A8="","",B2:B8*C2:C8))- A열 상품명이 필수라면 A2:A를 검사합니다.
- 상품명 없이 수량부터 입력할 수 있다면 실제로 입력 여부를 대표하는 다른 열을 고릅니다.
- 원본 셀이 수식으로
""을 반환할 수 있다면 ISBLANK보다A2:A=""또는 LEN 검사를 먼저 검토합니다.
Google 공식 ISBLANK 도움말은 빈 문자열 ""도 내용으로 간주해 FALSE를 반환한다고 설명합니다. 겉으로 빈 셀이 모두 같은 상태는 아니므로, 원본 열에 수식이 있는지 확인하세요.
#REF!는 왜 생기고 어떻게 복구하나요?
배열 결과가 펼쳐질 셀에 값이 있으면 #REF!가 발생할 수 있습니다. D2에 ARRAYFORMULA를 둔 상태에서 D4에 ‘수동값’을 넣어 재현하자, Google Sheets는 “배열 결과는 D4에서 데이터를 덮어쓰기 때문에 스프레드시트에서 펼쳐지지 않습니다”라고 표시했습니다.
- 오류 말풍선에서 덮어쓰려는 셀 주소를 확인합니다.
- 그 셀의 값이 필요한 기록이면 다른 열로 먼저 이동합니다.
- 불필요한 값이라면 삭제하고 원래 수식 셀로 돌아갑니다.
- 정상값이 아래 행까지 다시 펼쳐지는지 확인합니다.
방해 셀을 비우자 D2:D6의 36,000·42,000·45,000·56,000·102,000이 다시 계산됐습니다. 결과 셀 하나를 따로 수정한 시험에서도 그 값이 새 방해 셀이 되어 원본 배열이 #REF!로 돌아갔습니다.
헤더까지 한 수식으로 만들려면?
헤더와 계산식을 함께 관리하려면 D1에서 배열 리터럴과 ARRAYFORMULA를 세로로 결합할 수 있습니다. 이 글의 한국 로캘 시트에서는 세미콜론이 헤더 행과 계산 결과를 위아래로 연결했습니다.
={"금액";ARRAYFORMULA(IF(A2:A="","",B2:B*C2:C))}| 방식 | 수식 위치 | 추천 상황 |
|---|---|---|
| 헤더 분리 | D1은 ‘금액’, D2는 ARRAYFORMULA | 헤더를 자주 바꾸거나 수식을 따로 설명할 때 |
| 헤더 결합 | D1에 결합 수식 | 템플릿에서 헤더와 계산을 한 단위로 옮길 때 |
결합 수식은 D1 아래 전체를 결과 범위로 사용합니다. D2 이후에 수동값이 있으면 같은 #REF! 문제가 생깁니다. 시트 로캘에 따라 수식 구분자가 다르면 Google Sheets의 함수 자동 제안에 표시되는 구분자를 확인하세요.
결과가 예상과 다를 때 어디부터 보나요?
| 증상 | 먼저 볼 곳 | 복구 방법 |
|---|---|---|
| #REF!와 덮어쓰기 주소 표시 | 오류가 지목한 셀 | 필요한 값을 이동한 뒤 방해 셀을 비움 |
| 새 행만 계산되지 않음 | A2:A6 같은 고정 끝 행 | 운영 범위에 맞게 끝 행을 늘리거나 열린 범위 사용 |
| 빈 행에 0이 표시됨 | IF로 입력 열을 검사하는지 | 빈 행이면 ""을 반환하도록 조건 추가 |
| 헤더가 사라지거나 #REF! 발생 | 수식 시작 셀과 기존 헤더 | D2에서 시작하거나 D1 결합 수식으로 통일 |
| 일부 행만 수동 수정해야 함 | 결과 열의 운영 방식 | 수동 예외 열을 분리하거나 개별 수식 사용 |
지금 적용할 순서
- 사본이나 작은 시험 범위에서 결과 열을 비웁니다.
- D2의 기본 수식 또는 D1의 헤더 결합 수식 중 하나를 고릅니다.
- 정상 행과 빈 행을 함께 확인합니다.
- 새 행 하나를 추가해 자동 계산이 이어지는지 봅니다.
- #REF!가 나면 오류가 알려주는 방해 셀부터 확인합니다.
실무 기본값은 헤더를 D1에 두고 D2에서 IF를 포함한 ARRAYFORMULA를 시작하는 방식입니다. 구조가 단순해 수식 위치와 오류 범위를 구분하기 쉽습니다. 헤더와 계산을 한 단위로 옮겨야 할 때만 D1 결합 수식을 사용하세요.