Yinelenen sanat varlığı etiketlerini ve miktarlarını envanter satırlarına dönüştürmek için Microsoft 365 for Mac veya Excel 2024 for Mac’te CHOOSECOLS, TOCOL, HSTACK ve FILTER işlevlerini birleştirin. Aşağıdaki örnek formül, çiftlerin hizalamasını, sıfır miktarları ve eksik girişleri korur.

Bu anlatım, çalışma sayfasında bir öznitelik sütununun ardından miktar sütununun tekrarlandığı bir Mac masaüstü kullanıcısı içindir. Burada her öznitelik, Circle Tile gibi bir sanat varlığı etiketidir. Amaç, iki sütunlu bir envanter oluşturmak ve ardından miktarları etiketlerinden ayırmadan bulmaktır.

Excel for Mac’in hangi sürümleri bu formülü destekler?

Microsoft, üç dönüştürme işlevinin tümü için Excel for Microsoft 365 for Mac ve Excel 2024 for Mac sürümlerini açıkça listeler: CHOOSECOLS, TOCOL ve HSTACK. Ana formül için bu sürümlerden birini kullanın.

Excel 2021 for Mac dinamik dizileri, FILTER, LET ve XLOOKUP işlevlerini içerir; ancak bu, daha yeni dönüştürme işlevlerini desteklediğini göstermez. Microsoft’un Excel 2021 for Mac özellik belgeleri, bu sürümdeki dizi ve arama özelliklerini açıklar; yukarıdaki üç işlev sayfasında Excel 2021 listelenmez.

Sürümünüzü ve lisans türünüzü uygulamanın About iletişim kutusunda kontrol edin. Microsoft’un Office sürüm kılavuzu, Mac iletişim kutusunda hem sürümün hem de lisans türünün gösterildiğini açıklar. Sürümünüz dönüştürme işlevlerini içermiyorsa otomatik yöntem için Microsoft 365 for Mac veya Excel 2024 for Mac kullanın ya da aşağıda açıklanan manuel yığma yöntemini uygulayın.

Eksiksiz bir örnek oluşturun

Boş bir çalışma sayfasında bu verileri A1:F4 aralığına girin. 3. satırın ortasındaki iki hücreyi gerçekten boş bırakın. Tüm etiketler ve miktarlar örnek amaçlıdır.

Öznitelik 1 Miktar 1 Öznitelik 2 Miktar 2 Öznitelik 3 Miktar 3
Circle Tile 4 Line Texture 2 Dot Pattern 0
Arc Motif 3 Grid Texture 5
Circle Tile 7 Wave Pattern 6 Square Tile 1

Yan yana duran her çift bir envanter girdisini temsil eder. İstenen çıktı, her kaynak satırını soldan sağa okur ve ardından aşağı doğru devam eder. Circle Tile iki kez görünür; çünkü bunlar ayrı girdilerdir. Yinelenen etiketleri birleştirmek ayrı bir işlemdir.

Bu yöntem, her etiketin hemen ardından miktarının geldiğini ve seçilen tüm sütunların bu yinelenen düzene ait olduğunu varsayar. Sayfanızda her girdiye eşlik etmesi gereken bir koleksiyon adı veya başka bir alan da varsa bu iki sütunlu çıktı onu korumaz. Formülü uygulamadan önce envanterin hangi alanlara ihtiyacı olduğunu belirleyin.

Yinelenen çiftleri envanter satırlarına dönüştürün

H1 hücresine Asset, I1 hücresine de Quantity yazın. Bu başlıkların altındaki çıktı alanını boş bırakın. Formülün tamamını H2 hücresine yapıştırıp Return tuşuna basın. Microsoft’un dizi formülü kılavuzunda açıklandığı gibi, dinamik dizi formülleri bir kez girilir ve komşu hücrelere yayılır.

=LET(
    source,A2:F4,
    clean,IF(source="","",source),
    labels,TOCOL(CHOOSECOLS(clean,1,3,5),0,FALSE),
    quantities,TOCOL(CHOOSECOLS(clean,2,4,6),0,FALSE),
    keep,((labels<>"")+(quantities<>""))>0,
    FILTER(HSTACK(labels,quantities),keep,"No inventory entries")
)

Beklenen sonuç H2:I9 aralığını kaplar:

Varlık Miktar
Circle Tile 4
Line Texture 2
Dot Pattern 0
Arc Motif 3
Grid Texture 5
Circle Tile 7
Wave Pattern 6
Square Tile 1

Örnekteki çift sırası, sekiz çıktı girdisi, sıfırın korunması ve toplam 28 miktar, küçük ve belirli sonuçlar veren bir Python hesaplamasıyla kontrol edildi. Excel formülü, Microsoft’un belgelenmiş işlev sözdizimine göre incelendi; Excel’de çalıştırılmadı.

Formül çiftlerin hizalamasını nasıl korur?

LET, formülün ara sonuçları yeniden kullanabilmesi için onlara adlar verir. source, clean, labels, quantities ve keep adları bu formül içinde geçerlidir; adlandırılmış aralıklar oluşturmanız gerekmez. Microsoft bu davranışı LET başvurusunda açıklar.

CHOOSECOLS, 1, 3 ve 5 numaralı etiket sütunlarını; 2, 4 ve 6 numaralı miktar sütunlarını seçer. Bu sayılar, seçilen kaynak aralığındaki konumları belirtir. Microsoft’un CHOOSECOLS belgeleri, bu işlevin bir diziden belirtilen sütunları döndürdüğünü doğrular.

Her iki TOCOL çağrısı, tüm konumları korumak için 0 işlevini, satırlar boyunca tarama yapmak için de FALSE işlevini kullanır. Eşleşen konumları aynı sırayla taradıkları için her etiket kendi miktarıyla hizalı kalır. Microsoft, TOCOL başvurusunda her iki bağımsız değişkeni de belgeler.

HSTACK, elde edilen iki sütunu yan yana yerleştirir. Filtrelemeden önce her ikisi de dokuz konum içerdiğinden uzunlukları eşittir. Microsoft, HSTACK işlevinin dizileri yatay olarak eklediğini ve satır sayıları farklıysa kısa dizileri hatalarla doldurduğunu açıklar.

Son olarak keep, etiket, miktar veya her ikisini içeren konumları seçer. FILTER yalnızca tamamen boş çiftleri kaldırır. Son bağımsız değişkeni, Microsoft’un belgelenmiş FILTER sözdizimine uygun olarak, hiç girdi kalmadığında gösterilecek bir ileti sağlar.

Eksik girişleri gizlemeden boşları ve sıfırları koruyun

Boş miktar ile sayısal sıfır farklı bilgiler taşır. Sıfır, girilmiş bir sayımdır; boş hücrenin ise yorumlanması gerekir. Microsoft’un TOCOL örneklerinde, işlenmemiş boş hücrelerin sıfır döndürebildiği gösterilir. Bu nedenle clean adımı, dönüştürme işleminden önce boş kaynak hücreler için boş metin döndürmek amacıyla IF işlevini kullanır. Microsoft, koşullu dönüş değerlerini IF başvurusunda belgeler.

Formül, eksik çiftleri bilerek görünür tutar. Sonuca güvenmeden önce şu kontrolleri yapın:

Kaynak çifti Çıktı davranışı Yapılacak işlem
Etiket ve sayısal miktar İkisini de korur Miktarın amaçlanan değer olduğunu kontrol edin
Etiket ve sıfır Sıfırı korur Sıfır bilinçli olarak girilmiş bir sayıysa koruyun
Her iki hücre de boş Çifti kaldırır Düzeltme gerekmez
Etiket var, miktar boş Etiketi ve boş miktarı korur Miktarı tamamlayın
Etiket boş, miktar var Boş etiketi ve miktarı korur Etiketi tamamlayın

Etiket ve miktar dizilerindeki boşları birbirinden bağımsız biçimde kaldırmayın. Bir konumda etiket, başka bir konumda miktar eksikse boşları ayrı ayrı kaldırmak dizilerin kaymasına ve çiftlerin yanlış eşleşmesine neden olabilir. Bu formüldeki temel tasarım tercihi, dizileri birleştirme sonrasına kadar konumlarını koruyacak şekilde tutmaktır.

Boş gibi görünen ancak boşluk karakterleri içeren bir hücre bu formülde boş kabul edilmez. Bu tür girdileri kaynakta düzeltin. Benzer şekilde formül, metin olarak girilmiş miktarları otomatik olarak sayıya dönüştürmez. Toplamları hesaplamadan önce bu değerleri gözden geçirin.

Tek bir girdiyi arayın veya eşleşen tüm girdileri döndürün

K2 hücresine Circle Tile yazın. İlk miktarını almak için L2 hücresine şu formülü girin:

=XLOOKUP(K2,CHOOSECOLS(H2#,1),CHOOSECOLS(H2#,2),"No matching asset",0)

H2#, H2 hücresinden başlayan ve sonuçların taşmasıyla oluşan aralığın tamamını ifade eder; böylece aramanın boyutu değiştiğinde formül de buna uyar. Microsoft bu gösterimi taşan aralık işleci belgelerinde açıklar.

Sonuç 4’tür. Açık eşleşme modu 0 tam eşleşme ister ve varsayılan arama ilk eşleşen girdiyi döndürür. Microsoft’un XLOOKUP başvurusu her iki davranışı da belgeler. Ayrıca XLOOKUP işlevinin Excel 2016 ve Excel 2019’da kullanılamadığını doğrular.

Yinelenen etiketler anlam taşıyorsa ilk eşleşmeyi aramak yeterli olmaz. Tüm Circle Tile girdilerini döndürmek için sütunlarda N:O yer bırakıp bu alternatif formülün tamamını N2 hücresine girin:

=FILTER(H2#,CHOOSECOLS(H2#,1)=K2,"No matching asset")

Bu formül iki satır döndürür: Circle Tile için 4 ve Circle Tile için 7. FILTER, sağlanan koşulu karşılayan satırları döndürür; Microsoft’un FILTER başvurusu bu davranışı belgeler. Eşleşen tek bir girdi sorunuzun yanıtıysa ilk eşleşme formülünü; her örneği incelemeniz gerekiyorsa filtrelenmiş sonucu seçin.

Sonucu doğrulayın ve kaynak aralığını uyarlayın

Örnek için sekiz çıktı satırı olduğunu doğrulayın ve Dot Pattern miktarının hâlâ sıfır olduğunu kontrol edin. Ardından kaynak miktar toplamını dönüştürülmüş miktar toplamıyla karşılaştırın. Kullanılmayan iki hücreye şu formülleri girin:

=SUM(B2:B4,D2:D4,F2:F4)

=SUM(CHOOSECOLS(H2#,2))

Her ikisi de 28 döndürmelidir. Microsoft’un SUM belgeleri, birden fazla aralığın toplanmasını destekler ve metin değerlerinin yok sayıldığını açıklar. Bu nedenle toplamların eşleşmesi, her miktarın sayısal olduğunu kanıtlamaz ve etiketlerle miktarların yer değiştirdiğini gösteremez. Etiket ve miktar çiftlerini de kontrol edin.

Daha fazla kaynak satırı için ana formüldeki A2:F4 aralığını A2:F100 gibi gerçek aralıkla değiştirin. Bu başvurunun dışındaki satırlar hesaba katılmaz. Yinelenen çift sayısı dörde çıkarsa kaynağı A2:H100 gibi sekiz sütuna genişletin ve etiket konumlarını 1,3,5,7, miktar konumlarını da 2,4,6,8 olarak güncelleyin.

Kaynak aralığını sekiz sütuna genişletirken çıktıyı J2 gibi temiz bir alana taşıyın ve sonraki taşan aralık başvurularını J2# olarak güncelleyin. Kaynak ve çıktıyı ayrı tutun. CHOOSECOLS hatalarında açıklandığı gibi, geçersiz bir sütun konumu hata üretir.

Engellenen çıktıyı çözün veya manuel yığma yöntemini kullanın

Ana formül #SPILL! döndürürse formül hücresini seçip hedeflenen çıktı alanını inceleyin. Mevcut girdileri bu alanın dışına taşıyın ve birleştirilmiş hücre olup olmadığını kontrol edin. Formülü Excel Tablosu dışında bir yere yerleştirin: Microsoft’un #SPILL! sorun giderme kılavuzunda açıklandığı gibi, taşan dizi formülleri tabloların içinde taşamaz ve birleştirilmiş hücrelere de taşamaz.

Kaynakta bir hata varsa envanteri kullanmadan önce kaynak girdisini düzeltin. Formül hataları gizlemez; Microsoft, FILTER işlevinin dahil etme dizisindeki bir hatanın işlevin hata döndürmesine neden olduğunu belirtir. Dönüşümün tamamını hata gizleme işlevleriyle sarmak, düzeltilmesi gereken bir girdiyi görünmez kılabilir.

Excel 2021 for Mac’te küçük bir çalışma sayfası için aynı iki sütunlu yapıyı manuel olarak elde edebilirsiniz: A2:B4 aralığını H2:I4 aralığına, C2:D4 aralığını H5:I7 aralığına ve E2:F4 aralığını H8:I10 aralığına kopyalayın. Yalnızca her iki hücrenin de boş olduğu satırları silin. Bu yöntem, ana formülün kaynak satır sırası yerine çift bloklarına göre yığma yapar ve kaynak değiştikten sonra işlemin tekrarlanmasını gerektirir.

Bu manuel kurulumdan sonra Excel 2021 for Mac, sabit aralıklarla belgelenmiş XLOOKUP özelliğini kullanabilir:

=XLOOKUP(K2,H2:H10,I2:I10,"No matching asset",0)

İyi bir envanter, anlamlı her çifti korur, eksik girdileri görünür kılar ve yinelenen etiketlere uygun bir arama yöntemi kullanır. Önce örnek üzerinde bu özellikleri doğrulayın, ardından kaynak aralığını çalışma sayfanıza uyacak şekilde genişletin.

Kaynaklar