Использование шаблона "Сальдо" с Excel 2016

В Excel 2016 можно анализировать разные типы данных из разнообразных источников, преследуя при этом различные цели. Из этой статьи вы узнаете, как использовать встроенный шаблон Сальдо в Excel 2016, чтобы отслеживать, сколько вы зарабатываете и на что тратите деньги.

Использование шаблона "Сальдо"

Шаблон Сальдо — это простой пример того, как можно анализировать, моделировать и наглядно представлять данные в Excel 2016. Шаблон позволяет ознакомиться с возможностями анализа данных в Excel 2016 и демонстрирует, как встроенные шаблоны упрощают эту задачу.

В этой статье объясняется, как:

  1. Моделировать данные, используя встроенные возможности моделирования Excel.

  2. Использовать некоторые из функций логики операций со временем.

  3. Использовать в своих книгах мощные вычисления под названием Показатели, на которых основан шаблон "Сальдо".

Открытие шаблона "Сальдо"

Чтобы открыть шаблон "Сальдо", выберите Создать > Обзор возможностей бизнес-анализа.

Шаблон "Сальдо"

Если вы не видите шаблон, введите Обзор возможностей бизнес-анализа в поле поиска, а затем нажмите кнопку с лупой (или клавишу ВВОД), чтобы начать поиск. Откроется шаблон "Сальдо"

Вы увидите первую вкладку книги под названием Начало. Нажмите кнопку Начало работы.

Начало работы с шаблоном "Сальдо"

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

Выбор годового среза для шаблона "Сальдо"

Ввод данных о движении денежных средств

Просматривать образец данных, который предлагается с шаблоном, интересно, но еще интереснее использовать собственные данные. Чтобы ввести свои данные или просто поэкспериментировать с шаблоном, чтобы понять, как он работает, щелкните ярлычок листа Образец данных. Обратите внимание на раздел ПОПРОБОВАТЬ справа от таблицы, в котором есть инструкции и кнопка для ввода собственных данных.

Ввод образца данных

Если щелкнуть Добавить новую строку образца, шаблон выделит новую строку в конце таблицы и заполнит четыре столбца следующими данными:

  • Тип транзакции — в этом первом столбце указывается значение "Доход" или "Расход". Так как используются только эти две категории, с помощью графика на листе "Сальдо" можно единообразно сравнивать доходы и расходы. В этом примере нужно выбрать "Доход", так как получена премия.

  • Дата — это дата транзакции. Шаблон автоматически вставляет 01.04.2015 г., но вы можете использовать любую дату. На графике на листе "Сальдо" доходы и расходы группируются по месяцу и году на основе значения в этом столбце.

  • Категория — этот столбец позволяет классифицировать доходы и расходы. Вы можете выбрать одну из множества доступных категорий. Кроме того, вы можете создать новую категорию (например, если предполагаете, что будете часто получать премии). Доходы и расходы можно просматривать по категориям, поэтому единообразный ввод данных в категории позволит без проблем выполнять анализ.

  • Сумма — это сумма дохода или расхода. Вводите в этот столбец положительные числа (не указывайте отрицательные значения для расходов), так как вычисления, связанные с моделью данных, правильно обрабатывают расходы и доходы на основе положительных чисел в этом столбце.

Ввод образца данных в шаблон "Сальдо"

Если после ввода данных вернуться к диаграмме и нажать кнопку "Обновить сейчас" (которая обновляет модель данных), изменения отразятся на диаграмме движения денежных средств. Благодаря дополнительным 1500 руб., которые мы добавили с помощью инструкций ПОПРОБОВАТЬ, результаты за апрель перестали были ниже показателя за прошлый год (38,94%) и превысили его (68,48%).

С появлением новых данных изменяется диаграмма за апрель

Использование модели данных Excel

Вводя дополнительные примеры данных в книгу "Сальдо", вы взаимодействовали с моделью данных Excel. Модель данных — это упорядоченный набор элементов данных, которые связаны друг с другом в структурированным или стандартизированном виде. В модели данных "Сальдо" существует множество элементов данных (записей о движении средств), которые связаны друг с другом четырьмя структурированными (стандартизированными) категориями ("Тип транзакции", "Данные", "Категория", "Сумма").

Модели данных могут быть простыми, как "Сальдо", или сложными (такими как база данных или набор баз данных, определенным способом связанных друг с другом). Модели данных не обязательно должны быть постоянными или создаваться исключительно инженерами баз данных: их можно мгновенно получать в Excel из различных источников с учетом своих потребностей.

Модель данных также можно анализировать, изучая часть данных различными способами, например с помощью диаграмм или других визуализаций. Например, вам может потребоваться проанализировать только расходы за 2015 календарный год. При этом вы сможете рассмотреть (проанализировать) подмножество модели данных, соответствующее вашим условиям, и сделать важные выводы. Как и модели данных, средства их анализа и визуализации могут быть как простыми (например, график затрат на Интернет в 2015 г.), так и сложными.

Включение надстройки анализа данных

В Excel 2016 предлагается мощный компонент для моделирования данных под названием Power Pivot. Чтобы включить Power Pivot и другие надстройки анализа данных, выберите Файл > Параметры. Откроется окно Параметры Excel. Выберите Дополнительно в левой области, затем прокрутите содержимое окна вниз до раздела Данные, который находится в нижней части окна. Внизу раздела "Данные" можно включить надстройки анализа данных: Power Pivot, Power View и Power Map.

Окно параметров Excel

Установите флажок, а затем нажмите кнопку ОК.

При включении надстроек анализа данных в разделе Работа с данными ленты Данные появляется кнопка Управление моделью данных. При нажатии кнопки Управление моделью данных откроется окно моделирования данных Power Pivot.

Команда управления моделью данных на вкладке "Данные" ленты

Использование показателей с логикой операций со временем в Excel 2016

В шаблоне "Сальдо" используется несколько функций логики операций со временем Excel 2016, например сравнение по годам, описанное выше в этой статье. Эти функции реализованы в виде показателей, созданных в модели данных в шаблоне. Чтобы увидеть показатели, нажмите кнопку Управление моделью данных на вкладке Данные. Откроется окно Power Pivot. Первый показатель называется Общее движение денежных средств.

Вычисляемые значения в Power Pivot

В шаблоне "Сальдо" движение денежных средств за текущий год (мы назовем его CYC) сравнивается со значением за предыдущий год (PYC). Для создания вычисления по годам используется следующая формула:

Year-Over-Year = (CYC-PYC)/PYC

В шаблон "Сальдо" включены следующие показатели, учитывающие логику операций со временем:

  • TotalIncome:=CALCULATE(sum([AMOUNT]),'Sample'[TRANSACTION TYPE]="Income")

    TotalIncome обобщает данные в столбце Сумма, но включает только значения со значением Доход для параметра Тип транзакции.

  • TotalExpenses:=CALCULATE(sum([AMOUNT])*-1,'Sample'[TRANSACTION TYPE]="Expenses")

    Показатель TotalExpenses похож на TotalIncome, но для параметра Тип транзакции задано значение Расход. Затем результат умножается -1, чтобы показать, что результаты являются расходами.

  • Total Cashflow:=[TotalIncome]+[TotalExpenses]

    Total Cashflow получается путем сложения показателей [TotalIncome]+[TotalExpenses]. Использование показателей в качестве стандартных блоков для расчета значения обеспечивает автоматически отражение изменений любого из них в Total Cashflow.

    Наличие Total Cashflow также позволило создать сводную таблицу с данными движения денежных средств. Добавление среза Год дает возможность просмотреть движение денежных средств за определенный год (например, текущий). Для создания сравнения по годам нам также потребовались данные о движении денежных средств за предыдущий год. Кроме того, необходимо, чтобы эти данные отображались, когда с помощью среза показывается текущий год. Для решения этой проблемы мы создали показатель PreviousYearCashflow.

  • PreviousYearCashflow:=CALCULATE([Total Cashflow],SAMEPERIODLASTYEAR('Calendar'[Date]))

    Вычислить — это мощная функция, которая позволяет заменять существующий фильтр новым, чтобы решать аналитические проблемы в соответствии с требованиями пользователей.

    Показатель PreviousYearCashflow обобщает полученное значение Total Cashflow. Здесь также предлагается новый контекст измерения времени, позволяющий получить нужные данные. В частности, если в Total Cashflow выбран срез за 2015 г., в PreviousYearCashflow отображаются данные за 2014 г. с помощью функции SAMEPERIODLASTYEAR.

Синтаксис формулы, используемый в показателях, является частью мощного аналитического языка выражений анализа данных (DAX). Дополнительные сведения о DAX можно найти в статьях, ссылки на которые приведены ниже.

См. также

Функция "Скачать и преобразовать" в Excel 2016

Приступая к работе с модулем "Скачать и преобразовать" в Excel 2016

Power Pivot — обзор и обучение

Выражения анализа данных (DAX) в Power Pivot

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×