Уровни группировки в excel

Лучшие IT-решения для бизнеса

Nav view search

Navigation

Search

Программирование, доработки консультации по 1С

Как мы работаем

1. Обсуждаем проблему по телефону. При наличии удаленного доступа — показываете на экране вашего компьютера.

2. Мы оцениваем работу в рублях, если проект большой, если нет — примерное кол-во часов.

3. Мы выполняем работу.

4. Вы принимаете работу в вашей программе, если есть недочеты мы их исправляем.

5. Мы выставляем счет, вы оплачиваете.

Стоимость работ

1. Все работы делятся на 3 категории: консультация, обновление типовой конфигурации, разработка или программирование нового отчета, обработки, кнопки и т.п.

2. Стоимость работ по категориям:

3. На работы более 10 часов предварительно составляется техническое задание с описанием и стоимостью работ. Работы начинаются после согласования ТЗ с вами.

Техническая поддержка

1. Если вы обнаруживаете какие то ошибки, в ранее принятых работах, в течении 3х месяцев, мы исправляем их бесплатно.

2. Постоянным клиентам любые недочеты, допущенные в наших работах, исправляем бесплатно в течении года.

Программы для управления вашим бизнесом.

Купить 1С:Предприятие

Мы являемся официальным дилером фирмы 1С, вы можете приобрести у нас различные программные продукты и лицензии. Кроме покупки «коробки» мы поможем вам настроить программу, проконсультируем и сделаем базовые настройки.

• Бухгалтерский учет
• Автоматизация магазина
• Оптовые продажи
• Помощь в установке и первоначальной настройке включена в пакет!
• Тонкая настройка конфигураций под нужды заказчика, разработка новых модулей при отсутствии необходимых функций в стандартной конфигурации.

Предоставление сервера.

Моментальная настройка сервер + 1С.

Нет сервера? Не беда, подберем и быстро настроим сервер в «облаке». За небольшую плату вы получаете очень надежное решение.

• Доступность 247
• Нет необходимости держать собственного системного администратора (экономия покроет расходы на ваш сервер).
• Быстрая настройка и установка 1С на сервер, через 3 дня у вас уже будет полностью рабочая система.
• В любой момент можно переехать на локальный сервер, если решение не устроит.

SMS из вашей 1С

Хотите чтобы клиенты во время узнавали об акциях, скидках? Клиенты не возвращаются? Настройте отправку SMS прямо из 1С!

Наша компания сможет в короткие сроки настроить отправку SMS Вашим клиентам напрямую из 1С. Примеры событий которые можно автоматизировать:

• Благодарность за покупку и начисление бонусов сразу после очередной покупки.
• Начисление бонусов на карту в подарок на день рожденияк другому знаменательному или праздничному дню.
• Извещение о поступлении товара на склад.
• Истечение срока подарочных бонусов.
• Извещение о поступлении предоплаты и резервирования товара.
• Адрес с уточнениями проезда к магазинуофису, номера телефонов.
• И т.п.

Настройку в 1С можно произвести силами наших специалистов или своих сотрудников. Ознакомится с тарифами можно на странице SMS-тарифов.

• Гарантия доставки SMS, деньги снимаются только за доставленные SMS.
• Отдельная тарификация каждого SMS.
• Пополнение баланса разными способами.
• Просмотр истории всех отправленных SMS в любой момент.
• Имя отправителя вместо цифрового номера в телефоне получателя сообщения.

Многоуровневая группировка строк

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

Предположим, что мы работаем вот с такой сложной многоуровневой таблицей с данными:

Обратите внимание на то, что в таблице ясно и четко прослеживаются вложенные друг в друга уровни данных. Такую структуру часто можно увидеть в:

• бюджетировании — статьи доходов/расходов группируются в блоки (cost centers) или по географическому признаку (страна-край-город)
• управлении проектами — этапы проектов разбиты обычно на более мелкие подзадачи и действия
• строительных сметах — похожим образом обычно расписываются расчеты расхода материалов и их стоимости при строительстве
• и т.д. — дальше придумайте сами.

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

Нажмите сочетание клавиш ALT+F11, чтобы открыть редактор Visual Basic. В нем выберите в меню команду Insert — Module, чтобы вставить новый модуль и скопируйте туда текст макроса:

При необходимости, текст можно слегка подкорректировать под ваши особенности, а именно изменить:

• FIRST_ROW — номер первой строки списка, начиная с которой пойдет группировка. Если у вас шапка не из одной строки или над таблицей есть данные — меняйте.
• FIRST_COLUMN — номер первого столбца списка, с которого начинается анализ и группировка. Если слева от вашей таблицы есть еще колонки, то эту константу также нужно изменить.
• NUMBER_OF_LEVELS — количество уровней (столбцов) для анализа. В приведенном выше примере мы хотим проанализировать три первых столбца, поэтому значение этой константы =3

Важно! Макрос предполагает, что:

• Уровни заполняются по порядку, т.е., например, уровень 3 не может быть написан, если ему не предшествовал уровень 2.
• В первом столбце списка в последней строке должно быть слово Конец, которое необходимо, чтобы макрос понял, где заканчивается список и пора остановиться:

Чтобы запустить добавленный макрос для списка на текущем листе, нажмите сочетание клавиш ALT+F8, выберите в списке наш макрос Multilevel_Group и нажмите кнопку Выполнить (Run) .

Многоуровневая группировка строк

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

Предположим, что мы работаем вот с такой сложной многоуровневой таблицей с данными:

Обратите внимание на то, что в таблице ясно и четко прослеживаются вложенные друг в друга уровни данных. Такую структуру часто можно увидеть в:

• бюджетировании — статьи доходов/расходов группируются в блоки (cost centers) или по географическому признаку (страна-край-город)
• управлении проектами — этапы проектов разбиты обычно на более мелкие подзадачи и действия
• строительных сметах — похожим образом обычно расписываются расчеты расхода материалов и их стоимости при строительстве
• и т.д. — дальше придумайте сами.

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

Нажмите сочетание клавиш ALT+F11, чтобы открыть редактор Visual Basic. В нем выберите в меню команду Insert — Module, чтобы вставить новый модуль и скопируйте туда текст макроса:

При необходимости, текст можно слегка подкорректировать под ваши особенности, а именно изменить:

• FIRST_ROW — номер первой строки списка, начиная с которой пойдет группировка. Если у вас шапка не из одной строки или над таблицей есть данные — меняйте.
• FIRST_COLUMN — номер первого столбца списка, с которого начинается анализ и группировка. Если слева от вашей таблицы есть еще колонки, то эту константу также нужно изменить.
• NUMBER_OF_LEVELS — количество уровней (столбцов) для анализа. В приведенном выше примере мы хотим проанализировать три первых столбца, поэтому значение этой константы =3

Важно! Макрос предполагает, что:

• Уровни заполняются по порядку, т.е., например, уровень 3 не может быть написан, если ему не предшествовал уровень 2.
• В первом столбце списка в последней строке должно быть слово Конец, которое необходимо, чтобы макрос понял, где заканчивается список и пора остановиться:

Чтобы запустить добавленный макрос для списка на текущем листе, нажмите сочетание клавиш ALT+F8, выберите в списке наш макрос Multilevel_Group и нажмите кнопку Выполнить (Run) .

Группировка данных в сводной таблице

Группировка в сводных таблицах (831,4 KiB, 1 070 скачиваний)

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

И необходимо проанализировать данные в разрезе месяца или квартала или на основании групп артикулов с определенным шагом.

ГРУППИРОВКА ДАТЫ И ВРЕМЕНИ
Если необходимо просмотреть суммарную стоимость предложений по кварталам, то пригодиться группировка по датам.

1. Выделить любую ячейку нужного поля из области строк или столбцов и щелкнуть правой кнопкой мыши;
2. Выбрать из контекстного меню пункт Группировать (Group) ;
3. В поле Начиная с (Starting at) ввести начальную дату для группы;
4. В поле по (Ending at) ввести конечную дату для группы;
5. В поле с шагом (By) выбрать диапазон группировки: секунды, минуты, часы, дни, месяцы, кварталы, годы (seconds, minutes, hours, days, months, quarters, years) ;
6. Нажать OK
   

ГРУППИРОВКА ЧИСЛОВЫХ ПОЛЕЙ
Может пригодиться для группировки по занятым местам или по ценам предложений. Например, можно отобрать все предложения от 110 000р до 130 000р с шагом 10 000р. В данном случае получим таблицу, в которой будут интересующие предложения из указанного диапазона, разбитые с нужным шагом. Если какие значения превышают указанную сумму(130 000р), то будет отдельная группа: >130000, если меньше:

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

И вроде бы в ячейках даты/числа и все равно. В данном случае следует проверить — а действительно ли числа это числа, а даты — даты? Потому как бывает и так, что выглядят в ячейках данные как числа или даты, а на деле это просто текст. В большинстве случаев Excel подсвечивает такие ячейки зелеными треугольничками в левом верхнем углу:

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

После этого обязательно необходимо перейти в сводную таблицу и обновить её(выделить любую ячейку сводной таблицы→Правая кнопка мыши→Обновить (Refresh) или вкладка Данные (Data) →Обновить все (Refresh all) →Обновить (Refresh) ). Вполне возможно, что это действие придется повторить еще один-два раза.

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

Если после этого группировка все равно отказывается работать — значит где-то еще есть числа/даты, записанные как текст. Но они могут быть не подсвечены зеленым треугольником. Такое поведение часто наблюдается в файлах, выгруженных из 1С или иных программ. Часто побеждают это очень упорным трудом: выделяют ячейку, жмут F2(чтобы войти в режим редактирования ячейки) и Enter. Тогда Excel преобразует дату/число в настоящие дату/число. Но если таких ячеек хотя бы 100 — это уже не на пару минут рутины. Благо, все это можно сделать за пару секунд. Чтобы быстро преобразовать ячейки с датами/числами, записанными как текст в реальные даты/число необходимо:

• скопировать любую пустую ячейку на листе
• выделить все ячейки с датами/числами
• правая кнопка мыши -Специальная вставка (Paste Special) -в окне выбрать Значения (Values) , операция — Сложить (Multiply)
• ОК

Excel автоматом преобразует даты и числа в нормальные данные. Возможно, придется заново задать формат датам — но это уже совершенно не сложно: правая кнопка мыши —Формат ячеек (Format Cells) -Дата (Date) .
Про другие возможности Специальной вставки можно прочитать в этой статье: Как быстро умножить/разделить/сложить/вычесть из множества ячеек одно и то же число?

ГРУППИРОВКА ТЕКСТОВЫХ ПОЛЕЙ ИЛИ ОТДЕЛЬНЫХ ЭЛЕМЕНТОВ

1. Выделить ячейку из области строк или столбцов с одним из элементов поля для группировки;
2. Удерживая CTRL или SHIFT выделить другие элементы (ячейки) этого поля;
3. Щелкнуть правой кнопкой по любой выделенной ячейке и выбрать из контекстного меню пункт Группировать (Group) или на вкладке Параметры (Options) в группе Группировать (Group) нажать кнопку Группа по выделенному (Group Selection);
   
   
4. При необходимости задать свое имя группе

В полях с уровнями можно группировать только элементы, имеющие одинаковые подуровни. Например, если в поле есть два уровня «Страна» и «Город», нельзя сгруппировать города из разных стран.

ПЕРЕИМЕНОВАНИЕ ГРУППЫ ПО УМОЛЧАНИЮ
При группировке элементов Excel задает имена групп по умолчанию, например Группа1 (Group1) для выбранных элементов или Кв-л1 (Qtr1) для квартала 1(если работаем с датами). Задать группе более понятное имя совсем несложно:

1. Выделить имя группы;
2. Нажать клавишу F2;
3. Ввести новое имя группы.

1. Выделить группу элементов, которые требуется разгруппировать;
2. На вкладке Параметры (Options) в группе Группировать (Group) нажать кнопку Разгруппировать (Ungroup) (или щелкнуть правой кнопкой мыши и выбрать из контекстного меню пункт Разгруппировать (Ungroup) ).

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

1. Для источников данных OLAP (Online Analytical Processing), не поддерживающих инструкцию CREATE SESSION CUBE, группировка элементов невозможна.
2. При наличии одного или нескольких сгруппированных элементов использовать команду Преобразование в формулы (Параметры— Сервис— Средства OLAP) невозможно. Перед использованием этой команды необходимо сначала удалить сгруппированные элементы.
3. Для быстрой работы c группами данных надо выделить ячейки в области названий строк или столбцов сводной таблицы, щелкнуть правой кнопкой мыши на любой из выделенных ячеек и выбрать Развернуть/Cвернуть (Expand/Collapse)

Так же см.:
[[Общие сведения о сводных таблицах]]
[[Сводная таблица из нескольких листов]]

Статья помогла? Поделись ссылкой с друзьями!

Поиск по меткам

Можно ли добавить дополнительные «Промежуточные итоги» для сводной таблицы, содержащей большую структуру — столбцов.
Так, чтобы эти дополнительные итоги — показывали итоги по каждой структуре.
Например, есть сводная таблица по месяцам продаж (строки) по Магазинам, Маркам, Цветам товара (столбцы).

Хотелось бы увидеть в столбцах: Общие итоги (+), Итоги по Магазину (+), Итоги по Марке (- не дает, только внутри каждого магазина), Итоги по Цвету (+), Итоги по Магазину-Марке(+), Итоги по Марке-Цвету(- не дает), Итоги по Магазину -Цвету (не дает).

Итого 7 итогов: 4 могу сделать, а 3 не получается ( в одной таблице). Приходится делать надстройку поверх Сводной.

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме — добро пожаловать на Форум

Способы группировки данных в электронной таблице Excel.

Лабораторные работы

Содержание семинарских занятий и лабораторных работ по курсу

Лабораторная работа №1-2

Вопросы для отчета по лабораторной работе

1. Перечислите основные источники статистической информации.

2. Укажите особенности организации статистического наблюдения.

3. Укажите этапы проведения сбора статистической информации.

4. Что такое группировка?

5. Опишите основные виды группировки.

6. Опишите подробнее основные виды аналитической группировки (деление на интервалы).

7. Что такое статистическая сводка.

Доступные и недоступные источники статистической информации. Особенности организации статистического наблюдения.

Способы группировки данных в электронной таблице Excel.

Одним из основных наиболее распространенных методов об­работки и анализа первичной статистической информации явля­ется группировка.

Под группировкойпонимают расчленение единиц статистиче­ской совокупности на группы, однородные в каком-либо сущест­венном отношении, и характеристику таких групп системой по­казателей в целях выделения типов явлений, изучения структуры и взаимосвязей. Следовательно, с помощью группировок решаются три задачи:

§ разделение всей совокупности на качественно однородные группы — выделение социально-экономических типов. Эти груп­пировки называются типологическими(например, группировки хозяйственных объектов по формам собственности, населения по общественным группам и др.);

§ характеристика структуры явления и структурных сдвигов. Эти группировки называются структурными(например, опреде­ление значения каждого вида транспорта в транспортном балансе страны, изучение состава населения по полу, возрасту и другим признакам и т. д.);

§ изучение взаимосвязей между отдельными признаками изу­чаемого явления.

Такие группировки называются аналитическими(например, группировка предприятий определенной отрасли экономики по уровню производительности труда для выявления ее влияния на себестоимость продукции).

Признак, на основе которого производится подразделение единиц наблюдения на группы, называется группировочным при­знакомили основанием группировки.Группировка может выпол­няться по одному признаку (простая группировка) и по несколь­ким признакам <комбинированная группировка).

Группировочные признаки могут быть атрибутивными и ко­личественными. Атрибутивные признакирегистрируются в виде текстовой записи (например, профессия рабочих, социальная группа населения). Количественные признакиимеют цифровое выражение (стаж работы, размер дохода).

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

При группировкепо количественному признакучисло групп оп­ределяется в зависимости от характера изменения признака и за­дач исследования. Если количественный признак меняется пре­рывно (дискретно), т. е. может принимать только некоторые — ча­ще целые значения (например, тарифный разряд рабочих), то число групп должно соответствовать количеству значений при­знака.

При непрерывном изменении признак принимает любые зна­чения (например, стаж работы или возраст рабочих), поэтому группы ограничиваются значениями признака в интервале «от -до». Интерваломназывается разница между максимальным и ми­нимальным значениями признака в каждой группе. На практике используются три вида интервалов: равные, неравные (постепен­но увеличивающиеся) и специализированные.

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

Величина равного интервала исчисляется по формуле:

где х_max, х_min — соответственно наибольшее и наименьшее значения при­знака в изучаемой совокупности; т — принятое число групп.

Для расчета величины интервала по этой формуле необходи­мо заранее установить число групп (при числе наблюдений более 200 используют 10-15 групп).

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

где п— число наблюдений.

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

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

Специализированные интервалыиспользуются в типологичес­ких группировках; границы устанавливаются там, где намечается переход от одного качества к другому. Наметить точки перехода можно только на основе теоретического анализа, используя для выделения типов не отдельные, изолированные признаки, а сово­купность признаков, характеризующих различные стороны изу­чаемого явления.

Интервалы группировки могут быть закрытыми и открытыми.

Иногда имеющуюся группировку необходимо несколько из­менить: объединить ранее выделенные относительно мелкие группы в небольшое число более крупных, типичных групп или изменить границы прежних групп, с тем чтобы сделать группи­ровку сопоставимой с другими. Такая переработка результатов первичной группировки называется перегруппировкойили вто­ричной группировкой.

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

Результаты группировки и сводки материалов оформляются в виде статистических таблиц.

В статистической таблице выделяются два элемента:

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

• сказуемое— цифры, при помощи которых характеризуются выделенные в подлежащем единицы или группы.

Над таблицей помещается заголовок, отражающий в сжатой форме ее основное содержание, время и место, к которым отно­сятся изложенные в таблице данные.

Задание 1. Объем инвестиций в основной капитал характеризуется в России следующими данными (в фактически действовавших це­нах, млрд руб.): 1998 г.– 402,4; 1999 г.– 565,6; в том числе в отрасли: а) производящие товары — 1998 г. — 163,8; 1999 г. — 269,4; б) оказывающие рыночные и нерыночные услуги — 1998 г. -238,6; 1999 г.-296,2.

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

Решение:в данной задаче приводится относительно небольшое количество данных, поэтому группировку проще сделать вручную. Итак, представим имеющиеся у нас данные в виде следующей таблице и произведем необходимые расчеты (см. рис. 1.).

Обратите внимание, что при решении задачи удобнее использовать при необходимости абсолютность и относительность адресов ячеек. В столбцах, где измерения проходят в % не забудьте установить соответствующий формат (Формат/ Ячейки…) с необходимым количеством знаков после запятой.

Задание 2. Имеются следующие данные по заработной плате водителей за сентябрь: