1 блок. Знакомство с Excel. Продолжительность 4 часа (1 день)

• Краткий обзор вкладок Excel.
• Панель быстрого доступа (Обзор и настройка).
• Параметры страницы: обзор и настройка параметров страницы, листов, полей, колонтитулов, настройка печати, защита книги и листов с помощью пароля.
• Задания: Настройка параметров страницы, переименование листов, настройка быстрого доступа, настройка печати, колонтитулов.
• Посттренинг - видеоролики.

2 блок. Работа с таблицами. Продолжительность 5 часов. (1 день)

• Обзор вкладки "Главная" (Буфер обмена, Шрифт, Выравнивание, Число, Стили, Ячейки, Редактирование)
• Создание и работа с таблицами. Редактирование (изменение шрифта, выравнивание и стилистическое оформление текста), сортировка и фильтр, поиск информации, фиксация строк и столбцов. Добавление гиперссылок.
• Работа с графическими объектами, SmartArt
• Задание создать таблицу по заданным параметрам, применить фильтры, применить функцию найти, вставить графические объекты и гиперссылки.
• Домашнее задание. (3 день)
• Посттренинг - видеоролики.

3. блок. Формулы. Функции. Ссылки и массивы. Продолжительность 8 часов. (1 и 2 день)

• Основы работы с формулами
• Сложные формулы: текстовые, логические, дата и время, финансовые, автосумма, ЕСЛИ, ЕСЛИОШИБКА, И, ИЛИ, СЧЁТЕСЛИ, СУММЕСЛИ, СЧЁТЕСЛИМН, СУММЕСЛИМН, 3D-сумма, РАЗНДАТ, ПРЕДСКАЗ, ТЕНДЕНЦИЯ, РОСТ. и др.
• Ссылки и массивы: Функции: ВПР, ИНДЕКС, ПОИСКПОЗ, ГПР, и др.
• Задание применение различных формул и функций.
• Домашнее задание 2 часа. (3 день)
• Посттренинг - видеоролики.

4. блок. Условное форматирование. Продолжительность 4 часа. (2 день)

• Рассмотрение и создание правил условного форматирования.
• Задание составление различных правил условного форматирования к таблице.
• Домашнее задание 1 час. (3 день)
• Постренинг - видеоролики.

5. Урок. Работа с диаграммами. Продолжительность 6 часов. (2 день)

•  Диаграммы: создание различных диаграмм, спарклайнов
• Форматирование и корректировка диаграмм и спарклайнов
• Задание построение диаграмм и спарклайнов
• Домашнее задание 1 час. (3 день)
• Посттренинг видеоролики.

6. Урок. Сводные таблицы. Продолжительность 5 часов. (2 день)

• Создание сводных таблиц
• Применение фильтров для создания отчетов
• Расчеты внутри сводных таблиц
• Создание срезов
• Создание сводных диаграмм
• Задание составить сводную таблицу, срез, сводную диаграмму.
• Домашнее задание 1 час. (3 день)
• Посттренинг - видеоролики.

Итоговое тестирование. 1 час 30 мин.

 Бонусный урок по макросам (видео)

Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.

Символы “+,-,*” и т.д. называются операторами, т.е. указывают на выполняемое действие.

Числа называются операндами, т.е. объектами над которыми будут выполнены действия.

 Сложные формулы

Сложные формулы включают в себя ссылки на ячейки или диапазоны ячеек, функции, вложенные функции, гиперссылки и условные операторы.

Функция СУММЕСЛИ позволяет суммировать ячейки, которые удовлетворяют определенному критерию (заданному условию).

Аргументы команды следующие:

-Диапазон – ячейки, которые следует оценить на основании критерия (заданного условия).

-Критерий – определяет, какие ячейки из диапазона будут выбраны (записывается в кавычках).

-Диапазон суммирования – фактические ячейки, которые необходимо просуммировать, если они удовлетворяют критерию.

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

Рассмотрим пример, который наглядно продемонстрирует, как использовать функцию СУММЕСЛИ и насколько удобной она может оказаться при решении определенных задач.

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

Но как быть, если нам нужно быстро посчитать заработные платы только продавцов? В дело вступает использование функции СУММЕСЛИ:

-Прописываем аргументы.

-Диапазоном в данном случае будет являться список всех должностей сотрудников, потому что нам нужно будет определить сумму заработных плат. Поэтому проставляем E2:E14.

-Критерий выбора в нашем случае – продавец. Заключаем слово в кавычки и ставим вторым аргументом.

-Диапазон суммирования – это заработные платы, потому что нам нужно узнать сумму зарплат всех продавцов. Поэтому F2:F14.

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

Аналогично можно подсчитать зарплаты других сотрудников. Когда таблица небольшая, кажется, что все можно сосчитать и вручную, но при работе со списками, в которых по несколько сотен позиций, целесообразно использовать СУММЕСЛИ.

Домашнее задание №1:

1.Скопируйте на Лист АББ2 таблицу из предыдущего урока и добавьте в нее столбец Сумма.

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

3.Посчитайте Итоговую сумму.

4.С помощью функции СУММЕСЛИ посчитайте общую стоимость всей одежды, аксессуаров, обуви.

Условное форматирование

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

Инструмент “Условное форматирование” находится на главной странице в разделе “Стили”.

При нажатии на стрелочку справа открывается меню для условий форматирования.

Выделите диапазон значений. Откройте меню “Условного форматирования”. Выберите то правило, которое нужно задать.

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

При применении условного форматирования внешний вид ячеек изменяется на основании указанных вами условий. 

Домашнее задание №2:

1.Скопируйте на Лист АББ3 таблицу и создайте правило форматирования к столбцу "Количество"вашей таблицы.

Тип правила выберите "Форматировать все ячейки на основании их значений".

В описании правил укажите:

Стиль формата: набор значков

Стиль значка: выберите 1-й вариант светофора

Тип: Число

Зеленый значок присвойте числам значение которых >=40

Желтый значок числам, которые >=30

Красный значок числам <30

2.Создайте правило форматирования к столбцу "Цена за шт.".

Тип правила выберите "Форматировать все ячейки на основании их значений".

В описании правил укажите:

Стиль формата: трехцветная шкала

Тип: во всех трех ячейках - Число

Минимальное значение укажите 3000, среднее значение 10000,

максимальное значение 25000.

Цвета выберите красный - минимальное значение, желтый - среднее значение, зеленый - максимальное значение.

Должна получиться следующая таблица:

Внимание! Задания после каждого урока просим выполнять на отдельных листах Excel. В этом уроке 2 домашних задания. Все домашние задания прислать одним файлом, после прохождения всех уроков.

На вкладке “Главная” находятся несколько наиболее важных групп инструментов: Буфер обмена, Шрифт, Выравнивание, Число, Стили, Ячейки, Редактирование.

С помощью расширенных функций Буфера обмена можно повысить производительность работы в Excel при выполнении копирования и вставки элементов. Это одни из наиболее часто используемых операций при работе с программой Excel.

Буфер обмена может содержать до 24 элементов. В Excel неважно, что копировать: таблицу, столбец, строку, содержимое одной ячейки или просто текст – все это является отдельными элементами в независимости от его объема их информации.

Сортировка и фильтр

Сортировка в Excel – это распределение строк таблицы в определенном порядке, который соответствует конкретным условиям пользователя. Упорядочивание строк при сортировке всегда выполняется относительно выбранного столбца.

Перейдите на любую ячейку таблицы с данными.

Выберите инструмент: «Главная»-«Редактирование»-«Сортировка и фильтр»-«Фильтр»

Вы можете сделать сортировку:

-от А до Я;

-от Я до А;

-по цвету.

Найти и выделить

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

Находясь во вкладке “Главная”, кликните по кнопке “Найти и выделить”, которая расположена на ленте в блоке инструментов “Редактирование”. В появившемся меню выберете пункт “Найти”. Вместо этих действий можно просто набрать на клавиатуре сочетание клавиш Ctrl+F.

После того, как вы перешли по соответствующим пунктам на ленте откроется окно “Найти и заменить” во вкладке “Найти”. В поле “Найти” вводим слово, символы, или выражения, по которым собираетесь производить поиск. Щелкните на кнопку “Найти далее”, или на кнопку “Найти всё”.

При нажатии на кнопку “Найти далее” вы переместитесь к первой же ячейке, где содержатся введенные группы символов. Сама ячейка становится активной.

 Шрифты

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

Изменение шрифта:

-Выделите ячейку, к которой необходимо применить форматирование.

-Нажмите на стрелку рядом с полем Шрифт на вкладке “Главная”.

-В появившимся раскрывающемся списке выберите необходимый шрифт.

Изменение цвета шрифта:

-Выделите ячейки, цвет шрифта в которых необходимо изменить.

-Нажмите стрелку рядом с командой Цвет текста на вкладке “Главная”. Появится палитра цветов.

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

Выравнивание

Выделите ячейки с текстом, который вы хотите выровнять.

На вкладке “Главная” выберите один из указанных ниже вариантов выравнивания.

-Чтобы выровнять текст по вертикали, выберите Выровнять по верхнему краю, Выровнять по середине или Выровнять по нижнему краю .

-Чтобы выровнять текст по горизонтали, выберите Выровнять по левому краю, Выровнять по центру или Выровнять по правому краю.

Если строка с текстом слишком длинная, его часть может быть не видна. Чтобы исправить это, не меняя ширину столбца, нажмите Перенести текст.

-Чтобы выровнять по центру текст, занимающий несколько строк или столбцов, нажмите кнопку Объединить и выровнять по центру.

Число

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

Программа предлагает выбрать один из следующих основных видов форматирования:

Общий; Денежный; Числовой; Финансовый; Текстовый; Дата; Время; Дробный;     Процентный; Дополнительный.

Кроме того, существует разделение на более мелкие структурные единицы вышеуказанных вариантов. Например, форматы даты и времени имеют несколько подвидов (ДД.ММ.ГГ., ДД.месяц.ГГ, ДД.М, Ч.ММ PM, ЧЧ.ММ и др.).

Самый популярный способ изменения форматов диапазона данных – это использование контекстного меню.

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

Активируется окно форматирования. Выполните переход во вкладку "Число", если окно было открыто в другом месте. Именно в блоке параметров "Числовые форматы" находятся все варианты изменения характеристик. Выберите пункт, который соответствует данным, находящимся в выбранном диапазоне. Нажмите кнопку "ОК".

Домашнее задание:

1.На Листе АББ1 создайте таблицу, как в примере ниже:

2.Текст названия столбцов сделайте шрифтом Calibri 11

3.Текст в ячейках сделайте шрифтом Times New Roman 11, Текст в столбцах ”Количество” и “Цена за шт.” сделайте Times New Roman 14.

4.Текст в столбце “Дата поступления” выделите курсивом. Текст в столбце “Наименование товара” выделите полужирным. Цвет текста в столбце “Отдел” сделайте синим.

5.Выравнивание текста сделайте по центру.

6.Ячейки с названием столбцов выделите цветом, как в примере.

7.Примените фильтр к столбцу “Дата поступления” и сделайте сортировку от старых к новым.

Внимание! Задания после каждого урока просим выполнять на отдельных листах Excel. Все домашние задания прислать одним файлом, после прохождения всех уроков.

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

На вкладке “Вставка” щелкните по кнопке сводные таблицы. В диалоговом окне создание сводной таблицы укажите источник данных и место расположение создаваемой таблицы.

Откроется "Список полей сводной таблицы", которая находится в правой части листа.

В верхней части панели “Список полей сводной таблицы” будет отражен перечень названия полей исходной таблицы с флажками. Перетащите названия полей в нужные разделы макетов таблицы, представленные в нижней части панели задач.

Макет сводной таблицы включает разделы:

“Фильтр отчета” соответствует области страницы сводной таблицы. Значения полей, помещенных в эту область, станут заголовками страниц сводной таблицы.

Раздел “Значения” соответствует области данной сводной таблицы. Сюда помещают поля, по которым будут вычисляться итоговые данные.

Раздел “Название столбцов” соответствует области столбцов сводной таблицы. Значение полей, помещенных в эту область станут заголовками столбцов сводной таблицы.

Значение полей, помещенных в область раздела “Название строк” станут заголовками строк сводной таблицы.

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

 Срезы, которые используются только в одной сводной таблице называются локальными. Связанные сразу с несколькими таблицами – общими.

 Создание среза для фильтрации данных:

1.Щелкните в любом месте таблицы или сводной таблицы.

2.На вкладке " Главная " перейдите в раздел “Вставка”, кнопка “Срез”.

3.В диалоговом окне “Вставка срезов” установите флажки для полей, которые вы хотите отобразить, а затем нажмите кнопку "ОК".

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

Чтобы выбрать несколько элементов, нажмите клавишу CTRL и, удерживая ее нажатой, щелкните каждый из элементов, которые нужно отобразить.

Чтобы очистить фильтры среза, выберите команду очистить фильтра в срезе.

Домашнее задание:

1. Скопируйте таблицу из предыдущего урока на Лист АББ5 и создайте в нём сводную таблицу.

В поле сводной таблицы проставьте флажки во всех перечнях полей сводной таблицы (Ф.И.О и месяцы).

В раздел “Столбцы” перетащите область Ф.И.О, в разделе “Строки” должна быть Сумма значения, в разделе “Значения” появятся суммы по всем месяцам.

2.Сделайте Срез по Ф.И.О и месяцам.

Внимание! Задания после каждого урока просим выполнять на отдельных листах Excel. Все домашние задания прислать одним файлом, после прохождения всех уроков.

Ряд данных - линии, полосы, секторы и пр., с помощью которых отображаются значения на диаграмме.

Категория - число элементов в некотором ряду данных.

Легенда – прямоугольная область, содержащая определения рядов данных.

Ось – одна из сторон диаграммы.

Построение диаграмм

1 способ:

1.Выделите таблицу, включая заголовки ее столбцов и строк.

2.Нажмите на клавишу F11.

При помощи клавиши F11 произойдет построение диаграммы заданного по умолчанию типа на отдельном листе.

2 способ:

1.Перейдите на вкладку “Вставка”

2.Выделите таблицу с данными

3.На ленте приложения щелкните по кнопке соответствующей требуемому типу диаграмм.

Форматирование диаграмм

1.Щелкните мышью по внедренной на листе диаграмме. Должна появиться рамка вокруг диаграммы. Таким образом можно активировать любой элемент диаграммы, в соответствующем поле увидите название выделенного элемента.

2.Изменение элементов диаграммы можно выполнять с помощью соответствующих инструментов на Вкладке “Работа с диаграммами”. Это вкладки: Конструктор, Макет, Формат. Изменять элементы диаграммы можно при помощи команд контекстного меню выбранных объектов.

Кнопка “Выбрать данные” навкладке “Конструктор” раскрывает диалоговое окно “Выбор источника данных”, которое позволяет изменить набор исходных данных используемых для построения диаграммы.

При помощи команды “Строка/столбец” вы можете поменять строки и столбцы местами.

При помощи кнопок “Добавить, Изменить, Удалить” можно настроить элементы Легенды, т.е. ряды по своему усмотрению.

При помощи кнопки “Изменить” можно изменить подписи для горизонтальной оси.

Макеты диаграмм содержит палитру с образцами различного оформления диаграмм.

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

Домашнее задание №1:

1.На Листе АББ4 создайте таблицу, как в примере ниже:

2.Создайте диаграмму по данным таблицы.

3.Выберите тип Гистограмма с группировкой

4.Название диаграммы должно быть Продажи 2020г.

5.Подпишите оси: горизонтальная – Ф.И.О., вертикальная –количество, шт.

6.Расположение легенды выберите снизу.

7.Сделайте градиентную заливку фона.

Получившаяся диаграмма должна выглядеть следующим образом:

Спарклайны

Спарклайны — это нарисованные прямо в ячейках миниатюрные диаграммы, наглядно отображающие динамику наших данных.

Чтобы их создать, нажмите “Спарклайны» на вкладке «Вставка». В открывшемся окне укажите диапазон с исходными числовыми данными и ячейки, куда вы хотите вывести спарклайны.

После нажатия на кнопку "ОК" Microsoft Excel создаст их в указанных ячейках. На появившейся вкладке "Конструктор” можно дополнительно настроить их цвет, тип, включить отображение минимальных и максимальных значений и т. д.

Домашнее задание №2:

По данным таблицы сделайте 2 вида спарклайнов на Листе АББ4:

1.В виде графика

2.В виде гистограммы, с выделением максимальной и минимальной точек.

Получившиеся спарклайны должны выглядеть следующим образом:

Внимание! Задания после каждого урока просим выполнять на отдельных листах Excel. В этом уроке 2 домашних задания. Все домашние задания прислать одним файлом, после прохождения всех уроков.

-Составлять различные таблицы;

-Оформлять финансовые документы;

-Программировать сложные вычисления;

-Моделировать различные процессы;

-Строить диаграммы и графики.

Вы можете использовать, как встроенные в Excel формулы и функции, так и создавать свои собственные.

Рассмотрим Вкладки на Ленте Инструментов:

Вкладка "Файл" отвечает за самые главные действия: создание, открытие, сохранение, печать документа.

На вкладке "Главная" собраны команды по основным операциям редактирования.

Вкладка "Вставка" отвечает за создание и вставку разных объектов.

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

На вкладке "Формулы" собраны команды, связанные с вычислениями в таблице Excel.

Вкладка "Данные" позволяет задавать параметры подключения к внешнему источнику данных, управлять процессами получения данных, задавать способ размещения полученных данных таблиц и задавать способ отображения данных в таблице.

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

Вкладка "Вид" отвечает за то, как выглядит программа внешне, видны или скрыты те или иные ее основные инструменты.

Панель быстрого доступа

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

Книги и Листы

Книга — это файл, который содержит один или несколько листов для упорядочения данных. 

Создание Книги:

1.Откройте вкладку "Файл" и выберите пункт Создать.

2.Откройте шаблон двойным щелчком.

3.Щелкните и начните вводить текст.

Операции с рабочими листами

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

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

Параметры страницы

Для того, чтобы рассмотреть параметры страницы мы должны активировать на Ленте инструментов вкладку "Разметка страницы" и мы увидим группу команд "Параметры страницы".

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

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

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

Вкладка "Лист" позволяет определить какая именно информация из текущей Книги и в каком порядке будет выведена на печать.

Домашнее задание:

1.Создайте и сохраните Excel файл под названием Обучение Excel.

2.Создайте в файле 5 листов под названием: АББ1, АББ2, АББ3, АББ4, АББ5.

3.Пропишите в верхнем колонтитуле слева свою Фамилию и Имя.

Внимание! Задания после каждого урока просим выполнять на отдельных листах Excel. Все домашние задания прислать одним файлом, после прохождения всех уроков.

* В некоторых роликах вы услышите о том, что информация относится к Microsoft Excel 2010г., эти же функции также работают и в версиях 2013 и 2016 гг.

Обучение состоит из 5 уроков:

1. Урок. Знакомство с программой Excel. Продолжительность 45 мин.

• Возможности программы Excel
• Обзор вкладок на ленте инструментов (Файл, Главная, Вставка, Разметка страницы, Формулы, Данные, Рецензирование, Вид)
• Панель быстрого доступа (Обзор и настройка)
• Параметры страницы: обзор и настройка параметров страницы, листов, полей, колонтитулов, настройка печати
• Домашнее задание

2. Урок. Работа с таблицами. Продолжительность 1 час.

• Обзор вкладки "Главная", на которой находятся несколько наиболее важных групп инструментов (Буфер обмена, Шрифт, Выравнивание, Число, Стили, Ячейки, Редактирование)

• Создание и работа с таблицами. Редактирование (изменение шрифта, выравнивание и стилистическое оформление текста), сортировка и фильтр, поиск информации.
• Домашнее задание

3. Урок. Формулы. Условное форматирование. Продолжительность 1 час.

• Основы работы с формулами
• Сложные формулы
• Условное форматирование ячеек
• Домашнее задание

4. Урок. Работа с диаграммами. Продолжительность 1 час 30 мин.

• Диаграммы: создание диаграмм, спарклайнов
• Форматирование и корректировка диаграмм и спарклайнов
• Домашнее задание

5. Урок. Сводные таблицы. Продолжительность 25 мин.

• Создание сводных таблиц
• Применение фильтров для создания отчетов
• Создание срезов
• Домашнее задание

Внимание! Задания после каждого урока просим выполнять на отдельных листах Excel. Все домашние задания прислать одним файлом, после прохождения всех уроков.