Excel количество листов в книге for - Word и Excel - помощь в работе с программами

Перебор листов в книге Excel циклом For Each… Next с копированием данных из этих листов и вставкой в новый лист той же книги с помощью кода VBA.

Условие задачи по перебору листов

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

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

Для открытия книги (получения полного имени) будем использовать диалоговое окно выбора файлов GetOpenFilename, а для перебора листов — цикл For Each… Next.

Пример кода для перебора листов в книге Excel циклом For Each… Next с частичным копированием данных на отдельный лист:

Sub CopyDataFromAllSheets()

Dim wb As Workbook, newws As Worksheet, ws As Worksheet, n As Long

n = 1

‘Выбираем, открываем нужную книгу и присваиваем ссылку на нее переменной wb

Set wb = Workbooks.Open(Application.GetOpenFilename(«Файлы Excel,*.xls*», , «Выбор файла»))

‘Создаем в открытой книге новый лист и присваиваем ссылку на него переменной newws

Set newws = wb.Worksheets.Add

With newws

‘Присваиваем имя новому листу: «Отчет от dd.mm.yyyy»

.Name = «Отчет от « & Date

‘Запускаем цикл, перебирающий листы

For Each ws In wb.Worksheets

‘Проверяем, что имя текущего листа не равно имени нового листа «Отчет…»

If ws.Name <> newws.Name Then

‘Копируем столбец «B» текущего листа на лист «Отчет…» в столбец n

ws.Columns(«B»).Copy Destination:=.Columns(n)

‘Добавляем ячейку cверху столбца n

.Cells(1, n).Insert Shift:=xlShiftDown

‘Записываем в добавленную ячейку имя текущего листа

.Cells(1, n) = ws.Name

‘Задаем тексту в добавленной ячейке полужирное начертание

.Cells(1, n).Font.Bold = True

n = n + 1

End If

End With

End Sub

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

Источник

Diana

Пользователь

Сообщений: 982
Регистрация: 28.12.2012

Доброго времени суток всем на форуме!
маленький вопросик на ночь, чтобы крепче спалось
искала по всему форуму и интернету, но так и не нашла.

вопрос: как определить количество листов в книге?
например, чтобы в cells(1,1) написалось количество листов текущей книги.

понимаю, что нужно использовать Worksheets.Count, но что я с ним только не творила, не вышло… прошу помощи

спасибочки всем-всем!

-88232-

	А чего с ним творить? Число обычных листов cells(1,1)=worksheets.Count Число всех листов включая диаграммы и пр. cells(1,1)=sheets.Count

KuklP Пользователь Сообщений: 14868 Регистрация: 21.12.2012 E-mail и реквизиты в профиле.	Чего это? Public Sub www() [a1] = ThisWorkbook.Worksheets.Count End Sub В активный лист вставит… Я сам — дурнее всякого примера! …

Diana Пользователь Сообщений: 982 Регистрация: 28.12.2012	хм… вот так все просто да… а я мучилась почти три часа! Спасибочки Сергей и неизвестный автор!!!

KuklP Пользователь Сообщений: 14868 Регистрация: 21.12.2012 E-mail и реквизиты в профиле.	Баиньки, Дайана. Хороших снов. Я сам — дурнее всякого примера! …

	5 сек. я же не засну, пока Диана не скажет, кто на её аватарке? (если она уже говорила, то прошу меня простить, видно я пропустил этот момент) )

Юрий М Модератор Сообщений: 60570 Регистрация: 14.09.2012 Контакты см. в профиле

	{quote}{login=}{date=24.10.2010 11:57}{thema=}{post}5 сек. я же не засну, пока Диана не скажет, кто на её аватарке? (если она уже говорила, то прошу меня простить, видно я пропустил этот момент) ){/post}{/quote} …… и тут Остапа понесло………

	Классно рисует )) P.S. Эта аватарка мне намного больше нравится, чем предыдущая ))

	эх, даже порадоваться за Диану не дадут ((( Злые Вы.. уйду я от .. спать )) Спокойной ночки, Дианочка …

Diana Пользователь Сообщений: 982 Регистрация: 28.12.2012	сладких снов, молодые люди! и спасибо за помощь а на фото я как и на предыдущей

	Чувствую нужно получать Немецкую визу…

Guest Гость	#13 25.10.2010 13:18:26 Диана, на вкладке ССЫЛКИ есть такая — http://msoffice.nm.ru/faq/macros.htm ——- Сайт Павла Юрьевича Климова, практически полностью посвященный Excel. Обширный FAQ, множество примеров, доступных для бесплатного скачивания, несколько пользовательских функций VBA — есть что посмотреть! ——— Очень помогает, там такие кирпичики в здание решения:) По совету с форума там часто провожу время при поиске решений;) Игорь67

Источник

На чтение 5 мин Опубликовано 26.01.2021

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

Содержание

Список листов с помощью формулы
Как составить список листов через VBA
Надстройки для составления списка листов
Как подсчитать количество листов в книге

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

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

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

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

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

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

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

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

Обратите внимание! Ячейка A1 прописывается в формуле, чтобы пользователи могли перейти на конкретную ячейку каждого листа. После нажатия Enter появится кликабельное название листа.

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

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

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

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

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

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

Function SheetList(N As Integer)

SheetList = ActiveWorkbook.Worksheets(N).Name

End Function

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

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

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

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

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

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

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

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

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

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

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

Обратите внимание! Функция работает только в версиях Microsoft Excel от 2013.

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

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

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

Оцените качество статьи. Нам важно ваше мнение:

Источник

Содержание

Microsoft Excel
Как создать список всех листов, содержащихся в книге Excel при помощи макрофункции
Примеры использования функций ЛИСТ и ЛИСТЫ в формулах Excel
Функции ЛИСТ и ЛИСТЫ в Excel: описание аргументов и синтаксиса
Как получить имя листа формулой в Excel
Примеры использования функции ЛИСТ и ЛИСТЫ
Обработка информации о листах книги по формуле Excel
Как получить список листов книги Excel
Список листов с помощью формулы
Как составить список листов через VBA
Надстройки для составления списка листов
Как подсчитать количество листов в книге

Microsoft Excel

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

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

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

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

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

Рис. 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 возвращает числовое значение, которое соответствует количеству листов, на которые предоставлена ссылка.

Обе функции полезны для использования в документах, содержащих большое количество листов.
Лист в Excel – это таблица из всех ячеек, отображаемых на экране и находящихся за его пределами (всего 1 048 576 строк и 16 384 столбца). При отправке листа на печать он может быть разбит на несколько страниц. Поэтому нельзя путать термины «лист» и «страница».
Количество листов в книге ограничено лишь объемом ОЗУ ПК.

Функция ЛИСТ имеет в своем синтаксисе всего 1 аргумент и то не обязательный для заполнения: =ЛИСТ(значение).

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

При работе функции ЛИСТ учитываются все листы, которые являются видимыми, скрытыми и очень скрытыми. Исключениями являются диалоги, макросы и диаграммы.
Если аргументом функции является текстовое значение, которое не соответствует названию ни одного из листов, содержащихся в книге, будет возвращена ошибка #НД.
Если в качестве аргумента функции было передано недействительное значение, результатом ее вычислений будет являться ошибка #ССЫЛКА!.
В рамках объектной модели (иерархия объектов на VBA, в которой Application является главным объектом, а Workbook, Worksheer и т. д. – дочерними объектами) функция ЛИСТ недоступна, поскольку она содержит схожую функцию.

Функция листы имеет следующий синтаксис: =ЛИСТЫ(ссылка).

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

Данная функция подсчитывает количество всех скрытых, очень скрытых и видимых листов, за исключением диаграмм, макросов и диалогов.
Если в качестве параметра была передана недействительная ссылка, результатом вычислений является код ошибки #ССЫЛКА!.
Данная функция недоступна в объектной модели в связи с наличием там схожей функции.

Как получить имя листа формулой в Excel

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

Описание аргументов для функции ПСТР:

ЯЧЕЙКА(«имяфайла») – функция, возвращающая текст, в котором функция ПСТР выполняет поиск заданного количества символов. В данном случае вернется значение «C:UserssoulpDesktop[ЛИСТ_ЛИСТЫ.xlsx]Статические расчеты», где после символа «]» находится искомый текст – название листа.
НАЙТИ(«]»;ЯЧЕЙКА(«имяфайла»))+1 – функция, возвращающая номер позиции символа «]», единица добавлена с той целью, чтобы функция ПСТР не учитывала символ «]».
31 – максимальное количество символов в названии листа.

=ЛИСТ() – данная функция без параметра вернет номер текущего листа. В результате ее вычисления получим количество листов в текущей книге.

Примеры использования функции ЛИСТ и ЛИСТЫ

Пример 2. В книге Excel содержится несколько листов. Необходимо:

Вернуть номер текущего листа.
Вернуть номер листа с названием «Статические_расчеты».
Вернуть номер листа «Динамические_расчеты», если его ячейка A3 содержит значение 0.

Внесем данные в таблицу:

Далее составим формулы для всех 4-х условий:

для условия №1 используем следующую формулу: =ЛИСТ()
для условия №2 введем формулу: =ЛИСТ(«Статические_расчеты»)
для условия №3 запишем формулу:

Функция ЕСЛИ выполняет проверку условия равенства значения, хранящегося в ячейке A3 листа Динамические_расчеты, нулю или пустому значению.

В результате получим:

Обработка информации о листах книги по формуле Excel

Пример 3. В книге Excel содержится несколько листов. Необходимо определить общее количество листов, а также число листов, содержащихся между листами «Статические_расчеты» и «Экономические_расчеты».

Исходная таблица имеет вид:

Общее количество листов вычислим по формуле:

Для определения количества листов, содержащихся между двумя указанными листами, запишем формулу:

Статические_расчеты:Экономические_расчеты!A2 – ссылка на ячейку A2 диапазона листов между «Статические_расчеты» и «Экономические_расчеты» включая эти листы.
Для получения искомого значения было вычтено число 2.

В результате получим следующее:

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

Источник

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

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

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

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

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

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

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

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

Обратите внимание! Ячейка A1 прописывается в формуле, чтобы пользователи могли перейти на конкретную ячейку каждого листа. После нажатия Enter появится кликабельное название листа.

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

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

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

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

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

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

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

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

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

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

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

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

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

Обратите внимание! Функция работает только в версиях Microsoft Excel от 2013.

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

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

Источник

Подсчитать кол-во листов в экселе(через формулу)

frukti4ek

Дата: Вторник, 23.04.2013, 16:02 |
Сообщение № 1

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Добрый день! подскажите пожалуйста, как посчитать кол-во листов в экселе, если к примеру находишься на на 3-м листе, счет должен начинаться с 3 по n-ое число слева направо.
Может есть какая то формула по подсчету значений в какой то ячейке и все!!
Спасибо!

Ответить

Serge_007

Дата: Вторник, 23.04.2013, 16:26 |
Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 15888

Репутация:
2623
±

Замечаний:
±

Excel 2016

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

ЮMoney:41001419691823 | WMR:126292472390

Ответить

frukti4ek

Дата: Вторник, 23.04.2013, 17:49 |
Сообщение № 3

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

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

К сообщению приложен файл:

8777353.xls
(13.5 Kb)

Ответить

Serge_007

Дата: Вторник, 23.04.2013, 17:54 |
Сообщение № 4

Группа: Админы

Ранг: Местный житель

Сообщений: 15888

Репутация:
2623
±

Замечаний:
±

Excel 2016

Цитата (frukti4ek)

Что то у меня не получается

А что Вы пытались сделать? В файле нет ни одной формулы

ЮMoney:41001419691823 | WMR:126292472390

Ответить

frukti4ek

Дата: Вторник, 23.04.2013, 18:03 |
Сообщение № 5

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Написала

К сообщению приложен файл:

7492282.xls
(13.5 Kb)

Ответить

Serge_007

Дата: Вторник, 23.04.2013, 18:10 |
Сообщение № 6

Группа: Админы

Ранг: Местный житель

Сообщений: 15888

Репутация:
2623
±

Замечаний:
±

Excel 2016

Цитата (frukti4ek)

Написала

Я уже решение Вам выложил постом ранее

ЗЫ Макрофункции не работают на листе, об этом прямо написано в статье про них

ЮMoney:41001419691823 | WMR:126292472390

Ответить

frukti4ek

Дата: Вторник, 23.04.2013, 18:13 |
Сообщение № 7

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 117

Спасибо! буду разбираться

Ответить

JustMe

Дата: Четверг, 20.08.2015, 11:09 |
Сообщение № 8

Группа: Пользователи

Ранг: Новичок

Сообщений: 14

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Хмн… фигня какая-то. Эксель 2010 у меня почему-то не имеет ни ПОЛУЧИТЬ.РАБОЧУЮ.КНИГУ(), ни ЛИСТЫ(). Способ с суммой, конечно же, до безобразия прост, но он подходит для случаев, когда нужно посчитать количество листов в книге, а не страниц на распечатку. Поэтому позвольте поднять этого зомбия и спросить ещё раз.
Как посчитать количество страниц на распечатку?
ЗЫ. что за странный макрос прикручен к файлам в теме? случайно ни там описаны данные функции?
[moder]Хмн… фигня какая-то. Правила форума не читаны, вопрос вообще про другое, зомбиев каких-то поднимаем, короче, сплошное безобразие.
Тему закрываю.

Сообщение отредактировал _Boroda_ — Четверг, 20.08.2015, 11:14

Ответить

Источник

Как посчитать количество листов в рабочей тетради?

Вы когда-нибудь задумывались, как подсчитать количество листов в книге? Считать их по одному — скучная работа, если листов много. На самом деле нет прямой формулы для расчета количества рабочих листов. Но здесь я могу представить вам несколько полезных и удобных приемов для решения этой проблемы.

Подсчитайте количество листов в книге Функция ЛИСТЫ

Подсчитайте количество листов в книге с помощью команды Определить имя

Подсчитайте количество листов в книге с кодом VBA

Отображение количества листов в книге с помощью области навигации

Подсчитайте количество листов в книге, создав список листов

Подсчитайте количество листов в книге Функция ЛИСТЫ

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

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

Подсчитайте количество листов в книге с помощью команды Определить имя

В Excel вы можете сначала применить формулу к функции «Определить имя», а затем подсчитать количество листов.

1. Запустите книгу, в которой вы хотите подсчитать количество листов.

2. Нажмите Формула > Определить имя, см. снимок экрана:

3. И в Новое имя диалоговом окне укажите имя в поле Имя и фамилия текстовое поле, а затем введите эту формулу = ПОЛУЧИТЬ.РАБОТУ (1) & T (СЕЙЧАС ()) до Относится к текстовое поле, см. снимок экрана:

4. Затем нажмите OK чтобы закрыть это диалоговое окно и вернуться на рабочий лист, введите эту формулу = СЧЁТ (ИНДЕКС (Таблицы; 0))(CountSheets в этой формуле — это имя, которое вы определили на шаге 3, вы можете изменить его по своему усмотрению.) в пустую ячейку, см. снимок экрана:

5. И нажмите Enter key, вы получите количество ваших листов в текущей книге.

Подсчитайте количество листов в книге с кодом VBA

Если вы думаете, что приведенные выше формулы сложно запомнить, вот простой код VBA также может вам помочь.

1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.

Код VBA: подсчитайте количество листов в книге

Public Sub CountWorkSheets()
'Update 20140326
MsgBox "The total number sheets of this workbook: " & Application.Sheets.Count
End Sub

3, Затем нажмите F5 нажмите клавишу для выполнения этого кода, и появится окно с подсказкой, в котором будет указано общее количество листов, как показано на следующем снимке экрана:

Показать количество листов в книге с помощью области навигации

Если у вас есть Kutools for Excel установлен, вы, очевидно, можете просмотреть количество листов в книге с помощью Навигация панель.

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Нажмите Кутулс > Навигация для Панель навигации в левой части рабочего листа. Смотрите скриншот:

2. Затем нажмите Рабочая тетрадь и таблицы вкладка, а затем вы можете просмотреть общее количество листов в активной книге, отображаемое в нижней части панели, также вы можете просмотреть количество видимых и скрытых листов книги. Смотрите скриншот:

Работы С Нами Навигация панели, вы можете переключаться между листами, отображать все заголовки столбцов, имена диапазонов, добавлять автоматические тексты и т. д. Щелкните здесь, чтобы узнать подробности.

Панель навигации — Список и подсчет всех листов

Подсчитайте количество листов в книге, создав список листов

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

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

После бесплатная установка Kutools for Excel, пожалуйста, сделайте следующее:

1. Включите книгу, на которой нужно подсчитать листы, и нажмите Кутулс Плюс > Рабочий лист > Создать список имен листов. Смотрите скриншот:

2. Затем в появившемся диалоговом окне отметьте Содержит список гиперссылок вариант, а для удобного выбора имен листов вы можете отобразить индекс листа в 3 или 4 столбца. Смотрите скриншот:

3. Нажмите Ok, то создается рабочий лист со всеми именами листов перед всеми листами, и вам просто нужно выбрать эти имена листов и просмотреть результат подсчета в Статус bar.

Фактически, эти имена листов можно связать, вы можете щелкнуть одно имя листа, а затем быстро установить ссылку на соответствующий лист. И с Создать список имен листов, вы можете создавать связанные кнопки имен листов, проверяя Содержит кнопки и макросы вариант. Щелкните здесь, чтобы узнать больше о создании списка имен листов.

Создать список имен листов

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма …
Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон…
Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы… Предотвращение дублирования ячеек; Сравнить диапазоны…
Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор …
Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое …
Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии…
Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом …
Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF…
Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!

Источник

Помогаю со студенческими работами здесь

Изменить масштаб всех листов в книге
Люди, здравствуйте.

Как изменить масштаб всех листов в книге?
For Each wsh In wb.Worksheets
…

Как получить число листов в книге?
Пожалуйста объясните чайнику, как получить число листов в книге?
Заранее огромное спасибо!!!!

Сравнение листов в книге, и копирование значений
Помогите пожалуйста.

Есть книга с 3 листами, нужно провести сравнение и копирование. Сравнение…

Cколько листов в книге, удалить строки
Здравствуйте! Помогите, пожалуйста с проблемой.
1) Пользователь растаскивает информацию по листам…

Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:

Источник

Ill show you how to loop through all of the worksheets in a workbook in Excel using VBA and Macros.

This only takes a few lines of code and is rather simple to use once you understand it.

Here is the macro that will loop through all worksheets in a workbook in Excel:

Sub Sheet_Loop()
'count the number of worksheets in the workbook
sheet_count = ActiveWorkbook.Worksheets.Count

'loop through the worksheets in the workbook
For a = 1 To sheet_count

    'code that you want to run on each sheet
    
    'simple message box that outputs the name of the sheet
    MsgBox ActiveWorkbook.Worksheets(a).Name

Next a
End Sub

Now, Ill go through the macro step-by-step.

ActiveWorkbook.Worksheets.Count

This line is what counts the number of worksheets that are in the workbook.

The first part of the line simply sets the variable sheet_count equal to the number of sheets in the workbook:

sheet_count = ActiveWorkbook.Worksheets.Count

Now that we know how many worksheets there are, we can loop through them.

We are going to use a very simple For Next loop in this case and you can copy it directly from here into your project.

For a = 1 To sheet_count
'code that you want to run on each sheet
Next a

In the above lines we are creating a new variable a and setting it equal to 1. We then use the sheet_count variable to tell the macro when to stop looping; remember that it holds the numeric value of how many sheets there are in the workbook.

After this first line, which creates the loop, we then put all of the code that we want to run on each worksheet.

Dont forget that, at the end of the loop we still need something:

Next a

This tells the For loop that it should increment the value of the a variable by 1 each time the loop runs through a cycle or goes through a sheet. The loop will not work without this line of code at the end of it.

In the original example we also have a line of code within the For loop:

MsgBox ActiveWorkbook.Worksheets(a).Name

This line will output the name of each worksheet into a pop-up message box; it also illustrates how you can access the worksheets from within the loop.

To do anything with the sheets from within the loop, we need to access each sheet by its reference or index number. Each sheet has an index number and it always starts at 1 and increments by 1 for the next sheet in the workbook.

This is why we create the a variable and set it equal to 1 in the For loop, because the first sheet in the workbook always has an index number of 1, and we want to use a as the index number to access the worksheets.

Each time the loop runs and a is incremented by one, this allows you to access the next sheet in the workbook. This is why we needed to count how many sheets were in the workbook, so we would know when to tell the For loop to stop running because there were no more sheets.

So, we can access the worksheets from within the loop by using
ActiveWorkbook.Worksheets(index number)

Sheets(index number)

Remember that the variable a is being used as our index number in this case.

Using this method you can do anything you want with the corresponding worksheet.

And thats how you loop through all worksheets in a workbook in Excel!

Make sure to download the accompanying file for this tutorial so you can see the VBA/Macro code in action.

Subscribe for Weekly Tutorials

BONUS: subscribe now to download our Top Tutorials Ebook!

Источник

Условие задачи по перебору листов

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

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

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

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

Microsoft Excel

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

Примеры использования функций ЛИСТ и ЛИСТЫ в формулах Excel

Функции ЛИСТ и ЛИСТЫ в Excel: описание аргументов и синтаксиса

Как получить имя листа формулой в Excel

Примеры использования функции ЛИСТ и ЛИСТЫ

Обработка информации о листах книги по формуле Excel

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

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

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

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

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

Как посчитать количество листов в рабочей тетради?

Лучшие инструменты для работы в офисе

Kutools for Excel Решит большинство ваших проблем и повысит вашу производительность на 80%

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

Subscribe for Weekly Tutorials

BONUS: subscribe now to download our Top Tutorials Ebook!

А вот еще интересные статьи: