Установление диапазона критериев
Критерии бывают двух типов.
¯ Критерии вычисления – это критерии, которые являются результатом вычисления формулы. Например, диапазон критериев =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