Практическое занятие 6. Практическая работа Экономические расчеты в ms excel задание
 Скачать 2.54 Mb.
|
в ячейке G11 = B11*$C$6*ЕСЛИ(E11="+";1+$C$7;1);оплата за газ в ячейке Н11 = ЕСЛИ(D11<>"+";1;0)*C11*ЕСЛИ(E11="+";$C$5;$C$4);оплата за телефон в ячейке I11 = ЕСЛИ(F11="о";$C$2;ЕСЛИ(F11="с";$C$3;0)).Заполните остальные ячейки с помощью маркера автозаполнения. Просуммируйте данные ячеек по столбцам (для этого выделите нужное количество ячеек и нажмите знак Автосуммирования на вкладке Главная). Содержимое ячеек G11:I15 представьте в денежном формате. Покажите выполненное задание преподавателю. На листе Лист2 рассчитайте распределение фонда заработной платы, если известен общий фонд заработной платы, коэффициент трудового участия сотрудника и его ми- нимальная заработная плата (такая ситуация может произойти, если, например, увели- чен фонд заработной платы, но не должно получиться, что сотрудник будет получать ниже установленной заработной платы; таким образом, между сотрудниками будет де- литься в соответствии с коэффициентом трудового участия не весь фонд, а только та его часть, которая больше суммы всех прежних зарплат). Для этого на листе Лист2, который назовите Распределение фонда зарплаты, создайте таблицу по образцу, приведенному на рисунке.  Оформите таблицу. Содержимое ячеек С5 и F7:G17 представьте в денежном формате. Фонд зарплаты распределяется между сотрудниками в зависимости от устанавливаемо- го руководителем подразделения КТУ (коэффициент трудового участия – индивиду- альная оценка качества труда каждого работника) и в зависимости от его минимальной (прежней) заработной платы. Для определения новой зарплаты сначала нужно найти «удельный вес» единицы КТУ в денежном исчислении для той части фонда заработной платы, которая больше суммы всех прежних зарплат, а затем умножить его на индиви- дуальный коэффициент работника и прибавить прежнюю зарплату. Все вычисления для работника Антонова Р.И. определяются следующим образом: Сумма всех коэффициентов в ячейке D17 = СУММ(D7: D16); Начислено в ячейке F7 = E7*B7/$C$3; Надбавка в ячейке G7 = ($C$5-$F$17)/$D$17*D7; Новая зарплата в ячейке Н7 = F7+G7. Заполните остальные ячейки с помощью маркера автозаполнения. Просуммируйте данные ячеек по столбцам и убедитесь, что получена сумма – фонд за- работной платы. Содержимое ячеек F7:H17 представьте в денежном формате. По данным последнего столбца таблицы создайте круговую диаграмму (гистограмму). Оформите ее. Покажите выполненное задание преподавателю.  На листе Лист3, который назовите Сдельная зарплата, рассчитайте размеры сдельной заработной платы, зависящей от объема выполненной работы, а также налог и сумму, полученную на руки каждым из работников. Для этого создайте таблицу по образцу, приведенному на рисунке.Оформите заголовок таблицы, используя технологию объединения нескольких ячеек в одну. Оформите таблицу, используя возможности диалогового окна Форматячеек. Со- держимое ячеек С2:С3 представьте в денежном формате (Формат ячеек → вкладка Число → Денежный формат). Содержимое ячеек С1 и F1 представьте в процентном формате (Форматячеек→ вкладка Число→ Процентныйформат). Сдельная зарплата зависит от количества и качества произведенной продукции. Она определяется следующим образом: число обработанных деталей умножается на стои- мость ее обработки. Если работник допустил брак и испортил деталь, то ее стоимость вычитается из заработка. Все вычисления для работника Иванова В.А. определяются следующим образом: Зарплата в ячейке D7: =B7*$C$2 – C7*$C$3; Сумма налога в ячейке Е7: =D7*ЕСЛИ(D7>=5000;$F$1;$C$1); Сумма на руки задайте самостоятельно. Заполните остальные ячейки с помощью маркера автозаполнения. Просуммируйте данные ячеек по столбцам. Содержимое ячеек D7:F12 представьте в денежном формате.  По данным трех последних столбцов таблицы создайте столбчатую диаграмму (гисто- грамму) и оформите ее соответствующим образом.Покажите выполненное задание преподавателю. ЗА Д А Н И Е 5. ПОСТРОЕНИЕ ГРАФИКОВ  На диске D: в своей папке создайте папку Задание_5, в ней создайте файл MS ExcelГрафики. На листе Лист1, который назовите График_1, создайте таблицу по образцу. Значения аргумента хзаполните в диапазоне от – 4 до 4 с шагом 0,1.В соседнем столбце увычислите значения функции по формуле y 5sin xcos(3x1) .  Постройте график функции с помощью мастера диаграмм (вкладка Вставка диа- грамма Точечная), предварительно выделив нужный диапазон ячеек.  Отформатируйте диаграмму по образцу. В опции Форматрядовданныхвыберите тип линии Сглаженнаялиния.На листе Лист2, который назовите График_2, постройте график функции 4sin( 4) , заданной в полярных координатах. Для этого сначала вычислите значе- ния данной функции для 0 2 , затем по соответствующим формулам перехода вы- числите значения хи ув декартовых координатах. Вычисления оформите по образцу:  С помощью маркера автозаполнения заполните в столбце А нужные значения ,где 0 2 . В столбце В задайте формулу для вычисления заполнения заполните нужный диапазон. 4sin( 4) . С помощью маркера авто- В ячейках D4 и Е4 введите формулы для вычисления значений декартовых координат: x cos и y sin . С помощью маркера автозаполнения заполните нужные диа- пазоны значений хи у.  Постройте график функции с помощью мастера диаграмм (вкладка Вставка диа- грамма Точечная), предварительно выделив нужный диапазон ячеек х и у. Отформати- руйте диаграмму по образцу. В опции Форматрядов данныхвыберите тип линии Сглаженнаялиния.На листе Лист3, который назовите График_3, постройте поверхность, задаваемую формулой z x2 sin x 2 cos( y2 3) . Для этого задайте диапазон изменения перемен- ных хи уот – 2 до 2 с шагом 0,25. Вычисления оформите по образцу.  Для ячейки В5 задайте формулу = $A5^2*SIN($A5) + 2*COS(B$4^2– 3) (для того чтобы задать смешанную ссылку с помощью знака $, воспользуйтесь клави- шей F4), затем с помощью маркера автозаполнения заполните нужный диапазон.  Постройте график функции с помощью мастера диаграмм (вкладка Вставка диа- грамма Поверхность), предварительно выделив нужный диапазон ячеек. Отформати- руйте диаграмму по образцу.Щелкнув по области построения диаграммы правой кнопкой мыши, выберите пункт Поворот объемной фигуры… В появившемся окне Формат области диаграммы вы- полните различные преобразования по изменению вида диаграммы.  На листе Лист4, который назовите График_4, постройте сферу, которая определяется формулой x2 y2 z2 1 . Для этого в диапазон В4:В45 введите значения от – 1 до 1 с шагом 0,1, дублируя их последовательно дважды (значения нужно ввести «вручную»). Диапазон С3:АR3 заполните следующим образом: скопируйте значения из диапазона В4:В45, выделите ячейку С3 и на вкладке Главная выберите пункт Вставить Транс-понировать. В столбец А введите вспомогательные числа 2 и 3, чередуя их.  Для ячейки С4 задайте формулу для вычислений = КОРЕНЬ(1–$B4^2–C$3^2)*ЕСЛИ(ОСТАТ($A5;2)=0;1;–1), затем с помощью мар- кера автозаполнения заполните нужный диапазон.  Постройте график функции с помощью мастера диаграмм (вкладка Вставка диа- грамма Поверхность), предварительно выделив нужный диапазон ячеек. Отформати- руйте диаграмму по образцу.Покажите выполненное задание преподавателю. Самостоятельно постройте графики следующих функций: y 3cos(2x)sin( 5x) ;  y 5x2  1 x2 , x 0, ; , x 0. кардиоида 8 (1 cos); лемниската Бернулли 2 2 cos 2 ; гиперболический параболоид х2 у2 2z 0 ; однополостный гиперболоид х2 у2 z2 1.       Покажите выполненное задание преподавателю. ЗА Д А Н И Е 5. СОРТИРОВКА И ФИЛЬТРАЦИЯ На диске D: в своей папке создайте папку Задание_7, в ней создайте файл MSExcelСортировкаи фильтрация. На листе Лист1, который назовите Таблица, создайте таблицу по образцу:
Скопируйте таблицу на 9 листов. Для этого щелкните правой кнопкой мыши по ярлыку листа Таблицаи в контекстном меню выберите команду Переместить/Скопировать. В появившемся диалоговом окне выберите, куда скопировать лист с таблицей (перед листом Лист2). И выставьте флажок Создатькопию.   На листе Лист2, который назовите Сортировка_1, выберите любую ячейку в первом столбце, вкладка Данные Сортировка от А до Я. (Обратите внимание, что фамилии продавцов не отсортированы по алфавиту). Выберите любую ячейку в таблице, на вкладке Данные нажмите кнопку Сортировка. Укажите, что сортировать необходимо по первому (Месяц) и второму (Продавец) столбцам. Причем первый столбец должен быть отсортирован по порядку следования месяцев в году (Порядок Настраиваемый список). На листеЛист3, который назовите Фильтр_Продавец,используя меню Данные Фильтр, выберите все данные по Петрову П. П. и Иванову И. И. На листеЛист4, который назовите Фильтр_Товар,используя меню Данные Фильтр, выберите все данные по товарам Чай и Сахар. На листеЛист5, который назовите Фильтр_Регион,используя меню Данные Фильтр, выберите все данные по Востоку и Западу. На листеЛист6, который назовите Фильтр_Месяц,используя меню Данные Фильтр, выберите все данные по Январю. На листе Лист7, который назовите Фильтр_Сумма,используя меню Данные Фильтр, выберите в таблице всех продавцов, у кого сумма больше 10 000 (Числовыефильтры больше). На листе Лист8, который назовите Фильтр_1, ниже Вашей первой таблицы создайте следующие таблицы (при заполнении новой таблицы используйте копирование заго- ловков первой таблицы)
Установите курсор в любую ячейку исходной таблицы. Выберите в меню Данныекнопку  . В появившемся диалоговом окне установите опцию «Скопиро-ватьрезультатвдругоеместо», в поле Диапазонусловий выделите ячейки таблицы
В поле Поместитьрезультатвдиапазонвыделите ячейки новой таблицы
Нажмите ОК (или Enter). Таблица (Продавец, Товар, Сумма) должна заполниться ин- формацией. На листе Лист9, который назовите Сводные данные, выберите любую ячейку в первой таблице и используйте пункт меню Вставка Сводная таблица. В макете «перетащи- те» поле Месяц на окно Фильтр отчета, поля Продавец и Регион «перетащите» в окно Названия строк, поле Товар «перетащите» в Названия столбцов, поле Сумма – в окно Значения. Попробуйте «перетаскивать» столбцы в полученной таблице разными способами. Сохраните выполненное задание. Покажите выполненное задание преподавателю. ЗА Д А Н И Е 6. ОБЪЕДИНЕНИЕ (КОНСОЛИДАЦИЯ) И СВЯЗЫВАНИЕ ТАБЛИЦ В MS ЕХСЕL Инструмент Консолидация позволяет объединить таблицы, находящиеся в разных ме- стах одного листа, также на разных листах одной рабочей книги и даже в разных рабочих книгах. На диске D: в своей папке создайте папку Задание_8, в ней создайте файл MS ExcelОбъединениеи связывание. На листе Лист1, который назовите Январь, создайте таблицу по образцу:
Придумайте свое название для торговой фирмы и запишите его вместо ***. Заполните нужными формулами пустые столбцы. Скопируйте эту таблицу на листы Лист2 и Лист3, которые назовите соответственно Февральи Март. Измените данные исходной таблицы (названия товаров оставьте без изменения). Перейдите на лист Лист4, который назовите Итог, и создайте таблицу по образцу:
Скопируйте названия товаров в первый столбец созданной таблицы из таблицы на ли- сте Январь. Выделите пустые столбцы в сводной таблице на листе Итоги выберите на вкладке Данные команда Консолидация.  В появившемся диалоговом окне выберите функцию Сумма. В поле Ссылка укажите данные из таблиц с листа Январь. Для этого перейдите на лист Январь и выделите дан- ные в столбцах Всего и Выручка(диапазон G4:Н8).После выделения соответствующего диапазона нажмите кнопку Добавить. Аналогичные действия проделайте с листами Февральи Март. Обратите внимание на адреса полученных ссылок. Установите флажок Создаватьсвязисисходнымиданными. Обратите внимание на символы структуры в верхней левой части экрана. Опробуйте эти кнопки, обращая внимание на результат. Сохраните файл. Покажите выполненное задание преподавателю.  В папке Задание_8 создайте файл MSExcelКонсолидация. Создайте в нем таблицу по образцу:Заполните строку ИТОГО(с помощью кнопки Автосуммирование  на вкладкеГлавная). Скопируйте эту таблицу 3 раза на новые листы. Для этого щелкните правой кнопкой по названию первого листа и в появившемся контекстном меню выберите команду Пере-местить/скопировать Переместить в конец. Отметьте флажком пункт Создать ко-пию. Листы переименуйте соответственно в Октябрь, Ноябрь, Декабрь, Всего за IV квартал. Для этого щелчком правой кнопки мыши по ярлыку листа вызовите контекстное меню, в котором выберите команду Переименовать, после чего введите новое название листа. Не забудьте внести соответствующие изменения в заголовок таблицы на каждом листе. Заполните таблицу на каждом из листов Ноябрьи Декабрьизмененными данными. После заполнения всех таблиц выполните консолидацию (объединение) данных. Для этого на последнем листе Всего за IV квартал выделите диапазон пустых ячеек и на вкладке Данные выберите команду Консолидация. В появившемся диалоговом окне в поле Функциявыберите Сумма. Затем для выбора нужной ссылки щелкните по названию листа Октябрьи выделите диапазон В6:D11. После чего нажмите Добавить. Добавьте соответствующие диапазоны с листов Ноябрь,Декабрь. Установите флажок Создаватьсвязисисходнымиданными, после чего нажмите ОК. Появившиеся данные на листе Всего за IV квартал представляют собой сумму данных соответствующих ячеек таблиц с Вашими данными. Итоговые данные будут автоматически изменяться при внесении Вами каких-либо из- менений. Запомните какой-либо результат и внесите на одном из листов изменения. Обратите внимание на то, что в итоговой таблице на листе Всего за IV квартал данные тоже изменятся. Обратите внимание на символы структуры в верхней левой части рабочей области. Опробуйте эти кнопки, нажимая на них и обращая внимание на результат. Сохраните выполненные изменения. Покажите выполненное задание преподавателю. ЗА Д А Н И Е 7. ПРОГНОЗИРОВАНИЕ В MS ЕХСЕL На диске D: в своей папке создайте папку Задание_9, в ней создайте файл MS ExcelПрогноз. На листе Лист1, который назовите Тренд_1, создайте таблицу по образцу:
Постройте график по этим данным. Для этого выделите таблицу, на вкладке Вставка выберите График.   Выделите ряд данных, в контекстном меню выберите Добавить линию тренда, укажите тип Полиномиальный, 4-й степени. В Параметрах установите Прогноз на один периодвперед.Просмотрите результат. Попробуйте другие параметры и типы линии тренда. Оформите диаграмму самостоятельно. Сохраните выполненные изменения. На листе Лист2, который назовите Тренд_2, создайте таблицу по образцу: | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||