Aytaylik, siz uning raqamidan foydalanib, xodimning telefon kengaytmasini topishingiz va savdo miqdori uchun komissiya stavkasini to'g'ri hisoblashingiz kerak. Roʻyxatdagi aniq maʼlumotlarni tez va samarali topish uchun maʼlumotlarni qidirasiz va maʼlumotlardan toʻgʻri foydalanilayotganligini avtomatik tekshirasiz. Ma'lumotlarni ko'rganingizdan so'ng, siz hisob-kitoblarni amalga oshirishingiz va natijalarni qaytarish qiymatlarini ko'rsatish orqali ko'rsatishingiz mumkin. Ma'lumotlar ro'yxatida qiymatlarni qidirish va natijalarni ko'rsatishning bir necha yo'li mavjud.

Ushbu maqolada

Ro'yxatdagi qiymatlarni vertikal ravishda aniq moslik bo'yicha topish

Bu vazifani bajarish uchun VLOOKUP funksiyasidan yoki INDEX va MATCH funksiyalarining kombinatsiyasidan foydalanishingiz mumkin.

VLOOKUP funksiyasidan foydalanishga misollar

VLOOKUP funksiyasi.

INDEXES va MATCHES misollari

Nimani anglatadi:

=INDEX(C2:C10 dan MATCH ga mos keladigan qiymatni qaytarish kerak (B2:B10 massividagi "Karabam" birinchi qiymati))

Formula C2:C10 katakchasidagi birinchi qiymatni qidiradi karam(B7 da) va C7 da qiymatni qaytaradi ( 100 ) ga mos keladigan birinchi qiymat karam.

Qo'shimcha ma'lumot olish uchun Indeks funktsiyasi va MATCH funksiyasiga qarang.

Taxminiy moslik bo'yicha vertikal ravishda ro'yxatdagi qiymatlarni topish

Buning uchun VLOOKUP funksiyasidan foydalaning.

Muhim: Birinchi qatordagi qiymatlar o'sish tartibida tartiblanganligiga ishonch hosil qiling.

Yuqoridagi misolda VLOOKUP funksiyasi A2:B7 oralig'ida 6 ta kechikishi bo'lgan talabaning ismini qidiradi. Jadvalda hech qanday yozuv yo'q 6 kechikadi, shuning uchun VLOOKUP 6 dan past bo'lgan keyingi eng yuqori moslikni qidiradi va birinchi ism bilan bog'langan 5 qiymatini topadi Deyv, va shuning uchun qaytib keladi Deyv.

Qo'shimcha ma'lumot uchun VLOOKUP funksiyasi bo'limiga qarang.

Noma'lum o'lchamlar ro'yxatida aniq mos keladigan vertikal qiymatlarni topish

Bu vazifani bajarish uchun OFFSET va MATCH funksiyalaridan foydalaning.

Eslatma: Ushbu yondashuv ma'lumotlar har kuni yangilanadigan tashqi ma'lumotlar oralig'ida bo'lsa ishlatiladi. Siz B ustunida Narx borligini bilasiz, lekin server necha qator ma'lumotlarni qaytarayotganini bilmaysiz va birinchi ustun alifbo tartibida tartiblanmagan.

C1 diapazonning yuqori chap katakchasi (boshlang'ich katak deb ham ataladi).

Mos ("apelsinlar"; C2: C7; 0) C2: C7 oralig'ida to'q sariq rangni qidiradi. Siz diapazonga boshlang'ich katakchani qo'shmasligingiz kerak.

1 - qaytish qiymati qaytarilishi kerak bo'lgan boshlang'ich katakchaning o'ng tomonidagi ustunlar soni. Bizning misolimizda qaytarish qiymati D ustunida, Sotish.

Ro'yxatdagi qiymatlarni gorizontal ravishda aniq moslik bo'yicha topish

Bu vazifani bajarish uchun HLOOKUP funksiyasidan foydalaniladi. Quyida misol keltirilgan.

HLOOKUP funksiyasi ustun qidiruvini amalga oshiradi Sotish va belgilangan diapazondagi 5-satrdagi qiymatni qaytaradi.

Qo'shimcha ma'lumot olish uchun HLOOKUP funktsiyalari bo'limiga qarang.

Qidiruv ustasi yordamida qidirish formulasini yarating (faqat Excel 2007 uchun)

Eslatma: Qidiruv ustasi plaginlari Excel 2010 da eskirgan. Bu funksiya Funktsiyalar ustasi va mavjud qidirish va mos yozuvlar (mos yozuvlar) funksiyalari bilan almashtirildi.

Excel 2007 da qidirish ustasi satr va ustun sarlavhalariga ega bo'lgan ish varag'i ma'lumotlari asosida qidirish formulasini yaratadi. Qidiruv ustasi bitta ustundagi qiymatni bilsangiz, qatordagi boshqa qiymatlarni topishga yordam beradi va aksincha. Qidiruv ustasi yaratgan formulalarida indeks va MATCH dan foydalanadi.

Salom do'stlar. Qanchalik tez-tez Excel elektron jadvalida ma'lum qiymat uchun moslikni qidirishingiz kerak? Misol uchun, siz ma'lumotnomada shaxsning manzilini yoki narxlar ro'yxatida - tovarlarning narxini topishingiz kerak. Agar shunday vazifalar yuzaga kelsa, bu post siz uchun!

Men har kuni ushbu tartib-qoidalarni bajaraman va quyida tavsiflangan funktsiyalarsiz men haqiqatan ham juda qiyin bo'lardim. E'tibor bering va ularni ishga soling!

Excel elektron jadvallarini qidirish, VLOOKUP va HLOOKUP funksiyalari

Ushbu funktsiyalarning oddiy foydalanuvchi hayotidagi rolini ortiqcha baholash qiyin. Endi siz ma'lumotlar jadvalida mos yozuvni osongina topishingiz va unga mos keladigan qiymatni qaytarishingiz mumkin.

VLOOKUP funksiyasining sintaksisi: =VLOOKUP( qidiruv_qiymati; qidiruv_jadval; chiqish_ustun_raqami; [mos_turi]). Argumentlarni ko'rib chiqing:

• Istalgan qiymat izlanadigan qiymatdir. Bu talab qilinadigan dalil;
• Qidiruv jadvali- qidiruv bo'ladigan hujayralar qatori. Kerakli qiymatlarga ega ustun ushbu massivda birinchi bo'lishi kerak. Bu ham talab qilinadigan dalildir;
• Chiqish ustuni raqami ustunning tartib raqami (massivdagi birinchisidan boshlab), agar kerakli qiymatlar mos kelsa, funktsiya ma'lumotlarni ko'rsatadi. Majburiy argument;
• Xaritalash turi- qat'iy bo'lmagan moslik uchun "1" (yoki "TRUE") ni, to'liq moslik uchun "0" ("YOLG'ON") ni tanlang. Argument ixtiyoriy, agar u qoldirilsa, qidiruv amalga oshiriladi qat'iy bo'lmagan moslik.

VLOOKUP bilan aniq moslikni topish

Keling, VLOOKUP funksiyasi mos keladigan tur FALSE bo'lganda, aniq moslikni qidirganda qanday ishlashiga misolni ko'rib chiqaylik. B5: E10 massivida ma'lum bir kompaniyaning asosiy vositalari, ularning balans qiymati, inventar raqami va joylashuvi mavjud. B2 katakchada jadvaldagi inventar raqamini topishingiz va uni C2 katakka joylashtirishingiz kerak bo'lgan nom mavjud.

Formulani yozamiz: =VLOOKUP(B2;B5:E10;3;FALSE) .

Bu erda birinchi argument jadvalni B2 katakchasidagi qiymatni izlash kerakligini ko'rsatadi, ya'ni. faks so'zi. Ikkinchi dalil, qidiruv jadvalining B5: E10 oralig'ida ekanligini aytadi va birinchi ustunda "Faks" so'zini izlash kerak, ya'ni. B5:B10 massivida. Uchinchi argument dasturga hisoblash natijasi massivning uchinchi ustunida joylashganligini aytadi, ya'ni. D5: D10. To'rtinchi dalil FALSE, ya'ni. to'liq moslik talab qilinadi.

Shunday qilib, funktsiya B2 katagidan "Faks" qatorini oladi va uni B5:B10 massivida yuqoridan pastgacha qidiradi. Moslik topilgach (8-qator), funktsiya D ustunidan mos keladigan qiymatni qaytaradi, ya'ni. mazmuni D8. Aynan shu narsa bizga kerak edi, muammo hal qilindi.

Agar kerakli qiymat topilmasa, funktsiya qaytadi.

VLOOKUP yordamida noaniq moslikni topish

VLOOKUP-dagi ushbu parametr tufayli biz kerakli natijani topish uchun murakkab formulalardan qochishimiz mumkin.

B5:C12 massivida kredit summasiga qarab kreditlar bo‘yicha foiz stavkalari ko‘rsatilgan. B2 katakchasida biz kredit miqdorini ko'rsatamiz va biz C2da bunday operatsiya uchun stavkani olishni xohlaymiz. Vazifa qiyin, chunki miqdor har qanday bo'lishi mumkin va massivda ko'rsatilganlarga mos kelishi dargumon, aniq moslikni qidirish mos kelmaydi:

Keyin biz qat'iy bo'lmagan qidiruv formulasini yozamiz: =VLOOKUP(B2,B5:C12,2,TRUE). Endi, B ustunida keltirilgan barcha ma'lumotlardan dastur eng yaqin kichigini qidiradi. Ya'ni, 8000 miqdori uchun 5000 qiymati tanlanadi va tegishli foiz ko'rsatiladi.

Funktsiya to'g'ri ishlashi uchun jadvalning birinchi ustunini o'sish tartibida tartiblash kerak. Aks holda, bu noto'g'ri natija berishi mumkin.

HLOOKUP funksiyasi VLOOKUP funksiyasi bilan bir xil sintaksisga ega, lekin natijani ustunlar emas, balki satrlarda qidiradi. Ya'ni, u jadvallarni yuqoridan pastga emas, balki chapdan o'ngga qarab skanerlaydi va ustunni emas, balki berilgan qator raqamini ko'rsatadi.

BROWSE funksiyasi yordamida ma'lumotlarni topish

LOOKUP funksiyasi VLOOKUP funksiyasiga o‘xshash ishlaydi, lekin boshqa sintaksisga ega. Ma'lumotlar jadvalida bir necha o'nlab ustunlar mavjud bo'lganda foydalanaman va VLOOKUP-dan foydalanish uchun siz ko'rsatilgan ustun sonini qo'shimcha ravishda hisoblashingiz kerak. Bunday hollarda VIEW funksiyasi vazifani osonlashtiradi. Shunday qilib, sintaksis: =BROZLASH( qidiruv_qiymati; Qidiruv_uchun_massiv; ko'rsatish_massivi) :

• Istalgan qiymat– ma’lumotlar yoki qidiriladigan ma’lumotlarga havola;
• Qidirish uchun massiv- biz shunga o'xshash qiymatni qidirayotgan bitta satr yoki ustun. Bu massiv o'sish tartibida tartiblangan bo'lishi kerak;
• Ko'rsatish uchun massiv- natijalarni ko'rsatish uchun ma'lumotlarni o'z ichiga olgan diapazon. Tabiiyki, u qidiruv massivi bilan bir xil o'lchamda bo'lishi kerak.

Bunday belgi bilan siz natijalar qatoriga nisbatan nisbiy bo'lmagan havola berasiz. Va siz to'g'ridan-to'g'ri unga ishora qilasiz, ya'ni. ko'rsatilgan ustunning sonini oldindan hisoblashning hojati yo'q. Biz VLOOKUP funksiyasi uchun birinchi misolda VIEW funksiyasidan foydalanamiz (asosiy vositalar, inventar raqamlari): =QAZIRISH(B2,B5:B10,D5:D10). Vazifa muvaffaqiyatli hal qilindi!

Nisbiy koordinatalar bo'yicha qidirish. MATCH va INDEX funksiyalari

Ma'lumotlarni qidirishning yana bir usuli - MATCH va INDEX funktsiyalarini birlashtirish.

Ulardan birinchisi massivdagi qiymatni qidirish va uning tartib raqamini olish uchun ishlatiladi: MATCH( qidiruv_qiymati; qidirish_massivi; [Xaritalash turi] ). Funktsiya argumentlari:

• Istalgan qiymat- talab qilinadigan argument
• Qidiruv massivi- biz moslikni qidirayotgan bitta qator yoki ustun. Majburiy argument
• Xaritalash turi- Aniq moslik uchun "0", eng yaqin kichik uchun "1", eng yaqin katta uchun "-1" ni belgilang. Funktsiya ro'yxatning boshidan oxirigacha qidirganligi sababli, eng yaqin kichigini qidirganda, qidiruv ustunini kamayish tartibida tartiblang. Va ko'proq narsani qidirganda - uni o'sish tartibida tartiblang.

Kerakli qiymatning o'rni topildi, endi siz uni funksiyadan foydalanib ekranda ko'rsatishingiz mumkin INDEKS( massiv; qator_raqami; [ustun_raqami]) :

• massiv- argument qaysi yacheykalar massividan qiymat tanlamoqchi ekanligingizni bildiradi
• Qator raqami– ko‘rsatmoqchi bo‘lgan qatorning seriya raqamini (massivning birinchi katagidan boshlab) belgilang. Bu erda siz qiymatni qo'lda yozishingiz yoki boshqa funktsiyani hisoblash natijasidan foydalanishingiz mumkin. Masalan, SEARCH.
• Ustun raqami– ixtiyoriy argument, agar massiv bir nechta ustunlardan iborat bo'lsa, ko'rsatiladi. Agar argument o'tkazib yuborilsa, formula jadvalning birinchi ustunidan foydalanadi.

Endi natijaga erishish uchun ushbu funktsiyalarni birlashtiramiz:

Bu Excelda ma'lumotlarni qidirish va ko'rsatish usullari. Bundan tashqari, siz ulardan hisob-kitoblarda foydalanishingiz, taqdimotda foydalanishingiz, ular bilan operatsiyalarni bajarishingiz, ularni boshqa funktsiyalarga argument sifatida ko'rsatishingiz mumkin va hokazo.

Bilim va ko'nikmalaringiz qanday o'sib, mustahkamlanib borayotganini his qilyapsizmi? Keyin to'xtamang, o'qishda davom eting! Keyingi postda biz ko'rib chiqamiz: bu qiyin va qiziqarli bo'ladi!

Bizda turli oylarda ma'lum tovarlarni sotish hajmlari qayd etilgan jadval mavjud. Jadvalda ma'lumotlarni topish kerak va qidirish mezonlari qator va ustun sarlavhalari bo'ladi. Ammo qidiruv satr yoki ustun oralig'ida alohida bajarilishi kerak. Ya'ni, mezonlardan faqat bittasi qo'llaniladi. Shuning uchun bu erda INDEX funktsiyasidan foydalanib bo'lmaydi, lekin maxsus formula kerak.

Excel jadvalida qiymatlarni topish

Ushbu muammoni hal qilish uchun biz yuqorida tavsiflangan shartlarga javob beradigan sxematik jadvalga misol keltiramiz.

Vertikal va gorizontal qiymatlarni qidirish uchun jadvalli varaq:

Jadvalning o'zi tepasida natijalar bilan chiziq mavjud. B1 katakka qidiruv so'rovi mezonini, ya'ni ustun sarlavhasini yoki satr nomini kiritamiz. Va D1 katakchada qidiruv formulasi mos keladigan qiymatni hisoblash natijasini qaytarishi kerak. Shundan so'ng, ikkinchi formula F1 katakchasida ishlaydi, u allaqachon tegishli oyni topish mezoni sifatida B1 va D1 katakchalarining qiymatlaridan foydalanadi.

Excel qatorida qiymatni topish

Endi biz 4 ta eng ko'p miqdorda va qaysi oyda maksimal sotilganligini bilib olamiz.

Ustunlar bo'yicha qidirish uchun:

Ikki chorak davomida 4-bandning eng katta sotuvi qaysi oyda va nima bo'lganligi aniqlandi.

Excel qatorida qiymatni topish formulasining ishlash printsipi:

VLOOKUP (Vertikal qidirish) funksiyasining birinchi argumenti qidiruv mezoni joylashgan katakka havoladir. Ikkinchi argument qidiruv paytida qidiriladigan hujayralar oralig'ini belgilaydi. VLOOKUP funksiyasining uchinchi argumentida qiymat mahsulot 4 nomli qatorga nisbatan olinishi kerak bo‘lgan ustun raqami bo‘lishi kerak. Lekin biz bu raqamni oldindan bilmaganimiz uchun biz ustunlar massivini yaratish uchun COLUMN funksiyasidan foydalanamiz. B4: G15 diapazoni uchun raqamlar.

Bu VLOOKUP funksiyasiga qiymatlarning butun majmuasini to'plash imkonini beradi. Natijada, xotira 4-banddagi har bir ustun uchun barcha mos qiymatlarni saqlaydi (masalan: 360; 958; 201; 605; 462; 832). Shundan so'ng, MAX funktsiyasi faqat ushbu massivdan maksimal raqamni olishi va formulani hisoblash natijasida uni D1 katak uchun qiymat sifatida qaytarishi kerak.

Ko'rib turganingizdek, formulaning dizayni sodda va qisqa. Unga asoslanib, shunga o'xshash tarzda ma'lum bir mahsulot uchun boshqa ko'rsatkichlarni topishingiz mumkin. Masalan, buning uchun MIN yoki AVERAGE funksiyalaridan foydalangan holda sotuvlarning minimal yoki o'rtacha qiymati. Savdo hisobotining eng qulay tahlilini amalga oshirish uchun murakkabroq funktsiyalardan foydalangan holda ushbu skelet formulasini qo'llashga hech narsa to'sqinlik qilmaydi.

Bitta katak boshidan ustun sarlavhalarini qanday olish mumkin?

Misol uchun, biz ikkinchi formuladan foydalangan holda maksimal sotuv bo'lgan oyni qanchalik samarali ko'rsatdik. Ikkinchi formulada biz MAX funksiyasisiz birinchi formulaning skeletini ishlatganimizni payqash qiyin emas. Formulaning asosiy tuzilishi: VLOOKUP(B1;A5:G14;COLUMN(B5:G14);0). Biz MAX funktsiyasini MATCH bilan almashtirdik, bu birinchi argumentda oldingi formula bo'yicha olingan qiymatdan foydalanadi. Endi u oyni topish uchun mezon sifatida ishlaydi. Va natijada MATCH funksiyasi bizga 4-mahsulot uchun sotish hajmining maksimal qiymati joylashgan 2-ustun sonini qaytaradi.Bundan so'ng INDEX funksiyasi ishga kiritilgan bo'lib, u qiymatni soni bo'yicha qaytaradi. uning argumentlarida belgilangan diapazondan davr va ustun. Bizda ustun raqami 2 bo'lgani uchun va oylar nomlari saqlanadigan diapazondagi satr raqami har qanday holatda 1 bo'ladi. Keyin B4 diapazonidan tegishli qiymatni olish uchun INDEX funktsiyasidan foydalanishimiz qoladi. : G4 - fevral (ikkinchi oy).

Excel ustunida qiymatni topish

Vazifaning ikkinchi varianti mezon sifatida oy nomidan foydalangan holda jadval qidiruvi bo'ladi. Bunday hollarda biz formulamizning skeletini o'zgartirishimiz kerak: VLOOKUP funksiyasi GLOOKUP bilan, COLUMN funksiyasi esa ROW bilan almashtiriladi.

Bu bizga ma'lum bir oyda qancha va qaysi mahsulot eng ko'p sotilganligini aniqlash imkonini beradi.

Muayyan oyda qaysi mahsulot eng ko'p sotilganligini aniqlash uchun:

Excel ustunida qiymatni topish formulasining printsipi:

HLOOKUP (Gorizontal View) funksiyasining birinchi argumentida biz qidiruv mezoniga ega bo'lgan katakka havolani belgilaymiz. Ikkinchi argument ko'rilayotgan jadval diapazoniga havolani o'z ichiga oladi. Uchinchi argument STRING funksiyasi tomonidan ishlab chiqariladi, bu esa xotirada 10 elementli qator raqamlari massivini yaratadi. Jadval qismida bizda 10 ta qator bor.

Keyinchalik, HLOOKUP funktsiyasi har bir qator raqamidan foydalanib, ma'lum bir oy (iyun) uchun jadvaldan mos keladigan savdo qiymatlari qatorini yaratadi. Keyinchalik, MAX funktsiyasi faqat ushbu massivdan maksimal qiymatni tanlashi kerak.

DIQQAT! Formula skeletini boshqa vazifalar uchun ishlatganda, har doim HLOOKUP qidiruv funktsiyasining ikkinchi va uchinchi argumentlariga e'tibor bering. Argumentda ko'rsatilgan diapazondagi qoplangan qatorlar soni jadvaldagi qatorlar soniga mos kelishi kerak. Va shuningdek, raqamlash ikkinchi qatordan boshlanishi kerak!

Aytaylik, sizning hisobotingizda ko'plab ustunlarda juda ko'p ma'lumotlar mavjud jadval mavjud. Bunday jadvallarni vizual tahlil qilish juda qiyin. Hisobot bilan ishlash vazifalaridan biri bu ma'lum bir oyga tegishli satrlar va ustunlar sarlavhalari bo'yicha ma'lumotlarni tahlil qilishdir. Bir qarashda, bu juda oddiy vazifa, ammo uni bitta standart funktsiya yordamida hal qilib bo'lmaydi. Ha, albatta siz ushbu vositadan foydalanishingiz mumkin: "HOME" - "Tahrirlash" - "Topish" CTRL + F Excel varag'idagi qiymatlarni qidirish oynasini chaqirish uchun. Yoki jadval uchun shartli formatlash qoidasini yarating. Ammo keyin olingan natijalar bilan keyingi hisob-kitoblarni amalga oshirish mumkin bo'lmaydi. Shuning uchun tegishli formulani yaratish va to'g'ri qo'llash kerak.

Excel massivida qiymatni topish

Muammoni hal qilish sxemasi quyidagicha ko'rinadi:

• B1 katakka bizni qiziqtirgan ma'lumotlarni kiritamiz;
• B2 katak B1 katak qiymatini o'z ichiga olgan ustun sarlavhasini ko'rsatadi
• B3 katakchasi B1 katak qiymatini o'z ichiga olgan qator nomini ko'rsatadi.

Aslida, Excelda koordinatalarni qidirish kerak. Bu nima uchun? Ko'pincha biz qiymat bo'yicha jadvalning koordinatalarini olishimiz kerak. Bir oz teskari matritsa tahliliga o'xshaydi. Qisqacha aytganda, aniq bir misol shunga o'xshash narsaga o'xshaydi. Raqamlarda belgilangan maqsad boshlang'ich qiymat bo'lib, bu maqsadga kim va qachon yaqinroq ekanligini aniqlash kerak. Misol sifatida, quyidagi rasmda ko'rsatilganidek, sotilgan tovarlarning to'rtdan uch qismini hisobot beruvchi oddiy ma'lumotlar matritsasidan foydalanamiz. Barcha raqamlar mos kelishi muhim. Agar Excel elektron jadvalini noldan qo'lda yaratish va to'ldirish istagi bo'lmasa, maqolaning oxirida siz tayyor misolni yuklab olishingiz mumkin.

Biz har xil murakkablikdagi echimlarni ketma-ket ko'rib chiqamiz va maqolaning oxirida - yakuniy natija.

Excel ustunida qiymatni topish

Birinchidan, jadval ustunlari sarlavhalarini qiymat bo'yicha qanday olishni o'rganamiz. Buning uchun quyidagi amallarni bajaring:

1. B1 katakka 5277-jadvaldan olingan qiymatni kiriting va kiritish maydonining o'qilishi uchun uning fonini ko'k rang bilan belgilang (bundan keyin biz yangi qiymatlar bilan tajriba o'tkazish uchun B1 katakka boshqa raqamlarni kiritamiz).
2. C2 katakka ushbu qiymatni o'z ichiga olgan jadval ustunining sarlavhasini olish uchun formulani kiriting:
3. Tasdiqlash uchun formulani kiritgandan so'ng, CTRL + SHIFT + Enter tugmalar birikmasini bosing, chunki formula massivda bajarilishi kerak. To'g'ri bajarilgan bo'lsa, formulalar satrining chetlarida jingalak qavslar ( ) paydo bo'ladi.

Excel qatorida qiymatni topish

Endi biz bir xil qiymat uchun satr raqamini olamiz (5277). Buning uchun C3 katakchaga quyidagi formulani kiriting:

Tasdiqlash uchun formulani kiritgandan so'ng, yana CTRL + SHIFT + Enter tugmalar birikmasini bosing va natijani oling:

Formula 9 raqamini qaytardi - u jadvalning mos keladigan qiymati bo'yicha varaq satrining sarlavhasini topdi. Natijada, bizda D9 qiymatining to'liq manzili mavjud.

Jadvalning ustun sarlavhasi va satr sarlavhasini qanday olish mumkin

Endi keling, butun varaqning emas, balki joriy jadvalning koordinatalarini qiymat bo'yicha qanday olishni o'rganamiz. Bir so'z bilan aytganda, sarlavhalarni olish uchun D9 o'rniga 5277 qiymatini topishimiz kerak:

• jadval ustuni uchun - mart;
• qator uchun - 4-band.

Ushbu muammoni hal qilish uchun biz C2 va C3 kataklarida allaqachon olingan qiymatlar bilan formuladan foydalanamiz. Buning uchun biz shunday qilamiz:

Natijada jadvalning ichki koordinatalari qiymat bo'yicha olinadi - mart; 4-modda:

Excel diapazonida bir xil qiymatlarni topish

Jadval qiymatlari orasida dublikatlar mavjudligini nazorat qilish uchun biz dublikatlar mavjudligi haqida bizga xabar beradigan va ularning sonini hisoblaydigan formulani yaratamiz. Buning uchun E2 katakchaga formulani kiriting:

Bundan tashqari, jadval bo'limi diapazoni uchun biz shartli formatlash qoidasini yaratamiz:

Ko'rib turganingizdek, agar dublikatlar mavjud bo'lsa, sarlavha formulasi sarlavhani birinchi dublikatdan gorizontal ravishda (chapdan o'ngga) oladi. Va satrning nomini (raqamini) olish uchun formula birinchi dublikatdan raqamni vertikal (yuqoridan pastga) oladi. Ushbu yechimni tuzatishning 2 yo'li mavjud:

Bunday holda, biz formulalarni bir yoki boshqasini o'zgartiramiz, lekin bir vaqtning o'zida ikkita emas. Eslatib o'tamiz, eski formula C3 katagida qolishi kerak:

Bu erda birinchi dublikatning vertikal koordinatalari (yuqoridan pastga) to'g'ri ko'rsatilgan - varaq va avgust uchun I7; Stol uchun mahsulot 2. Keling, ushbu variantni keyingi yakuniy misol uchun qoldiramiz.

Excel diapazonida eng yaqin qiymatni topish

Ushbu jadval hali ham mukammal emas. Axir, tahlil qilishda siz uning barcha qiymatlarini aniq bilishingiz kerak. Agar B1 katakka kiritilgan raqam jadvalda topilmasa, xatolik qaytariladi - #VALUE! Jadvalda boshlang'ich raqam bo'lmasa, formulaning o'zi jadvaldagi eng yaqin qiymatni tanlasa, ideal bo'lar edi. Bunday jadvalni tahlil qilish dasturini yaratish uchun F1 katakka yangi formulani kiriting:

Shundan so'ng boshqa barcha formulalarda B1 o'rniga havola F1 bo'lishi kerak! Shuningdek, shartli formatlashda havolani o'zgartirishingiz kerak. Tanlang: "Uy" - "Uslublar" - "Shartli formatlash" - "Qoidalarni boshqarish" - "Qoidani tahrirlash". Va bu erda parametrlarda B1 o'rniga F1 ni belgilang. Dasturni sinab ko'rish uchun B1 katakka jadvalda bo'lmagan raqamni kiriting, masalan: 8000. Bu yakuniy natijaga olib keladi:

Endi siz har qanday boshlang'ich qiymatni kiritishingiz mumkin va dasturning o'zi jadvaldagi eng yaqin raqamni tanlaydi. Keyin joriy qiymat uchun ustun sarlavhasi va satr nomini ko'rsatadi. Masalan, agar siz 5000 raqamini kiritsangiz, biz yangi natijaga erishamiz:

Excel dasturidagi dasturimiz dastlabki qiymat uchun 4965 ning eng yaqin qiymatini topdi - 5000. Bunday dastur biznesni rejalashtirishda turli xil analitik masalalarni avtomatik ravishda hal qilish, maqsadlarni belgilash, oqilona yechim topish va hokazolar uchun foydali bo'lishi mumkin. Olingan qatorlar va ustunlar esa yangi Excel formulalaridan foydalangan holda ushbu turdagi hisobotlarning hisoblash imkoniyatlarini yanada kengaytirish imkonini beradi.

Ko'p sonli maydonlardan tashkil topgan Microsoft Excel hujjatlarida ko'pincha aniq ma'lumotlarni, qator nomini va hokazolarni topish kerak bo'ladi. To'g'ri so'z yoki iborani topish uchun juda ko'p qatorlarni ko'rib chiqish juda noqulay. O'rnatilgan Microsoft Excel qidiruvi vaqt va asablarni tejashga yordam beradi. Keling, u qanday ishlashini va undan qanday foydalanishni ko'rib chiqaylik.

Microsoft Excel-dagi qidiruv funktsiyasi "Topish va almashtirish" oynasi orqali kerakli matn yoki raqam qiymatlarini topish imkoniyatini taqdim etadi. Bundan tashqari, ilova kengaytirilgan ma'lumotlarni qidirish imkoniyatiga ega.

1-usul: oddiy qidiruv

Excelda oddiy ma'lumotlarni qidirish qidiruv maydoniga kiritilgan belgilar to'plamini (harflar, raqamlar, so'zlar va boshqalar) o'z ichiga olgan barcha katakchalarni kichik harf sezgirligisiz topish imkonini beradi.

2-usul: Belgilangan hujayralar oralig'ida qidirish

Agar sizda juda katta jadval bo'lsa, unda bu holda butun varaqni qidirish har doim ham qulay emas, chunki qidiruv natijalari ma'lum bir holatda kerak bo'lmagan juda ko'p natijalarni o'z ichiga olishi mumkin. Qidiruv maydonini faqat ma'lum bir hujayralar oralig'ida cheklashning bir usuli mavjud.

3-usul: Kengaytirilgan qidiruv

Yuqorida aytib o'tilganidek, oddiy qidiruv paytida, har qanday shakldagi ketma-ket qidiruv belgilar to'plamini o'z ichiga olgan barcha hujayralar, har qanday holatdan qat'i nazar, masala natijalariga kiradi.

Bundan tashqari, chiqishga nafaqat ma'lum bir hujayraning tarkibi, balki u tegishli bo'lgan elementning manzili ham kirishi mumkin. Masalan, E2 katakchasida A4 va C3 kataklarining yig'indisi bo'lgan formula mavjud. Bu summa 10 ga teng va bu E2 katakchada ko'rsatilgan raqam. Ammo, agar biz qidiruvda "4" raqamini qo'ysak, xuddi shu E2 katakchasi chiqish natijalari orasida bo'ladi. Bu qanday sodir bo'lishi mumkin? E2 katakchasi formula sifatida A4 katak manzilini o'z ichiga oladi, u faqat kerakli 4 raqamini o'z ichiga oladi.

Ammo shunga o'xshash va boshqa nomaqbul qidiruv natijalarini qanday qilib kesish mumkin? Aynan shu maqsadlar uchun kengaytirilgan Excel qidiruvi mavjud.

1. Oynani ochgandan keyin "Topish va almashtirish" yuqoridagi usullardan birida tugmani bosing "Tanlovlar".



2. Oynada qidiruvni boshqarish uchun bir qator qo'shimcha vositalar paydo bo'ladi. Odatiy bo'lib, ushbu vositalarning barchasi oddiy qidiruvda bo'lgani kabi bir xil holatda bo'ladi, lekin agar kerak bo'lsa, o'zgartirishlar kiritishingiz mumkin.

   

   Odatiy bo'lib, funktsiyalar "harflar katta-kichikligiga sezgir" Va "To'liq hujayralar" o'chirilgan, ammo agar biz tegishli elementlarning yonidagi katakchalarni belgilasak, unda bu holda natijani yaratishda kiritilgan holat va aniq moslik hisobga olinadi. Agar siz kichik harfli so'zni kiritsangiz, qidiruv natijalarida ushbu so'zning imlosini bosh harf bilan o'z ichiga olgan hujayralar, odatdagidek, endi kiritilmaydi. Bundan tashqari, agar funktsiya yoqilgan bo'lsa "To'liq hujayralar", keyin faqat aniq nomni o'z ichiga olgan elementlar chiqishga qo'shiladi. Misol uchun, agar siz "Nikolaev" qidiruv so'rovini o'rnatgan bo'lsangiz, u holda "Nikolaev A.D." matnini o'z ichiga olgan katakchalar qidiruv natijalariga qo'shilmaydi.

   

   Odatiy bo'lib, qidiruv faqat faol Excel varag'ida amalga oshiriladi. Ammo parametr bo'lsa "Qidirmoq" pozitsiyasiga o'tasiz "Kitobda", keyin qidiruv ochiq faylning barcha varaqlarida amalga oshiriladi.

   

   Parametrda "Ko'rib chiqish" qidiruv yo'nalishini o'zgartirishingiz mumkin. Odatiy bo'lib, yuqorida aytib o'tilganidek, qidiruv satr bo'yicha tartibda amalga oshiriladi. Kalitni holatiga o'tkazish orqali "ustunlar bo'yicha", siz birinchi ustundan boshlab chiqish natijalarini yaratish tartibini o'rnatishingiz mumkin.

   

   Grafikda "Qidiruv maydoni" qaysi alohida elementlar qidirilayotganligini aniqlaydi. Odatiy bo'lib, bu formulalar, ya'ni hujayra ustiga bosganingizda formulalar qatorida ko'rsatiladigan ma'lumotlar. Bu so'z, raqam yoki hujayra havolasi bo'lishi mumkin. Shu bilan birga, dastur qidiruvni amalga oshirayotganda natijani emas, balki faqat havolani ko'radi. Ushbu ta'sir yuqorida muhokama qilingan. Natijalar bo'yicha, formulalar satrida emas, balki katakchada ko'rsatilgan ma'lumotlar bo'yicha aniq qidirish uchun siz kalitni joydan o'zgartirishingiz kerak. "formulalar" holatiga "Qiymatlar". Bundan tashqari, eslatmalar bo'yicha qidirish mumkin. Bunday holda, kalit pozitsiyasiga o'tkaziladi "Eslatmalar".

   

   Tugmani bosish orqali qidiruvni yanada aniqroq belgilashingiz mumkin "Format".

   

   Bu hujayralarni formatlash oynasini ochadi. Bu yerda siz qidiruvda ishtirok etadigan katakchalar formatini belgilashingiz mumkin. Raqam formati, hizalama, shrift, chegara, soya va himoya, ushbu variantlardan biri yoki ularning kombinatsiyasiga cheklovlar o'rnatishingiz mumkin.

   

   Agar siz ma'lum bir hujayraning formatini ishlatmoqchi bo'lsangiz, oynaning pastki qismidagi tugmani bosing "Ushbu katak formatidan foydalaning...".

   

   Shundan so'ng, ko'z tomchisi shaklidagi vosita paydo bo'ladi. Uning yordamida siz formatidan foydalanmoqchi bo'lgan katakchani tanlashingiz mumkin.

   

   Qidiruv formati sozlangandan so'ng tugmani bosing KELISHDIKMI.

   

   Ba'zida ma'lum bir iborani emas, balki boshqa so'zlar va belgilar bilan ajratilgan bo'lsa ham, istalgan tartibda qidirish so'zlarini o'z ichiga olgan kataklarni topish kerak bo'lgan holatlar mavjud. Keyin bu so'zlarni ikkala tomondan "*" belgisi bilan ta'kidlash kerak. Endi qidiruv natijalari ushbu so'zlar joylashgan barcha hujayralarni istalgan tartibda ko'rsatadi.



3. Qidiruv sozlamalari o'rnatilgandan so'ng, tugmani bosing "Hammasini top" yoki "Keyingisini top" qidiruv natijalariga o'tish uchun.

Ko'rib turganingizdek, Excel dasturi juda oddiy, ammo ayni paytda juda funktsional qidiruv vositalari to'plamidir. Eng oddiy qichqiriqni qilish uchun qidiruv oynasiga qo'ng'iroq qilish, unga so'rov kiritish va tugmani bosish kifoya. Ammo, shu bilan birga, ko'p sonli turli xil parametrlar va qo'shimcha sozlamalar bilan individual qidiruvni o'rnatish imkoniyati mavjud.