Mesaj Panosundaki yaygın sorulardan biri, SumIf işlevinin iki farklı koşulla nasıl kullanılacağıdır. Maalesef cevap, SumIf'in iki farklı koşulu idare edemeyeceğidir.
İki koşulu gerçekleştirmek için, oldukça karmaşık bir dizi formülü kullanmanız gerekir. Koşullu toplam sihirbazı eklentisi, bu karmaşık formülleri kolaylıkla girmenize olanak tanır.
Burada ürün, satış temsilcisi ve satış için sütunlar içeren bir Excel çalışma sayfası. Veriler A2: C29 hücrelerindedir.
Satışları toplamak istiyorsanız, basit bir TOPLA () işlevi çalışacaktır. =SUM(C2:C29).
Birçok Excelci, SumIf işlevini keşfeder. Bu işlevi kullanarak, ABC ürününün toplam satışını hesaplamak oldukça kolaydır.=SUMIF(A2:A29,E2,C2:C29)
Satış temsilcisi Joe'nun yaptığı satışların toplamını hesaplamak da kolaydır =SUMIF(B2:B29,E2,C2:C29).
Daha sonra Joe tarafından yapılan ABC ürününün toplam satışını bulmanın mümkün olduğunu varsayarsınız. Bununla birlikte, bunu SumIf işleviyle yapmanın bir yolu yoktur. Oldukça karmaşık bir dizi veya CSE formülü kullanmanız gerektiği ortaya çıktı.
Kabul edelim - Sum formülü Excel 101'dir. SumIf formülü karmaşıklık açısından çok geride değildir. Ancak, Joe tarafından yapılan ABC satışlarının toplamını hesaplamak için CSE formülü kafamı bile döndürmek için yeterli.
İyi haber - Microsoft, bir aceminin bile 1, 2 veya daha fazla koşula dayalı karmaşık koşullu formüller girmesine izin veren Koşullu Toplam Sihirbazı'nı sunuyor. Koşullu Toplam Sihirbazı bir eklentidir. Bu işlevi Excel'e eklemek için, Araçlar menüsüne gidin ve Eklentiler'i seçin. Eklentiler iletişim kutusunda Koşullu Toplam Sihirbazı'nın yanındaki onay kutusunu seçin ve Tamam'ı seçin. Microsoft varsayılan yüklemeye sihirbazı dahil etmediğinden, bu noktada kurulum CD'nize ihtiyacınız olabilir.
Eklenti başarıyla açıldıktan sonra, Araçlar menüsünün alt kısmında bir Koşullu Toplam… seçeneği olacaktır.
Veri kümenizde tek bir hücre seçin ve Araçlar - Koşullu Toplam'ı seçin. Verilerinizin tek bir başlık satırıyla güzel bir şekilde biçimlendirildiğini varsayarsak, Excel verilerinizin aralığını doğru bir şekilde tahmin edecektir. İleri'yi seçin.
2. adımda, toplanacak sütunu seçin. Bu durumda, sihirbaz zaten ilk (ve yalnızca) sayısal sütun olan Satışları toplamak istediğinizi tahmin etti. İletişim kutusunun ortasında üç açılır kontrol vardır. Bunlar ilk koşul için doğrudur - Ürün ABC'ye eşittir, bu nedenle Koşul Ekle düğmesini seçin.
Ardından ikinci durumunuzu ekleyebilirsiniz. Bu durumda, Satış Temsilcisinin Joe olduğunu belirtmek istersiniz. İlk açılır menü için oku seçin. Excel, mevcut sütun adlarının alfabetik bir listesini sunar. Satış Temsilcisi'ni seçin.
Ortadaki açılır menü doğrudur, ancak burada tamlık için eşit, küçük, büyük, küçük veya eşit, büyük veya eşit veya eşit değil seçmiş olabileceğinizi görebilirsiniz.
Üçüncü açılır menüden Joe'yu seçin.
Koşul Ekle düğmesini seçin.
Artık 3. Adıma geçmeye hazırsınız. İleri düğmesine basın.
3. adımda iki seçeneğiniz var. İlk seçenekte, Sihirbaz, formüle "ABC" ve "Joe" kodlarının gömülü olduğu tek bir formül girecektir. Size cevabı verecek, ancak formülü kolayca değiştirme fırsatı olmayacak. İkinci seçenekle Excel, "ABC" değerine sahip yeni bir hücre ve "Joe" değerine sahip yeni bir hücre oluşturacaktır. Üçüncü bir hücre, bu iki değere dayalı olarak koşullu bir toplam yapan formülü içerecektir. Bu seçenekle, Adam tarafından satılan toplam XYZ'leri görmek için hücrelere yeni değerler yazabilirsiniz.
Sihirbaz daha sonra ABC değerini nerede istediğinizi soracaktır. Bir hücre seçin ve İleri'yi seçin. Sihirbaz sizden Joe için bir hücre ve formül seçmenizi istediği için tekrarlayın.
Son adımda Bitir'i seçtiğinizde, Excel, CSE formülünün biraz farklı (ancak geçerli) bir sürümünü oluşturur.
Bu formül, Joe'nun 33.338 $ 'lık ABC sattığını hesaplar.
Ürün giriş hücresini ABC'den DEF'ye değiştirirseniz formül, Joe'nun 24.478 ABD doları DEF sattığını göstermek için yeniden hesaplanır.
Koşullu Toplam Sihirbazı, karmaşık formülleri tüm Excel sahiplerinin erişebileceği bir yere koyar.
Ek bilgi: If you want to build a table that will show sales of each product by each sales rep, there is some special "care and feeding" that you will need to know about these formulas. Type each sales rep across the top of the range. Type each product down the left column of the range. Edit the formula provided by the wizard. In the image below, the formula is pointing a the product in cell E6. This reference really needs to be $E6. If you leave the reference as E6 and copy the formula to column G, the formula would look at F6 instead of E6 and this would be wrong. Adding a dollar sign before the E in E6 will make sure that the formula always looks at the product in column E. The formula is also pointing to a sales rep in cell F5. This reference really needs to be F$5. If you left the reference as F5 and copy down to row 7, the F5 reference will change to F6 and this is not right. Adding a dollar sign before the row number will lock the row number and the reference will always point to row 5.
Düzenleme modunda (hücreyi seçin ve düzenlemek için F2'ye basın), E'den önce bir $ yazın. F5'teki 5'ten önce bir dolar işareti yazın. Henüz Enter'a basmayın!
Bu formül özel bir formül türüdür. Enter tuşuna basarsanız, doğru olmayan bir 0 alırsınız.
Enter yazmak yerine, Enter tuşuna basarken Ctrl ve Shift tuşlarını basılı tutun. C trl + S hift + E nter'in bu sihirli kombinasyonu, bu CSE formüllerine neden diyorum.
Formülü tablonun geri kalanına kopyalamadan önce dikkate alınması gereken son bir nokta vardır. Eğiliminiz F6'yı kopyalayıp F6: G8'e yapıştırmak olabilir. Bunu denerseniz, Excel size şaşırtıcı "Bir Dizinin Parçasını Değiştiremezsiniz" iletisini verecektir. Excel, bir CSE formülünü orijinal CSE formülünü içeren bir aralığa yapıştıramayacağınızdan şikayet ediyor.
Bu konuda çalışmak kolaydır. F6 kopyalayın. F7: F8'e yapıştırın.
Kopyala F6: F8. G6: G8'e yapıştırın. İki koşula göre toplamları gösteren bir ÖAM formülleri tablonuz olacaktır.
