Лабораторни упражнения по информатика. Excel Labs

Министерство на образованието и науката

Руска федерация

Федерална държавна автономна образователна институция

висше професионално образование

Национален изследователски ядрен университет МИФИ

Волгодонски инженерно-технологичен институт - клон на NRNU MEPhI

Създаване на таблици

МЕТОДИЧЕСКИ УКАЗАНИЯкъм лабораторна работа

по компютърни науки в програматаМайкрософтпревъзходен

Волгодонск 2010 г

UDC 519.683(076.5)

Рецензент техн. науки Z.O. Кавришвили

Компилатор В.А. боздуган

Създаване на таблици. Указания за лабораторна работа в MicrosortExcel. 2010. 13 стр.

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

_____________________________________________________________________________

ã Волгодонски институт на Националния изследователски ядрен университет MEPhI, 2010 г

ã Булава В.А., 2010

Лабораторна работа

Създаване на таблици в програматапревъзходенчрез автоматизиране на въвеждането на данни.

Цел на работата. Да се ​​затвърдят придобитите знания за създаване, редактиране и форматиране на таблици в Excel.

Формулиране на проблема.

    Изчислете стойността на функцията г = f(х)/ ж(х) за всички хна интервала [ а, b] стъпка по стъпка Да се. Значение на функциите f(х) , ж(х) , стойността на краищата на интервала аИ bи стойност на стъпката Да сесе дава от маса 1в Приложението според варианта за конкретна специалност.

    Решението трябва да се получи под формата на таблици "Основни" и "Помощни".

    Изчислени стойности на функцията прикопиране в колона ДА СЕбез формули .

Програмата Excel се стартира с помощта на командите Старт → Програми →микросортпревъзходен.

    Когато създавате таблица, обединете клетки A1:H1 в първия ред и поставете текста "Таблици" в центъра.

    Във втория ред обединете клетки A2: E2 и поставете текста "Main" в центъра. Обединете клетки G2:H2 и центрирайте текста „Помощен“

    В клетка A3 въведете текста "No. p / p". В клетки B3:F3 поставете съответно имената на колоните: х ; f(х)=…(по ваш избор); ж(х)=…(по ваш избор); г= f(х)/ ж(х).

    В клетки G3:H3 поставете съответно имената на колоните: а ; Да се.

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

    В таблиците "Основни" и "Помощни" съдържанието на клетките трябва да е подравнено спрямо центъра на клетката и да е с размер на шрифта 12 pt.

    Цветът на шрифта на заглавията на таблиците трябва да е син.

    Оцветете външните граници на таблиците в синьо, вътрешните граници в зелено и запълването на клетките в жълто.

Формуляр за отчитане.

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

    Печатната версия на доклада трябва да съдържа:

а) заглавна страница

б) целта на работата;

в) поставяне на задачата;

г) резултатът от задачата.

2. Предоставете резултата от лабораторната работа в електронен вид на 3,5-инчова дискета като файл с наименование "Таблици".

Контролни въпроси.

    Какво е абсолютно, относително, смесено адресиране?

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

    Какви са начините за подравняване на съдържанието на клетка?

    Как мога да променя цвета и дебелината на линиите на външните и вътрешните граници на таблицата?

    Как мога да променя цвета на фона на клетките на таблицата?

Типичен пример.

Изчислете стойността на функцията y \u003d x ∙ sin (x) / (x + 1) на сегмента със стъпка 0,1. Решението е представено под формата на таблица. Изчислени стойности на функцията прикопиране в колона ДА СЕбез формули .

Решение.

В такъв случай f(х) = х∙ грях(х) , ж(х) = х+1 , а =0 , b = 2 , к = 0.1

1. В първия ред на таблицата изберете клетки A1:H1. Изпълнете командата Форматиране → Клетки, в прозореца, който се отваря, разгънете раздела подравняванеи изберете артикула сливане на клетки. В центъра на обединените клетки въведете текста „Таблици“.

2. По същия начин във втория ред обединете клетки A2: E2 и поставете текста „Main“ в центъра и обединете клетки G2: H2 и поставете текста „Auxiliary“ в центъра.

3. В третия ред в клетка A3 въведете текста № p / p (име на първата колона на таблицата ) , в клетка B3 - х(името на втората колона на таблицата ), клетка C3 - f(х)= х∙ грях(х) , в клетка D3 - ж(х)= х+1 , в клетка E3 - y=f(х)/ ж(х) , в клетка G3 - а, в клетка H3 - к.

4. Въведете в клетка A4 1 и попълнете клетки A5:A24 с числа от 2 до 21. За да направите това, изберете клетка A4 (направете я текуща), тя ще бъде маркирана в черна рамка. Задръжте курсора на мишката върху маркера за запълване (черен кръст в долния десен ъгъл на клетката) и с натискане на десния бутон на мишката плъзнете маркера за запълване по колоната Атака че черната рамка да покрива клетки A5:A24. Освобождавайки десния бутон на мишката, в менюто, което се отваря, изберете елемента запълвам. Клетки A5: A24 ще бъдат запълнени с числа 2; 3; 4 ...

5. В клетка G4 въведете стойността 0 (стойност на левия край на интервала).

6. В клетка H4 въведете стойността 0,1 (размер на стъпката).

7. Попълнете колоната INстойности х:

    В клетка B4 въведете формулата =$ Ж$4 (първоначална стойност на x), знакът $ показва абсолютно адресиране. В клетка B5 въведете формулата =B4+$з$4. Това означава, че първоначалната стойност на x ще бъде увеличена със стойността на стъпката;

    като използвате метода за автоматично довършване, попълнете клетки B5:B24 с тази формула. Изберете клетка B5. Задръжте показалеца на мишката върху манипулатора за запълване и щракнете налявобутон на мишката, плъзнете манипулатора за запълване, така че черната рамка да покрива клетки B5:B24. Колона B ще бъде попълнена с числа 0; 0,1; 0,2;… и съответните формули ще бъдат в лентата с формули.

8. Попълнете колоната C със стойностите на функцията f(x)=x∙sin(x). В клетка C4 въведете формулата =B4∙sin(B4). Нека попълним клетки C5:C24 с тази формула, като използваме метода за автоматично довършване.

9. Попълнете колона D със стойностите на функцията g(x)=x+1. В клетка D4 въведете формулата =B4+1. Нека попълним клетки D5:D24 с тази формула, използвайки метода за автоматично довършване.

10. Попълнете колона E със стойностите на функцията y=f(x)/g(x). В клетка E4 въведете формулата =C4/D4, попълнете клетки E5:E24 с тази формула, като използвате метода за автоматично попълване.

11. Да рамкираме масите:

12. Променете цвета на фона на клетките на основните и спомагателните таблици:

    изберете основната таблица;

    въведете команди от менюто Форматиране → Клетки → Изглед.В прозореца, който се отваря, изберете жълт цвят. Нека щракнете върху бутона OK.

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

13. В основната таблица стойностите, получени в резултат на изчисления прикопиране в колона ДА СЕбез формули:

    изберете клетки E4:E24;

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

    като натиснете десния бутон на мишката и без да го отпускате, преместете показалеца на мишката в клетка К4;

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

В резултат на работата получаваме следните таблици:

Основен

Помощни

Приложение

маса 1

x 2 – 1+ cos 2 (x)

3 - x-sin 2 (x)

12x - 3- lg 2 (x)

5x + 6cos 2 (x)

5x - x 3 - cos 2 (x)

3 + x 2 cos 2 (x)

3 + x 3 - tg 2 (x)

4x 2 - 9- lg 2 (x)

2 cos 2 (x)+ 5

cos2 (x) + x2

2x2-sin2(x)

4x 3 - cos 2 (x)

3ln 2 (x) + x 2

3sin(x) – x 3

4 + x + cos 2 (x)

4x 3 -sin 2 (x)

5x 2 + lg 2 (x)

2x 3 - x 2 + 7

4 cos 2 (x) + x 2

3x 2 – 5x cos 2 (x)

2sin(x) – x 2

3cos(x) + tg(x)

5 + x 3 -4 lg 2 (x)

4x3 – 2x2-7

5 cos 2(x) + 4x

Приложение

Таблица 1 продължава

Задача за студенти от специалността

f(х)

ж(х)

3x -sin 2(x)

1 + x 2 cos 2 (x)

12x - 3 cos 2 (x)

5x - x 2 + 3

5 + x 2 + 10x

2cos 2(x) + 5x

2x2-sin(x)

9x3 - cos(x2)

5sin 2 (x) + x 3

3sin(x) – x 3

3x2-sin(x3)

8x3-x2+1

2sin 2 (x 2) - x

4cos(x 3) - 3x

4x3 - 2x2 + 7

САМАРСКА ДЪРЖАВНА АКАДЕМИЯ ПО КОМУНИКАЦИИ

Катедра Информатика

ИНФОРМАТИКА

Електронна таблица MS Excel

Указания за изпълнение на лабораторни упражнения

за студенти от специалност OPU от всички форми на обучение

Съставител: Макарова I.S.

Ермоленко Т.И.

Самара 2006 г


Информатика. Електронна таблица MS Excel. [Текст]: методически указания за извършване на лабораторни упражнения за студенти от специалността на ОП на всички форми на обучение. - Част 2 / съставители: И.С. Макарова, Т.И. Ермоленко. - Самара: SamGAPS, 2006. - 44 с.

Приет на заседание на катедра „Информатика“ от 06.04.2006 г., протокол № 8.

Издава се с решение на редакционно-издателския съвет на академията.

Тези насоки са практическо ръководствовърху овладяването на техниките за работа в популярен процесор за електронни таблици Microsoft Excel. Разглеждат се основните елементи на интерфейса, техниките и технологиите за работа с данни, необходими за създаване на таблици, извършване на изчисления и конструиране на диаграми. Разглеждат се допълнителни възможности на MS Excel, като работа с текстови функции, математически изчисления, анализ на данни. За овладяване на работата в електронна таблица ще помогне изпълнението на предложените практически задачи, които съдържат подробни инструкции стъпка по стъпказа да получите крайния резултат.

Използването на тези указания предполага, че учениците познават основите на работа с операционна обвивка Windows.

Редактор: E.A. Краснова

Компютърно оформление: R.R. Абраамян

Подписано за печат на 15.06.06г. Формат 60х90 1/16.

Хартия за писане. Печатът е работещ. Реал. п.л. 2,75.

Тираж 200 бр. Заповед № 118.

© Самара държавна академиясредства за комуникация, 2006г

Въведение

Microsoft Excel е доста мощен и лесен за използване електронен продукт процесор за електронни таблици, предназначени за решаване на широк спектър от планови и икономически, счетоводни и статистически, научни, технически, математически и други проблеми. MS Excel се основава на работа с електронни таблици.

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

Excel поддържа файлови формати, маркирани с разширение xl*, а родните документи на Excel се намират във файлове с разширение xls.

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

Лабораторна работа №1. Основи на работа с MS Excel

Цел на работата: запознайте се с основните елементи на процесора за електронни таблици, методите за въвеждане на информация в таблици, техники за форматиране


При стартиране на MS Excel ( Старт/Програми/Microsoft Excel ) на екрана се появява прозорец на електронна таблица със зареден в него документ, който се нарича Работна книга (фиг. 1):

Ориз. 1. Прозорец на MS Excel

прозорец Excel програмисъдържа всички стандартни елементи, присъщи на прозорец на приложение на Windows:

икона на програмата

заглавен ред;

лента с менюта

ленти с инструменти;

Лента за състоянието

ленти за превъртане.

Лентата с менюта на Excel се различава от лентата с менюта на Word с командата Данни (вместо Таблица ). Лентата с инструменти има специални бутониза числови данни - паричен и процентен формат; разделител на хиляди; увеличаване и намаляване на битовата дълбочина на число; бутон за обединяване и центриране на текст в група от клетки.

Под лентата с инструменти е Лента с формули, който се използва за въвеждане и редактиране на данни в клетки. От лявата страна на лентата с формули има падащ списък − Поле за име, който показва адреса на текущата клетка. В същия ред при въвеждане на формули се появяват три бутона за управление на процеса на въвеждане.

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

Под работното поле има линия с етикети на работния лист.

Обмисли основни понятия на MS Excel.

Документът на Excel се извиква работна книга,състои се от колекция работни листове. По подразбиране всяка работна книга съдържа 3 работни листа, но техният брой може да се променя от 1 до 255. Работният лист има таблична структура и се състои от 65 536 реда и 256 колони. Редовете са номерирани, а колоните са обозначени с латински букви. азбуки A,B,C, …, Z, AA, AB, AC,…, BA, BB,…, IV.

активен лист(текущ лист) на работна книга е листът, върху който потребителят работи в момента. Разделът на активния лист винаги има по-светъл цвят на фона, като името му се показва с удебелен шрифт. Като щракнете върху етикетите, можете да се придвижвате от един лист към друг в работната книга. За придвижване през листовете на работната книга можете да използвате и клавишните комбинации: Ctrl + Page Down и Ctrl + Page Up или група от четири бутона, разположени в долния ляв ъгъл на работния прозорец на Excel.

В пресечната точка на ред и колона е клетка- най-малката структурна единица на работния лист. Всяка клетка има адрес, който се образува от името на колоната и номера на реда, в пресечната точка на който се намира. Така адресът на клетка C7 означава, че тази клетка се намира в пресечната точка на колона C и ред 7 на текущия работен лист. В случаите, когато е необходимо да се направи препратка към клетки, разположени на други работни листове, името на работния лист, на който се намират, се посочва преди адреса (например Sheet4!G9).

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

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

Клетъчен блок(обхват) - представлява правоъгълна област от съседни клетки. Блок от клетки може да се състои от една клетка, ред (или част от него), колона (или част от нея) или поредица от редове или колони (или части от тях). Блокиране на адресе комбинация от адресите на горната лява и долната дясна клетка на блока, разделени с двоеточие. Например, блок с адрес "A3:B5" съдържа следните шест клетки: A3, A4, A5, B3, B4, B5.

Excel съдържа над 400 вградени функции. За да улесните работата с вградените функции, използвайте Съветник за функции.

ЗАДАЧА 1. Запознаване с интерфейса на програмата Excel

1. Стартирайте електронната таблица на Excel . Автоматично ще се отвори документ, наречен Book1.

1. Определете броя на листовете в Book1. Поставете чрез контекстното меню Добавете… - Лист два допълнителни листа. Обърнете внимание на имената на новите листове и тяхното разположение .

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

3. Запишете работната книга във вашата папка като файл с име table.xls.

ЗАДАЧА 2. Избиране на клетки, редове, колони, блокове и листове

2. Опитайте го различни начиниизбор на фрагменти от електронната таблица (виж таблица 1).

маса 1

Изберете обект Оперативна техника
клетка Кликнете върху клетка
Линия Кликнете върху съответния номер на ред
Колона Кликнете върху съответния номер (буква) на колоната
Блок (диапазон) от съседни клетки 1. Поставете курсора в началото на селекцията (горната лява клетка на избрания блок). Натиснете левия бутон на мишката. Плъзнете курсора диагонално до долния десен ъгъл на избрания блок 2. Щракнете върху най-крайната ъглова клетка на избрания блок, натиснете клавиша Shift и щракнете върху противоположната ъглова клетка
Група от несъседни клетки Изберете първата клетка в групата. Натиснете и задръжте клавиша Ctrl Изберете останалите клетки в групата
Блокове от несъседни клетки Изберете блок от съседни клетки. Натиснете клавиша Ctrl Изберете следващия блок от клетки
Работен лист Кликнете върху бутона „Избор на всички“ в горния ляв ъгъл на работния лист
Няколко съседни работни листа Изберете първия работен лист. Натиснете клавиша Shift и, без да го пускате, изберете последния работен лист
Множество несвързани работни листове Изберете първия работен лист. Натиснете клавиша Ctrl и, без да го пускате, изберете следващия работен лист

3. Демаркирайте групата листове, като щракнете върху раздела на всеки неактивен лист.

4. Активирайте Лист 2като щракнете върху етикета му.

5. Изберете клетка с мишката C6.Върни се в килията A1с помощта на клавишите със стрелки.

6. Направете го актуален (активен) Лист 5.Изтрий Лист 5с помощта на контекстното меню.

7. Вмъкнете нов лист, като използвате командата от менюто Поставете. внимание! Име на новия лист - Лист 6.

8. Използвайте мишката, за да преместите раздела Лист 6след етикета Лист 4.

9. Върнете се към Лист 1.Дайте му име, като използвате контекстното меню Таблица.

10. Отидете на Лист 2.Маркирайте линия 3. Премахнете избора, като щракнете върху която и да е неизбрана клетка с левия бутон на мишката.

11. Маркирайте колона Д.

12. Маркирайте колони заедно B, C, D.Демаркирайте.

13. Изберете диапазон от клетки (блок) C4:F9с помощта на мишката. Демаркирайте.

14. Изберете блок A2:E11при натискане на клавиша смяна.

15. Изберете едновременно несъседни блокове A5:B5, D3:D15, H12, F5:G10.

16. Изберете цялата работа Лист 2.Демаркирайте.

ЗАДАЧА 3. Въвеждане на данни в клетки. Форматиране на клетки

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

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

За да отмените въвеждането на данни, натиснете клавиша Esc.

1. Към клетката А1 лист 2въведете текста Година на основаване на училище №147.

2. Към клетката B1въведете годината на основаване на училището 1965 г.

важно!

Текстовите данни са подравнени вляво в клетката, а числата са подравнени вдясно.

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

4. Променете ширината на колоната Атака че целият текст да се вижда в клетката . За да направите това, плъзнете десния разделител в заглавието на колоната (между буквите АИ INв заглавията на колоните) или щракнете двукратно върху разделителя на колони. Командите на менюто се използват и за промяна на ширината на колона. Формат / Колона / Ширина (AutoFit Ширина или Стандартна ширина).

5. Към клетката A2въведете текста Текуща година.

6. В клетката НА 2въведете стойност за текущата година.

7. В клетката A3въведете текста Училищна възраст.

8. Изберете клетка НА 3,въведете от клавиатурата формулата за изчисляване на възрастта на училището = B2- B1. В клетката ще се появи цифрова стойност, показваща възрастта на училището в години.

важно!

4 Въвеждането на формули винаги започва със знак за равенство = .

4Клетъчните адреси трябва да се въвеждат без интервали латинскиписма.

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

9. Променете ширината на първата колона, така че клетката да побира ширина приблизително 10 знака. Това може да стане "на око" с мишката или чрез щракване с десния бутон върху заглавието на колоната (буква А) и изпълнение на командата Ширина на колоната... (Това отново ще съкрати текста в клетките на първата колона.)

10. Изберете блока от клетки A1:A3 и изпълнете командата Формат/Клетки…

Отидете на отметка подравняванеи поставете отметка в квадратчето Движение по дума.

11. Обърнете внимание на маржовете Хоризонтално подравняванеИ вертикално. Запознайте се със съдържанието на падащите списъци на тези полета и задайте например опцията Наляво И Центрирано съответно. Кликнете Добре. Като резултат външен видклетките от първата колона ще се подобрят.

12. Изберете отново блока от клетки A1:A3 и изпълнете командата Формат/Клетки…

13. Отидете на отметка Шрифт. Задайте стила Удебелен курсив. Променете сами цвета на шрифта.

14. Отидете на отметка Прегледи изберете цвят за запълване на клетките.

15. Изберете блока от клетки A1:B3 и изпълнете командата Формат/Клетки…

16. Отидете на отметка Граница. Вижте възможните типове линии. Изберете вида и цвета на линията. След това щракнете Външени/или Вътрешенза задаване на границите на клетките (общият изглед може да се види в примерния прозорец). Кликнете Добре.

17. В клетката D1въведете текст Година на моето раждане .

18. В клетката E1въведете годината си на раждане.

19. В клетката D2въведете текста Текуща година.

20. В клетката E2въведете стойност за текущата година.

21. В клетката D3влез в моята възраст.

22. В клетката E3въведете формулата за изчисляване на вашата възраст.

23. Определете възрастта си през 2025 г. За да направите това, сменете годината в клетката E2за 2025г . Моля, обърнете внимание, че при въвеждане на нови данни таблицата е преизчислена автоматично.

24. Форматирайте клетките сами и ги форматирайте по същия начин като предишната таблица.

25. Преименуване Лист 2 V Опитвам.

26. Запазете работата си.

ЗАДАЧА 4. Операции за преместване, копиране и изтриване на съдържанието на клетки

1. Изберете клетка A1.Копиране на клетка A1като използвате десния бутон на мишката или бутона на лентата с инструменти Стандартен. Поставяне на съдържанието на клетка A1в клетка A5като използвате десния бутон или клавиатурата. Обърнете внимание, че не само съдържанието беше копирано, но и форматиращите елементи на клетката.

2. Копирайте отново клетката A1в клетка A7.

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

4. Върнете съдържанието на клетката A9в клетка A7.

5. Копирайте съдържанието на клетката с мишката A7в клетка A9.За да направите това, задръжте клавиша, докато се движите. ctrl.

6. Използване на команди от менюто Редактиране / Изрязване, и тогава Редактиране/поставяне преместете съдържанието на клетката A5в клетка A11.

7. Изберете клетка A11и натиснете клавиша Изтрий. Имайте предвид, че съдържанието на клетката е премахнато, но форматирането е запазено. За да ги премахнете, изпълнете командата Редактиране / Изчистване / Формати.

8. В клетка A7променете ориентацията на текста, така че текстът да е под ъгъл от 45° (команда от менюто Формат / Клетки , отметка подравняване).

9. В клетка A9позиционирайте текста вертикално.

10. Запазете работата си.

ЗАДАЧА 5. Автоматично попълване на клетки

1. Активирайте Лист 3. Преименувайте го на Автоматично довършване.

2. Към клетката E9въведете думата: сряда. Изберете клетка. Насочете мишката към маркера за автоматично довършване - квадратчето в долния десен ъгъл на рамката. Натиснете левия бутон на мишката и като го държите натиснат, преместете мишката няколко реда надолу.

3. Маркирайте отново клетката E9и го плъзнете за манипулатора няколко колони надясно.

4. Повторете операцията за плъзгане на клетка E9използвайки маркера още два пъти - нагоре и наляво.

5. Анализирайте резултатите и изчистете листа. За да направите това, щракнете върху празния бутон в горния ляв ъгъл на работния лист и натиснете клавиша Изтрий.

6. В клетката A1въведете числото 1. Плъзнете го с маркера надолу до 10-ия ред. Анализирайте резултата.

7. В клетката В 1въведете числото 1.

8. В клетката НА 2въведете номер 2.

9. Изберете блок от клетки B1: B2, плъзнете го за манипулатора 10 реда надолу. Анализирайте резултата.

10. В клетката C3въведете числото 1.

11. Плъзнете го за маркера Кликнете с десния бутонмишката 10 реда надолу. Пуснете левия бутон на мишката и ще се появи контекстното меню. Изберете команда от менюто Прогресия...

12. В отворения диалогов прозорец Прогресиятип набор - Аритметика , стъпка - 2 . Кликнете Добре

13. В клетката D1въведете текст: януари. Изберете клетката и плъзнете манипулатора 12 реда надолу.

14. В клетката E1въведете текста VAZ 2101. Плъзнете го с маркера 12 реда надолу. Анализирайте резултатите.

15. В клетката F1 Копиране на клетки . Анализирайте резултатите.

16. В клетката G1въведете текста VAZ 2101. Плъзнете го с маркера с десния бутон на мишката 12 реда надолу. В контекстното меню, което се отваря, изберете командата Напълнете . Анализирайте резултатите.

17. Запазете резултатите.

ЗАДАЧА 6: Създайте списък за автоматично довършване

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

1. Направете листа активен Автоматично довършване.

2. Изпълнете командата от менюто Сервиз / Опции .

3. Отидете до отметката Списъци.

4. Кликнете върху линията Нов списъкв полето Списъци. Въпреки това в полето Списък с елементище се появи текстовият курсор.

5. Въведете от клавиатурата фамилните имена на 10 студента от вашата група (след всяко фамилно име натиснете клавиша Въведете). След приключване на набирането натиснете бутона Добавете. Въведеният списък ще бъде в полето Списъци. Кликнете Добре.

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

7. За да редактирате отново списъка, изпълнете командата от менюто Сервиз / Опции и отидете на отметка Списъци.

8. В полето Списъциизберете списъка, който сте създали (той също ще се появи в полето Списък с елементиот дясната страна на прозореца). Изтрийте първото фамилно име и вместо него въведете фамилията Бармалеев .

9. Натиснете бутона Добавете, и тогава Добре.

10. Списък в колона зне се промени. Помислете защо. Какво трябва да се направи, за да се актуализира списъкът? Напишете отговора на този въпрос в полето A15.

11. Покажете резултата на учителя.

12. Премахнете създадения от вас списък от списъка със списъци.

13. Запазете работата си.

ЗАДАЧА 7. Планиране

1. Направете листа активен 4. Преименувайте го на График.

2. Към клетката A1въведете текста График на занятията за група # (посочете номера на вашата група) за текущата седмица.

3. В клетки A3-A6 въведете часовете на класа (8:30 - 10:00, 10:15 - 11:45 и т.н.)

4. В клетки B2 - F2 въведете имената на дните от седмицата (използвайте маркера за автоматично попълване).

5. Попълнете таблицата с имената на обектите, като използвате техники за копиране.

6. Изберете клетките от първия ред A1 - F1 и ги обединете с помощта на командата от менюто Формат / Клетки (отметка подравняване) или с помощта на бутона Слейте и поставете в центъра.

7. Стилизирайте заглавката на таблицата с командата Формат / Клетки.

8. Украсете основното поле на графика с помощта на граници и запълване.

9. Запазете работата си.

10. Покажете работата си на учителя.

Лабораторни работипревъзходен

Лаборатория №1

Създайте списък с клиенти

Въведете списък от 15 фирми. Фирмите са разделени в 5 града. След като въведете първия запис, щракнете върху бутона Добавете.
    Форматиране маси. За клетки I2-I14 задайте процентния стил (за да направите това, изберете този диапазон и щракнете върху бутона Процентен форматна лентата с инструменти Форматиране).



    Сортиране на данни.Трябва да се избере от менюто ДанниСортиране.Изберете първия критерий за сортиране в диалоговия прозорец Коди вторият критерий градИ ДОБРЕ. Филтриране на данни.Изберете от менюто ДанниФилтър/Атофилтър.Когато щракнете върху името на тази команда, ще се появи бутон със стрелка на първия ред до заглавието на всяка колона. Може да се използва за отваряне на списък, съдържащ всички стойности на полета в колона. Изберете името на един от градовете в град.В допълнение към стойностите на полето, всеки списък съдържа още три елемента: (Всички), (Първите 10…) и (Условие…).елемент (Всичко)е предназначен да възстанови показването на всички записи на екрана след прилагане на филтъра. елемент (Първите 10…)осигурява автоматично показване на първите десет записа в списъка. Ако се занимавате със съставяне на всякакви рейтинги, основната задачакойто трябва да определи първите десет, използвайте тази функция. Последният елемент се използва за формиране на по-сложен критерий за избор, в който могат да се прилагат условни оператори. ИИ ИЛИ. Поставете курсора във всяка попълнена клетка и направете следното: в менюто форматАвтоматичен форматСписък 2 .

Създаване на списък с продукти

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

Лаборатория №2

Поръчки на листове

    Преименувайте работния лист ListZадресиран Поръчки.

    Въведете следните данни в първия ред, които ще бъдат имената на полетата в бъдеще:
    A1Месец на поръчка , В 1дата на поръчка , СЪС 1 Номер на поръчка , д1 Номер на артикул , E1Име на продукта , Е1 Количество , Ж1 цена за един ., з1 Фирмен код на клиента ., аз1 Име на фирмата на клиента , Дж1 Цена на поръчката , К1Отстъпка (%) , Л1 Общо платено .

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

    Изберете колони една по една B, C, D, E, F, G, H, I, J, K, L и влезте поле имеимена Дата, Поръчка, Номер2, Артикул2, Количество, Цена2, Код2, Фирма2, Сума, Отстъпка2 И Плащане .

    Маркирайте колона INи изпълнете командата от менюто формат клетки. В раздела Номеризберете
    Числен формат дата, и в полето Типизберете формата ЧЧ.ММ.ГГ. В края на диалога
    щракнете върху бутона ДОБРЕ.

    Маркирайте колониЖ, Дж, Ли изпълнете командата от менюто формат клетки. В раздела Номер
    изберете Числен формат Парични , посочете Брой знаци след десетичната запетаяравно на 0, а в полето
    Избор на обозначение $ английски (САЩ). В края на диалоговия прозорец щракнете върху бутона Добре.

    Изберете колона K и изпълнете командата от менюто формат клетки. В раздела Номеризберете
    Числен форматПроцент , посочете Брой знаци след десетичната запетаяравна на 0. В края
    диалогов бутон за щракване Добре.

    В клетка A2трябва да въведете следната формула:

=АКО(ISBLANK($B2)," ";SELECT(MONTH($B2),"януари","февруари","март","април";"май"; "юни"; "юли"; "август"; "септември"; "октомври"; "ноември"; "декември")) (3.1)

И запълнете клетката в жълто.

Формула (3.1) работи по следния начин: първо се проверява условието за празнота на клетка A2. Ако клетката е празна, тогава се поставя интервал, в противен случай, като използвате функцията SELECT, изберете желания месец от списъка, чийто номер се определя от функцията MONTH.

За да получите формулата (3.1) направи следното:

    направи клетката активна A2и извикайте функцията АКО;

    в прозореца на функцията IF в полето Булев_изразвъведете ръчно $ B2="", V

поле стойност_ако_истинаТип " " , в полето стойност_ако_лъжаизвикване на функцията SELECT;

    в прозореца на функцията ИЗБОРв полето стойност1Тип " януари",в полето стойност2печат

в полето индекс_номери извикайте функцията МЕСЕЦ;

    в прозореца на функцията МЕСЕЦ в полето дата_като_номернаберете адреса $ б2 ;

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

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

=АКО($ D2=" "; “ ”; ТЪРСЕНЕ ($D2; Номер на продукт; Име на продукт) (3.2)

Правило за набор от формули:
Щракнете върху клетка E2. Поставете курсора върху иконата на стандартния панел. Ще се отвори прозорец Функция майстор...,изберете функцията IF. Следвайте стъпките, които виждате на снимката

Тези. в позиция Логически_изразщракнете върху клетка D2 и натиснете клавиша F4 три пъти - вземете $D2, въведете =" ", използвайте клавиша Tab или мишката, за да се придвижите до позицията стойност_ако_истинаи наберете. „ “, преместете се на позицията стойност_ако_лъжа– щракнете върху бутона до името на функцията и изберете команда Още функции.. → Категории → Препратки и масиви, в прозорец Функции → ПРЕГЛЕД→ OK → OK.

Ще се отвори прозорецът на функцията ПРЕГЛЕД. В позиция Търсена_стойностщракнете върху клетка D2 и натиснете клавиша F4 три пъти - вземете $D2, използвайте клавиша Tab или мишката, за да се придвижите до позицията Разгледан_вектори щракнете върху етикета на листа " Стоки”, изберете диапазон от клетки A2:A12, натиснете клавиша F4, отидете на позиция резултат_вектор– щракнете отново върху етикета на листа „ Стоки”, изберете диапазон от клетки Q2: W12, натиснете клавиша F4 и OK. Ако сте направили всичко правилно, то ще се появи в клетката # HD.

СЪС


запълване на клетка жълто цвят.

10. В клетката Ж2 въвеждаме следната формула:

=АКО($д2=" ";" ";ТЪРСЕНЕ($д2;Номер на продукта; Цена)) (3.3)

Направете запълване на клетка жълто цвят.

11. В клетката аз2 въвеждаме следната формула:
=АКО($H2=" ";" ";ТЪРСЕНЕ($з2;Код; твърд)) (3.4)
Направете запълване на клетка жълто цвят.

12. В клетката Дж2 въвеждаме следната формула:
=АКО(Е2=" ";" ";Е2* Ж2) (3.5)
Направете запълване на клетка жълто цвят..

13. В клетката К2 въвеждаме следната формула:
=АКО($H2=" ";" ";ТЪРСЕНЕ($з2;Код; Отстъпка)) (3.6)
Направете запълване на клетка жълто цвят.

14. В клетката Л2 въвеждаме следната формула:
=АКО(Дж2=" ";" ";Дж2- Дж2* К2) (3.7)
Направете запълване на клетка жълто цвят.

15. Клетки B2, D2 и H2 - в които няма формули, попълнете син цвят. Маркирайте диапазон A2 - Л 2 и манипулатор за запълване ( черен кръст в долния десен ъгъл на блока ) екструдиране на запълване и формули до 31 включени линии.

16. Направете клетката активна НА 2и плъзнете манипулатора за попълване надолу към клетката VZ1включително.

17. В клетката C2въведете числото 2008-01, което ще бъде номерът на началната поръчка и плъзнете надолу маркера за запълване до клетката° СZ1включително.

18. Сега трябва да попълните колоните от клавиатурата Q2: W31 , д2: д31 И H2: H31. СЪС НА 2от НА 11събираме януарски дати (например 01/2/08, 01/12/08). СЪС НА 12от НА 21събираме дати от февруари (например 12.02.08, 21.02.08) и от B22от B31събираме мартенски дати (например 05.03.08, 06.03.08). IN д2: д31 набираме номерата на стоките, т.е. 101, 102, 103, 104, 201, 202, 203, 204, 301, 302 и 303. Числата могат да се повтарят и да вървят в произволен ред, подобно на H2: H31влизам Кодовевашите фирми, които сте написали на листа клиенти.на колона Евлизам двуцифрен числа.

19.

(SRSP)Лаборатория #3

Формуляр за поръчка



    В клетка H5 въведете записа Код, и в клеткааз5 поставете формулата
    =АКО($д$3=" "; “ “;ПРЕГЛЕД($д$3;Поръчка; Код2)) Към клетката C7въведете запис Име на продукта. клетка д7 трябва да съдържа формулата
    =АКО($ д$3=" "; “ “;ПРЕГЛЕД($ д$3;Поръчка; Продукт2)),
    и клетки д7, Е7, Ж7 задайте подчертаване и центриране. Към клетката H7въведете символ № , и в клеткааз7 - формула:
    =АКО($ д$3=" "; “ “;ПРЕГЛЕД($ д$3;Поръчка; номер 2)) Към клетката C9въведете запис Поръчано количество. Към клетката E9- формула
    =АКО($ д     $3=" "; “ “;ПРЕГЛЕД($ д$3;Поръчка; количество)) Към клетката Е9 – запис единици по ценаи го подравнете към центъра на колоните ЕИ Ж. клетка H9трябва да съдържа формулата
    =АКО($ д     $3=" "; “ “;ПРЕГЛЕД($ д$3;Поръчка; Цена2)),
    тази клетка трябва да получи подчертаване и валутен стил.Към клетката аз9 – запис за единица Напишете в C11текст Обща стойност на поръчката, и в E11поставете формулата
    =АКО($ д     $3=" "; “ “;ПРЕГЛЕД($ д$3;Поръчка; Сума)),
    Към клетката Е11 – запис Отстъпка (%). Маркирайте Е11, Ж11, H11и щракнете върху бутона Обединяване и центриране . Към клетката аз11 поставете формулата
    =АКО($ д$3=" "; “ “;ПРЕГЛЕД($ д$3;Поръчка; Отстъпка2)),
    и задайте опции за форматиране: подчертан и процентен стил. Към клетката C13-текст Да платя.И то в клеткад13 публикувайте следната формула
    =АКО($ д$3=" "; “ “;ПРЕГЛЕД($ д$3;Поръчка; Плащане)),
    и задайте опции за форматиране: подчертаване и валутен стил. Към клетката E13въведете запис Проектирана от:, подчертайте E13, Е13 и задайте центрирането на текста. След това изберете Ж13, H13,аз13 и ги настройте да бъдат центрирани и подчертани. Накрая задайте ширината на колонитебИ Джравно на 1,57, маркирайте б2- Дж14 и задайте рамката за целия диапазон. Сега в E3посочете Номер на поръчка, и преди да отпечатате вашия формуляр фамилия.

    Успешно завършихте работата, предайте я на учителя!

осева таблица

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

Пивотните таблици се създават от списък или база данни.




8. Успешно завършихте работата, предайте я на учителя!

(SRSP) Lab. № 4. Клонове

    Създайте работна книга и я запазете във вашата папка като Клонове (вашето фамилно име).Нека започнем примера, като създадем таблица и въведем данни за всеки клон.

    Подготвителен етап.Копиране в клипборда от листа Стокикниги Поръчкиданни за стоките, техния брой и цени, т.е. копирайте диапазон от клетки А1-С12лист Стоки.

    Отидете на първата страница на книгата Клоновеи в клетката A3поставете копирания фрагмент от таблица. В 3 реда в клеткид3, д3, Е3 въведете съответно записите Брой поръчки, Продадено количествоИ Обем на продажбите. Задайте центриране на текста в клетките и разрешете обвиване на думи.

    Към клетката Е4 поставете формулата: \u003d C4 * E4и го копирайте в клетки Е5- Е14 .

    Въведете клетка B15дума Обща сума:, и в клеткаЕ15 вмъкнете формула за сума или щракнете върху бутона на лентата с инструменти Стандартен.превъзходен той ще определи диапазона от клетки, чието съдържание трябва да бъде обобщено.

    Трябва да има толкова листове, колкото градове сте имали в листа клиенти. Трябва да копираме този лист 4 пъти.

    За да направите това, поставете курсора на мишката върху неговия етикет и натиснете десния бутон на манипулатора. В контекстното меню изберете командата Преместване/Копиране, в диалоговия прозорец, който се появява, посочете листа, пред който да бъде вмъкнато копието, активирайте опцията Създайте копиеи натиснете Добре. Много по-лесно е да копирате с мишката: позиционирайте показалеца на мишката върху раздела на листа и го преместете в позиция за копиране и поставяне, като държите натиснат клавиша [ ctrl] .

    Имената на работните листове съответстват на заглавията градовеот листа клиенти, Например, Алмати, Астана, Шимкент, Актау, Карагандаили други имена. Въведете името на клона, съответстващо на името на листа и в клетката A1този лист.

    Попълнете листа Поръчкиоще една колона. Към клетката M1въведете дума град.Към клетката М2въведете формулата =АКО(ПРАЗЕН($ з 2);“ ”;ТЪРСЕНЕ($ з2;Код; град)) , разширете тази формула до ред 31 на тази колона.

    Изберете от менюто ДанниФилтър/Атофилтър.Изберете в колона град първи клон. Данни на колонаКоличестволист Поръчки ще бъдат въведени от вас в колонатаПродадено количество книжен лист клонове, в редовете, съответстващи на номерата на позициите. Ако стоки с еднакъв номер се продават през различни месеци, тогава се взема общото им количество. И така се попълват листовете на всички градове.

    Консолидиране на данни.Копирайте от първата страница на книгата Клоноведиапазон A3-B14, отидете на работен лист 6 и поставете в клетката A3.

    Да преминем към консолидацията. Задайте показалеца на клетката наC3и изберете от менюто ДанниКонсолидация.

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

    Поставете курсора за въвеждане в полето Връзка, кликнете върху етикета на първия град, например - Алмати, изберете диапазон от клеткид3- Е14 и натиснете бутона Добаветепрозорец Консолидация. В резултат на това посоченият диапазон ще бъде пренареден в полето Списък с диапазони.

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

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

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

    Бутон Прегледтрябва да се използва за избор на файла, който съдържа данните за консолидиране.

    Щракнете върху бутона ДОБРЕ.

    Към клетката A1въведете името на новата таблица Окончателни данни.

    Въведете клетка B70значение Обща сума:, и в E70 - и натиснете клавиша [ Въведете]

    Сега пристъпваме към определяне на дела от общата печалба от сумата, получена от продажбата на всеки продукт. Напишете в Е9 формула = E9/$д$70 и го копирайте в останалата част от колоната Е (към клетката Е70) .

    Форматиране на съдържанието на колонатаЕв процентен стил. Получените резултати ни позволяват да направим изводи за популярността на даден продукт.

    Когато консолидира данни, програмата записва всеки елемент в крайната таблица и автоматично създава структура на документа, което ви позволява да гарантирате, че на екрана се показва само необходимата информация и ненужните подробности са скрити. Структурните символи се показват отляво на таблицата. Числата показват нивата на структурата (в нашия пример - 1 И 2). Бутонът със знак плюс ви позволява да дешифрирате данни от по-високо ниво. Кликнете например върху бутона за клетка A9за получаване на информация за индивидуални поръчки.

    Копирайте формулата отЕ9 в клетките Е4- Е8.

Числата се превръщат в графики

    Подготвителна работа.Тъй като всяка диаграма се нуждае от собствена таблица, нека създадем нова обобщена таблица въз основа на данните от листа Поръчки книга със същото име Поръчки. Отворете предварително създадена работна книга Поръчки.Създайте нова работна книга и дайте име на първия й лист Таблица . Този лист ще съдържа цифровия материал за диаграмата. Поставете показалеца в клетката НА 3 и изберете меню ДанниПивотна таблица. Изберете първия начин за подреждане на данните − В списък или база данни Microsoftпревъзходен- Натисни бутона По-нататък. Във втората стъпка поставяне на курсора за въвеждане в полето Обхватпоследвано от меню прозорецотидете на работна книга Поръчки и работен лист Поръчки и маркирайте диапазонаА 1- Л 31 . След като натиснем бутона По-нататък. Структурата трябва да бъде дефинирана осева таблица. Място в района линии бутон Име на продукта, и в района колони - бутон месец. Сума ще се изчислява по поле цена на поръчката,тези. преместете този бутон в областта данни . Щракнете върху бутона Готов. Маркирайте диапазонб 4- Е 14 . Ако избирате диапазон от клетки с мишката, започнете избора от всяка клетка в диапазона, с изключение на клетката Е 4 A, който съдържа бутона PivotTable. Щракнете върху бутона Съветник за диаграмив лентата с инструменти Стандартен. В първата стъпка уточнете тип диаграма,щракнете върху бутона По-нататък. Потвърдете във втората стъпка диапазон =Таблица!$ б$4:$ Е$15. В третата стъпка уточнете опции за диаграма (Заглавия, оси, легенди и др.).Име на диаграмата влизам Обем на продажбите по месеци,Категории (X)- Име на продуктаИ Значение ( Y ) –Обем на продажбите (щатски долар) . Направените промени веднага ще бъдат отразени в изображението в полето. проба,щракнете върху бутона По-нататък. Кликнете върху бутона Готов.


Лаборатория №1

Целта на работата: да научите как да работите с електронни таблици и да научите как да изграждате различни диаграми.

Кратка теоретична информация

Excel е програма за електронни таблици.

Интерфейсът на прозореца на приложението Excel е подобен на този на прозореца на приложението Word (заглавна лента, лента с менюта, ленти с инструменти, лента на състоянието). Но се добавя лента с формули, която я няма в Word.

Има два вида показване на Excel документ - “normal” и “page layout”, които могат да бъдат зададени в меню View.

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

Работна тетрадка. Excel документ е работна книга , състоящ се от комплект работни листове съхранявани на диск в единичен файл . По подразбиране работната книга има 3 листа. Този номер може да се промени (до 255) в раздела Инструменти/Опции/Общи. Също така можете да добавяте или премахвате листове към книгата по всяко време (чрез контекстното меню с десния бутон). Листовете в книгата могат да бъдат залепени (Kn. Shift + щраква върху онези листове, които трябва да бъдат залепени). Информацията изписана върху залепените листове е една и съща. Например, ако трябва да създадете един и същ шаблон на таблица на няколко листа, трябва да ги залепите заедно, да създадете таблица веднъж, след което да „разгрупирате“ листовете чрез контекстното меню. Всички листове, които са били залепени заедно, ще имат една и съща маса.

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

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

Работен лист.Съдържа електронни клетки с адрес: А1, Б10 и др. Адресът на текущата клетка се показва в полето за име (най-лявото поле на лентата с формули). Работните листове съдържат 256 колони и 65536 реда. Заглавия на колони – A…Z,AA…AZ,BA…BZ. Заглавки на редове: 1 до 65536.

Клетъчни данни.Могат да се въведат две клетки мил данни: постоянни стойности и формули . Постоянните стойности се въвеждат директно в клетката, те не се променят при копиране. Формулите се използват за организиране на изчисления. При копиране на формули стойности на данните промяна в клетките.

Има два представителство клетъчни данни: в машината и на екрана . In-machine се използва за изчисления, това са вътрешните стойности на клетките, а не се показват на екрана. Представянето на дисплея се определя от формата на клетката.

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

Числа. Числата се съхраняват в машината с най-висока точност. Екранното представяне на число се определя от формата: Формат/ Клетки/ Число/ Числови формати. Можете да въвеждате цели числа, десетични знаци или числа в експоненциална (експоненциална) форма. Ако клетката е запълнена със знаци (диез), това означава, че въведеното число надвишава ширината на колоната.

Текст . Това е всеки въведен набор от знаци, който Excel не интерпретира като число, дата и час, булева стойност или стойност за грешка. Можете да въведете до 255 знака текст на клетка. За да въведете число като текст във формула, трябва да го поставите в кавички. = "45,00".

Форматиране на текст: Формат/Клетки/Подравняване на раздели, Шрифт, Граница, Външен вид.

дата и час .Датата е представена в машината като число, определено от броя дни от системната дата (1900) до датата, представена в

клетка. Това може да се види, ако изберете формат "Общ" в клетката с датата. Датата 01/22/2005 е еквивалентна на числото 38374 дни от 01/01/1900, а датата 01/07/2005 е еквивалентно на числото 38359 дни от 01/01/1900. Следователно операциите събиране и изваждане могат да се извършват върху дати (в клетки с дата "01/15/1900" и число "15" има формула =A1-B1, която изчислява броя на дните между датите "01 /22/2005-01/07/2005". Разликата е 15 ). Времето е представено в машината като дроб. Можете също да видите това, ако изберете формата „Общ“ в клетката с течение на времето. Времето 16:14 е еквивалентно на дробта 0,6763889.

Представянето на дисплея на датата и часа също се определя в менюто Формат/Клетки/Число/Формати на числа. За да въведете бързо текущия час в клетка, натиснете Ctrl +<:>, а за текущата дата –Ctrl+<;>.

Булеви стойности вземете стойностите "true" и "false". Тези стойности са резултат от логически и сравнителни операции.

Грешни стойности са резултат от грешни изчисления. Грешните стойности започват с остър знак: n/a! (невалидна стойност), връзка! (невалидна препратка), стойност! (грешен тип аргумент във функцията), име! (не разбира името), число! (не може да интерпретира правилно формулата в клетката) и др.

Обхват на клеткатае група от последователни клетки. Препратките към диапазони използват следните адресни операции:

: (двоеточие) - позволява ви да се обърнете към всички клетки между границите на диапазона, включително

значим (A1:B15);

, (запетая) - оператор за комбиниране на диапазони от клетки или отделни клетки

ب (интервал) е оператор за пресичане, който се отнася до клетки с общ диапазон,

Β5:B15ٮ A7:D7. В този пример клетка B7 се споделя между два диапазона.

Въвеждане, редактиране и форматиране на данни.

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

Директен – директно въвеждане на данни в текущата клетка. За да завършите въвеждането в текущата клетка и да преминете към следващата клетка, натиснете един от следните клавиши

Когато влезете същите данни в диапазон е необходимо: ​​Изберете диапазона - Въведете данни в активната клетка на диапазона - натиснете Ctrl + Enter.

Автоматизация на въвеждането.

Редактиране.

Операциите по редактиране могат да бъдат разделени на следните две групи:

    Редактиране въведени в клетка данни . Съдържанието на клетките може да се редактира както директно в клетката (щракнете двукратно върху клетката), така и в лентата с формули (щракнете върху дясната страна на лентата с формули), докато думата "Редактиране" се появява в лентата на състоянието. В този режим всички инструменти за редактиране стават достъпни.

    Редактиране на ниво клетки, диапазони, редове, колони. По принцип това са командите за редактиране на менютата "Редактиране" и "Вмъкване".

Форматиране.

В менюто "Форматиране" са концентрирани всички команди за форматиране на данни, редове, колони, листове и др.

Графики впревъзходен.

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

Обекти на диаграмата .ос(X е оста на категорията, Y е оста на стойността). точка за данни– един елемент от данните, например заплата за януари. Серии от данни- набор от точки от данни (ясно видими на графиката - всички точки от серията данни са свързани с една линия). Легенда– икони, модели, цветове, използвани за разграничаване на серии от данни. маркер за данни– представлява точка от данни на диаграмата като правоъгълник, сектор, точка и т.н., типът на маркера зависи от типа на диаграмата; всички маркери от една и съща поредица от данни имат еднаква форма и цвят. Текст– всички етикети (заглавие на диаграмата, стойности и категории по осите) и етикети (тест, свързан с точки от данни); за надписи можете да използвате иконата "надпис" в панела за рисуване или да създадете плаващтекст : щракнете върху един от редовете с данни - въведете теста (ще се появи в лентата с формули) - натиснете "Enter".

правила , използвани от Excel по подразбиранепри изграждане на диаграми.

1. Excel приема, че серията от данни за диаграма е по дългата страна на избрания диапазон от клетки.

2. Ако е избран квадратен диапазон от клетки или той заема повече клетки по ширина, отколкото по височина, тогава имената на категориите ще бъдат разположени в горния ред на диапазона. Ако има повече клетки по височина, отколкото по ширина, имената на категориите отиват надолу в лявата колона. И ако клетките, които Excel ще използва като имена на категории, съдържат числа (не текст или дати), тогава Excel приема, че тези клетки съдържат поредица от данни, и номерира имената на категориите като 1, 2, 3, 4 и т.н.

3. Excel приема, че заглавията по късата страна на селекцията трябва да се използват като етикети на легенди за всяка серия от данни. Ако има само една поредица от данни, Excel използва това име като заглавие на диаграмата. И ако клетките, които Excel възнамерява да използва като етикети на легенди, съдържат числа (не текст или дати), тогава Excel приема, че тези клетки съдържат първите точки от серията данни, и присвоява име на всяка серия от данни: "Серия1", " Серия2" и т.н. d.

Макроси. Служи за автоматизиране на повтарящи се операции в Excel. Макросът се състои от поредица от вътрешни команди на Excel (макрос). В Excel макросът се създава с помощта на командата Tools/Macro/Start Recording. Тази команда ви позволява да създадете макрос с помощта на записващо устройство за макроси (начин за запис на програма). Паралелно с действията на потребителя, записващото устройство за запис на макроси регистрира действията на потребителя, като автоматично ги превежда на свой собствен макро език. По този начин можете да създавате сравнително прости програми, които работят без намеса на потребителя.

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

Задачи за лабораторна работа №1.

    Създайте електронна таблица по указание на учителя.

    Изградете две диаграми въз основа на тази таблица:

    1. хистограма с една у-ос;

      диаграма с главната и второстепенната Y-оси, като представя две серии от данни под формата на графики.

    Създайте смесена диаграма, в която една серия от данни е представена като хистограма, а втората серия от данни е представена като графика. Задайте серията данни в редактора на Word, запазете файла с разширение .txt, след което импортирайте този файл от програмата Excel. Данните се предоставят от инструктора.

    Създайте макрос (по указание на учителя).

Copyright JSC "Централно конструкторско бюро "BIBCOM" & LLC "Agency Book-Service" Федерална агенция за образование Държавна образователна институция за висше професионално образование "Казански държавен технологичен университет" Лабораторна работа по информатика MS EXCEL Насоки Казан 2006 Copyright JSC "Central Design Bureau " BIBCOM" & LLC "Kniga-Service Agency" УДК 658.26:66.094 Съставител: ст.н.с. Е.С. Воробьов, ст.н.с. Е.В. Николаева, ст.н.с. F.I. Воробиева Лабораторна работа по информатика. MS Excel: Метод. инструкции / Казан. състояние технолог. un-t; Състав: E.S. Воробьов, Е.В. Николаев, Ф.И. Воробьов. - Казан, 2006. - 58 с. Очертани са основните методи за работа в пакета MS Excel, процедурата и правилата за създаване и редактиране електронни таблиции диаграми, извършване на основни изчисления, сортиране и филтриране на данни, анализиране и обобщаване на данни и използване на логически изрази, функции за обобщение и разпределение и матрични операции. Отделна лабораторна работа е посветена на намирането на решение на едно- и двупараметрична задача. Те могат да се използват при изучаването на дисциплините "Информатика", "Използване на компютрите в техниката" и "Използване на компютрите в изчисленията", могат да служат като наръчник за извънаудиторна работа на студентите, а също така могат да се използват от специалисти от всяка предметна областза самостоятелно развитие на компютърните технологии. Предназначен за пълно работно време и формуляри за кореспонденцияобучение по специалности 240802.65 „Основни процеси на химическото производство и химическа кибернетика” и 240801 „Машини и апарати на химическото производство”, студенти от направление 2480800 „Енерго- и ресурсоспестяващи процеси в химическа технология, нефтохимия и биотехнологии“. I л. 68, табл. 1, библиография. 5 имена Изготвен в катедра Обща химична технология. Публикува се с решение на редакционно-издателския съвет на Казанския държавен технологичен университет. Рецензенти: Б.К. Курбатов, доцент на KSTU. НА. Туполева Е.А. Мухутдинов, доцент, Казански държавен технологичен университет, Казански държавен технологичен университет, 2006 г. Excel таблици 1. Бягайте Програма на Microsoft Excel: щракнете върху бутона Старт; в менюто, което се показва, изберете Програми; изберете Microsoft Excel от изскачащото меню. 2. Разгледайте внимателно прозореца на Microsoft Excel (фиг. 1). Много хоризонтални елементи от менюто и бутони на лентата с инструменти са същите като елементите от менюто и бутоните в прозореца на редактора на Word. Работното пространство обаче има съвсем различен вид, което представлява маркирана таблица, състояща се от клетки с еднакъв размер. Една от клетките е ясно селектирана (в рамка с черна рамка - табличен курсор). Как да изберете друга клетка? За да направите това, просто щракнете върху него с мишката и показалецът на мишката на тази фиг. 1 път трябва да изглежда като лек кръст. Опитайте да маркирате различни клетки на таблицата. Използвайте лентите за превъртане, за да се движите около масата. 3. За да въведете текст в една от клетките на таблицата, трябва да я изберете и веднага (без да чакате, както в Word процесора за текстовия курсор) да „запишете“. Изберете една от клетките в таблицата и „напишете“ в нея името на днешния ден от седмицата. 4. Основната разлика между работата на електронните таблици и текстовия процесор е, че след въвеждане на данни в клетка, те трябва да бъдат фиксирани, т.е. изяснете на програмата, че сте приключили с въвеждането на информация -3- Copyright OJSC "Central Design Bureau" BIBCOM " & LLC "Agency Book-Service" в тази конкретна клетка. Можете да коригирате данните по един от следните начини: натиснете клавиша (Enter); щракнете върху друга клетка; използвайте курсорните клавиши на клавиатурата (стрелки). Запишете данните, които сте въвели. 5. Изберете клетката на таблицата, съдържаща деня от седмицата, и използвайте бутоните за подравняване на абзаци. Как се извършва подравняването? Направете заключение. След всички експерименти не забравяйте да върнете първоначалното подравняване - вляво, в бъдеще ще бъде важно. 6. Вече забелязахте, че таблицата се състои от колони и редове, като всяка от колоните има собствено заглавие (A, B, C ...), а всички редове са номерирани (1, 2, 3 ...) (фиг. 1). За да изберете цялата колона, просто щракнете върху нейната заглавка; за да изберете целия ред, щракнете върху нейната заглавка. Изберете цялата колона на таблицата, в която се намира името на деня от седмицата, който сте въвели. Какво е заглавието на тази колона? Изберете целия ред от таблицата, в който се намира името на деня от седмицата. Какво е заглавието на този ред? Използвайте лентите за превъртане, за да определите колко реда има таблицата и какво е името на последната колона. 7. Изберете клетката от таблицата, която е в колона C и ред 4. Обърнете внимание на факта, че в полето „Име“ (фиг. 1), разположено над заглавието на колона A, адресът на избраната клетка C4 се появи. Маркирайте друга клетка и ще видите, че адресът е променен в полето Име. Какъв е адресът на клетката, съдържаща деня от седмицата? 8. Нека си представим, че в клетката, съдържаща деня от седмицата, трябва да добавите и част от деня. Изберете клетката, съдържаща деня от седмицата, въведете името на текущата част от деня от клавиатурата, например "сутрин", и фиксирайте данните с натискане на клавиша (Enter). Какво стана? Част от деня не беше "добавена" към клетката и новите данни замениха оригиналните данни и вместо деня от седмицата получихте част от деня. Тоест, ако изберете клетка от таблица, съдържаща някои данни, и въведете нови данни от клавиатурата, клетката от таблицата ще съдържа най-новата информация. Как да допълня съдържанието на клетка от таблица (редактиране), без да въвеждам отново всички данни? Ако изберете клетката, съдържаща частта от денонощието, ще видите, че нейното съдържание се дублира в "Лента с формули", разположена над заглавията на колоните (фиг. 1). В лентата с формули можете да зададете традиционния текстов курсор с едно щракване на мишката, да направите всички необходими промени и след това да коригирате окончателната версия на данните. Изберете клетката на таблицата, съдържаща частта от деня, поставете текстовия курсор пред текста в лентата с формули и въведете отново деня от седмицата. Коригирайте данните. Трябва да получите следната картина (фиг. 2). -4- Copyright JSC "Central Design Bureau" BIBCOM " & LLC "Agency Book-Service" 9. Вижда се, че записът излезе извън клетката си и зае вторник сутринта част на съседната. Това се случва само когато съседната клетка е празна. Да вземем Фиг. 2 попълнете и проверете промените. Изберете клетката от таблицата, разположена вдясно от клетката, съдържаща вашите данни (клетката, до която са „дошли“), и въведете произволен текст в нея. Сега се вижда само тази част от вашите данни, която се побира в клетката (фиг. вторник, петък 3). Как да видите цялата публикация? И фиг. 3 отново, "Formula Bar" ще ви дойде на помощ. Именно в него можете да видите цялото съдържание на избраната клетка. И така, "Лентата с формули" ви позволява да: правите промени в съдържанието на избраната клетка; преглед на съдържанието на клетката, ако записът не се вижда изцяло. Изберете клетката, съдържаща деня от седмицата и част от деня, и вижте пълното съдържание на клетката в лентата с формули. 10. Как мога да увелича ширината на колоната, така че в клетката да се виждат едновременно деня от седмицата и част от деня? За да направите това, преместете показалеца на мишката до дясната граница на заглавието на колоната, "хванете" момента, в който показалецът на мишката се превърне в черна двойна стрелка и като задържите левия бутон на мишката, преместете границата на колоната надясно. Колоната се разшири. По същия начин можете да промените височината на линията. В този случай курсорът, когато се приближи до долния край на заглавката на реда, приема формата. Променете ширината на колоната, съдържаща деня от седмицата и част от деня, така че целият въведен текст да се вижда в клетката на таблицата. 11. Често се случва да трябва да изберете повече от една клетка и не цяла колона, а блок от клетки (няколко клетки, разположени една до друга). 12. За да направите това, поставете показалеца на мишката в най-крайната клетка на селекцията и с натиснат ляв клавиш преместете мишката до противоположния край на селекцията (целият избран блок е "покрит" от рамката, всички клетки , с изключение на този, от който започва селекцията, са боядисани в черно) . Моля, имайте предвид, че по време на процеса на подбор, "Поле за име" регистрира броя на редовете и колоните, които попадат в селекцията. В същия момент, когато отпуснете левия клавиш, в полето "Име" се изписва адреса на клетката, от която е започнала селекцията. Изберете блок от клетки, започвайки с клетка A1 и завършвайки с клетката, съдържаща „петък“. За да изберете цялата таблица, използвайте бутона "празен" ъгъл над заглавието на първия ред. -5- Copyright JSC "TsKB "BIBCOM" & LLC "Agency Book-Service" Изберете цялата таблица. Премахнете селекцията, като щракнете върху произволна клетка. 13. Как се изтрива съдържанието на клетка? За да направите това, просто изберете клетка (или блок от клетки) и натиснете клавиша (Изтриване) или използвайте командата от хоризонталното меню "Редактиране" ⇒ "Изчистване". Изтрийте всички ваши записи. Упражнение 2 Прилагане на основни техники за електронни таблици: Въвеждане на данни в клетка. Форматиране на шрифта. Промяна на ширината на колоната. Автоматично довършване, въвеждане на формула, рамкиране на таблица, подравняване на текста към центъра на селекцията, набор от долен и горен индекс Нека направим таблица, която изчислява n-тия член и сумата от аритметична прогресия. Първо, нека си припомним формулата за n-тия член на аритметичната прогресия: an = a1 + d (n − 1) и формулата за сбора на първите n членове на аритметичната прогресия: n S n = (a1 + an ) ⋅ , 2 където a1 е първият член на прогресията, а d е разликата на аритметичната прогресия. На фиг. 4 е таблица за изчисляване на n-тия член и сумата от аритметична прогресия, чийто първи член е -2 и разликата е 0,725. Ориз. 4 Преди да направите упражнението, измислете своя собствена аритметична прогресия, т.е. д. Посочете своя първи член на прогресията и разликата. Упражнението може да се разложи на следните стъпки: изберете клетка A1 и въведете в нея заглавието на таблицата „Изчисляване на n-тия член и сумата от аритметична прогресия“. Заглавието ще бъде поставено в един ред и ще заеме няколко клетки вдясно от A1; в клетка A2 въведете "d", в клетка B3 - "n", в C3 - "an", в D3 - "Sn". За да зададете индекси, първо въведете целия текст, който трябва да бъде в клетката (например an), след това влезте в "Лента с формули", изберете текста, който трябва да бъде индекс (например n), отворете командата "Форматиране " ⇒ „Клетки ...“ (в диалоговия прозорец, който се отваря, има само един раздел „Шрифт“) и активирайте превключвателя „долен индекс“ в групата „Промяна“; Изчисляване на n-тия член и сумата от аритметичната прогресия d n an Sn 0,725 1 -2 -2 0,725 2 -1,275 -3,275 0,725 3 -0,55 -3,825 0,725 4 0,175 -3,65 0,725 5 0,9 -2,75 0,725 6 1, 625 -1.125 0.725 7 2.35 1.225 0.725 8 3.075 4.3 0.725 9 3.8 8.1 0.725 10 4.525 12.625 -6- Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" изберете попълнените четири клетки. Използвайте съответните бутони на лентата с инструменти, за да увеличите размера на шрифта с 1 пункт, да го центрирате и да приложите удебелен стил на символа. Заглавката на таблицата е в рамка. Сега можете да започнете да попълвате таблицата. 1. В клетка A3 въведете стойността на разликата на аритметичната прогресия (в нашия пример това е 0,725). 2. След това трябва да попълните ред от долни клетки със същия номер. Безинтересно и нерационално е да въвеждате едно и също число във всяка клетка. В редактора на Word използвахме техниката copy-paste. Excel прави още по-лесно попълването на клетки с едни и същи данни. Изберете клетка A3, която съдържа разликата на аритметичната прогресия. Избраната клетка е оградена с рамка, в долния десен ъгъл на която има малък черен квадрат - манипулатор за запълване. Ако поставите показалеца на мишката до маркера за запълване и в момента, когато показалецът на мишката приеме формата на черен кръст, плъзнете маркера за запълване надолу в няколко клетки (в този случай се появява подсказка вдясно от курсора, какво стойността е въведена в текущата клетка), тогава целият ред от избрани клетки ще бъде запълнен с данните, разположени в първата клетка. По този начин попълнете още девет клетки под клетка A3 със стойността на разликата на аритметичната прогресия. 3. Следващата колона съдържа поредица от числа от 1 до 10. И отново, маркерът за попълване ще ни помогне да попълним реда. Въведете числото 1 в клетка B3, числото 2 в клетка B4, изберете и двете клетки и като хванете манипулатора за попълване, го плъзнете надолу. Разликата с попълването с едни и същи данни е, че избирайки две клетки, вие сте посочили принципа, по който да се попълнят останалите клетки. Маркерът за запълване може да се плъзга не само надолу, но и нагоре, наляво или надясно и запълването ще се разпространи в същите посоки. Елементът за запълване може да бъде не само формула или число, но и текст. Можете да въведете "януари" в клетката и, като попълните реда по-вдясно, ще получите "февруари", "март" и "разтягайки" маркера за запълване от клетката "януари" съответно наляво, ще получите " декември“, „ноември“ и др. Опитайте да направите това извън таблицата, която създавате. Най-важното е, че преди да разпространите селекцията, изберете точно клетката (или тези клетки), върху които е форматиран пълнежът. 4. Третата колона съдържа n-ти членове прогресии. Въведете в клетка C3 стойността на първия член от аритметичната прогресия. В клетка C4 трябва да въведете формула за изчисляване на n-тия член на прогресията, която се състои в това, че всяка клетка от колоната се различава от предишната чрез добавяне на разликата на аритметичната прогресия. Всички формули започват със знак за равенство. За да въведете формула в клетка, трябва: да активирате клетката; -7- Copyright OJSC "TsKB" BIBCOM " & LLC "Agency Book-Service" въведете знака "=" от клавиатурата или щракнете върху бутона "Промяна на формулата" в лентата с формули; въведете (без интервали) необходимите стойности или препратки, както и необходимите оператори; фиксирайте входа. Адресът на клетката се въвежда във формулите с латински регистър. Ако записът е направен в руски случай, тогава се появява съобщение за грешка "#NAME?". Изберете клетка C4 и въведете в нея формулата = C3 + A4 (не забравяйте да превключите на латиница и вместо да препращате към клетка A4, можете да въведете конкретната стойност на разликата на вашата аритметична прогресия). Не можете да въведете от клавиатурата адреса на клетката, към която е направена връзката. След като въведете знака за равенство, щракнете върху клетка C4 и нейният адрес ще се появи в лентата за формули, след което продължете да въвеждате формулата. В този случай не е необходимо да превключвате на латиница. След пълно въвеждане на формулата, коригирайте я, като натиснете (Enter), резултатът от изчислението ще бъде в клетката, а самата формула в "Лентата с формули". Появи се друга функция на „Лентата с формули“: ако в клетка видите резултата от изчисленията с помощта на формула, тогава самата формула може да се види в „Лентата с формули“, като маркирате съответната клетка. Ако сте въвели формула неправилно, можете да я коригирате в лентата с формули, като първо изберете клетката. Изберете клетка C4 и, подобно на запълването на клетките с разликата в прогресията, попълнете формулата, като плъзнете манипулатора за запълване надолу, ред от клетки, под C4. Изберете клетка C8 и погледнете в лентата с формули, за да видите как изглежда формулата, изглежда като =C7+A8. Прави впечатление, че препратките във формулата са се променили спрямо отместването на самата формула. 5. По същия начин въведете формулата = (-2 + C3) * B3 / 2 в клетка D3, за да изчислите сумата от първите n членове на аритметична прогресия, където вместо -2 трябва да има първия член на измислената от вас аритметика прогресия. Изберете клетка D3 и попълнете долните клетки с формули, като плъзнете манипулатора за попълване надолу. 6. Сега всички клетки са попълнени с данни, остава само да ги подредите. Всички колони са с еднаква ширина, въпреки че съдържат информация с различни размери. Можете ръчно (с помощта на мишката) да промените ширината на отделните колони или можете автоматично да регулирате ширината. За да направите това, изберете всички клетки на таблицата, съдържащи данни (не цели колони, а само блок от попълнени клетки без заглавието „Изчислете n-тия член и сумата от аритметична прогресия“) и изпълнете командата „Форматиране“ ⇒ „Колона“ ⇒ „Автоматично напасване на ширина“. 7. Сега нека форматираме заглавието на таблицата "Изчисляване на n-тия член и сумата от аритметична прогресия". Изберете клетка A1 и приложете удебелени знаци към съдържанието на клетката. Заглавието доста неестетично "изпълзява" надясно извън границите на нашата малка чиния. -8- Copyright OJSC "Централно дизайнерско бюро "BIBCOM" & LLC "Agency Book-Service" Изберете четири клетки от A1 до D1 и изпълнете командата "Format" ⇒ "Cells ...", изберете раздела "Alignment" и задайте превключвателите (фиг. 6): група "Подравняване" ⇒ "хоризонтално:" до позиция "център на селекцията"; група "Показване" ⇒ "Пренасяне по думи". Това ще ви позволи да подредите заглавието в няколко реда и в центъра на избрания блок от клетки. Масата беше почти доведена до 8. вид проба. Ако в този момент изпълните изгледа "Файл" ⇒ "Преглед", тогава се оказва, че остава да рамкирате таблицата. За да направите това, изберете таблицата (без заглавие) и изпълнете командата "Форматиране" ⇒ "Клетки ...". В диалоговия прозорец, който се отваря, изберете раздела "Граница", дефинирайте типа линия и активирайте превключвателите "Горе", "Долу", "Ляво", "Дясно" (фиг. 5). Тази процедура се прилага за всяка от клетките в избраната област. След това изберете блока от клетки, свързани с заглавката: от A1 до D2 и след като извършите същите операции, задайте превключвателя „Външен“. В този случай се получава рамка около всички избрани клетки, а не всяка. Извършете преглед. Ориз. 5 Фиг. 6 -9- Copyright JSC "Централно дизайнерско бюро "BIBCOM" & LLC "Agency Book-Service" Лабораторна работа №2 Упражнение 1 Консолидиране на основни умения за работа с електронни таблици, запознаване с понятията: сортиране на данни, видове подравняване на текст в клетка , формат на номера Изпращач и неговия адрес Получателят и неговият адрес До Регистър № Дата на получаване "___" ___________200__. ФАКТУРА № 123 от 15.11.2000 г. Доставчик Търговска къща "Рога и Копита" Адрес 243100, Клинци, ул. Пушкина, 23 R / сметка № 45638078 в MMM-bank, MFO 985435 Допълнения: № Име Измервателна единица 1 2 3 4 5 6 ОБЩО Ръководител на предприятието Количество Цена Сума Sidorkin A.Yu. Главен счетоводител Иванова A.N. Упражнението се състои в създаване и попълване на формуляр за стокова фактура. Най-добре е упражнението да се раздели на три етапа: Етап 1 - създаване на таблица на формуляра за фактура; 2 етап - попълване на таблицата; 3-ти етап - регистрация на формуляра. 1-ви етап Състои се в създаване на таблицата. Основната задача е да приспособите масата към ширината на листа. За да направите това: предварително задайте полетата, размера и ориентацията на хартията ("Файл" ⇒ "Настройка на страница ..."); - 10 - Copyright OJSC "Централно конструкторско бюро "BIBCOM" & LLC "Agency Book-Service", като изпълните командата "Сервиз" ⇒ в раздела "Параметри ...", в групата превключватели Параметри на прозореца, активирайте превключвател "Автоматично страниране" (Фиг. 7) В резултат на това ще получите дясната граница на лентата за набиране под формата на вертикална пунктирана линия (ако не се вижда, преместете с хоризонталната лента за превъртане Фиг. 7 до вдясно) и долната граница на лентата за набиране под формата на хоризонтална пунктирана линия (за да я видите как се движи с вертикална ивица превърти надолу). Автоматичното страниране ви позволява да следите кои колони се побират на страницата и кои не, още в процеса на въвеждане на данни и форматиране на таблицата. № 1 2 3 4 5 6 Наименование Мерна единица Количество Цена Сума ОБЩО Фиг. 8 Създайте таблица според предложения образец със същия брой редове и колони (фиг. 8). Подравнете и форматирайте шрифта в заглавните клетки, регулирайте ширината на колоните, като я промените с мишката. Въведете номерацията в първата колона на таблицата, като използвате манипулатора за попълване. "Очертайте" масата с помощта на линии с различна дебелина. Имайте предвид, че в последния ред пет съседни клетки нямат вътрешна граница. Най-лесно това се постига по следния начин: изберете цялата таблица и задайте рамката - "Външна" с удебелена линия; - 11 - Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" след това изберете всички редове с изключение на последния и задайте рамката с тънка линия "Right", "Left", "Top", "Bottom "; след това изберете отделно най-дясната клетка на долния ред и задайте рамката „Ляво“ за нея с тънка линия; остава да изберете първия ред на таблицата и да зададете рамката „Bottom“ за него с удебелена линия. Въпреки че можете да направите обратното. Първо "очертайте" цялата маса и след това премахнете допълнителните рамкиращи линии. На този етап е препоръчително да изпълните командата "Файл" ⇒ "Преглед", за да се уверите, че таблицата се побира изцяло на листа по ширина и всички гранични линии са на правилното място. 2-ри етап Състои се в попълване на таблицата, сортиране на данните и използване на различни числови формати. Попълнете колоните "Име", "Количество" и "Цена" по ваше желание. Задайте валутния формат на числото в клетките, където ще бъдат поставени сумите, и задайте необходимия брой десетични знаци, ако има такива. В нашия случай това са клетките на колоните "Цена" и Фиг. 9 "Сума". Трябва да ги изберете и да изпълните командата "Форматиране" ⇒ "Клетки ...", да изберете раздела "Число" и да изберете категорията "Валута" (фиг. 9). Това ще ви даде разделяне на хиляди, за да улесните навигирането в големи количества. Въведете формулата за изчисляване на сумата, която трябва да умножите цената по количеството, и попълнете реда от клетки надолу с формулата. Въведете формулата в клетката за общата сума. За да направите това, изберете блок от тези клетки, които искате да добавите, и една празна клетка под този блок, в която искате да поставите резултата. След това щракнете върху бутона на лентата с инструменти. Опитайте да промените данните в отделни клетки и вижте как се променя резултатът от изчислението. Сортирайте записите по азбучен ред. За да направите това, изберете всички редове на таблицата, с изключение на първия (заглавка) и последния ("Общо"), не можете да изберете и номериране. Изпълнете командата “Данни” ⇒ “Сортирай...” (фиг. 10), изберете колоната, по която искате да сортирате данните (в нашия случай това е колона B, тъй като съдържа списъка със стоки за сортиране ) и задайте превключвателя на възходяща позиция. 3-ти етап на регистрация на фактурата, въведете За допълнителни редове преди таблицата. За да направите това, изберете първите няколко реда от таблицата и изпълнете командата "Вмъкване" ⇒ "Редове". Ориз. 10 Ще бъдат вмъкнати същият брой редове, както сте избрали. Въведете необходимия текст преди и след таблицата. Внимавайте за подравняване. Моля, обърнете внимание, че текстът "Дата на получаване '__'_______200_" и имената на ръководителите на предприятието се въвеждат в същата колона, в която се намира колоната на таблицата "Сума" (най-дясната колона на нашата табела), прилага се само дясно подравняване. В клетката на най-лявата колона се въвежда текстът "СМЕТКА № ..." и се прилага подравняване в центъра на селекцията (клетките от един ред са предварително избрани по цялата ширина на таблицата на сметката). Към тези клетки е приложена граница отгоре и отдолу. Цялата друга текстова информация преди и след таблицата се въвежда в най-лявата колона, ляво подравняване. Извършете преглед. Упражнение 2 Въвеждане на понятието "абсолютна референция", задаване на точната стойност на ширината на колоната с помощта на командите на хоризонталното меню. Вмъкване на функция с помощта на съветника за функции Новата концепция за "абсолютна референция" може да се види с конкретен пример. Нека подготвим традиционна таблица с квадрати на двуцифрени числа (фиг. 11), така позната на всички от курса по алгебра. В клетка A3 въведете числото 1, в клетка A4 - числото 2, изберете двете клетки и плъзнете манипулатора за избор надолу, за да запълните колоната с числа от 1 до 9. По същия начин попълнете клетки B2 - K2 с числа от 0 до 9 , Когато попълните реда с числа от 0 до 9, всички клетки, които трябва да работите, не се виждат на екрана едновременно. Нека ги стесним, но така че всички колони да имат еднаква ширина (което не може да се постигне чрез промяна на ширината на колоните с мишката). За да направите това, изберете колони от A до K и изпълнете командата "Формат" ⇒ - 13 - Copyright OJSC "Централно дизайнерско бюро "BIBCOM" & LLC "Agency Book-Service" "Колона" ⇒ "Ширина ...", в полето за въвеждане "Ширина на колона » Въведете стойност, например 5. Разбира се, всеки разбира, че в клетка B3 трябва да поставите формула, която квадратира числото, съставено от десетиците, посочени в колона A, и единиците, съответстващи на стойността поставени в ред 2. По този начин самото число, което трябва да бъде повдигнато на квадрат в клетка B3, може да бъде определено по формулата A3 * 10 + B2 (броят на десетките, умножен по десет плюс броя на единиците). Остава да поставим на квадрат това число. 1 2 3 4 5 6 7 8 9 0 100 400 900 1600 2500 3600 4900 6400 8100 1 121 441 961 1681 2601 3721 5041 6561 8281 КВАДРАТНА МАСА 2 3 4 5 144 169 196 225 484 529 576 625 1024 1089 1156 1225 1764 1849 1936 2025 2704 2809 2916 3025 3844 3969 4096 4225 5184 5329 5476 5625 6724 6889 7056 7225 8464 8649 8836 9025 6 256 676 1296 2 116 3136 4356 5776 7396 9216 7 289 729 1369 2209 3249 4489 5929 7569 9409 8 324 784 1444 2304 3364 4624 6084 7744 9604 9 361 841 1521 2401 3481 4761 6241 7921 9801 11 Нека се опитаме да използваме "Съветника за функции". За да направите това, изберете клетката, в която трябва да бъде поставен резултатът от изчислението (RT), и изпълнете командата "Вмъкване" ⇒ "Функция ..." (фиг. 12). Диалоговият прозорец „Съветник за функции (Стъпка 1 от 2)“ (Фигура 12) има два подпрозореца: „Категория“ и „Функция“. Когато е избрана определена функция, в долната част на диалоговия прозорец се появява кратко описание на функцията. Сред предложените Фиг. 12 категории функции, изберете "Math", сред "Functions" - "Degree", натиснете бутона Ok. В следващия диалогов прозорец (Фиг. 13) въведете в полето "Число" (основа на степента) - A3 * 10 + B2 и в полето "Експонента" - 2. Както и при въвеждане на формулата директно в клетка на електронната таблица, няма нужда да въвеждате от клавиатурата адреса на всяка клетка, посочена от формулата. В диалоговия прозорец на втората стъпка на "Съветника за функции" е достатъчно да посочите с мишката съответната клетка от електронната таблица и нейният адрес ще се появи в полето за въвеждане "Число" на диалоговия прозорец. Ще трябва да въведете само аритметика Фиг. 13 знака (*, +) и числото 10. В случаите, когато трябва да изберете клетки, затворени от прозорец, за да въведете аргументи, вдясно на всяко поле има бутони за въвеждане на аргументи, които ви позволяват да свивате и разширявате диалоговия прозорец . В допълнение, прозорецът "Function Wizard" може да бъде преместен встрани чрез "хващане" на заглавната лента с мишката. В същия диалогов прозорец (фиг. 13) можете да видите стойността на самото число (10) и резултата от изчисляването на степента (100). Остава само да натиснете бутона Ok. В клетка B3 се появи резултатът от изчислението. Бих искал да разширя тази формула към останалите клетки на таблицата. Изберете клетка B3 и попълнете съседните клетки, като плъзнете манипулатора за запълване надясно. Какво се случи (фиг. 14)? Ориз. 14 Защо резултатът не отговори на очакванията ни? В клетка C3 числото не се вижда, защото не се побира изцяло в клетката Разширете с мишката колона C. Числото се появи на екрана, но явно не отговаря на квадрата на числото 11 (Фиг. 15 ). Ориз. 15 Защо? Факт е, че когато разширихме формулата надясно, Excel автоматично промени адресите на клетките, като взе предвид нашето отместване, към което се отнася формулата, и в клетка C3 не числото 11 е на квадрат, а числото, изчислено от формула B3 * 10 + C2. Във всички предишни упражнения бяхме доста доволни от относителните препратки към клетките на таблицата (когато формулата се премества по същия закон, препратките също се изместват), но тук стана необходимо да се коригират определени препратки, т.е. показват, че броят на десетиците може да бъде взет само от колона А, а броят на единиците само от ред 2 (за да стане възможна формулата - 15 - Copyright JSC "Централно проектантско бюро "BIBCOM" & LLC "Agency Book-Service " разпръснати надолу). За целта Excel има способността да задава абсолютни и смесени препратки. Абсолютна препратка е препратка, която не се променя, когато формулите се копират. За да направите това, преди името на колоната и номера на реда се добавя знак за долар $ (или се въвежда от клавиатурата, или след въвеждане на адреса на клетката се натиска функционалният клавиш F4). Смесените връзки са връзки, които са само частично абсолютни, т.е. или колона, или ред са фиксирани. В този случай знакът за долар $ се поставя или преди буквата, когато колоната е фиксирана, или преди цифрата, когато редът е фиксиран. Знакът за долар $ се въвежда или от клавиатурата, или след въвеждане на адреса на клетката се натиска функционален клавиш F4, докато знакът $ застане на нужното място. Когато копирате формула, съдържаща смесена препратка, се променя само относителната част от препратката. Върнете ширината на колона C в първоначалната й позиция и направете следното: Изберете клетка B3 и като зададете текстовия курсор в лентата с формули, коригирайте съществуващата формула =POWER(A3*10+B2;2) към правилната =МОЩНОСТ($A3*10+ при $2,2). Сега, като използвате услугите на маркера за запълване, можете да попълните всички свободни клетки на таблицата с тази формула (първо плъзнете маркера за запълване надясно, след това, без да премахвате селекцията от получения блок от клетки, надолу). Използвахме смесени препратки, за да въведем препратки към клетки за данни от колона A и ред 2. Абсолютната препратка в нашия пример може да се използва, ако във формулата въведем не числото 10, по което се умножават числата в колона A, а адреса на клетката, например A15 (където бихме въвели това число 10). В този случай формулата в клетка B3 ще бъде написана като: =POWER($A3*$A$15+B$2) и след това ще бъде копирана и в останалите клетки. Опитайте тази. Остава да подредите таблицата: въведете заглавието в клетка A1, форматирайте го и го центрирайте върху селекцията, рамкирайте таблицата и запълнете отделни клетки с фон. Упражнение 3 Представяне на концепцията за "име на клетка" Представете си, че имате собствена компания, която продава някои продукти и трябва да разпечатвате ценова листа всеки ден с цените на стоките в зависимост от обменния курс на долара. Подгответе таблица, състояща се от колони: "Име на продукта"; „Еквивалент на $US“; "Цена в рубли". - 16 - Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Попълнете всички колони, крем "Цена в рубли". Попълнете колоната „Име на продукта“ с текстови данни (списъкът на стоките по ваша преценка), а колоната „Еквивалент в щатски долари“ попълнете с числа (цени в $). Ясно е, че в колоната "Цена в r." формулата трябва да бъде поставена: „Еквивалент на $ US“ * „Обменен курс на долара“. Защо е неудобно в тази формула да се умножава по конкретна стойност на обменния курс? Да, защото с всяка промяна в курса ще трябва да променяте формулата си във всяка клетка. По-лесно е да го вземете под стойността на долара отделна клетка , който е посочен във формулата. Ясно е, че препратката трябва да е абсолютна, т.е. стойността на обменния курс на долара може да бъде взета само от тази конкретна клетка с фиксиран адрес. Обсъдихме как да зададем абсолютни препратки по-горе, но има друг удобен начин: да се обърнете не към адреса на клетката, а към името, което може да бъде присвоено на клетката. Когато наименувате клетка или диапазон от клетки, можете да получите достъп до тази клетка или диапазон по всяко време и от всяко място в таблицата, дори ако променят местоположението си или са на различни листове. Изберете клетката, в която ще бъде въведен курсът на долара (над таблицата), въведете стойността на курса на долара за днес в нея и изпълнете командата "Вмъкване" ⇒ "Име" ⇒ "Присвояване ...". В диалоговия прозорец, който се отваря (фиг. 16), можете да въведете произволно име и да изберете диапазона, за който се въвежда това име в полето "Формула". Името може да бъде с дължина до 255 знака и да съдържа букви, цифри, долни черти (_), обратни наклонени черти (\), точки и въпросителни знаци. Първият знак обаче трябва да е буква, долна черта (_) или обратна наклонена черта (\). Не се допускат имена, които се възприемат като числа или препратки към клетки. В диалоговия прозорец, който се появява, трябва само да въведете името на клетката (точният й адрес вече е даден в полето за въвеждане на формула) и да щракнете върху бутона OK. Моля, имайте предвид, че в "Поле на името", вместо адреса на клетката, сега се поставя нейното име. В клетката, разположена вляво от клетката "Dollar_rate", можете да въведете текста "Dollar rate". Ориз. 16 Сега остава да въведете формулата за изчисляване на цената в рубли. За да направите това, изберете най-горната празна клетка на колоната "Цена в рубли" и въведете формулата, както следва: въведете знака "=", след това щракнете върху клетката, разположена вляво (която съдържа цената в долари), след което въведете знака "*" и "Exchange_dollar". Формулата трябва да изглежда така: =B7*USD_rate. Попълнете формулата надолу, като използвате манипулатора за попълване. Изберете подходящите клетки и приложете формата на номера на валутата към тях. Стилизирайте заглавката на таблицата: центрирайте, приложете удебелен стил на шрифта, разгънете реда и приложете вертикално централно подравняване с помощта на командата "Форматиране" ⇒ "Клетки ...", изберете раздела "Подравняване" и в групата "Вертикално:" изберете "В центъра." В същия диалогов прозорец активирайте превключвателя „Пренасяне по думи“, в случай че някое заглавие не се побира на един ред. Променете ширината на колоните. Изберете таблицата и задайте рамка за нея. - 18 - Copyright OJSC "Централно конструкторско бюро "BIBCOM" & LLC "Agency Book-Service" Лабораторна работа № 3 Упражнение 1 Промяна на ориентацията на текста в клетката, запознаване с възможностите на базите данни данни в ексел. Сортиране на данни по множество ключове Представете си, че сте собственик на малък магазин. Необходимо е да се води стриктна отчетност за получаването и разходването на стоките, да имате всеки ден пред очите си реален баланс, да можете да отпечатвате името на стоките по отдели и т.н. Дори при такава трудна задача Excel може да улесни много работата. Да разбием това упражнение за няколко задачи в логическа последователност: създаване на таблица; попълване на таблицата с данни по традиционния начин и с помощта на формуляр; избор на данни по определен признак. 1. Създаване на таблица Въведете заглавията на таблицата в съответствие с предложения модел. Моля, обърнете внимание, че заглавието се намира в два реда на таблицата: в горния ред „Приходи“, „Разходи“, „Баланс“, а останалите елементи на заглавието в реда по-долу (фиг. 17). Остатъчна сума Оставаща Остатъчна сума Количество разход Разход Цена на разход Наименование на продукта Количество постъпление Отдел Цена на постъпление № Мерна единица Входящи 1 2 3 4 5 6 Фиг. 17 Въвеждането на заглавния текст е най-добре да започне от втория ред. Вече сте забелязали, че колоната "Входящи" обхваща две клетки. Думата „Доход“ се въвежда в същата колона като „Цена на дохода“, след това се избират две съседни клетки и текстът се центрира върху селекцията (тази операция беше разглеждана многократно в предишни упражнения). Клетките "Разходи" и "Остатък" са форматирани по подобен начин. Изберете втория заглавен ред и центрирайте. Можете също така да видите, че за да може цялата таблица да се побере в ширината на листа, в някои клетки текстът е "завъртян на 90°". Изберете онези клетки, в които искате да "разширите" текста и изберете командата "Форматиране" ⇒ "Клетки ..." в раздела "Подравняване" (фиг. 18) изберете "Ориентация на текста" 90o и не забравяйте да активирате превключете „Обвиване по думи“ (оставете вертикалното подравняване „Отдолу“). За останалите (неразширени) клетки приложете вертикалното подравняване „Центрирано“. Задайте границата на таблицата ("Форматиране" ⇒ "Клетки ...", раздел "Граница"). Инсталирайте в клетки, фиг. 18, съдържащи цени, паричен формат на числа („Формат“ ⇒ „Клетки…“, раздел „Число“). Въведете номерацията на редовете на таблицата (№ на колона), като използвате маркера за попълване. Въведете формули за сумата на салдото („Входящо количество“ минус „Разходно количество“) и сумата на салдото („Оставащо количество“ по „Изходяща цена“). Разширете тези формули надолу по таблицата. В процеса на изпълнение на задача в много случаи е по-удобно да използвате контекстното меню, извикано чрез натискане на десния бутон на мишката. Така че, за да форматирате клетки, достатъчно е да ги изберете, щракнете с десния бутон в момента, когато показалецът на мишката е вътре в селекцията и изберете командата "Форматиране" ⇒ "Клетки ...". Това ще ви отведе до същия диалогов прозорец Форматиране на клетки (Фигура 18). Да, и редактирането на съдържанието на клетката (коригиране, промяна на данни) изобщо не е необходимо в "Лентата с формули". Ако щракнете двукратно върху клетка или натиснете клавиша F2, в нея ще се появи текстов курсор и можете да направите необходимите корекции. 2. Попълване на таблицата Преименувайте "Sheet1" на "Availability". За да направите това, щракнете с десния бутон върху етикета "Sheet1" и изберете командата за преименуване. Въведете ново име и натиснете клавиша (Enter). Решете какъв тип продукт ще продавате и какви отдели ще има във вашия магазин. Въведете данните в таблицата не по отдел, а смесено (по реда на получаване на стоките). Попълнете всички клетки с изключение на тези, съдържащи формули („Остатък“). Не забравяйте да оставите последния ред на таблицата празен (но този ред трябва да съдържа всички формули и номериране). Въведете данните така, че да има различни стоки от един отдел (но не подред) и да има стоки с нулев баланс (всички продадени) (фиг. 19 ). Съгласете се, че традиционният начин за попълване на таблицата не е особено удобен. Нека се възползваме от базите данни на Excel. 1 2 3 4 5 6 Отдел Сладкарство Млечни продукти Месо Месо Вино и водка Наименование на продукта Маршмелоу в шоколад Кашкавал Наденица Москва Балик Водка "Абсолют" Консумация Оставаща цена Входящо количество Входящо количество Разходно количество Разходно количество Остатъчно количество Остатъчно количество № Мерна единица Пристигане оп. 20 стр. 15 кг. 65 стр. 10 кг. 110 r. 20 кг. 120 r. 10 бутилки 2 л. 400 r. 100 25р. 85 стр. 120р. 140 r. 450 r. 15 8 15 5 99 0 2 5 5 1 0 0 170 r. 600 r. 700 r. 450 r. 0 r. Ориз. 19 Изберете командата "Данни" ⇒ "Формуляр..." Ще получите формуляр за данни (Фиг. 20), съдържащ статичен текст (имена на полетата на базата данни) и прозорци за редактиране, в които можете да въвеждате и редактирате текст. Изчисляваните полета (в които се поставят формули) се показват без прозорци за редактиране (“Остатъчно количество” и “Остатъчно количество”). Сега имате вашата маса като че ли под формата на отделни карти със записи (всяка от които представлява ред от таблица). Ориз. 20 Можете да се придвижвате между записите или с помощта на бутоните "Назад", "Следващ", или с клавишите със курсора (нагоре, надолу), или с преместване на плъзгача върху лентата за превъртане на формата с данни. Достигайки до последно влизане (нарочно го оставихме празен, но разширихме формулите и номерацията към него), попълнете го с нови данни. Удобно е да се придвижвате между прозорците за редактиране, в които се въвеждат данни, с помощта на клавиша (Tab). Когато завършите цялото въвеждане, натиснете клавиша (Enter) и автоматично ще бъдете отведени до нова празна карта за въвеждане. Веднага след като попълните нов запис, цялата въведена от вас информация ще бъде автоматично възпроизведена в оригиналната таблица. Попълнете няколко нови записа и щракнете върху бутона Затвори. - 21 - Copyright JSC "Централно проектантско бюро "BIBCOM" & LLC "Agency Book-Service" Както можете да видите, попълването на таблицата в режим на формуляр е доста удобно. 3. Попълване на таблицата с помощта на готов списък с данни Тъй като имаме ограничен брой отдели и имената им са постоянни, най-добре е при попълване на таблицата да използвате предварително изготвен списък с тези отдели. Изтрийте имената на отделите от колоната „Отдел“ и въведете кратък списък, който включва имената на всички отдели веднъж извън таблицата, например в колона L. След това изберете клетките на колоната „Отдел“ в таблицата и изберете командата "Данни" ⇒ "Проверка". Това ще изведе Фиг. 21 диалогов прозорец "Валидиране на входни стойности" (фиг. 21), където трябва да посочим условията за проверка. В нашия пример трябва да изберем от списъка (който въвеждаме в полето "Тип данни"). За да изберете „Източник“ на данни, използвайте бутона за минимизиране на прозореца. Натиснете го, маркирайте списъка с нашите отдели в колона L и се върнете към прозореца с помощта на бутона за увеличаване на прозореца. След като изпълните тези стъпки, щракнете върху бутона Ok. Сега, когато преминете към клетките на колоната „Отдел“, където е зададено условието за проверка, вдясно от тези клетки ще се появи квадрат със стрелка, като щракнете върху който можем да изберем името на отдела, от който се нуждаем ( Фиг. 22). Ориз. 22 За да скриете таблицата с отдели, можете да направите шрифта в клетките на колона L бял или да скриете цялата колона. За да скриете колона L, изберете я, изберете Форматиране ⇒ Колона ⇒ Скриване. За да върнете колона L на екрана, изберете колоните около скритата колона (колони K и M) и изпълнете командата "Форматиране" ⇒ "Колона" ⇒ "Показване ". Имайте предвид, че командата "Скриване" може да се приложи и към низове. За да направите това, се избира ред, избира се командата "Форматиране" ⇒ "Линия" ⇒ "Скриване". За да върнете линия на екрана, трябва да изберете линиите около скритата линия и да изпълните командата "Форматиране" ⇒ "Линия" ⇒ "Показване". Ориз. 23 Можете също да създадете списък на друг лист. В този случай обаче е невъзможно да се посочат адреси, които включват името на листа като „Източник“ на информация, т.е. трябва да въведете името на диапазона от клетки като адрес. В миналия урок се запознахме с това как да зададем името на една клетка. За да зададете име за диапазони от клетки, трябва да изберете диапазона от клетки, а не само една клетка, преди да изпълните командата "Вмъкване" ⇒ "Име" ⇒ "Присвояване". Нека прехвърлим нашия списък с отдели от колона L на Sheet1 в Sheet2 в колона A. Изберете клетките, в които е поставен нашият списък и изпълнете командата "Insert" ⇒ "Name" ⇒ "Assign". В диалоговия прозорец, който се отваря (фиг. 23), можете да въведете произволно име, например "Отдел", и да изберете диапазона, за който това име е въведено в полето "Формула" (по подразбиране адресът на диапазона избраното от нас е поставено тук). След това натиснете бутона Ok. Сега в диалоговия прозорец на командата "Проверка ..." като източник (фиг. 21) е достатъчно да въведете знака "=", след това натиснете клавиша F3, за да отворите списъка с налични имена на клетки, изберете "Отдел " в списъка, който се отваря и натиснете бутоните Ok, за да затворите диалоговите прозорци. 4. Сортиране на данни И така, попълнихте таблицата по реда на получаване на стоките, но искате да имате списък на стоките по отдели, за това прилагаме сортиране по редове. Изберете таблицата с втория ред от заглавката, но без първата колона „No.“, и изберете командата „Данни“ ⇒ „Сортиране...“ (фиг. 24). Ориз. 24 - 23 - Copyright OJSC "TsKB "BIBCOM" & LLC "Agency Book-Service" Изберете първия ключ за сортиране: в падащия списък "Сортиране по" изберете "Отдел" и поставете превключвателя на позиция "Възходящо" (всички отдели в таблицата ще бъдат разположени по азбучен ред). Ако искате всички продукти в отдела да бъдат поставени по азбучен ред, изберете втория ключ за сортиране: в падащия списък „След това по“ изберете „Име на продукт“, поставете превключвателя на позиция „Възходящо“. Сега имате пълен списък стоки по отдели. 5. Филтриране на данни Нека продължим запознаването си с възможностите на базите данни на Excel. Спомнете си, че ежедневно трябва да отпечатаме списък със стоки, останали в магазина (с ненулев баланс) или да покажем баланса в някой отдел, но за това първо трябва да получим такъв списък, т.е. филтрирайте данните. Маркирайте таблицата с втория заглавен ред (както преди създаването на формуляра за данни). Изберете командата от менюто "Данни" ⇒ "Филтър..." ⇒ "Автофилтър". Демаркирайте таблицата. Всяка клетка в заглавката на таблицата вече има бутон със стрелка (не се отпечатва), който ви позволява да зададете критерия за филтър (фиг. 25). Ориз. 25 Да предположим, че искаме да оставим всички записи за "Сладкарски отдел". Разгънете списъка с клетки "Отдел" и изберете "Сладкиши". В този случай Excel ще промени таблицата и ще я покаже в изглед, където ще присъстват само данните, свързани с избрания отдел (фиг. 26), а стрелката в колоната, където е приложен автофилтърът, ще стане синя. Ориз. 26 - 24 - Copyright JSC "Централно дизайнерско бюро "BIBCOM" & LLC "Agency Book-Service" По същия начин можете да видите данни за други отдели или да изберете критерий за филтриране в друга колона. Таблицата може да бъде отпечатана във филтриран вид. Филтрираните редове могат да бъдат подчертани с цвят на шрифта, фон, рамки или форматирани по друг начин. Като премахнем филтрирането, получаваме много визуално оформление на таблицата. Във филтрирания раздел на таблицата можете да изчислявате суми, продукти и да извършвате други операции, сякаш няма други редове в таблицата. Например, искаме да изчислим сумата на баланса в сладкарския отдел. За да направите това, избираме данните от колоната "Остатъчна сума", като улавяме последната свободна клетка и натискаме бутона "Автоматично сумиране". Функцията SUBTOTALS(9; F2:F8) ще се появи в свободна клетка (фиг. 26). В него първият аргумент е числото на математическа или статистическа операция (1 изчисляване на средната стойност; 2 и 3 - преброяване на броя на числата и непразните клетки; 4 и 5 - изчисляване на максимума и минимума; 6 - продукт ; 7 и 8 - стандартно отклонение; 9 - сума; 10 и 11 са дисперсията), а втората е интервалът на изчисление. Функцията SUBTOTAL се намира в категорията на математиката и се различава по това, че изчислява стойности само за видими клетки и не взема предвид невидимите. Когато промените филтрирането, междинните суми също се променят (фиг. 25), докато обичайната функция за сумиране или продукт ще остане непроменена. Нека променим малко условието на проблема, да кажем, че искаме да видим данни за ненулеви остатъци на сладкарския отдел. За да направите това, изберете елемента "Условие" в списъка на колоната "Сума на баланса". Това ще изведе диалоговия прозорец Custom AutoFilter (фиг. 28). В горното поле изберете "още" "0.00r.". Полученият резултат е показан на фиг. 28. Фиг. 27 Сега, да кажем, че искаме да разгледаме данни за ненулеви салда в отделите за захарни изделия и месо. За да направите това, оставяме същия филтър в колоната „Сума на баланса“ и избираме „Състояние“ в колоната „Отдел“ (фиг. 28). В горното поле изберете "равно на" Фиг. 28 - 25 - Copyright JSC "TsKB "BIBCOM" & LLC "Agency Kniga-Service" "Сладкарски изделия", по-долу "е равно на" "Месо", и задайте ИЛИ като логическа функция. Полученият резултат е показан на фиг. 29. Фиг. 29 За да видите отново цялата таблица, трябва да кликнете върху стрелката в колоната, където е приложено филтрирането (те са показани със сини стрелки), да изберете „Всички“ в списъка или отново да отидете на „Филтър“ ⇒ Команда "Автоматичен филтър" от меню "Данни" за отмяна на режима на филтриране. - 26 - Copyright OJSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Лабораторна работа № 4 Упражнение 1 Създаване и редактиране на графики в Excel документМного често в живота трябва да измервате зависимостта на една променлива от друга и да показвате тези зависимости под формата на графики. Excel предоставя възможност за такова визуално показване на цифрови електронни данни. Да предположим, че е проведен някакъв експеримент, например е измерена зависимостта на някакъв параметър от температурата. Първоначалната температура беше 10°C. Стъпката на промяна е 10оС. Въведете тази информация на Excel лист (фиг. 30). За да поставите заглавието "Първоначални данни" над таблицата с изходни данни, както е показано на фиг. 30 е показано на фигурата, изберете две клетки, изпълнете командата "Форматиране" ⇒ "Формат на клетка ..." и в раздела "Подравняване" поставете отметка в квадратчето "обвиване по думи", "сливане на клетки" в дисплея и група за подравняване "центрирана" и хоризонтално, и вертикално (вижте Лаборатория #1). Сега нека подготвим таблица с експериментални данни (фиг. 31). Въведете данните от първата колона - числата по ред се въвеждат с помощта на маркера за попълване (черна точка в ъгъла на курсора на таблицата). Заглавието на колоната "Температура" се въвежда по формулата: =A2 (т.е. във формулата има връзка към адреса на клетката, фиг. 31, където името на параметъра е поставено в таблицата "Първоначални данни" ( Фиг. 30)). Началната температура е равна на началната температура, т.е. = B2. Освен това се различава от предишния с една стъпка. Следователно във втория ред температурата е равна на началната температура + стъпка, т.е. =E3+$B$3. За да използвате по-нататък манипулатора за попълване, за да копирате формулата и стъпката трябва да се прави постоянно от една и съща клетка, правим препратката към нея абсолютна. Въведената по този начин информация ни позволява да автоматизираме настройката на таблицата с експериментални данни към промените в началните условия. Опитайте вместо "температура" в първоначалните данни, въведете "налягане" и задайте първоначалната стойност, например 20. Стойностите на Yekper трябва да бъдат взети от експеримента, така че просто ги въведете от клавиатурата. За форматиране на заглавието "Експеримент" е най-добре да използвате опцията за копиране на формат. За да направите това: - 27 - Copyright OJSC "Централно конструкторско бюро" BIBCOM " & LLC "Agency Book-Service" Pic. 32 Фиг. 33 изберете клетката "Изходни данни"; Щракнете върху бутона с инструмента Format Standard Paint Bar. В този случай Excel копира формата на избраната клетка и показалецът на мишката се превръща в четка, вляво от която има знак плюс; плъзнете показалеца на мишката върху диапазона от клетки, където искате да копирате избрания формат. За да начертаете Yexper спрямо температурата, изберете тези две колони, включително техните заглавия, и изберете Вмъкване ⇒ Диаграма или щракнете върху бутона Chart Wizard в лентата с инструменти. Първият диалогов прозорец "Chart Wizard (Step 1 of 4) - Chart Type" (фиг. 32) има два раздела - "Standard" и "Custom". На този етап от наличните образци се избира вариант на изграждащата се диаграма. За да изградите графика на зависимостта на едно количество от друго, трябва да изберете типа на диаграмата "Точка", а след това някой от петте му вида. Нека просто да рисуваме точки, а не линии. Кликнете върху бутона "Преглед на резултата" (фиг. 32). Excel веднага ще покаже как нашите данни ще бъдат показани на готовата диаграма. За да преминете към всяка следваща стъпка от съветника за диаграми, използвайте бутона "Напред". Във втория прозорец „Съветник за диаграми (Стъпка 2 от 4): Източник на данни за диаграма“ в раздела „Диапазон от данни“ се показва адресът на избрания диапазон от изходни данни и извадка от изгражданата диаграма (фиг. 33) . Изберете „в колони“ и щракнете върху бутона „Напред“. - 28 - Copyright JSC "Централно дизайнерско бюро "BIBCOM" & LLC "Agency Book-Service" На третия етап от съветника за диаграми (на стъпка 3) в прозореца "Параметри на диаграмата" (фиг. 34) определете естеството на дизайн на диаграма - извършете нейното форматиране. Третият прозорец на Chart Wizard има следните раздели: title - позволява да въведете текста на заглавието на диаграмата и етикетите на осите; оси - позволява ви да дефинирате Фиг. 34 показване и маркиране на координатни оси; линии на мрежата - позволява ви да определите вида на линиите и характера на показването на мрежата; легенда - позволява ви да покажете или скриете легендата и да определите нейното място на диаграмата. Легенда - Фиг. 35 е малък подпрозорец на диаграмата, който показва имената на сериите от данни и примери за тяхното оцветяване на диаграмата (под формата на ключ Легенда); етикети на данни – позволява ви да контролирате показването на етикети, съответстващи на отделни елементи от данни на диаграмата; таблица с данни - позволява ви да добавите или скриете таблицата с данни, използвана за изграждане на диаграмата върху диаграмата. Последният (четвърти) прозорец на помощника за диаграми (фиг. 35) се използва за определяне на нейното разположение в работната книга. Препоръчително е да изберете поставянето му на отделен лист, т.к. в този случай диаграмата се вмъква по-лесно в други документи, не блокира оригиналните данни, по-четлива е и т.н. След като сте дефинирали всички необходими параметри, щракнете върху бутона "Край". Excel изгради диаграма за нас (фиг. 36), използвайки определен набор от параметри, като цвета на зоната за изграждане (областта, в която се показва самата диаграма, без заглавия, легенди и други елементи), шрифт, мащаби, размер на точката и т.н., по подразбиране. За да промените една или друга опция за форматиране на диаграмата, трябва да щракнете с десния бутон върху нея и да изберете подходящата команда в контекстното меню, което се отваря. Направете фона на областта за чертане бял. За да направите това, щракнете с десния бутон върху областта за чертане на диаграмата и изберете командата "Форматиране на областта за чертане". В диалоговия прозорец, който се отваря (фиг. 37), проверете превключвателя в групата за "нормално" запълване. Щракнете върху бутона Ok. - 29 - Copyright JSC "Централно конструкторско бюро "BIBCOM" & LLC "Agency Book-Service" Y=f(x) 12 10 8 Y ​​​​6 4 2 0 0 20 40 60 80 100 120 X Yexper Фиг. 36 Фиг. 37 Фиг. 38 Увеличете размера на точките. За да направите това, щракнете с десния бутон върху точките и изберете командата "Форматиране на серия от данни". В диалоговия прозорец, който се отваря (фиг. 38), в групата „размер“ задайте размера на точките, например 8 pt. Тук можете да изберете и други параметри на данните, например да промените маркера, т.е. тип точки, начертайте линия, като изберете нейния цвят, дебелина и тип, изгладете линията и т.н. Аргументът на нашата функция се променя от 10 на 100, а мащабът на оста X има минимална стойност 0 и максимална 120. В допълнение, шрифтът на етикета с данни е малък. Как да го промените? Щракнете с десния бутон върху оста X и изберете Форматиране на ос. В диалоговия прозорец, който се отваря (фиг. 39), в раздела "Мащаб" задайте минималната стойност - 10, максималната - 100 и цената на основните раздели - 10, т.к. нашите данни се променят на стъпки от 10. В същия прозорец, в раздела Шрифт, можете да увеличите размера на шрифта и да промените стила му, например да го увеличите до 8 pt и да направите курсив. На сгъвката "Подравняване" можете да зададете вертикалното писане на подписи. По същия начин можете - 30 - Copyright JSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" да форматирате Y-оста. Направете го. Разбрахте ли вече принципа на форматиране на диаграмата? След това увеличете размера на шрифта на заглавието, коригирайте легендата, заглавията на осите. Пример за форматирана диаграма е показан на фигурата (фиг. 40). За да се предвидят стойностите на отговора - параметър Y на изхода на експеримента от фактор - независими променливи X на входа на системата (в нашия случай това е температура), е необходимо да се знае функционалната зависимост Y = f (X). Excel има възможност за автоматичен избор на такава функция. Ориз. 39 Yexper Y=f(x) 12 10 8 Y ​​​​6 4 2 100 90 80 70 60 50 40 30 20 10 0 X 40 Щракнете с десния бутон върху точките и изберете Add Trendline от контекстното меню. В диалоговия прозорец, който се отваря (фиг. 41), в раздела Тип изберете типа линия на тренда. Обикновено за описание на системата се използва линия на тренда на полином от втори ред: Y = a0 + a1 * X + a2 * X 2 , (1) където ai са коефициентите на уравнението. Ако е необходимо, можете да промените степента на 6. Тогава уравнението ще приеме формата: - 31 - Copyright OJSC "Централно дизайнерско бюро "BIBCOM" & LLC "Agency Book-Service" Y = a0 + a1 * X + a2 * X 2 + a3 * X 3 + + a4 * X 4 + a5 * X 5 + a6 * X 6 . (2) Фиг. 41 Фиг. 42 В раздела "Параметри" задайте радио бутоните на "покажи уравнението на диаграмата" и "постави стойността на достоверността на приближението на диаграмата" (фиг. 42). Това ще ви позволи да видите уравнението и точността на приближението на нашите данни. Уравнението и точността, показани на екрана, могат да бъдат преместени на всяко място в диаграмата (както и други надписи, например заглавието на диаграмата, имената на осите, легендата), като „хванете“ рамката с левия бутон на мишката. Приблизителна окончателна форма на нашата зависимост е показана на фиг. 43. Запазете резултатите от упражнение 1, ще ни трябват по-късно (вижте Лабораторна #6 по-долу). y = -0,0054x2 + 0,6014x - 5,9667 R2 = 0,9817 Y=f(x) Yexp Полином (Yexp) 12 10 8 6 Y 4 2 0 -2 10 20 30 40 50 60 X 43 - 32 - 70 80 90 100 Copyright JSC "TsKB "BIBCOM" & LLC "Agency Kniga-Service" Упражнение 2 Създаване и редактиране на повърхности в документ на Excel В предишното упражнение разгледахме възможността за визуализиране на еднопараметърна зависимост ( функцията зависи само от една променлива). В действителност такива прости зависимости са доста редки. По-често трябва да се справяте с многопараметрични функции. Как да ги визуализирате, помислете за примера на проблем с два параметъра. Нека имаме уравнение: Z = sin X 2 − Y 2 + 5 ⋅ X ⋅ Y , (3) където X и Y се променят от -5 на 5 на стъпки от 1. Необходимо е да се начертае повърхността на полученото Z За да направите това, първо трябва да изградите матрица с данни (фиг. 44). () Ориз. 44 В клетка B1 въведете първата стойност Y = -5. След това изпълнете командата "Редактиране" ⇒ "Попълване" ⇒ "Прогресия ...". В диалоговия прозорец, който се отваря (фиг. 45), задайте: "Местоположение" - по редове, "Стъпка:" равно на 1 и "Гранична стойност:" равно на 5. След това щракнете върху бутона Ok. По абсолютно същия начин Фиг. 45, стойностите на X в колона A се попълват, с единственото изключение, че „Местоположение“ трябва да бъде в колони. Изпълнете го. След като стойностите на аргументите са въведени в таблицата, попълнете клетка B2 с формулата за изчисляване на Z (3). Функцията Sin е в математическата категория „Съветници за функции“. - 33 - Copyright OJSC "TsKB "BIBCOM" & LLC "Agency Kniga-Service" Не забравяйте, че формулата трябва да съдържа смесени препратки, тъй като стойностите X винаги трябва да бъдат избрани от колона A, а стойностите Y от ред 1. За да попълните целите таблици, използвайте манипулатора за попълване. Данните за начертаване на повърхността са готови, остава само да ги нанесете върху диаграмата. Както и в предишното упражнение, ще използваме "Chart Wizard" (фиг. 32 - 35). Първо изберете матрицата на функционалните стойности (не е необходимо да избирате стойностите X и Y на фиг. 46!), отворете съветника за диаграма по какъвто и да е начин, който знаете, и изберете типа диаграма "Повърхност". . Освен това конструкцията на повърхността от конструкцията на графиката не се различава. Крайната диаграма ще изглежда като тази, показана на фиг. 46. ​​​​Можете да завъртите или коригирате диаграмата в диалоговия прозорец Format 3D Surface (Фиг. 47), който е показан на Фиг. 47. 47 се отваря чрез щракване с десен бутон върху стените на повърхността и избиране на елемента от контекстното меню „3D изглед…“. - 34 - Copyright OJSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Лабораторна работа №5 Упражнение 1 Булеви изрази в Excel Нека въведем формулата =7>5 в клетка A1. Ще върне TRUE. Нека копираме съдържанието на A1 в A2 и коригираме формулата в A2: =3>5. Тази формула ще върне FALSE. Десните части на двете формули са твърдения, т.е. твърдения, за които може да се заключи дали са верни или не. Нека разгледаме друг пример. Въведете числото 2 в клетка A4 и формулата =A4>3 в клетка B4. Формулата връща FALSE. Въведете числото 6 в A4. Формулата връща TRUE. B4 съдържа предикат, т.е. оператор с променливи (в този случай има само една променлива). В зависимост от стойността на променливите, предикатът може да приеме стойностите TRUE и FALSE. В този пример формулата, така да се каже, отговаря на въпроса: „Числото (или резултатът от изчисленията, използващи формула) съхранено в клетка A4 по-голямо ли е от 3?“ В зависимост от стойността на A4, отговорът ще бъде ДА (ВЯРНО) или НЕ (НЕВЯРНО). Във формулата =A4>3 нейните съставни части (A4 и 3) могат да се считат за аритметични изрази, само много прости. По-сложен пример: =(A4^2-1)>(2*A4+1). Можете да пропуснете скобите в този израз, тъй като аритметичните операции имат по-висок приоритет от операторите за сравнение, но скобите правят формулата по-четима. Операциите за сравнение са обобщени в табл. 1. Таблица 1 > по-голямо от >= по-голямо или равно< <= меньше или равно меньше = <>равно не е равно Обърнете внимание, че символът за релация по-голямо или равно е представен с два знака: > и =. Причината е, че на клавиатурата няма знак ≥. Твърдението и предиката имат общо наименование – логически израз. На разположение логически операции, които ви позволяват да изграждате сложни логически изрази. Тези операции са реализирани в Excel като функции (НЕ, И, ИЛИ). При логически функцииаргументите могат да приемат само две стойности: TRUE и FALSE. Функцията НЕ може да има само един аргумент, докато функциите И и ИЛИ могат да имат два или повече аргумента. Пример 1 В клетка A1 (с име z) напишете произволно число. Разберете дали принадлежи към сегмента. Решение. Дайте на клетка A1 името z (Вмъкване ⇒ Име ⇒ Присвояване). Нека въведем в A1 числото 3. За да принадлежи z към сегмента е необходимо два предиката да са едновременно истинни: z ≥ 2 и z ≤ 5 . В клетка B1 поставяме формулата =I(z>=2;z<=5). Для ввода в формулу имени ячейки нажмите F3 для открытия списка имен. В В1 получим значение ИСТИНА. Следует предостеречь от неверного решения: формулы =2<=z<=5. Введите эту формулу в С1 и убедитесь, что она возвращает ЛОЖЬ! Коварство этой, на первый взгляд, такой естественной формулы в том, что Excel ничего не сообщает о ее некорректности. Пример 2 В ячейке А1 (с именем z) записано число. Выяснить, принадлежит ли оно одному из лучей на числовой оси: (-∞,2) или (5,∞). Решение. Для того чтобы z принадлежал хотя бы одному из лучей, нужно, чтобы был истинным хотя бы один из предикатов: z < 2 или z >5. В клетка D1 поставете формулата =OR(z<2;z>5). A1 съдържа числото 3, така че формулата връща FALSE. Задачата можеше да бъде решена по различен начин, като се има предвид, че работният лист съдържа формула за проверка дали числото z принадлежи на отсечката . Споменатите два лъча съставляват добавката към този сегмент на цифровата ос. Нека въведем формулата = НЕ (B1) в клетка E1. Проверете, като въведете различни числа в клетка A1, че формулите в клетки D1 ​​и E1 дават идентични резултати. На практика "в чист вид" логическите изрази по правило не се използват. Логическият израз служи като първи аргумент на функцията IF: IF(log_expression, value_if_true, value_if_false) Вторият аргумент съдържа израза, който ще бъде оценен, ако logical_expression върне TRUE, а третият аргумент съдържа израза, който се оценява, ако logical_expression връща FALSE. Пример 3 1. Въведете в клетка A2 формула, която връща z+1, ако z >1 и z в противен случай: = IF(z>1;z+1;z). (В съветника за функции IF е в категорията "Boolean", както и функциите AND, OR, NOT.); 2. Ако z > 60, тогава в клетка B2 покажете съобщението „Превишена прагова стойност“, в противен случай покажете z: =IF(z>60;"Прагова стойност е надвишена";z) Моля, имайте предвид, че текстът във формулите е въведен в кавички. 3. Ако z ∈ , тогава върнете z, ако z< 10, то возвращать 10, если z >25, след това върнете 25. Изразът за това условие ще изглежда нещо подобно (записваме формулата в C2): =IF(z<10;10;ЕСЛИ(z<=25;z;25)) Теперь попробуйте менять значение z в ячейке А1, следя за тем как меняются значения в ячейках с формулами. - 36 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Упражнение 2 Итоговые функции в Excel Некоторые функции Excel возвращают одно значение, в то время как аргументом имеют блок или несколько блоков. Такие функции будем называть итоговыми. Наиболее часто используемой из таких функций является СУММ − для ее ввода на панели «Стандартная» даже размещена специальная кнопка. Эта функция как бы подводит итог колонке чисел − отсюда и название для всей группы. Эти функции входят в категории «Статистические» и «Математические». Функция СУММ допускает до 30 аргументов. Поэтому с ее помощью можно находить сумму чисел из нескольких блоков. Допустима, например, такая формула =СУММ(В2:В9;12;-4.96;А4:С18). Если в блоке в какой-либо ячейке находится текстовое значение, то оно считается равным нулю. Кроме суммы к итоговым функциям относятся, например: МАКС и МИН − вычисление максимального и минимального значений, СРЗНАЧ − среднее арифметическое значение и т.д. Прочие итоговые функции вы можете найти в «Справке». К итоговым можно отнести функции И и ИЛИ. Пример 4 данные Имеются метеостанции – количество осадков (в мм) (Рис. 48). Необходимо получить суммарное, максимальное, минимальное и среднемесячное количество осадков. Решение. Введите формулы: в В17 =СУММ(В3:В14); в В18: =МАКС(В3:В14); в В19: =МИН(В3:В14); в В20: =СРЗНАЧ(В3:В14). Далее эти формулы скопированы в С17:D20. В блоке Е17:Е20 подведены итоги за три года. В Е17 формула =СУММ(В17:D17), в Е18: т.д. На =MAKC(B18:D18) и Рис. 48 Рис. 49 - 37 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» результаты наложен формат: одна цифра после десятичного разделителя (кнопка «Уменьшить разрядность»). Существует две полезные функции, примыкающие к итоговым: СЧЕТЕСЛИ и СУММЕСЛИ. Их названия показывают, что они считают и суммируют не все данные, а только удовлетворяющие некоторому критерию. Функция СЧЕТЕСЛИ (интервал; критерий) подсчитывает в интервале (т.е. блоке) количество значений, удовлетворяющих критерию. Пример 5 Вычислим количество засушливых месяцев, т.е. месяцев, когда выпадало менее 10 мм осадков (Рис. 49). В ячейке В22 формула =СЧЕТЕСЛИ(В3:В14;"<10"). Критерий взят в двойные кавычки, как текстовая строка. Формула скопирована в C22:D22. В Е22 подсчитана сумма. Функция СУММЕСЛИ(интервал;критерий;сумм_интервал) устроена сложнее. Значения, удовлетворяющие критерию, выбираются из блока, заданного первым аргументом, суммируются соответствующие значения из сумм_интервал, заданного третьим аргументом. Если третий аргумент опущен, то суммируются ячейки в аргументе интервал. Предположим, нужно вычислить суммарные осадки, которые выпали в незасушливые месяцы. Дополним таблицу (Рис. 49). В ячейку В23 введена формула =СУММЕСЛИ(B3:B14;">=10"), след което се копира в C23:D23. Тази функция може да се използва за решаване на по-труден проблем: какво е общото количество на валежите през 1993 г. в онези месеци, които са били сухи през 1994 г. Решението се дава по формулата = SUMIF(D3 :D14;"<10"; С4:С15), которая возвращает значение 128,6. Поместите ее в ячейку В24. К итоговым можно отнести еще две функции: НАИБОЛЬШИЙ(блок;k) и НАИМЕНЬШИЙ(блок;k). Первая из этих функций возвращает k-e наибольшее значение из множества данных, а вторая − наименьшее. Пример 6 В ячейки A1:D1 введем набор чисел. В блок А2:А5 введем формулы, которые показаны в соседнем столбце Рис. 50 (Рис. 50). Для понимания работы функции важно отметить, что третье наибольшее значение в блоке не 2, как можно было бы подумать, а 6, т.е. совпадает со вторым наибольшим значением. И еще: если в блоке n элементов, то функция НАИБОЛЬШИЙ(блок,n) возвращает минимальное значение, что мы и видим в примере. Обратите внимание, что в А2:А5 получен исходный массив чисел, отсортированный по убыванию. Если в исходном блоке изменить какое-либо число, - 38 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» то блок А2:А5 будет автоматически перестроен. В этом отличие от операции сортировки, которая проводится самим пользователем при необходимости. Обязательно сохраните результаты примеров 4-6, так как мы будем их использовать позднее. Упражнение 3 Табличные формулы в Excel Табличные формулы − очень мощное средство Excel, позволяющее в формулах обращаться с блоками, как с обычными ячейками, давать компактные решения сложных задач. В некоторых задачах вообще невозможно обойтись без табличных формул. Пример 7 Пусть нам известна сумма прихода и сумма расхода. Необходимо вычислить доход как разность этих величин. Введите исходные данные (Рис. 51). Рис. 51 В ячейку D2 можно набрать формулу: =В2-С2, а затем скопировать ее в D3:D5. В этих ячейках появятся формулы =В3-С3 и т.д. Однако здесь фактически из вектор-столбца В2:В5 вычитается вектор-столбец С2:С5. Поэтому можно непосредственно вычесть из вектора вектор одной формулой, а не создавать отдельные формулы для компонент вектора. Создание имен. Для наглядности дадим векторам имена. Выделите диапазон со вторым и третьим столбцами таблицы (В1:С5) и дайте команду меню «Вставка» ⇒ «Имя» ⇒ «Создать». Диапазон В2:В5 получит имя «Приход», а диапазон С2:С5 имя «Расход». Ввод табличной формулы с использованием имен диапазонов. Прежде мы вводили формулу в отдельную ячейку. А сейчас введем ее в диапазон. Подробно опишем шаги. Выделим блок D2:D5. В этом блоке активна ячейка D2. Наберем знак равенства =. Нажмем функциональную клавишу F3. Появится диалоговое окно «Вставка имени». Выберем имя «Приход» и щелкнем Оk. Формула примет вид: =Приход. Наберем знак минус -. Вновь нажмем клавишу F3. В диалоговом окне «Вставка имени» выберем имя «Расход» и щелкнем Ok. Формула примет вид: =Приход-Расход. Нажмем сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=Приход-Расход}. - 39 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Прокомментируем шаги. На третьем и пятом шаге мы выбирали имя из списка имен. Можно было ввести имя непосредственно с клавиатуры, но предложенный метод проще и нет риска ошибиться в наборе имени. На шестом шаге мы нажимаем не Enter, как ранее при вводе формулы, a Shift+Ctrl+Enter (при нажатии клавиши Enter должны быть нажаты обе клавиши Shift и Ctrl). Это очень важно. Если бы мы нажали Enter, то формула была бы введена только в активную ячейку блока D2 (Проверьте!). Фигурные скобки, окружающие формулу, говорят о том, что это табличная формула. Эти скобки нельзя набирать вручную (формула будет воспринята как текст). Ввод табличной формулы. Разумеется, табличную формулу можно вводить и без использования имен. Скопируйте блок А1:С5 в А8:С12. Повторите все шаги. Выделите блок D9:D12. В этом блоке активной ячейкой является D12. Наберите знак равенства =. Выделите блок В9:В12, наберите знак минус -, выделите блок С9:С12, нажмите сочетание клавиш Shift+Ctrl+Enter. Во всех ячейках блока появится формула {=В9:В12-С9:С12}. Мы получили две идентичные таблицы. Выделение блока с табличной формулой. Выделите одну из ячеек блока и нажмите клавишу F5 (эквивалент пункта меню «Правка» ⇒ «Перейти»). В диалоговом окне щелкните по кнопке «Выделить», установите переключатель «Текущий массив». Изменение табличной формулы. Попытайтесь очистить одну из ячеек, занятую табличной формулой. Например, выделите ячейку D8 и нажмите клавишу Del. В этом случае должно появится сообщение «Нельзя изменять часть массива». Удалить блок можно только целиком. Отредактировать формулу можно так: выделить блок с формулой, нажать функциональную клавишу F2, внести изменения в формулу, нажать сочетание клавиш Shift+Ctrl+Enter. (Попробуйте, например, ввести формулу {=Приход-Расход-1}, потом отмените это.) Коррекция табличной формулы при увеличении блока. Добавьте в обе таблицы на рабочем листе строку с данными: Год - 1996, приход - 240, расход – 200. Необходимо посчитать прибыль за 1996. Раньше, когда формулы записывались в отдельные ячейки, мы бы поступили просто: скопировали бы формулу из ячейки D5 в D6. Проделаем это для первой таблицы. Вместо ожидаемого 40 получим результат 50, т.е. число из первой ячейки блока с табличной формулой. Та же операция для второй таблицы даст правильный результат 40, но в строке формул мы увидим {=В13:В16-С13:С16} – образовался второй блок, что вовсе не входило в наши планы. Удалим формулы в ячейках D6 и D13. Правильное решение для первой и второй таблиц разное. Для первой таблицы изменим именованные блоки (выделим В1:С6 и «Вставка» ⇒ «Имя» ⇒ «Создать», для каждого имени Excel задаст вопрос: «Заменить» существующее определение имени?» Отвечаем «Да»). Выделяем D2:D6, нажимаем клавишу F2 (редактирование) и, ничего не изменяя в формуле, нажимаем клавиши Shift+ Ctrl+Enter. Для второй таблицы выделяем D8:D13, нажимаем клавишу F2 и редактируем формулу. Выделим в формуле подстроку В8:В12 и выделим блок В8:В13, также поступим с блоком С8:С12 либо просто заменим в адресах блоков цифру 2 на цифру 3. Нажимаем сочетание клавиш Shift+Ctrl+Enter. - 40 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Коррекция табличной формулы при уменьшении блока. Теперь мы хотим удалить в каждой из таблиц строку для 1996 г. Для первой таблицы вновь изменяем поименованные блоки (в ячейке D6 результат отображается как #Н/Д - недоступно). Выделяем блок с табличной формулой, нажимаем клавишу F2 и добавляем в самое начало формулы апостроф (он расположен на клавише с буквой "Э"). Формула превращается в текст. Вводим этот текст во все ячейки (клавиши Ctrl+Enter). Табличная формула прекратила существование. Очищаем последнюю строку таблицы. Выделяем блок D2:D5, нажимаем клавишу F2, удаляем апостроф, нажимаем клавиши Shift+Ctrl+Enter. Аналогично поступаем со второй таблицей. Как видим, процедура непростая и неприятная. Для решения задачи проще было воспользоваться простыми формулами. Но применение табличных формул, как мы убедимся, дает такие дополнительные возможности, что с неудобствами, связанными с изменениями этих формул, придется смириться. Упражнение 4 Дистрибутивные функции в Excel В Excel можно к блоку применить функцию (большое множество функций Excel), с тем, чтобы она вернула новый блок, содержащий значения функции для элементов исходного блока. Пример 8 Пусть в блоке А1:А4 записаны числа 1, 4, 9, 16. Поместим в B1:В4 табличную формулу {=КОРЕНЬ(А1:А4)}. Будет выведен столбец значений: 1, 2, 3, 4. Можно считать, что функция КОРЕНЬ была применена к вектору из четырех компонент и вернула новый вектор. (Разумеется, тот же результат можно было получить, записав в В1 формулу =КОРЕНЬ(А1) и скопировав ее в блок В2:В4.) Теперь рассмотрим функцию, которую нельзя применять к блоку. Поместим в D1:D2 логические значения ИСТИНА и ЛОЖЬ. В блок Е1:Е2 запишем табличную формулу {=И(D1:D2;”ИСТИНА”)}. Эта формула вернет значение ЛОЖЬ во всех ячейках блока Е1:Е2. Получается, что функцию КОРЕНЬ можно применять к массиву, а функцию И – нет. Функции, которые можно применять к списку, называются дистрибутивными. Продолжим пример с вычислением квадратного корня от элементов блока. Мы хотим вычислить сумму корней ∑ ai . Поместим в ячейку В5 формулу =СУММ(В1:В4). Результат, разумеется, 10. А теперь вычислим эту же сумму, не используя промежуточный блок В1:В4. Поместим в ячейку А5 табличную формулу {=СУММ(КОРЕНЬ(А1:А4))}. Обратите внимание, хотя формула возвращает значение в одной ячейке, она должна вводиться как табличная, т.е. ее ввод заканчивается нажатием комбинации клавиш Shift+Ctrl+Enter. Для сравнения введите в А6 эту формулу как обычную, – она вернет сообщение об ошибке #ЗНАЧ!. - 41 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» Итак, к массиву, возвращаемому дистрибутивной функцией, можно применить итоговую функцию, которая возвращает всего одно значение. Но вводить такую формулу нужно как табличную. Пример 9 Рис. 52 Функцию ИЛИ можно использовать в табличных формулах, но как итоговую, т.е. если ИЛИ имеет всего один аргумент и этот аргумент – блок. Пусть в блоке А1:В2 находятся названия планет (Рис. 52). В ячейку D2 введем табличную формулу {=ИЛИ(СОВПАД(А1:В2;D1))}. Функция СОВПАД возвращает логическое значение ИСТИНА или ЛОЖЬ в зависимости от совпадения или несовпадения своих аргументов – текстовых строк. Если бы в А4:В5 была введена формула массива {=СОВПАД(А1:В2;D1)}, то она вернула бы четыре значения (Рис. 52). Функция ИЛИ(А4:В5) возвращает значение ИСТИНА. Эти две формулы мы объединяем в одну табличную формулу, которую и ввели в D2. Пример 10 Вернемся к задаче обработки данных метеостанции (пример 4 и пример 5). Для расчета количества засушливых месяцев, т.е. месяцев, когда выпало менее 10 мм осадков, очень удобно использовать функцию СЧЕТЕСЛИ. Однако с ее помощью нельзя получить количество месяцев, на протяжении которых количество осадков лежало бы в диапазоне от 20 до 80 (назовем такие месяцы нормальными). Для этого необходимо использовать дистрибутивные функции. Скопируйте текст из ячейки А22 в ячейку А25 и откорректируйте его: «Количество нормальных месяцев». Сначала подсчитаем месяцы с нормальным количеством осадков. Будем использовать вспомогательный блок F3:H14 тех же размеров, что и блок с исходными данными. В ячейку F3 вводим формулу =ЕСЛИ(И(В3>20;B3<80);1;0) и копируем ее в остальные ячейки блока F3:H14. В блоке выводятся нули и единицы. Введенная формула является индикаторной функцией множества нормальных месяцев, т.е. 1 – выводится, когда количество осадков лежит в пределах между 20 и 80 мм и 0 – в противном случае. Остается подсчитать сумму таких месяцев. Для этого введем в ячейку F25 формулу =СУММ(F3:F14) и скопируем ее в блок G25:H25. А теперь решим эту же задачу без использования вспомогательного блока. Введем в В25 табличную формулу {=СУММ(ЕСЛИ(В4:В15>20;АКО(B4:B15<80;1;0);0))} и скопируем ее в C25:D25. (Таким образом, машина, перебирая значения в указанном блоке, проверяет больше или меньше текущее число 20 и, если оно меньше, прибавляет к исходному число 0. В противном случае – проверяет выполнение второго условия (менее 80). Если и второе условие выполняется, то к исходному числу прибавляет 1. В противном - 42 - Copyright ОАО «ЦКБ «БИБКОМ» & ООО «Aгентство Kнига-Cервис» случае – 0). Формула {=СУММ(ЕСЛИ(И(В4>20;B4<80);1;0))} не приведет к успеху, так как функция И не является дистрибутивной). Этот пример очень важен. Он Рис. 53 дает рецепт, как подсчитать в множестве количество элементов, удовлетворяющих определенному критерию. Нужно составить на основе вложенных функций ЕСЛИ индикаторную функцию подмножества и применить к ней итоговую функцию СУММ, введя формулу как табличную. Теперь вычислим суммарное количество осадков, выпавших в эти месяцы Введите в А26 текст «Осадки в нормальные месяцы», в В26 – табличную формулу {=СУММ(ЕСЛИ(В3:В14>20; АКО(B3:B14<80;В3:В14;0);0))} и скопируйте ее в C26:D26. В E25 и Е26 введите формулы для суммирования значений в строках (выделите блок В25:Е26 и щелкните кнопку «Автосумма»). Вы получите блок, показанный на рис. 53. Пример 11 В блоке А1:А10 записана числовая последовательность. Проверьте, является ли она возрастающей. Решение. Перейдите на новый лист. Запишите любую последовательность чисел в блок А1:А10. Окончательное решение можно записать одной формулой (поместите его в ячейку A12). {=ЕСЛИ(СУММ(ЕСЛИ(А2:А10-А1:А9>0;1;0))=БРОЙ(A1:A10)1;"увеличаване";"не нараства")) Сега нека анализираме тази формула: A2:A10-A1:A9 A8 се изважда от A9 и т.н.) - образува блок, състоящ се от първите разлики на елементите на оригиналния блок; IF(А2:А10-А1:А9>0;1;0) – съставлява блок от индикатори на положителни първи разлики; SUM(IF(A2:A10-A1:A9>0;1;0)) – брои броя на ненулевите елементи в блока от индикатори; COUNT(А1:А10)-1 – изчислява размера на блока от индикатори, равен на размера на оригиналния блок, намален с 1; ако броят на ненулевите елементи в блока от индикатори е равен на размера на блока от индикатори, тогава последователността се увеличава, в противен случай не е така. Опитайте се да изградите съответните блокове и крайните функции от тях на етапи, за да постигнете ясно разбиране за това как е съставена крайната формула. - 43 - Copyright OJSC "Central Design Bureau "BIBCOM" & LLC "Agency Book-Service" Упражнение 5 Матрични операции в Excel Най-простите операции, които могат да се извършват с матрици: събиране (изваждане), умножение с число, умножение, транспониране, изчисление обратна матрица . Пример 12 Събиране на матрица и умножение на матрица с число. Събираме матрици M и N, където − 1 0 4  2 − 3 7 M = и N =   2 − 3 5 .    − 1 5 6 Решение. Въвеждаме матриците M и N в блоковете A1:C2 и E1:G2. В блок A4:C5 въвеждаме таблична формула (=A1:C2+E1:G2). Имайте предвид, че избраният блок има същите размери като оригиналните матрици. Какво се случва, ако изберете блок A4:D6, преди да въведете формулата? #N/A ще се появи в "допълнителните" клетки, т.е. „Недостъпен“. И ако изберете A4: B5? Ще се покаже само част от матрицата, без никакви съобщения. Проверете. Използването на имена прави въвеждането на формула в електронна таблица много по-лесно. Дайте на диапазоните A1:C2 и E1:G2 имената съответно M и N (изпълнете командата за всеки блок „Вмъкване“ ⇒ „Име“ ⇒ „Присвояване“). В блок E4:G5 въведете табличната формула (=M+N). Резултатът, разбира се, трябва да е същият. Сега нека изчислим линейната комбинация от 2M-N матрици. В блок A7:C8 въвеждаме таблична формула (=2*M-N). Трябва да получите следните резултати:  5 − 6 10 1 − 3 11 M +N = и 2 M − N = − 4 13 7  .    1 2 11 Горните примери ни водят до заключението, че обичайната операция на умножение по отношение на блокове не е съвсем еквивалентна на умножението на матрици. Наистина, за матрични операции в Excel има функции, включени в категорията "Математически": MOPRED - изчисляване на детерминантата на матрицата; MOBR - изчисляване на обратната матрица; MULTIP - матрично умножение; TRANSPOSE - транспониране. Първата от тези функции връща число, така че се въвежда като обикновена формула. Останалите функции връщат блок от клетки, така че трябва да бъдат въведени като таблични формули. Първата буква "М" в името на трите функции е съкращение от думата "Матрица". Пример 13 Изчислете детерминанта и обратна матрица за матрицата - 44 - Copyright JSC "Централно проектантско бюро "БИБКОМ" & LLC "Агенция Книга-Сервиз" − 73 78 24 A =  92 66 25 .   − 80 37 10  Проверете правилността на изчислението на обратната матрица, като я умножите по оригиналната. Повторете тези стъпки за същата матрица, но с елемент a33=10.01. Решение. Нека поставим оригиналната матрица в блок A1:C3. В клетка B5 поставяме формулата за изчисляване на детерминантата = MOPRED (A1: C3). В блок A7:C9 въвеждаме формула за изчисляване на обратната матрица. За да направите това, изберете блока A7:C9 (има три реда и три колони, като оригиналната матрица). Нека въведем формулата (=MOBR(A1:C3)). Дори ако използвате съветника за функции, трябва да завършите въвеждането си, като натиснете клавишната комбинация Shift+Ctrl+Enter (вместо да щракнете върху бутона OK). Ако сте забравили да изберете предварително блок A7:C9 и сте въвели формулата в клетка A7 като обикновена формула на Excel (завършете с натискане на Enter), тогава не е необходимо да я въвеждате отново: изберете A7:C9, натиснете F2 (редактирайте ), но не променяйте формулата, просто натиснете клавишите Фиг. 54 Shift+Ctrl+Enter. Копирайте блок A1:C9 в блок E1:G9. Променете леко един елемент от оригиналната матрица: в клетка G3, вместо 10, въведете 10.01. Промените в детерминантата и в обратната матрица са поразителни! Този специално подбран пример илюстрира числената нестабилност на изчислението на детерминанта и обратната матрица: малко смущение на входа дава голямо смущение на изхода. За по-нататъшни изчисления ще присвоим имената на матриците в работния лист: A1: C3 - A, A7: C9 - Ainv, E1: G3 - AP, E7: G9 - APinv. За да се появят тези имена във вече въведени формули, изберете съответните формули, изберете елемента от менюто "Вмъкване" ⇒ "Име" ⇒ "Прилагане", изберете необходимите имена в диалоговия прозорец и щракнете върху "OK". Сега нека проверим правилността на изчислението на обратната матрица. В блок A12:C14 въвеждаме формулата (=MUMNOT(A,Ainv)), а в блок E12:G14 - формулата (=MUMNOT(AP,APinv)). Трябва да получите резултат като на фиг. 54. Както се очаква, получените матрици са близки до идентичността. - 45 - Copyright JSC "Централно конструкторско бюро "BIBCOM" & LLC "Agency Book-Service" Имайте предвид, че наборът от матрични операции в Excel е лош. Ако трябва сериозно да работите с матрици, по-добре е да прибягвате до помощта на такива математически пакети като MatLAB (Matrix LABoratory), Mathematica, Derive. - 46 - Copyright OJSC "Central Design Bureau "BIBCOM" & OOO "Agency Kniga-Service" Лабораторна работа № 6 Упражнение 1 Намиране на решение В лабораторна работа № 4 разгледахме пример за автоматично намиране на функционалната зависимост Y = f(X). Спомнете си, че намирането на такава зависимост е необходимо за прогнозиране на стойностите на отговора - параметър Y на изхода на експеримента от фактора - независими променливи X на входа на системата (виж Лабораторна работа № 4). В някои Фиг. 55 случая, представени в Функции на Excel не е достатъчно. Ето защо е важно да можете сами да изберете такава функция, като използвате един от методите за математическа оптимизация, например метода на най-малките квадрати. Нейната същност е да минимизира сумата на квадратната разлика между експериментални (Yexper) и изчислени (Ycalculation) данни: n ∑ (Yexper,i − Ycalculation,i) 2 , i =1 (4) където n в нашия проблем е равно на 10 Отворете Lab 4 и продължете да попълвате работния лист. Експерименталните Y вече са въведени. Сега нека попълним таблицата с изчислен Y. За да направим това, се нуждаем от допълнителна таблица с коефициенти, чиито стойности първо приравняваме на 1 (фиг. 55). Сега въведете формулата за полином от втора степен (1) за Y изчисление (фиг. 55). Следващата задача е да направим Фиг. 56 изберете коефициентите на уравнението така, че разликата между Ycalculation и Yexper да е минимална. За да направите това, трябва да въведете формулата за изчисляване на квадрата на разликата (3) и формулата за изчисляване на критерия Pearson, за да оцените точността на нашето изчисление (фиг. 56). И двете формули са вградени в Excel и са примери за функции, за които можете да се справите, без да въвеждате формули за електронни таблици (вижте Лаборатория #4 по-горе). Отворете съветника за функции по начин, който знаете. В категорията Математика изберете формулата SUMQDIFF и щракнете върху Ok. Във втория прозорец на съветника за функции на фиг. 57 Като array_x, въведете масив Yexper, като array_y, array Ycalculation и щракнете върху Ok. Формулата за изчисляване на критерия Pearson е в категорията "Статистически" (функция PEARSON). Във втория прозорец на съветника за функции също въведете масива Yexper като array_x, масива Ycalculation като array_y и щракнете върху Ok. За да намерите стойности на коефициента, Excel има добавка Solver, която ви позволява да решавате задачи за намиране на най-големите и най-малките стойности, както и да решавате различни уравнения. Изберете клетката, в която е въведена формулата за изчисляване на квадрата на разликата и изпълнете командата "Инструменти" ⇒ "Търсене на решение". Ако в менюто „Услуга“ няма такава команда, първо трябва да изпълните командата „Услуга“ ⇒ „Добавки“ и в диалоговия прозорец, който се отваря, поставете превключвателя в колоната „Търсене на решение“ ( Фиг. 57), и едва тогава изпълнете "Услуга" ⇒ "Търсене на решение". В диалоговия прозорец "Търсене на решение" (фиг. 58) въведете следните параметри: адреса на целевата клетка със стойността, която трябва да се избере (адреса на клетката с формулата за сумата на квадратната разлика) , ако предварително сте го избрали, тогава адресът се поставя автоматично; в полето "Равно на:" задайте превключвателя на "минимална стойност"; - 48 - Copyright АД "Централно конструкторско бюро "БИБКОМ" & LLC "Агенция Book-Service" в полето "Промяна на клетки" въведете диапазона от клетки за променливи коефициенти. Бутонът "Параметри" се използва за промяна и конфигуриране на параметрите за търсене. На фиг. 59 техният брой включва: начина на решаване на задачата, времето на изчисленията и точността на резултатите. В повечето случаи обаче е достатъчно да използвате настройките по подразбиране. Търсенето на решение се извършва след щракване върху бутона "Изпълни". Ако търсенето на решение приключи успешно, резултатите от изчисленията се въвеждат в изходната таблица и на екрана се появява диалоговият прозорец „Резултати от търсенето на решение“ (фиг. 59), с който можете да запишете намерените решения в изходната таблица, възстановете първоначалните стойности, запишете резултатите от търсенето на решение под формата на скрипт, генерирайте отчет за резултатите от операцията за търсене на решение. Сравнете получените стойности на коефициента с коефициентите в уравнението на тренд линията. Добавете изчислените Y стойности към графиката. За да направите това, отидете в прозореца на диаграмата, щракнете с десния бутон някъде в него и изберете командата "Първоначални данни" от контекстното меню. В диалоговия прозорец със същото име, който се отваря (фиг. 60), отидете в раздела "Ред" и щракнете върху бутона "Добавяне". В полето Име щракнете върху бутона за минимизиране на прозореца, Фиг. 60 отидете на листа с вашите данни, изберете заглавната клетка на колоната Ycalculation и се върнете към прозореца, като използвате бутона за увеличаване на прозореца. По същия начин Фиг. 58 - 49 - Copyright OJSC "TsKB "BIBCOM" & LLC "Agency Book-Service" въведете "X-стойности" (диапазон от клетки с X-стойности или температура) и "Y-стойности" (диапазон от клетки с изчислени Y стойности). Когато приключите с въвеждането, натиснете бутона Ok. Моля, обърнете внимание, че точките за изчисляване на Y попадат на тренд линията, която построихме по-рано (фиг. 61). И накрая, не забравяйте да запазите файла си, ние ще го използваме в следващия урок (вижте Лабораторна работа #7 по-долу). y = -0,0054x2 + 0,6014x - 5,9667 2 R = 0,9817 Y=f(x) Yexp Y Изчислителен полином (Yexp) 12 10 8 6 Y 4 2 0 -2 10 20 30 40 50 60 X 61 - 50 - 70 80 90 100 Copyright JSC "Централно конструкторско бюро "BIBCOM" & LLC "Agency Kniga-Service" Лабораторна работа №7 Упражнение 1 Намиране на решение на задача с два параметъра в Excel от една променлива). В действителност такива прости зависимости са доста редки. По-често трябва да се справяте с многопараметрични функции. Как да се справяме с такива зависимости и как да ги визуализираме, нека разгледаме примера на проблем с два параметъра. Нека се проведе експеримент, например, ние измерихме зависимостта на някакъв параметър от температурата и налягането. Средната температура беше 100°C. Стъпката на промяна е 50оС. Средно налягане - 2 atm. Стъпката на промяна е 1 атм. Такава система ще бъде описана чрез връзката: Y = f (X1, X 2) , (5) което е повърхност, която често се показва във форма, подобна на контурна карта (фиг. 62). Ориз. 62 - 51 - Copyright OJSC "TsKB "BIBCOM" & LLC "Agency Kniga-Service" За да открием тази зависимост за нашия случай, ще използваме подготовката на уроци 4 и 6. За да направите това, отворете записания файл и отидете на лист с данни. Щракнете върху прекия път Sheet и изберете командата Move/Copy (фиг. 63). В диалоговия прозорец, който се отваря (фиг. 64), можете да изберете къде искаме да преместим (копираме) нашия лист (в текущата книга или в нова). Изберете заглавието на текущата книга; пред кой лист искаме да поставим текущия лист или негово копие. Изберете „(преместване до края)“. Не забравяйте да поставите отметка в квадратчето „Създаване на копие“, в противен случай листът просто ще се премести в края на книгата. След това щракнете върху OK. По подразбиране Excel създава копие с името на текущия лист, добавяйки номера на копието в скоби в края. Нека го преименуваме за удобство. За да направите това, кликнете върху етикета на листа и изберете командата "Преименуване" (фиг. 63); въведете ново име, например "Experiment_2" и натиснете клавиша "Enter". Първо, изграждаме отново таблицата с изходни данни, както е показано на фиг. 65. Изберете две клетки в горната част на старата таблица (тези, в които е поставено името на параметъра "Температура" и неговата стойност) и изпълнете командата "Вмъкване" ⇒ "Клетки ...". Това ще отвори диалоговия прозорец Добавяне на клетки, предлагащ тяхното местоположение (Фигура 66). Поставете превключвателя на позиция "клетки с изместване надолу" и щракнете върху бутона Ok. - 52 - Фиг. 63 Фиг. 64 Фиг. 65 Фиг. Изберете празна колона C (щракнете върху заглавката на тази колона) и изпълнете командата "Вмъкване" ⇒ "Колони". Направете необходимите промени в таблицата (фиг. 65). Приведете експерименталната таблица по подобен начин във формата, показана на фиг. 67. Спомнете си, че заглавията на колоните "Температура" и "Налягане" трябва да бъдат въведени съгласно формули, за да направят детайла по-универсален. Ориз. 67 Нека сега попълним данните от таблицата "Експеримент". Координатите на точки 1 - 9 могат да бъдат изчислени в съответствие с фиг. 62 по следните формули: № 1 2 3 4 5 6 7 8 9 Темп. Xav,1-стъпка Xav,1 Xav,1+стъпка Xav,1-стъпка Xav,1 Xav,1+стъпка Xav,1-стъпка Xav,1 Xav,1+стъпка Налягане Xav,2-Step Xav,2-Step Xav,2-Step Xav,2 Xav,2 Xav,2 Xav,2-Step Xav,2-Step Xav,2-Step възможност за копиране. Трябва да вземем стойностите на Yexper от експеримента. Нека са равни: Точка № YExper 1 1 2 7 3 5 4 17 5 25 6 15 Y изчисление трябва да се изчисли по формулата: Y изчисление = A0 + A1 X 1 + A2 X 2 + A11 X 12 + A12 X 1 X 2 + A22 X 22 . - 53 - 7 3 8 10 (6) 9 4 Copyright JSC "Central Design Bureau "BIBCOM" & OOO "Agency Kniga-Service" Преди да въведете формулата (6), е необходимо да промените таблицата с коефициенти, както е показано в фиг. 68, като въведете първоначалните стойности на коефициентите 1. За да изберете функцията, ще използваме метода за минимизиране на сумата от квадратите на разликата между експерименталните (Yexper) и изчислените (Ycalculation) данни, които разгледахме в последен урок. Ориз. 68 Вече имаме формулите за изчисляване на квадрата на разликата и формулата за изчисляване на критерия на Пиърсън на листа. Сега е достатъчно да коригирате връзките в тях и да изпълните. Търсенето на решение се извършва по същия начин, както при функция с един параметър, но тъй като нашата зависимост е по-сложна, е необходимо да отворите подпрозореца "Параметри" в диалоговия прозорец "Търсене на решение" кутия (фиг. 69) и задайте следните опции: толеранс - 1%; "Автоматично мащабиране"; оценки - "Квадратичен"; разлики - "Централен". Ориз. 69 След това щракнете върху бутона Ok и в прозореца "Търсене на решение" - "Изпълни". Ако не се постигне задоволителна точност при първия опит, операцията по търсене на решение може да се повтори. Накрая трябва само да изградим повърхността. За да направим това, първо изграждаме матрица с данни на нов лист (фиг. 70). Отидете на нов лист и въведете заглавие на таблица. - 54 - Copyright OJSC "Централно конструкторско бюро "BIBCOM" & LLC "Agency Book-Service" Снимка. 70 X и Y стойности се изчисляват с помощта на формули. За да въведете първата стойност на налягането, въведете "=", след това отидете на листа "Experiment_2" и щракнете върху клетката с минималната стойност на налягането (в нашия случай е 1) и натиснете клавиша "Enter". Трябва да следвате същите стъпки, за да въведете минималната стойност на температурата. Следващите стойности на температурата и налягането се изчисляват по формулата: Yi = Yi −1 + Ymax − Ymin , l (7) максималната стойност на температурата и налягането, съответно, l е стъпката на мрежата (нека бъде равна на 10 ). Въведете формули за изчисляване на втората стойност на налягането и температурата. Те ще изглеждат по следния начин: =B4+(Experiment_2!$G$11-Experiment_2!$G$3)/10. За влизане в третия и т.н. стойности на температура и налягане, използвайте маркера за пълнене. Остава да въведете само стойностите на функцията (5). Вземете стойностите на коефициентите от листа "Експеримент_2". Имайте предвид, че коефициентите трябва да се посочват в абсолютни стойности, докато стойностите за температура и налягане трябва да се смесват. Формулата трябва да изглежда по следния начин: =Експеримент_2!$B$7+Експеримент_2!$B$8*$A5+Експеримент_2!$B$9*B$4 +Експеримент_2!$B$10*$A5^2+Експеримент_2!$B$11* $ A5*B$4+ Experiment_2!$B$12*B$4^2 Данните за начертаване на повърхността са готови, остава само да се нанесе върху диаграмата. - 55 - Copyright JSC "Central Design Bureau "BIBCOM" & OOO "Agency Book-Service" Използвайте "Diagram Wizard", като изберете типа на диаграмата "Surface" (вижте Лабораторна работа № 4). Крайната форма на диаграмата ще изглежда нещо подобно на показаното на фиг. 71. Фиг. 71 - 56 - Copyright JSC "TsKB "BIBCOM" & OOO "Agency Book-Service" 1. 2. 3. 4. 5. Литература Fulton, D. Овладейте самостоятелно Microsoft Excel 2000. 10 минути на урок. / Д. Фултън. – М.: Издателска къща Уилямс, 2001. – 224 с. Левин, А.Ш. Excel е много лесен! / А.Ш. Левин. - Санкт Петербург: Питър, 2004. - 74 с. Безручко, В.Т. Семинар по курса "Информатика". Работа с Windows 2000, Word, Excel: учеб. надбавка. / В.Т. Безручко. - М.: Финанси и статистика, 2003. - 544 с. Лавренов, С.М. Excel: Сборник с примери и задачи. / СМ. Лавренов - М.: Финанси и статистика, 2004. - 336 с. Воробьов, Е.С. Основи на информатиката. Как се работи в MS Office среда. Proc. помощ / E.S. Воробьов, Е.В. Николаев, Воробиева Ф.И., Казан. състояние технолог. un-t. Казан, 2005. - 84 с. - 57 - Copyright OJSC "Централно конструкторско бюро "BIBCOM" & LLC "Agency Book-Service" Съдържание Лабораторна работа № 1 ........................ ......................................................... ...... .................. 3 Упражнение 1: Разбиране на концепциите за електронни таблици в Excel .................. ................ ...................... 3 Упражнение 2: Прилагане на основни техники за електронни таблици: Въвеждане на данни в клетка. Форматиране на шрифта. Промяна на ширината на колоната. Автоматично довършване, въвеждане на формула, граници на таблица, подравняване на текста към центъра на селекцията, набор от индекси и горни индекси ........................... ................................... 6 Лабораторна работа номер 2 .......... ............................................ ............. .................................. 10 Упражнение 1. Затвърдяване на основните умения за работа с електронни таблици, запознаване с понятията: сортиране на данни, видове подравняване на текст в клетка, числов формат. ................... 10 Упражнение 2. Въвеждане на понятието „абсолютна връзка“, задаване на точната стойност на ширината на колоната с помощта на командите на хоризонталното меню. Вмъкване на функция с помощта на съветника за функции .............................................. ........................ ........................ ....... 13 Упражнение 3. Въвеждане на понятието "име на клетка"...... ........................ ....................... 16 Лаборатория № 3 ..................... ............................................................. ............................. 19 Упражнение 1: Промяна на ориентацията на текста в клетка, запознаване с възможностите на базите данни на Excel. Сортиране на данни по множество ключове ............................................. ................. .................... 19 Лабораторна работа #4 ....... ................ ................................. ............... ................................. 27 Упражнение 1: Създаване и редактиране на графики в документ на Excel 27 Упражнение 2: Създаване и редактиране на повърхности в документ на Excel 33 Лабораторна работа #5 ...... ............................................ ...... ............................................ .. 35 Упражнение 1. Булева изрази в Excel ................................................. ............. 35 Упражнение 2. Обобщаващи функции в Excel .................................. .................................. 37 Упражнение 3. Формули за електронни таблици в Excel ....... ... ................................................ 39 Упражнение 4 Функции за разпределение в Excel .................................................. .................... ... 41 Упражнение 5. Матрични операции в Excel ................... .................................................. 43 Лаборатория #6 .. ............................ ...................... ........................ ............... 47 Упражнение 1. Намиране на решение . .............................. .................... ................................. 47 Лаборатория #7 ................. ............................................ ............ ....................................... 51 Упражнение 1 ........ ......................................... ......... ..................... 57 - 58 - Авторско право OJSC "Централно конструкторско бюро "BIBCOM" & LLC "Agency Book-Service" Редактор: T.M. Петрова Лиценз № 020404 от 06.03.1997 г. Подписана за печат Хартия за писане. Уч.-изд. л. 2005 г. Печат Формат 60x84 1/16 арб. фурна л. Тираж 100 бр. Поръчка “C” 60 Издателство на Казанския държавен технологичен университет Офсетна лаборатория на Казанския държавен технологичен университет 420015, Казан, ул. К. Маркс, 68



Препоръчваме за четене