Екцел-живот хацкинг за оне који су укључени у обради извештавања и података

ТЕСЛА није први пут гостовао писац говори о Лаифхакере. Раније, објавили смо одличну материјал од њега о томе како да направите план обуке: главна књига и онлајн ресурсе, као и инкрементални алгоритам направити план обуке.

Овај чланак садржи неке једноставне технике које помажу поједностави рад у Екцелу. они су посебно корисни за оне који су укључени у извештавање, припрема различите аналитичке извештајима на основу искрцавања извештаја 1Ц и других облика ових презентација и дијаграма за руководство. Немојте се претварати да буде апсолутна новина - у једном или другом облику, ове методе свакако разматрано у форумима или из члана.

Једноставна алтернатива ВЛООКУП и ХЛООКУП, ако су потребне вредности нису у првој колони табеле: ПОГЛЕД, Индек + МАТЦХ

ВЛООКУП (ВЛООКУП) И ПГР (ХЛООКУП) само рад ако су циљне вредности у првој колони или реду табеле из које желите да добијете податке.

У другим случајевима, постоје две опције:

1. Употреба ПОГЛЕД функција (претрага).
   Она има следећу синтаксу: Ватцхинг (види_вредност Тражи вектор; вектор_резултата
   instagram viewer
   ). Али за то да се ради исправно треба да има вредности Тражи вектор Они су сортирани у растућем редоследу:
2. УТАКМИЦА користе комбинацију функција (Матцх) и индекс (индек).
   УТАКМИЦА функција враћа серијски број елемента у низу (уз његову помоћ можете наћи, у реду табеле жељени елемент), и функција ИНДЕКС враћа елемент низа са унапред одређеном бројем (што знамо да функционише УТАКМИЦА).Синтакса опције:
   • Матцх (види_вредност массив_поиска; МАТЦХ_ТИПЕ) - у нашем случају, треба неку врсту поређења "тачним подударање", она одговара броју 0.
   • Индек (арраи; број_реда; [ЦОЛУМН_НУМ]). У том случају, није потребно навести број колоне као низ састоји од једне линије.

Како брзо попунити празне ћелије у листи

Задатак - за попуњавање ћелије са вредностима у колони изнад (предмет стоји у сваком реду табеле, не само у првом блоку реду књига на тему):

Изаберите колону "Наслов", притисните на траку у групи на дугме "Хоме", "Пронађи и изаберите» → «Означите групу ћелија» → «Бланк & целл "и почети улазак формулу (тј стави знак једнакости) и односи се на ћелију одозго, једноставним притиском на стрелицу горе тастатура. Затим притисните Цтрл + Ентер. Након тога, подаци се могу сачувати као вредност, јер се више нису потребне формуле:

Како пронаћи грешке у формули

Прорачун Еинзелстимме формула

Разумјевању сложеног формулу (у коме као функцијских аргумената помоћу отхер функција, односно, неке функције су уграђени у другом), или да нађу у њему извор грешака, често је потребно да се израчуна део тога. Постоје два једноставна начина:

1. Да бисте израчунали прави део формуле у формула траци, изаберите део, и притисните Ф9:
   
   У овом примеру, дошло је до проблема са функцијом СЕАРЦХ (претрага) - било Микед Уп аргументи. Важно је запамтити да ако не поништи обрачун функције и притисните Ентер, а затим израчунати део ће остати број.
2. Притисните "Израчунати формулу" у групи "формулу" на траци:
   
   
   У прозору који се појави, можете израчунати корак формулу по корак и да се одреди у којој фази и на који функција грешке (ако их има):
   

Како одредити шта одређује ту формулу или референце

Да би се одредило која од ћелија зависи од формуле у групи "формулу" у траци, кликните на дугме "Траце преседане":

Постоје стрелице указују од којих је резултат зависи.

Ако се симбол појављује на одабраној слици у црвеном, формула зависи од ћелија које се налазе у другим листама или у другим књигама:

Кликом на њега, можемо тачно видети где је утицај на ћелије или опсега:

Поред "Траце прецедената" Постоји дугме "Депендантс", раде на исти начин: приказује стрелице од активне ћелије са формулом за ћелије које од ње зависе.

Дугме "ремове стрелице" који се налази у истом блоку, омогућава да се уклоне претходника, породица, или све одједном стрелице:

Како да пронађете количину (број, средње) вредности ћелија са више листова

Рецимо да имате неколико сличних лист са подацима које желите да додате, наћи или обрађују на неки други начин:

Да би то урадили, ћелија у којој желите да видите резултат, унесите стандардну формулу, као што је сума (сума), и унесите аргумент праћен дебелог црева и презименом првог листа са листе листова Потребан си ми proces:

Добићете износ ћелије адреса Б3 са листова "Дата1", "Дата2", "дата3":

Такви се баве радови на листовима уређених сукцесивно. Синтакса је: феатуре = (первии_лист: последнии_лист референца на распон!).

Како да аутоматски изградити формулисаних фразе

Користећи основне принципе рада са текстом у Екцелу и неколико једноставних функција, може бити припремљене формулисаних фразе за извештаје. Неколико принципи рада са текстом:

• Комбинују текст са назнаком & (може да замени своју функцију Цонцатенате (спајање), али то није од велике користи).
• Текст је увек пише у наводницима, референце ћелија са текстом - Не увек.
• Да бисте добили специјални карактер "цитате", користећи функцију карактер (знак) аргумент 32.

Пример креирања шаблон фразе користећи формула:

довести:

У овом случају, поред симбол (знак) (за приказ наводнике) користећи функцију ИФ (АКО), који омогућава да промените текст у У зависности од тога да ли постоји позитиван тренд у продаји и функција текста (текст), што омогућава да се прикаже број у било формата. Њена синтакса је описан у даљем тексту:

ТЕКСТ (вредност; формат)

Формат је наведено у наводницима на исти начин као да сте унели прилагођени формат у "Формат Целлс."

Можете аутоматизовати сложеније текстове. У мојој пракси је за аутоматизацију дуго, али су рутински коментари на рачунима управљања у формату "цифра пала / Росе у КСКС на плану углавном због повећања / смањења ФАКТОРА1 КСКС, повећање / смањење ФАКТОРА2 ИИ... »са листе мења фактори. Ако често писати такве коментаре и процес писања цан алгоритхмизатион - вредан једном збуњени створи формулу или макро који ће вас спасити од бар једног дела посла.

Како да сачувате податке у свакој ћелији након спајања

Када се комбинују ћелије задржавају само једну вредност. Екцел вас упозорава када покушате да се споји ћелије:

Сходно томе, ако сте имали формулу која зависи од сваке ћелије, она ће престати са радом након синдикалном (буба # Н / А у редовима 3-4 примере):

Да се ​​споји ћелије, уз очување података у сваком од њих (можда имате формулу, као у овом примеру, абстрацт; можда ћете желети да споје ћелије, али имајте све податке у будућности, или да сакрију своје намере), комбинују било коју ћелију на лист, изаберите их, а затим користите команду "Формат Паинтер" формат трансфер на ћелијама које ти треба и цомбине:

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

Ако треба да изгради консолидовати из више извора података, потребно је додати на траку или Куицк Аццесс Тоолбар "ПивотТабле Визард и графикона" у којем постоји таква опција.

То можете урадити на следећи начин: "Величина» → «Подешавања» → «палете алатки Куицк Аццесс» → «Све тим» → «изведене табеле Чаробњак и дијаграми» → «Адд»:

Након тога, трака ће бити одговарајућа икона, клик на који узрокује додавање мајстора:

Када кликнете на њега, дијалог:

У њему ћете морати да изаберете "неколико бендова консолидације" и кликните на "Нект". У следећем одељку, можете одабрати "Цреате поље једну страницу" или "Креирај поље за страницу." Ако желите сами да смисли име за сваки од извора података - да изаберете другу ставку:

У следећем кутије, додати све креће на основу којих се гради резиме, и питајте их имена:

Након тог тренутка, биће домаћин изведене табеле извештај у последњем дијалогу - на постојећи или нови радни:

Извештај изведене табеле спреман. Филтер "Страница 1" можете одабрати само један од извора података, уколико је потребно:

Како израчунати број појављивања текста у текст А Б ( «Блиц тарифни СуперМТС" - два од скраћеница МТС)

У овом примеру, колона А, постоји неколико редова текста, а наш задатак - да сазна колико пута сваки од њих испуњава тражене текст у ћелији Е1:

Да би се решио овај проблем, можете да користите сложену формулу који се састоји од следећих функција:

1. Лен (Лен) - израчунава дужину текста, један аргумент - у тексту. Пример: ЛЕН ( "машина") = 6.
2. ЗАМЕНА (ЗАМЕНА) - замењује текстуални низ одређени текст са другом. Синтакса: ЗАМЕНА (tekst; стар_текст; нов_текст). Пример: ЗАМЕНА ( "кола", "ауто", "") = "мобилни".
3. ГОРЊИ (уппер) - замењује све знакове у низу са великим словима. Једини аргумент - текст. Пример: УППЕР ( "машина") = "МАЦХИНЕ". Ова функција ће бити потребно да би потрага осетљив на величину слова. На крају крајева, ГОРЊИ ( "машина") = горња ( "машина")

Да бисте пронашли одређену унос текстуалног низа на други, морате уклонити све своје појава у оригиналу и упоредите дужину низа од оригинала:

Лен ( "тарифа супер МТС") - Лен ( "супер курс") = 6

А затим поделите разлика од дужине линије, ми смо у потрази за:

6 / ЛЕН ( "МТС") = 2

То је двоструко линије "Блиц" је у оригиналу.

Остаје да буду написани на језику алгоритма формула (означен "текст" текст који тражимо улазак и "жељени" - број појава које смо заинтересовани за):

= (ЛЕН (текст) -ДЛСТР (СУБСТИТУТЕ (УППЕР (текст); УППЕР (тражен за); ""))) / ЛЕН (тражен за)

У нашем примеру, формула гласи:

= (ЛЕН (А2) -ДЛСТР (СУБСТИТУТЕ (УППЕР (А2); УППЕР ($ Е $ 1); ""))) / ЛЕН ($ Е $ 1)