Microsoft Excel, одна из основных программ комплекса Microsoft Office, используется всеми сотрудниками офисов для любых применений — от подготовки простых списков до сложных вычислений, но большинству из нас по-прежнему есть чему поучиться в работе с этой широко распространенной электронной таблицей. В этом коротком списке советов (для версии 2002 и более поздних) содержатся рекомендации почти по всем аспектам Excel, в том числе базовым параметрам, сводным таблицам, передовым функциям вырезания и вставки, макрокомандам и быстрым комбинациям клавиш. Желаем успеха!

СОВЕТ 1.

Изменение применяемой функции

Почти все знают, что если выбрать последовательность ячеек, то в панели состояния отображается сумма значений этих ячеек. Но не всем известно, что можно щелкнуть правой кнопкой мыши на Sum в панели состояния и изменить применяемую функцию на Average, Count, Count Nums, Min или Max.

СОВЕТ 2.

Изменение стандартного числа рабочих листов

Многие пользователи досадуют на то, что каждая рабочая книга Excel изначально состоит из трех листов, даже если в них нет необходимости. Чтобы изменить число рабочих листов, из которых по умолчанию состоит каждая книга, следует выбрать пункт Options в меню Tools, открыть вкладку General и изменить параметр Sheets in a new workbook.

СОВЕТ 3.

Форматирование отчетов о сводных таблицах для SQL Server Analysis Services

Чтобы отформатировать отчеты PivotTable для Microsoft SQL Server Analysis Services, следует загрузить модуль расширения Excel Add-in for Analysis Services (http://go.microsoft.com/?linkid=4351028). Модуль совместим с Excel 2002 и более поздними версиями. Некоторые команды модуля расширения нельзя назвать интуитивно понятными, поэтому сначала имеет смысл посмотреть в сети TechNet Web-материал «Creating Free-form and Structured Reports with Excel Add-in for Analysis Services» (http://www.microsoft.com/ uk/windowsserversystem/bi/webcast/ exceladdinwebcast.wvx).

СОВЕТ 4.

Сохранение формата PivotTable

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

СОВЕТ 5.

Использование функций VLookup и HLookup для сокращения объема ручной работы

Функции VLookup и HLookup обеспечивают вертикальный (VLookup) или горизонтальный (HLookup) поиск значений в массиве. VLookup отыскивает в указанном столбце значения, точно или приблизительно совпадающие с заданным критерием, и возвращает соответствующие значения ячеек (либо формулу, в которой они используются), находящиеся в одной строке с найденной ячейкой. HLookup работает также, но обрабатывает строки, а не столбцы. Примеры использования этих функций приведены в конце тематического материала VLookup на Web-узле Office Online по адресу http://office.microsoft.com/ enus/assistance/HP052093351033.aspx.

СОВЕТ 6.

Использование именованного диапазона в качестве входного для сводной таблицы

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

СОВЕТ 7. Уменьшение размера файла рабочей книги

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

  • Если одни и те же данные используются в нескольких сводных таблицах, то мастер PivotTable and PivotChart Wizard всегда указывает в качестве источника данных отчет Another PivotTable and PivotChart. Потребление памяти программой Excel значительно снизится, если рабочая книга основана на данных из существующего отчета.
  • Можно отменить кэширование данных в рабочей книге (при отсутствии обращений к OLAP-кубу Analysis Services), сняв флажок Save data with table layout на экране PivotTable Options мастера PivotTable and PivotChart Wizard. Недостаток этого подхода заключается в необходимости обновлять PivotTable каждый раз при открывании рабочей книги.

СОВЕТ 8.

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

Для выполнения разнообразных операций вырезания и вставки очень полезны специальные функции. Собираясь вставить информацию в ячейку, следует открыть меню Edit и выбрать пункт Paste Special. Проведите эксперименты с различными вариантами вставки содержимого буфера обмена: в качестве простых величин, с сохранением формул, без или с форматированием и др. Функция Transpose поможет сэкономить много времени, когда строку требуется преобразовать в столбец или наоборот. Для удобства наиболее часто используемые функции специальной вставки представлены в отдельном контекстном меню Paste Options, которое появляется в нижнем правом углу любого нового фрагмента.

СОВЕТ 9.

Используйте макрокоманды

Пользователи Excel делятся на две категории: те, кто использует макрокоманды, и те, кто этого не делает. Макрокоманды незаменимы при выполнении большого числа повторяющихся операций. Подменю Macro находится в меню Tools; для начала полезно познакомиться с тематическим материалом в электронной документации Office «Create a macro» по адресу http://office.microsoft.com/en-us/assistance/HP052047111033.aspx.

Для создания макрокоманды достаточно записать последовательность операций, но после того, как пользователи убедятся в существенной экономии времени и точности выполнения автоматизированных процедур, они захотят освоить основы Visual Basic for Applications (VBA), чтобы разобраться во внутренних механизмах макрокоманд и расширить их возможности. Следует помнить, что макрокоманды могут представлять угрозу для безопасности; по умолчанию макрокоманды отключены, и необходимо изменить режим Macro Security Level в подменю Macro или активизировать макрокоманды для отдельных рабочих книг каждый раз при открывании рабочей книги.

СОВЕТ 10.

Использование комбинаций клавиш

Действительно эффективная работа с Excel невозможна без «быстрых клавиш». Для первого знакомства с ними полезно прочитать документацию Microsof http://office.microsoft.com/enus/assistance/ HP052037811033.aspx), выбрать пять регулярно выполняемых действий и запомнить соответствующие комбинации клавиш. После того как использование этих комбинаций будет доведено до автоматизма, следует изучить еще пять комбинаций. Стоит освоить операции быстрой поэкранной прокрутки рабочих листов (Page Down, Page Up), удаления ячеек (Ctrl+Hyphen), а не только их содержимого, и даже такие редко документируемые команды, как перемещение между рабочими листами в книге (Crtl+Page Down и Ctrl+Page Up).

СОВЕТ 11.

Закрытие нескольких рабочих книг

Если одновременно открыто несколько рабочих книг Excel, то иногда бывает удобно закрыть все книги сразу. Для этого нужно нажать и удерживать клавишу Shift перед тем, как открыть меню File. При этом пункт Close превращается в Close All.

Дуглас Макдауэлл (douglas@SolidQualityLearning.com). Директор по бизнес-аналитике в компании Solid Quality Learning, преподаватель, архитектор решений, руководитель проектов и основатель Atlanta.mdf, пользовательской группы SQL Server в Атланте. Имеет сертификаты MCSE, MCDBA и MCT, удостоен ежегодно вручаемой награды Worldwide Business Intelligence Solution of the Year компании Microsoft.

Поделитесь материалом с коллегами и друзьями