записей в таблицу и расчетные формулы Формулы Для выполнения следующего задания добавим столбец Льгота (1 – да

Дипломные работы на заказ

записей в таблицу и расчетные формулы.

Формулы:

Для выполнения следующего задания добавим столбец Льгота (1 – да, 2 — нет). Будем использовать значение этого столбца для начисления итоговой суммы к оплате. Если есть льгота, то тариф на одного человека идет как 50% от базового.
Также будем учитывать возможность того, что в квартире стоит счетчик и квартиросъемщик может оплачивать по счетчику. Для этого необходимо определять стоимость одного кубометра воды. Пусть это вычисляется из условия, что стоимость на одного человека вычисляется из нормы, что один человек потребляет в среднем 5 кубометров воды ежемесячно. Тогда в столбце стоимость одного кубометра будем стоимость воды на одного человека делить на 5.
Итого к оплате вычисляется исходя из двух условий:
Если ли у квартиросъемщика счетчик, тогда расчет идет по счетчику, иначе по норме
Если квартиросъемщик имеет льготу, то от суммы, полученной на первом этапе берется 50%.
Оформим описанный алгоритм таблицей:

Формулы:

3. Использование функций МИН( ), МАКС( ), СРЗНАЧ( )
Вычислим максимальную, минимальную и среднюю прибыль (сумму к оплате с квартиры):

Формулы:

В результате получили, что максимальная прибыль с квартиры составляет 1250р., минимальная – 250р., средняя – 492,5р.
Анализируя полученные данные, видим, что больше всего к оплате за холодную воду в квартире без счетчика и максимальным количеством жильцов.
Минимальная сумма к оплате в квартире с одним жильцом и установленным счетчиком.
Чтобы убедиться, что есть смысл в установке счетчика можно провести дополнительный анализ данных и сформировать столбец Экономия по счетчику. Если счетчика нет, то выводится сообщение «Счетчика нет», иначе проверяется экономия с вычетом льготы, если она есть. Т.е. все расчеты по экономии по счетчику проводятся без учета льготы.
Формула:
=ЕСЛИ(G3=1;
ЕСЛИ(ЕСЛИ(F3=0;E3-J3;E3-J3*2)<=0;
«экономии нет»;
ЕСЛИ(F3=0;E3-J3;E3-J3*2));
«счетчика нет»)

4. Проведем анализ данных:
а) Проведем настраиваемую сортировку по двум параметрам – наличию счетчика и фамилии. Таким образом отсортированная таблица позволяет вносить в таблицу данные работникам бухгалтерии по порядку, сначала по счетчику (заполняется столбец с количеством кубометров, для остальных жильцов этот столбец не заполняется).
Выполним сортировку:

Результат:

б) Работа с автофильтром.
Включим автофильтр, для этого выделим строку с заголовками таблицы и нажмем кнопку Фильтр:

Произведем фильтрацию по свойству Льгота. Этот фильтр нам покажет кто из квартиросъемщиков имеет льготу:

Результат:

Снимем фильтр.

с) Использование расширенного фильтра:
Для работы с расширенным фильтром необходима вспомогательная таблица с критериями фильтра:
Сформируем отчет по Сумме оплаты квартиросъемщиками, у которых проживает больше 4 человек и отсутствует счетчик и льгота. Этот отчет может понадобиться в случае рассмотрения вопроса о возможности скидок на оплату или целесообразности установки счетчика. Это позволит оценить расходы этих квартиросъемщиков и их количество.

Чтобы выполнить расширенный фильтр на вкладке Данные нажмем Дополнительно и выберем критерии для расширенного фильтра:

Результат формируем в другом месте, т.к. мы хотим использовать этот отчет для сохранения и печати как отдельного документа.
Результат:

Видим, что таких квартиросъемщиков двое и возможно, имеет смысл мотивировать их на приобретение счетчика, чтобы снизить расходы на оплату холодной воды.
Дадим название сформированному отчету:

д) Промежуточные итоги.
Промежуточные итоги удобнее всего формировать при помощи сводных таблиц:

Выберем сформировать сводную таблицу на новом листе, опираясь на данные нашей таблицы:

Создадим сводную таблицу для формирования промежуточных итогов суммы к оплате с учетом фильтров по льготе и счетчику, для этого выберем необходимые столбцы и распределим их между областями:

Задавая различные параметры, мы сможем формировать промежуточные итоги по различным категориям квартиросъемщиков.
Например, сформируем промежуточный итог с суммой к оплате по квартиросъемщикам, которые имеют льготу и счетчик:

Результат:

Таких квартиросъемщиков всего двои и к оплате от них поступает 700р.
Такие промежуточные итоги имеют смысл, т.к. сумма скидки, которая предоставлена квартиросъемщику компенсируется государством.

5. Построим диаграмму.
Для наглядного анализа прибыли, от оплаты холодной воды квартиросъемщиками лучше всего построить сводную диаграмму в которой будет отображаться сумма к оплате по категориям квартиросъемщиков:

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

Сводная диаграмма строится аналогично сводной таблице, а точнее на её основе. Уже на построенной диаграмме можно выбирать критерии фильтров, например, посмотреть только льготников:

Или наоборот только квартиросъемщиков без льгот:
%

Сводная диаграмма позволяет получать итоговые данные по прибыли удобные для анализа общей структуры прибыли.
По диаграмме можно сделать вывод, что большую часть прибыли (46%) организация получает от квартиросъемщиков без льготы и без счетчика.
Самый маленький доход приносят квартиры где есть счетчик и льгота (14%).

Следует отметить, что все выводы, основаны на таблице из 10 жильцов с данными об оплате за 1 месяц, поэтому нельзя им полностью доверять. Чтобы убедиться в истинности этих выводов следует проводить аналогичные сравнения ежемесячно.


+7 (812) 389-23-13

Работаем: Пн-Пт, с 10 до 17

+7 (499) 649-65-17

Работаем: Пн-Пт, с 10 до 17