Дізнаємося, як у «Екселі» порахувати суму. Різні способи як порахувати суму в excel

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

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

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

Нащо з легенько розминки. Якщо вам потрібно порахувати суму якихось чисел, то найпростіше це зробити звичайний приклад. Для цього встаньте на будь-яку комірку та напишіть знак рівності (=), після чого починайте складати потрібні цифри (=15+6+94+3-10+2). Останнім штрихом вам потрібно буде махнути клавішу Enterтоді весь цей математичний прикладблискавично перетворюється на вирішену відповідь.

Складання кожного осередку

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

Підсумовування стовпця

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

Виділіть стовпець або його частину, після чого переходьте у вкладку «Формули» та вибирайте там пункт «Автосума». Після цієї простої маніпуляції ви побачите суму всіх виділених вами чисел. Вона автоматично стане в найближчий вільний осередок у вашому стовпці.

Насправді, навіть необов'язково, щоб це був стовпець. Це може бути як рядок, так і окремі осередки (можна виділити за допомогою затиснутої клавіші CTRL). Тільки місце появи результату буде інше.

Використання формули за допомогою параметра "СУМ"

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

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

Підсумовування з різних листів

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

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

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

З повагою, Дмитро Костін.

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

Як написати формулу в Excel

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

1. Кожна починається зі знака "=".
2. Брати участь у обчисленнях можуть значення з осередків та функції.
3. Як звичні нам математичні знаки операцій використовуються оператори.
4. При вставці запису в комірці за промовчанням відображається результат обчислень.
5. Подивитися конструкцію можна у рядку над таблицею.

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

Отже, як створити та вставити формулу в Excel? Дійте за наступним алгоритмом:

Позначення Значення

Додавання
- Віднімання
/ Розподіл
* Множення

Якщо вам необхідно вказати число, а не адресу комірки, введіть його з клавіатури. Щоб вказати негативний знак у формулі Excel, натисніть "-".

Як вводити та скопіювати формули в Excel

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


Натисніть ліву кнопку та тягніть.


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


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


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


Ціна з ПДВ вираховується як ціна*(1+ПДВ). Введемо послідовність у першу комірку.


Спробуємо копіювати запис.


Результат вийшов дивним.


Перевіримо вміст у другому осередку.


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


Натисніть F4. Адреса буде розведена знаком «$». Це і є ознака абсолютно осередку.


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

Використання функцій для обчислень

Excel пропонує велику кількість функцій, які розбиті за категоріями. Переглянути повний перелік можна, натиснувши кнопку Fx біля рядка формул або відкривши розділ «Формули» на панелі інструментів.


Розкажемо про деякі функції.

Як задати формули «Якщо» в Excel

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


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


Як і при вставці будь-якої функції, відкриється вікно заповнення аргументів.


Вкажемо умову. Для цього необхідно клацнути в перший рядок і вибрати перший осередок «Продано». Далі поставимо знак «>» та вкажемо число 4.


У другому рядку напишемо "Закупити". Цей напис з'являтиметься для тих товарів, які були розпродані. Останній рядок можна залишити порожнім, тому що у нас немає дій, якщо умова хибна.


Натисніть кнопку ОК і скопіюйте запис для всього стовпця.


Щоб у осередку не виводилося «БРЕХНЯ» знову відкриємо функцію і виправимо її. Поставте покажчик на першу комірку та натисніть Fx біля рядка формул. Вставте курсор на третій рядок і поставте пробіл у лапках.


Потім ОК знову скопіюйте.


Тепер бачимо, який товар слід закупити.

Формула тексту в Excel

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


У першу комірку введемо функцію (кнопка «Текстові» у розділі «Формули»).


У вікні аргументів зазначимо посилання на комірку підсумкової суми та встановимо формат «#руб.».


Натиснемо ОК та скопіюємо.


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

«ЗНАЧ» означає, що обчислення не можуть бути зроблені.
Приклади форматів можна бачити на скріншоті.

Формула дати в Excel

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

Поставте покажчик на першу комірку четвертого стовпця та виберіть функцію зі списку «Дата та час».

Розставте відповідні адреси осередків і натисніть ОК.


Скопіюйте запис.

Автосума в Excel

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


Перейдіть на панель «Формули» та натисніть «Автосума».


Excel автоматично виділить найближчий числовий діапазон.


Ви можете виділити інший діапазон. В данному прикладі Excelвсе зробив правильно. Натисніть кнопку ОК. Зверніть увагу на вміст комірки. Функція СУММ підставилася автоматично.


При вставці діапазону вказується адреса першого осередку, двокрапка та адреса останнього осередку. «:» означає «Взяти всі осередки між першим і останнім. Якщо вам треба перерахувати кілька осередків, розділіть їх адреси крапкою з комою:
СУМ (F5; F8; F11)

Робота в Excel із формулами: приклад

Ми розповіли, як зробити формулу в Excel. Це ті знання, які можуть стати у нагоді навіть у побуті. Ви можете вести свій особистий бюджетта контролювати витрати.


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

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

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

Чудового Вам дня!

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

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

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

Базові знання про відсотки

Термін Відсоток(per cent) прийшов з Латині (per centum) і перекладався спочатку як З СОТНІ. У школі Ви вивчали, що відсоток – це якась частина зі 100 часток цілого. Відсоток розраховується шляхом розподілу, де в чисельнику дробу знаходиться шукана частина, а в знаменнику - ціле, і далі результат множиться на 100.

Основна формула для розрахунку відсотків виглядає так:

(Частина/Ціле)*100=Відсотки

Приклад: Ви мали 20 яблук, з них 5 Ви роздали своїм друзям. Яку частину своїх яблук у відсотках ви віддали? Здійснивши нескладні обчислення, отримаємо відповідь:

(5/20)*100 = 25%

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

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

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

Основна формула розрахунку відсотка в Excel

Основна формула розрахунку відсотка в Excel виглядає так:

Частина/Ціле = Відсоток

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

А тепер подивимося, як розрахунок відсотків у Excel може допомогти у реальній роботі з даними. Допустимо, у стовпець В у Вас записано кілька замовлених виробів (Ordered), а в стовпець С внесено дані про кількість доставлених виробів (Delivered). Щоб обчислити, яка частка замовлень вже доставлена, виконаємо такі дії:

• Запишіть формулу =C2/B2у комірці D2 і скопіюйте її вниз на стільки рядків, скільки це необхідно, скориставшись маркером автозаповнення.
• Натисніть команду Percent Style(Відсотковий формат), щоб відобразити результати поділу у форматі відсотків. Вона знаходиться на вкладці Home(Головна) у групі команд Номер(Кількість).
• За необхідності налаштуйте кількість знаків, що відображаються праворуч від коми.
• Готово!

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

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

Розрахунок відсотка від загальної суми в Excel

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

Приклад 1. Загальна сума порахована внизу таблиці у конкретному осередку

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

Наприклад, якщо у Вас записані якісь значення в стовпці B, а їх результат у осередку B10, то формула обчислення відсотків буде така:

Підказка:Є два способи зробити посилання на комірку в знаменнику абсолютним: або ввести знак $ вручну, або виділити у рядку формул потрібне посилання на комірку та натиснути клавішу F4.

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

Приклад 2. Частини загальної суми перебувають у кількох рядках

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

У цьому випадку використовуємо функцію SUMIF(СУМІСЛИ). Ця функція дозволяє підсумовувати ті значення, які відповідають якомусь певному критерію, у разі – це заданий продукт. Отриманий результат використовуємо для обчислення відсотка загальної суми.

SUMIF(range,criteria,sum_range)/total
=СУМІСЛІ(діапазон;критерій;діапазон_підсумовування)/загальна сума

У прикладі стовпець A містить назви продуктів (Product) – це діапазон. Стовпець B містить дані про кількість (Ordered) – це діапазон_підсумовування. У комірку E1 вводимо наш критерій- Назва продукту, яким необхідно розрахувати відсоток. Загальна сума по всіх продуктах порахована в осередку B10. Робоча формула виглядатиме так:

SUMIF(A2:A9,E1,B2:B9)/$B$10
=СУМІСЛІ(A2:A9;E1;B2:B9)/$B$10

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

SUMIF(A2:A9,"черени",B2:B9)/$B$10
=СУМІСЛІ(A2:A9;"cherries";B2:B9)/$B$10

Якщо необхідно обчислити, яку частину від загальної суми становлять кілька різних продуктів, то можна підсумувати результати кожного з них, а потім розділити на загальну суму. Наприклад, так буде виглядати формула, якщо ми хочемо обчислити результат для cherriesі apples:

=(SUMIF(A2:A9,"cherries",B2:B9)+SUMIF(A2:A9,"apples",B2:B9))/$B$10
=(СУМІСЛІ(A2:A9;"cherries";B2:B9)+СУМІСЛІ(A2:A9;"apples";B2:B9))/$B$10

Як розрахувати зміну у відсотках в Excel

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

Формула Excel, що обчислює зміну у відсотках (приріст/зменшення)

(B-A)/A = Зміна у відсотках

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

Приклад:Вчора у вас було 80 яблук, а сьогодні у вас є 100 яблук. Це означає, що сьогодні у вас на 20 яблук більше, ніж було вчора, тобто ваш результат – приріст на 25%. Якщо ж учора яблук було 100, а сьогодні 80 – це зменшення на 20%.

Отже, наша формула в Excel працюватиме за такою схемою:

(Нове значення – Старе значення) / Старе значення = Зміна у відсотках

А тепер давайте подивимося, як ця формула працює в Excel на практиці.

Приклад 1. Розрахунок зміни у відсотках між двома стовпцями

Припустимо, що у стовпці B записані ціни минулого місяця (Last month), а стовпці C – ціни актуальні цього місяця (This month). У стовпець D внесемо таку формулу, щоб обчислити зміну ціни від минулого місяця до поточного у відсотках.

Ця формула обчислює відсоткову зміну (приріст або зменшення) ціни цього місяця (стовпець C) порівняно з попереднім (стовпець B).

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

Приклад 2. Розрахунок зміни у відсотках між рядками

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

Тут C2 це перше значення, а C3 це наступне значення.

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

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

Наприклад, ось так виглядатиме формула для розрахунку процентної зміни для кожного місяця порівняно з показником Січня(January):

Коли Ви копіюватимете свою формулу з одного осередку у всі інші, абсолютне посилання залишиться незмінним, у той час як відносне посилання (C3) буде змінюватися на C4, C5, C6 і так далі.

Розрахунок значення та загальної суми за відомим відсотком

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

Приклад 1. Розрахунок значення за відомим відсотком та загальною сумою

Припустимо, Ви купуєте новий комп'ютерза $950, але до цієї ціни потрібно додати ще ПДВ у розмірі 11%. Запитання – скільки Вам потрібно доплатити? Іншими словами, 11% від зазначеної вартості – це скільки у валюті?

Нам допоможе така формула:

Total * Percentage = Amount
Загальна сума * Відсотки = Значення

Припустимо, що Загальна сума(Total) записана в комірці A2, а Відсотки(Percent) – у комірці B2. У цьому випадку наша формула виглядатиме досить просто =A2*B2і дасть результат $104.50 :

Важливо запам'ятати:Коли Ви вручну вводите числове значення в комірку таблиці та після нього знак %, Excel розуміє це як соті частки від введеного числа. Тобто, якщо з клавіатури ввести 11%, то фактично в осередку зберігатиметься значення 0,11 – саме це значення Excelбуде використовувати, роблячи обчислення.

Іншими словами, формула =A2*11%еквівалентна формулі =A2*0,11. Тобто. у формулах Ви можете використовувати або десяткові значення, або знаки відсотка – як Вам зручніше.

Приклад 2. Розрахунок загальної суми за відомим відсотком та значенням

Припустимо, Ваш друг запропонував купити його старий комп'ютер за $400 і сказав, що це на 30% дешевше за його повну вартість. Ви хочете дізнатися, скільки ж коштував цей комп'ютер спочатку?

Оскільки 30% – це зменшення ціни, то насамперед заберемо це значення від 100%, щоб обчислити якусь частку від первісної ціни Вам потрібно заплатити:

Тепер нам потрібна формула, яка обчислить первісну ціну, тобто знайде те число, 70% якого дорівнює $400. Формула виглядатиме так:

Amount/Percentage = Total
Значення/відсоток = Загальна сума

Для вирішення нашого завдання ми отримаємо таку форму:

A2/B2 або =A2/0,7 або =A2/70%

Як збільшити/зменшити значення на відсоток

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

Щоб збільшити значення на відсоток, використовуйте таку формулу:

Значення*(1+%)

Наприклад, формула =A1*(1+20%)бере значення, що міститься в осередку A1, і збільшує його на 20%.

Щоб зменшити значення на відсоток, використовуйте таку формулу:

Значення * (1-%)

Наприклад, формула = A1 * (1-20%)бере значення, що міститься в осередку A1, і зменшує його на 20%.

У нашому прикладі, якщо A2 – це Ваші поточні витрати, а B2 – це відсоток, на який Ви хочете збільшити або зменшити їх значення, то в комірку C2 потрібно записати таку формулу:

Збільшити на процент: =A2*(1+B2)
Зменшити на процент: =A2*(1-B2)

Як збільшити/зменшити на відсоток усі значення у стовпці

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

Нам потрібно всього 5 кроків для вирішення цього завдання:

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

В результаті значення у стовпці B збільшаться на 20%.

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

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

На сьогодні все, дякую за увагу!

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

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

Спосіб 1: арифметична формула

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

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

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

Спосіб 2: автосума

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

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

Спосіб 3: функція СУМ

Подолати недоліки двох вищеописаних способів може варіант із застосуванням вбудованої функції Excelпід назвою СУМ.

Оператор СУМналежить до групи математичних функцій Ексель. Його завданням є підсумовування чисел. Синтаксис цієї функції має такий вигляд:

СУМ(число1; число2; ...)

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

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

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

Спосіб 4: масове підсумовування значень у рядках

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

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

Добрий день.

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

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

Тож почнемо…

1. Основні операції та ази. Навчання основ Excel.

Усі дії у статті будуть відображатися в Excel версії 2007р.

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

Формула має починатися зі знака "=". Це обов'язкова умова. Далі ви пишіть те, що вам потрібно порахувати: наприклад, = 2 +3 (без лапок) і натискаєте по клавіші Enter - в результаті ви побачите, що в комірці з'явився результат "5". Скріншот нижче.

Важливо! Попри те що, що у осередку А1 написано число «5» - воно вважається за формулою («=2+3»). Якщо в сусідньому осередку просто текстом написати «5» - то при наведенні курсору на цей осередок - у редакторі формули (рядок зверху, Fx) - Ви побачите просте число «5».

А тепер уявіть, що в комірку ви можете писати не просто значення 2+3, а номери осередків, значення яких потрібно скласти. Допустимо так «=B2+C2».

Природно, що у B2 і C2 мають бути якісь числа, інакше Excel покаже нам у комірці A1 результат рівний 0.

І ще одне важливе зауваження…

Коли ви копіюєте комірку, в якій є формула, наприклад A1 - і вставляєте її в іншу комірку, то копіюється не значення «5», а сама формула!

Причому формула зміниться прямо-пропорційно: тобто. якщо A1 скопіювати в A2 - то формула в комірці A2 дорівнюватиме = B3 + C3. Excel сам змінює автоматично вашу формулу: якщо A1=B2+C2, логічно, що A2=B3+C3 (всі цифри збільшилися на 1).

Результат, до речі, A2=0, т.к. осередки B3 і С3 не задані, отже, рівні 0.

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

Якщо ви не хочете, щоб B2 та С2 змінювалисяпри копіюванні і завжди були прив'язані до цих осередків, просто додайте до них значок «$». Приклад нижче.

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

2. Додавання значень у рядках (формула СУМ і СУМЕСЛІМН)

Можна, звичайно, кожен осередок складати, роблячи формулу A1+A2+A3 і т.п. Але щоб так не мучиться, є в Excel спеціальна формула, яка складе всі значення в комірках, які ви виділите!

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

Для цього переходимо в комірку, в якій показуватиметься результат і пишемо формулу: «=СУМ(C2: C5)». Скріншот нижче.

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

2.1. Додавання з умовою (з умовами)

А тепер уявімо, що ми маємо певні умови, тобто. скласти треба не всі значення в осередках (Кг, на складі), а лише певні, скажімо, з ціною (1 кг) менше 100.

Для цього є чудова формула. СУМІСЛІМН«. Відразу приклад, а потім пояснення кожного символу у формулі.

=СУМІСЛІМН(C2:C5 ;B2:B5 ;«<100» ) , де:

C2:C5 - та колонка (ті комірки), які сумуватимуться;

B2:B5- колонка, за якою перевірятиметься умова (тобто ціна, наприклад, менше 100);

«<100» - сама умова, зверніть увагу, що умова пишеться в лапках.

Нічого складного в цій формулі немає, головне дотримуватися пропорційності: C2: C5; B2: B5 - правильно; C2: C6; B2: B5 - неправильно. Тобто. діапазон підсумовування та діапазон умов мають бути пропорційними, інакше формула поверне помилку.

Важливо! Умов суми може бути багато, тобто. можна перевіряти не по 1-й колонці, а відразу по 10, задавши багато умов.

3. Підрахунок кількості рядків, що задовольняють умовам (формула РАХУНОК)

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

Тож почнемо.

Для підрахунку товарів у потрібній комірці написали наступну формулу (див. вище):

=РАХУНОКЛІМН(B2:B5 ;«>90» ), де:

B2:B5- діапазон, за яким перевірятимуть, за заданою нами умовою;

«>90»-сама умова, полягає в лапки.

Тепер спробуємо трохи ускладнити наш приклад і додамо рахунок ще за однією умовою: з ціною більше 90+ кількість на складі менше 20 кг.

Формула набуває вигляду:

=РАХУНОКЛІМН(B2:B6;»>90″;C2:C6 ;«<20» )

Тут все залишилося таким самим, крім ще однієї умови ( C2: C6;<20″ ). До речі, таких умов може бути дуже багато!

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

4. Пошук та підстановка значень з однієї таблиці до іншої (формула ВПР)

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

Для такого завдання використовується формула ВВР. Свого часу сам «мудрив» із логічними формулами «ЯКЩО» поки не зустрів цю чудову штуку!

Тож почнемо…

Ось наш приклад + нова таблиця із цінниками. Зараз нам потрібно автоматично підставити нові цінники з нової таблиці до старої (нові цінники червоні).

Ставимо курсор у комірку B2 - тобто. в першу комірку, де нам потрібно змінити цінник автоматично. Далі пишемо формулу, як на скріншоті нижче (після скріншота буде докладне пояснення до неї).

=ВПР(A2 ;$D$2:$E$5 ;2 ), де

A2- то значення, яке ми шукатимемо, щоб взяти новий цінник. У нашому випадку шукаємо у новій таблиці слово «яблука».

$D$2:$E$5- Виділяємо повністю нашу нову таблицю (D2: E5, виділення йде від верхнього лівого кута до правого нижнього по діагоналі), тобто. там, де буде здійснюватися пошук. Знак «$» у цій формулі необхідний для того, щоб при копіюванні цієї формули до інших осередків - D2:E5 не змінювалися!

Важливо!Пошук слова «яблука» вестиметься лише у першій колонці вашої виділеної таблиці, у цьому прикладі «яблука» шукатиметься у колонці D.

2 - Коли слово «яблука» знайдено, функція повинна знати, з якого стовпчика виділеної таблиці (D2:E5) скопіювати потрібне значення. У прикладі копіювати з колонки 2 (E), т.к. у першій колонці (D) ми робили пошук. Якщо ваша виділена таблиця для пошуку складатиметься з 10 колонок, то першій колонці проводиться пошук, а з 2 по 10 колонки - ви можете вибрати число для копіювання.

Щобформула = ВПР (A2; $ D $ 2: $ E $ 5; 2)підставила нові значення й інших найменувань товару - просто скопіюйте їх у інші осередки стовпця з цінниками товару (у прикладі копіюйте в осередки B3:B5). Формула автоматично здійснить пошук і копіювання значення з потрібної колонки нової таблиці.

5. Висновок

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

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

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