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

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

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

У програмі Excelможна робити найскладніші математичні обчислення.

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

максимальні та мінімальні значення;
середні показники;
відсотки;
критерій Стьюдента та багато іншого.

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

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

При цьому необхідність вводити додаткові дані та параметри відпадає.

Як застосовувати прості формули у програмі?

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

Наприклад, У Осередок А1 – число 5, а в комірку В1 – 3. Для того, щоб у комірці А3 з'явилося сумарне значення необхідно ввести формулу:

СУМ(A1; B1).

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

При цьому якщо вибрати осередок С1, то у верхньому рядку видно формулу розрахунку.

Якщо одне з значень змінити, перерахунок відбувається автоматично. Наприклад, при заміні числа 5 в комірці В1 на число 8, змінювати формулу не потрібно, програма сама прорахує остаточне значення.

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

В Excel можна робити будь-які арифметичні операції: віднімання «-», розподіл «/», множення «*» або додавання «+». У формули задається вид операції, координати осередків з вихідними значеннями, функція обчислення.

Будь-яка формула має починатися знаком «=». Якщо спочатку не поставити рівно, то програма не зможе видати необхідне значення, так як дані введені неправильно.

Створення формули в Excel

У наведеному прикладі формула = СУМ(A1; B1) дозволяє визначити суму двох чисел у осередках, які розташовані по горизонталі. Формула починається зі знака "=". Далі задана функція СУМ. Вона показує, що необхідно провести підсумовування заданих значень.

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

СУМ(A1; B1; C1).

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

СУМ(A1: A10). На малюнку арифметична операція виглядатиме так:

Також можна визначити добуток цих чисел. У формулі замість функції СУМ необхідно вибрати функцію ВИРОБ і встановити діапазон осередків.

Комбіновані формули

Діапазон осередків у програмі вказується за допомогою заданих координат першого та останнього значення. У формулі вони поділяються знаком «:». Крім того, Excel має широкі можливості, тому функції можна комбінувати будь-яким способом.

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

Завдання вирішується у програмі за допомогою однієї формули, яка з'єднує кілька функцій і виглядає так:

ЯКЩО(СУМ(А1:С1)<90;СУММ(А1:С1)*1,4;СУММ(А1:С1)*1,5).

У прикладі задіяні дві функції – ЯКЩО та СУМ. Перша має три аргументи:

умова;
вірно;
неправильно.

Завдання – кілька умов. По-перше, сума осередків у діапазоні А1:С1 менше 90. У разі виконання умови, що сума діапазону осередків становитиме 88, то програма виконає вказану дію у 2-му аргументі функції «ЯКЩО», а саме в СУМ(А1:С3 ) * 1,4.

Якщо у разі відбувається перевищення числа 90, то програма обчислить третю функцію – СУММ(А1:С1)*1,5.

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

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

Вбудовані функції програми

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

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

Цю операцію можна зробити за допомогою комбінації на клавіатурі - Shift+F3 ( раніше ми писали про гарячі клавіші Excel). Якщо поставити курсор мишки на будь-яку комірку і натиснути на пункт «вибрати функцію», з'являється майстер функцій.

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

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

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

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

Функція ВВР

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Як приклад розглянемо просту формулу.

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

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

У нашому прикладі введіть =1+1 .

Примітки:

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

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

Натисніть клавішу ВВЕДЕННЯ(Windows) або Return(Mac).

Розглянемо інший варіант простий формули. Введіть =5+2*3 в іншому осередку і натисніть клавішу ВВЕДЕННЯабо Return. Excel перемножить два останніх числа і додасть перше число до результату множення.

Використання автосумування

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

Коли ви натискаєте кнопку Автосума Excel автоматично вводить формулу для підсумовування чисел (у якій використовується функція СУМ).

Примітка:Також у комірці можна ввести ALT+= (Windows) або ALT+ += (Mac), і Excel автоматично вставить функцію СУМ.

Приклад: Щоб скласти цифри за січень у бюджеті «Розваги», оберіть осередок B7, який знаходиться прямо під стовпцем з числами. Потім натисніть кнопку Автосума. У комірці В7 з'являється формула, і Excel виділяє комірки, які підсумовуються.

Щоб відобразити результат (95,94) у клітинці В7, натисніть клавішу ENTER. Формула також відображається у рядку формул у верхній частині вікна Excel.

Примітки:

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

Створивши формулу один раз, її можна копіювати до інших осередків, а не вводити знову і знову. Наприклад, при копіюванні формули з комірки B7 в комірку C7 формула комірки C7 автоматично налаштується під нове розташування і підрахує числа в комірках C3:C6.

Крім того, ви можете використовувати функцію «Автосума» одразу для кількох осередків. Наприклад, можна виділити комірки B7 та C7, натиснути кнопку Автосумаі підсумовувати два стовпці одночасно.

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

Примітка:Щоб ці формули виводили результат, виділіть їх та натисніть клавішу F2, а потім - ВВЕДЕННЯ(Windows) або Return(Mac).

Дані


Формула	Опис	Результат
	Сума значень у осередках A1 та A2
	Різниця значень у осередках A1 і A2
	Приватне від поділу значень у осередках A1 та A2
	Добуток значень у осередках A1 і A2
	Значення в комірці A1 у мірі, зазначеній у комірці A2
Формула	Опис	Результат

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

Вступ

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

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

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

Виконання базових арифметичних операцій

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

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

Наприклад, уявімо, що нам необхідно скласти два числа - «12» і «7». Встановіть курсор миші в будь-яку комірку та надрукуйте наступний вираз: «=12+7». Після закінчення введення натисніть клавішу «Enter» і в комірці з'явиться результат обчислення - «19».

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

Після проведення всіх операцій, зверніть увагу на результат поділу чисел 12 на 7, який вийшов не цілим (1,714286) і містить багато цифр після коми. У більшості випадків така точність не потрібна, та й такі довгі числа будуть лише захаращувати таблицю.

Щоб це виправити, виділіть комірку з числом, у якого необхідно змінити кількість десяткових знаків після коми та на вкладці Головнав групі Числовиберіть команду Зменшити розрядність. Кожне натискання на цю кнопку видаляє один знак.

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

Складання формул

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

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

Щоб порахувати сумарну витрату за січень у осередку B7, можна написати наступний вираз: «=18250+5100+6250+2500+3300» і натиснути Enter, після чого ви побачите результат обчислення. Це приклад застосування найпростішої формули, складання якої нічим не відрізняється від обчислень на калькуляторі. Хіба що знак одно ставиться спочатку висловлювання, а чи не наприкінці.

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

З огляду на це давайте змінимо нашу формулу обчислення сумарних щомісячних витрат.

У комірку B7, введіть знак одно (=) і… замість того, щоб вручну вбивати значення клітини B2, клацніть по ній лівою кнопкою миші. Після цього навколо комірки з'явиться пунктирна рамка виділення, яка показує, що її значення потрапило у формулу. Тепер введіть знак «+» і клацніть на осередку B3. Далі проробіть те саме з осередками B4, B5 і B6, а потім натисніть клавішу ВВЕДЕННЯ (Enter), після чого з'явиться те ж значення суми, що і в першому випадку.

Виділіть знову комірку B7 і подивіться рядок формул. Видно, що замість цифр – значень осередків, у формулі містяться їхні адреси. Це дуже важливий момент, тому що ми тільки-но побудували формулу не з конкретних чисел, а зі значень осередків, які можуть з часом змінюватися. Наприклад, якщо тепер змінити суму витрат на купівлю речей у січні, то вся щомісячна сумарна витрата буде перерахована автоматично. Спробуйте.

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

Встановіть курсор на порожній клітці збоку потрібного рядка (у нашому випадку це H2). Потім натисніть кнопку Сумана закладці Головнав групі Редагування. Тепер повернемося до таблиці і подивимося, що сталося.

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

Тепер подивимося на формулу. Як і раніше, спочатку йде знак рівності, але цього разу за ним слідує функція"СУМ" - заздалегідь визначена формула, яка виконає складання значень зазначених осередків. Відразу за функцією йдуть дужки розташовані навколо адрес клітин, значення яких потрібно підсумувати, звані аргументом формули. Зверніть увагу, що у формулі не вказані всі адреси комірок, а лише першої та останньої. Двокрапка між ними означає, що вказано діапазонклітин від B2 до G2

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

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

Автозаповнення

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

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

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

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

Звичайно, Excel не завжди правильно «розуміє», як потрібно заповнити наступні клітини, оскільки послідовності можуть бути досить різноманітними. Уявімо, що нам необхідно заповнити рядок парними числовими значеннями: 2, 4, 6, 8 і таке інше. Якщо ми введемо число "2" і спробуємо перемістити маркер автозаповнення вправо, то виявиться, що програма пропонує, як у наступну, так і в інші осередки знову вставити значення "2".

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

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

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

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

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

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

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

Додавання рядків, стовпців та об'єднання осередків

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

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

Клацніть правою кнопкою миші в будь-якому осередку другого рядка і у меню виберіть команду Вставити..., а потім у вікні - Додати рядок.

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

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

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

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

На стрічці для операцій додавання можна використовувати кнопку Вставити, розташовану у групі Осередкина закладці Головна, а для видалення, однойменну команду в тій же групі.

У нашому випадку нам необхідно вставити п'ять нових рядків у верхню частину таблиці одразу після шапки. Для цього можна повторити операцію додавання кілька разів, а можна, виконавши її один раз використовувати клавішу «F4», яка повторює останню операцію.

У результаті після вставки п'яти горизонтальних рядів у верхню частину таблиці, наводимо її до наступного виду:

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

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

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

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

Після виконання команди всі виділені комірки в рядку об'єднаються в одну велику комірку.

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

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

Висновок

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

Тепер переведемо ці розрахунки до формул зрозумілих Excel. Для січня (комірки B14) формула дуже проста і буде виглядати так: = B5-B12. А ось для осередку С14 (лютий) вираз можна записати двома різними способами: "= (B5-B12) + (C5-C12)" або "= B14 + C5-C12". У першому випадку ми знову проводимо розрахунок балансу попереднього місяця і потім додаємо до нього баланс поточного, а в другому формулу включається вже розрахований результат попереднього місяця. Звичайно, використання другого варіанту для побудови формули в нашому випадку набагато краще. Адже якщо слідувати логіці першого варіанту, то у виразі для березневого розрахунку фігуруватиме вже 6 адрес осередків, у квітні - 8, у травні - 10 і так далі, а при використанні другого варіанта їх завжди буде три.

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

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

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

А ось і наша підсумкова таблиця з виконаними розрахунками:

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

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

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

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

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

Функція ВВР

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Вітаю!

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

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

Можливо, що прочитай подібну статтю років 15-17 тому, я б сам набагато швидше почав користуватися Excel (і заощадив би купу свого часу для вирішення "простих" (прим.: як я зараз розумію)завдань)...

Примітка: всі скріншоти представлені нижче з програми Excel 2016 (як найновішої на сьогоднішній день).

Багато користувачів-початківців, після запуску Excel - ставлять одне дивне питання: "ну і де тут таблиця?". Тим часом, всі клітини, які ви бачите після запуску програми - це і є одна велика таблиця!

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

ліворуч: в комірці (A1) написано просте число "6". Зверніть увагу, коли ви вибираєте цей осередок, то рядку формули (Fx) показується просто число " 6 " .
справа : в комірці (C1) на вигляд теж просте число "6", але якщо вибрати цей комірку, то ви побачите формулу "=3+3" - Це і є важлива фішка в Excel!

Просто число (ліворуч) та порахована формула (праворуч)

Суть у тому, що Excel може вважати як калькулятор, якщо вибрати якусь комірку, а потім написати формулу, наприклад "=3+5+8" (без лапок). Результат вам писати не потрібно - Excel порахує його сам і відобразить у комірці (як у комірці C1 у прикладі вище)!

Але писати в формули і складати можна не просто числа, а й числа, які вже пораховані в інших осередках. На скріншоті нижче в комірці A1 та B1 числа 5 та 6 відповідно. У осередку D1 хочу отримати їх суму - можна написати формулу двома способами:

перший: "=5+6" (не зовсім зручно, уявіть, що в осередку A1 - у нас число теж вважається за якоюсь іншою формулою і воно змінюється. Не будете ж ви підставляти замість 5 щоразу заново число?!);
другий: "=A1+B1" - а ось це ідеальний варіант, просто складаємо значення осередків A1 і B1 (незважаючи навіть які числа в них!)

Додавання осередків, у яких вже є числа

Розповсюдження формули на інші осередки

У прикладі вище ми склали два числа у стовпці A та B у першому рядку. Але рядків у нас 6, і найчастіше в реальних завданнях скласти числа потрібно в кожному рядку! Щоб це зробити, можна:

у рядку 2 написати формулу "=A2+B2", у рядку 3 - "=A3+B3" і т.д. (Це довго і втомлює, цей варіант ніколи не використовують);
вибрати осередок D1 (в якому вже є формула), потім підвести покажчик мишки до правого куточка осередку, щоб з'явився чорний хрестик (див. скрін нижче). Потім затиснути ліву кнопку та розтягнути формулу на весь стовпець. Зручно та швидко! (Примітка: так само можна використовувати для формул комбінації Ctrl+C та Ctrl+V (скопіювати та вставити відповідно)).

До речі, зверніть увагу, що Excel сам підставив формули в кожен рядок. Тобто, якщо зараз ви оберете комірку, скажімо, D2 - то побачите формулу "=A2+B2" (Тобто Excel автоматично підставляє формули і відразу ж видає результат) .

Як задати константу (комірку, яка не змінюватиметься при копіюванні формули)

Досить часто потрібно у формулах (коли ви їх копіюєте), щоб якесь значення не змінювалося. Скажімо просте завдання: перевести ціни у доларах у рублі. Вартість рубля задається в одному осередку, в моєму прикладі нижче - це G2.

Далі в комірці E2 пишеться формула = D2 * G2 і отримуємо результат. Тільки якщо розтягнути формулу, як ми це робили раніше, за іншими рядках результату ми побачимо, т.к. Excel у рядок 3 поставить формулу "D3*G3", у 4-й рядок: "D4*G4" і т.д. Потрібно, щоб G2 всюди залишалася G2...

Щоб це зробити - просто змініть комірку E2 - формула матиме вигляд "=D2*$G$2". Тобто. значок долара $ - дозволяє задавати комірку, яка не буде змінюватися, коли ви копіюватимете формулу (тобто отримуємо константу, приклад нижче).

Як порахувати суму (формули СУМ і СУМЕСЛІМН)

Можна, звичайно, складати формули в ручному режимі, друкуючи = A1 + B1 + C1 і т.п. Але в Excel є швидші та зручніші інструменти.

Один із найпростіших способів скласти всі виділені осередки – це використовувати опцію автосуми (Excel сам напише формулу і вставити її в комірку).

спочатку виділяємо осередки (див. скрін нижче);
далі відкриваємо розділ "Формули";
наступний крок тиснемо кнопку "Автосума". Під виділеними вами осередками з'явиться результат із додавання;
якщо виділити комірку з результатом (у моєму випадку - це комірка E8) - ви побачите формулу "=СУМ(E2:E7)" .
таким чином, написавши формулу "=СУМ(xx)", де замість XXпоставити (або виділити) будь-які осередки, можна вважати найрізноманітніші діапазони осередків, стовпців, рядків...

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

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

"=Сумісний(F2:F7 ;A2:A7 ;"Саша") " - (прим.: зверніть увагу на лапки для умови - вони повинні бути як на скріні нижче, а не як у мене зараз написано на блозі). Також зверніть увагу, що Excel при вбиванні початку формули (наприклад "СУММ..."), сам підказує і підставляє можливі варіанти - а формул в Excel"e сотні!;
F2:F7 - це діапазон, яким складаються (сумуватися) числа з осередків;
A2:A7 - це стовпчик, за яким перевірятиметься наша умова;
"Саша" - це умова, ті рядки, в яких у стовпці A буде "Саша" будуть складені (зверніть увагу на показовий скріншот нижче).

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

Як порахувати кількість рядків (з однією, двома та більш умовою)

Досить типове завдання: порахувати не суму в осередках, а кількість рядків, що задовольняють будь-якій умові. Ну, наприклад, скільки разів ім'я Саша зустрічається в таблиці нижче (див. скріншот). Очевидно, що 2 рази (але це тому, що таблиця надто маленька і взята як наочний приклад). А як це вважати формулою? Формула:

"=РАХУНКИ(A2:A7 ;A2 )- де:

A2:A7- діапазон, в якому перевірятимуться та рахуватимуться рядки;
A2- Задається умова (зверніть увагу, що можна було написати умову виду "Саша", а можна просто вказати комірку).

Результат показаний у правій частині нижче.

Тепер уявіть більш розширене завдання: потрібно порахувати рядки, де зустрічається ім'я "Саша", і де в стовпці І - стоятиме цифра "6". Забігаючи вперед, скажу, що такий рядок лише один (скрин з прикладом нижче).

Формула матиме вигляд:

= РАХУНОК (A2:A7 ;A2 ;B2:B7 ;"6") (прим.: зверніть увагу на лапки - вони повинні бути як на скріні нижче, а не як у мене), де:

A2:A7; A2- перший діапазон та умова для пошуку (аналогічно прикладу вище);

B2:B7; "6"- другий діапазон та умова для пошуку (зверніть увагу, що умову можна задавати по-різному: або вказувати комірку, або просто написано в лапках текст/число).

Як порахувати відсоток від суми

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

Найпростіший спосіб, у якому просто неможливо заплутатися – це використовувати правило "квадрату", або пропорції. Вся суть наведена на скрині нижче: якщо у вас є загальна сума, припустимо в моєму прикладі це число 3060 - комірка F8 (тобто це 100% прибуток, і якусь її частину зробив "Саша", потрібно знайти якусь... ).

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

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