Ускоряем работу в Excel - полезные плагины

Сравнение таблиц с данными

Нередко при работе в Excel приходиться сравнивать между собой различные модификации таблиц (например, обновленные версии прайс-листов с изменившимися ценами и т.п.). Увы, при этом речь идет совсем не о том, какая из версий файлов является более свежей (это и так очевидно из свойств файлов), а о том, что именно изменилось в таблицах с точки зрения содержимого. Сравнить все позиции обеих таблиц вручную совершенно нереально в силу слишком больших затрат времени. Но ничего страшного, поскольку подобное сравнение может быть произведено автоматически с помощью самых разнообразных расширений. Каких? Например, с помощью уже упоминавшегося плагина Duplicate Remover либо более узкоспециализированных решений в лице расширения Compare Spreadsheets for Excel или независимого приложения Excel Compare. Плагин Duplicate Remover умеет сравнивать на предмет уникальных значений только два листа из двух различных таблиц. Хитростей проведения анализа данных с его помощью никаких. Следует открыть обе книги, щелкнуть на кнопке плагина и подсветить флажок "Compare two tables (lists, ranges)". Затем нужно будет указать диапазон первой таблицы, потом диапазон второй таблицы, выбрать вариант "Уникальные" ("Uniques"), поскольку задача заключается в выявлении отличающихся записей. По окончании еще потребуется отметить флажками все столбцы и определить, что следует делать с выявленными уникальными записями. После этого Duplicate Remover в мгновение ока найдет и отобразит определенным образом все изменившиеся записи. Возможности плагина Compare Spreadsheets for Excel (8,7 Мб; 1600 руб.; 20-дневная демо-версия) также ограничены сравнением лишь двух листов двух выбранных книг, но зато при сравнении с его помощью может учитываться формат ячеек, а сравниваться не только значения, но и формулы. Кроме того, допускается более тонкая настройка параметров сравнения (в частности, предусмотрены не один, а целых четыре метода сравнения) и отображения результатов, в которых уникальные ячейки выделяются по-разному в зависимости от "нюанса" их уникальности. Так, по умолчанию добавленные ячейки выделяются зеленым цветом, удаленные синим, а измененные обводятся красной рамкой. Воспользоваться данным плагином для выявления изменения в таблицах несколько сложнее, но зато и результат более впечатляет. Вначале щелкают по кнопке плагина и указывают исходные книги, листы на них и диапазоны. Затем выбирают метод сравнения - метод простого наложения ячеек (осуществляется поиск только измененных ячеек) либо метод выравнивания (ищутся не только измененные, но также удаленные и добавленные ячейки). После этого определяют, какие столбцы или строки левого диапазона ячеек следует сравнивать со строками или столбцами правого диапазона ячеек, и настраивают параметры сравнения (сравнивать по формулам или значениям, учитывать ли регистр и/или формат ячеек). На последнем этапе при желании корректируют параметры отчета - то есть внешний вид подсветки добавленных, удаленных и измененных ячеек. Результат проведенного сравнения открывается в двухпанельном окне. На левой панели окажутся отображенными данные из первой таблицы, на правой - из второй. И те, и другие будут раскрашены в соответствии со сделанными установками. Утилита Excel Compare (1,11 Мб; 290 руб.; демо-версия на 60 запусков) умеет сравнивать все одноименные рабочие листы двух файлов, все файлы, которые расположены в двух выбранных папках, либо все листы в файлах созданного в среде данной утилиты проекта. Однако для примера мы проведем аналогичное вышерассмотренному сравнение двух прайс-листов с указанием конкретных диапазонов, что позволит более наглядно сопоставить результаты работы Compare Spreadsheets for Excel и Excel Compare. Провести здесь такое сравнение гораздо проще, поскольку нужно будет лишь выбрать исходные книги, листы в них и диапазоны и щелкнуть на кнопке "Сравнить". Отчет различий, полученный при сравнении двух версий прайс-листа, представляется в виде отдельной Excel-книги. В ней зеленым и голубым цветами будут отмечены уникальные данные в первом и втором сравниваемых диапазонах. Эти данные окажутся сгруппированными в три группы: в первой группе отображаются измененные данные, во второй - добавленные, в третьей - удаленные. То есть все остальные данные (не уникальные) видны в отчете не будут, что, на наш взгляд, не очень удобно, однако все же есть возможность мгновенного переключения к конкретной строке одного из сравниваемых документов непосредственно из отчета щелчком на гиперссылке вида ">" или "<". Но зато настройки сравнений в Excel Compare можно запоминать в виде проектов, что ускоряет в дальнейшем проведение таких однотипных операций.

Быстрое слияние ячеек

Встроенными средствами Excel объединить ячейки, не потеряв данные в них, невозможно, поскольку в полученной после слияния обобщенной ячейке помещаются данные из самой левой ячейки в выделенной группе. А необходимость подобного слияния возникает довольно часто - к примеру, нужно получить столбец "ФИО" с полными данными о клиентах, а изначально при вводе фамилия, имя и отчество были введены в разные столбцы. Понятно, что вариантов решения этой проблемы множество - можно создать столбец "ФИО" с данными, вычисляемыми по формулам на основе отдельных столбцов, а затем последние удалить, можно скопировать данные вручную по очереди и т.п. Но все это окажется достаточно утомительным и потребует немало времени. Однако отчаиваться не стоит - существуют плагины, позволяющие осуществлять подобные варианты слияний одним кликом мыши - это плагин Merge Cells Wizard либо уже упоминавшиеся расширения ASAP Utilities и PLEX. Плагин Merge Cells Wizard (2,13 Мб; 29,95 долл.; 15-дневная демо-версия) может использоваться для слияния нескольких ячеек в одну либо объединения между собой строк, столбцов или диапазонов ячеек (то есть, например, построчного слияния данных из двух столбцов). Для примера работы плагина представим стандартную ситуацию - наличие в таблице столбцов "Вендер" и "Модель", содержимое которых требуется объединить в один столбец с совокупными данными "Вендер/Модель". Первым делом выделяют исходные столбцы и щелкают на кнопке вызова плагина " Merge Cells Wizard". Потом указывают принцип объединения (выберем в свитке "Merge selected cells" вариант "row by row", свитке "Separate values with" - "Alt+Enter", а в поле "Place the results to" - "left cell"), включают флажок "Clear the content of selected cells" для очистки данных в выделенных ячейках и щелкают на кнопке "Merge". В итоге, в первом из выделенных столбцов окажется объединенная информация, а второй будет очищен и его можно будет удалить. Слияние отдельных ячеек через Merge Cells Wizard ничем принципиальным не отличается за исключением того, что в свитке "Merge selected cells" придется выбирать не построчное слияние ("row by row"), как только что было осуществлено, а в одну ячейку, что соответствует варианту "in one cell". Выполнить слияние соответствующих ячеек указанных столбцов с помощью плагина PLEX нисколько не сложнее - достаточно выделить их и воспользоваться командой "PLEX" > "Ячейки" > "Объединение ячеек с сохранением текста", указав как конкретно нужно проводить слияние: по строкам или столбцам. Аналогично данная операция проводится и средствами плагина ASAP Utilities, где за подобные действия отвечают команды "Merge column data (join cells)" и "Merge row data (selected cells)" из меню "ASAP Utilities" > "Columns & Rows". То есть на практике нужно будет, например, только выделить сливаемые столбцы, вызвать команду "Merge column data (join cells)" и указать разделитель (например, "пробел") между сливаемыми ячейками.

Быстрое объединение данных из нескольких файлов

Теоретически, объединить в Excel таблицы из разных файлов в один документ - не проблема, поскольку достаточно просто открыть их и скопировать данные через буфер обмена. Однако при большом количестве файлов или для больших таблиц подобная операция становится достаточно трудоемкой. В таком случае на помощь придут плагины Advanced Consolidation Manager, Merge Tables Wizard или Tables Transformer for Excel. Плагин Advanced Consolidation Manager (3,6 Мб; 1100 руб.; 20-дневная демо-версия) поможет скопировать листы с данными из нескольких файлов в одну рабочую книгу либо объединить данные из разных файлов на один лист. Объединение данных может производиться с использованием итоговых функций: Sum, Count, Average, Max и т.д. Предположим, что у нас имеются два отдельных файла с данными о наличии в розничной продаже в конкретный месяц двух типов продукции; в каждом из файлов - по одному листу. Требуется создать результирующий файл с двумя листами - на первом должны фигурировать данные по первому виду продукции, на втором - по второму. Щелкаем на кнопке плагина и выбираем режим "Копировать данные из нескольких файлов в один файл". Затем указываем исходные файлы и определяем вариант именования страниц - последнее в данном случае не имеет значения, поскольку ни один из предложенных вариантов в данном примере нас не устраивает (хотелось бы, чтобы имена листов полностью совпадали с именами исходных книг, но, увы, так не получится), листы потом все равно придется переименовывать. Плагин Merge Tables Wizard (2,27 Мб; 39,95 долл.; 15-дневная демо-версия) работает не с листами, а с указанными на них данными и также позволяет объединять информацию из нескольких файлов в одном листе. Технически это будет чуть медленнее, поскольку данные придется выделять, но зато появляется возможность брать не все листы полностью, а только нужную информацию с них. Помимо обычного объединения информации из нескольких таблиц, названный плагин также может быть применен для сравнения однотипных данных и их обновления в рабочей таблице на основе информации базовой таблицы. Технология его применения несложная. В качестве примера рассмотрим ситуацию, когда имеются две таблицы: первая включает наименования товаров и их характеристики, а вторая - цены. Требуется создать одну общую таблицу с объединенными данными - то есть с группировкой по наименованию товаров. Для этого щелкают на кнопке плагина и указывают диапазон данных в первой таблице, а затем - диапазон данных во второй таблице. После чего определяют поля, через которые будет произведено слияние (то есть присутствующие в обеих таблицах), и поля из второй таблицы, которые следует добавить. В итоге, первая таблица окажется переписанной - в ней появятся поля из второй таблицы, что, вообще-то, не очень удобно; приятнее, когда есть выбор - обновлять исходную таблицу либо создавать новую. Плагин Tables Transformer for Excel (3,2 Мб; 1100 руб.; 20-дневная демо-версия) может объединить данные из двух таблиц в одну с их группировкой по наименованию конкретного поля. При этом он позволяет включать в общую таблицу не все столбцы из объединяемых таблиц, а только требуемые (исходные данные остаются неизменными). В качестве примера использования данного плагина рассмотрим ту же ситуацию, что и для Merge Tables Wizard. Для начала щелкают на кнопке плагина и активируют режим "Объединить таблицы". Затем указывают исходные таблицы, корректируют диапазоны данных и определяют тот столбец, который будет использоваться для сравнения. По окончании задают список полей из первой и второй таблиц, которые должны присутствовать в результирующей таблице, и указывают, где должна быть создана новая таблица: в текущем листе, новом листе либо в новой книге.