Как ускорить и оптимизировать код VBA

Рано или поздно у пишущих на Visual Basic for Applications возникает проблема - код хоть и облегчает жизнь и делает все автоматически, но очень долго. В этой статье я решил собрать несколько простых рекомендаций, которые помогут ускорить работу кода VBA, при этом в некоторых случаях весьма внушительно - в десятки, а то и больше, раз. Основной упор в статье сделан на начинающих, поэтому в начале статьи приводятся самые простые методы оптимизации. Более "глубокие" решения по оптимизации кода приведены в конце статьи, т.к. для применения данных решений необходим достаточный опыт работы в VB и сходу такие методы оптимизации кому-то могут быть непонятны.

1. Если в коде есть много всяких Activate и Select , тем более в циклах - следует немедленно от них избавиться. Как это сделать я писал в статье: Select и Activate - зачем нужны и нужны ли?
2. Обязательно на время выполнения кода отключить:
   • автоматический пересчет формул. Чтобы формулы не пересчитывались при каждой манипуляции на листе во время выполнения кода - это может дико тормозить код, если формул много:

     Application.ScreenUpdating = False

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

     ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False

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

   • На всякий случай можно отключить отображение информации в строке статуса Excel (в каких случаях там вообще отображается информация и зачем можно узнать в статье: Отобразить процесс выполнения). Хоть это и не сильно поедает ресурсы - иногда может все же ускорить работу кода:

     Application.StatusBar = False

     Application.StatusBar = False

   Главное, что следует помнить - все эти свойства необходимо включить обратно после работы кода . Иначе могут быть проблемы с работой внутри Excel. Например, если забыть включить автопересчет формул - большинство формул будут пересчитывать исключительно принудительным методом - Shift +F9 . А если забыть отключить обновление экрана - то есть шанс заблокировать себе возможность работы на листах и книгах. Хотя по умолчанию свойство ScreenUpdating и должно возвращаться в True, если было отключено внутри процедуры - лучше не надеяться на это и привыкать возвращать все свойства на свои места принудительно. По сути все это сведется к нескольким строкам:

   "Возвращаем обновление экрана Application.ScreenUpdating = True "Возвращаем автопересчет формул Application.Calculation = xlCalculationAutomatic "Включаем отслеживание событий Application.EnableEvents = True

   Как такой код выглядит на практике. Предположим, надо записать в цикле в 10 000 строк значения:

   Sub TestOptimize() "отключаем обновление экрана Application.ScreenUpdating = False "Отключаем автопересчет формул Application.Calculation = xlCalculationManual "Отключаем отслеживание событий Application.EnableEvents = False "Отключаем разбиение на печатные страницы ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False "Непосредственно код заполнения ячеек Dim lr As Long For lr = 1 To 10000 Cells(lr, 1).Value = lr "для примера просто пронумеруем строки Next "Возвращаем обновление экрана Application.ScreenUpdating = True "Возвращаем автопересчет формул Application.Calculation = xlCalculationAutomatic "Включаем отслеживание событий Application.EnableEvents = True End Sub

   Разрывы печатных страниц можно не возвращать - они тормозят работу в любом случае.

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

   lLastRow = Cells(Rows.Count,1).End (xlUp).Row

   lLastRow = Cells(Rows.Count,1).End(xlUp).Row

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

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

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

  Sub TestOptimize_Array() "Непосредственно код заполнения ячеек Dim arr, lr As Long "запоминаем в массив одним махом все значения 10000 строк первого столбца arr = Cells(1, 1).Resize(10000).Value "если нужно заполнение для двух и более столбцов "arr = Cells(1, 1).Resize(10000, 2).Value "или "arr = Range(Cells(1, 1),Cells(10000, 2)).Value "или автоматически вычисляем последнюю ячейку и заносим в массив данные, начиная с ячейки А3 "llastr = Cells(Rows.Count, 1).End(xlUp).Row "последняя ячейка столбца А "arr = Range(Cells(3, 1),Cells(llastr, 2)).Value For lr = 1 To 10000 arr(lr,1) = lr "заполняем массив порядковыми номерами Next "Выгружаем обработанный массив обратно на лист в те же ячейки Cells(1, 1).Resize(10000).Value = arr End Sub

  Sub TestOptimize_Array() "Непосредственно код заполнения ячеек Dim arr, lr As Long "запоминаем в массив одним махом все значения 10000 строк первого столбца arr = Cells(1, 1).Resize(10000).Value "если нужно заполнение для двух и более столбцов "arr = Cells(1, 1).Resize(10000, 2).Value "или "arr = Range(Cells(1, 1),Cells(10000, 2)).Value "или автоматически вычисляем последнюю ячейку и заносим в массив данные, начиная с ячейки А3 "llastr = Cells(Rows.Count, 1).End(xlUp).Row "последняя ячейка столбца А "arr = Range(Cells(3, 1),Cells(llastr, 2)).Value For lr = 1 To 10000 arr(lr,1) = lr "заполняем массив порядковыми номерами Next "Выгружаем обработанный массив обратно на лист в те же ячейки Cells(1, 1).Resize(10000).Value = arr End Sub

  Но здесь следует учитывать и тот момент, что большие массивы могут просто вызвать переполнение памяти. Наиболее актуально это для 32-битных систем, где на VBA и Excel выделяется памяти меньше, чем в 64-битных системах

• Если используете быстрый ЕСЛИ - IIF , то замените его на IF ... Then ... Else
• Так же лучше вместо Switch() и Shoose() применить тот же IF ... Then ... Else
• В большинстве случаев проверять строку на "не пусто" лучше через Len() , чем прямое сравнение с пустотой: Len(s)=0 вместо s = "" . Связано с тем, что работа со строками значительно медленнее, чем с числовыми данными
• Не применять объединение строк без необходимости. Например, s = "АВ" , будет быстрее, чем: s ="А" & "В"
• Не применять сравнение текстовых величин напрямую. Лучше применить встроенную функцию StrComp:
  If s s1 Then будет медленнее, чем
  If StrComp(s, s1, vbBinaryCompare) = 0
  и тем более, если при сравнении необходимо не учитывать регистр:
  If LCase(s) LCase(s1) Then будет медленнее, чем
  If StrComp(s, s1, vbTextCompare) = 0
• Циклы For … Next в большинстве случаев работает быстрее, чем цикл Do ... Lоор
• Избегать присвоения переменным типа Variant . Хоть соблазн и велик - этот тип забирает много памяти и в дальнейшем замедляет работу кода. Так же для объектных переменных следует избегать по возможности безликого глобального типа Object и применять конкретный тип:

  Dim rRange as Range, wsSh as Worksheet

• Если работаете с массивами, то можно при объявлении указать это явно:

  Dim arr() as string, arr2() as long

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

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

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

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

Альтернативы Excel практически нет. Не удивительно, ведь Excel позволяет сделать практически всё. Он как пустой мольберт для художника. Ты садишься за него и начинаешь писать свою картину.

Мазок, ещё мазок… Проходит несколько часов и… Пару небольших табличек и график с стандартным оформлением. Грустно не так ли?

И у меня такое бывало. Работаешь, работаешь. Уже вымотался, а работа только начата. Что же с этим делать?

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

Интересно? Тогда поехали!

Первое что нужно сделать — выкинуть мышку!

Именно так вы всё верно прочитали. Чтобы работать быстрее в Excel нужно убрать мышку. ТОЛЬКО не спешите закрывать вкладку, сейчас всё станет понятно.

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

Всё дело в горячих клавишах которые позволяют просто молниеносно переключаться между страницами, выделять, добавлять или удалять стройки/столбцы. В общем знание буквально 5-7 комбинаций ускоряет работу на порядок. Многие навигационные операции с помощью мыши делаются в 3-5, а то и 10 раз дольше.

Приведу пример. Чтобы добавить несколько строк в Excel есть три варианта:

1. Вариант 1. Взять мышь, навести её на соответствующий столбец или несколько столбцов в зависимости того сколько столбцов нужно вставить. Нажать правой кнопкой, в выпадающем окошке выбрать вставить.
2. Вариант 2. С помощью кнопок на вкладке Главная в группе ячейки. Опять же выделяем, идем на вкладку Главная. Щелкаем по соответствующим кнопкам для получения результата.
3. Вариант 3. Для выделения столбца нажать Ctrl + ‘Пробел’ , для добавления столбца нажать Ctrl + ‘+’ .

Вы можете сказать, что экономия не велика. Одним способом я сделаю за 15-20 секунд, другим за 3-5. И что я буду делать с освободившимися 12-15 секундами? И вообще сколько нужно выучить горячих клавиш чтобы почувствовать разницу?

Согласен! Время, сэкономленное одной горячей клавишей не велико, но вся наша работа в Excel — это монотонное повторение одних и тех же операций по сотне раз в разных последовательностях. Значит если условная экономия на одной операции предположим 10 секунд. При повторении одной операции 20-30 раз в день экономия от знания одной комбинации горячих клавиш — 3-5 минут. Значит знание 10 горячих клавиш экономит 30-50 минут времени!!! А вот это уже круто!!!

Совет номер два вытекает из первого — учите горячие клавиши. Полезных горячих клавиш не так уж и много, поэтому выучив 10-20 сочетаний вы быстро почувствуете разницу в скорости работы.

«Хорошенький совет!» — возразите вы. — «И как их учить? Становиться на табуретку перед коллегами и рассказывать как стихи?» 🙂

Нет. Конечно же всё не так. попробуйте найти свой удобный для вас путь изучения горячих клавиш. Что для этого можно сделать?

Например, можно взять список горячих клавиш и выбрать 3-5 наиболее используемых функций. Попробовать отработать их, потренироваться как они работают. Привыкнуть нажимать их когда они нужны. Со временем пальцы сами лягут на клавиатуру так, чтобы быстро и удобно переключаться между листами. Выучили эти — переходите к следующим, а потом ещё и ещё.

Ну и конечно же вот вам список полезных быстрых и горячих клавиш:

Горячие клавиши уж точно помогут вам рабоатать быстрее в Excel. Трениуртесь в их использовании и очень быстро скорость вашей работы существенно возрастёт. Но это ещё не всё!

У меня в запасе есть ещё несколько советов. Готовы? Поехали!

Совет по Excel номер ТРИ. Функции и их комбинации, которые обязатлеьно освоить

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

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

Вместо формулы

=СУММ(Лист1!А1; Лист2!А1; Лист3!А1; Лист4!А1; Лист5!А1; Лист6!А1; Лист7!А1; … ЛистN!A1)

Формула будет выглядеть как

=СУММ(Лист1:ЛистN!А1)

Всё это к чему? Помимо богатого функционала Excel, который есть в его стандартных формулах, существует множество комбинаций, знание которых позволяет решать и не стандартные задачи. Так, у Excel совсем нет функции МИНЕСЛИ. Да, есть СУММЕСЛИ, СЧЁТЕСЛИ, а МИНЕСЛИ не сделали. Также МАКСЕСЛИ, МЕДИАНАЕСЛИ и т.п., но при этом всё это решается путем использования функций областей. Возможно вы видели, когда формула облачается в фигурные скобки.

Некоторые функции отлично работают только в связке. Это я сейчас говорю о ИНДЕКС и ПОИСКПОЗ. Казалось бы, бестолковые функции по отдельности, но в паре дают отличный функционал.

Что для этого нужно? Например, подписаться на нашу группу в Facebook и ждать выхода новых постов. 😉

Также посмотрите список формул, на которые я рекомендую вам обратить внимание:

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

Совет по Excel номер ЧЕТЫРЕ. Структура и форматирование в файлах

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

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

Как этого можно избежать? Дам несколько советов:

• Отформатируйте файл. Пусть у вас будет одинаковое количество знаков после запятой, одинаковый шрифт по всему документу и ограниченная цветовая палитра. Мне вот нравится использовать палитры одного цвета но разных тонов (голубой, синий и тёмно-синий). Смотрится очень стильно.
• Разделите исходные данные, расчеты и результаты. Не всегда это нужно, но когда данных становится очень много наличие сводной таблички просто спасает.
• Делайте одинаковые формулы по столбцу или строке. согласитесь не очень правильно когда один и тот же показатель в разные периоды считается по разному. При этом обычно этого не видно пока не заглянешь в формулу. а често можно ещё и забыть.
• Старайтесь избегать циклических ссылок и ссылок на внешние файлы. Обычно именно они дают разный результат на разных компьютерах. Был случай, когда погрешность при обновлении внешних ссылок была больше чем с 7 нулями. Ай!
• Упрощайте и не эконономьте место. Excel позволяет сделать сколь угодно много строк и столбцов. За свои 10 лет работы с программой я никогда не использовал лист полностью. Вряд ли у вас это получится, поэтому не городите сложные формулы. Лучше сделайте расчет в несколько действий.

Вот такие советы. Поверьте, они упрощают жизнь. Ошибок становится меньше, расчеты аккуратнее и понятнее. если у вас есть ещё какие-то советы — оставляйте их в комментариях.

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

Хотелось бы дать интенсив на 28 минут, после которого все будут знать Excel лучше чем знают его разработчики, но увы. Такого не бывает. Постоянная и усердная работа приводит к поистине впечатляющим результатам. Именно поэтому совсем скоро мы запускаем тренинг по Excel, который можно будет пройти на нашем сайте совершенно бесплатно (по крайней мере первым 1000 пользователям).

• Tutorial

Предисловие

Перед началом работы

Ускоряем работу макроса

Я наведу пример кода и в комментариях объясню что к чему, так будет яснее. К тому же, могут пригодиться некоторые строки кода, не относящееся прямо к процессу ускорения.

Пример

"Для явной инициализации переменных, включаем эту опцию "Это поможет избежать многих ошибок Option Explicit Sub Test() "К листам будем обращаться через переменные Dim Sheet1_WS, Sheet2_WS As Worksheet "Переменная для прохождения срок на листе (в массиве) Dim i As Long "Массив, в котором будут храниться наши данные Dim R_data As Variant "Переменные последней строки и колонки Dim FinalRow, FinalColumn As Long "Можно инициализировать лист не по названию, а по порядковому номеру "Set Sheet1_WS = Application.ThisWorkbook.Worksheet("Sheet1") Set Sheet1_WS = Application.ThisWorkbook.Sheets(1) Set Sheet2_WS = Application.ThisWorkbook.Sheets(2) "Поиск последней не пустой строки в первой колонке "Нужно, что бы данные не были отфильтрованы, иначе последняя строка будет последней строкой в фильтре "Также в последней строке, в первой колонке, не должно быть пустой ячейки. Конечно, если в этой строке вообще есть данные. Иначе последней строкой будет последняя не пустая ячейка. FinalRow = Sheet1_WS.Cells(Rows.Count, 1).End(xlUp).Row "=10 000 "Поиск последней не пустой колонки в первой строке FinalColumn = Sheet1_WS.Cells(1, Columns.Count).End(xlToLeft).Column "=50 "Присваиваем массиву диапазон данных на Листе 1 R_data = Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, FinalColumn)) For i = 2 To FinalRow "Выполняем нужные нам операции с данными. "Проверяем, что бы не было деления на ноль. "Предполагается, что в колонке 2 и 3 стоят числовые данные "Иначе потребуется обработка ошибок If R_data(i, 3) <> 0 Then R_data(i, FinalColumn) = R_data(i, 2) / R_data(i, 3) End If Next i "Копируем данные из массива обратно на Лист1 "Перед этим очищаем данные на листе (если есть форматирование или формулы, то лучше Sheet1_WS.Cells.ClearContents) Sheet1_WS.Cells.Delete Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, FinalColumn)) = R_data "Копируем данные на Лист2, копируем первые 10 колонок. Sheet2_WS.Range(Sheet2_WS.Cells(1, 1), Sheet2_WS.Cells(FinalRow, 10)) = R_data "Закрываем книгу и сохраняем её Workbooks(Application.ThisWorkbook.Name).Close SaveChanges:=True End Sub

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

Dim R_new() As Variant ............................................ " Явно указываем размер массива ReDim R_new(1 To FinalRow, 1 To 50) As Variant ........................................... Sheet1_WS.Range(Sheet1_WS.Cells(1, 1), Sheet1_WS.Cells(FinalRow, 50)) = R_new()

Заключение

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

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

Спасибо за внимание. Удачных разработок.

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

Способ первый

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

Проблема

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

Решение

В Windows XP данная проблема может быть решена следующим образом:

1. Запустите Проводник (Explorer)
2. Выберите меню Сервис (Tools)
3. Меню Свойства папки (Folder Options)
4. Вкладка Типы файлов (File Types)
5. Найдите элемент XLS
6. Кнопка Дополнительно (Advanced)
7. Элемент списка Открыть (Open)
8. Кнопка Изменить... (Edit...)

В появившемся диалоговом окне вы должны внести два изменения:

Приложение, исполняющее действие: (Application to perform action:)

В конце строки, после ключа /e, добавьте пробел и "%1" (вместе с кавычками)

Незапущенное приложение DDE: (DDE Application Not Running:)

Вставьте текст: (вместе со скобками)

Подробности

Мы обнаружили проблему после обновления до MS Office 2007. Отец заметил, что таблица Excel загружается быстро, если включить путь к EXCEL.EXE в командную строку. Загрузка документа через DDE, как делает Проводник, похоже, занимает намного больше времени, по крайней мере, если приложение еще не запущено. Изменения, описанные выше, передают путь к файлу сразу же, когда приложение запущено. Команда DDE, запускаемая позже, является всего лишь комментарием и, как пустая строка, в результате выполняет действие по умолчанию из поля двумя строками выше. Ключ /e , похоже, предотвращает открытие пустой таблицы, поэтому я сначала исключил его, поскольку, если передан файл, обычно пустая таблица и так не открывается. Однако, я получил сообщение от человека (тут была ссылка на страницу форума майкрософт, которая теперь не существует. - примечание переводчика ), использующего ключ /e , и поскольку мне он тоже не мешает, я предлагаю оставить его на месте. Если Excel уже запущен, следующий документ будет открыт через DDE, как и раньше. В результате только один процесс Excel будет работать.

Word

Снова и снова меня спрашивают, знаю ли я подобный трюк для Word. Даже без всяких трюков Word запускается достаточно быстро, но при этом добавление "%1" дало мне небольшое увеличение скорости запуска. Я не знаю точно, что такое "". Это похоже на некий "специальный комментарий", который оказывает какой-то эффект, не смотря на то, что является комментарием. Я решил скопировать этот комментарий в окошко "Незапущенное приложение DDE " и не заметил нежелательных эффектов. Я также оставил ключи /e и /dde в командной строке, просто потому что не обнаружил никакого вреда от них.

Возврат к прежнему состоянию

Если вы попробовали эти рекомендации, но они вам не помогли, возвращение настроек к прежнему виду может оказаться более сложным чем вы ожидаете. Похоже, окно, в котором вы вносите изменения, всегда добавляет %1 к команде, когда вы нажимаете ОК. Единственное решение, которое мне удалось найти, это использовать редактор реестра. Команда хранится в дефолтном ключе в ветке: HKEY_CLASSES_ROOT\Excel.Sheet.8\shell\Open\command

Если вы также хотите вернуть настройку DDE, зайдите в соседнюю ветку ddeexec и удалите ветку IfExec целиком.

Другие способы

Недоступен сетевой ресурс

• Проверьте, нет ли в системе сетевых принтеров, которые в данный момент недоступны. Офисные приложения при запуске могут пытаться их обнаружить и подолгу ждать отклика.
• В реестре в ветке HKEY_CLASSES_ROOT\Applications найдите программы, которые в параметрах имеют путь типа \\another_comp\folder\app.exe . Если этот сетевой ресурс недоступен, сделайте доступным, или удалите этот параметр. (Сначала попробуйте добавить к этой программе пустой строковой параметр NoOpenWith .)

Большой размер файла

Удалите невидимые объекты:

1. Сделайте резервную копию файла
2. Откройте его
3. Нажмите Ctrl+G
4. Кнопка Выделить...
5. Переключите на объекты
6. Нажмите ОК
7. Если табличка Не найдено ни одного объекта - переходите на следующий лист, и пробуйте еще
8. Если завис, не нервничайте, ждите
9. После того как объекты выделены, удаляйте их клавишей Delete на клавиатуре
10. Повторите это действие на всех листах книги
11. Сохраните документ и попробуйте как теперь открывается

Формат файла

Если файл в формате xls (формат офиса 2003), попробуйте пересохранить в новом формате. Меню Файл (Логотип офиса) -> Сохранить как -> Книга Excel (*.xlsx) . После этого файл станет немного меньше и будет быстрее открываться офисом 2007 и 2010.

Надстройки сторонних производителей

1. Меню Файл (Логотип офиса в левом верхнем углу)
2. Кнопка Параметры Excel
3. В левой части окна выберите Надстройки
4. В нижней части из ниспадающего списка выберите Надстройки COM
5. Нажмите Перейти...
6. Если в открывшемся окне есть надстройки, попробуйте отключать их по очереди и проверяйте изменения в скорости запуска

Павлов Николай

В этой статье мне хотелось бы представить вам самые эффективные приемы работы в Microsoft Excel, собранные мной за последние 10 лет работы над проектами и проведения тренингов по этой замечательной программе. Здесь нет описания суперсложных технологий, но есть приемы на каждый день - простые и эффективные, описанные без "воды" - только "сухой остаток". На освоение большинства из этих примеров у вас уйдет не более одной-двух минут, а вот сэкономить они вам помогут гораздо больше.

Быстрый переход к нужному листу

Случается ли вам работать с книгами Excel, состоящими из большого количества листов? Если их больше десятка, то каждый переход к очередному нужному листу сам по себе становится маленькой проблемой. Простое и элегантное решение такой задачи - щелкнуть в левом нижнем углу окна по кнопкам прокрутки ярлычков листов не левой, а правой кнопкой мыши - появится оглавление книги с полным списком всех листов и на нужный лист можно будет перейти в одно движение:

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

Копирование без повреждения форматирования

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

Начиная с Excel 2002, есть решение этой проблемы - простое и изящное. Сразу после копирования (протаскивания) формулы на весь столбец, нужно воспользоваться смарт-тегом - небольшим значком, который временно появляется в правом нижнем углу диапазона. Нажатие на него выведет список возможных вариантов копирования, где и можно выбрать Копировать только значения (Fill without formatting). В этом случае формулы копируются, а форматирование - нет:

Копирование только видимых ячеек

Если вы работаете в Microsoft Excel больше недели, то должны были уже сталкиваться с подобной проблемой: в некоторых случаях при копировании-вставке ячеек их вставляется больше, чем было, на первый взгляд, скопировано. Это может происходить, если копируемый диапазон включал в себя скрытые строки/столбцы, группировки, промежуточные итоги или фильтрацию. Рассмотрим в качестве примера один из таких случаев:

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

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

Выделите копируемый диапазон (в нашем примере - это A1:C29)

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

В этом окне выберите опцию Только видимые ячейки (Visible cells only) и нажмите ОК.

Полученное выделение теперь можно смело копировать и вставлять. В результате мы получим копию именно видимых ячеек и вставим вместо ненужных 29-ти только необходимые нам 5 строк.

Если есть подозрение, что подобную операцию вам придется проделывать часто, то имеет смысл добавить на панель инструментов Microsoft Excel кнопку для быстрого вызова такой функции. Это можно сделать через меню Сервис>Настройка (Tools> Customize), затем перейти на вкладку Команды (Commands), в категории Правка (Edit) найти кнопку Выделить видимые ячейки (Select visible cells) и перенести ее мышью на панель инструментов:

Превращение строк в столбцы и обратно

Простая операция, но если не знать как сделать ее правильно - можно потратить полдня на перетаскивание отдельных ячеек вручную:

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

Щелкаем правой кнопкой мыши по пустой ячейке и выбираем команду Специальная вставка (Paste Special)

В открывшемся окне ставим флаг Транспонировать (Transpose) и жмем ОК:

Быстрое добавление данных в диаграмму

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

Другой путь - простой, быстрый и красивый - выделить ячейки с новыми данными, скопировать их (CTRL+C) и вставить (CTRL+V) прямо в диаграмму. Excel 2003, в отличие от более поздних версий, поддерживает даже возможность перетаскивания выделенного диапазона ячеек с данными и забрасывания его прямо в диаграмму с помощью мыши!

Если хочется контролировать все нюансы и тонкости, то можно использовать не обычную, а специальную вставку, выбрав в меню Правка>Специальная вставка (Edit> Paste Special). В этом случае Microsoft Excel отобразит диалоговое окно, позволяющее настроить куда и как именно будут добавлены новые данные:

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

Заполнение пустых ячеек

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

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

Есть способ решить эту задачу быстро и красиво при помощи одной формулы:

Выделите все ячейки в столбце с пустотами (т.е. диапазон A1:A12 в нашем случае)

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

Установите переключатель в положение Пустые (Blank) и нажмите ОК. Теперь в выделении должны остаться только пустые ячейки:

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

Чтобы ввести созданную формулу сразу во все выделенные пустые ячейки, нажмите не клавишу ENTER, а сочетание CTRL+ ENTER. Формула заполнит все пустые ячейки:

Теперь осталось только заменить формулы на значения для фиксации результатов. Выделите диапазон A1:A12, скопируйте его и вставьте в ячейки их значения, используя специальную вставку.

Выпадающий список в ячейке

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

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

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

Чтобы создать выпадающий список в ячейке:

Выделите ячейки, в которых вы хотите создать выпадающий список.

Если у вас Excel 2003 или старше, то выберите в меню Данные>Проверка (Data>Validation). Если у вас Excel 2007/2010, то перейдите на вкладку Данные (Data) и нажмите кнопку Проверка данных (Data validation).

В открывшемся окне выберите вариант Список (List) из раскрывающегося списка.

В поле Источник (Source) надо указать значения, которые должны быть в списке. Тут возможны варианты:

Вписать в это поле текстовые варианты через точку с запятой

Если диапазон ячеек с исходными значениями находится на текущем листе - достаточно его просто выделить мышью.

Если он находится на другом листе этой книги, то ему придется заранее дать имя (выделить ячейки, нажать CTRL+F3, ввести имя диапазона без пробелов), а затем прописать это имя в поле