Съдържание
Какво представляват динамичните масиви
През септември 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 също поддържат работа с динамични масиви. Нека да разгледаме няколко примера, за да ви дадем представа за дълбочината на промените, които са настъпили.
Транспониране
За транспониране на диапазон (размяна на редове и колони) Microsoft Excel винаги е имал вградена функция ТРАНСПОРТ (ТРАНСПОНИРАНЕ). За да я използвате обаче, първо трябва да изберете правилно диапазона за резултатите (например, ако входът е диапазон от 5×3, тогава трябва да сте избрали 3×5), след това да влезете във функцията и да натиснете комбинация Ctrl+Превключване+Въведете, защото може да работи само в режим на формула за масив.
Сега можете просто да изберете една клетка, да въведете същата формула в нея и да щракнете върху нормалното Въведете – динамичният масив ще направи всичко сам:
Таблица за умножение
Това е примерът, който дадох, когато бях помолен да визуализирам предимствата на формулите за масиви в Excel. Сега, за да изчислите цялата таблица на Питагор, достатъчно е да застанете в първата клетка B2, да въведете там формула, която умножава два масива (вертикален и хоризонтален набор от числа 1..10) и просто да кликнете върху Въведете:
Лепене и преработка на каси
Масивите могат не само да се умножават, но и да се слепват със стандартния оператор & (амперсанд). Да предположим, че трябва да извлечем първото и фамилното име от две колони и да коригираме малкия регистър в оригиналните данни. Правим това с една кратка формула, която формира целия масив, и след това прилагаме функцията към него ПРОПНАЧ (ПРАВИЛНО)за подреждане на регистъра:
Заключение Топ 3
Да предположим, че имаме куп числа, от които искаме да извлечем първите три резултата, като ги подредим в низходящ ред. Сега това става по една формула и отново без никаква Ctrl+Превключване+Въведете Както преди:
Ако искате резултатите да бъдат поставени не в колона, а в ред, тогава е достатъчно да замените двоеточията (разделител на ред) в тази формула с точка и запетая (разделител на елементи в рамките на един ред). В английската версия на Excel тези разделители са съответно точка и запетая.
VLOOKUP извлича няколко колони наведнъж
Функции ВПР (VLOOKUP) сега можете да изтегляте стойности не от една, а от няколко колони наведнъж - просто посочете техните номера (в произволен ред) като масив в третия аргумент на функцията:
Функция OFFSET, връщаща динамичен масив
Една от най-интересните и полезни (след VLOOKUP) функции за анализ на данни е функцията ИЗХВЪРЛЯНЕ (ИЗМЕСТВАНЕ), на което навремето посветих цяла глава в книгата си и статия тук. Трудността при разбирането и овладяването на тази функция винаги е била, че тя връща масив (обхват) от данни като резултат, но не можехме да го видим, защото Excel все още не знаеше как да работи с масиви от кутията.
Сега този проблем е в миналото. Вижте как сега, използвайки една формула и динамичен масив, върнат от OFFSET, можете да извлечете всички редове за даден продукт от всяка сортирана таблица:
Нека да разгледаме нейните аргументи:
- A1 – начална клетка (референтна точка)
- ПОИСКПОЗ(F2;A2:A30;0) – изчисляване на преместването от началната клетка надолу – до първата намерена зелка.
- 0 – изместване на „прозореца“ надясно спрямо началната клетка
- СЧЁТЕСЛИ(A2:A30;F2) – изчисляване на височината на върнатия „прозорец“ – броя на редовете, на които има зеле.
- 4 — размер на „прозореца“ хоризонтално, т.е. изведете 4 колони
Нови функции за динамични масиви
В допълнение към поддържането на механизма за динамични масиви в стари функции, няколко напълно нови функции бяха добавени към Microsoft Excel, изострени специално за работа с динамични масиви. По-специално това са:
- КЛАС (ВИД) – сортира входния диапазон и създава динамичен масив на изхода
- СОРТПО (СОРТИРАНЕ) – може да сортира един диапазон по стойности от друг
- FILTER (ФИЛТРИРАНЕ) – извлича редове от изходния диапазон, които отговарят на зададените условия
- УНИК (ЕДИНСТВЕН ПО РОДА СИ) – извлича уникални стойности от диапазон или премахва дубликати
- SLMASSIVE (RANDARRAY) – генерира масив от произволни числа с даден размер
- ПОСЛЕДНО РАЖДАНЕ (ПОСЛЕДОВАТЕЛНОСТ) — образува масив от поредица от числа със зададена стъпка
Повече за тях – малко по-късно. Те си заслужават отделна статия (а не една) за внимателно проучване 🙂
Заключения
Ако сте прочели всичко написано по-горе, тогава мисля, че вече осъзнавате мащаба на настъпилите промени. Толкова много неща в Excel вече могат да се правят по-лесно, по-лесно и по-логично. Трябва да призная, че съм малко шокиран колко много статии сега ще трябва да бъдат коригирани тук, на този сайт и в моите книги, но съм готов да направя това с леко сърце.
Обобщавайки резултатите, плюсы динамични масиви, можете да напишете следното:
- Можете да забравите за комбинацията Ctrl+Превключване+Въведете. Excel вече не вижда разлика между „обикновени формули“ и „формули за масиви“ и ги третира по същия начин.
- Относно функцията SUMPRODUCT (СУМА ПРОИЗВОД), който преди се използваше за въвеждане на формули за масиви без Ctrl+Превключване+Въведете можете също да забравите – сега е достатъчно лесно SUM и Въведете.
- Интелигентните таблици и познатите функции (SUM, IF, VLOOKUP, SUMIFS и др.) сега също така напълно или частично поддържат динамични масиви.
- Има обратна съвместимост: ако отворите работна книга с динамични масиви в стара версия на Excel, те ще се превърнат във формули за масиви (във фигурни скоби) и ще продължат да работят в „стария стил“.
Намерих някакъв номер минуси:
- Не можете да изтриете отделни редове, колони или клетки от динамичен масив, т.е. той живее като едно цяло.
- Не можете да сортирате динамичен масив по обичайния начин Данни – Сортиране (Данни — Сортиране). Вече има специална функция за това. КЛАС (ВИД).
- Динамичен диапазон не може да се превърне в интелигентна таблица (но можете да направите динамичен диапазон въз основа на интелигентна таблица).
Разбира се, това не е краят и съм сигурен, че Microsoft ще продължи да подобрява този механизъм в бъдеще.
Къде мога да изтегля?
И накрая, основният въпрос 🙂
Microsoft за първи път обяви и показа визуализация на динамични масиви в Excel през септември 2018 г. на конференция Ignite. През следващите няколко месеца имаше задълбочено тестване и въвеждане на нови функции, първо котки служители на самия Microsoft, а след това и на доброволни тестери от кръга на 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