В този урок ще анализираме как да създадем сложна формула в Excel, а също и да помислим типични грешкипроизтичащи от начинаещи потребители чрез невнимание. Ако наскоро работиш в Excel, ние Ви съветваме първо да се обърнете към урока, където обсъдихме създаването на прости формули.

Как да създадете сложна формула в Excel

В примера по-долу ще покажем как Excel изчислява сложни формули, разчитайки на процедурата за извършване на операции. В този пример искаме да изчислим стойността на данък върху продажбите за хранителни услуги. За да приложите това, напишете следния израз в клетката D4: \u003d (D2 + d3) * 0,075. Тази формула ще сгъне разходите за всички позиции на сметката, а след това умножава данъкът за продажба 7,5% (записан като 0.075).

Excel се придържа към реда на действие и първо прегъва стойностите в скоби: (44.85+39.90)=$84.75 . След това умножава този номер за данъчната ставка: $84.75*0.075 . Резултатът от изчислителните изследвания показва, че данъкът върху продажбите ще бъде $6.36 .

Изключително важно е да се въведат сложни формули с правилния ред на действие. В противен случай изчисленията на Excel могат да бъдат неточни. В нашия случай, при липса на скоби, умножението се извършва предимно и резултатът ще бъде неправилен. Скоби най-добрия начин Определения на процедурата за изчисления в Excel.

Създаване на сложни формули, използвайки процедурата

В примера по-долу ще използваме връзки във връзка с количествени данни за създаване на сложна формула, която ще изчисли пълната цена за преминаване на захранващата сметка. Формулата ще изчисли цената на всеки елемент от менюто и след това сгънете всички стойности заедно.

Можете да добавяте скоби към всяка формула, за да опростите възприятието му. Въпреки факта, че в този пример той няма да промени резултата от изчисленията, все още можем да направим умножение в скоби. Това изясняваме, че се извършва преди добавяне.

Excel не винаги предупреждава за грешки във формулата, така че трябва самостоятелно да проверите всичките си формули. За да разберете как може да се направи, научете формулите за проверка на урока.

Ако не сте работили с Excel преди, скоро ще разберете, че не е само таблица за въвеждане на числа. Разбира се, в Excel можете просто да помислите за суми в линии и колони, но можете да изчислите ипотечните плащания, да решавате математически и инженерни задачи и да намерите най-благоприятните опции в зависимост от посочените променливи.

В Excel всичко това се осъществява с формули в клетки. Според тези формули се извършват изчисления и други действия с данни за листа. Формулата винаги започва със знак за равенство (\u003d), след което можете да въведете цифри, математически оператори (например, знаци + и - за добавяне и изваждане) и вградени функции на Excel, значително разширяване на възможностите на формули.

По-долу е даден пример за формула, която умножава 2 до 3 и добавя към резултата 5, за да получите 11.

По-долу са примери за формули, които могат да се използват върху листове.

Части от формулата Excel

Формулата може също да съдържа един или повече такива елементи като функции, връзки, оператори и константи.

Процедурата за извършване на действия в формули

В някои случаи процедурата за изчисляване може да повлияе на стойността на връщане, така че е важно да се получат желаните резултати, за да се разбере стандартната процедура за изчисления и да се знае как да го промените.

Използване на функции и вложени функции в Excel Formulas

Функциите са предварително определени формули, които извършват изчисления за определени стойности - аргументи - в определен ред или чрез конкретна схема. Използването на функции могат да се извършват и прости и сложни изчисления. Всичко функции на Excel. Можете да видите в раздела с формула.

Използване на връзки в Excel Formulas

Връзката показва клетка или диапазон от листови клетки и доклади Microsoft Excel.където се намират необходимата стойност или данни за формулата. Използване на връзки можете да използвате в една и съща формула данни в различни части на листа, както и да използвате стойността на една клетка в няколко формули. В допълнение, можете да посочите връзки към клетки от различни листове от една книга или върху клетки от други книги. Връзки към клетки на други книги се наричат \u200b\u200bвръзки или външни препратки.

Използвайте имена в Excel Formulas

За да определите клетки, клетъчни диапазони, формули, константи и таблици на Excel, можете да създадете определени имена. Името е значително кратко обозначение, което обяснява целта на клетката, константи, формули или таблици, тъй като не е лесно да се разбере тяхната същност от един поглед. По-долу са примери за имена и показват как тяхното използване опростява разбирането на формулите.

Пример 1.

Пример 2.

Копирайте примерните данни от таблицата по-долу и го поставете в клетката A1. лист Excel. За да покажете резултатите от формулата, изберете ги и натиснете клавиша F2, след което въведете клавиша Enter. В допълнение, можете да регулирате ширината на колоните в съответствие с данните, съдържащи се в тях.

Забележка: Във формулите в колони C и D, специфично име на "продажби" се заменя с позоваване на диапазона А9: A13 и името "Информационна клип" се заменя с диапазона A9: B13. Ако книгата не е тези имена, формулите в D2: D3 ще върне грешка # име?.

Тип пример	Пример, в който не се използват имената.	Примерът, в който се използват имената.	Формула и резултат с имена
	"\u003d Суми (A9: A13)	"\u003d Суми (продажби)	Суми (продажби)
	"\u003d Текст (PRD (MAX (A9: 13), A9: B13,2, FALSE)," DD / mm / yyyy ")	"\u003d Текст (PRD (максимум (продажби), продажба на информация, 2, невярно)," dd.mm.yyyyy ")	Текст (максимум (продажби), информация за информация, 2, невярна), "dd.mm.yyyyy")
	Дата на продажба

За повече информация вижте Определение и използване на имена във формули.

Използване на масивите формули и константи на масиви в Excel

Формулата Massif може да изпълнява няколко изчисления и след това да върне една стойност или група ценности. Формулата Massif обработва няколко комплекта стойности, наречени аргументи на масив. Всеки масив аргумент трябва да включва същия брой редове и колони. Формулата на масива се създава по същия начин като другите формули, с разликата, че входовете Ctrl + Shift + се използват за въвеждане на такава формула. Някои вградени функции са масивни формули и за получаване на правилните резултати, те трябва да бъдат въведени като масиви.

Могат да се използват масивни константи вместо връзки, ако не е необходимо да въвеждате всяка постоянна стойност в отделна клетка На лист.

Използване на формула за масива за изчисляване на една или повече стойности

Забележка: Когато въвеждате формулата, арейсът на Excel автоматично завършва в къдрави скоби (и). Когато се опитате ръчно да влезете в Excel Braces, покажете формулата като текст.

Използвайте константи на масива

В обичайната формула можете да въведете връзка към клетка със самия стойност или стойност, също така наречете константата. По същия начин, в решаването формула, можете да въведете позоваване на масив или масив от стойностите, съдържащи се в клетките (понякога се нарича постоянна масива). Формите за масиви приемат константи, както и други формули, но константи на масива трябва да бъдат въведени в определен формат.

Константите на масива могат да съдържат номера, текст, логически стойности, като истина или фалшиви или стойности на грешки, като # n / d. В една постоянна масива, стойностите на различните видове могат да присъстват, например (1,3,4; истината, лъжа, истина). Числата в константи на масива могат да бъдат целочислени, десетични или имат експоненциален формат. Текстът трябва да бъде затворен в двойни кавички, като "вторник".

Когато форматирате константи на масията, уверете се, че изискванията се извършват по-долу.

Константи са сключени в къдрави скоби ( { } ).

Колоните са разделени със запетая ( , ). Например, да представят стойностите от 10, 20, 30 и 40, въведете (10,20,30,40). Тази константа на масив е матрица от 1 до 4 и съответства на връзка към един ред и четири колони.

Стойностите на клетките от различни линии са разделени от точка и запетая ( ; ). Например, за представяне на стойностите 10, 20, 30, 40 и 50, 60, 70, 80, които са разположени във всяка друга клетки, можете да създадете постоянна маса с размер 2 до 4: (10,20 , 30,40; 50, 60,70,80).

Отстраняване на формула

Заедно с формулата, резултатите от нейното изчисление се отстраняват. Възможно е обаче самата формула да напусне резултата от нейното изчисление под формата на стойност в клетката.

Използване на дебелото черво ( : ) Препратките към първата и последната клетки в диапазона са разделени. Например: A1: A5..

Посочени са задължителни аргументи

Функцията може да има задължителни и незадължителни аргументи (последните са посочени в скобите на синтаксиса). Трябва да въведете всички необходими аргументи. Опитайте се също така да не влизате твърде много аргументи.

Във формулата не повече от 64 нива на гнездене функции

Нивата на гнездене не могат да бъдат повече от 64.

Имената на книгите и листата са затворени в единични кавички

Ако имената на листа или книги, на клетки или стойности, в които се отнасят, съдържат необвързващи символи, те трябва да бъдат сключени в единични кавички ( " ).

Посочен път към външни книги

Номера, въведени без форматиране

При въвеждане на числа във формулата е невъзможно да се посочат признаците на долари, тъй като те се използват за обозначаване на абсолютни връзки. Например, вместо стойността $1000 Трябва да влезете 1000 .

Важно: Изчислените резултати на формулите и някои функции на Excel лист могат да се различават леко върху компютри, работещи с Windows с X86 или X86-64 архитектурата и компютрите, работещи с Windows RT с ARM ARCH.

В този пост, както в повечето членове на блога, таблиците на Excel аз наричам диапазони на клетки, към които е приложена командата Вмъкване - Таблици - Таблица . Прочетете повече за назначаването и предимствата на такава процедура, можете да прочетете. Ако не сте прочели този пост, ще ви бъде по-трудно да научите информация тук.

Сега нека да се съсредоточим подробно за това как формули работят в таблици.

Ред на резултатите

За изчисляване на крайните данни в таблиците има низ от резултати. За да го покажете, изберете всяка клетка вътре в таблицата и инсталирайте резервоара на лентата Дизайнер - параметри на таблица - улични струни . Таблицата ще се появи под масата. Маркирайте всяка клетка за изчисляване на колоната в този ред. В падащия списък изберете формулата, която програмата ще кандидатства за погрешно изчисляване на резултатите. Например, сумата, минималната, средна и т.н. Кликнете върху "Други функции ...", за да изберете друга функция.

Ред на резултатите в Excel

Можете също така да напишете себе си, за да изчислите резултата, той е написан по същия начин, както обичайните формули, само вместо клетки, техните имена се показват от заглавията на масата. Връзките към клетките показват мишката, така и самата програма ще замени имената им.

Не забравяйте, че резултатът от низ обработва тези клетки на таблиците, които са видими на екрана. Ако сте приложили филтър за някои таблични стойности, резултатите ще бъдат преизчислени само за избрани данни.

Формули за запис в колони

Ако трябва да извършите изчисления в колоните на таблицата, това е още по-лесно, отколкото в обичайната гама от клетки. Създайте нова колона в таблицата. Задайте курсора на всяка клетка на тази колона и запишете формулата. Използвайте метода за посочване на мишите връзки към клетките. Вместо това програмата ще се покаже във формулите на колоните, тя ще улесни възприемането на формулата. Кликнете върху Въведете и изненадайте! Excel не само изчислява резултата, но и копира формулата във всички колони. Вие дори не трябва да копирате формули ръчно!

Формули в таблици Excel

Тази функция не винаги е полезна, така че може да бъде забранена. За да направите това, когато поставите формулата, кликнете върху интелигентния маркер и изберете " Не създавайте изчислени колони автоматично "

Настройка на формулите на Excel Table

Обжалване на таблицата по име

Имате повикване за достъп до съдържанието на таблицата по име. За да зададете името на таблицата, посочете го в полето на лентата: Дизайнер - имоти - име на таблица .

Сега, за да извършите някои операции с данни за данни, можете да се свържете с него по име. Например, нашата маса се нарича "хора":

• Формула \u003d суми (хора) ще се опитат да обобщит всички клетки на таблицата
• Формула \u003d суми (хора [sum_name]) Определете размера на данните в колоната "Продажба на продажба" на таблицата "Хора"
• \u003d Хора [@ sum_name] / 100 - Разделете 100 продажби от една и съща линия

Ако числата са несигурни, клетъчните струни с формула и клетка в таблицата, Excel ще покаже традиционна справка с координатите.

Използването на формули в таблиците е напълно автоматизирано, интуитивно. Въпреки че много от тях ще бъдат трудни за тях, след няколко дни ще свикнете и оценявате всички предимства, които получават отлични таблици.

Благодарим ви, че прочетете този пост. И в следващата статия ще кажа. Ще се видим на страниците на блога.

Както знаете, редакторът на офиса на Excel на масата първоначално е предназначен за работа по математически, алгебрични и други изчисления, за които се използва въвеждането на специални формули. Въпреки това, в светлината на начина на регистриране на формулата в Excel, е необходимо да се вземе предвид, че повечето операции във въвеждането са коренно различни от всичко, което е обичайно да се използва в обикновения живот. Това означава, че самите формули имат малко по-различен вид и използват всички онези оператори, които се използват при записване на стандартни изчисления.

Помислете как да регистрирате формулата в Excel, на няколко прости примера, без да се засягат сложни операции, да разберете кой е необходим за изучаване на програмата дълбоко. Но дори предварителните познания ще дадат на потребителите разбиране на основните принципи на използването на формули за различни случаи.

Как да регистрирате формула в Excel: Първоначални концепции

Така че въвеждането на стойности на формулата в програмата е малко по-различно от стандартните операции, използвани от използваните знаци и оператори. При решаването на проблема как да се регистрирате формулата в Excel, е необходимо да се отблъсне от основните понятия, които се използват в почти всички компютърни системи.

Факт е, че машината е въвеждането на комбинация като "2 x 2" или общият компонент на скобата ("2 + 2) 5") не разбира. Това предвижда използването на няколко вида знаци, които са представени в таблицата по-долу, без да се броят логически оператори.

В този случай приоритетът на извършване на операции започва в степента и завършва с добавяне и изваждане. Освен това, въпреки факта, че Excel може да се използва като редовен калкулатор, като правило, е необходимо да се уточни номерата на клетките или техните диапазони. От само себе си се разбира, че форматът на данните във всяка такава клетка трябва да бъде настроен на съответния (поне цифров).

Сума и разлика

Как да предпишете сума от сумата или разликата в Excel? Така че, нека започнем с най-простите, когато искате да изчислите сумата. Във формулата (освен това за всички операции) се въвежда знак за равенство, след което желаната формула се вписва. В случай на конвенционален калкулатор, можете да укажете за инсталираната клетка "\u003d 2 + 2".

Ако се извършва сумирането за стойностите, въведени директно в други клетки (например, А1 и А2), формулата придобива формата "\u003d A1 + A2". Често, за използване на допълнителни оператори, сумата може да бъде в скоби. За разлика - същото, само с минус вместо плюс.

Когато трябва да зададете номера на клетките или техния диапазон, може да се приложи специален екип от сумата (в руската версия "суми" на английски език - сума). Когато се посочват няколко клетки, тя изглежда така: "\u003d суми (А1; А2)", за обхвата - "суми (A1: A10)", при условие че е необходимо да се изчисли сумата от всички числа в клетките от първо до десетата. По принцип, ако инсталирате активна клетка, която е непосредствено след последната в колоната с източници стойности, формулата не може да бъде въведена, но просто кликнете върху бутона Автоматично обобщение.

Умножение, разделяне и упражнения

Сега да видим как в Excel да регистрира формулата за умножение или разделяне. Същото е същото като при въвеждането на количеството или разликата, само операторите се различават.

За продукта се използва тип "\u003d A1 * A2" за частни - "A1 / A2". Това, между другото, точно същите команди, които могат да бъдат намерени с помощта на стандартния калкулатор на Windows.

Символът "^" се прилага към степента. За стойност в клетка А1, която например трябва да се повиши на квадрата, се използва формулата "A1 ^ 2".

Процентни изчисления

С процент, ако не засягат сложни изчисления, тя също е проста. Как да регистрирате процент формула в Excel?

Достатъчно е да въведете формулата на формуляра "\u003d A1 * 5%", след което ще получите тези пет процента от стойността в съответната клетка.

Използване на входни формули въз основа на селекция на клетки

Но всичко това се отнася до ръчна задача или така наречената формула за директно въвеждане (пряк или директен вход). Всъщност понякога е полезно да използвате мишката и клавиша CTRL.

Когато ключът е притиснат от ключа, можете просто да изберете желаните клетки, предварително обхващате необходимото изчисление във формулата. Клетките ще се добавят директно във формулата. Но в зависимост от вида на формулата, понякога скоби ще трябва да бъдат поставени ръчно.

Абсолютни, относителни и смесени видове клетки

Отделно си струва да се отбележи, че в програмата могат да се използват няколко вида клетки, да не говорим за данните, които съдържат.

Абсолютната клетка е непроменена и се нарича $ 1 $ 1, относителят е позоваване на обичайното местоположение (A1), смесено - има комбинация от референции и абсолютна и относителна клетка ($ a1 или $ 1). Обикновено такива формати се използват при създаването, когато данните за различни листове или дори в различни файлове участват във формулите.

Формул

И накрая, нека видим как в Excel регистъра на VLookup формулата. Тази техника ви позволява да вмъквате данни от един диапазон към друг. В този случай методът е донякъде подобен на проблема, използван при решаването на проблема как в Excel да регистрира формулата "Състояние", която използва знаците, показани в таблицата по-горе.

В общия смисъл тези изчисления са нещо като прост филтър, приложен към колоните, когато е необходимо да се филтрират само точни и не са приблизителни стойности.

В това изпълнение първо използвате стойностите на източника (първата) таблица (първата) таблица, в полето "Таблица", вторият диапазон със съдържанието (F4) е зададен, след което е зададен номерът на колоната и в Интервалната област, стойността на "лъжата" е настроена на действителност, когато филтрирането наистина трябва да се получи само, а не приблизителни стойности. Като правило такива формули се използват повече в склада или счетоводството, когато определянето на някои специализирани софтуерни продукти не е възможно.

Заключение

Остава да се каже, че не всички формули, които могат да се използват в редактора на таблицата на Excel, са описани тук. Това е, така да се каже, само Aza. Всъщност, ако копаете все още в тригонометрия или изчисления на логаритми, матрици или дори тензорни уравнения, всичко изглежда много по-сложно. Но за да изучавате всичко това, е необходимо да се наслади напълно ръководството за самия редактор. И това не е ясно, че в Excel, въз основа на променящите се данни, можете да създадете дори най-простите логически игри. Като пример, същата "змия", която първоначално нямаше никаква връзка с табличния редактор, но е възпроизведен от ентусиастите на неговия случай в Excel.

В останалите, трябва ясно да се разбере, че чрез изучаване на примитивни формули или действия за данни, тогава ще бъде възможно лесно да се научат и по-сложни изчисления, да речем, със създаването на кръстосани препратки, използвайки различни сценарии или VB скриптове и др. Всичко това се нуждае от време, така че ако искате да изследвате програмата и всички негови възможности максимум, ще трябва да се потиете върху теоретичната част.