Використання елементарних формул

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

за темою: "Створення Робочої таблиці в MS Excel"

Мета: Навчитися створювати Робочі таблиці для обчислення задач облікового напряму.

Завдання.

1. Прочитати Теоретичний матеріал (стор. 2 – 26) Лабораторної роботи № 1 і самостійно виконати на комп’ютері всі наведені там вправи з виконання операцій в MS Excel.

2. Із наведених 30 – ти завдань (стор. 27 – 51) вибрати завдання свого варіанту (номер прізвища у журналі групи). Усвідомити зміст задачі, спроектувати (на папері) макет Робочої таблиці і створити в середовищі MS Excel Робочу таблицю для розв'язку поставленої задачі свого варіанту.

2. Завдання з оброблення введених даних в створеній Робочій таблиці виконати на окремих сторінках (листках): кожне із завдань на окремій сторінці (листку). Ярлики кожної сторінки (листка) Робочої книги назвати відповідно до завдання, виконаного на цій сторінці. Наприклад, ярлик сторінки, де створена Робоча таблиця, назвати РобТабл і т. д. Створену книгу зберегти у власній папці з назвою ЛабРоб1.

3. Скласти звіт з лабораторної роботи за визначеною формою.

 

Структура Лабораторна робота № 1:

1) теоретичний матеріал – від 2 до 26 сторінки;

2) індивідуальні завдання – від 27 до 51 сторінки;

3) приклад виконання завдання – від 52 до 65 сторінки.

Теоретичні відомості

Дії з робочими книгами

Файл, створений програмою MS Excel, має тип xls і називається робочою книгою, яка складається із сторінок-аркушів. Всього може бути до 256 сторінок. В кожну нову книгу «за замовченням» вкладається 3 сторінки з назвами їх ярликів: Лист1, Лист2, Лист3. Перехід з одного аркуша на інший можна здійснювати натисканням на ярлику відповідного аркуша. Для того, щоб додати новий аркуш в робочу книгу, потрібно викликати контекстне меню, натиснувши праву кнопку миші на ярлику аркуша, і в меню вибрати команду Добавить… В діалоговому вікні вибрати вкладку Общие значок Лист та натиснути кнопку ОК. Новий аркуш буде розташований ліворуч від поточного аркуша.

Команда Переименовать із контекстного меню дозволяє перейменовувати аркуші. Ім’я аркуша може містити не більше 31 символа з врахуванням проміжків (пробілів) між символами.

Команда Удалить із контекстного меню дозволяє знищити поточний аркуш.

Команда Переместить/скопировать … дозволяє переміщувати та копіювати поточний аркуш як в середині даної книги, так і в інші книги.

В діалоговому вікні Переместить или скопировать (рис.1) у полі в книгу: можна вибрати книгу, в яку буде переміщено або копіювано вибраний аркуш.

У полі перед листом: можна вказати перед яким аркушем або в кінець вибраної книги буде переміщений поточний аркуш (рис. 2).

 

Рис. 2

 

Рис. 1

 

При відмітці перемикача Создать копию замість пререміщення відбувається команда копіювання поточного аркуша.

Команда Цвет ярлычка… дозволяє розфарбувати ярлики аркушів в різні кольори.

Діапазон клітин

Діапазон клітинок - це сусідні клітинки, що утворюють прямокутник. Діапазон клітин може складатися з клітин одного стовпчика, одного рядка або з комбінації клітинок, розташованих у різних стовпчиках і рядках. Головна умова: клітинки обов'язково утворюють прямокутник. Діапазон клітин задається адресою верхньої лівої клітини таадресою нижньої правої клітини через двокрапку. Наприклад, A1:D5 (рис. 3).

 

Рис. 3

Виділення клітинки

Перед тим, як копіювати, переміщати або видаляти дані в клітинках, потрібно спочатку їх виділити і тільки потім виконувати перелічені вище операції. Для виділення однієї клітинки просто клацніть на ній. Щоб виділити групу (діапазон) сусідніх клітинок, клацніть у лівому верхньому куту групи і, утримуючи ліву кнопку миші, перетягніть її покажчик у правий нижній кут, а потім відпустіть кнопку. Для виділення несуміжних клітин утримуйте CTRL. Щоб виділити цілий стовпчик (рядок), клацніть на його позначенні. Для виділення групи сусідніх стовпчиків або рядків перетягніть покажчик миші по їхніх позначеннях. Щоб виділити несуміжні рядки або стовпчики, клацніть на їхніх позначеннях, утримуючи при цьому натиснутою клавішу CTRL.

Редагування даних

Після введення даних можна виправити їх у рядку формул або у самій клітині. Для цього необхідно виділити потрібну клітинку та клацнути в рядку формул або виділити клітинку та натиснути клавішу F2 чи двічі клацнути в потрібній клітинці. Так клітинка переходить у режим редагування.

Видалення даних з клітинки

Для видалення даних з клітинки необхідно виділити клітинку або діапазон, а потім натиснути клавішу Dеlеtе. Можна також виділити потрібний діапазон клітин, натиснути правукнопку миші і в контекстному меню обрати пункт Очистить содержимое.

Типи даних

Існує п’ять типів даних в Ехсеl: текстові, числові, дати, формули, функції. Текстові дані являють собою послідовність букв, цифр і пробілів між ними. За замовченням текст, який вводиться до клітинки, вирівнюється по її лівому краю.

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

Розділовим знаком є: 1) в дійсних числах цілу частину від дробової відділяє кома, наприклад 333,145; 2) в датах день від місяця і року відділяє крапка, наприклад 01.09.2012; 3) в часі години відділяються від хвилин і секунд двокрапками, наприклад 8:15:00.

Для різних типів даних можливі різні операції. Наприклад, числа можна додавати, а дати не можна. Із тексту можна вирізати символи, а із формул - ні. Тип визначається автоматично за введеною інформацією. Визначення ведеться в порядку: функція, формула, дата, число, текст. Якщо в першій позиції стоїть знак = «дорівнює», то це функція або формула. Інакше, якщо розпізнається номер дня, місяць, рік з розділовим знаком дати, то це дата. Інакше розпізнається число. Якщо введена інформація не підійшла під перші 4 типи, то вважається, що в клітині знаходиться текст.

Зауваження: якщо Ви використали в якості розділового знака числа крапку, наприклад, 1.5, то в клітині замість числа з’явиться дата (рис. 4).

 

 

Рис. 4

Тепер, скільки б не вводили в цю клітину 1,5 – все рівно буде дата. Щоб змінити формат клітинки, потрібно вибрати із Головного меню: Формат - Ячейки. В діалоговомувікні вибрати вкладку Число. У списку Числовые форматы вибрати формат Общий або Числовой. Для Числового формату вказується ще й число десяткових знаків (рис. 5).

Із Головного меню: Данные - Проверка використовується для того, щоб значення, що вводяться в таблицю, задовольняли деяким умовам. Для цього у вікні діалогу Проверка вводимых значений на вкладці Параметры зазначаєтьсятип та інтервал значень, що дозволяється вводити. Вкладка Сообщение для ввода дозволяє створити підказування користувачу про те, які дані можна вводити. На вкладці Сообщение об ошибке можна ввести повідомлення, яке буде з’являтися на екрані, якщо введено некоректні дані.

 

Рис. 5

Наприклад, в діапазон клітин B2:B5 (рис. 6) можна вводити тільки цілі числа в діапазоні від 5 до 9. При введенні даних в клітини передбачити підказку, при введенні некоректних даних вивести на екран повідомлення про помилку.

Рис. 6

1. Виділяємо стовпчик B, Із Головного меню: Данные – Проверка, в діалоговому вікні «Проверка вводимых значений» у вкладці Параметры задаємо умови перевірки (рис. 7).

2. У вкладці Сообщения для ввода заповнюємо потрібні поля (рис.8).

Рис. 7 Рис. 8

 

У вкладці Сообщение об ошибке вводимо текст повідомлення та заголовок (рис. 9).

Рис. 9

Тепер при активізації будь-якої клітини з цього діапазону завжди буде висвічуватися текст повідомлення для введення (рис. 10).

Рис. 10

Якщо при введенні Ви допустили помилку, то з’явиться текст повідомлення про помилку (рис. 11).

Рис. 11

 

Об’єднання клітинок

Для об’єднання клітин потрібно виділити необхідний діапазон клітинок, вибрати із Головного меню: Формат - Ячейки. В діалоговомувікні вибрати вкладку Вьіравнивание, далі відмічаємо прапорець на опції Обьединение ячеек та Переносить по словам, якщо текст займає декілька рядків.

В цьому ж вікні можна задати види вирівнювання тексту. Наприклад, щоб вирівняти текст в об’єднаних клітинках по вертикалі, клацніть на стрілці відповідного списку, що випадає, і виберіть у ньому по центру. Тут же можна задати кут нахилу тексту (рис. 12).

 

 

Рис.12

Копіювання формул

Щоб скопіювати вже введені формули у сусідні клітинки, найпростіше виділити клітину, встановити покажчик миші в правому нижньому кутку цієї клітини так, щоб курсор прийняв вигляд чорного хрестика (маркер автозаповнення), і, натиснувши на ліву кнопку миші і утримуючи її в натисненому стані, перетягнути маркер на необхідні клітинки. Такий спосіб копіювання називається методом автозаповнення.

Можна здійснювати копіювання також за допомогою команд Копировать і Вставить.

Використання функцій

Функції – це вбудовані в програму MS Excel процедури, які дозволяють проводити складні обчислення та слугують для скорочення часу на побудову формул. Кожна функція складається з імені функції та списку аргументів, що задаються в дужках. Всі доступні функції знаходяться в Мастере функций.

Для виклику майстра функцій потрібно вибрати із Головного меню Вставка – Функция… або натиснути на кнопку в рядку формул або на панелі інструментів «Стандартная». В Мастере функций (рис. 16) всі функції розбиті на категорії: математичні, статистичні, логічні тощо. При виборі потрібної категорії у вікні Выберите функцию: з’являються всі функції, що належать до цієї категорії.

Виклик майстра функцій можна здійснити також за допомогою випадаючого списку, що з’являється у лівому кутку рядка формул після натиснення у правій частині рядка формул знака дорівнює «=» (рис. 17).

 

Рис. 17

 

Рис. 16

 

Наприклад, для обчислення суми в клітині B6 (рис. 18) потрібно:

1. Виділити клітину B6, натиснути

2. В діалоговому вікні вибрати Категория: Математические, Функция: СУММ, ОК.

Рис. 18

3. В наступному діалоговому вікні (рис. 19) вказуємо діапазон клітин B2:B5, тобто діапазон клітин, значення яких потрібно просумувати.

Рис. 19

У клітині B6 з’являється результат обчислення, а у рядку формул наступна формула = СУММ(B2: B5)

При обчисленні середнього, мінімального та максимального значень вибирають Категория: Статистические, Функция: СРЗНАЧ, МИН, МАХ відповідно, а в якості діапазону вказують B2:B5

Результат обчислень в режимі формул представлений на рис. 20.

 

Рис. 20

Функції ЕСЛИ, И та ИЛИ

Принцип дії більшості логічних функцій Excel полягає в перевірці деякої умови і виконання в залежності від неї тих або інших дій. Функція ЕСЛИ виконує перевірку умови, що задається першим аргументом логічного виразу:

=ЕСЛИ (лог_выражение; значение_если_истина; значение_если_ложь)

лог_выражение – це будь-яке значення або вираз, що приймає значення ИСТИНА або ЛОЖЬ. Наприклад, А10=100 – це логічний вираз; якщо значення в клітині А10 дорівнює 100, то вираз приймає значення ИСТИНА. У іншому випадку – ЛОЖЬ.

значение_если_истина – це значення, що повертається, якщо лог_выражение дорівнює ИСТИНА. Якщо лог_выражение дорівнює ИСТИНА, а значение_если_истина порожньо, то повертається значення 0. Значення значение_если_истина може бути формулою.

значение_если_ложь – це значення, що повертається, якщо лог_выражение ЛОЖЬ. Якщо лог_выражение дорівнює ЛОЖЬ, а значение_если_ложь відсутнє (тобто після значение_если_истина немає крапки з комою), то повертається логічне значення ЛОЖЬ. Якщо лог_выражение дорівнює ЛОЖЬ, а значение_если_ложь порожньо (тобто після значение_если_ложь стоїть крапка з комою з наступною закриваючою дужкою), то повертається значення 0. Значення значение_если_ложь може бути формулою.

Зауваження: до 7 функцій ЕСЛИ можуть бути вкладені одна в одну як значення значение_если_истина і значение_если_ложь.

 

Наприклад, якщо ціна за одиницю перевищує середнє значення ціни, то товар належить до 1-ої групи, інакше – до 2-ої (див. табл.1).

 

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

1. Активізуємо клітину G2, , в діалоговому вікні вибираємо Категория: Логические, Функция: ЕСЛИ, ОК.

2. В наступному діалоговому вікні (рис. 21) вказуємо умову та дії, якщо умова виконується або не виконується.

 

Рис. 21

Результат обчислень представлений на рис. 22.

 

Рис. 22

Додаткові логічні функції:

Функція И використовується тоді, коли потрібно перевірити виконання декількох умов одночасно. Одне з найбільш частіших використовувань функції И - є перевірка чи попадає число х до діапазону від х1 до х2.

=И(логическое_значение1; логическое_значение2;...)

Функція И приймає від 1 до 30 аргументів (в Office 2007 -- до 256), повертає значення ИСТИНА, якщо всі аргументи дійсні, та ЛОЖЬ, якщо хоч один аргумент недійсний.

 

Приклад, якщо кількість товару лежить в межах від нуля до 2 включно, потрібно у поле тара занести 1 кульок, інакше – 2 кулька.

 

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

1. Активізуємо клітину Н2, , в діалоговому вікні вибираємо Категория: Логические, Функция: ЕСЛИ, ОК.

2. В наступному діалоговому вікні заповнюємо поля Значение_если_истина та Значение_если_ложь (рис. 23), ставимо курсор у поле Лог_выражение та натискаємо на у лівій частині рядка формул, у розкриваючому списку вибираємо Другие функции…, вибираємо Категория: Логические, Функция: И, ОК.

3. В діалоговому вікні заповнюємо (рис. 24) відповідні поля і натискаємо ОК.

Рис. 24

Рис. 23

Результат представлений на рис. 25

 

Рис. 25

Функція ИЛИ

Функція ИЛИ використовується тоді, коли потрібно перевірити, чи виконується хоча б одне із багатьох умов.

=ИЛИ(логическое_значение1; логическое_значение2;...)

Функція ИЛИ приймає від 1 до 30 аргументів (в Office 2007 -- до 256), повертає значення ИСТИНА, якщо хоч би один із аргументів є ИСТИНА.

Зауваження: функції И та ИЛИ в переважній більшості не використовуються самі по собі, найчастіше їх використовують в якості аргументів інших функцій, наприклад, ЕСЛИ.

Вставка вкладених функцій

В одну функцію можна вставити іншу функцію. Дозволяється до 7-ми рівнів вкладення функцій (в Office 2007 - до 64). Існує дуже зручний спосіб вкладення функції – спеціальна кнопка у лівій частині рядка формул, що відкриває розкриваючий список, в якому відображуються функції, що недавно використовувалися і пункт Другие функции…

Приклад, в залежності від категорії (рис. 26) тарифна ставка набуває наступних значень

Рис. 26

Виконання:

1. Активізуємо клітину C2, , в діалоговому вікні вибираємо Категория: Логические, Функция: ЕСЛИ, ОК.

2. Заповнюємо перші два вікна відповідними значеннями (рис.27), ставимо курсор у третє вікно і натискаємо кнопку , у розкриваючому списку (рис. 28), вибираємо функцію ЕСЛИ, ОК.

3. З’являється діалогове вікно функції ЕСЛИ, яка буде вкладеною функцією і заповнюємо його відповідно умові (рис. 29).

Рис. 27

Рис. 28

Рис. 29

 

Результат виконання представлений на рис 30.

Рис. 30

Умовне форматування

При умовному форматуванні використовується КМ Формат - Условное форматирование… За допомогою цього інструмента Excel сам форматує клітини в залежності від умови.

Наприклад, потрібно щоб вартість товару що перевищує 30 грн. мала червоний колір, тип шрифту полужирный курсив (див. табл. 1).

 

Виконання:

1. Виділяємо клітини D2: D5

2. КМ Формат - Условное форматирование… У діалоговому вікні Условное форматирование вибираємо умову больше (рис. 31).

Рис. 31

Праворуч вказуємо значення 30 (рис. 32).

Рис. 32

Натискаємо кнопку Формат… та вибираємо потрібний червоний колір та тип накреслення шрифту (рис. 33), кнопка ОК. У діалоговому вікні Условное форматирование знову натискаємо кнопку ОК.

Рис. 33

Натиснувши на кнопці А также >> можна задати ще одну умову для умовного форматування.

Результат умовного форматування представлено на рис. 34.

 

 

Рис. 34

 

Зауваження: в MS Excel версії до 2003 включно можна використати не більше трьох умов.

Робота з функціями списків

Списком називають таблицю, рядки якої містять однотипну інформацію.

Microsoft Excel пропонує 14 функцій для роботи зі списками. Кожна з цих функцій повертає інформацію про елементи списку, які задовольняють деяким умовам.

Функція СЧЕТЕСЛИ (діапазон;критерій) підраховує кількість елементів у діапазоні, які задовольняють критерію.

Діапазон записується у вигляді блока, критерій записується у виді текстового рядка, що містить умови.

Наприклад, щоб підрахувати кількість співробітників, у яких кількість дітей більше одного (див. табл. 2).

Виконання:

1. Активізуємо клітину B8, , в діалоговому вікні вибираємо Категория: Статистические, Функция: СЧЕТЕСЛИ, ОК.

2. Вводимо діапазон клітин, що перевіряються та критерій (рис. 35)

Рис. 35

3. Результат представлений на рис. 36

Функція СУММЕСЛИ (діапазон;критерій;діапазон_підсумовування) підсумовує значення в зазначеному діапазоні, з огляду тільки на ті записи, що задовольняють критерію.

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

 

Виконання:

1. Активізуємо клітину B8, , в діалоговому вікні вибираємо Категория: Математические, Функция: СУММЕСЛИ, ОК.

2. Вводимо діапазон клітин, що перевіряються та критерій та діапазон підсумовування (рис. 37).

Рис. 37

3. Результат представлений на рис. 36.

Таблиця 2

Рис. 36

Результат у формульному вигляді має вигляд (рис. 38):

Рис. 38

Сортування даних

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

В Excel використовується наступна послідовність упорядкування даних за замовченням:

1. числа від найменшого від’ємного до найбільшого додатного числа;

2. текст – за алфавітом;

3. логічне значення ложь;

4. логічне значення истина;

5. значення помилок;

6. пусті значення

 

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

Способи упорядкування

1) Просте упорядкування

На стандартній панелі інструментів Excel розташовані дві кнопки для упорядкування таблиці за зростанням та спаданням. Для упорядкування даних потрібно виконати наступне:

1. виділити клітину в стовпчику, який повинен бути ключем сортування;

2. натиснути кнопку Сортировка по возрастанию або Сортировка по убыванию.

Таким способом можна упорядкувати дані тільки по одному полю, всі інші дані залишаються без змін.

 

2) Упорядкування по декільком полям

За замовченням рядки виділеної таблиці упорядковуються по вмісту одного із стовпчиків. Існує можливість упорядкування таблиці по двом і трьом полям одночасно. Для упорядкування даних потрібно виконати:

1. виділити таблицю разом із її структурою («шапкою»);

2. КМ Данные – Сортировка

3. В діалоговому вікні Сортировка диапазона перевірити установку перемикача Идентифицировать поля по Якщо таблиця містить заголовки стовпчиків, то повинно бути вибрано положення подписям (первая строка диапазона). В цьому випадку найменування стовпчиків не приймають участі в упорядкуванні. Якщо заголовків немає, то потрібно вибрати положення обозначениям столбцов листа.

4. В розкриваючому списку Сортировать по вибрати перше поле, по якому потрібно упорядкувати дані. Це поле називається первинним ключем сортування.

5. Вибрати положення перемикача по возрастанию або по убыванию;

6. Вибрати імена полів, якщо потрібно, в розкриваючих списках Затем по та В последнюю очередь, по. Друге поле упорядкування використовується, якщо виникає повторення значення першого поля, а третє поле - коли повторюються значення і першого, і другого полів;

7. Натискаємо кнопку ОК.

Фільтрація даних

За допомогою фільтрів можна виводити та переглядати тільки ті дані, що задовольняють визначеним умовам. Ехсel дозволяє швидко і зручно переглядати необхідні дані зі списку за допомогою простого засобу - Автофільтру. Складніші запити до бази даних можна реалізувати за допомогою команди Расширенный фильтр.

 

Автофільтр

Щоб використовувати автофільтр, треба спочатку виділити для пошуку область таблиці із заголовками полів. Потім вибрати КМ Данные – Фильтр – Автофильтр. Після встановлення автофільтру в клітинах заголовку таблиці з’являються кнопки у вигляді трикутників. При натисканні на кнопці з’являється розкриваючий список відповідного поля (стовпчика), який містить наступні опції:

§ (Все) – для вибору вмісту всього списку або для відображення всього списку після фільтрації;

§ (Первые 10…) – дозволяє вивести 10 (або вказану кількість) найбільших (найменших) елементів списку;

§ (Точное значение) – дозволяє вивести лише ті значення, поля яких в точності співпадають з обраним значенням;

§ (Условие…) – для встановлення критеріїв фільтрації. Критерій може містити не більше ніж дві умови, об’єднаних операціями И, ИЛИ. Кожна із умов являє собою вираз логічного типу, що містить будь-які операції відношень (<, <=, =, <>, >, >=). В автофільтрі ці операції представлені у вигляді тексту і призначені для створення умов переважно по числовим полям та полям типу дата та час. Для створення умов по текстовим полям призначені обмеження начинается с, не начинается с, заканчивается на, не заканчивается на, содержит, не содержит. Крім цього, при створенні текстових критеріїв можна використовувати символи шаблону:

“*” – представляє будь-яку кількість символів;

“?” – для представлення одного символу, що стоїть на визначеному місці.

 

Відмінити автофільтр можна тією самою послідовністю КМ Данные – Фильтр - Автофильтр.

Наприклад: в таблиці 3 потрібно знайти та вивести 3 найкращих студентів з математики.

Натискаємо на на полі Математика, вибираємо опцію (Первые 10), задаємо 3 найбільших елементів списку (рис. 49). Результат представлений на рис. 50.

Рис. 49

Рис. 50

Наприклад: в таблиці 3 потрібно знайти та вивести всіх студентів, у яких оцінка з математики знаходиться в межах від 3 до 4. Задання критерію відбору представлено на рис. 51.

 

Рис. 51

Варіант 1

В автоколоні працюють автомобілі чотирьох марок, по чотири машини кожної марки. Кожна машина має унікальний (єдиний). Коди машин повинні починатися з цифри номера групи: наприклад, першої групи – 101,102,103 і т. д.; другої – 201,202,203 і т.д.; третьої – 301,302,303 і т. д. Норма витрат пального на 100 км залежить від виду пального і становить: дизельне пальне – 11 л, газ – 10 л, АИ-92 – 9,5 л, АИ-95 – 8 л. Ціни за 1 л пального становлять: дизельне пальне – 9,25 грн., газ – 4,88 грн., АИ-92 – 9,47 грн., АИ-95 –9,76 грн.

Ярлик сторінки «Лист1» створюваної Робочої книги назвати «РобТабл» і на цій сторінці створити таблицю наступної структури: Код автомобіля, Марка автомобіля, Вид пального, Витрати пального, Ціна за пальне, Пробіг за тиждень, Витрати.

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури: Вид пального, Ціна на пальне, Витрати пального на 100 км.

 

Заповнення даними вихідної таблиці відбувається таким чином:

1) для введення даних у колонки Код автомобіля, Марка автомобіля, Вид пального організувати списки, що розкриваються;

2) поля Ціна на пальне та Витрати пального заповнити, використовуючи функцію ВПР зі звертанням до таблиці, що знаходиться на аркуші «Довідник»;

3) поле Витрати обчислюється як Ціна на пальне • (Пробіг за тиждень/100 • Витрати пального).

 

Завдання 1.1. Упорядкування

Витрати – за збільшенням;

Марка автомобіля – за алфавітом (первинний ключ), Пробіг за тиждень – за збільшенням (вторинний ключ);

Вид пального – за алфавітом (первинний ключ), Ціна на пальне – за зменшенням (вторинний ключ), Витрати – за зменшенням (третинний ключ).

 

Завдання 1.2. Фільтрація

Вид пального – дизельне;

Пробіг за тиждень знаходиться в межах від мінімального до середнього значення відповідного поля;

Пробіг за тиждень – найбільший;

Витрати – три найменші.

 

Завдання 1.3. Проміжні підсумки

для кожної марки автомобіля обчислити загальні витрати та максимальну ціну за пальне (аркуш «Проміжні підсумки»);

для кожного виду пального обчислити кількість марок автомобілів (аркуш «Проміжні підсумки_1»).

 

Варіант 2

П’ять магазинів замовляють на складі по 5 книг трьох авторів. Номери магазинів повинні починатися з цифри номера групи: наприклад, першої групи – 101,102,103 і т. д.; другої – 201,202,203 і т.д.; третьої – 301,302,303 і т. д.

Ярлик сторінки «Лист1» створюваної Робочої книги назвати «РобТабл» і на цій сторінці створити вихідну таблицю наступної структури: Номер магазину, Код книжки, Автор, Назва книжки, Ціна за одиницю, Фактична ціна за одиницю, Замовлена кількість, Загальна вартість замовлення.

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури:Код книжки, Назва книжки.

 

Заповнення даними вихідної таблиці відбувається таким чином:

1) для введення даних у колонки Номер магазину, Код книжки, Автор організувати списки, що розкриваються;

2) поле Назва книжки заповнити, використовуючи функцію ВПР зі звертанням до таблиці, що знаходиться на аркуші «Довідник»;

3) відомо, що якщо замовлення магазину становить більше 30 примірників, то склад зменшує Ціну за одиницю на 10%, а за 50 і більше примірників – на 15% – це і буде фактична ціна за одиницю. Поле Загальна вартість замовлення обчислюється як (Фактична ціна за одиницю • Замовлену кількість).

 

Завдання 1.1. Упорядкування

Замовлена кількість – за збільшенням;

Автор – за алфавітом (первинний ключ), Замовлена кількість – за збільшенням (вторинний ключ);

Номер магазину – за збільшенням (первинний ключ), Автор – за алфавітом (вторинний ключ), Ціна за одиницю – за зменшенням (третинний ключ).

 

Завдання 1.2. Фільтрація

а) Автор – на літеру «Д»;

б) Загальна вартість замовлення перевищує середнє значення відповідного поля;

в) Замовлена кількість – найбільша;

г) Фактична ціна за одиницю – три найменші.

 

Завдання 1.3. Проміжні підсумки

по кожному автору обчислити загальну замовлену кількість та максимальну фактичну ціну за одиницю (аркуш «Проміжні підсумки»);

для кожної книжки обчислити загальну вартість замовлення (аркуш «Проміжні підсумки_1»).

 

Варіант 3

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

Ярлик сторінки «Лист1» створюваної Робочої книги назвати «РобТабл» і на цій сторінці створити таблицю наступної структури: Назва господарства, Прізвище фермера, Код продукції, Назва продукції, Ціна за 1 кг, Обсяг поставки, кг, Вартість поставки. Коди продукції повинні починатися з цифри номера групи: наприклад, першої групи – 101,102,103 і т. д.; другої – 201,202,203 і т.д.; третьої – 301,302,303 і т. д.

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури: Код продукції, Ціна за 1 кг.

 

Заповнення даними вихідної таблиці відбувається таким чином:

1) для введення даних у колонки Назва господарства, Прізвище фермера, Назва продукції організувати списки, що розкриваються;

2) поле Ціна за 1 кг заповнити, використовуючи функцію ВПР зі звертанням до таблиці, що знаходиться на аркуші «Довідник»;

3) поле Вартість поставки обчислюється як добуток Ціна за 1 кг на Обсяг поставки, кг.

 

Завдання 1.1. Упорядкування

Назва господарства – за алфавітом;

Прізвище фермера – за алфавітом (первинний ключ), Обсяг поставки, кг – за збільшенням (вторинний ключ);

Назва продукції – за алфавітом (первинний ключ), Прізвище фермера – за алфавітом (вторинний ключ), Вартість поставки – за зменшенням (третинний ключ).

 

Завдання 1.2. Фільтрація

Назва господарства – будь-яке, вибране Вами;

Обсяг поставки, кг – в межах від мінімального до середнього значення відповідного поля;

Назва продукції – «молоко»;

Вартість поставки – три найбільші.

 

Завдання 1.3. Проміжні підсумки

для кожної продукції обчислити середню ціну за 1 кг і та мінімальний обсяг поставки (аркуш «Проміжні підсумки»);

по кожному господарству обчислити загальний обсяг поставок та кількість видів продукції, що постачаються (аркуш «Проміжні підсумки_1»).

 

Варіант 4

 

Деревообробне підприємство виробляє п’ять видів продукції: дошки, парти, столи, шафи, стільці. П’ять шкіл замовляють продукцію цього підприємства.

Ярлик сторінки «Лист1» створюваної Робочої книги назвати «РобТабл» і на цій сторінці створити таблицю наступної структури: № школи, Дата замовлення, Код товару, Назва товару, Кількість, Ціна за одиницю, Фактична ціна, Сума до сплати. Номери шкіл повинні починатися з цифри номера групи: наприклад, першої групи – 101,102,103 і т. д.; другої – 201,202,203 і т.д.; третьої – 301,302,303 і т. д.

На сторінці з ярликом «Лист2» створюваної Робочої книги (замінити його назву на «Довідник») створити таблицю наступної структури: Код товару, Ціна за одиницю.

 

Заповнення даними вихідної таблиці відбувається таким чином:

1) для введення даних у колонки № школи, Код товару, Назва товару організувати списки, що розкриваються;

2) поле Ціна за одиницю заповнити, використовуючи функцію ВПР зі звертанням до таблиці, що знаходиться на аркуші «Довідник»;

3) відомо, що якщо обсяг поставки відповідної продукції становить більше 30 одиниць, то ціна за одиницю зменшується на 10% – це і буде фактична ціна, інакше фактична ціна співпадає з ціною за одиницю. Поле Сума до сплати обчислюється як добуток Фактичної ціни на Кількість.

 

Завдання 1.1. Упорядкування

Назва товару – за алфавітом;

Назва товару – на літеру «ш» (первинний ключ), Кількість – за збільшенням (вторинний ключ);

№ школи – за збільшенням (первинний ключ), Прізвище фермера – за алфавітом (вторинний ключ), Сума до сплати – за зменшенням (третинний ключ).

 

Завдання 1.2. Фільтрація

Назва товару – будь-яка, вибрана Вами;

Сума до сплати – в межах від середнього до максимального значення відповідного поля;

Ціна за одиницю – найбільша;

Всі замовлення – на вказану дату.

 

Завдання 1.3. Проміжні підсумки

для кожної школі обчислити загальні суми до сплати та мінімальну кількість поставки (аркуш «Проміжні підсумки»);

по кожній даті замовлення обчислити максимальну фактичну ціну та кількість видів продукції, що постачаються (аркуш «Проміжні підсумки_1»).

 

Варіант 5

Бухгалтер ЖЕКу розраховує відомість оплати за газ та опалення. В коло його обов’язків входить виконати розрахунок сплати послуг для двох будинків (№9, №11) по вул. Приозерній та один будинок (№5) по вул. Квітковій. В таблицю занести дані по п’яти квартирах кожного будинку. Відомо, що будинок №9 по вул. Приозерній оснащений електричними плитами.