МИНИСТЕРСТВО ОБРАЗОВАНИЯ РЕСПУБЛИКИ БЕЛАРУСЬ Белорусский национальный технический университет Кафедра «Технология бетона и строительные материалы» РЕШЕНИЕ ТЕХНОЛОГИЧЕСКИХ ЗАДАЧ В EXCEL 2007 Лабораторный практикум Минск БНТУ 2013 1 МИНИСТЕРСТВО ОБРАЗОВАНИЯ РЕСПУБЛИКИ БЕЛАРУСЬ Белорусский национальный технический университет Кафедра «Технология бетона и строительные материалы» РЕШЕНИЕ ТЕХНОЛОГИЧЕСКИХ ЗАДАЧ В EXCEL 2007 Лабораторный практикум по дисциплине «Информатика» для студентов специальности 1-70 01 01 «Производство строительных изделий и конструкций» Минск БНТУ 2013 2 УДК 004.67 (076.5) ББК 32.973-018.2я7 Р47 Составители : Ж. Л. Зеленковская, О. Б. Сенько Рецензенты : зам. декана по учебной работе строительного факультета Ю. В. Лях; канд. техн. наук, доцент кафедры «Гидротехническое и энергетическое строительство» В. И. Селезнев Решение технологических задач в Excel 2007 : лабораторный практикум по дисциплине «Информатика» для студентов специаль- ности 1-70 01 01 «Производство строительных изделий и конструк- ций» / сост. : Ж. Л. Зеленковская, О. Б. Сенько. – Минск : БНТУ, 2013. – 99 с. ISBN 978-985-550-013-2. Издание содержит задания к лабораторным работам и примеры их вы- полнения, предназначено для обучения основным технологическим опера- циям в электронных таблицах Excel 2007 для автоматизации работы с дан- ными, представленными в табличной форме. Студентам предлагается освоить программный инструментарий в про- цессе выполнения конкретных заданий и решения практических задач, что повышает эффективность обучения. УДК 004.67 (076.5) ББК 32.973-018.2я7 ISBN 978-985-550-013-2 © Белорусский национальный технический университет, 2013 Р47 3 СОДЕРЖАНИЕ Введение .................................................................................................. 4 Лабораторная работа № 1 АВТОМАТИЗАЦИЯ ПРОЦЕССА ВВОДА ДАННЫХ. ОСОБЕННОСТИ ФОРМАТИРОВАНИЯ ДАННЫХ В MS EXCEL ........................................................................................... 5 Лабораторная работа № 2 ВЫЧИСЛЕНИЯ В EXCEL. МАСТЕР ФУНКЦИЙ ........................... 15 Лабораторная работа № 3 СТАТИСТИЧЕСКИЕ И ЛОГИЧЕСКИЕ ФУНКЦИИ ....................... 27 Лабораторная работа № 4 СОЗДАНИЕ ДИАГРАММ ................................................................... 41 Лабораторная работа № 5 НАХОЖДЕНИЕ ФУНКЦИИ НА ОСНОВАНИИ ЭКСПЕРИМЕНТАЛЬНЫХ ДАННЫХ ............................................... 55 Лабораторная работа № 6 БАЗЫ ДАННЫХ ................................................................................... 66 Лабораторная работа № 7 РЕШЕНИЕ СИСТЕМ ЛИНЕЙНЫХ АЛГЕБРАИЧЕСКИХ УРАВНЕНИЙ ДЛЯ ИНЖЕНЕРНЫХ ЗАДАЧ В СРЕДЕ MS EXCEL ........................................................................... 77 Лабораторная работа № 8 ЧИСЛЕННЫЕ МЕТОДЫ РЕШЕНИЯ ТЕХНОЛОГИЧЕСКИХ ЗАДАЧ .......................................................... 89 Литература ............................................................................................ 98 4 ВВЕДЕНИЕ Широкое распространение современных информационных тех- нологий в настоящее время требует от инженера любого профиля высокой информационной культуры. Самостоятельная управленческая деятельность невозможна без развития у будущего специалиста способностей к анализу инфор- мации, ее обработке и принятию верных решений. В любой сфере деятельности найдется множество задач, исходные данные и результаты которых должны быть представлены в виде таблиц. Для автоматизации решения подобных задач предлагается ряд программных продуктов – табличных процессоров или элек- тронных таблиц. Лидирующее место в этом классе программ зани- мают электронные таблицы Microsoft Excel. Эффективно использовать электронные таблицы можно и в научно- технических задачах, например, для:  проведения однотипных расчетов над большими наборами данных;  обработки результатов экспериментов;  автоматизации всех видов вычислений;  поиска оптимального значения параметра;  составления различных документов;  решения задач различной степени сложности;  построения диаграмм и графиков по имеющимся данным. Цель предлагаемого лабораторного практикума – обучение основ- ным технологическим операциям в электронных таблицах MS Excel для автоматизации работы с данными, представленными в табличной форме. Развитые инструментальные средства программы позволяют проводить эффективный анализ имеющихся данных, реализовывать сложные экономические расчеты, использовать графические средства представления информации. 5 Лабораторная работа № 1 АВТОМАТИЗАЦИЯ ПРОЦЕССА ВВОДА ДАННЫХ. ОСОБЕННОСТИ ФОРМАТИРОВАНИЯ ДАННЫХ В MS EXCEL Цель работы: использование в работе режима автоматизации ввода данных приложения MS Excel. Задачи: – научиться создавать пользовательский список, арифметические и геометрические прогрессии; – приобрести практические навыки работы с форматированием ячеек. Методические указания Для автоматизации ввода данных применяется режим Автоза- полнение, который позволяет, используя маркер автозаполнения, выполнять ввод данных в ячейки без набора с клавиатуры. Это воз- можность MS Excel, позволяющая ускорить ввод повторяющихся элементов списка c помощью автоматического завершения ввода текста. Таким образом, можно работать с числами, датами, днями недели, месяцами и комбинированными данными. Маркер заполнения отображается по умолчанию, но его можно включать или отключать: 1. Нажать кнопку Microsoft Office , затем щелкнуть Пара- метры Excel. 2. В категории Дополнительно в разделе Параметры правки ус- тановить или снять флажок в подразделе Разрешить маркеры за- полнения и перетаскивание ячеек, чтобы отобразить или скрыть маркер заполнения. Заполнение столбца (строки) последовательностью чисел 1. Выделить первую ячейку в диапазоне, который необходимо заполнить, и ввести начальное значение последовательности. 2. Ввести значение в следующую ячейку, чтобы определить шаблон. 6 3. Выделить ячейки, содержащие начальные значения. 4. Перетащить маркер заполнения (черный квадратик в правом нижнем углу выделенной ячейки, который при наведении на него указателя мыши превращается в тонкий черный крестик), охватив диапазон, который необходимо заполнить. Чтобы ввести последовательность в возрастающем порядке, пе- ретащите маркер вниз или вправо. Чтобы ввести последователь- ность в убывающем порядке, перетащите маркер вверх или влево. Порядок построения числовых рядов с использованием Автоза- полнения: 1. Ввести первое число ряда в первую ячейку диапазона. 2. Нажать и удерживать нажатой клавишу Ctrl. 3. Установить указатель мыши на маркер заполнения ячейки с первым числом ряда. 4. Нажать левую кнопку мыши и протянуть указатель, выделяя нужный диапазон. Построение смешанных рядов и рядов из дат Смешанные, или комбинированные, ряды содержат текст и число. В таком ряду число должно быть обязательно, текст может нахо- диться как перед числом, так и после него. Смешанный ряд строит- ся по закону изменения числа. Начальные значения ряда задаются одной или двумя ячейками так же, как и для числовых рядов. Создание пользовательского списка путем ввода значений 1. Нажать кнопку Microsoft Office , затем щелкнуть Пара- метры Excel. 2. Выбрать раздел Основные, затем в группе Основные парамет- ры работы с Excel нажать кнопку Изменить списки. 3. В поле Списки выбрать значение Новый список, затем ввести нужные записи в поле Элементы списка, начиная с первого элемен- та. После ввода каждого элемента нажимать клавишу Ввод. 4. По завершении ввода списка нажать кнопку Добавить. Выбран- ные для списка элементы будут добавлены в поле Списки (рис. 1.1). 7 Рис. 1.1. Создание пользовательского списка Создание пользовательского списка на основе диапазона ячеек 1. В диапазон ячеек ввести значения, по которым должна выпол- няться сортировка или подстановка в нужном порядке сверху вниз. 2. Выделить только что введенный диапазон. 3. Нажать кнопку Microsoft Office , щелкнуть Параметры Excel, выбрать раздел Основные, затем в группе Основные парамет- ры работы с Excel установить флажок Изменить списки. 4. В поле Импорт списка из ячеек указать необходимый диапа- зон ячеек и нажать кнопку Импорт. Чтобы получить доступ к списку в рабочей таблице, необходимо ввести любое название элемента из списка и протащить маркер за- полнения на нужный диапазон. Построение арифметической и геометрической прогрессии Построить ряд с положительным шагом, равным 1,5, в диапазоне А1:G1 и предельным значением 10. 1. Ввести в ячейку А1 первое число ряда 0. 2. Выполнить команду Заполнить и Прогрессия в группе Редак- тирование вкладки Главная (рис. 1.2). 3. В диалоговом окне Прогрессия задать шаг, предельное значе- ние, тип прогрессии и способ расположения данных. Для построения геометрической прогрессии совершить аналогич- ные действия. Выбрать Тип – геометрическая в диалоговом окне Прогрессия. Геометрическая прогрессия умножает шаг на преды- дущее число. 8 Рис. 1.2. Построение арифметической прогрессии Форматирование ячеек 1. Выделить ячейку или диапазон. 2. Выполнить команду Формат  Формат ячеек в группе Ячей- ки вкладки Главная (рис. 1.3) или щелкнуть правой кнопкой мыши на выделенной области и выполнить эту же команду выпадающего меню. Рис. 1.3. Форматирование ячеек 3. На вкладке Число в списке Числовые форматы выбрать нуж- ный формат. 4. Установить в поле Число десятичных знаков необходимое значение. 5. Нажать кнопку ОK, чтобы вернуться на лист. 9 Вкладка Выравнивание в диалоговом окне Формат ячеек конт- ролирует расположение текста и чисел в ячейках. Эту вкладку мож- но также использовать для создания многострочных надписей, по- вторения ряда символов в одной или нескольких ячейках и для вер- тикальной ориентации текста. Вкладка Шрифт изменяет шрифт, начертание, размер, цвет, под- черкивание и эффекты текста в выделенных ячейках. Вкладка Граница создает рамки (обрамление) вокруг выделен- ного блока ячеек. Вкладка Заливка позволяет задать закраску ячейки (цвет и узор). Вкладка Защита управляет скрытием формул и блокировкой ячеек (запрет редактирования данных ячеек). Создание имени с помощью диалогового окна Создание имени 1. В группе Определенные имена вкладки Формулы выполнить команду Присвоить имя. 2. В диалоговом окне Создание имени ввести имя, которое нуж- но использовать в качестве ссылки, в поле Имя. Длина имени не мо- жет превышать 255 знаков. 3. Для задания области действия имени в поле со списком Область выбрать элемент Книга или имя листа в книге. 4. В поле Диапазон указать выделенные на листе ячейки (рис. 1.4). Рис. 1.4. Создание имени для диапазона ячеек 10 Вставка нижнего индекса 1. Выбрать ячейку, диапазон ячеек, текст или символы, которые нужно отформатировать. 2. На вкладке Главная в группе Шрифт нажать кнопку вызова диалогового окна Формат ячеек. 3. В группе Видоизменение установить флажок подстрочный. Условное форматирование ячеек Условное форматирование (вкладка Главная) обеспечивает нагляд- ность при исследовании и анализе данных, обнаружении критических проблем, схем и тенденций. Условный формат изменяет внешний вид диапазона ячеек на основе условия (или критерия) (рис. 1.5). Рис. 1.5. Условное форматирование ячеек Если в категории Цветовые шкалы выбрать раздел другие прави- ла… или во вкладке Условное форматирование выполнить команду Создать правило…, на экране отобразится окно Создание правила форматирования (рис. 1.6). 11 Рис. 1.6. Расширенное форматирование 1. Для форматирования цветом в разделе Выберите тип правила выбрать первое правило Форматировать все ячейки на основании их значений. 2. В разделе Измените описание правила выбрать стиль форма- та – двухцветная или трехцветная шкала. 3. Если в параметре Тип выбрать Минимальное и Максимальное значение, то параметр Значение не вводится. 4. При выборе пункта Число, Процент (от 0 до 100), Формула вво- дятся Минимальное и Максимальное значение параметра Значение. 5. По умолчанию поля в области Значение выбираются по выде- лению. Чтобы изменить диапазон ячеек надо в поле Значение нажать кнопку Свернуть, а затем выбрать необходимый диапазон. 6. Выбрать пункт Цвет для каждого значения, а затем выбрать цвет. 7. Для трехцветной шкалы кроме Минимального и Максималь- ного значений указать еще Среднее значение. Вставка и удаление листов Имя (или заголовок) листа отображается на его ярлыке в нижней части экрана. По умолчанию листам присваиваются имена «Лист1», «Лист2» и т. д. Для переименования листа необходимо щелкнуть правой кнопкой мыши на ярлыке листа и выполнить команду выпа- дающего меню Переименовать. 12 Для вставки нового листа необходимо щелкнуть на вкладке Вста- вить лист в нижней части экрана (рис. 1.7). Рис. 1.7. Вставка нового листа Задания к лабораторной работе Задание 1 1. Задать количество листов в рабочей книге и первый лист пере- именовать в Задание 1. 2. Создать следующие ряды, используя средство Автозаполнение:  рабочие дни недели на месяц;  названия месяцев учебного года;  годы вашей учебы в вузе;  1 квартал, 2 квартал, 3 квартал и 4 квартал;  кв. 4 2013, кв. 4 2014, кв. 4 2015, кв. 4 2016;  10 кг, 15 кг, …, 40 кг;  марки бетона по водонепроницаемости W2, W4, W6, W8, W10, W12, W14, W16;  классы бетона по прочности на сжатие В10, В12,5, …, В50;  марки бетона по прочности на сжатие М150, М200, М250, …, М600. 3. Используя текущую дату, создать ряды (до 10 значений), из- меняя:  день недели (01.09.2013, 02.09.2013, …);  месяц (01.09.2013, 01.10.2013, …);  год (01.09.2013, 01.09.2014, …). Задание 2 1. Второй лист рабочей книги переименовать в Задание 2. 2. Создать следующие пользовательские списки:  классов бетона по прочности на сжатие (В10; В12,5; В15; В17,5 и т. д. до В100); Вставить 13  марок цемента (М400, М500, М550, М600);  марок бетонной смеси по удобоукладываемости (Ж4, Ж3, Ж2, Ж1, П1, П2, П3, П4, П5);  дисциплин в вашем расписании;  фамилий студентов вашей подгруппы;  сырьевых материалов для ячеистого бетона (портландцемент, известь, песок, гипс, алюминий, вода, добавка);  марок бетона по морозостойкости (F100, F150, F200, F250, F400, F500, F600, F800, F1000). 3. Присвоить каждому диапазону ячеек задания имя, соответст- вующее его содержанию (например: Классы_бетона, Марки_цемен- та и т. д.). Задание 3 1. Третий лист рабочей книги переименовать в Прогрессия. 2. Вычислить сумму членов арифметической прогрессии Sn при указанных начальном a1 и конечном аn значениях ряда и шаге d (табл. 1.1). 3. Применить условное форматирование минимального и макси- мального значений (двухцветное или трехцветное) для столбца зна- чений Sn. Таблица 1.1 Варианты заданий Вариант 1 2 3 4 5 6 7 8 a1 1 12 % 1,2 –2 50 9 5 6 d 8 6 % 0,5 0,725 –3 3 9 –2 аn 25 111 8,4 5 10 12 27 –3 Sn 4. Вычислить n-й член и сумму n первых членов геометрической прогрессии (табл. 1.2):  набрать в объединенной ячейке текст: Геометрическая прогрес- сия. Вычисление bn и Sn. Использовать вставку нижнего индекса; 14  в ячейках А3, В3, С3 и D3 ввести обозначения разности прог- рессии, количества первых членов, n-го члена и суммы n первых членов геометрической прогрессии;  в ячейку С5 поместить формулу = С4+А4;  в ячейку D4 ввести формулу суммы;  применить условное форматирование для минимального и мак- симального значений (двухцветное или трехцветное) для столбца значений Sn;  для обрамления таблицы определить тип линии и активизиро- вать внешние и внутренние переключатели. Таблица 1.2 Варианты заданий Вариант 1 2 3 4 5 6 7 8 9 10 b1 1,5 2 0,015 1/2 2 3 2 1 3 2 g 0,4 1,4 0,5 0,5 3 4 2 1/2 2 2 n 4 8 7 7 12 5 6 5 8 13 15 Лабораторная работа № 2 ВЫЧИСЛЕНИЯ В EXCEL. МАСТЕР ФУНКЦИЙ Цель работы: использование Мастера функций для ввода и ре- дактирования формул. Задачи:  изучить возможности применения формул при вычислениях в приложении Microsoft Office Excel;  приобрести практические навыки работы с мастером функций. Методические указания Вычисление – это процесс расчета по формулам и последующе- го отображения значений результатов в ячейках, содержащих фор- мулы. Формулы – это уравнения, с помощью которых можно вы- полнять вычисления, возвращать данные, манипулировать содер- жимым других ячеек, проверять условия и т. д. Формула всегда на- чинается со знака равенства (=). Для завершения ввода формулы следует нажимать клавишу Ввод. Порядок вычислений определяется обычными математическими законами. Для изменения порядка выполнения операторов в форму- ле используются круглые скобки, количество закрывающих и от- крывающих скобок должно совпадать. Редактирование формулы выполняется как в ячейке (после двой- ного щелчка либо после нажатия кнопки F2), так и в строке формул. Для перехода в режим отображения не численных результатов, а формул необходимо выполнить установку: Сервис  Парамет- ры, закладка Вид, раздел Параметры окна, отметить поле Формулы (Excel 2003) или Формулы  Зависимости формул  Показать формулы (Excel 2007). Формулы могут выдавать значения ошибки. Ошибочные значе- ния – это результат формулы, которую Excel не может вычислить. В Excel определено семь ошибочных значений:  #ЗНАЧ! – ссылка на текстовое значение;  #ДЕЛ/0! – попытка деления на нуль; 16  #ИМЯ? – используется отсутствующее имя или строка симво- лов не заключена в двойные кавычки;  #Н/Д – нет данных для вычислений;  #ССЫЛКА! – отсутствует диапазон, на который ссылается формула;  #ЧИСЛО! – задан неправильный аргумент функции либо зна- чение формулы очень велико (мало);  #ПУСТО! – указано пересечение диапазонов, не имеющих об- щих ячеек. Типы адресации ячеек В Excel используют два типа адресации ячеек: абсолютный и от- носительный. Оба типа можно применить в одной ссылке и создать смешанную ссылку. При использовании в формулах относительной ссылки Excel запоминает расположение относительно текущей ячейки. Так, на- пример, при вводе формулы =А1+B1 в ячейку С1 Excel интерпре- тирует формулу как «прибавить содержимое ячейки, расположен- ной двумя столбцами левее, к содержимому ячейки одним столбцом левее». Если скопировать формулу =А1+B1 из ячейки С1 в С2, Excel также интерпретирует формулу как «прибавить содержимое ячейки, расположенной двумя столбцами левее, к содержимому ячейки одним столбцом левее». Таким образом, формула в ячейке С2 примет вид =А2+B2 (рис. 2.1). Рис. 2.1. Пример относительной ссылки Абсолютная ссылка задает абсолютные координаты ячейки. При копировании формулы абсолютная ссылка на ячейку не будет изме- няться и задается путем указания символа $ перед номером строки и столбца, например, $D$1 (рис. 2.2). 17 Рис. 2.2. Пример абсолютной ссылки Смешанная ссылка представляет собой комбинацию абсолют- ной и относительной ссылок, когда для строки и столбца использу- ются разные способы адресации, например, B$4 или $C2. Тогда при копировании один параметр адреса изменяется, а другой – нет. При выделении ссылки на ячейку в режиме редактирования фор- мулы можно изменять тип ссылки путем нажатия клавиши F4. Копирование формул Формулу можно распространить на часть строки или столбца. При этом происходит изменение относительных ссылок. Для распростра- нения формулы необходимо выполнить следующие действия: 1. Установить курсор в ячейку с формулой. 2. Подвести указатель мыши к правому нижнему маркеру заполнения, при этом изображение указате- ля изменится на черный крестик. 3. Нажать левую кнопку мыши и, удерживая ее, перемещать кур- сор до нужного места. Для завершения распространения формулы отпустить кнопку мыши. Пример 2.1 Решить уравнение y = b / (x – 0,3), где b = 0,5, а x изменяется от 1 до 4 с шагом 0,5. Необходимо заполнить исходными данными ячейки А2:А7 и В2. В ячейку С2 следует записать формулу и нажать клавишу F4 для установки абсолютной ссылки на ячейку В2. При копировании формулы ссылка на эту ячейку остается неизменной. Ссылки на зна- чения х являются относительными, т. е. меняются в каждой строке при копировании формулы (рис. 2.3). 18 Рис. 2.3. Решение уравнения Пример 2.2 а) Определить среднюю плотность каменного образца 0 непра- вильной формы, если масса образца m, а объем вытесненной воды V1. Исходные и расчетные данные заносятся в соответствующие ячейки (рис. 2.4). До определения объема образца в воде его парафиниро- вали. Масса парафинированного образца m1. 0 1 п m V V    , 1п п m mV   , где m – масса сухого образца, г; V1 – объем образца с парафином, равный объему воды, вытес- ненной образцом, см3; Vп – объем парафина, затраченного на покрытие образца, см3; m1 – масса образца, покрытого парафином, г. Рис. 2.4. Исходные и расчетные данные для одного образца б) Определить среднюю плотность каменного образца 0 для не- скольких вариантов исходных данных. Для этого выделить ячейки E3 и F3, нажать на маркер заполнения и скопировать (протянуть) формулы вниз на заданные значения (рис. 2.5). 19 Рис. 2.5. Копирование формул для нескольких вариантов исходных данных Функции Excel – это специальные, заранее созданные формулы для сложных вычислений, в которые пользователь должен ввести только аргументы. Функции состоят из двух частей: имени функции и одного или нескольких аргументов. Аргументы функции Excel задают значения или ячейки, используемые функцией, они записываются в круглых скобках сразу за названием функции и отделяются друг от друга символом точка с запятой (;), при вводе диапазона ячеек использу- ется двоеточие (:). Некоторые функции не имеют аргументов, но даже в этом случае функция должна содержать круглые скобки: =С5*ПИ(). Способы ввода функций 1. Можно вводить функцию на рабочем листе прямо с клавиатуры. 2. Выделив ячейку, можно вызвать окно диалога Мастер функ- ций следующими способами: – выбрать в ленте Формулы кнопку Вставить функцию "fx"; – с помощью кнопки Вставка функции (fx или =) в строке формул; – нажатием клавиш Shift+F3. В первом окне диалога Мастера функций (рис. 2.6) надо выбрать категорию, а затем из алфавитного списка – необходимую функцию. Для удобства работы функции в Excel разбиты по категориям: статистические, текстовые, математические, функции управления ба- зами данных и списками, функции даты и времени, DDE/Внешние 20 функции, инженерные функции, финансовые, информационные, ло- гические, функции просмотра и ссылок. Рис. 2.6. Диалоговое окно Мастер функций Второе окно диалога Мастера функций (рис. 2.7) содержит по одному полю для каждого аргумента выбранной функции. Если функция имеет переменное число аргументов, это окно диалога при вводе дополнительных аргументов расширяется. При нажатии кноп- ки ОK созданная функция появится в строке формул. Рис. 2.7. Диалоговое окно Аргументы функции 21 Математические функции Excel СУММ (число1,число2,…) суммирует множество чисел. Аргу- мент может быть числом, формулой, диапазоном или ссылкой на ячейку, содержащую или возвращающую числовое значение. Пример суммирования нескольких ячеек: =СУММ (В2;В10;А6:А8;-34). ОКРУГЛ (число;количество цифр) округляет число, задаваемое аргументом, до указанного количества десятичных разрядов. Зада- ние аргумента количество цифр равным 0 округляет до ближайшего целого числа. ЧЁТН (число) округляет число вверх до ближайшего четного целого числа. НЕЧЁТ (число) округляет число вверх до ближайшего нечетного целого числа. Отрицательные числа округляются не вверх, а вниз. ПРОИЗВЕД (число1;число2;...) перемножает все числа, задавае- мые ее аргументами. Игнорирует любые пустые ячейки, текстовые и логические значения. КОРЕНЬ (число) возвращает положительный квадратный корень из числа. Аргумент число должен быть положительным числом. Если число отрицательное, КОРЕНЬ возвращает ошибочное значение. LOG (число;основание) возвращает логарифм положительного числа по заданному основанию. Если аргумент основания не указан, то Excel примет его равным 10. EXP (число) вычисляет значение константы eх. ПИ() возвращает значение константы  с точностью до 14 деся- тичных знаков. ГРАДУСЫ (угол) используется для преобразования радиан в градусы. Здесь угол – число, представляющее собой угол, измерен- ный в радианах. РАДИАНЫ (угол) используется для преобразования градусов в радианы. Здесь угол – число, представляющее собой угол, изме- ренный в градусах. SIN (число) возвращает синус угла. Здесь число – угол в радианах. ASIN (число) возвращает арксинус числа. Угол определяется в ра- дианах в диапазоне от –/2 до /2. Аргумент число – это синус иско- мого угла, значение должно быть в диапазоне от –1 до 1. Чтобы вы- разить арксинус в градусах, следует умножить результат на 180/ПИ( ) или использовать функцию ГРАДУСЫ. 22 СТЕПЕНЬ (число;степень) возвращает результат возведения числа в степень. Число – основание, может быть любым веществен- ным числом. Степень – показатель степени, в которую возводится основание. СУММЕСЛИ (диапазон,критерий) суммирует ячейки, задан- ные указанным условием. Диапазон – диапазон суммируемых ячеек. Критерий – условие в форме числа, выражения или текста, опреде- ляющее суммируемые ячейки. Например, суммирование всех положительных чисел в диапа- зоне ячеек С5:С18 выглядит следующим образом: =СУММЕСЛИ(С5:С18;">0") (рис. 2.8). Рис. 2.8. Функция СУММЕСЛИ ABS (число) возвращает модуль (абсолютную величину) числа. Пример 2.3 Найти корни квадратного уравнения y = –1,3x2 + 2,8x + 0,24. 1. Занести коэффициенты уравнения в таблицу. 2. В ячейке В3 набрать формулу. Для вставки функции КОРЕНЬ в меню Формулы выполнить команду Вставить функцию (рис. 2.9). Рис. 2.9. Вызов функции 3. На экране появится диалог Мастера функций. В списке Кате- гория выбрать математические, название функции – КОРЕНЬ. В поле Число записать формулу b2 – 4ac, указывая адреса ячеек (рис. 2.10). 23 Рис. 2.10. Аргументы функции КОРЕНЬ 4. В результате получится формула для вычисления корней квад- ратного уравнения (рис. 2.11). Рис. 2.11. Вычисление корней Задания к лабораторной работе Задание 1 Рассчитать предел прочности при сжатии цилиндрического об- разца из природного камня: сж FR S  , МПа, где F – разрушающее усилие, кН; S – площадь приложения нагрузки, мм. 1 МПа = 1 Н/мм2. Исходные данные представлены в табл. 2.1. 24 Таблица 2.1 Исходные данные по вариантам Вариант 1 2 3 4 5 6 7 8 9 d, мм 100 101 102 99 100 101 102 99 100 h, мм 100 101 102 100 99 101 99 100 102 F, кН 780 785 789 787 759 800 790 795 793 Задание 2 Образец природного камня в виде цилиндра диаметром d и вы- сотой h испытывается на удар на лабораторном копре Педжа. Масса падающего груза («бабы») m = 2 кг. Разрушение камня произошло при n-м ударе «бабы». Рассчитать ударную прочность камня Rуд.    2 уд 2 9 1 2 3... 1 10 9,81 10 4 n mWR V d h                        , где m = 2 (const) – масса стального груза, кг; n – порядковый номер удара, после которого появилась первая трещина, т. е. высота падения груза, см; (n – 1) – порядковый номер удара, предшествующего разрушению; (1 + 2 + …) – суммарная высота падения груза, см; V – объем образца, м3; 9,81 – коэффициент перерасчета массы в силу тяжести груза. Исходные данные представлены в табл. 2.2. Таблица 2.2 Исходные данные по вариантам Вариант 1 2 3 4 5 6 7 8 9 d, мм 40 42 44 41 40 41 42 43 45 h, мм 65 63 64 62 61 66 67 68 62 n 9 8 7 12 11 10 14 15 6 25 Задание 3 Определить истираемость материала. Истираемость вычисляют с погрешностью до 0,1 г/см2 по формуле 1И m m S  , г/см2, где m и m1 – масса образца до и после истирания, г; S – площадь истирания, см2. Исходные данные представлены в табл. 2.3. Таблица 2.3 Исходные данные по вариантам Вариант 1 2 3 4 5 6 7 8 9 Материал образца Гра- нит Лабра- дорит Мра- мор Извест- няк Пес- чаник Квар- цит Кера- мо- гранит Цемент- но-пес- чаный раствор Бетон m, г 977,5 955,3 940,8 225,0 712,2 883,9 2457,5 720,0 2270,0 m1, г 965,3 920,5 865,8 207,5 684,7 880,2 2419,6 660,0 2228,0 S, см2 И, г/см2 Задание 4 Определить предел прочности древесины при скалывании вдоль волокон с влажностью в момент испытания W: maxск FR b l   , где maxF – максимальное усилие, Н; b – толщина образца, мм; l – длина скалывания, мм. Полученное значение пересчитывают на стандартную влажность 12 % по формуле  ск(12) ск( ) 1 ( 12)WR R W   , 26 где α – поправочный коэффициент на влажность, равный 0,03 для всех пород на 1 % влажности; W – влажность образца в момент испытания, %. Исходные данные представлены в табл. 2.4. Таблица 2.4 Исходные данные по вариантам Вариант 1 2 3 4 5 6 7 8 9 Порода древесины Дуб Липа Ольха Осина Сосна Береза Ель Бук Пихта b, мм 20,0 19,9 20,2 20,1 20,3 19,8 20,1 20,2 20,4 W, % 16 18 20 22 15 17 10 9 8 l, мм 30,1 30,2 30,0 30,1 30,2 30,5 30,4 30,0 30,3 F, Н 5482,5 4380,0 3958,5 2932,0 4218,3 4883,7 4485,3 7724,8 4495,4 Задание 5 Найти корни квадратного уравнения y = ax2 + bx + c и экстремум функции x0 = (x2 – x1) / 2 + x1. Вычисления произвести для всех вари- антов (табл. 2.5). Таблица 2.5 Исходные данные по вариантам Вариант 1 2 3 4 5 6 7 8 9 Решение уравнения y = ax2 + bx + c a 0,5 3,16 –1,5 –2,9 7,2 –5,7 4,72 1,5 3,45 b –2,4 –2,7 0,9 4,6 –4,1 0,69 –3,91 3,89 2,9 c 1,8 0,12 4,2 –0,5 –1,9 5,82 –1,64 –1,57 –2,2 x1 x2 x0 y0 ∑ y0 y0<1 ∑ x0 x0<0 ∑ x1, х2 x1,х2>0 27 Лабораторная работа № 3 СТАТИСТИЧЕСКИЕ И ЛОГИЧЕСКИЕ ФУНКЦИИ Цель работы: использование статистических и логических функ- ций для разветвленного вычисления. Задачи:  изучить возможности применения функций даты и времени при вычислениях в приложении Microsoft Office Excel;  приобрести практические навыки работы с вложенными, ста- тистическими и логическими функциями. Методические указания Вложение функций Часто необходимо в качестве аргументов функций указывать зна- чения, вычисленные по другим, вложенным, функциям. Пример 3.1 Необходимо вычислить величину (ln )x . В ячейку А1 следует ввести число х = 2,50. Находясь в ячейке В2, надо вызвать Мастера функций, в категории Математические выбрать функцию КОРЕНЬ. Для ввода вложенной функции открыть список Имя, выбрать функцию LN (рис. 3.1). Для ввода аргумента логарифма щелкнуть на ячейке А1. Результат формулы =КОРЕНЬ(LN(A1)). Рис. 3.1. Вложенные функции Список Имя 28 Функции даты и времени ЧИСТРАБДНИ (нач_дата;кон_дата;праздники) возвращает количество рабочих дней между датами «нач_дата» и «кон_дата». Праздники и выходные в это число не включаются. Функцию ЧИСТРАБДНИ можно использовать для вычисления заработной платы работника на основе количества дней, отработанных в ука- занный период. Например, следует вычислить все рабочие дни в летний период. Для проведения вычислений Microsoft Excel преоб- разует дату в числовой формат (рис. 3.2). Рис. 3.2. Функция ЧИСТРАБДНИ СЕГОДНЯ ( ) возвращает текущую дату в числовом формате (рис. 3.3). Числовой формат даты – это код даты и времени, с по- мощью которого в Microsoft Excel производятся вычисления над датами и промежутками времени. Используется для подсчета дней перед датой. В вычисляемой ячейке установить числовой формат. Рис. 3.3. Функция СЕГОДНЯ ДЕНЬНЕД (дата_в_числовом_формате;тип) возвращает день недели, соответствующий дате. Дата_в_числовом_формате – число, числовой =ЧИСТРАБДНИ(A3;A4;A5) =СЕГОДНЯ() = 17 29 соответствующее дате, день недели которой необходимо найти. Тип – число, определяющее тип возвращаемого значения, по умолчанию равно 2 и определяется как целое число в интервале от 1 (понедель- ник) до 7 (воскресенье). При вычислении дата преобразуется в чис- ловой формат. ТЕКСТ (значение;формат) преобразует значение в текст в за- данном числовом формате. Значение – числовое значение ячейки. Формат – числовой формат, представленный в виде текстовой стро- ки, заключенной в кавычки ("дддд" – полное название дня недели, "ддд" – сокращенное) (рис. 3.4). Формулы Результат Рис. 3.4. Использование функций ДЕНЬНЕД и ТЕКСТ Статистические функции МИН (число1;число2;...), МАКС (число1;число2;...) возвраща- ет наименьшее (наибольшее) значение в списке аргументов. Число1, число2, ... – от 1 до 30 чисел, среди которых требуется найти наименьшее (наибольшее). В качестве аргументов могут вы- ступать ссылки на ячейки и просто числа.  Пустые ячейки и логические значения игнорируются.  Если аргументы не содержат чисел, то функция возвращает 0. СРЗНАЧ (число1;число2;...) возвращает среднее (арифметиче- ское) своих аргументов. Число1, число2, ... – это от 1 до 30 аргументов, для которых вы- числяется среднее. Например, функция =СРЗНАЧ (A2:A6; 25) нахо- дит среднее арифметическое чисел в диапазоне A2:A6 и числа 25. СРЗНАЧЕСЛИ (диапазон;условие) возвращает среднее зна- чение (среднее арифметическое) всех ячеек в диапазоне, кото- рые удовлетворяют данному условию. Например, функция =СРЗНАЧЕСЛИ(A8:A12;">0") вычисляет среднее арифметическое всех положительных ячеек в диапазоне A8:A12 (рис. 3.5). 30 Рис. 3.5. Функция СРЗНАЧЕСЛИ СЧЁТ (значение1;значение2;...) подсчитывает количество чи- сел в списке аргументов. Функция СЧЁТ используется для получе- ния количества числовых ячеек в интервалах или массивах ячеек. Значение1, значение2, ... – это от 1 до 30 аргументов, которые могут содержать данные различных типов или ссылаться на них, но в подсчете участвуют только числа. СЧЁТЗ (значение1;значение2;...) подсчитывает количество зна- чений в списке аргументов. СЧИТАТЬ ПУСТОТЫ (значение1;значение2;...) подсчитыва- ет количество пустых ячеек в диапазоне (рис. 3.6). Рис. 3.6. Использование функций СЧЁТ, СЧЁТЗ, СЧИТАТЬ ПУСТОТЫ СЧЁТЕСЛИ (диапазон;критерий) подсчитывает количество не- пустых ячеек в диапазоне, удовлетворяющих заданному условию (рис. 3.7). 31 Рис. 3.7. Пример функции СЧЁТЕСЛИ: строка 1 – количество ячеек, содержимое которых больше 10; строка 2 – количество ячеек, содержимое которых равно –88; строка 3 – количество ячеек с окончанием на букву «а» Логические функции ЕСЛИ (лог_выражение;значение_если_истина;значение_если_ложь) выполняет логическую проверку, после чего в зависимости от ре- зультата проверки выбирается ход решения задачи. Пример 3.2 =ЕСЛИ(A2=100;СУММ(B5:B15);"") Если число в ячейке А2 равно 100, вычисляется сумма ячеек в диа- пазоне B5:B15. В противном случае возвращается пустой текст (""). Пример 3.3 =ЕСЛИ(СРЗНАЧ(А2:А5)>30; СУММ(В2:В5); 0) Если среднее арифметическое ячеек диапазона А2:А5 больше 30, вычисляется сумма ячеек в диапазоне B2:B5. В противном случае возвращается 0. Значение_ если_истина Значение_ если_ложь Логическое выражение 32 И (логическое_значение1;логическое_значение2;...) возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, и возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. ИЛИ (логическое_значение1;логическое_значение2;...) возвра- щает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА, и возвращает ЛОЖЬ, если все аргументы имеют значе- ние ЛОЖЬ. НЕ (логическое_значение) меняет логическое значение своего аргумента на противоположное. =НЕ(1+1=2) меняет значение ИСТИНА, которому равно логическое выражение, на противопо- ложное (ЛОЖЬ). Пример 3.4 Вычислить значение функции y = x2 + 4, если x < 6. =ЕСЛИ(А1<10;А1^2+4;"не соответствует условию") Изменим условие. Вычислить значение функции y = x2 + 4, если 0  x < 6. =ЕСЛИ(И(А1>0;А1<=10);А1^2+4;"не входит в интервал") Здесь в качестве аргумента логического выражения используется вложенная функция И, т. е. проверяется и первое условие х < 10, и второе 0  x. Порядок вызова функций: 1. Вызвать функцию ЕСЛИ. 2. Когда курсор находится в поле Логическое выражение, вы- звать функцию И, в которой указать в качестве логического значе- ния 1 условие х >= 0, в качестве логического значения 2  x < 6, и не закрывать окно функции И (рис. 3.8). Рис. 3.8. Поле Логическое выражение Логическое значение Строка формул 33 3. Затем, не закрывая окно Мастера функций, щелкнуть в строке формул на первом слове ЕСЛИ, возвращаясь таким образом в функцию ЕСЛИ, и дальше ввести Значения_если_истина и Значе- ния_если_ложь (рис. 3.9). Рис. 3.9. Результат вычисленной функции из примера 3.4 Пример 3.5 Вычислить значение функции , [ , ]; , 0; = 0, = 10, [15; 15] с шагом 5. иначе. x x x a b y a x a b x x b       Порядок выполнения: 1. Создать таблицу исходных данных. При обращении к данным a и b использовать абсолютную адресацию (F4 – изменение ссы- лок), к х – относительную. 2. Находясь в ячейке В4, вызвать функцию ЕСЛИ. 3. Установить курсор в поле Логическое выражение и из списка Имя (из последних 10 использованных функций или из Другие функции) вызвать функцию И. 4. В поле Логическое значение1 набрать условие x >= a, в поле Логическое значение2 набрать условие x <= b. Не закрывая функции И, щелкнуть в строке формул на слове ЕСЛИ для возврата в функцию ЕСЛИ (рис. 3.10). 34 Рис. 3.10. Использование функции И 5. В открывшемся окне функции ЕСЛИ в поле Значение_если_ис- тина ввести формулу для первого условия х  х. Находясь в поле Значение_если_ложь, вызвать новую функцию ЕСЛИ из списка Имя (рис. 3.11). Рис. 3.11. Использование вложенной функции ЕСЛИ 6. В поле Лог_выражение ввести второе условие. В поле Значе- ние_если_истина ввести значение функции для второго условия. В поле Значение_если_ложь ввести значение функции для третьего условия. Не закрывая окна, щелкнуть в строке формул на названии первой функции ЕСЛИ. Завершить формулу нажатием кнопки ОK (рис. 3.12). Имя ЕСЛИ в строке формул Новая функция ЕСЛИ 35 Рис. 3.12. Результат вычисленной функции из примера 3.5 7. Полученную формулу скопировать в остальные ячейки: =ЕСЛИ(И(A4>=$B$1;A4<=$B$2);A4*A4;ЕСЛИ(A4<0;$B$1;КОР ЕНЬ(A4*$B$2))) Задания к лабораторной работе Задание 1 Дубовая доска имеет размеры a  b  l и массу mW при влажно- сти W. Исходные данные представлены в табл. 3.1. Определить плотность древесины дуба при стандартной влажности (Wст = 12 %): 12 12 W WK   , %, где ρ12 – плотность при стандартной влажности древесины 12 %, г/см3; ρW – плотность при влажности древесины W, г/см3; 12 WK  коэффициент пересчета изменения плотности в зависимо- сти от влажности (табл. 3.2). 36 Таблица 3.1 Исходные данные по вариантам Вариант 1 2 3 4 5 6 7 8 9 a, мм 25 35 45 35 25 45 25 35 45 b, мм 150 150 100 100 200 200 250 250 150 l, мм 600 500 400 3000 4000 2000 3000 2000 3000 mW, г 1625 1890 1278 7350 13 720 12 770 12 220 11 210 13 960 W, % 21 20 19 18 17 16 10 9 8 Таблица 3.2 Коэффициент пересчета изменения плотности в зависимости от влажности W, % 8 9 10 16 17 18 19 20 21 12 WK 0,985 0,989 0,992 1,014 1,017 1,020 1,023 1,026 1,029 Задание 2 Определить коэффициент размягчения силикатного кирпича по формуле нассжразм сухсж RK R  . Определить водостойкость кирпича и сделать прогноз о возмож- ности применения данного материала. Материалы с Kразм  0,8 отно- сят к водостойким. Материалы с Kразм < 0,8 в местах, подверженных систематическому увлажнению, применять не разрешается. Исходные и расчетные данные свести в табл. 3.3. 37 Таблица 3.3 Водостойкость силикатного кирпича Вариант 1 2 3 4 5 Предел прочности при сжатии сухих образцов сухсж ,R МПа 16 23 32 28 21 Предел прочности при сжатии образ- цов, насыщенных водой, нассжR , МПа 15 21 31 22 18 Коэффициент размягчения Прогноз водостойкости кирпича Задание 3 Определить водопоглощение керамического и силикатного кир- пича по объему. Найти значение пористости. Сделать прогноз о мо- розоустойчивости кирпича. 0 B BB mV   ; 0 ист П 1 100      ; нас B П VK  . Если величина Kнас не превышает 0,85, то материал является мо- розостойким. При Kнас > 0,85 замерзающая вода не имеет пустых («буферных») ячеек и будет разрушать стенки пор материала. Ис- ходные и расчетные данные свести в табл. 3.4. Таблица 3.4 Морозоустойчивость кирпича Показатель, ед. измерения Вариант Плотность воды В, кг/м3 1000 Плотность, кг/м3: керамического кирпича: – истинная ист – средняя 0 2700 1700 2700 1400 2700 1900 силикатного кирпича: – истинная ист – средняя 0 2600 2000 2600 1900 2600 1800 38 Окончание табл. 3.4 Показатель, ед. измерения Вариант Водопоглощение по массе Bm, %: – керамического кирпича – силикатного кирпича 10 6 12 7 14 8 Водопоглощение по объему BV, %: – керамического кирпича – силикатного кирпича Пористость П, %: – керамического кирпича – силикатного кирпича Коэффициент насыщения пор водой Kнас: – керамического кирпича – силикатного кирпича Прогноз морозостойкости кирпича: – керамического кирпича – силикатного кирпича Задание 4 На складе ведется учет остатков продукции с ограниченным сро- ком хранения (табл. 3.5 на листе Остатки). Информация о сроках хранения и стоимости единицы продукции представлена в табл. 3.6 на листе Стоимость. Если конечный срок реализации продукции на текущую дату истек, то на весь остаток определяется ущерб, равный стоимости нереализованной продукции. Таблица 3.5 Остатки продукции на складе Код продукта Дата производства Остаток на складе Конечный срок реализации Ущерб, тыс. руб. 1001 09.11.2012 50 24.11.2012 1023 17.11.2012 25 19.11.2012 3471 17.11.2012 45 24.11.2012 1378 02.11.2012 34 17.11.2012 1200 04.11.2012 30 06.11.2012 39 Таблица 3.6 Стоимость и срок хранения продукции Код продукта Стоимость единицы продукции, тыс. руб. Срок хранения, дн. 1001 12,5 5 1023 5,65 2 3471 20,3 4 1378 5,4 12 1200 13,2 7 Задание 5 Вычислить значение функции на интервале (табл. 3.7). Таблица 3.7 Исходные данные по вариантам Вари- ант Функция Значения аргументов и величин 1 3 2 sin2 , если [ , ]; 2 cos , если [ , ]. x x a b y x x x a b     a = –1, b = 1 x, y  [–5; 5] x = 0,5 y = 0,5 2 2 , если точка ( , ) IIквадранту; cos , если точка ( , ) IVквадранту; 0 иначе. x y x y х у f x e y х у     x, y  [–5; 5] x = 1 y = 1 3 2, если [ , ]; , если [ , ]. x a bxy a x a b     a = 0, b = 1 x = –1; 0,5; …; 2 4 22 22 22 1, если 1; 0, если 2; 1, если 2. yx z yx yx        x = 0; 0,5; ...; 2 y = 0; 0,5; ...; 2 40 Окончание табл. 3.7 Вари- ант Функция Значения аргументов и величин 5 2 2 2 2 2 2 1 , если 1; 1, если 4; 0 в остальных случаях. x y x y z x y         , 2,3x y  шаг по x и по y = 0,5 6 , если точка ( , ) I квадранту; 0 иначе. x y x y t    x = –1, 0, 1 y = –2, –1, …, 2 7 z = max2(x, y) + min(|x|, |y|, 0) x, y меняются от –3 до 3 с шагом 0,5 8 z = x, если x и y – четные, z = y, если x и y – нечетные, z = 0 в остальных случаях. x, y меняются от 1 до 6 с шагом 1 9 3 3 , если [ , ];sin 2 2 , если [ , ]. x a bxy x x a b     a = –1, b = 1 x = –3, –2, …, 3 10 sin( ) , если ; 0, если ; sin( ) , если . x x y y f x y y x y x     , [0,2 ]x y  шаг по x, y = 4  11 2 2 2 2 1 , если ; 1, если ; 1 , если . x y x y z x y y x x y         , [0,1]x y шаг по x, y = 0,2 12 2 2z x y  , если точка (x, y) принадлежит кру- гу радиусом 3 с центром в начале координат; z = 0 в противном случае x, y меняются от –4 до 4 с шагом 1 41 Лабораторная работа № 4 СОЗДАНИЕ ДИАГРАММ Цель работы: использование Мастера диаграмм для создания и редактирования графиков и поверхностей. Задачи:  изучить операции по созданию и добавлению диаграмм на основе введенных в таблицу данных;  освоить основные приемы редактирования и оформления диа- грамм. Методические указания В Excel имеются средства для создания диаграмм, с помощью которых можно в наглядной форме представить зависимости, отра- женные в числовых данных. Построение графического изображения производится на основе ряда данных. Так называют группу ячеек с данными в пределах от- дельной строки или столбца. Набор соответствующих друг другу значений из разных рядов называется категорией. Диаграмма связа- на с данными, на основе которых она создана, и обновляется авто- матически при их изменении. В зависимости от типа диаграммы данные отображаются на ней разными способами. Можно отметить несколько стандартных типов: 1. Гистограмма (столбчатая диаграмма). В этом типе диаграмм данные отображаются в виде вертикальных или горизонтальных столбцов. Высота (или длина) каждого столбца соответствует ото- бражаемому значению. 2. Круговая диаграмма. В этом типе диаграмм величины отобра- жаются секторами круга. На одной диаграмме может быть пред- ставлен только один ряд данных. 3. График показывает тенденции изменения данных за равные промежутки времени, при этом обе группы данных (х и у) отобра- жаются в виде графиков. 4. Точечная диаграмма отображает взаимосвязь между числовы- ми значениями в нескольких рядах и представляет две группы чисел в виде одного ряда точек. 42 5. Поверхность. Диаграмма, в которой ряды становятся линиями для некоторой поверхности в объеме. 6. Лепестковая диаграмма. Каждый ряд отображается как линия, соединяющая точки на радиусах. При графической обработке числовой информации с помощью табличного процессора следует указать область данных, по кото- рым будет строиться диаграмма, и определить последовательность выбора данных (по строкам или по столбцам) из указанного блока клеток. При выборе по столбцам Х-координаты берутся из крайнего левого столбца выделенного блока клеток, остальные столбцы со- держат Y-координаты диаграмм. По количеству столбцов определя- ется количество строящихся диаграмм. При выборе по строкам са- мая верхняя строка выделенного блока клеток является строкой Х- координат, остальные строки содержат Y-координаты диаграмм. Большинство диаграмм строятся в прямоугольной системе коор- динат. По горизонтальной оси Х откладываются значения незави- симой переменной (аргумента), а по вертикальной оси Y – значения зависимой переменной (функции). Порядок построения диаграммы 1. Протабулировать данные, если задана аналитическая зависи- мость, или упорядочить экспериментальные данные, которые будут использоваться для построения диаграммы. Для каждого столбца создать текстовый заголовок. Впоследствии он будет автоматически вставлен в легенду диаграммы. 2. Выделить ячейки, содержащие данные для построения диа- граммы, и на вкладке Вставка выбрать необходимый вид диаграм- мы (рис. 4.1). Рис. 4.1. Выбор типа диаграммы 43 3. Для изменения диапазона исходных данных или для измене- ния значений строк и столбцов можно выполнить на вкладке Кон- структор команду Выбрать данные (рис. 4.2).   Рис. 4.2. Изменение исходных данных 4. Оформление диаграммы включает в себя следующие парамет- ры, которые можно выбрать на вкладке Макет (рис. 4.3): – название диаграммы и надписи на осях; – отображение и маркировку осей; – отображение сетки линий, параллельных осям координат; – легенду (описание построенных графиков); – отображение надписей, соответствующих отдельным элемен- там данных на графике. Рис. 4.3. Параметры оформления диаграммы Пример 4.1 Построить график зависимости предела прочности древесины при растяжения и сжатии от плотности породы древесины. Исход- ные данные представлены в табл. 4.1. Поменять местами значения строк и столбцов 44 Таблица 4.1 Исходные данные Порода древесины Плотность Предел прочности при растяжении Предел прочности при сжатии Дуб 690 123 57,5 Бук 670 123 55,5 Береза 630 168 55 Ольха 520 101 44 Осина 495 125,5 42,5 Липа 495 121 45,5 1. Выделить два средних столбца и на вкладке Вставка выбрать Точечная диаграмма с гладкими кривыми (рис. 4.4). Рис. 4.4. Точечная диаграмма с гладкими кривыми 2. Для изменения начальных значений осей на вкладке Макет вы- брать Оси  Основная горизонтальная (вертикальная) ось  Допол- нительные параметры. Здесь же поменять названия диаграммы и осей (рис. 4.5). 45 Рис. 4.5. Изменение параметров осей 3. Для добавления графика на диаграмму на вкладке Конструк- тор выполнить команду Выбрать данные. Нажать кнопку Добавить и в окне Изменение ряда указать данные нового ряда (рис. 4.6). Рис. 4.6. Добавление нового ряда на диаграмму Построение графика зависимости функции двух переменных Графическое представление функции двух переменных – поверх- ность в трехмерном пространстве. 46 Рассмотрим пример построения поверхности z = x2 + y2 при x, y  [–1, 1]. 1. Протабулировать функцию, располагая значения х по горизон- тали (диапазон ячеек B1:L1), а у – по вертикали (диапазон ячеек A2:A12). Тогда на пересечении столбца со значением хi и строки со значением уi будет находиться значение функции zi . 2. В ячейку В2 ввести формулу =B$1^2+$A2^2. При наборе формулы необходимо зафиксировать знаком $ номер строки переменной, изменяющейся по горизонтали (т. е. х), и номер столбца переменной, изменяющейся по вертикали (т. е. у). 3. Выделить ячейку с формулой, установить указатель мыши на маркере заполнения ячейки и протащить его так, чтобы заполнить диапазон B2:L12. При копировании формулы из ячейки В2 в ячейки диапазона B2:L12 в них будет вычислено значение z при соответст- вующих значениях x и y (табл. 4.2 и рис. 4.7). Таблица 4.2 Таблица значений функции z = x2 + y2 х у –1 –0,8 –0,6 –0,4 –0,2 0 0,2 0,4 0,6 0,8 1 –1 2 1,64 1,36 1,16 1,04 1 1,04 1,16 1,36 1,64 2 –0,8 1,64 1,28 1 0,8 0,68 0,64 0,68 0,8 1 1,28 1,64 –0,6 1,36 1 0,72 0,52 0,4 0,36 0,4 0,52 0,72 1 1,36 –0,4 1,16 0,8 0,52 0,32 0,2 0,16 0,2 0,32 0,52 0,8 1,16 Рис. 4.7. Формулы вычисления функции z = x2 + y2 47 4. Для построения поверхности:  выделить значения вычисленной функции (диапазон B2:L12);  на вкладке Вставка выбрать Другие диаграммы  Поверх- ность;  на вкладке Макет нанести названия диаграммы и осей (рис. 4.8). Рис. 4.8. Поверхность z = x2 + y2 Задания к лабораторной работе Задание 1 1. Рассчитать предел прочности при сжатии цилиндрического образца из природного камня: сж , FR S  МПа, где F – разрушающее усилие, Н; S – площадь приложения нагрузки, мм. 1 МПа = 1 Н/мм2. Исходные данные представлены в табл. 4.3. 48 Таблица 4.3 Исходные данные по вариантам Вариант 1 2 3 4 5 6 7 8 9 d, мм 100 101 102 99 100 101 102 99 100 h, мм 100 101 102 100 99 101 99 100 102 F, кН 780 785 789 787 759 800 790 795 793 2. Построить график зависимости предела прочности при сжатии цилиндрического образца из природного камня от разрушающего усилия для девяти исходных образцов. Задание 2 1. Определить группу песка по крупности. Зерновой (гранулометрический) состав песка отражает содержа- ние в песке зерен разной крупности. Для определения состава пробу песка просеивают через набор стандартных сит. Частный остаток на каждом сите вычисляется по формуле 100,ii ma m   %, где mi – масса остатка на данном сите, г; m – масса просеиваемой навески, г. Полный остаток на каждом сите вычисляется по формуле Аi = а2,5 + а1,5 + … + а2,5, %. Модуль крупности песка вычисляется по формуле 2,5 1,25 0,63 0,315 0,16 К .100 A A A A A M     Результаты вычислений свести в табл. 4.4. 49 Таблица 4.4 Зерновой состав песка Размер ячейки сита d, мм Частные остатки песка на ситах Полные остатки на ситах Аi, % Требования ГОСТ 26633 по полным остаткам Аi, % Определение группы песка по крупности mi, г аi, %№1 №2 №3 2,5 58 75 53 0–20 1,25 178 186 172 5–45 0,63 178 198 181 20–70 0,315 443 447 459 35–90 0,16 118 66 112 90–100 <0,16 25 28 23 0–10 ∑mi Модуль крупности По величине модуля крупности и полному остатку на сите с от- верстием 0,63 мм определяют принадлежность испытанного песка к той или иной группе по крупности (табл. 4.5). Таблица 4.5 Группы песка по зерновому составу Группа песка Модуль крупности Полный остаток на сите с размером отверстий 0,63 мм, % Повышенной крупности свыше 3,0 до 3,5 свыше 65 до 75 Крупный свыше 2,5 до 3,0 свыше 45 до 65 Средний свыше 2,0 до 2,5 свыше 30 до 45 Мелкий свыше 1,5 до 2,0 свыше 10 до 30 Очень мелкий свыше 1,0 до 1,5 до 10 2. Отобразить графически результаты определения зернового со- става в виде кривой просеивания Аi(d). Добавить на диаграмму два графика: нижнюю и верхнюю границы по ГОСТ 26633 по полным остаткам Аi, %. 50 3. Построить круговую диаграмму частного остатка на каждом сите аi в процентах с подписями значений на каждом секторе. Задание 3 Построить график первой функции. Затем добавить на него гра- фик второй функции (табл. 4.6). Таблица 4.6 Функции для построения графиков Ва- ри- ант Функции Значение пара- метра Диапазон изменения аргументов функции Шаг аргумента функции 1 3( ) cos 2 xf x y  2 4 2 1 , 0; 1( ) sin2 , 0. 2 x x xg x xx x x         y = 1,3 2,5 3,5x   x = 0,25 2 5( ) 2 xy x x t   2 2 3sin cos , 0; ( ) 3 1 , 0. x x x g x x x       t = 2,9 2,0 12x   x = 2,0 3 3cos 2( ) x x x zf x e e   2 2 3 1 1 cos , 0; ( ) , 0. x x x x g x x x e        z = 0,8 0,5 3,25x   x = 0,25 51 Продолжение табл. 4.6 Ва- ри- ант Функции Значение пара- метра Диапазон изменения аргументов функции Шаг аргумента функции 4 2 3 3 log( ) x ty x x t   2 2 3sin cos , 0; ( ) 2 , 0. x x x g x x x       t = 1,4 1,5 4,5x   x = 0,5 5 2 2( ) sin xf x x p   2 3 0,2 1 , 0; ( ) 1 , 0. 2 1 x x x x g x x x e         р = 0,8 6 18x   x = 2,2 6 3 arctg( ) xy x x b   2 sin 2cos , 0; ( ) 1 , 0. x x x g x x x      b = 4,4 0,6 1,8x   x = 0,2 7  23sin)( xp xxf      3 2 2 3/5 1 , 3; 1 ( ) 2ln 1 , [ 3;0]; 1 , 0. x x x x g x x x x x           р = 1,6 6 18x   x = 3 52 Окончание табл. 4.6 Ва- ри- ант Функции Значение пара- метра Диапазон изменения аргументов функции Шаг аргумента функции 8 2 ( ) tg sin yf x p x   2 1 , 0; 1 ( ) 1 , [0;1]; 1 2 sin3 , 1. x x x xg x x x x x        р = 0,8 1,5 3,5x   x = 0,5 9 )sin(2 )( 2 xx bxf  2 3 2 2 1/3 1 , 1; 1 ( ) 2 , [ 1;2[; 2 , 2. x x x x g x x e x x x             b = 1,68 2 4x   x = 0,5 10 1,4( ) af x x  2 3 0,2 1 , 0; ( ) 1 , 0. 2 1 x x x x g x x x e         a = 0,26 2 2,5x   x = 0,5 53 Задание 4 Построить поверхность (табл. 4.7). Таблица 4.7 Функции для построения поверхности Ва- ри- ант Функция f (x, y) Диапазон изменения аргументов функции Шаг изменения аргументов hx, hy 1  2 2 2 2 cos ( , ) sin( ) 17,5 x ye x y f x y x y x y      [1, 6] 0,1 2  3 2 2 2 2 2 2 2 tg ( , ) cos 2 3,5 x yx y e x y f x y x y x y          [0, /2] /20 3    2 2 22 3 22 cos( , ) x y x ye x yf x y x x xy e     [/2, 3/2] /20 4  3 22 2 2 3 cos ( , ) sin ( ) 7,52 x ye x y f x y x y x y      [0, ] /20 5      2 3 2 2 32 2 sin cos 2 ( , ) cos x yx y e x y f x y x yx y    [1, 2] 0,05 6 3 2 2 2 2 ( , ) cos x x y f x y x y e        [–, ] /20 7     2 2 3 ( , ) cos 2 max , f x y x y x x xy         [1, 2] 0,2 54 Окончание табл. 4.7 Ва- ри- ант Функция f (x, y) Диапазон изменения аргументов функции Шаг изменения аргументов hx, hy 8   2 2 2 2 10 sin 2 ( , ) x y x y e x y x y f x y e      [1, 5] 0,5 9     2 2cos ( , ) min , 2,5 x ye x y f x y x xy    [1, 5] 0,5 10    2 2 2cos ( , ) max , x ye x y f x y x x y    [1, 5] 0, 5 55 Лабораторная работа № 5 НАХОЖДЕНИЕ ФУНКЦИИ НА ОСНОВАНИИ ЭКСПЕРИМЕНТАЛЬНЫХ ДАННЫХ Цель работы: использование аппроксимационных зависимостей при графическом анализе моделей. Задачи:  освоить основные методы нахождения функции на основании экспериментальных данных в приложении Microsoft Office Excel;  приобрести практические навыки работы с построением линии тренда на диаграмме. Методические указания При исследовании зависимости прочности бетона от активности цемента получают исходные данные, характеризующие исследуе- мый объект. Эти данные надо обработать так, чтобы в результате получить график и аналитическую зависимость. Метод наименьших квадратов Тип функции y = f(x) устанавливается из теоретических сообра- жений или на основании характера расположения на координатной плоскости точек, соответствующих экспериментальным значениям. Выбрав функцию y = f(x, a, b, с, …), остается подобрать входящие в нее параметры a, b, с, … так, чтобы она наилучшим образом описывала рассматриваемый процесс. Данная задача решается ме- тодом наименьших квадратов, заключающемся в том, чтобы сумма квадратов разности экспериментально полученного значения yi для точки хi и рассчитанного по подбираемым величинам коэффициен- тов a, b, с стремилась к минимальному значению. Для линейного уравнения y = ax + b коэффициенты рассчиты- ваются следующим образом:       1 2 1 n i i i n i i x x y y a x x          , (5.1) 56 где xi – i-e значение аргумента; x – среднее значение аргумента; yi – i-e значение функции; y – среднее значение функции; b y a x   . (5.2) Для экспоненциального уравнения bxy a e  коэффициенты рас- считываются следующим образом: ln y bxa e  ;       1 2 1 ln ln n i i i n i i x x y y b x x          , где ln yi – натуральный логарифм i-го аргумента; ln y – среднее значение натуральных логарифмов аргументов. Для анализа экспериментальных данных используют коэффи- циент корреляции         1 2 2 1 1 n i i i n n i i i i x x y y r x x y y              . (5.3) Уравнение считается приемлемым, если коэффициент корреля- ции более или равен 0,7. Метод регрессионного анализа (линии тренда) Excel имеет специальный аппарат для графического анализа мо- делей, в том числе построения аппроксимационных зависимостей (линий тренда P(x)) по данной таблице {xi, yi}, которые приближен- но отражают функциональную связь y = f(x). 57 Линии тренда обычно используют в задачах прогнозирования. Такие задачи решают с помощью методов регрессионного анализа, когда можно показать тенденцию изменения рядов данных, экстра- полировать их (т. е. продолжить линию тренда вперед или назад за пределы известных данных). Линиями тренда можно дополнить ряды данных, представлен- ные на линейчатых диаграммах, гистограммах, графиках, бирже- вых, точечных и пузырьковых диаграммах. Нельзя дополнить ли- ниями тренда ряды данных на объемных, лепестковых, круговых и кольцевых диаграммах. Excel позволяет выбрать один из пяти типов линии тренда:  Линейная аппроксимация – это прямая линия, наилучшим об- разом описывающая набор данных. Она применяется в самых про- стых случаях, когда точки данных расположены близко к прямой. Линейная аппроксимация хороша для величины, которая увеличи- вается или убывает с постоянной скоростью.  Логарифмическая линия тренда хорошо описывает величину, которая вначале быстро растет или убывает, а затем постепенно стабилизируется. Логарифмическая линия тренда может использо- ваться как для отрицательных, так и для положительных значений данных.  Экспоненциальная линия тренда – это кривая линия, которую следует использовать, если скорость изменения данных непрерывно возрастает. Однако для данных, которые содержат нулевые или от- рицательные значения, этот тип линии тренда не применяется.  Степенная линия тренда дает хорошие результаты, если за- висимость, которая содержится в данных, характеризуется постоян- ной скоростью роста. Если в данных есть нулевые или отрицатель- ные значения, использование степенной линии тренда невозможно.  Полиномиальная линия тренда используется для описания ве- личин, попеременно возрастающих и убывающих. Она полезна, на- пример, для анализа большого набора данных о нестабильной вели- чине. Степень полинома определяется количеством экстремумов (максимумов и минимумов) кривой. При выборе линии тренда можно проверить (по различным кри- териям), какой из типов лучше всего подходит в данной ситуации. Одним из критериев служит коэффициент детерминации R2, авто- 58 матическое вычисление которого встроено в диалоговое окно Линия тренда. Чем ближе коэффициент детерминации к единице, тем лучше тренд. Порядок нанесения линии тренда 1. На основании исходных данных построить точечную диаграм- му с помощью команды Вставка  Точечная  Точечная с марке- ром (рис. 5.1). Рис. 5.1. Построение точечной диаграммы 2. В контекстном меню выделенной диаграммы выполнить ко- манду Добавить линию тренда (рис. 5.2). Рис. 5.2. Добавление линии тренда на диаграмму 3. В открывшемся окне Формат линии тренда (рис. 5.3) устано- вить следующие параметры: Точечная с маркером 59  тип линии тренда;  показывать уравнение на диаграмме;  поместить на диаграмму величину достоверности аппрокси- мации (коэффициент детерминации R2). Рис. 5.3. Диалоговое окно Формат линии тренда 4. Построить различные типы линии тренда и выбрать ту, у ко- торой коэффициент детерминации R2 стремится к 1. В табл. 5.1 представлены различные типы линии тренда. В данном случае оп- тимальный тип линии тренда – Полиномиальная 6-й степени. 60 Таблица 5.1 Типы линии тренда Линейная Экспоненциальная Полиномиальная 2-й степени Полиномиальная 6-й степени 5. Для показа тенденции изменения рядов данных можно про- должить линию тренда вперед за пределы известных данных с по- мощью поля Прогноз в окне Формат линии тренда (рис. 5.4). 61 Рис. 5.4. Тенденция изменения рядов данных Пример 5.1 Необходимо исследовать влияние химической добавки на проч- ность бетона. Исходные данные представлены в табл. 5.2. В резуль- тате проведенного эксперимента получены следующие данные, где аргумент х – количество добавки, функция у – прочность бетона. Найти уравнение, описывающее данный процесс, двумя способами: методом наименьших квадратов и нанесением линии тренда. Пока- зать тенденцию изменения прочности бетона от количества химиче- ской добавки. Сделать выводы. Таблица 5.2 Исходные данные Количество добавки, % 0,00 0,10 0,15 0,20 0,25 0,30 0,35 0,40 Прочность бетона, МПа 38 37 35 36 34 33 32 31 I способ. Проанализировав данные, следует выбрать линейную зависимость и расчеты коэффициентов провести по формулам (5.1) и (5.2) мето- дом наименьших квадратов. Результаты расчетов свести в таблицу (рис. 5.5, 5.6). Прогнозирование изменения данных на 0,4 периода 62 Рис. 5.5. Нахождение коэффициентов в Excel Рис. 5.6. Отображение формул вычислений в Excel В результате получится линейное уравнение у = 38,40 – 17,84х, МПа. Для анализа экспериментальных данных следует определить коэф- фициент корреляции по формуле (5.3). Коэффициент корреляции по модулю равен 0,97 (рис. 5.7). Следовательно, полученное уравнение считается приемлемым. 63 =ЕСЛИ(ABS(A15)>=0,7;”Верно”;”Неверно”) Рис. 5.7. Нахождение коэффициента корреляции II способ. На основании исходных данных строится точечная диаграмма и на нее наносится линия тренда с уравнением и коэффициентом детерминации R2. В результате подбора следует выбирать опти- мальный вариант с максимальным коэффициентом детерминации R2 = 0,945 и линейным типом тренда (рис. 5.8). Рис. 5.8. Нахождение уравнения для экспериментальных значений Уравнения, полученные первым и вторым способом, совпадают. 64 Далее необходимо продлить линию тренда вперед на несколько значений (см. рис. 5.4) и проследить тенденцию изменения данных (рис. 5.9). Рис. 5.9. Тенденция изменения данных Вывод: введение исследованной добавки ухудшает качество бе- тона – его прочность. Задания к лабораторной работе Задание 1 В бетонную смесь вводили добавку ускорителя твердения и опре- деляли прочность бетона в возрасте одних суток (табл. 5.3). Таблица 5.3 Исходные данные Добавка в бетон, % 0,4 0,5 0,6 0,7 0,8 0,9 Прочность бетона, МПа 12,6 14,2 14,3 12,8 15,8 16,2 65 Получить аналитическую зависимость и график, предполагая ли- нейную зависимость у от х. Вычисления провести двумя способами: методом наименьших квадратов и регрессионным анализом. Продлить линию тренда назад и вперед на 0,3 периода. Сделать вывод. Задание 2 На предприятии внедряется ультразвуковой контроль прочности бетона. При испытании 10 серий образцов получены значения ско- рости ультразвука в м/с (х) и, после разрушения образцов, прочно- сти бетона в МПа (у) (табл. 5.4). Зависимость прочности бетона от скорости ультразвука носит экспоненциальный характер. Таблица 5.4 Исходные данные Скорость ультразвука, м/с 3740 3290 4120 4160 4500 3900 3520 4290 4415 4600 Прочность бетона, МПа 10,7 8,8 21,9 20,6 32,6 11,3 9,5 29,1 30,8 36,1 Получить аналитическую зависимость и график. Вычисления про- вести двумя способами: методом наименьших квадратов и регрес- сионным анализом. Продлить линию тренда назад и вперед на 0,4 периода. Сделать вывод. 66 Лабораторная работа № 6 БАЗЫ ДАННЫХ Цель работы: приобретение специальных технических умений конструирования и сортировки баз данных на уровне их свободного использования. Задачи:  изучить возможности применения сортировки и фильтрации данных по определенному условию;  приобрести практические навыки при разработке собственной базы данных. Методические указания Термин база данных можно применить к любой совокупности связанной информации, объединенной по определенному признаку. Основным назначением баз данных является быстрый поиск со- держащейся в них информации. При этом столбцы называются по- лями, а строки – записями. Столбцам присваиваются имена, кото- рые будут использоваться как имена полей записей. Информация в базах данных имеет постоянную структуру. Каж- дую строку можно рассматривать как единичную запись. Построе- ние списка следует начать с проектирования его структуры. Существуют ограничения, накладываемые на структуру базы данных:  первый ряд базы данных должен содержать неповторяющиеся имена полей и располагаться в одной строке;  в списке не должно быть пустых строк и столбцов;  для имен полей следует использовать шрифт, формат, рамку, отличные от тех, которые используются для данных в записях;  таблицу следует отделить от других данных рабочего листа пустым столбцом и пустой строкой;  информация по полям должна быть однородной, т. е. только цифры или только текст. Работа с любой базой данных заключается в поиске информации по определенным критериям, перегруппировке и обработке информации. 67 Рассмотрим на примере создание базы данных. Создание базы данных (списка) Пример 6.1 1. Спроектировать структуру списка согласно следующему за- данию. Определить предел прочности древесины при скалывании вдоль волокон с влажностью в момент испытания W: maxск FR b l   , где maxF – максимальное усилие, Н; b – толщина образца, мм; l – длина скалывания, мм. Полученное значение пересчитывается на стандартную влаж- ность 12 %:  ск(12) ск( ) 1 ( 12)WR R W    , где α – поправочный коэффициент на влажность, равный 0,03 для всех пород на 1% влажности; W – влажность образца в момент испытания, %. 2. На вкладке Вставка выполнить команду Таблица. В окне Создание таблицы указать диапазон будущего списка и установить флажок Таблица с заголовками (рис. 6.1). Рис. 6.1. Окно создания таблицы 68 3. Заполнить строки заголовков именами полей и выделить их жирным шрифтом (рис. 6.2). Рис. 6.2. Заголовки 4. При вводе данных желательно контролировать тип вводимой информации и сводить к минимуму ошибки ввода. В Excel выпол- нение подобных условий проверяется при помощи средства, кото- рое называется проверкой данных. Для этого необходимо выпол- нить следующие действия.  Выделить ячейки столбца, для которого устанавливается про- верка ввода данных.  На вкладке Данные выполнить команду Проверка данных.  На вкладке Параметры в области Условие проверки выбрать Тип данных: Любое значение (используется для отмены проверки ввода), Целое число, Действительное, Список, Дата, Время, Длина текста и Другой (формат, для которого можно задать собствен- ную формулу, например, "м" или "ж"). При выборе значения внизу окна появляются дополнительные поля для ввода условий или ог- раничений (например, минимального и максимального допустимых значений).  На вкладке Сообщение для ввода можно установить флажок Отображать подсказку, если ячейка является текущей и ввести сообщение, чтобы оно появлялось на экране при вводе данных, не соответствующих условию проверки (рис. 6.3). 69 Рис. 6.3. Проверка данных 5. Ниже заголовков внести в список записи данных. Для изме- нения размера таблицы необходимо нажать на маркер и протянуть его вправо и вниз для увеличения таблицы и соответственно влево и вверх – для ее уменьшения (рис. 6.4). Рис. 6.4. Созданная база данных Проверка данных Маркер изменения размера списка 70 Для преобразования базы данных в простую таблицу необходи- мо установить курсор внутри списка и на вкладке Конструктор выполнить команду Преобразовать в диапазон. Сортировка данных в списке Команда Сортировка позволяет переставить записи в другом по- рядке на основании значений одного или нескольких столбцов. За- писи сортируются по возрастанию/убыванию или в выбранном поль- зователем порядке (например, по дням недели). Порядок сортировки в списке: 1. Установить курсор в ячейку списка. 2. Выполнить команду Сортировка на вкладке Данные в группе Сортировка и фильтр (рис. 6.5). Рис. 6.5. Команда сортировки и фильтрации данных 3. В диалоговом окне Сортировка выбрать поле, по которому будет происходить сортировка; тип сортировки (по значению, цвет ячейки, цвет шрифта, значок ячейки) и порядок (по возрастанию, убыванию, настраиваемый) (рис. 6.6). Рис. 6.6. Диалоговое окно сортировки данных Выбрать первый, а затем второй столбцы сортировки Указать тип сортировки: значения или цвет Порядок по возрастанию или убыванию Добавление столбца сортировки Очистить фильтр 71 Автофильтр Отфильтровать список – значит показать только те записи, кото- рые удовлетворяют заданному критерию. Чтобы установить или убрать автофильтр, следует на вкладке Данные в группе Сортировка и фильтр выполнить команду Фильтр (см. рис. 6.5). После этого нажать кнопку со стрелкой возле назва- ния какого-либо поля, чтобы раскрыть список его элементов и вы- брать отображаемые значения или задать условие отбора (рис. 6.7). На экране появятся только те записи, которые отвечают заданному условию. В случае необходимости можно продолжить фильтрацию, нажимая кнопки со стрелками на других полях. Рис. 6.7. Список дополнительных фильтров Сводные таблицы Отчет сводной таблицы представляет собой интерактивный ме- тод быстрого суммирования больших объемов данных. Отчет свод- ной таблицы используется в случаях, когда требуется проанализи- ровать связанные итоги, особенно для сравнения нескольких фактов по каждому числу из списка обобщаемых чисел. 72 Пример 6.2 Создать сводную таблицу, в которой по двум датам испытаний, 08.04. и 18.06., будут суммироваться толщины образцов и будет найдено среднее значение по максимальному усилию. 1. Чтобы создать сводную таблицу, на вкладке Вставка в группе Таблицы необходимо выбрать пункт Сводная таблица. На экран бу- дет выведено диалоговое окно Создание сводной таблицы (рис. 6.8), в котором следует указать диапазоны исходной и сводной таблиц. Рис. 6.8. Создание сводной таблицы 2. Перетащить мышкой на созданный макет указанные поля (рис. 6.9). Рис. 6.9. Создание макета сводной таблицы Диапазон создаваемой сводной таблицы Диапазон исходной таблицы 73 3. В поле Дата испытаний установить фильтр по двум указан- ным датам (рис. 6.10). Рис. 6.10. Установка фильтра 4. Справа в окне Список полей сводной таблицы, щелкнув по по- лю Толщина образца: – выполнить в меню команду Параметры поля (рис. 6.11); Рис. 6.11. Вызов команды Параметры поля – в окне Параметры поля выбрать функцию Сумма; – в списке поля Толщина образца выполнить команду Перемес- тить в значения. 74 Для поля Максимальное усилие выбрать функцию Среднее и вы- полнить команду Переместить в значения (рис. 6.12). Рис. 6.12. Определение параметров поля Отчет полученной сводной таблицы представлен на рис. 6.13. Рис. 6.13. Сводная таблица Задания к лабораторной работе Сформировать базу данных «Теплопроводность и структурные ха- рактеристики строительных материалов», представленную в табл. 6.1, используя вычисление пористости по формуле 0П 1 100      . 75 Таблица 6.1 Теплопроводность и структурные характеристики строительных материалов Наименование материала Теплопро- водность  Плотность, кг/м3 Пористость П, % средняя 0 истинная  Сталь 58 7850 7850 по формуле Медь 387 8960 8960 Алюминий 210 2700 2700 Гранит 3,1 2590 2650 Тяжелый бетон 1,4 2300 2400 Керамзитобетон 0,42 1260 0 Ячеистый бетон 0,325 700 0 Кирпич силикатный 1,08 1985 2600 Кирпич керамический 0,85 1700 2650 Пеностекло 0,095 250 2500 Каждое задание выполнять на отдельном листе, предварительно скопировав туда базу данных. Задание 1 1. Набрать заголовки таблицы. 2. Создать из них список. 3. К столбцу Средняя плотность применить Проверку данных: вводимые данные должны быть более 250 и менее 9000. 4. Заполнить список данными. 5. Ячейки заголовков (поля списка) заполнить цветом. Задание 2 1. Отсортировать данные списка по наименованию материала по алфавиту. 2. Отсортировать данные списка по средней плотности по убы- ванию. 76 Задание 3 Используя команду Автофильтр, выбрать следующие данные из списка: – теплопроводность больше 10; – пористость выше среднего значения пористости; – среднюю плотность более 1000 и менее 2000. Задание 4 Создать сводную таблицу, в которой по истинной плотности   2650 будут суммироваться теплопроводности и будет найдено среднее значение по пористости. 77 Лабораторная работа № 7 РЕШЕНИЕ СИСТЕМ ЛИНЕЙНЫХ АЛГЕБРАИЧЕСКИХ УРАВНЕНИЙ ДЛЯ ИНЖЕНЕРНЫХ ЗАДАЧ В СРЕДЕ MS EXCEL Цель работы: изучение возможностей пакета MS Excel при реше- нии задач линейной алгебры. Задачи:  изучить возможности применения табличных формул при ра- боте с матрицами;  приобрести практические навыки при решении систем линей- ных алгебраических уравнений. Методические указания Средства MS Excel используются в линейной алгебре для опера- ций с матрицами и решения систем линейных уравнений. Работа с матрицами Матрица представляет собой таблицу с числовыми значениями, которая состоит из n строк и m столбцов. Формулы массива позволяют работать с блоками рабочего листа как с отдельными ячейками. При активизации любой ячейки из ин- тервала, содержащего формулу массива, в строке формул отобража- ется введенная формула, заключенная в фигурные скобки. Именно фигурные скобки являются признаком табличной формулы. При работе с матрицей выполняются следующие действия: – выделить диапазон ячеек, куда будут помещены результаты; – набрать формулу, которая будет отображаться в первой ячейке выделенного диапазона; – вместо клавиши Enter нажать комбинацию клавиш Ctrl + Shift + + Enter. К простейшим операциям с матрицами относятся сложение и вы- читание матриц, умножение и деление матрицы на число. 78 Пример 7.1 Для умножения (деления) матрицы размером 33 на число 10 (рис. 7.1) следует выполнить следующие действия: – выделить диапазон ячеек E1:G3 такого же размера, как и ис- ходная матрица; – набрать знак равно, выделить диапазон исходной матрицы А1:С3 и умножить (разделить) его на 10; – для завершения работы с матрицей нажать комбинацию кла- виш Ctrl + Shift + Enter. Рис. 7.1. Умножение матрицы на число Аналогичные действия производятся при сложении (вычитании) двух матриц одинакового размера: С = А + В (рис. 7.2). Рис. 7.2. Сложение двух матриц Электронная таблица Excel имеет ряд встроенных функций для работы с матрицами. ТРАНСП(матрица) – транспонирование матрицы. Преобразует вертикальный диапазон ячеек в горизонтальный (столбцы становят- ся строками). Функция находится в категории Ссылки и массивы. 79 I способ. Дана матрица А (34). Необходимо выбрать диапазон для транспонированной матрицы В размером 43. Вызвать Масте- ра функций и в категории Ссылки и массивы выбрать функцию ТРАНСП. В аргументах этой функции указать диапазон исходного массива А и нажать клавиши Ctrl + Shift + Enter (рис. 7.3). Рис. 7.3. Транспонирование матрицы с помощью функции ТРАНСП II способ. Для транспонирования матрицы необходимо выделить массив и скопировать его в буфер обмена. В любом месте указать одну свободную ячейку, затем в разделе Главная выполнить коман- ду Вставить  Транспонировать (рис. 7.4). В результате получа- ется транспонированная матрица. Рис. 7.4. Команда Транспонировать в главном меню 80 Следующие функции находятся в категории Математические. МОПРЕД(матрица) – вычисление определителя матрицы с рав- ным количеством строк и столбцов. Следует установить курсор в лю- бой ячейке и в Мастере функций выбрать функцию МОПРЕД. В ар- гументах этой функции указать диапазон исходного массива и для завершения нажать клавишу ОK (рис. 7.5). Рис. 7.5. Нахождение определителя матрицы МОБР(матрица) – вычисление обратной матрицы А–1 с равным количеством строк и столбцов. Необходимо выделить диапазон ячеек, равный диапазону исходной матрицы, и в Мастере функций выбрать функцию МОБР. В аргументах этой функции указать диапазон ис- ходного массива и для завершения нажать клавиши Ctrl + Shift + + Enter (рис. 7.6). Рис. 7.6. Нахождение обратной матрицы МУМНОЖ(матрица1;матрица2) – произведение матриц. При работе с функцией МУМНОЖ следует соблюдать следую- щие правила: Δ = –4143 81 – число столбцов в первой матрице должно равняться количест- ву строк во второй матрице; – результирующая матрица должна иметь то же число строк, что и первая матрица, и то же число столбцов, что и вторая матрица. Пример 7.2 Вычислить произведение матрицы А (42) на матрицу В (32). Количество строк матрицы С соответствует количеству строк матри- цы А – 4. Количество столбцов матрицы С соответствует количеству столбцов матрицы В – 3. Следовательно, размер результирующей матрицы – С (43). Порядок вычисления следующий: 1. Выделить диапазон для результирующей матрицы С (43). 2. Вызвать функцию МУМНОЖ и в аргументах поочередно ука- зать диапазоны массивов А и В. 3. Для завершения нажать клавиши Ctrl + Shift + Enter (рис. 7.7). Рис. 7.7. Нахождение произведения двух матриц Решение систем линейных алгебраических уравнений Метод обратной матрицы Систему линейных уравнений вида 11 1 12 2 1 1 21 1 22 2 2 2 1 1 2 2 ... ; ... ; ...............................................; ... n n n n n n nn n n a x a x a x b a x a x a x b a x a x a x b             (7.1) 82 принято называть системой n линейных алгебраических уравнений (СЛАУ) с n неизвестными. При этом произвольные числа aij (i = 1, 2, …, n; j = 1, 2, …, n) называются коэффициентами системы (коэф- фициентами при неизвестных), а числа bi (i = 1, 2, …, n) – свобод- ными членами. Решением СЛАУ называется совокупность чисел xi (i = 1, 2, …, n), при подстановке которых в систему каждое из ее уравнений обращается в тождество. Систему (7.1) можно записать в матричной форме: A  X = B, где A – матрица коэффициентов при неизвестных; X – вектор-столбец неизвестных; B – вектор-столбец свободных членов: 11 12 1 1 1 21 22 2 2 2 1 2 ... ... . ... ...... ... ... ... ... n n n nn n nn a a a x b a a a x b x ba a a                                Решение системы (7.1) записывается в следующем виде Х = А–1  В. При решении используются функции МУМНОЖ и МОБР. Пример 7.3 Рассмотрим задачу решения СЛАУ методом обратной матрицы на следующем примере: 1 2 3 1 2 3 1 2 3 8 2 8 24; 2 2 10 48; 2 2 8 18. x x x x x x x x x            В диапазон А3:С5 введем матрицу А, в диапазон D3:D5 – стол- бец свободных членов В. Для нахождения вектора неизвестных Х необходимо: 1. Выделить диапазон В7:В9 и набрать знак «=». 2. В Мастере функций выбрать функцию МУМНОЖ. 83 3. В окне Аргументы функции установить курсор в поле Массив1 и вызвать функцию МОБР (рис. 7.8). Рис. 7.8. Использование функций МУМНОЖ и МОБР 4. После внесения значений в Аргументы функции МОБР не на- до нажимать кнопку ОK, а следует щелкнуть мышью в поле формул по названию функции МУМНОЖ, тем самым возвращаясь в диало- говое окно функции МУМНОЖ. 5. В поле Массив2 указать диапазон столбца В и завершить вы- числение нажатием клавиш Ctrl + Shift + Enter (рис. 7.9). Рис. 7.9. Вычисление вектора неизвестных Вызываем функцию МОБР Указываем диапазон матрицы А Мастер функций Вектор свободных элементов Обратная матрица А–1 84 Метод Крамера Решение СЛАУ находится по формулам Крамера: 1 2 3 A AX A            , где A – главный определитель матрицы системы (7.1); Ai (i = 1, 2, …, n) – вспомогательные определители матриц Ai, которые получаются из матрицы A заменой i-го столбца на столбец свободных членов B. Линейная алгебраическая система не имеет решений, если A = 0. Пример 7.4 Рассмотрим решение СЛАУ из примера 7.3 методом Крамера. 1. Найти определитель матрицы А = МОПРЕД(A3:C5) и прове- рить, имеет ли система решение. 2. Поочередно заменить каждый столбец на вектор свободных элементов. Вспомогательные матрицы имеют следующий вид: 1 2 3 24 2 8 8 24 8 8 2 24 48 2 10 , 2 48 10 , 2 2 48 . 18 4 8 2 18 8 2 4 18 A A A                                           3. Найти определители вспомогательных матриц. Затем по фор- муле Крамера найти решение системы (рис. 7.10). Рис. 7.10. Решение СЛАУ методом Крамера 85 Задания к лабораторной работе Задание 1 В комбинат входят три завода, на которых в 1998 г. были выпу- щены железобетонные изделия пяти видов (табл. 7.1). Ассортимент комбината можно представить матрицей А (35). В табл. 7.1 также представлен выпуск изделий комбината за 1999 г. – матрица В (35). Ниже указаны цены на 1 м3 железобетонных изде- лий в тыс. руб. Эта информация образует вектор-столбец С (51). Таблица 7.1 Выпуск продукции на комбинате, тыс. м3 Завод- изготовитель Вид изделий Панели перекрытий Панели стеновые Колонны и балки Лестничные марши Комплект. изделия 1998 г. № 1 20 10 0 5 10 № 2 10 10 4 0 6 № 3 60 20 0 10 10 1999 г. № 1 25 8 0 5 7 № 2 12 12 6 0 10 № 3 62 21 0 10 11 Цены на 1 м3 железобетонных изделий, тыс. руб. 50 55 90 60 75 Определить общий объем годовой продукции трех заводов в де- нежном выражении. Задание 2 Выполнить следующие задания по вариантам из табл. 7.2. 1. Решить систему уравнений методом Крамера. 2. Решить систему уравнений с помощью обратной матрицы. При решении систем уравнений обязательно выполнить проверку. 3. Выполнить действия над матрицами, где А–1 – обратная мат- рица, АТ – транспонированная матрица. 86 Таблица 7.2 Индивидуальные задания по вариантам Вари- ант Задание 1 1. 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 4; 2 2 3 1; 3 2 2 1; 4 3 2 5. x x x x x x x x x x x x x x x x                   2. 7 5 2 18; 3; 2 2. x y z x y z x y z           3. Т 1 7 3 0 4 2 1 ( 2 ) 3 , где 1 1 0 , 1 0 1 . 2 0 3 3 2 1 B A B AB A B                      2 1. 1 2 3 4 2 3 4 1 3 4 1 2 3 5 3 7 3 1; 3 4 5; 2 3 4; 4 3 5 5. x x x x x x x x x x x x x                2. 2 3 1; 0; 2. x y z x z x y z         3. T 1 3 3 4 2 1 3( ) ( ) /10, где 4 1 0 , 5 0 1 . 2 5 3 3 2 1 A B A B A B                       3 1. 1 2 3 4 1 3 4 1 2 4 1 2 3 0; 2 2 1; 2 1; 3 2 0. x x x x x x x x x x x x x               2. 2 2 3; 2 0; 1. x y z x y z x y z          3. Т 1 7 3 0 4 2 1 ( ) 2 ( ), где 1 1 0 , 1 0 1 . 2 0 3 3 2 1 A A B B A B A B                       87 Продолжение табл. 7.2 Вари- ант Задание 4 1. 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 2 3 1; 3 2 4; 2 3 6; 2 3 4. x x x x x x x x x x x x x x x x                    2. 5 8 7; 2 3 1; 2 3 2 9. x y z x y z x y z           3. T 1 2 3 9 4 2 1 2( 2 ) (2 ) / , где 1 4 5 , 1 5 1 . 2 8 3 3 2 1 A B B A B A B                         5 1. 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 2 3 2 6; 2 3 8; 3 2 2 4; 2 3 2 8. x x x x x x x x x x x x x x x x                  2. 2 4; 3 5 3 1; 2 7 8. x y z x y z x y z          3. T 2 3 1 1 0 5 3 (2 ) / , где 4 5 2 , 0 1 3 . 2 0 7 3 2 1 A B A B A B                      6 1. 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 2 3 4 5; 2 2 3 1; 3 2 2 1; 4 3 2 5. x x x x x x x x x x x x x x x x                  2. 3 2 5; 2 3 1; 2 3 11. x y z x y z x y z          3. T 2 3 1 1 0 5 2 (2 ), где 4 5 2 , 0 1 3 . 2 0 7 3 2 1 A B B A A B                      7 1. 1 2 3 4 1 2 3 4 1 2 3 1 2 3 7 5 9 6 80; 8 5 6 35; 3 5 3 0; 6 3 7 5. x x x x x x x x x x x x x x                2. 5 9 9 70; 6 5 9 15; 5 3 3 8 10; 2 3 2 5. x y z x y z x y z t x y z t                  88 Окончание табл. 7.2 Вари- ант Задание 7 3. 2 1 T 7 2 0 2 0 5 (2 ) 10 , где 7 2 1 , 0 1 2 . 1 1 1 1 2 1 B A B A B                       8 1. 1 2 3 4 1 2 3 1 2 3 4 2 3 5 4 4; 2 3 11 8; 3 5 11 12 6; 9 11 2. x x x x x x x x x x x x x                  2. 4; 2 3 1; 2 3 11. x y x y z x y z         3. 1 T 6 3 1 1 0 5 (2 ), где 0 5 2 , 0 1 3 . 10 2 0 7 3 2 1 A B B A A B                       9 1. 1 2 3 4 1 2 3 1 2 3 1 2 3 4 9 7 8 16; 4 9 8; 5 3 5 2; 12 5 5 4 10. x x x x x x x x x x x x x x                2. 2 5 17; 2 2 3 3; 3 4 5 8. x z x y z x y z           3. 2 2 T 12 35 1 1 0 5 ( ) (20 ), где 14 5 2 , 0 1 3 . 2 0 7 0 2 1 A B B B A B                          10 1. 1 2 3 4 2 3 4 1 2 3 4 1 2 3 4 9 5 2 6; 7 2 6; 7 3 5 12 2; 4 5 3 10 8. x x x x x x x x x x x x x x x                     2. 3 2 5; 2 3 1; 2 3 11. x y z x y z x y z          3. 1 2 T 7 0 1 5 0 5 ( ), где 4 0 2 , 0 11 3 . 4 0 7 43 0 1 A B B A A B                    89 Лабораторная работа № 8 ЧИСЛЕННЫЕ МЕТОДЫ РЕШЕНИЯ ТЕХНОЛОГИЧЕСКИХ ЗАДАЧ Цель работы: приобретение навыков решения нелинейных урав- нений и систем средствами Microsoft Excel. Задачи:  научиться применять циклические ссылки для организации вы- числений;  приобрести практические навыки использования численных ме- тодов при решении уравнений. Методические указания Все численные методы решения уравнений представляют собой итерационные алгоритмы последовательного приближения к корню уравнения. В основе аналитического метода лежат теоремы анали- тического анализа. Теорема 1. Если непрерывная на отрезке [a, b] функция у = у(х) на концах указанного отрезка принимает значения разных знаков, т. е. у(а)у(b) < 0, то на интервале [a, b] она хотя бы один раз обра- щается в нуль. Теорема 2. Непрерывная монотонно возрастающая или монотон- но убывающая функция у = у(х) имеет и при том единственный нуль на отрезке [a, b] тогда и только тогда, когда на концах отрезка она принимает значения разных знаков. Аналитический метод отделения корней при решении нелиней- ного уравнения у(х) = 0 заключается в поиске отрезка [a, b], содер- жащего точку пересечения графика функции у(х) с осью Ох, при этом должны выполняться два условия: 1) у(а)  у(b) < 0; 2) y'(x) < 0 или y'(x) > 0 для x  [a, b]. Если задана допустимая погрешность , то задача отыскания при- ближенного решения с указанной точностью  сводится к нахожде- нию отрезка [a, b], содержащего только один корень уравнения и удовлетворяющего условию |b – a| < . 90 Рассмотрим наиболее распространенные методы уточнения корня. Метод половинного деления (бисекций) основан на последова- тельном делении отрезка [a, b] пополам точкой с = (a + b) / 2 (рис. 8.1). Далее рассматриваются два отрезка [a, с] и [с, b] и определяется, в котором из них находится корень уравнения. Если у(а)у(с) < 0, то в дальнейшем решении участвует отрезок [a, с] и, соответственно, переопределяются концы отрезка (a = a; b = с), иначе в решении участвует отрезок [с, b], при этом а = с; b = b. Корень считается найденным, когда длина отрезка станет меньше заданной погреш- ности, т. е. |b – a| < .   y(b) y(a) a bc ba x Рис. 8.1. Геометрическая интерпретация метода бисекций Пример 8.1 Найти корень уравнения 2 4 0x x   с точностью  = 0,001 на отрезке [–2; 1]. Последовательно заполнить ячейки с названиями столбцов в Excel и ввести формулы (рис. 8.2): в А2 – начало отрезка; в В2 – конец отрезка; в С2 – формулу для нахождения точки с; в D2 – формулу уравнения и скопировать ее в ячейки Е2:F2; в G2 – ото- бражение корня (точка с), если выполняется условие |b – a| <  – ло- гическая функция ЕСЛИ(abs(В2-А2)<$H$2, то С2, иначе – «нет»; в А3 – логическая функция для переопределения концов отрезка ЕСЛИ (у(а)у(с) < 0), то a = a, иначе а = с; и в В3 – логическая функция ЕСЛИ (у(а)у(с) < 0), то b = с, иначе b = b. Выделить диапазон ячеек A3:G3 и заполнить все нижестоящие ячейки до получения результата. 91 Рис. 8.2. Итерационная последовательность при поиске корня уравнения методом половинного деления Метод хорд. Найденный отрезок делится точкой с, которая на- ходится по формуле ( ) ( ) ( ) ( ) a y b b y ac y b y a     . (8.1) Геометрически с – это точка пересечения хорды, проходящей че- рез точки (a, у(а)) и (b, y(b)) с осью Ох (рис. 8.3). Корень будет считаться найденным, когда выполнится условие | ci+1 – ci | < . 92 y(b) y(a) a b c1 x c2   Рис. 8.3. Геометрическая интерпретация метода хорд Пример 8.2 При решении уравнения из примера 8.1 методом хорд необходимо внести следующие изменения (рис. 8.4): в ячейку С2 ввести фор- мулу (8.1) нахождения точки с; в ячейке G2 изменить условие на- хождения корня | ci+1 – ci | < . За приближенное значение корня принимается значение ci+1. Рис. 8.4. Поиск корня уравнения методом хорд Метод Ньютона (касательных) основан на построении вблизи приближенного значения касательной к функции f(x) и нахождении точки пересечения полученной линии с осью абсцисс. Тангенс угла наклона касательной равен значению производной в точке касания tg() = ( )f x (рис. 8.5). Координата точки пересечения вычисляется по итерационной формуле 1 ( ) ( ) tg( ) ( ) i i i i i i f x f xx x x f x      . 93 В качестве начального приближения к корню выбирается х0 = а или х0 = b, для которого выполняется условие 0 0( ) ( ) 0y x y x  .  y(b) y(a) a x0 = b x x2 y = y(x) = b x1 Рис. 8.5. Геометрическая интерпретация метода Ньютона Пример 8.3 Решение уравнения 2 4 0x x   с точностью  = 0,001 на от- резке [–2; 1] методом Ньютона представлено на рис. 8.6 с отобра- жением формул. Рис. 8.6. Поиск корня уравнения методом Ньютона Метод итераций. Формула метода итераций имеет вид  1 .k k kx x c y x    94 В случае, если известен отрезок [a, b], содержащий только один корень уравнения, за начальное приближение х0 можно взять сере- дину отрезка х0 = (a + b) / 2. Коэффициент с определяется следую- щим образом: [ , ] 1 . max ( ) , ( )a b c y a y b       Знак перед дробью берется обратным к знаку производной. Уточ- нение корня заканчивается при выполнении условия |xk+1 – xk | < . За приближенное значение корня принимается значение xk+1. Пример 8.4 Найти корень уравнения 2 4 0x x   с точностью  = 0,001 на отрезке [–2; 1], используя метод итераций. В ячейки С2 и D2 необ- ходимо ввести формулы вычисления производной функции в точ- ках а и b, в ячейке Е2 определить максимальную из них по модулю. Вычисление корня уравнения методом итераций представлено на рис. 8.7. Рис. 8.7. Вычисление корня уравнения методом итераций =ЕСЛИ(С2>0;-1/E2; 1/E2) =A8+$C$5*B8 =(A2+B2)/2 95 Подбор параметра Используя возможности Excel можно находить корни нелинейного уравнения вида f(x) = 0 в допустимой области определения пере- менной. При подборе параметра используется итерационный (цикли- ческий процесс). Для определения относительной погрешности вы- числений и изменения числа итераций необходимо выполнить сле- дующие действия. 1. Нажать кнопку Microsoft Office , внизу указать Парамет- ры Excel, а затем выбрать категорию Формулы. 2. В разделе Параметры вычислений установить флажок Вклю- чить итеративные вычисления. 3. Для установки максимального числа пересчетов ввести в поле Предельное число итераций – 100. Чем больше число итераций, тем больше времени потребуется для пересчета листа. 4. Установить относительную погрешность вычислений  = 0,0001. Чем меньше это число, тем точнее результат и тем больше времени требуется на пересчет листа. Последовательность операций нахождения корней: 1. Произвести табулирование функции в диапазон вероятного су- ществования корней. 2. По таблице зафиксировать ближайшие приближения к значе- ниям корней. 3. На вкладке Данные в группе Работа с данными выполнить команду Анализ "что-если", а затем в списке выбрать пункт Подбор параметра (рис. 8.8). Рис. 8.8. Выполнение команды Подбор параметра 96 4. В открывшемся окне Подбор параметра в поле Установить в ячейке ввести ссылку на ячейку, содержащую необходимую фор- мулу (В2), ввести нужное значение в поле Значение (в данном случае вводим 0, так как у(х) = 0). В поле Изменяя значение ячейки ввести ссылку на ячейку (А2), значение которой нужно подобрать (рис. 8.9). Рис. 8.9. Нахождение корня уравнения средством Подбор параметра Задания к лабораторной работе 1. Построить график функции. Определить отрезок, на котором уравнение имеет единственное решение. 2. Решить уравнение методами половинного деления, хорд, Нью- тона, простой итерации и подбором параметра. Вычисления произ- водить на разных листах. Уравнения по вариантам представлены в табл. 8.1. Корень уравнения 97 Таблица 8.1 Исходные данные Вариант Уравнение 1 4 3 23 4 12 5 0х х х    2 3 22 9 60 1 0х х х    3 4 3 22 8 8 1 0х х х    4 4 1 0х х   5 3 23 6 5 0х х х    6 3 20,1 0,4 1,2 0х х х    7 4 3 24 8 17 0х х х    8 3 23 6 1 0х х х    9 3 3 1 0х х   10 4 218 6 0х х   98 ЛИТЕРАТУРА 1. Бабицкий, В. В. Учебно-методическое пособие по подбору со- става бетона расчетно-графическим методом / В. В. Бабицкий, Я. Н. Ковалев, В. Д. Якимович. – Минск : БНТУ, 2003. – 43 с. 2. Бурдо, Е. Н. Электронная таблица MICROSOFT EXCEL / Е. Н. Бурдо. – Минск : БНТУ, 2006. – 128 с. 3. Додж, М. Эффективная работа с Excel 7.0 / М. Додж, К. Кината, К. Стинсон. – СПб. : Питер, 2000. – 971 с. 4. Могилат, Г. А. Информатика / Г. А. Могилат, Е. С. Калини- ченко. – Минск : БНТУ, 2005. – 62 с. 5. Романюк, Г. Э. Обработка данных средствами MICROSOFT EXCEL / Г. Э. Романюк. – Минск : БНТУ, 2005. – 36 с. 6. Технология монолитного и приобъектного бетонирования / Э. И. Батяновский [и др.]. – Минск : БНТУ, 2007. – 57 с. Учебное издание РЕШЕНИЕ ТЕХНОЛОГИЧЕСКИХ ЗАДАЧ В EXCEL 2007 Лабораторный практикум по дисциплине «Информатика» для студентов специальности 1-70 01 01 «Производство строительных изделий и конструкций» С о с т а в и т е л и : ЗЕЛЕНКОВСКАЯ Жанна Леонидовна СЕНЬКО Ольга Брониславовна Редактор В. О. Кутас Компьютерная верстка Н. А. Школьниковой Подписано в печать 24.05.2013. Формат 6084 1/16. Бумага офсетная. Ризография. Усл. печ. л. 5,75. Уч.-изд. л. 4,5. Тираж 100. Заказ 867. Издатель и полиграфическое исполнение: Белорусский национальный технический университет. ЛИ № 02330/0494349 от 16.03.2009. Пр. Независимости, 65. 220013, г. Минск.