Материал: Решение задач в области техносферной безопасности с использованием пакетов прикладных программ. Сазонова С.А., Колодяжный С.А

Рис.6.2. Иллюстрация правила изменения ссылок

при копировании формул из одной ячейки в другую

Рис. 6.3. Диалоговое окно «Мастер функций» для выбора категории и вида функции

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

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

После нажатия кнопки <ОК> появляется следующее диалоговое окно (пример окна приведен на рис. 6.4) и осуществляется построение функции, т.е. указание ее аргументов. Каждый аргумент вводится в специально предназначенную для него строку, например, так, как показано на рис. 6.4.

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

Правила построения формул с помощью Мастера функций:

состав аргументов функций, порядок задания и типы значений фиксированы и не подлежат изменению;

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

для формирования аргумента как результата промежуточного вычисления по функциям нажимается кнопка вызова функций в строке ввода (рис. 6.5); глубина вложенности - произвольная;

для ввода имени блока ячеек используется команда Вставка, Имя, Вставить с выбором имени блока;

для построения ссылки следует установить курсор в поле ввода, а затем перевести указатель мыши на требуемый рабочий лист для выделения ячейки или блока;

абсолютные ссылки формируются при установке курсора перед адресом ячейки в строке ввода и нажатии клавиши <F4>.

Рис. 6.4. Пример диалогового окна для задания аргументов логической функции ЕСЛИ

Рис. 6.5. Использование кнопки вызова функции в строке ввода

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

В качестве примера таблицы рассматривается экзаменационная ведомость (рис. 6.6).

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

Рис. 6.6. Форма экзаменационной ведомости для задания 6.1

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

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

задайте способ выравнивания названия граф (при больших текстах необходимо обеспечить перенос по словам);

в каждую ячейку одной строки введите названия граф таблицы;

установите ширину каждого столбца таблицы.

После окончания оформления шапки таблицы введите в таблицу постоянные данные:

фамилии студентов и полученные ими оценки по конкретной дисциплине;

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

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

Последовательность выполнения задания 6.1.

1. Загрузите ранее созданный в лабораторной работе № 5 "Настройка новой рабочей книги" шаблон экзаменационной ведомости с именем Session:

• выполните команду Файл, Открыть;

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

папка: имя вашего каталога;

имя файла: Session;

тип файла: Шаблоны.

2. Введите в указанные в табл. 6.1 ячейки тексты заголовка и шапки таблицы в соответствии с рис. 6.6 по следующей технологии:

• установите указатель мыши в ячейку, куда будете вводить текст, например ячейку В1, и щелкните левой кнопкой, появится рамка;

• введите текст (см. табл. 6.1) и нажмите клавишу ввода <Enter>;

• переместите указатель мыши в следующую ячейку, например в ячейку А3, и щелкните левой кнопкой;

• введите текст, нажмите клавишу ввода <Enter> и т.д.

3. Отформатируйте ячейки А1:Е1:

• выделите блок ячеек, нажмите правую кнопку мыши для вызова контекстного меню;

• введите команду контекстного меню Формат ячеек;

• на вкладке Выравнивание выберите опции:

по горизонтали: по центру выделения;

по вертикали: по верхнему краю;

• нажав кнопку <Размер>, выберите размер шрифта, например 14 пт;

• выделите текст жирным шрифтом.

Таблица 6.1

Содержимое ячеек в которых располагаются название таблицы и ее шапки

4. Проделайте подготовительную работу для формирования шапки таблицы, задав параметры выравнивания вводимого текста:

• выделите блок ячеек A3 - J5, где располагается шапка таблицы;

• вызовите контекстное меню и выберите команду Формат ячеек;

• на вкладке Выравнивание задайте параметры:

по горизонтали: по значению;

по вертикали: по верхнему краю;

переносить по словам: поставить флажок;

ориентация: горизонтальный текст (по умолчанию);

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

5. Установите ширину столбцов таблицы в соответствии с рис. 6.6. Для этого:

• подведите указатель мыши к правой черте клетки с именем столбца, например В, так, чтобы указатель изменил свое изображение;

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

• аналогичные действия проделайте со столбцами А, С, D, Е, F-J.

6. Заполните ячейки столбца В данными о студентах учебной группы, приблизительно 10-15 строк. Отформатируйте данные.

7. Присвойте каждому студенту порядковый номер:

• введите в ячейку А6 число 1;

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

8. После списка студентов в нижней части таблицы согласно рис. 6.6 введите в ячейки столбца А текст итоговых строк; Отлично, Хорошо, Удовлетворительно, Неудовлетворительно, Неявка, ИТОГО.

9. Объедините две соседние ячейки для более удобного представления текста итоговых строк. Технологию объединения покажем на примере объединения двух ячеек столбцов А и В, в которых будет расположена надпись Отлично:

• выделите две ячейки;

• вызовите контекстное меню и выберите команду Формат ячеек;

• на вкладке Выравнивание установите флажок Объединение ячеек и нажмите кнопку <ОК>;

• аналогичные действия проделайте с остальными ячейками, где хранятся названия итоговых ячеек.

10. Сохраните рабочую книгу, для которой файл будет иметь тип xls:

• выполните команду Файл, Сохранить как;

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

папка: имя вашего каталога;

имя файла: Session;

тип файла: Книга Microsoft Excel.

Задание 6.2. Работа с формулами на примере подсчета количества разных оценок в группе по экзаменационной ведомости.

В созданной в задании 6.1 рабочей книге с экзаменационной ведомостью (см. рис. 6.6), хранящейся в файле с именем Session, рассчитайте;

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

• общее количество полученных оценок.

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

1. Ввести дополнительное количество столбцов, по одному на каждый вид оценки (всего 5 столбцов).

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

в столбце пятерок - если студент получил 5, то отображается 1, иначе - 0;

в столбце четверок - если студент получил 4, то отображается 1, иначе - 0;

в столбце троек - если студент получил 3, то отображается 1, иначе - 0;

в столбце двоек - если студент получил 2, то отображается 1, иначе - 0;

в столбце неявок - если не явился на экзамен, то отображается 1, иначе - 0.

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

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

4. Сверить полученные общий вид таблицы, результаты и структуры формул с тем, что показано на рис. 6.7 (в режиме отображения значений) и на рис. 6.8 (в режиме показа формул).

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

При выполнении задания 6.2 постоянно сравнивайте ваши результаты на экране с изображением на рис. 6.7.

Последовательность выполнения задания 6.2

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

• выполните команду Файл, Открыть;

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

папка: имя вашего каталога;

имя файла: Session;

тип файла: Книга Microsoft Excel.

2. Проделайте подготовительную работу, вводя названия (5, 4, 3, 2, неявки) соответственно в ячейки F3, G3, Н3, I3, J3 вспомогательных столбцов (рис. 6.8).

3. В эти столбцы F - J введите вспомогательные формулы (см. ниже). Суть формулы состоит в том, что вид оценки фиксируется напротив фамилии студента в ячейке соответствующего вспомогательного столбца как 1.

Рис. 6.7. Электронная таблица Экзаменационная ведомость в режиме отображения значений

Рис. 6.8. Электронная таблица Экзаменационная ведомость в режиме отображения формул

Рассмотрим пример. Студент Снегирев получил оценку 5, тогда в ячейке F5 должна стоять 1, а в остальных вспомогательных столбцах G-J в данной строке - 0.

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

• установите курсор в ячейку F5 и выберите мышью на панели инструментов кнопку Мастера функций;

• в 1-м диалоговом окне выберите вид функции:

Категория - логические;

Имя функции - ЕСЛИ;

• щелкните по кнопке<ОК>;

• во 2-м диалоговом окне, устанавливая курсор в каждой строке, введите соответствующие операнды логической функции: логическое выражение - D5; значение, если истина, - 1; значение, если ложно, - 0;

• щелкните по кнопке <ОК>.

4. С помощью Мастера функции введите формулы аналогичным способом в остальные ячейки данной строки. В результате в ячейках F6 - J6 должно быть следующее содержание:

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

• выделите блок ячеек F6 - J6;

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

• выберите в контекстном меню команду Заполнить значения.