Ну, вот мы с вами и добрались до последней 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...
Отсортировано по релевантности | Сортировать по дате