Excel элементы управления excelka.ru

Способ 3. Выпадающий список на основе «умной» таблицы Excel.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1 Применение комбинации клавиш

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

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

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

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

2 Использование меню

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

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

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

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

Автоматическое заполнение, скорее всего, будет некорректным. Но при выборе значений вручную они будут выбираться из нужного диапазона — это становится возможным благодаря знакам доллара перед каждой частью адреса источника ($I$6: $I$8).

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

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

3 Создание нового элемента управления

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

Включить режим разработчика. Для это следует кликнуть правой кнопкой мыши по ленте Excel, открыть настройки и поставить галочку напротив соответствующего пункта.
Перейти к появившейся новой вкладке. В меню «Вставить» выбрать поле со списком.
Кликнуть по любому месту таблицы. После того, как там появилось нужное поле, открыть его контекстное меню и выбрать «Формат объекта». Здесь следует указать связь с ячейкой, в которой будет находиться номер значения в списке (48/96 — второй, поэтому в ячейке G2 находится «2»). А еще — диапазон, откуда берутся данные.

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

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

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

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

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

Способ 2. Стандартный

Этот способ чуть сложнее, но существенно богаче по возможностям настройки:

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

Этот способ чуть сложнее, но существенно богаче по возможностям настройки

Нажмите ОК и проверьте результат

Бонусом к этому способу идет возможность задать подсказку и сообщение об ошибке при неправильном вводе на вкладках Сообщение для ввода (Input Message) и Сообщение об ошибке (Error Alert):

Бонусом к этому способу идет возможность задать сообщение об ошибке при неправильном вводе

Лента

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

Рис. 1.1. Лента

С помощью ленты можно быстро находить необходимые команды (элементы управления: кнопки, раскрывающиеся списки, счетчики, флажки и т.п.). Команды упорядочены в логические группы, собранные на вкладках.

Заменить ленту панелями инструментов или меню предыдущих версий приложения Microsoft Excel нельзя.

Удалить ленту также нельзя. Однако, чтобы увеличить рабочую область, ленту можно скрыть (свернуть).

Нажмите кнопку Свернуть ленту, расположенную в правой части линии названий вкладок (рис. 1.2).

Рис. 1.2. Сворачивание ленты
Лента будет скрыта, названия вкладок останутся (рис. 1.3).

Рис. 1.3. Окно со свернутой лентой

Ленту можно свернуть и иначе.

Щелкните правой кнопкой мыши в любом месте ленты (см. рис. 1.2 ).
В контекстном меню выберите команду Свернуть ленту.

Для использования ленты в свернутом состоянии щелкните по названию нужной вкладки, а затем выберите параметр или команду, которую следует использовать. Например, при свернутой вкладке можно выделить текст в документе Excel, щелкнуть вкладку Главная и в группе Шрифт выбрать нужный размер шрифта. После выбора размера шрифта лента снова свернется.

Чтобы быстро свернуть ленту, дважды щелкните имя активной вкладки. Для восстановления ленты дважды щелкните вкладку.

Чтобы свернуть или восстановить ленту, можно также нажать комбинацию клавиш Ctrl + F1.

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

Рис. 1.4. Отображение ленты вкладки Главная при ширине окна 1024 точки

Рис. 1.5. Отображение ленты вкладки Главная при ширине окна 800 точек

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

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

=ДВССЫЛ(A9)

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

Подготовка

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

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

Оформление может быть любым, на ваш вкус и цвет. При формировании формы я использовал стили, расположенные на вкладке Главная. Это быстро и удобно, а также позволяет в один клик изменить цветовую гамму на вкладке меню «Разметка страницы» — «Тема»

Способ 3. Элемент управления

Этот способ представляет собой вставку на лист нового объекта – элемента управления «Поле со списком» с последующей привязкой его к диапазонам на листе. Для этого:

Этот способ представляет собой вставку на лист нового объекта

Откройте вкладку Разработчик (Developer). Если этой вкладки не видно, то в Excel 2007 нужно нажать кнопку Офис – Параметры Excel – флажок Отображать вкладку Разработчик на ленте (Office Button – Excel Options – Show Developer Tab in the Ribbon) или в Excel 2010–2013 щелкните правой кнопкой мыши по ленте, выберите команду Настройка ленты (Customize Ribbon) и включите отображение вкладки Разработчик (Developer) с помощью флажка.
Найдите значок выпадающего списка среди элементов управления форм (не ActiveX!). Ориентируйтесь по всплывающим подсказкам – Поле со списком (Dropdown list). Щелкните по значку и нарисуйте небольшой горизонтальный прямоугольник – будущий список. Чтобы рисовать ровно по границам ячеек – удерживайте клавишу Alt.
Щелкните по нарисованному списку правой кнопкой мыши и выберите команду Формат объекта (Format control). В появившемся диалоговом окне задайте:
- Формировать список по диапазону – выделите ячейки с наименованиями товаров, которые должны попасть в список.
- Связь с ячейкой – укажите ячейку, куда нужно выводить порядковый номер выбранного пользователем элемента.
- Количество строк списка – сколько строк показывать в выпадающем списке. По умолчанию – 8, но можно больше, чего не позволяет предыдущий способ.

После нажатия на ОК списком можно пользоваться

ИНДЕКС

Можно дополнительно использовать функцию ИНДЕКС

Мультивыбор

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

 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. Надеемся, эта информация поможет вам.

Стандартный способ

Потребуется выделить диапазон ячеек, из которых будет создан выпадающий список, после чего Вставка – Имя – Присвоить (Excel 2003).

В более новой версии (2007, 2010, 2013, 2016) перейдите на вкладку Формулы, где в разделе Определенные имена обнаружите кнопку Диспетчер имен.

Жмем кнопку Создать, вносите имя, можно любое, после чего ОК.

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

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

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

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

Используем элементы управления

Метод основан на вставке элемента управления, называемом «поле со списком«, которое будет представлять собой диапазон данных.

Выберите вкладку Разработчик (для Excel 2007/2010), в других версиях потребуется активировать эту вкладку на ленте в параметрах – Настроить ленту.

Переходим на эту вкладку – жмем кнопку Вставить. В элементах управления выбираем Поле со списком (не ActiveX) и нажмите по значку. Нарисуйте прямоугольник.

Правой кнопкой по нему – Формат объекта.

Далее выберите Формировать по диапазону и выделите требуемые поля.

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

Применение элементов ActiveX

Все, как и в предыдущем только выбираем Поле со списком (ActiveX).

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

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

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

Как настроить сортировку в Excel? Допустим мы хотим сортировать сотрудников по дате рождения (от старых к новым) в соответствии с каждым городом (места рождения). Для этого:

Сначала для данного примера лучше сбросить сортировку предыдущих примеров. Для этого отсортируйте таблицу по первому столбцу А (№п/п) – по возрастанию.
Перейдите на любую ячейку исходной таблицы и выберите инструмент: «ГЛЛАВНАЯ»-«Редактирование»-«Сортировка и фильтр»-«Настраиваемая сортировка». Появиться диалоговое окно в котором можно задать несколько условий для сортировки таблицы.
В первом выпадающем списке выбираем заголовок столбца по которому будут сортированы данные в первую очередь. В данном примере – это «Место рождения». Второй выпадающий список оставим без изменений – «Значение», а в третьем указываем тип сортирования «От А до Я».
Нажмите на кнопку «Добавить уровень» и в следующем условии указываем новые критерии. 1 – «Дата рождения», 2 – «Значение», 3 – «От старых к новым». И нажмите на кнопку ОК.

Таблица приобрела следующую структуру:

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

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

Тогда в диалоговом окне инструмента «Настраиваемая сортировка» следует отключить опцию «Мои данные имеют заголовки столбцов». В результате чего в качестве критериев для каждого первого выпадающего списка секции «Столбец» в каждом уровне, будут использованы заголовки столбцов рабочего листа Excel (Столбец А, Столбец B, C и т.д.). А при применении всех параметров в сортировку будут включены все строки и ячейки исходного диапазона (фрагмента таблицы), что является правильным результатом в данном случаи.

Примечание. Кнопка вызова инструмента для настраиваемой сортировки так же доступна на закладке «ДАННЫЕ»-«Сортировка».

Читайте так же: Сортировка по месяцам в Excel.

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

2 метода:Отсортируйте по алфавиту в два кликаУпорядочить по алфавиту при помощи сортировки

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

Метод 1 Отсортируйте по алфавиту в два клика

Введите текст, который хотите отсортировать в ячейки одной колонки.

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

Найдите иконки сортировки «АЯ» или «ЯА» в стандартной панели инструментов, на вкладке “Данные”.

Нажмите сортировать «АЯ», чтобы отсортировать по алфавиту в порядке возрастания, и «ЯА» — чтобы отсортировать в порядке убывания. Выделенные ячейки буду отсортированы.

Если не можете найти иконку сортировки «АЯ», то можете добавить стандартную панель инструментов, зайдя на вкладку “Вид”, выбрав “Панель инструментов” и отметив “Стандартная”. Теперь стандартная панель инструментов должна быть видна, и на ней присутствует иконка сортировки «АЯ».

Ошибка Excel #ИМЯ?

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

Почему в ячейке стоит #ИМЯ?

#ИМЯ? появляется в случае, когда Excel не может понять имя формулы, которую вы пытаетесь запустить, или если Excel не может вычислить одно или несколько значений, введенных в самой формуле. Чтобы устранить эту ошибку, проверьте правильность написания формулы или используйте Мастер функций, чтобы программа построила для вас функцию.

Нет, Эксель не ищет ваше имя в этом случае. Ошибка #ИМЯ? появляется в ячейке, когда он не может прочитать определенные элементы формулы, которую вы пытаетесь запустить.

Например, если вы пытаетесь использовать формулу =A15+C18 и вместо «A» латинской напечатали «А» русскую, после ввода значения и нажатия Enter, Excel вернет #ИМЯ?.

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

Как исправить #ИМЯ? в Экселе?

Чтобы исправить ошибку #ИМЯ?, проверьте правильность написания формулы. Если написана правильно, а ваша электронная таблица все еще возвращает ошибку, Excel, вероятно, запутался из-за одной из ваших записей в этой формуле. Простой способ исправить это — попросить Эксель вставить формулу.

Выделите ячейку, в которой вы хотите запустить формулу,
Перейдите на вкладку «Формулы» в верхней части навигации.
Выберите «Вставить функцию«. Если вы используете Microsoft Excel 2007, этот параметр будет находиться слева от панели навигации «Формулы».

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