Green-sell.info

Новые технологии
182 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Диспетчер листов excel

Как сделать список листов в Excel?

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

Приветствую всех, дорогие читатели блога TutorExcel.Ru.

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

Поэтому рассмотрим 2 варианта создания списка, которые помогут нам автоматизировать процесс:

  • Формула. С помощью стандартных функций получим имена всех листов книги;
  • Пользовательская функция (UDF). Аналогичный способ реализованный посредством VBA.

Способ 1. Формула

Воспользуемся свойствами функции ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ (в английском варианте GET.WORKBOOK), которая позволит нам получить список всех листов текущей книги.
Для начала перейдем в Диспетчер имен — переходим в панели вкладок на вкладку Формулы в раздел Определенные имена и добавляем новое имя (к примеру, Список_листов) в качестве диапазона которого прописываем следующую формулу:

=ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ(«]»;ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));»»)


Формально в качестве формулы можно было прописать только =ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1), однако в этом случае в названиях листов будет также содержаться названия книги (например, [Книга1.xlsb]Лист1).
Удаляем все до символа правой закрывающейся квадратной скобки ], в итоге мы оставляем только имя листа (Лист1), и чтобы не делать это формульно каждый раз при обращении к отдельным элементам переменной Список_листов, сделаем это один раз сразу для всех элементов (как в примере выше).

Таким образом, в новой созданной переменной Список_листов содержатся название всех листов текущей книги, своего рода массив с данными, теперь же нам нужно извлечь их оттуда.
Для работы с массивом воспользуемся функцией ИНДЕКС (в английской версии INDEX), которая по порядковому номеру вытаскивает элемент массива, и функцией СТРОКА (английский вариант ROW) для создания простой нумерации:


Далее для удобства навигации с помощью функция ГИПЕРССЫЛКА (в английской версии HYPERLINK) добавим гиперссылки к названиям листов:


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

Способ 2. Пользовательская функция (UDF)

Чтобы каждый раз не вспоминать алгоритм извлечения имени листа, реализуем пользовательскую функцию с идентичным функционалом.
Все стандартно: переходим в редактор Visual Basic (сочетание клавиш Alt + F11), создаем новый модуль и копируем туда текст функции:

Работа с листами в Microsoft Excel

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

Пользователи могут упорядочить данные в пределах одной книги

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

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

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

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

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

С листами можно работать не только отдельно, но и группируя их между собой. То есть изменения, касающиеся одного, будут применены и ко всем остальным страницам группы. Для этого отметьте один лист и нажмите Ctrl. Держа клавишу зажатой, продолжайте отмечать каждый ярлык. Закончив выделение, отпустите клавишу Ctrl. Для отмены нажмите правой кнопкой мыши и в появившемся списке выберите «Разгруппировать».

Читать еще:  Как сделать абсолютную адресацию в excel

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

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

Как в Экселе добавить лист и другие важные действия с листами

Здравствуйте. Вся информация в книге Эксель хранится на рабочих листах. Структура такого документа похожа на записную книжку. В одном файле-хранилище может быть огромное количество пустых листиков, которые заполняют на своё усмотрение. Расположение вкладок листов мы рассматривали в посте об основных понятиях Excel.

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

Умение правильно обращаться с вкладками поможет вам делать удобные, аккуратные и компактные расчеты.

Переключение между активными рабочими листами Excel

Аналогично рабочим окнам, только один рабочий лист Эксель может быть активен для работы. Активировать рабочий лист можно так:

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

Вкладки рабочих листов

  • Использовать горячие клавиши Ctrl+PgUp и Ctrl+PgDn для циклического пролистывания вкладок
  • Как я уже говорил, вы можете создать огромное количество рабочих листов. Часто случается так, что все вкладки листов не помещаются на экране. Чтобы увидеть ярлыки, которые не влезли – используйте стрелки прокрутки ярлыков (см. рис.). Еще один способ — уменьшить длину горизонтальной полосы прокрутки, потянув её границу вправо. Таким, образом, освободится больше места для отображения вкладок.

    Стрелки прокрутки ярлыков

    Добавление нового рабочего листа

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

    • Кликнуть на ярлыке Новый лист справа от ярлыка последнего рабочего листа. Листик будет добавлен в конец списка
    • Кликнуть на вкладке листа правой кнопкой мыши и в контекстном меню выбрать команду Вставить . Новый лист появится перед тем, на который вы кликнули

    Вставка нового листа

  • Использовать комбинацию клавиш Shift+F11 Лист будет добавлен перед активной вкладкой
  • Переименование рабочего листа Эксель

    Если вам не нравится текущее имя листа – его можно заменить на более информативное. Для этого сделайте двойной клик по имени листа. На вкладке появится текстовый курсор, можно писать новое имя. После ввода – нажмите Enter , чтобы подтвердить изменения.

    Так же, можно нажать правой кнопкой мыши на вкладке и выбрать команду Переименовать . Эффект будет такой же, как и от предыдущего способа.

    Переименование листа Эксель

    Как скопировать или переместить лист

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

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

    Перемещение рабочего листа

  • Перетянуть ярлык мышью в нужное расположение текущей или другой рабочей книги. По умолчанию, документ будет перемещён. Если нужно сделать копию – зажмите клавишу Ctrl и тяните. Курсор будет изменен на миниатюру листа и небольшую стрелку, помогающую выбрать, куда вставить листик
  • Если в документе уже есть лист с таким именем, как у перемещаемого – Excel переименует последний. Например, «Книга1» станет «Книга1(2)».

    Скрыть или удалить рабочий лист MS Excel

    Давайте предположим, что вы делаете отчёт о продажах некоего товара за длительный период. На одном листе у вас исходные данные – неформатированная таблица с динамикой продаж. Второй лист – готовый отчет со сводной таблицей, диаграммами, выводами. Очевидно, что первую таблицу не нужно показывать пользователям, чтобы не запутать их. Лучше её скрыть.

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

    Скрыть лист Эксель

    Чтобы отобразить скрытый лист – кликните правой кнопкой мыши на любой вкладке и выберите Показать . В появившемся диалоговом окне выберите нужный лист и нажмите Ок . Ярлык листа снова появится в списке.

    Показать лист

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

    Действия над несколькими листами

    Бывает, нужно сделать одну операцию сразу с несколькими листами. Для этого не нужно повторять действия на каждом из листов. Выделите нужные листы и делайте всё за один раз. Для этого выполним такую последовательность:

    1. Кликнем на одном из нужных листов для его активации
    2. Зажмем Ctrl и кликаем по остальным листам, которые будут обрабатываться в группе. В адресной строке к названию файла добавится текст [Группа]
    3. Выполняем те операции, которые будут общими для всех выделенных листов
    4. Когда все работы выполнены — снимем группировку. Для этого нужно кликнуть правой кнопкой мыши на ярлыке любого выделенного листа и в контекстном меню выбрать Разгруппировать .

    Не забывайте снимать группировку, когда групповые работы закончены. Неосторожное поведение с этим функционалом может привести к «затиранию» нужной информации!

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

    Друзья, как всегда, жду ваших вопросов в комментариях и предлагаю подписаться на обновления блога!

    Работа с листами в Excel 2010

    Здравствуйте уважаемый посетитель сайта! В этом уроке мы начнем работу с листами Microsoft Excel 2010, в этом уроке вы научитесь добавлять новый лист Excel, узнаете, как переименовать лист в Excel, а также узнаете, как скрыть и отобразить скрытые листы Excel 2010.

    И так приступим к обучению работе с листами Microsoft Excel 2010.
    Как добавить лист в Excel 2010.
    В Microsoft Excel 2010 добавить листы можно несколькими способами, мы сейчас рассмотрим самый легкий и доступный способ добавления листа в Excel 2010.
    И так чтобы добавить новый лист в Excel 2010 вам нужно выполнить следующие действие в документе Excel 2010:
    В самом низу документа Excel 2010 расположены листы, чтобы добавить лист в Excel 2010 наведите курсор мыши на один из листов и нажмите правую кнопку мыши, после чего раскроется дополнительное меню, в котором вам нужно будет выбрать пункт «Вставить».

    Когда вы выберите пункт «Вставить» в документе Excel откроется окошка вставки «листа Excel, диаграммы Excel, макрос Excel и т.д.».
    В этом окошке выбираем «Лист» и нажимаем на кнопочку «OK».

    Когда вы выберите лист Excel и нажмете на кнопочку «OK», в документ Excel будет добавлен новый лист.

    Для быстрого добовления листа в Excel 2010 наведите курсор мыши на маленький значок расположеный рядом с листами Microsoft Excel 2010 и нажмите левую кнопку мыши, после чего в документе Excel 2010 будет добавлен новый лист.
    Чтобы добавить лист в Excel сочетанием клавиш нужно нажать клавиши Shift+F11

    Как переименовать лист в Excel 2010.
    И так чтобы переименовать листы в Excel 2010, наведите курсор мыши на тот лист, который хотите переименовать и нажмите правую кнопку мыши, после чего раскроется дополнительное меню, в котором вам нужно выбрать пункт «Переименовать».

    Когда вы выберите пункт «Переименовать» ваш лист выделится, теперь можно переименовать наш лист.

    Для примера давайте переименуем наш лист «Сборка компьютера», после того как вы напишете название для вашего листа нажмите на клавишу Enter, после чего ваш лист в Excel 2010 будет переименован.

    Как скрыть лист в Excel 2010.
    Чтобы скрыть лист в Excel 2010 наведите курсор мыши на тот лист, который хотите скрыть, затем нажмите правую кнопку мыши, после чего раскроется дополнительное меню, в котором вам нужно выбрать пункт «Скрыть».

    Когда вы выберите пункт «Скрыть» лист в документе Excel будет скрыт. Теперь давайте рассмотрим, как отобразить скрытый лист Excel.

    И так чтобы отобразить лист в Excel 2010 наведите курсор мыши на любой лист, затем нажмите правую кнопку мыши, после чего отобразится меню, в котором вам нужно выбрать пункт «Показать».

    Когда вы выберите пункт «Показать», в вашем документе Excel откроется окошко, в котором будут показаны все скрытые листы Excel.
    Теперь выбираем лист, который хотим отобразить и нажимаем на кнопочку «OK».

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

    Диспетчер листов excel

    На этом шаге мы рассмотрим диспетчер сценариев.

    Анализ данных с помощью таблиц подстановки является весьма эффективным. Однако он имеет несколько недостатков:

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

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

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

    Чтобы Вы смогли получить общее представление о средстве Диспетчер сценариев, начнем с простого примера — производственной модели (рис. 1).

    Рис. 1. Пример производственной модели

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

    Доступ к средству Диспетчер сценариев можно получить с помощью команды Сервис | Сценарии. При выборе этой команды будет вызвано диалоговое окно Диспетчер сценариев, показанное на рисунке 2.

    Рис. 2. Диалоговое окно Диспетчер сценариев

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

    Рис. 3. Диалоговое окно Добавление сценария

    • Название сценария. В этом поле можно указать любое имя сценария, но желательно, чтобы оно что-то обозначало.
    • Изменяемые ячейки. Ячейки, в которых находятся исходные данные для сценария. В это поле можно ввести абсолютный адрес ячейки или ее имя. Разрешается выбирать несколько ячеек, причем все они не обязательно должны быть смежными. В каждом сценарии, которому присвоено имя, можно использовать одни и те же наборы изменяемых ячеек или разные изменяемые ячейки. Количество изменяемых ячеек для одного сценария ограничено числом 32.
    • Примечание. По умолчанию в это поле Excel помещает информацию о том, кто создал сценарий, а также дату его создания. Однако Вы можете отредактировать этот текст, добавить к нему новый текст или вовсе удалить его.
    • Защита. Две опции, которые позволяют защитить сценарий от изменений и скрыть его, можно активизировать только в том случае, если рабочий лист защищен и в диалоговом окне Защитить лист активизирована опция Сценарии. Защита сценария предотвращает модификацию его кем-либо другим, а скрытый сценарий вообще не появляется в диалоговом окне Диспетчер сценариев.

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

    Рис. 4. Диалоговое окно Значение ячеек сценария

    Примечание . Для удобства использования сценариев необходимо поместить на любую панель инструмент Сценарий:

    • Выполнить команду Сервис | Настройка.
    • В диалоговом окне Настройка выбрать вкладку Команды.
    • Выбрать категорию Сервис.
    • Выделить инструмент Сценарий и перетащить его на любую панель инструментов.
    • Щелкнуть на кнопке Закрыть.

    На следующем шаге мы рассмотрим отображение, изменение и объединение сценариев.

    Ссылка на основную публикацию
    ВсеИнструменты
    Adblock
    detector