Лабораторна робота №2, Зведені таблиці в Excel

« Назад

Завдання 1

На новому листі створити зведену таблицю, в якій визначається максимальний термін покарання по кожній ВТК та статті КК України.

Порядок виконання

1. Курсор знаходиться в полі таблиці.

2. Вибрати меню "Данные" - "Сводная таблица" (Зведена таблиця).

Побудова зведеної таблиці відбувається за допомогою Майстра зведених таблиць в 3 кроки:

- вибір даних, на основі яких будується зведена таблиця або діаграма (натискуємо ДАЛІ)

- вибираємо діапазон значень, якщо діапазон вірний натискуємо ДАЛІ

- на третьому кроці натискуємо МАКЕТ для створення таблиці

Для побудови зведеної таблиці перетаскуємо поля на макет таблиці

Так як необхідно знайти не сумму термінів, а їх максимальне значення, то двічі клікаємо на полі "Сумма по полю Термін" і відкриваємо вікно для зміни операції.

Обираємо операцію Максимум та натискуємо ОК

Далі у вікні (2) Макету натискуємо ОК, у вікні (1) - ГОТОВО.

3. В результаті отримуємо на новому листі зведену таблицю

У таблиці поля можна перетаскувати.

Та обирати дані за обмеженням (наприклад, максимальний термін тільки чоловіків).

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

Завдання 2

На основі даної початкової інформації та використовуючи інструмент "Підбір параметру", визначити скільки років людина провела у ВТК, якщо термін покарання зменшився 20 місяців, враховуючи, що за кожні 3 місяці гарної поведінки термін покарання зменшувався на 1 місяць.

Порядок виконання

1. У комірці В3 розрахувати на яку кількість місяців зменшиться термін покарання, якщо людина провела у ВТК вже 2 роки.

Якщо за кожні 3 місяці гарної поведінки термін покарання зменшувався на 1 місяць, то за рік термін показання зменшиться на 4 місяці. У комірку В3 вносимо формулу (формула = В2*4).

2. У комірці В4 визначаємо кількість років, які залишилося провести у ВТК з врахуванням зменшення покарання. Від терміну покарання (В1) віднімаємо кількість років, які вже провів у ВТК (В2) та кількість років за гарну поведінку (В3/12) та беремо цілу кількість років (формула =ОКРВНИЗ(В1-B2-В3/12;1)).

3. У комірці В5 визначаємо кількість місяців, які залишилося провести у ВТК з врахуванням зменшення покарання. Від терміну покарання (В1) віднімаємо кількість років, які вже провів у ВТК (В2) та термін покарання, який залишилося відбути у роках (В4), множимо на 12 (для визначення кількості місяців та віднімаємо кількість місяців за гарну поведінку (В3) - формула =(B1-В2-B4)*12-B3.

4. Для вирішення поставленої задачі (визначити скільки років людина провела у ВТК, якщо термін покарання зменшився 20 місяців, враховуючи, що за кожні 3 місяці гарної поведінки термін покарання зменшувався на 1 місяць) ставимо курсор у комірку В3 та викликаємо команду "Підбір параметру" з меню "Сервіс" (Для Office 2007-2010 в меню меню "Данные" вибрати категорію "Работа с данными", елемент "Анализ "что-если").

5. У діалоговому вікні "Підбір параметру" у полі "Встановити у комірці" вказуємо комірку В3 з формулою розрахунку місяців, вписуємо значення, яке необхідно отримати у полі "Значення" (20) та у полі "Змінюючи значення комірки" вказуємо комірку (В2), в якій необхідно визначити кількість років.

 

Завдання 3

На основі даної початкової інформації та використовуючи інструмент "Таблиця підстановки", спрогнозувати зменшення терміну покарання за кожен рік проведення у ВТК, що за кожні 3 місяці гарної поведінки термін покарання зменшувався на 1 місяць.

Порядок виконання

1. У комірки В3, В4 та В5 вносяться формули відповідно до завдання на листі "Підбір параметру".

2. Для прогнозування результатів необхідно у коміру С11 внести формулу з комірки В3, D11 - з комірки В4, Е11 - з комірки В5 (це також можна зробити вказавши просто на комірку з формулою)

3. Для виконання прогнозування необхідно виділити діапазон В11:Е21. Викликати меню "Данные" "Таблица подстановки". У полі "Подставлять значения по строкам" вказати комірку, в якій розміщено значення з кількість років, які провів у ВТК і які змінюються. Після цього натиснути ОК. Знищити поодинокі результати неможливо, тільки весь діапазон результатів.

Скільки років проведе людина у ВТК при гарній поведінці?

Завдання 4

Річне навантаження суддів міського суду, розподілилася таким чином: 17 справ, 22, 27, 27, 30, 30, 33, 38, 38, 45, 48, 50, 55.

Визначте загальну кількість суддів, справ та середнє навантаження суду.

Виходячи з того, що середнє навантаження міського суду не повинна перевищувати 55 справ, встановіть оптимальну кількість суддів.

Порядок виконання

1. У комірку C2 "Підсумок" порахувати загальну кількість справ, число яких збігається у кількох суддів (формула = А2*В2). Дані для діапазону С3:С11 отримати автозаповненням.

2. У комірці В12 визначити число всіх суддів міського суду (автосумма).

3. У комірці С13 підрахувати кількість усіх справ, розглянутих міським судом (автосумма діапазону С2:С11).

4. У комірці С14 знайти середнє значення навантаження міського суду (функція СРЗНАЧ з діапазону С2:С11).

5. Скопіювати всю наявну у вас інформацію і вставити її, наприклад, з комірки А20, для можливості порівняння отриманих результатів з результатами рішення задачі при використанням операції ПОШУК РІШЕННЯ.

Використання функції "Пошук рішення"

6. Встановіть курсор в комірку С13.

7. Для визначення оптимальної кількості суддів міського суду, виходячи з середнього навантаження в 75 справ, викликати команду ПОШУК РІШЕННЯ із меню СЕРВІС.

Якщо команда "Пошук рішення" відсутня, то необхідно із меню СЕРВІС вибрати команду "НаДстройки" та вибрати "Поиск решения" ("Пошук рішення").

Для Office 2007-2010 в головному меню або меню Файл вибрати "Параметри" - "НаДстройки" - "Поиск решения" - "Перейти", після вибору пакету у меню "Данные" з'явиться елемент "Аналіз".

8. У вікні команди в поле ВСТАНОВИТИ ЦІЛЬОВУ КОМІРКУ вказати комірку С13 (мишею) та встановити Максимальному значенню.

9. У полі "Изменяя ячейки" осередки внести блок В2: В11 (виділити блок мишкою).

10. У полі ОБМЕЖЕННЯ за допомогою кнопки ДОДАТИ (Добавить) внести наступні обмеження:

- в поле ПОСИЛАННЯ НА КОМІРКУ (Ссылка на ячейку) вказати виділенням мишкою діапазон В2:В11, в середньому віконці вибрати значення "цілий", у вікні поля ОБМЕЖЕННЯ автоматичні встановитися слово "ціле", виконати клацання по кнопці ДОДАТИ;

- в полі ПОСИЛАННЯ НА КОМІРКУ вказати виділенням мишкою блок В2:В11, в середньому віконці вибрати знак> =, у вікно поля ОБМЕЖЕННЯ внести 1, виконати клацання по кнопці ДОДАТИ;

- в полі ПОСИЛАННЯ НА КОМІРКУ вказати виділенням мишкою комірку С14, в середньому віконці вибрати знак =, у вікно поля ОБМЕЖЕННЯ внести 55, виконати клацання по кнопці ОК. Після клацання по кнопці ОК здійсниться перехід у вікно ПОШУК РІШЕННЯ.

11. У вікні команди ПОШУК РІШЕННЯ клацнути мишкою по кнопці ВИКОНАТИ, а у вікні РЕЗУЛЬТАТИ ПОШУКУ РІШЕННЯ встановити перемикач на елементі ЗБЕРЕЖЕННЯ ЗНАЙДЕНИХ РІШЕННЯ і натиснути кнопку ОК.

1. Яка кількість суддів необхідна міському суду для розгляду такої кількості справ?

2. Для якої кількості справ необхідно декілька суддів. Яка кількість суддів необхідна для такої кількості?