Методические указания для студентов заочного и дневного отделения геолого-географического факультета по курсу ''Информатика''. Электронные таблицы. Функции в Excel

Министерство образования Российской Федерации РОСТОВСКИЙ ОРДЕНА ТРУДОВОГО КРАСНОГО ЗНАМЕНИ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ

Амелина Н.И., Мачулина Л.А.

Методические указания для студентов заочного и дневного отделения геолого-географического факультета по курсу ИНФОРМАТИКА ЭЛЕКТРОННЫЕ ТАБЛИЦЫ ФУНКЦИИ В EXCEL

Ростов-на-Дону 2003

Печатается

по

решению

кафедры

прикладной

математики

и

программирования от 20 ноября 2003 г.

АННОТАЦИЯ В методических указаниях рассмотрены некоторые категории функций табличного процессора Excel ; приведены примеры их применения, в том числе с использованием Мастера функций, и лабораторные работы, позволяющие на практике освоить технику работы с функциями Excel. Методические указания предназначены для студентов геологогеографического факультета РГУ. Авторы: Н.И.Амелина, Л.А.Мачулина

© Н.И.Амелина, Л.А.Мачулина, 2003

3

СОДЕРЖАНИЕ Введение

4

1. Обзор функций Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

4

1.1. Математические функции . . . . . . . . . . . . . . . . . . . . . . . . . . .

4

1.2. Статистические функции . . . . . . . . . . . . . . . . . . . . . . . . . . .

7

1.3. Логические функции . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

8

1.4. Функции даты . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

10

2. Мастер функций . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Задача 1. Ширина реки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

13

Задача 2. Погода в феврале . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

17

Задача 3. Осадки . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 ЛИТЕРАТУРА . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

24

ПРИЛОЖЕНИЕ 1. Функции табличного процессора . . . . . . . . . . . . . . 24 ПРИЛОЖЕНИЕ 2. Сообщения об ошибках . . . . . . . . . . . . . . . . . . . . . . 28

4

ВВЕДЕНИЕ Функции табличного процессора – это встроенные формулы для выполнения стандартных вычислений. Чтобы использовать функцию, нужно ввести ее как формулу или часть формулы в ячейку рабочего листа. После имени функции в круглых скобках указывается список аргументов. Аргументы отделяются друг от друга точкой с запятой ";" или запятой "," в зависимости от настройки табличного процессора и способа представления вещественных чисел ( с запятой или точкой в качестве десятичного разделителя). Аргументами функции могут быть числа, текст, адреса ячеек и блоков ячеек, а также выражения, содержащие другие функции. Некоторые функции могут иметь необязательные аргументы, которые можно опускать. 1. ОБЗОР ФУНКЦИЙ EXCEL Табличный процессор Excel содержит большое количество встроенных функций для выполнения стандартных вычислений. Функции табличного процессора подразделяются на категории: –

математические,

–

статистические,

–

логические,

–

даты и времени и другие.

1.1. М а т е м а т и ч е с к и е ф у н к ц и и К категории математических функций относятся функции арифметические и тригонометрические. Тригонометрические функции включают большой набор функций, в том числе обратные тригонометрические функции. При использовании тригонометрических функций аргумент (угол) указывается в радианах, но имеются функции для перевода градусов в радианы и обратно. В качестве аргумента тригонометрической функции могут выступать

5

число, ссылка на ячейку, вызов функции, а также выражение (формула), содержащее числа, ссылки на ячейки и формулы. Например, SIN(ПИ()) имеет значение 1,22E-16, что приблизительно равно 0 (нулю) (синус π равен нулю), SIN(1,57) имеет значение 0,999999683, что приблизительно равно 1 – это и будет результатом, SIN(30*ПИ()/180) имеет значение 0,5 (синус 30 градусов), SIN(C5) – здесь С5 – ссылка на ячейку с аргументом функции. В перечисленных примерах используется функция без аргумента ПИ(), которая возвращает число 3,14159265358979 – математическую константу π с точностью до 15 цифр. ПРИМЕР 1.1. Найти площадь участка, имеющего форму равнобочной

трапеции, если известны длины меньшего из оснований a и боковых сторон b и угол α между боковой стороной и большим основанием (рис. 1).

α b

a Рис. 1. Участок в форме трапеции Площадь трапеции вычисляется по формуле

S = a · b · sin α + b · sin α · b · cos α = = b · sin α ·( α + b · cos α )

(1)

Вычислить площадь участка по результатам измерений:

a = 440 м, b = 335 м, α = 50

о

Так как аргумент тригонометрических функций Excel указывается в радианах, формула ( 1 ) должна быть преобразована к виду:

S = b · sin (α · π /180 ) · ( α + b · cos (α · π /180 ) )

(2)

6

Для вычисления площади участка по формуле (2) выполните следующие действия: 1. В ячейки A1, B1, B2, C1, C2, D2, E1 введите заголовки столбцов, как показано на рис. 2. и отформатируйте их (см. [2], раздел 6 ), используя возможности команды Формат/Ячейки… и кнопок панели Форматирование. A 1 2

B

Основание

3

C

D

Боковая

Угол

сторона

в градусах в радианах

440

335

50 0,87266463

E Площадь 168175

Рис. 2. Вычисление площади участка 2. В ячейки A3:C3 внесите соответственно значения основания трапеции

a , боковой стороны b и угла α в градусах. 3. В ячейке D3 наберите формулу для перевода из градусов в радианы: =C3*ПИ()/180

4. В ячейку E3 введите формулу для вычисления площади участка: =B3*SIN(D3)*(A3+B3*COS(D3))

Результат вычислений ( площадь участка ) представлен на рис. 2. Если аргументы функции не попадают в область допустимых значений, то функция принимает значение #Н/Д - неопределенные данные. Арифметические функции позволяют производить простые и сложные математические

вычисления,

например:

вычисление

суммы

значений

(СУММ); вычисление суммы диапазона ячеек, удовлетворяющих заданному условию (СУММЕСЛИ); вычисление количества ячеек диапазона, удовлетворяющих заданному условию (СЧЁТЕСЛИ); различные варианты округления чисел (ОКРУГЛ, ЦЕЛОЕ); нахождение частного (ЧАСТНОЕ) и остатка (ОСТАТ) от деления целых чисел и другие функции. Арифметические функции используют в качестве аргументов числовые значения.

7

Рассмотрим в качестве примера функцию СУММ, она может иметь до 30 аргументов, разделяемых точкой с запятой ";" (или запятой "," в зависимости от настройки). Аргументами могут быть числа, ссылки на ячейки и диапазоны ячеек. Пустые ячейки и ячейки, содержащие нечисловые значения, при суммировании игнорируются. Например, функция СУММ ( D1:D10; 23; D15)

вычисляет сумму числовых значений диапазона ячеек D1:D10, числа 23, значения в ячейке D15. Приведенную в качестве примера функцию, в отличие от других, можно вызвать с помощью кнопки на панели инструментов Стандартная. Это кнопка

– Автосумма.

Перечень основных математических функций приводится в приложении 1, полный перечень можно найти в справочной системе Excel.

1.2. С т а т и с т и ч е с к и е ф у н к ц и и Статистические функции позволяют выполнять статистический анализ данных. В статистических функциях в качестве аргументов может быть использован список значений. В списке могут присутствовать числа, адреса ячеек, их имена, адреса и имена блоков ячеек. В списке они разделяются точкой с запятой ";" или запятой ",". Пустые ячейки, являющиеся частью аргументов, при расчетах игнорируются. ПРИМЕР 1.2. Использование математических и статистических функ-

ций. Дана последовательность чисел, записанная в диапазоне ячеек B2:J2 (см. рис. 3 ). Требуется вычислить сумму этих чисел (СУММ) и сумму отрицательных из них (СУММЕСЛИ), посчитать общее количество чисел (СЧЁТ), найти среди чисел диапазона наибольшее (МАКС) и наименьшее (МИН), вычислить среднее значение (СРЗНАЧ).

8

Для вычисления в диапазон ячеек B4:B9 введите формулы Ячейка

Формула

B4

=СУММ(B2:J2)

B5

=СУММЕСЛИ(B2:J2;"<0")

B6

=СЧЁТ(B2:J2)

B7

=МАКС(B2:J2)

B8

=МИН(B2:J2)

B9

=СРЗНАЧ(B2:J2)

В н и м а н и е ! Для суммирования чисел используйте кнопку Автосумма

на панели инструментов Стандартная. В н и м а н и е ! Для выделения диапазона B2:J2 следует выбрать щелч-

ком мыши первую ячейку диапазона, нажать левую кнопку мыши и протянуть до конца диапазона. Исходные данные и результаты вычислений представлены на рис. 3. A 1 2 3 4 5 6 7 8 9

B

C -9

D -1

E -7

F 0

G 6

H -3

I -2

J -9

6

-19 -31 9 6 -9 -2,111

Рис. 3. Использование математических и статистических функций Перечень некоторых статистических функций приведен в приложении 1, полный перечень можно найти в справочной системе Excel.

1.3. Л о г и ч е с к и е ф у н к ц и и Логические функции предназначены для проверки выполнения условия или для проверки нескольких условий (функция ЕСЛИ), а также соответствуют логическим операциям И (логическое сложение), ИЛИ (логическое умножение) и НЕ (отрицание).

9

Функция ЕСЛИ имеет три аргумента. Первый аргумент – условие, которое должно быть проверено и результатом проверки которого является логическое значение ИСТИНА (TRUE), если условие выполняется, или ЛОЖЬ (FALSE), если условие не выполняется. Второй аргумент – значение, которое возвращается функцией, если условие выполняется, т.е. получает значение ИСТИНА. Третий аргумент – значение, которое возвращается функцией, если условие не выполняется, т.е. получает значение ЛОЖЬ. Таким образом, функция ЕСЛИ позволяет определить, выполняется или не выполняется указанное условие, и возвращает одно значение если условие истинно, или другое – если оно ложно. Результатом функции ЕСЛИ может быть число, текст или логическое значение. Второй и третий аргументы функции ЕСЛИ могут быть выражениями, содержащими функцию ЕСЛИ. ПРИМЕР 1.3. Вычислить ln ( x-2 ).

Если значение x находится в ячейке D3, то формула для вычисления значения логарифма имеет вид =ЕСЛИ(D3 > 2, LN(D3-2), "аргумент меньше или равен 0")

Более сложный выбор можно организовать с помощью нескольких применений функции ЕСЛИ. ПРИМЕР 1.4. Вычисление со сложным условием.

Необходимо баллу по 100-балльной шкале (например, полученному в результате некоторого тестирования) поставить в соответствие оценку по 5балльной шкале, используя, например, следующую формулу:

⎧5, ⎪4, оценка= ⎨ 3, ⎪ ⎩2,

если если если если

балл ≥ 90, 80 ≤ балл < 90, 60 ≤ балл < 80, балл < 60.

Если ячейка F8 содержит балл по 100-балльной шкале, то оценку по 5бальной определяет формула =ЕСЛИ(F8>=90; 5; ЕСЛИ(F8>=80; 4; ЕСЛИ(F8>=60;3;2))) В н и м а н и е ! Вводимая формула не должна содержать пробелов.

10

Функции И ( AND ) , ИЛИ ( OR ) и НЕ ( NOT ) позволяют формулировать более сложные условия для проверки.

1.4. Ф у н к ц и и д а т ы Функции для работы с датой и временем позволяют анализировать и работать со значениями даты и времени в формулах. Для работы с датами электронные таблицы используют число дней, прошедшее от 01.01.1900 до указанной даты (в таком виде они хранят даты). Но на экране даты представляются в привычной нам форме, если для соответствующей ячейки задан формат "Дата". В некоторых задачах требуется определять продолжительность периодов между двумя датами. Для этого подойдут функции даты : СЕГОДНЯ () - определение текущей даты по внутрисистемным часам ; ДЕНЬ ( дата ) - выделение дня из даты ; МЕСЯЦ ( дата ) - выделение месяца из даты ; ГОД ( дата ) - выделение года из даты ; ДАТА(год, месяц, день) – возвращает дату в числовом формате. ПРИМЕР 1.5. Определить возраст по дате рождения.

Допустим, в ячейке D5 записана дата рождения в формате даты. Требуется определить в ячейке F5 возраст, учитывая только год, т.е. возраст как разность между текущим годом и годом рождения. Для этого в ячейку F5 необходимо ввести формулу = ГОД ( СЕГОДНЯ ()) - ГОД ( D5 )

Может так случиться, что результат покажется вам странным, так как Excel установит для него формат Даты. Для перехода к числовому формату выделите ячейку F5 и установите для нее формат Общий. ПРИМЕР 1.6. Определить возраст с учетом месяца рождения.

В отличие от предыдущего примера здесь предполагается вычисление возраста как количества прожитых полных лет. Пусть по-прежнему, в ячейке

11

D5 находится возраст в формате даты, а в ячейке F5 мы получили возраст с учетом года (см. пример 1.5.). Для получения полных лет возраста введите в ячейку G5 формулу = ЕСЛИ (МЕСЯЦ (СЕГОДНЯ () )>МЕСЯЦ (D5 ); F5; F5 - 1)

используя логическую функцию ЕСЛИ и функции даты МЕСЯЦ и СЕГОДНЯ (пробелы при вводе формулы, как правило, недопустимы). 2. МАСТЕР ФУНКЦИЙ В Excel есть специальное средство для эффективной работы с функциями – Мастер функций. Запускается Мастер функций кнопкой Вставка функции панели инструментов Стандартная. ПРИМЕР 2.1. Использование Мастера функций.

Рассмотрим использование Мастера функций для вызова встроенных функций при вычислении среднего арифметического последовательности чисел и суммы отрицательных из них. 1. Введите в ячейки A2 : E2 произвольные числа. 2. Для получения первого результата выделите ячейку В4 и нажмите кнопку

на панели инструментов Стандартная. Появится диалоговое

окно Мастера функций. 3. В окне Категория выберите категорию Статистические, а в окне Функция выберите функцию СРЗНАЧ. Нажмите кнопку ОК.

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

12

мерцающей рамкой. В нижней части окна функции выводится вычисленное значение функции. Нажмите кнопку ОК. 5. В ячейке В4 будет виден результат вычисления, а в строке формул отображается формула, созданная Мастером функций: =СРЗНАЧ(A2:E2)

Исходные данные и результат вычислений представлены на рис. 4. A 1 2 3 4

B

C

5

D

-9

-1

-2,4

-17

E -7

0

Рис. 4. Результаты вычислений с использованием Мастера функций 6. Для получения второго результата выделите ячейку С4 и нажмите кнопку

на панели инструментов Стандартная.

7. В появившемся диалоговом окне Мастера функций в окне Категория выберите категорию Математические, а в окне Функция выберите функцию СУММЕСЛИ. Нажмите кнопку ОК. 8. В диалоговом окне выбранной функции протягиванием мыши выделите диапазон ячеек A2:E2 (см. п.4.). После того как вы отпустите кнопку мыши, в поле ввода Диапазон появится строка A2:E2. В поле ввода Условие задайте условие отрицательности выбираемых для суммирования чисел: "<0" (можно указать условие и в более простой форме, без кавычек, то есть так – <0 )

В нижней части окна функции будет выведено вычисленное значение функции. Нажмите кнопку ОК. 9. В ячейке С4 будет виден результат вычисления (см. рис. 4), а в строке формул отображается формула, созданная Мастером функций: =СУММЕСЛИ(A2:E2;"<0")

13

ЗАДАЧА 1. ШИРИНА РЕКИ Определить ширину реки (рис. 5.) по результатам измерений, приведенным в таблице 1. Величины углов α и β заданы в градусах, расстояния - в метрах.

C D d

α

β B l

A

Рис. 5. Схема определения ширины реки Ширину реки d = AC можно определить на основе измерений углов α и

β на расстоянии l . Таблица 1. Вариант

α

l

β

1

72,33

90

51

2

64,51

86

54

3

58,92

81

52

4

91,00

88

50

5

86,15

91

57

6

120,14

92

61

7

101,07

94

57

8

131,29

90

55

9

140,44

88

54

10

95,88

89

56

11

124,29

64

81

12

123,57

72

72

14

Пусть AD – высота, опущенная из точки A на сторону BC. Тогда, из прямоугольных треугольников ACD и ADB с общей стороной AD следует: AD = d · sin ( 180 – (α + β )) = l · sin β o

(3)

Следовательно,

d = l · sin β / sin (α + β ), так как

(4)

sin ( 180o – (α + β )) = sin (α + β )

Этап 1. В в о д и с х о д н ы х д а н н ы х 1. Запустите табличный процессор Excel и введите в ячейку B1 рабочего листа Лист1 заголовок таблицы: Определение ширины реки

2. Присвойте рабочему листу Лист1 содержательное имя, например, Ширина реки (см. [2], этап 1. задачи 2 ).

3. Сохраните таблицу (см. [2], раздел 7 ) в личной папке, дав файлу подходящее имя, например, Река (будет создан файл Река.xls). В н и м а н и е ! Не забывайте регулярно сохранять файл во избежание

потери результатов работы. 4. Введите в ячейки A2 : D2 заголовки столбцов таблицы:

Вариант ,

l, α, β

5. В диапазоне ячеек A3 : A14 постройте числовой ряд со значениями от 1 до 12: – введите первое число ряда 1 в первую ячейку диапазона A3; – нажмите и удерживайте нажатой клавишу Ctrl; – установите курсор мыши на маркер заполнения ячейки A3 (маленький черный квадратик в правом нижнем углу ячейки) – курсор должен принять вид тонкого черного крестика; – нажмите кнопку мыши и потяните вниз, выделяя диапазон; – отпустите кнопку мыши; – отпустите клавишу Ctrl.

15

6. Заполните блок ячеек B3 : D14 данными из таблицы 1. 7. Сохраните текущее состояние таблицы.

Этап 2. В ы п о л н е н и е р а с ч е т о в 1. Введите в ячейку E2 заголовок

d 2. В ячейке E3 получите формулу ( 4 ). Для этого •

Выделите ячейку E3.

•

Наберите на клавиатуре символ « = ».

•

Щелкните мышью на ячейке B3 – ссылка на неё попадет в формулу.

•

Наберите на клавиатуре символ « * ».

•

Наберите на клавиатуре

•

Щелкните мышью на ячейке D3.

SIN(

В н и м а н и е ! При использовании тригонометрических функций угол

указывается в радианах, поэтому аргумент D3 функции SIN нужно преобразовать в радианы (см. ПРИМЕР 1.1. ): •

Наберите на клавиатуре

•

Наберите на клавиатуре символ « / ».

•

Наберите на клавиатуре

*ПИ()/180)

SIN(

Для преобразования аргумента C3 + D3 функции SIN в радианы можно домножить аргумент на ПИ()/180, как было описано выше, а можно воспользоваться функцией РАДИАНЫ: •

Вызовите Мастер функций.

•

В появившемся окне Мастера функций в окне Категория выберите категорию Математические, а в окне Функция выберите функцию РАДИАНЫ и нажмите кнопку ОК.

•

В диалоговом окне выбранной функции в поле ввода Угол укажите (см. ПРИМЕР 2.1.)

C3 + D3

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

16 •

Так как в выражении не хватает закрывающейся скобки, Excel выдаст сообщение об ошибке. Следует принять предложенные исправления, нажав кнопку Да.

•

В ячейке E3 будет виден результат вычисления (рис. 6.) , а в строке формул появится формула, получившаяся в ячейке E3 : =B3*SIN(D3*ПИ()/180)/SIN(РАДИАНЫ(C3+D3)) A

B

1

C

D

E

Определение ширины реки

l

α

β

d

2

Вариант

3

1

72,33

90

51 89,32011

4

2

64,51

86

54 81,19274

5

3

58,92

81

52 63,48446

6

4

91,00

88

50

7

5

86,15

91

57 136,3443

8

6

120,14

92

61 231,4516

9

7

101,07

94

57 174,8407

10

8

131,29

90

55 187,5016

11

9

140,44

88

54 184,5468

12

10

95,88

89

56 138,5833

13

11

124,29

64

81 214,0252

14

12

123,57

72

72 199,9405

104,18

Рис. 6. Таблица вычисления ширины реки 3. Выделите ячейку E3 и скопируйте формулу, используя маркер заполнения, на блок ячеек E4:E14. 4. Отформатируйте полученную таблицу, используя возможности команды Формат/Ячейки… или кнопки на панели Форматирование. 7. Сохраните текущее состояние таблицы.

17

ЗАДАЧА 2. ПОГОДА В ФЕВРАЛЕ В течение нескольких лет в городе Ростове-на-Дону проводились в утренние часы ( 7-8 часов утра ) измерения температуры воздуха. Данные о температуре в феврале за 6 лет приведены таблице 2. Таблица 2. Числа месяца 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

Температура в феврале по годам 1996 г. 1997 г. 1998 г. 1999 г. 2000 г. 2001 г. 2002 г. -10 -4 0 -3 2 5 4 -15 -9 -13 -1 -3 3 2 -10 -13 0 3 0 -1 -6 -15 0 2 1 -1 -2 2 -15 -8 4 -2 -1 -3 -1 -11 -2 4 -1 -1 -2 1 -4 -4 4 0 -6 -7 -3 -3 -11 2 1 -6 -7 -5 -6 -11 2 4 -6 -5 -9 -1 -7 0 6 -3 -2 -13 0 -3 2 5 -1 -2 -16 5 0 2 1 0 0 -9 3 1 0 1 0 0 -5 -12 2 1 0 -1 -4 -1 2 3 -1 -2 -4 -16 2 4 0 -1 -16 0 0 3 0 -2 -1 -4 0 3 -1 -2 1 -10 -9 4 4 -1 -2 4 -13 3 2 2 -1 -2 1 -13 2 0 3 -8 -8 1 -7 3 -4 4 -1 -2 1 0 3 -1 3 0 -2 1 -2 2 3 0 -2 -3 -1 1 1 3 -1 0 -2 -5 -5 2 -4 -5 -6 -11 -3 2 -4 -2 -3 -11 3 2 -4 -1 -2 -10 -4

Обратите внимание на то, что некоторые ячейки таблицы пусты (см. 1997 г. и 3.02.98 ), так как данные за эти дни отсутствуют. Необходимо создать электронную таблицу, содержащую данные из таблицы 2, а затем на основе этих данных:

18

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

количество дней с минусовой, плюсовой и нулевой температурой,

•

среднемесячную температуру,

•

минимальную и максимальную температуры,

•

размах выборки – разность между минимальным и максимальным значениями температур.

Этап 1. П о с т р о е н и е т а б л и ц ы 1. Запустите табличный процессор Excel и введите в ячейку C1 рабочего листа Лист1 заголовок таблицы: Температура в феврале по годам

2. Присвойте рабочему листу Лист1 содержательное имя, например, Февраль.

3. Сохраните таблицу в личной папке, дав файлу подходящее имя, например, Погода (будет создан файл Погода.xls). В н и м а н и е ! Не забывайте регулярно сохранять файл во избежание

потери результатов работы. 4. Введите в ячейки B1 и B2 заголовок первого столбца таблицы: Числа месяца

5. В диапазоне ячеек B3 : B31 постройте числовой ряд (см. п. 5. этапа 1. задачи 1 ) со значениями от 1 до 29 (дни февраля). 6. В диапазоне ячеек C2:I2 постройте смешанный ряд со значениями от 1996 г. до 2002 г. Смешанным (комбинированным) называется ряд, в котором члены ряда

содержат текст и число. Такой ряд обязательно должен содержать число; текст может находиться как перед числом, так и после числа. Смешанный ряд строится по закону изменения числа, при этом начальные значения ряда задаются одной или двумя ячейками так же, как и для числовых рядов (см. п. 5.).

19

7. Заполните блок ячеек C3 : I31 данными из таблицы 2. В н и м а н и е ! При отсутствии данных в таблице 2 соответствующие

ячейки должны быть пустыми. 8. Расположите заголовок таблицы Температура в феврале по годам по центру таблицы. Для этого выделите диапазон C1 : I1 и нажмите кнопку Объединить и поместить в центре

панели Форматирование. Можно

изменить размер шрифта заголовка и его начертание. 9. Сохраните текущее состояние таблицы.

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

2. Введите в ячейки J2 и K2 заголовки столбцов: низкая и высокая

3. В ячейке J3 получите, используя Мастер функций

, формулу

(см. пример 2.1. ) : =МИН( C3:I3 ) У к а з а н и е. Функция МИН входит в категорию Статистические,

но так как ее часто применяют, сначала следует поискать ее в категории 10 недавно использовавшихся.

4. Аналогично получите в ячейке K3 формулу =МАКС( C3:I3 )

5. Выделите ячейки J3 : K3 и скопируйте формулы, используя маркер заполнения, на блок ячеек J4 : K31. 6. Отформатируйте полученную таблицу, используя возможности команды Формат/Ячейки… или кнопки на панели Форматирование. 7. Сохраните текущее состояние таблицы.

20

Этап 3. Р а с ч е т ы д л я ф е в р а л я з а к а ж д ы й г о д Итоговые расчеты для каждого года можно разместить ниже основной таблицы, например, как показано на рис. 7. A

B

C

D

E

F

G

H

I

32 33

Годы

Расчеты для февраля

34

1996 г. 1997 г. 1998 г. 1999 г. 2000 г. 2001 г. 2002 г.

35 Кол-во дней с tо < 0

21

15

15

4

8

18

24

36 Кол-во дней с tо > 0

8

3

8

18

19

2

2

37 Кол-во дней с tо = 0

0

2

4

6

2

8

2

38 Среднемесячная tо

-5,3

-5,9

-3,4

1,3

1,1

-1,4

-2,3

4

5

3

4

6

5

4

40 Минимальная tо

-16

-15

-16

-4

-4

-8

-8

41 Размах выборки

20

20

19

8

10

13

12

39 Максимальная tо

Рис. 7. Итоговая таблица на листе Февраль Для построения итоговой таблицы выполните следующие действия: 1. Внесите в ячейки A33 и C33 заголовки: Расчеты за февраль и Годы

2. Отрегулируйте ширину столбца A, перемещая границу между столбцами A и B с помощью левой кнопки мыши или используя команду Формат/Столбец/Ширина… Разместите заголовки по центру соответствую-

щих диапазонов A33 : B33 и C33 : I33 (см. п. 8. этапа 1). 3. Ячейки A35 : A41 заполните заголовками строк таблицы, как показано на рис. 7. 4. Смешанный ряд со значениями от 1996 г. до 2002 г. получите в ячейках C34 : I34 так, как в п. 6. этапа 1, либо скопируйте диапазон ячеек C3 : I3, используя команды Копировать и Вставить меню Правка или соответствующие кнопки панели Стандартная.

21

5. Для определения количества дней с минусовой, плюсовой и нулевой температурой внесите в ячейки C35 : C37, используя Мастер функций, формулы, содержащие функции категории Математические: =СЧЁТЕСЛИ(C3:C31;"<0") =СЧЁТЕСЛИ(C3:C31;">0") =СЧЁТЕСЛИ(C3:C31;"=0")

6. Для определения среднемесячной, максимальной и минимальной температур в феврале внесите, используя Мастер функций, в ячейки C38:C40 формулы, содержащие функции категории Статистические: =СРЗНАЧ(C3:C31) =МАКС(C3:C31) =МИН(C3:C31)

7. Размах выборки вычисляется в ячейке C41 как разность между максимальным и минимальным значениями температур за месяц: =C39 – C40

8. Выделите диапазон ячеек C35 : C41 и скопируйте его, используя маркер заполнения, на блок ячеек D35 : I41. Полученные результаты сравните со значениями на рис. 7. 9. Отформатируйте итоговую таблицу (см. п. 6. этапа 2 ). Для форматирования можно также использовать команду Формат/Автоформат… и из предложенного списка выбрать один из форматов. 10. Сохраните текущее состояние таблицы.

22

Упражнение 1.1. Постройте графики изменения температур по числам февраля (см. [3]): – за 1996 год, – за два последних года. Упражнение 1.2. Поместите итоговую таблицу Расчеты для февраля на другом листе Итоги за февраль (см. [2], этап 2. задачи 2 ). Сравните полученное с рис. 8. A

B

C

D

E

1 Расчеты для февраля 2

F

G

H

Годы 1996 г. 1997 г. 1998 г. 1999 г. 2000 г. 2001 г. 2002 г.

3

Кол-во дней с tо < 0

21

15

15

4

8

18

24

4

Кол-во дней с tо > 0

8

3

8

18

19

2

2

5

Кол-во дней с tо = 0

0

2

4

6

2

8

2

6

Среднемесячная tо

-5,3

-5,9

-3,4

1,3

1,1

-1,4

-2,3

7

Максимальная tо

4

5

3

4

6

5

4

8

Минимальная tо

-16

-15

-16

-4

-4

-8

-8

9

Размах выборки

20

20

19

8

10

13

12

Рис. 8. Итоговая таблица на листе Итоги за февраль Упражнение 1.3. Постройте по таблице Расчеты для февраля на листе

Итоги за февраль гистограмму (см. [3] ) для количества дней с минусовой, плюсовой и нулевой температурами по результатам наблюдений за 1996-2002 годы. Упражнение 1.4. Постройте по таблице Расчеты для февраля на листе

Итоги за февраль графики (см. [3] ) изменения среднемесячной, максимальной и минимальной температур по результатам наблюдений за 1996-2002 годы.

23

ЗАДАЧА 3. ОСАДКИ Данные метеостанции о количестве осадков (в мм), выпавших за каждый месяц в течение трех лет [4] приведены таблице 3. Таблица 3. 1997 г.

1998 г.

1999 г.

Январь

37,2

34,5

43,5

Февраль

11,4

34,1

66,4

Март

16,5

18,4

12,4

Апрель

19,5

20,3

28,4

Май

11,7

45,5

66,3

Июнь

129,1

71,4

60,2

Июль

57,1

152,6

43,8

Август

43,8

96,6

50,6

8,7

74,8

145,2

Октябрь

86,0

14,5

74,9

Ноябрь

12,5

21,0

56,6

Декабрь

21,2

22,3

9,4

Сентябрь

Необходимо создать электронную таблицу, содержащую данные из таблицы 3, а затем на основе этих данных определить: •

общее количество осадков, выпавших за каждый год,

•

среднемесячное количество осадков для каждого года,

•

число засушливых месяцев для каждого года, то есть месяцев, когда выпадало менее 20 мм осадков,

•

суммарное количество осадков для каждого года, выпавших в незасушливые месяцы (то есть в которые выпадало не менее 20 мм осадков),

•

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

24

ЛИТЕРАТУРА 1. Амелина Н.И., Мачулина Л.А., Чердынцева М.И. Практикум по электронным таблицам в экономике. – М.: "Издательство ПРИОР", 2000. 2. Амелина Н.И., Мачулина Л.А.

Методические указания по курсу

"Информатика" для студентов геолого-географического факультета. "Электронные таблицы. Основные возможности Excel". – Ростов-на-Дону, УПЛ РГУ, 2001. 3. Амелина Н.И., Мачулина Л.А.

Методические указания по курсу

"Информатика" для студентов геолого-географического факультета. "Электронные таблицы. Графические возможности Excel". – Ростов-на-Дону, УПЛ РГУ, 2001. 4. Златопольский Д.М. 1700 заданий по Microsoft Excel. – СПб.: БХВПетербург, 2003. 5. Русанова Я.М., Спивак И.Г. Методические указания для студентов геолого-географического факультета по курсу "Информатика". – Ростов-наДону, УПЛ РГУ, 2001. ПРИЛОЖЕНИЕ 1. ФУНКЦИИ ТАБЛИЧНОГО ПРОЦЕССОРА В приложении приведены наиболее часто используемые функции табличных процессоров. Функции разбиты на категории: математические, логические, статистические. Из функций категории «Дата и время» приведены только некоторые функции для работы с датами. В каждой категории имена функций расположены в алфавитном порядке. Список аргументов задается через точку с запятой ";". Аргументы могут отделяться и через запятую "," в зависимости от способа представления вещественных чисел в используемой версии табличного процессора. Математические функции

ABS ( аргумент ) Абсолютная величина аргумента.

25

ACOS ( аргумент ) Арккосинус аргумента – угол, косинус которого равен аргументу, (значение аргумента должно быть от -1 до 1 ). ASIN ( аргумент ) Арксинус аргумента – угол, синус которого равен аргументу, ( значение аргумента должно быть от -1 до 1 ). ATAN ( аргумент ) Арктангенс аргумента – угол, тангенс которого равен аргументу. ATAN2 ( аргумент1 ; аргумент2 ) Арктангенс отношения аргумента1 к аргументу2 – угол, вычисленный по значениям координат. COS ( аргумент ) Косинус аргумента (угла, выраженного в радианах ). EXP ( аргумент ) Экспонента аргумента. LN ( аргумент ) Натуральный логарифм аргумента. LOG10 ( аргумент ) Десятичный логарифм аргумента. SIN ( аргумент ) Синус аргумента (угла, выраженного в радианах ). TAN ( аргумент ) Тангенс аргумента (угла, выраженного в радианах ). ГРАДУСЫ ( угол ) Преобразование радиан в градусы. КОРЕНЬ ( аргумент ) Корень квадратный аргумента. ОКРУГЛ ( аргумент ; число разрядов ) Округление аргумента до указанного числа десятичных разрядов.

26

ОСТАТ ( аргумент1 ; аргумент2 ) Остаток от деления первого аргумента на второй. Результат имеет тот же знак, что и аргумент2. ПИ () [ PI ] Число π. РАДИАНЫ ( угол ) Преобразование градусов в радианы. СУММ ( список ) Сумма аргументов списка. СУММЕСЛИ ( диапазон ; условие) Сумма ячеек внутри диапазона; удовлетворяющих заданному условию. СЧЁТЕСЛИ ( диапазон ; условие) Количество непустых ячеек внутри диапазона, удовлетворяющих заданному условию. ЦЕЛОЕ ( аргумент ) Целая часть аргумента ( дробная часть отбрасывается ). ЧАСТНОЕ ( аргумент ) Частное от деления нацело. Логические функции

ЕСЛИ ( условие ; аргумент1 ; аргумент2 ) Значение аргумента1, если условие истинно, и значение аргумента2, если условие ложно. И ( аргумент1 ; аргумент2 ; ... ) ИСТИНА, если все аргументы истинны, и ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. ИЛИ ( аргумент1 ; аргумент2 ; ... ) ИСТИНА, если хотя бы один из аргументов имеет значение ИСТИНА, и ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

27

НЕ ( аргумент ) Меняет на противоположное значение своего аргумента ( ЛОЖЬ на ИСТИНА, а ИСТИНА на ЛОЖЬ). Статистические функции

МАКС ( список ) Максимальное значение из списка аргументов. МИН ( список ) Минимальное значение из списка аргументов. СРЗНАЧ ( список ) Среднее арифметическое аргументов. СРОТКЛ ( список ) [ STD ] Среднее абсолютных значений отклонений данных в списке от их среднего. СЧЁТ ( список ) Количество чисел в списке аргументов. СЧЁТЗ ( список ) Количество непустых значений в списке. Функции даты

ДАТА (год ; месяц ; день) Дата в числовом формате. ГОД ( дата ) Выделение года из даты. ДЕНЬ ( дата ) Выделение дня из даты. МЕСЯЦ ( дата ) Выделение месяца из даты. СЕГОДНЯ () Текущая дата, установленная в операционной системе.

28

ПРИЛОЖЕНИЕ 2. СООБЩЕНИЯ ОБ ОШИБКАХ #####

Размер ячейки недостаточен для размещения числа или результата

#ДЕЛ/0!

Деление на ноль

#ЗНАЧ!

Недопустимый тип аргумента или операнда

#ИМЯ?

Неверное имя функции или области

#Н/Д

Неопределенные данные

#ПУСТО!

Задано пересечение двух областей, не имеющих общих ячеек

#ССЫЛКА! Недопустимая ссылка на ячейку #ЧИСЛО!

Ошибка в вычислениях