Найпопулярніші формули для роботи в програмі Excel

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

Що таке Excel і навіщо він потрібен?

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

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

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

Для того, щоб застосовувати прості формули по додаванню, відніманню, множенню і діленню, слід використовувати знак рівності (=), який повинен стояти першим, а далі вже слід задати константи, тобто числові значення, а також оператори обчислення, плюс ( +), мінус (-), зірочка (*) і коса риска (/). Залежно від того, яку дію вам треба виконати: додати, відняти, помножити, розділити - проставляєте за цією формулою потрібні знаки і числа, і програма все автоматично рахує.

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

• Виділяємо бажану ячейку, куди і будемо вводити формулу певним чином.

• Потім ставимо знак рівності (=), а після нього - константи і оператори, але не більше 8192 знаків, які плануємо використовувати для обчислення.

• Наприклад, вводимо найпростіше, коли потрібно число додати до числа =2+2.

• Далі слід натиснути ВВЕДЕННЯ (Windows) або Return (Mac). І відразу ж отримаємо результат.

Слід пам'ятати, що замість введення констант в формі можна обирати ячейки, які вже мають потрібні значення, і далі вже вводити оператори між ними.

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

Для прикладу можна ввести іншу просту формулу, яка буде включати в себе не тільки поєднання, але й множення =4+2* 2. За тим же принципом прописуємо формулу, тільки міняємо знак, якщо потрібно відняти, розділити або дізнатися різницю і т.д.

Використання АВТОСУММИ

Для того, щоб швидко використовувати формулу "Автосума" слід вибрати ячейки (весь стовпець), де вказані потрібні для підсумовування числа. Далі слід натиснути кнопочку "Автосумма", яка знаходиться на вкладці "Головна". Потім натискаємо ВВЕДЕННЯ (Windows) або Return (Mac).

Після натискання "Автосумма", Ексель автоматично проводить формулу з метою підсумувати числа - СУМ.

Крім того, в ячейці є можливість ще одного варіанту отримання автосуми, шляхом введення ALT + = (Windows) або ALT + КОМАНДА + = (Mac). У цьому випадку Excel автоматично вирахує функцію СУММ.

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

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

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

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

Як об'єднати або розбити ячейки в Ексель?

Якщо вам треба об'єднати або розбити ячейки в Ексель, то зробити це досить просто. Для цього потрібно:

• Вибрати ячейки, які хочете об'єднати (вони повинні перебувати поруч);

• Далі необхідно вибрати на вкладці "Робота з таблицями" - "Макет" - "Об'єднання" - "Об'єднати ячейки".

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

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

• Далі проходимо по вкладках "Робота з таблицями" - "Макет" - "Об'єднання" - "Розділити клітинки".

• Вибираємо потрібну нам кількість нових ячейок по горизонталі і/або вертикалі (число стовпців і число рядків).

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

Як в екселі обчислити середнє значення

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

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

• Вибираємо вкладку "Головна" - "Редагування" - "Автосумма" - "Середнє" (AVERAGE) і натиснути Enter.

Формула СЦЕПИТЬ в ексель

Щоб виконати цю формулу, треба прописати наступне:

=СЦЕПИТЬ(ПРОПИСН(ЛЕВСИМВ(A1));ПРАВСИМВ(A1;(ДЛСТР(A1)-1))), в якій буде А1 - потрібна нам ячейка, де і планується заміна. Зверніть увагу на те, що написання повинно бути виключно правильним, інакше формула не дасть належного результату.

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

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

• Виділяємо всі потрібні нам ячейки, які ми змінювали;

• Натискаємо одночасно клавіші Ctrl + C;

• Після цього (не змінюючи положення виділених ячейок), одночасно натискаємо Ctrl + V;

• Далі в меню, яке випало "Параметри вставки" вибираємо "Тільки значення";

• Отримуємо тільки текстові значення, які не містять формул.

Як створити формулу в Excel з вибором певних ячейок

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

Для цього треба зробити такі кроки:

• Виділити ячейку, в яку бажаємо помістити безпосередньо формулу;

• Прописати знак рівності (=);

• Клікнути на клітинку, яку ми плануємо позначити в першій формулі. Адреса обраної ячейки пропишеться у формулі, а сама ячейка позначиться пунктиром і змінить контурний колір на блакитний;

• Далі слід вписати математичний оператор, який хочемо використовувати, наприклад, множення (*).

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

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

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

Як порахувати відсоток в екселі?

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

Для цієї мети вам потрібно буде поділити 20 000 грн на 125 000 грн. Для цього необхідно використовувати таку формулу C2: = B2 / A2 в якій С2 - результати, що нам потрібно отримати, В2 - наші 20000 грн, А2 - 125000 грн. В результаті ми отримуємо значення 0,16. Такий підсумок без відсотків бачимо через те, що ця група не налаштована на показ процентних даних.

Для того, щоб 0,16 Ексель почав показувати як відсотки (перед тим обов'язково слід видалити "0"), слід перейти на вкладку "Головна", а потім вибрати "%". І після цього вже з'явиться коректне відображення відсотків у звичному нам вигляді.

Формули масиву в Excel

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

Робимо наступні кроки без використання формули масиву:

Для початку слід розрахувати по кожному потрібному рядку розвиток;

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

Тепер використовуємо більш складну формулу масиву.

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

Вчинаємо такі дії:

• Розраховуємо по кожному потрібному рядку дані;

• Щоб визначити максимальне значення серед всіх даних, слід додати функцію МАКС (MAX) і замінити C2 на C2: C6, а B2 на B2: B6 (або інші потрібні нам осередки, де знаходяться потрібні нам дані).

• Щоб завершити редагування цієї формули слід натиснути комбінацію клавіш Ctrl + Shift + Enter.

Слід звернути увагу, що діапазон даних (той самий масив констант) додається в пам'ять програми Ексель, але не в діапазоні ячейок. Такий масив констант можна, наприклад, позначити в певному вигляді: {19;33;63;48;13} (підставляємо потрібні нам значення).

Функція МАКС (MAX) звертається до цього масиву як до аргументу і дає результат використовувати цей масив як аргумент і повертає результат.

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

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

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

Функція "ІНДЕКС" в екселі

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

Функція "ІНДЕКС" в екселі: як її використовувати? Є кілька способів і варіантів цієї функції. Розглянемо деякі з них.

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

=ИНДЕКС(диапазон_столбец; порядковый_номер_ячейки)

Інший варіант теж досить популярний і про нього знає більшість користувачів програми Ексель. При такому застосуванні зазвичай використовують функцію ІНДЕКС плюс ПОИСКПОЗ (MATCH). Друга функція здатна видавати номери необхідних значень в заданому діапазоні. Виглядає синтаксис так:

=ИНДЕКС(диапазон_столбец; ПОИСКПОЗ (порядковый_номер_ячейки))

Як витягувати дані з двомірного діапазону? У разі, якщо наш діапазон стає двовимірним (містить в собі кілька рядків і стовпців), тоді функцію слід прописувати дещо в іншому форматі:

=ИНДЕКС(диапазон; номер_строки; номер_столбца)

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

Визначення вибірки в програмі Excel за умовою

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

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

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

Як зробити, щоб порядковий номер в Ексель ставився автоматично?

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

Як створити або змінити посилання на ячейку?

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

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

• Позначаємо інформацію з однієї або декількох ячейок в робочому аркуші, які розташовуються поруч;

• Інформацію з ячейок, яка знаходиться в різних частинах робочого листа;

• Інформацію з інших листів цього ж файлу.

• Вибираємо ячейку, куди будемо вводити формулу.

• У місці "Рядок формул" вводимо знак рівності (=)

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

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