Floridalı David bugünün sorusunu soruyor:
İki çalışma kitabım var. Her ikisi de A sütununda aynı verilere sahiptir, ancak kalan sütunlar farklıdır. Bu iki çalışma kitabını nasıl birleştirebilirim?
David'e bir çalışma kitabının diğerinden daha fazla kayıt içermesinin mümkün olup olmadığını sordum. Ve cevap evet. David'e anahtar alanının her dosyada yalnızca bir kez görünüp görünmediğini sordum. Cevap da evet. Bugün bunu Power Query ile çözeceğim. Power Query araçları, Excel 2016+ Windows sürümlerinde Veri sekmesinin Al ve Dönüştür bölümünde bulunur. Excel 2010 veya Excel 2013'ün Windows sürümlerine sahipseniz, bu sürümler için Power Query eklentisini indirebilirsiniz.
İşte David'in çalışma kitabı 1. Ürün ve ardından üç veri sütunu var.
İşte David'in çalışma kitabı 2. Ürün Kodu ve ardından başka sütunlar var. Bu örnekte, çalışma kitabı2'de fazladan ürünler var, ancak çalışma kitabında fazladan sütun varsa çözümler işe yarayacaktır.
İşte adımlar:
-
Çalışma Kitabından, Dosyadan Veri Al, Verileri Seçin:
Bir dosyadan veri yükle - İlk çalışma kitabına göz atın ve Tamam'a tıklayın
- Gezgin iletişim kutusunda, soldaki çalışma sayfasını seçin. (Yalnızca bir çalışma sayfası olsa bile onu seçmelisiniz.) Verileri sağda göreceksiniz.
- Gezgin iletişim kutusunda, Yükle açılır menüsünü açın ve Şuraya Yükle… 'yi seçin.
- Yalnızca Bağlantı Oluştur'u seçin ve Tamam'a basın.
-
İkinci çalışma kitabı için 1-5 arasındaki adımları yineleyin.
Çalışma kitabına bir bağlantı oluşturun Her iki çalışma kitabını da yaptıysanız, Excel ekranınızın sağındaki Sorgular ve Bağlantılar Panelinde iki bağlantı görmelisiniz.
Her iki çalışma kitabına bağlantılar Çalışma kitaplarını birleştirme adımlarına devam edin:
-
Veriler, Veri Alın, Sorguları Birleştirin, Birleştirin.
İki sorguyu farklı sütunlarla birleştirin - Birleştir iletişim kutusunun üst kısmındaki açılır menüden ilk sorguyu seçin.
- Birleştir iletişim kutusundaki ikinci açılır listeden ikinci sorguyu seçin.
- Üst önizlemede Ürün başlığına tıklayın (bu anahtar alandır. Ctrl + Tıklayarak iki veya daha fazla anahtar alanını çoklu seçebileceğinizi unutmayın)
- İkinci önizlemede Ürün Kodu başlığına tıklayın.
-
Birleştirme Tipini açın ve Tam Dış'ı seçin (İkiden Tüm Satırlar)
Adım 8 - 12 burada gösterilmiştir -
Tamam'ı tıklayın. Veri önizlemesi ekstra satırları göstermez ve yalnızca son sütunda art arda "Tablo" yu gösterir.
Bu umut verici görünmüyor - DavidTwo'nun başlığında bir "Genişlet" simgesi olduğuna dikkat edin. Bu simgeye tıklayın.
-
İsteğe bağlı, ancak "Orijinal Sütun Adını Önek Olarak Kullan" seçeneğinin işaretini her zaman kaldırıyorum. Tamam'ı tıklayın.
Çalışma kitabı 2'deki alanları genişletin Sonuçlar bu önizlemede gösterilmektedir:
Her iki çalışma kitabındaki tüm kayıtlar - Power Query'de Giriş, Kapat ve Yükle'yi kullanın.
İşte güzel özellik: Her iki çalışma kitabındaki temel veriler değişirse, sonuçlar çalışma kitabına yeni veriler çekmek için Yenile simgesine tıklayabilirsiniz.
Not
Yenileme simgesi genellikle gizlidir. Simgeyi ortaya çıkarmak için Sorgular ve Bağlantılar bölmesinin sol kenarını sola sürükleyin.
Video izle
Video Metni
Podcast'ten Excel Öğrenin, Bölüm 2216: Ortak Bir Sütuna Dayalı İki Çalışma Kitabını Birleştirme.
İnternet yayınına tekrar hoş geldiniz, ben Bill Jelen Bugünün sorusu, IIA'nın Uzay Sahili Bölümü için Melbourne, Florida'daki seminerimde olan David'den.
David'in, Sütun A'nın ikisi arasında ortak olduğu iki farklı çalışma kitabı vardır. İşte Çalışma Kitabı 1, işte Çalışma Kitabı 2 - her ikisinin de ürün kodu var. Bunda ilkinde olmayan öğeler var ya da tam tersi ve David tüm sütunları birleştirmek istiyor. Yani burada üç sütunumuz ve burada dört sütunumuz var. Çalışmak için çalışma kitabını indirmeniz durumunda, ikisini de aynı çalışma kitabına koydum. Bunların her birini alın, kendi çalışma kitabına taşıyın ve kaydedin.
Pekala, bu dosyaları birleştirmek için Power Query kullanacağız. Power Query, Excel 2016'da yerleşiktir. Windows 10 veya 13 sürümündeyseniz, Microsoft'a gidip Power Query'yi indirebilirsiniz. Boş bir çalışma sayfasıyla yeni bir boş çalışma kitabından başlayabilirsiniz. Bu dosyayı kaydedeceksiniz - Farklı kaydet, bilirsiniz, belki Çalışma Kitabı, birleştirilmiş dosyaların .xlsx sonuçlarını göstermek için. Peki? Ve yapacağımız şey, iki sorgu yapacağız. Veri, Veri Al, Dosyadan, Çalışma Kitabından seçeneğine gideceğiz ve ardından ilk dosyayı seçeceğiz. Bir önizlemede, verilerinizin bulunduğu sayfayı seçin ve bu verilere herhangi bir şey yapmamız gerekmez. Bu nedenle, yükleme kutusunu açın ve Şuraya Yükle, Yalnızca Bağlantı Oluştur'u seçin, Tamam'a tıklayın. Mükemmel. Şimdi, bunu ikinci öğe için tekrarlayacağız - Veriler, Dosyadan,Bir Çalışma Kitabından DavidTwo'yu seçin, sayfa adını seçin ve ardından yükle, Şuraya Yükle, Yalnızca Bağlantı Oluştur'u açın. Burada, bu panelde göreceksiniz, iki bağlantımız da mevcut. Peki.
Şimdi asıl çalışma - Veri, Veri Al, Sorguları Birleştir, Birleştir ve ardından Birleştir iletişim kutusunda DavidOne, DavidTwo'yu seçin ve sonraki adım tamamen mantıksız. Bunu yapmak zorundasın. Ortak sütun veya sütunları seçin - yani Ürün ve Ürün. Peki. Ve sonra, burada birleştirme türüne çok dikkat edin. Her ikisinden de tüm satırları istiyorum çünkü birinde fazladan bir satır olabilir ve bunu görmem gerekiyor ve sonra Tamam'a tıklıyoruz. Peki. Ve işte ilk sonuç. Çalışmış gibi görünmüyor; 2. dosyadaki fazladan öğeleri eklemiş gibi görünmüyor. Ve bu sütun 5'e sahibiz - şimdi boş. 5. sütunu sağ tıklayıp, Bu sütunu kaldır diyeceğim. Öyleyse bu genişletme simgesini açın ve önek olarak orijinal sütun adını kullan ve BAM için bu kutunun işaretini kaldırın. işe yarıyor. Dolayısıyla, Dosya 2'de bulunan ve Dosya 1'de olmayan fazladan öğeler,görünür.
Peki. Şimdi bugünün dosyasında, bu Ürün Kodu sütunu bu Ürün sütunundan daha iyi görünüyor, çünkü fazladan satırları var. Ancak gelecekte Çalışma Kitabı 1'in Çalışma Kitabı 2'de olmayan şeylere sahip olduğu bir gün olabilir. Yani ikisini de orada bırakacağım ve herhangi bir boşluktan kurtulmayacağım çünkü, en alttaki bu satır tamamen boş görünse de, gelecekte bir durum olabilir. Burada birkaç boş var çünkü bir şeyler eksik. Peki? Son olarak, Kapat ve Yükle ve on altı satırımız var.
Şimdi, gelecekte bir şeylerin değiştiğini söyleyelim. Pekala, bu iki dosyadan birine geri döneceğiz ve Apple'ın sınıfını 99 olarak değiştireceğim ve hatta yeni bir şey ekleyip bu çalışma kitabını kaydedelim. Peki. Ve sonra, birleştirme dosyamızın güncellenmesini istiyorsak, buraya gelin - şimdi dikkat edin, bunu ilk kez yaptığınızda, Yenile simgesini göremezsiniz - bu çubuğu tutup üzerine sürüklemelisiniz . Ve Yenileme yapacağız ve 17 sıra yüklendi, karpuz belirecek, Apple 99'a dönüşecek - bu güzel bir şey. Şimdi, hey, Power Query hakkında bilgi edinmek ister misin? Bu kitabı Ken Puls ve Miguel Escobar'dan satın alın, M is for (DATA) MONKEY. Seni hızlandıracağım.
Bugün özetleyin: Florida'dan David'in birleştirmek istediği iki çalışma kitabı var; her ikisi de Sütun A'da aynı alanlara sahiptir, ancak diğer sütunların tümü farklıdır; bir çalışma kitabında diğerinde olmayan fazladan öğeler olabilir ve David bunları ister; her iki dosyada da kopya yok; bunu çözmek için güçlü sorgu kullanacağız, bu nedenle boş bir çalışma sayfasında yeni bir boş çalışma kitabıyla başlayın; üç sorgu yapacaksınız, birincisi Veriler, Dosyadan, Çalışma Kitabı ve ardından Yalnızca Oluşturulan Bağlantıya Yükle; ikinci çalışma kitabı için aynı şey ve ardından Veri, Veri Al, Birleştir, iki bağlantıyı seçin, her ikisinde de ortak olan sütunu seçin - benim durumumda, Ürün - ve ardından Birleştirme Türü'nden tam katılmak istiyorsunuz tümü Dosya 1'den, tümü Dosya 2'den. Ve güzel olan şey, temeldeki verilerin değişmesi,sadece sorguyu yenileyebilirsiniz.
Çalışma kitabını bugünün videosundan indirmek için YouTube açıklamasındaki URL'yi ziyaret edin.
David gibi seminerime gelmesini istiyorum, uğradığınız için teşekkür etmek istiyorum. Bir dahaki sefere başka bir internet yayını için görüşürüz.
Excel Dosyasını İndirin
Excel dosyasını indirmek için: common-on-common-column.xlsx tabanlı birleştirme
Power Query, Excel'de harika bir araçtır.
Excel Günün Düşüncesi
Excel Master arkadaşlarıma Excel hakkında tavsiyelerini sordum. Bugünün düşüncesi:
"Bir işlevde aralık veya matris okuduğunuzda her zaman F4'e basın"
Tanja Kuhn
