ПРАКТИЧЕСКАЯ РАБОТА. Практическая работа задание Формирование структуры таблицы и заполнение ее постоянными данными

• Название: Практическая работа задание Формирование структуры таблицы и заполнение ее постоянными данными
• Дата: 21.12.2022
• Размер: 286.5 Kb.
• Имя файла: ПРАКТИЧЕСКАЯ РАБОТА.doc
• Тип: Практическая работа; #856988
• страница: 1 из 2

ПРАКТИЧЕСКАЯ РАБОТА

Задание 1. Формирование структуры таблицы и заполнение ее постоянными данными

Подготовьте электронную экзаменационную ведомость, форма которой представлена на рис. 9.

Рис. 9. Форма экзаменационной ведомости

Последовательность действий:

1. Запустите программу Excel (Пуск —› Программы —› Microsoft Excel) и создайте новую рабочую книгу (команда Файл —› Создать или кнопка Создать на стандартной панели инструментов).

2. Введите в указанные ячейки (табл. 2) тексты заголовка и шапки таблицы, после чего отформатируйте ячейки и данные.

Таблица 2

3. Заполните ячейки столбца B данными о студентах учебной группы (приблизительно 10-15 строк) и отформатируйте данные.

4. Присвойте каждому студенту порядковый номер: введите в ячейку A6 число 1; установите курсор в нижний правый угол ячейки A6 так, чтобы указатель мыши приобрел изображение креста и, нажав правую кнопку мыши, протяните курсор на требуемый размер; выполните команду локального меню Заполнить.

5. После списка студентов в нижней части таблицы введите в ячейки столбца A текст итоговых строк согласно рис. 9.

6. Объедините две соседние ячейки для более удобного представления текста итоговых строк. Для этого выделите две ячейки; вызовите контекстное меню и выберите команду Формат ячеек; на вкладке Выравнивание установите флажок Объединение ячеек и нажмите кнопку ОК.

7. Сохраните рабочую книгу в своей рабочей папке (имя файла - Session).

Задание 2. Последовательность действий с формулами

В рабочей папке с именем Session рассчитайте:

• 
  количество неявок и оценок (отлично, хорошо, удовлетворительно, неудовлетворительно), полученных в данной группе;
  
• 
  общее количество полученных оценок.
  

Предлагается следующий алгоритм для расчета.

1. Ввести дополнительно 5 столбцов.

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

3. В нижней части таблицы ввести формулы подсчета суммарного количества полученных оценок определенного вида и общее количество оценок.

4. Скопировать несколько раз (по числу экзаменов в сессию) этот шаблон на другие листы и провести коррекцию оценок по каждому предмету.

Последовательность действий

1. Загрузите рабочую книгу с именем Session.

2. Введите названия (5, 4, 3, 2, неявки) соответственно в ячейки F5, G5, H5, I5, J5 вспомогательных столбцов.

3. Используя Мастер функций, введите в столбцы F5-J5 вспомогательные формулы. Рассмотрим эту технологию на примере ввода формулы в ячейку F6:

• 
  установите курсор в ячейку F6 и выберите мышью на панели инструментов кнопку Мастера функций;
  
• 
  в 1-м диалоговом окне выберите вид функции (Категория - логические; Функция - ЕСЛИ) и щелкните по кнопке ОК;
  
• 
  во втором диалоговом окне введите соответствующие операнды логической функции (Логическое выражение - D6 = 5; значение_если_ истина - 1; Значение_если_ ложно - 0) и щелкните по кнопке ОК.
  

4. С помощью Мастера функций введите формулы аналогичным способом в остальные ячейки данной строки.

5. Скопируйте эти формулы во все остальные ячейки дополнительных столбцов:

• 
  выделите блок ячеек F6:J6;
  
• 
  установите курсор в правый нижний угол выделенного блока и после появления черного крестика, нажав правую кнопку мыши, перетащите ее до конца таблицы;
  
• 
  выполните в контекстном меню команду Заполнить значения.
  

6. Определите имена блоков ячеек по каждому дополнительному столбцу.
Рассмотрим эту технологию на примере столбца F:

• 
  выделите все значения дополнительного столбца и введите команду Вставка —› Имя —› Присвоить;
  
• 
  в диалоговом окне в строке Имя введите слово ОТЛИЧНО и щелкните по кнопке Добавить;
  
• 
  проводя аналогичные действия с остальными столбцами, создайте еще имена блоков ячеек: ХОРОШО, УДОВЛЕТВОРИТЕЛЬНО, НЕУДОВЛЕТВОРИТЕЛЬНО, НЕЯВКА.
  

7. Выделите столбцы F - J целиком и сделайте их скрытыми (команда Формат —› Столбец —› Скрыть).

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

• 
  установите указатель мыши в ячейку С17 подсчета количества отличных оценок и щелкните по кнопке Мастер функций;
  
• 
  в диалоговом окне выберите: Категория - Математические, функция - СУММ; щелкните по кнопке ОК;
  
• 
  в следующем диалоговом окне в строке Число 1 установите курсор и введите команду Вставка —› Имя —› Вставить;
  
• 
  в появившемся диалоговом окне выделите имя блока Отлично и щелкните по кнопке ОК;
  
• 
  повторите аналогичные действия для подсчета количества других оценок в ячейках С18 - С21.
  

9. Подсчитайте общее количество (ИТОГО) всех полученных оценок другим способом:

• 
  установите курсор в пустой ячейке С21 и щелкните по кнопке ;
  
• 
  выделите блок ячеек, где подсчитываются суммы по всем видам оценок, и нажмите клавишу Enter.
  

10. Переименуйте текущий лист:

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

11. Скопируйте несколько раз текущий лист Экзамен 1:

• 
  установите курсор на имени текущего листа и вызовите контекстное меню;
  
• 
  выберите параметр Переместить/Скопировать, поставьте флажок Создавать копию и параметр Переместить в конец; щелкните по кнопке ОК.
  

12. Сохраните рабочую книгу.


Задание 3. Подготовка ведомостей назначения студентов на стипендию по результатам экзаменационной сессии (рис. 10)

Рис. 10. Форма стипендиальной ведомости

Алгоритм действий по технологии выполнения задания:

1. Загрузите экзаменационную ведомость.

2. На новом листе создайте ведомость стипендии (рис. 10) и скопируйте в нее список группы из экзаменационной ведомости.

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

4. Используя минимальное значение стипендии и учитывая, что сданы все экзамены, введите формулу начисления стипендии по условию:

• 
  если средний балл не менее 4,5, выплачивается 50%-ная надбавка к минимальной стипендии;
  
• 
  если средний балл от 3 (включительно) до 4,5, выплачивается минимальная стипендия;
  
• 
  если средний балл меньше 3, стипендия не выплачивается.
  

5. Подсчитайте сумму стипендиального фонда всей группы.

Последовательность действий

1. Загрузите рабочую книгу с именем Session.

2. Создайте в этой книге новый лист - Стипендия, на который из столбцов A и B листа Экзамен 1 скопируйте фамилии и порядковые номера студентов.

3. Оформите название и шапку ведомости назначения на стипендию согласно рис. 10.

4. Укажите размер минимальной стипендии в ячейке D3.

5. Вставьте два дополнительных столбца перед столбцом Стипендия и введите их названия - Средний балл и Кол-во сданных экзаменов. Сверьте полученное изображение электронной таблицы с рис. 11.

Рис. 11. Электронная таблица Ведомость назначения на стипендию

6. Введите формулу начисления среднего балла для первого студента (ячейка С6):

• 
  установите курсор в ячейке С6, вызовите Мастер функций и выберите в диалоговом окне параметры: Категория - Статистические, Имя - СРЗНАЧ; щелкните по кнопке ОК;
  
• 
  установите курсор в 1-й строке (имя Число 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;
  
• 
  установите курсор во 2-й строке (имя Число 2), щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
  
• 
  установите курсор в 3-й строке (имя Число 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по третьему экзамену;
  
• 
  щелкните по кнопке ОК; в ячейке С6 появится значение, рассчитанное по формуле: СРЗНАЧ('Экзамен 1'!D6;'Экзамен 1(2)!D6;' Экзамен 1(3)'!D6).
  

7. Скопируйте формулу по всем ячейкам столбца С:

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

8. Введите в столбец D формулу подсчета количества сданных каждым студентом экзаменов с учетом неявок. Технология ввода аналогична п. 6 (только в диалоговом окне выберите параметр Имя - СЧЕТ).

9. Скопируйте формулу по всем ячейкам столбца D (аналогично п. 7).

10. Введите формулу для вычисления размера стипендии студента в ячейку E6. Эта формула должна иметь следующий вид:

=ЕСЛИ(И(С6>=4,5;D6=3);$D$3*1,5;ЕСЛИ(И(С6>=3;D6=3);$D$3;0)).

Технология ввода формулы аналогична описанной в п. 6.

11. Скопируйте эту формулу в другие ячейки столбца E (аналогично п. 7).

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

13. Сохраните рабочую книгу.


Задание 4. Для таблицы на рис. 12 постройте два вида диаграмм - внедренную на лист с исходными данными и на отдельном листе

Рис. 12. Таблица успеваемости к заданию 4

Последовательность действий

1. Создайте новую рабочую книгу и сохраните ее под именем Diag.

2. Переименуйте Лист 1 на Успеваемость, используя контекстное меню.

3. Создайте таблицу согласно рис. 12, начиная с ячейки A1. В пустые ячейки с названием Факультет введите формулу вычисления среднего балла по факультету.

Для этого:

• 
  вызовите Мастер функций, выберите категорию функций Статистическая, имя функции - СРЗНАЧ; щелкните по кнопке ОК;
  
• 
  введите в первую строку диалогового окна адреса первой и последней ячеек столбца с оценками (B2:B5), используя для этого мышь;
  
• 
  скопируйте формулу в ячейку С6 столбца Высшая математика.
  

4. Для построения внедренной диаграммы нажмите кнопку Мастер диаграмм или выполните команду Вставка —› Диаграмма.

Этап 1. Выбор типа и формата диаграммы: на вкладке Стандартные выберите тип диаграммы Гистограмма и вид диаграммы - номер 1; щелкните по кнопке Далее.

Этап 2. Выбор и указание диапазона данных для построения диаграммы:

• 
  на вкладке Диапазон данных установите переключатель Ряды в столбцах и выделите диапазон данных A2:C6;
  
• 
  в том же диалоговом окне щелкните по вкладке Ряд, в строке с названием Ряд1, установите курсор в строке Имя и щелкните в ячейке В1 с названием Информатика;
  
• 
  в окне Ряд щелкните по названию Ряд2, установите курсор в строке Имя и щелкните в ячейке С1 с названием Высшая математика;
  
• 
  для задания подписей по оси X щелкните в строке Подписи оси X, выделите данные первого столбца таблицы (диапазон A2:A6) и щелкните по кнопке <Далее>.
  

Этап 3. Задание параметров диаграммы:

на вкладке Заголовки введите названия в соответствующих строках:

Название диаграммы:   Сведения об успеваемости Ось X:  Учебные группы Ось Y:  Средний балл

• 
  на вкладке Легенда поставьте флажок Добавить легенду и переключатель Справа;
  
• 
  щелкните по кнопке Далее.
  

Этап 4. Размещение диаграммы: установите переключатель Поместить диаграмму на имеющемся листе, выберите из списка лист Успеваемость и щелкните по кнопке Готово. В результате на рабочем листе будет создана внедренная диаграмма, представленная на рис. 13.

Сведения об успеваемости

Рис. 13. Диаграмма типа Гистограмма для задания 4

5. Постройте диаграмму другого типа на отдельном листе. Для этого выполните действия, аналогичные описанным в п. 4, но на четвертом шаге установите переключатель На отдельном листе.

Задание 5. Редактирование диаграммы

1. Скопируйте диаграмму (рис. 13) в другое место листа.

2. Добавьте в исходную таблицу столбец с оценками по философии.

3. Измените формат диаграммы на объемный.

4. Вставьте в диаграмму столбец с оценками по философии и измените диаграмму так, чтобы она отражала успеваемость (ось Y) каждой группы (ось Z) в зависимости от дисциплины (ось X).

5. Измените параметры диаграммы - названия осей, уберите легенду.

6. Разместите диаграмму на отдельном листе.

Последовательность действий

1. Скопируйте всю область диаграммы, используя буфер обмена.

2. Добавьте в исходную таблицу новый столбец Философия с различными оценками.

3. Измените формат диаграммы, сделав ее объемной:

• 
  установите курсор мыши во внутренней незаполненной области диаграммы и вызовите контекстное меню;
  
• 
  выполните команду Тип диаграммы, выберите на вкладке Стандартные тип Гистограмма, затем последний из представленных форматов (3-мерная гистограмма);
  
• 
  щелкните по кнопке ОК.
  

4. Вставьте в диаграмму столбцы, отражающие успеваемость по философии:

• 
  установите курсор мыши во внутренней незаполненной области диаграммы и вызовите контекстное меню;
  
• 
  выполните команду Исходные данные и измените параметры: во вкладке Диапазон данных укажите весь диапазон данных A2:D6, включив информацию столбца Философия; установите переключатель Ряды в положение столбцах; на вкладке Ряд введите имена (ряд1 - Информатика, ряд2 - Высшая математика, ряд3 - Философия) и щелкните по кнопке ОК.
  

5. Измените параметры диаграммы:

• 
  установите курсор мыши во внутренней незаполненной области диаграммы и вызовите контекстное меню;
  
• 
  выполните команду Параметры диаграммы и укажите на вкладке Заголовки:
  

Название диаграммы - без изменений Ось X: Учебные группы Ось Y: Дисциплины Ось Z: Средний балл

• 
  на вкладке Легенда уберите флажок Добавить легенду и щелкните по кнопке ОК.
  

6. Активизируйте контекстное меню диаграммы и выполните команду Размещение. Установите переключатель Поместить диаграмму на листе в положение отдельном и щелкните по кнопке <ОК>. Результат представлен на рис. 14.

Сведения об успеваемости

Рис. 14. Итоговый результат задания по редактированию диаграммы

Задание 6. Форматирование диаграммы

1. Измените настройку объемного вида трехмерной диаграммы (рис. 14).

2. Измените настройку области диаграммы и области построения диаграммы.

3. Измените форму представления данных на диаграмме: рядов данных и их элементов.

4. Измените отображение осей диаграммы.

5. Проведите форматирование сетки в области построения диаграммы.

6. На созданной диаграмме поместите новую легенду и проведите ее форматирование.

Последовательность действий

1. Измените настройки параметров диаграммы:

• 
  активизируйте внедренную диаграмму, щелкнув правой кнопкой мыши в пустой области диаграммы;
  
• 
  в появившемся меню выберите команду Объемный вид и в диалоговом окне установите следующие параметры:
  

Возвышение: 15 Поворот: 20 Изометрия: флажок Автомасштаб: флажок

• 
  нажмите кнопку ОК.
  

2. Проведите форматирование области диаграммы и области построения диаграммы:

• 
  активизируйте внедренную диаграмму и в появившемся меню выберите команду Формат области диаграммы: задайте на вкладках диалогового окна установки:
  

Вкладка Вид: Рамка - невидимая с тенью Заливка: голубой цвет Вкладка Шрифт: Шрифт Times New Roman Cyr Стиль: обычный Размер: 14

• 
  нажмите кнопку ОК;
  
• 
  в области построения диаграммы вызовите контекстное меню, а в нем команду Формат области построения;
  
• 
  задайте в диалоговом окне Вид установки:
  

 

Рамка: автоматическая Заливка: белый цвет

• 
  нажмите кнопку ОК.
  

3. Проведите форматирование рядов данных и их элементов:

• 
  установите указатель мыши на ряде 1, вызовите контекстное меню и выполните команду Формат рядов данных;
  
• 
  на вкладке Параметры произведите настройку:
  

Глубина зазора: 200 Ширина зазора: 170 Глубина диаграммы: 90

• 
  на вкладке Порядок данных в окне установите курсор на название Информатика и щелкните по кнопке Вверх. Закройте окно;
  
• 
  на вкладке Подписи данных установите переключатель Значения;
  
• 
  на вкладке Вид установите параметры:
  

Рамка: автоматическая Заливка: синий цвет

• 
  нажмите клавишу ОК;
  
• 
  повторите установку параметров на вкладке Вид для остальных рядов диаграммы: для ряда 2 - желтый, для ряда 3 - зеленый.
  

4. Проведите форматирование осей диаграммы:

• 
  на оси X вызовите контекстное меню, выполните команду Формат оси и установите параметры на вкладках:
  

Вкладка Вид: Метки делений - внизу, основные - наружу Вкладка Шкала: Число категорий между подписями делений - 1, число категорий между делениями - 1 Вкладка Выравнивание: 30 снизу вверх

• 
  выполните форматирование оси Y:
  

Вкладка Вид: Метки делений - внизу, основные - наружу Вкладка Шкала: Число категорий между подписями делений - 1, число категорий между делениями - 1 Вкладка Выравнивание: Авто

• 
  выполните форматирование оси Z:
  

Вкладка Вид: Метки делений - рядом с осью, основные - наружу Вкладка Шкала: минимальное значение - 0, максимальное значение - 5, цена основных делений - 0,5, цена промежуточных делений - 0,1, плоскость XY пересекает в значении 0 Вкладка Число: Числовые форматы: общий Вкладка Выравнивание: горизонтальное

 

Рис. 15. Итоговый результат задания по редактированию диаграммы.

5. Проведите форматирование сетки, стен и основания:

• 
  в одном из четырех углов диаграммы вызовите контекстное меню и выберите команду Параметры диаграммы. Во вкладке Линии сетки установите параметры:
  

Ось X: флажки - основные линии и промежуточные линии Ось Y: флажки - основные линии и промежуточные линии Ось Z: флажок - основные линии

• 
  в области стен диаграммы вызовите контекстное меню и выберите команду Формат стенок. Во вкладке Вид выберите светло-желтый цвет заливки;
  
• 
  в области основания диаграммы вызовите контекстное меню и выберите команду Формат основания. Во вкладке Вид выберите светло-желтый цвет заливки.
  

6. Проведите форматирование легенды:

• 
  в одном из четырех углов диаграммы вызовите контекстное меню и выберите команду Параметры диаграммы. Во вкладке Легенда поставьте флажок Добавить легенду и переключатель Справа;
  
• 
  в окне легенды вызовите контекстное меню, выполните команду Формат легенды и установите следующие параметры:
  

Вкладка Вид: рамка - обычная, заливка - светло-желтый цвет Вкладка Размещение: в верхнем правом углу

 

7. Сравните созданную вами диаграмму с образцом на рис. 15.


Задание 7. Построение тренда:

1. 
   Создайте таблицу, аналогичную табл. 3.
   
2. 
   Постройте гистограмму распределения оценок по информатике по группам.
   
3. 
   Постройте линейный тренд для гистограммы.
   
4. 
   Постройте полиномиальный тренд для гистограммы.
   
5. 
   Оформите диаграмму и линии тренда, как представлено на рис. 16.
   

Таблица 3

Последовательность действий

1. Создайте таблицу, представленную в табл. 3.

2. Постройте диаграмму распределения по группам оценок по информатике:

• 
  вызовите Мастер диаграмм, выберите на вкладке Стандартные обычный тип диаграммы и нажмите кнопку Далее;
  
• 
  установите курсор в строку Диапазон и выделите в таблице блок ячеек A2:B7;
  
• 
  оформите заголовки и названия осей так, как показано на рис. 16.
  

3. Постройте линейный тренд для диаграммы:

• 
  установите указатель мыши на один из столбиков гистограммы и щелкните левой кнопкой мыши так, чтобы появились на всех столбиках черные метки;
  
• 
  для выделенной гистограммы вызовите контекстное меню и выполните команду Добавить линию тренда;
  
• 
  в диалоговом окне <<Линия тренда>> на вкладке Тип выберите окно Линейная;
  
• 
  на вкладке Параметры установите параметры:
  

Прогноз: вперед на 1 период Показывать уравнение на диаграмме: установите флажок Поместить на диаграмму величину достоверности аппроксимации: установите флажок

• 
  нажмите кнопку ОК.
  

4. Постройте полиномиальный тренд для гистограммы по технологии п. 3.

5. Оформите диаграмму и линии тренда так, как представлено на рис. 16.

Рис. 16. Гистограмма и тренды

Задание 8. Сортировка данных:

1. 
   Создайте книгу и сохраните ее под именем Spisok, переименуйте Лист1 на Список, а Лист2 - на Сортировка.
   
2. 
   На листе Список создайте таблицу, приведенную в табл. 4.
   
3. 
   Произведите копирование списка (базы данных) с листа Список на лист Сортировка.
   
4. 
   Сделайте сортировку на трех уровнях по возрастанию: по преподавателям, по номеру группы, по коду предмета.
   

Последовательность действий

1. Проведите подготовительную работу в соответствии с п. 1 задания.

2. Сформируйте на листе Список шапку таблицы 4. Для этого:

• 
  выделите первую строку, вызовите контекстное меню и выберите команду Формат ячеек;
  
• 
  произведите форматирование ячеек первой строки, установив параметры на вкладке Выравнивание:
  

По горизонтали: по значению По вертикали: по верхнему краю Переносить по словам: установить флажок

 

• 
  введите названия столбцов (имен полей) в соответствии с таблицей 4;
  
• 
  заполните таблицу данными.
  

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

4. Выполните сортировку по столбцу Таб. № препод. Для этого:

• 
  установите курсор в поле списка и введите команду Данные —› Сортировка. При этом должна выделиться вся область списка. Если этого не произошло, то предварительно выделите весь список, а затем введите указанную команду;
  
• 
  в диалоговом окне <<Сортировка диапазона>> установите:
  

Сортировать по: поле Таб. № препод., по возрастанию Затем по: Номер группы, по возрастанию В последнюю очередь по: поле Код предмета, по возрастанию

• 
  установите флажок Идентифицировать поля по записям.
  

Таблица 4

 


Задание 9. Выборка данных из списка по критерию отбора, используя Автофильтр:

1. Переименуйте новый лист на Автофильтр и скопируйте на него исходную базу данных (табл. 4).
2. Выберите из списка данные, используя критерий:

• 
  для преподавателя а1 выбрать сведения о сдаче экзамена на положительную оценку;
  
• 
  вид занятий - лк.
  

3. Отмените результат автофильтрации.
4. Выберите из списка данные, используя критерий: для группы 1А получить сведения о сдаче экзамена по предмету п1 на оценки 3 и 4.
5. Отмените результат автофильтрации.

Последовательность действий

1. Переименуйте Лист3 на Автофильтр и скопируйте на него исходную базу данных.
2. Для выполнения п. 2 задания:

• 
  установите курсор в область списка и выполните команду Данные —› Фильтр —› Автофильтр (в каждом столбце появятся кнопки списка);
  
• 
  сформируйте условия отбора записей: в столбце Таб. № препод. из списка условий отбора выберите а1; в столбце Оценка из списка условий выберите Условие и в диалоговом окне сформируйте условие отбора >2;
  в столбце Вид занятия из списка условий выберите лк.
  

3. Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные —› Фильтр —› Автофильтр.
4. Выполните п. 4 задания, воспользуясь аналогичной п. 3 технологией фильтрации.
5. Отмените результат автофильтрации, установив указатель мыши в список и выполнив команду Данные —› Фильтр —› Автофильтр.


Задание 10. Выборка данных из списка, используя Расширенный фильтр, по Критерию сравнения и по Вычисляемому критерию:

1. Переименуйте новый лист на Расширенный лист и скопируйте на него исходную базу данных (табл. 4).
2. Скопируйте имена полей списка в другую область на том же листе.
3. Сформируйте в области условий отбора Критерий сравнения - о сдаче экзаменов студентами группы 1А по предмету п1 на оценки 4 и 5.
4. Произведите фильтрацию записей на том же листе.
5. Сформируйте в области условий отбора Вычисляемый критерий: для каждого преподавателя выбрать сведения о сдаче студентами экзамена на оценку выше средней, вид занятия - лк.
6. Произведите фильтрацию записей на том же листе.

Последовательность действий

1. Переименуйте Лист4 на Расширенный лист и скопируйте на него исходную базу данных.
2. Скопируйте все имена полей списка в другую область (область формирования условий отбора записей) на том же листе, например установив курсор в ячейку J1.
3. Сформируйте в области условий отбора Критерий сравнения в соответствии с п. 3 задания. Для этого после имен полей введите в столбец Номер группы - 1А, в столбец Код предмета - п1, в столбец Оценка - условие - >3.
4. Произведите фильтрацию записей расширенным фильтром на том же листе:

• 
  установите курсор в область списка (базы данных);
  
• 
  выполните команду Данные —› Фильтр —› Расширенный фильтр;
  
• 
  в диалоговом окне Расширенный фильтр задайте параметры:
  
• 
  Скопировать результат в другое место: установить флажокИсходный диапазон: A1:G17Диапазон условия: J1:P2Поместить результат в диапазон: J4
  
• 
  нажмите кнопку ОК.
  

5. Сформируйте в области условий отбора Вычисляемый критерий в соответствии с п. 3 задания. Для этого:

• 
  в столбец Вид занятия введите лк;
  
• 
  переименуйте в области критерия столбец Оценка, например, на имя Оценка 1;
  
• 
  в столбец Оценка 1 введите вычисляемый критерий вида
  =G2>СРЗНАЧ($G$2:$G$17).
  

6. Произведите фильтрацию записей расширенным фильтром на том же листе аналогично п. 4.

Задание 11. Выборка данных из списка с использованием Формы:

1. Переименуйте новый лист на Форма и скопируйте на него исходную базу данных (табл. 4).
2. Просмотрите записи списка с помощью формы и добавьте две новые.
3. Сформируйте условие отбора с помощью формы данных: для преподавателя а1 выбрать сведения о сдаче студентами экзамена на положительную оценку, вид занятий - лк.
4. Просмотрите отобранные записи.

Последовательность действий

1. Переименуйте Лист5 на Расширенный лист и скопируйте на него исходную базу данных. Установите курсор в область списка и выполните команду Данные —› Форма.
2. Просмотрите записи списка и внесите необходимые изменения с помощью кнопок Назад и Далее. С помощью кнопки Добавить добавьте две новые записи.
3. Сформируйте условие отбора в соответствии с заданием:

• 
  нажмите кнопку Критерии, название которой поменяется на Правка;
  
• 
  в пустых строках имен списка введите критерии: в строку Таб. № препод. введите а1, в строку Вид занятия - лк, в строку Оценка - условие >2.
  

4. Просмотрите отобранные записи, нажимая на кнопку Назад или Далее.

Задание 12. Структурирование таблицы ручным способом:

1. Откройте книгу с таблицей, отображенной в табл. 4, переименуйте новый лист на Структура и скопируйте на него исходную базу данных.
2. Отсортируйте строки списка по номеру учебной группы.
3. Вставьте пустые разделяющие строки между учебными группами.
4. Создайте структурные части таблицы для учебных групп.
5. Создайте структурную часть таблицы для столбцов: Код предмета, Таб. № препод., Вид занятия.
6. Закройте и откройте структурные части таблицы.
7. Отмените структурирование.

Последовательность действий

1. Откройте книгу с именем Spisok, переименуйте Лист6 на Структура и скопируйте на него исходную базу данных.
2. Отсортируйте строки списка по номеру учебной группы (команда Данные —› Сортировка).
3. Вставьте пустые разделяющие строки между учебными группами:

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

4. Создайте структурные части таблицы для учебных групп:

• 
  выделите блок строк, относящихся к первой группе;
  
• 
  выполните команду Данные —› Группа и структура —› Группировать. В появившемся окне установите флажок строки;
  

аналогичные действия повторите для других групп.

5. Создайте структурную часть таблицы для столбцов Код предмета, Таб. № препод., Вид занятия аналогично п. 4 (в появившемся окне установите флажок столбцы).
6. Закройте и откройте созданные структурные части таблицы, нажимая на кнопки Минус или Плюс.
7. Отмените структурирование командой Данные —› Группа и структура —› Разгруппировать.

Задание 13. Автоструктурирование таблицы и введение дополнительного иерархического уровня структуры ручным способом:

1. Откройте книгу с именем Spisok, вставьте и назовите новый рабочий лист.
2. Создайте таблицу расчета заработной платы (табл. 5), в которой:
- в столбцы Фамилия, Зарплата, Надбавка, Премия надо ввести константы;
- в строке Итого подсчитываются суммы по каждому столбцу;
- в остальные столбцы надо ввести формулы:

• 
  Подоходный налог = 0,12*Зар.плата
  
• 
  Пенсионный фонд = 0,01*Зар.плата
  
• 
  Общий налог = Подоходный налог + Пенсионный фонд
  
• 
  Итого доплат = Надбавка + Премия
  
• 
  Сумма к выдаче = Зар.плата - Общий налог + Итого доплат
  

Таблица 5
Пример исходной таблицы для автоструктурирования

3. Создайте автоструктуру таблицы расчета заработной платы.
4. Ознакомьтесь с разными видами таблиц, нажимая на кнопки иерархических уровней и кнопки со знаками плюс и минус.
5. Введите в структурированную таблицу дополнительный иерархический уровень по строкам.

Последовательность действий

1. Откройте книгу с именем Spisok, вставьте новый рабочий лист и назовите его - Зар.плата.
2. Создайте таблицу согласно табл. 5. Введите формулы в ячейки в соответствии с п. 2 задания, используя метод автозаполнения. Проведите сортировку в списке по фамилиям.
3. Создайте автоструктуру таблицы расчета заработной платы: установите курсор в любую ячейку области данных и выполните команду Данные —› Группа и структура —› Создать структуру.
4. Ознакомьтесь с разными видами таблиц, нажимая на кнопки иерархических уровней и на кнопки со знаками плюс и минус.
5. Введите в структурированную таблицу дополнительный иерархический уровень по строкам, разделив весь список фамилий на группы по две фамилии. Для этого:

• 
  вставьте пустую строку после первых двух фамилий: выделите третью строку и в контекстном меню выберите команду Добавить ячейки;
  
• 
  аналогично вставьте пустую строку перед строкой Итого;
  
• 
  выделите строки с первыми двумя фамилиями, вызовите контекстное меню и выполните команду Данные —› Группа и структура —› Группировать;
  
• 
  выделите строки с остальными фамилиями, вызовите контекстное меню и выполните команду Данные —› Группа и структура —› Группировать.
  

Задание 14. Структурирование таблицы с автоматическим подведением итогов по группам таблицы, представленной в табл. 4:

1. Откройте книгу с таблицей, отображенной в табл. 4, назовите новый лист Итоги и скопируйте на него исходную базу данных.
2. Отсортируйте записи списка по номеру группы, коду предмета, виду занятий.
3. Создайте 1-й уровень итогов - средний балл по каждой учебной группе.
4. Создайте 2-й уровень итогов - средний балл по каждому предмету для каждой учебной группы.
5. Создайте 3-й уровень итогов - средний балл по каждому виду занятий для каждого предмета по всем учебным группам.
6. Просмотрите элементы структуры, закройте и откройте иерархические уровни.

Последовательность действий

1. Откройте книгу Spisok, назовите новый лист Итоги и скопируйте на него исходную базу данных.
2. Отсортируйте список записей с помощью команды Данные —› Сортировка, выбрав в старшем ключе номер группы, в промежуточном - код предмета, в младшем - вид занятий. Установите флажок Идентифицировать поля по подписям.
3. Создайте 1-й уровень итогов - средний балл по каждой учебной группе:

• 
  установите курсор в произвольную ячейку списка и выполните команду Данные —› Итоги;
  
• 
  в диалоговом окне Промежуточные итоги укажите:
  

При каждом изменении в - Номер группы Операция: Среднее Добавить итоги по: Оценка Заменять текущие итоги: нет Конец страницы между группами: нет Итоги под данными: да

4. Аналогично п. 3 создайте 2-й уровень итогов - средний балл по каждому предмету для каждой учебной группы.
5. Аналогично п. 3 создайте 3-й уровень итогов - средний балл по каждому виду занятий для каждого предмета по всем учебным группам.
6. Просмотрите элементы структуры, закройте и откройте иерархические уровни, используя кнопки с минусом и плюсом.

Задание 15. Для таблицы 4 постройте следующие виды сводных таблиц:

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

Последовательность действий

1. Откройте книгу Spisok, переименуйте новый лист на Итоги и скопируйте на него исходную базу данных.
2. Создайте сводную таблицу с помощью Мастера сводных таблиц по шагам (команда Данные —› Сводная таблица):

этап 1 (выбор источника данных ) - щелкните по кнопке <в списке или базе данных Excel> и по кнопке <Далее>;
этап 2 - отображение в строке Диапазон блока ячеек списка (базы данных). Если диапазон указан неверно, то его надо стереть и указать нужный блок ячеек;
этап 3 - построение макета сводной таблицы для п. 1 задания. Технология его построения состоит в следующем:

• 
  перетащите элемент с именем поля, находящегося в правой стороне макета, в одну из областей: № группы - в Страницу; № зач. кн. -
  в Строку, Таб. № преп. и Вид занятия - в Столбец, Оценку - в Данные,
  
• 
  в области Данные два раза щелкните левой кнопкой мыши и в диалоговом окне Вычисление поля сводной таблицы выберите соответствующую операцию над значением поля;
  

этап 4 - выбор места расположения: существующий лист.

3. Выполните автоформатирование полученной сводной таблицы (команда Формат —› Автоформат).
4. Внесите изменения в исходные данные и выполните команду Данные —› Обновить данные.
5. Аналогично постройте сводную таблицу для п. 2 задания.

Задание 16. Консолидация данных по расположению и по категориям:

1. Откройте книгу Spisok, вставьте два листа и переименуйте их, присвоив им имена Консол.распол. и Консол.катег.
2. Создайте на листе Консол.распол. таблицу расчета заработной платы за январь (табл. 5).
3. Скопируйте созданную таблицу на тот же лист и измените в ней данные. Эта таблица будет отражать уровень заработной платы за февраль (табл. 5).
4. Выполните консолидацию данных по расположению.
5. Скопируйте обе таблицы с листа Консол.распол. на лист Консол.катег. и измените вторую таблицу в соответствии с табл. 6.
6. Выполните консолидацию данных по категориям.

Таблица 5

Таблица 6
Исходные таблицы для консолидации данных по категориям
(первая таблица не изменяется)

Последовательность действий

1. Выполните п. 1 задания.
2. Выполните п. 2 задания.
3. Выполните п. 3 задания.
4. Выполните консолидацию данных по расположению:

• 
  установите курсор в первую ячейку области, где будет располагаться консолидированная таблица, например в ячейку A11;
  
• 
  выполните команду Данные —› Консолидация;
  
• 
  в диалоговом окне Консолидация выберите из списка функцию Сумма и установите флажки подписи верхней строки, значения левого столбца;
  
• 
  установите курсор в окне Ссылка, выделите блок ячеек A2:D7 и нажмите кнопку Добавить;
  
• 
  установите курсор в окне Ссылка, выделите блок ячеек F2:I7 и нажмите кнопку Добавить;
  
• 
  нажмите кнопку ОК;
  

5. Выполните п. 5 задания.
6. Аналогично п. 4 выполните консолидацию данных по категориям.