Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
9
Запорізький національний технічний університет
Електронні таблиці Excel
для студентів денного відділення економічних спеціальностей
Запоріжжя 2006
Електронні таблиці Excel. Методичні вказівки та завдання до лабораторних робіт за курсом “Інформатика та комп’ютерна техніка” для студентів денного відділення економічних спеціальностей. / Укладачі Н.І.Біла, В.М. Кузіна, Т.М. Оникієнко - Запоріжжя: ЗНТУ, 2006.- 46 с.
Містить індивідуальні завдання й методичні вказівки до лабораторних робіт за курсом “Інформатика та комп’ютерна техніка” для студентів денного відділення економічних спеціальностей за темою «Електронні таблиці Excel».
Укладачі: Біла Н.І., доцент,
Кузіна В.М., ст. викладач,
Оникієнко Т.М. ст. викладач
Рецензент: Вишневська В.Г., доцент,
Експерт: Кримська Л.А., зав.кафедрою
менеджменту й маркетингу, доцент
Відповідальний за випуск Біла Н. И., доцент
Затверджено на засіданні кафедри
обчислювальної математики,
протокол № 7 від 21.03.06
Зміст
1 Лабораторна робота №1.Тема: Розрахунки в таблицях Excel 4
1.1 Короткі теоретичні відомості 4
1.2 Приклад виконання завдання 7
1.3 Завдання для самостійної роботи 8
1.4 Варіанти індивідуальних завдань 8
1.5 Контрольні питання 15
2 Лабораторна робота №2. Тема: Обробка даних, представлених у вигляді списку 16
2.1 Короткі теоретичні відомості 16
2.2 Завдання для самостійної роботи 24
2.3 Контрольні питання 24
3 Лабораторна робота №3. Тема: Підсумки, перевірка даних та зведені таблиці. 25
3.1 Короткі теоретичні відомості 25
3.2 Завдання для самостійної роботи 33
3.3 Індивідуальні завдання 33
3.4 Контрольні питання 36
4 Лабораторна робота №4.Тема: Ділова графіка 37
4.1 Короткі теоретичні відомості 37
4.2 Завдання для самостійної роботи 41
4.3 Контрольні питання 42
5 Лабораторна робота №5.Тема: Консолідація даних 43
5.1 Короткі теоретичні відомості 43
5.2 Завдання для самостійної роботи 45
5.3 Контрольні питання 45
6 Література 46
1 ЛАБОРАТОРНА РОБОТА № 1
Тема: Розрахунки в таблицях Excel
Файли, з якими працює Excel, називаються «Робоча книга». За замовчуванням файлам даються імена Книга1, Книга2, … При збереженні файлу йому можна дати будь-яке ім'я, при цьому автоматично додасться розширення .xls. Кожна робоча книга складається з листів. У робочій книзі може бути стільки листів, скільки необхідно. Завжди можна додати лист за допомогою пункту меню Вставка/Лист або видалити непотрібний лист за допомогою пункту меню Правка/Удалить лист. Кожний робочий лист складається з клітин. У верхній частині робочого листа розташовані імена стовпців, які записуються англійськими буквами. Усього стовпців може бути 255. Ліворуч робочого листа розташовані номери рядків, які записуються числами. Рядків може бути до 65536. На перетинанні рядка й стовпця перебуває клітина. Кожна клітина має унікальну адресу, що складається з імені стовпця та номера рядка, на перетинанні яких клітина перебуває. Виділену клітину називають активною. В клітину можна записувати дані або формули для обчислень.
Типи даних. Існує кілька типів даних, які можна записати в клітину: текст, число, дату й час, формулу.
Текстові дані являють собою послідовність букв, цифр і пробілів між ними.
Числові дані представляються у вигляді послідовності цифр 0-9. Числа можуть бути цілими або із дробовою частиною. Дробова частина відокремлюється комою або крапкою (це залежить від настроювання Windows), наприклад, 5,6. Числа відображаються в клітини не більш ніж 15 цифрами. Кількість цифр після десяткової коми залежить від формату числа, що встановлюється за допомогою пункту меню Формат/Ячейки…на вкладці Числа.
Дату можна записати в клітину, якщо використати один з форматів дати. Наприклад, значення 12.09.05 буде сприйнято як 12 вересня 2005 року. Над датами визначені наступні операції:
Формула записується в клітину, щоб виконати обчислення по формулі й відобразити результат в клітини. В клітині можна записати тільки одну формулу. Формула починається зі знака рівності (=). У формулах використовують числові константи, адреси клітин, функції й знаки операцій, а також можна використати круглі дужки ( ) для зміни порядку виконання операцій.
У формулах часто використовуються адреси діапазонів. Діапазон клітин - це безперервна прямокутна область із клітин. Адреса діапазону записується через двокрапку (:). Наприклад, А1:С4 - це адреса діапазону, що складається з 12 клітин.
Адреси клітин і діапазонів у формулах можуть бути двох видів: відносні й абсолютні. Ознакою абсолютної адреси є знак $. Наприклад, адреса В5 - відносна адреса клітини, а адреса $B$5 - абсолютна адреса тієї ж клітини. Адреси $B5 і B$5 - є частково абсолютними адресами клітини В5.
Приклади формул:
=(3*6 + 12)/4 - 2 результатом буде 5,5
=А2*2 результат - число з клітини А2, помножене на 2
=СУММ(В2:В6) результат - сума чисел з клітин від В2 до В6.
=СРЗНАЧ($В$2:$С$6) результат - середнє значення чисел з діапазону. У формулі використані абсолютні адреси клітин.
Копіювання даних
Дані, які введені в одну клітину, можна копіювати в інші клітини. Один зі способів копіювання використовує інструменти Копировать й Вставить, які працюють із буфером. Другий спосіб використовує маркер автоматичного заповнення, що розташований у правому нижньому куті кожної клітини або діапазону.
Якщо копіюється формула, то відносні адреси змінюються на величину здвигу. Наприклад, якщо формулу =А2+В2 , що записана в клітини С2, скопіювати до клітини С3, то вийде формула =А3+В3. Якщо ж у формулі записана абсолютна адреса, то вона копіюється в іншу клітину без змін.
Функції Excel. Для обчислень в Excel використовуються формули. У формулах часто використовуються вбудовані функції Excel. Усього є більше 100 вбудованих функцій.
Функції розділені на категорії. Повний список категорій функцій і самих функцій можна побачити у вікні майстра функцій, що викликається за допомогою інструмента fx . Інструмент перебуває на панелі інструментів Стандартная. У лівому нижньому куті вікна майстра функцій розташований знак «?», що викликає підказку по роботі з функцією. Підказка містить докладний опис аргументів функції й результату та велику кількість прикладів використання функції.
Функції значно спрощують обчислення. Наприклад, якщо необхідно обчислити суму значень, занесених в осередки від A1до H1, це можна зробити за допомогою функції =СУММ(А1:Н1), а не за допомогою довгої формули =А1+В1+С1+D1+E1+F1+G1+Н1.
Кожний виклик функції складається із трьох елементів: знак рівності (=) указує на формулу, ім'я функції (наприклад, СУММ) указує, яку операцію необхідно провести, аргумент функції (наприклад, А1:Н1) указує адреси клітин, значення яких використовуються при обчисленнях. Аргумент часто являє собою групу клітин, але може бути й більш складним. Ви можете ввести функцію в клітину самостійно або за допомогою майстра функцій. Оскільки функція СУММ досить часто використовується в EXCEL, її кнопка винесена на стандартну панель інструментів. Наприклад, щоб знайти суму значень, просто клацніть на кнопці (АВТОСУММА).
Приведемо опис декількох часто використовуваних функцій.
СРЗНАЧ(діапазон) - повертає середнє (арифметичне) своїх аргументів; наприклад, =СРЗНАЧ(А2:А9). Функція належить до категорії статистичні.
МАКС(діапазон), МИН(діапазон) - повертають максимальне й мінімальне значення серед чисел зазначеного діапазону. Належить до категорії статистичні.
ЕСЛИ(умова; вираз1; вираз2) - функція перевіряє умову й обчислює вираз1, якщо умова виконалася; функція обчислює вираз2, якщо умова не виконалася. Наприклад, результатом функції =ЕСЛИ(А2>20;1;0)
буде число 1, якщо в клітини А2 записане число більше 20, і 0, якщо в клітини А2 записане число, що менше або дорівнює 20.
Створення списку значень, що розкривається.
Часто в якому-небудь стовпці таблиці дані приймають фіксований набір значень. У цьому випадку зручно у вільному місці таблиці створити список можливих значень, а введення даних здійснювати, вибираючи потрібне значення зі списку, що розкривається. Для цього виконайте наступні дії:
1.2 Приклад виконання завдання
Завдання. Створити таблицю, у якій розраховується заробіток робітників сільського кооперативу при збиранні яблук. У сільському кооперативі працюють дві бригади, робітники яких збирають яблука трьох сортів. За 1 кг зібраних яблук сорту “Семеренко” працівник одержує 0,17 грн.; “Джонатан” - 0,12 грн.; “Шафран” - 0,10 грн. Скласти таблицю, що містить наступні стовпчики: номер бригади, прізвище робітника, сорт яблук, кількість зібраних кілограм, оплата за зібрані яблука. Для введення номера бригади, прізвища робітника й сорту яблук організувати списки, що розкриваються. У стовпці оплата записати формулу, що обчислює заробіток робітника. Обчислити загальну кількість зібраних яблук і загальну суму заробітку робітників.
На рисунку 1.1 наведена створена таблиця. Діапазон G3:G8 містить список всіх робітників. У діапазоні В3:В9 кожна клітина містить список, що розкривається, значень із діапазону G3:G8, що створювався через пункт меню Данные/Проверка.
До клітини Е3 введена формула:
=ЕСЛИ(C3 = $I$3; $J$3; ЕСЛИ(C3 = $I$4; $J$4; $J$5))*D3
Ця формула обчислює заробіток робітника залежно від зібраної кількості яблук (клітина D3) і розцінок для кожного сорту (клітини I3:J5). Адреси клітин з діапазону I3:J5 є абсолютними, тому що вони використовуються для кожного робітника, тому не повинні змінюватися при копіюванні. Щоб обчислити загальну кількість зібраних кілограм яблук, до клітини D11 введена формула =СУММ(D3:D9).
Рисунок 1.1 - Таблиця для завдання
1.3 Завдання для самостійної роботи
Створити таблицю для даних свого варіанту в Excel:
Варіант 1
На малому підприємстві працюють три бригади, робітники яких виготовляють деталі двох найменувань на станках з програмним управлінням. Кожен робітник за зміну виготовляє деталі тільки одного з найменувань. Існують різні розцінки для кожного виду деталей та норма для кількості деталей, виготовлених за зміну. Якщо кількість деталей, зроблених робітником, перевищує норму, то робітник отри мує премію у розмірі 10% від заробітку.
Норми визначити самостійно та записати їх на Лист Excel. Скла сти таблицю, що містить таку інформацію: номер бригади; прізвище робітника; найменування виготовлених деталей; їх кількість; вартість їх виготовлення; премія; загальна нарахована сума.
Найменування виготовлених деталей вибирати зі списку, створе ного в меню Данные – Проверка. Встановити контроль введе них даних для номеру бригади.
Підрахувати загальну суму, сплачену робітникам. Визначити се редній та максимальний розмір премії.
Варіант 2
Компанія по постачанню електроенергії отримує плату з клієн тів за тарифом:
Послугами компанії користуються 20 клієнтів, які мешкають у трьох будинках. Кожен платник може сплачувати нараховану суму готівкою або кредитною карткою.
Діючі тарифи повинні бути записані в окремих клітинках. Вид оплати вибирати зі списку, створеного в меню Данные – Проверка. Встановити контроль введених даних для номеру будинку.
Скласти таблицю, що містить таку інформацію: номер будинку; прізвище клієнта; попередній показник лічильника; останній показник лічильника; спожита кількість електроенергії; її вартість; вид оплати (готівка або кредитка).
Розрахувати вартість електроенергії для кожного клієнта і за гальну нараховану суму. Визначити мінімальну та середню кількість спожитої електроенергії.
Варіант 3
Три магазини замовляють на складі книжки. За замовлення більше ніж 30 примірників склад зменшує вартість на них на 10%; а за 50 і більше примірників – на 15%. Скласти таблицю, що містить таку інформацію: магазин, видавництво, автор, назва книги, ціна за одну книгу, замовлена кількість, загальна вартість зі знижкою.
Відсотки знижок записати до клітинок та посилання на них здійснювати за допомогою абсолютної адреси або імені. Назву видав ництва вибирати зі списку, створеного в меню Данные – Проверка. Встановити контроль введених даних для номеру магазину.
Обчислити загальну вартість та кількість замовлених книг. Визначити найбільший та середній обсяг замовлення.
Варіант 4
У місті розташовано три автопарки для таксі з автомобілями економ – класу та престиж – класу. Сплата за таксі здійснюється за такими правилами. Поїздки на 5 км або менше коштують однаково для всіх типів таксі. Кожний наступний кілометр сплачується за ін шим тарифом. Цей тариф для таксі престиж – класу більше ніж тариф для економ – класу на певний відсоток, наприклад на 10%. Водій отримує свій відсоток за кожну поїздку. Розміри тарифів і відсотків встановити самостійно та записати їх на Лист Excel.
Скласти таблицю обліку поїздок, яка містить таку інформацію: номер автопарку; клас таксі; прізвище водія; відстань; вартість по їздки; заробітна плата водія.
Клас таксі вибирати зі списку, створеного за допомогою меню Данные – Проверка. Для номеру автопарку встановити контроль вве дених даних або вибирати його зі списку.
Визначити найбільшу та середню відстань поїздки. Обчислити середню вартість поїздки та загальний розмір денної виручки.
Варіант 5
Три туристичні фірми організують подорожі до п’яти країн. Якщо клієнт замовив більше трьох путівок, він отримує знижку у роз мірі 5% від вартості путівок; більше десяти путівок – 10%. Відсотки знижок записати до окремих клітинок.
Скласти таблицю, яка містить таку інформацію: фірма; прізвище покупця; країна; вартість однієї путівки; кількість замовлених путівок; кількість днів відпочинку; вартість путівок з урахуванням знижок.
Назву фірми та країну для подорожі вибирати зі списків, створе них в меню Данные – Проверка.
Обчислити загальну вартість путівок. Визначити середню кіль кість днів відпочинку. Яка найбільша кількість путівок в одному замовленні?
Варіант 6
На оптовому складі зберігається продукція чотирьох категорій: парфумерія, побутова хімія, господарчі товари, посуд. На складі діє така система знижок:
Значення відсотків знижок повинні бути записані в окремих клі тинках. Відомість знижки ціни повинна мати таку інформацію: кате горія товару; його назва; кількість товару; ціна одиниці товару до знижки; термін зберігання в місяцях; ціна партії зі знижкою.
Категорію товару вибирати зі списку, створеного за допомогою меню Данные – Проверка. Встановити контроль введених даних для терміну зберігання.
Знайти середній термін зберігання однієї партії товару, загальну вартість усього товару на складі після знижки.
Варіант 7
На аптечному складі зберігаються ліки. Необхідно скласти відо мість з обліку ліків. Таблиця повинна мати таку інформацію: назва препарату; країна – виробник; дата виготовлення; термін зберігання в місяцях; ціна за одиницю упаковки; кількість; дата закінчення терміну зберігання; загальна вартість; уцінка. Для обчислення дати закінчення терміну зберігання вважатимемо, що в місяці завжди 30 днів.
Замість системної дати комп’ютера краще записати до окремої клітинки конкретну дату та використовувати її в розрахунках, не змі нюючи. Країну – виробника треба вибирати зі списку, створеного в меню Данные – Проверка. Встановити контроль введених даних для терміну зберігання.
Якщо термін зберігання закінчується менш ніж за 30 днів до вказаної дати, то ціна товару зменшується на 10%. Запишіть її до стовпчику "Уцінка". Якщо термін зберігання вже скінчився, то нав проти назви препарату в тому ж стовпчику зробіть позначку “Підлягає знищенню”.
Обчислити загальну вартість усіх ліків. Визначити середній та максимальний терміни зберігання ліків.
Варіант 8
У двох філіях фірми працює 20 працівників. З їхньої заробітної плати утримується прибутковий податок згідно таких ставок:
Відсотки податків та розмір неоподаткованого мінімуму зар плати (85 грн.) треба записати до окремих клітинок.
Всі працівники розподілені на три категорії: робітники, служ бовці, адміністрація. Робітники та службовці отримують премію у розмірі 10% від заробітку, а адміністрація – 15%. Скласти таблицю, яка містить такі дані: назва філії; прізвище працівника; категорія; заробітна плата; премія податок; одержати.
Назву філії та категорію працівника вибирати зі списків, створе них в меню Данные – Проверка.
Визначити максимальну та мінімальну премію. Обчислити се редній розмір податку та загальну суму заробітку.
Варіант 9
Фірма – постачальник продукції виконує замовлення чотирьох магазинів по таких групах товарів: побутова техніка, аудіоапаратура та відеоапаратура. Якщо замовлений товар не було поставлено у мага зин протягом певної кількості днів після оплати (наприклад, за 3 дні), фірма – постачальник сплачує штрафи у розмірі 0,1% від вартості товару за кожний день затримки.
Відсоток штрафу та договірну кількість днів поставки після оплати записати до окремих клітинок.
Назви груп товарів вибирати зі списку, створеного за допомо гою меню Данные – Проверка. Встановити контроль введених даних для номеру магазину.
Скласти таблицю виконання заявок магазинів, що містить таку інформацію: номер магазину; група товару; назва товару; ціна за оди ницю; замовлена кількість одиниць товару; дата оплати; дата постав ки; сплачена сума; фактична кількість днів між поставкою та оплатою товару; штрафи.
Обчислити середню вартість одного замовлення, Підрахувати загальну вартість замовлених товарів та суму штрафів.
Варіант 10
У двох бригадах працюють 20 робітників. Всі вони мають одна ковий щомісячний оклад. Кожного місяця відома нормативна кіль кість робочих днів. Оплата здійснюється пропорційно кількості від працьованих днів. Робітникам сплачуються такі надбавки за розряд:
Нормативну кількість робочих днів у місяці, розмір місячного окладу та відсотки надбавок треба записати до окремих клітинок.
Розряд робітника вибирати зі списку, створеного за допомогою меню Данные – Проверка. Встановити контроль введених даних для номеру бригади або кількості відпрацьованих днів.
Скласти таблицю розрахунку заробітної плати, яка містить такі дані: номер бригади; прізвище робітника; розряд; кількість відпрацьо ваних днів; зарплата без надбавок; надбавка; всього нараховано. Ви значити середню кількість днів у місяці, відпрацьованих одним робіт ником, мінімальну та загальну нараховану суму.
Варіант 11
Телефонна компанія об’єднує три АТС. Вартість міських та міжміських розмов підраховується окремо за різними тарифами. Крім того, для міжміських розмов існує пільговий тариф за розмови у свята та в нічний час. Розміри тарифів треба записати до окремих клітинок.
Скласти таблицю розрахунків за телефон, яка містить таку ін формацію: номер АТС; номер телефону; прізвище абонента; тип роз мов (міська, міжміська або з пільгами); тривалість розмов; нарахована плата за розмови.
Тип розмов вибирати зі списку, створеного в меню Данные – Проверка. Встановити контроль введених даних для номеру АТС або вибирати його зі списку.
Визначити максимальну та середню тривалість розмов. Обчис лити загальну суму, сплачену за телефонні розмови.
Варіант 12
Оптова торговельна фірма реалізує автомобілі марок “Таврія”, “Нубіра” та “Леганза”. Якщо покупець оплачує вартість замовлених автомобілів протягом трьох днів із дня оформлення замовлення, він отримує знижку у розмірі 5% від вартості покупки; протягом семи днів – 3%. Скласти таблицю, яка містить таку інформацію: прізвище продавця; марка автомобіля; вартість однієї машини; кількість замов лених автомобілів; дата замовлення; дата оплати; сума до сплати.
Вартість кожної марки автомобілів треба записати на Листі Excel в окремих клітинках та вставляти її до таблиці за допомогою функції ЕСЛИ. Марку автомобіля та прізвище продавця вибирати зі списку, створеного в меню Данные – Проверка. Встановити контроль введених даних для кількості замовлених автомобілів
Обчислити загальну кількість проданих машин та їх вартість з урахуванням знижок. Вказати середню та найбільшу кількість автомо білів в одному замовленні.
Варіант 13
Житлове управління нараховує квартплату мешканцям трьох будинків. Відома вартість оплати за один квадратний метр та норма житлової площі для однієї людини. Якщо площа квартири перевищує норму для всієї родини більше, ніж, на 10 кв. м, то сплата за надмірну площу збільшується вдвічі. За інших умов квартплата нараховується пропорційно площі квартири.
Скласти таблицю, яка містить таку інформацію: номер будинку; номер квартири; прізвище мешканця; площа квартири; кількість меш канців; нарахована квартплата.
Норми визначити самостійно, записати їх до клітинок і вико ристовувати в розрахунках посилання на них. Встановити контроль введених даних для номеру будинку та кількості мешканців за допо могою меню Данные – Проверка.
Підрахувати загальну та середню квартплату по всьому управ лінню. Визначити максимальну кількість мешканців в одній квартирі.
Варіант 14
В торговельній фірмі працюють продавці, консультанти та ме неджери. Щотижня фірма виплачує робітникам зарплату у розмірі 5% від виручки. Якщо товару продано більше, ніж на планову суму, то зарплата становить 7% від виручки. Робітники, які працюють у фірмі більше 5 років, отримують на 1% більше.
Посаду робітника вибирати зі списку, створеного в меню Данные – Проверка. Встановити контроль введених даних для поля стаж роботи.
Планову суму та відсотки для розрахунку зарплати визначити самостійно, записати їх до клітинок Excel.
Скласти таблицю, яка містить таку інформацію: прізвище робіт ника; посада; стаж роботи; сума виручки; зарплата. Визначити най більший та середній стаж роботи робітників і загальну суму зарплати.
Варіант 15
Компанія по постачанню води підприємствам нараховує плату за користування водою згідно тарифам:
Деякі клієнти мають пільги. Вони сплачують не 100% від варто сті води, а менше, наприклад 30% або 50%. Створити таблицю, яка містить таку інформацію: район міста; підприємство; розмір пільг; по переднє показання лічильника; останнє показання лічильника; спожи та кількість води; розмір оплати.
Діючі тарифи та відсотки пільг записати до клітинок Excel. Район міста вибирати зі списку, створеного в меню Данные – Проверка. Встановити контроль введених даних для розміру пільг.
Визначити максимальну та загальну кількість спожитої води. Знайти середню сплачену суму.
1.5 Контрольні питання
2 ЛАБОРАТОРНА РОБОТА №2
Тема: Обробка даних, представлених у вигляді списку
2.1 Короткі теоретичні відомості
Для того, щоб використати засоби сортування, фільтрація, зведені таблиці, консолідація та підсумки потрібно представити електронну таблицю у вигляді бази даних або списку.
Список - це один із засобів організації даних на робочому листі. Список створюється як безперервна прямокутна область клітин, що складається з рядків з однотипними даними. Наприклад, перелік працівників із приклада, наведеного в попередній лабораторній роботі, у якому стовпчики мають відповідно такі імена: № бригады, Фамилия, Сорт, Количество, Начислено (грн.) - являє собою список даних. Дані, організовані в список, у термінології Ехсеl називаються базою даних (БД). При цьому рядки таблиці - це записи бази даних, а стовпці - поля бази даних.
Щоб перетворити таблицю Ехсel у список, необхідно привласнити стовпцям імена, які будуть використовуватися як імена полів. Варто мати на увазі, що імена стовпців можуть складатися з декількох рядків заголовків, розміщених в одному рядку таблиці Ехcel, як це зроблено на рис 1.1. В прикладі список розташовано в діапазоні А2:Е9.
При створенні списку на робочому листі Ехсеl необхідно виконувати такі правила:
2.1.1 Сортування даних
Для швидкого сортування на панелі інструментів Стандартная перебувають дві кнопки: сортувати по зростанню; сортувати по спаданню. Ключем сортування в цьому випадку є стовпець із поточною клітиною.
Рисунок 2.1 - Кнопки сортування на панелі інструментів
Список можна відсортувати за алфавітом або за значенням відповідно до даних одного або декількох полів. Щоб відсортувати весь список, досить виділити одну клітину й клацнути на інструменті Сортировка, Ехсеl автоматично виділяє весь список і сортує записи за значеннями у виділеному полі. Перший рядок списку, в якому перебувають імена полів, не буде включений в сортування. Необхідно мати на увазі, що, якщо підсумковий рядок не відокремлений від списку, в цьому випадку підсумковий рядок також буде включений в сортування, тому більш доцільно самостійно виділяти область вихідного списку для сортування.
Команда Сортировка здійснюється також і через діалогове вікно пункту меню Данные/Сортировка. Тут можна вказати сортування списку по трьох полях. У трьох полях вікна Сортировка можна задати ключі – імена полів, по яких буде виконане сортування. Excel сортує список по першому обраному полю, а при збігу значень у першому полі, записи сортуються по другому обраному полю. Наприклад, дані в прикладі можна відсортувати по стовпчику № бригады. У другому полі діалогового вікна Сортировка можна задати наступний ключ сортування, наприклад Фамилия. Тоді список буде впорядкований по бригадах, а усередині бригад - по прізвищах (за алфавітом). Дія третього ключа сортування аналогічна.
2.1.2 Фільтрація даних у списку
За допомогою фільтрів можна виводити й переглядати тільки ті дані, які задовольняють певним умовам. Ехсel дозволяє швидко й зручно переглядати необхідні дані зі списку за допомогою простого засобу - автофільтру. Більш складні запити до бази даних можна реалізувати за допомогою команди Расширенный фильтр. Автофильтр. Щоб використати автофільтр, треба спочатку виділити область списку із заголовками полів. Потім виконати команду Автофильтр у меню Данные. За командою Автофильтр Ехсel розташовує список, що розкривається, безпосередньо в іменах стовпців списку. Клацнувши по стрілці, можна вивести на екран список всіх унікальних елементів відповідного стовпця. Якщо виділити деякий елемент стовпця, то будуть сховані всі рядки, крім тих, що містять виділене значення.
Наприклад, якщо вибрати значення № бригады рівне 1, то будуть обрані тільки ті робітники, які працюють у першій бригаді.
Елемент стовпця, що виділений у списку, що розкривається, називається критерієм фільтра. Можна продовжити фільтрацію списку за допомогою критерію з іншого стовпця. Наприклад, якщо тепер у поле Сорт вибрати значення «Семеренко», то на екран буде виведено тільки один рядок, як показано на рис. 2.2.
Щоб видалити критерії фільтра для окремого стовпця, треба вибрати параметр Все в списку, що розкривається.
Рисунок 2.2 - Вікно із установленим автофильтром
За допомогою автофильтра можна для кожного стовпця задати потрібні критерії відбору записів, наприклад вивести на екран тільки ті записи, значення полів яких перебувають у границях заданого інтервалу. Щоб задати потрібний критерій, треба в списку, що розкривається, вибрати параметр Условие..., а потім у діалоговому вікні Пользовательский автофильтр ввести потрібні критерії. На рисунку 2.3 показаний приклад завдання умов для поля Количество. Будуть вибиратися записи про співробітників, які зібрали більше 120 кг яблук і менше 180 кг.
Рисунок 2.3 - Установка умови у вікні
Складна фільтрація. Для фільтрації списку або бази даних за складним критерієм, що буде визначений нижче, а також для одержання частини списку, що задовольняє декільком заданим умовам, в Ехсel використається команда Расширенный фильтр меню Данные. Відмінність цієї команди від команди Автофильтр полягає в тому, що, крім перерахованих вище можливостей, відфільтровані записи можна винести в інше місце робочого листа Ехсel, не зіпсувавши початковий список.
Рисунок 2.4 - Таблиця критеріїв для розширеного фільтра
Щоб використати команду Расширенный фильтр, треба спочатку створити таблицю критеріїв, яку варто розмістити на тому ж робочому листі, що й список. Для формування таблиці критеріїв необхідно скопіювати імена полів списку у вільну частину робочого листа. Під іменами полів записуємо умови відбору даних. Наприклад, з метою відбору записів співробітників, які заробили більше 22 гривень, треба створити таблицю критеріїв як на малюнку 2.4.
Якщо необхідно одержати список співробітників, яким нарахована сума в діапазоні від 20 до 23 гривень, в таблиці критеріїв кожна умова повинна бути задана окремо, але в одному рядку, тому що вони зв'язані оператором И. Таким чином, таблиця критеріїв буде мати вигляд, представлений на рисунку 2.5.
Рисунок 2.5 - Блок критеріїв з подвійною умовою
Крім таблиці критеріїв для команди Расширенный фильтр треба визначити блок виведення. Це означає, що треба скопіювати у вільне місце робочого листа імена тих полів списку, які ви хочете бачити у відібраних даних. Наприклад, для таблиці із приклада необхідно одержати список співробітників, яким нарахована сума від 20 до 23 гривень. Тоді блок виведення може містити імена полів Фамилия й Начислено (грн.).
Кількість рядків у результаті Ехсel визначить самостійно. Таким чином, для виконання команди Расширенный фильтр треба виконати такі дії:
Для приклада, що ми розглядаємо, підготовлені для фільтрації таблиця критеріїв і блок виведення, а також список, що фільтрується, наведені на рисунку 2.6.
Рисунок 2.6 - Застосування розширеного фільтра
Тепер можна запускати команду Расширенный фильтр, по якій на екран буде виведене діалогове вікно. У цьому вікні треба задати область вхідного списку, визначити діапазон критеріїв (умов), задати рядок діапазону виведення.
В області Обработка треба вказати як буде виконуватися фільтрація. Якщо обрано режим - «фільтрувати список на місці», то Ехсеl сховає всі рядки вихідного списку, які не задовольняють заданим критеріям.
Якщо встановлено перемикач «Тільки унікальні записи», то повторювані рядки вхідного списку не будуть показані в області результату. Якщо умови пошуку задані в таблиці критеріїв в одному рядку, то ці умови зв'язані оператором И, якщо ж умови пошуку задані в різних рядках, то вони зв'язані оператором ИЛИ.
Використання критерію, що обчислюється
Завдання критеріїв, що обчислюються, вимагає виконання правил:
Наприклад, створимо таблицю критеріїв, що обчислюються, за якою ведеться пошук тих записів, де розмір заробітку (поле Начислено (грн.)) перевищує середнє значення по всіх працівниках. Формула для критерію, що обчислюється, використовує функцію СРЗНАЧ і має вигляд: =Е3>СРЗНАЧ ($Е$3:$Е$9).
Рисунок 2.7 - Використання критерію, що обчислюється
Використання критерію, що обчислюється, накладає обмеження на таблицю критеріїв. У цьому випадку ім'я стовпця в таблиці критеріїв, що містить значення критерію, що обчислюється, повинне відрізнятися від імені подібного стовпця у вихідному списку. Тому в прикладі ім'я поля Начислено (грн.) у таблиці критеріїв одержало ім'я Заработок.
2.1.3 Робота з функціями бази даних.
Microsoft Excel пропонує 14 функцій для роботи зі списками. Кожна із цих функцій повертає інформацію про елементи списку, які задовольняють деяким умовам.
1) Функція СЧЕТЕСЛИ(діапазон; критерій) підраховує кількість елементів у діапазоні, які задовольняють критерію. Діапазон записується у вигляді блоку, критерій записується у вигляді текстового рядка, що містить умови. Наприклад, щоб порахувати кількість працівників, які зібрали більше 150 кг яблук, записуємо у вільній клітині таблиці формулу: =СЧЕТЕСЛИ(D3:D9;”>150”).
2) Функція
СУММЕСЛИ(діапазон; критерій; діапазон_підсумовування)
підсумує значення в зазначеному діапазоні підсумовування, з огляду тільки на ті записи, які задовольняють критерію. Наприклад, щоб порахувати сумарну зарплату працівників першої бригади, записуємо у вільному місці таблиці формулу: =СУММЕСЛИ(А3:А9;”1”;Е3:Е9).
3) Інші функції, що орієнтовані на роботу зі списками, мають узагальнену назву БД-функції. На відміну від двох вище наведених функцій вони мають потребу в створенні блоку критеріїв як при роботі з розширеним фільтром. Функція
ДСРЗНАЧ(діапазон_списку;поле;блок_критеріїв)
- обчислює середнє значення в зазначеному полі серед записів, які задовольняють умові, записаній в блоці критеріїв. Наприклад, щоб обчислити середню зарплату працівників першої бригади створимо блок критеріїв в осередках K3:K4, записавши в K3 - № бригады, а в K4 - 1. Тоді така формула обчислить необхідне:
=ДСРЗНАЧ(A2:E9;Е2;K3:K4)
2.2 Завдання для самостійної роботи
Для списку, що ви створили в попередній лабораторній роботі, виконайте такі дії:
3 ЛАБОРАТОРНА РОБОТА №3
Тема: Підсумки, перевірка даних та зведені таблиці
3.1 Короткі теоретичні відомості
3.1.1 Формування підсумків у списках
Команда Итоги з меню Данные дозволяє обчислювати підсумкові величини для даних списку, у яких збігаються значення в зазначеному стовпці. Команда Итоги створює групи записів, у яких збігаються значення обраного поля. Щоб команда Итоги працювала, список повинен бути спочатку відсортований по полю, що створює групи. Після сортування можна обчислити підсумки по групах записів. Команда Итоги дозволяє підводити підсумки більш ніж по одному полю із групи записів, а також додавати оброблювані поля й застосовувати для обчислення результатів інші функції. У розглянутому прикладі відсортуємо список по полю № бригады й заповнимо вікно діалогу як показано на рисунку 3.1.
Рисунок 3.1 - Вікно діалогу «Проміжні підсумки»
У результуючій таблиці після закінчення записів кожної групи буде вставлений додатковий рядок з обчисленими підсумковими функціями, як показано на рисунку 3.2.
Рисунок 3.2 - Підведення підсумків по бригадах
Таблиця з підсумками структурована. Ліворуч показані три рівні угруповання. Якщо клацнути по значку “ - “ рівня 2, то на екрані залишиться тільки підсумковий рядок для першої або другої бригади.
3.1.2 Перевірка даних, що вводять
Ви можете використати команду Данные/Проверка для того, щоб перевіряти чи задовольняють значення, які вводяться в список, умовам. Для завдання умов на значення, що вводяться, виділіть діапазон, на який будете накладати умови, і виберіть пункти меню Данные/Проверка…У вікні діалогу Проверка значений на вкладці Параметры вкажіть тип даних і інтервал значень, які дозволяється вводити. На рисунку 3.3 наведене вікно діалогу, у якому задаються обмеження для поля Количество. Вкладка Сообщение для ввода дозволяє створити підказку користувачеві про те, які дані можна вводити. На вкладці Сообщение об ошибке можна вказати повідомлення, що буде з'являтися на екрані, якщо введені некоректні
дані.
Рисунок 3.3 - Перевірка даних
3.1.3 Створення зведених таблиць
Зведена таблиця - це інтерактивна таблиця робочого листа, що підсумовує великі обсяги даних, використовуючи обраний вами формат і метод обчислень. Зведена таблиця дозволяє обчислювати підсумкові величини для даних, у яких збігаються значення у двох зазначених стовпцях. Зведені таблиці є динамічними об'єктами, що дозволяють виводити інформацію з різним ступенем деталізації.
Для створення зведеної таблиці активізуємо будь-яку клітину усередині списку й виконаємо команду Данные/Сводная таблица. Починає працювати програма, що називається Майстер зведених таблиць. Ця програма допоможе вам задати всі необхідні параметри для створення зведеної таблиці.
Створимо зведену таблицю для нашого прикладу за допомогою майстра.
Поля бази даних, на основі якої будується зведена таблиця, представлені в цьому вікні у вигляді кнопок з назвами цих полів. Треба так перетягнути їх у відповідні області, щоб зведена таблиця придбала необхідну структуру. У вікні є чотири області:
Строка - для використання поля, розташованого в цій області, як заголовки рядків.
СТОЛБЕЦ – для використання поля, розташованого в цій області, як заголовки стовпців.
ДАННЫЕ – для підсумовування значень поля, розташованого в цій області, в осередках зведеної таблиці.
СТРАНИЦА – для забезпечення можливості виведення даних зведеної таблиці, що мають відношення тільки до поля, що розташоване в цій області.
У нашому прикладі кнопку № бригады треба перетягнути в область СТОЛБЕЦ, кнопку Сорт в область СТРОКА, а в область ДАННЫЕ треба перетягнути кнопку Количество.
Подвійний щиглик на кнопці, що переміщена до області ДАННЫЕ, відкриває діалогове вікно Вычисление поля сводной таблицы, що дозволяє вибрати правило, за яким підводять підсумки у зведеній таблиці.
Вкажіть структуру зведеної таблиці як на рисунку 3.7 та перевірте, що одержали зведену таблицю як на рисунку 3.6.
Можна змінити зведену таблицю, що наведена на рисунку 3.6, у такий спосіб: поміняти місцями рядки й стовпці. Зробіть це самостійно.
Рисунок 3.6 – Зведена таблиця, побудована по установках рис. 3.7
У кожній клітині зведеної таблиці перебуває сума кг яблук певного сорту, зібраних зазначеною бригадою.
Рисунок 3.7 - Четверте діалогове вікно Майстра зведених таблиць
Для внесення змін у зведену таблицю, або для побудови нової зведеної таблиці на основі існуючої зручно скористатися макетом зведеної таблиці. Для виклику макета виділіть будь-яку клітину усередині зведеної таблиці й викличте контекстне меню (правою кнопкою миші). Виберіть пункт Мастер. З'явиться третє діалогове вікно майстра зведених таблиць. Клацніть на кнопці Макет…
Одне з полів можна розташовувати в області Страница. Нижче наведений макет зведеної таблиці з використанням поля Страница.
Рисунок 3.8 – Макет зведеної таблиці з полем Сторінка
Зведена таблиця, побудована на основі цього макета, наведена на рис. 3.9. Зверніть увагу на те, що в клітини B1 з'явилося слово (ВСЕ). Це означає, що підсумки обчислені на підставі даних про всі бригади.
Щоб переглянути підсумки по кожній окремій бригаді, потрібно розгорнути список, що розкривається, що перебуває в клітини B1, і вибрати бригаду, що цікавить вас.
Рисунок 3.9 – Зведена таблиця з використанням поля Сторінка
Редагування даних у зведеній таблиці. Excel не дозволяє редагувати дані безпосередньо у зведених таблицях. Для цього треба змінити дані у вхідному списку й перерахувати зведену таблицю (знак на панелі зведених таблиць).
У зведену таблицю можна вставляти поля, що обчислюють. Наприклад, створимо нове поле з назвою Налог, який можна обчислити за формулою Начислено (грн.)*0,07 (податок 7%). Для цього на панелі інструментів Сводные таблицы обираємо команду Сводная таблица Ф ормулыВычисляемое поле (рис. 3.10).
Рисунок 3.10 - Панель інструментів „Зведені таблиці”
Рисунок 3.11 - Створення нового поля Налог
З'явиться діалогове вікно Вставка вычисляемого поля. Заповніть його як показано на рис. 3.11.
Щоб на екрані з'явилася панель інструментів Сводные таблицы, виберіть пункт меню Вид/Панели инструментов і відзначте рядок Сводные таблицы. Зведена таблиця з полем, що обчислює, наведена на рисунку 3.12.
Рисунок 3.12 - Зведена таблиця з полем, що обчислюється
3.2 Завдання для самостійної роботи
Створіть форму для введення й коректування даних у списку й вивчите всі можливості роботи з формою. (Данные/Форма).
Варіант 1
Варіант 2
Варіант 3
Варіант 4
Варіант 5
Варіант 6
Варіант 7
Варіант 8
Варіант 9
Варіант 10
Варіант 11
Варіант 12
Варіант 13
Варіант 14
Варіант 15
3.4 Контрольні питання
4 ЛАБОРАТОРНА РОБОТА №4
Тема: Ділова графіка в Excel
4.1 Короткі теоретичні відомості
Діаграми в Microsoft Excel створюються на основі даних, розміщених на листі. При зміні даних на листі діаграми змінюються теж. Для побудови діаграми зручніше за все скористатися Майстром Діаграм і діяти за його вказівками. Діаграму можна створити на окремому листі, а можна впровадити в потрібний лист книги Excel.
Рисунок 4.1 – Дані підсумкової таблиці для побудови діаграми
Розглянемо побудову діаграми на прикладі. Для цього на листі Ведомость підведіть підсумки по полю Сорт і клацніть на кнопці із цифрою 2 в області структури таблиці, щоб сховати вихідні дані й залишити тільки сумарні кількості. Виділіть два стовпці – Сорт і Количество, як показано на рис. 4.1. За цими даними будемо будувати діаграму. Перший виділений стовпець буде використатися як підписи до елементів діаграми. Виділяйте обов'язково й імена полів, вони також будуть використовуватись як підписи в діаграмі. Клацніть на кнопці Майстер діаграм, що розташована на панелі інструментів і додержуйтеся інструкцій майстра. Побудова діаграми складається з 4-х кроків.
На першому кроці необхідно вибрати один із зазначених типів діаграм. Спочатку виберіть його у вікні Тип. При виборі якого-небудь типу в правому вікні Вид з'являється зображення різних діаграм цього типу. Клацніть на обраному типі й виді діаграми, наприклад, на „Гистограмма” й натисніть кнопку Далі. Почнеться другий етап.
Рисунок 4.2 - Вибір типу й виду діаграми
Якщо перед викликом Майстра діаграм, Ви виділили дані для діаграми, то в поле Діапазон уже записані їхні адреси. Натисніть на кнопку Згорнути/Розгорнути в цьому полі. Вікно Майстра зникне й можна буде перевірити або змінити виділений діапазон, орієнтуючись по зображеній діаграмі. Виберіть один з перемикачів Ряди в рядках або Ряди в стовпцях. Подивіться, як міняється при цьому зовнішній вигляд діаграми.
Вкладка Ряд дозволяє видалити ряд даних або додати новий. Можна вказати підписи для рядів по осях, причому можна у відповідному полі вказати адресу клітини з потрібним текстом, а можна просто набрати текст.
3) Крок третій – Параметри діаграми.
Зараз можна задати Заголовки (назва діаграми й осей), Лінії сітки (основні й проміжні), Підписи даних (вибрати вид або не підписувати дані зовсім), можна додати до діаграми Таблицю вихідних даних і Легенду, вибрати її розташування.
Рисунок 4.3 – Завдання параметрів діаграми
Виберіть лист, на якому буде розташована діаграма, і клацніть по кнопці Готово. На рис. 4.4 представлена отримана діаграма. Висота стовпчиків цієї діаграми відповідає кількості зібраних яблук кожного сорту. Збоку діаграми розташована Легенда, що показує якими кольорами зображуються дані кожного стовпця. Це важливо, коли на гістограмі зображуються декілька рядів даних.
Рисунок 4.4 – Діаграма типу „Гистограмма”
Якщо необхідно проаналізувати внесок кожного елемента в загальну суму, то найкраще будувати діаграми типу Круговая.
Кругова діаграма будується тільки для одного ряду даних, причому при побудові кругової діаграми виділяють ряд даних і підписи до них. Кожному елементу даних на круговій діаграмі відповідає сектор, розмір якого залежить від внеску даного значення в загальну суму всіх значень, по яких будується діаграма. На рис. 4.5 наведена кругова діаграма. У параметрах діаграми відмінено виведення легенди й поставлений перемикач на виведення категорії й частки, тому на діаграмі підписані сектори й зазначені відсотки.
Розглянемо ще один тип діаграми – Точечная. Цей тип діаграми використовують, коли необхідно одержати залежність однієї величини від іншої. У нашому прикладі одержимо графік залежності оплати від кількості зібраних яблук. Для побудови цієї діаграми виділяємо два стовпці з даними – Количество і Начислено (грн.). Виберемо тип діаграми – точечная. У параметрах діаграми напишемо заголовки як показано на рис 4.6 і побудуємо діаграму на окремому листі. На діаграмі видно, що оплата росте з ростом кількості. Однак є розкид даних, що пов'язаний з не однаковою оплатою за різні сорти.
Рисунок 4.5 – Кругова діаграма для кількості яблук по сортах
Excel дозволяє за даними на діаграмі побудувати лінію тренда – тобто лінію, що записується математичним рівнянням і щонайкраще наближає дані. Щоб побудувати лінію тренда, виберіть пункт меню Диаграмма/Добавить линию тренда. У вікні діалогу вибираємо вид лінії, тобто яким рівнянням будуть наближатися дані. У нашому прикладі виберемо Линейная, тоді функція, що наближає дані, буде прямою лінією.
На вкладці Параметри поставте прапорець „Показувати рівняння на діаграмі”. Діаграма прийме вид як на рис. 4.6. У правому верхньому куті діаграми розташоване рівняння прямої, що знаходиться на діаграмі. Використовуючи це рівняння можна приблизно підрахувати яка буде оплата для заданої кількості Х зібраних яблук.
4.2 Завдання для самостійної роботи
Для даних зі списку, що ви створили в попередній лабораторній роботі, побудуйте кілька типів діаграм. Дані для діаграм беріть із вихідної таблиці, з підсумкових таблиць і зі зведеної таблиці. У діаграму типу Точечная додайте лінію тренда.
Рисунок 4.6 – Крапкова діаграма з лінією тренда
4.3 Контрольні питання
5 ЛАБОРАТОРНА РОБОТА №5
Тема: Консолідація даних.
5.1 Короткі теоретичні відомості
Консолідація дозволяє обчислювати підсумкові величини для однотипних даних, розташованих на декількох листах таблиці. Припустимо, ми маємо однотипні таблиці про збір яблук за три дні. Одержимо за допомогою консолідації на четвертому листі таблиці підсумкові значення про кількість зібраних яблук і нарахованих грошей за три дні.
Підготуємо спочатку дані для консолідації. Для цього:
Припустимо, що ми одержали три однакових таблиці, які розташовані на відповідних листах Відомість 1, Відомість 2, Відомість 3:
|
Відомість 1 оплати за збір урожаю |
||||
|
№ бригади |
Сорт |
Прізвище |
Кількість |
Нараховано (грн.) |
|
1 |
Джонатан |
Іванов А. А. |
200 |
24 |
|
1 |
Джонатан |
Петров П. П. |
80 |
9,6 |
|
2 |
Джонатан |
Сидоров А. . |
180 |
21,6 |
|
2 |
Джонатан |
Сергєєв В. П. |
170 |
20,4 |
|
1 |
Семеренко |
Ярема С. Р. |
140 |
23,8 |
|
2 |
Семеренко |
Якир Р. Р. |
130 |
22,1 |
|
2 |
Шафран |
Кіров С. С. |
160 |
16 |
Вставляємо новий лист і назвемо його Консолідація за 3 дні. Виділимо на листі Відомість список прізвищ робітників у комірках G2:G9 і скопіюємо його в комірки А1:А8 листа Консолідація за 3 дні. Скопіюємо також на цей лист імена полів, для яких будемо обчислювати підсумки за 3 дні – Кількість і Нарахована (грн.). Виділяємо на листі Консолідація за 3 дні комірки A2:C8 і вибираємо команду меню Дані/Консолідація…Заповнюємо вікно діалогу як показано на рисунку 5.1. У поле Посилання відзначаємо блок для консолідації й клацаємо на кнопці Додати, щоб відзначений діапазон потрапив у список діапазонів консолідації. Ставимо прапорець Використати як імена значення лівого стовпця, тому що в цьому стовпці перебувають прізвища робітників, по яких визначається кількість і заробіток для підсумовування (помітимо, що порядок прізвищ робітників на різних аркушах консолідації не збігається). Ставимо прапорець Створювати зв'язку з вихідними даними, щоб зміни у вихідних таблицях автоматично відображалися в
Рисунок 5.1 - Діалогове вікно «Консолідація»
Результати консолідації представлені на рисунку 5.2. Консолідована таблиця має структурований вигляд. При натисканні на кожній зі знаків «+» видно з яких значень складається підсумок.
Рисунок 5.2 – Результат консолідації
5.2 Завдання для самостійної роботи
Добавити ще два листа з даними для свого варіанту та виконати для них консолідацію. Знайти підсумки для нарахованих сум, встановити зв’язки з даними.
5.3 Контрольні питання
1. Що таке консолідація даних?
2. Які існують підсумкові функції?
3. Як створити структуровану таблицю?