Сортировка и фильтрация данных. Создание связанных таблиц

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

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

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

Будьте внимательны: если выделена не вся область данных, то при сортировке они перемешаются.

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

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

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

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

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

Консолидировать данные (команда Данные → Работа с данными → Консолидация) можно по расположению или по категории. Если вы консолидируете данные по расположению, MS Excel собирает информацию из одинаково расположенных ячеек каждого исходного листа. При консолидации данных по категории Excel использует в качестве основы для объединения листов заголовки столбцов или строк. Консолидация по категории предоставляет большую свободу для организации данных в исходных листах. При консолидации можно установить или убрать связь с исходнами листами. Функции консолидации:

– Количество значений;

– Среднее;

– Максимум;

– Минимум;

– Произведение/Количество чисел;

– Несмещенное отклонение;

– Смещенное отклонение;

– Несмещенная дисперсия.

Промежуточные итоги.Этот инструмент помогает быстро подвести итоги. Подведение итогов начинается с сортировки. Необходимо сгруппировать записи, по полю или полям которых требуется получить итоговые значения. Команда Данные → Структура → Промежуточные итоги откроет диалоговое окно Промежуточные итоги.

Сводные таблицы.Сводная таблица служит для быстрого подведения итогов или объединения больших объемов данных. Создание сводных таблиц осуществляется с помощью Мастера сводных таблиц(Вставка → Таблицы → Сводная таблица).

Этапы работы мастера:

1. На первом этапе выбираем, откуда выбираются данные: из одного диапазона, разных диапазонов или внешних источников.

2. Собственно выбор диапазонов

3. Макетирование сводной таблицы: необходимо мышью перетащить кнопки полей.

Практическая часть

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

Для дальнейшей работы преобразуем на новых листах таблицы из Задания 5.1. и 5.2. к виду:

Задание 5.5. Найти в преобразованной таблице Список(см. выше) тех студентов, у которых фамилия начинается с буквы "Б".

Выполнение.

1. Установить курсор в позиции «Ф.И.О»,

2. Выполнить команду Данные → Фильтр. Раскрыть список команд столбца «Ф.И.О».

3. Выбра позицию Текстовые фильтры, затем – Начинается с..

4. В окне Пользовательский автофильтр в поле Ф.И.О., которое будет участвовать в определении критерия, ввести критерий: Б*.

6. Нажать кнопку ОК, чтобы перейти к записи, удовлетворяющей введенному критерию.

Задание 5.6. Отсортировать таблицу Список по столбцам Группа и Номер зачетки.

Выполнение.

1) Выделить диапазон таблицы, включая шапку (А2:F14).

2) Выполнить команду Данные → Сортировка.

3) В диалоговом окне Сортировка диапазона в поле Сортировать по выбрать из списка имя столбца Группа, а в поле Затем по – Номер зачетки, установив переключатели для обоих столбцов – По убыванию.

4) Нажать кнопку [ОК].

Задание 5.7. Использование числового фильтра. Выбрать из таблицы Успеваемость, тех студентов, средний балл которых >=6.

Выполнение.

1) Выделить диапазон таблицы, включая шапку (A3:F15).

2) Выполнить команду Данные → Фильтр. Ячейки с названиями полей превратятся в раскрывающиеся списки.

3) Раскрыть список Автофильтрав столбце Средний балл студента.

4) Выбрать команду Числовые фильтры → Больше или равно. В новом окне Пользовательский автофильр ввести значение 6.

5) Нажать кнопку [ОК].

6) Для отмены условия выбрать в раскрывающемся списке столбца Средний балл студента команду Снять фильтр.

Задание 5.8. Работа с расширенным фильтром. Выбрать из таблицы Список, записи со студентами группы 113010, у которых Вид оплаты =1. Результат поместить в отдельный выходной блок ниже исходной таблицы.

Выполнение.

1. Скопируем преобразованную таблицу Список (заголовки располагаются в одной строке каждый) на отдельный лист.

2. На этом же листе сформируем диапазон условий (A18:D19), отделив его от исходного диапазона (A2:D14) тремя пустыми строками. Чтобы создать диапазон условий необходимо скопировать заголовки полей исходного диапазона, которые будут ключевыми при отборе записей (для нашего случая - это Группа и Вид оплаты), и заполнить строки критериев:

– в ячейку A18:D18 скопировать заголовки полей;

– в ячейку A19ввести 113010;

– в ячейку D19 ввести 1.

3. Установить курсор в любую ячейку исходного диапазона.

4. Выполнить команду Данные → Сортировка и фильтр → Дополнительно. В диалоговом окне Расширенный фильтр выполнить следующие действия:

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

– В поле Исходный диапазон уже введена ссылку на диапазон, содержащий исходную таблицу (A2:D14).

– В поле Диапазон условий ввести ссылку на диапазон условий (A18:D19). Ввод ссылок во всех полях данного диалогового окна легче и лучше всего осуществлять путем протаскивания указателя мыши по нужному диапазону.

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

– Необходимо установить флажок Только уникальные записи, чтобы oдинаковые записи не повторялись (будет выводиться только первая из всех удовлетворяющих критерию одинаковых записей).

– Нажать кнопку [ОК].

1. Получаем новую таблицу со строками исходного диапазона, отобранными согласно условию:

Задание 5.9. Подведение частных и общих итогов. Рассчитать средние баллы по всем дисциплинам, каждой из учебных групп (на примере таблицы Список).

Выполнение.

1. Выделить диапазон A3:F15– всятаблица, включая заголовки столбцов.

2. Выполнить команду Данные → Структура → Промежуточные итоги. В диалоговом окне Промежуточные итоги:

– в поле При каждом изменении в: из раскрывающегося списка выбрать Группа;

– в поле Операция из раскрывающегося списка выбрать Среднее;

– в поле Добавить итоги по: установить флажки: Математика, Информатика, Философия;

– установить флажок Итоги под данными.

3. Нажать кнопку [ОК].

4. Округлить полученные итоги до двух десятичных знаков с помощью команды Формат → Ячейки.

Результат выполненного задания:

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

5. Покажите результат второго уровня итогов.

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

Все промежуточные результаты скопировать в отчет.

Задание 5.10. Создание связанных таблиц. Сформировать в Excel таблицу для расчета сумм стипендий студентам факультета ПСФ в соответствии с формой, представленной в таблице Ведомость начисления. При этом учесть следующие условия:

– стипендия не начисляется студентам, обучающимся на платной основе;

– студенты получают минимальную стипендию, если средний экзаменационный балл больше 3;

– студенты, имеющие средний балл более 4.5, получают 50% надбавки к стипендии.

Выполнение.

1. В рабочей книге ПСФ.Иванов.xls перейти на новый лист и переименовать его на Начисления.

2. В строку 1 ввести название таблицы Ведомость начисления стипендии студентам ПСФ 1-го курса.

3. В ячейку B2 ввести текст Сумма минимальной стипендии.

4. В ячейку D2 ввести сумму минимальной стипендии – 120000.

5. Оформить шапку таблицы. Для этого в ячейки А3:Е3 ввести заголовки столбцов таблицы.

6. Столбец Номер зачетки заполнить данными. Чтобы не набирать еще раз номера зачеток, в столбце Номер зачетки установить ссылки на ячейки B5:B16, находящиеся на листе Список. Для того, чтобы при изменении номеров зачеток на листе Список, на листе Начисления они изменялись автоматически, выполните действия:

– перейти на лист Начисления, активизировать ячейку А4 и ввести знак "=" для определения формулы;

– перейти на лист Список. Ярлык листа Начисления выделится подсветкой, а в строке формул появится ссылка на лист-источник в следующем виде: Список!;

– выполнить щелчок левой клавишей мыши на ячейке В5 и нажать клавишу Enter;

– на листе Начисления в ячейке А4 будет введена формула вида: =Список!B5;

– скопировать формулу из ячейки А4 в остальные ячейки столбца А5:А15.

Замечание. Если нужная информация находится в другой книге (например, в книге с именем Отчет, на листе Ведомость, в ячейке В5), то необходимо перейти в нее с помощью команды Вид → Окно → Перейти в другое окно, при этом книга Отчетдолжна быть заранее открыта, затем перейти на нужный лист и выделить необходимую ячейку (например, В5) или диапазон ячеек. В строке формул появится ссылка вида: [Отчет.xls]Ведомость!В5.

8. Заполнить столбец Ф.И.О. данными. Для этого в ячейках В4:В15 листа Начисления установить ссылки на ячейки С5:С16 из листа Список.

9. Ввести формулы для вычисления суммы стипендии студентам:

– для расчета стипендии первому студенту активизируйте ячейку С5 на листе Начисления;

– перейти к пункту меню Формулы, выбрать категорию Логические, а в списке – функцию Если;

– щелкнуть на кнопке [ОК];

– во втором диалоговом окне задать аргументы функции. Для нашего примера, устанавливая курсор в каждой строке, ввести следующие операнды логической функции: Логическое выражение: И(Список!D5=1;Успеваемость!F5>=6), Значение если истина: $D$2,Значение если ложь: 0;

– нажать кнопку [ОК].

В результате в ячейке С4 появится значение, рассчитанное по формуле: =ЕСЛИ(И(Список!D5=1;Успеваемость!F5>=3);$D$2;0).

Для задания логического выражения используется логическая функция И, которая возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, и возвращает ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Чтобы ссылка на ячейку D2 оставалась неизменной при копировании формулы, необходимо сделать ее абсолютной путем указания знака $ перед именем столбца и номером строки – $D$2.

Для вычисления величины стипендии всем студентам скопировать формулу из ячейки С4 в диапазон ячеек С5:С15.

10. Ввести формулы для вычисления величины надбавки к стипендии студентам:

– ввести формулу для расчета величины надбавки первому студенту в ячейку D4 на листе Начисления, загрузить Мастер функций и выбрать функцию Если;

– ввести следующие операнды логической функции:

Логическое выражение: И(Список!D5=1;Успеваемость!F5>=4,5),

Значение если истина : $D$2*0,5,

Значение если ложь: 0;

– нажать кнопку [ОК].

В ячейке D5 появится значение, рассчитанное по формуле:

=ЕСЛИ(И(Список!D5=1;Успеваемость!F5>=4,5);$D$2*0,2;0).

– Для расчета величины надбавки всем студентам скопировать формулу из ячейки D4 в диапазон ячеек D6:D15;

– ввести формулы для расчета общей суммы начислений каждому студенту. Для этого в ячейку Е4 ввести формулу: =C4+D4 и скопировать ее в диапазон ячеек Е5:Е15.

В результате получится следующая таблица:

11. Сохранить рабочую книгу.

Создание макросов

Макрос – действие или набор действий, используемые для автоматизации выполнения повторяющихся задач. Макросы записываются на языке программирования Visual Basic для приложений (VBA) в Microsoft Office Excel. После создания макроса можно назначить его объекту (например, кнопке панели управления, графическому объекту или элементу управления), чтобы запускать этот макрос по щелчку объекта. Если макрос больше не нужен, его можно удалить.

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

Если вкладка Разработчик недоступна, выполните:

– щелкните значок Кнопка Microsoft Office, а затем щелкните Параметры Excel;

– в категории Основные в группе Основные параметры работы с Excel установите флажок Показывать вкладку "Разработчик" на ленте, а затем нажмите кнопку ОК.

Для установки уровня безопасности, временно разрешающего выполнение всех макросов, на вкладке Разработчик в группе Код нажмите кнопку Безопасность макросови в группе Параметры макросов выберите переключатель Включить все макросы (не рекомендуется, возможен запуск опасной программы), а затем дважды нажмите кнопку ОК.

Запуск макроса нажатием клавиши Ctrl в сочетании с клавишей быстрого вызова.Клавишу быстрого вызова макроса можно назначить уже созданному макросу в окне Параметры… выполнив Разработчик →Код → Макросыи выбрав нужный макрос по имени.

Для нового макроса – в поле Сочетание клавиш в появившемся окне при переходах Разработчик → Код → Запись макроса. К уже существующему Ctrl, надо ввести нужную прописную или строчную буквы.

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

Запуск макроса щелчком области графического объекта:

– выделите на листе графический объект (рисунок, клип, фигуру или рисунок SmartArt);

– для создания активной области на существующем объекте нажмите кнопку Фигуры в группе Иллюстрации на вкладке Вставка, выберите одну из фигур и нарисуйте ее на существующем объекте;

– щелкните созданную активную область правой кнопкой мыши, а затем выберите пункт Назначить макрос в контекстном меню;

– выполните одно из следующих действий:

1) чтобы назначить графическому объекту существующий макрос, дважды щелкните нужный макрос или введите его имя в поле Имя макроса,

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