Пътят на воина - poleznyashki превъзхождат

Poleznyashki Excel

На тази страница е колекция от семпли и елегантни инструменти за работа в Excel. Какво на английски се нарича съвети трикове. В някои случаи, давам линк към източника. Това означава само, че това е мястото, където се запознах с описанието на метода.

Ако е необходимо да се намери броя на първия не е празна клетка в ред (стълб), или не-празен стойността на първата клетка, можете да използвате малко известна особеност на функцията INDEX да се върне повече от една стойност, а на масив.

Докато функцията IF и повечето от тези функции може да бъде лесно се превръща логически стойности вярно / невярно цифров 1/0 SUMPRODUCT не го прави. За да се справи с проблема, посредством операция минус минус. или поставите на всички критерии в един аргумент, като се използва умножение (*) критериите за Булева И и допълнение (+) - за НОР.

Ако искате да защитите на листа, но оставят възможност за актуализиране на обобщени таблици Pivot, Excel стандартни методи не помагат (странно, както бе обявено, че трябва да помогне ...). VBA препоръчвам малко код за частична лист защита.

Сериозен недостатък при създаване на обобщена въз основа на голям набор от необработени данни - чака приключване на обработката на всички таблицата с данни, като добавите към него ново поле. Това се превръща в истинско мъчение за да добавите към обобщени таблици няколко големи полета с данни! Обърнете кутията Отложи Разпределение Update. организира всички полета, както се изисква, и едва след това се актуализира обобщени таблици Pivot.

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

Във версията на Excel Professional Plus е достъпно Запитване надстройка. Тя ви дава възможност да: анализира активната работна книга, да сравните двете книги, показани като схема на комуникация книга, лист, клетка; Изчистване на ненужни форматиране. Това е особено ценно, ако имаш файла от начинаещ потребител, който обича да рисува цели редове или колони.

В момента, в повечето изчисления използва десетични знака. Въпреки това, в някои случаи, може да се натъкнете на общи (обикновени) фракции. Това може да бъде проучване случай, или диаметъра на клапана (като част от инча), или накрая, теглото на злато слитък (като част от една унция). 🙂 Excel предлага 9 стандартни формати за лечение на фракции. В допълнение, можете да създадете голям брой персонализирани формати по своему.

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

Когато вашият Excel работна книга съдържа няколко листа, за лесна навигация, може да искате да се създаде таблица на съдържанието (индекс). Можете да го направите ръчно или с помощта на код VVA.

Ако използвате активно осеви таблици, най-вероятно трябва да се създаде формули, които сочат към клетката на осевата таблица. По подразбиране Excel не създава обичайната позоваване тип = C4 и формула GETPIVOTDATA. Ако е неудобно, или искате да бъдете в състояние да копирате формула покрай колоната, и научи Excel да се отнасят към клетка в осевата таблица, както обикновено.

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

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

Запознайте се с функцията на отместване. С негова помощ можете да намерите данни в случаите, когато CDF не могат да се справят. Основното предимство OFFSET проявява в създаването на динамичен диапазон (I-рано докосна по тази тема).

инструменти Standard Excel не ви позволяват да създавате контекстуални падащите списъци. Например, искате да направите част от списъка зависи от съдържанието на клетката отляво на списък от клетки. Създаване на име масиви и се отнасят до използването им непряка функция INDIRECT (косвено).

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

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

В Excel, има малко известни борсови диаграми. Те се наричат ​​блок или кутия и мустак. По мое мнение, тези класации несправедливо прескочени внимание. Аз не изключва възможността, че са запознати с тях ще ви позволи да се премине от детерминистичен възглед за света на вероятността. И това е по-вярно, особено при планирането и прогнозирането.

Финансовите функции в Excel изглежда много сложно. За да ги разберем, трябва да се разбере областта предмет - което, всъщност, се очаква тези функции. Прочетете прегледа сравняване анюитетна и диференцирани плащания за погасяване на ипотечен кредит. и за да можете да се повиши завесата на някои от финансовите функции, по-специално: PMT, OSPLT, IPMT, PS, NPER, процент.

Ако районът на данни, върху които е изградена схема се разширява, в лявата част на данните за видимите област Excel лист гранична линия редактирате формули. и на графиката ще видите всички нови данни.

Ако имате нужда да се движат на големи формули за масиви. съдържа относителни връзки, нова област на връзки лист задържащите на същите клетки, които са цитирани от оригиналната формула, използвайте = знак в замяна на някои редки знак, след обратната замяна.

По-рано е описано как да се използва потребителска функция за намиране на сумата от стойностите в клетките, цвета на осветяване. За съжаление, тази функция не работи, ако клетките са оцветени с помощта на условно форматиране. Въпреки това, много употреба условно форматиране, за да маркирате някои правила. Вместо да се анализира цвета на клетките, е достатъчно да се прилагат правилата на функциите или SUMIFS DSUM.

Ако трябва да се преброят на срещания на низ (характер) в текста. Можете да използвате прост VBA код или стандартен Excel-базирани инструменти функция Заместник ()

Много известни счетоводство "zamorochka", свързани с различията в една стотинка в резултатите на сумиране. Както е известно, Excel обобщава на базата на точни стойности, съхранявани в клетка, и отразява, в съответствие с формата, по свой избор, например, до стотинка. За да се преодолее този проблем предлага елегантна формула.

Смятате ли, че за симулация, като се използва метод Excel Монте Карло съм.

Използвайте непряко функция за транспониране на редове в колони формули спестяване. Понякога възниква проблемът за прехвърляне на данни от колоните в ред (или обратно) на. Ако в този случай не е необходимо само да транспонират ценности, но също така се запази връзките, а след това се опитват да използват INDIRECT функция.

Използвайте формула в условно форматиране. По-специално, тя позволява лесно да изберете минималните и максималните стойности в диапазона от клетки или алтернативните ред цветовете в таблицата. Подробности тук.

Научи функция WEEKDAY връща буквално стойност. Не знам за вас, но ме изнервя, че функция WEEKDAY връща число ... например, 4. Когато и приятен - "четвъртък". Този проблем може лесно да бъде решен един малък VBA код или Excel стандартни средства.

Създаване на име динамичен обхват. Ако работите с обем данни, което се променя с времето (увеличаване на броя на редовете и / или колони), тя е много удобно да присвоите име гама и обхват разделителна способност определя от функцията на отместване. Подробности тук.

Добавяне на стойностите в клетките с определен цвят. Доста често потребителите "боя" клетката в различни цветове. Ако след това е необходимо да се сумира стойностите в избраните клетки, а след това, за съжаление, няма такова Excel`ya стандартна характеристика. Използвайте непретенциозен VBA код. Можете също така да разчита на броя на клетките. който съдържа текста на определен цвят.

Инсталирайте филтъра за осевата таблица въз основа на данните в клетка. Ако използвате осеви таблици в Excel, които съдържат хиляди или десетки хиляди ценности, изборът на един от тях, филтър доклад вероятно карам си луд ... Въведете стойността, с която искате да инсталирате филтъра в клетка на лист и стартира макроса.

Използвайте анализ на чувствителността за конструиране на модели. Така например, при подготовката на инвестиционния проект е резултат от моделиране на вътрешната норма на възвръщаемост - IRR. Как ще се го в една или друга промяна в предположения? Подробности тук.

Използвайте функцията CDF за извличане на данни от таблица с два параметъра. Например, трябва да се определи на заплатите на работниците и служителите въз основа на оценка (квалификация) и режима на работа (5-dnevka или замяна). Подробности тук.

Използвайте таблицата шаблона с две вертикални оси. В случаите, когато искате да се показват на една и съща графично представяне на данните различен мащаб, това е полезно да се използват две Y-ос. Типичен случай - абсолютна (RUB) и относителни (процентни) показатели. Така например, размерът на вземането и неговия дял от продажбата. Подробности тук.

Използвайте "говорещи" структура заглавия. Такива заглавия веднага да обърнат внимание на основната идея, която искате да се предадат с помощта на диаграми. В същото време се обърне внимание, за да изберете вида на диаграма, която ще ви помогне да се отрази по-добре на вашите мисли. Подробности тук.