Вычисления в таблицах

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

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

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

Например, пусть в ячейке А1 содержится число 10, в ячейке В1 число 20, в ячейку С1 введем формулу =А1+В1+2. После завершения ввода (нажатие Enter) в ячейке С1 появится результат сложения чисел, содержащихся в ячейках А1 и В1, увеличенный на 2, т.е. 32. Если изменить число, хранящееся в ячейке А1, например, заменить на 20, то наличие формулы в ячейке С1 автоматически изменит ее значение на 42.

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

Автозаполнение. Если необходимо продублировать какое-либо данное (числовое или текстовое) в ряд смежных ячеек в строке или столбце, то можно ввести одно данное, затем перетащить маркер заполнения ячейки с введенным данным по строке или столбцу, произойдет автоматическое копирование содержимого ячейки. Иногда требуется заполнить смежные ячейки не одинаковыми, но похожими данными, например, «1 блок», «2 блок», «3 блок» или «ряд 1», «ряд 2», «ряд 3». Делается это также с помощью маркера заполнения, сначала выделяются первые ячейки («1 блок», «2 блок»), затем диапазон протягивается за маркер заполнения.

Автозаполнение ячеек формулами. Активизируем ячейку С1 из предыдущего примера и протянем ее за маркер заполнения вниз по столбцу. В ячейку С2 автоматически введется формула =А2+В2+2, в С3 =А3+В3+2 и т.д. При протягивании по столбцу номер столбца увеличивается. Вернемся в ячейку С1 и протянем ее по строке, получим: в ячейке D1 =В1+С1 , в El =C1+D1 и т.д., при протягивании по строке номер строки увеличивается. Таким образом, при протягивании формулы ссылки А1 и В1 модифицируются, такие ссылки называютсяотносительными.

Ссылка вида $С$1будет неизменна при любом протягивании. Такие ссылки называются абсолютными. Чтобы ссылка не модифицировалась при протягивании по строке, ее следует записать как С$1; запретить модификацию по столбцу можно, применив ссылку $С1. Такие ссылки называютсясмешанными. Циклическое переключение типа текущей ссылки осуществляется нажатием функциональной клавишиF4.

Весьма неудобно было бы постоянно помнить, по каким ссылкам какие параметры находятся (например, в $С$1 находится параметр Х, в $D$4 — У и т.п.). Для этого в Excel есть возможность именования ячеек и диапазонов. Меню ВСТАВКА ► ИМЯ ► ПРИСВОИТЬ (в Exсel2007 – Формулы ► Определённые имена ► Диспетчер имён) вызывает диалоговое окно, в котором можно создать (и изменить) имя и использовать его вместо абсолютных ссылок. В том же окне при необходимости можно удалить уже существующее имя.

Ссылки на другие листы и книги. Для обращения к значению ячейки, расположенной на другом рабочем листе, нужно указать имя этого листа вместе с адресом соответствующей ячейки. Например, находясь на листе 1, можно ввести в ячейку А1 формулу =ЛИСТ4!ВЗ+1 для обращения к ячейке ВЗ на рабочем листе ЛИСТ4. Если в названии листа есть пробелы, то оно (название) заключается в кавычки. Связывание двух ячеек можно упростить, для чего на листе 1 в ячейке А1 набрать знак = , затем через корешок обратиться к листу 4 и щелкнуть по ячейке ВЗ.

Копирование ячеек, содержащих формулы. Техника копирования, перемещения, удаления ячеек, содержащих формулы, такая же, как и ячеек, содержащих данные. Чтобы скопировать содержимое ячейки или диапазона, нужно активизировать ячейку или выделить диапазон, вызвать меню ПРАВКА ► КОПИРОВАТЬ или правой кнопкой мыши вызвать контекстное меню пункт КОПИРОВАТЬ, активизировать ячейку, являющуюся началом диапазона, куда должны быть скопированы данные, меню ПРАВКА ► ВСТАВИТЬ или контекстное меню пункт ВСТАВИТЬ, затем нажать клавишу [Enter]. Но если в формуле содержатся относительные ссылки, то при копировании (а при использовании стиля ссылокR1C1 – и перемещении) они модифицируются.

Рассмотрим на примере. Пусть в ячейке С3 содержится формула = A1+$B1+C$1+$D$1. Скопируем её в ячейку Е6, т.е. на два столбца правей и на три строки ниже. Тогда все относительные адреса формулы в ячейке Е6 увеличатся на два по столбцу, на три по строке. В результате в ячейке Е6 получим формулу =C4+$B4+E$1+$D$1.

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

Удобно вводить функции с помощью МАСТЕРА ФУНКЦИЙ меню ВСТАВКА (в версии 2007 – вкладка Формулы ► Вставить функцию) или кнопки f_x. (В заголовке диалогового окна «Мастера функций» сообщается, что выполняется первый из двух шагов.) На первом шаге выбирается группа функций в поле «Категория» и затем сама функция в списке «Функция», после выбора которой имя функции со скобками заносится в строку формул. Следующий шаг – окно «Аргументы функции», в котором отображаются поля для ввода аргументов, краткое описание функции и аргументов, а также их текущие значения. Если аргумент являетсяобязательным, то его название указывается жирным шрифтом.

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

СУММ(число1; число2; …) – суммирует аргументы. Например, СУММ(А1:В2;С3;4) определит сумму значений диапазона А1:В2;С3 и числа 4

МИН(число1; число2; …) – минимальное значение аргумента.

МАКС(число1; число2; …) – максимальное значение аргумента.

СРЗНАЧ(число1; число2; …) – среднее арифметическое значение аргумента.

СЧЁТ(число1; число2; …) – находит количество числовых значений аргумента.

ОСТАТ(число;делитель) – находит остаток от деления.

Среди логических функций отметим функцию, аналогичную оператору «ЕСЛИ-ТО-ИНАЧЕ»: ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь).