5.2. Практикум. Технология накопления данных и их обработки в Ехсеl

Определения и обозначения

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

Список данных — таблица, где каждая строка содержит сведения об одном объекте, а столбцы — отдельные характеристики объектов. Например, строка может описывать книгу, а поля — её название, автора и год издания. Такая структура помогает не путать значения и использовать команды Excel для обработки списка.

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

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

Как это устроено

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

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

При вводе каждой новой записи её значения размещают в одной строке, по соответствующим столбцам. Так сохраняется связь между характеристиками и объектом: название, автор и год книги остаются в одной записи. Когда появляются новые сведения, их добавляют следующими строками, придерживаясь принятой структуры и форматов. Благодаря этому список можно наращивать, не переустраивая его каждый раз.

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

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

Инструменты обработки списка данных в Excel

Сначала определите, какую задачу нужно решить: добавить сведения, изменить порядок записей или оставить на экране только подходящие строки. Затем выберите соответствующую операцию и проверьте, что она применяется ко всему списку. После обработки убедитесь, что нужные записи остались на месте и значения по-прежнему относятся к своим объектам.

Разбор задач

Задача 1. Нужно создать список книг школьной библиотеки, внести несколько записей и добавить новую книгу. Сначала решим, какие сведения помогут отличать книги и находить нужную. Каждую книгу будем описывать одной строкой, а её характеристики — отдельными столбцами.

1. Выберите поля: название, автор, год издания и жанр. Такой набор описывает книгу и позволяет позднее искать её по разным характеристикам.
2. Введите названия полей в заголовки столбцов, а сведения о каждой книге — в отдельную строку. Год указывайте числом, а названия жанров записывайте одинаково, без вариантов вроде «фантастика» и «фантаст.» для одной категории.
3. Чтобы добавить книгу, внесите её данные в следующую свободную строку. Проверьте, что название, автор, год и жанр стоят каждый в своём столбце.

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

Задача 2. Нужно упорядочить участников соревнования сначала по классу, а внутри класса — по фамилии. Одной сортировки по фамилии недостаточно: участники разных классов окажутся перемешаны. Поэтому зададим два уровня, где класс будет главным критерием, а фамилия — дополнительным.

1. Убедитесь, что в списке есть отдельные поля «Класс» и «Фамилия», а каждый участник записан одной строкой.
2. Выберите сортировку по нескольким уровням и укажите сначала поле «Класс». Выберите нужный порядок классов, например по возрастанию.
3. Добавьте второй критерий — «Фамилия» и задайте алфавитный порядок. После сортировки проверьте несколько строк: внутри каждого класса фамилии должны идти по алфавиту, а остальные сведения участника должны перемещаться вместе с ним.

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

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

1. Включите фильтрацию для таблицы и в поле даты выберите нужный месяц или установите условие для периода.
2. В поле категории оставьте требуемое значение, а в поле суммы задайте условие «больше» установленного порога. Каждое условие относится к своему столбцу.
3. Просмотрите оставшиеся строки и проверьте каждую по месяцу, категории и сумме. Снимите фильтры, чтобы снова показать полный список, если понадобится вернуться к остальным продажам.

Ответ. На экране останутся продажи, подходящие под все три условия. Остальные записи будут скрыты фильтром, а не удалены, поэтому их можно показать снова, изменив или сняв условия.

Типичные ошибки

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

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

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

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

Связь с другими темами

Для работы со списком нужно уметь вводить данные в ячейки, выделять диапазоны, переключаться между листами и сохранять книгу Excel. Без этих навыков трудно правильно подготовить область списка и сохранить результат. Сортировка и фильтрация применяются не только в учебных таблицах, но и в электронных каталогах и базах данных. Умение заранее продумать поля станет основой для дальнейшей работы с формулами, функциями и сводными таблицами.

Что запомнить

• Одна строка списка — одна запись об объекте.
• Столбец списка соответствует одному полю, или характеристике.
• У каждого поля должен быть понятный заголовок.
• Значения одного поля следует вводить в едином формате.
• Сортировка меняет порядок записей, но не должна нарушать их целостность.
• Фильтрация скрывает неподходящие записи, а не удаляет их.