Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1 2 След.
RSS
Как сделать материальный отчет?
 
#1
09.01.2008 19:54:23
Всем доброго времени суток!  
 
Просьба помочь вот в каком вопросе.  
 
Имеем: материальный отчет, в котором есть данные об остатках на конец месяца (кол-во& сумма) и есть столбцы "приход" и "расход" с суммой и кол-вом, в которые будут вводиться данные о движении товара. Само собой, по каждому наименованию в течение периода может быть несколько приходных и расходных накладных.  
 
Нужно: рассчитать итоги на конец периода, т.е. просуммировать приход и расход и рассчитать конечный остаток по формуле "Остаток на начало+Приход-Расход".  
 
Дополнительные условия: Будут рассчитываться 4 уровня итогов (по убыванию): по конторе в целом, по счету, по подсчету, по наименованию. Т.е. итоги по наименованиям входят в итоги по подсчетам, те входят в итоги по счетам, ну а итоги по счетам входят в итоги по конторе в целом.  
 
После расчета всех итогов нужно перенести остаток на конец периода в остаток на начало периода, обнулив приход и расход.  
 
Итоги по всем уровням и остаток на конец периода прекрасно считаются сводной таблицей, но как выделить из нее остаток на конец периода и вставить его в рабочую таблицу?  
 
Я пробовала пользоваться функцией "Итоги", но опять-таки возникает проблема и переноса остатков, и удаления лишних строк (итоговых по счетам и подсчетам). К тому же нужно избавляться от слова "Итог" в названии товара.  
 
Буду рада всем советам и предложениям.
 
 
 
#2
10.01.2008 08:22:32
{quote}{login=ГлавБух}{date=09.01.2008 07:54}{thema=Как сделать материальный отчет?}{post}как выделить из нее остаток на конец периода и вставить его в рабочую таблицу?{/post}{/quote}Выделить и скопировать не помогает? Без примера как-то сложно посоветовать.
 
 
 
#3
10.01.2008 20:19:36
{quote}{login=Лузер}{date=10.01.2008 08:22}{thema=Re: Как сделать материальный отчет?}{post}{quote}{login=ГлавБух}{date=09.01.2008 07:54}{thema=Как сделать материальный отчет?}{post}как выделить из нее остаток на конец периода и вставить его в рабочую таблицу?{/post}{/quote}Выделить и скопировать не помогает? Без примера как-то сложно посоветовать.{/post}{/quote}  
 
Пример прилагаю.
 
 
 
#4
11.01.2008 08:49:22
я в сводных таблицах не очень..  
но есть же такая функция "получить данные сводной таблицы"
 
 
 
#5
11.01.2008 09:50:08
Файл посмотрел, но не разобрался.  
slan правильно говорит. А пользоваться этой функцией просто: в нужной ячейке нажимаете =, затем кликаете на ячейку сводной - формула вставляется сама.  
Потом, мне кажется, можно построить вторую сводную с теми же данными, в которой будут только поля по остатку (или суммы остатков).  
Я, например, для разных отчетов из одних исходных данных строю две сводных таблицы - одну с разрезом по дням месяца, вторую с разрезом по объектам нарастающим итогом.
 
 
 
#6
11.01.2008 16:43:34
slan, Лузер, спасибо за ответы и советы! Хотя проблема не снята окончательно, пути к решению наметились!
 
 
 
#7
12.01.2008 14:57:25
В итоговой таблице данные должны быть в одну строку?!  
В приложенном в файле примере для сводной таблицы отсутствует параметр по которому будут группироваться данные.  
Не нужные для отображения промежуточные итоги скрываем с отображения просто правой кнопкой и скрыть. Скрывается по всей таблице.  
Дополнительный параметр для группировки можно вывести как на страницу (в примере) так и в столбце.
 
 
 
#8
12.01.2008 18:25:34
{quote}{login=Igor67}{date=12.01.2008 02:57}{thema=}{post}В итоговой таблице данные должны быть в одну строку?!  
В приложенном в файле примере для сводной таблицы отсутствует параметр по которому будут группироваться данные.  
Не нужные для отображения промежуточные итоги скрываем с отображения просто правой кнопкой и скрыть. Скрывается по всей таблице.  
Дополнительный параметр для группировки можно вывести как на страницу (в примере) так и в столбце.{/post}{/quote}  
Igor67, исходя из моей рабочей таблицы, что может быть параметром для группировки? Я пока не очень понимаю, о чем речь.  
В итоговой таблице в строках должны быть размещены наименования итогов - по филиалу, по счету, по подсчету, а в столбцах должны быть сами данные, т.е. итог по остатку, по приходу и по расходу, и по конечному остатку.  
Насчет промежуточных итогов - мне не нравится, что при подведении итогов к наименованию оборудования прилепляется слово "Итог". К тому же, в отчете есть несколько позиций, имеющих одинаковое название, но разную цену. Если я буду пользоваться функцией Итоги, то эти вещи будут просуммированы по количеству, и по сумме, а мне нужно, чтобы эти товары были бы показаны отдельно - т.е. да, название одинаковое, но цены-то разные.
 
 
 
#9
12.01.2008 19:56:26
Я не разбирал итоговую задачу. Я понял, что результат сводной таблицы Вас устраивает, а отображение на экране нет. По ВНЕШНЕМУ виду созданной Вами сводной таблицы она сделана не совсем корректно.  
В прикрепленном файле я сделал новую сводную таблицу и немного подкорректировал исходную. Добавил от "балды" столбец "год".  
Макет можно посмотреть. При этом посчитать построчно Остаток2 нужно тоже заранее. Сводная таблица "выбирает" значения по Вашим параметрам.
 
 
 
#10
12.01.2008 20:10:34
Igor67, Глазам не верю, да, сейчас результат именно тот, который мне хотелось бы видеть. Первоначальная таблица действительно устраивала по результату, но ее вид, ммм, оставлял желать... Спасибо громадное!    
 
Вот если бы справиться еще с одним нюансиком,    
см. http://www.planetaexcel.ru/forum.php?thread_id=2058
 
 
 
#11
12.01.2008 20:28:16
Вам понравилась сводная таблица?  
При создании своей сразу может не получиться нужный вид. Не отчаивайтесь - когда в МАКЕТЕ в области СТОЛБЦЫ стоит в моем случае год - возмите мышкой ДАННЫЕ и перетащите к нему. Данные примут нужный вид.  
Писать долго. Можете скинуть на мыло телефон kin67@inbox.ru - перезвоню подскажу.
 
 
 
#12
06.02.2008 21:33:34
{quote}{login=ГлавБух}{date=09.01.2008 07:54}{thema=Как сделать материальный отчет?}{post}Всем доброго времени суток!  
 
Итоги по всем уровням и остаток на конец периода прекрасно считаются сводной таблицей, но как выделить из нее остаток на конец периода и вставить его в рабочую таблицу?  
 
{/post}{/quote}  
 
Подниму темку.  
 
Благодаря советам, я прекрасно разобралась со сводной таблицей. Но вопрос об остатках по-прежнему актуален, т.е.: есть рабочая таблица, в которой подробно расписано движение материалов, на основе рабочей таблицы создана сводная таблица, в которой с помощью вычисляемых полей рассчитаны остатки на определенную дату (Остатки 2). Но ведь работа продолжается, материалы поступают и расходуются. Как сделать новый матотчет, как к остатку (который Остаток 2), который есть в сводной таблице прибавить новые данные?
 
 
 
#13
06.02.2008 21:33:34
{quote}{login=ГлавБух}{date=09.01.2008 07:54}{thema=Как сделать материальный отчет?}{post}Всем доброго времени суток!  
 
Итоги по всем уровням и остаток на конец периода прекрасно считаются сводной таблицей, но как выделить из нее остаток на конец периода и вставить его в рабочую таблицу?  
 
{/post}{/quote}  
 
Подниму темку.  
 
Благодаря советам, я прекрасно разобралась со сводной таблицей. Но вопрос об остатках по-прежнему актуален, т.е.: есть рабочая таблица, в которой подробно расписано движение материалов, на основе рабочей таблицы создана сводная таблица, в которой с помощью вычисляемых полей рассчитаны остатки на определенную дату (Остатки 2). Но ведь работа продолжается, материалы поступают и расходуются. Как сделать новый матотчет, как к остатку (который Остаток 2), который есть в сводной таблице прибавить новые данные?
 
 
 
#14
06.02.2008 21:57:27
Да, забыла написать: из остатка 2 нужно удалить товары с нулевым остатком. В обычной таблице можно применить фильтр, а в сводной?
 
 
 
#15
07.02.2008 08:09:19
{quote}{login=ГлавБух}{date=06.02.2008 09:57}{thema=}{post}Да, забыла написать: из остатка 2 нужно удалить товары с нулевым остатком. В обычной таблице можно применить фильтр, а в сводной?{/post}{/quote}Фильтр можно вот так вставить.  
Я так понимаю, что период у Вас заканчивается и Остаток2 становится Остатком1, т.е. остатком на начало периода?
 
 
 
#16
07.02.2008 17:34:45
{quote}{login=}{date=07.02.2008 08:09}{thema=Re: }{post}{quote}{login=ГлавБух}{date=06.02.2008 09:57}{thema=}{post}Да, забыла написать: из остатка 2 нужно удалить товары с нулевым остатком. В обычной таблице можно применить фильтр, а в сводной?{/post}{/quote}Фильтр можно вот так вставить.  
Я так понимаю, что период у Вас заканчивается и Остаток2 становится Остатком1, т.е. остатком на начало периода?{/post}{/quote}  
Да, именно так, и к этому остатку нужно прибавлять новый приход и вычитать новый расход.
 
 
 
#17
07.02.2008 17:34:45
{quote}{login=}{date=07.02.2008 08:09}{thema=Re: }{post}{quote}{login=ГлавБух}{date=06.02.2008 09:57}{thema=}{post}Да, забыла написать: из остатка 2 нужно удалить товары с нулевым остатком. В обычной таблице можно применить фильтр, а в сводной?{/post}{/quote}Фильтр можно вот так вставить.  
Я так понимаю, что период у Вас заканчивается и Остаток2 становится Остатком1, т.е. остатком на начало периода?{/post}{/quote}  
Да, именно так, и к этому остатку нужно прибавлять новый приход и вычитать новый расход.
 
 
 
#18
07.02.2008 18:03:46
{quote}{login=}{date=07.02.2008 08:09}{thema=Re: }{post}{quote}{login=ГлавБух}{date=06.02.2008 09:57}{thema=}{post}Да, забыла написать: из остатка 2 нужно удалить товары с нулевым остатком. В обычной таблице можно применить фильтр, а в сводной?{/post}{/quote}Фильтр можно вот так вставить.  
Я так понимаю, что период у Вас заканчивается и Остаток2 становится Остатком1, т.е. остатком на начало периода?{/post}{/quote}  
Сводная таблица фильтруется, но нулевые остатки не удаляются - появляется сообщение, что "Нельзя изменять часть сводной таблицы, добавлять или удалять строки".
 
 
 
#19
07.02.2008 18:40:15
Имхо, их не надо удалять. Это же остаток на конец периода. Потом захотите посмотреть что осталось на конец некоего периода, а не получится - удалено.  
Я бы отфильтровал не нулевые строки и скопировал бы их в другой файл с такой же структурой и уже обозвал бы их остатком на начало периода. Только сделал бы копию сводной, но без итоговвых строк
 
 
 
#20
07.02.2008 18:50:48
Лузер, мдя, однако светлая мысль искать фильтром не нулевые строки, а как раз ненулевые, в мою голову даже не приходила. Обязательно попробую.
 
 
 
#21
07.02.2008 19:47:34
Прошу прощения что вмешиваюсь, но я создал бы еще один резервный файлик в котором собирал данные по следующему формату:  
остаток на начало года, а потом только приход, расход и месяц+год отчетного периода.  
Потом когда нужно будет создавать квартальный, полугодовой и годовой отчет о движении мат. средств может пригодиться.  
А перенос данных из сводной таблицы -> копировать и  специальная вставка значения.
 
 
 
#22
07.02.2008 19:47:34
Прошу прощения что вмешиваюсь, но я создал бы еще один резервный файлик в котором собирал данные по следующему формату:  
остаток на начало года, а потом только приход, расход и месяц+год отчетного периода.  
Потом когда нужно будет создавать квартальный, полугодовой и годовой отчет о движении мат. средств может пригодиться.  
А перенос данных из сводной таблицы -> копировать и  специальная вставка значения.
 
 
 
#23
07.02.2008 22:06:03
Igor67, как грится, по пунктам.  
-------------  
1. А перенос данных из сводной таблицы -> копировать и специальная вставка значения.  
-------------  
Это все отлично, только вот теряется, эээ..., структура, т.е. нужно заново "прописывать" к какой группе и подгруппе относится данный материал. Эта мысль была и у меня, но это все выглядело как-то грубовато, что ли. Неизящно, так скажем.  
 
------------------  
2. Прошу прощения что вмешиваюсь, но я создал бы еще один резервный файлик в котором собирал данные по следующему формату:  
остаток на начало года, а потом только приход, расход и месяц+год отчетного периода.  
------------------  
Пока я предполагаю сделать следующее.  
У меня есть рабочая таблица, в которой есть Остаток 1, есть Приход и Расход. Я добавлю в эту таблицу еще столбцы Прихода и Расхода для следующих кварталов. Соответственно, у меня есть список наименований, а новый приход и расход я буду вписывать в столбцы соответствующих кварталов.  
 
Далее я буду создавать сводную таблицу "на основе данных, содержащихся в другой сводной таблице", ну то есть в имеющейся.  
 
Ну и далее, с помощью вычисляемого поля я буду считать Остаток 2. Далее к Остатку 2 я буду прибавлять имеющийся в рабочей таблице Приход 3 и вычитать Расход 3, соответственно, далее вычисляемым полем будем считать Остаток 3, к которому будем прибавлять Приход 4 и Расход 4 и т.д.  
 
Наверное, замороченно написала. Постараюсь быстро сделать и выложу файлы.
 
 
 
#24
08.02.2008 00:40:45
Ну быстрое заполнение данных в пустых ячейках описана здесь http://www.planetaexcel.ru/tip.php?aid=86.  
Или можно просто через дополнительные столбцы, формулу и опять спец вставку.  
а формула простая: пишем в соседнем столбце В, в ячейке В1/=A1  
в В2 =Если(A2="";B1;A2). Это означает что если в ячейке слева пусто -> берем значение из В1, если нет равно значению в ячейке А. И Ваша структура восстановлена.  
----  
А вот когда Вы будете заполнять данные в строку, вопервых необходимо следить какие данные в какую строку и как это сделать удобней, и это обрабатывается хуже.  
Но возможно все.
 
 
 
#25
08.02.2008 19:30:35
Сегодня я переделала рабочую таблицу своего отчета. Я  добавила туда столбцы прихода и расхода сразу для всех 4-х кварталов (отчет ежеквартальный). Теперь там етсь остаток на 01.01, приход/расход 1 кв., приход/расход 2-го кв. и т.д.  
 
Я сделала сводную таблицу по данным 1-го кв. Вычисляемым полем рассчитала остаток на конец квартала. Далее я скопировала сводную таблицу (полностью, именно как сводную), вставила ее на новый лист, далее я оставила в этоя таблице только остатки нового квартала. Потом из макета я бросила туда приход/расход нового квартала, вычисляемым полем рассчитала новые остатки - и почувствовала себя человеком, все получилось! Я хотела его сегодня выложить, но случайно я скинула на дискету другой файл, а в понедельник выложу вот этот. Короче, одну проблему сняли.  
 
Но вот с фильтрацией и нулями все оказалось чуть сложнее. Проблема в том, что из нового отчета нужно убрать все позиции, по которым и начальный остаток, и приход, и расход равны нулю. Или оставить только те товары, по которым либо начальный остаток, либо приход, либо расход не равны нулю.  
 
Я пробовала фильтровать. Но получается так, что после фильтрации столбца с остатками, если я ставлю фильтр на столбец прихода, то фильтруется не весь массив, а то, что было отфильтровано уже в остатках. Получается, я ищу товары, по которым И остаток, И приход, И расход НЕ равны нулю, а мне нужно не И, а ИЛИ.  
 
Какие будут предложения?
 
 
 
#26
08.02.2008 19:30:35
Сегодня я переделала рабочую таблицу своего отчета. Я  добавила туда столбцы прихода и расхода сразу для всех 4-х кварталов (отчет ежеквартальный). Теперь там етсь остаток на 01.01, приход/расход 1 кв., приход/расход 2-го кв. и т.д.  
 
Я сделала сводную таблицу по данным 1-го кв. Вычисляемым полем рассчитала остаток на конец квартала. Далее я скопировала сводную таблицу (полностью, именно как сводную), вставила ее на новый лист, далее я оставила в этоя таблице только остатки нового квартала. Потом из макета я бросила туда приход/расход нового квартала, вычисляемым полем рассчитала новые остатки - и почувствовала себя человеком, все получилось! Я хотела его сегодня выложить, но случайно я скинула на дискету другой файл, а в понедельник выложу вот этот. Короче, одну проблему сняли.  
 
Но вот с фильтрацией и нулями все оказалось чуть сложнее. Проблема в том, что из нового отчета нужно убрать все позиции, по которым и начальный остаток, и приход, и расход равны нулю. Или оставить только те товары, по которым либо начальный остаток, либо приход, либо расход не равны нулю.  
 
Я пробовала фильтровать. Но получается так, что после фильтрации столбца с остатками, если я ставлю фильтр на столбец прихода, то фильтруется не весь массив, а то, что было отфильтровано уже в остатках. Получается, я ищу товары, по которым И остаток, И приход, И расход НЕ равны нулю, а мне нужно не И, а ИЛИ.  
 
Какие будут предложения?
 
 
 
#27
08.02.2008 20:34:57
А если добавить столбец для фильтрации где просуммировать Остаток+Приход+Расход -> значение =0 можно удалять?
 
 
 
#28
08.02.2008 20:34:57
А если добавить столбец для фильтрации где просуммировать Остаток+Приход+Расход -> значение =0 можно удалять?
 
 
 
#29
08.02.2008 20:53:06
{quote}{login=Igor67}{date=08.02.2008 08:34}{thema=}{post}А если добавить столбец для фильтрации где просуммировать Остаток+Приход+Расход -> значение =0 можно удалять?{/post}{/quote}  
Igor67, не совсем поняла Вашу идею. Мне в итоговом отчете нужны строки, в которых либо по столбцу остаток, либо по столбцу приход, либо по столбцу расход значение больше 0 (не рано 0).
 
 
 
#30
08.02.2008 21:40:52
{quote}{login=}{date=08.02.2008 08:53}{thema=Re: }{post}{quote}{login=Igor67}{date=08.02.2008 08:34}{thema=}{post}А если добавить столбец для фильтрации где просуммировать Остаток+Приход+Расход -> значение =0 можно удалять?{/post}{/quote}  
Igor67, не совсем поняла Вашу идею. Мне в итоговом отчете нужны строки, в которых либо по столбцу остаток, либо по столбцу приход, либо по столбцу расход значение больше 0 (не рано 0).{/post}{/quote}  
Ааа, попробовала, и, кажется, разобралась. Ой, боюсь говорить, кажется, получается (ттт!) Спасибо! В понедельник попробую применить на основном отчете!  
 
З.Ы. Скажу честно, что мне идея применить фильтр, тем более сложить величины в о т  т а к даже не пришла бы в голову.
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх