Mundarija
Dinamik massivlar nima
2018-yil sentabr oyida Microsoft Microsoft Excelga mutlaqo yangi vositani qo‘shadigan yangilanishni chiqardi: Dinamik massivlar va ular bilan ishlash uchun 7 ta yangi funksiya. Bu narsalar, mubolag'asiz, formulalar va funktsiyalar bilan ishlashning barcha odatiy texnikasini tubdan o'zgartiradi va har bir foydalanuvchini tashvishlantiradi.
Mohiyatni tushuntirish uchun oddiy misolni ko'rib chiqing.
Aytaylik, bizda shahar-oylar haqidagi ma'lumotlarga ega oddiy jadval mavjud. Agar biz varaqning o'ng tomonidagi har qanday bo'sh katakchani tanlab, unga bitta katakka emas, balki darhol diapazonga bog'laydigan formulani kiritsak nima bo'ladi?
Excelning barcha oldingi versiyalarida, ustiga bosgandan so'ng kiriting biz faqat bitta birinchi B2 katakning mazmunini olamiz. Yana qanday qilib?
Xo'sh, yoki bu diapazonni =SUM(B2:C4) kabi qandaydir yig'ish funktsiyasiga o'rash va buning uchun umumiy summani olish mumkin bo'lar edi.
Agar bizga ibtidoiy yig'indidan ko'ra murakkabroq operatsiyalar kerak bo'lsa, masalan, noyob qiymatlarni yoki Top 3 ni chiqarish, biz klaviatura yorlig'i yordamida formulamizni massiv formulasi sifatida kiritishimiz kerak edi. Ctrl+smena+kiriting.
Endi hammasi boshqacha.
Endi bunday formulani kiritgandan so'ng, biz shunchaki bosishimiz mumkin kiriting - va natijada biz ta'kidlagan barcha uXNUMXbuXNUMXb qiymatlarini darhol oling:
Bu sehr emas, balki Microsoft Excel-da mavjud bo'lgan yangi dinamik massivlar. Yangi dunyoga xush kelibsiz 🙂
Dinamik massivlar bilan ishlash xususiyatlari
Texnik jihatdan, bizning butun dinamik massivimiz birinchi G4 katakchasida saqlanadi va kerakli miqdordagi hujayralarni o'ng va pastga ma'lumotlar bilan to'ldiradi. Agar siz massivdagi boshqa katakchani tanlasangiz, formulalar satridagi havola faol bo'lmaydi, bu biz "bola" hujayralardan birida ekanligimizni ko'rsatadi:
Bir yoki bir nechta "bola" hujayralarni o'chirishga urinish hech narsaga olib kelmaydi - Excel darhol ularni qayta hisoblab chiqadi va to'ldiradi.
Shu bilan birga, biz ushbu "bola" hujayralarga boshqa formulalarda ishonch bilan murojaat qilishimiz mumkin:
Agar siz massivning birinchi katagidan nusxa ko'chirsangiz (masalan, G4 dan F8 gacha), u holda butun massiv (uning havolalari) oddiy formulalar bilan bir xil yo'nalishda harakatlanadi:
Agar biz massivni siljitishimiz kerak bo'lsa, u holda harakat qilish kifoya qiladi (sichqoncha yoki kombinatsiyasi bilan Ctrl+X, Ctrl+V), yana faqat birinchi asosiy katak G4 - undan keyin u yangi joyga ko'chiriladi va butun massivimiz yana kengaytiriladi.
Agar siz varaqning boshqa joyida yaratilgan dinamik massivga murojaat qilishingiz kerak bo'lsa, uning bosh katakchasi manzilidan keyin # ("funt") maxsus belgisidan foydalanishingiz mumkin:
Misol uchun, endi siz yaratilgan dinamik massivga tegishli bo'lgan katakchada osongina ochiladigan ro'yxatni yaratishingiz mumkin:
Dinamik massiv xatolari
Ammo massivni kengaytirish uchun etarli joy bo'lmasa yoki uning yo'lida boshqa ma'lumotlar bilan band bo'lgan hujayralar mavjud bo'lsa nima bo'ladi? Excelda mutlaqo yangi turdagi xatolar bilan tanishing - #TRANSFER! (#TO'KMA!):
Har doimgidek, agar biz sariq olmos va undov belgisi bo'lgan belgini bossak, muammoning manbasini batafsilroq tushuntiramiz va biz aralashadigan hujayralarni tezda topamiz:
Agar massiv varaqdan chiqib ketsa yoki birlashtirilgan katakka tegsa, shunga o'xshash xatolar yuzaga keladi. Agar siz to'siqni olib tashlasangiz, hamma narsa darhol tezda tuzatiladi.
Dinamik massivlar va aqlli jadvallar
Agar dinamik massiv klaviatura yorlig'i yordamida yaratilgan "aqlli" jadvalga ishora qilsa Ctrl+T yoki Bosh sahifa - Jadval sifatida formatlash (Uy — Jadval sifatida formatlash), keyin u o'zining asosiy sifatini ham meros qilib oladi - avtomatik o'lcham.
Pastki yoki o'ngga yangi ma'lumotlarni qo'shganda, aqlli jadval va dinamik diapazon ham avtomatik ravishda cho'ziladi:
Biroq, bitta cheklov mavjud: biz aqlli jadval ichidagi forumlarda dinamik diapazondan foydalana olmaymiz:
Dinamik massivlar va boshqa Excel xususiyatlari
Yaxshi, siz aytasiz. Bularning barchasi qiziqarli va kulgili. Avvalgidek, formulani asl diapazonning birinchi katagiga havola bilan pastga va o'ngga va shunga o'xshash narsalarni qo'lda cho'zish kerak emas. Va hammasi shumi?
juda emas.
Dinamik massivlar Excelda shunchaki boshqa vosita emas. Endi ular Microsoft Excelning qalbiga (yoki miyasiga) - uning hisoblash mexanizmiga kiritilgan. Bu shuni anglatadiki, endi bizga tanish bo'lgan boshqa Excel formulalari va funktsiyalari ham dinamik massivlar bilan ishlashni qo'llab-quvvatlaydi. Keling, ro'y bergan o'zgarishlarning chuqurligi haqida tasavvurga ega bo'lish uchun bir nechta misollarni ko'rib chiqaylik.
O'tkazish
Diapazonni ko'chirish (satr va ustunlarni almashtirish) Microsoft Excel har doim o'rnatilgan funksiyaga ega bo'lgan TRANSP (TRANSPOZE). Biroq, undan foydalanish uchun avval natijalar diapazonini to'g'ri tanlashingiz kerak (masalan, agar kiritilgan 5×3 diapazon bo'lsa, u holda siz 3×5 ni tanlagan bo'lishingiz kerak), keyin funksiyani kiriting va tugmani bosing. kombinatsiya Ctrl+smena+kiriting, chunki u faqat massiv formulasi rejimida ishlashi mumkin edi.
Endi siz faqat bitta katakchani tanlashingiz, unga bir xil formulani kiritishingiz va oddiy tugmani bosishingiz mumkin kiriting - dinamik massiv hamma narsani o'zi bajaradi:
Ko'paytirish jadvali
Bu men Excelda massiv formulalarining afzalliklarini tasavvur qilishimni so'rashganda, men bergan misol. Endi, butun Pifagor jadvalini hisoblash uchun, birinchi B2 katakchasida turish kifoya, u erda ikkita massivni (vertikal va gorizontal raqamlar to'plami 1..10) ko'paytiruvchi formulani kiriting va shunchaki bosing. kiriting:
Yelimlash va korpusni konvertatsiya qilish
Massivlarni nafaqat ko'paytirish, balki standart operator & (ampersand) bilan birga yopishtirish ham mumkin. Aytaylik, ikkita ustundan ism va familiyani ajratib olishimiz va asl ma'lumotlardagi sakrash holatini tuzatishimiz kerak. Biz buni butun massivni tashkil etuvchi bitta qisqa formula bilan qilamiz va keyin unga funksiyani qo'llaymiz PROPNACH (TO'G'RI)registrni tartibga solish uchun:
Xulosa Top 3
Faraz qilaylik, bizda bir nechta raqamlar bor, ulardan eng yaxshi uchta natijani olishni xohlaymiz, ularni kamayish tartibida joylashtiramiz. Endi bu bitta formula bo'yicha va yana hech qanday holda amalga oshiriladi Ctrl+smena+kiriting oldingi kabi:
Agar natijalar ustunga emas, balki qatorga joylashtirilishini istasangiz, bu formuladagi ikki nuqtani (chiziq ajratuvchi) nuqtali vergul (bir qator ichidagi elementlarni ajratuvchi) bilan almashtirish kifoya. Excelning inglizcha versiyasida bu ajratgichlar mos ravishda nuqtali vergul va verguldir.
VLOOKUP bir vaqtning o'zida bir nechta ustunlarni chiqaradi
vazifalari VPR (KO'RISH) Endi siz qiymatlarni birdan emas, balki bir nechta ustunlardan olishingiz mumkin - ularning raqamlarini (istalgan tartibda) funktsiyaning uchinchi argumentida massiv sifatida ko'rsatish kifoya:
OFFSET funksiyasi dinamik massivni qaytaradi
Ma'lumotlarni tahlil qilish uchun eng qiziqarli va foydali (VLOOKUP dan keyin) funktsiyalardan biri bu funktsiyadir ISHLASH (OFSET), men bir vaqtning o'zida kitobimning butun bobini va bu erda bir maqolani bag'ishlaganman. Ushbu funktsiyani tushunish va o'zlashtirishdagi qiyinchilik har doim shunday bo'lib kelganki, u natijada ma'lumotlar massivini (diapazonini) qaytardi, lekin biz buni ko'ra olmadik, chunki Excel hali ham qutidan tashqarida massivlar bilan ishlashni bilmas edi.
Endi bu muammo o'tmishda qoldi. Endi qanday qilib bitta formuladan va OFFSET tomonidan qaytarilgan dinamik massivdan foydalanib, istalgan tartiblangan jadvaldan berilgan mahsulot uchun barcha qatorlarni ajratib olishingiz mumkinligini ko'ring:
Keling, uning dalillarini ko'rib chiqaylik:
- A1 - boshlang'ich katak (mos yozuvlar nuqtasi)
- POISKPOZ(F2;A2:A30;0) - boshlang'ich hujayradan pastga - birinchi topilgan karamga o'tishni hisoblash.
- 0 – “oyna”ning boshlang‘ich katakka nisbatan o‘ngga siljishi
- SChYoTESLI(A2:A30;F2) - qaytarilgan "deraza" ning balandligini hisoblash - karam mavjud bo'lgan chiziqlar soni.
- 4 — gorizontal «oyna» o'lchami, ya'ni 4 ta ustunni chiqarish
Dinamik massivlar uchun yangi funksiyalar
Eski funktsiyalarda dinamik massiv mexanizmini qo'llab-quvvatlashdan tashqari, Microsoft Excel-ga dinamik massivlar bilan ishlash uchun maxsus aniqlangan bir nechta mutlaqo yangi funktsiyalar qo'shildi. Xususan, bular:
- sinf (SARALASH) – kirish diapazonini saralaydi va chiqishda dinamik massiv hosil qiladi
- SORTPO (SARALASH TURI) - bir diapazonni boshqasidan qiymatlari bo'yicha saralashi mumkin
- Filtrni (FILTR) – belgilangan shartlarga javob beruvchi manba diapazonidan qatorlarni oladi
- UNIK (BEKISTON) - diapazondan noyob qiymatlarni chiqaradi yoki dublikatlarni olib tashlaydi
- SLMASSIVE (RANDARRAY) – berilgan o‘lchamdagi tasodifiy sonlar massivini hosil qiladi
- TUG'ILGAN KEYIN (SEQUENCE) — berilgan qadamli sonlar ketma-ketligidan massiv hosil qiladi
Ular haqida ko'proq - birozdan keyin. Ular chuqur o'rganish uchun alohida maqolaga (va bitta emas) arziydi 🙂
Xulosa
Agar siz yuqorida yozilganlarning barchasini o'qigan bo'lsangiz, menimcha, siz sodir bo'lgan o'zgarishlarning ko'lamini allaqachon tushungansiz. Excelda ko'p narsalarni endi osonroq, osonroq va mantiqiyroq qilish mumkin. Tan olishim kerakki, endi bu yerda, ushbu saytda va kitoblarimda qancha maqolalar tuzatilishi kerakligi meni hayratda qoldirdi, lekin men buni engil yurak bilan qilishga tayyorman.
Natijalarni umumlashtirib, qo'shib qo'ydi dinamik massivlar uchun quyidagilarni yozishingiz mumkin:
- Siz kombinatsiya haqida unutishingiz mumkin Ctrl+smena+kiriting. Excel endi "oddiy formulalar" va "massiv formulalari" o'rtasidagi farqni ko'rmaydi va ularga xuddi shunday munosabatda bo'ladi.
- Funktsiya haqida SUMPRODUCT (SUMPRODUCT), bundan oldin massiv formulalarini kiritish uchun foydalanilgan Ctrl+smena+kiriting siz ham unutishingiz mumkin - endi bu juda oson SUM и kiriting.
- Smart jadvallar va tanish funksiyalar (SUM, IF, VLOOKUP, SUMIFS va boshqalar) endi dinamik massivlarni ham to‘liq yoki qisman qo‘llab-quvvatlaydi.
- Orqaga moslik mavjud: agar siz Excelning eski versiyasida dinamik massivlar bilan ish kitobini ochsangiz, ular massiv formulalariga aylanadi (jingalak qavslarda) va "eski uslubda" ishlashni davom ettiradi.
Raqam topildi minuslar:
- Dinamik massivdan alohida satrlar, ustunlar yoki kataklarni o'chira olmaysiz, ya'ni u bitta ob'ekt sifatida yashaydi.
- Siz dinamik massivni odatdagidek saralay olmaysiz Ma'lumotlar - saralash (Ma'lumotlar - Saralash). Endi buning uchun maxsus funktsiya mavjud. sinf (SARALASH).
- Dinamik diapazonni aqlli stolga aylantirib bo'lmaydi (lekin siz aqlli jadval asosida dinamik diapazon yaratishingiz mumkin).
Albatta, bu oxiri emas va Microsoft kelajakda bu mexanizmni takomillashtirishda davom etishiga ishonaman.
Qayerdan yuklab olsam bo'ladi?
Va nihoyat, asosiy savol 🙂
Microsoft birinchi marta 2018 yil sentyabr oyida konferentsiyada Excel-dagi dinamik massivlarning ko'rinishini e'lon qildi va ko'rsatdi yonmoq. Keyingi bir necha oy ichida birinchi navbatda yangi xususiyatlar to'liq sinovdan o'tkazildi va ishga tushirildi Mushuklar Microsoft-ning o'zi xodimlari, keyin esa Office Insiderlar doirasidagi ko'ngilli testerlarda. Bu yil dinamik massivlarni qo'shadigan yangilanish asta-sekin oddiy Office 365 obunachilariga tarqatila boshlandi. Masalan, men uni faqat avgust oyida Office 365 Pro Plus (Oylik maqsadli) obunam bilan oldim.
Agar sizning Excel-da hali dinamik massivlar bo'lmasa, lekin siz ular bilan ishlashni xohlasangiz, unda quyidagi variantlar mavjud:
- Agar sizda Office 365 obunasi bo'lsa, ushbu yangilanish sizga yetib kelguncha kutishingiz mumkin. Bu qanchalik tez sodir bo'lishi ofisingizga yangilanishlar qanchalik tez-tez yetkazilishiga bog'liq (yilda bir marta, olti oyda bir marta, oyda bir marta). Agar sizda korporativ shaxsiy kompyuteringiz bo'lsa, administratoringizdan yangilanishlarni tez-tez yuklab olish uchun sozlashni so'rashingiz mumkin.
- Siz Office Insider test ko'ngillilari safiga qo'shilishingiz mumkin - shunda siz birinchi bo'lib barcha yangi funksiyalar va funksiyalarni qabul qilasiz (lekin Excelda xatolikni oshirish ehtimoli bor, albatta).
- Agar sizda obuna bo'lmasa, lekin Excelning alohida qutili versiyasi bo'lsa, hech bo'lmaganda 2022 yilda Office va Excelning keyingi versiyasi chiqquncha kutishingiz kerak bo'ladi. Bunday versiyalarning foydalanuvchilari faqat xavfsizlik yangilanishlari va xatolarni tuzatadilar va barcha yangi "yaxshiliklar" endi faqat Office 365 obunachilariga beriladi. Achinarli, lekin haqiqat 🙂
Qanday bo'lmasin, Excel-da dinamik massivlar paydo bo'lganda - ushbu maqoladan keyin siz bunga tayyor bo'lasiz 🙂
- Massiv formulalari nima va ularni Excelda qanday ishlatish kerak
- OFFSET funktsiyasi yordamida oyna (diapazon) yig'indisi
- Excelda jadvalni ko'chirishning 3 usuli