수식을 사용하여 Excel에서 FOR 루프를 만드는 방법(예제 3개)

Excel VBA를 사용하고 싶지 않고 FOR 루프를 만들고 싶지 않습니까? 수식을 사용하여 Excel에서? 이 기사에서는 FOR 루프를 만드는 방법을 보여주었습니다. 공식을 사용합니다.

Excel VBA로 코딩하는 방법을 안다면 , 당신은 축복받은 🙂. 그러나 VBA로 코드를 작성한 적이 없다면 또는 Excel 통합 문서를 Excel VBA 없이 유지하려는 경우 코드를 만든 다음 대부분의 경우 간단한루프를 만들기 위해 즉시 생각해야 합니다. .

작업 파일 다운로드

아래 링크에서 작업 파일을 다운로드하십시오.

수식을 사용하여 Excel에서 FOR 루프를 만드는 3가지 예

여기서는 3을 시연하겠습니다. FOR 루프를 만드는 예 수식을 사용하여 Excel에서 자세한 예를 살펴보겠습니다.

1. Excel에서 FOR 루프를 만들기 위해 결합된 함수 적용

이제 이 예제를 작성하게 된 배경을 알려주세요.

저는 Udemy에 대한 일부 과정의 저자입니다. 강의 중 하나는 Excel 조건부 서식에 관한 것입니다. 과정 제목은 7가지 실용적인 문제로 Excel 조건부 서식 배우기입니다. [이 과정에 무료로 액세스하려면 여기를 클릭하세요. ].

강의 토론 게시판에서 한 학생이 저에게 아래와 같은 질문을 했습니다[스크린샷 이미지].

Udemy의 학생이 던진 질문입니다.

위의 질문을 잘 읽고 풀어보세요...

위 문제를 해결하는 단계:

여기서는 OR을 사용합니다. , 오프셋 , 최대 , MIN , 및 ROW FOR 루프를 생성하는 Excel 수식으로 기능 .

• 먼저 귀하의 임무는 새 통합 문서를 열고 위의 값을 워크시트에 하나씩 입력하는 것입니다. [C5 셀에서 시작 ].
• 둘째, 전체 범위를 선택합니다. [from C5:C34 ].
• 셋째, 홈에서 리본>> 조건부 서식 클릭 명령.
• 마지막으로 새 규칙을 선택합니다. 드롭다운에서 옵션을 선택하세요.

현재 새 서식 규칙 대화 상자가 나타납니다.

• 이제 규칙 유형 선택에서 창>> 수식을 사용하여 서식을 지정할 셀 결정 선택 옵션.
• 그런 다음 이 공식이 참인 값의 서식에서 필드에 다음 공식을 입력하십시오.

• 이제 형식...을 클릭하여 적절한 형식 유형을 선택합니다. 버튼을 클릭하세요.

현재 셀 서식이라는 대화 상자가 나타납니다.

• 이제 채우기 에서 옵션>> 색상 중 하나를 선택해야 합니다. 여기서는 하늘색을 선택했습니다. 배경. 또한 샘플을 볼 수 있습니다. 곧. 이 경우 조명을 선택하세요. 색깔. 어두운 색은 입력된 데이터를 숨길 수 있기 때문입니다. 그런 다음 글꼴 색상을 변경해야 할 수도 있습니다. .
• 그런 다음 확인 을 눌러야 합니다. 형성을 적용합니다.

• 그런 다음 확인을 눌러야 합니다. 새 서식 규칙 대화 상자. 여기 미리보기에서 즉시 샘플을 볼 수 있습니다. 상자.

마지막으로 형식이 지정된 숫자가 표시됩니다.

위의 문제를 해결하는 알고리즘을 보여드리겠습니다.

• 여기에서는 알고리즘을 쉽게 이해할 수 있도록 두 개의 참조 셀(cell 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 , 패턴이 있습니다. 하지만 2부에서는 그런 패턴이 없습니다.

• 그럼 위의 패턴을 염두에 두고 알고리즘을 만들어 봅시다. 일반 수식을 작성하기 전에 C11 셀에 대한 수식이 무엇인지 보여 드리겠습니다. 및 C17 그런 다음 공식을 수정하여 모두에게 공통적으로 적용할 것입니다. 참조점의 경우(예:C11 또는 C17 ), 총 7 주위에 셀(참조점 포함)을 배치하고 배열을 생성하는 수식에 나란히 배치합니다. 그런 다음 차이점 중 하나라도 3과 같으면 배열의 차이를 알아낼 것입니다. 참조 셀은 TRUE가 됩니다. 가치가 있습니다.
• 여기서 OFFSET을 사용하여 쉽게 할 수 있습니다. OFFSET 기능 함수는 배열을 반환합니다. 셀 참조 C11에 대해 말하세요. , 다음과 같은 공식을 작성할 수 있습니다. =OR(OFFSET(C11, 0, 0, 4, 1)-OFFSET(C11, -3, 0, 4, 1)=3) . 이 공식은 무엇을 반환합니까? 수식의 첫 번째 오프셋 함수는 배열을 반환합니다. {10; 11; 12; 15} , 두 번째 오프셋 함수는 배열 {5; 8; 9; 10} . 그리고 {10; 11; 12; 15} – {5; 8; 9; 10} ={10-5; 11-8; 12-9; 15-10} ={5; 삼; 삼; 5} . 이 배열이 =3으로 논리적으로 테스트되는 경우 Excel은 다음과 같이 내부적으로 계산합니다. {5=3; 3=3; 3=3; 5=3} ={거짓; 진실; 진실; 거짓} . 또는일 때 함수가 다음 배열에 적용됩니다. OR({False; True; False; True} , 당신은 TRUE를 얻습니다 . 따라서 셀 C11 반환된 대로 true 값을 가져옵니다.
• 이 알고리즘이 작동하는 방식에 대한 전체 개념을 알고 있다고 생각합니다. 이제 문제가 있습니다. 이 수식은 C8 셀에서 작동할 수 있습니다. , C8 셀 위 , 3이 있습니다. 세포. 그러나 C5, C6 셀의 경우 및 C7 이 공식은 작동하지 않습니다. 따라서 이 셀에 대한 수식을 수정해야 합니다.
• 이제 C5 셀의 경우 C7으로 , 우리는 공식이 상위 3을 고려하지 않기를 원합니다. 세포. 예를 들어 C6 셀의 경우 , 우리의 수식은 C11 셀의 수식과 다릅니다. :=OR(오프셋(C11, 0, 0, 4, 1)-오프셋(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); [일반 공식입니다].
• 마지막으로 위의 공식에서 몇 가지 패턴을 찾으셨습니까? 첫 번째 오프셋 함수의 행 인수가 3에서 감소했습니다. ~ 0; 높이 인수가 1에서 증가했습니다. 4까지 . 두 번째 OFFSET 함수의 행 인수가 0에서 감소했습니다. -3까지 높이 인수가 1에서 증가했습니다. 4까지 .
• 먼저, 첫 번째 OFFSET 함수의 행 인수는 다음과 같이 수정됩니다. MAX(ROW(C$5)-ROW(C5)+3,0)
• 둘째, 두 번째 OFFSET 함수의 행 인수는 다음과 같이 수정됩니다. MAX(ROW(C$5)-ROW(C5),-3) 
• 셋째, 첫 번째 OFFSET 함수의 높이 인수는 다음과 같이 수정됩니다. MIN(ROW(C5)-ROW(C$5)+1,4)
• 넷째, 두 번째 OFFSET 함수의 높이 인수는 다음과 같이 수정됩니다. MIN(ROW(C5)-ROW(C$5)+1,4)
• 이제 위의 수정 사항을 이해하려고 합니다. 이것들은 이해하기 어렵지 않습니다. 이 모든 4개 수정 사항은 FOR LOOP로 작동합니다. Excel VBA를 사용했지만 Excel 수식으로 작성했습니다.
• 그래서 C5:C34에서 셀에 대한 일반 공식이 작동하는 방식을 알았습니다. .

그래서 저는 Excel 스프레드시트의 루핑에 대해 이야기하고 있었습니다. 따라서 이것은 Excel에서 반복되는 완벽한 예입니다. 여기서 수식이 7이 될 때마다 특정 값을 찾기 위해 셀에 대한 작업을 수행합니다.

2. IF 및 OR 함수를 사용하여 Excel에서 FOR 루프 생성

이 예에서 셀에 값이 포함되어 있는지 여부를 확인하려고 한다고 가정합니다. 또한 Excel VBA FOR 루프를 사용하여 쉽게 할 수 있지만 여기서는 Excel 공식을 사용하여 수행하겠습니다.

이제 을(를) 사용할 수 있습니다. 만약 , 및 또는 FOR 루프를 생성하는 Excel 수식으로 기능 . 또한 기본 설정에 따라 이 공식을 수정할 수 있습니다. 단계는 다음과 같습니다.

단계:

• 먼저 다른 셀을 선택해야 합니다. E5 상태를 보고 싶은 위치 .
• 둘째, E5에서 해당 공식을 사용해야 합니다. 세포.

• 그런 다음 Enter 키를 누릅니다. 결과를 얻을 수 있습니다.

수식 분석

여기서 또는 함수는 TRUE를 반환합니다. 주어진 논리 중 하나라도 TRUE가 되는 경우 .

• 첫째, B5=”” 첫 번째입니다. B5 셀이 값을 포함하거나 포함하지 않습니다.
• 둘째, C5=”” 두 번째입니다. C5 셀이 값을 포함하거나 포함하지 않습니다.
• 셋째, D5=”” 세 번째입니다. 논리. 마찬가지로 D5 셀이 값을 포함하거나 포함하지 않습니다.

이제 IF 함수는 주어진 조건을 충족할 결과를 반환합니다.

• 또는 함수는 TRUE를 제공합니다. 그러면 "정보 누락이 표시됩니다. "로 상태 . 그렇지 않으면 "완료 "를 상태로 .

• 그 후에는 채우기 핸들을 드래그해야 합니다. 자동완성 아이콘 나머지 셀의 해당 데이터 E6:E13 . 또는 채우기 핸들을 두 번 클릭할 수 있습니다. 아이콘.

마지막으로 모든 결과를 얻을 수 있습니다.

3. SUMIFS 함수를 사용하여 Excel에서 FOR 루프 생성

특정 사람에 대한 전체 청구서를 작성하려고 한다고 가정해 보겠습니다. 이 경우 FOR 루프를 사용할 수 있습니다. 엑셀 공식을 이용하여 여기서는 SUMIFS 함수를 사용하겠습니다. FOR 루프 생성 엑셀에서. 단계는 다음과 같습니다.

단계:

• 먼저 다른 셀을 선택해야 합니다. F7 상태를 보고 싶은 위치 .
• 둘째, F7 세포.

• 그런 다음 Enter 키를 누릅니다. 결과를 얻을 수 있습니다.

수식 분석

• 여기, $C$5:$C$13 SUMIFS 함수가 합산을 수행합니다.
• 그러면 $B$5:$B$13 SUMIFS의 데이터 범위입니다. 함수는 주어진 기준을 확인합니다
• 마지막으로 E7 기준입니다.
• 그래서,SUMIFS 함수는 E7에 대한 지불을 추가합니다. 셀 값.

• 그 후에는 채우기 핸들을 드래그해야 합니다. 나머지 셀의 해당 데이터를 자동 완성하는 아이콘 F8:F10 .

마침내 결과를 얻게 될 것입니다.

결론

이 기사가 도움이 되었기를 바랍니다. 여기에서는 3에 대해 설명했습니다. FOR 루프를 만드는 데 적합한 예 수식을 사용하여 Excel에서. Exceldemy 웹사이트를 방문할 수 있습니다. Excel 관련 콘텐츠에 대해 자세히 알아보십시오. 아래 의견 섹션에 의견, 제안 또는 쿼리가 있는 경우 삭제하십시오.

자세히 알아보기

• Excel VBA에서 Do While 루프를 사용하는 방법
• VBA Excel의 다음 루프(루프를 단계별로 실행하고 종료하는 방법)