Excel-живот хакерство за тези, които участват в отчитане и обработка на данни
Съвети / / December 19, 2019
Ренат не е първият път, гост-автор говори за Layfhakere. По-рано, ние публикувахме отличен материал от него за това как да се създаде план за обучение: главната книга и онлайн ресурси, както и частичното алгоритъм създаване на план за обучение.
Тази статия съдържа няколко прости техники, които помагат опрости работата в Excel. те са особено полезни за тези, които участват в управлението на отчитане, подготвя различни аналитични отчети въз основа на разтоварванията на доклади 1C и други форми на тези презентации и диаграми за лидерство. Не се прави на абсолютно новост - в една или друга форма, тези методи сигурност обсъждат във форуми или посочен в член.
Обикновено алтернатива на VLOOKUP и HLOOKUP, ако изискваните стойности не са в първата колона на таблицата: VIEW, INDEX + МАЧ
VLOOKUP (VLOOKUP) И PGR (HLOOKUP) само работа, ако целевите стойности са в първата колона или ред на таблицата, от която искате да получите данни.
В други случаи, има две възможности:
- функция Използвайте VIEW (справочни).
Тя има следния синтаксис: ГЛЕДАНЕ (търсена_стойност Търсене вектор; vektor_rezultata). Но, за да работи правилно, което трябва да референтна граница Търсене вектор Те бяха подредени във възходящ ред: - MATCH използва комбинация от функции (MATCH) и индекс (INDEX).
Функцията MATCH Връща поредния номер на елемента в масива (с негова помощ можете да намерите, в един ред от таблицата желания елемент) и функция ИНДЕКСА връща елемент масив с предварително определен брой (което ние знаем за функция MATCH).Синтаксис възможности:
• MATCH (търсена_стойност massiv_poiska; match_type) - в нашия случай, ние се нуждаем от такива сравнения "точно съвпадение", това съответства на броя 0.
• INDEX (решетка; ред_номер; [COLUMN_NUM]). В този случай, не е необходимо да се посочи номера на колоната като масива се състои от един ред.
Колко бързо да запълнят празните клетки в списъка
Задачата - да се запълни клетките със стойностите в колоната по-горе (в зависимост стои във всеки ред на таблицата, не само в първия блок ред на книги по този въпрос):
Изберете колоната "Тема", натиснете върху лентата в групата на "Начало" бутона "Намери и изберете» → «Маркирайте групата на клетки» → «Blank клетка "и започнете да въвеждате формулата (т.е. сложи знак за равенство) и се отнася до клетката, от по-горе, като просто натиснете стрелка нагоре клавиатура. След това натиснете Ctrl + Enter. След това, данните могат да бъдат записани като ценност, защото формулите вече не са необходими:
Как да намерите грешки в формулата
Изчисляване отделна част формула
За да се разбере комплекс с формула (в която като функция аргументи използват други функция, това е, някои функции са вградени в другата), или да се намери в него източник на грешки, често се налага да се изчисли част от него. Има два лесни начина:
- За изчисляване на дясната част на формулата в лентата за формули, изберете страна, и натиснете F9:
В този пример, че има проблем с функция за търсене (Search) - имаше смесени аргументи. Важно е да се помни, че ако не се откажете изчислението на функцията и натиснете Enter, след това изчислява част ще остане номер. - Натиснете бутона "Calculate формулата" в група "формула" на лентата:
В прозореца, който се появява, за да изчислявате формулата стъпка по стъпка и да се определят на какъв етап и в каква функция грешка (ако има такива):
Как да се определи това, което определя, че формула или съотнесени
За да се определи кои от клетките зависи от формулата в групата на "формулата" в лентата, щракнете върху бутона "Трейс Прецеденти":
Има стрелки, сочещи, от която резултатът зависи.
Ако символът се появи на избраното изображение в червено, формулата зависи от клетки, които се намират в други списъци, или в други книги:
Кликвайки върху него, можем да видим къде точно влиянието на клетката или диапазона:
В непосредствена близост до "Трейс прецеденти" има бутон "Зависими", които работят по същия начин: той показва стрели от активната клетка с формула за клетки, които зависят от него.
Бутона "Премахване стрели", разположени в същия блок, позволява да се отстранят прецеденти и съпрузите, или и с двете наведнъж стрели:
Как да разберете каква е сумата (число, средно) стойности на клетки с няколко листа
Да речем, че има няколко подобни лист с данните, които искате да добавите, да намерите или преработени по някакъв друг начин:
За да направите това, клетката, в която искате да видите резултата, влиза в стандартната формула, като SUM (SUM), и въведете аргумента, следван от двоеточие и фамилното име на първия лист от списъка на листа, което трябва процес:
Вие ще получите сумата на адрес на клетка B3 с листа "данни1", "данни2", "данни3":
Такива насочени работи за листа, подредени последователно. Синтаксис е: ИГРАЛНИ = (pervyy_list: posledniy_list позоваване на границите!).
Как автоматично да се изгради бланкетни фрази
Използване на основните принципи на работа с текст в Excel и няколко прости функции, може да се приготви бланкетни фрази за доклади. Няколко принципи на работа с текста:
- Комбинирайте текст с марката & (може да замени своята функция CONCATENATE (CONCATENATE), но това не е кой знае каква полза).
- Текстът е написан винаги в кавички, позоваванията клетъчни с текста - не винаги.
- За да получите най-специални знаци "кавички", като се използва характер функция (CHAR) аргумент 32.
Пример създаване шаблон фрази използване формули:
резултат:
В този случай, освен функция Символът (CHAR) (за показване на кавичките) с помощта на функцията IF (IF), която дава възможност за промяна на текста в В зависимост от това дали е налице положителна тенденция в продажбите и функцията на текста (текст), което позволява да се показва определен брой във всеки формат. Нейният синтаксис е описано по-долу:
ТЕКСТ (стойност; формат)
Форматът е определен в кавички, по същия начин, както ако сте влезли формат по избор в "Format Cells."
Можете да автоматизирате по-сложни текстове. В моята практика е да се автоматизира дълго, но рутинните коментарите за счетоводните отчети за управлението във формат "фигура паднаха / роза на XX в плана се дължи основно на увеличение / намаление на FAKTORA1 XX, увеличаване / намаляване на FAKTORA2 YY... »със списъка с променящите фактори. Ако пишете такива коментари често и в процеса на писане консерва алгоритмизация - струва веднъж озадачи създаде формула или макрос, който ще ви спаси от най-малко част от работата.
Как да запазите данните във всяка клетка след сливането
При комбиниране на клетките запазват само една стойност. Excel ви предупреждава, когато се опитате да се слеят клетки:
Съответно, ако имаше формула, която зависи от всяка клетка, тя ще престане да работа след техния съюз (бъг # N / A в редове 3-4 примери):
Да се слеят клетки, при запазване на данните във всяка от тях (може би имат формула, както в този пример, абстрактно; може да искате да се слеят клетки, но запази всички данни в бъдеще, или да се скрие намеренията си), комбинирайте някоя клетка лист, изберете ги и след това използвайте командата "Format Painter" формат за прехвърляне на клетките от които имате нужда и комбинират:
Как да се изгради обобщение на няколко източници на данни
Ако е необходимо да се изгради консолидирани от различни източници на данни, е необходимо да се добави към лентата или лентата с инструменти за бърз достъп "обобщена съветника и класации", в която има такава опция.
Можете да направите това, както следва: "File» → «Настройки» → «бърз достъп Toolbar» → «Всичко екипа» → «обобщена Wizard и диаграми» → «Добави»:
След това лентата ще бъде съответната иконка, кликването върху която причинява добавянето на капитана:
Когато кликнете върху нея, диалогов прозорец:
В него ще трябва да изберете "Преди няколко ленти на консолидация" и натиснете бутона "Next". В следващия раздел, можете да изберете "Създаване на едно поле страница" или "Създаване поле на страницата." Ако искате сами да излезе с едно име за всеки един от източниците на данни - за да изберете втора точка:
В следващия прозорец, добавете всички диапазони на базата на която да се гради резюме, и ги помолете имената:
След този момент, ще бъде домакин на обобщена доклад в последния диалогов прозорец - на съществуващия или нов работен лист:
Доклад обобщена готов. Филтърът "Page 1" можете да изберете само един от източниците на данни, ако е необходимо:
Как да се изчисли броят на срещания на текста в текста на А Б ( «MTS тарифни SuperMTS" - две появявания на MTS на съкращение)
В този пример, колона А, има няколко реда текст, както и нашата задача - да разберете колко пъти всеки от тях отговаря на необходимия текст в клетка E1:
За да се реши този проблем, можете да използвате сложна формула, състояща се от следните функции:
- LEN (МПП) - изчислява дължината на текста, един-единствен аргумент - текстът. Пример: LEN ( "машина") = 6.
- Заместник (заместител) - замества стринга определен текст с друг. Синтаксис: Заместник (текст; star_tekst; nov_tekst). Пример: SUBSTITUTE ( "кола", "кола", "") = "мобилен".
- UPPER (горна) - замества всички символи в низ в главни букви. Единственият аргумент - текстът. Пример: горна ( "машина") = "машина". ще бъдат необходими Тази функция да направи търсенето нечувствително. В крайна сметка, UPPER ( "Machine") = UPPER ( "Machine")
За да намерите определен запис на текстов низ в друг, трябва да премахнете всички свои събития в оригинал и сравни дължината на низа от оригинала:
LEN ( "тарифа Super MTS MTS") - LEN ( "Супер процент") = 6
И след това се разделят разликата от дължината на линията, което търсим:
6 / LEN ( "MTS") = 2
Това е два пъти повече от линия "MTS" е в оригинала.
Остава да бъде написана на езика на формулите за алгоритъм (означен "текст" на текста, който ние търсим влизане и "желания" - броят на събитията, че ние се интересуваме от):
= (LEN (текст) -DLSTR (заместващи (горен (текст); Горница (търсени за); ""))) / LEN (търсени за)
В нашия пример, формула е както следва:
= (LEN (А2) -DLSTR (заместващи (горен (А2) горен ($ E $ 1); ""))) / LEN ($ E $ 1)