Excel 동적 배열, 기능 및 공식

Excel 365의 혁신적인 컴퓨팅 엔진 업데이트는 배열 공식을 쉽게 이해할 수있게 해주므로 더 이상 고급 사용자에게 전용 기술이 아닙니다. 이 튜토리얼은 Excel Dynamic Array의 개념을 설명하고이를 사용하여 워크 시트 효율성을 향상시키고 설정을 단순화하는 방법을 보여줍니다.

Excel 어레이 공식은 항상 전문가 및 공식 전문가의 특허로 간주되었습니다. 문장은 "배열 공식으로 해결 될 수 있습니다" "종종 많은 사용자가 즉시"다른 방법이 있습니까? "

다이나믹 어레이의 도입은 오랫동안 기다려온 매우 환영받는 변화입니다. 트릭과 트릭없이 간단한 방식으로 여러 값을 처리 할 수 ​​있기 때문에 모든 Excel 사용자는 동적 배열 공식을 이해하고 쉽게 만들 수 있습니다.

- Excel 동적 배열

• 동적 배열 가용성
• 동적 배열 기능
• 동적 배열 공식의 예
• 오버 플로우 영역 - 하나의 공식, 다중 셀
• 오버 플로우 영역 참조 (# 기호)
• 암시 적 교차 및 @ 문자
• 동적 배열의 장점
• 동적 배열의 제한
• 동적 배열 및 기존 CSE 배열 공식
• 뒤로 호환성
• Excel 동적 배열 공식은 유효하지 않습니다

Excel 동적 배열

동적 배열은 단일 셀에 입력 된 공식을 기반으로 여러 셀로 자동으로 계산하고 값을 반환하는 크기가 크기가 큰 배열입니다.

Microsoft Excel은 지난 30 년 동안 많은 변화를 겪었지만 한 가지 일관성이 있습니다 : 공식, 셀. 전통적인 배열 공식을 사용하더라도 결과를 표시하려는 각 셀의 공식을 입력해야합니다. 동적 배열을 사용하면이 규칙이 더 이상 적용되지 않습니다. 이제 배열 값을 반환하는 모든 공식은 Ctrl 시프트 엔터를 누르거나 다른 작업을 수행하지 않고 인접한 셀로 자동으로 오버플로됩니다. 다시 말해, 동적 배열 조작은 단일 셀을 조작하는 것만 큼 간단합니다.

이 개념을 설명하기 위해 매우 기본적인 예를 사용해 봅시다. 예를 들어 두 세트의 숫자 세트를 곱해야한다고 가정하십시오. 예를 들어 다른 백분율을 계산하십시오.

비 동력 버전의 Excel에서, 다음 공식은 여러 셀에 입력하고 Ctrl Shift Enter를 눌러 배열 공식으로 명시 적으로 설정하지 않는 한 첫 번째 셀에만 적용됩니다.

=A3:A5*B2:D2

이제 Excel 365에서 동일한 공식을 사용할 때 어떤 일이 발생하는지 확인하십시오. 셀에 간단히 입력하십시오 (이 경우 B3) Enter를 누르십시오 ... 전체 영역이 즉시 결과를 작성합니다.

단일 공식으로 여러 셀을 채우는 것을 오버 플로우 라고하며 셀의 채워진 영역을 오버플로라고합니다.

최근 업데이트는 Excel 배열을 다루는 새로운 방법 이상이라는 점에 유의해야합니다. 실제로 이것은 전체 컴퓨팅 엔진에 대한 획기적인 변화입니다. 동적 배열로 일련의 새로운 기능이 Excel 기능 라이브러리에 추가되었으며 기존 기능도 더 빠르고 효율적으로 실행되기 시작했습니다. 궁극적으로 새로운 동적 배열은 Ctrl Shift Enter 바로 가기를 사용하여 입력 한 이전 스타일 배열 공식을 완전히 교체해야합니다.

Excel 동적 배열 가용성

Dynamic Array는 2018 년 Microsoft Ignite Conference에서 출시되었으며 2020 년 1 월에 Office 365 가입자에게 출시되었습니다. 현재 Microsoft 365 구독 및 Excel 2021에서 사용할 수 있습니다.

다음 버전은 동적 배열을 지원합니다.

• Windows 용 Excel 365
• Mac 용 Excel 365
• Excel 2021
• Mac 용 Excel 2021
• iPad 용 탁월합니다
• iPhone 용 탁월합니다
• 안드로이드 태블릿 버전 엑셀
• Excel의 Android 모바일 버전
• 웹에서 탁월합니다

Excel 동적 배열 기능

새로운 기능의 일환으로, 6 개의 새로운 기능이 Excel 365에 도입되어 배열 및 출력 데이터가 셀 범위에 출력 할 수 있습니다. 출력은 항상 동적입니다. 소스 데이터에서 변경 사항이 발생하면 결과가 자동으로 업데이트됩니다. 따라서 그룹의 이름은 동적 배열 함수 입니다.

이러한 새로운 기능은 전통적으로 해결하기 어려운 많은 작업을 쉽게 처리 할 수 ​​있습니다. 예를 들어, 복제를 삭제하고, 고유 값을 추출 및 계산하고, 널 값을 필터링하고, 임의의 정수 및 소수점을 생성하고, 오름차순 또는 내림차순 순서로 정렬 할 수 있습니다.

다음은 각 함수의 기능에 대한 간략한 소개와 심층 자습서에 대한 링크입니다.

• 고유 - 셀 범위에서 고유 한 항목을 추출하십시오.
• 필터 - 정의하는 기준에 따라 필터 데이터.
• 정렬 - 지정된 열로 셀 범위를 정렬합니다.
• Sortby- 다른 영역이나 어레이 씩 셀 범위를 정렬합니다.
• Randarray- 임의의 배열을 생성합니다.
• 시퀀스 - 일련 번호 목록을 생성합니다.
• TextSplit- 지정된 구분 기호에 의해 문자열을 열 또는/및 행으로 분할하십시오.
• TOCOL- 배열 또는 영역을 단일 열로 변환합니다.
• Torow- 영역이나 배열을 단일 행으로 변환합니다.
• Wrapcols- 각 행에 지정된 값 수에 따라 행 또는 열을 2 차원 배열로 변환합니다.
• Wraprows- 각 열에 지정된 값 수에 따라 행 또는 열을 2 차원 배열로 재구성합니다.
• 테이크 - 배열의 시작 또는 끝에서 지정된 수의 연속 행 또는 열을 추출합니다.
• 드롭 - 배열에서 특정 행이나 열을 제거합니다.
• 확장 - 배열을 지정된 수와 열로 확장합니다.
• Choosecols- 배열에서 지정된 열을 반환합니다.
• Chooserows- 배열에서 지정된 행을 추출하십시오.
• Groupby- 하나 이상의 열의 값을 기반으로 한 행별로 데이터를 그룹 및 집계합니다.
• Pivotby- 행 및 열별 데이터를 그룹화하고 관련 값을 집계합니다.

또한, 인기있는 Excel 함수에 대한 두 가지 현대적인 대안이 그룹에 공식적으로 속하지 않지만 동적 배열의 모든 이점을 활용할 수 있습니다.

• XLOOKUP- VLOOKUP, HLOOKUP 및 LOUCUP의보다 강력한 후임자로서 열과 행에서 여러 값을 찾고 반환 할 수 있습니다.
• Xmatch- 매치 함수 함수의 일반적인 후속 기능으로 수직 및 수평 조회를 수행하고 지정된 항목의 상대 위치를 반환합니다.

Excel 동적 배열 공식

최신 버전의 Excel에서, 동적 배열 거동은 깊게 통합되어 모든 기능 에 기본이된다. 요컨대, 여러 값을 반환하는 모든 공식의 경우 Excel은 결과가 출력되는 크기 조정 영역을 자동으로 생성합니다. 이 능력으로 인해 기존 기능은 이제 마법의 역할을 수행 할 수 있습니다!

다음 예제는 동적 배열 공식의 실제 적용과 기존 기능에 대한 동적 배열의 영향을 보여줍니다.

예 1. 새로운 동적 배열 함수

이 예제는 Excel Dynamic Array 함수를 사용하여 솔루션을 얼마나 빠르고 쉽게 달성 할 수 있는지 보여줍니다.

열에서 고유 한 값 목록을 추출하려면 일반적으로 아래와 같이 복잡한 CSE 공식을 사용합니다. Dynamic Excel에서는 기본 형식의 고유 한 공식 만 필요합니다.

=UNIQUE(B2:B10)

빈 셀에 공식을 입력하고 Enter를 누릅니다. Excel은 즉시 목록의 모든 다른 값을 추출하고 공식에 입력 한 셀에서 시작하는 셀 범위 (이 경우 D2)로 출력합니다. 소스 데이터가 변경되면 결과가 자동으로 다시 계산되고 업데이트됩니다.

예제 2. 한 공식에서 여러 동적 배열 함수 결합

하나의 기능으로 작업을 완료 할 수있는 방법이 없으면 여러 기능을 서로 연결할 수 있습니다! 예를 들어, 조건에 따라 데이터를 필터링하고 결과를 알파벳순으로 정렬하려면 다음과 같이 필터 주변의 정렬 기능을 랩핑 할 수 있습니다.

=SORT(FILTER(A2:C13, B2:B13=F1, "No results"))

여기서 A2 : C13은 소스 데이터이고 B2 : B13은 점검 할 값이고 F1은 조건입니다.

예 3. 기존 함수와 함께 새로운 동적 배열 함수 사용

Excel 365에서 구현 된 새로운 컴퓨팅 엔진은 기존 공식을 쉽게 배열로 변환 할 수 있으므로 새롭고 오래된 기능을 함께 그룹화하는 것을 막을 수있는 것은 없습니다.

예를 들어, 지역에 몇 개의 고유 한 값이 있는지 계산하려면 이전 Counta 기능에서 동적 배열 고유 함수를 중첩 할 수 있습니다.

=COUNTA(UNIQUE(B2:B10))

예제 4. 기존 함수는 동적 배열을 지원합니다

이전 버전의 Excel (예 : Excel 2016 또는 Excel 2019)에서 트림 함수에 셀 범위를 제공하는 경우 첫 번째 셀에 대한 단일 결과를 반환합니다.

=TRIM(A2:A6)

Dynamic Excel에서 동일한 공식은 모든 셀을 처리하고 다음과 같이 여러 결과를 반환합니다.

예 5. 여러 값을 반환하는 Vlookup 공식

우리 모두가 알고 있듯이 VLOOKUP 함수는 지정한 열 인덱스를 기반으로 단일 값을 반환하도록 설계되었습니다. 그러나 Excel 365에서는 여러 열에서 매치를 반환 할 열 번호 세트를 제공 할 수 있습니다.

=VLOOKUP(F1, A2:C6, {1,2,3}, FALSE)

예제 6. 단순화 된 전환 공식

이전 버전의 Excel에서는 Transpose 함수의 구문은 오류가 없을 수 없습니다. 워크 시트에서 데이터를 회전 시키려면 원래 열과 행을 계산하고 동일한 수의 빈 셀을 선택하고 방향을 변경하고 (큰 워크 시트에서 당황스러운 작업입니다!) 선택한 영역에 전환 공식을 입력하고 Ctrl Shift Enter를 눌러 올바르게 완료하십시오. 부르다!

Dynamic Excel에서는 출력 영역의 가장 왼쪽 셀에있는 공식을 입력하고 Enter를 누릅니다.

=TRANSPOSE(A1:B6)

끝내십시오!

오버 플로우 영역 - 하나의 공식, 다중 셀

오버 플로우 영역은 동적 배열 공식에 의해 반환 된 값을 포함하는 셀의 영역입니다.

오버 플로우 영역에서 셀을 선택할 때, 파란색 테두리는 그 내부의 모든 것이 왼쪽 상단 셀의 공식에 의해 계산된다는 것을 보여줍니다. 첫 번째 셀에서 공식을 삭제하면 모든 결과가 사라집니다.

오버 플로우 영역은 Excel 사용자가 더 쉽게 할 수있는 훌륭한 기능입니다. 이전에는 CSE 배열 공식을 사용할 때 복사하려는 셀 수를 추측해야했습니다. 이제 첫 번째 셀에서 공식을 입력하고 Excel이 나머지를 처리하게하십시오.

알아채다. 다른 데이터가 오버 플로우 영역을 차단하면 #spill 오류가 발생합니다. 차단 데이터가 제거되면 오류가 사라집니다.

자세한 내용은 Excel 오버플로 영역을 참조하십시오.

오버 플로우 영역 참조 (# 기호)

동적 배열 공식으로 반환 된 전체 오버 플로우 영역을 참조하려면 영역의 왼쪽 상단 모서리에있는 셀 주소 후에 파운드 부호 또는 파운드 부호 (#)를 추가하십시오.

예를 들어, A2의 Randarray 공식에 의해 얼마나 많은 랜덤 숫자가 생성되는지 확인하려면 Counta 기능에 대한 오버 플로우 영역 참조를 제공하십시오.

=COUNTA(A2#)

오버플로 영역에 값을 추가하려면 다음을 사용하십시오.

=SUM(A2#)

힌트:

• 오버플로 영역을 빠르게 참조하려면 마우스를 사용하여 파란색 상자의 모든 셀을 선택하면 Excel이 오버플로 참조를 만듭니다.
• 일반 지역 참조와 달리 오버 플로우 영역 참조는 동적이며 면적 크기에 자동으로 응답합니다. 자세한 내용은 오버플로 지역 운영자를 참조하십시오.

암시 적 교차 및 @ 문자

동적 배열 Excel에서 공식 언어는 또 다른 중요한 변경 사항이 있습니다 .

Microsoft Excel에서 암시 적 교차로는 여러 값을 단일 값으로 줄이는 공식 동작입니다. 구형 엑셀에서 셀은 단일 값 만 포함 할 수 있으므로 기본 동작이며 특수 연산자가 필요하지 않습니다.

Excel의 새 버전에서 모든 공식은 기본적으로 배열 공식으로 취급됩니다. 특정 공식에서 배열 동작을 사용하지 않으려면 암시 ​​적 교차로 연산자를 사용할 수 있습니다. 다시 말해, 공식이 하나의 값 만 반환하려면 @로 함수 이름을 접두사하면 전통적인 Excel에서 비 어울리 공식처럼 실행됩니다.

실제로 어떻게 작동하는지 보려면 아래 스크린 샷을 확인하십시오.

C2에는 결과를 많은 셀로 넘쳐나는 동적 배열 공식이 있습니다.

=UNIQUE(A2:A9)

E2에서 함수는 @ 문자로 접두사를 표시하며, 이는 암시 적 교차로라고합니다. 결과적으로 첫 번째 고유 한 값 만 반환됩니다.

=@UNIQUE(A2:A9)

자세한 내용은 Excel의 암시 적 교차점을 참조하십시오.

Excel 동적 배열의 장점

다이나믹 어레이가 수년에 걸쳐 Excel에서 최고의 향상 중 하나라는 것은 의심의 여지가 없습니다. 새로운 기능과 마찬가지로 장단점이 있습니다. 다행히도 새로운 Excel Dynamic Array 공식의 장점은 압도적입니다!

단순하고 강력합니다

다이나믹 어레이를 사용하면보다 강력한 공식을보다 쉽게 ​​만들 수 있습니다. 몇 가지 예는 다음과 같습니다.

• 고유 한 값 추출 : 전통적인 공식 | 동적 배열 기능
• 고유 한 값과 다른 값을 계산하십시오 : 전통적인 공식 | 동적 배열 기능
• 컬럼을 알파벳순으로 정렬 : 전통적인 공식 | 동적 배열 기능

모든 공식에 대한 기본 지원

Dynamic Excel에서는 어떤 기능을 지원하는 어레이와 어떤 기능이 없는지 걱정할 필요가 없습니다. 공식이 여러 값을 반환 할 수 있으면 기본적으로 그렇게합니다. 이 예에서 볼 수 있듯이 산술 작업 및 기존 기능에도 적용됩니다.

중첩 된 동적 배열 함수

보다 복잡한 작업 솔루션을 해결하려면 새로운 Excel Dynamic Array 함수를 자유롭게 결합하거나 여기 및 여기에 표시된 것처럼 오래된 기능과 함께 사용할 수 있습니다.

상대적이고 절대적인 인용은 그리 중요하지 않습니다

"하나의 공식, 다중 값"접근법으로 인해 기술적으로 공식은 하나의 셀에 있기 때문에 $ 기호로 영역을 잠글 필요가 없습니다. 따라서 대부분의 경우 절대, 상대적 또는 혼합 된 셀 참조 (항상 초보자에 대한 혼란의 원천)를 사용하는 것은 중요하지 않습니다. 동적 배열 공식은 어쨌든 올바른 결과를 생성합니다!

동적 배열의 제한

새로운 동적 배열은 훌륭하지만 새로운 기능과 마찬가지로 몇 가지 메모가 있습니다.

결과는 일반적인 방식으로 정렬 할 수 없습니다

동적 배열 공식으로 반환 된 오버플로 영역은 Excel의 분류 기능을 사용하여 정렬 할 수 없습니다. 그러한 시도는 " 배열의 일부를 변경할 수 없음 "오류가 발생합니다. 결과를 작거나 크거나 큰 것부터 작은 것부터 작은 것부터 정렬 함수로 줄이십시오. 예를 들어, 이것은 동시에 필터링하고 정렬 할 수있는 방법입니다.

오버플로 영역에서 값을 삭제할 수 없습니다

같은 이유로 오버플로 영역에서 값을 삭제하는 것은 불가능합니다. 배열의 일부를 변경할 수 없습니다. 이 동작은 예상되고 논리적입니다. 기존 CSE 배열 공식에도 동일하게 적용됩니다.

Excel 형태로 지원되지 않습니다

이 기능 (또는 오류?)은 예상치 못한 것입니다. 동적 배열 공식은 Excel 테이블에서 작동하지 않으며 일반 영역에서만 작동하지 않습니다. 오버플로 영역을 테이블로 변환하려고하면 Excel이이를 수행합니다. 그러나 #spill 만 볼 수 있습니다! 오류, 결과가 아닙니다.

Excel 전원 쿼리에는 사용할 수 없습니다

동적 배열 공식의 결과는 전원 쿼리에로드 할 수 없습니다. 예를 들어, 전원 쿼리를 사용하여 두 개 이상의 오버 플로우 영역을 함께 병합하려고하면이 작업이 작동하지 않습니다.

동적 배열 및 기존 CSE 배열 공식

동적 배열을 도입하면 두 가지 유형의 Excel을 논의 할 수 있습니다.

1. 동적 Excel은 동적 배열, 기능 및 공식을 완전히 지원합니다. 현재 Excel 365 및 Excel 2021 만.
2. 비 동체 Excel이라고도하는 전통적인 Excel은 Ctrl Shift Enter 배열 공식 만 지원합니다. Excel 2019, Excel 2016, Excel 2013 및 이전입니다.

말할 것도없이, 동적 배열은 모든면에서 CSE 배열 공식보다 우수합니다. 전통적인 배열 공식은 호환성 이유로 보존되지만 지금부터는 새로운 배열 공식을 사용하는 것이 좋습니다.

가장 중요한 차이점은 다음과 같습니다.

• 동적 배열 공식은 셀에 입력되어 일반 Enter 키를 사용하여 수행됩니다. 구식 배열 공식을 완료하려면 Ctrl Shift Enter를 누르야합니다.
• 새로운 배열 공식은 여러 셀로 자동으로 오버플로됩니다. CSE 공식은 여러 결과를 반환하려면 셀 범위에 복사해야합니다.
• 동적 배열 공식의 출력은 소스 영역의 데이터가 변경됨에 따라 자동으로 조정됩니다. 반환 영역이 너무 작 으면 CSE 공식이 출력을 잘립니다. 반환 영역이 너무 커지면 중복 셀에서 오류가 반환됩니다.
• 동적 배열 공식은 단일 셀에서 쉽게 편집 할 수 있습니다. CSE 공식을 수정하려면 전체 영역을 선택하고 편집해야합니다.
• CSE 공식 영역에서 행을 삭제하고 삽입 할 수는 없습니다. 먼저 기존 공식을 먼저 삭제해야합니다. 동적 배열을 사용하여 행을 삽입하거나 삭제하는 것은 문제가되지 않습니다.

뒤로 호환성 : 전통적인 Excel의 동적 배열

이전 Excel에서 동적 배열 공식이 포함 된 통합 문서를 열면 버팀대 {}로 둘러싸인 일반 배열 공식으로 자동 변환됩니다. 새 버전의 Excel에서 워크 시트를 다시 열면 버팀대가 삭제됩니다.

전통적인 Excel에서는 새로운 동적 배열 기능 및 오버 플로우 영역 참조가 _XLFN이 앞서 있어이 기능이 지원되지 않았 음을 나타냅니다. 오버 플로우 영역 참조 기호 (#)는 AnchorArray 함수로 대체됩니다.

예를 들어, 다음은 Excel 2013 에 고유 한 공식이 어떻게 표시되는지입니다.

대부분의 동적 배열 공식 (전부는 아닙니다!)은 변경할 때까지 결과를 전통적인 Excel로 계속 표시합니다. 공식 편집이 즉시 파괴하고 하나 이상의 #Names를 표시합니까? 오류 값.

Excel 동적 배열 공식은 유효하지 않습니다

함수에 따라 잘못된 구문 또는 유효하지 않은 매개 변수를 사용하면 다른 오류가 발생할 수 있습니다. 다음은 동적 배열 공식을 사용할 때 발생할 수있는 가장 일반적인 실수 중 세 가지입니다.

#엎지르다! 오류

#spill은 동적 배열이 여러 결과를 반환 할 때 발생하지만 무언가가 오버플로 영역을 차단합니다! 실수.

이 오류를 고치려면 완전히 비어 있지 않은 오버플로 영역의 셀을 지우거나 삭제합니다. 방해가되는 모든 셀을 빠르게 찾으려면 오류 표시기를 클릭하고 차단 셀 선택을 클릭하십시오.

비어있는 오버플로 영역 외에도이 오류는 다른 이유로 인해 발생할 수 있습니다. 자세한 내용은 다음을 참조하십시오.

• Excel #spill 오류 - 원인 및 솔루션
• vlookup에서 #spill을 수정하는 방법, 색인 매치, sumif! 실수

#ref! 오류

통합 문서 간의 외부 참조에 대한 제한된 지원으로 인해 동적 어레이는 동시에 두 개의 파일을 열어야합니다. 소스 통합 문서가 닫히면 #ref가 표시됩니다! 실수.

#이름? 오류

이전 버전의 Excel에서 동적 배열 기능을 사용하려고하면 #Name이 발생합니까? 실수. 새로운 기능은 Excel 365 및 Excel 2021에서만 사용할 수 있습니다.

이 오류가 지원되는 Excel 버전에서 발생하면 해당 셀의 함수 이름을 다시 확인하십시오. 어쩌면 그것은 잘못 입력되었을 것입니다 :)

이것은 Excel에서 동적 배열을 사용하는 방법입니다. 이 훌륭한 새로운 기능을 즐기시기 바랍니다! 어쨌든, 읽어 주셔서 감사합니다. 다음 주에 우리 블로그에서 뵙기를 바랍니다!

위 내용은 Excel 동적 배열, 기능 및 공식의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!