Создание связанных таблиц в Excel

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

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

Необходимым условиям является наличие столбца с уникальными значениями во всех таблицах, которые должны быть связанными.

Важно, чтобы данные были представлены не только в виде таблицы, но и отформатированы как таблица. Редактирование строк и столбцов с данными в специальной форме таблицы необходимо для версий с Word 2007 и выше.

Если информация записана по столбцам и строкам, необходимо выделить нужную область и на панели инструментов закладки «Главная» выбрать функцию форматирования данных как таблицы.



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




Следующий шаг – присвоение имени каждой таблице. Это осуществляется на закладке «Конструктор» в одноименном поле.



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

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

Далее возможны варианты в зависимости от версии MS Word и сложности задачи.

Функция ВПР()

В более старых версиях Word связывать таблицы можно через функцию ВПР(). Ее задача – поиск определенного значения в диапазоне и подстановка найденного в указанную ячейку.

Для этого устанавливается курсор в нужном поле. На закладке «Формула» выбирается нужная функция. Мастер предлагает найти по наименованию или категории то, что требуется в данный момент.



После нажатия кнопки «Ок» система выдаст запрос на указание аргументов.



Параметр «Исходное значение» задает то, что нужно найти в одной таблице и перенести в другую.
Параметр «Таблица» позволяет указать диапазон поиска.
Параметр «Номер столбца» показывает, какие данные из строки с искомым элементом необходимо взять.
Параметр «Интервальный просмотр» дает возможность указаний критерии поиска. Если указано значение «ЛОЖЬ», то поиск будет происходить строго по указанному значению. Аргумент «ИСТИНА» устанавливает более мягкий поиск.



Однако использование функции имеет свои ограничения. Так, например, отрицательный результат поиска возможен, если:
• Таблица, в которой происходит поиск, не отсортирована по столбцу, по которому указан аргумент «Исходное значение»
• Данные отсутствуют в исходной таблице
• Отличие форматов ячеек таблицы, из которой берутся значения, от той, куда вставляются. Особенно это показательно для числовых данных
• Наличие пробелов или невидимых непечатных знаков в диапазоне поиска – данная проблема может быть решена путем использования функций СПРОБЕЛЫ() и ПЕЧСИМВ().

Так же можно ввести дополнительную проверку с включением в качестве аргумента функции ЕСЛИОШИБКА().

И самый важный минус – функция берет значение для поиска только один раз. Если искомое значение встречается в конечной таблице несколько раз, то для второй и последующих строк ВПР() вернет «#ЗНАЧ!» (или «#Н/Д»).

Функции ИНДЕКС() и ПОИСКПОЗ()

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



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



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



Из параметров «Номер строки» и «Номер столбца» задается только один на выбор для выбора искомого значения.

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



В качестве параметров для ПОИСКПОЗ() в первом аргументе нужно выбрать столбец значений второй таблицы, в качестве второго аргумента выбирается первая таблица, где осуществляется поиск.



Тип сопоставления показывает жесткость критериев отбора – ноль или единица.



Специальная вставка

Еще один способ подразумевает связь между листами. Для этого нужно выделить ячейку и нажать сочетание клавиш Ctrl + C либо воспользоваться контекстным меню с функцией «Копировать».



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







Другие варианты

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

Рейтинг:(голосов:1)