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

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

¯ Критерии вычисления – это критерии, которые являются результатом вычисления формулы. Например, диапазон критериев =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 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ

НЕ

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

Синтаксис

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

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

ЕСЛИ

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

Синтаксис

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

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

 

 

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

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

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

¯ выделить ячейки, в которых должен находиться массив формул (рис.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