Профессиональные приемы работы в Microsoft Excel

Пожалуйста, войдите или зарегистрируйтесь.


Расширенный поиск  

Новости:

Новая редакция правил форума: 2.4. Если вопрос или ответ содержится во вложенном файле, все-равно кратко описывайте в сообщении вопрос или суть решения. Это необходимо, чтобы тему можно было найти через поиск.

Автор Тема: Бюджет с помощью сводных таблиц Excel  (Прочитано 43924 раз)

0 Пользователей и 1 Гость просматривают эту тему.

Sergey Skripay

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 43

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

GWolf

  • Старожил
  • ****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 926
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #1 : 18.11.2008, 10:39:52 »

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

Sergey Skripay

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 43
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #2 : 18.11.2008, 10:51:37 »


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

Andrey Lenets

  • Старожил
  • ****
  • Уважение: +54/-1
  • Оффлайн Оффлайн
  • Сообщений: 556
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #3 : 18.11.2008, 10:58:39 »

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

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

GWolf

  • Старожил
  • ****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 926
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #4 : 18.11.2008, 11:06:23 »

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

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

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

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

  • Ветеран
  • *****
  • Уважение: +31/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 085
  • WORK SMARTER, NOT HARDER
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #5 : 18.11.2008, 20:18:31 »

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

Sergey, [url=http://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

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 43
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #6 : 18.11.2008, 22:44:36 »

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

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

  • Ветеран
  • *****
  • Уважение: +31/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 085
  • WORK SMARTER, NOT HARDER
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #7 : 19.11.2008, 00:27:32 »

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

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

kim k.

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 47
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #8 : 19.11.2008, 10:51:56 »

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

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

+ второй вопрос (возник при просмотре вашего файла): как вы сделали что сгруппированные данные разворачиваются вниз от плюса группировки?
« Последнее редактирование: 19.11.2008, 11:11:04 от kim k. »
Записан

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

  • Ветеран
  • *****
  • Уважение: +31/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 085
  • WORK SMARTER, NOT HARDER
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #9 : 20.11.2008, 00:50:36 »

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

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

kim k.

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 47
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #10 : 20.11.2008, 16:33:01 »

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

nkuznetsova

  • Новичок
  • *
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 5
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #11 : 28.11.2008, 10:26:04 »

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

GWolf

  • Старожил
  • ****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 926
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #12 : 28.11.2008, 11:00:44 »

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


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

Saturn

  • Постоялец
  • ***
  • Уважение: +6/-0
  • Оффлайн Оффлайн
  • Сообщений: 101
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #13 : 11.12.2008, 22:34:52 »

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

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

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

  • Ветеран
  • *****
  • Уважение: +31/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 085
  • WORK SMARTER, NOT HARDER
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #14 : 17.12.2008, 03:17:02 »

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

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

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

Saturn

  • Постоялец
  • ***
  • Уважение: +6/-0
  • Оффлайн Оффлайн
  • Сообщений: 101
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #15 : 17.12.2008, 10:48:11 »

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

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

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

GWolf

  • Старожил
  • ****
  • Уважение: +50/-0
  • Оффлайн Оффлайн
  • Сообщений: 926
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #16 : 17.12.2008, 15:07:58 »

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

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

  • Ветеран
  • *****
  • Уважение: +31/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 085
  • WORK SMARTER, NOT HARDER
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #17 : 18.12.2008, 00:05:24 »

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

Saturn

  • Постоялец
  • ***
  • Уважение: +6/-0
  • Оффлайн Оффлайн
  • Сообщений: 101
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #18 : 18.12.2008, 15:12:27 »

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

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 17
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #19 : 05.03.2010, 22:39:57 »

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

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

  • Ветеран
  • *****
  • Уважение: +31/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 085
  • WORK SMARTER, NOT HARDER
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #20 : 05.03.2010, 22:51:28 »

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

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

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

  • Ветеран
  • *****
  • Уважение: +31/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 085
  • WORK SMARTER, NOT HARDER
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #21 : 05.03.2010, 22:52:25 »

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

Что-то не пойму, о чем Вы?
Записан

Valll

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 17
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #22 : 07.03.2010, 05:25:42 »

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

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

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

  • Ветеран
  • *****
  • Уважение: +31/-0
  • Оффлайн Оффлайн
  • Сообщений: 1 085
  • WORK SMARTER, NOT HARDER
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #23 : 07.03.2010, 08:19:18 »

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

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

Valll

  • Пользователь
  • **
  • Уважение: +0/-0
  • Оффлайн Оффлайн
  • Сообщений: 17
Re: Бюджет с помощю сводных таблиц Excel
« Ответ #24 : 07.03.2010, 08:55:17 »

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

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



Темы без ответов

30.09.2018 10:24 Расчет процентов за определенный период (месяц) с учетом изменений и платежей 390
22.05.2018 11:38 Скрипт написать который допишет данные в файл 1053
03.03.2018 00:00 Подсчет отработанного времени, за исключением заранее определенных перерывов 1181
14.02.2018 10:11 Подготовить читабельную отчетность по платежам 1181
23.01.2018 13:46 Найти вероятность повторной покупки 1071
12.01.2018 23:56 Сделать отчет на Power BI (Dashboard) 1508
06.09.2017 10:43 Solver VBA не решает гиперболическое уравнение, но при этом решает гармоническое 1365
17.08.2017 12:15 Гиперссылка и фильтр одновременно макрос 1712
23.05.2017 11:20 Копирование данных из одной таблицы в умную таблицу по условию 3465
15.03.2017 15:45 автозамена картинок PowerPoint 1965





Яндекс цитирования msexcel.ru Яндекс.Метрика

Страница сгенерирована за 0.283 секунд. Запросов: 165.