Задания для самостоятельной работы. В рабочей книге каждый лист отвести для решения одной задачи
В рабочей книге каждый лист отвести для решения одной задачи. Каждому листу дать название задание 1, задание 2, …, задание 15.
Отчет по выполненной работе должен содержать распечатку каждой отформатированной расчетной таблицы и таблицы с включенными формулами.
Задание 1
В таблице приведены данные о количестве легковых автомобилей, выпущенных отечественными автомобильными заводами в первом полугодии 2001 года.
| Автозавод | Январь | Февраль | Март | Апрель | Май | Июнь |
| АвтоГАЗ | ||||||
| КамАЗ | ||||||
| АвтоВАЗ | ||||||
| УАЗ | ||||||
| Москвич | ||||||
| Рослада | ||||||
| ТагАЗ |
Определить:
- сколько автомобилей выпускал каждый завод в среднем за 1 месяц и распределить их по местам (проранжировать);
- сколько автомобилей выпускалось в среднем на одном заводе за каждый месяц и распределить их по местам (проранжировать);
- три наибольших и три наименьших значения выпуска автомобилей в первом полугодии.
Задание 2
Заработная плата каждого из 12 работников фирмы за каждый месяц первого квартала записана в таблице
| № п/п | Ф.И.О. | 1 квартал | ||
| Январь | Февраль | Март | ||
| Иванов М.И. | 4050,23 | 4100,33 | 4090,55 | |
| Сидоров К.А. | 5020,60 | 5070,88 | 5103,55 | |
| … | ||||
| Вешкин А.А. | 4068,80 | 4104,88 | 4203,85 |
Определить:
- сколько в среднем получал каждый работник за один месяц;
- среднюю заработную плату на одного работника;
- среднемесячную зарплату на одного сотрудника в месяц;
- максимальную и минимальную зарплату.
Задание 3
В автомобиль загружаются грузы пяти видов. О каждом виде груза известны масса 1 штуки и количество штук. Подготовить таблицу следующего вида
| Груз | Количество мешков | Масса 1 шт |
| Сахар | ||
| Соль | ||
| Мука | ||
| ИТОГО |
Определить общий вес перевозимых грузов. При решении задачи использовать функцию СУММПРОИЗВ.
Задание 4
На предприятии зарплата каждому сотруднику рассчитывается пропорционально количеству отработанных за месяц часов. Подготовить таблицу следующего вида
| Ф.И.О. сотрудника | Отработано часов | Стоимость 1 часа |
| Иванов И.И. | 2,33 руб. | |
| Петров С.П. | 1,18 руб. | |
| Сидоров К.М. | 1,56 руб. | |
| ИТОГО |
Определить общую зарплату за месяц всех сотрудников, если для каждого сотрудника известен размер оплаты за 1 час работы и количество отработанных за месяц часов. При решении задачи использовать функцию СУММПРОИЗВ.
Задание 5
В сельскохозяйственном кооперативе под пшеницу выделили несколько участков земли. Известна площадь каждого участка (в га), а также средняя урожайность одного гектара (в тоннах зерна), рассчитанная по результатам предыдущего года.
| Участки | Площадь участка, га | Средняя урожайность, т |
| Участок 1 | 3,2 | |
| Участок 2 | 6,2 | |
| Участок 3 | 2,1 | |
| ИТОГО |
Определить ожидаемый урожай пшеницы (из расчета средней урожайности предыдущего года). При решении задачи не использовать функцию СУММ.
Задание 6
Заполните диапазон ячеек А1:В6 числами и определите, сколько раз в этом диапазоне встречается число 12.
Задание 7
В ячейках диапазона записаны числа. Определить сумму чисел, превышающих некоторое число, значение которого будет записано в отдельной ячейке.
Задание 8
Известны оценки по Информатике каждого из 15 студентов. Оформить лист для определения числа учеников, имеющих оценку 5.
Задание 9
Известны данные о температуре воздуха за каждый день марта. Определить, сколько раз температура опускалась ниже нуля.
Задание 10
Известны данные о количестве осадков, выпавших за каждый день июля. Определить, в течение какого количества дней в этом месяце осадки были.
Задание 11
Известен возраст 30 человек. Определить, сколько из них могут рассматриваться в качестве претендентов для приема на работу в фирму, если по условиям возраст претендентов не превышает 35 лет.
Задание 12
Известна сумма баллов, набранная студентами при сдаче контрольной точки. Определить число студентов, сдавших контрольную точку, если минимальное количество баллов равно 60.
Задание 13
Известны данные о стоимости каждого из 12 наименований товара.
| № п/п | Товар | Цена | Количество | Сумма |
| Бумага для ксерокса | ||||
| Стиплер | ||||
| … | ||||
| Маркер | ||||
| Итого | ||||
| Общая стоимость товаров, сумма которых превышает 1000 | ||||
| Общее количество товаров, сумма которых превышает 1000 |
Задание 14
Известна информация о багаже (количество вещей и общий вес багажа) 24 пассажиров.
| № п/п | Пассажир | Количество вещей | Общий вес, кг |
| Иванов И.И. | |||
| Соколов М.Л. | |||
| … | |||
| Петров И.Б. | |||
| Суммарный вес багажа | |||
| Суммарный вес багажа пассажиров, имеющих 1 вещь | |||
| Суммарный вес багажа, имеющих более 2 вещей |
Задание 15
Известна стоимость каждого товара, а также его вид.
| № п/п | Вид товара | Товар | Цена | Количество | Сумма |
| Хлебобулочные | Хлеб белый | 9,5 | |||
| Кондитерские | Лакомка | 7,0 | |||
| Хлебобулочные | Батон нарезной | 8,5 | |||
| Кондитерские | Птичье молоко | 12,0 | |||
| Хлебобулочные | Бородинский | 9,6 | |||
| Кондитерские | Сочник | 7,5 | |||
| Итого | |||||
| Итого по хлебобулочным изделиям | |||||
| Итого по кондитерским изделиям | |||||
| Общая сумма товаров, цена которых не превышает 10 рублей |
Дата и время
Цель: практические навыки использования функций Excel для подведения итогов.
Общие сведения о Датах
Даты в Excel хранятся как количество дней, прошедших от 1 января 1900 года. Это можно проверить введя в ячейку А1 дату 1.01.1900. Далее расположите в ячейках А2:А10 даты 2.01.1900, 3.01.1900 … Для этого воспользуйтесь функцией Ecxel Автозаполнение или пунктом меню Правка/ Заполнить/ Прогрессия. А затем скопируйте блок А1:А10 в В1:В10. Наложите на блок В1:В10 формат Общий, для этого выполните команду Формат/Ячейки и в появившемся диалоговом окне на вкладке Число выберите Основной. Вы увидите, что в диапазоне В1:В10 хранится арифметическая прогрессия 1,2, …, 100. Благодаря тому, что даты представляют собой порядковые числа, с ними можно производить вычисления. Например, введя формулу =10.01.1900-3.01.1900 (в нашем примере =A10-A3), можно вычислить, сколько дней прошло между указанными датами. Можно прибавлять к дате и вычитать определенное количество дней.
Наложите на ячейки в блоке В1:В10 по очереди различные форматы даты. Для этого выполните команду Формат/ Ячейки и на вкладке Число в окне Числовые форматы выберите формат Дата, а в окне Тип представление даты.
Далее перечислены некоторые функции, которые используются при работе с датами. Подробную информацию о работе функций смотрите в справке Excel.
| СЕГОДНЯ | Возвращает текущую дату в числовом формате. Если до ввода этой функции форматом ячейки был Общий, результат будет отформатирован как дата |
| ДАТА | Возвращает целое число, представляющее определенную дату |
| ДЕНЬНЕД | Возвращает день недели, соответствующий аргументу дата_в_числовом_формате. День недели определяется как целое в интервале от 1 (воскресенье) до 7 (суббота) |
| ДЕНЬ МЕСЯЦ ГОД | Возвращают день, месяц, год, соответствующий аргументу дата_в_числовом_формате. День, месяц, год определяются как целое в соответствующих интервалах: для дня 1-31, для месяца 1-12, а для года 1900-9999 |
Для получения в ячейке текстовой строки с названием дня или месяца используют функцию ТЕКСТ (значение, форматная строка).
| Формат | Описание |
| дд | День месяца |
| ддд | Краткое название дня недели (пн, вт и т.д.) |
| дддд | Полное название дня недели (понедельник, вторник и т.д.) |
| ММ | Порядковый номер месяца. Обязательно большие буквы |
| МММ | Краткое название месяца (янв, фев и т.д.) |
| ММММ | Полное название месяца (январь, февраль и т.д.) |
| гг | Две последние цифры года (04, 05 и т.д.) |
| гггг | Все цифры года (2004, 2005 и т.д.) |
Например, формула =ТЕКСТ(А1, “дддд, дд ММММ ГГГГ”) отобразит текстовую строку воскресенье, 01 январь 1900.
Общие сведения о Времени
Время в Excel хранится в числовом формате как доля суток. Вводится время в ячейку с использованием двоеточия для разделения часов, минут и секунд. Например, 15:56:48 означает 15 ч. 56 мин 48 с. Секунды можно не вводить, например 15:56. Можно не вводить и минуты, но двоеточие должно присутствовать обязательно, например 15.
Благодаря числовому внутреннему представлению времени с ним можно производить вычисления. Например, введя формулу =11:20:00-10:35:15, можно вычислить, сколько прошедшее число часов, минут и секунд в нашем примере 00:44:45.
Накладывая определенный формат ячейки, можно найти продолжительность только в часах, минутах или секундах. Например, для того чтобы подсчитать, сколько секунд прошло между указанными промежутками времени, выполните команду Формат/ Ячейки, во вкладке Число в окне Числовые форматы выберите (все форматы), в окне Тип введите [cc] и нажмите ОК. Вы получите результат 2685 с. Проверьте полученный результат: 44*60+45=2685. Итак, заключение форматного символа в квадратные скобки позволяет вычислять число часов, минут и секунд между двумя моментами времени.
[ч] для часов, [мм] для минут, [сс] – для секунд.
Обратите внимание, что форматный символ минут всегда имеет строчные буквы для того, чтобы отличить их от форматного символа месяца.
Ко времени можно прибавлять и часы, минуты или секунды как вещественные числа, однако, в этом случае их следует представлять в виде долей суток. Например, введем в ячейку Е1 время окончания урока 10:05:00, а в ячейку А2 перерыв 15 минут, но не как время (00:15), а как вещественное число – 10. Для того чтобы в ячейке Е3 получить новое значение времени, следует к значению времени, записанному в ячейке Е1, прибавить долю суток, которые составляют 10 минут.
Рассмотрим варианты, когда ко времени 10:05:00, записанном в ячейку E1, добавляется 10 часов, минут и секунд, занесенные в ячейку Е2:
=Е1+Е2/24 прибавит 10 часов новое время 20:05:00
=Е1+ Е2/1440 прибавит 10 минут новое время 10:15:00
=Е1+ Е2/86400 прибавит 10 секунд новое время 10:05:10
В ячейку можно одновременно ввести дату и время. В этом случае сначала вводится дата, а потом через пробел время. Например, введите в ячейку С10 следующее 21.03 12: и в ячейке отобразится 21.03.05 12:00. Наложите общий формат. Вы получите число 38432,5, т.е. количество дней и долю суток. Таким образом с ячейками, в которых введены одновременно и дата и время, можно производить вычисления, описанные выше.