VBA 없이 엑셀 수식으로 FOR 루프 만드는 방법(예제 3가지)

Excel VBA를 사용하지 않고 수식(Formula)만으로 FOR 루프를 만들고 싶으신가요? 이 글에서는 수식만 활용해 FOR 루프를 구현하는 방법을 소개합니다.

Excel VBA로 코딩할 줄 안다면 정말 편리하지요 🙂 하지만 VBA 코드를 작성해 본 경험이 없거나, 통합 문서에 매크로 코드를 포함하고 싶지 않은 경우라면 간단한 루프 하나를 만들 때도 색다른 발상이 필요합니다.

실습 파일 다운로드

아래 링크에서 실습 파일을 다운로드하세요:

수식으로 Excel FOR 루프 만들기: 예제 3가지

여기서는 수식을 이용해 Excel에서 FOR 루프를 만드는 3가지 예제를 살펴보겠습니다. 각 예제의 자세한 내용을 하나씩 확인해 보세요.

1. 여러 함수를 조합하여 FOR 루프 만들기

먼저 이 예제를 작성하게 된 배경부터 말씀드리겠습니다.

저는 Udemy에서 몇 개의 강좌를 운영하고 있는데, 그중 하나가 Excel 조건부 서식 강좌입니다. 강좌 제목은 '7가지 실전 문제로 배우는 Excel 조건부 서식(Learn Excel Conditional Formatting with 7 Practical Problems)'입니다. [무료로 수강하려면 여기를 클릭하세요].

강좌 토론 게시판에서 한 학생이 아래와 같은 질문을 올렸습니다 [스크린샷 참조].

Udemy에서 학생이 올린 질문

위 질문을 잘 읽어보고 직접 풀어보세요…

위 문제 해결 단계:

여기서는 OR, OFFSET, MAX, MIN, ROW 함수를 Excel 수식으로 활용하여 FOR 루프를 만들겠습니다.

• 첫째, 새 통합 문서를 열고 위 값을 하나씩 워크시트에 입력합니다 [셀 C5부터 시작].
• 둘째, 전체 범위 [셀 C5:C34]를 선택합니다.
• 셋째, 홈(Home) 리본 메뉴에서 조건부 서식(Conditional Formatting) 명령을 클릭합니다.
• 마지막으로 드롭다운 목록에서 새 규칙(New Rule) 옵션을 선택합니다.

그러면 새 서식 규칙(New Formatting Rule) 대화 상자가 나타납니다.

• 규칙 유형 선택(Select a Rule Type) 창에서 수식을 사용하여 서식을 지정할 셀 결정(Use a formula to determine which cells to format) 옵션을 선택합니다.
• 그런 다음 이 수식이 참일 경우 값의 서식 지정(Format values where this formula is true) 입력란에 다음 수식을 입력합니다:

• 대화 상자에서 서식(Format)... 버튼을 클릭해 원하는 서식 유형을 지정합니다.

이제 셀 서식(Format Cells) 대화 상자가 열립니다.

• 채우기(Fill) 탭에서 원하는 색상을 선택합니다. 여기서는 연한 파란색(Light Blue) 배경을 골랐으며, 오른쪽 미리 보기(sample)에서 결과를 즉시 확인할 수 있습니다. 이때 가급적 연한 색상을 선택하세요. 짙은 색은 입력된 데이터를 가릴 수 있어 글꼴 색(Font Color)을 변경해야 할 수도 있습니다.
• 이후 확인(OK)을 눌러 서식을 적용합니다.

• 그다음 새 서식 규칙 대화 상자에서 확인(OK)을 누르면 됩니다. 미리 보기(Preview) 창에서 결과를 즉시 확인할 수 있습니다.

마지막으로 서식이 적용된 숫자들을 확인할 수 있습니다.

이제 위 문제를 해결하는 알고리즘을 설명해 드리겠습니다:

• 알고리즘을 쉽게 이해할 수 있도록 두 개의 기준 셀인 C11과 C17을 중심으로 전체 과정을 설명하겠습니다. C11과 C17 셀의 값은 각각 10과 20입니다(위 이미지 참조). Excel 수식에 익숙하다면 OFFSET 함수가 쓰였음을 눈치챘을 것입니다. OFFSET 함수는 기준점을 바탕으로 동작하기 때문입니다.
• 이제 셀 범위 C8:C11과 C11:C14, 그리고 C14:C17과 C17:C20의 값을 나란히 놓아본다고 상상해 보세요 [아래 이미지]. 기준 셀은 C11과 C17이며, 기준 셀 주변의 총 7개 셀을 가져옵니다. 그러면 다음과 같은 가상의 그림이 그려집니다. 첫 번째 부분에서는 패턴이 보입니다. C9–C12=3, C10-C13=3처럼 일정한 차이가 있습니다. 반면 두 번째 부분에는 이런 패턴이 없습니다.

• 자, 위 패턴을 염두에 두고 알고리즘을 구축해 보겠습니다. 공통 수식을 만들기 전에 먼저 셀 C11과 C17에 대한 수식을 보여주고, 이후 모든 셀에 적용 가능하도록 수식을 수정하겠습니다. 기준점(C11 또는 C17)을 중심으로 주변 셀을 포함해 총 7개의 셀을 가져와 배열 형태로 나란히 놓습니다. 그다음 배열 간의 차이를 계산하고, 그 차이 중 하나라도 3과 같으면 해당 기준 셀은 TRUE가 됩니다.
• OFFSET 함수는 배열을 반환하기 때문에 이 작업을 손쉽게 처리할 수 있습니다. 예를 들어 셀 C11을 기준으로 수식을 다음과 같이 작성할 수 있습니다: =OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3). 이 수식은 무엇을 반환할까요? 첫 번째 OFFSET 함수는 배열 {10; 11; 12; 15}를 반환하고, 두 번째 OFFSET 함수는 배열 {5; 8; 9; 10}을 반환합니다. 그리고 {10; 11; 12; 15} – {5; 8; 9; 10} = {10-5; 11-8; 12-9; 15-10} = {5; 3; 3; 5}가 됩니다. 이 배열에 논리 연산 =3을 적용하면 Excel은 내부적으로 다음처럼 계산합니다: {5=3; 3=3; 3=3; 5=3} = {FALSE; TRUE; TRUE; FALSE}. 여기에 OR 함수를 적용하면 OR({FALSE; TRUE; TRUE; FALSE})의 결과는 TRUE입니다. 따라서 셀 C11은 TRUE 값을 반환받습니다.
• 이쯤이면 알고리즘이 어떻게 작동하는지 감을 잡으셨을 겁니다. 그런데 한 가지 문제가 있습니다. 이 수식은 셀 C8부터는 정상 작동하지만, C8 위에는 셀이 3개뿐이라 셀 C5, C6, C7에서는 작동하지 않습니다. 따라서 이 셀들을 위해 수식을 수정해야 합니다.
• 셀 C5부터 C7까지는 위쪽 3개 셀을 고려하지 않도록 수식을 조정해야 합니다. 예를 들어 셀 C6의 경우, 셀 C11용 수식인 =OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3)과 같은 형태가 되면 안 됩니다.
• 셀 C5의 수식은 다음과 같습니다: OR(OFFSET(C5, 3, 0, 1, 1)-OFFSET(C5, 0, 0, 1, 1)=3).
• 셀 C6의 수식은 다음과 같습니다: OR(OFFSET(C6, 2, 0, 2, 1)-OFFSET(C6, -1, 0, 2, 1)=3).
• 셀 C7의 수식은 다음과 같습니다: OR(OFFSET(C7, 1, 0, 3, 1)-OFFSET(C7, -2, 0, 3, 1)=3).
• 셀 C8의 수식은 다음과 같습니다: OR(OFFSET(C8, 0, 0, 4, 1)-OFFSET(C8,-3, 0, 4, 1)=3); [이것이 일반 공식입니다].
• 셀 C9의 수식 역시 같습니다: OR(OFFSET(C9, 0, 0, 4, 1)-OFFSET(C9,-3, 0, 4, 1)=3); [일반 공식].
• 위 수식들에서 어떤 패턴이 보이시나요? 첫 번째 OFFSET 함수의 rows 인수는 3에서 0으로 감소하고, height 인수는 1에서 4로 증가합니다. 두 번째 OFFSET 함수의 rows 인수는 0에서 -3으로 감소하고, height 인수는 1에서 4로 증가합니다.
• 첫째, 첫 번째 OFFSET 함수의 rows 인수는 다음과 같이 수정됩니다: MAX(ROW(C$5)-ROW(C5)+3,0)
• 둘째, 두 번째 OFFSET 함수의 rows 인수는 다음과 같이 수정됩니다: MAX(ROW(C$5)-ROW(C5),-3)
• 셋째, 첫 번째 OFFSET 함수의 height 인수는 다음과 같이 수정됩니다: MIN(ROW(C5)-ROW(C$5)+1,4)
• 넷째, 두 번째 OFFSET 함수의 height 인수 역시 다음과 같이 수정됩니다: MIN(ROW(C5)-ROW(C$5)+1,4)
• 위 수정 사항을 이해해 보세요. 생각보다 어렵지 않습니다. 이 네 가지 수정은 모두 Excel VBA의 FOR LOOP와 동일한 역할을 하지만, Excel 수식으로 구현한 것입니다.
• 이렇게 하면 일반 수식이 C5:C34 범위의 모든 셀에서 작동하는 원리를 이해하셨을 것입니다.

지금까지 스프레드시트에서의 반복 처리(Looping)에 대해 이야기했습니다. 이것이 Excel에서 반복 작업을 수행하는 완벽한 예제입니다. 수식은 매번 7개의 셀을 가져와 특정 값을 찾아냅니다.

2. IF 및 OR 함수로 FOR 루프 만들기

이번 예제에서는 셀에 값이 입력되어 있는지 여부를 확인한다고 가정해 보겠습니다. 물론 Excel VBA FOR 루프를 사용하면 간단하지만, 여기서는 Excel 수식으로 처리해 보겠습니다.

IF 함수와 OR 함수를 Excel 수식으로 활용해 FOR 루프를 만들 수 있습니다. 또한 필요에 따라 수식을 자유롭게 수정할 수도 있습니다. 단계는 아래와 같습니다.

단계:

• 첫째, Status(상태)를 표시할 다른 셀 E5를 선택합니다.
• 둘째, E5 셀에 다음 수식을 입력합니다.

• 이후 ENTER 키를 눌러 결과를 확인합니다.

수식 설명

여기서 OR 함수는 주어진 논리값 중 하나라도 TRUE이면 TRUE를 반환합니다.

• 첫째, B5=""는 첫 번째 논리식으로, 셀 B5에 값이 있는지 검사합니다.
• 둘째, C5=""는 두 번째 논리식으로, 셀 C5에 값이 있는지 검사합니다.
• 셋째, D5=""는 세 번째 논리식으로, 마찬가지로 셀 D5에 값이 있는지 검사합니다.

IF 함수는 주어진 조건에 따라 결과를 반환합니다.

• OR 함수가 TRUE를 반환하면 Status로 "Info Missing"(정보 누락)이 표시되고, 그렇지 않으면 "Done"(완료)이 표시됩니다.

• 그다음 채우기 핸들(Fill Handle) 아이콘을 끌어 나머지 셀 E6:E13까지 데이터를 자동 채우기(AutoFill)합니다. 또는 채우기 핸들을 더블클릭해도 됩니다.

마지막으로 모든 결과를 확인할 수 있습니다.

3. SUMIFS 함수로 FOR 루프 만들기

특정 사람의 총 청구 금액을 구하고 싶다고 가정해 보겠습니다. 이 경우에도 Excel 수식을 활용한 FOR 루프를 사용할 수 있습니다. 여기서는 SUMIFS 함수로 Excel에서 FOR 루프를 만들어 보겠습니다. 단계는 아래와 같습니다.

단계:

• 첫째, 결과를 표시할 다른 셀 F7을 선택합니다.
• 둘째, F7 셀에 다음 수식을 입력합니다.

• 이후 ENTER 키를 눌러 결과를 확인합니다.

수식 설명

• $C$5:$C$13은 SUMIFS 함수가 합계를 구할 데이터 범위입니다.
• $B$5:$B$13은 SUMIFS 함수가 조건을 검사할 데이터 범위입니다.
• E7은 조건(criteria)입니다.
• 따라서 SUMIFS 함수는 E7 셀 값에 해당하는 결제 금액들을 모두 더합니다.

• 그다음 채우기 핸들(Fill Handle) 아이콘을 끌어 나머지 셀 F8:F10까지 데이터를 자동 채웁니다.

마지막으로 최종 결과를 확인할 수 있습니다.

결론

이 글이 도움이 되었기를 바랍니다. 여기서는 수식을 활용해 Excel에서 FOR 루프를 만드는 3가지 실용적인 예제를 설명했습니다. 더 많은 Excel 관련 콘텐츠를 보려면 저희 웹사이트 Exceldemy를 방문해 주세요. 궁금한 점이나 의견, 제안이 있다면 아래 댓글 섹션에 남겨주세요.

더 읽어볼 내용

• Excel VBA에서 Do While 루프 사용하는 방법
• VBA Excel의 For Next 루프 (루프 건너뛰기 및 종료하는 법)