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