Мундариҷа
Массивҳои динамикӣ чистанд
Дар моҳи сентябри соли 2018, Microsoft навсозӣ баровард, ки ба Microsoft Excel як абзори комилан нав илова мекунад: Массивҳои динамикӣ ва 7 функсияи нав барои кор бо онҳо. Ин чизҳо, бидуни муболиға, ҳама техникаи маъмулии кор бо формулаҳо ва функсияҳоро ба куллӣ тағир медиҳанд ва ба ҳар як корбар нигаронида шудаанд.
Барои фаҳмондани моҳият як мисоли оддиро дида мебароем.
Фарз мекунем, ки мо ҷадвали оддӣ бо маълумот дар бораи моҳҳои шаҳр дорем. Агар мо ягон ячейкаи холиро дар тарафи рости варақ интихоб кунем ва ба он формулае ворид кунем, ки на ба як ячейка, балки фавран ба диапазон пайваст шавад, чӣ мешавад?
Дар ҳама версияҳои қаблии Excel, пас аз клик кардан даромадан мо мундариҷаи танҳо як ҳуҷайраи B2-ро мегирем. Дигар чӣ тавр?
Хуб, ё метавонист ин диапазонро дар як намуди функсияи ҷамъоварӣ ба монанди =SUM(B2:C4) печонида, барои он маблағи умумӣ ба даст овард.
Агар ба мо нисбат ба маблағи ибтидоӣ амалиёти мураккабтар лозим бошад, ба монанди истихроҷи арзишҳои беназир ё Top 3, мо бояд формулаи худро ҳамчун формулаи массив бо истифода аз миёнабурҳои клавиатура ворид кунем. Ctrl+Shift+даромадан.
Акнун ҳама чиз дигар аст.
Акнун пас аз ворид кардани чунин формула, мо метавонем танҳо клик кунед даромадан - ва дар натиҷа ҳама арзишҳои uXNUMXbuXNUMXb, ки мо ба онҳо ишора кардем, фавран ба даст оред:
Ин ҷоду нест, балки массивҳои нави динамикӣ, ки ҳоло Microsoft Excel дорад. Хуш омадед ба дунёи нав 🙂
Хусусиятҳои кор бо массивҳои динамикӣ
Аз ҷиҳати техникӣ, тамоми массиви динамикии мо дар ячейкаи якуми G4 нигоҳ дошта мешавад ва шумораи зарурии чашмакҳоро ба тарафи рост ва поён бо маълумоти он пур мекунад. Агар шумо ягон чашмаки дигарро дар массив интихоб кунед, он гоҳ истиноди сатри формула ғайрифаъол хоҳад буд ва нишон медиҳад, ки мо дар яке аз ячейкаҳои "кӯдак" ҳастем:
Кӯшиши нест кардани як ё якчанд ҳуҷайраҳои "кӯдак" ба ҳеҷ чиз оварда намерасонад - Excel фавран онҳоро дубора ҳисоб мекунад ва пур мекунад.
Ҳамзамон, мо метавонем ба ин ҳуҷайраҳои "кӯдак" дар формулаҳои дигар истинод кунем:
Агар шумо ячейкаи якуми массивро нусхабардорӣ кунед (масалан, аз G4 то F8), он гоҳ тамоми массив (истинодҳои он) ба ҳамон самт, ки дар формулаҳои муқаррарӣ ҷойгир аст, ҳаракат мекунанд:
Агар ба мо лозим ояд, ки массивро ҳаракат кунем, он гоҳ барои ҳаракат кофӣ хоҳад буд (бо муш ё комбинатсияи Ctrl+X, Ctrl+V), боз, танҳо як ҳуҷайраи асосии G4 - пас аз он, он ба ҷои нав интиқол дода мешавад ва тамоми массиви мо боз васеъ карда мешавад.
Агар ба шумо лозим меояд, ки дар ҷои дигаре дар варақ ба массиви динамикии сохташуда муроҷиат кунед, пас шумо метавонед аломати махсуси # (фунт) -ро пас аз суроғаи ячейкаи пешбари он истифода баред:
Масалан, акнун шумо метавонед ба осонӣ рӯйхати афтандаро дар чашмаке созед, ки ба массиви динамикии сохташуда ишора мекунад:
Хатогиҳои массивҳои динамикӣ
Аммо чӣ мешавад, агар барои васеъ кардани массив фазои кофӣ мавҷуд набошад ё ҳуҷайраҳое мавҷуданд, ки аллакай дар роҳи он маълумотҳои дигар ишғол карда шудаанд? Бо як навъи нави хатогиҳо дар Excel вохӯред - #Трансфер! (#РӮЗ!):
Чун ҳамеша, агар мо нишонаеро бо алмоси зард ва аломати нидо пахш кунем, мо дар бораи манбаи мушкилот шарҳи муфассал хоҳем гирифт ва мо метавонем ҳуҷайраҳои халалдорро зуд пайдо кунем:
Агар массив аз варақ берун равад ё ба чашмаки муттаҳидшуда барад, хатогиҳои шабеҳ рӯй медиҳанд. Агар шумо монеаро бартараф кунед, ҳама чиз фавран дар парвоз ислоҳ карда мешавад.
Массивҳои динамикӣ ва ҷадвалҳои интеллектуалӣ
Агар массиви динамикӣ ба ҷадвали "интеллектуалӣ" ишора кунад, ки тавассути миёнабури клавиатура сохта шудааст Ctrl+T ё аз тарафи Хона - Формат ҳамчун ҷадвал (Хона — Формат ҳамчун ҷадвал), он гоҳ он сифати асосии худ - худкорандозиро низ мерос мегирад.
Ҳангоми илова кардани маълумоти нав ба поён ё ба рост, ҷадвали интеллектуалӣ ва диапазони динамикӣ низ ба таври худкор дароз мешаванд:
Аммо, як маҳдудият вуҷуд дорад: мо наметавонем истинодҳои диапазони динамикиро дар форумулаҳо дар дохили ҷадвали интеллектуалӣ истифода барем:
Массивҳои динамикӣ ва дигар хусусиятҳои Excel
Хуб, шумо мегӯед. Хамаи ин шавковар ва хандаовар аст. Мисли пештара формуларо бо истинод ба чашмаки аввали диапазони аслӣ ба поён ва ба рост ва ҳама чиз дастӣ дароз кардан лозим нест. Ва ин ҳама?
На он қадар.
Массивҳои динамикӣ на танҳо як воситаи дигар дар Excel мебошанд. Ҳоло онҳо дар дили (ё майнаи) Microsoft Excel - муҳаррики ҳисобкунии он ҷойгир шудаанд. Ин маънои онро дорад, ки дигар формулаҳо ва функсияҳои ба мо шиноси Excel низ кор бо массивҳои динамикиро дастгирӣ мекунанд. Биёед якчанд мисолҳоро дида бароем, то ба шумо дар бораи умқи тағйироти ба амаломада тасаввурот диҳед.
Тарроҳ
Барои иваз кардани диапазон (иваз кардани сатрҳо ва сутунҳо) Microsoft Excel ҳамеша функсияи дарунсохт дошт ТРАНСП (ТРАНСПОЗ). Аммо, барои истифодаи он, шумо бояд аввал диапазони натиҷаҳоро дуруст интихоб кунед (масалан, агар вуруд диапазони 5×3 бошад, пас шумо бояд 3×5-ро интихоб карда бошед), пас функсияро ворид кунед ва тугмаро пахш кунед. комбинатсияи Ctrl+Shift+даромадан, зеро он метавонад танҳо дар реҷаи формулаи массив кор кунад.
Акнун шумо метавонед танҳо як чашмакро интихоб кунед, ҳамон формуларо ба он ворид кунед ва муқаррариро клик кунед даромадан - массиви динамикӣ ҳама чизро худаш иҷро мекунад:
Ҷадвали зарб
Ин мисолест, ки ман вақте аз ман хоҳиш карданд, ки манфиатҳои формулаҳои массивро дар Excel тасаввур кунам. Ҳоло барои ҳисоб кардани тамоми ҷадвали Пифагорӣ дар чашмаки аввали B2 истодан кифоя аст, ба он ҷо формулаеро ворид кунед, ки ду массивро (маҷмӯи амудӣ ва уфуқии рақамҳои 1..10) зарб мекунад ва танҳо пахш кунед. даромадан:
Ширкашӣ ва табдили парванда
Массивҳоро на танҳо зиёд кардан мумкин аст, балки бо оператори стандартӣ & (амперсанд) часпонед. Фарз мекунем, ки мо бояд ном ва насабро аз ду сутун ҷудо кунем ва ҳолати ҷаҳишро дар маълумоти аслӣ ислоҳ кунем. Мо инро бо як формулаи кӯтоҳ, ки тамоми массивро ташкил медиҳад, иҷро мекунем ва он гоҳ функсияро ба он татбиқ мекунем ПРОПНАЧ (ДУРУСТ)барои ба тартиб даровардани реестр:
Хулоса Top 3
Фарз мекунем, ки мо як қатор рақамҳо дорем, ки аз онҳо мо мехоҳем се натиҷаи болоро ба даст орем ва онҳоро бо тартиби камшавӣ ҷойгир кунем. Акнун ин бо як формула ва боз бе ягон формула анҷом дода мешавад Ctrl+Shift+даромадан мисли пештара:
Агар шумо хоҳед, ки натиҷаҳо на дар сутун, балки дар як саф ҷойгир карда шаванд, пас иваз кардани қафаҳои (ҷудокунандаи сатр) дар ин формула бо нуқта-вергул (ҷудокунандаи элементҳо дар як сатр) кифоя аст. Дар версияи англисии Excel ин ҷудокунакҳо мутаносибан нуқта ва вергул мебошанд.
VLOOKUP дар як вақт якчанд сутунҳоро истихроҷ мекунад
Вазифаҳо VPR (ИНТИЗОР) акнун шумо метавонед арзишҳоро на аз як, балки аз якчанд сутун якбора кашед - танҳо рақамҳои онҳоро (бо ҳар тартиби дилхоҳ) ҳамчун массив дар аргументи сеюми функсия нишон диҳед:
Функсияи OFFSET массиви динамикиро бармегардонад
Яке аз функсияҳои ҷолибтарин ва муфид (пас аз VLOOKUP) барои таҳлили додаҳо ин функсия мебошад ИСТИФОДА (ОФСЕТ), ки ман дар як вакт ба он як боби томи китоби худ ва як маколаро дар ин чо бахшида будам. Мушкилоти дарк ва азхудкунии ин функсия ҳамеша аз он иборат буд, ки дар натиҷа массив (диапазони) маълумотро баргардонд, аммо мо онро дида натавонистем, зеро Excel то ҳол намедонист, ки чӣ тавр бо массивҳо берун аз қуттӣ кор кунад.
Ҳоло ин мушкилот дар гузашта аст. Бингар, ки чӣ тавр ҳоло бо истифода аз формулаи ягона ва массиви динамикии аз ҷониби OFFSET баргардонидашуда, шумо метавонед ҳамаи сатрҳоро барои маҳсулоти додашуда аз ҳар ҷадвали ҷудошуда истихроҷ кунед:
Биёед ба далелҳои вай назар андозем:
- A1 - ячейкаи ибтидоӣ (нуқтаи истинод)
- ПОИСКПОЗ(F2;A2:A30;0) – њисоб кардани гузариш аз ячейкаи ибтидої ба поён – то карами аввалини ёфтшуда.
- 0 – кӯчонидани «тиреза» ба рост нисбат ба чашмаки ибтидоӣ
- СЧЁТЕСЛИ(A2:A30;F2) – њисоб кардани баландии «тиреза»-и баргардонидашуда – шумораи сатрњое, ки дар он карам мављуд аст.
- 4 — андозаи «тиреза»-ро ба таври уфукй, яъне 4 сутун мебарорад
Функсияҳои нав барои массивҳои динамикӣ
Илова ба дастгирии механизми массивҳои динамикӣ дар функсияҳои кӯҳна, ба Microsoft Excel якчанд функсияҳои комилан нав илова карда шуданд, ки махсус барои кор бо массивҳои динамикӣ тезонида шудаанд. Аз ҷумла, инҳоянд:
- синфи (ЧУНИН) – диапазони вурудро ба навъҳо ҷудо мекунад ва массиви динамикиро дар баромад тавлид мекунад
- SORTPO (ҶУДО КАРДАН БА) - метавонад як диапазонро аз рӯи арзишҳо аз дигараш ҷудо кунад
- филтр (ФИЛТР) – сатрҳоро аз диапазони манбаъ, ки ба шартҳои муқарраршуда мувофиқанд, бармегардонад
- УНИК (БЕКОР) - арзишҳои беназирро аз диапазон хориҷ мекунад ё такрорҳоро нест мекунад
- SLMASSIVE (RANDARRAY) – массиви ададҳои тасодуфии андозаи додашударо тавлид мекунад
- БАЪДИ ТАВАЛЛУД (пайда) — массивро аз пайдарпаии рақамҳо бо қадами додашуда ташкил медиҳад
Бештар дар бораи онҳо - каме дертар. Онҳо ба маблағи як мақолаи алоҳида (ва на як) барои омӯзиши оқилона мебошанд 🙂
Хулоса
Агар шумо ҳама чизеро, ки дар боло навишта шудаанд, хонда бошед, пас ба фикрам, шумо миқёси дигаргуниҳои ба амаломадаро аллакай дарк мекунед. Ҳоло бисёр чизҳоро дар Excel метавон осонтар, осонтар ва мантиқӣ иҷро кард. Ман бояд иқрор шавам, ки ман каме ҳайронам, ки ҳоло чӣ қадар мақолаҳоро дар ин ҷо, дар ин сайт ва китобҳои ман ислоҳ кардан лозим аст, аммо ман омодаам бо дили сабук ин корро анҷом диҳам.
Натиҷаҳоро ҷамъбаст намуда, плюс массивҳои динамикӣ, шумо метавонед инҳоро нависед:
- Шумо метавонед дар бораи комбинатсияи фаромӯш Ctrl+Shift+даромадан. Excel ҳоло байни "формулаҳои муқаррарӣ" ва "формулаҳои массив" ҳеҷ фарқияте намебинад ва ба онҳо ҳамин тавр муносибат мекунад.
- Дар бораи функсия SUMPRODUCT (МАХСУЛОТ), ки қаблан барои ворид кардани формулаҳои массив бе Ctrl+Shift+даромадан шумо инчунин метавонед фаромӯш кунед - ҳоло он кофӣ осон аст СУМ и даромадан.
- Ҷадвалҳои интеллектуалӣ ва функсияҳои шинос (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
- 3 Роҳҳои интиқоли ҷадвал дар Excel