Гіпермаркет Знань>>Інформатика>>Інформатика 11 клас>> Інформатика: Практична робота №3. Використання формул в електронних таблицях. Статті до предмету Інформатика 11 клас. Тема «Внесення формул і функцій у комірки. Абсолютна та відносна адресація».
Тема 3. Внесення формул і функцій у комірки. Абсолютна та відносна адресація. Формули і функції – основні інструменти для проведення розрахунків робочого листа. Вони містять тільки адреси комірок, абсолютні величини, константи або вбудовані функції. Створення формул завжди починається зі знака „=”, таким чином цей знак є зарезервованим і не може використовуватися як перший символ при введенні будь-якої константи. При введенні формули можна використовувати наступні математичні дії (вони наведені у відповідності до пріоритетів): (^) – піднесення до степеня числа, наприклад: 252 запишеться як 25^2; (*) – множення, (/) – ділення; (+) – додавання, (-) – віднімання або від’ємні константи. Також у формулах часто виникає необхідність використання дужок (). Використання дужок, як і в математиці призвані змінити порядок пріоритетів, наприклад наступну формулу треба буде записати так:
Комірки А1, А2, А3 та С2 заповнити відповідно „а=”, „b=”, „с=” та „у=” Проводячи будь-які розрахунки в Excel необхідно завжди пам’ятати, що табличний процесор не може розв’язувати задачі в загальному вигляді, йому завжди потрібно давати тільки числові значення будь-яких параметрів. Тому, враховуючи, що в нас є задача підрахунку у загальному вигляді наша формула запишеться так:
Приведемо приклад розрахунку наступної задачі. Побудувати зарплатну відомість для розрахунку заробітної платні для п’ятьох працівників фірми, якщо доход кожного працівника складається з окладу та премії, а податки з ПДВ та відрахунків у пенсійний фонд. Розміри окладу та премії задати самостійно. Податки беруться від загальної суми, що нарахована. ПДВ дорівнює 13%, а перерахунок у пенсійний фонд – 0,5%. Розрахувати скільки кожний працівник фірми отримає на руки. Спочатку зробимо шапку нашої таблиці. Вона матиме вигляд як показано на малюнку. Потім наберемо прізвища п’ятьох працівників і задамо кожному х них оклад і премію. Тепер починаємо формувати формули для підрахунку ПДВ. Сформуємо формулу для комірки D2: =(B2+C2)*0,13 або =(B2+C2)*13% Ці два записи тотожні, бо 13%=0,13. Введемо отримане значення у комірку D2.
При цьому способі заповнення комірок формулами всі адреси, на які посилається формула, відповідно зміняться у напрямку розтягування формули, так у комірці D3 буде формула =(B3+C3)*0,13 або =(B3+C3)*13% відповідно до тої, яка була введена у комірку D2. У комірці D4 буде формула =(B4+C4)*0,13 або =(B4+C4)*13% і так далі. Формула для введення колонки пенсійного фонду (комірка Е2) буде мати вигляд: =(B2+C2)*0,005 або =(B2+C2)*0,5% Формула, що повинна бути записаною до колонки До видачі (комірка F2) матиме вигляд: =B2+C2-D2-E2 В решті решт отримали заповнену таблицю хоча з клавіатури вводили лише три формули. У комірці F7 тепер можемо розрахувати скільки грошей повинен взяти з собою касир для видачі заробітної плати своїм працівникам. Зробимо це за допомогою простішої функції Автосумма. За допомогою функції Автосумма можна розрахувати суму значень у стовпці (або в рядку). Для цього активізують вільну комірку під стовпцем (чи праворуч від рядка) та натискають кнопку Автосумма (), яка знаходиться на панелі інструментів Стандартная. В комірці автоматично створюється функція СУММ із зазначеним аргументом – відповідним діапазоном стовпця (або рядка). Натискують клавішу Enter. Функція розрахунку суми у стовпці F матиме такий вигляд: =СУММ(F2:F6) і набуде значення 2607,11.
У Excel є можливість автоматичного створення прогресій, тобто послідовності чисел за заданою схемою. Наприклад при створенні списків учнів необхідно ввести їх порядковий номер. Для автоматизації створення порядкового номера необхідно у першу комірку ввести значення 1, у комірку нижче – 2. потім виділити обидва введені значення і ставши мишею на нижній правий кут виділеного діапазону зажати ліву клавішу миші і розтягнути діапазон на сумісні комірки. При цьому у наступній комірці з’явиться номер 3, далі – 4, і т.д. Таким чином ми отримаємо прогресію від 1 до N з кроком 1. Якщо треба задати інший крок прогресії вводимо перше значення прогресії у першу колонку, потім у другу колонку вводимо значення на крок більше за попереднє і проводимо інші дії з створення прогресії.
Завдання 1. 1. Зробити назву таблиці за допомогою об’єднання комірок А1-F1.
До цього ми розмножували формули таким чином, щоб зі зміною положення формули автоматично змінювались і комірки значень параметрів формули. Але існують такі випадки, коли адреса комірок не повинна змінюватись при зміні положення формули. Наприклад нам потрібно перерахувати ціну деяких продуктів харчування з гривень до доларів за заданим курсом. Нехай ми розташували курс гривни до долара у комірці В2, а ціна у гривнах на задані продукти знаходиться у комірках діапазону В4:В12. Необхідно до комірок С4:С12 записати значення ціни у доларах. Для того, щоб це виконати необхідно у комірку С4 ввести наступну формулу: =В2*В4 Далі для того щоб порахувати значення у комірках С5, С6 і так далі необхідно скопіювати введену формулу у комірку С4 на комірки С5, С6 і так далі. Скопіюємо це значення. Отримаємо у комірці С5 формулу: =В3*В5, але вона не є правильною, бо значення курсу знаходиться у комірці В2 а не у В3, тобто ми отримали не вірний результат. Спосіб копіювання виявився не придатним бо ми використовували метод відносної адресації, тоб-то адресація, яка при копіюванні змінюється відповідно до напряму копіювання. Застосуємо метод абсолютної адресації. Адреса комірки з абсолютною адресою має вигляд: $В$2. Знак $ означає що номер рядка або стовпчика, який іде після нього не повинен змінюватись. Таким чином у нас не повинен змінюватися ані стовпчик, ані рядок. Використовуючи запис В$2 ми закріплюємо від зміни тільки номер рядка, а стовпчик може змінюватись, в запису $В2 закріпленим є тільки стовпчик, рядок може змінюватися. Таким чином наша формула набуде вигляду: =В$2*В4, або =$В$2*В4. у даному разі ці записи є тотожними так як ми не розмножуємо комірки по горизонталі, що б привело до зміни номера стовпця. Поставити знак $ можна двома способами: з клавіатури, або за допомогою кнопки F4, яка змінює вигляд комірки по циклу: В2>$B$2>B$2>$B2>B2.
Завдання 1. Побудувати наступну таблицю і розрахувати ціну у заданих грошових одиницях використовуючи принцип абсолютної адресації.
2) Збитки компанії Карло і Буратіно у доларах, італійських лірах, фунтах за кожний дань і за тиждень при середній ціні яблука 20 центів.
Аргументи функції задають значення або адреси комірок, які використовуються при обчисленні значення функції. У якості аргументів можна розглядати числові, текстові і логічні значення, імена діапазонів, комірок, адреси комірок, масиви даних. Розглянемо ці типи аргументів. Числові значення. Аргументом функції може бути будь-яке число, наприклад: 256 25,6 0,14. Текстові значення. Будь-яка текстова константа, рядок символів у лапках або посилання на комірку, яка має текст. Логічні вирази. Аргументи деяких функцій можуть приймати тільки логічні значення ІСТИНА або ХИБНІСТЬ. Логічний вираз вертає на робочий лист або в комірку, в якій ця формула знаходиться, одне з логічних значень. Посилання на комірки. В якості аргументу може виступати посилання на комірку в яке було введене якесь значення або формула. Іменовані посилання. Аргументом функції може бути ім’я діапазону, присвоєне раніше. Масиви. Можуть бути аргументами функцій, які працюють з масивами даних. Аргументи змішаних типів. В одній функції можна примінити аргументи різних типів, а також інші вбудовані функції.
Для роботи з вбудованими функціями використовують кнопку (Вставка функции). При цьому активізується Майстер функцій, за допомогою якого зручно створювати функції. Майстер функцій має два шаги. На першому кроці виконується пошук функції за описанням її дії, обирається категорія функцій, обирається сама функція. На другому кроці у віконця функцій вносяться данні і аргументи функції.
На першому кроці можна також отримати коротку інформацію стосовно обраної функції, тобто що ця чи інша функція робить. На другому кроці при введенні аргументів функції можна отримати інформацію стосовно аргументів обраної функції, результат обчислень функції при заданому аргументі. Інше розподілення функцій – розподілення за кількістю аргументів. Всі функції можна поділити на п’ять груп: • Безаргументні – функції які не потребують введення аргументу.
ПИ() – вертає значення ?, тобто 3,1415. Зверніть увагу, при відсутності аргументу ці функції мають дужки (). У назвах функцій не допускається введення пустих символів, всі літери та аргументи повинні йти один за одним, або за допомогою стандартних розподільників. При введенні в комірку =ПИ() та натиснувши кнопку Ввод отримаємо у комірці значення функції – 3,1415. До одно аргументних функцій належать: SIN(...), COS(…), TAN(…), ASIN(…), ACOS(…), ATAN(…), EXP(…), LN(…), LOG10(…), КОРЕНЬ(...), ABS(…), ЦЕЛОЕ(...) – математичні функції. Аргументом цих функцій може бути число, адреса комірки, математичний вираз чи інші вбудовані функції. Вікно одно аргументної функцій має вигляд: Приклад: за умови, що значення параметру n знаходиться у комірці А1 набуде вигляд: =COS(ПИ()/4+2*ПИ()*А1). LOG(число;основание) – вертає логарифм за основою; ОКРУГЛ(число;число_розрядов) – автоматично округлює число; ОКРВВЕРХ(число;точность), ОКРВНИЗ(число;точность) – робить округлення чисел вгору та вниз; СТЕПЕНЬ(число;степень) – підводить задане число у задану степінь; СЧЁТЕСЛИ(діапазон; критерий) – рахує кількість значень діапазону, які задовольняють заданому критерію. Аргументи один від одного відокремлюються „ ; ” і не можуть містити порожніх символів або аргументів. Приклад вікна для двоаргументної функції: Приклад: необхідно розв’язати наступний вираз: . Значення n знаходиться у комірці А1. Вираз запишеться у вигляді: =LOG(12+А1;5). СУММЕСЛИ(диапазон;критерий;диапазон_суммирования) – додає значення коміркок, які задовольняють умові. Приклад. Є таблиця з даними по розташуванню грошей у заданих банках. Користувачу знадобилося підрахувати скільки грошей він має у банку Аваль.
ЕСЛИ(лог_выражение;значение_если_истина;значение_если_ложь) – перевіряє чи виконується умова і вертає одне значення, якщо виконується або інше, якщо не виконується. При написанні логічного виразу використовуються наступні оператори:
Значение_если_ислина – значення, що набуває функція при виконанні логічного виразу. Значение_если_ложь – значення, що набуває функція при не виконанні логічного виразу. Приклад. При підрахунку заробітної платні треба сплатити податок за схемою: якщо заробітна платня менше 500грн, податок складає 13%, якщо більше – 15%. Побудуємо таблицю розрахунку заробітної платні:
Логічним виразом буде порівняння значення у комірці окладу з 500грн, які є граничними для розрахунків податку. Для комірки С2 логічний вираз буде: В2<500. зверніть увагу та не, що ми при написанні формули не дивимось на безпосереднє значення комірки, так як вважаємо, що це значення може змінюватись у процесі роботи працівника, тобто можна підвищити йому оклад чи зменшити, зміст формули не повинен при цьому змінюватись. Значение_если_истина – буде формула для підрахунку податку у розмірі 13%, тобто: В2*13%. Значение_если_ложь – буде формула для підрахунку податку у розмірі 15%, тобто: В2*15%. Таким чином ми отримали узагальнену (без прив’язки до конкретного значення окладу) формулу для підрахунку податку. Після її отримання можна розмножити за допомогою відносної адресації цю формулу на комірки С3 – С7 і отримати розрахунок податку для інших працівників без зміни формули для розрахунку податку. Колонку До видачі отримуємо як різницю між окладом та податком. Такий спосіб запису стане в пригоді при внесенні змін у колонку Оклад, тобто переробляти формулу для підрахунку податку та колонку До видачі не знадобиться. До функцій з кількістю аргументів від 1 до 30 відносять наступні функції: Статистичні функції. СУММ(число1;число2;число3;...) – підраховує суму аргументів; СЧЁТ(аргумент1;аргумен2;...) – підраховує кількість чисел у списку аргументів; МАКС(число1;число2;число3;...) – вертає максимальне значення з списку; МИН(число1;число2;число3;...) – вертає мінімальне значення з списку; ПРОИЗВЕД(число1;число2;число3;...) – вертає додаток чисел з списку; СРЗНАЧ(число1;число2;число3;...) – вертає середнє значення чисел із списку; СУММКВ(число1;число2;число3;...) – вертає суму квадратів чисел з списку. Приклад. Знайти мінімальне число по стовпчиках А та С та комірках В3 та В5 у заданій таблиці. Діапазони сумісних комірок можуть бути записані до одного аргументу а діапазони окремих комірок або окремі діапазони повинні записувати у різні аргументи. Логічні функції. И(аргумент1;аргумент2;...) – перевіряє чи всі аргументи є ІСТИНА. Вертає значення ІСТИНА коли всі аргументи є ІСТИНА, і значення ХИБНІСТЬ, якщо хоча б один з аргументів є ХИБНІСТЬ. ИЛИ(аргумент1;аргумент2;...) – перевіряє чи є серед аргументів хоча б один аргумент ІСТИНА. Вертає значення ІСТИНА, якщо хоча б один з аргументів є ІСТИНА і ХИБНІСТЬ, якщо всі аргументи ХИБНІСТЬ. Ці функції доцільно використовувати, коли треба перевірити значення двох або більше аргументів.
Завдання 1. Вважаючи що значення параметра х знаходиться у комірці А1, у – у комірці А2, а z – у комірці А3 записати формули для розрахунків наступних функцій:
Спочатку обчислити формулу, яка не містить параметрів, а потім у кожній наступній формулі використовуйте значення, отримане на попередньому кроці.
Діапазон х є [— 2; 2] (крок 0,2) Діапазон х є [–2; 2] (крок 0,2) Діапазон х є [–5; 1] (крок 0,2) Діапазон х є [–3; 1] (крок 0,2) Діапазон х є [–1,8; 1,8] (крок 0,2) Діапазон х є [–2; 1,8] (крок 0,2) Діапазон х є [–1,7; 1,6] (крок 0,3) Діапазон х є [–1,5; 1,8] (крок 0,3)
Визначте суму "до виплати" для працівників підрозділу (7 чоловік) при розрахунку заробітної платні, якщо нарахування — це посадовий оклад, премія, доплата на дітей. Утримання включають податок та перерахунок в ощадний банк. Премія становить 10% від посадового окладу , якщо стаж роботи менший 5 років, і 20%, якщо стаж роботи більший 5 років; доплата на дітей становить 30% при наявності 2 і більше дітей; перерахунок в ощадний банк становить 5% від всіх нарахувань. Податок дорівнює: • 0, якщо сума менша ніж 400 грн; • 15% від нарахувань, якщо сума становить 400 або більше, але менше ніж 800 гри; • 20% від нарахувань, якщо нараховано 800 або більше гривень. Визначте суму "до виплати" для 5 чоловік, при розрахунку заробітної платні, якщо нарахування — це оплата за погодинну роботу; премія; доплата на дітей; доплата на харчування. Утримання включають податок. Тривалість денної зміни 8 год, а тривалість нічної зміни 7 год; оплата робіт за 1 годину денної зміни становить 5,50 гри, нічної 2 гри; премія становить 8% від погодинної оплати за нічні зміни, доплата на дітей становить 30% від нарахувань за погодинну роботу, якщо нараховано не більше 100 грн; доплата на харчування 10% від нарахувань за погодинну роботу. Податок дорівнює: • 10% від нарахувань, якщо сума менша ніж 600 грн; • 14% від нарахувань, якщо сума становить 600 або більше, але менше ніж 1000 грн; • 15% від нарахувань, якщо нараховано 1000 або більше гривень. При розрахунку оплати за погодинну працю потрібно вкажіть для кожного працівника кількість відпрацьованих змін (нічних та денних). Визначте суму "до виплати" при нарахуванні заробітної платні 6 працівникам (з них 2 - інженерне технічні) фірми, якщо нарахування - це посадовий оклад, премія та доплата на дітей. Утримання включають податок та перерахунок в ощадний банк. Премія становить 25% від посадового окладу для інженерно-технічного персоналу і 15% для інших співробітників; доплата на дітей становить 30% при наявності 2 і більше дітей; перерахунок в ощадний банк - 15% від нарахувань. Податок дорівнює: • 0, якщо нараховано менше ніж 250 грн; • 13% від нарахувань, якщо сума становить 250 або більше, але менше ніж 500 грн; • 16% від нарахувань, якщо нараховано 500 або більше гривень. Визначте суму "до виплати" при нарахуванні заробітної платні для 5 робітників цеху, якщо нарахування - це оплата за погодинну роботу, премія, доплата на дітей; доплата на харчування. Утримання включають податок. Тривалість денної зміни - 7 годин, а тривалість нічної зміни - 5 годин, оплата робіт за 1 годину денної зміни становить 1 грн, нічної - 2,1 грн; доплата на дітей становить 25% від нарахувань за погодинну роботу, якщо нараховано не більше 200 грн; доплата на харчування - 15% від нарахувань за погодинну роботу. Податок дорівнює: • 11% від нарахувань, якщо нараховано менше ніж 200 грн; • 12% від нарахувань, якщо сума становить 200 або більше, але менше ніж 500 грн; • 17% від нарахувань, якщо нараховано 500 або більше гривень. Визначте суму "до виплати" при розрахунку заробітної платні для 5 працівників установи, якщо нарахування - це посадовий оклад та премія. Утримання включають податок та аліменти на дітей. Премія становить 15% від посадового окладу, якщо стаж роботи більше 10 років; аліменти на дітей - 25% від нарахувань. Податок дорівнює: • 0, якщо нараховано менше ніж 400 грн; • 10% від нарахувань, якщо сума становить 400 або більше, але менше ніж 800 грн; • 15% від нарахувань, якщо нараховано 800 або більше гривень. Визначте суму "до виплати", яка утвориться при розрахунку заробітної платні для 7 працівників підрозділу, якщо нарахування - це посадовий оклад та премія, а утримання включають податок та профспілкові внески. Премія становить 15% від посадового окладу, якщо стаж роботи не більше 5 років, і 25%, якщо стаж більше 5 років. Профспілкові внески становлять 1% від нарахувань. Податок дорівнює: • 0, якщо нараховано менше ніж 500 грн; • 11% від нарахувань, якщо сума становить 500 або більше, але менше ніж 1000 грн; • 14% від нарахувань, якщо нараховано 1000 або більше гривень. Визначте суму "до виплати" при розрахунку заробітну платні для працівників відділу (7 чоловік), якщо нарахування - це посадовий оклад, премія, доплата на дітей. Утримання включають податок та сплату за кредит взятий у банку. Премія становить 10% від посадового окладу. Доплата на дітей становить 35% від посадового окладу при наявності двох і більше дітей. Сплата за кредит взятий в банку становить 15% від начислень. Податок дорівнює: • 0, якщо нараховано менше ніж 400 грн; • 12% від нарахувань, якщо сума становить 400 або більше але менше ніж 900 грн; • 15% від нарахувань, якщо нараховано 900 або більше гривень. Визначте суму "до виплати" при розрахунку заробітної платні 8 службовцям підприємства, якщо нарахування - це посадовий оклад; премія; доплата на дітей. Утримання складаються з податку та профспілкових внесків. При цьому: премія становить 8%, якщо стаж роботи не більше 3 років, і 15% якщо стаж більше 3 років; доплата на дітей становить 15% від посадового окладу для працівників, у кого стаж більший 5 років. Профспілкові внести для членів профспілки становить 1% від нарахувань. Податок дорівнює: • 0, якщо нараховано менше ніж 200 грн; • 10% від нарахувань, якщо сума становить 200 або більше, але менше ніж 500 грн; • 15% від нарахувань, якщо нараховано 500 або більше гривень.
Надіслала вчитель інформатики СЗШ № 54 Солом*янського району м. Києва Тальнова Олена Володимирівна. Предмети > Інформатика > Інформатика 11 клас > Практична робота №3. Використання формул в електронних таблицях > Практична робота №3. Використання формул в електронних таблицях. Статті |
Авторські права | Privacy Policy |FAQ | Партнери | Контакти | Кейс-уроки
© Автор системы образования 7W и Гипермаркета Знаний - Владимир Спиваковский
При использовании материалов ресурса
ссылка на edufuture.biz обязательна (для интернет ресурсов -
гиперссылка).
edufuture.biz 2008-© Все права защищены.
Сайт edufuture.biz является порталом, в котором не предусмотрены темы политики, наркомании, алкоголизма, курения и других "взрослых" тем.
Ждем Ваши замечания и предложения на email:
По вопросам рекламы и спонсорства пишите на email: