Кращі новини сайту
Колись, написати самостійно формулу в Excel - для мене було чимось неймовірним. І навіть, незважаючи на те, що часто доводилося працювати в цій програмі, нічого крім тексту не набивав.
Як виявилося, більшість формул не уявляють з себе нічого складного і з ними легко можна працювати, навіть, починаючому користувачеві комп'ютера. У статті, як раз, хотілося б розкрити найпотрібніші формули, з якими найчастіше і доводиться працювати.
1. Основні операції і ази. Навчання основам Excel.
Після запуску програми Excel - з'являється вікно з безліччю клітинок - наша таблиця. Головна особливість програми в тому, що вона може вважати (як калькулятор) ваші формули, які ви напишете. До речі, додати формулу можна в кожну клітинку!
Формула повинна починатися зі знака "=". Це обов'язкова умова. Далі ви пишете те, що вам потрібно порахувати: наприклад, "= 2 + 3" (без лапок) і натискаєте по клавіші Enter - в результаті ви побачите, що в осередку з'явився результат "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);
"<100" - само условие, обратите внимание, что условие пишется в кавычках.
Нічого складного в цій формулі немає, головне дотримуватися співмірність: C2: C5; B2: B5 - правильно; C2: C6; B2: B5 - неправильно. Тобто діапазон підсумовування і діапазон умов повинні бути відповідні, інакше формула поверне помилку.
Важливо! Умов для суми може бути багато, тобто можна перевіряти не по 1-й колонці, а відразу по 10, задавши безліч умов.
3. Підрахунок кількості рядків, які відповідають умовам (формула СЧЁТЕСЛІМН)
Досить часто-яка трапляється завдання: підрахувати суму значень в осередках, а кількість таких осередків, які відповідають певним умовам. Іноді, умов дуже багато.
У цій же прикладі спробуємо порахувати кількість найменування товару з ціною більше 90 (якщо окинути поглядом, то і так можна сказати, що таких товарів 2: мандарини і апельсини).
Для підрахунку товарів в потрібному осередку написали таку формулу (див. Вище):
B2: B5 - діапазон, за яким будуть перевіряти, по заданому нами умові;
"> 90" - саме умова, полягає в лапки.
Тепер спробуємо трохи ускладнити наш приклад, і додамо рахунок ще по одній умові: з ціною більше 90 + кількість на складі менше 20 кг.
Формула набуває вигляду:
Тут все залишилося таким же, крім ще однієї умови (C2: C6; "<20" ). Кстати, таких условий может быть очень много!
Зрозуміло, що для такої маленької таблиці ніхто не буде писати такі формули, а ось для таблиці з декількох сотень рядків - це вже зовсім інша справа. Для прикладу ж ця таблиця - більш ніж наочна.
4. Пошук і підстановка значень з однієї таблиці в іншу (формула ВВР)
Уявімо, що до нас прийшла нова таблиця, з новими цінниками для товару. Добре, якщо найменувань 10-20 - можна і в ручну їх все "перезабіть". А якщо таких найменувань сотні? Набагато швидше, якби Excel самостійно знайшов в співпадаючі найменування з однієї таблиці в іншій, а потім скопіював нові цінники в стару нашу таблицю.
Для такого завдання використовується формула ВВР. Свого часу сам "мудрував" з логічними формулами "ЯКЩО" поки не зустрів цю чудову штуку!
Ось наш приклад + нова таблиця з цінниками. Зараз нам потрібно автоматично підставити нові цінники з нової таблиці в стару (нові цінники червоні).
Ставимо курсор в осередок B2 - тобто в перший осередок, де нам потрібно змінити цінник автоматично. Далі пишемо формулу, як на скріншоті нижче (після скриншота буде докладний пояснення до неї).
A2 - то значення, яке ми будемо шукати, щоб взяти новий цінник. У нашому випадку шукаємо в новій таблиці слово "яблука".
$ D: $ E - виділяємо повністю нашу нову таблицю (D2: E5, виділення йде від верхнього лівого кута до правого нижнього по діагоналі), тобто там, де буде проводиться пошук. Знак "$" в цій формулі необхідний для того, щоб при копіюванні цієї формули в інші осередки - D2: E5 не змінювалися!
Важливо! Пошук слова "яблука" буде вестися тільки в першій колонці вашої виділеної таблиці, в даному прикладі "яблука" буде шукатися в колонці D.
2 - Коли слово "яблука" буде знайдено, функція повинна знати, з якого стовпчика виділеної таблиці (D2: E5) скопіювати потрібне значення. У нашому прикладі копіювати з колонки 2 (E), тому що в першій колонці (D) ми проводили пошук. Якщо ваша виділена таблиця для пошуку буде складатися з 10 колонок, то в першій колонці проводиться пошук, а з 2 по 10 колонки - ви можете вибрати число для копіювання.
5. Висновок
У статті ми розглянули основи роботи з Excel, з того як почати писати формули. Навели приклади найпоширеніших формул, з якими дуже часто доводиться працювати більшості, хто працює в Excel.
Сподіваюся що комусь знадобляться розібрані приклади і допоможуть прискорити його роботу. Вдалих експериментів!
А які формули використовуєте ви, чи можна якось спростити формули наведені в статті? Наприклад, на слабких комп'ютерах, при зміні якихось значень у великих таблицях, де виробляються автоматично розрахунки - комп'ютер зависає на пару секунд, перераховуючи і показуючи нові результати.
Як зробити в ворде цінник фото
Читати топ новини: