Вычисление количества месяцев с курсом доллара ниже среднего

3.8.1. Вычисление количества месяцев с курсом доллара ниже среднего значения за 2006 г.

Используем функцию СЧЕТЕСЛИ (которая имеет синтаксис:

=СЧЕТЕСЛИ(Диапазон; Критерий).

Она подсчитывает число значений, удовлетворяющих заданному критерию. Так как нас интересуют месяцы, когда курс доллара ниже среднего, анализируется интервал (диапазон) за 2006 год (т.е. В6:В17). Среднее значение курса валюты за 2006 г. подсчитано в ячейке В21 и равно 30,8. Функция имеет вид:

=СЧЕТЕСЛИ(В6:В17;”<30,8”).

Критерий берется в кавычки как текст.

Введите формулу, для этого:

· щелкните по В22, активизируя эту ячейку;

· щелкните по пиктограмме fx либо выполните команды Вставка – Функция ( меню Формулы – Вставить функцию), открывается первое окно Мастера функций;

· в списке Категории выберите Статистические;

· в списке статистических функций выберите функцию СЧЕТЕСЛИ. Откроется окно второго шага Мастера функций СЧЕТЕСЛИ (рис. 21);

Рис. 21. Ввод функции СЧЕТЕСЛИ в Excel

 

 

· В этом окне есть два поля для ввода: Диапазон и Условие.

· В поле Диапазон введите В6:В17 (обведите эти ячейки указателем мыши);

· щелчком мыши активизируйте окно Условие;

· в поле Условие введите <30,8 (без кавычек, их поставит сам Мастер функций);

· щелкните по ОК.

В ячейку В22 введена формула

=СЧЕТЕСЛИ(В6:В17;”<30,8”).

3.8.2. Вычисление числа месяцев с курсом доллара ниже среднего в 2007 и 2008 гг.

Ввести аналогичные формулы в ячейки С22:D22 (см. табл. 16).

 

 

 

3.8.3. Вычисление количества месяцев с курсом доллара ниже среднего значения за три года.

В ячейку Е22 ввести формулу =СУММ(В22:D22).

 

Вычисление количества месяцев с курсом доллара выше среднего

 

3.9.1. Ввод формулы для 2006 г.

· активизируйте ячейку В23;

· щелкните по пиктограмме fx, откроется первое окно Мастера функций;

· в левом списке Критерии выберите Статистические;

· в открывшемся правом списке функций выберите функцию СЧЕТЕСЛИ. Откроется окно второго шага Мастера функций;

· в поле Диапазон введите В6:В17;

· щелчком мыши активизируйте поле Условие и введите условие >30,8;

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

· В ячейку В23 введена формула =СЧЕТЕСЛИ(В6:В17;”>30,8”).

Ввести аналогично формулы в ячейки С23:D23 (см. табл. 16).

3.9.2. Подсчет числа месяцев с курсом выше среднего за три года

В ячейку Е23 вводим формулу =СУММ(В23:D23).

 

Просмотр таблицы в режиме показа формул

Выполните команды:

· Сервис - Параметры. Открывается окно Параметры;

· в окне Параметры поставьте флажок (щелчком мыши) перед командой Формулы;

· щелкните по ОК.

При работе с Excel 2007 выбрать меню Формулы, далее Зависимости

формул – Показать формулы.

 

Для возврата в режим вычислений выполним команды:

· Сервис - Параметры;

· снять флажок Формулы (щелчком мыши);

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

При работе с Excel 2007 снимите флажок Показать формулы.

 

3 .11. Сохранение таблицы

Выполните команды:

· Файл - Сохранить как…( кнопка Office - Сохранить как…);

· в окне Сохранение документа в поле Папка находим Мои документы, а в поле Имя файла введите “Курс валюты”;

· щелкните по Сохранить.

 

Печать таблицы

Выполните команды:

· Файл - Параметры страницы;

· в окне Параметры страницы во вкладке Страница выберите ориентацию Книжная;

· во вкладке Лист установите щелчком мыши флажки перед командами Сетка и Имена строк и столбцов;

· щелкните по ОК - закрывается окно Параметры страницы;

· Файл – Печать - ОК.

 

При работе с Excel 2007 выбрать меню Разметка страницы, вкладка Параметры листа, и поставить флажки Сетки – Вид, Заголовки – Вид.

Для печати таблицы выполнить команды Кнопка Office – Печать.

Построение диаграмм

Построить диаграмму курса валюты в 2006, 2007 и 2008 гг (выделить диапазон ячеек А5:D17 и выбрать тип График с маркерами).

 

Отчет по работе

Распечатка табл. 15 и диаграммы.

 

Литература: [3], с. 30 -35

 

 

Работа 6. Объединение данных нескольких таблиц

 

Цель работы

 

Научиться объединять данные нескольких таблиц с помощью формул.

 

Основные теоретические положения

 

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

Порядок выполнения работы

 

Задание 1. Создать таблицу: “Экзаменационная ведомость” (табл. 17, 18).

Учет результатов экзаменационной сессии ведется с использованием электронных ведомостей. Типовые ведомости по каждой дисциплине создают для группы и содержат список студентов (фамилия, имя, отчество, № зачетной книжки). В каждой ведомости вычисляется число оценок (2, 3, 4, 5) в группе, а также число неявок (н/я).

Задание 2. Создать таблицу ”Ведомость назначения на стипендию” (табл. 19). Требуется подготовить для каждой группы три экзаменационные ведомости по предметам.

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

· если средний балл не менее 4,5, выплачивается 50 %-я надбавка к минимальной стипендии;

· если средний балл от 3,5 до 4,5, выплачивается минимальная стипендия;

· если средний балл меньше 3,5 - стипендия не выплачивается.

3.1. Выполнение задания 1

Создание таблицы “Экзаменационная ведомость”

 

3.1.1. Ввод числовых и текстовых констант

Введите текст согласно табл. 17. Заполните строки 1¸6 и столбцы А, В и D (в режиме показа формул эта таблица представлена в табл. 18).

 

3.1.2. Подсчет оценок каждого студента

В столбцах F¸J осуществляется подсчет оценок каждого студента. Если студент имеет оценку 5, то в столбце F у него должна стоять единица, в противном случае - 0. Для этого удобно использовать функцию ЕСЛИ. В общем случае она имеет вид

=ЕСЛИ(условие;выражение1;выражение2)

и работает так. Проверяется условие, записанное как первый аргумент функции. Если оно выполняется, в ту ячейку, где находится функция ЕСЛИ, записывается выражение 1. Если не выполняется – в эту ячейку записывается выражение 2.

а) Заполните столбец F (столбец пятерок). В ячейку F8 с помощью Мастера функций введите формулу =ЕСЛИ($D8=5;1;0).

Таблица 17

	A	B	C	D	E	F	G	H	I	J
	ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ
	Группа №		Дисциплина
	№ пп	Фамилия, имя, отчество	№ зач. книжки	Оценка	Подпись экзаменатора					н/я
		Снегирев П.П.
		Орлов К.Н.
		Воробьева В.Л.
		Голубкина О.М.
		Дятлов В.А.
		Кукушкин М.И.
	Суммарное количество оценок в группе
	отлично
	хорошо
	удов
	неуд
	неявки
	И Т О Г О

Для этого:

· Щелкните по пиктограмме fх Мастера функций (при работе с пакетом

MS Office 2007 команды:меню Формулы – Вставить функцию);

· в списке Категории выберите Логические;

· в списке функций выберите ЕСЛИ и щелкните по кнопке ОК. Появится окно функции ЕСЛИ (рис. 22);

· в окно Логическое выражение введите $D8=5;

· в поле Значение, если истина введите 1;

· в поле Значение, если ложь введите 0;

· щелкните по ОК.

 

Рис. 22. Ввод функции ЕСЛИ в Excel

 

 

В ячейку F8 введена формула =ЕСЛИ($D8=5;1;0). Абсолютный адрес используется, чтобы эту формулу можно было скопировать в соседние столбцы.

· скопируйте формулу в столбец F (подсчет пятерок для каждого студента).

б) Скопируйте формулу в ячейки G8:J8;

в) В столбце G нужно подсчитать число четверок. После копирования в ячейке G8 находится формула ЕСЛИ($D8=5;1;0). Исправьте ее на =ЕСЛИ($D8=4;1;0);

· скопируйте формулу в ячейки G9:G13;

г) В столбце Н нужно подсчитать число троек. Исправьте находящуюся в ячейке Н8 формулу: =ЕСЛИ($D8=3;1;0);

· скопируйте ее в ячейки Н9:Н13;

 

 

 

д) В столбец I нужно ввести формулу для подсчета числа двоек. Исправьте находящуюся в ячейке I 8 формулу. Введите =ЕСЛИ($D8=2;1;0);

· скопируйте в ячейки I 9: I 13;

е) В столбце J нужно подсчитать число неявок. Приведите находящуюся в ячейке J8 формулу к виду =ЕСЛИ($D8=“н/я”;1;0);

· скопируйте в ячейки J9:J13.

 

3.1.3. Подсчет общего числа оценок в группе

В ячейке С16 должна находиться сумма числа пятерок в группе, т. е. количество единиц в столбце F. Аналогично в ячейке С17 будет сумма числа единиц в столбце G и т.д. Для подсчета общего числа оценок (по баллам) в группе:

· активизировать ячейку С16;

· щелкнуть по кнопке S - автосуммирование (в меню Формулы);

· поставьте указатель мыши в ячейку F8 и, удерживая левую клавишу, протащите до F13, отпустите клавишу. В ячейку С16 запишется число пятерок в группе;

· аналогичным образом ввести формулы в ячейки С17:С20.

 

3.1.4. Сохранить созданную ведомость с именем Сессия.

 

3.1.5. Подсчет общего числа сдавших экзамены в группе

Для подсчета общего числа сдавших экзамены в группе нужно просуммировать число имеющих оценки 5, 4, 3, 2 и число неявок на экзамены.

Для этого:

· активизируйте ячейку С21;

· щелкните по кнопке S (автосуммирование);

· указатель мыши протащите по ячейкам С16:С20;

· щелкните по клавише Enter.

 

3.1.6. Создание двух копий ведомости

Для создания каждой:

· поставьте указатель мыши на наименование листа (Лист 1) внизу таблицы;

· щелкните правой клавишей мыши, появится контекстное меню;

· выберите пункт Переместить/Скопировать;

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

Появится Лист 1(2);

· повторите все действия для создания копии Лист 1(3).

3.1.7. Переименуйте три имеющихся экземпляра экзаменационной ведомости

· поставьте указатель мыши на название Лист 1, щелкнув правой клавишей;

· в контекстном меню выберите Переименовать (название листа будет выделено);

· сотрите старое имя клавишей Backspace или Delete, наберите Экзамен 1 (можно вместо Экзамен 1 набрать имя предмета, например Химия);

· повторите эти операции и назовите две другие ведомости Экзамен 2 и Экзамен 3 (или, например, История и Информатика).

 

3.2. Выполнение задания 2

Создание таблицы “Ведомость назначения на стипендию”

Создать таблицу “Ведомость назначения на стипендию” (табл. 19).

Таблица 19

	A	B	C	D	E
	Ведомость назначения на стипендию
	Минимум стипендии
	№ пп	Фамилия, имя, отчество	Средний балл	Количество сданных экзаменов	Стипен-дия
		Снегирев П.П.
		Орлов К.Н.
		Воробьева В.Л.
		Голубкина О.М.
		Дятлов В.А.
		Кукушкин М.И.
	Стипендиальный фонд по группе

 

3.2.1. Ввод числовых и текстовых констант

а) Введите список числовых и текстовых констант в строки 1¸6 согласно табл. 19. Для этого:

· выберите чистый лист в той же рабочей книге, например Лист2, переименуйте его в Стипендия;

· введите заголовок таблицы;

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

· наберите заголовки столбцов (строка 5).

б) Скопируйте список фамилий и его нумерацию с листа Экзамен 1. Для этого:

· щелкните мышью по имени Экзамен 1;

· в открывшемся листе поставьте указатель мыши на ячейку А8 и, зажав клавишу, выделите ячейки А8:В13;

· щелкните по меню Правка;

· в открывшемся меню выберите Копировать (выделенный фрагмент таблицы будет обрамлен “бегущими муравьями”, щелкните по имени листа Стипендия, откроется этот лист;

· поставьте указатель мыши в ячейку А8 (начало вставки копируемого фрагмента);

· щелкните по меню Правка;

· в падающем меню выберите команду Вставить.

 

3.2.2. Ввод формулы для вычисления среднего балла

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

· активизируйте ячейку С8;

· щелкните по кнопке Мастер функций;

· в открывшемся окне Мастер функций выберите категорию Статистические, затем имя функции - СРЗНАЧ, щелкните по ОК;

· во втором окне Мастера функций поставьте курсор в окно Число 1, щелкните левой клавишей;

· поместите указатель мыши на имя листа Экзамен 1, щелкните левой клавишей. В окне Число 1 появится текст: Экзамен 1! (т. е. указано, что первое число нужно брать из ведомости Экзамен 1). Сразу после восклицательного знака введите имя ячейки, откуда следует брать информацию в таблице Экзамен 1 (щелкните по ячейке D8). В окне Число 1 появилась запись Экзамен 1!D8 (табл. 20);

· поставьте указатель мыши в окно Число 2, для чего щелкните левой клавишей мыши (или нажать клавишу Таb);

· щелкните мышью по имени листа Экзамен 2;

· добавьте в окне Число 2 имя ячейки D8. В результате в окне Число 2 будет запись Экзамен 2!D8;

· активизируйте окно Число 3 и, щелкнув по имени листа Экзамен 3, добавьте в окне имя ячейки D8. В результате в окне Число 3 будет запись Экзамен 3!D8;

· щелкните по кнопке ОК Мастера функций. В ячейке С8 появится средний балл студента Снегирева, в окне формул - формула

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

б) Скопируйте формулу на весь столбец С.

Фрагмент табл. 19 в режиме показа формул представлен в виде табл. 20 и 21 (табл. 20 – вычисление среднего балла).

 

3.2.3. Ввод формул для вычисления количества сдавших экзамены

Введите в ячейку D8 формулу для вычисления количества экзаменов, сданных каждым студентом.

Для этого повторите все операции п.3.2.2, но в окне Мастера функций выбирайте операцию Счет. Фрагмент табл. 19 в режиме показа формул с отображением операций СЧЕТ приведен в табл. 21.

 

.

Таблица 20

 

	A	B	C
	Ведомость назначения на стипендию
	Минимум стипендии
	№ пп	Фамилия, имя, отчество	Средний балл
		Снегирев.П.	=СРЗНАЧ(экзамен1!D8;экзамен2!D8;экзамен3!D8)
		Орлов К.Н.	=СРЗНАЧ(экзамен1!D9;экзамен2!D9;экзамен3!D9)
		Воробьева В.Л.	=СРЗНАЧ(экзамен1!D10;экзамен2!D10;экзамен3!D10)
		Голубкина О.М.	=СРЗНАЧ(экзамен1!D11;экзамен2!D11;экзамен3!D11)
		Дятлов В.А.	=СРЗНАЧ(экзамен1!D12;экзамен2!D12;экзамен3!D12)
		Кукушкин М.И.	=СРЗНАЧ(экзамен1!D13;экзамен2!D13;экзамен3!D13)
	Стипендиальный фонд по группе

 

Таблица 21

	D
	Количество сданных экзаменов
	=СЧЁТ(экзамен1!D8;экзамен2!D8;экзамен3!D8)
	=СЧЁТ(экзамен1!D9;экзамен2!D9;экзамен3!D9)
	=СЧЁТ(экзамен1!D10;экзамен2!D10;экзамен3!D10)
	=СЧЁТ(экзамен1!D11;экзамен2!D11;экзамен3!D11)
	=СЧЁТ(экзамен1!D12;экзамен2!D12;экзамен3!D12)
	=СЧЁТ(экзамен1!D13;экзамен2!D13;экзамен3!D13)

3.2.4. Ввод формул для начисления стипендии

а) Введите в ячейку F8 формулу для вычисления стипендии Снегирева. При этом имеется в виду, что при среднем балле меньше 3,5 или числе сданных экзаменов меньше трех стипендия не начисляется, при среднем балле больше 4,5 начисляется в полуторном размере. Если средний балл находится в пределах от 3,5 до 4,5 баллов, назначается минимальная стипендия. Для ввода формулы:

· активизируйте Е8;

· щелкните по кнопке Мастер функций (меню Формулы – Вставить функцию);

в первом окне выберите категорию Логические, функция ЕСЛИ;

· в окно Логическое выражение введите ИЛИ(D8<3;С8<3,5);

· в окно Значение если истина введите 0;

· в окно Значений если ложь введите выражение ЕСЛИ(С8>=4,5;1,5*D$3;D$3)

(в ячейке D3 находится значение минимального размера стипендии, а этот адрес не должен изменяться при копировании формулы по столбцу, поэтому он записан в абсолютной форме, со знаком $ перед номером строки 3);

· щелкните по кнопке ОК Мастера функций. В ячейке Е8 появится

назначенное значение стипендии, а в строке формул - формула

=ЕСЛИ(ИЛИ(D8<3;С8<3,5);0; ЕСЛИ(С8>=4,5;1,5*D$3;D$3)).

б) Скопируйте формулу на весь столбец Е (показ формул для данного фрагмента табл. 19 приведен в табл. 22).

в) Проверьте работу таблицы, изменяя оценки в ведомостях: Экзамен 1, Экзамен 2, Экзамен 3.

Таблица 22

	E
	Стипендия
	=ЕСЛИ(ИЛИ(D8<3;C8<3,5);0;ЕСЛИ(С8>=4,5;1,5*D$3;D$3))
	=ЕСЛИ(ИЛИ(D9<3;C9<3,5);0;ЕСЛИ(С9>=4,5;1,5*D$3;D$3))
	=ЕСЛИ(ИЛИ(D10<3;C10<3,5);0;ЕСЛИ(C10>=4,5;1,5*D$3;D$3))
	=ЕСЛИ(ИЛИ(D11<3;C11<3,5);0;ЕСЛИ(C11>=4,5;1,5*D$3;D$3))
	=ЕСЛИ(ИЛИ(D12<3;C12<3,5);0;ЕСЛИ(C12>=4,5;1,5*D$3;D$3))
	=ЕСЛИ(ИЛИ(D13<3;C13<3,5);0;ЕСЛИ(C13>=4,5;1,5*D$3;D$3))
	=СУММ(E8:E13)

 

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

Для подсчета:

· введите в ячейку В16 табл. 19 комментарий “Стипендиальный фонд по группе”;

· выровняйте текст по ячейкам В16:D16 (выделить В16:D16 и щелкнуть по пиктограмме ;

· активизируйте ячейку F16;

· щелкните по пиктограмме S;

· поставьте курсор мыши в ячейку Е8 и, зажав левую клавишу, протащите до Е13;

· нажмите клавишу Enter.

 

3.2.6. Сохранить файл с именем Стипендия

Отчет по работе

Распечатка табл. 18, 20.