Как создать выпадающий: Создание раскрывающегося списка

Содержание

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

Знакомство с Excel

Знакомство с Excel

Знакомство с Excel

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

  • Создание книги
    Статья
  • Вставка и удаление листов
    Статья
  • Как перемещать и копировать листы или их данные в Excel
    Статья
  • Печать листа или книги
    Статья
  • Использование Microsoft Excel в качестве калькулятора
    Статья
  • Автоматическое заполнение ячеек листа данными
    Статья
  • Создание раскрывающегося списка
    Статья

Далее: Строки и столбцы

Excel

Ввод и форматирование данных

Проверка данных

Проверка данных

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще.

..Меньше

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

  1. На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel. Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T.

    Примечания: 

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

      Дополнительные действия не требуются.

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

  2. Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.

  3. На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.

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

  4. На вкладке Параметры в поле Тип данных выберите пункт Список.

  5. Щелкните поле Источник и выделите диапазон списка. В примере данные находятся на листе «Города» в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.

  6. Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.

  7. Откройте вкладку Сообщение для ввода.

    • Если вы хотите, чтобы при выборе ячейки появлялось всплывающее сообщение, установите флажок Отображать подсказку, если ячейка является текущей и введите заголовок и сообщение в соответствующие поля (до 225 символов). Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.

  8. Откройте вкладку Сообщение об ошибке.

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

  9. Не знаете, какой параметр выбрать в поле Вид?

    • Чтобы отобразить сообщение, не препятствующее вводу данных, которые не содержатся в раскрывающемся списке, выберите вариант Сообщение

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

    • Чтобы заблокировать пользователям ввод данных, которые не содержатся в раскрывающемся списке, выберите вариант Остановка.

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

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

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

Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка.

Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.

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

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

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

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

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

  3. На вкладке Данные в группе Инструменты нажмите кнопку Проверка данных или Проверить.

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

  4. Откройте вкладку Параметры и во всплывающем меню Разрешить выберите пункт Список.

  5. Щелкните поле Источник и выделите на листе список допустимых элементов.

    Диалоговое окно свернется, чтобы было видно весь лист.

  6. Нажмите клавишу ВВОД или кнопку Развернуть , чтобы развернуть диалоговое окно, а затем нажмите кнопку ОК.

    Советы: 

    • Значения также можно ввести непосредственно в поле Источник через запятую.

    • Чтобы изменить список допустимых элементов, просто измените значения в списке-источнике или диапазон в поле Источник.

    • Можно указать собственное сообщение об ошибке, которое будет отображаться при вводе недопустимых данных. На вкладке Данные нажмите кнопку Проверка данных или Проверить, а затем откройте вкладку Сообщение об ошибке.

См.

также

Применение проверки данных к ячейкам

  1. На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel.

    Примечания: 

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

    • Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.

    • На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.

    • На вкладке Параметры в поле Разрешить выберите пункт Список.

    • Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.

    • Установите флажок Список допустимых значений

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

  2. Откройте вкладку Сообщение об ошибке.

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

  3. Нажмите кнопку ОК.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Применение проверки данных к ячейкам

Функции Excel (по категориям)

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

Виктор Бухтеев

18K

Обсудить

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

Вариант 1: Группировка существующего списка

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

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

  2. Перейдите на вкладку «‎Данные» и выберите инструмент «‎Проверка данных».

  3. В новом окне в качестве типа данных укажите «‎Список», отыскав соответствующий вариант из выпадающего меню.

  4. В качестве источника задайте те самые ячейки, выделив их левой кнопкой мыши. Нажмите «‎ОК» для применения настроек.

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

  6. Как видно, я поставил три одинаковых значения подряд, используя реализованный только что список. Точно так же можно менять все другие клетки, входящие в диапазон.

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

Вариант 2: Ручное добавление элементов списка

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

  1. Выделите заранее подготовленную область, где хотите расположить перечень. Перейдите на вкладку «‎Данные» и откройте «‎Проверка данных».

  2. Выберите в качестве типа данных список, а в «‎Источник» перечислите значения, которые желаете в него добавить. Используйте «‎;», чтобы отделить одно значение от другого.

  3. Подтвердите изменения и вернитесь к таблице. Теперь при развертывании списка вы сможете выбрать один из добавленных вариантов.

Вариант 3: Выбор массива с именем

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

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

  2. Измените имя массива на любое, начинающееся с буквы.

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

  4. В качестве источника укажите созданный массив, написав его название после «‎=».

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

Вариант 4: Создание выпадающего списка из таблицы

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

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

  2. Выберите подходящий для вас тип оформления.

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

  4. Вы автоматически окажетесь на вкладке «‎Конструктор таблицы», где можете изменить ее имя на любое удобное, начинающееся с буквы. 

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

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

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

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

Личный опыт

Наши постоянные авторы и читатели делятся лайфхаками, основанными на личном опыте. Полная свобода самовыражения.

Рекомендуем

Выпадающий список в Excel с помощью инструментов или макросов

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

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

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

Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».

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

  1. Вручную через «точку-с-запятой» в поле «Источник».
  2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
  3. Назначить имя для диапазона значений и в поле источник вписать это имя.

Любой из вариантов даст такой результат.



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

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

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

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

  1. Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.
  2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
  3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
  4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
  5. Private Sub Worksheet_Change(ByVal Target As Range)
     
    Dim lReply As Long
     
        If Target.Cells.Count > 1 Then Exit Sub
        If Target.Address = "$C$2" Then
         If IsEmpty(Target) Then Exit Sub
           If WorksheetFunction.CountIf(Range("Деревья"), Target) = 0 Then
              lReply = MsgBox("Добавить введенное имя " & _
                             Target & " в выпадающий список?", vbYesNo + vbQuestion)
              If lReply = vbYes Then
                  Range("Деревья").Cells(Range("Деревья").Rows.Count + 1, 1) = Target
              End If
           End If
         End If
    End Sub
     
  6. Сохраняем, установив тип файла «с поддержкой макросов».
  7. Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

  1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
  2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

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

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.

  1. Создадим первый выпадающий список, куда войдут названия диапазонов.
  2. Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
  3. Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.
  4. Выбор нескольких значений из выпадающего списка Excel

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

    1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
    2. Private Sub Worksheet_Change(ByVal Target As Range)
          On Error Resume Next
          If Not Intersect(Target, Range("Е2:Е9")) Is Nothing And Target.Cells.Count = 1 Then
              Application.EnableEvents = False
              If Len(Target.Offset(0, 1)) = 0 Then
                  Target.Offset(0, 1) = Target
              Else
                  Target.End(xlToRight).Offset(0, 1) = Target
              End If
              Target.ClearContents
              Application.EnableEvents = True
          End If
      End Sub
       
    3. Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
    4. Private Sub Worksheet_Change(ByVal Target As Range)
          On Error Resume Next
          If Not Intersect(Target, Range("Н2:К2")) 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
       
    5. Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.

    6. Private Sub Worksheet_Change(ByVal Target As Range)
          On Error Resume Next
          If Not Intersect(Target, Range("C2:C5")) 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

    Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

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

    1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
    2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
    3. Жмем «Свойства» – открывается перечень настроек.
    4. Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

    Скачать пример выпадающего списка

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

Выпадающий список в ячейке листа

9699 02.10.2012 Скачать пример

Видео

 У кого мало времени и нужно быстро ухватить суть — смотрим обучающее видео:

Кому интересны подробности и нюансы всех описанных способов — дальше по тексту.

Способ 1. Примитивный

Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка (Choose from drop-down list) или нажать сочетание клавиш ALT+стрелка вниз. Способ не работает, если ячейку и столбец с данными отделяет хотя бы одна пустая строка или вам нужен товар, который еще ни разу не вводился выше:

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

  1. Выделите ячейки с данными, которые должны попасть в выпадающий список (например, наименованиями товаров).
  2. Если у вас Excel 2003 или старше — выберите в меню Вставка — Имя — Присвоить (Insert — Name — Define), если Excel 2007 или новее — откройте вкладку Формулы (Formulas) и воспользуйтесь кнопкой Диспетчер имен (Name Manager), затем Создать. Введите имя (можно любое, но обязательно без пробелов и начать с буквы!) для выделенного диапазона (например Товары). Нажмите ОК.
  3. Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню (на вкладке) Данные — Проверка (Data — Validation). Из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и введите в строчку Источник (Source) знак равенства и имя диапазона (т.е. =Товары).

Нажмите ОК.

Все! Наслаждайтесь!

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

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

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

  1. В Excel 2007/2010 откройте вкладку Разработчик (Developer). В более ранних версиях — панель инструментов Формы (Forms) через меню Вид — Панели инструментов — Формы (View — Toolbars — Forms). Если этой вкладки не видно, то нажмите кнопку Офис — Параметры Excel флажок Отображать вкладку Разработчик на ленте (Office Button — Excel Options — Show Developer Tab in the Ribbon)
  2. Найдите значок выпадающего списка среди элементов управления форм (не ActiveX!). Ориентируйтесь по всплывающим подсказкам — Поле со списком:

    Щелкните по значку и нарисуйте небольшой горизонтальный прямоугольник — будущий список.
  3. Щелкните по нарисованному списку правой кнопкой мыши и выберите команду Формат объекта (Format control). В появившемся диалоговом окне задайте
    • Формировать список по диапазону — выделите ячейки с наименованиями товаров, которые должны попасть в список
    • Связь с ячейкой — укажите ячейку куда нужно выводить порядковый номер выбранного пользователем элемента.
    • Количество строк списка — сколько строк показывать в выпадающем списке. По умолчанию — 8, но можно больше, чего не позволяет предыдущий способ.

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

Чтобы вместо порядкового номера элемента выводилось его название можно дополнительно использовать функцию ИНДЕКС (INDEX), которая умеет выводить содержимое нужной по счету ячейки из диапазона:

Способ 4.

Элемент ActiveX

Этот способ частично напоминает предыдущий. Основное отличие в том, что на лист добавляется не элемент управления, а элемент ActiveX «Поле со списком» из раскрывающегося набора под кнопкой Вставить (Insert) с вкладки Разработчик (Developer):

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

Во-первых, созданный выпадающий ActiveX список может находится в двух принципиально разных состояниях — режиме отладки, когда можно настраивать его параметры и свойства, двигать его по листу и менять размеры и — режиме ввода, когда единственное, что можно — выбирать из него данные. Переключение между этими режимами происходит с помощью кнопки Режим Конструктора (Design Mode) на вкладке Разработчик (Developer):

Если эта кнопка нажата, то мы можем настраивать параметры выпадающего списка, нажав соседнюю кнопку Свойства (Properties), которая откроет окно со списком всех возможных настроек для выделенного объекта:

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

  • ListFillRange — диапазон ячеек, откуда берутся данные для списка. Выделить мышью диапазон он не даст, надо просто вписать его руками с клавиатуры (например, Лист2!A1:A5)
  • LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент
  • ListRows — количество отображаемых строк
  • Font — шрифт, размер, начертание (курсив, подчеркивание и т.д. кроме цвета)
  • ForeColor и BackColor — цвет текста и фона, соответственно

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

При использовании этого способа, также возможно указывать в качестве ListFillRange не только одномерные диапазоны. Можно, например задать диапазон из двух столбцов и нескольких строк, указав дополнительно, что выводить нужно два столбца (свойство ColumnCount=2). Тогда можно получить весьма привлекательные результаты, окупающие все потраченные на дополнительные настройки усилия:

 

Итоговая сравнительная таблица всех способов

  Способ 1. Примитивный Способ 2. Стандартный Способ 3. Элемент управления Способ 4. Элемент ActiveX
Сложность низкая средняя высокая высокая
Возможность настройки шрифта, цвета и т.д. нет нет нет да
Количество отображаемых строк всегда 8 всегда 8 любое любое
Быстрый поиск элемента по первым буквам нет нет нет да
Необходимость использования дополнительной функции ИНДЕКС нет нет да нет
Возможность создания связанных выпадающих списков нет да нет нет

Ссылки по теме:

  • Выпадающий список с данными из другого файла
  • Создание зависимых выпадающих списков
  • Автоматическое создание выпадающих списков надстройкой PLEX
  • Выбор фото из выпадающего списка
  • Автоматическое удаление уже использованных элементов из выпадающего списка
  • Выпадающий список с автоматическим добавлением новых элементов

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

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

Часть 1: Инструкция по созданию раскрывающегося списка Word
Часть 2: Преимущества формата PDF
Часть 3: Инструкция по созданию раскрывающегося списка в PDF-файлах

Инструкция по созданию раскрывающегося списка в Word

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

Шаг 1: Откройте текстовый документ и перейдите в «Файл», а затем нажмите «Параметры».

Шаг 2: Переключитесь на «Персонализированную ленту». Откройте меню «Выбрать команду…», выберите «Команды, отсутствующие на ленте», а затем «Вставить поле формы».

Шаг 3: Затем перейдите в правую часть окна и нажмите «Новая группа», а затем «Добавить». Вы можете вставить кнопку «Вставить поле формы» и нажать «ОК».

Шаг 4: Наведите курсор на то место, куда вы хотите вставить выпадающий список, и нажмите на «Вставить поле формы», которое уже было добавлено.

Шаг 5: Затем в разделе «Тип» выберите «Выпадающий список» и сохраните его, нажав «ОК».

Шаг 6: Вернитесь на страницу Word и отредактируйте вставленное поле. В разделе «Параметры поля выпадающего списка» определите элементы раскрывающегося меню и нажмите «Добавить». Данные элементы будут вставлены в «Элементы выпадающего списка». Щелкните по полю включения раскрывающегося списка, а затем нажмите кнопку ОК.

Очевидно, что бывают случаи, когда выпадающий список очень важен для документа, независимо от его формата. Это делает документ более удобным и упрощает навигацию. Приведенная выше инструкция показывает, как создавать выпадающие список в word, но как насчет аналогичной операции в PDF? Возможно ли это? Да, возможно.

Скачать бесплатно


Преимущества формата PDF

PDF — один из самых популярных форматов файлов, а возможно и самый популярный. Его используют многие крупные организации, государственные учреждения и частные лица, потому что он предлагает гораздо больше возможностей, чем другие форматы, включая Word. Например, Управление внутренних доходов Соединенных Штатов Америки использует PDF для своих налоговых форм, а многие другие учреждения используют PDF для обмена данными в Интернете. Почему же большинство из них используют именно PDF? Причины перечислены ниже

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

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

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

Скачать бесплатно

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


Инструкция по созданию выпадающего списка в PDF-файлах

Шаг 1. Откройте PDF-файл

Откройте документ с формой в PDFelement. Используйте кнопку «Открыть файл», чтобы загрузить его в программу.

Шаг 2. Создание выпадающего списка

Выберите «Список» и укажите любое место в документе, куда его нужно добавить.

Шаг 3. Изменение свойств

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

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

Sign in

Password recovery

Восстановите свой пароль

Ваш адрес электронной почты

MicroExcel.ru Уроки Excel Выпадающий список в Excel: как сделать

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

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

Реализовать так называемый выпадающий список можно несколькими методами, которые мы и рассмотрим ниже.

Содержание

  • Выпадающий список через контекстное меню
  • Создание списка с применением инструментов разработчика
  • Связанный список
  • Заключение

Выпадающий список через контекстное меню

Смотрите также: “Как из Экселя перенести таблицу в Ворд”

Самым простой и понятный метод, при котором нужно предварительно создать список в другом месте документа. Можно разместить его рядом с таблицей, либо создать новый лист и составить перечень там, чтобы не “засорять” исходный документ лишними элементами и данными.

  1. Во вспомогательной таблице пишем перечень всех наименований – каждый с новой строки в отдельной ячейке. В итоге должен получиться один столбец с заполненными данными.
  2. Затем отмечаем все эти ячейки, нажимаем в любом месте отмеченного диапазона правой кнопкой мыши и в открывшемся списке кликаем по функции “Присвоить имя..”.
  3. На экране появится окно “Создание имени”. Называем список так, как хочется, но с  условием – первым символом должна быть буква, также не допускается использование определенных символов. Здесь же предусмотрена возможность добавления списку примечания в соответствующем текстовом поле. По готовности нажимаем OK.
  4. Переключаемся во вкладку “Данные” в основном окне программы. Отмечаем группу ячеек, для которых хотим задать выбор из нашего списка и нажимаем на значок “Проверка данных” в подразделе “Работа с данными”.
  5. На экране появится окно “Проверка вводимых значений”. Находясь во вкладке “Параметры” в типе данных останавливаемся на опции “Список”. В текстовом поле “Источник” пишем знак “равно” (“=”) и название только что созданного списка. В нашем случае – “=Наименование”. Нажимаем OK.
  6. Все готово. Справа от каждой ячейки выбранного диапазона появится небольшой значок со стрелкой вниз, нажав на которую можно открыть перечень наименований, который мы заранее составили. Щелкнув по нужному варианту из списка, он сразу же будет вставлен в ячейку. Кроме того, значение в ячейке теперь может соответствовать только наименованию из списка, что исключит любые возможные опечатки.

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

Смотрите также: “Как отобразить скрытые столбцы в Экселе”

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

  1. В первую очередь, эти инструменты нужно найти и активировать, так как по умолчанию они выключены. Переходим в меню “Файл”.
  2. В перечне слева находим в самом низу пункт “Параметры” и щелкаем по нему.
  3. Переходим в раздел “Настроить ленту” и в области “Основные вкладки” ставим галочку напротив пункта “Разработчик”. Инструменты разработчика будут добавлены на ленту программы. Кликаем OK, чтобы сохранить настройки.
  4. Теперь в программе есть новая вкладка под названием “Разработчик”. Через нее мы и будем работать. Сначала создаем столбец с элементами, которые будут источниками значений для нашего выпадающего списка.
  5. Переключаемся во вкладу “Разработчик”. В подразделе “Элементы управления” нажимаем на кнопку “Вставить”. В открывшемся перечне в блоке функций “Элементы ActiveX” кликаем по значку “Поле со списком”.
  6. Далее нажимаем на нужную ячейку, после чего появится окно со списком. Настраиваем его размеры по границам ячейки. Если список выделен мышкой, на панели инструментов будет активен “Режим конструктора”. Нажимаем на кнопку “Свойства”, чтобы продолжить настройку списка.
  7. В открывшихся параметрах находим строку “ListFillRange”. В столбце рядом  через двоеточие пишем координаты диапазона ячеек, составляющих наш ранее созданный список. Закрываем окно с параметрами, щелкнув на крестик.
  8. Затем кликаем правой кнопкой мыши по окну списка, далее – по пункту “Объект ComboBox” и выбираем “Edit”.
  9. В результате мы получаем выпадающий список с заранее определенным перечнем.
  10. Чтобы вставить его в несколько ячеек, наводим курсор  на правый нижний угол ячейки со списком, и как только он поменяет вид на крестик, зажимаем левую кнопку мыши и тянем вниз до самой нижней строки, в которой нам нужен подобный список.

Связанный список

У пользователей также есть возможность создавать и более сложные взаимозависимые списки (связанные). Это значит, что список в одной ячейке будет зависеть от того, какое значение мы выбрали в другой. Например, в единицах измерения товара мы можем задать килограммы или литры. Если вы выберем в первой ячейке кефир, во второй на выбор будет предложено два варианта – литры или миллилитры. А если в первую ячейки мы остановимся на яблоках, во второй у нас будет выбор из килограммов или граммов.

  1. Для этого нужно подготовить как минимум три столбца. В первом будут заполнены наименования товаров, а во втором и третьем – их возможные единицы измерения. Столбцов с возможными вариациями единиц измерения может быть и больше.
  2. Сначала создаем один общий список для всех наименований продуктов, выделив все строки столбца “Наименование”, через контекстное меню выделенного диапазона.
  3. Задаем ему имя, например, “Питание”.
  4. Затем таким же образом формируем отдельные списки для каждого продукта с соответствующими единицами измерения. Для большей наглядности возьмем в качестве примера первую позицию – “Лук”. Отмечаем ячейки, содержащие все единицы измерения для этого продукта, через контекстное меню присваиваем имя, которое полностью должно совпадать с наименованием. Таким же образом создаем отдельные списки для всех остальных продуктов в нашем перечне.
  5. После этого вставляем общий список с продуктами в верхнюю ячейку первого столбца основной таблицы – как и в описанном выше примере, через кнопку “Проверка данных” (вкладка “Данные”).
  6. В качестве источника указываем “=Питание” (согласно нашему названию).
  7. Затем кликаем по верхней ячейке столбца с единицами измерения, также заходим в окно проверки данных и в источнике указываем формулу “=ДВССЫЛ(A2)“, где A2 – номер ячейки с соответствующим продуктом.
  8. Списки готовы. Осталось его только растянуть их все строки таблицы, как для столбца A, так и для столбца B.

Заключение

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

Смотрите также: “Деление в Экселе: формула, как сделать”

ЧАЩЕ ВСЕГО ЗАПРАШИВАЮТ

Таблица знаков зодиака

Нахождение площади трапеции: формула и примеры

Нахождение длины окружности: формула и задачи

Римские цифры: таблицы

Таблица синусов

Тригонометрическая функция: Тангенс угла (tg)

Нахождение площади ромба: формула и примеры

Нахождение объема цилиндра: формула и задачи

Тригонометрическая функция: Синус угла (sin)

Геометрическая фигура: треугольник

Нахождение объема шара: формула и задачи

Тригонометрическая функция: Косинус угла (cos)

Нахождение объема конуса: формула и задачи

Таблица сложения чисел

Нахождение площади квадрата: формула и примеры

Что такое тетраэдр: определение, виды, формулы площади и объема

Нахождение объема пирамиды: формула и задачи

Признаки подобия треугольников

Нахождение периметра прямоугольника: формула и задачи

Формула Герона для треугольника

Что такое средняя линия треугольника

Нахождение площади треугольника: формула и примеры

Нахождение площади поверхности конуса: формула и задачи

Что такое прямоугольник: определение, свойства, признаки, формулы

Разность кубов: формула и примеры

Степени натуральных чисел

Нахождение площади правильного шестиугольника: формула и примеры

Тригонометрические значения углов: sin, cos, tg, ctg

Нахождение периметра квадрата: формула и задачи

Теорема Фалеса: формулировка и пример решения задачи

Сумма кубов: формула и примеры

Нахождение объема куба: формула и задачи

Куб разности: формула и примеры

Нахождение площади шарового сегмента

Что такое окружность: определение, свойства, формулы

Создать раскрывающийся список

Введение в Excel

Справка по Excel и обучение

Введение в Excel

Введение в Excel

Создать раскрывающийся список

  • Создать новую книгу
    Статья
  • Вставка или удаление рабочего листа
    Статья
  • Переместить или скопировать рабочие листы или данные рабочего листа
    Статья
  • Печать листа или книги
    Статья
  • Используйте Excel в качестве калькулятора
    Статья
  • Автоматическое заполнение данных в ячейках листа
    Статья
  • Создать раскрывающийся список
    Статья

Следующий: Строки и столбцы

Excel

Введите и отформатируйте данные

Проверка данных

Проверка данных

Создать раскрывающийся список

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Больше. ..Меньше

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

Хотите пройти через этот процесс? Попробуйте наш новый онлайн-учебник по раскрывающимся спискам (бета-версия).

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

    Примечания:

    • Сейчас самое время отсортировать данные в диапазоне или таблице в раскрывающемся списке.

  2. Выберите ячейку на листе, в которой вы хотите разместить раскрывающийся список.

  3. Перейдите на вкладку Данные на ленте, затем Проверка данных .

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

  4. На вкладке Параметры в поле Разрешить щелкните Список .

  5. Щелкните поле Источник , затем выберите диапазон списка. Мы помещаем наш на лист под названием Cities в диапазоне A2:A9. Обратите внимание, что мы пропустили строку заголовка, потому что мы не хотим, чтобы это была опция выбора:

    .

  6. Установите флажок в раскрывающемся списке в ячейке.

  7. Перейдите на вкладку Входное сообщение .

    • Если вы хотите, чтобы при щелчке по ячейке отображалось сообщение, установите флажок Показывать входное сообщение при выборе ячейки и введите в поля заголовок и сообщение (до 225 символов). Если вы не хотите, чтобы сообщение отображалось, снимите флажок.

  8. Если вы хотите, чтобы всплывающее сообщение отображалось, когда кто-то вводит что-то, чего нет в вашем списке, отметьте Показать предупреждение об ошибке после ввода неверных данных поле, выберите параметр в поле Стиль и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите флажок.

  9. Не знаете, какой вариант выбрать из набора Style ?

    • Чтобы показать сообщение, которое не мешает людям вводить данные, которых нет в раскрывающемся списке, щелкните Информация или Предупреждение. Информация покажет сообщение с этим значком, а Предупреждение покажет сообщение с этим значком.

    • Чтобы люди не могли вводить данные, которых нет в раскрывающемся списке, нажмите Стоп .

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

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

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

Если вы решите изменить параметры в раскрывающемся списке, см. раздел Добавление или удаление элементов из раскрывающегося списка.

Чтобы удалить раскрывающийся список, см. Удаление раскрывающегося списка.

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

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

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

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

  2. Выберите ячейки, в которых вы хотите ограничить ввод данных.

  3. На вкладке Данные в разделе Инструменты щелкните Проверка данных или Подтвердить .

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

  4. Щелкните вкладку Настройки , а затем во всплывающем меню Разрешить щелкните Список .

  5. Щелкните поле Источник , а затем на листе выберите список допустимых записей.

    Диалоговое окно сворачивается, чтобы лист было лучше видно.

  6. Нажмите RETURN или нажмите кнопку Развернуть    для восстановления диалогового окна, а затем нажмите OK .

    Советы: 

    • Вы также можете ввести значения непосредственно в поле Source , разделив их запятой.

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

    • Вы можете указать свое собственное сообщение об ошибке для ответа на ввод неверных данных. На вкладке Data щелкните Data Validation или Validate , а затем щелкните вкладку Error Alert .

См.

также

Применение проверки данных к ячейкам

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

    Примечания:

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

    • Выберите ячейку на листе, в которой вы хотите разместить раскрывающийся список.

    • Перейдите на вкладку Данные на ленте, затем нажмите Проверка данных .

    • На вкладке Параметры в поле Разрешить щелкните Список .

    • Если люди могут оставить ячейку пустой, установите флажок Игнорировать пустое .

    • Проверить Поле раскрывающегося списка в ячейке.

    • Перейдите на вкладку Входное сообщение .

      • Если вы хотите, чтобы сообщение отображалось при щелчке по ячейке, установите флажок Показать сообщение и введите в поля заголовок и сообщение (до 225 символов). Если вы не хотите, чтобы сообщение отображалось, снимите флажок.

    • Перейдите на вкладку Предупреждение об ошибке .

      • Если вы хотите, чтобы сообщение отображалось, когда кто-то вводит что-то, чего нет в вашем списке, установите флажок Показать оповещение , выберите параметр в Введите и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите флажок.

    • Нажмите OK .

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

Нужна дополнительная помощь?

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

См. также

Применение проверки данных к ячейкам

Все функции Excel (по категориям)

Создание выпадающих списков (бесплатный предварительный просмотр)

Обзор таблиц Excel

Excel

Импорт и анализ данных

Столы

Столы

Обзор таблиц Excel

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Больше. ..Меньше

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

Примечание. Таблицы Excel не следует путать с таблицами данных, которые являются частью набора команд анализа «что, если». Дополнительные сведения о таблицах данных см. в разделе Вычисление нескольких результатов с помощью таблицы данных.

Узнайте об элементах таблицы Excel

Таблица может включать следующие элементы:

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

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

  • Строки с полосами     Попеременное затенение или полоса в строках помогает лучше различать данные.

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

  • Маркер изменения размера     Маркер изменения размера в правом нижнем углу таблицы позволяет перетаскивать таблицу до нужного размера.

    Другие способы изменения размера таблицы см. в разделе Изменение размера таблицы путем добавления строк и столбцов.

Создать таблицу

Вы можете создать в электронной таблице столько таблиц, сколько захотите.

Чтобы быстро создать таблицу в Excel, выполните следующие действия:

  1. Выберите ячейку или диапазон данных.

  2. Выберите Главная > Форматировать как таблицу .

  3. Выберите стиль таблицы.

  4. Эффективная работа с табличными данными

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

    • Использование структурированных ссылок     Вместо использования ссылок на ячейки, таких как A1 и R1C1, вы можете использовать структурированные ссылки, которые ссылаются на имена таблиц в формуле. Дополнительные сведения см. в разделе Использование структурированных ссылок с таблицами Excel.

    • Экспорт таблицы Excel на сайт SharePoint

      Если у вас есть авторский доступ к сайту SharePoint, вы можете использовать его для экспорта таблицы Excel в список SharePoint. Таким образом, другие люди смогут просматривать, редактировать и обновлять данные таблицы в списке SharePoint. Вы можете создать одностороннее подключение к списку SharePoint, чтобы обновлять данные таблицы на листе, чтобы включить изменения, внесенные в данные в списке SharePoint. Дополнительные сведения см. в статье Экспорт таблицы Excel в SharePoint.

      Нужна дополнительная помощь?

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

      См. также

      Отформатировать таблицу Excel

      Проблемы с совместимостью таблиц Excel

      Применить проверку данных к ячейкам

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

      Попробуйте!

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

      2. Выберите  Данные > Проверка данных .

              

      3. На вкладке Настройки в разделе Разрешить выберите вариант:

        • Целое число  – разрешить ячейке принимать только целые числа.

        • Decimal  – разрешить ячейке принимать только десятичные числа.

        • List  — выбрать данные из выпадающего списка.

        • Дата  – разрешить ячейке принимать только дату.

        • Время  — чтобы ячейка принимала только время.

        • Длина текста  – для ограничения длины текста.

        • Пользовательский  – для пользовательской формулы.

      4.  В разделе Данные выберите условие.

      5. Задайте другие необходимые значения в зависимости от того, что вы выбрали для  Разрешить  и  Данные .

      6. Выберите вкладку Входное сообщение и настройте сообщение, которое пользователи будут видеть при вводе данных.

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

      8. Выберите вкладку Предупреждение об ошибке , чтобы настроить сообщение об ошибке и выбрать стиль .

      9. Выберите  OK .

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

      Загрузите наши примеры

      Загрузите пример книги со всеми примерами проверки данных в этой статье.

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

      Ограничить ввод данных

      1. Выберите ячейки, в которых вы хотите ограничить ввод данных.

      2. На вкладке Данные щелкните Проверка данных > Проверка данных .

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

      3. В поле Разрешить выберите тип данных, которые вы хотите разрешить, и заполните ограничивающие критерии и значения.

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

      Запрашивать у пользователей действительные записи

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

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

      2. На вкладке Данные щелкните Проверка данных > Проверка данных .

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

      3. На вкладке Входное сообщение выберите Показывать входное сообщение при выборе ячейки 9флажок 0059.

      4. В поле Заголовок введите заголовок сообщения.

      5. В поле Ввод сообщения введите сообщение, которое вы хотите отобразить.

      Отображение сообщения об ошибке при вводе неверных данных

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

      1. Выберите ячейки, в которых вы хотите отобразить сообщение об ошибке.

      2. На вкладке Данные щелкните Проверка данных > Проверка данных .

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

      3. В предупреждении об ошибке , в поле Заголовок введите заголовок сообщения.

      4. В поле Сообщение об ошибке введите сообщение, которое должно отображаться при вводе неверных данных.

      5. Выполните одно из следующих действий:

        от до

        На Стиль всплывающее меню, выберите

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

        Стоп

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

        Предупреждение

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

        Важно

      Добавить проверку данных в ячейку или диапазон

      Примечание. Первые два шага в этом разделе предназначены для добавления любого типа проверки данных. Шаги 3–7 предназначены специально для создания раскрывающегося списка.

      1. Выберите одну или несколько ячеек для проверки.

      2. На вкладке Data в группе Data Tools щелкните Data Validation .

      3. На вкладке Параметры в поле Разрешить выберите Список .

      4. Убедитесь, что установлен флажок Раскрывающийся список в ячейке . В противном случае вы не сможете увидеть стрелку раскрывающегося списка рядом с ячейкой.

      5. Чтобы указать, как вы хотите обрабатывать пустые (нулевые) значения, установите или снимите флажок Игнорировать пустые .

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

      Примечания:

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

      • Удалить проверку данных . Выберите ячейку или ячейки, содержащие проверку, которую вы хотите удалить, затем перейдите к Данные > Проверка данных и в диалоговом окне проверки данных нажмите кнопку Очистить все , затем нажмите OK .

      В следующей таблице перечислены другие типы проверки данных и показаны способы их добавления в рабочие листы.

      Для этого:

      Выполните следующие действия:

      Ограничить ввод данных целыми числами в установленных пределах.

      1. Выполните шаги 1-2 выше.

      2. В поле Данные выберите нужный тип ограничения. Например, чтобы установить верхний и нижний пределы, выберите из .

      3. Введите минимальное, максимальное или конкретное допустимое значение.

        Вы также можете ввести формулу, которая возвращает числовое значение.

        Предположим, вы проверяете данные в ячейке F1. Чтобы установить минимальный предел вычетов, равный удвоенному количеству детей в этой ячейке, выберите больше или равно в поле Данные и введите формулу =2*F1 в поле Минимум .

      Ограничить ввод данных десятичным числом в определенных пределах.

      1. Выполните шаги 1-2 выше.

      2. В поле Разрешить выберите Decimal .

      3. В поле Данные выберите нужный тип ограничения. Например, чтобы установить верхний и нижний пределы, выберите из .

      4. Введите минимальное, максимальное или конкретное допустимое значение.

        Вы также можете ввести формулу, которая возвращает числовое значение. Например, чтобы установить максимальный лимит комиссионных и бонусов в размере 6% от зарплаты продавца в ячейке E1, выберите меньше или равно в поле Данные и введите формулу =E1*6% в поле Максимум .

        Примечание. Чтобы разрешить пользователю вводить проценты, например 20 %, выберите Десятичный в поле Разрешить , выберите тип ограничения в поле Данные , введите минимум, максимум или конкретное значение в виде десятичного числа, например . 2 , а затем отобразите ячейку проверки данных в процентах, выбрав ячейку и нажав Процентный стиль в группе Номер на вкладке Домашний .

      Ограничить ввод данных датой в диапазоне дат.

      1. Выполните шаги 1-2 выше.

      2. В поле Разрешить выберите Дата .

      3. Введите начальную, конечную или конкретную дату для разрешения.

        Вы также можете ввести формулу, которая возвращает дату. Например, чтобы установить временной интервал между сегодняшней датой и 3 днями от сегодняшней даты, выберите между в поле Данные , введите =СЕГОДНЯ() в поле Дата начала и введите =СЕГОДНЯ()+3 в поле Дата окончания .

      Ограничить ввод данных временем в рамках временного интервала.

      1. Выполните шаги 1-2 выше.

      2. В поле Разрешить выберите Время .

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

      4. В поле Разрешить выберите Длина текста .

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

      6. В этом случае мы хотим ограничить ввод 25 символами, поэтому выберите меньше или равно в поле Данные и введите 25 в поле Максимум .

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

      1. Выполните шаги 1-2 выше.

      2. В поле Разрешить выберите нужный тип данных.

      3. В поле Данные выберите нужный тип ограничения.

      4. В поле или полях под полем Данные щелкните ячейку, которую вы хотите использовать, чтобы указать, что разрешено.

        Например, чтобы разрешить ввод для учетной записи только в том случае, если результат не будет превышать бюджет в ячейке E1, выберите Разрешить > Целое число, Данные , меньше или равно и Максимум >= =Е1 .

      Примечания:

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

      • Скриншоты в этой статье были сделаны в Excel 2016; но функциональность такая же, как и в Excel для Интернета.

      Чтобы убедиться, что

      Введите эту формулу

      Ячейка, содержащая идентификатор продукта (C2), всегда начинается со стандартного префикса «ID-» и имеет длину не менее 10 (более 9) символов.

      =И(ВЛЕВО(C2,3)=»ID-«,ДЛСТР(C2)>9)

      Ячейка, содержащая название продукта (D2), содержит только текст.

      =ИСТЕКСТ(D2)

      Ячейка, содержащая чей-то день рождения (B6), должна быть больше, чем количество лет, указанное в ячейке B4.

      =ЕСЛИ(B6<=(СЕГОДНЯ()-(365*B4)),ИСТИНА,ЛОЖЬ)

      Все данные в диапазоне ячеек A2:A10 содержат уникальные значения.

      =СЧЁТЕСЛИ($A$2:$A$10;A2)=1

      Примечание.  Сначала необходимо ввести формулу проверки данных для ячейки A2, а затем скопировать A2 в A3:A10, чтобы второй аргумент СЧЕТЕСЛИ соответствовал текущей ячейке. это 9Часть 0058 A2)=1 изменится на A3)=1, A4)=1 и так далее.

      Для получения дополнительной информации

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

      =ЧИСЛО(НАЙТИ(«@»,B4))

      Хотите больше?

      Подробнее о проверке данных

      Добавить или удалить элементы из раскрывающегося списка

      Удалить раскрывающийся список

      Сортировка данных в диапазоне или таблице

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

      Данные можно сортировать по тексту (от А до Я или от Я до А), числам (от меньшего к большему или от большего к меньшему), а также по дате и времени (от самого старого к самому новому и от нового к самому старому) в одном или нескольких столбцах. Вы также можете сортировать по созданному вами пользовательскому списку (например, «Большой», «Средний» и «Маленький») или по формату, включая цвет ячейки, цвет шрифта или набор значков.

      Примечания:

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

      • Дополнительные сведения см. в разделах Фильтрация данных в таблице или диапазоне Excel и Применение условного форматирования в Excel .

      Сортировать текст

      1. Выберите ячейку в столбце, который вы хотите отсортировать.

      2. На вкладке Данные в группе Сортировка и фильтрация выполните одно из следующих действий:

        • Для быстрой сортировки по убыванию щелкните ( Сортировать от Z до A ).

      Примечания: Возможные проблемы

      • Убедитесь, что все данные хранятся в виде текста.     Если столбец, который вы хотите отсортировать, содержит числа, сохраненные в виде чисел, и числа, сохраненные в виде текста, вам необходимо отформатировать их все как числа или как текст. Если вы не применяете этот формат, числа, сохраненные как числа, сортируются перед числами, сохраненными как текст. Чтобы отформатировать все выбранные данные как текст, нажмите Ctrl+1 , чтобы открыть диалоговое окно Формат ячеек , нажмите Число , а затем в разделе Категория щелкните Общие , Номер или Текст .

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

      1. Выберите ячейку в столбце, который вы хотите отсортировать.

      2. На вкладке Данные в группе Сортировка и фильтрация выполните одно из следующих действий:

        • Чтобы отсортировать от меньшего к большему, нажмите ( Сортировать от меньшего к большему ).

        • Для сортировки от большего к меньшему нажмите ( Сортировать от большего к меньшему ).

      Примечания:

      • Возможная проблема

      • Убедитесь, что все числа сохранены как числа.     Если результаты не соответствуют вашим ожиданиям, столбец может содержать числа, сохраненные в виде текста, а не чисел. Например, отрицательные числа, импортированные из некоторых систем учета, или числа, введенные с начальным апострофом ( ) сохраняются как текст. Дополнительные сведения см. в разделе Исправление чисел в текстовом формате путем применения числового формата.

      1. Выберите ячейку в столбце, который вы хотите отсортировать.

      2. На вкладке Данные в разделе Сортировка и фильтрация выполните одно из следующих действий:

        • Для сортировки от более поздней к более ранней дате или времени щелкните ( Сортировать от новых к самым старым ).

      Примечания: Возможная проблема

      • Убедитесь, что даты и время хранятся как даты или время.     Если результаты не соответствуют вашим ожиданиям, столбец может содержать даты или время, сохраненные в виде текста, а не в виде дат или времени. Чтобы Excel правильно сортировал даты и время, все даты и время в столбце должны храниться в виде порядкового номера даты или времени. Если Excel не может распознать значение как дату или время, дата или время сохраняются в виде текста. Дополнительные сведения см. в разделе Преобразование дат, хранящихся в виде текста, в даты.

      • Если вы хотите отсортировать по дням недели, отформатируйте ячейки, чтобы показать день недели. Если вы хотите отсортировать по дням недели независимо от даты, преобразуйте их в текст с помощью функции ТЕКСТ. Однако функция ТЕКСТ возвращает текстовое значение, поэтому операция сортировки будет основываться на буквенно-цифровых данных. Дополнительные сведения см. в разделе Отображение дат как дней недели.

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

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

      1. Выберите любую ячейку в диапазоне данных.

      2. На вкладке Данные в группе Сортировка и фильтрация щелкните Сортировка .

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

      4. В разделе Sort On выберите тип сортировки. Выполните одно из следующих действий:

        • Для сортировки по тексту, числу или дате и времени выберите Значения .

        • В разделе Заказать выберите способ сортировки. Выполните одно из следующих действий:

          • Для текстовых значений выберите от A до Z или от Z до A .

          • Для числовых значений выберите От наименьшего к наибольшему или От наибольшего к наименьшему .

          • Для сортировки на основе пользовательского списка выберите Пользовательский список .

        • Чтобы добавить еще один столбец для сортировки, щелкните Добавить уровень и повторите шаги с третьего по пятый.

        • Чтобы скопировать столбец для сортировки, выберите запись и нажмите Копировать уровень .

        • Чтобы удалить столбец для сортировки, выберите запись и нажмите Удалить уровень .

          Примечание. Необходимо сохранить хотя бы одну запись в списке.

        • Чтобы изменить порядок сортировки столбцов, выберите запись и нажмите кнопку Вверх или Вниз стрелка рядом с кнопкой Опции для изменения порядка.

          Записи выше в списке сортируются перед записями ниже в списке.

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

      1. Выберите ячейку в столбце, который вы хотите отсортировать.

      2. На вкладке Данные в группе Сортировка и фильтрация щелкните Сортировка .

      3. В диалоговом окне Сортировка в разделе Столбец в поле Сортировка по выберите столбец, который вы хотите отсортировать.

      4. В разделе Приказ щелкните стрелку рядом с кнопкой, а затем, в зависимости от типа формата, выберите цвет ячейки, цвет шрифта или значок ячейки.

      5. Затем выберите способ сортировки. Выполните одно из следующих действий:

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

        • Чтобы переместить цвет ячейки, цвет шрифта или значок вниз или вправо, выберите Снизу для сортировки по столбцам и Справа для сортировки по строкам.

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

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

        Убедитесь, что вы выбрали тот же столбец в Затем по ящику и сделайте такой же выбор под Заказ .

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

      Пользовательский список можно использовать для сортировки в заданном пользователем порядке. Например, столбец может содержать значения, по которым вы хотите выполнить сортировку, например «Высокий», «Средний» и «Низкий». Как можно отсортировать так, чтобы сначала отображались строки, содержащие высокий уровень, затем средний, а затем низкий? Если бы вы сортировали в алфавитном порядке, сортировка «от А до Я» поместила бы «Высокий» вверху, а «Низкий» — перед «Средним». И если вы отсортируете от «Я» до «А», «Средний» появится первым, а «Низкий» посередине. Независимо от порядка, вы всегда хотите, чтобы «Средний» был в середине. Создав свой собственный список, вы можете обойти эту проблему.

        При необходимости создайте пользовательский список:

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

        2. Выберите диапазон, который вы только что ввели. Используя предыдущий пример, выберите ячейки A1:A3.

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

        4. Максимальная длина пользовательского списка составляет 255 символов, и первый символ не должен начинаться с цифры.

  • На вкладке Данные в группе Сортировка и фильтрация щелкните Сортировка .

  • В диалоговом окне Сортировка в разделе Столбец в поле Сортировка по или Затем по выберите столбец, который вы хотите отсортировать по пользовательскому списку.

  • В разделе Заказ выберите Пользовательский список .

  • В диалоговом окне Custom Lists выберите нужный список. Используя пользовательский список, созданный в предыдущем примере, щелкните Высокий, Средний, Низкий .

  • Нажмите OK .

    1. На вкладке Данные в группе Сортировка и фильтрация щелкните Сортировка .

    2. В диалоговом окне Параметры сортировки выберите С учетом регистра .

    3. Нажмите OK дважды.

    Чаще всего сортируют сверху вниз, но можно также сортировать слева направо.

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

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

    2. На вкладке Данные в группе Сортировка и фильтрация щелкните Сортировка .

    3. В диалоговом окне Сортировка нажмите Параметры .

    4. Под Ряд , в Сортировать по выберите строку, которую вы хотите отсортировать. Обычно это будет строка 1, если вы хотите отсортировать по строке заголовка.

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

    5. Для текстовых значений выберите от A до Z или от Z до A .

    6. Для числовых значений выберите От наименьшего к наибольшему или От наибольшего к наименьшему .

    7. Для значений даты или времени выберите От самых старых до самых новых или От новых до самых старых .

  • Чтобы отсортировать по цвету ячейки, цвету шрифта или значку ячейки, сделайте следующее:

    1. Менее Сортировать по , выберите Цвет ячейки , Цвет шрифта или Значок ячейки .

    2. В разделе Заказ выберите цвет ячейки, цвет шрифта или значок ячейки, затем выберите Слева или Справа .

  • Примечание. При сортировке строк, являющихся частью структуры листа, Excel сортирует группы самого высокого уровня (уровень 1), чтобы строки или столбцы сведений оставались вместе, даже если строки или столбцы сведений скрыты.

    Для сортировки по части значения в столбце, например по коду номера детали (789- WDG -34), фамилии (Кэрол Филипс) или имени (Филипс, Кэрол), сначала необходимо разделить разделите столбец на два или более столбца, чтобы значение, по которому вы хотите отсортировать, находилось в отдельном столбце. Для этого вы можете использовать текстовые функции для разделения частей ячеек или воспользоваться мастером преобразования текста в столбцы. Примеры и дополнительные сведения см. в разделах Разделение текста на разные ячейки и Разделение текста по столбцам с помощью функций.

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

    К счастью, Excel предупредит вас, если обнаружит, что вы собираетесь это сделать:

    Если вы не собирались сортировать таким образом, то нажмите кнопку Расширить выделение , иначе выберите Продолжить с текущим выделением .

    Если результаты не соответствуют вашим ожиданиям, нажмите Отменить .

    Примечание. Вы не можете сортировать таким образом в таблице.

    Если вы получаете неожиданные результаты при сортировке данных, сделайте следующее:

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

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

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

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

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

    • Чтобы исключить первую строку данных из сортировки, поскольку она является заголовком столбца, на Вкладка Главная , в группе Редактирование щелкните Сортировка и фильтрация , щелкните Пользовательская сортировка и выберите Мои данные имеют заголовки .

    • Чтобы включить первую строку данных в сортировку, поскольку она не является заголовком столбца, на вкладке Главная в группе Редактирование щелкните Сортировка и фильтр , щелкните Пользовательская сортировка , а затем очистить Мои данные имеют заголовки .

    Если ваши данные отформатированы как таблица Excel, вы можете быстро отсортировать и отфильтровать их с помощью кнопок фильтра в строке заголовка.

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

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

    3. Выберите ячейку для сортировки:

      • Если у ваших данных есть строка заголовка, выберите ту, по которой вы хотите отсортировать, например Население .

      • На вкладке Данные выберите один из методов сортировки:

        • Сортировка по возрастанию для сортировки от А до Я, от меньшего к большему или от самой ранней до самой поздней даты.

        • Сортировка по убыванию для сортировки от Z до A, от наибольшей до наименьшей или от самой последней до самой ранней даты.

        

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

    Выберите любую ячейку в диапазоне данных.

    1. На вкладке Данные в группе Сортировка и фильтрация нажмите Пользовательская сортировка .

    2. В диалоговом окне Пользовательская сортировка в разделе Столбец в поле Сортировать по выберите первый столбец, который вы хотите отсортировать.

      Примечание. Меню Сортировать по отключено, так как оно еще не поддерживается. На данный момент вы можете изменить его в настольном приложении Excel.

    3. В разделе  Заказ выберите способ сортировки:

      • Сортировка по возрастанию для сортировки от А до Я, от меньшего к большему или от самой ранней до самой поздней даты.

      • Сортировка по убыванию для сортировки от Z до A, от наибольшей до наименьшей или от самой последней до самой ранней даты.

    4. Чтобы добавить еще один столбец для сортировки, нажмите Добавить , а затем повторите шаги 5 и 6.

    5. Чтобы изменить порядок сортировки столбцов, выберите запись и нажмите стрелку Вверх или Вниз рядом с кнопкой Параметры  .

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

    1. Выберите ячейку в столбце, который вы хотите отсортировать

    2. На вкладке Данные в группе Сортировка и фильтрация выберите Пользовательская сортировка .

    3. В диалоговом окне Пользовательская сортировка в разделе Столбцы выберите столбец, который нужно отсортировать.

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

    5. В разделе  Заказ выберите нужный заказ (то, что вы увидите, зависит от типа вашего формата). Затем выберите цвет ячейки, цвет шрифта или значок ячейки.

    6. Далее вы выберете способ сортировки, перемещая цвет ячейки, цвет шрифта или значок:

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

      • Чтобы переместиться вверх или влево: выберите Сверху для сортировки по столбцам и Слева для сортировки по строкам.

      • Чтобы переместиться вниз или вправо: выберите Внизу для сортировки по столбцам и Справа для сортировки по строкам.

    7. Чтобы указать следующий цвет ячейки, цвет шрифта или значок для сортировки, выберите Добавить уровень и повторите шаги 1–5.

    • Убедитесь, что столбец в поле Затем по и выбор под Заказ совпадают.

    • На вкладке Данные в группе Сортировка и фильтрация нажмите Пользовательская сортировка .

    • В диалоговом окне Пользовательская сортировка нажмите  Параметры .

    • Нажмите  OK .

    • Чаще всего сортируют сверху вниз, но можно также сортировать слева направо.

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

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

      2. На вкладке Данные в группе Сортировка и фильтрация выберите Пользовательская сортировка .

      3. В диалоговом окне «Пользовательская сортировка» нажмите  Параметры .

      4. В разделе Ориентация нажмите Сортировка слева направо  

      5. В строке    в раскрывающемся списке «Сортировать по» выберите строку, которую нужно отсортировать. Обычно это будет строка 1, если вы хотите отсортировать по строке заголовка.

      6. Для сортировки по значению выберите один из вариантов в раскрывающемся списке Заказ :

        • Сортировка по возрастанию для сортировки от А до Я, от меньшего к большему или от самой ранней до самой поздней даты.

        • Сортировка по убыванию для сортировки от Z до A, от наибольшей до наименьшей или от последней к самой ранней дате

      Нужна дополнительная помощь?

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

      См. также

      Используйте функции SORT и SORTBY для автоматической сортировки данных.

      Как создать раскрывающийся список в Excel за 60 секунд или меньше

      Перейти к содержимому Как создать раскрывающийся список в Excel за 60 секунд или меньше

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

      Это позволяет вам (создателю) контролировать все записи данных для важных ячеек.

      Раскрывающийся список — самый популярный инструмент проверки данных в Excel.

      И это тоже выглядит довольно круто😎

      Просто выполните эти 5 шагов и узнайте, как вставить раскрывающийся список в Excel.

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

      Если вы хотите следовать тому, что делаю я, загрузите файл проекта Excel здесь.

      Шаг 1: Выберите элементы раскрывающегося списка

      Выпадающие списки управляют вводом данных.

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

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

      Запишите нужные параметры раскрывающегося меню в виде списка.

      Шаг 2: Перейдите на вкладку «Данные»

      Этот шаг прост😊

      Просто наведите курсор на вкладку «Данные» и щелкните левой кнопкой мыши.

      Шаг 3: Нажмите «Проверка данных»

      Теперь нажмите «Проверка данных» в середине ленты.

      (Не нажимайте на маленькую стрелку — просто нажмите на верхнюю часть кнопки).

      Теперь появится диалоговое окно «Проверка данных». Здесь вы выбираете настройки для раскрывающегося списка.

      Давайте углубимся🤿

      Шаг 4: Разрешить проверку данных «Список»

      В диалоговом окне проверки данных щелкните раскрывающийся список и выберите, какой метод проверки данных вы хотите использовать (как мета что?!).

      Выберите «Список» из… ну… списка !

      Я обещал, что вы сможете добавить раскрывающийся список менее чем за 60 секунд, не так ли?

      Итак, давайте не отставать🏃🏽

      Каспер Лангманн , специалист Microsoft Office

      Шаг 5: Выберите исходный список выпадающий список?

      Теперь пришло время соединить этот список параметров с раскрывающимся меню.

      Один раз щелкните левой кнопкой мыши в поле «Источник:».

      Затем перейдите к расположению вашего списка с разрешенными значениями. Тот, который вы написали на шаге 1, помните?📝

      Выберите ячейки, содержащие элементы раскрывающегося списка.

      И нажмите «ОК».

      PRO TIP

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

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

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

      После того, как вы нажмете «ОК», ваш раскрывающийся список готов к использованию.

      Круто, да?💪

      Оповещение об ошибке

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

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

      Но если введены неверные данные, появится предупреждение об ошибке.

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

      Чтобы улучшить его, просто перейдите на вкладку «Предупреждение об ошибке» после нажатия «Проверка данных».

      Отсюда убедитесь, что установлен флажок «Показывать предупреждение об ошибке после ввода неверных данных».

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

      Затем нажмите «ОК».

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

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

      Каспер Лангманн , специалист по Microsoft Office

      Ввод сообщения

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

      Но добавление «Входное сообщение» делает ввод данных еще проще и надежнее.

      При создании раскрывающегося списка вы можете добавить входное сообщение на вкладке «Входящее сообщение» в диалоговом окне проверки данных.

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

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

      Каспер Лангманн , специалист Microsoft Office

      Вот и все. Что теперь?

      Вы только что узнали, как создать раскрывающийся список в Excel.

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

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

      Молодец👍

      Но в Excel есть нечто большее, чем искоренение опечаток с помощью выпадающих списков.

      Давайте остановимся и подумаем: почему опечатки так опасны?

      Потому что они делают ваши функции менее эффективными.

      Особенно функции: ЕСЛИ, СУММЕСЛИ и ВПР.

      И это 3 самые важные функции в Excel.

      Если вы еще не знакомы с ними, я настоятельно рекомендую вам записаться на мой бесплатный онлайн-курс, состоящий из 3 частей, и раз и навсегда изучить ЕСЛИ, СУММЕСЛИ и ВПР.

      Другие соответствующие ресурсы

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

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

      Это называется зависимыми выпадающими списками (некоторые называют их каскадными выпадающими списками), и вы можете узнать все об этом в моем видео на YouTube здесь.

      Круто, правда? 😎

      В общем, выпадающие списки хороши, когда опечатки и другие неверные данные очень раздражают. Фильтры сильно страдают от опечаток. То же самое можно сказать и о сводных таблицах и функциях поиска, таких как ВПР, ПОИСКПОЗ ПО ИНДЕКСУ и XПР.

      Спасибо за внимание👋

      Kasper Langmann2022-08-04T10:58:03+00:00 Ссылка для загрузки страницы

      Создание раскрывающихся списков в Excel (в простых шагах)

      Создание раскрывающегося списка | Разрешить другие записи | Добавить/удалить элементы | Динамический раскрывающийся список | Удалить раскрывающийся список | Зависимые раскрывающиеся списки | Магия стола

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

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

      Чтобы создать раскрывающийся список в Excel, выполните следующие действия.

      1. На втором листе введите элементы, которые должны отображаться в раскрывающемся списке.

      Примечание: если вы не хотите, чтобы пользователи имели доступ к элементам на Листе2, вы можете скрыть Лист2. Для этого щелкните правой кнопкой мыши вкладку листа Sheet2 и выберите «Скрыть».

      2. На первом листе выберите ячейку B1.

      3. На вкладке Данные в группе Работа с данными щелкните Проверка данных.

      Появится диалоговое окно «Проверка данных».

      4. В поле Разрешить щелкните Список.

      5. Щелкните в поле Источник и выберите диапазон A1:A3 на Листе2.

      6. Нажмите OK.

      Результат:

      Примечание: чтобы скопировать/вставить выпадающий список, выберите ячейку с выпадающим списком и нажмите CTRL + c, выберите другую ячейку и нажмите CTRL + v.

      7. Вы также можете вводить элементы непосредственно в поле Источник вместо использования ссылки на диапазон.

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

      Разрешить другие записи

      Вы также можете создать раскрывающийся список в Excel, который разрешает другие записи.

      1. Во-первых, если вы введете значение, которого нет в списке, Excel покажет предупреждение об ошибке.

      Чтобы разрешить другие записи, выполните следующие шаги.

      2. На вкладке Данные в группе Работа с данными щелкните Проверка данных.

      Появится диалоговое окно «Проверка данных».

      3. На вкладке «Предупреждение об ошибке» снимите флажок «Показывать предупреждение об ошибке после ввода неверных данных».

      4. Нажмите OK.

      5. Теперь вы можете ввести значение, которого нет в списке.

      Добавить/удалить элементы

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

      1. Чтобы добавить элемент в раскрывающийся список, перейдите к элементам и выберите элемент.

      2. Щелкните правой кнопкой мыши и выберите Вставить.

      3. Выберите «Сдвинуть ячейки вниз» и нажмите «ОК».

      Результат:

      Примечание. Excel автоматически изменил ссылку на диапазон с Лист2!$A$1:$A$3 на Лист2!$A$1:$A$4. Вы можете проверить это, открыв диалоговое окно «Проверка данных».

      4. Введите новый элемент.

      Результат:

      5. Чтобы удалить элемент из выпадающего списка, на шаге 2 нажмите Удалить, выберите «Сдвинуть ячейки вверх» и нажмите ОК.

      Динамический раскрывающийся список

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

      1. На первом листе выберите ячейку B1.

      2. На вкладке Данные в группе Работа с данными щелкните Проверка данных.

      Появится диалоговое окно «Проверка данных».

      3. В поле Разрешить щелкните Список.

      4. Щелкните в поле Источник и введите формулу: =СМЕЩ(Лист2!$A$1,0,0,СЧЁТ(Лист2!$A:$A),1)

      Пояснение: функция СМЕЩ принимает 5 аргументов. Ссылка: Sheet2!$A$1, строки для смещения: 0, столбцы для смещения: 0, высота: COUNTA(Sheet2!$A:$A) и ширина: 1. COUNTA(Sheet2!$A:$A) подсчитывает число значений в столбце A на Sheet2, которые не пусты. Когда вы добавляете элемент в список на Листе2, СЧЕТЧИК(Лист2!$A:$A) увеличивается. В результате диапазон, возвращаемый функцией OFFSET, расширяется, а выпадающий список обновляется.

      5. Нажмите OK.

      6. На втором листе просто добавьте новый элемент в конец списка.

      Результат:

      Удалить раскрывающийся список

      Чтобы удалить раскрывающийся список в Excel, выполните следующие действия.

      1. Выберите ячейку из раскрывающегося списка.

      2. На вкладке Данные в группе Работа с данными щелкните Проверка данных.

      Появится диалоговое окно «Проверка данных».

      3. Щелкните Очистить все.

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

      4. Нажмите OK.

      Зависимые раскрывающиеся списки

      Хотите узнать больше о раскрывающихся списках в Excel? Узнайте, как создавать зависимые раскрывающиеся списки.

      1. Например, если пользователь выбирает Пиццу из первого раскрывающегося списка.

      2. Второй раскрывающийся список содержит пункты Пицца.

      3. Но если пользователь выбирает китайский язык из первого выпадающего списка, второй выпадающий список содержит китайские блюда.

      Table Magic

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

      1. На втором листе выберите элемент списка.

      2. На вкладке Вставка в группе Таблицы щелкните Таблица.

      3. Excel автоматически выбирает данные за вас. Нажмите «ОК».

      4. Если выбрать список, Excel покажет структурированную ссылку.

      5. Используйте эту структурированную ссылку для создания динамического раскрывающегося списка.

    Добавить комментарий

    Ваш адрес email не будет опубликован.