Практическая работа №11
Excel 2007. Работа со списками
В Excel имеются средства для обработки данных, организованных по принципу баз данных – функции списка, основные операции которых – сортировка данных и поиск определенной информации.
Excel имеет даже некоторые преимущества по сравнению с другими программами, предназначенными для работы с БД. Например, найденные значения в списке можно включить в вычисления, построить на их основе диаграмму.
Создание списка
Excel распознает списки автоматически при правильной их организации: отдельные записи должны быть однородны по строкам и/или столбцам. При автоматическом определении признаком конца области является пустая строка. Обычно Excel принимает первую (верхнюю) строку в качестве строки названий. Данные этой строки исключены из обрабатываемой области списка.
Задание 1
На отдельном листе электронного журнала класса оформить таблицу, содержащую данные об учениках класса «Сведения об учениках». Таблица должна содержать: номер, фамилию, имя, дату рождения, город рождения, домашний телефон (установите формат ячеек – текстовый), успеваемость (отличник, хорошист, троечник). При создании таблицы должны быть выполнены следующие требования:
-
Ввести в таблицу данные, при этом имена и город рождения с использованием функции автозавершения значения ячеек, для чего перед заполнением таблицы выполнить: Кнопка «Office»/Параметры Excel/Дополнительно. При вводе текста в ячейку электронная таблица Excel сначала будет проверять содержимое других ячеек данного столбца. Если будет обнаружен текст, первые символы которого совпадают с введенными символами, то ввод данных заканчивается автоматически. Можно проигнорировать предложение Excel, если продолжить ввод. Для подтверждения надо нажать ENTER.
-
Для получения формулы в столбце «успеваемость» необходимо использовать логические функции ЕСЛИ, И. При добавлении функции выдается информация по ее использованию, ознакомьтесь с правилами использования этих функций. Формула будет аналогична следующей:
=ЕСЛИ(И(матем!B3=5;био!B3=5;информ!B3=5);"отличник";ЕСЛИ(И(матем!B3>=4;био!B3>=4;иформ!B3>=4);"хорошист";ЕСЛИ(И(матем!B3>=3;био!B3>=3;информ!B3>=3);"троечник";"двоечник"))),
где матем, био, информ – имена листов. Для заполнения формулы лучше всего использовать выбор подставляемых значений с помощью кнопки мыши. Т.е. записали формулу, а вместо имен ячеек, на которые идет ссылка, вставляете ячейку путем нажатия на ней кнопкой мыши.
Сортировка списков
После ввода данных Вам может потребоваться упорядочить их. Процесс упорядочивания записей в базе данных называется сортировкой. При сортировке изменяется порядок следования записей в базе данных или таблице.
Сортировка по возрастанию предполагает следующий порядок: числа, текст, логические значения, значения ошибок, пустые ячейки. Сортировка по убыванию происходит в обратном порядке. Исключением являются пустые ячейки, которые всегда располагаются в конце списка. Текстовые данные упорядочиваются в алфавитном порядке.
Можно задать три уровня сортировки одновременно за одну операцию, можно выполнить сортировку сначала по первому уровню, потом в полученном списке – по второму, а затем – по третьему уровню. Второй и третий уровень позволяют определить порядок вторичной сортировки для записей, в которых имеются совпадающие значения.
При использовании функций списка, выделения области списка происходит автоматически. Однако пользователь может предварительно выделить диапазон ячеек с записями, подлежащие сортировке.
Задание 2
Выполнить различные виды сортировок списка, открыв диалоговое окно, сортировка диапазона: Данные/ Сортировка и фильтр. Сделать сортировку по трем уровням: успеваемость, дата рождения, фамилия.
Познакомиться с видами установок различных параметров в диалоговом окне «параметры сортировки».
Применение фильтров
Назначение фильтра: ввод, удаление записей в удобной для пользователя форме, а также поиск информации. Преимущество использования по сравнению с формой данных: результат запроса можно скопировать в отдельную область таблицы и сразу же использовать в вычислениях. Результат операции: строки, не соответствующие данному критерию, оказываются скрытыми. При использовании фильтра должны выполняться следующие требования:
-
записи должны быть однородны по строкам;
-
указатель ячейки должен находиться внутри списка;
-
в первой строке должны находиться метки столбцов.
В электронной таблице Excel для фильтрации данных используются команды
Фильтр и
Расширенный фильтр(Дополнительно). Обе команды вызываются в результате выбора на ленте
Данные/ Сортировка и фильтр.
Для выбора данных можно задавать целый ряд различных критериев, используя настраиваемый фильтр. При этом каждый следующий критерий всегда относится к подмножеству списка, полученного в результате применения предыдущего критерия. Можно задавать комплексные критерии типа >=1-янв-70 И <=8 янв-70; «отл.» ИЛИ «хор.»
Задание 3
-
С помощью фильтра создать список отличников и скопировать его в отдельное место рабочего листа.
-
В отдельном месте создать список все учеников, родившихся летом.
-
Создать список хорошистов и отличников, которые родились зимой (в один и тот же год).
-
Создать список всех учеников, родившихся в одном городе.
-
Создать список, содержащий сведения об учениках, родившихся в одном городе и имеющих телефон, начинающийся с первых двух одинаковых цифр.
Индивидуальное задание
Показать выполнение Задание 1, Задание 2, Задание 3.