Excel의 동적 배열

내용

동적 배열이란

2018년 7월, Microsoft는 Microsoft Excel에 완전히 새로운 도구인 동적 배열과 이를 사용하는 XNUMX개의 새로운 기능을 추가하는 업데이트를 출시했습니다. 이러한 것들은 과장 없이 수식과 함수로 작업하는 모든 일반적인 기술과 문자 그대로 모든 사용자에 대한 관심을 근본적으로 바꿉니다.

본질을 설명하기 위해 간단한 예를 고려하십시오.

도시 월에 대한 데이터가 있는 간단한 테이블이 있다고 가정합니다. 시트 오른쪽에 있는 빈 셀을 선택하고 셀 하나가 아니라 범위에 즉시 연결되는 수식을 입력하면 어떻게 될까요?

모든 이전 버전의 Excel에서 엔터 버튼 첫 번째 셀 B2의 내용만 가져옵니다. 다른 방법은 무엇입니까?

글쎄, 또는 =SUM(B2:C4)와 같은 집계 함수의 일종으로 이 범위를 래핑하고 총계를 얻을 수 있습니다.

고유 값 또는 상위 3개 추출과 같이 기본 합계보다 더 복잡한 연산이 필요한 경우 키보드 단축키를 사용하여 수식을 배열 수식으로 입력해야 합니다. Ctrl 키+변화+엔터 버튼.

이제 모든 것이 다릅니다.

이제 이러한 수식을 입력한 후 다음을 클릭하기만 하면 됩니다. 엔터 버튼 – 결과적으로 우리가 참조한 모든 값 uXNUMXbuXNUMXb를 즉시 가져옵니다.

이것은 마술이 아니라 이제 Microsoft Excel에 있는 새로운 동적 배열입니다. 새로운 세계에 오신 것을 환영합니다 🙂

동적 배열 작업의 기능

기술적으로 전체 동적 배열은 첫 번째 셀 G4에 저장되어 필요한 수의 셀을 데이터로 오른쪽 아래로 채웁니다. 배열에서 다른 셀을 선택하면 수식 입력줄의 링크가 비활성화되어 "하위" 셀 중 하나에 있음을 나타냅니다.

하나 이상의 "하위" 셀을 삭제하려고 해도 아무 결과가 발생하지 않습니다. Excel에서 즉시 다시 계산하여 채웁니다.

동시에 다른 공식에서 이러한 "자식" 셀을 안전하게 참조할 수 있습니다.

배열의 첫 번째 셀(예: G4에서 F8로)을 복사하면 전체 배열(해당 참조)이 일반 공식에서와 같은 방향으로 이동합니다.

배열을 이동해야 하는 경우 이동하는 것으로 충분합니다(마우스 또는 Ctrl 키+X, Ctrl 키+V), 다시 첫 번째 메인 셀 G4만 – 그 이후에는 새로운 장소로 옮겨지고 전체 어레이가 다시 확장됩니다.

시트의 다른 곳에서 생성된 동적 배열을 참조해야 하는 경우 선행 셀 주소 뒤에 특수 문자 #("파운드")를 사용할 수 있습니다.

예를 들어, 이제 생성된 동적 배열을 참조하는 셀에 드롭다운 목록을 쉽게 만들 수 있습니다.

동적 어레이 오류

그러나 배열을 확장할 공간이 충분하지 않거나 경로에 다른 데이터가 이미 사용 중인 셀이 있는 경우 어떻게 됩니까? Excel에서 근본적으로 새로운 유형의 오류를 만나보세요. #옮기다! (#엎지르다!):

항상 그렇듯이 노란색 다이아몬드와 느낌표가 있는 아이콘을 클릭하면 문제의 원인에 대한 자세한 설명을 볼 수 있으며 방해하는 셀을 빠르게 찾을 수 있습니다.

배열이 시트를 벗어나거나 병합된 셀에 닿으면 유사한 오류가 발생합니다. 장애물을 제거하면 모든 것이 즉시 수정됩니다.

동적 배열 및 스마트 테이블

동적 배열이 키보드 단축키로 생성된 "스마트" 테이블을 가리키는 경우 Ctrl 키+T 또는으로 홈 – 테이블 형식 (홈 — 표 형식), 그러면 기본 품질인 자동 크기 조정도 상속됩니다.

새 데이터를 아래쪽이나 오른쪽에 추가하면 스마트 테이블과 동적 범위도 자동으로 늘어납니다.

그러나 한 가지 제한 사항이 있습니다. 스마트 테이블 내부의 포럼에서 동적 범위 참조를 사용할 수 없습니다.

동적 배열 및 기타 Excel 기능

알겠습니다. 이 모든 것이 흥미롭고 재미있습니다. 이전과 같이 원래 범위의 첫 번째 셀을 참조하여 수동으로 수식을 오른쪽 아래로 늘릴 필요가 없습니다. 그리고 그게 다야?

좀 빠지는.

동적 배열은 Excel의 또 다른 도구가 아닙니다. 이제 그들은 계산 엔진인 Microsoft Excel의 핵심(또는 두뇌)에 내장되어 있습니다. 이것은 우리에게 친숙한 다른 Excel 수식과 함수도 이제 동적 배열 작업을 지원한다는 것을 의미합니다. 일어난 변화의 깊이에 대한 아이디어를 제공하기 위해 몇 가지 예를 살펴보겠습니다.

바꾸어 놓다

범위를 바꾸려면(행과 열 교환) Microsoft Excel에는 항상 기본 제공 기능이 있습니다. 트랜스 (바꾸어 놓다). 그러나 이를 사용하려면 먼저 결과의 범위를 올바르게 선택해야 합니다(예: 입력이 5×3의 범위인 경우 3×5를 선택해야 함). 콤비네이션 Ctrl 키+변화+엔터 버튼, 배열 수식 모드에서만 작동할 수 있기 때문입니다.

이제 하나의 셀을 선택하고 동일한 수식을 입력한 다음 일반 엔터 버튼 – 동적 배열은 모든 작업을 자체적으로 수행합니다.

곱셈 구구표

이것은 Excel에서 배열 수식의 이점을 시각화하라는 요청을 받았을 때 제공한 예입니다. 이제 전체 피타고라스 표를 계산하려면 첫 번째 셀 B2에 서서 두 개의 배열(수직 및 수평 세트 1..10)을 곱하는 공식을 입력하고 간단히 클릭하십시오. 엔터 버튼:

접착 및 대소문자 변환

배열은 곱할 수 있을 뿐만 아니라 표준 연산자 &(앰퍼샌드)와 함께 붙일 수도 있습니다. 두 열에서 이름과 성을 추출하고 원본 데이터에서 대소문자를 건너뛰어야 한다고 가정합니다. 전체 배열을 형성하는 하나의 짧은 공식으로 이 작업을 수행한 다음 여기에 함수를 적용합니다. 프로낙 (적절한)레지스터를 정리하려면:

결론 상위 3

상위 XNUMX개 결과를 도출하고 내림차순으로 정렬하려는 많은 숫자가 있다고 가정합니다. 이제 이것은 하나의 공식으로 수행되며 다시 Ctrl 키+변화+엔터 버튼 이전처럼:

결과를 열이 아닌 행에 배치하려면 이 수식의 콜론(줄 구분 기호)을 세미콜론(한 줄 내의 요소 구분 기호)으로 바꾸는 것으로 충분합니다. 영어 버전의 Excel에서 이러한 구분 기호는 각각 세미콜론과 쉼표입니다.

한 번에 여러 열을 추출하는 VLOOKUP

기능 VPR (조회) 이제 하나가 아니라 여러 열에서 한 번에 값을 가져올 수 있습니다. 함수의 세 번째 인수에 배열로 숫자(원하는 순서대로)를 지정하기만 하면 됩니다.

동적 배열을 반환하는 OFFSET 함수

데이터 분석을 위한 가장 흥미롭고 유용한(VLOOKUP 이후) 함수 중 하나는 다음 함수입니다. 처분 (오프셋), 나는 한 번에 내 책의 전체 장과 여기에 기사를 바쳤습니다. 이 함수를 이해하고 마스터하는 데 있어 항상 어려운 점은 결과적으로 데이터 배열(범위)을 반환했지만 Excel은 기본적으로 배열을 사용하는 방법을 아직 몰랐기 때문에 볼 수 없었습니다.

이제 이 문제는 과거입니다. 이제 OFFSET에서 반환된 단일 수식과 동적 배열을 사용하여 정렬된 테이블에서 주어진 제품에 대한 모든 행을 추출할 수 있는 방법을 확인하십시오.

그녀의 주장을 살펴보자.

A1 – 시작 셀(기준점)
ПОИСКПОЗ(F2;A2:A30;0) – 시작 셀에서 아래로 이동 계산 – 처음 발견된 양배추로.
0 – 시작 셀을 기준으로 "창"을 오른쪽으로 이동
СЧЁТЕСЛИ(A2:A30;F2) – 반환된 "창"의 높이 계산 – 양배추가 있는 줄의 수.
4 — 수평으로 "창"의 크기, 즉 출력 4열

동적 배열을 위한 새로운 함수

이전 함수에서 동적 배열 메커니즘을 지원하는 것 외에도 Microsoft Excel에 몇 가지 완전히 새로운 함수가 추가되어 동적 배열 작업을 위해 특별히 개선되었습니다. 특히 다음과 같습니다.

학년 (종류) – 입력 범위를 정렬하고 출력에 동적 배열을 생성합니다.
소르트포 (정렬 기준) – 한 범위를 다른 값으로 정렬할 수 있습니다.
FILTER (필터) – 지정된 조건을 충족하는 소스 범위에서 행 검색
유니크 (독특한) – 범위에서 고유한 값을 추출하거나 중복을 제거합니다.
슬매시브 (랜다레이) - 주어진 크기의 난수 배열 생성
후산 (순서) — 주어진 단계로 일련의 숫자로 배열을 형성합니다.

그들에 대해 자세히 알아보십시오 - 조금 후에. 그들은 사려 깊은 연구를 위해 별도의 기사 (하나가 아닌) 가치가 있습니다 🙂

결론

위에 쓰여진 모든 것을 읽었다면 이미 일어난 변화의 규모를 깨닫고 있다고 생각합니다. 이제 Excel의 많은 작업을 더 쉽고, 더 쉽고, 논리적으로 수행할 수 있습니다. 나는 지금 여기, 이 사이트와 내 책에서 얼마나 많은 기사를 수정해야 하는지 약간 충격을 받았지만 가벼운 마음으로 이 일을 할 준비가 되어 있음을 인정해야 합니다.

결과를 요약하자면, 플러스 동적 배열의 경우 다음을 작성할 수 있습니다.

조합을 잊을 수 있습니다 Ctrl 키+변화+엔터 버튼. Excel은 이제 "일반 수식"과 "배열 수식" 간에 차이가 없으며 동일한 방식으로 처리합니다.
기능에 대해 SUMPRODUCT (합산), 이전에 없이 배열 수식을 입력하는 데 사용되었습니다. Ctrl 키+변화+엔터 버튼 당신은 또한 잊을 수 있습니다 - 이제 충분히 쉽습니다 SUM и 엔터 버튼.
스마트 테이블과 친숙한 함수(SUM, IF, VLOOKUP, SUMIFS 등)도 이제 동적 배열을 완전히 또는 부분적으로 지원합니다.
이전 버전과의 호환성이 있습니다. 이전 버전의 Excel에서 동적 배열이 포함된 통합 문서를 열면 배열 수식(중괄호)으로 바뀌고 "이전 스타일"로 계속 작동합니다.

일부 번호를 찾았습니다. 마이너스:

동적 배열에서 개별 행, 열 또는 셀을 삭제할 수 없습니다. 즉, 단일 엔티티로 존재합니다.
일반적인 방법으로 동적 배열을 정렬할 수 없습니다. 데이터 – 정렬 (데이터 — 정렬). 이제 이를 위한 특별한 기능이 있습니다. 학년 (종류).
다이내믹 레인지는 스마트 테이블로 변환할 수 없습니다(하지만 스마트 테이블을 기반으로 다이내믹 레인지를 만들 수 있음).

물론 이것이 끝이 아니며 Microsoft는 앞으로 이 메커니즘을 계속 개선할 것이라고 확신합니다.

어디에서 다운로드할 수 있습니까?

그리고 마지막으로, 주요 질문 🙂

Microsoft는 2018년 XNUMX월 회의에서 Excel의 동적 배열 미리보기를 처음 발표하고 보여주었습니다. 불을 붙이다. 다음 몇 달 동안 철저한 테스트와 새로운 기능의 실행이 있었습니다. ~~고양이~~ Microsoft 자체 직원과 Office Insiders 서클의 자원 봉사 테스터. 올해 동적 배열을 추가하는 업데이트가 일반 Office 365 구독자에게 점진적으로 배포되기 시작했습니다. 예를 들어 Office 365 Pro Plus(월간 대상) 구독으로 XNUMX월에만 받았습니다.

Excel에 아직 동적 배열이 없지만 실제로 사용하려는 경우 다음 옵션이 있습니다.

Office 365 구독이 있는 경우 이 업데이트가 도착할 때까지 기다리기만 하면 됩니다. 이 작업이 얼마나 빨리 수행되는지는 업데이트가 Office에 전달되는 빈도에 따라 다릅니다(XNUMX년에 한 번, XNUMX개월에 한 번, 한 달에 한 번). 회사 PC가 있는 경우 관리자에게 업데이트를 더 자주 다운로드하도록 설정할 수 있습니다.
Office Insiders 테스트 자원 봉사자의 대열에 합류할 수 있습니다. 그러면 모든 새로운 기능을 가장 먼저 받을 수 있습니다(물론 Excel에서는 버그가 증가할 가능성이 있습니다).
구독이 없지만 박스형 독립 실행형 버전의 Excel이 있는 경우 최소한 2022년에 다음 버전의 Office 및 Excel이 출시될 때까지 기다려야 합니다. 이러한 버전의 사용자는 보안 업데이트와 버그 수정만 받을 수 있으며 모든 새로운 "혜택"은 이제 Office 365 구독자에게만 제공됩니다. 슬프지만 사실입니다 🙂

어쨌든 동적 배열이 Excel에 나타나면 이 기사를 마치면 준비가 된 것입니다 🙂

배열 수식이란 무엇이며 Excel에서 사용하는 방법
OFFSET 기능을 사용한 창(범위) 합산
Excel에서 표를 조옮김하는 3가지 방법