Как да напиша формула в Excel Learning. Необходими са самите формули
Някога писането на формула в Excel сам беше нещо невероятно за мен. И дори въпреки факта, че често трябваше да работя в тази програма, не написах нищо друго освен текст...
Както се оказа, повечето от формулите не са сложни и са лесни за работа, дори и за начинаещ потребител на компютър. В статията бих искал да разкрия най-необходимите формули, с които често трябва да работите...
И така, да започваме...
Всички действия в статията ще бъдат показани в Excel версия 2007.
След стартиране на програмата Excel се появява прозорец с много клетки - нашата таблица. Основната характеристика на програмата е, че може да брои (като калкулатор) вашите формули, които пишете. Между другото, можете да добавите формула към всяка клетка!
Формулата трябва да започва със знака "=". Това е задължително условие. След това пишете какво трябва да изчислите: например "=2+3" (без кавички) и натиснете клавиша Enter - в резултат на това ще видите, че в клетката се е появил резултатът "5". Вижте екранната снимка по-долу.
Важно! Въпреки факта, че числото "5" е написано в клетка A1, то се изчислява по формулата ("=2+3"). Ако просто напишете "5" в съседната клетка, тогава, когато преместите курсора в тази клетка - в редактора на формули, горният ред, Fx) - ще видите простото число "5".
А сега си представете, че в клетка можете да напишете не само стойността 2+3, но и номерата на клетките, чиито стойности трябва да бъдат добавени. Да приемем, че “=B2+C2”.
Естествено, в B2 и C2 трябва да има някакви числа, в противен случай Excel ще ни покаже резултата равен на 0 в клетка A1.
И още една важна забележка...
Когато копирате клетка, в която има формула, като например A1, и я поставите в друга клетка, тя не копирастойност "5" и самата формула!
Освен това формулата ще се промени правопропорционално: тоест, ако A1 се копира в A2, формулата в клетка A2 ще бъде равна на "=B3+C3". Excel автоматично променя вашата формула: ако A1=B2+C2, тогава логично A2=B3+C3 (всички числа са увеличени с 1).
Резултатът, между другото, е A2=0, тъй като клетки B3 и C3 не са посочени, което означава, че са равни на 0.
По този начин можете да напишете формулата веднъж и след това да я копирате във всички клетки на желаната колона - и Excel сам ще направи изчислението във всеки ред от вашата таблица!
Ако не искате B2 и C2 да се променят, когато копирате и винаги да сте обвързани с тези клетки, тогава просто добавете знак "$" към тях. Пример е по-долу.
По този начин, където и да копирате клетка A1, тя винаги ще препраща към обвързаната клетка.
2. Добавяне на стойности в редове (формула SUM и SUMIFS)
Можете, разбира се, да сумирате всяка клетка, като направите формулата A1+A2+A3 и т.н. Но за да не страдате така, има специална формула в Excel, която ще сумира всички стойности в клетките които избирате!
Да вземем един прост пример. В наличност има няколко наименования на продукти, като знаем колко е всеки продукт поотделно в кг. е в наличност. Нека се опитаме да изчислим колко е в кг. товар в склада.
За да направите това, отидете в клетката, в която ще се покаже резултатът, и напишете формулата: =SUM(C2:C5)”. Вижте екранната снимка по-долу.
В резултат на това всички клетки в диапазона ще бъдат сумирани и ще видите резултата.
2.1. Състав с условие (с условия)
А сега нека си представим, че имаме определени условия, тоест трябва да съберем не всички стойности в клетките (Kg, на склад), а само определени, да речем, с цена (1 kg) по-малко от 100.
Има отлична формула за това „ SUMIFS“.Пример, непосредствено последван от обяснение на всеки символ във формулата.
=SUMIFS(C2:C5;B2:B5;”, където:
C2:C5– колоната (клетките), които ще бъдат добавени;
B2:B5– колоната, по която ще се проверява условието (т.е. цената, например, е по-малка от 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;”
Всичко е оставено туксъщото, с изключение на още едно условие ( C2:C6;”). Между другото, може да има много такива условия!
Ясно е, че никой няма да напише такива формули за толкова малка таблица, но за таблица с няколкостотин реда е съвсем различен въпрос. Например, тази таблица е повече от визуална.
4. Търсене и заместване на стойности от една таблица в друга (BVR формула)
Нека си представим, че сме получили нова таблица с нови ценови етикети за продукта. Добре е да има 10-20 артикула - можете да ги "допълните" всички ръчно. И ако има стотици такива имена? Много по-бързо е, ако Excel независимо намери съвпадащи имена от една таблица в друга и след това копира новите ценови етикети в нашата стара таблица.
За този проблем се използва формулата BVR. По едно време и той самият се „мърдаше“ с логическите формули „АКО“, докато не срещна това прекрасно нещо!
И така, да започваме...
Ето нашия пример + нова таблица с ценови етикети. Сега трябва автоматично да заменим нови ценови етикети от новата таблица в старата (новите ценови етикети са червени).
Поставете курсора в клетка B2 - тоест в първата клетка, където трябва автоматично да променим етикета с цената. След това пишем формулата, както на екранната снимка по-долу (след екранната снимка ще има подробно обяснение за нея).
=VPR(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 колони можете да изберете за копиране.
Така чеформулата =VPR(A2;$D$2:$E$5;2)замества нови стойности и за други имена на продукти - просто я копирайте в други клетки на колоната с ценови етикети на продукта (в нашия пример копирайте в клетки B3:B5). Формулата автоматично ще търси и копира стойността от колоната на таблицата, от която се нуждаете.
5. Заключение
В статията разгледахме основите на работата с Excel, започвайки с това как да започнете да пишете формули. Дадохме примери за най-често срещаните формули, с които повечето хора, които работят в Excel, често трябва да работят.
Надявам се, че някой ще се нуждае от разглобените примери и ще помогне за ускоряване на работата му. Добри експерименти!
PS
И какви формули използвате, възможно ли е по някакъв начин да опростите формулите, дадени в статията? Например при слаби компютри, когато някои стойности се променят в големи таблици, където изчисленията се правят автоматично, компютърът замръзва за няколко секунди, преизчислява и показва нови резултати...