생산 달력, 즉 모든 공식 근무일과 공휴일이 그에 따라 표시되는 날짜 목록은 Microsoft Excel 사용자에게 절대적으로 필요한 것입니다. 실제로는 그것 없이는 할 수 없습니다.
- 회계 계산에서 (급여, 근속 기간, 휴가 ...)
- 물류 - 주말과 공휴일을 고려하여 정확한 배송 시간 결정
- 프로젝트 관리에서 - 근무일과 휴무일을 고려하여 정확한 용어 추정을 위해
- 다음과 같은 기능의 사용 근무일 (근무일) or 순수한 일꾼 (네트워크일), 인수로 휴일 목록이 필요하기 때문에
- Power Pivot 및 Power BI에서 시간 인텔리전스 기능(TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR 등)을 사용할 때
- … etc. etc. – 많은 예.
1C나 SAP와 같은 기업 ERP 시스템에 생산 캘린더가 내장되어 있어 업무를 하는 사람들에게 더 쉽습니다. 그러나 Excel 사용자는 어떻습니까?
물론 이러한 달력을 수동으로 유지할 수 있습니다. 그러나 그런 다음에는 우리 정부가 고안한 모든 주말, 이동 및 휴무일을 신중하게 입력하여 적어도 2020년에 한 번(또는 "jolly" XNUMX에서와 같이 더 자주) 업데이트해야 합니다. 그리고 매년 이 절차를 반복합니다. 지루함.
조금 미친듯이 Excel에서 "영구적인" 공장 달력을 만드는 것은 어떻습니까? 스스로 업데이트하고 인터넷에서 데이터를 가져와 계산에 나중에 사용할 수 있도록 항상 최신 휴무일 목록을 생성하는 것입니까? 유혹?
실제로 이렇게 하는 것은 전혀 어렵지 않습니다.
데이터 소스
주요 질문은 데이터를 어디에서 얻을 수 있습니까? 적절한 출처를 찾기 위해 몇 가지 옵션을 거쳤습니다.
- 법령의 원본은 정부 웹사이트에 PDF 형식(예: 여기에서는 그 중 하나)으로 게시되고 즉시 사라집니다. 유용한 정보를 빼낼 수 없습니다.
- 언뜻 보기에 유혹적인 옵션은 해당 데이터 세트가 있는 "연맹의 개방형 데이터 포털"인 것처럼 보였지만 자세히 살펴보면 모든 것이 슬픈 것으로 나타났습니다. 사이트가 엑셀로 가져오기가 너무 불편하고, 기술 지원이 응답하지 않고(자체 격리?), 데이터 자체가 오랫동안 구식이었습니다. 2020년 생산 달력이 2019년 2020월에 마지막으로 업데이트되었습니다(치욕스럽습니다!). 물론, 예를 들어 '코로나바이러스'와 XNUMX년 '투표' 주말은 포함되지 않습니다.
공식 출처에 환멸을 느낀 나는 비공식 출처를 파헤치기 시작했습니다. 인터넷에 많은 것들이 있지만 대부분은 다시 Excel로 가져 오는 데 완전히 적합하지 않고 아름다운 그림 형태로 생산 달력을 제공합니다. 하지만 벽에 걸어두는 건 우리가 할 일이 아니잖아요?
그리고 검색하는 과정에서 우연히 발견한 놀라운 사실 – 사이트 http://xmlcalendar.ru/
불필요한 "장식" 없이 단순하고 가볍고 빠른 사이트가 XML 형식으로 원하는 연도의 생산 달력을 모두에게 제공하는 하나의 작업을 위해 날카로워졌습니다. 훌륭한!
갑자기 모르는 경우 XML은 특수 문자로 표시된 내용이 포함된 텍스트 형식입니다.
만일을 대비하여 사이트 작성자에게 연락하여 사이트가 7년 동안 존재했음을 확인했으며 해당 사이트의 데이터는 지속적으로 업데이트되며(이를 위해 github에 분기도 있음) 사이트를 닫지 않을 것입니다. 그리고 저는 여러분과 제가 Excel의 모든 프로젝트 및 계산에 대한 데이터를 로드하는 데 전혀 신경 쓰지 않습니다. 무료입니다. 아직도 이런 분들이 계시다니 신기하네요! 존경!
파워 쿼리 추가 기능을 사용하여 이 데이터를 Excel에 로드해야 합니다(Excel 2010-2013 버전의 경우 Microsoft 웹 사이트에서 무료로 다운로드할 수 있으며 Excel 2016 이상 버전에서는 기본적으로 이미 기본 제공됨) ).
행동의 논리는 다음과 같습니다.
- XNUMX년 동안 사이트에서 데이터 다운로드를 요청합니다.
- 우리의 요청을 함수로 바꾸기
- 우리는 이 기능을 2013년부터 현재 연도까지 사용 가능한 모든 연도 목록에 적용하고 자동 업데이트가 포함된 "영구적인" 생산 달력을 얻습니다. 짜잔!
1단계. XNUMX년 달력 가져오기
먼저 2020년과 같이 XNUMX년의 생산 달력을 로드합니다. 이렇게 하려면 Excel에서 탭으로 이동합니다. Data (또는 파워 쿼리별도의 추가 기능으로 설치한 경우)를 선택하고 인터넷에서 (웹에서). 열리는 창에서 사이트에서 복사한 해당 연도에 대한 링크를 붙여넣습니다.
클릭 후 OK 버튼을 클릭해야 하는 미리보기 창이 나타납니다. 데이터 변환 (변환 데이터) or 데이터를 변경하려면 (데이터 편집) Power Query 쿼리 편집기 창으로 이동하여 데이터 작업을 계속할 것입니다.
오른쪽 패널에서 즉시 안전하게 삭제할 수 있습니다. 요청 매개 변수 (쿼리 설정) 단계 수정된 유형 (변경된 유형) 우리는 그가 필요하지 않습니다.
휴일 열의 표에는 휴무일에 대한 코드와 설명이 포함되어 있습니다. 녹색 단어를 클릭하여 두 번 "빠르게 통과"하면 내용을 볼 수 있습니다. 작업대:
돌아가려면 오른쪽 패널에서 다시 표시된 모든 단계를 삭제해야 합니다. 출처 (출처).
유사한 방식으로 액세스할 수 있는 두 번째 테이블에는 우리가 필요로 하는 모든 휴무일의 날짜가 정확히 포함되어 있습니다.
이 판을 처리해야합니다. 즉 :
1. 두 번째 열로 휴일 날짜(즉, XNUMX일)만 필터링 속성:t
2. 첫 번째 열을 제외한 모든 열 삭제 – 첫 번째 열의 머리글을 마우스 오른쪽 버튼으로 클릭하고 명령을 선택하여 다른 열 삭제 (다른 열 제거):
3. 명령을 사용하여 월과 일에 대해 개별적으로 점으로 첫 번째 열 분할 열 분할 – 구분자별 탭 변환 (변환 — 열 분할 — 구분 기호로):
4. 마지막으로 일반 날짜로 계산된 열을 만듭니다. 이렇게 하려면 탭에서 열 추가 버튼을 클릭하십시오. 맞춤 항목 (열 추가 - 사용자 정의 열) 표시되는 창에 다음 수식을 입력합니다.
=#날짜(2020, [#»속성:d.1″], [#»속성:d.2″])
여기서 #date 연산자에는 각각 연도, 월, 일의 세 가지 인수가 있습니다. 클릭 후 OK 정상적인 주말 날짜로 필요한 열을 얻고 2단계에서와 같이 나머지 열을 삭제합니다.
2단계. 요청을 함수로 전환
다음 작업은 2020년에 대해 생성된 쿼리를 모든 연도에 대한 범용 함수로 변환하는 것입니다(연도 번호가 인수가 됨). 이를 위해 다음을 수행합니다.
1. 패널 확장(아직 확장되지 않은 경우) 문의 (쿼리) 파워 쿼리 창의 왼쪽:
2. 요청을 함수로 변환한 후에는 요청을 구성하는 단계를 보고 쉽게 편집할 수 있는 기능이 안타깝게도 사라집니다. 따라서 우리의 요청을 복사하여 이미 가지고 장난을 치고 원본은 남겨두는 것이 좋습니다. 이렇게 하려면 캘린더 요청의 왼쪽 창을 마우스 오른쪽 버튼으로 클릭하고 복제 명령을 선택합니다.
달력(2)의 결과 복사본을 다시 마우스 오른쪽 버튼으로 클릭하면 명령이 선택됩니다. 이름 바꾸기 (이름 바꾸기) 새 이름을 입력하십시오. 예를 들어, fx연도:
3. 명령을 사용하여 내부 파워 쿼리 언어(간단히 "M"이라고 함)로 쿼리 소스 코드를 엽니다. 고급 편집기 탭 검토(보기 — 고급 편집기) 그리고 우리의 요청을 어떤 연도의 기능으로 바꾸기 위해 거기에서 약간의 변경을 가하십시오.
그것은 :
후 :
세부 사항에 관심이 있으시면 여기를 클릭하십시오.
- (숫자로서의 연도)=> – 우리는 함수가 하나의 숫자 인수를 가질 것이라고 선언합니다 – 변수 year
- 변수 붙여넣기 year 단계에서 웹 링크로 출처. 파워 쿼리에서는 숫자와 텍스트를 붙일 수 없으므로 함수를 사용하여 연도 번호를 즉석에서 텍스트로 변환합니다. Number.ToText
- 끝에서 두 번째 단계에서 2020년에 대한 연도 변수를 대체합니다. #”사용자 정의 개체 추가«, 여기서 우리는 조각에서 날짜를 형성했습니다.
클릭 후 마감재 우리의 요청은 함수가 됩니다:
3단계. 모든 연도의 캘린더 가져오기
마지막으로 남은 것은 사용 가능한 모든 연도에 대한 데이터를 업로드하고 수신된 모든 휴일 날짜를 하나의 테이블에 추가하는 마지막 주 쿼리를 만드는 것입니다. 이를 위해:
1. 회색 빈 공간에 왼쪽 쿼리 패널을 마우스 오른쪽 버튼으로 클릭하고 순차적으로 선택합니다. 새 요청 – 기타 소스 – 빈 요청 (새 쿼리 - 다른 소스에서 - 빈 쿼리):
2. 달력을 요청할 모든 연도 목록을 생성해야 합니다(예: 2013, 2014 ... 2020). 이렇게 하려면 표시되는 빈 쿼리의 수식 입력줄에 다음 명령을 입력합니다.
구조:
={숫자A..숫자B}
... 파워 쿼리에서 A에서 B까지의 정수 목록을 생성합니다. 예를 들어, 식
={1..5}
... 1,2,3,4,5의 목록을 생성합니다.
음, 2020년에 단단히 묶이지 않기 위해 함수를 사용합니다. 날짜시간.LocalNow() – Excel 기능의 아날로그 오늘 (오늘) 파워 쿼리에서 – 그리고 차례로 함수로 현재 연도를 추출합니다. 날짜.연도.
3. 결과 연도 집합은 꽤 적절해 보이지만 파워 쿼리용 테이블이 아니라 특수 개체입니다. 명부 (목록). 그러나 표로 변환하는 것은 문제가 되지 않습니다. 버튼을 클릭하기만 하면 됩니다. 테이블로 (테이블로) 왼쪽 상단에서:
4. 결승선! 앞서 만든 함수 적용하기 fx연도 연도의 결과 목록에. 이렇게 하려면 탭에서 열 추가 버튼을 누르십시오 커스텀 함수 호출 (열 추가 - 사용자 정의 함수 호출) 유일한 인수인 열을 설정합니다. Column1 수년 동안:
클릭 후 OK 우리의 기능 fx연도 가져오기는 매년 차례로 작동하며 각 셀에 휴무일 날짜가 있는 테이블이 포함된 열이 표시됩니다(테이블의 내용은 옆에 있는 셀의 배경을 클릭하면 명확하게 볼 수 있습니다. 단어 작업대):
열 머리글에 이중 화살표가 있는 아이콘을 클릭하여 중첩 테이블의 내용을 확장해야 합니다. 날짜 (진드기 원래 열 이름을 접두사로 사용 제거 가능):
... 그리고 클릭 후 OK 2013년부터 현재 연도까지의 모든 공휴일 목록:
이미 불필요한 첫 번째 열은 삭제할 수 있으며 두 번째 열은 데이터 유형을 설정합니다. 데이터 (데이트) 열 머리글의 드롭다운 목록에서:
쿼리 자체의 이름은 요청1 그런 다음 명령을 사용하여 동적 "스마트" 테이블 형태로 시트에 결과를 업로드합니다. 닫고 다운로드 탭 홈 (홈 — 닫기 및 로드):
명령을 통해 오른쪽 창에서 테이블이나 쿼리를 마우스 오른쪽 버튼으로 클릭하여 생성된 달력을 나중에 업데이트할 수 있습니다. 업데이트 및 저장. 또는 버튼을 사용 모두 새로고침 탭 Data (날짜 — 모두 새로 고침) 또는 키보드 단축키 Ctrl 키+다른+F5.
그게 다야.
이제 더 이상 휴일 목록을 검색하고 업데이트하느라 시간과 생각을 낭비할 필요가 없습니다. 이제 "영구적인" 생산 일정이 생겼습니다. 어쨌든, http://xmlcalendar.ru/ 사이트의 작성자가 그들의 자손을 지원하는 한, 나는 그것이 아주 아주 오랫동안 지속되기를 바랍니다(다시 한 번 그들에게 감사드립니다!).
- 파워 쿼리를 통해 인터넷에서 뛰어난 비트코인 비율 가져오기
- WORKDAY 함수를 사용하여 다음 영업일 찾기
- 날짜 간격의 교차점 찾기