Как создать справочник в excel

Справочник в EXCEL

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

Создадим Справочник на примере заполнения накладной.

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

Таблица Товары

Эту таблицу создадим на листе Товары с помощью меню Вставка/ Таблицы/ Таблица , т.е. в формате EXCEL 2007 (см. файл примера ). По умолчанию новой таблице EXCEL присвоит стандартное имя Таблица1 . Измените его на имя Товары , например, через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен )

К таблице Товары , как к справочной таблице, предъявляется одно жесткое требование: наличие поля с неповторяющимися значениями. Это поле называется ключевым . В нашем случае, ключевым будет поле, содержащее наименования Товара. Именно по этому полю будут выбираться остальные значения из справочной таблицы для подстановки в накладную.

Для гарантированного обеспечения уникальности наименований товаров используем Проверку данных ( Данные/ Работа с данными/ Проверка данных ):

• выделим диапазон А2:А9 на листе Товары ;
• вызовем Проверку данных ;
• в поле Тип данных выберем Другой и введем формулу, проверяющую вводимое значение на уникальность:

При создании новых записей о товарах (например, в ячейке А10 ), EXCEL автоматически скопирует правило Проверки данных из ячейки А9 – в этом проявляется одно преимуществ таблиц, созданных в формате Excel 2007 , по сравнению с обычными диапазонами ячеек. Проверка данных срабатывает, если после ввода значения в ячейку нажата клавиша ENTER . Если значение скопировано из Буфера обмена или скопировано через Маркер заполнения , то Проверка данных не срабатывает, а лишь помечает ячейку маленьким зеленым треугольником в левом верхнем углу ячейке.

Через меню Данные/ Работа с данными/ Проверка данных/ Обвести неверные данные можно получить информацию о наличии данных, которые были введены с нарушением требований Проверки данных .

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

Теперь, создадим Именованный диапазон Список_Товаров, содержащий все наименования товаров :

• выделите диапазон А2:А9 ;
• вызовите меню Формулы/ Определенные имена/ Присвоить имя
• в поле Имя введите Список_Товаров ;
• убедитесь, что в поле Диапазон введена формула =Товары[Наименование]
• нажмите ОК.

Таблица Накладная

К таблице Накладная , также, предъявляется одно жесткое требование: все значения в столбце (поле) Товар должны содержаться в ключевом поле таблицы Товары . Другими словами, в накладную можно вводить только те товары, которые имеются в справочной таблице Товаров , иначе, смысл создания Справочника пропадает. Для формирования Выпадающего (раскрывающегося) списка для ввода названий товаров используем Проверку данных :

• выделите диапазон C4:C14 ;
• вызовите Проверку данных ;
• в поле Тип данных выберите Список;
• в качестве формулы введите ссылку на ранее созданный Именованный диапазон Список_товаров , т.е. =Список_Товаров .

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

Теперь заполним формулами столбцы накладной Ед.изм., Цена и НДС . Для этого используем функцию ВПР() :

или аналогичную ей формулу

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

В столбцах Цена и НДС введите соответственно формулы: =ЕСЛИОШИБКА(ВПР(C4;Товары;3;ЛОЖЬ);»») =ЕСЛИОШИБКА(ВПР(C4;Товары;4;ЛОЖЬ);»»)

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

Телефонный справочник в Excel готовый шаблон скачать

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

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

Шаблон телефонного справочника

Как сделать справочник в Excel? Для создания телефонного справочника нужны, минимум, два столбца: имя человека или организации и, собственно, номер телефона. Но можно сразу сделать список более информативным, добавив дополнительные строки.

Шаблон готов. Шапка может быть другой, какие-то столбцы должны быть добавлены, какие-то исключены. Осталось только заполнить справочник информацией.

Дополнительно можно провести еще одну манипуляцию: определить формат ячеек. По умолчанию формат каждой ячейки значится как ОБЩИЙ. Можно оставить все как есть, но для столбца с номером телефона можно задать специальный формат. Для этого надо выделить ячейки из этого столбца, правой кнопкой вызвать меню, выбрать ФОРМАТ ЯЧЕЕК.

Среди предоставленных вариантов выбрать ДОПОЛНИТЕЛЬНЫЙ. Справа откроется мини-список, среди которых можно будет выбрать НОМЕР ТЕЛЕФОНА.

Как пользоваться справочником

Любой справочник нужен для того, чтобы по одному критерию можно было легко узнать остальные. Так, в телефонном справочнике мы можем ввести необходимую фамилию и узнать номер телефона этого человека. В Excel сделать это помогают функции ИНДЕКС и ПОИСКПОЗ.

Имеем небольшой справочник. В действительности, в фирмах обычно более длинные списки, поэтому и искать в них информацию вручную сложно. Составим заготовку, в которой будет значиться вся информация. А появляться она будет по заданному критерию – фамилия, поэтому сделаем этот пункт в виде выпадающего списка (ДАННЫЕ – ПРОВЕРКА ДАННЫХ – ТИП ДАННЫХ – СПИСОК).

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

В ячейку J6 (там, где ИМЯ) вводим команду =ИНДЕКС и начинаем заполнять аргументы.

1. Массив: выделяем всю таблицу заказов вместе с шапкой. Делаем его абсолютным, фиксируя клавишей F4.
2. Номер строки: сюда вводим ПОИСКПОЗ и заполняем уже аргументы этой функции. Искомым значением будет ячейка с выпадающим списком – J6 (плюс F4). Просматриваемым массивом является столбец с фамилиями (вместе с шапкой): A1:A13 (плюс F4). Тип сопоставления: точное совпадение, т.е. 0.
3. Номер столбца: снова нужен ПОИСКПОЗ. Искомое значение: I7. Просматриваемый массив: шапка массива, т.е. А1:Н1 (плюс F4). Тип сопоставления: 0.

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

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

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

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

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

Выделим оба списка (без шапок) с помощью клавиши CTRL. Свободное место между списками (т.е. столбец B) нам не нужно. Затем на вкладке ГЛАВНАЯ выбираем УСЛОВНОЕ ФОРМАТИРОВАНИЕ – ПРАВИЛА ВЫДЕЛЕНИЯ ЯЧЕЕК – ПОВТОРЯЮЩИЕСЯ ЗНАЧЕНИЯ.

Появится небольшое окно, где можно выбрать, чтобы команда показывала повторяющиеся или уникальные значения. Выберем УНИКАЛЬНЫЕ. Они подсветятся цветом, который можно выбрать справа. У нас это красный.

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

• Excel Formula Examples
• Создать таблицу
• Форматирование
• Функции Excel
• Формулы и диапазоны
• Фильтр и сортировка
• Диаграммы и графики
• Сводные таблицы
• Печать документов
• Базы данных и XML
• Возможности Excel
• Настройки параметры
• Уроки Excel
• Макросы VBA
• Скачать примеры

Финансы в Excel

Главная Статьи Формулы Использование справочников

Использование справочников

Содержание
Поиск в одномерном справочнике
Поиск в двумерном справочнике
Использование справочников для создания пользовательского интерфейса

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

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

• Одномерный — продукты с наименованием и единицей измерения
• Двумерный — прайс-листы с различными ценами по продуктам и периодам.

Поиск в одномерном справочнике

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

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

="руб/" & B3

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

=VLOOKUP(B17;$A$3:$B$6;2;0)

Функция ВПР ищет значение по определенному номеру столбца (в примере – второй столбец) в прямоугольном диапазоне ячеек. Последний параметр (0 или ЛОЖЬ) используется для указания искать ли ближайшее значение (ИСТИНА), либо точное совпадение (ЛОЖЬ). Первый вариант редко используется на практике, так как требует предварительно отсортированного диапазона поиска. Важное замечание по параметру «столбец». Это не номер столбца во всей электронной таблице, а номер столбца в диапазоне поиска. Для аналойного поиска по горизонтальному массиву используется функция ГПР (HLOOKUP).

Поиск в двумерном справочнике

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

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

=OFFSET($A$9;MATCH(B17;$A$10:$A$13;0);MATCH(DATE(YEAR(A17);MONTH(A17);1);$C$9:$E$9;0)+1)

Функция ПОИСКПОЗ (MATCH) ищет и возвращает порядковый номер элемента в списке (работает аналогично ВПР). Функция СМЕЩ (OFFSET) возвращает значение по относительной ссылке от определенной ячейки – в примере, от верхнего левого угла таблицы прайс-листов. Функция преобразования даты ДАТА (DATE) превращает дату в из таблицы продаж в дату, используемую в прайс-листе – первое число месяца.

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

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

Для лучшего понимания формулы, разложим ее в виде структуры и заменим диапазоны на значения (через нажатие F9 на выделенном диапазоне). Получим следующую картину:

=SUM( IF("Продукт4"=; IF(3=; < 2;2,1;2,2: 3;2,9;3,05: 1299;1280;1350: 6,4;6,9;6,7 >) ) )

Первое условие на продукт возвращает массив , второе условие выбирает из этого массива последнее значение.

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

Использование справочников для создания пользовательского интерфейса

Обратите внимание на выпадающий список для выбора элементов справочника на листе «Пример2», таблицы «Продажи» (Пример2!B17).

Организация подобных выпадающих списков возможна через опцию «Проверка». Доступ в Excel до 2007 через меню Данные\Проверка (Data\Validation), в Excel 2007 и старше – лента Данные\Работа с данными\Проверка данных (Data\Data Tools\Data Validation).

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

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

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

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

Обратите внимание на описание проверки в ячейке Пример2!B18. Здесь вместо ссылки на лист «Пример1» задана формула

=listProducts

Имя listProducts задано в примере как диапазон Пример1!$A$3:$A$6. Очевидно, что использование такого именованного диапазона делает более прозрачным смысл заданного условия проверки, а также снижает риск ошибки в формуле.

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

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

Читайте все накопления сайта по своему профилю, начиная с 2010 г.
Для этого оформите комплексную подписку на выбранный журнал на полугодие или год, тогда:

• его свежий номер будет ежемесячно приходить к вам по почте в печатном виде;
• все публикации на сайте этого направления начиная с 2010 г. будут доступны в течение действия комплексной подписки.

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

Рекомендовано для вас

«Библиотека знаний»: как создать и использовать (часть 1)

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

Светлана Глотова
к.и.н., доцент кафедры документоведения, аудиовизуальных и научно-технических архивов РГГУ

Когда регистрировать протокол?

Подскажите, пожалуйста, есть ли правила регистрации протокола? Он должен регистрироваться день в день, или можно его регистрировать позже в СЭД?

Вера Иритикова

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

Показать больше

Приказы в организации: виды и особенности оформления

Мы приводим обзор нормативной базы, которой следует руководствоваться при издании приказа, а также пример раздела Инструкции по делопроизводству (его формулировками вы можете воспользоваться при подготовке своего локального нормативного документа). Далее мы разбираем нюансы оформления каждого реквизита внутреннего и совместного приказа. Примеры оформления отдельных реквизитов и целых документов (всего их в статье 17) делают материал понятным и наглядным. Если в области оформления приказов у вас еще остались вопросы, то сейчас вы можете их прояснить. А если вы ас, то можете использовать статью для подготовки методических материалов для сотрудников своей организации либо просто адресуйте их к ней.

Евгения Кожанова
специалист по ДОУ и кадровому делопроизводству

Контроль исполнения поручений в MS Excel

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

Антипов Денис
специалист по электронному документообороту ООО «Тулпар Техник»

Режим рецензирования в Word: автоматическое выявление правок

Режим «Рецензирование» существенно облегчает разработку, согласование вордовского документа группой людей – он автоматически фиксирует каждую вставку, удаление, перемещение текста, форматирование и все это пользователи еще могут комментировать! Показываем, как управлять отображением правок (каким способом, какие типы и от каких пользователей показывать, как определить автора изменений, как распечатать исходный или окончательный вариант текста). Как выявить правки, которые коллега пытался вставить незаметно! Можно объединить в одном документе правки от разных людей, присланные ими в разных версиях файла. А еще можно защитить документ от редактирования либо установить пользователям пределы дозволенного. Даже если режимом «Рецензирование» уже пользуетесь, из этой статьи сможете узнать новые «ювелирные» настройки, которые сделают вашу работу еще удобнее.

Надежда Артонкина
специалист по внедрению автоматизированных систем управления

В конце года обнаружились «пустые» регистрационные номера

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