Для студентов EXCEL

форматы позволяют контролировать появление нежелательных числовых значений в вычислениях.

5.Следующий формат выводит число в зависимости от его значения

вразном виде и различным цветом.

5.1. Создать следующий формат ячейки

[Красный][>0](#,##p.);[Синий][<0](0,00р.);[Фиолетовый]_(—_0_0_р_._)

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

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

Тема 5. Формулы и функции в EXCEL

Цель занятия: освоить механизм вычислений как с помощью простых формул, так и с применением функций.

Формулы

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

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

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

Excel поддерживает арифметические операции, операторы сравнения и операторы связи.

Арифметические операции — сложение (+), умножение (*), нахождение процента (%), вычитание (–), деление (/), возведение в степень (^).

Операторы сравнения — равно (=), меньше (<), больше (>), меньше или равно (<=), больше или равно (>=), не равно (<>).

93

К операторам связи относятся диапазон (:), объединение (;) и оператор соединения текстов (&).

Результат вычислений по формуле — числовое или логическое значение в ячейке. Числовое значение формируется в случае использования только арифметических операций, а логическое значение (истина/ложь) — при использовании логических операций или операций сравнения. При вычислении учитывается приоритет выполнения операций, принятый в математике. После завершения ввода формулы в ячейке появляется значение, соответствующее результату вычислений по формуле.

Создание простых формул

1.Вставить формулу для вычисления в ячейке А4 следующего результата 25-А1+АЗ.

1.1.Ввести любые числа в ячейки А1 и A3.

1.2.В ячейке A4 начать ввод формулы со знака =.

1.3.Ввести число 25, затем оператор (знак –).

1.4.Ввести ссылку (адрес1) на первый операнд, набрав его в текущей ячейке, или в строке формул или щелчком мыши по нужной ячейке (А1).

1.5.Ввести следующий оператор (знак +).

1.6.Ввести адрес второго операнда (ячейка A3) в формуле.

1.7.Завершить ввод формулы

				нажатием клавиши и в ячейке
A4 получится результат.
2.			Ввести в соответствующие ячейки
			B		C
	1		Забронировано мест		41
	2		Стоимость одного билета		95 000
	3		Стоимость экскурсии		=C1*C2
3.			Изменить значение в C1 на 30, Еxcel пересчитает значение в C3.
4.			Изменить значение в C2 на 47.

1 Адреса ячеек вводятся только латинскими буквами!

94

5. Выделить ячейки C1:CЗ, подвести курсор к границе и, удерживая левую кнопку мыши, скопировать значения в соседние ячейки. Изменится ли результат вычислений?

Тиражирование формул при помощи маркера автозаполнения

Область ячеек (ячейка) может быть размножена с помощью МАРКЕРА АВТОЗАПОЛНЕНИЯ. Как было показано в предыдущем разделе, маркер

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

Часто бывает необходимо размножать не только данные, но и формулы, содержащие адресные ссылки. Процесс тиражирования формул при помощи маркера заполнения позволяет копировать формулу при одновременном изменении адресных ссылок в формуле.

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

1.Ввести в ячейки A1 : E1 любые двухзначные числа

2.Ввести в ячейку A2 формулу =A1^2, нажать .

3.Установить курсор в ниж-

нем правом углу ячейки A2 и, когда он примет вид черного крестика (+), удерживая его ле-

+вой кнопкой мыши, растиражи-

маркер автозаполнения	ровать формулу на диапазон яче-
ек A2 : E2. После этого в ячейках A2 : E2 должны появиться квадра-
ты чисел, расположенных в диапазоне A1 : E1.

4.Установить курсор в ячейке D2 и убедиться, что в этой ячейке формула имеет вид = D1^2, т.е. при тиражировании произошло верное изменение адреса ячеек.

5.Проверить, верно ли изменились относительные адреса в полученных формулах для других ячеек диапазона A2 : E2

95

Функции Excel

При использовании формул для более сложных расчетов Excel имеет средство автоматизации построения формул — МАСТЕР ФУНКЦИЙ. Мастер функций позволяет построить любую комбинацию вложенных функций, отслеживая правильность завершения каждой из них. В процессе работы пользователь отвечает на вопросы мастера. В зависимости от ответов мастер включает необходимые функции. Вызов мастера функций осуществляется командой Вставка| Функция.

Создание функции мастером осуществляется за два шага.

На первом шаге активизируется диалоговое окно выбора из множества функций Excel требуемой функции.

В списке КАТЕГОРИЯ приведены категории встроенных в Excel функций. При выборе соответствующей категории в списке Функция появляется перечень функций, принадлежащих выбранной категории.

ФИНАНСОВЫЕ функции осуществляют такие расчеты, как вычисление суммы платежа по ссуде, величину выплаты прибыли на вложения и др.

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

МАТЕМАТИЧЕСКИЕ функции выполняют простые и сложные математические вычисления, например вычисление суммы диапазона ячеек, абсолютной величины числа, округление чисел и др.

СТАТИСТИЧЕСКИЕ функции позволяют выполнять статистический анализ данных. Например, можно определить среднее значение и дисперсию по выборке и многое другое.

Функции ССЫЛКИ И МАССИВЫ позволяют осуществить поиск данных в списках или таблицах, найти ссылку на ячейку в массиве. Например, для поиска значения в строке таблицы используется функция ГПР.

Функции РАБОТЫ С БАЗАМИ ДАННЫХ можно использовать для выполнения расчетов и для отбора записей по условию.

ТЕКСТОВЫЕ функции предоставляют пользователю возможность обработки текста. Например, можно объединить несколько строк с помощью функции СЦЕПИТЬ.

96

ЛОГИЧЕСКИЕ функции предназначены для проверки одного или нескольких условий. Например, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое, если оно ложно.

Функции ПРОВЕРКА СВОЙСТВ И ЗНАЧЕНИЙ предназначены для определения данных, хранимых в ячейке. Эти функции проверяют значения в ячейке по условию и возвращают в зависимости от результата зна-

чения ИСТИНА или ЛОЖЬ.

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

1.Ввести в ячейки А1: A7 любые числа.

1.1.Находясь в ячейке A8 нажать кнопку Автосумма (∑), а затем

.

2.Ввести в ячейки A1: E1 любые числа.

2.1. Находясь в ячейке F1 нажать кнопку Автосумма (∑), а затем

.

97

3. Находясь в ячейке B2 нажать кнопку Автосумма (∑), выделить диапазон A1: E1 и нажать . Значения сумм в ячейках F1 и B2 должны совпасть.

Функция ЕСЛИ, принадлежащая к категории ЛОГИЧЕСКИЕ, реализует возможность задания альтернативных значений в текущую ячейку. Функция требует задания трех аргументов, первый из которых обычно принимает логическое значение. Как правило, в качестве этого аргумента используется операция сравнения. Если значение первого аргумента истинно (отлично от нуля), то в ячейку записывается второй аргумент, в противном случае — третий.

Относящаяся к категории ССЫЛКИ И МАССИВЫ функция ПРОСМОТР имеет две синтаксические формы: вектор и массив. Векторная форма просматривает диапазон, в который входят значения только одной строки или одного столбца (так называемый вектор), в поисках определенного значения и возвращает значение из другого столбца или строки. Форма массива функции ПРОСМОТР просматривает первую строку или первый столбец массива, находит указанное значение и возвращает значение из аналогичной позиции последней строки или столбца массива.

Гиперссылки в Excel

Гиперссылки организуют единообразные способы перемещения в пределах документа и между документами, что позволяет пользователю осуществлять разнообразные переходы. При этом перемещения выполняются как между электронными таблицами процессора MS Excel, так и среди других приложений Office. Применение гиперссылок позволяет, например, создать оглавление. При активизации привязанной к кнопке гиперссылки возможен переход от диаграммы к электронной таблице с дополнительными данными. Гиперссылки могут находиться в ячейках, быть присоединенными к изображениям, кнопкам и другим объектам.

Создание гиперссылки осуществляется двумя способами: с использованием диалогового окна команды Вставка| Гиперссылка или функции

Гиперссылка.

Использование команды Вставка| Гиперссылка активизирует диалоговое окно Добавить гиперссылку и является стандартным способом создания гиперссылок в приложениях семейства Office.

98

После ввода необходимых данных гиперссылка присоединяется к текущему объекту. Если адресат гиперссылки — другая электронная таблица, то это диалоговое окно используется для задания имени электронной таблицы или объекта в ней. В качестве объекта выступает именованный диапазон, лист или адрес ячейки таблицы. Для облегчения поиска требуемых объектов в другой электронной таблице используется кнопка Обзор, нажатие которой активизирует список листов и диапазонов.

Применение функции Гиперссылка позволяет динамически редактировать адресат гиперссылки в зависимости от изменения тех или иных данных в электронной таблице. Функция Гиперссылка имеет два аргумента: Адрес документа и Имя. Первый — это адресат, которым может быть электронная таблица MS Excel, Webстраница или другое приложение (адрес документа в Интернете). Часто в качестве этого аргумента указывается также и URL. Второй — текст, отображаемый в ячейке с гиперссылкой.

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

Тема 6. Автозаполнение

Цель занятия: научиться создавать ряды данных различного вида и назначения.

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

1. Получить арифметическую прогрессию от 1 до 31 с шагом 3. Для этого ввести в ячейку I1 число 1, выделить ее и выбрать команду

Правка| Заполнить| Прогрессия| Арифметическая| По столбцам| Шаг|

3| Предельное значение| 31.

2. Получить геометрическую прогрессию от 2 до 1024 с шагом 2. Для этого ввести в ячейку J1 число 2, выделить ее и выбрать команду

Правка| Заполнить| Прогрессия| Геометрическая| По строкам| Шаг| 2| Предельное значение| 1024.

99

3. Получить последовательность дат, начиная с 01.01.1980 до 01.01.2007 с шагом 1 месяц. Для этого ввести в ячейку J2 число 01.01.1980, выделить ее и выбрать команду Правка| Заполнить| Про-

грессия| Даты| По строкам| Шаг| 1| Предельное значение 01.01.2007.

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

4.Ввести в ячейки Е8, Е9 цифры 0,2 и 0,3. Выделить две ячейки Е8, Е9 и, удерживая маркер автозаполнения, продолжить выделение до ячейки Е15, получив в результате арифметическую прогрессию с шагом 0,1

5.Ввести в ячейки A1, A2 цифры 0 и –3. Выделить две ячейки A1, A2 и, удерживая маркер автозаполнения, продолжить выделение до A7. В результате в ячейках A1:A7 должна появиться арифметическая прогрессия отрицательных чисел

6.Ввести в ячейки Е5, F5 даты 01.01.2001 и 01.05.2001 и аналогичным образом получить последовательность дат в ячейках Е5:К5

7.Ввести в ячейку A21 следующую информацию: Ряд1. Выделить ее, и используя маркер автозаполнения, заполнить диапазон ячеек

А21:А30.

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

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

9.Последовательно ввести в ячейки A21:А27: Красный, Оранжевый, Желтый, Зеленый, Голубой, Синий, Фиолетовый. Выделить тек-

стовую последовательность, выбрать команду Сервис| Параметры |

Списки| Новый список| Импорт списка из ячеек | $A$21:$A$27| Им-

порт| Добавить|Ок. Ввести в любую ячейку Голубой, выделить эту ячейку и, используя маркер автозаполнения, получить новую последовательность.

Тема 7. Адресация в EXCEL

Цель занятия: выяснить механизм применения относительных, абсолютных и смешанных ссылок.

100

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

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

Абсолютный адрес задается путем указания знака $ перед номером столбца и/или строки. Допустимы следующие записи адреса ячейки: $АЗ, А$3, $А$3 или адреса диапазона $А$3:$В$5.

Относительный адрес включает заголовки строки и столбца и автоматически пересчитывается при перемещении формулы с учетом количества строк и столбцов, на которые была перенесена формула. Если перед номером столбца стоит знак $, то ссылка на номер столбца является абсолютной, т. е. номер столбца не изменяется при копировании или перемещении формулы. Если $ стоит перед номером строки, то номер строки является абсолютной ссылкой и не изменяется при распространении формулы. Если $ стоит и перед номером столбца, и перед номером строки, то происходит ссылка на указанную ячейку, причем адрес ячейки не изменяется при распространении формулы.

Для создания абсолютной ссылки удобно использовать клавишу абсолютной ссылки F4, которая осуществляет преобразование относительной ссылки в абсолютную и наоборот. Знак $ можно поставить в нужном месте, если при наборе адреса последовательно нажимать клавишу F4. Последовательное нажатие F4 будет добавлять или убирать знак перед номером столбца или строки в ссылке (С$2 или $С2 — так называемые смешанные ссылки).

1. Используя абсолютную и относительную адресации, создать и отформатировать таблицу умножения как приведено ниже.

1.1.В строку 2 ввести последовательность цифр от 1 до 9.

1.2.В столбец B также ввести последовательность цифр от 1 до 9.

Вячейку C3 ввести требуемую формулу (=C2*B3). Если скопировать эту формулу во все ячейки диапазона, результат будет неверным. Чтобы разобраться, что произошло, нужно установить курсор в любую из ячеек диапазона. Для определенности возьмем ячейку С4. В этой ячейке находится формула (=C3*B3). Это значит, что при перемещении формулы на одну ячейку вниз, адрес автоматически увеличился на единицу. Для нас важно, чтобы все значения брались из второй строки,

101

поэтому нужно запретить ее изменение. Ставим знак абсолютного адреса перед номером строки и получаем в ячейке С3 формулу (=C$2*B3). Пробуем копировать эту формулу во все ячейки диапазона и снова получаем ошибку. На этот раз нужно запретить изменение адреса столбца, что мы и сделаем.

1.3.Итоговая формула в ячейке С3 должна иметь следующий вид: (=C$2*$B3). Теперь можно скопировать эту формулу во все ячейки диапазона и быть уверенным, что результат получиться правильный.

1.4.Отформатировать таблицу.

2. Применяя абсолютную и относительную адресации, задать в таблице формулу для перевода суммы выручки туристических фирм из белорусских рублей в доллары ($) и евро (€).

2.1.В ячейку B2 ввести текущую дату, используя функцию =СЕГОДНЯ() и задать необходимый формат ячейки.

2.2.В ячейки B3 и B4 ввести курсы доллара и евро и задать необходимый формат ячейки.

2.3.В ячейки D3 и E3 ввести суммы выручки фирм Трио и Пилигрим в белорусских рублях и задать необходимые форматы ячеек (BR).

102