生産カレンダー、つまり、すべての正式な営業日と休日がそれに応じてマークされている日付のリスト - Microsoft Excel のすべてのユーザーにとって絶対に必要なもの。 実際には、それなしではできません。
- 会計計算 (給与、勤続年数、休暇など)
- ロジスティクス – 週末と休日を考慮して配達時間を正しく決定するため (古典的な「休日の後に来てください?」を覚えていますか?)
- プロジェクト管理 – 作業日と非作業日を考慮して、条件を正しく見積もる
- 次のような関数の使用 就業日 (平日) or 純粋な労働者 (ネットワーク日)、引数として休日のリストが必要なため
- Power Pivot および Power BI でタイム インテリジェンス関数 (TOTALYTD、TOTALMTD、SAMEPERIODLASTYEAR など) を使用する場合
- … etc. etc. – たくさんの例。
生産カレンダーが組み込まれているため、1C や SAP などの企業 ERP システムで作業する人にとっては簡単です。 しかし、Excel ユーザーはどうでしょうか。
もちろん、そのようなカレンダーを手動で保持することもできます。 ただし、少なくとも年に 2020 回 (または「楽しい」XNUMX 年のようにさらに頻繁に) 更新し、政府によって考案されたすべての週末、移動、および休業日を慎重に入力する必要があります。 そして、この手順を毎年繰り返します。 退屈。
少しクレイジーになって、Excel で「永久的な」工場カレンダーを作成してみませんか? 自分自身を更新し、インターネットからデータを取得し、その後の計算で使用するために常に非稼働日の最新リストを生成するものですか? 魅力的ですか？
実際、これを行うことはまったく難しいことではありません。
情報元
主な問題は、どこでデータを取得するかです。 適切なソースを探して、いくつかのオプションを試しました。
- 元の法令は政府の Web サイトで PDF 形式で公開されており (たとえば、ここではそのうちの XNUMX つ)、すぐに消えてしまいます。そこから有用な情報を引き出すことはできません。
- A tempting option, at first glance, seemed to be the “Open Data Portal of the Federation”, where there is a corresponding data set, but, upon closer examination, everything turned out to be sad. The site is terribly inconvenient for importing into Excel, technical support does not respond (self-isolated?), and the data itself is outdated there for a long time – the production calendar for 2020 was last updated in November 2019 (disgrace!) and, of course, does not contain our “coronavirus ‘ and the ‘voting’ weekend of 2020, for example.
公式の情報源に幻滅した私は、非公式の情報源を掘り始めました。 それらの多くはインターネット上にありますが、それらのほとんどは、やはり Excel にインポートするのにはまったく適しておらず、生産カレンダーを美しい写真の形で提供しています。 でも、壁に掛けるのは僕らじゃないですよね？
そして、検索の過程で、素晴らしいことが偶然発見されました – サイト http://xmlcalendar.ru/
不必要な「フリル」がなく、シンプルで軽量、高速なサイトで、XNUMX つのタスクのために研ぎ澄まされ、希望する年の生産カレンダーを XML 形式で全員に提供します。 優秀な！
突然、知らないとしたら、XML は、コンテンツが特殊なマークアップされたテキスト形式です。
念のため、サイトの作成者に連絡したところ、サイトが 7 年間存在し、そのデータは常に更新されており (このために github にブランチさえあります)、サイトを閉じるつもりはないことが確認されました。 そして、私たちのプロジェクトや Excel での計算のために、あなたと私がそこからデータをロードすることはまったく気にしません。 無料です。 こういう人がまだいるってわかってよかった！ 尊敬する！
Power Query アドインを使用してこのデータを Excel に読み込む必要があります (Excel 2010 ～ 2013 のバージョンでは、Microsoft の Web サイトから無料でダウンロードできます。Excel 2016 以降のバージョンでは、既定で既に組み込まれています)。 ）。
アクションのロジックは次のようになります。
- 任意の XNUMX 年間、サイトからデータのダウンロードをリクエストします
- リクエストを関数に変換する
- この関数を、2013 年から現在の年までの利用可能なすべての年のリストに適用すると、自動更新による「永久的な」生産カレンダーが得られます。 出来上がり！
ステップ 1. XNUMX 年間のカレンダーをインポートする
まず、任意の 2020 年間 (XNUMX 年など) の生産カレンダーを読み込みます。これを行うには、Excel でタブに移動します。 且つ （または パワークエリ別のアドオンとしてインストールした場合) を選択し、 インターネットから （ウェブより）. 開いたウィンドウで、サイトからコピーした対応する年へのリンクを貼り付けます。
をクリックした後 OK ボタンをクリックする必要があるプレビューウィンドウが表示されます データの変換 (変換データ) or データを変更するには (編集データ) Power Query クエリ エディター ウィンドウが表示されます。ここでデータの操作を続けます。
すぐに右側のパネルで安全に削除できます リクエストパラメータ (クエリ設定) 手順 改造型 (タイプ変更) 彼は必要ありません。
休日の列の表には、非稼働日のコードと説明が含まれています。緑の単語をクリックして XNUMX 回「フォールスルー」すると、その内容を確認できます。 表:
戻るには、右側のパネルで、以前に表示されたすべてのステップを削除する必要があります。 ソース （ソース）.
同様の方法でアクセスできる XNUMX 番目のテーブルには、まさに必要なもの (すべての非稼働日の日付) が含まれています。
このプレートを処理する必要があります。つまり、次のとおりです。
1. XNUMX 番目の列で休日の日付 (つまり、休日) のみをフィルター処理します 属性:t
2. 最初の列を除くすべての列を削除します – 最初の列の見出しを右クリックし、コマンドを選択します 他の列を削除する (他の列を削除):
3. コマンドを使用して、最初の列を月と日ごとにドットで分割します 列の分割 – 区切り記号による タブ 変換 (変換 — 列の分割 — 区切り記号による):
4. 最後に、通常の日付で計算列を作成します。 これを行うには、タブで 列の追加 ボタンをクリック カスタム列 (列の追加 — カスタム列) 表示されるウィンドウに次の式を入力します。
=#デート(2020、[#»属性:d.1″]、[#»属性:d.2″])
ここで、#date 演算子には、それぞれ年、月、日の XNUMX つの引数があります。 をクリックした後 OK 通常の週末の日付で必要な列を取得し、ステップ 2 のように残りの列を削除します
ステップ 2. リクエストを関数に変換する
次のタスクは、2020 年用に作成されたクエリを任意の年に対応するユニバーサル関数に変換することです (年番号がその引数になります)。 これを行うには、次のことを行います。
1. パネルの展開 (まだ展開されていない場合) お問い合わせ (クエリ) Power Query ウィンドウの左側:
2. リクエストを関数に変換した後、残念ながら、リクエストを構成するステップを確認して簡単に編集する機能は失われます。 したがって、私たちのリクエストのコピーを作成し、すでにそれで戯れ、オリジナルを予約しておくことは理にかなっています. これを行うには、左側のペインでカレンダー リクエストを右クリックし、[複製] コマンドを選択します。
結果の calendar(2) のコピーをもう一度右クリックすると、コマンドが選択されます リネーム （名前を変更） 新しい名前を入力します。たとえば、次のようにします。 外国為替年:
3. 次のコマンドを使用して、Power Query 内部言語 (簡潔に「M」と呼ばれます) でクエリ ソース コードを開きます。 高度なエディタ タブ レビュー(表示 — 詳細エディター) そこに小さな変更を加えて、リクエストを任意の年の機能に変えます。
そうだった：
後：
詳細が気になる方はこちら：
- (数字としての年)=> – 関数が XNUMX つの数値引数を持つことを宣言します – 変数 年
- 変数の貼り付け 年 ステップの Web リンクへ ソース. Power Query では数字とテキストを貼り付けることはできないため、関数を使用してその場で年数をテキストに変換します。 Number.ToText
- 最後から 2020 番目のステップで、年変数を XNUMX に置き換えます。 #”カスタムオブジェクトを追加«、フラグメントから日付を作成しました。
をクリックした後 終了 私たちのリクエストは関数になります:
ステップ 3. すべての年のカレンダーをインポートする
最後に、最後のメイン クエリを作成します。これは、利用可能なすべての年のデータをアップロードし、受け取ったすべての休日の日付を XNUMX つのテーブルに追加します。 このため：
1. マウスの右ボタンで灰色の空のスペースにある左のクエリパネルをクリックし、順番に選択します 新しいリクエスト – その他のソース – 空のリクエスト (新しいクエリ — 他のソースから — 空白のクエリ):
2. カレンダーをリクエストするすべての年のリスト、つまり 2013 年、2014 年 … 2020 年を生成する必要があります。これを行うには、表示される空のクエリの数式バーに次のコマンドを入力します。
構造：
={数値A..数値B}
… Power Query では、A から B までの整数のリストが生成されます。たとえば、次の式
={1..5}
… 1,2,3,4,5 のリストが生成されます。
さて、2020年に固執しないために、関数を使用します DateTime.LocalNow() – Excel 関数のアナログ TODAY （今日） Power Query で – 関数によって現在の年を抽出します。 日付.年.
3. 結果の年のセットは、非常に適切に見えますが、Power Query のテーブルではなく、特別なオブジェクトです。 リスト （リスト）. しかし、それをテーブルに変換することは問題ではありません: ボタンをクリックするだけです テーブルへ (テーブルへ) 左上隅に:
4. ゴール！ 先ほど作成した関数の適用 外国為替年 結果の年のリストに。 これを行うには、タブで 列の追加 ボタンを押す カスタム関数を呼び出す (列の追加 — カスタム関数の呼び出し) 唯一の引数である列を設定します Column1 長年にわたって：
をクリックした後 OK 私たちの機能 外国為替年 インポートは毎年順番に機能し、各セルに非稼働日の日付を含むテーブルが含まれる列を取得します (テーブルの内容は、横のセルの背景をクリックすると明確に表示されます)言葉 表):
列ヘッダーの二重矢印の付いたアイコンをクリックして、ネストされたテーブルの内容を展開する必要があります 試合日 （ダニ 元の列名をプレフィックスとして使用する 削除できます):
…そしてクリックした後 OK 2013 年から今年までのすべての祝日のリストです。
最初の不要な列は削除でき、XNUMX 番目の列にはデータ型を設定できます date （日にち） 列見出しのドロップダウン リスト:
クエリ自体は、より意味のある名前に変更できます。 リクエスト1 コマンドを使用して、結果を動的な「スマート」テーブルの形式でシートにアップロードします。 閉じてダウンロード タブ ホーム (ホーム — クローズ & ロード):
コマンドを使用して右側のペインでテーブルまたはクエリを右クリックすると、作成したカレンダーを後で更新できます。 更新して保存. または ボタンを使用してください すべて更新 タブ 且つ (日付 — すべて更新) またはキーボードショートカット Ctrlキー+他の+F5.
それで全部です。
これで、休日のリストを検索して更新するために時間と思考燃料を無駄にする必要がなくなりました。これで、「永続的な」生産カレンダーができました。 いずれにせよ、サイトhttp://xmlcalendar.ru/の作成者が彼らの子孫をサポートしている限り、それは非常に長い間続くことを願っています（彼らに再び感謝します！）。
- Power Query を介してインターネットからビットコイン レートをインポートしてエクセル化する
- WORKDAY 関数を使用して翌営業日を検索する
- 日付間隔の交点を見つける