Архив

Публикации с меткой ‘Как сделать?’

Как проверить открыта ли книга?

 

Собственно суть темы отражена в названии. Как при выполнении кода из VBA узнать перед обращением к книге открыта она или нет? Ведь если книга закрыта, то обращение к ней вызовет ошибку, а если открывать без проверки — то это может повлечь за собой утерю данных, если предварительно эта книга не была сохранена. Ни один ни второй вариант, естественно, не устраивают. Я покажу два способа проверки через функции. Если функция вернет True — книга открыта, если False — закрыта. Для проверки функций используем проверочную процедуру Check_Open_Book:

Sub Check_Open_Book()
    If IsBookOpen("Книга1.xls") Then
        MsgBox "Книга открыта", vbInformation, "Сообщение"
    Else
        MsgBox "Книга закрыта", vbInformation, "Сообщение"
    End If
End Sub
Sub Check_Open_Book()
    If IsBookOpen("Книга1.xls") Then
        MsgBox "Книга открыта", vbInformation, "Сообщение"
    Else
        MsgBox "Книга закрыта", vbInformation, "Сообщение"
    End If
End Sub

Данная процедура вызывает функцию IsBookOpen, передавая ей в качестве параметра имя книги, «открытость» которой мы хотим проверить. Я приведу два варианта самой функции IsBookOpen.
Вариант 1:

Function IsBookOpen(wbName As String) As Boolean
    Dim wbBook As Workbook
    For Each wbBook In Workbooks
        If wbBook.Name <> ThisWorkbook.Name Then
            If Windows(wbBook.Name).Visible Then
                If wbBook.Name = wbName Then IsBookOpen = True: Exit For
            End If
        End If
    Next wbBook
End Function
Function IsBookOpen(wbName As String) As Boolean
    Dim wbBook As Workbook
    For Each wbBook In Workbooks
        If wbBook.Name <> ThisWorkbook.Name Then
            If Windows(wbBook.Name).Visible Then
                If wbBook.Name = wbName Then IsBookOpen = True: Exit For
            End If
        End If
    Next wbBook
End Function

Функция просматривает все открытые книги и если находит среди них книгу с указанным именем, то функция возвращает True. Есть небольшая особенность — функция исключает скрытые книги(это либо надстройки, либо PERSONAL.XLS). Так же из просмотра исключена та книга, в которой расположен сам код. Если Вам нужно проверить наличие книги независимо от её видимости, то необходимо просто заменить блок

    If Windows(wbBook.Name).Visible Then
        If wbBook.Name = wbName Then IsBookOpen = True: Exit For
    End If
    If Windows(wbBook.Name).Visible Then
        If wbBook.Name = wbName Then IsBookOpen = True: Exit For
    End If

на одну строку(просто убрать лишнее условие проверки)

    If wbBook.Name = wbName Then IsBookOpen = True: Exit For
    If wbBook.Name = wbName Then IsBookOpen = True: Exit For

Либо можно воспользоваться Вариантом 2:

Function IsBookOpen(wbName As String) As Boolean
    Dim wbBook As Workbook: On Error Resume Next
    Set wbBook = Workbooks(wbName)
    IsBookOpen = Not wbBook Is Nothing
End Function
Function IsBookOpen(wbName As String) As Boolean
    Dim wbBook As Workbook: On Error Resume Next
    Set wbBook = Workbooks(wbName)
    IsBookOpen = Not wbBook Is Nothing
End Function

Данный способ обращается к любой открытой книге — будь то PERSONAL.XLS или надстройка.


Вариант 3:
По просьбам читателей решил добавить код, который проверяет открыта ли книга независимо от её месторасположения и используемого приложения Excel. Книга может быть открыта другим пользователем (если книга на сервере), в другом экземпляре Excel или в этом же экземпляре Excel.

Function IsBookOpen(wbFullName As String) As Boolean
    Dim iFF As Integer
    iFF = FreeFile
    On Error Resume Next
    Open wbFullName For Random Access Read Write Lock Read Write As #iFF
    Close #iFF
    IsBookOpen = Err
End Function
Function IsBookOpen(wbFullName As String) As Boolean
    Dim iFF As Integer
    iFF = FreeFile
    On Error Resume Next
    Open wbFullName For Random Access Read Write Lock Read Write As #iFF
    Close #iFF
    IsBookOpen = Err
End Function

Фукнция несколько отличается от приведенных выше — передается в неё не только имя книги, а полный путь к книге, включая имя и расширение:

Sub Test()
    MsgBox "Файл 'Книга1'" & IIf(IsBookOpen("C:\Книга1.xls"), " уже открыт", " не занят")
End Sub
Sub Test()
    MsgBox "Файл 'Книга1'" & IIf(IsBookOpen("C:\Книга1.xls"), " уже открыт", " не занят")
End Sub

Также см.:
Как узнать существует ли лист в книге?
Как узнать существует ли модуль в книге

Categories: Tags: ,

Как создать кнопку для вызова макроса на листе?

 

После создания макроса часто возникает вопрос: а как его теперь запустить? Можно, конечно, нажать сочетание клавишь Alt+F8 и выбрать нужный макрос, но…Во-первых это не всегда удобно, особенно если макросов больше 5-ти. Во-вторых: если файлом с макросом будут пользоваться другие люди, не умеющие всего этого делать, придется объяснять им все это, что тоже не очень здорово — многие просто будут вспоминать Вас не очень хорошими словами :-)

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

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

рис.1

Кнопка элемент ActiveX — данная кнопка считается устаревшей и не рекомендуется к использованию. Однако многие все же предпочитают использовать имеено её(либо не знают, что можно по-другому). Так же назначить для этой кнопки макрос кому-то покажется сложнее, чем для остальных, рассматриваемых в данной статье. Как создать такую кнопку:

в версии Excel 2007 для начала потребуется отобразить вкладку «Разработчик», если она еще не отображена. Делается это через - Параметры Excel- Основные- Показывать вкладку «Разработчик» на ленте(рис.2). Для Excel 2010: Файл- Параметры- Настройка ленты- ставим галочку напротив Разработчик.

рис.2

Теперь переходим на эту вкладку и видим команду «Вставить». Жем и у нас выпадает меню(рис.3) с возможностью выбрать элементы.

рис.3

В категории «Элементы ActiveX» выбираем самый первый(это серенький кирпичик). Курсор приобретет вид тоненького крестика. Теперь нам надо на листе нарисовать кнопку. Все, наша кнопка готова.

Для обладателей версии Excel 2003 не намного все проще. Сначала необходимо отобразить панель инструментов «Элементы управления». Делается это через: Меню-Настройка-вкладка «Панели инструментов«(рис.4). На данной панели так же выбираем серенький кирпичик и рисуем кнопку.

рис.4

Но на этом не все. Надо же еще и назначить макрос. Для этого, не выходя из режима конструктора(в Excel 2007 он на вкладке Разработчик, а в Excel 2003 на панели Visual Basic -Режим конструктора), производим двойной щелчок по созданной кнопке — Excel перейдет в режим редактирования кода из интерфейса VBA и автоматом будет создана пустая процедура: Private Sub CommandButton1_Click(). CommandButton1 — это имя нашей кнопки. У Вас оно может отличаться. Теперь внутрь процедуры мы вписываем строку вызова макроса — Call Макрос1. Вместо Макрос1, естественно, имя необходимого Вам макроса.

Private Sub CommandButton1_Click()
    Call Макрос1
End Sub
Private Sub CommandButton1_Click()
    Call Макрос1
End Sub

А вот на этом, пожалуй, все для данного элемента.

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

Кнопка элемент управления формы — здесь проще. Для того, чтобы добавить данный элемент на лист Вам для начала придется выполнить примерно те же действия, что и для создания кнопки ActiveX.  Для пользователей 2007 Excel отобразить вкладку «Разработчик» на ленте(если еще не отображена). Только выбор кнопки теперь надо делать из первой группы — «Элементы управления форм». А для пользователей 2003 добавить панель инструментов «Формы» — Меню-Настройка-вкладка «Панели инструментов».  Опять же жмем на первый элемент — серенький кирпичик. Рисуем. Как только Вы нарисуете кнопку появится меню с просьбой назначить макрос на кнопку(рис.6). Вы можете сделать это сразу, либо позже.

Автофигура — для пользователей Excel 2007: вкладка «Вставка»-Фигуры. Просто выбираете любую понравившуся фигуру. Для пользователей 2003 для начала необходимо отобразить панель инструментов «Рисование», как описано выше. Там выбрать пункт «Автофигуры» и выбрать из категории понравившуюся фигуру. В свойствах фигуры «Формат фигуры»(доступно по правому клику мышки на фигуре) Вы можете выбрать цвет заливки, прозрачность, обводку и еще разные опции — здесь все зависит от Вашей фантазии.

Рисунок — для пользователей Excel 2007: вкладка «Вставка»-Рисунок. Выбираете любой рисунок, который имеется у Вас на компьютере. Для пользователей 2003 для начала необходимо отобразить панель инструментов «Рисование»(если не отображена). Жмете на кнопку «Добавить рисунок из файла» .

Теперь как назначить макрос на все это: жмем правой кнопкой мыши на  элементе управления формы, фигуре или картинке — Назначить макрос.

рис.5

Появится диалоговое окно выбора макросов. Выбираете нужный и жмете Ок. Все, наша кнопка готова и по её нажатии выполняется выбранный нами макрос.

рис.6

Таким образом можно назначить макрос не только указанным элементам, но и Диаграмме, элементу Надпись, обекту WordArt, рисунку SmartArt.

Надеюсь данная статья помогла Вам.

Categories: Tags:

Как сделать лист очень скрытым

 

Если Вы хотите скрыть лист со всем его содержимым от посторонних любопытных глаз, то Вы можете скрыть его из контекстного меню листа(это для счастливых обладателей 2007 Excel. Подробнее об этом здесь). Но при использовании данного метода необходимо защитить книгу, иначе отобразить скрытые листы сможет каждый. А что делать, если книгу не надо защищать? Что если необходимо оставить пользователям возможность работы со структурой книги, например, добавлять листы? А лист скрыть надо. Здесь на помощь придет VBA. Притом не обязательно иметь какие-то глубокие познания в программировании. Просто заходим в редактор VBA(Alt+F11) и щелкаем по листу в окне объектов(если не отображается то — Ctrl+R или View-Project Explorer). Подробнее про модули листов и как их найти читайте здесь. После того как выбрали необходимый лист в Окне свойств — Properties Window(если не отображается:F4 или View-Properties Window) находим свойство Visible и назначаем ему значение xlSheetVeryHidden(рис.1).

Скрыть лист через VBAрис.1

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

Чтобы затем отобразить этот лист необходимо свойству Visible задать значение — xlSheetVisible.

Скрыть все листы в книге, кроме листа с именем «Видимый» можно следующим макросом:

Sub Hide_All_Sheets()
    Dim wsSh As Object
    For Each wsSh In ActiveWorkbook.Sheets
        If wsSh.Name <> "Видимый" Then wsSh.Visible = xlSheetVeryHidden
        'отобразить – xlSheetVisible; сделать лист просто скрытым -  xlSheetHidden
    Next wsSh
End Sub
Sub Hide_All_Sheets()
    Dim wsSh As Object
    For Each wsSh In ActiveWorkbook.Sheets
        If wsSh.Name <> "Видимый" Then wsSh.Visible = xlSheetVeryHidden
        'отобразить – xlSheetVisible; сделать лист просто скрытым -  xlSheetHidden
    Next wsSh
End Sub

Также см.:
?Как сделать лист скрытым?

Как заменить/удалить/найти звездочку?

 

Наверное некоторые из Вас уже сталкивались с подобной ситуацией — на листе в ячейках записаны слова/предложения, в середине которых(ну или не в середине) имеется знак — * — звездочка. Иногда его нужно заменить на другой символ или просто удалить. Но тут проблема: символ звездочки(как и знак вопроса) является служебным символом — он заменяет собой группу любых символов(а знак вопроса — один любой символ). И естественно, при попытке заменить только звездочку Excel заменят ВСЕ данные в ячейке. Я имею ввиду обычную замену — (Excel 2003: Правка-Заменить; Excel 2007-2010: вкладка Главная-Найти и выделить-Заменить)Ctrl+H. Неужели ничего нельзя сделать? Можно. Разработчики предусмотрели такой вариант. Просто перед служебным символом необходимо поставить другой знак, который сообщает Excel, что следующий за ним символ — не выполняет свои служебные функции, а выступает в роли обычного символа. Знак этот называется Тильда. Вот такой — ~.

Таким образом в поле Найти поискового окошка должен получиться такой текст — «~*». И не стоит забывать про дополнительные параметры замены(они раскрываются при нажатии кнопки «Параметры» поискового окна). А именно — необходимо поставить/снять галочку у параметра «Ячейка целиком«. В рассматриваемом случае эту галочку необходимо снять.

А вот скриншот:

Если же Вам надо найти саму Тильду, то тут все также — просто указываете две тильды: ~~

По тому же принципу этот символ используется и в формулах, которые принимают знаки подстановки(ВПР, СЧЁТЕСЛИ, СУММЕЛИ и т.п.)

Но. Всегда есть но. Некоторые формулы этот знак воспринимают как текст сразу. К ним относится функция НАЙТИ. В данной функции для поиска данного символа не надо ставить перед ним еще один, потому как она не воспринимает символы подстановки как таковые. Она абсолютно любой символ принимает как текст и ищет строго то, что указано:
=НАЙТИ(«~»; A1)
=НАЙТИ(«*»; A1)
=НАЙТИ(«?»; A1)

Есть аналогичная ей функция — ПОИСК. В этой функции обязательно применять тот же принцип, что и во всех вышеперечисленных — т.е. явно указывать Excel, что мы ищем спец.символ:
=ПОИСК(«~~»; A1)
=ПОИСК(«~*»; A1)
=ПОИСК(«~?»; A1)

Как разрешить изменять только выбранные ячейки?

 

Для защиты ячеек от изменений в Excel существует такая команда как Защитить лист. Расположена она: в Excel 2003Сервис-Защита-Защитить лист; в Excel 2007 — вкладка Рецензирование-Защитить лист. Но при выполнении этой команды защищаются ВСЕ ячейки. А если хочется защитить все ячейки, кроме А1, С2 и D3? Очень просто. Выделяете эти ячейки(А1, С2 и D3); затем Ctrl+1(или правая кнопка мыши-Формат ячеек)-вкладка Защита. Снимаете галочку с Защищаемая ячейка. Все. Защищаете лист и теперь даже при защищенном листе в эти ячейки можно вносить изменения.

Если надо сделать обратное — защитить лишь несколько ячеек, то последовательность будет такой:

  • Выделяете ВСЕ ячейки листа(это можно сделать так:щелкаете левой кнопкой мыши на пересечении заголовков строки и столбцов — рис.1);
    Выделить все ячейкирис.1
  • Формат ячеек-вкладка Защита. Снимаете галочку с Защищаемая ячейка;
  • выделяете через Ctrl нужные ячейки;
  • Формат ячеек-вкладка Защита. Ставите галочку Защищаемая ячейка.

Также см.:
?Защита листов/снятие защиты
?Как защитить лист от пользователя, но не от макроса?
?Как оставить возможность работать со структурой на защищенном листе?

Как получить последннюю заполненную ячейку формулой?

 

Очень часто при работе с большими(и не очень) таблицами возникает вопрос: как узнать последнюю заполненную ячейку в столбце? Если в случае с VBA все ясно, то формулами это не всегда так…Однако даже это предусмотрели разработчики нашего любимого Excel-я :-)

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

Данная формула вернет Вам номер строки, в которой последней расположено число:

=ПОИСКПОЗ(3E+307;A1:A100)

А эта формула вернет номер строки, в которой последней расположены текстовые данные:

=ПОИСКПОЗ("яяя";A1:A100)

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

  1. =МАКС(ПОИСКПОЗ({"яяя";3E+307};A1:A100))
  2. =ПРОСМОТР(2;1/(A1:A100<>"");СТРОКА(A1:A100))

Первая формула вводится как формула массива(ввод формулы в ячейку завершается нажатием не просто Enter, а сочетанием клавиш Ctrl+Shift+Enter). Но есть и еще один недостаток: если в столбце нет какого-либо типа данных — формула вернет #Н/Д. Обхитрить можно, если захватить в расчет заголовок, в котором будет текст или число, в зависимости от того, какие данные предположительно могут отсутствовать.

Вторая формула вводится в ячейку обычным методом и вроде как не имеет никаких подводных камней. Кроме одного: не стоит указывать в качестве диапазона ВЕСЬ СТОЛБЕЦ с данными — формула будет очень долго пересчитываться. Особенно это сказывается в файлах версии 2007 Excel, где строк больше миллиона. Предыдущие формулы лишены этого недостатка. Хотя я в любом случае советовал бы указывать явно диапазон «с запасом».

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

Скачать пример »

  Tips_General_Last_Cell_Formula.xls (19,5 KiB, 1 280 скачиваний)

Также см.:
Как определить последнюю ячейку на листе через VBA?
Что такое формула массива

Как сделать лист скрытым?

 

Иногда требуется скрыть от чужих глаз информацию на каком-то листе. Для этого разработчики Excel предоставили нам неплохую возможность — скрывать листы. Жаль, что описанный ниже метод доступен лишь в версиях Excel старше 2003. Т.е. лист будет, но он не будет отображаться — ярлычка не будет. В общем-то и делается это так же элементарно, как звучит. Щелкаем правой кнопкой мыши по ярлычку листа, который хотим скрыть и выбираем пункт Скрыть(рис.1).

Скрыть листрис.1

Для версий 2003 и ниже Формат-Лист-Скрыть или см. Как сделать лист очень скрытым.

Примечание: невозможно скрыть лист, если это единственный лист в книге, о чем Excel и сообщит при попытке это сделать.

Лист скрыли. А вдруг он нам понадобится? Тогда проделываем те же действия — правая кнопка мыши по ярлычку листа, но теперь у нас есть пункт Отобразить. Его и выбираем. Появляется диалоговое окно отображения листов(рис.2).

Отобразить листрис.2

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

Также см.:
?Как сделать лист очень скрытым

Categories: Tags: ,

Как сделать одинаковые изменения сразу на нескольких листах

 

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

Все, что Вам необходимо сделать, это выделить все необходимые листы с зажатой клавишей Shift или Ctrl(зависит от расположения листов). Выделили. Теперь все, что мы делаем на активном(видимом) листе в точности повторяется на других выделенных листах. И что не менее важно — если прописать в активном листе формулу, то ссылки на других листах будут у каждого свои, т.е. ссылаться формулы будут на тот лист, в котором написаны(если в формуле не указаны явно ссылки на другие листы). То же самое и с копированием. Если на одном листе в ячейке А1 у Вас написано Дом, а на другом Холм, то при копировании ячейки А1 на активном листе и вставки её в ячейку А2, то на каждом листе в ячейку А2 будет скопировано своё значение — Дом и Холм соответственно.

Так же см.:
?Как одновременно просматривать информацию с нескольких листов/диапазонов?

Categories: Tags: ,

Как одновременно просматривать информацию с нескольких листов/диапазонов?

 

Возможно это и не пригодиться никому, а возможно для кого-то будет весьма актуально. К примеру: у Вас есть книга Excel с, скажем тремя, листами. На каждом листе свои данные. На «Лист1» в диапазоне A4:A10 занесены данные. На «Лист2» в диапазоне А1:А10 ведутся расчеты, результат которых зависит от данных на «Лист1«. На «Лист3» тоже расчеты, но зависящие от данных в диапазоне А1:А10 на «Лист2«. И напоследок в диапазоне  T100:T102 на «Лист1» ведутся расчеты, зависящие от данных и на «Лист1» и на «Лист2» и на «Лист3«. Так вот, если Вы хотите видеть результаты вычислений во всех этих диапазонах, то Вам придется либо переходить с одного листа на другой, либо воспользоваться предлагаемым мной методом.

Итак. Для начала нам потребуется внести некоторые изменения на Быструю панель. А именно добавить на неё один элемент — Камера. Делается это так: для Excel 2003: Вид-Панели инструментов-Настройки-Команды-Сервис-Камера; для Excel 2007: Меню-Параметры Excel-Настройка-Все команды-Камера.

После того, как мы вынесли на панель эту команду остается дело за малым.

  1. Выделяем диапазон А1:А10 на «Лист2»
  2. Жмем значек Камера на панели. Курсор мыши приобретет вид тоненького крестика.
  3. Переходим на «Лист1» и рисуем этим крестиком область, примерно того же размера, что и диапазон А1:А10.

Теперь мы видим на листе в выделенной области объект, отображающий всю информацию из «снятого» диапазона «Лист2» . Эта информация отображается в режиме он-лайн. Т.е. если на «Лист2» в «снятой» области произойдут изменения — то они отобразятся и в данном объекте. Повторяем эти действия для всех необходимых диапазонов.

Так же подобное можно создать макросом. Как ни странно, но макрорекордер не записывает действие создания инструмента Камера. А между тем код создания весьма прост:

Sub Create_Camera()
    Sheets("Лист1").Range("A1:C10").Copy
    ActiveSheet.Pictures.Paste(Link:=True).Select
End Sub
Sub Create_Camera()
    Sheets("Лист1").Range("A1:C10").Copy
    ActiveSheet.Pictures.Paste(Link:=True).Select
End Sub

Запускать код надо с того листа, на который необходимо поместить объект Камера. Сам объект будет вставлен в активную ячейку этого листа. А данные для просмотра берутся из диапазона «A1:C10″ листа «Лист1″ .

Чтобы самим указать, в какую ячейку вставить объект Камера, достаточно перед вставкой активировать нужную ячейку и нужный лист:

Sub Create_Camera()
    Sheets("Лист1").Range("A1:C10").Copy
    Sheets("Лист2").Activate
    [A1].Select
    ActiveSheet.Pictures.Paste(Link:=True).Select
End Sub
Sub Create_Camera()
    Sheets("Лист1").Range("A1:C10").Copy
    Sheets("Лист2").Activate
    [A1].Select
    ActiveSheet.Pictures.Paste(Link:=True).Select
End Sub

Скачать пример »

  Tips_All_Camera.xls (37,0 KiB, 2 258 скачиваний)

Так же см.:
?Как сделать одинаковые изменения сразу на нескольких листах

Как уменьшить размер файла

Данный вопрос возникает периодически на различных форумах. Решил написать некоторые рекомендации по уменьшению веса файла. Применив их Вы сможете понять — действительно ли Ваши данные настолько раздувают файл или же в файле имеется много лишнего.
Почему я это пишу. Бывают ситуации, когда в файле всего один лист, данных на нем на 1000 строк и 20 столбцов. Никаких формул, только значения. Но размер файла непомерно велик — скажем 10 Мб. Этого недопустимо. Или есть различные формулы, но Вы все равно считаете, что размер файла не соответствует тому, что должно бы быть. Приступим. Следующие действия необходимо проделать на каждом листе книги:


  • Первое, что необходимо вспомнить — а не в общем ли доступе Ваш файл? Если в Общем, то есть вероятность, что Ваш файл раздувается именно из-за этого. Дело в том, что при установке общего доступа к файлу, по умолчанию ведется журнал изменений, в который записываются все действия, произведенные в книге за период, указанный в параметрах. Как избавиться или изменить период:
    Excel 2007-2010: вкладка РецензированиеДоступ к книге;
    Excel 2003: СервисДоступ к книге.
    Переходим на вкладку Подробнее. Для начала активируйте пункт «Не хранить журнал изменений». Сохраните файл. Затем установите кол-во дней, в течении которых хотите хранить журнал. По умолчанию — 30, но Вы можете сделать меньше, если файл сильно разбухает.
  • Перейдя на лист, нажмите Ctrl+End. Активируется последняя ячейка листа. Если она расположена ниже или правее последних данных таблицы(или просто данных) — то удалите все строки и столбцы после последних данных таблицы. Удалите полностью строки. Сделать это быстро можно так. Нажали Ctrl+End, попали на последнюю ячейку. Выделили эту строку, нажали Ctrl+Shift+стрелка Вверх. Выделились все строки вместе с последней строкой Ваших данных. Удерживая Shift жмете стрелку Вниз. Тоже самое и со столбцами.
  • Посмотрите лист на предмет форматирования. Необходимо избегать форматирования ЦЕЛИКОМ столбцов либо строк. Это приводит к раздуванию файла. Все форматирование, выходящее за границы таблицы необходимо убрать. А еще лучше — вообще избегать излишнего форматирования, особенно если книгой никто, кроме Вас не пользуется. Вместо Заливки ячеек — Белым цветом ставьте «Нет заливки».Чтобы убрать все форматирование из ячеек: выделяете необходимый диапазон и:
    для Excel 2003: ПравкаОчиститьФормат.
    В Excel 2007-2010: вкладка ГлавнаяОчиститьОчистить форматы
    Очистка форматов
  • Удаляем объекты:
    В Excel 2003: меню Правка- ПерейтиВыделитьОбъекты.
    в Excel 2007-2010: вкладка Главная-Найти и выделить-Выделение группы ячеек-
    Объекты.
    Нажмите Delete. Все объекты на листе будут удалены. Правда есть небольшой шанс, что у Вас есть скрытые объекты. Тогда надо идти в редактор VBA(Alt+F11)-Ctrl+R. Отображаете окно свойств(F4). Находите объект ЭтаКнига(ThisWorkbook) и в окне свойств свойство — DisplayDrawingObjects и ставите там значение — -4104-xlDisplayShapes. После этого переходите опять на лист и повторяете операции по выделению и удалению объектов, описанные выше. Нахождение на листе объектов не означает, что Вы их видите. Как правило такие объекты попадают на лист в результате копирования из других файлов, содержащих объекты. Объекты в некоторых случаях переносятся с нулевой длиной и шириной. Как следствие — объект не видно, но файл увеличивается в размерах. И при каждом копировании он начинает увеличиваться в размерах в геометрической прогрессии. Мало того, файл начинает дико тормозить даже при выделении ячеек. Выделили ячейку, хотите выделить другую — файл задумался на пару секунд. Не очень удобно…
  • Еще очень хорошо помогает удаление всех формул и связей. Удаление не в прямом смысле: заменить все формулы значениями, которые они вернули. Это можно сделать без макросов: выделяем все ячейки с данными на листе-Копируем-правая кнопка мыши-Специальная вставка-Значения. Но это не очень удобно, если листов много, поэтому я заготовил для этого еще и макрос, который проделает эту операцию на всех листах активной книги:
    Sub All_Cells_In_All_Sheets_To_Value()
        Dim wsSh As Object
        For Each wsSh In Sheets
            wsSh.UsedRange.Value = wsSh.UsedRange.Value
        Next wsSh
    End Sub
    Sub All_Cells_In_All_Sheets_To_Value()
        Dim wsSh As Object
        For Each wsSh In Sheets
            wsSh.UsedRange.Value = wsSh.UsedRange.Value
        Next wsSh
    End Sub

    Данный макрос необходимо вставить в стандартный модуль и выполнить(нажав F5 в редакторе VBA или непосредственно с листа: нажать Alt+F8-выбрать макрос All_Cells_In_All_Sheets_To_Value-Выполнить)

  • Так же у Вас в книге могут содержаться имена, которые тоже порой довольно неплохо прибавляют вес файлу. Посмотреть их можно, нажав сочетание клавиш Ctrl+F3на листе. Но может оказаться так, что имена будут скрытыми, и тогда Вы просто так их не увидите. Отобразить их можно при помощи следующего кода:
    Sub All_Names_Visible()
        Dim objName As Object, wsSh As Object
        For Each objName In ActiveWorkbook.Names
            objName.Visible = True
        Next objName
        For Each wsSh In Sheets
            For Each objName In wsSh.Names
                objName.Visible = True
            Next objName
        Next wsSh
    End Sub
    Sub All_Names_Visible()
        Dim objName As Object, wsSh As Object
        For Each objName In ActiveWorkbook.Names
            objName.Visible = True
        Next objName
        For Each wsSh In Sheets
            For Each objName In wsSh.Names
                objName.Visible = True
            Next objName
        Next wsSh
    End Sub

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

    Sub Delete_All_Names()
        Dim objName As Object, wsSh As Object
        On Error Resume Next
        For Each objName In ActiveWorkbook.Names
            objName.Delete
        Next objName
        For Each wsSh In Sheets
            For Each objName In wsSh.Names
                objName.Delete
            Next objName
        Next wsSh
    End Sub
    Sub Delete_All_Names()
        Dim objName As Object, wsSh As Object
        On Error Resume Next
        For Each objName In ActiveWorkbook.Names
            objName.Delete
        Next objName
        For Each wsSh In Sheets
            For Each objName In wsSh.Names
                objName.Delete
            Next objName
        Next wsSh
    End Sub

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

  • Еще в книге могут содержаться скрытые листы, на которых может содержаться ненужная Вам информация и с которыми можно проделать операции, описанные выше. Как их обнаружить можно узнать, прочитав эти статьи:
    Как сделать лист скрытым?
    Как сделать лист очень скрытым

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

Categories: Tags: