누구든지 Microsoft Excel에서 열에서 비어 있지 않은 마지막 셀의 값을 찾는 수식을 알고 있습니까?
답변
이것은 텍스트와 숫자 모두에서 작동하며 빈 셀이 있는지 상관하지 않습니다. 즉, 비어 있지 않은 마지막 셀을 반환합니다.
배열 입력이어야합니다 . 즉, 입력하거나 붙여 넣은 후 Ctrl-Shift-Enter를 누릅니다 . 아래는 A 열에 대한 것입니다.
=INDEX(A:A,MAX((A:A<>"")*(ROW(A:A))))
답변
다음과 같은 간단한 공식을 사용하는 것이 훨씬 빠릅니다.
=LOOKUP(2,1/(A:A<>""),A:A)
Excel 2003의 경우 :
=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)
다음과 같은 이점이 있습니다.
- 배열 수식 이 아닙니다.
- 휘발성 공식 이 아닙니다
설명:
(A:A<>"")반환 배열{TRUE,TRUE,..,FALSE,..}1/(A:A<>"")이 배열을{1,1,..,#DIV/0!,..}.- 이후
LOOKUP상하는 정렬 로 배열 오름차순 순서 및 경우 것을 고려한LOOKUP함수는 정확한 매치를 찾을 수는 최대 값을 선택lookup_range(우리의 경우{1,1,..,#DIV/0!,..}(우리의 경우 나 값과 같은보다 작하다)2), 수식은1배열에서 마지막 을 찾고result_range(세 번째 매개 변수-A:A) 에서 해당 값을 반환합니다 .
또한 약간의 참고 사항 -위의 수식은 오류가있는 셀을 고려하지 않습니다 (비어 있지 않은 마지막 셀에 오류가있는 경우에만 볼 수 있음). 이를 고려하려면 다음을 사용하십시오.
=LOOKUP(2,1/(NOT(ISBLANK(A:A))),A:A)
아래 이미지는 차이점을 보여줍니다.
답변
다음은 또 다른 옵션입니다. =OFFSET($A$1;COUNTA(A:A)-1;0)
답변
Doug Glancy의 답변이 제공 한 위대한 리드에 영감을 받아 어레이 공식 없이도 동일한 작업을 수행 할 수있는 방법을 생각해 냈습니다. 이유를 묻지 마십시오.하지만 가능한 경우 배열 수식을 사용하지 않으려 고합니다 (특정한 이유가 아니라 제 스타일 일뿐입니다).
여기있어:
=SUMPRODUCT(MAX(($A:$A<>"")*(ROW(A:A))))
A 열을 참조 열로 사용하여 비어 있지 않은 마지막 행 찾기
=SUMPRODUCT(MAX(($1:$1<>"")*(COLUMN(1:1))))
행 1을 참조 행으로 사용하여 비어 있지 않은 마지막 열 찾기
이것은 동적으로 명명 된 범위를 효율적으로 정의하기 위해 인덱스 기능과 함께 더 활용 될 수 있지만, 이것은 여기에서 다루는 즉각적인 질문과 관련이 없기 때문에 다른 게시물에 대한 것입니다.
위의 방법을 Excel 2010에서 “기본적으로”및 “호환성 모드”(이전 버전의 Excel 용)에서 테스트했으며 작동합니다. 다시 말하지만, Ctrl + Shift + Enter를 할 필요가 없습니다. sumproduct가 Excel에서 작동하는 방식을 활용하여 배열 작업을 수행해야하는 필요성을 파악할 수 있지만 배열 수식없이 수행합니다. 나는 누군가가 제안 된 합산 솔루션의 아름다움, 단순성 및 우아함을 나만큼이나 평가할 수 있기를 바랍니다. 위의 솔루션의 메모리 효율성을 증명하지는 않습니다. 단순하고, 아름답게 보이며, 의도 한 목적을 돕고, 다른 목적으로 사용을 확장 할 수있을만큼 유연합니다. 🙂
도움이 되었기를 바랍니다!
모두 제일 좋다!
답변
이 질문이 오래되었다는 것을 알고 있지만 제공된 답변에 만족하지 않습니다.
-
LOOKUP, VLOOKUP 및 HLOOKUP에는 성능 문제가 있으므로 절대 사용해서는 안됩니다.
-
배열 함수는 오버 헤드가 많고 성능 문제도있을 수 있으므로 최후의 수단으로 만 사용해야합니다.
-
COUNT 및 COUNTA는 데이터가 연속적으로 비어 있지 않은 경우 문제가 발생합니다. 즉, 공백이 있고 해당 범위에 다시 데이터가있는 경우
-
INDIRECT는 휘발성이므로 최후의 수단으로 만 사용해야합니다.
-
오프셋은 변동성이 있으므로 최후의 수단으로 만 사용해야합니다.
-
가능한 마지막 행 또는 열 (예 : Excel 2003의 65536 번째 행)에 대한 참조는 강력하지 않으며 추가 오버 헤드가 발생합니다.
이것이 내가 사용하는 것입니다
-
데이터 유형이 혼합 된 경우 :
=max(MATCH(1E+306,[RANGE],1),MATCH("*",[RANGE],-1)) -
데이터에 숫자 만 포함 된 것으로 알려진 경우 :
=MATCH(1E+306,[RANGE],1) -
데이터에 텍스트 만 포함 된 것으로 알려진 경우 :
=MATCH("*",[RANGE],-1)
MATCH는 오버 헤드가 가장 낮고 비 휘발성이므로 많은 데이터로 작업하는 경우 사용하는 것이 가장 좋습니다.
답변
이것은 Excel 2003에서 작동합니다 (나중에 사소한 편집 포함, 아래 참조). Ctrl + Shift + Enter (Enter뿐만 아니라)를 눌러이를 배열 수식으로 입력합니다.
=IF(ISBLANK(A65536),INDEX(A1:A65535,MAX((A1:A65535<>"")*(ROW(A1:A65535)))),A65536)
Excel 2003 에서는 전체 열에 배열 수식을 적용 할 수 없습니다 . 그렇게하면 수익이 발생합니다 #NUM!. 예상치 못한 결과가 발생할 수 있습니다! (편집 : 같은 : Microsoft에서 정보를 충돌하는 일을 하거나 하지 않을 수 있습니다 엑셀 2007에 대한 사실, 문제가 있습니다 2010에서 수정 된 )
그건 내가 범위에 배열 수식을 적용 할 이유 A1:A65535이며 마지막 셀, 특별한 치료를 포기 A65536단지 말할 수 엑셀 2003 년 A:A또는 A1:A65536후자 자동으로 되돌립니다로를 A:A.
A65536비어 있다고 확신하는 경우 해당 IF부분을 건너 뛸 수 있습니다 .
=INDEX(A1:A65535,MAX((A1:A65535<>"")*(ROW(A1:A65535))))
Excel 2007 또는 2010을 사용하는 경우 마지막 행 번호는 65536이 아니라 1048576이므로 위의 내용을 적절하게 조정하십시오.
데이터 중간에 빈 셀이 없으면 더 간단한 공식 인 =INDEX(A:A,COUNTA(A:A)).
답변
가능한 배열 수식없이 대안적인 솔루션 보다 강력한 보다도 배열 수식없이 용액 A (a 행 힌트)와 이전의 응답은 , 인
=INDEX(A:A,INDEX(MAX(($A:$A<>"")*(ROW(A:A))),0))
이 답변 을 예로 참조하십시오 . 했네 브래드 와 배리 후디니 해결 도움이, 이 질문을 .
배열이 아닌 공식을 선호하는 가능한 이유는 다음과 같습니다.
-
공식 Microsoft 페이지 ( “배열 수식 사용의 단점”찾기)
배열 수식은 마법처럼 보일 수 있지만 몇 가지 단점도 있습니다.- 때때로 Ctrl + Shift + Enter를 누르는 것을 잊을 수 있습니다. 배열 수식을 입력하거나 편집 할 때마다이 키 조합을 눌러야합니다.
- 다른 사용자는 귀하의 공식을 이해하지 못할 수 있습니다. 배열 수식은 비교적 문서화되어 있지 않으므로 다른 사람이 통합 문서를 수정해야하는 경우 배열 수식을 사용하지 않거나 해당 사용자가 변경 방법을 이해하고 있는지 확인해야합니다.
- 컴퓨터의 처리 속도와 메모리에 따라 큰 배열 수식을 사용하면 계산 속도가 느려질 수 있습니다.
-
배열 공식 이단 .
