Фильтры, виды фильтров и их применение

Средства защиты данных

В Excel есть несколько команд, позволяющих защищать книги, структуры книг, отдельные ячейки, графические объекты, диаграммы, сценарии и окна от несанкционированного доступа или изменения. По умолчанию Excel блокирует (защищает) ячейки, графические объекты, диаграммы, сценарии и окна, но эта защита не вступает в силу, пока не выполнена команда Защита - Защитить лист меню СЕРВИС.

27.Табличный процессор Эксель. Списки и средства их обработки. Фильтры, виды фильтров и их применение.

Списком в Excel является таблица, строки которой содержат однородную информацию. Как правило, в виде списка оформляется та информация, которую кроме хранения необходимо каким-либо способом обрабатывать: систематизировать, обобщать, находить, делать выборку и т.д.

Список состоит из трех структурных элементов:

1) Заглавная строка

2) Запись

3) Поля

Существуют правила создания списка, которых необходимо придерживаться при формировании списка, чтобы иметь возможность использовать так называемые функции списка.

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

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

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

Сортировка списков – это переупорядочивание одного или более столбцов.

По умолчанию, списки сортируются в алфавитном порядке.

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

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

Excel содержит два варианта фильтрации: автофильтр и усиленный фильтр.

1 .Автофильтр осуществляет быструю фильтрацию списка в соответствии с содержимым ячеек или в соответствии с простым критерием поиска.

2.Расширенный фильтр предназначен для фильтрации списка в соответствии с заданными пользовательскими критериями. В отличие от автофильтра, критерии расширенного фильтра формируются и располагаются в области рабочего листа.

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

Фильтрация списка с помощью усиленного фильтра выполняется командой Фильтр - Расширенный фильтр меню ДАННЫЕ

28.Табличный процессор Эксель. Создание сводных таблиц, формирование общих и промежуточных итогов. Консолидация данных.

Создание сводных таблиц:

Сводная таблица – это таблица, предназначенная для более наглядного представления и анализа данных из существующих списков и таблиц.

Сводная таблица может быть создана:

- на основе данных любой таблицы или области таблицы рабочего листа;

- на основе данных нескольких таблиц, полученных в результате консолидации данных;

- на основе данных сводной таблицы;

- на основе данных, находящихся во внешнем источнике данных Microsoft Access, FoxPro, dBase и т.д.

Для создания сводной таблицы необходимо выделить источник данных и запустить программу Мастер сводных таблиц. Перед построением сводной таблицы из источника данных обязательно должны быть убраны промежуточные итоги и наложенные фильтры. Запустить программу Мастер сводных таблиц можно путем выбора команды Сводная таблица меню ДАННЫЕ или нажатием кнопки Мастер сводных таблиц инструментальной панели Сводные таблицы.

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

.

Чтобы Excel автоматически обновлял сводную таблицу при каждом ее открытии, следует выполнить команду Сводная таблица - Параметры на панели инструментов Сводные таблицы. В появившемся диалоговом окне установить флажок Обновить при открытии.

Формирование общих и промежуточных итогов:

Простейший способ получения итогов – с помощью команды Итоги меню ДАННЫЕ. Для выполнения этой команды необходимо:

- представить данные в виде списка;

- командой Сортировка меню ДАННЫЕ упорядочить записи списка в соответствии со значениями того поля, по которому будут подводиться промежуточные итоги;

- установить указатель на ячейку списка;

- выполнить команду Итоги меню ДАННЫЕ;

- в диалоговом окне Промежуточные итоги задать нужные параметры.

После выполнения команды Итоги меню ДАННЫЕ создается структура, в которой данные (таблица) структурированы, т.е. разбиты на несколько уровней. С помощью уровней структуры можно управлять выводом данных соответствующего уровня из таблицы на экран, указывая, выводить данные или скрывать.

Для удаления промежуточных итогов необходимо в диалоговом окне команды Итоги выбрать кнопку Убрать все. При удалении промежуточных итогов из списка удаляется и структура таблицы.

Консолидация – это объединение данных из одной или нескольких областей данных и вывод их в виде таблицы в итоговом листе. В Excel предусмотрено несколько способов консолидации данных:

- консолидация данных с помощью трехмерных ссылок;

- консолидация данных по расположению;

консолидация данных по категориям.

Первый способ наиболее простой. Он позволяет объединить данные консолидируемых областей формулами.

Консолидация данных по расположению используется, если консолидируемые данные находятся в одном и том же месте разных листов и размещены в одном и том же порядке.

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

29.Табличный процессор Эксель. Средства анализа данных: подбор параметров, сценарии, поиск решений.

1.Команда Подбор параметра меню СЕРВИС позволяет определить неизвестное значение (параметр), которое будет давать желаемый результат.

.

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

По умолчанию команда Подбор параметра прекращает вычисления, когда выполняется 100 итераций или при получении результата, который находится в пределах 0,001 от заданного целевого значения\

Команда Подбор параметра находит только одно решение, даже если задача имеет несколько решений.

2.Поиск решений может применяться для решения задач, которые включают много изменяемых ячеек, и помогает найти комбинацию переменных, которые максимизируют или минимизируют значение в целевой ячейке. Он также позволяет задать одно или несколько ограничений условий, которые должны выполняться при поиске решений. Для запуска этого инструмента следует выполнить команду Поиск решения меню СЕРВИС.

После заполнения диалогового окна Поиск решения следует нажать кнопку Выполнить. При нахождении оптимального решения на экран выводится диалоговое окно Результаты поиска решения.

Поиск решений может представлять свои результаты в виде трех отчетов: Результаты, Устойчивость и Пределы. Отчет по устойчивости содержит информацию о том, насколько целевая ячейка чувствительна к изменениям ограничений и переменных

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

Поиск решений может решить не каждую предложенную задачу. Если оптимальное решение не найдено, в окне диалога Результаты поиска решений выводится сообщение о неуспешном завершении.

3. Сценарий -- это набор значений, которые в приложении Microsoft Office Excel сохраняются и могут автоматически подставляться в лист. Сценарии можно использовать для прогноза результатов моделей расчетов листа. Существует возможность создать и сохранить в листе различные группы значений, а затем переключаться на любой из этих новых сценариев, чтобы просматривать различные результаты. Или можно создать несколько входных наборов данных (изменяемых ячеек) для любого количества переменных и присвоить имя каждому набору. По имени выбранного набора данных MS Excel сформирует на рабочем листе результаты анализа. Кроме этого, диспетчер сценариев позволяет создать итоговый отчет по сценариям, в котором отображаются результаты подстановки различных комбинаций входных параметров.

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

Дата добавления: 2015-04-24