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

Доброго дня.

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

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

Тож почнемо…

зміст

  • 1 1. Основні операції і ази. Навчання основам Excel.
  • 2 2. Додавання значень в рядках (формула СУМ і СУММЕСЛІМН)
    • 2.1 2.1. Додавання з умовою (з умовами)
  • 3 3. Підрахунок кількості рядків, які відповідають умовам (формула СЧЁТЕСЛІМН)
  • 4 4. Пошук і підстановка значень з однієї таблиці в іншу (формула ВВР)
  • 5 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 сам зробить розрахунок в кожного рядка вашої таблиці!

Якщо ви не хочете, щоб 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. Підрахунок кількості рядків, які відповідають умовам (формула СЧЁТЕСЛІМН)

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

Тож почнемо.

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

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

= СЧЁТЕСЛІМН (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.

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

PS

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

Комп'ютерна Допомога