Ну, вот мы с вами и добрались до последней 30-й статье в нашем марафоне "30 дней = 30 функций". По факту, получилось чуть больше и дней и функций, но это не со зла :) А замыкающей у нас будет редкая, но весьма полезная функция БДСУММ (DSUM). Для выборочного подсчета по нескольким условиям в больших таблицах можно использовать несколько способов: фильтры, сводные таблицы, функции СУММЕСЛИ и СУММЕСЛИМН и т.д. Еще одним, относительно экзотическим, но весма мощным инструментом является функция БДСУММ (DSUM)...

Умение строить прогнозы, предсказывая (хотя бы примерно!) будущее развитие событий - неотъемлемая и очень важная часть любого современного бизнеса. Само-собой, это отдельная весьма сложная наука с кучей методов и подходов, но часто для грубой повседневной оценки ситуации достаточно простых техник. Одна из них - это функция ПРЕДСКАЗ (FORECAST) (http://www.planetaexcel.ru/techniques/25/2823/), которая умеет считать прогноз по линейному тренду. http://www.planetaexcel.ru/upload/medialibrary/f6d/forecast2...

Это одна из самых лаконичных функций Microsoft Excel, состоящая всего из одной буквы - Ч (N). При этом она весьма полезна, т.к. выполняет простое, но важное действие - превращает свой аргумент в число. На практике такое бывает полезно, например, для добавления текстового комментария прямо в формулу: http://www.planetaexcel.ru/upload/medialibrary/85b/n1.png (http://www.planetaexcel.ru/techniques/7/2812/) Простенько, но со вкусом. Пользуйтесь :) Николай Павлов

В Microsoft Excel давно есть в стандартном наборе функции СЧЁТЕСЛИ (COUNTIF), СУММЕСЛИ (SUMIF) и СРЗНАЧЕСЛИ (AVERAGEIF) и их аналоги, позволяющие искать количество, сумму и среднее в таблице по одному или нескольким условиям. Но что если нужно найти не сумму или среднее, а минимум или максимум по условию(ям)? Помогут новые функции, появившиеся в Excel 2016 - МИНЕСЛИ (MINIFS) и МАКСЕСЛИ (MAXIFS) (http://www.planetaexcel.ru/techniques/2/241/): http://www.planetaexcel.ru/upload/medialibrary/1df/min-if10...

Что если при расчете сроков нужная вам дата выпадет на выходные? Как найти ближайший рабочий день к заданной дате, учитывая выходные и праздники (а также отпуска, отгулы, декреты и т.п.)? Поможет функция РАБДЕНЬ (WORKDAY) (http://www.planetaexcel.ru/techniques/6/299/), которая умеет учитывать нерабочие дни в таких случаях: http://www.planetaexcel.ru/upload/medialibrary/d46/round-to-workday4.png (http://www.planetaexcel.ru/techniques/6/299/) Николай Павлов

На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) (http://www.planetaexcel.ru/techniques/25/2783/) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку - в полноценную ссылку.  http://www.planetaexcel.ru/upload/medialibrary/a98/indirect1.png (http://www.planetaexcel.ru/techniques/25/2783/) Однако, первое впечатление обманчиво, поверьте. Эта функция может помочь в огромном количестве ситуаций, например: для создания несбиваемых ссылокдля...

Замена одного текста на другой внутри заданной текстовой строки - весьма частая ситуация при работе с данными в Excel. Реализовать подобное можно двумя функциями: ПОДСТАВИТЬ (SUBSTITUTE) (http://www.planetaexcel.ru/techniques/25/2775/) и ЗАМЕНИТЬ (REPLACE). Эти функции во многом похожи, но имеют и несколько принципиальных отличий и плюсов-минусов в разных ситуациях. Давайте подробно и на примерах разберем сначала первую из них. http://www.planetaexcel.ru/upload/medialibrary/715/substitute1.png (http://www...

Часто возникает необходимость склеивать текст из нескольких ячеек в одну длинную фразу. Причем иногда нужно дополнительно вставлять между фрагментами еще и заданный символ-разделитель (пробел, запятую и т.д.) Microsoft Excel в подобной ситуации может помочь тремя функциями: СЦЕП (CONCAT)СЦЕПИТЬ (CONCATENATE)ОБЪЕДИНИТЬ (TEXTJOIN) Подробная статья (http://www.planetaexcel.ru/techniques/7/89/) про все варианты в очередной порции нашего марафона по функциям Excel. http://www.planetaexcel.ru/upload/medialibrary/e04/concat_text_functions2...

Если сводная таблица для вас еще не финал, и нужно извлечь из нее данные для дальнейших расчетов, анализа или прогнозирования, то пригодится функция с длинным названиемПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (http://www.planetaexcel.ru/techniques/8/229/) (GETPIVOTDATA), которую мало кто (по моему опыту) использует правильно. А она может быть очень и очень полезной: http://www.planetaexcel.ru/upload/medialibrary/1da/getpivotdata6.png (http://www.planetaexcel.ru/techniques/8/229/) Николай Павлов

Весьма часто в нашем распоряжении оказываются данные, которые хоть и содержат полезную информацию, но не пригодны для немедленного анализа. Одной из весьма распространенных проблем является наличие лишних пробелов - в начале, в конце или внутри между словами. Лечится эта проблема очень легко - специальной функцией СЖПРОБЕЛЫ (TRIM) (http://www.planetaexcel.ru/techniques/25/2734/). Она убирает все лишние пробелы, но оставляет по одному пробелу между словами: http://www.planetaexcel.ru/upload/medialibrary/303/clean-text2...

Скорее всего вам известны простые функции МИН (MIN) и МАКС (MAX), позволяющие быстро найти минимальное или максимальное значение в таблице. Но что если нам требуется найти, например, не самое большое, а 2-е или 5-е значение в ТОПе? Здесь помогут функции НАИБОЛЬШИЙ (LARGE) и НАИМЕНЬШИЙ (SMALL).  С их помощью можно не только найти k-й по счету элемент списка, но и, если нужно: отсортировать таблицу формулами "на лету"построить рейтинг или Топ10вытащить из списка все числовые значения по совпадению...

Потребность знать номер рабочей недели для заданной даты (или группы дат) весьма востребованная вещь в управленческом учете. Понедельные отчеты, еженедельный контроль выполнения плана, недельная сетка вещания в рекламе – все это требует умения определять номер рабочей недели для заданной даты. В Microsoft Excel есть основная функция НОМНЕДЕЛИ (WEEKNUM) и ее аналог для стандарта ISO - функция НОМНЕДЕЛИ.ISO (WEEKNUM.ISO):. Плюс ко всему, можно, конечно же, вычислить номер недели формулой "в лоб"...

Продолжаем наш марафон. День 18-й.  При анализе данных периодически возникает задача подсчитать количество значений, попадающих в заданные интервалы "от и до" (в статистике их называют карманы). Для решения подобной задачи можно воспользоваться функцией ЧАСТОТА (FREQUENCY) (http://www.planetaexcel.ru/techniques/25/2701/). http://www.planetaexcel.ru/upload/medialibrary/e3f/frequency1.png (http://www.planetaexcel.ru/techniques/25/2701/) Николай Павлов

Если вы используете фильтрацию или скрываете некоторые строки на листе (вручную или группировкой), то может возникнуть желание подсчитывать итоги только по оставшимся (видимым) строчкам. В такой ситуации может помочь либо классическая функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS), либо ее более совершенный преемник в последних версиях Excel - функция АГРЕГАТ (AGGREGATE) (http://www.planetaexcel.ru/techniques/2/222/): http://www.planetaexcel.ru/upload/medialibrary/1ea/sum-visible-only2.png Николай Павлов

Разбор применения редкой функции ПРОСМОТР (LOOKUP) (http://www.planetaexcel.ru/techniques/2/664/) для решения задачи поиска последней занятой ячейки в строке или столбце (по тексту и числам): http://www.planetaexcel.ru/upload/medialibrary/2c2/last-in-row-column2.png Николай Павлов

При внешней простоте, функция ГИПЕРССЫЛКА (HYPERLINK) (http://www.planetaexcel.ru/techniques/25/2681/) на практике оказывается весьма полезной и позволяет создавать ссылки на всевозможные объекты: ячейки и диапазоны внутри текущей книгивнешние файлы, расположенные на жестком диске или сервереименованные диапазоныфрагменты умных таблицвеб-страницызаполненные бланки электронной почты Все варианты применения - в очередной статье (http://www.planetaexcel.ru/techniques/25/2681/) нашего марафона - просвещайтесь...

Это одна из тех функций, про которые, при первом знакомстве, обычно говорят "и что?". На самом деле функция ВЫБОР (CHOOSE) (http://www.planetaexcel.ru/techniques/25/2639/) умеет не только выбирать заданный элемент из набора по номеру, а гораздо больше: выдавать ссылку на диапазон из наборасклеивать диапазоны прямо в формуле в любом порядкеработать с вложенными функциями http://www.planetaexcel.ru/upload/medialibrary/819/choose5.png (http://www.planetaexcel.ru/techniques/25/2639/) Николай...

Если нужно конвертировать вертикальный диапазон в горизонтальный или наоборот, то есть много способов, кроме очевидной специальной вставки. Один из них - использовать функцию ТРАНСП (TRANSPOSE) (http://www.planetaexcel.ru/techniques/2/87/), введя ее как формулу массива. http://www.planetaexcel.ru/upload/medialibrary/032/03296a17062de86ddbf20df58430acf9.gif Николай Павлов

Большинство функций Excel не различают строчные и прописные буквы. Если же нужно искать точно (с учетом регистра), то пригодится функция СОВПАД (EXACT) (http://www.planetaexcel.ru/techniques/25/2602/), умеющая сравнивать две текстовых строки с абсолютной точностью: http://www.planetaexcel.ru/upload/medialibrary/391/exact1.png Николай Павлов

Обычно, большинство пользователей, прочитав справку по функции СУММПРОИЗВ (http://www.planetaexcel.ru/techniques/25/2609/), бывают слегка, скажем так, разочарованы. На самом деле, использование этой функции по прямому назначению - для вычисления суммы произведений в нескольких диапазонах - самое скучный вариант ее применения. Она умеет намного больше! работать с массивамипроверять одно или несколько условий со связками И и ИЛИсуммировать по условию данные из закрытых(!) книг https://www.youtube.com/watch?v=VCVtXdDLfc0...