Як написати формулу в Excel? Навчання. Самі потрібні формулиДобрий день.

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

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

І так, почнемо…

Зміст:

  • 1. Основні операції ази. Навчання основам Excel.
  • 2. Додавання значень у рядках (формула СУМ і SUMIFS)
  • 2.1. Складання з умовою (з умовами)
  • 3. Підрахунок кількості рядків, що задовольняють умовам (формула COUNTIFS)
  • 4. Пошук і підстановка значень з однієї таблиці в іншу (формула ВВР)
  • 5. Висновок

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

Всі дії в статті будуть показуватися в Excel версії 2007р.

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

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

Як написати формулу в Excel? Навчання. Самі потрібні формули

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

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

Як написати формулу в Excel? Навчання. Самі потрібні формули

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

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

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

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

Як написати формулу в Excel? Навчання. Самі потрібні формули

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

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

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

Як написати формулу в Excel? Навчання. Самі потрібні формули

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

 

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

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

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

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

Як написати формулу в Excel? Навчання. Самі потрібні формули

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

Як написати формулу в Excel? Навчання. Самі потрібні формули

 

2.1. Складання з умовою (з умовами)

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

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

Як написати формулу в Excel? Навчання. Самі потрібні формули

=SUMIFS(C2:C5;B2:B5;”<100″), де:

C2:C5 – та колонка (клітинки), які будуть додаватися;

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

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

 

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

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

 

3. Підрахунок кількості рядків, що задовольняють умовам (формула COUNTIFS)

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

І так… почнемо.

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

Як написати формулу в Excel? Навчання. Самі потрібні формули

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

=COUNTIFS(B2:B5;”>90″), де:

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

“>90” саме умова, полягає в лапки.

 

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

Як написати формулу в Excel? Навчання. Самі потрібні формули

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

=COUNTIFS(B2:B6;”>90″;C2:C6;”<20″)

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

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

 

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

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

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

І так, почнемо…

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

Як написати формулу в Excel? Навчання. Самі потрібні формули

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

Як написати формулу в Excel? Навчання. Самі потрібні формули

=ВПР(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.

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

PS

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

 

 

Соц закладки
Соц закладки