29. Структурные единицы таблицы ms Excel (ячейка, строка, столбец, диапазон).

См. 28

30. Ms Excel: абсолютная и относительная адресация ячеек, создание формул, копирование формул.

Ввод и редактирование формул

Основным инструментом для обработки данных в Excel являются формулы.

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

Формулы используются для нахождения новых данных в ячейках на основе данных, существующих в других ячейках. Если запись в ячейке начинается со знака равенства (клавиша =), то программа воспринимает ее как формулу. Запись формулы можно начать и со знаков + или -, при этом последующая запись будет воспринята как формула.

Н абираемая формула, как и любая запись, отображается в строке формул. После команды ввода формулы (нажатия клавиши = либо щелчка по кнопке «=» в строке формул) в строке формул появляются новые кнопки Смысл этих кнопок простой: щелчок по кнопке с красным крестиком отменяет вне­сенные в формулу изменения, а щелчок по кнопке с зеленой «галочкой» дает ко­манду на исполнение формулы, запи­санной в ячейку. Завершить ввод формулы можно нажатием на клавишу Enter, либо перемещением курсора в другую ячейку, либо щелчком по кнопке с зеленой галочкой в строке формул.

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

Арифметические и другие типы операторов

В Excel имеются четыре вида операторов: арифметические, текстовые, операторы сравнения и операторы ссылок (адресные операторы). Ариф­метические операторы служат для выполнения операций над числами. В Excel используются следующие арифметические операторы: + (сложение), - (вычита­ние), / (деление), * (умножение), ^Л (возведение в степень), % (процент).

При работе с числами и текстом применяются еще операторы сравнения: =(равно), > (больше), < (меньше), >= (больше или равно), <= (меньше или равно), <> (не равно). Эти операторы используются для присвоения утверждениям зна­чений Истина или Ложь.

В Excel имеется один текстовый оператор & (амперсанд), который объединяет последовательности символов из разных ячеек в одну последовательность. Напри­мер, если в ячейки А1 и А2 введены тексты «паро» и «воз» соответственно, а в ячейку A3 записана формула =А1&А2, то результатом в ячейке A3 будет текст «паровоз».

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

Синтаксис формул

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

Символа = , которым всегда начинается запись формулы (этот символ вво­дится в ячейку с клавиатуры либо вызывается щелчком по кнопке «=» в строке формул);

Операторов +, -, *, <, > и прочих операторов;

Функций, выбираемых из набора встроенных функций Excel;

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

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

1. Адресные операторы (: , <пробел> );

2.Унарный минус (используется для указания отрицательных чисел: -1, -0,23 и т.д.);

3. Процент (%);

4. Возведение в степень ( ^А );

5.Умножение и деление ( * и /);

6. Сложение и вычитание ( + и - );

7. Объединение последовательностей символов ( & );

8. Операторы сравнения ( = , < , >, <=, >= , <> ).

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

Остановимся на более сложных способах ввода формул.

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

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

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

Диапазоны ячеек и адресные операторы

Двоеточие и запятая, используемые при записи ссылок на ячейки, называются адресными операторами. Еще одним адресным оператором в Excel является пробел. С помощью пробела можно ссылаться на область пересечения двух диапазонов. Например, запись =СУММ (Fl: F8_F5: F13) будет означать суммирование ячеек от F5 до F8 (здесь символ _ обозначает пустой пробел, а не подчеркивание).

Ссылки на ячейки

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

Ссылку на ячейку можно задать разными способами:

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

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

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

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

Относительные и абсолютные ссылки

Все виды ссылок, которые рассматривались ранее, являлись относитель­ными ссылками. Этот вид ссылок устанавливается в Excel по умолчанию, и отно­сительность их проявляется, к примеру, в следующем. Если записать в ячейку A3 формулу =А1+А2 и скопировать эту формулу в ячейку В5, то в итоге получим формулу =ВЗ+В4. При использовании относительных ссылок данная исходная формула представляется как некоторая операция, при которой нужно вычислить сумму двух вышестоящих ячеек.

Очевидно, что возможны случаи, когда изменение адресов в ссылках на неко­торые ячейки нежелательно, например, если в ячейки записаны оригинальные данные. Тогда устанавливают абсолютные ссылки, которые не изменяются при копировании формулы. Абсолютные ссылки отличаются от относительных нали­чием знака доллара $. Например, рассматриваемая формула, записанная в абсолютных ссылках, будет выглядеть как =$А$1+$А$2, и эта формула не будет изме­няться при копировании или перемещении в любую ячейку.

Чтобы изменить относительную ссылку на абсолютную, не обязательно вруч­ную расставлять знак $. Существует простой способ изменения ссылок с помощью клавиши F4. Следует ввести относительную ссылку в формулу, а затем с помощью после­довательных нажатий F4 выбрать форму записи ссылки из ряда типа А1 - $А$1 - А$1 - $А1.