Построение графика функции с двумя и более условиями. 2 страница

2. С помощью инструментов Фильтр, Сортировать, Итоги выполните:

а) сортировку по наименованиям, по категориям и датам (т.е. по датам в каждой категории), по цене;

б) поиск товаров, цена которых находится в некотором диапазоне;

в) поиск товаров категории Кондитер. изд., купленных по цене, не превышающей 1500 р.;

г) стоимостные итоги по товарам, относящимся к одинаковым категориям;

д) максимальную и минимальную цену товаров каждой категории.

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

4. Поработайте со сводной таблицей:

– внесите изменения в исходную таблицу и обновите сводную;

– уберите из исходной несколько записей и перестройте сводную;

– сгруппируйте графу дата по месяцам.

Вариант 6

1. Cоздайте таблицу для начисления квартплаты:

В таблице ячейки, выделенные зеленым цветом, должны содержать ссылки на ячейку с соответствующей информацией; синим цветом – формулы для вычислений. Курсивом набраны пояснения. Пеня начисляется в размере 0,25% на каждый день просрочки платежа, платеж должен проходить не позднее 10-ого числа текущего месяца, дата везде должна стоять текущая. Сверхнормативной считается площадь свыше 20 кв.м на человека.

2. Заполните неизменные данные (тарифы, перерасчет, ссылки и формулы) и сохраните как шаблон.

3. Отдельным файлом создайте список жильцов:

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

Вариант 7

1. Заполните таблицу. Зачет получает только тот студент, который сдал все лабораторные, количество пропусков при этом не более 10 часов, средний балл контрольных работ не менее 6, неудовлетворительных оценок (1, 2, 3) на контрольных нет.

2. С помощью инструмента Фильтр выявите тех, у кого:

– количество пропусков более 10 часов;

– средний балл контрольных работ 8 и выше;

– нет несданных лабораторных работ.

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

4. Выполните консолидацию данных, подсчитав количество пропусков для каждого студента

Вариант 8

1. Создать таблицу учета товарооборота реализации продукции через торговые точки:

2. Заполните таблицы:

а) заполните первую;

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

2. С помощью инструмента Фильтр отобразите на любой из таблиц:

а) продукцию определенной ценовой категории (больше одной цены, но меньше другой);

б) только хлебобулочные изделия.

3. С помощью инструмента Промежуточные итоги отобразите на любой из таблиц:

а) сумму продаж по каждому виду продукции;

б) среднюю цену по каждому виду продукции.

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

Вариант 9

1. Составить таблицу данных по погоде в городах Республики Беларусь:

2. Используя Пользовательский автофильтр определить:

– города, температура в которых за конкретную дату превышала 9 °С. Отсортировать полученные данные ла по городу, а затем — по возрастанию температуры;

– данные по погоде для заданного города за конкретный промежуток времени. Отсортировать полученные данные сначала по виду, а затем — по возрастанию количества осадков;

– города, в которых наблюдалось северо-восточное направление ветра за конкретный период времени. Отсортировать эти данные сначала по городам, а затем возрастанию даты;

– города, температура в которых наблюдалась в пределах от 5 до 14 °С за конкретную дату. Отсортировать полученные данные сначала по городам, а затем — возрастанию температуры.

3. Используя расширенный фильтр определить:

– города, для которых направление ветра – северное или северо-западное, температура воздуха в которых больше 8 °С, но меньше 12 °С;

– данные о погоде для Санкт-Петербурга или Минска за некоторый конкретный промежуток времени;

– города, в которых за конкретный промежуток времени выпал снег или снег с дождем, а также – температура в которых находилась в пределах от –5 °С до +3 °С;

– города, в которых сила ветра не превышает 10% от средней силы ветра для города Гродно, и количество осадков – больше либо равно среднему значению для всех городов, или города, вид осадков в которых – град с дождем;

– города с западным или юго-западным направлением ветра, сила которого больше минимальной для Минска, или города, температура в которых за некоторый промежуток времени составляет 20% от средней температуры для Могилева.

4. Выведите следующие промежуточные итоги:

– среднее количество осадков данного вида с учетом данного города и конкретной даты;

– суммарное и среднее количество осадков данного вида для конкретного города;

– количество случаев определенного направления ветра с учетом конкретной даты;

– средние значения температуры и давления для конкретного города с учетом конкретной даты;

– средняя сила ветра определенного направления для конкретного города.

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

Вариант 10

1. Составьте таблицу Спортивные достижения студентов.

2. Используя Пользовательский автофильтр, определить:

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

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

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

– студентов конкретного курса, оценка которых за сдачу спортивного норматива больше 2, но меньше либо равна на 4. Отсортировать полученные данные сначала по факультету, а затем – по оценке.

3. Используя Расширенный фильтр, определить:

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

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

– студентов с максимальными показателями (оценками) по всем спортивным нормативам для каждого курса факультета;

– студентов с неудовлетворительными оценками для всех курсов и факультетов;

– студентов данного года рождения и данного года поступления, сдавших бег на 100 м или прыжки в длину с оценкой "4" и выше.

4. Выведите следующие промежуточные итоги:

– средний балл по факультету;

– суммарный и средний балл для каждого спортивного норматива;

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

– средний результат по каждому курсу.

Создание макросов

Теоретическая часть

Макрос – действие или набор действий, используемые для автоматизации выполнения повторяющихся задач. Макросы записываются на языке программирования Visual Basic для приложений (VBA) в Microsoft Office Excel. После создания макроса можно назначить его объекту (например, кнопке панели управления, графическому объекту или элементу управления), чтобы запускать этот макрос по щелчку объекта. Если макрос больше не нужен, его можно удалить.

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

Если вкладка Разработчик недоступна, выполните:

– щелкните значок Кнопка Microsoft Office, а затем щелкните Параметры Excel;

– в категории Основные в группе Основные параметры работы с Excel установите флажок Показывать вкладку "Разработчик" на ленте, а затем нажмите кнопку ОК.

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

Запуск макроса нажатием клавиши Ctrl в сочетании с клавишей быстрого вызова.Клавишу быстрого вызова макроса можно назначить уже созданному макросу в окне Параметры… выполнив Разработчик →Код → Макросыи выбрав нужный макрос по имени.

Для нового макроса – в поле Сочетание клавиш в появившемся окне при переходах Разработчик → Код → Запись макроса. К уже существующему Ctrl, надо ввести нужную прописную или строчную буквы.

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

Запуск макроса щелчком области графического объекта:

– выделите на листе графический объект (рисунок, клип, фигуру или рисунок SmartArt);

– для создания активной области на существующем объекте нажмите кнопку Фигуры в группе Иллюстрации на вкладке Вставка, выберите одну из фигур и нарисуйте ее на существующем объекте;

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

– выполните одно из следующих действий:

1) чтобы назначить графическому объекту существующий макрос, дважды щелкните нужный макрос или введите его имя в поле Имя макроса,

2) чтобы записать новый макрос для назначения выделенному графическому объекту, нажмите кнопку Записать. После завершения записи макроса нажмите кнопку Остановить запись на вкладке Разработчик в группе Код.

Практическая часть

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

Выполнение.

1. Запустите табличный процессор Excel.

2. Для начала записи макроса проделайте следующие действия:

– выполните команду Разработчик → Код → Запись макроса;

– в появившемся окне Запись макроса в строке Имя макроса укажите имя макроса Многоразовые_таблицы.

– укажите, что макрос предназначен только для текущего документа, т.е. в строке Сохранить в, из раскрывающегося списка, выберите Эта книга. В строке Описание запишите, кто автор макроса, дату создания и что делает макрос, например – 25.06.10 г., Сидоров З.С., проектирует многоразовую таблицу;

– чтобы выполнить макрос с клавиатуры с помощью сочетания клавиш, введите соответствующую строчную или прописную букву в поле Сочетание клавиш. Назначьте макросу сочетание клавиш Ctrl+w;

– для начала записи макроса нажмите кнопку [OK].

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

5. Нажмите кнопку Относительная ссылка в группе Код.

6. Спроектируйте таблицу следующего вида:

Графа 6 содержит формулу: = (Графа 3*Графа 4) /100, а графа 7 – = (Графа 3*Графа 5)/100.

7. Снимите блокировку с ячеек с переменной информацией (информация в которых может изменяться). Для этого:

– выделите ячейки с переменной информацией (диапазон ячеек С4:Е12);

– выполните команду Главная → Ячейки → Формат. В ниспадающем меню выберите Форматячейки, затем в появившемся окне перейдите на вкладку Защита, где снимите флажок у строки Защищаемая ячейка и нажмите Ok.

8. Закрепите области командой Вид → Окно → Закрепить области, предварительно выделив ячейку, стоящую на пересечении первого столбца после боковика (столбец С) и первой строки после строки с нумерацией столбцов (4 строка), т.е. выделяем ячейку С4.

9. Защитите весь лист, выполнив команду Главная → Ячейки → Формат → Защитить лист. В появившемся окне установите флажки в строках: защитить лист и содержимое защищаемых ячеек, изменение объектов, изменение сценариев и нажмите OK (пароль вводить необязательно).

10. Остановите запись макроса – Разработчик → Код→ Остановить запись.

11. Сохраните документ с макросом на диске под именем Макрос_Сидорова в вашей папке.

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

Создать макрос, запускаемый щелчком в области графического объекта, созданного согласно своему варианту – Вставка → Иллюстрации → Фигуры, и присваиваивающий соответствующий формат ячейкам таблицы созданной в документе Макрос Сидорова(задание 5.11.). Формат ячейкам присвоить в соответствии со своим вариантом.

В отчет скопировать все промежуточные результаты по созданию макроса.

Вариант 1:Формат Общий. Основные фигуры – Солнце.

Вариант 2: ФорматЧисловой. Основные фигуры – Облако.

Вариант 3: Формат Денежный. Основные фигуры – Кольцо.

Вариант 4: Формат Финансовый, Основные фигуры – Восьмиугольник.

Вариант 5: Формат Процентный. Основные фигуры – Знак запрета.

Вариант 6: Формат Экспоненциальный. Основные фигуры – Сердце.

Вариант 7: Формат Дата. Основные фигуры – Крест.

Вариант 8: Формат Время. Основные фигуры – Овал.

Вариант 9: Формат Дробный. Основные фигуры – Молния.

Вариант 10: Формат Дополнительный. Основные фигуры – Улыбающееся лицо.

Контрольные вопросы

1. Что такое макрос?

2. Как произвести запись и сохрание макроса?

3. Макрос, записанный с абсолютными и относительными ссылками.

4. Как запустить макрос?

5. Как защитить лист?

6. Для чего нужна команда Безопасность макросов?

Решение задач в Excel с помощью средств «Поиск решения» и «Подбор параметра»

Теоретические сведения

Excel имеет большие возможности для работы с различными математическими средствами, позволяющими решать самые разнообразные инженерно-технические и научные задачи. Большинство из них не входят в базовый набор функций Excel, а подключаются дополнительно. Подключение осуществляется через кнопку Officeв меню кнопки Параметры Excel → Настройки. Выберите из меню строки Управление (нижний правый угол окна) Надстройки Excelи нажмите кнопку Перейти. В открывшемся окне выберите необходимые надстройки.

Основные надстройки, поставляемые вместе с пакетом Excel:

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

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

– Мастер подстановок. Автоматизирует создание формулы для поиска данных в таблице по названию столбца и строки и позволяет использовать поиск с параметром.

– Поиск решения. Используется для решения уравнений и задач оптимизации.

Средство Поиск решения.Запускается командой Данные → Анализ → Поиск решения. Элементы диалогового окна:

установить целевую ячейку –адрес ячейки с целевой функцией;

равной – значение, к которому стремиться целевая функция;

изменяя ячейки –адреса влияющих ячеек;

параметры –открывает окно для задания ограничений на значения влияющих ячеек.

Средство Подбор параметра.Запускается командой Данные → Работа с данными → Анализ «что-если» → Подбор параметра.

Практическая часть

Задание 5.12. Решить систему нелинейных уравнений с помощью средства Поиск решения.

(1)

Выполнение.

В основу метода решения системы нелинейных уравнений положено то, что геометрически решения системы (1) описывают точки пересечения прямой ( ) с окружностью ( ) радиуса равному . Решения заданной системы удовлетворяют и следующему уравнению:

(2)

Вместо системы (1) будем решать уравнение (2). Решений будет два.

Чтобы применить метод Поиск решения необходимо, предварительно, найти начальное приближение решений. Для этого построим таблицу значений левой части уравнения (2) по переменным х и у на интервале (– 1.7; +1.7) с шагом 0.3. Границы интервала взяты на основании того, что корни уравнения лежат внутри круга, радиус которого приблизительно равен =1.73.

Для построения таблицы выполняем:

1. В ячейки А2:А14 вводим значения х (в интервале [–1.7, 1.7]), а в ячейки В1:N1 – значения y в таком же интервале.

2. В ячейку В2 вводится формула =($A2^2+B$1^2-3)^2+(2*$A2+3*B$1-1)^2 –уравнение (2).

3. Копируем формулу ячейки B2 вдиапозон B2:N14.

В соответствии с формулой (2) за начальные значения х и y берутся значения в тех ячейках заполненного диапазона, где функция принимает наименьшие значения. Под значения первого корня отводим ячейки А16:В16, а А17:В17 – под значения второго корня.

Для системы (1), в соответствии с полученной таблицей первое минимальное значение 0,4325. В ячейку А16мы вводим 1.3 – значение x, в В16 – 1.4 – значение y. В ячейку С16 вводим формулу =(А16^2+В16^2-3)^2+(2*A16+3*B16-1)^2.

Открываем окно Поиска решенийи устанавливаем: Целевая ячейка – $C16; Изменяя ячейки – $A16:$B16; установить параметр – Минимальному значению. Нажимаем кнопку Выполнить.

Значение корней уравнения появятся в ячейках А16 и В16. Второй корень находим аналогично, взяв следующее наименьшее значение 0,08.

Задание 5.13. Найти корни кубического уравнения (полинома) с одним неизвестным с помощью средства Подбор параметра.

Выполнение.

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

1. Введем в ячейку A2 значение –1 , а в A3 – значение: – 0.8.

2. Используя маркер заполнения, заполним ячейки до А12.

3. В ячейку B2 вводим формулу: = A2^3 – 0,01*A2^2 – 0,7044*A2 + 0,139104.

4. Заполняем диапазон B3:B12.

5. По полученным значениям строим график заданного полинома.

Мы увидим, что для нашего случая полином меняет знак на интервалах [-1,-0.8], [0.2, 0.4] и [0.6, 0.8], т.е. пересекается с осью x. Интервалов три – столько корней имеет уравнение третьей степени. Корни локализованы.

Теперь зададим точность нахождения значений корней. На вкладке Office → Параметры Excel → Формулы → Параметры вычислений задаем относительную погрешность 0,00001 и предельное число итераций 1000 (число последовательных приближений).

Отводим на новом рабочем листе ячейку С2 под первый корень, соответственно ячейки C3 и C4 под второй и третий корни полинома.

Корни будем находим методом последовательных приближений. Поэтому в ячейку С2 вводим сначала значение, являющееся первым приближением к искомому корню. В нашем случае возьмем первый отрезок и в нем среднее значение, т.е. – 0,9. Соответственно в ячейки С3 и С4 вводим приближенные значения для второго и третьего корней: +0,3 и +0,7.

Для нахождения корня с помощью Подбора параметра уравнение надо представить в таком виде, чтобы его правая часть не содержала переменную. В нашем примере этого не требуется. Отводим ячейку D2 под функцию, для которой ведется поиск первого корня. Причем вместо неизвестной x у этой функции должна указываться ссылка на ячейку, отведенную под искомый корень. Таким образом, в ячейку D2вводится формула:

= C2^3 – 0,01*C2^2 – 0,7044*C2 + 0.139104.

Копируем эту формулу в ячейки D3 и D4 для второго и третьего корней полинома. С помощью инструмента Подбор параметранаходим первый корень:

1. Выбираем команду Данные → Работа с данными → Анализ «что-если» → Подбор параметра. На экране появится диалоговое окно.

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

3. В поле Значение вводим 0 – значение из правой части уравнения.

4. В поле Изменяя значение ячейки введем С2 –ссылка на ячейку, отведенную под первый корень.

5. Нажимаем ОК.

Получим окно с результатами:

Закрыв окно, найденное приближенное значение корня помещается в ячейку D2. В данном случае оно равно –0,92034.

Аналогично, повторив действия 1–5 для каждого из оставшихся корней, в ячейках D3 и D4 находим их значения. Соответственно, они равны 0,21021 и 0,72071.

Содержание отчета

Отчет должен содержать тему, ответы на контрольные вопросы, выполненные задания своего варианта с промежуточными вычислениями.

Контрольные вопросы

1. Что такое Надстройки и где они находятся в Excel 2007?

2. Для чего используется Пакет анализа?

3. Как подключить пакет Поиск решения?

4. Рассказать о структуре окна Поиск решения.

6. Как выбираются границы интервалов значений х и у при нахождении корней уравнений используя Поиск решения?

7. Как выбрать начальное приближение неизвестных х и у?

8. Почему в окне Поиск решения нужно установить параметр Минимальное значение?

9. Для какой цели вводится Целевая ячейка?

10. Что находят с помощью средства Подбор параметра.

11. Как задать точность вычислений?

12. Как отделить интервалы, в которых находятся корни кубического уравнения, с помощью построения графика?

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

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

Вариант 1.	Вариант 2.	Вариант 3.
Вариант 4.	Вариант 5.	Вариант 6.
Вариант 7.	Вариант 8.	Вариант 9.
Вариант 10.