Выбор значения из выпадающего списка в excel. Связанные выпадающие списки
Раздел 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.Все вышеперечисленное в одной сводной таблице.
| Список предприятий, подлежащих реконструкции | |||
| Аэропорт | |||
| Бытовое обслуживание | |||
| Организация | Вид деятельности | Площадь |
|
| ООО Успех | Химчистка | ||
| ЗАО Удача | Парикмахерская | ||
| ООО Игрок | Химчистка | ||
| Торговля | |||
| ООО Фиалка | Цветы | ||
| ЗАО Бублик | Хлеб | ||
| ООО Ирис | Цветы | ||
| Сокол | |||
| Бытовое обслуживание | |||
| ООО Ботинок | Ремонт обуви | ||
| ООО Волос | Парикмахерская | ||
| Торговля | |||
| ООО Мороз | Бытовая техника | ||
| ЗАО Калач | Хлеб | ||
| ООО Роман | Книги | ||
| ЗАО Селедка | Рыба |
Выпадающие списки в Microsoft Excel незаменимы при создании объемных таблиц, работе с базами данных. В чем же конкретно заключается удобство этого инструмента? Зачем нужны такие спискиЕсли при заполнении таблицы некоторые данные периодически повторяются, нет необходимости каждый раз вбивать вручную постоянное значение — например, наименование товара, месяц, ФИО сотрудника. Достаточно один раз закрепить повторяющийся параметр в списке. Зачастую, некоторые ячейки списка защищены от введения посторонних значений, что снижает вероятность допустить ошибку в работе. Таблица, оформленная таким образом, выглядит аккуратно. В статье я расскажу, как в Экселе сделать выпадающий список в ячейк е и как с ним работать. Формирование выпадающего спискаОдин из распространенных примеров использования выпадающих списков - интернет-магазины, в которых вся продукция распределена по категориям. Такая структура облегчает пользователям поиск по сайту. Рассмотрим наглядно: Все товары, перечисленные в таблице, нужно отнести к категории «Одежда». Чтобы создать выпадающий список для этого перечня, потребуется выполнить следующие действия:
Как добавлять значения в списокИногда возникает необходимость дополнить уже имеющийся перечень. В раскрывающемся списке все новые пункты отображаются автоматически при добавлении. Однако чтобы связать диапазон ячеек с добавленным вновь элементом, список требуется оформить в виде таблицы. Для этого нужно выделить диапазон значений, найти на вкладке «Главная» пункт «Форматировать как таблицу» и выбрать любой понравившийся стиль, например:
Имеется несколько способов создания выпадающего списка. Выбор одного из них зависит от структуры имеющихся у вас данных. Первый способ создания двухуровнего спискаПервый способ основывается на создание «умной» таблицы, заголовок которой содержит значения первого выпадающего списка (группы), а строки таблицы соответствуют значениям второго выпадающего списка (подгруппы). Значения элементов подгруппы должны располагаться в соответствующем столбце группы, как на рисунке ниже.
Теперь приступим к созданию первого выпадающего списка группы (в моем случае - список стран):
Формула ДВССЫЛ возвращает ссылку на диапазон заголовков «умной» таблицы. Преимущество использования такой таблицы в том, что при добавлении столбцов, выпадающий список будет автоматически расширен. Осталось создать второй зависимый выпадающий список - список подгрупп. Смело повторяем 4 первых пункта описанных выше. Источником в окне Проверка данных для второго выпадающего списка будет служить формула =ДВССЫЛ("Таблица1["&F2&"]") . Ячейка F2 в данном случае - значение первого выпадающего списка. Можно использовать и обычную «неумную» таблицу, однако в этом случае вам придется вручную изменять диапазоны заголовков и строк. В рассмотренном примере это происходит автоматически. Второй способ создания двухуровнего спискаВторой способ удобно применять, когда данные выпадающего списка записаны в два столбца. В первом идет наименование группы, а во втором - подгруппы. ВАЖНО! Перед созданием зависимого списка по подгруппам необходимо отсортировать исходную таблицу по первому столбцу (столбец с группой) далее будет понятно зачем это делается.
Для создания выпадающего групп нам понадобится дополнительный столбец, содержащий уникальные значения групп из исходной таблицы. Для создания этого списка используйте функцию удаления дубликатов или воспользуйтесь командой Уникальные из надстройки VBA-Excel. Теперь создадим выпадающий список групп. Для этого выполните 4 первых пункта из первого способа создания двухуровнего списка. В качестве Источника укажите диапазон уникальных значений групп. Тут все стандартно. Рекомендация: удобно в качестве источника указывать именованный диапазон. Для его создания откройте Диспетчер имен со вкладки Формулы и присвойте имя диапазону с уникальными значениями. Теперь самая сложная часть - указать в Источнике динамическую ссылку на диапазон со значениями второго выпадающего списка (списка подгрупп). Решать ее будем с помощью функции СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, [высота], [ширина]) , которая возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.
При создании таблиц порой очень удобно использовать выпадающие (иначе говоря — раскрывающиеся) списки. Excel 2010 позволяет это делать несколькими способами. Рассмотрим их. Способ 1. Создаем раскрывающийся список в Excel 2010, используя инструмент «Проверка данных»Этот способ считается стандартным, поскольку он прост и удобен. 1. На свободном месте листа запишите все элементы раскрывающегося списка в столбик, каждый элемент — в своей ячейке. 2. Задайте имя диапазону ячеек. Для этого:
Обратите внимание, имя списка всегда должно начинаться с буквы и не содержать пробелов. 3. Выделите ячейку в таблице, где должен будет размещаться выпадающий список. 4. Откройте вкладку «Данные» и нажмите кнопку «Проверка данных». В открывшемся окне перейдите на вкладку «Параметры». В выпадающем списке «Тип данных» выберите пункт «Список». 5. В строке «Источник» нужно указать адрес, откуда будет взяты элементы создаваемого списка. Адресом будет имя, которое вы присвоили диапазону ячеек. Задать адрес можно несколькими способами.
6. Если нужно создать сообщение для ввода, откройте одноименную вкладку. Напишите текст, который будет появляться рядом с ячейкой с раскрывающимся списком при ее выделении. На соседней вкладке — «Сообщение об ошибке», таким же образом можно написать текст, уведомляющий об ошибках.
7. Подтвердите ввод нажатием «ОК», и раскрывающийся список готов. Для его открытия нажмите на кнопку со стрелкой вниз, которая появится рядом с ячейкой, содержащей список. Способ 2. Быстрое создание выпадающего спискаВыпадающий список в Excel 2010 можно создавать одним нажатием сочетания клавиш, но он может быть расположен только в одном месте — в ячейке под элементами списка. 1. Перечислите в столбик все элементы будущего выпадающего списка. 2. Выделите ячейку, находящуюся под последним элементом и нажмите сочетание клавиш «Alt» + «стрелка вниз» — список будет создан. Этот метод позволит задать ячейке значение одного из элементов.
Способ 3. Создание выпадающего списка как элемента управленияДля применения этого метода включите отображение вкладки «Разработчик»: откройте меню «Файл» — «Параметры» — «Настройка ленты». В столбце «Основные вкладки» отметьте галочкой пункт «Разработчик». Подтвердите действие нажатием «ОК» — вкладка будет создана. 1. Перечислите элементы будущего списка в столбик. 2. Из меню «Вставить» вкладки «Разработчик» выберите пункт «Элементы управления формы» — «Поле со списком».
3. Нарисуйте на листе курсором ваш будущий раскрывающийся список. Кликните по нему правой кнопкой мыши и выберите из меню пункт «Формат объекта». 4. Значением поля «Формировать список по диапазону» должен быть список элементов — выделите его курсором, и поле будет заполнено автоматически. В поле «Связь с ячейкой» указывается адрес ячейки, где будет показан порядковый номер выделенного элемента. Выберите ячейку и кликните по ней. Поле «Количество строк списка» позволяет настроить, сколько элементов будет отображено при раскрытии списка.
5. Подтвердите ввод и нажмите «ОК». Список будет создан.
Способ 4. Создание выпадающего списка как элемента ActiveXСамый сложный метод, но обладающий максимально гибкими настройками. 1. Создайте список вышеописанным способом. 2. Из меню «Вставить» вкладки «Разработчик» выберите пункт «Элементы ActiveX» — «Поле со списком».
3. Нарисуйте на листе будущий раскрывающийся список. 4. Опция, которая позволяет редактировать выпадающий список называется «Режим конструктора». Если данный режим активен — будет выделена одноименная кнопка в разделе «Элементы управления», рядом с кнопкой «Вставить». Если кнопка не выделена — режим редактирования отключен. 5. Для задания параметров списка нажмите кнопку «Свойства» того же раздела. Откроется окно настроек «Properties». Обе вкладки этого окна содержат одни и те же настройки, рассортированные в первом случае — по алфавиту, во втором — по категориям.
6. Большинство настроек можно оставить по умолчанию, а самое необходимое перечислено ниже.
7. Сохраните настройки и деактивируйте режим конструктора нажатием на одноименную кнопку. Выпадающий список будет создан, а вы сможете проверить, как он работает. Как сделать в Эксель выпадающий список, этот вопрос мы сегодня рассмотрим на практике. С помощью этой статьи, научимся делать не только выпадающие списки, но и простые, зависимые, раскрывающие. Затем скопируем сделанный список в Excel программу и вставим его. Зачем нужны списки в ЭкселеЗдравствуйте друзья! Насколько известно, программа Ексель очень удобное ПО для записи и ведения различных учетов в документе. В ней можно заполнять данные о своих доходах и расходах, рассчитывать формулы и так далее. Но, некоторые люди, задают вопрос, а как сделать в Эксель выпадающий список? Зачем он нужен? На самом деле, выпадающие списки очень необходимы, если Вы часто работаете с таблицами в данной программе и её заполняете различными цифрами и символами. Они нужны, чтобы сэкономить время вашей работы, и не затрачивать его на долгое заполнение таблиц. На практике, выпадающий список работает эффективно и запоминает все ранее записанные Вами данные в таблицах. Далее, мы создадим такой список, и сделаем другие похожие списки, которые помогут Вам упростить работу в этой программе. Создадим простой выпадающий список. Открываем программу Ексель и прописываем в первой таблице какие-нибудь слова или цифры. Выделяем один столбец со словами и далее, вверху панели управления программы нажимаем на раздел «Данные» и затем «Проверка данных» (Скрин 1).
Затем следует нажать на «Источник» и ещё раз выделить заполненный нами столбец, только после этого нажмите «ОК». Итак, мы теперь знаем, как сделать в Эксель выпадающий список его ещё называют «всплывающий». После его создания он выглядит так (Скрин 3).
Вы можете нажимать на стрелочку и выбирать написанные ранее слова в таблице. Мы рассмотрели простой вариант. Далее, будут ещё списки, но с более усложнённом вариантом. Выпадающий список с другого листа в ExcelЕсли работаете с разных листов программы Эксель, то выпадающий список с другого листа очень сильно нам будет помогать в дальнейшей работе. Сделать его довольно просто. Сначала выделяете нужный столбец с данными в программе, и жмёте снова кнопку «Проверка данных» на втором листе. Далее переходим на первый лист и в разделе «Источник», потом Вам стоит выделить ячейки и нажать на кнопку «ОК». После этого, выпадающий список должен перенестись на другой лист. Зависимые выпадающие списки в ExcelЧто такое зависимые выпадающие списки? Это списки, которые создаются из двух источников. Например, есть две таблицы, в которые Вы записали имена своих знакомых и друзей. А в следующей колонке, будет отображаться зависимый выпадающий от них список, который показывает те же данные, что были записаны в других таблицах. Далее, мы создадим такие зависимые списки и раскрывающие. Раскрывающий список в Эксель можно создать с помощью специальной функции, о которой мы ниже поговорим. Он чем то похож на выпадающий, только с использованием данного значения «СМЕЩ». Итак, переходим к процессу создания. Проделывайте всё те же действия, что мы делали в первой части статьи и далее в «Источнике» укажите такую формулу « =СМЕЩ(A$2$;0;0;5», затем нажмите «ОК», чтобы создался раскрывающийся список в . Выше мы немного говорили о зависимых списках. А теперь, создадим их. Но для начала, создайте в и заполните два столбца любыми данными. Далее, выделяйте эти столбцы в таблицах, и нажимаете вверху кнопку – «Создать из выделенного фрагмента» (Скрин 4).
Далее, создаём выпадающий список, (процесс создания был показан выше в статье). Затем, зайдите в функцию «Источники» и напишите в поле такую команду — =ДВССЫЛ($D$2). Она поможет создать зависимые списки. Кликайте кнопку «ОК», после чего должна появится ещё одна колонка в таблице, с зависимым всплывающим списком. Как скопировать выпадающий список в ExcelСкопировать выпадающий список не составит сильного труда для пользователей. Для этого выделяем левой кнопкой мыши нужную нам ячейку, и нажимаем клавиши CTRL+C (Копировать). Затем, нажмите на любой столбец в таблице Ексель. И правой кнопкой мыши вызовите команду – «Специальная вставка». В этой функции вставки нужно установить – «Условия на значения» и нажать на «ОК», чтобы сохранить изменения. После этого снова нажимаем одновременно клавиши – CTRL+V, что значит «Вставить» и впоследствии, весь скопированный выпадающий список в этой программе успешно вставится. ЗаключениеИтак, в статье мы рассмотрели вопрос, как сделать в Эксель выпадающий список и вместе с тем сделали другие списки. Эта инструкция проверялась на практике. Возможно, данный инструмент от программы Ексель Вам поможет решить некоторые вопросы во время заполнения таблиц или при работе с документами. Пользуйтесь данными функциями, и работа с программой Excel станет быстрой и удобной. Спасибо за внимание, и удачи Вам! Получайте новые статьи блога прямо себе на почту. Заполните форму, нажмите кнопку "Подписаться"
2024 © gtavrl.ru.
| |
