Nashville'den Kaley bir bilet hesap tablosu üzerinde çalışıyor. Her etkinlik için bir bilet planı seçer. Bu bilet planı, etkinlik için 4 ila 16 bilet türü arasında herhangi bir yeri belirtebilir. Kaley, arama tablosuna gidip * tüm * eşleşmeleri döndürecek, uygun şekilde yeni satırlar ekleyecek bir formül istiyor.
Bunu çözebilecek bir DÜŞEYARA sahip olmasam da, Excel 2016'da yerleşik olarak bulunan yeni Power Query araçları bunu çözebilir.
Not
Excel 2010 veya Excel 2013'ün Windows sürümüne sahipseniz, Power Query'yi Microsoft'tan ücretsiz olarak indirebilirsiniz. Maalesef Power Query henüz Android için Excel, iOS için Excel veya Mac için Excel için mevcut değildir.
Hedefi açıklamak için: Mike McCann ve Mekanik, Allen Tiyatrosu'nda C bilet planıyla görünüyor. Arama tablosunda eşleşen dört sıra olduğundan Kaley, Mike McCann ve Mekanik yazan ve her biri farklı bir eşleşme içeren dört sıra istiyor. arama tablosu.
Orijinal tablodan bir hücre seçin. Bu verileri tablo olarak işaretlemek için Ctrl + T tuşlarına basın. Tablo Araçları sekmesinde, tabloyu Tablo1'den Şovlar olarak yeniden adlandırın. Arama tablosu için bunu tekrarlayarak Biletler olarak adlandırın.
Şovlar tablosunda bir hücre seçin. Veri sekmesinden, Tablodan / Aralıktan'ı seçin.
Power Query düzenleyicisi açıldıktan sonra, Kapat ve Yükle açılır menüsünü açın ve Kapat ve Şuraya Yükle… 'yi seçin.
Verileri İçe Aktar iletişim kutusunda Yalnızca Bağlantı Oluştur'u seçin.
Biletler tablosuna gidin. Yalnızca Biletlere Bağlantı Oluşturma adımlarını tekrarlayın. Sorgular bölmesinde her iki bağlantıyı da görmelisiniz:
Herhangi bir boş hücre seçin. Veri Seçin, Veri Alın, Sorguları Birleştirin, Birleştirin.
Birleştir iletişim kutusunda altı adım vardır. 3. ve 4. bölüm bana sezgisel görünmüyor.
- En üstteki açılır menüden Şovları seçin
- İkinci açılır menüden Biletler'i seçin.
- Gösteriler tablosunda yabancı anahtar olarak bu sütunu seçmek için en üstteki Bilet Planı başlığına tıklayın.
- Arama tablosunda anahtar alan olarak bu sütunu seçmek için alttaki Bilet Planı başlığına tıklayın.
- Birleştirme türünü açın ve İç öğesini seçin (yalnızca eşleşen satırlar).
- Tamam'ı tıklayın
Sonuçlar başlangıçta hayal kırıklığı yaratıyor. Tablo 1'deki tüm alanları ve Tablo, Tablo, Tablo yazan bir sütunu görüyorsunuz.
Biletler sütununun üst kısmındaki Genişlet simgesini tıklayın.
Bu alana zaten sahip olduğunuz için Bilet Planı'nın seçimini kaldırın. Orijinal Adı Önek olarak Kullan seçeneğinin işaretini kaldırmazsanız, kalan alan Biletler. Bilet Türü olarak adlandırılacaktır.
Başarılı! Her gösterinin her satırı patlayarak birden çok satıra bölünür.
Verilerin sıralanmasından özellikle memnun değilim. Tarihe Göre Sıralama, Bilet Türlerinin garip bir şekilde sıralanmasına neden olur.
Video izle
Bugünkü durumda video, yazı yazıldıktan sonra çekildi. Sıralama düzenini kontrol etmek için Bilet Türlerine bir sıra sütunu eklemenizi öneririm.
Video Metni
Podcast'ten Excel Öğrenin, Bölüm 2204: Tüm DÜŞEYARA'ları Döndür.
Hey, İnternet'e tekrar hoş geldiniz, ben Bill Jelen. Nashville Music City'den bugünün sorusu. Nashville'deydim, birisi biletleme sistemine bilet yüklemeyi planlamaktan sorumlu ve işte elimizde: Etkinliklerin bir listemiz var - yaklaşan etkinlikler - tarihimiz, mekânımız ve bilet planımız var. Yani, Sarayda bir şey yapılsa bile, farklı bilet planları olabilir - mesela, belki zemin yapılandırılmış, bilirsiniz, koltuklarla veya belki de sadece ayakta duran bir oda, değil mi?
Yani, ne tür bilet planına bağlı olarak, buraya Arama tablosuna gelmeli ve eşleşen tüm olayları bulmalısınız ve esasen DÜŞEYARA patlaması dediğim şeyi yapacağız. Yani, Hannah C'de bir şey varsa, Hannah C'ye gidecekler ve Hannah C'de - 1, 2, 3, 4, 5, 6-- 7 öğe varsa, bizde yedi satır döndürmek için - bu, altı satır daha eklemeniz ve bu verileri kopyalamanız gerektiği anlamına gelir. Peki.
Şimdi, bunu bir DÜŞEYARA ile yapmayacağız, ancak kavramı anladınız - bir DÜŞEYARA yapıyoruz ve tüm yanıtları yeni satırlar olarak döndürüyoruz. Pekala, bu iki tabloyu da alıp Ctrl + T ile gerçek bir tablo haline getireceğim. Bunlardan ilki Tablo 1 - korkunç isim, hadi buna Olaylar ya da Şovlar diyelim, buna Şov diyelim, şöyle - ve ikincisi, şimdi, hey, işte öğrendiklerim çünkü bunu pratik yaptım - sahip olmalıyız burada bir sıra alanı. Yani = SATIR (A1), çift tıklayın ve bunu aşağıya kopyalayın ve ardından özel değerleri kopyalayıp yapıştırın. Peki. Şimdi bunu bir tabloya dönüştüreceğiz - Ctrl + T ve buna Biletler diyeceğiz.
Peki. Gösterilerimiz var, biletlerimiz var. Veri sekmesine gideceğim ve burada şov olayındayım, verilerimi bir Tablodan veya Aralıktan almak istediğimi söylemek istiyorum - bu arada Power Query. Excel 2010 veya 2013'e geri döndüyseniz, bunu Microsoft'tan ücretsiz olarak indirebilir, Power Query aracını indirebilirsiniz. Mac veya iOS veya Android kullanıyorsanız, üzgünüz, sizin için Power Query yok. Pekala, bir Masa veya Seriden… Windows bilgisayarı olan bir arkadaşını bul ve bunu kurmasını sağlayan birini bul. Peki. İşte bir tablo, buna bir şey yapmayacağız, sadece Kapat ve Yükle, Kapat ve Yükle ve ardından "Sadece Bağlantı Oluştur" deyin, mükemmel. Buraya ikinci masamıza geleceğiz: Verileri Al, Bir Tablodan veya Aralıktan, buna hiçbir şey yapmıyoruz, Kapat ve Yükle,Kapat ve Yükle, "Yalnızca Bağlantı Oluştur", Tamam. Yani şimdi sahip olduğumuz şey, ilk tabloyla bir bağlantımız ve ikinci tabloyla bir bağlantımız var. Bu ikisini birleştirmeyeceğiz, ki bu aslında DÜŞEYARA veya bir Veritabanı Eklemi yapmaya benziyor, sanırım, gerçekten de öyle. Sorguları Birleştir, Birleştireceğiz. Peki.
Şimdi, bu iletişim kutusunda yapmanız gereken yedi şey - ve bu biraz kafa karıştırıcı - ilk tablo olarak Şovlar'ı seçeceğiz; ikinci masa olarak Biletleri seçin; ortak hangi alana sahip olduklarını seçin ve bu birden çok alan olabilir - kontrol tuşuna basarak tıklayabilirsiniz - ancak bu durumda yalnızca bir bilet planı vardır; ve ardından Bilet Planı; ve sonra Birleştirme türünü "yalnızca eşleşen satırlarla" İç birleşim olarak değiştireceğiz. Peki. Şimdi, Tamam'ı tıklıyorsunuz ve tüm probleminizin çözüleceğini düşünüyorsunuz, ama sadece ezildiniz çünkü işte A'dan gelen tüm veriler - hiç yeni satır eklemediler - ve burada, sadece Sadece Masa, Masa, Masa, hah olan Biletler adında sıkıcı, aptal bir alan.
Ama neyse ki, bunun en üstünde bir Genişlet simgesi var ve bunu genişleteceğiz - Bir plan yapmam gerekmiyor, zaten buna sahibim - Bilet Türü ve Sırası. Bunun Power Query'nin yapmak istediği gibi Tickets.TicketType olarak adlandırılmasını istemiyorum - bu yüzden bu kutunun işaretini kaldırıyorum. Peki. Şu anda 17 satır verimiz var; Tamam'ı tıkladığımda, BAM! Patlama var. Yani, Michael Seeley ve Starlighter'lar bunun gibi tüm farklı bilet türleriyle karşımıza çıkıyor. Pekala, bu bilet türlerinin sırayla göründüğünü görün, bu harika. Ancak Michael Seeley bir sonraki gösteri değil, bir sonraki gösteri 5 Haziran'da. Bu yüzden bunu Tarihe göre sıralamayı denediğimde - bu beni çıldırtıyor, bunu açıklayamam. Tarihe göre sırala ve Mike Man and the Mechanics 65'e kadar çıkıyor, ama sonra tüm biletler berbat. Onlar'yanlış sıradayız ve bu yüzden bu diziyi yapmak zorunda kaldım-- öyle hissettiriyor. Sıraya göre sıralayabilirim. Şimdi, 6, 5, güzel ve sonra bunun içinde Biletler doğru. Ve aslında, bu noktada artık bu sütuna ihtiyacımız yok. Böylece sağ tıklayıp kaldırabilir ve ardından Kapat ve Yükle - bu sefer aslında Kapat ve Yükle, Kapat ve Yükle değil - ve sonucumuz var. Peki.
Bu yüzden, olaylar listesinden bu büyük listeye gittik, ama işte harika kısım: Bunu batırdım, Mike Man ve Mekanik Saray B, Saray C değil. Bu yüzden sağ üstteki aslına geri dönüyorum - kitap hakkında daha fazla bilgi için el köşesi.
Peki. Bu Bölümdeki Konular: Nashville'deki Kaley'in, tüm eşleşmeleri döndürmek için genellikle yeni satırlar ekleyerek bir DÜŞEYARA yapması gerekir. Ve bu bir biletleme veritabanı, tamam mı? Bu yüzden buna DÜŞEYARA Patlaması diyeceğim çünkü her gösteri 16 satıra kadar patlayacak. Bunu çözmek için Power Query kullanacağız ve bilet türüne bir Sıra alanı eklemediğimiz sürece Tarihin yanlış Sırada görüneceğini öğrendim. Ctrl + T ile her iki seti de Tablo haline getirin; Bunları Gösteriler ve Biletler olarak adlandırın; ve sonra her tablodan Veri Al, Tablodan, Kapat ve Yükle, Yalnızca bir bağlantı oluşturmak için; diğer tablo için tekrarlayın; sonra Veri, Veri Al, Sorguları Birleştirme, Birleştirme; ve sonra bu iletişim kutusu, benim için oldukça kafa karıştırıcı - Etkinlikler'i seçin, Biletler'i seçin, her ikisinde de Bilet Türünü tıklayın, bağlantıyı bir iç birleşimle değiştirin,Tamam'a tıklayın ve sonra o korkunç hayal kırıklığı yaratan sonucu elde edersiniz, burada sadece Tablo, Tablo, Tablo, Tablo yazan bir sütun; bunun üst kısmındaki Genişlet simgesine tıklayın; Bilet Sırası alanını seçin; tablonun adını önek olarak kullanmayın; ve Tarihe Göre Sıralayabilir, Sıraya Göre Sıralayabilirsiniz; Kapatın ve elektronik tabloya yükleyin. İşin güzel yanı, temeldeki veriler değişirse - sadece Yenileyin ve sonuçlarınızı alın.
Şimdi, hey, bugünün videosundan kullanılan çalışma kitabını indirmek için, YouTube açıklamasında aşağıdaki URL'yi ziyaret edin. Ayrıca yaklaşan seminerlerin bir listesi de var - Sizi canlı Power Excel seminerlerimden birinde görmek isterim.
Nashville'e gelip bana bu harika soruyu verdiği için Kaley'e teşekkür etmek istiyorum. Uğradığın için seni 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: return-all-vlookups.xlsx
Power Query beni şaşırtmaya devam ediyor. Bu, yanıtın Power Query olduğu üç günlük bir dizinin ikincisidir:
- Salı: Tarih / Saat sütununu yalnızca tarihe dönüştürün
- Bugün: Tüm DÜŞEYARA'ları Döndür
- Perşembe: 1100 öğenin Her biri için bir Anket Oluşturun
Power Query ile çözdüğüm şeylerden oluşan eksiksiz bir YouTube Oynatma Listem var.
Excel Günün Düşüncesi
Excel Master arkadaşlarıma Excel hakkında tavsiyelerini sordum. Bugünün düşüncesi:
"Şüphe duyduğunuzda, ROUND Fonksiyonunu kullanın!"
Mike Girvin
