방법 1. 공식
워밍업을 위해 가장 간단한 옵션인 공식으로 시작해 보겠습니다. 입력으로 날짜별로 정렬된 작은 테이블이 있는 경우 별도의 열에서 누계를 계산하려면 기본 공식이 필요합니다.
여기서 주요 기능은 SUM 함수 내에서 범위를 까다로운 것으로 수정하는 것입니다. 범위의 시작에 대한 참조는 절대(달러 기호 포함)로, 끝은 상대(달러 제외)로 설정됩니다. 따라서 수식을 전체 열로 복사하면 확장 범위가 생기고 합계가 계산됩니다.
이 접근 방식의 단점은 분명합니다.
- 테이블은 날짜별로 정렬되어야 합니다.
- 데이터가 있는 새 행을 추가할 때 수식은 수동으로 확장해야 합니다.
방법 2. 피벗 테이블
이 방법은 조금 더 복잡하지만 훨씬 더 즐겁습니다. 그리고 악화시키기 위해 더 심각한 문제를 생각해 보겠습니다. 2000개의 데이터 행으로 구성된 테이블에 날짜 열을 기준으로 정렬하지 않고 반복이 있습니다(즉, 같은 날 여러 번 판매할 수 있음).
원래 테이블을 "스마트"(동적) 키보드 단축키로 변환합니다. Ctrl 키+T 또는 팀 홈 – 테이블 형식 (홈 — 표 형식), 다음 명령을 사용하여 피벗 테이블을 만듭니다. 삽입 – 피벗 테이블 (삽입 - 피벗 테이블). 요약의 행 영역에 날짜를 입력하고 값 영역에 판매된 상품 수를 입력합니다.
아주 오래된 버전의 Excel이 아닌 경우 날짜가 자동으로 연도, 분기 및 월별로 그룹화됩니다. 다른 그룹화가 필요한 경우(또는 전혀 필요하지 않은 경우) 날짜를 마우스 오른쪽 버튼으로 클릭하고 명령을 선택하여 수정할 수 있습니다. 그룹화/그룹 해제 (그룹화/그룹 해제).
기간별 결과 합계와 누적 합계를 별도의 열에서 모두 보려면 필드를 값 영역에 던지는 것이 좋습니다. 판매 다시 필드의 복제본을 얻으려면 - 여기에서 누적 합계 표시를 켭니다. 이렇게 하려면 필드를 마우스 오른쪽 버튼으로 클릭하고 명령을 선택합니다. 추가 계산 – 누적 합계 (값 표시 — 누계):
거기에서 증가하는 총계 옵션을 백분율로 선택할 수도 있으며 다음 창에서 누적할 필드를 선택해야 합니다. 이 경우에는 날짜 필드입니다.
이 접근 방식의 장점:
- 많은 양의 데이터를 빠르게 읽습니다.
- 수식을 수동으로 입력할 필요가 없습니다.
- 원본 데이터를 변경할 때 마우스 오른쪽 버튼이나 데이터 – 모두 새로 고침 명령으로 요약을 업데이트하면 충분합니다.
단점은 이것이 요약이라는 사실에서 비롯됩니다. 즉, 원하는 모든 작업(선 삽입, 수식 작성, 다이어그램 작성 등)을 더 이상 수행할 수 없습니다.
방법 3: 파워 쿼리
명령을 사용하여 소스 데이터가 있는 "스마트" 테이블을 파워 쿼리 쿼리 편집기에 로드해 보겠습니다. 데이터 – 테이블/범위에서 (데이터 — 테이블/범위에서). 그런데 최신 버전의 Excel에서는 이름이 바뀌었습니다. 잎으로 (시트에서):
그런 다음 다음 단계를 수행합니다.
1. 명령을 사용하여 날짜 열을 기준으로 테이블을 오름차순으로 정렬합니다. 오름차순으로 정렬 테이블 헤더의 필터 드롭다운 목록에서
2. 조금 후에 누계를 계산하려면 서수 행 번호가 있는 보조 열이 필요합니다. 명령어로 추가해보자 열 추가 – 인덱스 열 – 1부터 (열 추가 — 인덱스 열 — 1부터).
3. 또한 누계를 계산하려면 열에 대한 참조가 필요합니다. 판매, 요약된 데이터가 있는 곳입니다. 파워 쿼리에서 열은 목록(목록)이라고도 하며 링크를 가져오려면 열 머리글을 마우스 오른쪽 단추로 클릭하고 명령을 선택합니다. 디테일링 (세부사항 표시). 필요한 식은 이전 단계의 이름으로 구성된 수식 입력줄에 나타납니다. #"색인 추가", 테이블과 열 이름을 가져 오는 곳 [매상] 이 표에서 대괄호로 묶은 것:
나중에 사용할 수 있도록 이 표현식을 클립보드에 복사합니다.
4. 불필요한 마지막 단계 삭제 판매 명령을 사용하여 누계를 계산하기 위해 계산된 열을 대신 추가합니다. 열 추가 – 사용자 정의 열 (열 추가 — 사용자 정의 열). 필요한 공식은 다음과 같습니다.
여기서 기능 목록.범위 원본 목록(열 [매상]) 첫 번째부터 요소를 추출합니다(파워 쿼리의 번호 매기기가 0부터 시작하므로 수식에서 이 값은 XNUMX입니다). 검색할 요소의 수는 열에서 가져온 행 번호입니다. [색인]. 따라서 첫 번째 행에 대한 이 함수는 열의 첫 번째 셀 하나만 반환합니다. 판매. 두 번째 줄 - 이미 처음 두 개의 셀, 세 번째 줄 - 처음 세 개 등
그럼 기능은 목록.합 추출 된 값을 합산하고 각 행에 이전 요소의 합계, 즉 누적 합계를 얻습니다.
더 이상 필요하지 않은 인덱스 열을 삭제하고 홈 – 닫기 및 로드 명령을 사용하여 결과를 Excel로 다시 업로드해야 합니다.
문제가 해결되었습니다.
빠르고 격렬한
원칙적으로 이것은 멈출 수 있었지만 연고에 작은 파리가 있습니다. 우리가 만든 요청은 거북이의 속도로 작동합니다. 예를 들어, 가장 약한 PC가 아닌 제 PC에서는 2000행의 테이블이 17초 만에 처리됩니다. 데이터가 더 많다면?
속도를 높이려면 지정된 목록(목록)을 인수로 RAM에 로드하는 특수 List.Buffer 함수를 사용하여 버퍼링을 사용할 수 있습니다. 그러면 향후 액세스 속도가 크게 빨라집니다. 우리의 경우 2000개 행 테이블의 각 행에서 누계를 계산할 때 파워 쿼리가 액세스해야 하는 #”Added index”[Sold] 목록을 버퍼링하는 것이 좋습니다.
이렇게 하려면 기본 탭의 파워 쿼리 편집기에서 고급 편집기 단추(홈 – 고급 편집기)를 클릭하여 파워 쿼리에 내장된 M 언어로 쿼리의 소스 코드를 엽니다.
그런 다음 거기에 변수가있는 줄을 추가하십시오. 나의 목록, 그 값은 버퍼링 함수에 의해 반환되며 다음 단계에서 목록에 대한 호출을 다음 변수로 바꿉니다.
이러한 변경을 수행하면 쿼리가 훨씬 빨라지고 단 2000초 만에 0.3행 테이블을 처리할 수 있습니다!
다른 건 맞죠? 🙂
- 파레토 차트(80/20) 및 Excel에서 작성하는 방법
- 텍스트의 키워드 검색 및 파워 쿼리의 쿼리 버퍼링