Создание вложенных групп
В Эксель можно создавать не только группы первого порядка, но и вложенные. Для этого, нужно в развернутом состоянии материнской группы выделить в ней определенные ячейки, которые вы собираетесь сгруппировать отдельно. Затем следует провести одну из тех процедур, какие были описаны выше, в зависимости от того, со столбцами вы работаете или со строками.
После этого вложенная группа будет готова. Можно создавать неограниченное количество подобных вложений. Навигацию между ними легко проводить, перемещаясь по цифрам, расположенным слева или сверху листа в зависимости от того, что сгруппировано строки или столбцы.
Создание сводной таблицы в Excel
Открываем исходные данные. Сводную таблицу можно строить по обычному диапазону, но правильнее будет преобразовать его в таблицу Excel. Это сразу решит вопрос с автоматическим захватом новых данных. Выделяем любую ячейку и переходим во вкладку Вставить. Слева на ленте находятся две кнопки: Сводная таблица и Рекомендуемые сводные таблицы.
Если Вы не знаете, каким образом организовать имеющиеся данные, то можно воспользоваться командой Рекомендуемые сводные таблицы. Эксель на основании ваших данных покажет миниатюры возможных макетов.
Кликаете на подходящий вариант и сводная таблица готова. Остается ее только довести до ума, так как вряд ли стандартная заготовка полностью совпадет с вашими желаниями. Если же нужно построить сводную таблицу с нуля, или у вас старая версия программы, то нажимаете кнопку Сводная таблица. Появится окно, где нужно указать исходный диапазон (если активировать любую ячейку Таблицы Excel, то он определится сам) и место расположения будущей сводной таблицы (по умолчанию будет выбран новый лист).
Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.
Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.
В верхней части панели находится перечень всех доступных полей, то есть столбцов в исходных данных. Если в макет нужно добавить новое поле, то можно поставить галку напротив – эксель сам определит, где должно быть размещено это поле. Однако угадывает далеко не всегда, поэтому лучше перетащить мышью в нужное место макета. Удаляют поля также: снимают флажок или перетаскивают назад.
Сводная таблица состоит из 4-х областей, которые находятся в нижней части панели: значения, строки, столбцы, фильтры. Рассмотрим подробней их назначение.
Область значений – это центральная часть сводной таблицы со значениями, которые получаются путем агрегирования выбранным способом исходных данных.
В большинстве случае агрегация происходит путем Суммирования. Если все данные в выбранном поле имеют числовой формат, то Excel назначит суммирование по умолчанию. Если в исходных данных есть хотя бы одна текстовая или пустая ячейка, то вместо суммы будет подсчитываться Количество ячеек. В нашем примере каждая ячейка – это сумма всех соответствующих товаров в соответствующем регионе.
В ячейках сводной таблицы можно использовать и другие способы вычисления. Их около 20 видов (среднее, минимальное значение, доля и т.д.). Изменить способ расчета можно несколькими способами. Самый простой, это нажать правой кнопкой мыши по любой ячейке нужного поля в самой сводной таблице и выбрать другой способ агрегирования.
Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.
Область столбцов – аналогично строкам показывает уникальные значения выбранного поля, только по столбцам. Названия столбцов – это также обычно качественный признак. Например, годы и месяцы, группы товаров.
Область фильтра – используется, как ясно из названия, для фильтрации. Например, в самом отчете показаны продукты по регионам. Нужно ограничить сводную таблицу какой-то отраслью, определенным периодом или менеджером. Тогда в область фильтров помещают поле фильтрации и там уже в раскрывающемся списке выбирают нужное значение.
С помощью добавления и удаления полей в указанные области вы за считанные секунды сможете настроить любой срез ваших данных, какой пожелаете.
Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».
В результате мы получаем настоящую сводную таблицу.
На ее построение потребовалось буквально 5-10 секунд.
Группировка по строкам
Выполним группировку данных по строкам.
- Добавляем строчку над группой столбцов или под ней, в зависимости от того, как планируем выводить наименование и итоги. В новой ячейке вводим произвольное наименование группы, подходящее к ней по контексту.
- Выделяем строки, которые нужно сгруппировать, кроме итоговой строки. Переходим во вкладку «Данные».
- На ленте в блоке инструментов «Структура» кликаем по кнопке «Группировать».
- Открывается небольшое окно, в котором нужно дать ответ, что мы хотим сгруппировать – строки или столбцы. Ставим переключатель в позицию «Строки» и жмем на кнопку «OK».
На этом создание группы завершено. Для того, чтобы свернуть её достаточно нажать на знак «минус».
Чтобы заново развернуть группу, нужно нажать на знак «плюс».
Автоматическая структуризация
Ранее мы выполняли группировку вручную вручную. Есть и автоматический способ сделать то же самое. Но в этом случае эксель сам выбирает, какие данные объединить. Стоит отдать должное, он руководствуется логикой. Например, определяет заголовок и ячейки с суммированием, а строки между ними группирует и позволяет их сворачивать.
Благодаря этому таблица не занимает много места.
Правда, не всегда программа структурирует то, что нужно. Тогда приходиться прибегать к ручным настройкам. Однако если вы имеете дело с формулами, то автоматическая группировка вероятнее всего вам подойдет. Чтобы ее применить, команде «Группировать» выберите подпункт «Создание структуры» — готово.
Как отменить группировку, созданную вручную, вы узнали выше. Как это сделать после применения автоматического способа? В той же вкладке «Разгруппировать» нажмите «Очистить структуру».
Работа со сводными таблицами в Excel
Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.
Заменим выручку на прибыль.
Товары и области меняются местами также перетягиванием мыши.
Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.
На все про все ушло несколько секунд. Вот, как работать со сводными таблицами. Конечно, не все задачи столь тривиальные. Бывают и такие, что необходимо использовать более замысловатый способ агрегации, добавлять вычисляемые поля, условное форматирование и т.д. Но об этом в другой раз.
Автоматическая структуризация в Excel
Весь описанный выше метод – пример ручной группировки данных электронной таблицы. Но есть способ автоматизировать деление информации на группы. Правда, выборку для группировки будет создавать сама программа. Но во многих ситуациях Excel осуществляет структурирование информации правильно.
Самое большое преимущество автоматической структуризации данных в Эксель – экономия времени. Хотя нужно всегда быть готовым к тому, что придется некоторые правки вносить самостоятельно. К сожалению, роботы пока не научились читать мысли.
Когда оптимальнее всего использовать автоматическую группировку? Лучше всего она работает, когда пользователь работает с формулами. В этом случае ошибок, как правило, не возникает.
Отмена автоматической группировки осуществляется несколько иным образом. Нужно выбрать пункт «Разгруппировать», но нажать на меню «Очистить структуру». После этого таблица вернется в первоначальный вид.
Задаем название
Если вы еще не давали название блоку из выделенных строк, сделайте это сейчас. С заголовком вам будет легче находить нужную часть при повторных открытиях документа. Запишите наименование в нижней строке столбца. Вы скажете, что данное расположение неправильно. И будете правы. Такие настройки придуманы разработчиками, но мы сделаем по-своему:
- Выполните те же действия, что описаны в инструкции выше. Но не спешите применять команду «Группировать».
- Сначала нажмите на маленький квадратик рядом со словом «Структура».
- В появившемся окне «Расположение итоговых данных» снимите все галочки.
Теперь нам необходимо исправить заданную ранее систематизацию:
В поле «Структура» жмем «Разгруппировать». Снова появилось окно, так? Выбираем «Строки». И теперь, когда название переместилось вверх, повторяем разобранный вначале порядок действий.
Как сгруппировать строки в Excel?
Количество строк также велико, если рабочий лист содержит подробную информацию или данные. Как читатель отчетов, они не хотят видеть длинные строки; вместо этого они просто хотят видеть подробное представление, но в то же время, если им требуется какая-либо другая подробная информация, им нужна всего лишь кнопка, чтобы развернуть или свернуть представление по мере необходимости.
В этой статье мы покажем вам, как группировать строки в Excel с помощью разворачивания / свертывания, чтобы максимально использовать технику просмотра отчетов.
Как сгруппировать строки в Excel с помощью разворачивания / сворачивания?
Например, посмотрите на данные ниже.
В приведенной выше таблице данных у нас есть данные о продажах и затратах, относящиеся к городу и штату, но если вы посмотрите на первые две строки данных, у нас есть штат «Калифорния» и город «Лос-Анджелес», но продажи произошли в разные даты, поэтому каждый, кто читает отчеты, предпочитает читать продажи по штатам и по городу в одном столбце, поэтому, группируя строки, мы можем создать сводное представление в одну строку.
Выполните следующие шаги, чтобы сгруппировать строки в Excel.
Шаг 1: Сначала создайте промежуточный итог, как показано ниже.
Шаг 2: Теперь выберите первые строки штата (Калифорния), исключая промежуточные итоги.
Шаг 3: Перейдите на вкладку ДАННЫЕ и выберите опцию «Группа».
Шаг 4: Щелкните раскрывающийся список в Excel «Группа» и снова выберите «Группа».
Шаг 5: Теперь он спросит вас, группировать ли строки или столбцы. Поскольку мы группируем «Ряды,»Выберите строки и нажмите ОК.
Шаг 6: В тот момент, когда вы нажмете «ОК», вы увидите линию стыка с левой стороны.
Нажми на «МИНУС»Значок и увидеть волшебство.
Теперь мы могли видеть только общую сводку по городу «Калифорния», опять же, если вы хотите увидеть подробную сводку по городу, вы можете щелкнуть значок «ПЛЮС», чтобы развернуть представление.
Теперь снова выберите город «Колорадо»И нажмите«Группа»Вариант.
Теперь он будет сгруппирован для «Колорадо» штат.
Группировать с помощью сочетания клавиш
С помощью простого ярлыка в Excel мы можем очень легко группировать выбранные строки или столбцы. Сочетание клавиш для быстрой группировки данных: SHIFT + ALT + клавиша со стрелкой вправо.
Сначала выберите строки, которые нужно сгруппировать.
Теперь нажмите горячую клавишу SHIFT + ALT + клавиша со стрелкой вправо чтобы сгруппировать эти строки.
Выше мы видели, как сгруппировать данные и как сгруппировать строки с опцией развертывания и свертывания с помощью значков ПЛЮС и МИНУС.
Единственная проблема с вышеупомянутым методом заключается в том, что нам нужно делать это для каждого состояния индивидуально, поэтому это занимает много времени, когда существует много состояний. Какова будет ваша реакция, если я скажу, что вы можете группироваться одним щелчком мыши .
Удивительно, не правда ли? Используя «Авто контур,”Мы можем автоматически группировать данные.
Пример # 1 — Использование Auto Outline
Первое, что нам нужно сделать, это создать строки промежуточных итогов.
Теперь поместите курсор в диапазон данных. В раскрывающемся списке «Группа» мы можем увидеть еще один вариант, кроме «Группа», то есть «Авто контур. »
В тот момент, когда вы нажмете на эту опцию «Auto Outline», она сгруппирует все строки, которые находятся над строкой промежуточных итогов.
Как это круто . Очень круто, не правда ли ??
Пример # 2 — Использование промежуточных итогов
Если группировка строк для отдельного города является единственной проблемой, то даже перед группировкой строк возникает другая проблема, то есть добавление строк промежуточных итогов.
Когда есть сотни состояний, сложно создать строку промежуточных итогов для каждого состояния отдельно, поэтому мы можем использовать опцию «Промежуточный итог», чтобы быстро создать промежуточный итог для выбранного столбца.
Например, до создания промежуточного итога у нас были данные, подобные приведенным ниже.
На вкладке «Данные» у нас есть опция «Промежуточный итог«Рядом с»Группа»Вариант.
Щелкните этот параметр, выбрав любую из ячеек диапазона данных; сначала будет показан вариант ниже.
Сначала выберите столбец, в котором должен быть промежуточный итог; в этом примере нам нужен промежуточный итог для «Состояние,», Поэтому выберите то же самое в раскрывающемся списке« При каждом изменении ».
Затем нам нужно выбрать тип функции, поскольку мы добавляем все значения, чтобы выбрать «Сумма»В Excel.
Теперь выберите столбцы, которые нужно суммировать. Нам нужно резюме «Продажи и стоимостьСтолбцы, поэтому выбирайте то же самое. Нажмите «ОК».
Как сгруппировать строчки в Excel?
Количество строк также велико, если рабочий лист содержит подробную информацию либо данные. Как читатель отчетов, они не желают созидать длинноватые строчки; заместо этого они просто желают созидать подробное представление, но в то же время, если им требуется какая-либо иная подробная информация, им нужна всего только клавиша, чтоб развернуть либо свернуть представление при необходимости.
В данной нам статье мы покажем для вас, как группировать строчки в Excel при помощи разворачивания / свертывания, чтоб очень применять технику просмотра отчетов.
Как сгруппировать строчки в Excel при помощи разворачивания / сворачивания?
К примеру, поглядите на данные ниже.
В приведенной выше таблице данных у нас есть данные о продажах и издержек, относящиеся к городку и штату, но если вы поглядите на 1-ые две строчки данных, у нас есть штат «Калифорния» и город «Лос-Анджелес», но реализации произошли в различные даты, потому любой, кто читает отчеты, предпочитает читать реализации по штатам и по городку в одном столбце, потому, группируя строчки, мы можем сделать сводное представление в одну строчку.
Сделайте последующие шаги, чтоб сгруппировать строчки в Excel.
Шаг 1: Поначалу сделайте промежный результат, как показано ниже.
Шаг 2: Сейчас изберите 1-ые строчки штата (Калифорния), исключая промежные итоги.
Шаг 3: Перейдите на вкладку ДАННЫЕ и изберите опцию «Группа».
Шаг 4: Щелкните раскрывающийся перечень в Excel «Группа» и опять изберите «Группа».
Шаг 5: Сейчас он спросит вас, группировать ли строчки либо столбцы. Так как мы группируем «Ряды,»Изберите строчки и нажмите ОК.
Шаг 6: В тот момент, когда вы нажмете «ОК», вы увидите линию стыка с левой стороны.
Нажми на «МИНУС»Значок и узреть чудо.
Сейчас мы могли созидать лишь общую сводку по городку «Калифорния», снова же, если вы желаете узреть подробную сводку по городку, вы сможете щелкнуть значок «ПЛЮС», чтоб развернуть представление.
Сейчас опять изберите город «Колорадо»И нажмите«Группа»Вариант.
Сейчас он будет сгруппирован для «Колорадо» штат.
Группировать при помощи сочетания кнопок
При помощи обычного ярлычка в Excel мы можем весьма просто группировать избранные строчки либо столбцы. Сочетание кнопок для резвой группировки данных: SHIFT + ALT + кнопка со стрелкой на право.
Поначалу изберите строчки, которые необходимо сгруппировать.
Сейчас нажмите жаркую кнопку SHIFT + ALT + кнопка со стрелкой на право чтоб сгруппировать эти строчки.
Выше мы лицезрели, как сгруппировать данные и как сгруппировать строчки с опцией развертывания и свертывания при помощи значков ПЛЮС и МИНУС.
Единственная неувязка с вышеупомянутым способом заключается в том, что нам необходимо созодать это для всякого состояния персонально, потому это занимает много времени, когда существует много состояний. Какова будет ваша реакция, если я скажу, что вы сможете группироваться одним щелчком мыши .
Умопомрачительно, не правда ли? Используя «Авто контур,”Мы можем автоматом группировать данные.
Пример # 1 — Внедрение Auto Outline
1-ое, что нам необходимо создать, это сделать строчки промежных итогов.
Сейчас расположите курсор в спектр данных. В раскрывающемся перечне «Группа» мы можем узреть очередной вариант, не считая «Группа», другими словами «Авто контур. »
В тот момент, когда вы нажмете на эту опцию «Auto Outline», она сгруппирует все строчки, которые находятся над строчкой промежных итогов.
Как это круто . Весьма круто, не правда ли ??
Пример # 2 — Внедрение промежных итогов
Если группировка строк для отдельного городка является единственной неувязкой, то даже перед группировкой строк возникает иная неувязка, другими словами добавление строк промежных итогов.
Когда есть сотки состояний, трудно сделать строчку промежных итогов для всякого состояния раздельно, потому мы можем применять опцию «Промежный результат», чтоб стремительно сделать промежный результат для избранного столбца.
К примеру, до сотворения промежного итога у нас были данные, подобные приведенным ниже.
На вкладке «Данные» у нас есть функция «Промежный результат«Рядом с»Группа»Вариант.
Щелкните этот параметр, выбрав всякую из ячеек спектра данных; поначалу будет показан вариант ниже.
Поначалу изберите столбец, в котором должен быть промежный результат; в этом примере нам нужен промежный результат для «Состояние,», Потому изберите то же самое в раскрывающемся перечне« При любом изменении ».
Потом нам необходимо избрать тип функции, так как мы добавляем все значения, чтоб избрать «Сумма»В Excel.
Сейчас изберите столбцы, которые необходимо суммировать. Нам необходимо резюме «Реализации и стоимостьСтолбцы, потому выбирайте то же самое. Нажмите «ОК».
Как удалить пустые столбцы и строки в Excel 2013 – самый быстрый и простой способ
КонструкторМожно также щелкнуть правой., чтобы удалить всеMasyanjaMihasGrom Set uz = а оставшиеся данные(Удалить строки с кнопкой мыши и 6, 7 и новую строку между в случае, еслиУдалить дубликаты удалить. добавления столбцов при от последнего столбца.>
кнопкой мыши выделеннуюВставляйте и удаляйте строки, столбцы,: я думала всё: vikttur, этот метод
.FindNext() Loop While расположились вплотную друг листа). выбирая в контекстном 8. строками 7 и диапазон ячеек или.Можно просто выделить одну вставке значений из
В приведенном ниже примереРазмер таблицы строку и выбрать столбцы и ячейкикроме просто, а там не работает Not uz Is к другу.Все пустые строки будут меню команду
Нажмите команду Удалить, которая 8, выделите строку столбец таблицы содержитВ диалоговом окне или несколько ячеек диапазона C7:C9 в добавления строки при.
команду для оптимального размещениявыделенных.
уже пытались удалитьа для пользователя Nothing End IfЭтот метод поможет быстро удалены, а остальныеDelete находится в группе 8. хотя бы однуУдалить дубликаты в строках или
первый столбец справа вводе значения вВыделите весь диапазон ячеек,
Вставить данных на листе.С помощью контекстного меню и пару столбцов куда дальше а End Withкак-то так очистить любую таблицу окажутся смежными.(Удалить), но для команд Ячейки наНажмите команду Вставить, которая
пустую ячейку.в группе столбцах таблицы, которые от таблицы (столбец ячейку A4 таблица который нужно включитьилиПримечание:редактора запросов оказалось скрытыми, поэтому не просто макросы. примерно
Excel от пустыхТочно так же можно большой таблицы это вкладке Главная. находится в группеВыделите пустые строки вСтолбцы требуется удалить.
C) она расширяется расширяется за счет в таблицу, начинаяУдалить В Microsoft Excel установленыВыберите столбец, который вы и не удалялосьvikttur
Finswimmer строк и столбцов удалить пустые столбцы. займёт уйму времени!Выделенные строки будут удалены, команд Ячейки на таблице и нажмитевыберите столбцы, содержащиеНа вкладке за счет вставленных нее, а также
с верхней левой. следующие ограничения на хотите удалить. Чтобы нормально. но при: У меня работает.
: Sub УдалитьПустыеСтолбцы() LastColumn и значительно сэкономит Выделите область, в Существует более простой а те, что вкладке Главная.
клавиши CTRL+- (дефис). дубликаты, которые требуетсяГлавная данных, а столбцу смежной ячейки в ячейки.Выделите одну или несколько количество строк и выделить несколько столбцов, копировании они вылазятКак можно помогать, = ActiveSheet.UsedRange.Column - время. которой необходимо удалить и быстрый способ расположены ниже, сместятсяНовая строка появится вышеТаким же образом можно удалить.в группе присваивается заголовок «Кв3». столбце B.В приведенном ниже примере ячеек. Щелкните правой столбцов: 16 384 столбца щелкните их, удерживая
эти столбики и когда ни объяснений, 1 + ActiveSheet.UsedRange.Columns.CountПримечание переводчика:
пустые столбцы
Важно,. office-guru.ru>. office-guru.ru>
office-guru.ru>
Как скрывать и группировать столбцы в Excel
Из этого руководства Вы узнаете и сможете научиться скрывать столбцы в Excel 2010-2013. Вы увидите, как работает стандартный функционал Excel для скрытия столбцов, а также научитесь группировать и разгруппировывать столбцы при помощи инструмента «Группировка».
Уметь скрывать столбцы в Excel очень полезно. Может быть множество причин не отображать на экране какую-то часть таблицы (листа):
- Необходимо сравнить два или более столбцов, но их разделяют несколько других столбцов. К примеру, Вы хотели бы сравнить столбцы A и Y, а для этого удобнее расположить их рядом. Кстати, в дополнение к этой теме, Вам может быть интересна статья Как закрепить области в Excel .
- Есть несколько вспомогательных столбцов с промежуточными расчётами или формулами, которые могут сбить с толку других пользователей.
- Вы хотели бы скрыть от посторонних глаз или защитить от редактирования некоторые важные формулы или информацию личного характера.
Читайте дальше, и вы узнаете, как Excel позволяет быстро и легко скрыть ненужные столбцы. Кроме того, из этой статьи Вы узнаете интересный способ скрыть столбцы с помощью инструмента «Группировка», который позволяет скрывать и отображать скрытые столбцы в одно действие.
Устранение ошибок
Иногда, когда вы пытаетесь сгруппировать числа или даты, вы получаете сообщение об ошибке, в котором говорится, что вы не можете выполнить это действие.
Это может произойти по ряду причин.
Во-первых, если вы пытаетесь сгруппировать какие-то показатели в свою собственную пользовательскую группу, вы должны выбрать как минимум две записи для ее создания. Вы не можете создать группу только из одной записи.
Если в поле, содержащем даты или числа, есть пустые ячейки или ячейки с текстом, то из-за этого вы получите сообщение об ошибке.
Я думаю, вы понимаете, что Excel не может группировать числа и текст, ведь нельзя сложить букву и число.
В каждом случае проверьте исходные данные и устраните проблему, а затем выполните обновление сводной таблицы перед повторной попыткой.
Возможно, вам также будет полезно:
Группировка по строчкам
Выполним группировку данных по строчкам.
- Добавляем строку над группой столбцов либо под ней, в зависимости от того, как планируем выводить наименование и итоги. В новейшей ячейке вводим случайное наименование группы, подходящее к ней по контексту.
Выделяем строчки, которые необходимо сгруппировать, не считая итоговой строчки. Перебегаем во вкладку «Данные».
На ленте в блоке инструментов «Структура» кликаем по кнопочке «Группировать».
На этом создание группы завершено. Для того, чтоб свернуть её довольно надавить на символ «минус».
Чтоб поновой развернуть группу, необходимо надавить на символ «плюс».
Создание вложенных групп
В Эксель можно создавать не только лишь группы первого порядка, да и вложенные. Для этого, необходимо в развернутом состоянии материнской группы выделить в ней определенные ячейки, которые вы собираетесь сгруппировать раздельно. Потом следует провести одну из тех процедур, какие были описаны выше, в зависимости от того, со столбцами вы работаете либо со строчками.
Опосля этого вложенная группа будет готова. Можно создавать огромное количество схожих вложений. Навигацию меж ними просто проводить, перемещаясь по цифрам, размещенным слева либо сверху листа в зависимости от того, что сгруппировано строчки либо столбцы.