Auditportal.ru

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

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

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

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

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

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

При нажатии на иконку откроется новое окно для проверки вводимых значений. Здесь необходимо выбрать List в выпадающем списке Data type, а что вводить в появившемся поле Source мы сейчас разберемся.

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

Списки могут составляться тремя способами.

Указание элементов напрямую в источнике

Этот метод очень прост и подходит для небольших списков.

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

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

После нажатия на кнопку OK у нас появится раскрывающийся список.

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

Эту ячейку можно безопасно использовать во всей таблице. Копирует и вставляет ее в нужное место.

Элементы списка на том же листе

Этот способ позволяет использовать уже готовый список отделов на листе.

  • Нажмите в ячейке;
  • Введите управляющие данные;
  • Нажмите в поле Источник и с помощью мыши выберите нужную область в списке. Этот ряд должен быть на одном листе!

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

Просто скопируйте эту ячейку и вставьте ее в каждый ряд.

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

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

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

  • Создаем перечень отделов на другом листе;
  • Создаем Именованный диапазон. Выбираем диапазон с элементами списка. Слева от строки формул сейчас указана ячейка, с который вы начинали выделение. В моем случае — А2;
  • Вместо А2 даем Имя нашему диапазону. Например, называем его Отделы. После этого нажимаем клавишу Enter, Поздравляю, мы создали Именованный диапазон.
Читайте так же:
Как извлечь изображения из комментариев в Excel?

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

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

Возвращаемся обратно на исходный лист. Становимся на ячейку, где будем создавать список. Заходим в «Данные -> Проверить данные». В поле Источник, через знак = вводим название созданного на предыдущем этапе диапазона Отделы.

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

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

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

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

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

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

Рисунок 1: Состав выпадающего списка зависит от содержимого соседней ячейки

Заметку можно скачать в формате Word или pdf, с примерами в формате Excel2007

Команда Проверка данных находится на вкладке Данные в области управления данными.

Примечание. Команда проверки данных может быть недоступна:

  • Возможно, в это время происходит ввод данных. Команда проверки данных недоступна, когда данные вводятся в ячейку. Чтобы завершить ввод данных, нажмите клавишу Enter или ESC.
  • Лист можно защитить или сделать общим. Если лист защищен или находится в общем доступе, вы не сможете изменить параметры проверки данных. Снять защиту или снять защиту с общего режима.
  • Рабочий лист Excel можно связать с узлом SharePoint. Нельзя добавить проверку данных на рабочем листе Excel, который связан с узлом SharePoint. Чтобы добавить проверку данных, необходимо удалить ссылку из таблицы Excel или преобразовать ее в диапазон.

К сожалению, Excel, в своем стандарте, позволяет вести списки только на единичной основе:

  • Название таблицы
  • Диапазон ячеек
  • Прямое перечисление элементов списка (рис. 2).
Читайте так же:
Как исправить / преобразовать конечные отрицательные знаки в действительные числа в Excel?

Примечание: Введите элементы списка, используя стандартный разделитель элементов списка Microsoft Windows (в русском Excel разделителем по умолчанию является точка с запятой).

Рисунок 2: Возможные источники списка: вверху — название матрицы; посередине — диапазон ячеек; внизу — элементы списка

Попытка ввести формулу в поле Source диалогового окна Check Entries не удалась (Рисунок 3). Excel не использовал значение ячейки D2 (» color » ) в качестве имени массива и включил это значение в качестве единственного элемента в списке.

Рисунок 3. Неверный источник списка — формула

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

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

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

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

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

И все же в Excel есть одна косвенная функция. Название у нее говорящее: ИНДИРЕКТ. На русском языке она называется ДИВСИЛ. В чем заключается косвенное действие? В отличие от других функций Excel, функция ИНДИРЕКТ возвращает не значение, хранящееся в ячейке, а ссылку, хранящуюся в ячейке. Не понятно? Мне и самому было нелегко продираться сквозь это. Попробую объяснить. В справке Excel мы читаем: ДВССЫЛ — возвращает ссылку, заданную текстовой строкой. Мы отображаем содержимое ссылок сразу после их оценки (Рисунок 4).

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

Рисунок 4: Принцип работы функции DWSSYL: вверху — формулы, внизу — значения

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

Итак, еще раз, ДВССЫЛ возвращает ссылку, а не значение, хранящееся в ячейке. Ссылка немедленно вычисляется, и выводится ее значение (или содержимое). Именно это свойство позволит нам ввести непрямую ссылку на соседнюю ячейку так, что вернется не значение, хранящееся в этой соседней ячейке (как на рис. 3), а ссылка, хранящаяся в ячейке, эта ссылка тут же вычисляется, и получается имя массива (рис. 5).

Рисунок 5: Создание списка на основе значения левой ячейки

Примечание. Ссылка в формуле =DWSSYL(D2) должна быть относительной (D2), а не абсолютной ($D$2). Более подробную информацию об этом типе ссылки на ячейку можно найти в заметках Excel о формулах проверки данных. Валидация данных.

Linked (Dependent) Dropdowns — это статья, которая может помочь вам, если эта статья не приведет к решению вашей проблемы (обратите внимание также на многочисленные комментарии к ней).

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

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

Делаем пошагово

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

Реализация проста:

  • Сначала строится сам список данных.
  • Выберите поля, которые вы хотите заполнить данными.
  • Затем перейдите на вкладку Данные, команда Проверить данные — Тип данных (Список), в поле Источник поместите диапазон.
Читайте так же:
Как легко и быстро переместить первое слово в конец в Excel?

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

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

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

  • Установите флажок «Игнорировать пустые клетки».

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

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

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

Нюансы

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

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

Второй способ

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

Для этого перейдите на вкладку Разработчик (Excel 2007-2010). По умолчанию эта вкладка не отображается. Чтобы открыть ее, необходимо зайти в Параметры (Office / Assel / Preferences / Main / Show Developer Tab on Ribbon).

голоса
Рейтинг статьи
Читайте так же:
Как изменить цвет шрифта в зависимости от значения ячейки в Excel?
Ссылка на основную публикацию
Adblock
detector