Tekrarlanan varlık kimlikleri de dahil olmak üzere her envanter satırına varlık meta verileri eklemek için tam eşleşmeli arama kullanın. Mac için Excel’de HSTACK ve XLOOKUP ile tek bir taşan sonuç oluşturabilir veya Excel 2021’de ayrı arama sütunları kullanabilirsiniz.

Bu adım adım kılavuz, çok sayıda sanat varlığı envanter kaydı ve her varlık için bir meta veri satırı bulunan bir Mac masaüstü kullanıcısına yöneliktir. Amaç, envanterin özgün sırasını, miktarlarını ve tekrarlanan kayıtlarını korurken her varlığın başlığını, biçimini ve paletini eklemektir. Aşağıdaki örnek verilerin tümü sentetiktir.

Mac için Excel’in hangi sürümü bu yöntemi destekliyor?

Excel → About Microsoft Excel yolunu izleyerek yüklemenizi kontrol edin. Microsoft’un Mac yönergeleri, uygulamanın Hakkında iletişim kutusunda sürüm numarası ve lisans türünün gösterildiğini açıklar. Formül seçerken ikisini de kontrol edin. Office Hakkında: Hangi Office sürümünü kullanıyorum?

Mac sürümü Bu makaledeki yaklaşım
Mac için Microsoft 365 Excel HSTACK ve XLOOKUP kullanarak tek bir birleşik sonuç
Mac için Excel 2024 HSTACK ve XLOOKUP kullanarak tek bir birleşik sonuç
Mac için Excel 2021 Üç ayrı XLOOKUP taşan sütunu
Excel 2019 veya 2016 Bu formüller daha yeni bir sürüm gerektirir

Microsoft, Mac için Microsoft 365 Excel’i ve Mac için Excel 2024’ü HSTACK belgelerinde listeler. Mac için Excel 2021 özellik belgeleri XLOOKUP ve dinamik dizileri desteklediğini doğrular. XLOOKUP belgeleri, Excel 2016 ve 2019’u açıkça kapsam dışında bırakır.

Excel 2021 kullanıyorsanız aşağıdaki ayrı sütun yöntemini uygulayın. Bu yöntem, HSTACK gerektirmeden aynı zenginleştirme sonucuna ulaşır.

Envanter ve meta verileri ortak bir varlık kimliğiyle ayarlama

Aynı çalışma kitabında Inventory ve Metadata adlarında iki çalışma sayfası oluşturun. Aşağıdaki envanter verilerini Inventory!A1:B6 aralığına girin. Bu örnekte bu hücreleri normal bir çalışma sayfası aralığı olarak bırakın.

AssetID Quantity
ARC-01 3
TILE-02 5
ARC-01 2
GRID-03 4
TILE-02 1

Meta verileri Metadata!A1:D4 aralığına girin:

AssetID Title Format Palette
ARC-01 Arc Study Vector Teal
TILE-02 Tile Study Raster Amber
GRID-03 Grid Study Vector Slate

Envanterdeki tekrarlanan kimlikler kasıtlıdır: iki kayıt, aynı varlığın ayrı miktarlarını açıklayabilir. Meta veri sayfasında ortak açıklama bir kez bulunur. Bu nedenle çıktı, meta veriler yalnızca üç varlık içerse de beş veri satırı içermelidir.

Her iki sayfada da aynı varlığı tutarlı biçimde tanımlayan bir varlık kimliği kullanın. Bu örnekte her kimlik ve meta veri alanı doldurulmuştur. Kendi çalışma kitabınızda bilinmeyen bir alanı nasıl açıkça göstereceğinize karar verin; örneğin, eksik varlık eşleşmesinden ayırt edilebilmesi için Unknown metnini kullanabilirsiniz.

Her envanter kimliğinin tam olarak bir meta veri eşleşmesi olduğunu kontrol etme

Inventory sayfasında C1 hücresine MetadataMatches yazın. Bu formülü C2 hücresine girin, ardından C6 hücresine kadar kopyalayın:

=COUNTIF(Metadata!$A$2:$A$4,A2)

Çıktıyı oluşturmadan önce sonucu yorumlayın:

  • 0: varlık kimliği meta veri sayfasında eksik.
  • 1: varlık için bu iş akışının beklediği tek meta veri satırı mevcut.
  • 1 değerinden büyük herhangi bir sayı: meta verilerde çözümlenmesi gereken yinelenen kimlikler var.

COUNTIF, bir ölçütü karşılayan hücreleri sayar. Verilen verilerde her kontrol 1 sonucunu verir. Burada gösterilenler gibi basit kimlikler kullanın: COUNTIF, yıldız ve soru işaretlerini joker karakter olarak değerlendirir; bu nedenle bu karakterleri içeren kimlikler ek işlem gerektirir. Microsoft Excel’de COUNTIF işlevini kullanma

Devam etmeden önce yinelenen meta veri kimliklerini çözün. XLOOKUP varsayılan olarak ilk eşleşmeyi döndürür; bu nedenle sonucun dolu olması tek başına meta veri anahtarının benzersiz olduğunu göstermez. XLOOKUP işlevi

Microsoft 365 veya Excel 2024’te tek bir zenginleştirilmiş dizi oluşturma

Enriched adlı bir çalışma sayfası oluşturun. A1:E1 hücrelerine şu beş başlığı girin: AssetID, Quantity, Title, Format, Palette. A2:E6 aralığını boş bırakın.

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

=HSTACK(
  Inventory!A2:B6,
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0),
  XLOOKUP(Inventory!A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)
)

Bu formül, özgün iki envanter sütununu üç arama sonucu sütunuyla birleştirir. Eklenen alanların özgün kayıtlarla hizalı kalması için her aramada aynı beş envanter kimliği kullanılır. HSTACK, dizileri belirtilen sırayla yatay olarak birleştirir. HSTACK işlevi

Her aramanın sonundaki 0 tam eşleşme ister. Eşleşmeyen bir kimlik için MISSING METADATA metni sonuç olarak gösterilir. Bu metni görünür tutmak, açıklanamayan boş hücreler bırakmak yerine eksik eşleşmeleri bulmanızı sağlar. XLOOKUP işlevi

Beklenen çıktı Enriched!A2:E6 aralığını kaplar:

AssetID Quantity Title Format Palette
ARC-01 3 Arc Study Vector Teal
TILE-02 5 Tile Study Raster Amber
ARC-01 2 Arc Study Vector Teal
GRID-03 4 Grid Study Vector Slate
TILE-02 1 Tile Study Raster Amber

Her iki ARC-01 kaydının da 3 ve 2 miktarlarıyla kaldığına dikkat edin. İkisi de Arc Study, Vector ve Teal bilgilerini alır. Meta veriler bu kayıtları birleştirmeden zenginleştirmiştir.

Formül, sonuçları birleştirmeden önce her seferinde tek bir meta veri alanını arar. Böylece çıktı şekli açıkça belirlenir: beş envanter satırı, iki özgün sütun ve eklenen üç sütun.

Mac için Excel 2021’de üç arama sütunu kullanma

Excel 2021’de doğrudan özgün envanterin yanına çalışın. Eşleşme sayısı kontrolünü C sütununda tutun, ardından D1:F1 hücrelerine Title, Format ve Palette yazın. D2:F6 aralığını boş bırakın.

Bu formülü Inventory!D2 hücresine girin:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!B2:B4,"MISSING METADATA",0)

Bu formülü Inventory!E2 hücresine girin:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!C2:C4,"MISSING METADATA",0)

Bu formülü Inventory!F2 hücresine girin:

=XLOOKUP(A2:A6,Metadata!A2:A4,Metadata!D2:D4,"MISSING METADATA",0)

Her formülden sonra Return tuşuna basın. Her sonuç, başlangıç hücresinin altındaki beş satırı doldurur. Varlık kimlikleri ve miktarları A:B sütunlarında, tanılama sayısı C sütununda kalır; eklenen meta veriler ise D:F sütunlarında görünür. Mac için Excel 2021, bu yaklaşım için gereken dinamik dizi davranışını içerir. Mac için Excel 2021’deki yenilikler

Envanter satırları arttıkça çalışma kitabını uyarlama

Örneklerde her başvurunun açıkça görülebilmesi için sabit aralıklar kullanılır. Envanteriniz büyüdüğünde tüm envanter başvurularını aynı son satıra kadar genişletin. Örneğin, envanter 501 satırında bitiyorsa birleşik formülde Inventory!A2:B501 ve Inventory!A2:A501 aralığına üç başvuru gerekir. Eşleşme sayısı kontrolünü de aynı satıra kadar genişletin.

Meta veriler büyüdüğünde tüm meta veri başvurularını birlikte genişletin. Son satır 101 olursa anahtarlar için Metadata!A2:A101, buna karşılık gelen sonuç aralıkları için B2:B101, C2:C101 ve D2:D101 kullanın. Kullanılmayan çok sayıda satır eklemek yerine gerçek kayıtları dahil edin.

Düzenli olarak büyüyen bir çalışma kitabı için kaynak aralıklarını Excel tablolarına dönüştürüp yapılandırılmış başvurular kullanmayı değerlendirin. Microsoft, kaynak satırlar eklendikçe veya kaldırıldıkça bu başvuruların güncellendiğini belirtir. Taşan çıktıyı tablonun dışındaki çalışma sayfası ızgarasında tutun. Dinamik dizi formülleri ve taşan dizi davranışı

Bir Excel tablosunun içinde zenginleştirme yapmanız gerekiyorsa her meta veri sütunu ve envanter satırı için ayrı, tek sonuçlu bir formül kullanın. Tablolar, sütunun tamamına formül uygulayan hesaplanan sütunları destekler; birden çok hücreye taşan sonuç tablonun dışında yer almalıdır. Excel tablolarına genel bakış

Sorunları giderme ve tamamlanmış sonucu doğrulama

Excel #SPILL! görüntülüyorsa hedeflenen hücreleri inceleyin. Saklamak istediğiniz mevcut içeriği taşıyın ve formülün Excel tablosunun dışında olduğundan emin olun. Taşan bir sonucun yalnızca sol üst hücresi düzenlenebilir; formülü o hücrede değiştirin. Microsoft bu taşma sınırlarını ve engel hatalarını açıklar. Dinamik dizi formülleri ve taşan dizi davranışı

Bir satırda MISSING METADATA görünüyorsa ilgili eşleşme sayısı kontrolüne dönün. Sıfır sonucu, eksik meta veri kaydını eklemeniz veya varlık kimliğini düzeltmeniz gerektiğini gösterir. Görsel olarak aynı etiketler beklendiği gibi çalışmadığında başta veya sonda yanlışlıkla boşluk bırakılıp bırakılmadığını kontrol edin; Microsoft, beklenmedik COUNTIF sonuçlarının nedenlerinden biri olarak bunları belirtir. Microsoft Excel’de COUNTIF işlevini kullanma

Bu kurulumda iki kaynak çalışma sayfasını aynı çalışma kitabında tutun. Kaynağı daha sonra başka bir çalışma kitabına taşırsanız Microsoft’un belgelediği sınırlamayı göz önünde bulundurun: bağlantılı dinamik diziler her iki çalışma kitabının da açık olmasını gerektirir; kaynak kapalıyken yenileme #REF! hatasına yol açabilir. Dinamik dizi formülleri ve taşan dizi davranışı

Sentetik örneği şu üç somut kontrolle doğrulayın:

  1. Özgün envanter sırasına göre beş çıktı veri satırı bulunur.
  2. Miktarlar 3, 5, 2, 4, 1 olarak kalır ve toplamları 15 olur.
  3. Tekrarlanan kimlikler aynı meta verileri alır ve her eşleşme sayısı kontrolü 1 sonucunu verir.

Bu kontroller hedeflenen sonucu doğrular: her envanter kaydı yerini ve miktarını korurken varlık kimliğine ait açıklamayı alır. Daha büyük bir çalışma kitabında, aralıkları genişlettikten veya meta veri kayıtları ekledikten sonra aynı kontrolleri tekrarlayın.

Kaynaklar