Имеется несколько способов создания выпадающего списка. Выбор одного из них зависит от структуры имеющихся у вас данных.
Первый способ создания двухуровнего списка
Первый способ основывается на создание «умной» таблицы, заголовок которой содержит значения первого выпадающего списка (группы), а строки таблицы соответствуют значениям второго выпадающего списка (подгруппы). Значения элементов подгруппы должны располагаться в соответствующем столбце группы, как на рисунке ниже.
Теперь приступим к созданию первого выпадающего списка группы (в моем случае - список стран):
- Выберите ячейку, в которую будете вставлять выпадающий список;
- Переходим на вкладку ленты Данные ;
- Выбираем команду Проверка данных ;
- В выпадающем списке выбираем значение Список ;
- В поле Источник указываем следующую формулу =ДВССЫЛ("Таблица1[#Заголовки]") .
Формула ДВССЫЛ возвращает ссылку на диапазон заголовков «умной» таблицы. Преимущество использования такой таблицы в том, что при добавлении столбцов, выпадающий список будет автоматически расширен.
Осталось создать второй зависимый выпадающий список - список подгрупп.
Смело повторяем 4 первых пункта описанных выше. Источником в окне Проверка данных для второго выпадающего списка будет служить формула =ДВССЫЛ("Таблица1["&F2&"]") . Ячейка F2 в данном случае - значение первого выпадающего списка.
Можно использовать и обычную «неумную» таблицу, однако в этом случае вам придется вручную изменять диапазоны заголовков и строк. В рассмотренном примере это происходит автоматически.
Второй способ создания двухуровнего списка
Второй способ удобно применять, когда данные выпадающего списка записаны в два столбца. В первом идет наименование группы, а во втором - подгруппы.
ВАЖНО! Перед созданием зависимого списка по подгруппам необходимо отсортировать исходную таблицу по первому столбцу (столбец с группой) далее будет понятно зачем это делается.
Для создания выпадающего групп нам понадобится дополнительный столбец, содержащий уникальные значения групп из исходной таблицы. Для создания этого списка используйте функцию удаления дубликатов или воспользуйтесь командой Уникальные из надстройки VBA-Excel.
Теперь создадим выпадающий список групп. Для этого выполните 4 первых пункта из первого способа создания двухуровнего списка. В качестве Источника укажите диапазон уникальных значений групп. Тут все стандартно.
Рекомендация: удобно в качестве источника указывать именованный диапазон. Для его создания откройте Диспетчер имен со вкладки Формулы и присвойте имя диапазону с уникальными значениями.
Теперь самая сложная часть - указать в Источнике динамическую ссылку на диапазон со значениями второго выпадающего списка (списка подгрупп). Решать ее будем с помощью функции СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, [высота], [ширина]) , которая возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.
- Ссылка в нашем случае - $A$1 - верхний левый угол исходной таблицы;
- Смещ_по_строкам - ПОИСКПОЗ(F3;$A$1:$A$67;0)-1 - номер строки со значением искомой группы (в моем случае страны ячейка F3 ) минус единица;
- Cмещ_по_столбцам - 1 - так как нам необходим столбец с подгруппами (городами);
- [Высота] - СЧЁТЕСЛИ($A$1:$A$67;F3) - количество подгрупп в искомой группе (количество городов в стране F3 );
- [Ширина] - 1 - так как это ширина нашего столбца с подгруппами.
Раздел 3. Работа с группами таблиц
Понятие списка в Excel
Списком называется таблица Excel , которая состоит из одного и более столбцов. Столбцам списка присваиваются уникальные имена полей, которые заносятся в первую строку списка. Все ячейки в столбце имеют один и тот же формат данных, поэтому все строки или, как их еще называют записи, однотипны.
| Фамилия | Возраст | Пол |
| Петухова | ||
| Петров | ||
| Зайцева | ||
| Морев | ||
| Иванов | ||
| Петрова |
Рис. 1. Пример списка Excel
В приведенной таблице Excel данные в первом и в третьем столбце имеют текстовый формат, а данные во втором столбце – числовой. Названия полей списка должны быть помещены в одну ячейку. Данные списка и другие данные на этом же листе должны быть отделены, по крайней мере, одной пустой ячейкой, то есть они не должны соприкасаться. Список Excel является типичной базой данных и к нему применимо большое число специфичных операций. Большинство таблиц, с которыми работают пользователи Excel , являются списками или могут быть приведены к виду списка. Так, если таблица, показанная на рис.2 создавалась для каждой группы студентов, то их можно было бы объединить в одну таблицу, которая также была бы списком, добавив еще одно поле «Группа».
| Группа | Фамилия | Возраст | Пол | Рост | Вес |
| 99-л-3 | Петухова | ||||
| 99-л-3 | Петров | ||||
| 99-л-3 | Зайцева | ||||
| 97-л-1 | Попов | ||||
| 97-л-1 | Козлов |
Рис.2.Объединение таблиц в список
Работа со списками в Excel
Большинство операций, предназначенных для работы со списками, сосредоточены в меню «Данные». Если список создан правильно, то достаточно выделить одну из ячеек внутри списка и нажать нужную команду в меню «Данные». Excel автоматически определит границы вашего списка.
Рис. 2. Раскрытое меню «Данные»
Пункт «Сортировка» позволяет осуществить сортировку по выбранному критерию по одному или, в порядке приоритета, по двум или даже трем полям списка.
Рис.3.Двухступенчатая сортировка списка
«Фильтр» дает возможность показывать только те записи в списке, которые удовлетворяют некоторому критерию. Так установка пользовательского автофильтра, показанная на рис.3, отобразит в списке только фамилии оканчивающиеся на буку «в».
Рис3. Использование пользовательского автофильтра
Пункт «Форма» дает возможность быстрого ввода данных в список. Пункт «Итоги» позволяет подвести суммирующие итоги под данными каждой группы в списке. На рис.4 показано диалоговое окно подведения итогов, а на рис.5 результат выполнения этой операции.
Рис.4. Подведение итогов
| Группа | Фамилия | Возраст | Пол | Рост | Вес |
| 99-л-3 | Петухова | ||||
| 99-л-3 | Петров | ||||
| 99-л-3 | Зайцева | 19 | |||
| 99-л-3 Всего | |||||
| 97-л-1 | Попов | ||||
| 97-л-1 | Козлов | 19 | |||
| 97-л-1 Всего | |||||
| Общий итог | |||||
Рис.5.Результат от подведения итогов
«Консолидация» позволяет подвести итоги по нескольким однотипным таблицам. Возможности пунктов «Консолидация» и «Итоги» полностью перекрываются мощным механизмом построения сводных таблиц Excel .
Пункт «Группа и структура» применим не только к спискам Excel и позволяет изменять уровни детализации представления информации на листе Excel . Чтобы создать простейшую структуру на листе можно выделить несколько столбцов или строк и нажать пункт «Группировать».
Рис.6.Создание структуры
Над листом появится дополнительная полоска со значком квадрата . Нажатие на этот знак скроет столбцы, над которыми проведена черная линия. Если выделить столбцы под черной линией и вновь выбрать пункт «Группировать», то будет сделана двухуровневая структура. Аналогичным образом можно группировать и строки.
Рис 7. Результат проведения операции группирования
Создание структуры дает возможность просматривать на одном экране большие таблицы Excel , скрывая ненужные в данный момент данные.
Сводные таблицы Excel
Сводные таблицы позволяют осуществлять групповые операции над данными, находящимися либо в списках, либо в нескольких диапазонах консолидации, либо во внешних базах данных. При нажатии на пункт «Сводная таблица» в меню «Данные» возникает первое диалоговое окно мастера построения сводных таблиц (см. рис.8).
Рис.8. Мастер сводных таблиц –шаг1.
Для обработки списка нужно выбрать первую из предложенных опций и перейти на второй шаг мастера. На втором шаге необходимо выбрать обрабатываемый список и перейти к третьему шагу. На третьем шаге мастера (см. рис.9.) появится конструктор обработки данных списка.
Рис.10.Мастер сводных таблиц – шаг 3.
Для конструирования групповой операции над одним из полей списка, нужно перетащить мышкой название соответствующего поля в область «Данные». Так, чтобы получить суммарный вес, нужно перетащить в область «Данные». Области «Строка», «Столбец» и «Страница» позволяют получить кроме суммарного применения групповой операции по всем записям списка еще и частичные групповые операции над записями с одинаковыми значениями полей, выбранных в области. Так, чтобы получить не только суммарный вес всех людей, но и вес людей в каждой группе, нужно перетащить в область «Строка». На рис. 11 показан результат описанного перетаскивания.
Рис.11. Конструирование групповой операции по полю «Вес»
Для удаления любого поля из области конструктора нужно просто потянуть за него мышкой и вытащить за пределы данной области.
Нажав на кнопку «Далее» можно перейти на четвертый шаг мастера. На четвертом шаге нужно выбрать лист, в который будет помещена сводная таблица. Здесь можно выбрать создание сводной таблице на новом листе и завершить работу мастера. Полученная сводная таблица отображена на рис.12.
Рис.12.Сводная таблица, полученная в результате работы мастера
Для возврата в мастер сводных таблиц для изменения запроса на групповую операцию служит кнопка на панели «Сводные таблицы». Изменим запрос, добавив поле «Фамилия» в область данных. Сводная таблица примет вид, показанный на рис.13.
Рис.13. Сводная таблица с групповой операцией по полю «Фамилия»
Вновь вернемся в мастер сводных таблиц и добавим поле «Пол» в область «Столбец», а поле «Возраст» в область «Страница». Полученная сводная таблица показана на рис 14.
Рис.14.Результирующая сводная таблица
Область «Страница» отличается от областей «Строка» и «Столбец» тем, что дает возможность либо осуществить групповую операцию сразу по всем значениям поля, либо по одному выбранному значению. В приведенном примере было выбрано значение «19».
Задание
Преобразовать таблицу, показанную ниже, в список или списки Excel . На основании сводных таблиц определить:
1.Площадь предприятий, подлежащих реконструкции по районам;
2.Число предриятий, подлежащих реконструкции по видам деятельности;
3.Суммарную площадь и число предприятий по направлениям;
4.Все вышеперечисленное в одной сводной таблице.
Делая очередную запись в ячейку А9, при наборе первой буквы наименования профиля, например «Ш», Excel предлагает заполнить ячейку словом «Швеллер». После набора «Ш» достаточно нажать кнопку «Ввод» на клавиатуре – и слово будет введено в ячейку.
Если активировать мышью ячейку А9, нажать сочетание клавиш «Alt» «↓», то появится раскрывающийся список, содержащий все ранее введенные в этом столбце значения. Остается лишь выбрать мышью нужную запись. Вместо набора вышеуказанного сочетания клавиш можно щелчком правой кнопки мыши вызвать контекстное меню и выбрать в нем пункт «Выбрать из раскрывающегося списка…». В результате увидим тот же выпадающий список.
Для создания выпадающего списка в этом случае используется также элемент «Поле со списком», но панели инструментов «Элементы управления» (в MS Excel 2003). Это так называемые элементы ActiveX. Здесь все очень похоже внешне на вариант №3, но значительно шире возможности настройки и форматирования элемента.
3.
Нажимаем кнопку «Свойства» на панели «Элементы управления» и в выпавшем окне «Properties» («Свойства») вручную вписываем диапазон исходных данных, адрес связанной ячейки (ячейки, куда будет введено выбранное значение) и количество отображаемых строк.
