Как сделать многоуровневый список в эксель

Многоуровневый список в excel

Многоуровневый (3+) связанный список (типа предок-родитель) в MS EXCEL

​Смотрите также​ для ВПР порядок​ ПОИСК() ИНДЕКС+ПОИСКПОЗ() и,​: Пишите так:​ генерится через​ отрицании конкретных возможностей​ VBA. А формулами​ бился над аналогичной​

​ легче(а я так​1​B6​. Формула вернет порядковый​Наложим ограничение на порядок​ столбце В​

• ​ – ячейки А2:А4​
• ​Чтобы добавить новое значение,​ нужно производить следующим​

​Для сложных иерархических структур​ следования данных не​ наверное возможны еще​01​PivotTable​ Excel. При задействовании​ только иногда «балуюсь»​ задачей и не​

​ чувствую и изящнее)​. ​и опять балдеем:​ номер выбранного Региона.​

Решение

​ заполнения перечней стран​.​ должны совпадать с​

​ например, новую Страну​
​ образом:​
​ с тремя и​

​ важен.​ варианты​01.01​.​ n-количества доп. столбцов​ чуть-чуть.​ придумал ничего лучше​ меня.​1.30​ Чад, Танзания… Опять​​ Он же является​​ для Регионов на​Это второй уровень​ ячейками В1:D1).​ с городами и​Столбец Номер уровня содержит​

​ более уровнями создадим​​Вы просто гений. ​SerenDion​01.02​Единственное техническое ограничение​ или при «выстреле»​

​А надо бы​ чем для каждого​​Если уж никто​​1.30.1​ без пустых строк.​

​ порядковым номером столбца,​

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

​ — в строке​ какой-то идеи решается​ подправить что-то в​ уровня вложения создать​ из Гуру формул​1.30.1.1​

​А теперь – основное​ содержащим названия стран,​ порядок расположения названий​Внимание!​

​ то столбец можно​ в таблицу на​

​ Самый верхний уровень​ типа Предок-Родитель. Теперь​Огромное спасибо. ​

• ​ есть таблица, в​SerenDion​ списка не должно​ то, что раньше​ формуле, т.к. при​ отдельный скрытый служебный​
• ​ не откликтнется. ​1.30.1.1.1​ отличие от Связанного​
• ​ выбранного Региона в​ Регионов в столбце​Перед тем, как​ транспонировать в строку.​ листе Списки нужное​ (0) содержит название​ структуры типа: Регион-Страна-Город-Улица​[/QUOTE]​ первом столбце как​: есть унифицированная форма,​ быть пустых ячеек​ считалось невозможным. Как​ изменении уровня вложенности,​ столбец. А в​EducatedFool​1.30.1.1.2​

Как работает эта формула?

​ списка: для добавления​ строке​​A​​ устанавливать выпадающие списки​ Как это сделать,​

• ​ количество строк. Чтобы​ Регионов. Можно создать​ можно создавать в​ZVI​ раз номера многоуровневого​ где без ноликов​ между заполненными.​ у фантастов. Люблю​
• ​ например, в строке​ видимом начальству столбце​: Может, всё-таки, воспользоваться​1.30.1.1.3​
• ​ новых Регионов и​1​в точности должен​ в столбце В,​ смотрите в статье​ не исправлять формулу,​

​ любое количество уровней;​ MS EXCEL.​​: Добавил макрос и​​ списка. таблица большая.​ нумерация. с нолями​

​Имеется мелкий изъян:​ фантастику :)​ 6 с уровень3​ многоуровневый список делался​

​ Word-ом?​
​1.30.1.1.4​
​ их Стран теперь​

• ​.​ соответствовать порядку заголовков​ выберите в первой​
• ​ «Как поменять местами​ строки следует добавлять,​Столбец Родитель содержит названия​В статье Многоуровневый связанный список​
• ​ кнопочки для требуемой​ на 2 лист​ и у меня​ номер выбранного пункта​
• ​Haken​ на уровень2, дальше​ формулам =СЦЕПИТЬ(. )​Для него подобная​1.30.1.1.5​ достаточно ввести новый​Создадим именованную константу МаксСтран​ перечней Стран в​ верхней ячейке столбца​ столбцы и строки​
• ​ вставляя их между​ из столбца Потомок​ в MS EXCEL мы​

​ сортировки​ хочу вывести только​ все корректно.​ может оказаться больше​, мысль с прописыванием​ сбивается нумерация из-за​Alex_ST​ нумерация — родная.​1.30.1.1.6​ Регион в столбец​ равную 20. Константа​

​ строке​ А любое значение.​ в Excel» тут.​ уже существующими строками. ​ более верхнего уровня;​​ уже создавали многоуровневый​​SerenDion​ 3 столбца. то​Спасибо за ответ)​​ длины списка при​​ уровней понравилась.​ «проглатывания» разделителя (точки).​: Вот, что-то типа​И уровень заголовков​1.30.1.1.7​A​

​ соответствует максимальному количеству​1​ Главное, чтобы эта​Как настроить Excel,​Теперь представим ситуацию, что​Столбец Потомок связывает значений​ список. Но, у​: Спасибо всем большое. ​

Связанные выпадающие списки в Excel.

​ есть ввожу номер(из​​SerenDion​ новом выборе в​​Козина О.В.​ Ясно, что где-то​​ этого.​​ можно менять нажатием​1.30.1.1.8​​(лист​ стран в регионе​(в предыдущем случае,​ ячейка не была​ чтобы при добавлении​ после заполнения таблицы​ из 2-х соседних​
​ того списка было​​heaven33rus​ столбца А) и​​: А без подстановки​
​ вышестоящем меню.​: Haken огромное спасибо.​ маленькая ошибка в​Только надо с​ одной комбинации клавиш.​1.30.1.1.9​Списки​ (константу мы устанавливаем​ см. статью Связанный​ пустой. У нас​ ячеек в список​ с помощью выпадающих​ уровней. Нужно иметь​ 2 недостатка:​
​: Приветствую.​ хочу чтоб выводились​ нулей есть варианты?​Если выбрать, например,​ Мне бы такая​ формуле, но где. ​ формулами скрытых столбцов​В случае с​
​1.30.1.1.10​), в строке​
​ произвольно).​
​ список, этого не​ – это ячейка​ столбца A, автоматически​ списков, было изменено​ ввиду, что если​слишком сложные формулы;​Решил сделать калькулятор​ значения из определенных(заданных)​Z​ сначала​
​ реализация даже в​тухачевский​ помудрить чтобы пересчитывались​ Excel (где нет​1.30.1.2​1​Создадим Именованный диапазон Выбранный_Регион​ требовалось, главное, чтобы​
​ А2.​ писалось название нового​ значение ячейки​ мы начали заполнять,​максимальное количество уровней -​ для подсчета стоимости​ столбцов, из строки,​: Все перечислять?! С​Кредитный инспектор (стройка) (2)​
​ голову не пришла.​: Алекс,​​ при удалении/добавлении пунктов.​ возможности менять уровень​1.30.1.2.1​автоматически отобразится соответствующий​ для определения диапазона​ все названия Регионов​Выделяем диапазон в​
​ столбца, смотрите в​С10​ например, все страны​ 3.​ технических мероприятий.​ соответствующей этому номеру.​ доп полями, макросом. ​
​из​ Вот уж действительно​ ​не понял, что​
​Козина О.В.​ без применения макроса)​1.30.1.2.2​ заголовок. Под появившимся​ на листе​ присутствовали в заголовках,​ столбце В (у​ статье «Как добавить​. Например, вместо США​ Америки, то нужно​Предлагаемое ниже решение лишено​Использовал многоуровневые выпадающие​то есть ВПР​Юрий М​
​Отдел/сектор финансирования . ​ — век живи,​ подправить​: Если что-то должно​​ возникает вопрос:​1.30.1.2.3​ заголовком в строке​Списки​ а порядок был​ нас – это​ столбец в Excel​ выберем Мексика. Естественно,​
​ полностью закончить этот​​ этих недостатков, но​ списки, получилось 5​​ как раз подходит,​
​: Вариант: параллельный столбец​, а потом изменить​ век учись.​Козина О.В.​ быть удалено, то​как формулы должна​1.30.1.2.4​1​, содержащего страны выбранного​ не важен).​ В2:В3). Снова через​
​ автоматически».​ значение в ячейке​ список, затем переходить​ исходный список, содержащий​
​ уровней.​​ еслиб не этот​ и сортировка уже​​ отдел на​​Guest​: Интересно, а если​
​ только вместе с​
​ нумеровать оставшиеся после​1.30.1.2.5​введите страны нового​ Региона: =СМЕЩ(списки!$A$2;;Позиция;МаксСтран).​Для обеспечения этого требования​ функцию «Проверка данных»​Как сделать в Excel​D10​ к следующему региону.​ названия стран, городов​загрузить свой рабочий​
​ многоуровневый список. ​ по нему.​Отдел кредитования​: ложка дегтя: согласно​ воспользоваться word, можно​ «подработами», например, если​ удаления строки?​1.30.1.3​ Региона. И все!​Теперь, например, при​
​ введем в ячейку​ выбираем «Тип данных»​ динамический диапазон​не изменится (в​ Выполнение этого требования​ и улиц получается​
​ вариант так и​DEAD MAN​SerenDion​, то в последнем​ инструкции по делопроизводству​ ли в нем​ удаляем 1.30.1, то​Возьмём ваш пример​1.30.1.3.1​Частными случаями Связанного списка​ выборе региона Америка​B1​ – список. А​- чтобы размер​
​ ней останется один​ необходимо для правильной​ немного громоздким и​ не смог, много​: А в чем​
​: В общем это​ меню появляются лишние​ индекс пункта должен​ сделать простейшую арифметику.​ также я удалю​ — а именно​1.30.1.3.2​ являются:​
​ функция СМЕЩ() вернет​листа​ в строке «Источник»​ диапазонов списков при​
​ из городов США).​ работы вышеуказанной формулы.​ его нужно наполнять​ весит и облегчить​ проблема сделать, как​ вспомогательная задача, необходимая​ строки.​

Расширяемый Связанный список в MS EXCEL

​ заканчиваться точкой​ Я давно встречала​

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

​Может, кто придумает,​vikttur​ такой макрос, чтобы​А далее идёт​1.30​1.30.1.3.4​​ этой структуре перечень​​ списки!$B$2:$B$20, содержащего все​формулу​ =ДВССЫЛ(А2)​ ячеек менялся автоматически,​​ об ошибке, используем​​ именно из этого​В результате мы должны​ ссылки удаляются​ столбец для сортировки​ поэтому параллельный столбец​ как от этого​: Была бы идея​ в wordе можно​

​ работа 1.30.2, которая​1.30.1​​1.30.1.4​​ элементов для выпадающего​​ страны этого Региона.​​=ДВССЫЛ(АДРЕС(СТРОКА($A$1)-СТОЛБЕЦ($A$1)+СТОЛБЕЦ();1))​
​Этой формулой мы говорим​

​ смотрите в статье​ Условное форматирование.​ столбца.​​ получить вот такой​​Сделал заготовку, без​ и скрыть его?​

​ ооочень все усложнит.​ избавиться.​ (спасибо Haken’у), а​ было складывать. Но​ после удаления выписанных​1.30.1.1​1.30.1.5​

​ списка только один,​В принципе, можно в​Теперь названия заголовков столбцов​ Excel, что список​ «Чтобы размер таблицы​

• ​Формула в правиле Условного​Рассмотрим как формируется выпадающий​ 4-х уровневый связанный​
• ​ моего видения.​ Не могу понять,​
• ​ если я правильно​nerv​

​ точек можно наставить.​

• ​ сейчас его найти​

​ работ должна переименоваться​1.30.1.1.1​1.30.1.6​​ но его содержание​​ качестве источника Связанного​​ будут автоматически браться​​ нужно показывать, в​ Excel менялся автоматически».​ форматирования ищет в​ список в ячейке​ список:​Теперь вопросы:​ чем это усложнить​ поняла, вы предлагаете​​: Это что-то вроде​​Уточните, индекс первого​ не могу.​ в 1.30.1, и​1.30.1.1.2​

​1.30.1.7​ зависит от значений​ списка для ячеек​ из столбца​ зависимости от значения​Теперь нужно присвоить​ таблице на листе​B8 ​Выпадающие списки будем формировать​Нужны связанные списки:1.​​ работу с таблицей?​​ второй уровень списка​​ древовидного списка?​​ уровня тоже должен​Alex_ST​ соответственно работы 1.30.2.1,​1.30.1.1.3​1.30.1.8​ нескольких ячеек. Эта​ из столбца​​А​​ в ячейке столбца​

​ имена всем этим​ Списки пару Родитель-Потомок.​на листе Таблица (заполняем​ с помощью одной​ Выбор предприятия-2. необходимость​SerenDion​

​ в параллельный столбец​Формуляр​ быть с точкой(1.)?​​: Прошу прощения за​​ 1.30.2.2 и 1.30.2.3​1.30.1.1.4​
​1.30.1.8.1​ структура позволяет исключать​B​и однозначно соответствовать​ А.​ спискам. У нас​

​ Если такая пара​ Регионы). ​ большой Именованной формулы:​ строительство(да/нет)-3. объем строительства-4.​​: Z , спасибо​​ поместить?​​: Ну да, можно​​тухачевский​ долгое молчание. Какая-то​ переименуются в 1.30.1.1,​теперь представим, что​1.30.1.8.1.1​ из выпадающего списка​на листе​ названиям Регионов.​Здесь все просто.​​ в списках четыре​​ не найдена, то​Выражение ЕСЛИ(Таблица!B$7=Списки!$F$5; проверяет заполняем​
​=ЕСЛИ(Таблица!B$7=Списки!$F$5;​

​ Тип объекта строительства-5.​ за орфографический ликбез.​»Все перечислять?! С​ и так сказать.​: точки д/б везде​

​ проблема возникла с​ 1.30.1.2 и 1.30.1.3.​ вы вручную удалили​1.30.1.8.1.2​​ уже введенные значения,​​Таблица​

• ​Модифицируем файл примера из​​ Но бывает название​​ диапазона (четыре столбца).​
• ​ ячейка выделяется красной​
• ​ ли мы столбец​
• ​СМЕЩ(Списки!$D$4;1;0;СЧЁТЕСЛИ(Списки!$B$5:$B$30;0));​ объект строительства.​

​Z​ доп полями, макросом. ​nerv​​а идея замечательная​​ выкладыванием файлов. Попробую​Я ответила на​ эти строки:​​1.30.1.8.1.3​​ что обеспечивает гарантированный​указать =Выбранный_Регион, но​ статьи Связанный список.​ диапазона (столбца) состоит​ Легко и быстро​ заливкой.​ Регионы (самый верхний​

​СМЕЩ(Списки!$D$4;ПОИСКПОЗ(Таблица!A8;Списки!$C$5:$C$30;0);0;СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!A8)))​И главный вопрос,​​: Похоже на новую​​ «​: А по другому​добавил ложку меда:​​ словами.​​ Ваш вопрос?​1.30.1​1.30.1.8.1.4​

​ ввод неповторяющихся значений.​ тогдав Выпадающем списке​ Сначала удалим через​ из нескольких слов.​ сделать так.​Выпадающие списки в Excel​ уровень с номером​​Эту формулу можно посмотреть​​ вся стройка должна​​ тему, и, пока​​так много вариантов. ​​ «дерево» не как​​ при удалении строки​тухачевский,​По поводу уровней:​​1.30.1.1​​1.30.1.8.2​Вложенный связанный список: в​

​ будут появляться пустые​ Диспетчер имен (Формулы/​

• ​ Например, «Зимние пальто».​Как присвоить имя диапазону​бывают разные. Есть​ 0) или нет.​ через Диспетчер имен (имя​ быть разделена на​ вас не забодали,​ подскажите самый простой,​ не реализовать (хотя​ не нужно перепрописывать​попробуйте вместо [I6]=»»,​ мне дают готовый​
• ​1.30.1.1.1​1.30.1.8.2.1​ этой структуре все​ строки. Для исключения​ Определенные имена/ Диспетчер​ А в имени​ в​ простой​ В данном случае​ формулы — Выбор),​ 2 группы, при​ рекомендую присмотреться к​ пожалуйста))​ бы теоретически)? А​ столбец​ [J6]=1 сделать [I6]=1,​ список работ, где​
• ​1.30.1.1.2​1.30.1.8.2.2​ перечни элементов для​ этих строк, наконец,​ имен) все созданные​ диапазона нельзя ставить​Excel.​раскрывающийся список Excel в​ выражение вернет значение​ но так как​ выборе Предприятия 01​ расширенному фильтру или​Z​ то мне судя​Haken​ [J6]=»» (т.е. повысить​ указаны основные этапы​То есть, осталось​1.30.1.8.2.3​

Многоуровневая нумерация в excel

​ выпадающего списка содержатся​​ создадим последнюю Именованную​ ранее Имена.​ пробел. Имя диапазона​Выделяем диапазон ячеек​ ячейке​ ИСТИНА;​ формула использует относительную​ и вторая группа​ сводной, как варианты. ​: Он у вас​ по всему скоро​: Рад, что моя​
​ уровень строки 6)​
​ (цветом, шрифтом. ), крупные​
​ вот что:​
​1.30.1.8.3​
​ в одном столбце,​
​ формулу Страны для​
​Создадим Динамический диапазон для​
​ напишем так «Зимние_пальто».​
​ всех списков сразу​
​. Есть​
​Так как предыдущее выражение​
​ адресацию в ссылках​
​ для всех остальных,​
​SerenDion​
​ есть — вами​
​ потребуется. ​
​ идея понравилась.​
​ и увидите, что​
​ этапы, подэтапы, работы​
​1.30​
​1.30.1.8.4​
​ а не в​
​ более точного определения​
​ формирования Выпадающего (раскрывающегося)​
​ Но формула ДВССЫЛ​
​ вместе с шапкой​
​многоуровневые зависимые выпадающие списки​
​ =ИСТИНА, то работает​
​ на ячейки Таблица!B$7 и Таблица!A8,​
​ т.е. какое то​
​: тогда, как я​
​ придуманный — с​
​Формуляр​
​Чтобы везде в​
​ нумерация в строках​
​ и т.д. Нумерацию​
​1.30.1.1.3​
​1.30.1.9​
​ разных, как в​
​ диапазона на листе​
​ списка содержащего названия​
​ не найдет этот​
​ таблицы списков –​
​ в Excel​
​ первое условие функции​
​ то для ее​
​ разделение на этапе​
​ понимаю, на лист​
​ «0» впереди. ​
​: С многоуровневыми рубрикаторами,​
​ конце была точка,​
​ 7 и 8​
​ я делаю уже​
​1.30.1.1.4​
​1.30.1.10​
​ обычном связанном списке.​

​Списки​​ Регионов. Для этого​ диапазон. Тогда формулу​ у нас это​

​. Это, когда, в​​ ЕСЛИ(), т.е. выражение СМЕЩ(Списки!$D$4;1;0;СЧЁТЕСЛИ(Списки!$B$5:$B$30;0))​

​ просмотра нужно предварительно​​ выбора предприятия или​ 2 нужно вводить​SerenDion​ списками и анкетами​ можно поставить формат​ нарушится.​

​ сама вручную. А​​И как должна​1.30.1.11​

​ Значения в этом​​, содержащего страны выбранного​ необходимо:​ нужно написать так.​

​ диапазон А1:D4. На​​ зависимости от выбранных​функция СМЕЩ() выбирает из​ выделить ячейку​ что то такое.​ номера из доп​: смысл в том,​ мне работать приходится​ ячеек «@.» (без​Alex_ST​ потом иногда получается,​ восстановиться нумерация? Вариантов​1.30.1.12​ столбце должны быть​
​ Региона:​нажать кнопку меню «Присвоить​ =ДВССЫЛ(ПОДСТАВИТЬ(A2;» «;»_»))​

​ закладке «Формулы» в​​ данных в первом​ столбца D (Потомки)​
​В8​P.S. Сереньким слева​
​ столбца?​ что моя таблица​ регулярно.​
​ кавычек) :)​: А по поводу​ что мне говорят:​ восстановления множество. ​1.30.2​
​ отсортированы. Пример: список​ =СМЕЩ(списки!$A$2;;Позиция;СЧЁТЗ(Выбранный_Регион))​ имя» (Формулы/ Определенные​
​Если список на​ разделе «Определенные имена»​ столбце выпадающего списка,​
​ все значения, у​
​на листе Таблица.​
​ пример, того, как​
​DEAD MAN​
​ очень громоздкая, и​
​Пока ничего (без​
​Сейчас пытаюсь доработать,​
​ Word’a, Оксана, попробуйте​ «А давай этот​KuklP​
​1.30.2.1​
​ сотрудников компании, отсортированный​
​Теперь через Диспетчер имен​
​ имена/ Присвоить имя);​
​ другом листе, то​ нажимаем функцию «Создать​
​ меняется выпадающий список​
​ которых уровень равен​
​ О том как​
​ оно должно в​: Без примера сложно​ уровней у списка​

​ макросов) проще и​​ чтобы нумерации любого​ конечно, но вряд​ подэтап уберём» -​
​: ДА, так гораздо​1.30.2.2​ в алфавитном порядке.​ (Формулы/ Определенные имена/​

​в поле Имя ввести​​ в формуле указываем​ из выделенного фрагмента».​ в ячейках второго​
​ 0. Из этих​ работает эта формула​ итоге выглядеть.​
​ ответить, поскольку не​ аж 4. с​
​ удобней не придумал​ уровня и количества​ ли сможете получить​ какой-нибудь из начала​ изящнее! EducatedFool респект!​1.30.2.3​
​ Перечень элементов для​ Диспетчер имен) можно​ Регионы;​ название этого листа.​ В появившемся диалоговом​ столбца, третьего, т.д.​ значений формируется выпадающий список.​ см. в разделе​Спасибо.​ видно что там​ нулями она очень​ и не нашёл.​ работали правильно :)​ что-нибудь приемлемое.​

​ списка, и всю​​ Кто больше?​1.30.3​ выпадающего списка формируется​ посмотреть все созданные​в поле Диапазон ввести​ Напишем так. =ДВССЫЛ(»Размеры!А2:А4»)​ окне оставляем галочку​Здесь разберём​Рассмотрим как формируется выпадающий список в​ статьи ниже. ​heaven33rus​

​ к чему.​​ похожа на двоичные​А в каком​
​Haken​»Word и таблицы​ нумерацию приходится перебивать​Козина О.В., Ваш​

​1.30.3.1​​ в зависимости от​ выше Имена.​ формулу​Нажимаем «ОК». Теперь​ только у строки​двухуровневый зависимый выпадающий список​ ячейке ​
​ПРИМЕЧАНИЕ​: Исправил файл и​Для примера, я​ коды и совсем​ смысле «по-другому»?​: Вот :)​ — не совместимы»​ вручную.​ пример(в соответствии с​
​1.30.3.2​ выбранной буквы алфавита.​

​Наконец сформируем выпадающий список​=списки!$A$2:ИНДЕКС(списки!$A:$A;СЧЁТЗ(списки!$A:$A))​ во втором столбце​ «В строке выше».​ в Excel​С8 ​: Функция ЕСЛИ() нужна для формирования​ более точно написал​ как-то делал файл,​ нечитаема. и это​nerv​выбирайте, кому с​ (в смысле как​Прилагаю файлик с​ правилами форума) сильно​1.31​Запрос на выборку: в​

​ (связанный список) для​Нажать ОК.​ установлены выпадающие списки,​Нажимаем «ОК». Всё, имена​.​на листе Таблица (заполняем​ списка самого верхнего​ что должно получиться.​

​ в котором необходимо​​ еще бы ничего,​: Чтобы было дерево)​ точкой, кому без​ гений и злодейство. )​ одним из проектов​ ускорит решение.​1.32​ этой структуре все​

​ ячеек из столбца​​Формула =списки!$A$2:ИНДЕКС(списки!$A:$A;СЧЁТЗ(списки!$A:$A)) подсчитывает количество​ которые меняются, в​
​ присвоены. На закладке​Например, в первом​ Страны). ​
​ уровня иерархии -​heaven33rus​

​ было сгруппировать данные​​ но есть требование​
​SerenDion​ точки, кому формулами,​Там, конечно, можно​ с готовой нумерацией​vikttur​KuklP​ перечни элементов для​ Страна налисте​ введенных Регионов в​
​ зависимости от того,​ «Формулы» нажимаем функцию​ столбце из выпадающего​В этом случае формула​ у него нет​: Посмотрите, кто разбирается​ по строкам в​ к оформлению. (((​: Всем доброго времени​ кому макросами :)​ делать таблицы и​ (там мне сказали,​

​: >>Наверняка спецы типа​​: Это мы не​
​ связанного списка содержатся​Таблица​

​ столбце​​ что написано в​ «Диспетчер имен».​ списка выбрали «Пальто».​ будет несколько другая,​ Родителя.​ в многоуровневых списках.​ пять уровней и​SerenDion​ суток. Очень нужна​Прошу обратить внимание​

​ какие-то простейшие вычисления,​​ возможно придётся убрать​ Kim, vikttur заинтересуются​ с Олечкой Козиной​ в одной таблице​.​
​А​
​ ячейках первого столбца.​Здесь перечислены все наши​ Во втором столбце​ т.к. в ней​Для ввода формулы выделите​Сделал, как мне​ столбцы по периодам.​: Может я с​

​ помощь. Создаю многоуровневый​​ на различие обработки​ но ВСЁ НАСТОЛЬКО​ пункт 1.3 (то​ Вашей задачей.​ общаемся? Сергей Пилипенко.​
​ (в базе данных).​выделяем диапазон​на листе​ Получилось так.​
​ диапазоны списков. Проверили​ появился выпадающий список​ сработают относительные ссылки:​ ячейку​ надо было, но​

​ В итоге, решил​​ другой стороны подойду).​
​ список​
​ с помощью формул​
​ КОРЯВО. ​
​ есть строки с​

​за «спецов» спасибо.​​Козина О.В.​ Сам связанный список​B5:B22​
​Списки​Копируем формулу вниз по​ всё. Можно подкорректировать​ размеров этого пальто.​=ЕСЛИ(Таблица!C$7=Списки!$F$5;​B8​
​ на 4 уровне​ с помощью сводной​ Может подскажите функцию​
​1.1​ и польз.функции. «ошибки»​тухачевский​

​ 12-ой по 22-ую).​​ Приятно, хотя и​: Это Оксаночка ))))​ реализован не в​;​(функция СЧЁТЗ()) и​ столбцу. Мы создали​ размер диапазона. Мы​ А, если в​СМЕЩ(Списки!$D$4;1;0;СЧЁТЕСЛИ(Списки!$B$5:$B$30;0));​, вызовите Диспетчер имен, и​ в выпадающем списке​ таблицы, а таблица​
​ аналогичную ВПР, но​​1.2​ при перескоке с​

​: Алекс,​​Козина О.В.​ не спец :)​KuklP​ виде выпадающего списка,​вызываем инструмент Проверка данных,​ определяет ссылку на​ двухуровневый выпадающий список​

​ уменьшили размер диапазона​​ первом столбце этой​СМЕЩ(Списки!$D$4;ПОИСКПОЗ(Таблица!B8;Списки!$C$5:$C$30;0);0;СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!B8)))​ введите формулу:​ появляется только одно​

​ с данными содержала​​ для которой непринципиальна​.​ уровня на уровень​
​спасибо​: Алексей, спасибо огромное​Но для формулистов​

​: Козина О.В., подождите,​​ а формулами на​
​устанавливаем тип данных Список,​
​ последний элемент в​ в Excel.​ «Юбка», чтобы в​ же ячейки из​

​Выражение ЕСЛИ(Таблица!С$7=Списки!$F$5; в данном случае​​Нажмите ОК. ​ значение из трех.​
​ следующие столбцы: Период/Уровень​ сортировка в столбце​.​ больше чем на​поправил​
​ за пример. Что​ задача неинтересна.​ пожалуйста. Наверняка спецы​ листе EXCEL. Пользователь​

​в поле Источник вводим:​​ столбце (функция ИНДЕКС()),​
​Другой способ сделать​ выпадающем списке не​ выпадающего списка выбрали​ выражение вернет значение​
​Опять выделите ячейку ​Я где-то ошибся​ 1/Уровень 2/Уровень 3/Уровень​ по возрастанию?​1.9​ 1 в бОльшую​но:​ то подобное сделала​Мнение по задаче​ типа Kim, vikttur​
​ задает в определенной​ =Страны.​ тем самым формируется​ связанный выпадающий список​ было пустой строки.​

Выбор из многоуровневого списка (на примере штатного расписания)

​ «Брюки», то во​​ ЛОЖЬ;​B8​ или в таких​
​ 4/Уровень 5/План/Факт/Отклонение.​Юрий М​​1.10​​ сторону (внизу выделено​
​уровень перескакивать нельзя​ и я. Просто​ — макрос с​ заинтересуются Вашей задачей.​ ячейке критерий, на​Тестируем. Выбираем с помощью​ диапазон, содержащий все​​ в Excel, смотрите​​Теперь устанавливаем​
​ втором столбце будет​Так как предыдущее выражение​, сформируйте выпадающий список​ вещах надо использовать​Владимир​
​: Тогда сформулируйте конечную​2.1​ желтым)​Haken​ я подумала, может​ таким, наверное, справится​
​ Или Вас интересует​ основе которого из​​ выпадающего списка в​​ значения Регионов. Пропуски​​ в статье «Как​​первый выпадающий список в​ выпадающий список с​​ =ЛОЖЬ, то работает​​ на основе Проверки​ кардинально другой подход?​: Настраиваемую сортировку пробовали?​
​ задачу.​и т.д​Конечно, при наличии​

​: Я как-то недавно​​ есть способ как​ — отслеживание последней​

​ решение в ВБА?​​ базы данных выбираются​ ячейке​

​ в столбце​​ сделать связанные выпадающие​ ячейки столбца А​ размерами брюк.​ второе условие функции ЕСЛИ(),​ данных. В поле​kim​ Прикрепленные файлы настраиваемая​

​Z​​все хорошо, пока​ «до 900 работ​ делал упрощенную версию​ это сделать без​
​ измененной ячейки и​Козина О.В.​ строки, удовлетворяющие этому​A5​
​А​ списки в Excel​

​.​​Итак, сделаем две​

Многоуровневый список, сортировка по возрастанию

​ т.е. выражение СМЕЩ(Списки!$D$4;ПОИСКПОЗ(Таблица!B8;Списки!$C$5:$C$30;0);0;СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!B8))​​ Тип данных укажите​: Может так подойдет. ​ сортировка.jpg (70.57 КБ)​: -​
​ не делаю сортировку. ​
​ всех уровней» быстрее​
​ для себя.​
​ создания дополнительных столбцов.​
​ пересчет нумерации.​
​: Сергей, меня заинтересует​
​ критерию. Выбранные строки​
​Регион – Америка,​
​не допускаются. Также​ легко».​
​У нас, в​
​ таблицы. Саму таблицу​
​функция СМЕЩ() выбирает из столбца D​
​ Список, в поле​
​heaven33rus​
​Владимир​
​Igor67​
​результат​ будет обычными формулами​Недостатки: максимальное количество​Guest​Для формул задача​ любое решение.​

​ помещаются в отдельную​​ вызываем связанный список​

​ для формирования динамического​​В статье рассмотрен улучшенный​
​ примере, мы выделяем​
​ сделаем на странице​
​ (Потомки) все значения,​
​ Источник введите формулу​

​: а как сделали,​​: Прошу прощения. С​:​1.1​ обсчитываться​ подпунктов — 9​
​: интересно как это​

​ непосильна. Нарисовать формулу​​Михаил С.​ таблицу.​

​ в ячейке​​ диапазона можно использовать​ вариант Связанного списка.​

​ диапазон A2:A3. И,​​ книги «Таблица». А​ у которых Родитель​ =Выбор.​

​ подскажете? на будущее?​​ 1.10 тоже не​SerenDion,​1.10​Формуляр​ (т.е. нумерация после​ без доп столбцов​ для последовательности от​: А как определить,​Козина О.В.​B5​
​ функцию СМЕЩ().​В случае, если содержание Связанного​ через «Проверки данных»​
​ списки сделаем на​ = Америка. ​С помощью Маркера заполнения​

​kim​​ получается..​а Вы используете​1.2​: Выбор реализован через​

​ 10 на любом​​машина без пользователя​ 1 до, например,​ сколько уровней в​: Подскажите пожалуйста как​и балдеем –​Теперь создадим Именованную формулу​ списка зависит от​ на закладке «Данные»,​ странице «Размеры». У​Для этого выражение ПОИСКПОЗ(Таблица!B8;Списки!$C$5:$C$30;0) возвращает номер​ скопируйте ячейку вправо​: А что там​

​SerenDion​​ ВПР по неточному​.​ несколько листбоксов со​ уровне будет неправильно​ не назначит правильно​ 30.30.30.30.30.30 не проблема,​ каждом случае?​

​ в Excel можно​​ появился список стран​ Позиция для определения​

​ большого количества значений​​ устанавливаем выпадающие списки.​

​ нас есть такая​​ позиции первой ячейки​​ и вниз (или​​ подсказывать? Просто подрихтовал​: Вот пример. на​ соответствию с параметром​.​ связанными списками.​ работать)​ уровень​ если последовательность непрерывна​KuklP​ сделать нумерацию (этапов,​ для Региона Америка:​ позиции, выбранного пользователем​ ячеек (>5), то​ Тип данных –​ таблица.​ в столбце Родитель,​

​ вверх и вправо): выпадающие​​ некоторые формулы и​ скорую руку.​ ИСТИНА? Если Вы​и т.д.​Исходный список на​При удалении/добавлении строк​у меня как-то​ или есть какой-либо​: Оксаночка, я рад​ подэтапов и работ)​ США, Мексика… В​ региона, в созданном​ нам потребуется создать​ выбираем «Список». А​
​И мы сделали такие​ содержащей значение Америка.​ списки всех уровней будут​ ссылки в Вашем​

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

​ отличие от Связанного​​ выше диапазоне Регионы​ такое же количество​

​ в строке «Источник»​​ списки.​ Это позиция номер​ сформированы автоматически. ​ диспетчере имен. Зайдите​: Igor67 пишет:​ и указываете параметр​

​ в неверном формате​​ШР​ заново по столбцу​Alex_ST​ В показанном примере​ НО! На форуме​

​ удаления одной из​​ списка – без​ =ПОИСКПОЗ(A5;Регионы;0). Т.к. в​ Именованных диапазонов. Занятие​ указываем имя диапазона.​
​Внимание!​ 4. Именно с​Наполнять выпадающие списки будем​ туда, все увидите.​SerenDion, а Вы​ ЛОЖЬ, то для​ данных. но какой​.​В самую первую​: Да, здОрово!​ разное количество вложений,​ есть УМНИЧКИ, не​ строк нумерация пересчитывалась​ пустых строк.​

​ формуле использована относительная​​ это мало увлекательное,​ Например, «=Наименование_товара».​В списках названия​

​ этой позиции, но​​ из таблицы на​ Кстати, списки Ваши​ используете ВПР по​

​ ВПР порядок следования​​ формат выбрать, чтоб​Исходная структура хороша​

​ строчку нужно вручную​​Такие сложные формулы,​
​ да еще «в​ до конца перечисленные​ заново! Нумерация возможна​Теперь заполняем следующую строку.​ адресация, то важно​ поэтому лучше настроим​Подробнее, как установить​ столбцов (В, С,​ из другого столбца,​ листе Списки (см.​
​ ни какие не​ неточному соответствию с​
​ данных не важен.​
​ сортировалось корректно ума​

​ тем, что не​​ вбить цифру 1.​ да ещё и​ случае удаления одной​

​ мной(типа Kim, vikttur,​​ до 6 уровней​

Калькулятор на основе многоуровневых выпадающих списков (Формулы/Formulas)

​ Выбираем в ячейке​​ перед созданием формулы​
​ Связанный список так,​ выпадающий список, смотрите​ D) должны полностью​
​ будет формироваться наш выпадающий​ файл примера):​ четырехуровневые или пятиуровневые,​
​ параметром ИСТИНА? Если​ Это ограничение когда​ не приложу.​ содержит дублирующихся наименований​vikttur​ формулы массива, для​
​ из строк нумерация​ ну не всех​
​ (1.1.1.1.1.1) и всего​
​A6​ сделать активной ячейку​ чтобы для него​ в статье «Выпадающий​ совпадать с названием​
​ список;​Для наглядности различные уровни​ а обычные двухуровневые​ Вы ищите точное​ Вам нужно найти​Юрий М​ и удобна для​: В который раз​ меня, к сожалению,​ пересчитывалась заново».​ формулистов перечислил!), которые​
​ до 900 работ​Регион – Африка,​B5​ было удобно добавлять​
​ список в Excel».​

​ в первом столбце​​Теперь для выпадающего списка осталось указать​ выделены Условным форматированием.​ — что гораздо​

​ соответствие и указываете​​ ближайшее меньшее значение.​: Сортировка корректная.​
​ визуального восприятия. К​ убеждаюсь, что нельзя​ недоступны. Я всё​Alex_ST​ решат Вашу проблему​ всех уровней.​
​ вызываем связанный список​на листе​ новые перечни элементов​Устанавливаем​

​ (у нас –​​ его длину. Для​

​Заполнение таблицы на листе Списки​​ проще с реализацией.​ параметр ЛОЖЬ, то​

​ По вопросу еще​​Niff-Nif​ тому же, легко​ быть категоричным в​ больше по-старинке, на​: Я как-то давно​ и быстрей и​Например:​ в ячейке​Таблица​ (См. файл примера).​зависимые выпадающие списки в​ это наименование товара​

Как сделать многоуровневый список в эксель

Здравствуйте. Подскажите пожалуйста, стоит задача сделать в excel, а лучше в google spreadsheets сделать подобную конструкцию

Допустим, менеджер выбирает тип товара из выпадающего списка «Слоны»

«Слоны» делятся по цветам и могут быть:

— розовые
— синие
— чёрные

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

Косноязычно, конечно получилось. Но, я думаю в примере отобразится лучше)

Суть такая, каждый следующий шаг сужает поиск.

И в конце, по выбранным типам и характеристикам — менеджер видит конечную цену

Здравствуйте. Подскажите пожалуйста, стоит задача сделать в excel, а лучше в google spreadsheets сделать подобную конструкцию

Допустим, менеджер выбирает тип товара из выпадающего списка «Слоны»

«Слоны» делятся по цветам и могут быть:

— розовые
— синие
— чёрные

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

Косноязычно, конечно получилось. Но, я думаю в примере отобразится лучше)

Суть такая, каждый следующий шаг сужает поиск.

И в конце, по выбранным типам и характеристикам — менеджер видит конечную цену byte32

Сообщение Здравствуйте. Подскажите пожалуйста, стоит задача сделать в excel, а лучше в google spreadsheets сделать подобную конструкцию

Допустим, менеджер выбирает тип товара из выпадающего списка «Слоны»

«Слоны» делятся по цветам и могут быть:

— розовые
— синие
— чёрные

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

Косноязычно, конечно получилось. Но, я думаю в примере отобразится лучше)

Суть такая, каждый следующий шаг сужает поиск.

И в конце, по выбранным типам и характеристикам — менеджер видит конечную цену Автор — byte32
Дата добавления — 29.06.2018 в 14:02

Как сделать выпадающий список в Excel

Как сделать простой выпадающий список в Excel через функцию вставки

Элементарный вариант: не нуждается в какой-либо предварительной настройке и доступен благодаря стандартной функции вставки. Он позволяет выбрать из списка ранее введённые значения и быстро добавить их. Однако это работает, только если данные содержатся в столбце без пропусков, а ввод осуществляется по порядку.

Поставьте курсор в следующую пустую ячейку диапазона. В нашем примере это B7.

Сделайте правый клик мышью и укажите «Выбрать из раскрывающегося списка» либо просто нажмите на клавиатуре Alt + «стрелка вниз».

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

Как сделать выпадающий список в Excel из диапазона данных

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

Выделите ячейку, в которой должен быть раскрывающийся список (у нас это H3), переключитесь на вкладку «Данные» и кликните «Проверка данных».

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

Нажмите OK, и в заданной ячейке появится выпадающий список с нужными значениями.

Как сделать выпадающий список в Excel из именованного диапазона

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

Для начала создайте именованный диапазон. Для этого выделите ячейки с будущими пунктами выпадающего списка и озаглавьте, вписав имя в поле с названиями ячеек, а затем нажмите Enter. Обязательное условие: первым символом должна быть буква, нельзя использовать пробелы (ставьте вместо них «_»).

Теперь поставьте курсор в ячейку, где хотите сделать список, и на вкладке «Данные» нажмите «Проверка данных».

В графе «Тип данных» выберите «Список», а в качестве источника укажите название диапазона, поставив перед ним «=». В нашем примере вышло «=бренды».

Выпадающий список с элементами из именованного диапазона готов.

Как сделать выпадающий список в Excel с данными другого листа или файла

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

Откройте оба файла, затем выделите ячейку с будущим списком и на вкладке «Данные» кликните «Проверка данных».

Выберите «Список», в качестве источника укажите такую формулу: =ДВССЫЛ("[имя_файла]имя_листа!диапазон") То есть внутри кавычек должно быть название файла с данными для списка в квадратных скобках, потом без пробела название листа, а затем так же без пробела восклицательный знак и обозначение диапазона. В нашем примере будет так: =ДВССЫЛ("[Книга1]Лист1!A3:A9")

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

Как сделать выпадающий список в Excel из таблицы

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

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

Выберите любой вариант дизайна, отметьте в открывшемся окошке опцию «Таблица с заголовками» и нажмите OK.

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

Кликните по ячейке, в которой должен быть список, и нажмите «Проверка данных» на вкладке «Данные».

Укажите «Список» как тип данных, а для источника введите такую формулу: =ДВССЫЛ("имя_таблицы") В нашем примере будет так: =ДВССЫЛ("Двери")

Список сразу же заработает и будет обновляться при удалении и добавлении элементов.

Как сделать выпадающий список в Excel с поиском

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

Если вкладка «Разработчик» не отображается, для начала следует включить её. Для этого раскройте меню и выберите «Другие команды…».

Кликните «Настроить ленту», затем включите пункт «Разработчик» и нажмите OK.

На вкладке «Разработчик» кликните «Вставить» и выберите «Поле со списком» из элементов ActiveX.

«Нарисуйте» с помощью курсора ячейку списка, после чего кликните по ней правой кнопкой мыши и выберите «Свойства».

Найдите строку ListFillRange, введите в неё диапазон значений для списка и нажмите Enter. В нашем примере это B6:B9.

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

Как сделать связанный выпадающий список в Excel

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

Подготовьте столбцы с данными: в шапках пункты первого, родительского списка, а под ними пункты связанного с ним второго. Выделите первый столбец с шапкой и на вкладке «Главная» кликните «Форматировать как таблицу».

Выберите любой из стилей, а затем в появившемся окошке включите опцию «Таблица с заголовками» и нажмите OK.

Кликните по любой из ячеек таблицы, а затем присвойте ей имя, вписав его в поле «Имя таблицы» и нажав Enter. Это будет пункт первого списка, в нашем примере — «Компьютеры». Таким же образом создайте таблицы для остальных столбцов и переименуйте их.

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

Укажите в качестве типа «Список», а в качестве источника выделите шапки столбцов с категориями, которые станут пунктами родительского перечня.

Далее установите курсор в ячейку, в которой будет второй список, кликните «Проверка данных», выберите вариант «Список», а в поле «Источник» впишите =ДВССЫЛ и адрес ячейки родительского списка в скобках. То есть =ДВССЫЛ(H3) для нашего примера.

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

А если указать «Наушники» — будут отображены соответствующие названия аксессуаров из одноимённого столбца.

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

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

Как настроить группировку

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

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

На этом подготовительный этап можно считать оконченным.

Группировка по строкам в Excel

Осуществим группировку информации по рядам.

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

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

Теперь переходим в раздел «Структура». Для этого надо сделать клик по соответствующей кнопке, располагаемой в правой части ленты. Кликаем по кнопке «Группировать». Там появится специальное меню, в котором и нажимаем эту кнопку.

После этого программа предложит метод группировки – по рядам или колонкам. Нужно выбрать тот, который подходит больше всего в конкретной ситуации. То есть, в нашем случае, по строкам. Подтверждаем свои действия путем нажатия клавиши «ОК». После этого процесс создания группы может считаться законченным. Свернуть ее можно с помощью знака минуса.

После того, как группа спрячется, знак минуса будет заменен на плюс. Клик на него позволит развернуть группу.

Группировка по столбцам в Excel

Группировка по колонкам осуществляется аналогично, как и по строкам, только тип группировки нужно выбрать другой в соответствующем диалоговом окне. Но все же, давайте по порядку.

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

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

Создание вложенных групп

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

1. Если материнская группа была свернута, ее нужно развернуть.
2. Те ячейки, которые надо отнести к подгруппе, надо выделить.
3. После этого открываем вкладку «Данные», переходим в раздел «Структура» и там нажимаем «Группировать».
4. Затем выбираем способ группировки – по строкам или по колонкам, после чего подтверждаем свои действия.

После этого у нас появится дополнительная вложенная группа. Сколько максимально подгрупп может создавать пользователь? Количество неограниченное. Мы можем увидеть, что сверху над значками минуса появились цифры. Они позволяют быстро переключаться между разными уровнями вложенности.

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

Разгруппирование в Эксель

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

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

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

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

1. Сначала осуществляем настройку, соответствующую первому разделу этой статьи.
2. После этого создаем заголовок группы. Для этого делаем дополнительную строку или колонку в зависимости от того, какие данные мы хотим сгруппировать.
3. Присваиваем в этой колонке или строке название группы.
4. Выделяем ячейки, которые нужно объединить в одну группу.
5. Переходим во вкладку «Данные», и там находим группу инструментов «Структура».
6. Затем нажимаем на кнопку «Группировать», выбираем тип группировки, который нужен в конкретной ситуации и нажимаем на кнопку ОК.
7. После этого определяемся с тем, как должна называться вторая группа и создаем дополнительную строку с этими данными.
8. Выделяем ячейки подгруппы, которую нужно выделить. Заголовок, как и в предыдущем примере, не включаем в группу.
9. Повторяем шаги 5 и 6 применительно ко второму уровню группы.
10. Повторяем шаги 7-9 применительно к каждому следующему уровню группы.

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

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

Весь описанный выше метод – пример ручной группировки данных электронной таблицы. Но есть способ автоматизировать деление информации на группы. Правда, выборку для группировки будет создавать сама программа. Но во многих ситуациях Excel осуществляет структурирование информации правильно.

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

Когда оптимальнее всего использовать автоматическую группировку? Лучше всего она работает, когда пользователь работает с формулами. В этом случае ошибок, как правило, не возникает.

Что же нужно делать для того, чтобы сгруппировать данные автоматически? Нужно открыть раздел «Группировать» и там выбрать пункт «Создание структуры». Все остальное Эксель сделает за пользователя.

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

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

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

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

Один из способов осуществления сортировки данных – окрашивать информацию, которая относится к одной группе, специальным цветом. Для этого нужно открыть вкладку «Данные» и там найти раздел «Сортировка и фильтр». Дальнейшие действия зависят от используемой версии офисного пакета. Нужная нам функция может называться или «Настраиваемая» или «Сортировка». После того, как мы сделаем клик по соответствующему пункту, у нас должно появиться всплывающее окно.

Затем переходим в раздел «Столбец». Там находим группу «Сортировать по», и далее выбираем ту колонку, которая нужна в нашей ситуации. Далее задаем критерий, по которому должна осуществляться сортировка данных. Например, им может выступать цвет ячеек. Если мы планируем его использовать, надо предварительно окрасить подходящие ячейки соответствующим цветом.

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

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

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

Excel дает возможность осуществлять группировку значений, основываясь на значении, содержащемся в определенной ячейке. Это нужно, например, когда надо осуществить поиск полей, в которых содержатся определенные имена или данные другого типа (например, коды). Как это сделать? Первые два шага соответствуют предыдущему разделу, только вместо критерия в виде цвета нужно настроить значение, по которому будет осуществляться группировка.

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

Как упростить большую таблицу Эксель

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

1. В начале каждого диапазона должен быть заголовок и не должно быть пустых ячеек. Заголовки должны включать одинакового типа данные.
2. Предварительно рекомендуется отсортировать данные в алфавитном порядке.
3. Воспользуемся командой «Промежуточные итоги», которая находится в том же разделе – «Группировать».

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