Полезные инструменты и приемы работы в Excel 2010

Полезные инструменты и приемы работы в Excel 2010

• Сценарии.
• Выявление зависимостей.
• Макросы.
• Фильтр.
• Расчет итогов.
• Проверка корректности вводимых значений.
• Таблица подстановки.

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

Сценарии — одно из интереснейших средств автоматизации Excel, позволяющее оценивать варианты вычислений при том или ином наборе значений в ячейках. Каждый из заданных наборов параметров называется сценарием. В качестве параметров сценария можно вводить

Сценарии

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

Упражнение

Для изучения сценариев мы, как и раньше, воспользуемся примером.

1. Создайте новый лист Excel.
2. Составьте на листе таблицу, показанную на рис. 10.1.

Рис. 10.1. Исходные данные для сценария

1. При составлении таблицы в ячейку F2 должна быть введена следующая формула, суммирующая значения в ячейках C2, D2 и E2:

=СУММ(C2:E2)

1. Заполните этой формулой ячейки F2…F4. В ячейку G2 нужно ввести формулу, вычисляющую сумму выдачи с учетом налогов:

=B2-B2*F2

1. Заполните этой формулой ячейки G2…G4. Результат должен соответствовать рис. 10.1.
2. Теперь можно приступить к созданию нескольких сценариев. Выберите команду ДанныеРабота с даннымиАнализ «Чтоесли»Диспетчер сценариев. На экран будет выведено диалоговое окно, показанное на рис. 10.2. Кнопки в этом окне имеют достаточно красноречивые названия и служат для добавления, удаления, изменения и объединения сценариев. Кнопка Вывести позволяет вывести результат применения сценария, выделенного в списке, в текущую таблицу.
3. Щелкните на кнопке Добавить. На экран будет выведено окно добавления сценария (рис. 10.3).
4. В поле Название сценария введите имя сценария, например Сц1. В качестве изменяемых ячеек задайте диапазон $C$2:$E$4. Поле Примечание можете оставить пустым, хотя в это поле неплохо поместить описание сценария: цель его добавления, назначение и пр. Щелкните на кнопке OK. Откроется окно заполнения ячеек сценария (рис. 10.4).

Рис. 10.2. Окно диспетчера сценариев

Рис. 10.3. Окно добавления сценария

Рис. 10.4. Окно заполнения ячеек сценария Выявление зависимостей

1. Введите для каждой ячейки значение 0,01 и щелкните на кнопке OK. Вы вернетесь в окно диспетчера сценариев.
2. Щелкните на кнопке Добавить и добавьте еще два сценария, Сц2 и Сц3, для того же диапазона ячеек, но со значениями всех ячеек, соответственно, 0,2 (для второго сценария) и 0,3 (для третьего сценария).
3. Теперь, выбрав в списке нужный сценарий, вы можете щелкнуть сначала на кнопке Вывести, затем — на кнопке Закрыть и работать с таблицей дальше. Но куда более интересные возможности открывает для вас кнопка Отчет. Пользуясь этой кнопкой, вы можете просмотреть все сценарии сразу, так сказать «в одном флаконе», то есть в одной сводной таблице. Для этого щелкните на кнопке Отчет, выберите тип отчета — Сводная таблица, задайте диапазон результирующих ячеек (в нашем случае G2:G4) и щелкните на кнопке OK. В книге будет создан новый лист Сводная таблица по сценарию (рис. 10.5).

Рис. 10.5.

Сводная таблица по сценарию

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

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

Упражнение

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

1. Организуйте в ячейке G5 суммирование ячеек G2…G4.
2. Щелкните в ячейке F2.
3. Перейдите к группе инструментов ФормулыЗависимости формул. Назначение ее инструментов следующее:

показывает формулы, по которым эти значения вычисляются;

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

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

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

1. Щелкните на кнопке Влияющие ячейки. В таблице будут указаны стрелками синего цвета ячейки, из значений которых была получена сумма в текущей ячейке F2 (рис. 10.8).

Выявление зависимостей

Рис. 10.6. Окно вычисления формулы

Рис. 10.7.

Окно контрольного значения

Рис. 10.8.

Отображение влияющих ячеек

1. Щелкните на кнопке Зависимые ячейки. В таблице появится стрелка от ячейки F2 к ячейке G2. Щелкните на этой кнопке еще раз. От ячейки G2 стрелка будет проведена к ячейке G5. Таким образом отображаются несколько ступеней зависимостей (рис. 10.9).

Рис. 10.9. Отображение нескольких ступеней зависимостей

Макросом, или макрокомандой, называют последовательность команд на языке VBA, сохраненных под каким-либо именем. VBA (Visual Basic for Application) — это встроенный язык программирования всех приложений Microsoft ffice. По имени макроса или по присвоенному ему сочетанию клавиш эта последовательность команд может быть вызвана и выполнена.

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

Записать макрос легко, достаточно выбрать команду ВидМакросыЗапись макроса и затем произвести какие-либо действия на листах книги Excel. Для окончания записи достаточно щелкнуть на прямоугольной кнопке Остановить запись в строке состояния, находящейся в нижней левой части окна (рис. 10.10).

Рис. 10.10. Кнопка останова записи макроса

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

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

Упражнение

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

Макросы

показывающее, как записать макрос, назначить его кнопке и выполнить.

1. Создайте новый лист Excel.
2. Выберите команду ВидМакросыЗапись макроса. В выведенном на экран диалоговом окне посмотрите на имя макроса; это должно быть имя Макрос1. Ничего не меняя в элементах управления диалогового окна, щелкните на кнопке OK. В нижней части экрана появится кнопка останова записи.
3. Выделите мышью диапазон ячеек G6:K18. Задайте для него красный цвет заливки, затем щелкните мышью вне области этого диапазона.
4. Щелкните на кнопке Остановить запись. Вы записали макрос, закрашивающий диапазон красным цветом.
5. Выберите команду ВидМакросыЗапись макроса. В выведенном на экран диалоговом окне посмотрите на имя макроса; это должно быть имя Макрос2. Ничего не меняя в элементах управления диалогового окна, щелкните на кнопке OK.
6. Выделите мышью диапазон ячеек G6:K18. Задайте для него желтый цвет заливки, затем щелкните мышью вне области этого диапазона.
7. Щелкните на кнопке Остановить запись. Вы записали макрос, закрашивающий диапазон желтым цветом.
8. Для того чтобы увидеть, что представляют собой записанные макросы, нужно выбрать команду ВидМакросыМакросы. Откроется диалоговое окно Макрос. В списке макросов этого окна выберите один из макросов и щелкните на кнопке Изменить. На экран будет выведено окно среды Visual Basic с текстом записанного макроса, который должен выглядеть примерно так:

Sub Макрос1()

Range(“G6:K18”).Select

With Selection.Interior

.Pattern = xlSolid

.PatternColorIndex = xlAutomatic

.Color = 65535

.TintAndShade = 0

.PatternTintAndShade = 0

End With

End Sub

1. Чтобы воспользоваться записанным макросом, мы можем назначить его сочетанию клавиш или элементу управления, например, кнопке. Для того чтобы связать макрос с кнопкой, необходимо иметь доступ к вкладке Разработчик. Щелкните сначала на кнопке Office, затем — на кнопке Параметры Excel. В группе Основные установите флажок Показывать вкладку «Разработчик» на ленте. После этого выберите команду РазработчикВставитьЭлементы управления формыКнопка и указателем мыши в виде крестика задайте положение кнопки. Как только кнопка будет нарисована, откроется окно со списком макросов. Выберите в этом окне макрос Макрос1 и щелкните на кнопке OK. Проделайте то же самое второй раз, назначив второй кнопке макрос Макрос2.
2. Щелкните на первой кнопке правой кнопкой мыши, в контекстном меню выберите команду Изменить текст и отредактируйте текст на кнопке, удалив содержимое и вместо него введя слово Красный. То же самое проделайте со второй кнопкой, только дайте ей название Желтый. То, что у вас к этому времени должно получиться, показано на рис. 10.11.

Рис. 10.11. Кнопки на листе Excel

В результате щелчок на кнопке Красный закрасит диапазон красным цветом, а щелчок на кнопке Желтый — желтым.

Фильтр

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

• — сортировка строк по возрастающим значениям текущего столбца;
• — сортировка строк по убывающим значениям текущего столбца;
• — задание параметров сортировки;
• — включение-выключение параметров;
• — очистка параметров сортировки и фильтрации;
• — повторное применение заданных параметров сортировки и фильтрации;

• — задание дополнительных параметров сортировки и фильтрации.

Упражнение

В этом упражнении мы рассмотрим, как работают инструменты сортировки и фильтрации.

1. Создайте новый лист Excel. Для того чтобы посмотреть, как работает фильтр, на лист нужно поместить данные. Получите их из уже известного вам источника: выберите команду ДанныеВнешние данныеСоздать запрос и создайте запрос к базе данных MS Acess Борей.mdb (Northwind.mdb). Откройте для запроса таблицу Сведения о заказах, выберите в запрос все поля, не устанавливайте правила отбора и сортировки, а просто введите данные в Excel.
2. По умолчанию, когда вы импортируете данные из Access или из MS SQL Server, они вставляются в виде таблицы с кнопками сортировки и фильтрации в столбцах (рис. 10.12).

Рис. 10.12. Столбцы с кнопками раскрывающихся списков фильтров

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

Рис. 10.13. Отфильтрованные записи

1. Выбор пункта (Все) позволяет снять фильтр по данному столбцу и вновь вывести все значения. Пункт Числовые фильтры (для числовых полей) или Текстовые фильтры (для текстовых полей) позволяет произвести подробную настройку по условию. Пункт Первые 10 служит для вывода на экран первых десяти записей в таблице, отфильтрованных по первым десяти значениям соответствующего поля. Так же работают и остальные пункты задания условий. Наиболее функционально интересен пункт Настраиваемый фильтр. После выбора этого пункта на экран выводится окно задания пользовательского фильтра (рис. 10.14). Для текущего поля можно установить два условия, связанные между собой операциями И либо ИЛИ. Кроме того, при задании условия вы можете применять знаки подстановки * и ?.
2. Установите для поля КодЗаказа условия больше 10 341 или меньше 10 415. Затем для поля Скидка установите условие не равно 0. Таким образом можно осуществлять наложение условий по несколь-

Фильтр

ким столбцам. Выберите для столбцов КодЗаказа и Скидка пункт (Все). Таблица вернется в исходное состояние.

Рис. 10.14. Настройка пользовательского фильтра

1. Применение команды Дополнительно также рассмотрим на примере. Для того чтобы применить эту команду, нужно сначала создать диапазон условий. Для этого скопируйте заголовки существующих данных в новое место (диапазон A1…E1 скопируйте в диапазон G1…K1) и задайте условие, для начала — одно. Введите в ячейку G2 (под заголовком КодЗаказа) выражение <10251.

ВНИМАНИе

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

1. Выберите команду ДанныеСортировка и фильтрДополнительно. На экран будет выведено окно настройки расширенного фильтра (рис. 10.15).
2. В группе Обработка оставьте установленным переключатель Фильтровать список на месте, в качестве исходного диапазона задайте столбцы от A до E (это, как обычно, можно сделать, вводя значения вручную или воспользовавшись кнопкой), в качестве диапазона условий задайте диапазон G1…K2. Щелкните на кнопке OK. Имеющийся список будет отфильтрован. Для того чтобы снять фильтр и снова увидеть все записи, щелкните на кнопке Фильтр.
3. Теперь займемся копированием данных, отобранных при помощи фильтра, в новое место. Выберите команду ДанныеСортировка и фильтрДополнительное и в группе Обработка открывшегося окна настройки расширенного фильтра установите переключатель Скопировать результат в другое место. При этом становится доступным поле Поместить результат в диапазон. Введите в это поле значение $G$4:$K$4, в поле диапазона условий введите значение G1:K2 и щелкните на кнопке OK. Отфильтрованные данные будут помещены в указанный вами диапазон (рис. 10.16). Обратите внимание, что если в качестве диапазона данных указать меньшее количество столбцов, то «лишние» столбцы просто будут отброшены не войдут в конечный результат.

Рис. 10.15. Настройка расширенного фильтра

Рис. 10.16. Копирование отфильтрованных данных

1. Вы можете ввести в таблице условий несколько условий. В этом случае условия, введенные в одном столбце, будут объединены между собой оператором И, а условия, находящиеся в разных столбцах, — оператором ИЛИ.

Расчет итогов

Команда ДанныеСтруктураПромежуточные итоги является средством быстрого просмотра листа с данными с одновременным подведением итогов по различным группам, которые автоматически объединяются на основе сходства признаков.

Упражнение

Для выполнения этого упражнения воспользуемся теми же данными, что и в предыдущем упражнении.

1. Команда Промежуточные итоги не может быть применена к таблице, отображающей данные, связанные с источником данных. Щелкните где-либо внутри таблицы с данными и выполните команду КонструкторСервисПреобразовать в диапазон. Таблица будет преобразована в диапазон данных.
2. Выберите команду ДанныеСтруктураПромежуточные итоги. На экран будет выведено окно настройки (рис. 10.17).

Рис. 10.17. Окно настройки итоговых значений

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

в соответствии с которой должны подсчитываться итоги;

• список Добавить итоги по предназначен для выбора полей, по которым должен производиться подсчет итоговых значений;
• установка флажка Заменить текущие итоги приводит к автоматической замене текущих итогов новыми значениями;
• флажок Конец страницы между группами позволяет при выводе на

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

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

1. Выберите в раскрывающемся списке При каждом изменении в пункт КодЗаказа, в списке Операция — пункт Сумма, в списке Добавить итоги по установите флажки для полей ОтпускнаяЦена и Количество. В нижней части окна установите флажки Заменить текущие итоги и Итоги под данными, затем щелкните на кнопке OK.
2. Две-три секунды будет продолжаться процесс подсчета итогов, после чего на экране будет сформирована трехуровневая структура документа (рис. 10.18). Зона управления структурой документа находится слева. Щелчок на одной из кнопок с цифрами позволяет открыть соответствующий уровень структуры. Первый уровень — это уровень общих итогов, второй уровень — уровень промежуточных итогов, третий уровень — отображение всех строк данных.

Рис. 10.18. Трехуровневая структура документа

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

Проверка корректности вводимых значений

групп. В случае если вы выделите не все столбцы, а только часть столбцов и строк, например диапазон A1…C30, итоги будут подведены только для этого диапазона.

Рис. 10.19. Раскрытие третьего уровня структуры

Когда итоги в таблице станут ненужными, выделите диапазон, в котором они были подсчитаны, выберите команду ДанныеСтруктураПромежуточные итоги и в открывшемся окне настройки итоговых значений щелкните на кнопке Убрать все. Из таблицы будут убраны как вычисленные итоги, так и элементы структуры документа.

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

Упражнение

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

1. Создайте новый лист Excel.
2. Выделите диапазон ячеек A1…C5 и выберите команду ДанныеПроверка. На экран будет выведено диалоговое окно с тремя вкладками. Вкладка Параметры позволяет задать параметры значения, вводимого в ячейку. Задайте такие же параметры, как на рис. 10.20.

Рис. 10.20. Настройка условия проверки

1. Уже после ввода этого ограничения Excel не даст пользователю (в том числе и вам) ввести неверное значение. Но нужно позаботится еще о сообщениях, информирующих и содержащих грозные предупреждения. Для этого перейдите на вкладку Сообщение для ввода. Заполните поля на этой вкладке так, как показано на рис. 10.21.

Рис. 10.21. Создание всплывающей подсказки

Проверка корректности вводимых значений

1. Несколько иначе заполните поля на вкладке Сообщение об ошибке (рис. 10.22).

Рис. 10.22. Создание предупреждения

1. Дело сделано, можно щелкать на кнопке OK. Теперь проверьте, что у вас получилось. При выборе любой ячейки из диапазона A1…C5 на экране должна появляться подсказка (рис. 10.23).

Рис. 10.23. Всплывающая подсказка

1. Попытайтесь проигнорировать подсказку и введите в ячейку число 6. На экране тут же появится диалоговое окно с предупреждением (рис. 10.24).

Excel не даст ввести неверное значение только в том случае, если в качестве предупреждающего сообщения в списке Вид выбран пункт Останов (см. рис. 10.22). Если вы выбрали пункт Предупреждение или Сообщение, то ввод неверного значения в ячейку останется на совести пользователя — окончательное решение будет принимать он.

Рис. 10.24. Предупреждение пользователя о неправильных действиях

Нередко возникает необходимость подставить в формулу заданный ряд значений одной или двух переменных, входящих в ее состав, и посмотреть на результат. Для того чтобы удовлетворить подобную потребность, существует команда ДанныеРабота с даннымиАнализ «что если»Таблица данных.

Упражнение

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

1. Первое, что вам нужно сделать, — это создать формулу. Выделите любую ячейку, пусть это будет, например, ячейка F10, и затем в строке формул введите выражение =E9*F9. Ячейки, на которые ссылается формула в таблице подстановки, могут быть любыми, важно одно — чтобы они не попали в зону расположения будущей таблицы. Поэтому желательно, чтобы эти ячейки находились выше и левее той ячейки, в которой помещается формула.
2. А вот значения, которые будут подставляться в формулу, имеют свое строгое местоположение: первый ряд значений должен начинаться прямо под ячейкой, содержащей формулу подстановки, и распространяться вниз. Введите в диапазон F11…F19 целые числа от 1 до 9. Второй диапазон должен лежать справа от ячейки, содержащей формулу. Введите в диапазон G10…O10 еще один ряд целых чисел от 1 до 9. В результате у вас должна получиться пока еще пустая таблица, показанная на рис. 10.25.
3. Выделите диапазон F10…O19 и выберите команду ДанныеТаблица подстановки. На экран будет выведено диалоговое окно, в котором надо будет указать, в какие ячейки следует подставлять значения из столбца F11…F19 и из строки G10…O10. В качестве первой ячейки укажите E9, а в качестве второй — F9 (обе эти ячейки входят в фор-

Таблица подстановки

мулу как сомножители). Щелкните на кнопке OK, и пространство между столбцом и строкой заполнится значениями. Как и было обещано, это — значения таблицы умножения (рис. 10.26).

Рис. 10.25. Будущая таблица подстановки

Рис. 10.26. Заполненная таблица подстановки