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

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

Задание: Используя табличный процессор  Ms Excel  выполните ниже приведённую последовательность действий (сценарий) и выполните Контрольное задание по практической работе №4 по Ms Excel. В процессе выполнения задания каждое действие фиксируйте скриншотами. Скриншоты и комментарии к ним размещайте в файле отчёта по практической работе. Количество скриншотов в отчёте должно быть не меньше, чем в предложенном задании (16).

Поиск данных

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

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

     Data.xlsx (40,9 KiB, 4 408 hits)

3. Сохраните файл Data.xlsx на Рабочем столе компьютера под именем СортировкаФамилия_студента.xlsx
4. Откройте файл СортировкаФамилия_студента.xlsx
5. В группе Редактирование вкладки Главная щелкните по кнопке Найти и выделить и выберите команду Найти

6. Сделайте первый скриншот и вставьте его в файл ОтчётСортировкаФамилия_студента.docx

7. В поле Найти вкладки Найти окна Найти и заменить введите искомые данные (например: Фрукты). При необходимости, в поле Найти можно использовать подстановочные знаки: * (звездочка) заменяет любое количество любых символов; ? (знак вопроса) заменяет один любой символ.

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

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

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

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

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

12. Установите флажок Ячейка целиком для обеспечения поиска точного совпадения с набором символов, заданным в поле Найти, а не всех слов, в которые искомое слово входит как составная часть. Например, при поиске слова Фрукты, как имени собственного, установка указанных флажков позволит избежать нахождения таких слов как: фрукт, фруктовый, фрутис, фруктоза и др.

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

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

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

Замена данных

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

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

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

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

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

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

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

Общие правила

1. Как правило, при сортировке упорядочиваются целиком строки, но можно сортировать и отдельные ячейки.
2. Сортировку можно производить как по возрастанию, так и по убыванию. При желании можно сортировать данные в соответствии с собственным порядком сортировки.
3. Поскольку при сортировке Microsoft Excel автоматически определяет связанный диапазон данных, сортируемый диапазон не должен иметь пустых столбцов. Наличие пустых строк допускается, но не рекомендуется.
4. При сортировке заголовки столбцов обычно не сортируются вместе с данными, но сортируемый диапазон может и не иметь заголовков столбцов.
5. Скрытые строки не перемещаются при сортировке строк. Тем не менее, при сортировке строк, данные скрытых столбцов также упорядочиваются. Прежде чем приступать к сортировке, рекомендуется сделать видимыми скрытые строки и столбцы.
6. Можно выполнять сортировку данных по тексту (от А до Я или от Я до А), числам (от наименьших к наибольшим или от наибольших к наименьшим), а также датам и времени (от старых к новым или от новых к старым). Можно также выполнять сортировку по настраиваемым спискам или по формату, включая цвет ячеек, цвет шрифта, а также по значкам.

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

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

1. Числа. Числа сортируются от наименьшего отрицательного до наибольшего положительного числа.
2. Буквенно-цифровая сортировка. При сортировке алфавитно-цифрового текста сравниваются значения по знакам слева направо. Например, если ячейка содержит текст «Дом100«, она будет поставлена после ячейки, содержащей запись «Дом1«, и перед ячейкой, содержащей запись «Дом12«.

• Текст, в том числе содержащий числа, сортируется в следующем порядке: 0 1 2 3 4 5 6 7 8 9 (пробел) ! » # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z А Б В Г Д Е Ё Ж З И Й К Л М Н О П Р С Т У Ф Х Ц Ч Ш Щ Ъ Ы Ь Э Ю Я
• Апострофы (‘) и дефисы (-) игнорируются с единственным исключением: если две строки текста одинаковы, не считая дефиса, текст с дефисом ставится в конец.
• Если предназначенный для сортировки столбец содержит как числа, так и числа с текстом (например: 1, 1а, 2, 2а), все они должны быть отформатированы как текст. В противном случае после сортировки первыми будут располагаться числа, а за ними числа с текстом.

3. Логические значения. Логическое значение ЛОЖЬ ставится перед значением ИСТИНА.
4. Значения ошибки. Все значения ошибки равны.
5. Пустые значения. Пустые значения всегда ставятся в конец.

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

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

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

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

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

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

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

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

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

Прежде чем приступить к сортировке по формату подготовим нашу таблицу: в столбце Группа найдём все ячейки, содержащие группу Макароны и зальём эти ячейки зелёным цветом, ячейки с группой Свежие фрукты (Фрукты) зальём красным цветом, ячейки с группой Овощи зальём жёлтым цветом, ячейки с группой Выпечка зальём коричневым цветом (изменим формат- оформление ячеек). Для этого:

1. В группе Редактирование вкладки Главная щелкните по кнопке Найти и выделить и выберите команду Заменить.
2. В поле Найти вкладки Заменить окна Найти и заменить введите искомые данные — Макароны, а в поле Заменить на, заменяющие данные – Макароны.
3. Для расширения возможностей замены во вкладке Заменить диалогового окна Найти и заменить нажмите кнопку Параметры и установите особенности поиска и замены (в нашем случае заливаем ячейку жёлтым цветом). Для этого: нажимаем кнопку Формат, выбираем из списка Формат, жмём на вкладку Заливка и выбираем жёлтый цвет.
4. Последовательно находим и меняем цвета ячеек оставшихся групп.

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

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

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

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

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

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

1. Выделите строку в окне Сортировка.
2. Нажмите кнопку Удалить уровень.

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

4. Нажимаем кнопку ОК.

Сортировка по нескольким столбцам

Сортировку можно осуществлять по нескольким столбцам для группировки данных с одинаковыми значениями в одном столбце и последующего осуществления сортировки другого столбца или строки в этих группах с одинаковыми значениями. Например, если взять столбцы «Товар» и «Группа«, сначала можно отсортировать строки по столбцу «Группа» (для группировки однотипных групп), а затем по группе «Товар» (для расположения названий товаров в алфавитном порядке). Можно одновременно осуществлять сортировку по 64 столбцам.

1. Выделите одну любую ячейку в диапазоне сортируемых данных.
2. Нажмите кнопку Сортировка и фильтр группы Редактирование вкладки Главная и выберите команду Настраиваемая сортировка или нажмите кнопку Сортировка группы Сортировка и фильтр вкладки Данные.
3. В окне Сортировка в раскрывающемся списке Столбец выберите название столбца, по данным которого будет выполняться сортировка. В раскрывающемся списке Сортировка выберите признак сортировки (значение, цвет ячейки, цвет шрифта или значок ячейки). Затем щелкните по стрелке раскрывающегося списка Порядок и выберите направление сортировки или цвет.
4. В окне Сортировка нажмите кнопку Добавить уровень и после того, как появится новая строка, выберите название столбца, по данным которого будет выполняться последующая сортировка. Аналогично предыдущему пункту настройте порядок сортировки. Повторите это действие для других столбцов.
5. После выбора всех необходимых уровней сортировки нажмите кнопку ОК.

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

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

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

8. Перейдите к выполнению Контрольного задания по практической работе №4 по Ms Excel.

Приглашайте друзей на мой сайт