По статистике почти половина (49,8%) предпринимателей использует Excel как средство управления бизнес-процессами. Это эффективный и доступный инструмент работы с большим потоком данных. И овладеть им не так сложно, как многим кажется. А вот его польза значительно существеннее, чем время и силы, потраченные на его внедрение. Особенно, если использовать его функционал по максимуму. И одна из самых полезных и доступных функций – это сводные таблицы. Что они из себя представляют? Как их настроить? Даем пошаговый алгоритм действий.
Что такое сводная таблица Excel и почему это удобно
Сводная таблица в Excel – это простой, но при этом многофункциональный инструмент, позволяющий работать с большим объемом информации, группируя ее, систематизируя и анализируя.
Благодаря сводным таблицам можно:
-
с помощью пары кликов создавать отчеты из десятков, а то и сотен тысяч строк;
-
настраивать фильтры, которые помогают акцентировать внимание на конкретных сводных данных;
-
добавлять графики для более наглядного понимания и оценки информации;
-
объединять данные из разных таблиц в Excel, исключая необходимость переходить с одного листа на другой;
-
выявлять тенденции и закономерности в общем пласте данных и пр.
Своим появлением сводные таблицы обязаны Пито Саласу. Этот разработчик еще в 1986 году заметил, что при объединении разных таблиц выявляются определенные закономерности, и было бы неплохо использовать это преимущество при работе с данными. А в 1994 году эта возможность пополнила функционал Excel.
По сути, сводные таблицы стали важным и крайне популярным способом автоматизации процесса обработки данных. Сейчас этот инструмент активно используется в маркетинге, продажах, финансовой аналитике, закупках, управлении проектами и командой, работе с контент-планами, PR, бизнес-аналитике, предпринимательстве и пр. Ее даже можно использовать, чтобы планировать личный бюджет, оценивать расходы и доходы, разбивать их на разные категории, проводить анализ самых «затратных» направлений и пр.
Как сделать простую сводную таблицу
Например, у вас есть данные сгруппированные в обычную Excel-таблицу. В ней отражены названия текущих проектов, их тип, команда, которая их ведет, установленные сроки реализации проекта, а также сумма, которую выделил заказчик на каждый проект. Как оценивать все эти данные через сводную таблицу? Для этого необходимо:
-
открыть нужный Excel-файл;
-
перейти на вкладку «Вставка»;
-
выбрать в ней иконку «Сводная таблица»;
-
в открывшемся окне необходимо выбрать диапазон данных, которые будут использованы при аналитике – задать его можно вручную или просто с помощью выделения нужных ячеек;
-
выбрать место, где будет создана сводная таблица – либо на том листе, который уже открыт и содержит данные, либо на следующем;
-
нажать «ОК».
Всего 6 шагов, и ваша сводная таблица создана. Теперь ее необходимо настроить. Во вкладке «Поля сводной таблицы» у вас автоматически появляются варианты заданных полей. Ставя галочки напротив нужных, вы автоматически добавляете их во сводку. Они автоматически попадают в графу «Сроки» или «Значения», вы можете перетаскивать поля в нужную для вас область.
Меняя последовательность выбора граф, вы будете менять и способ формирование отчетности. Например, данные могут быть сгруппированы по названию проекта и отражать имя команды, которая им занимается, либо, наоборот, по названию команды и списку проектов, которые она реализует. Все зависит от того, с какой целью вы формируете отчет и какую информацию хотите получить. Для более «красивой» и простой подачи можно использовать фильтры.
Как обновить данные в сводной таблице
В процессе работы исходная информация, на которой выстраивается сводная аналитика, может меняться. Например, появляются новые входящие условия, обновляются данные, корректируются, уточняются. Особенно это актуально для данных, подтягивающихся из таблиц, которые ведут разные сотрудники.
Чтобы получать корректные сведения, необходимо зайти в раздел «Анализ сводной таблицы» и кликнуть по графе «Обновить». Не будет никаких всплывающих окон, просто программа автоматически загрузит новые данные и отразит в таблице результат с учетом изменений.
Важно! Если исходные данные меняются систематически, то, обращаясь к информации из сводной таблицы, нужно взять за привычку всегда обновлять их, прежде чем оценивать.
Но бывает, что изменения в исходных данных весьма существенны – добавляются новые строки или столбцы. Тогда простое обновление не поможет, потому что в сводной таблице будут учитываться только те графы, которые были установлены в пределах определенного диапазона. Что делать в этом случае? Ответ простой – изменить этот диапазон.
Для этого необходимо:
-
открыть раздел «Анализ сводной таблицы»;
-
кликнуть по графе «Источник данных»;
-
в открывшемся окне перейти в значение «Таблица или диапазон» – в нем сейчас отражены заранее выбранные параметры;
-
установить новый диапазон, который включает добавленные строки или столбцы;
-
сохранить настройки кликнув «ОК».
После этого в сводной таблице будут учитываться и новые значения. Таким образом, изменение исходных данных не требует создания новой сводной таблицы, достаточно скорректировать предыдущий диапазон.
Как сделать сводную таблицу из нескольких листов
Excel дает возможность создавать сводные таблицы, которые учитывают данные сразу из нескольких источников, например, с других листов или даже других файлов. Это дает возможность сформировать более полную аналитику и не переключаться с одной вкладки на другую.
Чтобы настроить такой способ работы, необходимо:
- Определить, какие таблицы будут использованы в сводной аналитике.
Заранее понять, какие источники необходимо будет использовать, определить четкие названия граф и соотношения их друг с другом.
- Сделать все эти таблицы «умными».
Для этого необходимо открыть нужный файл, обратиться к разделу «Вставка» и выбрать пункт «Таблица». Затем следует выделить требуемый диапазон или внести значения самостоятельно. Следующий шаг – выбор опции «Таблица с заголовками». Обязательно прописывайте наименования таблиц, это поможет лучше ориентироваться в источниках данных.
«Умная» таблица приобретет новый вид:
- Создать сводную таблицу, которая использует данные из разных источников.
Алгоритм действий такой же. Необходимо в разделе «Вставка» кликнуть на команду «Сводная таблица». Но теперь обязательно нужно поставить галочку напротив пункта «Добавить эти данные в модель данных».
В открывшемся конструкторе необходимо перейти во вкладку «Все» – там будут отражен список уже созданных отдельных таблиц. Кликнув на каждую из них, можно открыть вкладку с названием строк и столбцов. Далее следует самостоятельно выбрать нужные значения, либо воспользоваться функцией «Автообнаружение». Если таблицы стандартные, а названия в столбцах или строках вписаны однозначно и правильно, то весь процесс пройдет автоматически.
Если нужно построить более сложные связи, необходимо выбрать не «Автообнаружение», а «Создать» и самостоятельно задать отношения.
Важно! Для правильного формирования сводной таблицы, которая учитывает разные источники данных, нужно, чтобы в них были общие графы, по которым будет построено логическое соотношение.
Полезные функции сводной таблицы
Понимание принципа работы того или иного инструмента позволяет лучше и эффективнее его использовать. Есть 2 простые функции, которыми многие пренебрегают, а зря.
Во-первых, это использование шаблонов. В графе, где находится иконка «Создание сводной таблицы», если еще и другая – «Рекомендованные сводные таблицы». Она крайне полезна для тех, кто только начинает осваивать этот инструмент. Используя ее, можно не только выстроить наиболее стандартные и предполагаемые соотношения, но и понять принцип работы конструктора и как в нем располагаются различные столбцы. Это крайне полезная практика, направленная на более полное освоение общего функционала сводных таблиц.
Вторая крайне полезная функция – это настраивание вычисляемых полей. Она нужна в том случае, если необходимо производить определенные математические действия с данными, которые отражаются в сводной таблице. Например, вы знаете, что 5% бюджета команды тратится на дополнительные расходы, связанные с повседневной деятельностью.
Сумму, которая на это идет, можно вычислить при помощи сводной таблицы. Для этого необходимо:
-
создать сводную таблицу, в которой отражен бюджет каждой команды;
-
перейти в поле «Анализ сводной таблицы»;
-
открыть меню «Поля, элементы и наборы»;
-
выбрать в нем пункт «вычисляемое поле»;
-
в открывшемся окне необходимо придумать название нового столбца, например «Дополнительные расходы»;
-
в разделе «Формула» нужно ввести конкретное значение и способ исчисления, например: «=Бюджет*0.05», то есть умножение на 0,05, как способ вычисления 5% от заданного бюджета.
Нажав на «ОК», вы получите желаемый результат – в сводной таблице появится новый столбец, который будет автоматически отражать сумму, которая потрачена на дополнительные расходы команды.
Что в итоге
Благодаря сводным таблицам в Excel можно значительно упростить и ускорить работу, оценивать результативность действий команды «здесь и сейчас», следить за аналитикой и соблюдением дедлайнов, распределять бюджет, нагрузку, контролировать выполнение тех или иных задач.
Чтобы использовать этот инструмент наиболее эффективно, полезно соблюдать 6 простых принципов:
- Давайте четкие и однозначные названия таблицам и графам в них.
Во-первых, даже если создаются отдельные таблицы, данные из которых не задействованы в сводных, это не значит, что такой необходимости не появится в будущем. Во-вторых, схожие и неточные названия могут вызвать путаницу при общем анализе данных. Чем лаконичнее и однозначнее название – тем яснее информация, которую отражает строка, столбец или конкретная ячейка.
- Двигайтесь от простого к сложному.
Если сводная таблица создается впервые, лучше всего обучаться работе с ней, задействуя небольшой объем информации, использовать шаблоны и рекомендации. А уже потом постепенно расширять функционал и выстраивать сложную систему взаимодействия различных данных и таблиц.
- Четко определяйте цель сводной таблицы и информацию, которую хотите получить.
Несмотря на то, что этот инструмент позволяет автоматизировать работу с данными и уменьшить ошибки, без участия человека, все же, не обойтись. Начиная создавать сводную таблицу, необходимо сразу определить, какой результат должен получиться на выходе, и какие данные (из каких источников) нужно будет «подтянуть», чтобы получить объективный и полный отчет.
- Чем больше объем информации, тем полезнее использовать фильтры.
Человеческое внимание имеет свои пределы – ограниченное количество объектов, которые можно удерживать в голове одновременно. Поэтому при больших сводных таблицах лучше всего настраивать фильтры и группировать информацию, чтобы наиболее ясно понимать нужные сведения.
- Применяйте средства визуализации.
Чтобы нагляднее оценивать данные, информацию по любому отчету в сводной таблице можно отразить в виде диаграммы или графика. Это позволяет лучше понимать динамику изменения данных, смотреть за их ростом, выявлять показатели, которые «проседают».
- Подключайте к работе со сводными таблицами других сотрудников.
Например, при создании сводных таблиц в «Google Таблицах» можно настроить доступ для отдельных сотрудников, которые могут не только дополнять и обновлять данные, но и следить за общими показателями и аналитикой. Это полезно, если их работа зависит от данных сводной таблицы, а также для самостоятельной оценки эффективности, причем как личной, так и команды.
Сводная таблица в Excel – это надежный инструмент для работы с данными из разных источников, в том числе и внешних. На начальных этапах работы человек может сталкиваться с различными трудностями и ошибками. Однако, благодаря практике, можно понять, что на самом деле весь процесс взаимодействия с таблицами достаточно прост и логичен, нужно лишь уделить достаточное количество времени и внимания, чтобы овладеть этим полезным инструментом аналитики.
