- •Оглавление
- •Введение
- •3.2.Элементы окна программы и окна рабочей книги Файл Element
- •1. Открыть файл Element.
- •2. Найти на экране все элементы окна, которые обозначены на рис.1.
- •3. Записать, в таблицу на рабочем листе "Элементы" их названии и выучить назначение каждого элемента, попробовать его в действии.
- •3.3. Операции по изменению структуры рабочей книги Файл Rabbook 1
- •3 А д а н и е 1. Добавить новый рабочий лист в открытую рабочую книгу "Rabbook1", дли этого:
- •3 А д я н и е 5. Вызвать на экран список всех рабочих листов, для этого:
- •3 А д а н и е 7. Переименовать лист "Лист 18", присвоив ему в качестве нового имени слово "Февраль", для этою:
- •3 А д а н и е 12. Выделить все листы рабочей книги, для этого:
- •3 А д а н и е 13. Отменить выделение всех листов:
- •3 А д а н и е 15. Выделить несколько рабочих листов не например листы Лист 1, Лист 3, Лист 5.
- •Файл Rabbook2
- •3.4. Способы перемещения активной клеточки по рабочему листу Файл Move
- •3 А д а н и е. На рабочем листе с именем "Поле" найти ячейку, в которую записано контрольное слово.
- •3.5. Правила ввода данных в ячейки таблицы Файл Vvod
- •3.6 . Правила копированиям размножения данных Файл Сору
- •3.7. Способы поиска и устранения ошибок при организации вычислений Файл Error
- •3.8. Форма тиров ание я чеек и данных, которые они содержа т Файл Ramka
- •1. Открыть файл Ramka.
- •2. Создать таблицу, образец которой приведен ниже.
- •3.9. Создание собственных списков автозаполнения Файл Spisok
- •3.10. Добавление примечаний к ячейке электронной таблицы
- •3.11. Использование рациональных способов ? копирования и размножения данных при составлении стандартных таблиц Файл Calendar
- •3 А д а и и е 1. Создать заготовку для набора карманных календарей.
- •3 А д а н и е 3. На листе "Задание" создать три календари используя шаблон, подготовленный на листе "Пример".
- •3.12. Раздельное копирование значений, формул, примечаний и форматов ячейки Файл Spets
- •1. Числа, выделенные жёлтым цветом, увеличить и 3 раза,
- •2. К числам, выделенным зелёным цветом, прибавить 43.
- •3. Числа, выделенные синим цветом, уменьшить в 5 раз.
- •3 А д а н и е 3. Открыть рабочий лист "Пример 3".
- •3.13.Относительные и абсолютные ссылки на ячейки Файл Есо
- •Стиль ссылок а1
- •3Адание1. Открыть лист "Пример", заполнить пустые ячейки в шести таблицах.
- •3Адание3. Заполнить пустые клетки колонки l.
- •3Адание4. Открыть рабочий лист "Прайс". Заполнить пустые клетки таблицы результатами вычислений.
- •Файл Kvadrat 1
- •Файл Kvadrat 2
- •3.14. Команды пункта меню данные: "текст по столбцам", "сортировка", "итоги" Файл Sort
- •3Адание.Открыть рабочий лист "Список". Убедиться, что список в колонке в неудобен для оценки деятельности работников
- •1. Подсчитать среднегодовую температуру для всех городов с точностью до шестого знака после запятой.
- •2. Расположить названия городов мира по возрастанию среднегодовых температур.
- •Файл Маpk
- •Выделить одновременно: на рабочем листе "Задание" строку № 10, столбец н, блок ячеек а16:в19, блок ячеек f13:g16 и на рабочем листе "Маркировка" блок ячеек но32001:11032008.
- •Ввести во все выделенные ячейки число 7.
- •На листе "Задание" в ячейке в12 подсчитать сумму всех введённых чисел.
- •3.15. Вычисления с спользованием циклических ссылок Файл Itog
- •3.16. Построение графиков и поверхностей Файл Diagrama
- •Открыть рабочий лист "Задание 1".
- •По данным, приведённым в таблице на рабочем листе "Пример", построить графи со следующими параметрами:
- •3.17. Построение гистограмм с накоплением Файл Term
- •3.18. Создание и применение макросов Файл Makros
- •2.3. Проверить работу макроса ЧислаНомергруппы:
- •3Адание4. Записать макрос для последовательного введения в ячейки рабочего листа цветных ячеек.
- •3Адание5. Написать макрос для введения в ячейки рабочего листа таблицы с данными.
- •5.2. Проверить работу макроса БланкНомергруппы.
- •3Адание6.
- •6.1. Записать макрос для полной очистки рабочего листа "Макрос" - всего, что появится после выполнения четырёх ваших макросов.
- •3Адание7.Создать кнопку для запуска макроса ЧислаНомергруппы.
- •3Адание8. Создать кнопки для запуска всех макросов на рабочем листе "Макросы".
- •3Адание9.
- •3Адание10.
- •3Адание11.
- •3.19. Подпрограмма мастер функций Файл Master
- •3Адание1. Открыть рабочий лист "Пример". И ячейку f3 записать функцию для определения порядкового номера дня недели, дата которого записана в ячейку f4.
- •3Адание2. В ячейке f6 определить сумму взноса в месяц (грн) для погашения взятого кредита в размере 1 млн под месячную ставку, равную 0,75%, при ежемесячных платежах в течение 12 месяцев.
- •3Адание3.1. В ячейку f11 записать, число, которое будет Медианой - числом одновременно больше одной половины чисел и меньше второй половины из блока ячеек н12:f12.
- •3Адание3.2. В ячейке f14 подсчитать число перестановок из 1000 по 3.
- •3Адание3.4. (Контрольное). Открыть рабочий лист "Слово". Сосчитать, сколько раз каждое из слов записано в блок ячеек в16:135.
- •3Адание4.1. Открыть рабочий лист "Пример". В ячейку f16 записать количество символов, которое содержится в следующей фразе:
- •3Адание4.2. Открыть рабочий лист "Сцепить". В колонке f объединить данные колонок с,d,e так, как показано в следующей таблице:
- •3Адание6.3. (Контрольное). Подсчитать суммы зарплат работников 3-го отдела.
- •3.20. Математическая функция суммпроизв Файл Sumproiz
- •1.1. Подсчитать выторг каждого продавца. Результаты поместить в строке 15.
- •1.2. Подсчитать пять сумм:
- •3.21. Команда "частичная сумма"
- •1. Сервис V Мастер V Частичная сумма.
- •3.22. Логическая функция если Файл Nalog
- •Файл Footboll
- •3.23. Условное форматирование ячеек Файл Uslov
- •3Адание 1. На листе "Задание" сделайте так, чтобы:
- •3,24. Статистическая функция "счёту Файл Probel
- •3.25. Статистическая функция "частота" Файл Chast
- •3.26. Матема тическая функция мумнож
- •Файл Matriza
- •3.27. Функциягпр(впр)изкатегории "ссылки и ма ссивы"
- •3.28. Работа с базами данных Файл Fillr
- •3.29. Сводные таблицы Файл Svod
- •Порядок построения "Сводной таблицы"
- •Последовательность действий при выполнении задания:
- •2. Выбрать Формулы → Вычисляемое поле.
- •2. Выбрать Параметры поля.
- •3.30. Команда "подборпараметра" Файл Podbor
- •3.31. Финансовые функции Файл Finans
- •3 А д а н и с 4. На рабочем листе "Пример пз" разобрать решение следующей задачи:
- •3.32. Финансовые функции для расчета амортизации Файл Amort
- •3 А д а н и е 1. На рабочем листе "Расчёт" подготовлены
- •3.33. Команда "поиск решения"
- •Файл Фарби
- •Файл Gruz
- •Файл Kuxni
- •Файл grafik
- •Файл Capital
- •Файл Trans
- •Файл Nabor
- •Часть 1
- •Часть 2
3.33. Команда "поиск решения"
Одним из важнейших достоинств программы Excel является то, что она предоставляет пользователю, не имеющему серьёзной математической подготовки, возможность находи ответы к задачам, решение которых, например, сводится к численному решению нелинейных уравнений. Пользователь, изучивший программу Excel, решает подобные задачи путём точного выполнения инструкций программы.
Входящая в состав Excel, команда Поиск решения, позволяет создавать расчетные таблицы для решения многофакторных задач, которые часто встречаются в экономических, финансовых, маркетинговых и прочих вычислениях. Несмотря на разные формулировки проблем и их принадлежность различным сферам человеческой деятельности, последовательность действий при нахождении решения остаётся постоянной, а именно:
1 Составление расчетной таблицы, в которой должны быть:
• ячейки для записи начальных данных;
-
ячейки для записи формул;
-
ячейки для вывода результатов расчетов.
2. Введение в ячейки таблицы формул и исходных данных. (Нужно заметить, что это наиболее cложный этап решения задачи, поскольку требует от пользователя знаний возможностей программы Excel и понимания сути задачи, которая решается).
3. Заполнение диалогового окна команды Поиск решения, в котором указывают:
-
указывают адрес целевой ячейки;
-
указывают адреса изменяемых ячеек;
-
вводят ограничения на значение данных в изменяемых ячейках;
-
дают команду Выполнить.
Если решение для заданных условий существует, программа его найдет и в соответствующие ячейки запишет результаты.
Если программа сообщит, что решение не может быть найдено, необходимо изменить либо ограничения, либо исходные данные и запустить программу на выполнение еще раз.
При создании таблиц для использования команды Поиск решения необходимо понимать значение трех основных терминов: целевая ячейка, изменяемые ячейки и ограничения.
-
Целевой назначается та ячейка, в которую программа после расчетов и перебора возможных вариантов решений введет искомое значение.
-
В изменяемых ячейках программа будет подбирать такие значения переменных величин, при которых в целевой ячейке окажется требуемое (максимальное, минимальное или заданное) значение искомой величины;
-
Ограничения - это условия, которым должны удовлетворять значения в изменяемых ячейках.
Ниже разобрано решение нескольких прикладных задач.
Файл Фарби
Задача 1. Определить, какое наибольшее количество контурных карт четырёх стран можно раскрасить, используя кpacки четырёх цветов, при условии, чтобы всех карт было одинаков количество, если известны количество каждой краски на складе, и их расход на каждую карту (рис. 1).
Рисунок 1
Последовательность действий при решении задачи:
1. Создать расчетную таблицу.
• Открыть рабочий лист Краска 1. Там набрана таблица исходными данными (рис. 1). К этой таблице нужно добавить ячейки для подсчета:
-
расхода каждой из красок,
-
количества раскрашенных карт каждой страни,
-
количества раскрашенных карт всех стран. Место их расположения может быть любое. Но лучше размещать эти ячейки вместе с исходными данными тогда будут более понятны формулы и ограничения.
• Преобразовать таблицу, приведенную на рис. 1, в таблицу, приведенную на рис. 2, путем добавления одного столбика (D) и одной строки (4). Сделайте это на рабочем листе Краска 2, предварительно скопировав туда расчётную таблицу. В блок ячеек D5:D8 в дальнейшем планируем заносить подсчет затрат красок, а в блок ячеек Е4:Н4 - количество раскрашенных карт каждой страны. В ячейке D4 планируем получать общий результат.
Рисунок 2
2. Ввести формулы.
Открыть рабочий лист Краска 3 ,скопировать туда расчетную таблицу с листа Краска 2.
В блок ячеек D5:D8 ввести формулы, по которым будет возможно подсчитать общую затрату каждой краски.
Для ячейки D5, в которой должна подсчитываться затрата красной краски, число будет состоять из четырех составляющих: Е4*Е5 - затрата красной краски на корту № 1, F4:F5 – затрата красной краски на карту №2, G4:G5 – затрата красной краски на карту №3, H4*H5 – затрата красной краски на корту №4, поэтому расчет будет производится
по такой формуле:
В ячейке D5 =E4*E5+F4*F5+G4*G5+H4*, (1)
• Формула (1) записана в таком виде для наглядности (рис.2).
На практике, для удобства введения формулы в ячейку и для быстрого размножения формулы на другие ячейки , формулу (1) целесообразно заменить следующей:
=СУМПРОИЗВ($E$4:$H$4; E5:H5).
• Аналогично для других ячеек (рис.3):
В ячейке D6 = СУМПРОИЗВ(($E$4:$H$4;E6:H6), (3)
В ячейке D7 =СУМПРОИЗВ(($E$4:$H$4;E7:H7), (4)
В ячейке D8 =СУМПРОИЗВ(($E$4:$H$4;E8:H8), (5)
Рисунок 3
• Для того чтобы в ячейке D4 определять общее количество разрисованных контурных карт, нужно просуммировать количество карт из блока ячеек Е4:H4 по формуле (6):
= СУММ(Е4:Н4), (6)
3. Заполнить диалоговое окно (рис. 4).
• Вызвать диалоговое окно команды Поиск решения путём выбора: Сервис v Поиск решения.
В этом окне нужно:
-
Указать адрес целевой ячейки (ранее определено, такой ячейкой будет ячейка D4).
-
Выбрать одно из трех возможных значений для целевой ячейки. Из условия задачи известно, что мы ищем максимальное количество карт, поэтому следует активизировать опцию Максимальное значение
-
Указать адрес тех ячеек, изменяя значения которых команда Поиск решения
будет подбирать максимальное значение и целевой ячейке - это ячейки Е4, F4, G4, Н4. Ввести ограничения на расчеты. Их будет три:
♦ Первое ограничение: общий вес каждой краски, которая будет израсходована на раскрашивание четырёх карт, не должен превышать вес краски, которая есть на складе. Сжато это условие можно записать следующим образом:
$D$5:$D$8<=$C$5:$C$8.
♦ Второе ограничение: количество карт не может бить отрицательным числом, а
записать это условие можно так: $E$4:$H$4>=0.
♦ Третье ограничение: количество контурных карт – целое число.
Запись ограничений выполняется после нажатия на кнопку Добавить, редактирование ограничения выполняется после маркирования записи ограничения и нажатия на кнопку Изменить. Удаление ограничения выполняется после маркирования записи ограничения и нажатия на кнопку. Удалить. 4. Нажать на кнопку. Выполнить и дождаться, когда ячейки D4, D5:D8 и Е4:Н4 заполнятся числами (рис. 5).
Рисунок 5
Что делает в это время команда Поиск решения? – Заполняет числами блок ячеек Е4:Н4, подсчитывает по формулам затрату красок, следит за тем, чтобы числа в ячейках D5:D8 восходили соответствующие им числа в ячейках С5:С8, находит такие числа в ячейки Е4:Н4, чтобы значение в ячейке D4 было максимальное. Таким образом, получается искомый результат.