Вопрос 46 Выполнение аналитической обработки данных в списке: сортировка, отбор данных по критерию; подведение итогов (ms Excel).

Анализ данных

MS Excel предоставляет широкие возможности для проведения анализа данных, которые находятся в списке MS Excel. К средствам анализа относятся:

• обработка списка с помощью различных формул и функций;

• построение диаграмм и использование карт MS Excel;

• проверка данных рабочих листов и рабочих книг на наличие ошибок;

• автоматическое подведение итогов (включая Мастер Частичных сумм);

• консолидация данных;

• сводные таблицы;

• структуризация рабочих листов;

• специальные средства анализа выборочных записей и данных – Подбор параметра, Поиск решения, Сценарии, Пакет анализа и др.

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

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

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

Рабочий лист может содержать только одну структуру, хотя ее можно разделить на несколько частей.

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

Отбор данных по критерию: использование автофильтра MS EXCEL.

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

автофильтр для простых условий отбора.

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

Автофильтр. После выполнения команды Данный Фильтр Автофильтр в нижнем правом углу ячеек — имен столбцов в фильтруемом списке появляются стрелки автофльтра.

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

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

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

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

Расширенный фильтр из меню Данные

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

Чтобы отфильтровать список с помощью расширенного фильтра, столбцы списка должны иметь заголовки имена полей. Нужно создать дополнительно к списку два диапазона — условий и результатов отбора, каждый диапазон должен начинаться с имен полей списка. Между диапазонами должно быть не менее одной пустой строки. Условия отбора помещают в одну строку если условие И или разные если условие ИЛИ.

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

 

Вопрос 47 Защита данных в электронной таблице (ms Excel)

Защита элементов листа от всех пользователей

Откройте лист, который требуется защитить.

Разблокируйте все ячейки, которые должны быть доступны пользователям для изменения: выделите каждую ячейку или диапазон, выберите в меню Формат команду Ячейки, откройте вкладку Защита, а затем снимите флажок Защищаемая ячейка.

Скройте все формулы, которые не должны отображаться: выделите ячейки с этими формулами, выберите в меню Форматкоманду Ячейки, откройте вкладку Защита, а затем установите флажок Скрыть формулы.

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

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

Удерживая нажатой клавишу CTRL, последовательно щелкните все объекты, которые требуется разблокировать.

В меню Формат выберите команду, соответствующую выделенному объекту: Автофигура, Объект, Надпись,Рисунок, Элемент управления или Объект WordArt.

Откройте вкладку Защита.

Снимите флажок Защищаемый объект и флажокСкрыть текст (если он отображается).

В меню Сервис укажите на пункт Защита и выберите командуЗащитить лист.

Введите пароль для защиты листа.

ПРИМЕЧАНИЕ. Пароль задавать необязательно; однако, если не задать пароль, любой пользователь сможет снять защиту с листа и изменить защищенные элементы. Убедитесь, что выбран пароль, который легко запомнить, так как если пароль будет утерян, получить доступ к защищенным элементам листа будет невозможно.

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

Нажмите кнопку OK и, если будет предложено, введите этот пароль еще раз.