2018-01-08
184Тема: Робота зі списками
Мета: навчитись працювати зі списками, користуватись командами Автофильтр, Расширенный фильтр, навчитись сортувати дані, розраховувати проміжні підсумки та підбирати параметри для окремої комірки
Хід роботи:
- Створити список згідно свого варіанту і заповнити його текстовими і числовими даними. Список має містити дані (назви товарів), що повторюються (по 3-4 рази), кількість записів – не менше 25, всі інші значення різні..
A B C D E F
| товар | інвестиц. затрати | виручка від реаліз. | поточні витрати | податки | термін окупності |
| Зошити | 140,00 | 170,00 | 1,0 | ||
| Ручки | 129,00 | 584,30 | 0,2 | ||
| Олівці | 125,00 | 124,00 | 1,3 | ||
| Зошити | 147,20 | 124,00 | 1,7 | ||
| Ручки | 36,50 | 325,00 | 0,1 | ||
| Олівці | 25,30 | 412,00 | 0,1 | ||
| Олівці | 42,10 | 56,00 | 1,9 |
При чому, значення в останньому стовпці обчислюються за формулою B2/(C2-E2-D2).
- Скопіювати список та відібрати в ньому дані відповідно до поставленої умови командою Автофильтр.
- Створити новий список командою Расширенный фильтр.
- Скопіювати список та відсортувати в ньому дані з використанням 2-х ключів.
- Скопіювати відсортований список та розрахувати в ньому проміжні підсумки (Сумма, Минимум, Среднее...)
- Підібрати параметри для окремої комірки.
Підбір параметру використовується для прогнозування значення однієї комірки при зміні значення іншої. Такі комірки повинні бути повязані формулою. Нехай, наприклад потрібно визначити, при яких значеннях інвестиційних витрат для товару Зошити (b2), термін окупності (f2), становив би 0.8. Для цього курсор розташовуємо у БД і даємо к-ду Сервис - Подбор параметра. У вікні Подбор параметра вказуємо такі параметри: Отримуємо результат.
|
Приклад виконання лабораторної роботи.
- створюємо список. Значення в останньому стовпці обчислюються за ф. B2/(C2-E2-D2).
A B C D E F
| фірма | інвестиц. затрати | виручка від реаліз. | поточні витрати | податки | термін окупності |
| Арізона | 140,00 | 1,1 | |||
| Оріон | 129,28 | 257,32 | 0,6 | ||
| Престиж | 143,46 | 121,47 | 3,5 | ||
| Оріон | 98,01 | 143,15 | 1,0 | ||
| Престиж | 124,36 | 247,33 | 0,6 | ||
| Арізона | 196,96 | 0,1 | |||
| Престиж | 153,42 | 297,95 | 0,6 |
- Умова: Відібрати зі списку рядки(записи)для яких виручка від реалізації перевищує 200. Для цього:
1. Активізувавши б.-яку комірку списку виконуємо к-ду Данние-Фильтр-Автофильтр.
2. У стовпцях таблиці зявляються кнопки. Натискаємо кнопку стовпця виручка від реалізації і вибираємо пункт Условие.
3. Зявляється вікно в якому вказуємо - больше 200.
Отримуємо результат
| фірма | інвестиц. затрати | виручка від реаліз. | поточні витрати | податки | термін окупності |
| Арізона | 140,00 | 1,1 | |||
| Оріон | 129,28 | 257,32 | 0,6 | ||
| Престиж | 143,46 | 121,47 | 3,5 | ||
| Оріон | 98,01 | 143,15 | 1,0 | ||
| Престиж | 124,36 | 247,33 | 0,6 | ||
| Арізона | 196,96 | 0,1 | |||
| Престиж | 153,42 | 297,95 | 0,6 |
3. Суть к-ди Расширенний фильтр полягає в тому що на основі початкового списку створюється новий, який відображає лише ті рядки, що задовольняють задану умову. Порядок створення нового списку такий:
Копіюємо заголовок таблиці в довільно вибране місце
Під потрібним заголовком вказуємо умову(в нашому прикладі <40)
К-дою фильтр - Расширенний фильтр викликається вікно де вказуються параметри (для цього прикладу):- Исходний диапазон - $A$1:$F$8, Диапазон условий - $A$10:$F$11, поместить результат в диапазон - $A$13:$F$13 - ОК
| фірма | інвестиц. затрати | виручка від реаліз. | поточні витрати | податки |
окупності | ||
| Арізона | 140,00 | 1,1 | |||||
| Оріон | 129,28 | 257,32 | 0,6 | ||||
| Престиж | 143,46 | 121,47 | 3,5 | ||||
| Оріон | 98,01 | 143,15 | 1,0 | ||||
| Престиж | 124,36 | 247,33 |  40
| 0,6 | |||
| Арізона | 196,96 | 0,1 | |||||
| Престиж | 153,42 | 297,95 | 0,6 | ||||
| фірма | інвестиц. затрати | виручка від реаліз. | поточні витрати | податки | термін окупності | ||
| <40 | |||||||
| фірма | інвестиц. затрати | виручка від реаліз. | поточні витрати | податки | 
окупності | ||
| Арізона | 140,00 | 1,1 | |||||
| Престиж | 143,46 | 121,47 | 3,5 | ||||
| Оріон | 98,01 | 143,15 | 1,0 | ||||
| Арізона | 196,96 | 0,1 |
4. Сортування. Потрібно відсортувати початкову таблицю за такою, наприклад умовою:
4.1 перший ключ – (поле фірма) за зростанням
4.2 другий ключ – (поле поточні витрати) за спаданням
Це означає, що в межах однакових значень(тобто таких що повторюються) поля Фірма, дані в полі поточні витрати будуть розташовані за спаданням. Для цього вказівник миші поміщується в будь – яку комірку створеної таблиці і дається к-да Данные – сортировка. Зявляється вікно в якому зазначаємо задані умови. Отримуємо результат.
| фірма | інвестиц. затрати | виручка від реаліз. | поточні витрати | податки | термін окупності |
| Арізона | 140,00 | 1,1 | |||
| Арізона | 196,96 | 0,1 | |||
| Оріон | 129,28 | 257,32 | 0,6 | ||
| Оріон | 98,01 | 143,15 | 1,0 | ||
| Престиж | 124,36 | 247,33 | 0,6 | ||
| Престиж | 153,42 | 297,95 | 0,6 | ||
| Престиж | 143,46 | 121,47 | 3,5 |
5. Розрахунок проміжних підсумків. Розраховуються для полів, які мають значення що повторюються. Нехай потрібно обчислити мінімальні значення для полів “ інвестиц.
затрати ” та “ виручка від реалізації”. Для цього спочатку упорядковуються (сортуються) дані у полі “Фірма”. Далі, ввівши курсор у БД активізуємо к-ду Данние – Итоги. У вікні “ Промежуточние итоги” задаємо потрібні параметри і отримуємо результат.
| фірма | інвестиц. затрати | виручка від реаліз. | поточні витрати | податки | термін окупності |
| Арізона | 140,00 | 1,1 | |||
| Арізона | 196,96 | 0,1 | |||
| Арізона Минимум | 170,00 | ||||
| Оріон | 129,28 | 257,32 | 0,6 | ||
| Оріон | 98,01 | 143,15 | 1,0 | ||
| Оріон Минимум | 98,01 | 143,15 | |||
| Престиж | 124,36 | 247,33 | 0,6 | ||
| Престиж | 153,42 | 297,95 | 0,6 | ||
| Престиж | 143,46 | 121,47 | 3,5 | ||
| Престиж Минимум | 124,36 | 121,47 | |||
| Общий минимум | 10,00 | 121,47 |
6. Підбір параметру використовується для прогнозування значення однієї комірки при зміні значення іншої. Такі комірки повинні бути повязані формулою. Нехай, наприклад потрібно визначити, при яких значеннях інвестиційних витрат фірми Арізона (b2), термін окупності (f2), становив би 0.8. Для цього курсор розташовуємо у БД і даємо к-ду Сервис - Подбор параметра. У вікні Подбор параметра вказуємо такі параметри: Отримуємо результат.
|
