Как в excel сделать видимой только рабочую область?

Microsoft Excel

трюки • приёмы • решения

Как ограничить полезную площадь в листах таблиц Excel

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

Установка свойства ScrollArea

Свойство ScrollArea определяет видимый диапазон рабочего листа. Чтобы ограничить полезную площадь листа до определенного диапазона, выполните следующие действия.

  1. Откройте вкладку Разработчик. По умолчанию она не отображается, поэтому выберите команду Файл ► Параметры, затем в открывшемся диалоговом окне Параметры Excel перейдите к разделу Настройка ленты и установите флажок Разработчик в списке справа.
  2. Выполните команду Разработчик ► Элементы управления ► Свойства для отображения окна Properties (рис. 24.1).
  3. В поле ScrollArea окна Properties введите диапазон адресов и нажмите Enter. Вы не можете указать диапазон с помощью мыши — придется ввести его адрес
    вручную.

Рис. 24.1. Используйте окно Properties, чтобы установить область прокрутки для листа

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

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

  1. Сверните окно книги нажатием Ctrl+F5.
  2. Щелкните правой кнопкой мыши на строке заголовка книги и в контекстном меню выберите команду Исходный текст. Это действие отобразит модуль кода Эта Книга для книги.
  3. Введите следующий код VBA в модуль кода Эта Книга (корректируя имя листа и диапазон адресов):

Private Sub Workbook_Open() Worksheets(«Лист1»).ScrollArea = «C6:F13» End Sub

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

При открытии книги процедура Workbook_Open выполняется автоматически и свойство ScrollArea действует постоянно. Но, применяя данный метод, вы не сможете полностью ограничить доступ пользователей к частям книги. Так, продвинутый пользователь может открыть окно Properties и удалить содержимое поля ScrollArea или отключить макросы в открытой книге. Кроме того, чтобы отключить выполнение макроса Workbook_Open, достаточно нажать Shift при открытии файла.

Использование защиты листа

Второй метод ограничения полезной площади листа опирается на разблокирование ячеек и защиту книги.

  1. Выделите все ячейки, которые должны остаться незаблокированными. Это могут быть отдельные ячейки или любое количество диапазонов.
  2. Нажмите Ctrl+1. В появившемся диалоговом окне Формат ячеек перейдите на вкладку Защита и снимите флажок Защищаемая ячейка.
  3. Затем выберите команду Рецензирование ► Изменения ► Защитить лист и в открывшемся диалоговом окне Защита листа снимите флажок Выделение заблокированных ячеек (рис. 24.2).
  4. Если требуется, укажите пароль, который нужно ввести, чтобы снять защиту с рабочего листа, и нажмите ОК.

Рис. 24.2. Используйте диалоговое окно Защита листа, чтобы пользователь не мог выделять заблокированные ячейки

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

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

Трюк №8. Ограничение диапазона прокрутки листа Excel

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

Все листы Excel, созданные в Excel 97-2003, имеют 256 столбцов (от А до IV) и 65 536 строк. Чаще всего на листе используется лишь небольшая доля доступных ячеек. Немного усилий — и вы сможете ограничить область прокрутки листа только ячейками, содержащими нужные данные. Затем можно поместить данные, которые пользователь видеть не должен, в ячейки за пределами области прокрутки. Это также упростит переход по ячейкам на листе, так как часто возникает ситуация, когда пользователь неожиданно оказывается на 50 000 строке и начинает кричать, что на листе совершенно невозможно найти необходимые данные.

Самый простой способ установить границы — просто спрятать все неиспользуемые столбцы и строки. На своем листе найдите последнюю строку, содержащую данные, и целиком выделите строку под ней, щелкнув кнопку строки. Удерживайте клавиши Ctrl и Shift и нажимайте стрелку вниз, чтобы выделить все строки ниже. Чтобы спрятать их, выберите команду Формат → Строка → Скрыть (Format → Row → Hide). To же самое сделайте и с неиспользуемыми столбцами: найдите последний столбец с данными, полностью выделите столбец справа от него, удерживайте клавиши Ctrl и Shift, нажимая стрелку вправо, а затем выберите команду Формат → Столбец → Скрыть (Format → Column → Hide). Если все произошло как надо, используемые ячейки будут окружены серой областью, за пределы которой пе.рейти невозможно.

Второй способ определить границы — указать допустимый диапазон в окне Properties листа. Правой кнопкой мыши щелкните ярлычок листа в левом нижнем углу экрана и в контекстном меню выберите пункт View Code (Исходный текст). В Windows выберите команду View → Project Explorer (Ctrl+R), а в Mac OS X нажмите сочетание клавиш Apple+R, чтобы открыть окно Project Explorer. Если окно Properties не открыто, нажмите клавишу F4. Выберите нужный лист, и перейдите к свойству ScrollArea в окне Properties.

Теперь в Project Explorer выберите лист, область прокрутки которого хотите ограничить, а затем в окне Properties (рис. 1.12) перейдите к свойству ScrollArea. В соответствующем поле значений справа введите желаемые границы листа, например, $A$1:$G$50.

Рис. 1.12. Окно Properties в Project Explorer

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

Правой кнопкой мыши щелкните ярлычок с именем того листа, где нужно ограничить диапазон прокрутки, и в контекстном меню выберите команду Исходный текст (View Code), затем введите код из листинга 1.13.

// Листинг 1.13 Private Sub Worksheet_Activate () Me. Scroll Area = «A1:G50» End Sub

Как обычно, нажмите сочетание клавиш Alt/Apple+Q, чтобы вернуться в Excel и сохранить книгу. Хотя вы не сможете увидеть результат в виде серой области в первом способе, вы все же не сможете прокрутить лист или выбрать что-либо за пределами заданной области.

// Листинг 1.14 Sub МуМасrо() ‘ ‘ ‘макрос МуМасго ‘макрос записан 19/9/2011 ву Excel2010.ru ‘ ‘ ActiveSheet.Scroll Area = «» Range(«Z100»).Select Selection.Font.Bold = True ActiveSheet.Scroll Area — «$A$1:$G$50″ Sheets(‘Daily Budget»).Select ActiveSheet.Scroll Area = «» Range («T500»).Select Selection.Font.Bold = False ActiveSheet.Scroll Area = «$A$1:$H$25» End Sub

Наш записанный макрос выбирает ячейку Z100 и форматирует ее, делая шрифт жирным. Затем он выбирает лист с именем Daily Budget, выбирает на нем ячейку Т500 и отменяет жирный шрифт. Мы добавили строку ActiveSheet.ScrollArea = «», поэтому на листе можно выбрать любую ячейку, а затем восстановить желаемый ограниченный диапазон прокрутки. Выбирая другой лист (Daily Budget), мы снова позволяем коду выбирать любую ячейку на этом листе, а затем восстанавливаем ограниченный диапазон.

Третий метод, наиболее гибкий, автоматически ограничивает область прокрутки используемым диапазоном ячеек на листе, для которого вы создаете следующий код. Чтобы применить этот способ, правой кнопкой мыши щелкните ярлычок с именем нужного листа, в контекстном меню выберите пункт Исходный текст (View Code) и введите код, приведенный в листинге 1.15.

// Листинг 1.15 Private Sub Worksheet_Activate() Me.ScrollArea = Range(Me.UsedRange, Me.UsedRange(2.2)).Address End Sub

Теперь нажмите сочетание клавиш Alt/Apple+Q или щелкните крестик в правом верхнем углу окна, чтобы вернуться в Excel и сохранить книгу.

Этот макрос будет выполняться автоматически каждый раз, когда вы будете активировать лист, где он хранится. Однако некоторые проблемы могут возникнуть, если понадобится ввести данные за пределами текущего используемого диапазона. Чтобы избежать этого, примените стандартный макрос, который восстановит область прокрутки, равную полному листу. Выберите команду Сервис → Макрос → Редактор Visual Basic (Tools → Macro → Visual Basic Editor), затем выберите команду Insert → Module и введите код из листинга 1.16.

// Листинг 1.16 Sub ResetScrollArea() ActiveSheet.ScrollArea = «» End Sub

Теперь нажмите сочетание клавиш Alt/Apple+Q или щелкните крестик в правом верхнем углу окна, чтобы вернуться в Excel и сохранить книгу. Если хотите, можно упростить способ выполнения макроса, назначив ему сочетание клавиш. Выберите команду Сервис → Макрос → Макросы (Tools → Macro > Macros) или нажмите сочетание клавиш (Alt/Option+F8). Выберите ResetScrollArea (это имя вашего макроса), щелкните кнопку Параметры (Options) и выберите сочетание клавиш.

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

Как в Excel сделать удобный для работы интерфейс

Для комфортной и качественной работы с данными, недостаточно просто заполнить лист, структурировать рабочие окна и сгруппировать листы. Если вы используете большие объёмы информации – этот пост поможет в их обработке. Я буду вооружать вас знаниями, необходимыми для эффективного использования рабочего пространства, значительно превышающего размеры вашего экрана. Приступим!

Масштаб отображения листа Excel

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

  • Крутим колесо мышки при зажатой клавише Ctrl . Это самый простой и быстрый способ масштабирования. Такое универсальное правило работает для большинства окон в операционной системе Microsoft Windows. Вращение от себя — увеличиваем масштаб, к себе — уменьшаем.
  • Перемещаем бегунок масштаба в правом углу строки состояния Эксель. Этот инструмент всегда на экране, его использование просто и очевидно
    Настройка масштаба в строке состояния Excel
  • Команда на ленте Вид – Масштаб – Масштаб . Её выполнение открывает диалоговое окно выбора масштаба. Можно кликнуть на один из предложенных вариантов, либо записать нужный вариант вручную
    Выбор масштаба командой на ленте
  • Выделить диапазон ячеек, которые должны быть на экране. Далее выполняем команду Вид – Масштаб – Масштаб по выделенному . Программа подберет такую величину масштаба, чтобы на экране остался только выделенный фрагмент.

Создание дубликата рабочего окна

Иногда нужно открыть один и тот же документ в разных рабочих окнах. Это позволит использовать разные его участки одновременно. Чтобы создать новое рабочее окно – выделите нужный лист и выполните на ленте: Вид – Окно – Новое окно .

Создание нового окна Excel

Программа откроет еще один экземпляр текущего рабочего окна. Чтобы вы не запутались, Эксель добавит к имени документа в строке имён двоеточие и порядковый номер окна. Например, вы продублировали документ с именем «Книга». Исходное окно будет называться «Книга:1», а дубликат – «Книга:2». Навигация по таким листам будет выполняться раздельно, а вот изменения содержимого отобразятся в обоих окнах.

Чтобы закрыть лишние дубликаты – воспользуйтесь стандартными способами, например, крестиком (х) в строке имени.

Сравнение рабочих листов Excel

Часто нам нужно визуально сравнить данные, находящиеся в двух разных окнах. Для этого важно разместить данные рядом на экране. Майкрософт Эксель это умеет. Активируйте лист для сравнения и выполните: Вид – Окно – Рядом . Если открыто более двух окон – программа предложит выбрать документ, который нужно разместить рядом.

Отображение двух окон для сравнения

После выполнения, выбранные листы разместятся рядом, их будет удобно сравнивать. Если нужно, чтобы окошки прокручивались синхронно – кликните Вид – Окно – Синхронная прокрутка .

Для отмены режима сравнения, снова выполните команду Вид – Окно – Рядом .

Закрепление областей в Эксель

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

Закрепление областей в Excel

Чтобы закрепить первую строку листа – используйте кнопку Закрепить верхнюю строку . Для закрепления первого столбца жмите Закрепить первый столбец .

Вы можете зафиксировать на экране произвольное количество строк (начиная с первой) и столбцов (начиная с «А»). Для этого разместите курсор на одну строку ниже и на один столбец правее закрепляемых, выполните Вид – Окно – Закрепить области – Закрепить области .

Чтобы снять фиксацию области, нажмите: Вид – Окно – Закрепить области – Снять закрепление областей .

Разделение листа на области

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

Чтобы разделить лист – убедитесь, что у вас отключено закрепление областей. Далее выполните следующее:

  • Для разделения экрана на две части по вертикали – установите курсор в первой строке, и столбце справа от границы разделения. Например, нужно вставить разделитель между столбцами K и L, тогда выделите ячейку L1. Нажмите на ленте Вид – Окно – Разделить .
  • Для разбивки на две части по горизонтали выделите ячейку в первом столбце и строке под линией раздела. Выполните ту же команду, что и в предыдущем примере
  • Чтобы разделить лист на 4 части – установите курсор в ячейку справа и ниже от пересечения разделителей. Например, линия раздела должна проходить между строками 22 и 23, столбцами L и M. Тогда курсор нужно поставить в ячейку M23 Выполняем всю ту же команду Вид – Окно – Разделить .
    Разделение окна на 4 области

Чтобы отменить разделение, повторно нажмите Разделить .

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

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

Выполните команду Формулы – Зависимости формул – Окно контрольного значения . На экране появится окно отслеживания значения ячейки. Предположим, нам нужно контролировать значение ячейки А1. В окне контроля нажмём Добавить , кликнем мышью на ячейке А1 и еще раз Добавить . Всё, ячейка А1 будет контролироваться, и если окажется за пределами видимой области – вы всё равно будете знать её значение в любой момент времени.

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

На рисунке мы видим, что ячейка А1 не видна на экране, но её содержимое (число «125») видно в окне контрольного значения.

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

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

В следующем посте я планирую рассказать как работать со столбцами и строками, а пока задавайте ваши вопросы!

Трюк №8. Ограничение диапазона прокрутки листа Excel

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

Все листы Excel, созданные в Excel 97-2003, имеют 256 столбцов (от А до IV) и 65 536 строк. Чаще всего на листе используется лишь небольшая доля доступных ячеек. Немного усилий — и вы сможете ограничить область прокрутки листа только ячейками, содержащими нужные данные. Затем можно поместить данные, которые пользователь видеть не должен, в ячейки за пределами области прокрутки. Это также упростит переход по ячейкам на листе, так как часто возникает ситуация, когда пользователь неожиданно оказывается на 50 000 строке и начинает кричать, что на листе совершенно невозможно найти необходимые данные.

Самый простой способ установить границы — просто спрятать все неиспользуемые столбцы и строки. На своем листе найдите последнюю строку, содержащую данные, и целиком выделите строку под ней, щелкнув кнопку строки. Удерживайте клавиши Ctrl и Shift и нажимайте стрелку вниз, чтобы выделить все строки ниже. Чтобы спрятать их, выберите команду Формат → Строка → Скрыть (Format → Row → Hide). To же самое сделайте и с неиспользуемыми столбцами: найдите последний столбец с данными, полностью выделите столбец справа от него, удерживайте клавиши Ctrl и Shift, нажимая стрелку вправо, а затем выберите команду Формат → Столбец → Скрыть (Format → Column → Hide). Если все произошло как надо, используемые ячейки будут окружены серой областью, за пределы которой пе.рейти невозможно.

Второй способ определить границы — указать допустимый диапазон в окне Properties листа. Правой кнопкой мыши щелкните ярлычок листа в левом нижнем углу экрана и в контекстном меню выберите пункт View Code (Исходный текст). В Windows выберите команду View → Project Explorer (Ctrl+R), а в Mac OS X нажмите сочетание клавиш Apple+R, чтобы открыть окно Project Explorer. Если окно Properties не открыто, нажмите клавишу F4. Выберите нужный лист, и перейдите к свойству ScrollArea в окне Properties.

Теперь в Project Explorer выберите лист, область прокрутки которого хотите ограничить, а затем в окне Properties (рис. 1.12) перейдите к свойству ScrollArea. В соответствующем поле значений справа введите желаемые границы листа, например, $A$1:$G$50.

Рис. 1.12. Окно Properties в Project Explorer

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

Правой кнопкой мыши щелкните ярлычок с именем того листа, где нужно ограничить диапазон прокрутки, и в контекстном меню выберите команду Исходный текст (View Code), затем введите код из листинга 1.13.

// Листинг 1.13 Private Sub Worksheet_Activate () Me. Scroll Area = «A1:G50» End Sub

Как обычно, нажмите сочетание клавиш Alt/Apple+Q, чтобы вернуться в Excel и сохранить книгу. Хотя вы не сможете увидеть результат в виде серой области в первом способе, вы все же не сможете прокрутить лист или выбрать что-либо за пределами заданной области.

// Листинг 1.14 Sub МуМасrо() ‘ ‘ ‘макрос МуМасго ‘макрос записан 19/9/2011 ву Excel2010.ru ‘ ‘ ActiveSheet.Scroll Area = «» Range(«Z100»).Select Selection.Font.Bold = True ActiveSheet.Scroll Area — «$A$1:$G$50″ Sheets(‘Daily Budget»).Select ActiveSheet.Scroll Area = «» Range («T500»).Select Selection.Font.Bold = False ActiveSheet.Scroll Area = «$A$1:$H$25» End Sub

Наш записанный макрос выбирает ячейку Z100 и форматирует ее, делая шрифт жирным. Затем он выбирает лист с именем Daily Budget, выбирает на нем ячейку Т500 и отменяет жирный шрифт. Мы добавили строку ActiveSheet.ScrollArea = «», поэтому на листе можно выбрать любую ячейку, а затем восстановить желаемый ограниченный диапазон прокрутки. Выбирая другой лист (Daily Budget), мы снова позволяем коду выбирать любую ячейку на этом листе, а затем восстанавливаем ограниченный диапазон.

Третий метод, наиболее гибкий, автоматически ограничивает область прокрутки используемым диапазоном ячеек на листе, для которого вы создаете следующий код. Чтобы применить этот способ, правой кнопкой мыши щелкните ярлычок с именем нужного листа, в контекстном меню выберите пункт Исходный текст (View Code) и введите код, приведенный в листинге 1.15.

// Листинг 1.15 Private Sub Worksheet_Activate() Me.ScrollArea = Range(Me.UsedRange, Me.UsedRange(2.2)).Address End Sub

Теперь нажмите сочетание клавиш Alt/Apple+Q или щелкните крестик в правом верхнем углу окна, чтобы вернуться в Excel и сохранить книгу.

Этот макрос будет выполняться автоматически каждый раз, когда вы будете активировать лист, где он хранится. Однако некоторые проблемы могут возникнуть, если понадобится ввести данные за пределами текущего используемого диапазона. Чтобы избежать этого, примените стандартный макрос, который восстановит область прокрутки, равную полному листу. Выберите команду Сервис → Макрос → Редактор Visual Basic (Tools → Macro → Visual Basic Editor), затем выберите команду Insert → Module и введите код из листинга 1.16.

// Листинг 1.16 Sub ResetScrollArea() ActiveSheet.ScrollArea = «» End Sub

Теперь нажмите сочетание клавиш Alt/Apple+Q или щелкните крестик в правом верхнем углу окна, чтобы вернуться в Excel и сохранить книгу. Если хотите, можно упростить способ выполнения макроса, назначив ему сочетание клавиш. Выберите команду Сервис → Макрос → Макросы (Tools → Macro > Macros) или нажмите сочетание клавиш (Alt/Option+F8). Выберите ResetScrollArea (это имя вашего макроса), щелкните кнопку Параметры (Options) и выберите сочетание клавиш.

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

Читать еще:  Как сделать ячейку в excel по размеру текста?
Ссылка на основную публикацию
×
×
Adblock
detector