Bir sanat eseri varlık miktar matrisini, her öğe için çalışma sayfasında bir satır olacak şekilde genişletmek için önce her varlık–biçim birleşimini kümülatif miktarıyla listeleyin. Ardından etiketlerini gereken sayıda tekrarlamak için SEQUENCE ve XLOOKUP kullanın. Bu anlatımda örnek amaçlı sanat eseri etiketleri kullanılır.

Görev basittir: kompakt bir planlama tablosunu öğe listesine dönüştürmek. İki miktarı, aynı varlık ve biçime sahip iki satır oluşturur; sıfır ise hiç satır oluşturmaz. Aşağıdaki örnek, sonucu uyarlamadan önce kontrol edebilmeniz için ara hesaplamaları görünür tutar.

Mac için Excel’in hangi sürümleri bu yöntemi destekler?

Mac için Microsoft 365 Excel, Mac için Excel 2024 veya Mac için Excel 2021 kullanın. Microsoft’un belgelerinde bu sürümler hem SEQUENCE hem de XLOOKUP için listelenir. Ayrıca Mac için Excel 2021 özellik kılavuzunda dinamik diziler açıkça belgelenmiştir. SEQUENCE işlevi, XLOOKUP işlevi, Mac için Excel 2021’deki yenilikler.

Diğer anlatımlardaki formülleri kopyalarken işlev kullanılabilirliği önemlidir. Örneğin Microsoft, VSTACK işlevini Mac’te Microsoft 365 ve Excel 2024 için listeler, ancak Excel 2021 için listelemez. Buradaki yöntem bu bağımlılığı önler. Microsoft ayrıca XLOOKUP işlevinin Excel 2016 ve Excel 2019’da kullanılamadığını açıkça belirtir; bu nedenle bu sürümler aşağıdaki formülleri değiştirmeden çalıştıramaz. VSTACK işlevi, XLOOKUP işlevi.

Sanat eseri varlık miktar matrisini ayarlama

Boş bir çalışma sayfasında, aşağıdaki örnek verileri A1:C4 aralığına girin. İlk sütunda varlık etiketleri, başlık satırında biçimler, kalan hücrelerde ise miktarlar bulunur.

Varlık Küçük Büyük
Arc Tile 2 0
Dot Tile 1 0
Wave Tile 2 1

Bu örnekte her miktar sıfır veya daha büyük, tam sayı olmalıdır. Hiç öğe gerekmiyorsa açıkça sıfır girin. Devam etmeden önce boş hücreleri, metin biçimindeki miktarları, negatif değerleri, kesirleri ve hücre hatalarını düzeltin. Bu formüller geçerli sayılar varsayar; bunları doğrulamaz veya yuvarlamaz.

Beklenen çıktı altı öğe satırından oluşur: iki küçük Arc Tile, bir küçük Dot Tile, iki küçük Wave Tile ve bir büyük Wave Tile. Yinelenen etiketler kasıtlıdır; çünkü her satır bir öğeyi temsil eder. Formüller çalışma sayfası kayıtları oluşturur; sanat eseri dosyalarını oluşturmaz veya çoğaltmaz.

Matrisi denetlenebilir bir yardımcı listeye düzleştirme

D sütununu boş bırakın. E1:I1 hücrelerine Position, Asset, Format, Quantity ve End item başlıklarını girin. E2:E7 aralığına birden altıya kadar sayıları sırayla girin. Üç varlık ile iki biçimin çarpımı altı birleşim verdiği için yardımcı listede altı satır bulunur.

Yardımcı liste, aşağı geçmeden önce her kaynak satırını soldan sağa okur: Arc Tile Small, Arc Tile Large, ardından iki Dot Tile birleşimi ve son olarak iki Wave Tile birleşimi. Bu sırayı tutarlı biçimde korumak çok önemlidir.

Her birleşimin etiketlerini ve miktarını alma

Bu formülü F2 hücresine girin:

=INDEX($A$2:$A$4,INT((E2-1)/2)+1)

Bu formülü G2 hücresine girin:

=INDEX($B$1:$C$1,1,MOD(E2-1,2)+1)

Bu formülü H2 hücresine girin:

=INDEX($B$2:$C$4,INT((E2-1)/2)+1,MOD(E2-1,2)+1)

F2:H2 aralığını yedinci satıra kadar aşağı kopyalayın. Bunlar, her hücrede tek bir sonuç veren normal hücre formülleridir. INDEX, belirtilen satır ve sütundaki bir değeri alır. Burada INT, kaynak satırını her iki konumda bir ilerletirken MOD, kaynak sütununun iki biçim arasında dönüşümlü olmasını sağlar. INDEX işlevi, INT işlevi, MOD işlevi.

Her birleşimin son öğe numarasını hesaplama

I2 hücresine aşağıdaki formülü girin ve I7 satırına kadar aşağı kopyalayın:

=SUM($H$2:H2)

Sabit başlangıç başvurusu ve değişen bitiş başvurusu, birikimli toplam oluşturur. SUM, ilk yardımcı satırdan geçerli satıra kadar olan miktarları toplar. SUM işlevi.

Tamamlanmış yardımcı listeniz şöyle görünmelidir:

Position Asset Format Quantity End item
1 Arc Tile Small 2 2
2 Arc Tile Large 0 2
3 Dot Tile Small 1 3
4 Dot Tile Large 0 3
5 Wave Tile Small 2 5
6 Wave Tile Large 1 6

End item değerini bir sınır olarak yorumlayın. İlk birleşim bir ve iki numaralı öğeleri kapsar. Üçüncü birleşim üçüncü öğeyi kapsar. Beşinci birleşim dört ve beşinci öğeleri, altıncı birleşim ise altıncı öğeyi kapsar. Sıfır miktar sınırı ilerletmez; dolayısıyla bu birleşime öğe numarası atanmaz.

Her öğe için bir çıktı satırı oluşturma

K1 hücresine Asset, L1 hücresine Format girin. Her iki başlığın altındaki hücreleri boş bırakın. Çıktıyı Excel tablosunun dışındaki normal çalışma sayfası hücrelerine yerleştirin: Microsoft, taşan dizi formüllerinin tablolar içinde desteklenmediğini belirtir. Dinamik dizi formülleri ve taşan dizi davranışı.

Bu formülün tamamını K2 hücresine girin:

=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$F$2:$F$7,,1,1))

Bu formülün tamamını L2 hücresine girin:

=IF(SUM($H$2:$H$7)=0,"",XLOOKUP(SEQUENCE(SUM($H$2:$H$7)),$I$2:$I$7,$G$2:$G$7,,1,1))

Her formülü Return tuşuyla onaylayın. Bu iki formülü aşağı kopyalamayın: dizi sonuçları komşu satırlara otomatik olarak yayılır. Microsoft bu otomatik genişlemeyi taşma olarak tanımlar. Dinamik dizi formülleri ve taşan dizi davranışı.

SEQUENCE, toplam miktara kadar öğe numaralarını birden başlayarak oluşturur. Her numara için XLOOKUP, kümülatif sınırları arar. Son iki bağımsız değişkeni, ilk değerden başlayarak tam eşleşme veya sonraki büyük değeri aramayı belirtir. Bu arama yönü, sıfırlar yinelenen toplamlar oluşturduğunda ilk eşleşen sınırın korunmasını sağlar. SEQUENCE işlevi, XLOOKUP işlevi.

En dıştaki IF, toplam sıfır olduğunda boş metin döndürür. Bu durumda iki başlangıç hücresi boş görünür ve kullanılabilecek öğe kaydı bulunmaz. Bu, iki sonuç arasında seçim yapmak için IF kullanan, sıfır toplam için kasıtlı bir koşuldur. IF işlevi.

Verilen matris için K1:L7 aralığı şöyle görünmelidir:

Asset Format
Arc Tile Small
Arc Tile Small
Dot Tile Small
Wave Tile Small
Wave Tile Small
Wave Tile Large

Her iki çıktı sütunu da aynı oluşturulmuş öğe numaralarını ve sınır aralığını kullanır. Yalnızca döndürülen aralık değişir: F sütunu varlık etiketlerini, G sütunu ise biçimleri sağlar. Formülleri düzenlerken bu arama girdilerini eşzamanlı tutun.

Matrisi genişletmeden önce sonucu denetleme

Örnek verilerle üç denetim yapın:

  1. Toplamları karşılaştırın: =SUM(B2:C4) ve =SUM(H2:H7) ikisi de altı döndürmelidir.
  2. Birleşimleri karşılaştırın: çıktı, sıfırdan büyük dört birleşimi belirtilen tekrar sayılarıyla içermelidir.
  3. B2 değerini ikiden üçe değiştirin: beklenen çıktı, üç Arc Tile Small girdisiyle yedi satır olur. Ardından değeri tekrar ikiye döndürün.

İlk denetim, düzleştirme sırasında miktarların atlanıp yinelenmediğini saptar. İkincisi, yalnızca toplamın ortaya çıkaramayacağı etiket hizalama hatalarını saptar. Üçüncüsü, değişikliklerin yardımcı listeden çıktıya aktarılıp aktarılmadığını kontrol eder.

Daha fazla varlık veya biçimi dikkatlice ekleme

B2:C4 aralığındaki miktarları değiştirmek mevcut kurulumu kullanır. Matrisin boyutlarını artırmak, başvuruları ve yardımcı liste uzunluğunu değiştirmenizi gerektirir.

Eksiksiz bir örnek için kaynağı A1:D5 aralığında dört varlık ve üç biçim olacak şekilde genişletin. Yardımcı alanı E:I konumunda tutun veya taşıyın, E2:E13 aralığına birden on ikiye kadar konumları girin ve F2:H2 hücrelerindeki formülleri şunlarla değiştirin:

=INDEX($A$2:$A$5,INT((E2-1)/3)+1)

=INDEX($B$1:$D$1,1,MOD(E2-1,3)+1)

=INDEX($B$2:$D$5,INT((E2-1)/3)+1,MOD(E2-1,3)+1)

Bu formülleri on üçüncü satıra kadar aşağı kopyalayın ve I sütunundaki birikimli toplam formülünü de aynı satıra kadar uzatın. Her iki çıktı formülünde de yedinci satırda biten tüm yardımcı aralıkları on üçüncü satıra kadar uzatın. Her varlığın üç biçimi olduğundan bölen ve kalan tabanı artık üçtür. Bu değişiklikleri yaptıktan sonra kaynak toplamını ve her bir birleşimi yeniden denetleyin.

Eksik veya engellenmiş çıktıyı giderme

Excel #SPILL! gösterirse hedeflenen çıktı alanında mevcut girdiler veya birleştirilmiş hücreler olup olmadığını ve formülün Excel tablosunun dışında bulunduğunu kontrol edin. Çalışma sayfasının kenarını aşan bir sonuç da taşamaz. Microsoft bu koşulların her birini belgeler. #SPILL! hatasını düzeltme.

Çıktıda çok az satır varsa arama mantığını değiştirmeden önce miktarları inceleyin. Microsoft, SUM işlevinin başvurulan aralıklardaki metinleri yok saydığını belirtir. Bu nedenle metin olarak saklanan bir miktar toplama dahil edilmeyebilir. Kaynak değerini düzeltin ve toplam denetimini tekrarlayın. SUM işlevi.

Toplam doğru olduğu hâlde etiketler yanlışsa yardımcı listeyi matrisle hücre hücre karşılaştırın. Her yardımcı satırda varlığı, biçimi, miktarı ve kümülatif sınırı bir arada tutun. Yardımcı sütunları birbirinden bağımsız olarak yeniden düzenlemeyin.

Toplamlar ve birleşimler eşleştiğinde iki çıktı sütununu öğe listeniz olarak kullanın. Daha sonra miktarlarda yapılacak değişikliklerin yinelenen satırlarla ilişkisi izlenebilir kalsın diye matris ve yardımcı hesaplamaları elinizde bulundurun.

Kaynaklar