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

О синтаксисе ссылок

До сих пор в примерах фигурировали имена, которые Excel присваивает листам и книгам по умолчанию. Но при сохранении книги вы даете ей  другое, постоянное имя. Если вы сначала  создаете  формулы,  а  потом  сохраняете  книгу  под  новым  именем,  формулы подстраиваются  нужным  образом. Так, если вы сохраняете книгу Книга2 под именем Продажи.xls,  ссылка  =[Книга2]Лист2!$А$2  автоматически   преобразуется  в  ссылку

=[Продажи.xls]ЛИСТ2!$А$2. А если переименовать Лист2 в Февраль, ссылка примет вид

=[Продажи.xls]Февраль!$А$2. Если же книга, на которую указывает ссылка, закрыта, в ссылке      появляется      полный      путь      к      папке,      где      хранится      эта      книга:

=’С:\Ехсе1\[Продажи.х15]Февраль’!$А$2.

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

Использование в формулах «числового текста»

Под условным понятием «числовой текст» (numeric text) скрываются вводимые в ячейки значения, которые по внешнему виду напоминают  числа, но при этом на самом деле являются  текстом.  С  «числовым  текстом»  можно  производить  любые  математические операции, только если текстовые значения содержат следующие символы:

0  1  2  3  4  5  б  7  8  9  ,  +  Е  е1

Кроме  этого,  допускается  использовать  символ  /  для  записи  дробей  и  пять  символов форматирования (включая символ пробела):

$  %  (  )  2

1  В «родной» версии Excel вместо запятой по умолчанию используется точка. — Примеч. перев.

2  В «родной» версии Excel вместо пробела по умолчанию используется запятая. — Примеч. перев.

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

=$1234+$123, Excel выведет сообщение об ошибке в формуле. В этом  же сообщении будет предложено для исправления ошибки убрать знаки  денежной единицы. Но если ввести  в  ячейку  все  ту  же  формулу  в  таком  виде:  ="$1234"+’$123",  то  программа игнорирует знаки доллара  и выведет корректный результат — 1357. При выполнении сложения Excel автоматически преобразует числовой текст в числовые значения.

Текстовые значения

Текстовые  значения  (text  values)  —  это  любые  вводимые  в  ячейку   значения,  не являющиеся ни числами, ни числовым текстом. В большинстве случаев работа с текстом практически не отличается от  работы с числами. Скажем, если в ячейке А1 хранится текстовое  значение ABCDE, то, набрав в ячейке А10 ссылку =А1, вы увидите в  этой ячейке правильный результат — ту же строку символов ABCDE.

Для    объединения    (concatenation)   текстовых    значений    используется    оператор    & (амперсанд).  Возвращаясь  к  предыдущему  примеру,  предположим,  что  в  ячейке  А2 хранится текст FGHIJ и вы набрали в ячейке A3 формулу =А1&А2. В результате в ячейке A3  появится   слитный   текст   ABCDEFGHD.   Чтобы   разделить   значения   пробелом, измените  немного  вид  формулы:  =А1&"  "&А2.  В  этой  формуле   используются  два оператора  объединения  и  литерал  (текстовая  константа)  —  в  данном  случае  пробел, заключенный в кавычки.

С  помощью  оператора  «амперсанд»  (&)  можно  объединять  и   численные  значения. Например, если ячейка A3 содержит числовое значение 123, а в ячейке А4 хранится число 456, то результатом  использования формулы =АЗ&А4 будет строка символов 123456. Эта  строка  выравнивается  в  ячейке  по  левому  краю,  так  как  она  является  текстом. (Помните, с числовым текстом допустимо производить любые математические операции, если в нем используются только перечисленные выше числовые символы).

Наконец,  оператор  &  можно  использовать  для  объединения   текстовых   и  числовых значений. Так, если ячейка А1 содержит текст  ABCDE, а ячейка A3 — число 123, то значением формулы =А1&АЗ будет строка ABCDE123.

Объединения на практике

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

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

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

=В2&" "&А2. Формула для столбца Е (см. строку формул на рисунке)  просто вместо символа пробела вставляет между именем и фамилией запятую.

СМОТРИ ТАКЖЕ  

Подробно о средствах работы с текстом рассказывается в разделе  «Текстовые функции»

главы 14.

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

Функция (function) — это заранее определенная формула. Многие из функций Excel — не что иное, как краткие варианты часто используемых формул. Например, функция СУММ (SUM)     складывает     значения     в      выделенном     диапазоне.     Сравните     запись

=А1+А2+А3+А4+А5+А6+А7+А8+А9+А10 с  ее  аналогом  =СУММ(А1:А10).  Очевидно,

что функция СУММ существенно упрощает ввод и облегчает чтение таких выражений.

СМОТРИ ТАКЖЕ

Подробнее о стандартных функциях Excel см. главу 13, «Использование функций»; работа с функцией СУММ подробно описана в главе 14, «Наиболее полезные функции».

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

Каждая   функция   имеет   собственные   имя   и   аргументы    (arguments),    последние записываются в круглых скобках. В предыдущем  примере аргументом функции СУММ являлся диапазон А1:А10. Если  вы  забыли поставить закрывающую скобку и нажали Enter, программа  исправит за вас ошибку и добавит закрывающую скобку в то место формулы, где она должна, по ее мнению, находиться. Но полагаться в таких случаях на табличный  процессор  не  следует,  скобка  может  оказаться  совсем  не  там,  где  вы  ее собирались поставить, и результат вычислений станет непредсказуем. Рекомендуем всегда дополнительно проверять правильность расстановки скобок.

Автосумма

Функция   СУММ  используется   чаще   других,   именно   поэтому   кнопка   Автосумма (AutoSum) выведена на панель инструментов Стандартная. Рассмотрим, как добавлять формулу для суммирования в ячейку, используя кнопку Автосумма:

1.   Введите ряд числовых значений в ячейки, как показано на рис. 12.7.

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

3.   Если предлагаемый диапазон не устраивает вас, то, прежде чем нажать клавишу Enter,

выделите мышью нужный вам.

Рис. 12.7. Для суммирования чисел в столбцах или строках выделите ячейку ниже или правее ячеек с числами и нажмите кнопку Автосумма

СОВЕТ

Чтобы  быстро  узнать  результат  суммирования  значений  в  выделенном  диапазоне,  посмотрите  на  строку  состояния.  В  ней  выводится  итоговое  значение.  Щелкните  на  этой области строки состояния правой  кнопкой мыши и в контекстном меню выберите другую групповую  функцию для выделенного диапазона (например, среднее, максимальное  или минимальное значение). Подробнее см. раздел «Вывод результатов  в  строке состояния» главы 2.

Еще одно удобство при работе с кнопкой Автосумма — меню, которое  появляется на экране, если щелкнуть на стрелке рядом с этой кнопкой, как показано на рис. 12.7 справа. Используя  команды  данного  меню,  наравне  с  функцией  СУММ,  вы  можете  ввести  в ячейку любую из других предложенных функций: Среднее (Average), Число (Count), Максимум  (Мах)  или  Минимум  (Min).  Кроме  того,  если  выбрать  команду  Другие функции (More Functions), на экране появится окно  Мастера функций, открывающее доступ к любой доступной в Excel функции.

Кнопка Автосумма позволяет одновременно вставлять групповую функцию в диапазон. Например, если в таблице на рис. 12.5 выделить  ячейки В9:С9 и щелкнуть на кнопке Автосумма (AutoSum), программа  заполнит суммарными значениями обе выделенные

ячейки. Подробнее о функции СУММ рассказывается в разделе «Функция СУММ» главы

Вставка функций

Кнопка Вставка функции (Insert Function) в строке формул  предоставляет простой и удобной доступ к встроенным функциям Excel.  (Той же цели служит команда Функция (Function) меню Вставка.) После щелчка на этой кнопке на экране появляется диалоговое окно Мастера функций, показанное на рис. 12.8. Подробнее о работе с Мастером функций см. раздел «Добавление функций на рабочий лист» главы 13.

Рис. 12.8. Диалоговое окно Мастера функций

Создание «трехмерных» формул

Мы познакомились с тем, как оперировать ссылками на ячейки текущего  листа, других листов или других книг. Но вы также можете создавать  ссылки на ячейки диапазонов различных  листов  в  одной  книге.  Такие  ссылки  называются  «объемными»  (3-D),  или

«трехмерными»  (three-dimensional). Предположим,  в  вашей  книге  есть  12  листов,  по одному для каждого месяца, и один лист (Лист1) для подведения итогов за год. Если все листы с месячными отчетами имеют одну и ту же структуру, то для суммирования данных по  месяцам   допустимо  применить  формулу  с  трехмерной  ссылкой.  Так,   формула

=СУММ(Лист2:Лист13!В5) складывает все значения, содержащиеся в ячейках В5 на всех листах  книги,  от  Листа2  до  Листа13.  Чтобы   создать  такую  формулу,  выполните следующие действия:

1.   В ячейку В5 листа Лист1 введите =СУММ(.

2.   Щелкните на ярлычке Лист2 и выделите ячейку В5.

3.   Покажите   на   экране   ярлычки   нужных   листов.   Используйте   для   этого   кнопки прокрутки, расположенные слева от них.

4.   При нажатой клавише Shift щелкните на ярлычке Лист13. Обратите внимание, что его соседи  в  диапазоне  с  Лист2  по  Лист13   оказались   выделенными  белым  цветом, указывающим, что все эти листы будут включены в трехмерную ссылку.

5.   Выделите ячейку В5.

6.   Поставьте закрывающую скобку и нажмите клавишу Enter.

СМОТРИ ТАКЖЕ  –

Подробно  о  групповом  редактировании  говорится  в  разделе   «Редактирование  группы листов» главы 7.

7.   При работе с трехмерными ссылками можно использовать следующие статистические функции: СУММ (SUM), МИН (MIN), ДИСП (VAR), СЧЕТЗ (C0UNTA), ПРОИЗВЕД (PRODUCT), ДИСПР (VARP),  СРЗНАЧ (AVERAGE), СТАНДОТКЛОН (ST0EV), СЧЕТ (COUNT), МАКС (МАХ) и СТАНДОТКЛОНП (STDEVP).

Форматирование в строке формул

С целью улучшения восприятия и упрощения работы с формулой вы вправе включать в нее  пробельные  символы  и  разрывы  строки.  Это   никак  не  повлияет  на  результат вычислений. Чтобы добавить в формулу разрыв строки, нажмите клавиши Alt+Enter. На рис. 12.9 приведен пример такой формулы.

Рис. 12.9. Для облегчения восприятия формулы добавьте в нее пробелы и разрывы строки

Обозначения диапазонов в формулах

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

СМОТРИ ТАКЖЕ

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

заголовков. Об это рассказывает раздел «Присвоение имен ячейкам и диапазонам».

СОВЕТ  

По умолчанию режим «пересечения языкового барьера» всегда активен. Если вы не хотите, чтобы он был задействован, откройте диалоговое окно Параметры (Options), перейдите на вкладку Вычисления (Calculation) и сбросьте флажок Допускать названия диапазонов (Accept Labels In  Formulas). Этот параметр не имеет глобального действия, но  запоминается для каждой отдельной книги.

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

При обычной записи формула в строках 9 и 10 выглядела бы как =СУММ(В4:В7). Если же ссылки на ячейки подменить обычными словами, эту же формулу можно переписать в виде =СУММ(Кв 1) (см. рис. 12.10). В таком варианте Excel просматривает все заголовки строк  и   столбцов  таблицы,  и  если  обнаруживает  нужный,  обращается  затем   для вычисления формулы к довольно сложному набору правил. Не  будем  на нем детально останавливаться, заметим лишь следующее:

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

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

Если  программа  не  в  состоянии  точно  разобраться  в  ваших   намерениях,  на  экран выводится диалоговое окно Поиск ячейки (Identify Label), как показано на рис. 12.11.

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

=Регион 2 Кв 2 определяет ячейку С5, поскольку она находится в  узле, образуемом столбцом   Кв   2   и   строкой   Регион   2.   Обратите   внимание,   вы   должны   между обозначениями диапазонов поставить пробел.

Рис. 12.11. Диалоговое окно Поиск ячейки

Формулы такого рода разрешено точно так же копировать и вставлять в  другие ячейки листа,  при  этом  Excel  автоматически  настраивает  эти   формулы  в  соответствии  с местоположением целевой ячейки, как в таблице на рис. 12.11, где формула из ячейки В9 была  тиражирована  в  диапазон  С9:Е9.  Все  формулы  данного  диапазона  изменяются таким  образом, чтобы в них использовались обозначения соответствующих  диапазонов. Так, формула из ячейки В9 после копирования в ячейку Е9 приняла вид =СУММ(Кв 4).

ПРИМЕЧАНИЕ

При копировании естественно-языковых формул в совершенно неподходящее для них место Excel  всегда  выводит  предупреждение.  Например,  при  попытке  копировать  формулу  из ячейки Н9 в ячейку J9 в ней появится текст ИМЯ? (NAME?), поскольку столбец J не имеет заголовка.

Если  вы  измените  обозначения  диапазонов,  Excel  автоматически  обновит  и  ссылки соответствующих формул. Например, если вместо  текста Всего в ячейке G3 (см. рис. 12.11) ввести За год, формула в ячейке G9 будет выглядеть как =СУММ(За год).

СОВЕТ

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

Естественно-языковая форма записи существенно облегчает чтение и понимание формул,

но, как и любая сложная система, порождает другие проблемы:

? Очень просто получить сообщение о том, что имя недействительно.

? Результат вычислений может оказаться неопределенным (ошибка #ПУСТ0! (#NULL!)).

? При  редактировании  формулы  ее  часть  может трактоваться  как  название функции,

например для формулы =Col Row вероятна следующая интерпретация: =Col ROW().

Причин тому может быть несколько:

>  Формула   содержит   подпрограмму   VBA,   имя   которой    совпадает   с   обозна чением диапазона.

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

>  В данной рабочей книге уже присутствует идентичное имя  (вопросы именования ячеек и их диапазонов рассматриваются ниже в разделе «Присвоение имен ячейкам и диапазонам»).

>  Диапазон назван так же, как и встроенная функция Excel.

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

Соответственно, формулу =Кв 1 Регион 1 лучше записать в виде =’Кв 1’  ‘Регион 1′.

Замечания о пересечениях диапазонов

Если в ячейку I4 листа, изображенного на рис. 12.11, ввести формулу =Кв 1*4, Excel будет считать, что нас интересует только одно значение Кв 1 из диапазона В4:В7 — именно то, которое находится в той же строке, что и  формула (в данном случае в ячейке В4). Это называется неявным  пересечением (implicit intersection). Если скопировать формулу =Кв 1*4 в  диапазон  I5:I7, каждая  ячейка  этого  диапазона  станет содержать  одну  и  ту  же формулу, с той разницей, что в ячейке I5 она будет фактически ссылаться на В5, в I6 — на В6 и т. д.

Явное  пересечение  (explicit intersection) указывает на  конкретную  ячейку  и задается  с помощью оператора пересечения (intersection operator) — символа пробела. Если в любом месте листа ввести формулу =Кв 1 Регион 1*4, программа воспримет это так, как будто мы   собираемся  в  данной  ячейке  использовать  значение,  лежащее  на   пересечении диапазона Кв 1 и диапазона Регион 1, то есть значение из ячейки В4.

Автоматическая подстановка обозначений диапазонов

Excel  умеет  автоматически  заменять  ссылки  на  ячейки   обозначениями  диапазонов таблицы. Для этого их необходимо зарегистрировать:

1.   Выберите в меню Вставка команду Имя (Name), а затем — Заголовки диапазонов (Label). На экране появится диалоговое окно Заголовки диапазонов (Label Ranges), показанное на рис. 12.12.

2.   Щелкните в поле Добавить диапазон (Add Label Range) и  выделите с помощью мыши нужный диапазон.

3.   Установите переключатель в одно из положений: Названия строк (Row Labels) или

Названия столбцов (Column Labels).

4.   Нажмите кнопку Добавить (Add), чтобы включить выделенный  диапазон в список

Имеющиеся диапазоны (Existing Label Ranges).

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

Рис. 12.12. Диалоговое окно Заголовки диапазонов

Впоследствии ссылки в уже существующих формулах не претерпят никаких изменений, но при вводе любых новых формул Excel автоматически будет замещать в них ссылки на ячейки на соответствующий текст. Так, если в примере на рис. 12.12 щелкнуть на ячейке I5, затем ввести знак равенства и щелкнуть на ячейке С5, то в  обычном случае Excel вывел бы в этой ячейке ссылку на С4, но теперь в ней появится значение Кв 2 Регион 2.

РЕШЕНИЕ ПРОБЛЕМ

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

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

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

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

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