Создание межтабличных связей
Пример решения расчетной задачи с использованием процессора электронных таблиц
Условие задачи.
Фирма ООО "Строймастер" осуществляет деятельность, связанную с оптовой торговлей – покупкой и продажей строительных материалов. Цены на товары представлены в табл.1.1, данные о фирмах – заказчиках в табл. 1.2.
1. Построить таблицы "Базовый прайс-лист", "Список фирм–заказчиков", "Список заказов на месяц" по приведенным ниже табличным данным (табл. 1.1, 1.2, 1.3).
Таблица 1.1 – Базовый прайс-лист
| Код товара | Наименование товара | Поставщик | Единица измерения | Цена, руб. |
| Плитка керамическая | ООО "Ситалл" | кв.м. | ||
| Краска масляная | АО "Цвет" | кг. | ||
| Гипсокартон | "Крафт" | шт. | ||
| Доска обрезная | ООО "Лесообработка" | куб.м. |
Таблица 1.2 – Список фирм–заказчиков
| Код фирмы | Наименование фирмы | Адрес | Телефон | Контактная персона |
| Сервис | Кутузовский пр., 2 | 222-22-32 | Сидоров В.В. | |
| Проект-М | ул. Ботаническая, 3 | 331-09-65 | Бородкин К.П. | |
| Факториал | ул. Тверская, 15 | 976-43-23 | Власова И.А. | |
| Приват | ул. Ордынка, 33 | 123-34-56 | Столетова С.М. | |
| Строитель | ул. Грибоедова, 46 | 456-34-12 | Кравченко А.И. |
Таблица 1.3 – Список заказов на месяц
| Код заказа | Дата | Код фирмы | Наименование фирмы | Код товара | Наименование товара | Количество | Сумма, руб |
2. В таблице "Список заказов на месяц" организовать межтабличные связи для автоматического заполнения граф: "Наименование фирмы", "Наименование товара", используя данные таблиц "Базовый прайс-лист" и "Список фирм–заказчиков", код фирмы, код товара а также функции ЕСЛИ(), ПРОСМОТР() для MS Excel или функции If(), LOOKUP() для ООо.Calc. Рассчитать суммы заказов каждой фирмы-заказчика в графе "Сумма, руб." ,
3. Создать сводные таблицы "Форма заказа" и "Итоговая сумма заказов фирмы" по образцам (рис. 1.1, 1.2), используя в качестве основы таблицу "Список заказов на месяц".
| ООО "Строймастер" | |||||
| Заказ № | |||||
| Дата выполнения заказа | |||||
| Код фирмы | Наименование фирмы | Код товара | Наименование товара | Количество | Сумма, руб |
| Общий итог | |||||
| Принял | |||||
| Дата |
Рисунок 1.1 – Образец сводной таблицы "Форма заказа"
| ООО "Строймастер" | ||
| Итоговые суммы заказов по фирмам-заказчикам | ||
| Код товара | Наименование фирмы | Сумма, руб. |
| Общий итог |
Рисунок 1.2 – Образец сводной таблицы "Итоговая сумма заказов фирмы"
4. Построить диаграмму распределения сумм заказов по фирмам–заказчикам.
Оформить таблицы и рабочие листы.
Решение расчетной задачи
Общие операции по созданию, заполнению, форматированию таблиц, ячеек, формул, диаграмм, анализа данных в процессорах электронных таблиц MS Excel ХР-2010 и OpenOffice.org Calc совпадают, соответственно, решение расчетной задачи в этих приложениях, за исключением некоторых указанных ниже операций также совпадают.
1.Запустить табличный процессор MS Excel или OOoCalc.
2. Сохранить открытую книгу с именем "Строймастер". Пункт меню Файл®Сохранить (в MS Excel 2007(2010) кнопка Office ®Сохранить).
Ввод исходных данных
3. Лист 1 переименовать в лист с названием Товары (двойной щелчок левой кнопкой мыши (ЛКП) по имени листа Лист 1 и ввести новое имя).
4. На рабочем листе Товары создать таблицу "Базовый прайс-лист" согласно табл.1.1 и заполнить ее исходными данными (рис. 1.3).
Рисунок 1.3 – Таблица "Базовый прайс-лист" на рабочем листе Товары
5. Лист 2 переименовать в лист с названием Заказчики.
6. На рабочем листе Заказчики создать таблицу, в которой будет содержаться список фирм-заказчиков согласно табл.1.2 и заполнить ее исходными данными (рис. 1.4).
7. Лист 3 переименовать в лист с названием Список заказов.
Рисунок 1.4 – Таблица "Список фирм-заказчиков" на листе Заказчики
8. На рабочем листе Список заказов создать таблицу "Список заказов на месяц" согласно табл.1.3, оставив незаполненными графы "Наименование фирмы", "Наименование товара", "Сумма".
Создание межтабличных связей
Для создания межтабличных связей и заполнения таблицы "Список заказов на месяц" (рис. 1.5) следует применить функции ЕСЛИ(), ПРОСМОТР() для MS Excel или аналогичные функции If(), LOOKUP() для OOoCalc и выполнить следующие операции:
Рисунок 1.5 – Таблица "Список заказов за месяц" на листе Список заказов,заполненная связанными данными из других таблиц
9. Для заполнения графы Наименование фирмы таблицы "Список заказов за месяц" данными из таблицы "Список фирм-заказчиков" ввести в ячейку DЗ выражение (используя диалоговые окна соответствующих функций, см. ниже):
=ЕСЛИ(C3="";"";ПРОСМОТР(C3;Заказчики!$A$3:$A$7; Заказчики!$B$3:$B$7)).
Функция ПРОСМОТР (векторная форма)[1] просматривает диапазон, в который входят значения только одной строки или одного столбца (так называемый вектор), в поисках определенного значения и возвращает значение из другого столбца или строки. Функция имеет следующий синтаксис:
ПРОСМОТР(искомое_значение;
просматриваемый_вектор;вектор_результатов),
где искомое_значение – это значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.
Просматриваемый_вектор – интервал, содержащий только одну строку или один столбец. Значения в аргументе просматриваемый_вектор могут быть текстами, числами или логическими значениями.
Вектор_результатов – интервал, содержащий только одну строку или один столбец. Он должен быть того же размера, что и просматриваемый_вектор.
Для правильного ввода выражения следует выделить ячейку D3, выбрать пункт меню Вставка®Функция (в MS Excel 2007(2010) вкладка Формулы, кнопка Вставитьфункцию), в открывшимся Мастере функций (рис.1.6) в категории "Полный алфавитный перечень", выделить функцию ЕСЛИ, нажать кнопку ОК.
Рисунок 1.6 – Выбор функции ЕСЛИ с помощью Мастера функций
В диалоговом окне Аргументы функции ЕСЛИ (рис.1.7) в поле Лог_выражение ввести С3="", в поле Значение_если_истина ввести "" (двойные кавычки).
Рисунок 1.7 – Диалоговое окно аргументов функции ЕСЛИ
Установить курсор в поле Значение_если_ложь. В левой части Строки формул рабочего листа Excel раскрыть список, выбрать Другие функции и в открывшимся диалоговом окне Мастер функций в категории "Полный алфавитный перечень", выделить функцию ПРОСМОТР, нажать кнопку ОК.В открывшимся диалоговом окне выбора списка аргументов функции ПРОСМОТР (рис.1.8) выделить первую строку нажать кнопку ОК.
Рисунок 1.8 – Выбор списка аргументов функции ПРОСМОТР
В открывшимся диалоговом окне списка аргументов функции ПРОСМОТР (рис.1.9) ввести в поле Искомое_значение адрес ячейки С3 (лист "Список заказов").
Рисунок 1.9 – Диалоговое окно аргументов функции ПРОСМОТР
Установить курсор в поле Просматриваемый_вектор и выделить диапазон А3:А7 на листе "Заказчики", содержащий коды фирм. Нажать клавишу клавиатуры F4 (в OOoCalc Shift+F4) для создания абсолютной ссылки. Установить курсор в поле Вектор_результатов и выделить диапазон В3:В7 на листе "Заказчики", содержащий названия фирм. Нажать клавишу F4 (Shift+F4). Нажать кнопку ОК.
Скопировать с помощью маркера заполнения, введенную в ячейку D3 формулу в ячейки D4:D11.
Если в ячейку С3 таблицы "Список заказов на месяц" ввести число от 1 до 5, то в результате функция ПРОСМОТР найдет аналогичное число в таблице "Список фирм-заказчиков" в первом столбце и возвратит в ячейку D3 соответствующие введенному числу название фирмы из второго столбца таблицы "Список фирм-заказчиков". Таким образом будет создана межтабличная связь и выполнен цикл, управляющим параметром которого является номер строки.
В OOoCalc формула будет иметь вид:
=IF(C3="";"";LOOKUP(C3;Заказчики.$A$3:$A$7;Заказчики.$B$3:$B$7))
Для ввода формулы также следует использовать Мастер функций (пункт меню Вставка®Функция) идиалоговые окна соответствующих функций(рис.1.10). Выбрать функцию IF ® кнопка Далее ®в поле Тест ввести С3="", в поле Тогда значение ввести "" (двойные кавычки).
Установить курсор в поле Иначе значение. В левой части Мастера функцийвыбрать функцию LOOKUP ® кнопка Далее ® в поле Критерий поискаввести адрес ячейки С3 (лист "Список заказов"). Установить курсор в поле Вектор просмотра и выделить диапазон А3:А7 на листе "Заказчики", содержащий коды фирм. Нажать клавиши клавиатуры Shift+F4 для создания абсолютной ссылки. Установить курсор в поле Вектор результатов и выделить диапазон В3:В7 на листе "Заказчики", содержащий названия фирм. Нажать клавишу F4 (Shift+F4). Нажать кнопку ОК(рис.1.10).
Рисунок 1.10 – Ввод формулы с помощью Мастера функций в OOoCalc
9.1. Заполнить графу Наименование товара таблицы "Список заказов за месяц" данными из таблицы "Базовый прайс-лист". Для этого ввести в ячейку F3 формулу (используя диалоговые окна соответствующих функций):
=ЕСЛИ(E3="";"";ПРОСМОТР(E3;Товары!$A$3:$A$6;Товары!$B$3:$B$6)).
Скопировать с помощью маркера заполнения, введенную в ячейку F3 формулу в ячейки F4:F11.
В OOoCalc формула будет иметь вид:
=IF(E3="";"";LOOKUP(E3;Товары.$A$3:$A$6;$Товары.$B$3:$B$6)).
9.2. Для расчета суммы заказов каждой фирмы-заказчика (количество товара * на цену единицы товара) заполнить графу Сумма, руб. таблицы "Список заказов за месяц", используя данные о ценах на товары из таблицы "Базовый прайс-лист". Ввести в ячейку Н3 формулу:
=ПРОСМОТР(E3;Товары!$A$3:$A$6;Товары!$E$3:$E$6)*G3.
Скопировать с помощью маркера заполнения, введенную в ячейку Н3 формулу в ячейки Н4:Н11.
В OOoCalc формула будет иметь вид:
=LOOKUP(E3;Товары.$A$3:$A$6;Товары.$E$3:$E$6)*G3