Как Создать Справочник В Excel
Александр, спасибо за статью. Как в excel 2010 создать список, не нашел в главном меню «Данные. 15 Mar 2014 - 6 min - Uploaded by Vadim NikulinЗачем нужны списки и как ими пользоваться. Пример с курсами доллара. Авторский сайт JOIN VSP GROUP PARTNER. Все ответы на тему - Как создать справочник в excel. Вся информация на BabyBlog.
Справочник состоит из двух таблиц: справочной таблицы, в строках которой содержатся подробные записи о некоторых объектах (сотрудниках, товарах, банковских реквизитах и пр.) и таблицы, в которую заносятся данные связанные с этими объектами. Указав в ячейке лишь ключевое слово, например, фамилию сотрудника или код товара, можно вывести в смежных ячейках дополнительную информацию из справочной таблицы. Другими словами, структура Справочник снижает количество ручного ввода и уменьшает количество опечаток.
Создадим Справочник на примере заполнения накладной. В накладной будем выбирать наименование товара, а цена, единица измерения и НДС, будут подставляться в нужные ячейки автоматически из справочной таблицы Товары, содержащей перечень товаров с указанием, соответственно, цены, единицы измерения, НДС.
Логические Формулы В Excel
Таблица Товары Эту таблицу создадим на листе Товары с помощью меню Вставка/ Таблицы/ Таблица, т.е. Файл примера).
По умолчанию новой таблице EXCEL присвоит стандартное Таблица1. Измените его на имя Товары, например, через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен) К таблице Товары, как к справочной таблице, предъявляется одно жесткое требование: наличие поля с значениями.
Это поле называется ключевым. В нашем случае, ключевым будет поле, содержащее наименования Товара.
Именно по этому полю будут выбираться остальные значения из справочной таблицы для подстановки в накладную. Для гарантированного обеспечения наименований товаров используем ( Данные/ Работа с данными/ Проверка данных):. выделим диапазон А2:А9 на листе Товары;. вызовем Проверку данных;. в поле Тип данных выберем Другой и введем формулу, проверяющую вводимое значение на уникальность: =ПОИСКПОЗ(A2;$A:$A;0)=СТРОКА(A2) При создании новых записей о товарах (например, в ячейке А10), EXCEL автоматически скопирует правило Проверки данных из ячейки А9 – в этом проявляется одно преимуществ таблиц, созданных, по сравнению с обычными диапазонами ячеек.
Срабатывает, если после ввода значения в ячейку нажата клавиша ENTER. Если значение скопировано из Буфера обмена или скопировано через, то не срабатывает, а лишь помечает ячейку маленьким зеленым треугольником в левом верхнем углу ячейке. Через меню Данные/ Работа с данными/ Проверка данных/ Обвести неверные данные можно получить информацию о наличии данных, которые были введены с нарушением требований Проверки данных. Для контроля уникальности также можно использовать (см. Теперь, создадим СписокТоваров, содержащий все наименования товаров:. выделите диапазон А2:А9;. вызовите меню Формулы/ Определенные имена/ Присвоить имя.
в поле Имя введите СписокТоваров;. убедитесь, что в поле Диапазон введена формула =ТоварыНаименование. нажмите ОК. Таблица Накладная К таблице Накладная, также, предъявляется одно жесткое требование: все значения в столбце (поле) Товар должны содержаться в ключевом поле таблицы Товары. Другими словами, в накладную можно вводить только те товары, которые имеются в справочной таблице Товаров, иначе, смысл создания Справочника пропадает. Для формирования для ввода названий товаров используем:.
Инструкция По Excel
выделите диапазон C 4: C 14;. вызовите Проверку данных;. в поле Тип данных выберите Список;.
Логические Выражения Excel
в качестве формулы введите ссылку на ранее созданный Именованный диапазон Списоктоваров, т.е. Теперь товары в накладной можно будет вводить только из таблицы Товары. Теперь заполним формулами столбцы накладной Ед.изм., Цена и НДС. Для этого используем функцию ВПР: =ЕСЛИОШИБКА(ВПР(C4;Товары;2;ЛОЖЬ);') или аналогичную ей формулу =ИНДЕКС(Товары;ПОИСКПОЗ(C4;СписокТоваров;0);2) Преимущество этой формулы перед функцией ВПР состоит в том, что ключевой столбец Наименование в таблице Товары не обязан быть самым левым в таблице, как в случае использования ВПР.
Функции В Экселе
В столбцах Цена и НДС введите соответственно формулы: =ЕСЛИОШИБКА(ВПР(C4;Товары;3;ЛОЖЬ);') =ЕСЛИОШИБКА(ВПР(C4;Товары;4;ЛОЖЬ);') Теперь в накладной при выборе наименования товара автоматически будут подставляться его единица измерения, цена и НДС.