☰

변수 범위를 참조하는 수식 얻기

tag-icon • tag-icon microsoft-excel
변수 범위를 참조하는 수식 얻기

그래서 저는 다양한 수의 셀에 걸쳐 작동하는 방정식을 얻으려고 노력하고 있습니다. 그래서 제가 가지고 있는 것은 현재 비용과 예상 비용이 포함된 시트입니다. 현재 비용만으로 예측 수치를 계산하고 싶습니다. 이는 월말에 ACT로 표시됩니다. 순환 참조 오류 생성으로 인해 sumifs/countifs의 초기 공식이 거부되었으므로 다른 셀에 따라 방정식을 동적으로 업데이트할 수 있는 방법이 있는지 궁금합니다.

저는 동적 범위를 사용하지 않는 것을 선호합니다. 왜냐하면 우리는 업데이트가 필요한 약 400줄에 대해 이야기하고 있고, 범위는 한 번에 하나씩 추가해야 한다는 것을 제가 이해하고 있기 때문입니다.

제가 원하는 수식을 나타내는 예는 =Sum(A1:A(1+B1))입니다. 즉, B1이 증가하면 범위도 증가합니다.

샘플 데이터:

    Act   Act   Act   Proj   Proj  
A:   1     2     3      2      2
B:   1     3     5      3      3
C:   4     5     6      5      5
  • 프로젝션 A: 2
  • 프로젝션 B: 3
  • 프로젝션 C: 5

그런 다음 4번째 달의 실제 숫자를 추가하고 Proj를 Act로 변경하면 추가 입력 없이 예측 A, 예측 B 등이 3에서 4까지의 변경 사항을 계산하는 범위가 됩니다.

답변1

OFFSET()다음과 같이 XxY 열 범위에 대한 참조를 생성하는 데 사용할 수 있습니다 .

=OFFSET(A1,0,0,10,10)을 참조하는 10x10 참조를 생성합니다 A1:J10.

위 수식에서 하드코딩된 10을 COUNTIF()범위가 참조해야 하는 행과 열 수를 유추하는 로 바꿀 수 있습니다.

AltMMD순환성 오류를 방지하려면 이 수식을 사용하여 명명된 범위( )를 정의하고 수식을 사용할 때마다 명명된 범위를 사용해 볼 수 있습니다 .

OFFSET()대안으로, 너비 및 높이 매개변수 없이(예: 위 수식에서 10)를 사용하고 대신 범위의 첫 번째 셀과 마지막 셀을 찾아서 XxY 참조로 텍스트 문자열을 작성할 수 있습니다 . 그런 다음 CELL("address",...)해당 주소를 반환하는 호출 로 래핑합니다 .(주의: 시작점이 $A$1인 경우 끝 셀만 찾으면 됩니다.)

이들을 사이에 연결한 :다음 INDIRECT()필요한 모든 위치에 모든 것을 래핑합니다. 요약하면 다음에서 하드코딩된 시작을 가정합니다 $A$1.

=INDIRECT("Sheet1!$A$1:"&CELL("address",OFFSET(Sheet1!$A$1,COUNTIF(..x-rows..),COUNTIF(..y-cols..))

COUNTIF()독자에게 연습으로 남겨진 행과 열의 수를 찾는 논리

관련 정보