Порядок работы

1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.

2. Создайте таблицу «Экзаменационная ведомость» в соответствии с рис. 4.4.

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

4 . После списка студентов в нижней части таблицы согласно рис. 4.4. произведите расчет итоговых строк: Отлично, Хорошо, Удовлетворительно, Неудовлетворительно, Неявка, ИТОГО. Для расчета используется функция СЧЕТЕСЛИ (категория – логические).

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

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

• установите курсор на имени текущего листа и вызовите контекстное меню;

• выберите параметр Переименовать и введите новое имя, например Экзамен 1.

6. Скопируйте текущий лист Экзамен 1 - 2 раза. Переименуйте листы в Экзамен 2 и Экзамен 3. Измените данные в ведомостях: название дисциплины, оценки и подпись преподавателя.

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

Порядок работы

1. Создайте новый лист  Стипендия.

2. Введите тексты заголовка и шапки таблицы в соответствии с рис. 4.6. Укажите размер минимальной стипендии в ячейке D3;

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

• установите курсор в ячейке С6;

• щелкните по кнопке <Мастер функций> на панели Стандартная и выберите в диалоговом окне параметры:

  Категория: Статистические Имя: СРЗНАЧ

• щелкните по кнопке <ОК>, появится панель ввода аргументов функции СРЗНАЧ;

• установите курсор в 1-й строке (имя Число 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;

Рисунок 4.6 – Электронная таблица «Ведомость назначения на стипендию»

• установите курсор во 2-й строке (имя Число 2), щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экзамену;

• установите курсор в 3-й строке (имя Число 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по второму экзамену;

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

• в ячейке С6 появится значение, рассчитанное по формуле:

=СРЗНАЧ(’Экзамен 1’!D6;’Экэамен 1(2)’!D6;’Экзамен 1(3)’!D6).

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

4. Введите в столбец D формулу подсчета количества сданных каждым студентом экзаменов с учетом неявок. При этом технология ввода будет аналогична описанной в п.3:

• установите курсор в ячейке D6;

• щелкните по кнопке <Мастер функций> на панели Стандартная и выберите в диалоговом окне параметры:

  Категория: Статистические Имя: СЧЕТ

• щелкните по кнопке <ОК>, появится панель ввода аргументов функции СЧЕТ;

• установите курсор в 1-й строке (имя Значение 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;

• установите курсор во 2-й строке (имя Значение 2), щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экзамену;

• установите курсор в 3-й строке (имя Значение 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по третьему экзамену;

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

• в ячейке D6 появится значение, рассчитанное по формуле:

=СЧЕТ(‘Экзамен 1‘!D6;‘Экзамен 1(2)‘!D6;‘Экзамен 1(3)‘!D6).

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

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

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

7. Технология ввода формулы будет аналогична описанной в п. 3 и 4:

• установите курсор в ячейке Е6;

• щелкните по кнопке <Мастер функций> на панели Стандартная и выберите в диалоговом окне параметры:

Категория: Логические Имя: ЕСЛИ

• щелкните по кнопке <ОК>, появится панель ввода аргументов функции ЕСЛИ;

• курсор будет находиться в 1-й строке (имя  Логическое выражение) панели ввода аргументов функции;

• нажмите кнопку вызова функции в строке ввода, выберите категорию Другие функции и функцию И, нажмите кнопку <ОК>;

• появится второе окно ввода аргументов функции И, курсор автоматически будет установлен в строке Логическое 1;

• щелкните в ячейке С6, где показан средний балл этого студента, и наберите с клавиатуры условие >=4,5. В результате в этой строке должно быть выражение:

• С6>=4,5

• установите курсор на второй строке Логическое выражение 2 и аналогично сформируйте выражение, которое указывает необходимое количество сданных экзаменов (в данном примере это число 3):

• D6=3

• щелкните по кнопке <ОК>. В результате в строке ввода должно появиться выражение:

• =ЕСЛИ(И(С6>=4,5;D6=3)

• щелкните мышью на кнопке f_x строки ввода, появится первое окно ввода аргументов для функции ЕСЛИ;

• установите курсор во 2-й строке (имя  Значение_ если истина), щелкните в ячейке D3 и нажмите клавишу <F4>. Появится символ $ перед именем столбца и номером строки. Введите выражение *1,5. В результате в этой строке будет выражение:

• $D$3*1,5

• установите курсор в 3-й строке (имя Значение_ если ложь) и по аналогичной технологии введите оставшуюся часть формулы

• ЕСЛИ(И(С6>=3;D6=3);$D$3;0)

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

8. Скопируйте эту формулу в другие ячейки столбца Е.

9. Рассчитайте «Итого стипендиальный фонд по группе» с помощью <Мастера функций>.

10. Проверьте работоспособность таблицы:

• введите другие оценки в экзаменационные ведомости;

• измените минимальный размер стипендии.

1. Сохраните рабочую книгу командой Файл, Сохранить.

2. Закройте рабочую книгу командой Файл, Закрыть.

Краткая справка:

1. В структуре формулы имеются вложенные функции И(...), ЕСЛИ(...). Для ввода этих функций надо воспользоваться кнопкой вызова функции (см. рис. 4.2), находящейся в строке ввода под панелями.

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

3. В процессе набора формулы постоянно сравнивайте ее с выражением, которое приведено в этом пункте выше.

4. В числах для отделения целой части от дробной используется либо точка, либо запятая, что зависит от установок Excel.

5. Если после ввода формулы появится синтаксическая ошибка, то следует проверить количество скобок, наличие разделителя (точки с запятой), заменить в числе точку на запятую или наоборот.

Дополнительные задания

Задание 1. Заполнить таблицу 4.1, определить, кто из студентов допущен к экзамену, используя логическую функцию =ЕСЛИ().

Таблица 4.1 – Исходные данные для дополнительного задания

ФИО	ТГП	КП	ИГП	АП	Средний балл	Допуск
Петров	5	5	5	5	?	?
Иванов	5	4	5	4	?	?
Сидоров	3	5	4	5	?	?
Пашин	4	2	4	3	?	?
Семенов	3	4	2	2	?	?
Итого допущено:						?

Задание 2. Заполнить таблицу 4.2, рассчитать НДС (если цена до 8000 руб., то НДС – 10%, если цена от 8000 до 10000 руб., то НДС – 15%, если цена больше 10000 руб., то НДС 20%). Подсчитать количество товаров, у которых цена с учетом НДС превышает 10000 руб.

Биржевой курс 1$=28,35 руб.

Таблица 4.2 – Исходные данные для дополнительного задания

Товар	Цена в $	Цена в руб.	НДС	Цена с учетом НДС
Телевизор	410	?	?	?
Магнитофон	195	?	?	?
Холодильник	625	?	?	?
Комбайн	156	?	?	?
Пылесос	174	?	?	?
Компьютер	601	?	?	?
Микроволновая печь	190	?	?	?

Задание 3. Заполнить таблицу 4.3, рассчитать сумму возврата кредита при условии: если дата возврата фактическая не превышает договорную, то сумма возврата увеличивается на 40% от суммы кредита , в противном случае сумма возврата увеличивается на 40% плюс 1 % за каждый просроченный день.

Таблица 4.3 – Исходные данные для дополнительного задания

Наименование организации	Дата получения кредита	Сумма кредита (млн. руб.)	Дата возврата (по договору)	Дата возврата (фактическая)	Сумма возврата (млн. руб.)
АО «Роника»	05.12.98	200	04.03.99	22.02.99	?
СП «Изотоп»	25.01.99	500	24.04.99	15.05.99	?
ООО «Термо»	03.02.99	100	02.06.99	22.06.99	?
АОЗТ «Чипы»	21.11.98	300	20.05.99	18.05.99	?
АО «Медицина»	12.05.99	50	11.07.99	20.09.99	?
АО «Колос»	08.04.99	150	07.10.99	12.10.99	?

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

1-1,5%;2-7%; 3-10%; 4-15%; 5-20%.

Для расчета использовать функцию ПРОСМОТР (или ЕСЛИ), а для определения последней цифры номенклатурного номера функцию ОСТАТ.

Таблица 4.4 – Исходные данные для дополнительного задания

Но- менк- латур- ныи номер	Наимено- вание про- дукции	Коли- чество (шт.)	Цена (тыс. руб.)	Сумма (тыс. руб.)	% скид- ки	Сумма скидки (тыс. руб.)	Стои- мость с учетом скидки (тыс.руб.
202	Монитор	5	12	?	?	?	?
201	Клавиатура	25	0,25	?	?	?	?
403	Дискета	100	0,02	?	?	?	?
105	Принтер	2	10	?	?	?	?
204	Сканер	1	8	?	?	?	?