Както се очакваше, това, което може да бъде решено в MS Excel, може да бъде внедрено в Google Sheets. Но многобройните опити за решаване на проблеми с помощта на любимата ви търсачка доведоха само до нови въпроси и почти нулеви отговори.
Затова беше решено да улесним живота на другите и да се прославим.

Накратко за основното

За да може Excel или електронна таблица (електронна таблица на Google) да разбере, че написаното е формула, трябва да поставите знака "\u003d" в лентата с формули (Фигура 1).

• буквено-цифрени (ПИСМО \u003d КОЛОНА; НОМЕР \u003d РЕД), напр. "A1".
• в стил R1C1, в системата R1C1 и редовете, и колоните са обозначени с числа.

Адресът на клетка "B3" в такава система ще изглежда като R3C2 (R \u003d ред \u003d ред, C \u003d колона \u003d колона). За скриптове например се използват и двата стила.
Където пишем "\u003d формула", например \u003d SUM (A1: A10) и ще се покаже нашата стойност.
Общият принцип на работа на RC формулите е показан на фигура 2.

Фигура 2
Както можете да видите от фигура 3, стойностите на клетката са спрямо клетката, в която ще бъде записана формулата със знак за равенство. За да се запази естетическия вид на формулите, те съдържат символи, които могат да бъдат пропуснати: RC \u003d RC.

Фигура 3
Разликата между Фигура 2 и Фигура 3 е, че Фигура 3 е обща формулировка, която не е обвързана с редове и колони (вижте стойностите на редове и колони), което не може да се каже за Фигура 2. Но стилът RC в електронната таблица се използва главно за писане на javascript скриптове.

Типове връзки (типове адресиране)

За препратка към клетки се използват връзки, които са от 3 вида:

• Относителни връзки (пример, А1);
• Абсолютни връзки (пример, $ A $ 1);
• Смесени връзки (например $ A1 или A $ 1, те са наполовина относителни, наполовина абсолютни).

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

Относителни връзки

Относителна връзка "запомня" на какво разстояние (в редове и колони) сте щракнали RELATIVE до позицията на клетката, където сте поставили "\u003d" (отместване в редове и колони). След това издърпайте надолу ръчката за автоматично довършване и тази формула ще бъде копирана във всички клетки, през които се простирахме.

Абсолютни връзки

Както бе споменато по-горе, ако плъзнете формула, съдържаща относителни връзки върху маркера за автоматично довършване, таблицата ще преизчисли адресите им. Ако формулата съдържа абсолютни връзки, техният адрес ще остане непроменен. Най-просто казано - абсолютната препратка винаги сочи към една и съща клетка.
За да направите абсолютна относителна препратка, просто поставете знака "$" пред буквата на колоната и адреса на реда, например $ A $ 1. По-бърз начин е да маркирате относителната връзка и да натиснете клавиша F4 веднъж, докато електронната таблица ще добави самия знак $. Ако натиснете "F4" за втори път, връзката ще стане смесен тип A $ 1, ако за трети път - $ A1, ако четвърти път - връзката отново ще стане относителна. И така в кръг.

Смесени връзки

Смесените връзки са наполовина абсолютни и наполовина относителни. Знакът за долар в тях се появява или преди буквата на колоната, или преди номера на реда. Това е най-трудният за разбиране тип връзка. Например клетката съдържа формулата "\u003d A $ 1". Препратка A $ 1 е относителна в колона A и абсолютна в ред 1. Ако плъзнем тази формула надолу или нагоре върху маркера за автоматично довършване, връзките във всички копирани формули ще сочат към клетка A1, тоест те ще се държат като абсолютни. Ако обаче плъзнем надясно или наляво, връзките се държат като относителни, тоест електронната таблица ще започне да преизчислява адреса си. По този начин формулите, създадени чрез автодовършване, ще използват същия номер на ред ($ 1), но буквалната стойност на колоната (A, B, C ...) ще се промени.

Нека разгледаме пример за сумиране на клетки с умножение по определен коефициент.

Този пример предполага наличието на стойност на коефициента във всяка изчислена клетка (клетки D8, D9, D10 ... E8, F8 ...). (Фигура 4).
Червените стрелки показват посоката на разтягане с дръжката за пълнене на формулата, която се намира в клетка C2. Във формулата забележете промяната в клетка D8. При разтягане надолу се променя само числото, което символизира низа. Разтягането вдясно само променя колоната.

Фигура 4
Нека опростим примера, като използваме знака $ (Фигура 5).

Фигура 5
Но не винаги е необходимо да се замразяват всички колони и редове, понякога се използва само замразяване на редове или колони (Фигура 6)

Фигура 6
Можете да прочетете за всички формули на официалния уебсайт support.google.com
Важно: Данните, които трябва да бъдат обработени във формулите, не трябва да бъдат различни документи, това може да се направи само с помощта на скриптове.

Грешки във формулата

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

Фигура 7
Въпреки че грешките могат да бъдат не само синтактични, но и например математически, като разделяне на 0 (Фигура 7) и други (Фигура 7.1, 7.2, 7.3). За да видите бележка, показваща коя грешка е възникнала, задръжте курсора на мишката върху червения триъгълник в горния десен ъгъл на грешката.

Фигура 7.1

Фигура 7.2

Фигура 7.3
За удобство при четене на таблицата ще оцветим всички клетки с формули в лилаво.
За да видите формулите "на живо", трябва да натиснете горещия клавиш Ctrl + или да изберете Преглед (Изглед)\u003e Всички формули от горното меню. (Фигура 8).

Фигура 8

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

Има разлики в формулировките на формулите в справочника и във формулите, които се използват за работа в момента. Те се състоят в това, че вместо „запетая“, който беше използван по-рано в много формули, вече се използва „точка и запетая“ (промените настъпиха преди повече от половин година).
За да видите на какво се отнася формулата на тази страница (Фигура 9), трябва да щракнете в лентата с формули вдясно от етикета Fx (Fx се намира под главното меню, вляво).

Фигура 9
ВАЖНО: За да функционират правилно формулите, те трябва да бъдат написани с ЛАТИНСКИ букви. Руски (кирилица) „A“ или „C“ и латински „A“ или „C“ за формулата са 2 различни букви.

Формули

Аритметични формули.

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

Събиране, изваждане, умножение, деление.

• Описание: Формули за събиране, изваждане, умножение и деление.
• Тип формула: „Cell_1 + Cell_2”, „Cell_1-Cell_2”, „Cell_1 * Cell_2”, „Cell_1 / Cell_2”
• Самата формула: \u003d E22 + F22, \u003d E23-F23, \u003d E24 * F24, \u003d E25 / F25.

Имаме първоначалните данни в диапазона E22: H25 и резултата в колона D. Фигура 10 показва заглавие за всички данни, които ще бъдат използвани.

Фигура 10

Прогресия.

• Описание: формулата за увеличаване на всички следващи клетки с една (номериране на редове и колони).
• Тип формула: \u003d Предишна клетка + 1.
• Самата формула: \u003d D26 + 1

Спомнете си, че ако искате да използвате диапазон, той ще сумира всички клетки подред и ако трябва да сумирате клетки в определен ред, тогава трябва да ги посочите с „;“ в правилния ред. Имаме първоначалните данни за прогресията в клетка D26 и резултата в клетки E26: H26 (Фигура 11) Използва се за номериране на редове и колони.

Фигура 11

Закръгляване.

• Описание: Формула за закръгляване на число в клетка.
• Тип формула: \u003d КРЪГЛА (клетка с номер); брояч (колко цифри трябва да бъдат закръглени след десетичната запетая).
• Самата формула: \u003d КРЪГЛА (E28; 2).

Имаме първоначалните данни в клетка E28 и резултата в клетка D28 (Фигура 12)

Фигура 12
„КРЪГЪЛ“ се закръглява с математически закони, ако след десетичната запетая има цифра 5 или повече, тогава цялата част се увеличава с едно, ако 4 или по-малко, тогава тя остава непроменена, можете също да закръглите с помощта на менюто ФОРМАТ -\u003e Числа -\u003e " 1000,12 "2 знака след десетичната запетая (Фигура 13). Ако имате нужда от повече символи, трябва да щракнете върху ФОРМАТ -\u003e Числа -\u003e Персонализирани десетични -\u003e И да посочите броя на символите.

Фигура 13

Количество, ако клетките не са последователни.

Може би най-познатата функция

• Описание: сумата от числата, които са в различни клетки.
• Тип на формулата: \u003d SUM (номер_1; номер_2;… номер_30).
• Самата формула: "\u003d SUM (E30; H30)" пишем чрез ";" ако различни клетки.

Имаме първоначалните данни в клетки E30 и H30 и резултата в клетка D30

(Фигура 14).
Количество, ако клетките са последователни.

• Описание: сумиране на числа, които следват едно след друго (последователно).
• Тип на формулата: \u003d SUM (число_1: число_N).
• Самата формула: \u003d SUM (E31: H31) "напиши чрез": "ако е непрекъснат диапазон.
• Имаме първоначалните данни в диапазона от клетки E31: H31 и резултата в клетка D31 (Фигура 15).

Фигура 15

Средно аритметично.

• Описание: диапазонът от числа се сумира и разделя на броя клетки в диапазона.
• Тип на формулата: \u003d СРЕДНО (клетка с номер или номер_1; клетка с номер или номер_2;… клетка с номер или номер_30).
• Самата формула: \u003d СРЕДНА (E32: H32)

Имаме първоначалните данни в диапазона от клетки E32: H32 и резултата в клетка D32 (Фигура 16).

Фигура 16
Разбира се, има и други, но ние отиваме по-далеч.

Текстови формули.

От големия брой текстови формули, с които можете да правите всичко, което пожелаете с текста, най-популярна, според мен, е формулата за „залепване“ на текстови стойности. Има няколко възможности за неговото изпълнение:

Лепене на текстови стойности (формула).

• Описание: "залепване" на текстови стойности (опция А).
• Тип на формулата: \u003d CONCATENATE (клетка с номер / текст или текст_1; клетка с номер / текст или текст_2;…, клетка с номер / текст или текст_30).
• Самата формула: \u003d КОНКАТЕНАТ (E36; F36; G36; H36).

Имаме първоначалните данни в диапазона от клетки E36: \u200b\u200bH36 и резултата в клетка D36 (Фигура 17).
Използвайки Google Docs, те често провеждат анкети на служителите или съставят анкети чрез Google Forms (това са специални формуляри, които могат да бъдат създадени чрез менюто Insert-\u003e Form. След попълване на формуляра данните се представят в таблица. И след това те използват различни формули за работа с данните, например за залепване на пълното име).

Фигура 17

Числови стойности на свързване.

• Описание: „залепване“ на текстови стойности на ръка, без използване на специални функции (опция Б - ръчно писане на формула, сложността на формулата е всякаква.).
• Тип формула: \u003d клетка с номер / текст 1 & "" & клетка с номер / текст 2 & "" & клетка с номер / текст 3 & "" & клетка с номер / текст 4 ("" - интервал, & знак означава залепване , всички текстови стойности са написани в кавички „”).
• Самата формула: \u003d E37 & "" & F37 & "" & G37 & "" & H37.

Имаме първоначалните данни в диапазона от клетки E37: H37 и резултата в клетка D36 (Фигура 18 - слепени числа).

Фигура 18

Лепене на числови и текстови стойности.

• Описание: "залепване" на текстови стойности на ръка, без използване на специални функции (вариант С - смесен тип, всяка сложност на формулата).
• Тип формула: \u003d "text_1" & cell_1 & "text_2" & cell_2 & "text_3" & cell_3
• Важно: целият текст, който ще бъде написан на “”, ще остане непроменен за формулата.
• Самата формула: \u003d "още 1" & E38 & "използвай" & F38 & "като САЩ" & G38.

Имаме първоначалните данни „още 1“, „употреба“, „като US“ и в диапазона от клетки E38: G38, поради което е препоръчително да се използва този вид формула, а резултатът е в клетка D36 (Фигура 19) .
Залепете текст и числови стойности заедно.

Фигура 19

ЛОГИЧЕСКИ И ДРУГИ

Прехвърляне на данни от всякакви листове от същия файл.

Стигнахме до най-интересните, според мен функции: ЛОГИЧЕСКИ И ДРУГИ.
Една от най-полезните формули:

• Описание: прехвърляне на данни от всякакви листове от един и същ файл (за Excel можете да прехвърлите както от лист на една работна книга на друг лист от същата работна книга, така и от лист на една работна книга на лист на друга работна книга).
• Тип формула: \u003d "Sheet_Name"! клетка_1
• Самата формула: \u003d Данни! A15 (Данните са лист, A15 е клетка на този лист).

Имаме първоначалните данни на информационния лист, клетка A15 (фигура 20) и резултата на листа с формули в клетка D41 (фигура 20.1).

Фигура 20

Фигура 20.1

Масив от формули.

Повечето програми за електронни таблици съдържат два типа формули за масиви: „многоклетъчни“ и „едноклетъчни“.
Google Sheets разделя тези типове на две функции: ПРОДЪЛЖАВА и ARRAYFORMULA.
Формулите на многоклетъчен масив позволяват формула да връща множество стойности. Можете да ги използвате, без дори да го знаете, просто като въведете формула, която връща множество стойности.
Едноклетъчните формули за масив ви позволяват да пишете формули, като използвате въвеждане на масив, а не изход. Когато приложите формула във функцията \u003d ARRAYFORMULA, можете да предавате масиви или диапазони на функции и оператори, които обикновено използват само аргументи, които не са масиви. Тези функции и оператори ще бъдат приложени, по една за всеки запис в масива, и ще върнат нов масив с целия изход.
Ако искате да проучите проблема по-подробно, трябва да посетите support.google.
С прости думи, за да работите с формули, които връщат масиви от данни, за да избегнете синтаксични грешки, трябва да ги приложите в масив от формули.

Сумиране на клетки с условие IF.

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

• Описание: Сумирайте клетки с условие IF (формула SUMIF).
• Тип формула: \u003d SUMIF (‘Sheet’! Обхват; критерии; ‘Leaf’! Общ_диапазон)

За да обясним формулата, нека разгледаме по-отблизо един пример: 3 купувачи са инструктирани да купуват продукти от списъка, но плащат в една сума. След като хранителните стоки бяха разбити на касата, имаше списък с продукти (Фигура 21) в колона А и тяхното количество в колона Б.
Задачата, какъв вид ще има фискалната разписка след отпечатване (просто трябва да добавите продуктите на 3 купувача и да разберете общия брой продукти за всяка позиция)?

Фигура 21
Имаме първоначалните данни в листа с данни (Фигура 21) и резултата на листа с формули в колона D (Фигура 22). Колони E, F, G показват аргументите, използвани във формулата, а колона H показва общ изглед на формулата, който е в колона D и изчислява резултата.

Фигура 22
Примерът по-горе показва общ изглед на това как формулата „Sum If“ работи с едно условие, но най-често използваната е „Sum IF“ (с множество условия).

Сумиращи клетки IF, много условия.

Продължаваме да разглеждаме проблема с продуктите на различно ниво.
Купонът тепърва започва и след като се обадите на приятелите си, започвате да разбирате, че няма да има достатъчно алкохол. И трябва да го купите. Всеки от вашите приятели трябва да носи със себе си силно питие. Трябва да разберете броя бутилки бира, които трябва да донесете, и да дадете задача на приятелите си.

• Описание: сума на IF (с множество условия).
• Тип формула: \u003d SUMIF (‘Данни’! Обхват_1 & ‘Данни’! Обхват_2; критерии_1 & критерий_2; ‘Данни’! Общ_диапазон).
• Самата формула: \u003d (ARRAYFORMULA (SUMIF ((Данни! E: E & Данни! F: F); (B53 & C53); Данни! G: G)))

Имаме първоначалните данни в информационния лист (Фигура 23).

Фигура 23
Да предположим, че на листа с формула в клетка B53 (критерий_1 \u003d Бира) трябва да има името на напитката, а клетка С53 (критерий_2 \u003d 2) е броят на приятелите, които ще донесат Бира. В резултат клетка D53 ще съдържа резултата, че трябва да купим 15 бутилки бира. (Фигура 23.1), т.е. формулата ще определи количеството въз основа на два критерия - бира и брой приятели.

Фигура 23.1
Ако има повече такива позиции, линии 16 и 21 (Фигура 24), броят на мехурчетата в колона G се сумира (Фигура 24.1).

Фигура 24
Обща сума:

Фигура 24.1

Сега нека дадем по-интересен пример:

Ха ... купонът продължава и вие си спомняте, че имате нужда от торта, но не лесна, а супер - мега торта, с различни подправки, които според късмета също са криптирани с цифри. Предизвикателството е да купите подправките в точния брой торбички от всяка подправка. Главният готвач криптира необходимото количество в таблицата (Фигура 25.1), колони А и Б (в съседните колони правим нашите изчисления).
Всяка подправка има свой собствен сериен номер: 1,2,3,4. (Фигура 25).

Фигура 25
Нашата задача е да преброим броя на повтарящите се стойности, в нашия случай това са числа от 1 до 4 в колона Б и да определим процента на всяка от подправките.

• Описание: Преброяване на броя на еднакви цифри в големи масиви при допълнителни условия.
• Тип формула: COUNT IF ('Формула'! Range_A55: A61 + 'Формула'! Range_B55: B61; ConditionA „Подправки“ + ConditionB ”номер от 1 до 4”; Лист „Формула”! Range_B55: B61) / ConditionB ”номер от 1 до 4 ")

• Самата формула: \u003d ((ARRAYFORMULA (SUMIF ("Формула"! $ A $ 55: $ A $ 61 & "Формула"! $ B $ 55: $ B $ 61; $ F $ 55 & $ E59; "Формула") ! $ B $ 55: $ B $ 61))) / $ E59)

Имаме първоначалните данни в диапазона A55: B61, условието за избор е избрано от клетка F55 и E59: E62, а резултатът е в диапазона от клетки F59: F62 (броене на броя повторения на числови стойности при условия съвпада).

• Описание: Изчислява процента на подправките.
• Тип формула: Количество * 100% / Общо_номер
• Самата формула: \u003d F58 * $ G $ 56 / F $ 56

Фигура 25.1
В крайна сметка имаме броя повторения и процента.
За да напишете правилно формулата, трябва напълно да разберете какво ИМАТЕ, какво ИСКАТЕ ДА ПОЛУЧИТЕ и под каква форма. Може да се наложи да промените вида на първоначалните данни за това.
Преминавайки към следващия пример

Преброяване на стойности в обединени клетки.

Ако формулите използват стойности в „обединени клетки“, тогава е посочена първата клетка за обединените данни, в нашия случай това е колона F и клетка F65 (Фигура 26)



Фигура 26.
И накрая стигнахме до най-лошите формули.

Брои броя на числата в списъка с аргументи.

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

• Описание: Преброява броя клетки, съдържащи числа без текстови променливи.
• Тип формула: COUNT (стойност_1; стойност_2; ... стойност_30)
• Самата формула: \u003d COUNT (E45; F45; G45; H45)

Имаме първоначалните данни в диапазона от клетки E70: H70 и резултата в клетка D70 (Фигура 27 - броене на клетки, съдържащи числови стойности в диапазона, който съдържа клетки с текст).

Фигура 27.
Клетките, съдържащи текст и числа, също не се броят.

Фигура 27.1.

Преброяване на броя клетки, съдържащи числа с текстови променливи.

• Описание: Преброява броя клетки, съдържащи числа с текстови променливи.
• Тип формула: COUNTA (стойност_1; стойност_2; ... стойност_30)
• Самата формула: \u003d COUNTA (E46: H46)

Имаме първоначалните данни в диапазона от клетки E71: H71 и резултата в клетка D71 (Фигура 28 - броене на всички стойности в диапазона).

Фигура 28.
Също така формулата брои клетки, съдържащи само препинателни знаци, раздели, но не брои празни клетки.

Фигура 28.1

Замяна на стойности при условия.

• Описание: Замяна на стойности при условия.
• Тип на формулата: "\u003d IF (AND ((Условие1); (Условие2)); Резултатът е 0, ако са изпълнени условия 1 и 2; ако не, резултатът е 1)"
• Самата формула: "\u003d IF (AND ((F73 \u003d 5); (H73 \u003d 5)); 0; 1)"

Имаме първоначалните данни в клетки F73 и H73 и резултата в клетка D73 (Ако F73 \u003d 5 и H73 \u003d 5, тогава D73 \u003d 0 във всички останали случаи 1) (Фигура 29).

Фигура 29.

Фигура 29.1
Нека усложним примера.
Пребройте броя на клетките, в които е записана времевата рамка, с изключение на думите "автоматичен отговор", "зает", "-".

• Тип формула: "\u003d COUNTA (Range_A) -COUNTIF (Range_A;" auto answer ") - COUNTIF (Range_A;" - ") - COUNTIF (Range_A;" busy ")"

• Самата формула: \u003d COUNTA ($ E74: $ H75) -COUNTIF ($ E74: $ H75; "автоматичен отговор") - COUNTIF ($ E74: $ H75; "-") - COUNTIF ($ E74: $ H75; " зает ")

Имаме първоначалните данни в диапазона от клетки E74: H75 и резултата в клетка D74 (Фигура 30).

Фигура 30
И така, стигнахме до края на нашата малка образователна програма за формули в Google SpreadSheet и имам големи надежди, че съм хвърлил светлина върху някои аспекти на аналитичната работа с формули.
За да бъда честен, формулите бяха буквално трудно спечелени. Всеки от тях е създаден във времето. Надявам се, че статията ми и примерите в нея са ви харесали.
И накрая, като подарък. И нека разработчиците да ми простят!

Формула "ДОКУМЕНТ УБИЙЦ".

Ако трябва да скриете документ от любопитни очи завинаги, тогава тази формула е за вас.
Самата формула: "\u003d (ARRAYFORMULA (SUMIF ($ A: $ A & $ C: $ C; $ H: $ H & F $ 2; $ C: $ C)))". $ H: $ H управлява разпределението на формулата. След стартиране на fomlula (Фигура 31), долу в клетките ще започне да умножава следващата функция ПРОДЪЛЖАВА (клетка; ред; колона).

Фигура 31
Формулата циклично се добавя към цялата колона с формула. За да убиете документ, трябва да опитате малко, да създадете N-тия брой клетки и да напишете формулата в първите клетки на N-тия брой колони. Всичко! Никой друг не може да коригира и провери документа!
Ето какво казва помощната страница на Google за натоварването и ограниченията -

Организация на изчисленията в електронни таблици, Относителна препратка, Абсолютна препратка, Смесена препратка, Вградена функция, Логическа функция, Условна функция, Информатика Клас 9 Босов, Информатика Клас 9

Основната цел на електронните таблици е да организират всякакви изчисления. Вече знаете, че:
- Изчислението е процес на изчисляване по формули;
- Формулата започва със знак за равенство и може да включва операционни знаци, числа, референции и вградени функции. Нека първо разгледаме въпросите, свързани с организацията на изчисленията в електронни таблици.

3.2.1. Относителни, абсолютни и смесени връзки
Референцията показва клетката или диапазона от клетки, които искате да използвате във формулата. Връзките позволяват:
- използване в една формула, които са в различни части на електронната таблица;
- използвайте стойността на една клетка в няколко формули. Има два основни типа връзки:
1) относително - в зависимост от позицията на формулата;
2) абсолютен - не зависи от позицията на формулата.
Разликата между относителните и абсолютните препратки се появява, когато копирате формула от текущата клетка в други клетки.
Относителни връзки. Относителна препратка във формула определя местоположението на клетката с данни спрямо клетката, в която е написана формулата. Когато промените позицията на клетката, съдържаща формулата, препратката се променя.
Помислете за формулата \u003d A1 ^ 2, записана в клетка A2. Той съдържа относителна референция A1, която се възприема процесор за маса както следва: съдържанието на клетка, която е с един ред по-висока от тази, в която се намира формулата, трябва да бъде на квадрат.
Когато копирате формула по колона и по ред, относителната препратка се коригира автоматично по следния начин:
- изместването с една колона води до промяна в препратката на една буква в името на колоната;
- изместването с един ред води до промяна в референтния номер на реда по един.
Например, когато копирате формула от клетка A2 в клетки B2, C2 и D2, относителната препратка автоматично се променя и горната формула става: \u003d B1 ^ 2, \u003d C1 ^ 2, \u003d D1 ^ 2. Когато копираме същата формула в клетки AZ и A4, получаваме, съответно, \u003d A2 ^ 2, \u003d AZ ^ 2 (фиг. 3.4).

Пример 1. В клас 8 разгледахме проблема с населението на даден град, нарастващ ежегодно с 5%. Нека изчислим прогнозното население на града през следващите 5 години в електронни таблици, ако през текущата година това е 40 000 души.
Нека въведем първоначалните в таблицата, въведете формулата \u003d B2 + 0,05 * B2 в клетка OT с относителни връзки; копирайте формулата от клетка OT в диапазона от клетки B4: B7 (фиг. 3.5).



Ние (според условието на проблема) извършихме годишното изчисление на популацията по същата формула, първоначалните за които винаги бяха в клетка, разположена в една и съща колона, но с един ред по-висока от формулата за изчисление. При копиране на формула, съдържаща относителни препратки, необходимите промени бяха направени автоматично.
Абсолютни връзки. Абсолютната препратка във формула винаги се отнася до клетка на определено (фиксирано) място. В абсолютна препратка, $ се поставя преди всяка буква и цифра, например $ A $ 1. Промяната на позицията на клетката, съдържаща формулата, не променя абсолютната препратка. Когато копирате формула по линии и по колони, абсолютната препратка не се коригира (фиг. 3.6).

Пример 2. Определен гражданин открива сметка в банката за 10 000 рубли. Той бе информиран, че всеки месец размерът на депозита ще се увеличава с 1,2%. За да разбере възможната сума и увеличаването на сумата на депозита след 1, 2, ..., 6 месеца, гражданинът направи следните изчисления (фиг. 3.7).

Смесени връзки. Смесената препратка съдържа или абсолютно адресируема колона и относително адресируем низ ($ A1), или относително адресируема колона и абсолютно адресируем низ (A $ 1). Когато промените позицията на клетката, съдържаща формулата, относителната част на адреса се променя, но абсолютната част от адреса не се променя.
Когато копирате или попълвате формула по редовете и по колоните, относителната препратка се коригира автоматично и абсолютната препратка не се коригира (Фигура 3.8).

За да конвертирате връзка от относителна в абсолютна и обратно, можете да я изберете в реда за въвеждане и да натиснете F4 (Microsoft Office Excel) или Shift + F4 (OpenOffice.org Calc). Ако изберете относителна връзка, като А1, тогава при първото натискане на този клавиш (комбинация от клавиши) и редът, и колоната ще бъдат зададени на абсолютни препратки ($ A $ 1). При второто щракване само низът (A $ 1) ще получи абсолютната връзка. При третото щракване само колоната ($ A1) ще получи абсолютна препратка. Ако натиснете отново клавиша F4 (комбинация от клавиши Shift + F4), тогава за колона и ред се връзват относителни връзки (A1) отново.
Пример 3. Необходимо е да се състави таблица на добавяне на числата на първите десет, т.е. да се попълни таблица със следния формуляр:

При попълване на която и да е клетка от тази таблица се добавят съответните стойности на клетките от колона А и ред 1. С други думи, първият член остава непроменен в името на колоната (трябва да дадете абсолютна препратка към него ), но номерът на реда се променя (трябва да му дадете относителна препратка); вторият член променя номера на колоната (относителна препратка), но номерът на реда (абсолютна препратка) остава непроменен.
Въведете в клетка B2 формулата \u003d $ A2 + B $ 1 и я копирайте в целия диапазон B2: J10. Трябва да имате таблица за добавяне, с която всеки първокласник ще бъде запознат.


3.2.2. Вградени функции
Когато обработвате данни в електронни таблици, можете да използвате вградени функции - предварително дефинирани формули. Функцията връща резултата от извършване на действия върху стойностите, които действат като аргументи. Използването на функции ви позволява да опростите формулите и да направите процеса на изчисляване по-ясен.
Няколкостотин вградени функции са внедрени в електронни таблици, разделени на: математически, статистически, логически, текстови, финансови и др.
Всяка функция има уникално име, което се използва за нейното извикване. Името обикновено е съкращението на естествения език на името на функцията. При извършване на таблични изчисления често се използват следните функции:
SUM (SUM) - сбор от аргументи;
MIN (MIN) - определяне на най-малката стойност от списъка с аргументи;
MAX (MAX) - определя най-голямата стойност от списъка с аргументи.
Диалоговият прозорец на съветника за функции опростява създаването на формули и свежда до минимум грешки при печатане и синтаксис. Когато въведете функция във формула, диалоговият прозорец на съветника за функции показва името на функцията, всички нейни аргументи, описание на функцията и всеки от аргументите, текущия резултат на функцията и цялата формула.
Пример 4. Правилата на съдийството в международни състезания в един от спортовете са както следва:
1) представянето на всеки спортист се оценява от N съдии;
2) максималните и минималните оценки (една, ако са няколко) на всеки спортист се отхвърлят;
3) за спортиста се взема предвид средната аритметична стойност на останалите марки.
Информация за състезанието е представена в електронната таблица:



Изисква се изчисляване на оценките на всички участници в състезанието и определяне на оценката на победителя. За това:
1) в клетки A10, A1, A12 и A14 въвеждаме текстовете „Максимален резултат“, „Минимален резултат“, „Краен резултат“, „Победител“;
2) в клетка B10 въвеждаме формулата \u003d MAX (OT: B8); копирайте съдържанието на клетка B10 в клетки C10: F10;
3) в клетка В11 \u200b\u200bвъвеждаме формулата \u003d MIN (ВЗ: В8); копирайте съдържанието на клетка B10 в клетки C11: F11;
4) в клетка B12 въвеждаме формулата \u003d (SUM (OT: B8) -B10-B11) / 4; копирайте съдържанието на клетка B12 в клетки C12: F12;
5) в клетка B14 въвеждаме формулата \u003d MAKC (B12: F12).

3.2.3. Логически функции
При изучаването на предишния материал многократно сте попадали на логически операции NOT, AND, OR (NOT, AND, OR). Използвали сте изградените с тяхна помощ логически изрази при организиране на търсения в бази данни, при програмиране на различни изчислителни процеси.
Булевите операции също се изпълняват в електронни таблици, но тук те са представени като функции: първо се изписва името логическа операцияи след това логическите операнди са изброени в скоби.
Например булев израз, съответстващ на двойното неравенство 0<А1<10, в электронных таблицах будет записано как И(А1>0; А1<10).
Спомнете си как написахме подобен логически израз, когато се запознахме с бази данни и езика за програмиране Pascal.
Пример 5. Нека изчислим в електронни таблици стойностите на логическия израз NOT A И NOT B за всички възможни стойности на логическите променливи, включени в него.

При решаването на този проблем следвахме добре познатия алгоритъм за изграждане на таблица на истината за логически израз. Изчисленията в диапазоните на клетките SZ: C6, D3: D6, EZ: E6 се извършват от компютър съгласно формулите, които сме посочили.

За да се проверят условията при извършване на изчисления в електронни таблици, е внедрена логическа IF функция, наречена условна функция.
Условната функция има следната структура:
АКО (<условие>; <действие1>; <действие2>)
Тук<условие> - Булев израз, т.е. всеки израз, изграден с помощта на операции и логически операции, който приема стойността TRUE или FALSE.
Ако булевият израз е истина, тогава се определя стойността на клетката, в която е записана условната функция<действие1>ако е невярно -<действие2>.
Какво ви напомня за условна функция?
Пример 6. Помислете за проблема с приемането в училищен отбор по баскетбол: ученик може да бъде приет в този отбор, ако височината му е най-малко 170 cm.
Данните на кандидатите (фамилия, височина) са представени в електронна таблица.

Използването на условна функция в диапазона от клетки СЗ: С8 ви позволява да вземете решение (прието / неприето) за всеки кандидат.
Функцията COUNTIF ви позволява да преброите броя на клетките в диапазон, които отговарят на определено условие. Тази функция изчислява броя на кандидатите, избрани за екипа в клетка C9.

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

Общи характеристики и стартиране на редактора на формули

Писането и редактирането на формули в Word се извършва с помощта на редактора на формули Microsoft Equation 3.0, който съдържа около 120 шаблона. Позволява ви да вмъквате математически знаци и изрази във вашия документ, включително дроби, степени, интеграли и т.н. Когато пишете формула, подходящите стилове се прилагат автоматично за различните й компоненти (намален размер на шрифта за експоненти, курсив за променливи и т.н.).

Пример. Стартиране на редактора на формули.

1. Поставете курсора на мястото, където въвеждате и редактирате формулата.

2. В менюто Поставетезадайте командата Предмет…, отворете диалоговия прозорец Вмъкване на обект.

3. В раздела Съществов полето Тип обект:нека изберем MicrosoftEquation3.0.

4. Щракнете върху бутона OK.

Това ще отвори диалогов прозорец за работа с редактора на формули.

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

Редактирането или писането на формула завършва извън полето за въвеждане на формула.

Интерфейс за редактор на формула

След стартиране на редактора на формули ще се отвори прозорецът на редактора на формули, който има собствена лента с инструменти. Този панел се състои от два реда бутони:

 достъп до набори от символи,

 достъп до набори от шаблони.

Можете да въведете букви от руската и латинската азбука във формулата от клавиатурата, както и знаци за най-простите математически операции (+, -, /).

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

Следните набори от символи се намират в горния ред на лентата с инструменти отляво надясно:

• Символи на отношенията;

 Интервали и точки;

 Математически разлики;

 Признаци на операции;

 Символи със стрелки;

• Символи на теорията на множествата;

 Логически знаци;

 Различни символи;

 гръцки букви.

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

Следните набори от шаблони се намират в долния ред на лентата с инструменти отляво надясно:

 Шаблони на ограничения;

 Шаблони на фракции и корени;

 Създаване на индекси и индекси;

 Интеграли;

 Очертания и подчертавания;

 Маркирани стрелки;

 Работи и шаблони на теория на множествата;

 Матрични шаблони.

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

Писане и редактиране на формули

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

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

Редактирането на съществуваща формула включва изтриване на отделните й елементи и въвеждане на нови с помощта на редактора на формули.

Пример. Писане на фрагмент от формула.

Нека въведем фрагмент от формула на формата: .

1. Щракнете, за да отворите подменю с набор от шаблони за суми.

2. Изберете шаблона за сумиране с горни и долни граници (в горния ред, най-десния шаблон), като щракнете с мишката.

В резултат на това в прозореца за редактиране на формула ще се появи празно място: .

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

Пример. Изтриване на елемент на формула.

1. Изберете елемента за изтриване, като щракнете с мишката.

2. Натиснете клавиша .

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

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

Пример. Вмъкване на нови елементи във формула.

1. Поставете курсора за вмъкване на желаната позиция във формулата.

2. Нека въведем необходимата последователност от символи.

3. Ако е необходимо, като използвате шаблон, поставете празно място и след това запълнете слотовете му с необходимите символи.

Пример. Писане на формула с дробна линия.

.

1. Поставете курсора на мястото на формулата.

3. В слота за въвеждане на формула с помощта на клавиатурата въведете началото на формулата "

».

4. В комплекта Дробни и коренови моделищракнете върху шаблона

(горе вляво шаблон).

Това ще вмъкне шаблон с два слота в числителя и знаменателя на фракцията.

5. В слота за знаменател въведете израза

, а в слота на числителя -

.

6. В набор от шаблони Създаване на индекси и индексиизберете шаблона, който указва създаването на горния десен индекс.

7. В слота, който се появява, въведете израз за степента "n-1".

8. Поставете курсора в края на вече въведената част от формулата.

9. В набор от шаблони Създаване на индекси и индексиизберете шаблон.

10. В появилото се празно място в основния слот въведете символа „ д", И в слота на десния индекс въвеждаме израза на степента" - nt».

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

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

За да напишете матрични формули в долния ред на лентата с инструменти, има набор Матрични шаблони.

Пример. Писане на формула с къдрави скоби.

Помислете за писане на формула на формуляра:

2. Отворете прозореца на редактора на формули.

3. В слота за въвеждане на формулата от клавиатурата въведете „ у= ».

4. В комплекта Разделителни шаблоникликнете върху шаблона.

Това ще вмъкне къдрава скоба със слот вдясно от нея.

5. Поставете курсора в посочения слот.

6. В набора от шаблони на Matrix изберете шаблона :.

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

7. В горния и долния слот въведете съответните изрази на формула.

8. Затворете диалоговия прозорец за създаване на формула, като щракнете с мишката.

Пример. Написване на матрична формула.

Помислете за пример за писане на формулата за детерминанта от 3-ти ред:

.

1. Поставете курсора на мястото на формулата.

2. Отворете прозореца на редактора на формули.

3. В слота на рамката за въвеждане на формулата от клавиатурата въведете " \u003d".

4. Отворете комплекта Матрични шаблонии изберете шаблон:

5. Ще се отвори диалогов прозорец Матрица... Нека зададем броя на редовете и броя на колоните на матрицата.

6. Като щракнете вляво и вдясно от изображението на матрицата в прозореца, задайте вертикалните линии по краищата на матрицата.

7. В групата на ключове Подравняване на колонаизберете превключвателя Център.

8. В групата на бутона за подравняване на редове изберете радио бутона На основната линия... Щракнете върху OK.

Това ще вмъкне заготовка на матрица с три реда и три колони и вертикални линии отстрани.

9. В първия слот на първия ред въведете символа „ а».

10. В набора от шаблони Създаване на подменюта и индекси изберете шаблона, който указва създаването на долния десен индекс.

11. Нека въведем "11" в него.

12. Попълнете останалите слотове по същия начин.

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

Преоразмерете и преместете формула

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

Пример. Преоразмеряване на формула.

1. Изберете формулата, като щракнете с мишката.

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

Ако промените размера на формулата непропорционално, относителното положение на елементите може да бъде нарушено.

За да промените мащаба на формулата, изберете формулата и изберете Редактиране | Обект | Формула | Отворете... След това изберете подходящата скала (25% до 400%) от менюто Изглед.

Пример. Преместете формулата.

1. Изберете формулата, като щракнете с мишката.

2. Преместете показалеца на мишката върху формулата, така че да придобие формата на стрелка, насочена наляво.

3. Натиснете левия бутон на мишката и плъзнете формулата до желаното място в документа.

4. За да промените хоризонталното положение на формулата, задайте командата Абзац ...меню Формати задайте необходимите стойности за параметрите на абзаца с формулата.

Това е глава от книгата: Майкъл Гирвин. Ctrl + Shift + Enter. Овладяване на формули за масиви в Excel.

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

• (глава 11);
• (глава 13);
• (глава 15);
• (глава 17).

Фигура: 19.1. Извличане на уникални записи с помощта на опция Разширен филтър

Изтеглете бележка във формат или, примери във формат

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

На фиг. 19.1 показва набор от данни (диапазон A1: C9). Вашата цел е да получите списък с уникални състезателни писти. Тъй като трябва да запазите оригиналните данни, не можете да използвате опцията Премахване на дубликати (меню ДАННИ –> Работете с данни –> Премахване на дубликати). Но можете да използвате Разширен филтър... За да отворите диалогов прозорец Разширен филтър, преминете през менюто ДАННИ –> Сортиране и филтриране –> Освен товаили натиснете и задръжте клавиша Alt и след това натиснете последователно S, L (за Excel 2007 или по-нова версия).

В отворения диалогов прозорец Разширен филтър (фиг. 19.1) задайте опцията копирайте резултата на друго място, поставете отметка в квадратчето Само уникални записи, посочете региона за извличане на уникални стойности от ($ B $ 1: $ B $ 9) и първата клетка, където ще бъдат поставени извлечените данни ($ E $ 1). На фиг. 19.2 показва получения уникален списък (диапазон E1: E6). Ако не включите името на полето в Оригинален обхват диалогов прозорец Разширен филтър (вместо да използва $ B $ 2: $ B $ 9 на фигура 19.1) Excel ще третира първия ред от диапазона като име на поле и рискувате да получите дубликат. На фиг. 19.3 показва едно от многото възможни приложения за уникален списък.

Изтеглете уникален списък въз основа на критерий с опция Разширен филтър

В последния пример извадихте уникален списък от една колона. Разширеният филтър може също да извлече уникален набор от записи (т.е. целия ред на оригиналната таблица), използвайки критерий. На фиг. 19.4 и 19.5 показват ситуация, в която искате да извлечете уникални записи от диапазона A1: D10, за които името на компанията е ABC. По-нататък в тази глава ще видите как да направите тази работа с помощта на формула. Ако обаче не ви е необходим автоматичен процес, можете да използвате Разширен филтър, което със сигурност е по-просто от формулата.

Фигура: 19.4. Нуждаете се от уникални записи за ABC компания; за да увеличите изображението, щракнете с десния бутон върху него и изберете Отворете снимката в нов раздел

Фигура: 19.5. Използвайки Разширен филтър извличането на уникални записи въз основа на критерии е много по-лесно от метода на формулата. Извлечените записи обаче няма да се актуализират автоматично, ако критериите или данните на източника се променят.

Извличане на уникален списък от една колона с помощта на обобщена таблица

Ако вече използвате обобщени таблици, тогава знаете, че всеки път, когато поставите произволно поле в областта Струни или Колони (Фигура 19.6), автоматично ще получите уникален списък. На фиг. Фигура 19.6 показва как можете бързо да създадете уникален списък със състезателни писти и след това да преброите броя посещения на всеки от тях. Докато обобщената таблица е полезна за извличане на уникален списък от една колона, е малко вероятно да бъде полезна за извличане на уникални записи въз основа на критерии.

Фигура: 19.6. Можеш да използваш обобщена таблицакогато имате нужда от уникален списък и последващо изчисление въз основа на него

Извлечете уникален списък от една колона, използвайки формули и помощна колона

Използването на помощна колона прави извличането на уникални данни по-лесно от използването на формули на масиви (Фигура 19.7). Този пример използва техниките, които сте научили (с помощта на функцията COUNTIF) и (с помощта на помощна колона). Ако сега промените оригиналните данни в диапазона B2: B9, формулите автоматично ще отразят тези промени в областта D15: D21.

Формула на масив: Извличане на уникален списък от една колона с помощта на функцията SMALL

Тъй като формулите на масива, използвани в този раздел, са доста сложни за разбиране, тяхното създаване е разделено на етапи: първият е фрагмент, който отчита уникални стойности (Глава 17); второто е извличане на данни въз основа на критерии (Глава 15). На фиг. Фигура 19.8 показва формулата за изчисляване на уникални стойности (тъй като това е формула на масив, можете да я въведете, като натиснете Ctrl + Shift + Enter). Обърнете внимание на следните аспекти на тази формула:

1. Функцията FREQUENCY връща масив от числа (фиг. 19.9): за първото появяване на състезателна писта се връща броят на нейните появявания в оригиналните данни; за всяко следващо появяване на състезателната писта се връща нула (виж). Например Sumner се появява на първата и петата позиция в масива. На първа позиция функцията FREQUENCY връща 2 - общият брой на лятото в диапазона B2: B9, на петата позиция - 0.
2. Функцията FREQUENCY се намира в аргумента log_expression функцията IF, така че функцията IF връща TRUE за всяка ненулева стойност и FALSE за всяка ненулева стойност.
3. Аргумент стойност_ако_истина на функцията IF съдържа 1, така че функцията SUM отчита броя на такива.

Фигура: 19.8. Функцията FREQUENCY се намира в аргумента log_expression функции АКО

Фигура: 19.9. (1) функцията FREQUENCY връща масив от числа; (2) функцията IF връща 1 за ненулеви числа и FALSE за нули

Сега нека създадем формула за извличане на уникален списък. На фиг. 19.10 показва масив от относителни позиции, поставени в аргумент масив функции МАЛКИ.

В предишния пример (фиг. 19.9) в аргумента стойност_ако_истина функцията IF имаше едно разпределено, така че функцията IF върна едно и FALSE. Тук (фиг. 19.10) аргументът стойност_ако_истина съдържа: LINE ($ B $ 2: $ B $ 9) -LINE ($ B $ 2) +1. Следователно функцията IF (вътре в функцията SMALL) връща относително число на позицията в диапазон с уникална състезателна писта или FALSE за дубли (Фигура 19.11).

Фигура: 19.11. Функцията IF връща относително номер на позиция в диапазон с уникална състезателна писта или FALSE за дубли

На фиг. 19.12 показват резултатите от формулата. На фиг. 19.13 показва, че веднага щом първоначалните данни се променят, формулите веднага отразяват тези промени. Но какво, ако добавите нови записи? След това ще видите как да създадете формули за динамичен диапазон.

Фигура: 19.13. Ако първоначалните данни се променят, формулата се актуализира незабавно. Филтърът и Разширеният филтър не могат да се актуализират автоматично, без да се пише VBA код

Формула на масив: извличайте уникален списък от една колона, използвайки динамичен диапазон

Нека добавим последен пример от това, което сте научили за формули, които използват специфични имена въз основа на динамичния диапазон (). На фиг. 19.14 е формулата за определяне на името Проследяване... Тази формула предполага, че никога не въвеждате запис след ред 51.

Фигура: 19.14. Определение на името Проследяване въз основа на формула

След като идентифицирате името, можете да го използвате във всяка формула. На фиг. Фигура 19.15 показва как да използвате име, за да преброите броя на уникалните стойности (сравнете с Фигура 19.8). И на фиг. 19.16 показва формула, която извлича самите уникални стойности от списък на състезателни писти. Имайте предвид, че вместо фрагмент обхват<>"" (Както беше на фиг. 19.8 и 19.10), се използва функцията ETEXT (всеки текст ще върне стойността TRUE). Когато използвате ETEXT, ако въведете число (като в клетка B11) или друг нетекст, формулата ще игнорира тази стойност. На фиг. 19.17 показва, че формулата автоматично ще извлече всички нови имена на проследяване, като игнорира числата.

Фигура: 19.16. Извличане на уникално име на подравняване въз основа на динамичния диапазон

Създайте формула за уникални стойности за падащ списък

Въз основа на току-що видяния пример, нека дефинираме второ име - TrackList, също въз основа на динамичен обхват, но сега се позовава на списък с уникални следи (обхват E5: E14, фигура 19.18). Тъй като диапазонът E5: E14 съдържа само текст и празни стойности (тестови низове с нулева дължина - ""), в аргумента lookup_value функцията MATCH може да използва заместващи символи *? (което означава поне един знак). И в спора тип_съвпадение функцията MATCH трябва да използва стойност –1, за да намери последния текстов елемент в колоната, който съдържа поне един знак. Както е показано на фиг. 19.18, тогава можете да използвате конкретно име в полето Източник прозорец Проверка на входните стойности (за подробности относно създаването на падащ списък вижте). Падащият списък може да се разширява и свива при добавяне или премахване на нови данни в колона Б.

Където заместващите символи трябва да се третират като обикновени знаци

Както научихте, понякога заместващите символи трябва да се третират като символи. На фиг. 19.18 показва как можете да промените формулите за такива случаи. Добавяте тилда преди диапазона на аргументите lookup_value Функция SEARCH и добавете празен низ към задната част на диапазона в аргумента lookup_array.

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

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

Динамични формули за извличане на имена на клиенти и продажби

Формулите са показани на фиг. 19.22. Например, ако добавите нов запис TTКамиони на линия 17 , формулата SUMIF в клетка F15 автоматично ще добави новата стойност. Ако добавите нов клиент в колона B, той веднага ще се появи в колона E, а формулата SUMIF в колона F ще покаже новата сума.

Фигура: 19.22. Използване на конкретно име и две формули за масив за извличане на уникални клиенти и продажби

Обърнете внимание, че функцията SUMIF в аргумента сума_диапазонсъдържа една клетка - $ C $ 10. Ето какво може да каже помощта по формулата SUMIF по тази тема: аргумент сума_диапазон може да не е със същия размер като аргумента обхват... Горната лява клетка на аргумента се използва като начална клетка при определяне на действителните клетки, които трябва да бъдат добавени сума_диапазони след това се сумират клетките на частта от диапазона, съответстваща по размер на аргумента обхват... Формулите, въведени в клетки E15 и F15, се копират по колоните.

Сортиране на числови стойности

Формулите за сортиране на числа са доста прости, но за сортиране на смесени данни са безумно сложни. Ето защо, ако не се нуждаете от незабавна актуализация, тогава е по-добре да направите без формули, като използвате опцията Сортиране... На фиг. 19.23 показва две формули за сортиране.

На фиг. 19.24 показва как можете да използвате помощна колона за сортиране на числа. Тъй като функцията RANK не сортира едни и същи числа (придавайки им еднакъв ранг), е добавена функцията COUNTIF, за да ги различи. Обърнете внимание, че функцията COUNTIF има разширен обхват, който започва с една линия. Това е необходимо, за да не допринесе първото появяване на произволен номер. Второто появяване на числото ще увеличи ранга с един. Тази последователна номерация задава реда, в който функциите INDEX и SEARCH извличат записи в диапазона A8: B12.

Ако можете да си позволите да създадете помощна колона в областта за извличане на данни (диапазон A10: A14 на фигура 19.25), е удобно да приложите описаното по-горе сортиране на числата въз основа на функцията SMALL и въз основа на нея извлечете имената с помощта на функцията масив.

Фигура: 19.25. Ако не можете да използвате помощна колона, приложете МАЛКОто сортиране (в клетка A11) и формула на масив (в клетка B11)

Често в бизнеса и спорта се изисква да се извлекат N най-добрите стойности и имената, свързани с тези стойности. Стартирайте решението с формулата COUNTIF (клетка A11 на фигура 19.26), която ще определи броя на записите за показване. Обърнете внимание, че аргументът критерий във функцията COUNTIF в клетка A11 - повече или равно стойността в клетка D8. Това ви позволява да покажете всички гранични стойности (в нашия пример, въпреки че искате да покажете Топ 3, има четири подходящи стойности).

Фигура: 19.26. Извличане на първите три резултата и съответните им имена. Когато N се промени в клетка D8, зоната A15: B21 ще бъде актуализирана

Сортиране на текстови стойности

Ако можете да използвате помощна колона, задачата не е толкова трудна (Фигура 19.27). Операторите за сравнение обработват текстови символи въз основа на числовите ASCII кодове, присвоени на символите. В клетка C3 първата функция COUNTIF връща нула, а втората добавя една. В C4: 2 + 1, C5: 0 + 2, C6: 3 + 1.

Сортиране на смесени данни

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

Excel сортира резултатите в следния ред: първо числа, след това текст (включително низове с нулева дължина), FALSE, TRUE, стойности на грешки в реда, в който се появяват, празни клетки. Цялото сортиране се извършва в съответствие с ASCII кодовете. Има 255 ASCII кода, всеки от които съответства на число от 1 до 255:

Например 5 е ASCII 53, а S е ASCII 83. Ако сортирате двете стойности, 5 и S, от най-ниската към най-високата, тогава 5 е по-висока от S, защото 53 е по-малка от 83.

Наборът от данни в диапазона A2: A5 (фиг. 29) се преобразува в диапазона E2: E5 в съответствие с правилата за сортиране. За да разберете по-добре принципите на сортиране, вземете предвид стойностите в диапазона C2: C5. Например, ако зададете въпроса "Колко над мен в ранг?" на идентификатора в клетка A2 (54678), отговорът ще бъде нула, тъй като в сортирания списък ID 54678 ще бъде най-горният. SD-987-56 ще има три идентификационни номера над себе си. Нуждаете се от формула, за да получите стойности в диапазона C2: C5.

Първо изберете диапазона E1: H1 и в лентата с формули въведете \u003d TRANSPOSE (A2: A5), въведете формулата, като натиснете Ctrl + Shift + Enter (Фиг. 19.30). След това изберете диапазона E2: H5 в лентата с формули, въведете \u003d A2: A5\u003e E1: H1 и въведете формулата, като натиснете Ctrl + Shift + Enter (фиг. 19.31). На фиг. Фигура 19.32 показва резултата като правоъгълен масив от стойности TRUE и FALSE, които съответстват на всяка от клетките в получения масив, като отговор на въпроса "Заглавието на реда по-голямо ли е от заглавието на колоната?"

Фигура: 19.30. Изберете диапазона E1: H1 и въведете формули за масив

Фигура: 19.31. В диапазона E2: H5 въведете формулата на масива \u003d A2: A5\u003e E1: H1

Фигура: 19.32. Всяка клетка от диапазона E2: H5 съдържа отговора на въпроса "Заглавието на реда по-голямо ли е от заглавието на колоната?"

Например клетка E3 задава въпроса: SD-987-56\u003e 54678. Тъй като 54678 е по-малка от SD-987-56, отговорът е ИСТИНСКИ. Обърнете внимание, че диапазонът E3: H3 включва три стойности TRUE и една FALSE. Поглеждайки назад към фиг. 19.29, можете да видите, че числото три е в клетка C3.

Както е показано на фигури 19.33 и 19.34, можете да конвертирате TRUE и FALSE стойности в единици и нули, като добавите двойни отрицателни елементи към формулата на масива. Тъй като оригиналният масив (E2: H5) има измерение 4 × 4 и искате резултатът под формата на масив 4 × 1, използвайте функцията MULTIPLE (вижте фиг. 19.35 и). Функцията MULTIPLE е функция на масив, така че въведете я, като натиснете Ctrl + Shift + Enter (Фигура 19.36). Сега, вместо да използвате диапазона E2: H5, добавете подходящите елементи във формулата (Фигура 19.37).

Фигура: 19.36. Чрез избиране на диапазона C2: C5 и въвеждане на функцията за масив MULTIPLE получавате колона с числа, които казват колко ID са в сортирания списък над избрания

Фигура: 19.37. Вместо да се използва помощният диапазон E2: H5, съответните елементи се добавят във формулата

На фиг. 19.38 показва как можете да замените масив от константи със STRING ($ A $ 2: $ A $ 5) ^ 0.

Фигура: 19.39. За да се справят с потенциални празни клетки, всички случаи на A2: A5 трябва да бъдат допълнени с IF (A2: A5<>"", A2: A5); функцията STRING не изисква такова добавяне, тъй като функцията работи с адрес на клетка, а не със нейното съдържание

Тъй като окончателната формула ще се използва другаде, трябва да направите абсолютни всички диапазони (Фигура 19.40). На фиг. 19.41 показва получените стойности.

Фигура: 19.40. Обхвати A2: A5 се превърна в абсолютен

Тъй като в бъдеще този елемент ще се използва два пъти, можете да го запишете под конкретно име. Както е показано в диалоговия прозорец (фиг. 19.42), формулата се нарича SZB - Колко стойности са повече.

1. Аргумент масив функция INDEX се отнася до оригиналния диапазон A2: A5.
2. Първата функция MATCH ще каже на функцията INDEX относителното положение на елемента в масива A2: A5.
3. Докато аргументът lookup_value функцията SEARCH остава празна.
4. Посочено име (SZB) в аргумент lookup_array ще ви позволи първо да получите достъп до елемента, който има стойност 0, след това 2 и накрая 3.
5. Нула в аргументацията тип_съвпадение указва точно съвпадение, което ще премахне препратката към дубликати.

Фигура: 19.43. Стартирате формула за извличане и сортиране на данните в клетка A11. Аргумент lookup_value функцията SEARCH засега е празна

Преди да създадете аргумент lookup_value функция ТЪРСЕНЕ, не забравяйте от какво всъщност имате нужда. Има три уникални идентификатора, които трябва да бъдат сортирани, така че имате нужда от три числа в аргумента lookup_value тъй като формулата се копира надолу. Тези числа ще ви позволят да намерите относителната позиция в масива A2: A5, която трябва да предоставите на функцията INDEX:

1. В клетка A11 функцията MATCH ще върне 0, което съответства на относителното положение на 1 в посоченото име на MSB.
2. Когато формулата се копира надолу в клетка A12, функцията MATCH трябва да върне числото 2 и относителната позиция \u003d 4 в MSB.
3. В клетка A13 функцията MATCH трябва да върне 3 и относителната позиция \u003d 2 в MSB.

Картината се появява, когато се замислите за какъв аргумент lookup_value при копиране на формулата надолу, заявката трябва да съвпада: "Дайте минималната стойност в рамките на конкретно име на SZB, което все още не е използвано." Както е показано на фиг. 19.44 елемент на формула MIN (IF (UND (SEARCH ($ A $ 2: $ A $ 5; A $ 10: A10; 0)); SZB)) връща минималната стойност, когато копирате формулата надолу, отговаряйки точно на заявката. Причината това работи е, че във фрагмента на UNM (ТЪРСЕНЕ ($ A $ 2: $ A $ 5; A $ 10: A10; 0)) се сравняват два списъка (вижте). Обърнете внимание на разширяващия се диапазон A $ 10: A10 в аргумента lookup_array... В клетка A11 комбинацията от UND и MATCH помага да се извлекат всички уникални числа от SZB и да се предоставят на функцията MIN. Когато копирате формулата в клетка A12, идентификаторът, който е извлечен в клетка A11, отново присъства в разширения диапазон и отново ще бъде намерен в диапазона $ A $ 2: $ A $ 5. UND обаче връща FALSE и не се извлича стойност от MWB.За да видите това, въведете формулата на масива на фигура 19.44, като натиснете Ctrl + Shift + Enter и го копирайте надолу.

Фигура: 19.44. Елемент на формула в аргумент lookup_value функция MATCH съответства на заявката: „Дайте минималната стойност в конкретно име на SZB, което все още не е използвано“

На фиг. 19.45 показва, че аргументът lookup_array втората функция ТЪРСИ обхвата A $ 10: A10 се разшири до A $ 10: A11. За да разберете как работи тази формула, последователно изберете нейните фрагменти и кликнете върху F9 (фиг. 19.46-19.49).

Фигура: 19.45. Разширяем диапазон A $ 10: A11 сега (в клетка A12) включва първия ID (54678)

Фигура: 19.46. Комбинацията от UND и втората функция SEARCH доставя масив от логически булеви; две FALSE стойности изключват нулеви стойности от конкретно име на MSB

Фигура: 19.47. Нулите са изключени и остават само числа 3 и 2; числото 2 е минимумът, така че именно това трябва да се извлече, както следва

Фигура: 19.48. Функцията MIN избира числото 2; сега функцията MATCH може да намери правилната относителна позиция за функцията INDEX

Фигура: 19.49. Функцията INDEX ще извлече стойността 2, която съответства на относителната четвърта позиция на идентификатора в диапазона A2: A5

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

Фигура: 19.50. Във функцията MIN има две условия; първо: "клетките не са празни?", второ: "стойността все още не е използвана?"

На фиг. 19.51 е крайната формула. Добавено е условие, така че редовете в диапазона A11: A15 да останат празни след извличане на сортирани уникални стойности. На фиг. 19.52 показва какво се случва, ако клетка A3 е празна. Нашето допълнение за проверка за празни клетки работи.

Не беше лесно. Но ако сте прочели дотук, надявам се да ви е харесало.

ИЗЧИСЛЕНИЯ В ЕЛЕКТРОННИ МАСИ

2.1. V o d f o r m u l s

Изчисляването на стойността на клетката се извършва чрез въвеждане на формула. Формулите винаги започват със знак за равенство “ = ”.

Формулите ви позволяват да извършвате общи математически операции върху стойности от клетки в работен лист. Например трябва да добавите стойностите в клетки B1 и B2 и да покажете сумата им в клетка B5. За целта поставете курсора в клетка B5 и въведете формулата “\u003d B1 + B2”.

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

Следните оператори могат да се използват във формули:

a r i f m e t i c e -

w a n i -

t e c t a -

& - обединяване на текстови стойности.

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

2.2. СЪЗДАВАНЕ НА ФОРМУЛИ С МНОГО

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

поставете курсора в клетката, където искате да въведете формулата;

въведете знака за равенство „\u003d”;

поставете курсора в клетката, чиито координати трябва да бъдат в началото на формулата, и щракнете върху бутона на мишката;

въведете оператор (например знакът „+“) или друг знак, който продължава формулата;

преместете курсора до клетката, чиито координати искате да използвате във формулата и щракнете върху бутона на мишката;

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

2.3. A b s o lute и около s и t e l n e c e c

Има три основни типа адреси (връзки): относителни, абсолютни и смесени.

Разликите между относителните и абсолютните препратки се появяват, когато копирате и премествате формули от една клетка в друга.

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

Например клетка А1 съдържа константа 4, клетки В1 до В10 съдържат стойности от 0,1 до 1 на стъпки от 0,1. За да получите резултата в клетки D1: D10 съгласно формулата 4b i, където i \u003d 1, 2, ..., 10, трябва да въведете клетка D1 „\u003d $ A $ 1 * B1“ и да копирате формулата в клетки D2, D3,…, D10. В този случай D2 ще съдържа фразата “$ A $ 1 * B2”, в D3 - “$ A $ 1 * B3” и др., Където съдържанието на $ A $ 1 не се променя, тъй като адресът (връзка ) е абсолютно и B1 се променя на B2, B3, ..., B10, тъй като адресът е относителен.

За да посочите диапазона от клетки във формули, използвайте символа „ : ”, Например: A2 : A5.

За да обозначите група несъседни клетки, използвайте символа „ ; ”, Например: A2; B5; E10.

2.4. РЕДАКТИРАНЕ НА ФОРМУЛА

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

Първотози начин... Трябва да изберете клетката, която искате, да кликнете върху лентата с формули и да я редактирате.

Втори начин... Щракнете двукратно върху клетка и редактирайте формулата директно в клетката.

2.5. И използвайки функцията

Една от най-полезните функции на EXCEL е неговият широк набор от функции, които ви позволяват да извършвате различни видове изчисления. Всяка функция има синтаксис за писане:

ИМЕ НА ФУНКЦИЯТА (аргумент 1; аргумент 2; ...).

Аргументите на функцията могат да бъдат числа, текстове, логически стойности, стойности на грешки, връзки, масиви. При десетичните числа целочислената част се отделя от дробния знак „,“, например: –30.003.

Текстовите стойности трябва да бъдат затворени в двойни кавички. Ако самият текст съдържа двойни кавички, тогава те трябва да бъдат удвоени.

Булевите стойности са TRUE и FALSE. Булевите аргументи могат да бъдат и изрази за сравнение, за които TRUE или FALSE могат да бъдат оценени, например: B10\u003e 20.

Например функцията AVERAGE изчислява средната аритметична стойност на поредица от стойности. Изразът „\u003d СРЕДЕН (6; 12; 15; 16)“ ще даде резултата 12,75. Ако стойностите 6, 12, 15, 16 се съхраняват в клетки B10 - B15, тогава формулата може да бъде написана по следния начин: „\u003d СРЕДНА (B10: B15)“.

Функцията SUM се използва за определяне на сумата от стойности, например: „\u003d SUM (B10: B15)“. Числата 6, 12, 15, 16 ще бъдат обобщени.

Удобно е да се въведе функция във формула, използвайки Съветници за функции ... Съветникът за функции ви позволява да въведете функция във формулата, която създавате. За да направите това, направете следното:

поставете курсора в клетката, където искате да въведете функцията;

в стандартната лента с инструменти щракнете върху бутона на съветника за функции ¦ х или изпълнете командата Вмъкване + Функция ;

в диалоговия прозорец, който се появява в списъка Категории изберете желаната категория функции. След това в списъка Функция се появяват функциите на избраната категория;

в списъка Функция изберете функция и кликнете върху бутона Добре ;

ще се появи диалогов прозорец в зависимост от вида на избраната функция;

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

щракнете върху бутона в диалоговия прозорец Добре .

2.6. Автоматични s u m i r o v и e

Най-простият метод за сумиране на таблица е автоматичното сумиране. За целта поставете курсора в клетка под колоната или вдясно от реда, чиито стойности трябва да бъдат обобщени и да кликнете върху бутона на стандартната лента с инструменти Автоматично сумиране (показва символа „ å ”). След това натиснете бутона Въведете .

Когато сумирате елементите на матрицата по колони и редове, е удобно да изберете клетки на матрицата с допълнителен ред и колона и след това да натиснете бутона „ å ”. Сумата от всички редове и колони на матрицата ще бъде получена автоматично.

2.7. Отговорно за работа с m a s i in a m i

Формулите за масиви (таблични формули) ви позволяват да извършвате много изчисления, като пишете една формула. Например трябва да умножите стойностите в колона A2: A6 по съответните B2: B6. Запишете резултата в C2: C6, без да копирате формулата.

Трябва да направите следното:

изберете клетките на резултата C2: C6;

въведете знака “\u003d”;

изберете клетки A2: A6;

въведете знака “*”;

подчертайте B2: B6;

натиснете клавишите на клавиатурата Shift + Ctrl + Enter .

Формулата „(\u003d A2: A6 * B2: B6)“ ще се покаже в лентата с формули и резултатът ще се получи във всички клетки C2: C6.

Z A D A N I E 2

1) Стартирайте програмата EXCEL.

2) В собствената си директория създайте файл с име „lab_2.хls“.

3) Назовете първия лист от работната книга „Лаборатория. No 2 (въвеждане на формули) “.

4) В клетка A1 напишете фамилията си.

5) Създайте отчетна карта според примерната таблица. 2. Сумирайте елементите във всяка колона и всеки ред. Изчислете средния резултат, като използвате формули.

Таблица 2

	Математика	Икономика	Информатика	Среден резултат
Иванов	5
Петров	4
Сидоров	3
Яковлев	4
Среден резултат	4

6) Пребройте броя на "5" точки във всеки предмет. Отпечатайте списък с ученици със среден успех по-голям от „4“.

7) Изчислете y \u003d 2 x 2 + 3 x + 5, където x аргументът се променя от 0,1 на 1 на 0,1 стъпки. Използвайте абсолютни препратки за константи 2, 3, 5 и относителни препратки за x.

8) За матрица 4x4 изчислете нейната обратна матрица, изравнете я и намерете транспонираната матрица, използвайки формулите на таблицата.

9) Запазете съдържанието на работната книга.