이름 데이터 세트로 작업하는 경우 정렬은 자주 수행해야 하는 일반적인 작업 중 하나입니다.
Excel은 이름의 첫 번째 문자를 사용하여 정렬하는 경우 전체 이름을 기준으로 데이터를 사전순으로 쉽게 정렬할 수 있습니다.
그러나 원하는 경우Excel에서 성을 기준으로 데이터 정렬지휘하다정렬수행하는 방법?
그렇게 간단하지는 않지만 여전히 수행할 수 있습니다(이름 데이터가 구조화되는 방식에 따라 많이 달라짐).
어떤 방법을 사용하든 전체 이름에서 성을 어떻게든 추출하여 별도의 열에 넣어야 합니다.그런 다음 이 열을 사용하여 성을 기준으로 알파벳순으로 데이터를 정렬할 수 있습니다.
이 Excel 자습서에서는 성을 기준으로 이름이 있는 열을 정렬하는 방법을 보여 드리겠습니다.
시작하겠습니다!
내용
찾기 및 바꾸기를 사용하여 성을 기준으로 추출 및 정렬
성을 기준으로 정렬하는 첫 번째 단계는 성을 별도의 열로 가져오는 것입니다.
성 앞의 모든 항목을 공백으로 바꾸면 이 작업을 수행할 수 있으므로 성만 남게 됩니다.
아래와 같은 데이터 세트가 있고 해당 데이터를 성을 알파벳순으로 정렬하려고 한다고 가정합니다.
성을 기준으로 정렬하는 단계는 다음과 같습니다.
- 제목이 포함된 데이터세트를 선택합니다(이 경우 A1:A10).
- 인접한 열에 복사합니다(인접한 열이 비어 있지 않으면 새 열을 삽입한 다음 이름을 복사합니다)
- 복사한 열 머리글의 이름을 바꿉니다.이 예에서는 "Last Name"으로 이름을 지정하겠습니다.
- 복사된 모든 이름 선택(제목은 선택하지 않음)
- Control 키를 누른 상태에서 H 키를 누릅니다.찾기 및 바꾸기 대화 상자가 열립니다.
- 찾을 내용 필드에 *(별표 다음에 공백 문자)를 입력합니다.
- 바꿀 내용 필드를 비워 둡니다.
- 모두 바꾸기를 클릭합니다.이렇게 하면 모든 이름이 한 번에 바뀌고 성만 남게 됩니다.
위의 단계는 성을 유지하고 그 이전의 모든 것을 삭제합니다.이것은 Mr. 또는 Ms와 같은 중간 이름이나 접두사가 있는 경우에도 작동합니다.
인접한 열에 성이 있으면 성을 기준으로 사전순으로 데이터 세트(전체 이름 포함)를 쉽게 정렬할 수 있습니다.
성을 기준으로 정렬하는 단계는 다음과 같습니다.
- 제목이 있는 전체 데이터세트를 선택합니다(전체 이름 및 추출된 성을 포함).이름으로 정렬할 다른 열을 포함할 수도 있습니다.
- 데이터 탭을 클릭합니다.
- 클릭하여 정렬
- 정렬 대화 상자에서 내 데이터에 머리글이 있음이 선택되어 있는지 확인합니다.
- "정렬 기준" 옵션에서 성만 있는 열 이름을 선택합니다.
- 정렬 기준에서 셀 값을 선택합니다.
- "주문" 옵션에서 "A to Z"를 선택합니다.
- 확인을 클릭하십시오
위의 단계는 성을 기준으로 선택한 전체 데이터 세트를 정렬합니다.
완료되면 성이 있는 열을 삭제할 수 있습니다.
프로 팁: 언제든지 원래 데이터로 돌아가야 한다고 생각되면 해당 데이터 세트를 정렬 해제할 수 있는 방법이 필요합니다.이를 위해 인접한 열(왼쪽 또는 오른쪽)에는 정렬하기 전에 시퀀스 번호가 있습니다.이제 원본 데이터를 다시 가져와야 하는 경우 숫자를 기준으로 정렬하여 가져올 수 있습니다.
수식을 사용하여 성을 기준으로 추출 및 알파벳순 정렬
위에 표시된 방법(찾기 및 바꾸기 사용)이 모든 성을 가져오고 이를 기반으로 정렬하는 것을 선호하지만 제한 사항 중 하나는 결과 데이터가 정적이라는 것입니다.
즉, 목록에 이름을 더 추가하면 성을 얻기 위해 동일한 프로세스를 다시 거쳐야 합니다.
이것이 원하는 것이 아닌 경우 수식 방법을 사용하여 성을 기준으로 데이터를 정렬할 수 있습니다.
아래에 표시된 데이터 세트가 있다고 가정합니다.
전체 이름에서 성을 추출하는 공식은 다음과 같습니다.
= RIGHT (A2, LEN (A2) -FIND ( "", A2))
위의 공식은 전체 이름이 있는 패턴에 의존합니다(이 예에서는 성과 이름만).패턴은 이름과 성 사이에 공백 문자가 있다는 것입니다.
FIND 함수는 공백 문자의 위치를 얻는 데 사용됩니다.그런 다음 이 값을 이름의 총 길이에서 빼서 성의 총 문자 수를 구합니다.
그런 다음 RIGHT 함수에서 해당 값을 사용하여 성을 가져옵니다.
성 열이 있으면 이 데이터를 정렬할 수 있습니다(이는 첫 번째 방법에 자세히 설명되어 있음).
위의 공식은 성과 이름만 있는 경우에 작동합니다.
그러나 중간 이름도 있다면 어떨까요?또는 이름 앞에 인사말이 있을 수 있습니다(예: Mr. 또는 Ms.).
이 경우 다음 공식을 사용해야 합니다.
=RIGHT(A2,LEN(A2)-FIND("@",SUBSTITUTE(A2," ","@",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))
위 수식은 마지막 공백 문자의 위치를 찾은 다음 이를 사용하여 성을 추출합니다.
모든 경우에 두 번째 공식을 사용하는 것이 좋습니다. 더 간단하고 모든 경우를 처리합니다(성이 이름 끝에 있는 한).
참고: 이 두 수식은 각 이름 요소 사이에 공백 문자가 하나만 있다는 조건에 의존합니다.이 수식은 이중 공백 또는 선행/후행 공백이 있는 경우 잘못된 결과를 제공합니다.이 경우 TRIM 함수를 사용하여 선행, 후행 및 이중 공백을 먼저 제거한 다음 위의 공식을 사용하는 것이 가장 좋습니다.
이것은 복잡한 접근 방식처럼 보일 수 있지만 공식을 사용하면 결과를 동적으로 만든다는 이점이 있습니다.목록에 더 많은 이름을 추가하는 경우 수식을 복사하기만 하면 성이 표시됩니다.
텍스트를 열로 사용
Text to Columns는 Excel에서 셀을 분할하는 또 다른 쉬운 방법입니다.
구분 기호(예: 쉼표 또는 공백)를 지정하고 이를 사용하여 셀의 내용을 분할할 수 있습니다.요소를 별도의 열로 분할한 후 성이 있는 열을 사용하여 데이터를 알파벳순으로 정렬할 수 있습니다.
다음과 같은 데이터세트가 있다고 가정합니다.
Text to Column을 사용하여 성을 기준으로 정렬하는 단계는 다음과 같습니다.
- 이름이 있는 열 선택(헤더 제외)
- 데이터 탭을 클릭합니다.
- 데이터 도구 그룹에서 텍스트를 열로 옵션을 클릭합니다.그러면 Text to Columns 마법사가 열립니다.
- 텍스트를 열로 변환 마법사의 1단계에서 구분 기호를 선택하고 다음을 클릭합니다.
- 2단계에서 구분 기호로 "공백"을 선택하고(선택한 경우 다른 항목은 선택 취소) 다음 버튼을 클릭합니다.
- 3단계에서 데이터 미리 보기에서 이름 열을 선택한 다음 "열을 가져오지 않음(건너뛰기)" 옵션을 선택합니다.이렇게 하면 이름이 결과에 포함되지 않고 성만 표시됩니다.
- 또한 3단계에서 대상 셀을 원본 데이터에 인접한 셀로 변경합니다.이렇게 하면 성을 하나만 얻을 수 있고 원래 이름 데이터가 완전해집니다.
- 마침을 클릭
결과가 나오면 성을 기준으로 정렬할 수 있습니다.
쉼표를 구분 기호로 사용하는 경우 텍스트 열을 사용하여 이름과 성을 구분할 수도 있습니다.
글리터로 채우기
성을 얻는 또 다른 빠른 방법은 Flash Fill 기능을 사용하는 것입니다.
Excel 2013에 도입된 Flash Fill은 패턴을 인식하여 데이터를 조작하는 데 도움이 됩니다.이렇게 하려면 예상한 결과에 대해 Flash Fill을 여러 번 표시해야 합니다.
패턴을 인식하면 나머지는 빠르게 처리합니다.
다음과 같은 이름 데이터 세트가 있다고 가정합니다.
다음은 Flash Fill을 사용하여 성을 가져오고 정렬하는 데 사용하는 단계입니다.
- B2 셀에 "Maury"라는 텍스트를 입력합니다.이것은 세포에서 기대할 수 있는 것입니다.
- 다음 셀로 이동하여 인접한 셀(이 경우 Elliot)에 이름의 성을 입력합니다.
- 두 개의 셀을 선택
- 선택 항목의 오른쪽 아래 부분에 커서를 올려 놓습니다.커서가 더하기 아이콘으로 변경되었음을 알 수 있습니다.
- 두 번 클릭합니다(또는 클릭하고 아래로 드래그).이것은 당신에게 셀에 어떤 결과를 줄 것입니다 (원하는 것과는 달리)
- 자동 완성 옵션 아이콘을 클릭합니다.
- 빠른 채우기 클릭
이렇게 하면 모든 셀의 성이 될 수 있는 결과를 얻을 수 있습니다.
경우에 따라 Flash Fill이 작동하지 않을 수 있기 때문일 수 있습니다.인식 모드에 따라 다르기 때문에 항상 이렇게 하는 것은 아닙니다.또는 때로는 해독하는 패턴이 올바른 패턴이 아닐 수도 있습니다.
이 경우 하나 이상의 셀에 예상 결과를 입력한 다음 4-7단계를 수행해야 합니다.
열에 성이 모두 있으면 해당 성을 기준으로 데이터를 정렬할 수 있습니다.
따라서 네 가지 방법으로 성을 기준으로 데이터를 정렬할 수 있습니다.가장 좋은 방법은 찾기 및 바꾸기 기술을 사용하는 것이지만 결과를 동적으로 만들려면 수식 방법을 사용하는 것이 좋습니다.
이 튜토리얼이 유용했기를 바랍니다.
