Значення помилок у формулах
Вступ
Microsoft Office, найпопулярніше сімейство офісних програмних продуктів, включає в себе нові версії знайомих додатків, які підтримують технології Internet, і дозволяють створювати гнучкі інтернет-рішення
Microsoft Office - сімейство програмних продуктів Microsoft, яке об'єднує найпопулярніші у світі додатки в єдине середовище, ідеальну для роботи з інформацією. У Microsoft Office входять текстовий процесор Microsoft Word, електронні таблиці Microsoft Excel, засіб підготовки і демонстрації презентацій Microsoft PowerPoint і новий додаток Microsoft Outlook. Всі ці програми складають Стандартну редакцію Microsoft Office. У Професійну редакцію входить також СУБД Microsoft Access.
Microsoft Excel - програма призначена для організації даних у таблиці для документування та графічного представлення інформації.
Програма MS Excel застосовується при створенні комплексних документів в яких необхідно:
• використовувати одні й ті ж дані в різних робочих аркушах;
• змінити і відновлювати зв'язки.
Перевагою MS Excel є те, що програма допомагає оперувати великими обсягами інформації. робочі книги MS Excel надають можливість зберігання та організації даних, обчислення суми значень в осередках. Ms Excel надає широкий спектр методів дозволяють зробити інформацію простий для сприйняття.
У наш час, кожній людині важливо знати і мати навички в роботі з додатками Microsoft Office, так як сучасний світ насичений величезною кількістю інформацією, з якою просто необхідно вміти працювати.
Більш докладно в цій курсової буде представлено додаток MS Excel, його функції і можливості. А також використання сценаріїв з їх практичним застосуванням.
Загальне уявлення про функції MS Excel
Функції в Excel використовуються для виконання стандартних обчислень в робочих книгах. Значення, що використовуються для обчислення функцій, називаються аргументами. Значення, що повертаються функціями як відповідь, називаються результатами. Крім вбудованих функцій ви можете використовувати в обчисленнях користувальницькі функції, які створюються за допомогою засобів Excel. Щоб використовувати функцію, потрібно ввести її як частину формули в комірку робочого аркуша. Послідовність, в якій повинні розташовуватися використовувані у формулі символи, називається синтаксисом функції. Всі функції використовують однакові основні правила синтаксису. Якщо ви порушите правила синтаксису, Excel видасть повідомлення про те, що у формулі є помилка. Якщо функція з'являється на самому початку формули, їй повинен передувати знак рівності, як і у всякій іншій формулі.
Аргументи функції записуються в круглих дужках відразу за назвою функції і відокремлюються один від одного символом крапка з комою ";". Дужки дозволяють Excel визначити, де починається і де закінчується список аргументів. Усередині дужок повинні розташовуватися аргументи. Пам'ятайте про те, що при записі функції повинні бути присутніми відкриває і закриває дужки, при цьому не слід вставляти пробіли між назвою функції і дужками.
В якості аргументів можна використовувати числа, текст, логічні значення, масиви, значення помилок або посилання. Аргументи можуть бути як константами, так і формулами. У свою чергу ці формули можуть містити інші функції. Функції, що є аргументом іншої функції, називаються вкладеними. У формулах Excel можна використовувати до семи рівнів вкладеності функцій.
Запитання й вхідні параметри повинні мати допустимі для даного аргументу значення. Деякі функції можуть мати необов'язкові аргументи, які можуть бути відсутні при обчисленні значення функції.
Всі функції в Excel характеризуються:
· назвою;
· призначенням (що, власне, вона робить);
· кількістю аргументів (параметрів);
· типом аргументів (параметрів);
· типом значення, що повертається.
Для зручності роботи функції в Excel розбиті по категоріях: функції управління базами даних і списками, функції дати і часу, DDE / Зовнішні функції, інженерні функції, фінансові, інформаційні, логічні, функції перегляду і посилань. Крім того, присутні такі категорії функцій: статистичні, текстові та математичні.
За допомогою текстових функцій є можливість обробляти текст: витягати символи, знаходити потрібні, записувати символи в суворо визначений місце тексту і багато іншого.
Логічні функції допомагають створювати складні формули, які залежно від виконання тих чи інших умов, робитимуть різні види обробки даних.
У Excel широко представлені математичні функції. Наприклад, можна виконувати різні операції з матрицями: множити, знаходити зворотну, транспонувати.
Функції перегляду і посилань дозволяє «переглядати» інформацію, що зберігається в списку або таблиці, а також обробляти посилання.
Як ми бачимо, щоб Excel не видав помилку, функція повинна відповідати певному набору правил. Цей набір правил називається синтаксис запису функції.
Загальний синтаксис запису будь-якої функції в Excel:
імя_функциі ([аргумент_1; аргумент_2; ...; аргумент_N])
Список аргументів укладений у квадратні дужки, що говорить про те, що це необов'язкова частина. Деякі функції взагалі не приймають аргументів. Однак, навіть якщо функція не приймає аргументів, порожні круглі дужки писати обов'язково, інакше Excel видасть помилку! Деякі функції приймають РІВНЕ ОДИН аргумент. Наприклад функції sin (число), cos (число) і т. п. Деякі функції приймають більше, ніж один аргумент. У такому випадку аргументи розділяються між собою крапкою з комою «;».
У загальному випадку, аргументами функції можуть бути константи (числа, введені вручну), посилання на клітинки, посилання на діапазон комірок, іменовані посилання та інші функції (вкладені функції).
Існує кілька способів введення функції:
· введення функцій вручну;
· введення функції за допомогою кнопки "сигма";
· робота з майстром функцій;
Для набору найпростіших формул, що містять функції, можна не користуватися спеціальними засобами, а просто писати їх. Однак цей спосіб погано підходить для набору довгих формул.
Занадто велика ймовірність допустити помилку, набираючи вручну складні і довгі формули, і на це йде багато часу.
Один із засобів полегшити і прискорити роботи з функціями - кнопка на панелі інструментів «Стандартна». У ній розробники Microsoft «сховали» п'ять часто використовуваних функцій:
СУМ (мінімум один, максимум 30 аргументів). Підсумовує свої аргументи.
СРЗНАЧ (мінімум один, максимум 30 аргументів). Знаходить середнє арифметичне аргументів;
РАХУНОК (мінімум один, максимум 30 аргументів). Підраховує кількість чисел у списку аргументів (використовується для підрахунку кількості комірок з числами, порожні клітинки і текст ігноруються);
МАКС (мінімум один, максимум 30 аргументів). Повертає максимальний аргумент;
МІН (мінімум один, максимум 30 аргументів). Повертає мінімальний аргумент.
Принцип роботи: активізуємо осередок, де має бути результат (просто клацаємо); натискаємо на стрілочку праворуч від кнопки "сигма"; вибираємо потрібну функцію. Після вибору Excel сам вставить знак "=", ім'я функції, круглі дужки, і навіть спробує вгадати діапазон, який ми ходимо виділити (хоча він рідко вгадує); виділяємо зв'язний діапазон комірок. Excel вставить в круглі дужки адресу виділеного діапазону; якщо потрібно, наприклад, підсумувати числа з незв'язних діапазонів, затискаємо Ctrl, і виділяємо потрібну кількість діапазонів. Excel сам поставить крапку з комою, і вставить посилання на інший діапазон; коли виділили всі потрібні діапазони, для завершення натискаємо Enter; в процесі виділення можна натискати F4 для зміни типу посилання і F3 для вставки іменованих посилань.
При написанні складних формул, особливо використовують вкладені функції, використання майстра функцій - найкраще рішення. Він дуже полегшує і прискорює введення формул, і робить багато речей за нас: автоматично вставляє знак "дорівнює", ім'я функції, круглі дужки, розставляє крапки з комою. Дозволяє переглядати значення посилань і результати проміжних обчислень.
Існує 3 способи запуску майстра функцій:
· за допомогою кнопки в рядку формул;
· за допомогою команди "Інші функції ..." кнопки;
· за допомогою пункту меню "Вставка" "Функція";
Після виконання одного з цих дій відкриється вікно майстра функцій. Ми вибираємо потрібну нам функцію, користуючись пошуком або фільтром категорій. Після вибору потрібної функції натискаємо "ОК" і виділяємо необхідний діапазон осередку
В одну функцію можна вставити іншу функцію. Звичайно, функцію можна записати вручну (писати назву вкладеної функції, відкривати дужки, ставити крапки з комою). Однак це суперечить самій ідеології майстра функцій, який повинен полегшувати написання формул, захищати користувача від помилок і звести до мінімуму ручну роботу. Існує більш зручний спосіб вкласти функцію - спеціальна кнопка на панелі "Рядок формул".
Категорій функцій MS Excel
У Microsoft Excel використовується більше 100 функцій, об'єднаних за категоріями:
Функції роботи з базами даних можна використовувати, якщо необхідно переконатися в тому, що значення списку задовольняють умові. З їх допомогою, наприклад, можна визначити кількість записів у таблиці про продажі або витягти ті записи, в яких значення поля «Сума» більше 1000, але менше 2500.
Функції роботи з датою і часом дозволяють аналізувати і працювати зі значеннями дати і часу у формулах. Наприклад, якщо потрібно використовувати у формулі поточну дату, скористайтесь функцією СЬОГОДНІ, що повертає поточну дату з системних годинах.
Інженерні функції служать для виконання інженерного аналізу. Це функції для роботи з комплексними змінними, функції для перетворення чисел з однієї системи числення в іншу (десяткову, шістнадцяткову, вісімкову, двійкову) і функції для перетворення величин з однієї системи мір і ваг в іншу.
Фінансові функціїздійснюють такі типові фінансові розрахунки, як обчислення суми платежу за позикою, обсяг періодичної виплати по вкладенню або позиці, вартість вкладення або позики по завершенні всіх відкладених платежів і т.д.
Інформаційні функції призначені для визначення типу даних, що зберігаються в комірці. Вони перевіряють виконання якогось умови і повертають в залежності від результату значення ІСТИНА або БРЕХНЯ. Так, якщо клітинка містить парне значення, функція ЕЧЕТН повертає значення ІСТИНА. Якщо в діапазоні функцій є порожня клітинка, можна скористатися функцією СЧІТАТЬПУСТОТИ.
Логічні функції призначені для перевірки виконання умови або для перевірки декількох умов. Так, функція ЯКЩО дозволяє визначити, чи виконується зазначена умова, і повертає одне значення, якщо умова істинно, і інше, - якщо воно помилкове.
Функції посилання і автопідстановки здійснюють пошук в списках або таблицях. Наприклад, для пошуку значення в таблиці використовуйте функцію ВПР, а для пошуку положення значення у списку - функцію ПОІСКПОЗ.
Арифметичні та тригонометричні функції дозволяють проводити прості і складні математичні обчислення, наприклад обчислення суми діапазону комірок, обчислення суми осередків діапазону, що задовольняють вказаній умові, округлення чисел та інше.
Статистичні функції дозволяють виконувати статистичний аналіз діапазонів даних. Наприклад, можна провести пряму по групі значень, обчислити кут нахилу і точку перетину з віссю Y та інше.
Функції обробки тексту дозволяють проводити дії над рядками тексту, наприклад змінити регістр або визначити довжину рядка. Можна також об'єднати кілька рядків в одну. Наприклад, за допомогою функцій СЬОГОДНІ і ТЕКСТ можна створити повідомлення, що містить поточну дату і привести його до вигляду «дд-ммм-рр»: = «Балансовий звіт від» & ТЕКСТ (СЬОГОДНІ (), «дд-мм-рр»)
Якщо необхідно виконати складні обчислення, скористайтеся можливістю створення своїх власних нестандартних функцій. Їх створити за допомогою мови VBA.
Розглянемо основні функції.
· МІН (number1; чісло2; ...; чісло30) МАКС (число1; число2; ...; чісло30) Функції МІН і МАКС приймають від 1 до 30 аргументів (у Office 2007 - до 255) і повертає мінімальний / максимальний з них. Якщо в якості аргументу передати діапазон комірок, з діапазону буде вибрано мінімальне / максимальне значення. Ці функції також можуть бути вставлені за допомогою кнопки "сигма".
· СРЗНАЧ (number1; чісло2; ...; чісло30) Функція СРЗНАЧ (середнє значення) приймає від 1 до 30 аргументів (у Office 2007 - до 255) і повертає їх середнє арифметичне (сума чисел, поділена на кількість чисел). Цю функцію також можна вставити за допомогою кнопки "сигма"
· СТУПІНЬ (число; ступінь) Функція СТУПІНЬ повертає результат зведення першого аргументу ("число"), в ступінь, зазначену в другому аргументі ("ступінь").
· СУМ(арг1; АРГ2; ...; арг30) Функція СУММ приймає від 1 до 30 аргументів (у Office 2007 - до 255) і повертає їх суму. В якості аргументів можна передавати адреси діапазонів (що найчастіше і робиться), в цьому випадку підсумуйте всі числа в діапазоні.
· РАХУНОК (арг1; АРГ2; ...; арг30) Функція РАХУНОК приймає від 1 до 30 аргументів (у Office 2007 - до 255) і повертає кількість аргументів, які є числами. Найчастіше функції просто передають адресу діапазону, а вона підраховує кількість осередків з числами.
· ПІ () Повертає значення тригонометричної константи pi = 3,1415 ...
· Вироблено (арг1; АРГ2; ...; арг30) Функція заброньований приймає від 1 до 30 аргументів (у Office 2007 - до 255) і повертає їх твір. В якості аргументів можна передавати адреси діапазонів, в цьому випадку перемножуються всі числа в діапазоні.
· СУММЕСЛІЧасто необхідно не просто просуміровать всі значення в діапазоні, а включити в суму тільки ті, які задовольняють певній умові. Функція СУММ підсумовує все, що їй дають, функція СУММЕСЛІ дозволяє підсумувати лише значення, відповідні певній умові
· СУММЕСЛІ (діапазон; критерій; [діапазон_суммірованія]) Перевіряється діапазон, кожна клітинка з якого перевіряється на відповідність умові, вказаному у другому аргументі. Умова для підсумовування, на відповідність яким перевіряється кожна клітинка з перевіряється діапазону. Якщо необхідно використовувати операцію порівняння, то "логічне вираження" вказується без лівого операнта і полягає в подвійні лапки (наприклад, "> = 100" - підсумовувати всі числа, більші 100). Також можна використовувати текстові значення (наприклад, "яблука" - підсумовувати всі значення, що знаходяться навпроти тексту "яблука") і числові (наприклад, 300 - підсумовувати значення в осередках, значення в яких 300).
Необов'язковий аргумент, використовується тоді, коли перевірявся діапазон і діапазон підсумовування знаходяться в різних діапазонах. Якщо він не вказаний, то в якості діапазону підсумовування використовується перевірявся діапазон (перший аргумент). Якщо він зазначений, то підсумовуються значення з комірок цього діапазону, що знаходяться "навпроти" відповідних осередків перевіряється діапазону.
СЧЕТЕСЛІ Працює дуже схоже на функцію СУММЕСЛІ. На відміну від СУММЕСЛІ, яка підсумовує значення з комірок, СЧЕТЕСЛІ підраховує кількість осередків, що задовольняють певній умові. Якщо написати формулу СУММЕСЛІ ("> 10", A1: A10), буде підрахована сума значень з комірок, значення в яких більше 10. Якщо ж написати СЧЕТЕСЛІ ("> 10", A1: A10), буде підраховано кількість осередків, значення в яких більше 10.
СЧЕТЕСЛІ (діапазон; критерій) Перевіряється діапазон, кожна клітинка з якого перевіряється на відповідність умові, вказаному у другому аргументі. З цього ж діапазону відбувається підрахунок кількості осередків.
Критерій - умова, на відповідність яким перевіряється кожна клітинка з першого аргументу. Умова записується аналогічно СУММЕСЛІ.
У прикладі вище фактично підраховується кількість клітинок, які містять текст "Яблука".
Призначення: функція ЯКЩО виконує те ("Значення якщо ІСТИНА") чи інше ("Значення якщо БРЕХНЯ") дію залежно від того, виконується (так само ІСТИНА) умова чи ні (так само БРЕХНЯ).
Аргумент1. Логічний вираз: Все, що дає в результаті логічні значення БРЕХНЯ або ІСТИНА. Зазвичай або вираження ставлення (A1> = 12) або функції, які повертають логічні значення (І, АБО).
Аргумент2. Значення якщо ІСТИНА: будь-яке допустиме в Excel вираз.
Аргумент3. Значення якщо БРЕХНЯ: будь-яке допустиме в Excel вираз.
повертається значення: може повертати значення будь-яких типів, залежно від аргументів 2 і 3.
Логіч_знач І (логіч_знач1; логіч_знач2; ...; логіч_знач30)
Призначення: функція І використовується тоді, коли потрібно перевірити, чи виконуються декілька умов ОДНОЧАСНО. Одне з найбільш часто використовуваних застосувань функції І - перевірка, чи потрапляє число x в діапазон від x1 до x2.
Аргументи: Функція І приймає від 1 до 30 аргументів (у Office 2007 - до 256), кожен з яких є логічним значенням БРЕХНЯ або ІСТИНА, або будь-яким виразом або функцією, яке в результаті дає БРЕХНЯ або ІСТИНА. , Що повертається: Функція І повертає логічне значення. Якщо ВСЕ аргументи функції І рівні ІСТИНА, повертає ІСТИНА.
Логіч_знач АБО (логіч_знач1; логіч_знач2; ...; логіч_знач30)
Призначення: Функція АБО використовується тоді, коли потрібно перевірити, чи виконується ХОЧА-БИ ОДНЕ з багатьох умов.
Аргументи: Функція АБО приймає від 1 до 30 аргументів (у Office 2007 - до 256), кожен з яких є логічним значенням БРЕХНЯ або ІСТИНА, або будь-яким виразом або функцією, яке в результаті дає БРЕХНЯ або ІСТИНА. , Що повертається: функція АБО повертає логічне значення. Якщо ХОЧА Б ОДИН аргумент має значення ІСТИНА, повертає ІСТИНА.
Функція ВПР (Вертикальне Перше Рівність) Для функції необхідно внести наступні дані: яке значення шукати (вказується осередок містить значення), масив в якому відбувається пошук і номер стовпчика в якому знаходиться значення, номер повторного значення в масиві, значення якого і буде використано, яке треба повернути. Функція значно розширює свої можливості при використанні її в парі з логічною функцією ЕСЛИ (). !!
ВПР (що шукаємо, таблиця де шукаємо, з якого стовпця взяти значення, булевська мінлива актуальна для дуже великих відсортованих таблиць, для малих завжди ставимо 0 - повний перебір таблиці). Приміром = ВПР (A1, B: D, 2, 0) Ця функція потрібна наприклад коли вам потрібно копіювати дані з однієї таблиці в іншу. Припустимо, в одній таблиці у вас виробництво за моделями, а в іншій-продажу за моделями. У цих таблицях моделі не по порядку. Ви можете додати колонку "продажу" в таблицю "виробництво" і за допомогою функції ВПР знайти відповідні значення продажів з іншої таблиці. Використання функції автоматично знаходить в зазначеній таблиці потрібне значення і позбавляє від ручних копіювань. Примітка: ВПР вибирає тільки одне значення. Якщо у вас в таблицях моделі повторюються, необхідно використовувати функцію СУММЕСЛІ.
Поняття формули в Excel
Формулою в Excel називається послідовність символів, що починається зі знака рівності "=". У цю послідовність символів можуть входити постійні значення, посилання на клітинки, імена, функції чи оператори. Результатом роботи формули є нове значення, яке виводиться як результат обчислення формули за вже наявними даними. Якщо значення в комірках, на які є посилання у формулах, міняються, то результат зміниться автоматично.
Як приклад наведемо формули, що обчислюють корені квадратного тричлена ах 2 Ьх з = 0. Вони введені в комірки А2 і A3 і мають такий вигляд:
= (-В1 КОРІНЬ (В1 * В1 -4 * А1 * С1)) / 2/А1
= (-В1-КОРІНЬ (В1 * В1 -4 * А1 * С1)) / 2/А1
В осередках А1, В1 і С1 знаходяться значення коефіцієнтів a, b і с відповідно. Якщо Ви ввели значення коефіцієнтів а = 1, Ь = -5 і с = 6 (це означає, що в осередках А1, В1 і С1 записані числа 1, -5 і 6), то в комірках А2 і A3, де записані формули, Ви отримаєте числа 2 і 3. Якщо Ви зміните число в комірці А1 на -1, то в комірках з формулами Ви отримаєте числа -6 і 1.
У таблиці Excel в осередках А2 і A3 представлений результат роботи формули. Якщо курсор встановлений на осередку А2, то в рядку формул відображається записана у комірці А2 формула для знаходження кореня квадратного тричлена. Взагалі в рядку формул відображається вміст комірки, в якій розташований курсор.
У наведених вище формулах використовується функція КОРІНЬ, яка обчислює квадратний корінь числа. У даному випадку буде витягнутий квадратний корінь з числа, що вийшло у результаті обчислення виразу:
Bl * Bl-4 * Al * Cl.
Поняття функції в Excel
Функції в Excel використовуються для виконання стандартних обчислень в робочих книгах. Значення, які вживаються для обчислення функцій, називаються аргументами.
Значення, що повертаються функціями як відповідь, називаються результатами.
Крім вбудованих функцій Ви можете застосувати в обчисленнях користувальницькі функції, які створюються за допомогою засобів Excel.
Щоб використовувати функцію, потрібно ввести її як частину формули в комірку робочого аркуша. Послідовність, в якій повинні розташовуватися застосовувані у формулі символи, називається синтаксисом функції.
Всі функції використовують однакові основні правила синтаксису. Якщо Ви порушите правила синтаксису, Excel видасть повідомлення про те, що у формулі є помилка.
Правила синтаксису при записі функцій
Якщо функція з'являється на самому початку формули, їй повинен передувати знак рівності, як зазвичай на початку формули.
Аргументи функції записуються в круглих дужках відразу за назвою функції і відокремлюються один від одного символом крапка з комою ";". Дужки дозволяють Excel визначити, де починається і де закінчується список аргументів. Усередині дужок повинні розташовуватися аргументи. Пам'ятайте про те, що при записі функції повинні бути присутніми відкриває і закриває дужки, при цьому не слід вставляти пробіли між назвою функції і дужками.
В якості аргументів можна використовувати числа, текст, логічні значення, масиви, значення помилок або посилання. Аргументи можуть бути як константами, так і формулами. У свою чергу ці формули можуть містити інші функції. Функції, що є аргументами іншої функції, називаються вкладеними. У формулах Excel можна використовувати до семи рівнів вкладеності функцій.
Запитання й вхідні параметри повинні мати допустимі для даного аргументу значення. Деякі функції можуть мати необов'язкові аргументи, які можуть бути відсутні при обчисленні значення функції.
Розглянемо випадок, коли у функції немає аргументів. Прикладами таких функцій є ПІ, яка повертає число, або функція СЬОГОДНІ, що повертає поточну дату. При введенні таких функцій потрібно відразу після назви функції поставити круглі дужки. Якщо Ви хочете отримати в комірці число p або поточну дату, то введіть в комірки формули такого вигляду:
= ПІ ()
= СЬОГОДНІ ()
Для того щоб обчислити значення функції, введіть в комірку знак рівності "=", а потім назва функції і список її аргументів.
Розглянемо функцію ROUND (арг1; АРГ2), яка повертає число, округлене до заданого числа знаків після коми. Ця функція має два аргументи:
apгl - адреса комірки з числом (або саме число), яке потрібно округлити;
АРГ2 - кількість цифр після коми у числа після округлення.
Для округлення числа виконайте такі дії.
Введіть число 2,71828 в клітинку А1. Це число ми будемо округляти.
Введіть у клітинки В1, С1 і D1 формули такого вигляду:
= ROUND (А1; 1)
= ROUND (А1, 2)
= ROUND (А1; 3)
Ці формули округлюють число 2,71828 знаходиться в осередку А1, до одного, двох і трьох знаків після коми. Результати округлення Ви бачите в осередках В1, С1 і D1.
Не можна вставляти пробіли між назвою функції і дужками, в яких записані аргументи. В іншому випадку Excel видасть повідомлення про помилку "# ІМ'Я?".
Excel містить більше 400 вбудованих функцій. Тому безпосередньо вводити з клавіатури у формулу назви функцій і значення вхідних параметрів не завжди зручно. У Excel є спеціальний засіб для роботи з функціями - Майстер функцій.
При роботі з цим засобом Вам спочатку пропонується вибрати потрібну функцію зі списку категорій, а потім у вікні діалогу пропонується ввести вхідні значення.
Майстер функцій викликається командою Вставка / Функція.
або натисканням на кнопку Вставка функції. Ця кнопка розташована на панелі інструментів Стандартна, а також у рядку формул.
Натисніть кнопку ОК. З'явиться вікно діалогу Майстер функцій - крок 2 із 2.
Для отримання готової формули натискають кнопку ОК. При цьому готова формула вставляється у відповідну клітинку.
Введення і редагування формул
У формулах Ви можете використовувати операції додавання "+", віднімання "-", множення "*", ділення "/", зведення в ступінь "^". Ви також можете використовувати знак взяття відсотка "%", дужки "(", ")". При запису часу використовується символ двокрапки ":". Крім того, в довільне місце формули Ви можете за допомогою кнопки Вставка функції вставити будь-яку з численних функцій Excel. Ви можете редагувати як параметри функцій, які у формулі, так і безпосередньо символи формули. Для внесення змін у формулу клацніть мишею в рядку формул або натисніть клавішу F2. Потім внесіть зміни та натисніть кнопку введення в рядку формул або клавішу Enter. Якщо Ви хочете внести зміни у формулу безпосередньо в тій комірці, де вона записана, то двічі натисніть мишею на клітинку з цією формулою. Для скасування змін виберіть кнопку скасування в рядку формул або натисніть клавішу Esc. Значення формули виводиться в числовому форматі клітинки, яка містить формулу. Якщо формат числа для цього осередку не встановлений, значення формули виводиться в числовому форматі першою з клітинок, на які посилається формула. Наприклад, якщо для клітинки, яка містить формулу "= А1 + А2", не встановлено формат числа, значення формули виводиться в числовому форматі, встановленому для комірки А1.
Використання посилань
Посилання однозначно визначає комірку чи групу комірок робочого аркуша. Посилання вказують на те, в яких осередках перебувають значення, які потрібно застосувати в якості аргументів формули. За допомогою посилань можна використовувати у формулі дані, що знаходяться в різних місцях робочого листа, а також використовувати значення однієї і тієї ж осередки у кількох формулах.
Можна також посилатися на комірки, що знаходяться на інших аркушах робочої книги, в іншій робочій книзі або навіть на дані іншого додатка. Посилання на клітинки інших робочих книг називаються зовнішніми. Посилання на дані в інших додатках називаються вилученими.
Посилання на клітинки використовують заголовки відповідних рядків і стовпців робочого листа. У Excel стовпці позначені латинськими літерами (А, В, С, D, ..., Z; АА, АВ, AC, AD, ..., AZ; ВА, ВВ, ВС і т. д.), а рядки пронумеровані . Такі посилання називаються посиланнями типу А1.
Можна замінити літери в заголовках стовпців на номери. Такий тип посилань називається R1C1. Посилання на активну клітинку виводиться на екран в області посилань, розташованої ліворуч від рядка формул.
У наступному прикладі Вам належить використовувати у формулі посилання на різні листи робочої книги. Розглянемо формулу для обчислення коренів квадратного тричлена ах 2 + Ьх + с.
Коли коефіцієнти a, b і с розташовані в комірках Al, B1 і С1, формули для обчислення коренів мають наступний вигляд:
= (-В1 + КОРІНЬ (В1 * В1-4 * А1 * С1)) / 2/А1
= (-В1-КОРІНЬ (В1 * В1-4 * А1 * С1)) / 2/А1
Коли коефіцієнти a, b і с розташовані на аркушах 7, 8 і 9 (все в осередках А1), формули для обчислення коренів мають вигляд:
= (-Ліст8! А1 + КОРІНЬ (Ліст8! А1 ^ 2-4 * Ліст7! А1 * Ліст9! А1)) / 2/Ліст7! А1
= (-Ліст8! А1-КОРІНЬ (Ліст8! А1 ^ 2-4 * Ліст7! А1 * Ліст9! А1)) / 2/Ліст7! А1
Ви можете отримати такі формули двома способами. Перший спосіб полягає в тому, що необхідні вирази просто вводяться в рядок формул.
Другий спосіб полягає в тому, що Ви вказуєте мишею клітинки, значення з яких беруть участь в обчисленнях. Наведемо послідовність дій для отримання першого посилання на інший аркуш у першій з двох останніх формул:
Введіть символи = (- в рядок формул. Потрібно вставити коефіцієнт "Ь", який знаходиться в осередку А1 листа 8.
Натисніть на ярличок аркуша 8. Лист 8 став активним. У рядку формул Ви бачите символи виду:
= (-Ліст8!
Натисніть на клітинку А1. У рядку формул Ви бачите символи виду:
= (-Ліст8! А1
Використання імен у формулах
Ім'я - це легко запам'ятовується ідентифікатор, який можна використовувати для посилання на осередок, групу осередків, значення або формулу. Застосування імен забезпечує наступні переваги.
Формули, що використовують імена, легше сприймаються і запам'ятовуються, ніж формули, що використовують посилання на комірки. Наприклад, формула "= Активи-Пасиви" набагато зрозуміліша, ніж формула "= F6-D6".
При зміні структури робочого аркуша досить оновити посилання лише в одному місці - у визначенні імен, і всі формули, які використовують ці імена, будуть оперувати з коректними посиланнями.
Після того як ім'я визначене, воно може застосовуватися в будь-якому місці робочої книги. Доступ до всіх імен з будь-якого робочого аркуша можна одержати за допомогою вікна імені в лівій частині рядка формул.
Ви можете також визначити спеціальні імена, діапазон дії яких обмежується поточним робочим листом. Це означає, що ці імена можна використовувати лише на тому робочому аркуші, на якому вони визначені. Такі імена не відображаються у вікні імені рядка формул або вікні діалогу Присвоєння імені, викликається командою Вставка / Ім’я / Присвоїти, якщо активний інший робочий аркуш книги.
Після того як ім'я визначено, Ви можете:
· Замінити всі відповідні посилання цим ім'ям у всіх місцях робочого листа. Наприклад, визначивши ім'я «Прибуток» як "= $ F $ 12", можна замінити всі посилання на комірку $ F $ 12 ім'ям «Прибуток";
· Швидко перейти на пойменоване посилання, замінити посилання, вставити посилання у формулу за допомогою вікна імені в рядку формул;
· Присвоїти ім'я формулі, не розміщуючи цю формулу в комірку. Це допомагає скоротити розміри робочого листа, оскільки в цьому випадку Excel зберігає формулу тільки один раз, а не при кожному використанні формули в осередках.
Привласнюйте ім'я формулою в тому випадку, якщо одна і та ж формула використовується в багатьох місцях робочого листа.
Присвоєння імені формулою особливо корисно для формул, де застосовуються відносні посилання.
При зміні визначення пойменованої формули всі комірки, що використовують це ім'я, автоматично оновлюються.
Значення помилок у формулах
Excel виводить у вічко значення помилки, коли формула для цього осередку не може бути правильно обчислена. Якщо формула містить посилання на комірку, яка містить значення помилки, то ця формула також буде виводити значення помилки (за винятком тих випадків, коли використовуються спеціальні функції робочих аркушів ЕОШ, ЕОШІБКА або ЕНД, які перевіряють наявність значень помилок). У вас може виникнути необхідність простежити залежності для ряду осередків з посиланнями для того, щоб визначити джерело помилки.
| Код ошибки | Возможные причины |
| #ДЕЛ/0! | В формуле делается попытка деления на ноль |
| #имя? | Excel не смог распознать имя, использованное в формуле |
| #ПУСТО! | Вы задали пересечение двух областей, которые не имеют общих ячеек |
| #Н/Д | Нет доступного значения. Обычно такое значение ошибки непосредственно вводится в те ячейки рабочего листа, которые впоследствии будут содержать данные, отсутствующие в настоящий момент. Формулы, ссылающиеся на такие ячейки, также возвращают #Н7Д вместо вычисленного значения |
| #ЧИСЛО! | При операциях с числами неверно указан аргумент либо невозможно посчитать результат |
| #ССЫЛКА! | Формула неправильно ссылается на ячейку |
| #ЗНАЧ! | Аргумент или операнд имеют недопустимый тип |
Переміщення та копіювання формул
Після того як формула введена в комірку, Ви можете її перенести, скопіювати або поширити на блок комірок.
При переміщенні формули в нове місце таблиці посилання у формулі не змінюються, а комірка, де раніше була формула, стає вільною. При копіюванні формула переміщується в інше місце таблиці, посилання змінюються, але комірка, де раніше знаходилася формула, залишається без зміни. Формулу можна поширити на блок комірок.
При копіюванні формул виникає необхідність керувати зміною адрес комірок чи посилань. Для цього перед символами адреси чи комірок посилання встановлюються символи "$". Змінюватися будуть тільки ті частини адреси осередку, перед якими не стоїть символ "$". Якщо перед усіма частинами адреси осередку поставити символ "$", то при копіюванні формули посилання не зміниться.
Наприклад, якщо в записі формули посилання на комірку D7 записати у вигляді $ D7, то при переміщенні формули змінюватиметься лише номер рядка "7". Запис D $ 7 означає, що під час переміщення змінюватиметься тільки ім'я стовпчика "D". Якщо ж записати адресу у вигляді $ D $ 7, то посилання під час переміщення формули на цю адресу не зміниться й у розрахунках братимуть участь дані з комірки D7. Якщо у формулі зазначений інтервал комірок G3: L9, то керувати можна кожним із чотирьох символів: "G", "3", "L" та "9", поміщаючи перед ними символ "$".
Якщо на засланні використовуються символи "$", то вона називається абсолютною, якщо символів "$" на засланні немає - відносною. Адреси таких посилань називають абсолютними і відносними відповідно. Абсолютні адреси при переміщенні формул не змінюються, а у відносних адресах відбувається зсув на величину перенесення.
Для переміщення формули виконайте такі дії:
· Виділіть клітинку з формулою;
· Підведіть покажчик миші до того місця кордону осередки, де зображення покажчика миші змінюється з білого хрестика на білу стрілку;
· Натисніть ліву кнопку миші і, утримуючи її, переміщайте клітинку в потрібне місце таблиці. Для завершення переміщення формули відпустіть кнопку миші.
При переміщенні декількох формул, виділених у блок, покажчик миші потрібно підводити до кордону блоку.
Функції дати й часу
Представлення дати і часу має одну особливість. При введенні дати або часу Ви вводите послідовність символів, яка не є числом, але з цими символами можна робити обчислення: порівнювати, додавати, віднімати. Тому в Excel, поряд з текстовим поданням дати і часу, існує і числове подання.
За точку відліку за замовчуванням прийнята дата 1 січня 1900. Спосіб обчислення, якщо це необхідно, можна вказати в списку параметрів. Вибір способу обчислення визначається вибором використовуваного стандарту, кількістю днів у році і тривалістю місяці.
Наявність текстового і числового форматів представлення дати і часу практично не ускладнює роботу. У комірці з форматом Загальний результат буде представлений в потрібному вигляді без вашої участі, а якщо клітинки раніше були відформатовані для чисел, то результатом роботи функцій дати і часу буде число. У цьому випадку Вам доведеться самостійно подбати про введення потрібного формату (див. табл. 4.6).
Функція ВРЕМЗНАЧ перетворює час із текстового формату в числовий, а ЧАС - навпаки, за заданими годинах, хвилинах і секундах повертає час в числовому форматі.
Функція ХВИЛИНИ з числового або текстового формату дати і часу повертає лише хвилини. Якщо ввести одночасно і дату і час у формулу "= ХВИЛИНИ (" 10.07.95 18:30:15 ")", то повернуто буде тільки число хвилин. Для даної формули - "30". Аналогічно працюють функції СЕКУНДИ і ГОД. Вони повертають тільки секунди і годинник відповідно.
| Функции времени | Форматы представления | |
| Общий | ч:мм:сс | |
| =ВРЕМЗНАЧ("18:30:15") | 0,7710069 | нет |
| =ВРЕМЯ(18;30;15) | 6:30 РМ | 18:30:15 |
| =МИНУТЫ(0,771007) | нет | |
| =МИНУТЫ("18:30:15") | нет | |
| =СЕКУНДЫ(СЗ) | нет | |
| =СЕКУНДЫ(0,771007) | нет | |
| =ЧАС(ВЗ) | нет | |
| =ЧАС(0,771007) | нет |
| Функции даты | Форматы представления | |
| Общий | ч:мм:сс | |
| =ГОД("9.07.95") | нет | |
| =ГОД(В5) | нет | |
| =ДАТА(95;7;9) | 9.07.95 | 9 июля 1995 |
| =ДАТАЗНАЧ("9.07.95") | 9 июля 1995 | |
| =ДЕНЬ(Б5-9) | нет | |
| =ДЕНЬНЕД("9.07.95") | нет | |
| =МЕСЯЦф5) | нет | |
| =ТДАТА() | 9.07.95 18:48 | 9 июля 1995 |
Функція РІК повертає рік аргументу. Аналогічно працюють функції ДЕНЬ і МІСЯЦЬ. Функція ДАТА вимагає введення року, місяця і дня і повертає дату в числовому форматі, функція ДАТАЗНАЧ просто перетворює дату з текстового формату в числовий.
Функція ДЕНЬНЕД повертає номер дня тижня. Можна вибрати спосіб нумерації. Типово неділю вважається першим днем тижня.
Функція ТДАТУ повертає поточну дату і час в числовому форматі.
Математичні функції
У Excel є широкий вибір математичних функцій, що дозволяє виконувати дії з різних галузей математики: арифметики, алгебри, комбінаторики і т. д.
Статистичні функції
У Excel найбільш широко представлені функції, призначені для проведення різного роду статистичних обчислень: максимальне і мінімальне значення діапазону, значення ймовірностей, розподілу випадкових величин, середні, дисперсії, довірчі інтервали і т. д.
Функції для роботи з посиланнями і масивами
У Excel є ряд функцій для обробки посилань і масивів: обчислення номерів рядка або стовпця таблиці по імені посилання, визначення кількості стовпців (рядків) посилання або масиву, вибір значення за номером індексу і т. д.
Функції роботи з базою даних
Серед функцій Excel є розділ, присвячений обробці баз даних, що дозволяє виконувати наступні дії: пошук максимального і мінімального значень в діапазоні при виконанні певного критерію, підсумовування або перемножування чисел з діапазону, підрахунок кількості непорожніх осередків і т. д.
Текстові функції
У Excel можна створювати формули, які дозволяють виконувати різні дії з обробки текстової інформації: визначення кількості символів в рядку, виділення підрядка з рядка, перетворення тексту в числове значення, зміна регістру і т. д.
Логічні функції
Логічні функції є невід'ємним компонентом багатьох формул. Всякий раз, коли Вам необхідно виконати ті чи інші дії в залежності від виконання будь-яких умов, Ви використовуєте логічні функції.
У Excel Ви можете використовувати наступні логічні функції: ЯКЩО, І, АБО, ІСТИНА, БРЕХНЯ, НЕ. Результатом роботи логічних функцій І, АБО, ІСТИНА, БРЕХНЯ, НЕ є логічне значення ІСТИНА або БРЕХНЯ, а результатом роботи логічної функції ЯКЩО може бути число, текст або посилання на виконання яких-небудь дій.
Результатом роботи логічних функцій ІСТИНА () і БРЕХНЯ () є логічні значення ІСТИНА або БРЕХНЯ.
Функція НЕ (арг) змінює на протилежне логічне значення свого аргументу. Вона часто використовується в тих випадках, коли необхідно бути впевненим у тому, що значення не дорівнює деякій величині.
Аргумент функції арг - це значення або вираз, результатом обчислення якого є ІСТИНА або БРЕХНЯ. Якщо арг має значення FALSE, то функція НЕ повертає значення ІСТИНА, а якщо арг має значення ІСТИНА, то функція НЕ повертає значення БРЕХНЯ.
Функція І (арг1, АРГ2, ...) повертає значення ІСТИНА, якщо всі аргументи мають значення ІСТИНА, і повертає значення БРЕХНЯ, якщо хоча б один аргумент має значення FALSE.
Функція АБО (арг1, АРГ2, ...) повертає значення ІСТИНА, якщо хоча б один аргумент має значення ІСТИНА.
Аргументи aprl, АРГ2,. . . повинні бути логічними значеннями, масивами або посиланнями, які містять логічні значення. Якщо аргумент, який є посиланням або масивом, містить тексти або пусті клітинки, то такі значення ігноруються. Якщо зазначений інтервал не містить логічних значень, повертається значення помилки # VALUE!.
Функція ЕСЛИ (арг_лог, aprl, АРГ2) повертає значення aprl, якщо арг_лог при обчисленні набуває значення ІСТИНА, і АРГ2, якщо арг_лог при обчисленні набуває значення БРЕХНЯ. При конструюванні більш складних перевірок у якості значень аргументів aprl і АРГ2 можуть бути вкладені до семи функцій IF.
Охорона праці при роботі з ПК