<<
>>

2.5. Использование сводных таблиц


Очень полезным средством анализа и обработки данных являются сводные таблицы. С помощью сводных таблиц можно быстро и без труда обобщать большие объемы данных, в том числе и расположенные на разных листах рабочей книги.
В результате громоздкие списки могут быть представлены в удобном виде. Отчеты сводных таблиц допускают различные варианты форматирования, соргировки, группирования данных и так далее. Исходной информацией для сводных таблиц могут быть не только списки Excel, но и внешние базы данных.
2.5.1. Создание сводных таблиц
Чтобы создать сводную таблицу, выберите команду мен го Данные Сводная таблица. Будет открыт первый диалог мастера создания сводных таблиц и диаграмм (Рис. 2.35).
В данном диалоге нужно выбрать, что является источником данных для со-здания сводной таблицы. Установив переключатель в положение В списке или базе данных, вы говорите программе, что сводная таблица будет построена на основе списка. Установив переключатель в положение Из внешнего источника данных,
Со деть таблицу- на .-..¦поведанные н
ґ*,в списке hw данных Microsoft Excel' ван^шмнистоииихеданных в и^тшже^^м^х консолидации Г е другой сеодной г аОлнш:- но* сводной диаграмме

вид сдаваем го отчета їі сводная таблица
(со сводной таблицей)
JI ГЛ0К' | Сттам
Рис. 2.35. Первый диалог мастера
вы выберете создание сводной таблицы из импортируемых данных. Если пе-реключатель установлен в положение В нескольких диапазонах консолидации, вы создадите сводную таблицу на основе нескольких диапазонов рабочего листа или списков из нескольких листов рабочей книги. Если вы хотите построить сводную таблицу на основе другой сводной таблицы, установите переключатель в нижнее положение В другой сводной таблице.
В Excel 2000 и 2002 в этом диалоге есть еще один переключатель для
gM»^ и^ит- ши" 2 и» з Ум* «г* дияплэом, садвржчии Й ипоюные ДвНиые. ?члпкк> ^ I 1 a Отием | [CjSSK* * j| Сотмо Рис. 2.36. Выбор диапазона
ра вида создаваемого отчета. Можно создать сводную таблицу или сводную диаграмму. В Excel 97 можно создавать только сводные таблицы. Установите переключатель в положение Сводная таблица и нажмите кнопку Далее, чтобы продолжить создание сводной таблицы. Будет открыт второй диалог мастера (Рис. 2.36).
В этом диалоге вам предлагается выделить диапазон ячеек, содержащих ис-ходные данные для построения сводной таблицы. По умолчанию будет выделена вся область, занимаемая таблицей, но вы можете указать область, которая вам необходима. Для этого нажмите кнопку в правой части поля топа Диапазон, и диалог будет свернут. Выделите ячейки для построения сводной таблицы и нажмите кнопку чтобы восстановить диалог.
Если вы хотите использовать в качестве данных для сводной таблицы другую таблицу, нажмите кнопку Обзор, и будет открыт диалог для выбора файла. Укажите в этом диалоге папку и файл, содержащий нужную вам таблицу, и нажмите кнопку ОК этого диалога. Ссылка на диапазон ячеек нужной вам таблицы появится в поле ввода Диапазон. Нажмите кнопку Далее, чтобы продолжить создание сводной таблицы.
Если вы создаете сводную таблицу из нескольких листов рабочей книги и выбрали в первом диалоге положение переключателя В нескольких диапазонах консолидации, второй диалог мастера будет иметь несколько иной вид (Рис.
2.37, слева).
В данном диалоге нужно установить переключатель, определяющий, как со-здавать поля сводной таблицы. Верхнее положение переключателя означает создание только одного поля страницы для отчета сводной таблицы. Нижнее положение переключателя означает назначение диапазонам разных имен.
После установки переключателя нажмите кнопку чтобы продолжить
работу. Появится следующий диалог мастера для указания диапазонов ячеек на разных листах рабочей книги (Рис. 2.37, справа). В поле ввод а Диапазон, нужно указать адреса ячеек, образующих сводную таблицу. Нажав кнопку в правой - ЩШ! і
где [нога тсиены дньппэаны таблиц. UQTDPde СлегуЕТ РЙ^С ПНДНрг.ИТь' Тигсовь е операции"'SAS2; StlS4 Я Добй» ить \ гДвокть I обавр" | ?імсон диалмонов; Отмена | > 1! Готово | дашчгая***'"


Сз&щуі таблицу можно на
диапазонов из одного или нескольких листов. Каждый диапазон ні и арок
столбцов; данные с одинаковыми названиями будут просуммированы. Укажите^ кик следует создавать страничные поля, •затеи нажмите Кнопку "Далее>".
в ІСоздТ ницы|
С Создать пола страницы
Рис. 2.37. Создание сводной таблицы из нескольких листов http://itbookz.ru/
Особенности Excel, полезные бухгалтеру

Почктит* табПмц в: После того как диапазон вы-бран, нажмите кнопку Добавить, и Рис. 2.38. диапазон будет помещен в список в
Заключительный диалог мастера нижней части диалога мастера. Так
вы можете добавить в сводную таб-лицу несколько диапазонов с одного или нескольких листов рабочей книги. Если вы хотите удалить диапазон из списка, выберите его в нижней части диалога и нажмите кнопку Удалить. Если нужно добавить диапазон ячеек из другого ра-бочего листа, нажмите кнопку Обзор, и в появившемся диалоге выберите файл с таблицей. После выбора всех диапазонов нажмите кнопкуДалее, чтобы продол-жить создание сводной таблицы.
В Excel 97 будет открыт диалог мастера для настройки макета сводной табли-цы. Настройку макета мы рассмотрим ниже. В Excel 2000 и 2002 будет открыт за-ключительный диалог мастера (Рис. 2.38).
В данном диалоге вам предлагается с помощью установки переключателя Поместить таблицу в указать, где должна быть расположена сводная таблица. По умолчанию предлагается разместить таблицу на новом листе, но вы можете установить переключатель в положение Существующий лист и в поле ввода указать диапазон ячеек, куда помещать сводную таблицу. Можно указать диапазон вручную, а можно выделить диапазон мышью, предварительно скрыв диалог с помощью кнопки \ в правой части поля ввода.
Нажмите кнопку Макет заключительного диалога мастера, и будет открыт диалог настройки макета сводной таблицы (Рис. 2.39). Этот же диалог появится
ТЙЙЇЇ^ШЩЇ дчгим. -ЙН

в Excel 97 после выбора диапазона
ячеек для сводной таблицы.
neiw-ащнч «ноші по«Я а н^кмм
1 | j fleftтЗ j ^eat-tuaj СтоЧэшц едок* Рис. 2.39. Настройка макета сводной таблицы
В этом диалоге вам предлагается задать структуру сводной таблицы. В правой части в виде кнопок располагаются наименования полей исходной таблицы. Вам нужно перетащить мышью эти кнопки в ту или иную область макета. В область и Строка перетаскиваются те кнопки, которые будут заголовками столбцов и строк сводной таблицы. В областьДанные перемещаются кнопки тех полей, 1 ¦ * «? S-Ч вЙЯМК» І


ВЛебет
HWSre
з

НКрмес а Сумма ¦ Содержание проводки

; іеве:в jjina с.рдэ яриялтспбигд

Перетащите сюда элементы данные
Рис. 2.40. Создание макета вручную
которые составят содержимое сводной таблицы. В область Страница можно перетащить те кнопки, которые не попали в предыдущие области, и с помощью этих кнопок можно будет также управлять структурой сводной таблицы, как бы перелистывая страницы. После того, как макет сформирован, нажмите кнопку ОК, чтобы закрыть диалог. В окне программы должна появиться сводная таблица по вашему макету.
Работая в Excel 2000 и 2002 можно создавать макет сводных страниц и вручную. Для этого нужно не открывать диалог создания макета в заключительном диалоге мастера. Тогда по окончании работы мастера вместо сводной таблицы на экране появится ее макет (Рис. 2.40, слева).
Рядом с макетом будет открыт диалог с именами полей исходной таблицы, на основе которой строится сводная таблица (Рис. 2.40, справа). Выбрав поле в диалоге, вы можете перетащить его в нужное место макета сводной таблицы с помощью мыши. Также можно выбрать поле в списке, а затем в открывающемся списке, расположенном в нижней части диалога, выбрать область, куда следует поместить поле. В заключение нужно нажать кнопку Поместить в, и выбранное поле будет помещено в нужную область на макете страницы. После того, как сводная страница создана, вы можете приступать к анализу данных. Об этом вы узнаете чуть а пока как еще можно структурировать инфор
мацию в таблицах, чтобы с ней было удобно работать.
2.5.2. Расчет итогов
При работе со списками и сводными таблицами возможен анализ записей на наличие повторяющихся данных в столбце и подведение промежуточных итогов. Чтобы данная возможность работала правильно, нужно, чтобы список был отсортирован по какому-либо полю.
Создайте любой список и отсортируйте его по какому-либо полю, выделив поле и нажав кнопку ^ | на панели инструментов. Затем щелкните мышью на
Промежуточные итоги "jm ПР* каждом чзме*еч.і U BL ІДата з Qrapuiuw: [Количество zl До?авиіь mctw по: |Г кид сс -! і Суина р вадажмш» чия
У 3(М«НМГЫ1«?и** дай
ИТАПІ і ?очел страницы «ежду гр>т ІЧІН* HTfltH та д&инщм ІСбрвть act І! оіс \ Отмена 1 Рис. 2.41. Настройка итогов
любой ячейке списка и выберите команду меню Данные ф Итоги. Будет открыт диалог настройки вычис-ления итогов (Рис. 2.41)
Открывающийся список При каждом изменении в предназначен для выбора поля, по которому будут подсчитываться итоги. В списке вы можете выбрать одно из полей вашей таблицы. В открывающемся списке Операция вы можете выбрать вид вычисляемых итогов, например, количество значений, среднее, минимум и так далее. Группа элементов управления Добавлять итоги по содержит список всех полей вашей таблицы. Установив флажок около поля, вы включаете вычисление итогов по этому полю, а если флажок сброшен, итоги по этому полю не вычисляются.
Если установлен флажок Заменить текущие итоги, при вычислении новые итоги заменят предыдущие итоги. Если флажок сброшен, новые итоги будут добавлены к предыдущим итогам. Если установить флажок Конец страницы между группами, после вычисления итогов каждая группа итогов будет разделена символами разрыва страницы. Если установлен флажок Итоги под данными, сначала выводятся данные, а затем итоги. Если флажок сброшен, сначала выводятся итоги, а затем данные таблицы. Нажав кнопку Убрать все, вы удалите ранее подсчитанные итоги. После настройки нажмите кнопку ОК, чтобы закрыть диалог и вычислить итоги. При этом внешний вид списка изменится (Рис. 2.42).
При вставке промежуточных итогов программа изменяет разметку списка за счет группировки однотипных данных и вывода итогов в дополнительные строки. При вычислении итогов просматриваются столбцы сверху вниз и считаются записи, удовлетворяющие условиям. Именно поэтому прежде чем вычислить итоги, необходимо отсортировать список по соответствующему полю. А С D ' Е F G Н 1 1 Дата Дебет Деб с С Кредит Кред сс Сумма Содержание проводки о "25 01.01.01 Количкуііо 23 26 03 01 01 1 80 50000 Вклада уставной фонд 27 03 01 05 1 80 150000 Вклад а уставной фонд 28 03 01 01 1 80 30000 а уставной фонд 29 03 01 01 4 75.1 15000 Поступило ОС ЗО 03 01 05 4 75 ОС 31 03.01.01 Кояп«вс і-ко 5 32 04 01 01 1 15000 Введено в эксплуатацию ОС 1 - 33 04 01 01 1 8 7800 Введено в эксплуатацию ОС - 34 04.01.01 Количество 2 35 05 01 01 51 75 50000 Вклад в уставной фонд 36 05 01 01 51 75 150000 Вклад е ус таеной фонд 37 05 01 01 1С 1 60 5000 Поступили материалы 38 05 01 01 1 60 5000 Поступили материалы ЗО 0S4H 01 1 60 1ЭЭ00 Поступили материалы 40 05.01.01 о 5 41 06 01 01 К 101 500 Передан материал 42' 0601 01 зо 10 500 Передам материал 43 06 01 01 зо 10 7500 Передан материал 44 06.01.01 Количество 45 Общее ко. 38 Рис. 2.42. Пример промежуточных итогов
В левой части списка после вычисления итогов появляются элементы управления структурой таблицы. О том, что такое структура, вы узнаете ниже, а пока отметим, что, нажав кнопку 4|, вы откроете список элементов в группе, и кнопка превратится в . Нажав кнопку —j, вы свернете список, и кнопка опять станет а вместо группы останется только строка итога. Подобным образом можно свернуть часть информации в таблице, а можно свернуть все, оставив только итоги.
Отметим, что в группы можно вставлять промежуточные итоги
для более мелких групп. Однако прежде чем вставлять вложенные итоги, нужно выполнить сортировку по полю, значения которого будут определять необходимость вычисления вложенного итога.
2.5.3. Структуризация и консолидация данных
Для повышения наглядности и читаемости таблиц в программе есть средство, называемое структурой таблицы. Структура позволяет быстро найти в гро-моздких таблицах интересующие вас данные. Структурирование таблицы - это изменение степени детализации отображения данных с помощью создания структуры. Благодаря структуре часть данных можно скрыть, но можно в любой момент снова вывести на экран.
Для создания структуры вы должны вначале создать какой-либо список. При структурировании будут использоваться названия полей и записей списка. Чтобы создать структуру, выберите команду меню Данные Группа и структура Создание структуры. Сразу после этой команды на экране в дополнение к исходной таблице появятся элементы структуры (Рис. 2.43).
.Группы
Уровни детализации.
С 1 Дебсс^ эедит Кредсс Сумма Содержание проводки 76 Вспомогательная проводка 3 80 Вспомогательная проводка 4 90 Вспомогательная проводка 5 Гг 99 Вспомогательная проводка 6 80 50000 Вклад в уставной фонд У 1 80 150000 Вклад в уставной фонд 8 1 80 30000 Вклад в уставной фонд 15 .Кнопки сворачивания и разворачивания групп
Рис. 2.43. Элементы структуры
Кнопки уровня детализации предназначены для изменения степени подробности при выводе списка на экран. Нажав кнопку уровня детализации для строк, вы перейдете к сокращенному виду таблицы, в котором отсутствуют записи списка, охваченные символом группы в виде скобки. Если структура построена по столбцам, то, нажав кнопку уровня детализации для столбцов, вы свернете структуру по столбцам. Чтобы развернуть структуру, нужно нажать кнопку уровня детализации Сложные структуры могут иметь много уровней детализации, и вы, нажимая кнопки уровней детализации, можете придавать списку различный вид.
Нажимая кнопки уровня детализации, вы сворачиваете или разворачиваете все группы одного уровня. Если нужно свернуть определенную группу, следует воспользоваться кнопками на конце скобки выделения соответствующей группы. Нажав кнопку , вы свернете группу, и кнопка станет +J. Нажав эту кнопку, вы снова развернете группу, и кнопка примет первоначальный вид. Структура будет отображаться на экране до тех пор, пока вы не удалите ее. Чтобы удалить структуру выберите команду меню Данные О/: Группа и структура Удалить структуру.
В большинстве случаев программа в состоянии разобраться в созданном вами списке и предложить конкретный вариант структуры. Однако возможны случаи, когда вам придется самим создавать нужную вам структуру вручную. Чаще всего это происходит, если непосредственно над ячейкой с формулой имеется пустая строка, или слева от такой ячейки расположен пустой столбец. Также ручное создание структуры придется применить, если структура должна охватывать
Сгруппировать (8 [строки! сгодбцы
только часть таблицы, или некоторые формулы работают в одних направлениях, а другие — в противоположных.
ОК
Отмена
Рис. 2.44. Группировка
Чтобы начать создавать структуру вручную, нужно выделить строки и столбцы, которые будут образовывать группу, то есть могут быть одновременно, и затем выбрать команду меню Данные Группа и структура Группировать. Будет открыт диалог группировки (Рис. 2.44).
Установите переключатель в положение, соответствующее способу группировки по строкам или по столбцам и нажмите кнопку чтобы закрыть диалог группировки. Выделенные вами ячейки будут сгруппированы, и в левой или верхней части списка появятся элементы структуры. Так, группируя разные ячейки списка, вы можете построить нужную вам структуру. Чтобы удалить часть структуры, выделите образующие группу и затем выберите команду Данные Группа и структура >$> Разгруппировать.

Рис. 2.45. Настройка структуры
При создании структуры по умолчанию никакого оформления не происходит. Вы можете оформить уже созданную структуру или создать оформленную структуру. Выделите ячейки, образующие список, а затем выберите команду Данные Группа и структура Настройка. Будет открыт диалог настройки (Рис. 2.45).
В этом диалоге нужно с помощью флажков указать программе, где расположены итоговые данные, которые следует выделить с помощью форматирования. Если флажки установлены, считается, что итоги расположены в строках под данными и в столбцах справа от
данных. Если сбросить флажки, программа будет считать, что итоги расположены в строках сверху данных и в столбцах слева от данных.
Лучше установить флажок Автоматические стили, чтобы программа самостоятельно оформила итоговые данные. Для оформления уже готовой структуры нажмите кнопку Применить стили. Если нужно создать новую структуру, нажмите кнопку Создать. В заключение нажмите кнопку ОК. чтобы закрыть диалог. Структура будет создана или оформлена.
Если вы не знаете, какие на данном листе есть формулы или как они расположены, попробуйте структурировать лист автоматически, а если результат не будет соответствовать желаемому, отмените это действие и выполните структу-рирование вручную.
Консолидацией называется объединение значений из нескольких диапазонов данных. Например, если нужно просуммировать данные из одной и той же ячейки на разных листах рабочей книги, следует консолидировать эти ячейки, и тогда операция суммирования будет происходить автоматически. Кроме суммирования при консолидации можно указать и другие действия.
Консолидировать данные можно несколькими способами. Можно объединять ячейки с помощью формул, используя в формулах ссылки на ячейки, расположенные на разных листах рабочей книги или в разных книгах. Формулы, содержащие ссылки на несколько листов, называются трехмерными формулами.
Напомним, что при использовании в формуле ссылок из разных листов рабочей книги, следует указать имя листа и адрес ячейки через символ например, Если название листа имеет пробелы, его нужно указывать в кавычках, например «Журнал операций»!А1 Чтобы объединить данные из ячеек с одинаковым адресом с разных листов, нужно их перечислить перед адресом ячейки, например, Если информация в ячейках, участвующих в формуле, меняется, консолидация автоматически обновляется.
Если все данные всех исходных областей находятся в одной рабочей книге и размещены в одинаковом порядке (например, данные из нескольких листов созданы на основе одного шаблона), можно использовать консолидацию по распо-ложению. Перед использованием консолидации по расположению следует убедиться, что макеты всех диапазонов совпадают.
Если требуется обобщить набор листов, имеющих одинаковые заголовки рядов и столбцов, но различную организацию данных, следует использовать консолидацию по категории. Этот способ позволяет консолидировать данные с одинаковыми заголовками со всех листов. В этом случае перед консолидацией нужно убедиться, что имена столбцов или строк, которые требуется объединить, совпадают с учетом регистра букв. Если строки или столбцы не имеют имен, присвойте им имена. Для этого выделите диапазон, а затем щелкните мышью слева от строки формул и введите с помощью клавиатуры имя для выделенного диапазона.
Консолидированные данные рекомендуется располагать на отдельном листе рабочей книги. Перейдите на нужный лист, щелкнув мышью на его ярлычке. За- М» *ЩШ Ссвлга; d «юр.. 1 Спад» дийпазиио»: 51 Добавить j В I Бахчей СтрОйИ Г' 2иа--«№!Р левого civV^a | і Создавать--.:^.-1 сиг^о^ыи:- і
Рис. 2.46. Настройка консолидации
и •
те\ Щелкните мышью в левом верхнем углу области, в которой требуется разместить кон-солидированные данные. Выберите команду меню Данные О/- Консолидация. Будет открыт диалог настройки (Рис. 2.46).
В открывающемся списке Функция нужно выбрать функцию, которая будет использоваться при консолидации. Можно выбрать функцию суммирования, произведения, нахождения среднего значения, а также ряд статистических функций.
В поле Ссылка нужно перечислить име на всех диапазонов для консолидации. Можно ввести имя диапазона с помо
щью клавиатуры, а можно нажать кнопку в правой части поля ввода, и выбрать диапазон с помощью мыши. После выбора, чтобы снова развернуть диалог, нажмите кнопку Нажмите Добавить, и выбранный вами диапазон появится в Списке диапазонов. Повторите эти действия для всех диапазонов, которые нужно включить в консолидацию. Если диапазон добавлен ошибочно, выберите его в списке диапазонов в центральной части диалога и нажмите кнопку Удалить.
Группа флажков Использовать в качестве имен устанавливается в зависимости от выбранного вами способа консолидации. Если консолидация выполняется по положению, все флажки в группе должны быть сброшены. Если консолидация выполняется по категории, в группе нужно установить флажки, соответствующие расположению подписей в исходных диапазонах. Все имена, не совпадающие с именами в других исходных областях, в консолидированных данных будут расположены в отдельных строках или столбцах.
Если таблицу консолидации требуется обновлять автоматически при каждом изменении данных в каком-либо исходном диапазоне и позже не потребуется изменять или добавлять диапазоны исходных данных для консолидации, установите флажок Создавать связи с исходными данными. По окончании настройки нажмите кнопку ОК, чтобы закрыть диалог. Будет осуществлена консолидация указанных вами диапазонов с помощью выбранной вами функции.
Отметим, что консолидацию данных можно проводить и при создании сводных таблиц, если в качестве источников данных в первом диалоге мастера создания сводной таблицы выбрать В нескольких диапазонах консолидации. Данный метод похож на консолидацию по категории, однако обладает большей гибкостью.
2.5.4. Проведение анализа
Рассмотренные способы структурирования данных в таблице удобно использовать для анализа большого объема информации. Например, изменяя вид
(Показать все) ЙІ Л В 8
а» 019.
20 у 26
...040 v 41 V ОК Рис 2 47 Список элементов
таблицы, вы можете структурировать информацию в таблице определенным образом, и у вас будет возможность лучше проанализировать ее.
Можно изменить состав элементов каждого поля сводной таблицы. Для этого нажмите кнопку Ў в правой части нужного поля таблицы. Будет открыт диалог со списком элементов данного поля (Рис. 2.47).
поля
Например, если вы построили сводную таблицу на основе журнала хозяйственных операций, вы можете в качестве поля выбрать номер счета. По умолчанию отображаются все элементы поля. Если сбросить флажок Показать все, можно выбрать те элементы, которые нужны для анализа. Например, вы можете проследить все операции по выбранному вами счету. После выбора элементов нажмите кнопку ОК, чтобы закрыть диалог. Вид сводной таблицы изменится, и вы увидите информацию только по выбранным вами элементам поля.
Отметим, что после создания сводной таблицы, на экране появляется панель инструментов Сводные таблицы. Если этой панели на экране нет, выберите команду меню Вид Панели инструментов Сводные таблицы. Можно изменить макет сводной таблицы, если это необходимо. Если вы работаете в Excel 97 или Excel 2000, щелкните мышью на каком-либо поле таблицы. В панели инструментов Сводные таблицы ниже кнопок появится список всех полей. Чтобы добавить новое поле в сводную таблицу, перетащите название поля из списка на панели инструментов в нужную часть таблицы. Если вы работаете в Excel 2002, то чтобы добавить поле в сводную таблицу, нажмите кнопку [Щ на панели инструментов Сводные таблицы. Будет открыт диалог со списком полей (Рис. 2.40, справа). Выбрав поле в диалоге, перетащите его с помощью мыши в нужное место таблицы.
Иногда наглядность представления информации можно повысить, перемещая поля сводной таблицы. Чтобы переместить любое поле, подведите к нему указатель мыши, и он изменит форму на Перетащите поле с помощью мыши в нужное место таблицы, и, когда вы отпустите клавишу мыши, поле окажется на новом месте. Чтобы исключить поле из анализа, нужно подвести указатель мыши к заголовку этого поля и перетащить его с помощью мыши за пределы сводной таблицы.
Для каждого элемента поля строк или поля столбцов сводной таблицы можно вывести на экран более подробные данные. Если выделить ячейку в области данных сводной таблицы и нажать кнопку на панели инструментов, будет создан еще один лист Excel, в который будут помещены данные, скрываемые под выбранным элементом сводной таблицы. Если щелкнуть на каком-либо поле таблицы, в сводной таблице появятся дополнительные строки и столбцы, содержащие дополнительную информацию об элементах выбранного поля. Чтобы убрать дополнительные сведения, нажмите кнопку
тага*. ШІ |Де6бТ
Итог» Omwa [ li ВВтакогичсСКйе ІСуИИВ ¦кшьщкты )ідалит=. 1 Г накину), J
а ДОфИГиДО— 1 WJmHjV Mwf- И уі ображать пусты» *гн)Н«*ты _ Чадію,- 1 Рис. 2.48. Настройка вычисляемого поля
Особенности Excel, полезные бухгалтеру
Иногда бывает нужно, чтобы области полей строк, столбцов и страниц сводной таблицы были обведены сплошной синей линией. Чтобы эти границы были видны, нажмите кнопку || на панели инструментов. Повторное нажатие этой кнопки убирает границы полей сводной таблицы.
При подведении итогов в сводной таблице может быть использована функция отличная от той, которая применялась при построении исходной таблицы. Для задания новой функции щелкните мышью на вычисляемом поле сводной таблицы и нажмите кнопку ^ на панели инструментов. Будет открыт диалог настройки (Рис. 2.48).
В поле Имя можно задать новое имя для выбранного поля, при этом имя поля в исходных данных не изменяется. Переключатель Итоги определяет вычисление промежуточных итогов по выбранному полю. Если нужно вычисление итогов по каждому элементу выбранного поля внешней строки или столбца, установите переключатель в положение Автоматические. Чтобы итоги подводились по каждому элементу выбранного поля внутренней строки или столбца, выберите параметр Другие, и укажите нужную операцию в списке. Положение Нет выбирается, если не нужно показывать итоговые значения для выделенного поля сводной таблицы. Флажок Отображать пустые элементы следует установить в том случае, когда на экране должны отображаться элементы полей строк, столбцов и страниц, в которых не содержатся данные.

Рис. 2.49. Выбор варианта оформления
Нажав кнопку Дополнительно, вы откроете диалог настройки дополнительных параметров. В этом диалоге можно указать параметры сортировки выбранного поля сводной таблицы. Если нажать кнопку Макет, вы откроете диалог, в котором нужно выбрать, как располагать поля сводной таблицы. Можно выбрать отображение в виде таблицы или в виде структуры. Нажав кнопку Число, вы откроете диалог настройки формата чисел. В этом диалоге нужно ка
кой формат применять к числам в выбранном поле сводной таблицы. После настройки параметров нажмите кнопку чтобы закрыть диалог. Область данных сводной таблицы будет заполнена новыми итогами.
Отметим, что для получения обобщенных итогов вы можете в сводной таблице применить группировку данных по некоторым элементам полей. О группировке данных рассказано выше. Если вы работаете в Excel 2000 или 2002, вы можете изменить внешний вид сводной таблицы, выбрав один из предлагаемых форматов оформления. Нажмите кнопку на панели инструментов Сводные таблицы, и будет открыт диалог выбора формата (Рис. 2.49).
В этом диалоге представлено несколько вариантов оформления сводных таблиц, которые вы можете использовать по своему усмотрению. Чтобы выбрать вариант оформления, щелкните на нем мышью, а потом нажмите кнопку ОК. Диалог будет закрыт, а оформление сводной таблицы изменится.
Обычно при создании сводных таблиц подходят параметры, принятые по умолчанию. Но вы можете изменить параметры сводной таблицы в процессе анализа информации в ней. Для этого в панели инструментов Сводные таблицы нажмите кнопку Сводная таблица. Будет открыто вспомогательное меню, в котором выберите команду Параметры таблицы. Появится диалог настройки параметров сводной таблицы (Рис. 2.50).
В поле ввода Имя можно изменить предлагаемое программой имя сводной таблицы. Группа элементов управления Формат определяет параметры форматирования сводной таблицы. Если установлен флажок Общая сумма по столбцам, в отчете сводной таблицы будут показаны суммы по столбцам данных. Если установлен флажок Общая сумма по строкам, в отчет сводной таблицы также будут включены суммы по строкам данных. Для отображения в сводной таблице итогов расчетов необходимо установить эти флажки в зависимости от того, какие итоги вы желаете видеть в отчете.
Ш
ЩіШтищатщ-¦ • ¦
(своднаяТаблица!
•р общая ю т-і':iv р общая сумма по строен
F" ветоформяг
Г" мельчать скрытые значения r~of!jse#!««T» ячейки заголовков R сохранять форматирование F RSHrop.T.'t'ituw-ffi на каждой
Л, ЇЇГЄМ поперек — 1
страницы
Р э
Если установлен флаж о к Автоформат, к таблице будет применено форматирование, используемое программой по умолчанию. Если вы хотите, чтобы про-грамма сама подбирала формат создаваемой сводной таблицы, установите этот флажок. Если установлен флажок Включать скрытые значения, в итоги отчета сводной таблицы попадут неотобра- жаемые элементы полей страниц. Если вы хотите, чтобы значения скрытых ячеек учитывались при подсчете итогов, установите этот флажок.
й
число п(
' для :¦ :¦¦.:¦;¦¦¦. отображать:
-¦' для пусты* ячеек отображать: Г" -'-if!;:
F « ! т
источник; інвш'»іедаякь»г
І* евхранитмамные вместе с wwrff Г F" развертывание разрешено Г
! при
Г |3 Jg ¦¦¦
LZZj
Рш 2.50. Настройка параметров сводной таблицы
Если установлен флажок Объеди-нять ячейки заголовков, будет произведено объединение ячеек всех подписей внешних строк и столбцов в отчете сводной таблицы. Установив флажок Сохранять форматирование, вы скажете программе, что при изменении структуры отчета или самого отчета форматирование данных отчета менять не нужно. Если установлен флажок Повторять подписи на каждой странице, подписи элементов будут
повторяться для всех полей строк, расположенных слева от поля, где разрыв страницы отделяет группу элементов. Чтобы при выводе на печать подписи элементов не повторялись, этот флажок следует сбросить.
В списке Макет страницы можно выбрать порядок отображения полей страниц. Вариант Вниз, затем поперек, установленный по умолчанию, можно заменить на вариант Поперек, затем вниз. В поле ввода Число полей в столбце можно указать, сколько полей в столбце или строке должны располагаться перед началом новой строки или столбца. Если вы желаете, чтобы ошибки или отсутствующие данные в ячейках отображались определенными символами, введите эти символы в поля Для ошибок отображать и Для пустых ячеек отображать. предварительно установив соответствующие флажки. Если установлен флажок Печать заголовков, подписи полей и элементов отчета сводной таблицы будут использованы в качестве заголовков строк и столбцов при печати сводной таблицы.
Если в сводной таблице есть импортированные внешние данные, настройки параметров этих данных осуществляются в группе элементов управления Данные. Флажок Сохранять данные вместе с таблицей устанавливается по умолчанию и означает одновременное копирование внешних данных вместе с таблицей. Если вы установите флажок Развертывание разрешено, при двойном щелчке области данных сводной таблицы будут развертываться подробные данные. Установленный флажок Обновить при открытии означает, что данные сводной таблицы будут обновляться при открытии, если были внесены изменения в исходный список. Установив флажок Обновлять каждые, вы можете указать в поле ввода интервал автоматического обновления данных в сводной таблице.
Если в качестве источника данных для отчета сводной таблицы или сводной диаграммы используется внешняя база данных, которая запрашивает пароль, можно сохранить пароль как часть отчета с помощью установки флажка Сохранить пароль. Чтобы работать с отчетом можно было только после окончательной загрузки указанных в запросе внешних данных, нужно сбросить флажок Фоновый запрос. Если флажок установлен, запрос будет выполняться в фоновом режиме, а вы можете продолжать работать с таблицей. В заключение, после настрой-ки параметров нажмите кнопку чтобы закрыть диалог параметров сводной таблицы.
<< | >>
Источник: Коцюбинский А.О., Грошев С.В.. Excel для бухгалтера в примерах.. 2003

Еще по теме 2.5. Использование сводных таблиц:

  1. 18. Сводная и индивидуальная финансовая отчетность
  2. V. ОСНОВНЫЕ ПРАВИЛА СВОДНОЙ БУХГАЛТЕРСКОЙ ОТЧЕТНОСТИ 91
  3. 17.6. Сводный учет затрат на производство
  4. 4.2. Назначение и содержание сводного сметного расчета стоимости строительства
  5. 4.3. Состав уі порядок определения затрат по главам сводного сметного расчета
  6. 8.1. Источники данных (таблицы) запросов
  7. 1.2. Создание и редактирование таблиц
  8. 1.3. Оформление электронных таблиц
  9. Таблица штрафов за нарушение ПДД РФ
  10. 1.4. Расчеты в электронных таблицах
  11. Экономические таблици и графики
  12. ГЛАВА 1.ОСНОВЫ РАБОТЫ С ЭЛЕКТРОННЫМИ ТАБЛИЦАМИ EXCEL
  13. Рабочая таблица: инструмент для бухгалтеров
  14. Приложение 4 Новая таблица штрафов
  15. Составление отчета о движении денежных средств с помощью рабочей таблицы
  16. Таблица «Основные категории, рассматриваемые в судебных решениях»
  17. Таблица соответствий корреляционных функций кх(т) и спектральных плотностей 5* (оj