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

1.    Открытие имеющейся рабочей книги.

Задание 1:

Скопируйте файл, созданной Вами ранее рабочей книги под именем Классный журнал 3 (Ваша фамилия). Откройте ее в Excel (двойным щелчком)2

Создание диаграмм.

Информация о диаграммах

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

Создание диаграммы для смежных диапазонов данных

Задание 2.

Построить диаграмму успеваемости класса по предмету Математика за 1 четверть.

Указание:

Выделите Математика, щелкнув по его ярлычку.

Выделите   диапазон   ячеек,   соответствующий   оценкам   за   первую  четверть  (без заголовка!), включая итоговую

Нажмите кнопку Вставка-Диаграмма 

На закладке Работа с диаграммами определите

i)название для диаграммы - «Оценки за первую четверть по математике»

ii)название для оси X (категорий) - «Фамилии»

iii)названия для оси Y (значений) - «Оценки» и нажмите кнопку .

Разместите диаграмму на имеющемся листе - Математика: выберите переключить и из раскрывающего списка выберите лист с названием Математика

Создание диаграммы для НЕсмежных диапазонов данных

Задание 3. Построить диаграмму успеваемости класса по предмету Математика по четвертям и за год

Указание:

Выделите Математика, щелкнув по его ярлычку.

Выделите диапазоны ячеек, соответствующие ИТОГОВЫМ оценкам за первую, вторую, третью и четвертую четверть (без заголовка!), а также за год (удерживая клавишу CTRL)

Нажмите кнопку Диаграмма на панели инструментов Вставка  и постройте  гистограмму ПЯТИ рядов (аналогично предыдущему заданию), разместив ее на отдельном листе.

3.   Создание списка данных

Информация о списках

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

3.1. Описание заголовка списка и частичный ввод данных

Задание 4:

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

Указание:

Создайте новый лист и назовите его Сведения об учениках

Скопируйте столбцы номеров и фамилий учеников (с их заголовками!) с листа какого-либо предмета. Разнесите имена и фамилии в разные столбцы.

Дополните строку заголовка недостающими названиями столбцов: имя, дата рождения, адрес, телефон, успеваемость.

Введите  в  столбце  успеваемость  формулу,  суть  которой  состоит  в  получении ХАРАКТЕРИСТИКИ успеваемости для ученика. Для этого:

применяется статистическая функция ЕСЛИ(), которая возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ. Т.е. у данной функции ТРИ аргумента: логическое выражение, значение когда оно истинно и значение когда выражение ложно.

в качестве первого аргумента необходимо указать выражение на основе функции И(), проверяющая получение учеником ОЦЕНОК примерно одного и того же достоинства ПО ВСЕМ ПРЕДМЕТАМ . Например: И(Математика!86>=4.5;Физика!86>=4.5)

в качестве значения, заносимого в ячейку при истинности логического выражения (значение_если_истина), необходимо указать соответствующую характеристику: отличник, хорошист, троечник или двоечник (для начала проверка осуществляется для характеристики ОТЛИЧНИК).

в качестве последнего аргумента (значение_если ложь) необходимо использовать опять вызов функции ЕСЛИ(), для проверки на следующую характеристику. При этом функция ЕСЛИ() будет вызываться несколько раз и вложено, до тех пор пока не будут определены условия для описания всех характеристик

Замечание: До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов значение если истина и значение если ложь.

е) В общем случае данная формула имеет вид:

=ЕСЛИ(Условия на 5; «отличник»; ЕСЛИ (Условия на 4; «хорошист»; ЕСЛИ Условия на 3; «троечник»; «двоечник»)))

Пример записи Условия на 5 приведен в пункте b). По аналогии самостоятельно введите и другие условия.

Замечание: При вводе формулы (для указания адресов конкретных ячеек с разных листов!) используйте кнопку   -, позволяющей сформировать адрес указанной ячейки АВТОМАТИЧЕСКИ при щелчке по ней и кнопку … для возврата в поле ввода.

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

3.2.   Ввод данных списка при помощи формы

Информация о форме для работы с данными списка

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

Задание 5. Внесите с помощью формы недостающие данные в список

Указание:

Выделите ячейки под значении (напротив фамилий ) в столбце Телефон и задайте им числовой формат Номер телефона в категории Дополнительный (из контекстного меню по команде Формат ячеек...)

Внесите в ячейку значение (не используйте разделителей!) в междугороднем формате. Например: +73517748852 и обратите внимание на отображение этого значения в ячейке

Продолжить ввод можно с использованием формы: команда Форма... Для этого необходимо добавить кнопку «Форма» на панель быстрого доступа. Щелкните стрелку на панели быстрого доступа и выберите элемент Добавить команды. В поле Выбрать команды из щелкните элемент Все команды. В списке выберите кнопку Форма и щелкните элемент Добавить.  заполните оставшиеся пустые поля в записях учеников. Сделайте несколько адресов и лет рождений одинаковыми.

3.3.    Сортировка списков данных

Информация о сортировке данных списка .

Можно задать три уровня сортировки одновременно за одну операцию, можно выполнить сортировку сначала по первому уровню, потом в

полученном списке - по второму, а затем - по третьему уровню. При

использовании функций списка, выделения области списка происходит

автоматически. Однако, пользователь может предварительно выделить диапазон ячеек с записями, подлежащие сортировке.

Задание 6:

Сделать сортировку в порядке возрастания по трем уровням: успеваемость, дата рождения, фамилия.

Указание:

Выделите ячейки списка данных (вместе с заголовком)

Вызовите из меню Данные команду Сортировка

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

4.    Применение фильтров к списку данных

Информация о фильтрах

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

записи должны быть однородны по строкам;

курсор перед применением фильтра должен находиться внутри списка;

в первой строке должны находиться метки столбцов.

4.1.   Применение автофильтра

Задание 7:

С помощью автофильтра создать список отличников и скопировать его на новый лист путем специальной вставки только данных

Указание:

Создайте новый лист и назовите его Фильтры

Обозначьте заголовок первой группы - Список отличников

Перейдите на лист Сведения об учениках

Установите курсор внутрь списка и выберите из меню Данные/ Сортировка и фильтр команду Фильтр (заголовок списка должен приобрести вид совокупности раскрывающихся списков)

Раскройте список столбца Успеваемость выберите команду Условие...

В появившемся диалоговом окне выбрать операцию сравнения - равно и значение - отличник

Получившийся список выделить и скопировать на подготовленный ранее лист (Фильтры)

Восстановите первоначальный список: выберите команду (Все) из раскрывающегося списка поля Успеваемость

Задание 8:

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

Указание:

Создайте на листе Фильтры ниже списка отличников новый заголовок для списка - Одинаковые

При установленном автофильтре для столбца Адрес выберите условие: операция сравнения - содержит, значение которое ищем - Ленина (название улицы выбирайте сами!)

для столбца Фамилия выберите условие: операция сравнения - равно, значение которое ищем - ?а* (что означает любой одиночный символ вначале, второй - а, остальные также могут быть любыми)

4.2.   Применение расширенного фильтра

Задание 9:

С помощью расширенного фильтра создать список тезок и родившихся в диапазоне с 1970 по 2000 год

Указание:

1) Создайте на листе Сведения об учениках таблицу критериев отбора:

Скопируйте ниже основной таблицы названия заголовков: Имя и Год рождения;

Введите  в  одной  строке  условия  отбора  (для  проверки  их  одновременного наступления):

ТАБЛИЦА

2) Установите курсор внутрь списка и выберите из меню Данные/ Сортировка и Фильтр команду Дополнительно

В появившемся диалоговом окне выделите: Исходный диапазон (диапазон ячеек где располагается список (вместе с заголовком) и Диапазон условий (диапазон ячеек таблицы критериев отбора). Используйте кнопку … , позволяющей сформировать адрес  указанного диапазона ячеек АВТОМАТИЧЕСКИ  после  непосредственного

выделения его, и кнопку … для возврата в поле ввода.