Материалы, купленные в прошлом году по проектам

Сегодня я получил свое первое письмо с вопросом. С удовольствием на него отвечу.

Уважаемый Эдуард,

Подскажите, пожалуйста, встречались ли в Вашей практике аналогичные ситуации:

Задача:

В общем кол-ве списанного материала за текущий год необходимо  выделить то кол-во материалов, которое было закуплено, но не потреблено  в предыдущем году (это кол-во известно).

Допусти мы знаем, что на конец 2010 года осталось следующее кол-во материалов, из числа закупленных в 2010 году.

Таблица 1

Материал

Кол-во

Средневзвешенная цена

1001

100

10

1002

200

15

1003

150

100

Списание материалов в производство в 2011 году представлено в таблице 2.

Примечание:

1) Важно сохранить привязку к проекту, т.е. подбить total не пойдет

2) Материалов – сотни тысяч,  линеек — соответственно тоже. Формула должна быть надежной, так как вручную это пересчитать невозможно.

Таблица 2

В примере выше (Таблица 2) цифры в столбе «Из них закуплено в 2010 году» подставлены вручную. Надо придумать формулу, чтобы при каждом списании  материалов, кол-во списываемых материалов сравнивалось с имеющимся количеством на начало года (таблица 1) и учитывало предыдущие списания в текущем году.

Заранее благодарю за помощь.

С уважением, Евгения

Евгения,

Я бы реализовал это следующим образом.

(1) Подтянуть количество материалов по состоянию на конец прошлого года (лучше это сделать с проверкой на ошибку, например, с помощью формулы IFERROR). Формула VLOOKUP не будет работать, так как номера материалов представлены в разных форматах. Подробнее о формуле VLOOKUP читайте в этой статье.

(2) Предварительно отсортировав документы по дате (или по номеру документа, если бы он был), рассчитать количество данного материала, потраченного с начала года с помощью формулы SUMIFS. Подробнее о формуле SUMIFS читайте в этой статье.

(3) Рассчитать количество материалов, оставшихся из закупленных в прошлом году.

(4) Рассчитать количество материалов потраченных в этой линейке из оставшихся от закупленных в прошлом году (из третьего пункта).

Расчет смотрите в приложенном файле.

Если будут еще вопросы, пожалуйста, пишите письмо на адрес edward@youcanexcel.ru или оставляйте комментарий внизу страницы.

Полезные материалы и курсы обучения по Microsoft Excel
Копирование материалов сайта разрешено только с согласия владельца с размещением ссылки на источник.