Фамилия, имя, отчество: Куренкова Анастасия Павловна и Гвоздикова Анастасия Владиславовна

Фамилия, имя, отчество: Куренкова Анастасия Павловна и Гвоздикова Анастасия Владиславовна

Группа: 525

Работа выполнена: 10.07.2015

ВВЕДЕНИЕ

Удобство группировки данных и расчётов в виде таблиц общепризнанно. Именно поэтому стали очень популярными специальные программы, облегчающие ввод в ЭВМ табличных данных и всевозможные манипуляции над ними. Такие программы называют табличными процессорами. В настоящее время наиболее популярными из них являются программы Excel, входящие в пакет Microsoft Office разных версий для Windows.

В данных методических указаниях изложены основные приёмы ввода информации, выполнения разных по сложности вычислений и построения диаграмм средствами Excel 2000, а также приводятся задания для самостоятельной работы, без которой невозможно получение устойчивых навыков работы на компьютере. Для более полного ознакомления с возможностями табличного процессора Excel и другими примерами их использования можно обратиться, например, к [1– 6].

1. ОСНОВНЫЕ ПРАВИЛА РАБОТЫ В EXCEL

1.1. Список условных обозначений

· (Л’) – одинарный щелчок левой кнопкой мышки на объекте, указанном курсором;

· (Л”) – двойной щелчок левой кнопкой мышки на объекте, указанном курсором;

· (П') – одинарный щелчок правой кнопкой мышки на объекте, указанном курсором;

· (Л®) – перетаскивание мышкой объекта, который зацеплен курсором;

· <Ctrl> – название клавиши, которую следует нажать, или кнопки в окне Windows, по которой следует сделать (Л’);

· <Ctrl+Home> – одновременное нажатие клавиш, названия которых указаны между <>;

· Вставка®Имя®Присвоить… – движение по указанным пунктам меню для заказа нужного действия;

· названия программ, окон и параметров пишутся с большой буквы;

· при заказе параметров в диалоговом окне слово "выбираем" означает, что надо сделать (Л’) по нужным параметрам и затем (Л’) на кнопке подтверждения выбора (обычно <ОК>, <Готово>, <Применить>, <Далее> и т. п.);

· вместо мышки можно двигаться по кнопкам диалогового окна, нажимая клавиши <Tab> или <Shift+Tab>. Подтверждение выбора осуществляется клавшей <Enter>. Отказ от выбора – кнопкой <Отмена> или клавишей <Esc>.

1.2. Основные понятия Excel

Главное окно Excel аналогично окнам других приложений Windows. Его вид представлен на рисунке.

Файл документа, созданного в Excel, имеет расширение.xls и называется рабочей книгой. Книга состоит из отдельных листов. На них можно располагать данные, формулы, которые их обрабатывают, пояснительные тексты, индивидуальные программы пользователя для сложной обработки данных и т. д.

Листам можно присваивать удобные имена или пользоваться шаблонными именами "Лист1", "Лист2" и т. д. Заказ листа для работы – (Л’) на ярлычке листа, изменение имени – (Л”) на нём и, затем, набор нового имени вместо выделенного старого.

Рабочее поле листа разбито на ячейки. Каждая ячейка стоит на пересечении столбца и строки. Столбцы обозначаются буквами латинского алфавита, строки – числами. В адресе ячейки первым указывается столбец. Адрес активной ячейки, в которую поступает информация, набираемая на клавиатуре, указывается в адресном поле строки формул. Примеры адресов ячеек: V5, DA3, A78 и т. д. (но не 5V, 3DA, 78A).

Группа ячеек, которая обрабатывается одной командой, называется блоком. Прямоугольный блок ячеек задаётся адресами концов любой его диагонали, указанными через ":". Если блок имеет сложную форму, его разбивают на прямоугольные фрагменты и перечисляют их координаты через ";". Примеры блоков: А3:А3 – блок из одной ячейки, B2:D4 – блок из девяти ячеек (B2, C2, D2, B3, …, D4), Е2:Е4 – фрагмент столбца (Е2, Е3, Е4), А3:А6;D8:D12 – сложный блок из двух фрагментов столбцов. Если требуется сослаться на столбец (строку) целиком, в адресе указывают только одну компоненту. Пример: А:С – блок состоит из всех ячеек столбцов А, В и С, 12:34 – все ячейки строк с 12 по 34.

Вместо стандартных адресов ячейкам и блокам можно присваивать имена, удобные пользователю. Способы, которыми это можно сделать, описаны в п. 1.9.

1.3. Выделение блока ячеек

Ячейки, объединённые в блок, выделены рамкой и контрастным цветом. Одна из них (обычно верхняя левая) остаётся светлой. В неё можно вводить информацию, не снимая выделения с блока в целом. В правом нижнем углу выделена прямоугольная точка. Это маркер протягивания. Курсор мышки превращается на ней в тонкий знак “+”. (Л®) этого крестика называется протягиванием. Результаты, к которым приводит протягивание в разных случаях, описаны ниже.

Приёмы выделения прямоугольных блоков:

· Клавишами – <Shift + клавиши навигации курсора>.

· Мышкой – курсор в форме толстого крестика протащить по нужным ячейкам.

· Выделение целого столбца (строки) мышкой – (Л’) на маркере нужного столбца (строки) в адресной линейке.

· Выделение группы столбцов (строк) мышкой – (Л®) по маркерам нужных столбцов (строк) в адресной линейке.

· Выделение всего листа – (Л’) на пустой клетке, которая находится вверху слева на пересечении адресных линейки и столбца.

Если блок имеет сложную форму, первый фрагмент выделяется любым из перечисленных способов, а последующие добавляются к нему с помощью <Ctrl + (Л®)>.

Чтобы снять выделение, достаточно сделать (Л’) или нажать любую клавишу навигации курсора без <Shift>.

1.4. Ввод текстов

Текст вводится обычным способом, как на пишущей машинке или в документ Word. По умолчанию он прижимается к левому краю ячейки. Если в тексте часто встречаются повторяющиеся слова или выражения, ввод можно ускорить с помощью команды Сервис ® Автозамена…, которая действует также, как в Word’е.

Для текстов, расположенных в столбец, Автозамена включается автоматически: после набора первых букв Excel допечатывает текст, который уже был введён в этот столбец и начинался так же. Допечатанный текст выделяется контрастным цветом. Если пользователя устраивает такая замена, он переходит в другую ячейку, в противном случае он продолжает набор сам.

1.5. Ввод чисел

К числам относятся все данные, имеющие количественный смысл: арифметические числа, денежные суммы, даты, моменты времени и т. д. Единицы измерения при вводе опускаются. Если для удобства нужно видеть их на экране, их вводят в соседние ячейки как пояснительные тексты или выводят на экран с помощью команды Формат ® Ячейки… (вкладка Число). На экране числа прижимаются по умолчанию к правому краю ячейки.

При вводе арифметических чисел целая часть отделяется от дробной запятой, а не точкой, как это принято в большинстве языков программирования. На цифровой клавиатуре запятая вставляется автоматически при нажатии <. >. При вводе дат в качестве разделителя используют точку символьной клавиатуры. При вводе времени – двоеточие. Примеры: 1,5 – арифметическое число, 1.5 – дата первое мая. 1:5 – время пять минут второго.

Примечание. В некоторых диалоговых окнах клавиша <. > цифровой клавиатуры вводит точку. Если при работе в диалоговом окне Вы заметили это, следует набирать запятую на символьной клавиатуре.

Если вводимые числа меняются с постоянным шагом, можно воспользоваться автозаполнением ячеек. Для этого следует:

1. Ввести два первых числа ряда.

2. Выделить эти ячейки.

3. Протянуть их (см. п. 1.3) в том же направлении, в каком вводились первые числа, до тех пор, пока во всплывающем окне не появится конечное значение ряда.

Для ввода чисел, меняющихся по геометрической прогрессии, можно воспользоваться командой Правка ® Заполнить…

Задание.

· Введите в ячейки А1:А11 ряд чисел 1; 1,3; 1,6; …;4.

· Заполните ячейки В1:В21 теми же числами, но введёнными через строчку (два первых числа вводим через строчку, выделяем В1:В4, протягиваем до В21).

· Закажите для введённых в столбец А чисел денежный формат, для столбца В – процентный (команда Формат® Ячейки… (вкладка Число)).

1.6. Ввод стандартных списков.

Стандартными называются списки данных, постоянно хранящихся в памяти Excel. К ним относятся списки дней недели, месяцев и дней года и ряд других. Если ввести в ячейку какое-либо значение из списка и протянуть его в любом направлении, то в протянутые ячейки автоматически будут введены остальные элементы списка.

Набор стандартных списков можно изменять. Для этого следует:

1. выполнить команду Сервис ® Параметры… (вкладка Списки);

2. в окне Списки сделать (Л’) по строке Новый список;

3. в окне Элементы списка ввести нужные значения по одному на строке;

4. нажать кнопку <Добавить>;

5. нажать кнопку <ОК>.

Задание.

· Введите в С1:С50 даты, начиная с 15 мая, в D1:Х1 дни недели, начиная с субботы.

· Создайте список каких-либо предметов и введите его несколько раз, начиная с разных мест списка.

1.7. Ввод формул

Формулу, как и в математике, используют в тех случаях, когда известно не то, что следует ввести в ячейку, а только рецепт, как его получить. В зависимости от типа результата, который будет получен по этому рецепту, различают арифметические, текстовые, логические и прочие формулы. В данных методических указаниях будут рассматриваться только арифметические формулы. Правила составления формул других типов и знаки, соединяющие их операнды, приведены в [3, 4, 7].

Ввод любой формулы обязательно начинают со знака "=" и заканчивают нажатием клавиши <Enter>. После этого текст формулы высвечивается в информационном поле строки формул, а рассчитанное значение – в той ячейке, которая была активна в этот момент. Такие формулы называются формулами ячейки.

Если в результате работы формулы рассчитывается несколько значений, перед её вводом следует выделить блок ячеек, в которых надо разместить результаты, набрать формулу по обычным правилам и закончить ввод комбинацией клавиш <Shift + Ctrl + Enter>. Формулы, введённые таким образом, называются формулами массива (см. также [3] и п. 3.5).

Арифметические действия в формулах обозначаются так же, как в языке Бейсик. Порядок выполнения действий такой же, как в арифметике.

В формулы можно вставлять как конкретные числа, с которыми проводятся расчёты, так и ссылки на ячейки, в которых они размещены. Адреса ячеек можно вводить непосредственно набивкой в английском регистре или с помощью (Л’) по нужной ячейке. Если в формуле используется блок ячеек, его адрес можно ввести с помощью (Л®) по нужному блоку.

При изменении данных в ячейках, на которые ссылается формула, происходит автоматический пересчёт её значения..

Excel имеет большую библиотеку встроенных функций, облегчающих обработку данных. Поиск нужной функции и вставку её в формулу удобно делать с помощью Мастера функций (см. п. 1.8.).

При проверке правильности формул, особенно, если они используют много данных, можно разместить на экране стрелки, которые соединяют их с влияющими ячейками или, наоборот, стрелки, которые будут показывать, какие формулы используют то или иное данное, то есть зависимые ячейки. Эта возможность предоставляется из команды Сервис® Зависимости или из панели инструментов Зависимости.

Для перехода в режим корректировки введённой ранее формулы делают (Л”) на ячейке с неправильной формулой. Можно так же установить курсор на её ячейке и нажать клавишу <F2> или сделать (Л’) в строке формул на нужном фрагменте.

Для ускорения ввода однотипных формул, последовательно использующих данные из некоторого блока, можно использовать протяжку или копирование (см. п. п. 1.3, 1.12).

Задание.

Введите в ячейки А1:А3 какие-либо числа. В ячейку В1 введите формулу x²-2y²+3z², подставляя в явном виде вместо x, y и z те значения, которые указаны в А1, А2 и А3 соответственно. В ячейку В2 введите ту же формулу, ссылаясь на ячейки, в которых расположены нужные значения. Используйте при этом как прямую набивку адреса, так и (Л’) по ячейке. Измените числа, введённые в А1:А3. Измените коэффициент при y на –4. Проследите, как отражаются Ваши действия в строке формул и на рабочем поле листа.

1.8. Работа с Мастером функций

При первом обращении к Мастеру функций во время набора формулы эту программу можно вызвать либо командой Вставка ® Функция…, либо кнопкой с надписью f_x на стандартной панели инструментов.

Если формула начинается с функции, знак "=" набирать необязательно, Мастер вставит его сам.

Для второй и последующих функций в той же формуле Мастер вызывают через адресное поле в строке формул. Этим способом можно пользоваться сразу после ввода символа "=", с которого начинается формула (см. ниже).

Работа Мастера разбита на два шага.

На первом в правой части окна выделяется нужная функция. Для того чтобы облегчить её поиск, в левой части в поле Категория можно выделить нужный тип функций. По умолчанию всегда устанавливается тип "10 недавно использовавшихся". Пояснения по смыслу выбранной функции размещаются в нижней части окна и, если нужно, вызываются через справку. После того, как функция найдена, нажимают <ОК>.

На втором шаге Мастер выдаёт окно с полями для ввода аргументов. В них можно вводить константы, ссылки на ячейки или блоки, арифметические или логические выражения. Способы ввода аргументов те же, что и при наборе формулы. Для удобства ввода можно с помощью (Л®) переместить окно аргументов в другую часть экрана так, чтобы оно не загораживало ячейки с нужными данными. Справа от каждого поля выдаётся значение набранного аргумента, под полями всех аргументов – значение функции, внизу окна – значение всей формулы в целом.

Если функция заканчивает формулу, можно нажать <ОК> или клавишу <Enter>, в противном случае следует сделать (Л’) в тексте формулы и продолжать её набор.

Если в аргумент одной функции входит другая функция, то она называется вложенной. Такую функцию можно вызвать только через адресное поле строки формул. По умолчанию в нём высвечивается последняя функция, с которой работал Excel. (Л’) по ней выдаёт сразу окно аргументов (второй шаг Мастера). Если нужно вставить другую функцию, (Л’) по кнопке в правой части адресного поля вызывает их список. В нём перечислены последние функции, с которыми работал Мастер. Если среди них нет нужной, заказывают строку Другие функции…, которая вызывает первое окно Мастера. Для того чтобы окончить работу с вложенной функцией и продолжать набор аргументов первой, следует сделать (Л’) по названию первой функции в информационном поле строки формул.

Мастер функций допускает использование до семи вложенных функций.

Задание.

Введите в ячейки А1:А10 и В5:В10 какие-либо числа. В ячейку С1 с помощью Мастера функций введите формулу

=СУММ(МАКС(А1:А10);МАКС(В5:В10);МИН(А1:А10);МИН(В5:В10))

1.9. Присваивание имён ячейкам и блокам

Excel содержит ряд средств для замены стандартных адресов на текстовые имена. Практика показывает, что это удобнее при ссылках на информацию из ячеек. Основное ограничение: текстовые имена не должны быть похожи на стандартные адреса ячеек. Например, нельзя использовать для ячейки В15 имя "х1".

Самый простой способ создания имён заключается в следующем:

1. Выделяют нужную ячейку или блок.

2. Делают (Л’) по адресному полю в строке формул. Стандартный адрес, размещённый в нём, выделяется.

3. Набирают удобное пользователю имя.

4. Нажимают клавишу <Enter> для того, чтобы подтвердить замену. Если вместо <Enter> сделать (Л’) в рабочем поле, то замена стандартного адреса отменяется.

Кроме этого способа можно использовать команды меню. Основные из них:

· Вставка®Имя®Присвоить… Эта команда может как присваивать, так и удалять имена. Для создания нового имени следует:

1. Выделить нужную ячейку или блок.

2. Заказать команду.

3. В диалоговом окне в поле Имя ввести текст имени. Если в рабочем поле листа рядом с выделенным блоком предварительно был введён какой-либо текст, то он автоматически вставляется в поле имени. Если нужно присвоить другое имя, оно вводится вручную.

4. Нажать кнопку <ОК> или клавишу <Enter>.

· Вставка®Имя®Создать… Эту команду используют, если ячейки и имена, удобные для их обозначения, расположены на рабочем листе в виде горизонтальной или вертикальной таблицы, и тексты имён уже введены. Для одновременного создания всех имён следует:

1. Выделить блок, состоящий из текстов имён и именуемых ячеек.

2. Заказать команду.

3. В диалоговом окне поставить флажок на параметре, который правильно указывает, где находятся тексты по отношению к именуемым ячейкам.

4. Нажать кнопку <ОК> или клавишу <Enter>.

Для удаления уже существующих имён надо:

1. Заказать команду Вставка®Имя®Присвоить…

2. В диалоговом окне пометить ненужные имена на удаление. Для этого следует сделать (Л’) и нажать кнопку <Удалить> на каждом из них.

3. Нажать кнопку <ОК> или клавишу <Enter>.

Задание.

Введите в ячейки А3:I3 какие-либо тексты, в ячейки под ними – числа. Присвойте через адресное поле ячейке А4 имя, совпадающее с текстом в А3, а ячейке В4 – не совпадающее с текстом в В3. Для ячеек С4:I4 создайте имена с помощью других команд. Проследите по адресному полю, как теперь обозначает Excel эти ячейки. В А5 введите формулу =А4+В4*С4, в В5 – ту же формулу, но используя новые имена ячеек (либо прямой набивкой, либо по (Л’)).

3. РАСЧЁТНЫЕ АЛГОРИТМЫ В EXCEL

Большинство основных вычислительных алгоритмов в Excel оформлены в виде стандартных функций и вызываются с помощью программы Мастер функций (см. п. 1.8). Самые популярные из них:

· ЕСЛИ – позволяет предусмотреть разные варианты заполнения ячейки;

· СУММ, ПРОИЗВЕД – соответственно суммирование и перемножение значений из одного или нескольких блоков;

· СУММПРОИЗВ – суммирование произведений соответствующих элементов двух или нескольких массивов;

· СРЗНАЧ, СРГЕОМ – расчёт соответственно среднего арифметического и геометрического по числам в заданных блоках;

· СЧЁТ – определение количества чисел в заданном блоке.

Стандартную библиотеку можно дополнять функциями, созданными пользователем самостоятельно (см. [3, 4]).

Более сложные алгоритмы оформлены в виде команд и заказываются через меню Сервис. Наиболее важные из них:

· Подбор параметра… – нахождение аргумента, при котором функция примет нужное значение;

· Поиск решения… – решение систем уравнений и задач оптимизации;

· Пакет анализа – содержит программы, необходимые при статистической обработке данных.

Если нужная для вычислений команда отсутствует в меню, её можно установить с помощью команды Сервис®Надстройки…

3.1. Расчёт таблицы значений функции от одного аргумента

При явном задании функции таблица состоит из двух главных столбцов (строк). Первый – аргументы, второй – значения функции. Если алгоритм расчёта функции сложный, может потребоваться несколько дополнительных столбцов (строк) для записи промежуточных результатов.

Если аргументы меняются с постоянным шагом, то их можно ввести с помощью протяжки (1.5) или формулы. Последний способ более удобен, если может потребоваться пересчёт таблицы в разных диапазонах аргументов. Рассмотрим его на примере.

Пример.

Найти графически координаты корней и максимумов функции Y=2cos(x+2)e^-0,5x

Составим план размещения информации:

Формулы, занесённые в ячейки А5:А25, будут нагляднее, если перед их набором присвоить константам в А3:С3 имена (см. п. 1.9) и вводить их с помощью (Л’), а в формуле функции использовать вместо адреса подпись диапазона аргументов – заголовок из ячейки А4: =2*COS(Х+2)*EXP(-0,5*Х) (см. п. 1.10).

Задание.

Отформатируйте полученную таблицу и постройте диаграмму типа Точечная по ней.

Таблица и график показывают, что при х > 9 функция практически равна нулю, первый корень лежит в диапазоне 2 < x < 3, первый экстремум – около х = 4. Введём в А3:В3 новые константы: 2 и 4 соответственно. Excel сразу же пересчитал шаг, таблицу и график на новый диапазон аргументов и теперь можно локализовать корень и экстремум уже с точностью 0,1. Диапазон для корня 2,7 < x < 2,8, координата экстремума – x = 3,5. При необходимости можно снова изменить константы, с помощью которых создаётся диапазон аргументов, и продолжить уточнение ответов.

Задание.

Найдите какой-нибудь корень и экстремум этой функции в отрицательной области аргументов.

3.2. Расчёт таблицы значений функции от двух аргументов

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

Рассмотрим пример. Составить таблицу и график функции z = y ² x - ln(x + y) для диапазонов 2 < x < 6 и1 < y < 5.

Будем менять х с шагом 0,4, у – с шагом 0,8. Составим план размещения информации:

При вводе формулы в ячейку С4 символы "$" расставлены с таким расчётом, что при протяжке вбок разрешено менять адрес только у аргумента Y, аргумент Х для всей полученной строки берётся из одной и той же ячейки (в адресе Х закреплён столбец). При протяжке вниз – наоборот: в адресе аргумента Y закреплена строка, поэтому он не меняется, и в столбце оказываются значения функции, сосчитанные для одного и того же Y, но разных Х. Символ "$" можно вводить непосредственной набивкой в английском регистре или повторными нажатиями клавиши <F4> (см. п. 1.12).

Задание. Отформатируйте полученную таблицу (см. п. 1.13):

· объедините ячейки С2:Н2;

· объедините ячейки А4:А14, измените направление текста для Х;

· выделите цветом шрифта или заливкой заголовки таблицы (С2:Н3 и А4:В14);

· обведите толстой линией всю таблицу и отделите этой же линией заголовки с аргументами от значений функции;

· разделите тонкими линиями столбцы таблицы;

· разделите пунктирными линиями строки таблицы.

Перейдём к построению диаграммы. Представим на ней зависимость z(x) при разных y.

На первом шаге закажем тип Точечная с гладкими кривыми без маркеров. На втором шаге выделим диапазон данных В4:Н14 и параметр "Ряды в столбцах". На вкладке Ряд внесём для рядов данных имена: у=1, у=1,8 и т. д. На третьем шаге закажем легенду, основную сетку по осям и введём какие-нибудь заголовки. На четвёртом – размещение на том же листе.

Задание. Откорректируйте диаграмму:

· закажите более толстые линии для каждой зависимости;

· измените масштаб по оси Х;

· подберите тренд для одной из линий и выведите его уравнение на диаграмму.

Постройте вторую диаграмму, на которой будет представлена зависимость z(у) при разных х.

Примечание. Если перед вводом формулы в С4 присвоить диапазонам С3:Н3 и В4:В14 имена соответственно у и х, то формулы в С4 можно ввести в более понятном виде: =у^2*x-LN(x+y) (см. п. п. 1,9, 1.12).

3.3. Использование функции ЕСЛИ для анализа информации

Функция ЕСЛИ позволяет предусмотреть разные способы заполнения одной и той же ячейки. То, каким из них следует воспользоваться в данный момент, Excel определяет самостоятельно по тому, выполняется или нет при введённых данных указанное в функции условие. Рассмотрим действие этой функции на конкретных примерах.

Пример 1.

Поставщик ввёл оптовую скидку на цену для больших партий товара. Надо составить шаблон для расчёта стоимости любой партии товара.

Составим таблицу из констант, необходимых для расчёта. В ячейки А1:А4 ведём названия констант: "ОбъёмПартии", "ОптБарьер", "РознЦена", "ОптЦена". Присвоим ячейкам В1:В4 такие же имена (удобно пользоваться командой Вставка®Имя®Создать, см. п. 1.9). В ячейку С1 введём текст "СтоимПартииТовара".

Выделим ячейку С2 и вызовем через Мастер функций функцию ЕСЛИ. В окне аргументов введём для них следующие значения.

В аргумент "Логическое_выражение:" вводится условие, по которому Excel выбирает нужный вариант действий. Оно выглядит так (правила ввода аргументов изложены в п. 1.7):

ОбъёмПартии<=ОптБарьер

В строке второго аргумента ("Значение_если_истина:") указывается способ, по которому следует заполнить ячейку при правильном условии. Им может быть готовая константа, ссылка на ячейку, в которой содержится нужная информация или расчётная формула. Для нашего примера этот аргумент выглядит так:

ОбъёмПартии* РознЦена

И, наконец, в третьем аргументе ("Значение_если_ложь:") указывается, как заполнять ячейку, если условие не выполняется. Для нашего примера следует ввести

ОбъёмПартии* ОптЦена

Чтобы не набирать заново длинные названия переменных, можно ввести их с помощью (Л’) по соответствующим ячейкам в столбце "В". Расчётный шаблон готов. Чтобы проверить его, введите простые числа в ячейки В1:В4 и проверьте, правильно ли функция ЕСЛИ выбрала формулу для заполнения ячейки С2. Введите в В1 другой объём партии, при котором требуется использовать вторую формулу. Если в обоих случаях получены верные результаты, можно красиво отформатировать ячейки А1:С4 (см. п. 1.13) и пользоваться этим шаблоном, меняя только значения констант в В1:В4.

В качестве вариантов, которые выбирает функция ЕСЛИ, могут быть не только расчётные формулы, но и ссылки на ячейки, где находится нужная информация, текстовые строки и т. п.

Пример 2.

Заполним последний столбец в Задании п. 1.13.

Выделим ячейку С3 и вызовем через Мастер функций функцию ЕСЛИ. Для аргументов введём следующие значения.

Условие, по которому Excel выбирает нужный вариант действий, выглядит так: В3=МАКС($B$3:$B$15). В строку второго аргумента вводим символ "*" (без кавычек), в третий – пробел и нажмём после этого <ОК>.

Протянем полученную формулу по блоку С3:С15.

Посмотрите, какой вид приняла функция в разных ячейках этого блока.

Рассмотрим ЕСЛИ в той строке, в которой появилась "*". Значение функции y в этой строке максимально. Левая и правая части условия оказались одинаковыми, то есть первый аргумент – правильный. Поэтому для заполнения своей ячейки ЕСЛИ выбрала то, что указано во втором аргументе. Для значений функции у в других строках условие, введённое в функцию ЕСЛИ, оказывается неверным, поэтому она заполняет свои ячейки по варианту третьего аргумента. В нашем случае это пробел, который невидим на экране, поэтому ячейки кажутся пустыми.

Измените аргументы, введённые в А3:А15. "*" переместилась в другую строку, хотя формулы в С3:С15 не были изменены. После изменения данных каждая функция ЕСЛИ автоматически проверила свой первый аргумент наново и приняла новое управляющее решение, каким правилом пользоваться для заполнения своей ячейки.

Задание.

Введите в блок D3:D15 функцию ЕСЛИ, которая поставит знак "-" в строках с отрицательными значениями "у" и знак "+" – для положительных "у".

Другие варианты использования функции ЕСЛИ.

· Имеется два способа заполнения ячейки, но выбор нужного зависит от нескольких условий.

· Имеется больше двух способов для заполнения ячейки.

Пример 3.

Объём заказа k, который принимает фирма, должен лежать в диапазоне от a1 до a2 штук изделий. Цена одного изделия составляет d рублей. Составить шаблон для расчёта стоимости заказа, который будет выдавать предупреждение, если объём не попал в допустимые границы.

Введём в ячейку А1 общее название шаблона: Расчёт стоимости заказа, в А3:А6 названия переменных, а в В3:В6 их значения. Названия переменных, использованные при формулировке примера, неудобны для Excel – расчёта: а1, а2 совпадают со стандартными адресами других ячеек, поэтому можно воспользоваться длинными текстовыми именами, например, в А3 – ОбъёмЗаказа, в А4 – НеМеньше, в А5 – НеБольше, в А6 – Цена. В А2 введём текст СтоимостьЗаказа, в В2 – функцию ЕСЛИ со следующими аргументами:

Логическое_выражение: И(B3>=B4;B3<=B5)

Значение_если_истина: B3*B6

Значение_если_ложь: ТАКОЙ ЗАКАЗ НЕ ПРИНИМАЕМ

Если ячейкам В3:В6 предварительно присвоить имена, указанные в А3:А6 (см. п. 1.9), смысл аргументов станет более понятным.

Посмотрите, как действует шаблон, при разных значениях, введённых в В3. Отформатируйте его (см. п. 1.13):

· измените ширину столбца А так, чтобы все длинные тексты были видны на экране;

· выровняйте по левому краю значения переменных в столбце В;

· выделите заливкой или цветом шрифта ячейки А2:В2, А3:В3;

· отцентрируйте заголовок по ячейкам А1:В2.

Измените условие в первом аргументе:

ИЛИ(В3<=B4;B3>=B5)

В какой последовательности надо теперь перечислять способы заполнения ячейки В2?

Итог. Если выбор одного из двух вариантов заполнения ячейки зависит от нескольких условий, все они перечисляются через ";", заключаются в общие скобки и перед ними указывается нужный тип объединения:

· должны выполняться все одновременно – И;

· должно выполняться хотя бы одно из них – ИЛИ.

Пример 4.

Составить шаблон для расчёта подоходного налога по прогрессивной схеме.

Для простоты не будем учитывать налоговых льгот и будем считать, что существует только две границы, на которых меняется процентная ставка налога. Тогда расчётная формула выглядит следующим образом:

налог=

Здесь d – доход, n1, n2 – границы налоговых ставок, р1, р2, р3 – процентные ставки налогов (очередной процент действует только на ту часть дохода, которая попала в его диапазон).

Введём в ячейку А1 общий заголовок шаблона: Расчёт налога. В А2:А7 введём соответственно названия Доход, Граница1, Граница2, Проц1, Проц2, Проц3. В В2:В7 введём значения этих величин, В С2 – текст: Налог, в D2 – функцию ЕСЛИ, первый и второй аргументы которой такие:

Логическое_выражение: B2<=B3

Значение_если_истина: B5*B2

В третьем аргументе надо объяснить, как заполнять ячейку, если доход больше первой границы. Так как для этого случая существует ещё две возможности: d n2 и d > n2, в этот аргумент вставляют дополнительную функцию ЕСЛИ, которая должна выбрать нужный вариант. Правила вставки дополнительной функции в аргумент основной изложены в п. 1.8. Окончательно третий аргумент основной функции (Значение_если_ложь) выглядит так:

ЕСЛИ(B2<=B4;B5*B3+B6*(B2-B3);B5*B3+B6*(B4-B3)+B7*(B2-B4))

Итог. Если для заполнения ячейки имеется от трёх до семи вариантов, все они вводятся через вложенные функции ЕСЛИ, вставленные в аргумент Значение_если_ложь предыдущей функции. Если вариантов больше семи, все они разбиваются на мелкие группы и проверяются функциями ЕСЛИ в разных ячейках.

3.4. Оценка определённого интеграла

Интеграл – это площадь под графиком подынтегральной функции. Один из самых простых способов оценки этой площади – метод трапеций. По этому методу промежуток интегрирования [a;b] разбивают на несколько (n) равных частей длиной h=(b-a)/n. Подынтегральную функцию f(x) заменяют на хорды, проведённые через её значения на концах каждого из полученных отрезков. После этого реальную площадь считают приближённо равной сумме площадей прямоугольных трапеций, основания которых – ординаты функции в точках дробления промежутка интегрирования (x_i, i=0, 1, 2, …, n), а боковыми сторонами являются хорды, заменяющие функцию, и соответствующие отрезки на оси абсцисс. Математически площадь каждой трапеции выражается формулой

S_i= h(f(x_i-1)+f(x_i))/2

С учётом вышесказанного в Excel оценку интеграла можно выполнить следующим образом:

1. Составить таблицу значений подынтегральной функции (см. п. 3.1.).

2. Дополнить эту таблицу столбцом (строкой) с вычислением площадей элементарных трапеций.

3. Просуммировать эти площади.

Если помимо итогового интеграла интерес представляет и то, как он изменяется с увеличением промежутка интегрирования, в таблицу можно вставить ещё один столбец (строку), в которой будут расположены суммы площадей трапеций от начала до текущего аргумента.

Пример.

Составить таблицу значений интеграла как функции верхнего предела в диапазоне 0<v<5. Отразить эти данные на графике. Подобрать функцию тренда.

Постройте диаграмму типа Точечная для зависимости интеграла от верхнего предела. Подберите тренд для него. Выведите уравнение тренда на диаграмму. Отформатируйте таблицу и диаграмму.

3.5. Нахождение корня уравнения

Помимо способа, изложенного в п. 3.1, для решения этой задачи можно воспользоваться командой Сервис®Подбор параметра… Перед обращением к этой команде следует ввести в таблицу алгоритм расчёта функции (он может быть представлен одной или несколькими формулами) и ввести в ячейку её аргумента ориентировочное значение, с которого следует начать поиск корня.

Команда Сервис®Подбор параметра… вызывает на экран окно Подбор параметра, в котором следует указать:

· адрес ячейки, в которой находится конечное значение функции;

· то число, к которому её надо приравнять;

· ячейку аргумента.

В процессе выполнения команды начальное значение аргумента заменится на найденное, при котором функция будет равна нужному значению (не обязательно нулю). Точность подбора аргумента и максимально допустимое количество итераций при решении задачи задаются в диалоговом окне команды Сервис®Параметры… на вкладке Вычисления.

Задание.

Найдите двумя способами с точностью 0,001 корень уравнения e^-0,5x-2x+4=3.

3.6. Решение систем уравнений

Для решения систем нелинейных уравнений можно использовать команду Сервис®Поиск решения…, преобразовав задачу в оптимизационную (см. п. 3.7).

Систему линейных уравнений можно решить, запрограммировав вручную метод Гаусса, но проще сделать это матричным методом, опираясь на функции работы с массивами. В матричном виде линейная система любого порядка и её решение записываются следующим образом:

АХ=В; Х=А^-1В

Здесь А – матрица коэффициентов при неизвестных, В – столбец свободных членов системы, Х – неизвестные решения, А^-1 – обратная матрица коэффициентов системы.

В библиотеке Мастера функций Excel в категории Математические есть функции МУМНОЖ и МОБР, которые выполняют соответственно умножение и обращение матриц, необходимое для решения данной задачи. Так как результатом работы этих функций являются массивы чисел, их следует вводить как функции массива (см. п. п. 1.7, 1.8).

Рассмотрим систему четырёх линейных уравнений с четырьмя неизвестными. Для удобства работы перед вводом коэффициентов системы и расчётных формул можно провести форматирование таблицы (см. п. 1.13):

· объединить ячейки, в которых размещены заголовки;

· разместить эти заголовки по центру объединённых ячеек;

· изменить направление текста в заголовке А4:А7 на вертикальное;

· разрешить перенос по словам в заголовках G2:G3, H2:H3, I2:I3;

· разделить тонкими линиями столбцы полученной таблицы;

· обвести жирной рамкой всю таблицу в целом и блоки заголовков (A2:B7 и A2:I3).

Перед вводом формулы массива следует выделить ячейки, в которых надо разместить результаты. При решении системы это блок Н4:Н7, при проверке правильности найденного решения – I4:I7. Затем формула набирается обычным способом с помощью Мастера функций, но ввод заканчивается нажатием клавиши <Enter> или кнопки <ОК> при дополнительно утопленных клавишах <Ctrl+Shift>. При вводе формула массива автоматически заключается в фигурные скобки.

3.7. Решение задач оптимизации

Команда Сервис®Поиск решения… предоставляет пользователю следующие возможности:

· поиск безусловных экстремумов функции одного или нескольких аргументов;

· поиск экстремумов функции одного или нескольких аргументов при наличии ограничений на найденное решение;

· поиск аргументов, при которых функция примет нужное значение;

· выбор метода решения поставленной задачи;

· ввод ограничения на точность и время выполнения задачи.

Эти возможности реализуются с помощью параметров, собранных в основном окне Поиск решения и дополнительном Параметры поиска решения. Дополнительное окно вызывается кнопкой <Параметры> из основного. Кнопка <Справка> вызывает окно с разъяснением смысла каждого параметра и возможностей, которые предоставляются при его заказе.

Методы оптимизации можно так же применять для решения систем нелинейных уравнений. Для этого из уравнений системы

f₁(x₁,x₂…,x_n)=0; f₂(x₁,x₂…,x_n)=0; …; f_n(x₁,x₂…,x_n)=0;

составляют вспомогательную целевую функцию

S=f₁²+ f₂²+…+ f_n²

S – неотрицательная функция, её минимальное значение равно нулю и достигается только тогда, когда все слагаемые одновременно равны нулю. А это и есть решения исходной задачи.

Рассмотрим в качестве примера систему двух нелинейных уравнений

x²+y²=3; 2x+3y=1

Введём исходные данные задачи по представленному ниже плану.

Для удобства дальнейшей работы можно провести форматирование созданной таблицы, аналогичное п. 3.5.

Вызовем команду Сервис®Поиск решения… В окне Поиск решения установим следующие параметры:

· "Установить целевую ячейку:" А9

· "Равной:" минимальному значению

· "Изменяя ячейки:" А4:В4

· Нажмём кнопку <Параметры> и в дополнительном окне Параметры поиска решения проверим, что флажок Линейная модель не установлен. Закроем дополнительное окно кнопкой <ОК>

· Запустим команду кнопкой <Выполнить> основного окна.

Когда команда закончит работу, на экране автоматически появляется окно Результаты поиска решения. Пояснения к параметрам, представленным в нём, вызываются кнопкой <Справка>. Закажем, к примеру, параметры "Сохранить найденное решение" и "Тип отчёта: результаты". В этом случае начальные значения переменных в ячейках А4:В4 заменятся на найденные и в таблицу будет вставлен новый лист "Отчёт по результатам 1". Просмотрите отчёт. Проверьте, какое значение приняла вспомогательная целевая функция в А9 при найденных решениях. Если она существенно отличается от нуля, то решение найдено неверно.

Успешность поиска решения во многом зависит от выбора начального приближения переменных. В случае двух уравнений с двумя переменными можно не делать аналитического исследования функций системы, а составить таблицу вспомогательной целевой функции (см. п. 3.2) и выбрать по ней те комбинации аргументов, при которых функция принимает наименьшие значения.

Задание.

Составьте таблицу значений целевой функции S=(x²+y²-3)²+(2x+3y-1)² в диапазоне аргументов -3<x<3, -3<y<3. Выберите 4 – 5 точек с наименьшими значениями функции, проведите поиск решения из каждой из них. В результате должно быть получено только два разных решения: х₁=-1,268; у₁=1,179 и х₂=1, 576; у₂=-0,717. Графически уравнения системы представляются окружностью и прямой линией. Система такого типа не может иметь больше двух точек пересечения.

СОДЕРЖАНИЕ

Стр.

ВВЕДЕНИЕ……………………………………………………………………3

1. ОСНОВНЫЕ ПРАВИЛА РАБОТЫ В Excel …………………………..3

1.1. Список условных обозначений…………………………………….….3

1.2. Основные понятия Excel …………………………………………….4

1.3. Выделение блока ячеек………………………………………………...6

1.4. Ввод текстов……………………………………………………………6

1.5. Ввод чисел………………………………………………………………7

1.6. Ввод стандартных списков…………………………………………….7

1.7. Ввод формул……………………………………………………………8

1.8. Работа с Мастером функций……………………………………….….9

1.9. Присваивание имён ячейкам и блокам………………………………..10

1.10. Использование подписей данных……………………………………12

1.11. Правка информации……………………………………………….….12

1.12. Копирование и перемещение информации………………………….13

1.13. Команды форматирования…………………………………………...14

Дата добавления: 2015-08-28