2개의 열로 구성된 테이블을 수식을 사용하여 정렬해 보겠습니다. 테이블의 열 중 하나를 기준으로 정렬하겠습니다(두 가지 문제를 해결합니다: 숫자로 테이블 정렬 및 텍스트 열로 정렬). 새 데이터가 소스 테이블에 추가되면 정렬된 테이블이 동적으로 변경되도록 정렬 수식을 설정해 보겠습니다. 이렇게 하면 사용자 개입 없이 항상 테이블을 정렬할 수 있습니다. 또한 2단계 정렬을 수행합니다. 먼저 숫자를 기준으로 한 다음 (반복되는 숫자의 경우) 텍스트 열을 기준으로 합니다.

2개의 열로 구성된 테이블이 있다고 가정합니다. 한 열은 텍스트입니다. 과일 목록; 두 번째는 숫자입니다 판매량(예제 파일 참조)

문제 1 (숫자 열을 기준으로 테이블 정렬)

숫자 열의 내용(판매량 기준)을 기준으로 테이블 행을 정렬해야 합니다. 사용자가 테이블의 행을 지속적으로 채우는 것으로 가정하므로 추가된 값을 고려하여 수식을 작성해야 합니다.

명확성을 위해 열의 값은 판매량()로 강조 표시되었습니다. 반복되는 값도 노란색으로 강조 표시됩니다.

메모: 단일 열(목록)을 정렬하는 문제가 기사 및에서 해결되었습니다.

솔루션1

숫자 열에 값이 포함되지 않으면 문제를 쉽게 해결할 수 있습니다.

• LARGE() 함수를 사용하여 숫자 열을 정렬합니다(문서 참조).
• VLOOKUP() 함수 또는 여러 함수 인덱스()+매치()해당 숫자 값으로 텍스트 열에서 값을 선택합니다.

그러나 실제 문제에서는 숫자 열에 반복이 포함될 수 있으며, VLOOKUP() 함수는 반복의 경우 항상 맨 위에서 첫 번째 값만 선택하므로(문서 참조) 이 접근 방식은 적합하지 않습니다( 과일이 잘못 표시됩니다.)

따라서 정렬 메커니즘을 다르게 구현해야 합니다.

INDEX(매출;
라운드(재매트(가장 큰(
--(COUNTIF(매출;"<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
ROW()-ROW($E$6));1)*1000;0)
)

이 수식은 열을 정렬합니다. 판매량(동적 범위 매상) 내림차순. 소스 테이블의 간격은 허용되지 않습니다. 소스 테이블의 행 수는 1000개 미만이어야 합니다.

수식을 더 자세히 살펴보겠습니다.

• 공식 COUNTIF(매출;"<"&Продажи) 배열(4:5:0:2:7:1:3:5)을 반환합니다. 이는 숫자 64(셀에서)를 의미합니다. B7 원래 테이블, 즉 범위의 첫 번째 숫자 매상) 동일한 범위에서 4개 이상의 값; 번호 74 (셀에서 B8 원래 테이블, 즉 범위의 두 번째 숫자 매상) 동일한 범위에서 5개 이상의 값; 다음 숫자 23은 가장 작습니다(누구보다 크지 않음).
• 이제 위의 정수 배열을 분수 부분이 있는 숫자 배열로 바꾸겠습니다. 여기서 분수 부분에는 배열에 있는 숫자의 위치 번호가 포함됩니다. ). 이는 다음 표현식으로 구현됩니다. &","&REPEAT("0";3-LENGTH(ROW(판매액)-ROW($E$6)))&ROW(판매액)-ROW($E$6))소스 테이블의 행 수가 1000개 이하로 제한되는 것은 수식의 이 부분입니다(위 참조). 원하는 경우 쉽게 변경할 수 있지만 이는 의미가 없습니다(아래 계산 속도 섹션 참조).
• LARGE() 함수는 위의 배열을 정렬합니다.
• ROD() 함수는 0.005와 같이 위치 번호/1000인 숫자의 소수 부분을 반환합니다.
• ROUND() 함수는 1000을 곱한 후 정수로 반올림하고 위치 번호를 반환합니다. 이제 모든 위치 번호는 열 번호에 해당합니다. 판매량,내림차순으로 정렬되었습니다.
• INDEX() 함수는 위치 번호가 주어지면 그에 해당하는 번호를 반환합니다.

열에 값을 표시하기 위해 유사한 수식을 작성할 수 있습니다. 과일=INDEX(과일,ROUND(...))

예제 파일에서는 계산 속도 고려 사항(아래 참조)으로 인해 수식 부분의 동일한 유형, 즉 ROUND() 함수 내부의 모든 내용은 별도의 열에 배치됩니다. 제이 . 따라서 정렬된 테이블의 최종 수식은 다음과 같습니다. =INDEX(과일,J7)그리고 =INDEX(매출,J7)

또한 배열 수식에서 LARGE() 함수를 SMALL()로 변경하면 오름차순으로 정렬됩니다.

명확성을 위해 열의 값은 판매량(로 강조 표시됨 홈/ 스타일/ 조건부 서식/ 히스토그램). 보시다시피 정렬이 작동합니다.

테스트

이제 원본 테이블에 새 행을 추가해 보겠습니다. 동적으로 정렬된 테이블에서는 적절한 정렬이 필요합니다.

1. 셀로 A15 소스 테이블에 단어를 입력하세요 당근;
2. 셀로 B15 입력하다 판매량당근 = 25;
3. 해당 항목에 값을 입력한 후 디 그리고 이자형 내림차순으로 정렬된 테이블이 자동으로 표시됩니다.
4. 정렬된 표에서는 마지막에서 두 번째부터 새 행이 표시됩니다.

수식 계산 속도

성능 측면에서 "평균"컴퓨터에서 이러한 쌍을 다시 계산합니다. 배열 수식, 100줄에 위치하여 사실상 눈에 띄지 않습니다. 300개 행이 있는 테이블의 경우 재계산 시간이 2~3초 정도 걸려 불편하다. 또는 자동 시트 재계산을 비활성화해야 합니다( 수식/계산/계산 옵션) 키를 주기적으로 누릅니다. F9, 배열 수식 사용을 포기하고 해당 수식이 있는 열로 바꾸거나 표준 접근 방식을 사용하기 위해 동적 정렬을 완전히 포기합니다(다음 섹션 참조).

테이블 정렬에 대한 대체 접근 방식

표준 필터를 사용하여 소스 테이블의 행을 정렬해 보겠습니다(소스 테이블의 헤더를 선택하고 CTRL+SHIFT+L). 드롭다운 목록에서 필요한 정렬을 선택합니다.

우리는 우리와 동일한 버전의 테이블을 얻게 되지만 테이블에 새 값을 추가할 때 필터를 다시 적용해야 합니다.

정렬 도구( 데이터/정렬 및 필터/정렬). 이렇게 하려면 헤더를 제외하고 소스 테이블의 모든 값을 선택하고 정렬 도구를 호출하고 정렬할 열과 정렬 옵션을 선택해야 합니다.

우리는 우리와 동일한 버전의 테이블을 얻게 되지만, 새 값을 추가할 때 필터도 다시 적용해야 합니다.

이전 문제와 마찬가지로 정렬을 수행하는 열에 반복(과일 이름이 반복됨)이 있다고 가정해 보겠습니다.

테이블을 정렬하려면 2개의 서비스 열(D 및 E)을 생성해야 합니다.

=개수($B$7:$B$14;"<"&$B$7:$B$14)+1

이 공식은 텍스트 값(목록의 다른 값에 대한 값의 위치)과 유사합니다. 알파벳이 낮은 텍스트 값은 "순위"가 더 높습니다. 예를 들어 Apples 값은 최대 "순위" 7(반복 포함)에 해당합니다.

E열에 일반적인 수식을 입력합니다.

=카운티프($D$6:D6,D7)+D7

이 공식은 텍스트 값의 반복을 고려하여 "순위"를 조정합니다. 이제 사과의 다양한 값은 다양한 "순위"(7 및 8)에 해당합니다. 이를 통해 정렬된 값 목록을 표시할 수 있습니다. 이렇게 하려면 다음 수식(G열)을 사용하세요.

=INDEX($B$7:$B$14,MATCH(ROW()-ROW($G$6),$E$7:$E$14,0))

비슷한 수식으로 해당 판매량(H열)이 표시됩니다.

문제 2.1(2단계 정렬)

이제 원래 테이블을 Sales Volume별로 다시 정렬해 보겠습니다. 그러나 이제 반복되는 값(A열에 3개의 값 74가 있음)의 경우 해당 값을 알파벳 순서로 표시합니다.

이를 위해 문제 1.1과 문제 2의 결과를 사용하겠습니다.

Task2 시트의 예제 파일에 대한 세부 정보입니다.

최대 3개의 필드를 기준으로 목록을 사전순, 숫자순 또는 시간순으로 정렬할 수 있습니다. 각 열에 대해 오름차순 및 내림차순 정렬 순서를 지정할 수 있습니다.

오름차순 정렬 순서:

숫자는 가장 작은 음수부터 가장 큰 양수 순으로 정렬됩니다.

날짜 및 시간 값은 가장 빠른 값부터 최신 값까지 정렬됩니다.

텍스트는 알파벳순으로 정렬됩니다. 이 경우 텍스트로 지정된 숫자값이 먼저 위치하게 됩니다.

부울 값을 오름차순으로 정렬하면 FALSE 값이 먼저 표시되고 그 다음에 TRUE 값이 표시됩니다.

오류 값은 발생한 순서대로 정렬됩니다(정렬 관점에서 보면 모두 동일함).

정렬된 목록의 끝에 빈 셀이 나타납니다.

내림차순 정렬 순서:

내림차순 정렬 순서의 경우 Excel에서는 위에서 설명한 역순으로 레코드를 정렬합니다. 목록 끝에는 빈 셀만 표시됩니다.

사용자 정의 정렬 순서:

오름차순 및 내림차순 정렬 순서 대신 지정된 사용자 정의 목록에 따라 결정되는 사용자 정의 정렬 순서를 사용할 수도 있습니다.

목록 정렬

목록을 정렬하려면 목록 내부에 셀 포인터를 놓고 데이터 – 정렬 명령을 실행하세요.

Excel에서는 자동으로 목록을 선택하고 정렬 옵션을 지정할 수 있는 정렬 범위 대화 상자를 표시합니다.

최대 3개의 목록 필드별로 정렬을 설정하고 각 필드에 대해 서로 다른 정렬 순서를 지정할 수 있습니다.

이름이 포함된 셀의 형식이 데이터가 포함된 셀의 형식과 다른 경우 Excel에서는 필드 이름을 자동으로 인식합니다.

범위 정렬 대화 상자.

프로그램에서 수행한 범위 선택이 완전히 정확하지 않은 경우 대화 상자 하단의 스위치를 원하는 위치로 설정합니다("레이블(범위의 첫 번째 행)" 또는 "시트 열 지정"으로 필드 식별).

정렬 대화 상자에서 지정한 범위 및 정렬 옵션은 저장되어 다음에 대화 상자를 열 때 표시됩니다.

행 대신 범위 열을 정렬하도록 선택할 수도 있습니다. 이렇게 하려면 "정렬 범위" 대화 상자에서 "옵션" 버튼을 클릭하고 "정렬 옵션" 대화 상자의 "정렬" 그룹에서 스위치를 "범위 열" 위치로 설정합니다.

26. Excel에서 데이터 필터링.

목록의 데이터를 필터링하면 지정된 조건을 충족하는 레코드만 표시할 수 있습니다.

자동필터

자동 필터를 적용하면 원하는 필드 값을 선택하거나 사용자 정의 기준을 설정할 수 있는 드롭다운 목록으로 필드 이름이 보완됩니다.

자동 필터 삽입

1. 목록 안에 셀 포인터를 놓습니다.

2. 데이터 - 필터 하위 메뉴에서 "자동 필터" 명령을 선택합니다. 필드 이름 옆에 화살표 버튼이 표시되며, 이를 클릭하여 목록을 열 수 있습니다.

3. 값을 필터(선택 기준)로 사용하려는 필드의 목록을 엽니다. 목록에는 선택한 필드의 셀 값이 표시됩니다.

4. 목록에서 원하는 항목을 선택하세요. 지정된 필터와 일치하는 레코드만 화면에 표시됩니다.

5. 필요한 경우 다른 필드 목록에서 필요한 요소를 선택합니다. 지정된 모든 필터링 조건과 일치하는 레코드만 화면에 표시됩니다(개별 필드의 조건은 논리 연산 "AND"를 사용하여 결합됨).

필터링 조건이 지정된 필드에 대한 자동 필터를 여는 버튼은 파란색으로 표시됩니다.

자동 필터 명령을 실행하기 전에 하나 이상의 열을 선택한 경우 드롭다운 목록은 해당 필드에만 추가됩니다.

모든 목록 항목을 화면에 다시 표시하려면 데이터 - 필터 하위 메뉴에서 "모두 표시" 명령을 실행합니다.

해당 필드에 대한 자동 필터 목록에서 "모두" 항목을 선택하여 개별 필드에 대한 필터링 기준을 제거할 수 있습니다.

자동 필터 기능을 비활성화하려면(드롭다운 목록 제거) 데이터 – 필터 – 자동 필터 명령을 다시 선택하십시오.

맞춤 자동 필터 적용

사용자 정의 자동 필터를 사용하면 논리 연산자를 사용하여 개별 레코드 선택 조건을 연결할 수 있습니다.

데이터 - 필터 - 자동 필터 명령을 선택하여 목록에 자동 필터를 삽입합니다.

원하는 필드에 대한 자동 필터 목록을 열고 그 안에 있는 요소(조건)를 선택합니다.

열리는 "사용자 정의 자동 필터" 대화 상자(그림 6.3.27.)에서 첫 번째 기준을 지정합니다.

첫 번째 기준과 두 번째 기준을 결합하는 논리 연산자를 선택합니다.

"사용자 정의 자동 필터" 대화 상자.

사용자 정의 자동 필터의 개별 필드에 대해 하나 또는 두 개의 기준을 설정할 수 있습니다. 후자의 경우 논리 연산자 “AND” 또는 “OR”과 결합할 수 있습니다.

두 번째 기준을 설정합니다.

“확인” 버튼을 클릭하세요. Excel은 지정한 기준에 따라 레코드를 필터링합니다.

고급 필터

목록 데이터 필터링을 위한 복잡한 조건을 설정하기 위해 Excel은 사용자에게 도움이 되는 소위 고급 필터를 제공합니다.

기준 범위

기준은 워크시트의 여유 공간에 설정할 수 있습니다. 기준 범위에서는 두 가지 유형의 기준을 입력하고 결합할 수 있습니다.

단순 기준: 프로그램은 필드의 내용을 지정된 기준과 비교합니다(자동 필터를 사용하는 것과 유사).

계산된 기준: 이 경우 나열되지 않은 수식에서 반환되는 값을 지정할 수도 있습니다.

단순 기준을 지정할 때는 먼저 기준 범위에 필드명을 지정해야 하며, 기준을 지정하는 필드명만 입력할 수 있습니다.

필드 이름 줄 바로 아래 줄은 기준을 지정하는 곳입니다. Excel에서는 기준을 지정할 때 대소문자를 구분하지 않습니다. 기준을 지정할 때 와일드카드를 사용할 수 있나요? 그리고 *.

한 줄에 지정된 모든 기준은 동시에 실행되어야 합니다(논리 연산자 “AND”에 해당). "OR" 연산자를 사용하여 기준 연결을 지정하려면 다른 줄에 기준을 지정하세요.

고급 필터 적용

기준 범위를 생성한 후에는 고급 필터를 실행하고 목록 데이터를 필터링할 수 있습니다.

1. 목록 안에 셀 포인터를 놓습니다. 이 경우 Excel은 자동으로 목록 범위를 인식하고 대화 상자에 해당 범위에 대한 링크를 제공합니다.

2. 데이터 – 필터 – 고급 필터 명령을 실행합니다. "조건 범위" 필드에 입력 커서를 놓고 워크시트에서 해당 범위를 강조 표시합니다.

3. “확인” 버튼을 클릭하여 대화상자를 닫습니다. 이제 화면에는 지정된 기준을 충족하는 기록이 표시됩니다.

워크시트당 하나의 고급 필터만 적용할 수 있습니다.

고급 필터 적용 결과 중복된 레코드가 표시되지 않도록 하려면 고급 필터 대화 상자에서 고유 레코드만 확인란을 선택합니다.

필터링 후 워크시트의 모든 목록 항목 표시를 설정하려면 데이터 – 필터 – 모두 표시 명령을 실행합니다.

테이블에 있는 정보를 보고 분석할 때 사용자에게 편리하고 편안한 조건을 만들기 위해 MS Excel 프로그램은 상당히 다양한 데이터 정렬 가능성을 제공합니다. Excel에서 대규모 데이터베이스의 레코드를 정렬하는 중...

사용자가 관심 있는 정보를 좀 더 쉽게 찾을 수 있습니다.

정렬할 때 모든 데이터베이스 행은 우리가 선택한 알고리즘에 따라 위치를 변경하지만 물론 변경되지는 않습니다.

Excel의 세 가지 주요 정렬 알고리즘.

1. 숫자값은 "가장 작은 것에서 가장 큰 것"이라는 원칙에 따라 정렬되거나 그 반대의 경우도 마찬가지입니다.

2. 다음을 포함하는 셀이 있는 열 텍스트값은 정방향 또는 역방향으로 알파벳순으로 정렬됩니다.

3. 다음을 포함하는 셀이 있는 열 날짜, "가장 오래된 것부터 최신 것까지" 또는 그 반대로 정렬됩니다.

Excel 2007 및 최신 버전에서는 셀 채우기 색상, 셀 글꼴 색상, 조건부 서식 아이콘 세트 등 형식 지정 기준에 따라 정렬을 추가로 수행할 수 있습니다.

“2번 현장의 금속 구조물 생산” 기사에서 작성된 DB2 데이터베이스로 계속 작업해 보겠습니다. ».

고려 중인 훈련 데이터베이스는 6개의 필드(열)와 10개의 레코드(행)로만 구성됩니다. 실제 데이터베이스에는 일반적으로 12개 이상의 필드와 수천 개의 레코드가 포함되어 있습니다. 이러한 테이블에서 필요한 정보를 찾는 것은 쉽지 않습니다! 우리가 후속 행동을 살펴보아야 하는 것은 그러한 이해의 프리즘을 통해서입니다.

추가 작업은 다음과 같이 구성됩니다. 이 시리즈의 이 기사와 다음 기사에서는 프로그램의 다양한 기능을 사용할 것입니다. MS 뛰어나다 , 생산 현장 관리자의 실제 활동에서 발생하는 다양한 질문과 작업에 대한 즉각적인 답변을 공식화합니다. 즉, 실제 생산 문제를 해결해 드립니다!

데이터베이스 유지 관리 - 정보 수집 - 그 자체로는 아무것도 제공하지 않으며 시간이 좀 걸립니다. 하지만 이 정보를 분석한 결과 다양한 질문에 대한 정확한 답변을 제공할 수 있으므로 실제 생활에서 데이터베이스 개체의 관리 효율성이 크게 향상될 수 있습니다!

생산 과정에서 데이터베이스를 적절하게 구축하고 신중하게 유지 관리하면 분석 결과를 사용하여 동일한 영역, 장비, 동일한 기술 및 기술 장비 구성을 사용하여 생산량을 두 배, 때로는 세 배로 늘릴 수 있습니다.

가장 간단한 정렬.

Excel에서 가장 간단한 정렬은 "표준" 도구 모음에 있는 "오름차순 정렬" 및 "내림차순 정렬" 버튼을 사용하여 수행됩니다. (아래 그림에서 이러한 버튼은 빨간색 타원으로 둘러싸여 있습니다.)

작업 #1:

결정: 제조된 제품 중 가장 무거운 제품은 무엇이며 그 질량은 얼마입니까? 이 제품은 언제 만들어졌나요?

1. MS Excel에서 파일을 엽니다.

2. "1개 무게, t"라는 열 제목이 있는 셀 E7을 클릭하여 활성화합니다(관심 있는 열의 모든 셀을 활성화할 수 있습니다).

3. “표준” 툴바에서 “내림차순 정렬” 버튼을 클릭하세요.

4. 우리는 데이터베이스의 맨 윗줄(8번째 줄)에 제시된 질문에 대한 답을 읽었습니다. 데이터베이스에서 가장 무거운 제품은 주문 번호 2의 Beam 045입니다. Beam 045는 2014년 4월 23일부터 4월 25일까지 제조되었습니다(Excel 라인 번호 8-10의 항목 참조).

5. 동일한 "표준" 도구 모음에서 "취소" 버튼을 클릭하여 Excel에서 정렬하기 전에(필요한 경우) 데이터베이스를 양식으로 되돌릴 수 있습니다. 또는 데이터베이스의 날짜 열에 오름차순 정렬을 적용할 수 있습니다.

Excel에서 여러 열을 기준으로 정렬합니다.

이러한 방식으로 정렬하는 것은 2개 또는 3개 열에 걸쳐 순차적으로 수행될 수 있습니다.

작업 #2:

주문별로 그룹화된 데이터베이스 기록과 주문 내의 제품 이름을 알파벳 순으로 표시합니다.

1. 마우스를 클릭하여 데이터베이스 셀을 활성화합니다(예: C11 셀).

2. 메인 메뉴 버튼 "데이터"를 클릭하고 "정렬..."을 선택합니다.

3. 팝업되는 “정렬 범위” 창에서 왼쪽 스크린샷과 같이 드롭다운 목록에서 값을 선택하고 “확인”을 클릭합니다.

4. 작업 번호 2가 완료되었습니다. 기록은 먼저 주문 번호별로 정렬되고, 두 번째로 각 주문 내에서 제품 이름을 기준으로 알파벳순으로 정렬됩니다.

결과.

이 시리즈의 두 번째 기사에서는 정보 저장 및 데이터 관리에 대해 Excel에서 데이터베이스 레코드를 정렬하는 기본 기능을 살펴보았습니다. Excel의 매우 간단한 메커니즘은 독자 여러분 대부분에게 확실히 친숙합니다. 잘 알려진 서비스에 대한 진부한 이야기로 여러분의 시간을 너무 많이 빼앗지 않았으면 좋겠습니다. 하지만... 단지로 가는 길은 단순한 것에서부터 시작됩니다!

솔직히 말해서 실제로는 정렬에 의존할 필요가 거의 없습니다. 왜? 이 질문에 대한 답은 빠르고 효율적인 데이터 처리를 위한 더욱 발전되고 강력한 Excel 메커니즘을 포함하여 다른 것에 대해 설명할 다음 시리즈 기사에 나와 있습니다.

나는 간청한다 존경하는 작가의 작품 구독하다 기사 공지를 위해 각 글의 마지막 창이나 페이지 상단의 창에서 확인하세요!

독자 여러분, 댓글에 리뷰와 의견을 적어주세요.

피벗 테이블의 행과 열 영역에 있는 데이터 정렬은 기본적으로 오름차순(그림 1a) 또는 사용자 정의 정렬 목록을 사용하여 수행됩니다. 이는 항상 사용자에게 적합한 것은 아닙니다. 예를 들어, 소득이 가장 높은 고객을 목록 상단에 표시하려는 경우(그림 1b). 피벗 테이블이 오름차순(내림차순)으로 정렬된 경우 필드별로 정렬 순서를 제어하는 ​​규칙을 만들어야 합니다. 또한 이 필드에 대한 규칙은 피벗 테이블에 새 필드를 추가한 후에도 적용됩니다(그림 1c).

쌀. 1. 분야별 정렬 고객: (a) 기본적으로 – A부터 Z까지; (b) 소득이 감소하는 순서로; (c) 필드별로 정렬 순서 고객필드를 추가할 때 변경되지 않았습니다. 부문

형식의 메모를 다운로드하거나 형식의 예를 다운로드하십시오.

고객을 소득 내림차순으로 정렬

피벗 테이블 행을 소득 내림차순으로 정렬하려면 열에서 아무 셀이나 선택하세요. 분야별 수입금액, 예를 들어 E4(제목은 제외)를 클릭하고 아이콘을 클릭하세요. 일본탭에 위치 데이터(그림 2). 이 정렬은 표준 정렬과 유사하지만 이는 단지 표면적인 유사성일 뿐입니다. 피벗 테이블을 정렬하면 Excel에서는 피벗 테이블을 추가로 변경한 후에도 계속 작동하는 규칙을 만듭니다.

G:I 열(그림 1c)에 있는 피벗 테이블의 예를 사용하면 새 외부 행 필드를 추가한 후 어떤 일이 발생하는지 확인할 수 있습니다. 부문. 피벗 테이블은 각 부문 내 소득의 내림차순으로 데이터를 계속 정렬합니다. 예를 들어, 해당 분야에서 생산 General Motors가 750,163달러의 매출로 1위를 차지했습니다. Ford는 622,794달러의 매출로 그 뒤를 바짝 쫓고 있습니다. 필드를 삭제해도 고객피벗 테이블에서 추가 설정을 수행하고 이 필드를 다시 반환하지만 열 영역에서는 Excel이 고객을 소득 내림차순으로 정렬하는 것을 기억합니다.

따라서 G:I 열(그림 1c)에 있는 요약 테이블에서 섹터도 소득 내림차순으로 정렬되므로 다음 세 가지 방법 중 하나로 이동할 수 있습니다.

• G4 셀을 선택하고 마우스 오른쪽 버튼을 클릭한 후 선택하세요. 모든 축소필드고객과 관련된 모든 요소를 ​​숨깁니다. 화면에 하나의 섹터만 표시되면 셀 I4를 선택하고 탭에서 YA 아이콘을 클릭합니다. 데이터내림차순으로 정렬을 수행합니다. 그러면 필드에 대한 정렬 규칙이 생성됩니다. 부문. G4 셀을 다시 선택하고 마우스 오른쪽 버튼을 클릭하여 선택합니다. 모두 펼치기필드.
• 필드를 일시적으로 제거 고객피벗 테이블에서 소득의 내림차순으로 테이블을 정렬한 다음(그림 2에 설명된 방법 사용) 필드를 다시 반환합니다. 고객.
• 팀의 능력을 활용하라 추가의옵션정렬(저는 이 정확한 방법을 사용합니다). 명령을 호출하려면: (a) 셀 G4를 선택하고 마우스 오른쪽 버튼을 클릭한 후 선택합니다. 정렬 → 추가의옵션정렬(그림 3) 또는 (b) 필드에서 삼각형 아이콘을 클릭합니다. 부문을 선택한 다음 추가의옵션정렬(그림 4). 두 경우 모두 창이 열립니다 정렬(그림 5). 스위치를 위치로 설정 내림차순그리고 라인을 선택하세요 분야별 수입금액.

쌀. 3. 명령을 호출 추가의옵션정렬마우스 오른쪽 버튼으로 클릭

쌀. 4. 명령을 호출 추가의옵션정렬메뉴 사용하기 정렬 및 필터필드 부문

쌀. 5. 창에서 매개변수 설정하기 부문

대화 상자의 왼쪽 하단에 정렬고급 버튼이 있습니다... 이 버튼을 클릭하면 화면에 대화 상자가 나타납니다. . 이 창에서 다음을 수행할 수 있습니다. (a) 첫 번째 키를 기준으로 정렬하는 데 사용할 사용자 정의 목록을 정의합니다(자세한 내용은 아래 참조). (b) 열 대신 총계기본 정렬 열로 다른 열을 선택합니다.

예를 들어, 그림 1에 표시된 피벗 테이블의 경우 6, 총 수입을 기준으로 정렬하는 것이 아니라 한 가지 유형의 상품 판매로 인한 수입을 기준으로 정렬을 설정할 수 있습니다. 장치(고객은 F열이 아닌 C열을 기준으로 정렬됩니다.)

쌀. 6. 추가 매개변수를 사용하면 총 수입이 아닌 제품 판매 수입을 기준으로 고객을 정렬할 수 있습니다. 장치

다음과 같이 정렬하려면:

1. A4 셀에 있는 고객 목록을 확장합니다.
2. 추가 정렬 옵션 옵션을 선택합니다.
3. 대화 상자에서 정렬(고객)버튼을 클릭하세요 추가적으로…
4. 대화 상자에서 추가 정렬 옵션(고객)섹션을 선택하세요 정렬 순서그리고 스위치를 설정해 강조 표시된 열의 값.
5. 링크 상자를 클릭한 다음 C5 셀을 선택합니다. 값 셀 중 하나를 클릭해야 합니다. 장치, 헤더에 있기 때문에 장치 C4 셀은 클릭할 수 없습니다.
6. 매개변수 설정을 완료하려면 확인을 두 번 클릭합니다.

놀라지 마십시오. 이 단계별 알고리즘에 대한 설명은 교육 목적으로 제공됩니다. Excel 2013부터 피벗 테이블 데이터 정렬이 훨씬 쉬워졌습니다. 이제 YA 및 AYA 버튼이 탭에 있습니다. 데이터지능형 정렬 알고리즘을 사용합니다. 이러한 버튼을 사용하여 정렬을 시도할 때 프로그램은 정렬 버튼을 누르기 전에 강조 표시된 셀을 기반으로 사용자의 의도를 예측하려고 시도합니다(그림 7).

• A1, C1, D1, E1, F1, F2, A30, F30 – 사용할 수 없음
• A2:A29 – A열의 고객 이름을 알파벳순으로 정렬합니다.
• В1, В2, С2, D2, E2 – 2행의 상품 이름을 알파벳순으로 표시합니다.
• B30, C30, D30, E30 – 30행에 소득 금액을 내림차순(오름차순)으로 정렬합니다.
• 오름차순(내림차순) 판매 B3:B29 – 모듈, C3:C29 – 장치, D3:D29 – 부품, E3:E29 – 의약품, F3:F29 – 합계.

수동 정렬

대화 상자에서 참고하세요. 정렬(그림 5 참조) 데이터 정렬 규칙을 수동으로 정의할 수 있습니다. 그러나 피벗 테이블을 수동으로 정렬하는 것도 다소 특이한 다른 방식으로 수행됩니다. 그림의 피벗 테이블 보고서에서 그림 8a는 알파벳 순서로 정렬된 제품 범주의 순서를 보여줍니다. 부품, 모듈, 준비그리고 장치. 카테고리별 제품 판매량을 참고하세요. 세부 사항, 최고는 아닙니다. 그리고 이 카테고리를 먼저 표시할 가치가 거의 없습니다. E4 셀에 마우스 포인터를 놓고 다음 단어를 입력하세요. 세부 사항. 아무 키나 누르시면 됩니다 입력하다 Excel에서 열 이동 결정을 확인하는 방법 세부 사항테이블의 마지막 열로 이동합니다. 해당 상품 카테고리와 관련된 모든 수치 값은 B열에서 E열로 이동됩니다. 다른 상품 카테고리와 관련된 값은 왼쪽으로 이동됩니다. 이 동작은 비논리적으로 보이며 Excel 피벗 테이블에서만 일반적입니다. 일반 Excel 데이터 세트는 이런 방식으로 다시 정렬할 수 없습니다. 그림에서. 그림 8b는 새 열 머리글을 셀 E4로 이동한 후의 피벗 테이블을 보여줍니다.

쌀. 8. 수동 정렬: (a) 제품 범주는 알파벳순으로 정렬됩니다. (b) 부품 범주는 마지막에 배치됩니다.

마우스 매니아는 원하는 열 제목(또는 개별 행)을 끌어서 놓기만 하면 됩니다. 열 머리글 영역을 클릭하고 십자형 화살표로 바뀔 때까지 선택한 셀 범위의 테두리 위에 마우스 포인터를 올려 놓습니다. 셀을 선택한 위치로 드래그하기 시작합니다. 굵은 선과 세리프 형태의 포인터가 나타납니다. 마우스 버튼을 놓으면 숫자 값이 즉시 새 열로 이동됩니다. 수동 정렬을 사용하는 경우 데이터 소스에 추가된 제품은 목록 끝에 추가되므로 주의하시기 바랍니다. 이는 Excel이 새 영역을 정확히 추가할 위치를 알지 못하기 때문입니다.

사용자 정의 목록에 따라 데이터 정렬

필드가 표시되는 순서를 사용자 정의하는 문제에 대한 또 다른 해결책은 사용자 정의 목록을 만드는 것입니다. 이러한 목록을 사용하여 앞으로 생성되는 피벗 테이블이 정렬됩니다. 기본적으로 Excel에는 요일, 월, 요일 및 월의 약어 이름 등 네 가지 사용자 지정 목록이 포함되어 있습니다. 이 프로그램은 요일 이름을 Mon에서 시작하여 Sun으로 끝나는 자연스러운 순서로 정렬합니다(알파벳순 아님).

자신만의 정렬 목록을 만들려면 다음 단계를 따르세요.

1. 워크시트의 데이터 없는 영역에 생성 중인 사용자 지정 목록과 일치하는 순서대로 제품 카테고리 이름을 입력합니다. 각 셀에 이름을 하나씩 입력하고 이름을 한 열에 배치합니다(그림 9).
2. 제품 범주 이름의 결과 목록(셀 A10:A13)을 선택합니다.
3. 리본 탭 선택 파일왼쪽 창에 표시된 탐색 모음 하단에서 버튼을 클릭합니다. 옵션대화 상자를 열려면 엑셀 옵션.
4. 카테고리를 선택하세요 추가적으로, 섹션으로 이동 흔하다그리고 버튼을 클릭하세요 목록 편집.
5. 대화 상자에서 기울기미리 선택된 제목 목록이 포함된 범위의 주소가 필드에 표시됩니다. 셀에서 목록 가져오기(그림 10). 버튼을 클릭하세요 수입지정된 데이터를 기반으로 새로운 제품 카테고리 목록을 생성합니다. 영역 하단에 새 목록이 추가됩니다. 기울기.
6. 확인을 클릭하여 대화 상자를 닫습니다. 기울기. 확인을 다시 클릭하여 대화 상자를 닫습니다. 엑셀 옵션.

쌀. 10. 창문 기울기

새로 생성된 목록은 프로그램 설정에 저장되며 향후 Excel 세션에서 사용할 수 있습니다. 이제 앞으로 생성되는 모든 피벗 테이블은 목록에 지정된 순서에 따라 제품 필드별로 자동 정렬됩니다. 그림에서. 그림 11은 생성된 목록에 따라 정렬된 새로운 피벗 테이블(커스텀 제품 목록 추가 후 생성됨)을 보여줍니다.

새 맞춤 목록에 따라 이전에 생성된 피벗 테이블을 정렬하려면 다음 단계를 따르세요.

1. 필드 목록 확장 제품그리고 옵션을 선택하세요 추가 정렬 옵션.
2. 대화 상자에서 정렬(제품)선택 버튼 필드별 오름차순(A에서 Z까지)을 클릭하고 드롭다운 목록에서 제품.
3. 버튼을 클릭하세요 추가적으로…
4. 대화 상자에서 추가 정렬 옵션(제품)체크박스를 선택 해제하세요 자동 정렬.
5. 목록 확장 첫 번째 키로 정렬제품 카테고리 이름이 포함된 목록을 선택합니다(그림 12).
6. 확인을 두 번 클릭합니다.

이 메모는 Bill Jelen, Michael Alexander의 책을 기반으로 작성되었습니다. . 4장.

Excel에서 데이터 정렬은 표 형식 데이터를 원하는 순서로 정렬하는 것입니다(예: 오름차순(가장 작은 것부터 큰 것) 또는 내림차순(가장 큰 것에서 가장 작은 것)). 숫자 및 텍스트 값, 날짜 및 시간 값, 형식이 정렬됩니다. 열과 행을 기준으로 데이터 정렬이 가능합니다. 정렬하기 전에 숨겨진 행과 열을 표시해야 합니다.

정렬할 데이터 유형 및 정렬 순서

Excel에서 숫자 값 정렬

숫자 값의 오름차순 정렬은 값이 가장 작은 것에서 가장 큰 것(최소값에서 최대값)으로 정렬되는 값 배열입니다.

따라서 숫자값을 내림차순으로 정렬하는 것은 가장 큰 값에서 가장 작은 값(최대값에서 최소값)으로 값을 배열하는 것입니다.

Excel에서 텍스트 값 정렬

"A에서 Z로 정렬" - 데이터를 오름차순으로 정렬합니다.

"Z에서 A로 정렬" - 데이터를 내림차순으로 정렬합니다.

텍스트 값을 알파벳순으로 정렬하려면 이러한 값을 서로 비교합니다. 아시다시피, 텍스트를 저장할 때 컴퓨터는 각 문자에 문자 코드라고 하는 고유한 번호가 있는 체계를 사용합니다.어떤 텍스트 값이 더 크고 더 작은지를 결정하기 위해 비교되는 것은 이러한 코드입니다.

텍스트 값에는 알파벳, 숫자, 특수 문자가 포함될 수 있습니다. 이 경우 숫자는 숫자 형식과 텍스트 형식 모두로 저장할 수 있습니다. 숫자 형식으로 저장된 숫자는 텍스트 형식으로 저장된 숫자보다 작습니다. 텍스트 값을 올바르게 정렬하려면 모든 데이터를 텍스트 형식으로 저장해야 합니다. 또한 다른 응용 프로그램의 텍스트 데이터를 셀에 삽입하면 데이터 앞에 공백이 포함될 수 있습니다. 정렬을 시작하기 전에 정렬 중인 데이터에서 선행 공백(또는 기타 인쇄할 수 없는 문자)을 제거해야 합니다. 그렇지 않으면 정렬이 올바르게 수행되지 않습니다.

대소문자를 구분하여 텍스트 데이터를 정렬할 수 있습니다. 이렇게 하려면 정렬 옵션에서 "대소문자 고려" 상자를 선택해야 합니다.

일반적으로 대문자는 소문자보다 숫자가 낮습니다.

날짜 및 시간 값 정렬

"오래된 것부터 최신으로 정렬"은 날짜 및 시간 값을 가장 오래된 값부터 최신 값으로 정렬하는 것입니다.

"새 항목부터 이전 항목으로 정렬"은 날짜 및 시간 값을 최신 값부터 가장 빠른 값으로 정렬하는 것입니다.

정렬 형식

Microsoft Excel 2007 이상에서는 서식을 기준으로 정렬하는 기능을 제공합니다. 이 정렬 방법은 셀 채우기 색상, 글꼴 색상 또는 아이콘 집합을 사용하여 셀 범위의 서식을 지정할 때 사용됩니다. Excel의 채우기 및 글꼴 색상에는 자체 코드가 있으며 형식을 정렬할 때 사용되는 코드입니다.

사용자 정의 목록으로 정렬

표 형식 데이터는 월 목록, 요일 목록, 기업의 구조적 구분 목록, 직원 번호 목록 등과 같은 사용자 정의 목록으로 정렬할 수 있습니다. Excel에는 자신만의 데이터 정렬 목록을 만들 수 있는 기능이 있습니다. 이 정렬 순서를 사용하면 정렬할 데이터와 목록 값이 일치해야 합니다.

정렬 옵션

열을 기준으로 정렬

모든 Excel 버전에서는 기본적으로 열 기준 정렬이 설정되어 있습니다. 즉, 선택한 열의 셀 값이 원하는 순서로 정렬되고 셀 위치에 따라 범위의 행이 교체됩니다. 정렬된 열에서열별로 표를 정렬하려면 표의 셀에 선택 표시를 설정하고 최소에서 최대로 또는 최대에서 최소로 정렬을 상징하는 아이콘 중 하나를 클릭하세요. 테이블은 선택 표시자가 포함된 열을 기준으로 정렬됩니다.

문자열로 정렬

프로그램 설정에서 열 기준 정렬을 행 기준 정렬로 변경할 수 있습니다. 행별 정렬을 설정하면 선택한 행의 셀 값이 지정된 순서대로 정렬되고, 정렬된 행의 셀 위치에 따라 열이 교체됩니다.

다단계 정렬

따라서 열을 기준으로 정렬하면 행이 바뀌고, 데이터가 행을 기준으로 정렬되면 열이 바뀌게 됩니다.

Excel을 사용하면 하나의 열이나 하나의 행뿐만 아니라 다양한 정렬 수준을 만들어 데이터를 정렬할 수 있습니다. 예를 들어 Excel 2007에는 64개의 정렬 수준이 추가, 삭제, 복사 및 교체될 수 있습니다.

무엇보다도 정렬 시 대소문자를 고려하거나 무시할 수 있습니다.

Excel에서 데이터 정렬을 위한 추가 기능

Microsoft Excel은 다양한 유형의 값을 정렬하기 위한 상당히 큰 표준 도구 세트를 사용자에게 제공하지만 표준 도구를 사용하여 해결하려면 불편하거나 시간이 많이 걸리는 작업(예: 각 행/열 정렬)이 있습니다. 행/열 내에서만 정렬이 이루어지며 인접한 셀에는 영향을 주지 않는 방식입니다.