Создание простой таблицы - korshu.ru o_O
Главная
Поиск по ключевым словам:
Похожие работы
Создание простой таблицы - страница №2/5


Автоматизация расчетов. Формулы


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

Формулой называется выражение, в соответствии с которым вычис­ляется значение ячейки.

Программа Excel рассматривает содержимое ячейки как формулу, если оно начинается со знака равенства (=). Тем самым, чтобы начать ввод формулы в ячейку, достаточно нажать клавишу «=». Однако вводить форму­лы более удобно, если в строке формул щелкнуть на кнопке Изменить фор­мулу. В этом случае непосредственно под строкой формул открывается Па­литра формул, содержащая вычисленное значение указанной формулы.

Под формулой понимается набор операндов (чисел, встроенных фун­кций, адресов отдельных ячеек, блоков ячеек), соединенных знаками ма­тематических операций. При составлении формул используются знаки опера­ций «+» - сложение,«-» - вычитание, «*» - умножение,«/» - деление и «^» - возведение в степень. Приоритет этих операций соответствует приоритету, принятому в математике. Функции набираются либо вручную, либо при по­мощи Мастера функций.

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

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

Ссылки на ячейки в формулах могут относиться к текущему рабочему листу, к разным рабочим листам одной рабочей книги, а также к разным лис­там разных книг. Для ссылки на другой лист из данной книги перед именем ячейки следует указать номер листа в форме: ЛистN! адрес ячейки, где к - номер листа, например Лист2!Е4. Пробелы в этой записи не допускаются.

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

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

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

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



Пример. В ячейке А1 находится число. Умножим его на 2 и резуль­тат поместим в Ячейку В1. Порядок действий:

1. Щелкнуть мышью по ячейке В1.

2. Нажать клавишу =.

3. Щелкнуть по ячейке А1.

4. Нажать клавишу *.

5. Нажать клавишу 2.

6. Нажать ОК.

Если изменить число в ячейке А1, то изменится и результат вычисле­ния в В1. Если дважды щелкнуть по ячейке В1, то в ней появится введенная формула, в которой адрес ячейки А1 будет выделен тем же цветом, что и сама ячейка. Это позволяет проверить правильность введенных адресов.

При вводе формул не набирайте имена ячеек вручную. При этом су­ществует опасность набрать адреса символами кириллицы, что недопусти­мо. В ячейке вместо результата вычислений будет выведено сообщение #Имя? Обнаружить эту ошибку можно, дважды щелкнув по ячейке с фор­мулой и проверив, какие адреса не выделены цветом.


    1. Заполнение диапазона формулой. Относительная и абсолютная адресация

Предположим, что в столбце А со второй по 11 строку записаны чис­ла, а в столбце В в ответствующих строках должны стоять их утроенные значения. Формулу =3*А2 ввели в ячейку В2. Чтобы размножить ее, можно воспользоваться следующим способом:

• поставить указатель мыши на нижний правый угол ячейки В2, так, чтобы он превратился в черный крестик;

• нажать левую кнопку мыши и потянуть мышь вниз так, чтобы были выделены ячейки с ВЗ по В11;

• отпустить кнопку мыши.

Теперь в ячейки диапазона с ВЗ по В11 будут записаны формулы, ана­логичные формуле в ячейке В2, но вместо ссылки А2 будут фигурировать ссылки соответственно с A3 по А11, т. е. в ячейке ВЗ будет записана фор­мула =3*АЗ, в В4 =3*А4 и т. д. до В11.

По умолчанию в таблицах для связи с зависимыми ячейками исполь­зуются относительные ссылки. Относительная ссылка - это ссылка, ко­торая автоматически изменяется при изменении адреса ячейки и обознача­ется указанием соответствующих столбца и строки, например, А2.

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

Другой способ размножения формулы:

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

• выбрать пункт меню Правка/Заполнить и далее указать направ­ление заполнения (Вниз, Вверх, Вправо, Влево) в зависимости от поло­жения копируемой ячейки.

Если скопированные формулы содержат относительные ссылки, Microsoft Excel изменяет ссылки в копии формулы, Например, пусть ячейка А12 содержит формулу =СУММ(А2:А11). После копирования в ячейку В12, формула будет ссылаться на соответствующие ячейки этого столбца: =СУММ(В2:В11).

Однако иногда возникают ситуации, когда при заполнении ячеек фор­мулой необходимо ссылаться на одну и ту же ячейку, например, нам нужно умножить ячейки с А2 по А11 не на число 3, а на некий постоянный коэффи­циент, который мы предварительно записали в ячейку Е5, т.е. в ячейке В2 записана формула =Е5*А2. Если мы будем копировать формулу по описан­ным выше правилам в диапазон ВЗ:В11, то получим во всех ячейках кроме В2 значение ноль, так как в ячейках Е6, Е7, Е8 и т.д. ноль. В таком случае используется абсолютная ссылка.

Абсолютная ссылка - это ссылка, не изменяющаяся при измене­нии адреса ячейки. Абсолютные ссылки после копирования формулы не изменяются.

Для того чтобы задать ссылку на ячейку как абсолютную, надо за­дать перед обозначением номера столбца или номера строки символ «$». В нашем примере в ячейку В2 следовало ввести формулу =Е$5*А2, т.е. как бы заблокировать изменение номера строки. Если нужно было бы заблоки­ровать изменение и номера столбца, мы записали бы $Е$5. При заполнении ячеек формулой как относительная рассматривается только та часть адре­са, перед которой нет символа «$».

Таким образом, ссылка на ячейку Е5, может быть записана в форму­ле четырьмя способами: Е5, $Е5, Е$5 и $Е$5. Две последние записи будут верны для нашей задачи.






А

В

В

В

Е

1

Числа

Формула

=3*А2


Формула

=Е5*А2


Формула

=Е$5*А2





2

5

15

120

120




3

10

30

0

240




4

15

45

0

360




5

20

60

0

480

24

6

25

75

0

600




7

30

90

0

720




8

35

105

0

840




9

40

120

0

960




10

45

135

0

1080




11

50

150

0

1200




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

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

• ввести его в поле имени для текущей ячейки;

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

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

• невозможно ввести недопустимое имя (т. е. содержащее пробелы);

• если в ячейке, находящейся выше или слева от текущей находится текст, он предлагается в качестве имени, причем пробелы в нем будут за­менены символами подчеркивания;

• можно откорректировать уже заданные имена.

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

Так как суммирование значений в строке или столбце - самая попу­лярная операция при работе с таблицами, на панели инструментов Стан­дартная расположена кнопка Ʃ (Автосумма).

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

• выделить диапазон ячеек и нажать кнопку Автосумма; при этом:



  • если диапазон расположен в одном столбце, результат появится

в ячейке, расположенной под нижней ячейкой диапазона;

  • если диапазон расположен в одной строке, результат появится в ячейке, расположенной справа от крайней левой ячейки диапазона;

  • если диапазон занимает несколько столбцов и строк, результат

появится в ряду, следующем за нижним рядом диапазона.

• Щелкнуть по ячейке, в которой хотите увидеть сумму, а затем по кнопке Автосумма. Появится формула вида =СУММ (диапазон), причем диапазон будет выделен как в формуле, так и в таблице. Выделите мышью диапазон суммирования и нажмите ENTER.





    1. Мастер функций

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

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

• Пунктом меню Вставка/Функция.

• Щелчком по кнопке Вставка функции панели элементов Стан­дартная.

• Щелчком по кнопке Вставка функции строки формул.

• В режиме ввода формулы вместо поля имени крайним левым по­лем строки формул является поле функций. Оно содержит список после­дних использованных функций. Последняя строка в списке - «Другие функ­ции...». Если выбрать ее, вызывается Мастер функций.

Мастер функций работает в два этапа. На первом этапе окно мастера функций содержит два списка. Левый список - это список категорий функ­ций, правый - список функций, относящихся к данной категории. В нижней части окна дается синтаксис функции и ее краткое описание.

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

Если выбрать функцию и нажать ENTER, то Мастер перейдет к сле­дующему шагу. На этом этапе вводятся аргументы функций, причем каждо­му параметру дается полное описание. Как и в режиме ввода информации в ячейку, можно пользоваться мышью для ввода адресов ячеек. Справа от по­лей ввода после знака равенства выводится значение аргумента, а внизу по возможности значение функции. Если тип аргумента не соответствует требу­емому, то будет выдана ошибка #3нач?, выделенная красным цветом.

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

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


Математические и логические функции

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

К логическим функциям относятся функции И и ИЛИ, выполняющие, соответственно, логическое сложение и умножение аргументов, которые являются логическими (булевыми) данными, т. е. ИСТИНА или ЛОЖЬ. Также логической является функция ЕСЛИ, возвращающая одно из двух значений в зависимости от первого аргумента.
Аргументы функции ЕСЛИ:

1. Логическое выражение, т. е. принимающее значение ИСТИ­НА или ЛОЖЬ. Обычно здесь записывается результат операции сравне­ния (например, А1>0) или результат, возвращаемый функциями И и ИЛИ.

2. 3начение_если_истина. Здесь записывается то значение, кото­рое будет возвращено, если логическое выражение истинно.

3.3начение _если_ложь (необязательный). Это значение возвраща­ется, если логическое выражение ложно.
Статистические функции

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








    1. Возможные ошибки в формулах и способы их исправления

• Проверьте, чтобы все открывающие скобки имели закрывающие. При создании формулы Microsoft Excel будет выделять вводимые круглые скобки.

• Проверьте правильность использования оператора диапазона при ссылке на группу ячеек. Если имеется ссылка на группу ячеек, для разде­ления ссылок на первую и последнюю ячейки диапазона воспользуйтесь двоеточием (:).

• Для работы большинства функций необходимо ввести аргументы.

Проверьте, что введены все необходимые аргументы.

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

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

• Проверьте, что в каждой внешней ссылке указано имя книги и пол­ный путь к ней.

• Не изменяйте формат чисел, введенных в формулы. Например, даже если в формулу необходимо ввести 1000 р., то введите число 1000. Чтобы изменить формат результата, выводимого формулой, выберите ко­манду Ячейки в меню Формат, а затем - вкладку Число. После чего выберите необходимый формат.

Microsoft Excel отображает ошибку, если формула обработана непра­вильно. Например, если формуле требуются числовые значения, а на самом деле указаны текстовые, то отобразится ошибка. Ошибка появляется при удалении ячейки, на которую ссылается функция, а также при использова­нии ячейки, ширины которой не хватает для вывода результата.

Ошибки могут возникать не только из-за неправильной обработки формулы. Например, если отображается ошибка #Н/Д или #ЗНАЧ!, то, воз­можно, ошибка содержится в ячейке, на которую ссылается формула. Рас­смотрим основные ошибки и причины, их вызывающие.

Ошибка #####• появляется, когда вводимое числовое значение не умещается в ячейке или когда используется формула, результат выполне­ния которой не умещается в ячейке. Чтобы устранить такую ошибку, мож­но увеличить ширину столбца путем перемещения границы, расположенной между заголовками столбцов. Кроме того, можно изменить формат числа ячейки. Чтобы изменить формат числа, выберите команду Ячейка в меню Формат, а затем - вкладку Число; после чего укажите другой формат.

Ошибка ##### может появиться при определении числа дней между двумя датами, а также при определении количества часов между двумя временными промежутками. В этом случае проверьте правильность ввода формулы. Время и дата в Microsoft Excel должны быть положительными. Если результатом выполнения формулы, обрабатывающей даты и значения времени, является отрицательное число, то в ячейке отобразится ошибка ####. Чтобы вывести правильное значение, выберите команду Ячейки в меню Формат, а затем - вкладку Число; выберите формат, отличный от формата даты или времени.

Ошибка #ЗНАЧ! появляется, когда используется недопустимый тип аргумента или операнда, а также, если средствами автоисправления формулы не представляется возможным исправить формулу.

Одной из возможных причин является случай, когда вместо числово­го или логического (ИСТИНА или ЛОЖЬ) значения введен текст, и Microsoft Excel не может преобразовать его к нужному типу данных. Необходимо проверить правильность задания типов операндов или аргументов в функ­ции или формуле, а также значений ячеек, на которые ссылается формула. Например, если в ячейке А5 содержится число, а в ячейке А6 содержится текст, например, «Результат», формула «=А5+А6» вернет ошибку #ЗНАЧ!. Если все же нужно сложить два значения, воспользуйтесь в формуле функцией СУММ (функция СУММ игнорирует текстовые значения): = СУММ(А5:А6)

Ошибка #ИМЯ? появляется, когда Microsoft Excel не может рас­познать имя, используемое в формуле.

Если используемое имя было удалено или не было определено, опре­делите имя. Выберите команду Имя в меню Вставка, а затем – команду Создать.

Кроме того, команда Создать используется для добавления имени, отсутствующего в списке.

Может быть имя написано с ошибкой, тогда исправьте написание имени. Чтобы вставить правильное имя в формулу, выделите имя в строке формул, выберите команду Имя в меню Вставка, а затем - команду Вста­вить. На экране появится диалоговое окно Вставка имени. Выделите нуж­ное имя и нажмите кнопку ОК.

Часто такая ошибка возникает в написании имени функции, если не используется Мастер функций. Исправьте написание имени. Вставьте в формулу правильное имя функции при помощи панели формул.

Если в формулу введен текст, не заключенный в двойные кавычки, Microsoft Excel пытается распознать такую запись как имя. Необходимо заключить текст в двойные кавычки.

Может быть в ссылке на диапазон ячеек пропущен знак двоеточия (:).Исправьте формулу так, чтобы во всех ссылках на диапазон ячеек ис­пользовался знак двоеточия (:), например: СУММ(В5:С10).

Значение ошибки #Н/Д является сокращением термина «Нео­пределенные Данные». Это значение помогает предотвратить использова­ние ссылки на пустую ячейку. Введите в ячейки листа значение #Н/Д, если они должны содержать данные, но в настоящий момент эти данные отсут­ствуют. Формулы, ссылающиеся на эти ячейки, тоже будут возвращать зна­чение #Н/Д вместо того, чтобы пытаться производить вычисления.

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

Эта ошибка может возникнуть также при использовании некоторых специальных функций ГПР, ПРОСМОТР, ПОИСКПОЗ или ВПР - раз­беритесь с аргументами.

Ошибка #ССЫЛКА! появляется, когда используется недопусти­мая ссылка на ячейку.

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

Ошибка #ЧИСЛО! появляется, когда возникают проблемы при использовании чисел в формуле или функции, например, в функции с число­вым аргументом используется неприемлемый аргумент. Проверьте правиль­ность, используемых в функции аргументов. Может быть введена формула, возвращающая числовое значение, которое слишком велико или слишком мало, чтобы его можно было пред­ставить в Microsoft Excel. Измените формулу так, чтобы в результате ее вычисления получалось число, попадающее в допустимый диапазон.

Ошибка #ПУСТО! появляется, когда задано пересечение двух об­ластей, которые в действительности не имеют общих ячеек. Это возможно, когда использован ошибочный оператор пересечения диапазонов или ошибоч­ная ссылка на ячейку. Для создания ссылки на две непересекающиеся обла­сти, используется оператор объединения, обозначаемый запятой (,). Напри­мер, если формула суммирует два диапазона, проверьте, что в качестве разделителя диапазонов используется запятая (СУММ(В1:В10,К1:К10)). Если запятая пропущена, то Microsoft Excel будет пытаться суммировать общие ячейки указанных диапазонов (В1:В10 и К1:К10), которые на самом деле таких ячеек не имеют. Проверьте, не допущено ли ошибок при вводе ссылок на диапазоны ячеек.
1.10. Оформление таблицы. Границы

Когда ваша таблица заполнена данными и получены правильные ре­зультаты, без ошибок, самое время заняться ее оформлением, которое прак­тически не отличается от того, что вы уже умеете делать в Word. Чтобы задать обрамление таблицы или ее части, можно воспользоваться одним из следующих способов:

• нажать кнопку Границы панели инструментов Форматирование и из появившихся вариантов обрамления выбрать наиболее подходящий;

• выбрать пункт меню Формат/Ячейки или пункт контекстного меню Формат ячеек, вкладку Граница (Обрамление); здесь можно задать тип, цвет и толщину границы и внутренних линий выделенной области.


2. СВЯЗЫВАНИЕ ДАННЫХ. ПРОВЕРКА УСЛОВИЙ

2.1. Связывание данных, находящихся на разных рабочих листах
В рабочей книге EXCEL имеется несколько листов. Это позволяет не увеличивать размер таблицы за границы стандартной страницы, а вести работу на разных листах. Переход от листа к листу осуществляется щелч­ком мыши по соответствующему ярлыку. При внесении в формулу адреса ячейки, находящейся на другом листе, он записывается вместе с именем листа в следующем формате: имя_листа!А1, если имя листа имеет про­бел, то оно берется в апострофы: 'Имя листа'.

Переименование листов. Можно переименовать лист, дважды щелкнув по нему мышью, или выбрав пункт контекстного меню ярлыка ли­ста Переименовать. Лист обязательно должен иметь имя, поэтому про­сто стереть имя листа не удастся.

Выделение листов. Можно выделить группу листов, щелкая по каж­дому мышью, нажав при этом клавишу Ctrl. Можно выделить группу под­ряд идущих листов, щелкнув мышью по первому, нажав Shift и щелкнув по последнему листу.

Перемещение листов. Лист можно переместить в пределах рабо­чей книги, зацепив его мышью и перетащив в новою позицию. Можно пере­местить или скопировать лист или группу листов, выбрав пункт меню Правка/Переместить/скопировать лист.
<< предыдущая страница   следующая страница >>