15 магически формули в Excel

Excel е строга леля. От една страна, това е незаменим инструмент за създаване на отчети, списъци и анализи. От друга страна, можете да извлечете информацията, която искате от електронната таблица, само ако владеете типичния език на Excel. Такива формули на Excel прикачват всякакви връзки към клетките, за да връщат целева информация. Ето 15 функции, които могат да ви спестят време.

Ръчно или съветник за формули?

Предполагаме, че междувременно сте усвоили основните формули за прилагане на основните операции. Без да изпадаме в фокус фокус за специалистите, ние показваме колко полезни формули са съставени. Можете да ги въведете ръчно, но можете да използвате и бутона fx в лентата с формули: съветникът за формули. Той ще ви насочи да изграждате формулата стъпка по стъпка.

01 Текущо време

Вие ли сте някой, който редовно забравя да излиза правилно с работата си? Формулата TODAY автоматично попълва деня, месеца и годината, докато функцията NOW дори добавя времето към минутата. След това въвеждате = ДНЕС () или = СЕГА () . Тези функции са полезни и в работен лист, където искате да изчислите стойност въз основа на текущия ден и час. С десния бутон на мишката и избора за свойства на клетката можете след това да регулирате показването на датата и часа. За да актуализирате информацията за този час в активния работен лист, натиснете Shift + F9; използвайте F9, за да актуализирате цялата работна книга.

02 Брой напълнени клетки

Ако имате група клетки с текст и числа и искате да знаете колко числа има в даден избор, използвайте функцията COUNT . Тогава структурата на формулата изглежда така: = COUNT (област за търсене) . Областта, в която Excel трябва да търси, се появява между скобите. Това могат да бъдат клетки под или една до друга, но може да бъде и правоъгълна селекция от клетки. Ако има думи в селекцията, тези с функцията COUNT не се броят. Ако просто искате да преброите всички клетки, където е написано нещо, използвайте функцията = COUNTA (без точка).

03 Колко често?

Използвайте функцията COUNTIF, за да преброите конкретни данни . Да предположим, че сте съставили мрежа, в която се появяват четирима души, тогава можете да видите колко често се среща името Herman с = NANTAL.IF (област за търсене; „Herman“) . Въведете диапазона на търсене между скобите и оградете критериите за търсене в кавички.

04 Селективно добавяне

Функцията SUM за сумиране на клетки се използва широко. По-интелигентен вариант е SOM.IF () . Първо посочете областта, в която Excel трябва да търси между скобите. Обхватът на търсене трябва да бъде поредица от съседни клетки. След точка и запетая определяте какво трябва да се добави. Това могат да бъдат числа или справка. Ако е уравнение, трябва да го оградите в двойни кавички. Например = SUMIF (B20: B40, ”> 50”) прави сумата от всички клетки в този диапазон, които са по-големи от 50.

05 Добавяне при условие

Можете да разширите условието за добавяне още повече, като използвате информация в друга колона. Един пример става ясно. Да предположим, че имате цифри, които се отнасят до три града: Амстердам, Ротердам и Айндховен. Тогава можете да добавите номерата на Амстердам само с = SUMIF (диапазон; „Амстердам“; диапазон на добавяне) . В този случай формулата става = SUMIF (C48: C54; "Амстердам"; B48: B54) . На обикновен език: Когато думата Амстердам е в диапазона от C48 до C54, Excel трябва да сумира съответната стойност на клетката до нея в диапазона от B48 до B54.

06 Обединяване

С функцията CONCATENATE обединявате данните от различни клетки. Например, имена и фамилии клетки с нещо като = CONCATENATE TEXT (E34, ”“; F34) . Двойните кавички с интервала гарантират, че има интервал между собствено и фамилно име. По същия начин е възможно да се слеят текст с валута. Например, за да добавите валутата евро, трябва да я въведете като функция като = CONCATENATE TEXT (A1, ”“; B1; ”“ EURO (C1)) . Можете да прочетете това като „слейте клетки A1, B1 и C1 с интервали между тях и поставете знака за евро преди третия елемент на сливането“.

07 Увийте

Excel има няколко опции за закръгляване. Закръгляването по подразбиране изглежда = КРЪГЛО (число, брой десетични знаци) . И така, формулата = КРЪГЪЛ (12.5624, 1) връща 12.6 . В края на краищата искаш да закръглиш до едно число след десетичната запетая. Excel също ще закръгли до броя на десетичните знаци, който сте посочили с функциите ROUND.TO.ABOVEN и ROUND.BOTH . = ROUNDUP (12.5624; 2) следователно дава резултат 12,57 и = ROUNDDOWN (12.5624; 2), в резултат на 12.56 . Функцията INTEGER всъщност е и функция за закръгляване, но с това Excel закръглява до най-близкото цяло число.

08 Главни букви - малки букви

За да сте сигурни, че всичко се показва с главни букви в колона, използвайте функцията UPPERCASE . В SMALL.LETTERS формула прави обратното. И ако искате всяка дума да започва с главна буква, последвана от малки букви, използвайте функцията НАЧАЛНИ БУКВИ . Формулата = МАЛКИ БУКВИ (B4) показва съдържанието на клетка B4, но с малки букви.

09 При условие

Когато изчислението зависи от определени условия, вие използвате функцията IF . Принципът на тази функция е: = IF (условие, изчисление, ако условието е изпълнено, други случаи) . За да формулирате условието, използвайте знаците: = равно на,не е равно на, > повече от, < по-малко от, > = повече или равно на, < = по-малко или равно на. Да предположим, че в една организация всеки получава бонус, който е продал за 25 000 евро или повече. Ако получите бонус, думата „Ура“ автоматично ще се появи до името им, ако не, ще се появи думата „За съжаление“. Формулата, от която се нуждаете, е = IF (B2> = 2500; "Ура"; "За съжаление") .

10 най-големи - най-малките

За бързо намиране на най-високата и най-ниската стойност има функцията MAX и MIN . С = MAX (B2: B37) получавате най-високата стойност на тези клетки, а с = MIN (B2: B37) получавате най-ниската стойност в диапазона. Функциите НАЙ-ГОЛЯМА и МАЛКА са по-фини: можете също да поискате третата по големина или втората най-малка, например. Най-големият се намира с = НАЙ-ГОЛЯМ (B2: B37; 1); числото 1 показва най-голямото. С = LARGE (B2: B37,2) получавате втория по големина и т.н. По този начин можете лесно да съставите топ 3 или топ 10.

11 Вертикално търсене

Да предположим, че имате два работни листа с различна информация за едни и същи хора. С VLOOKUP извличате информация от работен лист 2 в работен лист 1. За да улесним това, ние сме дали на всеки човек уникален регистрационен номер и в двата раздела. Също така дайте име на диапазона в раздел 2, от който искате да получите информация. В този пример в работен лист 2 избираме колони A и B и в полето за име в горния ляв ъгъл въвеждаме името Address List . В клетка E2 на работен лист 1 поставяме функцията VLOOKUP . Форматът сега е = VLOOKUP (A2, Address List, 2, FALSE) . А2 се отнася до клетката с номера за записване във втория работен лист, Адресен списъкобозначава обхвата на търсене, 2 е номерът на колоната в работен лист 2, където са исканите данни. Последният аргумент е логическа стойност, където въвеждате FALSE, ако искате намерената стойност да съвпада точно.

12 Изтриване на интервали

С функцията TRIM изтривате ненужните интервали в текста. Тази функция ще остави някои интервали между думите, но ще премахне интервали преди или след думата. = TRIM (диапазон от клетки) е полезен за текст, импортиран от друга програма. В някои версии на Excel тази функция се нарича SPACES.CLEAR .

13 Размяна

Можете да прехвърлите съдържанието на колони в редове или обратно с функцията TRANSPOSE . Първо изберете клетките, където трябва да бъде поставена информацията. Уверете се, че сте избрали толкова клетки, колкото оригиналната серия. Тук набрахме годините в ред 8 и тримесечията в колона А. След това въведете функцията = TRANSPOSE и отворете скобите. След това плъзнете върху клетките, които искате да сменяте (тук от клетки B2 до E5). Затворете скобите и сега натиснете клавишната комбинация Ctrl + Shift + Enter. Това ще създаде формула за масив, която се съдържа в къдрави скоби.

14 Месечно погасяване

Ако вземате назаем за покупка, колко трябва да изплащате месечно? Да приемем, че взимате 25 000 евро ( B1 ) при 6% лихва ( B2 ) за 5 години ( B3 ). Показваме формулата в съветника, но можете и просто да въведете. С Лихва поставяте B2 / 12 , тъй като лихвата се отнася за една година и искате да знаете колко плащате всеки месец. За бройни вноски умножавате B3 по 12 , защото трябва да преобразувате годините в месеци. PV кутията означава Текуща стойност , която е 25 000 евро. Това дава формулата = BET (B2 / 12; B3 * 12; B1) или =ЗАЛОЖЕТЕ (6% / 12,5 * 12,25000) .

15 Фалшиви фигури

Когато експериментирате с формули, е полезно да имате фалшиви данни. Функцията SELECT BETWEEN генерира произволни данни, които са между определена най-ниска и най-висока стойност. Функцията = RANDBET (50, 150) генерира числа между 49 и 151.