Eurotehnik.ru

Бытовая Техника "Евротехник"
3 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Трюк №16. Проверка данных на основе списка на другом листе Excel

Трюк №16. Проверка данных на основе списка на другом листе Excel

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

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

Способ 1. Именованные диапазоны

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

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

Способ 2. Функция ДВССЫЛ

Функция ДВССЫЛ (INDIRECT) позволяет ссылаться на ячейку, содержащую текст, представляющий адрес ячейки. Эту ячейку можно использовать как локальную ссылку, даже если она получает данные из другого листа. Можно применять эту возможность для связи с листом, где расположен список.

Предположим, список находится на листе Sheetl в диапазоне $А$1:$А$8 . Щелкните любую ячейку на другом листе, где должен появиться этот список проверки (список выборки). Затем выберите команду Данные → Проверка (Data → Validation) и в поле Тип данных (Allow) выберите пункт Список (List). В поле Источник (Source) введите следующий код: =INDIRECT(«Sheetl!$А$1:$А$8») , в русской версии Excel =ДВССЫЛ(«Sheetl!$A$1:$A$8») . Удостоверьтесь, что флажок Список допустимых значений (In-Cell) установлен, и щелкните на кнопке ОК. Список на листе Sheetl должен появиться в раскрывающемся списке проверки.

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

Если имя листа, на котором расположен список, содержит пробелы, необходимо использовать следующий синтаксис функции ДВССЫЛ (INDIRECT): =INDIRECT(«‘Sheetl’!$А$1:$А$8») , в русской версии Excel =ДВССЫЛ(«‘Sheetl’!$А$1:$А$8») . Различие заключается в том, что здесь после первой кавычки стоит один апостроф, а второй апостроф находится перед восклицательным знаком.

Преимущества и недостатки обоих способов

У именованных диапазонов и функции ДВССЫЛ (INDIRECT) есть преимущества и недостатки. Преимущество использования именованного диапазона заключается в том, что изменение названия листа не повлияет на список проверки. Это подчеркивает недостаток функции ДВССЫЛ (INDIRECT): любое изменение названия листа не будет автоматически в ней отражаться. Преимущество функции ДВССЫЛ (INDIRECT): когда из именованного диапазона будет удалена первая ячейка или строка либо последняя ячейка или строка, то именованный диапазон вернет ошибку #REF! . В этом недостаток именованного диапазона — если удалить из него ячейки или строки, изменения не повлияют на список проверки.

Введите данные

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

  1. Открыть лист1 и тип Тип файла cookie: в клетку D1, Вы собираетесь создать раскрывающийся список в ячейке E1 на этом листе, рядом с этой записью.
  2. открыто Лист2.
  3. Тип Имбирный пряник в клетку A1.
  4. Тип Лимон в клетку A2.
  5. Тип Овсяная изюминка в клетку A3.
  6. Тип Шоколадные чипсы в клетку A4.

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

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

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

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

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

Список и таблица.

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

Добавлено значение елка.

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

Удалено значение береза.

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

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

  1. Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК. Создание имени.
  2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
  3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения. Сообщение об ошибке.
  4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
  5. Сохраняем, установив тип файла «с поддержкой макросов». Сообщение об ошибке.
  6. Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

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

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

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

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

На Листе 2, выделяем одну ячейку или диапазон ячеек, затем щёлкаем по кнопочке «Проверка данных».

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

Читайте так же:
Как включить стим браузер

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

Составление списка

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

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

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

Как создать список в Excel

Лучше сделать выпадающий список в экселе с форматированием

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

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

Создать раскрывающийся список в Excel 2010 можно любого стиля

Откроется окошко под названием Форматирование таблицы. В этом окошке нужно поставить галочку у пункта Таблица с заголовками и после этого нажимаете кнопку ОК.

Создать выпадающий список в Excel с условием

Лучше всего в Excel 2010 сделать выпадающий список форматированным

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

Читайте так же:
Можно ли давать фотографировать птс

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

Прежде чем сделать выпадающие списки в Excel 2010 необходимо создать эти списки

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

Из поле со списком в Excel 2010 создать выпадающий список

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

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

Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)

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

Предположим, что элементы списка шт;кг;кв.м;куб.м введены в ячейки диапазона A 1: A 4 , тогда поле Источник будет содержать =лист1!$A$1:$A$4

Преимущество : наглядность перечня элементов и простота его модификации. Подход годится для редко изменяющихся списков. Недостатки : если добавляются новые элементы, то приходится вручную изменять ссылку на диапазон. Правда, в качестве источника можно определить сразу более широкий диапазон, например, A 1: A 100 . Но, тогда выпадающий список может содержать пустые строки (если, например, часть элементов была удалена или список только что был создан). Чтобы пустые строки исчезли необходимо сохранить файл.

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

Читайте так же:
Как восстановить историю в хроме после удаления

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

Мультивыбор

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

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


Обратите внимание, что в строке

Следует проставить адрес ячейки со списком. У нас это будет E7.

Вернитесь на лист Excel и создайте в ячейке E7 список.

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

Следующий код позволит накапливать значения в ячейке.

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

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

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