Здравствуйте, прошу решить задание из файла по...

Пример решения расчетной задачи с использованием процессора электронных таблиц

Условие задачи.
Фирма ООО "Строймастер" осуществляет деятельность, связанную с покупкой и продажей строительных материалов. Цены на товары представлены в табл.1.1, данные о фирмах – заказчиках в табл. 1.2.
1. Построить таблицы "Базовый прайс-лист", "Список фирм– заказчиков", "Список заказов на месяц" по приведенным ниже табличным данным (табл. 1.1, 1.2, 1.3).
Таблица 1.1 – Базовый прайс-лист

2. В таблице "Список заказов на месяц" организовать межтабличные связи для автоматического заполнения граф: "Наименование фирмы", "Наименование товара", используя данные таблиц "Базовый прайс-лист" и
"Список фирм–заказчиков", код фирмы, код товара а также функции ЕСЛИ(), ПРОСМОТР() для MS Excel или функции If(), LOOKUP() для ООо.Calc.
Рассчитать суммы заказов каждой фирмы-заказчика в графе "Сумма, руб." , 3. Создать сводные таблицы "Форма заказа" и "Итоговая сумма заказов фирмы" по образцам (рис. 1.1, 1.2), используя в качестве основы таблицу "Список заказов на месяц".

4. Построить диаграмму распределения сумм заказов по фирмам–заказчикам.
Оформить таблицы и рабочие листы.

Решение расчетной задачи

Общие операции по созданию, заполнению, форматированию таблиц, ячеек, формул, диаграмм, анализа данных в процессорах электронных таблиц MS Excel ХР-2010 и OpenOffice.org Calc совпадают, соответственно, решение расчетной задачи в этих приложениях, за исключением некоторых указанных ниже операций также совпадают.
1.Запустить табличный процессор MS Excel или OOoCalc. 
2. Сохранить открытую книгу с именем "Строймастер". Пункт меню Файл Сохранить (в MS Excel 2007(2010) кнопка Office Сохранить).

Ввод исходных данных.
3. Лист 1 переименовать в лист с названием Товары.
4. На рабочем листе Товары создать таблицу "Базовый прайс-лист" согласно табл.1.1 и заполнить ее исходными данными (рис. 1.3). 

5. Лист 2 переименовать в лист с названием Заказчики.
6. На рабочем листе Заказчики создать таблицу, в которой будет содержаться список фирм-заказчиков согласно табл.1.2 и заполнить ее исходными данными (рис. 1.4).
7. Лист 3 переименовать в лист с названием Список заказов.

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

Для создания межтабличных связей и заполнения таблицы "Список заказов на месяц" (рис. 1.5) следует применить функции ЕСЛИ(), ПРОСМОТР() для MS Excel или аналогичные функции If(), LOOKUP() для
OOoCalc и выполнить следующие операции:

9. Для заполнения графы Наименование фирмы таблицы "Список заказов за месяц" данными из таблицы "Список фирм-заказчиков" ввести в ячейку DЗ выражение (используя диалоговые окна соответствующих
функций, см. ниже): 
=ЕСЛИ(C3="";"";ПРОСМОТР(C3;Заказчики!$A$3:$A$7; 
Заказчики!$B$3:$B$7)).
Функция ПРОСМОТР (векторная форма)1 просматривает диапазон, в который входят значения только одной строки или одного столбца (так называемый вектор), в поисках определенного значения и возвращает
значение из другого столбца или строки. Функция имеет следующий синтаксис:
ПРОСМОТР(искомое_значение;
просматриваемый_вектор;вектор_результатов),
где искомое_значение – это значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.
Просматриваемый_вектор – интервал, содержащий только одну строку или один столбец. Значения в аргументе просматриваемый_вектор могут быть текстами, числами или логическими значениями.
Вектор_результатов – интервал, содержащий только одну строку или один столбец. Он должен быть того же размера, что и просматриваемый_вектор.

Для правильного ввода выражения следует выделить ячейку D3, выбрать пункт меню Вставка Функция (в MS Excel 2007(2010) вкладка Формулы, кнопка Вставить функцию), в открывшимся Мастере функций
(рис.) в категории "Полный алфавитный перечень", выделить функцию ЕСЛИ, нажать кнопку ОК.

В диалоговом окне Аргументы функции ЕСЛИ (рис.1.7) в поле Лог_выражение ввести С3="", в поле Значение_если_истина ввести "" (двойные кавычки).

Установить курсор в поле Значение_если_ложь. В левой части Строки формул рабочего листа Excel раскрыть список, выбрать Другие функции и в открывшимся диалоговом окне Мастер функций в категории "Полный алфавитный перечень", выделить функцию ПРОСМОТР, нажать кнопку ОК. В открывшимся диалоговом окне выбора списка аргументов функции ПРОСМОТР (рис) выделить первую строку нажать кнопку ОК.

В открывшимся диалоговом окне списка аргументов функции ПРОСМОТР (рис.) ввести в поле Искомое_значение адрес ячейки С3 (лист "Список заказов").

Установить курсор в поле Просматриваемый_вектор и выделить диапазон А3:А7 на листе "Заказчики", содержащий коды фирм. Нажать клавишу клавиатуры F4 (в OOoCalc Shift+F4) для создания абсолютной
ссылки. Установить курсор в поле Вектор_результатов и выделить диапазон В3:В7 на листе "Заказчики", содержащий названия фирм. Нажать клавишу F4 (Shift+F4). Нажать кнопку ОК. 
Скопировать с помощью маркера заполнения, введенную в ячейку D3 формулу в ячейки D4:D11.
Если в ячейку С3 таблицы "Список заказов на месяц" ввести число от 1 до 5, то в результате функция ПРОСМОТР найдет аналогичное число в таблице "Список фирм-заказчиков" в первом столбце и возвратит в ячейку D3 соответствующие введенному числу название фирмы из второго столбца таблицы "Список фирм-заказчиков". Таким образом будет создана межтабличная связь и выполнен цикл, управляющим параметром которого  является номер строки.
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
Создание сводной таблицы в MS Excel 2007(2010).
1. Для создания сводной таблицы следует выделить таблицу с исходными данными "Список фирм-заказчиков" (рис.1.11). В таблице не допускаются имена столбцов в объединенных ячейках. На вкладке Вставка в группе Таблицы выбрать кнопку Сводная таблица, в диалоговом окне Создание сводной таблицы место, куда будет вставлен пустой отчет сводной таблицы (на новый лист, на существующий лист). На рабочем листе
появится пустой отчет и список полей сводной таблицы.
По умолчанию список полей сводной таблицы имеет две секции:
секцию полей в верхней части (список столбцов исходной таблицы) – для добавления и удаления полей, и секцию макета в нижней части (Фильтр отчета, Названия столбцов, Названия строк, Значения) – для изменения
порядка полей и их положения.
2. Для заполнения таблицы следует выполнить следующие операции: –в секцию с надписью "Фильтр отчета" перетащить поля "Код заказа" и "Дата заказа";
–в секцию с надписью "Названия строк" последовательно перетащить поля "Код фирмы", "Наименование фирмы", "Код товара", "Наименование товара", "Количество";
–в секцию с надписью "Значения" перетащить поле "Сумма, руб", данные из этого поля будут автоматически суммироваться;
–секция с надписью "Названия столбцов" остается пустой.
3. По умолчанию отчет сводной таблицы создается в сжатой форме.
Для изменения формы отчета следует открыть вкладку Конструктор (для активной таблицы), в группе Макет, кнопка Макет отчета, выбрать команду Показать в табличной форме.
4. Как правило, автоматически созданный отчет сводной таблицы содержит промежуточные итоги, которые загромождают таблицу. Чтобы убрать строки с промежуточными итогами надо на вкладке Конструктор
(для активной таблицы), в группе Макет, кнопка Промежуточные итоги, выбрать команду Не показывать промежуточные суммы.
5. В сводной таблице каждое поле можно использовать для фильтрации данных. Для фильтрации данных с кодом заказа 22 следует раскрыть поле со списком в строке "Код заказа", выбрать значение 22 ОК. Для фильтрации данных по наименованию фирмы раскрыть список поля "Наименование
фирмы", выбрать значение АО "Проект М" ОК.
Диалоговое окно Вычисление поля сводной таблицы можно использовать для изменения имени поля. Так для переименования поля "Дата", в поле с именем "Дата выполнения заказа" следует ввести новое имя в соответствующее поле диалогового окна.
6. Для завершения создания сводной таблицы по образцу выше таблицы добавить надпись ООО "Строймастер", а ниже таблицы надписи 
"Принял" и "Дата". В итоге получим сводную таблицу в виде, показанном на рисунке  

7. Для создания сводной таблицы "Итоговые суммы заказов" вставить в рабочую книгу новый лист. Переименовать Лист 5 в лист с названием Форма заказов. Выбрать команду Сводная таблица в пункте меню Данные и повторить операции создания сводной таблицы. В диалоговом окне Мастер сводных таблиц и диаграмм – макет для области "Строка" выбрать поля "Код товара" и "Наименование фирмы", а для области "Данные" поле "Сумма, руб.". Убрать для выбранных полей промежуточные итоги. Для фильтрации выбрать из списка в поле "Наименование фирмы" запись ОАО "Привет". Общий итог рассчитывается автоматически. Результат выполнения показан на рисунке

6. Для построения сводной диаграммы на основе сводной таблицы следует открыть вкладку Параметры, в группе Сервис, выбрать кнопку Сводная диаграмма, в открывшемся диалоговом окне Вставка диаграммы
выбрать Тип диаграммы, для данного примера Круговая или Лепестковая. При создании сводных диаграмм не используются Точечная, Пузырьковая и Биржевая типы диаграмм.