Файл: A по М. Для этого выделите нужный диапазон Мина вкладке Главная нажмите кнопку в каждом из столбцов выделите заполненные ячейки, подведите курсор к нижнему правому углу ячейки курсор принимает вид .pdf

5 ЗАДАНИЕ. АВТОЗАПОЛНЕНИЕ И ССЫЛКИ
1. На диске D: в своей папке создайте папку Задание, в ней создайте файл MS Excel Ав-
тозаполнение и ссылки. На листе Лист, который назовите Автозаполнение, создайте таблицу по образцу
2. Впервой строке объедините ячейки в столбцах с A по М. Для этого выделите нужный диапазон Мина вкладке Главная нажмите кнопку
3. В каждом из столбцов выделите заполненные ячейки, подведите курсор к нижнему правому углу ячейки (курсор принимает вид « + » (маркер автозаполнения)). После этого нажмите левую кнопку и, удерживая ее, протяните данные до 30 строки.
4. Для заполнения столбца G повторяющейся датой протяните данные до конца выделенной таблицы, удерживая нажатой левую кнопку мыши и клавишу Ctrl клавиатуры.
5. В столбце Н протяните данные до конца выделенной таблицы, удерживая нажатой правую кнопку мыши. В появившемся контекстном меню выберите пункт Заполнить по рабочим дням. Посмотрите, как заполнились ячейки.
6. Сохраните выполненные изменения.
7. На листе Лист, который назовите Относительные ссылки создайте таблицу по образцу
8. Оформите заголовок таблицы, используя технологию объединения нескольких ячеек в одну. Для этого выделите диапазон ячеек, которые нужно объединить. Выполните команды вкладка Главная

Выравнивание → вкладка Выравнивание и выберите команду Объединение ячеек (или воспользуйтесь кнопкой на вкладке Главная
).
9. Для оформления заголовков таблицы (ячейки А) на вкладке Главная выполните действия Выравнивание → Формат ячеек → вкладка Выравнивание (выравнивание по горизонтали и вертикали – по центру, переносить по словам. Либо воспользуйтесь соответствующими командами на вкладке Главная.

6 10. На других вкладках выберите оформление шрифта, границ и т.д.
11. Для столбца Оплата вдень задайте Денежный формат (вкладка Главная → Число → Денежный с двумя десятичными знаками.
12. Заполните столбцы таблицы. Для этого в ячейку Е введите формулу для вычисления начисленной суммы = С, те. оплату за один рабочий день умножьте на количество отработанных дней(любая формула начинается со знака =). Заполните весь столбец с помощью маркера автозаполнения.
13. Для заполнения столбца Удержано задайте в ячейке F4 формулу = Е %. Заполните весь столбец с помощью маркера автозаполнения.
14. Задайте формулу для нахождения значений столбца Получено на руки. Заполните весь столбец с помощью маркера автозаполнения.
15. Поданным столбцов Всего начислено и Получено на руки постройте гистограмму. Для этого выделите фамилии всех работников, начисленную сумму и размер суммы на руки нажав клавишу Ctrl клавиатуры, после этого выполните команды вкладка Вставка Гистограмма. Отформатируйте диаграмму в соответствии с образцом (см. ниже.
16. Необходимые изменения можно выполнить, используя вкладку Макет (добавьте название диаграммы, уберите горизонтальные линии сетки.
17. Для того чтобы переименовать Ряди Ряд, на вкладке Конструктор выберите пункт Выбрать данные (либо воспользуйтесь контекстным меню, в котором выберите команду Выбрать данные. В появившемся окне выделите Ряд, затем выберите пункт Изменить и впишите имя ряда Всего начислено. Аналогично переименуйте Ряд.
18. На вкладке Конструктор нажмите на кнопку Переместить диаграмму и выберите пункт На отдельном листе. Этот лист назовите Ведомость.

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

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

9 34. Заполните столбец Отработано часов (длительность каждого рабочего дня – 8 ч) и Начислено. Формулы задайте самостоятельно.
35. Постройте круговую диаграмму, отражающую число отработанных часов каждым сотрудником. Оформите диаграмму по образцу, приведенному ниже.
36. Постройте объемную гистограмму, отражающую начисленную сумму для каждого сотрудника. Оформите диаграмму по образцу, приведенному ниже.
37. Сохраните изменения.
38. На листе Лист, который назовите Сортировка, создайте таблицу по образцу
39. Заполните все незаполненные столбцы и строки таблицы, задав самостоятельно необходимые формулы.
40. Скопируйте заполненную таблицу ниже ТРИ раза.
41. Выделите строки таблицы, кроме строки ИТОГО и столбца № п/п, и выполните сортировку (вкладка Данные

Сортировка а. первой скопированной таблицы по столбцам Фамилия, Имя, Отчество в алфавитном порядке (в диалоговом окне используйте пункт Добавить уровень б. второй скопированной таблицы по столбцу Январь в порядке убывания в. третьей скопированной таблицы по столбцу Сумма к выдаче в порядке возрастания.
42. Выделите цветом отсортированные данные.
43. Сохраните выполненные изменения.
44. Покажите выполненное задание преподавателю.

ЗАДАНИЕ. СОЗДАНИЕ ВЕДОМОСТЕЙ. На диске Z: в своей папке создайте папку Задание, в ней создайте файл MS Excel Ведомости. На листе Лист, который назовите Ведомость переоценки, создайте таблицу по образцу. С помощью логической функцией ЕСЛИ заполните столбец для значения коэффициента в соответствии с условием
k = 3,3, если БС

700 млн.руб.;

k = 4,2, если 700 млн.руб.

БС

1 000 млн.руб.;

k = 5,1, если БС

1 000 млн.руб.
Сначала введите формулу в ячейку Е, затем скопируйте формулу в остальные ячейки столбца. Заполните остальные столбцы таблицы последующим формулам ОС = БС – ИО;
ВПС = БС*k; ВОС = ОС. Создайте гистограмму по указанным данными отформатируйте ее в соответствии с образцом. На листе Лист, который назовите Отчетная ведомость создайте таблицу по образцу
6. Заполните таблицу, для этого самостоятельно задайте необходимые формулы. Для заполнения столбца Суммарная выручка воспользуйтесь кнопкой Автосуммирование на вкладке Главная.
7. Для заполнения столбцов Место и Средняя выручка воспользуйтесь статистическими функциями РАНГ и СРЗНАЧ вкладка Формулы

категория Статистические.
8. Заполните последний столбец, задав формулу самостоятельно.
9. Создайте свой пользовательский формат данных. Для этого выполните команды вкладка Число – Все форматы (вкладка Главная. В поле Тип введите следующую конструкцию" тыс.руб.".
10. Ко всем данным, представляющим собой денежные значения, примените данный формат. Поданным последнего столбца создайте круговую диаграмму.
12. Лист переименуйте в Ведомость зарплаты и создайте на нем электронную таблицу по образцу, приведенному на рисунке.

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

13 23. Поданным последнего столбца создайте гистограмму.
24. Сохраните выполненные изменения.
25. Покажите выполненное задание преподавателю. ЗАДАНИЕ. ПРОДАЖИ
1. На диске D: в своей папке создайте папку Задание, в ней создайте файл MS Excel Продажи.
2. На листе Лист, который назовите Цены, создайте электронную таблицу по образцу, приведенному ниже
3. Добавьте семь аналогичных записей.
4. Создайте примечание (комментарий) для каждого товара (для каждой ячейки) столбца
Код. Для этого выделите ячейку, в которую следует ввести комментарий, и на вкладке Рецензирование выберите команду Создать примечание (либо выберите нужную команду в контекстном меню, которое вызывается правой кнопкой мыши. В появившемся окне примечания введите необходимый текст. Примечание должно содержать следующую информацию Серийный № *****. Изготовлен **.**.** г. Контракт о поставке.
ВыберитеФормат цен в валюте соответствующей страны-поставщика.
6. Произведите расчеты в столбце Цена в рублях (формулу задайте самостоятельно.
7. Установите формат для значений столбцаЦена в рублях в соответствии с условиями
 для значений цены <=5 000 руб. установите цвет отображения красный
 для значений цены от 5 000 до 10 000 руб. установите цвет отображения синий
 для значений цены >=10 000 руб. установите цвет отображения зеленый. Для этого выделите диапазон ячеек I2:I16 и на вкладке Главная выполните команды Условное форматирование

Создать правило

Форматировать только ячейки, которые содержат

Формат….Установите первое условие в появившемся окне для полязначения. Нажмите на кнопкуФормат и выберите цвет для этого условия. Повторите шаги для других условий.
8. Выделите и скопируйте всю таблицу на лист Лист Вашей рабочей книги. Назовите этот лист Продажи.
9. В скопированной таблице присвойте имена столбцам Цена в валюте и Курс валюты. Для этого выделите диапазон ячеек сданными столбца Цена в валюте для присвоения имени. На вкладке Формулы выберите команду Присвоить имя. Введите любое имя (не совпадающее с адресами ячеек, и не имеющее пробелов, например, имя Цена. Имена создают абсолютные ссылки. По аналогии присвойте соответствующему диапазону ячеек имя Курс.
10. Вычислите цену в рублях в скопированной таблице, используя в качестве ссылок на данные их Имена. Для этого установите курсор ввода в первую ячейку столбца Цена в рублях и введите формулу = Цена * Курс.
11. Вой таблице отобразите на экране одно какое-либо примечание. Для этого установите курсор в ячейку, имеющую примечание, и на вкладке Рецензирование выберите команду Показать все примечания.
12. На первом листе отобразите влияющие ячейки. Для этого установите курсор в ячейку
I16 и на вкладке Формулы выберите команду Влияющие ячейки. Появятся стрелки, указывающие, от каких ячеек зависит значение в ячейке I16. Эта информация может оказаться удобной при поиске ошибок при расчетах.
13. Отобразите на экране формулы электронной таблицы. Для этого на вкладке Формулы выберите команду Показать формулы.
14. Покажите выполненное задание преподавателю. ЗАДАНИЕ. КВАРТПЛАТА И ЗАРПЛАТА
1. На диске D: в своей папке создайте папку Задание, в ней создайте файл MS Excel Квартплата и зарплата.
2. На листе Лист, который назовите Квартплата, рассчитайте размеры ежемесячной оплаты квартиры ее владельцами в зависимости от площади, наличия и качества предоставляемых удобств. Для этого создайте электронную таблицу по образцу, приведенному ниже

15 3. Оформите таблицу, используя команды пункта Выравнивание на вкладке Главная.
4. Содержимое ячеек С2:С6 и G11:J16 представьте в денежном формате.
5. Содержимое ячейки С представьте в процентном формате.
6. Подсчитайте количество владельцев квартир, в которых установлены электроплиты, газовые колонки и телефоны. Для этого в соответствующих ячейках D16, E16 и F16 введите следующие формулы (вкладка Формулы

категория Статистические
 в ячейке D16 = СЧЁТЕСЛИ(D11:D15;"+")
 в ячейке Е = СЧЁТЕСЛИ(E11:E15;"+");
 в ячейке F16 = СЧЁТЗ(F11:F15) (рассчитывается как число непустых ячеек.
7. Платежи для квартиры № 1 определяются следующим образом
 коммунальные услуги в ячейке G11 = ЕСЛИ
 оплата за газ в ячейке Н = ЕСЛИ(D11<>"+";1;0)*C11*ЕСЛИ(E11="+";$C$5;$C$4);
 оплата за телефон в ячейке I11 = ЕСЛИ(F11="о";$C$2;ЕСЛИ(F11="с";$C$3;0)).
8. Заполните остальные ячейки с помощью маркера автозаполнения.
9. Просуммируйте данные ячеек по столбцам (для этого выделите нужное количество ячеек и нажмите знак Автосуммирования

на вкладке Главная.
10. Содержимое ячеек G11:I15 представьте в денежном формате.
11. Покажите выполненное задание преподавателю.
12. На листе Лист рассчитайте распределение фонда заработной платы, если известен общий фонд заработной платы, коэффициент трудового участия сотрудника и его минимальная заработная плата (такая ситуация может произойти, если, например, увеличен фонд заработной платы, ноне должно получиться, что сотрудник будет получать ниже установленной заработной платы таким образом, между сотрудниками будет делиться в соответствии с коэффициентом трудового участия не весь фонда только та его часть, которая больше суммы всех прежних зарплат.
13. Для этого на листе Лист, который назовите Распределение фонда зарплаты, создайте таблицу по образцу, приведенному на рисунке.

16 14. Оформите таблицу.
15. Содержимое ячеек Си представьте в денежном формате.
16. Фонд зарплаты распределяется между сотрудниками в зависимости от устанавливаемого руководителем подразделения КТУ (коэффициент трудового участия – индивидуальная оценка качества труда каждого работника) ив зависимости от его минимальной прежней) заработной платы. Для определения новой зарплаты сначала нужно найти удельный вес единицы КТУ в денежном исчислении для той части фонда заработной платы, которая больше суммы всех прежних зарплата затем умножить его на индивидуальный коэффициент работника и прибавить прежнюю зарплату. Все вычисления для работника Антонова Р.И. определяются следующим образом
 Сумма всех коэффициентов в ячейке D17
= СУММ D16);
 Начислено в ячейке F7
= E7*B7/$C$3;
 Надбавка в ячейке G7
= ($C$5-$F$17)/$D$17*D7;
 Новая зарплата в ячейке Н
= F7+G7.
17. Заполните остальные ячейки с помощью маркера автозаполнения.
18. Просуммируйте данные ячеек по столбцами убедитесь, что получена сумма – фонд заработной платы.
19. Содержимое ячеек F7:H17 представьте в денежном формате.
20. Поданным последнего столбца таблицы создайте круговую диаграмму (гистограмму. Оформите ее.
21. Покажите выполненное задание преподавателю.
22. На листе Лист, который назовите Сдельная зарплата, рассчитайте размеры сдельной заработной платы, зависящей от объема выполненной работы, а также налоги сумму, полученную на руки каждым из работников. Для этого создайте таблицу по образцу, приведенному на рисунке.
23. Оформите заголовок таблицы, используя технологию объединения нескольких ячеек в одну. Оформите таблицу, используя возможности диалогового окна Формат ячеек. Содержимое ячеек С2:С3 представьте в денежном формате (Формат ячеек → вкладка
Число → Денежный формат. Содержимое ячеек Си представьте в процентном формате (Формат ячеек → вкладкаЧисло → Процентный формат
24. Сдельная зарплата зависит от количества и качества произведенной продукции. Она определяется следующим образом число обработанных деталей умножается настои- мость ее обработки. Если работник допустил браки испортил деталь, то ее стоимость вычитается из заработка. Все вычисления для работника Иванова В.А. определяются следующим образом
 Зарплата в ячейке D7:
=B7*$C$2 – C7*$C$3;
 Сумма налога в ячейке Е ЕСЛИ
 Сумма на руки задайте самостоятельно.
25. Заполните остальные ячейки с помощью маркера автозаполнения.
26. Просуммируйте данные ячеек по столбцам.
27. Содержимое ячеек D7:F12 представьте в денежном формате.
28. Поданным трех последних столбцов таблицы создайте столбчатую диаграмму (гистограмму) и оформите ее соответствующим образом.
29. Покажите выполненное задание преподавателю. ЗАДАНИЕ. ПОСТРОЕНИЕ ГРАФИКОВ
1. На диске D: в своей папке создайте папку Задание, в ней создайте файл MS Excel Графики. На листе Лист, который назовите График, создайте таблицу по образцу. Значения аргументах заполните в диапазоне от – 4 до 4 с шагом 0,1.
2. В соседнем столбце у вычислите значения функции по формуле
)
1 3
cos(
sin
5



x
x
y