목록을 정렬해야 하는 경우 서비스에 여러 가지 방법이 있으며 가장 쉬운 방법은 탭이나 메뉴에 있는 정렬 버튼입니다. Data (데이터 — 정렬). 그러나 목록의 정렬을 자동으로 수행해야 하는 상황(예: 수식)이 있습니다. 예를 들어, 드롭다운 목록에 대한 데이터를 생성할 때, 차트에 대한 데이터를 계산할 때 등에 필요할 수 있습니다. 즉석에서 수식으로 목록을 정렬하는 방법은 무엇입니까?
방법 1. 수치 데이터
목록에 숫자 정보만 포함된 경우 함수를 사용하여 쉽게 정렬할 수 있습니다. 가장 작은 (작은) и LINE (열):
함수 가장 작은 (작은) 배열(A열)에서 행에서 n번째로 작은 요소를 꺼냅니다. 저것들. SMALL(A:A;1)은 열에서 가장 작은 숫자이고 SMALL(A:A;2)는 두 번째로 작은 숫자입니다.
함수 LINE (열) ROW(A1)=1, ROW(A2)=2 등의 지정된 셀에 대한 행 번호를 반환합니다. 이 경우 단순히 n=1,2,3… 우리의 정렬된 목록. 동일한 성공으로 추가 열을 만들고 숫자 시퀀스 1,2,3 …으로 수동으로 채우고 ROW 함수 대신 참조할 수 있었습니다.
방법 2. 텍스트 목록 및 일반 수식
목록에 숫자가 아니라 텍스트가 포함된 경우 SMALL 기능은 더 이상 작동하지 않으므로 약간 더 긴 다른 경로로 이동해야 합니다.
먼저 앞으로 정렬된 목록에 있는 각 이름의 일련 번호가 함수를 사용하여 계산되는 공식이 있는 서비스 열을 추가해 보겠습니다. 카운티 (카운티프):
영어 버전에서는 다음과 같습니다.
=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)
첫 번째 항은 현재보다 적은 셀의 개수를 세는 함수입니다. 두 번째는 이름이 두 번 이상 나오는 경우를 대비한 안전망입니다. 그러면 그들은 같지 않지만 계속해서 숫자가 증가합니다.
이제 수신된 번호를 오름차순으로 순차적으로 정렬해야 합니다. 이를 위해 기능을 사용할 수 있습니다 가장 작은 (작은) 첫 번째 방법에서:
글쎄, 마지막으로 목록에서 번호로 이름을 꺼내는 것만 남아 있습니다. 이렇게 하려면 다음 공식을 사용할 수 있습니다.
함수 더 많이 노출 (성냥) B열에서 원하는 일련 번호(1, 2, 3 등)를 검색하고 실제로 이 번호가 있는 줄의 번호를 반환합니다. 기능 INDEX (인덱스) A열에서 이 줄 번호의 이름을 가져옵니다.
방법 3: 배열 수식
이 방법은 사실 방법-2와 동일한 배치 알고리즘이지만 배열 수식으로 구현됩니다. 수식을 단순화하기 위해 C1:C10 셀 범위에 이름이 지정되었습니다. 명부 (셀 선택, 누름 Ctrl + F3 및 버튼 만들기):
셀 E1에서 수식을 복사합니다.
=INDEX(목록; MATCH(SMALL(COUNTIF(목록; "<"&목록); ROW(1:1)); COUNTIF(목록; "<"&목록); 0))
또는 영어 버전:
=INDEX(리스트, MATCH(SMALL(COUNTIF(리스트, «<"&리스트), ROW(1:1)), COUNTIF(리스트, "<"&리스트), 0))
그리고 밀어 Ctrl + Shift + Enter배열 수식으로 입력합니다. 그런 다음 결과 공식을 목록의 전체 길이로 복사할 수 있습니다.
공식이 고정된 범위가 아니라 목록에 새 요소를 추가할 때 조정할 수 있도록 하려면 전략을 약간 변경해야 합니다.
먼저 목록 범위를 동적으로 설정해야 합니다. 이렇게 하려면 생성할 때 고정 범위 C3:C10이 아니라 숫자에 관계없이 사용 가능한 모든 값을 참조하는 특수 수식을 지정해야 합니다. 딸깍 하는 소리 Alt + F3 탭을 열거나 수식 – 이름 관리자 (수식 — 이름 관리자), 새 이름을 만들고 필드에 (링크) (참고) 다음 수식을 입력하십시오(정렬할 데이터 범위가 셀 C1에서 시작한다고 가정).
=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)
=오프셋(C1,0,0,쇼츠(C1:C1000),1)
둘째, 위의 배열 공식은 앞으로 추가 데이터가 입력될 것으로 예상하면서 여백을 줄여야 합니다. 이 경우 배열 수식은 아직 채워지지 않은 셀에 #NUMBER 오류를 표시하기 시작합니다. 가로채기 위해 다음 기능을 사용할 수 있습니다. 오류, 배열 수식 "주변에" 추가해야 합니다.
=오류(INDEX(목록; MATCH(SMALL(COUNTIF(목록; "<"&목록); ROW(1:1)); COUNTIF(목록; "<"&목록); 0));»»)
=IFERROR(NDEX(목록, MATCH(SMALL(COUNTIF(목록, «<"&목록), ROW(1:1)), COUNTIF(목록, "<"&목록), 0));"")
#NUMBER 오류를 포착하고 대신 void(빈 따옴표)를 출력합니다.
:
- 색상별로 범위 정렬
- 배열 수식이란 무엇이며 왜 필요한가요?
- 새로운 Office 365의 SORT 정렬 및 동적 배열