Как Добавить Формулу в Сводную Таблицу Excel • Использование формул

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

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

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

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

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

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

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

специалист
Мнение эксперта
Витальева Анжела, консультант по работе с офисными программами
Со всеми вопросами обращайтесь ко мне!
Задать вопрос эксперту
Это довольно типичный пример торгового листа, который показывает дату заказа, имя клиента, торгового представителя, общую сумму продаж и некоторые другие данные. Если же вам нужны дополнительные объяснения, обращайтесь ко мне!
Вы можете использовать эту технику для любых действий — практически везде, где вы хотите увидеть, как одно значение сравнивается с другим. В этой статье мы собираемся использовать простой пример расчета и отображения процента, на который ежемесячно изменяется общая стоимость продаж.
Данные о продажах за два года для сводной таблицы

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

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

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

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

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

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

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

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

Как сделать сводную таблицу в 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 для сводных работать будет. Так что если исходные данные начинаются с первой же строки листа и будут пополняться, используйте диапазон без номеров строк.

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

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

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

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Adblock
detector