лаба ит 2
.pdf
В задании так же указано, что дата размещения заказа должна быть позже 1 января 1992 года. Следовательно, из оставшихся значений нам нужно отсеять те, которые были размещены до 1 января 1992 года. Это можно сделать так же через фильтр, указав нужные значения.
Выберем нужные нам года в окне фильтра и нажмем ОК:
После этого в таблице останутся только те строки которые удовлетворяют условию РАЗМЕЩЕНЫ ПОСЛЕ 1 ЯНВ 1992 ГОДА и СТОИМОСТЬ ЗАКАЗА БОЛЬШЕ 50.
21
Б) Для расширенной фильтрации с каким-либо условием нужно воспользоваться расширенным фильтром. Но прежде этого следует построить дополнительную таблицу условий фильтрации. В нашем случае она будет выглядеть таким образом:
Расширенный фильтр представляет собой автофильтр, выполняющий фильтрацию по одному или нескольким условиям одновременно. Самый удобный метод использования автофильтра, это использование макроса на языке VBA. Код для фильтрации выглядит следующим образом:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:N5")) Is Nothing Then
On Error Resume Next
ActiveSheet.ShowAllData
Range("A7").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace,
CriteriaRange:=Range("A1").CurrentRegion
End If
End Sub
22
Для корректной работы данного макроса следует указать правильные значения фильтруемой области и области критериев. Нас интересует следующее условие: (Название получателя ТОО… и Доставка =1) или Страна получателя – Украина.
Для этого в нужные поля впишем данные значения, следует отметить, что для фильтрации с условием или следует указывать значения в разных строках таблицы критериев. После чего получим нужные нам данные:
В) Вычисление средней стоимости доставки для каждого клиента. Для выполнение данной задачи нам потребуется создать сводную таблицу. После ее создания нам необходимо выбрать поле для отчета Стоимость доставки и перетащить его в поле значение. После чего необходимо установить параметр полей значений как Среднее по полю Сумма доставки. После чегом получим среднюю стоимость доставки для каждого из клиентов:
23
4.4Задание 4
Известны наименование и масса каждого из 15 грузов (см. рис.):
Определить среднюю массу блоков. Решение
Для выполнения этой задачи постоим нужную нам таблицу в среде Exel.
Для нахождения средней массы блока можно воспользоваться двумя способоми: Способ 1.
Воспользуемся специфической функцией БДСУММ, которая суммирует числа в поле (столбце) записей списка или базы данных, которые удовлетворяют заданным условиям.
Общий синтаксис данной команды: БДСУММ(база_данных; поле; условия), где База_данных — диапазон ячеек, образующих список или базу данных. База данных
представляет собой список связанных данных, в котором строки данных являются записями, а столбцы — полями. Верхняя строка списка содержит заголовки всех столбцов. В данном случае – это диапазон B2:С16.
Поле — столбец, используемый функцией. Введите текст с заголовком столбца в двойных кавычках, например, "Возраст" или "Урожай", или число (без кавычек), задающее положение столбца в списке: 1 — для первого столбца, 2 — для второго и т. д. В данном случае суммируются данные в 4-ом столбце.
Условия — интервал ячеек, который содержит задаваемые условия. В качестве значения аргумента «условия» может использоваться любой интервал, содержащий по крайней мере один заголовок столбца и по крайней мере одну ячейку с условием, расположенную под заголовком столбца. В данном случае как раз диапазон B1:B2 и является тем самым условием. В другом случае его можно набрать отдельно от таблицы.
24
Для получения среднего значения получившуюся сумму необходимо разделить на колличество позиций блока в списке.
В результате получим:
Способ 2.
Создадим дополнительный столбец, содержащий массу материала если его название начинается на Б, либо 0 в противном случае. Очевидно формула для первой ячейки такого столбца будет иметь вид: =ЕСЛИ(B2="Блок";C2;0). Протянув данную ячейку, получим весь столбец с данными:
Для нахождения средней массы блока в произвольную ячейку впишем формулу =СРЗНАЧЕСЛИ(D2:D16;">0") после чего получим:
25
4.5Задание 5
1)Введите данные, отражающие расчет заработной платы.
2)Выполнить расчет заработной платы с учетом количества отработанных часов
истоимости одного часа работы.
3)Автоматизировать процессы поиска средней зарплаты и сотрудников, получающих зарплату вышесредней.
4)Постройте диаграммы, наиболее удачно отражающие 1) распределение заработной платы по преподавателям; 2) изменение заработной платы у преподавателя 11 при изменении его часов работы и его ставки часов.
1)
2) Построим таблицу и заполним ее соответствующими данными. Для подсчёта подоходного налога в ячейку D2 запишем формулу =B2*C2*13/100, протянем ее до конца списка и получим значения налога для всех преподавателей. Затем нам нужно узнать значение столбца Удержано и К выдаче. Значения этих столбцов будут совпадать, поэтому в ячейку Е2 впишем формулу =$B2*$C2-$D2 и растянем ее в диапазоне E2:F12. Для подсчета суммарного количества выплат в ячейку F13 впишем формулу =СУММ(F2:F12).
В результате получим:
3)Для автоматизации процесса подсчёта средней заработной платы в ячейку F14 впишем формулу =СУММ(F2:F12)/11 в итоге получим:
26
Для поиска сотрудников, получающих зарплату выше средней применим фильтр для столбца к выдаче. Применим для него значение больше 1349,805 и получим следующий результат:
2)Для построения диаграммы снимем все фильтры и выделим столбец к выдаче
ивоспользуемся функцией построение диаграммы, находящейся на панели Вставка на главной ленте.
Нас интересует круговая или кольцевая диаграмма, после выбора интересующего нас типа можем выбрать более подходящий дизайн и укажем заголовок:
27
3) Для построения диаграммы для Преподавателя 11 укажем дополнительные значения часов и часовой оплаты в новых столбцах, после чего по данным этих столбцов построим линейчатую диаграмму. В итоге получим:
28
Список используемой литературы
1.Расширенный фильтр в Exel [Электронный ресурс] http://www.planetaexcel.ru/techniques/2/197/
2.Поддержка по Office [Электронный ресурс] - http://office.microsoft.com/ruru/support
3.Самоучитель Office 2013 / В. С. Пташинский. — М.: Эксмо, 2013. — 288 с. — (Компьютер на 100%).
29