Excel koşulunu ayarlayın. () Satış bonusunu hesaplarsa, Excel'de iç içe geçmiş işlevleri kullanarak nasıl

EĞER () işlevi IF in İngilizce versiyonu (), koşulları kontrol ederken kullanılır. Örneğin, \u003d EĞER (A1\u003e 100; "Bütçe aşıldı"; "Tamam!")... Hücredeki değere bağlı olarak A1 formülün sonucu "Bütçe Aşıldı" veya "Tamam!" olacaktır.

IF () işlevi, en sık kullanılan işlevlerden biridir.

İşlev sözdizimi

EĞER (boolean_test, değer_if_se, [yanlışsa_değer])

Log_Expression - DOĞRU veya YANLIŞ olabilecek herhangi bir değer veya ifade.
\u003d EĞER (A1\u003e \u003d 100; "Bütçe aşıldı"; "Tamam!")
Şunlar. hücrede ise A1 100'den büyük veya 100'e eşit bir değer içeriyorsa formül geri dönecektir TAMAM MI!,ve değilse, o zaman Bütçe aşıldı.

Formüller işlev bağımsız değişkenleri olarak kullanılabilir, örneğin:
\u003d EĞER (A1\u003e 100, TOPLA (B1: B10), TOPLA (C1: C10))
Şunlar. hücrede ise A1 100'den büyük bir değer içeriyorsa, sütun üzerinde toplama gerçekleştirilir B ve daha azsa sütuna göre İLE .

İç içe EĞER

EXCEL 2007'de bağımsız değişken değerleri olarak doğru ise_değer ve yanlışsa_değer daha karmaşık denetimler oluşturmak için 64 adede kadar iç içe geçmiş IF () işlevi kullanabilirsiniz.
\u003d EĞER (A1\u003e \u003d 100; "Bütçe aşıldı"; EĞER (A1\u003e \u003d 90; "Büyük proje"; IF (A1\u003e \u003d 50; "Orta proje"; "Küçük proje")))

GÖRÜNÜM (A1; (0; 50; 90; 100); ("Küçük proje"; "Orta proje"; "Büyük proje"; "Bütçe aşıldı"))

DÜŞEYARA (A1; A3: B6; 2)

DÜŞEYARA () işlevi için, aralıkta oluşturmanız gerekir A3: B6 değer tablosu:

Hücrede ise farklı bir metin görüntülemek istiyorsanız A1 negatif değer, pozitif değer veya 0 ise aşağıdaki formülü yazabilirsiniz:

ARA (A1; (- 1E + 307; 0; 1E-307); ("<0";"=0";">0"})

veya metin değerleri yerine formülleri görüntülemek istiyorsanız, bir hücre aralığına (formül içeren) başvurular kullanabilirsiniz.

ARA (A24; (- 1E + 307; 0; 1E-307); A27: A29) (örnek dosyaya bakın)

Üçüncü argüman atlandı [yanlışsa_değer]

İşlevin üçüncü bağımsız değişkeni isteğe bağlıdır; atlarsanız işlev YANLIŞ döndürür (koşul karşılanmazsa).
\u003d EĞER (A1\u003e 100; "Bütçe Aşıldı")
Bir hücrede ise A1 1 değerini içeriyorsa, yukarıdaki formül YANLIŞ döndürür.

İlk bağımsız değişkende TRUE veya FALSE yerine sayı girilir

Çünkü FALSE değeri 0'a eşittir, ardından formüller
\u003d EĞER (0, "Bütçe aşıldı"; "Tamam!")
veya (hücrede ise A1 0 değerini içerir)
\u003d EĞER (A1, "Bütçe aşıldı"; "Tamam!")
dönecek TAMAM MI!

Bir hücrede ise A1 0 dışında herhangi bir sayı olursa formül şunu döndürür Bütçe aşıldı... Bu yaklaşım, bir değerin sıfıra eşitliği test edilirken kullanışlıdır.

IF () işlevini koşulları kullanarak diğer işlevlerle ilişkilendirme

EXCEL, verileri koşulları kullanarak analiz etmek için kullanabileceğiniz başka işlevler de içerir. Örneğin, EĞERSAY () işlevi, bir hücre aralığındaki sayıların oluşum sayısını saymak için kullanılır ve SUMIF () işlevi, belirli koşulları karşılayan değerler eklemek için kullanılır.

IF () işlevi, alternatif olarak, koşulları kullanarak değerleri saymak ve eklemek için de kullanılabilir. Açıklayıcı örnekler aşağıda verilmiştir.

Verilerin aralıkta olmasına izin verin A6: A11 (örnek dosyaya bakın)


SUMIF () işlevini kullanarak 10'dan büyük değerlerin toplamını yazarak hesaplayalım \u003d SUMIF (A6: A11; "\u003e 10")... Benzer bir sonuç (23) kullanılarak elde edilebilir
\u003d TOPLA (EĞER (A6: A11\u003e 10, A6: A11))
(hücreye formül girmek yerine GİRİŞ basmaya ihtiyacım var CTRL + ÜST KRKT + ENTER)

Şimdi hücre aralığında 10'dan büyük sayıların oluşum sayısını sayalım. A6: A11 \u003d EĞERSAY (A6: A11; "\u003e 10")... Benzer bir sonuç (2) kullanılarak elde edilebilir
\u003d SAYI (EĞER (A6: A11\u003e 10, A6: A11))

Artık ilke netleştiğine göre, koşullu diğer formüller IF () işlevi kullanılarak oluşturulabilir. Örneğin, 10'dan büyük sayılar arasında minimum değeri bulmak:
\u003d MİN (EĞER (A6: A11\u003e 10, A6: A11))

Hücreler üzerinde mantıksal kontroller gerçekleştirme yeteneği güçlü bir araçtır. İçin sonsuz kullanım bulacaksınız EĞER() günlük belgelerinizde. Sorun, genellikle gerekli kontrollerin basit bir işlevin yeteneklerinin ötesinde olmasıdır. EĞER() ... Bu, sonucu kabul etmeden önce iki veya daha fazla koşulu kontrol etmek gerektiğinde olur.

Bu tür durumlarda, Excel birkaç seçenek sunar: EĞER() diğerinin içinde EĞER(), fonksiyonlar VE() ve IL VE()... Aşağıda bu yöntemlere bakacağız.

Kullanma EĞER() başka bir işlevin içinde EĞER()

Daha önce çalışılan fonksiyona dayalı bir varyantı düşünelim \u003d IF (A1\u003e 1000; "çok"; "az"). Ya A1'deki sayı örneğin 10.000'den büyük olduğunda başka bir dize yazdırmanız gerekirse? Başka bir deyişle, A1\u003e 1000 doğruysa, başka bir kontrol yapmak ve A1\u003e 10000 doğru olup olmadığını görmek istersiniz. İkinci işlevi uygulayarak böyle bir varyant oluşturabilirsiniz. EĞER() ilk bağımsız değişken olarak _if_true: \u003d EĞER (A1\u003e 1000; EĞER (A1\u003e 10000; "çok fazla"; "çok"); "küçük") değeri.

A1\u003e 1000 doğruysa, başka bir işlev başlar EĞER()A1\u003e 10000 olduğunda "çok fazla" değerini döndürür. Ancak A1, 10.000'den küçük veya eşitse, "çok" değeri döndürülür. İlk kontrol sırasında A1 numarası 1000'den küçükse, "küçük" değeri görüntülenecektir.

İlk kontrolün yanlış olup olmadığını (yani, ifo fonksiyonunun yanlışsa_değer bağımsız değişkeninde) ikinci kontrolü de çalıştırabileceğinizi unutmayın. A1'deki sayı 100'den küçük olduğunda "çok küçük" değerini döndüren küçük bir örnek: \u003d IF (A1\u003e 1000; "many"; IF (A1<100;"очень мало"; "мало")) .

Satış Primi Hesaplama

Bir çeki diğerinin içinde kullanmanın güzel bir örneği, personel için satış bonusunu hesaplamaktır. Kulüpte çalışan - otel Heliopark Thalasso, Zvenigorod. Bu durumda, değer X ise, bir sonuç istersiniz, Y diğeriyse, Z ise
- üçüncü. Örneğin, başarılı satışlar için bir bonus hesaplanması durumunda, üç seçenek mümkündür:

  1. Satıcı planlanan değere ulaşmadı, bonus 0.
  2. Satıcı planlanan değeri% 10'dan daha az aştı, bonus 1.000 ruble.
  3. Satıcı planlanan değeri% 10'dan fazla aştı, bonus 10.000 ruble'ye eşit.

İşte böyle bir örneği hesaplamak için formül: \u003d EĞER (E3\u003e 0; EĞER (E3\u003e 0.1; 10000; 1000); 0). E3'teki değer negatifse, 0 döndürülür (bonus yok). Sonuç olumlu ise% 10'dan fazla olup olmadığı kontrol edilir ve buna bağlı olarak 1.000 veya 10.000 çıkarılır. 4.17, formülün nasıl çalıştığına dair bir örnek gösterir.

VE () işlevi

Çoğunlukla yalnızca iki koşulun aynı anda uygunluğunu kontrol etmek gerekir. Örneğin, bir ikramiye yalnızca belirli bir ürünün satışları planı aştığında ve ayrıca toplam satışlar planı aştığında ödenir. Bu koşullardan biri (veya her iki koşul) karşılanmazsa, prim ödenmez. Boole mantığında, buna mantıksal AND denir çünkü tüm ifadenin doğru olması için her iki koşulun da doğru olması gerekir.

Excel'de boole ifadeleri VE işlevi kullanılarak işlenir VE(): AND (boolean_value1; boolean_value2; ...). Her bağımsız değişken, test edilecek bir boole değeridir. İstediğiniz kadar argüman girebilirsiniz.

İşlevin nasıl çalıştığını not edelim:

  • Tüm ifadeler DOĞRU (veya herhangi bir pozitif sayı) döndürürse, VE() DOĞRU döndürür.
  • Bir veya daha fazla bağımsız değişken YANLIŞ (veya 0) döndürürse, VE() YANLIŞ döndürür.

Daha sık VE() bir işlevin içine uygulanır EĞER()... Bu durumda, tüm argümanlar içeride olduğunda VE() DOĞRU, işlev döndürür EĞER() eğer doğruysa şube değerini izleyecektir. Bir veya daha fazla ifadenin VE() FALSE döndürür, işlev EĞER() dallanma değeri_if_false'yi izleyecektir.

İşte küçük bir örnek: \u003d EĞER (VE (C2\u003e 0; B2\u003e 0); 1000; "bonus yok"). B2'deki değer sıfırdan büyükse ve C2'deki değer sıfırdan büyükse, formül 1000 değerini döndürür, aksi takdirde "bonus yok" satırı görüntülenir.

Değerleri kategorilere ayırma

Ve () işlevinin yararlı bir kullanımı, değere göre kategorilere ayırmaktır. Örneğin, bir anketin veya oylamanın sonuçlarını içeren bir tablonuz var ve tüm oyları aşağıdaki yaş aralıklarına göre kategorilere ayırmak istiyorsunuz: 18-34.35-49, 50-64.65 veya daha fazlası. Yanıtlayanın yaşının B9 hücresinde olduğunu varsayarsak, aşağıdaki işlev bağımsız değişkenleri ve () aralığa ait yaş için mantıksal bir kontrol gerçekleştirir: \u003d VE (B9\u003e \u003d 18; B9


Kişinin cevabı C9 hücresindeyse, kontrol 18-34 yaş grubuna uygunluk için tetiklenirse aşağıdaki formül kişinin oylama sonucunu verecektir: \u003d IF (AND (B9\u003e \u003d 18; B9

  • 35-49: \u003d EĞER (VE (B9\u003e \u003d 35; B9
  • 50-64: \u003d EĞER (VE (B9\u003e \u003d 50; B9
  • 65+: \u003d EĞER (B9\u003e \u003d 65; C9; "")

VEYA () işlevi

Yalnızca koşullardan biri olumlu (DOĞRU) olduğunda bir karara ihtiyaç duyduğunuz durumlar vardır. Örneğin, toplam satışlar hedefin üzerinde olduğunda veya tek bir ürünün satışları tahminlerin üzerinde olduğunda çalışanlara bir prim ödemeye karar verebilirsiniz. Boole mantığında buna boole denir VEYA.

Bu tür koşullar, işlev kullanılarak Excel'de kontrol edilir. VEYA(): VEYA (boolean1, boolean2, ...). Her bağımsız değişken, test edilecek bir boole değeridir. İstediğiniz kadar argüman girebilirsiniz. İşin sonucu VEYA() aşağıdaki koşullara bağlıdır:

  • Bir veya daha fazla bağımsız değişken DOĞRU (herhangi bir pozitif sayı) döndürürse, VEYA() DOĞRU döndürür.
  • Tüm bağımsız değişkenler YANLIŞ (sıfır) döndürürse, sonuç VEYA() YANLIŞ olacaktır.

Hem de VE()çoğu zaman işlev VEYA() kontrol içinde kullanılmış EĞER()... Bu durumda, içindeki argümanlardan biri VEYA() DOĞRU, işlev döndürür EĞER() kendi dal değeri_if_true'yu izleyecektir. İçindeki tüm ifadeler VEYA() FALSE, işlev döndürür EĞER() şubeyi takip edecek yanlışsa_değer... İşte küçük bir örnek: \u003d EĞER (VEYA (C2\u003e 0; B2\u003e 0); 1000; "bonus yok").

Hücrelerden birinde (C2 veya B2) pozitif bir sayı varsa, işlev 1000 değerini döndürecektir. Yalnızca her iki değer de negatif (veya sıfıra eşit) olduğunda, işlev "bonus yok" dizesini döndürür.

Bu makale, Excel elektronik tablo düzenleyicisinin "Eğer" işlevine odaklanacaktır. Uygulamanın mantıksal yetenekleriyle ilgilenir. Bu işlev, genellikle işlem sırasında kullanılan en yaygın olanla ilişkilendirilebilir.


Ana Özellikler

İÇİNDE excel programı "Eğer" formülü, belirli değerleri karşılaştırmanızı ve bir sonuç almanızı gerektiren çeşitli görev türlerine izin verir. Bu çözüm, dallanma algoritmalarını kullanmayı, bir karar ağacı oluşturmayı mümkün kılar.

Uygulama örnekleri

İşlev şöyle görünür: \u003d EĞER (görev; doğru; yanlış). İlk bölüm bir boole ifadesidir. Bir ifade veya sayı olarak görünebilir. Örneğin, "10" veya "KDV'siz". Bu parametre doldurulmalıdır. Doğru, ifade doğruysa sonuç olarak görüntülenen değerdir. Yanlış, görev yanlış olduğunda döndürülecek verileri temsil eder.

İki hücrenin parametrelerinin eşitliği

Excel'deki If işlevinin yeteneklerini daha iyi anlamak için örnekler sağlamanız gerekir. Bu nedenle, onları daha fazla düşünmeye başlamaya değer. C1 hücresine 8 değerini girmeniz gerekir. Bundan sonra, D1 adresindeki alana şu biçimde bir formül girmelisiniz: \u003d IF (C1<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.

Başka bir örnek düşünülebilir. Sınav için kazanılan birkaç öğrenci ve notları vardır. Veriler şu şekildedir: 5, 4 ve ayrıca 3 ve 2. Ödev şartlarına göre, her öğrenci için "geçti" veya "geçmedi" şeklinde bir metin yorumu oluşturulması öngörülmüştür. Bu nedenle, bir öğrenci üç veya daha yüksek bir not alırsa, sınavı geçmiş sayılır. Notu 3'ten az ise, öğrenci başarılı olamamıştır. Böyle bir sorunu çözmek için aşağıdaki formülü yazın: \u003d EĞER (C1<3; «не справился»; «сдал»).

Program, her öğrencinin sonuçlarını karşılaştırmaya başlar. Gösterge üçten az ise, ilgili hücrede "başarısız" yazısı görünecektir. Puan 3 veya daha yüksek olduğunda, gerekli sütunda öğrencinin sınavı geçemediğine dair bir yorum görebilirsiniz. Metin yorumlarının her zaman tırnak içine alınması gerektiğini belirtmek gerekir. Bu işlevi kullanırken, aşağıdaki karşılaştırma operatörlerini kullanabilirsiniz:< >, =, >, <, >=, <=.

"VEYA", "VE" koşullarını kullanan örnekler

Bu uygulamanın mantıksal olanaklarını daha fazla düşünmek gerekir. If işlevini karşılaştırma işleçleriyle birleştirmek mümkündür. Bunlar aşağıdaki parametrelerdir:

"VEYA";
"VE".

Excel'de önemli bir koşulu belirtmek gerekir: bir öğrencinin notu 5'e eşit veya az, ancak 3'ten fazla olduğunda, bir yorum görüntülenmelidir: "geçer" veya "hayır". Böylece, yalnızca beş ve dörtlük kazanan öğrenciler geçer. Bu görevi bir elektronik tablo düzenleyicisine yazmak için özel bir formül uygulamalısınız. Şöyle görünür: \u003d IF (AND (A1<=5; A1>3); "Geçer", "hayır").

Daha karmaşık bir örnek düşünürseniz, "VEYA" veya "VE" kullanmanız gerekecektir. Böylece, işte birden çok koşul varsa Excel'de bir formül uygulama konusunda bilgi sahibi olabilirsiniz. Örneğin: \u003d EĞER (VEYA (A1 \u003d 5; A1 \u003d 10); 100; 0). Bu durumda, A1 hücresindeki değer 5 veya 10 olduğunda programın sonucu 100 gösterdiği sonucuna varabiliriz. Aksi takdirde 0'dır. Bu operatörleri daha karmaşık problemlere çözüm bulmak için kullanmak mümkündür.

Örneğin, veritabanında 10.000 ruble'den fazla ödemesi gereken borçluları hesaplamanız gerekir. Kredinin altı aydan fazla, yani altı aydır geri ödenmemesi koşulunu belirleyebilirsiniz. Excel elektronik tablo düzenleyicisinin "If" işlevi sayesinde, karşılık gelen adların yanında otomatik olarak "sorunlu müşteri" işaretini almak mümkündür. Diyelim ki A1 hücresi borcun vadesini (ayları) gösteren veriler içeriyor. B1 alanı miktarı içerir.

Bu durumda, formül aşağıdaki biçimde sunulur: \u003d EĞER (VE (A1\u003e \u003d 6; B1\u003e 10000); "sorunlu müşteri"; ""). Bu nedenle, belirtilen koşulları karşılayan bir kişi belirlenirse, program adının karşısında karşılık gelen bir açıklama görüntüler. Listenin diğer üyeleri için bu hücre boş kalacaktır.

Durum kritik olduğunda bir örnek de görebilirsiniz. Uygun bir yorum girilmelidir. Sonuç olarak, formül aşağıdaki gibi olacaktır: \u003d EĞER (VEYA (A1\u003e \u003d 6; B1\u003e 10000); "kritik durum"; ""). Bununla birlikte, program parametrelerden en az biri için eşleşme tespit ederse, ilgili not görüntülenir.

Zor görevler

Sıfır hatayla yerleşik bölünmeyi önlemek için Excel'in "If" işlevi kullanılır. Ek olarak, diğer birçok durumda kullanılır. İlk durum "DIV / 0" olarak belirlenmiştir. Oldukça sık bulunabilir. Bu genellikle "A / B" formülünün kopyalanmasını gerektirdiğinde olur. Aynı zamanda, B göstergesi bireysel hücreler 0'dır. Böyle bir durumdan kaçınmak için, söz konusu operatörün yeteneklerini kullanmaya değer. Dolayısıyla, gerekli formül: \u003d EĞER (B1 \u003d 0; 0; A1 / B1). Bu nedenle, B1 hücresi "sıfır" değeriyle doldurulmuşsa, düzenleyici "0" gösterecektir. Aksi takdirde, program A1 göstergesini B1 verilerine böler ve gerekli sonucu verir.

İndirim

Uygulamada görüldüğü gibi, aşağıda tartışılan durumlar sıklıkla ortaya çıkar. İndirimleri, belirli bir ürünün satın alınması için harcanan toplam tutara göre hesaplamanız gerekecektir. Kullanılan matris aşağıdaki gibidir:% 1000 - 0'dan az; 1001-3000 -% 3; 3001-5000 -% 5; % 5001 -% 7'den fazla. Excel'de bir ziyaretçi veri tabanına ve bir satın alma işlemi için harcanan miktar hakkında bilgi olduğunda durumu görebilirsiniz. Bir sonraki adım, her müşteri için indirimi hesaplamaktır. Bunu yapmak için aşağıdaki ifadeyi uygulamanız gerekir: \u003d EĞER (A1\u003e \u003d 5001; B1 * 0.93; EĞER (A1\u003e \u003d 3001; B1 * 0.95; ..).

Sistem, toplam satın alma miktarını kontrol eder. 5001 ruble değerini aşarsa, ürün, ürün fiyatının yüzde 93'ü ile çarpılır. 3001 birimlik işaret aşılırsa, benzer bir işlem gerçekleşir, ancak% 95'i zaten dikkate alınır.



Okumanızı öneririz