Большинство новинок уже оценено пользователями Excel 2013. Но тем не менее, всегда остается что-то интересное и, самое главное,нужное для работы.
Только обладатели набора приложений Office 2013 профессиональный плюс имеют возможность расширить возможности программы с помощью надстройки Inquire. Приятно, что эту надстройку нет необходимости дополнительно скачивать и устанавливать, она устанавливается вместе с Excel 2013.
Какие задачи нам помогут решать команды из надстройки Inquire?

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

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

-открыть Файл,  выбрать Параметры, затем группу Надстройки

-списке Управление выбрать Надстройки COM и нажать кнопку Перейти

– выбрать в списке Inquire и нажать ОК.

В результате появится новая вкладка INQUIRE со следующими командами:

  – СРАВНЕНИЕ ДВУХ ФАЙЛОВ.

Открыть оба сравниваемых файла. Нажать кнопку Compare Files.

В окне Select Files to Compare выбрать 2 файла (кнопка Swap Files поменяет файлы местами между Compare и To), нажать кнопку Compare.

Результат сравнения будет выведен в отдельное окно:

 – ПРОСМОТР СВЯЗЕЙ между рабочими книгами (Workbook Relationship), рабочими листами (Worksheet Relationship), и ячейками листа (Cell Relationship).

Например, структурная схема зависимостей рабочих книг:

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

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

 – АНАЛИЗ КНИГИ  по множеству параметров.

Нажав на кнопку Workbook Analysis, начинается работа по анализу книги. В области Items пользователь имеет возможность выбрать позиции по которым будет произведен анализ:

Кнопка Excel Export формирует отдельный файл-отчет:

Построение отчета сводной таблицы по данным одной таблицы, как правило начинается с команды Вставка [Insert] – Сводная таблица [PivotTable]. Для построения сводной таблицы по нескольким диапазонам консолидации необходимо воспользоваться Мастером сводных таблиц.

Начиная с Excel 2007 версии, команда Мастер сводных таблиц не располагается на ленте. Поэтому команду можно вывести, для этого выбрать Файл [File], Параметры [Options], в разделе Панель быстрого доступа [Quick Access Toolbar] в списке Выбрать команды из [Choose commands from] выбрать Команды не на ленте [Commands Not in the Ribbon]. Выделить Мастер сводных таблиц и диаграмм [PivotTable and PivotChart Wizard], нажать кнопку Добавить >> [Add >>], ОК.

ПОСЛЕДОВАТЕЛЬНОСТЬ ДЕЙСТВИЙ

Нажать кнопку Мастер сводных таблиц и диаграмм [PivotTable and PivotChart Wizard)]

В появившемся окне Мастер сводных таблиц и диаграмм – шаг 1 из 3 [PivotTable and PivotChart Wizard – Step 1 of 3] выбрать в нескольких диапазонах консолидации [Muitiple Consolidation ranges], нажать Далее [Next].

В окне Мастер сводных таблиц и диаграмм – шаг 2а из 3 [PivotTable and PivotChart Wizard – Step 2a of 3] выбрать Создать поля страницы [I will create the page fields], нажать Далее [Next]

В окне Мастер сводных таблиц и диаграмм – шаг 2б из 3 [PivotTable and PivotChart Wizard – Step 2b of 3] выбрать диапазоны для объединения и создать поля:

• В поле Диапазон [Range] выделить первый исходный диапазон вместе с заголовками, нажать Добавить [Add]. Повторить для всех исходных дипазонов
• В поле Во-первых, укажите количество полей страницы сводной таблицы [How many page fields do you want?] выбрать нужное количество полей (фильтры отчета сводной таблицы). Например, объединяя данные продаж компаний по разным городам можно создать 2 поля – компания и город.
• Каждый диапазон из списка диапзонов нужно описать по каждому из заданных полей (максимум 4). Выделить диапазон в списке диапазонов, в Первое поле [Field one] ввести описание первого поля и т.д. Например, Компания – это первое поле, Город – второе поле.

Нажать Далее [Next].
В окне Мастер сводных таблиц и диаграмм – шаг 3 из 3 [PivotTable and PivotChart Wizard – Step 3 of 3] выбрать расположение сводной таблицы.

Нажать Готово [Finish].

Поля Страница1 и Страница2, расположенные в области ФИЛЬТРЫ можно переименовать, переместить в область макета СТОЛБЦЫ и СТРОКИ, чтобы придать отчету большей наглядности:

Определим стоимость доставки груза в зависимости от его массы.

Подобную задачу можно решить с помощью функции ЕСЛИ [IF], а можно и воспользоваться формулой ВПР [VLOOKUP]. Наиболее часто, функцию ВПР используют для определения точного совпадения искомого значения в другой таблице, но на этот раз рассмотрим использование функции ВПР для приблизительного поиска.

Отличия будут заключаться в следующем:

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

• 4-й аргумент в функции ВПР – просматриваемый массив. Отвечает за то, как ведется поиск. При 0 ищет первое точное совпадение при просмотре сверху вниз, а при 1 если нет совпадения, то функция выдает максимальное значение из всех тех, что меньше искомого

Как же будет работать функция ВПР в данном случае? Рассмотрим 2 ситуации:

• масса груза совпадает, например, 250 г – ответ будет 33 у.е.
• масса груза будет 555 г –  при просмотре сверху вниз, программа найдет первое значение, которое окажется больше заданного, т.е. 750, а ответ будет по предыдущей строке, т.е. 55 у.е. Получается, что там, где в строке указано 500, на самом деле от 500 (включительно) до 750 (не включительно)