Составление бдр и бддс пример в excel: БДР и БДДС — что это такое и в чем отличия, порядок составления, объяснение простыми словами для чайников — «Мое Дело»

Содержание

что это, расшифровка, как составлять, откуда берутся данные, примеры в Excel

При ведении бизнеса необходимо правильно рассчитывать бюджет. С целью профессионального управления финансами компании используют БДР и БДДС. Они помогают вести учет прибыли и убытков, управлять движением денег, предоставляют сведения о работе предприятия.

При ведении бизнеса важно правильно управлять финансами.

Содержание

  1. Что такое БДР и БДДС – расшифровка
  2. Для чего нужно рассчитывать
  3. В чем отличия
  4. Составление бюджета доходов и расходов
  5. Откуда брать данные
  6. Расчет доходов
  7. Расчет расходов
  8. Вычисление чистой прибыли
  9. Планирование будущей прибыли
  10. Составление отчета
  11. Расчет бюджета движения денежных средств
  12. Определение остатков
  13. Вычисление доходной части
  14. Составление расходов
  15. Расчет прибыли
  16. Корректировка данных и утверждение
  17. К кому обратиться за помощью в составлении БДР и БДДС
  18. Какие программы использовать для вычислений
  19. Как автоматизировать процесс
  20. Примеры составления БДР и БДДС
  21. В 1С: ERP 2
  22. В “1С: Управление холдингом”
  23. В “WA: Финансист”
  24. Советы при бюджетировании

Что такое БДР и БДДС – расшифровка

Расшифровка БДР звучит как “бюджет доходов и расходов”. Благодаря ему можно оценить уровень развития предприятия, спрогнозировать увеличение или уменьшение дохода.

БДДС – это бюджет движения денежных средств. Через него фиксируются все финансовые потоки компании. Основная задача такой системы – обеспечить оплату первостепенных нужд фирмы в установленные сроки.

Для чего нужно рассчитывать

Главе предприятий необходимо рассчитывать бюджет для того, чтобы:

  • узнать рентабельность бизнеса;
  • иметь финансовые данные предприятия;
  • спрогнозировать будущие доходы и расходы;
  • оценить вложения для получения прибыли и т.д.

Глава предприятия должен знать рентабельность бизнеса.

Для таких целей используют бюджетирование – планирование финансов и нематериальных потоков на основе данных БДДС и БДР.

В чем отличия

Системы отличаются своими целями. БДР составляют для того, чтобы проанализировать эффективность работы компании и составить бизнес-прогноз управления финансами и затратами.

Отличие БДДС в том, что он необходим для учета следующих данных:

  • оценивания потребности в заемных средствах;
  • устранения кассовых разрывов;
  • выполнения налоговых обязательств;
  • оптимизации закупок.

Финансовые планы составляют разными способами. БДР складывается в момент трат и прибыли, а БДДС – после списания или зачисления денег на счет или через кассу.

БДР рассчитывают для того, чтобы составить бизнес-прогноз.

Составление бюджета доходов и расходов

БДР формируется из всех затрат и прибыли за установленное время: 1 год или 3 месяца. Он составляется заранее как план, которого планирует достичь предприятие в будущем.

Рассчитывают бюджет по таким этапам, как:

  1. Понимание бизнеса. Владелец предприятия должен знать суть работы, привлечь специалистов, которые осуществят стратегические планы, увеличат шанс на рентабельность.
  2. Ведение документооборота. Важно правильно документировать процесс расчета годового бюджета, чтобы избежать финансовых простоев.
  3. Оценка расходов и доходов. После составления БДР необходимо следить за ним по практическим результатам. Они предоставляют информацию о работе бизнеса и о том, достигнет ли он поставленных целей.
  4. Подсчет себестоимость продаж. Вычисляется путем вычета из общего дохода и стоимости.
  5. Вычисление валовой прибыли. Получается после вычета себестоимости.
  6. Извлечение чистой прибыли. Из валовой прибыли вычитают все затраты, связанные с производством.
  7. Анализ денежного потока. Это возможность выплатить деньги раньше, чем что-либо будет сделано взамен.

Бюджет состоит из всех затрат и прибыли.

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

Откуда брать данные

Для формирования БДР необходимы данные, которые помогут правильно рассчитать бюджетный план:

  1. Прогноз продаж. Составляется на основе статистических данных за предыдущие периоды. На будущий период делают прогноз с учетом ситуации на рынке, уровня цен у предприятия и конкурентов, сезонности и т.д.
  2. Бюджет производства. Должен быть направлен на то, чтобы объем производимой продукции перекрывал объем готовой и формировал запасы.
  3. Анализ запасов. Это сведения о объемах товаров, которые находятся на складах.
  4. Коммерческие расходы. Обуславливаются реализацией готовой продукции и носят переменный характер.
  5. Административные затраты. Направлены на содержание административного аппарата компании.
  6. Снабжение. Формируется из данных о запасах, продажах и производстве.
  7. Трата основного материала. Фиксируется исходя из информации о сырье и комплектующих.
  8. Заработная плата. Сотрудники, занимающиеся производством, получают оплату.
  9. Косвенные расходы на производство. Их предприятие несет для обеспечения должной работы.
  10. Себестоимость. Определяет фактическую стоимость единицы товара.

Для формирования БДР нужен прогноз продаж.

Из сведений вышеперечисленных бюджетов формируется БДР. Статистические данные получают из отчетных документов подразделений, занимающихся производством товаров.

Расчет доходов

На этом этапе бюджетирования учитывают все поступления в активы предприятия.

К ним относят:

  • выручку от продаж;
  • прибыль от услуг или аренды;
  • прочие доходы, получаемые не от реализации основного продукта.

Могут учитываться и другие источники прибыли, все зависит от специфики предприятия.

Также при формировании доходной части считаются с результатами прошлогоднего БДР.

Расчет расходов

В затратную часть предприятия входят следующие сведения:

  • затраты на производство;
  • коммерческие и административные траты;
  • налоговые отчисления;
  • заработная плата и др.

Затраты на производство, налоговые отчисления входят в список расходов.

Детали расходной части формируются из целей и возможностей предприятия.

Вычисление чистой прибыли

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

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

Планирование будущей прибыли

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

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

Также решаются главные задачи предприятия:

  • стимулирование работников и выплата зарплаты;
  • накопление денег на улучшение качества производства и его расширение;
  • обязательные выплаты инвесторам, собственникам и т.д.;
  • повышение конкурентоспособности.

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

Спланировать убыток невозможно, т.к. конечная отметка плановой прибыли – 0.

Составление отчета

После анализа всех бюджетов полученные сведения заносят в принятую в компании форму отчета бюджетных расходов и доходов. Ее схема зависит от рода деятельности и размера компании.

Для получения грамотного отчета можно обратиться к профессионалам или компании-аутсорсеру.

Расчет бюджета движения денежных средств

В БДДС учитывают плановые платежи и рассчитывают чистый денежный поток. Он показывает профицит или дефицит денежных средств. Основная цель БДДС – руководство платежеспособностью фирмы.

На ее основе формируются следующие задачи, которые решает бюджетный план:

  • аргументирование привлечения и расчета суммы дополнительных финансов;
  • обоснование бессмысленности текущих расчетов и их изменения;
  • формирование основы для анализа финансовых потоков и причин несостыковок реальности и прогноза.

В основе БДДС лежат данные о будущих расходах и доходах. Их берут из БДР и сведений о платежах.

Определение остатков

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

Определение остатков является важным этапом составления бюджета.

Но бюджетные планы составляют заранее, и узнать фактический остаток нельзя. Поэтому можно взять значение из БДДС прошлого года, если оно соответствует действительности.

Вычисление доходной части

При вычислении этой части изучают значения о выручке и прочей прибыли из БДР и переводят их в денежные потоки через графики или коэффициенты инкассации.

Разбивают прибыль на 3 части:

  • текущую;
  • инвестиционную;
  • финансовую.

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

Составление расходов

Расходы разделяют на 3 части, как и доходы. Определяют сумму капитальных затрат на приобретение активов, убирают амортизацию и разносят плановые выплаты по периодам. После таких расчетов получают сведения о расходах предприятия.

Расчет прибыли

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

Расчет прибыли происходит на основе вычета между оттоками и притоками.

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

Корректировка данных и утверждение

Этот этап позволяет выявить изменения во всех аспектах составления БДДС, кроме расчета убытков и чистого денежного потока.

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

К кому обратиться за помощью в составлении БДР и БДДС

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

Какие программы использовать для вычислений

Простой вариант составления бюджета и его планирования можно сделать в Excel, но эта программа подходит для небольших компаний. При расширении производства “Эксель” не будет справляться с увеличившимся объемом информации.

Для составления бюджета пользуются программами “1С”.

Наиболее качественными программами для вычисления БДР и БДДС являются:

  1. “1С: Управление холдингом”.
  2. “1С: Управление нашей фирмой”.
  3. “WA: Финансист”.
  4. “1С: ERP 2”.

Они способны комплексно наладить автоматическое составление бюджетов.

Как автоматизировать процесс

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

  1. Отсутствие гибкой бюджетной системы. Автоматизация снижает издержки на корректировку бюджета при наличии внешних и внутренних преобразований в компании.
  2. Непрозрачность контроля бюджетов и их составления. Автоматическая система обеспечивает сквозной контроль над бюджетными процессами в компании.
  3. Высокую сложность бюджетирования. Специальные программы автоматизации просты в использовании и доступны специалистам.

Поэтому для эффективности и контроля составления бюджетов применяют автоматизированные программы.

Автоматизация процессов решает различные проблемы.

Примеры составления БДР и БДДС

Самыми распространенными и дешевыми вариантами составления бюджетного плана являются:

  1. работа в Excel;
  2. создание бюджета в программах на платформе 1C.

Работа в “Эксель” трудоемкая, занимает много времени и не подходит для больших предприятий. Программы 1С автоматизированы и упрощают процесс бюджетирования.

В 1С: ERP 2

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

Модель бюджета состоит из комплекса форм и отчетов для анализа и ввода данных и выявляет необходимые для этого показатели.

К ним относят:

  • статьи бюджетов, по которым рассчитываются обороты;
  • фактические данные;
  • нефинансовые сведения и нормативы;
  • другие вычисления.

“1С: ERP” применяет вариативные схемы с планами продаж.

Эта информация может вводиться вручную или рассчитываться на основе данных в программе. В системе моделируются финансовые состояния компании и привлечение дополнительного дохода.

В 1C: ERP 2 есть специальный монитор бюджетного процесса, который показывает отклонения от сроков и поставленных задач.

В “1С: Управление холдингом”

Это набор инструментов для бюджетирования, управления, автоматизации и бизнес-анализа предприятия.

Программа способна:

  • снизить налоговые риски и обеспечить планирование с помощью руководства корпоративными налогами;
  • вести бухгалтерский учет;
  • повысить контроль над инвестиционными проектами;
  • снизить сроки и повысить качество отчетов МСФО и т.д.

“1С: Управление холдингом” – набор инструментов для бизнес-анализа.

Также программа минимизирует финансовые риски и управляет оборотным капиталом, повышает точность расчета бюджетирования.

В “WA: Финансист”

Это одна из программ на платформе 1С, которая обладает следующими преимуществами:

  • составляет стандартные и произвольные анализы бюджетов;
  • поддерживает мультивалютность;
  • загружает уже готовые бюджеты из “Эксель”;
  • утверждает и согласует данные;
  • автоматически заполняет сведения;
  • планирует начисление платежей согласно договорам и т.д.

С помощью настраиваемого документа “Бюджет” вводятся плановые показатели, а благодаря инструменту корректировки вносятся изменения в отчет.

Советы при бюджетировании

Чтобы понимать алгоритм составления бюджета, необходимо знать все о финансово-хозяйственной деятельности предприятия и владеть экономическими знаниями.

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

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

Формирование планового бюджета в 1С:ERP (1С:КА)

#Бюджетирование БДР и БДДС
#ERP

  • Сальникова Ольга
    Заместитель директора по продуктам финансово-экономического учета


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


Очень часто на старте проекта автоматизации Заказчик высказывает пожелание, чтобы все расчеты, которые сотрудники финансово — экономических службы предприятия выполняли в таблицах Excel, были автоматизированы в информационной системе. Несмотря на большой потенциал и гибкость типовых возможностей подсистемы Бюджетирования программных продуктов 1C: ERP и 1C: КА это не всегда бывает целесообразно. Особенно если речь идет о расчете данных по показателю или статье бюджета, значение которых зависит от значения других показателей и статей в количественном или суммовом выражении.


Давайте рассмотрим это на примере ситуации, с которой мы столкнулись на реальном проекте комплексной автоматизации деятельности производственного предприятия металлургической отрасли.


При планировании Бюджета доходов и расходов нам необходимо было получить в информационной системе Бюджет по Фонду оплаты труда по плановым данным. Самый простой вариант решения данной задачи — занести плановые данные вручную в документ планирования. Но он был и самым трудоемким для пользователей и не отвечал ожиданиям Заказчика. Поэтому мы стали искать другие пути решения данной задачи.


При этом необходимо было учесть следующие особенности планирования ФОТ на предприятии до начала процесса автоматизации:


  • планирование ФОТ производилось в таблицах Excel;


  • размер планового ФОТ зависел от различных факторов: объема выпускаемой продукции, загрузки оборудования, сменности работы производственного персонала и т. п. Все они были заложены в формулы, по которым рассчитывался плановый ФОТ. При этом до момента утверждения планового ФОТ он мог пересчитываться несколько раз;


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


Учитывая все это совместно с Заказчиком было принято решение о нецелесообразности переноса расчета планового ФОТ в информационную систему и использовании для формирования плановых данных по ФОТ типовой механизм загрузки данных их Excel.


Для реализации данного решения первое, что было сделано совместно с Заказчиком —это определен список статей бюджетов, по которым в бюджете БДР будут отражаться плановые и фактические расходы на оплату труда:



Рис 1. Список статей бюджета для отражения плановых и фактических данных по расходам на оплату труда


Аналитика данных статей бюджета — Подразделения предприятия, так как при планировании и анализе исполнения Бюджета по Фонду оплаты труда Заказчику необходима была информация не только по общей сумме расходов в целом по статье бюджета, но и информация о ФОТ в разрезе подразделений.


Затем также совместно с Заказчиком было выработано решение о соответствии между утвержденными статьями бюджетов и категориями персонала, в разрезе которых производиться расчет планового ФОТ в таблицах Excel. Например, расчет ФОТ в таблице Excel производится по каждому цеху в разрезе основных и вспомогательных рабочих:



Рис. 2. Пример расчета планового ФОТ в таблице Excel


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


Следующий шаг — это настройка в информационной системе вида бюджета Бюджет по Фонду оплаты труда для заведения плановых данных в утвержденной Заказчиком структуре:



Рис. 3. Бюджет по Фонду оплаты труда, настроенный в информационной системе


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


— структура бюджета должна быть максимально простая, количество аналитик по статье бюджета не должно превышать одного значения;


— аналитика, по которой необходима детализация по статье бюджета, должна быть указана в структуре бюджета при его настройке.


Затем настроенный вид бюджета был выгружен в файл формата Excel. Зачем это надо делать? Дело в том, что загрузить данные в документ Экземпляр бюджета из внешнего файла Excel можно только в той же структуре, в которой в информационной системе у нас хранится вид бюджета. Если мы попытаемся загрузить данные из любого другого файла Excel, с которым мы работаем, система не даст нам это сделать, так как структура файла и структура вида бюджета не будут совпадать.


Для выгрузки документа Экземпляр бюджета в файл формата Excel сначала необходимо создать документ Экземпляр бюджета по настроенному виду бюджета, заполнить все необходимые поля на закладке Основное и сохранить документ без заполнения данных на закладке Бюджет. После этого по команде Excel / Выгрузить в Excel формируем бланк для формирования данных в формате таблицы Excel.



Рис. 4. Заполнение закладки Основное документа Экземпляр бюджета



Рис. 5. Выгрузка структуры бюджета в файл Excel


При этом открывается окно создания файла, в котором по кнопке «Сохранить как» мы можем выбрать место хранения данного файла и сохранить его:



Рис. 6. Окно создания файла бюджета


Выгруженный из информационной системы файл Бюджет по Фонду оплаты труда имеет следующий вид:



Рис. 7. Выгруженный из информационной системы в Excel файл бюджета


Затем специалистами Заказчика была настроена связь между выгруженным файлом бюджета и исходным файлом расчета планового ФОТ типовыми методами Excel. При настройке связи между файлами учитывались выработанные ранее договоренности формирования данных по статьям бюджетов, закрепленные в методологии планирования расходов по ФОТ.


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



Рис. 8. Файл Бюджета по ФОТ с плановыми данными


В результате специалист ПЭО, ответственный за формирование в информационной системе плановых данных по ФОТ, мог формировать их автоматически. Для этого в информационной системе необходимо открыть сохраненный ранее документ Экземпляр бюджета (без заполненных данных) и заполнить его по команде Excel / Загрузить из Excel



Рис. 9. Заполнение Бюджета по ФОТ плановыми данными из Excel


В открывшемся по данной команде окне необходимо выбрать файл для загрузки с помощью команды «Выбрать»:



Рис. 10. Выбор файла для загрузки данных


Проверить данные в открывшемся окне «Сопоставление загружаемых данных из Excel» и по команде «Загрузить в документ и закрыть» заполнить документ экземпляр бюджета:



Рис. 11. Экземпляр бюджета, заполненный плановыми данными из Excel


При этом если в исходном файле расчета планового ФОТ данные изменялись в связи с тем, что в процессе формирования и утверждения БДР было принято решение об изменении планового ФОТ либо параметров, которые влияют на его размер, специалисту ПЭО было достаточно еще раз воспользоваться командой «Загрузить» из Excel и обновить в ИС плановые данные по ФОТ, а не вносить изменения в документ формирования плановых данных по Бюджету ФОТ вручную.


Какой вывод можно сделать? Нет ничего страшного в том, что в процессе автоматизации, особенно на первом ее этапе, некоторые плановые данные для формирования БДР или БДДС будут рассчитываться с помощью таблиц Excel. Типовые возможности программных продуктов 1С: ERP и 1С: КА позволяют загрузить данные из Excel в информационную систему и избавить пользователей от необходимости переносить их вручную.


Надеюсь, данная статья была для Вас полезна. Советую также прочитать статью БДР и БДДС. Настройка одновременного планирования.


______________________________________


Автор статьи: заместитель директора по продуктам финансово-экономического учета Сальникова Ольга Владимировна. Дата обновления статьи 04.09.2019 г.

Подпишитесь на нашу рассылку
и получите еще больше статей от экспертов по 1С!

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

Создание модели данных в Excel

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

Прежде чем вы сможете начать работать с моделью данных, вам нужно получить некоторые данные. Для этого мы будем использовать функцию Get & Transform (Power Query), так что вы можете сделать шаг назад и посмотреть видео или следовать нашему учебному руководству по Get & Transform и Power Pivot.


Где находится «Power Pivot»?

  • Excel 2016 и Excel для Microsoft 365 — Power Pivot включен в ленту.

  • Excel 2013 — Power Pivot является частью выпуска Office профессиональный плюс Excel 2013, но не включен по умолчанию. Узнайте больше о запуске надстройки Power Pivot для Excel 2013.

  • Excel 2010 — Загрузите надстройку Power Pivot, затем установите надстройку Power Pivot.


Где находится «Получить и преобразовать» (Power Query)?

  • Excel 2016 и Excel для Microsoft 365 — Get & Transform (Power Query) интегрированы с Excel на вкладке Data .

  • Excel 2013 — Power Query — это надстройка, которая входит в состав Excel, но ее необходимо активировать. Перейдите к Файл > Параметры > Надстройки , затем в раскрывающемся списке Управление в нижней части панели выберите Надстройки COM > Перейти . Отметьте Microsoft Power Query для Excel , затем OK , чтобы активировать его. На ленту будет добавлена ​​вкладка Power Query .

  • Excel 2010 — Загрузите и установите надстройку Power Query. После активации на ленту будет добавлена ​​вкладка Power Query .

Начало работы

Во-первых, вам нужно получить некоторые данные.

  1. В Excel 2016 и Excel для Microsoft 365 используйте Данные > Получить и преобразовать данные > Получить данные для импорта данных из любого количества внешних источников данных, таких как текстовый файл, книга Excel, веб-сайт, Microsoft Access, SQL Server или другая реляционная база данных, содержащая несколько связанных таблиц.

    В Excel 2013 и 2010 перейдите к Power Query > Получить внешние данные и выбрать источник данных.

  2. Excel предложит вам выбрать таблицу. Если вы хотите получить несколько таблиц из одного и того же источника данных, установите флажок Включить выбор нескольких таблиц . Когда вы выбираете несколько таблиц, Excel автоматически создает для вас модель данных.

    Примечание.  В этих примерах мы используем книгу Excel с вымышленными сведениями об учащихся по классам и оценкам. Вы можете скачать образец рабочей тетради Student Data Model и следовать ей. Вы также можете загрузить версию с заполненной моделью данных..

  3. Выберите одну или несколько таблиц, затем нажмите Загрузить .

    Если вам нужно отредактировать исходные данные, вы можете выбрать опцию Редактировать . Дополнительные сведения см. в разделе Введение в редактор запросов (Power Query).

Теперь у вас есть модель данных, содержащая все импортированные вами таблицы, и они будут отображаться в списке полей сводной таблицы .

Примечания:

  • Модели создаются неявно при одновременном импорте двух или более таблиц в Excel.

  • Модели создаются явным образом при использовании надстройки Power Pivot для импорта данных. В надстройке модель представлена ​​в виде вкладок, аналогично Excel, где каждая вкладка содержит табличные данные. См. статью Получение данных с помощью надстройки Power Pivot, чтобы узнать об основах импорта данных с помощью базы данных SQL Server.

  • Модель может содержать одну таблицу. Чтобы создать модель на основе только одной таблицы, выберите таблицу и нажмите Добавить в модель данных в Power Pivot. Вы можете сделать это, если хотите использовать функции Power Pivot, такие как отфильтрованные наборы данных, вычисляемые столбцы, вычисляемые поля, ключевые показатели эффективности и иерархии.

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

  • org/ListItem»>

    Советы по уменьшению размера модели данных см. в статье Создание модели данных с эффективным использованием памяти с помощью Excel и Power Pivot.

  • Дополнительные сведения см. в разделе Учебник: Импорт данных в Excel и Создание модели данных.

Совет: Как узнать, есть ли в вашей книге модель данных? Перейти к Power Pivot > Управление . Если вы видите данные, подобные рабочему листу, значит, модель существует. Дополнительные сведения см. в статье Узнайте, какие источники данных используются в модели данных рабочей книги.

Создайте отношения между вашими таблицами

Следующим шагом является создание отношений между вашими таблицами, чтобы вы могли получать данные из любой из них. Каждая таблица должна иметь первичный ключ или уникальный идентификатор поля, например идентификатор учащегося или номер класса. Самый простой способ — перетащить эти поля, чтобы соединить их в Power Pivot 9.0008 Представление диаграммы .

  1. Перейти к Power Pivot > Управление .

  2. На вкладке Home выберите Представление схемы .

  3. Будут отображены все ваши импортированные таблицы, и вам может потребоваться некоторое время, чтобы изменить их размер в зависимости от количества полей в каждой из них.

  4. Затем перетащите поле первичного ключа из одной таблицы в другую. Следующий пример представляет собой диаграмму наших таблиц учеников:

    Мы создали следующие ссылки:

    • tbl_Студенты | ID учащегося > tbl_Grades | Студенческий номер

      Другими словами, перетащите поле «Код учащегося» из таблицы «Студенты» в поле «Код учащегося» в таблице «Оценки».

    • tbl_Semesters | Идентификатор семестра > tbl_Grades | Семестр

    • табличные_классы | Номер класса > tbl_Grades | Номер класса

    Примечания:

    • Имена полей не обязательно должны быть одинаковыми для создания отношения, но они должны быть одного типа данных.

    • Разъемы в представлении схемы имеют «1» с одной стороны и «*» с другой. Это означает, что между таблицами существует отношение «один ко многим», и это определяет, как данные используются в сводных таблицах. См. Отношения между таблицами в модели данных, чтобы узнать больше.

    • Соединители указывают только на наличие связи между таблицами. На самом деле они не покажут вам, какие поля связаны друг с другом. Чтобы увидеть ссылки, перейдите на Power Pivot > Управление > Дизайн > Взаимосвязи > Управление взаимосвязями . В Excel вы можете перейти к Данные > Отношения .

Использование модели данных для создания сводной таблицы или сводной диаграммы

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

  1. В Power Pivot перейдите к Управление .

  2. На вкладке Home выберите PivotTable .

  3. Выберите, где вы хотите разместить сводную таблицу: на новом листе или в текущем местоположении.

  4. Нажмите OK , и Excel добавит пустую сводную таблицу с панелью списка полей, отображаемой справа.

Затем создайте сводную таблицу или сводную диаграмму. Если вы уже создали отношения между таблицами, вы можете использовать любое из их полей в сводной таблице. Мы уже создали отношения в образце рабочей книги Student Data Model.

Добавить существующие несвязанные данные в модель данных

Предположим, вы импортировали или скопировали большое количество данных, которые хотите использовать в модели, но не добавили их в модель данных. Вводить новые данные в модель проще, чем вы думаете.

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

  2. Используйте один из следующих подходов для добавления ваших данных:

  3. org/ListItem»>

    Нажмите Power Pivot > Добавить в модель данных .

  4. Щелкните Вставить > Сводная таблица и установите флажок Добавить эти данные в модель данных в диалоговом окне Создать сводную таблицу.

Диапазон или таблица теперь добавляются в модель как связанная таблица. Дополнительные сведения о работе со связанными таблицами в модели см. в разделе Добавление данных с помощью связанных таблиц Excel в Power Pivot.

Добавление данных в таблицу Power Pivot

В Power Pivot нельзя добавить строку в таблицу, просто введя новую строку, как на листе Excel. Но вы можете добавлять строки, копируя и вставляя или обновляя исходные данные и обновляя модель Power Pivot.

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

См. также

Учебные руководства по Get & Transform и Power Pivot

Введение в редактор запросов (Power Query)

Создайте модель данных с эффективным использованием памяти с помощью Excel и Power Pivot.

Учебное пособие. Импорт данных в Excel и создание модели данных

Узнайте, какие источники данных используются в модели данных книги

Отношения между таблицами в модели данных

Видео: ВПР — когда и как использовать

ВПР — когда и как использовать

Обучение Эксель 2013.

ВПР — когда и как использовать

ВПР — когда и как использовать

ВПР: когда и как использовать

  • ВПР: когда и как использовать

    видео

  • Гайки и болты VLOOKUP

    видео

  • Поиск значений

    видео

  • Скопируйте формулу ВПР

    видео

Следующий:

Расширенные функции ЕСЛИ

Важно: Попробуйте использовать новую функцию XLOOKUP, улучшенную версию VLOOKUP, которая работает в любом направлении и возвращает точные совпадения по умолчанию, что делает ее более простой и удобной в использовании, чем ее предшественница. Эта функция доступна только при наличии подписки на Microsoft 365. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена ​​последняя версия Office.

Основы использования ВПР.

Использовать функцию ВПР

  1. В строке формул введите = ВПР( ) .

  2. В скобках введите искомое значение, а затем запятую. Это может быть фактическое значение или пустая ячейка, которая будет содержать значение: (ч3,

  3. org/ListItem»>

    Введите массив таблиц или таблицу поиска, диапазон данных, которые вы хотите найти, и запятую: (h3,B3:F25,

  4. Введите порядковый номер столбца. Это столбец, в котором, по вашему мнению, находятся ответы, и он должен быть справа от ваших значений поиска: (h3,B3:F25,3,

  5. Введите значение поиска диапазона: ИСТИНА или ЛОЖЬ . TRUE находит частичные совпадения, FALSE находит точные совпадения. Ваша готовая формула выглядит примерно так: =ВПР(h3,B3:F25,3,ЛОЖЬ)

Хотите больше?

Функция ВПР

Краткий справочник: Напоминание о ВПР

Краткая справочная карта: советы по устранению неполадок функции ВПР

Если вам нужно найти информацию в большой электронной таблице или вы всегда ищете информацию одного и того же типа, используйте функцию ВПР .

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

Итак, в качестве примера я введу номера деталей, то, что знаю, и узнаю Цены, то, чего не знаю.

Для этого я щелкну ячейку, в которой я хочу увидеть цены, введу знак =, VLOOKUP и круглые скобки.

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

Я введу h3 в качестве первого аргумента, потому что именно там я буду вводить номера деталей.

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

Номера деталей начинаются в ячейке B3, и если я прокрутю вниз, вы увидите, что значения состояния заканчиваются в ячейке E52.

Итак, я введу B3, двоеточие и E52, затем еще одну запятую.