Excel for Mac’te bir grafik varlığı etiketiyle ilişkili tüm miktarları döndürmek için FILTER kullanın. Sonuç dizisi çalışma sayfası hücrelerine taşabilir, başka bir hesaplamada kullanılabilir veya yinelenen eşleşen girdiler kaybolmadan toplanabilir.

Bu kılavuzda sentetik grafik varlığı etiketleri ve miktarları kullanılır. Amaç bir etiket seçmek, bu etiketin tüm miktarlarını almak ve bu tek tek sayıları bir hesaplamada yeniden kullanmaktır. Microsoft, FILTER işlevini belirtilen ölçütlere göre bir dizi döndüren işlev olarak açıklar. FILTER işlevi.

Hangi Excel for Mac sürümleri bu yöntemi destekler?

Microsoft’un güncel işlev belgelerinde Excel for Microsoft 365 for Mac, Excel 2024 for Mac ve Excel 2021 for Mac sürümlerinin FILTER işlevini desteklediği açıkça belirtilir. Aşağıdaki formüller için bu sürümlerden birini kullanın. FILTER işlevi.

Yüklemenizi tanımlamak için Excel’i açıp Excel → Excel Hakkında menüsünü seçin. Microsoft bu Mac menü yolunu belgeliyor; sürüm belirleme kılavuzunda da Mac uygulamasının Hakkında iletişim kutusunda sürüm numarasının ve lisans türünün gösterildiği açıklanıyor. Yapı numarasının yanı sıra sürümü de kontrol edin. Office for Mac’in diğer sürümleriyle belge paylaşma; Office Hakkında: Hangi Office sürümünü kullanıyorum?.

Excel 2019 for Mac ve Excel 2016 for Mac, FILTER işlevinin belgelenmiş kullanılabilirlik kapsamı dışındadır. Bu sürümlerden birini kullanıyorsanız, bu dinamik dizi iş akışı için desteklenen yol çalışma kitabını listelenen Mac sürümlerinden birinde açmaktır. Aşağıdaki yönergeler bu ön koşula bağlıdır; eski dizi formüllerine yönelik bir geçici çözüm değildir.

Küçük bir varlık-miktar örneği oluşturma

Boş bir çalışma sayfasında aşağıdaki verileri A1:B7 aralığına girin. İlk satır başlıkları içerir. B6 hücresindeki sıfır da dahil olmak üzere miktarları sayı olarak girin.

Çalışma sayfası satırı A sütunu: Varlık B sütunu: Miktar
1 Varlık Miktar
2 Tile A 4
3 Arc B 7
4 Tile A 2
5 Dot C 5
6 Tile A 0
7 Tile A 4

D1 hücresine Seçilen varlık, D2 hücresine Tile A girin. F1 hücresine Eşleşen miktarlar yazın. Sonucun genişleyebilmesi için F2 hücresinin altındaki hücreleri boş bırakın.

Bu örnekte aynı etiket için bilerek iki adet dört ve bir adet sıfır miktarı bulunur. Doğru sonuç, eşleşen dört satırın tümünü korumalıdır: 4, 2, 0 ve 4. Yinelenen değerleri kaldırmak veya sıfırı dışarıda bırakmak istenen sonucu değiştirir.

FILTER ile eşleşen tüm miktarları döndürme

F2 hücresini seçin, aşağıdaki formülü girin ve Return tuşuna basın:

=FILTER(B2:B7,A2:A7=D2)

Formülde miktar dizisi olarak B2:B7, dahil etme testi olarak A2:A7=D2 kullanılır. Bu aralıklar aynı altı kaynak satırını kapsar ve Microsoft’un dahil etme dizisinin uyumlu bir yüksekliğe veya genişliğe sahip olması şartını karşılar. FILTER işlevi.

Örnek verilerle sonuç F2:F5 aralığını kaplar:

Hücre Sonuç
F2 4
F3 2
F4 0
F5 4

Formülü yalnızca F2 hücresine girin; Excel ek sonuçları komşu hücrelere otomatik olarak yerleştirir. Microsoft bu davranışı taşma olarak adlandırır. Formülü daha sonra değiştirmek için sonucun sol üst hücresini düzenleyin; burada bu hücre F2 olur. Dinamik dizi formülleri ve taşan dizi davranışı.

Dört değerinin iki kez görünmesi, ayrı kaynak satırlarının eşleştiğini gösterir. Bir sonraki hesaplamanız seçilen varlığın her örneğini hesaba katacaksa ikisini de koruyun.

XLOOKUP bu dört satırı neden getirmez?

Microsoft, XLOOKUP işlevinin bulduğu ilk eşleşmeye karşılık gelen öğeyi döndürdüğünü belirtir. Eşleşen bir satırdan birden fazla sütunu döndürebilir; ancak aynı etiketi içeren tüm satırları listelemez. XLOOKUP işlevi.

Örneğin aşağıdaki formül, örnekten yalnızca 4 değerini döndürür:

=XLOOKUP(D2,A2:A7,B2:B7)

Tek bir eşleşen satırın yanıtı sağladığı durumlarda bu sonuç uygundur. Bu görevde ise miktarları sağlayan dört ayrı eşleşen satır vardır; bu nedenle FILTER sonucunu kullanın.

Diziyi sonraki hesaplamalarda yeniden kullanma

Sonucun tamamına F2# ile başvurun. Microsoft, kare işaretini taşan aralık işlecini belirtmek için kullanır: başlangıç hücresinden üretilen dizinin tamamına başvurur. Böylece sonraki formülü mevcut dört hücrelik sonuca sabitlemeniz gerekmez. Dizi formülleriyle ilgili yönergeler ve örnekler.

Daha fazla eşleşen miktara yer açmak için sonraki formülleri sonucun hemen altına değil, yanına yazın. Aşağıdaki örneklerde H ile J sütunları kullanılır.

Eşleşen tüm miktarları toplama

H1 hücresine Toplam miktar yazın, ardından H2 hücresine şu formülü girin:

=SUM(F2#)

Örnek sonuç 10 olur: dört artı iki artı sıfır artı dört. SUM işlevi aralıkları kabul eder ve bu aralıklardaki sayısal değerleri toplar. SUM işlevi.

Yalnızca toplam gerekiyorsa, bunu doğrudan başka bir boş hücrede de hesaplayabilirsiniz:

=SUM(FILTER(B2:B7,A2:A7=D2))

Tek tek miktarları incelemek veya yeniden kullanmak istiyorsanız görünür diziyi seçin. Çalışma sayfasında yalnızca son toplam gerekiyorsa iç içe formülü seçin.

Her miktarı ortak bir katsayıyla çarpma

Örnekte eşleşen her miktarın üç kopyasının gerektiğini varsayalım. I1 hücresine Kopya, I2 hücresine 3 sayısını girin. J1 hücresine Düzeltilmiş miktarlar yazın ve ardından J2 hücresine bu formülü girin:

=F2#*$I$2

J2:J5 aralığında beklenen sonuçlar 12, 6, 0 ve 12 olur. Microsoft, değerleri çarpıp birden fazla sonuç döndüren dizi hesaplamalarını belgeliyor; Mac hesap makinesi kılavuzunda da yıldız işaretinin çarpma işleci olduğu belirtiliyor. Dizi formülleriyle ilgili yönergeler ve örnekler; Excel for Mac’te Excel’i hesap makinesi olarak kullanma.

Tek bir düzeltilmiş toplam için H4 hücresine şunu girin:

=SUM(F2#*$I$2)

Beklenen toplam 30 olur. Bunlar sağlanan sentetik verilerle yapılan örnek hesaplamalardır. Aritmetik bağımsız olarak kontrol edildi; çalışma kitabı formülleri Excel for Mac’te çalıştırılmak yerine Microsoft’un belgelerine göre incelendi.

Eşleşme olmadığında ne olacağına karar verme

D2 hücresini örnekte bulunmayan Wave D olarak değiştirin. Özgün iki bağımsız değişkenli FILTER formülü #CALC! hatası döndürür; çünkü Excel boş bir dizi döndüremez. Microsoft, sonuç olmadığında kullanılacak değeri isteğe bağlı üçüncü bağımsız değişkenle belirleyebileceğinizi açıklar. #CALC! hatasını düzeltme.

Bu değeri sonraki hesaplamanın gereksinimlerine göre seçin. Aşağıdaki karar yardımı bu çalışma sayfası için geçerlidir:

Gereksinim Formül veya yaklaşım Sonuç
Hesaplamalar sırasında eksik eşleşmeler görünür kalmalı =FILTER(B2:B7,A2:A7=D2) kullanın Eşleşme bulunmazsa #CALC! döner ve araştırma yapılmasını sağlar.
Sonuç esas olarak okunabilir bir liste olmalı =FILTER(B2:B7,A2:A7=D2,"No matches") kullanın Eşleşme bulunmazsa bir metin iletisi döner.
Olmayan bir etiket toplama sıfır olarak katkıda bulunmalı =SUM(FILTER(B2:B7,A2:A7=D2,0)) kullanın Eşleşme bulunmazsa toplam sıfır olur.

Sıfır yedek değeri, bilinçli bir hesaplama kuralıdır. Bu, kaynak satırlarından birinde sıfır miktar bulunduğu anlamına gelmez. Bu örnekte Tile A için gerçek miktar sıfırdır; Wave D içinse kaynak satırı yoktur. Bir varlığın mevcut olup olmadığı önemliyse bu ayrımı koruyun.

Metin iletisi de dikkat gerektirir: Microsoft, SUM işlevinin başvurulan hücrelerdeki metni yok saydığını belirtir. Sonuç olarak yalnızca Eşleşme yok içeren bir sonucu toplamak sıfır verebilir ve eşleşen satırların bulunmadığını gizleyebilir. Olmayan bir etiket nedeniyle sonraki hesaplamaların durması gerekiyorsa görünür hata yaklaşımını kullanın. SUM işlevi.

Özgün sayısal örneklere devam etmeden önce D2 değerini yeniden Tile A olarak ayarlayın.

Yalnızca görev gerektiriyorsa ikinci bir koşul ekleyin

Sonraki bir görev özellikle pozitif miktarları istiyorsa hem etiket testini hem de pozitif miktar testini kullanın:

=FILTER(B2:B7,(A2:A7=D2)*(B2:B7>0))

Bu, örnek için 4, 2 ve 4 değerlerini döndürür. Microsoft, FILTER işlevinde koşullar arasında çarpma işleminin VE testi olarak kullanıldığını belgeliyor. Bu değişiklik eşleşen sıfırı bilerek dışarıda bırakır; bu nedenle eşleşen her miktar gerekiyorsa özgün formülü koruyun. FILTER işlevi.

Sonuçla ilgili sorunları giderme ve yeni satırları kapsama

Formül beklenen dört miktarı üretmiyorsa çalışma sayfasını şu sırayla kontrol edin:

  1. Sonuç alanını inceleyin. Sonuç aralığının engellenmesi #SPILL! hatasına neden olur. Yeniden denemeden önce, gereken içeriği amaçlanan taşma alanından çıkarın.
  2. Formülün konumunu kontrol edin. Taşan dizi formülleri, Excel tablosunun dışında, çalışma sayfası kılavuzuna girilmelidir.
  3. Çalışma kitabı bağımlılıklarını kontrol edin. Microsoft, çalışma kitapları arasındaki bağlı dinamik dizileri yalnızca her iki çalışma kitabı da açıkken destekler; kaynak dosya kapatıldıktan sonra yenileme işlemi #REF! hatasına yol açabilir.

Bu sınırlar Dinamik dizi formülleri ve taşan dizi davranışı sayfasında belgelenmiştir.

Ardından kaynak etiketlerini ve miktarlarını inceleyin. D2 değerini amaçlanan varlık hücreleriyle karşılaştırın ve miktarların sayı olarak girildiğini kontrol edin. Tek başına makul görünen bir toplam yeterli doğrulama değildir; çünkü SUM metin değerlerini yok sayabilir. SUM işlevi.

Örnek formüller 2 ile 7. satırlar arasındaki kaynak satırlarına başvurur. 8. satıra veri eklerseniz her iki kaynak aralığını da bu satırı kapsayacak şekilde düzenleyin. Örneğin A8 hücresine Tile A, B8 hücresine sayısal 3 miktarını girdikten sonra F2 hücresindeki formülü şu formülle değiştirin:

=FILTER(B2:B8,A2:A8=D2)

Beklenen dizi 4, 2, 0, 4, 3 olur ve toplam 13 değerine çıkar. F2# başvurusu genişleyen sonucu izler; ancak özgün kaynak aralıklarının atlanan bir satırı kapsamasını sağlayamaz.

Sık sık büyüyen bir liste için Microsoft, tablo satırları eklenip kaldırıldıkça başvuruları ayarlanan yapılandırılmış başvurulara sahip bir Excel tablosu önerir. Taşan sonucu bu tablonun dışında tutun. Dinamik dizi formülleri ve taşan dizi davranışı.

Son toplama güvenmeden önce tek tek sonuçları kontrol edin: özgün altı kaynak satırında Tile A seçildiğinde iki dört değerini ve sıfırı içeren dört miktar dönmelidir. Bu sonuç doğruysa hesaplamalarda F2# başvurusunu yeniden kullanın ve çalışma sayfasının amacına uygun, eşleşme bulunamadığında uygulanacak açık bir kural seçin.

Kaynaklar