İçerik
Üretim takvimi, yani tüm resmi çalışma günlerinin ve tatillerin buna göre işaretlendiği bir tarih listesi – herhangi bir Microsoft Excel kullanıcısı için kesinlikle gerekli bir şey. Pratikte, onsuz yapamazsınız:
- muhasebe hesaplamalarında (maaş, hizmet süresi, tatiller…)
- lojistikte – hafta sonları ve tatil günleri dikkate alınarak teslimat sürelerinin doğru belirlenmesi için (“tatilden sonra gel” klasiğini hatırlayın)
- proje yönetiminde - çalışma ve çalışma dışı günleri de hesaba katarak terimlerin doğru tahmin edilmesi için
- gibi işlevlerin herhangi bir kullanımı İŞGÜNÜ (İŞGÜNÜ) or SAF İŞÇİLER (TAMİŞGÜNLERİ), çünkü argüman olarak bir tatil listesi istiyorlar
- Power Pivot ve Power BI'da Zaman Zekası işlevlerini (TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR vb.) kullanırken
- … vb. vs. – birçok örnek.
1C veya SAP gibi kurumsal ERP sistemlerinde çalışanlar için üretim takvimi yerleşik olduğundan daha kolaydır. Peki ya Excel kullanıcıları?
Elbette böyle bir takvimi manuel olarak tutabilirsiniz. Ancak daha sonra, hükümetimiz tarafından icat edilen tüm hafta sonlarını, transferleri ve çalışma dışı günleri dikkatlice girerek yılda en az bir kez (veya “neşeli” 2020'de olduğu gibi daha sık) güncellemeniz gerekecektir. Ve sonra her yıl bu prosedürü tekrarlayın. Can sıkıntısı.
Biraz çıldırmaya ve Excel'de "sürekli" bir fabrika takvimi yapmaya ne dersiniz? Kendini güncelleyen, İnternet'ten veri alan ve daha sonra herhangi bir hesaplamada kullanılmak üzere her zaman güncel bir çalışma dışı gün listesi oluşturan biri mi? Cazip?
Bunu yapmak, aslında, hiç de zor değil.
Veri kaynağı
Asıl soru, verilerin nereden alınacağıdır. Uygun bir kaynak ararken birkaç seçenekten geçtim:
- Orijinal kararnameler hükümetin web sitesinde PDF formatında yayınlanır (burada, örneğin bunlardan biri) ve hemen kaybolur - faydalı bilgiler bunlardan çıkarılamaz.
- İlk bakışta cazip bir seçenek, karşılık gelen bir veri setinin bulunduğu "Federasyonun Açık Veri Portalı" gibi görünüyordu, ancak daha yakından incelendiğinde her şeyin üzücü olduğu ortaya çıktı. Site, Excel'e aktarım için son derece elverişsizdir, teknik destek yanıt vermiyor (kendi kendini izole ediyor mu?) ve verilerin kendisi orada uzun süredir güncel değil - 2020 üretim takvimi en son Kasım 2019'da güncellendi (rezalet!) ve tabi ki bizim “koronavirüs”ümüzü ve 2020 yılının “oylama” haftasonunu içermiyor.
Resmi kaynaklarla hayal kırıklığına uğrayarak, gayri resmi kaynakları araştırmaya başladım. İnternette birçoğu var, ancak çoğu yine Excel'e aktarmak için tamamen uygun değil ve güzel resimler şeklinde bir üretim takvimi veriyor. Ama duvara asmak bize düşmez, değil mi?
Ve arama sürecinde, yanlışlıkla harika bir şey keşfedildi - site http://xmlcalendar.ru/
Gereksiz "fırfırlar" olmadan, tek bir görev için keskinleştirilmiş basit, hafif ve hızlı bir site - herkese istenen yıl için XML formatında bir üretim takvimi vermek. Harika!
Birdenbire bilgi sahibi değilseniz, o zaman XML, içeriği özel olarak işaretlenmiş bir metin biçimidir.
Her ihtimale karşı, sitenin yazarlarıyla iletişime geçtim ve sitenin 7 yıldır var olduğunu onayladılar, üzerindeki veriler sürekli güncelleniyor (bunun için github'da bir şubeleri bile var) ve kapatmayacaklar. Ve Excel'deki projelerimizden ve hesaplamalarımızdan herhangi biri için ondan veri yüklediğimizi hiç umursamıyorum. Bedava. Hala böyle insanların olduğunu bilmek güzel! Saygı duymak!
Power Query eklentisini kullanarak bu verileri Excel'e yüklemek için kalır (Excel 2010-2013 sürümleri için Microsoft web sitesinden ücretsiz olarak indirilebilir ve Excel 2016 ve daha yeni sürümlerinde varsayılan olarak yerleşiktir) ).
Eylemlerin mantığı aşağıdaki gibi olacaktır:
- Herhangi bir yıl için siteden veri indirmek için talepte bulunuyoruz
- İsteğimizi bir fonksiyona dönüştürmek
- Bu işlevi, 2013'ten başlayarak cari yıla kadar mevcut tüm yıllar listesine uyguluyoruz ve otomatik güncellemeli “sürekli” bir üretim takvimi elde ediyoruz. İşte!
Adım 1. Bir yıllık takvimi içe aktarın
İlk olarak, herhangi bir yıl için, örneğin 2020 için üretim takvimini yükleyin. Bunu yapmak için Excel'de sekmeye gidin. Veri (Ya da Güç Sorguayrı bir eklenti olarak yüklediyseniz) ve İnternetten (Web'den). Açılan pencerede, siteden kopyalanan ilgili yıla bağlantıyı yapıştırın:
Tıkladıktan sonra OK düğmeyi tıklamanız gereken bir önizleme penceresi görünür Verileri Dönüştür (Verileri dönüştür) or Verileri değiştirmek için (Verileri düzenle) ve verilerle çalışmaya devam edeceğimiz Power Query sorgu düzenleyici penceresine gideceğiz:
Hemen sağ panelde güvenle silebilirsiniz Parametreleri Talep Et (Sorgu ayarları) adım değiştirilmiş tip (Değiştirilen Tür) Ona ihtiyacımız yok.
Tatiller sütunundaki tablo, çalışılmayan günlerin kodlarını ve açıklamalarını içerir - içeriğini yeşil kelimeye tıklayarak iki kez “düşerek” görebilirsiniz. tablo:
Geri dönmek için, sağ panelde geri görünen tüm adımları silmeniz gerekecek. Kaynak (Kaynak).
Benzer şekilde erişilebilen ikinci tablo, tam olarak ihtiyacımız olanı içerir - tüm çalışma dışı günlerin tarihleri:
Bu plakayı işlemek için kalır, yani:
1. İkinci sütuna göre yalnızca tatil tarihlerini (yani tatil tarihlerini) filtreleyin Özellik: t
2. İlk sütun dışındaki tüm sütunları silin – ilk sütunun başlığına sağ tıklayarak ve komutu seçerek Diğer sütunları sil (Diğer Sütunları Kaldır):
3. İlk sütunu komutla ay ve gün için ayrı ayrı noktalara bölün Sütunu Böl – Sınırlayıcıya Göre çıkıntı Dönüşüm (Dönüştür — Sütunu böl — Sınırlayıcıya göre):
4. Ve son olarak, normal tarihlerle hesaplanmış bir sütun oluşturun. Bunu yapmak için sekmede Sütun ekleme düğmeye tıklayın Özel sütun (Sütun Ekle — Özel Sütun) ve görünen pencereye aşağıdaki formülü girin:
=#tarihli(2020, [#»Özellik:d.1″], [#»Özellik:d.2″])
Burada #date operatörünün üç argümanı vardır: sırasıyla yıl, ay ve gün. tıkladıktan sonra OK normal hafta sonu tarihlerine sahip gerekli sütunu alırız ve kalan sütunları 2. adımdaki gibi sileriz
Adım 2. İsteği bir işleve dönüştürmek
Bir sonraki görevimiz, 2020 için oluşturulan sorguyu herhangi bir yıl için evrensel bir fonksiyona dönüştürmektir (yıl numarası onun argümanı olacaktır). Bunu yapmak için aşağıdakileri yapıyoruz:
1. Paneli genişletme (zaten genişletilmemişse) Araştırma (Sorguları) Power Query penceresinin solunda:
2. İsteği fonksiyona dönüştürdükten sonra isteği oluşturan adımları görme ve kolayca düzenleme özelliği maalesef ortadan kalkıyor. Bu nedenle, talebimizin bir kopyasını çıkarmak ve onunla zaten eğlenmek ve orijinali yedekte bırakmak mantıklıdır. Bunu yapmak için, takvim isteğimizde sol bölmeye sağ tıklayın ve Çoğalt komutunu seçin.
Ortaya çıkan takvim (2) kopyasına tekrar sağ tıklamak komutu seçecektir. adını değiştirmek (Yeniden isimlendirmek) ve yeni bir ad girin - örneğin, fxYıl:
3. Komutu kullanarak sorgu kaynak kodunu dahili Power Query dilinde (kısaca “M” olarak adlandırılır) açıyoruz. Gelişmiş Düzenleyici çıkıntı Değerlendirme(Görünüm — Gelişmiş Düzenleyici) ve isteğimizi herhangi bir yıl için bir işleve dönüştürmek için orada küçük değişiklikler yapın.
It was:
Sonra:
Ayrıntılarla ilgileniyorsanız, işte burada:
- (yıl olarak sayı)=> – fonksiyonumuzun bir sayısal argümana sahip olacağını beyan ederiz – bir değişken yıl
- Değişkeni yapıştırma yıl adımda web bağlantısına Kaynak. Power Query, sayıları ve metni yapıştırmanıza izin vermediğinden, işlevi kullanarak yıl numarasını anında metne dönüştürürüz Number.ToText
- Sondan bir önceki adımda 2020 için yıl değişkenini değiştiriyoruz #”Özel nesne eklendi«, tarihi parçalardan oluşturduğumuz yer.
Tıkladıktan sonra Bitiş isteğimiz bir fonksiyon haline gelir:
Adım 3. Tüm yıllar için takvimleri içe aktarın
Geriye kalan son şey, mevcut tüm yıllar için veri yükleyecek ve alınan tüm tatil tarihlerini tek bir tabloya ekleyecek olan son ana sorguyu yapmaktır. Bunun için:
1. Soldaki sorgu panelinde gri boş bir alana farenin sağ tuşu ile tıklıyoruz ve sırayla seçiyoruz Yeni istek – Diğer kaynaklar – Boş istek (Yeni Sorgu — Diğer kaynaklardan — Boş sorgu):
2. Takvim isteyeceğimiz tüm yılların bir listesini oluşturmamız gerekiyor, yani 2013, 2014 … 2020. Bunun için görünen boş sorgunun formül çubuğuna şu komutu girin:
Yapısı:
={Sayı..SayıB}
… Power Query'de A'dan B'ye bir tamsayı listesi oluşturur. Örneğin, ifade
={1..5}
… 1,2,3,4,5'lik bir liste oluşturur.
Eh, 2020'ye katı bir şekilde bağlanmamak için işlevi kullanıyoruz DateTime.LocalNow() – Excel fonksiyonunun analogu BUGÜN (BUGÜN) Power Query'de - ve bundan, sırayla, işleve göre geçerli yılı çıkarın Tarih.Yıl.
3. Ortaya çıkan yıl kümesi, oldukça yeterli görünse de, Power Query için bir tablo değil, özel bir nesnedir – liste (Liste). Ancak onu bir tabloya dönüştürmek sorun değil: sadece düğmeye tıklayın Masaya (Masaya) sol üst köşede:
4. Bitiş çizgisi! Daha önce oluşturduğumuz işlevi uygulamak fxYıl ortaya çıkan yıl listesine. Bunu yapmak için sekmede Sütun ekleme düğmesine basın Özel işlevi çağır (Sütun Ekle — Özel İşlevi Çağır) ve tek argümanını ayarlayın - sütun Column1 yıllar sonra:
Tıkladıktan sonra OK bizim fonksiyonumuz fxYıl içe aktarma her yıl için sırayla çalışacak ve her hücrenin çalışmayan günlerin tarihlerini içeren bir tablo içereceği bir sütun alacağız (yanındaki hücrenin arka planına tıklarsanız tablonun içeriği açıkça görülebilir) kelime tablo):
Sütun başlığındaki çift oklu simgeye tıklayarak iç içe geçmiş tabloların içeriğini genişletmeye devam eder. Tarih (kene Önek olarak orijinal sütun adını kullan kaldırılabilir):
… ve tıkladıktan sonra OK istediğimizi elde ederiz – 2013'ten bu yıla kadar olan tüm tatillerin listesi:
Zaten gereksiz olan ilk sütun silinebilir ve ikincisi için veri türünü ayarlayın tarih (Tarih) sütun başlığındaki açılır listede:
Sorgunun kendisi, sorgudan daha anlamlı bir şey olarak yeniden adlandırılabilir. istek1 ve ardından komutu kullanarak sonuçları dinamik bir "akıllı" tablo biçiminde sayfaya yükleyin kapat ve indir çıkıntı Ana Sayfa (Ana Sayfa — Kapat ve Yükle):
Oluşturulan takvimi ileride tabloya sağ tıklayarak veya sağ bölmede bulunan sorgu ile komut üzerinden güncelleyebilirsiniz. Güncelle ve Kaydet. Veya düğmeyi kullanın Hepsini yenile çıkıntı Veri (Tarih — Tümünü Yenile) veya klavye kısayolu Ctrl+Ara Toplam+F5.
Bu kadar.
Artık tatil listesini aramak ve güncellemek için zaman ve düşünce yakıtı harcamanıza bir daha asla gerek yok - artık “sürekli” bir üretim takviminiz var. Her durumda, http://xmlcalendar.ru/ sitesinin yazarları yavrularını desteklediği sürece, umarım çok, çok uzun bir süre olacaktır (onlara tekrar teşekkürler!).
- Power Query aracılığıyla internetten mükemmel olmak için bitcoin oranını içe aktarın
- İŞGÜNÜ işlevini kullanarak bir sonraki iş gününü bulma
- Tarih aralıklarının kesişimini bulma