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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Семь футов под килем - это сколько в метрах? Можно, конечно, искать ответ в Яндексе и Гугле, но иногда проще и быстрее спросить Excel - воспользоваться специальной функцией ПРЕОБР (CONVERT), умеющей конвертировать результаты из одной системы мер в другую: http://www.planetaexcel.ru/upload/medialibrary/f50/f50119e8910b4f9a29c032aa52f1e2e8.gif

Подробная статья с видео о том, как правильно использовать одну из самых нужных и многогранных функций в Excel - функцию ИНДЕКС (INDEX) для: извлечения данных из одномерных и двумерных диапазоновизвлечения данных из нескольких таблицсоздания динамических ссылок на целые столбцы или строкисоздания автоподстраивающихся диапазонов http://www.planetaexcel.ru/upload/medialibrary/1e0/index1.png

Иногда бывает необходимо получить подробную информацию о какой-либо ячейке: тип введенных в нее данных, числовой формат, статус защиты и т.д. Для этого может пригодиться функция ЯЧЕЙКА (CELL). Подробный разбор ее параметров и практические приемы применения в очередной статье нашего марафона "30 дней = 30 функций": http://www.planetaexcel.ru/upload/medialibrary/322/cell1.png

Продолжаем наш ликбез по функциям. Сегодня простая, но нужная функция СИМВОЛ (CHAR), умеющая выводить нестандартные символы по их коду. С ее помощью можно делать простые, но наглядные визуализации роста или падения каких-либо параметров, количества проданных товаров и т.д. http://www.planetaexcel.ru/upload/medialibrary/5d2/char3.png

Одна из самых известных функций Excel - ВПР (VLOOKUP). Как ее использовать для поиска и подстановки значений из одной таблицы в другую: http://www.planetaexcel.ru/upload/medialibrary/b13/b1366e5562bfcec6a1a7d07f8b74d1b9.gif

Как перехватывать ошибки в формулах и заменять их на что-то более полезное с помощью функции ЕСЛИОШИБКА (IFERROR): http://www.planetaexcel.ru/upload/medialibrary/793/iferror2.png

Как исправить кривую текстовую дату в разных форматах и привести ее в нормальный вид с помощью функции ДАТАЗНАЧ (DATEVALUE): http://www.planetaexcel.ru/upload/medialibrary/7e9/datevalue1.png

Как вычислить разницу между двумя датами (стаж, возраст) в годах, месяцах или днях с помощью скрытой функции Excel РАЗНДАТ (DATEDIF): http://www.planetaexcel.ru/upload/medialibrary/c35/datediff1.png

Бывают ситуации, когда заранее не известно какие именно ячейки на листе нужно подсчитывать и нужно ссылаться на динамическое "окно" на листе заданных размеров. В этом случае поможет функция СМЕЩ (OFFSET): http://www.planetaexcel.ru/upload/medialibrary/fe2/offset4.png

Как использовать функцию ПОИСКПОЗ (MATCH) для поиска позиции элемента в списке. Как использовать приблизительный поиск (в большую или меньшую сторону), поиск ближайших дат, поиск первой или последней текстовой ячейки в строке-столбце, связку функций ИНДЕКС и ПОИСКПОЗ и т.д. http://www.planetaexcel.ru/upload/medialibrary/c68/match1.png

... в моем Twitter с хештегом #30ExcelFunctions. Поиск позиции элемента в списке ...