Математическая модель решения задачи

Для получения ведомости «Фактическое оказание услуг» необ

ходимо рассчитать такие показатели, как:

1) стоимость услуг, полученных каждым клиентом за месяц;

2) общая стоимость услуг, полученных всеми клиентами за ме

сяц;

3) стоимость услуг по клиентам и датам;

4) стоимость услуг по их кодам.

Расчеты выполняются по следующим формулам:

Ci = å å Sijd , C = åCi ,

d j i

где Сi – стоимость услуг, полученных i м клиентом;

S ijd – стоимость оказания i й услуги фактическая, полученной j м

клиентом, датой d;

C – общая стоимость услуг, оказанных всем клиентам.

Показатели, определяющие содержание результирующих доку

ментов «Фактическое оказание услуг по клиентам и датам» и «Фак

тическое оказание услуг по их кодам», аналитического представле

ния не требуют, так как их можно получить с помощью фильтров,

указав нужные коды (см. технологию решения задачи).

Технология решения задачи в среде MS Excel

1. Вызовите MS Excel:

·нажмите кнопкуПуск;

·выберите в главном меню командуПрограммы;

·в меню Microsoft Office выберите MS Excel.

2. ПереименуйтеЛист 1 вСправочник клиентов:

·установите курсор на ярлыкЛист 1 (нижняя часть экрана) и

нажмите правую кнопку мыши;

·выберите в контекстном меню командуПереименовать и на

жмите левую кнопку мыши;

·наберите на клавиатуреСправочник клиентов;

·нажмитеEnter.

3. Введите заголовок таблицыСправочник клиентов:

·сделайте ячейкуA1 активной (установите курсор на пересече

нии столбца A и строки 1 и нажмите левую кнопку мыши);

·наберите на клавиатуреСправочник клиентов;

·нажмите кнопку

в строке формул (установите курсор

на эту копку и нажмите левую кнопку мыши).

4. Отформатируйте заголовок:

·выделите ячейкиA1 ¸D1 (сделайте активной ячейкуA1, затем

нажмите левую кнопку мыши и, удерживая ее, переместите курсор

на ячейкуD1) (рис. 1)1.

Рис. 1.Пример выделения группы ячеек

·на панели инструментов в закладкеГлавная выберите раздел

Выравнивание, нажмите кнопку

.

5. Отформатируйте ячейкиA2 ¸C2 под ввод длинных заголовков:

·выделите ячейкиA2 ¸C2;

·выполните командуВыравнивание в разделеФормат ячеек

менюГлавная на панели инструментов;

·выберите закладкуВыравнивание;

·в группе опцийОтображение установите флажок в опциипе

реносить по словам (рис. 2);

Рис. 2.Задание переноса слов при вводе в ячейку

Длинных предложений

Здесь и далее в описании инструкции использована собственная нумерация

рисунков.

·нажмитеOK.

6. Введите в ячейкиA2 ¸D2 информацию, представленную на

рис. 3.

Рис. 3.Имена полей таблицы «Справочник клиентов»

7. Отформатируйте ячейкиD3 ¸D7 для ввода текстовых симво

лов:

·выделите ячейкиD3 ¸D7;

·на панели инструментов в менюГлавная выберитеЯчейки,

в пунктеФормат выполните командуФормат ячеек;

·выберите закладкуЧисло;

·выберите форматТекстовый (рис. 4);

Рис. 4.Выбор формата ячеек

·нажмитеOK.

8. Введите информацию, представленную в таблице.

Таблица

Справочник клиентов

9. Присвойте имя группе ячеек:

·выделите ячейкиA3 ¸D7;

·выберите командуПрисвоить имя в разделеОпределенные

имена менюФормулы (рис. 5);

Рис. 5.Вид окна «Создание имени»

·нажмитеOK.

10. ПереименуйтеЛист 2 вВедомость учета оказанных услуг

салона связи (аналогично действиям п. 2).

11. Создайте таблицуВедомость учета оказанных услуг сало

на связи (аналогично действиям п. 3–5) (рис. 6).

Рис. 6.Вид таблицы «Ведомость учета оказанных услуг

Салона связи»

12. Введите исходные данные.

13. Заполните графуНаименование клиента:

·сделайте ячейкуВ3 активной;

·воспользуйтесь командойВставить функцию менюФормулы;

·в полеКатегория выберитеСсылки и массивы;

·в полеВыберите функцию нажмитеВПР (рис. 7);

Рис. 7.Вид первого окна Мастера функций

·нажмитеOK;

·введите в полеИскомое_значение, щелкнув по ячейкеА3;

·нажмитеEnter;

·введите информацию в полеТаблица;

·воспользуйтесь командойИспользовать в формуле меню

Формулы, выбравВставить имена;

·в окнеВставка имени выделитеИмя: Код клиента (рис. 8);

Рис. 8.Ввод имени массива в качестве аргумента формулы

·нажмитеOK;

·нажмитеEnter;

·введите информацию – цифру 2 в полеНомер_столбца;

·введите информацию – цифру 0 в полеИнтервальный_про

смотр (рис. 9);

Рис. 9.Вид второго окна Мастера функций

·нажмитеOK;

·установите курсор на маркер в правом нижнем углу ячейкиВ3,

щелкните левой клавишей мыши и протяните его до ячейкиВ14.

Заполненная таблица выглядит так, как это показано на рис. 10.

Рис. 10.Результат заполнения таблицы

«Ведомость учета оказанных услуг салона связи»

14. Создайте сводную таблицуФактическое оказание услуг:

·установите курсор в поле таблицыВедомость учета оказан

ных услуг салона связи;

·воспользуйтесь командойСводная таблица из менюВставка;

·в окнеСоздание сводной таблицы (MS Office 2010) нажмите

OK (рис. 11).

Рис. 11.Создание сводной таблицы

Чтобы вставить поле в сводную таблицу, его необходимо перета

щить в одну из четырех областей:Фильтр отчета,Названия столб

цов,Названия строк и åЗначения:

·перенесите в полеФильтр отчета надписьКод услуги (для

этого поставьте курсор на полеКод услуги, нажмите левую клави

шу мыши и, удерживая ее, перенесите в полеФильтр отчета);

·перенесите в полеНазвания строк надписьНаименование

клиента;

·перенесите в поле åЗначения надписьСумма по счетам за

оказанные услуги;

·в результате получится сводная таблица (рис. 12);

Рис. 12.Фрагмент листа «Фактическое оказание услуг»

·переименуйте лист со сводной таблицей вФактическое ока

зание услуг.

15. Создайте ведомостьФактическое оказание услуг по кли

ентам и датам:

·установите курсор в поле таблицыВедомость учета оказан

ных услуг салона связи;

·воспользуйтесь командойСводная таблица из менюВставка;

·перенесите в полеНазвания строк надписьНаименование

клиента;

·перенесите в полеНазвания строк надписьДата оказания

услуги;

·перенесите в полеФильтр отчета надписьКод клиента;

·перенесите в поле åЗначения надписьСумма по счетам за

оказанные услуги;

·переименуйте лист со сводной таблицей вФактическое ока

зание услуг по клиентам и датам (рис. 13).

Рис. 13.Фактическое оказание услуг по клиентам и датам

(сводная таблица)

Фильтрация и группирование данных. Поле сводной таблицы

играет роль фильтра данных. Каждое поле имеет командуВсе, ис

пользуемую для отображения всех возможных значений поля.

Используя сводную таблицуФактическое оказание услуг, по

лучите данные для кода услуги 10005:

·сделайте активным листФактическое оказание услуг;

·нажмите кнопку правее поляКод услуги;

·выберите код услуги –10005 (рис. 14).

Рис. 14.Фильтрация данных по полю «Код услуги»

·нажмитеOK.

Фактическое оказание услуг по коду10005 представлено в виде

сводной таблицы (рис. 15).

Рис. 15.Фактическое оказание услуг по коду «10005»

16. Представьте наглядно результаты расчетов, создав диаграм

му по данным сводной таблицы:

·сделайте активным листФактическое оказание услуг;

·выделите диапазонA8 ¸B8;

·выберите командуГистограмма в разделеДиаграммы меню

Вставка;

·в разделеОбъемная гистограмма выберитеОбъемная гисто

грамма с группировкой;

·переименуйте гистограмму вФактическое оказание услуг

(рис. 16).

Фактическое оказание услуг

Рис. 16.Гистограмма «Фактическое оказание услуг»

Заключение

Таким образом, формирование сводных таблиц на основе «Ве

домости учета оказания услуг салона связи» позволяет решить по

ставленные задачи:

1) отслеживать соблюдение графика и объемов оказания услуг;

2) контролировать своевременность погашения и уменьшение

объема дебиторской задолженности.

Создание различных диаграмм (гистограмм, графиков) на осно

ве данных сводных таблиц средствами MS Excel позволяет не толь

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

проведения анализа с целью принятия решений, но и достаточно

быстро осуществлять манипуляции в области их построения

в пользу наиболее удобного представления результатов визуализа

ции по задаваемым пользователем (аналитиком) параметрам.

Варианты заданий контрольной работы

Предложенные экономические задачи решите с использованием

табличного процессора MS Excel. Отсутствующие числовые значе

ния данных и единицы их измерения задайте самостоятельно.

Вариант 1

Предприятие ООО «Энергос» осуществляет деятельность по

обеспечению физических и юридических лиц электроэнергией и про

изводит расчеты по предоставленным услугам. Данные, на основе

которых производятся расчеты по оплате, представлены в табл. 1.

?

1. Постройте табл. 1 в MS Excel.

2. Рассчитайте расход электроэнергии за месяц и сумму к оплате

(табл. 2). Результаты вычислений представьте в виде таблицы MS

Excel и в графическом виде.

3. Организуйте межтабличные связи для автоматического фор

мирования документа «Квитанция об оплате электроэнергии», ис

пользуя функцию ВПР или ПРОСМОТР.

4. Сформируйте и заполните квитанцию об оплате электро

энергии (табл. 3).

5. Результаты вычислений представьте в графическом виде, про

ведите их анализ.

Таблица 1

Показания электросчетчиков

ìåñÿö: äåêàáðü 2012

Таблица 2