Green-sell.info

Новые технологии
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Подпункты в excel

Группировка данных в Microsoft Excel

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

Настройка группировки

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

    Переходим во вкладку «Данные».

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

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

    Выполним группировку данных по строкам.

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

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

    На ленте в блоке инструментов «Структура» кликаем по кнопке «Группировать».

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

    Чтобы заново развернуть группу, нужно нажать на знак «плюс».

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

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

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

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

  • В открывшемся окошке на этот раз ставим переключатель в позицию «Столбцы». Жмем на кнопку «OK».
  • Группа готова. Аналогично, как и при группировании столбцов, её можно сворачивать и разворачивать, нажимая на знаки «минус» и «плюс» соответственно.

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

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

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

    Разгруппирование

    Если вы хотите переформатировать или просто удалить группу, то её нужно будет разгруппировать.

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

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

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

    Отблагодарите автора, поделитесь статьей в социальных сетях.

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

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

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

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

    Теперь приступим к созданию первого выпадающего списка группы (в моем случае — список стран):

    1. Выберите ячейку, в которую будете вставлять выпадающий список;
    2. Переходим на вкладку ленты Данные;
    3. Выбираем команду Проверка данных;
    4. В выпадающем списке выбираем значение Список;
    5. В поле Источник указываем следующую формулу =ДВССЫЛ(«Таблица1[#Заголовки]»).

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

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

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

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

    Читать еще:  Как сделать округление чисел в excel

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

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

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

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

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

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

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

    Создание дополнительного списка

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

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

    Далее создаем дополнительный список с повторяющимися данными (для примера возьмём поле «Название товара»). Вносим в дополнительный список все значения.

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

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

    В нашем случае все устройства можно отнести к категории Электроника, поэтому заполняем поле «Имя» и нажимаем кнопку «ОК».

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

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

    Проверяем работу

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

    Связанные списки

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

    Для примера мы попробуем к нашему списку добавить связанный список, а именно возьмем поле «Количество», присвоим значения от 1 до 6.

    Далее в основной таблице нажимаем на пустую ячейку в поле «Количество», переходим снова в «Проверку данных», выбираем список, в поле «Источник» вписываем следующее значение: =ДВССЫЛ($B3).

    Теперь можно сразу занести в таблицу количество каждого товара:

    Вывод

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

    Многоуровневый связанный список в EXCEL

    Для моделирования сложных иерархических данных создадим Многоуровневый связанный список.

    Потребность в создании иерархических данных появляется при решении следующих задач:

    • ОтделСотрудники отдела . При выборе отдела из списка всех отделов компании, динамически должен формироваться список, содержащий всех сотрудников этого отдела (двухуровневая иерархия);
    • Город – Улица – Номер дома . При заполнении адреса проживания из списка городов нужно выбирать город , затем из списка всех улиц этого города – улицу , затем, из списка всех домов на этой улице – номер дома (трехуровневая иерархия).

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

    Читать еще:  Excel для чайников читать онлайн

    Примечание : Рассмотренный в этой статье Многоуровневый связанный список на самом деле правильнее назвать Трехуровневым, т.к. создать четырехуровневый связанный список, используя рассмотренный здесь подход, очень проблематично. Для тех, кому требуется создать структуру с 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 найдите нужное название страны (оно автоматически появится там после добавления страны на листе Страны ). Под этим заголовком введите название города.

    Читать еще:  Поиск по диапазону ячеек в excel

    СОВЕТ: В этой статье города (и страны) размещены в нескольких столбцах. Обычно однотипные значения размещают в одном столбце (списке). В статье Многоуровневый связанный список в MS EXCEL на основе таблицы все исходные данные размещены на одном листе, а однотипные данные (названия городов) — в одном столбце. Это облегчает написание формул и позволяет создать списки с большим количеством уровней иерархии (4-6).

    Группы и промежуточные итоги в Excel

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

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

    1. Выделите строки или столбцы, которые необходимо сгруппировать. В следующем примере мы выделим столбцы A, B и C.
    2. Откройте вкладку Данные на Ленте, затем нажмите команду Группировать.
    3. Выделенные строки или столбцы будут сгруппированы. В нашем примере это столбцы A, B и C.

    Чтобы разгруппировать данные в Excel, выделите сгруппированные строки или столбцы, а затем щелкните команду Разгруппировать.

    Как скрыть и показать группы

    1. Чтобы скрыть группу в Excel, нажмите иконку Скрыть детали (минус).
    2. Группа будет скрыта. Чтобы показать скрытую группу, нажмите иконку Показать детали (плюс).

    Подведение итогов в Excel

    Команда Промежуточный итог позволяет автоматически создавать группы и использовать базовые функции, такие как СУММ, СЧЁТ и СРЗНАЧ, чтобы упростить подведение итогов. Например, команда Промежуточный итог способна вычислить стоимость канцтоваров по группам в большом заказе. Команда создаст иерархию групп, также называемую структурой, чтобы упорядочить информацию на листе.

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

    Создание промежуточного итога

    В следующем примере мы воспользуемся командой Промежуточный итог, чтобы определить сколько заказано футболок каждого размера (S, M, L и XL). В результате рабочий лист Excel обретет структуру в виде групп по каждому размеру футболок, а затем будет подсчитано общее количество футболок в каждой группе.

    1. Прежде всего отсортируйте данные, для которых требуется подвести итог. В этом примере мы подводим промежуточный итог для каждого размера футболок, поэтому информация на листе Excel должна быть отсортирована по столбцу Размер от меньшего к большему.
    2. Откройте вкладку Данные, затем нажмите команду Промежуточный итог.
    3. Откроется диалоговое окно Промежуточные итоги. Из раскрывающегося списка в поле При каждом изменении в, выберите столбец, который необходимо подытожить. В нашем случае это столбец Размер.
    4. Нажмите на кнопку со стрелкой в поле Операция, чтобы выбрать тип используемой функции. Мы выберем Количество, чтобы подсчитать количество футболок, заказанных для каждого размера.
    5. В поле Добавить итоги по выберите столбец, в который необходимо вывести итог. В нашем примере это столбец Размер.
    6. Если все параметры заданы правильно, нажмите ОК.
    7. Информация на листе будет сгруппирована, а под каждой группой появятся промежуточные итоги. В нашем случае данные сгруппированы по размеру футболок, а количество заказанных футболок для каждого размера указано под соответствующей группой.

    Просмотр групп по уровням

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

    Хоть в этом примере представлено всего три уровня, Excel позволяет создавать до 8 уровней вложенности.

    1. Щелкните нижний уровень, чтобы отобразить минимальное количество информации. Мы выберем уровень 1, который содержит только общее количество заказанных футболок.
    2. Щелкните следующий уровень, чтобы отобразить более подробную информацию. В нашем примере мы выберем уровень 2, который содержит все строки с итогами, но скрывает остальные данные на листе.
    3. Щелкните наивысший уровень, чтобы развернуть все данные на листе. В нашем случае это уровень 3.

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

    Удаление промежуточных итогов в Excel

    Со временем необходимость в промежуточных итогах пропадает, особенно, когда требуется иначе перегруппировать данные на листе Excel. Если Вы более не хотите видеть промежуточные итоги, их можно удалить.

    1. Откройте вкладку Данные, затем нажмите команду Промежуточный итог.
    2. Откроется диалоговое окно Промежуточные итоги. Нажмите Убрать все.
    3. Все данные будут разгруппированы, а итоги удалены.

    Чтобы удалить только группы, оставив промежуточные итоги, воспользуйтесь пунктом Удалить структуру из выпадающего меню команды Разгруппировать.

    Ссылка на основную публикацию
    Adblock
    detector