Excel е чудесен инструмент за отчитане, анализ, организиране и автоматизиране на данни. Функциите на Excel помагат много при работата с данни. Функциите като COUNTIFS, SUMIFS, VLOOKUP и т.н. са най -мощните и често използвани функции от самото начало в света на Excel.
Въпреки че функциите, налични в Excel 2016 и по -стари, са достатъчни за разработване на всякакъв вид изчисления и автоматизация, но понякога формулите стават трудни. Например, ако няма да намерите максималната стойност при някои условия, трябва да използвате някои трикове в по -старата версия на Excel за 2016 г. Този вид незначителни, но важни неща се решават в Excel 2019 и 365.
Има 10+ нови функции в Excel 2019 и 365, които намаляват човешките усилия и сложността на формулите.
1. Функцията MAXIFS
В Excel 2016 и по -стари, ако искате да получите максималната стойност в диапазон, когато едно или повече условия съвпадат, трябва да използвате MAX с IF с някои трикове. Това не е много трудно, но отнема време и обърква някои.
Excel 2019 въвежда нова функция, наречена функция MAXIFS. Тази функция връща максималната стойност от масив, когато всички посочени условия са съчетани.
Синтаксисът на функцията е:
| = MAXIFS (макс_диапазон, диапазон_критерии1, критерии1, диапазон_критерии2, критерии2 …) |
Max_range1: Числовият диапазон съдържа максималната стойност.
Диапазон на критерии1: Това е диапазонът от критерии, който искате да филтрирате, преди да получите максимална стойност.
Критерии 1: Това са критериите или филтърът, които искате да поставите в range_ range, преди да получите максималната стойност.
Да предположим, че трябва да получите максималните оценки от клас 3, тогава формулата ще бъде
| = MAXIFS (марки, клас, 3) |
Тук маркировките са именуваният диапазон, който съдържа маркировките, а класът е именуваният диапазон, който съдържа класа.
Прочетете подробно за функцията MAXIFS тук.
2. Функцията MINIFS
Същото като функцията MAXIFS, функцията MINIFS се използва за получаване на минималната стойност от дадения диапазон, когато са изпълнени всички посочени условия.
Синтаксисът на функцията е:
| = MINIFS (минимален_диапазон, диапазон_критерии1, критерии1, диапазон_критерии2, критерии2 … |
Минимален диапазон1: Численият диапазон съдържа минималната стойност.
Диапазон на критерии1: Това е диапазонът от критерии, който искате да филтрирате, преди да получите минимална стойност.
Критерии 1: Това са критериите или филтърът, които искате да поставите в range_ range, преди да получите минималната стойност.
Да предположим, че трябва да получите минималните оценки от клас 3, тогава формулата ще бъде
| = MINIFS (оценки, клас, 3) |
Тук "маркировки" е именуваният диапазон, който съдържа маркировките, а "клас" е именуваният диапазон, който съдържа класа.
Прочетете подробно за функцията MAXIFS тук.
За да намерите минимална стойност от обхват с условия в excel 2016 и по -стари, прочетете това.
3. Функцията IFS
Тъй като вложеният Ifs има специално място в ежедневието ни, ние много го харесваме. Но за някои нови учащи това е сложно. Вложените if позволяват да проверим множество условия и да върнем различна стойност, когато някое от условията е изпълнено. Формулите се усложняват с все повече и повече IF във функция.
Excel 2019 и Excel 365 вече използват IFS функция. Той може да проверява множество условия и да връща различни стойности за всяко условие.
Синтаксис на функцията IFS:
| = IFS (условие1, Стойност1_Ако_Истина, [Състояние2, Стойност2_Ако_Истина], …) |
Състояние 1:Първото условие.
Стойност1_Ако_Истина: Стойност, ако първото условие е вярно.
[Условие 2]: Това е по избор. Второто условие, ако имате такова.
[Value1_If_True]: Стойност, ако второто условие е вярно.
Можете да имате колкото се може повече комбинации от условия и стойности. Има ограничение, но никога няма да е необходимо да го достигнете.
Да речем, че трябва да дадете оценките на учениците чрез оценките там. За марки повече от 80, степен A, B за повече от 60, C за повече от 40 и F за по -малко или равно на 40.
| = IFS (A1> 80, "A", A1> 60, "B", A1> 40, "C", A1 <= 40, "F") |
Подробно обяснение на функцията IFS можете да намерите тук.
4. Функцията SWITCH
Функцията за превключване връща различни стойности в зависимост от резултатите от един израз. Звучи ли като IFS? Това е нещо като. Всъщност тази функция е за замяна на друг вид вложени IF формули.
За разлика от функцията IFS, която връща стойности въз основа на TRUE, FALSE; връща стойностите на функцията SWITCH въз основа на VALUES, върнати от израза.
| = SWITCH (израз, стойност1, резултат1, [по подразбиране или стойност2, резултат2], …) |
Израз: Това може да бъде всеки валиден израз, който връща някои стойности. Препратка към клетка, формула или статична стойност.
Стойност1, резултат1: Стойността и резултатът са сдвоени. Ако стойността, върната отизраз е стойност1, тогава резултатът1 ще бъде върнат.
[По подразбиране или стойност2, резултат2]: Ако искате да върнете някаква стойност по подразбиране, определете я тук. Иначе дефинирайте value2 и result2. Не е задължително.
Например, ако имате формула, която връща имената на животните. Сега, в зависимост от върнатото име на животното, искате да върнете звука на подписа на това животно.
| = Превключвател (A1, "куче", "Bow Wow", "котка", "мяу", "говори") |
Тук обясних подробно функцията SWITCH.
5. Функцията FILTER
Функцията FILTER се използва за филтриране на данни въз основа на някои критерии. Използвахме опцията за филтриране от началния раздел в Excel. Функцията FILTER работи по същия начин като опцията филтър. Той просто връща филтрираните данни с помощта на функция. Тези филтрирани данни могат да се използват като източник на данни за други формули.
Синтаксисът на функцията FILTER е:
| = FILTER (масив, включва, [ако_празна]) |
Масив: Това е масивът, който искате да филтрирате. Тя може да бъде едномерна или двумерна.
Включва:Това е филтърът, който искате да поставите върху масива. Например, цветове = "червено".
[ако_празна]:Това е по избор. Определете всеки текст или израз, ако филтърът не връща нищо.
Формулата по -долу връща всички плодове, чийто цвят е червен.
| = FILTER (плодове, цвят = "червено", "няма намерени плодове") |
Тук плодовете и цветовете са наименувани диапазони, които съдържат съответно имена на плодовете и техните цветове.
Можете да прочетете подробно за функцията FILTER тук.
6. Функцията SORT
В Excel 2016 и по -стари беше наистина трудно да се получи сортиран масив с помощта на формула. Този процес е опростен в Excel 2019 и 365.
Excel 2019 въвежда функция SORT. Функцията SORT сортира дадения масив във възходящ или низходящ ред по дадената колона/ред.
Синтаксисът на функцията SORT е:
| = SORT (масив, [сортиране_индекс], [ред на сортиране], [от_кол]) |
Масив:Това е препратката към масив или диапазон, който искате да сортирате.
[сортиране_индекс]:Номерът на колоната в двуизмерния масив, по който искате да сортирате диапазона. По подразбиране е 1.
[ред на подреждане]:Редът, по който искате да сортирате масива. За изкачване е 1, а за спускане --1. По подразбиране е 1.
[by_col]:Задайте го True (1), ако искате да сортирате хоризонтален масив. По подразбиране е False (0) за вертикални данни.
Да речем, ако искате да сортирате стойности в диапазона A2: A11 възходящо. тогава формулата ще бъде.
| = СОРТИРАНЕ (A2: A11) |
Тук обясних подробно функцията SORT.
7. Функцията SORTBY
Функцията SORTBY е подобна на функцията SORTBY. Единствената разлика е, че сортиращият масив не трябва да е част от сортирания масив във функцията SORTBY.
| = SORTBY (масив, сортиране_масив1, [ред], …) |
Масив:Това е масивът, който искате да сортирате.
Sorting_array1:Това е масивът, по който искате да сортирате масива. Размерът на този масив трябва да е съвместим с масив.
[поръчка]:По избор. Задайте го на -1, ако искате поръчката да е низходяща. По подразбиране той е възходящ (1).
Да речем, ако искате да сортирате диапазона A2: A11 по диапазон B2: B11, в низходящ ред. Тогава формулата в Excel 2019 или 365 ще бъде:
| = SORTBY (A2: A11, B2: B11, -1) |
Обясних подробно функцията SORTBY тук.
8. УНИКАЛНАТА функция
В Excel 2016 и по -стари използвахме редица функции в комбинация, за да получим всички уникални стойности от дадения списък. Използваната формула е доста сложна и трудна за разбиране.
Excel 2019 и 365 въвежда една проста UNIQUE функция, която връща всички уникални стойности от даден масив.
Синтаксисът на функцията UNIQUE е:
| = UNIQUE (масив, [by_col], [точно_однократно]) |
Масив: Масивът, от който искате да извлечете уникални стойности:
[by_col]: Задайте TRUE (1), ако масивът е хоризонтален. По подразбиране е FALSE за вертикални данни.
[точно_ един път]: задайте TRUE (1), ако искате да извлечете стойности, които се срещат само веднъж в масива. По подразбиране е FALSE (0) за извличане на всички уникални стойности.
Да речем, че искам да получа само един екземпляр от всяка стойност от диапазон A2: A11, тогава формулата ще бъде:
| = УНИКАЛНО (A2: A11) |
За да прочетете подробно за функцията UNIQUE, можете да щракнете тук.
9. Функцията SEQUENCE
За да получим поредица от числа в Excel 2016 и по -стари използваме комбинация от функции. Решението работи, но е сложно.
Excel 2019 и 365 предлагат решението под формата на функцията SEQUENCE. Функцията за последователност просто връща серията от номера.
Синтаксисът на функцията SEQUENCE е:
| = SEQUENCE (редове, [колони], [начало], [стъпка]) |
Редове:Броят редове, към които искате да разлеете последователността.
[колона]:Броят колони, към които искате да разлеете поредицата. Числата първо ще запълнят колоните и след това редовете. Колоната не е задължителна. По подразбиране е 1.
[начало]:По избор. Началният номер на последователността. По подразбиране е 1.
[стъпка]:Това е инкрементното число за следващото число. По подразбиране е 1.
Простият пример е получаването на серия от 1 до 10. Формулата ще бъде:
| = ПОСЛЕДОВАТЕЛНОСТ (10) |
За да разберете подробно функцията SEQUENCE в Excel 365, прочетете това.
10. Функцията RANDARRAY
Това е друга формула за динамичен масив, която връща масив от случайни числа. Това е комбинация от функция RAND и RANDBETWEEN. Можете да получите частични случайни числа или цели числа. Можете да посочите желания брой произволни числа. Дори редове и колони, в които искате да разпределите тези числа.
Синтаксисът на функцията RANDARRAY е:
| = RANDARRAY ([редове], [колони], [мин], [макс], [цяло число]) |
Всички аргументи в тази функция са незадължителни. По подразбиране тя работи като функция RAND.
[редове]:Броят на числата, които искате вертикално (брой редове, които искате да попълните).
[колони]:Броят на числата, които искате хоризонтално (брой колони, които искате да попълните).
[мин]:Началният номер или минималната стойност на случайното число/и.
[макс]:Максималният диапазон на номера.
[цяло число]:Задайте го вярно, ако искате случайните числа да са цели числа. По подразбиране е невярно и връща частични случайни числа.
Долната функция ще върне пет произволни дробни числа по ред:
| = RANDARRAY (5) |
Прочетете подробно за функцията RANDARRAY тук.
11. Функцията CONCAT
В Excel 2016 и по -стари не е лесно да се свържат повече от една клетка или диапазон с помощта на една формула.
В Excel 2019 и 365 проблемът се решава с функцията CONCAT. Функцията може да приема няколко клетки, диапазони като аргументи.
Синтаксисът на функцията CONCAT е:
| = CONCAT (текст1, [текст2], …) |
Текст 1: Текстът1 може да бъде всеки текст или диапазон, който искате да обедините.
[текст2]: Това е по избор. Това също може да бъде всеки текст или диапазон.
Да кажем, че ако искате да обедините всяка клетка в диапазон A2: A11, тогава формулата ще бъде
| = CONCAT (A2: A11) |
За да разгледате подробно функцията CONCAT, щракнете тук.
12. Функцията TEXTJOIN
Горната функция свързва всички клетки в диапазон, но не свързва клетките с някакъв определен разделител. Да речем, ако подготвяте файл за CSV формат, тогава ще трябва да обедините клетките със запетая. В този случай функцията CONCATENATE и CONCAT ще се провалят.
Тук функцията TEXTJOIN работи чудесно и съчетава дадените текстове с дадения разделител.
| = TEXTJOIN (разделител, игнориране_празни_ клетки, текст1, [текст2], …) |
Разделител:Това е разделителят, който искате да използвате разделител между отделни текстове. Това може да бъде запетая (,), точка и запетая (;) или нещо друго, дори нищо.
Игнорирай_празни_ клетки:Това е двоична променлива. Задайте го на TRUE, ако искате да игнорирате празни клетки в диапазони, иначе задайте FALSE, за да включите празните клетки.
Текст 1:Това е текстът, към който искате да се присъедините. Това могат да бъдат отделни текстове, клетки или цели диапазони.
Да речем, че искам да свържа диапазон A2: A11 със запетая, без да обръщам внимание на празните клетки.
| = TEXTJOIN (",", 1, A2: A11) |
За да разберете подробно тази функция, щракнете тук.
Тази статия беше само въведение в новата функция на Excel 365 и 2019. Обясних тези функции подробно в отделни статии. Можете да кликнете върху наличните връзки за всяка функция в статията, за да разберете напълно функцията. Има и други функции като XLOOKUP, които все още не са пуснати.
Ако имате някакви съмнения относно теми за Excel или VBA, попитайте в секцията за коментари по -долу. Кажете ни как можем да се подобрим. Оценяваме вашето предложение и нетърпеливи да чуем от вас.
Създайте VBA функция за връщане на масив | За да върнем масив от дефинирана от потребителя функция, трябва да го декларираме, когато назоваваме UDF.
Масиви в Excel Formul | Научете какви масиви има в Excel.
Как да създадете потребителска функция чрез VBA | Научете как да създавате потребителски функции в Excel
Използване на функция, дефинирана от потребителя (UDF) от друга работна книга, използваща VBA в Microsoft Excel | Използвайте дефинираната от потребителя функция в друга работна книга на Excel
Връща стойности на грешки от дефинирани от потребителя функции, използвайки VBA в Microsoft Excel | Научете как можете да върнете стойности за грешка от дефинирана от потребителя функция
Популярни статии:
Разделете Excel листа на множество файлове въз основа на колона с помощта на VBA | Този VBA код разделя базата на Excel на уникални стойности в определена колона. Изтеглете работния файл.
Изключете предупредителните съобщения с помощта на VBA в Microsoft Excel 2016 | За да изключим предупредителните съобщения, които прекъсват работещия VBA код, използваме класа Application.
Добавяне и запазване на нова работна книга с помощта на VBA в Microsoft Excel 2016 | За да добавяте и запазвате работни книги, използвайки VBA, използваме клас Работни книги. Работни книги. Добавянето лесно добавя нова работна книга, но …