МЕТОДІЧНІ ВКАЗІВКИ. до лабораторних робіт. по дисципліні «Економічна інформатика» (VBA). МЕТОДІЧНІ ВКАЗІВКИ. до лабораторних робіт. по дисципліні «Економічна інформатика » (VBA). Луганськ 2008

Стр 1 из 4Следующая ⇒

МІНІСТЕРСТВО ОСВІТИ ТА НАУКИ УКРАЇНИ

СХІДНОУКРАЇНСЬКИЙ НАЦІОНАЛЬНИЙ УНІВЕРСИТЕТ

імені ВОЛОДИМИРА ДАЛЯ

МЕТОДІЧНІ ВКАЗІВКИ

до лабораторних робіт

по дисципліні «Економічна інформатика» (VBA)

(для студентів напрямку підготовки «Економіка та фінанси»)

ЛУГАНСЬК 2008

МІНІСТЕРСТВО ОСВІТИ ТА НАУКИ УКРАЇНИ

СХІДНОУКРАЇНСЬКИЙ НАЦІОНАЛЬНИЙ УНІВЕРСИТЕТ

імені ВОЛОДИМИРА ДАЛЯ

МЕТОДІЧНІ ВКАЗІВКИ

до лабораторних робіт

по дисципліні «Економічна інформатика » (VBA)

(для студентів напрямку підготовки «Економіка та фінанси»)

ЗАТВЕРДЖЕНО

на засіданні кафедри інформатики.

Протокол № 1 від 29.08.2008

Луганськ 2008

УДК 004(076)

Методичні вказівки до лабораторних робіт по дисципліні «Економічна інформатика» (для студентів спеціальностей напрямку «Економіка та фінанси») (VBA)/ Уклад.: Сичева Л.Ф., Войтіков В.А. – Луганськ: вид-во СНУ ім. В.І.Даля, 2008.-**с.

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

Укладачі: Сичева Л.Ф., ст. викл.

Войтіков В.А., ассист.

Скорих І.В., ассист.

Відп. за випуск Сичева Л.Ф., ст. викл.

Рецензент Нефедов Ю.М., доцент

Лабораторна робота №3

Функції для роботи з базами даних

Мета лабораторної роботи – закріплення теоретичного матеріалу, отримання практичних навиків використання функцій для роботи з базами даних, а також стандартних функцій СУММЕСЛИ и СЧЕТЕСЛИ.

Категорія «Работа с базой данных»

До категорії «Работа с базой данных» відносяться наступні функції:

1. БДДИСП. Оцінює дисперсію генеральної сукупності по вибірці, використовуючи числа у вказаному полі всіх записів, які задовольняють критерію;

2. БДЦИСПП. Обчисляє дисперсію генеральної сукупності, використовуючи числа у вказаному полі всіх записів, які задовольняють критерію;

3. БДПРОИЗВЕД. Перемножує значення у вказаному полі всіх записів, задовольняють критерію;

4. БДСУММ. Підсумовує числа у вказаному полі всіх записів бази даних, які задовольняють критерію;

5. БИЗВЛЕЧЬ. Витягує одне значення з бази даних, яке задовольняє заданому критерію;

6. БСЧЁТ. Підраховує кількість вічок, які містять числа, у вказаному полі всіх записів, що задовольняють критерію;

7. БСЧЁТА. Підраховує не порожні вічка у вказаному полі всіх записів, які задовольняють критерію;

8. ДМАКС. Повертає найбільше число у вказаному полі всіх записів, що задовольняють критерію;

9. ДМИН. Повертає найменше число у вказаному полі всіх записів, що задовольняють критерію;

10. ДСРЗНАЧ. Повертає середнє значення у вказаному полі всіх записів, що задовольняють критерію;

11. ДСТАВДОТКЛ. Оцінює стандартне відхилення на базі вибірки з генеральної сукупності, використовуючи числа у вказаному полі всіх записів, що задовольняють критерію;

12. ДСТАНДОТКЛП. Обчислює стандартне відхилення генеральної сукупності, використовуючи числа у вказаному полі всіх записів, що задовольняють критерію;

Функції для роботи з базою даних мають наступний синтаксис:

«=Ім’я_функції(база_даних; поле; критерій)»

де

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

§ поле – заголовок стовпця, до якого застосується функція;

§ критерій - діапазон вічок, який містить таблицю критеріїв.

Усі функції для роботи з базами даних мають однакові аргументи. Однак, при роботі з функцією БИЗВЛЕЧЬ треба пам’ятати, що на неї накладаються обмеження на відміну від інших функцій бази даних.

Функція БИЗВЛЕЧЬ повертає наступні результати:

§ «#ЗНАЧ!». Якщо жоден з записів не задовольняє критеріям;

§ «#ЧИСЛО!». Якщо більш, ніж один запис задовольняє критеріям;

§ Запис. Якщо тільки один запис задовольняє критеріям.

Функції СУММЕСЛИ и СЧЕТЕСЛИ

1. СУММЕСЛИ. Математична функція розраховує суму значень діапазону, вказаного у полі «діапазон підсумовування», але заздалегідь робиться перевірка стовпця вказаного у полі «діапазон», та якщо значення відповідає заданим умовам, то значення діапазону «діапазон підсумовування», яке стоїть в цьому ж рядку, додається до суми, інакше, якщо не відповідає умовам, то відкидається.

Синтаксис цієї функції такий:

«=СУММЕСЛИ(діапазон; критерій; діапазон підсумовування)»

де

§ Діапазон - діапазон, до якого застосується критерій, який стоїть у полі «Критерій»;

§ Критерій - умова, яка повинна виконуватися для значень діапазону вказаного в полі «діапазон»;

§ Діапазон підсумовування - діапазон, що містить значення, які необхідно підсумувати. Якщо цей параметр опущений, підсумовуватися будуть вічка, які вказані у параметрі «діапазон».

1. СЧЁТЕСЛИ. Статистична функція підраховує кількість не порожніх й задовольняючих критерію, вказаному у параметрі «критерій», вічок у діапазоні вказаному у параметрі «діапазон».

Синтаксис цієї функції такий:

«=СЧЁТЕСЛИ(діапазон; критерій)»

де

§ діапазон - інтервал, у якому треба підрахувати вічка;

§ критерій - критерій у формі числа, виразу або тексту, що визначає, які вічка треба підрахувати.

Завдання для виконання лабораторної роботи

Завдання 3.1 Застосування функцій «СУММЕСЛИ()» и «СЧЕТЕСЛИ()»

Відкрити файл «Сводные.xls» розташований на робочому столі (Таблиця 5).

Прізвище

Рік народження

Стать

Факультет

Курс

Вік

Код спеціальності

Номер групи

Форма Оплати

Таб. 5 Заголовки стовпців у базі даних «Сводные»

1. Використовуючи функцію «СУММЕСЛИ()», підсумувати всі номера груп студентам, яких більш 20 років.

2. Використовуючи функцію «СУММЕСЛИ()», підсумувати всі коди спеціальностей для студентів транспортного факультету.

3. Використовуючи функцію «СЧЁТЕСЛИ()», підрахувати кількість випускників.

Завдання 3.2 Застосування функцій для роботи з базою даних

1. Використовуючи функцію «БДСУММ()», підсумувати всі номера груп студентів економічного факультету, контрактної форми навчання.

2. Використовуючи функцію «ДМАКС()», визначити вік найстаршого студента механічного факультету, бюджетної форми навчання.

3. Використовуючи функцію «ДМИН()», визначити вік наймолодшого студента факультету журналістики, контрактної форми навчання.

4. Використовуючи функцію «БДПРОИЗВЕД()», помножити номера груп студентів чоловічого пола, третього курсу, факультету управління.

5. Використовуючи функцію «ДСРЗНАЧ()», знайти середній вік студентів, які навчаються на транспортному факультеті, спеціальності №5.

6. Використовуючи функцію «БСЧЁТ()», визначити кількість студенток, 2 курсу електротехнічного факультету, вік яких визначається проміжком (18;20).

7. Використовуючи функцію «БСЧЁТА()», визначити кількість студентів чоловічої статі, 2 курсу факультету «Природничих наук», вік яких визначається проміжком (18;19), а також студентів 5 курсу гуманітарного факультету, вік яких визначається проміжком (25;30).

8. Використовуючи функцію «БИЗВЛЕЧЬ()», витягти прізвище студентки, яка навчається на 2 курсі економічного факультету, по спеціальності 5 та має номер групи 1, на контрактної формі навчання.

9. Використовуючи функцію «БИЗВЛЕЧЬ()», витягти прізвище студента (чоловіча стать), який навчається на 3 курсі економічного факультету та має номер групи 1, на контрактної формі навчання.

Рекомендації по виконанню практичних завдань

Завдання 3.1

1. Використовуючи функцію «СУММЕСЛИ()», підсумувати всі «номера груп» студентам, яких більш 20 років.

a. Всі функції та формули в MS Excel починаються з символу «=». Відступив стовпець від початкового списку, у будь-яку вічку поставити «=»;

b. Викликати майстер функцій в рядку формул;

c. Обрати категорію «Полный алфавитный перечень» або «Математические» та обрати функцію «СУММЕСЛИ()»;

d. У параметрі «диапазон» вказати діапазон стовпця «Вік» без заголовка;

e. У параметрі «критерий» вказати «>20»;

f. У параметрі «диапазон суммирования» вказати діапазон стовпця «Номер групи» без заголовка;

g. Натиснути кнопку «ОК»; Відповідь: 4265.

2. Використовуючи функцію «СУММЕСЛИ()», підсумувати всі «коди спеціальностей» для студентів транспортного факультету.

a. Викликати функцію «СУММЕСЛИ()»;

b. У параметрі «диапазон» вказати діапазон стовпця «Факультет» без заголовка;

c. У параметрі «критерий» вказати «Транспортний». Слова у полі критерій необхідно писати у подвійних лапках;

d. У параметрі «диапазон суммирования» вказати діапазон стовпця «Код спеціальності» без заголовка;

e. Відповідь: 2625.

3. Використовуючи функцію «СЧЁТЕСЛИ()», підрахувати кількість випускників.

a. Викликати майстер функцій та визначити категорію «Полный алфавитный перечень» або «Статистические»;

b. Обрати функцію «СЧЁТЕСЛИ()»;

c. У параметрі «диапазон» вказати діапазон стовпця «Курс» без заголовка;

d. У параметрі «критерий» вказати «випускник». Слова в параметрі критерій необхідно обрамувати подвійними лапками;

e. Натиснути кнопку «ОК». Відповідь: 561.

Завдання 3.2

1. Використовуючи функцію «БДСУММ()», підсумувати всі «номера груп» студентів економічного факультету, контрактної форми навчання.

a. Перед застосуванням функцій для роботи з базою даних необхідно сформувати таблицю критеріїв.

b. Критеріями відбору записів є наступні критерії «Факультет – економічний» та «Форма навчання – контракт». Цім критеріям відповідає наступна таблиця:

Факультет	Форма оплати
Економічний	К

c. У майстру функцій визначити категорію «Работа с базой данных» та обрати функцію «БДСУММ()»;

d. У параметрі «База данных» вказати діапазон, який містить всю базу даних;

e. У параметрі «Поле» вказати заголовок «Номер групи» - заголовок стовпця, за яким будуть виконуватися обчислення;

f. У параметрі «критерий» вказати діапазон, який містить таблицю критеріїв;

g. Натиснути кнопку «ОК». Відповідь: 223.

2. Використовуючи функцію «ДМАКС()», вказати вік найстаршого студента механічного факультету, бюджетної форми навчання.

a. Сформувати таблицю критеріїв за наступними критеріями: «факультет - механічний» и «Форма навчання - бюджет»;

b. Викликати функцію «ДМАКС()»;

c. У параметрі «База данных» вказати діапазон, який містить всю базу даних;

d. У параметрі «Поле» вказати заголовок «Вік» - заголовок стовпця, за яким будуть виконуватися обчислення;

e. У параметрі «критерий» вказати діапазон, який містить таблицю критеріїв;

f. Натиснути кнопку «ОК». Відповідь: 29,2.

3. Використовуючи функцію «ДМИН()», вказати вік наймолодшого студента факультету журналістики, контрактної форми навчання.

a. Сформувати таблицю критеріїв за наступними критеріями: «факультет - журналістики» и «Форма навчання - контракт»;

b. Викликаємо функцію «ДМИН()»;

c. У параметрі «База данных» вказати діапазон, який містить всю базу даних;

d. У параметрі «Поле» вказати заголовок «Вік» - заголовок стовпця, за яким будуть виконуватися обчислення;

e. У параметрі «критерий» вказати діапазон, який містить таблицю критеріїв;

f. Натиснути кнопку «ОК». Відповідь: 18,1.

4. Використовуючи функцію «БДПРОИЗВЕД()», помножити номера груп студентів чоловічої статі, третього курсу, факультету управління.

a. Сформувати таблицю критеріїв за наступними критеріями: «факультет - управління», «Курс - 3» и «Стать - чоловіча»;

b. Викликати функцію «БДПРОИЗВЕД()»;

c. У параметрі «База данных» вказати діапазон, який містить всю базу даних;

d. У параметрі «Поле» вказати заголовок «Номер групи» - заголовок стовпця, за яким будуть виконуватися обчислення;

e. У параметрі «критерий» вказати діапазон, який містить таблицю критеріїв;

f. Натиснути кнопку «ОК». Відповідь: 16.

a. Сформувати таблицю критеріїв за наступними критеріями: «факультет - транспортний» и «Спеціальність - №5»;

b. Викликати функцію «ДСРЗНАЧ()»;

c. У параметрі «База данных» вказати діапазон, який містить всю базу даних;

d. У параметрі «Поле» вказати заголовок «Вік» - заголовок стовпця, за яким будуть виконуватися обчислення;

e. У параметрі «критерий» вказати діапазон, який містить таблицю критеріїв;

f. Натиснути кнопку «ОК». Відповідь: 20,9.

a. Сформувати таблицю критеріїв за наступними критеріями: «курс - 2», «факультет - електротехнічний», «Стать - жіноча», «Вік - >18» и «Вік - <20»;

b. Викликати функцію «<БСЧЕТ()»;

c. У параметрі «База данных» вказати діапазон, який містить всю базу даних;

d. У параметрі «Поле» вказати заголовок стовпця, за яким будуть виконуватися обчислення. Можна вказати будь-який стовпець, який містить числа. Стовпець «Курс» не підходить, тому що містить значення «випускник»;

e. У параметрі «критерий» вказати діапазон, який містить таблицю критеріїв;

f. Натиснути кнопку «ОК». Відповідь: 42.

a. Сформувати таблицю критеріїв;

b. Викликаємо функцію «<БСЧЕТА()»;

c. У параметрі «База данных» вказати діапазон, який містить всю базу даних;

d. У параметрі «Поле» вказати заголовок стовпця, за яким будуть виконуватися обчислення. Можна вказати будь-який стовпець, який не містить порожніх вічок;

e. У параметрі «критерий» вказати діапазон, який містить таблицю критеріїв;

f. Натиснути кнопку «ОК». Відповідь: 22.

8. Використовуючи функцію «БИЗВЛЕЧЬ()», витягти прізвище студентки 2 курсу економічного факультету, по спеціальності 5, номер групи 1, на контрактної формі навчання.

a. Сформувати таблицю критеріїв;

b. Викликаємо функцію «БИЗВЛЕЧЬ()»;

c. У параметрі «База данных» вказати діапазон, який містить всю базу даних;

d. У параметрі «Поле» вказати заголовок стовпця, з якого буде витягуватися значення;

e. У параметрі «критерий» вказати діапазон, який містить таблицю критеріїв;

f. Натиснути кнопку «ОК». Відповідь: #ЗНАЧ!.

9. Використовуючи функцію «БИЗВЛЕЧЬ()», витягти прізвище студента (чоловіча стать) 3 курсу економічного факультету, який навчається у групі номер 1, на контрактної формі навчання.

a. Сформувати таблицю критеріїв;

b. Викликати функцію «БИЗВЛЕЧЬ()»;

c. У параметрі «База данных» вказати діапазон, який містить всю базу даних;

d. У параметрі «Поле» вказати заголовок стовпця, з якого буде витягуватися значення;

e. У параметрі «критерий» вказати діапазон, який містить таблицю критеріїв;

f. Натиснути кнопку «ОК». Відповідь: „Григорян О.”

Лабораторна робота №4

Виповнення обчислювань у списках

Мета лабораторної роботи – закріплення теоретичного матеріалу, отримання практичних навиків використання інструменту MS EXCEL «Итоги».

Команда «Итоги»

Команда «Итоги» призначена для організації створення різноманітних звітів за допомогою сортування та фільтрації.

Результати попереднього фільтрування списку необхідно копіювати на новий лист, тому що проміжні підсумки створюються на місці первісного списку, що може утруднити подальшу роботу з первісними даними.

Діалогове вікно «Промежуточные итоги»

Діалогове вікно «Промежуточные итоги» (Рисунок 9) можна викликати на екран, виповнив команду «Данные→Итоги».

Рис. 9 Діалогове вікно «Промежуточные итоги»

Діалогове вікно «Промежуточные итоги» містить наступні параметри:

§ «При каждом изменении в:». Вказує заголовок стовпця, при зміні значення у якому буде виведено проміжні підсумки. Очевидно, перед застосуванням проміжних підсумків необхідно здійснити сортування списку по цьому стовпцю;

§ «Операция:». Вказує яка операція буде застосовуватися до значень стовпців відмічених у параметрі «Добавить итоги по:». Параметр «Операция:» може містити наступні операції: «Сумма», «Количество», «Среднее», «Максимум», «Минимум», «Произведение», «Количество чисел», «Смещенное отклонение», «Несмещенное отклонение», «Смещенная дисперсия» та «Несмещенная дисперсия»;

§ «Добавить итоги по:». Вказує до яких стовпців треба застосувати операцію, визначену у другому параметрі;

§ Галочка «Заменить текущие итоги». Дозволяє замінити новими всі отримані раніше проміжні підсумки. Якщо галочка знята, нові підсумки накладаються на отримані раніше;

§ Галочка «Конец страницы между группами». Дозволяє розташувати підсумки кожної групи значень на окремій сторінці;

§ Галочка «Итоги под данными». Дозволяє розташувати проміжні підсумки під групою значень, за якою були отримані. Якщо галочка знята, проміжні підсумки розташовуються над групою значень, за якою були отримані.

§ Кнопка «Убрать все». Дозволяє прибрати всі присутні в цій момент у списку проміжні підсумки;

§ Кнопка «ОК». Дозволяє додати нові проміжні підсумки до первісного списку даних;

§ Кнопка «Отмена». Дозволяє покинути вікно «Промежуточные итоги» не змінюючи первісний список.

Застосування команди «Итоги»

1. Перед застосуванням проміжних підсумків необхідно сортувати всі стовпці, при зміні значень в яких планується додавати підсумки. Сортувати стовпці необхідно відповідно послідовності накладення підсумків (в ключі «Сортировать по» поле на яке накладається перший підсумок, в ключі «Затем по» поле на яке накладається другий підсумок, в ключі «В последнюю очередь по» поле на яке накладається третій підсумок. Якщо планується накладати більш трьох підсумків сортувати треба починаючи зі стовпця підсумок на який буде накладатися останнім та закінчуючи стовпцем підсумок на який буде накладатися першим);

2. Виділити будь-яку вічку бази даних;

3. Для виклику діалогового вікна «Промежуточные итоги» виповнити команду «Данные→Итоги»;

4. Визначити необхідні параметри та натиснути кнопку «ОК».

5. Після застосування проміжних підсумків зліва від результату автоматично створюється структура яка дозволяє приховувати непотрібні та відображати потрібні дані результату.

Завдання для виповнення лабораторної роботи

Завдання 4.1 Застосування проміжних підсумків

1. Визначити кількість студентів математичного факультету.

2. Визначити кількість студентів, які навчаються на бюджеті факультету комп’ютерних систем.

3. Визначити кількість студентів чоловічої статі гуманітарного факультету, що навчаються на контракті.

4. Визначити кількість випускників чоловічої статі, що навчаються на контракті.

5. Визначити вік наймолодшій студентки механічного факультету.

6. Визначити вік найстаршого студента чоловічої статі 5 курсу.

7. Визначити кількість студенток економічного факультету, що не є випускницями.

8. Визначити середній вік чоловіків другого курсу, що навчаються на контракті.

9. Визначити середній вік студенток електротехнічного факультету, що навчаються на бюджеті.

10. Визначити:

a. кількість студентів на математичному факультеті;

b. вік старшого чоловіка факультету природничих наук;

c. середній вік студенток механічного факультету, що навчаються на бюджеті.

11. Визначити:

a. кількість випускниць;

b. вік молодшої студентки третього курсу;

c. кількість випускників чоловічої статі механічного факультету.

12. Визначити:

a. середній вік студентів фінансового факультету;

b. кількість студентів транспортного факультету, що навчаються на контрактній основі;

c. старшого чоловіка юридичного факультету, що навчається на бюджеті;

d. молодшу студентку третього курсу факультету журналістики, що навчається на бюджеті.

Рекомендації по виповненню практичних завдань

Завдання 4.1

1. Визначити кількість студентів математичного факультету.

a. Сортувати стовпець «Факультет»;

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

c. В полі «При каждом изменении в:» обрати «Факультет»;

d. В полі «Операция:» обрати «Количество»;

e. В полі «Добавить итоги по:» обрати будь-яке поле в якому немає порожніх вічок;

f. Натиснути кнопку «ОК». Відповідь: 302.

2. Визначити кількість студентів, які навчаються на бюджеті факультету комп’ютерних систем.

a. Сортувати по полю «Факультет», потім по полю «Форма оплати»;

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

c. Додати проміжні підсумки для груп значень стовпця «Факультет». Використати операцію «Количество» по стовпцю «Факультет»;

d. Перейти у другий рівень структури;

e. Зняти галочку «Заменить текущие итоги»;

f. Додати проміжні підсумки для груп значень стовпця «Форма оплати». Використати операцію «Количество» по стовпцю «Форма оплати»;

g. Відповідь: 91.

3. Визначити кількість студентів чоловічої статі гуманітарного факультету, що навчаються на контракті.

a. Сортувати по полю «Факультет», потім по полю «Форма оплати», в останню чергу по полю «Стать»;

b. Зверніть увагу: сортування для першого, другого та третього завдання можна було зробити один раз так, як було сортовано у третьому завдані. Накладаючи вкладені підсумки, є можливість вирішувати одночасно декілька задач. Так при виповнені третьої одночасно вирішуються дві перші задачі;

c. Добавить проміжні підсумки для груп значень стовпця «Факультет». Використовувати операцію «Количество» по стовпцю «Факультет»;

d. Перейти на другий рівень структури;

e. Зняти галочку «Заменить текущие итоги»;

g. Додати проміжні підсумки для груп значень стовпця «Стать». Використати операцію «Количество» по стовпцю «Стать»;

h. Відповідь: 8.

4. Визначити кількість випускників чоловічої статі, що навчаються на контракті.

a. Сортувати за полем «Курс», потім за полем «Стать», в останню чергу за полем «Форма оплати»;

b. Добавить проміжні підсумки для груп значень стовпця «Курс». Використати операцію «Количество» по стовпцю «Курс»;

c. Перейти на другий рівень структури;

d. Зняти галочку «Заменить текущие итоги»;

e. Додати проміжні підсумки для груп значень стовпця «Стать». Використати операцію «Количество» по стовпця «Стать»;

g. Відповідь: 159.

5. Визначити вік наймолодшої студентки механічного факультету.

a. Сортувати за полем «Стать», потім по полю «Факультет»;

b. Добавить проміжні підсумки для груп значень стовпця «Стать». Використати операцію «Минимум» по стовпцю «Вік»;

c. Перейти на другий рівень структури;

d. Зняти галочку «Заменить текущие итоги»;

e. Додати проміжні підсумки для груп значень стовпця «Факультет». Використати операцію «Минимум» по стовпцю «Вік»;

f. Відповідь: 13,3.

6. Визначити вік найстаршого студента чоловічої статі 5 курсу.

a. Сортувати за полем «Стать», потім по полю «Курс»;

b. Додати проміжні підсумки для груп значень стовпця «Стать». Використати операцію «Максимум» по стовпцю «Вік»;

c. Перейти на другий рівень структури;

d. Зняти галочку «Заменить текущие итоги»;

e. Додати проміжні підсумки для груп значень стовпця «Курс». Використати операцію «Максимум» по стовпцю «Вік»;

f. Відповідь: 32,9.

7. Визначити кількість студенток економічного факультету, що не є випускницями.

a. Сортувати за полем «Стать», потім по полю «Факультет»;

b. Додати проміжні підсумки для груп значень стовпця «Стать». Використати операцію «Количество» по стовпцю «Стать»;

c. Перейти на другий рівень структури;

d. Зняти галочку «Заменить текущие итоги»;

e. Додати проміжні підсумки для груп значень стовпця «Факультет». Використати операцію «Количество чисел» по стовпцю «Курс»;

f. Відповідь: 177.

8. Визначити середній вік чоловіків другого курсу, що навчаються на контракті.

a. Сортувати за полем «Стать», потім за полем «Форма оплати», в останню чергу за полем «Курс»

c. Перейти на другий рівень структури;

d. Зняти галочку «Заменить текущие итоги»;

e. Додати проміжні підсумки для груп значень стовпця «Форма оплати». Використати операцію «Количество» по стовпцю «Форма оплати»;

f. Додати проміжні підсумки для груп значень стовпця «Курс». Використати операцію «Среднее» по стовпцю «Вік»;

g. Відповідь: 19.

9. Визначити середній вік студенток електротехнічного факультету, що навчаються на бюджеті.

a. Сортувати за полем «Стать», потім за полем «Факультет», в останню чергу за полем «Форма оплати»;

c. Перейти на другий рівень структури;

d. Зняти галочку «Заменить текущие итоги»;

e. Додати проміжні підсумки для груп значень стовпця «Факультет». Використати операцію «Количество» по стовпцю «Факультет»;

f. Додати проміжні підсумки для груп значень стовпця «Форма оплати». Використати операцію «Среднее» по стовпцю «Вік»;

g. Перейти на четвертий рівень структури;

h. Перейти на третій рівень структури;

i. Розкрийте електротехнічний факультет для перегляду даних.

j. Відповідь: 20,8.

10. Визначити: a) кількість студентів на транспортному факультеті. б) вік старшого чоловіка факультету природничих наук. в) середній вік студенток механічного факультету, що навчаються на бюджеті.

a. Сортувати за полем «Факультет», потім за полем «Стать», в останню чергу за полем «Форма оплати»;

b. Додати проміжні підсумки для груп значень стовпця «Факультет». Використати операцію «Количество» по стовпцю «Прізвище»;

c. Перейти на другий рівень структури. Відповідь: 717;

d. Зняти галочку «Заменить текущие итоги»;

e. Додати проміжні підсумки для груп значень стовпця «Стать». Використати операцію «Максимум» по стовпцю «Вік». Відповідь: 50;

f. Додати проміжні підсумки для груп значень стовпця «Форма оплати». Використати операцію «Среднее» по стовпцю «Вік». Відповідь: 20,8.

11. Визначити: а) кількість випускниць. б) вік молодшої студентки третього курсу. в) кількість випускників чоловічої статі механічного факультету.

a. Сортувати за полем «Курс», потім за полем «Стать», в останню чергу за полем «Факультет»;

b. Додати проміжні підсумки для груп значень стовпця «Курс». Використати операцію «Количество» по стовпцю «Курс»;

c. Перейти на другий рівень структури;

d. Зняти галочку «Заменить текущие итоги»;

e. Додати проміжні підсумки для груп значень стовпця «Стать». Використати операцію «Количество» по стовпцю «Стать». Відповідь: 222

f. Додати проміжні підсумки для груп значень стовпця «Стать». Використати операцію «Минимум» по стовпцю «Вік». Відповідь: 13,3;

g. Додати проміжні підсумки для груп значень стовпця «Факультет». Використати операцію «Количество» по стовпцю «Прізвище». Відповідь: 43.

12. Визначити: а) середній вік студентів фінансового факультету. б) кількість студентів транспортного факультету, що навчаються на контрактній основі. в) старшого чоловіка юридичного факультету, що навчається на бюджеті. г) наймолодшу студентку третього курсу факультету журналістики, що навчається на бюджеті.

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

b. Сортувати список за полем «Курс»;

c. Сортувати список за полем «Стать»;

d. Сортувати список за полем «Форма оплати»;

e. Сортувати список за полем «Факультет»;

f. Додати проміжні підсумки для груп значень стовпця «Факультет». Використати операцію «Среднее» по стовпцю «Вік»;

g. Перейти на другий рівень структури. Відповідь: 20,6;

h. Зняти галочку «Заменить текущие итоги»;

i. Додати проміжні підсумки для груп значень стовпця «Форма оплати». Використати операцію «Количество» по стовпцю «Форма оплати». Відповідь: 289;

j. Додати проміжні підсумки для груп значень стовпця «Стать». Використати операцію «Максимум» по стовпцю «Вік». Відповідь: 23,8;

k. Додати проміжні підсумки для груп значень стовпця «Курс». Використати операцію «Минимум» по стовпцю «Вік». Відповідь: 18,7;

Лабораторна робота №5

Створення та редагування звідні таблиці

Ціль лабораторної роботи – закріплення теоретичного матеріалу, отримання практичних навиків використання інструменту MS EXCEL «Сводные таблицы».

Поняття звідних таблиць

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

Інструмент «Сводные таблицы» дозволяє створювати підсумки визначеного виду на основі даних списків, інших звідних таблиць, зовнішніх баз даних, розрізнених областей даних електронних таблиць. Звідна таблиця надає різні способи агрегації інформації.

Створення звідних таблиць

Для виклику діалогового вікна «Мастер сводных таблиц и диаграмм» необхідно виповнити команду «Данные→Сводная таблица».

«Мастер сводных таблиц и диаграмм» здійснює побудову звідної таблиці у декілька етапів (кроків):

«крок 1 из 3» (Рисунок 10). Дозволяє вказати вид джерела звідної таблиці або діаграми, а також вид звіту, що створюється. В даній лабораторній роботі джерелом служить список.

Рис. 10 Майстер звідних таблиць та діаграм крок 1 з 3

«крок 2 из 3» (Рисунок 11). Дозволяє вказати інтервал вічок для побудування звідної таблиці.

Рис. 11 Майстер звідних таблиць та діаграм крок 2 з 3

«крок 3 из 3» (Рисунок 12). Дозволяє вказати місце розташування звідної таблиці, а також сформувати макет звідної таблиці (Кнопка «Макет») та параметри звідної таблиці (Кнопка «Параметры»).

Рис. 12 Майстер звідних таблиць та діаграм крок 3 з 3

Параметри звідної таблиці

При натисканні на кнопку «Параметры» відкривається діалогове вікно «Параметры сводной таблицы» (Рисунок 13).

Рис. 13 Діалогове вікно «Параметры сводной таблицы»

В цьому вікні пропонується настройка наступних опцій:

§ „Имя” - задається найменування звідної таблиці. По умовчанню задається „СводнаяТаблица1” і т. д.

§ „Общая сумма по столбцам” - під час встановлення галочки по кожному стовпцю підраховується загальні підсумки.

§ „Общая сумма по строкам” - під час встановлення галочки по кожному рядку підраховуються загальні підсумки.

§ „Автоформат” - застосування автоматичного форматування. Також можна застосувати команду «Формат→Автоформат»

§ „Включать скрытые значения” - вказівка включати в проміжні підсумки скриті вічка.

§ „Объединять ячейки заголовков” - вказівка об’єднувати вічка для зовнішніх заголовків рядків та стовпців.

§ „Сохранить форматирование” - вказівка зберігати задане форматування під час зміни звідної таблиці.

§ „Повторять подписи на каждой странице печати” - вказівка об необхідності печатати заголовки рядків на кожній сторінці звіту звідної таблиці.

§ „Помечать итоги *” - підсумкові значення помічаються символом зірочка (*) для їх виділення.

§ „Макет страницы” - вказує порядок слідування полів сторінці.

§ „Число полей в столбце” - вказується число полів сторінці, що виводяться, в рядку або стовпці перед виводом наступного рядку або стовпця.

§ „Для ошибок отображать” - вказується будь-яке значення, що вводиться в даній вічку звідної таблиці, якщо під час обчислення значення у ній виникне помилка.

§ „Для пустых ячеек отображать” - вказується будь-яке значення, що виводиться у порожніх вічках.

§ „Печать заголовков” - вказівка печатати заголовки на кожній сторінці звіту звідної таблиці.

§ „Сохранить данные вместе с таблицею” - вказівка створювати копію даних разом з робочою книгою. Для активізації цієї опції потрібен великий об’єм оперативної пам’яті.

§ „Развертывание разрешено” - вказівка виводити по кожному вічку звідної таблиці додаткові відомості. Для виводу додаткових відомостей потрібно по даному вічку двічі клацнути лівою клавішею миші.

§ „Обновить при открытии” - вказівка обновляти звідну таблицю під час кожного відкриття робочої книги, в якій вона розташована.

§ „Обновлять каждые [ ] мин” - вказівка обновляти звідну таблицю через задане число хвилин. Цю опцію має сенс встановлювати під час роботи з зовнішніми базами даних.

§ „Сохранить пароль” - вказівка зберігати пароль під час роботи з зовнішніми базами даних, щоб використовувати його у подальшій роботі та не повторювати його постійно.

§ „Фоновый запрос” - вказівка звертатися до зовнішньої бази даних в режимі фонового запиту.

§ „Оптимизировать память” - вказівка зменшити розмір пам’яті під час обновлення запиту до зовнішньої бази даних.

§ Після встановлення всіх опцій у цьому діалоговому вікні необхідно натиснути кнопку ОК для фіксації параметрів та закриття цього вікна.

Формування макету звідної таблиці або діаграми

Після використання кнопки Макет на третьому кроку створення звідної таблиці відкривається діалогове вікно «Мастер сводних таблиц и диаграмм - макет» (Рисунок 14).

Рис. 14 Діалогове вікно «Мастер сводных таблиц и диаграмм - макет»

В цьому вікні поля (стовпці) бази даних представлені кнопками з такими ж найменуваннями.

Макет складається з чотирьох областей:

§ „Страница” - поле, визначене в цій області, використовується у якості заголовків сторінок у даній звідній таблиці, а також забезпечує фільтрацію записів списку. В цю область слід поміщати поле, в якому необхідно визначити тільки одне значення (Наприклад, «Факультет → Математичний»). Цю область визначати не обов’язково.

§ „Строка” - поле, визначене в цій області, використовується у якості заголовків рядків в звідній таблиці. В цій облас

12 3 4 Следующая ⇒