ЛАБОРАТОРНОЙ РАБОТЫ. Цель работы получить навыки работы по созданию, редактированию и расчетам с помощью электронных таблиц
 Скачать 1.33 Mb.
|
|
Вариант 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. |