Excel shartli formatlash formulalar

Excelga shartli formatlashni qo'shish siz o'rnatgan muayyan shartlarga mos keladigan hujayra yoki hujayra doirasiga turli xil formatlash usullarini qo'llash imkonini beradi.

Formatlash opsiyalari faqat tanlangan hujayralar ushbu shartlarga javob berganda qo'llaniladi.

Qo'llanilishi mumkin bo'lgan formatlash variantlari shrift va fon rangi o'zgarishlarini, shrift turlarini, hujayra chegaralarini va ma'lumotlarni raqamli formatlashni qo'shadi.

Excel 2007 dan beri, Excelda muayyan qiymatdan katta yoki kamroq raqamlarni topish yoki o'rtacha qiymatdan yuqori yoki past raqamlarni topish kabi keng tarqalgan ishlatilgan shartlar uchun o'rnatilgan variantlar mavjud edi.

Ushbu oldindan o'rnatilgan variantlardan tashqari, foydalanuvchi tomonidan belgilangan shartlarni sinab ko'rish uchun Excel formulalarini ishlatib, shartli formatlash qoidalarini yaratish ham mumkin.

Ko'p qoidalarni qo'llash

Turli shartlarni sinab ko'rish uchun bir xil ma'lumotlarga bir nechta qoidalar qo'llanilishi mumkin. Misol uchun, byudjet ma'lumotlari ma'lum darajada - masalan, umumiy byudjetning 50%, 75% va 100% bo'lganda, formatlash o'zgarishlarini qo'llash shartlari bo'lishi mumkin.

Bunday holatlarda, Excel birinchi navbatda turli qoidalar mojaroli yoki yo'qligini aniqlaydi va agar shunday bo'lsa, dastur ma'lumotlarga qanday shartli formatlash qoidasini qo'llashni belgilash uchun o'rnatilgan tartib o'rnatiladi.

Misol: 25% dan 50% gacha bo'lgan ma'lumotlarni topish va shartli formatlash bilan ortadi

Quyidagi misolda ikkita maxsus shartli formatlash qoidalari B2 dan B5 hujayra oralig'iga qo'llaniladi.

Yuqoridagi rasmda ko'rinib turganidek, yuqoridagi shartlarning har biri to'g'ri bo'lsa, B1: B4 oralig'idagi hujayraning yoki hujayralarning fon rangi o'zgaradi.

Ushbu vazifani bajarish uchun ishlatiladigan qoidalar,

= (A2-B2) / A2> 25% = (A2-B2) / A2> 50%

Shartli formatlashni Yangi formatlash qoidalari dialog oynasi yordamida kiritiladi.

Ta'lim ma'lumotlarini kiritish

  1. Yuqoridagi rasmda ko'rinib turganidek, ma'lumotlarni A1 dan C5 xujayralariga kiriting

Eslatma: Ta'limotning 3-bosqichi shartli formatlash qoidalarining to'g'riligini tekshirish uchun A2: A5 va B2: B5 hujayralarida qiymatlar aniq foiz farqini ko'rsatuvchi C2: C4 hujayralariga formulalar qo'shiladi.

Majburiy formatlash qoidalarini belgilash

Excelda shartli formatlash uchun formulalarni ishlatish. © Ted frantsuz

Yuqorida aytib o'tilganidek, ikki shartni tekshiradigan shartli formatlash qoidalari shartli formatlashni Yangi formatlash qoidalari dialog oynasi yordamida kiritiladi.

Shartli formatlashni 25% ga ko'paytirish uchun sozlash

  1. Ishchi varaqdagi B2 dan B5 xujayralarini ajratib ko'rsatish.
  2. Ip ichki oynasini bosing.
  3. Ochilgan menyuni ochish uchun tasmada Shartli Formatlashtirish belgisini bosing.
  4. Yuqoridagi rasmda ko'rinib turganidek, yangi formatlash qoidasini ochish uchun yangi qoidani tanlang.
  5. Muloqot qutisi yuqori qismida so'nggi variantni bosing: Formuladan foydalanib, qanday hujayralarni formatlashini aniqlang.
  6. Muloqot oynasining pastki yarmida, bu formulaning to'g'ri bo'lgan format qiymatlarini bosing : yo'nalish.
  7. Quyidagi formulani yozing: = (A2-B2) / A2> 25%
  8. Formatlash hujayralarini dialog oynasini ochish uchun Format tugmasini bosing.
  9. Ushbu muloqot oynasida, to'ldirish yorlig'ini bosing va ko'k rangni to'ldirish rangini tanlang.
  10. Aloqa qutilarini yopish va ish sahifasiga qaytish uchun OK ni ikki marta bosing.
  11. B3 va B5 hujayralarining fon rangi ko'k bo'lishi kerak.

Shartli formatlashni 50% kattaroq topish uchun sozlash

  1. V-B2 dan B5 xujayralari hali tanlanmagan bo'lsa, yuqoridagi 1-6-bosqichlarni takrorlang.
  2. Quyidagi formulani yozing: = (A2-B2) / A2> berilgan joyda 50%.
  3. Formatlash hujayralarini dialog oynasini ochish uchun Format tugmasini bosing.
  4. To'ldirish yorlig'ini bosing va qizil rangni tanlang.
  5. Aloqa qutilarini yopish va ish sahifasiga qaytish uchun OK ni ikki marta bosing.
  6. B3 xujayrasining fon rangi hali ham ko'k bo'lishi kerak, chunki A3 va B3 kameralaridagi raqamlar o'rtasidagi farqlar 25% dan yuqori, lekin 50% dan kam yoki teng.
  7. B5 hujayrasining fon rangi qizil rangga o'zgarishi kerak, bu A5 va B5 kameralaridagi sonlar orasidagi farqning 50% dan yuqori ekanligini ko'rsatmoqda.

Shartli formatlash qoidalarini tekshirish

Shartli formatlash qoidalarini tekshirish. © Ted frantsuz

% Farqni hisoblash

Kiritilgan shartli formatlash qoidalarining to'g'riligini tekshirish uchun A2: A5 va B2: B5 oralig'idagi raqamlar o'rtasidagi aniq foiz farqini hisoblaydigan C2: C5 hujayralariga formulalarni kiritishimiz mumkin.

  1. Faol hujayra qilish uchun C2 uyasiga bosing.
  2. = (A2-B2) / A2 formulasini kiriting va klaviaturada Enter ni bosing.
  3. Javob 10% hujayra C2 hujayrasida raqam B2 B2 hujayrasidagi sonidan 10% ko'proq ekanligini ko'rsatib, hujayra ko'rinishi kerak.
  4. Javobni foiz sifatida ko'rsatish uchun C2 xonasida formatlashni o'zgartirish zarur bo'lishi mumkin.
  5. Formulani C2 hujayrasidan C3 dan C5 xujayralariga ko'chirish uchun to'ldirish dastagini ishlating.
  6. C3 - C5 hujayralari uchun javoblar: 30%, 25% va 60% bo'lishi kerak.
  7. Ushbu kameralardagi javoblar shartli formatlash qoidalarining to'g'ri ekanligini ko'rsatdi, chunki A3 va B3 hujayralari o'rtasidagi farq 25% dan yuqori va A5 va B5 hujayralari o'rtasidagi farq 50% dan katta.
  8. Hujayra B4 rangini o'zgartirmagan, chunki A4 va B4 xujayralari o'rtasidagi farq 25% ni tashkil qiladi va shartli formatlash qoidasi fon rangi uchun ko'k rangga o'zgartirish uchun 25% dan yuqori foizni talab qilganligini ko'rsatdi.

Shartli formatlash qoidalari uchun ustunlik tartibi

Excel shartli formatlash qoidalari menejeri. © Ted frantsuz

Shartli shartli formatlash qoidalarini qo'llash

Bir nechta qoida bir xil ma'lumotlar majmuasiga qo'llanilganda, Excel avval qoidalarga zid keladimi yoki yo'qligini aniqlaydi.

Qarama-qarshilik qoidalari har bir qoida uchun tanlangan formatlash imkoniyatlarining bir xil ma'lumotlarga tatbiq etilishi mumkin bo'lmagan holatlardir.

Ushbu qo'llanmada ishlatilgan misolda, qoidalar har ikkala qoidada ham bir xil formatlash opsiyasidan foydalanadi, chunki bu fon hujayra rangini o'zgartiradi.

Ikkinchidan, qoida haqiqiy bo'lgan holatda (qiymati farq ikki hujayradan 50% dan yuqori), keyin birinchi qoida (qiymati 25% dan kattaroq) ham to'g'ri.

Excelning ustunlik tartibi

Hujayra bir vaqtning o'zida ham qizil, ham ko'k fonga ega bo'lmagani uchun Excelga qanday shartli formatlash qoidasini qo'llashi kerakligini bilishi kerak.

Qaysi qoida qo'llaniladi? Excel tartib-qoida bo'yicha belgilanadi. Ushbu qoidada Shartli Formatlashtirish qoidalari menejeri oynasida ro'yxatda yuqoriroq bo'lgan qoida ustuvor bo'ladi.

Yuqoridagi rasmda ko'rsatilgandek, ushbu qo'llanmada (= (A2-B2) / A2> 50%) qo'llanilgan ikkinchi qoida yuqorida ko'rsatilgan va shuning uchun birinchi qoida bo'yicha ustunlikka ega.

Natijada, B5 hujayrasining fon rangi qizil rangga aylanadi.

Odatiy bo'lib, ro'yxatning ustki qismiga yangi qoidalar qo'shiladi va shuning uchun ham yuqoriroq ustunlikka egadir.

Yuqori darajadagi tartibni o'zgartirish uchun yuqoridagi va yuqoridagi rasmda ko'rsatilganidek Yuqoridagi va pastdagi o'q tugmalaridan foydalaning.

Tegishli bo'lmagan qoidalarni qo'llash

Ikki yoki undan ortiq shartli formatlash qoidalari har bir qoidani sinovdan o'tkazilsa, har ikkisi ham nizoga yo'l qo'ymaydi.

Misolimizdagi (= (A2-B2) / A2> 25%) birinchi shartli formatlash qoidasi B2 (B5) ko'k rangli chegara bilan ko'k rangli ranglar o'rniga formatlashtirilgan bo'lsa, ikkita shartli formatlash qoidalari ikkala format ham boshqasiga aralashmasdan qo'llanilishi mumkin.

Natijada, B5 hujayrasi ham ko'k chegaraga, ham qizil rangga ega bo'ladi, chunki A5 va B5 kameralaridagi raqamlar o'rtasidagi farq 25 va 50 foizdan kattaroqdir.

Shartli formatlash va muntazam ravishda formatlash

Shartli formatlash qoidalari va qo'lda qo'llaniladigan formatlash variantlari o'rtasidagi ziddiyatlar holatlarida, shartli formatlash qoidani har doim ustunlikka ega va qo'lda qo'shilgan formatlash variantlari o'rniga qo'llaniladi.

Agar sariq fon rangi misolda B2 dan B5ga qo'llanilsa, shartli formatlash qoidalari qo'shilgach, faqat B2 va B4 xujayralari sarg'ayadi.

Kiritilgan shartli formatlash qoidalari B3 va B5 hujayralarida qo'llanilganligi sababli ularning fon ranglari sariqdan ko'k va qizilga to'g'ri keladi.