Фильтрация, сортировка, промежуточные итоги

Лабораторная работа № 4

РАБОТА С БАЗАМИ ДАННЫХ В MS EXCEL

Закрепление строк и столбцов, разбиение окон

Чтобы определенная область листа оставалась видимой при прокрутке к другим областям, можно заблокировать определенные строки или столбцы: Вид[1] – Закрепить области. Для разблокировки используют команду Вид¹ – Закрепление области – Снять закрепление областей.

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

Упражнение 1. В подготовленном списке (лист Продажи) закрепите строку имен полей, чтобы она не исчезала при перемещении по экрану.

Поиск и замена в таблице

Поиск осуществляется на всем активном листе. Используется команда Главная – Найти и выделить – Найти[2].Для поиска в поле Найтивводится слово, фрагмент слова или сочетания знаков. При этом можно использовать знаки ? – любой один знак в данном месте или * – любое количество любых знаков в данном месте. Например, для поиска телефона (831)65-62-51 можно ввести *65-62*. В поле Заменитьвводится конкретный элемент замены.

Упражнение 2. Используйте команду Найти для быстрого поиска соусов в списке товаров (лист Продажи), сколько ячеек найдено?

С помощью команды Заменить замените название организации- заказчика QUICK на ООО Альфа. Сколько замен произведено?

Условное форматирование

Условное форматирование позволяет быстро выделить на листе важные сведения: Главная – Условное форматирование – Правила выделения ячеек[3]. Если встроенных вариантов недостаточно, можно создать собственное правило форматирования. Удалить или скорректировать условные форматы можно через меню Условное форматирование – Управление правилами[4].

Упражнение 3. С помощью условного форматирования выделите на листе Продажи крупные заказы (сумма покупки больше 2 тыс. руб.). Другим цветом выделите мелкие заказы (до 50 руб.).

Фильтрация, сортировка, промежуточные итоги

Упражнение 4.

1. С помощью фильтрации записей в списке продаж (Данные – Фильтр) отобразите заказы ООО Альфа. Проанализируйте активность этого заказчика.

2. Выполните сортировку списка продаж (Данные – Сортировка) по столбцу «Товар». Какие товары пользуются наименьшим спросом (по количеству заказов)?

3. Скопируйте таблицу с данными о продажах на новый лист Excel, переименуйте его в Итоги. Подведите промежуточные итоги (Данные – Промежуточные итоги[5]) с указанием для каждого товара общей суммы заказов за каждый квартал. Сверните структуру второго уровня и представьте окончательный результат в виде:

Какие товары пользуются устойчивым спросом?

4. На основании списка (лист Продажи) создайте сводную таблицу (Вставка – Сводная таблица[6]) для анализа активности заказчиков. Для отражения суммы заказов за каждый квартал для каждого заказчика перетащите поле «Заказчик» в Область строк, а поля «Кв. 1», «Кв. 2», «Кв. 3» и «Кв. 4» – в Область значений[7]. Какова общая сумма заказов в 4-м квартале?

Примените к полученной сводной таблице Фильтр по значению[8] для того, чтобы выявить 5 заказчиков с наибольшими суммами заказов в 4-м квартале. Какой процент от общей суммы заказов приходится на этих заказчиков по результатам 4-го квартала?

Настройте Фильтр, чтобы показать заказчиков с нулевой суммой заказа в 4-м квартале. Сколько таких заказчиков?

Консолидация данных

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

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

Чтобы консолидировать данные, используют команду Данные – Консолидация.

Существует два основных метода консолидации:

· консолидация по расположению – применяется в случае, если во всех исходных диапазонах данные расположены в одинаковом порядке;

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

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

Скопируйте таблицы на разные листы MS Excel, переименуйте листы соответственно в Январь и Февраль. Подсчитайте выплаченную сумму.

Свод лицевых счетов пенсионеров за январь 2015 г.
№ лицевого счета	ФИО	Сумма причитающейся пенсии, руб	Удержания по исполнит. документам, руб	Выплачено пенсионеру, руб
И1212	Иванов А.А.
А1245	Антонов С.С.
П1268	Петров И.И.
Д1378	Дубровицкий И.С.
С1577	Сидорчук А.В.

Свод лицевых счетов пенсионеров за февраль 2015 г.
№ лицевого счета	ФИО	Сумма причитающейся пенсии, руб	Удержания по исполнит. документам, руб	Выплачено пенсионеру, руб
А1245	Антонов С.С.
И1212	Иванов А.А.
К0568	Кузнецова Ю.Н.
П1268	Петров И.И.
С1577	Сидорчук А.В.

Установите курсор в ячейку А1 нового листа, где будет получена консолидированная таблица. Выберите команду Данные – Консолидация.

В открывшемся диалоговом окне задайте параметры консолидации.

· В поле Функция: выберите функцию Сумма, которая показывает тип объединения данных.

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

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

· Если требуется, чтобы консолидация была динамической, отметьте Создавать связи с исходными данными. Нажмите ОК.

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

Защита данных

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

Для повышения безопасности следует защитить паролем файл всей книги. При этом просматривать и изменять данные книги смогут только полномочные пользователи.

Средства защиты Excel разбиты на группы.

Защита рабочих листов – защищает от изменения отдельный рабочий лист или запрещает вносить изменения на рабочий лист определенным пользователям, например, числовые данные можно изменять, а формулы – нет. Для защиты конкретного листа следует выполнить в нем команду Рецензирование – Изменения[9] – Защитить лист.

Иногда нет необходимости устанавливать защиту на все ячейки листа. Для этого до установки защиты необходимо выделить незащищаемые ячейки, выбрать Главная– Ячейки – Формат–Формат ячеек[10]–на вкладке Защита ячеекснять флажок Защищаемая ячейка.

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

Чтобы обеспечить защиту файла, необходимо:

· Выбрать в меню Файл – Сохранить как, задать имя файла.

· Открыть список (внизу слева[11]) Сервис – Общие параметры; выбрать нужный вариант защиты и при необходимости ввести пароль; нажать ОКи подтвердить пароль.

· Нажать ОКи далее в диалоговом окне сохранения файла кнопку Сохранить.

Защита проектов VBA– защищает с помощью пароля код VBA от изменения.

Упражнение 7.

Организуйте защиту данных в лицевых счетов пенсионеров (листы Январь и Февраль) следующим образом:

ü разрешите изменять значения в ячейках столбцов «Удержания по исполнит. документам, руб»;

ü защитите от изменений остальные ячейки;

ü скройте формулы столбца «Выплачено пенсионеру, руб».

[1] В Excel-2003: Окно – Закрепить области (Снять закрепление областей)

[2] В Excel-2003: Правка – Найти

[3] В Excel-2003: Формат – Условное форматирование

[4] В Excel-2003: Формат – Условное форматирование – Удалить...

[5] В Excel-2003: Данные – Итоги