Информатика - Борисов

13. Основные правила работысфункциямиExcel

Рис. 13.3

Чтобы создать дополнительные рабочие листы, следует выбрать рабочий лист, перед которым должен быть добавлен новый лист, и дать команду меню Вставка→Лист или воспользоваться контекст- ным меню листов Книги Excel (команда Добавить…). Контекстное меню, кроме того, позволяет удалять, переименовывать, переме- щать, копировать и выделять листы, а также изменять цвет ярлыч- ков.

Изменить порядок следования рабочих листов можно перетаски- ванием ярлычков. Чтобы изменить название рабочего листа, можно также дважды щелкнуть на его ярлычке. После этого следует ввести новое название и нажать клавишу ENTER.

Формулы могут ссылаться на ячейки, находящиеся в других рабо- чих листах. Формат такой «межлистовой» ссылки:

<имя листа>!<адрес>.

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

Программа Excel позволяет также одновременно вводить или ре- дактировать данные на нескольких рабочих листах. Это удобно, ко- гда все рабочие листы содержат одинаковые заголовки или подписи. Чтобы выбрать для работы сразу несколько рабочих листов, надо выбрать первый из этих листов, а затем щелкнуть на ярлычках других листов, удерживая нажатой клавишу SHIFT или CTRL. В этом случае любая операция, проведенная на одном из рабочих листов, автома- тически дублируется на остальных.

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

191

Раздел III. Современные информационные технологии

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

Лист1:Лист3!А1:С12

(диапазон листов от Лист1 до Лист3, блок ячеек от А1 до С12).

13.2. Алгоритм ввода формул

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

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

вформуле ее имя. Это можно сделать вручную или щелчком на соот- ветствующей ячейке по ходу ввода формулы.

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

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

Каждая формула в электронной таблице содержит несколько арифметических действий с ее компонентами. Установлена последо- вательность выполнения арифметических операций. Сначала вы- полняется возведение в степень, затем – умножение и деление и только после этого – вычитание и сложение. Если вы выбираете ме- жду операциями одного уровня (например, между умножением и делением), то следует выполнять их слева направо. Нормальный по- рядок выполнения операций изменяют введением скобок. Операции

вскобках выполняются первыми.

Арифметические формулы могут также содержать операторы сравнения: равно (=), не равно (<>), больше (>), меньше (<), не бо- лее (<=), не менее (>=). Результатом вычисления арифметической формулы является число.

192

13. Основные правила работысфункциямиExcel

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

NOT – логическое отрицание «НЕ»; AND – логическое «И»;

OR – логическое «ИЛИ».

Логические формулы определяют, выражение истинно или лож- но. Истинным выражениям присваивается численная величина 1, а ложным – 0. Таким образом, вычисление логической формулы за- канчивается получением оценки «Истинно» (1) или «Ложно» (0).

По окончании ввода формула в таблице не отображается. Вместо нее в ячейке размещается вычисленное значение. Однако если сде- лать ячейку с формулой текущей, то формулу можно увидеть в строке формул.

При работе с Excel важно не производить никаких вычислений «в уме». Даже если рассчитать значение, хранящееся в ячейке, совсем нетрудно, все равно надо использовать формулу.

Если ячейка таблицы содержит производные данные, следует за- нести в нее формулу, которая их вычисляет. Вычисление результата «в уме» и занесение его в таблицу в виде числа — это неправильная операция, последствия которой проявляются не сразу и могут вы- звать ошибки при дальнейшей работе с таблицей.

У каждой ячейки есть свой адрес. Он однозначно определяется номерами столбца и строки, то есть именем ячейки. Когда, напри- мер, вычисляется значение ячейки D7 как произведение В7 и С7, нужно использовать адреса ячеек, входящих в формулу = В7*С7.

Однако если нужно подсчитать значение в следующей ячейке D8, то опять придется записывать формулу =B8*C8. Это неудобно для больших таблиц, и процесс можно автоматизировать.

Гораздо удобнее было бы записать формулу для всех ячеек столб- ца D, чтобы в них автоматически записывалось произведение соот- ветствующих ячеек столбцов В и С. Формула тогда выглядела бы так: умножить значение, находящееся на две ячейки левее данной, на значение, расположенное в ячейке слева от данной. Адресация по методу «левее», «правее», «ниже», «выше» не требует абсолютного указания адресов ячеек, входящих в формулу и называется относи- тельной адресацией.

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

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

193

Раздел III. Современные информационные технологии

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

Таким образом, ссылка на ячейку, например А1, может быть за- писана в формуле четырьмя способами: А1, $А1, А$1 и $А$1. При заполнении ячеек формулой как относительная рассматривается только та часть адреса, перед которой нет символа «$».

Если же ссылка на ячейку была внесена в формулу методом щелчка на соответствующей ячейке, то выбрать один из четырех возможных вариантов абсолютной и относительной адресации мож- но нажатием клавиши F4.

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

Программа Excel рассматривает содержимое ячейки как формулу, если оно начинается со знака равенства (=). Тем самым, чтобы на- чать ввод формулы в ячейку, достаточно нажать клавишу «=» (см. рис.13.4) в строке формул.

Рис. 13.4

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

(Рис. 13.5).

Это диалоговое окно позволяет выбрать любую стандартную функцию из имеющихся в программе Excel. В списке Категория вы-

194

13. Основные правила работысфункциямиExcel

бирают ту категорию, к которой относится нужная функция, а в окошке Выберите функцию – нужную функцию.

Рис. 13.5

После того как нужная функция выбрана, ее имя заносится в строку формул, а палитра функции изменяется, давая возможность ввести аргументы функции (рис.13.6).

Рис.13.6

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

195

Раздел III. Современные информационные технологии

ется справочная информация: общее описание назначения функции, а также сведения о задаваемых аргументах. Если аргумент указан по- лужирным шрифтом, значит, он является обязательным, а если обычным шрифтом, то его можно опустить. Например, для функции =ЕСЛИ() обязательным является аргумент «Лог_выражение», без указания которого функцию ввести не удастся.

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

Программа Excel допускает вложенность функций, то есть в каче- стве параметра одной функции может быть указано значение другой функции.

13.3. Приемы работы в Excel

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

Ввод формул

Адрес ячейки можно включить в формулу одним щелчком мыши. Например, вместо того чтобы вручную набирать =C6+F6+..., можно сделать следующее: ввести «=»; щелкнуть мышью на ячейке С6 (ее адрес появится в формуле); ввести «+»; щелкнуть на F6 и т. д.

Ввод функций

Вместо того чтобы набирать функции вручную, можно щелкнуть на кнопке fx в строке формул, на экране появится диалоговое окно Мастер функций. С его помощью можно ввести и отредактировать любую функцию. Так как функция суммирования используется в электронных таблицах очень часто, для нее в панели инструментов Стандартная предусмотрена специальная кнопка со значком Авто- сумма (рис 13.7).

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

Например, если выделить ячейки D6:D10 (в ячейках D6:D9 вве- дены числа, а ячейку D10 предполагается использовать для суммы) и щелкнуть на кнопке Автосумма, в строке формул и ячейке D10 поя-

196

13. Основные правила работысфункциямиExcel

вится заготовка формулы =CУMM(D6:D9). Можно при необходи- мости отредактировать эту формулу или зафиксировать результат (щелчком на кнопке с галочкой в строке формул или нажать клави- шу ENTER). Если же дважды щелкнуть на кнопке Автосумма, ре- зультат сразу фиксируется в ячейке.

Рис. 13.7

Копирование формул

Программа Excel позволяет скопировать готовую формулу в смежные ячейки, при этом адреса ячеек будут изменены автоматиче- ски. Для этого нужно выделить ячейку, в которой записана исходная формула. Например, выделите ячейку D6. При выделении ячейки в правом нижнем углу рамки появляется черный квадратик – маркер заполнения. Если установить курсор мыши на маркер, то курсор примет форму черного крестика. Нажать левую кнопку и перетащить маркер заполнения через заполняемые ячейки. Формула будет ско- пирована, причем будут учтены относительные и абсолютные адреса операндов.

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

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

Проценты

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

197

Раздел III. Современные информационные технологии

ли будут умножены на 100 и помечены знаком «%». Если нужно в отображаемых значениях отделить пробелом, например, тысячи от сотен, десятков и единиц, а также отображать значения в дробной части числа с большим или меньшим количеством знаков, следует воспользоваться кнопками Формат с разделителями, Увеличить раз-

рядность и Уменьшить разрядность, которые по умолчанию располо- жены рядом с кнопкой Процентный формат.

Расчетные операции

Расчетные операции выполняются по формулам. Заранее опреде- ленные формулы, которые выполняют вычисления по заданным ве- личинам (аргументам) называются функциями. Эти функции позво- ляют выполнять простые и сложные вычисления. Функция имеет имя (например, COS) и аргументы, которые записываются в круглых скобках следом за именем функции. Скобки – обязательная принад- лежность функции, даже если у нее нет аргументов. Аргументы отде- ляются друг от друга точкой с запятой «;». В качестве аргументов функции могут использоваться числа, адреса ячеек, диапазоны яче- ек, арифметические выражения и функции. Смысл и порядок следо- вания аргументов однозначно определены описанием функции, со- ставленным ее автором. Например, если в ячейке F3 записана фор- мула с функцией возведения в степень =СТЕПЕНЬ(ВЗ;5), значени- ем этой ячейки будет значение ячейки ВЗ, возведенное в степень 5.

Функция, записанная в формуле, возвращает арифметическое или логическое значение. Существуют функции, которые не возвра- щают значение, а выполняют некоторые операции (например, объе- диняют текстовые строки), а также функции без аргументов (напри- мер, функция ПИ() возвращает число π = 3,1416...).

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

Функции программы Excel разделены на категории: Финансовые; Да-

та и время; Математические; Статистические; Ссылки и массивы; Рабо- тасбазойданных; Текстовые; Логические; Проверкасвойствизначений.

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

Функции Дата и время используют особенности хранения даты в виде последовательных чисел. По умолчанию дате 1 января 1900 года соответствует порядковый номер 1, а 1 января 2013 года — 41275, так как интервал между этими датами в днях равен 41 275. Кроме даты программа Excel сохраняет время в виде десятичной дроби (время является частью даты). Поскольку даты и значения времени пред-

198

13. Основные правила работысфункциямиExcel

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

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

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

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

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

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

Задание 13.1. Использование логических функций

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

Пусть налог исчисляется по прогрессивной шкале следующим образом: с зарплаты, не превышающей 1000 руб., налог составляет 13%, а с части зарплаты, превышающей 1000 руб., взыскивается на- лог 20% от этой части.

Порядок выполнения задания.

1.Сначала определим исходные данные задачи: фамилии ра- ботников (текст) и размер зарплаты (число с двумя цифрами в дробной части).

2.Нужно рассчитать величины подоходного налога и получаемые каждымработникомсуммы.

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

199

Раздел III. Современные информационные технологии

4.В ячейку СЗ поместите формулу расчета величины подоходного налога, вычисляемого по прогрессивной шкале. Для этого выделите ячейкуСЗивведитеформулу

5.=ЕСЛИ( ВЗ<=1000; ВЗ*0,13; 1000*0,13+(ВЗ-1000)*0,20 ).

6.В этой формуле проверяется условие В3<=1000 (зарплата мень- ше, либоравна1000 руб.).

7.Если условие соблюдается, то налог вычисляется по формуле

В3*0,13.

8.Если условие ложно, то налог вычисляется по формуле

1000*0,13+(ВЗ-1000)*0,2 (13% отсуммызарплатыв1000 руб. + 20% от суммы, превышающей1000 руб.).

9.В ячейку D3 введите формулу =ВЗ-СЗ для определения суммы разностизарплатыиналога.

10.СкопируйтеформулыиздиапазонаC3:D3 вдиапазонC4:D6.

11.В ячейку В7 введите формулы суммирования результата по столбцу В, для чего, выделив ячейки ВЗ:В7 (плюс одна ячейка), щелк- нитекнопку«Автосумма» впанелиинструментовСтандартная.

12.СкопируйтеформулувычислениясуммыстолбцаизВ7 вC7:D7.

13.Оформите таблицу, выделив диапазон A2:D7 и выбрав команду Автоформат… вменюФормат. ВдиалоговомокнеАвтоформатизспи- ска форматов выберите вариант Финансовый 3 и щелкните кнопку «ОК». Измените формат отображения значений в ячейках B3:D7, для чего, выделив этот диапазон, выберите в меню Формат команду ячей- ки, затемв диалоговом окне Форматячееквыберите Финансовый фор- мат, в поле Число десятичных знаков задайте отображение двух цифр в дробной части, в поле Обозначение выберите р и щелкните кнопку «ОК» для применения заданного формата ячеек. После этого таблица будетиметьследующийвид.

РЕЗУЛЬТАТЫ РАБОТЫ ПРЕДЪЯВИТЬ ПРЕПОДАВАТЕЛЮ.

200