내 YouTube 채널에서 가장 많이 본 동영상 중 하나는 Microsoft Excel의 Flash Fill에 대한 동영상입니다. 이 도구의 핵심은 소스 데이터를 어떻게든 변환해야 하는 경우 인접 열에 원하는 결과를 입력하기 시작하면 된다는 것입니다. 여러 개의 수동으로 입력한 셀(보통 2-3개로 충분함) 후에 Excel은 필요한 변환의 논리를 "이해"하고 입력한 내용을 자동으로 계속하여 모든 단조로운 작업을 완료합니다.
효율성의 정수. 우리 모두가 아주 좋아하는 마법의 "do it right" 버튼, 맞죠?
사실, 파워 쿼리에는 그러한 도구와 유사한 것이 있습니다. 예제의 열 (예제 칼럼). 사실 이것은 데이터에서 빠르게 학습한 다음 변환할 수 있는 파워 쿼리에 내장된 작은 인공 지능입니다. 실제 작업에서 유용할 수 있는 부분을 이해하기 위해 몇 가지 실제 시나리오에서 해당 기능을 자세히 살펴보겠습니다.
예 1. 텍스트 붙이기/자르기
직원에 대한 데이터가 포함된 Excel에 이러한 "스마트" 테이블이 있다고 가정해 보겠습니다.
버튼을 사용하여 표준 방식으로 파워 쿼리에 로드 테이블/레인지에서 탭 Data (데이터 — 테이블/범위에서).
각 직원의 성 및 이니셜이 있는 열을 추가해야 한다고 가정합니다(첫 직원의 경우 Ivanov SV 등). 이 문제를 해결하려면 다음 두 가지 방법 중 하나를 사용할 수 있습니다.
- 소스 데이터가 있는 열 머리글을 마우스 오른쪽 버튼으로 클릭하고 명령을 선택합니다. 예제에서 열 추가 (예제에서 열 추가);
- 데이터가 있는 하나 이상의 열을 선택하고 탭에서 열 추가 팀을 선택 예제의 열. 여기의 드롭다운 목록에서 전체 또는 선택한 열만 분석해야 하는지 여부를 지정할 수 있습니다.
그러면 모든 것이 간단합니다. 오른쪽에 표시되는 열에 원하는 결과의 예를 입력하기 시작하고 파워 쿼리에 내장된 인공 지능은 변환 논리를 이해하고 자체적으로 계속 진행하려고 합니다.
그건 그렇고, 이 열의 모든 셀에 올바른 옵션을 입력할 수 있습니다. 즉, 반드시 하향식으로 행에 입력할 필요는 없습니다. 또한 나중에 제목 표시줄의 확인란을 사용하여 분석에서 열을 쉽게 추가하거나 제거할 수 있습니다.
창 상단에 있는 수식에 주의하십시오. 이는 스마트 파워 쿼리가 필요한 결과를 얻기 위해 생성하는 것입니다. 그건 그렇고, 이것이 이 도구와 즉시 채우기 엑셀에서. 즉석 채우기는 "블랙박스"처럼 작동합니다. 변환의 논리를 보여주지 않고 단순히 기성품 결과를 제공하고 우리는 이를 당연하게 여깁니다. 여기에서는 모든 것이 투명하며 데이터에서 정확히 무슨 일이 일어나고 있는지 항상 명확하게 이해할 수 있습니다.
파워 쿼리가 "아이디어를 포착"한 경우 버튼을 안전하게 누를 수 있습니다. OK 또는 키보드 단축키 Ctrl 키+엔터 버튼 – Power Query에서 발명한 수식이 있는 사용자 지정 열이 생성됩니다. 그건 그렇고, 나중에 일반 수동으로 생성된 열로 쉽게 편집할 수 있습니다(명령 열 추가 – 사용자 정의 열) 단계 이름 오른쪽에 있는 톱니바퀴 아이콘을 클릭하여:
예 2: 문장과 같은 경우
텍스트가 있는 열 머리글을 마우스 오른쪽 버튼으로 클릭하고 명령을 선택하면 변환 (변환), 레지스터 변경을 담당하는 세 가지 명령을 볼 수 있습니다.
편리하고 멋지지만, 예를 들어 이 목록에서 나는 개인적으로 항상 한 가지 옵션이 더 부족했습니다. 문장에서와 같이 대문자(대문자)가 각 단어의 첫 글자가 아니라 셀의 첫 글자만 되는 경우, 그리고 This가 소문자(소문자)로 표시될 때의 나머지 텍스트입니다.
이 누락된 기능은 인공 지능으로 구현하기 쉽습니다. 예제의 열 – 파워 쿼리가 동일한 정신으로 계속하려면 몇 가지 옵션을 입력하기만 하면 됩니다.
여기에서 수식으로 Power Query는 여러 함수를 사용합니다. 텍스트.상단 и 텍스트.하위, 텍스트를 각각 대문자와 소문자로 변환하고 기능 텍스트.시작 и 텍스트.미드 – Excel 함수 LEFT 및 PSTR의 유사체로 왼쪽과 중간에서 텍스트에서 부분 문자열을 추출할 수 있습니다.
예 3. 단어의 순열
때때로, 수신된 데이터를 처리할 때 셀의 단어를 주어진 순서로 재배열해야 할 필요가 있게 됩니다. 물론 열을 구분 기호로 별도의 단어 열로 나눈 다음 지정된 순서로 다시 붙일 수 있지만(공백 추가를 잊지 마세요), 도구의 도움으로 예제의 열 모든 것이 훨씬 쉬울 것입니다.
예 4: 숫자만
또 다른 매우 중요한 작업은 셀의 내용에서 숫자(숫자)만 추출하는 것입니다. 이전과 마찬가지로 파워 쿼리에 데이터를 로드한 후 탭으로 이동합니다. 열 추가 – 예제의 열 프로그램이 정확히 우리가 얻고자 하는 것을 이해할 수 있도록 두 개의 셀을 수동으로 채우십시오:
빙고!
다시 말하지만, Query가 수식을 올바르게 생성했는지 확인하기 위해 창 상단을 볼 가치가 있습니다. 이 경우에는 함수가 포함되어 있습니다. 텍스트. 고르다, 짐작할 수 있듯이 목록에 따라 소스 텍스트에서 주어진 문자를 추출합니다. 물론 이 목록은 필요한 경우 수식 입력줄에서 쉽게 편집할 수 있습니다.
예 5: 텍스트만
이전 예와 유사하게 텍스트만, 모든 숫자, 구두점 등을 제거하거나 그 반대의 경우도 마찬가지입니다.
이 경우 이미 의미가 반대인 함수인 Text.Remove가 사용되어 주어진 목록에 따라 원래 문자열에서 문자를 제거합니다.
예 6: 영숫자 죽에서 데이터 추출
파워 쿼리는 셀의 영숫자 죽에서 유용한 정보를 추출해야 하는 경우와 같이 더 어려운 경우에도 도움이 됩니다. 예를 들어 은행 거래 명세서의 지불 목적 설명에서 계좌 번호를 가져옵니다.
파워 쿼리 생성 변환 공식은 매우 복잡할 수 있습니다.
읽기 쉽고 이해하기 쉽도록 무료 온라인 서비스를 사용하여 훨씬 더 정상적인 형태로 변환할 수 있습니다. 파워 쿼리 포맷터:
매우 편리한 점 - 제작자에 대한 존경심!
예 7: 날짜 변환
수단 예제의 열 날짜 또는 날짜/시간 열에도 적용할 수 있습니다. 날짜의 첫 번째 숫자를 입력하면 파워 쿼리가 가능한 모든 변환 옵션 목록을 유용하게 표시합니다.
따라서 원래 날짜를 "년-월-일"과 같은 이국적인 형식으로 쉽게 변환할 수 있습니다.
예 8: 분류
도구를 사용한다면 예제의 열 숫자 데이터가 있는 열에 대해서는 다르게 작동합니다. 파워 쿼리에 로드된 직원 테스트 결과(0-100 범위의 조건부 점수)가 있고 다음 조건부 그라데이션을 사용한다고 가정합니다.
- 마스터 – 90점 이상 획득자
- 전문가 – 70에서 90 사이의 점수
- 사용자 – 30~70
- 초심자 – 30점 미만인 자
예제의 열을 목록에 추가하고 이러한 그라데이션을 수동으로 정렬하기 시작하면 곧 파워 쿼리가 아이디어를 선택하고 연산자가 서로 중첩되는 수식이 있는 열을 추가합니다. if 우리가 필요로 하는 것과 매우 유사한 로직이 구현될 것입니다:
다시 말하지만 상황을 끝까지 누를 수는 없지만 클릭하십시오. OK 그런 다음 공식에서 이미 임계값을 수정합니다. 이 방법이 더 빠릅니다.
결론
확실히 도구 예제의 열 "마법의 약"이 아니며 조만간 파워 쿼리가 실패하고 원하는 것을 해결할 수 없을 때 데이터에 비표준 상황 또는 특히 무시된 "집단 농장" 사례가 있을 것입니다. 우리를 위해 올바르게. 그러나 보조 도구로서 매우 좋습니다. 또한 그가 생성한 공식을 공부함으로써 미래에 항상 유용할 M 언어의 기능에 대한 지식을 확장할 수 있습니다.
- 파워 쿼리에서 정규식(RegExp)으로 텍스트 구문 분석
- 파워 쿼리의 퍼지 텍스트 검색
- Microsoft Excel에서 플래시 채우기