Для создания выпадающего списка используется формула. Как сделать сложный выпадающий список в 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.Все вышеперечисленное в одной сводной таблице.
| Список предприятий, подлежащих реконструкции | |||
| Аэропорт | |||
| Бытовое обслуживание | |||
| Организация | Вид деятельности | Площадь |
|
| ООО Успех | Химчистка | ||
| ЗАО Удача | Парикмахерская | ||
| ООО Игрок | Химчистка | ||
| Торговля | |||
| ООО Фиалка | Цветы | ||
| ЗАО Бублик | Хлеб | ||
| ООО Ирис | Цветы | ||
| Сокол | |||
| Бытовое обслуживание | |||
| ООО Ботинок | Ремонт обуви | ||
| ООО Волос | Парикмахерская | ||
| Торговля | |||
| ООО Мороз | |||
| ЗАО Калач | Хлеб | ||
| ООО Роман | Книги | ||
| ЗАО Селедка | Рыба |
Добрый день, уважаемый читатель! В этой статье, я хотел бы поговорить о том, что такое выпадающий список в ячейке, как его сделать, ну и соответственно для чего же он нужен? Это список закреплённых значений, которые доступны только с указанного диапазона значений. Это означает, что в указанную вами ячейку могут попасть данные только соответствующие значениям заданного диапазона, данные, которые не соответствуют – вводиться, не будут. В ячейке появляется возможность выбора значений, которые предлагает фиксированный список в ячейке. Ну что же, рассмотрим создание выпадающих списков и для чего же это нужно:  Я лично постоянно использую выпадающий список по всем 3 причинам. И она значительно упрощает мне работу с данными, я сознательно сокращаю к 0% возможность при введении первичных данных. Ну вот 2 вопроса, что и для чего, я рассказал, а вот о том, как это сделать ниже и поговорим. А делать выпадающий список в ячейке будем в несколько этапов: 1. Определяем диапазон ячеек, в которых мы будем создавать фиксированный список.
2. Выделяем нужный нам диапазон и в меню выбираем пункт “Данные” — “Проверка данных” , в появившемся контекстном окне выбираем из указанного выбора пункт “Список” .
3. В разблокированной ниже строке указываем диапазон данных, которые должны быть у нас в выпадающем списке. Нажимаем “Ок” и работа сделана.
В более старых версиях Excel, нет возможности формировать выпадающий список в ячейкеиспользуя данные других листов, поэтому имеет смысл создавать списки в том же листе и прятать их . Также при необходимости вы можете сформировать с вертикального списка – горизонтальный с помощью возможности . А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!
При заполнении таблички в редакторе Excel очень часто данные повторяются. К примеру, вы пишите наименование товара или ФИО работника. Сегодня мы поговорим о том, как в Эксель сделать выпадающий список, чтобы постоянно не вводить одно и то же, а просто выбирать нужное значение. Есть самый простой вариант справиться с поставленной задачей. Вы делаете правый клик мышкой по ячейке под столбиком с данными для вызова контекстного меню. Тут ищем пункт «Выбрать из раскрывающегося списка». Это же действие легко выполнить комбинацией Alt + стрелочка вниз .
Вот только данный метод не подойдет, если необходимо сделать такой объект в Эксель в другой ячейке и в 2-3 и т.д до и после. При такой необходимости используйте следующий вариант. Традиционный способВыделяете область самих ячеек, из которых создаете выпадающий перечень, далее переходите: Вставка/Имя/Присвоить (Excel 2003) В свежих версиях (2007, 2010, 2013, 2016) переходите: Формулы/Определенные имена/Диспетчер имен/Создать
Вписываете любое наименование, тапаете ОК .
Затем выделяете ячейки (или несколько), куда планируете вставить перечень заданных полей. В меню переходите: Данные/Тип данных/Список В разделе «Источник» вносите ранее написанное имя, просто отмечаете диапазон. Полученную ячеечку можете копировать в любое место, в ней уже будет меню заданных табличных элементов.
Кроме того, вы можете ее растягивать, чтобы создать диапазон. Кстати, в случае изменения информации в нем, списочная информация тоже будет изменяться, она является динамической.
Как в Экселе сделать выпадающий список: пользуемся управленческими методамиПри использовании этого варианта, вы вставляете элемент управления, представляющий собой непосредственно диапазон данных. Для этого:
Как создать в Экселе выпадающее меню: пользуемся элементами ActiveXДействия аналогичны вышеописанным, но ищем «Поле с ActiveX».
Здесь основные отличия в том, что спецэлемент ActiveX может быть в 2 вариантах - режим отладки, который позволяет менять параметры, и - режим ввода, получится сделать только выборку сведений из него. Поменять режим можно клавишей Режим Конструктора в разделе Разработчик . С помощью этого способа вы можете настроить цвет, шрифт и выполнить оперативный поиск.
Дополнительные возможностиВ программе Excel возможно создание связанного выпадающего перечня. То есть при выборе какого-то значения, в другой графе можно подобрать нужные ему параметры. К примеру, вы выбираете какой-то продукт, и понадобилось отметить меру измерения, к примеру, грамм, килограмм.
В первую очередь, следует сделать табличку, где будут сами списочки, а потом отдельно окошки с названиями продуктов.
Для второй активируем окошко проверки сведений, но в пункте «Источник» пишем «=ДВССЫЛ» и адрес 1-й ячейки. Все получилось. После этого, чтобы окошки снизу дополнились такими же свойствами, выделяем верхний участок и с нажатой мышкой перетаскиваем все вниз. Готово. Как в Эксель сделать выпадающий список? Как видите, это легко выполнить любым указанным методом. Просто выбирайте для себя самый оптимальный. А выбор будет зависеть от целей создания, предназначения, области использования, количества информации и прочего. У пользователей, которые довольно часто работают в Excel и ведут этой программе свои базы данных, наверняка нередко возникает потребность выбрать значение ячейки из заранее определенно перечня. К примеру, у нас есть перечень наименований товаров, и наша задача – заполнить каждую ячейку определенного столбца таблицы, используя данный список. Для этого нужно создать список всех наименований, а затем реализовать возможность их выбора в нужных ячейках. Такое решение избавит от необходимости писать (копировать) одно и то же название вручную много раз, а также спасет от опечаток и прочих возможных ошибок, особенно, когда речь идет о больших таблицах. Реализовать так называемый выпадающий список можно несколькими методами, которые мы и рассмотрим ниже. Самым простой и понятный метод, при котором нужно предварительно создать список в другом месте документа. Можно разместить его рядом с таблицей, либо создать новый лист и составить перечень там, чтобы не “засорять” исходный документ лишними элементами и данными.
Создать выпадающий список можно и другим образом – через инструменты разработчика с применением технологии ActiveX. Метод несколько сложнее описанного выше, но он предлагает более широкий набор инструментов по настройке списка: можно будет задать количество элементов, размер и внешний вид самого окна со списком, необходимость соответствия значения в ячейке с одним из значений списка и многое другое.
У пользователей также есть возможность создавать и более сложные взаимозависимые списки (связанные). Это значит, что список в одной ячейке будет зависеть от того, какое значение мы выбрали в другой. Например, в единицах измерения товара мы можем задать килограммы или литры. Если вы выберем в первой ячейке кефир, во второй на выбор будет предложено два варианта – литры или миллилитры. А если в первую ячейки мы остановимся на яблоках, во второй у нас будет выбор из килограммов или граммов.
Всем привет, дорогие друзья и гости моего блога. И снова с вами я, Дмитрий Костин, и сегодня мне хочется еще рассказать вам об экселе, а точнее об одной замечательной фишке, которую я теперь всегда применяю. Сталкивались ли вы с ситуацией. когда заполняете таблицу и в каком-нибудь столбце нужно постоянно вводить одно из нескольких значений. Эээээ. Давайте лучше расскажу вам на примере. Допустим, когда я создавал таблицу учета компьютерного оборудования (еще давно) у себя на работе, то чтобы сделать весь процесс работы более удобным и быстрым, я делал выпадающий список в определенных столбцах и вставлял туда определенные знaчения. И когда я заполнял столбец «Операционная система» (А ведь не на всех компах она одна и та же), то я забивал не сколько значeний (7, 8, 8.1, 10), а потом просто выбирал это всё одним нажатием кнопки мыши. И таким образом уже не нужно вбивать в кажую ячейку версию винды, или копировать из одной ячейки и вставлять в другую. В общем не буду вас томить, давайте лучше приступим. Давайте я покажу вам, как создать выпадающий список в excel, используя данные c другого листа. Для этого создадим некоторую табличку, к которой мы сможем это применить. Я буду делать это в 2013 версии, но для других версий процесс идентичный, так что не переживайте. Подготовка Основные действия Теперь точно таким же образом поработайте с графами «Фамилия специалиста»
и «Итог устранения»
, после чего снова вернитесь на главный лист и начинайте полноценно работать с таблицей. Вы сами увидите как это здорово и удобно, когда можно выбрать данные из доступных заранее подготовленных значений. Благодаря этому рутинное заполнение таблиц облегчается. Кстати в таких документах для более удобного отображения лучше . Тогда будет вообще всё круто. Ну а на сегодня я свою статью заканчиваю. Я надеюсь, что то, что вы сегодня узнали пригодится вам при работе в экселе. Если статья вам понравилась, то конечно же не забудьте подписаться на обновления моего блога. Ну а вас я с нетерпением буду снова ждать на страницах моего блога. Удачи вам и пока-пока! С уважением, Дмитрий Костин Похожие статьи
| |
Таким же образом создаем отдельные списки для всех остальных продуктов в нашем перечне.
