A 열에서 특정 값이있는 이전 행 찾기

debugcn 에 게시 Dev

짐밥 맥기

공식이 있거나없는 샘플 시트

의미 론적 'header', 'subheader'및 'subtotal'행이있는 위의 샘플 시트가 주어지면 현재 셀을 기준으로 이전 하위 헤더 행을 찾는 수식을 결정하려고합니다. 예를 들어 수식이에 입력 된 경우 F5행을 찾고에 2입력하면 F17행을 찾습니다 13.

행이 조건 값의 존재에 의해, 헤더, 서브 헤더 또는 합계로 포맷되고 H, S또는 T열에서 $A:$A, 즉, 서브 헤더의 행 n위치를 행이다 $An = "S". 이제이 개념을 내 공식으로 확장하고 싶습니다.

헤더 행 다음에는 항상 하위 헤더가옵니다 (따라서 순서가 잘못된 헤더와 하위 헤더에 대해 걱정할 필요가 없습니다).

나는 다음을 시도했다 :

=MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0)

집합에서 첫 번째 일치 항목을 반환하고 높이를 제한 할 수 2없기 때문에 항상 row 를 MATCH반환합니다 (즉, 이전 하위 헤더 위치를 알 수 없기 때문에 재귀 적으로).OFFSET
{=LARGE(MATCH("S", OFFSET($A5, 0, 0, -ROW($A5), 1), 0), 1)}

이것은 또한를 반환합니다 2. 왜냐하면 배열 컨텍스트 (즉, Ctrl+ Alt+ 사용 Enter)에서도 MATCH여전히 첫 번째 결과를 반환하기 때문입니다.
=LARGE(IF(OFFSET($A5, 0, 0, -ROW($A5), 1)="S", ROW(OFFSET($A5, 0, 0, -ROW($A5), 1))), 1)

이 반환 0하기 때문에, IF여기에 배열을 기대되지 않으므로 확장 OFFSET($A5, 0, 0, -ROW($A5), 1)한 값 0과 일치하지 않는, "S"및 LARGE취급 FALSE숫자로;
{=LARGE(IF(OFFSET($A5, 0, 0, -ROW($A5), 1)="S", ROW(OFFSET($A5, 0, 0, -ROW($A5), 1))), 1)}

이것은 #VALUE배열 확장이 너무 일찍 일어나기 때문에를 반환합니다 . 이는 유효한 숫자 매개 변수 가 아닌 -ROW($A5)array 로 남겨 집니다 ( 비트가 비트가 아닌 배열 이기를 원했지만 Excel을 구별 할 수 없음).-{5}heightOFFSETIF(OFFSET(...)="S",...)-ROW($A5)

현재 Excel 2010을 대상으로하고 있습니다. 이전 버전은 적용 할 수 없습니다 (향후 호환성은 보너스이지만). * .xlsx보다 * .xlsm 파일을 배포하는 것이 더 어렵 기 때문에 VBA를 사용하지 않으려 고합니다 (게다가 VBA로 수행하는 방법을 이미 알고 있습니다).

내가 시도 할 수있는 다른 것이 있습니까?

바이런 Wall

이를 수행하는 쉬운 방법은 절대 / 상대적 혼합 공식을 속이고 사용하는 것입니다. 이것은 셀에 입력 된 배열 수식 (CTRL + SHIFT + ENTER로 입력) B4이지만 4 행의 어느 곳 으로든 이동할 수 있습니다 S.으로 표시된 항목의 행 번호를 반환합니다 .

=MAX(IF($A$1:A4="S",ROW($A$1:A4)))

아래로 복사하면 참조의 두 번째 부분 B4 and A4이 증가합니다. 이렇게하면 현재 행 위에 가장 많이 일치하는 행을 얻을 수 있습니다. F4관련 범위를 입력 / 선택한 후 사용하면 이러한 수식을 더 빠르게 입력 할 수 있습니다 . 이것은 모든 선택을 통해 달러 기호를 순환합니다.

범위 그림

데이터 및 결과 그림

수식을 대체하는 데 사용

질문을 조금 읽은 후 (및 @SteveTaylor의 편집 내용을 기반으로 함) 수식을 업데이트하는 데 사용하는 것 같습니다. 위에서 반환 된 행을 함께 사용 INDEX하여 합계 할 데이터 범위를 가져올 수 있습니다. 대체 할 수있는 두 가지 공식이 있습니다.

레이블이 지정된 각 데이터 행에 대한 총 계산. 이 경우 위의 소계 행을 동적으로 참조 할 수 있습니다.
Total calcualtion for the substotal row. In this case the values to sum from above can be reference dynamically.

For the single row data, you can use the formula, starting in F3 as an array formula. Note that I switched over to using SUMPRODUCT which makes it much easier to go to more than 2 columns.

=C3*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A3="S",ROW($A$1:A3))),),D3:E3)

For the total row formula, you can use, starting in F11, again array formula:

=SUM(F10:INDEX($F$1:F10, 1+MAX(IF($A$1:A11="S",ROW($A$1:A11)))))

If you want one formula to rule them all! then you can combine these into a nested IF based on the value in column A. Here is said array formula, starting in F2 which can be copied down.

=IF(
  A2="S", 
  SUM(D2:E2), 
    IF(A2="T", 
      SUM(F1:INDEX($F$1:F1, 1+MAX(IF($A$1:A2="S",ROW($A$1:A2))))), 
      C2*SUMPRODUCT(INDEX(D:E,MAX(IF($A$1:A2="S",ROW($A$1:A2))),),D2:E2)))

This formula does not differentiate between a blank row and a "data" row. It currently returns 0 for the spacer row which is fine.

데이터의 두 블록에 대한 결과 및 공식 그림 .

여기에 이미지 설명 입력

이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.

침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제

에서 수정2021-06-1

몇 마디 만하겠습니다

0리뷰

로그인참여 후 검토

Related 관련 기사

기사

A 열에서 특정 값이있는 이전 행 찾기

A 열에서 특정 값이있는 이전 행 찾기

열에 대한 특정 값이있는 행 찾기 (matlab)

모든 열에서 특이 치 값이있는 행 찾기

주어진 행에서 특정 값이있는 다음 행 찾기

특정 행이있는 테이블에서 잠금 찾기 Sybase

특정 열에서 특정 값이있는 중복 행 제거

내부에 특정 행 값이있는 열 / 테이블을 찾는 방법

pandas-dtype 목록 (객체) 열의 값에 특정 값이있는 경우 행 찾기

다른 열의 행에 특정 값을 포함하는 Pandas에서 열 이름 찾기

다중 값 특정 열이있는 여러 행의 동일한 값에서 하나의 행 가져 오기

행에서 특정 값이있는 첫 번째 항목과 마지막 항목 찾기

R에서 특정 행 값이있는 열 삭제

SELECT에서 특정 열 값이있는 행 제외

테이블 이름, 행 및 열 인덱스가있는 테이블에서 특정 값 가져 오기

특정 열에 특정 값이있는 행 삭제

열에서 중복 값이있는 행 찾기

AWK를 사용하여 특정 열에서 특정 값이있는 행 선택

Python : 다른 열의 특정 값이있는 행에서만 열의 값 계산

특정 ID로 데이터 테이블에서 행 찾기

Python의 데이터 프레임에서 특정 행에 대해 특정 열에서 일치하는 값의 수 찾기

값이 PySpark Dataframe의 특정 열 사이에있는 모든 열 목록 찾기

값이 PySpark Dataframe의 특정 열 사이에있는 모든 열 목록 찾기

특정 열에서 가장 많이 반복되는 값이있는 행 선택

특정 열 사이에 몇 개의 고유 값이 있는지 찾기

특정 열 값이 존재하지 않는 특정 행을 찾기위한 MySQL 쿼리

그들 사이에 특정 시간 델타가있는 행 찾기

특정 열의 평균을 찾고 특정 평균 값이있는 모든 행 유지

특정 열에 반복 값이있는 경우 전체 행 제거

모든 값이 배열에서 나란히있는 특정 값을 포함하는 모든 조합 찾기

PostgreSQL 파티션 기준 및 특정 열 값이있는 첫 번째 행 선택