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

Обмен опытом => Microsoft Excel => Тема начата: Sergey Skripay от 18.11.2008, 02:08

Название: Бюджет с помощью сводных таблиц Excel
Отправлено: Sergey Skripay от 18.11.2008, 02:08
Бюджет составляется по нескольким финансовым подразделениям, которые нужно группировать. Сейчас все делаю в Excel через статические таблицы, но небольшое изменение в структуре подразделений или категорий расходов влечет за собой переделку всего файла.
Попробовал использовать _сводные_ (http://msexcel.ru/content/view/34/2/) таблицы Excel для этой задачи, достаточно гибко, но не могу в сводных таблицах сделать вычисление баланса ( текущий баланс = баланс предыдущего месяца+доходы текущего месяца- расходы текущего месяца).
Использует ли кто то _сводные_ (http://msexcel.ru/content/view/34/2/) таблицы Excel для подобных задач?
Буду благодарен за все советы.
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: GWolf от 18.11.2008, 10:39
Во-во, мы тоже пытались прикрутить к подобной задаче СТ. В итоге пришли к выводу - макросом проще. Проще в том плане, что нет риска в самый неподходящий момент получить глюк СТ. И ежли в макропрограмме отладкой можно найти причину почти всегда, то с СТ зачастую незнаешь что и делать. А времечко неждет ...
А посему ежли есть желание давайте соберем Ваш баланс на основе исходных таблиц (не СТ ;D).
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Sergey Skripay от 18.11.2008, 10:51
Цитата: GWolf от 18.11.2008, 10:39

А посему ежли есть желание давайте соберем Ваш баланс на основе исходных таблиц (не СТ ;D).
Значит не один я с этим мучаюсь.
Давайте соберем, можно для примера Ваше решение?
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Andrey Lenets от 18.11.2008, 10:58
GWolf, не вводите людей в заблуждение! _сводные_ (https://msexcel.ru/content/view/34/2/) таблицы НЕ ГЛЮЧАТ! надо уметь с ними работать...

Sergey, _сводные_ (https://msexcel.ru/content/view/34/2/) таблицы имеют вычисляемые поля и объекты, однако они не преспособлены для вычислений по отдельным полям. В любом случае, многое зависит от исходных данных. Можно использовать сводную таблицу как инструмент сведения данных, а дополнительные вычичления производить по результатам...
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: GWolf от 18.11.2008, 11:06
Цитата: Andrey Lenets от 18.11.2008, 10:58
GWolf, не вводите людей в заблуждение! _сводные_ (https://msexcel.ru/content/view/34/2/) таблицы НЕ ГЛЮЧАТ! надо уметь с ними работать...

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

Прошу учесть, я сам пользуюсь СТ когда необходимо! ;D Только вот заметил, что чаще и проще мне решить задачу на VBA, а не СТ. Но это наверное от задач зависит?!
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Алексей Шмуйлович от 18.11.2008, 20:18
Цитата: Andrey Lenets от 18.11.2008, 10:58
GWolf, не вводите людей в заблуждение! _сводные_ (https://msexcel.ru/content/view/34/2/) таблицы НЕ ГЛЮЧАТ! надо уметь с ними работать...

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

Поддерживаю Андрея.
Я очень широко использую _сводные_ (https://msexcel.ru/content/view/34/2/) таблицы именно по писанным в первом посте причинам - нужно сводить очень большие объемы информации и структура источников исходных данных часто меняется.

Использование вычисляемых полей и объектов сводной оправдано только для небольших источников данных. Если исходная таблица большая, то вычисления очень замедляют пересчет сводной.
Я использую _сводные_ (https://msexcel.ru/content/view/34/2/) таблицы, подключенные к внешним базам данных на несколько миллионов строк. Так вот добавление вычисляемого объекта приводит к тому, что любое изменение в макете сводной пересчитывается на хорошей машине 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;формула)

Еще важная вещь - функция ПОЛУЧИТЬ.ДАННЫЕ... умеет извлекать только те итоги, которые отображены в сводной таблице. Учитывая, что все оформление и размещение отчета для печати мы выносим на отдельный лист, можно показать итоги с максимальной подробностью.
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Sergey Skripay от 18.11.2008, 22:44
Спасибо Алексей Шмуйлович за идею, что то похожее я уже частично использую, а вот идея " оформление и размещение отчета для печати мы выносим на отдельный лист" это интересно.
Можно ли попросить файл небольшой файл примера как это у Вас работает?
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Алексей Шмуйлович от 19.11.2008, 00:27
Хорошо. Выкладываю сильно урезанный пример построения баланса на основании данных об остатках на бух. счетах. Данные расположены на сервере SQL. В файле примера данные в сводной сознательно не сохранены - информация конфиденциальная.

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

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

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

Группировка через меню Данные - Группа и структура - группировать. Там же есть настройки структуры, где можно выбрать - вверху отображать итоги или внизу.
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: kim k. от 20.11.2008, 16:33
Ок, на разных листах так наверное лучше всего
Спасибо!
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: nkuznetsova от 28.11.2008, 10:26
А я если честно такую функцию раньше использовала, а потом перестала. А причина в том, что набор информации у меня не жесткий (например, добавляется нвоый контрагент или еще что-нибудь) и поэтому приходилось постоянно искать почему итоги в печатной форме отличаются от сводной. :-\
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: GWolf от 28.11.2008, 11:00
Цитата: nkuznetsova от 28.11.2008, 10:26
А я если честно такую функцию раньше использовала, а потом перестала. А причина в том, что набор информации у меня не жесткий (например, добавляется нвоый контрагент или еще что-нибудь) и поэтому приходилось постоянно искать почему итоги в печатной форме отличаются от сводной. :-\


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

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

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

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

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

По большому счету, видимо, это должна быть некая БД, но БД не удобна с точки зрения промежуточных и дополнительных расчетов.
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: GWolf от 17.12.2008, 15:07
Цитата: Saturn от 17.12.2008, 10:48
Книга excel выполняет роль консолидации бюджета.
... но БД не удобна с точки зрения промежуточных и дополнительных расчетов.
Рискую навлечь негодование Andrey Lenets, но ... Как нет одного пути к вершине, так и нет одного решения задачь в Excel! А поэтому, я эту тему решил на VBA. И все работает! Если мой путь интересен - шлите пример.
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Алексей Шмуйлович от 18.12.2008, 00:05
Ну, по-моему, общий алгоритм такой:
Все универсальные действия (группировка, разделение на внутренние и внешние обороты для консолидации и т.п.) по-максимуму делаем в базе данных, где хранятся исходные данные учета.
До сводной Excel дотягиваем максимально сжатую выборку - ни больше и не меньше того, что понадобится в итоговых отчетах.
В Excel делаем именно специфические расчеты (нестандартные корректировки, подкрутки) и визуальное оформление отчетов.
Для скорости на больших объемах данных нежелательно использовать в сводных формулы и вычисляемые объекты.
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Saturn от 18.12.2008, 15:12
2 GWolf
Спасибо, но с VBA знаком на примитивном уровне, поэтому этот вариант не для меня  :( Хотя узнать, что именно автоматизированно на VBA было бы интересно...

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

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

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

Не представляю как это можно было бы реализовать в БД. Можно ли упростить (с сохранением или увеличением скорости пересчета книги) эту схему в excel?
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Valll от 05.03.2010, 22:39
Может я чего не понял, но... не проще ли использовать функцию консолидации данных?
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Алексей Шмуйлович от 05.03.2010, 22:51
Цитата: Saturn link=topic=957.msg6412#msg6412
Не представляю как это можно было бы реализовать в БД. Можно ли упростить (с сохранением или увеличением скорости пересчета книги) эту схему в excel?

Конечно, в базе данных имеет смысл обрабатывать только факт, например бухгалтерские данные. Плановые данные, финансовые модели в Excel обрабатывать удобнее. Но если эти модели объемные, будет тормозить :(
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Алексей Шмуйлович от 05.03.2010, 22:52
Цитата: Valll от 05.03.2010, 22:39
Может я чего не понял, но... не проще ли использовать функцию консолидации данных?

Что-то не пойму, о чем Вы?
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Valll от 07.03.2010, 05:25
Цитата: Sergey Skripay от 18.11.2008, 02:08
Бюджет составляется по нескольким финансовым подразделениям, которые нужно группировать. Сейчас все делаю в Excel через статические таблицы, но небольшое изменение в структуре подразделений или категорий расходов влечет за собой переделку всего файла.
Попробовал использовать _сводные_ (http://msexcel.ru/content/view/34/2/) таблицы Excel для этой задачи, достаточно гибко, но не могу в сводных таблицах сделать вычисление баланса ( текущий баланс = баланс предыдущего месяца+доходы текущего месяца- расходы текущего месяца).
Использует ли кто то _сводные_ (http://msexcel.ru/content/view/34/2/) таблицы Excel для подобных задач?
Буду благодарен за все советы.

Я имел ввиду первое сообщение в этой теме. Если вопрос сводится к суммированию подразделений имеющих одинаковые таблицы данных, то консолидация самый короткий и быстрый способ. Давным-давно я так организовал сведение общего баланса и нескольких форм отчетности обособленных подразделений горноспасателей (около 40 подразделений) с выводом общей итоговой отчётности и камеральной проверкой. Сейчас использую свою бухгалтерскую систему (для своего предприятия) полностью сделанную в Экселе в которой задача озвученная автором темы решается вообще одним кликом (независимо от количества подразделений). _сводные_ (https://msexcel.ru/content/view/34/2/) таблицы не применяются, а имеется одна плоская БД. Имея такую БД я гарантированно провожу вычисления быстрее чем в Акцессе, а уж про удобств нетипичных экспресс расчётов и говорить не приходится.
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Алексей Шмуйлович от 07.03.2010, 08:19
Цитата: Valll от 07.03.2010, 05:25
_сводные_ (https://msexcel.ru/content/view/34/2/) таблицы не применяются, а имеется одна плоская БД. Имея такую БД я гарантированно провожу вычисления быстрее чем в Акцессе, а уж про удобств нетипичных экспресс расчётов и говорить не приходится.

Все познается в сравнении. А у меня проводки одного предприятия из 30 за 1 месяц на лист Excel 2007 (!) не помещаются. Так что я в любом случае все это могу хранить только во внешнем источнике.
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Valll от 07.03.2010, 08:55
Конечно, в таком случае без вариантов - только серьёзная БД! Для своей проги я ставил эксперимент с БД на 16000 записей - считала очень быстро! Но для моей маленькой фирмы это невообразимо много...

Я как то спрашивал на офиц. сайте Майкрософт, что быстрее Эксель или Аксес. Ответ был такой: если БД одна (плоская), то Эксель лучше, если БД релятивисткая (много БД), то Аксес. У меня вся информация в одной БД. И вообще вся прога со всеми таблицами в одном файле, который сразу грузится в оперативную память - отсюда и скорость.
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: safin a.v. от 16.03.2010, 19:02
Всем здравствуйте!!!
скажите пожалуйста...я тут пытался упростить свою работу с excel т.е: у меня есть большой обьем данных (причем каждый месяц в него может что-то добавляться.)с продажами товаров по месяцам (сумма и кол-во),разные поставщики и т.п. и остатки на каждую неделю месяца, в конечном итоге мне нужно заказать товар с учетом продаж и наличия на остатках в течении месяца.Раньше делал через "впр" но excel глючит-долго открывает, большой обьем данных.Попытался через сводную таблицу, но столкнулся с проблемой невозможности копировать и вставлять артикула по которым она будет подставлять все остальное.

помогите пожалуйста!!! может у кого-то есть другие решения проблемы?
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: R Dmitry от 13.04.2010, 18:50
А может проще Palo BI Suite  использовать, месяц потренируетесь а потом все лететать будет без всяких заморочек
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: kotyambala от 18.05.2010, 11:28
Господа нужна помощь...
Имеем статьи расходв за 2008 и 2009 г.г.
Первичные _сводные_ (https://msexcel.ru/content/view/34/2/) таблицы я сделал... а вот с консолидированной сводной таблицей составленной на базе вервых двух (отдельно за 2008 и за 2009г.г.) и меются проблемы....
Итак консолидированная таблица расположена на закладке "Лист1" и как сделать:
1. чтобы в этой таблице кроме выводимой сыммы за два года отображалась разница по формуле: 2008год - 2009год. Дабы посмотреть динамику изменения расходов.
2. Как можно отсортировать строки вручную как еслибы в строках были численые значения.... т.е. по порядку: 1,2,3,4,5,6,7, и т.д.
3. И я никак не пойму для чего могут понадобиться добавление количества полей страницы сводной таблицы (форму я вложил ввиде рисунка)....
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Sr.GIO от 25.05.2010, 16:16
Подскжите дилетанту, как в сводной таблице отобразить скрытый правой кнопкой мыши столбец (пр.кн. по столбцу-скрыть, а как вернуть не могу понять, нет нигде отобразить :-[)
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: R Dmitry от 29.05.2010, 17:55
а у меня все работает на palo,  если количество измерений не слишком много, а я думаю их немного для бюджета, необходимо примерно 6-7 измерений то это в самый раз то что вам нужно
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: irridium77 от 05.06.2010, 01:36
Еще удобное решение когда на одном листе располагаешь сводную таблицу или ряд сводных таблиц, настраиваешь их нужным образом, а затем  заставляешь vb управлять ей ими а результаты перекидываешь на итоговый лист, правда нужно следить чтоб одна таблица на другую не налезала и для начала лучше снять галочку выделять несколько элементов.
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: A_00016 от 06.08.2010, 15:13
Цитата: Алексей Шмуйлович от 18.11.2008, 20:18
Цитата: Andrey Lenets от 18.11.2008, 10:58
GWolf, не вводите людей в заблуждение! _сводные_ (https://msexcel.ru/content/view/34/2/) таблицы НЕ ГЛЮЧАТ! надо уметь с ними работать...

..... Я использую _сводные_ (http://msexcel.ru/content/view/34/2/) таблицы, подключенные к внешним базам данных на несколько миллионов строк.

Добрый день, меня зовут Владимир.
Пожалуйста, подскажите как Вы подключаете _сводные_ (https://msexcel.ru/content/view/34/2/) таблицы к внешним базам данных?
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: A_00016 от 06.08.2010, 19:01
Не знаю как исправить предыдущее сообщение.
Собственно вопрос в том как подключить _сводные_ (https://msexcel.ru/content/view/34/2/) таблицы к внешним базам данных, чтоб данные выгружались сразу в них.
Спасибо.
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Serge 007 от 25.08.2010, 20:43
Цитата: A_00016 от 06.08.2010, 19:01
Собственно вопрос в том как подключить _сводные_ (http://msexcel.ru/content/view/34/2/) таблицы к внешним базам данных, чтоб данные выгружались сразу в них.
Первый шаг мастера, точку на "во внешних источниках данных".
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Sarytai от 08.09.2010, 22:20
Коллеги, честно говоря, не понимаю, почему вы используете для бюджета _сводные_ (https://msexcel.ru/content/view/34/2/) таблицы.
Бюджет - форма достаточно статичная и не требует использования СТ, т.к. не так много данных нужно суммировать. ВОт для отчетов или для балансов - это да, СТ просто инструмент номер 1. В бюджете же предлагаю просто использовать определенную форму, два листа: 1 - для данных, второй для итоговой формы бюджета.
я финдир и меняю бюджет не реже раза в месяц. но при этой структуре мне надо только правильно забить данные и все изменения через простые формулы отразятся. главное, чтобы не запутаться, соблюдать следующие правила:
1) чистые данные (не результат формул) выделять другим цветом, чтобы потом легко можно было найти ошибку или править данные; например, человека только берем на работу, его зп первый раз выделяется зеленым цветом, остальное через равно.
2) предусмотреть ячейки проверки. например при зп есть разделы "оклад", "подоходный", "соцвзносы", "зп на руки". проверка простая: делаем в конце листа данных раздел "проверка", где заводим ячейки проверки зп, где формула=оклад-подоходный-соцвзносы-зп. И делаем условное_форматирование (https://msexcel.ru/content/view/116/2/), что если ячейка не равно нулю, то пусть фон будет ядовито-оранжевый или красный.
3) везде соблюдать правильную структуру. редактирование данных должно происходить или изменением непосредственно в самих ячейках или добавлением целой строки.

Если непонятно объяснил, могу выслать форму бюджета. щас под руками нет.
Название: Связь исходной и сводной таблиц Excel 2007
Отправлено: Анна от 01.10.2010, 09:24
Подскажите, плиз! Я посторила сводную таблицу 2007. Но, когда я в исходной таблице устанавливаю фильтр(выбираю период временной), то в сводной ничего не меняется :-[. А как сделать так, чтобы и сводная менялась в зависимости от фильтра исходной таблицы? ::)
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Анна от 01.10.2010, 09:55
Ой, кажется сама нашла 8) Добавила в сводной таблице в область "Фильтр отчета" "период"(один из столбцов исходной таблицы). Красота!!! :)
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Shakirt от 22.11.2010, 14:30
Sarytai
добрый день! Если не сложно, вышли пожалуйста форму бюджета, которую ты используешь. Спасибо
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: iron priest от 17.02.2011, 00:13
подскажите, можно ли в сводной таблице сделать атк шобы выводились стоблцы за полугодие, за 9 мес. и за год. сама таблица соответственно состоит из 4 кварталов

нада получить таблицу следующиего вида

1 кв 2 кв полугодие 3 кв 9 мес 4 кв год
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: iron priest от 17.02.2011, 08:51
осё
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Serge 007 от 17.02.2011, 10:28
Лови.
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: iron priest от 17.02.2011, 18:19
круто, а как это сделано?
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Serge 007 от 17.02.2011, 23:20
С помощью вычисляемых элементов.
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: iron priest от 18.02.2011, 08:26
ниче не понял.
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Serge 007 от 18.02.2011, 23:45
Рассказывать про вычисляемые элементы долго, поэтому задавай вопросы.

ЗЫ Какой Экс?
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: iron priest от 19.02.2011, 08:24
ну как вставить эти столбцы, я уже вроде все перерыл
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Serge 007 от 19.02.2011, 18:36
ЦитироватьЗЫ Какой Экс?
2010: На ленте: Параметры - Вычисления - Поля, элементы и наборы - вычисляемый объект
2007: На ленте: Параметры - формулы - вычисляемый объект
2003: На панели инструментов "_сводные_ (https://msexcel.ru/content/view/34/2/) таблицы": Сводная таблица - формулы - вычисляемый объект
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: iron priest от 24.02.2011, 13:52
понял в чем соль! гениально!
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: iron priest от 16.03.2011, 15:25
доброго дня

помогите сделать вычисляемое поле "рентабельность 1 ед. реализованной продукции"

рассчитывает следующим образом "Ср. цена по расходу"/"Ср. цена по приходу"-1

Ср. цена это уже доп. поле, которые создал. если его нельзя будет использовать в формуле, то "рентабельность 1 ед. реализованной продукции"=

сумма по расходу/кол-во по расходу/сумма по приходу/кол-во по приходу-1
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: iron priest от 17.03.2011, 21:46
можно ли сделать вот такое. например таблица данных

01.01.11 100 грн..
02.01.11 50 грн.
05.01.11 10 грн.

нужно чтобы в сводной отображалось

01.01.11 100 грн..
02.01.11 50 грн.
03.01.11 50 грн. (сумма предыдущей даты)
04.01.11 50 грн. (сумма предыдущей даты)
05.01.11 10 грн.
06.01.11 10 грн. (сумма предыдущей даты)
...
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: Мария_Vl от 14.07.2011, 13:58
Товарищи!!!


Как сделать сводную таблицу из двух файлов????
Название: Re: Бюджет с помощю сводных таблиц Excel
Отправлено: iron priest от 21.08.2011, 12:34
Цитата: Мария_Vl от 14.07.2011, 13:58
Как сделать сводную таблицу из двух файлов????

https://forum.msexcel.ru/microsoft_excel/znayuschie_lyudi_pomogite_so_svodnoy_tablitsey-t6179.0.html (https://forum.msexcel.ru/microsoft_excel/znayuschie_lyudi_pomogite_so_svodnoy_tablitsey-t6179.0.html)
Название: Re: Бюджет с помощью сводных таблиц Excel
Отправлено: alexlw от 21.09.2011, 22:13


         Здравствуйте ВСЕМ!
Однажды воспользовавшись Excel, понимаешь её огромные возможности. Но для обывателя, который суммировал простейшие колонки, я поставил себе, наверное,  довольно сложную задачу. 2 недели упрямых освоений  форумов принесли кое-какие результаты (прикладываю файлом), но мозг закипел! Не знаю насколько уместно просить помощи с моей задачей, но может быть для профессионалов всё не так уж сложно.  Итак, моя ситуация:
   Есть некая таблица (Лист1), куда заносятся все расходы и финансовые поступления (как вносить поступления ещё не сделал). Хотелось бы создать некий полный анализ приходов-расходов с отчётностью. Если по пунктам:
   1) отчёт по з/п для каждой фамилии (что благополучно получилось на стр."Сводная")
   2) промежуточный отчёт по "расходам" (суммам выданным разным людям). Причём период отчёта начинается с даты последнего (например г-н Т отчитался последний раз 20.04.2011 (помечено жёлтым) и после этого у него было несколько поступлений (показано на стр."Отчёт"). Каким-то образом нужно ввести потраченные им средства в правой части таблицы (пример на стр."Отчёт") и после этого списать с тов. Т средства, пометив дату списания в таблице "Лист1" (хотя может есть другой способ?). Кроме этого результаты отчёта нужно куда-то(?) сохранить, а лист отчётности должен быть готов к новому отчёту (например для г-на А).
   3) общий отчёт за месяц (квартал, год), где анализируются все приходы (финансовые поступления) и соответственно расходы. Пример на стр. «Общий итог»
    Понимаю, что потрачу пару месяцев, но освою эту, кстати, очень интересную программу.     Но!!!
Буду очень благодарен, если мне кто-нибудь поможет!

Название: Re: Бюджет с помощью сводных таблиц Excel
Отправлено: obrushnikov от 18.03.2012, 00:28
Коллеги вот как можно принципиально решить вопрос бюджета с помощью сводных таблиц в одном файле
http://vimeo.com/37304230 (http://vimeo.com/37304230)

obruschnikov@yandex.ru
Название: Re: Бюджет с помощью сводных таблиц Excel
Отправлено: Sergey Skripay от 19.03.2012, 11:32
Цитата: obrushnikov от 18.03.2012, 00:28
Коллеги вот как можно принципиально решить вопрос бюджета с помощью сводных таблиц в одном файле
http://vimeo.com/37304230 (http://vimeo.com/37304230)

obruschnikov@yandex.ru

А файл примера можно попросить?
Название: Re: Бюджет с помощью сводных таблиц Excel
Отправлено: obrushnikov от 19.03.2012, 16:01
Цитата: Sergey Skripay от 19.03.2012, 11:32
Цитата: obrushnikov от 18.03.2012, 00:28
Коллеги вот как можно принципиально решить вопрос бюджета с помощью сводных таблиц в одном файле
http://vimeo.com/37304230 (http://vimeo.com/37304230)

obruschnikov@yandex.ru

А файл примера можно попросить?

Да конечно. На сайт не могу выложить - большой
http://vk.com/public35779770 (http://vk.com/public35779770) там есть и видео и презентации и файл
Название: Re: Бюджет с помощью сводных таблиц Excel
Отправлено: obrushnikov от 24.03.2012, 21:43
Добрый вечер коллеги.
   Это не статья, как обещал, но по крайней мере краткое видение проблематики создания рабочих инструментов в Excel.
С 2000 года мы занимались автоматизицией бизнес-процессов, и как и многие из нас, не имея кроме Access и Excel простых и надежных инструментов разработки. 1С на то время не мог закрыть все дыры учета, не говоря уже о возможностях моделирования реальных ситуаций.
   Задача стояла не только создать работающие инструменты управления бизнесом, но и разобраться самим в бизнес-процессах.
Сначала на своем бизнесе, затем по друзьям, потом на сторону мы создавали различные (теперь это называется CRM и ERP системы), закрывающие локальные задачи учета и анализа.
   В какой-то момент у нас сложилось стойкое ощущение, что несмотря на совершенно различную структуру данных в реляционнке, структура процессов одна и та же и можно ее как то проще описать. Долгие дебаты закончились вопросом - А что может описать всё?
   Мы это Обрушников Борис и Мартинсон Алексей, собственно разработчики Б7 (http://vimeo.com/37304230 (http://vimeo.com/37304230)) заканчивали по первому образованию лингвистику, поэтому для нас ответ показался простым - язык.
   Обратились к классикам: Синтаксические структуры и парождающая грамматика Хомского (http://ru.wikipedia.org/wiki/ (http://ru.wikipedia.org/wiki/)Синтаксические_структуры) и к Марку Мински теория фреймов " Framework for Representing Knowledge in the Phychology of Computer Vision ".
   В итоге начала вырисовываться концепция того как можно описать процессы с помощью лингвистической структуры данных в противовес реляционной. Канечно до искусственного интеллекта и даже бизнес-интеллекта мы еще не добрались но структурно кое что получилось. Решили сначала закрыть проблему бюджетирования в Excel. Результаты чего мы здесь и представляем. Почему в Excel, потому что всем знакомо.
  По поводу Хомского и Минского если все упростить и без лингистических заумствований общая концепция такая:
Есть глубинная структура языка - она везде одинаковая. упрощенно говоря все мы ее помним по школе (подлежащее сказуемое дополнение определени и тд) это записи в базе, несколько записей с одним контекстом могут группироваться в абзацы, абзацы в тексты, тексты в гипертексты, тексты могут иметь несколько версий (в школе мы учили времена,спряжения и наклонения глаголов) с другой стороны есть фреймы - многомерные деревья, собирающие в себя группы текстов. причем тексты могут рассматриваться как заголовки и вкладываться в гипертексты более высокого уровня.... вплоть до конечного Узла...
   Так вот, кроме глубинной структуры языка, существует так же "надстройка" в виде речи. Не хочу показаться дилетаном, не изучал досканально то что написано по теме по причине занятости. Но по моему мнению, эта надстройка особый вид текста и деревьев данных, который можно в принципе воспроизвести при желании.
   Заканчивая скажу про Б7. Канечно это не бизнесс-интеллект, но на мой взгляд достойная попытка решения прикладных задач хранения и обработки массивов, а также получения сводной аналитики по неограниченному числу разрезов многомерных данных.
Для бизнеса мы решили задачу бюджетирования, зарплатного учета, динамической оргштатной структуры и финансового моделирования.
Кому интересны наши прикладные решения милости просим 8-937-700-5000 obruschnikov@yandex.ru
Название: Re: Бюджет с помощью сводных таблиц Excel
Отправлено: obrushnikov от 24.03.2012, 22:10
P.S. По поводу Palo
Мы тоже пытались сначала развязать _сводные_ (https://msexcel.ru/content/view/34/2/) таблицы для редактирования в них. Полгода помню убили на это. Потом догадались, как обойти эту гибельную задачу. Поэтому сейчас с ужасом смотрю на эти формулы внутри псевдосводных таблиц. Но ребята молодцы доделали доработали.
Название: Re: Бюджет с помощью сводных таблиц Excel
Отправлено: iron priest от 20.06.2012, 16:57
Добрый день.

что-то не могу понять, можно ли сделать промежуточный итог по полю? например идет поле "дебет" и "кредит" с элементами в виде счета "10,20 итд) нужно сделать в конце итог для этих счетов. количество счетов неизвестно заранее. стандартным путем промежуточный итог идет после каждого счета, а нужно по всем в группе "дебет" и "кредит"
Название: Re: Бюджет с помощью сводных таблиц Excel
Отправлено: iron priest от 20.06.2012, 17:23
вроде вопрос снят, добавил в базу еще 1 столбец
Название: Re: Бюджет с помощью сводных таблиц Excel
Отправлено: Алексей Шмуйлович от 20.06.2012, 17:29
Нужно ДО и КО сделать не полями, а текстовым значением одного поля Вид оборота. И корсчет вытащить в одно поле.
Название: Re: Бюджет с помощью сводных таблиц Excel
Отправлено: iron priest от 20.06.2012, 18:00
так и сделал)))
Название: Re: Бюджет с помощью сводных таблиц Excel
Отправлено: iron priest от 21.06.2012, 09:52
Вот такой еще вопрос, как сделать чтобы фильтр в сводной таблицы брался из ячейки, а не выбирался вручную, и сводная автоматом обновлялась
Название: Re: Бюджет с помощью сводных таблиц Excel
Отправлено: Алексей Шмуйлович от 21.06.2012, 13:47
Видимо макросом по событию пересчета листа.
Название: Re: Бюджет с помощью сводных таблиц Excel
Отправлено: Sergey Skripay от 09.09.2016, 20:14
Как извлекать данные из сводной таблицы построенной на основе powerquery?

Если детально, не проблема получать данные из обычной сводной таблицы используя переменные
например обычняа сводная таблица:
исходная =ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("сумма";$I$12;"категория";"категория1") меняем с переменной (ссылкой на ячейку M12) ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("сумма";$I$12;"категория";M12)  работает

а сводная таблица на основе powerquery
исходная
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("[Measures].[Сумма по столбцу Сумма]";$A$3;"[Q1].[Категория]";"[Q1].[Категория].&[Обслуживание]")
при замене на
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ("[Measures].[Сумма по столбцу Сумма]";$A$3;"[Q1].[Категория]";G11) НЕ работает, результат #ССЫЛКА!

Название: Re: Бюджет с помощью сводных таблиц Excel
Отправлено: kuklp1 от 09.09.2016, 21:47
Кросс:
http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=81819&TITLE_SEO=81819-vyborka-iz-svodnoy-tablitsy-postroennoy-na-osnove-power-query