Новости:

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

Главное меню

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

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

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

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

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

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

Цитата: Saturn link=topic=957.msg6412#msg6412
Не представляю как это можно было бы реализовать в БД. Можно ли упростить (с сохранением или увеличением скорости пересчета книги) эту схему в excel?

Конечно, в базе данных имеет смысл обрабатывать только факт, например бухгалтерские данные. Плановые данные, финансовые модели в Excel обрабатывать удобнее. Но если эти модели объемные, будет тормозить :(

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

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

Что-то не пойму, о чем Вы?

Valll

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

Я имел ввиду первое сообщение в этой теме. Если вопрос сводится к суммированию подразделений имеющих одинаковые таблицы данных, то консолидация самый короткий и быстрый способ. Давным-давно я так организовал сведение общего баланса и нескольких форм отчетности обособленных подразделений горноспасателей (около 40 подразделений) с выводом общей итоговой отчётности и камеральной проверкой. Сейчас использую свою бухгалтерскую систему (для своего предприятия) полностью сделанную в Экселе в которой задача озвученная автором темы решается вообще одним кликом (независимо от количества подразделений). _сводные_ таблицы не применяются, а имеется одна плоская БД. Имея такую БД я гарантированно провожу вычисления быстрее чем в Акцессе, а уж про удобств нетипичных экспресс расчётов и говорить не приходится.

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

Цитата: Valll от 07.03.2010, 05:25
_сводные_ таблицы не применяются, а имеется одна плоская БД. Имея такую БД я гарантированно провожу вычисления быстрее чем в Акцессе, а уж про удобств нетипичных экспресс расчётов и говорить не приходится.

Все познается в сравнении. А у меня проводки одного предприятия из 30 за 1 месяц на лист Excel 2007 (!) не помещаются. Так что я в любом случае все это могу хранить только во внешнем источнике.

Valll

#24
Конечно, в таком случае без вариантов - только серьёзная БД! Для своей проги я ставил эксперимент с БД на 16000 записей - считала очень быстро! Но для моей маленькой фирмы это невообразимо много...

Я как то спрашивал на офиц. сайте Майкрософт, что быстрее Эксель или Аксес. Ответ был такой: если БД одна (плоская), то Эксель лучше, если БД релятивисткая (много БД), то Аксес. У меня вся информация в одной БД. И вообще вся прога со всеми таблицами в одном файле, который сразу грузится в оперативную память - отсюда и скорость.

safin a.v.

Всем здравствуйте!!!
скажите пожалуйста...я тут пытался упростить свою работу с excel т.е: у меня есть большой обьем данных (причем каждый месяц в него может что-то добавляться.)с продажами товаров по месяцам (сумма и кол-во),разные поставщики и т.п. и остатки на каждую неделю месяца, в конечном итоге мне нужно заказать товар с учетом продаж и наличия на остатках в течении месяца.Раньше делал через "впр" но excel глючит-долго открывает, большой обьем данных.Попытался через сводную таблицу, но столкнулся с проблемой невозможности копировать и вставлять артикула по которым она будет подставлять все остальное.

помогите пожалуйста!!! может у кого-то есть другие решения проблемы?

R Dmitry

А может проще Palo BI Suite  использовать, месяц потренируетесь а потом все лететать будет без всяких заморочек

kotyambala

Господа нужна помощь...
Имеем статьи расходв за 2008 и 2009 г.г.
Первичные _сводные_ таблицы я сделал... а вот с консолидированной сводной таблицей составленной на базе вервых двух (отдельно за 2008 и за 2009г.г.) и меются проблемы....
Итак консолидированная таблица расположена на закладке "Лист1" и как сделать:
1. чтобы в этой таблице кроме выводимой сыммы за два года отображалась разница по формуле: 2008год - 2009год. Дабы посмотреть динамику изменения расходов.
2. Как можно отсортировать строки вручную как еслибы в строках были численые значения.... т.е. по порядку: 1,2,3,4,5,6,7, и т.д.
3. И я никак не пойму для чего могут понадобиться добавление количества полей страницы сводной таблицы (форму я вложил ввиде рисунка)....

Sr.GIO

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

R Dmitry

а у меня все работает на palo,  если количество измерений не слишком много, а я думаю их немного для бюджета, необходимо примерно 6-7 измерений то это в самый раз то что вам нужно