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

Приветствую всех, дорогие читатели блога TutorExcel.Ru!

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

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

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

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

Давайте пошагово разберем как создать сводную таблицу.

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

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

• Умная таблица получает имя (в примере выше Таблица1, его можно легко поменять), которое можно использовать для определения диапазона в качестве источника для сводной таблицы;
• Умная таблица автоматически изменяет размер при добавлении или удалении новых строк или столбцов.

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

Вставка промежуточных итогов

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

1. Выберите любую ячейку в столбце С.
2. Щелкните правой кнопкой мыши и в контекстном меню выберите Сортировка ► Сортировка от А до Я.
3. Выберите Данные ► Структура ► Промежуточный итог для открытия диалогового окна Промежуточные итоги.
4. В окне Промежуточные итоги выберите в списке При каждом изменении в пункт Пол, в списке Операция — Среднее, а в поле Добавить итоги по установите флажок Оценка.

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

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

Как в Excel использовать сводную таблицу вместо формул — Трюки и приемы в Microsoft Excel

1. Выберите любую ячейку из диапазона данных и выполните команду Вставка ► Таблицы ► Сводная таблица для открытия диалогового окна Создание сводной таблицы.
2. В диалоговом окне убедитесь, что Excel выбрал правильный диапазон данных, и укажите ячейку в текущем листе в качестве местоположения. Ячейка Е1 будет неплохим выбором.
3. Нажмите кнопку ОК, и Excel отобразит список полей сводной таблицы.
4. В списке полей перетащите пункт Пол в раздел Названия строк, в самый низ.
5. Перетащите пункт Оценка в раздел Значения. Excel создаст сводную таблицу, но отобразит функцию СУММ, а не СРЗНАЧ.
6. Чтобы изменить используемую итоговую функцию, щелкните правой кнопкой мыши на любом значении в сводной таблице и выберите Итоги по ► Среднее в контекстном меню (рис. 103.3).

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

Открытые диапазоны в сводных таблицах

Существенный минус Сводных таблиц — они не работают с открытыми диапазонами.

Даже если вы укажете диапазон A2:E, он автоматически изменится на A2:E1000, где тысяча — номер последней строки в документе. Соответственно, если после этого в 1001 строке и ниже появятся данные, они не попадут в сводную.

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

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

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

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

Сводные таблицы в Google Spreadsheets | Ренат Шагабутдинов

Чтобы узнать среднее значение в зависимости от пола, можете воспользоваться функцией СРЗНАЧЕСЛИ для создания следующих формул:
=СРЗНАЧЕСЛИ(C2:C27;»Женский»;B2:B27)
=СРЗНАЧЕСЛИ(C2:C27;»Мужской»;B2:B27)