Добрий день.
Коли-то, самостійно написати формулу в Excel – для мене було чимось неймовірним. І навіть, незважаючи на те, що часто доводилося працювати в цій програмі, нічого крім тексту не набивав…
Як виявилося, більшість формул не представляють із себе нічого складного і з ними легко працювати, навіть починаючому користувачеві комп’ютера. У статті, як раз, хотілося б розкрити найбільш потрібні формули, з якими найчастіше доводиться працювати…
І так, почнемо…
Зміст:
- 1. Основні операції ази. Навчання основам Excel.
- 2. Додавання значень у рядках (формула СУМ і SUMIFS)
- 2.1. Складання з умовою (з умовами)
- 3. Підрахунок кількості рядків, що задовольняють умовам (формула COUNTIFS)
- 4. Пошук і підстановка значень з однієї таблиці в іншу (формула ВВР)
- 5. Висновок
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 сам зробить розрахунок у кожної строчки вашої таблиці!
Якщо ви не хочете, щоб В2 та С2 змінювалися при копіюванні і завжди були прив’язані до цих клітинок, то просто додайте до них значок “$”. Приклад нижче.
Таким чином, куди б ви не скопіювали клітинку A1 – вона завжди буде посилатися на прив’язані осередку.
2. Додавання значень у рядках (формула СУМ і SUMIFS)
Можна, звичайно, кожну клітинку складати, роблячи формулу A1+A2+A3 і т. п. Але щоб так не мучитися, є в Excel спеціальна формула, яка складе всі значення в комірках, які ви виділите!
Візьмемо простий приклад. Є на складі кілька найменувань товару, причому ми знаємо, скільки кожного товару окремо в кг. є на складі. Спробуємо порахувати, скільки всього у кг. вантажу на складі.
Для цього переходимо в клітинку, в якій буде відображатися результат і пишемо формулу: =SUM(C2:C5)”. См. скріншот нижче.
В результаті всі клітинки в діапазоні будуть підсумовані, а ви побачите результат.
2.1. Складання з умовою (з умовами)
А тепер уявімо, що у нас є певні умови, тобто скласти треба не всі значення в комірках (Кг, на складі), а лише певні, скажімо, з ціною (1 кг) менше 100.
Для цього є чудова формула “SUMIFS“. Відразу ж приклад, а потім пояснення кожного символу у формулі.
=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: мандарини і апельсини).
Для підрахунку товарів у потрібній клітинці написали таку формулу (див. вище):
=COUNTIFS(B2:B5;”>90″), де:
B2:B5 – діапазон, за яким будуть перевіряти, по заданому нами умові;
“>90” – саме умова, полягає в лапки.
Тепер спробуємо трохи ускладнити наш приклад, і додамо рахунок ще по одній умові: з ціною більше 90 + кількість на складі менше 20 кг.
Формула набуває вигляду:
=COUNTIFS(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.
Сподіваюся, що комусь знадобляться розібрані приклади і допоможуть прискорити його роботу. Вдалих експериментів!
PS
А які формули використовуєте ви, чи можна якось спростити формули наведені в статті? Наприклад, на слабких комп’ютерах, при зміні якихось значень у великих таблицях, де виробляються автоматично розрахунки – комп’ютер зависає на пару секунд, перераховуючи і показуючи нові результати…
