Bill, gereksiz Excel verileri hakkında bu haftaki soruyu sordu.
Excel'de aylık bir işlem listesi oluşturuyorum. Ayın sonunda gereksiz verileri ortadan kaldırmam ve hesap koduna göre toplamı bulmam gerekiyor. Her hesap kodu birden çok kez ortaya çıkabilir. Bill daha sonra, toplamları elde etmek için CSE formülleri matrisini kullanma planlarıyla birlikte benzersiz bir hesap kodları listesi oluşturmak için aşağıdaki yöntem 1'e benzer olan mevcut Excel metodolojisini açıkladı. Her hesap için toplamları olan benzersiz bir hesap kodları listesine ulaşmanın daha kolay bir yolu var mı?
Bu mükemmel bir tatil sorusu. 15 yıldır Lotus kullanıcısı olarak, Bill'in yöntemini Lotus'un 2.1 sürümünün eski güzel günlerinden "hızlı ve kirli" veri işleme için klasik yöntem olarak kabul ediyorum. Bu, kutsamalarımızı saymak için bir mevsim. Bu soruyu düşündüğünüzde, Microsoft'taki çalışanların yıllar boyunca bize gerçekten çok sayıda araç verdiğini fark ediyorsunuz. Excel 97 kullanıyorsanız, bu görevi gerçekleştirmek için en az beş yöntem vardır ve bunların tümü Bill tarafından açıklanan klasik yöntemden çok daha kolaydır. Bu hafta beş yöntemle ilgili bir eğitim sunacağım.
Basitleştirilmiş veri kümemin A sütununda hesap numaraları ve B sütununda miktarları var. Veriler A2: B100'den hareket ediyor. Başta sıralanmamış.
Yöntem 1
Cevabı bulmak için, Özel Değerleri Yapıştır ile birlikte yaratıcı If ifadelerini kullanın.
Excel tarafından sunulan daha yeni araçlar göz önüne alındığında, artık bu yöntemi önermiyorum. Daha iyi şeyler ortaya çıkmadan önce bunu çok kullanırdım ve hala yararlı olduğu durumlar var. Bunun için alternatif adım "The-Lotus-123-Sen-Olmadığın-Ruh Halinde-Kullanılmayan- @ DSUM" yöntemidir. İşte adımlar.
- Verileri A sütununa göre sıralayın.
- C sütununda, hesaba göre değişen bir toplam tutacak bir formül icat edin. C2 hücresi
=IF(A2=A1,C1+B2,B2). - D'de belirli bir hesap için son girişi belirleyecek bir formül icat edin. D2 hücresi
=IF(A2=A3,FALSE,TRUE). - C2: D2'yi tüm satırlarınıza kopyalayın.
- C2: D100 kopyalayın. Formülleri değerlere dönüştürmek için Edit - PasteSpecial - Values'ı tekrar C2: D100 üzerine yapın.
- Azalan D sütununa göre sırala.
- D sütununda DOĞRU olan satırlar için, A'da benzersiz bir hesap numaraları listesine ve C'de son hareketli toplama sahipsiniz.
Artıları: Hızlıdır. İhtiyacınız olan tek şey, IF ifadeleri yazmak için keskin bir duygudur.
Eksileri: Daha iyi yollar var.
Yöntem 2
Benzersiz hesapların listesini almak için Veri Filtresi - Gelişmiş Filtre'yi kullanın.
Bill'in sorusu, toplamları almak için CSE Formüllerini kullanabilmesi için gerçekten benzersiz bir hesap numarası listesinin nasıl alınacağıydı. Bu, benzersiz hesap numaralarının bir listesini almak için bir yöntemdir.
- A1'i vurgulayın: A100
- Menüden Veri, Filtre, Gelişmiş Filtre'yi seçin
- "Başka bir konuma kopyala" radyo düğmesini tıklayın.
- "Yalnızca Benzersiz Kayıtlar" onay kutusunu tıklayın.
- Benzersiz listenin görünmesini istediğiniz çalışma sayfasının boş bir bölümünü seçin. Bunu "Kopyala:" alanına girin. ("Başka bir konuma kopyala" seçeneğini belirleyene kadar bu alanın gri renkte olduğunu unutmayın.
- Tamam'ı tıklayın. Benzersiz hesap numaraları F1'de görünecektir.
- Sonuçlarınızı almak için herhangi bir alt hat işlemlerini, dizi formüllerini vb. Girin.
Artıları: 1. Yöntemden daha hızlıdır. Sıralama gerekmez.
Eksileri: Bundan sonra gerekli olan CSE formülleri başınızı döndürür.
Yöntem 3
Data Consolidate kullanın.
Excel, Data Consolidate'i sunduğunda yaşam kalitem arttı. Bu BÜYÜK oldu! Kurmak 30 saniye sürüyor, ancak DSUM'lar ve diğer yöntemler için ölüm anlamına geliyordu. Hesap numaranızın, toplamak istediğiniz sayısal alanların solunda olması gerekir. Her sütunun üzerinde başlıklara sahip olmalısınız. Sol sütundaki hesap numaralarını ve üst kısımdaki başlıkları içeren dikdörtgen hücre bloğuna bir aralık adı atamanız gerekir. Bu durumda, bu aralık A1: B100'dür.
- A1'i vurgulayın: B100
- Ad kutusuna (formül çubuğunun solunda) tıklayarak ve "TotalMe" gibi bir ad yazarak bu alana bir aralık adı atayın. (Alternatif olarak, Ekle - Ad'ı kullanın).
- Hücre işaretçisini çalışma sayfasının boş bir bölümüne koyun.
- Veri Seçin - Birleştirin
- Başvuru alanına aralık adını (ToplamMe) yazın.
- Etiketleri Kullan bölümünde, hem Üst Satır hem de Sol Sütun'u işaretleyin.
- Tamam'ı tıklayın
Artıları: Bu benim en sevdiğim yöntem. Sıralama gerekmez. Kısayol alt-D N (çalma adı) alt-T alt-L enter. Kolayca ölçeklenebilir. Aralığınız 12 aylık sütun içeriyorsa, cevabın her ay için toplamları olacaktır.
Eksileri: Aynı sayfada başka bir Veri Birleştirme işlemi yaparsanız, Sil düğmesini kullanarak Tüm Referanslar alanından eski aralık adını temizlemeniz gerekir. Hesap numarası, sayısal verilerinizin solunda olmalıdır. Pivot tablolardan biraz daha yavaştır ve 10.000'den fazla kayıt içeren veri kümelerinde fark edilir hale gelir.
Yöntem 4
Veri Alt Toplamlarını kullanın.
Bu harika bir özellik. Elde edilen verilerle çalışmak garip olduğundan, verileri Data Consolidate'ten daha az kullanıyorum.
- A sütununa göre artan şekilde sırala.
- Veri aralığındaki herhangi bir hücreyi seçin.
- Menüden Veri - Alt Toplamlar'ı seçin.
- Varsayılan olarak Excel, verilerinizin son sütununun alt toplamını sunar. Bu, bu örnekte işe yarar, ancak doğru alanları seçmek için genellikle "Alt Toplamı Ekle:" listesinde gezinmeniz gerekir.
- Tamam'ı tıklayın. Excel, her hesap numarası değişikliğine toplamla birlikte yeni bir satır ekler.
Alt toplamları girdikten sonra, ad kutusunun altında küçük bir 123 göreceksiniz. Toplamları içeren hesap başına yalnızca bir satır görmek için 2'ye tıklayın. Bunları yeni bir konuma kopyalamak için gereken özel adımların açıklaması için Excel Alt Toplamlarını Kopyala'yı okuyun. Tüm satırları görmek için 3'e tıklayın. Artıları: Harika Özellik. Her bölümden sonra toplamlar ve sayfa sonları içeren raporlar yazdırmak için harika.
Eksileri: Veriler önce sıralanmalıdır. Birçok veri için yavaş. Toplamları başka bir yerde almak için Goto-Special-VisbileCellsOnly'yi kullanmanız gerekir. Orijinal verilerinize geri dönmek için Data-Subtotals-RemoveAll kullanmanız gerekir.
Yöntem 5
Bir Pivot Tablo kullanın.
Pivot tablolar en çok yönlü olanıdır. Verilerinizin sıralanması gerekmez. Sayısal sütunlar, hesap numarasının solunda veya sağında olabilir. Hesap numaralarının kolayca aşağıya veya sayfa boyunca gitmesini sağlayabilirsiniz.
- Veri aralığındaki herhangi bir hücreyi seçin.
- Menüden Veri - PivotTable'ı seçin.
- 1. Adımda varsayılanları kabul edin
- 2. adımdaki veri aralığının doğru olduğundan emin olun (genellikle doğrudur)
- Excel 2000 kullanıyorsanız, 3. adımdaki Düzen düğmesine tıklayın. Excel 95 ve 97 kullanıcıları 3. adımda otomatik olarak düzene gider.
- Düzen iletişim kutusunda, iletişim kutusunun sağ tarafındaki Hesap düğmesini sürükleyin ve Satır alanına bırakın.
- Miktar düğmesini iletişim kutusunun sağ tarafından sürükleyin ve Veri alanına bırakın.
- Excel 2000 kullanıcıları Tamam'ı, Excel 95/97 kullanıcıları İleri'yi tıklatır.
- Sonuçları yeni bir sayfada mı yoksa mevcut bir sayfanın belirli bir bölümünde mi istediğinizi belirtin. Excel Özet Tablo Gelişmiş Püf Noktaları'nda pivot tablolar hakkında daha fazla bilgi edinin.
- Pivot tablolar inanılmaz işlevsellik sunar ve bu görevi çok hızlı hale getirir. Pivot tablo sonuçlarını kopyalamak için, bir Düzenle-Yapıştır Özel Değerleri yapmanız gerekir, aksi takdirde Excel satır vb. Eklemenize izin vermez.
Artıları: Hızlı, Esnek, Güçlü. Çok sayıda veri için bile hızlı.
Eksileri: Biraz korkutucu.
Bill'in artık gereksiz verileri ortadan kaldırmak için dört yeni yöntemi var. Bu yöntemler zamanın başlangıcından beri mevcut olmasa da, hem Lotus hem de Excel, bu sıradan görevi yerine getirmemiz için bize daha hızlı yollar getiren büyük yenilikçiler oldu.
