Вычисления на рабочем листе. Функции рабочего листа

Средства ввода и редактирования данных. Операции с листами, строками, столбцами и ячейками. Приемы выделения элементов таблицы

Формульные выражения, их назначение, способы записи и правила ввода. Ссылки и их виды.

Формула – это краткая запись некоторой последовательности действий, приводящих к конкретному результату. Формула может содержать не более 1024 символов. Структуру и порядок элементов в формуле определяет ее синтаксис.

Все формулы в Excel должны начинаться со знака равенства. Без этого знака все введенные символы рассматриваются как текст или число, если они образуют правильное числовое значение.

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

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

Пример формулы:

=А1+В1

Пример функции:

=ВПР(A4;$A$34:$D$40;4;ЛОЖЬ)

В Excel включено 4 вида операторов: арифметические, текстовые, операторы сравнения, адресные операторы.

Арифметические операторы используются для выполнения основных математических вычислений над числами. Результатом вычисления формул, содержащих арифметические операторы, всегда является число. К арифметическим операторам относятся: +, -, *, /, %,^.

Операторы сравнения используются для обозначения операций сравнения двух чисел. Результатом вычисления формул, содержащих операторы сравнения, являются логические значения Истина или Ложь. К операторам сравнения относятся: =, >, <, >=, <=, <>.

Текстовый оператор & осуществляет объединение последовательностей символов в единую последовательность.

Адресные операторы объединяют диапазоны ячеек для осуществления вычислений. К адресным операторам относятся:

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

, - оператор объединения, который ссылается на объединение ячеек диапазона. Например, СУММ(В5:В15,С15:С25);

“ “ – оператор пересечения, который ссылается на общие ячейки диапазона. Например, в формуле СУММ(В4:С6 В4:D4) ячейки В4 и С4 являются общими для двух диапазонов. Результатом вычисления формулы будет сумма этих ячеек.

Приоритет выполнения операций:

- операторы ссылок (адресные) «:», «,», «»;

- знаковый минус ‘-‘

- вычисление процента %;

- арифметические ^, *, /, +, -;

- текстовый оператор &;

- операторы сравнений =, <, >, <=, >=, <>.

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

После ввода формулы в ячейку рабочего листа на экране в окне рабочего листа в ячейку выводится результат вычисления. Для вывода в ячейки формул следует установить флажок Формулы на вкладке Вид команды Параметры меню СЕРВИС.

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

В Excel существуют три типа ссылок: относительные, абсолютные, смешанные.

Относительная ссылка указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула, например «на две строки выше». При перемещении формулы относительная ссылка изменяется, ориентируясь на ту позицию, в которую переносится формула. Например, если в клетке С1 записана формула: =А1+В1, то при копировании ее в клетку С2 формула будет иметь следующие относительные ссылки =А2+В2; при копировании в D1: =В1+С1.

Абсолютными являются ссылки на ячейки, имеющие фиксированное расположение на листе. Эти ссылки не изменяются при копировании формул. Абсолютная ссылка содержит знак $ перед именем столбца и именем строки.Например: $A$1

Смешанные ссылки - это ссылки, являющиеся комбинацией относительных и абсолютных ссылок. Например, фиксированный столбец и относительная строка: $D6.

Ссылки на ячейки других листов книги имеют следующий формат:

<имя раб.листа>!ссылка на ячейку, например: Лист2!А1:А10.

Если имя рабочего листа содержит пробелы, то оно заключается в одинарные кавычки, например: ‘лицевой счет’!А1:А10.

Excel позволяет ссылаться на диапазон ячеек нескольких рабочих листов. Такая ссылка называется объемной. Например: Лист1: Лист5!$A$1:$D$3.

Ссылки на ячейки других книг имеют следующий формат:

[имя книги]<имя листа>!ссылка на ячейку, например: [книга2]Лист3!Е5:Е15.

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

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

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

Выделить одну ячейку можно следующими способами: щелкнуть левой клавишей мыши в нужном месте или ввести адрес нужной ячейки в поле имени.

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

Для выделения несмежных ячеек следует выделить первую группу смежных ячеек и, удерживая клавишу Ctrl, продолжить выделение следующих фрагментов.

Для выделения всей строки или столбца следует щелкнуть по имени строки или столбца.

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

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

Ввод данных в активную ячейку осуществляется нажатием клавиши Enter после набора данных на клавиатуре. При этом выделение по умолчанию перемещается вниз по активному столбцу. Направление перемещения задается параметром Переход к другой ячейке после ввода в направлении на вкладке Правка команды Параметры меню СЕРВИС.

Для ввода данных в диапазон необходимо выделить нужный интервал ячеек и ввести данные последовательно в ячейки диапазона. Чтобы ввести одну и то же значение сразу в несколько ячеек, нужно сначала выделить эти ячейки, а затем ввести значение и при нажатой клавише Ctrl нажать Enter.

Допущенные при вводе ошибки могут быть исправлены до завершения ввода или после. До нажатия клавиши Enter можно вносить любые исправления, пользуясь строкой формул, можно также отменить весь ввод, нажав клавишу Esc.

Отмена ввода после нажатия клавиши Enter осуществляется командой Отменить меню ПРАВКА. Эта команда позволяет отменить до 16 последних выполненных действий для рабочего листа. Команда Вернуть меню ПРАВКА позволяет повторно выполнить отмененные действия.

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

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

Полное удаление редактируемых данных возможно также несколькими способами: удаление старых данных осуществляется при вводе новых данных, начинающимся с первой позиции; при нажатии клавиши DEL; при выполнении команды Очистить меню ПРАВКА Эта команда позволяет выбрать объект удаления:

- все – удаляет содержимое ячеек, форматирование и примечания;

- форматы – удаляет из ячейки только форматирование;

- содержимое – удаляет из ячейки только содержимое;

- примечание – удаляет только примечание, относящееся к ячейке.

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

Копирование и перемещение ячеек на рабочем листе можно выполнять с помощью команд меню ПРАВКА или мышью способом Drug & Drop. Последний способ используется при копировании или перемещении на небольшие расстояния. Для его использования должен быть установлен параметр Перетаскивание ячеек на вкладке Правка команды Параметры меню СЕРВИС.

Копирование и перемещение ячеек можно также производить с помощью оперативного меню.

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

1) Указать, что именно будет вставляться: формула, значение, форматы, примечание и т.д. Задается переключателем в поле Вставка.

2) Указать, какую операцию следует выполнить над содержимым буфера обмена и исходным содержимым диапазона назначения. Можно выполнять любые арифметические операции. Задается переключателем в поле Операция.

3) Выбрать параметр Транспонировать, указывающий, что строки диапазона будут вставлены по столбцам и наоборот.

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

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

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

Для вставки строк и столбцов следует щелкнуть по заголовку строки или столбца или выделить такое количество строк или столбцов, какое необходимо вставить, а затем выполнить команду Строки или Столбца меню ВСТАВКА. Вставка всегда происходит выше первой из выделенных строк и левее первого из выделенных столбцов. Удаление выделенных ячеек, строк, столбцов производится аналогично (команда Удалить меню ПРАВКА). При удалении ячеек следует указать направление смещения окружающих ячеек.

Над рабочими листами книги можно выполнять следующие операции:

- выделять рабочие листы;

- вставлять новые листы;

- удалять листы;

- переименовывать листы;

- перемещать и копировать листы в пределах одной книги или в другую книгу;

- защитить данные на рабочем листе.

Вставка и удаление листов производится также аналогично вставке строк и столбцов (соответствующие команды меню ВСТАВКА и ПРАВКА).

Рабочему листу можно присвоить любое символьное имя длиной не более 31 символа, включая пробелы. Переименовать лист можно следующими способами:

1) Щелкнуть дважды по ярлыку листа и ввести новое имя.

2) Выполнить команду Лист - Переименовать меню ФОРМАТ.

3) Выполнить команду Переименовать оперативного меню, выводимого при нажатии правой клавиши мыши, установленной на ярлыке листа.

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

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

1) С помощью команды Переместить/Скопировать лист меню ПРАВКА.

2) С помощью мыши аналогично перемещению листа внутри книги.

Команды копирования листов аналогичны командам перемещения. Копирование мышью осуществляется протаскиванием указателя мыши при нажатой клавише Ctrl.

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

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

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

Чтобы установить ручное обновление вычислений, следует включить режим Вручную на вкладке Вычисления команды Параметры меню СЕРВИС.

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

Чтобы пересчитать только активный лист, следует нажать комбинацию клавиш Ctrl и F9. Даже если установлено ручное обновление вычислений, Excel обычно пересчитывает всю книгу при ее сохранении на жестком диске.

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

Циклическая ссылка – это формула, которая зависит от своего собственного значения. При обнаружении циклической ссылки Excel выдает сообщение об ошибке. Многие циклические ссылки могут быть разрешены. Для установки этого режима следует установить флажок Итерации на вкладке Вычисления команды Параметры меню СЕРВИС. В этом случае Excel пересчитывает заданное число раз все ячейки во всех открытых листах, которые содержат циклическую ссылку. Если установлен флажок Итерации, можно задать предельное число итераций (по умолчанию 100) и относительную погрешность (по умолчанию 0,001). Excel выполняет пересчет указанное предельное число раз или до тех пор, пока изменение значений между итерациями не станет меньше заданной относительной погрешности.

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

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

В общем виде любая функция может быть записана в виде:

=<имя_функции>(аргументы)

Существуют следующие правила ввода функций:

1) Имя функции всегда вводится после знака «=».

2) Аргументы заключаются в круглые скобки, указывающие на начало и конец списка аргументов.

3) Между именем функции и знаком «(«пробел не ставится.

4) Вводить функции рекомендуется строчными буквами. Если ввод функции осуществлен правильно, Excel сам преобразует строчные буквы в прописные.

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

. Виды функций:

1) Арифметические и тригонометрические.

2) Инженерные, предназначенные для выполнения инженерного анализа (функции для работы с комплексными переменными; преобразования чисел из одной системы счисления в другую; преобразование величин из одной системы мер в другую).

3) Информационные, предназначенные для определения типа данных, хранимых в ячейках.

4) Логические, предназначенные для проверки выполнения условия или нескольких условий (ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ).

5) Статистические, предназначенные для выполнения статистического анализа данных.

6) Финансовые, предназначенные для осуществления типичных финансовых расчетов, таких как вычисление суммы платежа по ссуде, объема периодической выплаты по вложению или ссуде, стоимости вложения или ссуды по завершении всех платежей.

7) Функции баз данных, предназначенные для анализа данных из списков или баз данных.

8) Текстовые функции, предназначенные для обработки текста (преобразование, сравнение, сцепление строк текста и т.д.).

9) Функции работы с датой и временем. Они позволяют анализировать и работать со значениями даты и времени в формулах.

10) Нестандартные функции. Это функции, созданные пользователем для собственных нужд. Создание функций осуществляется с помощью языка Visual Basic.

Командная кнопка "автосумма" – на панели инструментов предназначена для автосуммирования, т.е. для получения итоговых данных для любых указанных диапазонов данных с помощью функции СУММ. Технология работы с командой Автосуммирования следующая:

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

- щелкнуть по кнопке «автосумма»;

- будет предложен диапазон для суммирования (он окружен подвижной рамкой). Если диапазон неверен, следует выделить нужный диапазон (ячейка, смежные ячейки, несмежные ячейки – в любой комбинации);

- щелкнуть по кнопке «автосумма».

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

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

1). #ДЕЛ/0! - попытка деления на 0. Эта ошибка обычно возникает, если в формуле делитель ссылается на пустую ячейку.

2). #ИМЯ? – в формуле используется имя, отсутствующее в списке имен диалога ПРИСВОЕНИЕ ИМЕНИ. Excel также вводит это ошибочное значение в том случае, когда строка символов не заключена в двойные кавычки.

3). #ЗНАЧ! – выдается при указании аргумента или операнда недопустимого типа, например, введена математическая формула, которая ссылается на текстовое значение, а также в том случае, когда Excel не может исправить формулу средствами автоисправления.

4). #ССЫЛКА! – отсутствует диапазон ячеек, на который ссылается формула (возможно он удален).

5). #Н/Д – нет данных для вычислений. Аргумент функции или операнд формулы является ссылкой на ячейку, не содержащую данные. Любая формула, которая ссылается на ячейки, содержащие #Н/Д, возвращает значение #Н/Д.

6). #ЧИСЛО! – задан неправильный аргумент функции, например, √(-5). #ЧИСЛО! может также указывать на то, что значение формулы слишком велико или слишком мало и не может быть представлено на листе.

7). #ПУСТО! – в формуле указано пересечение диапазонов, но эти диапазоны не имеют общих ячеек.

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

Функция позволяет:

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

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

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

Отслеживание зависимостей выполняется командой Зависимости меню СЕРВИС или командными кнопками панели инструментов Зависимости.

Существуют также три специальные логические функции ЕОШ, ЕОШИБКА и ЕНД, позволяющие перехватывать ошибки и значения #Н/Д и предотвращать их распространение по рабочему листу. Функции имеют следующий формат:

=ЕОШ(значение)

=ЕОШИБКА(значение)

=ЕНД(Значение)

Эти функции проверяют значение аргумента или ячейки и определяют, содержат ли они ошибочное значение. Функция ЕОШ проверяет значение на все ошибки, за исключением #Н/Д, ЕОШИБКА отслеживает все ошибочные значения, а ЕНД проверяет только появление значения #Н/Д.

В принципе аргумент «значение» может быть числом, формулой или строкой символов, но обычно это ссылка на ячейку или диапазон. В противном случае проверяется только одна ячейка диапазона, а именно ячейка, находящаяся в том же столбце или строке, что и формула. Обычно функции ЕОШ, ЕОШИБКА и ЕНД используются в качестве логических выражений функции ЕСЛИ.