Быстрая работа с ячейками. Быстрый поиск и замена данных
Как известно, Microsoft Excel широко используется не только корпоративными, но и домашними пользователями, что неудивительно, поскольку офисный пакет Microsoft имеется на компьютере большинства пользователей, а возможности электронных таблиц Excel достаточны для решения многих базовых задач. Да и работать в Excel достаточно удобно. Вместе с тем, при желании, решение разнообразных задач в этих электронных таблицах можно еще более упростить и ускорить. Каким образом? Оснастив Excel парой-тройкой подходящих расширений, о некоторых из которых мы сегодня и поговорим.
Быстрая работа с ячейками
Многие однотипные операции в таблицах приходится выполнять многократно: изменять формат ячеек, перемещать столбцы и строки, сортировать данные и т.д. Все это требует времени, поэтому крайне важно наличие функционала для быстрого осуществления таких действий, то есть одним кликом, а не через последовательный выбор тех или иных меню. Ускорить выполнение таких операций в Excel можно через макросы, которые, разумеется, перед этим нужно будет самим создать и отладить, а соответствующие макросам кнопки вынести на панель управления. Кстати, для части такого рода операций кнопки уже предусмотрены разработчиками, но их нужно вынести на панель. Для этого придется открыть окно "Настройка" (команда "Сервис" > "Настройка"), активировать вкладку "Команды" и, перебирая категории в левой части окна, внимательно рассматривать кнопки в его правой части. При обнаружении интересующей кнопки ее следует перетащить на панель инструментов.
Если кнопок для интересующих вас действий найти не удалось, а разбираться с макросами вам вовсе не хочется, то можно расширить функциональность Excel, установив надстройки
ASAP Utilities,
PLEX либо
Cells Assistant for Excel.
Самые скромные возможности у бесплатного плагина Cells Assistant for Excel (1,29 Мб), предназначенного для быстрого выделения ячеек. После его установки в Excel появится панель дополнительных кнопок, с помощью которых станет возможно быстрое выделение ячеек с комментариями/значениями/формулами (Comments/Constants/Formulas). А также ячеек текущего региона либо строки (Current Region/Current Array), графических объектов (Objects), только видимых ячеек (Visible cells only), ячеек, к которым было применено условное форматирование (Conditional formats), и т.п.
После инсталляции набора макросов PLEX (431 кб; 500 руб.; демо-версия на 500 запусков) в окне Excel ровным счетом ничего не добавится, и придется еще потрудиться. В частности, в Excel 2000-2003 надо выбрать команду "Сервис" > "Надстройки", в открывшемся диалоговом окне нажать кнопку "Обзор" и, открыв перед этим распакованную папку PLEX, указать файл PLEX.XLA.
После этого в окне Excel вы увидите дополнительную панель инструментов PLEX и меню PLEX, которые полностью дублируют друг друга. Используя кнопки данной панели, можно автоматически подсвечивать ячейки в зависимости от их содержимого (это, в частности, позволит быстро понять, где в большой таблице введено значение вместо формулы или текст вместо числа), отсортировать ячейки по цвету и провести суммирование ячеек с определенным цветом шрифта или заливки. А также быстро переставить ячейки в выделенном диапазоне в обратном порядке (первая становится последней, вторая - предпоследней и т.д.), удалить все пустые строки на листе, поменять между собой местами выделенные столбцы либо строки и т.п. Также быстро можно установить в выделенных ячейках денежные форматы, вставить текущее значение курса доллара (евро, гривны, фунта стерлингов) на заданную дату, вывести сумму прописью на русском и английском языках и др. Все эти и многие другие операции будут доступны как через меню PLEX, так и через панель инструментов PLEX.
Плагин ASAP Utilities (10,8 Мб; 49 долл.) представляет собой целый набор разного рода инструментов, упрощающих работу в Excel. Демо-версия решения почти полностью функциональна и не ограничена по времени использования, но в коммерческих организациях она может тестироваться не более 90 дней. После установки ASAP Utilities в Excel появляется дополнительное одноименное меню, открывающее доступ ко множеству самых разнообразных функций.
Так, команда "Select" в этом меню отвечает за быстрое выделение ячеек по каким-либо принципам: видимых, защищенных либо незащищенных, пустых или непустых, содержащих дублирующиеся значения, рассчитанных по формулам, имеющих определенное форматирование и т.д. Команда "Sheets" обеспечивает расширенную работу с листами - скажем, одним кликом можно будет создать целую группу новых листов с теми же именами, что и выделенные рабочие ячейки, или, наоборот, вставить в активную ячейку имя рабочего листа, быстро скрыть/сделать видимыми активные листы, отсортировать листы по алфавиту, номерам либо цвету и пр. В подменю "Range" объединены команды для расширенного поиска и замены, сортировки, специальной вставки, транспонирования и др. Они, в частности, позволяют провести сортировку ячеек с учетом не только значений в них, но и целого спектра других критериев (включая цвет заполнения ячейки, фонт и размер шрифта и др.). Команды подменю "Columns & Rows" помогут быстро раскрасить таблицу, залив четные и нечетные строки разными цветами, слить указанные столбцы либо строки, удалить пустые либо скрытые столбцы и строки, отрегулировать высоту строк в соответствии со значениями в них и пр. Подменю "Numbers" упросит ряд операций с числами - в частности, поможет легко изменить знак перед числом на противоположный, округлить до определенного числа знаков после запятой, отформатировать как текст и т.п. И это лишь самое немногое из того перечня операций, выполнение которых может быть ускорено с помощью расширения ASAP Utilities.
Очень удобно в ASAP Utilities реализовано применение формул - они назначаются сразу всему выделенному диапазону, при этом программа запоминает последние 100 задействованных формул, так что в случае повторного применения вводить нужную формулу уже не потребуется - ее можно просто выбрать из списка.
Быстрый поиск и замена данных
Встроенной в Excel возможности проведения поиска и замены хватает далеко не всегда. Предположим, нужно провести замену не во всех, а только в каких-то определенных найденных при поиске ячейках - встроенными средствами Excel в таком случае придется многократно щелкать по кнопке "Найти далее" и осуществлять замену, что весьма утомительно. Или, при необходимости проведения одной и той же замены в нескольких листах либо книгах, данную операцию нужно будет запускать многократно. А если какие-то варианты замен приходится проводить регулярно, то каждый раз нужно будет вводить данные для поиска и замены заново, поскольку в Excel запоминание такой информации не предусмотрено.
Ускорить выполнение подобных операций можно с помощью плагинов
Advanced Find & Replace или
MAPILab Find and Replace for Excel, существенно расширяющих возможности стандартного поиска и замены.
Произвести сложный поиск с заменой с помощью Advanced Find & Replace (3,49 Мб; 29,95 долл.; 15-дневная демо-версия) не составляет ни малейшего труда. Вначале нужно щелкнуть на появившейся в панели инструментов после установки плагина кнопке - это приведет к открытию в левом углу окна панели "Find & Replace". А затем ввести в поле поиска искомый текст и щелкнуть на кнопке "Find All". В итоге будут найдены ячейки с соответствующим текстом на всех листах открытой книги (или нескольких книг, если таковых было открыто более одной). После этого можно будет осуществить замену сразу во всех ячейках, указав текст для замены и щелкнув на кнопке "Replace All". Либо только в некоторых их них - тогда эти ячейки нужно будет выделить и воспользоваться командой "Replace in the selected items". При необходимости поиск можно проводить не только в значениях ячеек, но также в формулах, гиперссылках и комментариях - это настраивается на вкладке "Search in".
Принцип проведения поиска с заменой в MAPILab Find and Replace for Excel (1,98 Мб; 1100 руб.; 20-дневная демо-версия) совершенно иной. Книги открывать не нужно, а после открытия Excel следует сразу же щелкнуть на кнопке плагина - это приведет к запуску мастера, на вопросы которого и нужно будет ответить. А именно, вначале указать интересующие книги или диапазоны и затем настроить правила замены. Если изначально был установлен режим замены с предварительным просмотром изменений, то после этого программа выдаст на экран список всех найденных фрагментов текста.
Затем можно будет осуществить замену во всех из них либо только в некоторых, выделив их и нажав на кнопку "Заменить отмеченные".
Стоит заметить, что плагин MAPILab Find and Replace for Excel может применяться для сохранения и загрузки поисковых запросов в качестве шаблонов. Это позволит в дальнейшем проводить поиск более быстро, ведь исходные данные для поиска и замены уже вводить не придется - потребуется лишь указать список файлов и запустить процесс поиска. Кроме того, названный плагин допускает использование регулярных выражений и масок, что существенно расширяет возможности задания критериев поиска и замены данных, поскольку появляется возможность указывать не конкретные данные, а шаблон соответствия.
Удаление ненужных пробелов. Исправление опечаток. Записи-дубликаты
Удаление ненужных пробелов
Нередко при копировании в Excel текста из других источников либо просто при небрежном вводе данных появляются ячейки, имеющие вначале и/или в конце введенного в них текста пробелы, которые внешне могут быть и невидимы (если находятся в конце). Пытаться проанализировать данные с наличием подобных пробелов (к примеру, построить сводные таблицы) совершенно невозможно и потому их приходиться отлавливать и удалять; пытаться осуществить это вручную - дело неблагодарное. Гораздо разумнее автоматизировать процесс, воспользовавшись бесплатным плагином
Trim Spaces (1,3 Мб).
С его помощью выявить и удалить такие пробелы можно будет в считанные секунды, ведь для этого потребуется лишь выделить интересующие столбцы и щелкнуть на кнопке "Trim Spaces". Все лишние пробелы окажутся удаленными, о количестве найденных пробелов плагин любезно проинформирует.
Также стоит заметить, что функция удаления ненужных пробелов предусмотрена в уже упоминавшемся плагине ASAP Utilities - за подобную операцию в нем отвечает команда "Text" > "Delete leading and trailing spaces".
Исправление опечаток
Те, кому приходится анализировать большие Excel-таблицы, прекрасно знают, сколько хлопот доставляет отсутствие единообразия вводимых данных, о чем ответственные за непосредственный ввод данных в таблицы сотрудники совершенно не задумываются. Что это означает? Все очень просто - к примеру, вместо текста "Core 2 Duo" в некоторых строках может оказаться "Core2Duo", в других "CORE2 DUO" и т.д. Казалось бы, мелочь, ведь и так понятно, о чем идет речь, но попробуйте на практике провести какие-то выборки в таких таблицах, построить по ним сводные и т.п. Ситуацию еще больше ухудшает наличие банальных опечаток во вводимой информации, когда какой-то из символов случайно оказался просто пропущен либо заменен другим.
Обычно со всеми подобными неточностями приходится сражаться вручную, что отнимает львиную долю времени, однако существует и гораздо более простой способ решения проблемы в лице плагинов
Similar Data Finder for Excel или
Fuzzy Duplicate Finder. С их помощью выявить ячейки с похожими значениями и исправить значения в тех ячейках, где они оказались неверными, можно в считанные секунды. Подобная корректировка может производиться автоматически (исправляются неверные значения сразу во всех схожих ячейках) либо вручную, когда правка осуществляется только в отношении отдельных ячеек. Наличие возможности ручной правки не менее важно, ведь в немалом числе случаев исправлять требуется не все выявленные ячейки, а лишь часть из них. Дело в том, что наименования моделей продукции могут быть весьма схожими. Например, разные модели блоков бесперебойного питания "Back 500 Ippon Power Pro", "Back 600 Ippon Power Pro" и "Back 800 Ippon Power Pro" окажутся отнесенными данными плагинами к одной группе ячеек. А это означает, что корректно изменить в соответствующем столбце, например, вариант написания "Back 800 Ippon POWER PRO" на вариант "Back 800 Ippon Power Pro" в автоматическом режиме окажется невозможным - придется прибегать к корректировке значений вручную. Но все равно это окажется гораздо быстрее, чем без применения плагинов.
Найти вышеперечисленные ошибки, допущенные при вводе значений ячеек, средствами Similar Data Finder for Excel (3,9 Мб; 1100 руб.; 20-дневная демо-версия) также несложно, поскольку заправлять подобным поиском будет мастер. Нужно открыть рабочую книгу, запустить Similar Data Finder for Excel, щелкнув на одноименной кнопке в окне Excel, выбрать необходимую рабочую книгу или диапазон ячеек и указать режим их сравнения (то есть то, на сколько должны отличаться друг от друга ячейки в процентах либо по числу символов) и диапазон обработки.
Программа пару секунд поразмышляет "о жизни" и выдаст отчет показанного ниже вида с ячейками, имеющими похожие значения. После этого во всех либо только отдельных ячейках можно будет изменить особенности форматирования (скажем, фон или шрифт), содержимое либо скопировать правильное значение во все ячейки этой группы. Можно также просто удалить соответствующие строки, если речь идет о выявлении записей-дубликатов. В частности, для того чтобы присвоить значение конкретной ячейки всем ячейкам группы, нужно будет выделить строку с этой ячейкой в результатах поиска и выбрать из контекстного меню команду "Присвоить группе".
С помощью плагина Fuzzy Duplicate Finder (2,35 Мб; 29,95 долл.; 15-дневная демо-версия) подобный результат достигается несколько иначе. Выделяют интересующий интервал данных, на панели плагина в поле "Max number" задают число символов, на которое значения ячеек могут отличаться между собой, и щелкают по кнопке "Find Typos". Программа отображает схожие между собой ячейки также по группам. После этого можно будет заменить значения во всех либо только в некоторых найденных ячейках - например, для достижения единообразного вида данных во всех ячейках выделяют ячейку-образец, выбирают команду "Check item" из контекстного меню, а затем щелкают на кнопке "Apply Changes".
Выявление записей-дубликатов
По тем или иным причинам, в больших таблицах нередко появляются записи-дубликаты (а иногда и столбцы-дубликаты) - как правило, это связано с недостаточно добросовестным вводом данных. Анализировать данные с такими дубликатами бессмысленно и приходится их вычислять и удалять. Если проводить поиск записей-дубликатов (либо столбцов-дубликатов) встроенными средствами Excel, то эта операция будет отнимать много времени. В случае записей, например, нужно будет вначале отсортировать их по интересующему столбцу (то есть добиться последовательного расположения записей-дубликатов), а затем вручную просмотреть всю таблицу на предмет их выявления. Труд этот весьма утомительный, ведь таблицы с данными обычно очень большие, да и вероятность ошибок не исключена.
Для ускорения решения проблемы стоит воспользоваться расширениями
Remove Duplicates from Excel или
Duplicate Remover. Оба плагина предназначены для поиска и обработки дубликатов в документах Excel и позволят быстро найти все повторяющиеся элементы, а затем выделить их, изменив форматирование, либо сразу удалить. Причем поиск через эти плагины можно проводить как с целью выявления полностью идентичных строк, так и для того, чтобы найти ячейки с повторяющимися данными в конкретных столбцах или строках.
Плагин Remove Duplicates from Excel (1,76 Мб; 1100 руб.; 20-дневная демо-версия) позволяет искать дубликаты во всей таблице целиком либо только внутри указанного непрерывного диапазона ячеек, а выявленные дублирующиеся ячейки могут быть иначе отформатированы или очищены либо могут быть удалены или скрыты содержащие их строки/столбцы. В случае поиска строк-дубликатов технология следующая - следует выделить всю таблицу либо интересующий ее фрагмент, щелкнуть на кнопке "Поиск одинаковых строк" и в перечне допустимых действий указать нужную операцию (скажем, "Изменить вид" для выделения ячеек цветом). Следует отметить, что поиск может проводиться по значению (установлено по умолчанию) либо по формуле и с учетом либо без учета регистра.
Плагин Duplicate Remover (2,26 Мб; 29,95 долл.; 15-дневная демо-версия) умеет искать дубликаты в одной таблице (или ее диапазоне) либо в двух отдельных таблицах. При этом повторяющиеся данные могут быть выделены, иначе отформатированы, удалены либо скопированы или перемещены в новую рабочую книгу либо на новый/существующий рабочий лист. Если речь идет об одной таблице, то для проведения операции нужно выделить всю таблицу либо диапазон в ней (впрочем, это можно будет сделать и позднее) и щелкнуть на кнопке плагина. А затем подсветить вариант "Search in a single table" и при необходимости скорректировать диапазон поиска. На следующем шаге указать, что требуется искать - дублирующиеся элементы ("Duplicates") либо уникальные ("Uniques"), - и отметить флажками только те столбцы, которые будут проверяться на предмет наличия дубликатов либо уникальных данных. По окончании еще потребуется указать программе, что следует сделать с найденными данными: выделить их, отметить цветом и т.д.
Если задача проще, и нужно избавиться от дубликатов не на уровне таблицы, а, например, внутри фрагмента столбца - то есть выявить в диапазоне уникальные значения, - то тут возможны разные варианты решения проблемы. Можно создать по данному столбцу сводную таблицу, в которой автоматически окажутся отображенными только уникальные значения. Либо воспользоваться уже упоминавшимся плагином PLEX, умеющем извлекать из указанного диапазона уникальные элементы (команда "PLEX" > "Диапазоны" > "Вывод уникальных элементов").
Сравнение таблиц с данными. Объединение данных нескольких файлов
Сравнение таблиц с данными
Нередко при работе в 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. Для начала щелкают на кнопке плагина и активируют режим "Объединить таблицы". Затем указывают исходные таблицы, корректируют диапазоны данных и определяют тот столбец, который будет использоваться для сравнения. По окончании задают список полей из первой и второй таблиц, которые должны присутствовать в результирующей таблице, и указывают, где должна быть создана новая таблица: в текущем листе, новом листе либо в новой книге.
Разделение данных по таблицам. Эффективная работа с книгами
Разделение данных по таблицам
Если вам часто приходиться формировать на базе одной таблицы несколько новых, включая в них некоторую информацию из исходной таблицы, то стоит обратить внимание на выше упоминавшиеся плагины Advanced Consolidation Manager, Tables Transformer for Excel и ASAP Utilities.
Плагин Advanced Consolidation Manager окажется полезным при копировании данных в разные файлы с группировкой их по страницам - "в переводе на русский" это означает, что первые листы с данными из исходных файлов будут последовательно объединены в первой книге, вторые - во второй и т.д. Технология подобного разделения данных, в принципе, ничем не отличается от вышерассмотренного примера с объединением данных через этот же плагин - единственное, что выбирать в первом окне нужно будет режим "Копировать данные из нескольких файлов с группировкой по имени страницы".
Плагин Tables Transformer for Excel также поможет быстро разделить одну таблицу на несколько новых. Но принципы разделения тут совершенно иные - в зависимости от настроек, плагин руководствуется либо указанным пользователем количеством строк, либо уникальными значениями ячеек в определенных столбцах. В первом варианте достаточно лишь указать программе, через какое число строк следует начинать новую таблицу. В результате такого разделения, в самой первой таблице окажутся самые верхние строки исходной таблицы, во второй - следующие за ними строки и т.д. Названия столбцов в каждой новой таблице могут быть продублированы, а новые таблицы окажутся размещенными в новой либо рабочей книгах (на текущем или новом листах) Подобный вариант разбиения может пригодиться, например, для получения на базе одного годового отчета по продажам с данными за каждый месяц четырех квартальных отчетов.
Второй режим разбиения (с учетом уникальных значений ячеек в выбранном столбце), на наш взгляд, может потребоваться на практике чаще - скажем, он будет полезен для получения отдельных таблиц с данными по соответствующим уникальным значениям, например, по конкретным производителям, наименованиям продукции и т.п. Взяв за основу таблицу с данными о продукции от множества производителей, попробуем создать отдельные таблицы по каждому производителю. Для этого нужно открыть исходную книгу, щелкнуть по кнопке плагина, выбрать режим "Разделить таблицу", указать лист и диапазон с данными, выбрать в качестве способа разделения вариант "По уникальным значениям в столбцах" и указать столбец для разделения (в нашем случае это будет столбец "Vendor").
Возможности плагина ASAP Utilities в этом плане гораздо скромнее - им можно воспользоваться для формирования новых Excel-документов на основе отдельных листов исходной книги. Иными словами, каждый из листов исходной книги будет помещен в отдельный файл. Производится эта операция по команде "ASAP Utilities" > "Export" > "Export worksheets as separate files".
Использование автоформатирования и стилей
Как известно, в Excel предусмотрено автоматическое форматирование сводных таблиц (команда "Формат" > "Автоформат"), однако выбор предустановленных шаблонов там невелик. Поэтому чаще всего приходится прибегать к форматированию сводных таблиц вручную, но при их перестройке (например, при добавлении или удалении полей) часть сделанных настроек форматирования может теряться - в итоге, приходится затрачивать дополнительное время на повторное форматирование. Еще более актуально то, что каждую новую сводную таблицу вновь приходится форматировать, поскольку запомнить сделанные настройки форматирования в виде шаблона в Excel невозможно - подобной функциональности там просто нет. Поэтому при активном использовании сводных таблиц явно нелишней окажется установка плагина
AutoFormat for PivotTables.
В поставку плагина AutoFormat for PivotTables (3,76 Мб; 29,95 долл.; 15-дневная демо-версия) входит некоторый набор предустановленных и довольно привлекательных шаблонов, список которых может быть расширен путем скачивания дополнительных шаблонов с сайта программы либо за счет самостоятельной разработки. Последнее как раз и представляется самым интересным, поскольку после разработки нужных в работе шаблонов (понятно, что с последующим их сохранением в базе шаблонов), форматирование вновь создаваемых таблиц уже не будет отнимать львиную долю времени. Для его проведения достаточно будет просто запустить плагин, выбрать нужный шаблон из списка и щелкнуть на кнопке "Apply". Количество создаваемых пользовательских шаблонов не ограничено, и для удобства работы с ними шаблоны можно объединять в группы. Поддерживается импорт и экспорт пользовательских шаблонов, что позволяет восстанавливать их после переустановки системы и переносить между компьютерами. Кстати, по заверениям разработчиков, этот плагин может быть получен бесплатно теми пользователями, которые пришлют им свою коллекцию шаблонов.
Что касается стилей, то широкое применение их на практике позволяет не только добиваться единообразного оформления материалов, но и существенно ускоряет процесс настройки внешнего вида таблиц. Увы, встроенные в Excel возможности работы со стилями довольно ограничены - их немного, а создание пользовательских стилей хотя и допускается, но использовать такие стили можно только внутри той книги, где они были созданы. Если же необходимо применить пользовательский стиль в другой книге (то есть не в той, где он был создан), то придется открывать обе книги и затем копировать стиль из одной книги в другую, что долго и неудобно. Если такое положение дел вас не устраивает, то стоит обратить внимание на плагин
Style Manager for Excel.
Плагин Style Manager for Excel (3,08 Мб; 19,95 долл.; 15-дневная демо-версия) поможет легко и быстро оформлять таблицы с помощью стилей, управление которыми производится через дополнительную панель "Style Manager", включаемую/выключаемую нажатием комбинации клавиш "Ctrl+Shift+S". Список входящих в поставку стилей достаточно велик (имеются стили для оформления заголовков, исходных данных, результирующих эти данные ячеек и т.п.). Более того, данный список без проблем может быть расширен за счет пользовательских стилей, создать которые совсем несложно - достаточно настроить параметры форматирования в одной из ячеек, по своему вкусу подобрав цвета, шрифты, границы и прочие настройки форматирования, щелкнуть на кнопке "Add" и ввести имя нового стиля. Созданные таким образом пользовательские стили в дальнейшем без ограничений могут быть использованы в любой книге. Все стили (как встроенные, так и пользовательские) отображаются на панели "Style Manager" в древовидной структуре, причем, для удобства применения, их несложно сгруппировать по папкам. При необходимости, любой из стилей можно перетащить из одной папки в другую, переименовать или удалить. Наложение на выделенные ячейки стиля не составляет ни малейшего труда - достаточно щелчка мыши по выбранному стилю.
Быстрый доступ к избранным книгам
Активно работающим в Excel пользователям часто приходится открывать по множеству книг, которые могут находиться в самых разных папках на различных дисках, включая сетевые. Поэтому перемещение по соответствующим папкам нередко оказывается весьма утомительным. Способов решения данной проблемы множество. Можно, к примеру, прямо на рабочем столе разместить ведущие к документам ярлыки, однако это не самая лучшая идея. Гораздо разумнее воспользоваться бесплатной Excel-надстройкой
Favorite Bookmarks (509 кб). С ее помощью несложно создать нужный набор закладок на избранные папки с документами либо отдельные файлы, каждый раз щелкая на кнопке "Add" и указывая файл либо папку. Это позволит в дальнейшем открывать соответствующие папки либо загружать напрямую файлы всего одним кликом мыши. Созданным набором закладок можно управлять, перемещая их в желаемом порядке. Кроме того, для удобства ориентации, разрешается вставлять между закладками пустые строки либо метки.
Эффективная работа с книгами
Если одновременно приходится работать со множеством книг, постоянно переключаясь между ними, то имеет смысл обзавестись плагином
Explorer for Microsoft Excel. Данный плагин (3,54 Мб; 19,95 долл.; 15-дневная демо-версия) обеспечивает удобное визуальное управление открытыми книгами и листами в них, отображая и те, и другие на специальной панели в виде дерева. Эта панель располагается в левой части экрана и скрывается/отображается выбором из меню Ablebits.com либо нажатием комбинации клавиш "Ctrl+Shift+N" (правда, при тестировании последней версии плагина в нашем случае названная комбинация почему-то работать перестала, чему объяснения мы так и не нашли). Через панель Explorer for Excel очень удобно переключаться между листами и книгами, просто подсвечивая их на дереве. Можно также переименовывать и распечатывать листы и книги, создавать, удалять листы и перетаскивать их между книгами. Одним кликом несложно выявить ошибки во всех открытых документах, ссылки, имена, комментарии, гиперссылки и ячейки, имеющие условное форматирование. Перечень допустимых действий в отношении этих объектов различен - скажем, условные форматы можно очистить, имена изменить, ошибки проигнорировать и т.д.
Во многих таблицах в ряде ячеек значения вычисляются на основе данных из других листов либо даже других книг (файлов-источников), поскольку Excel позволяет связывать рабочие листы и отображать данные из одного рабочего листа в другом, вне зависимости от его расположения через внешние ссылки. Подобная возможность совершенно незаменима в случае регулярного обновления информации в файле-источнике либо необходимости обобщения данных из различных рабочих книг. За примерами такого рода задач далеко ходить не надо - скажем, требуется подготовить отчет по продажам, в котором некоторые исходные данные для вычислений (хотя бы текущие цены, которые ныне меняются не один раз на дню) берутся из связанного с рабочей книгой файла. А этот файл хранится в общей папке на сервере компании. При создании отчета можно пойти двумя путями - либо вручную каждый раз копировать в отчет изменившиеся цены, либо брать их напрямую из той самой книги, что находится в общей папке на сервере. Первый вариант весьма зануден, во втором достаточно будет лишь однажды настроить правильно формулы - и "дело в шляпе": в отчете всегда будут использоваться в расчетах самые последние данные. Но тут есть одно "но" - если кому-то придет в голову мысль переименовать исходный файл с ценами или переместить его в другую папку (пусть и находящуюся в той же самой общей папке), то все ссылки окажутся неработоспособными, а все связи, соответственно, нарушенными. Если такая ситуация приключилась, то придется выяснять, что произошло с исходным файлом (а таковых ведь может быть и несколько), и затем вручную вновь указать эти файлы во всех расчетных формулах, что потребует времени и нервов. Более быстрый способ решения проблемы - воспользоваться плагином
Fix Broken Links for Excel.
С помощью этого плагина (2,71 Мб; 1600 руб.; 20-дневная демо-версия), нарушенные связи могут быть восстановлены в автоматическом режиме. Это означает, что вручную указывать новые файлы-источники в формулах не придется - программа сделает это сама. Технически это будет выглядеть следующим образом - после запуска плагина выбирается режим "Восстановление связей", затем в пустой изначально список в следующем окне добавляют исходный файл, ссылки в котором необходимо восстановить. После этого указывают папки, в которых должен быть произведен поиск файла-источника, на который имеются ссылки в формулах исходного файла, указанного на предыдущем шаге. В итоге всего этого, плагин либо найдет файл-источник либо нет, а найдет он его лишь в случаях, если одна из указанных папок (но не подпапка) содержит файл-источник с исходным именем. Так что тут особо обольщаться не стоит - "чудес в этом мире не бывает" - и вам все равно нужно знать местонахождение файла-источника и его нынешнее имя. В случае безуспешного поиска в автоматическом режиме, придется указать файл-источник вручную - для этого на последнем шаге мастера, в режиме автоматического восстановления связей, в окне "Восстановление связей" нужно щелкнуть правой кнопкой мыши на ненайденном файле-источнике, в контекстном меню выбрать "Ручное восстановление" и самостоятельно указать файл. Конечно, в итоге, восстановление связей получается не в полном смысле автоматическим, но все равно это оказывается намного быстрее, чем восстановление таких нарушенных связей вручную средствами только Excel.
Также стоит заметить, что плагин Fix Broken Links for Excel дополнительно умеет копировать и переносить связанные файлы с сохранением работоспособности связей. Данная возможность предотвратит проблемы с нарушением связей при изменении структуры папок на рабочем компьютере и в случае использования связанных документов на нескольких компьютерах - например, рабочем компьютере и ноутбуке. А значит, и избавит от неизбежных проблем восстановления связей.
Заключение
Мы остановились лишь на некоторых популярных расширениях для Microsoft Excel, отобрав наиболее интересные в плане функциональности инструменты для широкого пользователя и отбросив дорогостоящие наборы в лице, скажем, Productivity Suite for Microsoft Excel. При этом, далеко не в каждом продукте нами были полностью указаны все его возможности, поскольку своей задачей мы ставили не перечисление всех без исключения функций того или иного расширения, а решение конкретного спектра задач, часто возникающих при работе с большими объемами данных в Excel. И, разумеется, признаем, что у каждого активного Excel-пользователя имеется свой набор установленных плагинов, позволяющих оптимально решать какой-то конкретный спектр задач.
