Группировка данных в сводной таблице
Microsoft Excel
Группировка в сводных таблицах
Несмотря на то что большая часть операций подведения итогов и вычисления показателей выполняется с использованием стандартных команд сводных таблиц, иногда возникают ситуации, в которых требуется дополнительная группировка данных. Команда Группировать вкладки Анализ позволяет легко и быстро консолидировать даты сделок в группы, основанные на месяцах или кварталах. Впоследствии вы сможете суммировать данные в этих группах таким же образом, как это выполняется в полях сводной таблицы. Отчет, сведенный по датам: Для группировки полей дат выделите заголовок поля дат или любую ячейку с датой. В3, В4, С4, D4… На контекстной вкладке ленты Анализ в области Группировать щелкните на кнопке Группировка по полю. Если поле содержит информацию о датах, откроется диалоговое окно Группирование рис. Обратите внимание, что исходные данные см. Если перед группированием вы выделили только одну ячейку, то кнопки Группировка по полю и Группировка по выделенному работают одинаково. Различие проявится только если перед группированием вы выделите несколько ячеек. Можно группировать данные по секундам, минутам, часам, дням, месяцам, кварталам и годам. По умолчанию выделен вариант Месяцы. Выберите также Дни и Годы и нажмите Ok. Обратите внимание на некоторые особенности группировки данных в итоговой сводной таблице. Во-первых, поля Месяцы и Годы добавлены в список полей рис. Не позволяйте себя одурачить — ваш источник данных не изменился и никаких новых полей не содержит. Эти поля теперь являются частью кеша сводной таблицы в памяти подробнее о кеше см. Создание нескольких сводных таблиц на основе одного источника данных: Во-вторых, по умолчанию поля Месяцы и Годы автоматически добавляются в макет сводной таблицы. Вы можете работать с ними, как с обычными полями: В-третьих, включайте данные по Дням , чтобы иметь возможность добавить это поле в сводную таблицу. Если вы в окне Группирование оставите только Месяцы , то поле Даты вы будете видеть в списке, вот только оно эквивалентно месяцам, а поле Месяцы не появится. И наконец, в окне Группирование добавляйте поле Годы. Если это сделать, то вы корректно сможете разделить данные по годам рис. Диалоговое окно Группирование предлагает настройки группировки по секундам, минутам, часам, дням, месяцам, кварталам и годам. А что делать, если нужно сгруппировать данные по одной или двум неделям или иным промежуткам времени? Прежде всего следует свериться с календарем, чтобы решить, с какого дня должна начинаться неделя: Например, первый понедельник в м году — 6 января. Если вы хотите, чтобы данные за первую неделю также отражались, вам следует выбрать в качестве начала отсчета последний понедельник го года — 30 декабря. Чтобы сгруппировать даты по неделям выделите заголовок или ячейку с датой. Перейдите на контекстную вкладку ленты Анализ и в разделе Группировать щелкните на кнопке Группировка по полю. В диалоговом окне Группирование рис. В результате станет доступным счетчик количество дней. Чтобы создать недельный отчет, установите значение 7. Установить в поле начиная с требуемую дату в нашем примере — В результате сгенерируется отчет, отображающий еженедельные объемы продаж, как показано на левой части рис. Если вы решили выполнить группировку данных по неделям, иные настройки группировки будут недоступны. Вы не сможете сгруппировать текущее или любое другое поле по месяцам или кварталам. Чтобы преодолеть это ограничение вы должны создавать сводные таблицы так, чтобы каждая была основана на своем кеше см. Именно так я и поступал в файле с примерами, чтобы сохранить вид примеров в первозданном виде. При группировке поля дат по месяцам и годам программа Excel переназначает исходное поле дат для отображения месяцев и добавляет новое поле для отображения лет. Новое поле получает имя Годы. Все довольно просто, если у вас в отчете представлено только одно поле дат. Если вам нужно создать отчет с двумя полями дат например, дата заказа и дата оплаты и вы пытаетесь сгруппировать оба поля по месяцам и годам, программа Excel сама установит первому сгруппированному полю имя Годы , а второму — имя Годы2. Чтобы не возникло путаницы, переименуйте поле Годы , прежде чем запустить группирование по второму полу дат. Диалоговое окно Группирование , применяемое для числовых полей, позволяет группировать элементы в одинаковые диапазоны. Это может быть полезно при проведении частотного анализа. Сводная таблица на рис. Здесь в область СТРОКИ помещено поле Доход , а в область ЗНАЧЕНИЕ — поле Заказчик. Поскольку поле Заказчик является текстовым, сводная таблица автоматически подсчитывает их количество, а не сумму. Выделите в столбце А любое число, а затем на вкладке Анализ щелкните на кнопке Группировка по полю. В диалоговом окне Группирование выберите параметры группирования рис. В рассматриваемом случае группирование начинается с 0 и завершается величиной 25 при шаге группирования Создав группу, вы можете разгруппировать её с помощью кнопки Разгруппировать , находящейся на вкладке Анализ. Достаточно выделить ячейку со сгруппированными данными и щелкнуть на этой кнопке. Эта команда также доступна в контекстном меню: Excel позволяет вручную группировать любые строки и столбцы сводной таблицы. Рассмотрим эту возможность на примере. Предположим, вас вызвал менеджер по продажам. Он хочет выполнить реорганизацию региональных отделений. Он просит предоставить отчет с объемами продаж за последние два года по указанным новым зонам и входящим в них рынкам сбыта. Вы догадываетесь, что эти зоны будут меняться не один раз до принятия окончательного решения, поэтому не хотите вносить изменения в источник данных. Для начала создайте отчет, отображающий доход по рынкам сбыта. Держа нажатой клавишу Ctrl, выделите рынки сбыта, на основе которых будет создана новая зона рис. Перейдите на вкладку Анализ, и щелкните на кнопке Группировка по выделенному. Используйте клавишу Ctrl для выделения несмежных ячеек, с тем чтобы сгруппировать их в новую зону. Excel добавит новое поле с именем Рынок сбыта2 рис. Выделенные на первом шаге рынки сбыта объединились в Группа1. Выделите оставшиеся рынки сбыта и повторно щелкните на кнопке Группировка по выделенному. Переименуйте не очень красиво звучащие новые поля, добавьте промежуточные итоги, отсортируйте по алфавиту строки по полю Зона продаж , и получите отчет, который не стыдно показать менеджеру рис. Сводные таблицы в Microsoft Excel Очень все доходчиво и понятно описано. То, что показано на рис. Группировка дат по неделям, есть возможность задать шаг группировки по дням с указанием к-ва дней. В вашем файле лист " Рис. Но когда я самостоятельно пытаюсь воссоздать подобную сводную, у меня шаг к-во дней не активно. Чтобы группировка по к-дням стала доступна, сначала разгруппируйте поле Даты. Если Вам нужно иметь группировку, и по месяцам, и по к-дням в одном файле, создайте вторую сводную таблицу на основе другого кеша. Как это сделать, см. В каких случаях в сводных таблицах группировка недоступна? Речь не идет о связанных таблицах — таблица только одна с исходными данными. Сергей, группировка по полю недоступна, если данные неоднородны. Например, среди дат есть текстовое значение или пустая ячейка. Или среди числовых данных затесалось хотя бы одно текстовое значение. В принципе текстовые значения не группируются, кроме как методом, описанным в заключительной части заметки. Например на одном компьютере месяцы на русском, на другом на английском, в зависимости от региональных установок на компьютере. Я не сталкивался с таким. Если я первоначально группирую по месяцам в русском Excel, то при открытии в английском Excel показывает название месяцев на английском; при этом группировка сохраняется. Мне не известна такая возможность. На мой взгляд, названия месяцев зашиты в региональные настройки MS Windows и не могут быть изменены внутри Excel. Если найдете, как это сделать, поделитесь…. Спасибо за подробное изложение материала. Если в сгруппированные строки таблицы необходимо добавить новую строку, то группировка слетает. Приходится убирать группировку всего документа, вносить требуемые дополнения и снова группировать. Есть ли более быстрый путь решения проблемы? Наталья, пришлите файл в личку с подробным описанием, что добавляете, и что при этом не получается. Уведомлять о новых сообщениях. Главная О проекте Рубрикатор Об авторе Цитаты Полезняшки Excel Современный управленческий учет. Путь воина Менеджерами не рождаются, менеджерами становятся. Группировка данных сводной таблицы в Excel Baguzin 13 комментариев Рубрика: Окно Группирование Можно группировать данные по секундам, минутам, часам, дням, месяцам, кварталам и годам. Добавление полей Месяцы и Годы в список полей и макет сводной таблицы Рис. Группировка дат по неделям Если вы решили выполнить группировку данных по неделям, иные настройки группировки будут недоступны. Группирование двух полей дат в одной сводной таблице При группировке поля дат по месяцам и годам программа Excel переназначает исходное поле дат для отображения месяцев и добавляет новое поле для отображения лет. Группировка числовых полей Диалоговое окно Группирование , применяемое для числовых полей, позволяет группировать элементы в одинаковые диапазоны. Группировка текстовых полей Excel позволяет вручную группировать любые строки и столбцы сводной таблицы. Используйте клавишу Ctrl для выделения несмежных ячеек, с тем чтобы сгруппировать их в новую зону Excel добавит новое поле с именем Рынок сбыта2 рис. Первая зона создана Переименуйте не очень красиво звучащие новые поля, добавьте промежуточные итоги, отсортируйте по алфавиту строки по полю Зона продаж , и получите отчет, который не стыдно показать менеджеру рис. Новак Сергей - С уважением Новак Ответить. При группировке по полю дата не получается явно указать язык названия месяца. Если найдете, как это сделать, поделитесь… Ответить. Как отключить сводную таблицу? Нажмите, чтобы отменить ответ. Полезняшки Excel 8. Разное 36 Библиотека О системе 67 О вероятностях 19 О познании 48 О психологии 54 О финансах 32 Об экономике 61 О маркетинге 24 О персонале 25 О качестве 18 О личной эффективности 33 Простая линейная регрессия Подсчет и суммирование ячеек, отвечающих критерию условного форматирования Определение среднего значения, вариации и формы распределения. Относительные, абсолютные и смешанные ссылки на ячейки в Excel Настольная книга стратега и новатора Построение доверительного интервала для математического ожидания генеральной совокупности Баланс, его структура и основные понятия О новых возможностях макроса по снятию "торможения" в Excel LG: Исследование о природе и причинах богатства народов Анри Пуанкаре. О науке Джон Локк. Два трактата о правлении Мишель Монтень. Как удержать внимание Ханна Арендт. Vita Activa, или о деятельной жизни. Goodwin Купить Купить Метандростенолон дозировка действует соло данабол в бодибилдинге для мышц метан Метан.
Helen Bradley рассказывает, как группировать данные в сводной таблице Excel для их дальнейшего обобщения и анализа. Работая в Excel со сводной таблицей, Вы можете прийти к выводу, что многие данные должны быть обобщены ещё больше. Это может быть сделано с помощью группировки данных, и цель этой статьи — показать Вам, как это делается. Представьте себе такую ситуацию: Вы анализируете подборку счетов, собранных в сводной таблице. Вы можете обобщать счета, объединяя их в группы по 5 — 10 или даже счетов. Давайте рассмотрим пример, где идентификатор строки OrderID — это числовое поле. В данный момент каждому счёту соответствует отдельная строка, и результат получается довольно громоздким. Мы можем сгруппировать эти данные, чтобы упростить таблицу. В этой сводной таблице каждый идентификатор OrderID представлен в отдельной строке. Такие данные можно считать обобщёнными, но нам этого недостаточно. Для этого кликните правой кнопкой мыши по одному из OrderID данной сводной таблицы и в появившемся меню выберите Group Группировать. Так как OrderID это числовое поле, то диалоговое окно уже будет подготовлено для численной группировки с полями Starting At Начиная с , Ending At По и By С шагом. В данной ситуации Excel предлагает Вам группировать элементы по 10 штук. По умолчанию Excel предлагает в качестве значения Starting At Начиная с первый элемент OrderID: Тем не менее, Вы можете настроить точнее или создать другую группировку, установив собственное значение параметра Starting At Начиная с. Чтобы начать с элемента , введите это значение в поле Starting At Начиная с — Вы можете установить любое стартовое значение, даже такое, которого нет в данных. Мы настраиваем параметры группировки данных — они будут сгруппированы по полю OrderID по десять последовательно пронумерованных заказов. Итак, мы настроили значение Starting At Начиная с , оставили значение Ending At По таким, как предложил Excel, и оставили значение By С шагом равным 10 , поскольку оно нам подходит. Вы можете делать группы меньше или больше, изменяя это значение — например: Данные будут сгруппированы по значению OrderID в более удобные для обработки группы. Вот так выглядят данные, сгруппированные по OrderID. Как видите, они значительно более обобщены, чем ранее. Если данные, с которыми Вы работаете, являются датами, то Вы сможете использовать похожий приём. Чтобы сгруппировать информацию по датам, кликните правой кнопкой мыши по дате в столбце или строке Вашей сводной таблицы и выберите Group Группировать. Вы можете выбрать шаг группировки — Seconds Секунды , Minutes Минуты , Hours Часы , Days Дни , Months Месяцы , Quarters Кварталы или Years Годы , и установить начальный и конечный момент времени. Для группировки по годам, кварталам и месяцам интервал зафиксирован равным 1 , а для группировки по дням Вы можете установить собственный интервал, например, равный 7 дням, чтобы сгруппировать данные по неделям. Для этого выберите Days Дни как шаг группировки, установите количество дней равным 7 , укажите для параметра Starting At Начиная с дату, которая выпадает на начало недели, и нажмите ОК. Информация будет сгруппирована по неделям. Большой объём данных будет сгруппирован по неделям по 7 дней , и первым днем выступит воскресенье — день недели, соответствующий дате, указанной в параметре Starting At Начиная с. Не всегда набор записей можно разделить по группам с такой же лёгкостью, как последовательность чисел или дат. Иногда группа становится группой просто потому, что Вы так решили. В этом случае можно создать свою собственную группировку. Откройте лист со сводной таблицей, кликните по первому элементу данных, который нужно включить в группу и далее, с нажатой клавишей Ctrl , последовательно щелкните по всем элементам, которые также нужно включить в эту группу. Таким же образом Вы можете выделить другие элементы и создать еще одну группу. При помощи этого инструмента Вы можете группировать данные в небольшие подборки, с которыми удобно работать. Так как по умолчанию группы названы именами Group 1 Группа 1 и Group 2 Группа 2 , Вам придётся самостоятельно переименовать их. Для этого кликните ячейку, содержащую имя группы, и в строке формул введите более подходящее имя для группы. Иногда, пытаясь сгруппировать выделенные данные, Вы получите сообщение об ошибке, уведомляющее, что выбранные данные не могут быть объединены в группу. Есть целый ряд причин, почему это может произойти. Если у Вас есть пустые ячейки в тех полях, где должны быть даты или числа, то это тоже станет причиной сообщения об ошибке. Ошибка появится и в том случае, если вместо даты или числа в ячейке содержится текстовое значение. В каждом случае, проверьте исходные данные и исправьте ошибку, а затем обновите сводную таблицу и попытайтесь снова. Одним из преимуществ, которые даёт группировка данных, является то, что Вы можете извлечь подмножество сгруппированных данных на новый лист. Чтобы сделать это, выберите группу, и дважды щелкните по ячейке с интересующим Вас итоговым значением. Данные, из которых было собрано это итоговое значение, будут извлечены на новый лист текущей рабочей книги Excel. Двойной щелчок мышью по любому значению в сводной таблице создаёт новый лист, содержащий все данные, относящиеся к этому значению. Вы можете создавать группы в сводной таблице, которая собрана из нескольких консолидированных диапазонов , например, из данных на разных листах рабочей книги. Для этого в Excel и на Панель быстрого доступа необходимо добавить команду PivotTable and PivotChart Wizard Мастер сводных таблиц и диаграмм. Чтобы сделать это, кликните выпадающее меню Панели быстрого доступа, выберите пункт More Commands Другие команды , в открывшемся диалоговом окне установите группу All Commands Все команды и в списке команд найдите PivotTable and PivotChart Wizard Мастер сводных таблиц и диаграмм. Нажмите кнопку Add Добавить , чтобы добавить кнопку на Панель быстрого доступа. Теперь Вы можете создать сводную таблицу из нескольких диапазонов Excel с единообразной компоновкой данных. Вам понадобятся данные с одинаковым количеством столбцов, одинаковыми заголовками столбцов, при этом каждая таблица может иметь разное количество строк. Эта команда исключена из меню Excel , но Вы можете добавить её на Панель быстрого доступа самостоятельно. Создайте новый лист в Вашей рабочей книге Excel, кликните только что добавленную кнопку PivotTable and PivotChart Wizard Мастер сводных таблиц и диаграмм , выберите пункты Multiple Consolidation Ranges В нескольких диапазонах консолидации и PivotTable Сводная таблица , а затем нажмите Next Далее. Выберите I will create the page fields Создать поля страницы и снова нажмите Next Далее. Теперь Вам нужно указать каждый из диапазонов. Зайдите на первый лист, выберите все данные, включая заголовки таблицы, и нажмите Add Добавить. Повторите шаги, чтобы добавить информацию со следующего листа и так далее, пока не добавите данные со всех листов, которые планировали использовать в сводной таблице. Мастер сводных таблиц и диаграмм позволяет объединять сразу несколько диапазонов в одной сводной таблице. В Мастере сводных таблиц и диаграмм первым делом нужно выбрать, какие диапазоны данных будут использованы. Далее укажите количество полей, которые нужно добавить в сводную таблицу — вероятнее всего, Вам понадобится одно или два. Теперь выберите один из диапазонов в поле немного выше и введите для него имя группы. Затем выберите следующий диапазон и проделайте для него те же самые шаги. И так далее для всех диапазонов. Если имя группы уже существует, Вы можете не вводить его, а выбрать из выпадающего списка. Так, например, диапазон, содержащий даты, может быть частью группы, которая разделяет месяцы по кварталам, а также частью группы, которая разделяет месяцы по сезонам: Winter Зима , Spring Весна , Summer Лето и Fall Осень. Группировки могут быть по любому признаку, который Вы посчитаете подходящим для своих данных. Нажмите Next Далее , кликните по ячейке, в которой должен будет находиться верхний левый угол сводной таблицы и нажмите Finish Готово. В Мастере сводных таблиц и диаграмм Вы можете связать диапазоны со страницами, как показано на рисунке выше — Quarters Кварталы и Seasons Сезоны. Сводная таблица будет сформирована автоматически, группы будут созданы как поля Report Filter Фильтры в списке полей сводной таблицы, но Вы, если пожелаете, можете перетащить эти поля в область Row Labels Строки или Column Labels Колонны. На рисунке выше показано: Группируете ли Вы данные в уже готовой сводной таблице или Вы делаете это вместе с созданием сводной таблицы из нескольких диапазонов — результат будет одинаковый, и это станет ещё одним полезным инструментом в Вашем арсенале для обобщения данных в сводных таблицах Excel. Урок подготовлен для Вас командой сайта office-guru. Андрей Антонов Правила перепечатки Еще больше уроков по Microsoft Excel. Оказываем помощь по MS Excel. Об авторе Правила сайта Блоги тестирование Контакты. Уроки MS Excel Самоучитель по Excel для чайников Уроки MS Word Каталог шрифтов Рекомендую. MS Excel Таблицы и сводные таблицы Как группировать данные в сводной таблице Excel для их дальнейшего обобщения и анализа. Группируем по значению Группируем по дате Группируем данные вручную Исправляем ошибки Детализация групп Группировка и несколько диапазонов консолидации Группируем по значению Представьте себе такую ситуацию: Группируем по дате Если данные, с которыми Вы работаете, являются датами, то Вы сможете использовать похожий приём. Группируем данные вручную Не всегда набор записей можно разделить по группам с такой же лёгкостью, как последовательность чисел или дат. Выделив несколько полей, Вы можете объединить их в группы так, как будет удобно. После того, как группы созданы, Вы вправе переименовывать их, как угодно. Исправляем ошибки Иногда, пытаясь сгруппировать выделенные данные, Вы получите сообщение об ошибке, уведомляющее, что выбранные данные не могут быть объединены в группу. Детализация групп Одним из преимуществ, которые даёт группировка данных, является то, что Вы можете извлечь подмножество сгруппированных данных на новый лист. Группировка и несколько диапазонов консолидации Вы можете создавать группы в сводной таблице, которая собрана из нескольких консолидированных диапазонов , например, из данных на разных листах рабочей книги. Войдите или зарегистрируйтесь чтобы добавлять комментарии. Кредиты различной длительности в Excel. Сортировка по цвету в Excel. Лист диаграммы в Excel. Как в диаграмме Excel использовать вспомогательную ось для построения графика. Как легко переместить или скопировать лист в Excel Серии статей и самоучители. Самоучитель по Excel 30 функций Excel за 30 дней примеров по Excel Работа с макросами в Excel Самоучитель по Excel VBA Сводные таблицы в Excel. Интерфейс и настройка Листы и книги Ячейки и диапазоны Форматирование Диаграммы и графика Формулы и функции: Ссылки и массивы Математические Текстовые Логические Даты и времени Статистические Проверка свойств и значений. Оказываем помощь по MS Excel Подробнее.
Какие витамины нужны против выпадения волос
Заполнение ттн 2017
Fox перевод на русский
Скачать карту украины для навител 9.7 2286
Keep it low перевод