Основы создания и использования списков (баз данных) в электронной таблице

6.1. Общие сведения, понятия и определения

Списки или базы данных в электронной таблице Excel представляют собой подобие информационно-справочных систем известных как системы управления базами данных (СУБД). СУБД - один из классов прог­раммных средств, предназначенных для создания, ведения и использова­ния баз данных, справочных, информационно-поисковых систем. Основными компонентами информационной системы являются: база данных, систе­ма управления базами данных, прикладная программа и интерфейс. База данных содержит интересующую пользователя информацию, а также описание структуры хранимых данных. СУБД выполняет типовые процедуры управления данными, осуществляет взаимодействие с прикладной програм­мой. Прикладная программа реализует требуемый алгоритм ведения диалога пользователя с информационной системой, ввода и контроля запросов, ор­ганизации информационного поиска, выборки и представления данных в ви­де справок и отчетов. Взаимодействие между прикладной программой и СУБД осуществляется с помощью специальных операторов или команд языка управления базой данных.

Возможны три модели баз данных: сетевые, иерархические и реляционные. Сетевые и иерархические СУБД по­лучили наибольшее распространение на больших- и мини-ЭВМ. На ПК ис­пользуется преимущественно реляционная модель данных. Сетевые СУБД ис­пользуют модель представления данных в виде произвольного графа. В ие­рархических СУБД данные представляются в виде древовидной структуры. Реляционная модель ориентирована на представление данных в виде табли­цы. В Excel реализована реляционная база данных.

Поле 1	Поле 2	….	Поле N	Ü строка заголовков
				Записи
Рис. 6.1. Структура базы данных

Таблица реляционной БД (рис.6.1) представляет собой двухмерный мас­сив и обладает следующими свойствами: каждый элемент таблицы - это один элемент данных, повторяющиеся группы отсутствуют; все столбцы (колонки) в таблице однородные. Это означает, что все элементы одного столбца имеют одинаковую природу, например: марка автомобиля или раз­мер заработной платы. Столбцам присвоены уникальные имена; в таблице нет одинаковых строк; в операциях с таблицей ее строки и столбцы могут просматриваться в лю­бом порядке и в любой последовательности безотносительно к их информа­ционному содержанию и смыслу.

Каждая строка таблицы – это запись, каждая колонка таблицы – поле записи. Размещение в одной строке таблицы определенных элементов данных означает установление между ними связи или отношения. Например, если база данных со­держит сведения о запасных частях к автомобилям, то в одной строке мо­гут быть помещены сведения о запасных частях к автомобилю конкретной марки. То есть, данные в одной строке связаны между собой тем, что принадлежат одной марке автомобиля. Строка таблицы с этими данными представляет собой один конкретный экземпляр отношения данного типа или его кортеж, а всю таблицу в целом называют отношением. Таким обра­зом, при описании реляционной модели данных отношением называют всю таблицу в целом как совокупность конкретных экземпляров отношения. Слова "отношения" и "реляционный" (от латинского relation - отношение) представляют собой синонимы.

Совокупность значений элементов данных, размещенных в одном столбце таблицы и определяющих некоторую характеристику или свойство объектов, описываемых строками таблицы, называют атрибутом отношения. Количество элементов данных в кортеже (количество столбцов в таблице) определяет степень отношения. Если таблица включает n столбцов, то она представляет собой отношение степени n. Количество кортежей в отноше­нии (число строк в таблице) определяют его мощность - m. Тогда общее количество элементов данных в отношении степени n будет равно n х m.

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

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

Создание баз данных

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

Для создания списка может использоваться любой диапазон ячеек. Тогда каждый столбец диапазона считается полем, которое может содержать строку длиной до 255 символов. Соответственно каждая строка диапазона будет считаться записью. Для создания и работы со списками Excel имеет специальные команды, функции и методы.

Щелкните правой клавишей мыши по ярлычку номера страницы и введи-

те название базы данных (по ее содержанию).

В первую строку диапазона, отведенного для создания списка, записываются имена полей. Имена полей должны быть, по возможности, простыми, краткими и описательными, при этом они не могут занимать более одной строки таблицы. В последующие строки записываются данные. Первая запись не должна ничем отделяться от строки заголовков, в списке не должно быть одинаковых записей, пустых строк и колонок. Пример базы данных приведен на рисунке 6.2.

Дата	Откуда	Вид	Количество	Объем	Цена	Стоимость
Сентябрь	Братск	Бумага
Сентябрь	Братск	Ватман
Сентябрь	Вологда	Цемент
Сентябрь	Тюмень	Клей столярный
Октябрь	Братск	Картон
Октябрь	Вологда	Плитка облицовочная
Рис.6.2. Пример базы данных в Excell

Для работы со списками используются команды Сортировка, Фильтр и Форма, которые находятся в пункте главного меню Данные.

Команда Форма используется для создания списка, добавления данных в список, просмотра списка и поиска данных по заданному критерию. При вводе команды открывается окно диалога (рис. 6.3). Оно позволяет просматривать базу данных, перемещаясь по ней с помощью команд Далее и Назад, добавлять, удалять и редактировать значения полей базы данных. Добавляемые записи помещаются в конец списка. При этом вычисляемые поля (Стоимость) недоступны для редактирования. Команда Критерий позволяет осуществлять поиск записей по заданному критерию:

- щелкните мышью по кнопке Критерий – открывается пустая форма;

- введите в нужное поле значение критерия.

Например, если требуется найти товары поступившие из Омска, тогда укажите в поле Откуда “Омск” – на экран будут выведена первая запись, удовлетворяющей условиям поиска. Просмотрите другие записи используя кнопки Далее и Назад.

Команда Сортировка позволяет отсортировать выделенный диапазон по значениям одного, двух или трех полей (рис. 6.4). Принцип сортировки аналогичен сортировке в Microsoft Word. Сортировку можно проводить по возрастанию или убыванию значения соответствующего поля. В случае сортировки базы данных столбцы будут фигурировать под названиями полей, а в случае сортировки простого списка – под названиями столбцов. В группе “Идентифицировать поля по” два переключателя. Если активизировать переключатель “подписями (первая строка диапазона)” то в списках будут указаны поля базы данных, а если активизировать переключатель “обозначениями столбца листа”, то в списки будут выведены заголовки столбцов таблицы. Кнопка “Параметры” позволяет указать дополнительные условия сортировки: учитывать ли регистр, сортировать по строки диапазона или столбцы диапазона.

Команда Фильтр. Команда Фильтр содержит опции Автофильтр, Отобразить все и Расширенный фильтр.

Вид	Количество	Объем
Бумага
Ватман
Цемент
Клей столярный
Картон
Рис. 6.5. База данных, режим Автофильтра

При выборе опции Автофильтр на каждом поле появляется кнопка раскрывающегося списка (Рис.6.5.). Если щелкнуть мышью по кнопке раскрывающегося списка, то открывается список параметров (Рис.6.6). Выбор требуемого параметра осуществляется щелчком мыши. Параметр Условие выводит на экран диалоговое окно Пользовательский автофильтр, которое позволяет объединить два параметра по логическому условию И или ИЛИ (Рис.6.7.). В левых раскрывающихся списках выбираются логические условия для выбора числовой или текстовой информации, в правых раскрывающихся списках выбираются из списка или вводятся значения параметров для отбора. При указании значений параметров допускается использование маски: вопросительный знак или звездочка. Вопросительный знак – заменяет один символ в текущей позиции. Звездочка заменяет все слово или его часть. После применения фильтра все записи, не удовлетворяющие заданным критериям, убираются с экрана. Для отображения всех записей необходимо применить параметр Все.

Расширенный фильтр предоставляет пользователю дополнительные возможности по выбору критериев и формированию результатов: список можно фильтровать на месте или скопировать результат в указанный диапазон; условия для выбора могут быть заданы в отдельном диапазоне рабочего листа; можно использовать при выборе только уникальные записи.

Блок критериев для расширенного фильтра содержит условия для поиска и выборки данных. Он может располагаться в любом месте электронной таблицы. Блок критериев состоит из двух или более строк. В первой строке задаются имена полей, а в последующих строках – значения критериев поиска. Критерием может быть текстовая или числовая константа, логическая функция или логическое выражение. Если критерий содержит несколько строк, то считается, что эти строки связаны функцией ИЛИ. Если строка критерия содержит несколько полей, то считается, что эти поля связаны функцией И. Примеры блоков критериев для расширенного фильтра приведены на рис. 6.8.

В блоке критериев E1:E4 (рис. 6.8) три наименования объединены по схеме ИЛИ. То есть при наличии на складе указанных товаров все они будут включены в выходной список. В блоке критериев G1:I2 три параметра объединены по схеме И. Это значит, что для выбора товара должны быть выполнены все три условия: в базе данных будет отыскиваться телевизор, поступивший на склад первого января 2000 года по цене меньше 200000 рублей. В блоке критериев G4:I7 три разных критерия объединены по схеме ИЛИ, то есть будут отыскиваться телевизор, все товары, поступившие первого января 2000 года и все товары по цене меньше 200000 рулей.

	E	F	G	H	I
	Наименование		Наименование	Дата_поступления	Цена
	Телевизоры		Телевизор	01.01.2000	<200000
	Холодильник
	Пылесос		Наименование	Дата_поступления	Цена
			Телевизор
				01.01.2000
					<200000
Рис. 6.8. Примеры блоков критериев расширенного фильтра

В поле критерия для текстовых данных могут использоваться шаблоны?, *. Символ “?” заменяет один знак в указанной позиции. Например, "ию?ь" совпадает с "июнь" и "июль". Символ * заменяет все слово или его часть. Например, "авто*" соответствует "автомобиль", "автокар" и т. д.

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

Для получения выборки с помощью расширенного фильтра необходимо:

- создать блок критериев;

- выделить базу данных (для выделения базы данных достаточно установить курсор в любую ячейку этой базы данных);

- ввести команду Данные, Фильтр, Расширенный фильтр;

- указать в окне диалога Расширенный фильтр (рис.6.9) исходный диапазон, диапазон условий;

- если предполагается получить выборку на месте, то следует нажать клавишу ОК. Если предполагается поместить выборку в другое место, тот следует активизировать флажок “Скопировать результат в другое место” и указать в строке ввода “Поместить результаты в диапазон” начальную ячейку выходного блока данных.

При обработке баз данных полезными являются функции обработки данных:

ДСРЗНАЧ() – среднее значение элементов базы данных, соответствующих заданному критерию;

БСЧЕТ() - количество записей в базе данных, удовлетворяющих заданному критерию;

ДМАКС() – максимальное значение записей, соответствующих критерию, заданному в поле;

ДМИН() - минимальное значение записей, соответствующих критерию, заданному в поле;

БДСУМ() – сумма значений записей, соответствующих критерию, заданному в поле и другие функции.

Все функции базы данных имеют одинаковый формат:

<Имя_функции>(база_данных, поле, критерий)

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

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

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

Например, для вычисления суммы количества бумаги, поступившей от поставщиков (рис.6.5) следует ввести в ячейку формулу:

БДСУММ(A10:G31;D10;C4:C5)

или

БДСУММ(ПокупныеИзделия;"Количество";C4:C5)

Здесь ПокупныеИзделия – имя базы данных, “Количество” – имя поля, С4:С5 – блок критериев.

Анализ данных

Для анализа данных можно использовать команды Итоги и Сводные таблицы меню Данные.

Команда Итоги позволяет получать сводные данные по числовым параметрам: сумму, минимум, максимум, среднее значение и другие статистические данные. Предварительно необходимо определить по какому параметру требуется группировать итоги и отсортировать данные по этому параметру. В примере на рис. 6.10, в качестве такого параметра выбран вид продукции. Затем необходимо выделить базу данных и ввести команду Данные, Итоги. На экране появится окно диалога Промежуточные итоги (рис. 6.10). Выберем в списке “При каждом изменении в:” параметр Вид, в списке “Операция” выберем вид операции Сумма. В списке “Добавить итоги по:” установим флажки для параметров Стоимость и Количество. Установим флажки “Заменить текущие итоги” и “Итоги под данными”.

Если флажок “Итоги под данными” не установлен, то итоговые данные

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

Флажок “Конец страницы между группами” позволяет разбить данные по видам и напечатать их на отдельных листах. При малом объеме данных это делать не целесообразно.

Кнопка Убрать все удаляет все итоги и переводит базу данных в исходное состояние

После установки всех параметров щелкните по кнопке ОК база данных преобразуется к виду, представленному на рис. 6.11. Под каждым видом Рис. 6.11. База данных после выполнения команды Итоги

 

продукции помещены промежуточные итоги, а внизу таблицы Общие итоги. Слева от таблицы появиляются кнопки для управления уровнем просмотра. При щелче по кнопке 2 на экране остаются только промежуточные и общие итоги, при щелчке по кнопке 1 – только общие итоги.

Сводные таблицы

Команда Сводные таблицы позволяет формировать новые таблицы на базе существующих таблиц в различных разрезах и проводить анализ полученных результатов. Создание сводных таблиц осуществляется с помощью Мастера сводных таблиц за четыре шага.

На первом шаге предлагается выбрать источник данных и вид отчета. В качестве источника данных может быть база данных Excel или внешняя база данных, например, база данных Microsoft Access, Visual FoxPro, dBase-Word.

На втором шаге предлагается указать диапазон, содержащий исходные данные.

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

 

Рассмотрим для примера фрагмент базы данных, приведенный на рис. 6.11. Поместим в зону Страница кнопку Дата, в зону Строка – кнопку Вид, в зону Столбец – кнопку Откуда и в зону Данные – кнопку Количество (рис. 6.13). При установке в зону Данные кнопки Количество по умолчанию предлагается выполнить суммирование значений данных по соответствующему полю и соответственно имя кнопки изменяется. Можно выбрать другой вид операции. Для этого щелкните дважды мышкой по кнопке Сумма по полю Количество. Для завершения операции щелкните по кнопке ОК – программа возвращается в окно шага 3 (рис.6.13). Для завершения формирования сводной таблицы щелкните по кнопке Готово. Результат приведен на Рис 6.14. В списке Все содержится список дат поступления товаров. Список Откуда содержит список источников поступления. Эти списки снабжены флажками, которые позволяют управлять выводом информации на экран. Например, открыв список Откуда, можно снять флажки у Вологды, Мурманска, Тюмени. Тогда на экране останутся данные только по Братску. В списке дата по умолчанию флажки установлены у всех элементов списка. Можно установить флажок только для одной, интересующей Вас даты, тогда на экране будет выведен список товаров, поступивших в указанную дату.

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

Пример 1.

Создайте новую книгу, назвав ее «Лабораторная работа №1».

Сохраните результат.

Пример 2.

Продолжите заполнение созданной книги, используя маркер автозаполнения для ячеек C3- D3; C11 - D11; C12 – D12. Измените формат представления данных в ячейках B5:D6; B10:D12 на денежный.

 

Сохраните результат.

Пример 3.

Скопируйте ниже диапазон ячеек A2:D15 методом перетаскивания, исправьте названия месяцев для второго квартала.

Сохраните книгу.

Пример 4.

Произведите расчеты в созданной таблице, используя формулы и автозаполнение. Объедините ячейки A1 – E1 c помощью команды меню Формат → Ячейки, выбрав вкладку Выравнивание. Измените цвет ячеек. Пересчитайте итоговые значения за квартал расходов, прихода и прибыли в доллары (используйте абсолютную адресацию). В ячейке F3 разрешите перенос по словам.

Сохраните результат

Пример 5

Перейдите на Лист 2. Вычислите сумму конечного ряда при n=5, должен получиться результат:

 

Сохраните результат.

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

Создайте новую книгу, назвав ее «Лабораторная работа №2».

Переименуйте Лист1 в Пример1.

Выполните указанные в примере действия.

Пример 1. Протабулировать функцию SIN(x) на интервале от –ПИ/2 до ПИ/2 с шагом 0,5.

Порядок работы:

- введем в ячейки A1, А3, А5, С1 (Рис. 1) текст Ячейка ввода, Начальное значение, Шаг табуляции, Ячейка ввода формулы;

	A	B	C
	Ячейка ввода		Ячейка ввода формулы
			SIN(a2)
	Начальное значение	-1,5708	-1
	-1,5708	-1,0708	-0,87758
	Шаг табуляции	-0,5708	-0,5403
	0,5	-0,0708	-0,07074
		0,429204	0,416147
		0,929204	0,801144
		1,429204	0,989992
		1,929204	0,936457
Рис. 1. Использование команды Таблица подстановки

- введем в ячейку ввода А2 произвольное число, например 0

- (это число не влияет на результат табулирования);

- ведем в ячейку А4 начальное значение аргумента х - -ПИ/2. Для ввода этого числа используем функцию ПИ();

- введем в ячейку А6 значение шага - 0,5;

- введем в ячейку С2 формулу SIN(A2). В качестве аргумента указывается адрес ячейки ввода;

- сгенерируем в столбце В, начиная с ячейки В3, ряд значений аргумента;

- выделим область В2:С10 и введем команду Данные, Таблица подстановки;

- в диалоговом окне Таблица подстановки (рис. 2.) введем в окно ввода “ Подставлять значения по строкам в...” номер Ячейки ввода А2 и щелкнем кнопку ОК. (Для ввода номера ячейки достаточно активизировать окно ввода щелчком мыши и щелкнуть по ячейке А2). Работа завершена.

Лист2 переименовать в Пример2.

Пример 2. Протабулировать функцию 2х+у² при х, изменяющимся от 0 до 1 с шагом 0,2, и у изменяющимся от 1до 4 с шагом 1.

Решение:

- обозначим ячейку А2 как ячейку ввода по строкам, а ячейку А4 как ячейку ввода по столбцам для этого внесем соответствующие записи в ячейки А1 и А3 (см. рис. 3);

- внесем в столбец В, начиная с ячейки В2 значения аргумента х;

- внесем в строку 1, начиная с ячейки С1 значения аргумента у;

- внесем в ячейку В1 (ячейка на пересечении первого столбца и первой

- строки будущей таблицы) формулу 2*х+у^2 или, с ссылками на номера ячеек, 2*A2+A4^2;

	A	B	C	D	E	F
	По строкам	2*х+у^2
	По столбцам	0,2	1,4	4,4	9,4	16,4
		0,4	1,8	4,8	9,8	16,8
		0,6	2,2	5,2	10,2	17,2
		0,8	2,6	5,6	10,6	17,6
Рис. 3. Табулирование функции двух переменных

- выделим область В1:F7 и введем команду Данные, Таблица подстановки;

- внесем в строку ввода Подставлять значения по столбцам в …: номер ячейки А4, а в строку ввода Подставлять значения по строкам в …: номер ячейки А2 и щелкнем кнопку ОК.

Переименовать Лист3 в Пример3. Выполнить табулирование функции в соответствии с вариантом.

Сохраните результат.

Лабораторная работа № 3

Пример 1.

Порядок работы:

1. В ячейки А5:А12 ввести любые числовые значения от 0 до 42.

2. В ячейку В5 записать формулу с использованием логической функции ЕСЛИ для вывода текстового значения "жара", "тепло" или "холодно", при следующих условиях: если значение в ячейке А5>=30, то выводится "жара", если значение в ячейке А5>=15. то выводится "тепло", иначе - "холодно".

=ЕСЛИ(А5>=30: "жара";ЕСЛИ(А5>=15;"тепло". "холодно"))

3. Скопировать эту формулу на весь диапазон.

Пример 2.

Порядок работы:

1. В диапазоны Е4:Е11 и F4:F11 ввести любые отрицательные и положительные числа.

2. В ячейке G11 записать формулу для вычисления с использованием логической функции ЕСЛИ. Использовать условие: если сумма значений в столбце Е4:Е11 больше 0, то вычисляется сумма значений в столбце F4:F11, в противном случае результат - 0.

=ЕСЛИ (СУММ(Е4:Е11)>0;СУММ(F4:F11);0)

 

Пример 3.

Порядок работы:

Вычислить значения y при заданных условиях:

1. Ввести в столбец С2:С11 значения х =(-3: 2; 4;-5: 1; 7; -1; 12; 3; ())

2. В ячейку D2 ввести формулу для вычисления y, используя логическую функцию ЕСЛИ:

=ЕСЛИ(C2<0;C2^3;COS (С2))

3. Скопировать эту формулу на весь диапазон значений х.

Пример 4.

Порядок работы:

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

1. В диапазон ячеек А13:АЗЗ ввести значения х: х Î [- 20;20], шаг 2

2. В ячейку ВІЗ ввести формулу для вычисления функции. Формулу можно записать двумя способами:

= ЕСЛИ(И(А13>=-10; А44<=10); АВS(А13); 10)) или

= ЕСЛИ(ИЛИ(А13<-10;А44>10);10; АВS(А13)).

3. Скопировать эту формулу на весь диапазон значений аргумента.

 

Пример 5. Вычислить значение функции у = е^х cos(Зх). для хÎ[-l;l] с шагом h.
h = 0.1. Определить количество отрицательных значений функции.

Порядок работы:

1. Ввести в ячейку А10 начальное значение аргумента: х = -1. Заполнить столбец А значениями аргумента функции в заданном диапазоне.

2. В ячейке В10 ввести формулу: =EXP(A10)*COS(3*A10).

3. Скопировать эту формулу на остальные ячейки диапазона. В итоге будут вычислены соответствующие значения функции.

4. Для определения количества отрицательных значений функции в ячейку С10 ввести формулу: = СЧЁТЕСЛИ (В10:В30;"<0")

5. В результате в ячейке С10 будет вычислено количество отрицательных значений функции в заданном диапазоне.

 

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

Найти максимальное значение функции z= F(x,y) в области, заданной системами неравенств.

Порядок работы:

Заданная область определения состоит из двух колец, принадлежащих квадрату: х Î [-1;1], у Î [-1:1], (Рис. 1.)

Для решения задачи зададим массив точек (х,у) в этом квадрате с шагом h по х и у. h=0.2.

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

1. В ячейки рабочего листа ввести координаты для точек массива в виде таблицы. В ячейки А9:А19 - значения х, в ячейки В8:Е8 - значения у (рис.2.).

2. В соответствии с заданными неравенствами формируется формула для определения принадлежности точек массива заданной области. В случае положительного результата в ячейке подсчитывается значение функции z = F(x,у), иначе выводится текстовое значение "нет". Итак, в ячейку В9, которая соответствует первым значениям координат х и у вводим формулу с использованием логических функций:

=ЕСЛИ(ИЛИ(И($А9^2+В$8^2<=1;$А9^2+В$8^2>=9/16);И($А9^2+В$8^2<=
1/4;$А9^2+В$8^2>=1/16));10*LN(1+$А9*В$8)+В$8/(1 +$А9^2);"нет").

3. Скопировать эту формулу на весь массив точек.

4. В ячейку В23 ввести текст: "максимальное значение функции", в ячейку В25 ввести формулу: = МАКС(В9:L19).

5. В ячейку В27 ввести текст: "кол-во точек массива, попавших в область определения", в ячейку В29 ввести формулу:
=СЧЁТЕСЛИ (В9:L19;"<>нет").

Рис.2. Поиск максимального значения функции от двух переменных

Сохраните результат.

 

Лабораторная работа №4

Создайте новую книгу, назвав ее «Лабораторная работа №4».

Пример 1. Построение графика функции одной переменной.

Построить графики функций Sin(x) и Cos(x) на отрезке от -ПИ до ПИ. Отрезок разделить на десять равных частей. Пример построения графика приведен на Рис 1.

Порядок работы:

1. Протабулируйте функции на заданном отрезке. Шаг табуляции определить как отношение длины отрезка табулирования функции к числу отрезков N: (ПИ() – (-ПИ))/N.

	A	B	C	D	E	F	G	Н
	Построение графика функции
	Интервал:	-3,1416	3,1416
	Число точек:		Шаг табуляции	0,628319
	Аргумент	Sin(x)	Cos(x)
	-3,14159		-1
	-2,51327	-0,587785	-0,809017
	-1,88496	-0,951057	-0,309017
	-1,25664	-0,951057	0,309017
	-0,62832	-0,587785	0,809017
	0,628319	0,587785	0,809017
	1,256637	0,951057	0,309017
	1,884956	0,951057	-0,309017
	2,513274	0,587785	-0,809017
	3,141593		-1

Рис.1 График функции

 

2. Выделите область исходных данных для построения графика функции Sin(x), включая заголовки: A6:B17. Щелкните в стандартной панели инструментов кнопку Мастер диаграмм.

3. Выполните первый шаг: выберите тип диаграммы - точечная и вид диаграммы - плавная кривая с метками точек на графике функции. Щелкните кнопку Далее.

4. Выполните второй шаг: так как диапазон данных был указан заранее, то просто щелкните кнопку Далее.

5. Выполнить третий шаг: используя закладки диалогового окна, введите название диаграммы “График функции Sin(x)”, название оси Х - “Х”, название оси Y - “Y”. Установите, при необходимости линии сетки основные и промежуточные, определите место расположения легенды, режим вывода на график числовых значений или категорий. Щелкните кнопку Далее.

6. Выполните четвертый шаг: укажите место размещения диаграммы: на отдельном листе или в текущем рабочем листе.

7. Добавьте к графику функции Sin(x) график функции Cos(x). Для этого выполните следующее:

- щелкните правой кнопкой мыши линию графика функции Sin(x) и выберите в контекстном меню команду Исходные данные;

- в окне диалога Исходные данные щелкните кнопку Добавить;

- в строку ввода Имя введите щелчком мыши содержание ячейки С6;

- в строку ввода Значения введите диапазон значений функции С7:С17;

- в строку ввода Подписи по оси Х введите диапазон значений аргумента А7:А17.

- щелкните кнопку Ок.

Примечание. Можно строить одновременно несколько графиков функций. В рассмотренном примере графики функций Sin(x) и Cos(x) строятся по очереди в учебных целях.

 

Лабораторная работа №5

Создайте новую книгу, назвав ее «Лабораторная работа №5».

Пример 1. Решить систему линейных алгебраических уравнений матричным методом (рис. 1.):

(1)

	A	B	C	D	E	F	G
	Решение системы линейных алгебраических уравнений матричным способом: Х=А^-1B
		Матрица Коэффициентов	Вектор свободных членов
			-5
			-4
			-6			-4
			Обратная матрица		Результат
		-2	-0,8	2,2		-28,8
		-1	0,4	0,4		-7,6
			0,6	-0,4		4,6

Рис.1. Решение системы уравнений матричным методом

Порядок работы:

1. Внесите в ячейки B6 – D8 значения коэффициентов при неизвестных.

2. Внесите в ячейки F6 – F8 значения свободных членов системы уравнений.

3. Выделите диапазон ячеек B12: D14 и введите формулу МОБР(B6:D8), для завершения операции ввода нажмите комбинацию клавиш Ctrl+Shift+Enter.

4. Выделите диапазон ячеек F12:F14 и введите формулу МУМНОЖ(B12:D14;F6:F8). Для завершения ввода формулы нажмите комбинацию клавиш Ctrl+Shift+Enter. В ячейках F12 – F14 появятся значения корней уравнения.

Пример 2. Решите систему линейных алгебраических уравнений (1) методом Крамера (Рис. 2.).

Порядок работы: