Текст книги "Excel. Трюки и эффекты"

Для запуска созданной программы нажмем кнопку, которой назначен макрос CalcColors – в результате откроется окно, соответствующее пользовательской форме, которая приведена на рис. 5.10. Предварительно следует выделить обрабатываемый диапазон – в этом случае поле Диапазон суммирования будет заполнено автоматически. В противном случае в данном поле следует с клавиатуры ввести требуемый диапазон (например, А5: С15). С помощью параметра Признак суммирования определяется, какой цвет шрифта или заливки будет применяться в качестве критерия отбора. При этом справа в поле Выберите цвет отображается перечень цветов, входящих в обрабатываемый диапазон; для выбора достаточно щелкнуть кнопкой мыши на кнопке соответствующего цвета. В расположенном ниже поле Другой из раскрывающегося списка можно выбрать какой-либо другой цвет. Список включает в себя следующие варианты: Красный, Оранжевый, Желтый, Зеленый, Голубой, Синий, Фиолетовый, Белый, Черный. Справа от поля Другой расположена кнопка, при нажатии которой выбранный цвет будет применен.

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

Если установлен флажок Проверять, то будет включен «обратный фильтр». Иначе говоря, при установленном данном флажке в расчет принимаются те ячейки диапазона, которые не соответствуют указанному цвету. Например, если выбран красный цвет, то ячейки с красным шрифтом (заливкой) не будут приниматься в расчет.

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

• Сумма;

• Среднее;

• Максимум;

• Минимум;

• Количество ячеек;

• Сумма положительных (значений);

• Сумма отрицательных (значений);

• Количество непустых (ячеек);

• Количество непустых ненулевых (ячеек);

• Количество положительных (значений);

• Количество отрицательных (значений).

По умолчанию в поле Тип вычислений установлено значение Сумма.

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

=ColorCalc(«D14:F17»;255;0;0;0)

Эта формула включает в себя следующие элементы:

• ColorCalc – имя функции;

• D14:F17– обрабатываемый диапазон (можно ввести несколько диапазонов – в этом случае их следует указать через запятую);

• 255 – цвет, используемый в качестве критерия отбора (в рассматриваемом примере – красный);

• 0 (первый) – указывает на то, что параметру Признак суммирования установлено значение Шрифт (при выборе значения Заливка в формуле будет отображаться 1);

• 0 (второй) – указывает на операцию, выбранную в поле Тип вычислений (в данном случае – Сумма); соответствующие константы перечислены в начале модуля, содержащего код функции CalcColor;

• 0 (третий) – указывает, что в расчет принимаются данные, соответствующие выбранному цвету (если указано 1, значит, включен «обратный фильтр»; иначе говоря, в окне настройки параметров установлен флажок Проверять).

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

Глава 6
Полезные советы

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

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

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

Каким образом в Excel обрабатываются дата и время как текст или как числа?

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

Какие символы при работе в Excel могут быть интерпретированы как числа?

В программе Microsoft Excel число может содержать только следующие знаки:

0 1 2 3 4 5 6 7 8 9 + – ( ) , / $ % . E e

Все знаки «плюс» (+), стоящие перед числом, игнорируются, а запятая интерпретируется как разделитель десятичных разрядов. Все другие сочетания знаков, состоящие из цифр и иных знаков, распознаются в программе как текст.

Как программа Excel определяет, какой числовой формат необходимо использовать?

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

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

Можно ли в Excel вводить числа как текст и если да, то как это сделать?

Да, такая возможность существует. В программе Microsoft Excel числа хранятся в виде числовых данных, даже если к содержащим их ячейкам был применен текстовый формат. Если такие данные, например коды товаров, необходимо интерпретировать как текст, то сначала желательно применить текстовый формат к пустым ячейкам (в окне Формат ячеек выберите формат Текстовый), а затем ввести числа. Если числа уже введены, назначьте ячейкам текстовый формат и, выделяя каждую ячейку, нажимайте клавишу F2, а затем – Enter, чтобы ввести данные заново.

Сколько значащих цифр поддерживается в программе Excel?

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

Можно ли каким-нибудь образом изменить количество действий «отката», которое установлено в Excel по умолчанию, и если да, то как это сделать?

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

Таким образом, чтобы решить данную задачу, необходимо войти в раздел реестра HKEYCURRENTUSERSoftwareMicrosoftOffice12.0ExcelOptions (вместо подраздела 12.0 может быть подраздел 10.0, 9.0 или 8. 0 – в зависимости от используемой версии программы) и найти параметр UndoHistory типа DWORD. Если такого параметра нет, то его необходимо создать. В качестве значения данного параметра следует указать требуемое количество действий «отката». Чтобы выполненные изменения вступили в силу, может потребоваться перезагрузка компьютера.

Каким образом можно отключить отображение заголовков строк и столбцов?

Для этого необходимо на вкладке Вид нажать кнопку Показать или скрыть. В появившемся окне снять флажок Заголовки.

Можно ли сделать так, чтобы макрос не отображался в окне списка макросов (данное окно вызывается с помощью команды Вид → Макросы → Макросы), и если да, то как это сделать?

Да, при создании или редактировании макроса можно запретить его отображение в окне списка макросов. Для этого нужно использовать ключевое слово Private, например: Private Sub ИмяМакроса (). Если в дальнейшем потребуется включить макрос в список макросов, то ключевое слово Private нужно убрать из кода.

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

Для вызова процедуры из другой рабочей книги нужно воспользоваться методом Run объекта Application. Ниже показан фрагмент программного кода:

Run «Primer.xls!MyMacro»

В данном примере вызывается процедура МуМасго, расположенная в рабочей книге Primer.xls.

В чем заключается разница между процедурой VBA и макросом?

Здесь никакой разницы нет. Эти термины в настоящее время являются взаимозаменяемыми.

При написании кода в редакторе VBA не работает символ продолжения текущей строки (подчеркивание). В чем может быть проблема и как выйти из данной ситуации?

Скорее всего, проблема заключается в том, что в данном случае для продолжения строки используется только один символ – подчеркивание, а нужно использовать два символа: сначала – пробел, затем – подчеркивание.

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

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

Можно ли, используя штатные средства программы, изменить количество рабочих листов в новой книге?

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

Как ввести в ячейку несколько абзацев? При нажатии Enter не получается курсор переходит в следующую ячейку.

Для решения данной проблемы следует после ввода абзаца нажать сочетание клавиш Alt+Enter – таким образом можно ввести в ячейку текст, содержащий несколько абзацев.

Каким образом можно создавать разные рабочие книги с одинаковым форматированием?

В данном случае наиболее приемлемым является использование механизма шаблонов. Для этого нужно подготовить рабочую книгу, на основании которой будут создаваться другие книги, и задать в ней необходимые параметры форматирования и оформления документа, после чего сохранить данную книгу как шаблон (с помощью команды Сохранить как, указав в окне Сохранение документа в поле Тип файла значение Шаблон). Если при этом в качестве папки для сохранения выбрать каталог автозагрузки Excel – XLStart (обычно эта папка хранится по адресу C:Program FilesMicrosoft OfficeOffice 12XLStart), то созданный шаблон будет использоваться по умолчанию при запуске Excel. Если же сохранить шаблон в каком-нибудь другом месте, то для доступа к нему необходимо будет воспользоваться командой Открыть.

Каким образом можно перетащить ячейку на другой рабочий лист?

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

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

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

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

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

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

Можно ли в Excel выполнить одновременное форматирование нескольких рабочих листов?

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

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

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

Можно ли как-нибудь узнать, содержит ли текущая рабочая книга макровирус?

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

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

Данные на рабочем листе расположены в нескольких диапазонах, которые разделены между собой пустыми строками либо пустыми столбцами. Можно ли каким-нибудь способом быстро выделить один из этих диапазонов?

Оптимальный способ в данном случае – это установить курсор в любую ячейку внутри диапазона и нажать одновременно клавиши Ctrl и * («звездочка» на цифровой клавиатуре). В результате будет полностью выделен диапазон с активной ячейкой.

Каким образом можно быстро пересчитать формулы, использующие пользовательскую функцию?

Для решения данной задачи следует воспользоваться комбинацией клавиш Ctrl+Alt+F9.

Как известно, для вывода на печать какой-нибудь области рабочего листа нужно выделить ее, а затем в окне настройки параметров печати установить режим выделенный диапазон. При этом, если необходимо распечатать несколько разных выделенных областей, приходится повторять данную операцию каждый раз. Можно ли автоматизировать этот процесс?

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

Можно ли каким-либо способом запретить пользователям «прокручивать» рабочий лист?

Для решения данной проблемы можно посоветовать скрыть неиспользуемые строки и столбцы. Если этот вариант по каким-либо причинам неприемлем, то можно воспользоваться соответствующим оператором VBA. Ниже приведен пример, в котором оператор устанавливает область прокрутки на листе Лист1 таким образом, что пользователь не сможет работать с ячейками за пределами диапазона А1:Е20:

Worksheets(«Лист1»).ScrollArea = «A1:E20»

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

Worksheets(«Лист1»).ScrollArea = ""

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

Можно ли сделать так, чтобы документ распечатывался вместе с сеткой?

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

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

Для перехода в режим соответствующих настроек нужно в редакторе VBA выполнить команду Tools → Options (Сервис → Параметры) и в открывшемся окне перейти на вкладку Editor Format (Формат редактора). На данной вкладке приводится перечень всех возможных фрагментов программного кода. Для настройки оформления следует выбрать требуемую позицию списка и в соответствующих полях указать тип и размер шрифта, его цвет, цвет выделенного текста и др.

Да, такая возможность существует, и она также находится за пределами Excel. В окне Свойства: Экран (открываемом с помощью команды Пуск → Панель управления → Экран) нужно перейти на вкладку Оформление и нажать кнопку Дополнительно – откроется окно Дополнительное оформление. В данном окне в поле Элемент нужно выбрать значение Всплывающая подсказка, после чего в открывшихся полях указать требуемый шрифт и цвет. Необходимо помнить, что выполненные изменения отразятся и в других местах (в частности, соответствующим образом изменится шрифт и цвет системных всплывающих подсказок).

При добавлении в редакторе VBA нового модуля он всегда начинается со строки Option Explicit. Что означает данная строка и для чего она нужна?

Если строка Option Explicit находится в начале модуля, это означает, что необходимо объявлять все переменные, которые будут использоваться в пределах данного модуля. Если необходимо отключить автоматическое появление данной строки в новых модулях, то следует в редакторе VBA выполнить команду Tools → Options (Сервис → Параметры), в открывшемся окне перейти на вкладку Editor (Редактор) и снять флажок Require Variable Declaration (Явное описание переменных).

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

Да, используя средства VBA, можно решить данную проблему. Для этого следует назначить строковые данные свойству StatusBar объекта Application. Соответствующий оператор выглядит следующим образом:

Application.StatusBar = "Обработка файла " & FileNum

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

Application.StatusBar = False