Создание формул и анализ данных – ЧАСТЬ 1

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

Основные сведения о формулах

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

Чтобы   увидеть,   как   работают   формулы,   давайте   создадим   несколько   простейших примеров. Начнем с того, что выделим ячейку,  например, А10. Теперь наберем в ней:

=10+5 и нажмем клавишу Enter. В ячейке появится значение 15. Если выделить ячейку А10, в строке формул можно увидеть саму формулу, введенную в эту ячейку. Полученное значение  является  результатом   вычислений,  а  в  строке  формул  показывается  сама формула.

Приоритет операторов

Операторами (operators) называются символы математических операций, включая знаки сложения  (+),  вычитания  (-),  умножения  (*)  и  деления   (/).  Операторам  с  большим приоритетом   при   выполнении   вычислений   отдается   первенство.   При   этом   Excel руководствуется следующими правилами:

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

? умножение и деление выполняются прежде сложения и вычитания;

? операторы с равным приоритетом обрабатываются в порядке слева направо.

Выделите пустую ячейку и введите в нее =4+12/6. Нажмите Enter, и в ячейке появится вычисленное значение 6. Здесь Excel сначала производит деление двух операндов —12 на 6, а затем к полученному  значению  прибавляет 4. Если бы порядок вычислений был другой, то и результат был бы совершенно отличный от полученного. Выделите еще одну пустую ячейку и введите в нее формулу =(4+12)/б. Нажмите Enter; в ячейке отобразится значение   2,666667.   Этот   пример   показывает,   как   скобки   влияют   на   приоритет вычислений в формулах. Еще несколько  случаев продемонстрировано в табл. 12.1, где формулы состоят из одних и тех же операндов и операторов, но итог вычислений зависит от расположения скобок.

Таблица 12.1. Влияние скобок на результат вычислений Формула                                    Результат

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

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

СОВЕТ

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

Использование в формулах ссылок

Ссылка  (cell reference) является  идентификатором  ячейки  или  группы  ячеек  в  книге. Создавая формулу, содержащую ссылки на ячейки, вы связываете ее с местоположением данных в книге. Значение формулы зависит от содержимого ячеек, на которые указывают ссылки.

Например, выделите ячейку А1 и введите в нее формулу =10*2. Теперь выделите ячейку А2 и наберите в ней =А1. В обеих ячейках вы  увидите число 20. Если вы измените значение в ячейке А1, то и  значение в ячейке А2 также изменится. Теперь выделим ячейку A3 и введем в нее формулу =А1+А2. Excel вернет значение 40. В дальнейшем вы убедитесь, насколько полезны ссылки при создании и использовании сложных формул.

Ввод ссылок с помощью мыши

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

1.   Выделите ячейку В10 и введите знак равенства.

2.   Щелкните на ячейке А9 и введите знак плюса.

3.   Щелкните на ячейке А10 и нажмите клавишу Enter.

Когда вы щелкаете на ячейке, вокруг нее появляется «бегущая» рамка, а в строке формул отображается ссылка на эту ячейку. Закончив ввод формулы, нажмите Enter. Если вы не нажмете  Enter  и  выделите   какую-либо  другую  ячейку,  Excel  воспримет  это  как продолжение ввода в формулу еще одной ссылки на ячейку.

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

СОВЕТ

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

залась в пределах видимости, нажмите клавиши Ctrl+Backspace.

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

Относительная   ссылка   (relative   reference)   указывает   на    местоположение   ячейки относительно  активной  ячейки,  например:   «на   две  строки  выше».  В  предыдущих примерах  мы  пользовались   именно  такими  ссылками.  Абсолютная  ссылка  (absolute references)  указывает  фиксированное  положение  ячейки  на  листе,  например:  «ячейка, расположенная на пересечении столбца А и строки 2». Наконец, смешанная ссылка (mixed reference) является сочетанием относительной и  абсолютной ссылок, например: «ячейка находится  в  столбце  А и  на  две  строки  выше».  Абсолютные  s  и  смешанные  ссылки особенно   полезны  при  копировании  формул  из  одного  места  листа  в  другое.   При выполнении     копирования     и     вставки     относительные     ссылки     подстраиваются автоматически, в отличие от абсолютных. К  этому мы еще вернемся более подробно в разделе «Поведение ссылок при копировании формул».

Относительная  ссылка  на  ячейку  А1  записывается  следующим  образом:  =А1.  Абсолютная ссылка на эту же ячейку имеет другой вид: =$А$1. Комбинируя абсолютные и относительные ссылки, можно  создать следующие два варианта смешанных ссылок:

=$А1 и =А$1.

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

С помощью клавиши F4 можно быстро изменить тип ссылки:

1.   Выделите ячейку А1 и введите в нее формулу =В1+В2 (не нажимайте пока клавишу

Enter).

2.   Нажмите  клавишу  F4, чтобы  относительную  ссылку,  находящуюся  рядом  с  мер-

цающим курсором, изменить на абсолютную. Формула примет вид =В1+$В$2.

3.   Снова нажмите клавишу F4, и ссылка из абсолютной преобразуется  в смешанную следующим  образом: =В1+В$2 (относительная  координата  столбца  и  абсолютная координата строки).

4.   Наконец, еще раз нажав F4, вы обратите эту смешанную ссылку, сделав координату столбца абсолютной, а строки — относительной.  Теперь формула будет выглядеть так: =В1+$В2.

5.   Если еще раз нажать F4, формула примет исходный вид: =В1+В2.

ПРИМЕЧАНИЕ –

Вместе  с  книгой  распространяется  демонстрационная  версия  надстройки  Spreadsheet Assistant,  которая  включает  в  себя  ряд  полезных  инструментов.  В  том  числе  меню Assistants  (Помощь),  а  в  нем  подменю  Formulas  &  Functions  (Формулы  и  функции), команды которого работают с числами, например выполняют преобразование «текстовых чисел» в «числовые числа» и округление значений в выделенных ячейках. Команда Hide Formula  Errors  (Скрыть  ошибочные  значения)  этого  меню  открывает  диалоговое  окно, позволяющее  указать диапазон ячеек, в котором все «ошибочные» значения не  будут отображаться.

Ссылки на другие листы той же книги

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

В10 листа Лист1 ссылку на ячейку А9 листа Лист2, выполните следующие действия:

1.   Выделите ячейку В10 на листе Лист1 и введите знак равенства.

2.   Активизируйте Лист2, щелкнув на ярлычке этого листа в нижней части окна.

3.   Щелкните на ячейке А9 и нажмите клавишу Enter.

После этого активным снова станет Лист1. Выделив ячейку В10, вы  увидите в строке формул содержимое этой ячейки: =Лист2!А9.

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

Ссылки на листы других книг

Точно так же, как вы ссылаетесь на ячейки листов текущей книги, вы можете ссылаться и на ячейки других книг. Такие ссылки называются внешними (external). Например, чтобы ввести  ссылку  из  книги  Книга1  на  ячейку  книги  Книга2,  необходимо  выполнить следующие действия:

1.   Создайте новую книгу — Книга2, нажав кнопку Создать (New) панели инструментов

Стандартная (Standard).

2.   Выберите в меню Окно (Window) команду Расположить (Arrange) и затем установите переключатель в положение Слева направо (Vertical).

3.   Выделите ячейку А1 на листе Лист1 книги Книга1 и введите знак равенства.

4.   Активизируйте Книгу2.

Источник: Эффективная работа: Microsoft Office Excel 2003 / М. Додж, К. Стинсон — СПб.: Питер, 2005. — 1088 с: ил.

Похожие посты:

Вы можете оставить комментарий, или ссылку на Ваш сайт.

Оставить комментарий