Ексел лайфхакове за тези, които се занимават с отчитане и обработка на данни

2024 Автор: Malcolm Clapton | [email protected]. Последно модифициран: 2024-01-13 00:44

В тази публикация Ренат Шагабутдинов, помощник генерален директор на издателство Mann, Ivanov and Ferber, споделя някои страхотни лайфхакове на Excel. Тези съвети ще бъдат полезни за всеки, който се занимава с различни отчети, обработка на данни и създаване на презентации.

Тази статия съдържа прости техники за опростяване на работата ви в Excel. Те са особено полезни за тези, които се занимават с управленско отчитане, изготвят различни аналитични отчети въз основа на изтегляния от 1С и други отчети, формират презентации и диаграми от тях за управление. Не се преструвам на абсолютна новост - в една или друга форма тези техники вероятно са били обсъждани във форумите или споменати в статии.

Прости алтернативи на VLOOKUP и HLOOKUP, ако желаните стойности не са в първата колона на таблицата: LOOKUP, INDEX + SEARCH

Функциите VLOOKUP и HLOOKUP работят само ако желаните стойности са в първата колона или ред на таблицата, от която планирате да получите данни.

В противен случай има два варианта:

1. Използвайте функцията LOOKUP.

   Той има следния синтаксис: LOOKUP (търсенна_стойност; lookup_vector; резултат_вектор). Но за да работи правилно, стойностите на диапазона view_vector трябва да бъдат сортирани във възходящ ред:

   

2. Използвайте комбинация от функции MATCH и INDEX.

   Функцията MATCH връща поредния номер на елемент в масива (с негова помощ можете да разберете в кой ред от таблицата се намира търсеният елемент), а функцията INDEX връща елемент от масива с даден номер (който ще разберем използвайки функцията MATCH).

   

   Синтаксис на функцията:

   • SEARCH (search_value; search_array; match_type) – за нашия случай се нуждаем от тип на съвпадение „точно съвпадение“, той отговаря на числото 0.

   • ИНДЕКС (масив; номер_ред; [номер_колона]). В този случай не е необходимо да посочвате номера на колоната, тъй като масивът се състои от един ред.

Как бързо да попълните празни клетки в списък

Задачата е да попълните клетките в колоната със стойностите в горната част (така че темата да е във всеки ред на таблицата, а не само в първия ред на блока от книги по темата):

Изберете колоната „Тема“, щракнете върху лентата в групата „Начало“, бутона „Намери и изберете“→ „Изберете група клетки“→ „Празни клетки“и започнете да въвеждате формулата (тоест поставете равно знак) и се обърнете към клетката в горната част, просто като щракнете със стрелка нагоре на клавиатурата. След това натиснете Ctrl + Enter. След това можете да запазите получените данни като стойности, тъй като формулите вече не са необходими:

Как да намерите грешки във формула

Изчисляване на отделна част от формула

За да разберете сложна формула (в която други функции се използват като аргументи на функцията, тоест някои функции са вложени в други) или за да намерите източника на грешки в нея, често трябва да изчислите част от нея. Има два лесни начина:

1. За да изчислите част от формула точно в лентата с формули, изберете тази част и натиснете F9:

   

   В този пример имаше проблем с функцията SEARCH - аргументите бяха разменени в нея. Важно е да запомните, че ако не отмените изчислението на частта от функцията и натиснете Enter, тогава изчислената част ще остане число.

2. Щракнете върху бутона Изчисляване на формула в групата Формули на лентата:

   

   В прозореца, който се показва, можете да изчислите формулата стъпка по стъпка и да определите на кой етап и в коя функция възниква грешка (ако има такава):

   

Как да определим от какво зависи или към какво се отнася една формула

За да определите от кои клетки зависи формула, в групата Формули на лентата щракнете върху бутона Засегнати клетки:

Появяват се стрелки, които показват от какво зависи резултатът от изчислението.

Ако символът, подчертан на снимката в червено, се покаже, тогава формулата зависи от клетките на други листове или в други книги:

Като кликнете върху него, можем да видим къде точно се намират влияещите клетки или диапазони:

До бутона "Влияние на клетки" е бутонът "Зависими клетки", който работи по същия начин: показва стрелки от активната клетка с формула към клетките, които зависят от нея.

Бутонът „Премахване на стрелките“, разположен в същия блок, ви позволява да премахвате стрелки към влияещи клетки, стрелки към зависими клетки или и двата вида стрелки наведнъж:

Как да намерите сумата (брой, средна стойност) на стойностите на клетките от множество листа

Да приемем, че имате няколко листа от един и същи тип с данни, които искате да добавите, преброите или обработите по някакъв друг начин:

За да направите това, в клетката, в която искате да видите резултата, въведете стандартна формула, например SUM (SUM) и посочете името на първия и последния лист от списъка с тези листове, които трябва да обработите в аргументът, разделен с двоеточие:

Ще получите сбора от клетки с адрес B3 от листовете "Data1", "Data2", "Data3":

Това адресиране работи за разположени листове последователно … Синтаксисът е както следва: = FUNCTION (първи_списък: последен_списък! Референтен диапазон).

Как автоматично да създавате шаблонни фрази

Използвайки основните принципи за работа с текст в Excel и няколко прости функции, можете да подготвите шаблонни фрази за отчети. Няколко принципа за работа с текст:

• Ние свързваме текста с помощта на знака & (можете да го замените с функцията CONCATENATE, но това няма много смисъл).
• Текстът винаги се изписва в кавички, препратките към клетки с текст винаги са без.
• За да получите служебния символ "кавички", използвайте функцията CHAR с аргумент 32.

Пример за създаване на шаблонна фраза с помощта на формули:

Резултат:

В този случай, в допълнение към функцията CHAR (за показване на кавички), се използва функцията IF, която ви позволява да променяте текста в зависимост от това дали има положителна тенденция на продажбите, и функцията TEXT, която ви позволява да показвате номер във всякакъв формат. Синтаксисът му е описан по-долу:

ТЕКСТ (стойност; формат)

Форматът е посочен в кавички, точно както ако въвеждате персонализиран формат в прозореца Форматиране на клетки.

По-сложните текстове също могат да бъдат автоматизирани. В моята практика имаше автоматизиране на дълги, но рутинни коментари към управленските отчети във формат „ИНДИКАТОР спадна/повиши с XX спрямо плана, главно поради ръст/спад на FACTOR1 с XX, ръст/спад на FACTOR2 от YY …” с променящ се списък от фактори. Ако пишете такива коментари често и процесът на писането им може да бъде алгоритмизиран, си струва веднъж да озадачите да създадете формула или макрос, който ще ви спести поне част от работата.

Как да съхранявате данни във всяка клетка след конкатенация

Когато обедините клетки, се запазва само една стойност. Excel предупреждава за това, когато се опитва да обедини клетки:

Съответно, ако сте имали формула в зависимост от всяка клетка, тя ще спре да работи след комбинирането им (# N / A грешка в редове 3-4 от примера):

За да обедините клетки и да запазите данните във всяка от тях (може би имате формула като в този абстрактен пример; може би искате да обедините клетки, но да запазите всички данни за бъдещето или да ги скриете умишлено), обединете всички клетки в листа, изберете ги и след това използвайте командата Format Painter, за да прехвърлите форматирането към клетките, които трябва да комбинирате:

Как да изградите опорна точка от множество източници на данни

Ако трябва да изградите опорна точка от няколко източника на данни наведнъж, ще трябва да добавите „Помощник за обобщена таблица и диаграма“към лентата или панела за бърз достъп, който има такава опция.

Можете да направите това по следния начин: „Файл“→ „Опции“→ „Лента с инструменти за бърз достъп“→ „Всички команди“→ „Съветник за обобщена таблица и диаграма“→ „Добавяне“:

След това на лентата ще се появи съответната икона, щракването върху която извиква същия съветник:

Когато кликнете върху него, се появява диалогов прозорец:

В него трябва да изберете елемента „В няколко диапазона на консолидация“и да кликнете върху „Напред“. В следващата стъпка можете да изберете „Създаване на поле за една страница“или „Създаване на полета за страница“. Ако искате самостоятелно да измислите име за всеки от източниците на данни, изберете втория елемент:

В следващия прозорец добавете всички диапазони, въз основа на които ще бъде изградена опорната точка, и им дайте имена:

След това, в последния диалогов прозорец, посочете къде ще бъде поставен отчетът на централната таблица - на съществуващ или нов лист:

Отчетът на обобщената таблица е готов. Във филтъра „Страница 1“можете да изберете само един от източниците на данни, ако е необходимо:

Как да изчислим броя на появяванията на текст A в текст B („MTS SuperMTS тарифа“- две поява на абревиатурата MTS)

В този пример колона A съдържа няколко текстови реда и нашата задача е да разберем колко пъти всеки от тях съдържа текста за търсене, разположен в клетка E1:

За да разрешите този проблем, можете да използвате сложна формула, състояща се от следните функции:

1. DLSTR (LEN) - изчислява дължината на текста, единственият аргумент е текстът. Пример: DLSTR („машина“) = 6.
2. SUBSTITUTE - заменя конкретен текст в текстов низ с друг. Синтаксис: SUBSTITUTE (текст; стар_текст; нов_текст). Пример: SUBSTITUTE (“автомобил”; “автоматичен”; “”) = “мобилен”.
3. UPPER - замества всички знаци в низ с главни букви. Единственият аргумент е текстът. Пример: UPPER (“машина”) = “CAR”. Нуждаем се от тази функция, за да правим търсене без значение на главни и малки букви. В крайна сметка UPPER ("кола") = UPPER ("Машина")

За да намерите появата на определен текстов низ в друг, трябва да изтриете всички негови срещания в оригиналния и да сравните дължината на получения низ с оригиналния:

DLSTR („Тарифа MTS Super MTS“) - DLSTR („Тарифа Super“) = 6

И след това разделете тази разлика на дължината на низа, който търсихме:

6 / DLSTR („MTS“) = 2

Точно два пъти линията "MTS" е включена в оригиналната.

Остава да напишем този алгоритъм на езика на формулите (нека да обозначим с „текст“текста, в който търсим събития, а с „търсен“- този, чийто брой събития ни интересува):

= (DLSTR (текст) -LSTR (ЗАМЕНА (ГОРНА (текст); UPPER (търсене), ""))) / DLSTR (търсене)

В нашия пример формулата изглежда така:

= (DLSTR (A2) -LSTR (ЗАМЕНА (ГОРНА (A2), ГОРНА ($ E $ 1), “”)) / DLSTR ($ E $ 1)