Taşan bir aramanın çevresinde IFNA kullanarak #N/A sonuçlarını ikinci bir başvuru tablosundan tamamlayın. Bu Excel for Mac örneği çizim varlığı etiketlerini eşleştirir, birincil tabloda bulunan miktarları korur ve her iki tabloda da bulunmayan etiketleri açıkça işaretler.

Excel for Mac'in hangi sürümleri bu yaklaşımı destekler?

Mac için Microsoft 365 Excel, Mac için Excel 2024 veya Mac için Excel 2021 kullanın. Microsoft, bu sürümleri hem XLOOKUP hem de IFNA için listeler. XLOOKUP belgelerinde, söz konusu sürümler sayfanın daha geniş uygulanabilirlik listesinde yer almasına rağmen Excel 2016 ve Excel 2019'un açıkça desteklenmediği belirtilir. Aşağıdaki formüller desteklenen Mac sürümlerini hedefler. XLOOKUP işlevi, IFNA işlevi

Görev belirli: istenen varlık etiketlerinden oluşan bir sütununuz var ve her etiket için bir miktar istiyorsunuz. Birincil başvuru önceliklidir; ikinci başvuru yalnızca birincil arama #N/A sonucunu verdiğinde miktar sağlar. Aşağıdaki tüm etiketler ve miktarlar örnek amaçlıdır.

İki başvuru tablosu ve istenen varlık listesini oluşturun

Tek bir çalışma kitabında boş bir çalışma sayfasıyla başlayın. Formüllerin başvuruları değiştirmeden çalışması için aşağıdaki hücre konumlarını kullanın. Bu küçük başvuru tabloları sıradan çalışma sayfası aralıklarıdır; bunları Excel Tablosu nesnelerine dönüştürmeniz gerekmez.

Birincil başvuruyu D1:E4 aralığına girin:

D sütunundaki hücre Varlık etiketi E sütunundaki hücre Miktar
D1 Varlık E1 Miktar
D2 Yaprak taslağı E2 8
D3 Nokta dokusu E3 0
D4 Döşeme deseni E4 12

İkinci başvuruyu G1:H5 aralığına girin:

G sütunundaki hücre Varlık etiketi H sütunundaki hücre Miktar
G1 Varlık H1 Miktar
G2 Nokta dokusu H2 9
G3 Eğri eskizi H3 5
G4 Gren dokusu H4 3
G5 Yaprak taslağı H5 10

J1 hücresine İstenen varlık yazın, ardından bu beş etiketi J2:J6 aralığına her hücreye bir tane gelecek şekilde ve aşağıdaki sırayla girin:

  1. Yaprak taslağı
  2. Nokta dokusu
  3. Eğri eskizi
  4. Gren dokusu
  5. Yay motifi

Miktarları sayısal tutun ve iki başvurudaki tüm miktar hücrelerini doldurun. Bu örnekte sıfır, kayıtlı miktarın sıfır olduğu anlamına gelir. Varlığın eksik olduğu anlamına gelmez.

Etiketlerin taşan listesini oluşturun

A1, B1 ve C1 hücrelerine sırasıyla Varlık, Birincil miktar ve Tamamlanmış miktar başlıklarını girin. A2:C20 aralığını boş bırakın, ardından bu formülü A2 hücresine girin:

=FILTER(J2:J20,J2:J20<>"","")

Bu, istenen listedeki boş hücreleri filtreler. Verilen verilerle beş varlık etiketini A2:A6 aralığına taşır. Microsoft, yukarıda listelenen üç Mac sürümü için FILTER işlevini belgeler ve son bağımsız değişkeninin koşulu karşılayan satır olmadığında bir sonuç sağladığını açıklar. Buradaki sonuç boş bir dizedir. FILTER işlevi

Sonraki formüller bu taşmanın tamamına başvurmak için A2# kullanır. # işleci taşma boyutunu izler; böylece geçerli son hücreyi A6 olarak sabit kodlamanız gerekmez. Microsoft bu davranışı taşan aralık işleci belgelerinde açıklar.

Eksik miktarları ikinci başvurudan tamamlayın

Önce birincil aramayı B2 hücresinde oluşturun:

=XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4)

Bu, birincil varlık sütununda arama yapar ve karşılık gelen miktarları döndürür. XLOOKUP varsayılan olarak tam eşleşme kullanır ve eşleşme bulunmadığında, alternatif bir sonuç sağlanmamışsa #N/A döndürür. XLOOKUP işlevi

Örnek için B2:B6 aralığında 8, 0, #N/A, #N/A ve #N/A bulunmalıdır. Sonraki adım için bu eşleşme bulunamadı hatalarını koruyun.

Şimdi tamamlama formülünü C2 hücresine girin:

=IFNA(B2#,XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed"))

IFNA birincil taşmayı inceler. #N/A bulunan konumlarda formül, ikinci aramanın karşılık gelen sonucunu sağlar. Diğer birincil sonuçlar değiştirilmeden aktarılır. Microsoft, ilk bağımsız değişkeni bir dizi formülü olduğunda IFNA işlevinin bir sonuç dizisi döndürdüğünü özellikle belgeler. IFNA işlevi

Beklenen çalışma sayfası sonucu şöyledir:

A sütunundaki varlık B sütunundaki birincil miktar C sütunundaki tamamlanmış miktar
Yaprak taslağı 8 8
Nokta dokusu 0 0
Eğri eskizi #N/A 5
Gren dokusu #N/A 3
Yay motifi #N/A Listede yok

Bu satırları bir öncelik testi olarak değerlendirin. İkinci başvuruda 10 bulunmasına rağmen Yaprak taslağı 8 olarak kalır. İkinci başvuruda 9 bulunmasına rağmen Nokta dokusu 0 olarak kalır. Eğri eskizi ve Gren dokusu ikincil miktarlarını alır. Yay motifi, iki başvuruda da bulunmadığından çözümlenmemiş olarak görünür.

İki dizi, aynı istenen varlıkları aynı sırada tanımlamalıdır. Bu kurulumda ikisi de A2# aralığından kaynaklanır. Mevcut bir çalışma kitabına uyarlama yapıyorsanız, birincil sonucu bir sıralama düzenine göre sıralanmışken yedek aramayı farklı sıralanmış bir etiket listesine dayandırmayın.

Ara sütuna ihtiyaç duymadığınızda tek formül kullanın

Birincil arama ve tamamlamayı birlikte tutmak için C2 hücresindeki formülü bu tam alternatifle değiştirin:

=IFNA(
    XLOOKUP(A2#,$D$2:$D$4,$E$2:$E$4),
    XLOOKUP(A2#,$G$2:$G$5,$H$2:$H$5,"Not listed")
)

Bu formül aynı örnek verileri kullanır ve aynı beş tamamlanmış miktarı üretmelidir. Hesaplama için B sütunu artık gerekli değildir. Çalışma kitabını kurarken iki aşamalı sürümü korumak, her sonucun hangi başvurudan geldiğini görmeyi kolaylaştırır.

Bir sonucun gerçekten eksik olup olmadığına karar verin

Formülü uyarlamadan önce ilk aramanın ne döndürdüğünü inceleyin. Eksik bir etiket, boş görünen bir sonuç ve sıfır miktar farklı kararlar gerektirir.

Birincil sonuç Bu tamamlama formülünün yaptığı Kontrol edilmesi gereken
0 dahil sayısal miktar Bunu korur Bunun amaçlanan birincil miktar olduğunu doğrulayın.
#N/A İkincil aramayı kullanır Hatanın bulunmayan bir etiketi temsil ettiğini doğrulayın.
Boş dize, "" Bunu korur Birincil formülün eksik eşleşmeleri gizleyip gizlemediğini inceleyin.
Başka bir hata Görünür bırakır Temel formülü veya başvuruyu düzeltin.

Örneğin mevcut birincil aramanız "" değerini if_not_found bağımsız değişkeni olarak kullanıyorsa, IFNA bu boş dizeyi #N/A olarak tanıyamaz. B2 hücresinde gösterildiği gibi bu bağımsız değişkeni birincil aramadan kaldırın ya da yukarıdaki birleşik formülü kullanın. Tamamlama mantığı, tanınabilir bir eşleşme bulunamadı sonucunun korunmasına bağlıdır. XLOOKUP işlevi, IFNA işlevi

Başvurulardaki miktarları da inceleyin. IFNA bir hata değerine tepki verir; miktar hücresinin doldurulup doldurulmadığını sınamaz. Eşleşen birincil kayıtta tamamlanmamış bir miktar varsa bu iş akışını uygulamadan önce kaydı tamamlayın veya tamamlanmamış miktarlar için ayrı bir kural belirleyin.

Bir ayrım daha var: birincil miktar verilerindeki mevcut bir #N/A hatası da bu yedeği tetikler. Bu örnek için ön koşul, #N/A değerinin yalnızca başarısız bir aramadan kaynaklandığı temiz sayısal miktar sütunlarıdır. Her değiştirme işlemini eksik varlık eşleşmesi olarak değerlendirmeden önce bu koşulu kontrol edin.

Önceliği kontrol edin ve taşmanın çalışmasını sağlayın

Kendi varlık etiketlerinizi eklemeden önce küçük bir kabul kontrolü olarak örnekteki beş satırı kullanın. Birlikte bu satırlar birincil eşleşmeyi, geçerli bir sıfırı, iki ikincil eşleşmeyi ve iki başvuruda da bulunmayan bir etiketi sınar.

Ek kontrol için, Yaprak taslağının ikincil miktarını geçici olarak 10 değerinden 14 değerine değiştirin. Tamamlanmış miktar 8 olarak kalmalıdır. Ardından Eğri eskizinin ikincil miktarını 5 değerinden 6 değerine değiştirin; tamamlanmış miktarı 6 olmalıdır. Sonrasında özgün değerleri geri yükleyin. Bunlar çalışma kitabınız için örnek kontrollerdir; bildirilmiş Excel test sonuçları değildir.

Her başvuruda her varlık etiketi için amaçlanan tek bir kayıt tutun. XLOOKUP normalde ilk öğeden başlayarak arar ve ilk eşleşmeyi döndürür; bu nedenle yinelenen etiketlerin miktarları birleştirmesi beklenmemelidir. Bu arama tasarımına güvenmeden önce yinelenen etiketlerin ne anlama geldiğini belirleyin. XLOOKUP işlevi

Engellenmiş bir çıktı aralığını düzeltin

Her taşan formülü yalnızca başlangıç hücresine girin. Excel komşu hücreleri otomatik olarak doldurur ve taşmadaki yalnızca sol üst hücre düzenlenebilir. Formülleri Excel Tablosu nesnelerinin dışına yerleştirin; Microsoft tabloların kaynak veri olarak kullanılmasını destekler ancak içlerinde taşan formülleri desteklemez. Dinamik dizi formülleri ve taşan dizi davranışı

#SPILL! görürseniz amaçlanan çıktı sınırını görmek için formül hücresini seçin. Kullanılabiliyorsa hata uyarısındaki Select Obstructing Cells seçeneğini kullanın ve sonucu engelleyen mevcut girişleri taşıyın. Birleştirilmiş hücreler de taşmayı önleyebilir; çıktıyı taşıyın veya etkilenen hücrelerin birleştirmesini kaldırın. #SPILL! hatasını düzeltme

Girdileri bilinçli biçimde genişletin

İstenen liste formülü J2:J20 aralığını inceler. Bu aralığa eklenen etiketler otomatik olarak dahil edilir; daha alt satırlara girilen etiketler için formülün aralığını genişletmeniz gerekir. Benzer şekilde, başvuru aramaları 4 ve 5 satırlarında biten sabit aralıkları kullanır. Kayıt eklerken her varlık aralığını ve buna karşılık gelen miktar aralığını birlikte genişletin.

Daha büyük bir çalışma sayfasında, yapılandırılmış başvurular kullanan Excel Tabloları, satır eklendikçe veya kaldırıldıkça kaynak aralıkların ayarlanmasını sağlayabilir. Çıktı formüllerini bu tabloların dışındaki çalışma sayfası ızgarasında tutun. Dinamik dizi formülleri ve taşan dizi davranışı

Bu örneğin girdilerini aynı çalışma kitabında tutun. Microsoft, çalışma kitapları arasında dinamik diziler için sınırlı destek olduğunu belgeler: bağlantılı dinamik dizilerin çalışması için her iki çalışma kitabının da açık kalması gerekir ve kaynak kapatıldıktan sonra yenileme #REF! sonucuna yol açabilir. Dinamik dizi formülleri ve taşan dizi davranışı

Beklenen satırlar uyuştuğunda, tamamlanmış sütunu varlık miktarı sonucu olarak kullanın. Birincil önceliği koruyun, anlamlı sıfırları muhafaza edin ve başvuru verilerini bilinçli biçimde tamamlayabilmeniz için iki başvuruda da bulunmayan etiketleri Listede yok olarak bırakın.

Kaynaklar