İçerik
Dinamik diziler nelerdir
Eylül 2018'de Microsoft, Microsoft Excel'e tamamen yeni bir araç ekleyen bir güncelleme yayınladı: Dinamik Diziler ve bunlarla çalışmak için 7 yeni işlev. Bu şeyler, abartı olmadan, formüller ve işlevlerle çalışmanın olağan tekniklerini kökten değiştirir ve kelimenin tam anlamıyla her kullanıcıyı ilgilendirir.
Özü açıklamak için basit bir örnek düşünün.
Diyelim ki elimizde şehir-ay verileri olan basit bir tablo var. Sayfanın sağındaki herhangi bir boş hücreyi seçip içine bir hücreye değil, hemen bir aralığa bağlanan bir formül girersek ne olur?
Excel'in önceki tüm sürümlerinde, üzerine tıkladıktan sonra Keşfet sadece bir ilk hücre B2'nin içeriğini alırdık. Başka nasıl?
Ya da bu aralığı =SUM(B2:C4) gibi bir tür toplama işlevine sarmak ve bunun için genel bir toplam elde etmek mümkün olabilir.
Benzersiz değerleri veya İlk 3'ü çıkarmak gibi ilkel bir toplamdan daha karmaşık işlemlere ihtiyacımız olsaydı, formülümüzü klavye kısayolu kullanarak bir dizi formülü olarak girmemiz gerekirdi. Ctrl+vardiya+Keşfet.
Şimdi her şey farklı.
Şimdi böyle bir formül girdikten sonra üzerine tıklayabiliriz. Keşfet – ve sonuç olarak, atıfta bulunduğumuz tüm uXNUMXbuXNUMXb değerlerini hemen alın:
Bu sihir değil, Microsoft Excel'in şimdi sahip olduğu yeni dinamik diziler. Yeni dünyaya hoş geldiniz 🙂
Dinamik dizilerle çalışmanın özellikleri
Teknik olarak, dinamik dizimizin tamamı ilk G4 hücresinde depolanır ve gerekli sayıda hücre sağda ve aşağıda verileriyle doldurulur. Dizideki başka bir hücreyi seçerseniz, formül çubuğundaki bağlantı etkin olmayacak ve "alt" hücrelerden birinde olduğumuzu gösterecektir:
Bir veya daha fazla "alt" hücreyi silme girişimi hiçbir şeye yol açmaz - Excel bunları hemen yeniden hesaplar ve doldurur.
Aynı zamanda, diğer formüllerde bu "alt" hücrelere güvenle başvurabiliriz:
Bir dizinin ilk hücresini (örneğin, G4'ten F8'e) kopyalarsanız, dizinin tamamı (referansları) normal formüllerle aynı yönde hareket eder:
Diziyi hareket ettirmemiz gerekiyorsa, hareket ettirmemiz yeterli olacaktır (fare ile veya bir kombinasyonu ile) Ctrl+X, Ctrl+V), yine sadece ilk ana hücre G4 - ondan sonra yeni bir yere aktarılacak ve tüm dizimiz tekrar genişletilecek.
Oluşturulan dinamik diziye sayfada başka bir yere başvurmanız gerekiyorsa, baştaki hücrenin adresinden sonra # ("pound") özel karakterini kullanabilirsiniz:
Örneğin, artık oluşturulan dinamik diziye başvuran bir hücrede kolayca bir açılır liste oluşturabilirsiniz:
Dinamik dizi hataları
Ancak diziyi genişletmek için yeterli alan yoksa veya yolunda başka veriler tarafından işgal edilmiş hücreler varsa ne olur? Excel'de temelde yeni bir hata türüyle tanışın - #AKTAR! (#DÖKÜLME!):
Her zaman olduğu gibi, sarı elmaslı ve ünlem işaretli simgeye tıklarsak, sorunun kaynağı hakkında daha ayrıntılı bir açıklama alırız ve müdahale eden hücreleri hızla bulabiliriz:
Dizi sayfadan çıkarsa veya birleştirilmiş bir hücreye çarparsa benzer hatalar ortaya çıkar. Engeli kaldırırsanız, her şey anında düzeltilecektir.
Dinamik diziler ve akıllı tablolar
Dinamik dizi, klavye kısayolu tarafından oluşturulan "akıllı" bir tabloya işaret ediyorsa Ctrl+T ya da Ana Sayfa – Tablo olarak biçimlendir (Ana Sayfa — Tablo Olarak Biçimlendir), o zaman aynı zamanda ana kalitesini de (otomatik boyutlandırma) devralır.
En alta veya sağa yeni veriler eklerken, akıllı tablo ve dinamik aralık da otomatik olarak esner:
Ancak, bir sınırlama vardır: akıllı tablo içindeki forumlarda dinamik aralık referansı kullanamayız:
Dinamik diziler ve diğer Excel özellikleri
Tamam, diyorsun. Bütün bunlar ilginç ve komik. Daha önce olduğu gibi, formülü orijinal aralığın ilk hücresine referansla aşağı ve sağa ve hepsine manuel olarak uzatmaya gerek yok. Ve hepsi bu?
Pek değil.
Dinamik diziler yalnızca Excel'deki başka bir araç değildir. Artık Microsoft Excel'in tam kalbine (veya beynine) gömülüdürler - hesaplama motoru. Bu, artık bize tanıdık gelen diğer Excel formüllerinin ve işlevlerinin de dinamik dizilerle çalışmayı desteklediği anlamına gelir. Gerçekleşen değişikliklerin derinliği hakkında size bir fikir vermek için birkaç örneğe bakalım.
aktarmak
Bir aralığı transpoze etmek (satırları ve sütunları takas etmek) için Microsoft Excel'in her zaman yerleşik bir işlevi vardır. TAŞIMA (TRANSPOZ). Ancak, kullanabilmek için önce sonuçlar için aralığı doğru bir şekilde seçmelisiniz (örneğin, giriş 5×3 aralığındaysa, 3×5 seçmiş olmalısınız), ardından işlevi girin ve düğmesine basın. kombinasyon Ctrl+vardiya+Keşfet, çünkü yalnızca dizi formülü modunda çalışabilir.
Şimdi sadece bir hücre seçebilir, aynı formülü ona girebilir ve normale tıklayabilirsiniz. Keşfet – dinamik dizi her şeyi kendi başına yapacaktır:
Çarpım tablosu
Excel'de dizi formüllerinin faydalarını görselleştirmem istendiğinde verdiğim örnek buydu. Şimdi, Pisagor tablosunun tamamını hesaplamak için, ilk B2 hücresinde durmak, oraya iki diziyi (dikey ve yatay sayı kümesi 1..10) çarpan bir formül girmek ve tıklamak yeterlidir. Keşfet:
Yapıştırma ve kutu dönüştürme
Diziler yalnızca çoğaltılamaz, aynı zamanda standart operatör & (ve işareti) ile birbirine yapıştırılabilir. İki sütundan adı ve soyadını çıkarmamız ve orijinal verilerdeki atlama durumunu düzeltmemiz gerektiğini varsayalım. Bunu tüm diziyi oluşturan kısa bir formülle yapıyoruz ve ardından işlevi ona uyguluyoruz. PROPNAK (DÜZGÜN)kayıt defterini düzenlemek için:
Sonuç İlk 3
Diyelim ki, onları azalan düzende düzenleyerek, ilk üç sonucu elde etmek istediğimiz bir grup sayımız olduğunu varsayalım. Şimdi bu tek bir formülle ve yine herhangi bir formül olmadan yapılır. Ctrl+vardiya+Keşfet daha önce olduğu gibi:
Sonuçların bir sütuna değil, bir satıra yerleştirilmesini istiyorsanız, bu formüldeki iki nokta üst üste (satır ayırıcı) noktalı virgül (bir satır içinde eleman ayırıcı) ile değiştirmeniz yeterlidir. Excel'in İngilizce sürümünde bu ayırıcılar sırasıyla noktalı virgül ve virgüldür.
DÜŞEYARA aynı anda birden çok sütunu ayıklıyor
fonksiyonlar VPR (DÜŞEYARA) şimdi değerleri bir kerede değil, birkaç sütundan çekebilirsiniz - sadece sayılarını (istediğiniz herhangi bir sırada) işlevin üçüncü argümanında bir dizi olarak belirtin:
Dinamik bir dizi döndüren OFFSET işlevi
Veri analizi için en ilginç ve kullanışlı (DÜŞEYARA'dan sonra) işlevlerden biri, işlevdir. BERTARAF (TELAFİ ETMEK), ki bir zamanlar kitabımda bütün bir bölüm ve burada bir makale ayırdım. Bu işlevi anlamanın ve ustalaşmanın zorluğu, her zaman sonuç olarak bir dizi (aralık) veri döndürmesi olmuştur, ancak bunu göremedik, çünkü Excel hala dizilerle nasıl çalışacağını kutudan çıkarmıştı.
Şimdi bu sorun geçmişte kaldı. Şimdi, tek bir formül ve OFFSET tarafından döndürülen dinamik bir dizi kullanarak, herhangi bir sıralanmış tablodan belirli bir ürün için tüm satırları nasıl ayıklayabileceğinizi görün:
Argümanlarına bir göz atalım:
- A1 – başlangıç hücresi (referans noktası)
- Önlemler(F2;A2:A30;0) – başlangıç hücresinden ilk bulunan lahanaya kaymanın hesaplanması.
- 0 – “pencerenin” başlangıç hücresine göre sağa kayması
- СЧЁТЕСЛИ(A2:A30;F2) – döndürülen “pencerenin” yüksekliğinin hesaplanması – lahana bulunan satırların sayısı.
- 4 — yatay olarak "pencerenin" boyutu, yani çıktı 4 sütun
Dinamik Diziler için Yeni İşlevler
Eski işlevlerde dinamik dizi mekanizmasını desteklemenin yanı sıra, Microsoft Excel'e, özellikle dinamik dizilerle çalışmak için keskinleştirilmiş birkaç tamamen yeni işlev eklendi. Özellikle, bunlar:
- SINIF (ÇEŞİT) – giriş aralığını sıralar ve çıkışta dinamik bir dizi üretir
- SORTPO (GÖRE SIRALA) - bir aralığı diğerinden değerlere göre sıralayabilir
- FILTRE (FİLTRE) – belirtilen koşulları karşılayan kaynak aralığından satırları alır
- UNIK (BENZERSİZ) – bir aralıktan benzersiz değerleri çıkarır veya kopyaları kaldırır
- AĞIR (RANDARRAY) - belirli bir boyutta rasgele sayılar dizisi oluşturur
- DOĞUM SONRASI (SEKANS) — belirli bir adımla bir dizi sayıdan bir dizi oluşturur
Onlar hakkında daha fazla - biraz sonra. Düşünceli bir çalışma için ayrı bir makaleye (bir tane değil) değer 🙂
Sonuç
Yukarıda yazılan her şeyi okuduysanız, meydana gelen değişikliklerin ölçeğini zaten anladığınızı düşünüyorum. Excel'de pek çok şey artık daha kolay, daha kolay ve daha mantıklı yapılabilir. İtiraf etmeliyim ki, burada, bu sitede ve kitaplarımda artık kaç makalenin düzeltilmesi gerekeceği konusunda biraz şok oldum, ancak bunu gönül rahatlığıyla yapmaya hazırım.
Sonuçları özetlemek, profesyoneller dinamik diziler, aşağıdakileri yazabilirsiniz:
- Kombinasyonu unutabilirsin Ctrl+vardiya+Keşfet. Excel artık "normal formüller" ve "dizi formülleri" arasında hiçbir fark görmemekte ve bunlara aynı şekilde davranmaktadır.
- fonksiyon hakkında SUMPRODUCT (TOPLAMÇARPIM)daha önce dizi formüllerini girmeden girmek için kullanılan Ctrl+vardiya+Keşfet ayrıca unutabilirsiniz – artık yeterince kolay SUM и Keşfet.
- Akıllı tablolar ve tanıdık işlevler (SUM, IF, VLOOKUP, SUMIFS, vb.) artık dinamik dizileri tamamen veya kısmen destekliyor.
- Geriye dönük uyumluluk vardır: Excel'in eski bir sürümünde dinamik diziler içeren bir çalışma kitabı açarsanız, bunlar dizi formüllerine (kıvrımlı parantez içinde) dönüşecek ve “eski stilde” çalışmaya devam edecektir.
bir numara buldum eksiler:
- Dinamik bir diziden tek tek satırları, sütunları veya hücreleri silemezsiniz, yani tek bir varlık olarak yaşar.
- Dinamik bir diziyi her zamanki gibi sıralayamazsınız. Veri – Sıralama (Veri — Sıralama). Artık bunun için özel bir işlev var. SINIF (ÇEŞİT).
- Dinamik aralık akıllı masaya dönüştürülemez (ancak akıllı tabloya dayalı bir dinamik aralık oluşturabilirsiniz).
Elbette bu son değil ve eminim Microsoft gelecekte bu mekanizmayı geliştirmeye devam edecektir.
Nereden indirebilirim?
Ve son olarak asıl soru 🙂
Microsoft, ilk olarak Eylül 2018'de bir konferansta Excel'de dinamik dizilerin bir önizlemesini duyurdu ve gösterdi. Tutuşturmak. Önümüzdeki birkaç ay içinde, ilk olarak yeni özelliklerin kapsamlı bir şekilde test edilmesi ve çalıştırılması yapıldı. kediler Microsoft'un kendi çalışanları ve ardından Office Insiders çemberinden gönüllü test uzmanları. Bu yıl, dinamik diziler ekleyen güncelleme, normal Office 365 abonelerine kademeli olarak dağıtılmaya başlandı. Örneğin, Office 365 Pro Plus (Aylık Hedefli) aboneliğimle yalnızca Ağustos ayında aldım.
Excel'inizde henüz dinamik diziler yoksa, ancak bunlarla gerçekten çalışmak istiyorsanız, aşağıdaki seçenekler vardır:
- Office 365 aboneliğiniz varsa, bu güncelleştirmenin size ulaşmasını bekleyebilirsiniz. Bunun ne kadar hızlı gerçekleştiği, güncelleştirmelerin Office'inize ne sıklıkta teslim edildiğine bağlıdır (yılda bir, altı ayda bir, ayda bir). Kurumsal bir PC'niz varsa, yöneticinizden daha sık indirilecek güncellemeleri ayarlamasını isteyebilirsiniz.
- Bu Office Insider test gönüllülerinin saflarına katılabilirsiniz – o zaman tüm yeni özellikleri ve işlevleri ilk alan siz olacaksınız (ancak Excel'de daha fazla hata yapma şansı elbette var).
- Aboneliğiniz yoksa, ancak Excel'in kutulu bağımsız bir sürümü varsa, en azından 2022'de Office ve Excel'in bir sonraki sürümünün yayınlanmasını beklemeniz gerekecektir. Bu tür sürümlerin kullanıcıları yalnızca güvenlik güncellemeleri ve hata düzeltmeleri alır ve tüm yeni "güzellikler" artık yalnızca Office 365 abonelerine gider. Acı ama gerçek 🙂
Her durumda, Excel'inizde dinamik diziler göründüğünde - bu makaleden sonra buna hazır olacaksınız 🙂
- Dizi formülleri nedir ve Excel'de nasıl kullanılır?
- OFFSET işlevini kullanarak pencere (aralık) toplamı
- Excel'de Bir Tabloyu Transpoze Etmenin 3 Yolu