Excel shartini o'rnating. Agar () savdo bonusini hisoblab chiqsa, ichki funktsiyalardan foydalanib, qanday qilib Excelda

IF () funktsiyasi , inglizcha versiyasi IF (), shartlarni tekshirishda foydalaniladi. Masalan; misol uchun, \u003d IF (A1\u003e 100; "Byudjet oshdi"; "OK!")... Katakchadagi qiymatiga qarab A1 formulaning natijasi "Byudjet oshdi" yoki "OK!" bo'ladi.

IF () funktsiyasi eng ko'p ishlatiladigan funktsiyalardan biridir.

Funktsiya sintaksisi

IF (boolean_test, value_if_true, [value_if_false])

Log_Expression - TRUE yoki FALSE qiymatlarini qabul qiladigan har qanday qiymat yoki ifoda.
\u003d IF (A1\u003e \u003d 100; "Byudjet oshdi"; "OK!")
O'sha. agar kamerada bo'lsa A1 100 dan katta yoki unga teng qiymatni o'z ichiga oladi, keyin formula qaytadi OK!,agar bo'lmasa, unda Byudjetdan oshib ketdi.

Formulalar funktsiya argumentlari sifatida ishlatilishi mumkin, masalan:
\u003d IF (A1\u003e 100, SUM (B1: B10), SUM (C1: C10))
O'sha. agar kamerada bo'lsa A1 \u003e 100 qiymatini o'z ichiga oladi, so'ngra summa ustun ustida amalga oshiriladi B va agar kamroq bo'lsa, u holda ustun bilan Dan .

Ichkarida

EXCEL 2007 da argument qiymatlari sifatida qiymat_if_true va qiymat_if_false yanada murakkab tekshiruvlarni yaratish uchun 64 ta ichki IF () funktsiyalaridan foydalanishingiz mumkin.
\u003d IF (A1\u003e \u003d 100; "Byudjet oshdi"; IF (A1\u003e \u003d 90; "Katta loyiha"; IF (A1\u003e \u003d 50; "O'rta loyiha"; "Kichik loyiha")))

KO'RISH (A1; (0; 50; 90; 100); ("Kichik loyiha"; "O'rta loyiha"; "Katta loyiha"; "Byudjetdan oshib ketdi"))

VLOOKUP (A1; A3: B6; 2)

VLOOKUP () funktsiyasi uchun oraliqda yaratishingiz kerak A3: B6 qiymat jadvali:

Agar siz hujayrada bo'lsa, boshqa matnni ko'rsatishni xohlasangiz A1 salbiy qiymat, ijobiy qiymat yoki 0 bo'lsa, quyidagi formulani yozishingiz mumkin:

KO'RISH (A1; (- 1E + 307; 0; 1E-307); ("<0";"=0";">0"})

yoki matn qiymatlari o'rniga formulalarni ko'rsatishni istasangiz, bir qator kataklarga (formulalarni o'z ichiga olgan) havolalardan foydalanishingiz mumkin

KO'RISH (A24; (- 1E + 307; 0; 1E-307); A27: A29) (namunaviy faylga qarang)

Uchinchi dalil chiqarib tashlandi [qiymat_if_false]

Funktsiyaning uchinchi argumenti ixtiyoriy, agar uni tashlab qo'ysangiz, funktsiya FALSE (agar shart bajarilmasa) qaytaradi.
\u003d IF (A1\u003e 100; "Byudjet oshdi")
Agar kamerada bo'lsa A1 1 qiymatini o'z ichiga oladi, keyin yuqoridagi formula FALSE qaytadi.

Birinchi argumentda Haqiqiy yoki FALSE o'rniga raqam kiritiladi

Chunki FALSE qiymati 0 ga teng, keyin formulalar
\u003d IF (0, "Byudjet oshdi"; "OK!")
yoki (agar katakchada bo'lsa A1 0 qiymatini o'z ichiga oladi)
\u003d IF (A1, "Byudjetdan oshdi"; "OK!")
qaytib keladi OK!

Agar kamerada bo'lsa A1 0 dan boshqa har qanday son bo'lsa, u holda formula qaytadi Byudjetdan oshib ketdi... Ushbu yondashuv qiymatning nolga tengligini tekshirishda qulaydir.

IF () funktsiyasini shartlar yordamida boshqa funktsiyalar bilan bog'lash

EXCEL shuningdek, shartlar yordamida ma'lumotlarni tahlil qilishda foydalanishingiz mumkin bo'lgan boshqa funktsiyalarni o'z ichiga oladi. Masalan, COUNTIF () funktsiyasi hujayralar oralig'idagi sonlarning paydo bo'lish sonini hisoblash uchun, SUMIF () funktsiyasi esa ma'lum shartlarga javob beradigan qiymatlarni qo'shish uchun ishlatiladi.

IF () funktsiyasi, muqobil ravishda, shuningdek, shartlardan foydalanib qiymatlarni hisoblash va qo'shish uchun ishlatilishi mumkin. Tasviriy misollar quyida keltirilgan.

Ma'lumotlar oralig'ida bo'lsin A6: A11 (namunaviy faylga qarang)

SUMIF () funktsiyasi yordamida yozish orqali 10 dan katta qiymatlar yig'indisini hisoblaymiz \u003d SUMIF (A6: A11, "\u003e 10")... Shunga o'xshash natijani (23) yordamida olish mumkin
\u003d SUM (IF (A6: A11\u003e 10, A6: A11))
(o'rniga hujayraga formulani kiritish uchun KIRISH bosish kerak CTRL + SHIFT + KIRISH)

Endi hujayralar oralig'ida 10 dan katta sonlarning paydo bo'lish sonini hisoblaymiz A6: A11 \u003d COUNTIF (A6: A11, "\u003e 10")... Shunga o'xshash natijani (2) yordamida olish mumkin
\u003d COUNT (IF (A6: A11\u003e 10, A6: A11))

Endi tamoyil aniq bo'lib, IF () funktsiyasi yordamida shartlarga ega bo'lgan boshqa formulalarni tuzish mumkin. Masalan, 10 dan katta sonlar orasida minimal qiymatni topish:
\u003d MIN (IF (A6: A11\u003e 10, A6: A11))

Hujayralarni mantiqiy tekshirishni amalga oshirish qobiliyati kuchli vositadir. Siz uchun cheksiz foydalanishlarni topasiz IF A () kunlik hujjatlaringiz. Muammo shundaki, ko'pincha kerakli tekshirishlar oddiy funktsiya imkoniyatlaridan tashqarida bo'ladi. IF A () ... Bu natijani qabul qilishdan oldin ikki yoki undan ortiq shartlarni tekshirish zarur bo'lganda yuz beradi.

Bunday holatlarda Excel bir nechta variantlarni taqdim etadi: foydalanish IF A () boshqa ichida IF A (), funktsiyalari VA () va IL VA ()... Ushbu usullarni quyida ko'rib chiqamiz.

Foydalanish IF A () ichida boshqa funktsiya IF A ()

Oldin o'rganilgan funktsiyaga asoslangan variantni ko'rib chiqamiz \u003d IF (A1\u003e 1000; "juda"; "oz"). Agar A1 dagi raqam, masalan, 10.000 dan katta bo'lsa, boshqa satrni chop etish kerak bo'lsa-chi? Boshqacha qilib aytganda, agar A1\u003e 1000 rost bo'lsa, siz yana bir tekshiruv o'tkazmoqchisiz va A1\u003e 10000 ning to'g'riligini tekshirmoqchisiz. Ikkinchi funktsiyani qo'llash orqali siz bunday variantni yaratishingiz mumkin IF A () argument sifatida birinchisida _if_true qiymati: \u003d IF (A1\u003e 1000; IF (A1\u003e 10000; "juda"; "ko'p"); "oz").

Agar A1\u003e 1000 to'g'ri bo'lsa, boshqa funktsiya boshlanadi IF A (), bu A1\u003e 10000 bo'lganida "juda" qiymatini qaytaradi. Agar bu holda A1 10000 dan kam yoki unga teng bo'lsa, "lot" qiymati qaytariladi. Agar birinchi tekshirishda A1 raqami 1000 dan kam bo'lsa, "kichik" qiymati ko'rsatiladi.

Shuni esda tutingki, birinchisi noto'g'ri bo'lsa (ya'ni ifo funktsiyasining value_if_false argumentida) ikkinchi tekshiruvni bajarishingiz mumkin. A1 dagi raqam 100 dan kam bo'lganida "juda kichik" qiymatini qaytaradigan kichik bir misol: \u003d IF (A1\u003e 1000; "many"; IF (A1<100;"очень мало"; "мало")) .

Savdo bonuslarini hisoblash

Xodimlar uchun savdo bonusini hisoblashda bitta chekni ikkinchisining ichida ishlatishga yaxshi misol. klubda ishlaydigan - Heliopark Talasso, Zvenigorod mehmonxonasi. Bunday holda, agar qiymat X bo'lsa, siz bitta natijani, Y boshqa bo'lsa, Z ni xohlaysiz
- uchinchi. Masalan, muvaffaqiyatli savdo uchun bonusni hisoblashda uchta variant mavjud:

Sotuvchi rejalashtirilgan qiymatga yetmagan, bonus 0 ga teng.
Sotuvchi rejalashtirilgan qiymatdan 10 foizdan oshib ketdi, bonus 1000 rubl.
Sotuvchi rejalashtirilgan qiymatdan 10% dan oshib ketdi, bonus 10000 rublga teng.

Bunday misolni hisoblash formulasi: \u003d IF (E3\u003e 0; IF (E3\u003e 0.1; 10000; 1000); 0). Agar E3dagi qiymat salbiy bo'lsa, 0 qaytariladi (bonus yo'q). Agar natija ijobiy bo'lsa, uning 10 foizdan ko'pligi tekshiriladi va shunga qarab 1000 yoki 10 000 beriladi. 4.17 formulaning ishlash namunasini ko'rsatadi.

VA () funktsiyasi

Ko'pincha bir vaqtning o'zida ikkita shartning vafodorligini tekshirish kerak. Masalan, bonus faqat ma'lum bir mahsulotni sotish rejadan oshib ketganda va shuningdek, umumiy savdo hajmi rejadan oshib ketganda to'lanadi. Agar ushbu shartlardan biri bajarilmasa (yoki ikkala shart ham) bo'lsa, mukofot to'lanmaydi. Mantiqiy mantiqda bu mantiqiy VA deb nomlanadi, chunki butun ifoda to'g'ri bo'lishi uchun ikkala shart ham to'g'ri bo'lishi kerak.

Excelda mantiqiy iboralar VA funktsiyasi yordamida qayta ishlanadi VA (): VA (boolean_value1; boolean_value2; ...). Har bir argument sinov uchun mantiqiy qiymatdir. Siz xohlagancha argumentlarni kiritishingiz mumkin.

Funktsiyaning qanday ishlashiga e'tibor bering:

Agar barcha iboralar TRUE (yoki biron bir ijobiy son) bo'lsa, VA () TRUE qiymatini qaytaradi.
Agar bir yoki bir nechta dalillar FALSE (yoki 0) bo'lsa, VA () FALSE qaytaradi.

Hammasidan ko'proq VA () funktsiya ichida qo'llaniladi IF A ()... Bunday holda, barcha bahslar ichkarida bo'lganda VA () "TRUE" funktsiyasini qaytaradi IF A () agar haqiqiy bo'lsa, uning filial qiymatiga amal qiladi. Agar bir yoki bir nechta iboralar VA () FALSE funktsiyasini qaytaradi IF A () value_if_false filialini kuzatib boradi.

Bu erda kichik bir misol: \u003d IF (AND (C2\u003e 0; B2\u003e 0); 1000; "bonus yo'q"). Agar B2-dagi qiymat noldan katta bo'lsa va C2-dagi noldan katta bo'lsa, formula 1000 ga qaytadi, aks holda "bonus yo'q" qatori ko'rsatiladi.

Qiymatlarni toifalarga ajratish

Va () funktsiyasidan foydali foydalanish qiymati bo'yicha toifalarga ajratishdir. Masalan, sizda so'rovnoma yoki ovoz berish natijalari ko'rsatilgan jadval mavjud va siz barcha ovozlarni quyidagi yosh oralig'iga ko'ra toifalarga ajratmoqchisiz: 18-34.35-49, 50-64.65 va undan yuqori. Respondentning yoshi B9 katakchada deb hisoblasak, quyidagi funktsiya argumentlari va () diapazonga tegishli yoshni mantiqiy tekshirishni o'tkazadi: \u003d AND (B9\u003e \u003d 18; B9

Agar odamning javobi C9 katakchada bo'lsa, 18-34 yosh guruhiga muvofiqligi tekshiruvi o'tkazilsa, quyidagi formula odamning ovoz berish natijasini chiqaradi: \u003d IF (AND (B9\u003e \u003d 18; B9)

35-49: \u003d IF (VA (B9\u003e \u003d 35; B9
50-64: \u003d IF (VA (B9\u003e \u003d 50; B9
65+: \u003d IF (B9\u003e \u003d 65; C9; "")

Yoki () funktsiyasi

Shartlardan biri ijobiy bo'lganida (HAQIQAT) qaror qabul qilish kerak bo'lgan holatlar mavjud. Masalan, jami sotuvlar belgilangan miqdordan oshib ketganda yoki alohida buyumlar savdosi prognozdan yuqori bo'lsa, siz xodimlarga bonus to'lashga qaror qilishingiz mumkin. Mantiqiy mantiqda bu mantiqiy deb nomlanadi Yoki.

Bunday shartlar funktsiya yordamida Excelda tekshiriladi Yoki (): OR (boolean1, boolean2, ...). Har bir argument sinov uchun mantiqiy qiymatdir. Siz xohlagancha argumentlarni kiritishingiz mumkin. Ish natijasi Yoki () quyidagi shartlarga bog'liq:

Agar bir yoki bir nechta dalillar TRUE (ijobiy son) bo'lsa, Yoki () TRUE qiymatini qaytaradi.
Agar barcha argumentlar FALSE (nol) ga qaytsa, natija bo'ladi Yoki () FALSE bo'ladi.

Shu qatorda; shu bilan birga VA (), ko'pincha funktsiya Yoki () chex ichida ishlatiladi IF A ()... Bunday holda, ichidagi argumentlardan biri bo'lganda Yoki () "TRUE" funktsiyasini qaytaradi IF A () value_if_true o'z filialiga amal qiladi. Agar barcha ifodalar Yoki () FALSE qaytadi, funktsiya IF A () filialni kuzatib boradi qiymat_if_false... Bu erda kichik bir misol: \u003d IF (OR (C2\u003e 0; B2\u003e 0); 1000; "bonus yo'q").

Agar katakchalarning birida (C2 yoki B2) musbat raqam bo'lsa, funktsiya 1000 ga qaytadi. Ikkala qiymat ham salbiy (yoki nolga teng) bo'lganda, funktsiya "bonus yo'q" qatorini qaytaradi.

Ushbu maqola Excel elektron jadval muharririning "Agar" funktsiyasiga bag'ishlangan. Bu dasturning mantiqiy imkoniyatlari bilan shug'ullanadi. Ushbu funktsiyani ish paytida tez-tez ishlatiladigan eng keng tarqalgan deb atash mumkin.

Asosiy xususiyatlar

IN excel dasturi "Agar" formulasi ma'lum qiymatlarni taqqoslashni va natija olishni talab qiladigan har xil turdagi vazifalarni bajarishga imkon beradi. Ushbu echim dallanadigan algoritmlardan foydalanishga, qarorlar daraxtini yaratishga imkon beradi.

Amaliy misollar

Funktsiya quyidagicha ko'rinadi: \u003d IF (vazifa; rost; yolg'on). Birinchi qism mantiqiy ifoda. Bu ibora yoki raqam sifatida ko'rinishi mumkin. Masalan, "10" yoki "QQSsiz". Ushbu parametr to'ldirilishi kerak. Haqiqiy - bu ifoda to'g'ri bo'lsa, natijada ko'rsatilgan qiymat. False, vazifa noto'g'ri bo'lganda qaytariladigan ma'lumotlarni aks ettiradi.

Ikki katak parametrlarining tengligi

Excel-dagi If funktsiyasining imkoniyatlarini yaxshiroq bilish uchun siz misollar keltirishingiz kerak. Shuning uchun ularni ko'rib chiqishni boshlash kerak. Siz C1 katakchaga 8 qiymatini kiritishingiz kerak, shundan so'ng D1 manzilidagi maydonga quyidagi formulani kiritishingiz kerak: \u003d IF (C1)<10; 1; 2). В результате программа самостоятельно начинает сравнение параметров из клетки C1 со значением 10. Когда оно достигнет десяти, в поле по адресу D1 можно увидеть единица. В противном случае редактор отобразит 2.

Yana bir misolni ko'rib chiqish mumkin. Imtihon uchun bir nechta talabalar va ularning baholari mavjud. Ma'lumotlar quyidagicha: 5, 4, shuningdek 3 va 2. Topshiriq shartlariga ko'ra, har bir talaba uchun "o'tgan" yoki "o'tmagan" matnli sharh yaratish ko'zda tutilgan. Shunday qilib, agar talaba uch va undan yuqori ball olgan bo'lsa, ular imtihondan o'tgan deb hisoblanadi. Agar uning bahosi 3 dan kam bo'lsa, talaba yaxshi o'qimagan. Bunday muammoni hal qilish uchun quyidagi formulani yozing: \u003d IF (C1<3; «не справился»; «сдал»).

Dastur har bir talabaning natijalarini taqqoslashni boshlaydi. Agar indikator uchdan kam bo'lsa, tegishli katakchada "muvaffaqiyatsiz" yozuvi paydo bo'ladi. Belgilangan belgi 3 yoki undan yuqori bo'lsa, kerakli ustunda talaba imtihondan o'tmaganligi haqidagi sharhni ko'rishingiz mumkin. Shunisi e'tiborga loyiqki, matnli sharhlar har doim tirnoq ichiga olinishi kerak. Ushbu funktsiyadan foydalanganda quyidagi taqqoslash operatorlaridan foydalanishingiz mumkin:< >, =, >, <, >=, <=.

"OR", "AND" shartlaridan foydalangan holda misollar.

Ushbu dasturning mantiqiy imkoniyatlarini qo'shimcha ravishda ko'rib chiqish kerak. If funktsiyasini taqqoslash operatorlari bilan birlashtirish mumkin. Bu quyidagi parametrlar:

"OR";
"VA".

Excelda muhim shartni ko'rsatish kerak: o'quvchining bahosi 5 ga teng yoki undan kam, lekin 3 dan yuqori bo'lsa, bu holda sharh ko'rsatilishi kerak: "o'tadi" yoki "yo'q". Shunday qilib, faqat beshinchi va to'rtlikka ega bo'lgan talabalar o'tishadi. Ushbu vazifani elektron jadval muharririga yozish uchun maxsus formulani qo'llash kerak. Bu shunday ko'rinadi: \u003d IF (AND (A1.)<=5; A1>3); "Passlar", "yo'q").

Agar siz murakkabroq misolni ko'rib chiqsangiz, "OR" yoki "AND" dan foydalanishingiz kerak bo'ladi. Shunday qilib, siz ishda bir nechta shartlar mavjud bo'lsa, Excelda formulani qo'llash bilan tanishishingiz mumkin. Masalan: \u003d IF (OR (A1 \u003d 5; A1 \u003d 10); 100; 0). Bunday holda, biz xulosa qilishimiz mumkinki, A1 katakchadagi qiymat 5 yoki 10 bo'lsa, dastur natijani 100 ga aks ettiradi. Aks holda, bu 0 ga teng bo'ladi. Ushbu operatorlardan yanada murakkab masalalar echimini topish uchun foydalanish mumkin.

Masalan, ma'lumotlar bazasida siz 10 000 rubldan ortiq to'lashingiz kerak bo'lgan qarzdorlarni hisoblashingiz kerak. Qarzni olti oydan ortiq, ya'ni olti oydan beri to'lamaslik shartini qo'yishingiz mumkin. Excel elektron jadval muharririning "Agar" funktsiyasi tufayli avtomatik ravishda mos nomlar yonida "muammoli mijoz" belgisini olish mumkin. Aytaylik, A1 katakchada qarz muddati (oylar) ni ko'rsatadigan ma'lumotlar mavjud. B1 maydonida uning miqdori ko'rsatilgan.

Bunday holda, formula quyidagi shaklda keltirilgan: \u003d IF (AND (A1\u003e \u003d 6; B1\u003e 10000); "muammoli mijoz"; ""). Shunday qilib, agar belgilangan shartlarga javob beradigan shaxs aniqlansa, dastur uning nomiga qarama qarshi sharhni namoyish etadi. Ro'yxatning boshqa a'zolari uchun bu katak bo'sh qoladi.

Vaziyat juda muhim bo'lganida siz ham misolni ko'rishingiz mumkin. Tegishli sharh kiritilishi kerak. Natijada, formula quyidagi ko'rinishga ega bo'ladi: \u003d IF (OR (A1\u003e \u003d 6; B1\u003e 10000); "tanqidiy vaziyat"; ""). Ammo, agar dastur parametrlarning kamida bittasi uchun mosliklarni aniqlasa, tegishli yozuv ko'rsatiladi.

Qiyin vazifalar

O'rnatilgan nol xatolar bilan bo'linmaslik uchun Excelning "Agar" funktsiyasi ishlatiladi. Bundan tashqari, u bir nechta boshqa holatlarda qo'llaniladi. Birinchi holat "DIV / 0" deb belgilangan. Uni tez-tez uchratish mumkin. Odatda bu "A / B" formulasi nusxalashni talab qilganda sodir bo'ladi. Shu bilan birga, B ko'rsatkichi alohida hujayralar 0. Bu kabi vaziyatni oldini olish uchun, ko'rib chiqilayotgan operatorning imkoniyatlaridan foydalanishga arziydi. Shunday qilib, kerakli formula: \u003d IF (B1 \u003d 0; 0; A1 / B1). Shunday qilib, agar B1 katakchasi "nol" bilan to'ldirilgan bo'lsa, muharriri "0" ni ko'rsatadi. Aks holda, dastur A1 indikatorini B1 ma'lumotlariga ajratadi va kerakli natijani beradi.

Chegirma

Amaliyot shuni ko'rsatadiki, ko'pincha quyida ko'rib chiqiladigan vaziyatlar yuzaga keladi. Sizga ma'lum bir buyumni sotib olish uchun sarflangan umumiy miqdor asosida chegirmalarni hisoblash kerak bo'ladi. Amaldagi matritsa quyidagicha: 1000 dan kam - 0%; 1001-3000 - 3%; 3001-5000 - 5%; 5001 dan ortiq - 7%. Excelda tashrif buyuruvchilar ma'lumotlar bazasi va sotib olishga sarflangan mablag 'to'g'risidagi ma'lumotlar mavjud bo'lgan vaziyatni ko'rishingiz mumkin. Keyin har bir mijoz uchun chegirmani hisoblashingiz kerak. Buning uchun quyidagi iborani qo'llash kerak: \u003d IF (A1\u003e \u003d 5001; B1 * 0.93; IF (A1\u003e \u003d 3001; B1 * 0.95; ..).

Tizim sotib olishning umumiy miqdorini tekshiradi. Agar u 5001 rubl qiymatidan oshsa, mahsulot buyum narxining 93 foiziga ko'paytiriladi. Agar 3001 birlik belgisi oshib ketgan bo'lsa, xuddi shunday harakatlar sodir bo'ladi, ammo 95% allaqachon hisobga olinadi.