Работа со справочником excel

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

Создадим

Справочник

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

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

Товары

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

Таблица

Товары

Эту таблицу создадим на листе

Товары

с помощью меню

, т.е. в

формате EXCEL 2007

(см.

файл примера

). По умолчанию новой таблице EXCEL присвоит стандартное

имя

Таблица1

. Измените его на имя

Товары

, например, через Диспетчер имен (

)

К таблице

Товары

, как к справочной таблице, предъявляется одно жесткое требование: наличие поля с

неповторяющимися

значениями. Это поле называется

ключевым

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

Для гарантированного обеспечения

уникальности

наименований товаров используем

Проверку данных

(

):

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

=ПОИСКПОЗ(A2;$A:$A;0)=СТРОКА(A2)

При создании новых записей о товарах (например, в ячейке

А10

), EXCEL автоматически скопирует правило

Проверки данных

из ячейки

А9

– в этом проявляется одно преимуществ таблиц, созданных

в формате Excel 2007

, по сравнению с обычными диапазонами ячеек.

Проверка данных

срабатывает, если после ввода значения в ячейку нажата клавиша

ENTER

. Если значение скопировано из

Буфера обмена

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

Маркер заполнения

, то

Проверка данных

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

Через меню

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

Проверки данных

.

Для контроля уникальности также можно использовать

Условное форматирование

(см. статью

Выделение повторяющихся значений

).

Теперь, создадим

Именованный диапазон

Список_Товаров,

содержащий все наименования товаров

:

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

Таблица

Накладная

К таблице

Накладная

, также, предъявляется одно жесткое требование: все значения в столбце (поле)

Товар

должны содержаться в ключевом поле таблицы

Товары

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

Товаров

, иначе, смысл создания

Справочника

пропадает. Для формирования

Выпадающего (раскрывающегося) списка

для ввода названий товаров используем

Проверку данных

:

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

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

Товары

.

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

Ед.изм., Цена

и

НДС

. Для этого используем функцию

ВПР()

:

=ЕСЛИОШИБКА(ВПР(C4;Товары;2;ЛОЖЬ);»»)

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

=ИНДЕКС(Товары;ПОИСКПОЗ(C4;Список_Товаров;0);2)

Преимущество этой формулы перед функцией

ВПР()

состоит в том, что ключевой столбец

Наименование

в таблице

Товары

не обязан быть самым левым в таблице, как в случае использования

ВПР()

.

В столбцах

Цена

и

НДС

введите соответственно формулы:

=ЕСЛИОШИБКА(ВПР(C4;Товары;3;ЛОЖЬ);»») =ЕСЛИОШИБКА(ВПР(C4;Товары;4;ЛОЖЬ);»»)

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

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

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

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

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

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

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

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

Пример1!B10:

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

Пример1!D17:

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

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

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

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

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

Пример1!E17:

=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) превращает дату в из таблицы продаж в дату, используемую в прайс-листе – первое число месяца.

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

Пример1!E22:

{=SUM(IF(B22=$A$10:$A$13;IF(MONTH(A22)=MONTH($C$9:$E$9);$C$10:$E$13)))}

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

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

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

Первое условие на продукт возвращает массив {6,4;6,9;6,7}, второе условие выбирает из этого массива последнее значение.

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

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

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

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

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

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

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

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

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

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

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

Вся обрабатываемая
инф-ция принята делиться на:

• Выходная

• Входная

  • Справочная-это
    та которая остается неизменной в
    течении длительного времени

  • Оперативная-
    меняется по истечению определённого
    времени

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

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

20. Фильтрация данных в табличном процессоре ms Excel

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

Для фильтрации
списков в Excel
существует две команды :

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

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

19. Табличный процессор ms Excel: работа с эт как с базой данных.

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

Таблица в Excel
представляет собой однотабличную
базу
данных.

В Excel
базы данных называются списками.

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

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

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

Для фильтрации
списков в Excel
существует две команды :

• Автофильтр

• Расширенный
  фильтр

Синтаксис:

Функция
(база данных; поле; диапазон условий),
где

база
данных
– интервал ячеек, формирующих базу
данных;

поле
— имя столбца (поля) базы данных;

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

Наиболее
часто используемые функции: БДСУММ,
БСЧЕТ,
ДМАКС,
ДМИН,
ДСРЗНАЧ.

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #

Автор: Сергей Талалаев

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

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

=VLOOKUP(B2 ; Список_Штатов ; 2 ; FALSE)

=VLOOKUP(D10 ; Возраст_автомобиля; 2 ; TRUE)

=INDEX(Список_Штатов,0,1)

=INDEX(INDIRECT(«Города_»&B2);0;2)

Обсудить в форуме