Как сделать несколько таблиц на одном листе


Как сделать несколько таблиц на одном листе

Как сделать несколько таблиц на одном листе

Как сделать несколько таблиц на одном листе





 

Microsoft Excel — лидирующее приложение на рынке решений для обработки электронных таблиц, которое широко используется как корпоративными клиентами, так и домашними пользователями. Такое признание вполне оправданно, поскольку данное решение удобно в применении, стабильно работает и отличается очень широкой функциональностью. Однако, как бы ни был хорош тот или иной программный продукт, всегда можно найти функции, которые неплохо было бы в нем усовершенствовать. Также и с программой Excel, для расширения функциональности которой сегодня разработано множество программ-надстроек. Основное их назначение — добавление новых возможностей и автоматизация привычных рутинных операций. Наиболее интересные решения подобного плана мы и рассмотрим.

Управление данными

Работа с ячейками, столбцами и строками

При работе с таблицами для достижения желаемого результата приходится выполнять огромное количество разнообразных действий: изменять в ячейках форматы, перемещать столбцы и строки, сортировать данные и т.д. Все это требует определенного времени, поэтому крайне важно наличие функционала для быстрого (то есть одним кликом, а не через последовательный выбор тех или иных меню) осуществления таких операций. Необходимый для этого функционал в Excel может быть разработан собственноручно через макросы. Например, если вам регулярно приходится устанавливать в ячейках числовой формат с одним знаком после запятой, то разумнее создать для этой операции макрос, назначить его кнопке и вынести последнюю на панель инструментов. Заметим, что некоторые подобного рода рутинные операции в Excel имеют свои кнопки, но они просто не вынесены на панель инструментов. Поэтому, прежде чем мудрить с макросами, стоит открыть окно Настройка (команда Сервис -> Настройка), активировать вкладку Команды и, перебирая категории в левой части окна, внимательно просмотреть кнопки в его правой части. При обнаружении интересующей кнопки ее следует перетащить на панель инструментов. Можно пойти другим путем — расширить функциональность Excel, установив подходящие надстройки типа ASAP Utilities, PLEX или Cells Assistant for Excel. Это позволит существенно ускорить выполнение ряда операций.

После установки плагина ASAP Utilities (; 3,36 Мбайт; 49 долл.) в Excel появляется дополнительное меню ASAP Utilities (рис. 1), имеющее инструментарий для выполнения множества разнообразных действий. Так, с помощью данного плагина можно быстро пронумеровать ячейки и вставить в выделенных ячейках (перед либо после введенных значений) конкретный текст. Столь же несложно перетасовать значения ячеек случайным образом и объединить ячейки с сохранением их содержимого. Либо выделить ячейки в соответствии с некоторым условием — например только защищенные или незащищенные, пустые или непустые либо те, в которых значения находятся в определенных интервалах или имеют связанные с другими файлами либо листами формулы и т.п. А также скопировать параметры настройки отдельного листа (размеры полей, колонтитулы, ориентацию страницы и пр.) на любой другой лист. ASAP Utilities поможет быстро удалить в таблице все ссылки, объекты (изображения, кнопки), ненужные пробелы и границы, а также ячейки, имеющие заливку определенного цвета либо содержащие определенного цвета текст. С его помощью можно отсеять из группы выделенных ячеек ячейки внутри конкретного диапазона и провести сортировку ячеек с учетом не только значений, но и целого ряда других критериев (включая цвет заполнения ячейки, цвет, гарнитуру и размер шрифта и др.). Очень удобно в ASAP Utilities реализовано применение формул — они назначаются сразу всему выделенному диапазону (рис. 2), при этом программа запоминает последние сто задействованных формул, так что в случае повторного применения вводить нужную формулу уже не потребуется — ее можно просто выбрать из списка.

 

Рисунок

Рис. 1. Меню ASAP Utilities

Рисунок

Рис. 2. Назначение формулы выделенному диапазону в ASAP Utilities

Плагин PLEX (; 1,83 Мбайт; 500 руб.) при инсталляции добавляет в окно Excel дополнительную панель инструментов PLEX Toolbar (рис. 3). Используя кнопки данной панели, можно автоматически подсветить ячейки в зависимости от их содержимого (это, в частности, позволит быстро понять, где в большой таблице введено значение вместо формулы или текст вместо числа), отсортировать ячейки по цвету и провести суммирование ячеек с определенным цветом шрифта или заливки. А также быстро переставить ячейки в выделенном диапазоне в обратном порядке (первая становится последней, вторая — предпоследней и т.д.), удалить все пустые строки на листе, поменять местами выделенные столбцы либо строки и т.п. Так же быстро можно установить в выделенных ячейках денежные форматы, вставить текущее значение курса доллара (евро, гривны, фунты стерлингов) на заданную дату, вывести сумму прописью на русском и английском языках и др.

 

Рисунок

Рис. 3. Панель инструментов PLEX Toolbar

Бесплатный плагин Cells Assistant for Excel (; 1,29 Мбайт) поможет ускорить выполнение некоторых базовых операций за счет вынесения отвечающих за данные операции кнопок на дополнительную панель (рис. 4). Он обеспечит быстрое выделение групп ячеек, к которым было применено условное форматирование, и ячеек, смежных с ячейкой, в которой находится курсор, и имеющих точно такое же форматирование. А также поможет мгновенно переместиться в правую нижнюю ячейку таблицы и быстро выделить ячейки со значениями, формулами или комментариями.

 

Рисунок

Рис. 4. Панель инструментов
Cells Assistant for Excel

Поиск и замена

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

Иное дело, если установить плагины MAPILab Find and Replace for Excel (; 2 Мбайт; 1100 руб.) или Advanced Find & Replace (; 2,78 Мбайт; 29,95 долл.), существенно расширяющие возможности стандартного поиска и замены. Самое главное — они позволяют проводить поиск с заменой сразу в нескольких файлах одновременно (рис. 5), причем искать можно как в выбранных книгах целиком, так и только в определенных листах этих книг. Помимо этого данные плагины умеют проводить поиск и заменять данные не только в значениях ячеек, но и в формулах, комментариях, гиперссылках и др.

 

Рисунок

Рис. 5. Проведение поиска с заменой в четырех документах
одновременно в Advanced Find & Replace

Помимо этого плагин MAPILab Find and Replace for Excel может применяться для сохранения и загрузки поисковых запросов в качестве шаблонов. Это позволит в дальнейшем выполнять поиск более быстро, ведь исходные данные для поиска и замены уже вводить не придется — потребуется лишь указать список файлов и запустить процесс поиска. Кроме того, названный плагин допускает использование регулярных выражений и масок, что существенно расширяет возможности задания критериев поиска и замены данных, поскольку появляется возможность указывать не конкретные данные, а шаблон соответствия.

Использование стилей

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

Если такое положение дел вас не устраивает, то стоит обратить внимание на плагин Style Manager for Excel (; 2,7 Мбайт; 19,95 долл.). Он поможет легко и быстро оформлять таблицы с помощью стилей, управление которыми производится через дополнительную панель Style Manager (рис. 6), включаемую/выключаемую нажатием комбинации клавиш Ctrl + Shift + S. Список входящих в поставку стилей достаточно велик и может быть расширен за счет пользовательских стилей, создать которые совсем несложно: достаточно настроить параметры форматирования в одной из ячеек, по своему вкусу подобрав цвета, шрифты, границы и прочие настройки форматирования, щелкнуть на кнопке Add и ввести имя нового стиля. Созданные таким образом пользовательские стили в дальнейшем без ограничений могут быть использованы в любой книге. Все стили (как встроенные, так и пользовательские) отображаются на панели StyleManager в древовидной структуре, и для удобства применения их несложно сгруппировать по папкам. При необходимости любой из стилей можно перетащить из одной папки в другую, переименовать и удалить.

 

Рисунок

Рис. 6. Окно Excel с панелью Style Manager

Если при разработке собственного стиля арсенала доступных цветов вам окажется недостаточно, то можно создать новые цвета, палитры и градиенты, включая полупрозрачные. Для этого потребуется дополнительно установить плагин Color Palette Manager (; 19,95 долл.).

Удаление ненужных пробелов

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

Отлавливание ненужных пробелов вручную занимает много времени — разумнее автоматизировать данный процесс, установив бесплатный плагин Trim Spaces (; 1,2 Мбайт). С его помощью выявить и удалить такие пробелы можно будет в считаные секунды, ведь для этого потребуется лишь выделить интересующие столбцы и щелкнуть на кнопке Trim Spaces (рис. 7).

 

Рисунок

Рис. 7. Удаление ненужных пробелов с помощью Trim Spaces

Стоит отметить, что функция удаления ненужных пробелов предусмотрена и в уже упоминавшемся плагине ASAP Utilities.

Исправление опечаток

Обычно данные в документах Excel представлены большим количеством строк, в которых часть информации (например, производитель, наименование модели и т.п.) многократно повторяется. В то же время пользователи, отвечающие за заполнение таблиц, далеко не всегда придерживаются при вводе принципа единообразия вводимых данных — то есть, например, вместо текста Core 2 Duo в одних строках может оказаться Core2Duo, в других — CORE2 DUO и т.д. Помимо этого возможны и банальные опечатки, когда какой-то из символов будет пропущен либо заменен другим. В итоге при аналитической обработке данных, например при создании сводных таблиц, возникает масса проблем, и все подобные неточности приходится устранять вручную, что отнимает очень много времени.

Существенно ускорить процесс исправления подобных неточностей совсем несложно — достаточно взять на вооружение плагины Similar Data Finder for Excel (; 3,6 Мбайт; 1100 руб.) и Fuzzy Duplicate Finder (; 2,26 Мбайт; 29,95 долл.). С их помощью выявить ячейки с похожими значениями можно в считаные секунды. А затем столь же быстро исправить значения в тех ячейках, где они оказались неверными (рис. 8 и 9), либо удалить такие ячейки, либо выделить их (последнее требуется, если перед исправлением вам необходимо уточнить, какой из вариантов следует считать верным, и возможно только в Similar Data Finder for Excel).

 

Рисунок

Рис. 8. Исправление схожих данных с помощью Similar Data Finder for Excel

Рисунок

Рис. 9. Поиск и исправление опечаток в Fuzzy Duplicate Finder

Подобная корректировка может производиться автоматически (исправляются неверные значения сразу во всех схожих ячейках) либо вручную, когда правка осуществляется в отношении отдельных ячеек. Наличие возможности ручной правки не менее важно, ведь во многих случаях исправлять требуется не все выявленные ячейки, а лишь часть из них. Дело в том, что наименования моделей продукции могут быть весьма схожими. Например, разные модели блоков бесперебойного питания Back 500 IpponPowerPro, Back 600 IpponPowerPro и Back 800 IpponPowerPro окажутся отнесенными данными плагинами к одной группе ячеек. А это означает, что корректно изменить в соответствующем столбце, например, вариант написания Back 800 IpponPOWERPRO на вариант Back 800 IpponPowerPro в автоматическом режиме окажется невозможным — придется прибегать к корректировке значений вручную. Но все равно это гораздо быстрее, чем работать без применения плагинов.

Работа с книгами

Быстрый доступ к нужным книгам

В процессе работы с таблицами приходится многократно открывать те или иные папки и загружать из них нужные книги, что в случае слишком большого уровня вложений может отнимать немало времени. Для ускорения доступа к часто открываемым папкам с табличными документами либо напрямую к табличным документам можно прямо на рабочем столе разместить ведущие к ним ярлыки. Однако при большом количестве таких документов (а также иных ярлыков на рабочем столе) это не ускорит навигацию, а, наоборот, усложнит ее, ведь ориентироваться в большом числе ярлыков сложно. Пользователи Excel для решения данной проблемы могут воспользоваться бесплатным плагином Favorite Bookmarks (; 509 Кбайт). С его помощью несложно создать закладки на избранные папки с документами либо отдельные файлы (рис. 10), что позволит в дальнейшем открывать соответствующие папки либо загружать напрямую файлы одним кликом. Созданным набором закладок можно управлять, перемещая их в желаемом порядке.

 

Рисунок

Рис. 10. Окно Favorite Bookmarks
с набором закладок

Эффективное управление книгами

Возможности быстрого открытия книг для организации эффективной работы с ними недостаточно — требуется еще иметь инструментарий, который бы обеспечивал удобное управление книгами. Такой инструментарий может предоставить плагин Explorer for Microsoft Excel (; 3,36 Мбайт; 19,95 долл.), предназначенный для удобного управления листами и книгами. После его установки все открытые книги и листы автоматически отображаются на панели плагина в виде дерева (рис. 11) — эта панель располагается в левой части экрана и включается/выключается нажатием комбинации клавиш Ctrl + Shift + N. Через данную панель можно легко переключаться между листами и книгами, перетаскивать листы из одной книги в другую, переименовывать листы и книги, сортировать их и удалять одним щелчком мыши. Возможна также работа со ссылками, комментариями, гиперссылками, ошибками и ячейками, имеющими условное форматирование.

 

Рисунок

Рис. 11. Окно Excel с открытой панелью Explorer for Microsoft Excel

Выявление и восстановление нарушенных ссылок в связанных книгах

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

К сожалению, использование в рабочих документах связанных данных может привести к тому, что в один прекрасный день ссылки на другие документы окажутся неработоспособными (рис. 12) и подобные отчеты уже нельзя будет сформировать. Произойти это может вследствие того, что какой-то из исходных файлов оказался переименованным или перемещенным либо был перемещен сам итоговый файл со связями. При возникновении такой ситуации придется выяснить, что произошло с исходными файлами, и затем вручную вновь указать их — это потребует дополнительных временных затрат. Имеется более быстрый способ решения проблемы — воспользоваться плагином Fix Broken Links for Excel (; 2,8 Мбайт; 1600 руб.).

 

Рисунок

Рис. 12. Сообщение Excel о нарушении связей

С его помощью связи могут быть восстановлены в автоматическом режиме. Это означает, что вручную указывать новые файлы-источники не придется — программа сама проанализирует папку, в которой они находятся, выявит их и восстановит нарушенные связи (рис. 13). При активном использовании связанных файлов, особенно если их много и ситуации с перемещением/переименованием имеют место довольно часто, данная возможность окажется весьма кстати. Однако еще более полезной представляется другая функция — Fix Broken Links for Excel (копирование и перенос связанных файлов с сохранением работоспособности связей). Данная возможность предотвратит проблемы с нарушением связей при изменении структуры папок на рабочем компьютере и в случае использования связанных документов на нескольких компьютерах — например на рабочем компьютере и ноутбуке.

 

Рисунок

Рис. 13. Процесс восстановления связей в Fix Broken Links for Excel

Объединение, разделение и сравнение данных

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

При работе с таблицами нередко возникает необходимость в объединении находящихся в разных файлах данных. Подобную операцию осуществить в Excel несложно — достаточно открыть исходные таблицы и скопировать через буфер обмена нужную информацию в итоговый файл. Однако при большом количестве файлов или для больших таблиц подобная операция становится достаточно трудоемкой и отнимает много времени. Если такие работы приходится проводить довольно часто, то полезно установить подходящие надстройки. Это могут быть плагины Advanced Consolidation Manager (; 3,6 Мбайт; 1100 руб.), Merge Tables Wizard (; 2,04 Мбайт; 39,95 долл.) или Tables Transformer for Excel (; 3,2 Мбайт; 1100 руб.).

Плагин Advanced Consolidation Manager поможет скопировать листы с данными из нескольких файлов в одну рабочую книгу (рис. 14 и 15) либо объединить данные из разных файлов на один лист. Объединение данных может производиться с использованием итоговых функций: Sum, Count, Average, Max и т.д.

 

Рисунок

Рис. 14. Копирование листов из двух файлов в новую рабочую книгу
в Advanced Consolidation Manager

Рисунок

Рис. 15. Результат копирования листов из двух файлов в новую рабочую книгу
в Advanced Consolidation Manager (вверху — исходные таблицы, внизу — итоговая)

Плагин Merge Tables Wizard работает не с листами, а с указанными на них данными и позволяет объединять данные из нескольких файлов в одном листе (рис. 16 и 17). Технически это будет чуть медленнее, поскольку данные придется выделять, но зато появляется возможность брать не все листы полностью, а только нужную информацию с них. Помимо обычного объединения информации из нескольких таблиц, этот плагин может быть применен для сравнения однотипных данных и их обновления в рабочей таблице на основе информации базовой таблицы.

 

Рисунок

Рис. 16. Добавление в первую таблицу столбца из второй таблицы в Merge Tables Wizard

Рисунок

Рис. 17. Результат добавления в первую таблицу столбца из второй таблицы в Merge Tables Wizard
(вверху — исходные таблицы, внизу — итоговая)

Плагин Tables Transformer for Excel может объединить данные из двух таблиц в одну (рис. 18) с группировкой по наименованию конкретного поля. При этом он позволяет включать в общую таблицу не все столбцы из объединяемых таблиц, а только требуемые.

 

Рисунок

Рис. 18. Слияние двух таблиц с помощью Tables Transformer for Excel

Объединение данных из нескольких ячеек

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

В таких ситуациях на помощь придут плагины ASAP Utilities, PLEX и Merge Cells Wizard (; 2,04 Мбайт; 29,95 долл.). С их помощью подобное слияние может быть осуществлено одним кликом мыши (рис. 19).

 

Рисунок

Рис. 19. Результат слияния ячеек в Merge Cells Wizard (
слева — исходная таблица, справа — итоговая)

Разделение данных по таблицам

Если вам часто приходится формировать на базе одной таблицы несколько новых, включая в них некоторую информацию из исходной таблицы, то стоит обратить внимание на упоминавшиеся плагины Advanced Consolidation Manager, Tables Transformer for Excel и ASAP Utilities.

Advanced Consolidation Manager окажется полезным для копирования данных в разные файлы с группировкой по страницам (рис. 20) — то есть первые листы с данными из указанных файлов будут объединены в первой книге, вторые — во второй и т.д.

 

Рисунок

Рис. 20. Результат копирования листов из четырех файлов в два с группировкой
по страницам в Advanced Consolidation Manager
(вверху — исходные таблицы, внизу — итоговые)

Плагин Tables Transformer for Excel поможет быстро разделить одну таблицу на несколько новых. В каждой новой таблице названия столбцов исходной таблицы продублируются, а сами таблицы окажутся размещенными в новой либо рабочей книге (на текущем или новом листе). Предусмотрены два принципа проведения подобного разбиения. Во-первых, исходная таблица разбивается с учетом указанного количества строк — тогда в каждой новой таблице окажется соответствующее число строк и браться они будут последовательно из исходной таблицы. Это может пригодиться, например, для получения на базе одного годового отчета по продажам с данными за каждый месяц четырех квартальных отчетов.

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

 

Рисунок

Рис. 21. Результат разделения таблицы по производителю
(столбец Vendors CPU) в Tables Transformer for Excel (первая таблица —
исходная, вторая и третья?— итоговые)

Плагин ASAP Utilities можно применить для формирования новых Excel-документов на основе листов рабочей книги, когда каждый из листов исходной книги помещается в отдельный файл (рис. 22).

 

Рисунок

Рис. 22. Экспорт листов в отдельные файлы
с помощью ASAP Utilities (вверху — процесс экспорта,
внизу — итоговые файлы)

Сравнение файлов с данными

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

Детальное сравнение всех позиций таблицы при поиске удаленных, добавленных и измененных ячеек вручную крайне трудоемко, а если таблица большая (что на практике чаще всего и наблюдается) — вообще нереально. Гораздо разумнее для проведения подобных операций прибегнуть к помощи специализированных решений Excel Compare (; 1,17 Мбайт; 290 руб.) и Compare Spreadsheets for Excel (; 8,7 Мбайт; 1600 руб.), которые позволят решать подобные задачи автоматически.

Оба решения умеют сравнивать диапазоны ячеек из выбранных листов указанных книг и формируют отчеты различий (рис. 23 и 24), отображающие изменения, — в данных отчетах добавленные, удаленные и измененные ячейки подсвечиваются либо выделяются. При этом в отчете Excel Compare отображаются лишь соответствующие строки, в которых такие изменения были найдены, а в отчете Compare Spreadsheets for Excel данные строки показываются вместе со всей прочей имеющейся в таблицах информацией.

 

Рисунок

Рис. 23. Отчет различий, полученный при сравнении
двух версий прайс-листа с помощью Excel Compare

Рисунок

Рис. 24. Результат сравнения двух версий прайс-листа в Compare Spreadsheets for Excel

Утилита Excel Compare умеет сравнивать все одноименные рабочие листы двух файлов, все файлы, которые расположены в двух выбранных папках, либо все файлы на листах созданного в среде данной утилиты проекта. Возможности плагина Compare Spreadsheets for Excel ограничены сравнением лишь двух листов двух выбранных книг, но зато при сравнении он может учитывать формат ячеек.

Выявление записей-дубликатов

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

При больших объемах данных провести подобные операции вручную проблематично: затраты времени окажутся слишком велики и не исключена вероятность удаления нужных данных. Гораздо лучше воспользоваться подходящими плагинами, например Remove Duplicates from Excel (; 1,8 Мбайт; 1100 руб.) или Duplicate Remover (; 2,16 Мбайт; 29,95 долл.). Оба решения предназначены для поиска и обработки дубликатов в документах Excel и позволяют быстро и с минимальными усилиями найти все повторяющиеся элементы, а затем выделить их (рис. 25 и 26), изменив форматирование, либо сразу удалить. Дополнительно к этому плагин Remove Duplicates from Excel умеет скрывать повторяющиеся данные, а Duplicate Remover может скопировать или переместить такие записи в новую рабочую книгу либо на новый/существующий рабочий лист. Поиск через данные плагины можно проводить как с целью выявления полностью идентичных строк, так и для того, чтобы найти ячейки с повторяющимися данными в конкретных столбцах или строках.

 

Рисунок

Рис. 25. Проведение поиска ячеек-дубликатов в столбце в Duplicate Remover

Рисунок

Рис. 26. Результат поиска ячеек-дубликатов
в столбце в Duplicate Remover
(дублирующиеся ячейки выделены цветом)

Если задача проще: нужно избавиться от дубликатов не на уровне таблицы, а, например, внутри фрагмента столбца, то есть выявить в диапазоне уникальные значения, — то тут возможны разные варианты решения проблемы. Можно создать по данному столбцу сводную таблицу, в которой автоматически окажутся отображенными только уникальные значения, либо воспользоваться уже упоминавшимся плагином PLEX, умеющим извлекать из указанного диапазона уникальные элементы (рис. 27).

 

Рисунок

Рис. 27. Выявление уникальных элементов в указанном диапазоне с помощью PLEX

Работа со сводными таблицами

Управление

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

Можно ускорить выполнение некоторых подобных операций, установив бесплатный плагин Pivot Table Helper (; 1,04 Мбайт). После его инсталляции на панели инструментов появится ряд дополнительных кнопок (рис. 28), обеспечивающих быстрое выделение отдельных элементов сводной таблицы (либо всей таблицы целиком), определение числового формата ячеек, изменение ширины столбцов и строк для удобного просмотра находящихся в них данных, удаление ранее примененного варианта автоматического форматирования и т.д.

 

Рисунок

Рис. 28. Панель Pivot Table Helper

Можно воспользоваться более широкими возможностями платного плагина Pivot Table Assistant (; 29,95 долл.). При инсталляции он внедряет в Excel дополнительную панель (рис. 29), кнопки которой позволяют выполнять одним кликом мыши немало разнообразных операций. Например, быстро изменять форматы столбцов с данными (делая данные целыми, устанавливая в них один или два знака после запятой, назначая им процентный формат и т.д.), изменять размеры столбцов и закреплять строки и столбцы заголовков для упрощения просмотра. А также обновлять данные сводной таблицы, задавать заголовки и назначать область печати сводной таблицы, создавать рамки по правому и нижнему краям каждой печатной страницы сводной таблицы для того, чтобы с распечаткой было удобнее работать.

 

Рисунок

Рис. 29. Панель Pivot Table Assistant

Автоматическое форматирование

В Excel предусмотрено автоматическое форматирование сводных таблиц (команда Формат -> Автоформат), однако выбор предустановленных шаблонов невелик. Поэтому чаще всего приходится прибегать к форматированию сводных таблиц вручную, но при их перестройке (например, при добавлении или удалении полей) часть сделанных настроек форматирования может теряться — в итоге приходится затрачивать дополнительное время на повторное форматирование. Еще более актуально то, что каждую новую сводную таблицу вновь нужно форматировать, поскольку запомнить сделанные настройки форматирования в виде шаблона в Excel невозможно — такой функциональности там просто нет.

Поэтому при активном использовании сводных таблиц полезно установить плагин AutoFormat for PivotTables (; 3,65 Мбайт; 29,95 долл.). В его поставку входит набор предустановленных и весьма привлекательных шаблонов, список которых может быть расширен путем скачивания дополнительных шаблонов с сайта программы либо за счет разработки пользовательских шаблонов. Последнее как раз и представляется самым интересным, поскольку после разработки нужных в работе шаблонов форматирование вновь создаваемых таблиц уже не будет отнимать столько времени — для его проведения достаточно будет просто выбрать нужный шаблон из списка и щелкнуть по кнопке Apple(рис. 30). Количество создаваемых пользовательских шаблонов неограничено, и для удобства работы шаблоны можно объединять в группы. Поддерживается импорт и экспорт пользовательских шаблонов, что позволяет восстанавливать их после переустановки системы и переносить между компьютерами.

 

Рисунок

Рис. 30. Проведение автоформатирования сводной таблицы
с помощью AutoFormat for PivotTables

Создание графиков и диаграмм

Если создаваемые в Excel графики и диаграммы вам кажутся недостаточно эффектными и выразительными, то стоит обратить внимание на решение PowerPlugs:Charts (ftp://ftp.crystalgraphics.com/cgi_powerplugs/chartsdemo.exe; 17,6 Мбайт; 99 долл.). С его помощью можно создавать впечатляющие двух- и трехмерные графики и диаграммы непосредственно в Excel, при этом возможность формирования диаграмм традиционно, то есть через Microsoft Graphs, останется доступной.

В PowerPlugs:Charts свыше ста встроенных шаблонов двух- и трехмерных графиков, разбитых на девять категорий (Area, Bar, Bubble, Column, Histograms, Lines, Pies, Scatter и Surface). Данный список шаблонов можно пополнить за счет пользовательских шаблонов, разработка которых окажется полезной для автоматизации процесса подготовки серий диаграмм в едином стиле оформления. Сделать верный выбор при таком разнообразии шаблонов непросто — помочь в этом может инструмент Data Analyzer, который проанализирует конкретный набор данных и выдаст заключение о том, какие типы диаграмм в этом случае предпочтительны. Создание (рис. 31) и редактирование диаграмм реализовано просто и удобно благодаря широкому набору мощных средств редактирования, возможности полного контроля над отображением и предварительному просмотру результата после изменения любых параметров. При редактировании, помимо настройки базовых параметров (изменения размеров, включения/выключения отображения разнообразных данных, изменения цветов и особенностей форматирования текста и т.п.), предоставляется ряд дополнительных возможностей, позволяющих добиваться очень интересных результатов. Например, можно менять степень прозрачности объектов, добавлять обводки, настраиваемые градиентные и текстурные заливки, совмещать разные типы графиков в комбинированные диаграммы, имитировать трехмерное представление диаграмм и т.д.

 

В начало В начало
Источник: http://compress.ru/article.aspx?id=20505





Как сделать несколько таблиц на одном листе

Как сделать несколько таблиц на одном листе

Как сделать несколько таблиц на одном листе

Как сделать несколько таблиц на одном листе

Как сделать несколько таблиц на одном листе

Как сделать несколько таблиц на одном листе

Как сделать несколько таблиц на одном листе

Как сделать несколько таблиц на одном листе

Читать далее: