Задание 1. Excel 2007. Работа со списками

Лабораторная работа 5

Excel 2007. Работа со списками

В Excel имеются средства для обработки данных, организованных по принципу баз данных – функции списка, основные операции которых – сортировка данных и поиск определенной информации.

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

Создание списка

Excel распознает списки автоматически при правильной их организации: отдельные записи должны быть однородны по строкам и/или столбцам. При автоматическом определении признаком конца области является пустая строка. Обычно Excel принимает первую (верхнюю) строку в качестве строки названий. Данные этой строки исключены из обрабатываемой области списка.

Задание 1

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

1. Ввести в таблицу данные, при этом имена и город рождения с использованием функции автозавершения значения ячеек, для чего перед заполнением таблицы выполнить: Кнопка «Office»/Параметры Excel/Дополнительно. При вводе текста в ячейку электронная таблица Excel сначала будет проверять содержимое других ячеек данного столбца. Если будет обнаружен текст, первые символы которого совпадают с введенными символами, то ввод данных заканчивается автоматически. Можно проигнорировать предложение Excel, если продолжить ввод. Для подтверждения надо нажать ENTER.

2. Для получения формулы в столбце «успеваемость» необходимо использовать логические функции ЕСЛИ, И. При добавлении функции выдается информация по ее использованию, ознакомьтесь с правилами использования этих функций. Формула будет аналогична следующей:

Рассмотрим подробнее.

Функция ЕСЛИ является логической, со следующей структурой:

ЕСЛИ(условие, выражение если истина, выражение если ложь).

Перейдем на лист 2 и для ячейки F4 выберем логическую функцию:

После нажатия OK:

При этом в строке адреса пропишется следующее:

Введем функцию путем нажатия OK

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

Результат:

Далее «заветвим» условие, то есть сразу поставим проверку на отличника, ударника, троечника и двоечника.

Прописав условие следующим образом:

В результате получим следующий резульатат

Здесь в приведенных примерах проверка проводилась только на один предмет.

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

Теперь перейдем на лист3, и начнем пошагово заполнять на проверку трех предметов, сначала только на отличника, а потом на все остальные:

А теперь поставим полную проверку

Условие на все оценки:

=ЕСЛИ(И(Лист1! D4=5; Лист1! E4=5; Лист1! F4=5); " Отличник"; ЕСЛИ(И(Лист1! D4> =4; Лист1! E4> =4; Лист1! F4> =4); " ударник"; ЕСЛИ(И(Лист1! D4> =3; Лист1! E4> =3; Лист1! F4> =3); " троечник"; " двоечник")))

Результат:

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