Раздел 5. Поиск решения
Значения в ячейках J4, H4, I4 – это, соответственно x1, x2 х3 .Так как это неизвестные параметры, то в эти ячейки вводим 0.
В диапазон ячеек А4:A8 вводим имеющиеся ограничения. В диапазон ячеек F8:H12 вводим данные о содержании продуктов в килограмме концентрата.
Выделяем ячейку В2, в строку формул вводим:
«=$G$4*F13+$H$4*G13+$I$4*H13»
Далее протягиваем маркер до ячейки В6.
В ячейку A19, так же вводим формулу:
«=$G$4*F17+$H$4*G17+$I$4*H17»
Ячейку A19 устанавливаем как целевую.
Выбираем Меню/ Сервис/Поиск решения. В появившемся диалоге вводим:
Рисунок 5.1 – Диалог «Поиск решения»
Выбираем «Выполнить». Сохраняем найденное решение. В результате получаем:
Рисунок 5.2 – Результаты поиска решения
Раздел 6. Фильтры.
Копируем список в Excel.
Создадим диапазон критериев, для этого копируем ячейки С1 (Ctrl+C) в ячейку А503 (вставить Ctrl+V). Ячейку L1 в ячейку В503. В результате получаем 1 строку диапазона, которая содержит имена полей, по которым будут задаваться условия.
Заполняем диапазон ячеек А503:В505 критериями для поиска, причем между критериями в строке связь по И, между строками – по ИЛИ.
Получим диапазон критериев:
Рисунок 6.1 – Диапазон критериев
Копируем ячейки A1, B1, F1, K1, N1, соответственно, в ячейки A509:E509 Выделяем любую ячейку списка, выполняем команду Данные/Фильтр/Расширенный фильтр.
Рисунок 6.2 - Диалог «Расширенный фильтр»
Нажимаем «ОК», и в ячейках A509: E509 будут выведены записи, удовлетворяющие заданным условиям:
Таблица 6.1 - «Результат Расширенного фильтра»
Раздел 7. Комплексное задание
1.Создаем таблицу, как указано в задании. Рассчитываем необходимые значения по формулам:
Отношение размера средней зарплаты к величине прожиточного минимума : «=B4/B3»
Отношение размера средней пенсии к величине прожиточного минимума: « =B5/B3»;
Минимум : «=МИН(B3:I3) »
Максимум: « =МАКС(B3:I3) »
Данные формулы копируются на все ячейки, в которых необходимо произвести вычисления.
Рисунок 7.1 Полученная таблица
По данным таблицы составляем две резанные круговые диаграммы(Меню Вставка\Диаграмма, или панель инструментов «Стандартная», значок ).
Исходные данные:
Рисунок 7.4 – Полученные диаграммы
2.Строим сводную таблицу. Для этого выделяем диапазон ячеек A1:K7, меню Данные/Сводная таблица. Выбираем создать таблицу на основе данных, находящихся: в списке или базе данных Microsoft Office Excel.Помещаем таблицу на существующий лист.
Рисунок 7.6 – Список полей сводной таблицы
В остальных случаях используем функцию «Сумма», установленную по умолчанию.
Рисунок 7.8 – Сводная таблица
4.Переходим в редактор Visual Basic: меню Сервис/Макрос/Редактор Visual Basic.
Выбираем форму из меню Insert\Form. Создаем на рабочей области формы необходимые элементы (Label – текстовая область, CommandButton - кнопка, ComboBox – выпадающий список, TextBox – текстовое поле). Свойства данных элементов настраиваются в панели Properties для каждого элемента формы в отдельности. Воспользуемся свойством Caption для UserForm1:
Рисунок 7.9 - панель Properties
Таким же образом настраивается заголовок для Label2. Для Label1 значение в строке Caption удаляем, так как в этой метки будет выводиться значение.
Макет формы примет вид:
Рисунок 7.10 – Макет формы
Вводим код для элементов управления:
Private Sub ComboBox1_Change()
Select Case ComboBox1.ListIndex
Case 0
Label1.Caption = s & Range("C3") & " Тыс.рублей "
Case 1
Label1.Caption = s & Range("C4") & " Тыс.рублей "
Case 2
Label1.Caption = s & Range("C5") & " Тыс.рублей "
Case 3
Label1.Caption = s & Range("C6") & " Тыс.рублей "
Case 4
Label1.Caption = s & Range("C7") & " Тыс.рублей "
End Select
End Sub
Private Sub TextBox1_Change()
End Sub
Private Sub UserForm_Initialize()
ComboBox1.AddItem " Величина прожиточного минимума в среднем на душу населения,в месяц ", 0
ComboBox1.AddItem " Среднемесячная начисленная заработная плата одного работника ", 1
ComboBox1.AddItem " Размер начисленных пенсий ", 2
ComboBox1.AddItem " Отношение размера средней зарплаты к величине прожиточного минимума ", 3
ComboBox1.AddItem " Отношение размера средней пенсии к величине прожиточного минимума ", 4
ComboBox1.ListIndex = 0
End Sub
Меню Insert/Module, в появившемся окне создаем макрос:
Private Sub Кнопка15_Щелчок()
UserForm1.Show
End Sub
Переходим в Microsoft Excel. С помощью панели инструментов «Формы» создаём кнопку на рабочем листе. После создания кнопки выбираем диалог «Назначить макрос объекту», выбираем:
Рисунок 7.11 – Диалог «Назначить макрос объекту»
Изменяем название кнопки..
В результате по нажатию кнопки «Button» будет появляться форма, в которой в зависимости от выбранной показателей, будет выводиться объем экспорта в 1993 году.
Рисунок 7.12 – Использование формы
ЗАКЛЮЧЕНИЕ
В курсовой работе было представлено лишь небольшое количество возможностей Microsoft Excel: использование функций, операции над матрицами, построение графиков и диаграмм, использование фильтров.
Однако и это позволяет понять, насколько незаменим этот пакет при обработке табличных данных, выполнении сложных вычислений с большими массивами чисел, построении диаграмм, печати финансовых отчетов.
ЛИТЕРАТУРА
Конспект лекций по дисциплине “Программные средства персональных компьютеров”
Джон Уокенбах “Microsoft Office Excel 2003. Библия пользователя”. Компьютерное издательство “Диалектика” Москва Санкт-Петербург Киев 2004.
Гр.107710 Моминов Б,Х