Курсы High Tech

Office 365 и Microsoft Office 2013/2016/2019

Продвинутый пользователь Microsoft Excel. Уровень II.


Продолжительность: 16 ч    

Описание курса

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

Курс является продолжением Уровня I и длится 16-20 часов (в зависимости от количества участников - это 2-3 дня с 10:00 до 17:00 или 8-10 будних дней по 2 часа в день, расписание дневное или вечернее, согласовывается с группой заранее). Есть вариант занятий по субботам и ДИСТАНЦИОННО!
Входные требования: знание Excel на базовом уровне

Программа курса

Microsoft Office 365
***Особенности Microsoft Office 365
* Автоматическое обновление версий
* Свободно меняемая подписка на 5 любых устройств
* Кроссплатформенность, мобильность
* Онлайн версии приложений, возможность работы без локальной установки
* Модульность, магазин приложений
* Интерфейс версии Excel 2019

Форматирование ячеек
* Числовые форматы, числа, доли, валюты, даты, текст и др
* Влияние региональных параметров и форматов ячеек в Excel, отключение влияния локализации "Точка или запятая?" Ошибка #VALUE(#ЗНАЧ)
* "Сюрпризы" при импорте из других программ (1С)
* Пользовательские форматы,
Написание своих кодов форматов для вывода чисел, дат, денежных единиц и текста в нужном виде, отсутствующем в шаблонах по умолчанию
* Создание дополнительные форматов; ИИН, БИН, телефонов. Счетов и пр

Новые типы данных в Excel 2019
* Тип данных Акции
* Тип данных География

Условное форматирование
* Быстрые правила условного форматирования: применение различного оформления ячеек в зависимости от величины числа, содержания текста, даты, повторяемости
* Применение гистограмм, шкал, значков
* Выделение диапазона чисел, нахождение нужного числа самых больших и малых значений
* Создание своих собственных правил условного форматирования с помощью формул и функций
* Создание своих собственных правил условного форматирования с помощью кодов формата ячеек
* Приоритет правил, порядок применения, удаление и копирование правил

Быстрое заполнение диапазонов
* Автозаполнение дней недели, месяцев, дат
* Создание прогрессии арифметической, геометрической, дат
* Зависимость встроенных списков от региональных параметров
* Создание пользовательских списков автозаполнения
* Функция мгновенного заполнения

Вычисления в Excel
* Ссылки в формулах. Стили ссылок: Относительные, Абсолютные, Смешанные, "Зачем нужен знак $. Один или два"?
* Стили ссылок A1 и R1C!1 Что значат квадратные скобки [ ]?
* Ссылки на листы в книге, трехмерные ссылки для консолидации, ссылки на другие книги
* Аудит формул, отслеживание зависимостей
* Обнаружение источника ошибок
* Пошаговые вычисления
* Автоматические и ручные вычисления
* Обновление формул, изменение и разрыв связей в формулах
* Вычисления с диапазонами без созданий формул
* Имена диапазонов и ячеек. "Зачем называть ячейки?"
* Запись вычислений с помощью простых формул и с использованием имён функций
* Соответствие русских и английских имён функций
* Самые распространённые функции в Excel,
* Категории функций математические, текстовые, логические, дата и время, ссылки и массивы, статистические, финансовые

Вычисления с множественными условиями, дополняющими друг друга или противоречащими друг другу
* Функции ЕСЛИ(IF), ИЛИ(OR), И(AND), ЕСЛИМН(IFS), ПЕРЕКЛЮЧ(SWITCH)
* Функции ИСТИНА(TRUE), ЛОЖЬ(FALSE)
* Функции СУММЕСЛИ(SUMIF), СУММЕСЛИМН(SUMIFS), СРЗНАЧЕСЛИ(AVERAGEIF), СРЗНАЧЕСЛИМН(AVERAGEIFS), СЧЁТЕСЛИ(COUNTIF), СЧЁТЕСЛИМН(COUNTIFS)
* Связывание функций друг с другом, вложение функций одна в другую

Подавление ошибок в вычислениях
* Функции ЕСЛИОШИБКА(IFERROR), ЕНД(ISNA), ЕСНД(IFNA)

Вычисления с датами и временем
* Дата и время – то же числа,
* Особенности формата даты
* Вставка текущей даты, функция СЕГОДНЯ(TODAY)
* Расчёт календарных и рабочих дней, с учетом праздников при пятидневной рабочей недели, функции РАБДЕНЬ(WORKDAY), ЧИСТРАБДНИ(NETWORKDAYS)
* Расчёт календарных и рабочих дней, с учетом праздников при рабочей недели с любым количеством выходных дней и их чередованием функции РАБДЕНЬМЕЖД(WORKDAY.INT), ЧИСТРАБДНИМЕЖД(NETWORKDAYS.INT)
* Вычисление дня недели для любой даты с помощью функции ДЕНЬНЕД(WEEKDAY) или с помощью формата ячеек
* Вычисление интервала(например стажа) в годах, месяцах, днях
* Пересчёт дат в разных календарях и системах
* Особенности формата времени
* Вставка текущего времени, функция ТДАТА(NAW)
* Вычисление интервалов в сутках, часах, минутах, секундах
* Связь временных вычислений с другими вычислениями

Операции с текстовыми ячейками
* Связывание, объединение текста из различных ячеек функции СЦЕПИТЬ(CONCATENATE), СЦЕП(CONCAT), ОБЪЕДИНИТЬ(TEXTJOIN)
* Разбиение текста на несколько столбцов
* Извлечение нужных символов функции ЛЕВСИМВ(LEFT), ПРАВСИМВ(RIGHT), ПСТР(MID)
* Поиск и замена одного текста на другой функции НАЙТИ(FIND), ПОИСК(SEARCH), ЗАМЕНИТЬ(REPLACE), ПОДСТАВИТЬ(SUBTITUTE)
* Удаление лишних пробелов СЖПРОБЕЛЫ(TRIM)
* Удаление непечатных символов, функция ПЕЧСИМВ(CLEAN)

Упорядочивание данных с помощью сортировки
* Сортировка строк и столбцов
* Многоуровневая сортировка
* Сортировка по значениям, цвету, значкам
* Сортировка по возрастанию(убыванию)
* Сортировка с помощью пользовательского списка сортировки

Отбор нужных значений с помощью фильтрации
* Простой автофильтр
* Настраиваемый фильтр с условиями И-ИЛИ с использованием масок подстановки
* Расширенный фильтр с заданием сложных условий
*** Множественные условия И-ИЛИ
*** Выборка результата в отдельный диапазон
*** Отбор уникальных значений
*** Задание условий с помощью формул и функций
* Удаление дубликатов

Промежуточные итоги
* Расчёт промежуточных итогов: суммы, среднего, количества, максимума, минимума и др.
* Вывод итогов групп на отдельные листы
* Детализация(углубление) промежуточных итогов
* Консолидация данных
* Структура

Инструмент “Умные" таблицы с динамическими именами(“SMART” TABLE)
* Новый тип имён -- динамические диапазоны, автоматически меняющие размер
* Структурированные ссылки -- новый тип формул c квадратными скобками [ ], НЕ R1C1
* Синтаксис формул в “умных” таблицах

Сводные таблицы(PIVOT TABLE)
* Построение сводных таблиц
* Обновление сводных таблиц
* Вычисление нескольких показателей в одной сводной таблице
* Стандартные вычисления в сводных таблицах(сумма, среднее, количество, максимум, минимум и др)
* Дополнительные вычисления в сводных таблицах(отличия, доли, ранги)Н
* Написание формул и использование функций для своих собственных вычислений в сводной таблице
* Группировка дат и временных интервалов в сводных таблицах
* Календарь с помощью сводной таблицы
* Инструмент временная шкала
* Новый инструмент перекрёстной фильтрации сводных таблиц – срезы
* Построение сводной диаграммы
* Типы макетов сводных таблиц, классический и современный
* Построение сводной таблицы из консолидированного диапазона
* Построение сводной таблицы из разных источников
* Настройка сводных таблиц, отключение вывода ошибок, промежуточных итогов
* Функция ПОЛУЧИТЬ ДАННЫЕ СВОДНОЙ ТАБЛИЦЫ(GET PIVOT DATA)

Проверка значений ячеек, запрет на ввод неправильной информации(DATA VALIDATION)
* Проверка значений ячеек при вводе, запрет на ввод неправильной информации: неверной даты, цены, имени и т.д.
* Поиск неверных значений в готовом файле
* Запрет на ввод дубликатов, запрет на ввод текста вместо чисел
* Использование формул и функций для проверки данных
* Создание связанных выпадающих списков в несколько уровней
* Создание связанных выпадающих списков для динамических(“умных”) таблиц

Вычисления с выборкой и подстановкой данных из связанных таблиц
* Категория функций ССЫЛКИ И МАССИВЫ(LOOKUP AND VALUE)
* Функции для вертикальной и горизонтальной подстановки ВПР(VLOOKUP) и ГПР(HLOOKUP)
* Функция ДВССЫЛ(INDIRECT)
* Функции для любой подстановки по нескольким условиям ПОИСКПОЗ(MATCH), ИНДЕКС(INDEX), СУММПРОИЗВ(SUMPRODUCT)

Формулы массивов
* Синтаксис формул массивов, использование фигурных { } скобок в формулах
* Функции связанные с массивами
* Использование формул массивов в различных вычислениях для диапазонов и отдельных ячеек

Анализ "Что-если"
* Таблицы подстановок{TABLE}
*** Таблицы подстановки – ещё один вариант использования массивов
*** Таблицы подстановок с одной переменной
*** Таблицы подстановок с двумя переменными
*** Использование таблиц подстановок в финансовом примере

* Сценарии
*** Построение таблицы, задание целевой и изменяемых ячеек
*** Запись возможных вариантов вычисления в виде различных сценариев в одну таблицу
*** Сравнение сценариев в виде отчёта

* Подбор параметра
*** Подбор параметра, решение задач "от обратного – известен результат, подбери исходные данные"

* Поиск решения
*** Поиск оптимального решения с несколькими неизвестными("подбор параметра" множественный)
*** Определение цели и постановка задачи("максимум", "минимум", "значение")
*** Определение изменяемых параметров связь их с целью с помощью формулы
*** Задание ограничений с помощью формул
*** Запуск вычисления, внесение изменений, сохранение результатов
*** Использование поиска решения совместно со сценариями

Надстройки(Add ins) обзорно(подробно в отдельном курсе)
* Понятие о дополнительных модулях(plug-ins)
* Получение надстроек из магазина Microsoft Office
* Надстройки Excel и надстройки Com
* Инструмент Inquire, быстрое сравнение версий файла(нахождение изменений), удаление излишнего форматирования, диаграммы связей между файлами, листами и ячейками
* Модуль Power Map, визуализация данных на 3D глобусе
* Краткий обзор плагина Power Pivot, импорт и обработка данных превышающих размер листа Excel(подробно в отдельном курсе)
* Почему спрятали надстройку Power View

Power Query – уже не надстройка!
* Консолидация данных с помощью Power Query
* Импорт данных
* Краткий обзор Power Query(подробно в отдельном курсе)

Инструмент формы(опционально)
* Вкладка разработчик
* Использование готовых элементов управления "формы" на листе Excel
* Связь элементов форм с ячейками с помощью формул

Основы работы с макросами(опционально! Подробно в отдельном курсе - Программирование на VBA Excel)
* Понятие о макросе
* Запись макроса с помощью макрорекодера
* Относительные и абсолютные ссылки в макросе
* Создание макроса для одного файла Excel или для всех файлов Excel на этом компьютере
* Редактор VBA, редактирование кода макроса в нём
* Безопасность макросов, разрешение и запрещение запуска макросов
* Назначение макроса кнопке, или объекту
* Создание пользовательских функций и форм(элементов ActiveX) в VBA
* Удаление, перемещение, копирование и пересылка макросов