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

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

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

Кроме значений полей каждый список содержит еще три элемента:
Все — предназначен для восстановления отображения на экране всех записей после применения фильтра
Первые 10 — обеспечивает представление на экране первых 10 записей фильтра
Условие — используется для формирования более сложного критерия отбора, в котором можно применять условные операторы отбора И и ИЛИ.
Предположим нас интересуют только в пределах стоимости от 6000 до 10000. Воспользуемся строкой условие. В результате откроется диалоговое окно Пользовательский Автофильтр

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

19.Промежуточные итоги.Консолидация данных.

Microsoft Excel может автоматически вычислять промежуточные и общие итоги в списке. При вставке автоматических промежуточных итогов Microsoft Excel
изменяет разметку списка, что позволяет отображать и скрывать строки каждого промежуточного итога.
Перед тем как вставить промежуточные итоги, необходимо отсортировать список, чтобы сгруппировать строки, по которым нужно подвести итоги. После этого можно подсчитать промежуточные итоги любого столбца, содержащего числа.
Если данные не организованы в виде списка или если требуется подвести один промежуточный итог, вместо автоматических промежуточных итогов можно использовать Автосумму 
Вычисление промежуточных итогов
Промежуточные итоги.
Для вычисления значений промежуточных итогов используется итоговая функция, например СУММ или СРЗНАЧ.
Промежуточные итоги могут быть отображены в списке с помощью нескольких типов вычислений одновременно.
Общие итоги.
Общие итоги подводятся с помощью подробных данных, а не с помощью значений промежуточных итогов.
Например, итоговая функция СРЗНАЧ возвращает среднее значение для всех строк списка, а не для промежуточных итоговых значений.
Автоматические вычисления.
Значения общих и промежуточных итогов пересчитываются автоматически при каждом изменении подробных данных.
Вложение промежуточных итогов
В существующие группы суммируемых значений можно вставлять промежуточные итоги для более мелких групп. В приведенном примере промежуточные итоги для каждого вида спорта присутствуют в списке, в котором уже вычисляются промежуточные итоги по каждому региону.

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

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

Консолидация данных.
При консолидации данных объединяются значения из нескольких диапазонов данных. Например, если имеется лист расходов для каждого регионального представительства, консолидацию можно использовать для преобразования этих данных в корпоративный лист по расходам.
Консолидировать данные в Microsoft Excel можно несколькими способами. Наиболее удобный метод заключается в создании формул, содержащих ссылки на ячейки в каждом диапазоне объединенных данных. Формулы, содержащие ссылки на несколько листов, называются трехмерными формулами.
Использование трехмерных формул
При использовании в формулах трехмерных ссылок не существует
ограничений на расположение отдельных диапазонов данных. Консолидацию можно изменять любыми способами. Консолидация автоматически обновляется при изменении данных в исходном диапазоне.
Используйте формулы для объединения данных. В приведенном ниже примере формула в ячейке A2 складывает три числа, расположенные в различных местах трех разных листов.

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

Консолидация по расположению
Консолидацию по расположению следует использовать в случае, если данные всех исходных областей находятся в одном месте и размещены в одинаковом порядке; например, если имеются данные из нескольких листов, созданных на основе одного шаблона.
Если установлено автоматическое обновление консолидации при изменении исходных данных, изменить набор ячеек и диапазонов, входящих в консолидацию, невозможно. Данная функция доступна только при обновлении консолидации вручную.
Консолидация по категории
Консолидацию по категории следует использовать в случае, если требуется обобщить набор листов, имеющих одинаковые заголовки рядов и столбцов, но различную организацию данных. Этот способ позволяет консолидировать данные с одинаковыми заголовками со всех листов.
Если установлено автоматическое обновление консолидации при изменении исходных данных, изменить набор ячеек и диапазонов, входящих в консолидацию, невозможно. Данная функция доступна только при обновлении консолидации вручную.
Другие способы объединения данных
Можно создать отчет сводной таблицы из нескольких диапазонов
консолидации. Данный метод сходен с консолидацией по категории, однако обладает большей гибкостью в отношении реорганизации категорий.
Сортировка и фильтрация данных
Информация в таблицах может быть отсортирована в порядке убывания или возрастания по одному или нескольким полям. Например, можно отсортировать таблицу клиентов по алфавиту, воспользовавшись фамилией в качестве первого поля сортировки и именем — в качестве второго. Получится список, подобный тому, который встречается в телефонных справочниках. В некоторых случаях сортировка применяется для того, чтобы сгруппировать табличные данные по определенному принципу. Например, если, прежде чем печатать почтовые наклейки, отсортировать адресную информацию по почтовому индексу, наклейки будут сгруппированы в соответствии с требованиями почтовой службы.
Хотя суть сортировки кажется вполне очевидной, для компьютера все не так просто. В Access цифровые данные могут трактоваться и как числа, и как текст. Поля, содержащие, помимо цифр, пробелы, тире или знаки препинания (как, например, в почтовом индексе или телефонных номерах), трактуются как текстовые. В отличие от них поля, содержащие такие данные, как цена или количество, считаются числовыми. При сортировке текстовых полей, все записи упорядочиваются вначале по первому символу, затем по второму и так далее — вплоть до последнего. Сортировка числовых данных осуществляется по их значениям в порядке возрастания или убывания. Такой подход может привести к неожиданным результатам. В следующей таблице представлены результаты сортировки первого столбца в зависимости от того, является его содержимое текстовым или числовым.
Чтобы отсортировать текстовое поле, содержащее цифры, в порядке возрастания или убывания значений, нужно дополнить их ведущими нулями до одинаковой длины. Например, данные 001, 011, 101 будут отсортированы правильно, даже если они определены как текстовые.
При сортировке записей по нескольким полям сортировка осуществляется последовательно, слева направо. Поэтому поля сортировки должны быть смежными и располагаться в нужной последовательности.
В отличие от сортировки, которая упорядочивает информацию, фильтрация позволяет извлечь из таблицы записи, отвечающие определенным условиям. Например, можно создать фильтр, который находит в таблице Клиенты всех клиентов, проживающих в Сиэтле, или клиентов, разместивших заказы после 18 января.
Работая с таблицами и формами, можно задавать простые фильтры. Обычно эти фильтры относятся к выделенному полю, но, если к результатам отбора применить дополнительные фильтры, область просмотра сузится еще больше.
Фильтрация записей осуществляется с помощью команды Фильтр (Filter) в меню Записи (Records), кнопок на панели инструментов и команд в контекстном меню. Доступ к этим командам зависит от конкретной ситуации.
При фильтрации таблицы, записи, не отвечающие условиям отбора, не удаляются, а просто перестают отображаться.
Если точное значение критерия отбора неизвестно, можно воспользоваться заменителями символов (wildcard characters). Наиболее распространенные из них: символ «*» заменяет любое число символов, символ «?» заменяет один символ, символ «#» заменяет одну цифру.
С помощью функции Изменить фильтр (Filter By Form) можно легко и быстро создать сложный фильтр, основанный на содержимом полей таблицы. Если открыть таблицу, а затем щелкнуть на кнопке Изменить фильтр (Filter By Form) вместо записей таблицы отобразится пустая строка, ячейки которой представляют собой поля со списками, содержащими все значения, встречающиеся в данном столбце таблицы.
Выбрав элемент из списка, можно щелкнуть на кнопке Применить фильтр (Apply Filter), чтобы отобразить только те записи, которые содержат выбранное значение.
Если таблица содержит десятки полей, использование функции Изменить фильтр (Filter By Form) усложняется, поскольку приходится прокручивать таблицу по горизонтали. В этом случае лучше воспользоваться кнопкой Изменить фильтр (Filter By Form) в режиме формы. Вместо исходной формы отобразится пустая форма, предназначенная для создания фильтра. Если щелкнуть в любом поле, а затем щелкнуть на стрелке, появится список всех значений поля. Выбрав значение и щелкнув на кнопке Применить фильтр (Apply Filter), щелкните на кнопке Следующая запись (Next), чтобы отобразить первую из отобранных записей. Воспользуйтесь кнопками перехода, чтобы просмотреть остальные записи.
Функция Изменить фильтр (Filter By Form) применима как в режиме таблицы, так и формы. В тех случаях, когда проще задать фильтр в форме, а просматривать результаты удобнее в таблице, можно быстро создать форму, воспользовавшись функцией Автоформа (Autoform). Затем, определив фильтр в форме, можно переключиться в режим таблицы для просмотра результатов.
Понравилась статья? Добавь ее в закладку (CTRL+D) и не забудь поделиться с друзьями:
Сортировка и фильтрация данных

Ознакомление учащихся с сортировкой и фильтрацией данных, обучение основным приёмам работы с сортировкой и фильтрацией данных в MS Excel.
Задачи:
Познакомить учащихся с изменением порядка следования записей в ЭТ, научить учащихся выводить на экран записи, отвечающие заданным требованиям, а также обеспечить практическое применение знаний по теме «Сортировка и фильтрация данных».
Развивать умения и навыки работы с электронными таблицами, внимание, память.
Воспитывать самостоятельность при выполнении практической работы и ответственность при работе с компьютером, соблюдение ТБ.
Тип урока: объяснительно-демонстрационный с элементами практикума
Оборудование: интерактивная доска, компьютер, ноутбуки, ПО MS Excel, презентация, раздаточный материал.
Ход урока:
Организационный момент
— Здравствуйте, ребята! Я рада вас видеть, и надеюсь на плодотворное сотрудничество. Наш класс – единая команда, единая цепь, скрепленная знаниями. Каждый ученик – звено одной цепи. Прочность цепи – это прочность ваших знаний. Насколько прочно скреплены звенья нашей цепи, мы выясним сегодня на уроке. Ребята, прошу вас быть сегодня на уроке наиболее активными, внимательными, я знаю, что вы все сегодня покажите достойный похвалы уровень знаний.
Актуализация знаний учащихся
Сегодня на уроке мы продолжаемм работать с электронными таблицами MS Excel.
Сейчас я вам предлагаю ответить на несколько вопросов и выполнить задания на компьютере по пройденной теме на прошлом уроке «Базы данных в MS Excel».
Мини блиц-опрос
Что называется базой данных?
База данных (БД) – информационная модель, которая позволяет в упорядоченном виде хранить данные о группе объектов, обладающих одинаковым набором свойств.
Строка таблицы – запись базы данных.
Что такое запись?
Столбец таблицы – поле базы данных.
Практическая работа
— Создайте базу данных «Водохранилища Казахстана» по предложенному образцу Приложение 1.
— Сохраните базу данных в папке Мои документы под названием «Водохранилища Казахстана».
Учитель контролирует выполнение заданий.
Изучение нового материала
Переходим к изучению новой темы. Откройте тетради и запишите тему урока «Сортировка и фильтрация данных».
1. Сортировка данных
Сортировка данных является базовой операцией любой таблицы. В Excel предусмотрены средства упорядочивания данных после любых изменений и дополнений, которые можно использовать при необходимости.
В Excel возможна сортировка по одному или нескольким ключевым полям. Чтобы заставить Excel правильно сортировать записи в базе данных, вы должны указать, какие поля будут определять новый порядок записей.
Сортировка – это упорядочение данных по возрастанию или убыванию.
Для сортировки данных необходимо:
Выделить одну любую ячейку базы данных;
Дать команду Данные-Сортировка;
В возникшем окне диалога выбрать нужный для сортировки столбец;
Выбрать вариант сортировки (по возрастанию или по убыванию)
по возрастанию – цифры сортируются по возрастанию, текст – в алфавитном порядке;
по убыванию – сортировка в обратном порядке;
Щелкнуть по кнопке ОК.
2. Фильтрация данных
Основная работа с любой базой данных заключается в поиске информации по определенным критериям. С увеличением количества записей поиск информации затрудняется. Excel позволяет упростить этот процесс путем фильтрации данных.
Фильтрация – быстрый способ поиска данных и работы с ними в списке.
Фильтруя список, можно отобразить только те строки, которые удовлетворяют определенным условиям отбора.
В Microsoft Excel доступны два способа фильтрации данных: автофильтр и расширенный фильтр.
Для фильтрации данных необходимо:
Выделить одну любую ячейку базы данных;
Дать команду Данные-Фильтр;
Нажать кнопку со стрелкой в столбце и выбрать нужное условие;
В возникшем списке указать условие и записать значение;
Щелкнуть по кнопке ОК.
3. Обучающая практическая работа
1. Откройте базу данных «Водохранилища Казахстана».
2. Выполните сортировку по полю Водохранилище (в алфавитном порядке). Результат сортировки скопируйте на Лист 2.
3. Выполните сортировку по полю Площадь по убыванию.
4. Переименуйте Лист 2, назовите его – Сортировка. Для этого:
указатель мыши установите на ярлычке Лист 2;
правой кнопкой мыши вызовите контекстное меню;
выполните команду Переименовать.
5. Выполните команду Данные-Фильтр (на полях появились кнопки).
6. Нажмите на кнопку в поле Год ввода в эксплуатацию. Выберите пункт Числовые фильтры.
7. В диалоговом окне задайте критерий: Год ввода в эксплуатацию>1967.
8. Остались водохранилища Капшагайское, Сергеевское, Вячеславское.
9. Нажмите на кнопку в поле Год ввода в эксплуатацию. Выберите пункт Выделить все и нажмите Ok.
Физкультминутка
Проверочная самостоятельная работа на усвоение нового материала
Откройте базу данных «Крупнейшие озера Казахстана»
Отсортируйте данные таблицы «Крупнейшие озера Казахстана» по названию озер (по убыванию). Назовите первые три озера.
Весь материал — в документе.
-82%
Сортировка и фильтрация данных. Сортировка – это упорядочение записей таблицы (списка) по определенному столбцу в порядке возрастания или убывания
Сортировка – это упорядочение записей таблицы (списка) по определенному столбцу в порядке возрастания или убывания. Для выполнения сортировки необходимо:
1. Выделить одну ячейку таблицы.
2. Выбрать команду Сортировка и Фильтр в группе Редактирование на вкладке Главная
3. Из раскрывающегося списка выберите способ сортировки – от минимального к максимальному или от максимального к минимальному. Нажатие на соответствующую кнопку приведет к выполнению команды.
Если необходимо провести сортировку по определенным параметрам, тогда:
1. Вызовите команду Настраиваемая сортировка в группе Редактирование на вкладке Главная.
2. В появившемся диалоговом окне Сортировка (рис. 19) выберите столбец, в котором нужно отсортировать данные, и порядок сортировки. Если вы хотите выполнить сортировку сразу по нескольким столбцам, заполните поля Затем по, В последнюю очередь по. Чтобы дополнить список сортируемых столбцов, нажмите кнопку Добавить уровень.
3.
Нажмите ОК.
Рисунок 19 – Диалоговое окно Сортировка
Фильтрация данных – это быстрый и простой способ найти нужные для работы данные в диапазоне ячеек или таблице. Например, с помощью фильтра можно просмотреть только указанные значения, наибольшие и наименьшие либо повторяющиеся значения. Отфильтровав данные в диапазоне ячеек или таблице, можно применить фильтр повторно, чтобы обновить результаты, либо очистить фильтр, чтобы отобразить все данные.
При помощи автофильтра можно создать фильтры трех типов: по списку значений, по формату или по условиям. Все они являются взаимоисключающими в пределах диапазона ячеек или столбца таблицы. Например, можно выполнить фильтрацию по цвету ячеек или по списку чисел, однако использовать фильтры обоих типов одновременно нельзя; точно так же необходимо выбрать один вариант, если требуется выполнить отбор по значкам или на основе настраиваемого фильтра.
Для выполнения фильтрации необходимо:
1. Выделите требуемый диапазон ячеек или просто установить курсор в произвольную ячейку диапазона, в котором будет производиться фильтрация.
2. В раскрывающемся списке Сортировка и Фильтр в группе Редактирование на вкладке Главная выберите команду Фильтр. После ее выполнения в каждой ячейке строки заголовка появятся кнопки
, обозначающие раскрывающийся список. В этом списке находятся следующие команды:
– Сортировка от минимального к максимальному и Сортировка от максимального к минимальному, Сортировка по цвету (Пользовательская сортировка);
– Удалить фильтр с;
– Фильтр по цвету;
– Установка фильтрации: Числовые фильтры (если в столбце числа), Текстовые фильтры (если в столбце текст), Фильтры по дате (если в столбце даты);
– Флажок Выделить все. Если снять этот флажок, то все столбцы перестанут отображаться;
– Флажки с названиями столбцов. Можно снимать флажки с разных столбцов, тогда они перестают отображаться.
3. После установки любого из фильтров в столбце с фильтром изменяется вид кнопки Раскрывающийся список – в нем появляется изображение фильтра.
4. Нажмите кнопку ОК.
Рассмотрим Числовые фильтры.
С помощью таких фильтров можно отобразить значения ячеек, которые принимают значения Равно, Не равно, Больше, Больше или равно, Меньше, Меньше или равно, Между какого-то указанного значения. Если выберите пункт Первые 10, то появляется окно, в котором можно выбрать какие первые 10 значений будут отображаться: 10 наибольших или 10 наименьших элементов списка или % от количества элементов. Причем количество отображаемых элементов или % можно изменять. Если выбираем пункт Выше среднего или Ниже среднего, то будут отображаться только те строки фильтруемого столбца, значения в которых выше либо ниже среднего арифметического, которое вычисляется автоматически.
Настраиваемый фильтр (Пользовательский автофильтр) позволяет указать условие фильтрации, состоящее из двух выражений, составляемых при помощи логических функций И или ИЛИ. (рис. 20)

Рисунок 20 – Диалоговое окно Пользовательский автофильтр
Данное окно можно вызвать, выполнив команду Настраиваемый фильтр в раскрывающемся списке типа фильтра (рис. 21):

Рисунок 21 – Раскрывающийся список, отображающий дополнительные параметры числового фильтра
С помощью текстовых фильтров можно выбрать такие параметры: Равно, Не равно, Начинается с, Заканчивается на, Содержит, Не содержит. Они настраиваются аналогично Числовым фильтрам.
Можно применять одновременно фильтрацию по нескольким столбцам. Допустим, выбираем: Наименования – начинается на букву А, затем устанавливаем фильтр по Складу 1 – больше 25.
Отменить фильтрацию можно одним из способов:
– с помощью горячих клавиш Shift+Ctrl+L;
– нажав кнопку Фильтр | Сортировка и фильтр на вкладке Главная в группе Редактирование.
Важно! Сортировать данные в таблицах Excel и проводить фильтрацию можно не только на вкладке Главная, но и с помощью команд Сортировка, Фильтр, Дополнительно в группе Сортировка и Фильтр на вкладке Данные.
4. Вопросы, вынесенные на самостоятельное изучение:
1. Защита книг Excel:
а) использование пароля для открытия книги;
б) запрет манипулирования с листами;
в) запрет изменения положения и размера окна рабочей книги.
2. Работа с изображениями, фигурами, объектами WordArt Объекты, SmartArt, надписями в Excel.