Как сделать иерархический список в excel
Для создания иерархического списка в Excel, следуйте этим шагам:
1. Вставьте данные в таблицу, откройте новую книгу Excel и введите данные в столбцы и строку.
2. Пометьте первый уровень расчета значением, объединение двух или нескольких ячеек, чтобы создать многорядность. Чтобы объединить несколько ячеек, выделите необходимые ячейки и нажмите правую кнопку мыши. В контекстном меню выберите пункт «Объединить и центрировать».
3. Добавьте второй уровень, кликнув на ячейку под первым уровнем и вводя значения. Объедините нужные ячейки.
4. Продолжайте добавлять дополнительные уровни, повторяя процесс добавления значений и объединения ячеек, если это необходимо.
Важно помнить, что иерархические списки могут быть созданы только в столбцах, а не в строках.
— Используйте форматирование шрифта и цвета, чтобы сделать таблицу более понятной и привлекательной.
— Введите формулу автоматического заполнения, чтобы скопировать значения в ячейки. Например, если первое значение на первом уровне ячейки A2, то введите «= A2» в ячейке A3, и выше, где требуется автоматическое заполнение.
— Используйте формулу «IF» для создания логических операций. Например, если вы хотите, чтобы значение на втором уровне ячейки B2 изменялось в зависимости от значения первого уровня, то введите «= IF (A2 = «значение1», «значение2», «значение3″)».
— Используйте формулу «SUM» для автоматической суммы значений в ячейках каждого уровня. Например, для суммирования всех значений на уровне 2 введите «= SUM (B2: B10)».
— Добавляйте комментарии, чтобы ваш список был более информативным и понятным для других пользователей.
Как быстро создать многоуровневые (каскадные) выпадающие списки в Excel
Мир вокруг нас полон иерархических структур. Зачастую при разработке форм в Microsoft Excel мы сталкиваемся с задачей организации выбора показателей, имеющих некую иерархию, например, адреса (страна-город-улица-дом), организационная структура предприятия (департамент – управление – отдел-сотрудник) или же номенклатура товаров магазина. Почти каждый пользователь Excel умеет создавать в ячейке выпадающий список, более продвинутые могут создать второй, связанный с первым выпадающий список.
А можно ли создавать более сложные системы списков в Excel? На самом деле да, причем количество уровней таких списков ничем не ограничено и определяется только вашей потребностью. Рассмотрим методику, которая позволяет быстро и без особого труда строить многоуровневые выпадающие списки.
Основное преимущество данной методики состоит в том, что полученная система является полностью динамической, т.е. в неё можно добавить любые данные, даже целые ветви иерархии, и они автоматически попадут в выпадающие списки.
Основа методики – организация исходной информации в «Умных таблицах», т.е. специально структурированных объектах Excel. «Умную таблицу» можно создать клавишами Ctrl+T или кнопкой Таблица на вкладке Вставка, после чего таблице необходимо задать имя (во всплывающей при её выделении вкладки Конструктор). Основное требование – названия нижестоящих таблиц должны соответствовать элементам вышестоящих . В качестве примера рассмотрим фрагмент 6-уровневой иерархии товаров магазина, оформленной в “Умных таблицах”:
Остальные данные организуются по такому же принципу. После того, как данные организованы, необходимо непосредственно создать выпадающие списки, которые задаются специальными формулами, вводимыми в поле Источник в меню Проверка данных – Список:
Всего возможны три вида выпадающих списков:
1 уровень (первичный, не связанный ни с чем список)
2 уровень (имеющий одну связь на один вышестоящий выпадающий список: имя таблицы или имя поля)
3 уровень (имеющий две связи на вышестоящие списки: на имя таблицы и имя поля)
Обратите внимание! Все уровни иерархии моделируются с использованием этих трёх видов выпадающих списков, сгруппированных в различных комбинациях.
Итак, вот формулы, которые подставляются в Проверку данных в зависимости от конкретной ситуации. Обратите внимание, вместо красного текста подставляются непосредственно названия таблиц и полей, а вместо оранжевого – ставится ссылка на выпадающий список вышестоящего уровня:
Выпадающий список 1 уровня:
Создать список конкретных умных таблиц книги: Таблица1 ; Таблица2 ; Таблица3
Создать список полей конкретной таблицы: =ДВССЫЛ(“ Таблица1 “&”[#Заголовки]”)
Создать список элементов конкретного поля конкретной таблицы: =ДВССЫЛ(“ Таблица1 “&”[ Поле1 ]”)
Выпадающий список 2 уровня:
Создать список полей таблицы, выбираемой в вышестоящем списке: =ДВССЫЛ( A1 &”[#Заголовки]”)
Создать список элементов выбираемого поля конкретной таблицы: =ДВССЫЛ(“ Таблица1 “&”[“& А1 &”]”)
Выпадающий список 3 уровня
Создать список элементов выбираемого поля выбираемой таблицы: =ДВССЫЛ( A1 &”[“& B1 &”]”)
Как в excel сделать иерархию
Группировка данных в Excel – придаем таблицам стройности
Здравствуйте, друзья. Думаю, каждому, кто работал в Эксель, попадались таблицы со структурой данных. Группировка в Эксель, чаще всего, применяется, когда есть организация со структурой, и мы оцениваем некоторые показатели согласно этой структуры. Например, продажи по Компании в целом, по региональным представительствам, по менеджерам. Посмотрите на картинке, как это может быть без группировки:
На рисунке детальный пример отчета о продажах по структурным подразделениям подневно. А что если нужно предоставить руководителям отчет в разрезе только лишь департаментов и помесячно, без детализации? Чтобы было вот так:
Согласитесь, такой вид таблицы более нагляден и показателен для анализа работы Компании в целом. Но как получить такую таблицу достаточно быстро, не скрывая и не удаляя ячейки? Очень просто, задайте структуру документа.
На рисунке выше я привел уже структурированный документ, с маркерами группы. В таблице сгруппированы менеджеры по регионам и регионы в компании, а так же, дни недели в месяцы. Чтобы скрыть какую-то отдельную группу, кликните на значке «минус» в итоговой строке этой группы». Чтобы раскрыть – на значке «плюс». Чтобы отобразить какой-то определенный уровень – нажмите на его номер в верхнем левом углу листа.
Обратите внимание, данные сгруппированы в несколько уровней. Каждый последующий вложен в предыдущий. Всего Эксель позволяет создать до восьми уровней в строках и восьми – в столбцах.
Как сгруппировать данные в Excel
Есть два способа создать структуру листа: автоматический и ручной.
Автоматическое создание структуры в Excel
Программа может попытаться создать структуру автоматически. Для этого нажмите на ленте: Структура – Группировать – Создать структуру .
Если у Вас правильно и логично записаны формулы суммирования, структура будет создана правильно. У меня эта функция часто срабатывает правильно, поэтому сначала я пробую именно автоматическое создание. Структура из примера отлично создалась автоматически.
Ручное создание структуры
Создание структуры вручную позволяет держать весь процесс под контролем и гарантированно получить правильный результат. Чтобы структурировать лист вручную, выполните такие действия:
- Оформите и наполните таблицу, создайте итоговые строки и столбцы
- Кликните на маленькой пиктограмме со стрелкой в нижнем правом углу ленточной группы Данные – Структура . В открывшемся окне настройте расположение строк и столбцов итогов
- Выделите строки или столбцы, которые нужно группировать (не выделяйте итоговые). Делайте это с каждой группой по очереди, начиная с самых «глубоких». В моем примере это фамилии менеджеров.
- Нажмите на ленте Структура – Группировать (ли нажмите Alt+Shift+стрелка вправо ). Будет создана группировка
- Повторяйте п.3-4 до полного структурирования данных. Повторюсь, начинайте с подчиненных групп, потом переходите на уровень выше. Т.е. в таблице из примера мы поочередно сгруппируем менеджеров в филиалы, потом филиалы в компанию.
Когда Вы вставляете новые строки и столбцы в структурированную таблицу – будьте осторожны. Если, например, добавляется новый менеджер в группу, вставляйте строку где-то между сгруппированными строками, не первым элементом группы и не последним. В противном случае, новая строка не впишется в структуру.
При копировании диапазона ячеек, скрытые строки и столбцы не копируются. Но когда они скрыты группировкой – все равно копируются. Чтобы выделить только данные, отображенные на экране, выделите нужный диапазон и нажмите F5 . В открывшемся окне выберите Выделить – Только видимые ячейки . Теперь ячейки, скрытые группировкой, не будут скопированы.
Если Вам больше не нужна структура – удалите ее. Для этого выполните на ленте Данные – Структура – Разгруппировать – Удалить структуру .
Группированные данные, структурированное рабочее пространство – не только добавляют практичности Вашим таблицам, это правило хорошего тона, когда одну и ту же таблицу можно легко посмотреть как в развернутом виде, так и в детальном.
Вот и все, тренируйтесь и применяйте на практике структурирование данных в Экселе. Приглашаю Вас комментировать изложенный материал, задавайте Ваши вопросы и делитесь опытом!
Следующая статья будет посвящена подключению внешних данных и их консолидации. Это неотъемлемая часть работы большинства пользователей Excel. Присоединяйтесь к чтению!
Добавить комментарий Отменить ответ
8 комментариев
Добрый день!
Как в книге создать 2 группировки друг за другом, чтобы когда они свернуты не отображались страницы.
Например, в таблице 15 строк. 3 всегда отображаются. Затем 4 строки в группировке и следующие 4 строки в группировке. Надо чтобы,когда обе группировки собраны из них ничего не отображалось.
Здравствуйте, Кир.
Для каждой группировки нужно определять итоговый столбец, который располагается либо над группируемым участком, либо под ним. Поскольку у Вас две группы строк примыкают друг к другу, нужно для верхнего участка установить итоги над данными, для нижнего — под данными. Как это делается — описано вначале пункта «Ручное создание структуры».
Добрый день!
Как сделать чтобы группировки были разного цвета( например 1 группа где знак (+) красного. 2 группа где знак (+) зеленого итд)
Дмитрий, здравствуйте. Если Вы хотите, чтобы именно строка итогов отличалась по цвету — лучше всего воспользоваться Условным форматированием
Здравствуйте! Почему при создании структуры с первого столбца в документе не отображается плюсик? Только точки над структурируемыми столбцами и цифры уровня структуры… Что делать, если структура нужна именно с 1го столбца? Excel 2016. Спасибо.
Здравствуйте, Елена. Не отображается плюсик, потому что он проставляется над столбцом промежуточных итогов. Например, в столбцах у Вас данные по месяцам, и они сворачиваются в итоговые цифры года. Плюсик будет над столбцом соответствующего года.
Итоговый столбец может быть слева или справа от группируемого массива. У Вас в настройках выставлено «слева», однако столбца слева не существует, Вы группируете начиная с первого. Потому, плюсика и нет. Вы можете пойти двумя путями:
1. Скрывать и раскрывать группировку кнопками уровней в верхнем левом углу окна
2. Выбрать расположение столбца итогов справа от группируемого массива. Тогда плюсик будет отображаться в следующем столбце после группируемых. Как это сделать — я рассказал в пункте «Ручное создание структуры»
Спасибо.
Научился группировать столбцы за 15 секунд.
Создание иерархии
Если вы хотите проиллюстрировать иерархические отношения по вертикали или по горизонтали, вы можете создать графический элемент SmartArt, в котором используется макет иерархии, например иерархия с подписями. Иерархическая структура, представляющая собой последовательность упорядоченных групп людей или элементов в системе. С помощью графического элемента SmartArt в Excel, Outlook, PowerPoint или Word вы можете создать иерархию и включить ее на лист, сообщение электронной почты, презентацию или документ.
Важно: Если вы хотите создать организационную диаграмму, создайте графический элемент SmartArt, используя макет организационной диаграммы.
Примечание: Снимки экрана, описанные в этой статье, были сделаны в Office 2007. Если у вас есть другая версия, представление может слегка отличаться, но если не указано иное, функция не будет иметь такой же вид.
На вкладке Вставка в группе Иллюстрации нажмите кнопку SmartArt.
В коллекции Выбор графического элемента SmartArt выберите элемент Иерархия, а затем дважды щелкните Макет иерархии (например, Горизонтальная иерархия).
Для ввода текста выполните одно из следующих действий.
В области текста щелкните элемент [Текст] и введите содержимое.
Скопируйте текст из другого места или программы, в области текста щелкните элемент [Текст], а затем вставьте скопированное содержимое.
Примечание: Если область текста не отображается, щелкните элемент управления.
Щелкните поле в графическом элементе SmartArt и введите свой текст.
Примечание: (ПРИМЕЧАНИЕ.) Для достижения наилучших результатов используйте этот вариант после добавления всех необходимых полей.
Щелкните графический элемент SmartArt, в который нужно добавить поле.
Щелкните существующее поле, ближайшее к месту вставки нового поля.
В разделе Работа с рисунками SmartArt на вкладке Конструктор в группе Создать рисунок щелкните стрелку под командой Добавить фигуру.
Если вкладка Работа с рисунками SmartArt или Конструктор не отображается, выделите графический элемент SmartArt.
Выполните одно из указанных ниже действий.
Чтобы вставить поле на том же уровне, что и выбранное поле, но после него, выберите команду Добавить фигуру после.
Чтобы вставить поле на том же уровне, что и выбранное поле, но перед ним, выберите команду Добавить фигуру перед.
Чтобы вставить поле на один уровень выше выбранного поля, выберите команду Добавить фигуру над.
Новая ячейка принимает место в выбранном поле, а выбранное поле и все поля прямо под ним помещаются на один уровень вниз.
Чтобы вставить поле на один уровень ниже выбранного поля, выберите команду Добавить фигуру под.
Новое поле будет добавлено после другого поля на том же уровне.
Чтобы удалить поле, щелкните его границу и нажмите клавишу DELETE.
Если вам нужно добавить поле в иерархию, экспериментируйте с добавлением поля до, после, сверху или ниже выделенного поля, чтобы получить нужное место для нового поля.
Несмотря на то, что вы не можете автоматически соединить два поля верхнего уровня с линией в макетах иерархии, например в горизонтальной иерархии, вы можете имитировать этот вид, добавив поле к графическому элементу SmartArt, а затем нарисовав линию , чтобы соединить поля.
Чтобы добавить поле из области текста, выполните указанные ниже действия.
Поместите курсор в начало текста, куда вы хотите добавить фигуру.
Введите нужный текст в новой фигуре и нажмите клавишу ВВОД. Чтобы добавить отступ для фигуры, нажмите клавишу TAB, а чтобы сместить ее влево — клавиши SHIFT+TAB.
Перемещение поля в иерархии
Чтобы переместить рамку, щелкните ее, а затем перетащите поле в новое место.
Чтобы фигура перемещалась с очень маленьким шагом, удерживайте нажатой клавишу CTRL и нажимайте клавиши со стрелками.
Переход на другой макет иерархии
Щелкните правой кнопкой мыши иерархию, которую вы хотите изменить, и выберите команду изменить макет.
Нажмите кнопку Иерархия, а затем выполните одно из указанных ниже действий.
Для отображения иерархических отношений сверху вниз и группировки по иерархии щелкните элемент Иерархия с подписями.
Для отображения групп данных сверху вниз и иерархий в каждой группе щелкните элемент Иерархия таблиц.
Чтобы показать, как иерархические отношения продвигается между группами, нажмите кнопку список иерархий.
Чтобы отобразить иерархические отношения горизонтально, щелкните Горизонтальная иерархия.
Чтобы отобразить иерархические отношения горизонтально и иерархически, щелкните Горизонтальная иерархия с подписями.
Примечание: Чтобы изменить макет SmartArt, можно также выбрать нужный параметр в разделе Работа с рисунками SmartArt на вкладке Конструктор в группе Макеты. При выборе варианта макета можно предварительно просмотреть, как будет выглядеть графический элемент SmartArt.
Чтобы быстро добавить внешний вид и оформление в графический элемент SmartArt, вы можете изменить цвета или применить к иерархии стиль SmartArt . Вы также можете добавить эффекты, такие как свечение, сглаживание или объемные эффекты.
К полям в графических элементах SmartArt можно применять цветовые вариации из Цвета темы.
Щелкните графический элемент SmartArt, цвет которого нужно изменить.
В разделе Работа с рисунками SmartArt на вкладке Конструктор в группе Стили SmartArt нажмите кнопку Изменить цвета.
Если вкладки Работа с рисунками SmartArt или Конструктор не отображаются, проверьте, выбран ли графический элемент SmartArt.
Выберите нужную комбинацию цветов.
Совет: (ПРИМЕЧАНИЕ.) При наведении указателя мыши на эскиз можно просмотреть, как изменяются цвета в графическом элементе SmartArt.
В графическом элементе SmartArt щелкните правой кнопкой мыши строку или рамку, которую вы хотите изменить, и выберите команду Формат фигуры.
Чтобы изменить цвет границы поля, нажмите кнопку Цвет линии, нажмите кнопку Цвет и выберите нужный цвет.
Чтобы изменить тип границы фигуры, щелкните Тип линии и задайте нужные параметры.
Щелкните правой кнопкой мыши границу фигуры и выберите команду Формат фигуры.
Щелкните область Заливка и выберите вариант Сплошная заливка.
Нажмите кнопку Цвет и выберите нужный цвет.
Чтобы указать степень прозрачности фонового цвета, переместите ползунок Прозрачность или введите число в поле рядом с ним. Значение прозрачности можно изменять от 0 (полная непрозрачность, значение по умолчанию) до 100 % (полная прозрачность).
Стиль SmartArt — это сочетание различных эффектов, например стилей линий, рамок или трехмерных эффектов, которые можно применить к полям графического элемента SmartArt для придания им профессионального, неповторимого вида.
Щелкните графический элемент SmartArt, стиль SmartArt которого нужно изменить.
В разделе Работа с рисунками SmartArt на вкладке Конструктор в группе Стили SmartArt выберите стиль.
Чтобы отобразить другие стили SmartArt, нажмите кнопку Дополнительно .
Если вкладка Работа с рисунками SmartArt или Конструктор не отображается, выделите графический элемент SmartArt.
(ПРИМЕЧАНИЕ.) При наведении указателя мыши на эскиз становится видно, как изменяется стиль SmartArt в рисунке SmartArt.
Вы также можете настроить графический элемент SmartArt, перемещая поля, изменяя размер полей, добавляя заливку и эффекти добавляя рисунки.
Если вы используете PowerPoint, вы можете анимировать иерархию, чтобы выделить каждое поле, каждую ветвь или каждый уровень иерархии.
Щелкните графический элемент SmartArt, который вы хотите анимировать.
На вкладке анимация в группе анимация нажмите кнопку анимация, а затем выберите один из переключателей по ветке.
Примечание: При копировании иерархии, к которой применена анимация, на другой слайд также копируется анимация.
Многоуровневый связанный список в EXCEL
Для моделирования сложных иерархических данных создадим Многоуровневый связанный список.
Потребность в создании иерархических данных появляется при решении следующих задач:
- Отдел – Сотрудники отдела . При выборе отдела из списка всех отделов компании, динамически должен формироваться список, содержащий всех сотрудников этого отдела (двухуровневая иерархия);
- Город – Улица – Номер дома . При заполнении адреса проживания из списка городов нужно выбирать город , затем из списка всех улиц этого города – улицу , затем, из списка всех домов на этой улице – номер дома (трехуровневая иерархия).
В этой статье рассмотрен Многоуровневый связанный список. Двухуровневый связанный список или просто Связанный список рассмотрен в статьях Связанный список и Расширяемый Связанный список. Материал статьи один из самых сложных на сайте Excel2.ru , поэтому необходимо для начала ознакомиться с вышеуказанными статьями. Многоуровневый связанный список будем реализовывать с помощью инструмента Проверка данных ( Данные/ Работа с данными/ Проверка данных ) с условием проверки Список .Создание Многоуровневого связанного списка рассмотрим на конкретном примере.
Примечание : Рассмотренный в этой статье Многоуровневый связанный список на самом деле правильнее назвать Трехуровневым, т.к. создать четырехуровневый связанный список, используя рассмотренный здесь подход, очень проблематично. Для тех, кому требуется создать структуру с 4-мя и более уровнями, см. статью Многоуровневый связанный список типа Предок-Родитель .
Постановка задачи
Имеется перечень Регионов . Для каждого Региона имеется свой перечень Стран . Для каждой Страны имеется свой перечень Городов .
Пользователь должен иметь возможность, выбрав определенный Регион , в соседней ячейке выбрать из Выпадающего (раскрывающегося) списка нужную ему Страну из этого Региона . В другой соседней ячейке пользователь должен иметь возможность выбрать нужный ему Город из этой Страны (см. файл примера ).
В окончательном виде трехуровневый связанный список должен работать так:
Сначала выберем, например, Регион «Америка» с помощью Выпадающего списка .
Затем выберем Страну «США» из Региона «Америка».
Причем перечень стран в выпадающем списке будет содержать только страны из выбранного на предыдущем шаге Региона «Америка».
И, наконец, выберем Город «Атланта» из Страны «США».
Причем перечень городов в выпадающем списке будет содержать только города из выбранной на предыдущем шаге Страны, т.е. из «США».
Решение
Итак, приступим к созданию Трехуровневого связанного списка . Таблицу, в которую будут заноситься данные с помощью Трехуровневого связанного списка , разместим на листе Таблица .
Список Регионов и перечни Стран разместим на листе Страны .
Обратите внимание, что названия Регионов (диапазон А2:А12 на листе Страны ) в точности должны совпадать с заголовками столбцов, содержащих названия соответствующих Стран ( В1: L 1 ).
Это требование обеспечивается формулой (см. статьи о Транспонировании ). =ДВССЫЛ(АДРЕС(СТРОКА($A$1)-СТОЛБЕЦ($A$1)+СТОЛБЕЦ();1))
с помощью которой формируются заголовки столбцов. Введем ее в диапазон ячеек В1: L 1 .
Список Стран и перечни Городов разместим на листе Города .
Откуда же возьмется перечень стран на листе Города ? Очевидно, что после заполнения листа Страны названиями стран, необходимо, что они каким-то чудесным образом переместились на лист Города . Это чудесное перемещение организуем формулами. Список Стран сформируем на листе Города в столбце А с помощью решения приведенного в статье Объединение списков . Значения для этого списка будем брать из Именованного диапазона Диап_Стран (его нужно предварительно создать через Диспетчер имен ) . Именованный диапазон Диап_Стран образуем формулой:
Для формирования списка Стран нам также понадобится Именованная формула Строки_Столбцы_Стран
Окончательная формула в столбце А на листе Города выглядит так:
сформирует необходимый нам список Стран .
Теперь создадим Динамический диапазон для формирования Выпадающего списка содержащего названия Регионов . Для этого необходимо:
- нажать кнопку меню « Присвоить имя » ( Формулы/ Определенные имена/ Присвоить имя );
- в поле Имя ввести Регионы ;
- в поле Диапазон ввести формулу
Формула подсчитывает количество элементов в столбце А на листе Страны (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в столбце (функция ИНДЕКС() ), тем самым формируется диапазон, содержащий все значения Регионов . Пропуски в столбце А не допускаются.
Аналогичным образом создадим Динамический диапазон Список_Стран для формирования выпадающего списка содержащего названия стран:
Создадим Именованную формулу Позиция_региона для определения позиции, выбранного пользователем региона, в созданном выше диапазоне Регионы:
Т.к. в формуле использована относительная адресация , то важно перед созданием формулы сделать активной ячейку B5 на листе Таблица .
Аналогичным образом создадим именованную формулу для определения позиции, выбранной пользователем страны, в диапазоне Список_Стран =ПОИСКПОЗ(таблица!B5;Список_Стран;0) . Перед созданием формулы нужно сделать активной ячейку С5 на листе Таблица .
Создадим Именованные константы МаксСтран равную 20 и МаксГородов равную 30. Константы соответствует максимальному количеству стран в регионе и, соответственно, максимальному количеству городов в стране. Эти значения произвольны и их можно изменить.
Создадим именованный диапазон Выбранный_Регион для определения диапазона на листе Страны , содержащего страны выбранного региона:
Теперь, например, при выборе региона Америка функция СМЕЩ() вернет ссылку на диапазон страны!$B$2:$B$20
Создадим аналогичный диапазон Выбранная_Страна для определения диапазона на листе Города , содержащего города выбранного региона: =СМЕЩ(города!$A$2;;Позиция_страны;МаксГородов)
Создадим две последние именованные формулы Страны и Города : =СМЕЩ(страны!$A$2;;Позиция_региона;СЧЁТЗ(Выбранный_Регион)) =СМЕЩ(города!$A$2;;Позиция_страны;СЧЁТЗ(Выбранная_Страна))
Эти формулы нужны для того, чтобы в выпадающих списках не отображались пустые строки.Наконец сформируем связанный выпадающий список для ячеек из столбца Страна налисте Таблица .
- выделяем диапазон B5:B22 налисте Таблица ;
- вызываем инструмент Проверка данных ,
- устанавливаем тип данных Список ,
- в поле Источник вводим: =Страны .
Также создадим связанный выпадающий список для ячеек из столбца Город (диапазон С5:С22 , в поле Источник вводим: =Города )
На листе Таблица после выбора Региона и Страны теперь есть возможность выбора Города .
Для добавления новых Регионов и их Стран достаточно ввести новый Регион в столбец A (лист Страны ), в строке 1 автоматически отобразится соответствующий заголовок. Под появившимся заголовком в строке 1 введите страны нового Региона .Для добавления новых Городов, на листе Города в строке 1 найдите нужное название страны (оно автоматически появится там после добавления страны на листе Страны ). Под этим заголовком введите название города.
СОВЕТ: В этой статье города (и страны) размещены в нескольких столбцах. Обычно однотипные значения размещают в одном столбце (списке). В статье Многоуровневый связанный список в MS EXCEL на основе таблицы все исходные данные размещены на одном листе, а однотипные данные (названия городов) – в одном столбце. Это облегчает написание формул и позволяет создать списки с большим количеством уровней иерархии (4-6).
Как быстро создать многоуровневые (каскадные) выпадающие списки в Excel
В данной статье рассматривается методика быстрого создания многоуровневых (каскадных) выпадающих списков в Excel на основе умных таблиц для моделирования иерархических данных. В качестве примера в видеоуроке создаются 6-уровневые выпадающие списки.
Видеоурок к статье:
Первая таблица Группы_товаров связывает группы товаров и категории: в магазине 2 группы товаров – поля Продукты_питания и Одежда , каждая из которых включает по 2 категории товаров: продукты питания состоят из элементов Молочные_продукты и Мясо , одежда – Верхняя_одежда и Спортивные_товары .
Обратите внимание! Элементы таблицы будут являться одновременно названием для нижестоящих таблиц. К именам таблиц предъявляются специальные требования: имя должно начинаться с буквы, не должно содержать пробелов и специальных символов.
На следующем уровне создаются таблицы, связывающие категории и виды продукции: в приведенном примере создана таблица с именем Молочные_продукты , содержащая поля Молоко (подразделяется на козье и коровье) и Сыр (подразделяется на твердый и полутвердый), а также таблица с именем Мясо , содержащая поля Красное_мясо (подразделяется на говядина и свинина) и Птица (подразделяется на курицу и индейку).
Остальные данные организуются по такому же принципу.
Список 1 уровня
Создать список конкретных умных таблиц книги: Таблица1;Таблица2;Таблица3
Создать список полей конкретной таблицы:
Создать список элементов конкретного поля конкретной таблицы: =ДВССЫЛ(” Таблица1 “&”[ Поле1 ]”)
Список 2 уровня
Создать список полей таблицы, выбираемой в вышестоящем списке:
Создать список элементов выбираемого поля конкретной таблицы: =ДВССЫЛ(” Таблица1 “&”[“& А1 &”]”)
Список 3 уровня
Создать список элементов выбираемого поля выбираемой таблицы: =ДВССЫЛ( A1 &”[“& B1 &”]”)
После того, как данные организованы, необходимо непосредственно создать выпадающие списки, которые задаются специальными формулами, вводимыми в поле Источник в меню Проверка данных – Список:
Формулы можно задать вручную, что, однако, достаточно трудоемко. Лучше всего воспользоваться специальной группой команд Выпадающие списки в надстройке SubEx для Excel , которая моментально сформирует за вас нужные формулы!
Всего возможны три вида выпадающих списков:
1 уровня (первичный, не связанный ни с чем список)
2 уровня (имеющий одну связь на один вышестоящий выпадающий список: имя таблицы или имя поля)
3 уровня (имеющий две связи на вышестоящие списки: на имя таблицы и имя поля)
Обратите внимание! Все уровни иерархии моделируются с использованием этих трёх видов выпадающих списков, сгруппированных в различных комбинациях.
Итак, вот формулы, которые подставляются в Проверку данных в зависимости от конкретной ситуации. Обратите внимание, вместо красного текста подставляются непосредственно названия таблиц и полей, а вместо оранжевого – ставится ссылка на выпадающий список вышестоящего уровня:
Список первого уровня является первичным, т.е. задается абсолютно и не является зависимым, это может быть список таблиц книги, список полей конкретной таблицы или список элементов конкретного поля конкретной таблицы, в зависимости от ваших потребностей.
Следующие списки будут связанными, т.е. зависеть от выбранного значения вышестоящего списка. Список может быть 2 уровня, т.е. ссылаться на один вышестоящий список, или 3 уровня, т.е. ссылаться на два вышестоящих списка (например, на список с именем таблицы и список с именем поля таблицы).
Как было сказано выше полученная система таблиц и списков является полностью динамической: можно добавлять новые поля и элементы в любую таблицу и они автоматически подвяжутся в выпадающие списки.
Подробный пример создания 6-уровневых выпадающих списков на примере номенклатуры товаров магазина приведен в видеоуроке!
Как в excel сделать иерархию
Excel, таблица данных, создание иерархической структуры
Здравствуйте, дорогие друзья! По Вашим многочисленным просьбам я вновь обращаюсь к своей любимой теме – табличный процессор Excel, таблицы данных Excel. Сегодня я хотел бы поговорить с Вами о построении модели таблицы данных и придании таблице определенной структуры.
Как обычно, начнем с терминологии. Моделью называется рабочая таблица или набор рабочих таблиц и макротаблиц пакета Microsoft Excel, объединенных и предназначенных для решения конкретной вычислительной задачи. Продвинутые пользователи MS Excel часто создают модели для себя, для решения своих повседневных задач, а также для других пользователей в качестве помощи или услуги. Для создания удобной модели, особенно для сторонних пользователей, в Microsoft Excel предусмотрен сервис придания рабочей таблице данных иерархической структуры. Причем структура может быть достаточно сложной, допускающей до восьми уровней иерархии.
Нам известно, что рабочие таблицы в Excel состоят из строк и столбцов, соответственно электронную таблицу можно структурировать, как вертикально, так и горизонтально. А вообще, при рассказе о структурировании таблицы я часто прибегаю к модели «русской матрешки». То есть нижние уровни информации объединяются в более высокие, те еще в более высокие и так далее. Чтобы было понятно, обратимся к несложному примеру. Я создам обычную модель электронной таблицы данных, которая будет отслеживать сбыт автомобилей во времени, а для удобства пользования придам ей определенную иерархическую структуру.
В строках рабочей таблицы будет показан помесячный объем продаж определенных моделей автомобилей. Ячейки, выделенные зеленым цветом, содержат расчетные формулы. Помесячный объем продаж моделей – нижний уровень. Строки можно сгруппировать по маркам: LADA и УАЗ. Это более высокий уровень иерархии.
Как это сделать? Очень просто. Напомню, что на данный момент я пользуюсь MS Excel 2007, а возможность создания иерархической структуры появилась в гораздо более ранних версиях электронных таблиц семейства Excel. Поэтому такая функциональная возможность у Вас, пользователя любой версии Excel, есть, только она может быть оформлена несколько по-иному.
Переходим в строке меню на вкладку «Данные», раздел «Структура», затем выделяете мышью… Читать далее…
Вступите в группу, и вы сможете просматривать изображения в полном размере
Как сделать иерархию в excel?
Дата: 31 января 2017 Категория: Excel Поделиться, добавить в закладки или статью
Здравствуйте, друзья. Думаю, каждому, кто работал в Эксель, попадались таблицы со структурой данных. Группировка в Эксель, чаще всего, применяется, когда есть организация со структурой, и мы оцениваем некоторые показатели согласно этой структуры. Например, продажи по Компании в целом, по региональным представительствам, по менеджерам. Посмотрите на картинке, как это может быть без группировки:
На рисунке детальный пример отчета о продажах по структурным подразделениям подневно. А что если нужно предоставить руководителям отчет в разрезе только лишь департаментов и помесячно, без детализации? Чтобы было вот так:
Согласитесь, такой вид таблицы более нагляден и показателен для анализа работы Компании в целом. Но как получить такую таблицу достаточно быстро, не скрывая и не удаляя ячейки? Очень просто, задайте структуру документа.
На рисунке выше я привел уже структурированный документ, с маркерами группы. В таблице сгруппированы менеджеры по регионам и регионы в компании, а так же, дни недели в месяцы. Чтобы скрыть какую-то отдельную группу, кликните на значке «минус» в итоговой строке этой группы». Чтобы раскрыть – на значке «плюс». Чтобы отобразить какой-то определенный уровень – нажмите на его номер в верхнем левом углу листа.
Обратите внимание, данные сгруппированы в несколько уровней. Каждый последующий вложен в предыдущий. Всего Эксель позволяет создать до восьми уровней в строках и восьми – в столбцах.
Есть два способа создать структуру листа: автоматический и ручной.
Автоматическое создание структуры в Excel
Программа может попытаться создать структуру автоматически. Для этого нажмите на ленте: Структура – Группировать – Создать структуру.
Если у Вас правильно и логично записаны формулы суммирования, структура будет создана правильно. У меня эта функция часто срабатывает правильно, поэтому сначала я пробую именно автоматическое создание. Структура из примера отлично создалась автоматически.
Ручное создание структуры
Создание структуры вручную позволяет держать весь процесс под контролем и гарантированно получить правильный результат. Чтобы структурировать лист вручную, выполните такие действия:
- Оформите и наполните таблицу, создайте итоговые строки и столбцы
- Кликните на маленькой пиктограмме со стрелкой в нижнем правом углу ленточной группы Данные – Структура. В открывшемся окне настройте расположение строк и столбцов итогов
- Выделите строки или столбцы, которые нужно группировать (не выделяйте итоговые). Делайте это с каждой группой по очереди, начиная с самых «глубоких». В моем примере это фамилии менеджеров.
- Нажмите на ленте Структура – Группировать (ли нажмите Alt+Shift+стрелка вправо). Будет создана группировка
- Повторяйте п.3-4 до полного структурирования данных. Повторюсь, начинайте с подчиненных групп, потом переходите на уровень выше. Т.е. в таблице из примера мы поочередно сгруппируем менеджеров в филиалы, потом филиалы в компанию.
Когда Вы вставляете новые строки и столбцы в структурированную таблицу – будьте осторожны. Если, например, добавляется новый менеджер в группу, вставляйте строку где-то между сгруппированными строками, не первым элементом группы и не последним. В противном случае, новая строка не впишется в структуру.
При копировании диапазона ячеек, скрытые строки и столбцы не копируются. Но когда они скрыты группировкой – все равно копируются. Чтобы выделить только данные, отображенные на экране, выделите нужный диапазон и нажмите F5. В открывшемся окне выберите Выделить – Только видимые ячейки. Теперь ячейки, скрытые группировкой, не будут скопированы.
Если Вам больше не нужна структура – удалите ее. Для этого выполните на ленте Данные – Структура – Разгруппировать – Удалить структуру.
Группированные данные, структурированное рабочее пространство – не только добавляют практичности Вашим таблицам, это правило хорошего тона, когда одну и ту же таблицу можно легко посмотреть как в развернутом виде, так и в детальном.
Вот и все, тренируйтесь и применяйте на практике структурирование данных в Экселе. Приглашаю Вас комментировать изложенный материал, задавайте Ваши вопросы и делитесь опытом!
Следующая статья будет посвящена подключению внешних данных и их консолидации. Это неотъемлемая часть работы большинства пользователей Excel. Присоединяйтесь к чтению!
Поделиться, добавить в закладки или статью
Часть 1 Как автоматически создать структуру
Откройте таблицу Excel. Для этого дважды щелкните по Excel-файлу.
Щелкните по вкладке Данные. Она находится в левой части ленты инструментов, которая в свою очередь расположена в верхней части окна Excel. Откроется панель инструментов «Данные».
Щелкните по направленной вниз стрелке рядом с опцией Группировать. Вы найдете эту опцию справа на панели «Данные». Откроется выпадающее меню.
Нажмите Создание структуры.
Эта опция находится в выпадающем меню «Группировать».Файл:Group and Outline Excel Data Step 4 Version 2.jpg
- Если появилось сообщение «Невозможно создать структуру документа», формула, которой подчиняются данные, не совместима с функцией структурирования. В этом случае создайте структуру вручную (читайте следующий раздел).
Скройте данные. Нажмите кнопку «» в верхней или левой части таблицы Excel, чтобы скрыть сгруппированные данные. В большинстве случаев это действие приведет к отображению только последней строки с данными.
Удалите структуру (если нужно). Нажмите «Разгруппировать» (справа от опции «Группировать»), а затем в выпадающем меню нажмите «Удалить структуру». Так вы отобразите данные, которые были сгруппированы и скрыты.
Часть 2 Как создать структуру вручную
Выделите данные. Щелкните и удерживайте верхнюю левую ячейку нужных данных, а затем перетащите курсор в нижнюю правую ячейку нужных данных.
Щелкните по вкладке Данные. Она находится в левой части ленты инструментов, которая в свою очередь расположена в верхней части окна Excel.
Щелкните по Группировать. Вы найдете эту опцию справа на панели «Данные».
Нажмите Группировать. Эта опция находится в выпадающем меню «Группировать».
Выберите способ группирования. Нажмите «Строки», чтобы сгруппировать данные по вертикали, или нажмите« «Столбцы», чтобы сгруппировать данные по горизонтали.
Щелкните по OK. Эта кнопка находится в нижней части всплывающего окна.
Скройте данные. Нажмите кнопку «» в верхней или левой части таблицы Excel, чтобы скрыть сгруппированные данные. В большинстве случаев это действие приведет к отображению только последней строки с данными.
Удалите структуру (если нужно). Нажмите «Разгруппировать» (справа от опции «Группировать»), а затем в выпадающем меню нажмите «Удалить структуру». Так вы отобразите данные, которые были сгруппированы и скрыты.
Как сделать иерархию в Excel
Недавно мой друг интересовался кнопками «Детализация» и «Детализация» на вкладке «Инструменты сводной таблицы» на ленте. Почему они постоянно серые? Они занимают много места в ленте. Как кто-то должен их использовать?
Найдите вкладку Power Pivot слева от вкладок инструментов сводной таблицы
После некоторых исследований мы нашли способ их использовать, но для создания иерархии необходимо использовать модель данных и представление диаграммы Power Pivot. Если у вас нет вкладки Power Pivot на ленте, вам придется найти коллегу, у которого есть кнопка, чтобы создать иерархию. (Или, если вы просто хотите попробовать эту функцию, загрузите созданный мной файл Excel: Hierarchy.xlsx)
Найдите вкладку Power Pivot слева от инструментов сводной таблицы в Excel.
Первый шаг. Преобразуйте исходный набор сводных данных в таблицу, используя Главная страница – Форматировать как таблицу или Ctrl + T . Убедитесь, что выбран параметр «Моя таблица имеет заголовки».
Используйте Вставка – Сводная таблица. В диалоговом окне «Создать сводную таблицу» установите флажок «Добавить эти данные в модель данных».
Создать сводную таблицу.
Вот поля сводной таблицы перед созданием иерархии.
Поля сводной таблицы.
Нажмите значок «Управление» на вкладке Power Pivot на ленте. (Во многих экземплярах Excel 2013 и 2016 нет этой вкладки. Она не отображается на Mac.)
Кнопка «Управление» на вкладке Power Pivot на ленте.
В окне Power Pivot для Excel щелкните значок Представление диаграммы. Он находится справа от вкладки «Главная».
Кнопка просмотра схемы.
Используйте маркер изменения размера в правом нижнем углу Таблицы 1, чтобы увеличить Таблицу 1, чтобы вы могли видеть все свои поля. Нажмите на первый элемент в вашей иерархии (Континент в моем примере). Удерживая нажатой клавишу Shift, щелкните последний элемент в иерархии (город в моем примере). Вы также можете щелкнуть один элемент и щелкнуть, удерживая нажатой клавишу Ctrl, другие, если поля иерархии не являются смежными. Выбрав поля, щелкните правой кнопкой мыши любое из полей и выберите «Создать иерархию».
Иерархия1 создана и ожидает ввода нового имени. Я назову свою иерархию Географией. Если щелкнуть вне Power Pivot, Hierarchy1 больше не находится в режиме переименования. Щелкните правой кнопкой мыши Hierachy1 и выберите «Переименовать».
Закройте Power Pivot и вернитесь в Excel. В полях сводной таблицы теперь отображается иерархия географии и дополнительные поля. Поле «Продажи» скрыто в разделе «Дополнительные поля». Я немного понимаю, почему они прячут Континент, Страна, Регион, Территория, Город под Больше полей. Но я не понимаю, почему они скрывают продажи в разделе «Дополнительные поля».
Чтобы построить сводную таблицу, установите флажок для иерархии «География». Откройте Дополнительные поля, щелкнув треугольник рядом с ним. Выберите Продажи.
Создать сводную таблицу
На изображении выше есть на что обратить внимание. Когда вы изначально создаете сводную таблицу, активная ячейка находится на A3, а значок детализации неактивен. Однако, если вы переместите указатель ячейки на Северную Америку в A4, вы увидите, что Детализация включена.
Наведите указатель на ячейку на Северную Америку, нажмите «Развернуть вниз», и «Континент» заменится на «Страна».
Нажмите кнопку «Развернуть вниз».
Установив указатель на ячейку Канады, нажмите «Развернуть вниз», и вы увидите Восточную Канаду и Западную Канаду. Обратите внимание, что в этот момент обе кнопки «Развернуть» и «Развернуть вверх» включены.
Кнопки Детализация и Детализация включены.
Я нажал «Подробнее», чтобы вернуться в раздел «Страна». Выберите США. Разверните три раза, и я окажусь в городах Каролины. В этот момент кнопка Детализация неактивна.
Кнопка «Детализация» неактивна.
Обратите внимание, что на уровне континентов вы можете нажать «Развернуть поле», чтобы отобразить континенты и страны. Затем в первой стране выберите «Развернуть поле», чтобы открыть регионы. В первом регионе используйте «Развернуть поле», чтобы отобразить территории. В первой территории нажмите «Развернуть поле», чтобы открыть город.
На всех приведенных выше снимках экрана показана сводная таблица в представлении по умолчанию в режиме «Показать в табличной форме». Если ваши сводные таблицы созданы в компактной форме, вы увидите представление ниже. (Чтобы узнать, как сделать так, чтобы все ваши будущие сводные таблицы начинались в табличной форме, см. это видео).
Изменить макет отчета.
В чем преимущество Иерархии? Я попытался создать обычную сводную таблицу без иерархии. У меня все еще есть возможность разворачивать и сворачивать поля. Но если я хочу показать только регионы Канады, мне придется добавить срез или фильтр отчетов.
Посмотреть видео
Расшифровка видео
Изучайте Excel из подкаста MrExcel, выпуск 2196: Детализация и детализация сводных таблиц.
Здравствуйте, добро пожаловать на сетевую трансляцию MrExcel, я Билл Джелен. В сводных таблицах есть загадка. Если я вставлю сюда сводную таблицу, вы увидите, что у нас есть поля Drill Up и Drill Down, но они никогда не загораются. Что случилось с этим? Почему они у нас есть? Как заставить их работать? Хорошо, это отличный, отличный вопрос, и, к сожалению, я плохо себя чувствую по этому поводу. Я пытаюсь всю свою жизнь работать в Excel, никогда не используя вкладку Power Pivot. Я не хочу, чтобы вам приходилось платить дополнительные 2 доллара в месяц за версию Office 365 Pro Plus, но это один — это один — где мы должны тратить дополнительные 2 доллара в месяц или искать кого-то, у кого есть дополнительные 2 доллара США в месяц за настройку.
Я возьму этот формат данных в виде таблицы. Неважно, какой формат я выберу, формат не важен; просто достать нам столик — важная часть. Power Pivot, мы собираемся добавить эту таблицу в нашу модель данных, а затем нажать «Управление». Итак, вот наша таблица в модели данных. Нам нужно перейти в представление диаграммы, теперь мы сделаем его немного шире, чтобы мы могли видеть все поля. Я собираюсь выбрать Континент; Я собираюсь нажать Shift+клик на город. Теперь это составляет мою иерархию Drill Down, Drill Up. Затем мы щелкнем правой кнопкой мыши и скажем «Создать иерархию». И они дают нам имя. Я наберу «География» для своей Иерархии, вот так. Отлично, теперь с этим одним изменением мы вставляем сводную таблицу — и это будет сводная таблица модели данных — и вы видите, что мы можем добавить географию в качестве собственной иерархии.
Теперь, что мне не особенно нравится в этом, так это то, что все остальное перемещается в дополнительные поля. Хорошо? Итак, мы выбираем «Географию», и она летит влево. И хотя это здорово, мне также нужно выбрать Доход, и они взяли поля, которые не были частью Иерархии, и переместили их в Дополнительные поля.Так что, как я понимаю, они пытаются скрыть поля, которые я не должен выбирать, но в процессе этого они также скрыли дополнительные поля — Доход или Продажи здесь. Хорошо. Поэтому немного расстраивает то, что нам приходится обращаться к большему количеству полей, чтобы получить поля, которые не являются частью географии, но так оно и есть.
Хорошо. Итак, теперь, когда у нас есть это, давайте посмотрим, что здесь работает. Сижу на Континенте, захожу во вкладку Анализ и ничего не загорается, не помогло. Стрелять! Нет, это сработало, вам просто нужно приехать в Северную Америку, а затем я могу углубиться, и он заменит «Континент» на «Страна». А затем из Канады я могу развернуть и получить Восточную Канаду и Западную Канаду. Если углубиться в Восточную Канаду, я получу Онтарио и Квебек. Онтарио, я получаю эти города, я могу углубляться, углубляться и выбирать Соединенные Штаты; Развернуть, развернуть, развернуть. Хорошо, вот как это работает.
Попробуйте, у вас должна быть вкладка Power Pivot или найдите кого-нибудь с такой вкладкой. Если вы просто хотите попробовать, посмотрите в описании YouTube, там будет ссылка на веб-страницу, и на веб-странице есть место, где вы можете скачать этот файл, и вы сможете использовать Иерархию, даже если у вас нет вкладки Power Pivot. Если вы используете Excel 2016 или Office 365, все должно работать.
Теперь, видите ли, я думаю, что я не уверен, что являюсь поклонником того факта, что они избавляются от другой информации, а не используют значок «Развернуть», который затем расширяйтесь в следующую группу, и следующую группу, и следующую группу. У нас всегда был значок «Развернуть», но даже тогда он работает немного по-другому. Здесь, если бы я захотел, я мог бы сидеть там в Северной Америке и расширять один уровень за раз, не выбирая каждый дополнительный из модели данных. Похоже, нам нужно перемещать указатель ячейки по одному биту за раз.
Хорошо, теперь этот совет был действительно обнаружен. MVP Excel поговорили с командой Excel об этих кнопках, поэтому в этой книге они не рассматриваются. Но есть и много других замечательных советов, описанных в MrExcel LIVe, 54 величайших советах всех времен.
Подведение итогов на сегодня. Почему элементы Детализация и Детализация постоянно отображаются серым цветом? Ну, вы должны создать Иерархию. Чтобы создать иерархию, вам нужно войти в Power Pivot; в представление диаграммы; выберите поля для иерархии; а затем щелкните правой кнопкой мыши; и Создать иерархию.
Я хочу поблагодарить вас за то, что заглянули, увидимся в следующий раз на другой сетевой трансляции от MrExcel.
Иерархия в модели данных — это список вложенных столбцов в таблице данных, которые считаются единым элементом при использовании в Power PivotTable. Например, если у вас есть столбцы «Страна», «Штат», «Город» в таблице данных, можно определить иерархию для объединения трех столбцов в одно поле.
В списке полей Power PivotTable иерархия отображается как одно поле. Таким образом, вы можете добавить только одно поле в сводную таблицу вместо трех полей в иерархии. Кроме того, это позволяет вам перемещаться вверх или вниз по вложенным уровням осмысленным образом.
Рассмотрите следующую модель данных в качестве иллюстраций к этой главе.
Создание иерархии
Иерархии можно создавать в представлении схемы модели данных. Обратите внимание, что вы можете создать иерархию только на основе одной таблицы данных.
Нажмите на столбцы «Спорт», «ID дисциплины» и «Событие» в таблице данных «Медаль» в указанном порядке. Помните, что порядок важен для создания значимой иерархии.
Щелкните правой кнопкой мыши по выделенному фрагменту.
Выберите «Создать иерархию» в раскрывающемся списке.
Поле иерархии с тремя выбранными полями по мере создания дочерних уровней.
Переименование иерархии
Чтобы переименовать поле иерархии, сделайте следующее —
Щелкните правой кнопкой мыши на Hierarchy1.
Выберите «Переименовать» в раскрывающемся списке.
Создание сводной таблицы с иерархией в модели данных
Вы можете создать Power PivotTable, используя иерархию, созданную в модели данных.
Перейдите на вкладку «Сводная таблица» на ленте в окне Power Pivot.
Нажмите «Сводная таблица» на ленте.
Появится диалоговое окно «Создать сводную таблицу». Выберите «Новый рабочий лист» и нажмите «ОК».
На новом листе создается пустая сводная таблица.
В списке полей сводной таблицы EventHierarchy отображается как поле в таблице медалей. Остальные поля в таблице медалей свернуты и отображаются как дополнительные поля.
Нажмите на стрелку перед EventHierarchy.
Нажмите на стрелку перед пунктом Дополнительные поля.
Отобразятся поля в EventHierarchy. Все поля таблицы медалей будут отображаться в разделе «Дополнительные поля».
Как видите, три поля, которые вы добавили в иерархию, также отображаются в разделе «Дополнительные поля» с флажками. Если вы не хотите, чтобы они отображались в списке полей сводной таблицы в разделе «Дополнительные поля», вам необходимо скрыть столбцы в таблице данных — Медали в представлении данных в окне Power Pivot. Вы всегда можете отобразить их, когда захотите.
Добавьте поля в сводную таблицу следующим образом:
Перетащите EventHierarchy в область ROWS.
Перетащите медальон в область ∑ VALUES.
Значения поля «Спорт» отображаются в сводной таблице со знаком + перед ними. Отображается количество медалей для каждого вида спорта.
Нажмите на знак + перед водными видами спорта. Отобразятся значения поля DisciplineID в разделе «Водные виды спорта».
Нажмите на появившийся дочерний элемент D22. Будут отображены значения поля Event под D22.
Как вы можете видеть, количество медалей дается для событий, которые суммируются на родительском уровне — DisciplineID, которые далее суммируются на родительском уровне — спорт.
Создание иерархии на основе нескольких таблиц
Предположим, вы хотите отобразить дисциплины в сводной таблице, а не идентификаторы дисциплин, чтобы сделать сводку более читабельной и понятной. Для этого вам нужно иметь поле Discipline в таблице Medals, которого, как вы знаете, нет. Поле «Дисциплина» находится в таблице данных «Дисциплины», но вы не можете создать иерархию с полями из более чем одной таблицы. Но есть способ получить требуемое поле из другой таблицы.
Как вы знаете, таблицы «Медали» и «Дисциплины» связаны между собой. Вы можете добавить поле «Дисциплина» из таблицы «Дисциплины» в таблицу «Медали», создав столбец, используя связь с DAX.
Нажмите кнопку просмотра данных в окне Power Pivot.
Перейдите на вкладку «Дизайн» на ленте.
Столбец — Добавить столбец в правой части таблицы выделен.
Введите = RELATED (Disciplines [Дисциплина]) в строке формул. Создается новый столбец — CalculatedColumn1 со значениями в качестве значений поля «Дисциплина» в таблице «Дисциплины».
Переименуйте полученный таким образом новый столбец в таблице медалей как «Дисциплина». Затем вам нужно удалить DisciplineID из иерархии и добавить Discipline, о чем вы узнаете в следующих разделах.
Удаление дочернего уровня из иерархии
Как видите, иерархия видна только в представлении схемы, а не в представлении данных. Следовательно, вы можете редактировать иерархию только в представлении диаграммы.
Нажмите на схему в окне Power Pivot.
Щелкните правой кнопкой мыши DisciplineID в EventHierarchy.
Выберите Удалить из иерархии в раскрывающемся списке.
Появится диалоговое окно подтверждения. Нажмите Удалить из иерархии.
Поле DisciplineID удаляется из иерархии. Помните, что вы удалили поле из иерархии, но исходное поле все еще существует в таблице данных.
Далее вам нужно добавить поле Discipline в EventHierarchy.
Добавление дочернего уровня в иерархию
Вы можете добавить поле «Дисциплина» в существующую иерархию — EventHierarchy следующим образом —
Нажмите на поле в таблице медалей.
Перетащите его в поле Events ниже в EventHierarchy.
Поле Discipline добавляется в EventHierarchy.
Как видите, порядок полей в EventHierarchy следующий: Спорт–Событие–Дисциплина. Но, как вы понимаете, это должно быть Спорт-Дисциплина-Событие. Следовательно, вам нужно изменить порядок полей.
Изменение порядка дочерних уровней в иерархии
Чтобы переместить поле «Дисциплина» на позицию после поля «Спорт», сделайте следующее: —
Щелкните правой кнопкой мыши поле «Дисциплина» в EventHierarchy.
Выберите Вверх из раскрывающегося списка.
Порядок полей изменится на Спорт-Дисциплина-Событие.
Сводная таблица с изменениями в иерархии
Чтобы просмотреть изменения, внесенные в EventHierarchy, в сводную таблицу, создавать новую сводную таблицу не нужно. Вы можете просмотреть их в самой существующей сводной таблице.
Нажмите на рабочий лист со сводной таблицей в окне Excel.
Как видите, в списке полей сводной таблицы дочерние уровни в EventHierarchy отражают изменения, внесенные вами в иерархию в модели данных. Те же изменения также соответствующим образом отражаются в сводной таблице.
Нажмите знак + перед пунктом Водные виды спорта в сводной таблице. Дочерние уровни отображаются как значения поля «Дисциплина».
Скрытие и отображение иерархий
Вы можете скрыть иерархии и показать их, когда захотите.
Снимите флажок «Иерархии» в верхнем меню представления схемы, чтобы скрыть иерархии.
Установите флажок Иерархии, чтобы отобразить иерархии.
Создание иерархии другими способами
В дополнение к тому, как вы создали иерархию в предыдущих разделах, вы можете создать иерархию еще двумя способами.
1. Нажмите кнопку «Создать иерархию» в правом верхнем углу таблицы данных «Медали» в представлении схемы.
В таблице создается новая иерархия без каких-либо полей.
Перетащите поля Год и Сезон в указанном порядке в новую иерархию. Иерархия показывает дочерние уровни.
2. Другой способ создания той же иерархии заключается в следующем:
Щелкните правой кнопкой мыши поле «Год» в таблице данных медалей в виде диаграммы.
Выберите «Создать иерархию» в раскрывающемся списке.
В таблице создается новая иерархия с дочерним полем «Год».
Перетащите полевой сезон в иерархию. Иерархия показывает дочерние уровни.
Удаление иерархии
Вы можете удалить иерархию из модели данных следующим образом:
Щелкните правой кнопкой мыши по иерархии.
Выберите Удалить из раскрывающегося списка.
Появится диалоговое окно подтверждения. Нажмите «Удалить из модели».
Вычисления с использованием иерархии
Вы можете создавать вычисления, используя иерархию. В EventsHierarchy вы можете отобразить количество медалей на дочернем уровне в процентах от количества медалей на его родительском уровне следующим образом:
Щелкните правой кнопкой мыши значение счетчика медалей события.
Выберите Настройки поля значения из раскрывающегося списка.
Появится диалоговое окно «Параметры поля значения».
Перейдите на вкладку «Показать значения как».
Выберите % от общей суммы родительской строки из списка и нажмите OK.
Дочерние уровни отображаются в процентах от родительских итогов. Вы можете убедиться в этом, просуммировав процентные значения дочернего уровня родителя. Сумма будет равна 100%.
Развертывание и развертка иерархии
Вы можете быстро переходить по уровням иерархии и детализировать их с помощью инструмента Quick Explore.
Нажмите значение поля «Событие» в сводной таблице.
Нажмите инструмент «Быстрый обзор», который появится в правом нижнем углу ячейки, содержащей выбранное значение.
Появится окно «Исследовать» с параметром «Свернуть». Это связано с тем, что из события вы можете только детализировать, так как под ним нет дочерних уровней.
Данные сводной таблицы детализированы до дисциплины.
Нажмите на инструмент быстрого просмотра, который появится в правом нижнем углу ячейки, содержащей значение.
Отображается окно «Исследовать» с параметрами «Развернуть» и «Развернуть». Это связано с тем, что из раздела «Дисциплина» можно перейти к разделу «Спорт» или перейти к разделу «Событие».
Создание организационных диаграмм в Excel идеально подходит для быстрых разовых задач. В этом руководстве читатели узнают, как создавать иерархические организационные диаграммы вручную или путем импорта данных о сотрудниках из шаблона организационной диаграммы Excel.
Загрузить шаблоны Excel
Как построить организационную диаграмму в Excel
Выберите параметр «Вставить» на верхней ленте, затем нажмите кнопку SmartArt.
Нажмите Организационную диаграмму во всплывающем меню иерархии
В раскрывающемся меню SmartArt наведите указатель мыши на параметр «Иерархия», затем выберите макет организационной диаграммы, который вы предпочитаете для создания простой организационной диаграммы.
Добавить текст в фигуры организационной диаграммы
После создания организационной диаграммы отредактируйте текст непосредственно в каждом из полей или в текстовом поле SmartArt. Если у вас есть несколько точек данных для сотрудников, нажмите Enter, чтобы пропустить строку в фигурах SmartArt.
Добавить или удалить фигуры в организационной структуре
Добавьте фигуры на организационную диаграмму, щелкнув правой кнопкой мыши (клавиша Control + щелчок) и выбрав место для добавления фигуры, или нажав символ «+» в текстовом поле SmartArt.
Обновить формат организационной диаграммы
Вы можете выбирать из множества цветов, форм и шрифтов для создания уникальных организационных диаграмм в Excel. Чтобы изменить цвет фигур организационной диаграммы, щелкните вкладку «Дизайн SmartArt» на верхней ленте во время работы с текстовым полем SmartArt. Если вы хотите обновить формы или стиль шрифта организационной диаграммы, нажмите вкладку «Формат» на верхней ленте, работая в текстовом поле SmartArt.
Создать организационную диаграмму, которая обновляется автоматически
Создать организационную диаграмму в Excel из списка сотрудников
Скачать шаблон организационной диаграммы для Excel
Этот файл будет содержать все общие точки данных, найденные в организационных диаграммах. Вы сможете быстро добавить информацию о своих сотрудниках в этот шаблон и отредактировать информацию, отображаемую на организационной диаграмме.
Добавить сотрудников в шаблон организационной диаграммы
Добавьте точки данных для сотрудников, например имя, фамилию и должность. Этот шаблон организационной диаграммы может служить основным документом для вашей базы данных сотрудников.
Скопируйте/вставьте данные о сотрудниках в организационную схему
Скопируйте все данные, которые вы хотите отобразить на организационной диаграмме, затем очистите информацию в текстовом поле SmartArt и вставьте данные о сотрудниках.
Если в базе данных сотрудников есть данные, которые вам не нужны, вы можете скрыть столбцы перед копированием данных; это исключит любые нежелательные точки данных о сотрудниках.
Оформление данных о сотрудниках на организационной диаграмме
Информация, скопированная из шаблона организационной диаграммы, будет представлена одной длинной строкой. Нажмите на каждую фигуру, куда были вставлены данные о сотруднике, и организуйте информацию в отдельные строки.
Чтобы ваша организационная структура соответствовала приведенному ниже примеру, начните с сотрудника справа или самого младшего сотрудника и понижайте его в должности до тех пор, пока он не станет подчиненным своему руководителю.
Бесплатные шаблоны организационных диаграмм для Excel
Ищете другие способы структурирования бизнес-структуры или организационной структуры? Загрузите шаблоны для ресторанных и производственных организационных диаграмм.
Получить бесплатные шаблоны
Используете другой инструмент для построения организационной диаграммы?
У нас есть шаблоны для приложений, которые вы уже используете.
Ищете более простой способ построения организационной диаграммы?
Организационные диаграммы удобны для визуальной организации всего, от крупной организационной структуры до генеалогического древа. Выполните шаги, описанные ниже, чтобы создать собственную организационную диаграмму в Excel, а затем узнайте, как упростить процесс с помощью Lucidchart — визуального рабочего пространства, которое сделает создание организационной диаграммы невероятно простым.
Хотите создать собственную организационную диаграмму? Попробуйте Люсидчарт. Это быстро, просто и совершенно бесплатно.
Как сделать организационную диаграмму в Excel
Excel — это великолепный инструмент для организации и синтеза данных, в котором реализованы некоторые элементарные возможности построения диаграмм. Включение организационных диаграмм в электронную таблицу Excel может улучшить как визуальную привлекательность, так и полезность электронной таблицы.
1. Вставить смарт-арт
Сначала перейдите на вкладку «Вставка» > SmartArt в электронной таблице Excel. Чтобы найти шаблон организационной диаграммы, нажмите на группу «Иерархия» слева, затем выберите шаблон, который хотите использовать.
2. Введите текст
Выбрав шаблон организационной диаграммы, вы сможете щелкнуть любую фигуру SmartArt и ввести текст. Поскольку каждая фигура будет представлять человека в организации, мы рекомендуем сначала организовать ваши данные, чтобы убедиться, что все учтены. К сожалению, Excel не может легко хранить больше информации, чем имя человека. Чтобы получить более полный способ построения организационной диаграммы, перейдите к разделу Lucidchart.
3. Настроить иерархию
По мере изменения организационной структуры используйте кнопки «Повысить» и «Понизить», которые находятся на вкладке «Дизайн» инструментов SmartArt, чтобы перемещать людей по горизонтали. Точно так же кнопки «Вверх» и «Вниз» перемещают людей по организации вертикально.
В нашем примере вице-президент по маркетингу теперь будет подчиняться вице-президенту по продажам. Чтобы отразить это изменение, переместите вице-президента по маркетингу, пока он не окажется рядом с вице-президентом по продажам, а затем нажмите кнопку понижения уровня, щелкнув фигуру вице-президента по маркетингу.
4. Добавляйте и удаляйте фигуры
Добавьте фигуры на организационную диаграмму в Excel, нажав «Добавить фигуру» на вкладке «Дизайн инструментов SmartArt». Выполните предыдущие шаги, чтобы ввести текст в новую фигуру и управлять ее положением на организационной диаграмме.
Допустим, ваша компания растет, и в нее наняли двух новых сотрудников. Первый будет подчиняться непосредственно вице-президенту по маркетингу, а второй — вице-президенту по производству. Чтобы добавить новый отчет в вице-президента по маркетингу, щелкните существующую фигуру вице-президента по маркетингу и найдите кнопку «Добавить фигуру». Сделайте то же самое для нового сотрудника, который будет подчиняться вице-президенту по производству.
5. Отформатируйте организационную диаграмму
Настройте свою организационную диаграмму в Excel, изменив цвета, шрифты и размеры фигур на вкладках «Дизайн» и «Формат» инструментов SmartArt.
С Lucidchart можно быстро и легко строить диаграммы. Начните бесплатную пробную версию сегодня, чтобы начать творить и сотрудничать.
Как создать организационную диаграмму в Lucidchart
Lucidchart, визуальное рабочее пространство для совместной работы с организационным конструктором диаграмм, поможет вам создавать потрясающие диаграммы без всех ограничений и неудобств, присущих Excel. С Lucidchart вы легко создадите свою организационную диаграмму, а затем легко добавите ее обратно в электронную таблицу Excel с помощью нашей надстройки MS Excel.
Начало работы с Lucidchart
Начните с регистрации учетной записи Lucidchart. После регистрации откройте новый документ Lucidchart и включите библиотеку форм организационной диаграммы, нажав кнопку «+ Фигуры» в левой части экрана и убедившись, что библиотека форм «Организационная диаграмма» отмечена флажком.
Добавить фигуры организационной диаграммы
Перетащите фигуру организационной диаграммы, чтобы начать ее построение. Дважды щелкните фигуру, чтобы открыть контекстную панель и найти варианты оформления организационной диаграммы. Чтобы добавить дополнительные фигуры, просто нажмите красные кнопки «+» вокруг существующих фигур. Перетащите фигуры, чтобы изменить структуру отчетов или внести другие изменения в свою организацию.
Импорт данных организационной диаграммы
Чтобы ускорить процесс создания диаграммы, Lucidchart позволяет всем платным пользователям импортировать свои организационные данные и автоматически создавать организационную диаграмму. Просто предоставьте Lucidchart правильные данные, и пусть Lucidchart сделает все остальное!