합계를 만들기위한 조합 결정 (Microsoft Excel)

Posted on: 2020年11月12日 2020年11月12日

데이터 열이 3 개인 워크 시트가 있다고 가정합니다. 첫 번째 열에는 A부터 Z까지 알파벳의 각 문자가 순서대로 있습니다.

두 번째 열에는 알파벳 문자와 관련된 여러 항목이 포함됩니다. 세 번째 열에는 알파벳 문자와 관련된 시간이 포함됩니다.

세 번째 열 (시간)을 기준으로 알파벳 문자 조합을 4 개의 그룹으로 가능한 한 균등하게 배포하려면 어떻게해야합니까?

예를 들어 알파벳의 각 문자에 대한 모든 시간의 합계가 4,000 시간이면 알파벳을 분리하여 네 그룹 각각이 그룹당 약 1,000 시간을 가질 수 있도록 조합을 만들 수 있습니다.

이것은 실제로 이산 수학 분야에서 잘 알려진 문제입니다. 솔루션을 제공하기 위해 다양한 알고리즘이 개발되었으며 특정 프로그래밍 언어 (예 : LISP)가 있습니다.

최적의 솔루션을 “검색”할 수있는 트리 구조를 쉽게 만들 수 있습니다.

그러나이 경우에는 매크로를 사용하는 간단한 방법이 가장 좋습니다. A ~ C 열에 데이터가 있다고 가정 해 보겠습니다. 다음 매크로는 지정한 범위를 분석하고 요구 사항을 충족하는 값 조합을 반환합니다.

Function DoDist(sRaw As Range, _   iTCol As Integer, _   iBuckets As Integer, _   iWanted As Integer, _   iRetCol As Integer) As String

Dim lGTotal As Long     Dim lPerBucket As Long     Dim lCells() As Long     Dim sRet() As String     Dim lBk() As Long     Dim sBk() As String     Dim lTemp As Long     Dim sTemp As String     Dim J As Integer     Dim K As Integer     Dim L As Integer

Application.Volatile     ReDim lCells(sRaw.Rows.Count)

ReDim sRet(sRaw.Rows.Count)

ReDim lBk(iBuckets)

ReDim sBk(iBuckets)



lGTotal = 0     For J = 1 To sRaw.Rows.Count         lCells(J) = sRaw(J, iTCol)

lGTotal = lGTotal + lCells(J)

sRet(J) = sRaw(J, iRetCol)

Next J

For J = 1 To sRaw.Rows.Count - 1         For K = J + 1 To sRaw.Rows.Count             If lCells(J) < lCells(K) Then                 lTemp = lCells(J)

lCells(J) = lCells(K)

lCells(K) = lTemp                 sTemp = sRet(J)

sRet(J) = sRet(K)

sRet(K) = sTemp             End If         Next K     Next J

lPerBucket = lGTotal / iBuckets     For J = 1 To sRaw.Rows.Count         L = iBuckets         For K = iBuckets To 1 Step -1             If lBk(K) <= lBk(L) Then L = K         Next K         lBk(L) = lBk(L) + lCells(J)

sBk(L) = sBk(L) & sRet(J) & ", "

Next J

For J = 1 To iBuckets         If Right(sBk(J), 2) = ", " Then             sBk(J) = Left(sBk(J), Len(sBk(J)) - 2)

End If         sBk(J) = sBk(J) & " (" & lBk(J) & ")"

Next J

DoDist = sBk(iWanted)

End Function

이 함수에는 5 개의 매개 변수가 전달됩니다. 첫 번째는 평가할 범위이고, 두 번째는 합산되어야하는 해당 범위 내의 열 오프셋, 세 번째는 평가에 사용할 “버킷”수, 네 번째는 평가할 반환 할 버킷이고 다섯 번째는 반환 할 값이 포함 된 열 (지정된 범위 내)의 오프셋입니다.

매크로가하는 일은 합계를 구하려는 열의 모든 값을 가져 와서 내림차순으로 정렬하는 것입니다. 이 값은 가장 큰 것에서 가장 작은 것까지 아무리 많은 “버킷”에 분배됩니다.

있어야한다고 지정했습니다. 숫자는 항상 가장 작은 합계를 포함하는 버킷에 추가됩니다. 함수가 반환하는 문자열은 반환 값 (5 번째 매개 변수로 지정된 열의 각 셀에있는 값)과 버킷의 합계를 나타냅니다.

예를 들어 A1 : C : 26 범위를 평가하려는 경우 범위의 세 번째 열 (C 열)에있는 값을 기반으로 분포를 원했고 분석에 4 개의 버킷이 있어야했습니다. 세 번째 버킷이 반환되기를 원했고 함수가 범위의 A 열에있는 모든 것을 반환하도록하려는 경우 다음을 사용하여 함수를 호출합니다.

=DoDist(A1:C26,3,4,3,1)

_ 참고 : _

이 페이지 (또는 ExcelTips 사이트의 다른 페이지)에 설명 된 매크로를 사용하는 방법을 알고 싶다면 유용한 정보가 포함 된 특별 페이지를 준비했습니다.

link : / excelribbon-ExcelTipsMacros [새 브라우저 탭에서 특별 페이지를 열려면 여기를 클릭하세요].

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (2408)은 Microsoft Excel 97, 2000, 2002 및 2003에 적용됩니다. 여기에서 Excel (Excel 2007 이상)의 리본 인터페이스에 대한이 팁의 버전을 찾을 수 있습니다.

link : / excelribbon-Determining_Combinations_to_Make_a_Total [합계를 만들기위한 조합 결정].