Renat не е първият автор на гости на Lifehacker. По-рано, ние публикувахме отличен материал от него за това как да се създаде план за обучение: основни книги и онлайн ресурси, както и стъпка по стъпка алгоритъм за създаване на план за обучение.
Тази статия съдържа прости техники, които улесняват работата в Excel. Те са особено полезни за тези, които участват в управлението на отчитане, подготвя различни аналитични доклади въз основа на разтоварванията на 1C и други доклади, форми на тях за презентации за управление и диаграми. Аз не твърдя за абсолютна новост – в една или друга форма тези техники със сигурност са били дискутирани на форуми или споменати в статии.
Прости алтернативи на VLOOK и GPR, ако търсените стойности не са в първата колона на таблицата: VIEW, INDEX + MATCH
Функциите VLOOKUP и HLOOKUP работят само ако стойностите за търсене са в първата колона или ред от таблицата, от която планирате да получавате данни.
В други случаи има две възможности:
- Използвайте функцията LOOKUP.
Той има следния синтаксис: VIEW (търсена_стойност Търсене вектор; резултатен вектор). Но за правилната му работа е необходимо стойностите на диапазона Търсене вектор бяха сортирани във възходящ ред:
- Използвайте комбинацията от функциите MATCH и INDEX.
MATCH функция връща поредния номер на елемента в масива (с негова помощ можете да намерите желаната позиция в таблицата в един ред) и INDEX връща елемента на масива с предварително определен брой (което научаваме чрез MATCH).
Функционален синтаксис:
• СЪОТВЕТСТВИЕ (търсена_стойност масив за търсене; match_type) – за нашия случай се нуждаем от тип съвпадение “точно съвпадение”, то съответства на числото 0.
• ИНДЕКС (решетка; ред номер; [COLUMN_NUM]). В този случай номерът на колоната не трябва да се посочва, тъй като масивът се състои от един ред.
Как бързо да попълните празните клетки в списъка
Задачата е да запълните клетките в колоната с стойностите от горната част (така че темата да е във всеки ред на таблицата, а не само в първия ред на блока от книги по темата):
Изберете колона “Тема”, щракнете върху лентата “Home” група, кликнете върху “Намери и изберете» → «Маркирайте групата на клетки» → «Празните клетки” и започнете да въвеждате формулата (т.е. сложи знак за равенство) и се отнася до клетката, от по-горе, само с едно натискане на стрелка нагоре на клавиатурата. След това натиснете Ctrl + Enter. След това можете да запазите данните като стойности, тъй като формулите вече не са необходими:
Как да намерите грешки във формулата
Изчисляване на отделна част от формулата
За да се разбере сложната формула (който функционира като аргументите, използвани от друга функция, това е, някои функции са вградени в другата), или да се намери в него източник на грешки, често се налага да се изчисли тази част от нея. Има два прости начина:
- За да изчислите частта за формула директно във формулата, изберете тази част и натиснете F9:
В този пример имаше проблем с функцията за търсене – това беше объркано с аргументи. Важно е да запомните, че ако не анулирате изчислението на част от функцията и натиснете Enter, изчислената част ще остане число. - Кликнете върху бутона “Изчисляване на формула” в групата “Формула” на панделката:
В прозореца, който се показва, можете да изчислите формулата по стъпки и да определите в кой етап и в коя функция възниква грешката (ако съществува):
Как да определите от какво зависи формулата или какво се отнася до нея
За да определите от кои клетки зависи формулата, в групата “Формули” на лентата кликнете върху бутона “Въздействащи клетки”:
Появяват се стрелки, които показват какво зависи от резултата от изчислението.
Ако символът, подчертан в картинката, е показан в червено, формулата зависи от клетките на други листове или в други книги:
Като щракнете върху него, ще видим точно къде се намират влияещите клетки или диапазони:
До бутона “Influencing cells” е бутонът “Зависими клетки”, който работи подобно: показва стрелките от активната клетка с формулата до клетките, които зависят от нея.
Бутонът “Премахване на стрелките”, намиращ се в същия блок, ви позволява да премахнете стрелките към повлияващите се клетки, стрелките към зависимите клетки или и двата вида стрелки наведнъж:
Как да намерите сумата (брой, средна стойност) на стойностите на клетките от няколко листа
Да приемем, че имате няколко листа от същия тип с данните, които искате да добавите, да преброите или да обработите нещо различно:
За да направите това, клетката, в която искате да видите резултата, влиза в стандартната формула, като SUM (SUM) и въведете аргумента, следван от двоеточие и фамилното име на първия лист от списъка на тези листове, трябва да се справи:
Ще получите сумата от клетките с адрес B3 от листовете “Data1”, “Data2”, “Data3”:
Това адресиране работи за разположените листове последователно. Синтаксисът е: = FUNCTION (Първи лист: last_list!).
Как да създадете автоматично шаблонни фрази
Използвайки основните принципи на работа с текст в Excel и няколко прости функции, можете да подготвите шаблонни фрази за отчети. Няколко принципа на работа с текста:
- Комбинирайте текст със знак (можете да го замените с функцията CONCATENATE, но това няма много смисъл).
- Текстът винаги е написан в цитати, препратките към клетки с текст са винаги без.
- За да получите символа на услугата “котировки”, използвайте функцията CHAR с аргумент 32.
Пример за създаване на шаблонна фраза чрез формули:
резултат:
В този случай, освен функция Символ (CHAR) (за показване на кавичките) с помощта на функцията IF (IF), дава възможност за промяна на текста, в зависимост от това дали положителната динамика на продажбите там, и функцията на текста (текст), което позволява да се показва определен брой във всякакъв формат. Неговият синтаксис е описан по-долу:
ТЕКСТ (стойност; формат)
Форматът е цитиран по същия начин, както ако сте въвели потребителски формат в прозореца Форматиране на клетки.
Автоматизирате също така и по-сложни текстове. В моята практика аз бях за автоматизиране на дълга, но рутинните коментарите за счетоводните отчети за управлението във формат “са паднали / увеличава с XX в плана се дължи основно на увеличение / намаление на FAKTORA1 XX, увеличение / намаление на YY на FAKTORA2 …» със списъка с промяна на фактори. Ако пишете такива коментари често и в процеса на писане може алгоритмизация – струва веднъж озадачи създаде формула или макрос, който ще ви спаси от най-малко част от работата.
Как да запазите данните във всяка клетка след комбиниране
Когато комбинирате клетки, само една стойност се запазва. Excel предупреждава за това, когато се опитва да обедини клетките:
Съответно, ако имате формула, която зависи от всяка клетка, тя ще спре да работи след комбинирането й (# N / A грешка в редове 3-4 на примера):
За да обедините клетки, като същевременно се запазят данните във всяка от тях (може би имате формула, както в този абстрактен Например, може да искате да обедините клетки, но запази всички данни в бъдеще, или да се скрие намеренията си), комбинирайте всички клетки в работен лист , изберете ги и след това използвайте командата “Format by pattern”, преместете форматирането към клетките, които искате да комбинирате:
Как да изградим състав от няколко източника на данни
Ако е необходимо да се изгради консолидирани от различни източници на данни, е необходимо да се добави към лентата или лентата с инструменти “обобщена съветника и класации” за бърз достъп, в която има такава опция.
Можете да направите това, както следва: “File» → «Настройки» → «Бърза Toolbar достъп» → «Всичко екипа» → «обобщена Wizard и диаграми» → «Добави»:
След това съответната икона се появява на лентата, като кликването върху нея причинява същия съветник:
Когато кликнете върху него, се появява диалогов прозорец:
В него трябва да изберете “В няколко обхвата на консолидация” и да кликнете върху “Напред”. В следващия абзац можете да изберете “Създаване на една страница” или “Създаване на полета на страницата”. Ако искате сами да измислите име за всеки от източниците на данни, изберете втория елемент:
В следващия прозорец добавете всички диапазони, на които се основава обобщението, и им дайте имената:
След това в последния диалогов прозорец посочете къде ще бъде поставен отчетът за обобщената таблица – на съществуващия или на новия лист:
Обобщеният отчет за таблицата е готов. В филтъра “Page 1” можете да изберете само един от източниците на данни, ако е необходимо:
Как да се изчисли броят на случаите на текст А в текст Б (“MTS тарифа SuperMTS” – две събития на съкращението MTS)
В този пример в колона А има няколко текстови низове и нашата задача е да разберете колко пъти във всеки от тях има търсеният текст, намиращ се в клетка Е1:
За да разрешите този проблем, можете да използвате сложна формула, състояща се от следните функции:
- LEN (LEN) – изчислява дължината на текста, единственият аргумент е текстът. Пример: DLSTR (“машина”) = 6.
- SUBSTITUTE – замества текста в текстовия низ с друг текст. Синтаксис: SUBSTRATE (текст; star_tekst; nov_tekst). Пример: ПРЕДОСТАВЯНЕ (“кола”; “авто”; “) =” мобилен “.
- НАГОРЕ – замества всички знаци в низа с главни букви. Единственият аргумент е текстът. Пример: ПРЕДЛАГА (“машина”) = “МАШИНА”. Нуждаем се от тази функция, за да направим търсене на нечувствителност към малки букви. В края на краищата, PROPOSE (“машина”) = PROPISN (“Машина”)
За да откриете появата на определен текстов низ в друг, трябва да изтриете всичките му събития в изходния низ и да сравните дължината на получения низ с оригинала:
DLSTR (“Тарифа MTS Super MTS”) – DLSTR (“Супер тарифа”) = 6
И след това разделете тази разлика по дължината на линията, която търсехме:
6 / DLSTR (“MTS”) = 2
Двата пъти линия “MTS” е включена в оригиналната.
Остава да бъде написана на езика на формулите за алгоритъм (означен “текст” на текста, който ние търсим влизане и “желания” – броят на събитията, които се интересуват от):
= (DLSTR (текста) -DLSTR (ПРЕДОСТАВЯНЕ (ПРЕДОСТАВЯНЕ (текста); ПРЕДЛАГА (търсени за); “)))) / ДЛСТР (търсени за)
В нашия пример формулата изглежда така:
(A2) -DLST (SUPPORT (PRESENT (A2); PRESCRIPTION ($ E $ 1); DLR ($ E $ 1)
