Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
Лабораторная работа № 7
Применение в расчетах статистических, математических, логических, текстовых функций, функций даты и времени.
Цель занятия: Получение навыков по использованию в расчетах статистических, математических, финансовых, логических, текстовых, функций даты и времени. Закрепить умения по вводу простых и сложных формул с использованием арифметических операторов, встроенных функций и ссылок на ячейки.
Основная литература:
Уокенбах, Джон. Microsoft Excel 2010. Библия пользователя.: Пер.с англ. – М.: ООО «И.Д.Вильямс», 2011.-312 с. : ил. – Парал.тит.англ.
Дополнительная литература:
Волков В.Б. Понятный самоучитель Excel 2010. – СПб.:Питер, 2010.-256с.:ил.
Кулешова О.В., Центр Компьютерного обучения «Специалист», Microsoft Excel 2010. Анализ и визуализация данных. Решения практических задач. Методическое пособие, 2012.
Ход работы
Задание 1. Работа с математическими функциями (Лист 1).
Задание 2. Работа с логическими функциями (Лист 2).
1. Известен возраст двух человек – a, b. Определите, кто из них старше. (Функция ЕСЛИ).
2. В одну из ячеек будет введено число. В другой ячейке получить ответ на вопрос, является ли введенное число отрицательным.
3. В одну из ячейку будет введен год рождения первого человека, в другую ячейку — второго человека (значения годов рождения не равны между собой). Необходимо получить ответ на вопрос, кто старше — первый человек или второй.
4. Оформить лист для расчета значения у при заданном значении х:
Значение х должно вводиться в одну из ячеек.
Задание 3. Работа с текстовым функциями.
ДЛСТР возвращает количество знаков в текстовой строке.
ЗАМЕНИТЬ замещает указанную часть знаков текстовой строки другой строкой текста.
ЛЕВСИМВ возвращает указанное число знаков с начала текстовой строки.
Функция НАЙТИ находит вхождение одной текстовой строки (искомый_текст) в другую текстовую строку (просматриваемый_текст) и возвращает положение начала искомого текста относительно крайнего левого знака просматриваемого текста.
Функция ПОДСТАВИТЬ используется, когда нужно заменить определенный текст в текстовой строке.
ПОИСК Возвращает позицию первого вхождения знака или текстовой строки при поиске слева направо, начиная с нач_позиция.
ПРАВСИМВ возвращает заданное число последних знаков текстовой строки.
ПРОПИСН Делает все буквы в тексте прописными.
ПСТР возвращает указанное число знаков из текстовой строки, начиная с указанной позиции.
СТРОЧН Преобразует знаки в текстовой строке из верхнего регистра в нижний.
СЦЕПИТЬ Объединяет несколько текстовых строк в одну.
Задание 4. Работа с функциями даты и времени (Лист 4).
ДЕНЬ Возвращает день в дате, заданной в числовом формате.
МЕСЯЦ Возвращает месяц в дате, заданной в числовом формате.
ГОД Возвращает год, соответствующий аргументу дата_в_числовом_формате.
|
Ячейка |
Формула |
Формат вывода |
Описание |
|
С4 |
=СЕГОДНЯ()-B4+1 |
ГГ |
Возраст |
|
Е4 |
=ГОД(СЕГОДНЯ()-D4)-1900+МЕСЯЦ(СЕГОДНЯ()-D4)/12 |
#0,00 |
Стаж работы |
Задание 5. Работа со статистическими функциями (Лист 5).
Задание 6. Работа с финансовыми функциями (Лист 6).
При задании аргументов финансовых функций следует помнить следующее. Все аргументы, означающие расходы денежных средств (например, ежегодные платежи), представляются отрицательными числами, а аргументы, означающие поступления (например, дивиденды) - положительными числами. Все даты как аргументы функции имеют числовой формат. Для логических аргументов используются константы ИСТИНА или ЛОЖЬ, ли функции категории Дополнительные
Задание 7. Дан прямоугольный параллелепипед со сторонами а, Ь, с. Вычислить:
• объем V= abc;
• площадь поверхности S = 2(ab+bc+ac);
• длину диагонали ;
• угол между диагональю и плоскостью основания ;
• угол между диагональю и боковым ребром ;
• объем шара, диаметром которого является диагональ ,
Задание 8. В правильной четырехугольной пирамиде заданы: длина стороны основания а и высота h. Вычислить:
• объем
• угол наклона бокового ребра к плоскости основания
• длину бокового ребра ;
• радиус описанного около пирамиды шара
• угол наклона боковой грани к основанию
• радиус вписанного в пирамиду шара ;
• площадь полной поверхности пирамиды
Задание 9. Сформировать и заполнить накопительную ведомость по переоценке основных средств производства по форме, которая приводится ниже:
Формулы для расчетов:
ОС = БС - ИО,
ВПС = БС * К,
ВОС = ОС * К,
где К - коэффициент, равный: 1) 3,3 - если БС меньше либо равен 650 млн. руб.; 2) 4,2 - если БС больше 650 млн. руб., но меньше 1000 млн. руб.; 3) 5,1 -если БС равен 1000 млн.руб. или более.
Отформатировать полученные в таблице результаты.
Задание 10. Сформировать и заполнить отчетную ведомость о результатах работы сети компьютерных клубов по форме, которая приводится ниже:
Для вычисления в столбце «Место» используется функция РАНГ.
Отформатировать полученные в таблице результаты.
Задание 11. Сформировать на рабочем листе ведомость «Расчет заработной платы работников научно-производственного отдела Альфа»
При расчетах в ведомости учитывать:
а) k, Надбавка за стаж. Итого, Процент налога, Удержать, Выдать - вычисляются с помощью соответствующих формул;
б) коэффициент k присваивается из следующего расчета: 0,1 - отработано до 5 лет ключительно, 0,2 - от 5 до 10 лет включительно, 0,25 — от 10 до 15 лет включительно, 0,3
свыше 15 лет;
в) Надбавка за стаж - денежный эквивалент за стаж работы;
г) Итого —тарифная ставка с учетом стажа;
д) Процент налога - учитывает, что: 2 % - начисление (по Итого) составляет до 400000 включительно, 10 % - от 400000 до 550000 включительно, 20 % - от 550000 до 700000 включительно, 35 % - превышающие 700000.
е) Удержать - денежный эквивалент налогов;
ж) Выдать - сумма к выдаче: Итого без Удержать.
Настроить условное форматирование так, чтобы стаж работы - до 5 лет - данные были представлены желтым цветом, от 5 до 10 - синим, от 10 до 15 - зеленым свыше 15-красным.
Задание 12.
Средство Поиск решения позволяет находить для одной формулы такие значения ее входных переменных, которое приводили бы к точно заданному значению, либо минимально или максимально возможному значению. Это средство позволяет также налагать ограничения на значения любых переменных, от которых зависит результат вычисления формулы.
Вычислим оптимальные цены и количество театральных билетов разной категории таким образом, чтобы стоимость всех проданных билетов достигала определенной величины. В театре имеются билеты трех категорий: детские, для взрослых и льготные. Найдем, сколько необходимо продать театральных билетов для того, чтобы в кассе оказалось ровно 240 тыс. руб., при выполнении следующих ограничений:
Рабочий лист выглядит так:
Параметры окна Поиск решения:
Задание 13. Некий завод производит в смену суммарно не более 300 изделий. Необходимо определить при этом ограничении, а также при условии, что должно производиться не менее 30 шт. любого изделия, количество выпускаемых изделий таким образом, чтобы получить максимальный доход.
Рабочий лист имеет вид:
Задание 14. Прогноз аукционных продаж.
Подготовьте лист для расчетов
1. Определить для первого лота, сколько должно быть подано заявок, чтобы в течение 6 дней продаж среднее увеличение цены в день составляло 150000.
Вид окна Поиск решения:
2. Необходимо определить, какое количество заявок и количество дней продажи (по каждому лоту) обеспечат среднее за день возрастание цены (по всем лотам), равное 360000. При этом должны выполняться следующие ограничения:
Вид окна Поиск решения:
Задание 15. Расчет по банковским кредитам.
Подготовьте лист для расчетов:
Необходимо определить максимально возможный размер кредита, если сумма ежемесячных выплат не должна превышать 45 000, при этом известно, что процентная ставка не превысит 10%, а кредит берется на 60 месяцев.
Задание 16. Производственные расчеты
Подготовьте лист для расчетов: