문맥
원하는 시작 날짜와 종료 날짜로 여러 활동을 수행하고 동시에 3개(예:)만 수행할 수 있다는 제약 조건을 고려하여 해당 활동이 합리적인지 확인해야 합니다. 3개 이상 수행할 수 없으므로 일정 내에서 활동이 지연되는 것을 허용해야 합니다. MS 프로젝트에서 이 작업을 수행할 수 있다는 것을 알고 있지만 Excel 내에서 특별히 수행해야 합니다.
문제
나는 이것을 Excel 내에서 공식화했으며 짧은 기간(최대 약 30,000개 셀)에 걸쳐 소수의 작업에 대해 의도한 대로 완전히 작동하고 있습니다. 그러나 훨씬 더 오랜 기간 동안 더 많은 활동에 적용해야 합니다(잠재적으로 총 최대 500,000개의 셀 - 현재 수행하는 데 약 10분이 소요됩니다!).내 수식에 대한 개선 사항을 추천해 주실 수 있나요?나는 이미 많은 최적화 작업을 수행했으며 몇 가지 다른 옵션도 조사했습니다(아래 참조).
내 현재 방법
내 방법은 우선순위 번호가 부여된 활동에 의존하고 이에 따라 순서가 지정된다는 점에 유의하십시오(단순히 가장 빠른 "시작" 기간이 됩니다).
프로필/간트 차트 생성을 위한 공식 설명:
- 간트 차트의 첫 번째 줄($B9 = 1)인 경우 앞에 다른 활동이 없으므로 너무 많이 생각하지 마세요. 원하는 날짜 내에 있으면 각 셀에 1을 입력하세요(IF(AND (AO$3>=$C9,AO$3<=$D9),1,"")).
- 다른 행의 경우... 수식은 (A) 올바른 날짜 범위 내에 있는지 (B) 이미 수행 중인 3개의 활동이 있는지 (C) 이 활동에 이미 충분한 1을 입력했는지 여부를 확인합니다.
(저는 모든 행에서 수식이 일관되게 유지되기를 원하는 Excel 표를 사용하고 있습니다. 이것이 바로 동일한 수식에 첫 번째와 두 번째 점이 있는 이유입니다.)
현재 개선 시도:
- 전체 범위를 합산하는 대신 더 적은 수의 셀을 합산하도록 SUM(OFFSET(...))을 설정해 보았습니다. 그러나 이를 시도한 것은 수식에 일부 활동이 누락되어 제약 조건에도 불구하고 일부 활동이 시작되거나 의도한 기간보다 오래 지속된다는 것을 의미했습니다.
- 하루 단위로 하기보다는 매주/월 단위로 해보았어요. 확실히 계산 속도는 빨라졌지만 이 수준의 세분성은 정확한 결과를 제공하지 못했기 때문에 매일 계속해야 했습니다.
TL:DR:
=IF($B10=1, IF(AND(AO$3>=$C10,AO$3<=$D10),1,""), IF(AND(AO$3>=$C10)를 더 최적화할 수 있는 방법이 있습니까? ,SUM(AO$4:AO9)<$D$13,SUM($F10:AN10)<$E10),1,"")). 감사합니다.
답변1
작업할 셀이 엄청나게 많습니다. 도우미 열을 사용하여 실제 시작 날짜와 종료 날짜를 계산하고 오른쪽 계산을 훨씬 더 간단하게 만들겠습니다.
- 실제 시작 공식:
=IF(COUNTIF($F$1:F1,">="&B2)<$C$11,B2,LARGE($F$1:F1,$C$11)+1) - 실제 종료 공식 :
=E2+D2-1
이제 달력 부분에서 다음과 같은 수식을 사용할 수 있습니다.=IF(AND(X$1>=$E2,X$1<=$F2),1,"")
업데이트
공식 작동 방식 :
COUNTIF($F$1:F1,">="&B2)- 원하는 시작 후 완료된 이전 활동 수를 계산합니다.COUNTIF(... )<$C$11- 제약조건과 비교=IF(.... ,B2,...- 원하는 시작을 유지 가능=IF(... ,... ,LARGE($F$1:F1,$C$11)+1)- 지연이 필요한 경우 시작을 제한하는 활동 중 가장 빨리 끝나는 활동을 확인하고 다음 날 시작하려면 +1하세요.
답변2
행의 99.9%에 대해 상수 비교가 필요하지 않으므로 행 1의 격리된 사례를 제거하면 약간의 CPU 시간을 얻을 수 있습니다. 헤더 바로 아래에 빈 행 하나를 삽입하고 첫 번째 IF를 버리면 됩니다.
=IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<$D$13,SUM($F10:AN10)<$E10),1,"")
더욱이, 동일한 종료 날짜 비교를 수행하고 있는 AO$3<=$D10것으로 보입니다 . SUM($F10:AN10)<$E10합계 없이 빠른 단일 비교를 사용하는 것이 좋습니다.
=IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<$D$13,AO$3<=$D10,1,"")
=3다음으로 셀 조회를 방지하기 위해 명명된 범위 "제약 조건"을 에 대한 상수( 참조)로 정의합니다 .
=IF(AND(AO$3>=$C10,SUM(AO$4:AO9)<constraint,AO$3<=$D10,1,"")
다음으로 AND 대신 중첩된 IF를 선택합니다. 그러면 창 조건이 이미 FALSE인 경우 Excel에서 매번 CPU 집약적인 "SUM"을 계산할 필요가 없습니다. 거짓일 확률이 가장 높은 순서대로 IF를 정렬합니다.
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(SUM(AO$4:AO9)<constraint,1,""),""),"")
마지막으로 SUM을 COUNT로 바꿉니다.
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(COUNT(AO$4:AO9)<constraint,1,""),""),"")
외모도 중요하기 때문이죠:
이미 논의한 것처럼 조건부 서식은 의문의 여지가 없습니다. 그러나 1 대신 "█"와 같은 ASCII 문자를 사용할 수 있습니다.
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(ROW(AO9)-ROW(AO$4)+1-COUNTBLANK(AO$4:AO9)<constraint,1,"█"),""),"")
그러나 이는 이전 "█"의 수를 더 복잡하게 계산하는 대가로 발생합니다. 더 빠른 대안을 위해 Webdings 글꼴 형식의 숫자 4를 사용하십시오(거의 다음과 유사함 ►).
=IF(AO$3<=$D10,IF(AO$3>=$C10,IF(COUNT(AO$4:AO9)<constraint,4,""),""),"")
0~9 사이의 숫자를 사용하고 글꼴을 변경하여 보다 시각적인 "막대형 차트" 효과를 얻을 수 있습니다. 숫자여야 하지만 그렇지 않으면 COUNT가 작동하지 않으며 더 느린 "█" 유형 COUNTBLANK 수식을 사용해야 합니다.
답변3
몇 가지 간단한 작업만으로도 속도가 크게 향상됩니다. 가장 중요한 것은 셀을 분석하는 방법을 재정렬하는 것입니다.
Excel에서는 가능한 한 왼쪽에서 오른쪽으로, 발생한 청크별로 계산하며, 수식 논리가 강제로 적용하는 경우에만 이탈합니다. 한 가지 차이점은 테스트를 수행하면 가능한 경우 곧바로 결과로 이어진다는 것입니다.
따라서 긴 수식의 첫 번째 부분으로 테스트가 있고 IF()테스트 결과가 간단한 결과인 경우 다른 분기를 평가하지 않습니다.You have such a thing that would cut away almost all of the calculating you are doing.
그것은 AO$4:AO4제약 조건에 대한 테스트입니다. 해당 계산이 실패하면 즉시 출력되며 더 이상 처리가 필요하지 않습니다. 해당 셀에 대해서만 끝납니다. 따라서 먼저 테스트하려면 수식을 다시 주문하세요. 이렇게 하면 모든 행 대신 세 개의 행만 이 테스트보다 추가 계산을 볼 수 있습니다.
("첫 번째"에 대해 말하면: Mobus모든 셀에서 "첫 번째 줄" 계산 수행을 중지합니다. 위에 표시된 범위 AO$4:AO4(분명히 열마다 다름)를 사용하고 다른 행과 마찬가지로 첫 번째 행을 처리합니다. 그런 다음 범위는 현재와 마찬가지로 고정되고 확장되며 다른 행과 마찬가지로 "행 1"을 처리하면 이 문제에 도움이 되지 않습니다. )
SUM()다음으로 기존 완료를 테스트하는 행에 대해 또는 계산을 수행합니다 COUNT(). 거의 모든 행이 완료된 상태로 존재할 만큼 충분한 행이 있는 것처럼 보이므로 날짜가 관심을 끄는지 먼저 테스트하면 어쨌든 이를 확인해야 합니다. 먼저 수행하고 테스트할 날짜를 아주 적은 수로 줄이세요. 다시 말하지만, 해당 계산은 어쨌든 실행되며 이동해도 그에 비해 이득도 손실도 없지만 먼저 수행하면 다른 계산이 많이 줄어듭니다.
다른 답변 및/또는 의견에서 언급된 것은 명명된 범위에 대한 아이디어입니다. 나는 명명된 범위와 도우미 열(심지어 도우미 페이지)에 대해서도 관심이 있지만 명명된 범위 대신 셀에 존재하는 제약 조건은 중요하지 않습니다. 항상 위치를 바꾸세요. Excel은 계산 체계를 구축하고 첫 번째 계산 후에 변경 사항이 있는 분기에 대해서만 계산을 트리거합니다. 제약 조건을 변경되지 않는 위치에 배치하고 편집하지 마세요. Excel에서는 다시 계산하는 데 시간을 소비할 필요가 없습니다. 데이터 영역 아래에 놓고 새 작업을 삽입할 때마다 행을 변경하게 하면 그렇게 됩니다. 그런 종류의 지속적인 변화를 볼 수 없는 곳에 놓으면 다시 계산할 필요가 없습니다. 명명된 범위는 이를 수행하는 쉽고 멋진 방법이지만 원할 경우 스프레드시트의 해당 측면을 간단히 다시 정렬할 수 있으며 가끔씩 변경하고 싶은 경우 또는 사용자가 그렇게 하도록 할 수도 있습니다. 그래서. 그러나 이 경우 제약 조건을 변경하면 수식은 스프레드시트의 결과를 완전히 변경하게 되며 이를 원하지 않을 경우 명명된 범위 대신 시트에 유지하는 이유는 무엇입니까? 명명된 범위에는 다른 장점도 있지만 이 문제에서는 중요하지 않습니다. 그러나 그렇게 한다면 주소가 계속 변경되지 않는 곳에 두십시오(대체로 변경된 내용에 관계없이 "변경은 변경입니다"). 이는 속도 문제에 중요하지 않습니다.
나는 속도 SUM()와 같은 문자열 연산과 같은 산술을 직접 다루는 내용을 읽은 적이 없습니다 . COUNT()다른 사람들이 읽은 것처럼 보이며 문자열 작업이 더 빠릅니다. 이 경우, 사용하는 모든 항목을 SUM()use로 변경하세요 COUNT()COUNTA(). 산술이 이길 것이라고 추측했지만, 거기까지입니다.
예, 귀하의 의견에 따르면 이미 도착한 작업 시작 날짜만 중요하며 예상 완료 날짜는 아닙니다. 그래서 그것만 확인하는 것이 중요합니다. 너무 오래된 작업을 포기하는 것이 문제에 포함되지 않기 때문입니다. 따라서 무슨 일이 있어도 완료되지 않은 작업이 1, 2, 3인 경우에는 원하는 완료 날짜에 관계없이 완료됩니다.
그러나 당신이 할 수 있는 모든 것 중 가장 큰 변화는 위의 모든 것(다른 답변 포함)을 실제로 왜소하게 만드는 것입니다...STOP recalculating every line over and over again.
작업이 완료되면 제시된 논리에서 다시 처리되지 않습니다. 그래서why keep addressing it again and again???
주기적으로(주간 또는 월간 제안) 완성된 모든 줄을 복사하고 해당 값을 붙여넣습니다. 그래서 그들은 영원히 끝났습니다. 아마도 15,000개의 수식이 아닌 100개의 행이 남아 있을 것입니다. (한 번에 3개의 작업을 수행하면 2,000개의 작업을 나열할 수 없으므로 100개를 선택했지만 20개라도 더 그럴듯해 보입니다.) 따라서 이러한 계산은 절대, 절대, 절대 다시 수행되지 않습니다. 거기에서 속도 향상을 생각해보십시오.
현재 상황에 비하면 마음이 애처롭습니다.
그러면 흥미로운 이점도 있을 것입니다. 가장 큰 것은 용량이 증가/감소하면 제약 조건이 변경될 수 있다는 것입니다. 위에서 언급했듯이 현재 변경되면 과거 결과가 완전히 파괴됩니다. 그러나 가치로 전환하더라도 기존 자료는 영향을 받지 않습니다. 그것을 바꾸고 싶습니까? 변경 지점 위의 모든 행에 대한 값을 복사하여 붙여넣고 변경한 후 다시 변경할 때까지 계속 진행합니다.
또 다른 방법은 계산 부하가 작을수록 동적 주소 지정과 같은 기능을 보다 광범위한 방식으로 사용할 수 있다는 것입니다. 비록 이제 포인트는 훨씬 작아지겠지만 말입니다.
그건 그렇고, 작업 속도를 높이기 위해 사용하는 도우미 열을 사용하면 다른 열의 공식을 쉽게 하기 위해 일반적인 "어려운 작업을 개별적으로 수행"하는 아이디어와 다릅니다. 이 경우 요점은 도우미 열에 있는 기본 수식의 변경되지 않는 부분을 분리하여 Excel이 해당 부분을 한 번 계산한 다음 어떻게든 변경된 경우에만 계산하도록 하는 것입니다. 따라서 일반적으로 수식에 11개의 매개변수가 있지만 7개는 전혀 변경되지 않거나 거의 변경되지 않는 경우 해당 효과가 도우미 열에서만 발생하고 나머지 수식은 결과를 단일 매개변수처럼 읽도록 수식을 수정합니다. 매개변수를 처리하는 방법에 대한 매우 다른 접근을 포함하여 많은 개편이 필요할 수 있지만 일반적으로 그렇게 할 수 있으며 그런 다음 해당 부분은 다시 계산되지 않으므로 모든 것이 훨씬 빠르게 실행됩니다. 가끔은 할 수 없는 경우가 있습니다. {때때로 간단하게 작동하지 않고 `IF(ISERROR())}를 사용해야 하는 것처럼 IFERROR()수행할 수 있는 작업은 약간만 이익이 됩니다. 하지만 500,000개 이상의 공식이 있지만 그 중 일부는 여전히 매우 먼 길을 가고 있습니다.
간트 차트 측면에 관해서는 조건부 서식("CF")이 속도를 늦추는 부분이라고 생각합니다. (나는 그런 언급을 보지 못했지만 1이 있는 간트 차트는 멋진 선을 만들기 위해 CF를 사용하는 것만큼 가능성이 없다고 가정합니다. 에서 언급했듯이 MobusCF를 사용하는 것보다 더 좋은 방법이 있습니다. Mobus말한 내용에 추가됨 적합한 "블록" 문자를 선택할 수 있으며 필요한 경우 Alignment|Horizontal 아래의 "Fill" 기능을 사용하여 셀을 채우도록 하면 크기와 모양을 일치시키는 것이 덜 중요하지만 일치하도록 글꼴을 조정해야 합니다. 행의 높이까지 문자를 REPT()서버합니다.
그러나 "노래"하게 하려면(특히 관련된 계산을 크게 줄이기 위해 위의 방법을 사용한 것처럼) 함수를 통해 출력을 표시 TEXT()하고 글꼴 색상을 포함하도록 형식 지정 문자열을 작성할 수 있습니다. 이렇게 하면 각 행의 행에 대해 위아래 행과 다른 색상을 지정할 수 있으므로 색상이 서로 옆에 배치되지 않아 가독성이 향상됩니다. 해당 부분은 속도 효과뿐만 아니라 다른 모든 문제가 있는 CF가 아닌 셀 측 공식에 있습니다.
귀하와 귀하의 사용자가 반드시 사용할 수 있는 것은 아니라는 점을 제외하고 Access 또는 유사한 프로그램으로 전환하는 것은 500,000개의 셀이 대부분 행이 아닌 "오른쪽에 쌓이는 셀"인 것처럼 보이기 때문에 놀라운 도움이 될 것 같지 않습니다. 쌓여있다. 각각 5개의 셀이 있는 100,000개의 작업이 있으면 Access 또는 유사하거나 프로젝트 전용으로 계산이 필요합니다. 그러나 오른쪽에 3년의 날짜가 있는 500개의 행(행당 1,000-1,100개의 계산)이 있으면 데이터베이스 프로그램이 필요하지 않습니다(여전히 전용 소프트웨어가 필요하지만... 항상 가능한 것은 아닙니다. 아프다). 대중적인 "지식"과는 달리 Excel은 일종의 "1월의 당밀"처럼 느린 계산 기계가 아니며 SQL은 오늘날 우리 세상에 어떻게든 존재하는 항상 밝고 반짝이는 41세기 신동이 아닙니다. 어차피 위의 작업을 수행하므로 계산 부담이 현재의 작은 부분으로 줄어들고 있으므로 ...