Ref-sgain.ru

Программы и сервисы
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как сделать выпадающий список, позволяющий вводить произвольный текст (ввод вручную) в Excel?

Динамические выпадающие списки Excel

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

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

  • В росте;
  • Зависимый.

Растущий выпадающий список

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

Давайте рассмотрим пример создания такого списка.

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

=МЕМБЕРШИП($A$1;;СКРИН($A:$A))), где:

  • $A$1 — ячейка со значением первого элемента в списке
  • $A:$A — столбец со списком всех элементов списка.

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

Параметры динамического выпадающего списка :

Параметры ростущего выпадающего списка

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

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

В качестве примера рассмотрим создание зависимых выпадающих списков со списком товаров. Сначала определите категории доступных продуктов (это будет основной список):

  • Бытовая техника (пробел в названии намеренно заменен знаком подчеркивания, так как менеджер имен, который будет использоваться далее, не принимает символ пробела);
  • Электроника;
  • Мебель.

Теперь для каждого значения категории создается список, которому в Менеджере имен (вкладка «Формулы» -> поле «Определенные имена») присваивается имя в соответствии с этим значением.

Присвоенные имена для связанных выпадающих списков excel

В этом шаге мы устанавливаем связанные выпадающие списки, которые мы создали для ячеек с помощью проверки данных (вкладка «Данные» -> область «Работа с данным»).

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

=DWSSSIL($A$1), где :

  • A$1 — это ячейка, содержащая главный список.

Параметры зависимых списков в excel

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

Лист excel с перечнем для списков и ячейки с настроенными выпадающими связанными списками

  • < Назад
  • Вперёд >
  • Проверьте данные в Excel.
  • Критерий Манна-Уитни
  • Подключение MySQL в Excel
  • Подключение Excel к SQL Server

Если вы нашли материал на сайте office-menu.ru полезным, пожалуйста, поддержите проект для его дальнейшего развития.

Комментарии

Добрый день, Александр!

Это можно сделать с помощью макросов. Я не знаю другого способа.

Читайте так же:
Как сделать, чтобы в группе флажков в Excel был выбран только один флажок?

Как вставить флажок в Excel (для создания интерактивных списков и диаграмм)

В Excel флажок — это интерактивный инструмент, который можно использовать для выбора или отмены выбора параметра. Пользователи Интернета, вероятно, видели это на ряде веб-форм.

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

Данное руководство по Excel охватывает следующие темы:

  • Как получить вкладку «разработчик» на ленте Excel.
  • Как определить флажок в Excel.
  • Примеры использования флажков в Excel.
  • Как вставить несколько флажков в Excel.
  • Как удалить флажок в Excel.
  • Как исправить положение флажка в Excel.
  • Название в сравнении с фоновым названием.

Чтобы установить этот флажок в Excel, необходимо сначала активировать вкладку «Разработчик» рабочей книги.

Не видите ли вы вкладку «Разработчик»?

Не волнуйтесь и читайте дальше.

Получите вкладку разработчика на ленте Excel

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

В этом разделе описано, где находится вкладка разработчика на ленте Excel.

  • Щелкните правой кнопкой мыши любую из существующих вкладок на ленте Excel и выберите «Настроить ленту». Он открывает диалоговое окно "Параметры Excel".
  • В диалоговом окне «Параметры Excel» у вас будут параметры «Настроить ленту». Справа на панели «Основные вкладки» установите флажок «Разработчик».
  • Щелкните ОК. В результате вкладка разработчика появится как одна из вкладок на ленте.

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

Как установить флажок в Excel

Ниже описаны шаги по проверке этого поля в Excel:

  1. Перейдите на вкладку разработчика -> Элементы управления -> Вставить -> Элементы управления формы -> Флажок.
  2. Щелкните в любом месте листа, и появится флажок (как показано ниже).
  3. Теперь нужно привязать флажок к ячейке в Excel. Для этого щелкните правой кнопкой мыши флажок и выберите «Управление форматом».
  4. В диалоговом окне «Управление форматом» на вкладке «Управление» внесите следующие изменения:
    • Значение: установлен (это гарантирует, что флажок установлен по умолчанию при открытии книги)
    • Ссылка на ячейку: $ A $ 1 (это ячейка, на которую установлен флажок). Вы можете ввести это вручную или выбрать ячейку, чтобы получить ссылку.
  5. Щелкните ОК.

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

Примеры использования флажка в Excel

Приведем несколько примеров использования флажка в Excel.

Читайте так же:
Как создавать новые листы для каждой строки в Excel?

Создание интерактивного списка дел в Excel

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

В приведенном примере происходит несколько вещей:

  • Как только флажок для элемента/задачи установлен, статус меняется на DONE (с Done), ячейка становится зеленой, а текст перечеркивается.
  • Значение ссылки на ячейку для этого флажка изменяется с FALSE на TRUE.
  • Числа «Задача выполнена» и «% выполнения задачи» (в ячейках H3 и H4) изменяются в зависимости от количества задач, отмеченных как выполненные.

Как ты это делаешь?

  • Укажите действия, перечисленные в ячейке A2: A7.
  • Установите флажки и поместите их в ячейку B2: B7.
  • Свяжите эти флажки с ячейкой E2: E7. Невозможно связать все флажки сразу. Вам придется вручную связывать каждый флажок один за другим.
  • В ячейке C2 введите следующую формулу: = IF (E2, «Done», «To Be Done») и перетащите для всех ячеек (C2: C7).
    • В ячейке C2: C7 примените условное форматирование, чтобы придать ячейке зеленый цвет фона и зачеркнутое форматирование, когда значение ячейки равно Done.
    • Подсчитывается общее количество задач, отмеченных как выполненные.
    • Здесь отображается процент выполненных заданий.

    Контрольный список можно скачать по ссылке.

    Создание динамической диаграммы в Excel

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

    В данном случае флажок над диаграммой связан с ячейками С7 и С8.

    Если вы установите флажок для 2013 года, значение в ячейке C7 станет TRUE. Аналогично, если вы установите флажок для 2014 года, значение в ячейке C8 станет TRUE.

    Данные, использованные для составления этой таблицы, можно найти в кодах C11-F13. Данные за 2013 и 2014 годы зависят от соответствующей ячейки (C7 и C8). Если значение в ячейке C7 равно TRUE, отображаются значения в C11: F11, в противном случае отображается ошибка # N/A. То же самое относится и к данным за 2014 год.

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

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

    Вставка нескольких флажков в Excel

    Существует несколько способов вставить несколько флажков в один рабочий лист.

    # 1 Установка флажка с помощью вкладки разработчика

    Для нескольких флажков выберите вкладку Разработчик -> Элементы управления -> Вставка -> Элементы управления формой -> Флажок.

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

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

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

    # 2 Копирование Вставка флажка

    Выберите существующий флажок, скопируйте и вставьте его. Можно также использовать комбинацию клавиш (Control + D).

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

    # 3 Перетащите и заполните ячейки флажком

    В Excel, если перетащить весь дескриптор заполнения вниз, он создает копии флажка. Ниже приведен пример:

    • Имена заголовков всех новых флажков одинаковы. Но имя бэкенда будет другим (потому что это отдельные объекты).
    • Все эти флажки будут привязаны к одной ячейке (если первый флажок был привязан к той же ячейке). Вы должны вручную изменить ссылку на все из них по очереди.

    Удаление флажка в Excel

    Вы можете легко удалить один флажок, выделив его и нажав клавишу delete. Чтобы установить флажок, удерживайте нажатой клавишу Control и нажмите левую кнопку мыши.

    Если вы хотите удалить несколько ячеек :

    • Удерживая нажатой клавишу Control, выберите все те, которые вы хотите удалить.
    • Нажмите клавишу Delete.

    Этот список позволит вам удалить все флажки сразу, если ваш лист включает много флажков:

    • Перейдите на главную -> Редактирование -> Найти и выбрать -> Панель выбора.
      • Это откроет панель выбора, в которой будут перечислены все объекты на этом листе (включая флажки, фигуры и диаграммы).
      • Обратите внимание, что имена флажков здесь являются именами серверных программ, а не названиями заголовков.

      Примечание: В области выбора отображаются только все объекты в активном листе.

      Как исправить положение флажка в Excel

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

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

      • Щелкните левой кнопкой мыши по флажку и выберите «Управление форматом».
      • В диалоговом окне «Управление форматом» выберите вкладку «Свойства».
      • На вкладке свойств в разделе «Положение объекта» выберите «Не перемещать и не изменять размер вместе с ячейками».
      • Щелкните ОК.

      Флажки теперь остаются на месте даже при изменении размера или удалении ячеек.

      Название заголовка Vs. Имя

      Когда вы устанавливаете флажок в Excel, вы видите название перед полем (например, «флажок 1» или «флажок 2»).

      Этот текст — перед полем — является заголовком для флажка. Чтобы отредактировать этот текст, щелкните правой кнопкой мыши и выберите опцию «Редактировать текст».

      Серверная сторона будет продолжать ссылаться на этот флажок 1 до тех пор, пока будет отображаться новый текст.

      Если вы установите флажок и посмотрите на поле «Имя», вы увидите имя, которое Excel использует для данного поля на стороне сервера.

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

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

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

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

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

      Для этого введите данные, которые вы хотите видеть в списке, в ячейки A1:A7. Теперь выберите ячейку, в которой вы хотите создать выпадающий список — B2.

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

      » Вкладка «Данные» и нажмите кнопку «Проверить данные».

      На вкладке «Параметры» выберите «Список» в поле «Тип данных». Значения в поле «Источник» могут быть введены различными способами:

      1 — введите значения для списка вручную, разделяя их точкой с запятой;

      2 — укажите диапазон ячеек, в которые вводятся данные для выпадающего списка;

      3 — Выберите ячейки с именами, щелкните по ним правой кнопкой мыши и выберите в меню пункт «Присвоить имя».

      Затем введите ‘имя’ выделенных данных.

      Выберите ячейку B2 и в поле «Источник» поставьте «=» , затем напишите созданное имя.

      Поэтому мы создали раскрывающийся список Excel.

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

      Добавляем значения в выпадающий список – динамический список

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

      Затем нажмите «Главная» и выберите «Форматировать как таблицу» для диапазона ячеек D1:D8.

      Подтвердите местоположение данных и установите флажок «Таблица заголовков».

      Наша таблица имеет верхний заголовок Employees (Сотрудники) и заполняется данными.

      Выделяем ячейку, в которой будет выпадающий список и кликаем по кнопочке «Проверка данных» . В следующем окне, в поле «Источник» , пишем следующее: =ДВССЫЛ(«Таблица1[Сотрудники]») . У меня одна таблица на листе, поэтому пишу «Таблица1» , если будет вторая – «Таблица2» , и так далее.

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

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

      Когда выпадающие списки находятся на одном листе, а данные для них — на другом, эта функция пригодится.

      Выберите ячейку или диапазон ячеек на Листе 2, а затем нажмите кнопку «Проверить данные».

      Перейдите на Лист 1, установите курсор в поле «Источник» и выделите необходимый диапазон ячеек.

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

      Создаем зависимые выпадающие списки

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

      Первый называется «Имя», второй — «Фамилия». Второй — «Фамилия», третий — «Отчет». Третий — «Отец».

      Давайте сделаем еще одну область, в которой будут прописаны присвоенные имена. Назовем его «Сотрудники».

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

      Введите «Список» в поле «Тип данных», затем в поле источника введите «=Employees» или выберите диапазон ячеек, которым должно быть присвоено имя.

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

      После выделения ячейки нажмите «Проверка данных». Выберите «Список» в качестве типа данных, а затем напишите =SOURCE($E$1) в поле источника. Первый выпадающий список можно найти в ячейке E1.

      По этому принципу можно создавать зависимые выпадающие списки.

      Если затем нужно ввести значения в поле, которое имеет имя, например «Имя» . Перейдите на вкладку «Формулы» и нажмите «Обработка имен» . Теперь выберите «Фамилия» в названии поля. и внизу, вместо последней ячейки C3, введите C10 . Нажмите на выбранный элемент. Область расширится, и вы сможете добавить в нее данные, которые автоматически появятся в выпадающем списке.

      голоса
      Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector