Можна створити просту формулу для додавання, віднімання, множення або поділу значень на аркуші. Прості формули завжди починаються зі знаку рівності (=), за яким йдуть константи, які є числовими значеннями та операторами обчислення, такими як плюс (+), мінус (–), зірочка (*) або коса риса (/).
Нижче наведено приклад простої формули.
- На аркуші виділіть комірку, в яку потрібно ввести формулу.
- Введіть = (знак рівності), а потім константи та оператори (не більше 8192 знаків), які потрібно використовувати при обчисленні. У нашому прикладі введіть =1+1.
- Замість введення констант у формулі можна вибрати осередки з потрібними значеннями та ввести оператори між ними.
- Відповідно до стандартного порядку математичних операцій, множення та розподіл виконуються до складання та віднімання.
Розглянемо інший варіант простий формули. Введіть =5+2*3 в іншому осередку і натисніть клавішу ВВЕДЕННЯ або Return. Excel перемножить два останніх числа і додасть перше число до результату множення.
Використання автосумування
Для швидкого підсумовування чисел у стовпці чи рядку можна використовувати кнопку "Автосумма". Виберіть комірку поряд з числами, які ви бажаєте підсумувати, виберіть Автосума на вкладці Головна , натисніть клавішу ВВЕДЕННЯ (Windows) або Return (Mac) та все!
При виборі параметра Автосума Excel автоматично вводить формулу (яка використовує функцію СУМ) для підсумовування чисел.
Примітка: Також в осередку можна ввести ALT+= (Windows) або ALT+
+= (Mac) і Excel автоматично вставить функцію СУМ.
Наведемо приклад.Щоб додати числа січня до цього бюджету розваги, виберіть комірку B7, комірку безпосередньо під стовпцем чисел. Потім виберіть Автосума. У осередку B7 з'явиться формула, а Excel виділяє осередки, які ви підсумовуєте.
Щоб відобразити результат (95,94) у клітинці В7, натисніть клавішу ENTER. Формула також відображається у рядку формул у верхній частині вікна Excel.
- Щоб отримати суму стовпця чисел, виділіть комірку відразу під останнім числом у стовпці. Щоб отримати суму рядка чисел, виділіть комірку відразу праворуч.
- Створивши формулу один раз, її можна копіювати до інших осередків, а не вводити знову і знову. Наприклад, якщо скопіювати формулу з комірки B7 в комірку C7, формула C7 автоматично адаптується до нового розташування і обчислює числа C3:C6.
- Крім того, ви можете використовувати функцію «Автосума» одразу для кількох осередків. Наприклад, можна виділити осередки B7 та C7, вибрати AutoSum і підсумовувати обидва стовпці одночасно.
Скопіюйте дані прикладу з таблиці нижче та вставте їх у комірку A1 нового листа Excel. За потреби змініть ширину стовпців, щоб побачити всі дані.
Примітка: Щоб ці формули виводили результат, виділіть їх та натисніть клавішу F2, а потім — ВВЕДЕННЯ (Windows) або Return (Mac).
Програмне забезпечення Microsoft Office 07.12.2019 6410 Microsoft Excel полегшує життя тим, хто працює у сфері інформаційних технологій. Електронні таблиці, завдяки своїй величезній функціональності, дозволяють виконувати найскладніші обчислення, аналізувати їх та будувати діаграми. Використання різних типів формул дозволяє працювати з константами, операторами, посиланнями, текстом, функціями тощо.Давайте обговоримо, як створити формулу Excel, а також детально розберемо конкретні приклади.
Як створити формулу в Excel?
- Відкриється вікно з безліччю осередків. Саме в них потрібно вводити формули. Кожен осередок має свою унікальну адресу, що складається з номера стовпця та літери рядка. Він також відображається в спеціальному полі над таблицею, якщо ви помістите курсор на комірку, що цікавить. На екрані активний осередок виділено чорним кольором, його адресу E5.
- Наступним кроком буде створення таблиці Excel. Вносимо туди вихідні дані, необхідні розрахунків. У нашому випадку – доходи та витрати на конкретну дату. Необхідно розраховувати прибуток за кожен день (віднімати витрати з доходів).
- Клацніть лівою кнопкою миші по комірці, в якій ви очікуєте побачити результат. У цьому випадку ми розраховуємо прибуток за перший день, адресу осередку D5.
- Клацніть лівою кнопкою миші комірку доходу (B5). Він буде виділений кольором, а його адреса з'явиться після позначки рівності.
- Курсор знаходиться в комірці, в яку ми вводимо формулу. Натисніть знак мінуса "-" на клавіатурі "-".
- У прикладі потрібно розрахувати прибуток за кілька днів. Звичайно, вам не потрібно щоразу заново вводити формулу в Excel. Ви можете зробити це найпростіше. Клацаємо по осередку, в якому вже було введено формулу, переміщаємо курсор миші у правий нижній кут – дивимося, щоб він перетворився на знак плюса.
- Натисніть і утримуйте ліву кнопку миші, одночасно вибираючи потрібні комірки (якби ви розтягували формулу).
- Відпустивши кнопку миші, ми побачимо, що формула автоматично скопіювалася в комірки, де відразу відобразилися результати розрахунку.
- Як видно з прикладу, нічого складного немає і розглянутий спосіб введення формули — не можливий варіант.Є інший спосіб: вгорі екрана (над осередками) є спеціальний рядок, в який також можна вводити формули. При цьому слід враховувати, що зовсім не обов'язково клацати осередками, які беруть участь у формулі, достатньо вказати їх адреси.
Порада: Іноді при роботі з великим обсягом даних у табличній формі стає незручно постійно повертатися на початок, щоб побачити заголовок. В цьому випадку в Excel краще "заморозити" рядок – дані потечуть, а заголовок таблиці залишиться на місці.
Приклади написання формул в Екселі
Використання математичних, логічних, фінансових та інших функцій дозволяє користувачам виконувати величезну кількість обчислень. Таблиці значно полегшують життя студентам, інженерам, економістам, маркетологам тощо. Список формул, доступних в Excel, можна переглянути кількома способами:
У Excel є багато різних операторів і функцій – давайте розглянемо докладніше найпопулярніші.
СУМ – підсумовування чисел
Практично кожному потрібно щось підсумувати, тому оператор СУММ використовується в Excel частіше за інших. Алгоритм дій за необхідності складання чисел:
- Клацніть комірку, в якій ви очікуєте отримати результат. Перейдіть на вкладку "Формули" в меню, натисніть "Математика". Знайдіть функцію СУММ у списку, що розкривається, і клацніть по ній лівою кнопкою миші.
- У вікні потрібно ввести аргументи функції. Тут можна піти декількома способами: вибрати потрібні осередки чи діапазон чи ввести їх адреси вручну. При цьому слід враховувати, що осередки перераховуються через точку з комою (наприклад, A1; A3; A4), а діапазон у формулах позначається двокрапкою (наприклад, B4: B10).
- Натисніть «ОК» та перегляньте результат підсумовування.Зверніть увагу, що у рядку формул відображається функція та її аргументи.
Синтаксис функції: = СУМ (число1; число2; число3; …) або = СУММ (число1: число N).
СУМІСЛІ – підсумовування при дотриманні заданої умови
Відмінний оператор, що дозволяє швидко додавати числа до осередків, якщо виконується умова. Наприклад, вам потрібно додати тільки позитивні числа або дізнатися про загальну зарплату продавців без урахування менеджерів та інших співробітників. Розглянемо приклад, в якому вам потрібно розрахувати зарплату для кожної позиції в Excel:
- Виберіть діапазон для суми – для цього потрібно натиснути на відповідний рядок, потім вибрати осередки із зарплатою (діапазон E4: E13).
- Визначте діапазон, з якого ми візьмемо комірки, які відповідають умові. У нашому випадку ми вибираємо осередки з локаціями. У рядку "Критерій" пишемо – "продавець", тобто розраховуємо зарплати всіх продавців.
Звичайно, умови можуть бути різними, все залежить від конкретної ситуації та потреб користувача.
Синтаксис функції: = СУМІСЛІ (діапазон, критерій, діапазон_суми).
СТУПЕНЬ – зведення в ступінь
Часто необхідно певною мірою збільшити число, тому слід використовувати функцію ГРАДУСИ:
- У вікні, введіть аргументи функції: число – це основа (те, що ми зводимо в ступінь), ступінь – це показник ступеня.
Аргументи записуються як вручну з клавіатури, так і при натисканні на комірки, якщо вони були попередньо заповнені.
Синтаксис функції: = Градуси (число; градус).
ВИПАДОКМІЖ — виведення випадкового числа в інтервалі
Ця функція повертає довільне число між двома певними. Непогана альтернатива звичайному сервісу. Ми діємо так:
- Аргументами функції є нижня та верхня межі: ми вводимо їх, натискаючи на комірки.
Синтаксис функції: = РАЗМІЖ (нижня_ межа; верхня межа).
ВПР – пошук елемента в таблиці
Функція, яка значно заощаджує час, допомагаючи вам знаходити дані. Це стосується посилальних операторів. Ви можете використовувати його в різних ситуаціях, наприклад, вам потрібно знайти код співробітника з ім'ям співробітника:
- У полі «Значення пошуку» пишемо, що шукаємо (у разі повне ім'я співробітника). У аргументі функції "Таблиця" потрібно вказати область пошуку (виділити всю таблицю). У "Номер стовпця" вказуємо, з якого стовпця потрібно повертати результат (залежний код). У режимі відтворення інтервалів введіть FALSE, якщо потрібний збіг.
Синтаксис функції: ВПР (lookup_value, table, column_num, range_lookup).
СРЗНАЧ – повернення середнього значення аргументів
Статистична функція, що дозволяє обчислити середнє арифметичне. Допустимо, ви хочете дізнатися середню зарплату в компанії:
- На вкладці «Формули» клацніть «Додаткові функції» та виберіть пункт «Статистика». У списку знайдіть СЕРЕДНЕ.
- В «Аргументах функцій» необхідно вказати зарплату кожного співробітника, виділивши відповідні осередки таблиці. Також можна ввести діапазон вручну.
- Функція поверне середнє арифметичне аргументи, яке буде середньою зарплатою в компанії.
Синтаксис функції: = СЕРЕДНЕ (число1; число2;).
МАКС – визначення найбільшого значення з набору
Необхідність швидко знайти найбільше значення будь-якої вибірки виникає досить часто. І тут допоможе функція статистики MAX. Допустимо, нам потрібно дізнатися, яка максимальна кількість відвідувачів була на сайті за певний період:
- Клацніть комірку, в якій відображатиметься результат. На вкладці «Формули» натисніть «Вставити функцію», у «Категорії» виберіть «Статистика» і в списку — МАКС.
- В «Аргументах функції» потрібно вказати діапазон, з якого буде вибрано максимальне число. У нашому випадку осередки із кількістю відвідувачів. Натисніть «Число 1» та виберіть потрібні комірки (звісно, ви можете ввести діапазон із клавіатури).
Синтаксис функції: = МАКС (число1; число2; …).
Корел – коефіцієнт кореляції
Надзвичайно корисна та зручна функція, що дозволяє визначати та оцінювати взаємозв'язок між наборами даних. Наприклад, ми маємо інформацію про кількість відвідувачів сайту за кожен день і дані про покази реклами в цей час. Визначаємо, як реклама впливає на кількість гостей:
- У вкладці «Формули» натискаємо «Вставити функцію», у «Категорії» переходимо до «Статистика» та знаходимо КОРЕЛЯЦІЮ у списку.
- Заповнюємо "Масиви функцій": як "Масив 1" вибираємо осередки з кількістю відвідувачів, в "Масив 2" вставляємо дані про показ реклами.
- В результаті функція Корель повертає значення коефіцієнта кореляції, який показує наявність або відсутність залежності двох значень один від одного. У цьому прикладі це 0,7261, що означає досить тісний зв'язок між кількістю відвідувачів веб-сайту та показом реклами.
Щоб отримати ще більше інформації про взаємозв'язок між випадковими величинами, ви можете виконати регресійний аналіз, на основі якого ви зможете побудувати графік функції Excel або створити діаграму Word.
Синтаксис функції: = КОРРЕЛ (масив1; масив2).
ДНІ – кількість днів між двома датами
Функція, за допомогою якої Excel можна швидко дізнатися кількість днів між двома відомими датами.Наприклад, співробітник йде у відпустку: є дві дати, які визначають тривалість відпустки. Вам потрібно розрахувати, скільки днів триває свято:
- У полі «Кінець_дата» введіть дату закінчення вихідного, клацнувши відповідний осередок, а в «Початкова_дата» — дату його початку.
Синтаксис функції: = ДНІ (кінцева_дата; початкова_дата).
ЯКЩО – виконання умови
Ця функція Excel призначена для перевірки певної умови. Наприклад, компанія має план продажу на кожен день і співробітники, які ведуть статистику загального обсягу продажів для кожного з них. Визначте, хто добре спрацював та виконав план:
- Заповнимо «Аргументи функції». У полі «Log_Expression» потрібно написати умову: у нашому випадку виторг від продажів (B5) має бути більше 40 000 руб. У Value_if_true ми пишемо, що буде відображатися в осередку при виконанні умови. Отже, якщо прибуток перевищує 40 000 рублів, «план виконаний». У "Value_if_false" вказуємо "План не виконаний" – ця пропозиція з'явиться в осередку, якщо дохід продавця менше 40 000 руб.
- Розповсюджуємо формулу на інші осередки і отримуємо результат виконання плану для всіх співробітників.
Синтаксис функції: = ЯКЩО (логічне_вираження; значення_якщо_ справжнє значення; значення_якщо_ брехня).
ЗЧЕПИТИ – об'єднання текстових рядків
Формула відноситься до тексту і призначена для поєднання кількох рядків в один. Часто використовується в Excel для об'єднання двох або більше осередків. Наприклад, прізвища, імена та по батькові людей записуються в різних осередках, і виникла потреба створити зведений стовпець. Ми діємо:
- В «Аргументах функцій» ми послідовно вказуємо комірки, що об'єднуються. Ви можете натиснути на них або вручну ввести буквено-цифрове позначення.
- Вирішити цю проблему не так вже й складно – деякі рекомендують просто додати пробіл після тексту в кожному осередку, проте, якщо інформації багато, ви не хочете додавати зайві символи. Краще піти іншим шляхом: виправити формулу. Для цього в синтаксисі функції потрібно додати прогалини в лапках – "".
Синтаксис функції: = ЗЧЕПИТИ (текст1; текст2;…).
ЛЕВСІМ – повертає задану кількість символів
Зручна функція Excel, яка дуже допомагає в роботі з текстом, повертаючи певну кількість символів з початку рядка. Наприклад, у статей є заголовки, але іноді досить довгі, що погіршує сприйняття. Якщо ви визначите, що заголовок повинен складатися з 70 символів, ви можете дізнатися, на що схожі заголовки, та виправити їх:
- У полі «Текст» введіть потрібну комірку, у полі «Кількість знаків» вкажіть бажану довжину заголовка.
- Розтягуємо формулу на інші осередки та отримуємо результат: текст обмежений зазначеною кількістю символів.
Синтаксис функції: = LEFT (текст; число символів).
Підбиваємо підсумки
Excel дозволяє вирішувати безліч різних завдань, чи то віднімання відсотка від суми, чи проведення регресійного аналізу наборів даних. Одночасно керувати величезною кількістю функцій непросто, але жодна з них не змушує вас освоювати програму як усередині, так і зовні. Діяти згідно з поставленими цілями цілком реально. В Excel є довідкові матеріали за кожною формулою, які краще прочитати перед початком роботи.
Формула, вона функція, – одна з основних складових електронних таблиць, створюваних за допомогою програми Microsoft Excel.Розробники додали величезну кількість різних функцій, призначених до виконання як простих, і складних розрахунків. До того ж користувачеві дозволено самостійно проводити математичні операції, що також можна назвати своєрідною реалізацією формул. Саме про роботу з цими компонентами і йтиметься далі.
Я розберу основи роботи з формулами та корисні «фішки», здатні спростити процес взаємодії з таблицями.
Пошук переліку доступних функцій в Excel
Якщо ви починаєте своє знайомство з Microsoft Excel, корисно буде дізнатися, які функції існують, для чого призначені і як відбувається їх створення. Для цього у програмі є графічне меню з відображенням всього списку формул та коротким описом дії розрахунків.
- Відкрийте вкладку Формули та натисніть на кнопку "Вставити функцію" або розгорніть список з категорією функцій, що вам сподобалася.
- Натомість завжди можна клікнути по значку із зображенням "Fx" для відкриття вікна "Вставка функції".
- У цьому вікні переключіть категорію на «Повний алфавітний список», щоб у списку нижче відобразилися всі доступні формули Excel, розташовані в алфавітному порядку.
- Виділіть будь-який рядок лівою кнопкою миші та прочитайте короткий опис знизу. У дужках показано синтаксис функції, якого необхідно дотримуватись під час її написання, щоб усі аргументи та значення збігалися, а обчислення відбувалося коректно. Натисніть «Довідка з цієї функції», якщо хочете відкрити сторінку про неї в офіційній документації Microsoft.
- У браузері ви побачите велику кількість інформації за обраною формулою як у текстовому, так і у форматі відео, що дозволить самостійно розібратися із принципом її роботи.
Зазначу, що наявність подібної інформації російською мовою, ще й у такому розгорнутому вигляді, робить процес знайомства з ПЗ ще більш простим, особливо коли йдеться про перехід до складніших функцій, що діють не зовсім очевидним чином. Не соромтеся і переходьте на згадані сторінки, щоб отримати довідку від фахівців і дізнатися про щось нове, що хоча б мінімально або навіть значно прискорить робочий процес.
Вставлення функції до таблиці
Тепер давайте розберемося з тим, як Excel задати формулу, тобто додати її в таблицю, забезпечивши обчислення певних значень. Ви можете писати функції як самостійно, оголошуючи їхню назву після знака «=», так і використовувати графічне меню, перехід до якого здійснюється так, як було показано вище. У Ком'юніті вже є стаття «Як вставити формулу в Excel», тому я рекомендую натиснути на виділене посилання і перейти до прочитання корисного матеріалу.
Використання математичних операцій в Excel
Якщо необхідно виконати математичні дії з осередками або конкретними числами, в Excel також створюється формула, оскільки всі записи, що починаються з «=» в осередку, вважаються функціями. Усі знаки для математичних операцій є стандартними, тобто*»- помножити, «/»- Розділити і так далі. Слід зазначити, що зведення у ступінь використовується знак «^». Розглянемо коротко оголошення подібних функцій.
Виділіть будь-який порожній осередок і напишіть у ньому знак «=», Оголосивши тим самим функцію. Як значення можете взяти будь-яке число, написати номер комірки (використовуючи літерні та цифрові значення зліва та зверху) або виділити її лівою кнопкою миші. На наступному скріншоті ви бачите простий приклад =B2*C2тобто результатом функції буде перемноження зазначених осередків один на одного.
Після заповнення даних натисніть Enter та ознайомтеся з результатом. Якщо синтаксис функції дотримано, у вибраному осередку з'явиться число, а не повідомлення про помилку.
Спробуйте самостійно використовувати різні математичні операції, додаючи дужки, чергуючи цифри та комірки, щоб швидше розібратися з усіма можливостями математичних операцій та в майбутньому застосовувати їх, коли це знадобиться.
Розтягування функцій та позначення константи
p align="justify"> Робота з формулами в Ексель передбачає і виконання більш складних дій, пов'язаних із заповненням рядків всієї таблиці і зв'язуванням декількох різних значень. У цьому розділі статті я об'єднаю одразу дві різні теми, оскільки вони тісно пов'язані між собою та обидві спрощують взаємодію з відкритим у програмі проектом.
Спочатку зупинимося на розтягуванні функції. Для цього вам необхідно ввести її в одному осередку та переконатися в отриманні коректного результату. Потім затисніть крапку в нижньому правому кутку комірки і проведіть вниз.
У результаті ви повинні побачити, що функція розтягнулася на обраний діапазон, а значення в ній автоматично підставлені. Так, початкова функція мала вигляд =B2*C2, але після розтягування вниз наступні значення підставилися автоматично (від B3*C3 до B13*C13, що видно на наступному зображенні). Так само розтягування працює з СУМ та іншими простими формулами, де використовують кілька аргументів.
Константа, або абсолютне посилання, – позначення, що закріплює конкретну комірку, стовпець або рядок, щоб при розтягуванні функції обране значення не замінювалося, а залишалося таким самим.
Спочатку розберемося з тим, як поставити константу.Як приклад зробимо постійним і рядок, і стовпець, тобто закріпимо комірку. Для цього поставте знак «$» як біля літери, так і цифри комірки, щоб у результаті вийшло таке написання, як показано на наступному зображенні.
Розтягніть функцію і зверніть увагу на те, що постійне значення таким самим і залишилося, тобто відбулася заміна лише першого аргументу. Зараз це може здатися складним, але варто вам самостійно реалізувати подібне завдання, як все стане гранично ясно, і в майбутньому ви згадаєте, що для виконання конкретних завдань можна використати подібну хитрість.
У закріплення теми розглянемо три константи, які можна позначити під час запису функції:
- $В$2 – при розтягуванні чи копіюванні залишаються незмінними стовпець і рядок.
- B$2 – Незмінний рядок.
- $B2 – Константа стосується лише стовпця.
Побудова графіків функцій
Графіки функцій – тема, опосередковано пов'язані з використанням формул в Excel, оскільки передбачає не додавання в таблицю, а безпосереднє складання таблиці за такою формулою, щоб потім сформувати з неї діаграму чи лінійний графік. Зараз детально зупинятись на цій темі не будемо, але якщо вона вас цікавить, перейдіть за посиланням нижче, щоб прочитати іншу мою статтю з цієї теми.
У цій статті ви дізналися, які функції в Excel, як зробити формулу і використовувати корисні можливості програми, що роблять процес взаємодії з електронними таблицями простіше. Застосовуйте отримані знання для самостійної практики та поставлених завдань, що вимагають проведення розрахунків та їхньої автоматизації.