ЛР. Сортировка и фильрация_v2 (1). Тема Работа с данными. Фильтрация и сортировка данных
 Скачать 2.31 Mb.
|
|
Практическая работ Тема: Работа с данными.Фильтрация и сортировка данных Оглавление 1. Закрепление первой строки, удаление лишних пробелов и поиск пустых ячеек 2 1.1 Закрепление первой строки 2 1.2. Удаление лишних пробелов (2 способа) 2 3. Проверка данных 5 4. Фильтрация 12 3.1. Создание фильтра 12 5. Фильтрация по цвету ячейки 15 5.1. Фильтрация по цвету фона ячейки 15 5.2. Сортировка таблицы по цветам данных 17 5.3. Сортировать /фильтровать по цвету текста в ячейке 20 5.4. Как отменить сортировку и вернуть исходные данные 22 5.6. Диапазон сортировки 23 Задание: 26 1 1. Закрепление первой строки, удаление лишних пробелов и поиск пустых ячеек 1.1 Закрепление первой строки Если данных в таблице много, то при прокрутке название строк (столбцов) будет не видно, что неудобно при работе с большим объемом данных. Для этого в Google Sheets предусмотрена возможность закрепления строк (столбцов) . Для этого на панели инструментов заходим во вкладку “Вид”, выбираем “Закрепить”-”1 строку” Аналогичным образом можно закрепить столбцы, а также несколько строк и столбцов. 1.2. Удаление лишних пробелов (2 способа) Способ 1. При помощи формул Удалить лишние пробелы можно при помощи формул. Так, например, в столбце “В” перед словами “Мужской” и “Женский присутствуют излишние пробелы. Избавиться от них можно следующим образом. Выбираем свободный от данных столбец например “I” и во второй строке создаем формулу. Напоминаем, что все формулы начинают писаться со знака “=”. 2 Итак в ячейке I2 создаем формулу =сжпробелы(B2), B2 это ячейка в которой предполагаем, что есть пробелы: После создания формулы, мы ее копируем и тиражируем по столбцу “I” до конца таблицы: После того как растиражировали данные без пробелов, их необходимо вставить в столбец “B”. Для этого копируем столбец “I” (начиная со второй строки) и с помощью “Специальной вставки” - “Только значения” заменяем исходные данные на новые, но уже бе з пробелов. После этого столбец “I” удаляем. 3 ” 2 Способ Выделим таблицу о во вкладке “Данные” нужно выбрать “Очистка данных”-”Удалить пробелы”. 4 2. Проверка данных 2.1. Нахождение ошибок Функция позволяет убедиться, что сведения соответствуют указанным параметрам. Проверим все ли данные в столбце G являются числовыми 5 надо выставить настройки, в соответствии с которыми будет происходить проверка. В меню "Правила» выбрать подходящий вариант — в данном случае это "Число». Также можно выбрать другие критерии, такие как только текст, даты, предварительно определенный список параметров, элементы из указанного диапазона или пользовательскую формулу проверки. 6 7 Замечание: При необходимости, проверять можно не весь документ, а указать диапазон ячеек, которые следует проверить на соответствие формату. В открывшемся окне проверки выберем “В диапазоне” : 8 Предупреждающее сообщение уведомляет пользователя о том, что число должно быть между определенным диапазоном, и содержит недействительные данные в ячейке с красным уведомлением. Если необходимо удалить установленное правило необходимо выделить диапазон, в котором создано правило, в разделе "Данные» — "Проверка данных» необходимо навести указатель мыши на “Удалить проверку” и все правила, созданные ранее, будут удалены из указанного диапазона. 1.3. Замена точек на запятые в числах Для того чтобы табличный редактор мог производит вычисления, необходимо чтобы данные имели правильный формат. Так если число “12.66” написан через точку то редактор воспринимает эту запись не числом а текстом, Правильная запись должна быть через запятую “12,66”. Поэтому необходимо провести проверку исходных данных и при необходимости произвести замену точек на запятую. 9 Чтобы найти или заменить данные можно нажать на сочетание клави “CTTL+F” или в меню нахлоим “Правка” и выбираем “Найти и заменить” Попадаем на новую вкладку для поиска данных. В этой вкладке выбираем три точки: Открывается расширенная вкладка для поиска и замены данных. В нашем случае необходимо произвести замены всех точек в столбце G на запятые, чтобы поменять формат данных с текстового на числовой. 10 4. Фильтрация 3.1. Создание фильтра 11 Для создания фильтра нажмите Просмотр и наведите указатель мыши на Закрепить. Выберите 1 строку в появившемся меню. Щелкните любую ячейку , содержащую данные. Нажмите кнопку Фильтр. В заголовке каждого столбца появляется стрелка раскрывающегося списка. Щелкните стрелку раскрывающегося списка для столбца, который вы хотите отфильтровать. В нашем примере отфильтруем столбец B , чтобы просмотреть информацию связанную с “Мужчины”. Сначало нажмите Сбросить, чтобы снять все галочки. 12 После этого отметим галочкой “Мужчины” 13 Получаем таблицу: 14 5. Фильтрация по цвету ячейки Google Таблицы позволяют сортировать набор данных по цвету. Это может быть цвет фона ячейки или цвет текста. Поэтому, если вы работаете с данными, к которым применен цвет, вы можете легко отсортировать их по цвету, чтобы все эти цветные ячейки / строки собрались вместе. 5.1. Фильтрация по цвету фона ячейки Предположим, есть набор данных, как показано ниже, и вы хотите отфильтровать эти данные по цвету (так, чтобы все ячейки с желтым цветом были вместе вверху). Выбираем любую ячейку в данных (или выберите весь набор данных) и в меню выберите пункт «Данные» - «Создать фильтр». Это применит фильтр к верхней строке в наборе данных. 15 Щелкните значок фильтра в любом из заголовков столбцов (где столбец имеет цвет, на основе которого вы хотите отсортировать). Наведите курсор на параметр «Фильтровать по цвету», а затем наведите его на параметр «Цвет заливки». Это покажет вам все цвета, которые использовались в наборе данных. Щелкните цвет, по которому проводится фильтрация.. В этом примере по цвету “Темно-желтый (1)” 16 Вышеупомянутые шаги мгновенно отсортируют набор данных и выдадут результат, как показано ниже (где все ячейки желтого цвета находятся вверху). 5.2. Сортировка таблицы по цветам данных Рассмотрим пример в котором дана таблица данных с выделенными по строкам цветами: Для сортировки по нескольким цветам в Google Таблицах необходимо выбрать любую ячейку в данных (или выберите весь набор данных). В меню выберите пункт «Данные». В появившихся опциях нажмите «Создать фильтр». Это применит фильтр к верхней строке в наборе данных. 17 Далее щелкните значок фильтра в любом из заголовков столбцов (где столбец имеет цвет, на основе которого вы хотите отсортировать). Наведите курсор на параметр «Сортировать по цвету», а затем наведите его на параметр «Цвет заливки». Это покажет вам все цвета, которые использовались в наборе данных. выберите “Светло-голубой (2)”. Теперь снова нажмите значок фильтра в любом заголовке, наведите курсор на «Сортировать по цвету», затем на «Цвет заливки». Нажмите на вариант цвета «оранжевый». Это отсортирует набор данных, в котором все строки с желтым цветом находятся вверху. Теперь снова нажмите значок фильтра в любом заголовке, наведите курсор на «Сортировать по цвету», затем на «Цвет заливки», а затем нажмите на вариант 18 цвета «светло-желтый 3». Вышеупомянутые шаги сортируют данные, где последний выбранный вариант цвета (зеленый в нашем примере) находится в верхней части отсортированного набора данных, а затем желтый цвет. Замечание. При использовании функции «Сортировка по цвету» в Google Таблицах, где набор данных имеет несколько цветов, помните, что цвет, который отсортирован последним, будет идти вверх. Поэтому, если вы хотите, 19 чтобы данные были отсортированы по ячейкам голубого цвета, желтого и оранжевого цветов сверху вниз, вы должны сначала отсортировать по оранжевому цвету, затем по желтому и, наконец, по голубому. 5.3. Сортировать /фильтровать по цвету текста в ячейке Так же, как вы можете сортировать (фильтровать) по цвету фона ячеек, Google Таблицы также позволяют сортировать по цвету шрифта. Предположим, есть набор данных, показанный ниже, где в некоторых ячейках есть текст красного цвета . Чтобы отсортировать ячейки с красным цветом шрифта необходимо: выбрать любую ячейку в данных, в меню выберите пункт «Данные», щелкните “Создать фильтр”. Это включит фильтры для верхней строки набора данных. Наводим мышью на заголовок столбца, в котором встречается красный шрифт “Административные округа”, щелкаем на значок фильтра. Далее наводим курсор на параметр «Сортировать по цвету», а затем наведите его на параметр «Цвет текста. Это покажет вам все цвета, которые использовались в наборе данных. Выберите необходимый цвет - красный. 20 После выполнения всех действий получим таблицу вида: Замечани! Если хотите, чтобы эти ячейки были внизу, необходимо выбрать другой цвет (черный в этом примере), и он отсортирует и перенесет все ячейки со шрифтом черного цвета вверху. Это также означает, что те, что имеют красный цвет, автоматически окажутся внизу. Если есть многоцветные шрифты, все равно можно их отсортировать. Просто отсортируйте его один раз по одному цвету, а затем по следующему цвету. И помните, что цвет, который сортируете по последнему, всегда будет вверху в наборе данных (после сортировки). Похожий порядок действий и для фильтрации по цвету текста: 21 Получим следующий набор данных 5.4. Как отменить сортировку и вернуть исходные данные Если вам может понадобиться вернуть исходный порядок сортировки позже, вы можете легко это сделать. Хитрость здесь заключается в том, чтобы добавить вспомогательный столбец (слева или справа от набора данных) и пронумеровать их с шагом 1 (например, 1,2,3…), как показано ниже: 22 Теперь, если вы сортируете данные и по какой-то причине вам необходимо их отсортировать и вернуть исходные данные, вы можете отсортировать эти данные на основе вспомогательного столбца. 5.6. Диапазон сортировки Другой способ сортировки данных — с помощью параметра Диапазон сортировки . При этом вы можете либо отсортировать весь столбец данных, либо отсортировать только выбранный диапазон данных в столбце. Например, предположим, что необходимо отсортировать только первые 10 строк данных в столбце “имя” алфавитном порядке. Можно выделить первые 10 строк под столбцом B, а затем перейти к Данные - Сортировать диапазон . 23 Как видно, первые 10 строк данных отсортированы в алфавитном порядке по имени. Замечание: Во всплывающем окне Можно выбрать “расширенные настройки сортировки данных” и задать например сортировку по двум столбцам и более. 24 Например отсортируем нашу таблицу по столбцу B и E. Выберем диапазоном всю нашу таблицу без заголовков A2:G31. И далее как было показано выше: В расширенных настройках выставляем сортировку сначало по столбцу В, а затем нажимаем на “Добавить еще один столбец и выбираем Е: 25 Получим следующую таблицу данных: Задание: В приложенном к заданию файле: 1. Загрузить файл Класс.CSV. Выполнить шаги описанные в методических указаниях. 2. Загрузить файл представленный в формате Класс.CSV 3. Закрепить первую строку 4. Удалить лишние пробелы и осуществить поиск пустых ячеек 5. Провести проверку данных по слову “Западный” 6. Создать фильтр. Провести фильтрацию по районам Южный Северный 7. Применение несколько фильтров. Например слову “футбол” и полу “Женщины”. 8. Выделить слово женщины оранжевым цветом. Осуществить фильтрацию по цвету фона ячейки. 9. Отменить сортировку и вернуть исходные данные 10. Выделить красным цветом значения обозначающих 1 и 2 клас. 11.Осуществить сортировку по цвету данных 12. Сортировать /фильтровать по цвету текста в ячейке 13. Как отменить сортировку и вернуть исходные данные 26 |