Часть 4
Предыдущие части статьи были посвящены обзору различных методов работы с многомерными данными посредством сводных таблиц Microsoft Excel. Здесь же мы поговорим об альтернативном подходе — наборе методов КУБ().
Прежде чем перейти к рассказу о возможностях функций семейства КУБ(), сделаем небольшое отступление и перечислим некоторые существенные ограничения стандартных сводных отчетов Microsoft Excel.
[expert_bq id=»1570″]Всю ответственность за опубликованные материалы, содержащиеся в них сведения, а также за соблюдение авторских прав несут пользователи, загрузившие материал на сайт. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] — Ты прав на 100%. Очень многие люди из бизнеса просто не добираются до крутых бизнес-аналитиков вроде тебя, а потому продолжают работать с тем, что уже изучили. Во многих компаниях даже при наличии каких-то зверских аналитических систем люди продолжают что-то пилить в Excel.Николай Павлов: «Excel — как автомат Калашникова. Он был, есть и будет»
Сводные таблицы идеальны для перекрестного сравнения двух наборов данных. Например, можно создать сводную таблицу на основе базы данных сотрудников, суммирующую зарплату сотрудников разных подразделений под каждой категории должностей.
Excel. Сводная таблица на основе другой сводной
В моей практике не раз возникала задача анализа данных ранее собранных в сводную таблицу. Допустим, что анализируя продажи, вы воспользовались исходными данными, представленными в следующем формате (табл. 1):
| Клиент | Дата отгрузки | Сумма отгрузки |
| ООО «Ромашка» | 21 декабря 2010 | 12 039,58 руб. |
| … | … | … |
| ООО «Лютик» | 22 декабря 2010 | 291 574,23 руб. |
На основе таких данных в Excel несложно сформировать сводную таблицу, что-то типа (табл. 2):
| Клиент | Сумма отгрузок за период |
| ООО «Ромашка» | 1 156 421,97 руб. |
| … | … |
| ООО «Лютик» | 2 586 924,36 руб. |
Если же вы хотите распределить клиентов по объему продаж, чтобы получить сводную таблицу типа (табл. 3):
| Диапазон объема продаж | Число клиентов в диапазоне |
| 0 – 499 999,99 руб. | 254 |
| … | … |
| 2 000 000 – 2 499 999,99 руб. | 21 |
то вы столкнетесь с трудностями, так как создать такую таблицу на основе исходных данных (таблица 1), вам не удастся.
Если это разовая процедура, то вы сначала создадите табл. 2, а потом на её основе создадите табл. 3. Но как быть, если вы хотите поддерживать табл. 3 в актуальном состоянии при изменении исходных данных (табл.1)!?
Мне не известны стандартные методы в Excel, позволяющие это сделать. К сожалению, опция в мастере сводных таблиц[1] «создать таблицу на основе данных, находящихся… в другой сводной таблице», не подходит:
При использовании мастера новая сводная таблица «опирается» на те же исходные данные, что и первоначальная сводная таблица. Для удобства воспользуйтесь Excel-файлом с примером. Перейдите на лист «стандарт». На нем представлены детальные данные о продажах: номер и дата заказа, номенклатура, входящая в заказ, и количество товаров
Стандартным образом создайте сводную таблицу на основе этих данных, а затем запустите мастер сводных таблиц, выберите опцию «создать таблицу на основе данных, находящихся… в другой сводной таблице», нажмите «Далее»:
На шаге 2 выберите на основе, какой сводной таблицы вы создадите новую таблицу:
Видно, что вторая сводная «опирается» на те же данные, что и первая:
Можете поэкспериментировать, и убедиться, что набор данных второй сводной таблицы не зависит от вида первой сводной таблицы (то есть от того, какие поля и как мы выбрали в первой таблице), а зависит только от исходных данных (колонки А–D).
Фактически мы создали копию первой сводной таблицы. Так что стандартные методы Excel для решения нашей задачи не подходят. Применим маленькие хитрости.
Пример приведен в Excel-файле на листе «хитрость». Для начала создадим именованный динамический диапазон на основе исходных данных – «исх1» с использованием функции СМЕЩ (как это делать можно посмотреть здесь). Именованный диапазон избавит нас от проблем при добавлении исходных данных, и позволит актуализировать все сводные таблицы простым нажатием кнопки «Обновить»:
Создавая сводную таблицу, укажите, что исходные данные – это диапазон с именем «исх1»:
Создайте именованный диапазон для сводной таблице, также с помощью функции СМЕЩ:
Вот зачем мы отключили итоги – чтобы они «не лезли» в этот диапазон!
Запустите мастер сводных таблиц (из панели быстрого доступа) и выберите «Создать таблицу на основе данных, находящихся… в списке или базе данных MS Excel», нажмите «Далее»:
На втором шаге, укажите диапазон, содержащий исходные данные для второй сводной таблицы – «св1», нажмите «Далее»:
Сгруппируйте индивидуальные значения в диапазоны, постройте сводную диаграмму:
Поэкспериментируйте с исходными данными, добавляя / удаляя строки. Достаточно последовательно обновить первую и вторую сводные таблицы, и все внесенные изменения будут учтены.
[1] Как вызвать мастера сводных таблиц в Excel2007 (где он в явном виде не представлен) см. здесь . [expert_bq id=»1570″]Если позже вы решите отображать расходы и доходы за весь период, снова установите флажок в поле Все раскрывающегося списка фильтрации и щелкните на кнопке ОК. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] На экране появится диалоговое окно Рекомендуемые сводные таблицы, показанное на скриншоте ниже. В левой его части приведены примеры сводных таблиц, которые Excel может создавать на основе данных, выбранных в списке.Сложная сводная таблица в excel как сделать — Информатизация
— Да, продолжаю пилить Plex. Для меня это не столько средство заработка (хотя его покупают), сколько способ автоматизировать то, за чем не хочется лезть в Power Query. Ну и не все знают Query. Поэтому сейчас я готовлю обновление «Плекса», хочу добавить как раз инструменты для работы с Power Query: быстрое создание запросов, замер быстродействия запросов, такие мелкие фишечки.
| Клиент | Сумма отгрузок за период |
| ООО «Ромашка» | 1 156 421,97 руб. |
| … | … |
| ООО «Лютик» | 2 586 924,36 руб. |
Сводные таблицы в excel 2010 и 2013 года – пример работы в excel
Создание отчёта в эксель 2010 и 2013 практически одинаково. Процесс в этих версиях немного отличается от создания в версии 2007 года. Но, некоторые моменты похожи: перед началом работы, необходимо создать простую таблицу, в которой указать все данные, необходимые для вас. Верхняя строка должна содержать название столбцов.
- Вам будет доступна панель инструментов для управления значениями. Все поля и их заголовки будут перечислены во вкладке «Выберите поля для добавления в отчёт». Для добавления нужных пунктов необходимо выбрать нужные поля и список будет создан.
- Есть возможность управлять расположением полей. Вы можете перетаскивать столбцы в нужные окна, а также сортировать в удобном для вас порядке. Также можно отфильтровать пункты. Для этого нужно перетащить пункт в окно «Фильтр». Если вам необходимо произвести расчёт по отдельному полю, то перетащите его в окно «Значение».
- Вы можете воспользоваться дополнительными опциями. Найти их можно по следующему принципу: «Параметры» ► «Работа со сводными таблицами». Там очень много инструментов, которые имеют большое количество настроек и дополнительных функций.
Сводная таблица в excel – как сделать в разных версиях программы
- Запускаем эксель 2007 года. На верхней панели выбираем вкладку «Вставка».
- Выбираем «Создание рекомендуемых сводных таблиц».
- Появится окно, в котором будут представлены варианты рамок. Вы можете предварительно посмотреть, как будет выглядеть ваш отчёт, если нажмёте на макет. После этого можете выбрать основной. Нажимаете на Ок и отчёт появится на листе. Кроме этого, эксель сам определит порядок расположения строк.
- В программе можно отфильтровать и упорядочить значения. Для этого есть специальный значок на панели.
Когда я объявил в соцсетях, что жду этого человека в прямом эфире в Instagram, восторгов было очень много. Неудивительно: Николай Павлов — настоящий гуру Excel, создавший самую большую базу знаний в Рунете по этому инструменту.
«Ушел из найма, когда убедился, что смогу работать на себя»
— Что заставило тебя перейти из работы в найме в частную практику? Как решился на это, может, был какой-то поворотный момент?
— Я вообще по натуре «перестраховщик», не склонен к резким переменам — обязательно нужно все подготовить, получить уверенность, иметь план B, а еще желательно и план C… Поэтому подготовка к этому переходу заняла очень много времени. Наверное, год или даже два.
Тогда я вел тренинги, и периодически после тренинга люди обращались со своими проектами. Кому-то нужно было написать макрос, кому-то — сделать систему учета. Тогда ведь не было BI, все было на макросах. Несколько таких проектов я тащил параллельно с тренерской работой. Один из них как раз и подтолкнул к частной практике.
Это был очень крупный проект для нефтяной компании, мы делали его около полугода. А у меня тогда еще такой подход был ко всему… Даже стоимость своей работы не называл клиентам. Думал, ну работаем же, что-то да заплатят 
Когда дело дошло до оплаты, это и изменило все в моей голове.
Меня спросили: сколько ты хочешь за эту работу — 20, 30 тысяч? И тут же уточнили: долларов, конечно! Надеюсь, что мое лицо в этот момент не изменилось. Я понимал, что 30 тысяч рублей за такой объем работы — это маловато даже по тем временам. Но о том, что это может быть 30 тысяч долларов, и подумать не мог!
Это были первые большие деньги, заработанные самостоятельно. Тогда я понял, что у меня есть запас примерно на полгода, и решил, что нужно уходить из найма. К тому же на тот момент я уже то ли перерос ту работу, то ли просто устал и выгорел… Вот, так все и произошло.
— С какими проблемами или вызовами ты сталкивался уже по мере роста собственного бизнеса?
— Самое сложное для меня — это оценить задачу в человеко-часах, а значит, и в деньгах. Самые большие факапы случались именно тогда, когда я некорректно и неправильно оценивал усилия, которые понадобятся для решения задачи.
Еще одна проблема — некорректное ТЗ. Иногда я не писал его вообще или писал небрежно. Так вот, когда так делаешь, обязательно потом огребаешь. Большинство моих косяков были связаны именно с одной из этих двух вещей.
А еще бывает обидно, когда кто-то заплатил за внедрение условного SAP 10 миллионов, а в итоге ничего не работает. И тебе приходится исправлять это за сумму в сотни раз меньше. Такое случается, да, у меня было не раз и не два. Правда обидно.
[expert_bq id=»1570″]Еще, насколько я знаю, Microsoft сейчас делает акцент на работу в онлайне, тратит ресурсы на то, чтобы Эксель Онлайн стал таким же, как десктопный. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Сводные таблицы в Excel позволяют экспериментировать с итоговыми данными в динамическом режиме. Кроме того, можно воспользоваться графическим образом сводной таблицы — сводной диаграммой, которая позволяет увидеть результаты анализа данных.Excel. Сводная таблица на основе другой сводной
Многие люди думают, что создание сводной таблицы сложно и отнимает много времени, но это просто неправда. По сравнению с тем, сколько времени вам потребуется для создания эквивалентного отчета вручную, сводные таблицы невероятно быстрые. Если у вас есть хорошо структурированные исходные данные, вы можете создать сводную таблицу менее чем за минуту.
Дистанционное обучение как современный формат преподавания
Сводные таблицы в Excel: Создание, Фильтрация, Форматирование
— Примерно 80% моей работы — это тренинги. Но часто после тренинга люди приходят и со своими проектами — это оставшиеся 20%. Чисто аналитических задач, таких как сбор данных, очистка, выявление закономерностей, построение отчетов, у меня совсем немного — обычно это только часть какой-то более крупной задачи. В проектах люди обращаются как за консалтингом, так и за разработкой.
Как построить Сводную таблицу за одну минуту?
Многие люди думают, что создание сводной таблицы сложно и отнимает много времени, но это просто неправда. По сравнению с тем, сколько времени вам потребуется для создания эквивалентного отчета вручную, сводные таблицы невероятно быстрые. Если у вас есть хорошо структурированные исходные данные, вы можете создать сводную таблицу менее чем за минуту.
Пример исходных данных
- На вкладке «Вставка» ленты нажмите кнопку «Сводная таблица».
- В диалоговом окне «Создание сводной таблицы» проверьте данные и нажмите кнопку «ОК».
- Перетащите поле (например, Товар) в область «Строки»
- Перетащите числовое поле в область «Значения» (например, Продажи).
Базовая сводная таблица за 30 секунд
Сводная таблица выше показывает общий объем продаж по продуктам, но вы можете легко изменить порядок полей, чтобы показать общий объем продаж по регионам, категориям, месяцам и т.д.
2. Какие должны быть идеальные исходные данные?
Чтобы минимизировать проблемы в будущем, всегда используйте исходные данные хорошего качества, организованные в виде таблицы. «Идеальные» исходные данные выглядят так:
- не содержат пустых строк или столбцов
- не содержат промежуточных итогов
- каждый столбец имеет уникальное имя (только в одной строке)
- каждое поле имеет значение в каждой строке
- столбцы не содержат повторяющиеся группы данных (например, названия месяцев, названия регионов и т.д.).
Идеальные исходные данные для сводной таблицы
3. Как проверить правильно ли работает Сводная таблица?
Когда вы создаете сводную таблицу в первый раз, используйте ее сначала для генерации простого счета, чтобы убедиться, что сводная таблица обрабатывает данные в соответствии с вашими ожиданиями.
Для этого просто добавьте любое текстовое поле в качестве поля Значение.
Вы увидите очень маленькую сводную таблицу, в которой отображается общее количество записей, то есть общее количество строк в ваших данных.
Если это число имеет смысл для вас, с таблицей можно работать. Если число не имеет смысла, возможно, сводная таблица неправильно читает данные или данные не были определены правильно.
300 имен означает, что у нас 300 сотрудников. Проверьте.
4. Как правильно спланировать работу со сводной таблицей?
Хоть это и очень увлекательно перетаскивать поля сводной таблицы и наблюдать за тем, как Excel выдает еще одно необычное представление данных, можно легко обнаружить множество непроизводительных кроличьих ям. Через час станет уже не так весело.
Прежде чем приступить к созданию таблицы, запишите, что вы пытаетесь измерить или понять, и нарисуйте несколько простых отчетов в блокноте. Эти простые заметки помогут вам пропустить огромное количество вариантов, которые есть в вашем распоряжении. Не усложняйте и сосредоточьтесь на вопросах, на которые вам нужно ответить.
5. Как создать «динамический диапазон»?
Если вы используете таблицу Excel для исходных данных вашей сводной таблицы, вы получите очень приятное преимущество: ваш диапазон данных становится «динамическим».
Динамический диапазон автоматически расширяет и сжимает таблицу при добавлении или удалении данных, поэтому не нужно беспокоиться о том, что в сводной таблице отсутствуют последние данные.
Когда вы используете таблицу, как исходник, сводная таблица всегда будет синхронизирована с вашими данными.
- Выберите любую ячейку в данных с помощью сочетания клавиш Ctrl + T, чтобы создать таблицу
- Нажмите кнопку Сводная таблица на вкладке Конструктор
- Создайте свою сводную таблицу
- Преимущество: данные, которые вы добавляете в таблицу, автоматически отображаются в сводной таблице при обновлении.
6. Как используйте сводную таблицу для подсчета?
По умолчанию в сводной таблице будет учитываться любое текстовое поле. Это может пригодиться во многих ситуациях. Например, предположим, у вас есть список сотрудников и вы хотите получить их количество по отделам?
Чтобы получить разбивку по отделам, выполните следующие действия:
Распределение сотрудников по отделам
7. Как показать итоги в процентах?
Во многих сводных таблицах вам нужно показывать процент, а не количество. Например, возможно, вы хотите показать разбивку продаж по продуктам. Но вместо того, чтобы показывать общие продажи для каждого продукта, вы хотите показать продажи в процентах от общего объема продаж.
Предполагая, что у вас есть поле с именем Продажи в ваших данных, просто выполните следующие действия:
- Добавьте товар в сводную таблицу в поле Строки
- Добавьте Продажи в сводную таблицу в качестве значения
- Щелкните правой кнопкой мыши поле «Продажи» и установите «Дополнительные вычисления» на «% от общей суммы»
8. Как использовать сводную таблицу для построения списка уникальных значений?
Поскольку сводные таблицы суммируют данные, их можно использовать для поиска уникальных значений в поле. Это хороший способ быстро увидеть все значения, которые появляются в поле, а также найти опечатки и другие несоответствия.
Например, предположим, что у вас есть данные о продажах, и вы хотите увидеть список каждого проданного товара. Чтобы создать список продуктов:
Каждый товар из данных указан в списке (включая опечатку)
9. Как создать автономную сводную таблицу?
Когда вы создали сводную таблицу из данных на том же листе, вы можете удалить данные, если хотите, и сводная таблица продолжит работать в обычном режиме. Это связано с тем, что в сводной таблице есть сводный кэш, содержащий точную копию данных, использованных для создания сводной таблицы.
10. Как сгруппировать сводную таблицу вручную?
Хотя сводные таблицы автоматически группируют данные разными способами, вы также можете группировать элементы вручную в свои собственные пользовательские группы.
Например, предположим, что у вас есть сводная таблица, которая показывает распределение сотрудников по отделам. Вы хотите еще больше сгруппировать отделы: Продажи, Закуп и Маркетинг в группу 1, а Эксплуатацию и Бухгалтерию — в группу 2. Группа 1 и группа 2 не отображаются в данных, это ваши собственные пользовательские группы.
Чтобы сгруппировать сводную таблицу в специальные группы, нужно:
- Удерживая клавишу «Ctrl», выберите каждый элемент в первой группе.
- Щелкните правой кнопкой мыши один из элементов и выберите в меню «Группа».
- Excel создает новую группу «Группа1»
- Выберите Эксплуатация и Бухгалтерия в столбце B и сгруппируйте, как указано выше.
- Excel создает другую группу, «Группа2»
Начало группировки вручную Половина ручной группировки — группа 1 выполнена Законченная группировка вручную
11. Как сгруппировать числовые данные в диапазоны?
Одна из наиболее интересных и мощных функций, которые есть в каждой сводной таблице, — это возможность группировать числовые данные в диапазоны или сегменты.
[expert_bq id=»1570″]Например, можно создать сводную таблицу на основе базы данных сотрудников, суммирующую зарплату сотрудников разных подразделений под каждой категории должностей. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq]- Нажмите кнопку «Редизайн таблицы» на вкладке XLTools > Откроется диалоговое окно.
- Выделите сводную таблицу, включая заголовки.
Совет: нажмите на любую ячейку таблицы, и вся таблица будет выделена автоматически. - Укажите размер заголовков:
Фильтрация с помощью временных шкал
- Где это возможно, надстройка продублирует заголовки из сводной таблицы.
- Категориям таблицы будет автоматически присвоен заголовок «Категория».
- Переменным значениям таблицы будет автоматически присвоен заголовок «Значение».
Excel нужен всем, начиная от домохозяек и заканчивая корпорациями. Это значит, что круг потенциальных клиентов очень широк. А уходить в другую систему, значит, сужать этот круг. Может, это действительно было бы более прибыльно, но я думаю, что пользы людям при этом ты будешь приносить меньше.
