Сложные формулы в экселе: Сложные формулы и простые решения в Excel

Excel очень крут / Хабр

Статья Хилла Уейна, автора почтовой рассылки «Компьютерные штучки», которая посвящена формальным методам и применению математики в программировании, истории и культуре программного обеспечения, философии и теории программирования, а также всяким экзотическим штукам и нишевым темам. В общем, рекомендуем.

В прошлом выпуске рассылки Уейн игрался с языком программирования J, который работает как арифметический калькулятор в консоли, и пошутил по ходу дела:

Я столько времени трачу на интерактивные вычисления, что даже не мог оторваться от этих функций… Может, мне просто хорошенько освоить Excel?

Пошутив про офисную программу, Хилл Уейн решил действительно посмотреть на неё поближе… и был поражён увиденным: «Это как найти сундук с сокровищами у себя на чердаке», — пишет он. И перечисляет некоторые новые функции современного Excel, о которых мало кто знает.

Вот кое-что из интересного.

В Excel теперь реальные возможности для программирования

Начнём с самой полезной функции, о которой, похоже, никто не знает. y) имя MAXEXP, то можно использовать MAXEXP в качестве обычной встроенной формулы. LET позволяет связывать в середине формулы новые имена, которые также могут ссылаться на предыдущие связывания. Очень простой пример: LET(x, 1, y, x+1, z, y+x) выдаёт 3. Примерно так же с LET работают все языки программирования. Конечно, синтаксис здесь немного странный, если вы не фанат S-выражений (sexp), но всё же. Полезная штука.

Заливка!

Предположим, у нас такая табличка.

Последний столбец — сложное преобразование текста

Нажимаем Ctrl+E:

Теперь в последних двух строках столбец заполнен по тому же образцу

Разве не здорово?!

Источники данных

Очень порадовала кнопка «Импорт из интернета»:

Она делает почти то, что вы ожидаете: вводите URL страницы — и оттуда HTML-таблица преобразуется в таблицу Excel.

Но есть нюанс (вот почему «почти»). Значения из интернета не просто копируются на лист. Excel сохраняет «запрос данных» по URL. Если изменить таблицу до неузнаваемости, то можно обновить запрос — и немедленно восстановить исходную таблицу вместе с любыми обновлениями из источника. То есть мы можем просто периодически (или автоматически) обновлять значения, которые подгружаются из интернета.

Это можно сделать практически с любым источником данных, включая XML, JSON и даже таблицы в PDF. В качестве эксперимента я импортировал в Excel старую налоговую декларацию в PDF — и он извлёк нужные данные.

Но прежде чем импортировать данные, можно изменить их на лету с помощью Power Query. Отфильтровать строки, заполнить пустые ячейки, разделить столбец по разделителю, выполнить базовую очистку данных. Все изменения моделируются как последовательность шагов преобразования. Это означает, что если обновить запрос, то все первоначальные преобразования применятся заново. Вот как это выглядит:

Очищаем CSV от плохих данных с помощью воспроизводимых шагов

Я уже говорил, что можно переименовать и комментировать каждый шаг? Если это недостаточно круто, как вам такое — можно форкнуть любой запрос, чтобы у разных запросов были общие начальные шаги! Например, вытянуть из интернета кучу данных, очистить их — а затем разложить на отдельные таблицы 2021 и 2022. 2, то это применится к транзитному массивуC1 — и теперь у нас будет D1=9, D2=49, D3=121.

А если написать C1 = A1:A3 + TRANPOSE(B1:B3), то получится следующее:

C D E
3 5 7
5 7 9
7 9 11

Это открывает некоторые забавные возможности в стиле APL. Недавно у меня была такая таблица данных:

A  B
12	
15 x

В этом случае значение в столбце B показывает на нечётные значения, которые не делятся на два. Я хотел вычислить сумму всех значений после деления на два. «Правильный» способ будет такой:

=SUM(A:A*IF(ISBLANK(B:B), 1, 0.5))

Но можно сделать и в стиле APL, например, так:

SUM(A:A*(1-0.5*(B:B="x")))

В инсайдерской сборке есть несколько формул, которые ещё больше превращают Excel в нечто APL-подобное.

А вот игра «Жизнь» в Excel:

LET(x, SUM(OFFSET(cell#,-1,-1,3,3)), (x=3)+cell#*(x=4))

Разные крутости

• Excel поставляется с решателем для логического программирования с учётом ограничений (constraint solver). Можно включить его в дополнениях программы (аддонах).
  
• Можно напрямую перемещать или копировать листы между разными файлами Excel.
  
• С помощью связанных типов данных можно вставить в ячейку позу йоги — и Excel скажет, насколько она сложная.
  
• Есть кнопка для создания 3D-глобусных карт по вашим данным. Видимо, по ним можно водить экскурсии, хотя я не пробовал (и вряд ли попробую).
  
• Есть кнопка, которая преобразует выборку данных в изображение.

---

В целом я рад, что потратил время на изучение Excel, потому что это очень интересный инструмент, который может оказаться полезным для меня. Даже удивительно, насколько он круче Google Sheets. Все обзоры в интернете говорят, что они примерно равны, но в Sheets даже нет приличных таблиц, не говоря уже о лямбдах или кнопке «Принять позу йоги» (здесь в словах автора чувствуется некая ирония — прим. пер.). Но зато Google Sheets бесплатный.

Вряд ли большинство пользователей, или даже «опытных пользователей», используют эти функции.

То же самое верно и для разработчиков, большинство из которых на самом деле не используют все возможности своих редакторов. Однако такие штуки интересно исследовать!

В любом случае, любопытно познакомиться с этим инструментом, если вы или ваша компания оплатила подписку на Office 365.

Сложение, вычитание, умножение и деление в Excel

Редактор таблиц Microsoft Excel имеет очень широкий набор возможностей для решения задач самой разной сложности в различных сферах деятельности. Именно благодаря этому Эксель стал таким популярным среди пользователей по всему миру. Одним из базовых навыков работы с программой является проведение простейших вычислений и математических операций. В этой статье подробно разберём, как выполнять сложение, вычитание, умножение и деление в Excel. Давайте же начнём! Поехали!

Математические операции выполняются без использования калькулятора

Все расчёты в Экселе основаны на построении простых формул, с помощью которых программа и будет производить вычисления. Для начала необходимо создать таблицу со значениями. Обратите внимание на то, что каждая ячейка таблицы имеет свой адрес, который определяется буквой и цифрой. Каждая буква соответствует столбцу, а каждая цифра — строке.

Начнём с самых простых операций — сложения и вычитания. Для сложения чисел можно использовать, так называемую функцию «Автосумма». Ей удобно пользоваться в случаях, когда необходимо посчитать сумму чисел, которые стоят подряд в одной строке, столбце либо в выделенной вами области. Чтобы воспользоваться этим инструментом, перейдите во вкладку «Формулы». Там вы обнаружите кнопку «Автосумма». Выделив участок таблицы со значениями, которые нужно сложить, кликните по кнопке «Автосумма». После этого появится отдельная ячейка, содержащая результат вычисления. Это был первый подход.

Второй подход заключается в том, что формула для расчёта вводится вручную. Допустим, перед вами стоит задача вычислить сумму чисел, разбросанных по таблице. Для этого сделайте активной (кликните по ней левой кнопкой мыши) ячейку, в которую желаете поместить результат вычисления. Затем поставьте знак «=» и по очереди вводите адрес каждой ячейки, содержимое которой нужно просуммировать, не забывая ставить знак «+» между ними. К примеру, у вас должно получиться: «=A1+B7+C2+B3+E5». После того как будет введён адрес последней ячейки, нажмите на клавиатуре «Enter» и вы получите сумму всех отмеченных чисел. Необязательно вводить каждый адрес вручную. Достаточно кликнуть по определённой ячейке и в поле для формул сразу отобразится её адрес, ставьте после него «+» и переходите к следующей.

Существует ещё один подход — использование функции «Специальная вставка». Этот способ удобен тем, что позволяет суммировать данные из нескольких отдельных таблиц, при условии, что все их графы одинаковые. Для начала создайте сводную таблицу, в которую вы будете вставлять скопированные данные. Выделите числа одной таблицы и вставьте их в сводную, далее поступите так же со значениями второй таблицы, только в этот раз кликните по ячейке правой кнопкой мыши и выберите пункт «Специальная вставка». В открывшемся окне в разделе «Вставить» отметьте «Значения», а в разделе «Операция» выберите сложить. В результате все данные просуммируются.

Вычитание в Excel выполняется таким же способом, как и сложение. Вам понадобится ввести формулу, указав необходимые ячейки, только вместо знака «+» между адресами ставится «–».

Чтобы умножить числа в Экселе, напишите формулу, отмечая нужные данные и ставя между ними знак «*». Формула будет иметь следующий вид: «=A3*A7*B2».

Деление производится аналогичным образом, только используется знак «/». Также вы можете выполнять несколько арифметический операций сразу. Формулы строятся по математическим правилам. Например: «=(B2-B4)*E8/(A1+D1)*D4». Построенная вами формула может быть любой сложности, главное, не забывать основные математические правила, чтобы расчёт был выполнен верно.

Владея навыками простых арифметических вычислений в программе Microsoft Excel, вы уже сможете упростить себе процесс решения некоторых задач и сэкономить время. Эксель позволяет решать сложные уравнения, выполнять инженерный и статистический анализ. Постепенно овладевая базовыми функциями и инструментами программы, вы научитесь выполнять всё больше операций в редакторе Excel. Пишите в комментариях помогла ли вам статья разобраться с возникшими вопросами и делитесь своим опытом с другими пользователями.

20 продвинутых функций и формул Excel (для профессионалов в Excel)

В Excel есть более 450 функций, которые могут делать множество замечательных вещей. Если вы использовали Excel хотя бы несколько дней, я уверен, что вы слышали о таких функциях, как ВПР, СУММЕСЛИ, СЧЁТЕСЛИ и так далее.

И что еще более удивительно, так это то, что одна функция Excel — формула, состоящая из двух, трех или более функций.

С помощью комбинации функций вы можете создавать некоторые расширенные формулы Excel, которые могут делать невероятно сложные вещи одним нажатием клавиши.

Итак, если вы такой же поклонник Excel, как и я, я уверен, вам понравится эта статья. В этой статье я расскажу о 20 расширенных функциях Excel, которые вы должны знать.

Я также поделюсь примерами некоторых расширенных формул, которые вы можете создать с помощью этих расширенных функций.

Небольшое примечание о расширенных функциях/формулах. Под расширенными я подразумеваю функции, которые требуют некоторого ноу-хау и обычно не используются базовыми пользователями Excel (такие как СУММ или СЧЁТ).

Итак, приступим!

В этом руководстве рассматриваются:

Функция XLOOKUP

XLOOKUP — это король расширенных функций (Microsoft не дала это название ни одной функции, но я уверен, что никто не заслуживает этого больше, чем эта функция).

Это новая функция, доступная в Excel 2021 и Excel для Microsoft 365. Это означает, что люди, использующие более ранние версии Excel, не смогут ее использовать.

XLOOKUP — это усовершенствованная версия VLOOKUP, в которой устранены некоторые недостатки функции VLOOKUP.

Вы можете использовать эту функцию в ситуациях, когда вам нужно просмотреть список значений в строках или столбцах и выяснить, содержит ли список искомое значение (называемое значением поиска).

Это также позволяет найти значение, а затем вернуть любое другое значение из той же строки или столбца.

Если вы все еще не понимаете, что делает эта функция, подумайте вот о чем: вы идете в недавно открывшееся кафе рядом с вашим домом и хотите что-нибудь поесть. так что вы получаете их меню и начинаете просматривать меню сверху вниз.

Когда вы определяете товар, который хотели бы заказать, вы смотрите направо, чтобы проверить цены.

И это то, что XLOOKUP делает для вас. Он может сканировать список, определять положение искомого значения и возвращать значение из любого соответствующего столбца в той же строке.

Есть много удивительных вещей, которые вы можете сделать с помощью XLOOKUP, и я рассказал о некоторых из них в видео ниже:

Примечание : Если вы используете XLOOKUP и делитесь файлом с кем-то, кто использует более старую версию Excel, формулы не будут работать в их книгах. Так что в этом случае лучше не использовать XLOOKUP 9.0003

Функция ВПР

ВПР была бесспорным королем функций Excel, пока не появилась функция ВПР. Поскольку многие люди до сих пор не используют Excel 2021 или Excel для Microsoft 365, у них нет доступа к XLOOKUP.

Итак, для них ВПР — это функция, которую они должны знать. Для продвинутых пользователей формул полное понимание использования ВПР может действительно изменить их работу.

Как и XLOOKUP, функция VLOOKUP также просматривает список в столбце и может возвращать совпадающее значение поиска или любое значение из любого столбца в той же строке.

Если вы хотите узнать, как лучше всего использовать функцию ВПР, ознакомьтесь с подробным руководством по этой расширенной формуле: 10 примеров ВПР для начинающих и опытных пользователей

Вот несколько примеров расширенных формул использования ВПР:

• Как использовать функцию ВПР с несколькими критериями
• Использование VLookup для получения последнего числа в списке в Excel
• Избегайте вложенных функций ЕСЛИ в Excel… ВПР для спасения
• Используйте ЕСЛИОШИБКА с функцией ВПР, чтобы избавиться от ошибок #Н/Д

Функция ИНДЕКС / ПОИСКПОЗ

Сочетание функций ИНДЕКС и ПОИСКПОЗ может творить чудеса.

Поэтому, если вы хотите узнать о расширенных формулах в Excel, вы должны узнать, как работает эта волшебная комбинация.

До появления XLOOKUP многие продвинутые пользователи Excel предпочитали использовать INDEX/MATCH вместо VLOOKUP (и эта комбинация может устранить некоторые недостатки VLOOKUP). Раньше велись жаркие споры о том, какая формула ВПР лучше, чем ИНДЕКС/ПОИСКПОЗ.

Даже с XLOOKUP я настоятельно рекомендую вам учиться с помощью ИНДЕКС/ПОИСКПОЗ.

Хотя по отдельности эти функции не очень полезны, в сочетании они представляют собой мощный инструмент для продвинутых пользователей формул Excel.

Вы можете прочитать следующее руководство, в котором я рассказываю о некоторых основных и расширенных аспектах использования формулы ИНДЕКС/ПОИСКПОЗ – Комбинация функций ИНДЕКС и ПОИСКПОЗ в Excel (10 простых примеров) т. е. XLOOKUP, VLOOKUP и INDEX/MATCH), СУММПРОИЗВ — одна из тех функций, которые нравятся продвинутым пользователям Excel.

Его название может ввести вас в заблуждение, заставив думать, что это математическая или статистическая формула, которая может складывать несколько чисел, но это нечто большее.

Первоначально команда Excel хотела, чтобы эта функция выполняла вычисления, где она могла бы дать вам сумму произведения в двух столбцах.

Хотя это достойный вариант использования, пользователи Excel проявили хитрость и обнаружили, что это может быть намного больше, чем это.

Опытные пользователи Excel часто используют формулу СУММПРОИЗВ для условных сумм или условных вычислений.

Предположим, у вас есть набор данных о продавцах, их регионе и количестве продаж. Затем вы можете использовать СУММПРОИЗВ для:

• Добавлять только стоимость продаж для определенного региона (или более одного региона)
• Добавлять только значения продаж выше определенного значения
• Добавление значений продаж выше или ниже определенного значения (условие ИЛИ)

Вы можете посмотреть видео ниже, где я показываю примеры СУММПРОИЗВ:

Примечание. Функция СУММПРОИЗВ довольно популярна среди пользователей Excel, которые создают информационные панели и отчеты. В Excel появилась новая функция СУММЕСЛИМН, которая может выполнять часть функций функции СУММПРОИЗВ, но не все. Поэтому, если вы хотите изучить сложные формулы Excel, я бы посоветовал вам изучить и эту.

Функции СУММЕСЛИ/СЧЁТЕСЛИ и СУММЕСЛИМН/СЧЁТЕСЛИ

Я объединил эти четыре функции вместе, так как они имеют одинаковый принцип работы.

Вы можете использовать это для условного суммирования или подсчета из вашего набора данных.

Например, если у вас есть список имен и вы хотите быстро подсчитать, сколько раз конкретное имя появляется в списке, вы можете использовать формулу СЧЁТЕСЛИ.

Аналогичным образом, если у вас есть набор данных, в котором есть имена торговых представителей и объемы их продаж, и вы хотите получить сумму всех продаж, совершенных конкретным торговым представителем, вы можете использовать функцию СУММЕСЛИ.

А если вам нужно проверить несколько условий, вы можете использовать формулы СЧЁТЕСЛИМН или СУММЕСЛИМН.

Например, если у меня есть набор данных со столбцом «Дата», столбцом «Имя торгового представителя» и их значениями «Продажи», я могу использовать формулу СУММЕСЛИМН, чтобы получить сумму всех продаж, совершенных конкретным торговым представителем в определенном месяц.

Поскольку нужно проверить два условия (имя торгового представителя и дату), вам нужно использовать формулу СУММЕСЛИМН. Точно так же, если у вас есть несколько условий для проверки при получении числа в наборе данных, используйте формулу СЧЁТЕСЛИМН.

Функция ФИЛЬТР

ФИЛЬТР — это новая функция в Excel 2021 и Excel для Microsoft 365, и она потрясающая.

Как следует из названия, вы можете использовать его для фильтрации набора данных в Excel и извлечения результатов.

Это была одна из наиболее востребованных функций в Excel, поскольку обходной путь без функции ФИЛЬТР довольно длинный и сложный.

В качестве простого примера, демонстрирующего утилиту функции ФИЛЬТР, предположим, что у вас есть набор данных с именами торговых представителей, их регионами/странами и объемами продаж.

С помощью функции ФИЛЬТР вы можете быстро отфильтровать все записи для любой конкретной страны. И чтобы сделать его еще более мощным, вместо того, чтобы жестко кодировать название страны в формуле, вы можете поместить его в ячейку и ссылаться на эту ячейку в формуле.

Таким образом, вы можете просто изменить название страны в ячейке, и формула мгновенно выдаст вам все записи для этой страны.

Ниже видео, которое я сделал, показывающее некоторые продвинутые примеры функции ФИЛЬТР.

Функция SORT & SORTBY

Снова две новые функции в Excel 2021 и Excel для Microsoft 365.

Так же, как и функция ФИЛЬТР, они были очень необходимы, поскольку не было простого способа динамической сортировки данных с помощью формулы .

До введения этой формулы большая часть сортировки данных выполнялась с использованием функции сортировки в Excel, которая дает вам статический результат.

Это означает, что если вы измените исходный набор данных, результирующие отсортированные данные не будут обновляться, и вам придется сортировать их снова.

Но не больше.

Используя функцию СОРТИРОВКИ, вы можете быстро отсортировать любой набор данных на основе указанной строки или столбца. Это также дает вам возможность выбирать порядок сортировки (например, по возрастанию или по убыванию).

Итак, если у вас есть набор данных и вы хотите отсортировать эти данные на основе одного определенного столбца, вы можете использовать функцию СОРТИРОВКИ.

Функция SORTBY предоставляет немного больше возможностей при сортировке данных по формуле. С помощью этой функции вы можете сортировать по нескольким столбцам.

Например, если у вас есть 2 столбца, где первый столбец содержит название региона (например, Восток, Запад, Север, Юг), а другой столбец содержит значения продаж, вы можете использовать функцию СОРТИРОВАТЬ ПО сначала отсортируйте это по названию региона, а затем по стоимости продаж.

 Читайте также: Как сортировать по цвету в Excel (менее чем за 10 секунд) 

УНИКАЛЬНАЯ функция

УНИКАЛЬНАЯ Снова новая функция, доступная только пользователям Microsoft 365.

Так же, как функции СОРТИРОВКИ и СОРТИРОВКИ, эта функция была крайне необходима.

Как следует из названия, функция UNIQUE предоставит вам список уникальных имен или элементов в наборе данных.

До этой функции это можно было сделать с помощью функции удаления дубликатов или функции расширенного фильтра.

Но даже с этими встроенными функциями, с большими наборами данных и большим количеством условий раньше это становилось сложным.

И, конечно же, раньше результат был статическим, а это означало, что в случае изменения исходных данных результирующие данные не будут обновляться, и вам придется повторять процесс заново.

Функция TEXTJOIN

TEXTJOIN — это относительно новая функция, представленная в Excel 2019. Редактирование решает основную проблему.

TEXTJOIN позволяет быстро объединять содержимое выбранного диапазона ячеек без создания формулы конкатенации или использования знака & миллион раз.

Итак, если у вас есть несколько элементов в разных ячейках в строке или столбце, и вы хотите быстро их объединить, вы можете сделать это с помощью одной единственной формулы.

Это также позволяет указать разделитель, например пробел или запятую, чтобы все объединенные ячейки имели этот разделитель между содержимым ячеек.

Честно говоря, это очень простая формула, которую может использовать даже самый простой пользователь Excel, но если вы продвинутый пользователь Excel, вам непростительно использовать конкатенацию и объединение ячеек (вам лучше знать)

 Читайте также: Как объединить имя и фамилию в Excel 

Функция IFS

IFS — еще одна функция, которая была введена в Excel 2019 (слишком поздно, если вы спросите меня).

Мне нравится функция ЕСЛИ, и за эти годы она сделала для меня много тяжелой работы. Но у чувака есть ограничения.

Начнем с того, что вы можете проверить только два условия, поэтому, если вам нужно проверить три или более условий, вы получите длинную вложенную формулу if.

И не дай бог, если придется проверять 5, 10 или 15 условий, представляю какой ужас будут у ваших формул.

Чтобы уменьшить нагрузку на перегруженную формулу ЕСЛИ, Microsoft представила функцию ЕСЛИ, которая может проверять несколько условий.

Так что, если вы учитель, оценивающий студентов, или менеджер, которому нужно решить, какую комиссию ваши торговые представители получат в зависимости от объема их продаж, вам не нужно создавать длинные запутанные формулы ЕСЛИ, вместо этого вы можете использовать IFS функция.

 Читайте также: Тестирование нескольких условий с помощью функции Excel IFS 

Функция ЕСЛИОШИБКА

Вы не можете называть себя продвинутым пользователем формул Excel, если ваш лист Excel полон ошибок, таких как #N/A или #REF! или #ДЕЛ/0!.

Хотя получение этих ошибок иногда может быть вне вашего контроля, ЕСЛИОШИБКА позволяет вам обрабатывать эти ошибки, заменяя их чем-то более значимым.

Например, если вы используете формулу ВПР и она не может найти искомое значение, она вернет ошибку NA. с помощью формулы ЕСЛИОШИБКА вы можете заменить эту ошибку NA чем-то более значимым, например, «Искомое значение не найдено» или «Данные недоступны».

 Читайте также: Использование ЕСЛИОШИБКА с ВПР для избавления от ошибок #Н/Д 
 
 Функция OFFSET позволяет сместить ссылку на указанное количество строк или столбцов. 
 
 Например, я могу использовать функцию OFFSET, чтобы сместить ссылку A1 на две строки и два столбца, чтобы получить C3 (которая является ссылкой на ячейку, расположенную двумя строками ниже и двумя столбцами справа) 
 
 Если вам интересно узнать, как работает функция СМЕЩ, у меня есть подробное руководство, в котором я привожу несколько примеров использования формулы СМЕЩ. 
  
 Одна важная вещь, которую вам нужно знать о функции OFFSET, это то, что она изменчива, а это означает, что каждый раз, когда в вашем рабочем листе происходит изменение, функция будет пересчитываться. 
 
 Хотя вы не заметите никаких изменений, если используете функцию OFFSET в нескольких ячейках, но если вы используете внутренний большой набор данных или всю строку или столбец, это может замедлить работу вашего файла Excel. 
 
 Функция НАЙТИ / ПОИСК (с подстановочными знаками) 
 
 НАЙТИ и ПОИСК — это две похожие функции, которые позволяют вам искать заданный текст в ячейке и возвращать начальную позицию строки, которую вы искали. 
 
 Вы не можете утверждать, что являетесь опытным пользователем формул Excel, если не знаете, как использовать все возможности функций НАЙТИ/ПОИСК. 
 
 Хотя сами по себе эти функции не очень полезны, в сочетании с другими текстовыми функциями ВЛЕВО/ВПРАВО/СРЕДНЯЯ вы можете выполнять серьезные манипуляции с текстом.  
 
 Например, если у вас есть столбец, полный адресов электронной почты, и вы хотите получить идентификатор пользователя, а не все, что находится после символа @, вы можете сделать это с помощью функции НАЙТИ или ПОИСК. 
 
 Чтобы сделать это, нам сначала нужно использовать одну из этих функций, чтобы найти позицию добавления скорости в ячейке, и как только вы получите эту позицию, вы можете использовать это значение в левой функции, чтобы извлечь все, что есть слева от символа @. 
 
  Обратите внимание, что вы не можете использовать подстановочные знаки в формуле НАЙТИ, но вы можете использовать их в формуле ПОИСК  
 
 ВПРАВО / ВЛЕВО / СРЕДНЯЯ Функция 
  
 Работа с текстовыми данными является обычной частью повседневной работы многих продвинутых пользователей Excel. 
  
 Несмотря на то, что в Excel доступно множество вариантов работы с текстом (например, «Текст в столбцы» или «Power Query»), многие опытные пользователи Excel по-прежнему предпочитают использовать формулу.  
 
 Функции RIGHT, LEFT и MID позволяют извлекать часть текстовой строки. 
 
 Например, если у вас есть идентификатор электронной почты, такой как [email protected], вы можете использовать функцию ВЛЕВО, чтобы извлечь только имя пользователя из этого идентификатора электронной почты, или вы можете использовать функцию ПРАВО, чтобы извлечь домен имя (которое будет email.com). 
 
 С помощью функции MID вы можете извлечь текст из середины текстовой строки. Например, если я хочу извлечь доменное имя между @ и . (точка), я могу легко сделать это с помощью функции MID. 
 
 Эти текстовые функции не очень полезны сами по себе, но когда вы комбинируете их с другими формулами, такими как ДЛСТР или НАЙТИ, вы можете делать с ними невероятно сложные вещи. 
 
 Вот несколько расширенных примеров формул с использованием функций ВПРАВО/ВЛЕВО/СРЕДНЯЯ: 
 
 
 Удалить символы слева в Excel (простые формулы) 
 
 Извлечение фамилии в Excel (5 простых способов) 
 
 Как извлечь первое слово из текстовой строки в Excel (3 простых способа) 
 
 Как извлечь подстроку в Excel (используя формулы TEXT) 
 
  
 ЗАМЕНИТЬ / ПОДСТАВИТЬ Функция 
 
 ЗАМЕНИТЬ и ПОДСТАВИТЬ, как следует из названия, позволяет вам найти какой-либо текст в свободном тексте и заменить или заменить его тем, что вы хотите.  
 
 С помощью функции REPLACE вы можете указать начальную позицию и общее количество символов, которые вы хотите заменить указанной текстовой строкой. 
 
 С другой стороны, с помощью функции ПОДСТАВИТЬ вы можете указать текстовую строку, которую вы хотите заменить другой текстовой строкой. Например, если у вас есть набор данных, в котором вы хотите заменить слово «Private» на «Pvt», вы можете легко сделать это с помощью функции ПОДСТАВИТЬ. 
 
 Хотя эти функции весьма полезны сами по себе, они часто используются с другими текстовыми функциями, такими как LEN, TRIM или RIGHT/LEFT/MID 
 
 Функция IMAGE 
 
 IMAGE — это совершенно новая функция в Microsoft 365 ( по крайней мере на момент написания этой статьи). 
 
 Долгое время я хотел, чтобы в Excel была функция, позволяющая использовать URL-адрес и вставлять изображение с этого URL-адреса в ячейку Excel. 
 
 Наконец-то мое желание сбылось. 
 
 С помощью функции ИЗОБРАЖЕНИЕ вы можете использовать URL-адрес изображения в ячейке, и оно будет вставлять изображение в саму ячейку.  
 
 Раньше, чтобы поместить изображение в ячейку, нужно было вручную добавить изображение на рабочий лист, а затем расположить его так, чтобы оно помещалось в ячейку. 
 
 Хотя я не думаю, что многие обычные пользователи Excel будут использовать эту функцию, некоторые опытные пользователи Excel могут использовать ее при создании информационных панелей. 
 
 Например, вы можете создать шаблон, в котором функция извлекает изображение продукта или логотип компании на основе URL-адреса. 
 
 Рискуя показаться нелояльным по отношению к Excel, мы должны поблагодарить Google Sheets за это. Функция ИЗОБРАЖЕНИЕ уже много лет является частью Google Таблиц, и многие пользователи Excel (такие как я) просили хорошо добавить ее в Excel. Итак, в интересах человечества Excel скопировал эту функцию из Google Sheets (и ура, что они сделали) 
 
 МАЛЕНЬКИЙ / БОЛЬШОЙ Функция 
 
 МАЛЕНЬКИЙ и БОЛЬШОЙ — это две простые формулы, которые делают одну простую вещь — дают вам K-е наименьшее или наибольшее значение из набора данных.  
 
 Например, я могу использовать функцию НАИМЕНЬШИЙ, чтобы получить второе или третье наименьшее значение из диапазона ячеек. 
 
 И зачем включать эти две формулы в мой самый продвинутый учебник по формулам, потому что я использовал их в ситуациях, когда никакая другая функция не могла сделать то, что они могли сделать. 
 
 Например, я использовал их для управления выбросами в своем наборе данных, использую их с формулами, которые возвращают массив (например, функция ИНДЕКС), и получаю из него первые три или пять первых значений. 
 
 Я согласен с тем, что эти две формулы имеют ограниченное применение в расширенных формулах Excel, но я все же призываю вас держать их в заднем кармане на случай, если они вам понадобятся. 
 
 Читайте также: Как найти выбросы в Excel (и как с ними справиться) 
 Функция ПОСЛЕДОВАТЕЛЬНОСТЬ 
 ПОСЛЕДОВАТЕЛЬНОСТЬ — это также новая функция, доступная в Excel 2021 и Excel с Microsoft 365.
 Функция ПОСЛЕДОВАТЕЛЬНОСТЬ заполняет диапазон ячеек последовательностью чисел. вы можете указать, сколько строк и столбцов вы хотите заполнить, начальный номер и шаг между каждым номером.
 Например, если я введу приведенную ниже формулу в ячейку A1, она заполнит ячейки от A1 до A10 последовательностью чисел, начиная с 1 до 10.
 =ПОСЛЕДОВАТЕЛЬНОСТЬ(10,1,1,1) 
 Хотя ранее в Excel были и другие способы сделать это (например, вручную ввести данные, а затем использовать дескриптор заполнения или метод заполнения ряда), наличие формулы делает это не только более удобным, но и необходимым в некоторых ситуациях.
 Один из расширенных вариантов использования этой функции может быть полезен при создании календаря, в котором вы хотите заполнить сетку ячеек последовательностью чисел.
 Вы можете проверить шаблон календаря Excel, который я создал с помощью этой функции.
 Но что более важно, я часто сталкивался с ситуациями, когда мне нужна была последовательность чисел как часть моей формулы. Раньше мне приходилось полагаться на такие хитрости, как использование формул СТРОКИ или СТОЛБЦЫ, теперь я могу легко сделать это с помощью функции ПОСЛЕДОВАТЕЛЬНОСТЬ.
 Читайте также: 7 быстрых и простых способов пронумеровать строки в Excel Когда я начал использовать Excel и изучал эти формулы, я думал о них как о еще одной формуле, которая может быть полезна в некоторых ситуациях. 
 
 Но чем больше я узнаю об этих функциях, тем больше я верю, что вам нужно знать, как правильно их использовать, чтобы называться продвинутым пользователем формул. 
 
 Сначала эти формулы делают простые вещи: 
 
 
 РАБДЕНЬ — вычисляет дату после указанного количества рабочих дней 
 
 ЧИСТРАБДНИ — общее количество рабочих дней между двумя заданными датами 
 
 
 А так как у нас в неделе есть рабочие и нерабочие дни (как и праздничные дни), то эти функции автоматически учитывают выходные дни, а также можно указать праздничные дни, которые будут игнорироваться. 
 
 И поскольку этого было недостаточно, поскольку у многих людей выходные дни отличались от субботы и воскресенья, Excel выпустил две дополнительные функции: РАБДЕНЬ. МЕЖД и ЧИСТРАБДНИ.МЕЖД. 
 
 Эти международные функции позволяли пользователям выбирать дни, которые будут считаться выходными. 
 
 Но подождите, это еще не все. 
 
 Что делать, если вы работали неполный рабочий день и хотите рассчитать данные после определенного количества рабочих дней или общее количество рабочих дней между двумя заданными датами? 
 
 Вы тоже можете это сделать. 
 
 Международные версии этих функций позволяют указать все рабочие и нерабочие дни и тогда он будет делать расчет на их основе. 
 
 Например, вы можете указать, что у вас рабочие только понедельник и четверг, а все остальные дни нерабочие. 
 
 Читайте также: Как рассчитать количество дней между двумя датами в Excel 
 Функция TEXTAFTER / TEXTBEFORE 
 Хотя в Excel уже было несколько действительно удивительных текстовых функций, они добавили TEXTBEFORE и TEXTAFTER в версию Excel для Microsoft 365, чтобы сделать вещи еще проще.
 Раньше, если нужно было извлечь имя из полного имени, приходилось использовать комбинацию функции НАЛЕВО с функцией НАЙТИ. Но с помощью функции TEXTBEFORE вы можете легко извлечь весь текст перед заданным конкретным разделителем.
 Аналогично, с помощью TEXTAFTER вы можете извлечь весь текст после заданного разделителя вместо использования комбинации формул.
 Я недавно начал использовать эти две функции, и я могу сказать вам, что они являются большим улучшением по сравнению с существующими текстовыми функциями и в значительной степени упрощают работу.
 В этом руководстве я рассмотрел  20 лучших расширенных функций Excel , основываясь на моем понимании и многолетнем опыте работы с Excel.
 Хотя этот список может немного отличаться для вас в зависимости от того, какую работу вы выполняете, в целом, если вы освоите эти 20 расширенных функций Excel и узнаете, как использовать их в сложных формулах, вы будете намного впереди.
 Надеюсь, эта статья была вам полезна.
  Другие учебные пособия по Excel, которые также могут оказаться полезными: 
 БЕСПЛАТНОЕ онлайн-обучение Excel (более 12 часов) | Изучите Excel (базовый/продвинутый)
 10 расширенных диаграмм Excel, которые можно использовать в повседневной работе
 Расширенный фильтр Excel — полное руководство с примерами
 Как скрыть или показать панель формул в Excel?
 Удалить символы слева в Excel
 Формула и функция в Excel — в чем разница?
 Как превратить сложные формулы в простые в использовании пользовательские функции с помощью LAMBDA() в Excel 
 Как превратить сложные формулы в простые в использовании пользовательские функции с помощью LAMBDA() в Excel 
 В электронных таблицах Excel сложно вводить сложные формулы без ошибок. Функция LAMBDA() Microsoft Excel может упростить поиск таких ошибок.
 Мы можем получать компенсацию от поставщиков, которые появляются на этой странице, с помощью таких методов, как партнерские ссылки или спонсируемое партнерство. Это может повлиять на то, как и где их продукты будут отображаться на нашем сайте, но поставщики не могут платить за влияние на содержание наших обзоров. Для получения дополнительной информации посетите нашу страницу «Условия использования». 
 Изображение: Монтичелло/Adobe Stock
 Функции LAMBDA являются новыми для Microsoft Excel. С помощью функций LAMBDA вы можете превратить сложный расчет в простую функцию на уровне листа. Вы должны знать сложные вычисления, но они подвержены ошибкам и их трудно поддерживать; например, если что-то изменится, вы можете изменить несколько формул на уровне листа. Почему бы вместо этого не использовать LAMBDA()? Вы вводите сложный расчет один раз, даете ему имя функции, и все.
 В этом руководстве я объясню, что такое функция LAMBDA и как использовать новую функцию Excel LAMBDA(). Я предполагаю, что у вас есть хотя бы базовые навыки работы с Excel. Как только вы научитесь использовать функции LAMBDA, ожидайте, что вы будете часто использовать их в своих электронных таблицах Excel.
  СМ.:   Политика установки программного обеспечения   (TechRepublic Premium) 
 Я использую Microsoft 365 в 64-разрядной системе Windows 10. Функция Excel LAMBDA() доступна только в Microsoft 365 и Excel для Интернета. Я предполагаю, что у вас есть базовые навыки работы с Excel. Для вашего удобства вы можете скачать демонстрационный файл в формате .xlsx. В этой статье предполагается, что у вас есть базовые навыки работы с Excel, но вы должны быть в состоянии следовать инструкциям для достижения успеха.
 Что такое функция LAMBDA()? 
 Обязательная к прочтению информация о Windows 
 Microsoft PowerToys 0.69.0: анализ нового приложения Registry Preview
 Обновление Windows 11 переносит Bing Chat на панель задач
 Microsoft предлагает Windows 11 для HoloLens 2
 Как подключить беспроводную клавиатуру Apple к Windows 10
 Функция Excel LAMBDA() аналогична пользовательской функции VBA, но без VBA. Короче говоря, функция LAMBDA() в Excel позволяет создавать пользовательские и многократно используемые функции и давать им осмысленные имена, используя форму:
.
 LAMBDA([параметр1, параметр2, …,] вычисление)
, где аргументы необязательных параметров — это значения, которые вы передаете функции — аргументы также могут ссылаться на диапазон. Аргумент вычисления — это логика, которую вы хотите выполнить. Как только все будет правильно, вы сохраните все это, используя диспетчер имен Excel, чтобы дать ему имя. Чтобы использовать эту функцию, вы просто вводите имя функции на уровне листа, как и любую из встроенных функций Excel.
 Все это прекрасно, но есть еще кое-что. Excel LAMBDA() поддерживает массивы в качестве аргументов, а также может возвращать результаты в виде типов данных и массивов. Обычному пользователю может не понадобиться такая большая мощность, но вы должны знать, что она доступна.
 Прежде чем мы продолжим, есть несколько правил, которые вы должны соблюдать при создании функции LAMBDA():
 LAMBDA() поддерживает 253 параметра, что должно быть достаточно для большинства пользователей.
 LAMBDA() соответствует соглашениям об именах и именах параметров Excel, за одним исключением: нельзя использовать символ точки (.).
 Как и другие функции, LAMBDA() вернет значение ошибки, если это необходимо.
 Как создать функцию LAMBDA() в Excel 
 Создать функцию LAMBDA() в Excel довольно просто. Используя LAMBDA(), вы вводите параметры и аргументы расчета, используя переменные. Используя функцию «Определить имена» в Excel, вы называете функцию и вводите функцию LAMBDA(), и все.
 Самый простой способ понять, что функции Excel LAMBDA() могут сделать для вас, — это начать с простого. Например, Excel предлагает функцию СУММ(), но не функцию ВЫЧИТАТЬ(). Вы по-прежнему можете вычитать, но для начала это простой расчет:
 Введите тестовый расчет =B3-C3 в любую ячейку вне таблицы. Если он возвращает ожидаемые результаты, продолжайте. Если нет, продолжайте работать над расчетом, пока он не станет правильным.
 Перейдите на вкладку "Формулы" и нажмите "Определить имя".
 В появившемся диалоговом окне введите  SUBTRACTYL  в поле Name. Суффикс L идентифицирует функцию как функцию LAMBDA(), но вы можете использовать любое соглашение, которое вам нравится.
 Пока не изменяйте параметр Scope, но вы можете ограничить LAMBDA() листом, а не рабочей книгой.
 Введите комментарий, описывающий функцию LAMBDA(), например,  Вычитает два числа  .
 В элементе управления Относится к введите функцию LAMBDA(),  =LAMBDA(a,b,a-b)  (  Рисунок A  ).
 Нажмите кнопку ОК.
  Рисунок А 
 Создайте LAMBDA(), дав ей имя.
 Переменные a и b сначала идентифицируют оцениваемые значения, а a-b — вычисление. Поскольку переменных всего две, функция SUBTRACTL оценивает только два значения или два диапазона.
 Теперь вы готовы использовать функцию LAMBDA() SUBTRACTL().
  ПОСМОТРЕТЬ: Язык программирования Python: это обучение положит начало вашей карьере программиста (Академия TechRepublic) 
 Как вызвать функцию Excel LAMBDA() 
 Вы будете использовать функции LAMBDA() так же, как и функции Excel. Чтобы продемонстрировать, введите функцию и обратитесь к значениям, показанным в  Рисунок B  ,  = ВЫЧИТАТЬ(B3,C3)  . (Точка является грамматической и не является частью функции.) B3 и C3 удовлетворяют переменным a и b соответственно. При вычислении b вычитается из a в порядке, указанном на уровне функции.
  Рисунок В 
 Используйте функцию ВЫЧИТАТЬ() для вычитания значений.
 При работе с новой функцией LAMBDA() вы можете проверить ее, вставив исходную формулу. В данном случае это =B3-C3. Как видно из  Рисунок C  , проверочное выражение и LAMBDA() возвращают одинаковые результаты. Несмотря на то, что вы проверили вычисление перед тем, как создать SUBTRACTL(), рекомендуется проверить его еще раз. Если вы используете объект Table (как и я), Excel использует структурированные ссылки =[@Value1]-[@Value2].
  Рисунок С 
 Проверка новой функции LAMBDA() — хорошая идея.
 Стоит отметить, что вы всегда можете явно передать значения. Например, функция SUBTRACTL(182,138) возвращает 44.
 Поскольку функция LAMBDA() в Excel использует язык формул Excel, ее поведение предсказуемо. Например, когда вы начинаете вводить функцию, вводя всего несколько символов, SUBTRACTL() появляется в списке автозаполнения, как показано на  рис. D . Обратите внимание, что Excel также отображает описание, которое вы ввели при присвоении ему имени. Единственное, что он пока не может сделать, это показать аргументы, как это сделала бы встроенная функция.
  Рисунок D 
 Excel реагирует на LAMBDA(), как и любая другая встроенная функция Excel.
 Прежде чем мы рассмотрим более разумную и сложную функцию LAMBDA(), давайте рассмотрим несколько ошибок, которые могут возникнуть при ее использовании.
 Об ошибках Excel LAMBDA() 
 Функции LAMBDA() так же подвержены ошибкам, как и встроенные функции. Вы должны передать ожидаемые параметры, а логика вычислений должна быть правильной. В противном случае вы могли бы увидеть ошибки. Давайте посмотрим на некоторые из возможностей:
 #ЗНАЧ!: Если вы видите это значение ошибки, проверьте переданные аргументы — вы передали неверный номер.
 #ЧИСЛО!: Проверьте циклическую ссылку, если видите это значение ошибки.
 #ИМЯ!: проверьте фактическое имя функции, которое вы ввели, на наличие опечатки.
 Для большинства из нас #ЦЕННОСТЬ! Это наиболее вероятно, и его легко устранить. Теперь давайте рассмотрим более сложную LAMBDA().
  SEE: Почему Microsoft Lists — это новый Excel (TechRepublic) 
 Как использовать Excel LAMBDA() для возврата первых 
 n  значений
 Вы многому научились, и теперь пришло время использовать то, что вы узнали, для создания полезной функции LAMBDA(). Вычисление первых  n  значений в столбце является обычной задачей и требует определенных специальных знаний. Раньше можно было использовать расширенный фильтр, выражение или сводную таблицу. Кроме того, вы можете использовать правило условного форматирования, чтобы выделить эти значения в источнике. В статье Как вернуть верхние или нижние n записей без фильтра или сводной таблицы в Excel не используется ни одна из них, и она опирается на функции обработки массивов Excel, СОРТИРОВКА () и ПОСЛЕДОВАТЕЛЬНОСТЬ ().
 Теперь мы решим эту проблему, используя функцию Excel LAMBDA(). Мы также будем использовать функцию массива SEQUENCE() в Excel в форме
.
 =ЛЯМБДА(значения, n, НАИБОЛЬШИЙ(значения, ПОСЛЕДОВАТЕЛЬНОСТЬ(n)))
  На рис. E  указанная выше функция показана в G3. Вы можете сказать, что это функция массива, потому что результаты имеют синюю рамку. Давайте разберем его, чтобы вы могли увидеть, как это работает:
 При входе в функцию выберите значения Value1 — не включая ячейку заголовка. Это удовлетворяет аргументу значений.
 Введите 3, удовлетворяя аргументу n.
 SEQUENCE(n) — это функция массива, которая определяет количество возвращаемых  строк , но в данном случае это числа из Table1[Value1].
 Функция НАИБОЛЬШИЙ() — это встроенная функция Excel, которая возвращает  n -е наибольшее значение в диапазоне.
  Рисунок Е 
 TOPnL() возвращает первые n значений в диапазоне чисел.
 LAMBDA() передает ссылку на числа, которые вы хотите оценить (значения), и число, которое вы хотите, чтобы массив возвращал (n). Вычислительная часть LARGE(значения, ПОСЛЕДОВАТЕЛЬНОСТЬ(n)) выполняет свою работу, но LAMBDA() упрощает ее использование. Это одна из замечательных особенностей функций LAMBDA() — пользователям не нужны специальные знания, чтобы выполнять свою работу.
 Теперь, когда вы знаете, как это работает, давайте создадим его:
 Перейдите на вкладку "Формулы" и нажмите "Определить имя".
 В появившемся диалоговом окне введите  TOPnL  в поле Имя. Суффикс L идентифицирует функцию как функцию LAMBDA(), но вы можете использовать любое соглашение, которое вам нравится.
 Пока не изменяйте параметр Scope, но вы можете ограничить LAMBDA() листом.
 Введите комментарий, описывающий функцию LAMBDA(), например,  Возвращает первые  n  значений как LAMBDA()  .
 В элементе управления Относится к введите функцию LAMBDA(),  =LAMBDA(значения, n, НАИБОЛЬШИЙ(значения, ПОСЛЕДОВАТЕЛЬНОСТЬ(n)))  . (  Рисунок F  ).
 Нажмите кнопку ОК.
  Рисунок F 
 Назовите функцию LAMBDA(), чтобы вы могли использовать ее на уровне листа.
 После ввода TOPnL() в G3 (  Рисунок E  ), скопируйте его в h4 и посмотрите, что произойдет. Ссылка является относительной, поэтому вы получаете второй массив из трех верхних значений в Value2, как вы можете видеть в 9.0041 Рисунок G  .
  Рисунок G 
 Копировать ТОПнЛ; это будет работать как большинство встроенных функций!
 Дополнительные сведения о возврате верхних значений  n  см. в следующих статьях TechRepublic:
 Как выделить первые n значений на листе Microsoft Excel
 Как отобразить первые n записей в сводной таблице Excel с помощью встроенных параметров
 Сьюзан Харкинс
 Опубликовано:  11 мая 2022 г.