Как узнать вес листа в excel
Как посчитать количество листов в рабочей тетради?
Вы когда-нибудь задумывались, как подсчитать количество листов в книге? Считать их по одному - скучная работа, если листов много. На самом деле нет прямой формулы для расчета количества рабочих листов. Но здесь я могу представить вам несколько полезных и удобных приемов для решения этой проблемы.
Отображение количества листов в книге с помощью области навигации
Подсчитайте количество листов в книге, создав список листов
Подсчитайте количество листов в книге Функция ЛИСТЫ
Если ваша версия 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: подсчитайте количество листов в книге
3, Затем нажмите F5 нажмите клавишу для выполнения этого кода, и появится окно с подсказкой, в котором будет указано общее количество листов, как показано на следующем снимке экрана:
Показать количество листов в книге с помощью области навигации
Если у вас есть Kutools for Excel установлен, вы, очевидно, можете просмотреть количество листов в книге с помощью Навигация панель.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Нажмите Кутулс > Навигация для Панель навигации в левой части рабочего листа. Смотрите скриншот:
2. Затем нажмите Рабочая тетрадь и таблицы вкладка, а затем вы можете просмотреть общее количество листов в активной книге, отображаемое в нижней части панели, также вы можете просмотреть количество видимых и скрытых листов книги. Смотрите скриншот:
Работы С Нами Навигация панели, вы можете переключаться между листами, отображать все заголовки столбцов, имена диапазонов, добавлять автоматические тексты и т. д. Щелкните здесь, чтобы узнать подробности.
Панель навигации - Список и подсчет всех листов
Подсчитайте количество листов в книге, создав список листов
Если вы считаете, что вышеперечисленные методы неудобны, я могу предложить вам обходной путь.
Если вы установили Kutools for Excel, вы можете создать список имен всех листов, а затем выбрать их для подсчета в строке состояния.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Включите книгу, на которой нужно подсчитать листы, и нажмите Кутулс Плюс > Рабочий лист > Создать список имен листов. Смотрите скриншот:
2. Затем в появившемся диалоговом окне отметьте Содержит список гиперссылок вариант, а для удобного выбора имен листов вы можете отобразить индекс листа в 3 или 4 столбца. Смотрите скриншот:
3. Нажмите Ok, то создается рабочий лист со всеми именами листов перед всеми листами, и вам просто нужно выбрать эти имена листов и просмотреть результат подсчета в Статус bar.
Фактически, эти имена листов можно связать, вы можете щелкнуть одно имя листа, а затем быстро установить ссылку на соответствующий лист. И с Создать список имен листов, вы можете создавать связанные кнопки имен листов, проверяя Содержит кнопки и макросы вариант. Щелкните здесь, чтобы узнать больше о создании списка имен листов.
Если в один прекрасный момент вы осознаете, что ваш основной рабочий файл в Excel разбух до нескольких десятков мегабайт и во время открытия файла можно смело успеть налить себе кофе, то попробуйте пробежаться по описанным ниже пунктам - возможно один или несколько из них укоротят вашего "переростка" до вменяемых размеров и разгонят его "тормоза" :)
Проблема 1. Используемый диапазон листа больше, чем нужно
Если ваша таблица занимает 5 на 5 ячеек, то это отнюдь не означает, что Excel запоминает при сохранении этого файла только 25 ячеек с данными. Если вы в прошлом использовали какие-либо ячейки на этом листе, то они автоматически включаются в используемый диапазон (так называемый Used Range), который и запоминается при сохранении книги. Проблема в том, что при очистке используемых ячеек Excel далеко не всегда автоматически исключает их из используемого диапазона, т.е. начинает запоминать в файле больше данных, чем реально имеется.
Проверить это просто – нажмите на клавиатуре сочетание клавиш Ctrl+End и посмотрите куда переместится активная ячейка. Если она прыгнет на фактическую последнюю ячейку с данными на листе – отлично. А если вдруг ускачет сильно правее и/или ниже "в пустоту" – дело плохо: все эти ненужные пустые ячейки Excel тоже запоминает внутри файла.
Лечится это, тем не менее, достаточно легко:
- Выделите первую пустую строку под вашей таблицей
- Нажмите сочетание клавиш Ctrl+Shift+стрелка вниз – выделятся все пустые строки до конца листа.
- Удалите их, нажав на клавиатуре Ctrl+знак минус или выбрав на вкладке Главная – Удалить – Удалить строки с листа (Home – Delete – Delete rows) .
- Повторите то же самое со столбцами.
- Повторите все вышеописанные процедуры на каждом листе, где при нажатии на Ctrl+End активная ячейка перемещается не на фактическую последнюю ячейку с данными а "в пустоту" ниже и/или правее.
- Сохраните файл (обязательно, иначе изменения не вступят в силу!)
Если в вашей книге очень много таких листов, то проще, наверное, использовать короткий макрос.
Проблема 2. Используется старый формат XLS вместо новых XLSX, XLSM и XLSB
Много лет и версий подряд еще с начала девяностых в Excel был один формат файлов - XLS. Это, конечно, убирало проблемы совместимости, но, сам по себе, этот формат давно устарел и имел много неприятных недостатков (большой размер, непрозрачность внутренней структуры данных, легкую повреждаемость и т.д.)
Начиная с верии Excel 2007 Microsoft ввела новые форматы сохранения файлов, использование которых заметно облегчает жизнь и - ваши файлы:
- XLSX - по сути является зазипованным XML. Размер файлов в таком формате по сравнению с Excel 2003 меньше, в среднем, в 5-7 раз.
- XLSM - то же самое, но с поддержкой макросов.
- XLSB - двоичный формат, т.е. по сути - что-то вроде скомпилированного XML. Обычно в 1.5-2 раза меньше, чем XLSX. Единственный минус: нет совместимости с другими приложениями кроме Excel, но зато размер - минимален.
Проблема 3. Избыточное форматирование
Сложное многоцветное форматирование, само-собой, негативно отражается на размере вашего файла. А условное форматирование еще и ощутимо замедляет его работу, т.к. заставляет Excel пересчитывать условия и обновлять форматирование при каждом чихе.
Оставьте только самое необходимое, не изощряйтесь. Особенно в тех таблицах, которые кроме вас никто не видит. Для удаления только форматов (без потери содержимого!) выделите ячейки и выберите в выпадающем списке Очистить - Очистить форматы (Clear - Clear Formats) на вкладке Главная (Home) :
Особенно "загружают" файл отформатированные целиком строки и столбцы. Т.к. размер листа в последних версиях Excel сильно увеличен (>1 млн. строк и >16 тыс. столбцов), то для запоминания и обрабоки подобного форматирования нужно много ресурсов. В Excel 2013-2016, кстати, появилась надстройка Inquire, которая содержит инструмент для быстрого избавления от подобных излишеств - кнопку Удалить избыточное форматирование (Clean Excess Cell Formatting) :
Она мгновенно удаляет все излишнее форматирование до конца листа, оставляя его только внутри ваших таблиц и никак не повреждая ваши данные. Причем может это сделать даже для всех листов книги сразу.
Если вы не видите у себя в интерфейсе вкладку Inquire, то ее необходимо подключить на вкладке Разработчик - Надстройки COM (Developer - COM Addins) .
Проблема 4. Ненужные макросы и формы на VBA
Большие макросы на Visual Basic и особенно пользовательские формы с внедренной графикой могут весьма заметно утяжелять вашу книгу. Для удаления:
- нажмите Alt+F11, чтобы войти в редактор Visual Basic
- найдите окно Project Explorer’а (если его не видно, то выберите в меню View - Project Explorer)
- удалите все модули и все формы (правой кнопкой мыши - Remove - дальше в окне с вопросом о экспорте перед удалением - No):
Также код может содержаться в модулях листов - проверьте их тоже. Также можно просто сохранить файл в формате XLSX без поддержки макросов - все макросы и формы умрут автоматически. Также можно воспользоваться инструментом Очистить книгу от макросов из надстройки PLEX.
Проблема 5. Именованные диапазоны
Если в вашем файле используются именованные диапазоны (особенно с формулами, динамические или получаемые при фильтрации), то имеет смысл от них отказаться в пользу экономии размера книги. Посмотреть список имеющихся диапазонов можно нажав Ctrl+F3 или открыв окно Диспетчера имен (Name Manager) на вкладке Формулы (Formulas) :
Также вычищайте именованные диапазоны с ошибками (их можно быстро отобрать с помощью кнопки Фильтр в правом верхнем углу этого окна) - они вам точно не пригодятся.
Проблема 6. Фотографии высокого разрешения и невидимые автофигуры
Если речь идет о фотографиях, добавленных в книгу (особенно когда их много, например в каталоге продукции), то они, само-собой, увеличивают размер файла. Советую сжимать их, уменьшая разрешение до 96-150 точек на дюйм. На экране по качеству это совершенно не чувствуется, а размер файла уменьшает в разы. Для сжатия воспользуйтесь кнопкой Сжать рисунки (Compress pictures) на вкладке Формат (Format) :
Кроме видимых картинок на листе могут содержаться и невидимые изображения (рисунки, фотографии, автофигуры). Чтобы увидеть их, выделите любую картинку и на вкладке Формат (Format) нажмите кнопку Область выделения (Selection Pane) .
Для удаления вообще всех графических объектов на текущем листе можно использовать простой макрос:
Проблема 7. Исходные данные сводных таблиц
По-умолчанию Excel сохраняет данные для расчета сводной таблицы (pivot cache) внутри файла. Можно отказаться от этой возможности, заметно сократив размер файла, но увеличив время на обновление сводной при следующем открытии книги. Щелкните правой кнопкой мыши по сводной таблице и выберите команду Свойства таблицы (Pivot Table Properties) - вкладка Данные (Data) - снять флажок Сохранять исходные данные вместе с файлом (Save source data with file):
Если у вас несколько сводных таблиц на основе одного диапазона данных, то сократить размер файла здорово помогает метод, когда все сводные таблицы после первой строятся на основе уже созданного для первой таблицы кэша. В Excel 2000-2003 это делается выбором переключателя на первом шаге Мастера сводных таблиц при построении:
В Excel 2007-2016 кнопку Мастера сводных таблиц нужно добавлять на панель вручную - на ленте такой команды нет. Для этого щелкните по панели быстрого доступа правой кнопкой мыши и выберите Настройка панели быстрого доступа (Customize Quick Access Toolbar) и затем найдите в полном списке команд кнопку Мастер сводных таблиц (PivotTable and PivotChart Wizard) :
Проблема 8. Журнал изменений (логи) в файле с общим доступом
Если в вашем файле включен общий доступ на вкладке Рецензирование - Доступ к книге (Review - Share Workbook) , то внутри вашего файла Excel на специальном скрытом листе начинает сохраняться вся история изменений документа: кто, когда и как менял ячейки всех листов. По умолчанию, такой журнал сохраняет данные изменений за последние 30 дней, т.е. при активной работе с файлом, может запросто занимать несколько мегабайт.
Мораль: не используйте общий доступ без необходимости или сократите количество дней хранения данных журнала, используя вторую вкладку Подробнее (Advanced) в окне Доступ к книге. Там можно найти параметр Хранить журнал изменений в течение N дней (Keep change history for N days) или совсем отключить его:
Проблема 9. Много мусорных стилей
Про эту пакость я уже подробно писал ранее в статье о том, как победить ошибку "Слишком много форматов ячеек". Суть, если кратко, в том, что если вы разворачиваете на вкладке Главная список Стили ячеек (Home - Cell Styles) и видите там очень много непонятных и ненужных стилей, то это плохо - и для размера вашего файла Excel и для его быстродействия.
Удалить ненужные стили можно с помощью макроса или готовой команды из надстройки PLEX.
Проблема 10. Много примечаний
Примечания к ячейкам, конечно, не самый вредный момент из всех перечисленных. Но некоторые файлы могут содержать большое количество текста или даже картинок в примечаниях к ячейкам. Если примечания не содержат полезной для вас информации, то их можно легко удалить с помощью команды на вкладке Главная - Очистить - Очистить примечания (Home - Clear - Clear Comments) .
Как посчитать количество видимых листов в книге?
В большой книге с несколькими листами некоторые из листов видны, а некоторые скрыты, и в некоторых случаях вам может потребоваться подсчитать общее количество видимых листов книги, как вы можете подсчитать их в коротком время? Теперь я предлагаю вам несколько уловок, позволяющих быстро подсчитать количество видимых листов в книге.
Подсчитайте количество видимых листов / скрытых листов / всех листов с помощью Kutools for Excel
Подсчитайте количество видимых листов только с VBA
В Excel, кроме подсчета видимых листов по одному, есть VBA, который поможет вам быстро подсчитать количество видимых листов.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Нажмите Alt + F11 ключи для включения Microsoft Visual Basic для приложений окно, затем щелкните Вставить > Модули чтобы открыть новый модуль.
2. Вставьте ниже код VBA в окно модуля.
VBA: подсчитать количество видимых листов книги
3. Нажмите Run нажмите кнопку, затем появится диалоговое окно, в котором будет указано количество видимых листов.
Подсчитайте количество видимых листов / скрытых листов / всех листов с помощью Kutools for Excel
Если вы не знакомы с VBA, вы можете использовать Kutools for ExcelАвтора Панель навигации для быстрого просмотра количества видимых листов, скрытых листов и всех листов.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Включите книгу, в которой нужно подсчитать количество видимых листов, и нажмите Кутулс > Навигация. Смотрите скриншот:
2. Затем Панель навигации появляется слева от текущего листа, и щелкните Рабочая тетрадь и лист вкладку, чтобы перейти к Рабочая тетрадь и лист раздел. Вы можете просмотреть количество видимых листов, скрытых листов и всех листов, включая видимые и скрытые листы, в нижней части панели. Смотрите скриншот:
Если вы хотите скрыть панель навигации, просто нажмите Кутулс > Навигация чтобы скрыть это.
Kutools for ExcelАвтора Панель навигации является мощным, с его помощью вы можете просматривать список листов, список имен диапазонов, список заголовков столбцов, а также добавлять диапазоны, изображения, формулы в виде автоматических текстов, кроме того, вы можете найти и заменить слово на выбранных листах. Щелкните здесь, чтобы узнать больше о панели навигации.
Как проверить размер каждого рабочего листа книги?
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу .
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Проверьте размер каждого рабочего листа с помощью кода VBA
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
С помощью следующего кода VBA вы можете быстро получить размер каждого рабочего листа в своей книге. Пожалуйста, сделайте так:
1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.
2. Нажмите Вставить > Модулии вставьте следующий код в Окно модуля.
Код VBA: проверьте размер каждого листа в книге p>
3. Затем нажмите F5 ключ для выполнения этого кода и новый рабочий лист с именем KutoolsforExcel будет вставлен в текущую книгу, которая содержит имя каждого рабочего листа и размер файла, а единицей измерения является бит. Смотрите скриншот:
Проверьте размер каждого рабочего листа с помощью Kutools for Excel
Если у вас есть Kutools for Excel, С его Разделить книгу утилиту, вы можете разделить всю книгу на отдельные файлы, а затем перейти в конкретную папку, чтобы проверить размер каждого файла.
Kutools for Excel включает более 300 удобных инструментов Excel. Бесплатная пробная версия без ограничений в течение 30 дней. Получить сейчас.
После установки Kutools for Excel выполните следующие действия:
1. Откройте книгу, в которой вы хотите проверить размер каждого листа, и нажмите Предприятие > Рабочие инструменты > Разделить книгу, см. снимок экрана:
2. В Разделить книгу диалоговое окно, проверьте все рабочие листы и нажмите Трещина кнопку, а затем укажите папку для размещения файлов новой книги. Смотрите скриншоты:
3. И затем каждый рабочий лист вашей текущей книги будет сохранен как отдельный файл Excel, вы можете перейти в свою конкретную папку, чтобы проверить размер каждой книги.
Статьи по теме:
Лучшие инструменты для работы в офисе
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
- Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма .
- Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон .
- Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы . Предотвращение дублирования ячеек; Сравнить диапазоны .
- Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор .
- Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули , Флажки и многое другое .
- Извлечь текст , Добавить текст, Удалить по позиции, Удалить пробел ; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии .
- Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом .
- Комбинируйте книги и рабочие листы ; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов ; Пакетное преобразование xls, xlsx и PDF . Pivot Table Grouping by week number, day of week and more. Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name . -->
- Более 300 мощных функций . Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint , Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Эй, Бен, не могли бы вы перевставить всю строку текста с добавленными элементами № 2 и № 4 из вашего электронного письма? МОИ знания VBA довольно ограничены, и я не уверен, где именно добавить их в цикл For. В моей книге есть несколько скрытых листов, и во время выполнения макроса происходит сбой. Спасибо, Боб
У меня есть Excel .xlsx, который весит 47 МБ. Я хотел бы знать, какие части книги являются самыми тяжелыми и работают с ними, так как я не добился большого прогресса вручную, пытаясь определить и уменьшить размер файла. есть ли способ узнать findout, сколько Весов каждого листа в книге Excel?
не хотел отвечать на свой вопрос, но потом узнал, как это сделать. Так вот в чем дело:
сначала сделайте копию файла, переименуйте его .zip, откройте его и перейдите к [zipfile]\xl\worksheets\ там вы увидите .xml-файлы с их несжатым размером. Например:
определить самый большой лист, в данном случае sheet6.xml
открыть файл [zipfile]\xl\_rels\workbook.xml.rels и узнайте соответствующий идентификатор связи ранее определенного листа.XML. В этом случае r: id на sheet6.xml 10:
открыть файл [zipfile]\xl\workbook.xml . Используя rid большой .xml файл вы получили выше и структура workbook.xml (показано ниже), узнайте имя большого листа в книге.
47 NB xlsx файл очень редко, если он не имеет много изображений в нем. Если у вас нет, шанс велик, что у вас есть один из тех экспортированных файлов, которые пошли не так.
в основном, что происходит в том, что, когда файл xlsx экспортируется, открывается с excel, а затем сохраняется снова, он сохраняет файл с большим количеством пустых строк и столбцов. Вы можете определить это, видя огромные полосы прокрутки (например: scrollknob крошечный).
чтобы избежать этого, сделайте выбор из сначала пустой столбец полностью тоже последний (используя ячейки заголовка, не выбирая отдельные ячейки), затем на заголовке щелкните правой кнопкой мыши и выберите Удалить. Сделайте то же самое для строк (которых много. ), а затем сохраните файл.
Excell должен теперь сохранить файл, и если это было проблемой, он будет идти от 47 МБ до, может быть, 2 Мб.
Я не могу комментировать (недостаточно очков), но я использовал это в дополнение к удивительному ответу, предоставленному S_A, чтобы получить мой раздутый файл с 75 МБ до 1,7 МБ.
Я никогда не слышал о "Убрать Лишние Ячейки" функции раньше. (Кстати, это часть Excel, а не сторонний продукт. )
извините за отправку этого ответа, но я хотел бы рассказать вам об этом полезном инструменте и поблагодарить вас за полезное обсуждение! Надеясь получить достаточно очков когда-нибудь, чтобы участвовать.
Edit: по рекомендации robinCTS, я собираюсь быстро показать, как включить / доступ к этому инструменту из Excel
Читайте также: