Работа с несколькими рабочими листами

Практическая работа 1

Цель работы: Научиться работать с несколькими рабочими листами. Самостоятельно выполнить задание аналогично Практической работе 3 раздела 2.9.

Таблица 2.20

Товар
Наименование	Цена	Количество	Сумма
Блокнот	8, 50
Блокнот мал.	3, 40
Ежедневник	135, 00
Карандаши	12, 30
Пластилин	35, 70
Ручка шариковая	8, 60
Тетрадь 48 л.	12, 80
Тетрадь в клетку	6, 50
Тетрадь в линейку	4, 20
Всего

 

Таблица 2.20. Итоговая

Товар
Наименование	Цена	Количество	Сумма
Блокнот	8, 50
Блокнот мал.	3, 40
Ежедневник	135, 00
Карандаши	12, 30
Пластилин	35, 70
Ручка шариковая	8, 60
Тетрадь 48 л.	12, 80
Тетрадь в клетку	6, 50
Тетрадь в линейку	4, 20
Всего

Таблица 2.21

Продано
Товар	Количество	Сумма
Блокнот
Блокнот мал.
Ежедневник
Карандаши
Пластилин
Ручка шариковая
Тетрадь 48 л.
Тетрадь в клетку
Тетрадь в линейку
Всего

Таблица 2.21. Итоговая

Продано
Товар	Количество	Сумма
Блокнот
Блокнот мал.		1247, 8
Ежедневник
Карандаши		1709, 7
Пластилин		1213, 8
Ручка шариковая		412, 8
Тетрадь 48 л.		2278, 4
Тетрадь в клетку
Тетрадь в линейку		655, 2
Всего		10593, 7

Таблица 2.22

Остаток
Товар	Количество	Сумма
Блокнот
Блокнот мал.
Ежедневник
Карандаши
Пластилин
Ручка шариковая
Тетрадь 48 л.
Тетрадь в клетку
Тетрадь в линейку
Всего

Таблица 2.22. Итоговая

Остаток
Товар	Количество	Сумма
Блокнот		612, 0
Блокнот мал.		452, 2
Ежедневник		810, 0
Карандаши		1365, 3
Пластилин		571, 2
Ручка шариковая		17, 2
Тетрадь 48 л.		281, 6
Тетрадь в клетку		52, 0
Тетрадь в линейку		184, 8
Всего		4346, 3

Порядок выполнения работы

1. Создайте новый файл под именем «Книга 3».

2. Назовите Лист 1 «Товар», Лист 2 «Продано», Лист 3 «Остаток».

3. На Листе «Товар» введите таблицу по образцу (табл. 2.20). Выберите границу и заливку произвольно.

4. Формула для столбца «Сумма» = Цена × Количество.

5. На Листе «Продано» введите таблицу по образцу (табл. 2.21), используя ссылки на соответствующий лист.

6. Формула для столбца «Сумма» = Товар! В2*В2

7. На Листе «Остаток» введите таблицу по образцу (табл. 2.22), используя ссылки на соответствующий лист.

8. Формулы для столбцов «Количество» и «Сумма»:

- Количество =Товар! С2-Продано! В2;

- Сумма = В2*Товар! В2.

9. Сохраните файл.

 

Пактическая работа 2

Цель работы: Ввести данные и произвести вычисления в таблицах 2.23-2.26, используя ссылки на соответствующие ячейки и таблицы и работая одновременно с несколькими рабочими листами. Построить график по итогам (рис. 2.23).

Порядок выполнения работы

1. Откройте файл «Книга 3» и создайте рабочие области со следующими названиями: Лист 4 - «Минимаркет 1», Лист 5 - «Минимаркет 2», Лист 6 - «Минимаркет 3», Лист 7 - «Итоги».

2. На Листы 4-7 введите таблицыпо образцу табл. 2.23-2.26.Выберите границу и заливку произвольно.

3. Произведите расчет в таблицах при помощи формул.

4. Для листов «Минимаркет 1», «Минимаркет 2» и «Минимаркет 3» формула для строки Прибыль следующая = Выручка – Расход.

5. На Листе «Итоги» создайте табл. 2.26. Заполните ее полученными в предыдущих таблицах данными в строке Прибыль.

6. Выделите каждый столбец табл. 2.26 и найдите сумму поквартально в строке «Итого» при помощи автосуммы «Σ ».

7. Для подсчета в с троке «Среднее значение» выделите ячейку для получения результата, выберите вкладку Главная • Редактирование • Сумма «Σ » • Среднее значение и задайте данные квартала.

8. Выделите столбцы A, B, C, D, E и строчки «Минимаркет 1», «Минимаркет 2», «Минимаркет 3» и постройте диаграмму (рис. 2.23.).

 

Таблица 2.23

Минимаркет 1
Квартал	I	II	III	IV
Выручка
Расход
Прибыль

Таблица 2.23. Итоговая

Минимаркет 1
Квартал	I	II	III	IV
Выручка
Расход
Прибыль

Таблица 2.24

Минимаркет 2
Квартал	I	II	III	IV
Выручка
Расход
Прибыль

Таблица 2.24. Итоговая

Минимаркет 2
Квартал	I	II	III	IV
Выручка
Расход
Прибыль

Таблица 2.25

Минимаркет 3
Квартал	I	II	III	IV
Выручка
Расход
Прибыль

Таблица 2.25. Итоговая

Минимаркет 3
Квартал	I	II	III	IV
Выручка
Расход
Прибыль

Таблица 2.26

Прибыль за 2011 год
Квартал	I	II	III	IV	Итого за год
Минимаркет 1
Минимаркет 2
Минимаркет 3
Итого:
Среднее значение

Таблица 2.26. Итоговая

Прибыль за 2011 год
Квартал	I	II	III	IV	Итого за год
Минимаркет 1
Минимаркет 2
Минимаркет 3
Итого:
Среднее значение

Рис. 2.23.

9. Сохраните файл.

 

Штатное расписание предприятия

Практическая работа

Цель работы: Решить задачу «Создание штатного расписания предприятия». Построить круговые диаграммы.

Порядок выполнения работы

1. Создайте новый файл под именем «Книга 4».

2. На Листе 1 решите задачу «Создание штатного расписания предприятия». Условия задачи :

-минимальная зарплата В = 1200 р.;

- минимальная ежемесячная надбавка С = 800 р.;

- коэффициент А задан для каждой должности в табл. 2.27;

- число сотрудников каждой должности указано в табл. 2.27;

- формула для вычисления окладов: Оклад = А× В+С (В и С - следует принять за абсолютные значения);

- формула для вычисления суммарного оклада: Суммарная выплата на должности = Количество сотрудников × Оклад.

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

3. На Лист 1 введите таблицу по образцу (таб. 2.27). Выберите границу и заливку произвольно.

 

Таблица 2.27

Таблица 2.27. Итоговая

4. Постройте объемную разрезанную круговую диаграмму по табл. 2.27 (

5. Самостоятельно пересчитайте таблицу с новыми значениями В = 800. Результат в табл. 2.28.

Таблица 2.28

6. Постройте объемную разрезанную круговую диаграмму по табл. 2.28.

 

7. Сохраните файл.

 

Логические функции

Функции И, ИЛИ, СЧЕТ ЕСЛИ, СУММ(А4: А24), Максимум, Минимум, Среднее, СРЗНАЧ используются при решении логических операций.

 

Практическая работа 1

Цель работы: Применить логическую функцию «ЕСЛИ» при вычислении оплаты труда сотрудникам.

· Задание 1. Вычислите трудовой стаж и в зависимости от него рассчитайте надбавки и итоговые выплаты по каждому сотруднику, постройте диаграмму.

Порядок выполнения задания

1. Откройте файл под именем «Книга 4». На Лист 2 введите данные (табл. 2.29).

2. Вычислите:

- стажкаждого сотрудника на текущий год по формуле: Стаж на текущий год = $C$1-СЗ;

- надбавку по следующему алгоритму: сотруднику дается надбавка в размере 1500 р., если его стаж не менее 20 лет. Формула: Надбавка= ЕСЛ И(ЕЗ> =20; 1500; 0);

- итого =3арплата+Надбавка;

- общий итог при помощи суммирования.

 

Таблица 2.29

Текущий год
Табельный номер	Ф.И.О.	Год приема	Зарплата	Стаж на текущий год	Надбавка	Итого
	Андреев А.Б.
	Рудников С.А.
	Савельев М.А.
	Соколов Л.В.
	Томарев Л.В.
	Токарев А.А.
	Тимофеев Б.Р.
	Ульянов Д.Г.
	Устинов Д.Г.
	Усиков В.В.
	Филатов Л.А.
	Федосеев Б.В.
	Филонов К.Е.
	Яковлев И.И.
				Итого

Таблица 2.29. Итоговая

Текущий год
Табельный номер	Ф.И.О.	Год приема	Зарплата	Стаж на текущий год	Надбавка	Итого
	Андреев А.Б.
	Рудников С.А.
	Савельев М.А.
	Соколов Л.В.
	Томарев Л.В.
	Токарев А.А.
	Тимофеев Б.Р.
	Ульянов Д.Г.
	Устинов Д.Г.
	Усиков В.В.
	Филатов Л.А.
	Федосеев Б.В.
	Филонов К.Е.
	Яковлев И.И.
				Итого

3. Постройте диаграмму, отражающую изменение оклада сотрудников в зависимости от стажа (рис. 2.26).

Рис. 2.26

· Задание 2. Вычислите трудовой стаж и в зависимости от его значения рассчитайте надбавки и итоговые выплаты по каждому сотруднику.

1. Скопируйте табл. 2.29 на Лист 2, измените заголовок на «Вычисление надбавок в зависимости от стажа работы и оклада сотрудников»

2. Удалите итоговые результаты столбцов «Надбавка» и «Итого».

3. Вычислите надбавки по алгоритму:

- надбавка - 0, если стаж меньше 10 лет;

- надбавка - 10% оклада, если стаж больше или равен 10 годам и меньше 15 лет;

- надбавка - 20% оклада, если стаж больше или равен 15 годам.

Формула: Надбавка=ЕСЛИ(ЕЗ< 10; 0; ЕСЛИ(ЕЗ> =15; DЗ*20%; DЗ*10%)).

Формула: Итого=Зарплата+Надбавка.

 

Таблица 2.30

Текущий год
Табельный номер	Ф.И.О.	Год приема	Зарплата	Стаж на текущий год	Надбавка	Итого
	Андреев А.Б.
	Рудников С.А.
	Савельев М.А.
	Соколов Л.В.
	Томарев Л.В.
	Токарев А.А.
	Тимофеев Б.Р.
	Ульянов Д.Г.
	Устинов Д.Г.
	Усиков В.В.
	Филатов Л.А.
	Федосеев Б.В.
	Филонов К.Е.
	Яковлев И.И.
				Итого

4. Постройте диаграмму, отражающую изменения окладов сотрудников (рис. 2.27). Удерживая нажатой клавишу Ctrl (Shift), выделите столбцы В («Ф.И.О.») и G («Итого») с данными расчета результатов и выберите вкладку Вставка • Диаграммы • График.

Рис. 2.27

 

5. Сохраните файл.

 

Практическая работа 2

Цель работы: Применить логическую функцию «ЕСЛИ» при решении вопроса закупки и продажи овощей.

Порядок выполнения работы

1. Откройте файл «Книга 4».

2. На Лист 3 введите следующие данные (табл. 2.31).

 

Таблица 2.31

Товар	Область	Количество товара	Купить	Продать
Курага	Краснодар
Инжир	Краснодар
Чернослив	Краснодар
Изюм	Краснодар
Курага	Дагестан
Инжир	Дагестан
Чернослив	Дагестан
Изюм	Дагестан
Курага	Осетия
Инжир	Осетия
Чернослив	Осетия
Изюм	Осетия
Курага	Ингушетия
Инжир	Ингушетия
Чернослив	Ингушетия
Изюм	Ингушетия
Итого

3. Используя логические функции, встолбец «Купить» введитеформулы, которыепозволят получить в ячейках столбца знак «+», если продукт поставлен из Краснодара и при этом его наличие на базе меньше 5000 кг, в противном случае должен быть полученпробел. Формула: Купить=ЕСЛИ(И(В2=»Краснодар»; С2< 5000); »+»; » «).

4. Используя логические функции, в столбец «Продать» введите формулы, которыепозволят получить в ячейках столбца знак «+», если продукт поставлен из Дагестанаи при этом его наличие на базе больше 3000 кг, в противном случае должен быть получен пробел. Формула: Продать=ЕСЛИ(И(В2=»Дагестан»; С2> 3000); »+»; » «).

Таблица 2.31. Итоговая

Товар	Область	Количество товара	Купить	Продать
Курага	Краснодар		+
Инжир	Краснодар		+
Чернослив	Краснодар
Изюм	Краснодар		+
Курага	Дагестан			+
Инжир	Дагестан
Чернослив	Дагестан			+
Изюм	Дагестан
Курага	Осетия
Инжир	Осетия
Чернослив	Осетия
Изюм	Осетия
Курага	Ингушетия
Инжир	Ингушетия
Чернослив	Ингушетия
Изюм	Ингушетия
Итого

5.Постройте диаграмму наличия на базе Кураги (рис. 2.28). Отфильтруйте таблицу по столбцу «Количество товара», для этоговыделите таблицу и выберите вкладку Главная • Редактирование • Сортировка и фильтр • Сортировать от А до Я Выделите строки с закупкой Кураги (табл. 2.32.) и постройтеразрезанную круговую диаграмму.

Таблица 2.32

Товар	Область	Количество товара	Купить	Продать
Курага	Краснодар		+
Курага	Дагестан			+
Курага	Осетия
Курага	Ингушетия

Рис. 2.28

Рис. 2.29

6. Выделите строки с закупкой Изюма (табл. 2.33) и постройте разрезанную круговую диаграмму наличия на базе Изюма (рис. 2.29).

Таблица 2.33

Товар	Область	Количество товара	Купить	Продать
Изюм	Краснодар
Изюм	Дагестан
Изюм	Осетия
Изюм	Ингушетия

7. Сохраните файл.

Обработка массивов данных

Практическая работа 1

Цель работы: По данным метеорологических наблюдений вычислить максимальную и минимальную температуру за год, среднемесячную температуру за год, число морозных месяцев (< -20 °С), число жарких месяцев (> 25 °С), используя логические функции.

Порядок выполнения работы

1. Откройте файл «Книга 4».

2. На Лист 4 введите следующие данные по образцу (табл. 2.34).

 

Таблица 2.34

Среднемесячная температура воздуха в регионе за год, С°
Месяц
Январь	-21, 0	-19, 0	-20, 5	-20, 5	-21, 0	-17, 5
Февраль	-15, 0	-16, 5	-14, 0	-12, 5	-14, 5	-12, 5
Март	-6, 0	-7, 0	-6, 5	-8, 0	-7, 5	-6, 5
Апрель	-2, 0	-1, 0	-1, 5	-0, 5	-1, 0	-0, 5
Май	4, 0	4, 5	3, 5	4, 0	4, 5	3, 5
Июнь	15, 0	14, 5	16, 0	15, 5	14, 5	15, 0
Июль	21, 0		24, 0	24, 5	23, 5	23, 0
Август	16, 5	15, 5	16, 0	14, 5	14, 0	13, 5
Сентябрь	12, 4	14, 3	12, 0	14, 5	12, 8	14, 0
Октябрь	7, 6	6, 9	7, 2	8, 1	6, 9	6, 5
Ноябрь	-3, 0	-2, 5	-3, 8	-2, 5	-3, 5	-3, 2
Декабрь	-12, 5	-11, 8	-10, 3	-9, 4	-10, 5	-11, 2

 

3. Ниже создайте табл. 2.35 и произведите необходимые расчеты по формулам:

Максимальная температура =MAKC(B3: G14)

Минимальная температура =MИH(B3: G14)

Среднемесячная температура за год =CPЗHAЧ(B3: G14)

Число морозных месяцев (< -20 °С) =СЧЕТЕСЛИ(ВЗ: С14; »< =-20»

Число жарких месяцев (> 25 °С) =СЧЕТЕСЛИ(ВЗ: С14; »> 25»)

4. Постройте диаграмму (рис. 2.30) по данным табл. (2.35. Итоговая), используя Вставка • График • График с накоплением.

 

Таблица 2.35

Статистические данные за 2010-2015 гг.
Максимальная температура
Минимальная температура
Среднемесячная температура за год
Число морозных месяцев (< -20 °С)
Число жарких месяцев (> 25 °С)

Таблица 2.35. Итоговая

Статистические данные за 2010-2015 гг.
Максимальная температура	24, 5
Минимальная температура	-21
Среднемесячная температура за год	1, 7
Число морозных месяцев (< -20 °С)
Число жарких месяцев (> 25 °С)

Рис. 2.30

5. Сохраните файл.

 

Практическая работа 2

Цель работы: Создать таблицу по образцу и произвести вычисления, используя стандартные и логические функции.

Порядок выполнения работы

1. Откройте файл «Книга 4».

2. На Лист 5 введите следующие данные (табл. 2.36).

 

Таблица 2.36

Выручка торговой компании по магазинам в течение 2014г., р.
Месяц	Магазин
«Эльдорадо»	«Турист»	«Водник
Январь
Февраль
Март
Апрель
Май
Июнь
Июль
Август
Сентябрь
Октябрь
Ноябрь
Декабрь

3. Создайте табл. 2.37 и произведите необходимые вычисления по формулам:

Максимальная выручка в течение года =MAKC(B4: D15)

Минимальная выручка в течение года =MИH(B4: D15)

Среднегодовая выручка =CPЗHAЧ(B4: D15)

Суммарная выручка за год =CУMM(B4: D15)

Число убыточных месяцев (выручка< 10000р.) =СЧЕТЕСЛИ(В4: D15; »< 10000»)

Создайте табл. 2.37 для каждого магазина отдельно и произведите расчеты. На основании полученных данных постройте объемную разрезанную круговую диаграмму для каждого магазина (рис. 2.31).

 

Таблица 2.37

Максимальная выручка в течение года
Минимальная выручка в течение года
Среднегодовая выручка
Суммарная выручка за год
Число убыточных месяцев (выручка< 10000р.)

Таблица 2.37. Итоговая

Максимальная выручка в течение года
Минимальная выручка в течение года
Среднегодовая выручка
Суммарная выручка за год
Число убыточных месяцев (выручка< 10000р.)

Рис. 2.31

4. Сохраните файл.

 

⇐ Предыдущая1234

Поделиться:

Популярное:

1. II. Работа с раздаточным материалом
2. III. Работа по теме урока Представление журналов
3. IV. Работа над пройденным материалом.
4. MS Excel. Расчеты с условиями. Работа со списками
5. MS Word. Работа с математическими формулами
6. V. Работа над пройденным материалом.
7. VI. Рефлексия Работа по учебнику
8. XXIV. Охрана труда при работах в зоне влияния
9. XXXIV. Охрана труда при работах на электрофильтрах
10. А. Программирование работы гирлянды, работающей в режиме бегущей волны
11. Автоклавы для обеспечения безопасной работы снабжаются, также как и сосуды, работающие под давление, предохранительной и запорной арматурой, контрольно-измерительными приборами.
12. Анализ карты гидроизогипс и работа с ней