Farklı bütçelerle birkaç proje yürüttüğünüzü ve her biri için maliyetlerinizi görselleştirmek istediğinizi varsayalım. Yani, bu kaynak tablodan:
.. şöyle bir şey alın:
Başka bir deyişle, bütçeyi her projenin günlerine yaymanız ve proje Gantt şemasının basitleştirilmiş bir versiyonunu almanız gerekir. Bunu ellerinizle yapmak uzun ve sıkıcı, makrolar zordur, ancak böyle bir durumda Excel için Power Query gücünü tüm ihtişamıyla gösterir.
Güç Sorgu hemen hemen her kaynaktan Excel'e veri aktarabilen ve daha sonra bir sürü farklı yolla dönüştürebilen bir Microsoft eklentisidir. Excel 2016'da bu eklenti varsayılan olarak yerleşiktir ve Excel 2010-2013 için Microsoft web sitesinden indirilebilir ve ardından bilgisayarınıza yüklenebilir.
İlk önce komutu seçerek orijinal tablomuzu “akıllı” bir tabloya çevirelim. Tablo olarak biçimlendir çıkıntı Ana Sayfa (Ana Sayfa — Tablo Olarak Biçimlendir) veya klavye kısayoluna basarak Ctrl+T :
Ardından sekmeye gidin Veri (Excel 2016'nız varsa) veya sekmesinde Güç Sorgu (Excel 2010-2013'e sahipseniz ve Power Query'yi ayrı bir eklenti olarak yüklediyseniz) ve Tablodan / Aralıktan düğmesini tıklayın. :
Akıllı tablomuz, ilk adımın tablo başlığındaki açılır menüleri kullanarak her sütun için sayı biçimlerini ayarlamak olduğu Power Query sorgu düzenleyicisine yüklenir:
Günlük bütçeyi hesaplamak için her projenin süresini hesaplamanız gerekir. Bunu yapmak için (tuşu basılı tutun) öğesini seçin. Ctrl) ilk sütun Bitiş, ve sonra Başlama ve bir takım seçin Sütun ekle – Tarih – Günleri çıkar (Sütun Ekle — Tarih — Günleri çıkar):
Ortaya çıkan sayılar gerekenden 1 eksik, çünkü her projeye sabah ilk gün başlayıp akşam son gün bitirmemiz gerekiyor. Bu nedenle, sonuç sütununu seçin ve komutu kullanarak ona bir birim ekleyin. Dönüştür – Standart – Ekle (Dönüştür — Standart — Ekle):
Şimdi günlük bütçeyi hesapladığımız bir sütun ekleyelim. Bunu yapmak için sekmede Sütun ekle oynamıyorum Özel sütun (Özel Sütun) ve görünen pencerede, listedeki sütunların adlarını kullanarak yeni alanın adını ve hesaplama formülünü girin:
Şimdi en ince an – 1 günlük bir adımla baştan sona tarihlerin bir listesini içeren başka bir hesaplanmış sütun oluşturuyoruz. Bunu yapmak için tekrar düğmeye basın Özel sütun (Özel Sütun) olarak adlandırılan yerleşik Power Query dili M'yi kullanın. Liste.Tarihler:
Bu işlevin üç argümanı vardır:
- başlangıç tarihi - bizim durumumuzda sütundan alınır Başlama
- oluşturulacak tarih sayısı – bizim durumumuzda bu, sütunda daha önce saydığımız her proje için gün sayısıdır Çıkarma
- zaman adımı – tasarım tarafından belirlenir #süre(1,0,0,0), M dilinde anlamı – bir gün, sıfır saat, sıfır dakika, sıfır saniye.
Tıkladıktan sonra OK tablo başlığındaki düğmeyi kullanarak yeni satırlara genişletilebilen bir tarih listesi (Liste) alırız:
… ve şunu elde ederiz:
Şimdi geriye kalan tek şey, yeni sütunların adları olarak oluşturulan tarihleri kullanarak tabloyu daraltmak. Bundan ekip sorumludur. Ayrıntı sütunu (Pivot Sütun) çıkıntı dönüştürmek (Dönüştür):
Tıkladıktan sonra OK istenene çok yakın bir sonuç elde ederiz:
Null, bu durumda, Excel'deki boş bir hücrenin bir analogudur.
Gereksiz sütunları kaldırmak ve ortaya çıkan tabloyu komutla orijinal verilerin yanında kaldırmak için kalır. Kapat ve yükle – Kapat ve yükle… (Kapat ve Yükle — Kapat ve Yükle…) çıkıntı Ana Sayfa (Ev):
Sonuç olarak şunu elde ederiz:
Daha fazla güzellik için, sonuçta ortaya çıkan akıllı tabloların görünümünü sekmede özelleştirebilirsiniz. Inşaatçı (Tasarım): tek bir renk stili ayarlayın, filtre düğmelerini devre dışı bırakın, toplamları etkinleştirin, vb. Ek olarak, sekmedeki koşullu biçimlendirmeyi kullanarak tarih içeren bir tablo seçebilir ve bunun için sayı vurgulamayı etkinleştirebilirsiniz. Ana Sayfa — Koşullu Biçimlendirme — Renk Ölçekleri (Ana Sayfa — Koşullu Biçimlendirme — Renk Ölçekleri):
Ve en iyi yanı, gelecekte eskileri güvenle düzenleyebilmeniz veya orijinal tabloya yeni projeler ekleyebilmeniz ve ardından sağ fare düğmesiyle doğru tabloyu tarihlerle güncelleyebilmeniz ve Power Query, yaptığımız tüm eylemleri otomatik olarak tekrarlayacaktır. .
Voilà!
- Koşullu biçimlendirme kullanarak Excel'de Gantt grafiği
- Proje kilometre taşı takvimi
- Power Query ile Yinelenen Satır Oluşturma