Ревчук И.Н. Практикум по MS Excel для экономистов

Подождите немного. Документ загружается.

21 22

2. Составление формул

Основные сведения о формулах

Формула, вводимая в ячейку, может содержать следую-

щие элементы:

• операторы (+, –, /, *, ^, &, %, =, >, >= ,<, <=, <>, : , запятая,

одиночный пробел). Последние три оператора – операторы

ссылки. Двоеточие – оператор диапазона. Запятая – оператор

объединения. Пробел – оператор пересечения;

• ссылки на ячейки. Сюда входят именованные ячейки и

диапазоны, относящиеся как к текущему рабочему листу, так и

к ячейкам другого листа текущей рабочей книги и даже ячей-

кам листов другой рабочей книги;

• константы (числовые, текстовые, дата и время);

• функции и их аргументы;

• скобки. Задают порядок выполнения действий в формуле.

Ввод формул можно осуществить вручную. При ручном

вводе можно одновременно редактировать вводимую формулу.

Завершение ввода формулы

– ENTER (за исключением ввода

табличных формул). В ячейке обычно после этого появляется

результат вычислений, а в строке формул видна сама формула.

При вводе ссылок на ячейки и их диапазоны желательно

производить их ввод с использованием мыши.

Для вставки имени в формулу следует выполнить коман-

ду Вставка/Имя/Вставить.

Чаще всего формулы

не имеют пробелов. Однако при

вводе формул все же можно использовать пробелы и разрывы

строк для улучшения читаемости формулы. Для разрыва стро-

ки следует использовать сочетание ALT+ENTER.

Формула не может содержать более 1024 символов.

Редактирование формул можно выполнить одним из сле-

дующих способов:

• двойной щелчок при курсоре, расположенном в ячейке с

редактируемой формулой;

• нажать F2;

• выделить ячейку с формулой и перейти в строку формул;

• щелкнуть по кнопке Изменить формулу в строке формул (=).

При редактировании формулы можно выделять несколько сим-

волов либо с помощью мыши, либо с помощью клавиш со

стрелками при нажатой клавише SHIFT. Можно использовать

клавиши Home, End.

Ниже приведена таблица с приоритетами операторов в формулах.

Символ Оператор

Приоритет

– Отрицание 1

% Процент 2

^ Возведение в степень 3

*, / Умножение и деление 4

+, – Сложение и вычитание 5

& Объединение текстовых строк в одну 6

=, <, >, <=, >=, <> Сравнение 7

Для изменения порядка выполнения операций использу-

ются круглые скобки. Вычисление по формуле происходит сразу

же после ввода (ENTER).

Существует четыре типа ссылок:

• относительные А1. При копировании формулы ссылка на

ячейку изменяется в соответствии с новым расположением ячей-

ки со скопированной формулой;

• абсолютные $A$1. При копировании формулы ссылка на

ячейку не меняется

;

• абсолютная строка $A1;

• абсолютный столбец A$1.

Знак доллара в соответствующем месте ссылки можно по-

лучить с использованием клавиши F4.

По умолчанию Excel использует тип ссылок А1. Однако

программа поддерживает и другую систему записи – R1C1. В

соответствии с этой системой ячейка А1 имеет адрес R1C1 и т.д.

Для изменения системы записи следует выполнить команду

Сервис/Параметры и на вкладке Общие установить или убрать

соответствующий флажок.

23 24

В формуле могут использоваться ссылки на ячейки дру-

гих листов данной рабочей книги или на ячейки листов другой

рабочей книги:

=Лист2!А1+1

=[Бюджет.xls]Лист1!А1+1.

Если в имени рабочей книги присутствуют пробелы, имя

книги и имя рабочего листа следует заключить в одинарные

кавычки:

=’[Анализ бюджета.xls]Лист1’!А1+1.

Если рабочая

книга, на которую задана ссылка, закрыта,

необходимо добавить полный путь к файлу этой книги:

=’C:\MSOffice\Excel\Анализ бюджета.xls]Лист1’!А1+1.

Совсем не обязательно записывать такие строки вруч-

ную. При формировании формулы в случае открытых книг

можно выполнять переходы на нужные листы и книги кнопкой

мыши.

При копировании формулы Excel автоматически изменя-

ет ссылки

на ячейки при вставке формулы в новую ячейку. Ес-

ли этого следует избежать, то можно скопировать формулу в

режиме редактирования как обычный текст и затем вставить

формулу в нужную ячейку. Таким же образом можно копиро-

вать при необходимости часть формулы.

Иногда требуется преобразовать формулу в значение.

Для этого следует использовать копирование

нужных ячеек, а

затем выполнить команду Правка/Специальная вставка и

выбрать переключатель Значения.

Чтобы скрыть формулы, следует выполнить команду

Формат/Ячейки, перейти на вкладку Защита, установить

флажок Скрыть формулы (рисунок 14), в меню Сервис от-

крыть подменю Защита и выбрать команду Защитить лист (с

паролем)

Рисунок 14

Ниже приведены ошибки, возникающие при вводе формул:

Значение

ошибки

Пояснения

#ДЕЛ/0! Деление на ноль

#ИМЯ? В формуле используется имя, которое Excel не

воспринимает

#Н/Д Формула обращается к ячейке (прямо или косвен-

но), в которой используется функция НД, что

служит сигналом обнаружения неопределенных

данных.

#ПУСТО! Задано пустое пересечение диапазонов

#ССЫЛ! Недопустимая ссылка на ячейку (например, ячей-

ка удалена)

#ЧИСЛО! Возникли проблемы со значением. Например,

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

быть введено положительное число

#ЗНАЧ! Используется недопустимый тип аргумента

Иногда при работе с формулами Excel выдает сообщение

об обнаружении циклических ссылок. Такая ссылка появляется,

когда формула содержит прямую или косвенную ссылку на соб-

ственное значение. Обычно появление сообщений такого рода

связано с ошибочным вводом. Если же нужны итерационные

25 26

вычисления, то следует установить флажок Итерации на

вкладке Вычисления окна диалога Сервис/Параметры.

Использование имен

Имя можно рассматривать как идентификатор какого-

либо элемента рабочей книги. Имя может быть присвоено

ячейке, диапазону, диаграмме, формуле и т.д. Преимущества

использования имен:

• легче понять смысл формулы;

• описательное имя диапазона легче

запомнить;

•

можно легко перемещаться по рабо-

чему листу, используя поле Имя в

левой части строки формул;

• при выделении именованной ячейки

в поле Имя получаем имя этого эле-

мента;

• легче написать формулу, используя

команду Вставка/Имя/Вставить.

Чтобы присвоить имя ячейке или

диапазону, следует выделить нужный

элемент и выполнить команду

Встав-

ка/Имя/Присвоить. Затем ввести имя

в соответствующем текстовом поле.

Если продолжаем присваивать имена, то щелкнем по кнопке

Добавить и продолжим процесс. Количество имен не ограни-

чено. Имя можно задавать и пре-

рывистому диапазону ячеек.

Присвоить имя можно с по-

мощью поля Имя. Для этого сле-

дует выделить диапазон, открыть

поле Имя и ввести имя.

Правила создания имен:

1) имена не должны содержать

пробелов;

2) первый символ – буква;

3) количество символов – не

более 255;

4) для разделения слов внутри имени не следует использо-

вать других символов кроме подчеркивания и точки;

5) регистр не имеет значения.

Имена могут присваиваться автоматически. Чтобы при-

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

текст и ячейки, которым должно быть присвоено имя. Текст

имен должен находится в смежных ячейках относительно ячеек,

которым следует назначить имена. Например, в ячейках А1:А12

находятся названия месяцев.

Чтобы ячейкам В1:В12 были

присвоены соответствующие

имена, выделяем диапазон

А1:В12 и выполняем

команду

Вставка/Имя/Создать. Обычно

программа правильно распо-

знает нужный вариант при-

сваивания имен. В противном

случае следует вручную уста-

новить нужный флажок.

Иногда имеет смысл

присвоить имя целому столбцу или строке:

=СУММ(ПРОДАЖИ) вместо =СУММ(А:А).

Чтобы назначить имя диапазону А1:С3, который распро-

страняется на Лист1, Лист

2, Лист3, можно выполнить такие дей-

ствия:

1) активизировать Лист1;

2) выполнить команду Вставка/Имя/Присвоить;

3) ввести имя;

4) активизировать поле Формула;

5) удерживая нажатой клавишу Shift, щелкнуть на ярлыке

Лист3. При этом получим ссылку:

=’Лист1:Лист3’!$A$1;

6) выделить диапазон А1:С3 на листе Лист1 еще один раз. В

поле Формула

появится запись:

=’Лист1:Лист3’!$A$1:$C$3;

7) Ok

27 28

Имя, присваиваемое ячейке или диапазону, обычно мо-

жет использоваться на любом из листов рабочей книги. Такое

имя называется именем уровня рабочей книги (или глобаль-

ным именем). По умолчанию все создаваемые имена именно

таковы.

Чтобы создать имя уровня рабочего листа, следует вы-

полнить все те же команды по созданию имени. Однако

в поле

Имя перед именем объекта следует задать имя листа, за кото-

рым следует восклицательный знак.

Чтобы использовать имя в формуле, следует выполнить

команду Вставка/Имя/Вставить, затем выбрать нужное имя,

нажать Ok и продолжать запись формулы.

Примеры решения задач

Задача 2.1. На рисунке 15 в ячейках А3:А20 расположены чис-

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

рублях. Получить в ячейках В3:D20 эквивалентные суммы в

российских рублях, евро и долларах США. Курсы указанных

валют по отношению к белорусскому рублю приведены в

ячейках F3:H3. Отформатировать полученную таблицу так, как

показано на рисунке 16.

Решение.

Введем в ячейки В3:D3 формулы:

=A3/$F$3

=A3/$G$3

=A3/$H$3

соответственно и распространим их на диапазон В4:D20 (рису-

нок 15). Здесь использованы абсолютные ссылки на ячейки с

курсами валют. Отформатируем числовые данные в ячейках

А3:D20 так, как показано на рисунке 16. Для этого следует вы-

делить соответствующий столбец, выполнить команду

ФОРМАТ/ЯЧЕЙКИ и на вкладке ЧИСЛО

выбрать соответст-

вующий числовой формат (рисунок 17).

Рисунок 15

Рисунок 16

29 30

Рисунок 17

Замечание.Если в ячейку В3 ввести формулу =$A3/F$3, а затем

распространить ее сначала на ячейки С3:D3, а затем – на диа-

пазон В4:D20, то получим тот же результат.

Задача 2.2. Рабочий лист по расходам содержит в ячейках

B2:B4 фактические расходы за январь, февраль, март: 1500,

500 и 500 соответственно. Ячейки C2:C4 содержат данные по

предполагаемым расходам за

те же периоды: 900, 900 и 925

(рисунок 18). Составить формулу для проверки соответствия

бюджету расходов определенного месяца, выводя сообщения

вида: «Превышение бюджета» или «В пределах бюджета»

Решение

Рисунок 18

Установим курсор в ячейку D2 и введем в нее формулу:

=ЕСЛИ(B2>C2;"Превышение бюджета";

"В пределах бюджета").

Рисунок 19

Выполняем затем распространение этой формулы на оставшийся

диапазон D3:D4. Результат работы формулы приведен на рисун-

ке 20.

Рисунок 20

Задача 2.3. Пусть требуется назначить буквенную категорию

числам согласно приведенной ниже таблице.

31 32

Средний балл Категория

Больше 89 A

От 80 до 89 B

От 70 до 79 C

От 60 до 69 D

Меньше 60 F

Решение

1 вариант. Воспользуемся вложенными функциями ЕСЛИ:

=ЕСЛИ(A20>89;"A";ЕСЛИ(A20>79;"B";

ЕСЛИ(A20>69;"C";ЕСЛИ(A20>59;"D";"F")))).

В предыдущем примере второе предложение ЕСЛИ является в

то же время аргументом значение_если_ложь для первого пред-

ложения ЕСЛИ. Аналогично, третье предложение ЕСЛИ являет-

ся аргументом значение_если_ложь для второго предложения

ЕСЛИ. Например, если первое лог_выражение (Среднее>89)

имеет значение ИСТИНА, то возвращается значение «A». Если

первое логическое выражение

имеет значение ЛОЖЬ, то

вычисляется второе предложение ЕСЛИ, и так далее.

33 34

2 вариант. Задача решается с ис-

пользованием функций ВПР. Соз-

дадим таблицу, содержащую диа-

пазоны баллов с указанием катего-

рии, причем первый столбец этой

таблицы должен быть отсортиро-

ван по возрастанию. Присвоим

этому диапазону имя Сред-

ний_балл. Установив курсор в

ячейку В20, вводим формулу:

=ВПР(A20;Средний__балл;2).

Задача 2.4. В ячейках А3:А20 расположены числа, представ-

ляющие собой денежные суммы, а в ячейках В3:В20 – вид ва-

люты. Получить в ячейках С3:С20 эквивалентные суммы в бе-

лорусских рублях. Курсы валют по отношению к белорусско-

му рублю приведены в ячейках F3:H3..

Решение

1 вариант. Введем в ячейку С3 формулу:

=ЕСЛИ(B3=$F$2;A3*$F$3;

ЕСЛИ(B3=$G$2;A3*$G$3;

ЕСЛИ(B3=$H$2;A3*$H$3;"нет курса"))).

Здесь предусмотрен случай вывода фразы «нет курса», если

обозначение курса валюты в диапазоне В3:В20 отсутствует в

диапазоне F2:H2. Распространим формулу на диапазон С4:С20.

2 вариант. Рассмотрим вариант решения задачи с использова-

нием функций ГПР и ЕОШИБКА: Введем в ячейку С3

формулу

=ЕСЛИ(ЕОШИБКА(ГПР(B3;$F$2:$H$3;2;ЛОЖЬ));

"нет курса"; ГПР(B3;$F$2:$H$3;2;ЛОЖЬ)*A3),

которую распространим затем на диапазон С4:С20.

Задача 2.5. В ячейках A1:Е1 расположены числа, представляю-

щие собой денежные суммы. Произвести начисление процентов

по этим суммам, если оно производится в соответствии со сле-

дующей таблицей:

Сумма Процент от суммы

до 100000 включительно 5 %

От 100001 до 300000 4 %

От 300001 до 700000 3 %

От 700001 до 1000000 2,5 %

От 1000001 до 5000000 2 %

От 5000001 1 %

сунок

35 36

Решение

1 вариант. Пусть исходные данные (денежные суммы) рас-

положены в диапазоне А1:Е1. Установим курсор в ячейку А2 и

введем в нее формулу:

=ЕСЛИ(A1<=0;0;ЕСЛИ(A1<=100000;0,05*A1;

ЕСЛИ(A1<=300000;0,04*A1;

ЕСЛИ(A1<=700000;0,03*A1;

ЕСЛИ(A1<=1000000;0,025*A1;

ЕСЛИ(A1<=5000000;0,02*A1;0,01*A1)))))).

Выполним затем копирование этой формулы на диапазон

В2:Е2.

Ниже в таблице приведен результат начислений (вторая

строка) для сумм в первой строке:

2 вариант. Задача решается с использованием функции

ГПР. Справочные данные расположим в таблице как пока-

зано ниже, причем в первой строке данные отсортированы

по возрастанию. Этому диапазону присвоим имя «табли-

ца_начислений».

Установим курсор в ячейку А2 и введем в нее формулу:

=ГПР(А1;таблица_начислений;2)*А1.

Распространим формулу на диапазон В2:Е2.

Задача 2.6. В ячейке А2 находится денежная сумма, которую

положили в банк под некоторый процент, указанный в ячейке

В2. Через сколько лет вклад утроится?

Решение. Расположим исходные данные так, как на рисунке 22

(диапазон А1:В2). Ячейкам А2 и В2 присвоим имена Сумма и

Процент соответственно. Предположим, что начисляются слож-

ные

проценты.

1 вариант. В ячейку В4 введем формулу:

=Сумма+Сумма*Процент,

в В5 – формулу

=B4+B4*Процент.

Рисунок 22

В ячейки С4 и С5 введем формулы

=B4/Сумма и =B5/Сумма.

Распространим содержимое ячеек В5 и С5 вниз до тех пор, пока

в очередной ячейке столбца С не получится первое число, рав-

ное или большее 3. Тогда в соответствующей ячейке столбца А

получим нужное количество лет (целое). Результат работы фор-

мулы приведен

на рисунке 23.

37 38

Рисунок 23

2 вариант. Воспользуемся финансовой функцией

КПЕР(ставка;платеж;нз;бз;тип), которая возвращает общее ко-

личество периодов выплаты для данного вклада на основе пе-

риодических постоянных выплат и постоянной процентной

ставки.

В нашем случае ставка равна 7 %, плата не производит-

ся, то есть равна 0, нз равно -1000000, бз равно 3000000:

=КПЕР(Процент;0;-1000000;3000000).

Знак минус

означает, что мы отдаем свои деньги (если бы бра-

ли сумму как кредит, например, то нз было бы положительно).

В ответе получаем 16,24, что означает, что на семнадцатом го-

ду наш вклад утроится. Если определить более точный срок, то

утроение вклада произойдет через 16 лет, два месяца и 28 дней.

3 вариант. Обозначим через х неизвестное количество лет.

Воспользуемся математическими соотношениями, приведен-

ными ниже:

⇒

07,1*10000003000000

07,1ln

3ln

07,1ln*3ln07,13 =⇒=⇒= xx

Применив эту формулу, получим такой же результат.

Задача 2.7. В ячейках А1:Н1 находится информация, полученная

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

бражаются так, что знак «–» расположен справа от числа

(рис.24). Получить в ячейках А2:Н2 числовую информацию для

обработки в Excel.

Рисунок 24

Решение. Воспользуемся функциями обработки текста

ЛЕВСИМВ и ДЛСТР. Поместим курсор в ячейку А2 и введем в

нее формулу:

=ЕСЛИ(ЕЧИСЛО(A1);A1;ЛЕВСИМВ(A1;ДЛСТР(A1)–1)*(–1)).

Функция ЕЧИСЛО возвращает значение ИСТИНА, если значе-

ние является числом. Распространяем формулу из ячейки А2 на

диапазон В2:Н2 (рис. 25).

Рисунок 25

39 40

Задача 2.8. Пусть в ячейках А4:А14 находятся числа, пред-

ставляющие собой денежные суммы. Необходимо вывести эти

суммы соответственно в ячейки В4:В14 так, чтобы запись за-

нимала 25 позиций, причем пустые позиции должны быть за-

полнены знаком «*».

Решение. Воспользуемся функциями обработки текста

ПОВТОР и ДЛСТР. Установим курсор в ячейку В4 и

введем в

нее формулу:

=A4&ПОВТОР("*";20–ДЛСТР(A4))&" руб.".

Рисунок 26

Распространив эту формулу на весь диапазон В5:В14, полу-

чим результат (рис. 26).

Задача 2.9. Задана таблица, содержащая фамилию служащего

и его табельный номер. Вывести фамилию служащего с задан-

ным табельным номером.

Решение. Пусть исходные данные расположены так, как на ри-

сунке 27. В ячейку D2 вводится табельный номер служащего.

Рисунок 27

Введем в ячейку Е2 формулу =ВПР(D2;A2:B12;2;ЛОЖЬ). В ре-

зультате получим:

Задача 2.10. Пусть задана таблица с информацией о продавцах

(фамилия, срок работы, объем продаж) и две дополнительные

таблицы с коэффициентами для вычисления комиссионных в за-

висимости от стажа и объема продаж. Следует произвести на-

числения, подобрав подходящий коэффициент каждому сотруд-

нику.

Решение. Пусть исходные данные расположены так, как на ри-

сунках

28–29.

Поместим курсор в ячейку D2. Для подбора подходящего

коэффициента каждому сотруднику следует воспользоваться

следующей формулой:

=ВПР(C2;ЕСЛИ(B2<3;$G$2:$H$8;$J$2:$K$7);2).