Работа в Ecxel

от числа экзаменов) в которых находится список студентов  по каждому отдельному экзамену с  поля-

ми Номер по порядку, Фамилия Имя Отчество, Номер зачетной книжки, Оценка, Подпись экзамена-

тора, Дата (рис. 30); 2) один общий лист Ведомость с  полями Номер по порядку, Фамилия  Имя Отче-

ство, Количество сданных экзаменов, Средний балл, Стипендия, Премия, Итого к выдаче (рис. 31).

        На каждом листе с названием  экзамена подсчитайте количество  пятерок, четверок, троек и

двоек по данному  экзамену. Для определенности допустим, что рассматриваемая Вами группа сдава-

ла четыре экзамена. В листе Ведомость средний балл вычисляется по результатам сдачи экзаменов

по каждому студенту. Учтите, что средний балл будет  рассчитываться только в том случае, если сту-

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

        При подсчете Стипендии следует  учесть следующие условия:

 • если средний  балл не менее 4,6, выплачивается  30% надбавка к минимальной стипендии;

 • если средний  балл от 3,6 (включительно) до 4,6 –  выплачивается минимальная стипендия;

 • если средний  балл меньше 3,6 – стипендия не  выплачивается.

        Значение с минимальным размером  стипендии помещается в отдельную  ячейку. При измене-

нии минимального размера перерасчет значений в поле Стипендия должен происходить автоматиче-

ски. Премия в размере 20% от минимального размера стипендии дается студентам только в том слу-

чае, если сданы все экзамены на пятерки. Подсчитайте сумму общего стипендиального фонда для

группы.

 

 

 

                                               9

 

 

                   Рис. 30. Форма экзаменационной ведомости  по математике

 

                                        Тема № 7

                     Решение задачи «Продажа товаров  на оптовой базе»

                       с использованием функций ЭТ  Microsoft Excel

 

ЗАДАНИЕ.

          Разработайте автоматизированную  учетную документацию продажи  товара на оптовой ба-

зе «Финист» с учетом скидок для постоянных клиентов. Исходные данные расположите на двух лис-

тах: лист Товары (рис. 34) и лист Клиенты (рис. 35). Расчетным будет являться лист Заказы (рис.

36).

 

                                         Тема № 8

                      Использование функции СУММЕСЛИ  для подсчета

                     значений с нарастающим итогом  в ЭТ Microsoft Excel

 

ЗАДАНИЕ.

   Используя данные Справочника клиентов (Код клиента, Клиент) и Ценника (Код товара, На-

именование товара, Цена за единицу), создайте автоматизированную форму (Январь) для ежемесяч-

ного учета продаж мелкооптового магазина «Стройматериалы» следующей структуры: Номер доку-

мента, Дата заказа, Код клиента, Клиент, Код товара, Наименование товара, Цена за единицу, Коли-

чество, Сумма покупки, Сумма покупок клиента с начала месяца. Следует учесть, что не подчеркну-

тые столбцы вводятся с клавиатуры, а подчеркнутые вычисляются по формулам.

 

ЗАДАНИЕ.

  Самостоятельно подсчитайте, на какую сумму с начала месяца покупается каждый товар (добавьте

еще один столбец  и назовите его Сумма продаж товара с начала месяца).

 

ЗАДАНИЕ.

   Самостоятельно, используя данные Справочника  клиентов и Ценника, создайте  автоматизиро-

ванную форму  для ежемесячного учета продаж торгово-закупочной базы «Парадокс» следующей

структуры: Номер документа; Дата заказа; Код клиента; Клиент; № товара; Наименование товара;

Цена за единицу; Количество; Сумма покупки; Сумма  покупок клиента с начала месяца; Скидка

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

(!!!); Сумма покупок  клиента с учетом скидки. Обратите  внимание, что в течение месяца  на фирме

действует система  скидок: 3%, если сумма покупок клиента  с начала месяца больше 10000 рублей;

5%, если сумма  покупок клиента с начала месяца  больше 50000 рублей.

 

                                        Тема № 9

                    Организация базы данных «Заказы»  с использованием

                    функций списка «сортировка»  и «фильтрация» в Excel

 

       Электронную таблицу Microsoft Excel можно использовать в качестве базы данных, в которой

строки будут  соответствовать записям в базе данных, а столбцы – полям базы данных. С такой базой

данных в электронной  таблице можно производить различные  преобразования, которые организова-

ны и в системе управления базой данных, например, сортировка, фильтрация и др. Рассмотрим на

примере указанные возможности электронной таблицы Microsoft Excel.

 

ЗАДАНИЕ.

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

торгового центра «Виола»  по продаже продуктов питания. Предусмотрите  возможность печати дан-

ных для каждого отдельного заказа. Проанализируйте данные полученного списка.

 

ЗАДАНИЕ.

           Составьте табличную базу данных  «Успеваемость студентов за неделю»  по форме, изобра-

женной на рис. 50. Задайте структуру и получите итоги по трем уровням: 1-го уровня – средний балл

по каждой учебной  группе; 2-го уровня – средний балл по каждому предмету по всем учебным  груп-

пам; 3-го уровня – средний балл по каждому виду занятий определенных предметов по всем учебным

группам.

 

 

 

 

                         Рис. 50. Форма листа Успеваемость  студентов

 

 

 

 

                                               11

 

 

ЗАДАНИЕ.

          Самостоятельно в таблице «Успеваемость  студентов» создайте новые промежуточные ито-

ги вида: на первом уровне – по учебному предмету, на втором уровне – по виду занятия, на третьем

уровне – по номеру учебной группы.

 

ЗАДАНИЕ.

         Самостоятельно создайте промежуточные  итоги для листа Заказы покупателей  из задания

Темы 9, куда включите итоги следующего вида: на первом уровне – по коду продукта вывести итоги

в столбцах Сумма  заказа и Уплачено, на втором уровне – по коду продукта вывести количество

одинаковых названий продукта. Обратите внимание, что при  сортировке таблицы по любому полю

столбец Номер заказа теряет свою автоматизацию, поэтому  его целесообразно скрыть.

 

 

 

 

                    Рис. 52. Фрагмент окна таблицы  после подведения итогов

                                          Тема № 11

                          Анализ базы данных «Заказы  покупателей»

                     с помощью Мастера сводных  таблиц в Microsoft Excel

 

       В электронной таблице Microsoft Excel предусмотрена возможность создания сводной табли-

цы из исходной базы данных. Это упрощает работу пользователей с большими информационными

потоками, придавая им вид упорядоченных данных. Команда  Сводная таблица для создания свод-

ной таблицы находится в меню Данные. Рассмотрим на конкретном примере технологию построения

сводной таблицы.

 

ЗАДАНИЕ.

          Проведите анализ данных списка  Заказы покупателей из Темы 9 с  помощью сводной

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

 

ЗАДАНИЕ.

          Самостоятельно на новом рабочем  листе создайте сводную таблицу,  в которой бы отража-

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

          Самостоятельно на новых рабочих  листах создайте сводные таблицы,  в которых отражают-

ся детальные сведения о:

                                              12

 

 

         а) покупках конкретной фирмой продуктов за определенный месяц;

         б) наибольшей уплаченной сумме  по каждому отдельному продукту  за каждый конкретный

месяц.

 

 

 

 

                         Рис. 57. Сводная таблица по Заказам  покупателя

 

                                          Тема № 12

                                     Консолидация данных

                             в электронной таблице Microsoft Excel

 

       При обработке большого объема  табличной информации часто возникает  потребность в объе-

динении (консолидации) данных из нескольких однотипных таблиц в одну итоговую таблицу. Такая

возможность имеется  в электронной таблице Microsoft Excel. Запускается процедура консолидации

через меню Данные команда Консолидация. Рассмотрим на практическом примере технологию реа-

лизации данной функции.

 

ЗАДАНИЕ.

          Проанализируйте данные списка  Заказы покупателей с помощью  процедуры консолида-

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

всего способствовал  росту объема продаж во всех вместе взятых филиалах.

 

ЗАДАНИЕ.

          Самостоятельно определите доли  Объема продаж. Для этого добавьте  столбец Доля про-

даж в конце таблицы Консолидация. Заполните этот столбец соответствующей формулой.

 

ЗАДАНИЕ.

         Проанализируйте объем продаж  по филиалам из предыдущего  задания с помощью сводной

таблицы, источники  данных для которой находятся  в нескольких диапазонах консолидации. Просле-

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

 

 

 

 

                                              13

 

 

                                         Тема № 13

                             Графическое представление данных

                            в электронной таблице Microsoft Excel

 

       Электронная таблица Microsoft Excel представляет собой не только мощное приложение для

обработки табличной  информации, но и содержит в себе дополнительные возможности по графиче-

скому представлению этих табличных данных. Для этого в электронную таблицу встроен Мастер

диаграмм, работу которого мы рассмотрим на примерах.

 

ЗАДАНИЕ.

          Проведите анализ данных задачи  из Темы 12 по объему продаж  различных продуктов в

разрезе месяцев с помощью диаграммы.

 

ЗАДАНИЕ.

           Самостоятельно отобразите графически  с помощью создания диаграммы  доли отдельных

продуктов в общем  объеме продаж, используя данные листа  Заказы покупателей. Подготовьте чи-

словой материал для составления диаграммы, создав соответствующую сводную таблицу (рис. 67). А

затем, на основе этой сводной таблицы постройте диаграмму, показанную на рис. 68.

 

 

 

 

                            Рис. 67. Сводная таблица с долей  в итоге

 

ПРИМЕЧАНИЕ

♦ Для изменения  операции над итоговыми значениями достаточно осуществить двойной  щелчок

мыши на кнопке сводной  таблицы Сумма по полю уплачено. В открывшемся окне Вычисление по-

ля сводной таблицы  нажмите кнопку Дополнительно. Затем  в поле Дополнительные вычисления

установите параметр (для условия нашей задачи) Доля от общей суммы. Нажмите ОК.

 

ЗАДАНИЕ.

          Самостоятельно постройте график  для сравнения учета доходов  и расходов фирмы «Быт»

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

(за год).

        Данные о доходах и расходах  от выполненных услуг представлены  на рис. 69.

        Примерное оформление графика  показано на рис. 70.

 

 

 

 

                                              14

 

 

    Рис. 68. Объемный  вариант круговой диаграммы

 

 

 

 

Рис. 69. Табличные  данные расходов и доходов фирмы  Быт

 

 

 

 

    Рис. 70. График  доходов и расходов фирмы Быт

 

 

 

                         15

 

 

                                        Тема № 14

                         Программная надстройка «Поиск  решения»

                           в электронной таблице Microsoft Excel

 

       Программная надстройка Поиск  решения – это встроенная в  Microsoft Excel 2003 подпро-

грамма, которая облегчает пользователю нахождение оптимального решения поставленной задачи

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

решения задачи, необходимо подготовить в понятном компьютеру виде начальные данные, т.е. пред-

варительно поставить и формализовать задачу, установив исходные числовые значения. Как правило,

в электронной таблице  основой для формализации задачи служит созданная пользователем  таблица, в

которой указан весь необходимый числовой материал. При этом таблица должна содержать форму-

лы, отражающие зависимости между определенными данными таблицы. После создания таблицы с

достаточными параметрами  можно активировать программу Поиск  решения. Полученные опти-

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

 

ЗАДАНИЕ.

          Проведя маркетинговые исследования, Вы пришли к выводу о наличии  высокого спроса на

муку. Кроме того, имеется возможность приобретать  зерно по относительно невысокой  цене. Поэто-