Впервые сам инструмент PowerView появился в Excel в 2013 версии и присутствовал на вкладке Вставка [Insert]. Обладателям 2016 версии PowerView доступен, однако разработчик решил поиграть с пользователем в очередные прятки. Удивительно, что другие надстройки, начинающие с Power отображается, а именно эта прячется. Хотим работать с PowerView? Значит, придется немного потрудиться.

Чтобы приступить к работе с PowerView, надо настроить отображение команды, например, на уже привычной вкладке Вставка [Insert]. Для этого:

1. Выбрать Файл [File], Параметры [Options], раздел Настройка ленты [Customize Ribbon]. Раскрыть список Выбрать команды [Choose commands from] и выбрать Все команды [All Commands], найти PowerView.

2. В группе Настроить ленту [Customize Ribbon], выбрать Вставка [Insert], нажать кнопку Создать группу [New Group]. Появляется Новая группа (настраиваемая) [New Group (Custom)], выделив которую и нажав в окне кнопку Переименование [Rename], следует ввести более понятное имя, например, PowerView. Нажать OK.

3. Выделить в списке Основных вкладок [Main Tabs] созданную группу PowerView (настраиваемая), выделить команду PowerView в списке команд и нажать Добавить >> [Add>>]

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

4. Закрыть окно параметров Excel, нажав ОК.

На вкладке Вставка [Insert] появилась созданная группа и кнопка:

Использование функции ДВССЫЛ значительно ускоряет процесс получения данных как на одном, так и на разных листах. Это так удобно – написать одну формулу, задать в ней изменяющиеся или постоянные параметры, такие как название листа, адрес ячейки или диапазона, скопировать формулу и получить результаты!

Сперва многим кажется не привычным – “сочинять” адрес ячейки или диапазона, но вскоре этот барьер преодолевается. И одна из сложностей, с которой сталкиваются пользователи, – это наличие в названии листов пробелов, дефисов и т.д. Однако, и это не может служить проблемой, стоит только вспомнить/понять как Excel обозначает такие ссылки и проблема решена.

Адрес ячейки с другого листа выглядит в общем виде так: ИмяЛиста!Ячейка, если имя листа написано с какими-нибудь разделителями, то имя листа заключается в апострофы: ‘ИмяЛиста’!Ячейка.

Поэтому в функции ДВССЫЛ [INDIRECT] простая сцепка названия листа (значение ячейки A2) и ячейки C1 дает результат только там, где название города написано без каких-либо разделителей:

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

Поэтому, чтобы исправить данную ошибку, следует принудительно заключить название листа в апострофы:

Таким образом, более универсальная формула для работы с именами листов будет:

=ДВССЫЛ(“‘”&A2&”‘!C1”) или =INDIRECT(“‘”&A2&”‘!C1”)

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

Однако, если настройки будут изменены, то всех этих удобств можно и не увидеть вовсе – форматирование не распространяется на добавленное, формулы не копируются…

Как быть? Конечно следует изменить настройки и работать как и прежде.

1-я настройка:  Имя поля против адреса ячейки.

1. Выбрать Файл [File], Параметры [Options].
2. В разделе Формулы [Formulas]в группе Работа с формулами [Working with formulas], выбрать Использовать имена таблиц в формулах [Use table name in formulas].
3. OK.

 2-я настройка: Автоматическое копирование формулы на всё поле.

1. Выбрать Файл [File], Параметры [Options].
2. В разделе Правописание [Proofing] нажать кнопку Параметры автозамены [AutoCorrect Options], перейти на вкладку Автоформат при вводе [AutoFormat As You Type].
3. Выбрать в области Автоматически в ходе работы [Automatically as you work] выбрать Создавать вычисляемые столбцы, заполнив таблицы формулами [Fill formulas in tables to create calculated columns].
4. OK.
5. OK.

3-я настройка: Форматирование таблицы при добавлении строк/столбцов.

1. Выбрать Файл [File], Параметры [Options].
2. В разделе Правописание [Proofing] нажать кнопку Параметры автозамены [AutoCorrect Options], перейти на вкладку Автоформат при вводе [AutoFormat As You Type].
3. Выбрать в области Выполнять в ходе работы [Apply as you work] выбрать Включать в таблицу новые строки и новые столбцы [Include new rows and columns in table].
4. OK.
5. OK.