Як створити формулу в Excel: інструкція для новачків
Розповіли та показали на прикладах, як створювати формули вручну, як використовувати вбудовані формули, як «розтягнути» формулу на інші осередки.
Зображення: Meery Mary для Skillbox Media
Розповідає просто про складні речі зі світу бізнесу та управління. До редактури — п'ять років у банку та три — в оцінці майна.
Формули в Excel - це команди. Їх вводять у комірки, щоб провести розрахунки чи виконати операції зі значеннями таблиці.
Якщо ви починаєте розбиратися в роботі з електронними таблицями, прочитайте цю інструкцію.
- як створити формулу Excel вручну;
- як використовувати вбудовані формули Excel;
- як скопіювати одну формулу в кілька осередків, закріпивши частину значень;
- як користуватися формулами в Google Таблицях;
Як створити формулу в Excel вручну
Щоб створити формулу, потрібно вибрати порожню комірку, ввести знак рівності і далі написати формулу у звичайному математичному вигляді. Наприклад, щоб скласти числа 5 і 2, потрібно ввести формулу =5+2. Потім натиснути Enter.
Створюємо формулу з чисел та отримуємо результат
Скріншот: Excel / Skillbox Media
Створюємо формулу з чисел та отримуємо результат
Скріншот: Excel / Skillbox Media
Крім чисел, формулу можна створювати з посилань на комірки таблиці. Замість того, щоб вводити значення з клавіатури, натисніть на потрібні комірки зі значеннями.
Наприклад, щоб підсумувати значення осередків A1 і A2, виберіть порожню комірку та введіть знак рівності. A1, введіть оператор + і клацніть на комірку A2. Вийде така формула: =A1+A2. Потім також натисніть Enter.
Створюємо формулу з посилань на комірки таблиці
Скріншот: Excel / Skillbox Media
Для створення формул Excel використовують стандартні математичні оператори. У таблиці перерахували основні оператори та їх значення.
| Оператор | Яку операцію виконує |
|---|
| + | Скласти |
| - | Відняти або показати негативне значення |
| / | Поділити |
| * | Помножити |
| ^ | Звести у ступінь |
| & | Об'єднати значення осередків в одну послідовність |
| : | Створити діапазон осередків. Створює одне посилання на всі осередки, що знаходяться між першим і останнім осередками діапазону включно |
При введенні формули в комірці вона одночасно з'являється у рядку формул - у білій смузі під панеллю інструментів.
Вводити формулу можна відразу в цей рядок. Для цього виберіть порожню комірку - місце, де з'явиться результат формули. Потім поставте курсор миші в рядок формул, введіть знак рівності і напишіть формулу математичні оператори, числа, посилання на комірки.
Далі також натисніть Enter або кнопку з галочкою, розташовану ліворуч від рядка формул, - Excel проведе розрахунок і видасть результат.
Створюємо формулу в рядку формул Excel і отримуємо результат
Скріншот: Excel / Skillbox Media
Створюємо формулу в рядку формул Excel і отримуємо результат
Скріншот: Excel / Skillbox Media
Навчання у Skillbox для тих, хто хоче професійно працювати в Excel
- Курс "Excel + Google Таблиці з нуля до PRO"
- Курс «Excel для робочих та особистих завдань»
- Безкоштовний онлайн-інтенсив «Експрес-курс по Excel: освоюємо таблиці з нуля за 3 дні»
- Безкоштовні відеоуроки з Excel та «Google Таблицям»
Як використовувати вбудовані формули Excel
Замість писати формули самостійно, можна використовувати формули, вбудовані в Excel. Це готові команди, за допомогою яких проводять розрахунки та виконують різні операції з даними у таблиці.
Наприклад, функцію СУММ застосовують для складання чисел в осередках, а СРЗНАЧ — для того, щоб знайти середнє значення.
Вбудовані формули використовують як для математичних обчислень, але й інших завдань. Наприклад, за допомогою них можна поєднувати інформацію з кількох осередків в одній, видаляти дублікати, перевіряти дані таблиці на відповідність заданим умовам і так далі.
Розберемося де знайти і як використовувати вбудовані формули в Excel.
Виберіть порожню комірку та натисніть кнопку Fx ліворуч від рядка формул. У рядку формул з'явиться знак рівності, а у правій частині екрана – вікно «Побудовник формул» із переліком доступних функцій.
Можна натиснути будь-яку функцію, і в нижній частині екрана з'явиться короткий опис того, які дії вона виконує. Детальний опис усіх вбудованих функцій Excel можна прочитати на веб-сайті підтримки Microsoft.
Подвійним кліком вибираємо необхідну функцію — наприклад виберемо СУММ. З'явиться вікно для введення аргументів функції - значень, які вона використовуватиме виконання розрахунків.
У функції СУМ все просто: аргументи - це значення, які потрібно підсумовувати. Але в деяких функціях аргументи не такі очевидні. Щоб розібратися в них, також звертайтеся до опису функції під будівельником формул або довідкових матеріалів Microsoft.
Побудовник формул запитує аргументи функції для розрахунку
Скріншот: Excel / Skillbox Media
Як і при ручному створенні формули, про який ми говорили вище, можна вводити аргументи функції з клавіатури або вибирати значення клітинок таблиці.
Щоб вибрати значення з таблиці, поставте курсор у рядок аргументу, а потім натисніть на комірку з потрібним значенням.
У нашому прикладі функція СУМ за промовчанням пропонує два аргументи. +, розташовану під аргументами.
Щоб функція здійснила розрахунок, потрібно натиснути кнопку «Готово».
Поруч із створенням формули в будівництві формул вона у рядку формул. Fx=СУМ(B1; B2).
Тиснемо «Готово» і отримуємо результат розрахунку
Скріншот: Excel / Skillbox Media
Також вбудовані формули можна знайти на вкладці Формули в головному меню Excel. Там вони для зручності розбиті за категоріями: фінансові формули, логічні, текстові, математичні і так далі.
Запустити вбудовані функції можна на вкладці «Формули» у головному меню Excel
Скріншот: Excel / Skillbox Media
Для запуску потрібної функції виберіть її в меню, що випадає - у правій частині екрана з'явиться будівельник формул. Після цього порядок дій той же, що ми описали вище.
Запускаємо будівельник формул через вкладку «Формули» у головному меню Excel
Скріншот: Excel / Skillbox Media
Запускаємо будівельник формул через вкладку «Формули» у головному меню Excel
Скріншот: Excel / Skillbox Media
Як скопіювати одну формулу в кілька осередків: абсолютні та відносні посилання
Найчастіше в Excel застосовують формули одночасно до кількох осередків. Наприклад, відразу до всіх осередків стовпця.
Припустимо, у таблиці перераховано кілька найменувань товарів, кількість кожного товару на складі та ціна за одиницю товару.
Так виглядає таблиця з вихідними даними
Скріншот: Excel / Skillbox Media
Нам потрібно розрахувати загальну вартість кожного товару, тобто помножити кількість товару на складі на ціну цього товару. Виберемо перший порожній осередок у стовпці «Вартість, руб.», введемо знак рівності та напишемо формулу: =B2*C2.
Вводимо формулу для розрахунку вартості першого товару
Скріншот: Excel / Skillbox Media
Далі тиснемо Enter і отримуємо результат - вартість першого товару в таблиці. Щоб не вводити цю формулу для інших товарів, її можна скопіювати.
Для цього наведемо курсор на нижній правий кут комірки з результатом – курсор перетвориться на плюсик. Затиснемо його і простягнемо вниз до кінця таблиці.
Копіюємо формулу в інші осередки стовпця
Скріншот: Excel / Skillbox Media
Готово — формула скопіювалася до інших осередків.
Скопійована формула терміново розрахувала вартість кожного товару в таблиці
Скріншот: Excel / Skillbox Media
У цьому прикладі посилання аргументи формули були відносними — при копіюванні формули вони змінювалися кожного рядка таблиці. Наприклад, формула для розрахунку вартості товару з п'ятого рядка була такою: =B5*C5.
Відносні посилання Excel встановлює за промовчанням.
За потреби посилання на осередки можна зробити абсолютними. Аргументи, на яких стоїть абсолютне посилання, при копіюванні формули не змінюються. Давайте розберемося, як це працює, на прикладі.
Допустимо, нам потрібно розрахувати частку вартості кожного товару у загальній вартості товарів.
Так виглядає таблиця з вихідними даними
Скріншот: Excel / Skillbox Media
Для розрахунку частки необхідно вартість одного товару поділити на загальну вартість товарів.
Вводимо формулу для розрахунку частки першого товару
Скріншот: Excel / Skillbox Media
У цьому випадку перший аргумент змінюватиметься для кожного товару, а другий аргумент — загальна вартість — має бути завжди одним і тим самим. Тому посилання на цей аргумент слід зробити абсолютним.
Для цього виділимо у формулі посилання на загальну вартість товарів. D8 - І натиснемо:
- на Windows - клавішу F4;
- на macOS - поєднання клавіш Command + T.
Після цього в абсолютному засланні з'являться додаткові значки $. У нашому прикладі формула набуде такого вигляду: =D2/$D$8.
Робимо посилання на другий аргумент формули абсолютним
Скріншот: Excel / Skillbox Media
Тепер при копіюванні формули до інших осередків стовпця розрахунок проходитиме коректно: значення вартості окремих товарів змінюватимуться кожної рядки, а загальна вартість товарів буде єдиної.
Як користуватися формулами в Google Таблицях
Принципи роботи з формулами в Google Таблицях такі ж, як і в Excel. Головна відмінність у тому, що в Google Таблицях немає побудова формул — аргументи вбудованих функцій потрібно вводити відразу в рядок формул.
Створюємо формулу в Google Таблицях
Скриншот: "Google Таблиці" / Skillbox Media
Створюємо формулу в Google Таблицях
Скриншот: "Google Таблиці" / Skillbox Media
Щоб переглянути всі вбудовані функції «Таблиць Google», натисніть «Вставка» → «Функція». Повний опис функцій - які операції вони проводять, а також який синтаксис - можна прочитати в довідці.
Відкриваємо повний перелік вбудованих функцій у «Google Таблицях»
Скриншот: "Google Таблиці" / Skillbox Media
Щоб закріпити осередки у формулі в Google Таблицях — тобто поставити абсолютні посилання — при введенні формули потрібно натиснути:
- на Windows - клавішу F4;
- на macOS - поєднання клавіш Fn + F4.
Чи вивчаєте Excel? Перегляньте ці статті-інструкції Skillbox Media
- Добірка з посиланнями на 15 статей та відео про інструменти Excel, необхідні у повсякденній роботі
- Як зробити список, що випадає
- Як встановити фільтр та розширений фільтр
- Як зробити сортування даних
- Як зробити зведені таблиці
- Як зробити ВВР
Формули Ексель: Як створити формулу, Оператори та Функції в Excel
Програма Excel за істиною проривний винахід компанії Microsoft. Завдяки такому інструменту, як формули Ексель, можливості програми стають практично безмежними і дозволяють обробляти дані, оскільки вам завгодно за лічені секунди, що в свою чергу економить ваш час та нерви. Тож давайте познайомимося ближче з формулами Ексель і дізнаємося про всі їхні можливості.
Формули Ексель
З чого складаються формули Ексель:
Будь-яка формула Excel має починатися зі знака і «=», щоб програма розуміла, що це формула, а чи не звичайний текст.
Оператори Excel бувають чотирьох видів: арифметичні, оператори порівняння, оператори об'єднання тексту, оператори посилань на осередки.
Функція – це зумовлена формула, яка виконує певний тип обчислень. Наприклад, функція СУММ виконує підсумовування певних осередків. Завдяки функціям скорочується та спрощується формула в Excel.
Як ввести формулу в Excel
Основним елементом програми Excel є формули. Формули Ексель дозволяють отримувати миттєвий результат обчислень. У цьому формула відразу робить перерахунок за зміни вихідних значень.
Розглянемо наступний приклад:
У комірки A1 і B1 помістимо будь-які числа, наприклад, 8 і 5 відповідно. А в комірку C1 введемо формулу:
=A1*B1
Щоб ввести цю формулу в таблиці Excel, необхідно виконати сувору послідовність дій:
Можна вчинити і по-іншому.
При цьому в комірці C1 з'явиться посилання на комірку A1
В Excel як оператор множення використовується символ зірочки «*».
При цьому в комірці після зірочки з'явиться посилання на комірку B1.
У комірці C1 відобразиться результат множення осередків A1 та B1.
Основною перевагою електронних таблиць Excel є автоматичне коригування результату обчислень формули Ексель за зміни даних у осередках, куди вона посилається.
Спробуйте змінити значення в комірках A1 або B1, і ви відразу побачите новий результат обчислень в комірці C1.
Для вказівки осередків, які у формулі, простіше виділити їх мишею, ніж вводити посилання вручну. Це не тільки швидший спосіб, він також знижує ризик завдання неправильних осередків. При введенні з клавіатури можна ненароком ввести невірну літеру стовпця або номер рядка і не побачити помилки, доки не відобразиться обчислений результат формули Ексель.
Формули Ексель: Використання операторів
Оператори здійснюють основні обчислення таблицях Excel. Крім того, вони здатні порівнювати та поєднувати необхідні значення.
Арифметичні оператори
| Математична операція
|
Оператор
|
приклад
|
| Додавання |
+ |
=4+5 |
| Віднімання |
— |
=2-1 |
| Розмноження |
* |
=10*2 |
| Поділ |
/ |
=8/4 |
| Відсоток |
% |
=85% |
| Зведення у ступінь |
^ |
=6^2 |
Зміна природного порядку операцій
У формулах Ексель дотримуються математичні пріоритети виконання операцій, тобто. спочатку виконується множення та розподіл, а вже потім додавання та віднімання.
Наприклад візьмемо таку формулу:
=A1-B1/C1
Заповнимо комірки наступними цифрами: в комірку A1 поставимо число 8, у комірці B1 — 6, а в комірці C1 — 2. Таким чином отримаємо таку формулу:
Використовуючи математичні пріоритети, Excel спочатку розділить 6 на 2, а потім від 8 забере 3. У результаті вийде число 5.
Якщо потрібно спочатку виконати операцію віднімання, а потім розподіл, то потрібні цифри полягають у круглі дужки:
=(A1-B1)/C1
Таким чином, ми даємо команду програмі спочатку виконати операцію віднімання у дужках, а потім розділити отриманий результат. Таким чином, програма забере від 8 цифру 6 і розділить його на 2. У результаті формула видасть зовсім інший результат: 1.
Як і математиці, в таблицях Excel можна використовувати кілька пар дужок, вкладених одна в іншу. Тим самим можна змінювати порядок операцій, так як вам потрібно. Excel спочатку виконає обчислення у внутрішніх дужках, потім у зовнішніх. Наприклад розберемо таку формулу:
=(А3+(В3+С3))*D3
У цій формулі програма спочатку складе осередки B3 і C3, потім до отриманого результату додасть значення в осередку A3 і цю суму помножить на значення в осередку D3.
Якби дужок не було, то програма, за правилами математики, спочатку помножила осередки D3 і C3, а потім додала до отриманого результату значення в осередках B3 і A3.
Не важливо скільки буде у формулі дужок, головне, щоб у кожної дужки, що відкривається, була своя закриваюча дужка. Якщо ж ви забудете поставити одну з дужок, то програма виведе повідомлення з пропозицією внести виправлення у формулу, але не завжди програма розуміє де необхідно поставити потрібну дужку, тому ви можете як погодиться з виправленням, натиснувши на кнопку «Так», так і відмовити від нього, натиснувши кнопку "Ні".
І пам'ятайте, що Excel розуміє лише круглі дужки, якщо ви використовуватимете квадратні або фігурні дужки у формулі, то програма виведе повідомлення про помилку.
Оператори порівняння
Дані оператори порівнюють одне значення з іншим. В результаті оператор порівняння видає ІСТИНУякщо порівняння підтверджується, або Брехняякщо порівняння не підтверджується.
| Знак
|
Оператор
|
приклад
|
| знак «рівності» |
= |
=A1=B2 |
| знак «більше» |
> |
= C3> B1 |
| знак «менший» |
| =B2 |
| знак «більше чи одно» |
>= |
=A3>=D2 |
| знак «менше чи одно» |
| =B3 |
| знак «не одно» |
<> |
=A1<>B1 |
Оператор об'єднання тексту
Щоб об'єднати вміст двох осередків у таблиці Excel, необхідно використовувати символ «&» (амперсанд).ЗЧЕПИТИ». Давайте розглянемо кілька прикладів:
- Для об'єднання тексту чи іншого вмісту з різних осередків у єдине ціле необхідно застосувати таку формулу:
=A1&C1&E1
- Щоб вставити між об'єднаними осередками пробіл, символ, цифру чи літеру потрібно скористатися лапками.
- Об'єднати можна не тільки комірки, а й слова всередині одного комірки.
="Водо"&"пад"
Запам'ятайте, що лапки можна використовувати тільки на скріншоті.
Оператори посилань на комірки
- Щоб створити посилання на діапазон осередків, достатньо ввести перше і останнє посилання на комірки і між ними поставити знак «:» (двокрапка).
=СУМ(A11:A13)
- Якщо потрібно вказати посилання на окремі осередки, то для цього використовують символ «;» (точка з комою).
= СУМ (A11; A12; A13)
- Якщо потрібно вказати значення комірки на перетині діапазонів осередків, між ними ставиться «пробіл».
= F12: G12 G11: G13
Використання посилань
У програмі Excel існують кілька видів посилань на осередки. Однак, не всі користувачі про них знають.
Отже, посилання бувають таких видів: прості посилання, посилання на інший лист, абсолютні посилання, відносні посилання.
Прості посилання
Просте посилання на комірку є адресою стовпця та адресою рядка.Наприклад, посилання B3 вказує, що осередок розташований на перетині стовпця B і рядка номер 3.
У таблиці Excel загальна кількість стовпців дорівнює 16384 (від A до XFD), а рядків 1048576.
Для закріплення розглянемо такі приклади:
- діапазон осередків у стовпці A починаючи з 1 по 10 рядок – «A1:A10»;
- діапазон осередків у рядку 3, починаючи зі стовпця C до E – «C3:E3»;
- усі осередки у рядку 5 – «5:5»;
- всі осередки у рядках з 3 по 28 – «3:28»;
- всі клітини в стовпці C - "C: C";
- всі клітини в стовпцях з D по G - "D: G".
Посилання на інший лист
Іноді у формулі необхідно вказати посилання дані з іншого аркуша. Робиться це досить просто:
=СУМ(Лист2!A3:C3)
На аркуші 2 введені такі значення.
Якщо в назві листа є пробіл, тоді назва листа полягає в одинарних лапках.
=СУМ('аркуш № 2'!A3:C3)
Абсолютні та відносні посилання у формулах Ексель
Відносні посилання
Щоб зрозуміти, що таке відносні посилання, розглянемо наступний приклад.
Ми маємо таблицю продажів за перший квартал 2019 року. Скористаємося функцією СУМ та підрахує загальну суму продажів за січень місяць. Формула виглядатиме так:
=СУМ(B3:B6)
Далі скопіюємо цю формулу в комірку C7.
При копіюванні вихідної формули Ексель в комірку С7 програма трохи змінює формулу, після чого вона набуває такого вигляду:
= СУМ (ЗЗ: С6)
Excel змінює покажчик стовпця з на С, оскільки копіювання проводилося зліва направо по рядках.
Якщо формула копіюється вниз по стовпцю, Excel змінює у формулі значення рядків, а чи не стовпців, щоб формула залишалася коректною. Наприклад, осередок ЕС робочого листа, що розглядається нами, містить таку формулу:
=CУMM(B3:D3)
При копіюванні цієї формули Ексель в осередок Е4 програма створює таку формулу:
= СУМ (В4: D4)
Програма змінила посилання на рядки, щоб вони відповідали новому, четвертому рядку. Оскільки такі посилання на комірки у копіях формули Ексель змінюються щодо напрямку копіювання, вони й називаються відносними.
Абсолютні посилання
Всі нові формули Ексель містять відносні посилання, якщо явно не буде вказано інше. Так як більшість створюваних копій формул потребує коригування посилань на комірки, рідко доводиться думати про інше. Однак іноді виникають виняткові ситуації, у яких необхідно вирішувати, які посилання мають зміщуватися, а які ні.
Одним із найпоширеніших винятків є порівняння осередків деякого діапазону з одним значенням. Наприклад, вам може знадобитися вказати в осередках обсяг продажів кожного з підрозділів щодо загального обсягу продажів компанії в цілому. На робочому аркуші обсягів продажів компанії “Наш концерн” така ситуація виникає при копіюванні формули Ексель, яка обчислює, який відсоток становлять щомісячні обсяги (комірки B9: D9) у щоквартальному обсязі продажів (комірка Е7).
Припустимо, що ми починаємо введення цих формул у рядку 9 з комірки В9. Формула в цьому осередку обчислює відсоток продажів у січні (В7) щодо квартального (Е7) методом поділу. Що може бути простішим?
=В7/Е7
Ця формула поділяє результат січневих продажів (в комірці В7) на квартальний підсумок у осередку Е7. А тепер подивіться, що станеться, якщо перетягнути маркер заповнення на одну комірку вправо, щоб скопіювати формулу в комірку С9:
=C7/F7
Коригування осередку чисельника з В7 на С7 — це те, що лікар прописав. Проте зміна другого покажчика осередку c E7 на F7 - Це вже катастрофа.Ви не тільки не зможете обчислити відсоткове співвідношення лютневого продажу в осередку С9 щодо підсумкових продажів першого кварталу в осередку Е7, але й отримайте в результаті страшну помилку #СПРАВ/0! (#DIV/0!) в осередку С9.
Щоб запобігти зміні посилання на комірку у всіх створюваних копіях формули Ексель, потрібно перетворити посилання з відносної в абсолютну. Це виконується за допомогою кнопки після перемикання Excel на режим редагування (за допомогою клавіші ). У відповідь на це програма поміщає перед літерою стовпця та номером рядка у формулі знаки долара. Як приклад розглянемо скріншот нижче. Осередок В9 на цьому малюнку містить коректну формулу, яку вже можна копіювати в діапазон осередків C9: D9:
=B7/$E$7
Подивимося тепер на цю формулу в осередку С9 після копіювання в діапазон C9: D9 методом перетягування. У рядку формул відображається таке:
=С7/$Е$7
Оскільки посилання Е7 у вихідній формулі ми замінили посиланням $Е$7, всі її копії матимуть ті ж абсолютні (Тобто незмінні) значення.
Якщо ви збираєтеся копіювати формулу, де всі або деякі посилання повинні бути абсолютними, але поки залишаються відносними, змініть формулу так, як описано нижче.
- Двічі клацніть на комірці з формулою або натисніть , щоб розпочати редагування.
- Перемістіть точку вставки до посилання, яке хочете перетворити на абсолютну.
- Натисніть .
- Після завершення редагування клацніть на кнопці Ввод у рядку формул, а потім скопіюйте її в діапазон комірок шляхом перетягування маркера заповнення.
Натискайте клавішу тільки тоді, коли необхідно перетворити посилання на комірку повністю абсолютну. Якщо натисніть клавішу вдруге, то отримаєте так зване змішане посилання, в якому рядок абсолютний, а стовпець відносний (наприклад, Е$7). Якщо натисніть клавішу ще раз, то отримаєте інший тип змішаного посилання, в якому стовпець абсолютний, а рядок відносний (наприклад, $Е7). Якщо ж натиснути клавішу ще раз, посилання стане повністю відносною (наприклад, Е12). Таким чином, ви повернетеся до того, з чого почали. Наступні натискання кнопки повторять вищеописаний цикл перетворень.
Якщо програма Excel встановлена на пристрої із сенсорним екраном, до якого не підключена фізична клавіатура, то єдиний спосіб перетворення адрес осередків у формулах із відносної форми на абсолютну або змішану - відкрити екранну (віртуальну) клавіатуру. З її допомогою додайте значки долара перед літерою стовпця та/або номером рядка для відповідної адреси комірки у рядку формул.
Формули Ексель: Використання функцій
Ви вже знаєте, як створювати формули Ексель, які виконують прості математичні операції, такі як розподіл, множення, додавання та віднімання. Якщо ж вам потрібні складніші формули, то замість комбінування безлічі математичних операцій краще скористатися функціями
Excel.
функцією називають зумовлену формулу, що виконує певний тип обчислень. Їй необхідно передати значення, які у операції (вони називаються аргументами). Як і в простих формулах, аргументами функцій можуть бути числа (наприклад, 22 або -4,56), а також посилання на комірки (B10) або діапазони комірок (СЗ: РЗ).
Як і формулу, функцію потрібно попереджати знаком рівності, щоб програма не сприйняла її як звичайний текст.За знаком рівності повинне слідувати ім'я функції (при введенні можна не звертати уваги на регістр, головне — не допускати друкарської помилки). Після імені функції вказуються аргументи, укладені у круглі дужки.
Якщо ви вводите функцію в комірку вручну, не вставляйте пробіли між знаком рівності, ім'ям та аргументами. Деякі функції для роботи вимагають кількох аргументів - у такому разі поділяйте їх крапкою з комою.
Як тільки буде введено знак рівності та перші символи імені функції, безпосередньо під рядком формул відкриється список усіх функцій, що починаються з цих літер. Якщо ви побачите у списку потрібну функцію, двічі клацніть на ній, і програма вставить її ім'я в рядок формул, додавши дужку для аргументів.
Усі аргументи, які потребує функції, відображаються під рядком формул, при цьому їх можна виділити на робочому аркуші або ввести з клавіатури. Якщо функція має кілька аргументів, перед введенням або виділенням другого аргументу поставте крапку з комою.
Після введення останнього аргументу закрийте правою дужкою, що завершує список аргументів. Як тільки буде введено ім'я функції разом з аргументами, список, що розкривається, під осередком зникне. Щоб вставити функцію в комірку та обчислити її значення, натисніть клавішу Enter.
Вставлення функції у формулу за допомогою майстра
Незважаючи на те, що функцію можна ввести безпосередньо в комірку, рядок формул містить спеціальну кнопку майстра функцій. З її допомогою можна вибрати будь-яку функцію. Після клацання на кнопці відкриється діалогове вікно вибору функції.
Діалогове вікно майстра функцій містить текстове поле Пошук функції (Search for a Function), а також списки Категорія (Or Select a Category) та Виберіть функцію (Select a Function). Коли відкриється вікно вставки функції, автоматично вибирається категорія десяти функцій, що були нещодавно використані.
Після вибору функції відкриється діалогове вікно введення її аргументів. Найкраще використовувати майстер для введення незнайомих функцій, які часто містять безліч не цілком зрозумілих аргументів.
Щоб отримати докладну довідку щодо вибраної функції, клацніть посилання. Довідка з цієї функції, що знаходиться в нижній лівій частині вікна.
Якщо потрібної функції не було у списку нещодавно використаних, виберіть відповідну категорію. Якщо не можете визначитися з категорією, то пошукайте функцію, ввівши її опис у полі Пошук функції, а потім натисніть або клацніть на кнопці Знайти. Excel відкриє список рекомендованих функцій і ви зможете вибрати будь-яку з них.
Наприклад, щоб знайти всі функції Excel, що сумують значення, введіть у поле Пошук функції слово сум та клацніть на кнопці Знайти. Після цього в окремому вікні з'явиться список обов'язкових аргументів і в нижній частині вікна з'явиться призначення функції.
Коли потрібна функція буде знайдена та вибрана, клацніть на кнопці ОК, щоб вставити її в поточну комірку та відкрити вікно аргументів. У цьому вікні відображаються обов'язкові та необов'язкові аргументи.
Як приклад виберіть функцію СУМ (вона зазвичай лідирує в категорії часто використовуваних) і клацніть на кнопці ОК. Як тільки ви це зробите, програма вставить у поточну комірку та рядок формул запис СУМ (). Потім відкриється діалогове вікно введення аргументів. У цьому вікні слід зазначити аргументи функції.
Функція СУМ може підсумовувати до 255 аргументів.Цілком очевидно, що всі вони знаходяться далеко не в одному осередку. Насправді вам доведеться найчастіше підсумовувати значення, які у сусідніх осередках.
Щоб вибрати перший аргумент функції, клацніть на комірці робочого листа або перетягніть вказівник миші по діапазону комірок. У текстовому полі Число1 (Numberl) програма відобразить адресу комірки (або діапазон адрес), а в нижній частині вікна, у полі Значення (Formula result), з'явиться результат обчислень.
Майте на увазі, що під час вибору осередків на робочому аркуші діалогове вікно аргументів можна згорнути; при цьому відображатиметься лише поле Число! Щоб згорнути вікно аргументів, клацніть на кнопці праворуч від поля Число1. Після цього можна виділити діапазон осередків і натиснути на кнопці відновлення вікна (у згорнутому вікні ця кнопка буде єдиною) або натиснути клавішу . Замість згортання можна перемістити це вікно у будь-яке вільне місце екрану.
Якщо на робочому аркуші заповнено багато комірок, клацніть на полі Число2 або натисніть . (Excel відреагує на це, відкривши поле Число3.) У полі Число2 введіть аналогічно другий діапазон осередків, тільки цього разу для згортання вікна клацніть на кнопці поруч із цим полем. У полі результату обчислень з'явиться сума двох діапазонів значень. При бажанні можете виділити кілька діапазонів (Число2, Число3, Число4 і т.д.).
Коли закінчите виділення комірок, клацніть на кнопці ОК, щоб закрити вікно аргументів і помістити функцію в поточну комірку.
Редагування функцій за допомогою майстра
За допомогою майстра можна редагувати формули Ексель із функціями безпосередньо у рядку формул.Виділіть комірку, яка містить таку формулу, і клацніть на кнопці майстри формул (на цій кнопці зображені символи fх, і розташована безпосередньо перед полем введення формули).
Відразу після клацання на кнопці відкриється вікно аргументів функції, де їх можна відредагувати. Для цього виділіть значення в полі аргументу і відредагуйте його (або виділіть інший діапазон осередків).
Врахуйте, що Excel автоматично додає для поточного аргументу комірку (або діапазон), виділену на робочому аркуші. Якщо хочете замінити поточний аргумент, виділіть його і натисніть клавішу , а потім виділіть новий діапазон комірок. (Не забувайте, що будь-якої миті можна згорнути це вікно або перемістити в інше місце екрана, якщо воно перекриває комірки, які потрібно виділити.)
Змінивши функцію, клацніть діалоговому вікні аргументів на кнопці ОК. Відредагована функція з'явиться у поточній комірці.
Формули Ексель: Операції з формулами
Копіювання/вставка формули Ексель
Якщо вам потрібно скопіювати формулу з одного осередку до іншого, достатньо скористатися всім відомою комбінацією клавіш (копіювати) і (вставити). Для цього виділіть потрібну вам комірку, клацнувши по ній курсором миші, натисніть комбінацію клавіш Ctrl + C, при цьому контури комірки будуть виділені пунктирною лінією. Потім виділіть ту комірку, в яку потрібно вставити значення з першої комірки і натисніть комбінацію клавіш Ctrl+V. Весь вміст з першого осередку скопіюється у другий осередок.
Скасування операцій
Перш ніж починати редагувати щойно відкриту робочу книгу, слід дізнатися про функцію скасування операцій та про те, як вона може врятувати випадково видалені дані. Кнопка Скасувати (Undo) панелі швидкого доступу - справжній "хамелеон": вона пристосовується до виконаних вами дій. Наприклад, якщо ви випадково видалили вміст групи осередків, натиснувши клавішу , то екранна підказка цієї кнопки буде говорити “Скасувати очищення (Ctrl+Z)”. Якщо ви перетягнули діапазон осередків в іншу частину робочого аркуша, підказка зміниться на “Скасувати перетягування”.
Для використання цієї команди можна не лише клацати на кнопці панелі швидкого доступу, але й натискати клавіші .
Команда Скасувати панелі швидкого доступу постійно змінюється у відповідь на виконані вами дії та зберігає їхній порядок. Якщо ви забули її натиснути після будь-якої виконаної дії і вже встигли виконати кілька інших дій, то відкрийте прикріплене до неї контекстне меню і знайдіть там саме ту дію, яка потребує скасування. В результаті буде скасовано і цю дію, і всі дії, виконані після нього (вони автоматично виділяються).
Повторення дій
Після виконання команди Скасувати програма активізує кнопку Повернути (Redo), що знаходиться безпосередньо праворуч від неї. Якщо ви видалили вміст клітинки за допомогою клавіші , а потім клацнули на кнопці Скасувати (або натиснули комбінацію клавіш Повернути, буде говорити: “Повернути очищення (Ctrl+Y)”.
Якщо тепер клацнути на кнопці Повернути або натиснути комбінацію клавіш , то Excel повторить щойно скасовану операцію. Насправді все звучить набагато складніше, ніж є насправді. Просто клавіші Скасувати і Повернути служать перемикачами між станом робочої книги до операції та після неї (як включення та вимкнення лампочки).
Що робити, якщо неможливо скасувати операцію
Якщо ви вважаєте, що спокійно можете невпізнанно змінити важливу робочу книгу, то хочу вас попередити: команда скасування операції працює не завжди. Можна скасувати останнє невдале видалення вмісту осередку, переміщення даних або неправильне копіювання, але не можна скасувати збереження робочої книги. (Звичайно, якщо ви зберігали книгу під іншим ім'ям за допомогою команди Зберегти як, вибраної на вкладці Файл, то вихідна книга залишиться незмінною. Однак якщо ви скористалися звичайною командою збереження, то всі внесені зміни стають частиною початкової робочої книги.)
На жаль, Excel не попереджає про крок, після якого дороги назад немає. Ви дізнаєтеся про це, коли буде вже надто пізно. Після того, як буде виконано необоротну дію, екранна підказка кнопки Скасувати замість очікуваного “Скасувати…” повідомить: “Неможливо скасувати”.
Єдиним винятком із цього правила є випадки, коли програма сама попереджає про неможливість скасування операції. Коли ви вибираєте команду, яка за нормальних умов оборотна, але в даний момент (за браком пам'яті або тому, що змінюється занадто велика частина робочого аркуша) програма знає, що скасування зробити не зможе, вона попередить вас і запитає, чи ви хочете все- таки її виконати. Якщо ви погодитеся і виконаєте операцію редагування, то пам'ятайте, що потім доведеться у всьому звинувачувати лише себе. Наприклад, якщо ви виявите, що помилково видалили цілу низку важливих формул (про які забули, тому що в осередках вони не відображаються), то не зможете їх відновити.У такому разі єдине, що залишається, — закрити файл (команда Файл^Закрити) та у відповідь на запит вказати, що зміни зберігати не слід.
Старе добре перетягування
Першою методикою редагування, яку слід освоїти, є перетягування (Drag-and-drop). Як випливає з назви, ця методика передбачає використання покажчика миші, який переносить виділення осередків та залишає його в іншому місці робочого листа. Незважаючи на те, що перетягування в основному використовується для переміщення вмісту осередків у межах робочого листа, його можна застосовувати і для копіювання даних.
Щоб використати перетягування для переміщення діапазону комірок (за один раз можна перемістити лише один діапазон), виконайте наведені нижче дії.
- Виділіть діапазон осередків.
- Помістіть вказівник миші (або палець або стілус під час роботи з сенсорним екраном) над однією з меж виділеного діапазону.
Як тільки покажчик миші набуде вигляду чотириспрямованої стрілки, можна починати перетягування діапазону в інше місце.
Перетягніть виділений діапазон у потрібне місце. Перетягування виконується шляхом натискання головної (зазвичай лівої) кнопки миші та її утримування під час перетягування.
Під час перетягування ви переміщаєте лише контур діапазону, а Excel у екранній підказці інформує про те, якими будуть адреси нового діапазону, якщо ви зараз відпустите кнопку миші.
Перетягуйте контур, доки цей діапазон не збігається з необхідним.
- Відпустіть кнопку миші (або відірвіть палець або стілус від сенсорного екрана).
- Як тільки відпустіть кнопку миші, вміст осередків виділеного діапазону відобразиться у новому місці.
Копіювання шляхом перетягування
Що робити, якщо потрібно скопіювати, а не перемістити виділений діапазон? Припустимо, необхідно почати нову таблицю в іншому місці робочого аркуша, і ви хочете скопіювати вже існуючу з готовою відформатованою назвою та заголовками стовпців. Щоб скопіювати відформатований діапазон заголовків у робочому аркуші прикладу, виконайте наведені нижче дії.
У цьому прикладі цим діапазоном буде А1:Е2.
- Утримуючи клавішу Ctrl>, помістіть покажчик миші межу виділеного фрагмента.
Покажчик миші набуде вигляду чотириспрямованої стрілки з розташованим праворуч знаком "плюс" (до того ж поруч ви побачите екранну підказку). Знак “плюс” свідчить про те, що виконуватиметься не переміщення, а копіювання.
- Перетягніть контур виділеного діапазону в потрібне місце та відпустіть кнопку миші.
Якщо при перетягуванні осередків контур, що переміщається, перекриває вже заповнені осередки, то Excel відкриє вікно попередження з питанням про те, чи хочете ви замінити їх вміст. Щоб уникнути заміни існуючого вмісту та скасувати операцію перетягування, у вікні попередження натисніть кнопку Скасувати; Щоб продовжити операцію, натисніть кнопку ОК або натисніть .
Особливості вставки під час перетягування
Якщо вміст осередків переміщається або копіюється в нове місце, то він повністю заміщає собою записи, що існували раніше, ніби їх ніколи раніше і не існувало.
Щоб вставити діапазон осередків, що перетягується, у вже заповнений без заміщення попереднього вмісту, під час перетягування утримуйте . (При копіюванні доведеться виявити неабияку спритність, щоб одночасно утримувати натиснутими клавіші та .)
Якщо під час перетягування утримувати клавішу , то при переміщенні відображається не контур області, а вертикальний відрізок, що вказує місце потенційної вставки, поряд з екранною підказкою з поточними адресами, куди в результаті буде вставлено вміст комірок. Зверніть увагу, що під час переміщення відрізок намагається прикріпитися до найближчих кордонів стовпців і рядків. Коли ви досягнете межі діапазону, в який має бути вставлений вміст, відпустіть кнопку миші. Excel вставить діапазон осередків, перемістивши вміст, що існував раніше, в найближчі вільні осередки.
При вставці осередків шляхом перетягування можна уявити відрізок як одну з осей області, в яку буде вставлено вміст. Також майте на увазі, що іноді після переміщення діапазону в нове місце робочого аркуша замість даних ви побачите в осередках лише значки ґрат (#######). Справа в тому, що Excel не автоматично розширює нові стовпці, як при форматуванні даних. Позбутися "решіток" можна вручну, розширивши відповідні стовпці, щоб повністю відобразити відформатовані дані. Найпростіше розширювати стовпці подвійним клацанням на правій межі їхнього заголовка.
Але я ж утримував натиснутою клавішу Shift>, як ви й казали…
Перетягування в режимі вставки – одна з найхимерніших функцій Excel. Іноді, коли робиш все правильно, однаково отримуєш попередження Excel про заміщення існуючого вмісту. Якщо ви побачите таке попередження, завжди клацніть на кнопці Скасування! На щастя, завжди можна скористатися командою Вставка, не турбуючись про те, як виглядає форма відрізка, що переміщається.
Автозаповнення формулами
Копіювання методом перетягування (з утримуванням клавіші ) особливо корисно, коли потрібно скопіювати великий діапазон осередків в іншу частину робочого листа. Однак найчастіше потрібно скопіювати лише одну формулу в масу сусідніх осередків, щоб у них виконувався той же тип обчислень (наприклад, підсумовування значень у стовпці). І хоча такий спосіб копіювання формул досить поширений, його неможливо виконати методом перетягування. Натомість використовується функція автозаповнення або послідовність команд Копіювати та Вставити.
Не забувайте про параметр Підсумки (Totals) - панелі інструментів швидкого аналізу. З його допомогою можна миттєво створювати рядок або стовпець підсумків, що знаходиться в нижній або правій частині таблиці даних відповідно. Просто виділіть таблицю як діапазон осередків і клацніть на кнопці Швидкий аналіз (Quick Analysis), а потім на панелі інструментів швидкого аналізу виберіть параметр Підсумки. Якщо клацнути на кнопці Сума (Sum), що знаходиться на початку панелі, буде створена формула, яка підраховує суму по стовпцях і відображає її в новому рядку (у нижній частині таблиці). Якщо ж клацнути на кнопці Сума, що знаходиться в правому кінці панелі інструментів, будуть створені формули Ексель, що підраховують суми по рядках і виводять результат у новому стовпці (у правому кінці таблиці).
Формули Ексель: Висновок
У цій статті ми торкнулися всіх найважливіших аспектів, які можуть знадобитися при створенні формули Ексель. Сподіваємося, що ця стаття допоможе вирішувати будь-яке завдання в таблицях Excel.