Марафон "30 дней = 30 функций"



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

Для реализации 95% повседневных задач у 95% пользователей Excel нужно знать далеко не все, а от силы 20-30 основных функций и уметь их комбинировать.

Можно провести аналогию с правилами дорожного движения. Если вы сдавали на права, то учили 800 вопросов в билетах, три сотни дорожных знаков и жесты регулировщика. Помните как это было? А теперь прикиньте, сколько из этих знаний вы реально используете, доезжая на машине до работы-магазина?

Так и в Excel. Да, вам может встретиться задача с вычислением параметров распределения Стьюдента, или дохода по ценным бумагам с нерегулярным первым периодом купона, или необходимо будет перевести градусы в радианы, но это - скажем откровенно - экзотика. А для подавляющего большинства типовых ежедневных задач хватит и 10% от всего набора функций Excel "за глаза" плюс умение их комбинировать.

Вот эти 30 ключевых функций и отделяют новичка от уверенного пользователя Excel, и того, кто мучается с файлами Excel допоздна от того, кто уходит домой вовремя. Не надо учить все 472, освойте и внедрите в свои книги эти 30 - и получите мгновенный и заметный результат. А остальные 442 можно потом не спеша расковыривать в свободное время "для души".

Ну, ладно, хватит лирики. Надеюсь, я уже донес до вас свою мысль :)  К чему я веду?

Начинаю сегодня ликбез-марафон "30 дней = 30 функций" по ключевым функциям Excel. На каждую будет отдельная статья с подробным разбором синтаксиса и примерами использования на практике в разных (в том числе и нестандартных) вариантах. Публиковать ссылки на материалы буду здесь, в этом посте и в специально созданной категории ФУНКЦИИ в ПРИЕМАХ и в моем Twitter с хештегом #30ExcelFunctions.

  1. Поиск позиции элемента в списке функцией ПОИСКПОЗ (MATCH)
  2. Как суммировать из динамического диапазона-окна с заранее неизвестными размерами и положением на листе функцией СМЕЩ (OFFSET)
  3. Как вычислить разницу между двумя датами (стаж, возраст) в днях, месяцах или годах скрытой функцией РАЗНДАТ (DATEDIF)
  4. Превращение кривой текстовой даты в полноценную функцией ДАТАЗНАЧ (DATEVALUE)
  5. Перехват ошибок в формулах функцией ЕСЛИОШИБКА (IFERROR)
  6. Поиск и подстановка данных из одной таблицы в другую функцией ВПР (VLOOKUP)
  7. Визуализация значками с функциейСИМВОЛ (CHAR)
  8. Извлечение информации о ячейке функцией ЯЧЕЙКА (CELL)
  9. Поиск и извлечение данных из одно- и двумерных диапазонов функцией ИНДЕКС (INDEX)
  10. Конвертирование систем мер функцией ПРЕОБР (CONVERT)
  11. Как не забивать гвозди микроскопом с функцией СУММПРОИЗВ (SUMPRODUCT)
  12. Поиск точных совпадений функцией СОВПАД (EXACT)
  13. Превращение строк в столбцы и обратно функцией ТРАНСП (TRANSPOSE)
  14. Как получить элемент из набора функцией ВЫБОР (CHOOSE)
  15. Создание внешних и внутренних ссылок, а также писем функцией ГИПЕРССЫЛКА (HYPERLINK)
  16. Поиск последней занятой ячейки в строке-столбце функцией ПРОСМОТР (LOOKUP)
  17. Вычисления только по видимым ячейкам функцией АГРЕГАТ (AGGREGATE)
  18. Частотный анализ попадания в интервалы функцией ЧАСТОТА (FREQUENCY)
  19. Как определить номер недели функцией НОМНЕДЕЛИ (WEEKNUM)
  20. Выявление топовых значений функциями НАИБОЛЬШИЙ и НАИМЕНЬШИЙ (LARGE и SMALL)
  21. Удаление лишних пробелов функцией СЖПРОБЕЛЫ (TRIM)
  22. Правильные ссылки на сводную таблицу функцией ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
  23. Склейка текста функциями СЦЕПИТЬ, СЦЕП и ОБЪЕДИНИТЬ
  24. Замена текста функцией ПОДСТАВИТЬ (SUBSTITUTE)
  25. Разбор функции ДВССЫЛ (INDIRECT) на примерах + видео
  26. Поиск ближайшего рабочего дня функцией РАБДЕНЬ (WORKDAY)
  27. Поиск минимального и максимального по условию функциями МИНЕСЛИ (MINIFS) и МАКСЕСЛИ (MAXIFS)
  28. Скрытый комментарий к формуле функцией Ч (N)
  29. Быстрый прогноз по линейному тренду функцией ПРЕДСКАЗ (FORECAST)
  30. Выборочный подсчет по нескольким сложным условиям функцией БДСУММ (DSUM)
16 Марта 2017 9:37
Интересная тема!

Я как-то тоже считал, какие функции/инструменты ExcelЯ приходится чаще всего использовать в работе, получилось как-то так:

• ВПР
• СУММ
• СРЗНАЧ
• СЧЁТ
• СУММЕСЛИ
• СЧЁТЕСЛИ
• ФИЛЬТР
• ПРОМЕЖУТОЧНЫЕ.ИТОГИ
• СЦЕПИТЬ
• ЕСЛИ
• СУММПРОИЗВ
• СОРТИРОВКА
• НАЙТИ/ЗАМЕНИТЬ
• ЗАКРЕПИТЬ ОБЛАСТИ

Николай, а можно Ваш список сразу огласить? :)

Все что не попадёт в ваш список буду рад показать в своем блоге: http://biweb.ru/blog
16 Марта 2017 11:34
Сергей, я имел ввиду функции рабочего листа, а не все инструменты Excel в целом. Т.е. фильтр, закрепление областей, сортировка и т.п. - мимо.
А список оглашать, с вашего позволения, не буду. Во-первых, может захочется еще внести правки по ходу. А во-вторых, пусть будет интрига :)
25 Апреля 2017 16:27
Николай,

Большое спасибо за Марафон "30 дней = 30 функций"

Это было круто со всех точек зрения!!!

На базе статей этого марафона можно оформить «Практический справочник по функциям Excel»:
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. БДСУММ

С вашего разрешения хочу добавить в этот список на своем блоге (http://biweb.ru/blog), описание еще нескольких функций (часть из которых уже описана на вашем сайте):

СЕГОДНЯ/ТДАТА
ОКРУГЛ/ОКРУГЛТ/+
СЛЧИС/СЛУЧМЕЖДУ/РАНГ
СУММЕСЛИМН
СРЗНАЧЕСЛИМН
СЧЁТЕСЛИМН
СТРОКА/СТОЛБЕЦ
ЧИСЛСТОЛБ/ЧСТРОК
ДЛСТР
ЛЕВСИМВ/ПРАВСИМВ
ПСТР
НАЙТИ/ПОИСК
ПЕЧСИМВ
СТРОЧН/ПРОПИСН/ПРОПНАЧ
ЕСЛИ
И/ИЛИ/НЕ
ТИП.ОШИБКИ
16 Марта 2017 16:28
Добрый день, Николай!

А может провести что-то вроде экспресс-опроса, типа "какие функции вы чаще всего используете ? " или "каким самые полезные" и т.д. Что-то подобное я уже встречал на англоязычных блогах.

С уважением,
Ильгар
16 Марта 2017 17:11
Моя цель, в данном случае, как раз расширить кругозор, а не собрать статистику. Статистику я и так знаю примерно - 10 лет тренингов за спиной :)
18 Марта 2017 15:17
Спасибо Вам! Внимательно слежу за марафоном. 8)
28 Марта 2017 16:47
Здравствуйте!
Николай, спасибо Вам за интересный и познавательный цикл статей, которые Вы для нас приготовили!
Скажите пожалуйста, используют  ли организации в которых Вы проводите тренинги альтернативные офисы, LibreOffice в частности, применимы ли Ваши уроки к ним, хотя бы частично?
28 Марта 2017 19:33
За последние 10 лет тренингов один раз видел OpenOffice и пару раз Google-таблицы.
Все остальное - Excel :)
Хотя, возможно, в такие организации меня просто не приглашают, поэтому моя выборка не репрезентативная ("синдром выжившего" так сказать).