Как сделать многоуровневую таблицу в excel?

MulTEx » 9 Июль 2016       Дмитрий       7019 просмотров

Вызов команды:
MulTEx -группа Специальные —Работа со сводными —Подготовить таблицу — строки

Понять принцип работы и основные параметры настроек поможет видеоинструкция к команде:

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

Ну и конечно же, это можно сделать при помощи команды MulTEx Подготовить таблицу — строки.
Самая главная проблема в отчете выше — это так называемая группировка строк при формировании отчета в 1С.

На примере выгрузки выше к группировке строк относятся: Контрагент, Номенклатура и Регистратор(Документ).

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

Например, на картинке выше к Контрагенту относятся Автокомпонент-ТЗК и Азов-ТЗК. Это верхний уровень группировки. Следующий уровень — Номенклатура: Р86023 Pehapol разбавитель и PLP02004 разбавитель.

И последний, третий уровень — Регистратор(Документ), к нему относятся все светло-желтые строки — «Реализация товаров и услуг…».
Вторая проблема — данные по каждому товару разбиты на месяцы и в каждом месяце три отдельных категории значений: Количество (в ед. хранения), Цена продажи и Цена закупки. И при этом месяцы расположены не в строках, а в столбцах. Что так же затрудняет работу с таблицей формулами, не говоря о сводных таблицах.

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

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

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

Чтобы более точно понять принцип работы всех настроек и команды в целом лучше применить некоторые определения для данных:

• Свойства строки — данные, которые характеризуют строку (Наименование, Артикул, Код, Цвет, Номенклатура, Документ и т.п.)
• Значения строки — как правило числовые значения(Количество, Цена, Сумма и т.п.), относящиеся к конкретному товару, документу и т.п. Т.е. непосредственно количественные данные нижнего уровня таблицы (строки с максимальным количеством отступов)

Настройки
После вызова команды появится окно настроек:

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

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

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

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

Строки с наибольшим количеством отступов(на картинке выше это Регистратор (Документ)) считаются как данные нижнего уровня и данные этих строк будут определены как Значения строки и будут записаны в результирующую таблицу. Остальные строки(с меньшим количеством отступов) будут записаны в каждую строку результирующей таблицы как Свойства строки(см.схему выше).

Важно: для более корректного преобразования данных лучше указывать соответствия отступов в порядке возрастания(от минимального к максимальному: , 1, 2 или , 3, 6 и т.д.).
Примечание: отступы определяются исключительно в одном столбце — в том, в котором расположена Первая ячейка в строках данных

Чтобы добавить соответствие необходимо:

• либо дважды щелкнуть мышью по любой пустой строке поля
• либо нажать сочетание клавиш Ctrl+Plus(кнопка + на клавиатуре)
• либо нажать в поле правой кнопкой мыши и в появившемся меню выбрать Добавить

Чтобы изменить существующее соответствие необходимо:

• либо дважды щелкнуть мышью по строке поля с нужным соответствием(при этом в зависимости от того над каким пунктом был щелчок активируется либо Наименование, либо Кол-во отступов)
• либо нажать сочетание клавиш Ctrl+Right(стрелка вправо на клавиатуре)
• либо нажать в поле правой кнопкой мыши и в появившемся меню выбрать Изменить

Чтобы удалить существующее соответствие необходимо:

• либо нажать сочетание клавиш Ctrl+Delete
• либо нажать в поле правой кнопкой мыши и в появившемся меню выбрать Удалить

Если в таблице нет отступов, то в любом случае необходимо указать хотя бы один уровень и записать для него количество отступов, равное нулю. См.пример таких таблиц и настроек: пример 1>>, пример 2 >>

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

Заголовки переменных данных

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

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

Заголовки расположены в ячейках(C8:E8) — указывается диапазон ячеек одной строки заголовка. Если нет повторяющихся заголовков значений, то данные указанных столбцов будут определяться как Значения строки. Если есть повторяющиеся заголовки значений, то значения ячеек указанных столбцов буду определяться как Свойства строки.

Есть повторяющиеся заголовки значений — если в отчете имеются данные, повторяющиеся с каким-то шагом и эти данные необходимо включить в итоговый отчет, то необходимо установить этот флаг.

— Заголовки повторяются с шагом — указывается целое число больше нуля, определяющее с какой периодичностью повторяются данные. В рассматриваемой таблице данные расположены таким образом, что Количество (в ед.хранения), Цена продажи и Цена закупки повторяются для каждого месяца. Т.е. на один месяц три столбца данных. Значит шаг необходимо выставить равный 3.
— Заголовки в ячейках(F8:AO8) — указывается диапазон ячеек (или одна ячейка), одной строки заголовка, данные из столбцов которых необходимо записать построчно для каждого значения строки. Если нет подзаголовков значений, то данные указанных столбцов будут определяться как Значения строки. Если есть подзаголовки значений, то значения ячеек указанных столбцов буду определяться как Свойства строки.
— Подзаголовки в ячейках(F10:H10) — если указаны, то Значения строки будут взяты из столбцов, указанных в этом поле. Значения из столбцов поля Заголовки в ячейках будут определяться как Свойства строки и записываться в отдельный столбец(соответствующий названию столбца). Также, если подзаголовки указаны и шаг повторения больше 1, то из Заголовков в результирующую таблицу будет записываться одно значение на весь повторяющийся блок подзаголовков(в примере используется шаг — 3):


Определять последний столбец по заголовкам — если установлен, то для заголовков переменных данных достаточно будет указать только первую ячейку заголовка — остальные данные будут определены на основании строки заголовка. При этом если данные в различных заголовках и подзаголовках заканчиваются в различных столбцах — в качестве последнего столбца данных будет взят самый дальний столбец, т.е. наибольший из всех. Если данный пункт отключен, то данные из таблицы будут браться исключительно из указанных в полях столбцов.
Например, если в таблице выше подзаголовки записаны в столбцах F10:AL10, то в поле Подзаголовки в ячейках достаточно будет указать ячейку F10 — последняя ячейка(AL10) будет определена автоматически. Если флажок снят — то будут обработаны только те столбцы точек, которые указаны в поле Подзаголовки в ячейках.
Для чего это нужно: иногда в таблицах в конце присутствует блок итогов и не всегда эти итоги нужны в таблице для анализа. Если указать определение автоматически — то итоги так же попадут в результирующий лист и будут только мешать.
Записывать каждый подзаголовок в новую строку — если установлен, то подзаголовки будут переносится не блоком в одну строку, а каждая ячейках подзаголовка будет записана в новую строку. При этом все свойства строки будут продублированы для значения подзаголовка. См.пример применения >>
Переносить в новую таблицу только данные с непустыми ячейками — если установлен, то в результирующую таблицу будут перенесены только те строки ячеек полей, определенных как Значения строки, по которым в ячейке есть хоть какое-то значение.
Для чего это нужно: на примере таблицы выше: в ячейках от F13 до H19 нет данных. Если флажок установлен — то в результирующем отчете будет как минимум на 7 ненужных строк меньше. В большинстве случаев этот флажок лучше ставить, но бывают ситуации, когда даже пустые строки и ячейки важны для анализа(например, определить, по каким позициям операции отсутствуют).
Считать последнюю строку группы максимальным уровнем — применяется в случаях, когда структура исходной таблицы построена таким образом, что в каждой группе данных не всегда есть максимальный доступный уровень отступа:

На картинке видно, что для группы «Материалы и запчасти на Контр по ЛЗК» максимальный уровень отступов 16, для «Иные запчасти» — 14, а для «ОС до 40 тыс.руб» — 12. Если галочка Считать последнюю строку группы максимальным уровнем будет снята, то данные будут перенесены некорректно, т.к. даже если указать максимальный уровень 16, то данные с меньшим количеством отступов просто не попадут в итоговую таблицу в качестве Значения строки. Если же галочка Считать последнюю строку группы максимальным уровнем будет установлена — то даже если уровень будет меньше 16 программа определит, что это последний уровень в группе и перенесет именно эти строки в итоговый отчет как Значения строки. Шаблоны настроек
Создание нового шаблона
Создавать шаблоны настроек просто — достаточно заполнить левую часть формы для обработки какого-либо отчета, после чего перейти в правую часть, выбрать создать новый шаблон, вписать имя шаблона в соответствующее поле и нажать Сохранить текущие настройки в шаблон. Лучше сразу давать понятные имена шаблонам(выгрузка_1С_продажи, 1С8_остатки и т.п.). Имя шаблона не должно содержать пробелов, знаков препинания(кроме нижнего подчеркивания) и не должно начинаться с цифры. Если имя шаблона будет задано неверно — надстройка выдаст предупреждение и необходимо будет изменить имя в соответствии с требованиями.
По сути на этом создание шаблона заканчивается. Он успешно запоминается надстройкой и теперь будет доступен всегда.
Применение шаблона
Для применения шаблона необходимо выбрать нужный шаблон в списке шаблонов (правая часть формы) и нажать Применить настройки выбранного шаблона. Поля настроек (левая часть формы) будут заполнены в соответствии с шаблоном. Так же заполнить поля из шаблона можно двойным кликом левой кнопкой мыши на названии шаблона.
Изменение шаблона
Для изменения шаблона необходимо выбрать нужный шаблон в списке шаблонов (правая часть формы), при необходимости заполнить поля настроек (двойной клик мыши или Применить настройки выбранного шаблона). После этого необходимо внести требуемые настройки в левой части формы. Выбрать изменить выбранный шаблон и нажать Сохранить текущие настройки в шаблон. Настройки выбранного шаблона будут изменены.
Удаление шаблона
Для удаления шаблона необходимо выбрать шаблон из списка и нажать кнопку Удалить выбранный. Следует помнить, что данная операция необратима и шаблон будет удален без возможности восстановления.
Удаление всех шаблонов
Для удаления всех шаблонов из списка необходимо нажать кнопку Удалить все шаблоны. Следует помнить, что данная операция необратима и все шаблоны будут удалены без возможности восстановления.
Другие примеры таблиц и настроек

Пример обработки оборотно-сальдовой ведомости:
Для преобразования такой таблицы в плоскую необходимо будет применить такие настройки формы:
В результате получим такую таблицу:

• Пример отчета без повторяющихся заголовков:

  Настройки формы для преобразования такой таблицы в плоскую:

• В результате получим такую таблицу:
• Пример настроек с использование пункта Записывать каждый подзаголовок в новую строку. Наиболее актуально это для таблиц примерно следующего вида:
• Для преобразования в удобную таблицу необходимо применить следующие настройки:
• результатом будет такая таблица:
• Попробовав преобразовать пару различных таблиц можно достаточно быстро понять принцип.
• Так же см.:
  Подготовить таблицу — столбцы
  Формат ячеек как в источнике

Источник: https://www.excel-vba.ru/multex/podgotovit-tablicu-stroki/

Как в Excel сделать таблицу: пошаговая инструкция

Таблицы – важный инструмент в работе пользователя Excel. Как в Экселе сделать таблицу и автоматизиро…

Таблицы – важный инструмент в работе пользователя Excel. Как в Экселе сделать таблицу и автоматизировать этот процесс, ответит наша статья.

Советы по структурированию информации

Перед тем, как создать таблицу в Excel, предлагаем изучить несколько общих правил:

• Сведения организуются по колонкам и рядам. Каждая строка отводится под одну запись.
• Первый ряд отводится под так называемую «шапку», где прописываются заголовки столбцов.
• Нужно придерживаться правила: один столбец – один формат данных (числовой, денежный, текстовый и т.д.).
• В таблице должен содержаться идентификатор записи, т.е. пользователь отводит один столбец под нумерацию строк.
• Структурированные записи не должны содержать пустых колонок и рядов. Допускаются нулевые значения.

Как создать таблицу в Excel вручную

Для организации рабочего процесса пользователь должен знать, как создать таблицу в Экселе.Существуют 2 метода: ручной и автоматический. Пошаговая инструкция, как нарисовать таблицу в Excel вручную:

1. Открыть книгу и активировать нужный лист.
2. Выделить необходимые ячейки.
3. На панели инструментов найти пиктограмму «Границы» и пункт «Все границы».
4. Указать в таблице имеющиеся сведения.

II способ заключается в ручном рисовании сетки таблицы. В этом случае:

1. Выбрать инструмент «Сетка по границе рисунка» при нажатии на пиктограмму «Границы».
2. При зажатой левой кнопке мыши (ЛКМ) перетащить указатель по обозначенным линиям, в результате чего появляется сетка. Таблица создается, пока нажата ЛКМ.

Как создать таблицу в Excel автоматически

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

Область таблицы

Перед тем, как составить таблицу в Excel, пользователю нужно определить, какой интервал ячеек ему понадобится:

1. Выделить требуемый диапазон.
2. В MS Excel 2013-2019 на вкладке «Главная» кликнуть на пиктограмму «Форматировать как таблицу».
3. При раскрытии выпадающего меню выбрать понравившийся стиль.

Кнопка «Таблица» на панели быстрого доступа

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

1. Активировать интервал ячеек, необходимых для работы.
2. Перейти в меню «Вставка».
3. Найти пиктограмму «Таблицы»:

• В MS Excel 2007 кликнуть на пиктограмму. В появившемся диалоговом окне отметить или убрать переключатель пункта «Таблица с заголовками». Нажать ОК.
• В MS Excel 2016 нажать пиктограмму и выбрать пункт «Таблица». Указать диапазон ячеек через выделение мышкой или ручное прописывание адресов ячеек. Нажать ОК.

Примечание: для создания объекта используют сочетание клавиш CTRL + T.

4. Для изменения названия столбца перейти на строку формул или дважды кликнуть на объекте с заголовком.

Диапазон ячеек

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

Активированная вкладка «Конструктор» раскрывает инструментарий, облегчающий процесс редактирования объекта.

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

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

Заполнение данными

Работа со структурированной информацией возможна, если ячейки заполнены текстовой, численной и иной информацией.

• Для заполнения необходимо активировать ячейку и начать вписывать информацию.
• Для редактирования ячейки дважды кликнуть на ней или активировать редактируемую ячейку и нажать F2.
• При раскрытии стрелок в строке заголовка структурированной информации MS Excel можно отфильтровать имеющуюся информацию.
• При выборе стиля форматирования объекта MS Excel автоматически выбрать опцию черезстрочного выделения.
• Вкладка «Конструктор» (блок «Свойства») позволяет изменить имя таблицы.
• Для увеличения диапазона рядов и колонок с последующим наполнением информацией: активировать кнопку «Изменить размер таблицы» на вкладке «Конструктор», новые ячейки автоматически приобретают заданный формат объекта, или выделить последнюю ячейку таблицы со значением перед итоговой строкой и протягивает ее вниз. Итоговая строка останется неизменной. Расчет проводится по мере заполнения объекта.

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

Сводная таблица

Сводка используется для обобщения информации и проведения анализа, не вызывает трудностей при создании и оформлении. Для создания сводной таблицы:

1. Структурировать объект и указать сведения.
2. Перейти в меню «Вставка» и выбрать пиктограмму: в MS Excel 2007 – «Сводная таблица»; в MS Excel 2013-2019 – «Таблицы – Сводная таблица».
3. При появлении окна «Создание сводной таблицы» активировать строку ввода диапазона, устанавливая курсор.
4. Выбрать диапазон и нажать ОК.

Примечание: Если сводка должна находиться после создания на этом же листе, пользователь устанавливает переключатель на нужную опцию.

5. При появлении боковой панели для настройки объекта перенести категории в нужные области или включить переключатели («галочки»).

Созданная сводка автоматически подсчитывает итоги по каждому столбцу.

Рекомендуемые сводные таблицы

Поздние версии MS Excel предлагают воспользоваться опцией «Рекомендуемые сводные таблицы». Подобная вариация анализа информации применяется в случаях невозможности правильного подбора полей для строк и столбцов.

Для применения рекомендуемых сводных таблиц:

1. Выделить ячейки с введенной информацией.
2. При клике на пиктограмму «Таблицы» выбрать пункт «Рекомендуемые сводные таблицы».
3. Табличный процессор автоматически анализирует информацию и предлагает оптимальные варианты решения задачи.
4. В случае выбора подходящего пункта таблицы и подтверждения через ОК получить сводную таблицу.

Готовые шаблоны в Excel 2016

Табличный процессор MS Excel 2016 при запуске предлагает выбрать оптимальный шаблон для создания таблицы. В офисном пакете представлено ограниченное количество шаблонов. В Интернете пользователь может скачать дополнительные образцы.

Чтобы воспользоваться шаблонами:

1. Выбирать понравившийся образец.
2. Нажать «Создать».
3. Заполнить созданный объект в соответствии с продуманной структурой.

Оформление

Экстерьер объекта – важный параметр. Поэтому пользователь изучает не только, как построить таблицу в Excel, но и как акцентировать внимание на конкретном элементе.

Создание заголовка

• Дана таблица, нарисованная посредством инструмента «Границы». Для создания заголовка:
• Выделить первую строку, кликнув ЛКМ по численному обозначению строки.
• На вкладке «Главная» найти инструмент «Вставить».
• Активировать пункт «Вставить строки на лист».

После появления пустой строки выделить интервал клеток по ширине таблицы.

Нажать на пиктограмму «Объединить» и выбрать первый пункт.

Задать название в ячейке.

Изменение высоты строки

Обычно высота строки заголовка больше первоначально заданной. Корректировка высоты строки:

• Нажать правой кнопкой мыши (ПКМ) по численному обозначению строки и активировать «Высота строки». В появившемся окне указать величину строки заголовка и нажать ОК.
• Или перевести курсор на границу между первыми двумя строками. При зажатой ЛКМ оттянуть нижнюю границу ряда вниз до определенного уровня.

Выравнивание текста

Если пользователь предполагает расположение текста в ячейке иное, нежели по умолчанию, то пользуется пиктограммами «Выравнивание» относительно горизонтали и вертикали, а также кнопкой «Ориентация». Выбор пунктов выпадающего списка позволит расположить текст по вертикали или диагонали.

Изменение стиля

Изменение размера шрифта, начертания и стиля написания осуществляется вручную. Для этого пользователь пользуется инструментами блока «Шрифт» на вкладке «Главная» или вызывает диалоговое окно «Формат ячеек» через ПКМ.

Пользователь может воспользоваться пиктограммой «Стили». Для этого выбирает диапазон ячеек и применяет понравившийся стиль.

Как вставить новую строку или столбец

Для добавления строк, столбцов и ячеек:

• выделить строку или столбец, перед которым вставляется объект;
• активировать пиктограмму «Вставить» на панели инструментов;
• выбрать конкретную опцию.

Удаление элементов

Для удаления объектов на листе MS Excel пользователь активирует аналогичную кнопку на панели инструментов, предварительно выделив строку, столбец, ячейку.

Заливка ячеек

Для задания фона ячейки, строки или столбца:

• выделить диапазон;
• найти на панели инструментов пиктограмму «Цвет заливки»;
• выбрать понравившийся цвет.

II способ

• вызвать «Формат ячеек» через ПКМ;
• перейти на вкладку «Заливка»;
• выбрать цвет, способы заливки, узор и цвет узора.

III способ

• щелкнуть на стрелочку в блоке «Шрифт»;
• перейти на вкладку «Заливка»;
• выбрать понравившийся стиль.

Формат элементов

На панели инструментов находится пиктограмма «Формат». Опция помогает задать размер ячеек, видимость, упорядочить листы и защитить лист.

Формат содержимого

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

Использование формул в таблицах

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

Ознакомиться с полным списком и вписываемыми аргументами пользователь может, нажав на ссылку «Справка по этой функции».

Для задания формулы:

• активировать ячейку, где будет рассчитываться формула;
• открыть «Мастер формул»;

или

• написать формулу самостоятельно в строке формул и нажимает Enter;

или

• применить и активирует плавающие подсказки.

На панели инструментов находится пиктограмма «Автосумма», которая автоматически подсчитывает сумму столбца. Чтобы воспользоваться инструментом:

• выделить диапазон;
• активировать пиктограмму.

Использование графики

Для вставки изображения в ячейку:

1. Выделить конкретную ячейку.
2. Перейти в меню «Вставка – Иллюстрации – Рисунки» или «Вставка – Рисунок».
3. Указать путь к изображению.
4. Подтвердить выбор через нажатие на «Вставить».

Инструментарий MS Excel поможет пользователю создать и отформатировать таблицу вручную и автоматически.

Источник: https://FreeSoft.ru/blog/kak-sozdat-tablitsu-v-excel-poshagovaya-instruktsiya

Многоуровневый связанный список в MS EXCEL на основе таблицы

В статье Многоуровневый связанный список рассмотрен вариант 3-х уровневого списка. Элементы каждого уровня в нем располагаются на отдельных листах. Это не всегда удобно: при создании 4-х и 5-и уровневых списков — резко увеличивается число задействованных столбцов. В этой статье сформируем связанный список из единой таблицы.

Эта статья является обзорной, а не подробным изложением, т.к. пользователи, которые решаться на создание подобных «монстров» должны хорошо разбираться в Выпадающих списках (Dropdown List using Data Validation), создании имен (Names), динамических диапазонах (Dynamic Named Ranges), формулах массива (Array Formula), понимать, что такое Связанный список (Dependent Drop-Down List) и др.

Для начала создадим таблицу, в которую будем вводить элементы всех списков (6 уровней — 6 столбцов). См. файл примера.

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

Выбирать значения будем как обычно с помощью Выпадающих списков.

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

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

Поясним картинку. Т.к. на материке Америке (Уровень 1) нет страны Германии (Уровень 2), то это несоответствие подсвечивается Условным форматированием.

Это несоответствие появилось вследствие того, что пользователь перевыбрал значение в Уровне1 с Европа на Америка, а значение на следующем уровне, естественно, автоматически не поменялось.

Это ограничение обходится в статье Связанный список в MS EXCEL на основе элемента управления формы.

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

Однотипность имен и формул в ячейках позволяет быстро изменять количество уровней (правда, куда уж больше?).

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

здесь заполняется только одна (!) группа связанного списка.

Примечание. Пример будет работать начиная с версии MS EXCEL 2007, т.к. функция ЕСЛИОШИБКА() будет работать начиная с этой версии, чтобы обойти это ограничение читайте статью про функцию ЕСЛИОШИБКА().

Источник: https://excel2.ru/articles/mnogourovnevyy-svyazannyy-spisok-v-ms-excel-na-osnove-tablicy

Группировка строк в excel: настройка

Здравствуйте, друзья.

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

Инструкция

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: http://profi-user.ru/gruppirivka-v-excel/

Здравствуйте, друзья. Думаю, каждому, кто работал в Эксель, попадались таблицы со структурой данных.

Группировка в Эксель, чаще всего, применяется, когда есть организация со структурой, и мы оцениваем некоторые показатели согласно этой структуры.

Например, продажи по Компании в целом, по региональным представительствам, по менеджерам. Посмотрите на картинке, как это может быть без группировки:

На рисунке детальный пример отчета о продажах по структурным подразделениям подневно.  А что если нужно предоставить руководителям отчет в разрезе только лишь департаментов и помесячно, без детализации? Чтобы было вот так:

Согласитесь, такой вид таблицы более нагляден и показателен для анализа работы Компании в целом. Но как получить такую таблицу достаточно быстро, не скрывая и не удаляя ячейки? Очень просто, задайте структуру документа.

На рисунке выше я привел уже структурированный документ, с маркерами группы. В таблице сгруппированы менеджеры по регионам и регионы в компании, а так же, дни недели в месяцы.

Чтобы скрыть какую-то отдельную группу, кликните на значке «минус» в итоговой строке этой группы». Чтобы раскрыть – на значке «плюс».

Чтобы отобразить какой-то определенный уровень – нажмите на его номер в верхнем левом углу листа.

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

Есть два способа создать структуру листа: автоматический и ручной.

Автоматическое создание структуры в Excel

Программа может попытаться создать структуру автоматически. Для этого нажмите на ленте: Структура – Группировать – Создать структуру.

Если у Вас правильно и логично записаны формулы суммирования, структура будет создана правильно. У меня эта функция часто срабатывает правильно, поэтому сначала я пробую именно автоматическое создание. Структура из примера отлично создалась автоматически.

Ручное создание структуры

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

1. Оформите и наполните таблицу, создайте итоговые строки и столбцы
2. Кликните на маленькой пиктограмме со стрелкой в нижнем правом углу ленточной группы Данные – Структура. В открывшемся окне настройте расположение строк и столбцов итогов

1. Выделите строки или столбцы, которые нужно группировать (не выделяйте итоговые). Делайте это с каждой группой по очереди, начиная с самых «глубоких». В моем примере это фамилии менеджеров.

1. Нажмите на ленте Структура – Группировать (ли нажмите Alt+Shift+стрелка вправо). Будет создана группировка

1. Повторяйте п.3-4 до полного структурирования данных. Повторюсь, начинайте с подчиненных групп, потом переходите на уровень выше. Т.е. в таблице из примера мы поочередно сгруппируем менеджеров в филиалы, потом филиалы в компанию.

Когда Вы вставляете новые строки и столбцы в структурированную таблицу – будьте осторожны. Если, например, добавляется новый менеджер в группу, вставляйте строку где-то между сгруппированными строками, не первым элементом группы и не последним. В противном случае, новая строка не впишется в структуру.

При копировании диапазона ячеек, скрытые строки и столбцы не копируются. Но когда они скрыты группировкой – все равно копируются. Чтобы выделить только данные, отображенные на экране, выделите нужный диапазон и нажмите F5. В открывшемся окне выберите Выделить – Только видимые ячейки. Теперь ячейки, скрытые группировкой, не будут скопированы.

Если Вам больше не нужна структура – удалите ее. Для этого выполните на ленте Данные – Структура – Разгруппировать – Удалить структуру.

Вот и все, тренируйтесь и применяйте на практике структурирование данных в Экселе. Приглашаю Вас комментировать изложенный материал, задавайте Ваши вопросы и делитесь опытом!

Следующая статья будет посвящена подключению внешних данных и их консолидации. Это неотъемлемая часть работы большинства пользователей Excel. Присоединяйтесь к чтению!

Источник: https://officelegko.com/2017/01/31/gruppirovka-dannyih-v-excel-pridaem-tablitsam-stroynosti/

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

118061 07.10.2012 Скачать пример

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

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

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

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

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

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

Sub Multilevel_Group()
Dim level As Single, i As Single
Dim start As Single, LastRow As Single

Const FIRST_ROW = 2 'первая строка списка
Const FIRST_COLUMN = 1 'первый столбец списка
Const NUMBER_OF_LEVELS = 3 'количество уровней

Set ws = ActiveSheet
ws.UsedRange.ClearOutline 'убираем все группировки на листе
LastRow = WorksheetFunction.Match(«Конец», ws.Columns(FIRST_COLUMN), 0) 'определяем номер последней строки

'проходим во вложенном цикле по уровням и группируем
For level = 1 To NUMBER_OF_LEVELS
start = 0
For i = FIRST_ROW To LastRow
'если нашли начало группы — запоминаем номер строки
If ws.Cells(i, level+FIRST_COLUMN-1) «» And _
WorksheetFunction.CountA(ws.Cells(i + 1, FIRST_COLUMN).Resize(1, level)) = 0 Then start = i

'если нашли конец группы — группируем
If WorksheetFunction.CountA(ws.Cells(i + 1, FIRST_COLUMN).Resize(1, level)) > 0 And start > 0 Then
ws.Rows(start + 1 & «:» & i).Group
start = 0
End If
Next i
Next level
End Sub

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

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

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

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

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

Ссылки по теме

Источник: https://www.planetaexcel.ru/techniques/9/47/

Двухуровневый выпадающий список в Excel

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

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

Формула ДВССЫЛ возвращает ссылку на диапазон заголовков «умной» таблицы. Преимущество использования такой таблицы в том, что при добавлении столбцов, выпадающий список будет автоматически расширен.

Осталось создать второй зависимый выпадающий список – список подгрупп.

Смело повторяем 4 первых пункта описанных выше. Источником в окне Проверка данных для второго выпадающего списка будет служить формула =ДВССЫЛ(«Таблица1[«&F2&»]»). Ячейка F2 в данном случае — значение первого выпадающего списка.

Можно использовать и обычную «неумную» таблицу, однако в этом случае вам придется вручную изменять диапазоны заголовков и строк. В рассмотренном примере это происходит автоматически.

Второй способ создания двухуровнего списка

Второй способ удобно применять, когда данные выпадающего списка записаны в два столбца. В первом идет наименование группы, а во втором – подгруппы.

ВАЖНО! Перед созданием зависимого списка по подгруппам необходимо отсортировать исходную таблицу по первому столбцу (столбец с группой) далее будет понятно зачем это делается.

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

Теперь создадим выпадающий список групп. Для этого выполните 4 первых пункта из первого способа создания двухуровнего списка. В качестве Источника укажите диапазон уникальных значений групп. Тут все стандартно.

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

Теперь самая сложная часть — указать в Источнике динамическую ссылку на диапазон со значениями второго выпадающего списка (списка подгрупп). Решать ее будем с помощью функции СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, [высота], [ширина]), которая возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.

• Ссылка в нашем случае — $A$1 — верхний левый угол исходной таблицы;
• Смещ_по_строкам — ПОИСКПОЗ(F3;$A$1:$A$67;0)-1 — номер строки со значением искомой группы (в моем случае страны ячейка F3) минус единица;
• Cмещ_по_столбцам — 1 — так как нам необходим столбец с подгруппами (городами);
• [Высота] — СЧЁТЕСЛИ($A$1:$A$67;F3) — количество подгрупп в искомой группе (количество городов в стране F3);
• [Ширина] — 1 — так как это ширина нашего столбца с подгруппами.

Источник: https://micro-solution.ru/excel/drop-down-lists/two-level