컴퓨터를 만나는 기쁨

1. 의미 가. 동적 배열 수식 전통적인 배열 수식의 경우는 Ctrl+Shift+Enter키로 배열 함수를 입력했지만, Microsoft 365 버전에서는 Ctrl+Shift+Enter키를 사용하지 않고 엔터 키 입력만으로, 인접 범위에 값을 반환할 수 있는데 이들 함수를 동적 배열 수식(Dynamic Array Formulas)라고 합니다. 또한 인접 범위에 넘치게 값이 반환된다고 해서 유출된 배열 수식(Spilled Array Formulas [Functions])라고도 합니다. 수식은 A1셀에만 있지만, 값은 D3셀까지 채워집니다. 나. 유출된 범위 연산자 위와 같이 수식은 한 셀에만 있지만 여러 셀에 걸쳐 값이 반환되는 경우 # 연산자를 이용해 유출된(Spilled) 범위를 반환받을 수 있습니다...

1. 의미 정렬할 범위를 지정한 다음, Xlookup 함수와 유사하게 정렬 기준 항목과 정렬 순서를 지정해서 정렬하는 것입니다. Sort 함수의 경우는 sort_index(정렬 순번, 정렬 기준 항목)라고 숫자로 지정하는데, SortBy함수는 정렬 기준 항목을 열 전체로 지정하고, 정렬 순서를 순서대로 지정하는 것이 다릅니다. 2. 구문 =SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…) 첫 번째 인수인 array(정렬할 배열 또는 범위)와 두 번째 인수인 by_array1(정렬 기준 1인 배열 또는 범위)만 필수적인 요소이며, 세 번째 인수인 sort_order1(정렬 순서 1), 네 번째 인수인 by_array2(정렬 기준 2인 ..

5. Sort 함수 인수 입력 방법 가. array : 배열 또는 범위 (1) 범위 내 Sort 함수를 입력하면 순환 참조 발생 Sort 함수는 함수이므로 데이터 영역에 작성하면, 다시 말해 A2셀에 =sort(a2:d17)이라고 입력하고 엔터키를 누르면 순환참조 에러가 발생하며, '수식을 다른 셀로 이동하세요'라고 해결방법을 알려줍니다. 확인 버튼을 누릅니다. 그러면 A2셀에 수식이 들어가고, 값이 0으로 표시되는데, 빠른 실행 도구 모음에서 입력 취소 아이콘을 누르거나, 단축키 Ctrl + Z를 누릅니다. (2) 머리글 포함 지정하면 머리글까지 정렬 대상이 됨 F1셀에 =sort(a1:d17)이라고 입력하면 1행도 머리글이 아니라 데이터로 인식돼서 정렬됩니다. 이는 데이터 탭 - 정렬 명령에 있는 '..

1. Sort 함수의 의미 데이터를 정렬해 주는 것인데, 데이터 탭의 정렬은 명령이라면 Sort함수는 데이터를 정렬해 주는 함수입니다. 또한, 정렬 명령은 데이터 범위를 그대로 유지하면서 정렬하는데, Sort함수는 데이터를 기준으로 새로운 범위에 정렬 결과를 반환합니다. 2. Sort 함수의 적용 범위 Microsoft 365용 Excel Mac용 Microsoft 365용 Excel 웹용 Excel Excel 2021 Mac용 Excel 2021에서만 사용 가능한 최신 함수입니다. 3. Sort 함수의 구문 =SORT(array,[sort_index],[sort_order],[by_col]) - array : 정렬한 범위 또는 배열입니다. - [sort_index] : 정렬한 행 또는 열을 나타내는 숫..

아래와 같이 성명과 점수가 있을 때 점수를 내림차순으로 표시하고, 점수에 해당하는 성명을 추출하는 것을 수식을 통해 해 보겠습니다. 1. 정렬 데이터탭에서 정렬 명령을 누른 후 정렬 기준으로 점수를 선택하고, 정렬 순서를 내림차순으로 지정하고 확인 버튼을 누르면 쉽게 구할 수 있습니다. 2. index와 Aggregate 함수 이용하기 먼저 Ctrl + Z키를 눌 정렬하기 이전 상태로 되돌립니다. 가. 필요성 Vlookup함수로 찾기 어려운 일치하는 값을 찾을 때 index와 match함수를 이용하는데, match함수는 일치하는 값 또는 유사한 값 하나만의 위치를 찾아주기 때문에 위와 같이 동일한 값이 여러 개 있을 때는 적용하기 어려운 점이 있습니다. 나. 논리 index 함수의 구문은 INDEX(ar..

라. 조건에 맞는 데이터를 작은 값부터 표시하기 (1) 가장 작은 값이 0으로 표시되는 문제 j2셀의 채우기 핸들을 오른쪽으로 끈 후 K2셀의 Aggregate 함수의 첫 번째 인수를 작은 값부터 표시하도록 15-Small로 바꾸고 엔터키를 누르면, 가장 작은 값이 0이 아닌데 0이 나옵니다. 따라서, 조건에 해당하는 부분을 마우스로 끌어서 선택 후 F9키를 눌러서 계산값을 확인하 조건에 맞지 않을 경우 0이 돼서 그렇습니다. (2) 행정구역명(대)는 맞고 행정구역명(중)이 불일치할 때 문제 Esc키를 눌러 수식을 원래로 돌려놓고, 수식 맨 앞에 1/를 추가하고 엔터키를 누르면 될 줄 알았는데, False일 경우 #DIV/0!로 표시되는데, 첫 번째 #DIV/0! 다음이 0입니다. 왜 그런가 하고 살펴보..

다. 조건에 맞는 데이터를 큰 값부터 표시하기 (1) 방법 1 j1셀에 인구수 내림차순이라고 씁니다. 그리고, j2셀에 Aggregate 함수를 이용해 수식을 작성합니다. Aggregate 함수의 의미와 구문에 대해서는 이 글을 참고 바랍니다. (1) Aggregate 함수의 첫 번째 인수 내림 차순이므로 첫 번째 인수로는 14(Large)를 선택하고, (2) Aggregate 함수의 두 번째 인수 두 번째 인수로는 오류값을 무시하는 2를 선택합니다. (3) Aggregate 함수의 세 번째 인수 세 번째 인수에 조건을 입력하는데 F, G열과 인구수가 있는 E열을 살펴보면 서울특별시인 경우 F열은 '서울특별시'여야 하고, G열은 '공백이면 안되며', 조건에 맞는 'E열을 값'을 가져와야 합니다. 배열 수..

아래와 같이 서울특별시에 여러 개의 구가 있는데 최대 인구수가 어느 구인지, 두 번째로 많은 구는 어딘지 순서대로 5개를 찾아보겠습니다. 서울특별시뿐만 아니라 부산광역시 등 광역시, 경기도 등 도에도 적용해보려고 합니다. 1. 행정구역명(대)의 중복값을 제거해서 고유한 값 추출하기 가. 중복된 항목 제거 F열을 복사해서 H열에 붙여 넣고, 데이터 - 중복된 항목 제거 명령을 누릅니다. 왼쪽 열과 연결되어 있어서 '선택 영역 확장'에 체크되어 있는데, '현재 선택 영역으로 정렬'을 클릭하고 중복된 항목 제거 버튼을 누릅니다. 그러면 항목이 하나뿐이 없어서 '행정구역명(대)에 체크되어 있습니다. 확인 버튼을 누릅니다. 메시지가 좀 이상한데, "총 274개 항목 중 18개의 고윳값이 남아 있다"는 의미입니다...

한 셀에 들어 있는 행정구역명을 공백을 기준으로 2개 열로 나누는 것을 다뤘었는데, 이번에는 비어 있는 셀을 윗셀 값으로 채우는 것에 대해 알아보겠습니다. 위를 보면 왼쪽에 서울특별시가 있고, 산하 구명이 보이는데, 데이터 처리를 위해서는 구명왼쪽인 F열에 서울특별시가 채워져야 합니다. 1. 이동 옵션 - 빈 셀 이용하기 빈 셀만 찾아서 그 위의 셀로 채우기 위해서는 ① 빈 셀이 있는 F4셀부터 F열의 마지막 셀까지 선택해야 하므로 Shift + Ctrl 키를 누른 상태에서 End키를 누릅니다. 그러면 아래와 같이 293행까지 모두 선택됩니다. ② 홈 탭 - 찾기 및 선택 명령 아래 이동 옵션을 클릭합니다. 그러면 이동 옵션 창이 표시되는데, 빈 셀 왼쪽 옵션 버튼에 체크하고 확인 버튼을 누릅니다. ③ ..

이전에 데이터 탭의 텍스트 나누기와 파워 쿼리의 열 분할에 대해 다뤄봤는데, 이번에는 다른 경우의 데이터 분할에 대해 알아보겠습니다. 통계청에서 조회한 행정구역(시군구)별 주민등록세대수 자료인데, 다운로드하여 보니 서울특별시와 종로구 등의 데이터가 열을 달리해야 데이터 다루기가 편한데 같은 열에 있습니다. 따라서, 이를 다른 열로 만드는 것에 대해 알아보겠습니다. 1. 구조 파악서울특별시는 첫째 자리부터 시작하고, 종로구는 위치를 =FIND("종",A4) 수식으로 알아보니 4부터 시작합니다. 2. 분리하는 방법 가. 실패1부터 시작하는 것과 4부터 시작하는 것을 두 개의 열에 나눠서 표시하면 됩니다. F4셀의 수식은 복사할 때 방해가 되므로 F열과 G열을 선택한 후 마우스 오른쪽 버튼을 누른 후 삽입 메..