Файл: Контрольные задания для заочников.doc

Расширенный фильтр снимает оговоренные в предыдущем методе ограничения. Кроме того, он позволяет производить вычисления над отфильтрованными записями.

2.1. Поиск автофильтром

Для поиска информации в БД с помощью автофильтра необходимо выполнить следующие действия:

• курсором выбрать стрелку того поля, по которому необходимо произвести поиск, например, «Направление перемещения товара» (см. рис.3);

Рис. 3. Поиск информации автофильтром

• выбрать в списке значение, по которому нужно осуществить поиск (в нашем случае выбираем ИМ  ОК  на экране отобразятся все записи, удовлетворяющие введенному значению);

• если необходимо выбрать информацию в БД по двум условиям (критериям поиска), например, введем критерий для поля «Цена товара, $»: 3000<= «Цена товара, $» <= 5000 (рис. 4), используя команду «Числовые фильтры» для поля «Цена товара, $».

Рис. 4. Пример пользовательского автофильтра

Такой поиск можно выполнять последовательно по нескольким полям. На одно поле можно наложить не более двух условий. После окончания поиска необходимо восстановить список, для этого: выберем кнопку «Сортировка и фильтр»  «Очистить».

2.2. Поиск расширенным фильтром

При расширенном поиске можно производить фильтрацию по нескольким полям и включать функции MS Excel. На одно поле можно накладывать несколько ограничений. Условия поиска формируются в отдельной, специально создаваемой рядом с основным списком таблице – интервал критериев.

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

Например, отберем те записи фирм ООО «СЕЛДОН», ГП «ПРЕССА», ООО «СОКОЛ» в которых цена товара более $1500 и менее $4000. Для этого MS Excel отберет те записи, в которых поле «Фирма-получатель» будет равно или ООО «СЕЛДОН», или ГП «ПРЕССА», или ООО «СОКОЛ». Значение поля «Цена товара, $» для этих фирм должно быть больше или равно 1500 и меньше или равно 4000.

Рис. 5. Критерии расширенного фильтра

Последовательность действий:

1. в списке файла Tabl.xls вставим перед таблицей 6 строк для удобства ввода критерия поиска;

2. сформируем критерий поиска (рис. 5) – скопируем (во избежание ошибок) на место заголовков критерия заголовки тех столбцов, на которые ставятся условия поиска, и введем условия отбора записей;

3. определим координаты ячеек, в которых сформирован критерий: E2:H5;

4. курсор поместим в любую ячейку списка, выберем вкладку «Данные»  «Сортировка и фильтр»  «Дополнительно»  на экране отобразится окно диалога «Расширенный фильтр» (рис. 6).

Мы хотим скопировать результаты поиска в другое место текущего листа, поэтому отметим соответствующую настройку в диалоговом окне и определим адреса ячеек для представления результатов поиска: A223:K269. Если в процессе выполнения поиска MS Excel обнаружит, что указанный диапазон маловат, то он выведет на экран соответствующее сообщение. В этом случае фильтрацию придется повторить с новыми координатами диапазона результата.

---

Рис. 6. Диалоговое окно «Расширенный фильтр»

Фактически мы выполнили запрос по образцу, с помощь которого отобрали те записи списка, которые соответствуют данному значению. Результат поиска в ячейках на рис. 7.

Кроме описанных выше критериев, можно проводить поиск еще по одному мощному типу критериев, который называется вычисляемым. Вычисляемый критерий, вводимый как обычный критерий при работе с расширенным фильтром, может включать формулы, в которых аргументами являются поля БД. Вводимые формулы могут быть составлены пользователем и включать функции MS Excel. В остальном поиск по вычисляемому критерию проводится аналогично рассмотренному нами примеру.

Рис. 7. Результаты работы расширенного фильтра по критериям

3. Функции базы данных

В MS Excel есть возможность сразу процессе поиска производить действия с отобранными данными. Для этого используются функции MS Excel категории «Работа с базой данных», которые обеспечивают с записями, удовлетворяющими введенному критерию, следующие действия:

• арифметические, включающие сложение и умножение величин, находящихся в ячейках отфильтрованных записей;

• статистический анализ величин, находящихся в ячейках отфильтрованных записей.

Пример: для таблицы Tabl.xls определим стоимость всех грузов, импортируемых фирмой-отправителем ДХЛ, используя функцию БДСУММ.

1. ввести критерии отбора записей для вычисления назначенной функции (критерии вводятся по тем же правилам, что и при работе с расширенным фильтром);

2. определите координаты ввода критерия (E2:F3);

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

4. выберем в мастере функций категорию «Работа с базой данных»  БДСУММ;

5. на экране отобразится диалоговое окно БДСУММ (рис. 8), при этом в область «Поле» вводится адрес поля, по которому вычисляется выбранная функция, в данном случае – суммирование.

Рис. 8. Окно функции БДСУММ

Результат введенных данных приведен на рис. 9.

Рис. 9. Результаты расчета функции БДСУММ

В тех случаях, когда необходимо просуммировать значения диапазона, то используется функция СУММЕСЛИ. Ячейки диапазона могут быть как смежными, так и несмежными, то есть соответствующие указанному условию.

В общем виде функция СУММЕСЛИ соответствует следующим правилам:

СУММЕСЛИ(диапазон; критерий; [диапазон_суммирования])

Функция СУММЕСЛИ имеет аргументы (Аргумент – значение, предоставляющее информацию для действия, события, метода, свойства, функции или процедуры.), указанные ниже.

Диапазон – обязательный аргумент. Диапазон ячеек, оцениваемых по критериям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые ячейки и ячейки, содержащие текстовые значения, пропускаются.

Критерий – обязательный аргумент. Критерий в форме числа, выражения, ссылки на ячейку, текста или функции, определяющий, какие ячейки необходимо просуммировать. Например, критерий можно выразить как 32, ">32", B5, "32", "яблоки" или СЕГОДНЯ().

---

Все текстовые критерии и критерии с логическими и математическими знаками необходимо заключать в двойные кавычки ("). Если критерием является число, использовать кавычки не требуется.

Диапазон_суммирования – необязательный аргумент. Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Microsoft Excel суммирует ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяется критерий).

В тех случаях, когда необходимо просуммировать ячейки в диапазоне, удовлетворяющие нескольким критериям, используется функция СУММЕСЛИМН.

Функции похожи, но, порядок аргументов в функциях СУММЕСЛИМН и СУММЕСЛИ различается!!!

В СУММЕСЛИМН аргумент диапазон_суммирования является первым аргументом, а в СУММЕСЛИ – третьим.

Например, необходимо рассчитать суммарный вес нетто по коду товара по ТН ВЭД ЕАЭС – 4911101000 отправителя «ДХЛ» (Рис. 10). Формула в ячейке М10 будет выглядеть так:

=СУММЕСЛИМН(J2:J214;E2:E214;"ДХЛ";G2:G214;4911101000).

Рис. 10. Пример функции СУММЕСЛИМН

Вернемся к нашему файлу. Как будет выглядеть формула, для подсчета количества оформленных деклараций по «ИМ»? Нам поможет функция СЧЁТЕСЛИ.

Функция СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые соответствуют одному указанному пользователем условию. Например, можно подсчитать количество всех ячеек, которые начинаются с определенной буквы или содержат числа, большие или меньшие указанного значения.

Рассчитаем количество деклараций оформленных по «ИМ» (Рис. 11):

=СЧЁТЕСЛИ(D2:D214;"ИМ").

Рис. 11. Пример функции СЧЕТЕСЛИ

4. Получение промежуточных итогов

Если необходимо получить промежуточные итоги по какому-то полю, то можно воспользоваться вкладкой «Данные»  «Промежуточный итог». Предварительно необходимо выполнить сортировку, группирующую строки в нужной последовательности.

Например, необходимо определить сумму торгового оборота по каждой фирме в таблице файла Tabl.xls. Для этого:

1. выберем последовательность сортировки (рис. 10): «Отправитель», «Направление перемещения товара», «Код таможенного органа», «Дата», «Номер ДТ»;

Рис. 10. Настройки окна «Сортировка»

2. для получения промежуточных итогов используется вкладка «Данные»  «Промежуточный итог»  ОК (рис. 11). Результат представлен на рис. 12. В конце таблицы обязательно присутствует Общий итог.

Рис. 11. Настройка диалогового окна «Промежуточные итоги»

Рис. 12. Результат применения «Промежуточных итогов»

Слева от номеров строк на экране присутствует символы структуры. Остановимся на этом более подробно.

Структура документа – это механизм, который позволяет управлять сокрытием или выводом на экран рабочей части листа. К символам структуры относятся кнопки (см. рис. 12), обозначающие уровни структуры. Так, под кнопкой 1 – выводится на экран «Общий итог» по БД, под кнопкой 2 – «Промежуточные итоги», а под кнопкой 3 – отображаются все уровни данных таблицы.

---

5. Сводная таблица

Другим способом выявления и отображения зависимостей между данными является сводная таблица. Необходимым условием для создания сводной таблицы является наличие заголовков столбцов в исходной БД. Одно из полей служит признаком для разбивки таблицы на страницы. Другие поля БД можно разместить в сводной таблице в виде строк или столбцов. При необходимости можно проводить вычисления над полями БД. Для формирования сводной таблицы необходимо:

1. выбрать вкладку «Вставка»  «Сводная таблица»;

2. в диалоговом окне «Создание сводной таблицы» выбираются данные для анализа, и указывается место размещения результата (рис. 13).

3. используя контекстное меню выберем команду «Параметры сводной таблицы»  вкладку «Вывод»  поставить флажок «Классический макет сводной таблицы»  ОК (рис. 14);

Рис. 13. Мастер «Создания сводной таблицы»

Рис. 14. Макет создания сводной таблицы

4. перетаскиваем кнопки с названиями полей БД из правой части «Список полей сводной таблицы» в соответствующие области проекта сводной таблицы. В области «Перетащите сюда поля фильтра отчета» выберем поле «Направление перемещения товара». Поля «Отправитель», «Код по ТНВЭД ТС» перемещаем в область «Перетащите сюда поля строк» сводной таблицы. В область «Данные» перемещаем поля, по которым будут производиться вычисления «Цена товара, $», «Номер ДТ» (рис. 15).

Рис. 15. Результат формирования «Сводной таблицы»

6. Диаграммы

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

Величины, по которым строятся диаграммы, бывают двух видов: непрерывные и дискретные. Для наглядного представления каждого вида величин целесообразно применять определенные типы диаграмм (табл. 1).

Таблица 1

Типы диаграмм

Величины	Двухмерные диаграммы	Трехмерные диаграммы
Дискретные	Гистограммы	Гистограммы
Непрерывные	Графики	Поверхности
Смешанные	Двух осевые	Графики
Одна величина	Круговые	Кольцевые

В таможенной статистики наиболее распространены гистограммы и графики для отражения экономических процессов. Для представления данных в долях используются круговые и кольцевые диаграммы.

6.1. Алгоритм построения столбиковой диаграммы

Рассмотрим построение столбиковой диаграммы «Импортируемые автомобили» на примере столбцов «Количество» таблицы (рис. 16).

• Выделите диапазон значений: A6:B13  выбрать вкладку «Вставка»  в области «Диаграммы» выбрать требуемый вид, например,

Рис. 16. Исходные данные для анализа

«Гистограмма» (рис. 17)  в появившемся ниспадающем меню выбрать вид гистограммы, например, «Гистограмма с группировкой»  на экране появляется выбранная диаграмма (рис. 18).

---

Рис. 17. Вид «Гистограмма»	Рис. 18. Результат построения

• Для отображения на диаграмме количества иностранных автомобилей следует добавить еще один ряд значений. Для этого активизируем диаграмму правой кнопкой мыши (вызвав контекстное меню) и выберем команду «Выбрать данные» (рис. 19)  в появившемся окне диалога добавим новый ряд D6:D13, используя кнопку «Добавить» в области «Элементы легенды (ряды)» (рис. 20). Используя кнопку «Изменить» присвоим имя «Ряду1» – «Задекларировано транспортных средств Российского происхождения» (щелкнув по адресу ячейки B4), аналогично добавим название «Ряду2» (рис. 19).

Рис. 19. Диалоговое окно «Выбор источника данных»

Рис. 20. Настройка выбора источника данных

• Для выбора местоположения диаграммы (например, на отдельном листе в MS Excel) воспользуемся командой контекстного меню «Перемещение диаграммы» и в появившемся диалоговом сделаем соответствующий выбор (рис. 21).

Рис. 21. Окно «Перемещение диаграммы»

• Для оформления подписи диаграммы воспользуемся вкладкой «Макет» (она доступна, когда активна построенная диаграмма)  в области «Подписи» выберем: «Название диаграммы», «Название осей» (рис. 22).

Рис. 22. Окончательный вид диаграммы

6.2. Применение диаграмм для анализа данных

Для выявления тенденций или направления изменения ряда данных, полученных экспериментальным путем, используют направленную линию – линию тренда. Эти линии часто применяют для демонстрации скользящих средних значений, которые сглаживают колебания данных, или при решении задач прогнозирования исследуемого процесса.

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

В задачах прогнозирования и статистического анализа линию тренда называют линией регрессии. Регрессионный анализ позволяет определить конкретный вид зависимости, которая связывает переменные X и Y, составляющие набор пар значений из некоторой совокупности наблюдений.

MS Excel предлагает несколько типов линии регрессии (рис. 23).

Модель прямой используется для описания динамики равномерно изменяющихся процессов. Примером могут служить данные об изменении доходов.

Для процессов с постоянными темпами роста используется экспонента.

Рис. 23. Типы линии регрессии

Логарифмическая функция служит для описания процессов с замедленным ростом, таких как оценка численности населения или некоторые экономические процессы.

Полиномиальный ряд или набор парабол служит для равноускоренных (равнозамедленных) процессов. Последние две модели могли бы подойти, например, для описания роста экспорта или падения импорта.

Для добавления на построенной диаграмме линии регрессии (например, для оценки количества задекларированных импортных автомашин), щелкнем в построенной диаграмме правой кнопкой мышки по требуемому ряду и в контекстном меню выберем команду «Добавить линию тренда»  выберем, например, логарифмический тип, отметив флажком область «Показывать уравнение на диаграмме» этого окна, отобразим соответствующее уравнение (рис. 24).

---