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

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

Автовычисления

В строке состояния Excel есть поле, содержащее результат выполнения определенной операции над выделенными данными. Это поле позволяет выполнять следующие функции: Average (Среднее), Count (Количество ячеек), Count Mums (Количество числовых ячеек), Мах (Максимум), Min (Минимум), Sum (Сумма). При этом результат выполнения вы увидите быстрее, чем при вводе соответствующей функции в отдельную ячейку, кроме того, нет необходимости отводить ячейку под данные, которые могут понадобиться вам лишь однажды. Автовычисления удобно использовать для подсчета, каких либо промежуточных результатов.

Автосуммирование

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

Нажатие кнопки AutoSum (Автосумма) ведет суммированию соседних строк или столбцов путем автоматической вставки функции SUM под ячейками столбца или правее ячеек строки и подстановки диапазона ячеек в формулу.

1.2 Ошибки в формулах

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

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

В верхнем левом углу, где могут содержаться ошибки, появляется зеленый треугольник. Если эту ячейку выделить, рядом с ней отобразится кнопка Trace Error (Источник ошибки). Когда на данную кнопку помещается указатель мыши, появляются описание предполагаемой ошибки и стрелка для открытия меню действия кнопки. После щелчка на стрелке откроется список команд, предназначенных для проверки исправления ошибок. Набор команд зависит от типа ошибки.

Исправление ошибок

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

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

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

Отслеживание зависимостей

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

Команды для отслеживания таких зависимостей вызываются из подменю Formula Auditing (Зависимости) меню Tools  (Сервис), а также с помощью кнопок панели инструментов Formula Auditing (Зависимости).

Чтобы открыть данную панель, следует воспользоваться командой Tools –Formula Auditing—Show Formula Auditing Toolbar (Сервис—Зависимости формул—Панель зависимостей).

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

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

2 Абсолютные, относительные и смешанные ссылки

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

изменение ссылок  согласно новому месту записи формулы. Это позволит быстро вводить однотипные формулы для разных столбцов или строк данных. В то же время для других ссылок при копировании формул необходимо обеспечить неизменность адресов или отдельных частей адресов. Возможности для подобного манипулирования со ссылками в Excel предусмотрены. В зависимости от задачи на ячейки могут устанавливаться абсолютные, относительные или смешанные ссылки. При копировании формул в другие ячейки результаты будут разными в зависимости от типа ссылки, поэтому необходимо хорошо разобраться в том, как организуются ссылки разных типов на ячейки. По умолчанию, если не указано обратное, для адресации ячеек в формулах Excel используются относительные ссылки, позволяющие при копировании формул автоматически изменять адреса, на которые они ссылаются. Относительные ссылки записываются простым указанием номеров строк и букв столбцов ячеек, например А2,С38,AD23 и т.д. Если в ячейке С11 содержится формула =SUM(C1 :С10), то при копировании этой формулы в ячейку D11 она автоматически преобразуется в формулу =SUM(D1 :D10). Если необходимо просуммировать данные в нескольких столбцах, то достаточно ввести формулу суммирования только один раз, используя относительные ссылки, а затем скопировать введенную формулу под все суммируемые столбцы. При этом ссылки в формулах поменяются так, как описано ранее. Относительные ссылки удобнее в большинстве случаев, и поэтому применяются наиболее часто. Абсолютные ссылки противоположны относительным. Они используются, когда необходимо сохранение адресов ячеек при копировании формулы в другую ячейку.

Для обозначения абсолютной ссылки перед номером строки и буквой столбца указывается знак доллара ($).Таким образом, абсолютная ссылка на ячейку А1 будет выглядеть следующим образом: $А$1. Абсолютные ссылки удобны, когда данные зависят от одной ячейки или группы ячеек, например при использовании ячейки, содержащей курс доллара или размер процентной ставки. Сначала создается формула в одной из ячеек и туда вводится абсолютная ссылка, а затем эта формула копируется в нужные ячейки столбца или строки. Сочетанием абсолютных и относительных ссылок являются смешанные ссылки. Их используют, когда при копировании формулы нужно сохранять фиксированным только один параметр, то есть номер строки или букву столбца. При этом перед сохраняемым параметром адреса пишется знак доллара ($), а перед изменяемым — нет. Например, для ссылки $В8 буква столбца при копировании меняться не будет, а номер строки будет автоматически изменяться согласно положению ячейки с формулой. Ссылка, у которой фиксированным остается номер строки, а буква столбца меняется, выглядит следующим образом: В$8.

Замена значениями

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

2.1 Заполнение ячеек с помощью клавиш Ctrl+Enter

Excel позволяет вводить данные и формулы в несколько ячеек сразу. При этом вводимые формулы и значения заполняют выделенные ячейки, а при выполнении команде Fill (Заполнить) или Copy (Копировать) и Paste (Вставить). То есть при использовании относительных ссылок в формулах ссылки будут автоматически изменяться при заполнении разных ячеек диапазона. Этим методом можно заполнять как диапазон ячеек, так и несколько несмежных диапазонов. Для ввода данных или формулы во все ячейки выделенного диапазона можно использовать комбинацию клавиш Ctrl+Enter.

2.2 Конкатенация строк

Конкатенацией обычно называют операцию объединения двух строк. В Excel оператором конкатенации является амперсанд (&). Этот оператор может объединять не только строковые данные, но и числа, даты и произвольные пользовательские форматы в одну длинную текстовую строку, которая и будет результатом выполнения операции. Например, формула =А1&»»&А2,помещенная в ячейку A3, выведет в этой ячейке строку, составленную из содержимого ячейки А1, следующего за ней пробела и содержимого ячейки А2. Если использовать этот оператор таким образом: =А1&А2, то содержимое двух ячеек будут объединено без пробела между ними, что не всегда удобно для чтения. Поэтому в формулу с конкатенацией лучше всегда добавлять кавычки с пробелом между ними (« »), чтобы после выполнения формулы программа Excel вставляла пробел между содержимым двух ячеек.

Скрытие

Важной возможностью для защиты данных является их скрытие. Этот прием убережет вашу важную информацию от постороннего взгляда. Вы можете указать, следует ли отображать значение ячеек на рабочем листе Excel, а также следует ли отображать содержимое ячейки в строке формул. Даже если значение или формула не выводятся в строке формул, они могут быть видны в самой ячейке. Параметры защиты не действуют, если не включена защита листа (не выполнена команда Tools (Сервис) > Protect Document (Защита документа)).

Снятие защиты ячейки

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

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

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

Sheet2 (Лист2) и т.д. Пользователь может менять имена рабочих листов. При записи адреса перед координатами ячейки ставится имя рабочего листа. Например, чтобы ввести ссылку на ячейку А1, расположенную на листе Sheetl, необходимо ввести в формулу ссылку SheetliAl. Как вы можете видеть, для отделения названия рабочего листа от координат ячейки используется восклицательный знак (!).Если вы изменили имя листа, используйте новое имя, например Отчет!А1.Если в имени листа есть пробелы, то его следует окружить одинарными прямыми кавычками, например "Отчет по продажам"!А1.

Отображение на экране

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

3 Функции

3.1 Использование функций

Функция Excel – это лишь специализированная готовая формула. Каждая функция состоит из 2-х частей: имени (например, СРЗНАЧ) и набора

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

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