Вычисляемое поле в сводной таблице разделить один столбец на другой EXCEL
У меня есть две колонки в сводной таблице. Количество заказов на выполнение работ и сумма затрат. Я хочу вставить вычисляемое поле, которое просто делит сумму затрат на количество рабочих заданий, чтобы получить среднее значение для каждого рабочего задания.
Когда я вставляю, я вставляю вычисляемое поле по следующей формуле, оно дает общую стоимость, а не среднее значение. Вы увидите, что поля являются промежуточными (стоимость) и WO#(рабочий заказ)
И вот как выглядит вывод в моей сводной таблице.
2 ответа
Когда вы добавляете вычисляемое поле в сводную таблицу, вам нужно добавить только ссылку, а не вычисление внутри нее, поэтому вам не нужно добавлять сумму или количество в ваше определение. Тем не менее, Excel работает с вычисляемыми полями очень раздражающим способом — сначала он добавляет ваши значения, а затем выполняет вычисления — если, например, у меня есть вычисляемое поле, которое просто field3=field2/field1 , когда я хочу отобразить сумму этих значений, а не sum(field3) , оно делает sum(field2)/sum(field1)
Я бы порекомендовал сделать этот расчет за пределами пивота.
Например, посмотрите мои результаты, когда у меня есть следующая таблица в качестве входных данных для сводной
У меня возникла та же проблема, и я нашел нужный мне ответ. Как и OP, я хочу вычислить среднее значение — SUM(поле 1), деленное на COUNT(поле 2), — но проблема в том, что в одной формуле есть две функции (SUM, деленная на COUNT). Как мы уже видели, использование нескольких функций в одном расчете приводит к непредвиденным результатам.
Ключ, который работал для меня, состоял в том, чтобы создать новое поле (поле 3) в исходных данных с формулой, которая присваивает 1 элементам, которые я хочу считать, и 0 элементам, которые я не хочу считать, поэтому count этого столбца просто сумма. Таким образом, я конвертирую COUNT(поле 2) в знаменателе в SUM (поле 3). Итак, теперь мне нужно получить результат SUM, деленный на SUM, одну и ту же функцию сверху и снизу, которую может обрабатывать Excel. И, к счастью для меня в этой ситуации, «бесстрастная манера» вычислений в Excel — именно то, чего я хочу.
Как сказал Фернандо, рассчитанное поле должно относиться только к самому полю; он не должен использовать SUM или COUNT или что-либо еще. Требуемая функция будет применена, когда вы добавите поле в сводную таблицу и выберете нужную функцию.
Я установил для своей вычисляемой функции значение [поле 1 / поле 3] с оператором IF, чтобы избежать деления на 0, и я использовал функцию SUM, когда помещал вычисляемое поле в сводную таблицу. Конечным результатом является SUM (поле 1) / SUM(поле 3), что равно SUM (поле 1) / COUNT(поле 2)
Разделить сводную таблицу / пустые столбцы сводной таблицы
Столкнулся на работе с проблемой при формировании сводной таблицы по проектам.
С общей таблицы формирую сводную, по форме отчета, при этом между данными в форме отчета есть поля, заполняемые данными, не содержащимися в сводной таблице.
Вопрос:
Возможно ли разбить сводную таблицу на 2 диапазона, например: $A:$D и $H:$XX
или
Возможно ли каким-то образом пропустить 3 столбца в сводной таблице, например вставить пустые столбцы.
Были мысли касательно использования вычисляемых полей с формулой "=0", но может есть какой-то более изящный вариант. Если кто-то сталкивался с похожей проблемой и находил иное решение (отличное от "=0") подскажите пожалуйста.
Всем добра и снисходительного VBA.
Как сделать сводную таблицу из таблицы значений?
Такой код: Построитель = Новый ПостроительОтчета; Построитель.МакетОформления =.
Извлечение информации из сводной таблицы в таблицу
Добрый день. Помогите, плиз решить срочно задачки, не сталкивалась с данной работой по сводным.
Как разделить таблицу с уже готовым в ней текстом на столбцы
Ребята, есть текст Вложение 1. Который нужно преобразовать в таблицу с 4-мя столбцами, сначала.
Как сослаться на элементы управления сводной таблицы и сводной диаграммы ?
Как сослаться на элементы управления сводной таблицы и сводной диаграммы ?
Как вставить срезы в сводные таблицы Microsoft Excel
Andrew Childress Last updated Oct 7, 2017
Сводные таблицы могут изменить вашу карьеру. Если вы работаете в офисной среде и знаете, как использовать Excel, вы можете создавать отчеты и файлы, чтобы помочь каждому понять их данные.
Срез (фильтр-разделитель) — это функция сводной таблицы, которая делает отчеты интерактивными. Вместо того, чтобы использовать кнопки фильтра в сводной таблице, срез добавляет кнопки и, чтобы отфильтровать ваши данные, нужно лишь кликнуть.
В этом простом примере среза в Excel используется срез для фильтрации данных за один год, 2017. Обратите внимание на изменения данных в таблице.
Представьте, что вы используете срез для фильтрации ваших данных за определенный год. У вас есть блок срезов с каждым годом, который находится в данных, например «2016» и «2017». Чтобы увидеть данные только за 2016, вы можете кликнуть по нему в окне срезов, чтобы отфильтровать данные в сводной таблице.
Это делает отчеты интерактивными и удобными для пользователей. Направьте сводную таблицу со срезами сотруднику и ему не придется возвращаться к вам каждый раз, когда им понадобится другой отчет.
В этом уроке вы узнаете, как вставлять срезы в Excel и настраивать их, чтобы упростить работу со сводными таблицами.
Предпосылки: Что такое сводная таблица?
В принципе, сводная таблица является инструментом перетаскивания для создания отчетов в Excel. Вы можете взять большую таблицу данных и превратить её в значимые сводки всего за несколько минут с хорошо построенной сводной таблицей.
Этот скриншот является отличным примером того, как вы можете использовать сводную таблицу, чтобы понять большую электронную таблицу данных.
Что такое срезы в Excel?
Срезы упрощают работу со сводными таблицами. Вы можете создавать простые кнопки, чтобы пользователь мог просто нажать для фильтрования данных. Они упрощают работу с сводными таблицами.
Если вы хотите начать работу со сводными таблицами, ознакомьтесь с некоторыми из нижеприведенных руководств, прежде чем погрузиться в оставшуюся часть этого урока.
Теперь давайте погрузимся в создание и настройку срезов в Excel.
Как быстро вставлять срезы в сводные таблицы Excel (смотри и учись)
В этом быстром видеоуроке я покажу вам, как добавлять срезы в сводную таблицу, а затем настраивать их. Эти срезы помогут вам отфильтровывать и уточнять данные, отображаемые в сводной таблице.
Продолжайте читать, чтобы узнать больше о том, как использовать срезы в Excel в вашу пользу:
Добавить свой первый срез в Excel
Чтобы начать работу со срезами, начните, кликнув внутри сводной таблицы. На ленте Excel найдите раздел Работа со сводными таблицами и нажмите Параметры.
Теперь найдите пункт меню Вставить срез. Нажмите на него, чтобы открыть новое меню и выбрать срез.
Выбрав сводную таблицу, перейдите в пункт Параметры > Вставить срез, чтобы начать добавление новых окон со срезами в вашу книгу.
В появившемся новом меню у вас будут флажки для каждого из полей, которые находятся в сводной таблице. Установите флажки для срезов, которые вы хотите добавить.
После нажатия OK вы увидите окно для каждого из фильтров. Вы можете перетащить эти окна срезов в другие места электронной таблицы.
Я выбрал три параметра для добавления срезов и каждый из них теперь доступен для выбора, кликните и отфильтруйте данные.
Вот так! Вы вставили срез полей. Теперь вы можете выбрать и кликнуть, чтобы отфильтровать ваши данные. Давайте узнаем больше о том, как использовать и настраивать фильтры.
Как использовать срезы в Excel
В этой части урока основное внимание будет уделено использованию срезов для анализа ваших данных. Помните: срез — это просто удобные кнопки фильтров.
После того, как вы добавили срезы в вашу электронную таблицу, вы можете просто кликнуть по одному из полей внутри среза, чтобы отфильтровать ваши данные. При нажатии на срез вы увидите данные в сводной таблице.
Я решил разделить свои данные как по «Tutorials», так и по данным в 2017 году, используя два среза, которые меняют данные в исходной сводной таблице.
Когда вы нажимаете на кнопки срезов, сводная таблица обновляет только данные, соответствующие вашим настройкам фильтра.
Вы можете использовать срез для фильтрации данных, которые не отображаются в таблице. В принципе, это означает, что даже если вы не видите «year» как строку или столбец в сводной таблице, у вас всё равно может быть срез «Year», который изменяет данные, отображаемые в сводной таблице.
Предположим, что вы хотите выбрать несколько элементов срезов. Вы можете удерживать клавишу Ctrl на клавиатуре и нажимать на несколько элементов срезов, чтобы сводная таблица отфильтровалась по тем пунктам, которые вы выбрали.
На этом снимке экрана я выбрал несколько типов проектов, удерживая Ctrl и нажав несколько типов проектов.
И наконец, срезы складываются. После того, как вы нажмёте на кнопку для среза, вы увидите, что другие параметры срезов серые. Это означает, что первый фильтр, который вы применили, «выбил» другие параметры, что делает их недоступными для нажатия.
После того, как я отфильтровал по Accounting, вы заметите, что кнопка «2017» в срезах по году выделена серым цветом; это означает, что в 2017 году нет данных для Accounting, поэтому срез показывается так, что его нельзя выбрать.
Чтобы очистить настройки срезов, вы всегда можете щелкнуть маленькую иконку в правом верхнем углу, которая является кнопкой фильтра с небольшим красным X. Это приведет к отмене всех фильтров, которые вы применили с помощью срезов.
Расширенные параметры срезов в Excel
Давайте узнаем ещё несколько методов, чтобы действительно улучшить работу со срезами. Чтобы работать с большинством из этих параметров, убедитесь, что у вас выбран срез и щелкните в меню Инструменты для среза > Параметры на ленте, чтобы сделать эти настройки видимыми.
Убедитесь, что вы выбрали срез и перейдите в раздел Инструменты для среза > Параметры на ленте.
Оформление срезов в Excel
Вы можете легко изменить цвет срезов, чтобы они выделялись. В разделе Стили срезов на ленте вы можете просто кликнуть один из миниатюр цветов, чтобы изменить стиль среза.
Нажмите на другое представление, чтобы изменить Стиль срезов и изменить цвет вашего среза.
Выбор нескольких вариантов
Помните, как вы научились удерживать Ctrl на клавиатуре для многократного выбирать элементы срезов? Есть ещё один способ заставить этот вариант работать.
В правом верхнем углу каждого среза есть кнопка выбора, которую вы можете щёлкнуть. Когда эта опция включена, вы можете включать или отключать множество параметров, нажимая на них.
Нажмите кнопку множественного выбора, чтобы изменить работу кнопок; вместо того, чтобы удерживать Ctrl для выбора нескольких элементов, нажмите на каждый из них, чтобы включить или выключить их.
Это отличный приём, если вы отправляете сводную таблицу другим пользователям для работы.
Несколько столбцов в срезе
Если у вас много опций для срезов, они могут не помещаться внутри окна. Excel добавит полосу прокрутки. Однако прокручивать вверх и вниз может быть неудобно в этом окне.
Решение состоит в том, чтобы расположить кнопки в несколько столбцов срезов. Выбрав срез, найдите раздел параметров Кнопки в меню Инструменты для среза > Параметры. В меню столбцов измените число, чтобы в столбце отображалось больше столбцов.
Параметр Столбцы позволяет вам изменять количество столбцов кнопок, отображаемых в срезе.
Срезы Excel, подключенные к нескольким таблицам
Вот один из моих любимых трюков при использования срезов для нескольких сводных таблицах Excel. Если у вас несколько сводных таблиц, связанных с одними и теми же данными, это помогает подключить срезы для управления несколькими таблицами. Нажимаем на фильтр в срезе и все сводные таблицы будут обновляться соответственно ему.
Чтобы подключить срез к нескольким сводным таблицам, кликните поле срез и выберите Инструменты для среза > Параметры на ленте Excel. Затем нажмите Подключения к отчетам, чтобы открыть настройки.
Это окно показывает сводные таблицы, которыми можно управлять срезом. Просто установите флажки для сводных таблиц, к которым вы хотите подключить срез, и нажмите OK.
Подключите срез к нескольким сводным таблицам, чтобы синхронизировать их при нажатии на фильтры.
Теперь срез будет управлять несколькими сводными таблицами. Вам не нужно нажимать на несколько срезов, чтобы синхронизировать эти сводные таблицы. Представьте, что вы используете первый лист своей книги Excel со всеми срезами для настройки вашего отчёта на следующем листе, при этом все таблицы обновляются вместе.
Повторите и изучите больше инструментов данных в Excel
Я не могу рассказать всего о мощности срезов. Сводные таблицы — это отличное начало для анализа ваших данных, а срезы упрощают передачу другим пользователям для простой фильтрации. Вместо создания нескольких отчетов, пусть пользователь создает разные представления с помощью срезов.
Ниже приведены некоторые дополнительные ресурсы, которые помогут вам работать со срезами и сводными таблицами в Microsoft Excel:
Вопросы? Не стесняйтесь сообщить мне в разделе комментариев ниже, если вам нужны идеи о том, как использовать срезы в сводных таблицах Excel.
Как разделить строки в сводной таблице excel
В прошлой статье рассказывалось про то как создать простую сводную таблицу. Сейчас мы ее немного усложним и заодно разберемся в чем отличие областей СТРОК, КОЛОНН и ЗНАЧЕНИЙ. Вы поймете когда и в какую область необходимо переносить поле сводной таблицы.
Задача
Возьмем все те же исходные данные (перечень активов компании) и сделаем сводный отчет в котором посчитаем рыночную стоимость по группам активов, и филиалам.
Решение
Создадим пустую сводную таблицу по алгоритму, который описан в предыдущей статье.
Теперь давайте конструировать отчет. Давайте перенесем поле Филиал в область СТРОКИ, тогда мы получим перечень всех филиалов (без дубликатов) из исходной таблицы.
Теперь давайте расположим все наши группы активов по столбцам, для этого перенесем поле Группа в область КОЛОННЫ. Получим следующую картину:
Так как групп достаточно много, то и столбцов сводной таблицы получилось столько, что они не влезают на экран. А вот филиалов в компании не много. Давайте поменяем местами филиалы и группы, посмотрим, что получится.
Так получилось намного нагляднее. Осталось добавить сумму по рыночной стоимости в наш отчет. Для этого перенесем поле Рыночная стоимость в область ЗНАЧЕНИЯ.
Группировка данных в сводной таблице в Excel
Поговорим про группировку данных в сводной таблице в Excel, которая позволяет структурировать вид таблицы и существенно упрощает работу с данными.
Приветствую всех, дорогие читатели блога TutorExcel.Ru!
Не так давно мы с вами учились и разбирались в том как создавать сводные таблицы, если еще не читали, то всячески рекомендую к прочтению.
Но построить сводную таблицу — это можно сказать лишь первый шаг в работе с ними, одним из следующих шагов является умение работать с построенными данными, и сегодня мы как раз научимся группировать сводную таблицу и структурировать в ней данные. Также не забудем и про снятие группировки.
Мы разберем 3 основных варианта группировки полей сводной таблицы в зависимости от типа данных:
- Дата/время;
- Числа;
- Произвольная группировка.
При этом абсолютно не важно какие именно поля сводной таблицы мы группируем, алгоритм и для строк и для столбцов одинаковый, поэтому для удобства мы рассмотрим примеры для строк, для столбцов же действия будут идентичными.
Как сгруппировать данные в сводной таблице в Excel?
Рассмотрим простую таблицу, где в качестве данных как раз присутствуют интересные нам срезы данных — даты, числа и наименования:
А теперь приступим к конкретным примерам.
Группировка по датам в сводной таблице
Построим по исходной таблице сводную (подробно про это рассказывал в отдельной статье), в строки таблицы добавим даты, в значения поместим сумму:
Мы видим, что каждый день по отдельности получился и в сводной таблице, и часто бывает так, что такой формат раздувает по объему таблицу и нам совершенно не подходит. Поэтому достаточно часто требуется сгруппировать сводную таблицу по месяцам или кварталам, тем самым укрупнить показатель.
Встаем в любой ячейку с датой, нажимаем правой кнопкой мыши и выбираем в контекстном меню команду Группировать:
В открывшемся окне мы можем выбрать начальную и конечную дату, по которым будут строиться данные, но здесь нас больше интересует именно шаг данных.
Мы можем выбрать любую метрику по времени от секунд до годов (в том числе и сразу показателей несколько), выберем подходящие (к примеру, дни и месяца слева на картинке, кварты и месяца — справа) и группировка дат в сводной таблице будет сделана от более крупного к более мелкому:
Важный момент. Начиная с версии Excel 2016 программа автоматически умеет группировать данные по дате. Тем не менее если группировку захочется поменять, то в этом случае как раз подойдет ручной способ группировки.
Сгруппировать сводную таблицу по датам у нас получилось, теперь перейдем к аналогичной задаче для чисел.
Группировка по числам в сводной таблице
Немного видоизменим нашу сводную таблицу и в строки вместо дат добавим числовой код товара:
Алгоритм действий точно такой же как и в варианте с датами, щелкаем по любой ячейке с числами правой кнопкой мыши и выбираем команду Группировать:
Мы также можем настроить начальную и конечную точку для группировки чисел, а также задать шаг по которому числа будут делиться на группы:
С числами тоже все оказалось не так сложно, перейдем к последнему варианту.
Произвольная группировка в сводной таблице
Иногда хочется сделать группировку не исходя из каких-то четких правил формирования с заданием шага, а абсолютно произвольной. В этом смысле Excel нас никак не ограничивает и мы можем сгруппировать любые данные (не только даты и числа) по нашему усмотрению. Так как даты и числа мы с вами уже научились группировать, то предлагаю произвольную группировку сделать для текста.
Опять немного видоизменим нашу сводную таблицу и в этот раз в поля добавим наименование в текстовом виде:
Произвольная группировка от автоматической отличается тем, что в произвольной нам нужно выделить все элементы, которые мы хотим объединить в одну группу.
Например, давайте объединим наименование по компьютерной тематике, в одну группу поместим компьютеры, мониторы, ноутбуки и процессоры. Выделяем мышкой подходящие ячейки, также щелкаем по ним правой кнопкой мыши и выбираем команду Группировать:
Новой группе мы можем дать имя, для этого достаточно непосредственно в ячейке прописать новое название. Если нужно сделать группировку для остальных элементов, то принцип действия точно такой же — выделяем подходящие ячейки и группируем:
С группировкой данных разобрались, но вполне может потребоваться вернуть данные в исходный вид и тут нужно уметь снимать группировку.
Как разгруппировать данные в сводной таблице в Excel?
Принцип разгруппировки данных в целом точно такой же как и для группировки. Достаточно выделить ячейку из сводной таблицы, которая содержится в группировке, щелкнуть правой кнопкой мыши по ней и выбрать команду Разгруппировать, после чего сводная таблица вернется в первоначальный вид.
Спасибо за внимание!
Если у вас есть вопросы по теме — заходите и пишите комментарии.