Проектирование функциональной структуры системы учета товарно-материальных ценностей (ТМЦ) (лабораторная работа), страница 20

В столбце F должен автоматически рассчитываться приход соответствующего вида товара в натуральном выражении на основе данных из таблицы ПРИХОД. Поскольку один и тот же вид товара может поступать неоднократно, и ему будет соответствовать несколько записей в таблице учета поступления, следует из таблицы на листе ПРИХОД выбрать все данные по данному виду товара и суммировать их. Аналогичные действия нужно проделать для расчета прихода в стоимостном выражении и подсчета суммы НДС, подлежащей оплате в связи с поступлением товара данного вида.

Это вычисления можно автоматизировать с помощью функции

=СУММЕСЛИ (диапазон просмотра; критерий; диапазон суммирования).

1-й аргумент - диапазон названий видов товара из таблицы учета поступления;

2-й аргумент -  название вида товара, по которому осуществляется поиск в диапазоне названий товаров;

3-й аргумент - диапазон значений количества прихода, среди которых для суммирования будут выбираться значения, соответствующие товару, заданному в качестве второго аргумента.  

Эта же функция будет также использоваться для расчета прихода в стоимостном выражении и для расчета показателей реализации (раздельно как для отечественных, так и зарубежных контрагентов) и др.

Отсюда видно, что при применении этой и других функций часто потребуется указывать разные диапазоны ячеек в качестве аргументов. Использовать для этого адреса ячеек не очень удобно, гораздо эффективнее использовать имена соответствующих диапазонов. Поэтому присвоим имена тем диапазонам, что будут неоднократно использоваться.

Задание – перейдите на лист ПРИХОД и присвойте имена следующим диапазонам:

         - Е3:Е50   - ПРИХОД.ИМЯ_ТОВАРА    

         - G3:G50   - ПРИХОД.КОЛ

         - K3:K50   - ПРИХОД.СУММА_ОТЕЧ

         - L3:L50   - ПРИХОД.НДС_ОТЕЧ

         - M3:M50   - ПРИХОД.ИТОГО_ОТЕЧ

         - N3:N50   - ПРИХОД.СУММА_ЗАРУБ

         - O3:O50   - ПРИХОД.НДС_ЗАРУБ

         - P3:P50   - ПРИХОД.ИТОГО_ЗАРУБ       

          Перейдите на лист ПРОДАЖА и присвойте имена следующим диапазонам:

         - Е3:Е50   - ПРОДАЖА.ИМЯ_ТОВАРА    

         - G3:G50   - ПРОДАЖА.КОЛ

         - K3:K50   - ПРОДАЖА.СУММА_ОТЕЧ

         - L3:L50   - ПРОДАЖА.НДС_ОТЕЧ

         - M3:M50   - ПРОДАЖА.ИТОГО_ОТЕЧ

         - N3:N50   - ПРОДАЖА.СУММА_ЗАРУБ

         - O3:O50   - ПРОДАЖА.НДС_ЗАРУБ

         - P3:P50   - ПРОДАЖА.ИТОГО_ЗАРУБ       

                    Вернитесь на лист ТМЦ и введите расчетные формулы для блока данных ПРИХОД в следующие ячейки:

         - F3:   =СУММЕСЛИ(ПРИХОД.ИМЯ_ТОВАРА; А3; ПРИХОД.КОЛ) 

         - G3:   =СУММЕСЛИ(ПРИХОД.ИМЯ_ТОВАРА;A3;ПРИХОД.СУММА_ОТЕЧ)

         - H3:   =СУММЕСЛИ(ПРИХОД.ИМЯ_ТОВАРА;A3;ПРИХОД.НДС_ОТЕЧ)

         - I3:   =СУММЕСЛИ(ПРИХОД.ИМЯ_ТОВАРА;A3;ПРИХОД.СУММА_ЗАРУБ)

         - J3:   =СУММЕСЛИ(ПРИХОД.ИМЯ_ТОВАРА;A3;ПРИХОД.НДС_ЗАРУБ)

          Теперь формулы получились довольно наглядными и не требуют дополнительных пояснений. Установите для ячеек G3 и H3 числовой формат(2 десятичных знака). Скопируйте эти формулы в нижележащие ячейки соответствующих столбцов.

В графе K рассчитывается средневзвешенная цена данного вида товара. Формула в клетке К3 выглядит так:

=ЕСЛИ(D3+F3>0;ОКРУГЛ((E3+G3+I3)/(D3+F3);2);0)

Формула означает, что если данный вид товара имеется в наличии, то средняя цена формируется как сумма остатка на начало и прихода за период в стоимостном выражении, отнесенная к сумме остатка и прихода в натуральном выражении (полученное значение округляется до 2 знаков после запятой). Если товара нет в наличии, то средняя цена фиксируется как нулевая.