Управление структурой данных

Разделение текста по столбцам. При импортировании в Excel данных из других приложений (например, из Word) в ячейки рабочего листа могут попасть не отдельные поля, а целые записи. Например, в ячейках A1:A3 (рис. 6.17,а) содержатся данные, которые можно разделить на четыре столбца. В первом будут фамилии, потом – имена, даты рождения и названия ВУЗов.

Разделение текста по столбцам можно выполнить, перемещая отдельные блоки информации из ячеек A1:A3 в B1:B3, C1:C3 и D1:D3. В результате может быть по­лучена таблица, показанная на рис. 6.17,б.

Рис. 6.17. Импортированные данные до разделения по столбцам (а) и после разделения (б)

Excel позволяет отказаться от рутинного перетаскивания данных и разделить текст по столб­цам с помощью мастера текстов. Для этого нужно выполнить следующие операции:

Выделить диапазон ячеек с импортированными данными (в нашем примере это A1:A3).

Выбрать команду Данные 4 Текст по столбцам. Откроется первое окно мастера текстов:

Рис. 6.18. Первое окно мастера текстов

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

3. В первом окне мастера текстов щелкнуть на кнопке Далее >. В результате откроется второе окно мастера текстов:

Рис. 6.19. Второе окно мастера текстов

Поскольку в исходном тексте (см. рис. 6.17,а) поля отделяются друг от друга пробелами, в группе Символом-разделителем является: нужно установить флажок Пробел. При этом в группе Образец разбора данных появятся вертикальные линии (см. рис. 6.19).

4. Во втором окне мастера текстов щелкнуть на кнопке Далее >. В результате откроется третье окно мастера текстов:

Рис. 6.20. Третье окно мастера текстов

По умолчанию в этом окне выбран наиболее универсальный Общий формат данных. Его целесообразно оставить для всех столбцов, так как в этом случае осуществляется автоматическое преобразование числовых данных в числа; значений, имеющих формат дат, – в даты; а всех остальных данных – в текст. В противном случае нужно будет по очереди выделять столбцы и задавать для них формат с помощью переключателей.

Если нужно сохранить исходную информацию в том виде, который она имеет после импортирования (см. диапазон A1:A3 на рис. 6.17,а), то таблицу с разделенными текстовыми дан­­ными можно поместить вне исходного диапазона. Для этого в текстовое поле Поместить в нужно ввести адрес левого верхнего угла создаваемой таблицы (например, D5).

5. В третьем окне мастера текстов щелкнуть на кнопке Готово. В результате будет создана таблица, полями которой являются элементы исходных записей.

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

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

Если поля были раздвинуты верно, во втором окне мастера текстов отобразятся верти­кальные линии разделения столбцов. Пользователь может удалять эти линии двойным щелчком, буксировать их, или добавлять свои линии щелчком мыши.

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

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

Настроить необходимый вид рабочего листа. В нашем примере нужно скрыть все записи для судов Керчь и Трепанг, а также столбец Груз. Для этого следует выделить в исходном списке строки 3, 5, 6, 7 и выбрать команду Формат 4 Строка 4 Скрыть; а затем выделить столбец C и выбрать команду Формат 4 Столбец 4 Скрыть.

Рис. 6.21. Исходный список (а), представление (б) и окна настройки (в, г)

Выбрать команду Вид 4 Представления. В результате откроется диалоговое окно Представления (рис. 6.21,в).

В окне Представления щелкнуть на кнопке Добавить. В результате окно Представления закроется и откроется окно Добавление представления (рис. 6.21,г).

В текстовое поле Имя ввести имя создаваемого представления (в нашем примере – Витязь) и щелкнуть на кнопке ОК. В результате представление будет сохранено под заданным именем, а окно Добавление представления закроется. Исходному списку грузоперевозок после этого можно вернуть исходный вид, щелкая на кнопке Отменить.

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

Чтобы использовать какое-либо из созданных представлений, следует выбрать команду Вид 4 Представления, затем в открывшемся окне (см. рис. 6.21,в) выбрать имя нужного представления и щелкнуть на кнопке Применить.

Ненужное представление легко удалить. Для этого необходимо выбрать его имя в списке представлений (см. рис. 6.21,в), после чего щелкнуть на кнопке Удалить.

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

Группировка данных. Если данные списка имеют общие атрибуты, такие данные можно сгруппировать, то есть представить список в виде иерархической структуры и отображать в различных ситуациях данные разных уровней детализации.

Рис. 6.22. Список перевозки пассажиров судами Витязь и Керчь

В качестве приме­ра на рис. 6.22 показан список перевозки пассажиров двумя судами в первом полугодии 2003 г. В этом списке общими атрибутами строк являются месяцы и кварталы, а общими атрибутами столбцов – количества пассажиров, перевезенных в разных направлениях.

Для строк на самом верхнем иерархическом уровне находится Полугодие. В него входит группа из двух элементов: Квартал 1 и Квартал 2. Каждому элементу этой группы подчинены по три месяца. То есть иерархия групп по вертикали трехуровневая.

Для столбцов на верхнем уровне находится общее количество перевезенных пассажиров (ВСЕГО). В него входит группа из двух элементов: Ялта – Сочи и Сочи – Ялта. То есть иерархия групп по горизонтали двухуровневая.

Чтобы сгруппировать данные строк приведенного на рис. 6.22 списка, необходимо выпол­нить следующие операции:

Выделить строки 3, 4, 5 и выбрать команду Данные 4 Группа и структура 4 Группиро­вать. При этом левее номеров строк появится область символов структуры с кноп­кой управления структурой (см. рис. 6.23). Знак "минус" на кнопке означает, что группа Январь, Февраль, Март развернута. Щелчок на кнопке скрывает строки 3, 4, 5. При этом кнопка превращается в (знак "плюс" на кнопке означает, что группа свер­ну­та). Щелчок на кнопке разворачивает группу. При этом кнопка превращается в .

Одновременно с кнопкой в верхней части области символов структуры строк поя­вятся кнопки . Они также служат для управления структурой данных. Щел­чок на кнопке сворачивает группу, а щелчок на кнопке разворачивает ее.

Рис. 6.23. Список перевозки пассажиров с кнопками управления структурой

Выделить строки 7, 8, 9 и выбрать команду Данные 4 Группа и структура 4 Груп­пи­ровать. При этом в области символов структуры строк появится вторая кнопка , предназначенная для сворачивания группы Апрель, Май, Июнь (см. рис. 6.23). Если щелкнуть на этой кнопке, строки 7, 8, 9 будут скрыты, а кнопка превратится в (рис. 6.24). Щелчок на кнопке развернет группу Апрель, Май, Июнь. При этом кноп­ка превратится в .

Рис. 6.24. Список со свернутой группой строк и свернутой группой столбцов

Выделить строки с 3-й по 10-ю включительнои выбрать команду Данные 4 Группа и структура 4 Груп­пи­ровать. При этом в области символов струк­туры строк появится третья кнопка , предназначенная для сворачивания и разворачивания данных, находящихся в строках 3, 4,…, 9, 10 (см. рис. 6.23). Эта кнопка обеспечивает группировку самого высокого уровня. Если щелкнуть на ней, строки с 3-й по 10-ю будут скрыты, а кнопка превратится в (рис. 6.25,б). Щелчок на кнопке возвращает списку его исходный вид, а кнопка снова превращается в .

Одновременно с появлением третьей кнопкой в верхней части области символов структуры строк произойдет замена кнопок на (см. рис. 6.23). Щелчок на кнопке свора­чивает все данные, кроме итогов за полугодие (см. рис. 6.25,б); щелчок на кнопке сворачивает только ежемесячные итоги (см. рис. 6.25,а); а щелчок на кнопке полностью разворачивает все строки (см. рис. 6.23).

Рис. 6.25. Список с двумя свернутыми группами строк (а) и полностью свернутый список (б)

Теперь рассмотрим, как можно группировать данные столбцов приведенного на рис. 6.22 списка. Для выполнения этой операции необходимо:

1. Выделить столбцы B, C и выбрать команду Данные 4 Группа и структура 4 Груп­пиро­вать. При этом над именами столбцов появится область символов структуры с кнопкой управления структурой (см. рис. 6.23). Знак "минус" на кнопке означает, что группа данных Ялта – С о чи и Сочи – Ялта развернута. Щелчок на кнопке скрывает столбцы B и C; при этом кнопка превращается в (см. рис. 6.25). Знак "плюс" на кнопке означает, что группа свер­ну­та. Щелчок на кнопке разворачивает группу.

Вместе с кнопкой в левой части области символов структуры столбцов по­явятся кнопки и (см. рис. 6.23). Они также служат для управления структурой. Щел­чок на кнопке сворачивает группу, а щелчок на кнопке разворачивает ее.

2. Выделить столбцы F, G и выбрать команду Данные 4 Группа и структура 4 Группиро­вать. При этом в области символов структуры столбцов появится вторая кнопка , предназначенная для сворачивания столбцов F и G (см. рис. 6.23). На рис. 6.24 показано, какой вид будет иметь список после щелчка на этой кнопке.

Наряду с рассмотренными способами создания средств управления структурой, для группировки данных можно ис­пользовать предусмотренные в Excel средства автоматизации. Если выбрать команду Данные 4 Группа и структура 4 Создание структуры, то для всех находящихся на рабочем листе списков с иерархической структурой данных будут автоматически созданы области символов структуры с соответствующими средствами управления. При этом адрес активной ячейки никакого значения не имеет. Если нужно создать средства управления структурой лишь для какой-то одной таблицы, то перед выбором команды эту таблицу нужно выделить.

Поскольку средства управления структурой данных занимают часть окна документа, мо­жет возникнуть необходимость скрыть их. Проще всего это сделать, нажав комбинацию клавиш Ctrl + 8. Повторное нажатие этой же комбинации клавиш восстанавливает отображение средств управления структурой.

В случаях, когда нужно не скрыть, а удалить все средства управления структурой, следует воспользоваться командой Данные 4 Группа и структура 4 Удалить структуру. Если же требуется удалить символы управления структурой одной группы, нужно выделить эту группу данных, а потом выбрать команду Данные4 Группа и структура 4 Раз­группировать.

Вычисление итогов. Для любого надлежащим образом упорядоченного списка Excel предоставляет возможность автоматического вычисления итоговых результатов в заданных столбцах. При этом в список вставляются строки промежуточных итогов, а в самом низу – строка общих итогов. Эта задача решается с использованием команды Данные 4 Итоги. При­менение этой команды возможно при выполнении двух условий:

¡ В первой строке списка должны находиться имена полей.

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

Рассмотрим последовательность действий, необходимых для вычисления итогов, на при­мере списка, показанного на рис. 6.21,а. Для получения итоговых результатов нужно:

Отсортировать список по возрастанию или убыванию значений того столбца, в котором имеются группы повторяющихся данных. В нашем примере нужно сортировать список по значениям столбца Судно, чтобы сгруппировать суда по их названиям (рис. 6.26,б), а затем вычислить общее количество перевезенного груза и суммарную прибыль для каждого судна и для всех судов вместе[8].

Убедиться, что активна одна из ячеек списка, и выбрать команду Данные 4 Итоги. В ре­зультате откроется диалоговое окно Промежуточные итоги (рис. 6.26,а).

В раскрывающемся списке При каждом изменении в: выбрать заголовок того столбца, по значениям которого был отсортирован список.

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

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

Рис. 6.26. Диалоговое окно Промежуточные итоги (а) и результат вычисления итогов (б)

С помощью флажков задать параметры отображения итогов:

— Флажок Заменить текущие итоги предусмотрен для тех случаев, когда команда Дан­ные 4 Итоги применяется к списку с ранее вычисленными итогами. Если, например, при повторном вычислении итогов изменено задание (вместо сумм вычисляются средние значения), то при установленном флажке старые итоги будут заменены новыми, а при снятом флажке к старым итогам добавятся новые.

— Флажок Конец страницы между группами устанавливается, если нужно чтобы после каждой группы данных был вставлен символ конца страницы. В результате этого при выводе таблицы на печать каждая группа данных вместе со своими итогами будет напечатана на отдельной странице.

— Флажок Итоги под данными устанавливается, если нужно чтобы промежуточные и общие итоги отображались под соответствующими данными. Если флажок снять, итоги будут предварять данные. Обычно этот флажок установлен.

Когда все настройки выполнены, остается щелкнуть на кнопке ОК. В результате исходный список (см. рис. 6.21,а) обретет вид, показанный на рис. 6.26,б. Назначение всех средств управления структурой рассмотрено выше в разделе Группировка данных.

Если необходимость в итоговых данных отпала, соответствующие строки и средства упра­вления структурой можно удалить. Для этого следует выделить одну из ячеек списка, выбрать команду Данные 4 Итоги, а затем в открывшемся окне Промежуточные итоги (см. рис. 6.26,а) щелкнуть на кнопке Убрать все.

Консолидация данных. Целью консолидации является объединение нескольких одно­типных таблиц в одну таблицу с одновременным вычислением итоговых результатов. Например, если имеются таблицы получения товаров в январе, феврале, марте и т.д., то с использованием команды Данные 4 Консолидация можно создать объединен­­­­ную таблицу, где в качестве итогов будет вычислено общее количество каждого товара, полученного за эти месяцы. Исходные еже­месячные таблицы при этом останутся неизменными.

Объединяемые таблицы могут располагаться в разных областях рабочего листа, на разных рабочих листах и даже в разных рабочих книгах. Всего можно объединить до 255 таб­лиц.

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

Рис. 6.27. Диалоговое окно Консолидация (а) и результат консолидации (б)

Последовательность действий, выполняемых при консолидации данных, поясним на при­мере объединения таблиц перевозки грузов судами в январе и феврале 2003 г. Будем считать, что январская таблица расположена на Листе1, в диапазоне A1:D8 (см. рис. 6.21,а), а февральская – на Листе2, в диапазоне B1:E6. Задачей консолидации является создание на Листе3 объединенной таблицы, в которой будут определены суммарный грузооборот и суммарная прибыль для судов Витязь, Керчь и Селенга за два месяца (рис. 6.27,б). Чтобы решить поставленную задачу, нужно выполнить следующие операции:

Щелчком мыши сделать активной ту ячейку, где должен находиться верхний левый угол консолидированной таблицы. В нашем примере это ячейка A1 на Листе3.

В меню Данные выбрать команду Консолидация. В результате откроется диалоговое окно Консолидация (рис. 6.27,а).

Если заданная по умолчанию операция суммирования числовых данных (Сумма) не подходит для вычисления итогов, в раскрывающемся списке Функция: можно выбрать другую операцию: Количество, Среднее, Максимум, Минимум или др. В нашем примере оставлена операция Сумма.

Щелчком мыши активизировать поле Ссылка, после чего перейти на тот рабочий лист, где расположена первая таблица, и выделить ее.В результате в поле Ссылка по­явится первый диапазон: Лист1!$A$1:$D:$8.

В окне Консолидация щелкнуть на кнопке Добавить. Диапазон Лист1!$A$1:$D:$8 при этом скопируется из поля Ссылка в поле Список диапазонов.

Перейти на тот рабочий лист, где расположена вторая таблица, и выделить ее. В результате в поле Ссылка появится второй диапазон: Лист2!$B$1:$E:$6.

В окне Консолидация щелкнуть на кнопке Добавить. Диапазон Лист2!$B$1:$E:$6 при этом скопируется из поля Ссылка в поле Список диапазонов (см. рис. 6.27,а).

С помощью флажков задать параметры консолидации:

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

— Флажок Значения левого столбца устанавливается, чтобы в консолидированной таблице отображались названия тех объектов, для которых вычисляются итоги. В нашем примере – это названия судов Витязь, Керчь и Трепанг.

— Флажок Создавать связи с исходными данными устанавливается, если нужно что­бы при изменении данных в исходных таблицах автоматически выполнялось такое же изменение консолидированных данных.

Когда все перечисленные настройки выполнены, необходимо щелкнуть на кнопке ОК. В результате на Листе3 появится итоговая таблица со средствами управления структурой данных.

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