Материал: MS EXCEL в расчетных задачах(учебник,по которому выполнены работы )

62

•сумму квартплаты по категориям домов;

•общую сумму квартплаты в данном списке.

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

Рекомендации:

•базовые стоимости 1м2 B хранить в отдельных ячейках;

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

Вариант № 10

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

Считать, что результатом тестирования является количество неправильных ответов КОЛ_НЕПР из общего количества вопросов КОЛ_ВОПР. Определение оценки производится следующим образом:

Таблица оценок тестирования содержит следующие столбцы:

•фамилия;

•общее количество вопросов (КОЛ_ВОПР);

•количество неправильных ответов (КОЛ_НЕПР);

•процент правильных ответов;

•оценка (определяется по справочной таблице).

Справочная таблица содержит два столбца (рис.5.11) - "Процент правильных ответов" и "Оценка".

Процент правильных ответов для каждого студента рассчитывается по формуле: 100*(КОЛ_ВОПР-КОЛ_НЕПР)/КОЛ_ВОПР.

Вычислить:

•средний балл по всему списку (целесообразно использовать функцию СРЗНАЧ);

•количество студентов, получивших оценки 2, 3, 4 и 5 (целесообразно использовать функцию СЧЁТЕСЛИ).

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

Требования к решению:

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

63

6.ОБРАБОТКА СПИСКОВ

6.1.ОСНОВНЫЕ ПОНЯТИЯ

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

Термины "база данных" и "список" фактически являются синонимами. Однако далее будем придерживаться принятого в Microsoft соглашения и называть структурированные таблицы в документах MS Excel списками. Базой данных будем называть файлы таблиц, созданные другими системами обработки данных, такими как Microsoft Access, dBase или FoxPro.

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

Работа со списками в MS Excel, как и работа с любыми наборами данных, сводится к некоторому набору стандартных операций:

1.Поддержание данных в актуальном состоянии 1.1.Добавление новых записей (строк), 1.2.Удаление записей.

1.3.Корректировка (внесение изменений) в уже существующие записи.

2.Сортировка записей по какомулибо признаку.

3.Отбор записей по какому-либо критерию.

4.Подведение итогов.

6.2.ФОРМИРОВАНИЕ СПИСКА

Для обеспечения эффективности работы со списками необходимо соблюдать следующие правила при их создании:

1.Каждый столбец должен содержать однородную информацию.

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

3.Необходимо избегать пустых строк и столбцов внутри списка.

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

Правило 2 обеспечивает присвоение имен полям. Эти имена постоянно используются при обработке списков.

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

На рис.6.1 приведен список из 10 столбцов.

64

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

Чтобы присвоить имя списку:

1.Выделите весь список, включая заголовки столбцов;

2.Напечатайте в области ссылок (перед строкой формул)

База_данных (между двух слов вставлен символ подчеркивания, для того чтобы они воспринимались как единое целое).

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

Если всему списку было присвоено имя База_данных, то при добавлении строк с помощью стандартной экранной формы (по нажатию кнопки Добавить) они (строки) автоматически включаются в определение имени База_данных.

Рис.6.1.

6.3. СОРТИРОВКА СПИСКОВ

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

65

Пример.

Продемонстрируем сортировку на списке рис.6.1. Нужно отсортировать список по столбцу Бригада. Для этого:

1)выделите одну ячейку (не интервал) в этом списке;

2)выполните команду Сортировка (Вкладка Данные, группа

Сортировка и фильтр);

3)откроется диалоговое окно Сортировка (рис.6.2);

4)выберите поле, по которому нужно сортировать (в этом примере -

Бригада).

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

Пользуясь командой Сортировка..., можно сортировать список по нескольким столбцам. Для этого необходимо использовать вкладку Добавить уровень в диалоговом окне Сортировка (рис.4.2).

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

Рис. 6.2 6.4. АНАЛИЗ СПИСКА С ПОМОЩЬЮ ФИЛЬТРОВ

Отфильтровать список − это значит скрыть все строки кроме тех, которые удовлетворяют заданным критериям. Excel предоставляет две команды фильтрации: Автофильтр − для простых критериев, и Расширенный фильтр − для более сложных критериев.

6.4.1. КОМАНДА АВТОФИЛЬТР

Для применения обычного или автофильтра нужно выполнить следующую последовательность действий:

1) выделите какую-либо ячейку в списке;

66

2)нажать кнопку Фильтр в группе Сортировка и фильтр

Справа от каждого заголовка столбца появиться кнопка

"Раскрывающийся список" (со стрелкой вниз). Если щелкнуть по этой кнопке, то раскроется список уникальных значений данного столбца, которые можно использовать для задания критерия фильтра. На рис 6.3 показан результат фильтрации по столбцу Бригада, выбраны только те строки, где значение Бригада равно 21.

Номера строк, не удовлетворяющие критериям команд Фильтр (Автофильтр) и Расширенный фильтр, MS Excel просто скрывает. Номера отфильтрованных строк выводятся контрастным цветом, а в строке состояния появляется сообщение Найдено записей.

Рис 6.3

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

Критерий заданного фильтра на рис.6.3 фактически состоит из одного равенства Бригада=21 . Можно создать автофильтр с более сложным критерием, состоящим из одного или двух условий с любыми знаками сравнения. Например, следует отфильтровать значения, находящиеся в некотором интервале. Чтобы создать пользовательский автофильтр, раскройте список критериев и выберите элемент Настраиваемый фильтр...

(рис.6.4.а).

Откроется диалоговое окно Пользовательский автофильтр (рис 6.4.б). В этом диалоговом окне можно создать два условия, соединяемое логическими условиями И или ИЛИ.

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

Предположим, что в нашем списке требуется посмотреть только записи со значением столбца Бригада, лежащим в интервале от 9 до 14. Т.е. значение Бригада больше или равно 9 И меньше или равно 14 . Эти