Общ преглед на формулите в Excel

Една формула може да съдържа всички или някои от следните елементи: функции, препратки, оператори и константи.

Частите на формула   

1. Функции: Функцията PI() връща стойността на числото пи: 3,142...

2. Препратки: A2 връща стойността в клетката A2.

3. Константи: Стойности на числа или текст, въведени директно във формула, например 2.

4. Оператори: Операторът ^ (карета) повдига число на степен, а операторът * (звездичка) умножава числа.

Константа е стойност, която не се изчислява; винаги остава същата. Например датата 9.10.2008 г., числото 210 и текстът „Тримесечни приходи“ са константи. израз или стойност, получена от израз, не е константа. Ако използвате константи във формула вместо препратки към клетки (например = 30 + 70 + 110), резултатът се променя само ако промените формулата. Като цяло, най-добре е да поставите константи в отделни клетки, където те могат лесно да бъдат променени, ако е необходимо, след което да направите препратки към тях във формули.

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

• Стил на препратки A1

  По подразбиране Excel използва стила за препратки A1, който препраща към колони, означени с букви (от A до XFD, общо 16 384 колони) и към редове, означени с номера (от 1 до 1 048 576). Тези букви и числа се наричат заглавия на редове и колони. За да препратите към една клетка, въведете буквата на колоната, последвана от номера на реда. Например B2 препраща към клетката в пресечната точка на колоната B и реда 2.

  За да препратите към	Използвайте
  Клетката в колона A и ред 10	A10
  Диапазонът от клетки в колона A и редове от 10 до 20	A10:A20
  Диапазонът от клетки в ред 15 и колони от B до E	B15:E15
  Всички клетки в ред 5	5:5
  Всички клетки в редовете от 5 до 10	5:10
  Всички клетки в колона H	H:H
  Всички клетки в колоните от H до J	H:J
  Диапазонът от клетки в колоните от A до E и редовете от 10 до 20	A10:E20

• Създаване на препратка към клетка или диапазон от клетки в друг работен лист в същата работна книга

  В следващия пример функцията AVERAGE изчислява средната стойност за диапазона B1:B10 в работния лист с име "Маркетинг" в същата работна книга.

  

  1. Препраща към работния лист, наречен "Маркетинг"

  2. Препраща към диапазона от клетки от B1 към B10

  3. Удивителният знак (!) разделя препратката към работен лист от препратката към диапазон от клетки

  : Ако работният лист, към който има препратки, съдържа интервали или числа, трябва да добавите апострофи (') преди и след името на работния лист, например ='123'! A1 или ='Приходи от януари'! A1.

• Разликата между абсолютни, относителни и смесени препратки

  1. Относителни препратки    Относителната препратка към клетка в формула, например A1, се базира на относителното положение на клетката, която съдържа формулата, и клетката, към която сочи препратката. Ако позицията на клетката, която съдържа формулата, се промени, препратката се променя. Ако копирате или попълвате формулата в друг ред или колона, препратката се настройва автоматично. По подразбиране новите формули използват относителни препратки. Ако например копирате или попълвате относителна препратка от клетка B2 към клетка B3, тя автоматично се настройва от =A1 на =A2.

     Копирана формула с относителна препратка   

     

  2. Абсолютни препратки    Абсолютната препратка към клетка във формула, например $A$1, винаги препраща към клетката с указаното местоположение. Ако позицията на клетката, съдържаща формулата, се промени, абсолютният адрес остава същият. Ако копирате или попълвате формулата в друг ред или колона, абсолютният адрес не се пренастройва. По подразбиране новите формули използват относителни препратки, така че може да се наложи да ги преобразувате в абсолютни препратки. Ако например копирате или попълвате абсолютен адрес от клетка B2 в клетка B3, той остава същият и в двете клетки: =$A$1.

     Копирана формула с абсолютна препратка   

     

  3. Смесени препратки    Смесената препратка има или абсолютна колона и относителен ред, или абсолютен ред и относителна колона. Абсолютната препратка с колона приема формата $A1, $B1 и т.н. Абсолютната препратка с ред приема формата A$1, B$1 и т.н. Ако позицията на клетката, която съдържа формулата, се промени, относителната препратка се променя, а абсолютната препратка не се променя. Ако копирате или попълвате формулата в друг ред или колона, относителната препратка автоматично се коригира, а абсолютната препратка не се коригира. Ако например копирате или попълвате смесена препратка от клетка А2 към клетка B3, тя се пренастройва от =A$1 на =B$1.

     Копирана формула със смесена препратка   

     

• Стил на препратки 3D

  Удобно препращане към множество работни листове    Ако искате да анализирате данните в една и съща клетка или диапазон от клетки от множество работни листове в работна книга, използвайте 3D препратка. 3D препратката включва препратка към клетката или диапазона, предшествана от диапазон от имена на работни листове. Excel използва всички работни листове, съхранени между началното и крайното име от препратката. Например =SUM(Лист2:Лист13!B5) добавя всички стойности, съдържащи се в клетките B5 на всички работни листове между и включително Лист2 и Лист13.

  • Можете да използвате 3D препратки, за да препращате към клетки в други листове, да определяте имена и да създавате формули с помощта на следните функции: SSUM, AVERAGE, AVERAGEA, COUNT, COUNTA, MAX, MAXA, MIN, MINA, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA и VARPA.

  • 3D препратките не могат да се използват във формули с масиви.

  • 3D препратките не могат да се използват с оператор за сечение (единичен интервал) или във формули, които използват неявна пресечна точка.

  Какво се случва, когато премествате, копирате, вмъквате или изтривате работни листове    Следващите примери обясняват какво става, когато преместите, копирате, вмъкнете или изтриете работни листове, които са включени в 3D препратка. Примерите използват формулата =SUM(Лист2:Лист6!A2:A5), която сумира клетките от A2 до A5 в работните листове от 2 до 6.

  • Вмъкване или копиране    Ако вмъкнете или копирате листовете между Лист2 и Лист6 (крайните точки в този пример), Excel включва в изчисленията всички стойности от клетките между A2 и A5 на добавените листове.

  • Изтриване     Ако изтриете листове между Лист2 и Лист6, Excel премахва техните стойности от изчислението.

  • Преместване    Ако преместите листове, намиращи се между Лист2 и Лист6 извън адресирания диапазон в листа, Excel премахва техните стойности от изчислението.

  • Преместване на крайна точка    Ако преместите Лист2 или Лист6 на друго място в същата работна книга, Excel настройва изчислението, така че да обхване новия диапазон от листове между тях.

  • Изтриване на крайна точка    Ако изтриете Лист2 или Лист6, Excel настройва изчислението, така че да обхване диапазона от листове между тях.

• Стил на препратки R1C1

  Можете също да използвате стил на препратка, при който и редовете, и колоните в работния лист са номерирани. Стилът на препратка R1C1 е полезен за изчисляване на позициите на редовете и колоните в макроси. При стила R1C1 Excel показва местоположението на една клетка с "R", последвано от номер на ред, и "C", последвано от номер на колона.

  Препратка	Значение
  R[-2]C	относителна препратка към клетката, намираща се два реда нагоре в същата колона
  R[2]C[2]	Относителна препратка към клетката, намираща се два реда надолу и две колони вдясно
  R2C2	Абсолютна препратка към клетката във втория ред и втората колона
  R[-1]	Относителна препратка към целия ред над активната клетка
  R	Абсолютна препратка към текущия ред

  Когато записвате макрос, Excel записва някои команди с помощта на стила R1C1. Ако например запишете команда, като например избиране на бутона "Автосумиране ", за да вмъкнете формула, която добавя диапазон от клетки, Excel записва формулата, като използва стилА R1C1, а не стила A1.

  Можете да включвате или изключвате стила на препратки R1C1, избирайки или изчиствайки отметката от квадратчето Стил на препратки R1C1, под раздела Работа с формули, в категорията Формули на диалоговия прозорец Опции. За да се покаже този диалогов прозорец, изберете раздела Файл .

  Най-горе на страницата