• Архив

    «   Апрель 2017   »
    Пн Вт Ср Чт Пт Сб Вс
              1 2
    3 4 5 6 7 8 9
    10 11 12 13 14 15 16
    17 18 19 20 21 22 23
    24 25 26 27 28 29 30
                 

Марафон "30 дней = 30 функций" #30: Выборочное суммирование по нескольким условиям с БДСУММ (DSUM)

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

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

Марафон "30 дней = 30 функций" #29: Быстрый прогноз функцией ПРЕДСКАЗ (FORECAST)

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

Марафон "30 дней = 30 функций" #28: Скрытый комментарий к формуле функцией Ч(N)

Это одна из самых лаконичных функций Microsoft Excel, состоящая всего из одной буквы - Ч (N). При этом она весьма полезна, т.к. выполняет простое, но важное действие - превращает свой аргумент в число. На практике такое бывает полезно, например, для добавления текстового комментария прямо в формулу:



Простенько, но со вкусом. Пользуйтесь :)

Марафон "30 дней = 30 функций" #27: Поиск минимального и максимального по условию с МИНЕСЛИ и МАКСЕСЛИ

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



Если у вас (или тех, кто будет потом работать с вашим файлом) более старые версии, то придется шаманить другими способами:
  • писать формулу массива
  • использовать функцию ДМИН
  • делать сводную таблицу и вытаскивать данные из нее
Все способы - в очередной статье нашего марафона-ликбеза.

Марафон "30 дней = 30 функций" #26: Поиск ближайшего рабочего дня функцией РАБДЕНЬ (WORKDAY)

Что если при расчете сроков нужная вам дата выпадет на выходные? Как найти ближайший рабочий день к заданной дате, учитывая выходные и праздники (а также отпуска, отгулы, декреты и т.п.)? Поможет функция РАБДЕНЬ (WORKDAY), которая умеет учитывать нерабочие дни в таких случаях:

Марафон "30 дней = 30 функций" #25: Подробный разбор на примерах функции ДВССЫЛ (INDIRECT)

На первый взгляд (особенно при чтении справки) функция ДВССЫЛ (INDIRECT) выглядит простой и даже ненужной. Ее суть в том, чтобы превращать текст похожий на ссылку - в полноценную ссылку.



Однако, первое впечатление обманчиво, поверьте. Эта функция может помочь в огромном количестве ситуаций, например:
  • для создания несбиваемых ссылок
  • для сбора данных с нескольких листов или книг
  • для суммирования по интервалу-окну на листе
  • для транспонирования формулами
  • ...
Подробный разбор всех нюансов + видео - в очередной статье нашего марафона-ликбеза по ключевым функциям Microsoft Excel.

Марафон "30 дней = 30 функций" #24: Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)

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

Марафон "30 дней = 30 функций" #23: Как склеивать текст функциями СЦЕП, СЦЕПИТЬ и ОБЪЕДИНИТЬ

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

Марафон "30 дней = 30 функций" #22: Извлечение данных из сводной таблицы с ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

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

Марафон "30 дней = 30 функций" #21: Удаление лишних пробелов из текста функцией СЖПРОБЕЛЫ (TRIM)

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

Марафон "30 дней = 30 функций" #20: Анализ предельных значений функциями НАИБОЛЬШИЙ и НАИМЕНЬШИЙ

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


Подробнее - в очередной статье нашего марафона по самым нужным функциям Excel.

Марафон "30 дней = 30 функций" #19: Номер недели по дате функцией НОМНЕДЕЛИ (WEEKNUM)

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

Все варианты в очередной статье марафона "30 дней = 30 функций":

Марафон "30 дней = 30 функций" #18: Частотный анализ функцией ЧАСТОТА (FREQUENCY)

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

Марафон "30 дней = 30 функций" #17: Подсчет только по видимым ячейкам функцией АГРЕГАТ (AGGREGATE)

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

Марафон "30 дней = 30 функций" #16: Поиск последнего значения в строке-столбце функцией ПРОСМОТР (LOOKUP)

Разбор применения редкой функции ПРОСМОТР (LOOKUP) для решения задачи поиска последней занятой ячейки в строке или столбце (по тексту и числам):

Марафон "30 дней = 30 функций" #15: Создание внешних и внутренних ссылок функцией ГИПЕРССЫЛКА (HYPERLINK)

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

Марафон "30 дней = 30 функций" #14: Зачем нужна функция ВЫБОР (CHOOSE)

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

Марафон "30 дней = 30 функций" #13: Превращение строк в столбцы и обратно функцией ТРАНСП (TRANSPOSE)

Если нужно конвертировать вертикальный диапазон в горизонтальный или наоборот, то есть много способов, кроме очевидной специальной вставки. Один из них - использовать функцию ТРАНСП (TRANSPOSE), введя ее как формулу массива.

Страницы: 1 | 2 | 3 | 4 | 5 | ... | 21 | След.