애들센스


루프없애는 게 대세라던데... 오피스/VBA/Office.JS

파이썬이나 함수형 프로그래밍 덕분에 당연히 사용하던 루프를 쓰면 웬지 촌스러운 느낌이 든다. 루프없이 쓴다면 웬지 멋있어 보인다. 얼마 전 셀 영역을 대상으로 조건부 최소값, 조건부 최대값, 조건부 표준편차 등이 필요했었다. 당연히 이를 계산하려면 필터함수 또는 루프를 사용해야 한다. 루프를 사용하지 않으려면 엑셀의 워크시트함수를 이용해야 한다.

엑셀의 워크시트함수중 조건관련함수로는 AVERAGEIF, AVERAGEIFS, SUMIF, SUMIFS 등이 있다. 그러나 MINIF, MAXIF와 같은 함수는 아직 없다. 그런데 VBA의 WorksheetFunction개체에는 MinIf, MaxIf함수가 보인다. 그런데 사용해보니 에러가 난다.
흠, 이넘들이 시험적으로 해보았다가, 현재는 함수를 삭제했구만~



그래서 이를 수식으로 만들어 계산하도록 하였다. 다음은 A열의 값을 가져와서 B열에 1000을 곱한 결과를 출력하는 예이다.
Set rng = Sheet1.Range(Sheet1.Range("A6"), Sheet1.Range("A6").End(xlDown))
'// A열 값에 1000을 곱하여 B열에 출력
Sheet1.Range("B6").Resize(rng.Rows.Count, 1) = Sheet1.Evaluate(rng.Address & "*1000")
이때 계산은 Evaluate 함수를 사용한다 ( 참고 : http://timebird.egloos.com/7380115) 여기서 Evaluate 함수는 엑셀의 배열수식연산을 해준다.
간단한 예인데, 보다 실용적인 수식을 소개하자면 다음과 같다.
' 지원하지 않는 함수를 생성가능 - 조건부Min,Max,Stdev
'min = .Evaluate("MIN(IF(" & rngB.Address & "=" & Chr(34) & strSym & Chr(34) & "," & rngD.Address & "))")
'max = .Evaluate("MAX(IF(" & rngB.Address & "=" & Chr(34) & strSym & Chr(34) & "," & rngD.Address & "))")
'std = .Evaluate("STDEV(IF(" & rngB.Address & "=" & Chr(34) & strSym & Chr(34) & "," & rngD.Address & "))")


덧글

  • 해색주 2020/09/16 16:24 # 답글

    파이썬에서 루프가 않쓰나 봐요. ^^ 저는 루프 많이 써서 짰는데.
  • 타임버드 2020/09/16 17:00 #

    파이썬의 넘파이나 판다스는 브로드캐스팅을 통해 연산을 루프없이 간결하게 처리합니다. 엑셀에서 배열수식이 브로드캐스팅역할을 합니다. 그래서 느린 vba는 엑셀의 특성을 잘 이용하면 빠르고 간결한 코드를 만들 수 있죠
댓글 입력 영역