2 семестр / vba_2002

На	На Web-узле издательства содержится надстройка Extended	Date Functions
Web-узле \	{Расширенные функции даты). Установив ее, вы получите доступ к восьми но-
	вым функциям рабочего листа, которые позволяют работать с любыми датами в
	диапазоне от 100-ого до 9999-го года. На рис. 3.8 показан рабочий лист, в кото-
	ром эти функции подсчитывают количество дней между датами до	1900 года.

Рис.3.8.НадстройкаExtendedDateFunctionvпозволяетуправлять дамами до 1900 года

Создание мегаформул

Зачастую для получения необходимого результата в электронных таблицах используются промежуточные формулы. Т.е. формула может зависеть от других формул, которые, в свою очередь, зависят еще от каких-либо формул. Добившись, чтобы все они работали правильно, вы получаете возможность удалить промежуточные формулы и использовать вместо них единственную формулу, которая называется мегаформулой. Каковы ее преимущества? Используется меньше ячеек (и, следовательно, рабочий лист не так загроможден), а также быстрее происходит пересчет данных. Кроме того, понимающие пользователи будут поражены вашими "формулотворческими" способностями. Существуют ли в ней недостатки? Да, такую формулу совершенно невозможно понять или изменить.

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

Рис. 3.9. Для удаления вторых имен и инициалов требуется шесть промежуточных формул

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

79

На рис. 3.9 представлен результат довольно удачного решения — применено шесть промежуточных формул, перечисленных в табл. 3.5. Имена с фамилиями находятся в столбце А, а конечный результат — в столбце Н. Что же касается столбцов от В до G, то в них как раз и содержатся промежуточные формулы.

Таблица 3.5. Промежуточные формулы, введенные в первой строке рабочего листаЛист!(рис.3.9)

Ячейка	Промежуточная формула	Выполняемые функции
В1	= С Ж П Р О Б Е Л Ы ( А 1 )	Удаляет лишние пробелы
С1	= Н А Й Т И (" ",- В1,-1)	Находит первый пробел
D1	=НАЙТИ (" - ;В1;С1+1)	Находит второй пробел
Е1	= Е С Л И { ЕОШИБКА {D1); c i ; DI )	Использует первый найденный пробел, если не най-
		ден второй
F1	=ЛЕВСИМВ ( B l ; c i )	Выделяет первое имя
G1	=ПРАВСИМВ (В1;ДЛСТР{В1) - El)	Выделяет последнее имя (то есть фамилию)
HI	=Fl&Gl	Выполняет конкатенацию двух имен

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

=ЛЕВСИМБ{СЖПРОБЕЛЫ(А1);НАЙТИ{"";СЖПРОБЕЛЫ(Al};1)) &ПРАВС№1В(СЖПРОБЕЛЫ(А1);ДЛСТР(СЖПРОБЕЛЩА1))- ЕСЛИ(ЕОШИЕКА(НАЙТИ(" ";СЖПРОБЕЛЫ(А1);НАЙТИ(" СЖПРОБЕЛЫ(А1);1)+1>);НАЙТИ{" ";СЖПРОБЕЛЫ(А1);1); НАЙТИ(" ";СЖПРОБЕЛЫ(А1);НАЙТИ{" ";СЯПРОБЕЛЫ(А1);1)+1)))

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

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

АСуществует единственное ограничение, которое накладывается на мегаформу-

ммепсу,	лы: длина формулы Excel не может превышать 1024 символа. Решение этой про-
^*10Г	блемы заключается в создании на языке VBA пользовательской функции рабоче-
	го листа. Тогда мегаформулу можно заменить простой формулой, например:
	NOMIDDLE(Al)
	Я действительно написал такую функцию, чтобы сравнить ее с промежуточными
	формулами и мегаформулами.
Сложность мегаформул наталкивает на мысль, что при их использовании вычисления могут
существенно замедляться. На самом деле это не так. Создадим рабочий лист, в котором мега-
формула использовалась 65536 раз. Затем создадим другой рабочий лист, в котором применено
80	Глава 3. Особенности использования формул

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

I Таблица 3.6. Сравнение промежуточных формул с мегаформулой и VBA-функцией

Метод		Время пересчета (в секундах)		Размер файла
Промежуточные формулы		10,8		24,4 Мбайта
Мегаформула		6,2		8,9 Мбайта
УВД-функция		106J		8,6 Мбайта

Результаты, получаемые на практике, будут существенно отличаться от указанных в таблице. Как именно — зависит от производительности системы и объема установленной памяти.

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

/ На Web-узле издательства можно найти асе три файла, которые участвовали в /наимтку этом тестировании.

Резюме

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

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

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

81

Файлы Excel

Если	вы собираетесь профессионально работать с Excel,
	то просто обязаны ознакомиться с несколькими спосо-

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

Запуск Excel

Программу Excel можно запускать по-разному (в зависимости от способов ее установки). Но все варианты в конечном итоге сводятся к запуску исполняемого файла E x c e l . ехе.

Запускаясь, программа Excel считывает из системного реестра Windows значения настроек и подключает все установленные надстройки. (Имеются в виду те надстройки, рядом с названиями которых в диалоговом окне Надстройки выставлены фляжки). Затем отображается пустая рабочая книга; количество листов этой книги определяется значением параметра, задаваемого пользователем и хранящегося в системном реестре. Это число можно изменить, выбрав на вкладке Общие диалогового окна Параметры опцию Листов в новой книге. Само диалоговое окно отображается в результате выполнения команды Сервис=>Параметры.

Если в папке x l s t a r t (дословно означает "запуск Excel") содержатся рабочие книги, то они автоматически откроются, а пустая книга не будет отображаться на экране. Если же в этой папке находится файл рабочего пространства, то в специально подготовленном рабочем пространстве может открыться несколько файлов. Впрочем, вы всегда вправе определить другую панку Д1Я запуска. Для этого соответствующий путь указывается в поле Каталог автозагрузки вкладки Общие диалогового окна Параметры.

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

сохраните в папке x l s t a r t в виде шаблона с именем Book.xlt. Подробно о создании и использовании файлов шаблонов рассказывается в справочной системе программы.

Excel распознает несколько переключателей, командной, строки. Они описаны в табл. 4. L

Таблица 4.1, Переключатели командной строки Excel

Переключатель		Операция
/ a u t o m a t i o n		Excel запускается без подключения надстроек и шаблонов, а также загрузки файлов
		из папки x l s t a r t или любой другой папки автозагрузки. Этот переключатель ис-
		пользуется для "чистой начальной загрузки" Excel
/ е		Excel запускается во "внутреннем" режиме. Этот переключатель применяется тогда,
		когда требуется запустить программу Excel без вывода на экран надоедливой за-
		ставки и создания новой рабочей книги
/embedded		Запускает скрытый сеанс Excel (не рекомендуется)
/т		Программа создает новую рабочую книгу с единственным макролистом Excel 4.0
		(устаревший объект)
/ о		Excel регистрируется а системном реестре Windows, Заменяет пропущенные записи
		реестра. Неправильные записи реестра не исправляются (см. далее в этой
		табл. переключатель / r e g s e r v e r )
/р	п а п к а	Задает путь к папке, не указанной по умолчанию
/г	имя файла	Программа открывает указанный файл в режиме только чтения
/s		Запускает Excel в "безопасном" режиме; не загружает надстроек и файлов, распо-
		ложенных в папке x l s t a r t или другой папке автозапуска
/ r e g s e r v e r		Excel регистрируется в системном реестре Windows, а затем оканчивает сеанс своей
		работы. Используйте этот переключатель, если требуется, чтобы программа Excel
		перезаписала все параметры реестра и заново создала связи с необходимыми ти-
		пами файлов, такими, например, как рабочие книги и диаграммы
/ u n r e g s e r v e r		Программа удаляет данные о себе из системного реестра Windows, а затем оканчи-
		вает сеанс работы

Указать один из этих переключателей можно, в частности, отредактировав свойства ярлыка, с помошью которого запускается программа Excel. Например, если требуется, чтобы папка XIfiles была назначена рабочей папкой программы Excel, то с помощью переключателя /р вы выполните данную задачу в поле Рабочая папка, которое находится в диалоговом окне свойств для соответствующего ярлыка. Чтобы отобразить диалоговое окно Свойства, щелкните правой кнопкой мыши на ярлыке, а затем — левой кнопкой мыши на вкладке Ярлык. Например, содержимое поля Рабочая папка можно изменить следующим образом:

"C:\Program Files\Microsoft Office\Office\EXCEL.EXE" /р C:\Xlfiles

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

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

13

Расширения файлов Excel

Программа Excel использует, кроме E x c e l . ехе, и другие файлы. Все они загружаются в память по мере необходимости. В табл. 4.2 перечислены типы файлов, которые записываются на жесткий диск во время установки Excel, а также те, которые встречаются при создании электронных таблиц.

Таблица 4.2. Типы файлов, устанавливаемых с программой Excel

Тип файла	Описание
СНМ	Компилированный HTML-файл справки
DLL	Файл библиотеки динамической связи (Dynamic Link Library). Файлы DLL используются при-
	ложениями Windows для хранения программного кода
ЕХЕ	Исполняемый файл. Исполняемым файлом, который запускает приложение Excel, является
.	E x c e l . е х е
OLB	файл библиотеки типов объектов (object type library)
TXT	Простой текстовый ASCII-файл, который можно открыть в любом текстовом редакторе
	(таком, например, как Блокнот). В данных файлах часто приводится самая свежая инфор-
	мация о программе, которая отсутствует в руководстве
XLA	файл надстройки Excel. Несколько таких файлов поставляются вместе с Excel. Кроме них,
	вы можете загружать другие надстройки, в том числе собственного производства
XLS	Файл конфигурации панелей инструментов Excel. Текущая конфигурация панелей инстру-
	ментов сохраняется в папке Windows, в файле E x c e i i o . x i b (что касается сетевой сре-
	ды, то соответствующий файл конфигурации имеет другое имя)
XLC	файл диаграммы Excel 4 (устаревший, начиная с Excel 5)
XLL	файл библиотеки ссылок Excel. Например, файлы этого типа используются надстройкой
	Analysis TooPak (пакет инструментов для анализа данных)
XLM	файл макросов Excel 4 (устаревший, начиная с Excel 5)
XLS	файл рабочей книги Excel. К сожалению, по расширению такого файла нельзя определить
	версию Excel, в которой он создан
XLT	Файл шаблона Excel *
XLW	Файл рабочего пространства, содержащий информацию об окнах и их расположению в ра-
	бочем пространстве. Данное расширение также использовалось для файлов документов
	Excel 4 (в этом понимании оно является устаревшим, так как, начиная с Excel 5, все файлы
	документов Excel являются рабочими книгами)
XLK	файл резервной копии
	В версиях Excel, предшествовавших Excel 2000, использовались стандартные
	файлы справочной системы Windows { « . h i p ) . Начиная с Excel 2000, применяет-
	ся справочная система, разработанная в формате HTML, в которой используются
	компилированные HTML-файлы с расширением * , chin.

84	Глава 4. Файлы Excel

Поддерживаемые форматы файлов электронных таблиц

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

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

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

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

Файлы электронных таблиц Lotus 1-2-3

Существует несколько разновидностей электронных таблиц Lotus.

• Файлы WKS— файлы одного рабочего листа, которые используются в Lotus 1-2-3 версии 1 .х для DOS. В Excel их можно не только открывать, но и сохранять.

Кроме того, в Excel вы откроете файлы Microsoft Works, которые также имеют расширение WKS.

•Файлы WKJ — файлы одного рабочего листа, которые используются в Lotus 1-2-3 версии 2_\ для DOS. Форматирование тгнх файлов сохраняется в файлах с расширением * . a l l (сохраняются надстройкой Allways) или * . fml (сохраняются надстройкой WYSIWYG (What You See Is What You Get, "ты видишь то, что получаешь"). Программа Excel открывает и сохраняет все указанные файлы. Сохраняя файл в формате

*. wkl, вы можете выбрать тип форматирования данных, а также расширение файла форматирования.

•Файлы WK3 генерируются в Lotus 1-2-3 версии 3.x для DOS, Lotus 1-2-3 версии 4..v для DOS и Lotus 1-2-3 версии l..v для Windows. В этих файлах может сохраняться больше одного листа. Форматирование хранится в файлах с расширением * . fm3 (сохраняется надстройкой WYSIWYG). Программа Excel может читать и сохранять файлы WK3. к каждому из которых прилагается (не обязательно) свой файл FM3.

• Файлы WK4 генерируются в Lotus 1-2-3 версии 4 ..г для Windows и Lotus 1-2-3 версии 5..V для Windows (компания Lotus, наконец-то, устранила главный недостаток — обязательность отдельного файла форматирования). В этих файлах может сохраняться больше одного листа. Программа Excel не открывает и не сохраняет такие файлы. Если вам необходимо открыть в Excel файл WK4, то воспользуйтесь единственным спо-

собом: с помощью Lotus	I-2-3 версии 4 для Windows (или более поздних) сохраните
файл в формате WK3, который Excel понимает.
Часть /. введение в Excel	85

• Файлы 123 генерируются Lotus 1-2-3 97 и Lotus 1-2-3 Millennium Edition. В них сохраняется более одного листа. Excel также не открывает и не сохраняет эти файлы, Если вам потребуется прочитать в Excel файл формата 123, то для этого подойдет только один способ. Воспользовавшись Lotus 1-2-3, сохраните его в формате WK3, который программа Excel понимает.

Файлы электронных таблиц Quattro Pro

Файлы программы Quattro Pro также представлены несколькими версиями.

•Файлы WQI — файлы одного рабочего листа, которые генерируются Quattro Pro для DOS версий 1, 2, 3 и 4. Эти файлы про(рамма Excel может и открывать, и сохранять.

•Файлы WQ2 генерируются Quattro Pro для DOS версии 5. Этот формат файлов программа Excel не может ни открывать, ни сохранять.

•Файлы WBI генерируются Quattro Pro для Windows версий 1 и 5 (версии со 2 по 4 отсутствуют). Этот формат программа Excel может только открывать, но не сохранять.

•Файлы WB2 генерируются Quattro Pro для Windows версии 6. Этот формат файлов программа Excel не может ни открывать, ни сохранять.

•Файлы WB3 генерируются Quattro Pro для Windows версий 7 и 8. Этот формат файлов программа Excel не может ни открывать, ни сохранять.

Загрузите с Web-узла компании Microsoft конвертор файлов Quattro Pro. Он позволит импортировать файлы WB3, созданные с помощью Quattro Pro 97. Загружается конвертор со следующей страницы:

http://o£ficeupdate.microsoft.com/downloadDetails/quattr97.htm.

Форматы файлов баз данных

Файлы DBF— это однотабличные файлы баз данных, генерируемые dBASfi и некоторыми другими процессорами баз данных. Программа Excel открывает и сохраняет файлы DBF до формата dBASE 4 включительно.

Что касается других форматов баз данных, то открывать и сохранять их напрямую Excel не позволяет. Впрочем, для доступа к файлам других форматов баз данных можно использовать Microsoft Query, а затем скопировать требуемые данные непосредственно на рабочий лист Excel или связать их с этим листом. Microsoft Query можно запустить непосредственно из Excel, выполнив команду Д а н н ы е * Импорт внешних данных ^Создать запрос.

Форматы текстовых файлов

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

• В файлах, разграниченных позициями табуляции, каждая строка состоит из полей, которые отделены друг от друга символами табуляции. Программа Excel открывает эти файлы, преобразуя каждую строку текста в строку электронной таблицы, а каждое по- л е — в столбец. Кроме того, Excel сохраняет файлы в подобном формате, присваивая им по умолчанию расширение * . tx1:.

86	Глава 4. Файлы Excel

•В файлах, разделяемых запятыми, каждая строка состоит из полей, которые обычно отделены друг от друга запятыми. (Что же касается национальных настроек, в которых десятичные дроби содержат запятые, то тогда в текстовых файлах в качестве разделителей используются точки с занятой.) Иногда текст заключается в кавычки. Программа Excel открывает файлы, преобразуя каждую строку текста в строку электронной таблицы, а каждое поле — в столбец. Кроме того. Excel сохраняет эти файлы, присваивая им по умолчанию расширение CSV.

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

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

Если мастер импорта данных не нужен, отключите его: держите нажатой клавишу <Shift>, когда щелкаете на кнопке ОК в диалоговом окне открытия документа.

Пользуясь текстовыми файлами, можно также создавать запросы. Для этого выполните команду Данные1^ Им порт внешних данных1* Импортировать данные.

Другие форматы файлов

ВExcel также поддерживаются следующие форматы файлов.

•DIF (Data Interchange Format — формат обмена данными) используется программой VisiCalc. Встречаются достаточно редко. Эти файлы программа Excel может к открывать, и сохранять.

•SYLK (SYmbolic LinK— символическая связь) используется программой MultiPlan. В настоящее время файлы SYLK также являются большой редкостью. Программа Excel их может и открывать, и сохранять.

Файлы, сохраняемые в Excel

Excel сохраняет файлы нескольких типов. О них речь пойдет в этом разделе.

Файл XLS

Файлы рабочих книг XLS, создаваемые программой Excel 2002, поддерживают тот же формат, что Excel 2000 и Excel 97. Эти файлы нельзя открыть ни в одной из версий Excel, предшествовавшей Excel 97. Впрочем, вы вправе сохранить рабочую книгу, указав любой из старых форматов Excel. При этом будет утеряна информация, специфичная для нового формата файла.

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

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

87

Какую версию имеет файл XLS

К сожалению, нет такого способа, который позволяет точно определить, в какой версии Excel создан тот или иной файл XLS. Если у в;эс установлена одна из ранних версий Excel, то при попытке открыть в ней файл XLS, созданный в более поздней версии, вы, скорее всего, получите сообщение об ошибке или рабочую область, заполненную бессмысленным сочетаниям символов. Но если файл все же успешно открыт, то с помощью простого оператора VBA можно легко определить версию Excel, 13 которой он создавался.

Откройте рабочую книгу и проверьте, что она осталась активной. Для запуска Visual Basic Editor (редактор Visual Basic) нажмите комбинацию клавиш <:Alt+F11>, а затем, чтобы активизировать окно Immediate,— <:Ctrl+G>. Перед тем, как нажать <Enter>, введите следующий оператор VBA:

Print ActiveWorkbook.FileFormat

В окне Immediate будет отображено значение, которое соответствует версии активной рабочей книги. Вы увидите одно из значений, перечисленных в следующей таблице.

Значение	Версия Excel
16	Excel 2
29	Excel 3
33	Excel 4
39	Excel 5. 95
-4143	Excel 97, 2000, 2002

Файл рабочего пространства

Файл рабочего пространства — это специальный файл, который содержит информацию

орабочем пространстве Excel. Например, в вашем проекте нспользуются две рабочие книги,

иокна этих книг должны быть определенным образом настроены. Данную "оконную конфигурацию'" можно сохранить в файле с расширением XLW. Для этого выполните команду Файл^Сохранить рабочую область. Впоследствии при открытии такого файла программа Excel восстановит требуемое рабочее пространство.

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

Файлы шаблонов

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

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

88	Глава4.ФайлыExcel