Многоликие Представления

Представления (Custom Views) в Microsoft Excel - это весьма удобный и практичный инструмент, который, почему-то, обделен вниманием большинства пользователей. За 10 с лишним лет тренингов и консультирования я видел очень мало людей (одной руки хватит, пожалуй), которые бы его использовали. А зря.

Давайте восстановим справедливость. Он вам понравится, вот увидите.

Итак, в чем суть?

Представление - это сохраненный вариант просмотра таблицы на заданном листе Microsoft Excel. Когда вы создаете представление, то в нем запоминаются:

  • положение текущей ячейки
  • какие строки-столбцы скрыты и какие видны
  • какие группы строк-столбцов свернуты и какие развернуты
  • условия фильтрации
  • параметры печати

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

Управлять представлениями можно с помощью кнопки Представления (Custom Views) на вкладке Вид (View), которая открывает соответствующее окно:

Окно представлений в Excel

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

Другой, более быстрый способ - это вынести выпадающий список с представлениями на панель быстрого доступа в левый верхний угол окна Excel. Для этого нажмите Файл - Параметры - Панель быстрого доступа (File - Options - Quick Access Toolbar), затем в выпадающем списке сверху выберите Все команды (All Commands) и добавьте список Представления (Custom Views) на панель:

Добавление списка представлений на панель быстрого доступа Excel

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

Пример 1. Запоминаемые наборы фильтров

Предположим, что у нас есть таблица со списком сотрудников:

Таблица с фильтром

... и мы часто ищем в этой таблице с помощью фильтров одних и тех же людей, например:

  • всех мужчин / женщин
  • всех начальников отделов
  • всех пенсионеров и т.д.

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

Создание представления

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

Список представлений на панели быстрого доступа

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

Пример 2. Подробный или общий отчет

У вас есть отчет с данными по доходам за год:

Отчет по доходам

Можно сделать два представления для удобной работы с ним - подробное (видно все данные) и короткое (видно только итоги по кварталам, месяцы скрыты). Можно воспользоваться группировкой, или макросом для скрытия строк-столбцов, а можно все сделать проще - представлениями.

Логика действий та же:

  1. Выделяем по-очереди столбцы с месяцами D:F, H:J, L:M, P-R и скрываем их правой кнопкой мыши - Скрыть (Hide) или сочетанием клавиш Ctrl+0.

  2. Создаем представление через вкладку Вид - Представления - Добавить. Также можно просто вписать имя нового представления в выпадающий список на панели быстрого доступа и нажать на Enter.

Теперь можно быстро переключаться между общим и детальным вариантом с помощью все того же выпадающего списка в левом верхнем углу окна Excel:

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

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

Переключение между режимами План и Факт

Примечания

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

Ссылки по теме



28.06.2016 12:44:27
И на самом деле приятная штука:) спасибо!
29.06.2016 22:16:10
Работает только в одном файле, в новом надо создавать заново?
03.07.2016 18:42:28
Да, конечно - привязывается же к конкретной таблице на конкретном листе.
03.07.2016 18:53:07
А жалко, что не только к вкладке, каждый раз создавать новый.
04.07.2016 17:27:57
Если у нового файла та же структура, можно прописать представления макросом.
04.07.2016 07:59:41
Как для меня, то это самое большое ограничение :(
"Представления несовместимы с "умными таблицами". Если в вашей книге на любом листе создана хоть одна "умная таблица", то представления перестают работать."
04.07.2016 08:21:01
Николай, здесь есть "подводные камни". Помню, пытался разобраться, почему документ "разбух". Оказалось, что если файл расшарен, и каждый пользователь сделает сохранение, то представления создадутся автоматически. Когда удалил порядка 20 штук, большой объем документы сразу пропал
13.07.2016 18:21:25
Николай у меня в меню ВИД представления есть но они отключены.
наверное я что-то нащелкал.
то есть невозможно создать представления.
не могли бы подсказать как их включить.
спасибо!
19.07.2016 07:13:44
При добавлении на панель быстрого доступа появляется кнопка для создания новых представлений, но выпадающего списка не появляется. Что-то делаю не так, или дело в том, что Excel 2013?
27.07.2016 08:42:26
Татьяна, следуйте инструкции из этой статьи и у Вас всё получится!
30.07.2016 10:27:30
Спасибо, конечно. Но уже разобралась. Действительно, в 2016 Excel появляется выпадающий список, а в 2013 - только кнопка для создания.
25.07.2016 17:58:09
Очень удобно, спасибо!
Но только для своего компьютера, если я правильно понимаю. Если переслать файл кому-то, то объяснить, как и где искать преднастроенные Представления будет проблематично. Или есть возможность показать их в виде кнопки или типа того, которая будет видна любому пользователю файла, независимо от его настроек панели быстрого доступа?
27.07.2016 08:39:38
Дмитрий, при добавлении списка на панель qat вы можете выбрать использовать эту настройку на уровне приложения или книги.
27.07.2016 12:47:04
Спасибо! Но, если не ошибаюсь, это будет работать только на моем Excel. Если я перешлю файл коллеге, у него quick access toolbar настроен по-своему, соответственно, для выбора представлений ему придется лезть в меню, что не удобно. Или я что-то путаю?
10.08.2016 17:10:41
ну вот почему я только сегодня начала изучать Ваши приемы:cry:
Спасибо!
24.08.2016 17:47:05
Это конечно круто, но я стал играться с этой штукой в большом файле, где есть умная таблица. Создал несколько представлений, а теперь не могу их удалить, т.к. кнопка на вкладке ВИД не активна. Есть какой-нибудь программный способ их удалить?
12.10.2016 15:24:00
Нашел наводку на эту проблему: "If any worksheet in the workbook contains an Excel table, the Custom Views command will not be available anywhere in the workbook."
Т.е. суть: как только мы в книгу на любой из листов добавляем форматирование диапазона как "умной таблицы", пропадает возможность делать представления, т.к. в "умных" такую роль играют срезы. Жаль, конечно, что во всей книге блочатся представления, видимо это программная заморочка.
Так что, пожалуй, самый простой вариант чтобы удалить ненужные представления, надо преобразовать все "умные таблицы" в обычные диапазоны ("Работа с таблицами-Преобразовать в диапазон"). После удаления представлений можно и "умные таблицы" вернуть.
12.10.2016 16:37:23
Еще вариант, когда много всякой ерунды в большом файле:
Качаем XML Notepad (оф.ссылка Microsoft), открываем наш excel файл любым архиватором (например, 7zip). Заходим в папку "xl" и открываем в блокноте файл "workbook.xml". В блокноте видим структуру файла, находим папку "customWorkbookViews" и удаляем. Сохраняем. Обновляем архив и все.
12.10.2016 18:19:01
О, а вот этот вариант мне нравится. Т.к. преобразовать таблицу в диапазон невозможно, т.к. на нее слишком много чего зашито. Спасибо!
18.10.2016 11:12:31
Кнопку "Представление" нашла, но она не активна. Как исправить? Сама нашла.:)
24.10.2016 09:09:09
добрый день! Подскажите а есть ли прием который совместит функционал Представления с умной сортировкой по столбцу. (работа с прайс листом на складе с адресным хранением).  Спасибо.
27.10.2016 10:38:19
Класс!
10.11.2016 18:19:56
Здравствуйте, Николай. Скажите, пожалуйста, если сделать фильтрацию по алфавиту и сделать представление, оно будет работать? А то у меня не получается, чтоб также вышло в алфавитном порядке.Заранее благодарю.
01.02.2017 17:44:37
Николай, добрый день.
Настраиваю представление на защищенном листе, при выборе представления, выдает ошибку.
Я правильно понимаю, что представления с защитой листов не работают?