Как скопировать названия всех листов excel

Microsoft Excel

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

Как создать список всех листов, содержащихся в книге Excel при помощи макрофункции

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

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

1. Вставьте новый лист, на котором будет находиться список.
2. Выполните команду Формулы ► Присвоить имя, чтобы открыть диалоговое окно Создание имени.
3. Впишите в поле Имя название Список.
4. Введите следующую формулу в поле Диапазон (рис. 9.1): =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ(«]»;ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));»») .
5. Осталось нажать ОК, чтобы закрыть диалоговое окно Создание имени.

Рис. 9.1. Работа с окном Создание имени для создания именованной формулы

Обратите внимание: в формуле используется функция ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ, которая не относится к числу стандартных функций рабочего листа. Это макрофункция, написанная в сравнительно старом XLM-стиле и предназначенная для применения на специальном листе макросов. При использовании аргумента 1 она возвращает массив имен листов, причем перед именем каждого листа указано название книги, откуда он взят. Функции ЗАМЕНИТЬ и НАЙТИ удаляют имя книги, сопутствующее названию листа. Чтобы сгенерировать имена листов, введите в ячейку А1 следующую формулу, а затем скопируйте ее ниже по столбцу: =ИНДЕКС(Список;СТРОКА()) .

На рис. 9.2 приведена данная формула для диапазона А1:А10. В книге семь листов, поэтому формула возвращает ошибку #ССЫЛКА! при попытке отобразить имя несуществующего листа. Чтобы избежать этого, измените формулу следующим образом: =ЕСЛИОШИБКА(ИНДЕКС(Список;СТРОКА());»») .

Рис. 9.2. Использование формулы для отображения списка имен листов

Список названий листов можно откорректировать, если вы будете добавлять, удалять или переименовывать листы, но это не происходит автоматически. Чтобы принудительно обновить формулы, нажмите Ctrl+Alt+F9. Если вы хотите, чтобы имена листов автоматически обновлялись при пересчете книги, измените именованную формулу следующим образом, чтобы сделать ее меняющейся: =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1;НАЙТИ(«]»;ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));»»)&Т(ТДАТА()) .

Рис. 9.3. Создание списка гиперссылок

Зачем может понадобиться список названий листов? На рис. 9.3 показано оглавление, созданное с помощью ссылки ГИПЕРССЫЛКА. В ячейке В1 записана следующая формула: =ГИПЕРССЫЛКА(«#»&A1&»!A1″;»Перейти по ссылке») . Щелкнув на гиперссылке, вы активизируете лист и выделяете ячейку А1. К сожалению, Excel не поддерживает гиперссылок, ведущих на лист с диаграммами, поэтому при попытке сослаться на такой лист программа выдаст ошибку.

Если вы пользуетесь такими приемами, то книгу нужно сохранить как файл с поддержкой макросов (в формате *.xlsm или *.xls).

Источник

Как получить список листов книги Excel

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

Список листов с помощью формулы

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

1. Переходим на вкладку «Формулы» и кликаем по кнопке «Диспетчер имен». Опция находится в разделе «Определенные имена».

1. Нажимаем «Создать» в открывшемся диалоговом окне.

1. Записываем новое имя в верхнем поле, выбираем область «Книга» (обычно она установлена по умолчанию) и записываем в графу «Диапазон» эту формулу: =ЗАМЕНИТЬ(ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1);1; НАЙТИ(“]”;ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(1));””)
2. После заполнения всех полей жмем «ОК». В книге Excel пока ничего не изменится, но эти шаги помогут в будущем. Окно диспетчера имен можно закрыть.

1. Открываем лист, где будет расположен список. Выбираем ячейку и записываем в ней формулу с только что созданным именем: =ИНДЕКС(Список_листов;СТРОКА()). Нажмите Enter, и в ячейке появится название первого листа.

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

1. Создадим список, из которого можно перейти на каждый лист. Выберите другую пустую ячейку и вставьте эту формулу: =ГИПЕРССЫЛКА(“#”&A1&”!A1″;””&A1).

1. Полный список с гиперссылками можно создать так же, с помощью маркера заполнения.
2. Если название листа изменено, придется перезаполнить списки – для этого нужно просто удалить их, снова вставить те же формулы и выделить ячейки ниже.

Как составить список листов через VBA

Существует другой способ составления списка листов из книги – можно подключить пользовательскую функцию через редактор Visual Basic. Такой метод может показаться сложным, но это не так, если воспользоваться шаблоном для добавления функции в программу.

1. Открываем VBA с помощью комбинации клавиш «Alt+F11». Если при нажатии F11 снижается яркость или срабатывает другая функция, установленная на эту кнопку, зажмите клавишу Fn.
2. Нажмите «Вставить» (Insert) на верхней панели и выберите в открывшемся меню пункт «Модуль» (Module).

1. Вставляем в свободное поле этот текст:

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

1. Открываем лист для списка и вводим формулу в начальную ячейку. Теперь не нужно длинное выражение, чтобы создать список листов. Новая формула выглядит так: =SheetList(СТРОКА()).
2. Нажимаем Enter и получаем название листа в ячейке. Маркером заполнения создаем список.

1. Для гиперссылок придется использовать ту же длинную формулу: =ГИПЕРССЫЛКА(“#”&A1&”!A1″;””&A1).

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

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

Существуют платные и бесплатные надстройки. Сегодня рассмотрим набор дополнений для Excel 2007-2019 под названием «Ёxcel». Разработчик распространяет файл на своем сайте за добровольное пожертвование. Установите надстройку по инструкции – после этого можно приступать к составлению списка.

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

1. Выбираем, какие листы показать в списке. Для простейшего перечисления названий листов кликаем по пункту «Простой список» и жмем на кнопку с галочкой в левом нижнем углу диалогового окна.
2. На экране появится список листов. Если выставить настройки сложнее, то внешний вид списка немного изменится.

Как подсчитать количество листов в книге

Иногда в книгах Excel появляется много листов, например если документ относится к крупному проекту. Выяснить, сколько в файле страниц, можно с помощью функции ЛИСТЫ.

1. Выбираем пустую ячейку и записываем в ней формулу: =ЛИСТЫ(). Не обязательно заполнять аргумент «Ссылка», если нужно посчитать листы в одной книге.

1. Жмем Enter и получаем числовое значение.

Если все листы переименованы, и нужно узнать их номера, воспользуйтесь функцией ЛИСТ. Эта формула также доступна с 2013-й версии. У функции ЛИСТ один аргумент – «Значение». Если аргумент не заполнен, после нажатия клавиши Enter в ячейке появится номер того же листа, где была введена формула. Простое выражение с ЛИСТ выглядит так: =ЛИСТ().

Источник

Как скопировать названия всех листов в excel

Как перемещать и копировать листы или их данные в Excel

В этом курсе:

• Создание книги
  Статья
• Вставка и удаление листов
  Статья
• Как перемещать и копировать листы или их данные в Excel
  Статья
• Печать листа или книги
  Статья
• Использование Microsoft Excel в качестве калькулятора
  Статья
• Автоматическое заполнение ячеек листа данными
  Статья
• Создание раскрывающегося списка
  Статья

Перемещение и копирование листов или данных листов

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

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

Перемещение листа в книге

Щелкните ярлычок листа и перетащите его в нужное место.

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

Копирование листа в одной книге

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

Щелкните ярлычок листа правой кнопкой мыши и выберите команду Переместить или скопировать.

Установите флажок Создать копию.

В разделе перед листом укажите, куда вы хотите поместить копию.

Нажмите кнопку ОК.

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

Перемещение листа

Перетащите ярлычок листа в нужное место в строке ярлычков.

Копирование листа

Нажмите и удерживайте клавишу OPTION.

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

Важно: Отпустите сначала кнопку мыши, а затем — клавишу OPTION.

Перемещение листа в другую книгу

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

Откройте книгу, в которую требуется переместить лист.

В меню Окно щелкните книгу, в которую входит перемещаемый лист.

Щелкните лист, который требуется скопировать в другую книгу.

В меню Правка выберите пункты Лист > Переместить или скопировать лист.

В меню В книгу выберите книгу, в которую требуется переместить лист.

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

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

Нажмите кнопку ОК.

Копирование листа в другую книгу

Откройте книгу, в которую требуется скопировать лист.

В меню Окно щелкните книгу, в которую входит копируемый лист.

Выберите лист, который требуется скопировать.

В меню Правка выберите пункты Лист > Переместить или скопировать лист.

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

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

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

Установите флажок Создать копию.

Нажмите кнопку ОК.

Изменение цвета ярлычка листа

Изменение цвета ярлычков листов может облегчить отслеживание информации в больших книгах.

Удерживая клавишу CONTROL, щелкните ярлычок листа.

Выберите команду Цвет ярлычка и укажите нужный цвет.

В Excel в Интернете можно дублировать (или копировать) книги. Просто щелкните правой кнопкой мыши имя ярлычка в нижней части листа и выберите команду Дублировать.

Примечание: При дублировании листа, содержащего диаграмму, рисунок или фигуру, может появиться сообщение об ошибке. В этом случае лист можно продублировать вручную.

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

Выделите все данные на листе.

Сочетания клавиш. Нажмите клавиши CTRL+ПРОБЕЛ на клавиатуре, а затем клавиши SHIFT+ПРОБЕЛ.

Скопируйте все данные на листе, нажав CTRL+C.

Щелкните знак плюса (+), чтобы добавить пустой лист.

Щелкните первую ячейку на новом листе и нажмите CTRL+V, чтобы ввести данные.

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

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

Чтобы переместить или скопировать книгу в другую книгу в Excel в Интернете, выполните указанные Excel в Интернете.

Выделите все данные на листе.

Сочетания клавиш: нажмите клавиши CTRL+ПРОБЕЛ, а затем клавиши SHIFT+ПРОБЕЛ.

Скопируйте все данные на листе, нажав CTRL+C.

Откройте книгу, в которую необходимо вставить данные, и щелкните знак «плюс» ( +) в строке состояния, чтобы добавить новый пустой лист.

Щелкните первую ячейку нового таблицы, а затем нажмите CTRL+V, чтобы ввести данные на этот.

Примечание: При вставке ячеек в другую книгу примененное к ним условное форматирование будет потеряно.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Как вывести список листов в Excel

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

Вывести список листов в ячейку можно следующим способом:

1. Перейти на вкладку VBA-Excel (доступна после установки).
2. В меню Диспетчеры выбрать пункт Диспетчер листов.
3. Выбрать команду Список листов
   
4. В диалоговом окне указать ячейку, в которую будет выводиться список листов. Вы можете вывести как все листы книги, так и те, которые вы выбрали в диспетчере листов.
   
5. Нажать кнопку Создать список

Источник