Задание по Excel для студентов 2курса СПО

Задания на контрольную работу по EXCEL

преподаватель Пахомова Светлана Анатольевна

Оглавление

Порядок выбора вариантов заданий и требования к оформлению контрольной работы............................................................................................................................... 3

Задание 1. Расчеты в Excel. Работа со списками........................................... 4

Задание 2. Тема «Линия тренда».................................................................... 20

Приложение 1..................................................................................................... 22

Приложение 2..................................................................................................... 23

Порядок выбора вариантов заданий и требования к оформлению контрольной работы

Контрольная работа по данной дисциплине включает 3 задания.

Номера вариантов по каждому заданию выбираются по буквам фамилии сту- дента в соответствии с таблицей:

№ варианта	1	2	3	4	5	6	7	8	9	10
Буквы	А	Б	В	Г	Д	Е	Ё	Ж	З	И
	К	Л	М	Н	О	П	Р	С	Т	У
	Ф	Х	Ц	Ч	Ш	Щ	Ъ	Ы	Ь	Э
	Ю	Я

Например, для студента с фамилией Иванов должны быть выбраны следую- щие варианты заданий:

Задание №1 - 10 вариант (буква И) Задание №2 - 3 вариант (буква В) Задание №3 - 1 вариант (буква А)

Контрольная работа должна быть выполнена на сшитых стандартных листах формата А4 и включать: титульный лист, информацию о выполнении каждого из заданий.

На титульном листе указываются: вверху – названия: комитета, колледжа и специальности, шифр группы, в центре – Контрольная работа и название дисциплины, ниже – Студент Фамилия И.О., затем – должности и Фамилии И.О. преподавателей. Пример титульного листа приведен в приложении 1.

По каждому заданию необходимо представить:

-   № задания, тему;

-   № варианта, условие задания;

-   краткое описание выполнения задания, скриншот(ы) листов Excel с резуль- татами и формулами (описание действий для отображения формул приведено в приложении 2)

Задание 1. Расчеты в Excel. Работа со списками

Вариант № 1 Ведомость по продаже соков

·      На рабочем листе Excel внести данные из справочной таблицы, можно до- полнить таблицу своими данными (до 8 позиций в таблице)

·      Ниже создать рабочую таблицу с данными не менее 30 строк, столбцы 1-3 должны содержать повторяющиеся данные, в столбце Марка вносить только марки соков из справочной таблицы. Дата поставки – за I квартал прошлого года.

·      Выделенные курсивом поля не заполняются, а рассчитываются по форму- лам. Расчеты в задании 4-5 отделить от исходных данных хотя бы одной пустой строкой.

·      Задания 9-12 выполнять каждое на отдельном листе.

·      Листы переименовать по смыслу информации.

Справочная таблица:

Марка	Цена
Добрый	253,50
Сады придонья	246,20
Фруктовый сад	314,50

Рабочая таблица:

ФИО мене- джера	Марка	Дата по- ставки	Фактическое количе- ство	Цена	Сумма	Зарплата мене- джера
Сергеев	Добрый		100
Андреев	Сады придонья		120
Васильев	Фруктовый сад		320
Васильев	Сады придонья		360
Сергеев	Сады придонья		150
Васильев	Фруктовый сад		80
Андреев	Добрый		120
…	…		…

1.     С помощью функции Просмотр или ВПР сформировать столбец Цена в зависи- мости от марки соков (из справочной таблицы).

2.     Сформировать столбец Сумма. Найти общую сумму поставок.

3.     Используя функцию ЕСЛИ заполнить столбец Зарплата менеджера в зависи- мости суммы поставки:

-       Если сумма меньше 5000, то 3% от суммы поставки;

-       Если сумма от 5000 до 10000, то 5% от суммы поставки;

-       Если сумма больше 10000, то 7% от суммы поставки.

4.     С помощью функции СУММЕСЛИ найти зарплату менеджера Сергеева.

5.     С   помощью  функции   СЧЕТЕСЛИ  найти   количество  продаж  с суммой менее 5000.

6.     Выделить в таблице сумму поставок, используя режим Условное форматирова- ние (формула):

·                    Сумма поставки меньше 5000 – светло-зеленый цвет заливки;

·                    Сумма поставки от 5000 до 1000 – светло-желтый цвет заливки;

·                    Сумма поставки больше 10000 – светло-серый цвет заливки.

7.     Выделить красным цветом в столбце Дата поставки уникальные значения, ис- пользуя режим Условное форматирование (формула).

8.     Выделить синим цветом в столбце Фактическое количество значения, которые выше среднего значения в этом столбце, используя режим Условное формати- рование.

9.     Используя функцию Итоги… рассчитать, на какую сумму и в каком количестве было продано сигарет за каждую дату.

10. С помощью команды Автофильтр отобразить поставки сигарет марки Bond с количеством поставки более 200

11. Используя Расширенный фильтр, отфильтровать информацию о сигаретах, по- ставленных в феврале с количеством менее 150 и в марте с количеством более 300.

12. Используя сводную таблицу подвести итоги:

-       за каждый день по каждому менеджеру сумма поставки; сгруппировать дан- ные по месяцам;

-       для каждой марки сигарет количество поставок за каждый месяц.

Вариант № 2. Ведомость реализации товаров

·      На рабочем листе Excel внести данные из справочной таблицы, можно до- полнить таблицу своими данными (до 8 позиций в таблице)

·      Ниже создать рабочую таблицу с данными не менее 30 строк, столбцы 1-3 должны содержать повторяющиеся данные, в столбце Наименование вносить только товар из справочной таблицы. Дата поставки – за I квартал прошлого года.

·      Выделенные курсивом поля не заполняются, а рассчитываются по форму- лам. Расчеты в задании 5-6 отделить от исходных данных хотя бы одной пустой строкой.

·      Задания 10-13 выполнять каждое на отдельном листе.

·      Листы переименовать по смыслу информации.

Справочная таблица:

Наименование	Цена
Кошелек	1200
Портмоне	1150
Портфель	4800

Рабочая таблица:

Дата реа- лизации	Наименование товаров	Поставщики	Цена	Количе- ство	Сумма	Скидка	Сумма со скид- кой
	Кошелек	ООО «Смайлик»		300
	Портмоне	Кожторг		…
	Портфель	ООО «Смайлик»		..
	Кошелек	Кожторг
	Портмоне	Кожторг
	Портфель	Мода
	Портфель	Мода
	Кошелек	Кожторг
	Портмоне	Мода
	Портфель	ООО «Смайлик»
..	…	…		..

1.     С помощью функции Просмотр или ВПР сформировать столбец Цена в зависи- мости от наименования товара (из справочной таблицы).

2.     Сформировать поле Сумма.

3.     Используя функцию ЕСЛИ заполнить столбец Скидка следующим образом:

-       для суммы менее 8000 - 0%;

-   для суммы от 8000 до 15000 - 3%;

-       для суммы свыше 15000 – 5%.

4.     Сформировать поле Сумма со скидкой.

5.     С помощью функции СУММЕСЛИ найти общее количество проданных кошель- ков.

6.     С   помощью   функции   СЧЕТЕСЛИ   найти   количество   продаж   с сумой более 10000.

7.     Выделить в столбце Сумма поставок с учетом скидки, используя режим Услов- ное форматирование (формула):

·                    Сумма поставки меньше 1000 –синий цвет шрифта;

·                    Сумма поставки от 1000 до 5000 –красный цвет шрифта;

·                    Сумма поставки больше 5000 –зеленый цвет шрифта.

8.     Выделить красным цветом в столбце Дата поставки повторяющиеся значения, используя режим Условное форматирование (формула).

9.     Выделить синим цветом в столбце Цена значения, которые ниже среднего зна- чения в этом столбце, используя режим Условное форматирование.

10. Используя функцию Итоги… рассчитать, на какую сумму и в каком количестве было продано товаров каждым поставщиком.

11. С помощью команды Автофильтр отобразить продажи в январе и феврале со скидками более 1000 руб.

12. Используя Расширенный фильтр отфильтровать продажи товаров – кошельки в январе и портфели в январе и феврале.

13. Используя сводную таблицу подвести итоги:

-       за каждый день по каждому товару сумма поставки; сгруппировать данные по месяцам;

-       для каждого поставщика по каждому товару суммарная скидка.

Вариант № 3. Доставка товара

·      На рабочем листе Excel внести данные из справочной таблицы, можно до- полнить таблицу своими данными (до 8 позиций в таблице)

·      Ниже создать рабочую таблицу с данными не менее 30 строк, столбцы 1-3 должны содержать повторяющиеся данные, в столбце Наименование мороженого вносить только товар из справочной таблицы. Дата поставки – за I квартал про- шлого года.

·      Выделенные курсивом поля не заполняются, а рассчитываются по форму- лам. Расчеты в задании 4-5 отделить от исходных данных хотя бы одной пустой строкой.

·      Задания 9-12 выполнять каждое на отдельном листе.

·      Листы переименовать по смыслу информации.

Справочная таблица:

Наименование мороженого	Закупочная цена
Пломбир	20,25
Эскимо	25,30
Ягодка	15,65

Рабочая таблица:

Название мороженого	Дата по- ставки	Менеджер	Закупочная цена	Отпускная цена	Количество	Стои- мость до- ставки	При- быль
Пломбир		Зайцев
Пломбир		Волков
Ягодка		Синичка
Эскимо		Синичка
Ягодка		Волков
Пломбир		Синичка
Эскимо		Зайцев

1.     С помощью функции Просмотр или ВПР сформировать столбец Закупочная цена в зависимости от наименования мороженого (из справочной таблицы).

2.     Используя функцию ЕСЛИ заполнить столбец Стоимость доставки следую- щим образом:

-       В январе – 1200 руб.

-       В феврале – 1150 руб.

-       В марте – 1000 руб.

3.     Сформировать поле Прибыль.

4.     С помощью функции СУММЕСЛИ найти общее количество поставок мене- джера Зайцева.

5.     С помощью функции СЧЕТЕСЛИ найти количество поставок пломбира.

6.     Выделить в столбце Прибыль, используя режим Условное форматирование (формула):

·                    Прибыль меньше 2000 –светло-зеленый цвет заливки;

·                    Сумма поставки от 2000 до 5000 –розовый цвет заливки;

·                    Сумма поставки больше 5000 –светло-желтый цвет заливки.

7.     Выделить синим цветом в столбце Дата поставки уникальные значения, ис- пользуя режим Условное форматирование (формула).

8.     Выделить заливкой светло-серого цвета в столбце Количество значения, кото- рые выше среднего значения в этом столбце, используя режим Условное форма- тирование.

9.     Используя функцию Итоги... рассчитать прибыль, стоимость доставки и коли- чество мороженого, проданного каждым менеджером.

10. С помощью команды Автофильтр отобразить все закупки пломбира с количе- ством более 1000.

11. Используя Расширенный фильтр отобразить все закупки пломбира объемом бо- лее 4000 руб. и эскимо количеством более 3000.

12. Используя сводную таблицу подвести итоги:

-       за каждый день по каждому менеджеру сумма поставки; сгруппировать дан- ные по месяцам;

-       для каждого вида мороженого за каждый месяц сумму прибыли.

Вариант № 4. Поставка товара

·      На рабочем листе Excel внести данные из справочной таблицы, можно до- полнить таблицу своими данными (до 8 позиций в таблице)

·      Ниже создать рабочую таблицу с данными не менее 30 строк, столбцы 1-3 должны содержать повторяющиеся данные, в столбце Наименование товара вно- сить только товар из справочной таблицы. Дата поставки – за IV квартал прошлого года.

·      Выделенные курсивом поля не заполняются, а рассчитываются по форму- лам. Расчеты в задании 4-5 отделить от исходных данных хотя бы одной пустой строкой.

·      Задания 9-12 выполнять каждое на отдельном листе.

·      Листы переименовать по смыслу информации.

Справочная таблица:

Наименование товара	Цена
Календарь перекидной	55,00
Календарь настольный	36,50
Календарь настенный	95,50

Рабочая таблица:

Дата по- ставки	Наименование товара	Поставщик товара	Цена	Количе- ство	Дилерская скидка	Сумма
	Календарь перекидной	ЧП «Иванофф»		2000
	Календарь настольный	ЧП «Петрофф»
	Календарь настенный	ЧП «Иванофф»
	Календарь настольный	ЧП «Иванофф»
	Календарь перекидной	ЧП «Петрофф»
	Календарь перекидной	ЧП «Иванофф»
	Календарь настенный	ЧП «Смирнофф»
	Календарь настенный	ЧП «Иванофф»
	Календарь настольный	ЧП «Смирнофф»
…	…	…		…

1.     С помощью функции Просмотр или ВПР сформировать столбец Цена в зависи- мости от наименования товара (из справочной таблицы).

2.     Используя функцию ЕСЛИ заполнить столбец "Дилерская скидка" следующим образом:

-       ЧП «Иванофф»- 5%.

-       ЧП «Петрофф»- 10%.

-       остальным - 0%.’

3.     Сформировать поле Сумма.

4.     С помощью функции СУММЕСЛИ найти общую сумму поставок перекидных календарей.

5.     С помощью функции СЧЕТЕСЛИ найти количество поставок ЧП «Иванофф».

6.     Выделить в столбце Сумма, используя режим Условное форматирование (фор- мула):

·                    Сумма меньше 4000 –светло-синий цвет заливки;

·                    Сумма поставки от 4000 до 8000 –светло-серый цвет заливки;

·                    Сумма поставки больше 8000 –светло-зеленый цвет заливки.

7.     Выделить красным цветом в столбце Дата поставки повторяющиеся значения, используя режим Условное форматирование (формула).

8.     Выделить синим цветом в столбце Количество значения, которые ниже сред- него значения в этом столбце, используя режим Условное форматирование.

9.     Используя функцию Итоги… рассчитать, на какую сумму и в каком количестве было продано товаров каждого вида.

10. С помощью команды Автофильтр отобразить все поставки настенных и пере- кидных календарей количеством более 1500 единиц в декабре.

11. Используя Расширенный фильтр отобразить данные для отображения всех по- ставок от ЧП «Иванофф» с количеством более 1000 и ЧП «Петрофф» с количе- ством менее 2000.

12. Используя сводную таблицу подвести итоги:

-       за каждый день по каждому поставщику сумма поставки; сгруппировать дан- ные по месяцам;

-       для каждого вида товара количество поставок за каждый месяц.

Вариант № 5. Ведомость закупки чая

·      На рабочем листе Excel внести данные из справочной таблицы, можно до- полнить таблицу своими данными (до 8 позиций в таблице)

·      Ниже создать рабочую таблицу с данными не менее 30 строк, столбцы 1-3 должны содержать повторяющиеся данные, в столбце Наименование чая вносить только товар из справочной таблицы. Дата поставки – за I квартал прошлого года.

·      Выделенные курсивом поля не заполняются, а рассчитываются по форму- лам. Расчеты в задании 4-5 отделить от исходных данных хотя бы одной пустой строкой.

·      Задания 9-12 выполнять каждое на отдельном листе.

·      Листы переименовать по смыслу информации.

Справочная таблица:

Наименование чая	Цена
Чай Зеленый DeMarco	192,00
Чай Пуэр	260,50
Курильский чай с чабрецом	141,00

Рабочая таблица:

Производитель	Дата поставки	Магазин	Цена	Количество	Сумма
Чай Зеленый DeMarco		Чайхана
Курильский чай с чабрецом		Чай-кубы
Чай Зеленый DeMarco		Чай-Ок
Чай Пуэр		Чай-кубы
…

1.     С помощью функции Просмотр или ВПР сформировать столбец Цена в зависи- мости от наименования чая (из справочной таблицы).

2.     Используя функцию ЕСЛИ заполнить столбец Количество следующим обра- зом:

-          Для магазина Чайхана – 200 единиц,

-       Для магазина Чай-Ок – 100 единиц,

-       Для других магазинов – 50 единиц.

3.     Сформировать поле Сумма.

4.     С помощью функции СУММЕСЛИ найти общую сумму закупки по магазину Чайхана.

5.     С  помощью  функции  СЧЕТЕСЛИ найти количество поставок на сумму более 3000.

6.     Выделить в столбце Сумма, используя режим Условное форматирование (фор- мула):

·                    Сумма меньше 2000 –синий цвет шрифта;

·                    Сумма поставки от 2000 до 3000 – оранжевый цвет шрифта;

·                    Сумма поставки больше 3000 –зеленый цвет шрифта.

7.     Выделить светло-серым цветом заливки в столбце Дата поставки уникальные

значения, используя режим Условное форматирование (формула).

8.     Выделить красным цветом в столбце Цена значения, которые выше среднего значения в этом столбце, используя режим Условное форматирование.

9.     Используя функцию Итоги... подсчитать сумму закупки каждого чая; общее ко- личество единиц и сумму закупки для каждого магазина.

10. Используя функцию Автофильтр отобразить данные о продаже чая в марте и феврале.

11. Используя Расширенный фильтр отобразить информацию о поставках чая в ма- газин Чайхана в январе и феврале.

12. Используя сводную таблицу подвести итоги:

-       за каждый день по каждому магазину сумма поставки; сгруппировать данные по месяцам;

-       для каждого типа чая по каждому магазину количество поставленного чая.

Вариант № 6. Ведомость поставки товаров

·      На рабочем листе Excel внести данные из справочной таблицы, можно до- полнить таблицу своими данными (до 8 позиций в таблице)

·      Ниже создать рабочую таблицу с данными не менее 30 строк, столбцы 1-3 должны содержать повторяющиеся данные, в столбце Наименование товара вно- сить только товар из справочной таблицы. Дата поставки – за I квартал прошлого года.

·      Выделенные курсивом поля не заполняются, а рассчитываются по форму- лам. Расчеты в задании 4-5 отделить от исходных данных хотя бы одной пустой строкой.

·      Задания 9-12 выполнять каждое на отдельном листе.

·      Листы переименовать по смыслу информации.

Справочная таблица:

Наименование товара	Цена
Плавленый сыр «Коровка»	23,50
Маргарин «Солнечный»	28,35
Масло «Ферма»	48,55

Рабочая таблица:

Дата реа- лизации	Наименование товара	Постав- щик	Цена	Количество	Стоимость доставки	Сумма
	Маргарин «Солнечный»	База 1		200
	Плавленый сыр «Коровка»	База 1		…
	Плавленый сыр «Коровка»	База 2
	Масло «Ферма»	База 3
	Плавленый сыр «Коровка»	База 2
	Маргарин «Солнечный»	База 1
	Масло «Ферма»	База 3
	Маргарин «Солнечный»	База 1
	…	…

1.     С помощью функции Просмотр или ВПР сформировать столбец Цена в зависи- мости от наименования товара (из справочной таблицы).

2.     Используя функцию ЕСЛИ заполнить столбец Стоимость доставки в зависи- мости от поставщика следующим образом:

-   База 1 – 1500;

-   База 3 – 2500;

-          Остальные поставщики – 1000 .

3.       Сформировать поле Сумма с учетом доставки.

4.     С помощью функции СУММЕСЛИ найти общую сумму поставок с базы 1.

5.     С  помощью  функции  СЧЕТЕСЛИ найти количество поставок с суммой более 5000.

6.     Выделить в столбце Сумма, используя режим Условное форматирование (фор- мула):

·                    Сумма меньше 4500 –синий цвет заливки;

·                    Сумма поставки от 4500 до 5500 – оранжевый цвет заливки;

·                    Сумма поставки больше 5500 –зеленый цвет заливки.

7.     Выделить желтым цветом заливки в столбце Дата поставки повторяющиеся значения, используя режим Условное форматирование (формула).

8.     Выделить красным цветом в столбце Цена значения, которые ниже среднего значения в этом столбце, используя режим Условное форматирование.

9.     Используя функцию Итоги... рассчитать, на какую сумму и в каком количестве было поставлено товаров каждого поставщика.

10. Используя функцию Автофильтр отобразить информацию – все поставки в ян- варе на сумму более 5000.

11. Используя Расширенный фильтр отобразить информацию о всех поставках масла «Ферма» в феврале и маргарина «Солнечный» в марте.

12. Используя сводную таблицу подвести итоги:

-       за каждый день по каждому товару сумма поставки; сгруппировать данные по месяцам;

-       для каждого товара за каждую дату количество поставок.

Вариант № 7. Ведомость реализации мониторов

·      На рабочем листе Excel внести данные из справочной таблицы, можно до- полнить таблицу своими данными (до 8 позиций в таблице)

·      Ниже создать рабочую таблицу с данными не менее 30 строк, столбцы 1-3 должны содержать повторяющиеся данные, в столбце Модель вносить только товар из справочной таблицы. Дата поставки – за I квартал прошлого года.

·      Выделенные курсивом поля не заполняются, а рассчитываются по форму- лам. Расчеты в задании 4-5 отделить от исходных данных хотя бы одной пустой строкой.

·      Задания 9-12 выполнять каждое на отдельном листе.

·      Листы переименовать по смыслу информации.

Справочная таблица:

Модель	Производитель	Цена
Asus VX248H	Asus	25000
Asus MX259H	Asus	28000
Samsung 18.5" S19D300NY	Samsung	8700

Рабочая таблица:

Модель	Производитель	Дата поставки	Цена	Количество	Скидка	Стоимость
Asus VX248H				20
Asus VX248H
Asus MX259H
Samsung 18.5" S19D300NY
Asus MX259H
Asus MX259H
…		…		…

1.     С помощью функции Просмотр или ВПР сформировать столбцы Производи- тель и Цена в зависимости от модели (из справочной таблицы).

2.     Используя функцию ЕСЛИ заполнить столбец Скидка следующим образом:

-       Количество менее 5 единиц – 0%

-       Количество от 5 до 15 единиц – 3%

-       Количество больше 5 – 5%

3.     Сформировать поле Стоимость с учетом скидки.

4.     С помощью функции СУММЕСЛИ найти общее количество поставленных мо- ниторов фирмы Asus.

5.     С помощью функции СЧЕТЕСЛИ найти количество поставок с объемом по- ставки менее 10 единиц.

6.     Выделить в столбце Скидка, используя режим Условное форматирование (фор- мула):

·                    Скидка меньше 2000 – желтый цвет заливки;

·                    Скидка от 2000 до 4000 – оранжевый цвет заливки;

·                    Скидка больше 4000 –розовый цвет заливки.

7.     Выделить зеленым цветом в столбце Дата поставки уникальные значения, ис- пользуя режим Условное форматирование (формула).

8.     Выделить синим цветом в столбце Количество значения, которые выше сред- него значения в этом столбце, используя режим Условное форматирование.

9.     Используя функцию Итоги... вычислить средние цены мониторов каждого про- изводителя, и количество мониторов каждого типа.

10. Используя функцию Автофильтр отобразить мониторы, произведенных фир- мой Samsung стоимостью выше 15000.

11. Используя Расширенный фильтр отобразить информацию о поставках монито- ров фирмы Samsung в феврале и Asus в марте.

12. Используя сводную таблицу подвести итоги:

-       для каждого производителя ежедневная сумма продажи; сгруппировать дан- ные по месяцам;

-       для каждой модели за каждый месяц количество поставок.

Вариант № 8. Ведомость закупки телефонов

·      На рабочем листе Excel внести данные из справочной таблицы, можно до- полнить таблицу своими данными (до 8 позиций в таблице)

·      Ниже создать рабочую таблицу с данными не менее 30 строк, столбцы 1-3 должны содержать повторяющиеся данные, в столбце Модель вносить только товар из справочной таблицы. Дата поставки – за I квартал прошлого года.

·      Выделенные курсивом поля не заполняются, а рассчитываются по форму- лам. Расчеты в задании 4-5 отделить от исходных данных хотя бы одной пустой строкой.

·      Задания 9-12 выполнять каждое на отдельном листе.

·      Листы переименовать по смыслу информации. Справочная таблица:

Модель	Производитель	Цена, руб
Samsung SM-B350E	Samsung	3100
Lenovo A319	Lenovo	6400
Xiaomi Redmi Note 3	Xiaomi	11500

Рабочая таблица:

Модель	Дата закупки	Производитель	Цена	Количество	Скидка	Сумма
Xiaomi Redmi Note 3				30
Lenovo A319				5
Xiaomi Redmi Note 3
Samsung SM-B350E
Lenovo A319
Xiaomi Redmi Note 3
…	…			…

1.     С помощью функции Просмотр или ВПР сформировать столбцы Производи- тель и Цена в зависимости от модели (из справочной таблицы).

2.     Используя функцию ЕСЛИ заполнить столбец Скидка следующим образом:

-       Lenovo – 2%.

-       Samsung – 2,5%.

-       Остальные производители – нет скидки.

3.     Сформировать поле Сумма с учетом скидки.

4.     С помощью функции СУММЕСЛИ найти общую сумму поставок телефонов Lenovo.

5.     С  помощью  функции  СЧЕТЕСЛИ найти количество поставок на сумму более 20000.

6.     Выделить в столбце Количество, используя режим Условное форматирование (формула):

·                    Количество меньше 20 – синий цвет шрифта;

·                    Количество от 20 до 50 – зеленый цвет шрифта;

·                    Количество больше 50 –красный цвет шрифта.

7.     Выделить зеленым цветом в столбце Дата закупки повторяющиеся значения, используя режим Условное форматирование (формула).

8.     Выделить светло-серым цветом заливки в столбце Цена значения, которые ниже среднего значения в этом столбце, используя режим Условное форматирование.

9.     Используя функцию Итоги... рассчитать сумму закупки и общее количество для каждого производителя.

10. Используя Автофильтр отобразить все закупки в феврале и марте. 11.Используя Расширенный фильтр отобразить все закупки Lenovo с количеством

более 50 единиц и Samsung с количеством более 70 единиц.

12.  Используя сводную таблицу подвести итоги:

-       за каждый день по каждому производителю сумма закупки; сгруппировать данные по месяцам;

-       для каждого производителя за каждый месяц сумму закупки.

Вариант №9. Сотрудники

·      На рабочем листе Excel внести данные из справочной таблицы, можно до- полнить таблицу своими данными (до 8 позиций в таблице)

·      Ниже создать рабочую таблицу с данными не менее 30 строк, столбцы 1-3 должны содержать повторяющиеся данные, в столбце Должность вносить только должности из справочной таблицы.

·      Выделенные курсивом поля не заполняются, а рассчитываются по форму- лам. Расчеты в задании 5-6 отделить от исходных данных хотя бы одной пустой строкой.

·      Задания 10-13 выполнять каждое на отдельном листе.

·      Листы переименовать по смыслу информации. Справочная таблица:

Должность	Оклад
Специалист	6500
Инженер	6300
Лаборант	3450

Рабочая таблица:

Ф.И.О.	Подразде- ление	Должность	Оклад	Дата поступле- ния на работу	Стаж работы	Надбавка	Начис- лено
Иванов П.Ф.	Отдел 1	Лаборант		13.08.2000
Петров А.Р.	Отдел 2	Специалист		30.08.2005
Сидоров Р.Л.	Отдел 2	Специалист		…
Козлов А.Д.	Отдел 1	Инженер		…
Степанов И.О.	Отдел 3	Лаборант		…
…	…	…		…

1.     С помощью функции Просмотр или ВПР сформировать столбец Оклад в зави- симости от должности (из справочной таблицы).

2.     Определить стаж работы каждого сотрудника.

3.     Используя функцию ЕСЛИ заполнить столбец Надбавка в зависимости от стажа работы:

-       при стаже менее 3 лет – нет надбавки;

-       при стаже от 3 до 10 лет - 10% от оклада;

-       при стаже более 10 лет – 20% от оклада.

4.     Сформировать поле Начислено.

5.     С   помощью   функции   СУММЕСЛИ   найти   общую   начисленную сумму по отделу 1.

6.     С помощью функции СЧЕТЕСЛИ найти количество лаборантов.

7.     Выделить в столбце Стаж, используя режим Условное форматирование (фор- мула):

·                    Стаж меньше 5 лет – голубой цвет заливки;

·                    Стаж от 5 до 15 – розовый цвет заливки;

·                    Стаж больше 15 –светло-зеленый цвет заливки.

8.     Выделить синим цветом в столбце Дата поступления на работу повторяющи- еся значения, используя режим Условное форматирование (формула).

9.     Выделить красным в столбце Оклад значения, которые выше среднего значения в этом столбце, используя режим Условное форматирование.

10. Используя функцию Итоги определить средний оклад по каждому подразделе- нию и сумму начисленной зарплаты.

11. Используя функцию Автофильтр вывести список лаборантов, поступивших на работу в 2000 году.

12. Используя Расширенный фильтр отобразить информацию о специалистах со стажем более 10 лет и инженеров со стажем более 5 лет.

13. Используя сводную таблицу подвести итоги:

-       для каждого подразделения количество сотрудников каждой должности; сгруппировать данные по годам поступления на работу;

-       для каждого подразделения для каждой должности сумму начисленной надбавки.

Вариант № 10. Ведомость реализации принтеров

·      На рабочем листе Excel внести данные из справочной таблицы, можно до- полнить таблицу своими данными (до 8 позиций в таблице)

·      Ниже создать рабочую таблицу с данными не менее 30 строк, столбцы 1-3 должны содержать повторяющиеся данные, в столбце Модель вносить только мо- дели принтеров из справочной таблицы.

·      Выделенные курсивом поля не заполняются, а рассчитываются по форму- лам. Расчеты в задании 5-6 отделить от исходных данных хотя бы одной пустой строкой.

·      Задания 10-13 выполнять каждое на отдельном листе.

·      Листы переименовать по смыслу информации.

Справочная таблица:

Модель	Цена	Тип
SAMSUNG SL-M2020	6100	лазерный
EPSON L120	9400	струйный
EPSON L805	25600	струйный

Рабочая таблица:

Модель	Тип	Дата продажи	Цена руб	Цена (у.е.)	Количе- ство	Скидка	Сумма (руб.)
EPSON L805					20
EPSON L120
SAMSUNG SL-M2020
EPSON L120
EPSON L805
EPSON L120
…

1.     С помощью функции Просмотр или ВПР сформировать столбцы Тип и Цена в зависимости от модели (из справочной таблицы).

2.     Сформировать поле Цена (у.е.).

3.     Используя функцию ЕСЛИ заполнить столбец Скидка следующим образом:

-       если количество более 10 – 2%;

-       если количество от 10 до 20 – 4%;

-       количество более 20 – 1%.

4.     Сформировать поле Сумма с учетом скидки.

5.     С помощью функции СУММЕСЛИ найти общую сумму реализации струйных принтеров.

6.     С помощью функции СЧЕТЕСЛИ найти количество поставок принтеров с ценой более 15000.

7.     Выделить в столбце Количество, используя режим Условное форматирование (формула):

·                    Количество меньше 5 единиц – светло-зеленый цвет заливки;

·                    Количество от 5 до 15 – светло-синий цвет заливки;

·                    Количество больше 15 –светло-серый цвет заливки.

8.     Выделить красным цветом в столбце Дата продажи на работу повторяющиеся значения, используя режим Условное форматирование (формула).

9.     Выделить оранжевым в столбце Цена значения, которые ниже среднего значе- ния в этом столбце, используя режим Условное форматирование.

10. Используя функцию Итоги... вычислить средние цены каждого типа принтера в руб. и количество принтеров каждого типа.

11. Используя функцию Автофильтр вывести список всех принтеров фирмы EPSON, проданных в январе.

12. Используя Расширенный фильтр отобразить информацию о всех лазерных принтерах дешевле 1500 руб. и струйных дороже 10000 руб.

13. Используя сводную таблицу подвести итоги:

-       для каждой модели ежедневная сумма продажи; сгруппировать данные по месяцам;

-       для каждого типа принтеров количество проданных принтеров, минималь- ную и максимальную цены.

Задание 2. Тема «Линия тренда»

1.           По данным таблицы построить точечную диаграмму.

2.           Добавить несколько линий тренда разного типа (рекомендуется построить несколько диаграмм и добавлять на каждой по разной линии тренда), вывести для каждого типа уравнение зависимости, величину достоверности аппроксимации R².

3.           Сделать анализ, какая из построенных линий тренда подходит лучше к экс- периментальным данным.

4.           На лучшей линии тренда сделать прогноз на два периода вперед.

Вариант 1

Год	1995	1996	1997	1998	1999	2000	2001	2002	2003
Продажи мяса, млн.т.	10,3	12,5	16,7	16,8	16,7	16,9	20,01	20,1	20,3

Вариант 2

Год	1995	1996	1997	1998	1999	2000	2001	2002	2003
Продажи молока, млн.т.	12,3	12,5	13,5	13,2	13,2	13,25	14,2	14,1	14,3

Вариант 3

Год	1995	1996	1997	1998	1999	2000	2001	2002	2003
Продажи кофе, млн.т.	19,3	21,3	21	20,9	22,6	22	23	23,5	23

Вариант 4

Год	1995	1996	1997	1998	1999	2000	2001	2002	2003
Продажи сои, тыс. руб	2,1	5,6	6,8	8,1	11,3	14,3	14,5	15,1	15,2

Вариант 5

Год	1995	1996	1997	1998	1999	2000	2001	2002	2003
Производство мебели, млн. руб	25,3	26,1	24	24,8	24,7	22,3	20,3	15,1	10,3

Вариант 6

Год	1995	1996	1997	1998	1999	2000	2001	2002	2003
Продажи сахара, млн. руб	123,6	110,6	136,5	147,7	133,2	154,6	154	166,7	154,6

Вариант 7

Год	1997	1998	1999	2000	2001	2002	2003	2004	2005
Продажи хлеба, млн. руб	14,19	14,42	14,52	14,53	14,5	14,58	14,49	14,57	14,57

Вариант 8

Год	1997	1998	1999	2000	2001	2002	2003	2004	2005
Продажи зерна, млн. руб	32,6	30,6	31,2	29,6	31,6	31,1	28,4	29,4	28,7

Вариант 9

Год	1997	1998	1999	2000	2001	2002	2003	2004	2005
Продажи компьютеров, млн. руб	45,6	45,3	45,1	46,5	45,7	47,1	46,1	47,1	48,1

Вариант 10

Год	1997	1998	1999	2000	2001	2002	2003	2004	2005
Продажи автомо- билей, млн. руб	145,3	146,7	157,3	158,2	159,3	165	166,6	170,3	170,9

Приложение 1

Комитет образования и науки Курской области

Областное бюджетное профессиональное образовательное учреждение 

«Железногорский горно-металлургический колледж»

Шифр специальности

Контрольная работа

по дисциплине «Информатика»

Выполнил

студент группы                           

(группы)

(Фамилия, инициалы)

Проверил преподаватель Пахомова С.А.

Железногорск 2020

Приложение 2

Для того, что увидеть формулы на листе Excel, откройте диалоговое окно Файл – Параметры Excel. Затем откройте вкладку Дополнительно и в группе Па- раметры отображения листа установите флажок Показывать формулы, а не их значения.