МІНІСТЕРСТВО ОСВІТИ І НАУКИ УКРАЇНИКиївський національний університет будівництва і архітектури

ЕЛЕКТРОННЫЕ ТАБЛИЦЫMICROSOFT EXCEL

Методические указания к выполнению практических работ для студентов,

які навчаються за напрямами 6.060101 «Будівництво», 6.030601 «Менеджмент», 6.030510 «Товарознавство і торговельне

підприємництво», 6.080101 «Геодезія, картографія та землеустрій», 6.060103 «Гідротехніка (водні ресурси)»

КИЇВ 2009

УДК 004:69ББК 73:38.1

Е50

Укладачі: Н.М. Яковенко, асистентО.О. Тихонова, асистент

Рецензент Г.Л. Васильєва, канд. фіз.-мат. наук, доцент

Відповідальний за випуск П.П. Лізунов, д-р техн. наук, професор

Затверджено на засіданні кафедри основ інформатики, протокол № 1 від 31серпня 2009 року.

Видається в авторській редакції.

Електронні таблиці Microsoft EXCEL: методичні вказівки доЕ50 виконання практичних робіт / уклад.: Н.М. Яковенко,

О.О. Тихонова. – К.: КНУБА, 2009. – 28 с.

Розглянуто використання засобів Microsoft E X C E L для створення та редагування електронних таблиць.

Призначено для студентів, які навчаються за напрямами 6.060101 «Будівництво», 6.030601 «Менеджмент», 6.030510 «Товарознавство і торговельне підприємництво», 6.080101 «Геодезія, картографія та землеустрій», 6.060103 «Гідротехніка (водні ресурси)».

© КНУБА, 2009

ЗМІСТЗагальні положення . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4Практична робота №1. Основи роботи з MS Excel . . . . . . . . . . . . . . . . . . 5

Вивчення інтерфейсу Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5Засоби роботи з листами в MS Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5Переміщення по таблиці . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5Основи виділення фрагментів таблиці . . . . . . . . . . . . . . . . . . . . . . . . . . 6Створення рядів даних . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7Введення формул . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7

Практична робота №2. Форматування таблиць . . . . . . . . . . . . . . . . . . . 8Побудова таблиць в Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8Переміщення й копіювання інформації . . . . . . . . . . . . . . . . . . . . . . . . . 8Використання відносної та абсолютної адресації . . . . . . . . . . . . . . . . . 8Форматування таблиць . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . . . 9Автоматичне форматування . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

Практична робота №3. Вставка функцій . . . . . . . . . . . . . . . . . . . . . . . . . 11Вставка математичних функцій . . . . . . . . .. . . . . . . . . . . . . . . . . . . . . . . 11Статистичні функції . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12Використання логічних функцій . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12Застосування формули масиву . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

Практична робота №4. Створення, редагування і форматування діаграмПобудова лінійчатої діаграми . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14Побудова гістограми . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . .. 15Побудова кругової, кільцевої та точкової діаграми . . . . . . . . . . . . . . . 16Побудова графіків . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

Практична робота №5. Робота з базами даних у середовищі MS EXCELСтворення, сортування та фільтрація списків . . . . . . . . . . . . . . . . .. . . 18Підведення підсумків . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .. . . . . . . . . . . 19

Практична робота №6. Аналіз даних в таблицях Excel. Підготовка електронних таблиць до друку . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

Консолідація даних . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20Метод „Подбор параметра” . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22Зведені таблиці . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ...23Підготовка електронних таблиць до друку . . . . . . . . . . . . . . . . . . . . . . . 24Підготовка до друку діаграм та графіків . . . . . . . . . . . . . . . . . . . . . . . . 25Питання для самоконтролю . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26Список літератури . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27

3

Загальні положення

У даних методичних вказівках розглянуто роботу з найпоширенішою програмою електронних таблиць MS Excel.

Мета виконання практичних робіт – опанування засобів організації розрахунків та аналізу ділових даних, створення діаграм, придбання навичок роботи в MS Excel.

Дані методичні вказівки охоплюють основні аспекти практичного використання засобів MS Excel. До кожної практичної роботи подаються практичні завдання для виконання на комп’ютері. Самостійне опрацювання завдань студентами сприятиме ефективному засвоєнню теоретичного матеріалу і формуванню навичок кваліфікованого користування комп’ютером. Результат виконання роботи зберігається у вигляді файлу на диску.

Електронні таблиці MS Excel – це зручний засіб, за допомогою якого можна виконувати різноманітні розрахунки: економічні, математичні. Excel є самою простою в експлуатації і в той же час самою потужною системою електронних таблиць.

Щоб прискорити процес розрахунків, електронні таблиці забезпечені різноманітними вбудованими функціями, які поділяються на категорії: математичні, статистичні, фінансові, дати і часу, функції роботи з базами даних, логічні тощо.

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

Програма надає додаткові можливості щодо аналізу інформації: створення баз даних, зведених таблиць, консолідації, статистичної обробки тощо. Інформація, що сформована у вигляді баз даних, може оброблятися за допомогою спеціальних функцій та інших засобів. Це сортування, корегування та відображення даних, фільтрація інформації тощо.

Бази даних можна впорядкувати за допомогою групування записів з виведенням проміжних підсумків, середніх значень.

Оскільки програма MS Excel є складовою частиною пакета Microsoft Office, цей засіб має спільні риси з іншими додатками цього пакету.

4

Практическая работа №1

Основы роботы в MS Excel

Цель работы:- научиться работать с рабочими книгами;- освоить основные приемы работы с таблицами;- овладеть средствами создания рядов данных;- научиться использовать формулы.

Строение рабочей книги MS Excel

Внимательно рассмотрите окно документа рабочей книги MS Excel.

Методы работы с рабочими листами в MS Excel

1. Сделайте текущим лист Лист3. Вернитесь к листу Лист1. Вставьте несколько листов в книгу.

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

3. Установить количество листов -6, остальные удалите.

4. Переместите лист Лист 3 перед листом Лист1.

5. Переименуйте лист Лист 1 в Практическая №1.

6. Выделите одновременно листы Практическая №1 и Лист 2. Занесите в ячейку А2 листа Практическая №1 слово Выполнил, в ячейку А3 – Группа, в ячейку В2 – свое имя, в ячейку В3 – номер своей группы.

5

Снимите выделение с выделенных листов и просмотрите их содержимое. Убедитесь, что информация заносится одновременно во все выделенные листы.

7. Скопируйте Лист3 в конец книги.

8. Переместите Лист 2 за Лист3.

7. Скройте Лист2 и Лист3. Восстановите скрытые листы.

Перемещение по таблице

1. Выделите ячейку D4 с помощью мыши или клавиш управления курсором.

2. Перейдите от системы адресации ячеек А1 к системе адресации С1R1 и наоборот (Кнопка Office Параметры Excel ФормулыРабота с формулами Стиль ссылок R1C1).

3. Используя клавиши управления курсором, перейдите к:

- последней ячейке строки 5 (Ctrl+→)

- последней ячейке столбца D (Ctrl+↓);

- последней ячейке рабочего листа (Ctrl+→);

Обратите внимание на общее количество строк и столбцов в таблице.

Перейдите к первой ячейке рабочего листа (Ctrl+Home).

Основы выделения фрагментов таблицы

1. Выполните выделение таких фрагментов таблицы:

- строку 3,

- столбец D,

- диапазон ячеек А1:Е5,

- диапазон ячеек А10:Е20 и А30:D40,

- все ячейки рабочего листа.

Снимите выделение.

2. Измените ширину столбца А с помощью мыши. Аналогично измените высоту строки 3. Выполните эти же действия с помощью команды меню Главная Панель Ячейки Размер ячейки. Для диапазона ячеек таблицы А2:В3 выберите из меню Главная Панель

6

Ячейка Формат Размер ячейки Автоподбор ширины. Обратите внимание на то, что ширина столбцов изменилась в соответствии с размером данных.

3. Восстановите стандартное значение высоты строки 3.

4. Отредактируйте текст в ячейке В2 – добавьте свою фамилию.

Вставка и удаление ячеек таблицы

1. Выполните вставку таких фрагментов таблицы:- одну ячейку перед ячейкой В3;- одну строку перед строкой 2;- три строки перед строкой 2,- один столбец перед столбцом А;2. Удалите столбец, несколько столбцов, строку, несколько строк

таблицы с помощью команды меню Главная Панель Ячейки Вставить или контекстного меню.

Создание рядов данных

1. В ячейку А7 введите число 1, в ячейку А8 – число 2. Заполните ячейки А9:А17 числами с шагом 1, используя маркер Автозаполнения.

2. Создайте на рабочем листе методом автозаполнения ряды из 10 элементов:

- простой числовой ряд с шагом 7,- геометрическую прогрессию с шагом 3,- ряд дат, начиная с текущей, ряд дат с шагом -7,- ряд дат рабочих дней месяца,- года, начиная с текущего.

3. заполните ячейки названиями месяцев года и создайте свой личный список для автозаполнения ячеек.

Ввод формул

1. Ввод формулы начинается с набора знака “=”. В ячейки D2 та D3 введите такие формулы: =105*5+368 на 105^3-300 ( знак “^” – возведение в степень набирается на английском драйвере комбинацией Ctrl+6). Заканчивается ввод формулы нажатием на клавишу Enter ! Обратите внимание на результат в ячейках D2 и D3 и проверьте их содержимое в строке формул.

7

2. В ячейки E2 и E3 введите формулу, которая содержит ссылку на адреса ячеек: =(D2+D3)*5 та =(D2+D3)/(D2-D3) соответственно. При вводе в формулу адресов ячеек можно щелкнуть мышью по нужной ячейке, чтобы появилась ссылка на ячейку в формуле. Проверьте результат в ячейках E2 та E3.

3. Вычисление в таблице производятся автоматически. Режим Автоматически можно отключить. (Сервис → Параметры вкладка

Вычисления. Если в результате ввода формулы Вы получили , то это означает, что результат не вмещается в ячейку, столбец надо расширить.

4. Обратите внимание на то, что формулы в Excel вводятся в виде

линейной записи, то есть формула D 2+D 3D2−D3 имеет вид =(D2+D3)/(D2-D3).

Сохраните файл в папку Вашей группы.

Практическая работа №2Форматирование таблиц

Цель работы:

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

Построение таблиц в Excel

1. Загрузите Excel. Откройте файл с практической роботой №1. Переименуйте рабочий лист Лист 2 в Практическая № 2.

2. Постройте таблицу согласно образцу (рис. 1), начиная с ячейки A5.

Рис. 1. Затраты на комплектующие

8

Посчитайте значения в столбце Всего. Введите формулу для вычисления цен в ячейку D7: =B7*С7. Скопируйте эту формулу в ячейки D8:D12 при помощи мыши или буфера обмена. Для вычисления

показателя Всего используйте кнопку Автосумма .3. На листе Практическая №2 зафиксируйте столбец Наименование

и первую строку таблицы (команды меню: Окно Разделить, Окно Закрепить), проверьте работу режима фиксации. Отмените режим фиксации.

Перемещение и копирование информации

Скопируйте данные из блока ячеек A5:D13 в ячейку K5, используя буфер обмела, «гарячие клавиши», технологию Drag and Drop, контекстное меню, правую кнопку мши. Переместите эти данные, используя все способы. Отмените последние действия (Ctrl+Z).

Использование относительной и абсолютной адресации

1. В таблицу Затраты на комплектующие вставьте столбец – Цена, в грн (рис. 2).

2. Введите в ячейку D4 текст – Текущий курс долара, в Е4 –значение текущего курса долара, например 24,7. Цена в гривнах будет расчитываться как Цена в $, умноженная на текущий курс долара. В формуле ссылка на ячейку Е4 заносится как абсолютная, тоесть $E$4. Во время копирования формулы абсолютная ссылка на ячейки не изменяется. Введите в ячейку С7 формулу: =В7*$E$4.

Рис. 2. Использование абсолютной адресации

9

3. Скопируйте формулу из ячейки С7 в ячейку С8:С12. Проверьте по строке формул, что ссылка $E$4 остается неизменной

Форматирование таблиц

1. Выбор шрифта

1.1. На закладке Главная в панели инструментов Шрифт виберите тип шрифта в списке шрифтов и измените розмеір шрифта.

1.2. Установите для заголовка таблицы Затрати на комплектующие начертание шрифта – полужирный, для названий столбцов – полужирный курсив, розмер – 12 пт.

2. Форматирование ячеек

2.1. Выделите нужные ячейки, вызовите команду Формат ячейки из контекстного меню или Главная панель ЯчейкиФормат ячеек…, закладка Число и внимательно просмотрите список числових форматов диалогового окна Формат ячеек: Числовой, Денежный, Финансовый, Дата и т.д.

2.2. Для данных стобца «Цена, в $» установите формат – Денежный, $ Английский США, для данных столбца «Цена, в грн.» формат грн.

2.3. Введите в ячейку С2 текущую дату, установите для этой ячейки формат даты в формате: 10.11.15.

2.4. Для данных столбца «Количество» установите формат Числовой целый.

2.5. Выделите числовые данные в столбце С и нажмите кнопку

Увеличить разрядность на панели инструментов Число. Уменьшите разрядность чисел в этих ячейках с помощью кнопки Уменьшить

разрядность . Ограничьтесь двумя цифрами после десятичной точки.

3. Выравнивание тектста

3.1. Для выравнивания текста вдоль выделенных ячеек используйте

кнопку Объединить и поместить по центру на панели Выравнивание. Для отображения текста в ячейке в несколько строк

10

используйте кнопку перенос текста на панели Выравнивание.

3.2. Используйте команду Автоподбор ширины ЯчейкиРазмер ячейки для уменьшения шрифта в таблице. Отмените последнее действие (команду).

3.3. Выделите первый столбец таблицы (Рис.2) и в контекстном меню выберите команду Формат ячеек…, в диалоговом окне Формат яччек откройте вкладку Выравнивание, включите фляжок Переносить по словам.

4. Создание рамок

4.1. Границы ячеек таблиц можно обводить различными линиями а внутренность ячеек заливать различными цветами. Выделите ячейки таблицы Затраты на комплектующие и выполните команды Формат ячеек…, вкладка Граница. В поле Тип линии выберите тип линии рамки, например Двойная, в списке Цвет выберите цвет линии.

4.2. Обведите таблицу снаружи двойной линией, внутренние линии ячеек обведите тонкой линей (Рис.3).

4.3. Выделите ячейки заголовка таблицы с помощью цвета и

рисунка фона, используя кнопки Цвет заливки и Цвет шрифта

.

11

(Рис.3)

5. Автоматическое форматирование

5.1. Используя на панели Стили кнопки выберите понравившийся формат таблицы и ячеек.

Практическая работа №3

Вставка функций

Цель работы:

- научиться применять в расчетах математическе функции;- ознайомиться с статистическими и логическими функциями;- научиться использовать формулы массива.

Вставка математических функций

1. Откройте файл с практическими работами по Excel, переименуйте Лист 3 в Практическая №3.

12

2. Выделите ячейку и нажмите кнопку f(x) в строке формул, расмотрите окно Мастер функций. В списке Категория выберите категорию, например, Математические. Просмотрите список математических функций. Выберите функцию Sin(x) и задайте аргумент функции.

3. Для аргумента x= -1, 0, 2 найдите значения функций, представленных на рис. 4, при помощи Мастера функций, используя функции SIN(), TAN(), КОРЕНЬ(), СТЕПЕНЬ(), EXP(), ABS(), ПИ(), ГРАД(), РАДИАН() и другие.

Рис. 4. Использование математических функций

Статистические функции

1. В окне Мастера функций выберите категорию Статистические и внимательно просмотрите функции: MAKC(), MИН(), СРЗНАЧ() и другие.

2. При помощи функций МАКС(), МИН() и СРЗНАЧ() исследуйте показатель Прибыль, данные котрого представлены в табл. 1.

13

Таблица 1Исследование показателя Прибыль

Год 2005 2006 2007 2008

Прибыль,тыс.грн. 146 168 190 150

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

Минимальное значение

Середнее значение

Использование логических функций

1. Логические функции используются в случае принятия решений, сопровождающеесяусловием. Например, найти функцию Y:

Y=

X , X >=5X 2̂+5 , X<5

2. Решение этого примера легко найти с помощью функции ЕСЛИ() – рис. 5. В ячейку G6 надо ввести формулу: ЕСЛИ(F6>=5; F6;F6^2+5).

Рис. 5. Использование функции ЕСЛИ()

Использлвание формулы массива

1. Для вычисления значений в нескольких ячейках по одной формуле используется формула массива. Чтобы ввести формулу массива надо виделить ячейки, в которые будут выводиться результаты вычислений, ввести формулу и нажать комбінацію клавіш <Ctrl> + <Shift> + <Enter>.

2. Выделите ячейки I22:I26 и введите формулу =G22:G26*С (рис. 6). Для ввода блока ячеек G22:G26 мышью выделите этот блок потом введите знак умноження, мышью выделите блок G22:G26, нажмите комбинацию клавиш <Ctrl> + <Shift> + <Enter>.

14

Рис. 6. Использование формулы массива

3. Часто формулы массива используют для роботы с матрицами. Для изменеия формулы массива надо выделить весь диапазон ячеек с формулой и изменить формулу для всех ячеек. Изменять содержимое ячейки внутри блока с формулой массива нельзя.

4. Используйте кнопку Формат по образцу для копирования формата таблицы с практической работой 2 в другие таблицы.

Практична робота №4

Создание, редактирование и форматирование диаграмм

Цель работы:

- познакомиться с основными понятиями Мастера диаграмм;- изучить технологии создания диаграмм и графиков;- овладеть способами редактирования и форматирования диаграмм и

графиов;- научиться строить графики математических функций.

Построение линейчатой диаграммы

1. Переименуйте Лист 4 в Практическаяа работа № 4.2. Создайте табл. 2 Объемы производства кирпича.

Таблица 2Данные для создания линейчатой диаграммы

Объемы производства кирпича

Наименование изделияГоды

2012201

3 2014 2015Облицовочный красный кирпич 155 158 160 175Строительный пустотелый кирпич 135 154 179 190

15

Силикатный кирпич 141 152 169 167Виделите диапазон данных для построения диаграммы (строки, содержащие названия изделия и соответствующие им данные по годам). На закладке Вставка в панели Диаграммы нажмите па кнопку Линейчатая и выберите первый вид диаграммы из предложенных вариантов

. Рис. 7

При этом названия рядов данных отображаются в легенде. Для отображения названия категорий надо выделить ось кадегорий и в контекстном меню выбрать команду Выбрать данные…. В окне диалога

Рис.8Выбор источника даннях выделить первую строку в поле Подписи горизонтальной оси (категорий) и нажать кнопку Изменить. Затем в

16

Таблице 2 выделить ячейки с годами. Номера категорий будут заменены на значения в соответствующих ячейках.

Рис.9

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

4. Для добавления подписей даннях надо на закладке Макет нажать кнопку Подписи даннях на панели Подписи.

5. Для удаления линий сетки на вкладке Макет в панели Оси используйте кнопку Сетка.

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

1. Создайте табл. 3 Основные показатели деяльности предприятия.

Таблица 3Данные для построения гистограммы

Основные показатели деяльности предприятия по годам, тыс. грн

Показатели 2005 2006 2007Основные средства 926,1 796,3 1234,6Часный капитал 804,3 747,1 913,4Денежные средства 4,2 3,5 14,5Оборотные активы 43,0 177,4 230,0

17

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

3. Створіть гістограму об'єма власного капіталу по роках. Додайте до діаграми лінію тренду. Виведіть формулу на діаграму. Визначте правильність вибору типу лінії тренду. Порівняйте отриману формулу з формулою для попередньої гістограми.

4. Змініть тип діаграми на „цилиндрическую со столбцами в виде цилиндров”.

Побудова кругової, кільцевої та точкової діаграми

1. Створіть табл. 4. Для рядка Укрбудмет побудуйте об'ємну кругову діаграму. Змініть колір секторів діаграми. Додайте до діаграми підписи даних у вигляді відсотка (частки). Розташуйте підписи даних поверх відповідних секторів. Створіть фон для підписів даних.

2. Змініть розмір, колір та тип шрифту заголовка діаграми. Збільшіть розміри діаграми. Змініть тип лінії обрамлення заголовку діаграми та її колір.

3. Виберіть зелений колір заливки області побудови діаграми. Розмістіть легенду справа від діаграми. Виріжте найбільший сектор діаграми та перемістіть його.

4. Змініть тип діаграми на розрізану. Порівняйте діаграмиТаблиця 4

Дані для створення різних типів діаграм

Ціна бетону М100 П3 по місяцях, грн.Постачальник Жовтень Листопад Грудень СіченьУкрбудмет 466,1 454,3 431,6 412,6Завод будматеріалів 464,3 447,1 413,4 400,0ВЕНТАсервіс 461,7 445,5 414,5 404,8Бетонбудторг 480,0 471,4 465,2 439,0

18

5. Для даних з табл. 4 Ціна бетону М100 П3 побудуйте кільцеву діаграму.

6. Змініть розмір діаграми. Відформатуйте область побудови діаграми: змініть колір та спосіб заливки на двокольорову з вертикальною штриховкою гранітної текстури бірюзового кольору, товщину та вид рамки на штрихпунктирну з тінню, діаметр отвору – 65, колір легенди –жовтий.

7. Перемістіть легенду в правий кут діаграми. Розріжте діаграму.8. Перенесіть діаграму на інший лист.9. Для третього рядку даних (ВЕНТАсервіс) з табл. 4 Ціна

бетону М100 П3 побудуйте „точечную” діаграму №2. Додайте значення даних на діаграму.

10. Змініть колір лінії на червоний, виберіть тип маркерів – округлої форми зеленого кольору. Розмістіть діаграму на іншому листі. Збільшіть її розмір. Змініть заливку області побудови діаграми на жовтий, область діаграми - зелений, колір рамки – червоний.

11. Для рядка Укрбудмет таблиці 4 виберіть тип „нестандартная голубая круговая”. Використовуючи дані табл. 3 Основні показники діяльності підприємства, побудуйте „нестандартную трубчатую” діаграму. Змініть тип діаграми на „логарифмическую”.

Побудова графіків

1. Виділіть діапазон даних Оборотні активи в таблиці Основні показники діяльності підприємства. В опції тип виберіть тип діаграми – графік, вид – графік з маркерами.

2. У полі заголовка введіть назву графіка, по осі абсцис відкладіть значення аргументу – „роки”, а по осі ординат – значення оборотних активів у грн.

3. Встановіть перетин осі значень з віссю категорій. Вирівняйте надписи осей. Змініть колір та текстуру заливки області побудови графіка. Виберіть інший вид та колір рамки. Змініть розміри графіка.

4. Створіть таблицю квадратів, кубів і логарифмів з основою 2 (табл. 5). Побудуйте графіки математичних функцій в одній системі координат, використавши різні види графіків.

5. Збільшіть діапазон значень аргумента до 10, проаналізуйте зміну значень функцій.

19

6. Змініть формат заголовка першого графіка, виберіть шрифт Arial, розмір 16 з тінню. Змініть колір ліній на червоні. Зробіть різнокольорові заливки полотна графіків, використайте різні узори та текстури. Виберіть інший вид та колір маркерів.

Таблиця 5Дані для побудови графіків

Таблиця квадратів, кубів і логарифмів з основою 2

х У = х^2 У = х^3 У = Log2(х)1 1 1 02 4 8 13 9 27 1,5854 16 64 25 25 125 2,322

Практична робота №5

Робота з базами даних у середовищі MS EXCEL

Мета роботи:- вивчити створення, сортування та фільтрацію списків;- ознайомитись з підведенням підсумків за допомогою розширеного

фільтра.

Створення, сортування та фільтрація списків

1. Дайте листу 5 назву Сортування. 2. Створіть таблицю Успішність групи ТВ-13 (рис. 6). В клітинках

А4:А8 створіть список студентів (команда Сервис → Параметры → Закладка Списки → Новый список, або Данные → Список).

3. Відсортуйте дані в діапазоні В4:В8 за збільшенням, а потім за зменшенням значень, команда Данные → Сортировка.

А В С D E1 Успішність групи ТВ-132 П.І.Б. Екзаменаційні оцінки Середній

20

бал3 Інформатика Історія Хімія4 Кучер М.І. 5 5 45 Малиш Н.О. 5 3 46 Сулема Д.В. 4 4 27 Телешко К.Р. 3 2 28 Михась О.Т. 3 4 39 Кількість студентів з середнім балом вище 4,5

Рис. 6. Створення та сортування списків

4. Обчисліть середній бал за навчання кожного студента в групі. Значення клітинок Е4:Е8 відсортуйте за зменшенням значень. Виділіть клітинку з найбільшим середнім балом, використовуючи команду Фильтр вікно Наложение условия по списку. В пункті меню Данные виберіть Фильтр → Автофильтр. Відфільтруйте значення за зменшенням. За допомогою команди Условие та вікна конструктора умов виділіть клітинки з середнім балом, який більше 4,5. Виберіть зі списку прізвища студентів з оцінками „4” і „5” (застосувавши команду Расширенный фильтр). Створіть поля для діапазону умов та для вводу результату.

Підведення підсумків

1. Додайте лист 6 з назвою завдання: Підведення підсумків. Створіть табл. 6: Показники економічної діяльності підприємства.Визначте проміжні підсумки: суму балансової вартості, залишкової вартості та відновної повної вартості кожного цеху та складу, а також загальний підсумок. Для цього потрібно:

Встановити курсор в будь-який елемент таблиці з даними. Вибрати пункт меню Данные → Итоги. У вікні При каждом

изменении вибрати «Найменування об'єкта». У вікні Операция вибрати Сумма. У вікні Добавить итоги по вибрати Сумма (перевірити, щоб в решті

полів прапорці були відсутні). Включити прапорець Итоги под данными. Якщо прапорець цього параметра встановлений, то підсумки розміщуються під даними, якщо знятий – над ними.

Натиснути ОК, зняти виділення з таблиці і проглянути результат на екрані.

Таблиця 6

21

Показники для підведення підсумків

Показники економічної діяльності підприємства, тис. грнНайменування об'єкта

Балансова вартість

Залишкова вартість

Відновна повна вартість

Склад №1 670,50 359,30 1711,50

Склад №3 554,90 476,90 1694,70

Цех №1 730,20 539,90 2130,60

Цех №2 865,60 574,60 2413,80

Цех №3 951,00 792,80 2799,00

2. Додайте лист 7 з назвою Підсумки. Створіть табл. 7 «Накладні витрати на утримання автівок». Обчисліть значення підсумків по першому стовпчику, команда Данные → Итоги. Створіть рядки підсумків для вантажних та легкових автівок. Знайдіть середнє та мінімальне значення пробігу за місяць для вантажних та легкових автівок.

Таблиця 7Дані для підведення підсумків

Накладні витрати на утримання автівок

Автомобіль

Фіксовані витратив місяць,

грн

Іншівитрати,

грн

Витратина 1 км пробігу,

грн

Пробігза місяць

в км

Всього,грн

Вантажівка 1 500 50 2 2000

Вантажівка 2 400 45 2 1800

Легковик 1 350 70 1 1500

Легковик 2 370 20 0,50 2500

Всього, грн

22

Практична робота №6

Аналіз даних в таблицях Excel. Підготовка електронних таблиць до друку

Мета роботи: - ознайомитись з процесом керування даними;- навчитись використовувати консолідацію даних, зведені таблиці

та метод „Подбор параметра”;- освоїти перевірку орфографії та підготовку робочих листів до

друку.

Консолідація даних

Консолідація – це об’єднання даних з різних діапазонів.

1. Додайте Лист 8 з назвою Консолідація.

2. В клітинках A1:D13 розмістіть таблицю з початковими даними для консолідації (рис. 7). Зверніть увагу: для правильного проведення консолідації необхідно дотримуватись форматування таблиці (зображення, шрифти, межі).

A B C D1 Постачальник 12 Товар Ціна в січні, грн Ціна в

лютому, грнЦіна в

березні, грн3 Двері сосна 890 900 9204 Двері дуб 1010 1040 10555 Двері ДСП 300 305 3156 Постачальник 27 Двері сосна 870 870 8958 Двері дуб 1015 1015 10359 Двері ДСП 295 290 31010 Постачальник 311 Двері сосна 900 915 94012 Двері дуб 1020 1035 105013 Двері ДСП 290 300 310

Рис. 7. Дані для консолідації

23

3. За допомогою клавіші Ctrl виділіть діапазони клітинок: B3:D5, B7:D9, B11:D13. В пункті меню Формат виберіть команду Ячейки. На вкладці Число виберіть формат Денежный, позначення – грн. Натисніть ОК.

4. Залийте рядки 1, 6, 10 блідо-зеленим кольором. Об'єднайте клітинки A15:D15. Залийте жовтим кольором. Введіть текст: Результати консолідації. Мінімальне значення. Змініть зображення на полужирний.

5. Клацніть клітинку А16. В пункті меню Данные виберіть Консолидация. В діалоговому вікні Консолидация, що з'явилося, в полі Функция виберіть Минимум. Клацніть в полі Ссылка. Виділіть діапазон клітинок A3:D5. Натисніть кнопку Добавить.

6. Поверніться до поля Ссылка, очистіть його, виділіть діапазон клітинок A7:D9. Натисніть кнопку Добавить. Аналогічно додайте значення по постачальнику, що залишився – діапазон A11:D13.

7. В полі Использовать в качестве имени поставте прапорець Значения левого столбца. Натисніть кнопку ОК.

8. Виділіть отриману таблицю (клітинки A16:D18). В пункті меню Формат виберіть команду Ячейки. На вкладці Граница виберіть тип ліній – жирна, виберіть лінії – зовнішні, колір – червоний. Натисніть кнопку ОК.

9. Клацніть клітинку А20. Аналогічно отримайте таблицю: Результати консолідації. Середнє значення. Для цього в діалоговому вікні Консолідация змініть тільки поле Функция. В ньому виберіть Среднее. Отриману таблицю відформатуйте так само, як і попередню.

Метод „Подбор параметра”

Метод «подбор параметра» використовується у тому випадку, коли потрібно отримати певне значення формули. Для того, щоб вирішити цю задачу, вказують одну з клітинок, що впливає на значення формули. Excel підбирає для вмісту клітинки таке значення, при якому у формулі буде отриманий необхідний результат.

Завдання 1

Додайте лист 9 з назвою Подбор параметра. В клітинку А1 введіть назву завдання:

Вивчення можливостей методу ” Подбор параметра ”.

24

Знайдіть значення невідомого для формули Х + 3 = 5 за допомогою Сервис Подбор параметра. Для цього встановіть в клітинці В5 значення 5, змінюючи клітинку В3 (рис.8). Обчислений результат з’явиться в клітинці В3.

А В С D E1 Вивчення можливостей методу „Подбор параметра”

Завдання 12 Х + 3 = 53 Результат підбору4 Впливаюча клітинка 35 Підсумкова формула = В3 + В4

Рис. 8. Завдання 1

Завдання 2

Знайдіть корінь рівняння Х2 + 2Х – 5 = 0. Встановіть точність знаходження коренів. За методом Подбор параметра корінь знаходиться засобом послідовних наближень. Для цього за допомогою команди Сервис Параметры, на вкладці Вычисления задайте относительную погрешность(точність обчислень) рівною 0,001 та число итераций (кроків підбору) рівним 100. Після отримання результату змініть относительную погрешность та число итераций на 0,0001 і 1000 відповідно. Порівняйте результати (рис.9).

А В

6 Вивчення можливостей методу „Подбор параметра”Завдання 2

7 Х2 + 2Х – 5 = 08 Результат підбору9 Впливаюча клітинка 510 Підсумкова формула =В3^2 + 2*В3 - B4

Рис. 9. Завдання 2

Завдання 3

Знайдіть об’єм продажу керамічної плитки в жовтні, якщо відомо, що в серпні було продано 43 тис. штук, у вересні об’єм продажу становив

25

75% від продажу в серпні, а за три місяці було продано 104 тис. штук керамічної плитки.

Завдання 4

Складіть задачу з використанням методу „Подбор параметра”.

Зведені таблиці

Зведена таблиця – це засіб для впорядковування інформації. При створенні зведеної таблиці користувач розподіляє інформацію, вказуючи, які елементи і в яких полях будуть розміщені.

1. Додайте лист 11 з назвою Зведена таблиця. Створіть початкову таблицю для створення зведеної таблиці (рис. 10)

2. Необхідно створити зведену таблицю з підведенням підсумків з продажу товарів магазинами за видами продукції і видами обсягів продажу. Встановіть курсор в клітинку А18 з даними початкової таблиці. Виберіть пункт меню Данные Сводная таблица. В групі Создать таблицу на основе данных, находящихся встановіть перемикач в Списке или базе данных Microsoft Excel. В групі Вид створюваного звіту встановіть перемикач Сводная таблица і натисніть кнопку Далее.

3. Перевірте, що у вікні, що з'явилося, в опції Диапазон вказаний весь діапазон елементів початкової таблиці A1:D11, і натисніть Далее (при виділенні діапазону спочатку виділіть перший стовпчик, а потім протягніть вправо).

4. В опції Поместить таблицу в встановіть перемикач в новый лист і натисніть кнопку Макет.

A B C D1 Звіт про продаж продукції в першому кварталі2 Вид продукції Найменування

магазинуЩоденний обсяг продажу, тис. грн.

Місячний обсяг продажу, тис. грн.

3 Паркет бук Будмат 145 43504 Паркет бук Будівництво 100 30005 Паркет бук Інтер’єр 160 48006 Паркет клен Будмат 175 52657 Паркет клен Будівництво 155 46508 Паркет клен Інтер’єр 170 5100

26

9 Паркет дуб Будмат 153 459010 Паркет дуб Будівництво 132 396011 Паркет дуб Інтер’єр 120 3600

Рис. 10. Звіт для зведеної таблиці

5. За допомогою миші перетягніть поле «Найменування магазину» в область Страница, поле «Вид продукції» – в область Строка, поле «Щоденний обсяг продажу, тис. грн.» – в область Столбец, поле «Місячний обсяг продажу, тис. грн» – в область Данные і натисніть ОК. Натисніть кнопку Готово.

Підготовка електронних таблиць до друку

1. Підготуйте до друку таблиці з практичної роботи №5. Встановіть масштаб 100%.

2. Встановіть параметри сторінки при розташуванні на одному листі: книжна орієнтація; горизонтальне і вертикальне центрування, задайте поля: верхнє – 2,5, нижнє – 2,5, ліве – 2, праве – 2, поля верхнього та нижнього колонтитулів – 1,3.

3. Виберіть розмір аркуша А4.4. Створіть для даної таблиці колонтитули: у верхньому

колонтитулі введіть прізвище та ім’я студента, номер групи, дату виконання роботи, в нижньому колонтитулі – номер сторінки.

5. Перевірте орфографію за допомогою команди Автозамена або Сервис → Орфография.

6. Прогляньте таблицю в режимі «Предварительный просмотр».

7. Встановіть такі параметри друку:– встановіть сітку;– виберіть чорно-білий друк документа;– виведіть на друк заголовки рядків та стовпців;

– виберіть режим виводу на друк – вниз, потім вправо.

Підготовка до друку діаграм та графіків

1. Підготуйте до друку діаграми та графіки з практичної роботи №4.

27

2. Встановіть масштаб 25%, для того, щоб всі графіки та діаграми розмістились на одному аркуші, задайте поля: верхнє – 2, нижнє – 2, ліве – 2, праве – 2, поля верхнього та нижнього колонтитулів -1,3.

3. Орієнтація – альбомна, горизонтальне і вертикальне центрування.

4. Виберіть розмір аркуша А4.5. Створіть для цього аркуша колонтитули: в нижньому

колонтитулі введіть прізвище та ім’я студента, номер групи, дату виконання роботи, у верхньому колонтитулі – „Діаграми та графіки”.

6. Перегляньте практичну роботу в режимі «Предварительный просмотр».

7. Встановіть такі параметри друку: виведіть на друк заголовки рядків та стовпців; виберіть режим виводу на друк – вниз, потім вправо; виберіть чорновий друк документу; встановіть якість друку – 300 точок на дюйм.

28

Список літератури

29

1. Інформатика. Комп'ютерна техніка /під ред. Баженова В.А. – К.: Каравела, 2003. – 464 с.

2. Баженов В.А. Інформатика. Комп'ютерна техніка. Комп'ютерні технології: підручник / В.А. Баженов, П.С. Венгерський, В.М. Горлач та ін. – К.: Каравела, 2004. – 464 с.

3. Информатика. Базовый курс / под ред. С.В. Симоновича. – СПб., 1998.

4. Джим Бойс. Использование Мiсгоsoft Оffсе 97. Профессиональный выпуск. – К.; М.; СПб., 1998. – 1119 с.

5. Інформатика: Комп'ютерна техніка. Комп'ютерні технології /за ред. О.І. Пушкаря. – К.: Видавничий центр «Академія», 2001. – 696 с.

6. Інформатика: Комп'ютерна техніка Комп'ютерні технології: підручник для студентів вищих навч. закладів / за ред. О.І. Пушкаря. – К.: Видавничий центр «Академія», 2003. – 704 с.

7. Глушаков С.В. MICROSOFT EXCEL XP: навчальний посібник / С.В. Глушаков., О.С. Сурядний. – Харків: Фоліо, 2006. – 508 с.

8. Зелінський С.Е. MICROSOFT OFFICE 2007: навчальний посібник / С.Е. Зелінський. – Харків: Фоліо, 2008. – 506 с.

30

Навчально-методичне видання

ЕЛЕКТРОННІ ТАБЛИЦІ MS E X C E L

Методичні вказівки до виконання практичних робіт для студентів,

які навчаються за напрямами 6.060101 «Будівництво», 6.030601 «Менеджмент», 6.030510 «Товарознавство і торговельне

підприємництво», 6.080101 «Геодезія, картографія та землеустрій», 6.060103 «Гідротехніка (водні ресурси)»

Укладачі: Яковенко Наталія МиколаївнаТихонова Ольга Олексіївна

Комп’ютерне верстання Т.І. Кукарєвої

Підписано до друку 2009. Формат 60 84 1/ 16

Ум. друк. арк. 1,63. Обл.-вид. арк. 1,75. Тираж 100 прим. Вид. № 88/ІІІ-09. Зам. №

КНУБА, Повітрофлотський проспект, 31, Київ, Україна, 03680

E-mail: red-isdat@knuba.edu.ua

Віддруковано в редакційно-видавничому відділіКиївського національного університету будівництва і архітектури

Свідоцтво про внесення до Державного реєстру субєктівВидавничої справи ДК № 808 від 13.02.2002 р.