[ Федеральное государственное образовательное учреждение
высшего профессионального образования
«ФИНАНСОВАЯ АКАДЕМИЯ ПРИ ПРАВИТЕЛЬСТВЕ
РОССИЙСКОЙ ФЕДЕРАЦИИ»
КАФЕДРА « ИНФОРМАЦИОННЫЕ ТЕХНОЛОГИИ»
Р.М. Магомедов, М.М. Ниматулаев
Сборник упражнений и
заданий по Excel
Методические указания и задания к изучению дисциплины “Информатика” Для самостоятельной работы студентов всех специальностей всех форм обучения Москва 2009 Федеральное государственное образовательное учреждение высшего профессионального образования
«ФИНАНСОВАЯ АКАДЕМИЯ ПРИ ПРАВИТЕЛЬСТВЕ
РОССИЙСКОЙ ФЕДЕРАЦИИ»
Кафедра « Информационные технологии»
УТВЕРЖДАЮ
Ректор М.А. Эскиндаров «»2008г.Р.М. Магомедов, М.М. Ниматулаев Сборник упражнений и заданий по Excel Методические указания и задания к изучению дисциплины “Информатика” Для самостоятельной работы студентов всех специальностей всех форм обучения Рекомендовано Ученым советом по специальности «Математические методы в экономике»
(протокол № «» от « » 200 г.) одобрено кафедрой «Информационных технологий»
( протокол № «» от « » _ 200 г.) Москва УДК 004(078) ББК 32.81 я М Магомедов Р.М., Ниматулаев М. Сборник упражнений и заданий по Excel.
Методические указания и задания к изучению дисциплины “Информатика”.
Учебное издание для студентов всех специальностей всех форм обучения. М.:
Финансовая академия при Правительстве Российской Федерации, кафедра «Информационные технологии», 2009. – 70 с.
Рецензент: Е.А. Мамонтова, к.п.н., доцент.
В методическом пособии “Сборник упражнений и заданий по Excel” подобраны и систематизированы упражнения и задания по теме “Табличный процессор”, изучаемый в рамках дисциплины “Информатика”. В нем каждая из рассматриваемых тем сопровождается краткими теоретическими сведениями, подробным разбором примеров и перечнем заданий для самостоятельного решения.
При выполнении упражнений и заданий следует руководствоваться соответствующими методическими пособиями, разработанными на кафедре.
Пособие может быть рекомендовано для студентов очного, очно-заочного и заочного обучения всех специальностей.
Рамазан Магомедович Магомедов, Ниматулаев Магомедхан Магомедович Компьютерный набор Р.М. Магомедов, М.М. Ниматулаев Компьютерная верстка Р.М. Магомедов, М.М. Ниматулаев Формат 60x90/16. Гарнитура Times New Roman Усл. п.л. _ Изд. №. Тираж экз. и/или Отпечатано в Финансовой академии при Правительстве РФ Полное и частичное воспроизведение или размножение каким-либо способом допускается только с письменного разрешения Финансовой академии при Правительстве РФ © Финансовая академия при Правительстве РФ, Содержание:
1. Упражнения и задания по теме “Адресация. Относительная, абсолютная и смешанная”
Упражнение.
Задания для самостоятельного выполнения
2. Упражнения и задания по теме “Форматирование. Условное форматирование”
Упражнение.
Задания для самостоятельного выполнения
Упражнение (Задача аттестация студентов)
Задание для самостоятельного выполнения
3. Упражнения и задания по теме “Встроенные функции. Логические функции” Упражнение
Задания для самостоятельного выполнения
4. Упражнения и задания по теме “Встроенные функции. Финансовые функции”
Упражнение.
Упражнение.
Задания для самостоятельного выполнения
5. Упражнения и задания по теме “Встроенные функции. Функции ПРОСМОТР, ГПР, ВПР”
Упражнение
Упражнение
Задания для самостоятельного выполнения
6. Упражнения и задания по теме “Числовые последовательности”.................. Упражнение
Задания для самостоятельного выполнения
7. Упражнения и задания по теме “Приближенное вычисление пределов числовых последовательностей“
-3Упражнение
Задания для самостоятельного выполнения
8. Упражнения и задания по теме “Решение задач из линейной алгебры”........ Упражнение
Упражнение
Упражнение
Задания для самостоятельного выполнения
9. Упражнения и задания по теме “Построение диаграмм и графиков”............ Упражнение
Упражнение
Задания для самостоятельного выполнения
Упражнения и задания по теме “Таблицы подстановки”
10.
10.1 Упражнение
10.2 Упражнение
10.3 Задания для самостоятельного выполнения
Упражнения и задания по теме “Подбор параметра. Нахождения корней 11.
уравнения”
11.1 Упражнение
11.2 Задания для самостоятельного выполнения
Упражнения и задания по теме “Дифференцирование и интегрирование в 12.
Excel”
12.1 Упражнение
12.2 Задания для самостоятельного выполнения
12.3 Упражнение
12.4 Упражнение
12.5 Задания для самостоятельного выполнения
Упражнения и задания по теме “Поиск решения”
13.
13.1 Упражнение (Транспортная задача)
13.2 Задания для самостоятельного выполнения
14.
фильтр ”
14.1 Упражнение
14.2 Задания для самостоятельного выполнения
14.3 Упражнение
14.4 Задания для самостоятельного выполнения
Упражнения и задания по теме “Консолидация”
15.
15.1 Упражнение (Консолидация по расположению)
15.2 Упражнение (Консолидация по категориям)
15.3 Задания для самостоятельного выполнения
Упражнения и задания по теме “Функции баз данных”
16.
16.1 Упражнение
16.2 Задания для самостоятельного выполнения
Упражнения и задания по теме “Сводные таблицы”
17.
17.1 Упражнение
17.2 Задания для самостоятельного выполнения
Литература
Относительная, абсолютная и смешанная” Ссылки на ячейки существуют трех типов – относительные, абсолютные и смешанные. Для того чтобы изменить ссылку, необходимо добавить знак доллара ($) перед обозначением столбца и (или) строки.
1.1 Упражнение Необходимо заполнить таблицу (см. рис.1), т.е. найти стоимость каждого вида автомобиля в рублях и долларах, зная текущий курс валюты. Текущий курс валюты записан в отдельной ячейке, и изменяя курс в ячейке, все формулы, в которых участвует данная ячейка, пересчитываются.
В ячейку Е4 запишите формулу =С4*D4 (относительные ссылки).
Выделив ячейки E4:F4 (см. рис.2), скопируйте находящиеся в них В столбце Е (см. рис.3) получится правильное решение, потому что копируя формулу для каждой модели автомобиля, она изменяется за счет относительности ссылки.
Столбец F, Стоимость в у.е (см. рис.3) вычисляется с ошибками.
отображения формул (см. рис.4).
В данной ситуации получается неверная ссылка на ячейку, где хранится текущий курс доллара для F5 и далее во всем столбце. Исправить ошибку можно, если в ячейке F4 изменить ссылку на второй множитель В1 с относительной на абсолютную, закрепив столбец и строку, потому, что ссылка на курс доллара должна оставаться постоянной для каждой модели.
Измените ссылку с =E4/B1 на =E4/$B$1 (зафиксировать ячейку абсолютно или смешанно можно с помощью нажатия клавиши F один или два раза соответственно) и повторив копирование, получается правильное решение столбца Стоимость в у.е.
Отмените режим отображения формул (см. рис.5).
1.2 Задания для самостоятельного выполнения 1. В примере, решенном выше, заполнить правильно столбец Стоимость в у.е., использовав не абсолютную ссылку на В1, а смешанную, предварительно удалив предыдущее решение для столбца Стоимость руководствуясь теорией создания ссылок.
2. Упражнения и задания по теме “Форматирование.
следующее:
1. Выделите ячейки, формат которых требуется изменить.
2. В меню Формат выберите команду Ячейки, а затем откройте вкладку 3. В списке Числовые форматы выберите пункт (все форматы).
4. Введите в поле Тип коды форматов. Образец показывает, как будет выглядеть значение после применения формата.
2.1 Упражнение Задать код для номера телефона со знаками чисел до 11 знаков.
1. В меню Формат выберете команду Ячейки, далее выберете вкладку 2. В списке Числовые форматы выберите пункт (все форматы).
3. В поле Тип введите следующий код 8 (000) 000-00-00.
Тогда, например значение 4998356428 будет выведено как 8 (499) 835-64Задания для самостоятельного выполнения 1. В ячейке введена скорость 250 километров в час. Создать пользовательский формат, чтобы число 250 выводилось как 250 км/час.
2. Создать пользовательский формат, который дописывает к дате краткое название месяца (3 буквы).
Условное форматирование. Чтобы применить условное форматирование к ячейке или диапазону, выполните следующие действия:
1. Выделите ячейку или диапазон.
2. В меню Формат выберите команду Условное форматирование.
условия) либо формула (для форматирования, использующего формулы).
4. Определите условия или введите формулу. При определении условия выберите операцию сравнения, а затем введите необходимое значение или формулу. Перед формулой нужно поставить знак равенства. При использовании формулы в качестве критерия форматирования введите формулу, принимающую логическое значение ИСТИНА или ЛОЖЬ.
5. Щелкните на кнопке Формат и определите форматирование, которое нужно применить, если заданное условие удовлетворяется.
6. Чтобы добавить дополнительные условия, щелкните на кнопке «А также»
и повторите пункты 3-5.
2.3 Упражнение (Задача аттестация студентов) В ходе аттестации студентов факультета «Кредит» получены следующие результаты (см. рис.6).
Предусмотреть заливку соответствующим цветом: если студент получил баллы от 51 до 70, то – синим, если от 71 до 85, то – желтым, если от 86 и выше, то красным цветом. В противном случае (если количество баллов от 0 до 51) цвет оставить без изменения.
1. Выделяем диапазон ячеек D2:N10.
2. В меню Формат выбираем Условное форматирование.
3. В окне Условное форматирование вводим данные, как показано на рисунке 7. Для того чтобы задать второе условие щелкаем на кнопке А также>>.
4. Для заливки необходимым цветом щелкаем на кнопку Формат, далее во вкладке Вид выбираем нужный цвет.
2.4 Задание для самостоятельного выполнения 1. В ходе аттестации студентов факультета «Кредит» получены следующие результаты (см. рис.8).
Предусмотреть заливку красным цветом непустых ячеек, в которых количество баллов находится в пределах от 0 до 50.
2. Ввести в диапазоне А1:А15 числа 1,2,…,15. Используя условное форматирование, выделить красным курсивом на голубом фоне числа от 2 до 4, синим курсивом – числа от 6 до 8, зеленым курсивом – числа от 12 до 15.
3. Упражнения и задания по теме “Встроенные функции.
Логические функции:
ЕСЛИ (УСЛОВИЕ; ВЫРАЖЕНИЕ1; ВЫРАЖЕНИЕ2).
ЕСЛИ (И (УСЛОВИЕ1; УСЛОВИЕ2); ВЫРАЖЕНИЕ1; ВЫРАЖЕНИЕ2) ЕСЛИ (ИЛИ (УСЛОВИЕ1; УСЛОВИЕ2); ВЫРАЖЕНИЕ1; ВЫРАЖЕНИЕ2).
Торговый склад производит уценку хранящейся продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превышает 6 месяцев, но не достигает 10 месяцев, то в 1, раза. Получить ведомость уценки товара, которая должна включать следующую информацию: наименование товара, срок хранения, цена товара до уценки, цена товара после уценки.
1. Данные столбцов: наименование товара, срок хранения, цена товара до уценки заполняем самостоятельно или ввести те же данные что и в таб.1.
=ЕСЛИ(B2>10;C2/2;ЕСЛИ(И(B2>6;B20;1+SIN(A4);1-SIN(A4)), далее копируем эту формулу до ячейки А20.
Вставка/Диаграмма.
5. В окне Мастер диаграмм во вкладке Стандартные выбираем График, а в окне Вид – вид графика, щелкаем на кнопке Далее.
6. В окне Мастер диаграмм (шаг 2) выбираем закладку Ряд. В поле Подписи оси х указываем ссылку на диапазон, содержащий значения х, т.е.
на диапазон ячеек А4:А20. Щелкаем на кнопке Далее.
7. В окне Мастер диаграмм (шаг 3) вводим подписи: Название диаграммы, Ось х, Ось у. Щелкаем на кнопке Готово.
На рабочий лист будет выведена следующая диаграмма (см. рис.22):
9.2 Упражнение Алгоритм построения диаграммы.
1. Выделяем диапазон, содержащий исходные данные и нажимаем кнопку Мастер диаграмм (или выбираем в меню Вставка команду Диаграмма).
В результате появится окно мастера для построения диаграммы.
Процесс создания диаграммы с помощью мастера включает несколько шагов. После выполнения каждого шага можно перейти к следующему, нажав кнопку Далее, или вернуться к предыдущему, нажав кнопку Назад. Можно в любое время отказаться от построения диаграммы, нажав кнопку Отмена.
Можно также нажать кнопку Готово, чтобы пропустить оставшиеся шаги и информации.
2. Первое окно диалога мастера диаграмм предоставляет набор рисунков с типами диаграмм. Выбираем тип диаграммы в левой части окна и вид – в правой части окна. Нажимаем кнопку Далее.
3. Во втором окне мастера диаграмм можно подтвердить или задать данные, выделены перед нажатием кнопки Мастер диаграмм, поэтому поле Диапазон уже содержит ссылку на диапазон, где хранятся исходные данные для диаграммы, имеет смысл включать в него все заголовки, которые идентифицируют ряды данных и категории диаграммы. Мастер диаграмм вставит текст этих заголовков в диаграмму.
4. Используя переключатели Ряды данных, устанавливаем Ряды в столбцах. Примечание: Вкладка Ряд позволяет удалять или добавлять ряды данных из диаграммы. Нажимаем кнопку Далее.
5. В третьем окне Мастера диаграмм устанавливаются различные параметры для создаваемой диаграммы. Во вкладке Заголовки называем диаграмму. Во вкладке Линии сетки добавляем основные линии по оси X и Y. Во вкладке Легенда размещаем легенду справа от диаграммы.
Нажимаем кнопку Далее.
6. В последнем окне диалога Мастер диаграмм, для создания внедренной диаграмму на имеющемся листе. Нажимаем кнопку Готово.
9.3 Задания для самостоятельного выполнения 1. Построить график функции у=ах3-bx+c. Х изменяется от -20 до +20 с шагом 0,5. Значения a, b, с задаются в отдельных ячейках, например в ячейках D1:F1.
2. Построить в одной системе координат при х [-2;2] графики следующих двух функций:
а) y=2sin(x) - 3cos(x), z=cos2(2x)-2sin(x);
б) y=5sin(x) - cos(3x)sin(x), z=cos(2x)-2sin3(x).
3. Построить круговую диаграмму под названием «Расходы федерального бюджета». Диаграмма должна быть снабжена легендой и подписями данных.
Подсчитать долю прочих расходов бюджета, если вся сумма расходов составляет 100%. Данные для диаграммы представлены в таблице 7.
Прочие расходы 4. Создать и оформить по образцу таблицу (таб.8), выполнить все необходимые расчеты, оформить соответствующим столбцам денежный формат, использовать, где необходимо функции. Предусмотреть премию в размере 15% от «к оплате» тем сотрудникам, у кого количество отработанных часов больше или равно 50. Налог составляет:
12%, если сумма к оплате находится в пределах от 1000 до По данным таблицы 8 построить разные типы диаграмм по столбцам «Фамилия» и «На руки». Задать название диаграммы «Ведомость на получение зарплаты». Диаграмма должна быть снабжена легендой и подписями данных.
10. Упражнения и задания по теме “Таблицы подстановки” 10.1 Упражнение Клиент в течение 6 лет в начале каждого года делает вклады в банк в размере 1000 руб. Годовая процентная ставка по выбранному виду вклада равна 12%. Первоначальный взнос 5000 руб. Требуется рассчитать будущее значение вклада и определить, какими будут будущие значения вкладов при меняющейся процентной ставке.
1. С помощью функции БС рассчитаем будущее значение вклада (см. рис.23):
2. В ячейки E3:F6 вводим следующие данные, оставив пустой строку перед числовыми значениями (см рис.24):
При этом должны соблюдаться следующие правила:
а) Если значения в таблице подстановки ориентированы по столбцу, вводим формулу в ячейку, расположенную на одну строку выше и на одну ячейку правее первого значения;
б) Если значения в таблице подстановки ориентированы по строке, вводим формулу в ячейку, расположенную на один столбец левее и на одну строку ниже первого значения.
3. В ячейку F2 вводим следующую формулу для расчета будущего значения вклада =БС(B1;B2;B3;B4;1).
4. Выделяем диапазон ячеек E2:F6.
5. В меню Данные выбираем команду Таблица подстановки (рис.25).
6. В поле “Подставлять значения по строкам в:” (т.к. значения процентных ставок находятся в разных строках) указываем ячейку В1.
7. После нажатия на Ок будет получен следующий результат (рис. 26):
10.2 Упражнение Требуется рассчитать будущие значения вкладов при меняющейся процентной ставке, в зависимости от срока выплаты. Условия задачи те же что и в упражнении 1.
1. Модифицируем таблицу предыдущего примера таким образом, чтобы значения одного изменяемого параметра располагались в левом столбце, а значения другого – в верхней строке таблицы.
2. В ячейке Е2 вводим формулу =БС(B1;B2;B3;B4;1). Формула при этом должна быть помещена в левый верхний угол формируемой таблицы (рис.27).
3. Выделяем диапазон ячеек Е2:К6.
4. В меню Данные выбираем команду Таблица подстановки.
5. В поле “Подставлять значения по строкам в:” указываем ячейку В1, В поле “Подставлять значения по столбцам в:” указываем ячейку В2, как показано на рис.28.
6. После нажатия на Ок будет получен следующий результат (рис.29):
10.3 Задания для самостоятельного выполнения 1. С помощью финансовой функций БС определить, какая сумма будет накоплена при следующих условиях:
начальное значение вклада – 15 000 евро;
годовая процентная ставка – 9,5%;
Проценты начисляются ежеквартально.
Как изменится результат, если годовая процентная ставка составит 9%, 10%, 12%, 13%, 14%, 15%?
2. Построить таблицу умножения Пифагора.
3. С помощью финансовой функции ПС определить, какую сумму нужно ежемесячно докладывать при следующих условиях:
будущее значение вклада – 120 000 долларов;
годовая процентная ставка – 11,25%;
Проценты начисляются каждые полгода.
Определить, какую сумму нужно ежемесячно докладывать:
при процентной ставке 10%, 10,5%, 12%, 12,75%;
если будущие значения вклада составят 80000, 90000, 100000, 4. Используя финансовую функцию ПЛТ, таблицу подстановки с двумя входами и исходные данные таблицы 9, рассчитать динамику изменений размера выплаты при изменении процентной ставки и срока выплаты ссуды в указанных пределах.
5. Используя финансовую функцию ПС, таблицу подстановки с двумя входами и исходные данные таблицы 10, рассчитать начальное значение вклада при изменении процентной ставки и срока выплаты в указанных пределах.
11. Упражнения и задания по теме “Подбор параметра.
11.1 Упражнение Требуется найти корни уравнения Y=x2+3,5x-5 на отрезке [-2;2].
Уравнение представлено полиномом второй степени, поэтому оно имеет не более двух корней.
1. Создайте последовательность чисел для отрезка [-2;2] в диапазоне 2. В смежном столбце найдите значение данной функции в точке -2 и скопируйте формулу для каждой точки в диапазоне [-2;2].
3. Столбец со значениями функций меняет знак на отрезке (1; 1,2).
Значит, функция пересекает ось Х на отрезке [-2;2] всего один раз и, в текущем интервале находится первый корень данного уравнения.
4. Скопируйте в ячейку Е3 значение точки х=1, а в смежной ячейке F вычислите значение функции в этой точке.
5. Для того, чтобы установить необходимую точность и количество итераций, используйте команды Сервис – Параметры и на вкладке Вычисления установите Е=0,00000001 и N=1000.
6. Выполните команды Сервис – Подбор параметра и заполните Установить в ячейке: ссылка на ячейку F3, в которой записана Значение: в строке установите значение 0, которому должно удовлетворять значение функции.
находится значение аргумента функции (см. рис.30).
После нажатия кнопки ОК получится значение 1,09, которое является корнем данного уравнения на отрезке [-2;2] с заданной точностью.
11.2 Задания для самостоятельного выполнения 1. Найти корни уравнения y=3*х-3,5 на отрезке [0;3].
2. Найти корни уравнения y=e2x-3 на отрезке [-1;3].
3. Найти корни уравнения y=sin(x) на отрезке [ /2;5* /2].
4. Найти корни уравнения y=Ln(x)-1 на отрезке [2;4].
12.1 Упражнение Найти производную функции Y= 4х2 - 4x + 6 в точке x=2. Заметим, что производная приведенной функции в точке x=2, вычисленная аналитическим методом, равна 12 – это значение нам понадобится для проверки результата, полученного путем вычисления численным методом в электронной таблице.
Из математического анализа известно: выражение для вычисления производной функции одной переменной в точке x, имеет вид:
где x – очень малая конечная величина. То есть вместо выражения х можно взять достаточно маленькое число, например 0,00001.
Решим задачу двумя способами.
1. Вводим в ячейку B1 рабочего листа заданное значение аргумента, равное 2, в другой ячейке - B2 укажем достаточно малое приращение аргумента – например 0,00001, в ячейке В3 вычисляем сумму В3=В1+В2.
3. После нажатия клавиши Enter получаем результат вычисления 12, (см. рис.31).
1. Зададим окрестность точки х=2 достаточно малого размера, например, значение слева X k = 1,99999, а значение справа X k 1 = 2,00001 и введем эти значения в ячейку А2 и А3 соответственно.
2. Вводим в ячейку рабочего листа формулу правой части заданной функциональной зависимости, например в ячейку В2, как показано на рис.32, делая ссылку на ячейку А2, где находится значение х: = 4*А2^2 A2+6.
3. Копируем эту формулу в ячейку В3.
4. В ячейку С2 вводим формулу вычисления производной (рис.32): = (В3В2)/(A3-A2).
В результате вычисления в ячейке С2 будет выведено приближенное значение производной заданной функции в точке х=2, величина которой равна 12, что соответствует результату, полученному аналитически.
12.2 Задания для самостоятельного выполнения 1. Найти производную функции f(x)=3cos3x в точке х.
вычислить двумя способами.
3. Вычислить производную функции F(x)=ln3(x)+3x2-log2x в точке 8.
Результат вычислить двумя способами.
12.3 Упражнение Вычислите определенный интеграл Для численного вычисления определенного интеграла с использованием конечных разностей существует несколько методов. Наиболее простым является метод трапеций. Для вычисления определенного интеграла по методу трапеций используется формула:
Для численного вычисления величины интеграла в электронной таблице можно применить две технологии – технологию приближенного вычисления и технологию точного вычисления.
Величина интеграла, вычисленная аналитически, равна 19.
Решение (Технология приближенного вычисления) 1. Табулируем подынтегральную функцию в диапазоне изменения значений аргумента [2,3] с шагом 0,05 (Чем меньше шаг, тем больше точность вычисления) (рис.33).
2. В ячейку B2 вводим формулу =3*A2^2*(A3-A2)+((3*A3^2-3*A2^2)*(A3A2))/2, которая реализует часть приведенной выше формулы, размещенной правее знака суммы, т.е вычисляет величину элементарной площади (криволинейной трапеции).
3. Копируем буксировкой формулу, записанную в ячейке B2, до значения аргумента х = 2,95, то есть до предпоследнего значения точки х.
результаты. Вычисленное значение в ячейке В22 и будет величиной интеграла – 19,00125.
12.4 Упражнение Вычислим интеграл 2 sin xdx, используя технологию точного вычисления.
Технология точного вычисления основана на использовании аппарата циклических ссылок и итераций. Применение этой технологии позволяет задавать достаточно малый шаг интегрирования, что увеличивает точность вычислений. Для точного вычисления нужно выполнить следующие операции:
1. Определить, на сколько интервалов нужно разбить диапазон интегрирования, чтобы получить требуемую точность, и задать их количество в виде количества итераций. Положим для решения нашей задачи достаточно 10000 интервалов.
Вычисления в диалоговом окне Параметры и в поле Предельное число итераций ввести число 10000. В поле Относительная погрешность ввести число равное 0,00001. Если установлен флажок Итерации, то нужно выключить его. Закрыть диалоговое окно Параметры.
3. В ячейки рабочего листа ввести исходные данные и формулы для вычислений (рис.34).
В ячейке В6 формула =(B4-B3)/B5 вычисляет шаг интегрирования. В ячейке С3 формула = 0+C3+B6 – вычисляет текущее значение аргумента х.
Значение 0 в формуле устанавливает нижний предел интегрирования. В формуле есть циклическая ссылка на эту же ячейку С3 +В6, она реализует накопление величины х относительно нижнего предела.
В ячейке D3 записана формула, реализующая метод трапеций и накопление суммы площадей элементарных трапеций.
4. После ввода исходных данных и формул вновь выполнить команду меню Сервис/Параметры, открыть закладку Вычисления в диалоговом окне Параметры и установить флажок Итерации. Щелкнуть на кнопке ОК.
Потребуется некоторое время для того, чтобы табличный процессор (рис.34).
5. После завершения вычислений вновь вызвать диалоговое окно Параметры и выключить флажок Предельное число итераций.
6. Результат вычисления будет 2,00031413.
12.5 Задания для самостоятельного выполнения 1. Вычислить определенный интеграл функции 2cos2x на интервале от 0 до Результаты сравнить.
сравнить.
Результаты сравнить.
5. Вычислить определенный интеграл двумя способами x 4dx. Результаты сравнить.
13. Упражнения и задания по теме “Поиск решения” 13.1 Упражнение (Транспортная задача) На четырех элеваторах A, B, C, D находится зерно в количестве 100, 120, 150, 130 тонн, которое нужно доставить на четыре сельскохозяйственных предприятия для посева. Предприятию 1 необходимо поставить 140т, Стоимость доставки потребителям от поставщиков представлена в таблице 11.
Составьте оптимальный план перевозки зерна из условия минимума стоимости перевозки.
1. Вводим данные условия задачи, как показано на рисунке 35. В столбце F введены возможности элеваторов (в тоннах), а в строке 8 необходимые потребности предприятий в зерне в тоннах. А в ячейках В4:Е7 - стоимость доставки одной тонны потребителям от поставщиков.
2. В диапазон ячеек В11:Е14 вводим возможные изменяемые значения (в тоннах) перевозок зерна от поставщика к потребителям, то есть с элеватора на предприятие. Вводим в эти ячейки, например число 1.
3. В ячейке F11 автосуммированием вычисляем сумму чисел В11:Е11.
Копируем полученный результат в ячейки F12:F14. Таким же образом в ячейке В15 вычисляем сумму чисел В11:В14. Копируем полученную формулу в ячейки С15:Е15.
Так как ячейки В11:Е14 изменяемые, то понятно что, во-первых, эти числа должны быть целыми и не отрицательными (так как количество перевозок не может быть меньше нуля и равняться дробному числу), то есть В11:Е14>=0, В11:Е14=целое. Во-вторых, потребности предприятий должны быть полностью должны превышать заявленных, то есть F11:F14=F4:F7.
4. В ячейках В18:Е21 вычисляем стоимость (в рублях) доставки зерна от элеваторов к предприятиям. Для этого выделяем диапазон ячеек В18:Е21, ставим знак равно, далее выделяем диапазон ячеек В4:Е7, ставим знак умножения и выделяем диапазон ячеек В11:Е14. После этого нажимаем комбинацию клавиш Shift +Ctrl +Enter.
5. В ячейке В23 находим сумму чисел В18:Е21 – это у нас будет целевая функция.
6. Выделяем ячейку В23, после этого по команде Сервис\Поиск решения открываем диалоговое окно Поиск решения. Если Поиск решения отсутствует в меню Сервис, то выбираем команду Сервис\Надстройка и в диалоговом окне Надстройки ставим галочку в поле Поиск решения.
7. В диалоговом окне Поиск решения вводим данные, как показано на рисунке 36.
8. Чтобы задать ограничения, нажимаем командную кнопку Добавить и в окне Добавления ограничения, вводим ограничения, как показано на рис.37.
9. После того как ввод данных завершен, в окне Поиск решения нажимаем на Выполнить и появляется окно Результаты поиска решения.
Подтверждаем сохранность найденного решения нажатием командной кнопки Ок. В ячейке В23 должен отобразиться результат равный 1880.
1. Завод выпускает продукцию в четырех цехах: A,B,C,D, расположенных на разных территориях. Свою продукцию завод поставляет в шесть магазинов города. Цех A производит 130 тыс. изделий, цех B -90, цех С- 100 и цех D – соответственно 140 тыс. шт. изделий. Плановая потребность магазинов в продукции завода следующая: магазин 1 - 110 тыс. шт. изделий, магазин 2 – тыс. шт., магазин 3 - 30 тыс. шт., магазин 4 – 80 тыс. шт., магазин 5 – 100 тыс.
шт. и магазин 6 – 90 тыс. шт. Стоимость перевозки 1 тыс. шт. изделий из цехов в магазины приведена в таблице 12.
Составьте такой план перевозки изделий, при котором расходы на перевозку изделий были бы наименьшими.
2. Торговая фирма «Весна и осень» включает четыре предприятия и шесть складов в различных регионах страны. Каждый месяц предприятия фирмы производят 100, 15, 90 и 55 ед. продукции. Вся производимая продукция направляется на склады, вместимость которых следующая: 30, 40, 55, 80, 45, и ед. продукции. Издержки транспортировки продукции от предприятий до складов следующие (ден. ед.) (см. таб.13):
Определите план перевозок из условия минимизации ежемесячных расходов на транспортировку.
3. Имеются четыре овощехранилища, расположенные в разных районах города, в которых сосредоточено 10, 20, 35 и 45 т овощей соответственно.
Овощи необходимо перевезти четырем потребителям соответственно в количестве 25, 30, 40 и 15 т. Расстояния от хранилищ до потребителей следующие (см. таб.14):
Затраты на перевозку 1т овощей на 1 км постоянны и равны 20 руб.
Определите план перевозок продукта от хранилищ до потребителей из условия минимизации транспортных расходов.
14.1 Упражнение Определить, какая сумма по зарплате была выплачена в первом квартале 1997 года Антонову В.Л. (О:\RASHOD.xls).
1. Для того, что бы использовать инструмент автофильтр необходимо, находясь в списке базы данных в меню Данные/Фильтр выбрать команду Автофильтр (см. рис.38).
2. Для столбца Дата в ниспадающем списке выбрать команду условие и в диалоговом окне Пользовательский автофильтр определить диапазон первого квартала 1997 года (см. рис.39).
рис.40).
4. Для столбца Получатель в ниспадающем списке выбрать получателя Антонов В.Л..
5. Используя функцию Промежуточные итоги, подсчитать сумму по столбцу Сумма (см. рис.41).
1. С помощью автофильтра вывести данные по накладным расходам за первое полугодие 1997 года.
2. Определить получателей, которые получают зарплату свыше 1000$.
3. Вывести данные по зарплатам, сумма которых лежит в пределах от 1000$ до 2000$ за четвертый квартал 1997 года.
4. Использовав инструмент автофильтр и функцию промежуточные итоги, определить затраты на материалы в третьем квартале 1997 года.
14.3 Упражнение Сформируйте таблицу, в которую войдут все расходы по АО Престиж, ЗАО БИН, ТОО Надежда, сумма которых превышает 500$ за первое полугодие 1997 г. (О:\RASHOD.xls).
Использовать для решения данной задачи инструмент автофильтр недостаточно, потому что нужно сформировать отдельную таблицу и данные по получателям АО Престиж, ЗАО БИН, Антонов В.Л. одновременно.
Предварительно создайте имена для всей базы данных и для каждого столбца отдельно (выделите всю базу данных вместе с шапочкой, в меню Вставка – Имя – Присвоить задайте имя База, далее не снимая выделение, повторите последовательность операций за некоторым исключением - Вставка – Имя – Создать, в диалоговом окне Создать имена установим галочку только для команды в строке выше).
1. Создайте диапазон условий по столбцам – Получатель, Дата и Сумма исходя из задачи (см. рис.42).
На основе данных файла О:\RASHOD.xls 2. В меню Данные – Фильтр – выбрать Расширенный фильтр и заполнить его следующим образом (см. рис.43):
Обработка: скопировать результат в другое место;
Исходный диапазон: с помощью клавиши F3 командной строки, в окне Вставка имени, выберите имя База;
Диапазон условий: выделить область критерия, созданного по требованиям задачи;
Поместить результат в диапазон: выделить в свободной области рабочей книги крайнюю левую ячейку области, где предполагается поместить результат - F7.
14.4 Задания для самостоятельного выполнения 1. Используя автофильтр, определить, сколько составляла сумма накладных расходов по базе RASHOD за третий квартал 1997 (О:\ RASHOD.xls).
2. Определить с помощью расширенного фильтра сумму, выплаченную по зарплате получателям Васильева М.Ф., Казаков С.С., Иванов И.И., Песков В.А., Голубев А.Л., Борисова Т.М., Антонов В.Л.
3. Сформировать таблицу, содержащую информацию о получателях Песков В.А., Голубев А.Л., ТОО Надежда, АО Оргсинтез, АО ИНВЕСТ за четвертый квартал.
4. Определить количество операций по столбцу Материалы за второй квартал 1997 года по физическим лицам.
расходам для получателя - фирма ДИЛЛ.
15. Упражнения и задания по теме “Консолидация” 15.1 Упражнение (Консолидация по расположению) Требуется подсчитать сумму всех окладов и премий, выданных каждому сотруднику за 2 месяца.
1. Заполняем рабочую книгу данными о получаемых сотрудниками зарплате и премиях, поместив информацию о каждом месяце на отдельный лист. Выделив два листа рабочей книги, вводим в 1-ю строку шапку таблицы «Фамилия», «Оклад», «Премия» и в первый столбец 2. Снимаем выделение рабочих листов и на каждом листе заполняем в полученных таблицах 2-й и 3-й столбцы произвольными значениями.
3. На новом рабочем листе формируем итоговую таблицу, скопировав названия столбцов.
4. Поместим курсор под названием 1-го столбца, т.е. «Фамилия».
5. Выбираем в меню Данные команду Консолидация.
6. В поле Функция из списка функций выбираем функцию Сумма.
7. Установив курсор в поле Ссылка, щелкнем мышью на ярлыке первого листа и выделяем на нем все фамилии и числовые значения.
8. Нажимаем кнопку Добавить в окне Консолидация.
9. Вернувшись в поле Ссылка, аналогичным образом добавим данные со 2го листа.
10. В наборе флажков Использовать в качестве имен устанавливаем флажок, соответствующий расположению в исходной части заголовков:
Подписи левого столбца.
11. После нажатия на Ок в новую таблицу будут помещены фамилии всех сотрудников и суммы полученных ими окладов и премий.
Требуется подсчитать количество премий, выданных каждому сотруднику за 2 месяца.
1. Воспользуемся данными первых 2-х листов из упражнения 1.
2. Добавляем на 2-м листе еще 2 любые фамилии и соответствующие им значения окладов и премий.
3. На новом рабочем листе (или в новом месте 3-го листа) формируем итоговую таблицу, скопировав или введя заново названия столбцов «Фамилия» и «Премия».
4. Выделяем названия столбцов, т.е. «Фамилия» и «Премия».
5. Выбираем в меню Данные команду Консолидация.
6. В поле Функция из списка функций выбираем функцию Количество Значений.
7. С помощью кнопки Удалить удаляем старые ссылки из поля Список Диапазонов.
8. Установив курсор в поле Ссылка, щелкаем мышью на ярлыке первого листа и выделяем на нем значения всех 3-х столбцов (включая шапку).
9. Нажимаем кнопку Добавить в окне Консолидация.
10. Вернувшись в поле Ссылка, аналогичным образом добавляем данные со 2го листа (обратите внимание, что исходная область на этом листе больше и ее нужно увеличить).
11. В наборе флажков Использовать в качестве имен устанавливаем флажок, соответствующий расположению в исходной части заголовков: Подписи верхней строки и Подписи левого столбца.
12. После нажатия на Ок в новую таблицу будут помещены фамилии всех сотрудников и количество полученных ими премий.
15.3 Задания для самостоятельного выполнения Откройте файл Rashod.xls с сетевого диска Common.
Консолидация). В первом столбце – Дата расхода, во втором – количество сумм, расходованных по дням, в третьем столбце – общее количество выплат по дням.
Начало таблицы смотрите в таблице 15.
2. Построить таблицу из трех столбцов (используя инструмент Консолидация). В первом столбце – месяц расхода, во втором – количество сумм, расходованных по месяцам, в третьем столбце – общее количество выплат по месяцам.
Начало таблицы смотрите в таблице 16.
3. Вычислить количество полученных затрат и общую сумму Иванова И.И., Казакова С.С. и Пескова В.А.
значение выданной суммы.
5. Построить таблицу из двух столбцов. В первом – список получателей, во втором – общая сумма полученных денег каждым получателем.
6. Построить таблицу из трех столбцов. В первом – день недели, во втором – общая сумма, выплаченная в каждый из дней, в третьем – количество выплат.
16. Упражнения и задания по теме “Функции баз данных” 16.1 Упражнение Откройте файл Rashod.xls с сетевого диска Common.
Определить сумму, выплаченную по зарплате получателям Васильева М.Ф., Казаков С.С., Иванов И.И., Песков В.А., Голубев А.Л., Борисова Т.М., Антонов В.Л.
1. Создайте диапазон условий (F1:G7) в соответствии с требованиями 2. В ячейке H10 вызовите функцию БДСУММ из категорий Работа с базой данных и заполните строки следующим образом:
Вставка имени, выберите базу данных - база (предварительно назовите список именем - база и каждый столбец списка, именами Дата, Расход, Сумма, Получатель, соответственно руководствуясь Строка Поле: выделите шапку Сумма.
Критерий: выделите диапазон условий вместе с заголовками столбцов (см. рис.45).
16.2 Задания для самостоятельного выполнения 1. С помощью функции базы данных определить, сколько составляла сумма по зарплате базы RASHOD за ноябрь 1997 года (О:\ RASHOD.xls).
сумму по накладным расходом для получателей: ТОО Надежда, фирма Найти количество выплат по зарплате Борисова Т.М. и Казаков С.С. за весь период, когда сумма выплат была больше 1000$.
4. Вычислить количество накладных расходов во втором квартале для сумм в пределах от 500$ до 1500$.
5. Определить максимальную сумму по столбцу Сумма за второй квартал 1997 года по зарплате для физических лиц.
17. Упражнения и задания по теме “Сводные таблицы” 17.1 Упражнение Требуется определить среднее значение заработной платы за каждый месяц 1997 года получателя Пескова В.А. (О:\RASHOD.xls).
1. Установите курсор в область базы данных с помощью меню Данные - Сводная таблица, запустите инструмент сводная таблица.
2. В диалоговом окне Мастер сводных таблиц выберите опцию в списке или базе данных Microsoft Excel,а в опции – вид создаваемого отчета сводная таблица.
3. На втором шаге в качестве диапазона с помощью командной 4. На следующем шаге укажите область расположения итоговой таблицы и нажмите на кнопку Макет (см. рис.46).
5. В следующем диалоговом окне настройки макета укажите в качестве заголовков строк столбец Дата, в качестве заголовков столбцов столбец Получатель, а в качестве данных определите значения столбца Сумма. Далее в качестве функции агрегации выберите двойным щелчком поле Сумма (см. рис.47).
6. Поместите результат на новый лист и нажмите клавишу Готово.
7. Далее по столбцу Получатель в ниспадающем списке снимите галочку напротив Показать все и устанавите ее напротив фамилии Песков В.А.
8. Для данных в строке Дата вызовите контекстное меню и выбирите команду Группировка и структура, далее команду Группировать. В диалоговом окне Группирование в области с шагом установите Месяцы (см. рис.48).
17.2 Задания для самостоятельного выполнения 1. С помощью инструмента сводная таблица определить, сколько составляла сумма по зарплате базы RASHOD за ноябрь 1997 года (О:\ RASHOD.xls).
2. Используя сводную таблицу для работы с базами данных, определить выплаченную сумму по накладным расходам для получателей ТОО Надежда, фирма ДИЛЛ.
Найти количество выплат по зарплате Борисову Т.М. и Казакову С.С.
за каждый месяц.
4. Вычислить среднюю сумму по зарплате за каждый квартал 5. Определить максимальную сумму по зарплате для каждого получателя.
1. Магомедов Р.М. Учебно-методическое пособие «Информатика». Учебное издание для иностранных абитуриентов всех форм обучения. – М.:
Финансовая академия при Правительстве РФ, Кафедра “Информационные технологии”, 2008г. – 101 с.
2. Мамонтова Е.А., Мельников П.П., Степанова Н.В. Работа с табличным процессором Excel MS Office. Методические указания и задания к изучению курса “Экономическая информатика”, (“Информатика”). – М.:
Финансовая академия при Правительстве РФ, Кафедра “Информационные технологии”, 2004г. – 82 с.
3. Мельников П.П., Поляков В.П. Применение табличного процессора Excel для поиска решений. Методические указания и задания к изучению курса Правительстве РФ, Кафедра “Информационные технологии”, 2001г. – 40 с.
использованию MS Excel при изучении дисциплины «Информатика». – М.:
Финансовая академия при Правительстве РФ, Кафедра “Информационные технологии”, 2006г. – 66 с.
5. Мельников П.П. Методическое пособие для практических работ по информатике (в четырех частях). Для студентов, обучающихся по специальности «Финансы и кредит». – М.: Финансовая академия при Правительстве РФ, Кафедра “Информационные технологии”, 2007г.
6. Невежин В.П., Кружилов С.И. Сборник задач по курсу “Экономикоматематическое моделирование”. – М.: ОАО «Издательский Дом “Городец”», 2005. – 320 с.
«