Excel

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

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

1.Выделяем диапазон А1:А10 на "Лист2"

2.Жмем значок Камера на панели. Курсор мыши приобретет вид тоненького крестика.

3.Переходим на "Лист1" и рисуем этим крестиком область, примерно того же размера, что и диапазон А1:А10. 

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

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

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

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

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

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

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

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

рис.1

Формат ячеек - вкладка Защита. Снимаете галочку с Защищаемая ячейка;

выделяете через Ctrl нужные ячейки;

Формат ячеек - вкладка Защита. Ставите галочку Защищаемая ячейка. 
 

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

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

Способ первый - создание именованной формулы(без  применения VBA - Visual Basic for Application)

Выделяете ячейку B1

жмеte Ctrl+F3(вызов диспетчера имен)

в поле имя пшите - HasFormula(или любое понравившееся Вам название, но не противоречащее правилам создания имен в Excel)

в поле Диапазон вписываете такую формулу: =ПОЛУЧИТЬ.ЯЧЕЙКУ(48;Лист1!A1) 

Теперь записав  в ячейку В1 =HasFormula Вы узнаете, есть ли в ячейке А1 формула или же там константа. 

Недостатки данного  метода - Вы привязаны к "левостороннему" определению формулы. Т.е. таким методом  Вы можете узнать есть ли формула в ячейке, расположенной слева от ячейки с именованной формулой. Для того, чтобы "узнать формулу справа" именованная формула должна выглядеть так: =ПОЛУЧИТЬ.ЯЧЕЙКУ(48;Лист1!C1). 

Способ второй - создание пользовательской функции.

Открываете редактор VBA

Вставляете обычный  модуль

В модуль копируете  следующий код:  

Function IsFormula(ByVal Cell As Range, Optional ShowFormula As Boolean = False)

   If ShowFormula Then

        If Cell.HasFormula Then

           IsFormula = "Формула: " & IIf(Cell.HasArray, "{" & Cell.FormulaLocal & "}", Cell.FormulaLocal)

        Else

           IsFormula = "Значение: " & Cell.Value

        End If

   Else

        IsFormula = Cell.HasFormula

   End If

End Function 

Теперь в ячейку записываете =IsFormula(A2). 

Данная функция имеет два аргумента:

Cell - ссылка на проверяемую ячейку;

ShowFormula - необязательный аргумент. Если присвоено значение ЛОЖЬ или 0 или опущен(не указан) - то функция вернет значение ИСТИНА или ЛОЖЬ(в зависимости от наличия или отсутствия формулы в ячейке). Если присвоено значение ИСТИНА или 1, то в функция вернет не просто ИСТИНА или ЛОЖЬ, а еще и текст самой формулы.  

Формулу можно  так же найти в диспетчере функций  в категории Определенные пользователем - IsFormula. 
 

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

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

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

Приступим. Следующие действия необходимо проделать на каждом листе книги:

Перейдя на лист, нажмите Ctrl+End. Активируется последняя ячейка листа. Если она расположена ниже или правее последних данных таблицы(или просто данных) - то удалите все строки и столбцы после последних данных таблицы. Удалите полностью строки. Сделать это быстро можно так. Нажали Ctrl+End, попали на последнюю ячейку. Выделили эту строку, нажали Ctrl+Shift+End. Выделились все строки вместе с последней строкой Ваших данных. Удерживая Shift жмете стрелку Вниз. Тоже самое и со столбцами.

Посмотрите лист на предмет форматирования. Необходимо избегать форматирования ЦЕЛИКОМ столбцов либо строк. Это приводит к раздуванию файла. Все форматирование, выходящее за границы таблицы необходимо убрать. А еще лучше - вообще избегать излишнего форматирования, особенно если книгой никто, кроме Вас не пользуется. Вместо Заливки ячеек - Белым цветом ставьте "Нет заливки".  

Чтобы убрать все  форматирование из ячеек: выделяете необходимый диапазон - для Excel 2003: Правка - Очистить - Формат. В Excel 2007: вкладка Главная - Очистить - Очистить форматы 

 

Удаляем объекты:

В Excel 2003:

меню Правка- Перейти – Выделить – Объекты.

В Excel 2007:

вкладка Главная - Найти и выделить - Выделение группы ячеек - Объекты. 

Нажмите Delete. Все объекты на листе будут удалены. Правда есть небольшой шанс, что у Вас есть скрытые объекты. Тогда надо идти в редактор VBA(Alt+F11)-Ctrl+R. Отображаете окно свойств(F4). Находите объект ЭтаКнига(ThisWorkbook) и в окне свойств свойство - DisplayDrawingObjects и ставите там значение - -4104-xlDisplayShapes.

После этого  переходите опять на лист и повторяете операции по выделению и удалению объектов, описанные выше.  

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

6.9 Excel удаляет вместо отфильтрованных строк - все?! Как избежать 

  С появлением новой версии Excel 2007 появилась возможность работать с данными более 1000000 строк(если точно - 1048576), строить большие сводные таблицы, фильтровать и сортировать данные по цвету и т.д. Но появились и неудобства. Об одном таком неудобстве и пойдет речь в этой статье. При активном фильтре на листе в Excel 2003 можно было просто выделить отфильтрованные строки и удалить. В 2007 если сделать также, то удаляются не только отфильтрованные строки, но и все, что между ними! Да и все операции с отфильтрованными данными применяются почему-то не к отфильтрованным данным, а ко всем. Поначалу это пугает и вкрадывается подозрение, что новую версию Excel разрабатывали диверсанты с одной целью - не дать Вам нормально работать. Но нет. Этот глюк легко обойти. Есть два способа. 

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

Необходимо просто вынести на Панель быстрого доступа  команду - "Выделить видимые ячейки". Делается это следующим образом: Меню-Параметры Excel-Настройка-Все команды - Выделить видимые ячейки.

 

Теперь перед  совершением какой-либо операцией  Вы выделяете диапазон, затем жмете  на панели быстрого доступа кнопку "Выделить видимые ячейки". Применяете действие(удаление, вставка формулы и т.д.). Данное действие будет применено именно к видимым отфильтрованным ячейкам. 

Второй способ:

Перед удалением  строк(или другим действием) выделяете  необходимые ячейки, жмете Alt+ж(для русской раскладки и соответственно Alt+; для английской). Это быстрые клавиши для выделения видимых ячеек. А далее? Правильно - применяете действие(удаление, вставка формулы и т.д.). Данное действие будет применено именно к видимым отфильтрованным ячейкам. 
 
 
 

Список литературы:

1)Марк Додж, Крейк Стинсон «Эффективная работа c Microsoft excel 2000.»-спб.:Питер,2001-с.:ил.

2)Microsoft Excel 2000. Шаг за шагом: Практическое пособие./ превод с английского – Москва: Издательство ЭКОМ, 1999.- 472 с.: илл.

3)Калугина О.Б. «Работа с электронными таблицами. Microsoft Office Excеl 2003.»

4)Смирнов В.М.  «обработка даны средствами MS EXCEL»ЧГУ2008 – 68с.

5)Кузьмин В.  «Microsoft Office Excеl 2003:русская версия:учеб.курс» - СПБ: Питер; Киев: BHV,2005. – 462с.

6)Рудикова Л.В. «Microsoft Office для студента» - СПБ.: БХВ-Петербург, 2005- 589с.

7)Берков Н.А.  «программирование на Visual Basic: учебное пособие» - М.:ГИНФО, 2001, - 159с.