Сделай Сам Свою Работу на 5

Подготовка отчётов с помощью меню





В предыдущем разделе были описаны способы автоматизации двух операций обработки данных:

q расчет итогов (оборотов, сальдо) по формулам,

q подготовка списков счетов и типовых проводок с помощью фильтра Excel.

При этом часть работы (создание макета отчета и ввод формул) была выполнена вручную. С помощью команд Итоги, Консолидация и Сводная табли­ца меню Данные создавать отчеты гораздо легче. Например, команда Итоги обеспечивает:

q автоматическое создание макета итогового отчета,

q автоматический ввод формул и расчёт итогов.

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

Команды меню Данные позволяют создавать отчеты следующих типов:

q Двусторонняя оборотно-сальдовая ведомость — обычная ведомость, включающие обороты и сальдо всех счетов по дѐбету или кре­̀диту.

q Односторонние оборотно-сальдовые ведомости (ведомости по одной стороне счета — дѐбету или кре­̀диту).



q Оборотно-сальдовая ведомость по проводкам. В отличие от ведомости по счетам она содержит обороты и сальдо по корреспонденциям счетов.

q Шахматная ведомость — это комбинированная ведомость. Она включает обороты и сальдо по проводкам и счетам. Это итоги двух уровней. Итоги верхнего уровня (обороты и сальдо по счетам) включают вложенные итоги нижнего уровня (итоги по проводкам).

q Итоговые отчеты по группам счетов — статьям баланса и других финансовых отчетов.

Каждая команда меню Данные имеет свои достоинства и недостатки:

q перед использованием команды Итоги учётные записи надо сортировать,

q команды Консолидация и Сводная табли­ца создаёт таблицы стандартной формы, таблицу произвольной формы создать нельзя,

q наиболее мощным инструментом является команда Сводная табли­ца, однако, она (в отличие от команды Консолидация) не обеспечивает автоматического пересчета итогов при изменении исходных данных. Для обновления данных надо использовать кнопку на панели инструментов.



Форму макета отчёта определяют два условия:

q Тип журнала учёта операций (расположение счетов дѐбета и кре­̀дита):

○ номера (имена) счетов дѐбета и кре­̀дита находятся в одном столбце,

○ номера (имена) счетов дѐбета и кре­̀дита находятся в одной строке.

q Возможности используемой для создания отчета команды меню Данные.

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

Ниже описаны возможности отдельных команд меню Данные.

Возможности команд меню Данные

Команды меню Данные — Итоги, Консолидация, Сводная табли­ца, Сортировка, Фильтр–Расширенный фильтр и др. применяют при обобщении данных (расчёте итогов) и создании отчетов. Эти команды можно назвать структурными или структурно-аналитическими.

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

Структурно-аналитические команды помимо изменения структуры позволяют выполнять вычисления, например расчёт итогов.

Познакомимся поближе с возможностями этих команд.

Возможности команды Ито­ги

В окне Промежуточные итоги (команда Данные–Ито­ги) видим три поля с выпадающими списками — При каждом изменении в, Операция и Добавить итоги по.

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



Во втором списке выбирают тип итога — сумма, средняя и др.

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

Под списками располагается два полезных элемента управления:

q Флаг Заменить текущие итоги.

Сбросив этот флаг, можно подсчитать новые (вложенные) итоги и сохранить итоги, полученные ранее (итоги верхнего уровня). Таким образом, с помощью команды Итоги можно создавать многоуровневые итоги.

q Кнопка Убрать всё.

Выделив итоговую таблицу и нажав эту кнопку, уберём структуру и восстановим исходные данные.

В результате выполнения команды Итоги создается так называемая структура данных — таблица с элементами + и – (плюс и минус) слева от таблицы для разворачивания и сворачивания итогов.

Возможности команды Консолидация

Команда Консолидация позволяет объединять таблицы и подсчитывать ито­ги. В окне Консолидация видим следующие элементы управления:

q Поле Функция со списком способов расчёта итогов — итоговых функций.

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

q Два флага Подписи верхней строки и Значения левого столбца.

Эти флаги задают имена чисел, для которых надо считать итоги, а именно: числа с одинаковой парой имён в первой строке и первом столбце объединяют в один итог.

Следовательно, в отличие от команды Итоги, сортировать учётные записи не требуется.

q флаг Создавать связи с исходными данными служит для автоматического пересчета итогов при изменении исходных данных, если они размещены на отдельном лис­­те. При этом создается структура данных — таблица с элементами + и – (плюс и минус) слева от таблицы для разворачивания и сворачивания итогов, иначе вычисляются только итоги.

Возможности команды Сводная таблица

Эта команда, в отличие от других команд меню Данные, создает на основе столбцов журнала операций элементы управления типа Выпадающий спи­сок, содержащие значения соответствующих столбцов (см. например Табл. 28, с. 141).

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

q Открыть окно настройки макета нажатием кнопки Макет в третьем окне мастера, запускаемого командой Сводная таблица (см. рисунок ниже).

q Создать пустую сводную таблицу вместе с инструментом настройки макета(см. рисунок ниже), нажав кнопку Готово.

При определении макета сводной таблицы в окне мастера открывается соответствующее окно (см. рисунок ниже).

Как видим, макет сводной таблицы состоит из четырех областей — Страница, Строка, Столбец и Данные. Для задания макета сводной таблицы надо перенести мышью заголовки столбцов журнала учёта в соответствующие области макета. Столбцы журнала учёта с точки зрения команды Свод­ная таблица могут быть двух типов:

q Столбцы с именами чисел, для которых следует считать итоги, например столбец №счёта в журнале учёта со счетами в столбце (см. Табл. 7, с. 38). Эти столбцы надо переносить в любую область имён сводной таблицы — Страница, Строка, Столбец.

q Столбцы с числами, для которых следует считать итоги, например столбцы с суммами по дебету и кредиту в журнале учёта со счетами в столбце. Эти столбцы надо переносить в любую область данных сводной таблицы — Данные.

Итак, для задания макета сводной таблицы надо выполнить следующее:

q Перенести заголовки столбцов с именами чисел в

q области Страница, Строка или Столбец.

q Перенести заголовки столбцов с числами в область Данные.

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

Команда Свод­ная таблица — самый мощный способ обобщения данных, позволяющий, в отличие от других команд меню Данные,следующее:

q Создание многомерной таблицы (см. например Табл. 28, с. 141).

Обычные таблицы имеют два измерения — строка, столбец.

Рабочий лист Excel имеет три измерения — строка, столбец, лист.

Свод­ная таблица имеет много измерений.

Измерения свод­ную таблицу добавляют в виде выпадающих списков. Эти списки включают значения столбцов таблицы с исходными данными (реквизитами документа).

Такое представление данных позволяет не вводить значение, а выбирать его из списка. Это позволяет упростить, ускорить и повысить надёжность ввода данных.

Например, можно создать свод­ную таблицу с двумя списками — номер дѐбета и номер крѐдита. Выбирая значения номера счетов из этих списков, можно получить итоги двух типов — обобщённые итоги по выбранным счетам и детальные итоги по проводкам, связывающим эти счета.

q Группировки объектов учёта для расчета итогов, например, можно сгруппировать данные учёта по периодам — год, квартал, месяц и др.

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

q Изменение структуры макета таблицы и с одновременным пересчетом итогов помощью мыши прямо на экране.

Возможности команды Сортировка

Возможности команды Сортировка Excel гораздо шире, чем у Word и Access. Обратим внимание на следующие особенности сортировки в Excel:

q Доступна сортировка не только строк, но столбцов (использовать кнопку Параметры в окне диалога Сортировка диапазона). Речь идёт об изменении положения столбцов в таблице без сортировки значений в этих столбцах. Например, можно сортировать столбцы с показателями состояния предприятий — объём выпуска продукции, прибыль, рентабельность, себестоимость изделий, цена и др.

q можно сортировать не только по алфавиту (этот порядок определяет в основном расположением знаков в таблице кодов Windows), но и в соответствии с произвольным списком. Этот список создаёт пользователь на вкладке Списки в окне Параметры (меню Сервис). Способ сортировки в соответствии с этим списком можно выбрать в поле Сортировка по первому ключу в окне Параметры сортировки.

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

В Word и Excel одна операция сортировки позволяет упорядочить таблицу только по трем ключам сортировки — столбцам или строкам (в Access количество столбцом сортировки не ограничено).

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

Возможности команды Фильтр–Расширенный фильтр

С помощью фильтра Excel можно выполнять простые операции группировки и выборки записей из списков (список в Excel — это таблица в первой нормальной форме, см. пособие по Access [9]):

q фильтрация записей непосредственно в таблице, используя переключатель Фильтровать список на месте,

q выборка записей с переносом результата в указанное пользователем место, используя переключатель Скопировать результат в другое место,

q группировка записей (исключение дубликатов), используя флаг Только уникальные записи.

Компьютерные документы

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

Команды меню Данные позволяют создать два типа документов (таблиц) с графическим интерфейсом — формы и струк­туры (см. например, Табл. 28, с. 141 и Табл. 26, с. 133).

Формы создают командой Свод­ная таблица–Данные.

Создать струк­туру можно с помощью трёх команд — Группа и Структура, Итоги и Ко­н­со­ли­да­ция из меню Данные.

Наименее удобна, на наш взгляд, команда Группа и Структура, поскольку перед её использованием надо предварительно подсчитать итоги по столбцам и/или по строкам таблицы.

Формы и струк­туры различаются способом вывода итогов на экран.

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

Струк­туры, в отличие от форм, используют для управления выводом манипуляторы структуры — кнопки со знаками + и – (плюс и минус), размещённые в первом столбце струк­туры. Манипуляторы действуют аналогично соответствующим кнопкам в проводнике Windows. Щелчок по кнопке разворачивает или сворачивает итог текущего уровня подобно дереву файлов в проводнике. В результате можно показывать только итоги или итоги нескольких вложенных уровней вместе с исходными данными.

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


Создание оборотно-сальдовой ведомости по счетам

Обычная оборотно-сальдовая ведомость включает итоги (обороты и сальдо) по счетам. Итоги считают обычно по двум сторонам счёта — дебету и кредиту. Поэтому такой документ можно назвать двусторонней ведомостью. Аналогично ведомость с итогами только по дебету или только по кредиту можно назвать односторонней ведомостью (см. раздел Регистры учёта, с. 46).

Как известно, оборот по дебету (или кредиту) счёта равен сумме обо­ротов по парам счетов (проводкам), образованным этим счётом и кор­рес­пон­ди­рованными счетами. Поэтому представляет интерес расчёт оборотов не только по счетам, но и по проводкам с последующим созданием на их основе соответствующих ведомостей — оборотных ведомостей по проводкам.

Кроме оборотно-сальдовых ведомостей по счетам и проводкам очень популярна шахматная ведомость. Эта ведомость является комбиниро­Ван­ной, т.к. она включает итоги и по счетам, и проводкам.

Двустороннюю ведомость итогов по счетам можно создать с помощью команд меню Сервис. При этом можно использовать журналы учёта двух рассмотренных выше типов (со счетами в столбце или в строке) с учётом следующих особенностей и ограничений.

q Команды Ито­ги и Консолидация можно использовать для создания дву­сто­­рон­ней ведомости только на основе записей из журнала со счетами в столбце (на основе журнала со счетами в строке можно создать одностороннюю ведомость).

q Команду Сводная таблица можно использовать для создания дву­сто­­рон­ней ведомости на основе журналов любого типа — со счетами в столбце и в строке.

Создание двусторонней ведомости командой Ито­ги

Подготовка исходных данных

Перед созданием двусторонней оборотно-сальдовой ведомости по счетам коман­дой Ито­ги (как и в любом другом случае) надо извлечь исход­ные данные из журнала учёта операций. Исход­ные данные в этом случае (при использовании команды Ито­ги) должны включать следующие три столбца:

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

q Столбец с дебетуемыми суммами.

q Столбец с кредитуемыми суммами.

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

Легко видеть, что указанные списки проще всего извлечь из журнала учёта операций со счетами в столбце (см. Табл. 15, с. 75). Для этого надо выполнить следующее.

q Скопировать на новый лист Excel диапазон адресов, включающий номера счетов и суммы проводок (см. диапазон ячеек D1:F28 Табл. 15).

q Отсортировать скопированный диапазон ячеек по столбцу Счёт, поскольку команда Ито­ги начинает вычисления при изменении значения в управляющем столбце (столбце Счёт, см. раздел Возможности команды Ито­ги, с.122). Для этого выполним команду Данные–Сортировка,

q Установить в окне Сортировка переключатель По подписям (первая строка диапазона), иначе будут сортироваться не только записи, но первая строка. Нажмем Enter.

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

В результате получим список проводок (см. Табл. 25):

Табл. 25 — Список проводок

Счёт Дебет Кредит
1,00р.  
  4,00р.
7,00р.  
  5,00р.
2,00р.  
4,00р.  
  2,00р.
3,00р.  
  9,00р.
  3,00р.
  6,00р.
  8,00р.
9,00р.  
  1,00р.
6,00р.  
  7,00р.
8,00р.  
68.2 5,00р.  

Расчет оборотов

Для расчёта оборотов выполним следующее:

q Выделим исходные данные (список счетов и суммы по дебету/кредиту),

q Выполним командуИто­ги.

q Выберем Счёт в первом выпадающем списке.

q Выберем Дѐбет и Крѐдит — в третьем списке (см. рисунок ниже).

В результате получим структуру (см. таблицу ниже) с суммами по дѐбету и крѐдиту.

Табл. 26 — Оборотная ведомость в виде структуры

Щёлкая по знакам – (минус) в первом столбце слева от номеров строк, свернём структуру следующим образом (см. рисунок ниже).

Щёлкнув по знаку – (минус) во втором столбце слева от номеров строк, свернём структуру следующим образом (см. рисунок ниже).

Выделим ячейку В7 с итогом по счёту 10. Как видим, итоги рассчитаны с помощью функции Промежуточные.Ито­ги (№ ; ссылка). Эта функция имеет два параметра:

q — номер типа итога, который вычисляет эта функция. № = 9 соответствует функции суммирования (номера других типов итогов, например номер функции для вычисления средней см. справке мастера функций).

q Ссылка — это указатель диапазона ячеек, для которого надо рассчитать итог, в нашем случае это диапазон B4:B6.

Проверим, как подсчитаны итоги. Для этого развернем структуру, щёлкая по знаку + (плюс) и – (минус) слева от номеров строк. Например, раскроем итог по счёту 10, равный 8. Как видим, он образован суммиро­ва­нием первой проводки 1 и третьей проводки 7.

Чтобы улучшить внешний вид ведомости, выполним следующее.

q Удалим слово Итог в диапазоне ячеек с номерами счетов командой Правка–За­ме­нить.

q Удалим слово Общий в ячейке Общий итог.

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

q Обороты по дѐ­бету и/или крѐдиту в целом по всей ведомости.

q Обороты по дѐ­бету и/или крѐдиту в целом и в разрезе счетов (то есть с детализацией оборотов по каждому счёту).

q Обороты по дѐ­бету и/или крѐдиту в целом, в разрезе счетов и в Оборот по любому счёту разрезе каждой проводки.

Расчет конечного сальдо

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

Начальное сальдо + Оборот по дебету – Оборот по кредиту.

В ячейке С7 (для первой строки со счётом 10, см. рисунок ниже) это выражение в скалярной адресной форме имеет вид:

=A7+C7-D7.

Формулу для расчёта сальдо по кредиту и сальдо на счетах других типов — активные и пассивные, с переходящим остатком и без переходящего остатка и др., см. в разделе Особенности расчёта итогов для счетов разных типов, с. 91.

Для подсчёта итога по начальному и конечному сальдо можно использовать кнопку Автосумма на панели инструментов Стандартная.

Создание двусторонней ведомости командой Консолидация

Подготовка исходных данных

Исход­ные данные для создания двусторонней оборотно-сальдовой ведомости по счетам с использованием команды Консолидация (как и при использовании команды Ито­ги) должны включать три столбца:

q Столбец с номерами дебетуемых (кредитуемых) счетов. Однако сортировать их не обязательно, команда Консолидация делает это самостоятельно.

q Столбец с дебетуемыми суммами.

q Столбец с кредитуемыми суммами.

Действительно, если номера дебетуемых (кредитуемых) счетов находятся в первом столбце списка исходных данных, тогда команда Консолидация счёта может следующее:

q Рассчитать обороты по дебету и кредиту для повторяющихся номеров счетов в первом столбце этого списка.

q Перенести суммы по дебету и кредиту для неповторяющихся номеров счетов в столбцы с оборотами по дебету и кредиту.

Легко видеть, что указанные списки проще всего извлечь из журнала учёта со счетами в столбце (см. Табл. 15, с. 75). Как это сделать, было описано в предыдущем разделе (см. Табл. 25, с. 131).

Порядок создания оборотной ведомости

Создадим новый лист, выполним командуКонсолидация и в одно­имён­ном окне выполним следующее (см. окно диалога ниже):

q укажем мышью в поле Ссылка адрес диапазона с исходными данными (списком номеров счетов и сумм проводок), подготовленными в предыдущем разделе,

q Включим исходные данные в список консолидируемых диапазонов, нажав кнопку Добавить,

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

q Создадим связь с исходными данными, установив одноимённый флаг.

В результате появится структура с дѐбетовыми и крѐдитовыми оборотами (см. Табл. 27).

Табл. 27 — Обороты по дебету/кредиту. Команда Консолидация

Развернем структуру для проверки результата вычислений. Например, щёлкнем по знаку + (плюс) слева от счёта 10. Как видим (см. рисунок ниже):

q итог по этому счёту — 8 равен сумме двух проводок 1 и 7.

q Команда Консолидация, в отличие от команды Итоги, не выполняет следующее:

○ Не показывает формулу (функцию) для расчёта итогов.

○ Не подсчитывает итог по дебету/кредиту всех счетов.

q В столбце В выводится имя активного файла Excel. Чтобы скрыть этот столбец, перетащим влево границу между столбцами В и С (см. рисунок ниже).

Для проверки автоматического обновления итогов изменим исхо­д­ные данные в журнале учёта.

Расчет итогов

Для расчёта оборотов выделим ячейку С23 в последней строке итоговой таблицы (см. Табл. 27, выше) и щелкнем по кнопке Автосумма. Резу­льтат по­лу­чится неверный (см. рисунок ниже):

=СУММ(C22;C20;C17;C13).

Дело в том, что Excel пра­ви­льно рас­познаёт только непреры­вные ди­а­пазоны. До­ба­вим в форму­лу пропу­щенные ячей­ки C10, C5, щёлкая по них мышью и разделяя их символом ; (точка с запятой). В результате получим верный результат (см. рисунок ниже). Аналогично введём формулу для расчёта кредитового оборота в ячейке Е23.

О том, как рассчитать конечного сальдо см. выше раздел Расчет конечного сальдо.

Создание двусторонней ведомости командой
Сводная таблица

Подготовка исходных данных

Исход­ные данные для создания двусторонней оборотно-сальдовой ведомости по счетам с помощью команды Сводная таблица (как и при ис­поль­зовании других команд — Ито­ги и Консолидация) долж­ны включать три столбца:

q Столбец с номерами дебетуемых (кредитуемых) счетов. Сортировать счета не обязательно, как и при использовании команды Консолидация. Номера счетов сортирует команда Сводная таблица.

q Столбец с дебетуемыми суммами.

q Столбец с кредитуемыми суммами.

Действительно, чтобы создать ведомость оборотов в окне проектирования макета сводной таблицы, надо выполнить следующее:

q Перетащить заголовок столбца с номерами дебетуемых (кредитуемых) счетов в область Столбец или Строка.

q Перетащить заголовки столбцов с дебетуемыми (кредитуемыми) суммами в область Данные.

Тогда команда Сводная таблица может рассчитать суммы по дебету и кредиту для каждого счёта в первом столбце этого списка.

Легко видеть, что указанные списки проще всего извлечь из журнала учёта со счетами в столбце (см. Табл. 15, с. 75).

Расчет оборотов

Для расчёта оборотов выполним следующее:

q Выделим исходные данные (журнал учёта операций сосчетами в столбце,см. Табл. 15, с. 75) и выполним команду Сводная таблица.

q Выберем в первом ок­не мас­тера первый вариант — В списке или базе дан­ных Microsoft Excel.

q Проверим во втором ок­не мас­тера, правильно ли выделены адреса объединяемых данных.

q Сохраним установки в окне Параметры сводной таблицы по умолчанию (кнопка Параметры) и щёл­к­нем по кнопке Макет в третьем ок­не мас­тера.

q Перенесем по­ля Дѐ­бет и Крѐдит в окне Макет в область Данные.

q Создадим три варианта сводной таблицы, перенося поле Счёт в три области макета сводной таблицы — области Столбец, Строка и Страница.

При переносе по­лей Дѐ­бет и Крѐдит в область Данные автоматически устанавливается итог типа Количество (см. рисунок ниже).

Чтобы получить сумму, следует выполнить двойной щелчок по полю и выбрать в открывшемся окне (см. рисунок ниже) нужный тип итога.

В результате получим первый вариант сводной таблицы со счетами в первой строке (см. Табл. 28).

Табл. 28 — Сводная таблица со счетами в первой строке

Таким образом, если перенести поле Счет в область макета Столбец сводной таблицы, номера счетов выводятся в первой строке.

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

Напомним, чтокоманды Итоги и Консолидация создают структуры ито­гов, которые можно сворачивать и разворачивать. Причём сворачивать и раз­во­рачивать можно не только структуру целиком, но и структуру её отдельных ветвей вплоть до исходных чисел. Это позволяет контролировать ход расчёта итогов.

Аналогичным образом действуют выпадающие списки. Например:

q Открыв список, связанный с полем Счёт можно выбрать вывод оборотов по всем счетам или только по отдельным счетам.

q Открыв список, связанный с полем Данные, можно выбрать вывод сумм (оборотов) по дѐ­бету и/или крѐдиту (см. рисунок ниже).

Результат фильтрации приводится на рисунке ниже.

Как видим, этот вариант сводной таблицы, в отличие от таблиц, созданных командами Итоги и Консолидация, не показывает исходные числа (суммы проводок)

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

Для правки макета сводной таблицы с помощью этого окна надо выполнить следующее:

q Выбрать поле в верхней части окна, местоположение которого надо изменить, например Счёт.

q Открыть выпадающий список в нижней части окна, включающий четыре области макета сводной таблицы (см. список на рисунке ниже).

q Выбрать область макета, в которую надо переместить выбранное поле.

Вместе со сводной таблицей и окном для проектирования макета и последующей правки макета сводной таблицы появится также панель инструментов Сводные таблицы (см. панель на рисунке ниже).

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

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

Однако, возможности изменения макета сводной таблицы ограничены, например перенести поле Данные в область Страница нельзя.

Форматирование сводной таблицы

Отформатируем сводную таблицу следующим образом:

q Введём более информативные имена столбцов и строк. Например, исправим имя списка Данные на имя Сумма или Обороты (см. таблицу ниже).

q Установим размер столбцов и строк в соответствии с введенными в них значениями. Для этого служат команды Формат–Столбец–Автоподбор ширины и Формат–Строка–Автоподбор высоты.

q Установим денежный формат для оборотов командой Формат–Ячейка–Число–Денежный или Финансовый.

В результате сводная таблица со счетами в первой строке приобретает вид:

Табл. 29 — Сводная таблица со счетами в первой строке

 

Расчёт сальдо

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

Итак, добавим в сводную таблицу начальное и конечное сальдо. Для этого выполним следующее:

q Добавим в ячейки А6 и А7 заголовки НачСальдо и КонСальдо. (см. Табл. 29 выше и Табл. 30 ниже)

q Введем в ряд В6:J6 значение начального сальдо 10.

q Оценим коне­ч­ное сальдо в ячейке В7 по формуле для активных счетов

(начальное сальдо плюс оборот по дѐ­бету минус оборот по крѐдиту)

или в скалярной адресной форме:

=В6+В4-В5.

При этом в ячейке В7 появится формула:

=B6 +

По­лу­чить.Дан­ные.Сво­д­ной.Таб­лицы("По де­бету", $A$2, "Счёт", B3) –Полу­чить.Дан­ные.Свод­ной.Таб­ли­цы("По кредиту", $A$2, "Счёт", B3).

Очевидно, что второй член формулы вычисляет оборот по дѐ­бету, а третий член — оборот по крѐдиту.

Щелкнем по кнопке fX на панели формул. Появится окно мастера функций с аргументами функции По­лу­чить.Дан­ные.Сво­д­ной.Таб­лицы().

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

q вписать знак равно ( = ) в ячейку, в которую требуется извлечь данные,

q затем щелкнуть ячейку в сводной таблице, содержащей данные, которые требуется вернуть.

Оценим конечное сальдо для других счетов. Для этого надо скопировать формулу вправо по строке. Перед этим надо исправить фор­мулу, введя в нее вместо номера счета "10" адрес ячейки B3 с номером этого счета.

Сделаем формулу более понятной. Для этого создадим имена Счета и НачальноеСальдо для рядов В3:J3 и В6:J6.

Тогда формула принимает более читаемый вид:

=НачальноеСальдо +

По­лу­чить.Дан­ные.Сво­д­ной.Таб­лицы("По де­бету", $A$2, "Счёт", Счета) –

Полу­чить.Дан­ные.Свод­ной.Таб­ли­цы("По кредиту", $A$2, "Счёт", Счета).

Теперь сводная таблица со счетами в первой строке приобретает вид:

Табл. 30 — Сводная таблица со счетами в первом столбце

 

Аналогичным образом подготовим сводную таблицу со счетами в первом столбце (см. Табл. 31).

Табл. 31 — Сводная таблица со счетами в первом столбце

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

Иначе говоря, заголовок Данные имеет два подзаголовка Сумма по полю Дебет и Сумма по полю Кредит, которые, в свою очередь, являются заголовками чисел в столбце Итог.

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

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

 








Не нашли, что искали? Воспользуйтесь поиском по сайту:



©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.