배열 수식에서 IFNA를 올바르게 만드는 방법은 무엇입니까?

Question

배경

명명된 범위를 참조하고 해당 값을 표시하려는 문제가 있습니다. 범위는 값의 목록이지만 목록에 몇 개의 요소가 있는지 미리 알 수 없습니다. 목록에 1~8개의 값이 있다고 가정해 보겠습니다.

내 방법은 8개의 긴 범위에서 배열 수식을 사용하고 IFNA()를 사용하여 #N/A 출력을 마스크하는 것입니다. 출력에 빈 공간이 있는 것은 허용되지만 #N/A를 표시하는 것은 그다지 전문적으로 보이지 않습니다. ISNA는 작동하지 않으며 ISNA 동작을 변경하는 배열 수식 때문인 것으로 생각됩니다. 아래 이미지의 예.

솔루션의 한계

데이터는 이후 단계의 피벗 테이블에서 사용되므로 단순히 데이터를 보이지 않게 마스킹하는 것은 불가능합니다. #N/A 값을 가져오지 않기를 바랍니다. 빈 문자열은 괜찮습니다. 빈 문자열도 실제로 정답은 아니지만 적어도 좀 더 우아하게 실패합니다.

제가 일부 동료를 위해 모델을 개발 중이고 다른 사람들이 나중에 추가 변경을 원할 수도 있으므로 솔루션은 다른 사람들을 위해 상대적으로 유지 관리가 쉬워야 합니다. 그러므로 나는 매우 긴 수식과 이름 관리자의 광범위한 사용을 피하고 싶습니다.

질문

N/A를 얻지 않고 가변 크기의 범위를 단순히 배열 복사하는 "적절한" 방법은 무엇입니까?

... 여기서 "적절한"이란 유지 관리가 쉽고 숨겨진 데이터를 포함하지 않는 간단하고 컴팩트한 공식을 의미합니다.

Answer 1

대신 INDEX를 사용하고 이를 출력의 첫 번째 셀에 넣고 원하는 행 수를 복사하세요.

=IFERROR(INDEX(rng_1,ROW(1:1)),"")

ROW(1:1)는 복사되어 줄의 다음 항목을 끌어당기면서 반복됩니다. 다 떨어지면 오류가 발생하고 ""그 자리에 놓이게 됩니다.

Answer 2

N/A를 얻지 않고 다양한 크기의 가변 크기를 단순히 "배열 복사"하는 적절한 방법은 무엇입니까?

나는 그런 것이 없다고 생각하지만 배열이 있는 열의 조건부 서식 지정이나 배열이 아닌 수식 사용과 같은 다른 방법이 문제에 도움이 될 수 있습니다.

조건부 서식:

조건부로 열/범위의 서식을 지정하여 오류 셀 텍스트의 서식을 흰색 또는 셀 색상과 혼합되는 모든 항목으로 변경합니다.

공식 예:

배열이 아닌 수식을 사용하고 수식에서 오류를 처리하도록 하세요.

=IFERROR(IF(INDEX(Pets,ROWS($J$1:J1),COLUMNS($J$1:J1))="","",INDEX(Pets,ROWS($J$1:J1),COLUMNS($J$1:J1))),"")

Answer 3

Scott Craners의 답변을 통해 나는 다음을 결정했습니다.

ROWS명령은 출력이 기록되는 것과 동일한 영역을 참조하므로 오류 발생 가능성이 약간 줄어듭니다 . 이렇게 하면 수식을 인식하지 못한 채 수식을 너무 심하게 파괴하는 행을 삽입해서는 안 됩니다.

나는 이 솔루션에 만족하지 않지만 그렇게 해야 할 것 같습니다...