​​Приемы работы с умными таблицами (часть 2)

Строка итогов

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

Аналогичного эффекта можно добиться при помощи сочетания клавиш ALT + =.

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

​​Приемы работы с умными таблицами (часть 3)

Вставка формул

Использование умных таблиц позволяет упростить рутинные операции. Например, при вставке формулы в одну из ячеек мы автоматически получим аналогичную формулу во всем соответствующем столбце.

Но это удобство может создавать проблемы, если в других ячейках столбца формулы должны отличаться.
Мы можем управлять автозаполнением формул с помощью параметров автозамены — для этого имеется специальный значок, который появляется в правом нижнем углу ячейки.

При помощи команды Отменить вычисляемый столбец можно запретить вычисление в других ячейках этого столбца и ввести в них другие формулы.
А с помощью Не создавать вычисляемые столбцы автоматически можно отключить автозаполнение формулами сразу для всех столбцов таблицы.

​​​​Как создать раскрывающийся список?

— Шаг 1
Введите список элементов в диапазон. Затем выделите ячейку, которая будет содержать раскрывающийся список.

— Шаг 2
Выберите Данные → Работа с данными → Проверка данных.
В диалоговом окне Проверка вводимых значений откройте вкладку Параметры.

— Шаг 3
В списке Тип данных выберите пункт Список.
Затем в поле Источник укажите диапазон, который содержит элементы.
Нажмите кнопку ОК, готово!

Новая функция IMAGE для вставки изображений в ячейки

В этом видеоуроке автор подробно и наглядно разбирает варианты использования новой функции ИЗОБРАЖЕНИЕ (IMAGE) для вставки картинок по ссылкам на лист Excel, а также рассматривает собственный аналог на VBA для вставки изображений из локальных файлов.

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

Логические операторы И (*) и ИЛИ (+) в формулах Excel

Во многих функциях Excel используются логические выражения ИСТИНА и ЛОЖЬ, при этом есть нюансы, которые стоит знать, чтобы максимально эффективно использовать условия при создании формул.

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

​​Вставка изображения под текст

Есть пара способов добавления картинки под ваш текст. Давайте разбираться:

1. Колонтитул. Произведите типичную комбинацию для добавления колонтитула на страницу: «Вставка» → «Текст» → «Колонтитул». В шапке программы клинике по кнопке «Вставка изображений». Останется только добавить изображение из проводника, интернета или другого источника.

2. Подложка. Это своеобразный фон страницы, которая отлично подойдет для того, чтобы вставить туда изображение без дополнительных настроек размера. Откройте вкладку «Разметка страницы» и кликните по мелкой кнопке «Подложка». Добавьте изображение с вашего ПК и ликуйте!

Функция ФИЛЬТР в Excel

В этом видеоуроке автор на примерах разберет функцию динамических массивов ФИЛЬТР.

00:00 Вступление
01:25 Синтаксис функции ФИЛЬТР
02:05 ФИЛЬТР вместо ВПР
03:40 Ошибка ВЫЧИСЛ
04:50 Поиск всех совпадений
07:22 Кастомизация отчета с помощью функции ВЫБОР
09:08 Несколько условий
11:00 Зависимые списки
12:42 Ограничения функции ФИЛЬТР

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

Новые функции 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