Excel 셀과 VBA의 범위 함수 이해

Excel 셀과 VBA의 범위 함수 이해

엑셀은 강력하다. 많이 사용한다면 수식이나 자동 서식을 사용하는 많은 트릭을 이미 알고 있을 것입니다. 세포 그리고 범위 VBA의 기능을 사용하면 Excel 분석을 완전히 새로운 수준으로 높일 수 있습니다.





VBA에서 셀 및 범위 기능을 사용할 때의 문제는 고급 수준에서 대부분의 사람들이 이러한 기능이 실제로 어떻게 작동하는지 이해하기 어렵다는 것입니다. 그것들을 사용하면 매우 혼란스러울 수 있습니다. 당신이 상상하지 못했던 방식으로 그것들을 사용할 수 있는 방법은 다음과 같습니다.



세포 기능

셀 및 범위 기능을 사용하면 VBA 스크립트 데이터를 얻거나 배치하려는 워크시트의 정확한 위치. 두 셀의 주요 차이점은 참조 대상입니다.





세포 일반적으로 한 번에 하나의 셀을 참조하는 반면 범위 한 번에 셀 그룹을 참조합니다. 이 함수의 형식은 셀(행, 열) .

이것은 전체 시트의 모든 단일 셀을 참조합니다. Cell 함수가 단일 셀을 참조하지 않는 한 가지 예입니다.



Worksheets('Sheet1').Cells

이것은 맨 위 행의 왼쪽에서 세 번째 셀을 참조합니다. 셀 C1:

Worksheets('Sheet1').Cells(3)

다음 코드는 D15 셀을 참조합니다.



Worksheets('Sheet1').Cells(15,4)

원하는 경우 'Cells(15,'D')'를 사용하여 셀 D15를 참조할 수도 있습니다. 열 문자를 사용할 수 있습니다.

열과 셀에 숫자를 사용하여 셀을 참조할 수 있다는 점에서 많은 유연성이 있습니다. 특히 다음을 수행할 수 있는 스크립트를 사용하는 경우 루프 스루 많은 수의 셀을 매우 빠르게 처리하고 계산을 수행합니다. 아래에서 더 자세히 알아보겠습니다.



범위 기능

여러 면에서 Range 함수는 단일 셀 또는 특정 셀 범위를 한 번에 모두 참조할 수 있기 때문에 셀을 사용하는 것보다 훨씬 강력합니다. 셀에 대한 참조가 숫자가 아니기 때문에 Range 함수를 반복하고 싶지 않을 것입니다(내부에 Cell 함수를 포함하지 않는 한).

이 함수의 형식은 범위(셀 #1,셀 #2) . 각 셀은 문자-숫자로 지정할 수 있습니다.

몇 가지 예를 살펴보겠습니다.

명령 프롬프트 윈도우 10 명령 목록

여기에서 범위 함수는 A5 셀을 참조합니다.

Worksheets('Sheet1').Range('A5')

여기에서 범위 함수는 A1에서 E20 사이의 모든 셀을 참조합니다.

Worksheets('Sheet1').Range('A1:E20')

위에서 언급했듯이 숫자-문자 셀 할당을 사용할 필요가 없습니다. 실제로 Range 함수 내에서 두 개의 Cell 함수를 사용하여 다음과 같이 시트의 범위를 식별할 수 있습니다.


With Worksheets('Sheet1')
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With

위의 코드는 Range('A1:E20') 함수와 동일한 범위를 참조합니다. 그것을 사용할 때의 가치는 루프를 사용하여 범위와 동적으로 작동하는 코드를 작성할 수 있다는 것입니다.

이제 셀 및 범위 함수의 형식을 지정하는 방법을 이해했으므로 VBA 코드에서 이러한 함수를 창의적으로 사용하는 방법에 대해 알아보겠습니다.

셀 함수로 데이터 처리하기

셀 함수는 여러 셀 범위에서 수행하려는 복잡한 수식이 있을 때 가장 유용합니다. 이러한 범위는 여러 시트에 걸쳐 존재할 수도 있습니다.

간단한 예를 들어보겠습니다. 11명으로 구성된 영업 팀을 관리하고 있고 매달 그들의 성과를 확인하려고 한다고 가정해 보겠습니다.

당신은 가질 수 있습니다 시트1 판매 수와 판매량을 추적합니다.

에 시트2 회사 고객으로부터 지난 30일 동안 피드백 평가를 추적하는 곳입니다.

두 시트의 값을 사용하여 첫 번째 시트의 보너스를 계산하려면 여러 가지 방법이 있습니다. 두 시트의 데이터를 사용하여 계산을 수행하는 첫 번째 셀에 수식을 작성하고 아래로 끌 수 있습니다. 효과가 있습니다.

이에 대한 대안은 시트를 열 때마다 실행하도록 트리거하거나 시트의 명령 단추에 의해 트리거되어 계산 시기를 제어할 수 있는 VBA 스크립트를 만드는 것입니다. 어쨌든 VBA 스크립트를 사용하여 외부 파일에서 모든 판매 데이터를 가져올 수 있습니다.

그렇다면 그 당시 동일한 스크립트의 보너스 열에 대한 계산을 트리거하지 않는 이유는 무엇입니까?

작용하는 세포 기능

이전에 Excel에서 VBA를 작성한 적이 없다면 개발자 메뉴 항목을 활성화해야 합니다. 이렇게 하려면 다음으로 이동하십시오. 파일 > 옵션 . 클릭 리본 사용자 정의 . 마지막으로 왼쪽 창에서 개발자를 선택하고, 추가하다 오른쪽 창으로 이동하고 확인란이 선택되어 있는지 확인합니다.

이제 클릭하면 좋아요 메인 시트로 돌아가면 개발자 메뉴 옵션이 표시됩니다.

당신은 사용할 수 있습니다 끼워 넣다 메뉴를 사용하여 명령 단추를 삽입하거나 코드 보기 코딩을 시작합니다.

이 예에서는 통합 문서를 열 때마다 스크립트를 실행합니다. 이렇게 하려면 코드 보기 개발자 메뉴에서 다음 새 함수를 코드 창에 붙여넣습니다.

Private Sub Workbook_Open()
End Sub

코드 창은 다음과 같습니다.

이제 계산을 처리하는 코드를 작성할 준비가 되었습니다. 단일 루프를 사용하여 11명의 모든 직원을 단계별로 실행할 수 있으며 Cells 기능을 사용하여 계산에 필요한 세 가지 변수를 가져올 수 있습니다.

Cells 함수에는 각 개별 셀을 식별하기 위한 매개변수로 행과 열이 있습니다. 'x' 행을 만들고 숫자를 사용하여 각 열의 데이터를 요청합니다. 행 수는 직원 수이므로 1에서 11까지입니다. 열 식별자는 판매 수에 대해 2, 판매량에 대해 3, 피드백 점수에 대해 시트 2에서 2입니다.

최종 계산에서는 다음 백분율을 사용하여 총 보너스 점수의 최대 100%를 합산합니다. 이상적인 판매 수는 50, 판매 금액은 ,000, 피드백 점수는 10을 기준으로 합니다.

  • (판매수/50) x 0.4
  • (판매량/50,000) x 0.5
  • (피드백 점수/10) x 0.1

이 간단한 접근 방식은 영업 직원에게 가중 보너스를 제공합니다. 개수가 50이고 볼륨이 ,000이고 점수가 10인 경우 해당 월의 최대 보너스를 모두 받습니다. 그러나 모든 요소에서 완벽하지 않은 것은 보너스를 줄입니다. 이상보다 더 나은 것은 보너스를 증가시킵니다.

이제 이 모든 논리를 매우 간단하고 짧은 VBA 스크립트에서 추출하는 방법을 살펴보겠습니다.

Private Sub Workbook_Open()
For x = 2 To 12
Worksheets('Sheet1').Cells(x, 4) = (Worksheets('Sheet1').Cells(x, 2).Value / 50) * 0.4 _
+ (Worksheets('Sheet1').Cells(x, 3).Value / 50000) * 0.5 _
+ (Worksheets('Sheet2').Cells(x, 2).Value / 10) * 0.1 _
Next x
End Sub

이 스크립트의 출력은 다음과 같습니다.

계정없이 인스타그램을 보는 방법

보너스 열에 백분율이 아닌 실제 달러 보너스가 표시되도록 하려면 최대 보너스 금액을 곱하면 됩니다. 더 나은 방법은 해당 금액을 다른 시트의 셀에 배치하고 코드에서 참조하는 것입니다. 이렇게 하면 나중에 코드를 편집하지 않고도 값을 쉽게 변경할 수 있습니다.

Cells 기능의 장점은 데이터를 가져오기 위해 꽤 창의적인 논리를 구축할 수 있다는 것입니다. 많은 세포 많은 다른 시트에 걸쳐 수행하고 일부 꽤 복잡한 계산 그들과 함께.

셀 기능을 사용하여 셀에 대해 모든 종류의 작업을 수행할 수 있습니다. 예를 들어 셀 지우기, 글꼴 서식 변경 등과 같은 작업을 수행할 수 있습니다.

추가로 수행할 수 있는 모든 작업을 알아보려면 다음을 확인하십시오. 마이크로소프트 MSDN 페이지 Cells 개체의 경우.

범위 함수로 셀 서식 지정

여러 셀을 한 번에 하나씩 반복하려면 Cells 기능이 완벽합니다. 그러나 전체 셀 범위에 한 번에 무언가를 적용하려는 경우 Range 함수가 훨씬 더 효율적입니다.

이에 대한 한 가지 사용 사례는 특정 조건이 충족되는 경우 스크립트를 사용하여 셀 범위의 서식을 지정하는 것일 수 있습니다.

예를 들어, 모든 영업 직원의 모든 판매량 합계가 총 0,000를 초과하는 경우 팀이 추가 팀 보너스를 획득했음을 나타내기 위해 보너스 열의 모든 셀을 녹색으로 강조 표시하려고 합니다.

어떻게 할 수 있는지 살펴 보겠습니다. IF 문 .

Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End Sub

이것이 실행될 때 셀이 팀 목표를 초과하면 범위의 모든 셀이 녹색으로 채워집니다.

이것은 Range 함수를 사용하여 셀 그룹에서 수행할 수 있는 많은 작업의 한 가지 간단한 예일 뿐입니다. 할 수 있는 다른 작업은 다음과 같습니다.

  • 그룹 주위에 윤곽선 적용
  • 셀 범위 내의 텍스트 맞춤법 검사
  • 셀 지우기, 복사 또는 잘라내기
  • '찾기' 방법으로 범위 검색
  • 훨씬 더

꼭 읽어보세요 마이크로소프트 MSDN 페이지 Range 개체가 모든 가능성을 볼 수 있도록 합니다.

Excel을 한 단계 업그레이드

이제 Cell과 Range 함수의 차이점을 이해했으므로 VBA 스크립팅을 다음 수준으로 끌어올릴 차례입니다. Excel에서 계산 및 추가 기능 사용에 대한 Dann의 기사를 통해 모든 데이터 세트에 걸쳐 값을 매우 빠르게 누적할 수 있는 훨씬 더 고급 스크립트를 작성할 수 있습니다.

Excel에서 VBA를 막 시작하는 경우 환상적인 기능이 있음을 잊지 마십시오. Excel VBA 입문서 당신에게도.

공유하다 공유하다 트위터 이메일 Canon vs. Nikon: 어느 카메라 브랜드가 더 낫습니까?

Canon과 Nikon은 카메라 업계에서 가장 큰 두 회사입니다. 그러나 어느 브랜드가 더 나은 카메라와 렌즈 라인업을 제공합니까?

다음 읽기 관련 항목
  • 생산력
  • 프로그램 작성
  • 비주얼 베이직 프로그래밍
  • 마이크로 소프트 엑셀
저자 소개 라이언 두브(942건의 기사 게재)

Ryan은 전기 공학 학사 학위를 받았습니다. 그는 자동화 엔지니어링에서 13년, IT에서 5년을 근무했으며 현재는 앱 엔지니어입니다. MakeUseOf의 전 편집장인 그는 데이터 시각화에 관한 전국 회의에서 연설했으며 전국 TV와 라디오에 출연했습니다.

Ryan Dube가 참여한 작품 더보기

뉴스레터 구독

뉴스레터에 가입하여 기술 팁, 리뷰, 무료 전자책 및 독점 거래를 확인하십시오!

구독하려면 여기를 클릭하세요.