Новости:

Теперь на форум можно залогиниться / зарегистрироваться с помощью ВКонтакте. Уже существующие пользователи могут связать свою учетную запись с аккаунтом ВКонтакте одним кликом в профиле пользователя http://forum.msexcel.ru/index.php?action=profile;area=account

Главное меню

Бюджет с помощью сводных таблиц 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, может быть Вы подробнее опишите вашу задачу и схему получения - обработки данных. Может еще чего и посоветуем.

Saturn

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

Собственно задача: как большое количество данных из 1-й (2-х) таблиц раскидать на кучу кастомизированных форм.

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

GWolf

Цитата: Saturn от 17.12.2008, 10:48
Книга excel выполняет роль консолидации бюджета.
... но БД не удобна с точки зрения промежуточных и дополнительных расчетов.
Рискую навлечь негодование Andrey Lenets, но ... Как нет одного пути к вершине, так и нет одного решения задачь в Excel! А поэтому, я эту тему решил на VBA. И все работает! Если мой путь интересен - шлите пример.
Путей к вершине - множество. Этот один из многих!

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

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

Saturn

2 GWolf
Спасибо, но с VBA знаком на примитивном уровне, поэтому этот вариант не для меня  :( Хотя узнать, что именно автоматизированно на VBA было бы интересно...

2 Алексей Шмуйлович
Учетных системы 2:
1) 1С 7.1 Разнесения оборотов (заведенные счета)
2) 1С 7.? счета бухг. учета.
Из обоих выгружается в Excel, во втором случае производятся промежуточные корректировки.

Затем в файле с бюджетом обновляются _сводные_ таблицы (группируя счета в постатьи), забирая данные из предыдущих 2-х.
Данные, возможно, можно было бы группировать (подстатьи в статьи) в 1С (1), но перед группировкой производится подмена отсутствующего факта прогнозом. Прогноз делается в файле с бюджетом. Существуют несколько прогнозных моделей:
план
факт предыдущего периода
среднее из прогнозов/фактов предыдущих периодов
константа
резерв
доля от заданной базы

Сначала реализовал в экселе формулу той или иной прогнозной модели, в зависимости от номера прогнозной модели, проставленной у отсутствующего факта (по любой аналитике), но такая структура (=ЕСЛИ;ТО;ИНАЧЕ(ЕСЛИ....) надолго растягивала обновление книги. Потом поменял подход: все данные пересчитываются по каждой модели прогноза, а затем нужный результат "выбирается" перемножением результата каждой прогнозной модели на проверку совпадения номера прогнозной модели с выбранным значением, и их суммированием, т.е.:
(сокращения)
выбранная прогнозная модель (ВПМ)
номер прогнозной модели (НПМ)
Результат вычисления прогнозной модели (РПМ)
=(ВПМ=НПМ1*РПМ1)+(ВПМ=НПМ2*РПМ2)+(ВПМ=НПМ3*РПМ3)+(ВПМ=НПМ4*РПМ4)+(ВПМ=НПМ5*РПМ5)+(ВПМ=НПМ6*РПМ6)
если мы выбрали прогнозную модель №3 (проставили 3 в ячейку), то формула выдаст =0+0+РПМ3+0+0

Не представляю как это можно было бы реализовать в БД. Можно ли упростить (с сохранением или увеличением скорости пересчета книги) эту схему в excel?

Valll

Может я чего не понял, но... не проще ли использовать функцию консолидации данных?