LINEBURG


<< Пред. стр.

страница 45
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

1
Другой вариант записи формул:
для общей суммы: =12500*27
для суммы НДС: =12500*27*20/120
для «чистой» суммы: =12500*27*100/120
377
Базовые информационные технологии Microsoft Excel


метра. Кнопка с изображением знака ? выводит справку о встроенной функции.
Для завершения ввода параметров функции нажимается кнопка ОК, для отказа
от ввода параметров — кнопка Отмена.




Рис. 5.27. Диалоговое окно встроенной функции ЕСЛИ

Параметрами встроенной функции могут быть: константы, ссылки на ячейку
или диапазон ячеек; имена блоков, вычисляемые выражения, в том числе с ис­
пользованием других вложенных функций. Максимально допустимая глубина
вложений встроенных функций в формуле — 7.
Порядок ввода констант:
• числовые константы вводятся с учетом знака числа и разделителя десятич­
ной точки,
• строки текста вводятся без двойных кавычек;
• даты вводятся с принятым разделителем (точка, косая черта или дефис),
либо используется встроенная функция ДАТА (Год, Месяц, День) для преобра­
зования даты в числовой формат;
• логические константы вводятся как числа 1 — истина, 0 — ложь или строки
текста ИСТИНА, ЛОЖЬ (без кавычек), либо как встроенные функции ИСТИНА()
и ЛОЖЬ().
Константы и знаки операций при построении вычисляемых выражений вводят­
ся вручную, ссылки на ячейки или диапазоны ячеек формируются с помощью
курсорного путевождения. Для этого нажимается правая кнопка («красная» кноп­
ка), находящаяся справа от поля ввода. После нажатия «красной» кнопки диа­
логовое окно Мастера функций скрывается, на экран выводится окно построите­
ля выражения. При выводе окна построителя выражения можно:
• свободно перемещать курсор на листе, переходить на другие листы или в
другую открытую рабочую книгу;
• формировать ссылку на ячейку щелчком левой кнопкой мыши;
• формировать ссылку на диапазон ячеек путем выделения диапазона при на­
жатой левой кнопке мыши;
• выводить список имен блоков для выбора с помощью команды меню Вставка •
Имя • Вставить либо нажатием клавиша F3.
378 Глава 5. Электронная таблица Microsoft Excel 2000


Если именованный блок ячеек существует, при выделении соответствующего
диапазона ячеек в формулу автоматически вводится имя блока. Если именован­
ный блок ячеек создается после ввода формулы, команда меню Вставка • Имя •
Применить заменяет ссылку на диапазон ячеек именем блока.

ПРИМЕЧАНИЕ
Ссылка на диапазон ячеек другой открытой книги также создается путем перехода в окно
рабочей книги с помощью команды меню Окно и выделения ячеек курсором. Имена бло­
ков ячеек других рабочих книг в формулах не используются.

Редактирование формул
Содержание формулы можно редактировать, для этого курсор устанавливается
в ячейку с формулой, редактирование выполняется в строке формул. Если при
установке курсора в ячейку с формулой нажать клавишу F2, можно редактиро­
вать текст формулы непосредственно в ячейке.
Для групповых замен в формулах можно воспользоваться командой меню Прав­
ка • Заменить, предварительно выполнив команду меню Сервис • Параметры, на
вкладке Вид указать параметры окна — Формулы. В этом случае вместо пред­
ставления значений в ячейках выводятся формулы.

СОВЕТ
Если формула содержит встроенную функцию, для вызова диалогового окна встроенной
функции курсор устанавливается в строке формул на названии функции, а затем нажима­
ется кнопка со знаком =.

Пример 9
Вычислить предельные расходы на рекламу, принимаемые при налогообложе­
нии (табл. 5.8).

Таблица 5.8. Лимит расходов на рекламу

Объем выручки от реализации Лимит расходов на рекламу
продукции, в год
0-2 000 000 2 % от объема выручки
2 000 001-50 000 000 40 000 + 1 % с суммы выручки, превышающей
2 000 000
50 000 001 и более 520 000 +0,5 % с суммы выручки, превышающей
50 000 000

Последовательность действий.
1. Открыть файл Примеры.ХЬБ с помощью команды меню Файл • Открыть.
2. Вставить, если необходимо, новый лист с помощью команды меню Вставка •
Лист.
379
Базовые информационные технологии Microsoft Excel


3. Переименовать новый лист с помощью команды меню Формат • Лист • Пере­
именовать, лист — Пример 9.
4. Заполнить таблицу:
О в ячейку А1 ввести значение объема выручки;
О в ячейку А2 ввести формулу вида:
=ЕСПИ(АК2000000;А1*0,02; ЕСЛИ(А1<50000001;40000+
(А1-2000000)*0,01;520000+(А1-50000000)*0,005))
Порядок создания формулы.
1. Установить курсор в ячейку А2.
2. Нажать клавишу =.
3. Нажать кнопку fx для вызова Мастера функций.
4. Выбрать категорию функций — Логические, указать функцию — ЕСЛИ.
5. На экран выводится диалоговое окно функции ЕСЛИ для ввода парамет­
ров.
6. Установить курсор в поле Логическоевыражение и нажать «красную»
кнопку.
7. Щелкнуть левой кнопкой мыши на ячейке А1 и дописать выражение для
условия: < 2000000, полный вид условия: А К 2000000.
8. Нажать «красную» кнопку для возврата в диалоговое окно встроенной
функции ЕСЛИ.
9. Установить курсор в поле Значение_если_истина и нажать «красную»
кнопку.
10. Сформировать вычисляемое выражение вида: 40000+(А1-2000000)*0,01. Вы­
ражение формируется вручную, за исключением ссылки на ячейку А1.
11. Нажать «красную» кнопку для возврата в диалоговое окно встроенной
функции ЕСЛИ.
12. Установить курсор в поле Значение_если_ложь.
13. Нажать стрелку поля со списком встроенных функций (слева от строки
формул).
14. Выбрать функцию ЕСЛИ, которая становится вложенной для первой функ­
ции ЕСЛИ, появляется новое диалоговое окно для ввода параметров вло­
женной функции ЕСЛИ.

ВНИМАНИЕ
Иного способа вставки вложенной встроенной функции нет. Если функция отсутствует в
списке, выбирается Другие функции..., выводится окно Мастера функций для выбора кате­
гории и функции.


15. Установить курсор в поле Логические_выражение и нажать «красную»
кнопку.
16. Сформировать условие проверки: А10<50000001.
380 Глава 5. Электронная таблица Microsoft Excel 2000


17. Нажать «красную» кнопку для возврата в диалоговое окно встроенной
функции ЕСЛИ.
18. Установить курсор в поле Значение_если_истина и нажать «красную» кнопку.
19. Сформировать вычисляемое выражение вида: 40000+(А10-2000000)*0,01.
20. Нажать «красную» кнопку для возврата в диалоговое окно встроенной
функции ЕСЛИ.
21. Установить курсор в поле Значение_если_ложь и нажать «красную» кнопку.
22. Сформировать вычисляемое выражение вида:
520000+(А10-50000000)*0,005.
23. Нажать «красную» кнопку для возврата в диалоговое окно встроенной
функции ЕСЛИ.
24. Нажать кнопку ОК для завершения вода формулы.
5. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.

Копирование и перемещение формул
Ячейки с формулами можно копировать и перемещать в любое место — в преде­
лах листа, книги или в другую рабочую книгу. Результат операции зависит от
вида ссылок, используемых в формуле: относительные ссылки автоматически
изменяются, абсолютные ссылки — нет. Признаком абсолютной ссылки по од­
ной или двум координатам является символ $
Для указания абсолютной ссылки следует в процессе ввода или редактирования
формулы установить курсор на ссылку и нажать соответствующее число раз
клавишу F4, например:
• =N3 — относительная ссылка на ячейку с адресом N3;
• =$N$3 — абсолютная ссылка по двум координатам на ячейку с адресом N3;
• =N$3 — абсолютная ссылка по номеру строки на ячейку с адресом N3;
• =$N3 — абсолютная ссылка по номеру столбца на ячейку с адресом N3.

ПРИМЕЧАНИЕ
При копировании формул, использующих имена блоков, ссылки на блоки не изменяются,
их можно считать абсолютными.


Пример 10
Вычислить стоимость товарных запасов и налог на добавленную стоимость.
Последовательность действий.
1. Открыть файл Примеры.XLS с помощью команды меню Файл • Открыть.
2. Вставить новый лист, если необходимо, с помощью команды меню Вставка •
Лист.
3. Переименовать новый лист с помощью команды меню Формат • Лист • Пере­
именовать, лис! — Пример 10.
381
Базовые информационные технологии Microsoft Excel


4. Заполнить таблицу, начиная с ячейки А1 (табл. 5.9).

Таблица 5.9. Товары

Курс у.е. 28,25
(долл.)

Наименование Запас Стоимость В т.ч. НДС, Стоимость
Ед. Цена, руб.
изм. запаса, руб. руб. в у.е.

Шт. 7
Стол 2 500,00р.

Стул Шг. 345,00р. 12

Шт. 2
Диван 7 250,00р.


5. Ввести формулу стоимости запаса в ячейку ЕЗ: = C3*D3.
6. Скопировать формулу ячейки ЕЗ в ячейки Е4:Е5.
7. Ввести формулу суммы НДС в ячейку F3: = ЕЗ*20/120.
8. Скопировать формулу ячейки F3 в ячейки F4:F5.
9. Ввести формулу стоимости товарных запасов в условных единицах в ячейку
G3:=E3/$B$1 (абсолютная ссылка на ячейку В1, в которой содержится значе­
ние курса условной единицы — доллара).
10. Скопировать формулу G3 в ячейки G4:G5. Результирующая таблица — табл. 5.10.
11. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.

Таблица 5.10. Результаты расчетов

Курс у.е. ! 28,25
(долл.) I

Запас Стоимость В т.ч. НДС, Стоимость
Наименование Ед. Цена, руб.
изм. запаса, руб. руб. в у.е.

Стол Шт. 2 500,00р. 7 17 500,00р. 2 916,67 619,47

Шт. 12 4 140,00р. 690,00 146,55
Стул 345,00р.

Диван Шт. 2 14 500,00р. 2 416,67 513,27
7 250,00р.


ВНИМАНИЕ
Если вместо ссылки на отдельную ячейку указана ссылка на ячейки строки или столбца,
Microsoft Excel выбирает именно ту ячейку, которая находится в той же строке (если диа­
пазон является столбцом) или в том же столбце (если диапазон является строкой), что и
ячейка с формулой. Формулы со ссылками на диапазон ячеек строки или столбца долж­
ны вводиться строго в определенной ячейке для выполнения пересечения строк и столб­
цов в формулах.
382 Глава 5. Электронная таблица Microsoft Excel 2000


В условиях предыдущего примера можно использовать одни и те же формулы
для ячеек определенных столбцов:
1. Ввести формулы в ячейки ЕЗ:Е5 — =$C$3:$C$5*$D$3:$D$5.
2. Ввести формулы в ячейки F3:F5 — =$Е$3:$Е$5*20/120.
3. Ввести формулы в ячейки G3:G5 — =$Е$3:$Е$5/$В$1.
4. Создать именованные блоки ячеек столбцов с помощью команды меню Встав­
ка • Имя • Создать • По тексту в строке выше:
О СЗ:С5 — Цена_руб.;
О ЕЗ:Е5 — Запас.
5. Заменить диапазоны ячеек в формулах на имена блоков с помощью команды
меню Вставка • Имя • Применить, выбрать блоки Запас и Цена_руб., нажать
кнопку ОК.
6. Просмотреть формулы в ячейках ЕЗ:Е5.
7. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.

Формулы массива
Microsoft Excel использует особый вид формулы — формулы массива, которые
выполняют вычисления над массивами ячеек. Результат формулы массива за­
писывается либо в массив ячеек (для всех ячеек массива применяется одна и та
же формула), либо в одну ячейку. В отличие от обычных формул ввод и редак­
тирование формулы массива заканчивается нажатием клавиш Ctrl+Shift+Enter, a
сама формула автоматически заключаются в фигурные скобки. Для редактиро­
вания формулы массива следует выделить все ячейки массива.
Для скоростного выделения группы ячеек, содержащих формулу массива, следует:
1. Выбрать любую ячейку массива формул.
2. Выбрать команду меню Правка • Перейти.
3. Нажать кнопку Выделить. Появляется окно Выделение группы ячеек, в кото­
ром следует указать Выделить текущий массив.
4. Нажать кнопку ОК.
В условиях примера 10 можно выполнить вычисления с помощью массива фор­
мул. Последовательность действий.
1. Открыть файл npnMepbi.XLS с помощью команды меню Файл • Открыть.
2. Выбрать лист Пример 10.
3. Выделить блок ячеек ЕЗ:Е5.
4. Ввести формулу массива: {=C3:C5*D3:D5}. Ввод формулы заканчивается на­
жатием клавиш Ctrl+Shift+Enter.
5. Выделить блок ячеек F3:F5.
6. Ввести формулу массива: {=ЕЗ:Е5*20/120}. Ввод формулы массива закан­
чивается нажатием клавиш Ctrl+Shift+Enter.
7. Выделить блок ячеек G3:G5.
383
Базовые информационные технологии Microsoft Excel


8. Ввести формулу массива: {=ЕЗ: Е5/В1}. Ввод формулы массива заканчивает­
ся нажатием клавиш Ctrl+Shift+Enter.
9. Заменить диапазоны ячеек в формулах на имена блоков Запас и Цена_руб. —
команда меню Вставка • Имя • Применить, указать имена блоков и нажать кноп­
ку ОК.
10. Просмотреть формулы в ячейках ЕЗ:Е5.
11. Закрыть файл с сохранением с помощью команды меню Файл • Закрыть.
В формулах массива может использоваться массив констант, который задается
в фигурных скобках. Массив констант может быть одномерным или двумер­
ным. Элементы одного столбца разделяются "запятыми (,), строки разделяются
точками с запятой (;). Массив констант может включать:
• числа, текст, логические значения или значения ошибок (например, # Н / Д ) ;
• числа в массиве — произвольный формат (целые, с десятичной точкой или в
экспоненциальном формате);
• текст в двойных кавычках.
Один массив констант может состоять из элементов разного типа, но не может
содержать специальные символы, например: $ (знак доллара), скобки или % (знак
процента), ссылки на ячейки.
Пример массива констант: {10,20,30,40} — одномерный массив констант; {10,20,
30,40;50,60,70,80} — двумерный массив констанг.
В Microsoft Excel можно использовать и трехмерные ссылки с координатами
ячеек: Лист, Столбец, Строка. Блоки ячеек одной и той же конфигурации, распо­
ложенные на разных листах, составляют трехмерный массив.

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


Внешние ссылки в формулах
Microsoft Excel позволяет включать в формулы внешние ссылки — на ячейки
другой рабочей книги. Перед созданием формулы, содержащей внешнюю ссыл­
ку, необходимо сохранить рабочую книгу, в ячейки которой вводятся формулы,
открыть рабочие книги, с которыми устанавливается связь. При создании фор­
мулы внешняя ссылка строится автоматически при выделении диапазона ячеек
в другой открытой рабочей книге.
Для поддержания правильности использования внешних ссылок используется
команда меню Правка • Связи, которая открывает диалоговое окно Связи (рис. 5.28).
Обновление может осуществляться автоматически при открытии текущей кни­
ги или по запросу. Для автоматического обновления внешних связей следует
выполнить команду меню Сервис • Параметры, на вкладке Вычисления указать
384 Глава 5. Электронная таблица Microsoft Excel 2000


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




ВНИМАНИЕ
Если команда меню Правка • Связи недоступна, книга не содержит формул с внешними
ссылками


Встроенные функции Microsoft Excel
Встроенные функции Microsoft Excel в значительной степени упрощают вычис­
ления в электронных таблицах. Все встроенные функции разделены на катего­
рии: Дата и время, Инженерные, Информационные, Логические, Математические и
тригонометрические, Определенные пользователем, Проверка свойств и значений,
Работа с базой данных, Ссылки и массивы, Статистические, Текстовые, Финансо­
вые.

Категория «Ссылки и массивы»
Ссылка соответствует адресу ячейки или диапазону ячеек электронной табли­
цы. Ссылка на ячейки других рабочей книг или приложений носит название
внешней или удаленной ссылки. В Microsoft Excel используются ссылки раз­
личного стиля, который выбирается с помощью команды меню Сервис • Пара­
метры, вкладка Общие:
• номер строки, номер столбца — R1C1;
• имя столбца, номер строки — А1.
385
Базовые информационные технологии Microsoft Excel


Столбцы обозначаются буквами от А до IV (256 столбцов максимально) или
цифрами 1-256, строки — числами от 1 до 65 536. Ссылка на диапазон ячеек за­
дается как ссылка на верхний левый угол диапазона, далее ставится знак двое­
точия (:), указывается ссылка на правый нижний угол диапазона.

Пример 11
Встроенные функции категории Ссылки и массивы изучаются на конкретных
примерах (см. ниже). Последовательность действий.
1. Открыть файл Примеры.XLS с помощью команды меню Файл • Открыть.
2. Вставить новый лист, если необходимо, с помощью команды меню Вставка •
Лист.
3. Выполнить переименование листа с помощью команды меню Формат • Лист •
Переименовать, лист — Ссылки и массивы.
4. Заполнить значения ячеек в диапазоне С2:Е5 (рис. 5.29).
5. Создать именованный блок для диапазона ячеек С2:Е5 с именем Блок с помо­
щью команды меню Вставка • Имя • Присвоить.

А В D
С Е F О
"прииер|$Е14" 125
1 Асбест

1000 450
2 125 Асбест 45С0 Глина
200 Глина 500
5
3 4 Мел
125 270 Мел ЮСО
4 3 Цемент

<< Пред. стр.

страница 45
(всего 83)

ОГЛАВЛЕНИЕ

След. стр. >>

Copyright © Design by: Sunlight webdesign