Ну, вот мы с вами и добрались до последней 30-й статье в нашем марафоне "30 дней = 30 функций". По факту, получилось чуть больше и дней и функций, но это не со зла А замыкающей у нас будет редкая, но весьма полезная функция БДСУММ (DSUM).
Для выборочного подсчета по нескольким условиям в больших таблицах можно использовать несколько способов: фильтры, сводные таблицы, функции СУММЕСЛИ и СУММЕСЛИМН и т.д. Еще одним, относительно экзотическим, но весма мощным инструментом является из категории Работа с базой данных (Database). При внешней простоте, она позволяет гибко фильтровать списки по нескольким сложным и связанным между собой И-ИЛИ условиям и подсчитывает сумму найденных записей по заданному столбцу.
Умение строить прогнозы, предсказывая (хотя бы примерно!) будущее развитие событий - неотъемлемая и очень важная часть любого современного бизнеса. Само-собой, это отдельная весьма сложная наука с кучей методов и подходов, но часто для грубой повседневной оценки ситуации достаточно простых техник. Одна из них - это , которая умеет считать прогноз по линейному тренду.
Это одна из самых лаконичных функций Microsoft Excel, состоящая всего из одной буквы - Ч (N). При этом она весьма полезна, т.к. выполняет простое, но важное действие - превращает свой аргумент в число. На практике такое бывает полезно, например, для добавления текстового комментария прямо в формулу:
В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF), СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)? Помогут новые функции, появившиеся в Excel 2016 - :
Если у вас (или тех, кто будет потом работать с вашим файлом) более старые версии, то придется шаманить другими способами:
писать формулу массива
использовать функцию ДМИН
делать сводную таблицу и вытаскивать данные из нее
Что если при расчете сроков нужная вам дата выпадет на выходные? Как найти ближайший рабочий день к заданной дате, учитывая выходные и праздники (а также отпуска, отгулы, декреты и т.п.)? Поможет функция , которая умеет учитывать нерабочие дни в таких случаях:
На первый взгляд (особенно при чтении справки) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку - в полноценную ссылку.
Однако, первое впечатление обманчиво, поверьте. Эта функция может помочь в огромном количестве ситуаций, например:
для создания несбиваемых ссылок
для сбора данных с нескольких листов или книг
для суммирования по интервалу-окну на листе
для транспонирования формулами
...
Подробный разбор всех нюансов + видео - в по ключевым функциям Microsoft Excel.
Замена одного текста на другой внутри заданной текстовой строки - весьма частая ситуация при работе с данными в Excel. Реализовать подобное можно двумя функциями: и ЗАМЕНИТЬ (REPLACE). Эти функции во многом похожи, но имеют и несколько принципиальных отличий и плюсов-минусов в разных ситуациях. Давайте подробно и на примерах разберем сначала первую из них.
Часто возникает необходимость склеивать текст из нескольких ячеек в одну длинную фразу. Причем иногда нужно дополнительно вставлять между фрагментами еще и заданный символ-разделитель (пробел, запятую и т.д.) Microsoft Excel в подобной ситуации может помочь тремя функциями:
СЦЕП (CONCAT)
СЦЕПИТЬ (CONCATENATE)
ОБЪЕДИНИТЬ (TEXTJOIN)
про все варианты в очередной порции нашего марафона по функциям Excel.
Если сводная таблица для вас еще не финал, и нужно извлечь из нее данные для дальнейших расчетов, анализа или прогнозирования, то пригодится функция с длинным названием(GETPIVOTDATA), которую мало кто (по моему опыту) использует правильно. А она может быть очень и очень полезной:
Весьма часто в нашем распоряжении оказываются данные, которые хоть и содержат полезную информацию, но не пригодны для немедленного анализа. Одной из весьма распространенных проблем является наличие лишних пробелов - в начале, в конце или внутри между словами. Лечится эта проблема очень легко - . Она убирает все лишние пробелы, но оставляет по одному пробелу между словами:
Скорее всего вам известны простые функции МИН (MIN) и МАКС (MAX), позволяющие быстро найти минимальное или максимальное значение в таблице. Но что если нам требуется найти, например, не самое большое, а 2-е или 5-е значение в ТОПе? Здесь помогут функции НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL). С их помощью можно не только найти k-й по счету элемент списка, но и, если нужно:
отсортировать таблицу формулами "на лету"
построить рейтинг или Топ10
вытащить из списка все числовые значения по совпадению (Мульти ВПР)
Потребность знать номер рабочей недели для заданной даты (или группы дат) весьма востребованная вещь в управленческом учете. Понедельные отчеты, еженедельный контроль выполнения плана, недельная сетка вещания в рекламе – все это требует умения определять номер рабочей недели для заданной даты. В Microsoft Excel есть основная функция НОМНЕДЕЛИ (WEEKNUM) и ее аналог для стандарта ISO - функция НОМНЕДЕЛИ.ISO (WEEKNUM.ISO):. Плюс ко всему, можно, конечно же, вычислить номер недели формулой "в лоб".
Продолжаем наш марафон. День 18-й. При анализе данных периодически возникает задача подсчитать количество значений, попадающих в заданные интервалы "от и до" (в статистике их называют карманы). Для решения подобной задачи можно воспользоваться .
Если вы используете фильтрацию или скрываете некоторые строки на листе (вручную или группировкой), то может возникнуть желание подсчитывать итоги только по оставшимся (видимым) строчкам. В такой ситуации может помочь либо классическая функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS), либо ее более совершенный преемник в последних версиях Excel - :
