Лабораторная работа №1
Создание и редактирование таблиц в, выполнение расчетов и построение диаграмм в MS Excel
Цель работы: Научиться использовать электронные таблицы MS Excelдля выполнения расчетов и построения диаграмм.
Задание:
1. Создать таблицу в (табл.1). Имеющиеся в шапке таблицы месяцы заносить с помощью автозаполнения.
2. Оформить таблицу с помощью обрамления, добавить заголовок, расположив его по центру таблицы. Текст шапки таблицы выполнить полужирным шрифтом, текст в первом столбце курсивом.
3. Выполнить соответствующие вычисления с помощью встроенных функций Excel, применяя копирование формул.
4. Сделать заливку таблицы.
5. Построить диаграммы и отформатировать (обрамление, заливка).
Таблица 1 – Исходные данные
| Наименование товара | Стоимость 1 ед. продукции | Продано за 1 полугодие прошлого года (тыс. шт.) | Продано за 1 полугодие текущего года (тыс. шт.) | |||||
| Январь | Февраль | Март | Апрель | Май | Июнь | |||
| Цитрамон | 11,47₴ | |||||||
| Анальгин | 5,46₴ | |||||||
| Но-шпа | 47,30₴ | |||||||
| Йод | 7,36₴ | |||||||
| Септефрил | 5,21₴ | |||||||
| Диазолин | 6,29₴ | |||||||
| Флюколд | 46,51₴ |
Найти:
1. Среднее количество проданных медикаментов каждого наименования за текущий год.
2. Выручку от продажи медикаментов каждого наименования за текущий год и от продажи всех медикаментов за каждый месяц текущего года.
3. Общее количество проданных медикаментов за каждый месяц.
4. Минимальное и максимальное количество медикаментов за полугодие, количество максимальных продаж.
|
|
5. Вклад (в %) продажи цитрамона в общее количество проданных медикаментов за предыдущее полугодие и каждый месяц текущего года.
6. Построить гистограмму, отражающую количество проданных медикаментов по наименованиям за каждый месяц текущего года.
Порядок выполнения лабораторной работы
Определение структуры таблицы и ввод текста.
Рассмотрим некоторые особенности ввода текста в ячейки рабочего листа. Текст «Наименование товара», который вводится в ячейку А1, целиком в этой ячейке не помещается и занимает еще и ячейку В1. Поскольку в ячейку В1 не было введено никакой информации, текст виден полностью. При вводе в ячейку В1 текста «Стоимость 1единицы продукции», текст в ячейке А1 будет виден частично, в пределах границ столбца А. Если при вводе информации ширина столбца оказалась недостаточной для полного вывода содержимого ячейки, необходимо либо изменить ширину всего столбца, либо отформатировать одну ячейку. Изменить ширину столбца можно несколькими способами:
a) пометить столбец (столбцы). Выбрать вкладку Главная – Ячейки – Формат – Ширина столбца. В появившемся окне указать нужную ширину столбца;
b) установить ширину столбца по самому длинному в нем значению: дважды щелкнуть по линии, отделяющей его заголовок от заголовка столбца справа или Главная – Ячейки – Формат – Автоподбор ширины столбца;
c) изменить ширину столбца с помощью мыши: установить указатель мыши в области заголовков столбцов на линии, отделяющей этот столбец от соседнего справа столбца. Указатель мыши примет форму обоюдонаправленной стрелки. Удерживая левую кнопку мыши, необходимо перетащить линию раздела столбцов вправо или влево. Ширина столбца выводится в поле имени в строке формул. Кнопку мыши можно отпустить, когда ширина столбца достигнет нужного размера;
|
|
Кроме того, для форматирования текста в ячейке можно воспользоваться командой Главная – Число – Выравнивание. Опция Перенос по словам позволяет увидеть весь введенный в ячейку текст, при этом изменяется не ширина столбца, а ширина строки. Те же действия можно выполнить, выделив ячейку и выбрав контекстное меню Формат ячеек – Выравнивание.
Итак, выделяем ячейки A1:A2, выполняем команды Главная – Выранивание пиктограммы: Объединить и поместить в центре, Перенос текста, Выровнять по центру (рис.1).
Рисунок 1 – Выравнивание
Не снимая выделения ячейки A1, выполняем Главная 
Буфер обмена Формат по образцу (кисточка). Для автоматизации ввода формата кисточкой выделяем диапазон ячеек B1:C2. Теперь вводим текст первого, второго и третьего столбцов шапки таблицы. Далее, выделяем D1:I1, выполняем Объединить и поместить в центре, Выровнять по середине, вводим текст «Продано за 1 полугодие текущего года (тыс. шт.)». Выделяем D2, выполняем команды: Выровнять по середине, затем вводим текст «Январь». Для автоматизации ввода часто повторяющихся последовательностей данных (дни недели, названия месяцев, года и т.д.) в MS Excel существует автозаполнение. Выделим ячейку с текстом «Январь» и перетащим маркер автозаполнения (черный крестик в правом нижнем углу ячейки) в нужном направлении на число ячеек, равное количеству элементов, которые необходимо включить в список.
|
|
Для того чтобы добавить к полученной таблице заголовок, необходимо вначале добавить пустую строку. Установим курсор в ячейку D1 и выполним команду Главная – Ячейки – Вставить или команду контекстного меню Вставить…, выполнение которой влечет за собой появление окна, изображенного на рис.2.
Рисунок 2 –Добавление строки
Введите фразу «Отчет о реализации медикаментов» в ячейку А1, которая теперь свободна, т.к. вся таблица сместилась вниз на одну строку. Для оформления заголовка выделим ячейки А1:I1 (см. рис.1). Затем выполним команду Главная – Выравнивание – 
, установив опцию Объединить и поместить в центре (то же самое можно выполнить при помощи контекстного меню Формат ячеек – Выравнивание – Объединение ячеек.
Для обрамления шапки таблицы необходимо и в контекстном меню выбрать пункт Формат ячеек – Граница, либо воспользоваться кнопкой Границы 
тема Шрифт вкладка Главная.
Изменение типа, фона и цвета шрифта осуществляется с помощью пункта контекстного меню Формат ячеек – Шрифт,либо с помощью кнопоктемы Шрифт вкладка Главная.
Изменение типа, фона и цвета шрифта происходит в текущей ячейке или в выделенной области.
Заполните таблицу числовыми данными (рис.3.).
Рисунок 3 – Исходные данные
Переименуйте Лист1 книги Excel в «Практическая работа №1» и удалите пустые листы
Переименовать лист можно несколькими способами:
1) с помощью пункта меню Главная – Ячейки – Формат – Переименовать лист;
2) щелкнуть правой клавишей мыши на ярлыке листа, в появившемся контекстном меню выбрать пункт Переименовать;
3) щелкнуть дважды левой клавишей мыши на ярлыке листа и ввести новое название.
Рисунок 4 – Переименование листа
Ввод формул
Формула в Excel – это способ вычисления с использованием информации, хранящейся в других ячейках. Ссылки (табл. 2) на ячейки осуществляются по их адресам (относительным, абсолютным или смешанным).
Таблица 2 – Виды ссылок
| Пример | Виды ссылок |
| А1 | Относительная ссылка |
| $A$1 | Абсолютная ссылка |
| $A1 | Смешанная ссылка (адрес столбца является абсолютной ссылкой) |
| A$1 | Смешанная ссылка (адрес строки является абсолютной ссылкой) |
Ввод формулы начинается со знака «=». Для написания формул используются операторы:
– арифметические: +, -, *, /, %, ^;
– сравнения: =, >, <, ³, £, < >;
– связи:: – диапазон (=СУММ (В1: С10));
; – объединение (=СУММ (А1; А3; В1: С4));
– соединения текстов: & (= «общее количество в» &C18 «году:» &D1).
Иерархия (старшинство) операций естественная, как в математике. Для изменения иерархии используются круглые скобки.
В формулах могут использоваться встроенные функции Excel (более 300 функций), которые делятся на категории. Для ввода функций в Excel используется панель формул (рис. 5 – 6):
Рисунок 5 – Вызов мастера функцій
Рисунок 6 – Мастер функций
Ниже приведены основные функции, используемые при выполнении данной работы:
1. Математические:
• СУММ – сумма аргументов;
• ПРОИЗВЕД – произведение аргументов;
• СУММПРОИЗВ – сумма произведений соответствующих массивов.
2. Статистические:
• СРЗНАЧ – среднее арифметическое аргументов;
• МАКС – максимальное значение из списка аргументов;
• МИН – минимальное значение из списка аргументов;
• СЧЕТЕСЛИ – подсчитывает количество непустых ячеек в диапазоне, удовлетворяющих заданному условию.
В ячейках J4:J10 производится расчет объема продаж за 1 полугодие текущего года. Ячейки K4:K10 содержат вычисления выручки от продажи товара каждого наименования за текущий год (рис. 7 – 9).
Рисунок 7 – Нахождение суммы чисел
Рисунок 8 – Заполнение ячеек данными
Рисунок 9 – Расчет показателей объема продажи и реализации
В диапазоне D11:I11 подсчитывается количество медикаментов, проданных за каждый месяц текущего года. В ячейки D12:I12 введена формула для расчета ежемесячной выручки (рис. 10): = СУММПРОИЗВ (С4:С10;$B4:$B10), которая означает, что столбец B4:B10 последовательно умножается на столбцы С4:С10, D4:D10 и т.д.
В ячейках, содержащих стоимость 1 единицы продукции, реализацию и общую сумму реализации зададим формат Денежный с двумя знаками после запятой. Для этого выделим, например, диапазон В4:В10 и выполним команду контекстного меню Формат ячеек – Число – Денежный и выставим количество десятичных знаков 2. Задать формат можно другим способом, выполнив команды Главная – Число – Число – Денежный и задать разрядность с помощью пиктограммы 
Для диапазона С16:I16 аналогичным образом зададим формат Процентный, ноль знаков после запятой.
Отформатируем таблицу по образцу (рис.10 – 11):
Рисунок 10 – Лист с формулами
Рисунок 11 – Отчет о реализации медикаментов
Построение диаграммы.
1) Построим гистограмму отражающую количество проданных медикаментов по наименованиям за каждый месяц текущего года.
Поставим курсор в ячейку под таблицей. Выполним команды Вставка – Диаграмма – Гистограмма.
Если область диаграммы перекрывает таблицу, то ее нужно сдвинуть, удерживая левую кнопку мыши на области диаграммы.
Обратите внимание, при выделенной области диаграммы активна вкладка Конструктор работы с диаграммами (рис.12). Теперь зададим числовые данные. Для этого выполним команду Выбрать данные (рис.12).
Рисунок 12 – Выбор данных для гистограммы
Выпавшее меню (рис.13) условно делится на три части: в верхней части указывается общий диапазон данных, входящий в диаграмму. В левой части задаются ряды значений (ось Y), в правой части задаются подписи по горизонтальной оси (ось X). В верхнее окно введем диапазон D4:I10. Нажмем кнопку Строка/столбец. Изменим название рядов данных, для этого в левом окне нажмем кнопку Изменить (рис. 13 – 15).
Рисунок 13 – Изменение подписи ряда
Рисунок 14 – Изменение ряда
Рисунок 15 ‑ Изменение подписи ряда
Аналогично введем название всех рядов (наименование медикаментов). Затем переходим к правой части данного меню. Команда Изменить вызовет появление меню для ввода подписей на оси категорий (рис. 16 –18):
Рисунок 16 – Переименование рядов
Рисунок 17 – Подписи оси
Рисунок 18 – Заполненные данные
В результате получим диаграмму:
Рисунок 19 – Полученная гистограмма
В правой части области диаграммы находится легенда. Осталось ввести название диаграммы. Перейдем во вкладку Макет – Подписи – Название диаграммы – Над диаграммой. В текстовой области введем название «Количество проданного товара» (рис.20).
Рисунок 20 – Графическое отображение исходных данных
Аналогичным образом можно построить другой тип диаграммы, выбрав команду на панели инструментв Вставка – Диаграмма.
Варианты индивидуальных заданий к лабораторной работе
«Создание и редактирование таблиц в, выполнение расчетов и построение диаграмм в MS Excel»
Задание:
1. Заполнить таблицу своего варианта (не менее 7 строк).
2. Имеющиеся в шапке таблицы данные (года, месяцы, дни недели) заносить с помощью автозаполнения.
3. Оформить таблицу с помощью обрамления, добавить заголовок, расположив его по центру таблицы. Шапку таблицы выполнить в цвете (шрифт и фон), полужирным шрифтом.
4. Выполнить соответствующие вычисления. Формулы заносить с помощью автозаполнения.
5. При построении диаграммы предусмотреть название, обозначение осей, выставить границы диаграммы, заливку области построения и диаграммы.
Вариант №1
| Название препарата | Стоимость препарата | Продано за | 4 квартал | |||
| Октябрь | Ноябрь | Декабрь | ||||
| Анаферон |
Найти:
1. Общее количество препарата каждого наименования проданного за квартал
2. Среднее количество препарата проданного за каждый месяц.
3. Сумму от продажи каждого наименования за квартал.
4. Построить круговую диаграмму роста продаж одного из медикаментов.
Вариант №2
| Наименование | Сумма реализации (текущая неделя) тыс.грн. | |||||
| аптеки | Понедельник | Вторник | Среда | Четверг | Пятница | |
| Магистр |
Найти:
1. Общую сумму реализации за каждый день текущей недели.
2. Минимальный объем реализации каждой аптеки за неделю.
3. Вклад (в %) аптеки «Магистр» в общую сумму реализации.
4. Построить гистограмму объема реализации каждой аптекой.
Вариант №3
| Наименования медикаментов | Количество проданных медикаментов | |||||
| Январь | Февраль | Март | Апрель | Май | Июнь | |
| Аспирин |
Найти:
1. Среднее количество проданных медикаментов за каждый месяц.
2. Сумму продажи всех медикаментов за полугодие.
3. Прирост продажи медикаментов в июне по сравнению с январем.
4. Построить гистограмму спроса на аспирин.
Вариант №4
| Заболевание | Стоимость 1 дня лечения | ||||||
| Грипп | 5.6 |
Найти:
1. Среднюю стоимость 1 дня лечения каждой болезни за 5 лет.
2. Общую стоимость лечения всех болезней по годам.
3. % изменения стоимости лечения в 2015 г. по сравнению с 2013 г.
4. Построить гистограмму роста стоимости лечения по заболеваниям.
Вариант №5
| Болезнь | Количество больных | |||||
| Январь | Февраль | Март | Апрель | Май | Июнь | |
| Грипп |
Найти:
1. Общее число больных за каждый месяц.
2. Среднее число больных за каждый месяц.
3. Процент больных гриппом в каждом месяце текущего года.
4. Построить гистограмму заболеваемости за полугодие.
Вариант №6
| Областной центр | Прирост населения в тыс. чел. | |||||||
| Днепр | -4 | -15 |
Найти:
1. Максимальный прирост населения за пятилетие по каждому городу.
2. Средний прирост населения за пятилетие по каждому городу.
3. Общий прирост населения по всем городам за каждый год.
4. Построить гистограмму прироста населения за каждый год.
Вариант №7
| Название шахты | Количество травмированных работников | ||||
| 1 кв. | 2 кв. | 3 кв. | 4 кв. | ||
| Глубокая |
Найти:
1. Общее число травмированных работников за каждый квартал.
2. Среднее число травмированных работников за год по каждой шахте.
3. Шахта, на которой количество травм было наибольшим (за год).
4. Построить гистограмму травматизма за каждый квартал.
Вариант №8
| ФИО провизора | Сумма продажи товаров, грн. | |||||
| Июль | Август | Сентябрь | Октябрь | Ноябрь | Декабрь | |
| Петров |
Найти:
1. Сумму продажи товаров всеми продавцами за каждый месяц.
2. Среднюю сумму продажи товаров каждым продавцом за полугодие.
3. Зарплату Петрова в декабре, если он получает 8% от продаж.
4. Построить круговую диаграмму продаж за декабрь каждым из продавцов.
Вариант №9
| Отделение | Количество койко-дней | |||||
| Январь | Февраль | Март | Апрель | Май | Июнь | |
| Хирургия |
Найти:
1. Общее количество койко-дней за каждый месяц.
2. Среднее количество койко-дней за полугодие в каждом отделении.
3. Отделение, принявшее максимальное кол-во больных за полугодие.
4. Построить гистограмму загруженности отделений.
Вариант №10
| Название препарата | Стоимость препарата | Продано за | 4 квартал | |||
| Октябрь | Ноябрь | Декабрь | ||||
| Анаферон |
Найти:
1. Общее количество препарата каждого наименования проданного за квартал
2. Среднее количество препарата проданного за каждый месяц.
3. Сумму от продажи каждого наименования за квартал.
4. Построить круговую диаграмму препарата анаферон, проданного за 4 квартал.
Вариант №11
| Наименование | Стоимость 1 ед. | Количество проданного медикамента за год | |||
| медикамента | медикамента | 1 кв. | 2 кв. | 3 кв. | 4 кв. |
| Анальгин | 10,20 |
Найти:
1. Общее количество проданного медикамента каждого наименования за год.
2. Среднее количество товара проданного за каждый квартал.
3. Сумму продажи каждого товара за текущий год.
4. Построить круговую диаграмму продаж медикаментов за 1 кв.
Вариант №12
| Заболевание | Стоимость 1 дня лечения | ||||||
| Грипп | 15.6 |
Найти:
1. Среднюю стоимость 1 дня лечения каждой болезни за 5 лет.
2. Общую стоимость лечения всех болезней по годам.
3. % изменения стоимости лечения в 2015 г. по сравнению с 2012 г.
4. Построить гистограмму роста стоимости лечения по заболеваниям.
Вариант №13
| Район | Население в 2014г | Родилось в 2015 г. | |||
| 1 кв. | 2 кв. | 3 кв. | 4 кв. | ||
| Центральный |
Найти:
1. Общее количество родившихся за каждый квартал.
2. Среднее количество родившихся по районам.
3. Прирост населения по районам в %.
4. Построить гистограмму роста рождаемости в 2015г. по районам.
Вариант №14
| Наименование | Сумма реализации (текущая неделя) тыс.грн. | |||||
| аптеки | Понедельник | Вторник | Среда | Четверг | Пятница | |
| Магистр |
Найти:
1. Общую сумму реализации за каждый день текущей недели.
2. Минимальный объем реализации каждой аптеки за неделю.
3. Вклад (в %) аптеки «Магистр» в общую сумму реализации.
4. Построить гистограмму объема реализации каждой аптекой.
Вариант №15
| Наименования медикаментов | Количество проданных медикаментов | |||||
| Январь | Февраль | Март | Апрель | Май | Июнь | |
| Аспирин |
Найти:
1. Среднее количество проданных медикаментов за каждый месяц.
2. Сумму продажи всех медикаментов за полугодие.
3. Прирост продажи медикаментов в июне по сравнению с январем.
4. Построить кольцевую диаграмму спроса на аспирин.