Лабораторная работа по статистике

Для автоматического заполнения наименования товара используем функцию ВПР().

С помощью функции ВПР можно выполнить поиск в первом столбце диапазона ячеек и получить значение из любой ячейки в той же строке диапазона.

Функция имеет следующий прототип:

ВПР(искомое значение; таблица; номер столбца, [интервальный просмотр])

Искомое значение – значение, которое должно быть найдено в первом столбце таблицы или диапазона.

Таблица - диапазон ячеек, содержащий данные.

Номер столбца - номер столбца в аргументе таблица, из которого возвращается совпадающее значение.

 

Интервальный просмотр - логическое значение, определяющее, какое совпадение должна найти функция ВПР — точное (ЛОЖЬ) или приблизительное (ИСТИНА).

Таким образом, для автоматического заполнения наименования в ячейку B2 введите формулу

=ВПР(А2;Обороты!$A$4:$B$54;2;ЛОЖЬ).

Растяните формулу на 50 строк.

Для расчета столбцов Сумма без НДС и НДС  требуется значение ставки НДС. Данное значение может изменяться с годами. Хорошим тоном считается выделять константы, участвующие в расчетах в отдельный лист.

Создайте в книге новый лист и переименуйте его в «Константы». Отформатируйте ячейку B1 в процентный формат и введите значение 18%

Поставьте курсор мыши на ячейку B1, в панели управления перейдите на вкладку «Формулы» и нажмите кнопку «Присвоить имя». В диалоговом окне задайте имя для выделенной ячейки

После данной процедуры в формулах вместо числовой константы 18% можно использовать ее имя НДС.

Снова перейдем на лист «Приход». Значения столбца «Сумма по документу» рассчитываются как произведения соответствующих ячеек столбцов «Количество» и «Стоимость единицы». Таким образом, введите в ячейку E2 формулу =C2*D2 и растяните ее на 50 строк вниз.

Значения столбца «Включает НДС» определяют, будем ли мы начислять на материальные ценности налог. Данный столбец должен заполнятся в ручную значениями ЛОЖЬ или ИСТИНА.

Если значение ячейки столбца «Включает НДС» имеет значение ЛОЖЬ, то значение ячейки столбца «Сумма без НДС» равна значению соответствующей ячейки столбца «Сумма по документу». В случае если значение ячейки столбца «Включает НДС» имеет значение ИСТИНА, нам необходимо с помощью операции математического дисконтирования рассчитать первоначальную сумму (без начисленного на нее НДС).

Введем в ячейку G2 следующую формулу

=ЕСЛИ(F2;E2/(1+НДС);E2) и протянем ее на 50 строчек вниз.

Чтобы рассчитать НДС составляющую нам опять необходимо анализировать значения столбца «Включает НДС». Если значение в ячейке равно ЛОЖЬ, то налог еще не начислен и необходимо это сделать. В противном случае НДС составляющая рассчитывается как разница между «Сумма по документу» и «Сумма без НДС».

Таким образом в ячейку Н2 введите формулу

=ЕСЛИ(F2;E2-G2;E2*НДС) и протяните ее на 50 строчек вниз.

По алгоритму первой задачи определите диапазоны редактируемых ячеек и сделайте защиту листа от изменений.

Заполните лист «Приход» 30 материальными ценностями. При этом необходимо учитывать, что значения столбца «Код» должны соответствовать кодам из листа «Обороты» и могут повторяться. Пример заполнения

Перейдите к листу «Расход». С помощью формул организуйте автоматическое заполнение столбца «Наименование» материальных ценностей по их кодам. Растяните формулу на 50 строк

Определите диапазоны редактируемых ячеек и сделайте защиту листа от изменений.

Заполните лист «Расход» пятнадцатью наименованиями материальных ценностей. Учитывайте, что столбец «Количество» заполняется с учетом значений аналогичного столбца на листе «Приход». Пример заполнения

Перейдите на лист «Обороты». Добавьте к таблице поля «Количество», «Сумма», «Расход», «Приход» как это сделано на рисунке

Сначала подведем итоги по суммам для каждой из групп. Чтобы заполнить ячейку D3 для группы «На начало периода» введите в нее формулу

=СУММ(D4:D54). Протяните формулу на 50 строк вниз.

По аналогии введите формулы в ячейки F3, H3 и J3.

Как говорилось в начале задачи, столбцы «Количество» и «Сумма» для группы «На начало периода» заполняются значениями из прошлого отчетного периода. В нашем случае будем заполнять их руками. Пример заполнения

Для столбца «Количество» группы «Приход» посчитаем количество каждой материальной ценности. Для этого воспользуемся функцией СУММЕСЛИ().

Функция СУММЕСЛИ используется, если необходимо просуммировать значения, соответствующие указанному критерию.

Функция имеет следующий прототип

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

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

Введите в ячейку E5 формулу

=СУММЕСЛИ(Приход!$A$2:$A$52;A5;Приход!$C$2:$C$52) и растяните ее на 50 строк вниз.

По аналогии введите формулу для ячейки G5. Необходимо учитывать, что суммирование ведется по листу «Расходы». Протяните формулу на 50 строк.

Чтобы рассчитать колонку «Количество» в группе «На конец периода» достаточно сложить «Количества» в группах «На начало периода» и «Приход» и вычесть «Количество» из группы «Расход».

Введите в ячейку I5 соответствующую формулу и растяните ее на 50 строк.

Как было оговорено в условии задачи, суммы расходов вычисляются по средневзвешенным стоимостям. То есть значение столбца «Средняя цена» должна складываться из цен на материальную ценность в группах «На начало периода» и «Приход» и усредняться «Количеством» в этих же группах. Целесообразно было бы ввести в ячейку K5 формулу =(D5+F5)/(C5+E5). Однако, для незаполненных ячеек может сложиться ситуация, что знаменатель в формуле равен нулю. Чтобы исключить такие случаи воспользуемся функцией ЕСЛИ() и введем в K5 формулу

=ЕСЛИ(C5+E5>0;(D5+F5)/(C5+E5);0). Растяните формулу на 50 строк вниз.

Зная среднюю цену и количество, рассчитайте в ячейке Н5 цену материальной ценности. Растяните формулу на 50 ячеек вниз.

В завершении, необходимо вычислить значения столбца «Сумма» группы «На конец периода». Данные значения рассчитываются по тому же принципу, что и «Количество» в этой группе. Растяните формулу.

Определите редактируемые диапазоны ячеек и сделайте защиту листа от изменений.

Пример заполненной таблицы

Для контроля ошибок ввода и учета рассмотрим случай когда в графе расходов количество материальных ценностей превышает их настоящее количество. В этом случае в группе «На конец периода» в колонках «Количество» и «Сумма» мы получим отрицательные значения. Чтобы проконтролировать это будем автоматически раскрашивать ячейки с отрицательными значениями.

Выделите диапазон ячеек в котором содержаться значения столбцов «Количество» и «Сумма» группы «На конец периода». На панели управления выберите вкладку «Главное» и нажмите кнопку «Условное форматирование». В открывшемся меню выберите пункт «Правила выделения ячеек», а далее «Меньше».

Заполните диалоговое окно по образцу

Нажмите ОК.

На листе «Расходы» для любой материальной ценности введите количество заведомо большее, чем есть в наличие.

Перейдите на лист «Обороты» и убедитесь, что ячейки с отрицательными значениями изменили цвет. Пример результата