Практическое занятие №1 Тема: «Ссылки. Встроенные функции MS Excel 2013».

План занятия:  Знакомство с ссылками на данные: абсолютной, относительной, смешанной;

• Использование в расчетах встроенные математические и статистические функции Excel 2013;

• Выполнение операций по копированию, перемещению и автозаполнению отдельных ячеек и диапазонов.

 

В Excel различают ссылки трех типов:

• Относительные;  Абсолютные;

• Смешанные.

 

Относительная ссылка Excel -когда при копировании и переносе формул в другое место, в формулах меняется адрес ячеек относительно нового места

(Рис.2).

 

Рис

унок

.

Относительная ссылка

Абсолютная ссылка всегда указывает на зафиксированную при создании ячейку или диапазон и не изменяется при переносе или копировании формулы (Рис. 3). Для того чтобы каждый раз не заполнять знак $ перед и после буквы заданной ячейки можно «щелкнуть» в ячейке и нажать F4 до преобразования адреса к нужному виду.

Рисунок 3. Абсолютная ссылка

Группу ячеек, образующих прямоугольник называют диапазон (Рис. 4).

 

Рисунок 4

.

Диапазон С1:С4

 

Встроенные функции Еxcel 2013

 

Excel 2013 содержит 320 встроенных функций. Простейший способ получения информации о любой функции это нажать на кнопку Вставить функцию

и выбрать из появившегося списка окна Вставка функции любую из

них. Внизу окна будет краткое описание выбранной функции (рис. 5)

Рисунок 5. Краткое описание функции ЕСЛИ

 

Для удобства функции в Excel 2013 разбиты по категориям: математиче-

ские, финансовые, статистические и т.д.

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

Таблица 1. Встроенные функции Excel 2013

Функции	Вид записи	Назначение
Математические	КОРЕНЬ(...)	Вычисление квадратного корня
ABS(...)	Вычисление абсолютного значения (модуля) числа
ЦЕЛОЕ(...)	Округление числа или результата выражения, указанного в скобках, до ближайшего меньшего (!) целого
ПИ() *	Значение математической константы «ПИ» (3,1415926...)
НОД(…)	Наибольший общий делитель нескольких чисел
НОК(…)	Наименьшее общее кратное нескольких чисел
СЛЧИС() *	Вычисление случайного числа в промежутке между 0 и 1
Статистические	МИН(...)	Определение минимального из указанных чисел
МАКС(…)	Определение максимального из указанных чисел
СРЕДНЕЕ(...)	Определение среднего значения указанных чисел
СУММ(...)	Определение суммы указанных чисел
Дата и время	СЕГОДНЯ () *	Значение сегодняшней даты в виде даты в числовом формате
МЕСЯЦ(дата)	Вычисление порядкового номера месяца в году по указанной дате
ДЕНЬ(дата)	Вычисление порядкового номера дня в месяце по указанной дате
ГОД(дата)	Вычисление года по указанной дате
Логические	И(условие1; усло- вие2;...)	Вычисление значения (ИСТИНА, ЛОЖЬ) логической операции И
ИЛИ(условие1; условие2;...)	Вычисление значения (ИСТИНА, ЛОЖЬ) логической операции ИЛИ
ЕСЛИ(условие; знач_ИСТИНА; знач_ЛОЖЬ)	Вычисление значения в зависимости от выполнения условия

* Записывается без аргументов.

Задание 1. Предлагается рассчитать расход и стоимость электроэнергии за месяц. Заданы стоимость 1 кВт/ч. электроэнергии и показания счетчика за предыдущий и текущий месяцы.

Рисунок 6. Таблица с исходными данными для расчета электроэнергии

Ход работы:

1. Оформить таблицу как на рисунке 6.

2. Заполнить самостоятельно номера квартир и данные для показаний счетчиков до 34 ячейки. Для этого в ячейку А4 нужно ввести: Кв. 127, в ячейку А5 ввести: Кв. 128. Выделить ячейки А4:А5 и с помощью маркера заполнения^* заполнить нумерацию квартир по 157 включительно.

* Маркер заполнения- небольшой черный квадрат в правом нижнем углу выделенной ячейки или выделенного диапазона. Маркер заполнения используется для заполнения соседних ячеек содержимым выделенной ячейки.

3. Для заполнения показаний счетчика в текущем и предыдущем месяце можно использовать функцию СЛУЧМЕЖДУ, которая позволяет выбрать случайное число между выбранными диапазонами. Например

= СЛУЧМЕЖДУ(150;190) – столбец В и =СЛУЧМЕЖДУ(190;350) – столбец С.

4. Задать фиксированную ширину строк. Выделите ячейки А3:Е3. На главной вкладке панели управления выбрать команду Формат – Ширина столбца– 15.

5. Выравнивание текста в ячейках. Для этого нужно выделить ячейки А3:Е3. «Щелкнуть» правой кнопкой мыши, выбрать команду Формат ячеек – Выравнивание: по горизонтали –по центру, по вертикали– по центру, отображение – переносить по словам.

6. В ячейку D4 ввести формулу =C4-B4. И заполнить строки ниже с помощью маркера заполнения.

7. В ячейку E4 ввести формулу =D4*$B$1. И заполните строки ниже с помощью маркера заполнения.

8. В ячейке А35 ввести текст «Статистические расчеты». Выделить ячейки A35:B35 и «щелкнуть» на панели инструментов кнопку «Объединить и поместить в центре».

9. В ячейках A36:A39 ввести текст, указанный на рисунке.

10. В ячейку В36 ввести математическую функцию СУММ. Для этого

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

11. Аналогично функции задаются и в ячейках B37:B39.

12. Переименовать лист в Электроэнергию.

13. Сохранить результат работы в папке своей группы.

Задание 2.

Рассчитать свой возраст, начиная с текущего года и по 2040 год, используя маркер заполнения. Год рождения является абсолютной ссылкой (рис. 7)

Рисунок 7. Формула для расчета возраста

 

Задание 3.

Рассчитать количество прожитых дней.

Технология работы:

1. Создать новый лист.

2. В ячейку A1 ввести дату своего рождения (число, месяц, год –

20.12.91).

3. Просмотреть различные форматы представления даты (Формат ячеек – Число – Числовые форматы - Дата). Перевести дату в тип ЧЧ.ММ.ГГГГ.

4. В ячейку A2 ввести сегодняшнюю дату.

5. В ячейке A3 вычислить количество прожитых дней по формуле

=A2-A1. Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой формат (рис. 8). (Формат ячеек – Число – Числовые форматы – Числовой – число знаков после запятой – 0).

Рисунок

. Числовой формат

Задание 4.

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

Рисунок

. Список учащихся

 

Технология работы:

1. Чтобы рассчитать возраст учащихся необходимо с помощью функции СЕГОДНЯ выделить сегодняшнюю текущую дату из нее вычитается дата рождения учащегося, далее из получившейся даты с помощью функции ГОД выделяется из даты лишь год. Из полученного числа вычитается 1900 – века и получается возраст учащегося.

2. В ячейку D3 записать формулу =ГОД(СЕГОДНЯ()-С3)-1900. Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой формат. (Формат – Формат ячеек – Число – Числовые форматы – Числовой – число знаков после запятой – 0).

3. Определить самый ранний день рождения. В ячейку C22 записать формулу =МИН(C3:C21);

4. Определить самого младшего учащегося. В ячейку D22 записать формулу = МИН(D3:D21);

5. Определить самый поздний день рождения. В ячейку C23 записать формулу = МАКС(C3:C21);

6. Определить самого старшего учащегося. В ячейку D23 записать формулу = МАКС(D3:D21).

7.

Задание 5.

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

Технология работы:

1. Заполнить таблицу исходными данными (рис. 10). Внимание! Столбцы G и H рассчитываются по формулам.

 

Рисунок 10. Сведения об учащихся группы.

2. Для расчета возраста используется следующая формула (на примере ячейки G4): =ЦЕЛОЕ((СЕГОДНЯ()-E4)/365,25)

Комментарии к формуле: из сегодняшней даты вычитается дата рождения ученика. Таким образом, получается полное число дней, прошедших с рождения ученика. Разделив это количество на 365,25 (реальное количество дней в году, 0,25 дня для обычного года компенсируется високосным годом), получается полное количество лет ученика; наконец, выделив целую часть — возраст ученика.

3. Является ли девушка отличницей, определяется формулой (на примере ячейки H4):

=ЕСЛИ(И(D4=5;F4="ж");1;0)

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

=СУММЕСЛИ(F4:F15;"м";D4:D15)/СЧЁТЕСЛИ(F4:F15;"м")

Функция СУММЕСЛИ позволяет просуммировать значения только в тех ячейках диапазона, которые отвечают заданному критерию (в данном случае студент является юношей). Функция СЧЁТЕСЛИ подсчитывает количество значений, удовлетворяющих заданному критерию.

5. Для подсчета доли отличниц среди всех девочек необходимо отнести количество девушек-отличниц к общему количеству девушек: =СУММ(H4:H15)/СЧЁТЕСЛИ(F4:F15;"ж")

6. Определить отличие средних баллов студентов возрастов 21 и 22 года

=ABS(СУММЕСЛИ(G4:G15;21;D4:D15)/СЧЁТЕСЛИ(G4:G15;21)-СУММЕСЛИ(G4:G15;22;D4:D15)/СЧЁТЕСЛИ(G4:G15;22))

Самостоятельная работа:

 

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

 

2. Создайте таблицу «Озера Европы», используя следующие данные по площади (кв. км) и наибольшей глубине (м): Ладожское 17 700 и 225; Онежское 9510 и 110; Каспийское море 371 000 и 995; Венерн 5550 и 100; Чудское с Псковским 3560 и 14; Балатон 591 и 11; Женевское 581 и 310; Веттерн 1900 и 119; Боденское 538 и 252; Меларен 1140 и 64. Определите самое большое и самое маленькое по площади озеро, самое глубокое и самое мелкое озеро.

 

3. В банке производится учет своевременности выплат кредитов, выданных нескольким организациям. Известна сумма кредита и сумма, уже выплаченная организацией. Для должников установлены штрафные санкции: если фирма выплатила кредит более чем на 70 процентов, то штраф составит 10 процентов от суммы задолженности, в противном случае штраф составит 15 процентов. Посчитать штраф для каждой организации, средний штраф, общее количество денег, которые банк собирается получить дополнительно. Определить средний штраф бюджетных организаций.

 

4. Произведите необходимые расчеты роста учеников в разных единицах измерения.

 

Рисунок 11. Таблица для расчета роста учеников

 

Практическое занятие № 2