Новости:

Прикрепить к сообщению можно только файлы xls, gif, jpg, rar, zip,7z, bas, frm, cls, doc размером до 150 Кб.

Главное меню

Бюджет с помощью сводных таблиц Excel

Автор Sergey Skripay, 18.11.2008, 02:08

« назад - далее »

Sergey Skripay

Бюджет составляется по нескольким финансовым подразделениям, которые нужно группировать. Сейчас все делаю в Excel через статические таблицы, но небольшое изменение в структуре подразделений или категорий расходов влечет за собой переделку всего файла.
Попробовал использовать _сводные_ таблицы Excel для этой задачи, достаточно гибко, но не могу в сводных таблицах сделать вычисление баланса ( текущий баланс = баланс предыдущего месяца+доходы текущего месяца- расходы текущего месяца).
Использует ли кто то _сводные_ таблицы Excel для подобных задач?
Буду благодарен за все советы.
NULL

GWolf

Во-во, мы тоже пытались прикрутить к подобной задаче СТ. В итоге пришли к выводу - макросом проще. Проще в том плане, что нет риска в самый неподходящий момент получить глюк СТ. И ежли в макропрограмме отладкой можно найти причину почти всегда, то с СТ зачастую незнаешь что и делать. А времечко неждет ...
А посему ежли есть желание давайте соберем Ваш баланс на основе исходных таблиц (не СТ ;D).
Путей к вершине - множество. Этот один из многих!

Sergey Skripay

Цитата: GWolf от 18.11.2008, 10:39

А посему ежли есть желание давайте соберем Ваш баланс на основе исходных таблиц (не СТ ;D).
Значит не один я с этим мучаюсь.
Давайте соберем, можно для примера Ваше решение?
NULL

Andrey Lenets

GWolf, не вводите людей в заблуждение! _сводные_ таблицы НЕ ГЛЮЧАТ! надо уметь с ними работать...

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

GWolf

Цитата: Andrey Lenets от 18.11.2008, 10:58
GWolf, не вводите людей в заблуждение! _сводные_ таблицы НЕ ГЛЮЧАТ! надо уметь с ними работать...

Sergey, [url=https://msexcel.ru/content/view/34/2/]_сводные_[/url] таблицы имеют вычисляемые поля и объекты, однако они не преспособлены для вычислений по отдельным полям. В любом случае, многое зависит от исходных данных. Можно использовать сводную таблицу как инструмент сведения данных, а дополнительные вычичления производить по результатам...

Прошу учесть, я сам пользуюсь СТ когда необходимо! ;D Только вот заметил, что чаще и проще мне решить задачу на VBA, а не СТ. Но это наверное от задач зависит?!
Путей к вершине - множество. Этот один из многих!

Алексей Шмуйлович

Цитата: Andrey Lenets от 18.11.2008, 10:58
GWolf, не вводите людей в заблуждение! _сводные_ таблицы НЕ ГЛЮЧАТ! надо уметь с ними работать...

Sergey, [url=https://msexcel.ru/content/view/34/2/]_сводные_[/url] таблицы имеют вычисляемые поля и объекты, однако они не преспособлены для вычислений по отдельным полям. В любом случае, многое зависит от исходных данных. Можно использовать сводную таблицу как инструмент сведения данных, а дополнительные вычичления производить по результатам...

Поддерживаю Андрея.
Я очень широко использую _сводные_ таблицы именно по писанным в первом посте причинам - нужно сводить очень большие объемы информации и структура источников исходных данных часто меняется.

Использование вычисляемых полей и объектов сводной оправдано только для небольших источников данных. Если исходная таблица большая, то вычисления очень замедляют пересчет сводной.
Я использую _сводные_ таблицы, подключенные к внешним базам данных на несколько миллионов строк. Так вот добавление вычисляемого объекта приводит к тому, что любое изменение в макете сводной пересчитывается на хорошей машине 1-2 минуты.

Как правило, задача построения отчета делится на 3:
1) организовать исходные данные так, чтобы по ним можно было построить сводную таблицу. То есть нужно создать простую двухмерную таблицу, без объединенных ячеек, столбцы должны быть подписаны и т.п.

2) составляем сводную таблицу

3) извлекаем результаты вычислений в печатную форму с помощью функции ПОЛУЧИТЬ.Данные.Сводной.Таблицы()

Это очень полезная функция.
Если Вы попробуете в любую ячейку ввести мышкой ссылку на ячейку в области данных сводной таблицы, Вы получите не просто ссылку, а формулу с функцией ПОЛУЧИТЬ.Данные.Сводной.Таблицы().

Например так:

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма";'Сальдо внутр'!$A$3;"Шифр";"01.1";"Дата";ДАТА(2008;11;23))

Набор аргументов этой функции зависит от структуры сводной таблицы и меняется. Первые два - это поле данных и указание на адрес верхней правой ячейки сводной таблицы. А дальше идут фильтры по полям строк и столбцов сводной.
В приведенном примере будет извлечен итог по полю Сумма для шифра "01.1" на 23.11.2008.

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

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

=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("Сумма";'Сальдо внутр'!$A$3;"Шифр";A18;"Дата";'Исходные данные'!$B$3)

Видите? Было

"Шифр";"01.1"

Стало

"Шифр";A18

Аналогично изменен фильтр по дате.
Теперь Вы можете подставить любой шифр в ячейку А18 и формула извлечет нужные данные.
Что это нам дает?
Можно в любой столбец отчета ввести список нужных объектов (шифров, наименований, фамилий), а в другой извлечь нужные данные по этому объекту из сводной таблицы. Одну формулу получаем, использую мышку. Корректируем, заменяя конкретное значение на ссылку в нужный столбец и просто копируем формулу в остальные ячейки столбца.
Причем в третий столбец Вы можете спокойно извлечь данные из другой сводной таблицы.
Например, в один столбец выручку менеджера по продажам из базы продаж, в другой - его заработную плату из бухгалтерии.

Может возникнуть ситуация, когда запрошенных данных не окажется в сводной таблице. Тогда функция вернет ошибку #ССЫЛКА. Исправить ситуацию поможет сложная формула типа

=ЕСЛИ(ЕОШИБКА(формула);0;формула)

Еще важная вещь - функция ПОЛУЧИТЬ.ДАННЫЕ... умеет извлекать только те итоги, которые отображены в сводной таблице. Учитывая, что все оформление и размещение отчета для печати мы выносим на отдельный лист, можно показать итоги с максимальной подробностью.

Sergey Skripay

Спасибо Алексей Шмуйлович за идею, что то похожее я уже частично использую, а вот идея " оформление и размещение отчета для печати мы выносим на отдельный лист" это интересно.
Можно ли попросить файл небольшой файл примера как это у Вас работает?
NULL

Алексей Шмуйлович

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

У меня по такому принципу выбираются данные по 15 предприятиям, а затем строится консолидированный баланс. Обновляется вся система в течение двух минут.

kim k.

#8
Алексей Шмуйлович, спасибо за пояснения!
Возник вопрос о выделении нескольких ячеек с помощью ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()
Можно разделять аргументыс помощью ";", но если надо включить в формулу много элементов находящихся рядом с друг другом -- есть ли какая-то возможность обвести их маркером как в обычной СУММ()?
При перечислении аргументов формулы с помощью ";" довольно быстро исчерпается максимальная длинна формулы (ведь каждый аргумент является не короким адресом на ячейку, но длиннющим перечислением фильтров)

Т.е. я пытаюсь получить сумму подобную этой:
=СУММ(ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(" Кінцеве Сальдо";$A$4;"Рік";"2008 р.";"Квартал";"4 Квартал";"Місяць";"Грудень";"Группа 1 контрагентов";"Гуртожиток";"Контрагенты";"Озёрный К.Н.";"Місяць2";"Группа1") :
'это начало диапазона
ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(" Кінцеве Сальдо";$A$4;"Рік";"2008 р.";"Квартал";"4 Квартал";"Місяць";"Грудень";"Группа 1 контрагентов";"Гуртожиток";"Контрагенты";"Ярошенко А.М.";"Місяць2";"Группа1")
'это конец диапазона

+ второй вопрос (возник при просмотре вашего файла): как вы сделали что сгруппированные данные разворачиваются вниз от плюса группировки?

Алексей Шмуйлович

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() - это не ссылка ня ячейку, а функция. Поэтому сумм(получить...():получить...()) не сработает. Нужно =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ()+ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ().
Или другой, более правильный вариант, настроить саму сводную так, чтобы нужная Вам сумма там считалась. Вы можете скрыть ненужные значения в сводной, тогда итог будет такой как Вам нужно. Если нужны разные варианты, сделайте копию листа сводной и настройте по-разному.

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

kim k.

Ок, на разных листах так наверное лучше всего
Спасибо!

nkuznetsova

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

GWolf

Цитата: nkuznetsova от 28.11.2008, 10:26
А я если честно такую функцию раньше использовала, а потом перестала. А причина в том, что набор информации у меня не жесткий (например, добавляется нвоый контрагент или еще что-нибудь) и поэтому приходилось постоянно искать почему итоги в печатной форме отличаются от сводной. :-\


Ну! Ежли в русле "маньяков СТ", то возьмите диапазон побольше, а затем пустые строки отключите! ;D
Путей к вершине - множество. Этот один из многих!

Saturn

2 Алексей Шмуйлович
Вижу, что вы используете ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() на больших объемах данных  :o. Действительно, очень удобное решение, сам его активно использую. Можете ли что-то посоветовать по увеличению производительности этой функции?! (Хочу ускорить пересчет файла, у меня эта функция используется в нескольких сотнях тысяч ячеек одного файла). Влияет ли на скорость работы этой функции:
1. текстовые или числовые аргументы функции (имхо влияет, но не пойму, на сколько значительно)
2. использование "=ЕСЛИ(ЕОШИБКА(". Может быть эффективнее добавить в исходный данные все возможные варианты (с нулевыми значениями) что бы не проводить проверку.

И общие вопросы по производительности к гуру:
какова степень влияния на скорость пересчета книги условия (ЕСЛИ), функция СМЕЩ, ВПР (про ограничение исходного диапазона знаю), ссылки на другие листы одной книги?!

Алексей Шмуйлович

Цитата: Saturn от 11.12.2008, 22:34
2 Алексей Шмуйлович
Вижу, что вы используете ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ() на больших объемах данных  :o. Действительно, очень удобное решение, сам его активно использую. Можете ли что-то посоветовать по увеличению производительности этой функции?! (Хочу ускорить пересчет файла, у меня эта функция используется в нескольких сотнях тысяч ячеек одного файла). Влияет ли на скорость работы этой функции:
1. текстовые или числовые аргументы функции (имхо влияет, но не пойму, на сколько значительно)
2. использование "=ЕСЛИ(ЕОШИБКА(". Может быть эффективнее добавить в исходный данные все возможные варианты (с нулевыми значениями) что бы не проводить проверку.

И общие вопросы по производительности к гуру:
какова степень влияния на скорость пересчета книги условия (ЕСЛИ), функция СМЕЩ, ВПР (про ограничение исходного диапазона знаю), ссылки на другие листы одной книги?!

Это вопрос, на который трудно ответить, не видя файл. Если честно, то я плохо понимаю, зачем может понадобиться использовать функцию Получить.Данные... в нескольких сотнях тысяч ячеек. Ведь смысл сводной как раз в том, чтобы отделить громоздкие вычисления на уровне первичных исходных данных от сводных результатов для отчетных таблиц. Просто такое количество информации, как у Вас обозначено, головой человек проанализировать все-равно не сможет, если даже Excel их долго пересчитывает. А если Вы используете ПОЛУЧИТЬ.ДАННЫЕ для каких-то дополнительных промежуточных вычислений, то скорее всего, раз с помощью сводной все вычисления сделать не получается, было бы эффективнее делать их не в Excel, а, например в Access.
Saturn, может быть Вы подробнее опишите вашу задачу и схему получения - обработки данных. Может еще чего и посоветуем.