Содержание

Урок информатики на тему «Сортировка и фильтрация данных (Microsoft Excel)»

Цель урока: Ознакомление учащихся с сортировкой и фильтрацией данных, обучение основным приёмам работы с сортировкой и фильтрацией данных в MS Excel.

Познакомить учащихся с изменением порядка следования записей в ЭТ, научить учащихся выводить на экран записи, отвечающие заданным требованиям, а также обеспечить практическое применение знаний по теме «Сортировка и фильтрация данных».
Развивать умения и навыки работы с электронными таблицами, внимание, память.
Воспитывать самостоятельность при выполнении практической работы и ответственность при работе с компьютером, соблюдение ТБ.

Оборудование: интерактивная доска, компьютер, ноутбуки, ПО MS Excel, раздаточный материал.

Ход урока:

I. Организационный момент

II. Актуализация знаний учащихся

Сегодня на уроке мы продолжаем работать с электронными таблицами MS Excel.

Сейчас я вам предлагаю ответить на несколько вопросов и выполнить задания на компьютере по пройденной теме на прошлом уроке «Базы данных в MS Excel».

— Создайте базу данных «Водохранилища Казахстана» по предложенному образцу.

— Сохраните базу данных в папке Мои документы под названием «Водохранилища Казахстана».

III. Изучение нового материала

Переходим к изучению новой темы. Откройте тетради и запишите тему урока «Сортировка и фильтрация данных».

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

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

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

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

Тип сортировки:
по возрастанию — цифры сортируются по возрастанию, текст — в алфавитном порядке;
по убыванию — сортировка в обратном порядке;

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

Фильтрация — быстрый способ поиска данных и работы с ними в списке.

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

В Microsoft Excel доступны два способа фильтрации данных: автофильтр и расширенный фильтр.

Выделить одну любую ячейку базы данных;
Дать команду ;
Нажать кнопку со стрелкой в столбце и выбрать нужное условие;
В возникшем списке указать условие и записать значение;
Щелкнуть по кнопке ОК.

Откройте базу данных «Водохранилища Казахстана».
Выполните сортировку по полю Водохранилище (в алфавитном порядке). Результат сортировки скопируйте на Лист 2.
Выполните сортировку по полю Площадь по убыванию.
Переименуйте Лист 2, назовите его — Сортировка. Для этого:

указатель мыши установите на ярлычке Лист 2;
правой кнопкой мыши вызовите контекстное меню;
выполните команду Переименовать.

IV. Физкультминутка

V. Проверочная самостоятельная работа на усвоение нового материала

1. Откройте базу данных «Крупнейшие озера Казахстана».

2. Отсортируйте данные таблицы «Крупнейшие озера Казахстана» по названию озер (по убыванию). Назовите первые три озера.
1) Шалкар
2) Шагалалытениз
3) Уялы

3. Отсортируйте данные таблицы «Крупнейшие озера Казахстана» по увеличению высоты над уровнем моря. Назовите первые три озера.
1) Шалкар
2) Индер
3) Теке

4. В таблице «Крупнейшие озера Казахстана» выберите озера, у которых максимальная глубина более 10 м.
1) Аральское море
2) Балкаш
3) Алаколь
4) Маркаколь
5) Шалкар

5. В таблице «Крупнейшие озера Казахстана» выберите озера, у которых высота над уровнем моря менее 50 м.
1) Аральское море
2) Теке
3) Шалкар
4) Кызылкак
5) Индер

2. Таблицу «Реки Казахстана» упорядочите по возрастанию площади бассейна, если у некоторых рек совпадает площадь бассейна, упорядочите их по алфавиту. Назовите первые шесть рек.
1) Аксу
2) Шарын
3) Лепси
4) Оба
5) Калмаккырган
6) Тундык

3. Таблицу «Реки Казахстана» упорядочите по убыванию длины рек в Казахстане, если у некоторых рек совпадает длина в Казахстане, упорядочите их по алфавиту. Назовите первые три реки.
1) Ертис
2) Есиль
3) Сырдария

4. В таблице «Реки Казахстана» определите, сколько рек с площадью бассейна от 50 до 100 тыс.кв.км.
1) Жайык
2) Нура
3) Иле
4) Шу
5) Сарысу
6) Талас
Количество записей — 6

5. В таблице «Реки Казахстана» определите, сколько рек с общей длиной от 1000 до 2000.
1) Тобыл
2) Иле
3) Шу
Количество записей — 3

[expert_bq id=»1570″]Один из простых способов воспользоваться инструментом для преобразования списка в вариант в алфавитном порядке это проведение действий на панели управления. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Итак, прежде всего, были отсортированы строчки в зависимости от имен. После этого, следует обратить внимание на имя Дарья. Их 4, однако располагаются они в той последовательности, как и были в таблице изначально.

Динамическая сортировка таблицы в EXCEL. Примеры и описание

Познакомить учащихся с изменением порядка следования записей в ЭТ, научить учащихся выводить на экран записи, отвечающие заданным требованиям, а также обеспечить практическое применение знаний по теме «Сортировка и фильтрация данных».
Развивать умения и навыки работы с электронными таблицами, внимание, память.
Воспитывать самостоятельность при выполнении практической работы и ответственность при работе с компьютером, соблюдение ТБ.

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

Сортировка в Excel. Работа в Excel. Excel в примерах

Создание собственного порядка сортировки

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

Чтобы создать собственный порядок, нужно перейти во вкладку «Сервис», а затем выбрать пункт «Параметры». Там есть меню «Строка», где в левой части окошка расположены уже существующие списки, используемые для сортировки. Для создания собственного порядка необходимо выбрать опцию «Новый список». Все необходимые значения и параметры нужно задать в поле «Элементы списка».

Теперь можно легко упорядочить информацию, чтобы работа в Excel была комфортной. Для этого нужно перейти в меню «Данные», затем выбрать вкладку «Сортировка». В поле «Сортировать по…» необходимо задать конкретные параметры и нажать соответствующую кнопочку. На экране появится окошко «Параметры сортировки», где нужно указать порядок сортировки данных.

После этого следует выбрать собственный порядок сортировки и подтвердить выбор, нажав ОК.

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

Сортировка по фамилии и окладу

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

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

Нужно активизировать команду «Данные», а затем «Сортировка».
В открывшемся окошке «Сортировка диапазона» необходимо выбрать «Сортировать по…» и указать конкретный столбец. Затем следует переместить указатель в нужное положение: по возрастанию или по убыванию.

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

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

Сортировка по алфавиту

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

Многоуровневая сортировка

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

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

Формулы в программе Microsoft Excel

Формулы в Excel представляют собой выражения, которые начинаются со знака «равно», состоят из числовых значений, адресов ячеек, имен или функций. Все они соединяются знаками арифметических операций: умножения, деления, вычитания или возведения в степень.

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

Как внести изменение в формулу?

Если возникает потребность изменить формулу, нужно щелкнуть мышкой в строке формул или нажать кнопочку F2. После всех корректировок необходимо нажать «Enter» или значок ввода в строке. Изменения также можно вносить непосредственно в ячейке. Для этого на выбранной области нужно щелкнуть мышкой дважды.

Ссылки на ячейки и перемещение формул

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

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

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

[expert_bq id=»1570″]Задача заключается в последовательной их прописание, чтобы в итоге вытаскивались либо первые два символа дата , либо четыре год. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] По значку отсортировать нельзя. Но сортировка по полям не отменит сделанное условным форматированием выделение. Или этого недостаточно? Всё же условное форматирование в глаза сильно бросается. Ничего не теряется.

Какие Уровни Сортировки Предусмотрены в Программе Excel • Сортировка по форматированию

Практическое задание по Ms Excel: поиск и замена данных, сортировка | Информационные технологии

Нужно активизировать команду «Данные», а затем «Сортировка».
В открывшемся окошке «Сортировка диапазона» необходимо выбрать «Сортировать по…» и указать конкретный столбец. Затем следует переместить указатель в нужное положение: по возрастанию или по убыванию.

Практическое задание №4. Работа с данными в Microsoft Excel: поиск и замена данных, сортировка

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

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

Создайте на Рабочем столе компьютера файл отчёта по практической работе: ОтчётСортировкаФамилия_студента.docx
Откройте файл Data.xlsx

8. Для расширения возможностей поиска во вкладке Найти диалогового окна Найти и заменить нажмите кнопку Параметры . При этом появятся новые элементы диалогового окна

9. В списке Искать выберите область поиска: на листе или в книге . Во втором случае будут просматриваться данные всех листов книги (за исключением скрытых).

10. В списке Область поиска выберите способ поиска: по формулам (формулы) или по значениям ячеек (значения). В нашем случае выбираем значения.

Например, ячейка может показывать значение 100, но содержать формулу =А8*25. При поиске числа 100 по формулам эта ячейка найдена не будет. При поиске числа 100 по значениям эта ячейка будет найдена. Поиск также можно выполнять по примечаниям, добавленным к ячейкам.

11. Установите флажок Учитывать регистр , если при поиске необходимо различать прописные и строчные буквы.

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

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

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

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

В группе Редактирование вкладки Главная щелкните по кнопке Найти и выделить и выберите команду Заменить .

В поле Найти вкладки Заменить окна Найти и заменить введите искомые данные, а в поле Заменить на , заменяющие данные (например: Свежие фрукты ).

3. Так же как и при поиске данных, для расширения возможностей замены во вкладке Заменить диалогового окна Найти и заменить нажмите кнопку Параметры и установите особенности поиска и замены.

4. Для замены данных на всем листе (книге) нажмите кнопку Заменить все, после чего выйдет сообщение о количестве произведенных замен. Если же требуется заменить только некоторые из искомых данных, то следует последовательно нажимать кнопку Найти далее и после нахождения требуемого значения нажать кнопку Заменить .

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

Сортировка данных. Правила сортировки

Сортировка – расположение данных на листе в определенном порядке. Чаще всего необходимо сортировать строки с данными.

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

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

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

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

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

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

Порядок сортировки — по возрастанию

При сортировке по возрастанию используется следующий порядок.

Порядок сортировки — по убыванию

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

Сортировка по значениям одного столбца

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

Выделите одну любую ячейку в столбце, по данным которого сортируется таблица

Нажмите кнопку Сортировка и фильтр группы Редактирование вкладки Главная и выберите направление сортировки. Например, для сортировки по столбцу « Регион продаж » следует выделить любую ячейку столбца D

Для сортировки можно также использовать кнопки группы Сортировка и фильтр вкладки Данные .

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

Сортировка по форматам — сортировка по цвету ячеек, цвету шрифта и по значкам.

В группе Редактирование вкладки Главная щелкните по кнопке Найти и выделить и выберите команду Заменить .

В поле Найти вкладки Заменить окна Найти и заменить введите искомые данные — Макароны , а в поле Заменить на , заменяющие данные – Макароны.

Для расширения возможностей замены во вкладке Заменить диалогового окна Найти и заменить нажмите кнопку Параметры и установите особенности поиска и замены (в нашем случае заливаем ячейку жёлтым цветом). Для этого: нажимаем кнопку Формат , выбираем из списка Формат , жмём на вкладку Заливка и выбираем жёлтый цвет.

Последовательно находим и меняем цвета ячеек оставшихся групп.

Теперь можем приступить к сортировке по формату (в нашем случае по цвету ячейки) для этого:

Выделите одну любую ячейку в диапазоне сортируемых данных.

Нажмите кнопку Сортировка и фильтр группы Редактирование вкладки Главная и выберите команду Настраиваемая сортировка или нажмите кнопку Сортировка группы Сортировка и фильтр вкладки Данные .

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

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

5. После выбора всех необходимых цветов нажмите кнопку ОК.

Пользуясь кнопками Вверх и Вниз окна Сортировка можно изменять последовательность уровней сортировки.

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

3. Последовательно удаляем все уровни группировки до получения следующего результата:

Выделите одну любую ячейку в диапазоне сортируемых данных.

Нажмите кнопку Сортировка и фильтр группы Редактирование вкладки Главная и выберите команду Настраиваемая сортировка или нажмите кнопку Сортировка группы Сортировка и фильтр вкладки Данные .

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

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

После выбора всех необходимых уровней сортировки нажмите кнопку ОК.

Результат сортировки будет выглядеть следующим образом:

6. Сохраните файл отчёта ОтчётСортировкаФамилия_студента.docx в папке Яндекс.Диск→Excel

7. Закройте табличный процессор Ms Excel.

Поддержите проект! Выберите один из вариантов платежа:
[expert_bq id=»1570″]Для этого нужно выделить все значения исходной таблицы не включая заголовок, вызвать инструмент Сортировка, выбрать столбец, по которому требуется сортировать и вариант сортировки. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Также можно воспользоваться инструментом Сортировка ( Данные/ Сортировка и Фильтр/ Сортировка ). Для этого нужно выделить все значения исходной таблицы не включая заголовок, вызвать инструмент Сортировка, выбрать столбец, по которому требуется сортировать и вариант сортировки.
Хитрости сортировки

В группе Редактирование вкладки Главная щелкните по кнопке Найти и выделить и выберите команду Заменить .

В поле Найти вкладки Заменить окна Найти и заменить введите искомые данные — Макароны , а в поле Заменить на , заменяющие данные – Макароны.

Для расширения возможностей замены во вкладке Заменить диалогового окна Найти и заменить нажмите кнопку Параметры и установите особенности поиска и замены (в нашем случае заливаем ячейку жёлтым цветом). Для этого: нажимаем кнопку Формат , выбираем из списка Формат , жмём на вкладку Заливка и выбираем жёлтый цвет.

Последовательно находим и меняем цвета ячеек оставшихся групп.

Далее откроется окошко, в котором следует по очереди выбирать столбцы, которые будут содержать усчитанные данные, а также, указать методы: в зависимости от цвета, значения, возрастания либо убывания и т.п.

Дополнительные данные → Похожие темы → Все про Exel → Как вставить значения → Как объединить ячейки → Как вставить форматы → Дополнительные данные → Вставить формулы→ Аргументы функции