İçerik
Uygulamada, çoğu zaman, belirli bir sayıya göre bir kümede (tabloda) en yakın değeri bulmamız gereken durumlar vardır. Örneğin şunlar olabilir:
- Hacme bağlı olarak indirim hesaplaması.
- Planın uygulanmasına bağlı olarak ikramiye miktarının hesaplanması.
- Mesafeye bağlı olarak nakliye ücretlerinin hesaplanması.
- Mallar için uygun kapların seçimi vb.
Ayrıca duruma göre hem yukarı hem aşağı yuvarlama yapmak gerekebilir.
Böyle bir sorunu çözmenin - bariz ve çok açık olmayan - birkaç yolu vardır. Onlara sırayla bakalım.
Başlangıç olarak, toptan satışta indirim yapan bir tedarikçi düşünelim ve indirim yüzdesi satın alınan mal miktarına bağlıdır. Örneğin, 5 parçadan fazla satın alırken %2, 20 parçadan satın alırken - zaten %6 vb.
Satın alınan malların miktarını girerken indirim yüzdesini hızlı ve güzel bir şekilde nasıl hesaplayabilirim?
Yöntem 1: İç İçe IF'ler
“Düşünecek ne var - zıplaman gerek!” serisinden bir yöntem. Yuvalanmış işlevleri kullanma IF (EĞER) hücre değerinin aralıkların her birine düşüp düşmediğini sırayla kontrol etmek ve karşılık gelen aralık için bir indirim görüntülemek için. Ancak bu durumda formül çok hantal olabilir:
Böyle bir “canavar bebek”te hata ayıklamanın veya bir süre sonra ona birkaç yeni koşul eklemeye çalışmanın eğlenceli olduğunu düşünüyorum.
Ek olarak, Microsoft Excel'in EĞER işlevi için bir iç içe yerleştirme sınırı vardır - eski sürümlerde 7 kez ve daha yeni sürümlerde 64 kez. Ya daha fazlasına ihtiyacınız olursa?
Yöntem 2. Aralık görünümü ile DÜŞEYARA
Bu yöntem çok daha kompakttır. İndirim yüzdesini hesaplamak için efsanevi işlevi kullanın VPR (DÜŞEYARA) yaklaşık arama modunda:
nerede
- B4 – ilk işlemdeki indirim aradığımız mal miktarının değeri
- $G$4:$S$8 – indirim tablosuna bir bağlantı – “başlık” olmadan ve adresleri $ işaretiyle sabitlenmiş olarak.
- 2 — iskonto tablosundaki iskonto değerini almak istediğimiz sütunun sıra numarası
- DOĞRU – burası “köpek”in gömülü olduğu yer. Son işlev argümanı olarak ise VPR belirtmek UZANMAK (YANLIŞ) veya 0, sonra işlev arayacaktır sıkı maç miktar sütununda (ve bizim durumumuzda indirim tablosunda 49 değeri olmadığından #N/A hatası verecektir). Ama bunun yerine UZANMAK yazmak DOĞRU (DOĞRU) veya 1, o zaman işlev tamı aramayacak, ancak en yakın en küçük değer ve bize ihtiyacımız olan indirim yüzdesini verecektir.
Bu yöntemin dezavantajı, indirim tablosunu ilk sütuna göre artan düzende sıralama ihtiyacıdır. Böyle bir sıralama yoksa (veya ters sırada yapılırsa), formülümüz çalışmaz:
Buna göre, bu yaklaşım sadece en yakın en küçük değeri bulmak için kullanılabilir. En yakındaki en büyük olanı bulmanız gerekiyorsa, farklı bir yaklaşım kullanmanız gerekir.
Yöntem 3. İNDEKS ve KAÇINCI işlevlerini kullanarak en yakın en büyüğü bulma
Şimdi sorunumuza diğer taraftan bakalım. Çeşitli kapasitelerde birkaç endüstriyel pompa modeli sattığımızı varsayalım. Soldaki satış tablosu müşterinin ihtiyaç duyduğu gücü gösterir. En yakın maksimum veya eşit güce sahip, ancak projenin gerektirdiğinden daha az olmayan bir pompa seçmemiz gerekiyor.
DÜŞEYARA işlevi burada yardımcı olmayacak, bu nedenle analogunu kullanmanız gerekecek - bir dizi INDEX işlevi (İNDEKS) ve DAHA FAZLASI (KİBRİT):
Burada, son argümanı -1 olan KAÇINCI işlevi, en yakın en büyük değeri bulma modunda çalışır ve ardından INDEX işlevi, ihtiyacımız olan model adını bitişik sütundan çıkarır.
Yöntem 4. Yeni işlev GÖRÜNÜM (XLOOKUP)
Tüm güncellemelerin yüklü olduğu bir Office 365 sürümünüz varsa, DÜŞEYARA yerine (DÜŞEYARA) analogunu kullanabilirsiniz – GÖRÜNÜM işlevi (DÜŞEYARA), daha önce ayrıntılı olarak analiz ettiğim:
İşte:
- B4 – indirim aradığımız ürün miktarının başlangıç değeri
- $G$4:$G$8 – eşleşme aradığımız aralık
- $S$4:$S$8 – indirimi iade etmek istediğiniz sonuç aralığı
- dördüncü argüman (-1) tam eşleşme yerine istediğimiz en yakın en küçük sayının aranmasını içerir.
Bu yöntemin avantajları, indirim tablosunu sıralamaya gerek olmaması ve gerekirse sadece en yakın en küçük değil, aynı zamanda en yakın en büyük değeri arama yeteneğidir. Bu durumda son argüman 1 olacaktır.
Ancak ne yazık ki, henüz herkes bu özelliğe sahip değil - yalnızca Office 365'in mutlu sahipleri.
Yöntem 5. Güç Sorgusu
Excel için güçlü ve tamamen ücretsiz Power Query eklentisine henüz aşina değilseniz, buradasınız. Zaten aşina iseniz, sorunumuzu çözmek için kullanmaya çalışalım.
Önce bir hazırlık çalışması yapalım:
- Bir klavye kısayolu kullanarak kaynak tablolarımızı dinamik (akıllı) hale getirelim Ctrl+T veya takım Ana Sayfa – Tablo olarak biçimlendir (Ana Sayfa — Tablo Olarak Biçimlendir).
- Açıklık için onlara isim verelim. Satış и indirimler çıkıntı Inşaatçı (Tasarım).
- Düğmeyi kullanarak tabloların her birini sırayla Power Query'ye yükleyin Tablodan/Aralıktan çıkıntı Veri (Veri — Tablodan/aralıktan). Excel'in son sürümlerinde bu düğme şu şekilde yeniden adlandırılmıştır: yaprakları ile (sayfadan).
- Tablolar, örneğimizde olduğu gibi (“Malların miktarı” ve “Miktardan gelen…”) miktarlarla birlikte farklı sütun adlarına sahipse, Power Query'de yeniden adlandırılmalı ve aynı şekilde adlandırılmalıdır.
- Bundan sonra, Power Query düzenleyici penceresindeki komutu seçerek Excel'e geri dönebilirsiniz. Ana Sayfa — Kapat ve Yükle — Kapat ve Yükle… (Ana Sayfa — Kapat&Yükle — Kapat&Yükle…) ve sonra seçenek Sadece bir bağlantı oluşturun (Yalnızca bağlantı oluşturun).
- Sonra en ilginç olanı başlar. Power Query'de deneyiminiz varsa, bundan sonraki düşüncenin, önceki yöntemde olduğu gibi bu iki tabloyu bir birleştirme sorgusu (birleştirme) ve DÜŞEYARA ile birleştirme yönünde olması gerektiğini varsayıyorum. Aslında, ilk bakışta pek açık olmayan ekleme modunda birleştirmemiz gerekecek. Excel sekmesinde seçin Veri – Veri Al – İstekleri Birleştir – Ekle (Veri — Veri Al — Sorguları birleştir — Ekle) ve sonra masalarımız Satış и indirimler görünen pencerede:
- Tıkladıktan sonra OK masalarımız tek bir bütün halinde - alt alta yapıştırılacaktır. Lütfen bu tablolardaki mal miktarlarını içeren sütunların birbirinin altına düştüğünü unutmayın, çünkü. aynı ada sahipler:
- Satış tablosundaki orijinal satır dizisi sizin için önemliyse, sonraki tüm dönüşümlerden sonra geri yükleyebilmeniz için, komutu kullanarak tablomuza numaralı bir sütun ekleyin. Sütun Ekleme – Dizin Sütunu (Sütun ekle — Dizin sütunu). Satır sırası sizin için önemli değilse, bu adımı atlayabilirsiniz.
- Şimdi, tablonun başlığındaki açılır listeyi kullanarak sütuna göre sıralayın Adet Artan:
- Ve asıl püf noktası: sütun başlığına sağ tıklayın İndirim bir takım seç Doldur – Aşağı (Doldur - Aşağı). ile boş hücreler boş önceki indirim değerleriyle otomatik olarak doldurulur:
- Sütuna göre sıralayarak orijinal satır sırasını geri yüklemek için kalır indeks (daha sonra güvenle silebilirsiniz) ve bir filtre ile gereksiz satırlardan kurtulun boş sütuna göre İşlem kodu:
- Veri aramak ve aramak için DÜŞEYARA işlevini kullanma
- DÜŞEYARA (DÜŞEYARA) kullanımı büyük/küçük harfe duyarlıdır
- XNUMXD DÜŞEYARA (DÜŞEYARA)