|
Читайте также: |
Обычно база данных состоит из нескольких таблиц, связанных между собой. Как правило, в БД существует основная таблица (рабочая), где хранятся оперативные данные по предметной области, и несколько вспомогательных таблиц, содержащих справочную информацию для основной таблицы. Базы данных проектируются таким образом, чтобы избежать избыточности информации и увеличения объёмов таблиц.
Таблицы базы данных связываются по общим полям, т.е. каждая таблица должна иметь хотя бы один столбец, заголовок и значения которого имеются в другой таблице. Такое поле называется ключевым, связующим полем.
Поиск в связанных таблицах необходимых данных осуществляется таким образом: для искомого значения ключевого поля основной таблицы в первом столбце справочной таблицы находится соответствующее ему значение, затем для него из указанного столбца выводится нужное.
Для связи таблиц поиска в Excel существуют специальные функции, которые организуют автоматический поиск данных в справочной таблице и их использование в основной таблице расчётов.
Это функции из группы «Ссылки и массивы» Мастера функций - ВПР (вертикальный поиск) и
ГПР (горизонтальный поиск).
Функция ВПР просматривает первый столбец справочной таблицы в поисках заданного значения и возвращает соответствующее ему значение из столбца этой же таблицы с заданным номером.
Функция ГПР просматривает первую строку таблицы в поисках заданного значения и возвращает соответствующее значение из строки с указанным номером.
Формат функций:
ВПР(искомое знач.-е;таблица;номер столбца; тип просмотра)
ГПР(искомое знач.-е;таблица;номер строки; тип просмотра)
Аргументы функций ВПР:
ü Искомое значение - это значение связующего поля из первой строки основной таблицы, которое должно быть найдено в первом столбце (связующем) справочной таблицы.
ü Таблица - это справочная таблица, в которой ищутся данные. Можно указать её координаты (абсолютные адреса!) или имя в списке имён.
ü Номер столбца - это номер столбца в справочной таблице, в котором должно быть найдено соответствующее значение искомому значению.
ü Тип просмотра -необязательный аргумент, имеет значение логической константы ИСТИНА или ЛОЖЬ и определяет, нужно ли, чтобы функция искала точное или приближенное соответствие.
· Если этот аргумент - ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; а именно: если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем заданное. Значения в первом столбце таблицы должны быть расположены в возрастающем порядке.
· Если этот аргумент - ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д. Таблица в этом случае не обязана быть сортированной.
Функция ГПР имеет те же аргументы, за исключением номера столбца, который заменён на номер строки.
Пример 8.1. Подсчитать общую стоимость поступившего товара в магазин, используя справочную таблицу – «Прейскурант товаров».
 |
Рис.19. Таблица учёта
поступившего товара.
Рис.20. Справочная таблица
«Прейскурант товаров».
Решение:
1) Вычислить стоимость партии каждого товара в таблице учёта (рис.19) по формуле:
«Стоимость партии» = «размер партии» * «цена за кг.»,
где «цена за кг.» находится в таблице «Прейскурант товаров» по заданному наименованию продукции:
 |
= ВПР (B3 ;$G$5:$H$9; 2; ЛОЖЬ)* D3;
 |
2) Скопировать формулу на блок ячеек Е3:Е10.
3) Подсчитать общую стоимость всех проданных
товаров - å.
Дата добавления: 2015-07-12; просмотров: 79 | Нарушение авторских прав
| <== предыдущая страница | | | следующая страница ==> |
| Выполнение. N | | | Контрольные задания |