Новые функции Excel 2021

В этом видео автор подробно разбирает новые функции в Microsoft Excel 2021, которые помогут упростить вашу работу:

00:00 Вступление
00:35 Динамические массивы
03:03 Функция ПРОСМОТРХ и сортировки
07:28 Фильтрация с помощью формулы
09:55 Функция УНИК
11:45 Функция ПОСЛЕД
13:58 Заключение

Смотреть это видео на youtube: youtu.be/Tg3MlNmPDiU

​​Как подсчитать количество рабочих дней между двумя датами

Excel обладает несколькими инструментами по подсчëту количества дней между датами. Минимально доступная дата — 01.01.1900, а дробная часть — это доля суток, если мы говорим о часах, минутах и секундах.

Воспользуйтесь функцией «ЧИСТРАБДНИ», чтобы рассчитать количество рабочих дней. Первый аргумент — это начальная дата, а второй — конечная.

​​Функция СМЕЩ

СМЕЩ возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.

СМЕЩ(Ссылка, Смещ_по_строкам, Смещ_по_столбцам, [Высота], [Ширина])

• Ссылка (обязательный аргумент) — ссылка на ячейку или на диапазон смежных ячеек, от которых вычисляется смещение;
• Смещ по строкам (обязательный аргумент) — количество строк, которое требуется отсчитать вверх или вниз, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку;
• Смещ по столбцам (обязательный аргумент) — количество столбцов, которое требуется отсчитать влево или вправо, чтобы левая верхняя ячейка результата ссылалась на нужную ячейку;
• Высота (необязательный аргумент) — число строк возвращаемой ссылки;
• Ширина (необязательный аргумент) — число столбцов возвращаемой ссылки.

Пример работы формулы приведен на картинке ниже.

Многоуровневый список по столбцам в Excel

В этом видеоуроке автор подробно и наглядно разберет три способа (формулы, Power Query и макрос), чтобы разложить многоуровневый список с вложенной нумерацией по отдельным столбцам.

Смотреть это видео на youtube: youtu.be/Zx5xw2dUiWs

​​Выборочное форматирование ячеек

Предположим, вам понадобилось выделить жирным текстом только определённые значения в таблице, сохраняя при этом форматирование других ячеек.

Для этого можно нажать сочетание клавиш CTRL + H и ввести форматируемое значение в поле Найти. В поле Заменить на введите то же значение, после чего нажмите на Параметры → Формат.

В появившемся окне на вкладке Шрифт остаётся лишь выбрать начертание (в нашем случае полужирное) и нажать ОК, готово.

Анализ данных и поиск решений

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

00:00 Пакет "Анализ данных"
09:17 Пакет "Поиск решения"
16:26 Дополнительные надстройки

Смотреть это видео на youtube: youtu.be/E6_fc5FNpIo

Построение радиальной гистограммы

В этом видеоуроке автор подробно разбирает процесс построения радиальной гистограммы. Это гистограмма, построенная в радиальной системе координат. Её отличительной особенностью и одновременно недостатком является то, что иногда такую гистограмму некорректно интерпретируют, поскольку нашему глазу более привычен классический вид диаграммы с прямыми столбцами / строками.

00:28 Особенности радиальной гистограммы
00:44 Недостатки
01:12 Процесс построения
03:31 Особенности настройки параметров
07:36 Примеры радиальных гистограмм

Смотреть это видео на youtube: youtu.be/WTWzDwwB5VY

​​Быстрый способ добавления функции

Переходить по кнопке «Fx» в шапке программы и искать популярную (или даже никому не известную) функцию — бессмысленно.

Сэкономьте время: перейдите во вкладку «Формулы». Там откроется полное меню для добавления и редактирования имеющихся формул. По одному клику можно создать известную формулу, там же еë настроить и произвести вычисления.

Поиск ошибок в Excel: подсчет и обработка

В этом ролике автор подробно рассматривает ошибки в расчетах и способы работы с ними, а также разбирает процесс подсчета значений с ошибками. Вы изучите функции:

• ЕНД (ISNA)
• ЕЧИСЛО (ISNUMBER)
• ЕСЛИОШИБКА (IFERROR)
• СЧЕТЕСЛИ (COUNTIF)
• АГРЕГАТ (AGGREGATE)
• работу функции массива

Смотреть это видео на youtube: youtu.be/wMGewRJcpEU

Как построить графики синуса и косинуса

В этом ролике автор подробно разбирает создание графиков синуса и косинуса. Стандартные функции Sin(X) и Cos(X) используют в качестве входного параметра не градусы, а радианы. И это нужно учитывать при построении графиков.

Смотреть это видео на youtube: youtu.be/1XZfALFezgE

​​Примечания ячейки

Крайне удобный и необходимый при передаче рабочего файла инструмент — примечания.

Выглядит как «Заметки» в PowerPoint, только привязан не ко всему слайду, а к конкретной ячейке/таблице/графику.

Для этого кликните по необходимой ячейке, перейдите во вкладку «Рецензирование» и найдите функцию «Создать примечание».

​​Разность между датами

Полезная функция — РАЗНДАТ. Введите пару любых дат и программа посчитает количество дней, месяцев или лет между календарными датами. Формула выглядит так:

=РАЗНДАТ(нач_дата;кон_дата;ед)

В первой и центральной ячейке введите дату или кликните по нужному значению. Последняя ячейка довольно разнообразна, внутри может быть разное:

«d» — результат отображается в днях;
«m» — в полных месяцах;
«y» — в полных годах;
«YD» — разность в днях (без учета годов);
«MD» — разность в днях (без учета месяцев и годов);
«YM» — разница в месяцах.

Инструмент Подбор параметра в Excel

"Подбор параметров" позволяет подобрать такое значение ячейки, при котором формула, ссылающаяся на эту ячейку, будет принимать нужное значение.

0:00 Подбор параметров в Excel
0:12 Вкладка данные, «Анализ что-если»
0:25 Пример простой финмодели
0:58 Практический пример по шагам
3:55 Когда полезно использовать подбор параметра
4:22 Когда подбор особенно актуален
4:52 Минус этого метода

Смотреть это видео на youtube: youtu.be/XouUziJa0fA

​​Копирование без нарушения форматов

Если потянуть за маркер при автозаполнении, содержимое ячейки или формулы скопируется сразу на несколько ячеек. Но проблема в том, что копируется не только сама формула, но и формат ячейки.

Чтобы этого избежать, сразу после протягивания нажимаем специальный значок, появляющийся в правом нижнем углу скопированной области, и выбираем опцию Копировать только значения.

Теперь Excel скопирует вашу формулу без формата и не будет портить оформление.

Как построить диаграмму Парето (80 на 20) в Excel

В этом видеоуроке автор разберет 5 способов выполнения частотного анализа и построения диаграммы Парето (20/80): формулами, на сводных таблицах с группировкой, надстройкой Пакет Анализа и т.д.

Смотреть это видео на youtube: youtu.be/A8yGyUuxCd4

​​Как привести телефонные номера к единому формату

Для того, чтобы не страдать с программными форматами, создайте свой. Тем более в Excel это решается проще простого.

Для этого правой кнопкой мыши щëлкните по свободной ячейке. В контекстном меню выберите функцию «Формат ячеек».

В открывшемся окне во вкладке «Число» выберите пункт «Все форматы». В появившееся справа поле «Тип» введите маску необходимого формата. В нашем случае: +7" "(#)" "000-00-00.

Особое внимание обратите на раздел «Образец», где данные должны были поменять свой вид. Если всë совпало — подтверждайте «Ок».

Расчет кредита в Excel

В этом видеоуроке автор разберет, как посчитать банковский кредит в Excel.

0:08 Как моделировать классический банковский кредит
0:25 Как посчитать кредитные платежи
1:00 Почему аннуитет выгоден для банка
1:25 Калькулятор с функциями ПРПЛТ() и ОСПЛТ()
2:30 Заполняем данные по кредиту
4:30 Оформляем таблицу
6:40 Калькулятор с плавающей датой старта
7:25 Итоги

Смотреть это видео на youtube: youtu.be/U-pcknLN--I

Как построить диаграмму в Excel

В этом видеоуроке автор подробно и наглядно разбирает основные типы диаграмм в Microsoft Excel и на практике показывает процесс создания и редактирования диаграмм.

0:17 Вступление
0:27 Основные типы диаграмм
0:38 График с маркерами
3:37 Линейчатая диаграмма
6:20 Круговая диаграмма
8:34 Заключение

Смотреть это видео на youtube: youtu.be/MM330UIPpbQ

​​Как быстро узнать о существовании скрытых листов?

Чтобы сделать тот или иной лист в документе скрытым, нужно заходить в меню VBA (сочетанием клавиш ALT + F11), что не всегда удобно.

Если вы хотите узнать о существовании скрытых листов одним кликом, то:

— Шаг 1
Кликните правой кнопкой мыши на панели состояния (снизу возле надписи Готово).

— Шаг 2
Из выпадающего списка выберите пункт Статистика книг.

— Шаг 3
При следующем нажатии на Статистика книг будет отображено общее количество листов. В нашем случае листов 4, а видимых — всего 3.

Как закрепить шапку таблицы

В этом видеоуроке автор рассматривает как закрепить однострочную шапку и многострочную, если таблица сложная.

0:00 Вступление
0:11 Что будем делать
0:18 Закрепление однострочной шапки
1:03 Закрепление многострочной шапки
2:01 Итоги

Смотреть это видео на youtube: youtu.be/LP0lLwixb-U