Детальный отчет по продажам

1с для россии 1с для казахстана 1с для белоруссии 1с для украины

Отчет формирует детальный анализ продаж в разрезе менеджеров, видов номенклатуры, номенклатуры, клиентов и подразделений с возможность расшифровки до документов.

  • Бухгалтерия предприятия 3.0
  • Управление торговлей 11.4
  • Управление торговлей 3 (KZ) *
  • Комплексная автоматизация 2.4 (KZ) *
  • Управление торговлей 3 (BY) **
  • Комплексная автоматизация 2.4 (BY) **
  • Управление предприятием ERP 2.4
  • Комплексная автоматизация 2.4
  • Розница 2.3
  • BAS Управление торговлей 3 (UA) ***
  • BAS Комплексное управление предприятием 2 (UA) ***
  • BAS ERP (UA) ***

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

Отчеты по продажам

Отчеты по продажам собраны в разделе «Продажи».

По команде «отчеты по продажам» мы переходим в панель отчетов по продажам.

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

«Типовые условия продаж»

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

Анализ цен

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

«Оценка рентабельности продажи»

Следующие отчеты также доступны из документов «Заказ клиента». А именно, открыв соответствующий заказ, мы можем дополнительно вывести несколько отчетов применительно к данному заказу.

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

«Состояние выполнения документа»

Следующий отчет также доступен из «заказа клиента» ¾ это «Состояние выполнения документа». Данный отчет, во-первых, приводит информацию о задолженности клиента (то есть насколько выполнены этапы по оплате – скажем, аванс, предоплата), информацию по отгрузке. И в нижней табличной части, в части каждой товарной позиции приводится информация, какой товар был отгружен, в каком количестве, на какую сумму.

Причины отмены заказов

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

«Ведомость расчетов с клиентами»

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

«Задолженность клиента»

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

«Динамика просроченной задолженности клиентов»

Следующий отчет – «Динамика просроченной задолженности клиентов». Данный отчет показывает информацию о темпах роста просроченной задолженности. В табличной части информация сгруппирована по клиентам, по менеджерам. У нас в программе 1С Управление торговлей (УТ 11) 11.2 сейчас просроченная задолженность не зарегистрирована (вся задолженность является текущей), поэтому данный отчет пустой и никакой информации не содержит.

Платежная дисциплина клиента

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

«Задолженность клиентов по срокам»

Следующий отчет, который может пригодиться при анализе, — это «Задолженность клиентов по срокам». В данном отчете необходимо указать дату, на которую будет анализироваться информация, а также классификацию задолженности. В программе у нас сейчас одна классификация. В каждой созданной классификации указываются периоды, по которым будет анализироваться задолженность на предмет просрочки. Основной постулат здесь такой: наиболее свежую отчетность наиболее легко взыскать. Чем больше период просрочки, тем сложнее взыскать задолженность. В данном отчете мы получаем информацию по каждому клиенту, о состоянии взаиморасчетов с ним, а также информацию о сроках просрочки данной задолженности.

Карточка расчетов с контрагентом

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

Все эти отчеты доступны нам либо в разделе «Продажи» в группе «Отчеты по продажам», либо из документов взаиморасчетов – документов, которыми оформляются продажи клиенту, такие как «заказы клиентов» и «коммерческие предложения клиентам».

Такие возможности предоставляет нам программа 1С Управление торговлей версия 11.2 для анализа результатов продаж.

Построение отчета по продажам в разрезе производителей и выручки 1C УТ 8.3

В данном видео-уроке рассмотрен способ построение отчета по продажам в разрезе производителей и полученной выручке по ним в 1С управление торговлей 8.3. Основной смысл отчета — с товаров каких производителей мы получаем больше всего дохода.

Анализ розничных продаж

Для анализа реализации товаров торговыми точками в конфигурации реализовано достаточно много отчетов.
Все они доступны в разделе Продажи.

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

Данный отчет отражает Начальный и Конечный остаток, Приход и Расход, по каждой номенклатуре.

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

Для этого заходим в Настройки и на закладке Отборы выбираем нужный товар.

В результате чего сформировался нужный нам отчет. Из отчета мы видим что в Магазине Макси на начало не было остатка, 27.06.2016 было поступление на 100шт, а 29.06.16 была реализация двух единиц. Итого на конец периода остаток кофе 98шт.

Для контроля денежных средств в кассе ККМ используем отчет Денежные средства в кассах ККМ.

В сформировавшемся отчете мы видим Начальный и Конечный остаток, Сумму продажи и поступлений ДС, а также выемки ДС по дням.

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

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

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

В результате чего сформируется отчет следующего вида.

В программе есть возможность формировать отчет по регламентированной форме Торг-29. Данный отчет предназначен для анализа розничных продаж. В отчете отражаются остатки на начало и конец месяца, а также документы, по которым оформлялось движение товаров по указанному складу и указанной организации. Суммовые показатели в отчете рассчитывается по виду цены, указанному в карточке данного склада.

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

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

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

  • Минимальный. Рассчитывается по показателям предыдущего периода. При его невыполнении нужно проводить полный анализ ошибок и слабых мест в работе отдела продаж.
  • Нормативный. Это минимум плюс коэффициент, равный 1,3. При нем ожидается увеличение продаж на 30 процентов. При выполнении нормы работу отдела признают удовлетворительной, а прибыль – близкой к возможной.
  • Максимальный. Отличный результат. Но если его достигают неоднократно, всю систему планов потребуется пересмотреть в сторону увеличения.

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

Пошаговая инструкция по составлению плана:

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

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

Читайте также:  Как сделать сортировку в Excel по возрастанию и по убыванию

Структура правильного плана продаж

Бюджетирование в Excel заключается в создании форм бюджетов в формате Excel и связывании этих форм при помощи формул и макросов. Формы бюджетов, в том числе бюджета доходов и расходов, бюджета движения денежных средств могут быть различными, с укрупненными статьями или более подробные, разбиты на длительные периоды (например, годовой бюджет по кварталам) или на более короткие периоды (например, месячный бюджет по неделям) – в зависимости от потребности финансового менеджмента в компании.

Ниже приведен Бюджет доходов и расходов (пример составления в эксель) и пример Бюджета движения денежных средств.

Рисунок 1. Бюджет доходов и расходов предприятия образец Excel.

Рисунок 2. Бюджет движения денежных средств пример в Excel.

Планировать загруженность каждого менеджера

Самое лучшее планирование — это детальное планирование не только на всю компанию, но и по отдельным продавцам. Все из-за разницы загрузки и разных клиентских баз.

Самое глупое, что может сделать руководитель отдела продаж, — поставить всем сотрудникам одинаковый план.

Например, цель бизнеса в этом месяце — продажи на миллион рублей. Руководитель просто делит миллион на пять сотрудников, а затем ждет от каждого «по способностям». Не надо так.

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

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

Выполнение этого плана нужно контролировать не по итогам месяца, а с регулярной периодичностью. Лучше всего — еженедельно или ежедневно. В таком «трекинге» тоже хорошо помогает CRM-система. Следить за выполнением плана каждого сотрудника в ней гораздо удобнее, чем в ежедневных Excel-табличках.

Мы уже несколько лет планируем продажи именно так: оцениваем ресурсы, детализируем выручку, которую ожидаем от новых и старых клиентов. В среднем мы выполняем план на 101%.

Методы составления плана продаж

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

методы составления плана продаж

Наши эксперты отметили, что в компаниях малого бизнеса наиболее популярными из субъективных методов являются мнение персонала и мнение руководителей. А из объективных — статистический анализ спроса. Рассмотрим, как их правильно использовать.

Субъективное мнение

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

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

Александр Примак, коммерческий директор компании «A-leads»:
— Первый способ хорошо работает только на этапе роста, когда можно позволить свободу продавцам самим устанавливать план. Если они достигают поставленных задач, то зарабатывают много, не достигают — теряют в зарплате. Когда компания встала на ноги, план должны устанавливать только руководители.

Статистический анализ спроса

Трудоемкий метод, зато дает объективный результат. Нужно собрать все данные прошлых продаж, провести экономический анализ показателей и сделать выводы. При этом нужно учитывать факторы, которые влияют на объем сделок. К примеру, если компания производит палатки для туристов, то нужно учитывать сезонность, ведь весной и летом спрос будет повышаться.

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

Сергей Кудрявцев, гендиректор компании «АкваБур»:
— Мы включаем данные о предстоящих сделках, в которых уверены. Далее путем математических вычислений добавляем рост продаж в определенные месяцы за несколько лет. Учитываем факторы, влияющие на спрос в отрасли и во внешней среде. Это может быть что угодно, вплоть до прогноза погоды. Таким образом, путем статистического анализа изучается динамика уровня сделок — как сезонная, так и годовая. На сегодняшний день среднее выполнение месячного плана колеблется от 98 до 104%. Вместе с продажами мы планируем конверсию и отслеживаем эти показатели во взаимосвязи.

Если ваш бизнес работает в интернете, то не обязательно перелистывать кипы отчетов. Статистику можно собрать там же — в интернете.

Александр Примак, коммерческий директор компании «A-leads»:
— Спрос можно проанализировать путем экстраполяции данных статистики wordstat.yandex. К примеру, мы знаем, что в июле прошлого года спрос вырос на 19% по отношению к июню. Значит, в этом году в июле мы не будем делать много рекламы, так как товар продастся и без нее. А в сентябре прошлого года покупали меньше, значит в этом году мы вписываем в план увеличение расходов на рекламу именно в сентябре, чтобы удержать покупателей и нарастить продаж. С другой стороны, мы имеем экономические реалии. К примеру, лимит по возможной дебиторской задолженности. Это ограничивающий фактор, который надо учитывать.

Есть инструмент, который может собирать всю статистику по продажам за вас. Это CRM-система. Но не каждая подойдет именно вашему бизнесу. Сначала составьте список данных, по которым собирается статистика, а затем уже выясняйте, как работать с ними в CRM. Если вам сложно сделать это самостоятельно, то закажите аудит у специалистов. Они проанализируют ваш бизнес по 50-ти показателям и подскажут верное решение.

Какой товар продается лучше остальных?

В разделе «Анализ» используется отчет «Валовая прибыль по номенклатуре». Нам потребуется не стандартный вариант отчета, поэтому откроем «Настройку» и выберем «Расширенный» вид. (Рис 11)

На вкладке «Отбор» для поля «Номенклатура. (Рис 12) Краткое наименование» нужен отбор по условию, что «Краткое наименование» содержит какую-то определенную фразу. (Рис 13 и Рис 14) Кроме того, можно выполнить отбор по полям реквизитов, которые есть в составе агрегатных полей.

Далее в «Структуре» отчета требуется добавить новую группировку по цвету нужной номенклатуры. (Рис 15, 16)

Для этого из состава характеристики выберите «Цвет». Теперь у отчета появится такой вид, который нам необходим. (Рис 17)

В настройке нужно будет добавить информацию по количеству на вкладке «Сортировка». Выбираем признак сортировки «По убыванию». (Рис 18, 19)

После этого отчет будет удовлетворять всем условиям. (Рис 20)

Читайте также

PR-пакет «диетический» – «заморит червячка»

PR-пакет «диетический» – «заморит червячка» PR-пакет малой степени интенсивности делается для того, чтобы показать, в чем может заключатся работа PR-агентства. Зачастую он носит презентативный характер или, в крайнем случае, контракты по нему могут заключаться разве что

PR-пакет «средний» – удовлетворит голод

PR-пакет «средний» – удовлетворит голод Данный PR-пакет берут те, кто хочет хорошего продвижения, но пока не решается или боится платить «по полной»; или же те, кому удалось договориться о снижении ставки «Лучшего PR-абонемента» за вычетом небольшой порции услуг.В общем в

Мотивационный пакет

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

2.4.1. Манипулятивный «пакет» руководителя

2.4.1. Манипулятивный «пакет» руководителя Что такое манипуляция? Это скрытое влияние на человека или группу лиц с целью добиться своего. Например, провести в жизни какое-либо решение, зачастую непопулярное. Манипуляцию можно определить как скрытое управление с

2.4.3. Социальный пакет и лояльность персонала

2.4.3. Социальный пакет и лояльность персонала Социальный пакет в отличие от административных взысканий, напротив, относится к «пряникам». В принципе важно даже не содержимое пакета (оплачиваемый отпуск, декрет и т. д.), а сам факт того, что он, социальный пакет, существует.

Пакет для прессы

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

Уходите от шаблонов

Уходите от шаблонов Несколько слов по поводу офферов. Не обязательно использовать шаблон «Купи и получи». Одну и ту же фразу можно сказать по – разному. Например, предложение «Закажи рекламный блок и получи второй бесплатно» можно переписать таким образом: «Два

Отчетам быть. Но всё по-другому!

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

Применение шаблонов

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

Стратегия 3: небольшой пакет советов

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

6. Социальный пакет и другие емкости

6. Социальный пакет и другие емкости В какой-то момент наступает время обсуждения социального пакета и того, каким он должен быть. Иногда его наполнение влияет на сотрудников совершенно неожиданно, не так, как мы ожидали. Разбираясь с функцией, которую несет социальный

Компенсационный пакет

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

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

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

Что такое план продаж?

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

Что такое воронка продаж: как ее построить?

Цели составления плана продаж:

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

Читайте также:  Решение транспортной задачи в Excel с примером и описанием
Тип плана Срок Особенности
Перспективный 5-10 лет Ориентирован на долгосрочное прогнозирование результатов работы предприятия. Создается высококвалифицированными специалистами.
Текущий 12 месяцев Дополнение перспективного планирования. Основные функции – уточнение и корректирование показателей. Разрабатывается за 1-2 месяца до его внедрения.
Текущий 1-6 месяцев Месячные, квартальные и полугодичные планы несут в себе локальные задачи бизнеса – реализовать 1000 единиц продукции, набрать 30 человек в клиентскую базу и прочее.

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

  • "Продажи и переговоры. Психологические факторы успеха" онлайн тренинг от 5000 руб.р.
  • "Как выйти в большие продажи" практикум онлайн от 1500р.
  • "Стальные нервы" Антистресс- курс от 1500р.
  • Онлайн-тренинг "Стратегия и тактика больших продаж" от 3900 руб.р.
  • Руководитель отдела продаж. Курс в Москве + Онлайн-трансляция 36900р.

module = advertiment than change it to ‘da’ against anti-blocking system —>

Excel-лайфхаки для тех, кто занимается отчётностью и обработкой данных

Excel-лайфхаки для тех, кто занимается отчётностью и обработкой данных

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

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

Простые альтернативы ВПР и ГПР, если искомые значения не в первом столбце таблицы: ПРОСМОТР, ИНДЕКС+ПОИСКПОЗ

Функции ВПР (VLOOKUP) и ГПР (HLOOKUP) работают только в том случае, если искомые значения находятся в первом столбце или строке той таблицы, из которой вы планируете получить данные.

В остальных случаях есть два варианта:

  1. Использовать функцию ПРОСМОТР (LOOKUP).
    У неё следующий синтаксис: ПРОСМОТР (искомое_значение; вектор_просмотра; вектор_результата). Но для её корректной работы нужно, чтобы значения диапазона вектор_просмотра были отсортированы по возрастанию:excel
  2. Использовать сочетание функций ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX).
    Функция ПОИСКПОЗ возвращает порядковый номер элемента в массиве (с её помощью вы можете найти, в какой строке таблицы искомый элемент), а функция ИНДЕКС возвращает элемент массива с заданным номером (который мы и узнаем с помощью функции ПОИСКПОЗ).excelСинтаксис функций:
    • ПОИСКПОЗ (искомое_значение; массив_поиска; тип_сопоставления) — для нашего случая нам нужен тип сопоставления «точное сопоставление», ему соответствует цифра 0.
    • ИНДЕКС (массив; номер_строки; [номер_столбца]). В данном случае номер столбца указывать не нужно, так как массив состоит из одной строки.

Как быстро заполнить пустые ячейки в списке

Задача — заполнить ячейки в столбце со значениями сверху (чтобы тематика стояла в каждой строке таблицы, а не только в первой строке блока книг по тематике):

excel

Выделяем столбец «Тематика», нажимаем на ленте в группе «Главная» кнопку «Найти и выделить» → «Выделить группу ячеек» → «Пустые ячейки» и начинаем ввод формулы (то есть ставим знак равно) и ссылаемся на ячейку сверху, просто нажимая стрелку вверх на клавиатуре. После этого нажимаем Ctrl + Enter. После этого можно сохранить полученные данные как значения, так как формулы больше не нужны:

Как найти ошибки в формуле

Вычисление отдельной части формулы

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

Excel

  1. Чтобы вычислить часть формулы прямо в строке формул, выделите эту часть и нажмите F9:

    В данном примере была проблема с функцией ПОИСК (SEARCH) — в ней были перепутаны местами аргументы. Важно помнить, что если вы не отмените вычисление части функции и нажмёте Enter, то вычисленная часть так и останется числом.
  2. Нажмите на кнопку «Вычислить формулу» в группе «Формулы» на ленте:

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

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

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

Excel

Появляются стрелки, указывающие, от чего зависит результат вычислений.

Если отображается символ, выделенный на картинке красным цветом, то формула зависит от ячеек, находящихся на других листах или в других книгах:

Excel

Щёлкнув на него, мы увидим, где именно находятся влияющие ячейки или диапазоны:

Excel

Рядом с кнопкой «Влияющие ячейки» находится кнопка «Зависимые ячейки», работающая аналогично: она отображает стрелки от активной ячейки с формулой к ячейкам, которые зависят от неё.

Кнопка «Убрать стрелки», расположенная в том же блоке, позволяет убрать стрелки к влияющим ячейкам, стрелки к зависимым ячейкам или же оба типа стрелок сразу:

Excel

Как найти сумму (количество, среднее) значений ячеек с нескольких листов

Допустим, у вас есть несколько однотипных листов с данными, которые вы хотите сложить, посчитать или обработать как-то иначе:

Excel Excel

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

Excel

Вы получите сумму ячеек с адресом B3 с листов «Данные1», «Данные2», «Данные3»:

Excel

Такая адресация работает для листов, расположенных последовательно. Синтаксис следующий: =ФУНКЦИЯ (первый_лист:последний_лист!ссылка на диапазон).

Как автоматически строить шаблонные фразы

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

  • Объединяем текст с помощью знака & (можете заменить его функцией СЦЕПИТЬ (CONCATENATE), но в этом нет особого смысла).
  • Текст всегда записывается в кавычках, ссылки на ячейки с текстом — всегда без.
  • Чтобы получить служебный символ «кавычки», используем функцию СИМВОЛ (CHAR) с аргументом 32.

Пример создания шаблонной фразы с помощью формул:

Excel

Excel

В данном случае, кроме функции СИМВОЛ (CHAR) (для отображения кавычек) используется функция ЕСЛИ (IF), позволяющая изменять текст в зависимости от того, наблюдается ли положительная динамика продаж, и функция ТЕКСТ (TEXT), позволяющая отобразить число в любом формате. Её синтаксис описан ниже:

ТЕКСТ (значение; формат)

Формат указывается в кавычках точно так же, как если бы вы вводили пользовательский формат в окне «Формат ячеек».

Автоматизировать можно и более сложные тексты. В моей практике была автоматизация длинных, но рутинных комментариев к управленческой отчётности в формате «ПОКАЗАТЕЛЬ упал/вырос на XX относительно плана в основном из-за роста/снижения ФАКТОРА1 на XX, роста/снижения ФАКТОРА2 на YY…» с меняющимся списком факторов. Если вы пишете такие комментарии часто и процесс их написания можно алгоритмизировать — стоит один раз озадачиться созданием формулы или макроса, которые избавят вас хотя бы от части работы.

Как сохранить данные в каждой ячейке после объединения

При объединении ячеек сохраняется только одно значение. Excel предупреждает об этом при попытке объединить ячейки:

Excel

Соответственно, если у вас была формула, зависящая от каждой ячейки, она перестанет работать после их объединения (ошибка #Н/Д в строках 3–4 примера):

Excel

Чтобы объединить ячейки и при этом сохранить данные в каждой из них (возможно, у вас есть формула, как в этом абстрактном примере; возможно, вы хотите объединить ячейки, но сохранить все данные на будущее или скрыть их намеренно), объедините любые ячейки на листе, выделите их, а затем с помощью команды «Формат по образцу» перенесите форматирование на те ячейки, которые вам и нужно объединить:

Как построить сводную из нескольких источников данных

Если вам нужно построить сводную сразу из нескольких источников данных, придётся добавить на ленту или панель быстрого доступа «Мастер сводных таблиц и диаграмм», в котором есть такая опция.

Сделать это можно следующим образом: «Файл» → «Параметры» → «Панель быстрого доступа» → «Все команды» → «Мастер сводных таблиц и диаграмм» → «Добавить»:

Excel

После этого на ленте появится соответствующая иконка, нажатие на которую вызывает того самого мастера:

Excel

При щелчке на неё появляется диалоговое окно:

Excel

В нём вам необходимо выбрать пункт «В нескольких диапазонах консолидации» и нажать «Далее». В следующем пункте можно выбрать «Создать одно поле страницы» или «Создать поля страницы». Если вы хотите самостоятельно придумать имя для каждого из источников данных — выберите второй пункт:

Excel

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

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

Excel

Отчёт сводной таблицы готов. В фильтре «Страница 1» вы можете выбрать только один из источников данных, если это необходимо:

Excel

Как рассчитать количество вхождений текста A в текст B («МТС тариф СуперМТС» — два вхождения аббревиатуры МТС)

В данном примере в столбце A есть несколько текстовых строк, и наша задача — выяснить, сколько раз в каждой из них встречается искомый текст, расположенный в ячейке E1:

Excel

Для решения этой задачи можно воспользоваться сложной формулой, состоящей из следующих функций:

  1. ДЛСТР (LEN) — вычисляет длину текста, единственный аргумент — текст. Пример: ДЛСТР (“машина”) = 6.
  2. ПОДСТАВИТЬ (SUBSTITUTE) — заменяет в текстовой строке определённый текст другим. Синтаксис: ПОДСТАВИТЬ (текст; стар_текст; нов_текст). Пример: ПОДСТАВИТЬ (“автомобиль”;“авто”;“”)= “мобиль”.
  3. ПРОПИСН (UPPER) — заменяет все символы в строке на прописные. Единственный аргумент — текст. Пример: ПРОПИСН (“машина”) = “МАШИНА”. Эта функция понадобится нам, чтобы делать поиск без учёта регистра. Ведь ПРОПИСН(“машина”)=ПРОПИСН(“Машина”)

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

ДЛСТР(“Тариф МТС Супер МТС”) – ДЛСТР(“Тариф Супер”) = 6

А затем разделить эту разницу на длину той строки, которую мы искали:

6 / ДЛСТР (“МТС”) = 2

Именно два раза строка «МТС» входит в исходную.

Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» — тот, число вхождений которого нас интересует):

Финансовое моделирование проекта: что надо знать

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

Вот пошаговый план реализации.

Рисунок 1. Построение финансовой модели: рекомендуемые этапы

Рисунок 1. Построение финансовой модели: рекомендуемые этапы

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

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

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

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

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

Под катом вы найдете описание некоторых приемов работы с Excel, которые могут быть полезны при анализе массивов данных. Мы расскажем, как мы ведем управленческую статистику по сервису jivosite.ru с помощью сводных таблиц Excel и подключения к MySQL через ODBC на примере отчета по воронке продаж. Предлагаемый способ довольно прост и универсален, с его помощью можно строить красивые отчеты за считанные минуты.

Постановка задачи

Требуется построить отчет по воронке продаж для сервиса jivosite.ru. Это онлайн-консультант для интернет-магазинов, который продается по модели freemium. Пользователи подключаются с двухнедельным демо-периодом, в течение которого доступна расширенная версия. По истечении 2 недель остается бесплатная версия, которая продолжает работать без ограничения срока.

Таким образом, мы имеем следующую воронку продаж:

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

Загружаем исходные данные из БД

Для загрузки данных из БД в Excel нам понадобится ODBC-драйвер. В нашем случае будем использовать коннектор ODBC-MySQL для Windows. На маке с коннектором у нас что-то не сложилось, но возможно это уже поправили в новых версиях.

После установки драйвера создаем пустую книгу Excel, открываем вкладку «Данные» — «Из других источников» — «Из Microsoft Query»

Затем выбираем «Новый источник данных», вводим название подключения, выбираем драйвер «MySQL ODBC Driver». Затем нажимаем кнопку «Связь», вводим параметры подключения к нашей БД, кликаем «ОК». После этого, если подключение успешно установлено, Microsoft Query предложит пошаговый мастер создания запросов. Закрываем все всплывающие окна с отказом, а затем жмем «SQL» и вводим наш SQL-запрос, который выдаст исходную таблицу, вручную. Наш запрос просто делает выборку из таблицы подключенных клиентов со slave-сервера БД.

  • created — дата регистрации клиента
  • name — URL сайта
  • was_installed — 1 если клиент устанавливал виджет на свой сайт, 0 если никогда не устанавливал
  • chats_count — количество диалогов, состоявшихся с помощью нашего сервиса
  • is_paid — 0, если клиент нам ничего не платил, 1 — если платил

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

Создаем сводную таблицу

Чтобы большой исходный массив данных превратился в удобные и красивые отчеты, мы воспользуемся сводными таблицами. Кликаем на самую верхнюю-левую ячейку таблицы с исходными данными (ячейка А1) затем «Вставка» – «Сводная таблица» – «ОК». Таким образом, исходным массивом для сводной таблицы будет весь результат запроса в MySQL, при добавлении новых столбцов и строк сводная таблица обновится автоматом.

Пустая сводная таблица выглядит так:

Считаем количество клиентов на каждом этапе продаж

Прежде, чем формировать такой отчет, нам для каждой строки в исходной таблице нужно добавить номер недели и год, в котором клиент был подключен. Это нужно, чтобы группировать данные по году и неделе. Для этого открываем лист с исходными данными, листаем вправо до последнего столбца, кликаем на ячейку справа от заголовка последнего столбца, и там пишем «Неделя подключения». В таблицу добавился новый столбец с пустыми значениями в строках. Теперь в ячейке под заголовком нового столбца пишем формулу «=НОМНЕДЕЛИ(» и кликаем по ячейке в этой строке, в которой у нас указана дата подключения клиента.

В этом случае формула будет выглядеть как «=НОМНЕДЕЛИ([@created];21)». Если ячейка находится в той же строке, что и формула, умный Excel формирует ссылку на нее по названию столбца, а так же автоматически заполняет все строки таблицы этой формулой. При добавлении строк в таблицу исходных данных новые вычисляемые ячейки будут добавлены автоматически. Удобно, Экзелю респект :). Обратите внимание, что есть разные алгоритмы вычисления номера недели. Для себя мы выбрали схему №21.

Аналогично добавляем столбец «Год подключения» с формулой «=ГОД([@created])». После этого переходим на лист с нашей сводной таблицей, кликаем по сводной таблице правой кнопкой – «Обновить», чтобы таблица узнала про новые столбцы в исходных данных.

Разумеется, эти столбцы можно было бы добавить в исходные данные средствами SQL, но в Экзеле это как-то быстрее и приятнее. Хотя, это конечно дело вкуса 🙂

Теперь перетаскиваем столбцы «Год подключения» и «Неделя подключения» из списка полей в область «Названия строк», а поле «name» (у нас в этом поле хранится URL сайта) в область «Значения».

Мы получим аккуратную таблицу, в которой по неделям года разбито количество подключившихся клиентов. Мы перетащили поле «name» в область значений, чтобы Экзель посчитал количество элементов в этом столбце (т.е. все элементы), с группировкой по неделям и годам. Это будет число регистраций (второй этап воронки).

Посчитаем, сколько из зарегистрировавшихся в каждую неделю клиентов установили на сайт наш чат. Для этого перетащим поле «was_installed» в область значений. В этом поле в исходных данных стоит «0», если виджет не установлен, и «1», если установлен. Затем правый клик – «Параметры полей значений» — выбираем операцию «Сумма». Теперь в сводной таблице появился второй столбец, в котором мы видим, сколько из клиентов, зарегистрировавшихся в какую-либо неделю, установили виджет на сайте.

Теперь посчитаем активных клиентов. Активными будем считать тех, у которого состоялось более 20 диалогов с посетителями сайта. Для этого нам понадобится в таблицу исходных данных добавить столбец «is_active» c формулой ячеек «=ЕСЛИ([@[chats_count]]>20;1;0)». В столбце «chats_count» у нас количество чатов клиента. В результате в столбце «is_active» у нас будет «1», если у клиента более 20 чатов. Теперь поле is_active можно так же перетащить в область значений.

Добавив немного феншуя в виде гистограмм и переименовав столбцы, получаем вот такую табличку:

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

Считаем коэффициенты конверсии

Чтобы посчитать k0, надо взять данные по уникальным посетителям из гугл аналитики, и это мы оставим за рамками настоящего мануала (эту задачу, кстати, мы пока решаем копипастом из гугл аналитики).

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

Тут есть один не совсем красивый момент, который мы не нашли, как решить прямым образом: в таблицу исходных данных надо добавить столбец «one» с формулой «=1» — чтобы во всех ячейчах исходной таблицы появилась единица в этом столбце.

Теперь можно добавить такое вычисляемое поле:

Имя пишем «k1», в формуле указываем «=СУММ(was_installed)/СУММ(one)».

Если в сводной таблице отчет будет сгруппирован по неделям, то мы получим отношение количества клиентов, установивших виджет (СУММ(was_installed)), к общему количеству клиентов, зарегистрировавшихся в эту неделю (СУММ(one)). Если отчет будет сгруппирован по месяцам, то коэффициент будет пересчитан соответственно. Важно отметить, что конверсия показывает то, какая доля клиентов установила чат на своем сайте среди тех, кто зарегистрировался в определенную неделю. Т.е. если клиент зарегистрировался на четвертой неделе, и установил чат на сайте только на 10-й неделе, то изменится цифра в отчете за 4-ю неделю.

Теперь считаем конверсию из установленных в активных клиентов:
k2 = СУММ(is_active)/СУММ(was_installed)

Точно так же добавляем поле для конверсии из активных клиентов в платные:
k3 = СУММ(is_paid)/СУММ(is_active)

Только k3 на скриншотах показать не можем, коммерческая тайна 🙂

Теперь в нашей сводной таблице появились поля k1, k2, k3, которые можно перетащить в область значений. Добавив немного феншуя, получаем такую таблицу по воронке с разбивкой по неделям:

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

Воронка продаж по месяцам

Из недельного отчета сделать отчет по месяцам очень просто. В исходные данные добавляем столбец «Месяц подключения» с формулой «=МЕСЯЦ([@created])», кликаем правой по сводной таблице – «обновить» и перетаскиваем в сводной таблице поле «Месяц подключения» в область «Названия строк» (после поля «Год подключения»). Получится примерно так:

И вот красивая табличка по месяцам:

Другие варианты отчетов

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

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

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

Ссылка на основную публикацию