 ПОЗНАВАТЕЛЬНОЕ Сила воли ведет к действию, а позитивные действия формируют позитивное отношение Как определить диапазон голоса - ваш вокал Игровые автоматы с быстрым выводом Как самому избавиться от обидчивости Противоречивые взгляды на качества, присущие мужчинам Вкуснейший "Салат из свеклы с чесноком" Натюрморт и его изобразительные возможности Применение, как принимать мумие? Мумие для волос, лица, при переломах, при кровотечении и т.д. Как научиться брать на себя ответственность Зачем нужны границы в отношениях с детьми? Световозвращающие элементы на детской одежде Как победить свой возраст? Восемь уникальных способов, которые помогут достичь долголетия Классификация ожирения по ИМТ (ВОЗ) Глава 3. Завет мужчины с женщиной
Оси и плоскости тела человека - Тело человека состоит из определенных топографических частей и участков, в которых расположены органы, мышцы, сосуды, нервы и т.д.
| Поиск решения, оптимизация
Задача оптимизации в общем случае состоит в следующем: требуется найти максимум (минимум) функции y = f (x1, x2, … , xn) при следующих ограничениях на аргументы: x1, x2, … , xn.
g3 (x1, x2,…, xn) ≤ bm Функции g1, g2, … , gm – данные функции от m аргументов, b1, b2, … , bm - заданные числа. Набор x10, x20, …, xn0 является решением задачи оптимизации, если он удовлетворяет соотношению (**) и f (x10, x20, …, xn0) ≥ f (x1, x2, … , xn), где x1, x2, … , xn – любой набор, удовлетворяющий также соотношению (**). Функцию y = f (x1, x2, … , xn) называют целевой функцией. Частным случаем задачи (**) является задача линейного программирования. Доказано, что задача линейного программирования может иметь единственное решение, бесконечно много решений или не иметь решений вовсе. Задача линейного программирования сводится к задаче оптимального распределения ресурсов. Для решения задачи оптимизации в MSE используется инструмент «Поиск решения». В окне «Поиск решения» в поле «Установить целевую ячейку» указывается ссылка на ячейку с целевой функцией. В поле «Изменяя ячейки» устанавливаются ссылки на ячейки, которые отведены под переменные целевой функции. В поле «Ограничения» вводятся ограничения. Кнопка «Выполнить» завершает подготовительные операции и приводит к решению задачи.
РАБОТА СО СПИСКАМИ КАК С БАЗОЙ ДАННЫХ Создание базы данных Excel может работать как с простыми и небольшими по размерам, так и с более сложными и занимающими большой объем дискового пространства базами данных (БД). В Excel БД – это просто список, состоящий из одного или более столбцов. Выделяют следующие элементы списка (рис. 14): · запись (отдельная строка); · поле (отдельный столбец); · имена полей (отдельные ячейки в первой строке списка); · строка заголовков (вся первая строка списка).
Рисунок 2 – Элементы списка.
Полуфункциональный список или БД в Excel состоит из диапазона базы данных, диапазона критериев, диапазона для извлечения: · диапазона базы данных – область, в которой хранятся данные списка (именуется «База_данных»); связанные друг с другом данные записываются в отдельные строки, каждому столбцу соответствует свое поле списка с уникальным именем поля; · диапазона критериев – область, в которой задаются критерии поиска информации; здесь указываются имена полей и отводится область для записи условий отбора; · диапазон для извлечения – область, в которую копируют выбранные из списка данные. Начинать создание БД удобнее с создания обычной таблицы Excel. Преобразование обычной таблицы в БД осуществляется путем именования диапазона рабочего листа, содержащего исходную таблицу («Вставка» → «Имя» → ввод имени → «База_данных» → «Добавить») Ввод данных в существующую БД осуществляется следующими способами: · непосредственный ввод в рабочий лист; если диапазону ячеек списка присвоено имя, лучше всего вставлять новые строки или ячейки между существующими записями списка, т.к. если вставлять ячейки после последней записи списка, то они окажутся вне именованного диапазона и его необходимо будет переопределять; · использование автоматически создаваемой формы данных (рис. 15): в открывающемся списке имен выбрать «База_данных», дать команду «Данные» → «Форма», нажать кнопку Добавить и ввести в соответствующие поля формы, нажать кнопку Закрыть.
Рисунок 3 – Окно Форма.
Сортировка данных в БД Чтобы выполнить сортировку списка, нужно выделить соответствующий диапазон ячеек, дать команду «Данные» → «Сортировка». В появившемся окне задаются ключи сортировки (рис. 16). Порядок сортировки может быть «По возрастанию» и «По убыванию». С помощью окна Сортировка диапазона можно упорядочить список по любому количеству полей. Это осуществляется путем последовательных сортировок. Для того, чтобы результаты предыдущих сортировок не терялись, следует начинать с ключей самого нижнего уровня. Чтобы воспользоваться собственным порядком сортировки, его нужно предварительно создать: · вызвать окно Параметры с помощью команды «Сервис» → «Параметры»; · перейти на вкладку Списки; · в поле Элементы списка ввести элементы, после каждого элемента нажимать клавишу ввода; · после ввода всех элементов нажать кнопку Добавить и закрыть окно Параметры. Чтобы отсортировать данные в особом порядке, требуется в окне Сортировка диапазона нажать кнопку Параметры, а затем в раскрывающемся списке Порядок сортировки по первому ключу выбрать подходящий вариант.
Рисунок 4 – Задание параметров сортировки.
Поиск, фильтрация и редактирование в списках и БД Поиск производится на основе задаваемых пользователем критериев – требований, налагаемых на информацию. Результатом фильтрации является временное скрытие записей, не удовлетворяющих критериям. Поиск и фильтрацию данных можно проводить с помощью формы данных (кнопка Критерии), автофильтра (команда «Данные» → «Фильтр» → «Автофильтр») и расширенного фильтра (команда «Данные» → «Фильтр» → «Расширенный фильтр»). Существуют следующие критерии поиска: · поиск точного соответствия (можно использовать различные операции сравнения =, >, >=, <, <=, <>); · поиск близкого соответствия с использованием образца (используются символы: «*» заменяет любое количество любых символов, «?» заменяет один любой символ); · поиск соответствия с использованием множественного критерия с операциями И и ИЛИ.
Построение диаграмм Диаграмма – представление числовых данных в графическом виде. На диаграммах легко проглядывается взаимосвязь различных процессов и тенденции к изменению данных. Диаграммы в MSE можно создать с помощью Мастера диаграмм. Мастер диаграмм вызывается из меню «Вставка» → «Диаграмма» или с помощью кнопки Диаграмма на стандартной панели инструментов. Окно Мастера диаграмм представлено на рисунке 19. Для построения диаграммы необходимо: · ввести данные, на основе которых строится диаграмма; · выделить данные, включая и заголовки столбцов, и названия строк; · запустить Мастер диаграмм; · выбрать тип диаграммы и вид диаграммы (тип и вид выбираются на основании данных в таблице); · указать, как расположены данные в таблице; · установить параметры диаграммы.
Рисунок 5 – Окно Мастера диаграмм.
Ниже приведен список универсальных рекомендаций, которые необходимо учитывать при создании любой эстетически привлекательной диаграммы: · декоративные элементы не должны отвлекать от рядов данных или загромождать диаграмму; · ось времени диаграммы (если она есть) должна быть направлена слева направо; · при выборе заливки проверьте, печатная версия диаграммы (предварительный просмотр); · избегайте по возможности вертикальной и наклонной ориентации текста; · убедитесь, что линии сетки не загромождают диаграмму; · в нескольких диаграммах для одних и тех же данных должны использоваться одинаковые цвета, шрифты, числовые форматы, набор и размеры элементов и т.д. Номер варианта выбирается согласно номера в списке и выдается преподавателем. Пример выполнения практического задания приведен в приложении А, а также в файле «Пример.xls» Каждое новое задание оформлять на новой вкладке. При оформлении отчета студент должен знать ответы на все контрольные вопросы и в письменном виде дать ответ на вопрос согласно своего варианта. Все работы должны иметь электронную копию. Вариант 1 Задание 1 Создать таблицу расчета прибыли фирмы, произвести расчеты суммарных доходов, расходов и прибыли, произвести пересчет прибыли в условные единицы по курсу:
Задание 2 Дано натуральное число n. Определить, является ли оно четным. Определить, его факториал.
Задание 3 Найти двоичное представление для чётных совершенных чисел вида 2(p-1)*((2p)-1) Вариант 2 Задание 1 Определить план выгодного производства, т.е. какой продукции и сколько необходимо произвести, чтобы общая прибыль от реализации была максимальной.
Задание 2 Даны 4 точки заданные координатами. Является ли данная фигура трапецией.
Задание 3 Определить k - кол-во трёхзначных чисел сумма цифр которых равна a. Вариант 3 Задание 1
Задание 2 Даны числа M,N и двумерный массив M*N. Некоторый элемент массива назовем седловой точкой, если он яв-ся одновременно наименьшим в своей строке и наибольшим в своём столбце. Напечатать координаты какой-нибудь седловой точки.
Задание 3 Для участия в конкурсе из класса в n человек требуется выбрать m человек.Сколькими способами это можно сделать. Вариант 4 Задание 1 Производство маргарина в регионе характеризуют следующие данные:
2000 год – базисный. Рассчитать в MS Eхcel абсолютные приросты цепные, абсолютные приросты базисные, темпы роста цепные, темпы роста базисные. Использовать следующие формулы:
Задание 2 Даны стороны треугольника : a,b,c. Вычислить косинус углов по теореме косинусов : sqr(c)=sqr(a)+sqr(b)-2ab*cos(alfa)
Задание 3 Дан текст. Определить в нем кол-во слов 'кот'. Вариант 5 Задание 1 Сравнить доходность размещения средств организации, положенных в банк на один год, если проценты начисляются m раз в год, исходя из процентной ставки j = 9,5 % годовых (рис. 14.9); по результатам расчета построить график изменения доходности инвестиционной операции от количества раз начисления процентов в году (капитализации).
Выясните, при каком значении j доходность (при капитализации m = 12) составит 15 % (Используется подбор параметра). Краткая справка. Формула для расчета доходности Доходность = (1 +j/m)m - 1.
Задание 2 Можно ли заданное натуральное число M представить в виде суммы двух квадратов натуральных чисел.
Задание 3 Посчитать сумму цифр всех целых чисел 1 до n. Вариант 6 Задание 1 Используя файл «План производства», определить план выгодного производства, т.е. какой продукции и сколько необходимо произвести, чтобы общая прибыль от реализации была максимальной.
Задание 2 Вывести список симметричных нечётных трехзначных чисел (например 353 или 757).
Задание 3 Найти остаток от деления числа, записываемого с помощью k семёрок, на число а (k и a -заданные натуральные числа). Вариант 7 Задание 1 По следующим данным:
Найти значение Постройте на одном графике y и
Задание 2 Определить, какая цифра находится в позиции n числовой последовательности. 1011112131415...979899-подряд выписаны все двузначные числа.
Задание 3 Вывести список шестизначных "счастливых" чисел (сумма первых трёх цифр равна сумме трёх последних), кратных семи Вариант 8 Задание 1
Задание 2 Определить кол-во слов в строке. Слова отделяются одним или несколькими пробелами.
Задание 3 Найти натуральные числа из диапазона (0, n) у которых количество делителей является произведением двух простых чисел. Вариант 9 Задание 1 Используя файл «План производства», определить план выгодного производства, т.е. какой продукции и сколько необходимо произвести, чтобы общая прибыль от реализации была максимальной.
Задание 2 Вывести 5 шестизначных чётных чисел, делящихся без остатка на сумму своих цифр.
Задание 3 Найти сумму всех совершенных чисел меньше m. Справка: Совершенными называются числа сумма делителей включая 1, которых равна самому числу, делители не должны быть равны самому числу. Вариант 10 Задание 1 Создать таблицу расчета прибыли фирмы, произвести расчеты суммарных доходов, расходов и прибыли.
Выясните, при каком значении зарплаты прибыль будет равна 500000 р. (используйте режим Подбор параметра).
Задание 2 Определить можно ли из символов входящих в строку а, составить строку с.
Задание 3 Найти в одномерном числовом массиве элемент, который наибольшее кол-во раз повторяется в массиве. Вывести это число и кол-во его вхождений в массив. Вариант 11 Задание 1 Создать «Ведомость учета остатков продуктов и товаров на складе». Текстовую часть документа создайте в текстовом редакторе MS Word, таблицу учета продуктов и товаров создайте в MS Excel, проведите расчеты и вставьте в текстовый документ.
Задание 2 Даны натуральные числа N и i. Расчитаь формулу
Задание 3 Вывести введенные цыфры (от 0 до 99) словами Вариант 12 Задание 1
С помощью инструмента Подбор параметра ответить: на сколько нужно изменить количество кур, если всего нужно потратить 3500 руб.
Задание 2 Любую сумму n больше 7 можно выплатить без сдачи трешками и пятерками т.е. для n>7 найти все целые неотрицательные а и b, что 3а+5b=n
Задание 3 Два натуральных числа называются дружественными, если каждое из них равно сумме всех делителей другого, кроме самого этого числа. Найти все пары в диапазоне от 0 до n. Вариант 13 Задание 1 Используя файл «План производства», определить план выгодного производства, т.е. какой продукции и сколько необходимо произвести, чтобы общая прибыль от реализации была максимальной.
Задание 2 Дан прямоугольный целочисленный массив размером N*N. Определить является ли данный массив магическим квадратом, т.е. сумма элементов в строках, столбцах и на главных диагоналях равна.
Задание 3 Найти все простые делители натурального числа N. Вариант 14 Задание 1 Фирма хочет накопить деньги для реализации нового проекта. С этой целью в течение пяти лет она кладет на счет ежегодно по 1250 $ в конце каждого года под 8 % годовых (рис.). Определить сколько будет на счете фирмы к концу пятого года (в MS Excel). Построить диаграмму по результатам расчетов. Выясните, какую сумму надо ежегодно класть на счет, чтобы к концу пятого года накопить 10 000 $.
Краткая справка. Формула для расчета
Задание 2 Определить самое короткое и самое длинное слово в строке введённой с клавиатуры.
Задание 3 Среди простых чисел, не превосходящих n, найти такое, в двоичной записи которого максимальное число единиц. Вариант 15 Задание 1 Сбор урожая в регионе характеризуют следующие данные:
2000 год – базисный. Рассчитать в MS Eхcel темпы роста цепные, темпы роста базисные, темпы прироста цепные и базисные, абсолютное значение 1% прироста. Использовать формулы:
Задание 2 Даны натуральные числа N и P. Расчитаь формулу
Задание 3 Даны действительные числа a, b, c (a≠0). Решить неравенство вида Вариант 16 Задание 1 Определить план выгодного производства, т.е. какой продукции и сколько необходимо произвести, чтобы общая прибыль от реализации была максимальной.
Задание 2 Даны натуральные числа a, b, c. Найти площадь треугольника с заданными сторонами.
Задание 3 Даны натуральные числа a, b, c, d, x1, y1, x2, y2. Найти площадь пересечения прямоугольника1 (с сторонами a и b и координатой верхнего левого угла x1, y1) и прямоугольника2 (с сторонами c и d и координатой верхнего левого угла x2, y2) Вариант 17 Задание 1 Создать «Акт о порче товарно-материальных ценностей». Текстовую часть документа создайте в текстовом редакторе MS Word, таблицу расчета стоимости товарно-материальных ценностей (ТМЦ) для списания создайте в MS Excel, проведите расчеты и скопируйте в текстовый документ.
Задание 2 Дано натуральное число n. Определить, сколько парных цифр в числе n. Чему равна сумма нечетных его цифр?
Задание 3 Даны натуральные числа n и m. Получить сумму m последних цифр числа n. Вариант 18 Задание 1
С помощью инструмента Подбор параметра ответить на следующие вопросы:
до какой цены на мясо надо поторговаться, чтобы расходы составили 4000 руб.
Задание 2 Дано натуральное число n. Определить, сколько цифр в числе n. Чему равна сумма его цифр?
Задание 3 Женщина шла на базар и разбила яйца, лежавшие у неё в корзине. Она сказала, что не знает сколько яиц у неё было, но когда она брала по 2,3,4,5 и 6 яиц то оставалось в остатке одно. Когда же она брала по 7 яиц, то ничего не оставалось в остатке. Сколько яиц могло быть в корзине. Вариант 19 Задание 1 Фирма собирается инвестировать проект в течение трех лет. Имеется два варианта инвестирования: 1-й вариант: под 12 % годовых в начале каждого года; 2-й вариант: под 14 % годовых в конце каждого года. Предполагается ежегодно вносить по 500 000 р. Определить, в какую сумму обойдется проект (рис.)
Выясните, какую сумму надо вносить ежегодно по каждому варианту инвестирования, чтобы общая сумма проекта составила 2 000 000 р.
Задание 2 Найти наибольшее число из диапазона (0, n) у которого наибольшее количество делителей.
Задание 3 Даны натуральные числа N и P. Расчитаь формулу
Вариант 20 Задание 1 По следующим данным:
Найти значение Постройте на одном графике y и
Задание 2 Дана строка слов разделенных пробелом, в конце строки точка. Поменять местами два центральных слова если их количество четно.
Задание 3 Задана линейная таблица, состоящая из целых чисел. Определить есть ли в этой таблице хотя бы одно число кратное k. Вариант 21 Задание 1
С помощью инструмента Подбор параметра ответить на следующие вопросы:
При каком значении курса доллара затраты станут ниже 150 долл.
Задание 2 Дано натуральное число N. Расчитаь формулу
Задание 3 Даны натуральные числа r, a, b, c. Найти разницу площадей круга (с радиусом r) и треугольника (с сторонами a, b , c) Вариант 22 Задание 1 Создать по образцу бланк счета для аренды автомобиля в MS Excel. Автомобиль использовался с 12.10.04 00:00 до 14.10.04 16:40. Тарифная ставка = 120 р./ч. Краткая справка. Для ввода даты используйте функцию «Сегодня». При вводе периода аренды автомобиля используйте формат «Дата», в котором присутствуют дата и время. Значение даты и времени представляют собой так называемые серийные числа, поэтому с ними можно работать как с обычными числами, например вычитать одну дату из другой, чтобы получить разность в днях.
Задание 2 Даны действительные числа a, b. Решить неравенство вида ax+b>0.
Задание 3 Дано натуральное число n. Вычислить Вариант 23 Задание 1 Открыть документ Mytabl_фамилия. Восьмому рабочему листу присвоить имя Таблица 8. Создать и заполнить таблицу по образцу.
Столбец Средний балл заполнить с помощью функции СРЗНАЧ (среднее значение по столбцам F, G, H). Выделите диапазон А2:I14 и присвойте ему имя База_данных. Вызовите окно Форма и добавьте новую запись в эту БД. Добавьте еще одну запись путем ввода в рабочий лист. Провести сортировку списка по фамилии (по возрастанию), по дате рождения (по убыванию). С помощью Автофильтра получить информацию о студентах, родившихся в период (06.10.1984;22.08.1985) и имеющих средний балл не ниже значения 4.
Задание 2 Дано натуральное число n. Определить, сколько цифр в числе n больше “5”. Найти первую и последнюю цифры числа n.
Задание 3 Даны действительные числа a, b, c. Найти корни уравнения вида Вариант 24 Задание 1 Открыть документ Mytabl_фамилия. Шестому рабочему листу присвоить имя Таблица 6. Для покупки автомобиля необходима сумма 200000 руб. Есть возможность взять 30-летнюю ипотечную ссуду со ставкой 8% годовых. При этом нужно сделать 20% взнос. Определить какую сумму нужно взять в банке, чтобы на руки вы получили требуемую сумму. Пусть в банке выдали х руб. (So). Первоначальный взнос (V) составляет 20%*х. Остается сумма S = So – V. Представим исходные данные, как показано на рисунке.
Таблица 6: «Решение частной задачи линейного программирования»
Задание 2 Даны n чисел. Определить являются ли они взаимно простыми т.е. имеют общий делитель отличный от единицы.
Задание 3 Два двузначных числа, записанных одно за другим образуют четырехзначное число, которое делится на их произведение. Найти эти числа. Вариант 25 Задание 1 Открыть документ Mytabl_фамилия. Десятому рабочему листу присвоить имя Таблица 10. Создать и заполнить таблицу по образцу. Требуется рассчитать надбавку к зарплате по следующему правилу. Размер надбавки зависит от размера оклада и стажа работы. Для сотрудников со стажем работы 5 лет надбавка равна 5% от оклада; для сотрудников со стажем работы 10 лет – 10% от оклада и т.д. Данные и формула для расчета приведены на рисунке 22 в строке формул.
Таблица 10: Таблица расчета повышения заработной платы по результатам аттестации. Окончательно заработную плату с учетом надбавки вычислить в отдельной таблице по формуле, приведенной на рисунке в строке формул.
Расчет заработной платы с учетом надбавки. Далее требуется добавить соответствующие столбцы и вычислить среднюю надбавку по каждому разряду, а также среднюю заработную плату с учетом надбавки по каждому разряду. Формулы для вычислений составить с использованием функции СРЗНАЧ. Построить диаграмму по столбцам: «Оклад», «Средняя надбавка», «Средняя заработная плата с учетом надбавки»
Задание 2 Вычислить в какой координатной четверти расположен треугольник образованный осями координат и прямой y=kx+b. Задание 3 Сколькими различных способами можно надеть на нить n бусин k цветов. КОНТРОЛЬНЫЕ ВОПРОСЫ 1. Каково назначение и основные функциональные возможности табличного процессора Microsoft Excel? 2. Назовите основные элементы окна программы Microsoft Excel и укажите их функциональное назначение. 3. Назовите элементы строки формул и укажите их назначение. 4. Какая информация отражается в Строке состояния Excel? 5. Какие операции можно выполнять с рабочими листами Excel? 6. Назовите и охарактеризуйте основные типы данных в ячейках электронной таблицы. 7. Что такое ссылка? Какие бывают ссылки? Их назначение? 8. Каковы основные функции маркера автозаполнения? 9. Перечислите основные типы функций, используемых в Microsoft Exce. 10. Какие средства Excel позволяют осуществлять автоматическое подведение итогов в электронной таблице? 11. Какие средства Excel позволяют проводить анализ и обработку данных электронной таблицы? 12. Что означает в Microsoft Excel понятие список или база данных? 13. Какие средства имеются в Excel для работы с базой данных? 14. Каково назначение инструмента Подбор параметра? 15. Каково назначение надстройки Поиск решения? 16. Каким образом в программе Microsoft Excel можно работать одновременно с несколькими документами? 17. Как выделить в электронной таблице смежные и несмежные ячейки, диапазоны (блоки) ячеек? 18. Как ввести данные в ячейку? Как зафиксировать ввод данных? 19. Как скопировать ячейку, блок ячеек, рабочий лист? 20. Как переместить ячейку, блок ячеек, рабочий лист? 21. Как переименовать рабочий лист? 22. Как удалить из рабочей книги рабочий лист? Как добавит новый рабочий лист? 23. Как объединить ячейки в электронной таблице? Как снять объединение ячеек? 24. Как нужно вводить числовые данные и даты, чтобы программа Microsoft Excel воспринимала их как текст? 25. Как отредактировать ранее введенные данные? 26. Как разбить текст ячейки на несколько строк? 27. Как отформатировать данные в ячейке? Как удалить формат ячейки? 28. Какие возможности предоставляет диалоговое окно Формат ячеек? 29. Как изменить ширину столбцов и высоту строк? 30. Как присвоить ячейке или диапазону ячеек собственное имя? 31. Как ввести в ячейку формулу? 32. Как можно скрыть столбцы и строки? Как показать скрытые столбцы и строки? 33. Как оформить таблицу Microsoft Excel рамками и заливкой? 34. Как убрать с экрана изображение сетки? 35. Как построить в электронной таблице диаграмму? 36. Как отредактировать построенную диаграмму? 37. Какими способами можно отсортировать данные электронной таблицы? 38. Какими способами можно защитить информацию в электронной таблице Microsoft Excel? |
g1 (x1, x2,…, xn) ≤ b1
g2 (x1, x2,…, xn) ≤ b2 (**)
, где 
- линейное уравнение регрессии. Для нахождения коэффициентов b0 и b1 используйте функцию ЛИНЕЙН (найдите информацию в справке).
.
.
.
