MS Excel - Розширені можливості. Рівень 2

Для успішного засвоєння матеріалу курсу, слухачам необхідно володіти знаннями курсу "MS Excel - Експерт. Для професійного використання. Рівень 1".

для досвідчених користувачів Microsoft Excel

Тема №1. Імпорт даних з інших джерел для подальшого аналізу.

1. Імпорт даних з Microsoft Access.
2. Імпорт з текстового файлу.
3. Імпорт з веб-сторінки.

Тема №2. Іменовані діапазони.

1. Способи створення імен осередків і діапазонів. Застосування поля Ім'я для створення імені, Створення імен в діалоговому вікні. Створення імені. Автоматичне створення імен на основі тексту заголовків.
2. Синтаксичні правила для імен.
3. Використання діалогового вікна. Диспетчер імен: Зміна та видалення імен.
4. Фільтрація імен.
5. Пошук іменованих діапазонів на робочому аркуші.
6. Робота з іменами осередків і діапазонів. Вставка імен в формулу. Застосування імен в уже існуючих формулах. Створення списку доступних імен на аркуші.

Тема №3. Перевірка даних. Список, що розкривається, в осередках Excel.

1. Захист осередків від введення невірних даних - Перевірка значень, що вводяться.
2. Встановлення параметрів перевірки. Створення повідомлень для введення. Створення повідомлень про помилку.
3. Перевірка даних, що вводяться з формулою. Заборона введення повторюваних значень.
4. Створення списку на основі Перевірки даних.
5. Список, що розкривається, як елемент управління - Поле зі списком.

Тема №4. Використання Excel для аналізу даних. Аналіз «що-якщо». Оптимізація даних.

1. Рішення завдань пошуку цільового значення за допомогою Підбору параметру.
2. Використання Таблиці даних для аналізу чутливості моделей на зміну вхідних даних. Таблиця даних з однієї змінної. Таблиця даних з двома змінними.
3. Численна оптимізація і пошук оптимального розподілу за допомогою надбудови.Пошук рішення.
4. Моделювання розвитку подій при невизначеності факторів з використанням Сценаріїв. Створення, перегляд, зміна сценарію. , Підсумковий звіт для вибору оптимального варіанту.

Тема №5. «Високий рівень» VLOOKUP (ВПР).

1. Динамічні діапазони при роботі з функцією VLOOKUP.
2. Багаторазове копіювання формули VLOOKUP, визначення номера стовпчика: - використання додаткових осередків, - функції СТОЛБЕЦ, ЧИСЛСТОЛБ, - визначення позиції за допомогою функції MATCH (ПОИСКПОЗ).
3. VLOOKUP за двома і більше критеріями для отримання даних з таблиці.
4. VLOOKUP і повторювані значення.
5. Пошук збігів в різних списках, порівняння баз даних.

Тема №6. Пошук даних в масиві даних за багатьма критеріями. Функції INDEX та MATCH (ИНДЕКС, ПОИСКПОЗ).

1. Пошук за одним критерієм по горизонталі і вертикалі.
2. Вибірка зі списку за декількома умовами по горизонталі.
3. Вибірка за багатьма критеріями по горизонталі і вертикалі

Тема №7.Функція "непрямої переадресації" INDIRECT (ДВССЫЛ).

1. Збір інформації з декількох листів. Використання назв листів у формулі.
2. Пов'язані списки, що розкриваються, з постійним джерелом даних.

Тема №8. Посилальна функція і функція пошуку OFFSET (СМЕЩ).

1. Зміни, пов'язані списки, що розкриваються, із змінним діапазоном даних.
2. Відображення фото при виборі значень в списку.

Тема №9. Транспонування таблиць і збереження зв'язку з вихідними даними.

1. Використання функцій INDEX та MATCH (російською ИНДЕКС, ПОИСКПОЗ).
2. Транспонування за допомогою формули масиву.
3. Функції INDIRECT, ADDRESS, ROW, COLUMN (російською ДВССЫЛ, АДРЕС, СТРОКА, СТОЛБЕЦ).

Тема №10. Зведені таблиці і діаграми.

1. Створення зведеної таблиці (діаграми).
2. Вирахування в зведених таблицях: підсумки, додаткові обчислення, обчислювані поля.
3. Макети зведених таблиць. Приховування або відображення проміжних і загальних підсумків.
4. Групування дат, числових і текстових полів.
5. Фільтрація данних зведеної таблиці. Зрізи.
6. Функція GETPIVOTDATA . Приклади використання.

Тема №11. Умовне форматування. Розширені можливості.

1. Розширення можливості використання умовного форматування з використанням функцій Excel.
2. Включення / Відключення умовного форматування за допомогою перемикача.
3. Побудова діаграми Ганта з використанням умовного форматування.

Тема №12. Можливості візуалізації даних. Діаграми.

1. Динамічна діаграма із змінним діапазоном даних.
2. Додавання на гістограму підписів з підсумковими значеннями.
3. Попарне порівняння даних за допомогою діаграми Торнадо.
4. Візуалізація досягнення цільових значень у вигляді Кульової діаграми.
5. Каскадна діаграма відхилень «Водоспад».
6. Ілюстрація плану, графіка робіт за проектом за допомогою діаграми Ганта.
7. Створення діаграми Спідометр з рухомою стрілкою.
8. "Живі" інтерактивні діаграми, Діаграма з вибором значень в списку. Включення і вимикання рядів даних за допомогою прапорців. Діаграма з прокруткою і масштабуванням.

ДОДАТКОВА ТЕМА

Ця тема читається в разі, якщо залишився час в кінці курсу. Якщо час не залишається, можна замовити окремий урок на цю тему. Тривалість 2 год.

Тема № 13. Методи прогнозування даних

1. Згладжування тимчасових рядів: метод змінного середнього.
2. Прогнозування за допомогою функцій регресії: - Прогноз методом лінійної регресії: функція FORECAST (ПРЕДСКАЗ), - Множинна лінійна регресія: функція TREND (ТЕНДЕНЦИЯ), - Складання нелінійного прогнозу: функція GROWTH (РОСТ).
3. Приклад прогнозування з використанням лінії тренда.
4. Сезонна модель прогнозування.