«Создание, редактирование и форматирование таблицы в среде MS Excel »
ПРАКТИЧЕСКОЕ ЗАНЯТИЕ
«Создание, редактирование и форматирование таблицы в среде MS Excel »
Технология создания и преобразования информационных объектов.
Цель работы: Изучить функциональные возможности табличного процессора MS Excel и приобрести навыки практической работы по созданию, редактированию и форматированию таблиц.
ЗАДАНИЯ ДЛЯ ВЫПОЛНЕНИЯ ПРАКТИЧЕСКОГО ЗАНЯТИЯ:
Задание 1. Создать таблицу финансовой сводки за неделю, произвести расчеты, построить диаграмму изменения финансового результата, произвести фильтрацию данных.
Исходные данные представлены на рис. 1
Порядок выполнения:
1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу (при стандартной установке MS Office выполните Пуск/Программы/Microsoft Office/ Microsoft Excel).
Рис 1. Исходные данные для Задания 1
2. Введите заголовок таблицы «Финансовая сводка за неделю (тыс. р.)», начиная с ячейки А1.
Проведите форматирование заголовка таблицы. Для этого выделите интервал ячеек от А1 до D1, объедините их кнопкой панели инструментов Объединить и поместить в центре или командой меню Формат Ячеек/вкладка – Выравнивание/отображение – Объединение ячеек. Задайте начертание шрифта – полужирное, цвет – по вашему усмотрению.
3. Для оформления шапки таблицы выделите ячейки на третьей строке A3:D3 и создайте стиль для оформления. Для этого на вкладке Главная в группе Стили выберите команду Создать стиль ячейки (рис. 2).
Рис. 2. Создание стиля ячеек
В открывшемся окне наберите имя стиля «Шапка таблиц» и нажмите кнопку Формат. На вкладке Выравнивание задайте Переносить по словам и выберите горизонтальное и вертикальное выравнивание – по центру (рис.3), на вкладке Число укажите формат – Текстовой, на вкладке Шрифт – полужирный.
Рис. 3. Форматирование ячеек – задание переноса по словам
После этого нажмите кнопку ОК. Выделите ячейки A3:D3 и примените к ним созданный стиль.
4. На третьей строке введите названия колонок таблицы – «Дни недели», «Доход», «Расход», «Финансовый результат», далее заполните таблицу исходными данными согласно Заданию 1.
Краткая справка. Для ввода дней недели наберите «Понедельник» и произведите автокопирование до «Воскресенья» (нажатие левой кнопкой мыши на маркер автозаполнения в правом нижнем углу ячейки и протянуть до ячейки А10).
Выделите ячейки А13:С13, объедините их, воспользовавшись командой Формат ячеек (вкладка Выравнивание).
5. Произведите расчеты в графе «Финансовый результат» по следующей формуле:
Финансовый результат = Доход – Расход (= C4-D4)
Поставьте курсор в ячейку D4, поставьте знак « = », щелкните по ячейке В4, поставьте знак « – » , щелкните по ячейке С4, нажмите Enter.
Введите расчетную формулу только для расчета по строке «Понедельник» – ячейка D4, далее произведите автокопирование формулы (нажатие левой кнопкой мыши на маркер автозаполнения в правом нижнем углу ячейки и протянуть до ячейки D10).
6. Для ячеек с результатом расчетов задайте формат «Денежный» с выделением отрицательных чисел красным цветом (рис.4) (выделите числовые данный последнего столбца нажмите правой кнопкой мыши Формат Ячеек/вкладка – Число/формат – Денежный/ отрицательные числа – красные. Число десятичных знаков задайте равное двум. Обозначение валюты – Нет).
Обратите внимание, как изменился цвет отрицательных значений финансового результата на красный.
Рис. 4. Задание формата отрицательных чисел красным цветом
Для числовых данных столбцов Доход и Расход примените денежный формат, Число десятичных знаков задайте равное двум. Обозначение валюты – Нет.
7. Рассчитайте средние значения Дохода и Расхода, пользуясь мастером функций (кнопка fx – рис.5).
Рис.5. Кнопка вызова мастера функций
Функция СРЗНАЧ находится в разделе «Статистические». Для расчета функции среднего значения дохода установите курсор в соответствующей ячейке для расчета среднего значения (В11), запустите мастер функций (кнопка fx) и выберите функцию СРЗНАЧ (категория – Статистические/СРЗНАЧ). В качестве первого числа (Число 1) выделите группу ячеек с данными для расчета среднего значения – В4:В10 и нажмите ОК.
Аналогично рассчитайте среднее значение расхода.
8. В ячейке D13 выполните расчет общего финансового результата (сумма по столбцу «Финансовый результат»). Запустите мастер функций и выберите функцию СУММ. В качестве первого числа выделите группу ячеек с данными для расчета суммы – D4:D10 и нажмите Enter.
Рис.6. Таблица расчета финансового результата (Задание 1)
9. Выделите всю таблицу (А3:D11), зайдите в Формат ячеек вкладка Границы и задайте для внешних границ сплошной тип линии (жирной) синего цвета, для внутренних – сплошной тип линии (тонкой) синего цвета. Нажмите ОК.
Рис. 7. Задание границ для таблицы
Выделите ячейки B11, C11, D13 и залейте желтым цветом (Формат ячеек – вкладка Заливка)
10. Постройте диаграмму (линейчатого типа) изменения финансовых результатов по дням недели с помощью мастера диаграмм.
Для этого выделите интервал ячеек с названием дней недели А4:А10, зажмите клавишу Ctrl и выделите интервал ячеек с данными финансового результата D4:D10.
Рис 8. Выбор данных для построения диаграммы
Выберите команду Вставка/Диаграмма/Линейчатая.
Рис. 9 Вставка диаграммы
Далее введите название диаграммы, воспользовавшись вкладкой Макет/Название диаграммы. Добавьте подписи данных (Макет/Подписи данных).
Рис 10. Добавление названия и подписей данных
11. Произведите фильтрацию значений дохода, превышающих 4000 р. Выделите столбец Доход (В3:В11), зайдите во вкладку Данные и нажмите на кнопку Фильтр.
Рис. 11. Добавление фильтра
Щелкните на появившийся значок и в открывшемся окне выберите Числовые фильтры и задайте условие «Больше 4000» (рис. 10).
Рис.12. Пользовательский фильтр. Добавление условия фильтра
Произойдет отбор данных по заданному условию.
Проследите, как изменились вид таблицы и построенная диаграмма.
Рис 13. Конечный вид выполненного Задания 1
Задание 2. Заполнить ведомость учета брака (рис.2.1), произвести расчеты, выделить минимальную, максимальную и среднюю суммы брака, а также средний процент брака; произвести фильтрацию данных по условию процента брака < 8 %, построить график отфильтрованных значений изменения суммы брака по месяцам.
1. Перейдите на второй лист 
и создайте таблицу согласно образцу
Рис. Исходные данные для Задания 2
К ячейкам A2: F2 примените созданный в первом задании стиль Шапка таблиц.
2. Заполните столбец Месяц используя автозаполнение.
3. Рассчитайте данные в столбце Сумма брака.
Формула для расчета:
Сумма брака = Процент брака * Сумма затрат.
Краткая справка. В столбце «Процент брака» установите процентный формат чисел (Формат ячеек/ вкладка – Число/формат – Процентный). В столбцах «Сумма брака» и «Сумма затрат» установите денежный формат без обозначений, число десятичных знаков 0.
Для выделения максимального/минимального значения установите курсор в ячейке расчета, выберите встроенную функцию Excel МАКС (МИН) из категории «Статистические», в качестве первого числа выделите диапазон ячеек значений столбца «Сумма брака» (ячейки F3:F10).
4. Задайте внутренние и внешние границы для таблицы на свое усмотрение, залейте диапазон ячеек A2: F2 цветом.