Функция группировки в excel

Функция группировки в excel

Сводные таблицы (Группировка)

Ценность данных определяется не их объемом, а возможностью их преобразования в значимую — релевантную информацию (релевантный от англ, relevant — существенный, уместный, относящийся к делу). Согласно современным представлениям, наиболее удобным способом хранения, организации и поиска информации являются базы данных (БД) (рис. 3.1).

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

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

Рис. 3.1. База данных

Задание 1. Используя данные Приложения 1, создадим Лист 1 с исходными данными для группировки (табл. 3.8). Сохраним рабочую книгу под названием «Группировка».

Расчетные показатели дня группировки

Важной задачей является задача разбиения на группы, удовлетворяющие определенным критериям. Она может быть решена в Excel различными способами: команда Данные — Фильтр; команда Данные — Группировать и т.д. Важным средством Excel при решении задачи разбиения на группы являются сводные таблицы (команда Вставка — Сводная таблица).

Рассмотрим процесс построения группировки по шагам.

Шаг 1. Выберем команду Вставка — Сводная таблица — ОК. Если курсор мыши находится в поле таблицы, то диапазон задается автоматически (рис. 3.2).

Рис. 3.2. Создание сводной таблицы

Шаг 2. Для группировки исходных данных по «признаку энергообеспеченность на 100 га сельхозугодий» предварительно заполним макет сводной таблицы (рис. 3.3, 3.4).

Рис. 3.3. Макет сводной таблицы

Рис. 3.4. Пример заполнения полей сводной таблицы

Шаг 3. Для группировки щелкнем правой кнопкой мыши по полю «Энергообеспеченность» и выберем из контекстного меню команду Группировать с шагом 103,62 (для разбиения на три группы) (рис. 3.5).

Рис. 3.5. Окно мастера «Группирование»

В результате получим группировку по указанному признаку (рис. 3.6).

Рис. 3.6. Сводная таблица после группировки по полю «Энергообеспеченность»

Шаг 4. Для вторичной группировки перетащим в поле «Название строк» показатель «Фондообеспеченность», сделаем для него группировку с шагом 1063,45 (рис. 3.7).

Рис. 3.7. Детализация списка полей сводной таблицы для вторичной группировки

Полученную сводную таблицу скопируем на новый лист («Группи- ровочная таблица»), используя команду Правка — Специальная вставкаЗначения, чтобы иметь возможность редактирования полей и итогов (рис. 3.8). Достроим сводную таблицу, введя показатели, которые необходимо рассчитать на основании итогов сводной таблицы. Введем соответствующие формулы. С помощью контекстных меню строк и столбцов скроем строки и столбцы с лишними расчетами, отформатируем. В результате получим комбинационную группировку влияния энергообеспеченности и фондообеспеченности сельхозугодий на эффективность производства (рис. 3.9).

Читайте также:  Что такое слоу моу

Вывод. Расчеты показали, что с ростом энергообеспеченности эффективность сельскохозяйственного производства повышается. Так, если в 1-й группе хозяйств со средней энергообеспеченностью 199,9 л.с. в исследуемом году в расчете на 100 га сельскохозяйственных угодий было получено 675,9 тыс. руб. валовой продукции, а в расчете на одного работника — 142,8 тыс. руб., то в 3-й группе хозяйств со средней энергообеспеченностью 407,3 л.с. стоимость валовой продукции в расчете на 100 га и одного работника превысила показатели 1-й группы соответственно на 622,5 и 48,8 тыс. руб.

Рис. 3.8. Часть сводной таблицы после вторичной группировки

Рис. 3.9. Комбинационная группировка

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

В среднем по всей исследуемой совокупности сельскохозяйственных организаций энергообеспеченность составила 290,6 л.с., а фондообеспеченность — 1207,9 тыс. руб. Средний объем производства валовой продукции в расчете на 100 га и одного работника достиг соответственно 876,6 и 157,7 тыс. руб.

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

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

Мы рассмотрим все указанные способы для того, чтобы вы определили наиболее подходящий для себя.

Автоматическая группировка в Excel

Для начала необходимо открыть файл, с которым вы будете работать, а после перейти на вкладку «Данные». В новых версиях Microsoft Excel она находится в одном из разделов ленты, а в старых – на панели управления.

Алгоритм автоматического структурирования:

  • Обратите внимание на таблицу: участок или участки сгруппировались и теперь отмечены полоской вдоль левой стороны. Щелкнув на кнопку [-] – вы сможете свернуть группу сведений и в итоге в зоне обзора останется лишь самое важное.
  • Если Excel неправильно принял ваш запрос, вы можете очистить структуру. Для этого следует выполнить команду «Разгруппировать/Очистить структуру».
  • Ручная группировка в Excel

    • Выделите ячейки, которые следует свернуть. После сворачивания информации, видимой будет оставаться только шапка и результат.
    • Выполните «Данные/Группировать/Группировать»
    • В ответ на появившийся запрос определите способ выравнивания: ряды или колонки.

    Советы и рекомендации при работе с группировкой в Эксель

    Если авто-структура не работает должным образом, выберите обычный способ: он может оказаться проще и больше соответствовать информации, содержащейся в таблице Excel. Команда не может использоваться на листах с совместным доступом, при сохранении в htm/html или при защите документа, в последнем случае пользователь-получатель не сможет сворачивать/разворачивать ряды.

    Читайте также:  Сделать фотошоп своей фотографии

    Видеоинструкция

    Хочу облегчить жизнь тем, кто работает с большими таблицами. Для этого мы сейчас разберемся с понятием группировка строк в excel. Благодаря ему ваши данные примут структурированный вид, вы сможете сворачивать ненужную в настоящий момент информацию, а потом быстро ее находить. Удобно, правда?

    Инструкция

    Открываем файл excel и приступаем к группировке:

    • Выделите нужные строки;
    • Откройте вкладку «Данные» в меню сверху;
    • Под ним в поле «Структура» найдите команду «Группировать»;

    • В появившемся окошке поставьте галочку напротив строк;

    Ничего сложного, не так ли?
    Объединенные экселем области имеют рядом и/или сверху полоску. Под ней находится знак минуса. Нажмите на него. Сгруппированные ячейки свернулись. Чтобы снова их увидеть, нажмите на плюсик сверху.

    Задаем название

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

    • Выполните те же действия, что описаны в инструкции выше. Но не спешите применять команду «Группировать».
    • Сначала нажмите на маленький квадратик рядом со словом «Структура».
    • В появившемся окне «Расположение итоговых данных» снимите все галочки.

    Теперь нам необходимо исправить заданную ранее систематизацию:

    • В поле «Структура» жмем «Разгруппировать». Снова появилось окно, так? Выбираем «Строки». И теперь, когда название переместилось вверх, повторяем разобранный вначале порядок действий.

    Автоматическая структуризация

    Ранее мы выполняли группировку вручную вручную. Есть и автоматический способ сделать то же самое. Но в этом случае эксель сам выбирает, какие данные объединить. Стоит отдать должное, он руководствуется логикой. Например, определяет заголовок и ячейки с суммированием, а строки между ними группирует и позволяет их сворачивать.

    Благодаря этому таблица не занимает много места.

    Правда, не всегда программа структурирует то, что нужно. Тогда приходиться прибегать к ручным настройкам. Однако если вы имеете дело с формулами, то автоматическая группировка вероятнее всего вам подойдет. Чтобы ее применить, команде «Группировать» выберите подпункт «Создание структуры» — готово.

    Как отменить группировку, созданную вручную, вы узнали выше. Как это сделать после применения автоматического способа? В той же вкладке «Разгруппировать» нажмите «Очистить структуру».

    Как сортировать данные таблицы?

    Максимально оптимизировать вашу таблицу поможет такая функция экселя как сортировка данных. Ее можно производить по разным признакам. Я расскажу об основных моментах, которые помогут вам в работе.

    Читайте также:  Что лучше аккорд 9 или инфинити м25

    Цветовое деление

    Вы выделяли некоторые строки, ячейки или текст в них другим цветом? Или только хотели бы так сделать? Тогда этот способ поможет вам быстро их сгруппировать:

    • Во вкладке «Данные» переходим к полю «Сортировка и фильтр».
    • В зависимости от версии excel нужная нам команда может называться просто «Сортировка» или «Настраиваемая». После нажатия на нее должно появиться новое окно.

    • В разделе «Столбец» в группе «Сортировать по» выберите необходимый столбец.
    • В разделе сортировки кликните, по какому условию необходимо выполнить деление. Вам нужно сгруппировать по цвету ячейки? Выбирайте этот пункт.
    • Для определения цвета в разделе «Порядок» кликните на стрелочку. Рядом вы можете скомандовать, куда переместить отсортированные данные. Если нажмете «Сверху», они сместятся наверх по столбцу, «Влево» — по строке.

    Примечание: чтобы выполнить те же действия с другими значениями и цветами, в диалоговом окне сортировки нажмите «Добавить уровень». После того, как вы сохраните файл, можно выполнить еще раз такое же объединение. Необходимо в тот же поле нажать кнопку «Применить повторно».

    Объединение значений

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

    В группе «Порядок» есть пункт «Настраиваемый список», нажав на который вы можете воспользоваться предложением сортировки по спискам экселя или настроить собственный. Таким способом можно объединить данные по дням недели, с одинаковыми значениями и пр.

    Упрощаем большую таблицу

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

    • Проверьте, чтобы в начале всех столбцов, которые мы будем объединять, был заголовок, что все они содержат информацию одинакового типа, и нет пустых мест.
    • Чтобы столбцы имели опрятный вид, в поле сортировки выберите команду «Сортировать от А до Я» или наоборот.
    • Вставьте итоговые строки, то есть, те, что имеют формулы и ссылаются на объединяемые нами ячейки. Сделать это можно с помощью команды «Промежуточные итоги», которая находится в том же поле, что и кнопка «Группировать».
    • Выполните группировку всех столбцов, как мы делали раньше. Таким образом, у вас получится гораздо больше плюсиков и минусов с левой стороны. Вы можете также переходить от одного уровня к другому путем нажатия вкладок с цифрами в той же панели сверху.

    На этом всё, друзья.

    Если же вы хотите изучить Excel поглубже, то рекомендую приобрести данный видео курс: « Неизвестный Excel ».

    Ссылка на основную публикацию
    Формат записи видео mov
    MOV против MP4 Существует много форматов файлов, которые можно использовать для хранения ваших видео в зависимости от ваших потребностей. MOV...
    Усилитель pioneer a 405r
    Вероятно, госпожа Симметрия владела умами дизайнеров Pioneer, когда они разрабатывали внешний вид этой серии усилителей. Но, расположив в центре регулятор...
    Усилитель амфитон у 002 характеристики
    усилитель Амфитон -002 . Доработан по статье Жуковского '' Оверклоккинг Амфитона . '' и по рекомендациям Вова мастер звук. T.е....
    Формат ммгг как писать
    Сбербанк Онлайн позволяет проводить различные платежи прямо из дома с любого устройства, имеющего доступ в Интернет. Это существенно экономит время...
    Adblock detector