SUMIF 기능에 대한 명명된 범위의 특정 열 선택

SUMIF 기능에 대한 명명된 범위의 특정 열 선택

나는 내 엑셀 시트에서 지정된 범위의 특정 열에 값을 동적으로 추가하는 SUMIF 함수를 만들려고 합니다.

명명된 범위가 없을 때는 이 작업을 매우 쉽게 수행할 수 있습니다.

공식은 이름에 "런던"이 포함된 모든 셀을 선택하고 런던과 관련된 비용을 합산합니다.

TripsData(A2: 트립 데이터)라는 이름의 범위를 사용하려고 합니다.B5) 및 SUMIF 함수에게 이 범위의 2열에 있는 항목 중 자신의 이름에 런던이 있는 기준을 충족하는 항목을 합산하도록 지시합니다.

2열에 대한 두 번째 명명된 범위를 생성할 필요 없이 Excel에게 이 명명된 범위의 지정된 열 내를 살펴보라고만 하면 어떻게 이 작업을 수행할 수 있습니까?색인/일치는 하나의 값만 반환하므로 이름에 런던이 포함된 셀이 여러 개 있을 때는 값이 작동하지 않습니다.

도와주셔서 감사합니다!

사용하다INDEX범위의 열열을 수 )을 가리키는 입니다.

=SUMIF(TripsData,"*London*",INDEX(TripsData,,2))

데이터를 Excel Table 개체로 변환하면 지정된 범위 없이도 이 작업을 수행할 수 있습니다.범위 또는 전체 범위에서 셀을 선택하고 삽입 > 표를 클릭하거나 T-를 누릅니다.

테이블에 머리글이 있는지 묻는 대화상자가 나타납니다.당신은 그렇습니다.이제 테이블과 테이블의 열을 고유 이름으로 참조하고 다음과 같이 공식을 작성할 수 있습니다.

=SUMIF(Table1[Expense],"*London*",Table1[Cost])

수식이 지정된 후에도 테이블 이름을 변경할 수 있습니다.표에서 셀을 클릭하면 표에만 관련된 명령에 대한 새 리본이 나타납니다.이것은 매우 강력한 도구입니다.

전체 테이블 열에 적용되는 수식, 형식 등은 자동으로 새 테이블 행으로 전달됩니다.테이블 열 참조도 자동으로 조정되므로 동적 범위 이름을 혼동하거나 명명된 범위가 적용되는 대상을 다시 정의할 필요가 없습니다.

참고: 공식은 셀 주소 대신 구조화된 참조를 사용합니다.파일 > 옵션 > 수식 > 수식을 클릭하거나 "공식에 테이블 이름 사용"을 선택 취소하여 이 옵션을 해제할 수 있습니다.

당신은 크리스의 아이디어를 사용할 수 있습니다.Index(Table1,,Col#)(어떤 이유로 원하지 않을 경우 Excel 테이블 객체를 만들지 않고) "Table1"이라는 이름의 범위로 여전히 Applez가 Chris의 아이디어 아래에 언급한 문제를 피합니다.Applez는 나중에 지정된 범위의 열 앞에 다른 열을 삽입할 경우 열 번호 참조에 상수를 사용하는 것이 위험하다고 경고합니다.Excel은 상수를 자동으로 증가시키지 않으므로 공식이 깨집니다.

Applez가 옳습니다. 그러니 상수를 사용하지 말고 상수 대신 열 번호 "참조"를 사용하십시오.예를 들면...

=SUMIF(TripsData,"*London*",INDEX(TripsData,,Column(B1)))

나중에 A와 B 사이에 열을 삽입하면 Excel이 참조를 자동으로 증분합니다.Column(B1)로.Column(C1)삭제하지 않음B1또는Row 1그렇지 않으면 REF 오류가 발생합니다.일반적으로 열 참조 내의 해당 테이블 열에 대해 머리글/타일 "셀"을 사용합니다(전체 열을 삭제하지 않는 한 테이블 열의 머리글/제목 셀을 삭제할 가능성은 거의 없습니다).이 특정한 예에서 밝혀진 바와 같이,B1데이터 테이블의 해당 열의 제목/헤더 셀 "IS".이것이 제가 예시로 사용한 것입니다.

멋진 공식입니다. 누군가가 범위를 필터링하기 위해 유사한 접근 방식을 사용해야 할 경우를 대비해서 말입니다.저는 이 접근법을 사용했습니다.

pmGendHC필터링할 범위(데이터로 유출된 범위 예상) 0과는 다른 열(열 번호 13)이 필요했습니다.

=FILTER(pmGendHC,INDEX(pmGendHC,,13)<>0) 

언급URL : https://stackoverflow.com/questions/40794638/selecting-a-specific-column-of-a-named-range-for-the-sumif-function