HYPERLINK “http://www.ukrreferat.com/” www.ukrreferat.com – лідер
серед рефератних сайтів України!
Тема. Створення та редагування таблиць.
Проведення розрахунків в таблицях.
Форматування елементів та частин таблиць і таблиць у цілому.
Побудова таблиці складається з чотирьох етапів:
на першому етапі у комірки таблиці заноситься заголовок та «шапка»
майбутньої таблиці;
на другому етапі комірки таблиці заповнюються вхідними значеннями;
на третьому етапі розраховуються вихідні дані — у відповідні комірки
таблиці заносяться формули або функції для обчислень;
на четвертому етапі таблиці та її елементам надається потрібний вигляд —
формат.
Розглянемо декілька прикладів для побудови таблиць.
Приклад 1. Побудова таблиці з використанням
стандартних функцій
Побудувати показану нижче таблицю та вирахувати потрібні дані.
ОБЛІК ВИТРАТ НА ТЕХНІЧНЕ ОБСЛУГОВУВАННЯ
Місяць Марка Загальний
пробіг, тис. км Норма витрат на
1 тис. км, грн Усього
витрат, грн
Січень ВАЗ-2109 65 150,50 грн ?
Лютий Опель 67 130,00 грн ?
Березень Ауді 100 110,00 грн ?
Квітень Таврія 45 99,00 грн ?
Лютий Ауді 90 110,00 грн ?
Березень ВАЗ-2109 46 150,50 грн ?
Лютий Таврія 78 99,00 грн ?
Січень Опель 23 130,00 грн ?
Квітень ВАЗ-2109 40 150,50 грн ?
Січень Ауді 56 110,00 грн ?
Лютий ВАЗ-2109 89 150,50 грн ?
Квітень Таврія 32 99,00 грн ?
Разом:
Середнє ?
Сума ?
1 етап.
Заголовок таблиці: Облік витрат на технічне обслуговування заноситься у
комірку А1.
Шапка таблиці:
Місяць — заноситься у комірку А3
Марка — заноситься у комірку В3
Загальний пробіг, тис. км — заноситься у комірку С3
Норма витрат на 1 тис. км, грн — заноситься у комірку D3
Усього витрат, грн — заноситься у комірку E3
2 етап.
Вхідними значеннями є:
назви місяців — заносяться у комірки А4:А15
назви марок автомобілів — заносяться у комірки В4:В15
цифри загального пробігу — заносяться у комірки С4:С15
цифри норм витрат — заносяться у комірки D4:D15
назва Разом для підсумкового рядка
Після завершення другого етапу таблиця набуде вигляду:
3 етап
Вихідними значеннями є:
загальні витрати по кожному рядку у конкретному місяці для конкретної
марки автомобіля;
середнє значення для загального пробігу по всіх місяцях і усіх марках
автомобілів;
сума для загальних витрат по усіх місяцях і марках автомобілів.
Загальні витрати вираховуються як формула добутку значень Норми витрат і
Загального пробігу. Активізується комірка Е4 і записується формула:
=С4*D4, натискається на Enter, і у комірці Е4 з’явиться цифра результату
9782,5, а у рядку формул — текст уведеної формули.
Формула справедлива і для комірок Е5:Е15, тому її можна скопіювати вниз
по стовпчику Е, тобто організувати цикл для визначення кожного із
вихідних даних. Для цього активізується комірка Е4, мишка встановлюється
на крапку автозаповнення і «протягується» вниз по комірках Е5:Е15. Усі
комірки заповняться визначеними цифрами. Якщо активізувати комірку Е5,
то у рядку формул з’явиться формула = С5*D5, тобто при зміні положення
формули змінюються посилання на адреси у ній (адреси є відносними) і так
далі у заповнених комірках стовпчика Е.
Для визначення середнього значення загального пробігу активізується
комірка С17, і записується функція для обчислення середнього значення у
визначеному діапазоні або викликається Майстер функцій, категорія
Статистические, функція СРЗНАЧ і натискається Ok.
З’явиться вікно другого кроку Майстра функцій, у якому треба визначити
діапазон для розрахунку (комірки С4:С15) і натиснути на Ok.
на панелі інструментів.
Загальний вигляд таблиці після третього етапу:
4 етап.
Заголовок таблиці треба записати напівжирним підкресленим шрифтом 14
розміру і відцентрувати по комірках А1:Е1. Для цього віділяється
діапазон А1:G1, пункт меню Формат/Ячейки…, вкладинка Выравнивание і з
списку Выравнивание по горизонтали вибрати По центру выделения і
натиснути на Ok, а потім встановити потрібний стиль і розмір шрифта.
У шапці таблиці текстові дані треба виділити напівжирним шрифтом з
розташуванням тексту у декілька рядків. Для цього виділяється діапазон
А3:Е3, пункт меню Формат/Ячейки…, вкладинка Выравнивание і позначити
покажчик Переносить по словам, а потім натиснути на Ok, а потім
встановити потрібний стиль і розмір шрифта.
, або вибирається пункт меню Формат/Ячейки…, вкладинка Число, числовий
формат Денежный, встановлюється кількість десяткових знаків — 2, із
списку Обозначение вибирається грн, і натискається Ok.
За бажанням можна змінити ширину стовпчиків таблиці, вибрати шрифт
напівжирний курсив для підсумкового рядка, поставити подвійні рамки для
таблиці тощо.
Після завершення таблиця набере такого вигляду:
ОБЛІК ВИТРАТ НА ТЕХНІЧНЕ ОБСЛУГОВУВАННЯ
Місяць Марка Загальний
пробіг, 1000 км Норма витрат на 1000 км, грн Усього витрат, тис. грн
Січень ВАЗ-2109 65 150,50 грн 9 782,50 грн
Лютий Опель 67 130,00 грн 8 710,00 грн
Березень Ауді 100 110,00 грн 11 000,00 грн
Квітень Таврія 45 99,00 грн 4 455,00 грн
Лютий Ауді 90 110,00 грн 9 900,00 грн
Березень ВАЗ-2109 46 150,50 грн 6 923,00 грн
Лютий Таврія 78 99,00 грн 7 722,00 грн
Січень Опель 23 130,00 грн 2 990,00 грн
Квітень ВАЗ-2109 40 150,50 грн 6 020,00 грн
Січень Ауді 56 110,00 грн 6 160,00 грн
Лютий ВАЗ-2109 89 150,50 грн 13 394,50 грн
Квітень Таврія 32 99,00 грн 3 168,00 грн
Разом:
60,92
90 225,00 грн
Приклад 2. Обчислення математичних
виразів у таблиці
Використовуючи подані нижче математичні вирази, вирахувати значення
виразів Y1; Y2 та Y3 і результати представити у вигляді таблиці. За
вхідними та вихідними даними обчислити максимальне, мінімальне та
середнє значення.
Значення a = 3,1; b = 5,9; x = – 1;13;0;6;12;3;27. При x > 20 та x = 6
функція не визначена.
Значення Y4 вираховується так само, як і Y3, проте використовуються інші
способи перевірки умови.
1 етап.
Створюється заголовок таблиці — комірка А17 та рядок 19.
2 етап.
У відповідні комірки робочого аркуша заносяться вхідні значення
коефіцієнтів А — комірка А20; В — комірка В20 та задані значення Х —
комірки С20:С26. У комірках В27:В29 розміщуються заголовки для
визначення мінімального, максимального та середнього значень. Зовнішній
вигляд таблиці показаний нижче.
3 етап.
Для визначення виразів Y1, Y2, Y3 та Y4 використовуються формули, які
містять посилання на адреси зі вхідними значеннями — коефіцієнти А, В,
значення Х та вказані функції. Елементи формули поєднуються потрібними
знаками (математичними та пунктуаційними) і дужками для зміни пріоритету
виконання обчислень. Найвищий пріоритет мають функції, потім операції
зведення у ступінь, потім ділення та множення і нарешті — операції
додавання та віднімання. Формула для визначення Y1 заноситься у комірку
D20, а потім копіюється у комірки D21:D26, у зв’язку з цим адреси
коефіцієнтів А та В мають бути абсолютними, тобто не змінюватимуться під
час копіювання, а адреса значення Х має бути відносною, тобто
змінюватиметься під час копіювання. Аналогічно формули для визначення
Y2, Y3 та Y4 заносяться у комірки Е20, F20 та G20 і копіюються у комірки
Е21:Е26, F21:F26 i G21:G26.
Формула для обчислення виразу Y1 має такий вигляд:
=$A$20*(LN($B$20+ABS(C20^3))/$B$20)–$B$20*
СТЕПЕНЬ($A$20+$B$20*C20;1/3)+C20+SIN($A$20*$B$20*C20)
Де $A$20 — абсолютна адреса із значенням коефіцієнта А,
$B$20 — абсолютна адреса із значенням коефіцієнта В,
C20 — відносна адреса поточного значення Х,
LN(…) — функція для визначення натурального логарифму,
ABS(…) — функція для визначення модуля числа,
СТЕПЕНЬ(….; значення ступеня) — функція для визначення ступеня числа або
виразу. У даному випадку значення кореня третього ступеня можна замінити
значенням виразу у ступені 1/3.
SIN(…) — функція для визначення синуса числа.
Текст формули у комірку D20 можна вводити з клавіатури, а можна
використати Майстра функцій:
Активізувати комірку D20, натиснути на =, вибрати мишкою адресу
коефіцієнта А і зафіксувати її клавішею F4, натиснути знак * і знак (і в
рядку формул буде показаний текст:
= $A$20*).
Потім викликається Майстер функцій, вибирається категорія математична,
функція LN і Ok, мишкою вказується адреса коефіцієнта В і фіксується
(клавіша F4), знак додавання і заноситься вкладена функція ABS. Її ім’я
вибирається по стрілці із зони, що розташована ліворуч від рядка формул,
як показано на рисунку.
Відкриється Майстер для функції ABS, де треба занеcти аргументи для ABS
— вибрати мишкою адресу значення Х, знак зведення у ступінь ^ і 3.
Для повернення із вкладеної функції у викликаючу треба у рядку формул
мишкою вибрати ім’я викликаючої функції (у даному випадку LN), перейти у
кінець тексту і продовжити набір.
Для виклику наступної функції СТЕПЕНЬ виконують дії, як при виклику
функції ABS. Функція СТЕПЕНЬ містить два параметри: перший — це вираз,
який зводиться у ступінь, а другий — це значення самого ступеня:
Потім знову у кінець рядка формул і так далі, поки формула не буде
повністю внесена.
Після закінчення уведення тексту формули мишкою натискається Ok і
результат обчислення заноситься у комірку D21. Використовуючи кнопку
автозаповнення, введену формулу копіюють у комірки вниз по стовпчику до
рядка 26:
Усі позначені комірки заповнюються обчисленими значеннями, при цьому
посилання на коефіцієнти залишаються у формулах без змін, а посилання на
значення Х змінюється залежно від нового положення формули, тобто
реалізується циклічний алгоритм.
Для обчислення Y2, Y3 та Y4 використовується логічна функція ЕСЛИ. Вона
реалізує алгоритм із розгалуженням і містить три аргументи:
=ЕСЛИ(умова; дія, якщо умова ІСТИННА; дія, якщо умова ХИБНА)
Етапи розв’язання задачі:
У випадку обчислення виразу Y2 перевіряється, чи поточне значення Х
більше за 10 і, якщо ця умова виконується, то обчислення провадиться за
виразом:
якщо ж умова не виконується, обчислення провадиться за виразом
.
Повний текст функції:
=ЕСЛИ(C20>10;EXP(SIN(C20))–
КОРЕНЬ(C20^3);LN(ABS(C20)+1)–COS(C20^2+C20–3,14*$A$20*$B$20))
Блок-схема алгоритму вирішення задачі подана нижче.
Так само, як у попередньому виразі, текст функції можна вводити або з
клавіатури, або користуватися послугами Майстра функцій:
Перший рядок аргументів містить умову С20>10;
Другий рядок аргументів містить формулу, якщо умова виконується — вираз
EXP(SIN(C20))-КОРЕНЬ(C20^3).
Третій рядок аргументів містить формулу, якщо умова
не виконується — вираз LN(ABS(C20)+1)–COS(C20^2+C20–3,14*$A$20*$B$20)
При цьому знову викликаються вкладені функції — EXP, SIN, КОРЕНЬ, LN,
ABS, COS. Після введення всього тексту треба повернутися у викликаючу
функцію ЕСЛИ і натиснути на Ok.
У деяких випадках, якщо формула містить помилки, може з’явитися вікно
повідомлень:
Microsoft Excel запропонує виправити помилку, і треба або погодитися
(кнопка Да), або ні (кнопка Нет) і тоді вносити виправлення самостійно.
Якщо ж формула помилок не містила, обчислене значення запишеться в
активну комірку.
Для визначення виразу Y3 так само використовують
функцію ЕСЛИ, проте аргументами функції є не тільки формули для
обчислення виразу, а й укладені функції ЕСЛИ
у випадку, якщо введена умова виконується або не виконується.
У даному випадку значення функції може визначатися :
4. При x > 20 функція Y3 не визначена
5. При x = 6 функція Y3 не визначена
Реалізується алгоритм з розгалуженням і перевіркою складної умови.
A A a a
ae
XZUUeh
j
v
x
?
U
Ue
u
ue
v
v
x
ue
??
??
??
??
??
??
??
??
??
??
??
j ? I t ae @ ?thhhhhhhh
‹µ…}…}…}…}…}…}…OewOe…p
??
&
F
$a$
&
F
eR
eR
¤E ¤x
¤ a$
©
NH
©
©
©
1/2
1/2
1/2
$ ¤xa$
ja
NH
.ється умова, чи значення Х > 6, тобто визначається інтервал, у якому
функція Y3 визначається за позицією 2. Якщо ж умова і тепер не
виконується, то знову викликається функція ЕСЛИ і перевіряється умова,
чи значення Х = 6, тоді значення функції Y3 не визначене (позиція 5). І
нарешті, якщо і ця умова не виконується, залишається інтервал, де
значення Х автоматично стає меншим за 6, тобто записується вираз для
обчислення за позицією 1.
Повний текст функції Y3 показаний нижче:
=ЕСЛИ(C2020;”невизначена”;ЕСЛИ(С20>14; EXP($A$20*$B$20*
C20); LN($A$20*$B$20+C20^3)))))
Нижче наведена блок-схема алгоритму:
Перший етап перевірки:
Другий етап перевірки:
Третій етап перевірки:
Четвертий етап перевірки:
Потім натискається Ok, обчислене значення записується у комірку і
копіюється униз по стовпчику до рядка 27.
У наведеному вище прикладі всі умови, що перевіряли-
ся, були простими, тобто за один раз перевірялася лише
одна умова, проте відокремлювалися і визначалися інтервали, в яких
функція Y3 набувала того чи іншого значення (позиції 1—5).
Ті ж самі інтервали можна визначити за допомогою складної перевірки.
Наприклад, значення функції Y3 не визначене, якщо Х або дорівнює 6, або
більший за 20 (позиції 4 та 5), якщо ж значення Х лежить у інтервалі, де
Х більший за 6 та менший або дорівнює 14, то значення функції Y3
обчислюється за позицією 2, в інтервалі, де Х змінюється від значення,
що більше за 14, але менше та дорівнює 20, то значення функції Y3
обчислюється за позицією 3, якщо ж Х не лежить у жодному з перерахованих
інтервалів, то він автоматично розташований в інтервалі, що менший за 6,
і тоді функція Y3 визначається за позицією 1.
Для організації складних перевірок використовують логічні функції И або
ИЛИ, кожна з яких може містити максимум 30 простих умов:
=И(умова 1; умова 2; умова 3; …; умова 30)
=ИЛИ(умова 1; умова 2; умова 3; …; умова 30).
Функція И набуває значення ІСТИНА, якщо кожна пе-
рерахована у ній умова виконується, і набуває значення
ХИБА, якщо хоча б одна з перерахованих умов не виконується.
Функція ИЛИ набуває значення ІСТИНА, якщо хоча б одна з перерахованих
умов виконується, і набуває значення ХИБА, якщо жодна з указаних умов не
виконується.
І функцію И, і функцію ИЛИ можна використовувати як перший аргумент
умови у складі функції ЕСЛИ.
Повний текст функції для визначення значення Y4 із застосуванням
вкладених функцій И та ИЛИ показаний нижче:
=ЕСЛИ(ИЛИ(C20>20;C20=6);”невизначена”;ЕСЛИ(И(C20>14;C206;C20 10
Истина
Ложь
Y
Кінець
Початок
а,b
x
x 20
Истина
Ложь
x > 14
Y
Кінець
Функція не визначена
Нашли опечатку? Выделите и нажмите CTRL+Enter
