Задания для самостоятельной работы. В рабочей книге каждый лист отвести для решения одной задачи
В рабочей книге каждый лист отвести для решения одной задачи. Каждому листу дать название задание 1, задание 2, …, задание 10.
Отчет по выполненной работе должен содержать распечатку каждой отформатированной расчетной таблицы и таблицы с включенными формулами.
Задание 1
На рабочем листе приведены данные о составе учебной группы. Определить, какую долю (в %) составляют юноши и девушки.
| Количество, чел | Доля, % | |
| Юноши | ||
| Девушки | ||
| Всего |
Задание 2
Имеется список сотрудников фирмы и их окладов. Подготовить лист для расчета премии каждого сотрудника, если премия выплачивается в % от оклада (процент премии указан в ячейке С2).
| % премии | 60 % | |
| Фамилия И.О. | Оклад | Премия |
| Иванов И.И. | ||
| Сидоров А.Р. | ||
| Федоров В.Л. | ||
| Соколов М.Д. |
Задание 3
Известна раскладка на одну порцию плова. Подготовить лист для расчета массы продуктов, необходимых для приготовления заказанного числа порций, которое будет задаваться в отдельной ячейке – В2.
| Число порций | ||
| Продукт | Раскладка на 1 порцию | Необходимое количество продуктов |
| Мясо | ||
| Лук репчатый | ||
| Морковь | ||
| Рис | ||
| Масло растительное |
Задание 4
Подготовить лист для расчета цены каждого из 5 наименований товара с учетом скидки, величина которой указывается в ячейке С2.
| Размер скидки, % | 15 % | ||
| № п/п | Наименование товара | Цена | Цена со скидкой |
| Шуба норковая | |||
| Дубленка женская | |||
| Куртка кожаная | |||
| Костюм кожаный | |||
| Полушубок норковый |
Задание 5
Оклад работников организаций бюджетной сферы определяется по Единой тарифной сетке (ЕТС) следующим образом. Каждому работнику присваивается разряд от 1-го до 18-го, а для каждого разряда устанавливается коэффициент, по которому определяется оклад работника путем умножения коэффициента на минимальный размер оплаты труда (МРОТ). Оформить лист для расчета оклада работника каждого разряда.
| МРОТ: | ||
| Разряд ЕТС | Коэффициент | Оклад |
| 1,11 | ||
| 1,23 | ||
| 1,36 | ||
| 1,51 |
Задание 6
Подготовить лист, с помощью которого можно, задавая показания счетчика электроэнергии, определять ее расход и сумму оплаты. Тариф (стоимость 1 кВт·ч электроэнергии) задается в ячейке С1.
| Тариф | 1,25 | коп/кВт ч | ||
| Месяц | Дата | Показания счетчика | Расход, кВТ ч | Сумма, руб. |
| Январь | 12.01 | |||
| Февраль | 14.02 | |||
| Март | 10.03 | |||
| Апрель | 15.04 |
Задание 7
В таблице приведена цена нескольких наименований товаров. При покупке двух-пяти штук товара цена единицы товара уменьшается на величину (в %), значение которой будет указано в ячейке С24, при покупке более пяти штук – на величину (в %), значение которой будет указано в ячейке D4. Подготовить лист для определения новых цен.
| Цена товаров, со скидкой и без | |||
| Наименование товара | Объем покупок, шт | ||
| 2-5 | Больше 5 | ||
| Скидка, % | нет | 3 % | 5 % |
| Холодильник | |||
| Телевизор | |||
| Пылесос |
Задание 8
Подготовить лист для определения стоимости подписки на 1, 2, … 6 месяцев различных газет и журналов. Исходными данными для расчета являются стоимости подписки каждого издания на 1 месяц, которые будут указываться в ячейках В4:В9.
| Стоимость подписки | ||||||
| Название издания | Количество месяцев | |||||
| Аргументы и факты | ||||||
| Собеседник | ||||||
| Известия | ||||||
| Вокруг света |
Задание 9
Подготовить лист для определения стоимости квартир по заданной цене каждого в условных единицах и в зависимости от курса, который будет указываться в ячейке С1.
| Курс | ||
| Наименование | Цена, у.е. | Цена, руб. |
| 3-х комн., центр | ||
| 2-х комн., евроремонт | ||
| 3-х комн., без ремонта | ||
| 1-комн., перепланир. | ||
| 2-комн., 1-й этаж |
Задание 10
При подаче налоговой декларации необходимо заполнить форму следующего содержания. Значения в последней колонке получить путем копирования (распространения) формулы, введенной в одну ячейку.
| Месяц | Доход (зарплата за месяц) | Общий доход (зарплата с начала года) |
| Январь | ||
| Февраль | ||
| Март | ||
| Апрель | ||
| Май | ||
| Июнь | ||
| Июль | ||
| Август | ||
| Сентябрь | ||
| Октябрь | ||
| Ноябрь | ||
| Декабрь |
Итоговые функции
Цель: практические навыки использования функций Excel для подведения итогов.
Краткие сведения
Некоторые функции Excel возвращают одно значение, в то время как аргументов имеют блок или несколько блоков. Будем называть такие функции ИТОГОВЫМИ. Наиболее часто используемой из таких функций является СУММ. Эта функция как бы подводит итог колонке чисел – отсюда и название для всей группы. Эти функции входят в категории «Статистические» и «Математические».
В таблице 2 перечислены некоторые итоговые функции, которые используются при решении нижеприведенных задач. Подробную информацию о работе функций смотрите в справке Excel.
Таблица 2
Итоговые функции
| Функция | Назначение |
| СУММ | Суммирует все числа в интервале ячеек |
| СЧЕТ | Подсчитывает количество чисел в списке аргументов |
| СУММЕСЛИ | Суммирует ячейки, заданные критерием |
| СЧЕТЕСЛИ | Подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию |
| СРЗНАЧ | Возвращает среднее (арифметическое) своих аргументов |
| МАКС | Возвращает наибольшее значение из набора значений |
| МИН | Возвращает наименьшее значение в списке аргументов |
| НАИБОЛЬШИЙ | Возвращает k-ое по величине значение из множества данных. Эта функция позволяет выбрать значение по его относительному местоположению, например, для определения наилучшего, второго или третьего результатов тестирования в баллах |
| НАИМЕНЬШИЙ | Возвращает k-ое наименьшее значение в множестве данных. Эта функция используется для определения значения, занимающего определенное относительное положение в множестве данных |
| СУММПРОИЗВ | Перемножает соответствующие элементы заданных массивов и возвращает сумму произведений |
| РАНГ | Возвращает ранг числа в списке чисел. Ранг числа — это его величина относительно других значений в списке. (Если список отсортировать, то ранг числа будет его позицией.) |
Пример задания
Необходимо из отчетной ведомости, рассчитанной на первом практическом занятии, по результатам работы сети торговых точек за IV квартал, подсчитать:
- минимальную и максимальную выручку по всем торговым точкам;
- найти тройку лучших и тройку худших реализаций по всем торговым точкам;
- среднюю суммарную выручку по каждой торговой точке за три месяца и распределить их по местам (проранжировать);
- количество торговых точек, в которых сумма реализации превышала среднюю суммарную выручку, по всем торговым точкам.
Минимальная и максимальная выручка
Введите в ячейку А9 и В9 соответственно заголовки: минимум и максимум. Для того чтобы подсчитать минимальное значение в указанном диапазоне, воспользуйтесь мастером функций, который вызывается по нажатию кнопки 
, расположенной в строке формул. В появившемся диалоговом окне, представленном на рисунке 17, в поле Категория укажите соответствующую категорию, в нашем случае Статистические, и выберите нужную функцию из списка, представленного в поле Выберите функцию. Нажмите кнопку ОК.
Рис. 17. Первый шаг мастера функций
На втором шаге мастера задайте аргументы функции. На рисунке 18 в поле Число1 задайте интервал ячеек В3:D6. В нашем случае этот диапазон содержит сведения о всех реализациях за квартал, кроме итоговых значений.
Рис. 18. Второй шаг мастера функций
Нажмите кнопку ОК.
Действуя по аналогии, самостоятельно найдите максимальное значение в том же диапазоне, для чего воспользуйтесь функцией МАКС.
Тройка лучших результатов
В диапазон ячеек С9:С11 проставьте места 1, 2 и 3 соответственно, а в ячейку С12 введите Лучшие. Результаты будем подсчитывать в диапазон ячеек D9:D11.
Для подсчета результатов вызовите мастер функций и выберите в категории Статистические функцию НАИБОЛЬШИЙ и нажмите кнопку ОК На втором шаге задайте аргументы функции (рис. 19).
Рис. 19. Аргументы функции НАИБОЛЬШИЙ
В качестве массива задайте тот же диапазон В3:D6, однако ссылку на него сделаете абсолютную, т.е. нажмите клавишу [F4], для появления абсолютной адресации $В$3:$D$6.
В поле К укажите на ячейку С9, которая содержит искомое место. В нашем примере ячейка С9 содержит 1.
Нажмите кнопку ОК.
Введенную в ячейку D9 функцию с помощью маркера заполнения протащите на диапазон D10:D11. Обратите внимание, что ссылка на диапазон не изменилась, а значение мест «перенастраивалось». Теперь, если Вы введете, например, в ячейку С11 значение 5 и нажмете клавишу [Enter], то значение в ячейке D11 изменится.
Аналогично найдите три самых худших результата, для чего воспользуйтесь функцией НАИМЕНЬШИЙ.
Среднее значение
Введите в ячейку G2 заголовок столбца: Среднее. Для подсчета среднего значения по строке вызовите мастер функций и выберите в категории Статистические функцию СРЗНАЧ и нажмите кнопку ОК. На втором шаге задайте аргументы функции (рис. 20).
Рис. 20. Аргументы функции СРЗНАЧ
На рисунке 20 в поле Число1 задайте интервал ячеек В3:D3. В нашем случае этот диапазон содержит сведения о реализациях за квартал по одной торговой точке. Нажмите кнопку ОК.
Введенную в ячейку G3 функцию с помощью маркера заполнения протащите на диапазон G4: G7.
Ранжирование результатов
Введите в ячейку Н2 заголовок столбца: Ранг. Для ранжирования результатов работы торговых точек за квартал вызовите мастер функций и выберите в категории Статистические функцию РАНГ и нажмите кнопку ОК. На втором шаге задайте аргументы функции (рис. 21).
Рис. 21. Аргументы функции РАНГ
В поле Число задайте адрес ячейки, для которой определяется ранг. Обязательно ячейка должна быть первой в диапазоне. В нашем случае это ячейка Е3, содержащая суммарную выручку по первой торговой точке.
В поле Ссылка задайте ссылку на диапазон ячеек, внутри которого будем проводить ранжирование. В нашем случае это диапазон $Е$3:$Е$6.
В поле Порядок - число, определяющее способ упорядочения. Введите 0, так как упорядочение будет по убыванию. Для сортировки по возрастанию следует ввести любое ненулевое число.
Нажмите кнопку ОК.
Введенную в ячейку Н3 функцию с помощью маркера заполнения протащите на диапазон Н4:Н6.
Количество реализаций, превышающих среднюю
Сначала в отдельной ячейке подсчитайте среднее значение всех реализаций, воспользовавшись функцией
=СРЗНАЧ(B3:D6).
Затем в мастере функций выберите в категории Статистические функцию СЧЕТЕСЛИ и нажмите кнопку ОК. На втором шаге задайте аргументы функции (рис. 22).
Рис. 22. Аргументы функции СЧЕТЕСЛИ
В поле Диапазон задайте диапазон ячеек, в котором нужно подсчитать ячейки. В нашем случае это диапазон B3:D6.
В поле Критерий — критерий отбора. Критерий отбора может содержать ссылку на ячейку, но в этом случае будет проверяться условие равенства. В нашем случае введите критерий >360, где 360 – это среднее значение реализаций.
Нажмите кнопку ОК.
В результате всех расчетов Вы должны получить таблицу, представленную на рисунке 23.
Рис. 23. Результаты расчетов