Функция ОСТАТ в Excel(получение остатка от деления)
Функция ОСТАТ в Excel
(получение остатка от деления)
Функция ОСТАТ в Excel предназначена для получения остатка от деления одного числа на другое. Относится к группе математических формул программы, использовать которую очень просто (смотрите описание функции ОСТАТ, а также примеры и видео-урок).
Получение остатка от деления мы все помним ещё со школы. Ну ладно, по правде сказать многие это уже забыли. В любом случае сейчас придётся это вспомнить, если Вы хотите понимать, как работает функция ОСТАТ в Excel.
Название функции ОСТАТ происходит в Excel от «остаток», так что запомнить легко.
В принципе, сложного ничего нет, но всё же сначала рассмотрим синтаксис функции и особенности её работы, если таковые найдутся.
Постановка задачи
Исходные данные
Для начала, давайте определимся, какие у нас есть исходные данные и что нам нужно получить на выходе. Фактически, все что у нас есть, это некоторые исторические данные. Если мы говорим о прогнозировании продаж, то историческими данными будут продажи за предыдущие периоды.
Примечание. Собранные в разные моменты времени значения одной и той же величины образуют временной ряд. Каждое значение такого временного ряда называется измерением. Например: данные о продажах за последние 5 лет по месяцам — временной ряд; продажи за январь прошлого года — измерение.
Составляющие прогноза
Следующий шаг: давайте определимся, что нам нужно учесть при построении прогноза. Когда мы исследуем наши данные, нам необходимо учесть следующие факторы:
- Изменение нашей пронозируемой величины (например, продаж) подчиняется некоторому закону. Другими словами, в временном ряде можно проследить некую тенденцию. В математике такая тенденция называется трендом.
- Изменение значений в временном ряде может зависить от промежутка времени. Другими словами, при построении модели необходимо будет учесть коэффициент сезонности. Например, продажи арбузов в январе и августе не могут быть одинаковыми, т.к. это сезонный продукт и летом продажи значительно выше.
- Изменение значений в временном ряде периодически повторяется, т.е. наблюдается некоторая цикличность.
Эти три пункта в совокупность образуют регулярную составляющую временного ряда.
Примечание. Не обязательно все три элемента регулярной составляющей должны присутствовать в временном ряде.
Однако, помимо регулярной составляющей, в временном ряде присутствует еще некоторое случайное отклонение. Интуитивно это понятно — продажи могут зависеть от многих факторов, некоторые из которых могут быть случайными.
Вывод. Чтобы комплексно описать временной ряд, необходимо учесть 2 главных компонента: регулярную составляющую (тренд + сезонность + цикличность) и случайную составляющую.
Виды моделей
Следующий вопрос, на который нужно ответить при построении прогноза: “А какие модели временного ряда бывают?”
Обычно выделяют два основных вида:
- Аддитивная модель: Уровень временного ряда = Тренд + Сезонность + Случайные отклонения
- Мультипликативная модель: Уровень временного ряда = Тренд X Сезонность X Случайные отклонения
Иногда также выделают смешанную модель в отдельную группу:
- Смешанная модель: Уровень временного ряда = Тренд X Сезонность + Случайные отклонения
С моделями мы определились, но теперь возникает еще один вопрос: «А когда какую модель лучше использовать?»
Классический вариант такой:
— Аддитивная модель используется, если амплитуда колебаний более-менее постоянная;
— Мультипликативная – если амплитуда колебаний зависит от значения сезонной компоненты.
Следуя нашему алгоритму, мы должны сгладить временной ряд. Воспользуемся методом скользящей средней. Видим, что в каждом году есть большие пики (май-июнь 2016 и апрель 2017), поэтому возьмем период сглаживания пошире, например, месячную динамику, т.е. 12 месяцев.
Удобнее брать период сглаживания в виде нечетного числа, тогда формула для расчета уровней сглаженного ряда:
yi — фактическое значение i-го уровня ряда,
yt — значение скользящей средней в момент времени t,
2p+1 — длина интервала сглаживания.
Но так как мы решили использовать месячную динамику в виде четного числа 12, то данная формула нам не подойдет и мы воспользуемся этой:
Иными словами, мы учитываем половины от крайних уровней ряда в диапазоне, в остальном формула не претерпела больше никаких изменений. Вот ее точный вид для нашей задачи:
Сглаживаем наши уровни ряда и растягиваем формулу вниз:
Сразу можем построить график из известных значений уровня продаж и их сглаженной. Выведем ее уравнение и значение коэффициента детерминации R^2:
В качестве сглаженной я выбрала полином третьей степени, так как он лучше всего описывал уровни временного ряда и имел наибольший R^2.
Как в Excel построить поле корреляции
Корреляцию в Excel можно найти по формуле:
Результат показан ниже
Также можно построить график поля корреляции
Для этого, переходим на вкладку Вставка в области диаграммы выбираем точечный график
затем переходим на область графика
и выбираем данные из диапазона B3:C11, затем Ок. В итоги получаем график поля корреляции по точкам
Также быстро корреляцию можно найти через анализ данных
Вкладка Данные, затем Анализ данных. Если у вас эта вкладка не отображается в Excel, то см. здесь как сделать надстройку.
Как в Эксель посчитать проценты: обратная задача
Теперь хочу дать вам последовательность действий, если нужно получить конкретное число, которое является частью суммы. Причем известно процент, который это значение занимает в общем показателе.
- Расчет будет вестись по следующей формуле величина_процента%*общая_сумма.
Например, если нам дано 7% от 70, то подставив эти значения в формулу, получим числовое значение. Таким образом, ячейке, в которой появится итоговый результат не нужно присваивать формат «Процентный». В этой строке должно быть написано «Общий» или «Числовой».
- Как и в предыдущих случаях останется нажать на кнопку Enter.
Чтобы стать профессионалом в Excel обратите внимание на курс «Power BI & Excel PRO». Профессиональные наставники научат вас секретным техникам работы с таблицами. При этом вы намного упростите себе жизнь.
Работу с формулой, описанной выше можно применять и для таблиц.
К примеру, применить ее можно в ситуации, когда нужно получить величину НДС, составляющую 18%.
- Для этого в пустой ячейке выполняем действия, как и в предыдущем примере, только в качестве первого числа ставим 18%, а для второго делаем ссылку на ячейку с числом.
- После чего останется нажать на кнопку Enter. Причем не нужно менять формат ячейки на процентный.
Планирование доходов и расходов. Виды потребностей.
Прежде чем мы перейдем к изучению таблицы учета доходов и расходов в excel стоит затронуть тему планирования как семейного, так и личного бюджета. Ни для кого не секрет, что наши потребности делятся на базовые первостепенные и второстепенные, навязанные нам реалиями современного маркетинга. Таким образом, главным ключом к эффективному планированию доходов и расходов бюджета является удовлетворение основных жизненных потребностей и минимизация расходов на необязательные нужды.
Какие же потребности можно отнести к необходимым базовым?
- Физиологические потребности. Не будем перечислять их все, только те, которые относятся к нашим расходам. А это потребность в пищи, воде, одежде и жилье (сюда отнесем оплату самого жилья и коммунальных услуг). Данные нужды непосредственно влияют на наше физическое и эмоциональное состояние, поэтому их нельзя игнорировать и необходимо удовлетворять в первую очередь.
- Потребность в безопасности. В первую очередь сюда можно отнести защищенность собственного здоровья и здоровья членов наших семей. Человек должен быть уверен, что ему ничего не угрожает и, в случае возникновения непредвиденных обстоятельств (например, болезни), он и его семья смогут получить квалифицированную помощь. Сюда же относится и покупка лекарств, что особенно актуально в наше непростое время.
- Потребность в комфорте. Наше психоэмоциональное состояние напрямую зависит от условий комфорта нашей жизни. Но здесь нужно видеть тонкую грань между необходимыми потребностями, такими как: удобная кровать для сна или обувь для работы, и навязанными: последняя модель телефона, компьютерные игры и т.д..
График остатков — это график, на котором остатки находятся на вертикальной оси, а независимая переменная — на горизонтальной оси. Если точки случайно распределены вокруг горизонтальной оси, то для данных подходит модель линейной регрессии; в противном случае выберите нелинейную модель.
Следующий пример показывает несколько образцов на остаточных участках.
Шаг 1: Вычислить невязки для каждой точки данных.
х | 60 | 70 | 80 | 85 | 95 |
---|---|---|---|---|---|
y (фактическая стоимость) | 70 | 65 | 70 | 95 | 85 |
h a t y (прогнозируемая стоимость) | 65,411 | 71,849 | 78,288 | 81,507 | 87,945 |
e (Остаток) | 4,589 | -6,849 | -8,288 | 13,493 | -2,945 |
Шаг 2: — Нарисуйте график остаточного графика.
Шаг 3: — Проверьте случайность остатков.
Здесь график невязки имеет случайный характер: первый остаток является положительным, следующие два — отрицательным, четвертый — положительным, а последний — отрицательным. Поскольку картина довольно случайна, это указывает на то, что модель линейной регрессии подходит для вышеуказанных данных.