Лабораторная работа 7 Применение в расчетах статистических математических логических текстовых функ
Работа добавлена на сайт samzan.net:
Лабораторная работа № 7
Применение в расчетах статистических, математических, логических, текстовых функций, функций даты и времени.
Цель занятия: Получение навыков по использованию в расчетах статистических, математических, финансовых, логических, текстовых, функций даты и времени. Закрепить умения по вводу простых и сложных формул с использованием арифметических операторов, встроенных функций и ссылок на ячейки.
Основная литература:
Уокенбах, Джон. Microsoft Excel 2010. Библия пользователя.: Пер.с англ. – М.: ООО «И.Д.Вильямс», 2011.-312 с. : ил. – Парал.тит.англ.
Дополнительная литература:
Волков В.Б. Понятный самоучитель Excel 2010. – СПб.:Питер, 2010.-256с.:ил.
Кулешова О.В., Центр Компьютерного обучения «Специалист», Microsoft Excel 2010. Анализ и визуализация данных. Решения практических задач. Методическое пособие, 2012.
Ход работы
Задание 1. Работа с математическими функциями (Лист 1).
- Найти произведение чисел: 5,15,16,80,26,10,87,25,36,198. (Функция ПРОИЗВЕД). Числа расположите в диапазоне А1:А10, результат – в А11.
- Найти значение выражения: .
- Вычислить значение выражения: . (Функция СУММКВ).
- Как будут выглядеть числа 14587, 258, 4785, 26 преобразованные в римские числа? (функция РИМСКОЕ).
- Перемножьте матрицы: и . (функция МУМНОЖ).
Задание 2. Работа с логическими функциями (Лист 2).
1. Известен возраст двух человек – a, b. Определите, кто из них старше. (Функция ЕСЛИ).
2. В одну из ячеек будет введено число. В другой ячейке получить ответ на вопрос, является ли введенное число отрицательным.
3. В одну из ячейку будет введен год рождения первого человека, в другую ячейку — второго человека (значения годов рождения не равны между собой). Необходимо получить ответ на вопрос, кто старше — первый человек или второй.
4. Оформить лист для расчета значения у при заданном значении х:
Значение х должно вводиться в одну из ячеек.
- Торговый агент получает вознаграждение в размере некоторой доли от суммы совершенной сделки: если объем сделки до 5000 руб., то в размере 5%; если выше — 7%.
- В трех ячейках записаны числа. Если все они не нулевые, то вывести в какой-то четвертой ячейке 1, в противном случае — 0. Использовать сложное условие (функции и, или, НЕ).
Задание 3. Работа с текстовым функциями.
ДЛСТР возвращает количество знаков в текстовой строке.
ЗАМЕНИТЬ замещает указанную часть знаков текстовой строки другой строкой текста.
ЛЕВСИМВ возвращает указанное число знаков с начала текстовой строки.
Функция НАЙТИ находит вхождение одной текстовой строки (искомый_текст) в другую текстовую строку (просматриваемый_текст) и возвращает положение начала искомого текста относительно крайнего левого знака просматриваемого текста.
Функция ПОДСТАВИТЬ используется, когда нужно заменить определенный текст в текстовой строке.
ПОИСК Возвращает позицию первого вхождения знака или текстовой строки при поиске слева направо, начиная с нач_позиция.
ПРАВСИМВ возвращает заданное число последних знаков текстовой строки.
ПРОПИСН Делает все буквы в тексте прописными.
ПСТР возвращает указанное число знаков из текстовой строки, начиная с указанной позиции.
СТРОЧН Преобразует знаки в текстовой строке из верхнего регистра в нижний.
СЦЕПИТЬ Объединяет несколько текстовых строк в одну.
- В ячейку вводится слово из 8 букв. Поменять местами его половины (соответствующее слово получить в ячейке ВЗ).
- В ячейке записано некоторое слово, в котором имеются буквы "а". Найти номер позиции, которую занимает первая такая буква в слове.
- В ячейке (рис.) записаны два слова, разделенных одним пробелом (начальных пробелов нет). Получить первое и второе слово. Решение оформить в следующем виде.
- В ячейке получить текст, состоящий из фамилии и инициалов в виде Иванов Н.И. (рис.)
- В ячейке записано некоторое слово, в котором имеются идущие подряд две буквы "н". Найти номер позиции, с которой начинается первое из сочетаний этих букв.
Задание 4. Работа с функциями даты и времени (Лист 4).
- Заполните предложенную таблицу, используя функции:
ДЕНЬ Возвращает день в дате, заданной в числовом формате.
МЕСЯЦ Возвращает месяц в дате, заданной в числовом формате.
ГОД Возвращает год, соответствующий аргументу дата_в_числовом_формате.
- По дате, указанной в ячейке, определить номер дня недели, на который приходилась эта дата (понедельник — 1, вторник — 2, ..., воскресенье — 7). Определите день недели даты вашего рождения (Функция ДЕНЬНЕД).
- Определить количество рабочих дней в период с 01.06.2010 по 28.08.2010.
- Определите свой возраст в днях и неделях.
- Определить стаж работы.
|
Ячейка |
Формула |
Формат вывода |
Описание |
|
С4 |
=СЕГОДНЯ()-B4+1 |
ГГ |
Возраст |
|
Е4 |
=ГОД(СЕГОДНЯ()-D4)-1900+МЕСЯЦ(СЕГОДНЯ()-D4)/12 |
#0,00 |
Стаж работы |
Задание 5. Работа со статистическими функциями (Лист 5).
- Даны 20 чисел. Подготовить лист для определения количества чисел, больших некоторого значения, которое будет задаваться в отдельной ячейке. (функция СЧЕТЕСЛИ)
- На листе оформите сведения о дате рождения (в формате Дата) 10 учащихся группы. Определите количество учащихся, которые родились после некоторой даты, указанной под таблицей.
- Даны 20 чисел (среди которых есть отрицательные). Подготовить лист для определения количества чисел, больших суммы всех чисел.
Задание 6. Работа с финансовыми функциями (Лист 6).
При задании аргументов финансовых функций следует помнить следующее. Все аргументы, означающие расходы денежных средств (например, ежегодные платежи), представляются отрицательными числами, а аргументы, означающие поступления (например, дивиденды) - положительными числами. Все даты как аргументы функции имеют числовой формат. Для логических аргументов используются константы ИСТИНА или ЛОЖЬ, ли функции категории Дополнительные
- Рассчитать 20-летнюю ипотечную ссуду со ставкой 10% годовых при начальном взносе 25% и ежемесячной (ежегодной) выплате с помощью функции ППЛАТ.
- Определить, какая сумма окажется на счете, если вклад размером 1000000 руб. положен под 8 % годовых на 10 лет, а проценты начисляются ежеквартально. (функция БС(ставка/4;кпер*4;;-пс)).
- Какая сумма должна быть выплачена, если четыре года назад была выдана ссуда 200000 руб. под 20 % годовых с ежемесячным начислением процентов.
- Сколько лет потребуется, чтобы платежи размером 1000000 руб. в конце каждого года достигли значения 10,897 млн.руб., если ставка процента 14,5 %?
- Фонд размером 21 млн.руб. был сформирован за два года за счет отчислений по 770000 руб. в начале каждого месяца. Определить годовую ставку процента.
- Рассчитать будущую стоимость облигации номиналом 200000 руб., выпущенной на 8 лет, если в первые три года проценты начисляются по ставке 18 %, а в остальные четыре года по ставке 21 %.
- Какую сумму необходимо положить на депозит под 20 % годовых, чтобы через 3 года получить 90 млн. руб. при ежеквартальном начислении процентов?
- Капитальные затраты по проекту составляют 570 млн. руб., и ожидается, что его реализация принесет следующие доходы за три года: 270, 330, 290 млн. руб. соответственно. Издержки привлечения капитала равны 17 %. Определить чистую текущую стоимость проекта.
Задание 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 тыс. руб., при выполнении следующих ограничений:
- Цена детских билетов постоянна и равна 6000
- Цена билетов для взрослых равна 10000
- Цена льготных билетов равна 9000
- Билетов каждой категории можно продать не более 100 шт.
- В театре может быть аншлаг, но «лишних билетиков» нет.
Рабочий лист выглядит так:
Параметры окна Поиск решения:
Задание 13. Некий завод производит в смену суммарно не более 300 изделий. Необходимо определить при этом ограничении, а также при условии, что должно производиться не менее 30 шт. любого изделия, количество выпускаемых изделий таким образом, чтобы получить максимальный доход.
Рабочий лист имеет вид:
Задание 14. Прогноз аукционных продаж.
Подготовьте лист для расчетов
1. Определить для первого лота, сколько должно быть подано заявок, чтобы в течение 6 дней продаж среднее увеличение цены в день составляло 150000.
Вид окна Поиск решения:
2. Необходимо определить, какое количество заявок и количество дней продажи (по каждому лоту) обеспечат среднее за день возрастание цены (по всем лотам), равное 360000. При этом должны выполняться следующие ограничения:
- Количество заявок по любому лоту должно быть в пределах от 3 до 12
- Количество дней продажи – от 3 до 10
- Значения количеств заявок и количеств дней должны быть целыми числами.
Вид окна Поиск решения:
Задание 15. Расчет по банковским кредитам.
Подготовьте лист для расчетов:
Необходимо определить максимально возможный размер кредита, если сумма ежемесячных выплат не должна превышать 45 000, при этом известно, что процентная ставка не превысит 10%, а кредит берется на 60 месяцев.
Задание 16. Производственные расчеты
Подготовьте лист для расчетов:
- Бюджет по первому проекту ограничен суммой 1200000, при этом производственные и фиксированные расходы остаются неизменными, можно изменять только расходы на маркетинг и рекламу, но расходы на маркетинг должны быть не менее 120000, а расходы на рекламу не могут превышать 70000.
- Общий бюджет ограничен суммой 10000000, при этом производственные и фиксированные расходы всех проектов остаются неизменными, можно изменять только расходы на маркетинг и рекламу, но расходы на маркетинг должны быть не менее 120000, а расходы на рекламу не могут превышать 80000 на один проект. Кроме того, бюджет любого проекта не должен превышать суммы 1,2 млн.
2. Разоружение В 1891 году когда Америка делала опыты вызывания дождя голод в России и в Индии происшедший
3. тема управления объекты и субъекты управления
4. Тема 2. Міжнародне бізнессередовище та його вплив на прийняття рішень у транснаціональних компаніях Пита
5. Інформатика та інформаційні процеси
6. Национальные особенности этики делового поведения
7. комплексного анализатора
8. РЕФЕРАТ дисертації на здобуття наукового ступеня кандидата біологічних наук Київ '
9. лектики- по вопросу связей старого и нового если диалектика признает наличие связей между старым и н
10. Что такое базальная температура и как ее измерять
11. ю добу післяопераційного періоду
12. КИНОВУЗ
13. На тему- Обработка изображений с помощью пакета dobe Pнotoshop Воронеж 2006 г.html
14. Контрольная работа- Роль банків у міжнародних розрахунках
15. Изучение работы контроллеров.html
16. 28 ПОВТОРЕНИЕ Методический комментарий Последняя часть ваших занятий посвящена подготовке к рабо
17. тематические ошибки в мышлении или шаблонные отклонения в суждениях которые происходят в определённых ситу
18. тема соціального захисту на Україні у сучасних умовах постійно потребує до себе уваги з боку держави та суспі
19. і Кейбір атааналар т~рмысты~ ~арбаласты~ы себебінен баланы т~рбиелеуге нем~~райлы ~арап ~алатын жа~дайлар
20. Ru Все книги автора Эта же книга в других форматах Приятного чтения Г