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


excelДобрий день.

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

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

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


Зміст:

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

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

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

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

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

2014-03-29 08_19_23-Microsoft Excel - Книга1

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

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

2014-03-29 08_26_56-Microsoft Excel - Книга1

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

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

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

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

2014-03-29 08_35_39-Microsoft Excel - Книга1

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


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

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

excel

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

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

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

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

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

2014-03-29 08_48_17-Microsoft Excel - Книга1

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

2014-03-29 08_48_31-Microsoft Excel - Книга1

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

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

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

2014-03-29 09_15_41-Microsoft Excel - Книга1

=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: мандарини і апельсини).

2014-03-29 09_27_36-Microsoft Excel - Книга1

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

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

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

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

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

2014-03-29 09_27_15-Microsoft Excel - Книга1

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

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

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

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

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

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

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

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

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

2014-03-29 10_01_05-Microsoft Excel - Книга1

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

2014-03-29 10_00_33-Microsoft Excel - Книга1

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

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