Практическое занятие 6. Практическая работа Экономические расчеты в ms excel задание

• Название: Практическая работа Экономические расчеты в ms excel задание
• Дата: 26.11.2022
• Размер: 2.54 Mb.
• Имя файла: Практическое занятие 6.docx
• Тип: Практическая работа; #812945
• страница: 1 из 6

^{Практическая работа 6. Экономические расчеты в} MS Excel


ЗАДАНИЕ 1. АВТОЗАПОЛНЕНИЕ И ССЫЛКИ

1. 
   На диске D: в своей папке создайте папку Задание_1, в ней создайте файл MS Excel Ав-тозаполнение и ссылки. На листе Лист1, который назовите Автозаполнение, создайте таблицу по образцу:
   
2. 
   В первой строке объедините ячейки в столбцах с A по М. Для этого выделите нужный диапазон A1:М1 и на вкладке Главнаянажмите кнопку .
   
3. 
   В каждом из столбцов выделите заполненные ячейки, подведите курсор к нижнему
   

правому углу ячейки (курсор принимает вид « + » (маркер автозаполнения)). После это- го нажмите левую кнопку и, удерживая ее, «протяните» данные до 30 строки.

4. 
   Для заполнения столбца G повторяющейся датой «протяните» данные до конца выде- ленной таблицы, удерживая нажатой левую кнопку мыши и клавишу Ctrlклавиатуры.
   
5. 
   В столбце Н «протяните» данные до конца выделенной таблицы, удерживая нажатой правую кнопку мыши. В появившемся контекстном меню выберите пункт Заполнитьпорабочим дням. Посмотрите, как заполнились ячейки.
   
6. 
   Сохраните выполненные изменения.
   
7. 
   На листе Лист2, который назовите Относительныессылки,создайте таблицу по образцу:
   

8. 
   Оформите заголовок таблицы, используя технологию объединения нескольких ячеек в одну. Для этого выделите диапазон ячеек, которые нужно объединить. Выполните ко- манды: вкладка Главная  Выравнивание → вкладка Выравнивание и выберите коман- ду Объединениеячеек(или воспользуйтесь кнопкой на вкладке Главная
   

).

9. 
   Для оформления заголовков таблицы (ячейки А3:G3) на вкладке Главная выполните действия: Выравнивание → Формат ячеек → вкладка Выравнивание (выравнивание по горизонтали и вертикали – по центру, переносить по словам). Либо воспользуйтесь со- ответствующими командами на вкладке Главная.
   
10. 
    На других вкладках выберите оформление шрифта, границ и т.д.
    
11. 
    Для столбца Оплата в день задайте Денежный формат (вкладка Главная → Число →Денежныйсдвумядесятичнымизнаками).
    
12. 
    Заполните столбцы таблицы. Для этого в ячейку Е4 введите формулу для вычисления начисленной суммы: = С4*D4, т.е. оплату за один рабочий день умножьте на количе- ство отработанных дней (любая формула начинается со знака =). Заполните весь стол- бец с помощью маркера автозаполнения.
    
13. 
    Для заполнения столбца Удержано задайте в ячейке F4 формулу: = Е4*13 %. Заполни- те весь столбец с помощью маркера автозаполнения.
    
14. 
    Задайте формулу для нахождения значений столбца Получено на руки. Заполните весь столбец с помощью маркера автозаполнения.
    
15. 
    По данным столбцов Всего начислено и Получено на руки постройте гистограмму. Для этого выделите фамилии всех работников, начисленную сумму и размер суммы на руки (нажав клавишу Ctrl клавиатуры), после этого выполните команды: вкладка Вставка  Гистограмма. Отформатируйте диаграмму в соответствии с образцом (см. ниже).
    
16. 
    Необходимые изменения можно выполнить, используя вкладку Макет(добавьте название диаграммы, уберите горизонтальные линии сетки).
    

17. 
    Для того чтобы переименовать Ряд1 и Ряд2, на вкладке Конструктор выберите пункт Выбрать данные (либо воспользуйтесь контекстным меню, в котором выберите коман- ду Выбрать данные). В появившемся окне выделите Ряд1, затем выберите пункт Изме-нитьи впишите имя ряда Всегоначислено. Аналогично переименуйте Ряд2.
    
18. 
    На вкладке Конструктор нажмите на кнопку Переместить диаграмму и выберите пункт Наотдельномлисте. Этот лист назовите Ведомость.
    

19. 
    Для изменения ориентации подписи фамилий выделите эти подписи, затем в кон- текстном меню (вызовите его с помощью правой кнопки мыши) выберите пункт Фор-мат оси. На вкладке Выравнивание в пункте Направление текста выберите нужный вариант ориентации подписей.
    
20. 
    Сохраните выполненные изменения.
    
21. 
    На листе Лист3, который переименуйте в Абсолютные ссылки, создайте таблицу по образцу:
    
22. 
    В ячейку D6 введите формулу, вычисляющую стоимость израсходованного бензина: расход бензина (в л.) умножьте на стоимость одного литра бензина. Для ячейки с неиз- меняющимися данными задайте абсолютную адресацию (с помощью клавиши F4). Скопируйте формулу в оставшиеся ячейки таблицы с помощью маркера автозаполне- ния. При копировании формулы содержимое ячеек с абсолютной адресацией (цена бензина) не будет изменяться.
    
23. 
    Заполните ячейки строки ИТОГО. Для этого выделите ячейки С14 и D14 и нажмите на знак суммы  на панели инструментов.
    
24. 
    Измените цену бензина на актуальную. Посмотрите, как изменились данные в таблице.
    
25. 
    Закройте Ваш файл, сохранив изменения.
    
26. 
    В папке Задание_1 создайте файл MS Excel Табель учета. На листе Лист1, который назовите Табель, создайте таблицу по образцу:
    

27. 
    Заполните рабочие дни октября 2020 г (диапазон E5:Z5). Для этого выделите ячейку E5, подведите курсор к нижнему правому углу, нажмите правую кнопку мыши и, удерживая ее, «протяните» данные, после чего в появившемся контекстном меню вы- берите пункт Заполнить по рабочим дням.
    
28. 
    Задайте условие на проверку данных, вводимых в ячейки D6:D12. Для этого выделите нужный диапазон ячеек и на вкладке Данные выполните команду Проверка данных. На соответствующих вкладках диалогового окна внесите необходимые изменения, как это показано на рисунках:
    
29. 
    Заполните таблицу: введите оплату (руб./час). Попробуйте ввести значения, не входя- щие в указанный диапазон от 150 руб. до 300 руб., и убедитесь, что Вы не можете этого сделать. Тем самым пользователь может заранее снизить вероятность ошибок при вве- дении данных.
    
30. 
    Подсчитайте число дней явок для каждого сотрудника (не суммируя эти числа). Для этого выделите соответствующую ячейку таблицы для Борисовой О. В. (АА6), на вкладке Формулы выполните команды Вставить функцию  категория Статистиче-ские→ функция СЧЕТ. Появившееся окно заполните по образцу:
    
31. 
    Выполните автозаполнение для остальных сотрудников.
    
32. 
    Подсчитайте количество дней, проведенных каждым сотрудником в отпуске. Для этого воспользуйтесь функцией СЧЕТЕСЛИ (вкладка Формулы → Вставить функцию → категория Статистические). В качестве критерия введите «о» (появившееся окно за- полните по образцу (см. выше)).
    
33. 
    Аналогично подсчитайте количество дней, пропущенных по болезни («б»), а также прогулы («п»).
    
34. 
    Заполните столбец Отработаночасов(длительность каждого рабочего дня – 8 ч.) и
    

Начислено. Формулы задайте самостоятельно.

35. 
    Постройте круговую диаграмму, отражающую число отработанных часов каждым со- трудником. Оформите диаграмму по образцу, приведенному ниже.
    
36. 
    Постройте объемную гистограмму, отражающую начисленную сумму для каждого со- трудника. Оформите диаграмму по образцу, приведенному ниже.
    
37. 
    Сохраните изменения.
    
38. 
    На листе Лист2, который назовите Сортировка, создайте таблицу по образцу:
    

39. 
    Заполните все незаполненные столбцы и строки таблицы, задав самостоятельно необ- ходимые формулы.
    
40. 
    Скопируйте заполненную таблицу ниже ТРИ раза.
    
41. 
    Выделите строки таблицы, кроме строки ИТОГО и столбца № п/п, и выполните сорти- ровку (вкладка Данные  Сортировка):
    

а. первой скопированной таблицы по столбцам Фамилия,Имя,Отчествов алфавит- ном порядке (в диалоговом окне используйте пункт Добавить уровень);

б. второй скопированной таблицы по столбцу Январьв порядке убывания;

в. третьей скопированной таблицы по столбцу Суммаквыдачев порядке возрастания.

42. 
    Выделите цветом отсортированные данные.
    
43. 
    Сохраните выполненные изменения.
    
44. 
    Покажите выполненное задание преподавателю.
    

ЗА Д А Н И Е 2. СОЗДАНИЕ ВЕДОМОСТЕЙ

1. 
   На диске D: в своей папке создайте папку Задание_2, в ней создайте файл MS Excel Ве-домости. На листе Лист1, который назовите Ведомость переоценки, создайте таблицу по образцу:
   
2. 
   С помощью логической функцией ЕСЛИзаполните столбец для значения коэффициен- та kв соответствии с условием:
   
   • 
     k=3,3, если БС  700 млн.руб.;
     
   • 
     k=4,2, если 700 млн.руб. БС  1 000 млн.руб.;
     
   • 
     k=5,1, если БС  1 000 млн.руб.
     

Сначала введите формулу в ячейку Е5, затем скопируйте формулу в остальные ячейки столбца.

3. 
   Заполните остальные столбцы таблицы по следующим формулам: ОС = БС – ИО; ВПС = БС*k; ВОС = ОС*k.
   
4. 
   Создайте гистограмму по указанным данным и отформатируйте ее в соответствии с об- разцом:
   
5. 
   На листе Лист2, который назовите Отчетнаяведомость,создайте таблицу по образцу:
   

6. 
   Заполните таблицу, для этого самостоятельно задайте необходимые формулы. Для за- полнения столбца Суммарнаявыручкавоспользуйтесь кнопкой Автосуммирование на вкладке Главная.
   
7. 
   Для заполнения столбцов Место и Средняя выручка воспользуйтесь статистическими функциями РАНГи СРЗНАЧ(вкладка Формулы категорияСтатистические).
   
8. 
   Заполните последний столбец, задав формулу самостоятельно.
   
9. 
   Создайте свой пользовательский формат данных. Для этого выполните команды вклад- ка Число – Все форматы (вкладка Главная). В поле Тип введите следующую конструк- цию: # ###,00" тыс.руб.".
   
10. 
    Ко всем данным, представляющим собой денежные значения, примените данный фор- мат.
    
11. 
    По данным последнего столбца создайте круговую диаграмму.
    

12. 
    Лист3 переименуйте в Ведомость зарплатыи создайте на нем электронную таблицу по образцу, приведенному на рисунке.
    

13. 
    Остальные столбцы ведомости заполните с помощью соответствующих формул, с ис- пользованием функций и автозаполнения.
    
14. 
    Столбец Тарифная ставка заполните с помощью логической функции ЕСЛИ (вкладка Формулы категория Логические) с учетом следующих тарифов: лаборант – 15 670 руб., инженер – 28 500 руб., мл.н.сотрудник – 25 700 руб., ст.н.сотрудник – 29 700 руб., зав.лабораторией – 31 500 руб. (сначала введите формулу в ячейку D5, при этом вы- полните проверку логического условия только для одной ячейки С5, затем заполните остальные ячейки столбца с помощью маркера автозаполнения).
    
15. 
    С помощью логической функции ЕСЛИ заполните столбец k, присваивая значения ко- эффициента из расчета: отработано до 5 лет включительно – 0,1, от 5 до 7 лет включи- тельно – 0,15, от 7 до 10 лет включительно – 0,2, от 10 до 15 лет включительно – 0,25, свыше 15 лет – 0,3.
    
16. 
    Столбцы Надбавка за стаж и Итого заполните, задав для вычислений соответствую- щие формулы.
    
17. 
    Столбец Процент налога заполните с помощью логической функции ЕСЛИ по значе- ниям столбца Итого в соответствии со следующей сеткой: доход до 20 000 руб. вклю- чительно облагается налогом 8 %, доход от 20 000 до 30 000 руб. включительно – 11 %, от 30 000 руб. до 35 000 руб. включительно – 13 %, свыше 35 000 руб. – 15 %.
    
18. 
    Заполните два последних столбца, самостоятельно задав в них нужные формулы для вычислений.
    
19. 
    Для соответствующих данных задайте денежный и процентный формат.
    
20. 
    Создайте пользовательский формат данных, учитывающий стаж работы: до 5 лет – данные представлены синим цветом, от 5 до 10 лет – зеленым цветом, от 10 до 15 лет – красным цветом. Для этого выделите ячейки Е5:Е19 и на вкладке Главная выполните команды: Условное форматирование  Создать правило  Форматировать толькоячейки,которыесодержат Формат….
    
21. 
    Создайте сообщение об ошибке в случае ошибочного ввода отрицательного количества лет в столбец Стаж. Для этого выделите ячейки D5:D19 и на вкладке Данные выпол- ните команды Проверка данных  Сообщение об ошибке: «СТАЖ НЕ МОЖЕТ БЫТЬ ОТРИЦАТЕЛЬНЫМ!». На вкладке Параметрызадайте необходимое условие.
    
22. 
    Для этого же поля Тарифная ставка создайте сообщение: «БУДЬТЕ ВНИМАТЕЛЬНЫ ПРИ ВВОДЕ ТАРИФНОЙ СТАВКИ!» Для этого выделите ячейки D5:D19 и выполните команду Проверка данных (вкладка Данные)  на вкладке Сообщение для ввода задай- те нужное сообщение.
    

23. 
    По данным последнего столбца создайте гистограмму.
    

24. 
    Сохраните выполненные изменения.
    
25. 
    Покажите выполненное задание преподавателю.
    

ЗА Д А Н И Е 3. ПРОДАЖИ

1. 
   На диске D: в своей папке создайте папку Задание_3, в ней создайте файл MSExcelПродажи.
   
2. 
   
   На листе Лист1, который назовите Цены, создайте электронную таблицу по образцу, приведенному ниже:
   
3. 
   Добавьте семь аналогичных записей.
   
4. 
   Создайте примечание (комментарий) для каждого товара (для каждой ячейки) столбца Код. Для этого выделите ячейку, в которую следует ввести комментарий, и на вкладке Рецензирование выберите команду Создать примечание (либо выберите нужную ко- манду в контекстном меню, которое вызывается правой кнопкой мыши). В появившем-
   

ся окне примечания введите необходимый текст. Примечание должно содержать сле- дующую информацию: «Серийный № *****. Изготовлен **.**.** г. Контракт о постав- ке № ***».

5. 
   Выберите Форматценв валюте соответствующей страны-поставщика.
   
6. 
   Произведите расчеты в столбце Ценаврублях(формулу задайте самостоятельно).
   
7. 
   Установите формат для значений столбца Ценаврубляхв соответствии с условиями:
   
   • 
     для значений цены <=5 000 руб. установите цвет отображения – красный;
     
   • 
     для значений цены от 5 000 до 10 000 руб. установите цвет отображения – синий;
     
   • 
     для значений цены >=10 000 руб. установите цвет отображения – зеленый.
     

Для этого выделите диапазон ячеек I2:I16 и на вкладке Главная выполните команды: Условное форматирование Создать правило Форматировать только ячейки,которые содержат  Формат…. Установите первое условие в появившемся окне для поля значения. Нажмите на кнопку Формат и выберите цвет для этого условия. Повто- рите шаги для других условий.

8. 
   Выделите и скопируйте всю таблицу на лист Лист2 Вашей рабочей книги. Назовите этот лист Продажи_2.
   
9. 
   В скопированной таблице присвойте имена столбцам: Цена в валюте и Курс валюты. Для этого выделите диапазон ячеек с данными столбца Цена в валюте для присвоения имени. На вкладке Формулы выберите команду Присвоить имя. Введите любое имя (не совпадающее с адресами ячеек), и не имеющее пробелов, например, имя Цена. Имена создают абсолютные ссылки. По аналогии присвойте соответствующему диапазону ячеек имя Курс.
   
10. 
    Вычислите цену в рублях в скопированной таблице, используя в качестве ссылок на данные их Имена. Для этого установите курсор ввода в первую ячейку столбца Цена врубляхи введите формулу =Цена * Курс.
    
11. 
    Во 2-й таблице отобразите на экране одно какое-либо примечание. Для этого установи- те курсор в ячейку, имеющую примечание, и на вкладке Рецензирование выберите ко- манду Показать всепримечания.
    
12. 
    На первом листе отобразите влияющие ячейки. Для этого установите курсор в ячейку I16 и на вкладке Формулы выберите команду Влияющие ячейки. Появятся стрелки, ука- зывающие, от каких ячеек зависит значение в ячейке I16. Эта информация может ока- заться удобной при поиске ошибок при расчетах.
    
13. 
    Отобразите на экране формулы электронной таблицы. Для этого на вкладке Формулы
    

выберите команду Показать формулы.

14. 
    Покажите выполненное задание преподавателю.
    

ЗА Д А Н И Е 4. КВАРТПЛАТА И ЗАРПЛАТА

1. 
   На диске D: в своей папке создайте папку Задание_4, в ней создайте файл MS ExcelКвартплатаи зарплата.
   
2. 
   На листе Лист1, который назовите Квартплата, рассчитайте размеры ежемесячной оплаты квартиры ее владельцами в зависимости от площади, наличия и качества предо- ставляемых удобств. Для этого создайте электронную таблицу по образцу, приведен- ному ниже:
   

3. 
   Оформите таблицу, используя команды пункта Выравниваниена вкладке Главная.
   
4. 
   Содержимое ячеек С2:С6 и G11:J16 представьте в денежном формате.
   
5. 
   Содержимое ячейки С7 представьте в процентном формате.
   
6. 
   Подсчитайте количество владельцев квартир, в которых установлены электроплиты, газовые колонки и телефоны. Для этого в соответствующих ячейках D16, E16 и F16 введите следующие формулы (вкладка Формулы  категория Статистические):
   
   • 
     в ячейке D16 = СЧЁТЕСЛИ(D11:D15;"+")
     
   • 
     в ячейке Е16 = СЧЁТЕСЛИ(E11:E15;"+");
     
   • 
     в ячейке F16 = СЧЁТЗ(F11:F15) (рассчитывается как число непустых ячеек).
     
7. 
   Платежи для квартиры № 1 определяются следующим образом:
   
   • 
     коммунальные услуги