Печать скрытых листов excel

Обновлено: 07.07.2024

Автоматизация вычисления промежуточных итогов Перемещение курсора при вводе данных в Excel Печать скрытых листов в 1-2-3 Автоматизация вычисления промежуточных итогов С помощью программы Excel я веду списки данных о спортивных командах, и мне нужно периодически выполнять ряд расчетов.

Автоматизация вычисления промежуточных итогов

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

Можно ли организовать автоматическую выдачу отчета о промежуточных итогах, не переделывая все вручную каждый месяц?

Сэнди Джерниган,
г. Манкс-Корнер, шт. Южная Каролина

Для решения этой типичной задачи подсчета промежуточных итогов существует несколько путей. Можно использовать сводные таблицы Excel, но эффективнее всего применить функцию подстановки значений в формулы (аналогичные функции есть во всех популярных табличных процессорах, появившихся после 1-2-3 версии 1A) в сочетании с функцией вычисления среднего в базе данных, которая в Excel называется DAVERAGE ("ДСРЗНАЧ"), а в 1-2-3 и Quattro Pro - @DAVG).

Таблица подстановки показывает, как отражаются изменения в ячейках таблицы на результатах вычисления по одной или нескольким формулам. Когда ячейка содержит критерий для привязки к базе данных (ограничивает диапазон используемых значений несколькими записями), результат вычислений каждый раз относится к новому набору данных. В Excel таблица подстановки представляет собой формулу, обрабатывающую массив и автоматически вычисляющую результат заново в случае изменения данных. В 1-2-3 и Quattro Pro для пересчета результата необходимо дать соответствующую команду.

Чтобы решить задачу в Excel, выполните следующие действия, сверяясь с рис. 1 (некоторые операции специфичны именно для Excel).

1. Введите необходимые данные в диапазоне A1:D12 (в столбце A записаны имена игроков, в столбце B - названия соответствующих команд, столбцы C и D показывают, сколько очков набрал игрок в первый и второй раз).

2. Введите метки для ячейки ввода (F1) и для таблицы подстановки (F4:F7).

3. Вызовите меню Insert·Name· Define ("Вставка·Имя·Присвоить"), задайте диапазоны для критерия - F1:F2 и для базы данных - =offset($A$1,0,0,counta($A:$A),4).

4. В ячейке G4 введите формулу =daverage(database,"Score 1",criteria), а в ячейке H4 - =daverage(database,"Score 2",criteria). Сформатируйте ячейку G4 таким образом, чтобы результат вычисления формулы заменялся на метку "Score 1" ("Счет 1" - число очков, набранных в первый раз). Для этого выделите ячейку, дайте команду Format·Cells·Custom ("Формат·Ячейки·Все форматы"), введите строку "Score 1" (включая кавычки) и нажмите OK. Аналогично сформатируйте ячейку H4.

5. Создайте таблицу данных: выделите диапазон F4:H7, дайте команду Data·Table ("Данные·Таблица подстановки"), в поле Column Input Cell ("Подставлять значения по столбцам в") укажите F2, нажмите OK.

Формула в ячейке G4 вычисляет среднее значение данных в столбце C, соответствующих критерию в ячейке F2. Формула в H4 делает то же самое для столбца D. Таблица данных (F4:H7) подставляет значения из диапазона F5:F7 в ячейку F2. В результате диапазон G5:G7 содержит средние значения параметра Score 1 для каждой из трех команд, а диапазон H5:H7 - средние значения параметра Score 2 для этих же команд.

В Quattro Pro использовать имя динамически меняющегося диапазона нельзя, поэтому придется присвоить имя database диапазону A1..D12. В ячейке G4 введите формулу @davg(database,2,criteria). Число 2 соответствует смещению столбца C, содержащего значения параметра Score 1. В ячейке H4 введите формулу @davg(database,3,criteria). Здесь число 3 соответствует смещению столбца D, содержащего значения параметра Score 2. Сформатируйте ячейки так, чтобы они показывали метки Score 1 и Score 2: по очереди выделите ячейки щелчком правой кнопки мыши, дайте команду Block Properties ("Свойства блока"), перейдите к закладке Numeric Format ("Числовой формат"), выберите опцию User Defined ("Определяется пользователем") и в поле Formats Defined ("Определенные форматы") введите либо T"Score 1", либо T"Score 2" (включая одиночные кавычки). Чтобы создать таблицу данных, выделите диапазон F4:H7, дайте команду Tools·Numeric Tools·What-If ("Инструменты·Числовые инструменты·Что, если"). В поле Input Cell ("Рабочая ячейка") укажите F2, нажмите кнопку Generate ("Создать"), затем Close ("Закрыть").

В 1-2-3 следует присвоить имя database диапазону A1..D12, в ячейке G4 ввести формулу @davg(database,"Score 1",criteria), а в ячейке H4 - формулу @davg(database, "Score 2",criteria). Чтобы создать таблицу данных, нужно выделить диапазон F4..H7, дать команду Range·Analyze·What-If Table ("Диапазон·Анализ·Таблица "что, если""). Число переменных укажите равным 1, в поле Input Cell 1 ("Рабочая ячейка 1") укажите F2 и нажмите OK.

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

Мы с братом купили себе компьютер, и я по его (брата) совету перешел с 1-2-3 на Excel 95. При нажатии для ввода значения, курсор перемещается на одну ячейку вниз. Мой брат находит эту функцию полезной, а меня она раздражает. Нельзя ли ее отключить?

Тайрон Пуссейнт,
г. Батон-Руж, шт. Луизиана

Хороший вопрос. А ответ подсказал другой наш читатель, Чак Пейсек из г. Парсонс (шт. Канзас). Нужно вызвать меню Tools·Options ("Сервис·Параметры"), перейти к закладке Edit ("Правка") и снять пометку с опции Move Selection after Enter ("Переход к другой ячейке после ввода").

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

Вы с братом работаете с Windows 95, а значит, каждый из вас может настроить для себя индивидуальную конфигурацию. В Панели управления Windows 95 вызовите утилиту Passwords ("Пароли"), перейдите к закладке User Profiles ("Конфигурации"), включите опцию Users can customise their preferences ("Каждый пользователь может иметь свою систему настроек") и нажмите OK. После этого при запуске Windows система будет запрашивать у вас пароль и идентификатор пользователя. Пусть каждый из вас создаст собственную конфигурацию системы, в частности, по-своему настроит Excel с помощью меню Tools·Options. Поскольку эта программа специально предназначена для работы в Windows 95, то в каждой конфигурации произведенные настройки станут значениями по умолчанию.

Печать скрытых листов в 1-2-3

У меня есть документ 1-2-3, содержащий три листа. Чтобы не дать посторонним возможность менять формулы и данные, я сделал два листа скрытыми, да к тому же защитил файл паролем. Но оказалось, что распечатать весь документ я могу, только если сделаю скрытые листы видимыми. Нельзя ли распечатывать данные со скрытых листов?

Джон Повеска,
Лорейн, Огайо

В 1-2-3 нет стандартного средства, чтобы распечатать отчет, содержащийся на скрытых листах, поэтому я написал макрос (его можно найти в файле 5HSS2.WK4 в службе PC World Online), который решает эту задачу. Макрос снимает парольную защиту, раскрывает скрытые листы и распечатывает по одному диапазону с каждого из них. Затем он снова делает листы скрытыми и восстанавливает парольную защиту. Кроме того, в макросе есть средство обработки ошибок, обеспечивающее безопасность в случае сбоя. Сам макрос необходимо поместить на скрытом листе, чтобы никто не смог узнать пароль.

Чтобы увидеть макрос в действии, создайте документ из четырех листов. Двойным щелчком активизируйте лист D и назовите его Macros . Затем введите команды, как показано на рис. 2. С помощью меню Range·Name ("Диапазон·Имя") присвойте ячейке B1 имя p, а ячейке B9 - имя p_err. На листе B создайте имя firstrange, а на листе C - secondrange . Перейдите к листу A и выберите инструмент "кнопка", чтобы создать кнопку для вызова макроса. В диалоговом окне Assign to Button ("Связывание макроса с кнопкой"), в поле Assign macro from ("Местоположение макроса") выберите опцию Range ("Диапазон"). В списке Existing named ranges ("Именованные диапазоны") укажите имя p.

Для запуска макроса перейдите к листу A и нажмите кнопку или клавиши +P. Убедившись, что макрос работает, активизируйте его лист и в списке стилей выберите команду Hide ("Скрытый"), нажмите кнопку Sheet ("Лист"), а затем OK. Лист будет спрятан.

Вот как работают команды макроса.

указывает, что в случае сбоя (или нажатия пользователем клавиш +
) во время выполнения макроса следует продолжить работу, начиная с команды в ячейке B9, чтобы снова спрятать конфиденциальные данные.

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

делает лист A текущим.

делает выполнение оставшихся команд макроса незаметным для пользователя.

В этой статье представлены несколько способов шаг за шагом показать все скрытые листы и рабочие листы в Excel.

Показывать все скрытые листы по одному в Excel
Показать все скрытые листы с помощью кода VBA
Показать все скрытые листы Kutools for Excel одним щелчком мыши
Показать все скрытые листы с помощью функции «Переключить видимость скрытых листов»
Показать все очень скрытые рабочие листы Kutools for Excel

Показывать все скрытые листы по одному в Excel

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

1. Нажмите Главная > Формат > Скрыть и показать > Показать. Смотрите скриншот:
Внимание: Вы также можете щелкнуть правой кнопкой мыши любое имя листа на панели вкладок Лист и выбрать Unhide из контекстного меню.

2. Теперь все скрытые листы перечислены в Unhide диалоговое окно. Выберите лист, который вы хотите показать, и нажмите кнопку OK кнопку.

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

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

Переключить видимость скрытых листов - один щелчок, чтобы показать все скрытые листы, и один щелчок, чтобы восстановить скрытые.

С помощью удобной кнопки переключения Worksheets предоставленный Kutools for Excel, вы можете одним щелчком мыши отобразить все скрытые рабочие листы в активной книге и щелкнуть его еще раз, чтобы переключить все скрытые рабочие листы на невидимые. Полнофункциональная бесплатная 30-дневная пробная версия!

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Показать все скрытые листы с помощью кода VBA

Следующий короткий код VBA также может помочь вам отобразить все скрытые листы одновременно.

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

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

3, нажмите F5 ключ для запуска этого макроса. И скрытые листы отобразятся сразу.

Показать все скрытые листы Kutools for Excel одним щелчком мыши

Kutools для Excel Показать все скрытые листы Функция может помочь вам легко отобразить все скрытые листы одним щелчком мыши.

Нажмите на Kutools > Показать / Скрыть > Показать все скрытые листы, см. снимок экрана:

Тогда все скрытые листы будут показаны сразу. Смотрите скриншот:

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

С помощью этого удобного Показать все скрытые листы вы можете отобразить все скрытые листы одним щелчком мыши в Excel. Получите бесплатную пробную версию!

Показать все скрытые листы с помощью функции «Переключить видимость скрытых листов»

Kutools for Excel также предоставляет удобный Worksheets (Переключить видимость скрытых листов), чтобы отображать или скрывать все скрытые листы одним щелчком мыши в активной книге.

Под Kutools вкладку, нажмите Рабочий лист кнопку, чтобы отобразить все скрытые листы, и нажмите кнопку Worksheets еще раз, чтобы восстановить скрытый статус.

Worksheets переключает видимость рабочих листов. Один щелчок, чтобы отобразить все скрытые листы, и другой щелчок, чтобы скрыть их снова. Получите бесплатную пробную версию!

Показать все очень скрытые рабочие листы Kutools for Excel

Иногда вышеуказанные методы не могут отобразить скрытые рабочие листы. Это потому, что эти рабочие листы очень скрыты. В этой ситуации вы можете применить функцию «Скрыть / отобразить книги и таблицы» в Kutools for Excel, чтобы быстро отобразить их.

1. Нажмите Kutools > Показать спрятать > Скрыть / отобразить книги и таблицы.

2. В диалоговом окне «Скрыть / отобразить рабочие книги и листы» щелкните, чтобы выбрать активную книгу, и щелкните значок Показать все в Листы раздел. Смотрите скриншот.

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

Скрыть / отобразить книги и таблицы скрыть или отобразить сразу несколько открывающихся книг и их рабочих листов сразу, в том числе очень скрытые. Получите бесплатную пробную версию!

Поговорим как скрывать листы, а также как показывать скрытые листы в Excel, в том числе разберем как можно отобразить очень скрытые листы с помощью Visual Basic.

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

Также возникает и обратная необходимость — в частичном или полном отображении скрытых листов. В общем давайте рассмотрим различные способы реализации как скрытия, так и отображения листов.

Как скрыть лист в Excel?

Процедура скрытия листа достаточно простая — в нижней панели щелкаем правой кнопкой мыши по названию листа, который хотим сделать невидимым, и в выпадающем меню выбираем команду Скрыть:

Если нужно спрятать сразу несколько листов, то удерживая клавишу Ctrl выделяем нужные листы с помощью мышки и повторяем вышеописанные действия (щелкаем правой кнопкой мыши по любому из выделенных листов и далее нажимаем Скрыть).

Как показать скрытый лист в Excel?

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

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

Как сделать очень скрытый лист в Excel?

Параметры отображения любого листа в Excel мы можем настроить посредством Visual Basic. Переходим в редактор Visual Basic (на панели вкладок выбираем Разработчик -> Код -> Visual Basic или воспользуемся комбинацией клавиш Alt + F11):

В левой верхней части окна в списке выбираем нужный нам лист и чуть ниже подробно смотрим в блок с настройками (если он изначально не отображается, то уже в панели вкладок Visual Basic выбираем View -> Properties Window и блок появится).

За отображение листа в параметрах отвечает свойство Visible и как мы видим всего есть 3 варианта:

xlSheetVisible. Лист открыт и отображается во вкладках;
xlSheetHidden. Лист скрыт, но отображается в списке невидимых листов;
xlSheetVeryHidden. Лист скрыт и нигде не отображается.

Таким образом, чтобы скрыть лист средствами Visual Basic указываем значение параметра xlSheetHidden, а чтобы показать устанавливаем xlSheetVisible.

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

Вернемся к нашему примеру.

Так как мы в начале скрывали Лист1, то для него установлена видимость xlSheetHidden, а нас сейчас интересует как раз 3 вариант. Выбираем его и теперь в списке скрытых листов пусто (кнопка в данном случае не активна, хотя мы знаем, что очень скрытые листы в книге на самом деле есть):

Теперь пользователь не увидит такой супер скрытый лист в интерфейсе Excel, однако он все же сможет его обнаружить если зайдет в редактор Visual Basic.

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

Автоматизация открытия скрытых листов с помощью макроса

Однако процесс открывания каждого листа вручную (либо посредством Visual Basic) может отнять достаточно много времени, так как нужно будет проделывать эту процедуру для каждого скрытого листа.

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

В редакторе Visual Basic создаем новый модуль (Insert -> Module) и вставляем туда следующий код (добавил детальные пояснения напротив кода):

Автоматическое скрытие определенного рабочего листа при открытии книги с кодом VBA

Если вы хотите скрыть один конкретный лист при открытии книги, вы можете применить следующий код VBA.

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

2. Слева Проект-VBAProject панели дважды щелкните значок ThisWorkbook , чтобы открыть пустой модуль, а затем скопируйте и вставьте в модуль следующий код:

Код VBA: скрыть определенный лист при открытии книги:

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

3. Затем сохраните и закройте это окно кода и нажмите Файл > Сохранить как чтобы сохранить эту книгу как Excel Macro-Enabled Workbook формат, см. снимок экрана:

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

Автоматическое скрытие некоторых конкретных листов при открытии книги с кодом VBA

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

1. Сначала вы должны перечислить имена листов, которые вы хотите скрыть при открытии, в списке рабочего листа, а затем перейти к Поле имени дать им название диапазона -SheetsToHide, и нажмите Enter ключ, см. снимок экрана:

2. Затем удерживайте ALT + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

3. Слева Проект-VBAProject панели дважды щелкните значок ThisWorkbook , чтобы открыть пустой модуль, а затем скопируйте и вставьте в модуль следующий код:

Код VBA: скрыть некоторые конкретные рабочие листы при открытии книги:

Внимание: В приведенном выше коде SheetsToHide - это имя диапазона, которое вы создали на шаге 1.

4. Затем сохраните и закройте это окно кода и нажмите Файл > Сохранить как чтобы сохранить эту книгу как Excel Macro-Enabled Workbook формат. С этого момента, когда вы откроете эту книгу в следующий раз, указанные рабочие листы будут автоматически скрыты.

Читайте также: