Bir seçme sorgusunun özü, kaynak tablodan belirli ölçütleri karşılayan satırları seçmektir (standart olanı kullanmaya benzer). kullanarak kaynak tablodan değerleri seçelim. kullanmaktan farklı olarak ( CTRL + ÜST KRKT + L veya Veri / Sırala ve Filtrele / Filtrele) seçilen satırlar ayrı bir tabloya yerleştirilecektir.
Bu makalede, en yaygın sorguları ele alacağız, örneğin: sayısal bir sütundaki değeri belirli bir aralık (aralık) içinde olan tablo satırlarının seçimi; tarihi olan satırların seçimi belirli bir dönem; 2 metin kriteri ve diğerleri ile görevler. Basit sorgularla başlayalım.
1. Bir sayısal kriter (Fiyatın minimumdan daha yüksek olduğu Ürünleri seçin)
örnek dosya, sayfa Bir kriter sayıdır ).
Sadece Kaynak tablosundan fiyatı 25'ten yüksek olan kayıtları (satırları) ayrı bir tabloda görüntülemek gerekir.
Bu ve sonraki görevleri yardımı ile kolayca çözebilirsiniz. Bunu yapmak için Kaynak tablosunun başlıklarını seçin ve CTRL + ÜST KRKT + L... Fiyatlar başlığındaki açılır listeyi kullanarak seçin Sayı Filtreleri..., ardından gerekli filtreleme koşullarını ayarlayın ve Tamam'a tıklayın.
Seçim kriterlerine uyan kayıtlar görüntülenecektir.
Başka bir yaklaşım kullanmaktır. Seçilen satırların aksine, bunlar ayrı bir tabloya yerleştirilecektir - örneğin, Kaynak tablosundan farklı bir tarzda biçimlendirilebilen veya başka değişiklikler yapılabilen bir tür.
Ölçütü (asgari fiyat) hücreye yerleştirin E6 , filtrelenmiş veriler için tablo - aralıkta D10: E19 .
Şimdi aralığı seçelim D11: D19 (Ürün sütunu) ve şunu girin:
İNDEKS (A11: A19;
KÜÇÜK (EĞER ($ E $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-LINE ($ B $ 10))
Onun yerine GİRMEK klavye kısayoluna basın CTRL + ÜST KRKT + ENTER(dizi formülü olacaktır).
E11: E19 (Sütun Fiyatı) benzer bir tane gireceğimiz yer:
İNDEKS (B11: B19;
KÜÇÜK (EĞER ($ E $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-LINE ($ B $ 10))
Sonuç olarak, yalnızca hücrede belirtilenden daha az olmayan fiyatlara sahip ürünleri içerecek yeni bir tablo elde edeceğiz. E6 .
Alınan Numune İsteğinin dinamizmini göstermek için, E6 değer 55. Yeni tabloya yalnızca 2 kayıt girecektir.
Kaynak tablosuna Fiyatı 80 olan yeni bir ürün eklerseniz, yeni tabloya otomatik olarak yeni bir kayıt eklenecektir.
Not... Ayrıca filtrelenmiş verileri görüntülemek için ve kullanabilirsiniz. Belirli bir aracın seçimi, kullanıcının karşı karşıya olduğu göreve bağlıdır.
Kullanırken rahat değilseniz dizi formülü Birden çok değer döndüren , aşağıdaki bölümlerde açıklanan farklı bir yaklaşım kullanabilirsiniz: 5.a, 7, 10 ve 11. Bu durumlarda kullanılır.
2. İki sayısal kriter (Fiyatın aralık dahilinde olduğu Ürünleri seçin)
Ürünlerin ve Fiyatların bir listesini içeren bir Başlangıç tablosu olsun (bkz. örnek dosya, sayfaSayı Aralığı).
Kriterleri (alt ve üst fiyat sınırları) aralığa yerleştirin E5: E6 .
Onlar. Malların Fiyatı belirtilen aralığa düşerse, yeni Filtrelenmiş veri tablosunda böyle bir kayıt görünecektir.
Önceki görevden farklı olarak iki tane oluşturacağız: Ürünler ve Fiyatlar (onlar olmadan yapabilirsiniz, ancak formül yazarken uygundurlar). İlgili formüller Ad Yöneticisine bakmalıdır ( Formüller / Tanımlı İsimler / İsim Yöneticisi) aşağıdaki gibidir (aşağıdaki şekle bakın).
Şimdi aralığı seçelim D11: D19 ve biz tanıtmak:
İNDEKS (Mal;
EN AZ(
EĞER (($ E $ 5<=Цены)*($E$6>= Fiyatlar); HAT (Fiyatlar); "");
Onun yerine GİRMEK klavye kısayoluna basın CTRL + ÜST KRKT + ENTER.
Menzil ile aynı manipülasyonları yapacağız E11: E19 benzer bir tane tanıtacağız:
İNDEKS (Fiyatlar;
EN AZ(
EĞER (($ E $ 5<=Цены)*($E$6>= Fiyatlar); HAT (Fiyatlar); "");
HAT (Fiyatlar) - HAT ($ B $ 10)) - HAT ($ B $ 10))
Sonuç olarak, yalnızca fiyatları hücrelerde belirtilen aralıkta olan ürünleri içerecek yeni bir tablo elde edeceğiz. E5 ve E6 .
Alınan Raporun (Seçim talebi) dinamizmini göstermek için E6 değer 65. Yeni ölçütü karşılayan yeni tabloya Kaynak tablosundan bir kayıt daha eklenecektir.
Kaynak tablosuna Fiyatı 25 ile 65 arasında olan yeni bir ürün eklerseniz, yeni tabloya yeni bir kayıt eklenir.
Örnek dosya aynı zamanda Fiyat sütununda # DIV / 0 gibi bir hata değeri içerdiğinde oluşan hataları işleyen dizi formülleri de içerir. (bkz. sayfa Hata işleme).
Aşağıdaki görevler benzer şekilde çözülmüştür, bu nedenle bunları ayrıntılı olarak ele almayacağız.
3. Bir kriter Tarih (Teslimat Tarihinin belirtilenle eşleştiği Ürünleri seçin)
örnek dosya, sayfaTek kriter - Tarih).
Satırları seçmek için Görev1'e benzer dizi formülleri kullanılır (kriter yerine<= используется =):
=İNDEKS (A12: A20; KÜÇÜK (EĞER ($ E $ 6 = B12: B20; SATIR (B12: B20); ""); SATIR (B12: B20) -LINE ($ B $ 11)) - SATIR ($ B 11 $))
İNDEKS (B12: B20; KÜÇÜK (EĞER ($ E $ 6 = B12: B20; SATIR (B12: B20); ""); SATIR (B12: B20) -LINE ($ B $ 11)) - SATIR ($ B 11 $))
4. İki kriter Tarih (Teslimat Tarihi aralık dahilinde olan Ürünleri seçin)
Malların ve Teslimat tarihlerinin listesini içeren bir Başlangıç tablosu olduğunu varsayalım (bkz. örnek dosya, sayfaTarih aralığı).
Tarih sütununun SIRALANMADIĞINI unutmayın.
Çözüm1: Satırları filtrelemek için kullanabilirsiniz.
hücreye gir D12 dizi formülü:
İNDEKS (12$: 20$;
BÜYÜK (($ E $ 6<=$B$12:$B$20)*($E$7>= B $ 12: $ B $ 20) * (LINE ($ B $ 12: $ B $ 20) -LINE ($ B $ 11));
$ J $ 12-LINE (A12) + HAT ($ B $ 11) +1))
Not: Formülü girdikten sonra ENTER tuşu yerine CTRL + SHIFT + ENTER tuş kombinasyonuna basın. Bu klavye kısayolu, dizi formüllerini girmek için kullanılır.
Dizi formülünü istediğiniz hücre sayısına kopyalayın. Formül, yalnızca belirtilen tarih aralığında teslim edilen Ürünlerin değerlerini döndürür. Geri kalan hücreler #SAYI! Hataları içerecektir. Hatalar örnek dosya (Sayfa 4 Tarih Aralığı) .
E sütunundaki tarihler için benzer bir formül girilmelidir.
bir hücrede J12 ölçütleri karşılayan kaynak tablonun satır sayısını hesapladı:
EĞERSAYLAR (B12: B20; "> =" & $ E $ 6; B12: B20; "<="&$E$7)
Ölçütleri karşılayan kaynak tablo satırlarıdır.
Çözüm2: Satırları seçmek için Task2'ye benzer dizi formüllerini kullanabilirsiniz (yani):
=İNDEKS (A12: A20; KÜÇÜK (EĞER (($ E $ 6<=B12:B20)*($E$7>= B12: B20); HAT (B12: B20); ""); HAT (B12: B20) - HAT ($ B $ 11)) - HAT (B $ 11))
İNDEKS (B12: B20; KÜÇÜK (EĞER (($ E $ 6<=B12:B20)*($E$7>= B12: B20); HAT (B12: B20); ""); HAT (B12: B20) - HAT ($ B $ 11)) - HAT (B $ 11))
İlk formülü girmek için bir hücre aralığı seçin G12: G20 ... Formülü girdikten sonra ENTER tuşu yerine CTRL + SHIFT + ENTER tuş kombinasyonuna basın.
Çözüm3: Tarih sütunu SIRALANDI ise, dizi formüllerini kullanmanıza gerek yoktur.
İlk olarak, kriterleri karşılayan satırların ilk ve son konumlarını hesaplamanız gerekir. Ardından satırları yazdırın.
Bu örnek, formül yazmanın ne kadar kolay olduğunu bir kez daha açıkça göstermektedir.
5. Bir kriter Tarih (Teslimat Tarihinin belirtilenden daha erken / daha geç olmayan Malları seçin)
Malların ve Teslimat tarihlerinin listesini içeren bir Başlangıç tablosu olduğunu varsayalım (bkz. örnek dosya, sayfa Bir kriter - Tarih (en geç) ).
Tarihi daha erken olmayan (tarihin kendisi dahil) satırları seçmek için dizi formülünü kullanın:
=İNDEKS (A12: A20; KÜÇÜK (EĞER ($ E $ 7<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
Ayrıca örnek dosya, koşullar için formüller içerir: Daha önce değil (dahil değil); Daha sonra değil (dahil); Daha sonra değil (dahil değil).
7. Bir Metin Ölçütü (Belirli bir türdeki Ürünleri seçin)
Ürünlerin ve Fiyatların bir listesini içeren bir Başlangıç tablosu olsun (bkz. örnek dosya, sayfaTek kriter - Metin).
8. İki Metin Ölçütü (Belirli bir ayda teslim edilen belirli türdeki Malları seçin)
Ürünlerin ve Fiyatların bir listesini içeren bir Başlangıç tablosu olsun (bkz. örnek dosya, sayfa 2 kriter - metin (I) ).
ENDEKS ($ A $ 11: $ A $ 19;
KÜÇÜK (($ F $ 6 = $ A $ 11: $ A $ 19) * ($ F $ 7 = $ B $ 11: $ B $ 19); SATIR ($ A $ 11: $ A $ 19) -LINE ($ A $ 10); 30); SATIR (DOLAYLI ("A1: A" & SATIRLAR ($ A $ 11: $ A $ 19)))))
İfade ($ F $ 6 = $ A $ 11: $ A $ 19) * ($ F $ 7 = $ B $ 11: $ B $ 19) her iki koşulu da (Öğe ve Ay) ayarlar.
İfade SATIR (DOLAYLI ("A1: A" & SATIRLAR ($ A $ 11: $ A $ 19)))(1: 2: 3: 4: 5: 6: 7: 8: 9), yani. tablodaki satır numaraları.
9. İki Metin Ölçütü (Belirli Türlerdeki Ürünleri Seçin)
Ürünlerin ve Fiyatların bir listesini içeren bir Başlangıç tablosu olsun (bkz. örnek dosya, sayfa2 kriter - metin (VEYA)).
Problem 7'den farklı olarak, 2 tip mal içeren satırları seçin ().
Dizi formülü satırları seçmek için kullanılır:
ENDEKS (11$: 19$;
BÜYÜK ((($ E $ 6 = $ A $ 11: $ A $ 19) + ($ E $ 7 = $ A $ 11: $ A $ 19)) * (SATIR ($ A $ 11: $ A $ 19) ) -LINE ($ A $ 10) ); COUNTIF ($ A $ 11: $ A $ 19; $ E $ 6) + EĞERSAY ($ A $ 11: $ A $ 19; $ E $ 7) -SATIR ($ 11 $: A11) +1))
Şart ($ E $ 6 = $ A $ 11: $ A $ 19) + ($ E $ 7 = $ A $ 11: $ A $ 19) sarı hücrelerden sadece belirtilen mal türlerinin seçileceğini garanti eder (Ürün2 ve Ürün3). Görev için + (ilave) işareti kullanılır (en az 1 kriter karşılanmalıdır).
Yukarıdaki ifade bir dizi (0: 0: 0: 0: 1: 1: 1: 0: 0) döndürür. ifade ile çarparak HAT ($ A $ 11: $ A $ 19) -LINE ($ A $ 10), yani (1: 2: 3: 4: 5: 6: 7: 8: 9), kriterleri karşılayan bir dizi pozisyon (tablo satır numaraları) alırız. Bizim durumumuzda bir dizi olacak (0: 0: 0: 0: 5: 6: 7: 0: 0).
Örnek olarak, aşağıdaki soruna çözümler sunuyoruz: Fiyatı belirli bir aralıkta olan ve belirli sayıda veya daha fazla tekrarlanan Ürünler'i seçin.
Malların sevkıyat tablosunu ilk tablo olarak alalım.
1000 rublelik bir fiyata kaç tane ve hangi mal sevkiyatının sağlandığıyla ilgilendiğimizi varsayalım. 2000r'ye kadar. (kriter 1). Ayrıca, aynı fiyata en az 3 taraf olmalıdır (2. kriter).
Çözüm bir dizi formülüdür:
KÜÇÜK (SATIR ($ A $ 14: $ A $ 27) * ($ C $ 14: $ C $ 27> = $ B $ 7) * ($ C $ 14: $ C $ 27<=$C$7)*($D$14:$D$27>= $ B $ 10); F14 + ($ G $ 8- $ G $ 9))
Bu formül, her iki ölçütü de karşılayan satır numaralarını döndürür.
formül = TOPLA (($ C $ 14: $ C $ 27> = $ B $ 7) * ($ C $ 14: $ C $ 27<=$C$7)*($D$14:$D$27>= $B $ 10)) kriterleri karşılayan satır sayısını sayar.
11. (Herhangi biri) veya (Tümü) kriterinin değerini kullanırız.
V "11. Kriter Herhangi biri veya (Tümü)" sayfasındaki örnek dosya kriterin bu varyantı uygulanmıştır.
Bu durumda formül, EĞER () işlevini içermelidir. Değer (Tümü) seçilirse, bu kriter dikkate alınmadan değerleri görüntülemek için formül kullanılır. Başka bir değer seçilirse, ölçüt olağan şekilde çalışır.
EĞER ($ C $ 8 = "(Tümü)";
KÜÇÜK ((LINE ($ B $ 13: $ B $ 26) -LINE ($ B $ 12)) * ($ D $ 13: $ D $ 26> = $ D $ 8); F13 + ($ G $ 6 - $G $ 7);
KÜÇÜK ((LINE ($ B $ 13: $ B $ 26) -LINE ($ B $ 12)) * ($ D $ 13: $ D $ 26> = $ D $ 8) * ($ C $ 13: $ C $ 26 = $ C $ 8 ; F13 + ($ G $ 6- $ G $ 7)))
Formülün geri kalanı yukarıda tartışılanlara benzer.
Veri işleniyor
"Nitel performans, yüzde" sütununda Sayfa 5'teki örnekle ilgili bir rapor oluşturun. (Sayfa 8, tablo 7'den)
Verileri örneklemek için aşağıdaki adımları gerçekleştirmelisiniz:
InputBox operatörünü kullanarak bir değişken girerek belirli bir koşula göre yeni bir dizinin eleman sayısını belirleyin
Yeni bir dizi bildir ve yeniden bildir
Yeni bir dizi oluşturun. Bunu yapmak için, yeni u = 1 dizisinin ilk öğesinin sayısını ayarlamanız gerekir. Ardından, seçim koşulunun "Nitel performans, yüzde" sütununa göre yazıldığı bir döngü yürütülür. Kontrolün sonucu doğruysa, analiz edilen dizinin elemanı yeni dizinin bir elemanı olur.
Yeni öğeyi Sayfa 8'de görüntüle
Alt RaporÖrneği ()
Sayfalar ("Sayfa8"). Seçme
Dim A () Varyant Olarak
ReDim A (1 ila n1, 1 ila m)
VVOD "Sayfa5", A, n1, m, 4
C = InputBox ("Bir koşul girin")
Sayfalar ("Sayfa8"). Hücreler (5.11) = C
i = 1 ila n1 için
Eğer A (i,
8) >
d = d + 1
Sayfalar ("Sayfa8"). Hücreler (5,10) = d
Dim B () Varyant Olarak
ReDim B (1 ila d, 1 ila m)
i = 1 ila n1 için
Eğer A (i,
8)> Sayfalar ("Sayfa8"). Hücreler (5.11) Sonra
j = 1'den m'ye
B (u, j) = A (i, j)
u = u + 1
i = 1'den d'ye
j = 1'den m'ye
Sayfalar ("Sayfa8"). Hücreler (i + 4, j) = B (i, j)
Şekil 6. Getirdikten sonra tablo verileri
Bir seçimde otomatik makro oluşturma
Makro kaydını açıyoruz. Araçlar > Makro > Kaydı Başlat > Tamam. Kaydı durdur düğmesinin olduğu yerde bir kare görünecektir. Sayfa5'te (rapor), başlıklar ve toplamlar olmadan tabloyu seçin, Sayfa10'a (otomatik seçim) kopyalayın. Başlıksız tabloyu seçin ve menü öğesinde Veri> Filtre> Otomatik Filtre'yi seçin> koşulu seçin> Tamam. Sıralayacağımız sütunu işaretliyoruz. Makronun çalışmasını bitiriyoruz.
Alt Macro2Sample ()
"Macro2Setup Makrosu
Sayfalar ("Sayfa5"). Seçme
Seçim. kopyala
Sayfalar ("Sayfa9"). Seçme
Etkin sayfa. Yapıştırmak
Aralık ("H5: H17"). Seçme
Başvuru. CutCopyMode = Yanlış
Seçim. Otomatik filtre
Etkin sayfa. Aralık ("$ H $ 5: $ H $ 17"). Otomatik Filtre Alanı: = 1, Kriter1: = "> 80", _
Operatör: = xlAnd
Aralık ("G22"). Seçme
Şekil 7. Otomatik seçimden sonra tablo verileri
Maksimum ve minimum değerin belirlenmesi
"Toplam", "Mutlak performans, yüzde", "Nitel performans" sütunları için maksimum ve minimum değerleri belirleyin (tablo 9, sayfa 10)
Maksimum ve minimum değerleri belirlemek için aşağıdakileri yapmanız gerekir:
Mevcut minimum (maksimum) olacak referans değişkenini ayarlayın
Popülasyonun her bir öğesi, mevcut minimum (maksimum) ile tek tek karşılaştırılır ve bu öğe arama koşullarını karşılamıyorsa (minimum olması durumunda daha büyük ve maksimum olması durumunda daha azdır) , ardından referans değeri, karşılaştırılan öğenin değeriyle değiştirilir
Mevcut minimum (maksimum) değişkenindeki tüm öğelerin tam bir taramasından sonra, gerçek minimum (maksimum) bulunur
Minimum (maksimum) değeri ilgili hücrelerde görüntülenir.
Alt minimum max ()
Dim A () Varyant Olarak
n1 = Sayfalar ("Sayfa4"). Hücreler (5.12)
m = Sayfalar ("Sayfa2"). Hücreler (5.12)
ReDim A (1 ila n1, 1 ila m)
VVOD "Sayfa5", A, n1, m, 4
VIVOD "Sayfa10", A, n1, m, 4
VVOD "Sayfa10", A, n1, m, 4
j = 3 için m
maksA = 0.00001
minA = 1.000.000
i = 1 ila n1 için
A (i, j)> maxA ise
maxA = A (i, j)
Eğer A(i,j)< minA Then
minA = A (i, j)
Sayfalar ("Sayfa10"). Hücreler (i + 4 + 2, j) = maxA
Sayfalar ("Sayfa10"). Hücreler (i + 4 + 3, j) = minA
Makro, ileride başvurmak üzere kaydedilmiş ve kaydedilmiş bir eylemler dizisidir. Kaydedilen makro, özel bir komutla oynatılabilir. Başka bir deyişle, eylemlerinizi bir makroya kaydedebilir, kaydedebilir ve ardından diğer kullanıcıların makroda kaydedilen eylemleri basit bir tuşa basarak tekrar oynatmasına izin verebilirsiniz. Bu, özellikle PivotTable raporlarını dağıtırken kullanışlıdır.
Müşterilerinize PivotTable raporlarını aya, çeyreğe ve yıla göre gruplandırma olanağı vermek istediğinizi varsayalım. Teknik olarak gruplama işlemi herhangi bir kullanıcı tarafından yapılabilir, ancak bazı müşterileriniz bunu anlamayı gerekli bulmayabilir. Böyle bir durumda, bir gruplama makrosunu aya göre, diğerini çeyreğe göre ve üçüncüyü yıla göre kaydedebilirsiniz. Ardından, her makro için bir tane olmak üzere üç düğme oluşturun. O zaman PivotTable olmayan müşterilerinizin PivotTable raporunu düzgün bir şekilde gruplamak için bir düğmeyi tıklamaları yeterlidir.
PivotTable raporlarında makro kullanmanın temel faydası, müşterilerin özet tablolar normalde gerçekleştiremeyecekleri işlemler. Bu, sağlanan verilerin analizinin verimliliğini önemli ölçüde artırır.
Notu formatta indirin veya örneklerle indirin (içeride Excel dosyası makrolar ile; sağlayıcının politikası, bu formattaki bir dosyayı siteye doğrudan yüklemenize izin vermez).
makro kayıt
Şekil 2'de gösterilen pivot tabloya bir göz atın. 1. İçine sağ tıklayıp seçim yaparak bu pivot tabloyu yenileyebilirsiniz. Yenile... Pivot tabloyu güncellerken eylemleri makro olarak kaydettiyseniz, siz veya başka herhangi bir kullanıcı bu eylemleri yeniden yürütebilir ve makroyu çalıştırmanın bir sonucu olarak pivot tabloyu güncelleyebilirsiniz.
Pirinç. 1. Bu pivot tablonun güncellenmesi sırasında eylemlerin kaydedilmesi, gelecekte makroyu çalıştırmanın bir sonucu olarak verileri güncellemenizi sağlayacaktır.
Makro kaydetmenin ilk adımı bir iletişim kutusu çağırmaktır. makro kayıt... sekmeye git Geliştiricişerit ve düğmeye tıklayın makro kayıt... (Şeritteki sekmeyi bulamıyorsanız Geliştirici, sekmeyi seçin Dosya ve düğmesine tıklayın Seçenekler... Görüntülenen iletişim kutusunda Excel seçenekleri bir kategori seç Şeridi Özelleştirme ve sağdaki listede kutuyu işaretleyin Geliştirici... Sonuç olarak, şeritte bir sekme görünecektir Geliştirici.) Alternatif yol bir makro kaydetmeye başlayın - düğmesine tıklayın (Şekil 2).
iletişim kutusunda makro kayıt makro hakkında aşağıdaki bilgileri girin (Şekil 3):
İsimmakro... Ad, makro tarafından gerçekleştirilen eylemleri açıklamalıdır. Ad bir harf veya alt çizgi ile başlamalıdır; boşluk veya diğer geçersiz karakterler içermemelidir; çalışma kitabındaki yerleşik Excel adıyla veya başka bir nesnenin adıyla aynı olmamalıdır.
kombinasyonanahtarlar... Bu alana herhangi bir harf girebilirsiniz. Makroyu oynatmak için kullanılacak klavye kısayolunun bir parçası olacaktır. Tuş kombinasyonu isteğe bağlıdır. Varsayılan olarak, bir kombinasyonun başlangıcı olarak yalnızca Ctrl sunulur. Kombinasyonun Shift'i de içermesini istiyorsanız, Shift tuşunu basılı tutarken penceredeki harfi yazın.
Kaydetmekv... Makronun depolandığı yer burasıdır. PivotTable raporunu diğer kullanıcılara dağıtacaksanız, seçeneğini belirleyin. Bukitap... Excel ayrıca makroyu Yeni kitap veya içinde Kişisel makro kitabı.
Açıklama... Bu alan, oluşturulan makronun açıklamasını girmek için kullanılır.
Pirinç. 3. Pencere ayarı makro kayıt
Makro pivot tabloyu güncellediğinden, adı seçin Veri güncellemesi... Ayrıca bir kombinasyona makro atayabilirsiniz. Ctrl tuşları+ Üst Karakter + S. Bir makro oluşturduktan sonra, onu çalıştırmak için bu klavye kısayolunu kullanacağınızı unutmayın. Makroyu saklama yeri olarak seçeneği seçin Bu kitap ve tıklayın Tamam.
İletişim kutusuna tıkladıktan sonra makro kayıt düğmede Tamam makro kaydı başlar. Bu noktada Excel'de yaptığınız tüm işlemler günlüğe kaydedilecektir.
PivotTable alanına sağ tıklayın ve Yenile(Şekil 1'deki gibi, ancak makro kayıt modunda). Pivot tabloyu güncelledikten sonra butonunu kullanarak makro kayıt işlemini durdurabilirsiniz. Kaydetmeyi bırak sekmeler Geliştirici... Ya da Şekil 2'de gösterilen düğmeye tekrar tıklayın. 2.
Demek ilk makronuzu kaydettiniz. Artık Ctrl + Shift + Q klavye kısayolunu kullanarak makroyu çalıştırabilirsiniz.
Makro güvenlik uyarısı. Makrolar kullanıcı tarafından kaydedilirse, güvenlik alt sistemi tarafından herhangi bir kısıtlama olmaksızın yürütüleceklerine dikkat edilmelidir. Ancak dağıtım yapılırken çalışma kitabı makrolar içeren, diğer kullanıcılara çalışma dosyalarını açmada herhangi bir risk olmadığından ve makroların yürütülmesinin sisteme virüs bulaşmadığından emin olma fırsatı sağlamak gerekir. Özellikle, bu bölümde kullanılan örnek dosyanın, özellikle Excel'in üzerinde makro çalıştırmasına izin vermediğiniz sürece tam olarak çalışmayacağını hemen fark edeceksiniz.
Makrolarınızı güvende tutmanın en kolay yolu, yalnızca "Güvenilen" virüssüz çalışma kitaplarının yerleştirileceği bir klasör olan Güvenilir Konum oluşturmaktır. Güvenilir konum, sizin ve müşterilerinizin çalışma kitaplarındaki makroları herhangi bir güvenlik kısıtlaması olmaksızın yürütmenize olanak tanır (bu davranış, çalışma kitapları güvenilir klasörde olduğu sürece devam eder).
Güvenilir bir konum ayarlamak için aşağıdaki adımları izleyin.
Şerit sekmesini seçin Geliştirici ve düğmeye tıklayın makro güvenlik... Ekranda bir iletişim kutusu belirecektir. Güven merkezi.
düğmesine tıklayın Yeni konum ekle.
düğmesine tıklayın genel bakış güvendiğiniz iş dosyaları için bir klasör belirtmek için.
Güvenilir bir konum belirlediğinizde, varsayılan olarak içindeki tüm çalışma kitapları için rastgele makrolar çalışır.
Güvenlik modeli Excel 2013'te geliştirilmiştir. Daha önce "güvenilir" olan çalışma kitabı dosyaları artık hatırlanıyor; açtıktan sonra Excel çalışma kitapları ve düğmeye tıklayın İçeriği dahil et Excel bu durumu hatırlar. Sonuç olarak, bu kitap güvenilirler kategorisine giriyor ve sonraki açılışında gereksiz sorular sorulmuyor.
Form denetimlerini kullanarak bir kullanıcı arabirimi oluşturma
Ctrl + Shift + Q tuş bileşimini kullanarak bir makro çalıştırmak, PivotTable raporunda yalnızca bir makro olduğunda yardımcı olur. (Ayrıca, kullanıcıların bu kombinasyonu bilmesi gerekir.) Ancak, müşterilerinize farklı eylemler gerçekleştiren birden çok makro sağlamak istediğinizi varsayalım. Bu durumda, müşterilere anlaşılır ve basit bir şekilde tuş kombinasyonlarını ezberlemek zorunda kalmadan her makroyu çalıştırın. mükemmel çözüm basit Kullanıcı arayüzü düğmeler, kaydırma çubukları ve makroları fare tıklamalarıyla yürütmenize izin veren diğer araçlar gibi bir denetimler topluluğu olarak.
Excel, doğrudan bir elektronik tabloda bir kullanıcı arabirimi oluşturmak için tasarlanmış bir dizi araç sunar. Bu araçlara form kontrolleri denir. Temel fikir, bir form kontrolü koymanın mümkün olmasıdır. elektronik tablo ve daha önce kaydedilen makroyu atayın. Bir kontrole atandığında, makro o kontrole tıklanarak çalışacaktır.
Form kontrolleri grupta bulunabilir Form kontrollerişerit sekmeleri Geliştirici... Kontrol paletini açmak için bu gruptaki düğmesine tıklayın. Sokmak(şekil 4).
Pirinç. 4. Form kontrolü Buton
Lütfen dikkat: form kontrollerine ek olarak palet ayrıca şunları içerir: ActiveX denetimleri... Benzer olmalarına rağmen, programatik olarak tamamen farklı nesnelerdir. Form kontrolleri engelli ve basit ayarlarçalışma sayfalarına yerleştirmek için özel olarak tasarlanmıştır. Aynı zamanda ActiveX denetimleriöncelikle özel formlarda kullanılır. Çalışma sayfalarınıza yalnızca form denetimleri yerleştirmeyi bir kural haline getirin.
Eldeki göreve en uygun kontrolleri seçmelisiniz. Bu örnekte, müşterilerin bir düğmeyi tıklayarak PivotTable'ı güncelleyebilmesi gerekir. Kontrole tıklayın Buton, fare işaretçisini çalışma sayfasında düğmenin olmasını istediğiniz konuma getirin ve tıklayın.
Düğmeyi tabloya yerleştirdikten sonra bir iletişim kutusu açılacaktır. Makro ata nesne(şek. 5). Gerekli makroyu seçin (bizim durumumuzda - Veri güncellemesi daha önce kaydedilmiş) ve tıklayın Tamam.
Pirinç. 5. Düğmeye atanacak makroyu seçin ve düğmesine tıklayın. Tamam... V bu durum bir makro uygulanmalı Veri güncellemesi
PivotTable raporuna gerekli tüm kontrolleri yerleştirdikten sonra, temel bir arayüz oluşturmak için tabloyu biçimlendirebilirsiniz. İncirde. Şekil 6, biçimlendirmeden sonra PivotTable raporunu gösterir.
Kaydedilmiş bir Makroyu Değiştirme
Makro kaydetmenin bir sonucu olarak Excel programı gerçekleştirdiğiniz eylemleri saklayan bir modül oluşturur. Kaydedilen tüm eylemler, makroyu oluşturan VBA kodu satırlarıyla temsil edilir. Çeşitli türlerde pivot tablo raporları ekleyebilirsiniz. işlevsellikİstediğiniz sonuçları elde etmek için VBA kodunu değiştirerek. Tüm bunların nasıl çalıştığını anlamayı kolaylaştırmak için, ilk beş müşteri kaydını görüntüleyen yeni bir makro oluşturalım. sekmeye git Geliştirici ve düğmeye tıklayın makro kayıt... Şekil 2'de gösterilen iletişim kutusu. 7. Oluşturulan makroyu adlandırın İlkNmüşteriler ve kaydetme konumunu belirtin Bu kitap... Tıklamak Tamam Makroyu kaydetmeye başlamak için
Kayda başladıktan sonra kutunun yanındaki oku tıklayın. Müşteri adı, Seçme Değere göre filtrele ve seçenek İlk 10(Şek.8a). Görüntülenen iletişim kutusunda, ayarları Şekil 1'de gösterildiği gibi yapılandırın. 8b. Bu ayarlar, satış açısından ilk beş müşterinin verilerini görüntülemenizi söyler. Tıklamak Tamam.
Pirinç. 8. Satışa göre ilk beş müşteriyi görüntülemek için filtreyi (a) seçin ve parametreleri (b) ayarlayın.
En iyi 5 satış adayını almak için gereken tüm adımları başarıyla kaydettikten sonra sekmeye gidin. Geliştirici ve düğmeye tıklayın Kaydetmeyi bırak.
Artık en iyi 5 satış müşterisini almak için pivot tabloyu filtreleyecek bir makronuz var. Makronun kaydırma çubuğunun durumuna tepki vermesi gerekir, yani. kaydırma çubuğunu kullanarak, verileri pivot tablo raporunda görüntülenecek müşterilerin sayısını makroya gösterebilmeniz gerekir. Böylece, kullanıcı kaydırma çubuğunu kullanarak ilk beş, ilk sekiz veya otuz iki en iyi müşteriyi uygun gördüğü şekilde getirebilecektir.
E-tablonuza kaydırma çubuğu eklemek için sekmeye gidin Geliştirici, düğmeye bas Sokmak, palet üzerinde bir kontrol seçin Kaydırma çubuğu ve çalışma kağıdına yerleştirin. Kontrole sağ tıklayın Kaydırma çubuğu nesne biçimi... Bir iletişim kutusu açılacaktır Kontrol formatı(şek. 9). İçinde, ayarlarda aşağıdaki değişiklikleri yapın: En az değer değer 1 ata, parametre Maksimum değer - değer 200 ve sahada Hücre iletişimi kaydırma çubuğu değerini M2 hücresinde görüntülemek için $ M $ 2 değerini girin. düğmesine tıklayın Tamam Daha önce belirtilen ayarları uygulamak için
Şimdi son kaydedilen makroyu eşleştirmeniz gerekiyor İlkNmüşteriler kontrol ile Kaydırma çubuğuçalışma sayfasında. Kontrole sağ tıklayın Kaydırma çubuğu ve bağlam menüsü takım seç Makro ata Makro Ataması iletişim kutusunu açmak için Kaydedilen makroyu kaydırma çubuğuna atayın İlkN müşteri... Makro, kaydırma çubuğuna her tıklandığında çalışacaktır. Oluşturulan kaydırma çubuğunu test edin. Stripe tıkladıktan sonra makro çalışacaktır. İlkN müşteri ve M2 hücresindeki sayı, kaydırma çubuğunun durumunu gösterecek şekilde değişecektir. M2 hücresindeki sayı, makroyu kaydırma çubuğuna bağlamak için kullanıldığından önemlidir.
Geriye kalan tek şey, makroyu kaydırma çubuğuyla ilişkilendirerek M2 hücresindeki sayıyı işlemek. Bunu yapmak için makronun VBA koduna gitmeniz gerekir. Bunu yapmak için sekmeye gidin Geliştirici ve düğmeye tıklayın makrolar... Bir iletişim kutusu açılacaktır Makro(şek. 10). İçinde seçili makroyu çalıştırabilir, silebilir ve düzenleyebilirsiniz. Bir makronun VBA kodunu ekranda görüntülemek için makroyu seçin ve düğmesine tıklayın. Değiştirmek.
Pirinç. 10. VBA makro koduna erişmek için İlkNmüşteriler, makroyu seçin ve düğmesine tıklayın Değiştirmek
Ekranda bir editör penceresi görünecektir Visual Basic makronun VBA kodu ile (şekil 11). Amacınız, makro kaydı sırasında ayarlanan sabit kodlu 5 sayısını, kaydırma çubuğuna bağlı M2 hücresindeki değerle değiştirmektir. Başlangıçta, en yüksek gelire sahip ilk beş müşteriyi görüntülemek için bir makro kaydedildi.
Koddan 5 sayısını kaldırın ve bunun yerine aşağıdaki ifadeyi girin:
ActiveSheet.Range ("M2") .Value
Filtreleri temizlemek için makronun başına iki satır ekleyin:
Aralık ("A4") .Seç
ActiveSheet.PivotTables ("PivotTable1") .PivotFields ("Müşteri Adı") .ClearAllFilters
Makro kodu şimdi Şekil 2'de gösterilene benzemelidir. 12.
Visual Basic Düzenleyicisi'ni kapatın ve Özet Tablo raporuna dönün. Kaydırıcıyı 11'e sürükleyerek kaydırma çubuğunu test edin. Makro çalışmalı ve yaklaşık 11 kaydı filtrelemelidir. en iyi müşteriler satış yoluyla.
Bir açılır menü ile iki pivot tabloyu senkronize edin
Şekilde gösterilen rapor. 13, iki pivot tablo içerir. Her birinin bir satış alanı seçmenizi sağlayan bir sayfa alanı vardır. Sorun şu ki, bir pivot tablonun sayfa alanında bir pazar seçtiğinizde, başka bir pivot tablonun sayfa alanında aynı pazarı seçmeniz gerekir. Veri analizi aşamasında iki tablo arasında filtreleri senkronize etmek büyük bir sorun değildir, ancak muhtemelen siz veya müşterileriniz bunu yapmayı unutacaktır.
Pirinç. 13. İki pivot tablo, verileri pazara göre filtreleyen sayfa alanları içerir. Tek bir pazarın verilerini analiz etmek için her iki pivot tabloyu da senkronize etmeniz gerekir.
Bu pivot tabloları senkronize tutmanın bir yolu, bir açılır liste kullanmaktır. Buradaki fikir, sahadan istenen pazarı seçen bir makro kaydetmektir. satış pazarı her iki tabloda. Ardından bir açılır liste oluşturmanız ve onu iki pivot tablodan satış pazarlarının adlarıyla doldurmanız gerekir. Son olarak, kaydedilen makro, açılır listedeki değerleri kullanarak her iki PivotTable'ı filtreleyecek şekilde değiştirilmelidir. Bu sorunu çözmek için aşağıdaki adımları uygulamanız gerekir.
1. Yeni bir makro oluşturun ve ona bir ad verin SynchMarkets... Kayıt başladığında, alanda seçin Her ikisi için de pazar satış pivot tabloları Kaliforniya ve makroyu kaydetmeyi durdurun.
2. Form kontrolleri paletini görüntüleyin ve çalışma sayfasına bir açılır liste ekleyin.
3. Tüm PivotTable pazarlarının kodlanmış bir listesini oluşturun. Listedeki ilk öğenin (Tümü) olduğunu unutmayın. Açılır listeden tüm pazarları seçebilmek istiyorsanız bu öğeyi etkinleştirmelisiniz.
4. Bu noktada, PivotTable raporu Şekil 1'de gösterilene benzemelidir. on dört.
Pirinç. 14. İhtiyacınız olan tüm araçlara sahipsiniz: bir alanı değiştiren bir makro satış pazarı her iki pivot tablo, bir açılır liste ve pivot tabloda yer alan tüm satış pazarlarının bir listesi
5. Açılır listeye sağ tıklayın ve komutu seçin nesne biçimi kontrolü özelleştirmek için.
6. İlk olarak, Şekil 6-7'de gösterildiği gibi, açılır listeyi doldurmak için kullanılan ilk değer aralığını ayarlayın. 15. Bu durumda 3. adımda oluşturduğunuz satış pazarlarının listesinden bahsediyoruz. Ardından, seçilen öğenin seri numarasını görüntüleyen hücreyi belirtin (bu örnekte bu H1 hücresidir). Parametre Liste satırlarının sayısı açılır listede aynı anda kaç satırın görüntüleneceğini belirler. düğmesine tıklayın Tamam.
Pirinç. 15. Açılır liste ayarları, başlangıç değer aralığı olarak satış pazarları listesini göstermeli ve bağlantı noktası olarak H1 hücresini tanımlamalıdır.
7. Şimdi, açılır listeden bir satış pazarı seçme ve ayrıca H1 hücresinde ilişkili seri numarasını tanımlama olanağına sahipsiniz (Şekil 16). Soru ortaya çıkıyor: neden piyasanın gerçek adı yerine endeks değeri kullanılıyor? Çünkü açılır menü bir isim değil, bir sayı döndürür. Örneğin, açılır listeden Kaliforniya'yı seçerseniz, H1 hücresinde 5 değeri görüntülenir; bu, Kaliforniya'nın listedeki beşinci öğe olduğu anlamına gelir.
Pirinç. 16. Açılır liste artık pazarların adlarıyla doldurulur ve seçilen pazarın seri numarası H1 hücresinde görüntülenir.
8. Piyasa adı yerine sıra numarasını kullanmak için INDEX işlevini kullanarak iletmelisiniz.
9. H1 hücresindeki seri numarasını anlamlı bir değere dönüştüren INDEX işlevini girin.
10. INDEX işlevi iki bağımsız değişken alır. İlk argüman, listedeki değer aralığını temsil eder. Çoğu durumda, açılır menüyü dolduran aralığı kullanırsınız. İkinci argüman bir sıra numarasıdır. Bir hücreye bir seri numarası girilirse (örneğin, Şekil 17'deki gibi H1 hücresine), o zaman basitçe bu hücreye başvurabilirsiniz.
Pirinç. 17. I1 hücresindeki INDEX işlevi, H1 hücresinde saklanan sıra numarasını bir değere dönüştürür. Makroyu değiştirmek için I1 hücresindeki değeri kullanacaksınız.
11. Makroyu düzenleyin SynchMarkets sabit kodlanmış değer yerine I1 hücresindeki değeri kullanarak. sekmeye git Geliştirici ve düğmeye tıklayın makrolar... Şekilde gösterildiği gibi ekranda bir iletişim kutusu belirecektir. 18. İçinde bir makro seçin SynchMarkets ve Değiştir düğmesini tıklayın.
Pirinç. 18. Bir makronun VBA koduna erişmek için makroyu seçin SynchMarkets ve tıklayın Değiştirmek
12. Makroyu kaydederken, her iki pivot tablodaki alandan Kaliforniya satış bölgesini seçtiniz. satış pazarı... Olarak Şekil l'de görülebilir. 19, California pazarı artık VBA makro kodunda sabit kodlanmıştır.
13. California'yı Activesheet.Range ("I1") .Value ile değiştirin; bu, I1 hücresindeki değeri ifade eder. Bu noktada, makro kodu Şekil 1'de gösterilene benzemelidir. 20. Makroyu değiştirdikten sonra Visual Basic Düzenleyicisini kapatın ve elektronik tabloya dönün.
Pirinç. 20. "California"yı ActiveSheet.Range ("I1") .Value ile değiştirin ve Visual Basic Düzenleyicisini kapatın
14. Açılır listeden bir satış alanı seçerken sadece makronun yürütülmesini sağlamak için kalır. Açılır menüye sağ tıklayın ve bir seçenek belirleyin Makro ata... Bir makro seçin SynchMarket ve düğmeye tıklayın Tamam.
15. Pivot tablolarda ve oluşturduğunuz pazarlar ve endeks formülleri listesinde sayfa alanlarıyla birlikte satırları ve sütunları gizleyin.
İncirde. 21 nihai sonucu gösterir. Artık müşterilerin tek bir açılır liste kullanarak her iki pivot tabloda da satış alanlarını seçmelerine olanak tanıyan bir kullanıcı arayüzünüz var.
Açılır listeden yeni bir öğe seçtiğinizde, sütunlar, içinde görüntülenen tüm verileri barındırmak için otomatik olarak yeniden boyutlandırılır. Programın bu davranışı, bir çalışma sayfası şablonunu biçimlendirirken oldukça sıkıcıdır. PivotTable'a sağ tıklayıp seçerek bunu önleyebilirsiniz. Pivot tablo seçenekleri... Ekranda, onay kutusunu temizlemeniz gereken aynı ada sahip bir iletişim kutusu görünecektir. Güncelleme sırasında sütunları otomatik olarak yeniden boyutlandır.
Jelen'in kitabına dayanan not, Alexander. ... Bölüm 12.
Excel araçlarını kullanarak, bir aralıktaki belirli verileri rastgele sırayla, bir veya birkaç koşulda seçebilirsiniz. Bu tür sorunları çözmek için kural olarak dizi formülleri veya makrolar kullanılır. Bazı örneklere bir göz atalım.
Excel'de koşula göre seçim nasıl yapılır
Dizi formüllerini kullanırken seçilen veriler ayrı bir tabloda gösterilir. avantajı nedir Bu method Geleneksel bir filtre ile karşılaştırıldığında.
Kaynak tablosu:
Öncelikle tek bir sayısal kritere göre seçim yapmayı öğrenelim. Görev, tablodan 200 ruble'nin üzerinde bir fiyata sahip ürünleri seçmektir. Çözümlerden biri filtreleme uygulamaktır. Sonuç olarak, yalnızca talebi karşılayan ürünler orijinal tabloda kalacaktır.
Başka bir çözüm, bir dizi formülü kullanmaktır. İsteğe karşılık gelen satırlar ayrı bir tablo raporuna sığacaktır.
İlk olarak, orijinal tablonun yanında boş bir tablo oluşturuyoruz: yinelenen başlıklar, satır ve sütun sayısı. Yeni tablo E1: G10 aralığını kaplar. Şimdi E2: E10'u ("Tarih" sütunu) seçin ve aşağıdaki formülü girin: ( }.
Bir dizi formülü almak için Ctrl + Shift + Enter tuş bileşimine basın. Bir sonraki sütunda - "Ürün" - benzer bir dizi formülü giriyoruz: ( ). INDEX işlevinin yalnızca ilk argümanı değişti.
"Fiyat" sütununda, INDEX işlevinin ilk bağımsız değişkenini değiştirerek aynı dizi formülünü girin.
Sonuç olarak, fiyatı 200 rubleden fazla olan mallar hakkında bir rapor alıyoruz.
Böyle bir seçim dinamiktir: Sorgu değiştiğinde veya kaynak tabloda yeni ürünler göründüğünde, rapor otomatik olarak değişecektir.
Görev numarası 2 - 20 Eylül 2015'te satışa sunulan malları orijinal tablodan seçin. Yani seçim kriteri tarihtir. Kolaylık sağlamak için, istenen tarihi ayrı bir hücre olan I2'ye gireceğiz.
Problemi çözmek için benzer bir dizi formülü kullanılır. Sadece bir kriter yerine).
Benzer formüller diğer sütunlara girilir (yukarıdaki ilkeye bakın).
Şimdi metin kriterini kullanıyoruz. I2 hücresindeki tarih yerine "Ürün 1" metnini girin. Dizi formülünü biraz değiştirelim: ( }.
Excel'de böyle harika bir seçim işlevi.
Excel'de birden çok koşula göre seçim
İlk olarak, iki sayısal kriter alalım:
Görev, 400'den az ve 200'den fazla rubleye mal olan malları seçmektir. Koşulları "*" işaretiyle birleştirelim. Dizi formülü şöyle görünür: ( }.!}
Bu, rapor tablosunun ilk sütunu içindir. İkinci ve üçüncü için - INDEX işlevinin ilk argümanını değiştiriyoruz. Sonuç:
Birkaç tarih veya sayısal kritere göre seçim yapmak için benzer dizi formülleri kullanırız.
Excel'de rastgele örnekleme
Bir kullanıcı ile çalıştığında büyük miktar veriler, sonraki analizler için rastgele örnekleme gerektirebilir. Her satıra rastgele bir sayı atayabilir ve ardından seçime sıralama uygulayabilirsiniz.
Orijinal veri kümesi:
İlk olarak, sola iki boş sütun ekleyelim. A2 hücresine RAND () formülünü yazın. Bunu tüm sütunla çarpalım:
Şimdi rastgele sayılar içeren sütunu kopyalayıp B sütununa yapıştırıyoruz. Belgeye yeni veri girildiğinde bu sayıların değişmemesi için bu gereklidir.
Formül yerine değerler eklemek için B sütununa sağ tıklayın ve Özel Yapıştır aracını seçin. Açılan pencerede "Değerler" öğesinin önüne bir onay işareti koyun:
Artık B sütunundaki verileri artan veya azalan düzende sıralayabilirsiniz. Orijinal değerlerin sunulduğu sıra da değişecektir. Yukarıdan veya aşağıdan herhangi bir sayıda satır seçiyoruz - rastgele bir örnek alacağız.
Çeşitli (39)
Excel hataları ve aksaklıkları (3)
Benzersiz (yinelenmeyen) değerlerin listesini nasıl alabilirim?
Farklı isimlerden, isimlerden, personel numaralarından vb. oluşan geniş bir liste hayal edin. Ve bu listeden aynı isimlerin bir listesini bırakmak gerekir, ancak kendilerini tekrar etmemeleri için - yani. tüm yinelenen girişleri bu listeden kaldırın. Başka bir şekilde adlandırıldığı gibi: benzersiz öğelerin bir listesini oluşturun, tekrarlanmayan bir liste oluşturun, kopya yok. Bunu yapmanın birkaç yolu vardır: yerleşik Excel araçları, yerleşik formüller ve son olarak kod kullanma Uygulama için Visual Basic (VBA) ve pivot tablolar. Bu makale seçeneklerin her birine bakacaktır.
Excel 2007 ve üzeri sürümlerde yerleşik özelliklerle
Excel 2007 ve 2010'da bunu yapmak armutları soymak kadar kolaydır. özel ekip, buna denir -. Sekmesinde bulunur Veri alt bölüm Veri araçları
Bu komut nasıl kullanılır. Sütunu vurgulayın (veya birkaç) mükerrer kayıtların silinmesi gereken verilerle. sekmeye git Veri -Yinelenenleri Kaldır.
Bir sütun seçerseniz, ancak yanında veri içeren daha fazla sütun (veya en az bir sütun) olacaksa, Excel şunları seçmenizi önerecektir: seçim aralığını bu sütunla genişletin veya seçimi olduğu gibi bırakın ve yalnızca verileri silin seçilen aralıkta. Aralığı genişletmezseniz, verilerin yalnızca bir sütunda değişeceğini ve bitişik sütundaki verilerin değişmeden kalacağını hatırlamak önemlidir.
Kopyaları kaldırma seçeneklerinin bulunduğu bir pencere açılacaktır. 
Kaldırmak istediğiniz sütunların, kopyaların önündeki kutuları işaretleyin ve Tamam'ı tıklayın. Veri başlıkları da seçilen aralıkta bulunuyorsa, bayrağı ayarlamak daha iyidir. Verilerim başlıklar içeriyor tablodaki verilerin yanlışlıkla silinmesini önlemek için (birdenbire başlıktaki değerle tamamen örtüşürlerse).
Yöntem 1: Gelişmiş filtre
Excel 2003 durumunda işler daha karmaşıktır. gibi bir araç yok Yinelenenleri kaldır... Ama öte yandan, harika bir araç var. Gelişmiş filtre... 2003 yılında bu araç şurada bulunabilir: Veri -Filtre -Gelişmiş filtre... Bu yöntemin güzelliği, orijinal verileri bozmak yerine farklı bir aralıkta bir liste oluşturmak için kullanabilmenizdir. 2007-2010 Excel'de de orada ama biraz gizli. Sekmede bulunur Veri, grup Sırala ve Filtrele - ileri
Nasıl kullanılır: belirtilen aracı çalıştırın - bir iletişim kutusu belirir: 
- Tedavi: Biz seciyoruz Sonucu başka bir konuma kopyalayın.
- Liste aralığı: Veri içeren bir aralık seçme (bizim durumumuzda A1: A51'dir).
- Ölçüt aralığı: bu durumda boş bırakıyoruz.
- Aralığa kopyala: verileri görüntülemek için ilk hücreyi belirtiriz - herhangi bir boş (resimde - E2).
- Kutuyu kontrol et Yalnızca benzersiz kayıtlar.
- basıyoruz Tamam.
Not: sonucu başka bir sayfaya yerleştirmek istiyorsanız, başka bir sayfa belirtemezsiniz. Başka bir sayfada bir hücre belirtebileceksiniz, ancak ... Ne yazık ki ve ah ... Excel, verileri diğer sayfalara kopyalayamayacağına dair bir mesaj verecek. Ancak bu da atlatılabilir ve oldukça basit bir şekilde. Sadece koşman gerekiyor Gelişmiş filtre sonucu yerleştirmek istediğimiz sayfadan. Ve ilk veriler olarak, herhangi bir sayfadan veri seçiyoruz - buna izin verilir.
Ayrıca sonucu diğer hücrelere aktaramazsınız, ancak verileri yerinde filtreleyebilirsiniz. Veriler hiçbir şekilde etkilenmeyecektir - bu, olağan veri filtreleme olacaktır.
Bunu yapmak için, İşleme bölümünde seçmeniz yeterlidir. Listeyi yerinde filtreleyin.
Yöntem 2: Formüller
Bu yöntem deneyimsiz kullanıcılar için anlaşılması daha zordur, ancak orijinal verileri değiştirmeden benzersiz değerler listesi oluşturur. Aynı zamanda daha dinamiktir: Kaynak tablodaki verileri değiştirirseniz sonuç da değişecektir. Bu bazen yararlıdır. Neyi ve neyi parmaklarımda açıklamaya çalışacağım: örneğin, A sütununda (A1: A51, A1'in başlık olduğu) verileri içeren bir listeniz var. Listeyi C2 hücresinden başlayarak C sütununda görüntüleyeceğiz. C2'deki formül aşağıdaki gibi olacaktır:
(= INDEX ($ A $ 2: $ A $ 51; KÜÇÜK (EĞERSAY ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; SATIR ($ A $ 1: $ A $) 50))); 1))) )
(= INDEX ($ A $ 2: $ A $ 51; KÜÇÜK (EĞERSAY ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; SATIR ($ A $ 1: $ A $) 50))); 1))) )
Bu formülün çalışmasının ayrıntılı bir analizi makalede verilmiştir:
Bu formülün bir dizi formülü olduğuna dikkat edilmelidir. Bu söylenebilir diş telleri, bu formülün ekinde. Ve böyle bir formül, klavye kısayoluyla bir hücreye girilir - Ctrl+Vardiya+Girmek... Bu formülü C2'ye girdikten sonra, tüm benzersiz öğeleri doğru bir şekilde görüntülemek için birkaç satıra kopyalayıp yapıştırmalıyız. Alt hücrelerdeki formül döner dönmez #SAYI!- bu, tüm öğelerin görüntülendiği ve aşağıdaki formülü uzatmanın bir anlamı olmadığı anlamına gelir. Hatayı önlemek ve formülü daha evrensel hale getirmek için (hata görünene kadar her seferinde uzatmadan), basit bir kontrol kullanabilirsiniz:
Excel 2007 ve üzeri için:
(= EĞERHATA (INDEX ($ A $ 2: $ A $ 51; KÜÇÜK (EĞERSAY ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; STRING ($ A $ 1: $ A $ 50 $)); 1 )); ""))
(= EĞERHATA (INDEX ($ A $ 2: $ A $ 51; KÜÇÜK (EĞERSAY ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; SATIR ($ A $ 1: $ A $ 50 $)); 1 )); ""))
Excel 2003 için:
(= EĞER (ISH (KÜÇÜK (EĞERSAY ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; SATIR ($ A $ 1: $ A $ 50)); 1); ""; INDEX ( $ A $ 2: $ A $ 51; KÜÇÜK (EĞERSAY (EĞERSAY ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; SATIR ($ A $ 1: $ A 50 $)); 1)))))
(= EĞER (ISERR (KÜÇÜK (EĞERSAY ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; SATIR ($ A $ 1: $ A $ 50)); 1); ""; INDEX ( $ A $ 2: $ A $ 51; KÜÇÜK (EĞERSAY (EĞERSAY ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; SATIR ($ A $ 1: $ A 50 $)); 1)))))
O zaman bir hata yerine #SAYI! (#SAYI!) sahip olacaksın boş hücreler(tabii ki tamamen boş değil - formüllerle :-)).
ESLIOSHIBKA ve IF formüllerinin farklılıkları ve nüansları hakkında biraz daha ayrıntı (EOSH bu makalede okunabilir: Hata yerine formül içeren bir hücrede 0 nasıl gösterilir?
Yöntem 3: VBA kodu
Bu yaklaşım, makroların çözümlenmesini ve onlarla çalışmanın temel bilgisini gerektirecektir. Bilginizden emin değilseniz, önce şu makaleleri okumanızı öneririm:
- Makro nedir ve onu nerede bulabilirim? makaleye bir video eğitimi eklenmiştir
- Modül nedir? Hangi modüller var? aşağıdaki kodları nereye ekleyeceğinizi bulmak için gerekli olacak
Aşağıdaki kodların her ikisi de yerleştirilmelidir standart modül... Makrolara izin verilmelidir.
İlk verileri aynı sırada bırakalım - verileri içeren liste "A" sütununda bulunur (A1: A51, burada A1 bir yöndür)... Listeyi yalnızca C sütununda değil, E2 hücresinden başlayarak E sütununda görüntüleyeceğiz:
Sub Extract_Unique () Dim vItem, avArr, li As Long ReDim avArr (1'e Rows.Count, 1'e 1) Hatada Yeni Koleksiyonla Aralıktaki Her vItem İçin Sonraki Devam Ettir ("A2", Cells (Rows.Count, 1) .End (xlUp)). Değer "(! LANG: Cells (Rows.Count, 1) .End (xlUp) - A sütunundaki son doldurulmuş hücreyi tanımlar. vItem, CStr (vItem) ekleyin Err = 0 ise, li = li + 1: avArr (li, 1) = vItem Else: Err.Clear End If Next End With If li Then .Resize (li) .Value = avArr End Sub
Bu kodu kullanarak, yalnızca bir sütundan değil, herhangi bir sütun ve satır aralığından benzersiz değerler çıkarabilirsiniz. Eğer satır yerine
Menzil ("A2", Cells(Rows.Count, 1).End(xlUp)).Value !}
Selection.Value belirtin, ardından kodun sonucu, etkin sayfada seçilen aralıktan benzersiz öğelerin bir listesi olacaktır. Ancak o zaman değerlerin çıktı hücresini değiştirmek güzel olurdu - yerine
veri olmayanı koyun.
Belirli bir aralık da belirtebilirsiniz:
Aralık ("C2", Hücreler (Rows.Count, 3). End (xlUp)) Değer
Benzersiz değerleri seçmek için evrensel kod
Aşağıdaki kod herhangi bir aralığa uygulanabilir. Çalıştırmak, yalnızca tekrarlanmayanları (birden fazla sütuna izin verilir) seçmek için değerler içeren bir aralık ve sonucu görüntülemek için bir hücre belirtmek yeterlidir. Belirtilen hücreler taranacak, bunlardan yalnızca benzersiz değerler(boş hücreler atlanır) ve ortaya çıkan liste belirtilen hücreden başlanarak yazılır.
| Sub Extract_Unique () Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "benzersiz değerler seçmek için hücrelerin adresini isteyin rVals = Application.InputBox olarak ayarlayın ( "Benzersiz değerleri örneklemek için bir hücre aralığı belirtin", "Veri Talebi", "A2: A51", Tür: = 8) rVals Hiçbir Şey Değilse O Zaman "İptal düğmesine tıklanırsa Alt Uçtan Çık Eğer "yalnızca bir hücre belirtilmişse, seçim yapmanın bir anlamı yoktur. rVals.Count = 1 ise MsgBox "Benzersiz değerleri filtrelemek için birden fazla hücre belirtmeniz gerekir", vbInformation, "www.site" Exit Sub End If "çalışma aralığının dışındaki boş satırları ve sütunları kesin Set rVals = Intersect (rVals, rVals.Parent.UsedRange) "yalnızca çalışma aralığının dışındaki boş hücreler belirtilirse Eğer rVals Hiçbir Şey Değilse MsgBox "Değerleri seçmek için yetersiz veri", vbInformation, "www.site" Alt Uçtan Çık AvVals = rVals.Value ise "sonucu görüntülemek için bir hücre isteyin rResultCell = Application.InputBox olarak ayarlayın ( "Seçilen benzersiz değerleri eklemek için bir hücre belirtin", "Request Data", "E2", Type: = 8) Eğer rResultCell Hiçbir Şey Değilse "İptal düğmesine tıklanırsa Alt Uçtan Çık Eğer "sonuç için dizinin olası maksimum boyutunu tanımlayın ReDim avArr (1'den Rows.Count'a, 1'e 1) "Bir Koleksiyon nesnesi kullanarak "yalnızca benzersiz kayıtları seçin, "çünkü Koleksiyonlar yinelenen değerler içeremez Yeni Koleksiyon ile Hatada Devam Et Sonraki Her x İçin AvVals If Len (CStr (x)) Sonra "boş hücreleri atla.x, CStr (x) ekleyin "eklenen öğe Koleksiyonda zaten varsa, bir hata oluşur "hata yoksa bu değer henüz girilmemiştir, "sonuçtaki diziye ekle Err = 0 ise O zaman li = li + 1 avArr (li, 1) = x Else "Error nesnesini temizlediğinizden emin olun. Err.Clear End If End If Next End With "sonucu belirtilen hücreden başlayarak sayfaya yaz If li Then rResultCell.Cells (1, 1) .Resize (li) .Value = avArr End Sub |
Sub Extract_Unique () Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "hücrelerin adresini talep ederek benzersiz değerler seçin Set rVals = Application.InputBox (" Aralığı belirtin hücre sayısı " , "Veri talebi", "A2: A51", Tip: = 8) rVals Hiçbir Şey Değilse O zaman "İptal düğmesine tıklanırsa Alt Uçtan Çık" sadece bir hücre belirtilmişse , rVals.Count = 1 ise seçmenin bir anlamı yoktur MsgBox " Benzersiz değerler seçmek için birden fazla hücre belirtmeniz gerekir ", vbInformation," www.site "Exit Sub End If" dışındaki boş satırları ve sütunları keser çalışma aralığı Set rVals = Intersect (rVals, rVals.Parent.UsedRange) "çalışma aralığı dışında yalnızca boş hücreler belirtilirse rVals Hiçbir Şey Değilse MsgBox "Değerleri seçmek için yeterli veri yok", vbInformation, "www..Value" (! LANG: sonucu görüntülemek için bir hücre isteyin Set rResultCell = Application.InputBox ("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "E2", Type:=8) If rResultCell Is Nothing Then "если нажата кнопка Отмена Exit Sub End If "определяем максимально возможную размерность массива для результата ReDim avArr(1 To Rows.Count, 1 To 1) "при помощи объекта Коллекции(Collection) "отбираем только уникальные записи, "т.к. Коллекции не могут содержать повторяющиеся значения With New Collection On Error Resume Next For Each x In avVals If Len(CStr(x)) Then "пропускаем пустые ячейки.Add x, CStr(x) "если добавляемый элемент уже есть в Коллекции - возникнет ошибка "если же ошибки нет - такое значение еще не внесено, "добавляем в результирующий массив If Err = 0 Then li = li + 1 avArr(li, 1) = x Else "обязательно очищаем объект Ошибки Err.Clear End If End If Next End With "записываем результат на лист, начиная с указанной ячейки If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr End Sub!}
Yöntem 4: pivot tablolar
Birçok standart olmayan yol benzersiz değerlerin alınması.
- Tabloda bir veya daha fazla sütun seçin, sekmeye gidin Sokmak-grup Tablo -Pivot tablo
- iletişim kutusunda PivotTable Oluştur veri aralığı seçiminin doğruluğunu kontrol ederiz (veya yeni kaynak veri)
- PivotTable'ın konumunu belirtin:
- Yeni Çalışma Sayfası
- Mevcut Çalışma Sayfası
- bir düğmeye basarak oluşturmayı onaylayın Tamam
Çünkü pivot tablolar, satırlar veya sütunlar alanına yerleştirilen verileri işlerken, sonraki analizler için onlardan yalnızca benzersiz değerler seçin, o zaman bir pivot tablo oluşturmak ve verilerini yerleştirmek dışında bizden kesinlikle hiçbir şey gerekmez. satır veya sütun alanında istenen sütun.
Makaleye ekli dosyayı örnek olarak kullanarak:
Bu durumda pivot tablolarla çalışmanın sakıncası nedir: kaynak veriler değişirse, pivot tablonun manuel olarak güncellenmesi gerekecektir: Pivot tablonun herhangi bir hücresini seçin - Sağ fare düğmesi - Yenile veya sekmesi Veri -Hepsini yenile -Yenile... Ve kaynak veriler dinamik olarak yenilenirse ve daha da kötüsü, kaynak verilerin aralığını yeniden belirlemek gerekecektir. Ve bir dezavantaj daha - pivot tablonun içindeki veriler değiştirilemez. Bu nedenle, gelecekte ortaya çıkan listeyle çalışmak gerekirse, oluşturduktan sonra istenen listeözet kullanılarak, istenen sayfaya kopyalanıp yapıştırılmalıdır.
Tüm adımları daha iyi anlamak ve pivot tabloların nasıl kullanılacağını öğrenmek için, pivot tablolar hakkında genel bilgiler - bir video eğitimi ekli makalesini okumanızı şiddetle tavsiye ederim. pivot tabloların özellikleri.
Ekli örnekte, açıklanan tekniklere ek olarak, benzersiz öğelerin formül ve kodla çıkarılmasının biraz daha karmaşık bir varyasyonu yazılmıştır, yani: kritere göre benzersiz elemanların çıkarılması... Ne hakkında konuşuyoruz: soyadının bir sütununda ve ikincisinde (B) bazı veriler varsa (dosyada bunlar aylardır) ve B sütununun benzersiz değerlerini yalnızca seçilen soyadı. Bu tür benzersiz ekstraksiyonların örnekleri sayfada yer almaktadır. kritere göre ayıkla.
Örnek indir:
(108.0 KiB, 14.152 indirme)
Makale yardımcı oldu mu? Bağlantıyı arkadaşlarınızla paylaşın! video dersleri
