İçerik
Karşılaştırmamız ve farkları hızlı bir şekilde bulmamız gereken iki tablomuz var (örneğin, fiyat listesinin eski ve yeni sürümleri):
Yeni fiyat listesine bir şey eklendiği (hurma, sarımsak…), bir şeyin kaybolduğu (böğürtlen, ahududu…), bazı mallar için fiyatların değiştiği (incir, kavun…) hemen belli oluyor. Tüm bu değişiklikleri hızlı bir şekilde bulmanız ve görüntülemeniz gerekir.
Excel'deki herhangi bir görev için hemen hemen her zaman birden fazla çözüm vardır (genellikle 4-5). Sorunumuz için birçok farklı yaklaşım kullanılabilir:
- işlev VPR (DÜŞEYARA) — eski fiyat listesindeki yeni fiyat listesinden ürün adlarını arayın ve eski fiyatı yenisinin yanında görüntüleyin ve ardından farklılıkları yakalayın
- iki listeyi tek bir listede birleştirin ve ardından buna dayalı olarak farklılıkların açıkça görüleceği bir pivot tablo oluşturun
- Excel için Power Query Eklentisini kullanın
Hepsini sırayla alalım.
Yöntem 1. Tabloları DÜŞEYARA işleviyle karşılaştırma
Bu harika özelliğe tamamen yabancıysanız, önce buraya bakın ve bununla ilgili bir video eğitimi okuyun veya izleyin - kendinize birkaç yıl hayat kurtarın.
Tipik olarak, bu işlev, bazı ortak parametreleri eşleştirerek verileri bir tablodan diğerine çekmek için kullanılır. Bu durumda, eski fiyatları yeni fiyata itmek için kullanacağız:
#N/A hatasının çıktığı ürünler eski listede değil, yani eklendi. Fiyat değişiklikleri de açıkça görülebilir.
Artılar bu yöntem: basit ve net, dedikleri gibi “türün klasiği”. Excel'in herhangi bir sürümünde çalışır.
Eksiler da orada. Yeni fiyat listesine eklenen ürünleri aramak için aynı işlemi ters yönde yapmanız yani DÜŞEYARA yardımıyla yeni fiyatları eski fiyatına çekmeniz gerekecektir. Tabloların boyutları yarın değişirse, formüllerin ayarlanması gerekecektir. Ve gerçekten büyük masalarda (> 100 bin sıra), tüm bu mutluluklar terbiyeli bir şekilde yavaşlayacak.
Yöntem 2: Bir pivot kullanarak tabloları karşılaştırma
Tablolarımızı alt alta kopyalayalım, fiyat listesinin adının olduğu bir sütun ekleyerek daha sonra hangi listeden hangi satırdan anlayasınız:
Şimdi oluşturulan tablodan yola çıkarak bir özet oluşturacağız. Ekle – Özet Tablo (Ekle — Özet Tablo). Hadi bir alan atalım PLATFORM çizgilerin alanına, alan Ücret sütun alanına ve alanına Цena aralığına:
Gördüğünüz gibi, pivot tablo, eski ve yeni fiyat listelerinden tüm ürünlerin genel bir listesini otomatik olarak oluşturacak (tekrar yok!) ve ürünleri alfabetik olarak sıralayacaktır. Eklenen ürünleri (eski fiyatı yoktur), kaldırılan ürünleri (yeni fiyatı yoktur) ve varsa fiyat değişikliklerini net bir şekilde görebilirsiniz.
Böyle bir tablodaki genel toplamlar bir anlam ifade etmez ve sekmede devre dışı bırakılabilirler. Yapıcı – Genel toplamlar – Satır ve sütunlar için devre dışı bırak (Tasarım — Genel Toplamlar).
Fiyatlar değişirse (ama mal miktarı değişmezse!), o zaman oluşturulan özeti sağ tıklayarak güncellemeniz yeterlidir – Yenile.
Artılar: Bu yaklaşım, büyük tablolarda DÜŞEYARA'ya göre çok daha hızlıdır.
Eksiler: verileri birbiri altına manuel olarak kopyalamanız ve fiyat listesinin adını içeren bir sütun eklemeniz gerekir. Tabloların boyutları değişirse, her şeyi baştan yapmanız gerekir.
Yöntem 3: Tabloları Power Query ile karşılaştırma
Power Query, hemen hemen her kaynaktan Excel'e veri yüklemenize ve ardından bu verileri istediğiniz şekilde dönüştürmenize olanak tanıyan ücretsiz bir Microsoft Excel eklentisidir. Excel 2016'da bu eklenti, varsayılan olarak sekmede yerleşik olarak bulunur Veri (Veri), ve Excel 2010-2013 için Microsoft web sitesinden ayrı olarak indirmeniz ve yüklemeniz gerekir - yeni bir sekme alın Güç Sorgu.
Fiyat listelerimizi Power Query'ye yüklemeden önce akıllı tablolara dönüştürülmeleri gerekir. Bunu yapmak için, veri içeren aralığı seçin ve klavyedeki kombinasyona basın. Ctrl+T veya şeritteki sekmeyi seçin Ana Sayfa – Tablo olarak biçimlendir (Ana Sayfa — Tablo Olarak Biçimlendir). Oluşturulan tabloların isimleri sekmede düzeltilebilir. Inşaatçı (Standartları bırakacağım Tablo 1 и Tablo 2, varsayılan olarak elde edilir).
Düğmeyi kullanarak eski fiyatı Power Query'ye yükleyin Tablodan/Aralıktan (Tablodan/Aralıktan) sekmeden Veri (Tarih) veya sekmesinden Güç Sorgu (Excel sürümüne bağlı olarak). Yüklemeden sonra, Power Query'den komutla Excel'e geri döneceğiz Kapat ve yükle – Kapat ve yükle… (Kapat ve Yükle — Kapat ve Yükle…):
… ve görünen pencerede ardından öğesini seçin. Sadece bir bağlantı oluşturun (Yalnızca Bağlantı).
Aynısını yeni fiyat listesiyle tekrarlayın.
Şimdi önceki ikisinden gelen verileri birleştirecek ve karşılaştıracak üçüncü bir sorgu oluşturalım. Bunu yapmak için, sekmede Excel'de seçin Veri – Veri Al – İstekleri Birleştir – Birleştir (Veri — Veri Al — Sorguları Birleştir — Birleştir) veya düğmesine basın Birleştirmek (Birleştirmek) çıkıntı Güç Sorgu.
Katıl penceresinde, açılır listelerden tablolarımızı seçin, içinde mal isimlerinin bulunduğu sütunları seçin ve altta birleştirme yöntemini ayarlayın – Komple harici (Tam Dış):
Tıkladıktan sonra OK Üç sütunlu bir tablo görünmelidir, burada üçüncü sütunda başlıktaki çift oku kullanarak iç içe geçmiş tabloların içeriğini genişletmeniz gerekir:
Sonuç olarak, her iki tablodan da verilerin birleştirilmesini elde ederiz:
Elbette, başlıktaki sütun adlarını daha anlaşılır olanlara çift tıklayarak yeniden adlandırmak daha iyidir:
Ve şimdi en ilginç olanı. Sekmeye git Sütun ekle (Sütun Ekle) ve düğmesine tıklayın koşullu sütun (Koşullu Sütun). Ardından açılan pencerede, karşılık gelen çıkış değerleriyle birlikte birkaç test koşulu girin:
tıklamak kalıyor OK ve aynı düğmeyi kullanarak ortaya çıkan raporu Excel'e yükleyin kapat ve indir (Kapat ve Yükle) çıkıntı Ana Sayfa (Ev):
Güzellik.
Ayrıca ileride fiyat listelerinde herhangi bir değişiklik olursa (satırlar eklenir veya silinir, fiyatlar değişir vb.), sadece klavye kısayolu ile taleplerimizi güncellememiz yeterli olacaktır. Ctrl+Ara Toplam+F5 veya düğme ile Hepsini yenile (Hepsini yenile) çıkıntı Veri (Tarih).
Artılar: Belki de hepsinin en güzel ve kullanışlı yolu. Büyük masalarla akıllıca çalışır. Tabloları yeniden boyutlandırırken manuel düzenleme gerektirmez.
Eksiler: Power Query eklentisinin (Excel 2010-2013'te) veya Excel 2016'nın yüklenmesini gerektirir. Kaynak verilerdeki sütun adları değiştirilmemelidir, aksi takdirde “Şu ve bu sütun bulunamadı!” hatası alırız. sorguyu güncellemeye çalışırken.
- Power Query kullanarak belirli bir klasördeki tüm Excel dosyalarından veri nasıl toplanır
- Excel'de iki liste arasındaki eşleşmeler nasıl bulunur?
- Yinelenenler olmadan iki listeyi birleştirme