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

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

Знакомство с 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. org/ListItem»>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   Советы: 

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

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

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

См.

также

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

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

   Примечания: 

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

   • org/ListItem»>

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

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

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

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

5. org/ListItem»>

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

   Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы

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

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

8. org/ListItem»>

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

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

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

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

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

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

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

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

См. также

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

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

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

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

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

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

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

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

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

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

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

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

13223 04. 10.2019 Скачать пример

Классический выпадающий список в ячейке листа Excel, сделанный через Данные — Проверка (Data — Validation) — простая и удобная штука, которую ежедневно применяют очень многие пользователи. Однако, у этого списка есть один весьма серьезный недостаток — в нём нет быстрого поиска по первым символам, т.е. фильтрации (отбора) только тех значений, куда введённый фрагмент входит как подстрока. Это серьезно ухудшает удобство пользования даже если в списке всего пара-тройка десятков позиций, а при нескольких сотнях убивает юзабилити напрочь.

Давайте рассмотрим как всё же реализовать подобный трюк. В качестве подопытного кролика возьмём список 250 лучших фильмов по версии IMDb:

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

Шаг 1. Определяем, кто нам нужен

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

Теперь завернем нашу формулу в функцию проверки ЕЧИСЛО (ISNUMBER), которая превратит числа в логическую ИСТИНУ (TRUE), а ошибки — в ЛОЖЬ (FALSE):

Теперь сделаем так, чтобы ЛОЖЬ превратилась в 0, а вместо ИСТИНА в столбце появились последовательно возрастающие индексы-числа 1,2,3… и т.д. Это можно сделать с помощью добавления к нашей же формуле ещё парочки функций:

Здесь функция ЕСЛИ (IF) проверяет что мы имеем (ИСТИНУ или ЛОЖЬ), и

• если была ИСТИНА, то выводит максимальное значение из всех вышестоящих чисел + 1
• если была ЛОЖЬ, то выводит 0

Шаг 2.

Отбираем в отдельный список

Дальше — проще. Теперь банальной функцией ВПР (VLOOKUP) просто выведём все найденные названия (я добавил столбец с порядковыми номерами для удобства):




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

Шаг 3. Создаем именованный диапазон




Теперь создадим именованный диапазон, который будет ссылаться на отобранные фильмы. Для этого выбрем на вкладке Формулы команды Диспетчер имен — Создать (Formulas — Name Manager — Create):

Имя диапазона может быть любым (например, Фильмы), а самое главное — это функция СМЕЩ (OFFSET), которая и делает всю работу. Напомню её синтаксис, если вы подзабыли:

=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; высота; ширина)




У нас:

• В качестве начальной ячейки задаём первую ячейку списка отобранных элементов (E2).
• Сдвиги вниз и вправо у нас отсутствуют, т.е. равны нулю.
• Высота диапазона у нас соответствует максимальному значению индекса из столбца А.
• Ширина диапазона — 1 столбец.

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

Шаг 4. Создаем выпадающий список




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





Чтобы Excel не ругался при вводе на неточное совпадение наших фраз с исходным списком, на вкладке Сообщение об ошибке (Error Alert) в этом окне нужно выключить флажок Выводить сообщение об ошибке (Show error alert):




Вот и всё. Можно жать на ОК и наслаждаться результатом:

Для пущего удобства при вводе с клавиатуры можно использовать Ctrl+Enter вместо Enter после ввода текста (так активная ячейка не уходит вниз) и сочетание клавиш Alt+стрелка вниз, чтобы развернуть выпадающий список без мыши.


P.S. 

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





Всё, что мы делали на Шагах 1-3 заменяется одной(!) формулой, где новая функция ФИЛЬТР (FILTER) отбирает из исходного диапазона A2:A251 только те фильмы, которые содержат заданную подстроку.

А дальше останется при создании выпадающего списка указать в качестве источника первую ячейку диапазона отобранных фильмов (C2) и добавить к ней знак #, чтобы получить ссылку на весь динамический массив:

И всё. Никаких именованных диапазонов и медленных СМЕЩ, никаких танцев с дополнительными столбцами и формулами. Песня!


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

• Что такое динамические массивы в Excel
• Разбор трех основных функций динамических массивов: СОРТ, ФИЛЬТР и УНИК
• 4 способа создать выпадающий список на листе Excel

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

22266 15.10.2020 Скачать пример

Необходимое предисловие

Если у вас Excel 2010 или древнее, то можно почитать вот эту статью, где было описано как реализовать такой выпадающий список с помощью функции СМЕЩ (OFFSET).

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

Шаг 1.

Готовим каталог изображений

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




При создании имеет смысл держать в голове несколько моментов:

• Если в будущем планируется добавление новых строк (товаров), то лучше сразу оформить такой каталог в виде «умной» динамической таблицы с помощью сочетания клавиш Ctrl+T или кнопки Форматировать как таблицу на вкладке Главная (Home — Format as Table). Имя созданной «умной» таблицы можно задать на вкладке Конструктор (Design) — я назвал её в этом примере Каталог.
• Картинки должны полностью вписываться в соответствующие ячейки и не выходить за их границы (иначе они будут обрезаны). Для упорядочивания изображений после их выделения удобно использовать команды выравнивания и распределения на вкладке Формат (Format). Там же можно задать размеры (высоту и ширину) изображений, чтобы быстро вписать их в ячейки:

• В отдельном столбце рядом с изображениями (в нашем примере он называется ИД) нужно придумать и ввести имена диапазонов, по которым мы впоследствии будем адресоваться к картинкам. Эти имена должны быть уникальными, не содержать пробелов и не быть похожими на адреса ячеек (A1, B3 и т.п.)

Шаг 2. Создаем именованные диапазоны для ячеек с картинками

Чтобы массово и быстро присвоить ячейкам с изображениями в столбце Картинка придуманные имена из столбца ИД используем следующий трюк:

1. Выделяем столбцы ИД и Картинка (диапазон C2:D7 в нашем примере)
2. Открываем вкладку Формулы (Formulas)
   
3. Выбираем команду Создать из выделенного (Create from selection).
4. Ставим флажок В столбце слева (Left column), остальные флажки выключаем и жмем ОК.



Проверить получившиеся результаты можно в Диспетчере имен там же на вкладке Формулы (Formulas — Name manager).


Шаг 3. Создаем выпадающий список товаров

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


Техника тут простая:

1. Выделяем ячейку для выпадающего списка.
2. Выбираем на вкладке Данные команду Проверка данных (Data — Data Validation).
3. Из выпадающего списка Тип (Allow) выбираем вариант Список (List) и в поле Источник (Source) вводим формулу:

=ДВССЫЛ(«Каталог[Название]»)

=INDIRECT(«Каталог[Название]»)

Здесь «Каталог[Название]» — это текстовая (в кавычках!) ссылка на содержимое столбца с названиями товаров в нашей «умной» таблице-каталоге, а функция ДВССЫЛ (INDIRECT) нужна, чтобы превратить эту текстовую ссылку в настоящую.


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

Шаг 4. Вытаскиваем имя диапазона для выбранного товара

Здесь же давайте получим имя диапазона из столбца ИД для выбранного в выпадающем списке товара. Проще всего это сделать классической функцией ВПР (VLOOKUP):




В английской версии это будет:

=VLOOKUP(C2;Каталог;3;FALSE)

Здесь:

• С2 — адрес ячейки с выпадающим списком, содержимой которой мы ищем
• Каталог — имя нашей «умной» таблицы
• 3 — порядковый номер столбца ИД в «умной» таблице
• ЛОЖЬ (или 0) — точный режим поиска

Шаг 5. Создаем именованный диапазон с ДВССЫЛ

Как вы уже, наверное, догадались, мы будем использовать содержимое ячейки E2 как ссылку на нужное нам изображение. Однако напрямую сделать это не получится, т.к. для Excel её содержимое формально является текстом. Чтобы оживить текстовую ссылку и превратить её в настоящую — используем уже знакомую нам функцию ДВССЫЛ (INDIRECT). Для этого:

1. Открываем Диспетчер имён на вкладке Формулы и жмём кнопку Создать (Formulas — Name Manager — Create).
2. Вводим любое подходящее имя для создаваемого диапазона, например Фото.
3. В поле Ссылка (Reference) вводим формулу: =ДВССЫЛ(Лист2!$E$2)
   




Шаг 6. Добавляем картинку и привязываем её к списку




Остался последний шаг:

1. Выделим в нашей «умной» таблице любую ячейку с картинкой (например D2).
2. На вкладке Главная развернём выпадающий список Копировать и выберем опцию Копировать как рисунок (Home — Copy — Copy as Picture). В появившемся затем окне можно выбрать вариант Как на экране (As on screen) и Растовый (Bitmap):

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

3. Вставим скопированное обычным образом (например через Ctrl+V) рядом с нашим выпадающим списком.
4. Для вставленной (и выделенной) картинки пропишем в строке формул ссылку на созданный ранее именованный диапазон (=Фото) и нажмём на Enter:

   
   

Вот, собственно, и всё 🙂 Можно наслаждаться результатом:




Примечания

• Ячейку С2, где мы с помощью ВПР получали имя нужного нам изображения можно, само-собой, скрыть.
• Чтобы не было видно уродливых рамок вокруг картинки — нужно отключить все границы и убрать заливку на листе с каталогом. Для «умных» таблиц это делается на вкладке Конструктор (Design). Также можно отключить на листе каталога и тонкие серые линии сетки через Вид — Сетка (View — Gridlines).
• Если в будущем вы будете дописывать новые товары и добавлять новые изображения в каталог — не забудьте присвоить новым ячейкам имена через команду Формулы — Создать из выделенного.
  

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

Ошибка 404 — Not Found

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

It looks like somebody took this page away and did not put it back.

• IT PC
  • Система компьютерной вёрстки LaTeX
    • Самоучитель LaTeX
    • Символы LaTeX
    • LaTeX → HTML
    • WinEdt
    • MikTex
    • Видеоуроки LaTeX
    • Титульный лист LaTeX
  • Операционные системы Линукс
    • Debian FAQ
    • Debian
    • Дневник — первый опыт с Debian
    • Memory stick в Debian
    • Configure Make Install
    • Настройка сети в Linux
    • Системная переменная PATH Linux
    • Virtualbox
    • Bash
      • Bash Scripting
    • Ubuntu
    • CentOS
  • DEVOPS
    • AWS
    • Git
    • Docker
    • Virtulabox
      • Уставновка виртуальной Windows 7 с помощью Virtulabox в Debian
  • Операционная система OpenBSD
  • Программы, за которые не нужно платить
  • Программирование
    • Язык программирования Си
    • Язык программирования Си++
    • Java Script
    • Язык программирования Python
      • Python: сложности, нюансы, детали.
      • PIP
    • Язык программирования Ruby
    • Язык программирования PHP
      • Как отобразить время различных часовых поясов PHP
      • Как вставить переменную в ссылку PHP
      • json_decode PHP
      • Как определить ширину экрана PHP
      • Premature end of chunk coded message body: closing chunk expected
  • Web
    • Язык разметки HTML
    • CMS Joomla
    • Браузер Mozilla Firefox
    • Переадресация внутри сайта
  • IT Helpdesk
    • Заметки о BAT файлах
    • Доступ по RDP через SSH туннель
    • Grep
    • Sed
    • Awk
    • Заметки о системном администрировании
    • Режим разработчика в Windows 10
    • Использование Bash в Windows 10
    • Запись установочного образа на флешку с помощью UltraISO
    • Firewall Windows
  • Microsoft Office
    • Microsoft Excel
      • Цветной выпадающий список
      • Всё пропало
    • Microsoft Word
  • Как создать репозиторий с помощью TortoiseSVN
  • Тестирование ПО
    • Jira
    • Учебник
    • Тестирвоание API
    • SOAP UI
    • Clumsy 0. 2
    • Postman
    • Тестирование с Python
    • Cherry Picking
• Образование
  • Физика
    • Физика 6 класс
    • Физика 7 класс
    • Физика 8 класс
    • Физика 9 класс
    • Физика ГИА
    • Физика 10 класс
    • Физика 11 класс
    • Физика ЕГЭ
    • Прикладная оптика
    • Микроэлектроника
    • Оптоэлектроника
    • Оптическая спектроскопия
    • Антенны
  • Математика
  • Ядерная физика
    • Заметки о ядерной физике
      • Ядерные уровни
      • Эффективное поперечное сечение
      • Деление ядер
      • Распределение энергии деления 235U тепловыми нейтронами между продуктами.
      • Классификация частиц
      • Видеоматериалы
    • Кафедра ядерной физики СПбГУ
      • Конспекты и прочее
      • Основные свойства атомных ядер
      • Ядерные силы
      • Электронные методы
      • Вторичное квантование
      • Теория групп
      • Резонансное рассеяние гамма-лучей
      • Ядерные реакции
      • Кварковая структура адронов
      • Основы дозиметрии
      • Экзотические ядра
      • Внутренняя конверсия
      • Слабые и электромагнитные процессы
      • Прямые ядерные реакции
      • Квантовая хромодинамика
      • Нейтронные резонансы и нейтронная оптика
      • Тяжёлые ионы
  • Атомная энергетика
    • Список принятых в ядерной энергетике сокращений
    • ВВЭР
  • Физический факультет СПбГУ
    • Численные методы
    • Болонский процесс
    • Жизнь студентов — покупательная способность стипендии
    • ПУНК глазами немцев
    • Впечатления Максима Николаевича
    • Максим Николаевич о Физ-факе
  • Теорвер
    • Задачи по теорверу
  • Английския язык
    • Вводные предложения в английском языке
  • Литература
    • Эрих Мария Ремарк. В каком порядке читать
    • Эрих Мария Ремарк. Фильмы
    • Эрих Мария Ремарк. Аудиокниги
    • Кормак МакКарти
    • Анджей Сапковский. Ведьмак
    • Бокононизм
• Физкультура, спорт, здоровье
  • Баскетбол
    • Баскетбольный клуб Спартак Санкт-Петербург
      • Видео
        • 2013 Осень
        • 2013 Весна
        • 2012 Осень
        • 2012 Весна
        • 2011 Осень
        • 2011 Весна
        • 2010 Весна
        • 2009 Осень
        • 2009 Весна
        • 2008 Осень
        • 2000 — 2005
        • 90-е
      • Bisons — Спартак (6 ноября 2013)
    • Физическая подготовка
    • Техника
  • Здоровый образ жизни
• Разное
  • Инновационный центр СПбГУ
  • Поликлиника в ПГТ им. Морозова
  • Навальный и Носик считают деньги
  • Карта сайта
  • RFID
    • RFID Основы
    • RFID тэги
    • RFID Компании
    • Online инструменты
    • RFID словарь
• Реклама
• Блог
  • Varis
• Другие проекты
  • HeiHei.ru
  • TopBicycle.ru
• Mузыка
  • Музыка
  • Maruv
  • Холстинин
  • Ежемесячные
  • Лук, лучок
  • Валентин Дядька
  • Бутер Бродский
• Поиск по сайту
• aofeed — Telegram канал чтобы следить за выходом новых статей
• aofeedchat — задать вопрос в Телеграм-группе

Контакты и сотрудничество:

Рекомендую наш хостинг beget. ru

Пишите на [email protected] если Вы:

1. Хотите написать статью для нашего сайта или перевести статью на свой родной язык.

2. Хотите разместить на сайте рекламу, подходящуюю по тематике.

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

4. Нашли на сайте ошибку, неточности, баг и т.д. … …….

5. Статьи можно расшарить в соцсетях, нажав на иконку сети:

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

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

20 сен в 2021

20K

Обсудить

Раскрывающийся список в 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 и обучение

Введение в 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 .

   Примечания:

   • org/ListItem»>

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

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

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

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

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

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

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

   .

6. org/ListItem»>

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

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

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

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

9. org/ListItem»>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

   Советы: 

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

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

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

См.

также

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

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

   Примечания:

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

   • org/ListItem»>

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

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

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

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

5. org/ListItem»>

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

   Фрукты, овощи, крупы, молочные продукты, закуски

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

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

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

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

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

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

10. Нажмите 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.

• org/ListItem»>

  Итоговая строка     После добавления итоговой строки в таблицу Excel предоставляет раскрывающийся список автосуммы для выбора из таких функций, как СУММ, СРЗНАЧ и т. д. Когда вы выберете один из этих параметров, таблица автоматически преобразует их в функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которая будет игнорировать строки, которые по умолчанию были скрыты фильтром. Если вы хотите включить в свои расчеты скрытые строки, вы можете изменить аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

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

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

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

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

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

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

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

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

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

4. org/ListItem»>

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

Также посмотрите видео о создании таблицы в Excel.

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

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

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

• org/ListItem»>

  Обеспечение целостности данных      Вы можете использовать встроенную функцию проверки данных в 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. org/ListItem»>

   В поле Источник введите значения списка, разделенные запятыми. Например, введите Низкий, Средний, Высокий .

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

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

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

Примечания:

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

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

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

Для этого:	Выполните следующие действия:
Ограничить ввод данных целыми числами в установленных пределах.	Выполните шаги 1-2 выше. org/ListItem»> Из списка Разрешить выберите Целое число . В поле Данные выберите нужный тип ограничения. Например, чтобы установить верхний и нижний пределы, выберите из . Введите минимальное, максимальное или конкретное допустимое значение. Вы также можете ввести формулу, которая возвращает числовое значение. Предположим, вы проверяете данные в ячейке F1. Чтобы установить минимальный предел вычетов, равный удвоенному количеству детей в этой ячейке, выберите больше или равно в поле Данные и введите формулу =2*F1 в поле Минимум .
Ограничить ввод данных десятичным числом в определенных пределах.	Выполните шаги 1-2 выше. В поле Разрешить выберите Decimal . В поле Данные выберите нужный тип ограничения. Например, чтобы установить верхний и нижний пределы, выберите из . Введите минимальное, максимальное или конкретное допустимое значение. Вы также можете ввести формулу, которая возвращает числовое значение. Например, чтобы установить максимальный лимит комиссионных и бонусов в размере 6% от зарплаты продавца в ячейке E1, выберите меньше или равно в поле Данные и введите формулу =E1*6% в поле Максимум . Примечание. Чтобы разрешить пользователю вводить проценты, например 20 %, выберите Десятичный в поле Разрешить , выберите тип ограничения в поле Данные , введите минимум, максимум или конкретное значение в виде десятичного числа, например . 2 , а затем отобразите ячейку проверки данных в процентах, выбрав ячейку и нажав Процентный стиль в группе Номер на вкладке Домашний .
Ограничить ввод данных датой в диапазоне дат.	Выполните шаги 1-2 выше. В поле Разрешить выберите Дата . org/ListItem»> В поле Данные выберите нужный тип ограничения. Например, чтобы разрешить даты после определенного дня, выберите больше 9.0059 . Введите начальную, конечную или конкретную дату для разрешения. Вы также можете ввести формулу, которая возвращает дату. Например, чтобы установить временной интервал между сегодняшней датой и 3 днями от сегодняшней даты, выберите между в поле Данные , введите =СЕГОДНЯ() в поле Дата начала и введите =СЕГОДНЯ()+3 в поле Дата окончания .
Ограничить ввод данных временем в рамках временного интервала.	Выполните шаги 1-2 выше. В поле Разрешить выберите Время . В поле Данные выберите нужный тип ограничения. Например, чтобы разрешить время до определенного времени суток, выберите меньше . org/ListItem»> Введите начало, конец или конкретное время для разрешения. Если вы хотите ввести конкретное время, используйте формат времени чч:мм. Например, предположим, что у вас есть ячейка E2 со временем начала (8:00) и ячейка F2 со временем окончания (17:00), и вы хотите ограничить время встречи между этими временами, затем выберите между в поле Data введите =E2 в поле Время начала , а затем введите =F2 в поле Время окончания .
Ограничить ввод данных текстом указанной длины.	org/ListItem»> Выполните шаги 1-2 выше. В поле Разрешить выберите Длина текста . В поле Данные выберите нужный тип ограничения. Например, чтобы разрешить ввод до определенного количества символов, выберите меньше или равно . В этом случае мы хотим ограничить ввод 25 символами, поэтому выберите меньше или равно в поле Данные и введите 25 в поле Максимум .
Вычислить разрешенное на основе содержимого другой ячейки.	Выполните шаги 1-2 выше. В поле Разрешить выберите нужный тип данных. В поле Данные выберите нужный тип ограничения. В поле или полях под полем Данные щелкните ячейку, которую вы хотите использовать, чтобы указать, что разрешено. Например, чтобы разрешить ввод для учетной записи только в том случае, если результат не будет превышать бюджет в ячейке 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. На вкладке Данные в группе Сортировка и фильтрация выполните одно из следующих действий:

   • org/ListItem»>

     Для быстрой сортировки по возрастанию нажмите ( Сортировка от А до Я ).

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

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

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

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

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

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

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

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

Примечания:

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

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

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

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

   • org/ListItem»>

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

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

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

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

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

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

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

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

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

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

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

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

   • org/ListItem»>

     Для сортировки по формату выберите Цвет ячейки , Цвет шрифта или Значок ячейки .

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

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

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

   • org/ListItem»>

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

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

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

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

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

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

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

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

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

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

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

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

4. org/ListItem»>

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

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

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

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

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

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

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

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

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

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

org/ItemList»>

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

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

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

   3. org/ListItem»>

      Перейдите к File > Options > Advanced > General > Edit Custom Lists , затем в диалоговом окне Custom Lists дважды нажмите Import , а затем нажмите OK .

      Примечания:

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

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

2. org/ListItem»>

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

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

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

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

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

7. Нажмите OK .

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

2. org/ListItem»>

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

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

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

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

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

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

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

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

4. org/ListItem»>

   В диалоговом окне Параметры сортировки в разделе Ориентация щелкните Сортировать слева направо , а затем щелкните OK .

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

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

6. org/ListItem»>

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3. org/ListItem»>

   Чтобы отменить сортировку, используйте кнопку Undo на Home таб.

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

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

   • org/ListItem»>

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

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

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

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

    

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• org/ListItem»>

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

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

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

3. org/ListItem»>

   В меню Параметры выберите С учетом регистра .

4. Нажмите  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 могут значительно облегчить ввод данных. Вот посмотрите, как использовать функцию проверки данных Microsoft Excel для создания удобных списков на ваших листах.

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

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

SEE: команды Windows, Linux и Mac, которые должен знать каждый (бесплатный PDF) (TechRepublic)

Я использую рабочий стол Microsoft 365 в 64-разрядной системе Windows 10, но вы можете использовать более раннюю версию. Для вашего удобства вы можете скачать демонстрационные файлы . xlsx и .xls.

Однако объект «Таблица» недоступен в более старых версиях меню (.xls), поэтому некоторые положения этой статьи неприменимы, если вы все еще используете версию .xls. Вы по-прежнему можете использовать диапазон данных для списка, но заполняемый раскрывающийся список не будет обновляться при обновлении элементов списка. Microsoft Excel для Интернета поддерживает проверку данных, и вы даже можете добавить раскрывающийся список проверки данных в веб-версии. Вы не можете развернуть таблицу Excel, перемещаясь по ячейке, чтобы вставить новую строку, но вы все равно можете добавить строку.

Что такое раскрывающийся список в Excel?

На рисунке A показан простой раскрывающийся список на листе Excel. Чтобы использовать раскрывающийся список, щелкните ячейку ввода данных (в данном случае D2), а затем щелкните стрелку раскрывающегося списка, чтобы отобразить список значений в B3:B6. Если пользователь пытается ввести что-то, что не является элементом этого списка значений, Excel отклоняет ввод, защищая достоверность ваших данных.

Рисунок А

Изображение: Сьюзан Харкинс/TechRepublic. Используйте проверку данных, чтобы ограничить выбор при вводе данных.

Чтобы создать раскрывающийся список проверки данных в Excel, вам потребуются две вещи: список значений и пустая ячейка для использования в качестве ячейки ввода данных. Список находится в B3:B6, а ячейка ввода данных — D2.

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

Теперь, когда вы знаете, какую пользу пользователи и ваши данные получат от раскрывающегося списка, давайте добавим его в D2. Сначала добавьте краткий список ( Рисунок A ) в B3:B6 и отформатируйте его как объект таблицы Excel. Просто щелкните любую ячейку в списке и нажмите Ctrl + T. В появившемся диалоговом окне убедитесь, что выбран параметр «Моя таблица имеет заголовки», и нажмите «ОК». Технически элементы списка не нужно форматировать как таблицу, но таблица делает раскрывающийся список динамическим.

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

1. Выберите D2, чтобы следовать примеру, но вы можете разместить раскрывающийся список где угодно.
2. Перейдите на вкладку «Данные», а затем нажмите «Проверка данных» в группе «Инструменты данных». Перейдите на вкладку «Настройки», если необходимо.
3. В появившемся диалоговом окне «Проверка данных» нажмите «Список» в раскрывающемся списке «Разрешить».
4. Щелкните элемент управления исходным кодом и выделите B3:B6 — элементы списка.
5. Нажмите кнопку ОК.

Рисунок В

Изображение: Сьюзан Харкинс/TechRepublic. Определите элементы списка, которые будут заполнять раскрывающийся список.

Щелкните раскрывающийся список, чтобы просмотреть список, показанный ранее ( Рисунок A) .

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

SEE: Почему Microsoft Lists — это новый Excel (TechRepublic)

Ранее вы форматировали элементы списка как объект таблицы. Теперь давайте выясним, почему, введя новый элемент списка в таблицу:

1. Выберите B6 и нажмите Tab, чтобы добавить новую ячейку в таблицу.
2. Введите «коза» и нажмите Enter.

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

Рисунок С

Изображение: Сьюзан Харкинс/TechRepublic. Отформатируйте список как таблицу, чтобы раскрывающийся список обновлялся автоматически.

Как использовать раскрывающийся список в таблице Excel

Обязательная к прочтению информация о Windows

• Виндовс 11 22х3 уже здесь
• Контрольный список: защита систем Windows 10
• Защитите свою сеть с помощью внешних служб безопасности Майкрософт
• Как очистить кеш DNS в Windows 10

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

.
1. Создайте быструю таблицу, нажав F2:G4. Затем нажмите Ctrl + T, выберите параметр «Моя таблица имеет заголовки» и нажмите «ОК».
2. Выберите D4, ячейку с раскрывающимся списком, который мы создали ранее, и нажмите Ctrl + C.
3. Выберите G3, первую ячейку в этом столбце, и нажмите Ctrl + v, чтобы скопировать раскрывающийся список в эту ячейку.

Раскрывающийся список доступен в первой ячейке столбца G ( рис. D ).

Выберите элемент из раскрывающегося списка и нажмите клавишу Tab, чтобы вставить новую запись в таблицу. Перейдите к G4, и вы увидите, что таблица автоматически копирует раскрывающийся список ( Рисунок E ). Если вы используете Excel для Интернета, щелкните правой кнопкой мыши таблицу, выберите «Вставить» в появившемся подменю, а затем выберите «Строки таблицы выше». Веб-версия скопирует раскрывающийся список, как и настольная версия.

Рисунок D

Изображение: Сьюзан Харкинс/TechRepublic. Скопируйте раскрывающийся список из G2 в D3.

Рисунок Е

Изображение: Сьюзан Харкинс/TechRepublic. Таблица копирует раскрывающийся список в каждую новую запись.

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

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

Использование нескольких ссылок на книги Microsoft Excel

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

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

Оставайтесь с нами

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

Сьюзен Харкинс

Опубликовано: 6 мая 2022 г., 11:31 по тихоокеанскому времени Изменено: 28 сентября 2022 г., 12:22 по тихоокеанскому времени Увидеть больше Программное обеспечение

См.

также
• Шпаргалка по Windows 11: все, что вам нужно знать (ТехРеспублика)
• Windows PowerToys: шпаргалка (ТехРеспублика)
• Google Workspace и Microsoft 365: параллельный анализ с контрольным списком (ТехРеспублика Премиум)
• Лучшее программное обеспечение для бизнеса и конечных пользователей (TechRepublic на Flipboard)
Поделиться: Как создать выпадающий список в Excel
• Майкрософт
• Программного обеспечения

Выбор редактора

• Изображение: Rawpixel/Adobe Stock ТехРеспублика Премиум

  Редакционный календарь TechRepublic Premium: ИТ-политики, контрольные списки, наборы инструментов и исследования для загрузки

  Контент TechRepublic Premium поможет вам решить самые сложные проблемы с ИТ и дать толчок вашей карьере или новому проекту.

  Персонал TechRepublic

  Опубликовано: 3 октября 2022 г., 9:30 по тихоокеанскому времени Изменено: 3 октября 2022 г., 12:25 по тихоокеанскому времени Читать далее Узнать больше
• Изображение: diy13/Adobe Stock Программного обеспечения

  Виндовс 11 22х3 уже здесь

  Windows 11 получает ежегодное обновление 20 сентября, а также ежемесячные дополнительные функции. На предприятиях ИТ-отдел может выбирать, когда их развертывать.

  Мэри Бранскомб

  Опубликовано: 20 сентября 2022 г., 10:00 по тихоокеанскому времени Изменено: 20 сентября 2022 г. , 10:17 по тихоокеанскому времени Читать далее Увидеть больше Программное обеспечение
• Изображение: Кто такой Дэнни/Adobe Stock Край

  ИИ на переднем крае: 5 трендов, за которыми стоит следить

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

  Меган Краус

  Опубликовано: 19 сентября 2022 г., 11:46 по тихоокеанскому времени Изменено: 19 сентября 2022 г., 11:46 по тихоокеанскому времени Читать далее Увидеть больше
• Изображение: яблоко Программного обеспечения

  Шпаргалка по iPadOS: все, что вы должны знать

  Это полное руководство по iPadOS от Apple. Узнайте больше об iPadOS 16, поддерживаемых устройствах, датах выпуска и основных функциях с помощью нашей памятки.

  Персонал TechRepublic

  Опубликовано: 30 августа 2022 г., 7:00 по тихоокеанскому времени Изменено: 14 сентября 2022 г., 11:07 по тихоокеанскому времени Читать далее Увидеть больше Программное обеспечение
• Изображение: Worawut/Adobe Stock
• Изображение: Bumblee_Dee, iStock/Getty Images Программного обеспечения

  108 советов по Excel, которые должен усвоить каждый пользователь

  Независимо от того, являетесь ли вы новичком в Microsoft Excel или опытным пользователем, эти пошаговые руководства принесут вам пользу.