Як робити розрахунки в екселі. Формули EXCEL з прикладами - Інструкція із застосування. Використання вкладених функцій

• знак «менше чи одно» – «<=»;

• знак «не одно» – «<>».

Оператор об'єднання тексту

Для цього використовується спеціальний символ «&» (амперсанд). За допомогою його можна поєднати різні фрагменти в одне ціле – той самий принцип, що й з функцією «ЗЧЕПИТИ». Наведемо кілька прикладів:

1. Якщо ви хочете об'єднати текст у комірках, потрібно використовувати наступний код.

1. Для того, щоб вставити між ними якийсь символ або букву, потрібно використовувати таку конструкцію.

1. Об'єднувати можна як комірки, а й звичайні символи.

Будь-який текст, крім посилань, необхідно вказувати у лапках. Інакше формула дасть помилку.

Зверніть увагу, що лапки використовують саме такі, як на скріншоті.

Для визначення посилань можна використовувати такі оператори:

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

Оскільки тільки вона підпадає під визначення «перетину множин». Саме таку назву має даний оператор (пробіл).

Використання посилань

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

Прості посилання A1

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

• стовпців - від A до XFD (не більше 16 384);
• рядків - від 1 до 1048576.

Наведемо кілька прикладів:

• осередок на перетині рядка 5 та стовпця B – «B5»;
• діапазон осередків у стовпці B починаючи з 5 по 25 рядок – «B5: B25»;
• діапазон осередків у рядку 5, починаючи зі стовпця B до F – «B5:F5»;
• усі осередки у рядку 10 – «10:10»;
• всі осередки у рядках з 10 по 15 – «10:15»;
• всі клітини в стовпці B - "B: B";
• всі клітини в стовпцях з B до K – «B:K»;
• діапазон осередків з B2 до F5 – «B2-F5».

Іноді у формулах використовується інформація з інших аркушів. Працює це в такий спосіб.

На другому аркуші наведено такі дані.

Якщо в назві листа є пробіл, то у формулі його потрібно вказувати в одинарних лапках (апострофи).

Абсолютні та відносні посилання

Редактор Ексель працює з трьома видами посилань:

• абсолютні;
• відносні;
• змішані.

Розглянемо їх уважніше.

Усі зазначені раніше приклади належать до відносної адреси осередків. Цей тип найпопулярніший. Головна практична перевага в тому, що редактор під час перенесення змінить посилання інше значення. Відповідно, куди саме ви скопіювали цю формулу. Для підрахунку враховуватиметься кількість клітин між старим і новим становищем.

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

1. Введемо формулу до розрахунку суми першої колонки.

1. Натисніть клавішу Ctrl + C . Щоб перенести формулу на сусідню клітину, необхідно перейти туди і натиснути на Ctrl +V .

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

1. Тепер подивіться нові формули. Зміна індексу стовпця відбулася автоматично.

Якщо ви хочете, щоб при перенесенні формул усі посилання зберігалися (тобто вони не змінювалися в автоматичному режимі), потрібно використовувати абсолютні адреси. Вони вказуються як «$B$2».

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

Цей тип адрес використовується тоді, коли необхідно зафіксувати тільки стовпець або рядок, а не одночасно. Використовувати можна такі конструкції:

• $D1, $F5, $G3 – для фіксації стовпців;
• D$1, F$5, G$3 – для фіксації рядків.

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

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

1. Як приклад використовуємо таке вираз.

1. Перенесемо цю формулу в іншу комірку. Бажано не на наступний і на іншому рядку. Тепер ви бачимо, що новий вираз містить той самий рядок (4), але іншу літеру, оскільки тільки він був відносною.

Тривимірні посилання

Під поняття «тривимірні» потрапляють адреси, у яких вказується діапазон аркушів. Приклад формули виглядає так.

У цьому випадку результат буде відповідати сумі всіх осередків «A5» на всіх аркушах, починаючи з 1 по 4. При складанні таких виразів необхідно дотримуватись наступних умов:

• у масивах не можна використовувати подібні посилання;
• тривимірні вирази забороняється використовувати там, де є перетин осередків (наприклад, оператор «пробіл»);
• при створенні формул з тривимірними адресами можна використовувати наступні функції: СРЗНАЧ, СТАНДОТКЛОНА, СТАНДОТКЛОН.В, СРЗНАЧА, СТАНДОТКЛОНПА, СТАНДОТКЛОН.Г, СУМ, РАХУНОК, РАХУНОК, МІН, МАКС, МІНА, МІН, МІНА, МІН, МІНА, МІНА, МІНА, МІНА, МІНА У та ДИСП.

Якщо порушити ці правила, то ви побачите якусь помилку.

Посилання формату R1C1

Цей тип посилань від «A1» відрізняється тим, що номер задається не тільки рядкам, а й стовпцям. Розробники вирішили замінити звичайний вид на цей варіант для зручності в макросах, але їх можна використовувати будь-де. Наведемо кілька прикладів таких адрес:

• R10C10 - абсолютне посилання на клітину, яка розташована на десятому рядку десятого стовпця;
• R - Абсолютне посилання на поточну (в якій вказується формула) посилання;
• R[-2] - відносне посилання на рядок, яка розташована на дві позиції вище цієї;
• R[-3]C - відносне посилання на клітину, яка розташована на три позиції вище в поточному стовпці (де ви вирішили прописати формулу);
• RC - відносне посилання на клітину, яка розташована на п'ять клітин правіше і п'ять рядків нижче поточної.

Використання імен

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

Імена ви можете використовувати для множення, поділу, додавання, віднімання, розрахунку відсотків, коефіцієнтів, відхилення, округлення, ПДВ, іпотеки, кредиту, кошторису, табелів, різних бланків, знижки, зарплати, стажу, ануїтетного платежу, роботи з формулами «ВПР» , «ВСД», «ПРОМІЖНІ.ПІДСУМКИ» і так далі. Тобто можете робити будь-що.

Головною умовою можна назвати лише одну – ви повинні заздалегідь визначити це ім'я. Інакше Ексель про нього нічого не знатиме. Робиться це так.

1. Виділіть якийсь стовпець.
2. Викличте контекстне меню.
3. Виберіть пункт «Призначити ім'я».

1. Вкажіть бажане ім'я цього об'єкта. При цьому слід дотримуватися наступних правил.

1. Щоб зберегти, натисніть кнопку «OK».

Так само можна присвоїти ім'я якомусь осередку, тексту чи числу.

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

А якщо спробувати замість адреси D4:D9 вставити наше ім'я, то ви побачите підказку. Достатньо написати кілька знаків, і ви побачите, що найбільше підходить (з бази імен).

У нашому випадку все просто – «стовпець_3». А уявіть, що у вас таких імен буде безліч. Усі напам'ять ви запам'ятати не зможете.

Використання функцій

У редакторі Excel вставити функцію можна кількома способами:

• вручну;
• за допомогою панелі інструментів;
• за допомогою вікна "Вставка функції".

Розглянемо кожен метод уважніше.

У цьому випадку все просто – ви за допомогою рук, власних знань та умінь вводите формули у спеціальному рядку або прямо в комірці.

Якщо ж у вас немає робочого досвіду в цій галузі, то краще спочатку використовувати полегшені методи.

В цьому випадку необхідно:

1. Перейти на вкладку Формули.
2. Клікнути на якусь бібліотеку.
3. Вибрати потрібну функцію.

1. Відразу після цього з'явиться вікно «Аргументи та функції» із вже обраною функцією. Вам залишається лише проставити аргументи та зберегти формулу за допомогою кнопки «OK».

Майстер підстановки

Застосувати його можна так:

1. Зробіть активним будь-яку комірку.
2. Натисніть на іконку «Fx» або натисніть клавішу SHIFT +F3 .

1. Відразу після цього з'явиться вікно «Вставка функції».
2. Тут ви побачите великий список різних функцій, відсортованих за категоріями. Крім того, можна скористатися пошуком, якщо ви не можете знайти потрібний пункт.

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

1. Виберіть якусь функцію із запропонованого списку.
2. Щоб продовжити, потрібно натиснути кнопку «OK».

1. Потім вас попросять вказати «Аргументи та функції». Зробити це можна вручну або легко виділити необхідний діапазон осередків.
2. Щоб застосувати всі налаштування, потрібно натиснути на кнопку «OK».

1. В результаті цього ми побачимо цифру 6, хоча це було так зрозуміло, оскільки у вікні «Аргументи і функції» виводиться попередній результат. Дані перераховуються моментально за зміни будь-якого з аргументів.

Використання вкладених функцій

Як приклад використовуватимемо формули з логічними умовами. Для цього нам потрібно буде додати якусь таблицю.

Потім дотримуйтесь наступної інструкції:

1. Клацніть на перший осередок. Викличте вікно «Вставка функції». Виберіть «Якщо». Натисніть «OK», щоб вставити.

1. Потім потрібно буде скласти якесь логічне вираження. Його необхідно записати у перше поле. Наприклад, можна скласти значення трьох осередків в одному рядку і перевірити, чи буде сума більша за 10. У разі «істини» вказуємо текст «Більше 10». Для хибного результату – «Менше 10». Потім для повернення до робочого простору натискаємо на «OK».

1. У результаті ми бачимо наступне – редактор видав, що сума осередків у третьому рядку менша за 10. І це правильно. Виходить, наш код працює.

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

1. У результаті редактор перераховує наш вираз для кожного рядка.

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

Зробити це можна кількома способами: використовувати рядок формул або спеціальний майстер. У першому випадку все просто – клацаєте у спеціальне поле та вручну вводите потрібні зміни. Але писати там не зовсім зручно.

Єдине, що ви можете зробити, це збільшити поле для введення. Для цього достатньо клікнути на вказану іконку або натиснути клавіші Ctrl + Shift + U .

Це єдиний спосіб, якщо ви не використовуєте у формулі функції.

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

1. Зробіть активною клітину із формулою. Натисніть на іконку Fx.

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

1. Щоб зберегти внесені зміни, потрібно використовувати кнопку «OK».

Для того, щоб видалити якийсь вираз, достатньо зробити таке:

1. Клацніть на будь-яку комірку.

1. Натисніть кнопку Delete або Backspace. Внаслідок цього клітина виявиться порожньою.

Домогтися такого самого результату можна і за допомогою інструмента «Очистити все».

Можливі помилки при складанні формул у редакторі Excel

Нижче наведено найпопулярніші помилки, які допускаються користувачами:

• у виразі використовується величезна кількість вкладеностей. Їх має бути не більше 64;
• у формулах зазначаються шляхи до зовнішніх книг без повного шляху;
• неправильно розставлені дужки, що відкриваються і закриваються. Саме тому у редакторі у рядку формул усі дужки підсвічуються іншим кольором;

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

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

• неправильно вказуються діапазони осередків. Для цього необхідно використовувати оператор «:» (двокрапка).

Коди помилок під час роботи з формулами

При роботі з формулою ви можете побачити такі варіанти помилок:

• #ЗНАЧ! - Ця помилка показує, що ви використовуєте неправильний тип даних. Наприклад, замість числового значення намагаєтеся використати текст. Зрозуміло, Ексель зможе обчислити суму між двома фразами;
• #ІМ'Я? – подібна помилка означає, що ви припустилися помилки у написанні назви функції. Або ж намагаєтеся запровадити щось неіснуюче. Так робити не можна. Крім цього проблема може бути і в іншому. Якщо ви впевнені в імені функції, спробуйте подивитися на формулу уважніше. Можливо, ви забули якусь дужку. Крім цього, слід враховувати, що текстові фрагменти вказуються в лапках. Якщо нічого не допомагає, спробуйте скласти вираз наново;
• #КІЛЬКІСТЬ! – відображення подібного повідомлення означає, що у вас якась проблема з аргументами чи результатом виконання формули. Наприклад, число вийшло надто величезним або навпаки – маленьким;
• #ДІЛ/0! - Ця помилка означає, що ви намагаєтеся написати вираз, в якому відбувається розподіл на нуль. Excel не може скасувати правила математики. Тому такі дії також заборонені;
• #Н/Д! – редактор може показати це повідомлення, якщо якесь значення недоступне. Наприклад, якщо ви використовуєте функції ПОШУК, ПОШУК, ПОШУКПОЗ, та Excel не знайшов шуканий фрагмент. Або даних взагалі немає і формулі немає з чим працювати;
• Якщо ви намагаєтеся щось порахувати, і Excel пише слово #ПОСИЛКА!, значить, в аргументі функції використовується неправильний діапазон осередків;
• #ПУСТО! – ця помилка з'являється в тому випадку, якщо у вас використовується формула, що не узгоджується, з діапазонами, що перетинаються. Точніше – якщо насправді подібні осередки відсутні (які перебувають на перетині двох діапазонів). Часто така помилка виникає випадково. Достатньо залишити одну прогалину в аргументі, і редактор сприйме її як спеціальний оператор (про нього ми розповідали раніше).

При редагуванні формули (комірки підсвічуються) ви побачите, що вони насправді не перетинаються.

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

Для того щоб побачити значення, що міститься там, достатньо змінити розмір стовпця.

Крім цього, можна використовувати форматування осередків. Для цього необхідно виконати декілька простих кроків:

1. Викличте контекстне меню. Виберіть «Формат комірок».

1. Вкажіть тип "Загальний". Для продовження натисніть кнопку «OK».

Завдяки цьому редактор Ексель зможе перевести це число в інший формат, який міститься в цьому стовпці.

Приклади використання формул

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

Щоб оцінити математичні можливості Экселя, потрібно виконати такі дії.

1. Створіть таблицю з умовними даними.

1. Щоб вирахувати суму, введіть наступну формулу. Якщо хочете додати лише одне значення, можна використовувати оператор додавання («+»).

1. Як не дивно, у редакторі Excel не можна відібрати за допомогою функцій. Для відрахування використовується стандартний оператор «-». І тут код вийде наступний.

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

Зверніть увагу, що символ відсотка ставиться наприкінці, а чи не на початку. Крім цього, під час роботи з відсотками не потрібно додатково множити на 100. Це відбувається автоматично.

1. Excel може складати з урахуванням відразу кількох умов. Можна порахувати суму клітин першого стовпця, значення яких більше 2 і менше 6. І ту саму формулу можна встановити для другої колонки.

=СУМІСЛІМН(B3:B9;B3:B9;">2";B3:B9;"<6") =СУМІСЛІМН(C3:C9;C3:C9;">2";C3:C9;"<6")

1. Також можна порахувати кількість елементів, які задовольняють якусь умову. Наприклад, нехай Ексель порахує, скільки в нас чисел більше ніж 3.

=РАХУНОКИ(B3:B9;">3") =РАХУНКИ(C3:C9;">3")

1. Результат усіх формул буде наступним.

Математичні функції та графіки

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

Як приклад спробуємо побудувати графіки для експоненти та якогось рівняння. Інструкція буде наступною:

1. Створимо таблицю. У першій графі ми матимемо вихідне число «X», у другій – функція «EXP», у третій – зазначене співвідношення. Можна було б зробити квадратичний вираз, але тоді результуюче значення на тлі експоненти на графіці практично зникло б.

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

Подібним чином можна уявити графічно будь-яку функцію чи математичне вираз.

Все описане вище підходить для сучасних програм 2007, 2010, 2013 та 2016 років. Старий редактор Ексель значно поступається у плані можливостей, кількості функцій та інструментів. Якщо відкриєте офіційну довідку від Microsoft, то побачите, що вони додатково вказують, у якій версії програми з'явилася ця функція.

У всьому іншому все виглядає практично так само. Як приклад, порахуємо суму кількох осередків. Для цього необхідно:

1. Вказати якісь дані для обчислення. Клацніть на будь-яку клітинку. Натисніть на іконку Fx.

1. Вибираємо категорію "Математичні". Знаходимо функцію «СУМ» та натискаємо на «OK».
1. Можете спробувати перерахувати у будь-якому іншому редакторі. Процес відбуватиметься так само.

Висновок

У цьому самовчителі ми розповіли про все, що пов'язано з формулами в редакторі Excel, – від найпростішого до дуже складного. Кожен розділ супроводжувався докладними прикладами та поясненнями. Це зроблено для того, щоб інформація була доступна навіть для повних чайників.

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

Крім цього важливо пам'ятати, що формули повинні починатися з символу «=» (рівно). Багато користувачів-початківців забувають про це.

Файл прикладів

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

Відеоінструкція

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

В електронні таблиці заносять вихідні дані та формули для розрахунку результуючих величин. Будь-яка зміна вихідних даних викликає автоматичну зміну результатів. Електронні таблиці використовуються для виконання математичних, фінансових, статистичних та інженерних розрахунків. Наприклад: розрахунок заробітної плати, розрахунок квартирної плати та ін.

Основні переваги MS Excel:

1. Подібно до інших електронних таблиць, на екрані Excel з'являється сітка з рядків і стовпців - електронна книга обліку, в яку вводяться дані. Кожна клітинка сітки (перетин рядка та стовпця) називається осередком. Зазвичай до осередків заносяться числа, але туди можна помістити і текст з описом, наприклад, заголовки рядків і стовпців.

2. Excel може виконувати обчислення та виводити результати. У будь-якому осередку можуть зберігатися результати обчислень, виконаних над вмістом будь-яких інших осередків таблиці.

3. Вміст клітинок легко змінити. І найкраще те, що при зміні числа, яке використовується в обчисленнях десь в іншому місці таблиці, автоматично зміниться результат цих обчислень.

4. Дані можна розмістити так, щоб їх легко читати як на екрані, так і на друку. Можна розмістити заголовки по центру, виділити текст жирним шрифтом та курсивом, обвести важливі осередки жирною рамкою та багато іншого. Але дивіться, не перестарайтеся! Занадто велика кількість елементів форматування можуть швидше утруднити, ніж полегшити читання таблиці.

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

Основу структури листа Excel складають стовпці, які розташовуються зверху вниз, та, рядки, що проходять зліва направо. Стовпці позначаються літерами, а рядки – цифрами. Заголовки стовпців - це букви A, B, C..., а заголовки рядків - цифри 1, 2, 3 і т.д. На перетині кожного стовпця та кожного рядка розташовується невеликий прямокутник, званий комірка. Кожен осередок має свою адресу, яка визначається відповідними стовпцем та рядком. Наприклад, на перетині стовпця З рядком 3 розташовується осередок С3.

Можна працювати з одним осередком, а можна виділити кілька осередків та працювати з ними одночасно. Сукупність кількох виділених осередків називається діапазоном.

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

Робочі книги- це файли MS Excel, які можуть містити один або кілька робочих аркушів.

Основні елементи вікна Excel (рис. 9.1):

1. Поле назви головного вікна.
2. Рядок меню.
3. Кнопки керування
4. Панелі інструментів.
5. Поле імені.
6. Рядок формули.
7. Рядок стану
8. Поле назви вікна робочої книжки (дочірнє вікно Excel).
9. Активний аркуш робочої книги.
10. Кнопка виділення всього аркуша.
11. Активний (поточний) осередок.
12. Ім'я стовпця.
13. Ім'я рядка.

Мал. 9.1 Елементи робочого листа

Осередок - це основний елемент електронної таблиці лише у ній може бути якась інформація. У осередку таблиці може бути:

• текст;
• число;
• дата;
• формула.

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

Структура осередку Excel:

• 1-й рівень містить видиме на екрані зображення (тобто відформатований текст) чи результат обчислення формули).
• 2-й рівень містить формати комірки (формат чисел, шрифти, вимикач (вмикач) ознака показувати чи ні комірку, вид рамки, захист комірки).
• 3-й рівень містить формулу, яка може складатися з тексту, числа чи вбудованих функцій.
• 4-й рівень містить ім'я осередку, це ім'я може використовуватися у формулах інших осередків, при цьому забезпечується абсолютна адресація цього осередку.
• 5-й рівень містить примітки цієї комірки (довільний текст). Якщо осередок містить примітку, то у верхньому правому кутку з'являється червоний квадратик (точка)

9.2. Рівні осередку

Блок осередків - група осередками.

Рядок введення – смуга під панелями інструментів Microsoft Excel, використовується для введення або редагування значень чи формул у комірках чи діаграмах. Тут виводиться постійне значення або формула активного осередку. Для введення даних виділіть комірку, введіть дані та клацніть по кнопці із зеленою галочкою або натисніть ENTER. Дані з'являються у рядку формул у міру їхнього набору.

Поле імені знаходиться на лівому кінці рядка введення. Використовуйте поле імені для визначення імен активному осередку або виділеному блоку. Для цього клацніть поле імені, введіть туди ім'я і натисніть ENTER. Такі імена можна використовувати при написанні формул або при побудові діаграм. Також поле імені можна використовувати для переходу до займенованого осередку або блоку. Для цього відкрийте список і виберіть потрібне ім'я.

Дані Excel виводяться на екран у певному форматі. За промовчанням інформація виводиться у форматі Загальний. Можна змінити формат подання інформації у виділених осередках (Формат | Осередки).

Обчислення в Excel. Формули та функції

Формулою в Excel називається послідовність символів, що починається із знаку рівності “=“. До цієї послідовності символів можуть входити постійні значення, посилання на комірки, імена, функції чи оператори. Результатом роботи формули є нове значення, яке виводиться як результат обчислення формули за наявними даними.

Якщо значення у комірках, на які є посилання у формулах, змінюються, то результат зміниться автоматично.

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

Використання імен забезпечує наступні переваги:

• Формули, що використовують імена, легше сприймаються та запам'ятовуються, ніж формули, що використовують посилання на комірки.
• Наприклад, формула = Активи-Пасиви набагато зрозуміліше, ніж формула = F6-D6.
• При зміні структури робочого аркуша достатньо оновити посилання лише одному місці - у визначенні імен, і всі формули, які використовують ці імена, будуть використовувати коректні посилання.
• Після того, як ім'я визначено, воно може використовуватись у будь-якому місці робочої книги. Доступ до всіх імен із будь-якого робочого листа можна отримати за допомогою вікна імені у лівій частині рядка формул.
• Ви також можете визначити спеціальні імена, діапазон дії яких обмежується поточним робочим листом. Це означає, що ці імена можна використовувати лише на тому робочому аркуші, де вони визначені. Такі імена не відображаються у вікні імені рядка формул або діалоговому вікні “Присвоїти ім'я”, якщо активний інший робочий лист книги.
• Excel автоматично створює імена на основі заголовків рядків та стовпців робочого листа. Докладну інформацію про створення таких імен міститься у розділі “Бази даних”.
• Після того, як ім'я визначено, ви можете:
• Замінити всі відповідні посилання на це ім'я у всіх місцях робочого листа.
• Швидко перейти на назване посилання, замінити посилання, вставити посилання в формулу за допомогою вікна імені в рядку формул.

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

При копіюванні формул виникає необхідність керувати зміною адрес осередків або посилань. Для цього перед символами адреси осередку або посилання встановлюються символи $. Змінюватися лише атрибути адреси осередку, перед якими не стоїть символ “$”. Якщо перед усіма атрибутами адреси комірки поставити символ “$”, то копіювання формули посилання не зміниться.

Абсолютні адреси при переміщенні формул не змінюються, а відносних адресах відбувається зміщення на величину переносу.

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

Щоб використовувати функцію, потрібно ввести її як частину формули в комірку робочого листа. Послідовність, в якій повинні розташовуватися символи, що використовуються у формулі, називається синтаксисом функції. Усі функції використовують однакові основні правила синтаксису. Якщо ви порушите правила синтаксису, Excel видасть повідомлення про те, що у формулі є помилка.

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

Аргументи функції записуються в круглих дужках відразу за назвою функції та відокремлюються один від одного символом крапка з комою “;”. Дужки дозволяють Excel визначити, де починається і закінчується список аргументів. Усередині дужок повинні бути аргументи. Пам'ятайте про те, що при записі функції повинні бути відкриті та закриваючі дужки, при цьому не слід вставляти прогалини між назвою функції і дужками.

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

Для зручності роботи функції в Excel розбиті за категоріями: функції управління базами даних та списками, функції дати та часу, DDE/Зовнішні функції, інженерні функції, фінансові, інформаційні, логічні, функції перегляду та посилань. Крім того, присутні такі категорії функцій: статистичні, текстові та математичні.

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

За допомогою функцій дати та часу можна вирішити практично будь-які завдання, пов'язані з урахуванням дати або часу (наприклад, визначити вік, обчислити стаж роботи, визначити кількість робочих днів на будь-якому проміжку часу).

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

Excel широко представлені математичні функції. Наприклад, можна виконувати різні операції з матрицями: множити, знаходити зворотну, транспонувати.

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

В Excel можна вирішувати завдання оптимізації та використовувати аналіз Фур'є. Зокрема, Excel реалізований алгоритм швидкого перетворення Фур'є, за допомогою якого ви можете побудувати амплітудний і фазовий спектр.

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

Майстер функцій викликається командою "Вставка" | Функції або натисканням на кнопку Майстер функцій

Помилки у формулах

Якщо формула неправильна оброблена, Microsoft Excel відображає помилку. Причини виникнення помилок можуть бути різними:

• ##### - результат обробки формули не вміщується в комірці або результатом виконання формули, що оперує датами та часом, є негативне число.
• #ЗНАЧ! - використовується неприпустимий тип аргументу чи операнда.
• #СПРАВ/0! - У формулі робиться спроба поділу на нуль.
• #ІМ'Я? - Excel не може розпізнати ім'я, яке використовується у формулі.
• #Н/Д - невизначені дані (найчастіше зустрічається, якщо некоректно визначено аргументи функції).
• #ПОСИЛАННЯ! - використовується неприпустиме посилання на комірку (наприклад, комірки, на які посилається формула, були видалені).
• #КІЛЬКІСТЬ! - числове значення, що повертається, занадто велике або занадто мало, щоб його можна було представити в Microsoft Excel (діапазон відображуваних чисел від -10307 до 10307).
• #ПУСТО! - задано перетин двох областей, які насправді не мають спільних осередків.

Помилки можуть виникати не тільки через неправильну обробку формули, помилка може міститися в комірці, на яку посилається формула.

Опис різних функцій Excel

Серії

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

Щоб побудувати довільні серії, виконайте команду Правка | Заповнити | Прогресія.

Типи прогресії:

• Арифметична
• Геометрична
• Дати
• автозаповнення

Крім прогресії серії можна створювати ще кількома способами без допомоги меню, вони наведені нижче.

Перший спосіб

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

Такий простий процес введення можливий лише тоді, коли в комірку введено один із елементів наявного списку. Списки створюються у діалоговому вікні Файл | Установки на закладці Списки.

Другий спосіб

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

Цей спосіб є найзручнішим створення простих серій (типу арифметичної прогресії).

Третій спосіб

Третій спосіб є найуніверсальнішим для створення серій будь-якого цифрового типу арифметичної та геометричної прогресії, статечні ряди та інші складні серії.

Для цього в першому осередку серії вводиться її початкове значення, у другому осередку серії вводиться формула, що визначає дану серію і натискається ENTER. Потім, скориставшись правим-нижнім кутом виділення, продовжити виділення до потрібної області, аналогічно першому та другому способу.

Як говорилося вище третій спосіб є універсальним.

Діаграми та графіки

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

Мал. 9.3

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

Лінія на графіці чи група стовпчиків гістограми зображують ряд даних - вміст кількох сусідніх осередків таблиці. На одній діаграмі можна надати кілька рядів даних.

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

Подання даних у графічному вигляді дозволяє вирішувати найрізноманітніші завдання. Основна перевага такого уявлення – наочність. На графіках легко проглядається тенденція зміни. Можна навіть визначити швидкість зміни тенденції. Різні співвідношення, приріст, взаємозв'язок різних процесів - це легко можна побачити на графіках.

Мал. 9.4

Існує три види діаграм:

• звичайна;
• із накопиченням;
• нормована.

Щоб побудувати діаграму, виконайте команду Вставка | Діаграма або натисніть кнопку Майстер діаграм

Для редагування діаграми:

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

Форматування діаграми

Під форматуванням діаграми розуміється процес зміни зовнішнього вигляду діаграми. У процесі форматування можна, наприклад, змінити шрифт, яким виведено заголовок діаграми, колір елементів діаграми (стовпчиків діаграми, ліній графіка), вид ліній сітки тощо. На діаграмі можна виділити наступні елементи (рис. 9.5):

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

Мал. 9.5

Форматування діаграми виконується шляхом форматування окремих елементів.

Основні формати

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

Загальний формат

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

Числовий формат

Числовий формат є найбільш універсальним (рис. 9.6). - На відміну від загального формату, у числовому форматі можна задати кількість цифр дробової частини (число десяткових знаків), що відображаються. Під час відображення дробових чисел виконується округлення відповідно до заданої кількості цифр дробової частини. Наприклад, якщо встановлено формат з відображенням двох чисел дробової частини, число 567,897 буде представлено як 567,90. Це число буде відображено як 568, якщо задати формат без відображення цифр дробової частини (установити значення лічильника Число десяткових знаків дорівнює нулю).

Мал. 9.6 Вибрати формат Числовий, можна налаштувати його характеристики

Грошовий формат

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

Дробові числа, що відображаються у грошовому форматі, округляються відповідно до заданої кількості цифр дробової частини. Округлення виконується за відомим правилом: якщо значення розряду, який має бути відкинутий, менше п'яти, то він відкидається, інакше значення попереднього розряду збільшується на одиницю. Якщо мають бути відкинуті кілька розрядів, то наведене правило послідовно застосовується до всіх розрядів, які мають бути відкинуті, починаючи з наймолодшого.

Фінансовий формат

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

Дата

Якщо в комірці таблиці знаходиться дата, можна змінити формат її відображення (рис. 9.7).

Мал. 9.7 Спосіб відображення дати потрібно вибрати у списку Тип

Розрізняють повний та скорочений формати подання дати. Повний формат відображає день, місяць та рік. У скороченому форматі може бути представлений день і місяць, іноді лише місяць або день. Ще раз слід звернути увагу на символ-розділювач, який використовується для запису дат. У Росії – це точка.

Відсотки

При відображенні вмісту комірки у відсотковому форматі реальне значення комірки множиться на 100 і після числа виводиться знак відсотка. Наприклад, якщо в комірку записати число 0,2 і встановити формат Відсотковий, то в цьому комірці буде відображено 20,00 %.

Графіка

На поверхню аркуша робочої книжки можна помістити ілюстрацію. Це може бути, наприклад, логотип фірми на прайс-листі чи бланку. Ілюстрацію можна підготувати заздалегідь за допомогою різних графічних редакторів, наприклад Microsoft Paint, або намалювати безпосередньо в Excel.

Обробка даних

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

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

Сортування

Для зручності роботи інформація в таблицях зазвичай упорядкована відповідно до деякого критерію (відсортована). Наприклад, список співробітників зазвичай упорядкований за абеткою.

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

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

Мал. 9.8 Рядки таблиці відсортовані за вмістом стовпця Прізвище

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

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

Щоб виконати сортування рядків, потрібно виділити ці рядки та клацнути на одній з кнопок Сортування за зростанням або Сортування за спаданням.

Мал. 9.9 Кнопка для сортування

Excel дозволяє виконати сортування рядків за вмістом будь-яких кількох (але не більше трьох) стовпців. У загальному випадку, для того, щоб виконати сортування рядків таблиці, потрібно виділити ці рядки та в меню Дані вибрати команду Сортування.

Фільтри

Часто користувача цікавить вся інформація, що у базі даних, а конкретна вибірка. Наприклад, інформація про витрати у січні, список студентів групи 221/2 тощо.

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

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

Зведена таблиця

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

Зведена таблиця складається з:

Поле сторінки- це поле Вашого вихідного списку, за яким слід підбивати підсумки. Це поле представлене у вигляді списку, що розкривається, елементами якого є значення відповідної колонки у вихідному списку. При виборі певного значення зі списку зведена таблиця перераховується для відображення підсумків, пов'язаних з цим значенням.

Поле рядка- це поле вихідного списку, вміщене в область малої орієнтації зведеної таблиці. Значення поля рядка є значення відповідного поля у вихідному списку.

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

Поле даних- Це поле вихідного списку, значення якого є джерелом даних для обчислень у зведеній таблиці. Якщо поле даних одне, воно знаходиться в області виділення таблиці. Якщо таких полів кілька, їм відводиться окрема колонка, що називається Дані.

Область даних- Це частина зведеної таблиці, що містить підсумкові дані. У осередках області даних відображаються підсумки поля даних для елементів полів рядка і стовпця, відповідних значенням поля сторінки. Усі значення, що відображаються, відповідають вихідним даним.

Для побудови зведених таблиць передбачено спеціальний інструмент - Майстер зведених таблиць (Дані - зведена таблиця).

Зведена таблиця будується за три кроки:

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

2. На другому кроці визначається саме джерело даних. Тут необхідно вказати безперервний діапазон, де міститься вихідний список.

3. На заключному етапі роботи майстер з'ясовує місцезнаходження зведеної таблиці.

В результаті додаються лист із заготівлею зведеної таблиці та вікно Список полів зведеної таблиці, в якому перераховані назви полів вихідної таблиці (рис. 9.10). області рядків, області стовпців і які дані вихідної таблиці є елементами даних зведеної таблиці.

Мал. 9.10 Меню вкладки Зведена Таблиця

Після перетягування всіх перелічених полів буде сформовано зведену таблицю.

Запитання для оцінки рівня знань

1. Що таке електронна таблиця?
2. Основні переваги Excel?
3. Що є основою структури аркуша Excel?
4. Що таке робочий лист і робоча книга?
5. Чи перерахуєте основні елементи вікна Excel?
6. Що таке осередок?
7. Структура осередку?
8. Що таке формула у Excel?
9. Що означає символ $ у посиланнях?
10. Як Excel реагує на помилки?
11. Що означає функція ЯКЩО?
12. Навіщо служать діаграми?
13. Що таке низка даних?
14. Назвіть основні види діаграм, які використовуються в Excel?
15. ІІІ. Методика вимірювань та розрахункові формули. I. Мета роботи: Ознайомитися з деформаціями зсуву, крутіння та методами визначення модуля зсуву на основі деформації крутіння
16. ІІІ. Методика вимірювань та розрахункові формули. I.Мета роботи: перевірка досвідченим шляхом основного закону динаміки обертального руху за допомогою маятника Обербека та визначення моменту інерції
17. ІІІ. Методика вимірювань та розрахункові формули. Вантажі підвішені на нитки, перекинутій через шків, при цьому на кожен із вантажів дві сили: сила тяжіння та сила натягу нитки
18. ІІІ. Основні повноваження та функції органів управління, підрозділів

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

Використання посилань на комірки дозволяє перераховувати результат за формулами, коли відбуваються зміни вмісту осередків, включених до формул.

У формулах Excel починаються зі знака =. Дужки () можуть використовуватися для визначення порядку математичних операцій.

Excel підтримує такі оператори:

• Арифметичні операції:
  • додавання (+);
  • множення (*);
  • знаходження відсотка (%);
  • віднімання (-);
  • розподіл (/);
  • експонента (^).
• Оператори порівняння:
  • = одно;
  • < меньше;
  • > більше;
  • <= меньше или равно;
  • >= більше чи одно;
  • <>не дорівнює.
• Оператори зв'язку:
  • : діапазон;
  • ; об'єднання;
  • & оператор з'єднання текстів.

Таблиця 22. Приклади формул

Вправа

Вставка формули -25-А1+АЗ

Попередньо введіть будь-які числа в комірки А1 та A3.

1. Виберіть потрібну комірку, наприклад, В1.
2. Почніть уведення формули зі знака=.
3. Введіть число 25, а потім оператор (знак -).
4. Введіть посилання на перший операнд, наприклад, клацанням миші на потрібну комірку А1.
5. Введіть наступний оператор (знак +).
6. Клацніть мишею в тій комірці, яка є другим операндом у формулі.
7. Завершіть введення формули натисканням клавіші Enter. У осередку В1 отримаєте результат.

Автосумування

Кнопка Автосума (AutoSum)- ∑ може використовуватися для автоматичного створення формули, яка підсумовує область сусідніх осередків, що знаходяться безпосередньо злівау цьому рядку і безпосередньо вищеу цьому стовпці.

1. Виберіть комірку, в яку треба помістити результат підсумовування.
2. Клацніть Автосума - ∑ або натисніть комбінацію клавіш Alt+=. Excel прийме рішення, яку область включити в діапазон підсумовування, і виділить її пунктирною рамкою, що рухається, званою кордоном.
3. Натисніть Enterдля прийняття області, яку вибрала програма Excel, або виберіть нову область за допомогою миші, а потім натисніть Enter.

Функція "Автосума" автоматично трансформується у разі додавання та видалення осередків усередині області.

Вправа

Створення таблиці та розрахунок за формулами

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

Таблиця 23. Вихідна таблиця даних

1. Виберіть комірку В7, в якій буде обчислено суму по вертикалі.
2. Натисніть кнопку Автосума - ∑ або натисніть Alt+=.
3. Повторіть дії пунктів 2 та 3 для осередків С7 та D7.

Обчисліть кількість працівників без вищої освіти (за формулою В7-ВЗ).

1. Виберіть комірку В8 та наберіть знак (=).
2. Клацніть мишею в осередку В7, який є першим операндом у формулі.
3. Введіть з клавіатури знак (-) і клацніть мишею в осередку ВЗ, який є другим операндом у формулі (буде введена формула).
4. Натисніть Enter(У комірці В8 буде обчислено результат).
5. Повторіть пункти 5-8 для обчислень за відповідними формулами у комірках С8 та 08.
6. Збережіть файл з ім'ям Освіта_працівників.х1s.

Таблиця 24Результат розрахунку

Тиражування формул за допомогою маркера заповнення

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

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

1. Виберіть комірку, яка містить формулу для тиражування.
2. Перетягніть маркер заповненняу потрібному напрямку. Формула буде розмножена у всіх осередках.

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

Вправа

Тиражування формул

1.Відкрийте файл Освіта_працівників.х1s.

1. Введіть у комірку ЕС формулу для автосумування осередків = СУМ (ВЗ: 03).
2. Скопіюйте, перетягнувши маркер заповнення, формулу в комірки Е4: Е8.
3. Перегляньте як змінюються відносні адреси осередків в отриманих формулах (табл. 25) і збережіть файл.

Таблиця 25. Зміна адрес осередків при тиражуванні формул

Відносні та абсолютні посилання

Формули, реалізують обчислення таблицях, для адресації осередків використовують звані посилання. Посилання на комірку може бути відносноючи абсолютною.

Використання відносних посилань аналогічне зазначенню напрямку руху вулицею - "йти три квартали на північ, потім два квартали на захід". Дотримання цих інструкцій з різних початкових місць буде приводити до різних місць призначення.

Наприклад, формула, яка підсумовує числа у стовпці або рядку, потім часто копіюється для інших номерів рядків або стовпців. У таких формулах використовуються відносні посилання (див. попередній приклад у табл. 25).

Абсолютне посилання на осередок. Йди область осередків завжди буде посилатися на ту саму адресу рядка і стовпця. При порівнянні з напрямками вулиць це буде приблизно таке: "Ідіть на перетин Арбата і Бульварного кільця". Незалежно від місця старту це буде призводити до одного місця. Якщо формула вимагає, щоб адреса комірки залишалася незмінною під час копіювання, то має використовуватись абсолютне посилання (формат запису $А$1). Наприклад, коли формула обчислює частки від загальної суми, посилання на комірку, що містить загальну суму, не повинно змінюватись при копіюванні.

Знак долара ($) з'явиться як перед посиланням на стовпець, так і перед посиланням на рядок (наприклад, $С$2). змішані посилання).

1. Створіть таблицю, аналогічну наведеній нижче.

Таблиця 26. Розрахунок зарплати

1. У комірку СЗ введіть формулу для розрахунку зарплати Іванова = В1 * ВЗ.

При тиражуванні формули даного прикладу з відносними посиланнями в комірці С4 з'являється повідомлення про помилку (#ЗНАЧ!), так як зміниться відносна адреса комірки В1 і в комірку С4 скопіюється формула = В2 * В4;

1. Задайте абсолютне посилання на комірку В1, поставивши курсор у рядку формул на В1 і натиснувши клавішу F4, Формула в комірці СЗ матиме вигляд = $ $1 * ВЗ.
2. Скопіюйте формулу в комірки С4 та С5.
3. Збережіть файл (табл. 27) під назвою Зарплата.xls.

Таблиця 27. Підсумки розрахунку зарплати

Імена у формулах

Імена у формулах легше запам'ятати, ніж адреси осередків, тому замість абсолютних посилань можна використовувати іменовані області (одна або кілька осередків). Необхідно дотримуватися таких правил при створенні імен:

• імена можуть містити не більше 255 символів;
• імена повинні починатися з літери та можуть містити будь-який символ, крім пробілу;
• імена не повинні бути схожі на посилання, такі як ВЗ, С4;
• імена не повинні використовувати функції Excel, такі як СУМ, ЯКЩОі т.п.

В меню Вставка, Ім'яІснують дві різні команди створення іменованих областей: Створити та Присвоїти.

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

Створення імені

1. Виділіть комірку В1 (табл. 26).
2. Виберіть у меню Вставка, Ім'я (Insert, Name) команду Присвоїти (Define).
3. Введіть ім'я Годинна ставка та натисніть ОК.
4. Виділіть комірку В1 і переконайтеся, що в полі імені вказано Годинна ставка.

Створення кількох імен

1. Виділіть осередки ВЗ:С5 (табл. 27).
2. Виберіть у меню Вставка, Ім'я (Insert, Name) команду Створити (Create)з'явиться діалогове вікно Створити імена(Рис. 88).
3. Переконайтеся, що перемикач у стовпці ліворуч помічений та натисніть ОК.
4. Виділіть осередки ВЗ:СЗ та переконайтеся, що в полі імені вказано Іванов.

Мал. 88. Діалогове вікно Створити імена

Можна у формулу вставити ім'я замість абсолютного посилання.

1. У рядку формул встановіть курсор у місце, де буде додано ім'я.
2. Виберіть у меню Вставка, Ім'я (Insert, Name) команду Вставити (Paste),з'явиться діалогове вікно Вставити імена.

1. Виберіть потрібне ім'я зі списку та натисніть OK.

Помилки у формулах

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

Excel може розпізнати далеко не всі помилки, але ті, що виявлені, треба вміти виправити.

Помилка # # # # з'являється, коли число, що вводиться, не вміщається в комірці. У цьому випадку слід збільшити ширину шпальти.

Помилка #СПРАВ/0!утворюється, коли у формулі робиться спроба поділу на нуль. Найчастіше це трапляється, коли як дільник використовують посилання на комірку, що містить нульове або порожнє значення.

Помилка #Н/Д!є скороченням терміну "невизначені дані". Ця помилка вказує на використання у формулі посилання на порожню комірку.

Помилка #ІМ'Я?з'являється, коли ім'я, що використовується у формулі, було видалено або не було визначено раніше. Для виправлення визначте або виправте ім'я області даних, ім'я функції та ін.

Помилка #ПУСТО!з'являється, коли задано перетин двох областей, які насправді не мають спільних осередків. Найчастіше помилка вказує, що припущена помилка під час введення посилань на діапазони осередків.

Помилка #ЧИСЛО!з'являється, коли функції з числовим аргументом використовується неправильний формат або значення аргументу.

Помилка #ЗНАЧ! з'являється, коли у формулі використовується неприпустимий тип аргументу чи операнда. Наприклад, замість числового чи логічного значення для оператора чи функції введено текст.

Крім перелічених помилок, під час введення формул може виникнути циклічна посилання.

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

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

Функції у Excel

Більш складні обчислення таблицях Excel здійснюються з допомогою спеціальних функцій (рис. 90). Список категорій функцій доступний під час вибору команди Функціяу меню Вставка (Insert, Function).

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

Функції Дата та час дозволяють працювати зі значеннями дати та часу у формулах. Наприклад, можна використовувати у формулі поточну дату, скориставшись функцією СЬОГОДНІ.

Мал. 90. Майстер функцій

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

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

Функції роботи з базами данихможна використовувати для виконання розрахунків та для відбору записів за умовою.

Текстові функціїнадають користувачеві можливість обробки тексту. Наприклад, можна об'єднати кілька рядків за допомогою функції ЗЧЕПИТИ.

Логічні функції призначені для перевірки однієї чи кількох умов. Наприклад, функція ЯКЩОдозволяє визначити, чи виконується зазначена умова, і повертає одне значення, якщо умова істинна, та інше, якщо вона хибна.

Функції Перевірка властивостей та значеньпризначені для визначення даних, що зберігаються в комірці. Ці функції перевіряють значення в комірці за умовою та повертають залежно від результату значення ІСТИНА чи БРЕХНЯ.

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

Вправа

Обчислення середнього значення для кожного рядка у файлі Освіта.хls.

1. Виділіть комірку F3 і натисніть кнопку майстра функцій.
2. У першому вікні майстра функцій з категорії Статистичні виберіть функцію Відмінник, Натисніть на кнопку Далі.
3. У другому діалоговому вікні майстра функцій мають бути задані аргументи. Курсор введення знаходиться у полі введення першого аргументу. У цьому полі як аргумент число! введіть адресу діапазону B3: D3 (рис. 91).
4. Натисніть ОК.
5. Скопіюйте отриману формулу в комірки F4:F6 та збережіть файл (табл. 28).

Мал. 91. Введення аргументу у майстрі функцій

Таблиця 28. Таблиця результатів розрахунку за допомогою майстра функцій

Для введення діапазону осередків у вікно майстра функцій можна обвести на робочому аркуші таблиці цей діапазон (у прикладі B3:D3). Якщо вікно майстра функцій закриває потрібні осередки, можна пересунути вікно діалогу. Після виділення діапазону осередків (B3:D3) навколо нього з'явиться пунктирна рамка, що біжить, а в полі аргументу автоматично з'явиться адреса виділеного діапазону осередків.

Щоб зрозуміти як користуватися цією програмою, необхідно розглянути формули EXCEL з прикладами.

Якщо поставити курсор мишки на будь-яку комірку і натиснути на пункт «вибрати функцію», з'являється майстер функцій.

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

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

Якщо функція невідома користувачеві, він може встановити категорію «повний алфавітний перелік».

Наприклад, дане завдання, знайти функцію СУМЕСЛІМН. Для цього потрібно зайти до категорії математичних функцій і там знайти потрібну.

Функція ВВР

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

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

Обчислення ВПР можна простежити з прикладу, у якому наведено список з прізвищ . Завдання – за запропонованим номером знайти прізвище.

Застосування функції ВВР

Формула показує, що першим аргументом функції є комірка С1.

Другий аргумент А1:В10 - це діапазон, в якому здійснюється пошук.

Третій аргумент – це порядковий номер шпальти, з якого слід повернути результат.

Обчислення заданого прізвища за допомогою функції ВПР

Крім того, здійснити пошук прізвища можна навіть у випадку, якщо деякі порядкові номери пропущені.

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

Пошук прізвищ з пропущеними номерами

Пояснюється таке явище тим, що функція ВПР має четвертий аргумент, за допомогою якого можна задати інтервальний перегляд.

Він має лише два значення – «брехня» чи «істина». Якщо аргумент не задається, він встановлюється за умовчанням у позиції «істина».

Округлення чисел за допомогою функцій

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

А набуте значення можна використовувати при розрахунках в інших формулах.

Округлення числа здійснюється за допомогою формули «ОКРУГЛВЕРХ». Для цього потрібно заповнити комірку.

Перший аргумент – 76375, а другий – 0.

Округлення числа за допомогою формули

У разі округлення числа відбулося більшу сторону. Щоб округлити значення в меншу сторону, слід вибрати функцію «ОКРУГЛВНИЗ».

Округлення відбувається до цілого числа. У нашому випадку до 77 чи 76.

У програмі Excel допомагають спростити будь-які обчислення. За допомогою електронної таблиці можна виконати завдання з математики.

Найактивніше програму використовують проектувальники, підприємці, а також студенти.

Вся правда про формули програми Microsoft Excel 2007

Формули EXCEL з прикладами - Інструкція застосування

Якщо ви раніше не використовували табличний процесор для створення документів, радимо прочитати наше керівництво Ексель (Excel) для чайників.

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

Детальна інформація про базові функції та можливості табличного процесора MS Excel. Опис основних елементів документа та інструкція для роботи з ними у нашому матеріалі.

Робота з осередками. Заповнення та форматування

Перш ніж розпочинати конкретні дії, необхідно розібратися з базовим елементом будь-якого документа в Excel. Файл Ексель складається з одного або кількох аркушів, розграфлених на дрібні осередки.

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

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

Мал. 1 – приклад заповнення осередків

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

Виберемо осередок «Доля акцій». Адреса її розташування – А3. Ця інформація вказана в панелі властивостей, що відкрилася. Також ми можемо побачити вміст. Формул цієї клітини немає, тому вони не показуються.

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

Мал. 2 – контекстне меню осередку та її основні властивості

Сортування даних

Часто користувачі стикаються із завданням сортування даних на аркуші Ексель. Ця функція допомагає швидко вибрати та переглянути лише потрібні дані з усієї таблиці.

Перед вами вже заповнена таблиця (як її розберемо далі в статті). Уявіть, що потрібно відсортувати дані за січень за зростанням. Як би це ви зробили? Банальне передрук таблиці - це зайва робота, до того ж, якщо вона об'ємна, робити цього ніхто не буде.

Для сортування Excel є спеціально відведена функція. Від користувача потрібно лише:

• Виділити таблицю чи блок інформації;
• Відкрити кладку «Дані»;
• Натисніть на іконку «Сортування»;

Мал. 3 – вкладка «Дані»

• У вікні, що відкрилося, виберіть колонку таблиці, над якою будемо проводити дії (Січень).
• Далі тип сортування (ми виконуємо угруповання за значенням) та, нарешті, порядок – за зростанням.
• Підтвердьте дію, натиснувши кнопку «ОК».

Мал. 4 – налаштування параметрів сортування

Відбудеться автоматичне сортування даних:

Мал. 5 – результат сортування цифр у стовпці «Січень»

Аналогічним чином можна проводити сортування за кольором, шрифтом та іншими параметрами.

Математичні розрахунки

Головна перевага Excel – можливість автоматичного проведення розрахунків у процесі заповнення таблиці. Наприклад, у нас є два осередки зі значеннями 2 і 17. Як у третій осередок вписати їх результат, не роблячи розрахунки самостійно?

Для цього, вам необхідно клікнути на третій осередок, в який буде вписано кінцевий результат обчислень. Потім натисніть на значок функції f(x), як показано нижче. У вікні, що відкрилося, виберіть дію, яку хочете застосувати. СУМ – це сума, СРЗНАЧ – середнє значення тощо. Повний список функцій та їх найменувань у редакторі Excel можна знайти на офіційному сайті компанії Microsoft.

Нам потрібно знайти суму двох осередків, тож натискаємо на «СУМ».

Мал. 6 – вибір функції «СУМ»

У вікні аргументів функції є два поля: «Число 1» та «Число 2». Виберіть перше поле та клацніть мишкою на комірку з цифрою «2». Її адреса запишеться до рядка аргументу. Клацніть на «Число 2» та натисніть на комірку з цифрою «17». Потім підтвердіть дію та закрийте вікно. Якщо необхідно виконати математичні дії з трьома або великою кількістю клітин, просто продовжуйте вводити значення аргументів у поля "Число 3", "Число 4" і таке інше.

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

Мал. 7 – результат виконання підрахунків

Створення таблиць

У еселівських таблицях можна зберігати будь-які дані. За допомогою функції швидкого налаштування та форматування в редакторі дуже просто організувати систему контролю особистого бюджету, список витрат, цифрові дані для звітності та інше.

Таблиці в Excel мають перевагу перед аналогічною опцією Word та інших офісних програмах. Тут ви маєте можливість створити таблицю будь-якої розмірності. Дані заповнюються легко. Існує панель функцій для редагування вмісту. До того ж готову таблицю можна інтегрувати у файл docx за допомогою звичайної функції копіювання-вставка.

Щоб створити таблицю, виконайте інструкції:

• Перейдіть на вкладку «Вставка». У лівій частині панелі опцій виберіть «Таблиця». Якщо вам необхідно звести будь-які дані, вибирайте пункт «Зведена таблиця»;
• За допомогою мишки виділіть місце на аркуші, яке буде відведено для таблиці. Також можна ввести розташування даних у вікно створення елемента;
• Натисніть кнопку OK, щоб підтвердити дію.

Мал. 8 – створення стандартної таблиці

Щоб відформатувати зовнішній вигляд таблички, відкрийте вміст конструктора і в полі «Стиль» клацніть на шаблон, що сподобався. За бажанням, можна створити власний вигляд з іншою кольоровою гамою та виділенням осередків.

Мал. 9 – форматування таблиці

Результат заповнення таблиці даними:

Мал. 10 – заповнена таблиця

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

Додавання графіків/діаграм

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

Щоб створити діаграму/графік, потрібно:

• Повністю виділити таблицю. Якщо графічний елемент потрібно створити лише для відображення даних певних осередків, виділіть лише їх;
• Відкрийте вкладку вставки;
• У полі рекомендованих діаграм виберіть іконку, яка, на вашу думку, найкраще візуально опише табличну інформацію. У нашому випадку це об'ємна кругова діаграма. Піднесіть до іконки вказівник та виберіть зовнішній вигляд елемента;

Аналогічним чином можна створити точкові графіки, лінійні діаграми та схеми залежності елементів таблиці. Всі отримані графічні елементи можна також додати до текстових документів Ворд.

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

Тематичні відеоролики: