Установление диапазона критериев

Меню

Под заголовком окна находится строка меню, через которую можно вызвать любую команду Microsoft Excel. Для открытия меню необходимо щелкнуть мышью на его имени. После этого появятся те команды этого меню, которые используются наиболее часто (рис.3). Если щелкнуть по кнопке в нижней части меню то появятся все команды этого меню (рис.4).

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

Под строкой меню расположены панели инструментов, которые состоят из кнопок с рисунками. Каждой кнопке соответствует команда, а рисунок на этой кнопке передает значение команды. Большинство кнопок дублирует наиболее часто употребляемые команды, доступные в меню. Для вызова команды, связанной с кнопкой, необходимо щелкнуть мышью на этой кнопке. Если навести указатель мыши на кнопку, рядом появится рамка с названием команды.

Обычно под строкой меню находятся две панели инструментов – Стандартная и Форматирование. Чтобы вывести или убрать панель с экрана, следует выбрать в меню Вид пункт Панели инструментов, а затем щелкнуть на имя нужной панели. Если панель присутствует на экране, то рядом с ее именем будет стоять метка

Файл Microsoft Excel называется рабочей книгой. Рабочая книга состоит из рабочих листов, имена которых (Лист1, Лист2, …) выведены на ярлыках в нижней части окна рабочей книги (рис.2). Щелкая по ярлыкам, можно переходить от листа к листу внутри рабочей книги. Для прокручивания ярлыков используются кнопки слева от горизонтальной координатной линейки:

	переход к ярлыку первого листа;
	переход к ярлыку предыдущего листа;
	переход к ярлыку следующего листа;
	переход к ярлыку последнего листа.

Рабочий лист представляет собой таблицу, состоящую из 256 столбцов и 65536 строк. Столбцы именуются латинскими буквами, а строки – цифрами. Каждая ячейка таблицы имеет адрес, который состоит из имени строки и имени столбца. Например, если ячейка находится в столбце F и строке 7, то она имеет адрес F7.

Выделение элементов таблицы

Одна из ячеек таблицы всегда является активной. Активная ячейка выделяется рамкой. Чтобы сделать ячейку активной, необходимо клавишами управления курсором подвести рамку к этой ячейке или щелкнуть в ней мышью.

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

Чтобы выделить целый столбец или строку таблицы, необходимо щелкнуть мышью на его имени. Для выделения нескольких столбцов или строк следует щелкнуть на имени первого столбца или строки и растянуть выделение на всю область.

Для выделения нескольких листов необходимо нажать клавишу Ctrl и, не отпуская ее, щелкать на ярлыках листов.

Рис.5

Заполнение ячеек

Для ввода данных в ячейку необходимо сделать ее активной и ввести данные с клавиатуры. Данные появятся в ячейке и в строке редактирования (рис.8). Для завершения ввода следует нажать Enter или одну из клавиш управления курсором. Процесс ввода данных закончится и активной станет соседняя ячейка.

Чтобы отредактировать данные в ячейке, необходимо:

¯ сделать ячейку активной и нажать клавишу F2 или дважды щелкнуть в ячейке мышью;

¯ в ячейке появится текстовый курсор, который можно передвигать клавишами управления курсором;

¯ отредактировать данные;

¯ выйти из режима редактирования клавишей Enter.

Внимание! Перед выполнением любой команды Microsoft Excel следует завершить работу с ячейкой, т. е. выйти из режима ввода или редактирования.

Отмена операций

Для отмены последней операции над данными необходимо в меню Правкавыбрать команду Отменить … или щелкнуть кнопку . Если щелкнуть на стрелке 6 рядом с этой кнопкой, то откроется список операций, выполненных в текущем сеансе. Щелкнув на имени одной операции, можно отменить ее и все операции, выполненные после нее.

Чтобы вернуть последнюю отмененную операцию, следует в меню Правкавыбрать команду Повторить … или щелкнуть кнопку . Для просмотра списка отмененных операций следует щелкнуть на стрелке 6 рядом с этой кнопкой.

Работа с формулами

Основные сведения

Вычисления в таблицах выполняются с помощью формул. Формула может состоять из математических операторов, значений, ссылок на ячейку и имена функций. Результатом выполнения формулы есть некоторое новое значение, содержащееся в ячейке, где находится формула. Формула начинается со знака равенства "=". В формуле могут использоваться арифметические операторы + - * / Порядок вычислений определяется обычными математическими законами.

Примеры формул: =(А4+В8)*С6, =F7*С14+B12.

Константы – текстовые или числовые значения, которые вводятся в ячейку и не могут изменяться во время вычислений.

Ссылка на ячейку или группу ячеек – способ, которым указывается конкретная ячейка или несколько ячеек. Ссылка на отдельную ячейку – ее координаты. Значение пустой ячейки равно нулю.

Ссылки на ячейки бывают двух типов:

относительные – ячейки обозначаются относительным смещением от ячейки с формулой (например: F7).

абсолютные – ячейки обозначаются координатами ячеек в сочетании со знаком $ (например: $F$7).

Комбинация предыдущих типов (например: F$7).

При копировании формул относительные ссылки изменяются на размер перемещения.

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

: (двоеточие) – формирует обращение к блоку ячеек. Через двоеточие указывается левая верхняя и правая нижняя ячейки блока. Например: С4:D6 – обращение к ячейкам С4, С5, С6, D4, D5, D6.

; (точка с запятой) – обозначает объединение ячеек. Например, D2:D4;D6:D8 – обращение к ячейкам D2, D3, D4, D6, D7, D8.

Для ввода формулы в ячейку следует ввести знак '=' и формулу для вычисления. После нажатия клавиши Enter в ячейке появится результат вычисления. При выделении ячейки, содержащей формулу, формула появляется в строке редактирования.

Функции

Функциями в Microsoft Excel называют объединения нескольких вычислительных операций для решения определенной задачи. Функции в Microsoft Excel представляют собой формулы, которые имеют один или несколько аргументов. В качестве аргументов указываются числовые значения или адреса ячеек.

Например:

=СУММ(А2:А4) – сумма ячеекА2, А3, А4;

=СРЗНАЧ(B1:B4) – среднее значение ячеек B1, B2, B3, B4 .

Функции могут входить одна в другую, например:

=СУММ(A1:A10)ОКРУГЛ(СРЗНАЧ(H4:H8);2);

Для введения функции в ячейку необходимо:

¯ выделить ячейку для формулы;

¯ вызывать Мастер функций с помощью команды Функция меню Вставка или кнопки ;

¯ в диалоговом окне Мастер функций (рис.6), выбрать тип функции в поле Категория, затем функцию в списке Функция;

¯ щелкнуть кнопку ОК;

Рис.6. Мастер функций

¯ в полях Число1, Число2 и др. следующего окна ввести аргументы функции (числовые значения или ссылки на ячейки);

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

¯ щелкнуть ОК.

Вставить в ячейку функцию суммы СУММ можно с помощью кнопки .

Массивы формул

Массивы формул удобно использовать для введения однотипных формул и обработки данных в виде таблиц. Например, для вычисления модуля от чисел, размещенных в ячейках B1, C1, D1, E1, вместо ввода формул в каждую ячейку можно ввести одну формулу – массив для всех ячеек. Microsoft Excel добавляет вокруг массива формул фигурные скобки

{ }, по которым его можно отличить.

Для создания массива формул необходимо:

¯ выделить ячейки, в которых должен находиться массив формул (рис.7);

¯ ввести формулу обычным способом, указав в качестве аргументов группу ячеек-аргументов;

¯ в последнем окне вместо кнопки ОК нажать комбинацию клавиш Ctrl+Shift+Enter.

Для редактирования массива формул необходимо:

¯ выделить ячейки, в которых находится массив;

¯ щелкнуть мышью внутри строки редактирования и отредактировать формулу;

¯ нажать комбинацию клавиш Ctrl+Shift+Enter.

Рис.7

Сообщения об ошибках

Если формула в ячейке не может быть правильно вычислена, Microsoft Excel выводит в ячейку сообщение об ошибке. Если формула содержит ссылку на ячейку, которая содержит значения ошибки, то вместо этой формулы также будет выводиться сообщение об ошибке. Значение сообщений об ошибках следующее:

#### – ширина ячейки не позволяет отобразить число в заданном формате;

#ИМЯ? – Microsoft Excel не смог распознать имя, использованное в формуле;

#ДЕЛ/0! – в формуле делается попытка деления на нуль;

#ЧИСЛО! – нарушены правила задания операторов, принятые в математике;

#Н/Д – такое сообщение может появиться, если в качестве аргумента задана ссылка на пустую ячейку;

#ПУСТО! – неверно указано пересечение двух областей, которые не имеют общих ячеек;

#ССЫЛКА! – в формуле задана ссылка на несуществующую ячейку;

#ЗНАЧ! – использован недопустимый тип аргумента

Работа с базами данных

С помощью Microsoft Excelможно создавать и обрабатывать базы данных. База данных в Microsoft Excel – таблица, состоящая из однотипных записей (строк). Столбцы таблицы являются полями записи в базе данных. Под имена полей выделяется первая строка базы данных. Например, если базой данных считать телефонный справочник, то полями записи будут фамилии, номера телефонов и адреса абонентов.

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

Рис.8

Установление диапазона критериев

Критерии бывают двух типов.

¯ Критерии вычисления – это критерии, которые являются результатом вычисления формулы. Например, диапазон критериев =F7>СРЗНАЧ($F$7:$F$21) выводит на экран строки, имеющие в столбце F значения большие, чем среднее значение величин в ячейках F7:F21. Формула должна возвращать логическое значение ЛОЖЬ илиИСТИНА. При фильтрации будут доступные только те строки, значения которых будут придавать формуле значения ИСТИНА.

¯ Критерии сравнения – это набор условий для поиска, используемый для извлечения данных при запросах по примеру. Критерий сравнения может быть последовательностью символов (константой) или выражением (например, Цена > 70).

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

¯ выделить ячейку в таблице;

¯ в меню Данные выбрать команду Форма;

¯ щелкнуть кнопку Критерии;

¯ в полях редактирования ввести критерии для поиска данных;

¯ для вывода на экран первой записи, отвечающей критерию, щелкнуть кнопку Далее;

¯ для вывода на экран предыдущей записи, отвечающей критерию, щелкнуть кнопку Назад;

¯ для поиска записей в списке по другим критериям щелкнуть кнопку Критерии и ввести новые критерии;

¯ по окончанию щелкнуть кнопку Закрыть.

Чтобы снова получить доступ ко всем записям таблицы, необходимо щелкнуть кнопку Критерии, а затем – кнопку Правка.

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

Автофильтр

Команда Автофильтр устанавливает кнопки скрытых списков (кнопки со стрелками) непосредственно в строку с именами столбцов (рис.9). С их помощью можно выбирать записи базы данных, которые следует вывести на экран. После выделения элемента в открывшемся списке, строки, не содержащие данный элемент, будут скрыты. Например, если в скрытом списке поля Цена выбрать 50р., то будут выведены только записи, у которых в поле Цена содержится значение 50р.

Рис.9

Если в поле списка выбрать пункт Условие … , то появится окно Пользовательский автофильтр (рис.10). В верхнем правом списке следует выбрать один из операторов (равно, больше, меньше и др.), в поле справа – выбрать одно из значений. В нижнем правом списке можно выбрать другой оператор, и в поле по левую сторону – значение. Когда включен переключатель И, то будут выводиться только записи, удовлетворяющие оба условия. При включенном переключателе ИЛИ будут выводиться записи, удовлетворяющие одному из условий.

Для вывода нескольких записей с самым большим или самым малым значением по любому полю следует в скрытом списке поля выбрать пункт Первые 10. В диалоговом окне Наложение условия по списку в первом поле со счетчиком необходимо выбрать количество записей, а в поле справа выбрать – наибольших или наименьших.

Чтобы вывести все данные таблицы, необходимо вызвать команду Отобразить все или отменить команду Автофильтр меню Данные, подменю Фильтр.

Рис.10

Расширенный фильтр

Команда Расширенный фильтр позволяет фильтровать данные с использованием диапазона критериев для вывода только записей, удовлетворяющих определенным критериям (рис.11). При повторной фильтрации будут просматриваться все строки, и скрытые, и открытые. Значение переключателей и полей окна Расширенный фильтр следующие:

Рис.11

фильтровать список на месте – переключатель, скрывающий строки, которые не удовлетворяют указанному критерию;

скопировать результат в другое место – копирует отфильтрованные данные на другой рабочий лист или на другое место на этом же рабочем листе;

Исходный диапазон – поле, определяющее диапазон, который содержит список, подлежащий фильтрации;

Диапазон условий – поле, определяющее диапазон ячеек на рабочем листе, который содержит необходимые условия;

Поместить результат в диапазон – поле, определяющее диапазон ячеек, в который копируются строки, удовлетворяющие определенным условиям; это поле активно только в том случае, если выбран переключатель скопировать результат в другое место;

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

Для установления сложных критериев необходимо:

¯ вставить несколько строк в верхней части рабочего листа;

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

¯ при использовании критериев сравнения, имена критериев должны быть идентичны именам столбцов, которые проверяются;

¯ в строках, расположенных под строкой с именами проверяемых столбцов, ввести критерии, которым должны соответствовать ячейки проверяемых столбцов;

¯ выбрать в меню Данные пункт Фильтр, затем – Расширенный фильтр, и в диалоговом окне ввести условия фильтрации.

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

Создание диаграмм

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

Для создания диаграммы необходимо:

¯ на рабочем листе выделить данные, по которым следует построить диаграмму, включая ячейки, содержащие имена категорий или рядов, которые будут использоваться в диаграмме;

¯ выбрать команду ДиаграммаменюВставка или щелкнуть кнопку ;

¯ в диалоговых окнах Мастера диаграмм следует выбрать тип, формат и другие параметры диаграммы;

¯ для перехода к следующему шагу используется кнопка Далее >;

¯ для построения диаграммы на любом шаге можно щелкнуть кнопку Готово, тогда Мастер диаграмм самостоятельно закончит построение диаграммы;

¯ в последнем (4-м) окне щелкнуть кнопку Готово.

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

Задание 1.

Вычислить значение функции y=f(x) на интервале [a,b] с шагом h.

Решение должно быть получено в виде таблицы.

Построить график функции

y=cos x + e

Интервал и шаг задать самостоятельно.

1. В ячейки А1 и В1 ввести текст «х» и «у» соответственно.

2. В ячейки С1, D1 и E1 ввести текст «а», «b» и «шаг» соответственно.

3. В ячейки С2, D2 и E2 вводим значения a,b и шага, например,

a=1 b=5 шаг=0,5

4. В ячейку А2 вводим формулу =$C$2

5. В ячейку А3 вводим формулу =A2+$E$2

6. Копируем эту формулу до значения x=5

7. В ячейку B2 вводим формулу =(COS(A2^4))^5+EXP(2*A2)

8. Копируем эту формулу до последнего значения х.

Рис.12

9. Выделить диапазон ячеек A2:B10.

10. Вызываем мастер диаграмм

11. Выбираем тип диаграммы – Точечная.

12. Далее по диалогу называем график функции, определяем оси OX и OY. В результате получим график функции. Рис.13

Рис.13

Логические функции

Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Синтаксис

И(логическое_значение1; логическое_значение2; ...)

Логическое_значение1, логическое_значение2, ... — это от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

ИЛИ

Возвращает ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА; возвращает ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Синтаксис

ИЛИ(логическое_значение1;логическое_значение2; ...)

Логическое_значение1, логическое_значение2,... — от 1 до 30 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ

НЕ

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

Синтаксис

НЕ(логическое_значение)

Логическое_значение — величина или выражение, которые могут принимать два значения: ИСТИНА или ЛОЖЬ.

ЕСЛИ

Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

Синтаксис

ЕСЛИ(лог_выражение;значение_если_истина ;значение_если_ложь)

Логическое_выражение — это любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Если логическое выражение получает значение ИСТИНА, то работает значение сразу после логического выражения, в противном случае последнее.

Массивы формул

Для создания массива формул необходимо:

¯ выделить ячейки, в которых должен находиться массив формул (рис.14);

¯ ввести формулу обычным способом, указав в качестве аргументов группу ячеек-аргументов;

¯ в последнем окне вместо кнопки ОК нажать комбинацию клавиш Ctrl+Shift+Enter.

Для редактирования массива формул необходимо:

¯ выделить ячейки, в которых находится массив;

¯ щелкнуть мышью внутри строки редактирования и отредактировать формулу;

¯ нажать комбинацию клавиш Ctrl+Shift+Enter.

Рис.14

Задача 2.

Вычислить функцию y=f(x), обеспечив не менее 2-х точек из каждого интервала:

–x +3 , если x£-6

y= x +lnx , если 0<x£2

x +1 , если x>2

На числовой прямой данная функция определена следующим образом:

y= –x +3 -6 0 y= x +lnx 2 y= x +1

На интервале от -6 до 0 функция не определена.

Алгоритм

Ввод x

Если x -6

y= –x +3

Вывод y

иначе

если x 0

Вывод « Функция не определена»

Иначе

если x 2

y= x +lnx

Вывод y

Иначе y= x +1

Вывод у

Всеесли

Реализация на Excel:

1. В ячейки А1 и В1 ввести текст «х» и «у» соответственно.

2. В диапазон А2:A10 ввести значения х из всех данных интервалов, т.е.

(-∞;-6], (-6;0], (0;2], (2,+∞) по 2-3 значения из каждого.

3.В ячейку F2 ввести текст «Функция не определена».

4. В ячейку В2 ввести формулу:

=ЕСЛИ(A2<=-6;-A2^2+3;ЕСЛИ(A2<=0;$F$2;ЕСЛИ(A2<=2;A2^2+LN(A2);A2^2+1)))

В результате получится следующая таблица значений:

Задача 2.

Вычислить функцию z=f(x,y), учитывая область существования.

Z = +lnxy

Область существования: x-y≥0 x-5≠0 xy>0

Алгоритм

Ввод x,y

Если x-5≠0

если x-y≥0

если xy>0

Z = +lnxy

Вывод y

иначе

Вывод « Логарифм отрицательного числа »

Всеесли

Иначе

Вывод « Корень из отрицательного числа»

Всеесли

Иначе

Вывод « Деление на ноль»

Всеесли

Реализация на Excel:

1. В ячейки А1 , В1,С1 ввести текст «х» «у» «z» соответственно.

2. В диапазоны А2:A10, B2:В10 ввести значения х и y из области существования .

3.В ячейку F1 ввести текст «Логарифм отрицательного числа».

4. В ячейку F2 ввести текст «Корень из отрицательного числа».

5. В ячейку F3 ввести текст «Деление на ноль».

6. В ячейку С2 ввести формулу:

=ЕСЛИ(A2-5<>0;ЕСЛИ(A2-B2>=0;ЕСЛИ(A2*B2>0;(A2-B2)^0,5/(A2-5)+LN(A2*B2);$F$1);$F$2);$F$3)

В результате получится следующая таблица значений

Задание 3.

Дана система уравненией с несколькими неизвестными. Найти корни этой системы:

А) методом Крамера (определителей)

Б) методом обратной матрицы

С) поиск решений

При выполнении этой лабораторной работы будут использоваться массивы формул (см.выше) и функции:

МОПРЕД(массив)-

Массив — числовой массив с равным количеством строк и столбцов.

Определитель матрицы — это число, вычисляемое на основе значений элементов массива.

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

МОПРЕД(A1:C3) равняется
A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*С1)

МОБР(массив)

Возвращает обратную матрицу для матрицы, хранящейся в массиве. Обратные матрицы, как и определители, обычно используются для решения систем уравнений с несколькими неизвестными. Произведение матрицы на ее обратную матрицу — это единичная матрица, то есть квадратный массив, у которого диагональные элементы равны 1, а все остальные элементы равны 0. Если дана матрица вида , то её обратная матрица будет следующей:

Так расчёт выполняется математически, а в Excel эту задачи выполняет функция МОБР() и МОПРЕД().

МУМНОЖ(массив1; массив2)

Возвращает произведение матриц (матрицы хранятся в массивах). Результатом является массив с таким же числом строк, как массив1 и с таким же числом столбцов, как массив2. Количество столбцов аргумента массив1 должно быть таким же, как количество сток аргумента массив2, и оба массива должны содержать только числа.

Пример:

Дана система линейных уравнений. Найти корни этой системы

13x - 12x - 14x + 18x = 39

7x + 17x + 3x + 6x = 60,6

12x + 16x + 8x + 4x = 59,2

2x - x - 3x + 6x = 7,4

Метод Крамера.

, где Δ – главный определитель

Δx -

1. В Excel ввести коэффициенты при неизвестных, вектор свободных членов следующим образом:

2.Получить 4 новых матрицы 4х4 путём замены 1-го столбца вектором из свободных членов, далее 2-го столбца –вектором свободных членов, 3-го столбца, 4 -гостолбца.

В результате :

3. В ячейках Н1: Н5 записать текст:

4. В ячейках I1: I5 записать формулы:

5. В ячейках Н7: Н10 записать текст: x1=,x2=,x3=,x4=

В ячейках I7: I10 записать формулы:

Таким образом решается система уравнений методом Крамера.

Решение системы уравнений методом обратной матрицы:

, где - обратная матрица,

В- вектор свободных членов.

1.В Excel ввести коэффициенты при неизвестных, вектор свободных членов следующим образом:

2.Вычислить обратную матрицу:

В ячейке А6 записать формулу: = МОБР(A1:A4)

Выделить диапозон c A6:D9, указатель мыши в строку формул и нажать CTRL+SHIFT+ENTER одновременно.

3.В ячейках Н1: Н4 записать текст: x1=,x2=,x3=,x4=

В ячейках H5: H9 записать формулы

Надстройка «Поиск решения»

Поиск решений является частью блока задач, который иногда называют анализ «что-если». Процедура поиска решения позволяет найти оптимальное значение формулы содержащейся в ячейке, которая называется целевой. Эта процедура работает с группой ячеек, прямо или косвенно связанных с формулой в целевой ячейке. Чтобы получить по формуле, содержащейся в целевой ячейке, заданный результат, процедура изменяет значения во влияющих ячейках. Чтобы сузить множество значений, используемых в модели, применяются ограничения. Эти ограничения могут ссылаться на другие влияющие ячейки.

Решение системы линейных уравнений с использованием надстройки «Поиск решений» выполняется следующим образом:

1. Даются значения предполагаемых корней.

2. В целевую ячейку вводится формула какого-либо уравнения с этими корнями.

3. В ограничения заносятся все остальные уравнения.

1 этап выполнения:

2 этап выполнения:

3 этап выполнения:

После выполнения:

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

Задание 4.

Дана таблица «Товар» с полями:

Товар

Поставщик

Цена

Дата_покупки

1. Получить список товаров с ценой >3000 и <10000

2. Выбрать товары, начинающиеся на заданную букву и датой покупки после 30.10.05

Алгоритм выполнения

1. Оформить заголовок таблицы

2. Заполнить таблицу данными.

3. Задать критерии отборки.

4. Отфильтровать данные по критериям

(Данные/Фильтр/ Расширенный фильтр)

Результаты фильтрации в нашем примере помещены на другой лист:

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

Критерий выборки по второму условию будет выглядеть следующим образом:

Товар	Дата_покупки
Т*	>30.10.05

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

Построить таблицу значений и диаграмму(график , точечная) для функции

Y=f(x) в Excel на интервале [a,b] c шагом h (все задается самостоятельно).

ВАРИАНТЫ ЗАДАНИЙ:

1.y=cos x-2ln cos x 13.y==5sin x e+

2.y=6*ln 14.y=arccos x -e+

3. y=(2-x)cos x -e+ 15.y==(9+x)ctg x -e+

4.y=ln(x+ ) 16.y=(10-x)sin x -e+ 5.y=3 17.y=6+tg

6.y=(1+x )arctgx 18.y=9*tg

7.y=arcsin(1-x)+ 19.y=6x+cos

8.y=arccos (x)-x +tg x 20.y= sin(x+ )

9.y=arcsin (x)+x +ln x 21.y= tg(x+5+ )

10.y=cos (x)-sinx +arctg x 22.y= sin (1-x )+

11.y=sin (x)+x +ctg x 23.y== cos (1+x )+

12.y==(2-x)cos x -e + cosx 24.y== ctg (1-x )+

25.y==ln (1-x )+

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

Вычислить значение функции z=f(x,y), учитывая область существования.

1.z=arcsin(x+y) 13.z=

2.z=sin 14.z=

3.z=lnx+lny 15.z=arccosx+9y

4.z= 16.z=

5.z=arccos(xy) 17.z=

6.z=tg 18.z=

7.z=lnx-4lny 19.z=arccosy+lnx

8.z= 20.z=

9.z= 21.z=sin

10.z=arccos(x y) 22.z=

11.z=ctg 23.z= +lny

12.z=lnx+lnxy 24.z=arcsinxy+lny

25.z= 26.z=

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

Решить систему уравнений: А) по правилу Крамера

Б) методом обратной матрицы

В) Сервис/ Поиск решений

1. 12x + x + 7x + 14x = 43,4

x - 13x - 4x - 8x = 25,2

3x + 16x + 7x + 6x = 23,4

9x - x + 5x + 4x = 29,8

2. 13x - 12x - 14x + 18x = 39

7x + 17x + 3x + 6x = 60,6

12x + 16x + 8x + 4x = 59,2

2x - x - 3x + 6x = 7,4

3. 6x + 4x + 3x = 36

2x + x + 3x = 22

10x + 5x + 8x = 80

2x + 6x + 3x = 45

4. 24x + 27x - 14x + 18x = 18,3

15x - 11x + 3x + 6x = 5

13x - 19x + 8x + 4x = 106

8x + 25x - 3x + 6x = 82,2

5. x - 3x - 45x + 2x = 36

49x + 60x - 3x - 2x = 68

59x - 42x - 95x = 65

48x + 2x + x - x = 21

6. 7x - 12x + 4x + 2x = -8,8

3x + 21x + 8x + 5x = 23,7

x + 7x - 14x + 18x = -3

21x + 4x + 32x + 11x = 30,1

7. 21,6x - 3,2x + 86,4x - 4,8x = 1113

44,8x - 5,6x + 67,2x - 6,4x = 1125

67,2x - 6,4x + 44,8x - 5,6x = 1136

86,4x - 4,8x + 21,6x - 3,2x = 1147

8. 3x - 4x + 5x = 13

3x - x = 5

7x - 8x - 4x = 21

3x + 10x - 4x = 4

9. 4x + 0,24x - 0,08x + 0,16x = 8

0,09x + 3x - 0,15x - 0,12x = 9

0,04x - 0,08x + 4x + 0,06x = 20

0,02x + 0,06x + 0,04x - 10x = 1

10. x - 5x - x + 3x = -5

2x + 3x + x - x = 4

3x - 2x + 3x + 4x = -1

5x + 3x + 2x + 2x = 0

11. 10,9x + 1,2x + 2,1x + 0,9x = -7

1,2x + 11,2x + 1,5x + 2,5x = 5,3

2,1x + 1,5x + 9,8x + 1,3x = 10,3

0,9x + 2,5x + 1,3x + 12,1x = 24,6

12. –0,88x - 0,23x + 0,25x - 0,16x = -1,24

0,14x - 0,66x - 0,18x + 0,24x = 0,89

0,33x + 0,03x - 0,54x - 0,32x = -1,15

0,12x - 0,05x - 0,85x = 0,57

13. 20,9x + 1,2x + 2,1x + 0,9x = 21,7

1,2x + 21,2x + 1,5x + 2,5x = 27,46

2,1x + 1,5x + 19,7x + 1,3x = 28,76

0,9x + 2,5x + 1,3x + 32,1x = 49,72

14. 4x + 4x + 5x + 5x = 0

2x + 3x - x = 10

x + x - 5x = -10

3x + 2x = 1

15. 2x - x + 3x + 2x = 4

3x + 3x + 3x + 2x = 6

3x - x - x - 2x = 6

3x - x + 3x - x = 6

16. 2x + 2x - x + x = 4

4x + 3x - x + 2x = 6

8x + 5x - 3x + 4x = 12

3x + 3x - 2x + 2x = 6

17. 2x + 5x + 4x + x = 20

x + 3x + 2x + x = 11

2x + 10x + 9x + 9x = 40

3x + 8x + 9x + 2x = 0

18. 3x + 5x + 3x + 5x = -6

3x + 4x + x + 2x = -3

6x + 8x + x + 5x = -8

3x + 5x + 3x + 7x = -8

19. 3x - 2x - 5x + x = 3

2x - 3x + x + 5x = - 3

x + 2x - 4x = - 3

x - x - 4x + 9x = 22

20. x + x - 6x - 4x = 6

3x - x - 6x - 4x = 2

2x + 3x + 9x + 2x = 6

3x + 2x + 3x + 8x = -7

21. x + 2x + 3x + 4x = 0

7x + 14x + 20x + 27x = 0

5x + 10x + 16x + 19x = -2

3x + 5x + 6x + 13x = 5

22. x + 3x - x = 10

x + 3x + 8x - x = 22

4x + 2x - 3x = 11

x - 6x - 2x = 0

23. x + x - x + x = 4

2x - x + 3x - 2x = 1

x - x + 2x = 6

3x - x + x - x = 0

24. x + 2x + 3x + 4x = 0

7x + 14x + 20x + 27x = 0

5x + 10x + 16x + 19x = -2

3x + 5x + 6x + 13x = 5

25. x + x + 2x = - 3

2x + x + 2x - x = 6

x - x + 3x - 2x = 10

3x + 2x - x + 4x = -15

26. 0,63x + x + 0,71x + 0,34x = 2,08

1,17x + 0,18x - 0,65x + 0,71x = 0,17

2,71x - 0,75x + 1,17x - 2,35x = 1,28

3,58x + 0,28x - 3,45x - 1,18x = 0,05

27. 3x + 2x + x + 11x = 25

7x + 4x + 3x + 5x =37

x + 10x + 2x + 19x = 44

6x - 8x + 14x + 15x = 31

28. 0,6x + x + 0,7x + 0,3x = 5,9

1,1x + 0,1x - 0,6x + 0,7x = 2,3

2,7x - 0,7x + 1,1x - 2,3x = -4,6

3,5x + 0,2x - 3,4x - 1,1x = -10,7

29. 0,3x + x + 0,1x + 0,4x = 4,8

1,7x + 0,8x - 0,5x + 0,1x = 8,3

2,1x - 0,5x + 1,7x - 2,5x = 7,8

3,8x + 0,8x - 3,5x - 1,8x = 8,8

30. 12x + 9x + 6x + 11x = 76

27x + 14x + 12x + 7x = 120

15x + 20x + 16x + 19x = 140

2x - 15x + 6x + 33x = 52

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

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

Варианты заданий:

1.”ЧЕЛОВЕК”

Ф.И.О.

Национальность

Вероисповедание

Рост

Вес

Дата рождения

А) Получить список людей моложе 25 лет.

Б) Получить список людей ,православных, ростом выше 1м 70см и ниже 1м 85см, с весом менее 100 кг.

2.”ПОКУПАТЕЛЬ”

Ф.И.О.

Адрес (город)

Номер счета

Название товара

Стоимость покупки

Дата покупки

А) Получить список покупателей ,сделавших покупки в один день.

Б) Получить список покупателей, купивших товар в пределах стоимости от 200 до 1000 рублей, в одном городе, наименование товара начинается на букву “т”.

3.”ПАЦИЕНТ”

Ф.И.О.

Год рождения

Номер телефона

Домашний адрес

Номер медкарты

Группа крови

Диагноз

А) Получить список пациентов, имеющих одну и ту же группу крови.

Б) Получить список пациентов, родившихся в период с 1940 до 1970г.,

с одинаковым диагнозом, фамилии которых начинаются на букву «С»

4.”СПОРТИВНАЯ КОМАНДА”

Название команды

Город

Количество игр

Количество игроков

Побед

Ничьих

Поражений

А) Получить список команд из одного города.

Б) Получить список команд, название которых начинаются на букву «Т», с количеством побед в диапозоне от 5 до 10 и не имеющих ни одного поражения.

5. ” АВТОМОБИЛИСТ”

ФИО

Номер авто

Марка авто

Цвет

Дата регистрации

Отдел регистрации ГИБДД

А) Получить список автомобилистов,имеющих автомобили красного цвета.

Б) Получить список автомобилистов, фамилия которых начинается на букву «К», стоящих на учете в одном отделе ГИБДД, купивших свою машину с1995 г. по 2000г.

6. “АВТОМОБИЛЬ”

Марка

Цвет

Серийный номер

Год выпуска

Цена

Регистрационный номер

А) Получить список машин одной марки.

Б) Получить список автомобилей с годом выпуска 1999, ценой от 6000$ до 10000$ и одного цвета.

7. “ФИЛЬМ” Название

Режиссер

Страна

Киностудия-производитель

Год выпуска

Стоимость

А) Получить список фильмов из одной страны.

Б) Получить список фильмов данного режиссёра, сделанных на одной киностудии и стоимостью от 1000 000 $ до 15000 000$.

8.”МУЗЫКАЛЬНЫЙ ТОВАР”

Носитель(грампластинка,лазерный диск,аудиокассета)

Название

Исполнитель

Количество произведений

Цена по каталогу

Время звучания

А) Получить список названий музыкальных произведений на СД.

Б) Получить список носителей одного исполнителя , время звучания котрых от 30 мин до 1,5 часа.

9.”ГОСУДАРСТВО”

Страна

Столица

Государственный язык

Население

Площадь

Денежная единица

А) Получить список стран,говорящих на английском языке.

Б) Получить список стран, столица которых начинается на букву «М»,население от 100000 до 50 000000 человек,площадь < 20000 кв.км.

10.”СТУДЕНТ”

ФИО

Город проживания

Институт

Группа

Число сессий

Средний балл

А) Получить список студентов из одного города.

Б) Получить список студентов,обучающихся в одном вузе, сдавших сессии на «4» и «5».

11. “ВОКЗАЛ”

Название вокзала

Номер поезда

Тип вагона

Пункт назначения

Время отправления

Цена билета

А) Получить список вокзалов,начинающих на букву «К».

Б) Получить список номеров поездов,где есть вагоны «Люкс»,с временем отправления от 14 до18 часов.

12. ” АЭРОФЛОТ”

Номер рейса,

Пункт назначения

Время вылета

Количество свободных мест

Цена билета

Авиакомпания

А) Получить список рейсов самолётов одной авиакомпании.

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

13. “К Н И Г О Л Ю Б”

Шифр книги

Автор

Название

Год издания

Издательство

Тираж

А) Получить список книг данного автора.

Б) Получить список книг ,названия которых начинается с буквы «М»,с годом издания с 1995 до 2000г.

14. “ И Г Р У Ш К А”

Название

Цена

Количество в магазине

Возрастные границы

Тип игрушки

Фирма-производитель

А) Получить список игрушек одного типа.

Б) Получить список фирм-производителей,выпускающих одну и туже игрушку с ценой от 30 до 200 рублей.

15. “З А Р П Л А Т А Р А Б О Ч И Х”

ФИО рабочего

Наименование цеха

Количество отработанных дней

Оклад

Премия

Зарплата

А) Получить список рабочих данного цеха.

Б) Получить список рабочих в зарплатой от 1000 до 2000 рублей получивших премию.

16. “И Н С Т И Т У Т”

Название института

Адрес

ФИО сотрудника

Название отдела

Стаж работы

Должность

А) Получить список институтов, находящихся в одном городе.

Б) Получить список сотрудников,работающих в одном отделе,со стажем работы от 5 до 10 лет.

1 7. “ А С С О Р Т И М Е Н Т”

Артикул обуви

Наименование

Количество

Цена одной пары

Фирма-производитель

Магазин

Артикул начинается с буквы “П” -детская, “М”-мужская,

“Д”-дамская.

А) Получить список обуви для детей.

Б) Получить список магазинов,где продают обувь данной фирмы и ценой от 3000 до 6000 руб.

18. “С Е М Ь Я”

Фамилия членов семьи

Адрес

Количество человек

Средний доход на одного человека

Дата добавления: 2021-03-18; просмотров: 165; Мы поможем в написании вашей работы!

Поделиться с друзьями:

12 Следующая ⇒

Мы поможем в написании ваших работ!