2 семестр / vba_2002

Базы данных рабочих листов

Обычно, когда указатель находится внутри базы данных, Excel распознает ее и по мере возможности отображает имена полей. Например, если вы переместите указатель внутрь базы данных, которая находится на рабочем листе, и затем выполните команду Данные1^Сортировка, то сможете указать критерии сортировки, выбрав в раскрывающихся списках необходимые имена полей.

Особенно полезной является такая возможность Excel, как автофильтр— он позволяет отображать только те записи, которые вы хотите видеть на экране. Когда включен, этот режим, вы можете фильтровать данные, выбирая значения из раскрывающихся списков. Данные списки появляются там, где введены имена полей. Для активизации автофильтра следует выполнить команду Данные^Фильтр^Автофильтр. Строки, не удовлетворяющие условию фильтрации, будут временно скрыты. На рис. 2.8 показан один из примеров того, как используется автофильтр в реальном рабочем листе,

Puc.2.8.ВExcelспомощьюавтофильтрамоленопросматриватьтолько
те записи базы данных, которыеудовлетворяют вашим критериям	"",

Если при управлении базами данных электронных таблиц вы предпочитаете использовать

те традиционные приемы, в которых задействуете» несколько критериев, выполните команду

Данные^Фильтр^Расширенный фильтр.

Внешние базы данных

Чтобы иметь возможность работать с таблицами внешних баз данных, выполните команду Данные^Импорт внешних данных. Запустится программа Microsoft Query, и вы сможете выбрать исходные базы данных, а также определить запросы к ним. Результаты выполнения запросов передаются непосредственно на рабочий лист.

Начиная с Excel 97, вы также можете создавать Web-запросы, чтобы получать данное, хранящиеся в корпоративной сети или в глобальной сети Internet.

Кроме того, используя технологии DAO (Data Access Objects — объекты доступа к данным) и ADO (ActiveX Data Objects — объекты данных ActiveX), вы имеете возможность работать

Часть I. Введение в Excel

59

с объектами данных, созданных вне Excel, Обе эти технологии позволяют с помощью VBA получить доступ к внешним базам данных, и, кроме того, ADO используется для изменения данных непосредственно в базе данных, а не только на рабочем листе.

Функции использования Internet

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

Файлы Excel можно сохранять в формате HTML, что обеспечивает значительную интерактмвность. Эта возможность, представленная благодаря Web-компонентам пакета Office, позволяет публиковать на Web-сервере интерактивные рабочие книги и дает возможность работать с ними другим пользователям (которые имеют лицензию на Web-компоненты пакета Office).

Инструменты анализа

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

Структуры

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

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

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

Управление сценариями

Если вы ищете наиболее эффективное средство управления сценариями, то это, скорее всего, окажется менеджер версий из Lotus 1-2-3. По сравнению с ним диспетчер сценариев Excel выглядит слабовато, хотя и справляется с простыми задачами управления сценариями. Использовать его намного удачнее, чем вручную отслеживать разные сценарии.

Analysis ToolPack

В надстройке Analysis ToolPack (Пакет анализа) содержится 19 специальных инструментов анализа (в основном, статистических по своей природе), а также ряд специализированных функций рабочих листов. Благодаря этим инструментам к программе Excel можно смело обращаться для проведения статистического анализа данных.

60	Глава 2. Вкратце об Excel

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

Сводные таблицы

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

О том, как с помощью VBA управлять сводными таблицами, рассказывается в главе 17.

Аудит

Кроме того, в Excel существуют и другие средства, которые помогают найти ошибки или проследить логику незнакомой электронной таблицы. Чтобы получить доступ к этим инструментам, выполните команду Сервис^Зависимости формул.

Поиск решения

Для решения специальных линейных и нелинейных задач в Excel применяется надстройка Поиск решения, которая использует структуры "что-если" для подбора данных в одних ячейках, на основе ограничений, накладываемых на другие ячейки. Средство Поиск решения имеет много общего с подобным инструментом Lotus 1-2-3 для Windows и Quattro Pro для Windows. (Пусть это сходство вас не удивляет, поскольку все они разрабатывались одной компанией — Frontline Systems.)

Надстройки

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

Кроме надстроек, которые поставляются вместе с Excel, существуют и другие, загружаемые с Web-узла компании Microsoft. Более того, на рынке также представлены надстройки сторонних производителей; эти надстройки можно или покупать, или загружать из Internet. Вы также можете легко создавать свои собственные надстройки, о чем подробно рассказывается в главе 21.

ЧастьI. ВведениевExcel

61

Совместимость

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

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

Если вы опытный пользователь программы Lotus 1-2-3 для DOS, то в ваше распоряжение предоставляется подробная справочная система, разработанная таким образом, чтобы научить вас мыслись категориями Excel.

Отдельный аспект совместимости — это поддержка предыдущих версий Excel. Здесь разработчиков могут подстерегать определенные сложности, которые обсуждаются в главе 26.

Резюме

В этой главе представлен концептуальный обзор Excel 2002, особенно полезный для новичков.

Наше повествование продолжится главой 3, в которой речь пойдет о формулах.

62	Глава 2. Вкратце об Excel

Особенности

использования

формул

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

Создание формул можно рассматривать как своего рода "программирование". В этой главе описаны возможности Excel по управлению формулами и рассмотрены некоторые методы их применения.

О формулах

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

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

Ниже приведены элементы, которые представляют часть введенной в ячейку формулы:

Шоператоры — например, + (сложение) и * (умножение);

Шссылки на ячейки (в том числе имена ячеек и их диапазонов);

•значения или строки;

•функции рабочих листов (например, СУММ или СРЗНАЧ).

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

Подробно использование формул рассмотрено в книге

Подробное руководство по созданию формул в Excel 2002,

выпущенной издательством "Диалектика".

В Excel 2002 появилось несколько новых функций управления формулами. Воэ- V можно, самая полезная из них — это команда пометки тех формул, которые могут _Н|Ш вызывать ошибки. Формулы помечаются как автоматически, так и по запросу. Соответствующие параметры можно задавать на вкладке Проверка ошибок диалогового окна Параметры. Еще одной новой функцией является диалоговое окно Вычисление формулы. С его помощью вы оцените результаты выполнения отдельных частей сложной формулы. Такая возможность также представляется при

использовании панели инструментов Зависимости.

Вычисление значений формул

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

•Когда вы вносите изменения (например, вводите или редактируете данные или формулы). Excel немедленно вычисляет значения формул уже с учетом новых или отредактированных данных.

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

•Формулы вычисляются в естественном порядке. Другими словами, если формула в ячейке D12 зависит от результата вычисления формулы в ячейке D11, то сначала вычисляется значение в ячейке D11, а только потом— в ячейке D12.

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

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

М При нажатии клавиши <F9> вычисляются значения формул во всех открытых рабочих книгах.

•При нажатии комбинации клавиш <Shift+F9> вычисляются значения формул только в активном рабочем листе. В других рабочих диетах этой же рабочей книги вычисления не выполняются.

•Комбинация клавиш <Ctrl+Shift+F9> приводит к пересчету абсолютно всего. Эта комбинация клавиш яв;гяется недокументированной. Используйте ее, если Excel по какойлибо причине явно рассчитывает данные неправильно, иди существует необходимость выполнить пересчет формул, в которых используются пользовательские функции, созданные на языке VBA.

		В Excel режим вычисления не относится только к одной (текущей) рабочей таб-
|На заметку		лице. Изменение этого режима затрагивает все открытые рабочие книги, а не
*	„--"•"•	только активную.
64		Глава 3. Особенности использованияформул

Ссылки на ячейки и диапазоны

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

•Относительная. Ссылка является полностью относительной. Когда формула копируется, то ссылка изменяется в соответствии с новым местоположением формулы (например: А1).

•Абсолютная. Ссылка является полностью абсолютной. Когда формула копируется, ссылка не меняется (например: $А$1).

•Абсолютная строка. Ссылка является частично абсолютной. Когда формула копируется, то та часть ссылки, которая указывает столбец, меняется в соответствии с новым местоположением формулы, а строчная часть ссылки остается неизменной (например: А$1).

•Абсолютный столбец- Ссылка является частично абсолютной. Когда формула копируется, то строчная часть ссылки меняется в соответствии с новым местоположением формулы, а та часть ссылки, которая указывает столбец, остается неизменной (например: $А1).

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

С какой целью используются неотносительные ссылки

Думая об этом, вы поймете, что единственная причина, по которой когда-либо придется изменить тип ссылки— это необходимость копирования формулы. Почему это так, показано на рис. 3.1. В ячейке С4 находится следующая формула:

=С$3*$В4

Рис.3.1.Примерформулыснеотносителънымиссылками
Часть I. Введение в Excel	65

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

О ссылках R1C1

Как правило, в Excel используется формат записи ссылок А1. Каждый адрес ячейки, отображаемый в таком формате, состоит из буквы, которая обозначает столбец, и числа, которое соответствует строке. Впрочем, в Excel также поддерживается формат записи ссылок RJC1. (Здесь R означает row, то есть "строка", а С — column, то есть "столбец"). В этом формате ячейка А1 обозначается как R1C1. а А2 — соответственно, как R2C1 и т.д.

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

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

Таблица 3.1. Сравнение простых формул, выведенных в одном | из двух форматов записи

Стандартный	R1C1
s*l+l	=RC[-1]+1
=SA$1+1	=R1C1+1
=SA1+1	=RC1+1
=A$1+1	=R1C[-1]+1
=СУММ(А1:А10)	= СУММ(11С[-1]:R[9]C[-1])
СУММ($А$1:$А$10)	=CyMM(RlCl:R1OC1)

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

Числа в квадратных скобках обозначают относительное местоположение ссылок. Например, ссылка Rf-5]C[-3] указывает на ячейку, которая находится на пять строк выше и на три столбца левее той ячейки, в которой расположена текущая ссылка. С другой стороны, ссылка R[5]C[3] обозначает ячейку, расположенную пятью строками ниже и тремя столбцами правее текущей. Если квадратных скобок нет, то это означает ту же самую строку или тот же самый столбец. Например, R[5]C указывает на ячейку, расположенную пятью строками ниже в текущем столбце.

Скорее всего, формат R1C1 не станет для нас используемым по умолчанию, однако он все же вам пригодится. С его помощью легко отыскать формулу с ошибкой, Если вами используется формат R1C1, то любые копии одной и той же формулы будут одинаковыми. Это относится ко всем типам применяемых вами ссылок на ячейки (относительных, абсолютных или смешанных). Можете перейти в режим RIC1 и проверить скопированные формулы. И если ка- кая-либо из них отличается от остальных, то, скорее всего, она и является неправильной.

66	Глава 3. Особенности использования формул

Кроме того, если вы создаете код VBA для получения формул рабочих листов, то, возможно, предпочтете формат R1CI.

Ссылки на другие листы или рабочие книги

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

=Лист2!А1+1

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

=[Бюджет.xls]Лист1IA1 + 1

Если в имени рабочей книги, используемом в ссылке, содержатся пробелы, то его (вместе с именем рабочего листа) необходимо заключить в одинарные кавычки. Например: ='[Бюджет на 2002 год]Лист1'!А1+1

Указанная в ссылке рабочая книга может быть закрыта, тогда в ссылке следует указать полный путь к этой книге. Например:

='С:\MSOffice\Excel\[ Бюджет на 2002 год]Лист1'!А1+1

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

Использование ссылок для восстановления данных в поврежденном файле

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

Когда не удается загрузить поврежденную рабочую книгу Excel, напишите формулу со ссылкой, чтобы восстановить все или часть данных (но только не формулы). Дело в том, что исходный файл, указанный в формуле со ссылкой, открывать нет необходимости. Если испорченный файл называется, например, B a d f i l e . x l s , то, чтобы восстановить данные листа Лист1 поврежденного файла, откройте пустую рабочую книгу и на ее листе Листе1 введите

вячейке А1 следующую формулу:

='С: \Files\ [Badfile.xls]JlMCTl • !А1

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

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

Часть I. Введение в Excel

67

Использование имен

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

Присвоение имен ячейкам и диапазонам

Имена ячеек и диапазонов можно создавать с помощью команды Встаека^Имя^Прис - воить (или комбинации клавиш <Ctrl+F3>). Впрочем, создавать имена можно еще быстрее (воспользуйтесь полем имен — раскрывающимся списком, который расположен в левой части строки формул). Вам достаточно выбрать одну ячейку или диапазон ячеек, ввести необходимое имя в поле имен, а затем нажать <Enter>.

Имена ячеек или диапазонов можно создавать автоматически, на основе заголовков строк и столбцов рабочего листа. Для этого выполните команду Вставка^Имя^Создать. Например, на рис. 3.2 показано, что диапазон С4 : F4 получил название Север, С5 : F5 — название Юг и т.д. Что касается вертикальных диапазонов, то СЗ : Сб назван Квартал 1, D3 : D6 — Квартал 2 и т.д.

Рис. 3.2. В рабочих листах Excel можно создавать описательныеимени

Использование имен особенно эффективно при написании кода VBA, в котором применяются ссылки на отдельные ячейки или диапазоны. Почему же так важны имена? Ответ заключается в следующем: если ячейку или диапазон, на которые ссылается оператор VBA, вы переместите в другое место, то в VBA-коде автоматически эти ссылки обновляться не будут. Например, если в VBA-коде значение записывается в ячейку С4, заданную как Range ("С4 " ) , то после вставки новой строки над этой ячейкой или нового столбца слева от нее, данные будут записываться не в требуемую ячейку. Чтобы не возникало подобных проблем, применяйте ссылки на именованные ячейки, например, Range (" I n t e r e s t R a t e " ) .

Использование имен существующих ссылок

Когда ячейке или диапазону ячеек задается имя, то Excel автоматически не использует его вместо ссылок на ячейку ИЛИ диапазон, которые уже содержатся в формулах. Предположим, что в ячейке F10 находится формула:

=А1-А2

68	Глава 3. Особенности использования формул