Решение расчетной задачи
Общие операции по созданию, заполнению, форматированию таблиц, ячеек, формул, диаграмм, анализа данных в процессорах электронных таблиц 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