Как в excel сделать отчет

Делаем профессиональные отчёты в Excel: пять простых советов

Ольга Базалева, руководитель аналитического отдела в РБК, создатель онлайн-школы Excellent, автор книги «Мастерство визуализации данных» и автор блога, поделилась с Нетологией советами, как делать более удобные и понятные коллегам отчёты в Excel.

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

Хотите, чтобы руководитель вас заметил и в глазах коллег вы стали классным профессионалом? Научитесь делать качественные отчёты.

Создатель онлайн-школы визуализации данных Excellent

Power BI

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

Встаньте на место начальника. Кому вы готовы платить больше? Специалисту, который присылает ему данные в таком виде?

1. Помните о наглядности

• Располагайте данные логично: обычно это от большего к меньшему.
• Всегда разделяйте разряды в больших числах: 10 000 000, а не 10000000.
• Убирайте лишние знаки после запятой.
• Убирайте яркую сетку, это визуальный мусор. Делайте линии светлыми, а иногда можно обойтись вовсе без сетки.

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

2. Используйте диаграммы

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

3. Пишите выводы в сопроводительном письме

Отчёт вложен в письмо и готов к отправке заказчикам? Не спешите нажимать кнопку «отправить». Подумайте, что нужно человеку, который откроет ваше письмо? Ему нужны быстрые выводы. Обязательно добавьте в текст письма аналитические комментарии и подкрепите их фактами из отчёта — например, добавьте скриншот диаграмм.

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

4. Делайте отчёты удобными

Если ваш отчёт большой и занимает 10−20 листов в Excel, сделайте навигацию по нему. Это можно реализовать одним из следующих способов:

• создать лист Summary, показать на нём главные цифры и сделать ссылки «Подробнее» с переходом на листы, содержащие все данные;
• сделать оглавление, куда добавить ссылки на каждый лист с аналитикой.

5. Автоматизируйте обновление регулярных отчётов

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

В Excel есть множество инструментов, которые помогут автоматизировать отчёты:

• настройте выгрузку всех необходимых данных на один лист в Excel, назовём этот лист «Source»;
• создайте необходимые аналитические таблицы на других листах;
• формулами в эти аналитические таблицы подтяните нужные данные с листа Source;
• сделайте связку Power Point — Excel. Тогда при изменении диаграмм в Excel, в презентации Power Point они будут обновляться автоматически.

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

Когда ваши отчёты преобразятся, они станут в разы ценнее. Заказчики данных будут быстро получать ответы на свои вопросы, а набор сухих таблиц превратится в удобные и наглядные аналитические отчёты. Специалистов, которые умеют обрабатывать информацию и помогают принимать бизнес-решения ценят гораздо больше, чем тех, кто занимается простым заполнением непонятных таблиц.

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

Источник: netology.ru

Создание отчетов при помощи сводных таблиц

Видео

Лирическое вступление или мотивация

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

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

• кто из наших менеджеров заключил сделку
• с каким из заказчиков
• какого именно товара и на какую сумму продано
• с какого из наших складов была отгрузка
• когда (месяц и день месяца)

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

• Сколько и каких товаров продали в каждом месяце? Какова сезонность продаж?
• Кто из менеджеров сколько заказов заключил и на какую сумму? Кому из менеджеров сколько премиальных полагается?
• Кто входит в пятерку наших самых крупных заказчиков?

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

Если у вас Excel 2003 или старше

Ставим активную ячейку в таблицу с данными (в любое место списка) и жмем в меню Данные – Сводная таблица (Data – PivotTable and PivotChartReport) . Запускается трехшаговый Мастер сводных таблиц (Pivot Table Wizard) . Пройдем по его шагам с помощью кнопок Далее (Next) и Назад (Back) и в конце получим желаемое.

Шаг 1. Откуда данные и что надо на выходе?

На этом шаге необходимо выбрать откуда будут взяты данные для сводной таблицы. В нашем с Вами случае думать нечего – “в списке или базе данных Microsoft Excel”. Но. В принципе, данные можно загружать из внешнего источника (например, корпоративной базы данных на SQL или Oracle). Причем Excel “понимает” практически все существующие типы баз данных, поэтому с совместимостью больших проблем скорее всего не будет. Вариант В нескольких диапазонах консолидации (Multiple consolidation ranges) применяется, когда список, по которому строится сводная таблица, разбит на несколько подтаблиц, и их надо сначала объединить (консолидировать) в одно целое. Четвертый вариант “в другой сводной таблице. ” нужен только для того, чтобы строить несколько различных отчетов по одному списку и не загружать при этом список в оперативную память каждый раз.

Вид отчета – на Ваш вкус – только таблица или таблица сразу с диаграммой.

Шаг 2. Выделите исходные данные, если нужно

На втором шаге необходимо выделить диапазон с данными, но, скорее всего, даже этой простой операции делать не придется – как правило Excel делает это сам.

Шаг 3. Куда поместить сводную таблицу?

На третьем последнем шаге нужно только выбрать местоположение для будущей сводной таблицы. Лучше для этого выбирать отдельный лист – тогда нет риска что сводная таблица “перехлестнется” с исходным списком и мы получим кучу циклических ссылок. Жмем кнопку Готово (Finish) и переходим к самому интересному – этапу конструирования нашего отчета.

Работа с макетом

То, что Вы увидите далее, называется макетом (layout) сводной таблицы. Работать с ним несложно – надо перетаскивать мышью названия столбцов (полей) из окна Списка полей сводной таблицы (Pivot Table Field List) в области строк (Rows) , столбцов (Columns) , страниц (Pages) и данных (Data Items) макета. Единственный нюанс – делайте это поточнее, не промахнитесь! В процессе перетаскивания сводная таблица у Вас на глазах начнет менять вид, отображая те данные, которые Вам необходимы. Перебросив все пять нужных нам полей из списка, Вы должны получить практически готовый отчет.

Останется его только достойно отформатировать:

Если у вас Excel 2007 или новее

В последних версиях Microsoft Excel 2007-2010 процедура построения сводной таблицы заметно упростилась. Поставьте активную ячейку в таблицу с исходными данными и нажмите кнопку Сводная таблица (Pivot Table) на вкладке Вставка (Insert) . Вместо 3-х шагового Мастера из прошлых версий отобразится одно компактное окно с теми же настройками:

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

• Названия строк (Row labels)
• Названия столбцов (Column labels)
• Значения (Values) – раньше это была область элементов данных – тут происходят вычисления.
• Фильтр отчета (Report Filter) – раньше она называлась Страницы (Pages) , смысл тот же.

Перетаскивать поля в эти области можно в любой последовательности, риск промахнуться (в отличие от прошлых версий) – минимален.

Единственный относительный недостаток сводных таблиц – отсутствие автоматического обновления (пересчета) при изменении данных в исходном списке. Для выполнения такого пересчета необходимо щелкнуть по сводной таблице правой кнопкой мыши и выбрать в контекстном меню команду Обновить (Refresh) .

Источник: www.planetaexcel.ru

Примеры работы со сводными таблицами в Excel

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

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

Создание отчета с помощью мастера сводных таблиц

У нас есть тренировочная таблица с данными:

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

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

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

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

1. Выберите «Файл»-«Параметры»-«Панель быстрого доступа».
2. В выпадающем списке левой колонки: «Выбрать команду из» укажите «Все команды».
3. В левой колонке найдите по алфавитному порядку и выделите: «Мастер сводных таблиц и диаграмм». Нажмите на кнопку между колонками: «Добавить» чтобы инструмент переместился в правую колонку и нажмите ОК.

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

1. Ставим курсор в любом месте таблицы с данными. Вызываем мастер сводных таблиц, нажимая на соответствующий инструмент, который теперь уже расположенный напанели быстрого доступа.
2. На первом шаге выбираем источник данных для формирования сводной таблицы. Нажимаем «Далее». Чтобы собрать информацию в сводный отчет из нескольких листов, выбираем: «в списке или базе данных Microsoft Excel».
3. На втором шаге определяем диапазон данных, на основании которых будет строиться отчет. Так как у нас стоит курсор в таблице, диапазон обозначится автоматически.
4. На третьем шаге Excel предлагает выбрать, куда поместить сводную таблицу. Жмем «Готово» и открывается макет.
5. Нужно обозначить поля для отображения в отчете. Допустим, мы хотим узнать суммы продаж по каждому продавцу. Ставим галочки – получаем:

Готовый отчет можно форматировать, изменять.

Как обновить данные в сводной таблице Excel?

Это можно сделать вручную и автоматически.

1. Ставим курсор в любом месте сводной таблице. В результате становится видна вкладка «Работа со сводными таблицами».
2. В меню «Данные» жмем на кнопку «Обновить» (или комбинацию клавиш ALT+F5).
3. Если нужно обновить все отчеты в книге Excel, выбираем кнопку «Обновить все» (или комбинацию клавиш CTRL+ALT+F5).

Настройка автоматического обновления при изменении данных:

1. На вкладке «Работа со сводными таблицами» (необходимо щелкнуть по отчету) выбираем меню «Параметры».
2. Открываем «Дополнительные параметры сводной таблицы». Открывается мастер.
3. В разделе «Данные» устанавливаем галочку напротив пункта «Обновить при открытии файла».

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

Некоторые секреты форматирования

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

Группировка по дате в сводной таблице Excel:

1. Источник информации – отчет с данными.
2. Так как нам нужна группировка по дате, выделяем любую ячейку с соответствующим значением. Щелкаем правой кнопкой мыши.
3. Из выпавшего меню выбираем «Группировку». Откроется инструмент вида:
4. В полях «Начиная с» и «По» Excel автоматически проставил начальную и конечную даты диапазона данных. Определяемся с шагом группировки. Для нашего примера – либо месяцы, либо кварталы. Остановимся на месяцах.

Получаем отчет, в котором четко видны суммы продаж по месяцам. Поэкспериментируем и установим шаг – «Кварталы». Результат – сводная таблица вида:

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

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

Работа с итогами

У нас есть сводный отчет такого вида:

Видны итоги по месяцам (сделано «Группировкой») и по наименованиям товаров. Сделаем отчет более удобным для изучения.

Как в сводной таблице сделать итоги сверху:

1. «Работа со сводными таблицами» – «Конструктор».
2. На вкладке «Макет» нажимаем «Промежуточные итоги». Выбираем «Показывать все промежуточные итоги в заголовке группы».
3. Получается следующий вид отчета:

Уже нет той перегруженности, которая затрудняла восприятие информации.

Как удалить промежуточные итоги? Просто на вкладке макет выбираем «Не показывать промежуточные суммы»:

Получим отчет без дополнительных сумм:

Детализация информации

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

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

Мы можем переместить всю сводную таблицу на новый лист, выбрав на вкладке «Действия» кнопку «Переместить».

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

В нашем примере – ВСЕ товары, ВСЕ даты, ВСЕ суммы и магазины. Возможно, пользователю не нужны некоторые элементы. Они просто загромождают отчет и мешают сосредоточиться на главном. Уберем ненужные элементы.

1. Нажимаем на стрелочку у названия столбца, где будем корректировать количество информации.
2. Выбираем из выпадающего меню название поля. В нашем примере – это название товара или дата. Мы остановимся на названии.
3. Устанавливаем фильтр по значению. Исключим из отчета информацию по односпальным кроватям – уберем флажок напротив названия товара.

Источник: exceltable.com

Создание отчетов в Excel

Что такое отчет?

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

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

Как сделать отчет в Excel

Правда в том, что есть несколько способов раскрыть свою информацию в Excel. Я разделил отчеты Сводная таблица, Сценарий, Печать и Вручную. Я вижу, как создавать основные отчеты Excel:

1 – сводная таблица

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

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

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

Здесь просто выберите правильный диапазон или таблицу (обратите внимание, что в нашем примере интервалом является таблица 1) и нажмите кнопку OK. После этого вы откроете свой отчет сводной таблицы в Excel на новой вкладке:

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

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

2 – Сценарий отчета

Менеджер сценариев – это инструмент данных Excel, который расположен внутри кнопки Проверка гипотезы на вкладке Данные:

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

После создания сценариев нажмите кнопку «Возобновить»:

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

Нажатие ОК откроет новую вкладку со всеми возможностями изменения результата в ячейке O6 в соответствии с созданными сценариями. Обратите внимание, что сценарий оптимистичных яблок имеет лучший результат (2730), намного лучше, чем результаты 1870 и 2070 других сценариев.

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

3 – Распечатать отчет

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

Например, в нашей Рабочий лист денежного потокаМы создали специальную вкладку для отчета о печати в Excel:

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

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

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

4 – ручной отчет

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

Обратите внимание, что отчет имеет функции СОМАСЫ суммировать данные, находящиеся на нескольких других вкладках листа.

Создавайте отчеты в Excel

Теперь ваша очередь засунуть руку в тесто. Создайте свои собственные отчеты в Excel. Если у вас есть какие-либо трудности, скажите нам, какой из них поможет вам составить лучшее резюме из всех! Если вы хотите руку, мы рекомендуем Excel Начальный / Средний и Продвинутый курсы Excel.

Источник: blog.luz.vc

Как собрать отчеты с сотрудников и не попасть в Excel Hell

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

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

Как правило, организован этот процесс следующим образом

Есть руководитель, который заинтересован в получении сводной информации по подразделениям. Он ставит задачу своему ближайшему подчиненному (назовем его “аналитик”), о получении этого отчета.
Очевидным решением для аналитика является создание Excel-шаблона, который он рассылает по подразделениям. В подразделениях в свою очередь есть сотрудники, которые владеют этой информацией. Они заполняют шаблоны и присылают файлы обратно аналитику по почте. Половина из них ошибается и присылает исправленный файл на следующий день.
В итоге, аналитик тратит два дня на объединение всех этих файлов в один и подготовку сводного отчета. Руководитель же после просмотра сводного отчета понимает, что в нем не хватает данных, и просит добавить еще один столбец, который аналитик не предусмотрел. И вся цепочка повторяется с начала, рассылаются “шаблоны v2.xls”, отчитывающиеся сотрудники заполняют их и присылают обратно. В итоге у аналитика два набора данных, и понять какие из них актуальны может только он.

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

Это типичная ситуация возникновения Excel Hell в отдельно взятой организации.

Рассмотрим основные проблемы в этом процессе.

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

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

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

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

• Изменение структуры отчета.
  Если руководитель захочет смотреть отчет в другом разрезе или добавить какие-либо данные, которых раньше не было, или просто заменить вид графика на другой — что делать со всеми старыми отчетами? Перестраивать или оставить в “устаревшем” виде?

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

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

Ошибки при заполнении? — можно ограничить доступные значения в ячейках.
Разные версии данных? — завести таблицу в Google Spreadsheet или поставить Sharepoint.
Типовые операции? — написать макрос в Excel.
Исторические данные? — сделать сводную таблицу Excel или разработать свою БД.
Изменение структуры? — обучить аналитика SQL, если на прошлом этапе сделали БД.
Оперативность? — регламентировать процесс и платить премии за соблюдение сроков.

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

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

Как мы видим правильную организацию такого процесса

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

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

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

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

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

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

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

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

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

• Оперативность.
  Введенные значения должны сразу попадать в общее хранилище.

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

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

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

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

Соответственно, в сводном отчете, автоматически будут доставаться значения этого показателя (либо рассчитываться по формуле). Для простоты заполнения форм, мы поддерживаем импорт из Excel, copy-paste через буфер обмена и клавишную навигацию по таблице. Все данные полностью версионируются. Даже если удалить значение в ячейке, мы можем показать, какое число там было, кто его ввел и кто удалил. Кроме того, для обеспечения прослеживаемости информации, все вычисленные и сагрегированные значения можно раскрыть до исходных данных — вам не понадобится бегать по ячейкам Excel и понимать, что значит та или иная формула. Формулы пишутся не в терминах положения ячеек в таблице, а в терминах показателей и их признаков, т.е. в QuBeQu “Выручка”=”Доход”-”Расход”, а не “A1=B1-C1”.

Вы наверняка скажете, что поддержка такой системы требует затрат, потому что не бывает, чтобы “все само работало”. Но попробуйте сравнить, сколько времени аналитик тратил бы на сведение информации из файлов, со временем, которое он будет тратить на систему. Даже если это значения одного порядка — мы получаем принципиально разный результат.
Все данные, которые мы получили от отчитывающихся сотрудников структурированы, их можно быстро и просто использовать, они не потеряются. Есть единое место хранения информации, и если аналитиков, руководителей и отчитывающихся сотрудников много — там будет копиться большой объем данных, который можно исследовать.

Источник: habr.com

Умные Таблицы Excel – секреты эффективной работы

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

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

Как создать Таблицу в Excel

В наличии имеется обычный диапазон данных о продажах.

Для преобразования диапазона в Таблицу выделите любую ячейку и затем Вставка → Таблицы → Таблица

Есть горячая клавиша Ctrl+T.

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

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

Перед тем, как перейти к свойствам Таблицы, посмотрим вначале, как ее видит сам Excel. Многое сразу прояснится.

Структура и ссылки на Таблицу Excel

Каждая Таблица имеет свое название. Это видно во вкладке Конструктор, которая появляется при выделении любой ячейки Таблицы. По умолчанию оно будет «Таблица1», «Таблица2» и т.д.

Если в вашей книге Excel планируется несколько Таблиц, то имеет смысл придать им более говорящие названия. В дальнейшем это облегчит их использование (например, при работе в Power Pivot или Power Query). Я изменю название на «Отчет». Таблица «Отчет» видна в диспетчере имен Формулы → Определенные Имена → Диспетчер имен.

А также при наборе формулы вручную.

Но самое интересное заключается в том, что Эксель видит не только целую Таблицу, но и ее отдельные части: столбцы, заголовки, итоги и др. Ссылки при этом выглядят следующим образом.

=Отчет[#Все] – на всю Таблицу
=Отчет[#Данные] – только на данные (без строки заголовка)
=Отчет[#Заголовки] – только на первую строку заголовков
=Отчет[#Итоги] – на итоги
=Отчет[@] – на всю текущую строку (где вводится формула)
=Отчет[Продажи] – на весь столбец «Продажи»
=Отчет[@Продажи] – на ячейку из текущей строки столбца «Продажи»

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

Выбираем нужное клавишей Tab. Не забываем закрыть все скобки, в том числе квадратную.

Если в какой-то ячейке написать формулу для суммирования по всему столбцу «Продажи»

то она автоматически переделается в

Т.е. ссылка ведет не на конкретный диапазон, а на весь указанный столбец.

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

А теперь о том, как Таблицы облегчают жизнь и работу.

Свойства Таблиц Excel

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

2. Если Таблица большая, то при прокрутке вниз названия столбцов Таблицы заменяют названия столбцов листа.

Очень удобно, не нужно специально закреплять области.

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

4. Новые значения, записанные в первой пустой строке снизу, автоматически включаются в Таблицу Excel, поэтому они сразу попадают в формулу (или диаграмму), которая ссылается на некоторый столбец Таблицы.

Новые ячейки также форматируются под стиль таблицы, и заполняются формулами, если они есть в каком-то столбце. Короче, для продления Таблицы достаточно внести только значения. Форматы, формулы, ссылки – все добавится само.

5. Новые столбцы также автоматически включатся в Таблицу.

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

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

Настройки Таблицы

В контекстной вкладке Конструктор находятся дополнительные инструменты анализа и настроек.

С помощью галочек в группе Параметры стилей таблиц

можно внести следующие изменения.

— Удалить или добавить строку заголовков

— Добавить или удалить строку с итогами

— Сделать формат строк чередующимися

— Выделить жирным первый столбец

— Выделить жирным последний столбец

— Сделать чередующуюся заливку строк

— Убрать автофильтр, установленный по умолчанию

В видеоуроке ниже показано, как это работает в действии.

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

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

Однако самое интересное – это создание срезов.

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

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

Для фильтрации Таблицы следует выбрать интересующую категорию.

Если нужно выбрать несколько категорий, то удерживаем Ctrl или предварительно нажимаем кнопку в верхнем правом углу, слева от снятия фильтра.

Попробуйте сами, как здорово фильтровать срезами (кликается мышью).

Для настройки самого среза на ленте также появляется контекстная вкладка Параметры. В ней можно изменить стиль, размеры кнопок, количество колонок и т.д. Там все понятно.

Ограничения Таблиц Excel

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

1. Не работают представления. Это команда, которая запоминает некоторые настройки листа (фильтр, свернутые строки/столбцы и некоторые другие).

2. Текущую книгу нельзя выложить для совместного использования.

3. Невозможно вставить промежуточные итоги.

4. Не работают формулы массивов.

5. Нельзя объединять ячейки. Правда, и в обычном диапазоне этого делать не следует.

Однако на фоне свойств и возможностей Таблиц, эти недостатки практически не заметны.

Множество других секретов Excel вы найдете в онлайн курсе.

Источник: statanaliz.info