Сводные таблицы от 0 до PRO
● Логика построения и изменения отчёта сводной таблицы.
● Корректировка названий столбцов, строк, итогов.
● Настройки внешнего вида: формат ячеек, центрирование заголовков, фиксированная ширина столбцов, прочерки для пустот, убираем ошибки и пр.
● Многоуровневая аналитика записей.
● Перестройка макета.
● Особенности фильтрации и сортировки записей.
● Добавление срезов для наглядности фильтрации. Настройка срезов.
● Страницы отчёта. Вывод страниц на разные листы.
● Группировка по датам и объединение текстовых записей в произвольные группы.
● Изменение формулы для вывода результата.
● Дополнительные вычисления.
● Добавление собственных вычисляемых полей и объектов.
● ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
● Обновление отчёта. Корректировка источника.
● Переделка источника, если в нём объединённые ячейки.
● Создание независимых отчётов сводной таблицы по одному источнику (разный кэш).
Удаление дубликатов из выгрузок и для создания уникальных списков.
● Составляем список уникальных значений.
● Удаляем задвоенные записи из списков.
● Выделяем повторы цветом.
*** Разные повторы разным цветом (готовый макрос для использования).
Текст по столбцам. Разносим данные разного типа по своим колонкам.
● Делим текст по количеству знаков.
● Делим текст по разделителю.
● Настройки диалогового окна разбора текста (сохранение оригинала, формата, ограничитель строк и т.п.).
Умные таблицы. Преимущества оформления списка таблицей.
● Создаём умную таблицу.
● Формулы внутри умной таблицы и на её ячейки извне. Имя таблицы.
● Фильтр и заголовки умной таблицы.
● Итоговая строка умной таблицы.
● Добавление срезов для наглядности фильтрации. Настройка срезов.
● Корректируем и убираем дизайн.
Выпадающие списки и другие ограничения на ввод данных.
● Создание выпадающего списка по справочнику и без него.
● Подсказки для заполняющих таблицу.
● Жёсткое и мягкое ограничение.
● Как переделать существующие таблицы, чтобы не заполнять их заново.
● Другие ограничения на ввод данных: например, на кол-во символов в ячейке, ввод даты.
Защита ячеек, листов, файла. Многопользовательский доступ.
● Защита всех или отдельных ячеек (например, с формулами).
● Защита ячеек от копирования.
● Блокировка доступа к листам (в т.ч. скрытым).
● Пароль на изменение диапазонов.
● Пароль на доступ к файлу.
● Доступ к файлу только для чтения.
● Многопользовательский режим работы в файле.
● Что делать, если забыли пароль.
Фильтр расширенный для чтения условий фильтрации с листа. Транспонирование.
● Мгновенная фильтрация по большому набору элементов.
● Фильтр по нескольким взаимоисключающим условиям.
● Обновление и сброс фильтра.
Промежуточные итоги для удобства работы со списками.
● Вставка итогов по столбцу с повторяющимся текстом.
● Вставка многоуровневых итогов.
● Оформление и копирование итоговых строк. Выбираем только видимые ячейки, пропуская скрытые.
● Удаление итогов.
● Создание итоговой строки с формулами в выгрузке из учётной системы.
Консолидация. Групповой режим обработки листов для их одновременной правки.
● Составляем один отчёт по нескольким разношёрстным исходным таблицам.
● Обновляем консолидацию со связями и без.
● Компактная формула для расчёта итога по стандартизированным таблицам.
● Избавляемся от рутины. Групповая обработка нескольких листов.
Условное форматирование по 6-му типу (по формуле)
● Разбираем логику создания условных функций.
● Учимся выделять целые строки и столбцы таблицы в зависимости от значения конкретной ячейки.
● Создаём несколько правил для одного диапазона ячеек на примере анализа цен.
● Создаём подобие диаграммы Ганта для графика отпусков (автоматически закрашенные ячейки, соответствующие датам начали и окончания отпуска).
● Управляем приоритетом выполнения правил, настраиваем ручную и автоматическую корректировку диапазона.
● Мгновенного оформляем текущие и будущие итоговые строки и столбцы таблицы.
● Используем условное форматирование для контроля правильности вычислений.
Часть II "PRO функции"
ВПР+ПОИСКПОЗ от 0 до RPO для переноса данных между таблицами
● Логика поиска данных ВПР.
● Поиск названия строки по нескольким столбцам.
● Поиск 2-го, 3-го и следующих значений (не уникальный столбец).
● Для каких таблиц создан интервальный просмотр.
● Горизонтальный поиск данных.
● Excel считает порядковый номер столбца.
● Excel находит столбец по его названию.
● Как записать «искомое значение содержит».
● Как убрать из искомого значения лишние знаки.
● Функциональная переделка ТЕКСТА в ЧИСЛА/даты и обратно.
● Типовые ошибки функции ВПР.
ИНДЕКС+ПОИСКПОЗ (коротко основы) для переноса данных между таблицами
● Как устроен ИНДЕКС.
● Как применить, чтобы не менять столбцы таблицы местами.
● Помощник ПОИСКПОЗ.
ЕСЛИ от 0 до PRO логические функции
● Логика построения функции ЕСЛИ и ЕСЛИОШИБКА.
● Настройка числовых и текстовых условий.
● Варианты сравнения больше/меньше/равно/не равно/содержит.
● Последовательные проверки вложенными ЕСЛИ.
● Одновременная проверка нескольких условий.
● Выборочная проверка одного из условий.
● Функция оптимизации ВЫБОР.
СУММЕСЛИ/МН и СРЗНАЧЕСЛИ/МН расчёт показателей по условиям
● Логика расчётов по условию.
● Числовые и текстовые условия.
● Варианты сравнения больше/меньше/равно/не равно/содержит.
● Сравнение с числом/датой в ячейке, знак &.
● Вычисление общих итогов по вертикали и горизонтали.
СУММПРОИЗВ и управление внешними связями
● Как понимать эту функцию. Синтаксис.
● Логика создания условных массивов
● Логика создания вычисляемых массивов.
● Одновременные проверка нескольких условий.
● Выборочные проверка одного из условий.
● Замена СУММЕСЛИ для вычислений по закрытым файлам.
● Управление внешними связями: изменение источника, разрыв связей.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ
● Как работает функция.
● Особенности работы с видимыми строками по 9 коду.
● Особенности работы с видимыми строками по 109 коду.
ИМЕНА ЯЧЕЕК
● Создание имён.
● Управление именами через Диспетчер имён.
● Использование имён в формулах.
● Удаление имени с сохранением формул.
ФУНКЦИИ ДАТЫ и времени
● Расчёты по времени (часовые тарифы, интервалы в чмс).
● Расчёты по месяцам.
● Вычисление рабочего дня.
● Расчёт рабочего дня с учётом переносов функцией-макросом.
● Интервал между датами в рабочих днях.
● Интервал между датами в ГОДАХ, МЕСЯЦАХ и ДНЯХ.
● Функция ТЕКСТ.
● День недели по дате формулой (результат номером и текстом).
● Сцепка дат с текстом.
● Разбор даты на составляющие. Создание даты из элементов.
ТЕКСТОВЫЕ ФУНКЦИИ
● Перенос в отдельную ячейку первых, последних или средних знаков из ячейки ЛЕВСИМВ, ПРАВСИМВ, ПСТР.
● Как посчитать знаки, если нет явной числовой закономерности.
● Поиск неуникальных символов - второй, третий слэш и т.п.
● Функция расчёта ДЛСТР.
● Функции поиска НАЙТИ и ПОИСК.
● Функции замены ПОДСТАВИТЬ и ЗАМЕНИТЬ.
● Функция заполнения ПОВТОР.
● Функция СИМВОЛ.
8 БОНУСНЫХ функций-макросов (готовы к использованию)
● ЧИСЛО прописью.
● СУММА прописью с копейками в нескольких валютах.
● КОЛИЧЕСТВО ячеек по цвету шрифта.
● КОЛИЧЕСТВО ячеек по цвету заливки.
● СУММА ячеек по цвету шрифта.
● СУММА ячеек по цвету заливки.
● СЦЕПИТЬ по диапазону с добавлением своего разделителя.
● РАБДЕНЬ с учётом переносов.
● Создание надстройки для использования VBA-функций в любых своих файлах.