Завдання. 1. Поняття функції в Microsoft Excel

План.

1. Поняття функції в Microsoft Excel.

2. Логічні функції в Microsoft Excel.

3. Приклад виконання задачі за допомогою логічних функцій.

Література

1. Додж М, Кината К., Стинсон К., Ефективна робота з Excel 7/0 для Windows 95/Пер. с англ. Спб:Питер, 1996 -1040с.

2. Додж М., Кината К., Стинсон К., Ефективна робота з Excel 2000 - Спб: Видавництво «Питер», 2000. - 1056с.

3. Інформатика та комп'ютерна техніка: Навчальний посібник. - К.: НМЦ "Укоопосвіта", 2000 -335с.

4. Новиков Ф., Яценко A. Office 97 у цілому. - Спб: BHV - Санкт-Петербург, 1998 - 624с.

5. Хзлворсон М., Янг М. Ефективна робота з Microsoft Office 2000 - Спб: Видавництво «Питер», 2000. - 1232 с.

1. Поняття функції в Microsoft Excel.

Використання функцій для обчислення значень

Функції — заздалегідь визначені формули, що виконують обчислення по заданих величинах, називаним аргументами, і в зазначеному порядку. Наприклад, функція СУМ підсумовує значення в діапазоні осередків, а функція ППЛАТ обчислює величину виплати за один період річної ренти на основі постійних виплат і постійної процентної ставки.

Аргументи Список аргументів може складатися з чисел, тексту, логічних величин (наприклад ІСТИНА або НЕПРАВДА), масивів, значень помилок (наприклад #Н/Д) або посилань. Необхідно стежити за відповідністю типів аргументів. Крім того, аргументи можуть бути як константами, так і формулами. Ці формули, у свою чергу, можуть містити інші функції.

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

2. Логічні функції в Microsoft Excel.

І (И) - Повертає значення ІСТИНА, якщо всі аргументи мають значення ІСТИНА.

НЕПРАВДА (ЛОЖЬ)-Повертає логічне значення НЕПРАВДА.

ЯКЩО (ЕСЛИ)-Визначає виконувану логічну перевірку.

НІ (НЕ) - Змінює на протилежне логічне значення свого аргументу.

АБО (ИЛИ)-Повертає ІСТИНА, якщо хоча б один аргумент має значення ІСТИНА.

ІСТИНА -повертає логічне значення ІСТИНА.

Розглянемо функції:

ЯКЩО- Повертає одне значення, якщо задана умова при обчисленні дає значення ІСТИНА, і інше значення, якщо НЕПРАВДА.

Функція ЯКЩО використовується для умовної перевірки значень і формул.

Синтаксис 1

ЯКЩО (лог_вираження;значення_якщо_істина;значення_якщо_неправда)

Лог_вираження - це будь-яке значення або вираження, що при обчисленні дає значення ІСТИНА або НЕПРАВДА.

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

Значення_якщо_неправда - це значення, що повертається, якщо лог_вираження має значення НЕПРАВДА. Якщо лог_вираження має значення НЕПРАВДА і значення_якщо_неправда опущена, то повертається значення НЕПРАВДА. Значення_якщо_неправда може бути іншою формулою.

Зауваження

· До 7 функцій ЯКЩО можуть бути вкладені друг у друга як значення аргументів значення_якщо_істина і значення_якщо_неправда, щоб конструювати більш складні перевірки, див. останній із приведених нижче прикладів.

· Функція ЯКЩО завжди повертає значення, що повертається обчисленим аргументом значення_якщо_істина і значення_якщо_неправда.

· Якщо який-небудь аргумент функції ЯКЩО є масивом, то при виконанні функції ЯКЩО обчислюється кожен елемент масиву. Якщо який-небудь з аргументів значення_якщо_істина або значення_якщо_неправда є дією, то всі дії виконуються.

Приклади

У наступному прикладі, якщо значення осередку A10 - 100, те лог_вираження має значення ІСТИНА й обчислюється сума для осередків B5:B15. У противному випадку лог_вираження має значення НЕПРАВДА і повертається порожній текст (""), що очищає осередок, що містить функцію ЯКЩО.

ЯКЩО(A10=100;СУМ(B5:B15);"")

Припустимо, що робітничий лист по витратах містить в осередках B2:B4 фактичні витрати за січень, лютий, березень: 1500, 500 і 500 відповідно. Осередку C2:C4 містять дані по передбачуваних витратах за ті ж періоди: 900, 900 і 925.

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

ЯКЩО(B8>C8;"Перевищення бюджету";"OK") дорівнює "Перевищення бюджету"

ЯКЩО(B9>C9;"Перевищення бюджету";"OK") дорівнює "OK"

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

Середній Бал Категорія

Більше 89 A

Від 80 до 89 B

Від 70 до 79 C

Від 60 до 69 D

Менше 60 F

Тоді можна використовувати вкладені функції ЯКЩО:

ЯКЩО(Середній балл>89;"A";ЯКЩО(Середній балл>79;"B";

ЯКЩО(Середній балл>69;"C";ЯКЩО(Середній балл>59;"D";"F"))))

У попередньому прикладі друга пропозиція ЯКЩО є в той же час аргументом значення_якщо_неправда для першої пропозиції ЯКЩО. Аналогічно, третя пропозиція ЯКЩО є аргументом значення_якщо_неправда для другої пропозиції ЯКЩО. Наприклад, якщо перше лог_вираження (Середнє>89) має значення ІСТИНА, то повертається значення "A". Якщо перше лог_вираження має значення НЕПРАВДА, то обчислюється друга пропозиція ЯКЩО і так далі.

І - Повертає значення ІСТИНА, якщо всі аргументи мають значення ІСТИНА; повертає значення НЕПРАВДА, якщо хоча б один аргумент має значення НЕПРАВДА

Синтаксис

І (логічне_значення1; логічне_значення2;...)

Логічне_значення1, логічне_значення2,... - це від 1 до 30 умов, що перевіряються, що можуть мати значення або ІСТИНА, або НЕПРАВДА.

· Аргументи повинні бути логічними значеннями, масивами або посиланнями, що містять логічні значення.

· Якщо аргумент, що є посиланням або масивом, містить тексти або порожні осередки, то такі значення ігноруються.

· Якщо зазначений інтервал не містить логічних значень, то И повертає значення помилки #ЗНАЧ!.

Приклади

І(ІСТИНА; ІСТИНА) дорівнює ІСТИНА

І(ІСТИНА; НЕПРАВДА) дорівнює НЕПРАВДА

І(2+2=4; 2+3=5) дорівнює ІСТИНА

Якщо інтервал B1:B3 містить значення ІСТИНА, НЕПРАВДА, і ІСТИНА, то:

І(B1:B3) дорівнює НЕПРАВДА

Якщо осередок B4 містить число між 1 і 100, то:

І(1<B4; B4<100) дорівнює ІСТИНА

Припустимо, що потрібно вивести на екран вміст осередку B4, якщо вона містить число строго між 1 і 100 і повідомлення "Значення поза інтервалом" у противному випадку. Тоді, якщо осередок B4 містить число 104, то вираження:

ЯКЩО(І(1<B4; B4<100); B4; "Значення поза інтервалом") дорівнює "Значення поза інтервалом",

а якщо осередок B4 містить число 50, то вираження:

ЯКЩО(І(1<B4; B4<100); B4; "Значення поза інтервалом") дорівнює 50

НЕПРАВДА - Повертає логічне значення НЕПРАВДА.

Синтаксис

НЕПРАВДА()

Зауваження

Можна безпосередньо ввести слово НЕПРАВДА в робочий лист або у формулу, і Microsoft Excel буде інтерпретувати його як логічне значення НЕПРАВДА.

НІ - Змінює на протилежне логічне значення свого аргументу. Функція НЕ використовується в тих випадках, коли необхідно бути упевненим у тім, що значення не дорівнює деякій конкретній величині.

Синтаксис

НІ(логічне_значення)

Логічне_значення - це значення або вираження, що при обчисленні дає ІСТИНА або НЕПРАВДА. Якщо логічне_значення має значення НЕПРАВДА, то функція НІ повертає значення ІСТИНА; Якщо логічне_значення має значення ІСТИНА, то функція НІ повертає значення НЕПРАВДА.

Приклади

НІ(НЕПРАВДА) дорівнює ІСТИНА

НІ(1+1=2) дорівнює НЕПРАВДА

АБО -Повертає ІСТИНА, якщо хоча б один з аргументів має значення ІСТИНА; повертає НЕПРАВДА, якщо всі аргументи мають значення НЕПРАВДА.

Синтаксис

АБО(логічне_значення1;логічне_значення2;...)

Логічне_значення1, логічне_значення2,... - це від 1 до 30 умов, що перевіряються, що можуть мати значення або ІСТИНА, або НЕПРАВДА.

· Аргументи повинні бути виражені логічними значеннями, такими як ІСТИНА або НЕПРАВДА, масивами або посиланнями, що містять логічні значення.

· Якщо аргумент, що є масивом або посиланням, містить тексти, порожні значення або значення помилок, то ці значення ігноруються.

· Якщо заданий інтервал не містить логічних значень, то функція АБО повертає значення помилки #ЗНАЧ!.

· Можна використовувати функцію АБО як формулу масиву, щоб перевірити, чи маються значення в масиві. Для того, щоб увести формулу масиву, натисніть CTRL+SHIFT у Microsoft Excel 97 для Windows або +ENTER у Microsoft Excel 97 для Macintosh.

Приклади

АБО(ІСТИНА) дорівнює ІСТИНА

АБО(1+1=1;2+2=5) дорівнює НЕПРАВДА

Якщо осередку A1:A3 містять значення ІСТИНА, НЕПРАВДА й ІСТИНА, то:

АБО(A1:A3) дорівнює ІСТИНА

ІСТИНА - Повертає логічне значення ІСТИНА.

Синтаксис

ІСТИНА()

Зауваження

Можна безпосередньо ввести значення ІСТИНА в осередки і формули без використання цієї функції. Функція ІСТИНА призначена для сумісності з іншими системами електронних таблиць.

Приклад виконання задачі за допомогою логічних функцій.

Задача.

Студентам нараховується підвищена стипендія в розмірі 180 грн, у тому випадку якщо вони здали всі іспити на «5» і одержали заліки. Звичайна стипендія в розмірі 100 грн. Нараховується в тому випадку, якщо отримано не більш однієї оцінки «3» і здані заліки.(рис.1)

Рис.1. Таблиця Відомість нарахування стипендії студентам.

Для виконання завдання необхідно виконати дії, що приведені в наступній таблиці. (таб.1.)

Таблиця 1.

№	Активний осередок	Операція	Спосіб виконання
		В осередках А1:G4 створити таблицю	Мал.1 Відомість нарахування стипендії студентам
	F2	Увести формулу	З використанням майстра функцій увести формулу: = Если(И(B2 = 5;C2 = 5;D2 = 5;E2 = "залік");180;0)
	F2	Скопіювати формулу в осередки F3:F4
	G2	Увести формулу	З використанням майстра функцій увести формулу: = Если(f2>0;0;Если(Или(И (b2>=3;c2>=4;d2>=4;e2="залік"); И(b2>=4;c2>=3;d2>=4;e2="залік"); И(b2>=4;c2>=4;d2>=3;e2=''залік''));100;0))
	G2	Скопіювати формулу в осередки G3:G4

Отримані результати в таблиці 2.

Таблиця 2.

Тема: “Microsoft Excel - консолідація даних”

Робота з групою аркушів.

Досить часто в практиці необхідно узагальнювати дані однотипних документів. У цьому випадку форма документа та сама при різному змісті, наприклад, місячні звіти або бюджети відділень. Для створення таких документів Excel має засіб групового редагування. Можна згрупувати разом кілька аркушів у книзі і потім додати, відредагувати або відформовувати дані на всіх цих аркушах одночасно.

Виділення й угруповання аркушів можна виконати одним із методів.

1. Виділити ярличок першого листа в діапазоні суміжних аркушів, які потрібно згрупувати, і при натиснутій клавіші [Shift] клацнути по ярличку останнього листа в діапазоні.

2.Виділити ярличок першого листа і при натиснутій клавіші [Ctrl] послідовно клацнути по ярличках всіх аркушів, що потрібно включити в групу, незалежно від їхнього положення в книзі.

3. Команда Вибрати всі аркуші в контекстному меню ярличків аркушів.

Excel позначає режим групового редагування, додаючи [Група] у рядку заголовка книги.

Зауваження. Можна активізувати інші аркуші в групі, не виходячи з режиму групового редагування. Якщо клацнути по ярличку листа поза групою, Excel виходить з режиму групового редагування і видаляє [Група] з рядка заголовка книги. Вихід з режиму групового редагування, якщо всі аркуші книги включені в групу - клацнути по ярличку будь-якого неактивного листа.

Тепер, усе що буде вводитися на першому листі, як під копірку буде з'являтися на всіх аркушах групи. Будь-які зміни форматування, що зроблені на вкладках Число, Вирівнювання, Шрифт, Рамка, Вид і Захист у вікні діалогу Формат осередків, застосовуються одночасно до всіх членів групи. У режимі групового редагування команди Рядок, Стовпець, Автоформат і Стиль також застосуються до всіх аркушів у групі

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

Команди з меню Вставка - Осередку, Рядок, Стовпець і Функція можна використовувати для одночасної вставки відповідних елементів в усі аркуші в групі.

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

Приклад 1 Необхідно створити звіти для заповнення й обробки даних по холодильному обладнанню у розрізі матеріально-відповідальних осіб (5 чоловік) за тиждень наступної форми:

Виконання:

1. Додати нові аркуші в книгу – клацнути правою кнопкою миші по ярличку останнього листа ® к. Додати ® вкладка Загальні ®виділити значок Лист ® ОК (з'явиться новий лист). Повторювати потрібну кількість разів.

2. Упорядкувати аркуші: перетягнути мишкою ярличок листа, що стоїть не один по одному, на місце.

3. Клацнути по ярличку Лист1, натиснути клавішу [Shift] і одночасно клацнути по ярличку Лист5.

4. На першому виділеному листі створити зазначену форму c формулами.

5. Зняти виділення з аркушів, клацнувши по ярличку Лист6.

6. У кожнім листі заповнити прізвище матеріально-відповідальної особи.

Додати в сформовану книгу (даного приклада) звітні форми по двох новим реалізаторам.

Послідовність дій:

1. Виділити групу аркушів з 5 по 7.

2. На листі 5 виділити усю форму звіту, м. Виправлення ® к. Заповнити ® п. По аркушах ® покажчик Усі ® ОК.

3. Зняти виділення з групи аркушів і відкоригувати прізвища на аркушах 6 і 7.

Примітка: Якщо копійований звіт був заповнений, виділити групу нових аркушів (6 і 7), потім виділити предметну частину листа 6 і натиснути клавішу [Delete].

При формуванні підсумкової таблиці по створених звітних формах можна використовувати об'ємні формули, що можуть підсумовувати дані відразу по декількох однойменних осередках різних аркушів.

Консолідація аркушів

При виконанні робіт зі створення зведених звітів по однотипних детальних звітах, крім об'ємних формул, Excel дозволяє використовувати ще один досить простий і зручний прийом – консолідацію даних.
Команда Консолідація з меню Дані може об'єднати інформацію з вихідних аркушів (до 255 аркушів) в одному підсумковому листі. Вихідні аркуші можуть розташовуватися в тій же самій книзі, у якій знаходиться підсумковий лист, або в інших книгах.

Команду Консолідація можна використовувати декількома способами. Можна зв'язати консолідовані дані з вихідними даними, щоб наступні зміни у вихідних аркушах відображалися в підсумковому листі. Або можна просто консолідувати вихідні дані без створення зв'язків.

Консолідувати дані можна по розташуванню або по категорії. Якщо консолідують дані по розташуванню, Microsoft Excel збирає інформацію з однаково розташованих осередків кожного вихідного листа. При консолідації даних по категорії Excel використовує як основу для об'єднання аркушів заголовки стовпців або рядків. Можна консолідувати аркуші, використовуючи будь-яку функцію, приведену в поле зі списком, що розкривається, Функція у вікні діалогу Консолідація. За замовчуванням використовується функція Сума, що підсумовує дані з кожного вихідного листа і поміщає результат у підсумковий лист. Також можна використовувати кожну з наступних функцій: Кількість значень, Середнє, Максимум, Мінімум, Добуток, Кількість чисел, Незміщене відхилення, Зміщене відхилення, Незміщена дисперсія і Зміщена дисперсія.

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

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

Консолідація по розташуванню

При консолідації по розташуванню Excel застосовує підсумкову функцію (Сума, Середнє або будь-яку іншу з перерахованих раніше) до осередків з однаковими адресами в кожнім вихідному листі. Це найпростіший спосіб консолідації, при якому консолідовані дані у всіх вихідних аркушах повинні мати зовсім однакове розташування.

Приклад 1.1. Виконати консолідацію звітів про реалізації безалкогольних напоїв за тиждень. Розташувати консолідовані звіти по розташуванню і категоріям на окремих аркушах.

Виконання:

1. Додати в книгу зі звітами 2 нових листи і привласнити їх ярличкам імена Консолід_по_розташ. і Консолід_по категор.

2. Скопіювати в лист Консолід_по_розташ форму звітупо реалізації товару, очистити кінцеву область (область призначення), тобто блок осередків, у котрий будуть поміщені консолідовані дані – В7:К15. Виділення не знімати!

3. Виберіть команду Консолідація в меню Дані (рис.1).

Рисунок 1 - Діалогове вікно консолідації.

4. Для підрахунку значень з кожного вихідного листа випливає в поле зі списком Функція, у вікні діалогу Консолідація вибрати функцію Сума. Залишіть прапорці в секції Використовувати мітки невстановленими. Оскільки ми не збираємося створювати зв'язку з вихідними аркушами, прапорець Створювати зв'язку з вихідними даними також залишіть невстановленим.

5. Уведіть посилання для кожного вихідного діапазону в поле Посилання або виділите ці діапазони за допомогою миші: клацнути по полю Посилання ® клацнути по ярличку Лист1 ® виділити осередку В7:К15 (зменшити діалогове вікно можна клацнути по кнопці наприкінці поля Посилання) ® клацнути по кнопці Додати (розгорнути діалогове вікно можна щигликом по тій же кнопці наприкінці поля Посилання) – зазначений діапазон осередків повинний з'явитися в поле Список діапазонів ® клацнути по ярличку Лист2 ® виділити осередку В7:К15 (при створенні форм документів у групі діапазон осередків буде встановлений автоматично) ® клацнути по кнопці Додати ® повторити зазначені дії для всіх підсумкових звітів ® ОК.

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

{Ім'я файлу}Ім'я листа! Посилання

Якщо вихідному діапазонові було призначене ім'я, можна використовувати це ім'я замість посилання.

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

Примітка. Консолідація виконується по блоці суміжних осередків, тому графа «Ціна», як довідковий матеріал, винесена в кінець звіту, щоб не розривати консолідований діапазон.

Консолідація по категорії

Розглянутий приклад 1.1. припускав повну ідентичність звітів у частині розглянутих категорій, у даному випадку, реалізованих товарів, що в практичній діяльності зовсім не обов'язково. Список реалізованих товарів у кожної підзвітної особи може змінюватися. У цьому випадку необхідно використовувати консолідацію по категоріях.

Приклад 1.2. Внести в звіт про реалізації на Лист1 дані по обладнанню «Холодильник Норд», на Лист3 – дані по обладнанню «Морозильна камера Норд» і «Група Норд», на Лист5 – по обладнанню «Трикамерний холодильник Норд». На листі Консол_по_категор створити консолідований звіт по всім реалізаторам за тиждень.

Виконання.

1. На лист Консол_по_категор скопіювати заголовну частину таблиці звіту (А1:L6) з будь-якого листа.

2. Виділити осередок А7 як кінцеву область. При завданні одного осередку як кінцеву область команда Консолідація заповнює необхідну область нижче і праворуч від цього осередку.

3. М. Дані ® к. Консолідація ® заповнити вікно діалогу Консолідація ® вибрати Сума в поле зі списком, Функція ® установити прапорець У лівому стовпці в секції Використовувати мітки.

4. Увести посилання для кожного вихідного діапазону в поле Посилання або виділити ці діапазони за допомогою миші: клацнути по полю Посилання ® клацнути по ярличку Лист1 ® виділити осередку А7:К15 (у діапазон повинні бути включені осередки з категоріями) ® клацнути по кнопці Додати ® клацнути по ярличку Лист2 ® виділити осередку А7:К15 ® клацнути по кнопці Додати ® повторити зазначені дії для всіх підсумкових звітів, виділяючи дані про реалізовані товари ® ОК.

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

6. Консолідований звіт можна відформовувати, додати підсумкові рядки з формулами, довідкову інформацію (наприклад, ціну).

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

Змістовий модуль. Основи алгоритмізації і програмування

Тема. Загальні етапи підготовки і реалізації програм на ПК.

Дата добавления: 2015-01-03