Atlanta Power Excel seminerimde olan Quentin'den bugünün sorusu. Quentin, Excel'deki 1000'den fazla müşterinin her biri için aynı 7 anket sorusunu oluşturmalıdır.
Bu şekilde görebileceğiniz gibi müşteriler A'da. Tekrarlanacak sorular D sütununda.
Bunu VBA veya formüllerle çözebilirsiniz, ancak bu Power Query haftası, bu yüzden Power Query'de harika bir numara kullanacağım.
Her anket arasında boş bir satır olmasını istiyorsanız, bir sıra numarası ekleyin ve son sorudan sonra 7 rakamını ekleyin.
Her iki veri kümesinden Ctrl + T'ye basın. İkinci veri kümesine, Sorular veya Anket gibi hatırlayabileceğiniz bir ad verin.
İkinci veri kümesinden, Tablodan Veri'yi kullanın.
Power Query düzenleyicisi açılır. Ana Sayfa sekmesinden Kapat ve Yükle açılır menüsünü seçin ve Kapat ve Şuraya Yükle… öğesini seçin. Sonraki iletişim kutusunda, Yalnızca Bağlantı Oluştur'u seçin.
Artık Excel'e geri döndünüz. Müşteri tablosundaki A sütunundaki herhangi bir hücreyi seçin. Veriler, Tablodan. Sorgu Düzenleyicisi açıldığında, Şeritteki Sütun Ekle sekmesine tıklayın ve ardından Özel Sütun'u seçin. Formül =#"Questions"(# ve tırnak işaretleri dahil).
Düzenleyicide her satırda Tablo değeri yinelenen yeni bir sütun belirir. Sütun başlığındaki Genişlet simgesini tıklayın.
Tablodaki her iki alanı da seçin. Giriş sekmesinden Kapat ve Yükle'yi seçin.
1000'den fazla müşterinin her biri için tekrarlanan 7 sorudan oluşan yeni bir çalışma sayfası görünecektir.
Video izle
Video Metni
Podcast Bölüm 2205'ten Excel Öğrenin: Kredi Anketi Patlaması.
İnternet yayınına tekrar hoş geldiniz, ben Bill Jelen Şimdi, daha dün 2204 bölümünde Nashville'den Kaylee bir DÜŞEYARA patlaması yapmak zorunda kaldı - burada D sütunundaki her öğe için, G sütununda eşleşen bir grup öğe vardı ve bunları patlatmamız gerekiyordu. Yani, Saray C'de 8 öğe olsaydı, 8 sıra alırdık.
Şimdi, bugün Quentin var. Quentin Atlanta'daki seminerimdeydi, ama o aslında Florida'dan ve Quentin'in burada yaklaşık 1000 müşterisi var - yani, 1000'den fazla müşteri - A sütununda ve her müşteri için bu anketi oluşturması gerekiyor. - 1, 2, 3, 4, 5, 6 soruluk bu anket. Ve burada yapacağım şey, sadece 1'den 7'ye kadar sayılarla bir Sıra numarası ekleyeceğim, böylece bu şekilde, arada güzel bir boş satır oluşturabilirim. Bu iki veri setini de bir tablo haline getireceğim; bu nedenle, bu 1000 müşterinin her biri için bu 7 satırı patlatmaya çalışıyoruz. Amaç bu.
Şimdi bunu VPA ile yapabilirim; Bunu formüllerle yapabilirim; ama burada bir tür "Power Query Week", bunun peşindeyiz, arka arkaya üçüncü Power Query örneğimiz, bu yüzden Power Query'yi kullanacağım. Bunu bir masaya bırakacağım. Bunu Tablo 1 olarak adlandırmaya çok dikkat edeceğim. Ona bir isim vereceğim. Bu adı daha sonra tekrar kullanmamız gerekecek, bu yüzden ona Sorular diyeceğim-- bunun gibi. Ve sonra bu Tablo 2 olacak, ama bunu Müşteriler olarak yeniden adlandıracağım - bunu yeniden adlandırmam o kadar da önemli değil çünkü adı olması gereken ikincisidir. Yani, bunu seçeceğiz; Veri; ve Tablodan / Aralıktan diyeceğiz. Verileri Al ve Dönüştür - bu, Power Query olarak bilinir. Excel 2016'da yerleşiktir. 2010 veya 2013'e sahipseniz, Windows'ta,Mac değil, iOS veya Android değil, Power Query'yi Microsoft'tan ücretsiz olarak indirebilirsiniz.
Yani, Tablo / Aralıktan veri alacağız; işte masamız - ona hiçbir şey yapmayacağız, sadece Kapat ve Yükle; Kapat & Yükle; yalnızca Bir Bağlantı Oluşturun; peki ve bakın, bu Sorgunun adı Sorular. Buradaki ile aynı adı kullanıyor. Ve sonra buna geri dönüyoruz ve Data; Tablodan / Aralıktan; 1000 veya daha fazla müşterimizin bir listesi var.
Hey şimdi, işte M Is For (DATA) MONKEY'in ortak yazarı olan arkadaşım Miguel Escobar'a bir ses. Videoda buna bir bağlantı koyacağım - Power Query hakkında harika bir kitap - bu konuda bana yardımcı oldu. Yepyeni bir Özel Sütun ekleyeceğiz ve Özel Sütun formülü tam burada: = # "sorgunun adı". Miguel olmasaydı bunu asla anlayamazdım, bu yüzden Miguel'e teşekkürler.
Ve Tamam'a tıkladığımda, evet, işe yaramış gibi görünmüyor - sadece masa, masa, masa alıyoruz, ama aynen dün Kaylee ve bilet işlemlerinde yaptığımız şey buydu. Ve tek yapmam gereken bunu genişletmek ve aslında muhtemelen Sekansa ihtiyacım olmadığını söyleyeceğim… peki, her ihtimale karşı koyalım. Onu gördükten sonra çıkarabiliriz. Şu anda 1000 sıramız var ve şimdi 7000 sıramız var - çok güzel. Şimdi onun Sırada göründüğünü görebiliyorum, bu yüzden buna ihtiyacım yok. Sağ tıklayıp sadece bir sütunu kaldıracağım. Ve sonra eve dönebilirim; Kapat & Yükle; ve BAM! - şimdi 6 sorudan oluşan 7000'den fazla satırımız ve her müşteri için bir boş alanımız olmalı. Quentin, seminerde bundan çok etkilendi. Harika, havalı numara - VBA'dan kaçınır, Index'i kullanarak bir sürü formülden kaçınır,ve bunun gibi şeyler - gitmek için harika bir yol.
Ama, hey, bugün seni M Is For (DATA) MONKEY ile göndermeme izin ver. Ken Puls ve Miguel Escobar, Power Query hakkında en büyük kitabı yazdı. O kitabı seviyorum; 2 saat içinde o kitapla profesyonel olacaksın.
Pekala, bugün toparlayın - Quentin'in 1000 farklı müşteri için aynı anketi oluşturması gerekiyor. Her müşteri için 6 veya 7 veya 8 soru vardır. Şimdi, bunu VBA veya makro ile yapabiliriz, ancak burada Power Query çalıştırdığımız için, bir Power Query yapalım. Sorulara fazladan boş bir soru ekledim; Boşluğun orada kalmasını sağlamak için bir Sıra Numarası ekledim; müşterileri bir masaya dönüştürmek; soruları bir tablo haline getirin; Sorulara hatırlayabileceğiniz bir ad vermeniz gerçekten önemli - Benimkine "Sorular" adını verdim. Power Query'ye soruları Yalnızca Bağlantı Olarak ekleyin; ve ardından, müşterileri Power Query'ye eklerken, formülün olduğu yeni bir özel sütun oluşturun: # "ilk sorgunun adı" ve ardından Power Query düzenleyicisinde bu sütunu genişletin; Kapat &Elektronik tabloya geri yükleyin ve bitirdiniz. İnanılmaz bir numara - Power Query'yi seviyorum - 20 yılda Excel'in başına gelen en büyük şey.
Seminerime gösterdiği için Quentin'e teşekkür etmek istiyorum. Daha önce birkaç kez seminerime gelmişti - harika adam. Uğradığın için teşekkür ederim. 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: kredi-anket-patlama.xlsx
Power Query beni şaşırtmaya devam ediyor. Daha fazla Power Query öğrenmek için Data Monkey için M kitabına göz atın.
Excel Günün Düşüncesi
Excel Master arkadaşlarıma Excel hakkında tavsiyelerini sordum. Bugünün düşüncesi:
"AGGREGATE ile anlamak dışında her şeyi yapabilirsiniz."
Liam Bastick
