мазмуну
Динамикалык массивдер деген эмне
2018-жылдын сентябрында Microsoft Microsoft Excel программасына таптакыр жаңы куралды кошкон жаңыртууну чыгарды: Динамикалык массивдер жана алар менен иштөө үчүн 7 жаңы функция. Бул нерселер, апыртпастан, формулалар жана функциялар менен иштөөнүн бардык кадимки техникасын түп-тамырынан бери өзгөртөт жана түзмө-түз ар бир колдонуучуга тынчсызданат.
Маңызын түшүндүрүү үчүн жөнөкөй мисалды карап көрөлү.
Бизде шаар-айлар боюнча маалыматтар бар жөнөкөй таблица бар дейли. Эгерде биз барактын оң жагындагы каалаган бош уячаны тандап, ага бир уячага эмес, дароо диапазонго байланыштырган формуланы киргизсек эмне болот?
Excelдин бардык мурунку версияларында, чыкылдаткандан кийин кирүү биз бир гана биринчи B2 уячасынын мазмунун алабыз. Башка кантип?
Ооба, же бул диапазонду =SUM(B2:C4) сыяктуу кандайдыр бир топтоо функциясына ороп, ал үчүн жалпы сумманы алууга болот.
Эгерде бизге уникалдуу маанилерди алуу же Топ 3 сыяктуу примитивдүү суммага караганда татаалыраак операциялар керек болсо, анда биз формулабызды баскычтоптун жарлыгын пайдаланып массив формуласы катары киргизишибиз керек болот. Ctrl+өзгөрүү+кирүү.
Азыр баары башкача.
Эми мындай формуланы киргизгенден кийин, биз жөн гана чыкылдата алабыз кирүү - жана натыйжада биз айткан бардык uXNUMXbuXNUMXb баалуулуктарын дароо алыңыз:
Бул сыйкыр эмес, бирок Microsoft Excel азыр ээ болгон жаңы динамикалык массивдер. Жаңы дүйнөгө кош келиңиз 🙂
Динамикалык массивдер менен иштөөнүн өзгөчөлүктөрү
Техникалык жактан алганда, биздин бүт динамикалык массивибиз биринчи G4 уячасында сакталып, керектүү сандагы уячаларды анын маалыматтары менен оңго жана ылдыйга толтурат. Эгерде сиз массивден башка уячаны тандасаңыз, анда формула тилкесиндеги шилтеме жигердүү эмес болуп калат, бул биздин "бала" уячалардын биринде экенибизди көрсөтөт:
Бир же бир нече "балдар" уячаларын жок кылуу аракети эч нерсеге алып келбейт - Excel аларды дароо кайра эсептеп, толтурат.
Ошол эле учурда, биз аман-эсен бул "бала" клеткаларга башка формулалар менен кайрыла алабыз:
Эгерде сиз массивдин биринчи уячасын көчүрсөңүз (мисалы, G4тен F8ге чейин), анда бүт массив (анын шилтемелери) кадимки формулалардагыдай багытта жылат:
Эгер массивди жылдырышыбыз керек болсо, анда аны жылдыруу жетиштүү болот (чычкан же комбинация менен Ctrl+X, Ctrl+V), дагы, биринчи гана G4 негизги уячасы - андан кийин, ал жаңы жерге которулат жана биздин массив кайрадан кеңейтилет.
Эгер сиз барактын башка жеринде түзүлгөн динамикалык массивге кайрылууңуз керек болсо, анда анын алдыңкы уячасынын дарегинен кийин # (фунт) өзгөчө белгисин колдонсоңуз болот:
Мисалы, эми сиз түзүлгөн динамикалык массивге тиешелүү уячада ылдый түшүүчү тизмени оңой түзө аласыз:
Динамикалык массивдин каталары
Бирок массивди кеңейтүү үчүн орун жетишсиз болсо, же анын жолунда башка маалыматтар ээлеген клеткалар бар болсо, эмне болот? Excelде жаңы типтеги каталар менен таанышыңыз - #Трансфер! (#ТӨКҮП!):
Адаттагыдай эле, сары бриллиант жана илеп белгиси бар сөлөкөттү чыкылдатсак, биз көйгөйдүн булагы жөнүндө кененирээк түшүндүрмө алабыз жана тоскоолдук кылган клеткаларды тез таба алабыз:
Окшош каталар массив барактан чыгып кетсе же бириктирилген уячага тийсе пайда болот. Эгер сиз тоскоолдукту алып салсаңыз, анда баары тез арада оңдолот.
Динамикалык массивдер жана акылдуу таблицалар
Эгерде динамикалык массив баскычтоптун жарлыгы менен түзүлгөн "акылдуу" таблицага ишарат кылса Ctrl+T же тарабынан Башкы бет – Таблица катары форматтоо (Башкы бет — Таблица катары формат), анда ал да өзүнүн негизги сапатын мурастап калат - авто-өлчөм.
Жаңы маалыматтарды ылдыйга же оңго кошкондо, акылдуу таблица жана динамикалык диапазон да автоматтык түрдө созулат:
Бирок, бир чектөө бар: биз акылдуу столдун ичиндеги форумдарда динамикалык диапазондун шилтемесин колдоно албайбыз:
Динамикалык массивдер жана башка Excel функциялары
Макул, сен айтасың. Мунун баары кызыктуу жана күлкүлүү. Мурдагыдай эле формуланы кол менен баштапкы диапазондун биринчи уячасына ылдый жана оңго жана ушуга чейин созуунун кереги жок. Жана ушунчабы?
Андай эмес.
Динамикалык массивдер Excelдеги башка курал эмес. Эми алар Microsoft Excelдин жүрөгүнө (же мээсине) орнотулган - анын эсептөө кыймылдаткычы. Бул бизге тааныш болгон башка Excel формулалары жана функциялары азыр динамикалык массивдер менен иштөөнү колдойт дегенди билдирет. Сизге болгон өзгөрүүлөрдүн тереңдиги жөнүндө түшүнүк берүү үчүн бир нече мисалдарды карап көрөлү.
Transpose
Диапазонду которуу үчүн (саптарды жана мамычаларды алмаштыруу) Microsoft Excel ар дайым орнотулган функцияга ээ ТРАНСП (TRANSPOSE). Бирок, аны колдонуу үчүн, адегенде натыйжалардын диапазонун туура тандоо керек (мисалы, киргизүү 5×3 диапазону болсо, анда сиз 3×5ти тандаган болушуңуз керек), андан кийин функцияны киргизип, баскычты басыңыз. айкалышы Ctrl+өзгөрүү+кирүү, анткени ал массив формуласы режиминде гана иштей алган.
Эми сиз жөн гана бир уячаны тандап, ага ошол эле формуланы киргизип, нормалдуу баскычты чыкылдатсаңыз болот кирүү – динамикалык массив баарын өзү жасайт:
Көбөйтүү таблицасы
Бул Excelдеги массив формулаларынын артыкчылыктарын визуалдаштырууну суранганда мен берген мисал. Эми, бардык Пифагор таблицасын эсептөө үчүн, биринчи B2 уячасында туруп, ал жерге эки массивди (вертикалдуу жана горизонталдык топтом 1..10 сандары) көбөйтүүчү формуланы киргизип, жөн гана чыкылдатыңыз. кирүү:
Желимдөө жана корпустун конверсиясы
Массивдерди гана көбөйтпөстөн, стандарттуу & (амперсанд) оператору менен жабыштырса болот. Биз эки мамычанын аты менен фамилиясын чыгарып, баштапкы маалыматтардагы секирүү учурун оңдошубуз керек дейли. Биз муну бүт массивди түзгөн бир кыска формула менен жасайбыз, анан ага функцияны колдонобуз PROPNACH (ТУУРА)реестрди иретке келтирүү үчүн:
Жыйынтык Топ 3
Бизде бир топ сандар бар дейли, алардан биз аларды кемүү иретинде тизип, эң жогорку үч натыйжаны алгыбыз келет. Эми бул бир формула менен жасалат жана дагы, эч кандай жок Ctrl+өзгөрүү+кирүү мурдагыдай:
Эгерде сиз жыйынтыктарды мамычага эмес, катарга жайгаштырууну кааласаңыз, анда бул формуладагы кош чекиттерди (сап бөлгүчтү) чекиттүү үтүрлүү (бир саптын ичиндеги элемент бөлгүч) алмаштыруу жетиштүү. Excelдин англис версиясында бул бөлгүчтөр тиешелүүлүгүнө жараша чекиттүү үтүр жана үтүр болуп саналат.
VLOOKUP бир эле учурда бир нече мамычаларды чыгарып жатат
милдеттери VPR (КӨРҮҮ) Эми сиз маанилерди бирден эмес, бир эле учурда бир нече тилкеден чыгара аласыз – жөн гана алардын сандарын (каалаган тартипте) функциянын үчүнчү аргументинде массив катары көрсөтүңүз:
OFFSET функциясы динамикалык массивди кайтарып берет
Маалыматтарды талдоо үчүн эң кызыктуу жана пайдалуу функциялардын бири (VLOOKUP кийин) бул функция DISPOSAL (OFSET), Мен бир убакта китебимдин бүтүндөй бир бөлүмүн жана бул жерде бир макаланы арнадым. Бул функцияны түшүнүү жана өздөштүрүүдөгү кыйынчылык ар дайым анын натыйжасында берилиштердин массивин (диапазонун) кайтарып берген, бирок биз аны көрө алган жокпуз, анткени Excel дагы эле кутудан тышкары массивдер менен иштөөнү билген эмес.
Азыр бул маселе өткөндө калды. Эми кантип бир формуланы жана OFFSET тарабынан кайтарылган динамикалык массивди колдонуп, каалаган сорттолгон таблицадан берилген продукт үчүн бардык саптарды кантип чыгара аларыңызды көрүңүз:
Келгиле, анын аргументтерин карап көрөлү:
- A1 – баштапкы уяча (маалымат чекити)
- ПОИСКПОЗ(F2;A2:A30;0) – баштапкы уячадан ылдый – биринчи табылган капустага чейин жылышты эсептөө.
- 0 – «терезенин» баштапкы уячага салыштырмалуу оңго жылуусу
- СЧЁТЕСЛИ(A2:A30;F2) – кайтарылган “терезенин” бийиктигин эсептөө – капуста бар сызыктардын саны.
- 4 — «терезенин» горизонталдуу өлчөмү, башкача айтканда, 4 мамычаны чыгаруу
Динамикалык массивдер үчүн жаңы функциялар
Эски функцияларда динамикалык массив механизмин колдоодон тышкары, Microsoft Excelге динамикалык массивдер менен иштөө үчүн атайын курчутулган бир нече таптакыр жаңы функциялар кошулду. Атап айтканда, булар:
- КЛАСС (СОРТ) – киргизүү диапазонун сорттойт жана чыгарууда динамикалык массивди чыгарат
- SORTPO (ИРЕТТӨӨ) - бир диапазонду экинчисинен маанилери боюнча иреттей алат
- FILTER (ФИЛЬТР) – белгиленген шарттарга жооп берген булак диапазонунан саптарды чыгарат
- UNIQUE (Уникалдуу) - диапазондон уникалдуу маанилерди чыгарат же кайталанмаларды жок кылат
- SLMASSIVE (RANDARRAY) – берилген өлчөмдөгү кокус сандардын массивин түзөт
- ТӨРӨГӨН (ырааттуулук) — берилген кадам менен сандар ырааттуулугунан массивди түзөт
Алар жөнүндө көбүрөөк - бир аздан кийин. Алар ойлонуп изилдөө үчүн өзүнчө макала (жана бир эмес) татыктуу 🙂
Тыянактар
Эгерде сиз жогоруда жазылгандардын баарын окуп чыккан болсоңуз, анда болуп өткөн өзгөрүүлөрдүн масштабын түшүндүңүз деп ойлойм. Excelде көп нерселерди эми оңой, оңой жана логикалуураак кылса болот. Моюнга алышым керек, мен бул жерде, бул сайтта жана китептеримде канча макаланы оңдоого туура келет деп бир аз таң калдым, бирок мен муну жеңил жүрөк менен жасоого даярмын.
Жыйынтыктарды чыгарып, жакшы динамикалык массивдер, сиз төмөнкүлөрдү жаза аласыз:
- Сиз айкалыштыруу жөнүндө унутууга болот Ctrl+өзгөрүү+кирүү. Excel азыр "кадимки формулалар" менен "массив формулаларынын" ортосунда эч кандай айырманы көрбөйт жана аларга бирдей мамиле кылат.
- Функция жөнүндө СУПРОДУКТ (СУПРОДУКТ), буга чейин массив формулаларын кирүү үчүн колдонулган Ctrl+өзгөрүү+кирүү Сиз да унута аласыз – азыр бул жетиштүү жеңил СУМ и кирүү.
- Акылдуу таблицалар жана тааныш функциялар (SUM, IF, VLOOKUP, SUMIFS ж.б.) эми динамикалык массивдерди толук же жарым-жартылай колдойт.
- Артка шайкештик бар: Excelдин эски версиясында динамикалык массивдери бар жумушчу китебин ачсаңыз, алар массив формулаларына (тармал кашааларда) айланып, "эски стилде" иштей беришет.
Номер таптым минустар:
- Сиз динамикалык массивден жеке саптарды, мамычаларды же уячаларды жок кыла албайсыз, башкача айтканда, ал бир объект катары жашайт.
- Сиз динамикалык массивди кадимки жол менен иреттей албайсыз Маалыматтар - сорттоо (Маалымат — сорттоо). Бул үчүн азыр атайын функция бар. КЛАСС (СОРТ).
- Динамикалык диапазону акылдуу столго айландыруу мүмкүн эмес (бирок сиз акылдуу столдун негизинде динамикалык диапазонду түзө аласыз).
Албетте, бул аягы эмес жана Microsoft келечекте бул механизмди өркүндөтүүнү улантат деп ишенем.
Кайдан жүктөп алсам болот?
Акыр-аягы, негизги суроо 🙂
Microsoft биринчи жолу 2018-жылдын сентябрында конференцияда Excelдеги динамикалык массивдердин алдын ала көрүүсүн жарыялаган жана көрсөткөн тутандыруу. Кийинки бир нече айдын ичинде жаңы функцияларды кылдат тестирлөө жана иштетүү болду кошки Microsoft компаниясынын кызматкерлери, андан кийин Office Insider чөйрөсүндөгү ыктыярдуу тестерлер. Бул жылы динамикалык массивдерди кошкон жаңыртуу акырындык менен кадимки Office 365 абоненттерине жайылтыла баштады. Мисалы, мен аны августта Office 365 Pro Plus (Айлык максаттуу) жазылуу менен гана алдым.
Эгерде сиздин Excelде динамикалык массивдер жок болсо, бирок сиз алар менен иштөөнү кааласаңыз, анда төмөнкү параметрлер бар:
- Эгер сизде Office 365 жазылууңуз болсо, бул жаңыртуу сизге жеткенге чейин күтө аласыз. Бул канчалык тез ишке ашары жаңыртуулар сиздин кеңсеңизге канчалык тез жеткирилгенинен көз каранды (жылына бир жолу, алты айда бир жолу, айына бир жолу). Эгерде сизде корпоративдик компьютер болсо, сиз администраторуңуздан жаңыртууларды тез-тез жүктөп алуу үчүн орнотууну сурансаңыз болот.
- Сиз Office Insider тест ыктыярчыларынын катарына кошула аласыз – ошондо сиз биринчилерден болуп бардык жаңы функцияларды жана функцияларды аласыз (бирок Excelде багги көбөйүү мүмкүнчүлүгү бар, албетте).
- Эгер сизде жазылууңуз жок болсо, бирок Excel'дин кутучалуу өз алдынча версиясы болсо, анда Office жана Excelдин кийинки версиясынын 2022-жылы чыкканга чейин, жок дегенде, күтүшүңүз керек болот. Мындай версиялардын колдонуучулары коопсуздук жаңыртууларын жана мүчүлүштүктөрдү оңдоолорду гана алышат жана бардык жаңы "жакшылыктар" эми Office 365 жазылуучуларына гана өтөт. Өкүнүчтүү бирок чын 🙂
Кандай болгон күндө да, динамикалык массивдер Excelде пайда болгондо - бул макаладан кийин сиз ага даяр болосуз 🙂
- Массив формулалары деген эмне жана аларды Excelде кантип колдонуу керек
- OFFSET функциясын колдонуу менен терезенин (диапазонун) суммасы
- Excelде таблицаны которуунун 3 жолу