Теоретичні відомості. Крім чисел, тексту й адрес комірок формула може містити вбудовані в Excel функції

Крім чисел, тексту й адрес комірок формула може містити вбудовані в Excel функції.

Функції — це наперед визначені формули, які виконують обчислення за заданими величинами, які називають аргументами в зазначеному порядку. Ці функції дають змогу виконувати прості та складні обчислення.

Функція починається зі знака рівності (=), за ним іде ім'я функції, дужка, список аргументів, розділених комами, дужками.

Щоб відобразити список доступних функцій, клацніть на комірці та натисніть клавіші Shift + F3.

Аргументи. Функція в Microsoft Excel автоматизує процес обчислення певної величини або значення. Її можна використати тільки у формулі, записавши її ім'я та значення аргументів у круглих дужках: ім'я функції(аргументи). Якщо функція має кілька аргументів, їх розділяють символом крапки з комою «;».

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

• Математичні функції в Microsoft Excel реалізують ті ж обчислення, що й відповідні функції в математиці.

• Призначення статистичних функцій — обчислення статистичних показників для наборів значень.

• Логічні функції використовують для обробки виразів, значення яких істинні або хибні.

• Інформаційні функції застосовують для визначення типу й формату даних, а також наявності помилок у певних клітинках і їх типу.

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

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

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

Логічні умови використовують такі знаки: >, <, =, >=, <=, <> AND (умова1;умова2;умова3;...)

IF (логічна умова; дія за умови TRUE; дія за умови FALSE) OR (умова1;умова2;...)

Майстер функцій – підпрограма, що містить покрокові інструкції, виконавши які можна розв’язати задачу. Діалогове вікно Майстер функцій з’являється на екрані після клацання мишкою по піктограмі «Вставка функції f_x». У програмі Excel «Майстер функцій» містить більше 350 функцій, розподілених за тринадцятьма категоріями.

Порядок роботи з підпрограмою «Майстер функцій»:

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

2. Викликати діалогове вікно «Майстер функцій».

3. Вибрати «Категорію» функції, потім вибрати ім’я функції, ОК.

4. Виконувати покрокові інструкції підпрограми.

Кожне з вікон майстра має клавішу «Довідка», натиснувши на яку можна викликати на екран опис правил роботи з функцією. Докладну інформацію про «Майстер функцій» можна одержати в головній довідковій системі програми в розділі «Майстер функцій».

Завдання 3. За допомогою функції «ДЕНЬНЕД», визначити порядковий номер тижня.

Послідовність дій при виконанні завдання:

4. Відкрити файл Додаток 18_03-13. Перейти на робочий лист Приклад.

5. Зробити активною комірку F3, викликати «Майстер функцій», вибрати категорію «Дата и время».

6. Вибрати функцію «ДЕНЬНЕД», ОК, у вікно, що з’явилося, ввести: Адресу комірки F4 (в неї записано дату), ОК.

7. Після цього в комірці F3 повинно з’явитися число, що вказує номер дня тижня для цієї дати. Перший порядковий номер відповідає неділі, другий – понеділку і так далі. Для того, щоб повторити обчислення для іншої дати, досить в комірку F4 ввести нову дату.

Завдання 4. За допомогою фінансової функції «ПЛТ» визначити суму внеску за місяць (грн.) для погашення кредиту в розмірі 1 млн. грн. під місячну ставку 0,75%, при щомісячних платежах протягом 12 місяців.

Послідовність дій при виконанні завдання:

1. На робочому листі Приклад зробити активною комірку F6, викликати «Майстер функцій», вибрати категорію «Финансовые», вибрати функцію «ПЛТ», ОК, у вікно, що з’явилося, ввести:

• Ставка – адреса комірки, де записано величину процентної ставки – F8;
• Кпер - адреса комірки, де записано кількість платежів – F9;
• Пс - адреса комірки, де записано суму отриманого кредиту – F7;
• Після клацання по кнопці ОК в комірці F6 повинна з’явитися сума щомісячного внеску, що дорівнює -87451 грн., а в рядку формул видно формулу, за якою зроблено обчислення =ПЛТ(F8;F9;F7)

Зауваження: функція ПЛТ в деяких пакетах програм може мати назву ППЛАТ.

Завдання 5. За допомогою статистичної функції «МЕДИАНА» визначити Медіану заданого масиву чисел.

Медіана — це число, що є серединою множини чисел, тобто половина чисел мають значення більші за медіану, а половина чисел мають значення менші, ніж медіана.

Послідовність дій при виконанні завдання:

1. На робочому листі Приклад зробити активною комірку F11, викликати «Майстер функцій», вибрати категорію «Статистические», вибрати функцію «МЕДИАНА», ОК, у вікно, що з’явилося, ввести адресу діапазону комірок H12:AF12.

2. Після клацання по кнопці ОК в комірці F11 повинно з’явитися число 46, що є медіаною для даного ряду чисел, а в рядку формул видно формулу, за якою зроблено обчислення =МЕДИАНА(H12:AF12).

Завдання 6. За допомогою статистичної функції «ПЕРЕСТ» підрахувати число переміщень з 1000 по 3.

Послідовність дій при виконанні завдання:

1. На робочому листі Приклад зробити активною комірку F14, викликати «Майстер функцій», вибрати категорію «Статистические», вибрати функцію «ПЕРЕСТ», ОК, у вікно, що з’явилося, ввести: число – 1000, обране число – 3.

2. Після клацання по кнопці ОК в комірці F14 повинно з’явитися число 997 002 000, а в рядку формул видно формулу, за якою зроблено обчислення =ПЕРЕСТ(1000;3).

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

Послідовність дій при виконанні завдання:

1. На робочому листі «Число» зробити активною комірку О3, викликати «Майстер функцій», вибрати категорію «Статистические», вибрати функцію «СЧЁТЕСЛИ», ОК, у вікно, що з’явилося, ввести: діапазон - B15:O54, критерий - G3 (адреса комірки, у яку записано нуль).

2. Після клацання по кнопці ОК в комірці О3 повинно з’явитися число 48 (саме стільки нулів знаходиться в блоці комірок), а в рядку формул видно формулу, за якою зроблено обчислення =СЧЁТЕСЛИ(B15:O54;G3).

3. Для знаходження кількості інших чисел можна або виконати для них вказані вище дії, або розмножити (обов’язково з урахуванням правил посилання на комірки).

Завдання 8 (Контрольне). На робочому листі «Слово» в блоці комірок І4:І14 підрахувати, скільки разів кожне зі слів записано в блок комірок В16:І35.

Завдання 9. За допомогою текстової функції «ДЛСТР» підрахувати кількість символів, що містяться в заданій фразі.

Послідовність дій при виконанні завдання:

1. На робочому листі Приклад зробити активною комірку F16, викликати «Майстер функцій», вибрати категорію «Текстовые», вибрати функцію «ДЛСТР», ОК, у вікно, що з’явилося, ввести: Е18 – адресу комірки, в яку записано фразу – Як тебе не любити, Києве мій.

2. Після клацання по кнопці ОК в комірці F16 повинно з’явитися число 29 – число символів в даній фразі, а в рядку формул видно формулу, за якою зроблено обчислення =ДЛСТР(E18).

Завдання 10. За допомогою текстової функції «СЦЕПИТЬ» об’єднати текстові дані.

Зразок виконання завдання:

	C	D	E	F
	Пушкін	Олександр	Сергійович	Пушкін Олександр Сергійович
	Кирилов	Юрій	Борисович	Кирилов Юрій Борисович
13-20	…	…	…	…
	Гоголь	Микола	Васильович	Гоголь Микола Васильович

Послідовність дій при виконанні завдання:

1. На робочому листі «Зчепити» зробити активною комірку F11, викликати «Майстер функцій», вибрати категорію «Текстовые», вибрати функцію «СЦЕПИТЬ», ОК, у вікно, що з’явилося, ввести:

• Текст1 – адресу комірки з першим словом Пушкін – С11;
• Текст2 – ввести один пробіл;
• Текст3 - адресу комірки з другим словом Олександр – D11;
• Текст4 - ввести один пробіл;
• Текст5 - адресу комірки з третім словом Сергійович – Е11.

2. Після клацання по кнопці ОК в комірці F11 повинно з’явитися фраза «Пушкін Олександр Сергійович», а в рядку формул видно формулу, за якою зроблено обчислення =СЦЕПИТЬ(C11;" ";D11;" ";E11).

3. Для заповнення інших комірок стовпчика F необхідно розмножити формулу з комірки F11 на діапазон комірок F11: F21.

Завдання 11. Застосування логічної функції «ЕСЛИ». На робочому аркуші «Приклад» в комірку F20 записати формулу, за якою у неї буде записуватися слово «вірно» тільки тоді, коли в комірці F21 з’явиться число 5, у всіх інших випадках у F20 повинно з’являтися слово «помилка».

Послідовність дій при виконанні завдання:

1. На робочому листі Приклад зробити активною комірку F20, викликати «Майстер функцій», вибрати категорію «Логические», вибрати функцію «ЕСЛИ», ОК, у вікно, що з’явилося, ввести:

• Логічний вираз - F21=5;
• Значення істинне – вірно; Значення хибне – помилка.

2. Після клацання по кнопці ОК в комірці F20 повинно з’явитися слово «помилка», а в рядку формул видно формулу, за якою зроблено обчислення =ЕСЛИ(F21=5;"вірно";"помилка"). Якщо в комірку F21 ввести число 5, то в комірці F20 з’явиться слово «вірно».

Завдання 12. За допомогою математичної функції «РИМСКОЕ» перевести число з арабської форми запису в римську форму запису.

Послідовність дій при виконанні завдання:

1. На робочому листі Приклад зробити активною комірку F24, викликати «Майстер функцій», вибрати категорію «Математические», вибрати функцію «РИМСКОЕ», ОК, у вікно, що з’явилося, ввести: F25 – адресу комірки, у яку записано число 2001.

2. Після клацання по кнопці ОК в комірці F24 повинно з’явитися число ММІ – число у римській формі запису, а в рядку формул видно формулу, за якою зроблено обчислення =РИМСКОЕ(F25).

Завдання 13. За допомогою математичної функції «СУММЕСЛИ» підрахувати кількість товарів, проданих працівниками першого відділу.

Послідовність дій при виконанні завдання:

1. На робочому листі СУММЕСЛИ зробити активною комірку Е16, викликати «Майстер функцій», вибрати категорію «Математические», вибрати функцію «СУММЕСЛИ», ОК, у вікно, що з’явилося, ввести:

• Діапазон – D8:D13; Критерій – 1; Діапазон підсумовування – Е8:Е13.

2. Після клацання по кнопці ОК в комірці Е16 повинно з’явитися число 25 000, а в рядку формул видно формулу, за якою зроблено обчислення =СУММЕСЛИ(D8:D13;1;E8:E13).

3. Підрахуйте суму зарплат працівників третього відділу.

Завдання 14. За допомогою математичної функції «СУММПРОИЗВ» підрахувати виторг кожного продавця.

Послідовність дій при виконанні завдання:

1. Відкрити файл Додаток 18_03-14. Перейти на робочий лист Приклад.

2. Зробити активною комірку С15, викликати «Майстер функцій», вибрати категорію «Математические».

3. Вибрати функцію «СУММПРОИЗВ», ОК.

4. Для визначення адреси «Масив1» - виділити блок комірок В7:В14.

5. Для визначення адреси «Масив2» - виділити блок комірок С7:С14, ОК.

8. В комірці С15 повинно з’явитися число 372 – виторг першого продавця, що є результатом додавання добутків чисел, записаних в комірки: B7 і C7; B8 і C8; B9 і C9; B10 і C10; B11 і C11; B12 і C12; B13 і C13; B14 і C14. У рядку формул видно формулу =СУММПРОИЗВ(B7:B14;C7:C14).

9. Аналогічно підрахувати виторг інших продавців. Підсумкова таблиця набере наступного вигляду:

Найменування товарів	Ціни товарів	Порядкові номери продавців
Товар 1
Товар 2
Товар 3
Товар 4
Товар 5
Товар 6
Товар 7
Товар 8
	Виторг(грн):

Завдання 15. Формування навичок раціонального способу роботи з функцією «СУММ» за допомогою команди «Автосуммирование».

1. Підрахувати сумарний виторг усіх продавців.

2. Підрахувати виторг працівників з непарними номерами.

3. Підрахувати виторг працівників з парними номерами.

4. Підрахувати виторг перших семи працівників.

5. Підрахувати сумарний виторг 3, 4, 6, 7 і 9 працівників.

Послідовність дій при виконанні 1-го пункту завдання:

1. Зробити активною комірку К17. Клацнути по піктограмі (Автосумма).

2. Виділити блок комірок С15:L15, ОК.

3. В комірці К17 повинно з’явитися число 25170, що дорівнює сумарному виторгу десяти продавців.

4. Розрахунки для завдань за пунктами 2-5 виконати самостійно.

5. Виконати обчислення за номером свого варіанту.

Завдання 16 (Контрольне). Відкрити файл Додаток 18_16.

Надано:

1. У таблицях на робочих аркушах з іменами «Варіант №…» надано списки студентів, що виконали 15 тестів і по кожному з них набрали: 0, 2 або 4 бали.

2. На листі «Коефіцієнти» надано таблицю з коефіцієнтами складності, що присвоєно кожному з 15-ти тестів. Ці коефіцієнти повинні враховуватися при підведенні підсумків шляхом множення коефіцієнта на бали за виконання тестів.

Потрібно:

1. Підрахувати сумарну кількість балів, набрану кожним студентом.

2. Відсортувати список студентів за кількістю набраних балів, починаючи з найбільшого.

3. Записати в звіт прізвища і кількість балів трьох перших студентів.

4. Результат роботи відправити на електронну скриньку куратора (викладача)-тьютора: [email protected]

Завдання 17. Напишіть висновок до лабораторної роботи.

ІІІ етап (виконується вдома) Дата ______

Завдання 18. За контрольними запитаннями підготуйтесь до захисту лабораторної роботи.

Дата добавления: 2015-05-24