Вінницький державний аграрний університет методичні вказівки
| Вид материала | Документы |
- Україна міністерство аграрної політики україни вінницький державний аграрний університет, 1415kb.
- Міністерство аграрної політики україни вінницький державний аграрний університет, 1439.59kb.
- Міністерство аграрної політики України Вінницький державний аграрний університет, 635.52kb.
- Вінницький державний аграрний університет дипломна робота, 203.57kb.
- Міністерство аграрної політики та продовольства україни державний вищий навчальний, 3279.57kb.
- Міністерство аграрної політики та продовольства україни державний вищий навчальний, 1931.11kb.
- Державний університет "Львівська політехніка" Методичні вказівки, 2030.28kb.
- Навчально-методичний комплекс дисципліни " аграрна політика" для підготовки фахівців, 1320.76kb.
- Міністерство аграрної політики України Луганський національний аграрний університет, 838.75kb.
- Міністерство освіти І науки україни одеський національний політехнічний університет, 224.06kb.
2.2 Завдання
2.2.1 Задача
У сільскогосподарському кооперативі працюють три бригади по шість чоловік у кожній, робітники яких збирають яблука. За 1кг. Зібраних яблук сорту “Семеренко” працівник отримує 12 коп.; “Джонатан” – 10 коп.;”Шафран- 8 коп. Кожний зібраний понад норми кілограм сплачується в подвійному розмірі.
Підрахувати, скільки грошей отримає кожний робітник за зібраний врожай. Визначити максимальну та мінімальну заробітну плату. Обчислити середню платню.
2.2.2 Послідовність дій
На Лист 2 створити таку таблицю:
На Лист 3 створити таблицю:
На Лист1 створити таку таблицю:
Ввести відповідні дані до стовпців A, D, F згідно інформаціі яку надають робітники під час збирання яблук. Наприклад:
В
вести формули в таблицю “Зарплата”:
- в клітинку B3 :=ЛЕВСИМВ(A3;1)
- в C3=ИНДЕКС(Лист2!$B$3:$B$19;ПОИСКПОЗ(A3;Лист2!$A$3:$A$19;))
- в E3=ИНДЕКС(Лист3!$B$3:$B$5;ПОИСКПОЗ(D3;Лист3!$A$3:$A$5;0))
- в G3=ИНДЕКС(Лист3!$C$3:$C$5;ПОИСКПОЗ(D3;Лист3!$A$3:$A$5;0))
- в H3=ЕСЛИ(F3<=ИНДЕКС(Лист3!$D$3:$D$5;ПОИСКПОЗ(D3;Лист3!$A$3:$A$5;0));F3*G3;
ИНДЕКС(Лист3!$D$3:$D$5;ПОИСКПОЗ(D3;Лист3!$A$3:$A$5;0))*G3+(F3-ИНДЕКС(Лист3! $D$3:$D$5;ПОИСКПОЗ(D3;Лист3!$A$3:$A$5;0)))*G3*2)
- використовуючи “ФОРМУ”, заповнити таблицю “Зарплата” відповідними даними. Зверніть увагу на поля, які доступні для введення інформації, а які обчислюються за допомогою раніше введених формул.
Після заповнення даними отримаемо таблицю:
Підрахувати скільки грошей одержить кожний робітник за зібраний врожай, для чого виконати наступні дії.
Опція головного меню Данные – Сортировка, у вікні Сортировка диапазона у рядку Сортировать по вибрати Табельный №.
Опція головного меню Данные – Итоги, у вікні Промежуточные итогои в рядку При каждом изменении в: вибрати Фамилия , в рядку Операція: вибрати Сумма , в рядку Добавить итоги по: вибрати Начислено , Ok.
Для відміни групування записів – опція головного меню Данные – Итоги – Убрать все .
Для визначення мінімальної та максимальної заробітної платні виконати слідуючи дії.
Виділити осередок за межами таблиці і ввести в нього текст “Максимальна заробітна платня”, перейти в осередок поруч зправа від введеного тексту і натиснути кнопку
Майстер функцій на стандартній панелі інструментів. У вікні майстра вибрати функцію МАКС із категорії Статистические, у вікна діалогу цієї функції вибрати адреси осередків, з яких повинно бути вибрано максимальне значення, а саме – узагальнені нараховані суми заробітної платні. Цю саму операцію повторити для находження мінімальної заробітної платні, використовуючи функцію МИН.3 ПРАКТИЧНА РОБОТА №3
ТЕМА: Ділова графіка в Excel та її використання.
3.1 Теоретичні відомості
Діаграми в Microsoft Excel створюються на підставі даних, що розміщені на листі. В разі зміни даних діаграми змінюються також. Для створення діаграми найзручніше використовувати Майстер Діаграм та виконувати його вказівки. Діаграму можна створювати на окремому листі Робочої книги, а можна вставляти до потрібного листа певної книги Excel.
3.1.1 Створення діаграми
Побудуйте діаграму для наступних даних.
Для цього спочатку виділіть мишкою числові дані та підписи до них (А4:Е9). Це не обов’язково, але таким чином можна прискорити процес побудови діаграми.
Кликніть по кнопці Майстер діаграм, яка розташована на панелі інструментів, та виконуйте інструкції Майстра.Побудова діаграми складається з 4 -х етапів.
Етап перший – Тип діаграми
На цьому етапі необхідно обрати один з можливих типів діаграм. Спочатку оберіть його у вікні Тип. Після вибору якогось типу в правому вікні Вид з’являється зображення різних видів діаграм цього типу. Натисніть на ліву кнопку мишки, обираючи бажаний вид діаграми, а потім натисніть на кнопку “Далее”. Після цього почнеться другий етап. Якщо Ви побажаєте змінити тип діаграми, то можна буде повернутися до попереднього етапу, натиснувши кнопку “Назад”.
Етап другий – Джерело даних (источник данных).
Якщо перед викликом Майстра діаграм, Ви виділили дані для побудови діаграми, то в полі Діапазон вже записані їх адреси. Натисніть на кнопку Згорнути/Розгорнути в цьому полі. Вікно Майстра щезне і можна буде перевірити або змінити виділений діапазон, орієнтуючись на зображену діаграму.
Оберіть один з перемикачів Ряды в строках або Ряды в столбцах. Подивіться, як змінюється при цьому зовнішній вигляд діаграми. Вкладка Ряд дозволяє видалити ряд даних або добавити новий. Можна задати підписи для рядів по осям, причому можна у відповідному полі вказати адресу клітини з потрібним текстом, а можна просто ввести текст.
Етап третій – Параметри діаграми.
Зараз можна визначити Заголовки (назву діаграми та її осей), Лінії сітки (основні та проміжні), Підписи даних (обрати їх вигляд або не підписувати дані зовсім), можна додати до діаграми Таблицю початкових даних та Легенду, вказати місце її розташування.
Етап четвертий – Розміщення діаграми.
Оберіть лист, на якому буде розташована діаграма, та натисніть кнопку “Готово”.
3.1.2 Зміна зовнішнього вигляду діаграми
Майстер діаграм можна використовувати й для зміни існуючих діаграм. Для цього треба спочатку виділити діаграму, тоді в меню з’явиться пункт Діаграма, в якому можна обрати будь-яку з його команд.
Можливості для внесення змін до діаграми надає й панель інструментів Діаграми, яка з’являється після виділення діаграми. Якщо ця панель відсутня, її можна добавити за допомогою пункту меню Вид/Панель інструментів/Діаграми або натиснути на ліву кнопку мишки, коли вона знаходиться на будь-якій панелі інструментів, і обрати пункт Діаграми.
Щоб виділити будь-який елемент діаграми, достатньо натиснути на ліву кнопку мишки, коли вона знаходиться на ньому, або використати список Елементи діаграми панелі інструментів Діаграми. Його можна розкрити та обрати потрібний елемент. Якщо двічі натиснути на ліву кнопку мишки на якомусь елементі діаграми, то активізується сам елемент, та ще й вікно Формат для обраного елемента. Вкладки цього вікна містять параметри, що характеризують виділений об’єкт.
3.1.3 Зміна формату виділеного елемента
Виділіть Область построения диаграммы, а в ній який-небудь ряд. Потім оберіть кнопку Формат рядів даних та змініть колір елементів ряду. За допомогою вкладки Порядок рядків змініть їх порядок, спробуйте обрати різні варіанти вкладки Підписи даних. Виділіть мишкою найбільший елемент ряду та змініть колір тільки цього елемента.
Виділіть ось ОY, на вкладці Шрифт змініть розмір та колір шрифту, на вкладці Шкала змініть ціну поділки, за допомогою вкладки Выравнивание змініть орієнтацію надписів, додайте Таблицю даних.
3.1.4 Добавка та зміна тексту
На панелі інструментів Рисование натисніть кнопку Надпис. Виділіть мишкою область потрібного розміру в обраному місці і введіть необхідний текст, наприклад, заголовок діаграми. (Якщо панель Рисование відсутня, її можна добавити, обравши в меню Вид пункт Панелі інструментів, а в ньому пункт Рисование).
Для зміни існуючого тексту достатньо клікнути на ньому мишкою і зробити необхідні зміни в полі, що утворилося.
3.1.5 Добавка нових даних
Виділіть діаграму, а потім оберіть в меню Діаграма пункт Исходные данные, вкладку Ряд, кнопку Добавить. Тепер у вікні Имя вкажіть ім’я цього ряду, наприклад, Итого, або його адресу (А10). У вікні Значения вкажіть адреси клітин нового ряду (В10:Е10). Найпростіше зробити це за допомогою мишки, згорнувши вікно та виділивши дані в таблиці Excel.
3.1.6 Побудова ліній тренду
Лінії тренду будуються за допомогою регресіонного аналізу даних і дозволяють передбачити дані майбутніх періодів або дані, яких не вистачає. Лінії тренду дозволяють побачити “згладжені” дані. Вони можуть використовувати одну з таких математичних моделей: лінійну, поліноміальну, логарифмічну, експоненціальну або степеневу.
Для побудови ліній тренду необхідно виконати такі дії:
- виділити один з рядів даних;
- обрати пункт “ Добавить линию тренда”в меню Діаграма або в контекстному меню, яке з’являється після натискання правої кнопки мишки;
- на вкладці Тип оберіть який-небудь тип, наприклад, “Полиномиальная 4 степени”;
- на вкладці Параметри поставте позначку на текст ”показывать уравнение на диаграмме“ та замовте ”прогноз вперед на 1 периодов“;
- натисніть кнопку ОК.
Лінія тренду з’явиться на екрані.
Аналогічно побудуйте лінію тренду для функції іншого виду та для іншого ряду даних. Для видалення лінії тренду необхідно її виділити та натиснути клавішу Delete.
3.2 Контрольні запитання
3.2.1 Які етапи побудови діаграм Ви знаєте?
3.2.2 З яких елементів складається діаграма?
3.2.3 Наведіть приклади деяких видів діаграм.
3.3.4 Що таке лінія тренду і як її побудувати?
3.3.5 Як виділити діаграму або якийсь конкретний елемент?
3.3.6 Як змінити розмір діаграми та її розташування?
3.3.7 Як коригувати зовнішній вигляд діаграми?
3.3 Індивідуальні завдання
3.3.1 Скласти таблицю, в якій зазначити рекламодавців на каналі 1+1 з кількістю рекламних роликів за 1 тиждень. Розрахувати суму затрачених коштів на рекламу та побудувати кругову діаграму для кожного рекламодавця від загальних витрат.
| Рекламо-давець | Категорія реклами | Затрати | Підсумок | % до загальних витрат | ||
| Час, хв | Кількість | Ціна хв | ||||
3.3.2 Скласти таблицю товарного експорту та імпорту в Україні (по регіонам) за 2000р. Побудувати гістограму.
| Назва регіону | Експорт | Імпорт | % до підсумку | ||
| Вартість | % до підсумку | Вартість | % до підсумку | ||
3.3.3 Скласти таблицю послуг різних нотаріальних контор. Розрахувати прибуток. Побудувати кругову діаграму прибутку нотаріальних контор в долях від загальної.
| Назва нотаріальної контори | Вид послуг | Кількість наданих послуг | Вартість послуги | Прибуток по кожній послузі |
3.3.4 Скласти таблицю прибутків різних фірм міста. Розрахувати загальний прибуток кожної фірми. Побудувати кругову діаграму прибутку нотаріальних контор в долях від загальної.
| Назва фірми | Укладені угоди | Загальний прибуток | % прибутку фірми | ||
| Категорія | Кількість | Середній прибуток по кожній | |||
3.3.5 Розрахувати відсоткове співвідношення розглянутих справ та позовів. Побудувати гістограму.
| № п/п | Назва суду | Судові позови | % розглянутих справ від кількості судових позовів по місту | ||
| Кількість | Розглянуто до справ | % розглянутих справ | |||
3.3.6 Скласти таблицю. Розрахувати загальну кількість наданих послуг від юридичних та фізичних осіб на протязі 1-ї декади місяця. Побудувати гістограму.
| Назва нотаріальної контори | День | Кількість звернень від юрид. осіб | Кількість звернень від фіз. осіб | Загаьна кількість наданих послуг |
3.3.7 Побудувати таблицю прибтків нотаріальної контори за певний період. Розрахувати прибуток, який отримує контора окремо по кожній послузі. Побудувати кругову діаграму за видами послуг.
| Назва послуги | Вартість однієї виконаної послуги | Кількість наданих послуг на рік | Прибуток від кожного виду послуг |
3.3.8 Скласти таблицю прибутків приватних нотаріусів окремо по кожному району міста. Розрахувати суму находжень до бюджету, якщо податок складае 20% від прибутку. Побудувати гістограму для різних районів міста.
| Назва району | Кількість приватних нотаріусів | Середній прибуток нотаріуса | Сума находжень у вигляді податку |
3.3.9 Побудуйте таблицю даних про збут комп’ютерів однією комп’ютерною фірмою. Розрахуйте вартість по кожному покупцю і загальний прибуток фірми. Побудуйте графік продаж впродовж місяцю. Побудувати гістограму.
| Дата | Покупець | Назва товару | Кількість | Місто | Ціна | Вартість |
3.3.10 Побудувати таблицю облікових даних про продаж товарів у вересні 2000р. Побудувати гістограму реалізації товарів на протязі місяця.
| Дата | № документа | Продавець | Товар | Вартість | ||
| Назва | Кількість | Ціна | ||||
3.3.11 Побудувати таблицю даних про продаж товарів на ринку різними фірмами. Розрахуйте, яку частину ринку займає кожна окрема фірма в % до загальної їх кількості. Побудувати кругову діаграмую.
| Виробник | Назва продукції | Обсяги продажів, грн. | Частка ринку | |
| Кількість | Ціна, грн. | |||
3.3.12 Побудувати таблицю забеспеченості населення товаром за певний період. Обчислити забеспеченість у грошовому виразі на 1ч. кожного району. Побудувати гістограму забеспечення населення товаром для районів міста.
| Район | Кількість мешканців | Товари першої необхідності | Забезпеченість населення товаром, на 1 чол. | ||
| Назва | Кількість | Ціна | |||
3.3.13 Скласти звіт про роботу суду. Пободувати кругову діаграму розглянутих справ кожного судді.
| Призвище судді | Кількість позовів | Розглянуті справи | У судовому розгляді відмовлення | ||
| Кількість | % до кільк. позовів | Кількість | % до кільк. позовів | ||
3.3.14 Побудувати таблицю, яка б показувала прибуток періодичних видань Запоріжжя від друку реклами. Побудувати гістограму.
| Назва періодичного видання | Ціна одного рекламного оголошення | Кількість оголошень у одному номері | Витрати на видавництво видання | Прибуток від реклами |
3.3.15 Скласти таблицю прибутковості виробництва товарів заводом, окремо для кожного цеху. Побудувати кргову діаграму вкладу кожного цеху в загальний прибуток заводу.
| Назва цеху | Вид продукції | Собівартість виробництва одиниці продукції | Ринкова ціна продукту | Прибуток від виробництва |
4 ПРАКТИЧНА РОБОТА № 4
ТЕМА: Консолідація таблиць у Excel
4.1 Завдання
4.1.1 Введіть таку таблицю і виконайте всі необхідні обчислення.
Таблиця 4.1
| А  НАЛІЗ ПОПИТУ і ПРОДАЖІВ Продукції торгової фірми «Шанс» | |||||||
| Найменування | Ціна | Попит, | Пропо-зиція, | Продаж | Виторг | ||
| продукції | за один. (дол.) | шт. | шт. | Безго-тівк. | Готів. | усього | від продаж. |
| Телевізори Відеомагнітофони Програвачі Муз.центри Аудіоплеєри Відеокамери Відеоплеєри | 350,3 320 400,5 750 40 974,8 198,1 | 13 70 65 15 23 12 155 | 16 65 134 15 23 44 234 | 5 30 40 7 2 16 15 | 7 35 26 8 16 14 43 | | |
| Разом | | | | | | | |
4.1.2 Вставте такі три додаткові рядки в таблицю:
Таблиця 4.2
| Найменування | Ціна | Попит | Пропоз. | Продаж | |
| безготівк | готівк. | ||||
| Радіотелефони Моноблоки Відеокасети | 390,50 547,0 3,47 | 16 8 180 | 18 20 240 | 8 2 160 | 6 6 16 |
Для цього клацніть по заголовку рядка 7 і протаскайте покажчик миші униз, захопивши 8-й і 9-й рядки, виберіть Вставка/Строка, введіть нову інформацію в порожні рядки, що з’явилися, і скопіюйте формули в клітини G7:G9 і Н7:Н9. Вставка порожніх стовпчиків здійснюється аналогічно.
Припустимо, що вам необхідно цілком видалити 8-й рядок. Для видалення рядка необхідно виділити 8-й рядок, клацнувши мишею по заголовку рядка (цифра 8), вибрати Правка/Удалить і зняти виділення рядка. Стовпчики ЕТ видаляються аналогічно.
4.1.3 Перед Вами постає задача аналізу роботи фірми «Шанс» за кожний місяць першого кварталу і створення звітної таблиці з підсумковими показниками. Таким чином, для аналізу діяльності фірми «Шанс» знадобляться ще три таблиці: за лютий і березень із структурою, що аналогічна наведеній, і зведена таблиця з новою структурою.
Щоб не займатися стомливою роботою по введенню в таблицю великої кількості нових даних, приймемо таке допущення: будемо вважати, що в таблицях за лютий і березень усі дані залишилися незмінними, крім ціни продукції (стовпчик В).
4.1.4 Відкриємо нову робочу книгу для того, щоб згодом об’єднати всі таблиці в ній. Для цього виберіть Файл/Создать (або клацніть кнопку Создать книгу) і натисніть ОК. Ехсеl відчинить робочу книгу з ім'ям Книга2. Якщо ви до цього вже пробували створити нову робочу книгу, Ехсеl запропонує для неї наступний номер: Книга3, Книга4 т.д. Для зручності одночасної роботи з декількома книгами упорядкуємо вікна на екрані. Для цього виберіть Окно/Расположить і встановіть прапорець Рядом. Натисніть ОК.
4.1.5 Далі, скопіюємо Таблицу1 на Лист1, Лист2 і Лист3 робочої книги Книга2. Для цього активізуйте вікно Таблица1, клацнувши в ньому. Виділить для копіювання вашу електронну таблицю. Натисніть на праву кнопку миші й у контекстному меню виберіть Копировать. Перейдіть до Книги2 на Лист1 в клітину А1, клацніть правою кнопкою миші й у контекстному меню виберіть Вставить. Повторіть ці дії для Листа2 і Листа3 у Книге2. Відформатуйте отримані таблиці на зразок Таблиці 1.
4.1.6 Для більш зручної орієнтації усередині робочої книги перейменуємо листи, давши їм імена: Січень, Лютий і Березень. Для цього двічі клацніть по ярличку Лист1. У діалоговому вікні Ім'я листа введіть: Січень. Натисніть кнопку ОК. Лист1 одержав нову назву. Повторіть ці операції для Листа2 і Листа3, перейменувавши листи в Лютий і Березень відповідно. Після виконаної роботи стало зручніше звертатися до листів робочої книги.
4.1.7 Нарешті, відредагуємо заголовки таблиць і зміст стовпчиків В за лютий і березень у відповідності з такими даними (табл. 4.3).
Таблиця 4.3 - Вихідні дані за лютий
-
Найменування продукції
Ціна за одиницю
лютий
березень
Телевізори
Відеомагнітофони
Радіотелефони
Відеокасети
Програвачі
Муз. центри
Аудиоплейєри
Відеокамери
Відеоплеєри
430,5
400,7
698,0
5,44
500,4
890,0
100,5
1007,8
1954,0
588,9
690,3
700,0
6,88
800,65
1090,0
200,6
1567,99
2067,0
4.1.8 Збережемо робочу книгу Книга2 на диску з ім’ям Квартал1. Для цього виберіть Файл/Сохранить как. У полі Имя файла введіть Квартал1. У списку Тип файла виберіть «Робоча книга». Тепер у робочій книзі Квартал1 підшиті три таблиці про діяльність фірми «Шанс» за січень, лютий і березень.
4.1.9 Об'єднання і зв'язування електронних таблиць в Ехсеl
Microsoft Ехсеl надає можливість об’єднувати і зв’язувати робочі листи або файли електронних таблиць таким чином, щоб у наслідок введення значення до однієї таблиці змінювався вміст іншої таблиці, а виконання обчислень в одному файлі призводило б, наприклад, до видачі повідомлення в іншому.
При об’єднанні і зв’язуванні таблиць використовується абсолютна адресація. Ознакою абсолютної адреси є $ (знак долара). У залежності від позиції цього знака в адресі клітини відповідна область даних не модифікується. Наприклад: $А$1, $А1, А$1.
Об’єднувати листи робочої книги можна:
- підсумовуванням змісту клітин відповідних робочих листів із використанням функції Автосуммирование;
- консолідацією електронних таблиць;
- створенням зведених таблиць за допомогою Майстра зведених таблиць.
4.1.10 Розглянемо можливості консолідації ЕТ. Для цього створимо зведену таблицю для аналізу даних за перший квартал, що зв’язує показники продажу та виторги за січень, лютий і березень (табл. 4.4).
Таблиця 4.4 - Зведені показники за перший квартал
| Найменування продукції | Продано, шт. | Виторг за квартал |
| ... ... ... РАЗОМ | ? ? ? ? | ? ? ? ? |
Розмістимо цю таблицю на четвертому листі, давши йому ім’я Квартал1. Введемо заголовок таблиці і заголовки нових граф у стовпчики В і С, а потім скопіюємо зміст стовпчика А з листа Березень.
Консолідація дозволяє об’єднувати дані з областей-джерел і виводити їх в область призначення. При консолідації даних можуть використовуватися різноманітні функції: підсумовування, розрахунку середнього арифметичного, підрахунків максимальних і мінімальних значень и т п. Крім того, при консолідації можна створювати зв’язок з вихідними даними в областях-джерелах, при цьому область призначення буде автоматично поновлюватися у разі внесення змін в областях-джерелах.
4.1.11 Перевіримо, як працює консолідація в Ехсеl у двох випадках: без створення зв’язків із вихідними даними і з їхнім створенням.
Консолідуємо дані зі стовпчиків G і Н за три місяця першого кварталу, не створюючи зв’язків із вихідними даними. Для рішення цієї задачі виділіть область призначення – на Листі Квартал1 клітини В5:С14 або тільки В5, виберіть Данные/Консолидация, у вікні Функция виберіть функцію Сумма. Потім у полі Ссылка визначте області-джерела, які потрібно консолідувати. Для цього перейдіть на лист Січень і виділіть клітини G5:Н14. Потім у вікні Консолідація натисніть кнопку Добавить. Зверніть увагу, як сформувалося посилання на виділену область:
Січень!$G$5:$Н$14
Ехсel використовує тут абсолютні посилання на клітини.
4.1.12 Потім перейдіть на лист Лютий і виділить клітини G5:H14. У вікні Консолідація натисніть кнопку Добавить. Перейдіть на лист Березень і виділить клітини G5:Н14. Перевірте, чи знятий прапорець Создавать связи с выходными даннями. Натисніть кнопку ОК. Ваша зведена таблиця має такий вигляд (табл 4.5).
При консолідації без установлення зв’язків зміна даних у таблицях-джерелах не призводить до відновлення даних у підсумковій таблиці, тому у разі внесення змін до таблиці-джерела консолідацію необхідно повторити.
Таблиця 4.5 - Зведена таблиця
| Найменування продукції | Продано, шт. | Виторг за квартал |
| Телевізори Відеомагнітофони Радіотелефони Відеокасети Програвач Музичні центри Аудіоплеєри Відеокамери Відеоплеєри РАЗОМ | 36 195 42 528 198 45 54 90 174 1362 | 16437 91715 25039 2779,04 112302,3 40950 6139,8 106517,7 244707 8 646587,64 |
4.1.13 Тепер консолідуємо дані зі стовпчиків G і Н за три місяця 1 кварталу, створивши зв’язок із вихідними даними. Для рішення даної задачі скопіюйте заголовок, шапку таблиці і вихідні дані стовпчика А з Листа4 на Лист5. Для консолідації виділить область призначення - клітини В5:С14 на Листі5. Виберіть Данные/Консолидация, у вікні Функция виберіть функцію Сумма, у полі Ссылка визначите області-джерела, що потрібно консолідувати, тобто посилання на клітини G5:Н14 усіх листів за аналогією з попередньою задачею. Потім установить прапорець Создавать связи с выходными данными. Натисніть ОК.
Зверніть увагу на отриману таблицю. Її результати такі ж самі, як і на Листі4. Проте, змінився вигляд екрану: у його лівій вертикальній частині з’явилися символи структури документу і деякі рядки стали невидимими.
4.1.14 Робота зі структурованою таблицею
Структурування даних робочих листів використовується в Ехсel при створенні підсумкових звітів. Воно дозволяє показувати або приховувати рівні структурованих даних, виводячи на екран дані з більшими або меншими подробицями.
Звернемося до отриманої таблиці і познайомиємося із символами структури. Їх два типи: кнопки з номерами рівнів (у вашій таблиці це кнопки 1 і 2, що знаходяться в лівому верхньому куточку екрану) і знаки + (плюс) і/або - (мінус), що дозволяють відповідно розкривати або приховувати деталі структурованого документа.
4.1.15 Клацніть на кнопці 2. Ваша підсумкова таблиця “розкрилася”, надавши можливість пересвідчитись, що консолідуються дані за три місяці. Клацнувши по кнопці 1, ви зможете сховати вихідні дані з таблиць-джерел. Перевірте це на своїй таблиці. Клацніть по якому-небудь зі значків + (плюс). Результатом буде відкриття однієї зі складової підсумкової таблиці. Клацнувши по значку - (мінус), ви сховаєте вихідні дані з таблиць-джерел.
4.1.16 При консолідації даних із створенням зв’язків Ехсel автоматично коректує підсумкові дані при зміні їх у таблицях-джерелах. Внесіть які-небудь зміни в стовпчики В, Е або F таблиць-джерела і перевірте, як змінилася підсумкова таблиця.
4.2 Контрольні запитання
4.2.1 Що таке робоча книга і робочий лист? Як дати нове ім.’я робочому листу?
4.2.2 Як видалити лист робочої книги?
4.2.3 Якими засобами можна об’єднати листи робочої книги?
4.2.4 Що таке область-джерело й область-призначення? Як правильно їх задавати?
4.2.5 Що таке консолідація даних і які засоби консолідації Вам відомі?
4.2.6 Які підсумкові функції Ви знаєте?
4.2.7 Що таке структурована таблиця і як із нею працювати?
5 ПРАКТИЧНА РОБОТА № 5
ТЕМА: Опрацювання даних у Excel, сортування даних,
фільтрація даних у списку, формування підсумків
5.1 Стислі теоретичні відомості
Використання засобів сортування, фільтрація і формування підсумків потребує уявлення електронної таблиці у виді бази даних або списку.
5.1.1 Уявлення ЕТ у виді списку
С
писок - це один із засобів організації даних на робочому листі. Список створюється як позначена область, що складається з рядків таблиці зі зв’язаними даними. Перший рядок містить назви стовпців. Дані в кожному стовпчику мусять бути однотипними. Наприклад, перелік співробітників деякого відділу, у якому стовпчики мають відповідно такі імена: Ф.И.О. , Кол-во иждивенцев, Всего начислено, Всего удержано, Сумма к выдаче, - являє собою список даних. Дані, організовані в список, у термінології Ехсеl називаються базою даних (БД). При цьому рядки таблиці - це записи бази даних, а стовпчики - поля БД.
Щоб перетворити таблицю Ехсel у список, необхідно надати стовпчикам імена, які будуть використовуватися як імена полів бази даних. Варто мати на увазі, що імена стовпчиків можуть складатися з декількох рядків заголовків, розміщених в одному рядку таблиці Ехcel, як це зроблено на рис. 5.1.
Створюючи список на робочому листі Ехсеl необхідно дотримуватись певних правил:
- на одному робочому листі не варто поміщати більш одного списку, оскільки деякі операції, наприклад, фільтрація, не працюють водночас з кількома списками;
- варто відокремлювати список від інших даних робочого листа хоча б одним незаповненим стовпчиком або одним незаповненим рядком. Це допоможе Ехсеl автоматично виділити список при виконанні фільтрації або при сортуванні даних;
- список може займати весь робочий лист: 16384 рядки і 256 стовпчиків;
- імена стовпчиків повинні розташовуватися в першому рядку списку. Ехсеl використовує ці імена при створенні звітів, для пошуку і сортування даних;
- для імен стовпчиків варто використовувати шрифт, тип даних, вирівнювання, формат, рамку або стиль букв, відмінні від тих, що використовувалися для даних списку;
- щоб відокремити імена стовпчиків від даних, варто розмістити рамку по нижньому краю клітин рядка з іменами стовпчиків. Не рекомендується використовувати порожні рядки або пунктирні лінії.
5.1.2 Сортування даних
Список можна відсортувати за алфавітом, за значенням або в хронологічному порядку відповідно до даних конкретного поля. Щоб відсортувати весь список, достатньо виділити одину клітину і вибрати в меню Данные команду Сортировка. Ехсеl автоматично виділить увесь список. Якщо в першому рядку списку знаходяться імена полів, то вони не будуть сортуватися. Але майте на увазі, що в цьому випадку підсумковий рядок списку також буде включено до сортування, тому більш доцільно самостійно виділяти необхідну область вихідного списку для сортування.
Команда Сортировка здійснюється також і через діалогове вікно. У вікні Сортировка в трьох полях введення можна задати ключі, за якими буде виконане сортування. У першому полі (у списку) необхідно вибрати стовпчик, за яким Ехсеl повинен відсортувати дані. Наприклад, дані зведеної відомості можна відсортувати по стовпчику Кол-во иждивенцев. В другому полі діалогового вікна Сортировка можна задати інший ключ сортування, наприклад Ф.И.0. Сортування для третього ключа виконується аналогічно.
У діалоговому вікні Сортировка існує режим Параметры. Він дозволяє встановити порядок сортування по першому ключу - звичайний або вказаний користувачем, задати врахування великих та малих літер (урахування регістру символів), а також напрямок сортування - за зростанням або за спаданням.
На панелі інструментів Стандартная знаходяться дві кнопки для швидкого сортування: “Сортувати по зростанню”, та “Сортувати по спаданню”. Ключем сортування в цьому випадку є стовпчик із активною клітиною.
5.1.3 Фільтрація даних у списку
За допомогою фільтрів можна виводити та переглядати тільки ті дані, що задовольняють визначеним умовам. Ехсel дозволяє швидко і зручно переглядати необхідні дані зі списку за допомогою простого засобу - Автофільтру. Складніші запити до бази даних можна реалізувати за допомогою команди Расширенный фильтр.
5.1.4 Автофільтр
Щоб використовувати автофільтр, треба спочатку виділити для пошуку область списку із заголовками полів. Потім виконати команду Автофильтр в меню Данные. Після вибору пункту Автофильтр Ехсel розташовує списки, що розкриваються, безпосередньо до відповідних імен стовпчиків списку. Клацнувши по стрілці, можна вивести на екран список всіх унікальних елементів відповідного стовпчика. Якщо виділити деякий елемент стовпчика, то будуть сховані всі рядки, крім тих, що містять виділене значення. Наприклад, якщо вибрати значення поля Кол-во иждивенцев рівним 1, то будуть обрані тільки ті співробітники, що мають одного утриманця.
Елемент стовпчика, який виділений у списку, що розкривається, називається критерієм фільтра. Можна продовжити фільтрацію списку за допомогою критерію з іншого стовпчика. Наприклад, якщо після виділення елемента «1» у списку для поля Кол-во иждивенцев вібрати у списку Всего начислено значення «500», то на екран буде виведений тільки один рядок для співробітника Сидорова.
Щоб видалити критерії фільтра для окремого стовпчика, треба вибрати параметр Все в списку, що розкривається. Щоб показати всі сховані в списку рядки, треба вибрати в меню Данные команду Фильтр, а потім - команду Отобразить все.
За допомогою автофільтра можна для кожного стовпчика задати потрібні критерії відбору записів, наприклад, вивести на екран тільки ті записи, значення полів яких знаходяться в межах заданого інтервалу. Щоб задати необхідний критерій, треба в списку, що розкривається, вибрати пункт (Условие...), а потім у діалоговому вікні Пользовательский автофильтр ввести потрібні критерії.
5.1.5 Складна фільтрація
Для фільтрації списку або бази даних за складним критерієм, що буде визначений нижче, а також для одержання частини наданого списку з декількома потрібними стовпцями, в Ехсel використовується команда Расширенный фильтр меню Данные. Відмінність цієї команди від команди Автофильтр полягає в тому, що, крім перелічених вище можливостей, відфільтровані записи можна винести в інше місце робочого листа Ехсel, не зіпсувавши наданий список.
Щоб використовувати команду Расширенный фільтр, треба спочатку створити таблицю критеріїв, яку варто розмістити на тому ж робочому листі, що і первісний список, але так, щоб не приховувати лист під час фільтрації. Для формування таблиці критеріїв необхідно скопіювати імена полів списку в ту частину робочого листа, де буде розташовуватися таблиця критеріїв. При цьому кількість рядків цієї таблиці визначається тільки кількістю критеріїв пошуку. Наявність порожніх рядків у таблиці критеріїв неприпустима, оскільки в цьому випадку будуть знайдені всі записи списку.
Завдання критеріїв пошуку у виді констант потребує точної копії імен тих стовпчиків списку, що задають умови фільтрації. Наприклад, для фільтрації списку “Сводная ведомость за 1 квартал” із метою відбору записів співробітників, у яких “Сумма к выдаче” більше 400, треба створити таку таблицю критеріїв:
Сумма к выдаче
>400
Якщо необхідно одержати список співробітників, у яких “Сумма к выдаче” знаходиться в діапазоні від 200 до 400 , то в таблиці критеріїв кожна умова повинна бути задана окремо, але в одному рядку, тому що вони пов’язані оператором И. Таким чином, таблиця критеріїв матиме вигляд:
Сумма к выдаче Сумма к выдаче
>200 <400
Крім таблиці критеріїв, для команди Расширенный фильтр треба визначити, як повинен виглядати результат. Це означає, що слід скопіювати у вільне місце робочого листа імена тільки тих полів списку, які визначають вигляд вихідного документу. Наприклад, для таблиці “Сводная ведомость за 1 квартал” необхідно одержати список співробітників, у яких Сумма к выдаче знаходиться в заданому вище діапазоні. Тоді заголовок вихідного документу повинен мати такий вигляд:
Ф.И.0. Сумма к выдаче
Кількість рядків у вихідному документі Ехсel визначить самостійно. Таким чином, для виконання команди Расширенный фильтр треба виконати три дії:
- сформувати у вільному місці робочого листа таблицю критеріїв;
- сформувати шапку вихідного документу;
- виділити область пошуку в первісному списку.
Для описаного вище приклада підготовлені для фільтрації таблиця критеріїв і заголовок вихідного документу, а також список, що фільтрується, приведені на рис. 5.2.
Тепер можна запускати команду Расширенный фильтр, яка виведе на екран діалогове вікно. У діалоговому вікні треба задати Исходный диапазон, тобто область знаходження тієї частини списку, яку треба відфільтровати. Якщо перед запуском команди Расширенный фильтр область пошуку була виділена, то в діалоговому вікні Исходный диапазон буде заданий (рис. 5.3).
Рисунок 5.3 - Заповнене діалогове вікно Расширенный фильтр
Далі треба визначити Диапазон условий, задавши область знаходження таблиці критеріїв, до якої обов’язково входять імена стовпчиків таблиці. В області діалогового вікна Обработка треба зазначити, буде фільтрація виконуватися на місці або результат буде записаний в іншій області робочого листа. Якщо був обраний режим - «фильтровать список на месте», то Ехсеl сховає всі рядки початкового списку, які не задовольняють заданим критеріям. Якщо встановлений перемикач «Только уникальные записи», то однакові рядки списку не будуть показані в області вихідних даних. Якщо умови пошуку задані в таблиці критеріїв в одному рядку, то ці умови зв’язані оператором И, якщо ж умови пошуку задані в різних рядках, то вони пов’язані оператором ИЛИ.
Виконайте фільтрацію й перевірте правильність відбору даних.
5.1.6 Використання критерію, що обчислюється
Використання критеріїв пошуку, що обчислюються, в таблиці критеріїв потребує виконання таких правил щодо формул:
- формула повинна виводити логічне значення Істина або Хибність (Лож). Після виконання пошуку на екран виводяться тільки ті рядки, для яких результатом обчислення формули буде Істина;
- формула повинна посилатися хоча б на один стовпчик у списку;
- формула, використана в критерії, повинна посилатися або на заголовок стовпця, або на відповідне поле першого запису списку (в наведеному нижче прикладі це може бути або “Сумма к выдаче”, або Е4, або Е5).
Створимо таблицю умов, у якій задається критерій, що обчислюється. Наприклад,треба здійснити пошук тих записів, де розмір Суммы к выдаче перевищує середнє значення цієї суми за квартал.
Формула для критерію обчислюється за допомогою функції СРЗНАЧ, а саме:
=Е5>СРЗНАЧ($Е$5:$Е$11)
Р
езультат наведений на рис. 5.4.
Використання критерію, що обчислюється, накладає обмеження на таблицю критеріїв. У цьому випадку ім’я стовпчика в таблиці критеріїв, який містить значення критерію, що обчислюється, повинно відрізнятися від імені подібного стовпчика у початкових даних. Тому, в наведеному нижче прикладі, ім’я поля Сумма к выдаче в таблиці критеріїв одержало ім’я Выдача.
Виконайте зазначену фільтрацію і порівняйте отримані результати.
Рисунок 5.4 - Результат фільтрації з використанням критерію, що обчислюється
5.1.7 Робота з функціями бази даних
Microsoft Excel пропонує 14 функцій для роботи зі списками. Кожна з цих функцій повертає інформацію про елементи списку, які задовольняють деяким умовам.
Функція СЧЕТЕСЛИ(діапазон;критерій) підраховує кількість елементів у діапазоні, які задовольняють критерію.
Діапазон записується у вигляді блока, критерій записується у виді текстового рядка, що містить умови. Наприклад, щоб підрахувати кількість співробітників, у яких кількість утриманців більше одного, треба записати у вільному місці таблиці формулу:
=СЧЕТЕСЛИ(B4:B10;”>1”)
Функція СУММЕСЛИ(діапазон;критерій;діапазон_підсумовування)
підсумовує значення в зазначеному діапазоні, з огляду тільки на ті записи, що задовольняють критерію. Наприклад, щоб підрахувати сумарну зарплату співробітників, які мають більше одного утриманця, треба записати у вільному місці таблиці формулу:
=СУММЕСЛИ(B4:B10;”>1”;C4:C10)
Інші функції, орієнтовані на роботу зі списками, мають узагальнену назву БДфункції. На відміну від двох наведених вище функцій, вони потребують створення блока критеріїв, як при роботі з розширеним фільтром.
Функція ДСРЗНАЧ(діапазон_списку;поле;блок_критеріїв) - обчислює середнє значення у вказаному полі серед тих записів, що задовольняють умові, записаній в блоці критеріїв. Наприклад, щоб обчислити середню зарплату співробітників, які мають більш одного утриманця, створимо блок критеріїв в клітинах G3:G4, записавши в G3 - Кол-во иждивенцев, а в G4 - >1. Відповідь дасть формула:
=ДСРЗНАЧ(F3:E10;”Всего начислено”;G3:G4)
Ознайомтесь з іншими функціями обробки бази даних самостійно.
Ви можете використовувати команду Данные/Проверка для того, щоб значення, що вводяться в список, задовольняли деяким умовам. У вікні діалогу Проверка вводимых значений на вкладці Параметры зазначте тип та інтервал значень, що дозволяється вводити. Вкладка Сообщение для ввода дозволяє створити підказування користувачу про те, які дані можна вводити. На вкладці Сообщение об ошибке можна ввести повідомлення, яке буде з’являтися на екрані, якщо введено некоректні дані. Задайте перевірку для даних із списку і перевірте як вона працює.
5.1.8 Формування підсумків в електронній таблиці
Команда Итоги з меню Данные вибирає фрагмент списку, застосовує функцію типу СУММ до виділених даних, а потім відображає результати. Щоб команда Итоги працювала, список треба спочатку відсортувати. Після сортування можна підрахувати підсумки по групах записів. Команда Итоги дозволяє підводити підсумки більш ніж по одному полю з групи записів, а також додавати оброблювані поля та застосовувати для обчислення результатів інші функції.
5.2 Контрольні запитання
5.2.1 Яка частина таблиці може бути списком? Чим список (база даних) відрізняється від електронної таблиці?
5.2.2 Поясніть різницю в можливостях сортування за допомогою кнопок панелі інструментів і за допомогою пункту меню Данные/Сортировка. Які обмеження має сортування в Excel?
5.2.3 Які можливості надає користувачу опція Автофильтр?
5.2.4 Перелічіть правила завдання блока критеріїв для розширеного фільтра. Які особливості завдання критерію, що обчислюється?
5.2.5 Які функції для роботи зі списком Ви знаєте? Їхнє призначення і можливості.
5.2.6 Як задати перевірку значень, що вводяться, і які можливості у Вас при цьому є?
5.2.7 Назвіть головні можливості та вимоги до опції Данные/Итоги в списку.
5.3 Завдання для самостійної роботи
Для списку, що Ви створили в попередній лабораторній роботі, виконайте таке:
5.3.1 Зробіть сортування списку по декількох полях усіма можливими засобами.
5.3.2 За допомогою команди Данные/Фильтр/Автофильтр відберіть дані, що задовольняють декільком умовам (визначте умови самостійно).
5.3.3 За допомогою команди Данные/Фильтр/Расширенный фильтр організуйте відбір даних у вільне місце таблиці, що задовольняють більш складним критеріям, а також критеріям, що обчислюються. Для цього задайте декілька блоків критеріїв і блоків виведення.
5.3.4 Обчисліть середні значення, максимальні і мінімальні значення по числових полях для даних, що задовольняють різноманітним умовам, використовуючи функції бази даних. Підрахуйте кількість записів у списку, що задовольняють декільком умовам.
5.3.5 Створіть форму для введення і коригування даних у списку та вивчить всі можливості роботи з формою.
5.3.6 Організуйте для декількох полів перевірку значень, що вводяться.
5.3.7 Організуйте список для введення значень двох полів.
5.3.8 Підведіть підсумки по декількох полях таблиці, використовуючи різноманітні підсумкові функції.
6 ПРАКТИЧНА РОБОТА №6