교대 근무 목록 옆에 이름 목록이 있습니다. 나는 각 교대조에 있는 사람들의 목록을 매일 자동으로 동적으로 업데이트하고 싶습니다. 나는 그것을 할 수 있는 최선의 방법을 찾을 수 없습니다.
기본 명단에는 직원 이름 열과 교대 근무 열 14개가 있습니다. 그런 다음 첫 번째 테이블에서 누가 어떤 교대 근무를 하는지에 따라 14개의 이름 목록을 만들고 싶습니다.
예: 기본 범위에는 다음이 포함됩니다.
A B C D E
---------- --- --- --- ---
1 Joe Blogs E E L O
2 Jill Bleg L L E E
3 Geoff Ted O L L L
예를 들어 D 열을 조회하고 Joe Blogs를 나열하기 위해 L의 첫 번째 인스턴스를 검색한 다음 Geoff Ted를 나열하기 위해 L의 두 번째 인스턴스를 검색하고 싶습니다.
셀 범위 내에서 N번째 문자열 인스턴스를 검색할 수 있는 방법이 있습니까?
답변1
이것은 보이는 것만큼 쉽지 않습니다. 먼저 D열에 있는 모든 "L"의 행 번호를 찾으려면 다음을 사용합니다.
=IF(D1:D3="L"; ROW(D1:D3))
이것을 다른 이름으로 저장배열 수식: 입력한 후 Ctrl+Shift+Return(또는 Mac에서는 Command+Shift+Return)을 누르세요. 그러면 그럴 것이다보여주다중괄호 안에. 그리고 그 결과에는 단일 셀이 아니라 작업 중인 범위만큼의 셀이 포함됩니다. 위의 예에서는 값이 1, 공백, 3인 3개의 셀이 표시됩니다.
다음,사용SMALLN번째 값을 찾으려면:
SMALL(numberlist; n)(순서가 지정되지 않은) 범위 또는 숫자 배열 내에서
n번째로 작은 숫자를 반환합니다numberlist.
위의 내용을 적용하면 SMALL결과적으로 단일 셀로 돌아갑니다.
=SMALL(IF(D1:D3="L"; ROW(D1:D3)); 1)
=SMALL(IF(D1:D3="L"; ROW(D1:D3)); 2)
결과가 단일 셀임에도 불구하고 Ctrl+Shift+Return을 사용하여 저장해야 합니다.
이제 행 번호를 알면 INDEX다음의 첫 번째 열에서 이름을 찾을 수 있습니다 A1:A3.
=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); 1); 1)
=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); 2); 1)
다시 말하지만 Ctrl+Shift+Return을 사용하여 모든 항목을 저장해야 합니다.
그러나 순위 "1"과 "2"는 자동으로 "3"이 되지 않으므로 이러한 수식을 끌어서 다른 셀로 확장할 수는 없습니다. 대신, 공식이 있는 행을 기반으로 필요한 순위를 계산하려면 다음을 수행하십시오.
=INDEX(A1:A3; SMALL(IF(D1:D3="L"; ROW(D1:D3)); ROW()); 1)
이것을 행 1의 어딘가에 배열 수식으로 저장한 후 아래로 끌어서 행 2, 3 등에 추가할 수 있습니다.
또는 결과를 확장하는 대신 배열 수식을 복사/붙여넣어 열 및 행 참조를 즉시 조정할 수 있습니다. 아래 스크린샷에서는 B7, B12 및 B17의 다음 내용을 다른 셀에 복사했습니다.
=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$6; ROW(B$1:B$3)); ROW()-ROW(B$6)); 1)
=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$11; ROW(B$1:B$3)); ROW()-ROW(B$11)); 1)
=INDEX($A$1:$A$3; SMALL(IF(B$1:B$3=B$16; ROW(B$1:B$3)); ROW()-ROW(B$16)); 1)
배열 수식의 키보드 단축키는 실제로 수식을 변경한 후에만 작동합니다. 간단히 Return을 누른 다음 다시 수식으로 들어가서 Ctrl+Shift+Return을 누르면 아무런 효과가 없습니다.
또한 배열 수식이 확장되면 해당 수식을 변경하려면 모든 결과 셀을 선택해야 합니다. 그렇지 않으면 당신은 얻을 것이다"배열의 일부만 변경할 수는 없습니다.".