содржина
Што се динамички низи
Во септември 2018 година, Мајкрософт објави ажурирање што додава целосно нова алатка во Microsoft Excel: Динамички низи и 7 нови функции за работа со нив. Овие работи, без претерување, радикално ја менуваат сета вообичаена техника на работа со формули и функции и го загрижуваат, буквално, секој корисник.
Размислете за едноставен пример за да ја објасните суштината.
Да претпоставиме дека имаме едноставна табела со податоци за градови-месеци. Што ќе се случи ако избереме која било празна ќелија десно од листот и внесеме формула што не се поврзува со една ќелија, туку веднаш до опсег?
Во сите претходни верзии на Excel, откако ќе кликнете на Внесете би ја добиле содржината на само една прва ќелија Б2. Како поинаку?
Па, или би било можно да се завитка овој опсег во некој вид на агрегирана функција како =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 кои ни се познати сега поддржуваат работа со динамички низи. Ајде да погледнеме неколку примери за да ви дадеме идеја за длабочината на промените што се случиле.
Транспонирај
За транспонирање опсег (замена на редови и колони) Microsoft Excel отсекогаш имал вградена функција ТРАНСП (ТРАНСПОЗИРАЈ). Меѓутоа, за да го користите, прво мора правилно да го изберете опсегот за резултатите (на пример, ако влезот бил опсег од 5×3, тогаш мора да сте избрале 3×5), потоа внесете ја функцијата и притиснете го комбинација Ctrl+Префрлат+Внесете, бидејќи можеше да работи само во режим на формула со низа.
Сега можете само да изберете една ќелија, да ја внесете истата формула во неа и да кликнете на нормалната Внесете – динамичката низа ќе направи сè сама по себе:
Табела за множење
Ова е примерот што го дадов кога ми беше побарано да ги визуелизирам придобивките од формулите за низи во Excel. Сега, за да ја пресметате целата питагорова табела, доволно е да застанете во првата ќелија B2, внесете формула која множи две низи (вертикално и хоризонтално множество од броеви 1..10) и едноставно кликнете на Внесете:
Лепење и конверзија на куќиште
Низите не само што можат да се множат, туку и да се залепат заедно со стандардниот оператор & (амперанд). Да претпоставиме дека треба да го извлечеме името и презимето од две колони и да го поправиме скокачкиот случај во оригиналните податоци. Ова го правиме со една кратка формула која ја формира целата низа, а потоа ја применуваме функцијата на неа ПРОПНАХ (ПРАВИЛНИ)да го среди регистарот:
Заклучок Топ 3
Да претпоставиме дека имаме куп броеви од кои сакаме да ги изведеме првите три резултати, подредувајќи ги по опаѓачки редослед. Сега ова се прави со една формула и, повторно, без никаква Ctrl+Префрлат+Внесете како порано:
Ако сакате резултатите да не се ставаат во колона, туку во ред, тогаш доволно е да ги замените запирките (раздвојувач на линии) во оваа формула со точка-запирка (сепаратор на елементи во една линија). Во англиската верзија на Excel, овие сепаратори се запирки и запирки, соодветно.
VLOOKUP извлекува повеќе колони одеднаш
Функции VPR (VLOOKUP) сега можете да повлечете вредности не од една, туку од неколку колони одеднаш - само наведете ги нивните броеви (по кој било посакуван редослед) како низа во третиот аргумент на функцијата:
Функцијата OFFSET враќа динамична низа
Една од најинтересните и најкорисните (по VLOOKUP) функции за анализа на податоци е функцијата РАБОТУВАЕ (ОФСЕТ), на што му посветив едно време цело поглавје од мојата книга и една статија овде. Тешкотијата во разбирањето и совладувањето на оваа функција отсекогаш била тоа што како резултат враќа низа (опсег) на податоци, но не можевме да ги видиме, бидејќи Excel сè уште не знаеше како да работи со низи надвор од кутијата.
Сега овој проблем е во минатото. Погледнете како сега, користејќи една формула и динамична низа вратени со OFFSET, можете да ги извлечете сите редови за даден производ од која било сортирана табела:
Да ги погледнеме нејзините аргументи:
- A1 – почетна ќелија (референтна точка)
- ПОИСКПОЗ(F2;A2:A30;0) – пресметка на поместувањето од почетната ќелија надолу – до првата пронајдена зелка.
- 0 – поместување на „прозорецот“ надесно во однос на почетната ќелија
- СЧЁТЕСЛИ(A2:A30;F2) – пресметка на висината на вратениот „прозорец“ – број на линии каде има зелка.
- 4 — големината на „прозорецот“ хоризонтално, т.е. излези 4 колони
Нови функции за динамички низи
Покрај поддршката на механизмот за динамична низа во старите функции, неколку сосема нови функции се додадени во Microsoft Excel, изострени специјално за работа со динамички низи. Конкретно, тоа се:
- GRADE (СОРТИРАЈ) – го подредува влезниот опсег и произведува динамична низа на излезот
- SORTPO (СОРТИРАЈ ПО) - може да сортира еден опсег по вредности од друг
- ФИЛТЕРОТ (ФИЛТЕР) – презема редови од изворниот опсег што ги исполнуваат наведените услови
- УНИК (ЕДИНСТВЕНИ) - извлекува уникатни вредности од опсег или отстранува дупликати
- МАСИВНО (РАНДАРЕН) – генерира низа од случајни броеви со дадена големина
- ПОРОДЕЊЕ (РЕСЕЛА) — формира низа од низа броеви со даден чекор
Повеќе за нив - малку подоцна. Тие вредат посебна статија (а не една) за внимателна студија 🙂
Заклучоци
Ако сте прочитале сè што е напишано погоре, тогаш мислам дека веќе го сфаќате обемот на промените што се случија. Толку многу работи во Excel сега може да се направат полесно, полесно и пологично. Морам да признаам дека сум малку шокиран од тоа колку статии сега ќе треба да се поправат овде, на оваа страница и во моите книги, но јас сум подготвен да го сторам тоа со лесно срце.
Сумирајќи ги резултатите, плус динамички низи, можете да го напишете следново:
- Можете да заборавите на комбинацијата Ctrl+Префрлат+Внесете. Excel сега не гледа разлика помеѓу „обични формули“ и „формули на низа“ и ги третира на ист начин.
- За функцијата СУМПРОДУКТ (SumProduct), кој претходно се користеше за внесување формули на низи без Ctrl+Префрлат+Внесете можете и да заборавите – сега е доволно лесно SUM и Внесете.
- Паметните табели и познатите функции (SUM, IF, VLOOKUP, SUMIFS итн.) сега исто така целосно или делумно поддржуваат динамички низи.
- Постои компатибилност наназад: ако отворите работна книга со динамични низи во стара верзија на Excel, тие ќе се претворат во формули за низи (во кадрави загради) и ќе продолжат да работат во „стариот стил“.
Најдов некој број минуси:
- Не можете да избришете поединечни редови, колони или ќелии од динамична низа, т.е. живее како единствен ентитет.
- Не можете да сортирате динамична низа на вообичаен начин Податоци – Сортирање (Податоци - Подреди). Сега постои посебна функција за ова. GRADE (СОРТИРАЈ).
- Динамичен опсег не може да се претвори во паметна табела (но можете да направите динамичен опсег врз основа на паметна табела).
Се разбира, ова не е крајот и сигурен сум дека Microsoft ќе продолжи да го подобрува овој механизам во иднина.
Каде можам да преземам?
И конечно, главното прашање 🙂
Мајкрософт првпат објави и прикажа преглед на динамички низи во Excel уште во септември 2018 година на конференција Запалам. Во следните неколку месеци, имаше темелно тестирање и вклучување на нови функции, најпрво мачки вработени во самиот Мајкрософт, а потоа и на тестери волонтери од кругот на Office Insiders. Оваа година, ажурирањето што додава динамични низи почна постепено да се пренесува на редовните претплатници на Office 365. На пример, го добив само во август со мојата претплата за Office 365 Pro Plus (месечна целна).
Ако вашиот Excel сè уште нема динамични низи, но навистина сакате да работите со нив, тогаш постојат следниве опции:
- Ако имате претплата за Office 365, можете едноставно да почекате додека ова ажурирање не стигне до вас. Колку брзо ќе се случи ова зависи од тоа колку често се доставуваат ажурирањата до вашата канцеларија (еднаш годишно, еднаш на секои шест месеци, еднаш месечно). Ако имате корпоративен компјутер, можете да побарате од вашиот администратор да постави ажурирања за да се преземаат почесто.
- Може да се приклучите на редот на оние волонтери за тестирање на Office Insiders - тогаш ќе бидете први што ќе ги добиете сите нови функции и функции (но, се разбира, постои можност за зголемено багирање во Excel).
- Ако немате претплата, туку самостојна верзија на Excel, тогаш ќе мора да почекате до објавувањето на следната верзија на Office и Excel во 2022 година, барем. Корисниците на таквите верзии добиваат само безбедносни ажурирања и поправени грешки, а сите нови „добри“ сега одат само за претплатниците на Office 365. Жално но вистинито 🙂
Во секој случај, кога ќе се појават динамички низи во вашиот Excel - по овој напис, ќе бидете подготвени за тоа 🙂
- Што се формули за низи и како да ги користите во Excel
- Збир на прозорец (опсег) со помош на функцијата OFFSET
- 3 начини за транспонирање табела во Excel