Як напісаць формулу ў Excel? Навучанне. Самыя патрэбныя формулы

Добры дзень.

Калісьці, напісаць самастойна формулу ў Excel - для мяне было чымсьці неверагодным. І нават, нягледзячы на ​​тое, што часта даводзілася працаваць у гэтай праграме, нічога акрамя тэксту не набіваў ...

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

І так, пачнём ...

1. Асноўныя аперацыі і асновы. Навучанне асновам Excel.

Усе дзеянні ў артыкуле будуць паказвацца ў Excel версіі 2007г.

Пасля запуску праграмы Excel - з'яўляецца акно з мноствам клетачак - наша табліца. Галоўная асаблівасць праграмы ў тым, што яна можа лічыць (як калькулятар) вашыя формулы, якія вы напішаце. Дарэчы, дадаць формулу можна ў кожнае вочка!

Формула павінна пачынацца са знака "=". Гэта абавязковая ўмова. Далей вы пішыце тое, што вам трэба палічыць: напрыклад, "= 2 + 3" (без двукоссяў) і націскаеце па клавішы Enter - у выніку вы ўбачыце, што ў вочку з'явіўся вынік "5". См. Скрыншот ніжэй.

Важна! Нягледзячы на ​​тое, што ў вочку А1 напісана лік "5" - яна лiчыцца па формуле ( "= 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

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

Глядзіце відэа: Programming - Computer Science for Business Leaders 2016 (Можа 2024).