Как сделать сортировку в excel из выпадающего списка?

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

• Думаю, всем известно, как выполнить сортировку по столбцу в алфавитном порядке или по возрастанию / убыванию. Это делается одним нажатием кнопки А-Я (A-Z) и Я-А (Z-A) в разделе Редактирование (Editing) на вкладке Главная (Home) либо в разделе Сортировка и фильтр (Sort & Filter) на вкладке Данные (Data):
• 
• Однако, сортировка в Excel имеет гораздо больше настраиваемых параметров и режимов работы, которые не так очевидны, но могут оказаться очень удобны:

Сортировка по нескольким столбцам

Я покажу Вам, как в Excel сортировать данные по двум или более столбцам. Работа инструмента показана на примере Excel 2010 – именно эта версия установлена на моём компьютере.

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

Разницу можно заметить только в расцветке диалоговых окон и форме кнопок. Итак, приступим…

1. Кликните мышью по кнопке Сортировка (Sort) на вкладке Данные (Data) или по кнопке Настраиваемая сортировка (Custom sort) на вкладке Главная (Home), чтобы открыть диалоговое окно Сортировка (Sort).
2. В появившемся диалоговом окне, нажимая кнопку Добавить уровень (Add Level), установите нужное количество столбцов, по которым будет выполняться сортировка.
3. В выпадающих списках Сортировать по (Sort by) и Затем по (Then by) выберите столбцы, по которым нужно произвести сортировку данных. Например, Вы планируете, как провести выходные, и просматриваете список отелей, предложенный туристическим агентством. Вы хотите отсортировать этот список сначала по географическому признаку (столбец Region), затем по системе питания (столбец Board basis) и в конце концов по цене (столбец Price), как это показано на рисунке ниже:
4. Жмите ОК и смотрите, что получилось:
   • В первую очередь отсортированы значения в столбце Region – в алфавитном порядке.
   • Затем упорядочены значения в столбце Board basis так, что отели, предлагающие сервис “Всё включено” (AI) – сгруппированы в начале списка.
   • И, наконец, по стоимости в столбце Price данные расположены в порядке возрастания.

   

Сортировать данные по нескольким столбцам в Excel оказалось совсем не сложно, правда? Однако, в диалоговом окне Сортировка (Sort) кроется значительно больше возможностей.

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

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

Сортировка данных в Excel по заголовкам строк и столбцов

Я полагаю, что в 90% случаев сортировка данных в Excel выполняется по значению в одном или нескольких столбцах. Однако, иногда встречаются не такие простые наборы данных, которые нужно упорядочить по строке (горизонтально), то есть изменить порядок столбцов слева направо, основываясь на заголовках столбцов или на значениях в определённой строке.

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

Нам нужно отсортировать этот список фотокамер по наиболее важным для нас параметрам. Для примера первым делом выполним сортировку по названию модели:

1. Выбираем диапазон данных, которые нужно сортировать. Если нам нужно, чтобы в результате сортировки изменился порядок всех столбцов, то достаточно выделить любую ячейку внутри диапазона. Но в случае с нашим набором данных такой способ не допустим, так как в столбце A перечисляются характеристики камер, и нам нужно, чтобы он остался на своём месте. Следовательно, выделяем диапазон, начиная с ячейки B1:
2. На вкладке Данные (Data) нажимаем кнопку Сортировка (Sort), чтобы открыть одноимённое диалоговое окно. Обратите внимание на параметр Мои данные содержат заголовки (My data has headers) в верхнем правом углу диалогового окна. Если в Ваших данных нет заголовков, то галочки там быть не должно. В нашей же таблице заголовки присутствуют, поэтому мы оставляем эту галочку и нажимаем кнопку Параметры (Options).
3. В открывшемся диалоговом окне Параметры сортировки (Sort Options) в разделе Сортировать (Orientation) выбираем вариант Столбцы диапазона (Sort left to right) и жмём ОК.
4. Следующий шаг – в диалоговом окне Сортировка (Sort) под заголовком Строка (Row) в выпадающем списке Сортировать по (Sort by) выбираем строку, по значениям которой будет выполнена сортировка. В нашем примере мы выбираем строку 1, в которой записаны названия фотокамер. В выпадающем списке под заголовком Сортировка (Sort on) должно быть выбрано Значения (Values), а под заголовком Порядок (Order) установим От А до Я (A to Z).

В результате сортировки у Вас должно получиться что-то вроде этого:

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

Таким же образом мы можем сделать сортировку нашего списка фотокамер по строке, в которой указаны размеры, разрешение, тип сенсора или по любому другому параметру, который сочтём более важным.

Сделаем ещё одну сортировку, на этот раз по цене.

Наша задача – повторить описанные выше шаги 1 – 3. Затем на шаге 4 вместо строки 1 выбираем строку 4, в которой указаны розничные цены (Retail Price). В результате сортировки таблица будет выглядеть вот так:

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

Надеюсь, теперь стало ясно, как работает сортировка по строке в Excel. Но что если наши данные должны быть упорядочены не по алфавиту и не по возрастанию / убыванию?

Сортировка в произвольном порядке (по настраиваемому списку)

Если нужно упорядочить данные в каком-то особом порядке (не по алфавиту), то можно воспользоваться встроенными в Excel настраиваемыми списками или создать свой собственный. При помощи встроенных настраиваемых списков Вы можете сортировать, к примеру, дни недели или месяцы в году. Microsoft Excel предлагает два типа таких готовых списков – с сокращёнными и с полными названиями.

1. Предположим, у нас есть список еженедельных дел по дому, и мы хотим упорядочить их по дню недели или по важности.

1. Начинаем с того, что выделяем данные, которые нужно сортировать, и открываем диалоговое окно Сортировка (Sort), точно так же, как в предыдущих примерах – Данные > Сортировка (Data > Sort).
2. В поле Сортировать по (Sort by) выбираем столбец, по которому нужно выполнить сортировку. Мы хотим упорядочить наши задачи по дням недели, то есть нас интересует столбец Day. Затем в выпадающем списке под заголовком Порядок (Order) выбираем вариант Настраиваемый список (Custom list), как показано на снимке экрана ниже:
3. В диалоговом окне Списки (Custom Lists) в одноимённом поле выбираем нужный список. В нашем столбце Day указаны сокращённые наименования дней недели – кликаем по соответствующему варианту списка и жмём ОК.

Готово! Теперь домашние дела упорядочены по дням недели:

Замечание: Если Вы планируете вносить изменения в эти данные, помните о том, что добавленные новые или изменённые существующие данные не будут отсортированы автоматически. Чтобы повторить сортировку, нажмите кнопку Повторить (Reapply) в разделе Сортировка и фильтр (Sort & Filter) на вкладке Данные (Data).

Как видите, сортировка данных в Excel по настраиваемому списку – задача вовсе не сложная. Ещё один приём, которому мы должны научиться – сортировка данных по собственному настраиваемому списку.

Сортировка данных по собственному настраиваемому списку

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

Повторите шаги 1 и 2 из предыдущего примера.

Когда откроется диалоговое окно Списки (Custom Lists), в одноимённом столбце слева нажмите НОВЫЙ СПИСОК (NEW LIST) и заполните нужными значениями поле Элементы списка (List entries). Внимательно введите элементы Вашего списка именно в том порядке, в котором они должны быть расположены в результате сортировки.

Нажмите Добавить (Add), и созданный Вами список будет добавлен к уже существующим. Далее нажмите ОК.

• Вот так выглядит наш список домашних дел, упорядоченных по важности:

Подсказка: Для создания длинных настраиваемых списков удобнее и быстрее импортировать их из существующего диапазона. Об этом подробно рассказано в статье Создание настраиваемого списка из имеющегося листа Excel.

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

1. И вот, наконец, наш список домашних дел упорядочен в наивысшей степени логично, сначала по дням недели, затем по важности

На сегодня всё. Спасибо за внимание!

Оцените качество статьи. Нам важно ваше мнение:

Источник: https://office-guru.ru/excel/kak-v-excel-sortirovat-po-stroke-po-stolbcu-i-po-nastraivaemomu-spisku-402.html

Выпадающий список в Excel

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

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

Как сделать выпадающий список в Excel 2010 или 2016 с помощью одной командой на панели инструментов? На вкладке «Данные» в разделе «Работа с данными» найдите кнопку «Проверка данных». Нажмите на нее и выберите первый пункт. Откроется окно. Во вкладке «Параметры» в выпадающем разделе «Тип данных» выберите «Список».

Снизу появится строка для указания источников.

Указывать информацию можно по-разному.

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

Сначала назначим имя. Для этого создайте на любом листе такую таблицу. Выделите ее и нажмите правую кнопку мыши. Щелкните по команде «Присвоить имя».

• 
• 

Введите имя в строку сверху. Вызовите окно «Проверка данных» и в поле «Источник» укажите имя, поставив перед ним знак «=». В любом из трех случаев Вы увидите нужный элемент. Выбор значения из выпадающего списка Excel происходит с помощью мыши. Нажмите на него и появится перечень указанных данных. Вы узнали, как создать выпадающий список в ячейке Excel. Но можно сделать и больше.

Подстановка динамических данных Excel

Если Вы добавите какое-то значение в диапазон данных, которые подставляются в перечень, то в нем изменения не произойдет, пока вручную не будут указаны новые адреса. Чтобы связать диапазон и активный элемент, необходимо оформить первый как таблицу. Создайте вот такой массив. Выделите его и на вкладке «Главная» выберите любой стиль таблицы.

Обязательно поставьте галочку внизу. Вы получите такое оформление. Создайте активный элемент, как было описано выше. В качестве источника введите формулу =ДВССЫЛ(«Таблица1[Города]»)

Чтобы узнать имя таблицы, перейдите на вкладку «Конструктор» и посмотрите его. Можете поменять имя на любое другое. Функция ДВССЫЛ создает ссылку на ячейку или диапазон. Теперь ваш элемент в ячейке привязан к массиву данных.

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

=Адрес_ячейки

Например, если перечень данных находится в ячейке D1, то в ячейке, куда будут выведены выбранные результаты введите формулу

=D1

Как убрать (удалить) выпадающий список в Excel

Откройте окно настройки выпадающего списка и выберите «Любое значение» в разделе «Тип данных».

Ненужный элемент исчезнет.

Зависимые элементы

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

Это будет название города. При именовании Санкт-Петербурга и Нижнего Новгорода Вы получите ошибку, так как имя не может содержать пробелов, символов подчеркивания, специальных символов и т.д. Поэтому переименуем эти города, поставив нижнее подчеркивание. Первый элемент в ячейке A9 создаем обычным образом. А во втором пропишем формулу: =ДВССЫЛ(A9)

Сначала Вы увидите сообщение об ошибке. Соглашайтесь.

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

Как настроить зависимые выпадающие списки в Excel с поиском

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

Для второго перечня нужно ввести формулу: =СМЕЩ($A$1;ПОИСКПОЗ($E$6;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$E$6);1)

Функция СМЕЩ возвращает ссылку на диапазон, который смещен относительно первой ячейки на определенное число строк и столбцов:=СМЕЩ(начало; вниз; вправо; размер_в_строках; размер_в_столбцах)

Мы получили связанные выпадающие списки в Excel с условием на совпадение и поиском диапазона для него.

Мультивыбор

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

Откроется окно разработчика. В него надо вставить следующий алгоритм. Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«C2:F2»)) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
If Len(Target.Offset(1, 0)) = 0 Then
Target.Offset(1, 0) = Target
Else
Target.End(xlDown).Offset(1, 0) = Target
End If
Target.ClearContents
Application.EnableEvents = True
End If
End Sub

Обратите внимание, что в строке

If Not Intersect(Target, Range(«E7»)) Is Nothing And Target.Cells.Count = 1 Then

Следует проставить адрес ячейки со списком. У нас это будет E7.

Вернитесь на лист Excel и создайте в ячейке E7 список. При выборе значения будут появляться под ним.

Следующий код позволит накапливать значения в ячейке. Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range(«E7»)) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) 0 And oldval newVal Then
Target = Target & «,» & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub

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

Источник: https://WindowsTips.ru/vypadayushhij-spisok-v-excel

Как в экселе сделать выпадающий список с выбором нескольких значений? Простые и зависимые списки

Доброго времени на fast-wolker.ru! В процессе рутинной работы с таблицами всегда возникает желание ускорить ввод значений. Особенно это касается наименований позиций. Зачастую мы имеем один и тот же список,  с которыми постоянно приходится работать, а новые добавляются постепенно.

Каждый раз заносить в таблицу одно и то  же название утомительно; к тому же всегда есть возможность ошибки — поставить лишнюю точку, пробел  и так далее. Сегодня расскажу о способах, который позволяет в ряде случаев ускорить работу. В самых простых примерах, может что-то  подойдет и Вам. Итак…

Как в экселе создать выпадающий список с  выбором нескольких значений

Начну  с самого  простого способа. Его используют, если  в таблице одно и тоже наименование позиции нужно  быстро ввести несколько раз. Обязательным условием является наличие заранее составленного списка наименований и непрерывность, отсутствие в нем пустых строк:

Ставим курсор на  следующую пустую ячейку под списком кликаем правой кнопкой мыши;  из контекстного меню выбираем команду. Или используем сочетание клавиш «Alt+ Стрелка вниз». Такой способ выбора из списка использовать приходиться не слишком часто. Наименования ведь редко повторяются.

Лучше использовать следующий способ — хранение готового списка на другом листе нашей книги. А в нужной  ячейке делать ссылку на данные этого листа.

 Как в эксель сделать выпадающий список с другого листа?

Я таблицу уже создал . Теперь создадим новый  лист в документе и назовем его «база».  Лист потом этот можно скрыть, чтобы случайно не сломать документ. В произвольном месте этого  нового листа сохраним наш  будущий список,  в этом примере его можно дополнить пустыми строками и время от времени добавлять в них новые позиции.

Выделяем нужную область и в верхней левом верхнем углу присваиваем  ей имя «Список_Кондит». У нас есть теперь первый ИМЕНОВАННЫЙ диапазон в нашей книге эксель. Чтобы  потом диапазонами управлять,  нужно открыть вкладку «Формулы» и найти там  «Диспетчер имен»:

Следующий шаг — идем обратно в наш лист где создана  основная таблица. Выделяем в графе нужные строки.  Идем во вкладку «Данные»- «Проверка данных». В строке «тип данных» выбираем «Список»….

А в строке «Источник»  копируем адрес нашего диапазона из «диспетчера имен»…

Теперь из списка можно выбирать готовые значения. Если список длинный, то прокручиваем вниз. Но нельзя  будет менять шрифты, цвета  — то есть форматировать содержимое, искать по букве наименование.

Как в экселе сделать выпадающий список в ячейке с выбором нескольких данных

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

Сначала сделаем в листе «база» наши  будущие списки. У нас  будет три категории товара — полуфабрикаты, рыба и хлебобулочные. И мы заполним ими   три графы.

Сейчас в них немного позиций; ниже будет описано, как сделать, чтобы при добавлении в «базу» новых позиций они автоматом появлялись в списке при выборе. Первым сделаем выпадающий список  категорий в точности так, как это было описано в предыдущем примере.  Идем в «Данные -«Проверка данных». Укажем в качестве диапазона только строку с названиями категорий.

Нажимаем клавишу Enter. Убеждаемся, что в нашей таблице на другом листе в нужных выделенных графах появилась возможность  выбора наших категорий из списка.

Кстати, получившийся результат можно копировать в нижние ячейки -как обычные формулы. Работаем далее. Сначала займемся доработкой наших трех списков на листе «База». Доработаем сначала графу «Полуфабрикаты». Сделаем это, чтобы позже можно было добавлять в «базу» новые позиции, а они автоматом отображались бы в списках.

Ставим курсор в строку из  «Полуфабрикаты,  затем открываем вкладку «Главная» и жмем на  кнопку «Форматировать как таблицу». Дизайн выбираем любой.  Задаем по столбцу нужный вертикальный диапазон, включая  только строки с наименованиями;  ничего кроме диапазона не меняем.

В процессе этих действий нам стал доступен «конструктор таблиц». Обязательно присваиваем нашей( как бы созданной) таблице имя  (в левом верхнем углу) «Полуфабрикаты».

Повторяем действо для граф «Рыба» и «Хлебобулочные».

Завешающий этап.  Встаем в  нужную строку таблицы, как обычно идем в «Данные»-«Проверка данных», выбираем тип «Список» и в качестве диапазона указываем на  соседнюю ячейку , т.е на ранее созданный список, но…

… с использованием в «Источнике» функции ДВССЫЛ. «=ДВССЫЛ($Е$6)». Эта функция возвращает ссылку на наши диапазоны т.е. предыдущие списки, а не содержимое ячейки.  Мы работаем с данными, в функцию просто их подставили. Наименование вводится вручную.

Обратите внимание на «источник». Указана абсолютная адресация столбца и ячейки (через знаки «$»). Чтобы распространить в таком виде на остальные строки, нужно будет указывать для каждой строки свой источник данных. Это долго. Можно аккуратно убрать абсолютную адресацию руками из «источника» и после скопировать ячейку на остальные строки ниже. Тогда «формула»  распространится корректно.

Попробуйте добавить в базу новые позиции; проверьте — появляются ли они теперь автоматически в списке после указании категорий? Должно получиться.

Делаем выпадающий список в ячейке  эксель с выбором по первой букве

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

• В режиме конструктора вставим в наш документ элемент управления Active X «Поле со списком«:

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

• ListFillRange —  сюда вставляем наш именованный диапазон из «диспетчера имен»;
• LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент — нужно, если данные ее будут использоваться в других таблицах, формулах и т.д;
• ListRows — количество отображаемых строк в списке;
• Font —  выбираем шрифт, размер, стиль;
• ForeColor и BackColor — цвет текста и фона;
• ListRow —  количество возвращаемых  строк в списке;
• PrintObject —  определяет — выводить элемент управления на печать или нет (истина или ложь)

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

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

Источник: https://fast-wolker.ru/kak-v-eksel-sdelat-vypadayushhij-spisok-delaem-prostye-i-zavisimye-spiski.html

Автозаполнение выпадающего списка в Excel 2010-2013

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

Вот как это автозаполнение может выглядеть на простом примере:

Способ 1. Укажите заведомо большой источник данных

Самая простая и несложная хитрость. Создаем выпадающий список, действуя по обычному алгоритму действий: в меню выберите на вкладке Данные – Проверка данных (Data – Validation). Из перечня Тип данных (Allow) выберите вариант Список (List).

Поставьте курсор в поле Источник (Source).  Зарезервируем под источник выпадающего списка набор данных с большим запасом: например, до 55-й строки, хотя занято у нас только 7. При этом обязательно не забудьте поставить галочку в пункте «Игнорировать пустые ячейки».

Тогда ваш «резерв» из пустых значений не будет вам мешать.

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

• Конечно, в качестве источника можно указать и весь столбец:
• =$A:$A
• Но обработка такого большого диапазона может несколько замедлить вычисления.

Способ 2. Используем именованный диапазон

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

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

Выделим имеющийся в нашем распоряжении перечень имен A2:A10. Затем присвоим ему название, заполнив поле «Имя», находящееся левее строки формул. После этого создадим с ячейке С2 выпадающий список. В качестве источника данных укажем выражение

=имя

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

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

Главный недостаток здесь заключается в том, что используемый нами диапазон — статический. Автоматически его размеры измениться не могут. Согласитесь, не слишком удобный и технологичный способ автоматического увеличения выпадающего списка. Слишком много ручных операций.

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

Способ 3. Использование «умной» таблицы

Начиная с 2007 года таблица для Excel — это не просто набор строк и столбцов. Если вы просто расположите данные с привычном для нас табличном виде, то он не будет считать это таблицей.

Существует специальное форматирование (меню Главная — Форматировать как таблицу), после чего этот диапазон начинает вести себя как единое целое, приобретая целый ряд интересных свойств.

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

Любой набор ячеек в Excel может быть таким образом преобразован. Например, A1:A8. Выделите их при помощи мыши.

Затем преобразуйте в таблицу при помощи меню Главная — Форматировать как таблицу (Home — Format as Table). Укажите, что в первой строке у вас расположено название столбца. Это будет «шапка» вашей таблицы.

Внешний вид ее можете выбрать любой: это не более чем внешнее оформление и ни на что больше не влияет.

Как уже было сказано выше, «умная» таблица хороша для нас тем, что динамически меняет свои размеры при добавлении в нее данных. Если в строку ниже нее вписать какие-то данные, то она тут же присоединит к себе эту строку. Таким образом, новые данные можно просто дописывать. К примеру, впишите в A9 «кокос», и таблица тут же расширится до 9 строк.

1. Следовательно, автоматическое обновление перечня используемых данных можно организовать, если использовать данные какого-либо столбца «умной» таблицы.
2. Осталось только обозначить ее как источник данных. Проблема заключается в том, что Excel в раскрывающемся списке в качестве источника данных не понимает выражение вида
3. =Таблица1[Столбец1]

и не считает его формулой. Хотя в ячейках на листе вашей рабочей книги это вполне будет работать. Эта конструкция обозначает ссылку на первый столбец. Но в поле «Источник» она почему-то не работает.

Чтобы использовать столбец в развертывающемся списке, нам придется пойти на небольшую хитрость и воспользоваться функцией ДВССЫЛ (INDIRECT в английском варианте). Эта функция преобразует текстовую переменную в обычную ссылку Excel.

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

=ДВССЫЛ(«Таблица5[Продукт]»)

Таблица5 — это имя, автоматически присвоенное при создании таблицы. У вас оно может быть другим. На появившейся после создания «умной» таблицы вкладке меню Конструктор (Design) можно изменить это стандартное имя на свое (но без пробелов!). По этому имени мы сможем потом адресоваться к нашей таблице на любом листе этой книги.

«Продукт» — название нашего первого и единственного столбца, присвоено по его заголовку.

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

Теперь если в A9 вы допишете еще один продукт (например, кокос), то он тут же автоматически появится и в раскрывающемся списке. Аналогично будет, если мы что-то удалим. Задача автоматического увеличения выпадающего списка решена.

А вот еще полезная для вас информация:

Создаем выпадающий список в Exel при помощи формул — Задача: Создать выпадающий список в Excel таким образом, чтобы в него автоматически попадали все новые значения. Сделаем это при помощи формул, чтобы этот способ можно было использовать не только в… Как сделать выпадающий список в Excel? — Одной из наиболее полезных функций при вводе данных в Excel является возможность создания выпадающего списка. Он позволяет выбирать значение из предварительно определенного перечня. Раскрывающиеся списки позволяют пользователям вводить только те…

Источник: https://mister-office.ru/funktsii-excel/dropdown-list-auto-excel.html

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

Хитрости » 1 Май 2011       Дмитрий       167523 просмотров

Скачать файл, используемый в видеоуроке:

  Связанные выпадающие списки.xls (216,5 KiB, 1 391 скачиваний)

Чтобы понять о чем пойдет речь в статье сначала необходимо понимать что такое выпадающий список и как его создать. Теперь попробуем разобраться что значит выражение «связанный выпадающий список». Я бы еще назвал такой список зависимым. Т.е.

когда список значений одного выпадающего списка зависит от значения, выбранного в другом выпадающем списке или просто забитого в ячейку. Представим ситуацию: есть ячейка А2. В ней создан выпадающий список со значениями: Овощи, Фрукты, Мясо, Напитки.

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

Подготовка
Для начала нам потребуется создать все эти списки. Что-то вроде этого:

Далее для каждого из этих списков необходимо назначить именованный диапазон. Создать можно любым способом из описанных в этой статье.

Главное помнить — если сами списки расположено на листе, отличном от того, на котором списки выпадающие — то обязательно создавать именованный диапазон с назначением области действия — Книга.

В приложенном к статье примере диапазоны имеют имена категорий — их можно видеть в заголовках.

Если ваши категории содержат пробел — необходимо заменить его на нижнее подчеркивание (_) или удалить, т.к. в качестве именованного диапазона такое значение не подойдет и ничего в результате не получится.

Создание зависимых списков
В ячейке А2 создаем «список списков» — основной список, на основании значений которого будет создаваться второй список. Этот список может быть создан любым способом (как создать выпадающий список). Назовем его Список категорий.

В ячейке В2 потребуется создать список на основании формулы, хоть по сути и так же, как и остальные: вкладка Данные (Data)—Проверка данных (Data validation)—Список (List).

Но теперь вместо прямого указания имени списка необходимо указать ссылку на именованный диапазон, который мы выберем в Списке категорий(ячейка А2), на основании его имени. В этом нам поможет функция ДВССЫЛ (INDIRECT).

Просто записываем эту формулу в поле Источник (Source): =ДВССЫЛ($A2)

На что обратить внимание: если вы планируете распространять такой список на столбец, то ссылка должна выглядеть именно так: $A2. Перед цифрой не должно быть знака доллара ($A$2 — неправильно). Иначе зависимый список будет всегда формироваться исключительно на основании значении ячейки А2.

Источник из другой книги
Сами списки товара могут находится и в другой книге. Если книга называется Книга со списком.xls и на Лист1 в ячейке А1 в этой книге находится имя нужного нам списка, то формула будет выглядеть так:
=ДВССЫЛ(«'[Книга со списком.

xls]Лист1'!»&$A$1)
На что обратить внимание: лучше всегда перед именем книги и после имени листа ставить апостроф — '. Так вы избежите проблем и недопонимания, если имя листа или книги содержит пробелы и иные специфические символы. В отличии от списков внутри одной книги в данном случае знак доллара должен быть и перед буквой и перед цифрой.

В ином случае возможны ошибки (если, конечно, это не было сделано специально с пониманием того, что делалось).

Ограничения: данный способ создания списков хорош, но не обошлось и без ложки дегтя. Даже двух:

1. обе книги должны быть открыты. Если вы закроете книгу со списками, то получите ошибку — выпадающие списки просто перестанут работать
2. созданные подобным образом связанные списки не будут работать с динамическими именованными диапазонами

• И ничего с этими ограничениями не поделать при подобном подходе.
• Скачать пример
•   Tips_Lists_Connect_Validation.xls (26,5 KiB, 16 349 скачиваний)
• Так же см.:
  Выпадающие списки
  Именованные диапазоны
  Динамические именованные диапазоны

Статья помогла? Поделись ссылкой с друзьями!

Источник: https://www.excel-vba.ru/chto-umeet-excel/svyazannye-vypadayushhie-spiski/

Вопрос 7 Сортировка данных(списков) ms excel . Параметры сортировки

Сортировка
списков

Сортировка
или упорядочивание списков значительно
облегчает поиск информации. После
сортировки записи отображаются в
порядке, определенном значениями
столбцов (по алфавиту, по возрастанию/убыванию
цены и пр.).

• Сделайте
  небольшой список для тренировки.
• Выделите
  его.
• Нажмите
  кнопку «Сортировка
  и фильтр»
  на панели «Редактирование»
  ленты «Главная».

Выберите
«Сортировка
от А до Я».
Наш список будет отсортирован по первому
столбцу, т.е. по полю ФИО.

Если
надо отсортировать список по нескольким
полям, то для этого предназначен пункт
«Настраиваемая
сортировка..».

Сложная
сортировка подразумевает упорядочение
данных по нескольким полям. Добавлять
поля можно при помощи кнопки «Добавить
уровень».

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

Перемещать
уровни сортировки можно при помощи
кнопок «Вверх»
и «Вниз».

Не
следует забывать и о контекстном меню.
Из него, также, можно настроить сортировку
списка. К тому же есть такие интересные
варианты сортировки, связанные с
выделением того или иного элемента
таблицы.

Вопрос 8 Фильтрация данных ms excel/ условия отбора записей

1. Фильтрация
   списков
2. Основное
   отличие фильтра от упорядочивания — это
   то, что во время фильтрации записи, не
   удовлетворяющие условиям отбора,
   временно скрываются (но не удаляются),
   в то время, как при сортировке показываются
   все записи списка, меняется лишь их
   порядок.
3. Фильтры
   бывают двух типов: обычный
   фильтр
   (его еще называют автофильтр) и расширенный
   фильтр.
4. Для
   применения автофильтра нажмите ту же
   кнопку, что и при сортировке — «Сортировка
   и фильтр»
   и выберите пункт «Фильтр»
   (конечно же, перед этим должен быть
   выделен диапазон ячеек).

В
столбцах списка появятся кнопки со
стрелочками, нажав на которые можно
настроить параметры фильтра.

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

Для
формирования более сложных условий
отбора предназначен пункт «Текстовые
фильтры»
или «Числовые
фильтры».
В окне «Пользовательский
автофильтр»
необходимо настроить окончательные
условия фильтрации.

• При
  использовании расширенного фильтра
  критерии отбора задаются на рабочем
  листе.
• Для
  этого надо сделать следующее.
• Скопируйте
  и вставьте на свободное место шапку
  списка.
• В
  соответствующем поле (полях) задайте
  критерии фильтрации.

1. Выделите
   основной список.
2. Нажмите
   кнопку «Фильтр»
   на панели «Сортировка
   и фильтр»
   ленты «Данные».
3. На
   той же панели нажмите кнопку «Дополнительно».

В
появившемся окне «Расширенный
фильтр»
задайте необходимые диапазоны ячеек.

В
результате отфильтрованные данные
появятся в новом списке.

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

Вопрос
9 Подведение промежуточных итогов ms
excel/
Функции агрегирования.

АГРЕГИРОВАНИЕ
ДАННЫХ В MS EXCEL.
Агрегирование данных
списков состоит в формировании
промежуточных итогов, создании сводных
и консолидированных таблиц. Агрегирование
информации выполняется для списков
Microsoft Excel, в записях которых имеются поля
с повторяющимися значениями. Наиболее
типичными для анализа данных являются
различного вида статистические итоги.
Агрегирование данных

Источник: https://studfile.net/preview/4515493/page:5/