Искате да направите обобщение по име в Excel, така че да се обновява автоматично при промяна на данните? Тази статия ще ви покаже как да го направите с помощта на динамични формули – без Pivot таблици, без ръчно влачене на обхвати, без сложни макроси. Ще разгледаме както класическия подход със SUMIFS, така и по-модерни и мощни комбинации с LET, MAP и HSTACK. Примерът ни е прост – лицеви опори по дни – но подходът работи при всякакви данни: от продажби и разходи до билети и грешки в системата.
Примерна таблица
Имаме следната таблица:
– Колона A: Name (име на човека)
– Колона B: Day (ден от седмицата)
– Колона C: Push-ups (брой лицеви опори)
Таблицата се намира в областта A3:C21, като ред 3 съдържа заглавията на колоните. Целта ни е да изчислим общия брой за всеки човек. Освен това искаме този списък да се обновява автоматично при добавяне, изтриване или промяна на стойности в списъка. За да постигнем това, удачно е да използваме т.нар. динамични формули в Excel.
Как да направим списък с уникални имена
За да можем да обобщим резултатите на всеки човек първото нещо е да направим списък с уникалните имена на хората. В него всяко име трябва да се среща само по веднъж. Тук може да видите цели 14 начина да направите списък с уникални стойности. В случая можем да използваме формулата:
=FILTER(UNIQUE(A4:A125), UNIQUE(A4:A125)<>0)
Тази формула връща списък с уникални имена, като премахва празните клетки. Областта A4:A125 е по-голяма от реалната, за да позволи бъдещо добавяне на стойности по редовете. Ако е необходимо, можем да използваме и област с 20,000 реда – това не променя логиката на формулата. Но има едно неудобство – празните клетки се възприемат като 0 (нула) от UNIQUE. Ако използваме само = UNIQUE(A4:A125), това ще добави една нула в края на списъка с имената. Затова се използва FILTER, за да се махне тази нула.
Внимание!
Важно е имената да са наистина уникални – ако има две лица с името Ivan, в уникалния списък ще имаме само едно име Ivan. Така ще получим само един сбор за двамата. Накратко – справката ще бъде грешна. Затова е добре да използваме пълно име, ЕГН или друг уникален идентификатор.
Простото решение със SUMIFS
След като имаме списъка с уникални имена, можем да използваме стандартен SUMIFS в съседната клетка, за да сумираме:
=SUMIFS(C$4:C$125, A$4:A$125, E4#)
Формулата се въвежда в една клетка и връща масив със суми – по една за всяко име. Обърнете внимание на E4# – това е една новост в Excel. По този начин се означава, че формулата използва динамичен диапазон, който започва от клетка Е4 и се разширява или свива автоматично.
Предимства на SUMIFS с динамичен диапазон
Този метод е ясен, лесен за разбиране и достатъчен за повечето нужди. Работи с класически SUMIFS и дава гъвкавост при добавяне и изтриване на редове. Това решение е подходящо за потребители на Excel с Офис 365, които още не са напреднали с динамичните области. То следва стандартната логика на създаване на формули – една формула за едно нещо в една колона. Списъкът с имената се получава с формула в клетка Е4 и сам се смалява и увеличава при промяна на имената. До него в клетка F4 е SUMIF, който събира броя лицеви опори за всяко име.
По-мощно решение с LET, MAP и HSTACK
Новите динамични функции в Excel ни дават възможност да направим обобщената таблица с една единствена формула.
=LET(
u, UNIQUE(FILTER(A4:A125, A4:A125<>““)),
nums, MAP(u, LAMBDA(x, SUMIFS(C4:C125, A4:A125, x))),
HSTACK(u, nums)
)
Ето я и на картинка:
Ето какво прави всяка част:
– FILTER премахва празните клетки
– UNIQUE извлича уникални стойности
– MAP с LAMBDA изчислява суми за всяко име
– HSTACK подрежда имената и сумите в таблица
– LET позволява по-четима структура. – с нея създаваме параметрите u и nums, които са резултат от дълги формули. Така вместо да използваме по-нататък в HSTACK дългите формули използваме само u и nums.
Какво научихме
С помощта на няколко нови Excel функции можете да изградите обобщение по име, което е:
– динамично и самоподдържащо се
– без нужда от Pivot таблици
– подходящо за всякакъв тип групиране на данни
Методът работи еднакво добре за отчети по човек, по проект, по продукт и др.
Заключение
Независимо дали сте фен на класическите формули в Excel или обичате да използвате най-новите му възможности, динамичното обобщение по име ще ви спести време и усилия.
Видяхме как с комбинация от UNIQUE, FILTER, SUMIFS, MAP и LET можем да постигнем автоматично обновяване на данните, поддържано само от формули. Без допълнителни инструменти. Без Pivot таблици.
Приложете този подход в собствените си файлове и адаптирайте структурата според нуждите си. Ако откриете по-елегантно решение – ще се радвам да го споделите!