Bu, itfaiyecinin bütçe sorunudur. Bir itfaiye binasındaki insanlar Excel'de bütçelerini yanlış yapıyorlar. Harika bir Power Query dönüşümü çözümü sağlar.
Video izle
- Steve bir metin sütununa girilen sayıları toplamalı
- Her hücrede, alt = "" + Enter ile ayrılmış birden çok satır vardır
- Bu satırları satırlara ayırmanız, ardından dolar tutarını her hücrenin ortasından ayrıştırmanız gerekir
- Maliyet Merkezine göre özetleyin
- Bir arama tablosu oluşturun
- Boş satırdaki hataları yok saymak için IFNA'yı kullanarak arama tablosundan toplamları alın
- Bonus: Bir hücreyi değiştirdiklerinde çalışma sayfasını güncellemek için bir Etkinlik makrosu ekleyin.
Video Metni
Podcast Bölüm 2160: Alt + Girilen SUM Verilerinden Excel Öğrenin.
Hey. İnternet yayınına tekrar hoş geldiniz. Ben Bill Jelen. Bunu ben uydurmuyorum. Buna benzeyen verilere - bütçe verilerine - sahip olan birinden bir sorum var. Şimdi, buraya sahte kelimeler koyuyorum, böylece bütçe bilgilerine sahip değiliz ama muhasebe departmanına yeni gelen kişi bir şirkete gitti ve bu şirket yıllardır bütçesini böyle yapıyor. Bütçeyi yapan muhasebeci değiller, sıraya girmiş insanlar, ama bunu böyle yapıyorlar ve onları değiştiremiyor. Yani, işte amacımız. Bunun bütçeyi Word'e yazmak kadar kötü olduğunu söylüyor.
Neredeyse, ama neyse ki, güç sorgusu sayesinde sorunumuzu kurtaracak. İşte amacımız. Buradaki her MALİYET MERKEZİ için, tüm bu sayıların toplamını rapor etmek istiyoruz. Yani, gider adı var, a -, rutin olarak a -, sonra bir $ işareti ve sonra, sırf hayatı ilginç kılmak için, arada bir, rastgele bir not; her zaman değil, sadece bazen. Her biri arasında boş satır. Tonlarca ve tonlarca veri.
İşte yapacağım şey şu. En aşağıya, en son hücreye ineceğim, başlıklar dahil tüm bu şeyleri seçeceğim. Bir NAME oluşturacağım. Ona MyData adını vereceğim. MyData, bunun gibi, tamam mı? Peki. Şimdi, 2016 ve 2016 Office 365'te yerleşik olarak 2010 veya 2013'te ücretsiz olan güç sorgusunu kullanacağız. Bu, bir TABLO VEYA ARALIK'tan gelecek. Peki. İlk olarak, SÜTUN A'da bu boşluklara sahip olduğumuzda, kurtulmak istediğimiz tüm BOŞLAR. Bu yüzden NULL'un işaretini kaldıracağım. Harika. Tamam. Gerçekten, bu verilerde, verilerin bu sürümünde, bir DÜŞEYARA oluşturacağım için bu sütuna ihtiyacımız yok. Bu yüzden, sağ tıklayıp o sütundan kurtulacağım, bu yüzden sütunu KALDIR.
Peki. Şimdi, işte bu korkunç sihrin gerçekleşeceği yer. Bu sütunu, BİR DELIMITER TARAFINDAN BÖLÜNEN SÜTUNU seçin ve kesinlikle GELİŞMİŞ bölümüne geçeceğiz. Sınırlayıcı özel bir karakter olacak ve sınırlayıcının her geçtiği yeri böleceğiz. Yani, burada, sanırım bunu zaten çözdüler çünkü ben genişlettim, ama size göstereceğim. ÖZEL KARAKTER EKLEYİN. Bunun bir ÇİZGİ BESLEME olduğunu söyleyeceğim, pekala, bu yüzden, ÇİZGİ BESLEME'nin her oluşumunda ve ben SATIRLARA AYIRILACAĞIM. Pekala, burada ne olacak, 1, 2, 3, 4, 5, 5 satır alacağım ya da 1001 diyeceğim, ama her satırda farklı olacak bu hücreden satır. Bu harika. 1, 2, 3, 4, 5, 1001 vardır. Pekala. Şimdi bu kötü çocuğu çözümlememiz gerekiyor. Peki,bu nedenle, SÜTUNU DELIMITER TARAFINDAN BÖLÜNE ALIN. Bu sefer, bir sınırlayıcı bir $ işareti olacak. Bu mükemmel, bir kez, bulduğumuz ilk $ işaretinde, sadece gelecekte orada bir $ işareti olması durumunda. KOLONLARA AYIRACAĞIZ. Tamam'ı tıklayın. Peki. Yani detaylar var. İşte paramız.
Şimdi, bunu SPACE'de paylaşacağım. Öyleyse, bu sütunu seçin, BİR DELIMITER TARAFINDAN AYIRILMIŞ SÜTUNU ve sınırlayıcı bir BOŞLUK olacaktır, evet, bir kez SOL-EN SİLİNCİ'de Tamam'ı tıklayın ve orada bu yorumlara ihtiyacım yok, bu yüzden bu yorumları biz KALDIR'a gidiyoruz. Aslında, buna da ihtiyacım yok çünkü sadece tüm bunları toplamaya çalışıyorum, bu yüzden KALDIRACAĞIM.
Şimdi dönüştürün. MALİYET MERKEZİNE GÖRE GRUP, YENİ SÜTUN ADI TOTAL olarak adlandırılacak, OPERASYON TOPLA olacak ve hangi sütuna TOPLA gideceğiz? DETAYLAR 2.1. Güzel. Tamam'ı tıklayın, tamam. Elde ettiğimiz sonuç, tüm bu satır öğelerinin TOPLAMI ile birlikte MALİYET MERKEZİ başına bir satırdır. HOME, CLOSE & LOAD. Muhtemelen yeni bir çalışma sayfası ekleyecek. Umarım yeni bir çalışma sayfası ekler ve ekler ve bu çalışma sayfasına MYDATA_1 denir. MYDATA_1.
Peki. Şimdi buraya orijinal verilere geri döneceğiz ve bu adımları uygulayacağız. İlkinde, = 1001'in sonuçlarımıza DÜŞEYARA. Bu, döngüsel bir referans oluşturmak gibi bir şey, ancak bize döngüsel bir referans vermeyecek. , 2, YANLIŞ. Tam eşleşmesini istiyorum. Pekala, ama boş hücreler için bunu yapmak istemeyeceğiz. Öyleyse, şunu söyleyeceğim, aslında, bunu tamamen kopyalayalım. CONTROL + C, sadece ne aldığımızı görmek için sonuna kadar gidin. Belki N / As alıyoruz ve IFNA ile bundan kurtulabilirim. Evet, güzel, pekala. Öyleyse, N / As'lardan kurtulalım. Eğer N / A ise, biz sadece "" istiyoruz. Orada hiçbir şey istemiyoruz. CONTROL + ENTER. Peki. Şimdi, bu TOTAL olmalıdır. Bakalım kısa bir tane bulabilecek miyiz ve sadece hesabı yapalım. = 627,37 + 7264,25 + 6066.01 + 4010.66 + 9773.94 ve TOPLAM, 27742.23 budur. Müthiş heyecanlanıyorum. (= IFNA (DÜŞEYARA (A2, Verim_1,2, YANLIŞ), ""))
Şimdi, anlaşma şu. Öyleyse, burada bir şeyler değiştiren o sıralı insanlar var, pekala, diyelim ki geçerler ve bütçeyi değiştirirler, 40294.48, ve buraya gelip bunu 6000 olarak değiştirirler, ve eklerler yeni bir tane, ALT + ENTER, SOMETHING - $ işareti, 1000 $ eklendi. Peki. Şimdi, tabii ki, ENTER tuşuna bastığımda, bu numara, 40294.48 güncellenmeyecek, pekala, ama yapmamız gereken şey DATA sekmesine gitmek ve TÜMÜNÜ YENİLEME istiyoruz. Yani, 40294.48. İzle, izle, izle, izle. HEPSİNİ YENİLE. Acayip harika.
Güç sorgusunu seviyorum. Güçlü sorgu en şaşırtıcı şeydir. Esasen tıpkı bir hücredeki kelime verileri gibi olan bu veriler, şimdi güncelleniyor. Muhtemelen, COLUMN C'de bir kişi her değişiklik yaptığında, devam edip makroyu kullanarak TÜMÜNÜ YENİLEME seçeneğine tıkladığımızı ve sadece bu sonuçları sürekli, sürekli yenilenen alacağımızı söyleyen bir çeşit makro bile yapabilirsiniz.
Ne kadar korkunç bir soru gönderildi. Bununla uğraşmak zorunda kalan Steve için üzülüyorum, ancak şimdi Office 365'te güç sorgusunu kullanarak veya 2010 veya 2013 için indirdiğiniz, bunu çözmenin çok ama çok kolay bir yolu var.
Bekle. Tamam, bir zeyilname: hadi daha da iyi hale getirelim. Bu sayfaya VERİ adı verilir ve çalışma kitabını makro etkin olarak kaydettim, yani xlsm. Xlsx iseniz, xlsm olarak kaydetmeyi atlamayın. ALT + F11. DATA adlı çalışma kitabını bulun, çift tıklayın, sol üst, ÇALIŞMA SAYFASI ve ardından çalışma sayfasını her değiştirdiğimizde DEĞİŞTİR ve AKTİVEVİ KİTABI.REFRESHALL diyeceğiz ve sonra kapat, tamam ve şimdi deneyelim. Bir şeyler düzenleyelim. Yani, şu anda 8.000 olan ahududuları alacağız ve onu 1000 olarak değiştireceğiz, yani 7000 azaltacağız. ENTER tuşuna bastığımda, 42.000'in 35.000'e düştüğünü görmek istiyorum. Ah. Harika.
Peki, merhaba. Burası genellikle kitabımı almanız için size yalvarıyorum ama bugün sizden arkadaşlarımın kitabını satın almanızı isteyeceğim - Ken Puls ve Miguel Escobar - M (VERİ) MAYMUN içindir. Güç sorgusu hakkında öğrendiğim her şeyi bu kitaptan öğrendim. Harika bir kitap. Kontrol et.
Bölüm özeti: Steve, bir metin sütununa girilen toplamı gereken sayılara sahiptir; her hücrede ALT + ENTER ile ayrılmış birden çok satır; bu satırları satırlara ayırmanız, ardından dolar tutarını her hücrenin ortasından ayrıştırmanız gerekir; COST CENTER ile özetleyin; bir arama tablosu oluşturun; boş satırdaki hataları yok saymak için IFNA'yı kullanarak arama tablosundan toplamları alın; ve sonra, sonunda bir bonus, makro, bir hücreyi değiştirdiklerinde çalışma sayfasını güncellemek için bir olay makrosu.
Steve'e bu soruyu gönderdiği için teşekkür etmek istiyorum ve bir cevabım olduğu için çok mutluyum - güç sorgusundan önce, gerçekten çok zor olurdu - ve 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.
Dosyayı indir
Örnek dosyayı buradan indirin: Podcast2160.xlsm
