Qoidaga ko'ra, keng ko'lamli, murakkab operatsiyalarning samaradorligini bitta W ko'rsatkichi yordamida tavsiflash mumkin emas, unga boshqa, qo'shimcha W 1, W 2,..., Vt yordam berishi kerak; Ulardan ba'zilari ko'proq qilish tavsiya etiladi, boshqalari - kamroq. Masalan, korxona faoliyatini baholashda bir qator ko'rsatkichlarni hisobga olish kerak:

• mahsulotlarning to'liq hajmi

  xarajat narxi va boshqalar.

Jangovar operatsiyani tahlil qilishda, asosiy ko'rsatkichga qo'shimcha ravishda, dushmanga etkazilgan zararni matematik kutish, bir qator qo'shimchalarni hisobga olish kerak:

o'z yo'qotishlari

operatsiyani bajarish vaqti,

o'q-dorilarni iste'mol qilish va boshqalar.

Ba'zilarini maksimal darajada oshirish, boshqalari esa minimallashtirish maqsadga muvofiq bo'lgan samaradorlik ko'rsatkichlarining ko'pligi har qanday murakkab operatsiyalarni o'rganish muammosiga xosdir. Bunday holda, to'g'ri shakllantirish "ma'lum xarajatlarda maksimal samaraga erishish" yoki "minimal xarajatlar bilan berilgan samaraga erishish" dir. Umumiy holda, bitta ko'rsatkich W 1 ni maksimalga va bir vaqtning o'zida boshqa W 2 ko'rsatkichini maksimal (yoki minimal) ga aylantiradigan yechim yo'q; Bundan tashqari, bunday yechim bir nechta ko'rsatkichlar uchun mavjud emas. Biroq, samaradorlikning miqdoriy tahlili bir nechta ko'rsatkichlar bo'lsa ham foydali bo'lishi mumkin, chunki oldindan tashlab yuborish imkonini beradi aniq mantiqsiz har jihatdan eng yaxshi variantlardan past bo'lgan yechim variantlari.

Keling, bir misolni ko'rib chiqaylik. Keling, ikkita ko'rsatkich bilan baholangan Q jangovar operatsiyasini tahlil qilaylik:

V- jangovar topshiriqni bajarish ehtimoli;

S- sarflangan mablag'lar qiymati.

Birinchi ko'rsatkichni maksimal darajaga, ikkinchisini esa minimal darajaga aylantirish tavsiya etiladi.

Faraz qilaylik, 20 xil yechim variantlari x 1, x 2,…, x 20. Ularning har biri uchun ikkala W va S ko'rsatkichlarining qiymatlari ma'lum (1.1-rasmga qarang).

Rasmda ba'zi yechim variantlari darhol bekor qilinishi mumkinligini ko'rsatadi. Ikki ko'rsatkich bo'yicha samaradorlikni baholashda qaysi variantlarga ustunlik berish kerak. Shubhasiz, mintaqaning o'ng va pastki chegaralarida bir vaqtning o'zida yotadiganlar (1.1-rasmda - nuqta chiziq). Bu. To'rtta variant qoldi: X 16, X 17, X 19, X 20. Ulardan X 16 eng samarali, ammo nisbatan qimmat; X 20 eng arzon, ammo unchalik samarali emas. Qaror qabul qiluvchining vazifasi samaradorlikning ma'lum bir o'sishi uchun qanday narxni to'lashimiz mumkinligini yoki aksincha, juda katta moddiy yo'qotishlarga yo'l qo'ymaslik uchun qanday samaradorlikni qurbon qilishga tayyor ekanligimizni aniqlashdir.

Sx

x

x x x

x x x

x x

x x x

x x

x x

x

x x

Bir vaqtning o'zida bir nechta ko'rsatkichlar yordamida operatsiyani har tomonlama baholash qiyin bo'lganligi sababli, amalda bir nechta ko'rsatkichlar bitta umumlashtirilgan ko'rsatkichga birlashtiriladi. Ko'pincha bunday mezon sifatida kasr olinadi; hisoblagichga W 1 ,…, W ko'rsatkichlarini qo'ying , oshirish maqsadga muvofiq, va maxrajda - kamaytirish maqsadga muvofiq:

U=
(4)

(4) turdagi mezonning umumiy kamchiligi shundaki, bitta ko'rsatkich bo'yicha samaradorlikning etishmasligi har doim boshqasi tomonidan qoplanishi mumkin (masalan, o'q-dorilarning kam sarflanishi tufayli jangovar topshiriqni bajarish ehtimoli pastligi va boshqalar).

Ko'pincha, kompozit mezonlar individual ishlash ko'rsatkichlarining "vaznli yig'indisi" sifatida qabul qilinadi:

U=a
+α
+…+α
(5)

Bu erda a - ijobiy yoki manfiy koeffitsientlar.

Ijobiy ko'rsatkichlar maksimal darajaga ko'tarilishi kerak bo'lgan ko'rsatkichlar uchun belgilanadi; salbiy - minimallashtirish kerak bo'lganlar uchun. Koeffitsientlarning mutlaq qiymatlari ko'rsatkichlarning muhimlik darajasiga mos keladi. (5) turdagi mezon bir xil kamchilikka ega (heterojen ko'rsatkichlarni o'zaro kompensatsiya qilish imkoniyati) va noto'g'ri tavsiyalarga olib kelishi mumkin. Biroq, a i o'zboshimchalik bilan tanlanmagan, ammo kompozit mezon eng yaxshi bo'lishi uchun tanlangan hollarda. funktsiyasini bajargan bo'lsa, uning yordami bilan cheklangan qiymatli natijalarni olish mumkin.

Ba'zi hollarda, bir nechta ko'rsatkichlar bilan bog'liq muammo, agar bitta (asosiy) samaradorlik ko'rsatkichi W 1 ni ajratib ko'rsatsa va uni maksimal darajaga ko'tarishga harakat qilsa va qolgan yordamchi ko'rsatkichlarga W shaklning faqat ba'zi cheklovlarini qo'ysa, bitta ko'rsatkich bilan bog'liq muammoga aylanishi mumkin. 2, V 3,...

V
; …V
; V
; ...V

Keyinchalik bu cheklovlar belgilangan shartlar majmuasiga kiritiladi a 1 , a 2 ,… .

Muammoning ushbu shakllantirilishi bilan barcha ishlash ko'rsatkichlari, asosiysidan tashqari, toifaga o'tkaziladi berilgan ish sharoitlari. Berilgan chegaralarga to'g'ri kelmaydigan yechim variantlari darhol o'chiriladi. Olingan tavsiyalar, shubhasiz, qo'llab-quvvatlovchi ko'rsatkichlar uchun chegaralar qanday tanlanganiga bog'liq bo'ladi. Bu yechim tanlash bo'yicha yakuniy tavsiyalarga qanchalik ta'sir qilishini aniqlash uchun cheklovlarni oqilona chegaralar ichida o'zgartiring.

Murosa yechimini yaratishning yana bir usuli bor, uni "deyish mumkin. ketma-ket imtiyozlar usuli" Ishlash ko'rsatkichlari ahamiyatini pasaytirish tartibida joylashtirilsin: birinchi navbatda asosiy W 1, keyin boshqa yordamchilar: W 2, V 3, .... Oddiylik uchun biz ularning har birini maksimal darajaga aylantirish kerakligini taxmin qilamiz (agar bunday bo'lmasa, eksponentning belgisini o'zgartirish kifoya). Murosali yechimni qurish tartibi quyidagicha. Birinchidan, W 1 asosiy samaradorlik ko'rsatkichini maksimal darajada oshiradigan yechim izlanadi. Keyinchalik, amaliy mulohazalar va dastlabki ma'lumotlarning ma'lum bo'lgan aniqligi (ko'pincha kichik) asosida ma'lum bir "imtiyoz" DW 1 tayinlanadi, biz ikkinchi ko'rsatkich W 2 ni maksimal darajada oshirish uchun ruxsat berishga rozimiz. Biz W 1 indikatoriga W 1 * - ∆W 1 dan kam bo'lmasligi uchun cheklov qo'yamiz (W 1 * - W 1 ning maksimal mumkin bo'lgan qiymati) va bu cheklash bilan biz W 2 ni aylantiradigan echimni qidiramiz. maksimal darajada.

Keyinchalik, W 2 indikatorida yana "kontsessiya" tayinlanadi, uning narxiga W 3 ni maksimal darajada oshirish mumkin va hokazo. Bu usul yaxshi, chunki bir ko'rsatkichdagi "imtiyoz" boshqasida qanday xarajat evaziga olinganligi darhol aniq bo'ladi. Shu bilan birga, hatto kichik imtiyozlar evaziga olingan yechimni tanlash erkinligi ham muhim bo'lishi mumkin, chunki Maksimal mintaqada eritmaning samaradorligi odatda juda kam o'zgaradi.

Qanday bo'lmasin, rasmiylashtirishning har qanday usuli bilan bir nechta ko'rsatkichlar bo'yicha qarorni miqdoriy jihatdan asoslash vazifasi to'liq aniqlanmagan bo'lib qoladi va yechimning yakuniy tanlovi "qo'mondon" ning ixtiyoriy harakati bilan belgilanadi. Tadqiqotchining vazifasi - "qo'mondon" ni etarli miqdordagi ma'lumotlar bilan ta'minlash, unga har bir yechim variantining afzalliklari va kamchiliklarini har tomonlama baholash va ular asosida yakuniy tanlov qilish imkonini beradi.

Funktsiya bo'yicha darsimizning ikkinchi qismida VLOOKUP(VLOOKUP) Excelda biz barcha kuchingizni yo'naltirishga yordam beradigan bir nechta misollarni ko'rib chiqamiz VLOOKUP eng ambitsiyali Excel muammolarini hal qilish uchun. Misollar, siz ushbu funktsiya qanday ishlashi haqida asosiy bilimga ega ekanligingizni taxmin qiladi. Agar yo'q bo'lsa, siz ushbu qo'llanmaning sintaksis va asosiy foydalanishni tushuntiradigan birinchi qismidan boshlashni xohlashingiz mumkin. VLOOKUP. Xo'sh, keling, boshlaylik.

Bir nechta mezonlardan foydalangan holda Excelda qidiring

Funktsiya VLOOKUP Excel ma'lumotlar bazasida ma'lum bir qiymatni qidirish uchun juda kuchli vositadir. Biroq, sezilarli cheklov mavjud - uning sintaksisi faqat bitta qiymatni qidirishga imkon beradi. Agar bir nechta shartlar yordamida qidirish kerak bo'lsa-chi? Siz quyida yechim topasiz.

1-misol: 2 xil mezondan foydalanib qidiruv

Aytaylik, bizda buyurtmalar ro‘yxati bor va biz topmoqchimiz Tovarlar miqdori(Miqdor), ikkita mezon asosida - Mijoz nomi(mijoz) va Mahsulot nomi(Mahsulot). Quyidagi jadvaldan ko'rinib turibdiki, xaridorlarning har biri bir necha turdagi tovarlarga buyurtma berganligi bilan masala murakkablashadi:

Doimiy funktsiya VLOOKUP bu stsenariyda ishlamaydi, chunki u berilgan qidiruv qiymatiga mos keladigan birinchi topilgan qiymatni qaytaradi. Misol uchun, agar siz buyumning miqdorini bilmoqchi bo'lsangiz Shirinliklar, xaridor tomonidan buyurtma qilingan Jeremy Hill, quyidagi formulani yozing:

VLOOKUP(B1,$A$5:$C$14,3, YOLG'ON)
=VLOOKUP(B1,$A$5:$C$14,3, FALSE)

– bu formula natijani qaytaradi 15 , mahsulotga mos keladi Olmalar, chunki u mos keladigan birinchi qiymatdir.

Oddiy vaqtinchalik echim bor - barcha kerakli mezonlarni birlashtiradigan qo'shimcha ustun yarating. Bizning misolimizda bu ustunlar Mijoz nomi(mijoz) va Mahsulot nomi(Mahsulot). Esda tutingki, birlashtirilgan ustun har doim qidiruv oralig'idagi eng chap ustun bo'lishi kerak, chunki bu funktsiyaning chap ustunidir. VLOOKUP qiymatni qidirishda ko'rinadi.

Shunday qilib, siz jadvalga yordamchi ustun qo'shasiz va uning barcha kataklari bo'ylab shunday formuladan nusxa ko'chirasiz: =B2&C2. Agar siz satrning o'qilishi mumkin bo'lishini istasangiz, birlashtirilgan qiymatlarni bo'sh joy bilan ajratishingiz mumkin: =B2&” “&C2. Shundan so'ng siz quyidagi formuladan foydalanishingiz mumkin:

VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4, FALSE)
=VLOOKUP("Jeremy Hill Sweets";$A$7:$D$18;4;FALSE)

VLOOKUP(B1,$A$7:$D$18,4, YOLG'ON)
=VLOOKUP(B1,$A$7:$D$18,4, FALSE)

Hujayra qayerda B1 argumentning birlashtirilgan qiymatini o'z ichiga oladi qidiruv_qiymati(qidiruv_qiymati) va 4 - dalil col_index_num(ustun_raqami), ya'ni. Qabul qilinadigan ma'lumotlarni o'z ichiga olgan ustun raqami.

2-misol: VLOOKUP ikkita mezon asosida jadval boshqa varaqda ko'riladi

Agar siz boshqa varaqda yoki boshqa Excel ish kitobida joylashgan ikkinchi jadvaldan (jadvalni qidirish) ma'lumotlarni qo'shish orqali Asosiy jadvalni yangilashingiz kerak bo'lsa, kerakli qiymatni to'g'ridan-to'g'ri asosiy jadvalga kiritgan formulada to'plashingiz mumkin. .

Oldingi misolda bo'lgani kabi, Qidiruv jadvalida birlashtirilgan qiymatlarga ega yordamchi ustun kerak bo'ladi. Bu ustun qidiruv uchun belgilangan diapazondagi eng chap ustun bo'lishi kerak.

Shunday qilib, formula bilan VLOOKUP shunday bo'lishi mumkin:

VLOOKUP(B2&" "&C2, Buyurtmalar!$A&$2:$D$2,4, YOLG'ON)
=VLOOKUP(B2&" "Buyurtmalar!$A&$2:$D$2;4;FALSE)

Bu erda B va C ustunlari mos ravishda mijozlar nomlari va mahsulot nomlarini va havolani o'z ichiga oladi Buyurtmalar!$A&$2:$D$2 boshqa ish varag'ida qidirish uchun jadvalni belgilaydi.

Formulani o'qish uchun qulayroq qilish uchun siz ko'rayotgan diapazonga nom berishingiz mumkin, bu formulani ancha soddalashtiradi:

VLOOKUP(B2&" "&C2, Buyurtmalar, 4, FALSE)
=VLOOKUP(B2&" "Buyurtmalar;4;FALSE)

Formulaning ishlashi uchun siz ko'rayotgan jadvalning eng chap ustunidagi qiymatlar qidiruv mezonlari bilan bir xil tarzda birlashtirilishi kerak. Yuqoridagi rasmda biz qiymatlarni birlashtirdik va ular orasiga bo'sh joy qo'ydik, funktsiyaning birinchi argumentida ham xuddi shunday qilish kerak (B2&" "&C2").

Eslab qoling! Funktsiya VLOOKUP 255 belgi bilan cheklangan, u 255 belgidan ortiq qiymatni qidira olmaydi. Buni yodda tuting va siz izlayotgan qiymatning uzunligi ushbu chegaradan oshmasligiga ishonch hosil qiling.

Men yordamchi ustunni qo'shish eng oqlangan va har doim ham maqbul echim emasligiga qo'shilaman. Yordamchi ustunsiz ham xuddi shunday qilishingiz mumkin, lekin bu funksiyalar kombinatsiyasi bilan ancha murakkab formulani talab qiladi INDEKS(INDEX) va MATCH(MATCH).

Biz 2, 3 va hokazolarni chiqaramiz. VLOOKUP yordamida qiymatlar

Siz buni allaqachon bilasiz VLOOKUP faqat bitta mos qiymatni, aniqrog'i topilgan birinchisini qaytarishi mumkin. Ammo bu qiymat siz ko'rayotgan massivda bir necha marta takrorlansa va siz ulardan 2 yoki 3-chi qismini chiqarmoqchi bo'lsangiz-chi? Agar barcha qiymatlar bo'lsa-chi? Muammo murakkab ko'rinadi, ammo yechim bor!

Faraz qilaylik, jadvalning bir ustunida mijozlarning nomlari (mijoz nomi), ikkinchisida esa ular sotib olgan mahsulotlar (Mahsulot) mavjud. Keling, ma'lum bir mijoz tomonidan sotib olingan 2, 3 va 4-mahsulotlarni topishga harakat qilaylik.

Eng oddiy usul - ustundan oldin yordamchi ustun qo'shish Mijoz nomi va har bir ismning takroriy soni bilan mijozlar nomlari bilan to'ldiring, masalan. Jon Doe1, Jon Doe2 va hokazo. Funktsiyadan foydalanib, raqamlash bilan hiyla qilaylik COUNTIF(COUNTIF), mijoz nomlari B ustunida joylashganligi sababli:

B2&COUNTIF($B$2:B2,B2)
=B2&COUNTIF($B$2:B2,B2)

Shundan so'ng siz oddiy funktsiyadan foydalanishingiz mumkin VLOOKUP kerakli tartibni topish uchun. Masalan:

• topamiz 2 Den Braun:

  VLOOKUP("Dan Braun2",$A$2:$C$16,3, YOLG'ON)
  =VLOOKUP("Dan Braun2",$A$2:$C$16,3, YOLG'ON)

• topamiz 3 xaridor tomonidan buyurtma qilingan tovarlar Den Braun:

  VLOOKUP("Dan Braun3",$A$2:$C$16,3, YOLG'ON)
  =VLOOKUP("Dan Braun3",$A$2:$C$16,3, YOLG'ON)

Agar siz faqat qidirsangiz 2 takrorlash uchun, murakkabroq formulani yaratish orqali buni yordamchi ustunsiz qilishingiz mumkin:

XATO(VLOOKUP($F$2,BILOSHIY("$B$"&(MATCH($F$2,Jadval4,0)+2)&":$C16"),2,YOLG'ON),"")
=XATO(VLOOKUP($F$2,BEVOILOVIY("$B$"&(MATCH($F$2,Jadval4,0)+2)&":$C16"),2,ROQIQ)"")

Ushbu formulada:

• $F$2- xaridorning nomini o'z ichiga olgan katak (u o'zgarmagan, diqqat qiling - havola mutlaq);
• $B$- ustun Mijoz nomi;
• 4-jadval– Sizning stolingiz (bu joyda oddiy assortiment ham bo'lishi mumkin);
• $C16– jadvalingiz yoki diapazoningizning oxirgi katakchasi.

Bu formula faqat ikkinchi mos qiymatni topadi. Qolgan takrorlashlarni chiqarib olishingiz kerak bo'lsa, oldingi yechimdan foydalaning.

Agar sizga barcha mosliklar ro'yxati kerak bo'lsa, funksiyadan foydalaning VLOOKUP Bu erda hech qanday yordam yo'q, chunki u bir vaqtning o'zida faqat bitta qiymatni qaytaradi - tamom. Lekin Excelda funksiya bor INDEKS(INDEX), bu vazifani osongina engish mumkin. Bunday formula qanday ko'rinishini quyidagi misolda bilib olasiz.

Istalgan qiymatning barcha takrorlanishini olish

Yuqorida aytib o'tilganidek, VLOOKUP ko'rilayotgan diapazondan barcha takroriy qiymatlarni chiqarib bo'lmaydi. Buni amalga oshirish uchun sizga bir nechta Excel funktsiyalaridan tashkil topgan biroz murakkabroq formula kerak bo'ladi, masalan INDEKS(INDEKS), KICHIK(KICHIK) va QAT(LINE)

Masalan, quyidagi formula F2 katakchadagi qiymatning barcha takrorlanishini B2:B16 oralig'ida topadi va C ustunidagi bir xil qatorlar natijasini qaytaradi.

(= AXO(INDEX($C$2:$C$16,KICHIK(AGAR($F$2=B2:B16, QAT(C2:C16)-1,""), QAT()-3)),"") )
(= AXO(INDEX($C$2:$C$16,KICHIK(AGAR($F$2=B2:B16, QAT(C2:C16)-1;""); QAT()-3));"") )

Ushbu massiv formulasini bir nechta qo'shni hujayralarga kiriting, masalan. F4: F8, quyidagi rasmda ko'rsatilganidek. Hujayralar soni qidiruv qiymatining maksimal mumkin bo'lgan takroriy soniga teng yoki undan ko'p bo'lishi kerak. Bosishni unutmang Ctrl+Shift+Enter massiv formulasini to'g'ri kiritish uchun.

Agar siz uning qanday ishlashini tushunmoqchi bo'lsangiz, keling, formulaning tafsilotlariga biroz chuqurroq kiraylik:

1-qism:

AGAR($F$2=B2:B16,ROW(C2:C16)-1,"")
AGAR($F$2=B2:B16;ROW(C2:C16)-1;"")

$F$2=B2:B16– F2 katakchadagi qiymatni B2:B16 diapazonidagi har bir qiymat bilan solishtiring. Agar moslik topilsa, u holda ifoda QAT(C2:C16)-1 tegishli qatorning raqamini qaytaradi (qiymat -1 sarlavha chizig'ini o'tkazib yuborishga imkon beradi). Hech qanday moslik bo'lmasa, funksiya AGAR(IF) bo'sh qatorni qaytaradi.

Funktsiyaning natijasi AGAR(Agar) shunday gorizontal massiv bo'ladi: (1,"",3,"",5,"","",,"",,"",,"",,"",12," ","",,"")

2-qism:

ROW() -3
ROW() -3

Mana funktsiya QAT(ROW) qo'shimcha hisoblagich vazifasini bajaradi. Formula F4:F9 yacheykalarga ko'chirilganligi sababli, sonni ayirib tashlaymiz 3 qiymatni olish uchun funktsiya natijasidan 1 hujayrada F4(4-qator, ayirish 3) olish uchun 2 hujayrada F5(5-qator, ayirish 3) va hokazo.

3-qism:

KICHIK(AGAR($F$2=B2:B16, QAT(C2:C16)-1,""), QAT()-3))
KICHIK(AGAR($F$2=B2:B16, QAT(C2:C16)-1,""), QAT()-3))

Funktsiya KICHIK(KICHIK) qaytaradi nth ma'lumotlar massividagi eng kichik qiymat. Bizning holatda, qaysi pozitsiyani (eng kichikdan) qaytarish funktsiya bilan belgilanadi QAT(LINE) (2-qismga qarang). Shunday qilib, hujayra uchun F4 funktsiyasi KICHIK((massiv);1) qaytadi 1-chi massivning (eng kichik) elementi, ya'ni 1 . Hujayra uchun F5 qaytadi 2 massivning eng kichik elementi, ya'ni 3 , va hokazo.

4-qism:

INDEX($C$2:$C$16,KICHIK(AGAR($F$2=B2:B16, QAT(C2:C16)-1,""), QAT()-3))
INDEX($C$2:$C$16,KICHIK(AGAR($F$2=B2:B16, QAT(C2:C16)-1;""), QAT()-3))

Funktsiya INDEKS(INDEX) oddiygina massivdagi ma'lum bir katakning qiymatini qaytaradi C2: C16. Hujayra uchun F4 funktsiyasi INDEX($C$2:$C$16,1) qaytadi Olmalar, Uchun F5 funktsiyasi INDEX($C$2:$C$16,3) qaytadi Shirinliklar va hokazo.

5-qism:

FAROQ()
FAROQ()

Nihoyat, formulani funksiya ichiga joylashtiramiz XATO(IFERROR), chunki siz xato xabari bilan xursand bo'lishingiz dargumon #Yo'q(#N/A) agar formula koʻchiriladigan katakchalar soni koʻrilgan diapazondagi takroriy qiymatlar sonidan kam boʻlsa.

Ma'lum qator va ustunlar bo'yicha ikki o'lchovli qidiruv

Excelda ikki o'lchovli qidiruvni amalga oshirish ma'lum satr va ustun raqamlari yordamida qiymatni qidirishni o'z ichiga oladi. Boshqacha qilib aytganda, siz ma'lum bir satr va ustunning kesishmasida hujayra qiymatini olasiz.

Shunday qilib, jadvalimizga o'tamiz va formulani funktsiya bilan yozamiz VLOOKUP, unda mart oyida sotilgan limonlarning narxi haqida ma'lumot topiladi.

Ikki o'lchovli qidiruvni amalga oshirishning bir necha usullari mavjud. Mavjud variantlar bilan tanishib chiqing va sizga eng mosini tanlang.

VLOOKUP va MATCH funksiyalari

Siz bir qator funktsiyalardan foydalanishingiz mumkin VLOOKUP(VLOOKUP) va QIDIRMOQ(MATCH) maydonlar kesishmasidagi qiymatni topish uchun Mahsulot nomi(string) va Oy Ko'rib chiqilayotgan massivning (ustun):

VLOOKUP("Limonlar",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0), YOLG'ON)
=VLOOKUP("Limonlar",$A$2:$I$9, MATCH("Mar",$A$1:$I$1,0), YOLG'ON)

Yuqoridagi formula oddiy funktsiyadir VLOOKUP, bu A2 dan A9 gacha bo'lgan kataklardagi "Limonlar" qiymatining aniq mosligini qidiradi. Ammo siz mart oyidagi sotuvlar qaysi ustunda ekanligini bilmasligingiz sababli, funksiyaning uchinchi argumenti uchun ustun raqamini ko‘rsata olmaysiz. VLOOKUP. Buning o'rniga funktsiya ishlatiladi QIDIRMOQ Ushbu ustunni aniqlash uchun.

MATCH("Mar",$A$1:$I$1,0)
MATCH("Mar",$A$1:$I$1,0)

Inson tiliga tarjima qilingan ushbu formula quyidagilarni anglatadi:

• Biz "Mar" belgilarini qidirmoqdamiz - argument qidiruv_qiymati(qidiruv_qiymati);
• Argument uchun A1 dan I1 gacha bo'lgan kataklarni qidiramiz qidirish_massivi(ko'rish_massivi);
• Aniq moslikni qaytarish - argument moslik_turi(moslik_turi).

Foydalanish 0 uchinchi argumentda siz funktsiyani aytasiz QIDIRMOQ qidiruv qiymatiga to'liq mos keladigan birinchi qiymatni qidiring. Bu qiymatga teng FALSE(FALSE) to'rtinchi dalil uchun VLOOKUP.

Shunday qilib, Excelda ikki tomonlama qidiruv formulasini yaratishingiz mumkin, bu ikki o'lchovli qidiruv yoki ikki tomonlama qidiruv sifatida ham tanilgan.

SUMPRODUCT funktsiyasi

Funktsiya SUMPRODUCT(SUMPRODUCT) tanlangan massivlar mahsuloti yig‘indisini qaytaradi:

SUMPRODUCT(($A$2:$A$9="Limonlar")*($A$1:$I$1="Mar"),$A$2:$I$9)
=SUMPRODUCT(($A$2:$A$9="Limonlar")*($A$1:$I$1="Mar");$A$2:$I$9)

INDEX va MATCH funksiyalari

Men ushbu funktsiyalarni keyingi maqolada batafsil tushuntiraman, shuning uchun hozircha ushbu formuladan nusxa ko'chirishingiz mumkin:

INDEX($A$2:$I$9,MATCH("Limonlar",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=INDEX($A$2:$I$9,MATCH("Limonlar",$A$2:$A$9,0), MATCH("Mar",$A$1:$I$1,0))

Nomlangan diapazonlar va kesishish operatori

Agar siz ushbu murakkab Excel formulalari haqida aqldan ozmasangiz, sizga ushbu vizual va esda qolarli usul yoqadi:

Ismni kiritganingizda, Microsoft Excel xuddi formulani kiritganingizda bo'lgani kabi, mos keladigan nomlar ro'yxati bilan maslahatchini ko'rsatadi.

1. bosing Kirish va natijani tekshiring

Umuman olganda, yuqoridagi usullardan qaysi birini tanlasangiz, ikki o'lchovli qidiruv natijasi bir xil bo'ladi:

Biz bitta formulada bir nechta VLOOKUPSdan foydalanamiz

Shunday bo'ladiki, asosiy jadval va qidirish jadvali umumiy bitta ustunga ega emas va bu odatiy funktsiyadan foydalanishga to'sqinlik qiladi. VLOOKUP. Biroq, bizni qiziqtirgan ma'lumotni o'z ichiga olmaydi, lekin asosiy jadval va qidirish jadvali bilan umumiy ustunga ega bo'lgan yana bir jadval mavjud.

Keling, quyidagi misolni ko'rib chiqaylik. Bizda ustunli asosiy jadval mavjud SKU (yangi), bu erda boshqa jadvaldan tegishli narxlar bilan ustun qo'shishingiz kerak. Bundan tashqari, bizda 2 ta qidirish jadvali mavjud. Birinchisi (1-jadvalni qidirish) yangilangan raqamlarni o'z ichiga oladi SKU (yangi) va mahsulot nomlari, ikkinchisi (2-jadvalni qidirish) - mahsulot nomlari va eski raqamlar SKU (eski).

Ikkinchi qidirish jadvalidagi narxlarni asosiy jadvalga qo'shish uchun siz double deb nomlanuvchi amalni bajarishingiz kerak VLOOKUP yoki o'rnatilgan VLOOKUP.

1. Funktsiyani yozing VLOOKUP, bu jadvaldagi mahsulot nomini topadi Qidiruv jadvali 1 foydalanish SKU, kerakli qiymat sifatida:

   VLOOKUP(A2,Yangi_SKU,2,FALSE)
   =VLOOKUP(A2,New_SKU,2,FALSE)

   Bu yerga Yangi_SKU- nomli diapazon $A:$B jadvalda Qidiruv jadvali 1, A 2 - bu mahsulotlarning nomlarini o'z ichiga olgan B ustuni (yuqoridagi rasmga qarang)

2. Jadvaldan narxlarni kiritish uchun formulani yozing Qidiruv jadvali 2 taniqli mahsulot nomlari asosida. Buning uchun avval yaratgan formulani yangi funksiya uchun qidiruv qiymati sifatida kiriting VLOOKUP:

   VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Narx,3,FALSE)
   =VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Narx,3,FALSE)

   Bu yerga Narxi- nomli diapazon $A:$C jadvalda Qidiruv jadvali 2, A 3 narxlarni o'z ichiga olgan C ustunidir.

Quyidagi rasmda biz yaratgan formula bo'yicha qaytarilgan natija ko'rsatilgan:

VLOOKUP va INDIRECT yordamida turli jadvallardagi ma'lumotlarni dinamik almashtirish

Birinchidan, bir-birimizni to'g'ri tushunishimizga ishonch hosil qilish uchun "Turli jadvallardagi ma'lumotlarni dinamik almashtirish" iborasi bilan nimani nazarda tutayotganimizni aniqlab olaylik.

Xuddi shu formatdagi ma'lumotlarga ega bir nechta varaqlar mavjud bo'lgan holatlar mavjud va ma'lum bir varaqdan kerakli ma'lumotlarni ma'lum bir katakka kiritilgan qiymatga qarab ajratib olish kerak bo'ladi. Menimcha, buni misol bilan tushuntirish osonroq.

Tasavvur qiling-a, bir xil mahsulot va bir xil formatda bir nechta mintaqalar uchun savdo hisobotlari mavjud. Muayyan mintaqa uchun savdo ko'rsatkichlarini topishingiz kerak:

Agar sizda faqat ikkita bunday hisobot bo'lsa, unda siz funktsiyalari bilan juda oddiy formuladan foydalanishingiz mumkin VLOOKUP Va AGAR(IF) qidiruv uchun kerakli hisobotni tanlash uchun:

VLOOKUP($D$2,AGAR($D3="FL",FL_Sales,CA_Sales),2,YOLG'ON)
=VLOOKUP($D$2,AGAR($D3="FL",FL_Sales,CA_Sales),2,YOLG'ON)

• $D$2- bu mahsulot nomini o'z ichiga olgan katak. Iltimos, formulani boshqa kataklarga nusxalashda qidirish qiymatini o'zgartirmaslik uchun bu erda mutlaq havolalardan foydalanayotganimizni unutmang.
• $D3– bu hudud nomi yozilgan katak. Biz ustun uchun mutlaq havoladan va satr uchun nisbiy havoladan foydalanamiz, chunki biz formulani xuddi shu ustunning boshqa kataklariga nusxalashni rejalashtirmoqdamiz.
• FL_Sales Va CA_Sales- tegishli savdo hisobotlarini o'z ichiga olgan jadvallar (yoki nomli diapazonlar) nomlari. Siz, albatta, oddiy varaq nomlari va hujayra diapazoni havolalaridan foydalanishingiz mumkin, masalan. ‘FL Sheet’!$A$3:$B$10, lekin nomli diapazonlar ancha qulayroq.

Biroq, bunday jadvallar ko'p bo'lganda, funktsiya AGAR- bu eng yaxshi yechim emas. Buning o'rniga siz funktsiyadan foydalanishingiz mumkin BILVOSIT(INDIRECT) kerakli qidiruv diapazonini qaytarish uchun.

Ehtimol siz bilganingizdek, funktsiya BILVOSIT matn qatori berilgan havolani qaytarish uchun ishlatiladi, bu bizga hozir kerak bo'lgan narsadir. Shunday qilib, ifodani yuqoridagi formuladagi funktsiya bilan almashtiring AGAR funksiyasi bilan havolaga BILVOSIT. Bu kombinatsiya VLOOKUP Va BILVOSIT juftlikda ajoyib ishlaydi:

VLOOKUP($D$2,BEVOLIY($D3&"_Sotish"),2,YOLG'ON)
=VLOOKUP($D$2,BEVOID($D3&"_Sotish"),2,YOLG'ON)

• $D$2- bu mahsulot nomiga ega katak, u mutlaq havola tufayli o'zgarmaydi.
• $D3- bu hudud nomining birinchi qismini o'z ichiga olgan katakdir. Bizning misolimizda bu FL.
• _Sotish- barcha nomlangan diapazonlar yoki jadvallar nomining umumiy qismi. D3 katakchadagi qiymat bilan birlashtirilib, kerakli diapazonning to'liq nomini hosil qiladi. Quyida funksiya bilan tajribaga ega bo'lmaganlar uchun ba'zi tafsilotlar mavjud BILVOSIT.

INDIRECT va VLOOKUP qanday ishlaydi

Avvalo, sizga funksiya sintaksisini eslatib o'taman BILVOSIT(bilvosita):

• A1, agar argument teng bo'lsa TO'G'RI(TRUE) yoki ko'rsatilmagan;
• R1C1, Agar FALSE(YOLG'ON).

Keling, savdo hisobotimizga qaytaylik. Esingizda bo'lsa, har bir hisobot alohida varaqda joylashgan alohida jadvaldir. Formulaning to'g'ri ishlashi uchun siz jadvallar (yoki diapazonlar) ga nom berishingiz kerak va barcha nomlar umumiy qismga ega bo'lishi kerak. Masalan, bu kabi: CA_Sales, FL_Sotish, TX_Sotish va hokazo. Ko'rib turganingizdek, barcha nomlarda "_Sales" mavjud.

Funktsiya BILVOSIT D ustunidagi qiymatni va "_Sotish" matn qatorini bog'laydi, shu bilan hisobot beradi VLOOKUP qaysi jadvalga qarash kerak. Agar "FL" qiymati D3 katakda bo'lsa, formula jadvalni qidiradi FL_Sotish, agar "CA" jadvalda bo'lsa CA_Sales va hokazo.

Funktsiyalarning natijasi VLOOKUP Va BILVOSIT quyidagilar bo'ladi:

Agar ma'lumotlar turli Excel ish kitoblarida joylashgan bo'lsa, unda siz ish kitobi nomini nomlangan diapazondan oldin qo'shishingiz kerak, masalan:

VLOOKUP($D$2,BEVOLIY($D3&"Ishchi kitob1!_Sotish"),2,YOLG'ON)
=VLOOKUP($D$2,BEVOLIY($D3&"Ish kitobi1!_Sotish"),2,YOLG'ON)

Agar funktsiya BILVOSIT boshqa ish kitobiga ishora qilsa, u holda bu ish kitobi ochiq bo'lishi kerak. Agar u yopilsa, funksiya xato haqida xabar beradi #REF!(#LINK!).

KPI - bu bajarilgan harakatlar samaradorligini ob'ektiv baholash imkonini beruvchi samaradorlik ko'rsatkichi. Ushbu tizim turli ko'rsatkichlarni (butun kompaniya faoliyati, alohida tuzilmalar, aniq mutaxassislar) baholash uchun ishlatiladi. U nafaqat nazorat funktsiyalarini bajaradi, balki mehnat faoliyatini ham rag'batlantiradi. Ko'pincha ish haqi tizimi KPI asosida quriladi. Bu ish haqining o'zgaruvchan qismini shakllantirish usuli.

KPI asosiy ishlash ko'rsatkichlari: Excelda misollar

KPI rag'batlantirish tizimidagi rag'batlantiruvchi omil pul mukofotidir. Uni o'ziga yuklangan vazifani bajargan xodim olishi mumkin. Bonus / bonus miqdori ma'lum bir xodimning hisobot davridagi faoliyatiga bog'liq. Ish haqi miqdori qat'iy belgilanishi yoki ish haqining foizi sifatida ifodalanishi mumkin.

Har bir korxona asosiy ishlash ko'rsatkichlarini va har birining vaznini alohida belgilaydi. Ma'lumotlar kompaniyaning maqsadlariga bog'liq. Masalan:

1. Maqsad - oyiga 500 000 rubl mahsulot sotish rejasiga erishish. Asosiy ko'rsatkich - bu savdo rejasi. O'lchov tizimi: haqiqiy sotish miqdori / rejalashtirilgan savdo miqdori.
2. Maqsad - davrda yuk tashish hajmini 20% ga oshirish. Asosiy ko'rsatkich - o'rtacha jo'natish miqdori. O'lchov tizimi: haqiqiy o'rtacha jo'natish / rejalashtirilgan o'rtacha jo'natish.
3. Maqsad - ma'lum bir mintaqada mijozlar sonini 15% ga oshirish. Asosiy ko'rsatkich - bu korxona ma'lumotlar bazasidagi mijozlar soni. O'lchov tizimi: mijozlarning haqiqiy soni / rejalashtirilgan mijozlar soni.

Korxona koeffitsientning (vaznlarning) tarqalishini ham mustaqil ravishda belgilaydi. Masalan:

1. Rejaning 80% dan kam bajarilishi qabul qilinishi mumkin emas.
2. Rejaning bajarilishi 100% - koeffitsient 0,45.
3. Rejani 100-115% bajarish - har 5% uchun 0,005 koeffitsient.
4. Xatolar yo'q - koeffitsient 0,15.
5. Hisobot davrida hech qanday izoh yo'q - koeffitsient 0,15.

Bu faqat motivatsion koeffitsientlarni aniqlashning mumkin bo'lgan variantidir.

KPIni o'lchashda asosiy nuqta - bu haqiqiy ko'rsatkichning rejalashtirilgan ko'rsatkichga nisbati. Deyarli har doim xodimning ish haqi ish haqi (qat'iy qism) va bonusdan (o'zgaruvchan / o'zgaruvchan qism) iborat. Motivatsiya koeffitsienti o'zgaruvchining shakllanishiga ta'sir qiladi.

Ish haqidagi doimiy va o'zgaruvchan qismlarning nisbati 50 × 50 deb faraz qilaylik. Asosiy ko'rsatkichlar va ularning har birining og'irligi:

Keling, quyidagi koeffitsient qiymatlarini qabul qilaylik (1-ko'rsatkich va 2-ko'rsatkich uchun bir xil):

Excelda KPI jadvali:

Tushuntirishlar:

Bu Exceldagi namunaviy KPI jadvalidir. Har bir korxona o'zini (ishning xususiyatlarini va bonus tizimini hisobga olgan holda) tashkil qiladi.

KPI matritsasi va Excelda misol

Xodimlarni asosiy ko'rsatkichlar bo'yicha baholash uchun matritsa yoki maqsadlar bo'yicha kelishuv tuziladi. Umumiy shakl quyidagicha ko'rinadi:

1. Asosiy ko'rsatkichlar - bu xodimlarning ishi baholanadigan mezonlar. Ular har bir pozitsiya uchun farq qiladi.
2. Og'irliklar 0 dan 1 gacha bo'lgan raqamlar bo'lib, ularning umumiy yig'indisi 1. Ular kompaniyaning maqsadlarini hisobga olgan holda har bir asosiy ko'rsatkichning ustuvorliklarini aks ettiradi.
3. Baza - indikatorning qabul qilinadigan minimal qiymati. Asosiy darajadan past - natija yo'q.
4. Norm - rejalashtirilgan daraja. Xodim bajarishi kerak bo'lgan narsa. Quyida - xodim o'z vazifalarini bajara olmadi.
5. Maqsad - bu intilish kerak bo'lgan qiymat. Natijalarni yaxshilash imkonini beruvchi standartdan yuqori ko'rsatkich.
6. Fakt - ishning haqiqiy natijalari.
7. KPI indeksi normaga nisbatan natijalar darajasini ko'rsatadi.

Kpi ni hisoblash formulasi:

KPI indeksi = ((Haqiqiy - Baza) / (Norma - Baza)) * 100%.

Ofis menejeri uchun matritsani to'ldirishga misol:

Ishlash koeffitsienti indekslar va og'irliklar mahsulotining yig'indisidir. Xodimlarning ish faoliyatini baholash shartli formatlash yordamida aniq ko'rsatilgan.

Aytaylik, siz ma'lum bir mintaqadagi mahsulot sotuvi yig'indisi kabi bir nechta shartli qiymatlarni yig'moqchisiz. Bu formulada SUMIFS funksiyasidan yaxshi foydalanish.

Ushbu misolga e'tibor bering, bizda ikkita shart mavjud: talab qilinadigan savdo miqdori go'sht(C ustunidan) gacha janubiy maydonlar (a ustunidan).

Buni amalga oshirish uchun ishlatilishi mumkin bo'lgan formula:

= SUMIFS (D2: D11; a2: A11; "Janubiy"; C2: C11; "go'sht")

Natijada 14,719 qiymati.

Keling, formulaning har bir qismining batafsil tasvirini ko'rib chiqaylik.

SUMIFS arifmetik formuladir. Bu holda D ustunida joylashgan raqamlarni hisoblaydi. Birinchi qadam raqamlarning joylashishini belgilashdir.

=SUMIFS(D2:D11,

Boshqacha qilib aytganda, agar ular ma'lum shartlarga javob bersa, formuladan ushbu ustundagi raqamlarni yig'ishini xohlaysiz. Bu hujayra diapazoni birinchi dalil ushbu formulada funktsiya kirish sifatida foydalanadigan birinchi ma'lumotlar elementi.

Keyin siz ikkita shartga javob beradigan ma'lumotlarni topishingiz va birinchi shartni kiritishingiz kerak, bunda funktsiya uchun ma'lumotlarning joylashishini (a2: A11), shuningdek, shart "Janubiy" ekanligini ko'rsating. Alohida argumentlar orasidagi vergullarga e'tibor bering.

=SUMIFS(D2:D11,A2:A11,"Janubiy";

"Janubiy" so'zi atrofidagi qo'shtirnoq bu matn ma'lumotlari ekanligini ko'rsatadi.

Nihoyat, siz ikkinchi shart uchun argumentlarni kiritasiz - "Go'sht" so'zini o'z ichiga olgan katakchalar diapazoni (C2: C11), shuningdek so'zning o'zi (tirnoqlar ichida) Excel ularga mos kelishi uchun. Qavsni yopish orqali formulani to'ldiring ) ni bosing va Enter tugmasini bosing. Natijada yana 14,719.

=SUMIFS(D2:D11,A2:A11,"Janubiy",C2:C11,"Go'sht")

Excelda SUMIFS funksiyasini yozayotganingizda, agar ushbu argumentlarni eslamasangiz, Yordam tayyor. Formulani kiritgandan so'ng = SUMIFS ( Avtomatik to'ldirish formulalari formula ostida paydo bo'ladi va argumentlar ro'yxati kerakli tartibda ro'yxatga olinadi.

Bizning misolimizdagi formulani avtomatik to'ldirish va argumentlar ro'yxati tasvirini ko'rib chiqing yig'indisi_oraliq Bu D2:D11, yig'indisi kerak bo'lgan raqamlar ustuni. mezon_oraliq1- a2. A11 - o'z ichiga olgan ma'lumotlar ustuni shart 1"Janubiy".

Formulani kiritganingizda, qolgan argumentlar (bu erda ko'rsatilmagan) Formulani avtomatik to'ldirishda paydo bo'ladi; diapazon_shartlari2 o'z ichiga olgan ma'lumotlar ustuni bo'lgan C2:C11 oralig'idir shart 2- "Go'sht".

Agar bossangiz SUMIFS Formulani avtomatik to'ldirganingizda, qo'shimcha ma'lumot olishingiz mumkin bo'lgan maqola ochiladi.

Amalga oshirishga harakat qiling

Agar siz SUMIFS funksiyasi bilan tajriba o'tkazmoqchi bo'lsangiz, bu erda ba'zi namuna ma'lumotlari va funksiyadan foydalanadigan formulalar mavjud.

Ushbu veb uchun Excel ish kitobida siz shu yerda namunali ma'lumotlar va formulalar bilan ishlashingiz mumkin. Darhol natijalar o'zgarishini ko'rish uchun qiymatlar va formulalarni o'zgartiring yoki o'zingiznikini qo'shing.

Quyidagi jadvaldagi barcha kataklardan nusxa oling va ularni yangi Excel varag'ining A1 katakchasiga joylashtiring. Formulalar yaxshiroq ko'rinishi uchun ustunlar kengligini sozlashingiz mumkin.

Ikki yoki undan ortiq mezonlarga asoslangan VPR

VLOOKUP funksiyasi bilan tanish bo'lgan har bir kishi, albatta, u ko'rsatilgan qiymatlarni faqat belgilangan jadvalning chap ustunida qidirishini biladi (VLOOKUP haqida ko'proq maqolada o'qishingiz mumkin: Boshqa jadvaldagi qiymatni qanday topish yoki VLOOKUP kuchi) . Bundan tashqari, ko'p odamlar VLOOKUP faqat bitta qiymat asosida qidirishini bilishadi.

Maqola yordam berdimi? Havolani do'stlaringiz bilan baham ko'ring! Video darslar

("Bottom bar":("textstyle":"statik","textpositionstatic":"pastki","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"lefts","detexteffects :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic:"duration"OutCubic01", ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":6010"texteffect2":6010", textcss":"display:blok; to‘ldirish:12px; matnni tekislash:chap;","textbgcss":"displey:blok; pozitsiya:mutlaq; tepa:0px; chap:0px; kenglik:100%; balandlik:100% ; fon rangi:#333333; shaffoflik:0,6; filtr:alfa(shaffoflik=60);","titlecss":"displey:blok; pozitsiya: nisbiy; shrift:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; rang:#fff;","descriptioncss":"displey:blok; pozitsiya: nisbiy; shrift:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; rang: #fff; margin-top:8px;","buttoncss":"displey:blok; pozitsiya: nisbiy; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive" "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))