1 Круговая

2 Линейчатая

3 Гистограмма

4 График

5 Точечная

Соответствия

Типы сравнения:

1 Покомпонентное

2 Позиционное

3 Временное

4 Частотное

5 Корреляционное

Алгоритм выбора типа диаграммы:

1) Сформулировать идею диаграммы

2) Выбрать тип сравнения

3) Выбрать тип диаграммы

Элементы диаграммы

Область диаграммы

Заголовок

Область построения

Подписи данных

Ряд данных

Легенда

Ось категорий

Ось значений

Работа с диаграммами в MSExcel

Набор средств Работа с диаграммами :

1) Конструктор

2) Макет

3) Формат

ГРАФИЧЕСКОЕ ПРЕДСТАВЛЕНИЕ СТАТИСТИЧЕСКИХ ДАННЫХ, метод наглядного изображения и обобщения данных о социально-экономических явлениях посредством геометрических образов, рисунков или схематических географических карт и пояснительных надписей к ним. Графическое представление статистических данных отчётливо и наглядно отображает взаимосвязь между явлениями и процессами общественной жизни, основные тенденции их развития, степень их распространения в пространстве; позволяет увидеть как всю совокупность явлений в целом, так и отдельные его части.

Для графического представления статистических данных используются разнообразные виды статистических графиков. Каждый график состоит из графического образа и вспомогательных элементов. К ним относятся: экспликация графика, пространственные ориентиры, масштабные ориентиры, поле графика. Вспомогательные элементы делают возможным чтение графика, его понимание и использование. Графики можно классифицировать по ряду признаков: в зависимости от формы графического образа они могут быть точечными, линейными, плоскостными, пространственными и фигурными. По способу построения графики делятся на диаграммы и статистические карты.

Наиболее распространённый способ графических изображений - диаграмма. Это чертёж, на котором статистические данные представлены как геометрические фигуры или знаки, а территория, к которой относятся эти данные, указана только словесно. Если диаграмма наложена на географическую карту или на план территории, к которой относятся статистические данные, то график называется картодиаграммой. Если же статистические данные изображены путём штриховки или раскраски соответствующей территории на географической карте или плане, то график называется картограммой.

Для сравнения одноимённых статистических данных, характеризующих разные объекты или территории, могут быть использованы различные виды диаграмм. Наиболее наглядны столбиковые диаграммы, на которых статистические данные изображаются в виде вытянутых по вертикали прямоугольников. Их наглядность достигается сравнением высоты столбиков (рис. 1).

Если базовая линия расположена вертикально, а столбики горизонтально, то диаграмма называется полосовой (ленточной). На рисунке 2 приведена полосовая диаграмма сравнения, характеризующая территорию земного шара.

Диаграммы, предназначенные для популяризации, иногда строятся в виде стандартных фигур - рисунков, характерных для изображаемых статистических данных, что делает диаграмму более выразительной, привлекает к ней внимание. Такие диаграммы называются фигурными или изобразительными (рис. 3).

Большую группу показательных графиков составляют структурные диаграммы. Метод графического изображения структуры статистических данных заключается в составлении структурных круговых или секторных диаграмм (рис. 4).

Для изображения и анализа развития явлений во времени строятся диаграммы динамики: столбиковые, ленточные, квадратные, круговые, линейные, радиальные и др. Выбор вида диаграммы зависит от особенностей исходных данных, цели исследования. Например, если имеется ряд динамики с несколько неравноотстоящими уровнями во времени (1913, 1940, 1950, 1980, 2000, 2005), то используют столбиковые, квадратные или круговые диаграммы. Они зрительно впечатляют, хорошо запоминаются, но не пригодны для изображения большого числа уровней. Если число уровней в ряду динамики велико, то применяются линейные диаграммы, которые воспроизводят процесс развития в виде непрерывной ломаной линии (рис. 5).

Нередко на одном линейном графике приводится несколько кривых, дающих сравнительную характеристику динамики различных показателей или одного и того же показателя в разных странах (рис. 6).

Для отображения зависимости одного показателя от другого строится диаграмма взаимосвязи. Один показатель принимается за Х, а другой за Y (т. е. функцию от Х). Строится прямоугольная система координат с масштабами для показателей, и в ней вычерчивается график (рис. 7).

Развитие вычислительной техники и прикладного программного обеспечения сделало возможным создание географических информационных систем (ГИС), представляющих качественно новый этап в графическом представлении информации. ГИС обеспечивают сбор, хранение, обработку, доступ, отображение и распространение пространственно-координированных данных; включают большое количество графических и тематических баз данных в соединении с модельными и расчётными функциями, позволяющими представлять информацию в пространственном (картографическом) виде, получать в различном масштабе многослойные электронные карты региона. По территориальному охвату различают глобальные, субконтинентальные, государственные, региональные и локальные виды ГИС. Предметная ориентация ГИС определяется решаемыми с её помощью задачами, среди которых могут быть инвентаризация ресурсов, анализ, оценка, мониторинг, управление и планирование.

Лит.: Герчук Я. П. Графические методы в статистике. М., 1968; Теория статистики / Под редакцией Р. А. Шмойловой. 4-е изд. М., 2005. С. 150-83.

Занятие 3 . Наглядное представление данных электронного журнала .

1. Построение диаграммы текущей успеваемости учащихся............................. 1

............................................................................ 1

........................................................................ 2

......................................................... 7

2. Выбор диапазона ячеек журнала для наглядного представления итоговой успеваемости учащихся........................................................................................................................... 8

3. Использование данных успеваемости за длительный период для подготовки заключений относительно тенденций изменения результатов на перспективу................... 9

4. Практические задания к занятию......................................................................... 11

5. Вопросы на понимание материала занятия................................................... 12

1. Построение диаграммы текущей успеваемости учащихся.

Как Вы уже убедились на материале прошлого занятия, работа с электронным журналом позволяет следить за процессом обучения и оперативно подводить итоги. Однако, как ни интересна динамическая таблица (журнал) успеваемости для презентации или выразительного отчетного документа красочные диаграммы являются наилучшим способом представления чисел и демонстрации связи между ними. Редактор электронных таблиц Excel позволит Вам быстро и эффективно решить различные задачи, связанные с наглядным представлением Ваших данных.

¨ Откройте созданный Вами ранее электронный журнал.

Процедура создания диаграмм в Excel элементарна. Воспользуемся встроенной в редактор программой «Мастер диаграмм».

1.1. Выделение диапазона ячеек.

¨ Для начала необходимо выделить тот диапазон ячеек, который Вы предполагаете представить в виде диаграммы или графика.

¨

Предлагаю выделить ячейки, отображающие текущую успеваемость Ваших учеников (см. рис. 1).

Рис. 1. Выделение диапазона ячеек для построения диаграммы или графика.

¨ Так мы указываем Мастеру диаграмм источник данных для построения графика.

1.2. Работа с мастером диаграмм

¨ Затем найдите на панели инструментов «Стандартная» кнопку с изображением стилизованной диаграммы и щелкните по ней.

Рис. 2. Кнопка Мастера диаграмм.

¨ В диалоговом окне запускается Мастер диаграмм, с которым мы начинаем работать. Нам предстоит сделать четыре шага до конечного результата (см. рис.3).

Рис. 3. Вид диалогового окна Мастера диаграмм.

¨ На первом шаге мы выбираем тип диаграммы. Нашим целям отображения развития процесса во времени (в нашем случае – текущей успеваемости) хорошо отвечает График. Его мы и выбираем из многочисленных возможностей.

¨ Обращаю Ваше внимание на то, что при выборе того или иного типа необходимо обращать внимание на комментарии в нижней правой части окна, так как они описывают выбранный график.

¨ На этом же этапе можно воспользоваться кнопкой «Просмотр результата». Нажав указанную кнопку, в окне Образца в правой части диалогового окна (после нажатия кнопки Просмотра окно Вида меняется на окно Образца) можно посмотреть, как будут выглядеть отмеченные для наглядного представления данные при использовании того или иного типа диаграммы (см. рис. 4).

Рис. 4. Вид диалогового окна Мастера диаграмм после нажатия кнопки «Предварительный просмотр».

¨

После нажатия на кнопку «Далее» переходим ко второму шагу работы с Мастером диаграмм, на котором определяется источник данных для построения графика. Но мы это уже сделали, выделив диапазон ячеек текущей успеваемости, перед запуском Мастера. Поэтому окно «Диапазон» оказалось у нас уже заполненным (см. рис. 5).

Рис. 5. Вид диалогового окна Мастера диаграмм на втором шаге.

¨ Здесь необходимо проверить правильность выбора рядов нашего графика: располагаются ли ряды в строках или в столбцах. Заметим, что поскольку нас интересует успеваемость каждого ученика за исследуемый период, то необходимо поменять выбор на «Строки». В противном случае графики будут отражать данные в перевернутом для обычного представления таких данных формате.

¨ Кроме того, следует обратить внимание на то обстоятельство, что ячейки с заголовками столбцов таблицы (Фамилия, имя и даты проведения уроков) также были восприняты Мастером диаграмм как элементы, которые необходимо учитывать при построении графиков.

¨

Для того чтобы исключить те ряды или строки, которые не должны отражаться на графиках, можно, активизировав закладку «Ряд» Диалогового окна Мастера диаграмм для шага 2, удалить ненужные данные.

Рис. 6. Кнопка «Удалить» для исключения из построения ненужных данных.

¨ После удаления ненужных рядов необходимо заполнить окно «Подписи оси Х». Для этого проверяем, что в указанном окне находится курсор, и в основной таблице (журнале) выделяем диапазон ячеек с датами проведения уроков (см. рис. 7).

Рис. 7. Заполнение окна «Подписи оси Х».

¨

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

Рис. 8. Вид окна Мастера диаграмм на шаге 2 после заполнения окна «Подписи оси Х».

¨
Теперь можно нажать кнопку «Далее» и перейти к третьему шагу работы Мастера диаграмм.

Рис. 9. Вид диалогового окна «Мастера диаграмм» на шаге 3.

¨ На этом шаге Вам предлагается заполнить окна с названиями диаграммы, осей Х и Y. Здесь же можно поработать с различными закладками окна для получения желаемого вида диаграммы. Для ввода названия и подписей осей нужно щелкнуть в соответствующем поле (там появится текстовый курсор) и набрать на клавиатуре необходимый текст. Для настройки легенды (условных обозначений) нужно щелкнуть по одноименной закладке и настроить положение легенды, выбрав мышкой нужную позицию в левой части окна. В процессе настройки в правой части окна уже можно посмотреть на прообраз графика. В случае если Вас что-то не устраивает, можно вернуться к предыдущим этапам, нажав на кнопку «Назад».

Рис. 9. Вид диалогового окна Мастера диаграмм на шаге 3.

¨ На 4-ом, заключительном шаге необходимо указать, как расположить график: на отдельном листе или же на листе, где расположен сам журнал.

Рис. 10. Вид диалогового окна Мастера диаграмм на шаге 4.

¨ После выбора нажимаем кнопку «Готово».

На представленном ниже рис.11 можно видеть результат работы Мастера диаграмм. Мастер выполнил все наши пожелания, но, возможно, результат Вас удовлетворяет не полностью.

Рис. 11. Вид готовой диаграммы после окончания работы Мастера диаграмм.

1.3. Изменение вида объектов диаграммы.

Теперь поговорим о том, как можно изменить параметры готовой диаграммы.

¨ Имея уже готовую диаграмму, Вы можете легко изменить те ее параметры, которые Вас не устраивают. Для этого выделите область диаграммы щелчком мыши.

¨ Теперь обратите внимание на изменение горизонтального меню: там вместо элемента Данные появился элемент Диаграмма, в ниспадающем меню которого Вы сможете найти команды, которые позволят Вам изменить при желании тип диаграммы, добавить или удалить исходные данные, изменить параметры диаграммы и ее размещение.

¨

Кроме того, выделяя отдельные элементы диаграммы, Вы можете изменять их формат. На рис. 12 представлена диаграмма после изменения форматов некоторых ее объектов.

Рис. 12. Вид диаграммы после изменения форматов некоторых объектов.

Я не призываю Вас в точности следовать решениям, приведенным на рисунке выше. Эта пестрота нужна мне для того, чтобы продемонстрировать эффекты изменения форматов объектов. Жду от Вас более изысканных решений.

2. Выбор диапазона ячеек журнала для наглядного представления итоговой успеваемости учащихся.

Теперь попробуем построить диаграмму, соответствующую итоговым результатам обучения. В этом случае нам придется использовать для построения несмежные диапазоны данных (столбик с фамилиями учащихся и столбик с их итоговым отметками).

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

¨ Выделите сначала диапазон ячеек, в которых содержатся фамилии и имена учеников, затем нажмите клавиш у Ctrl и проведите выделение столбца с итоговыми результатами (см. рис. 13).

Рис. 13. Вид таблицы после выделения несмежных столбцов.

¨

В результате уже известных Вам действий мы получим диаграмму рейтинга учащихся по данному предмету на конец четверти (см. рис. 14).

Рис. 14. Диаграмма, построенная с использованием данных в несмежных столбцах.

NB! Обращаю Ваше внимание на то, что до сих пор наши Диаграммы меняются синхронно с вносимыми в журнал изменениями.

¨ Проверьте это, внеся в свои журнальные данные изменения и переходя к графику.

¨ Чтобы зафиксировать диаграмму в том состоянии, в котором она находится в данный момент, отказавшись от дальнейшего ее обновления в соответствии с изменениями исходных данных, необходимо:

q выделить диаграмму,

q выделить ряд данных на диаграмме,

q щелкнуть мышью в строке формул и нажать клавишу F 9,

q повторить два предыдущих шага для всех рядов данных, представленных на диаграмме.

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

3. Использование данных успеваемости за длительный период для подготовки заключений относительно тенденций изменения результатов на перспективу

¨ Работая с диаграммами Вы, наверняка, обратили внимание на команду Добавить линию тренда в выплывающем меню элемента Диаграмма или в контекстном меню при щелчке на ряде данных диаграммы. Эта команда предоставляет Вам возможность прогнозировать поведение выбранной величины на основании исходных данных.

¨ В случае, когда идет дело о принятии управленческих решений, наглядное представление таких данных поможет Вам его принять и быть при этом весьма убедительными, если дело потребует коллективного обсуждения этого решения.

¨ Предлагаю Вашему вниманию таблицу с данными по средней оценке успеваемости класса по предмету за несколько отчетных периодов.

Рис.15. Таблица, в которую включены значения за прошедший период на основании данных журнала.

¨ Если мы хотим получить продолжение таблицы, используя возможности прогнозирования, то для того, чтобы сохранить после пересчета исходные данные, нужно скопировать их в следующий столбец, который и будем подвергать изменению.

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

Рис. 16. Выделение диапазона ячеек для построения прогноза.

¨ Теперь в элементе меню Правка находим команду Заполнить è Прогрессия. В окне Прогрессия активизируем опцию Автоматическое определение шага, и результат не заставит себя ждать.

¨

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

Рис. 17. Окно линии тренда при выборе линейной линии тренда.

¨ При работе с закладкой данного окна Параметры необходимо указать диапазон, на который осуществляется прогноз. Здесь же Вы можете активизировать опцию показа уравнения на диаграмме и т. д.

Рис. 18. Закладка Параметры окна Линия тренда.

¨ В результате у нас получится достаточно убедительный наглядный материал. Если на графике исследуемая величина имеет нужную тенденцию, значит, работа в данном направлении идет успешно. Если же полученная тенденция не совпадает с Вашими ожиданиями, то следует что-то менять в налаженном ритме работы.

4. Практические задания к занятию

Задание 1 .

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

Ø График представьте на текущем листе, т. е. на листе журнала с таблицей успеваемости.

Ø Измените, пользуясь «хэндлерами»,(активными точками на границе зоны выделения) размер области диаграммы так, чтобы графики для каждого ученика отчетливо читались.

Задание 2 .

Ø Проведите изменение шрифтов и их размеров в названии диаграммы и подписей осей.

Ø Измените фон области диаграммы на фон другого цвета с градиентной заливкой.

Ø Измените положение Легенды и ее фон.

Ø Примените другие цвета для отображения графиков текущей успеваемости тех учеников, чьи данные на диаграмме изображены слишком бледными, по Вашему мнению, цветами, примененными Мастером диаграмм автоматически.

Ø Измените тип и толщину используемых для построения линий графиков.

Ø Попробуйте изменить вид и цвет маркеров на графиках.

Ø Полученную диаграмму разместите на отдельном листе.

Задание 3 .

Ø Постройте диаграмму итоговых результатов обучения Ваших учеников.

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

Ø Попробуйте построить диаграмму среза знаний в один из дней занятий, дата которого находится в середине периода обучения.

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

Ø Для большей наглядности воспользуйтесь возможностями изменения форматов объектов диаграмм.

Ø Полученные диаграммы разместите на отдельных листах.

Задание 4 .

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

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

Ø Ответьте на вопрос: есть ли разница в полученных диаграммах?

5. Вопросы на понимание материала занятия

1. Вид круговой диаграммы всегда привлекает внимание. Для графического представления каких данных подходит этот тип диаграммы?

2. Для прогнозирования поведения каких величин можно использовать возможности тренда?

Как построить диаграмму Ганта в Excel, автоматически проверять заявки на платеж, залатать дыры в таблицах, выгруженных из «1С»? Такие вопросы задали финансовые директора в ходе опроса, проведенного редакцией журнала. Некоторые из этих задач решаются с помощью встроенных инструментов программы, другие – за счет несложных комбинаций типовых функций.

По итогам опроса, проведенного редакцией журнала, был составлен перечень самых распространенных проблем, с которыми сталкиваются финансовые директора при работе в Excel, и для них подобраны наиболее оптимальные решения. Освоение предлагаемых приемов и техник потребует не более пяти минут, а экономия времени при составлении отчетов в Excel в дальнейшем будет существенной.

Выгрузка данных из учетной системы

Нередко источником данных для разно­образных управленческих отчетов служит информация, выгруженная из учетной сис­темы. Распространенный недостаток таких импортированных таблиц – пустые ячейки там, где должен быть указан какой-либо аналитический признак. Если в нескольких строках значение аналитики одинаково, зачастую оно указывается только в верхней строке. Подобное мешает построению сводных таблиц на основе выгруженных данных, не позволяет использовать фильтры и формулы по аналитике – одним словом, возникает целый ворох проблем.

Безусловно, при небольшом количестве таких незаполненных ячеек аналитические признаки можно копировать вручную. Если же число строк в таблице превышает несколько сотен, такой способ не годится. Да и когда данных немного, монотонные ручные операции – источник большинства ошибок. Поэтому лучше отказаться от ручного ввода и действовать по следующей схеме:

• выделяем столбец с незаполненными ячейками (диапазон A1:A12);
• оставляем в выделении только ячейки без значений. Для этого нужно нажать клавишу F5, в открывшемся диалоговом окне нажать кнопку «Выделить», далее выбрать условие «Пустые ячейки» и нажать «ОК»;
• в первую ячейку из этого диапазона (А2) вводим знак «равно» и нажимаем на клавиатуре кнопку «Вверх». В итоге получится формула, ссылающаяся на предыдущую ячейку. Но после ее появления надо нажать не Enter, а сочетание клавиш CTRL + Enter. Так формула скопируется во все выделенные ячейки;
• избавляемся от формул и оставляем только значения аналитики в столбце. Снова выделяем диапазон (A1:A12), копируем его и вставляем на то же самое место, но уже одни значения (щелкнуть правой кнопкой мыши по выделению – «Специальная вставка» – выбрать опцию «Вставить значения»).

Защита от ошибок ручного ввода

Те финансовые директора, кому постоянно приходится готовить планы или отчеты в разрезе контрагентов, не понаслышке знают, сколько хлопот доставляет отсутствие единообразия данных, введенных в разных таблицах. Например, бывает так. В ре­естре покупателей компания отражена как ООО «Ромашка», в отчете по дебиторам – «Ромашка», в платежном календаре – Romashka Ltd. Казалось бы, мелочь, и так понятно, о какой организации идет речь, но при попытке сделать сортировку по компаниям, построить сводную таблицу ничего хорошего не получится. У Excel на этот случай есть простой и удобный инструмент – проверка вводимых данных с раскрывающимся списком.

Итак, предположим, что нам крайне важно проверять правильность ввода на­именований товаров в отчете о продажах.

Начинаем с создания на любом листе списка правильных наименований товаров – образца, который Excel будет использовать для проверки вводимых данных. Дальше созданному диапазону надо дать имя для использования в дальнейшем в качестве источника данных для выпадающего списка. Для этого выделяем наш список (все ячейки, содержащие корректные наименования товаров), нажимаем сочетание клавиш CTRL + F3 и кнопку «Создать» и вводим имя диапазона без пробелов, к примеру, «Товары».

Теперь возвращаемся в отчет о продажах и выделяем ту область ячеек, куда будут вноситься названия товаров. Открываем вкладку «Данные» – «Проверка данных». В появившемся окне «Проверка вводимых значений» на вкладке «Параметры» задаем тип данных. В нашем случае – «Список» .

Пос­ле этого в появившейся строке «Источник» достаточно ввести ссылку на образцовый список наименований – «=Товары». После этого останется нажать кнопку «ОК».

Перейдя на одну из ячеек в отчете, куда будут заноситься наименования товаров, вы увидите, что рядом с ней появится стрелка. Теперь можно вводить номенклатуру вручную (Excel выдаст предупреждение, если была допущена ошибка) или выбирать из раскрывающегося списка.

Кстати, при помощи «Проверки» можно ограничивать размеры вводимых чисел («Целое» и «Действительное») и длину строки («Длина текста»), устанавливать диапазоны дат («Дата») или создать свое собственное ограничение, введя необходимую формулу («Другой»).

Наглядный отчет о движении денежных средств

Хорошей иллюстрацией к отчету о ДДС может стать каскадная диаграмма отклонений (см. рис. 1). Она характеризует динамику чистого денежного потока. Входящий остаток на январь у компании был в размере 100 млн рублей. За месяц чистый денежный поток оказался отрицательным – 10 млн рублей, а в феврале – еще –30 млн рублей.

рисунок 1. Построение каскадной диаграммы отклонений

Разобрались? Такие диаграммы очень удобны, но не входят в число стандартных. Вот как можно восполнить этот пробел:

• сначала готовим исходные данные – дополнительную таблицу, содержащую четыре столбца (см. рис. 1): дата (месяц), положительный и отрицательный чистые денежные потоки, а также вспомогательный столбец – остаток денежных средств на конец месяца (первое значение в нем – входящий остаток);
• выделяем таблицу (диапазон A1:D13) и строим на ее основе гистограмму с накоплением (на вкладке «Вставка» выбрать пункт «Гистограмма», далее – «Гистограмма с накоплением»);
• кликаем правой клавишей мышки по нижнему ряду данных диаграммы и делаем его невидимым. Для этого нужно выбрать в контекстном меню команду «Формат ряда данных» (на рис. 2). В открывшемся окне в категории «Заливка» указать «Нет заливки», в категории «Цвет линий» – «Нет линий». Остается нажать «ОК» – диаграмма готова.

рисунок 2. Выбор невидимой границы и прозрачной заливки

Актуальный курс валют в Excel

Очень часто в расчетах, выполненных с помощью Excel, нужно использовать актуальные курсы различных валют. Приведем пример из практики. Компания специализируется на торговле импортными товарами. Прайс-лист включает цену в долларах и рублях. Отпускная цена в рублях рассчитывается в Excel ежедневно по курсу Центробанка, увеличенному на 2 процента. Сам расчет не представляет труда при условии, что в таблицу ежедневно специалистом «руками» вносится актуальный курс доллара. Увы, но в компании, о которой идет речь, однажды курс не был изменен на актуальный вовремя. В результате клиенту выставили счет с неправильной ценой и этот счет был оплачен. Компания потеряла деньги. Этого не произошло бы, если бы обновление курса доверили Excel. Сделать это просто:

• нажимаем на вкладке «Данные» в группе «Получить внешние данные» кнопку «Из Веба»;
• в появившемся окне запроса (оно напоминает обычный интернет-браузер) вводим адрес сайта, с которого планируем брать информацию (например, yandex.ru или любой другой портал, публикующий актуальную информацию о курсе валют) и нажимаем «Пуск». Обратите внимание, что в Excel и на сайте должны быть одинаковыми знаки-разделители целой и дробной части числа (точка или запятая);
• как только страница загрузится, на ней появятся черно-желтые стрелки. Ими помечены области данных, пригодные для импорта в Excel (см. рис. 3). Остается кликнуть по стрелке рядом с интересующими данными и нажать кнопку «Импорт» в правом нижнем углу окна. Программа запросит, куда поместить новые данные, и загрузит их в нужные ячейки через несколько секунд;
• для настройки частоты обновления валютного курса нужно кликнуть правой кнопкой мыши по любой ячейке из вставленного диапазона. Далее в контекстном меню выбрать команду «Свойства диапазона данных» и указать наиболее приемлемый вариант – обновлять каждый раз при открытии файла или с периодичностью в несколько минут (количество минут указывается).

рисунок 3. Настройка автоматического обновления курса валют

Диаграмма Ганта в Excel

Самый популярный инструмент планирования и контроля сроков работ по тем или иным проектам – диаграмма Ганта. Но использовать специализированные решения, например MS Project, не всегда удобно. Лучше построить диаграмму Ганта в Excel. Самый простой способ – рисовать ее с помощью условного форматирования.

Сначала потребуется создать таблицу-заготовку с названиями работ, сроками их исполнения (даты начала, окончания, длительность), величиной задержек по этапам в днях и процентах (см. рис. 4).

рисунок 4. Диаграмма Ганта

На что стоит обратить внимание при подготовке этой таблицы:

• даты по горизонтали (на рис. 4 от ячейки G1 вправо по строке) зависят от сроков самого проекта. Для краткосрочных проектов, например, это один-два дня, для долгосрочных – неделя, месяц или более. Для универсальности можно указать шаг временной шкалы в отдельной ячейке, а потом ссылаться на нее при разметке дат;
• начало первого этапа (на рис. 4 ячейка B2) задается вручную;
• значение задержки по этапу определяет, через какое время пос­ле завершения предыдущего этапа начинается следующий. Она может быть как положительной (пауза между этапами), так и отрицательной (следующий этап выполняется одновременно с предыдущим). Технически это реализовано с помощью таких формул, как указано на рисунке 5.

рисунок 5. Исходная таблица для диаграммы Ганта

Теперь нужно настроить правила условного форматирования. Эта функция поможет разметить ячейки цветом, тем самым изобразить этапы проекта и их выполнение. Первое, что нужно сделать, – выделить диапазон таблицы, непосредственно предназначенный для отображения диаграммы Ганта (в примере его верхний левый угол – ячейка G2). Затем выбрать на вкладке «Главная» команду «Условное форматирование», кликнуть по пункту «Создать правило», затем «Использовать формулу для определения форматируемых ячеек». Понадобится сформулировать всего два правила – заливать, например, синим цветом интервал, где работы уже завершены, голубым – еще выполняются. Эти условия задаются с помощью формул типа:

И(G$1>=$B2;G$1

И(G$1>=$B2;G$1

где И – это логическая функция, проверяющая все заданные в аргументах (перечисленных в скобках через точку с запятой) критерии и требующая обязательного выполнения каждого из них. Например, условие G$1>=$B2 – не что иное, как проверка того, позже ли дата текущей ячейки (G1), чем дата начала работ (B2), а G$1

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

Группировка данных в Excel

Предположим, у вас на руках сводная ведомость заказов, полученных от покупателей за предыдущий год. В таблице – более 5,5 тысячи записей (строк), основные колонки: «Номер заказа», «Дата», «Наименование товара», «Артикул», «Количество», «Сумма заказа в рублях без учета НДС». Задача – срочно сгруппировать все заказы по месяцам и кварталам, подсчитать по ним промежуточные итоги.

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

На выполнение всех этих действий уйдет уйма времени. А можно справиться за две минуты, если воспользоваться «Сводными таблицами».

Порядок действий будет таким:

• выделяем все ячейки исходной таблицы. После этого на вкладке «Вставка» нажимаем кнопку «Сводная таблица». В появившемся диалоговом окне нажимаем кнопку «ОК». Сразу же на новом листе появится макет сводной таблицы, а также список ее полей (колонок ведомости заказов в нашем случае);
• мышкой перетаскиваем поле «Дата» из списка полей в область «Названия строк», поле «Сумма заказа в рублях без учета НДС» – в область «Значения»;
• задаем «правильные» имена двум колонкам сводной таблицы – «Дата» и «Сумма заказа в рублях без учета НДС» соответственно. Макет уже приобрел вид нормальной, привычной таблицы, осталось немного;
• ставим курсор на первое значение колонки «Дата», переходим на вкладку меню «Данные» и кликаем кнопку «Группировать». В появившемся диалоговом окне выбираем условие группировки «Месяцы» и «Кварталы», нажимаем кнопку «ОК». Программа автоматически создаст структуру данных в разрезе кварталов и месяцев, а также посчитает итоги (см. рис. 6).

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

Проверка заявки на платеж на соответствие бюджету

Стандартная функция «СУММЕСЛИ» может творить чудеса. К примеру, с ее помощью, прежде чем согласовать очередную заявку на платеж, можно легко и быстро проверить, приведет ли это к перерасходу по той или иной статье бюджета движения денежных средств.

Предположим, что есть бюджет движения денежных средств, составленный на месяц. В нем отдельным блоком выделены статьи, платежи по которым осуществляются на основе поступающих заявок от подразделений (оплата товаров поставщикам, аренда, транспортные расходы). А также предусмотрены графы, названия которых говорят сами за себя: «План на месяц», «Факт на текущую дату», «Сумма заявленных, но не исполненных на текущую дату платежей», «Ожидаемые отклонения от бюджета при выполнении всех заявок».

Кроме того, есть реестр платежей, формируемый из заявок на оплату, поступающих от руководителей функциональных подразделений. У ре­естра аналитика такая: дата подачи заявки, реквизиты счета, подразделение, инициировавшее платеж, а также сумма. Самое главное – для каждой заявки на платеж указывается бюджетная статья, в рамках которой будет осуществлена выплата и статус заявки – «Оплачено» или нет (в последнем случае графа не заполняется).

А теперь о том, как организовать автоматическую проверку платежных заявок на базе этих таблиц:

• добавляем в реестр платежей еще одну колонку. Она предназначена исключительно для служебных целей – для решения технических задач. В ее ячейки вводим следующую формулу:

  =F14&" "&H14,

  Где F14 – ссылка на ячейку, в которой указывается, по какой статье бюджета запланирована выплата, Н14 – ссылка на ячейку со статусом заявки (оплачено или нет), а знак & – функция, объединяющая текстовые значения. Пробел в кавычках (" ") введен в формулу для того, чтобы текст не слипался. Если в ячейке F14 указано «Оплата товаров», Н14 – «Оплачено», то выше описанная формула выдаст вот такой результат: «Оплата товаров оплачено»;

• проверяем, совпадают ли наименования бюджетных статей в реестре заявок и в бюджете движения денежных средств. Они должны быть полностью идентичны;
• в бюджете денежных средств по каждой статье расходов рассчитываем фактический расход денежных средств на текущую дату. Например, чтобы определить, сколько средств потратила компания на оплату товаров, в ячейку на пересечении строки «Оплата товаров» и графы «Факт на текущую дату» вводится формула:

  =СУММЕСЛИ("Реестр заявок на платеж"!I14:I57;"Оплата товаров оплачено";"Реестр заявок на платеж"!G14:G39),

  Где "Реестр заявок на платеж"!I14:I57 – ссылка на все ячейки технического столбца в реестре заявок. Перебирая данные из этого столбца, функция будет либо складывать суммы заявок, либо игнорировать их. «Оплата товаров оплачено» – это и есть критерий, которым будет руководствоваться программа, решая, учитывать тот или иной платеж или нет. Совпадает значение в ячейках технического столбца с этой фразой – складывать, нет – пропускать. "Реестр заявок на платеж"!G14:G39 – ссылка на все ячейки, содержащие суммы по заявкам на платеж. Аналогично вводятся формулы для всех статей, оплата по которым осуществляется на основании заявок от руководителей подразделений (см. рис. 7);

• добавляем в бюджет движения денежных средств данные о тех заявках, которые еще не были оплачены. Иначе говоря, нужно заполнить графу «Сумма заявленных, но не исполненных на текущую дату платежей». Логика будет той же, что и в предыдущем случае. Разница в том, что вместо оплаченных заявок надо будет заставить Excel отобрать те, по которым деньги еще не ушли. Формула выглядит так:

  =СУММЕСЛИ("Реестр заявок на платеж"!I14:I57;"Оплата товаров";"Реестр заявок на платеж"!G14:G39).

Обратите внимание, что в критерии отбора заявок («Оплата товаров») после названия статьи бюджета должен стоять пробел. Помните, создавая технический столбец в реестре заявок, мы добавили знак пробела? Его и надо учесть. Иначе ничего работать не будет.

Теперь все готово. В бюджете есть данные о запланированных выплатах на месяц, фактически осуществленных (оплаченные заявки) и предстоящих (заявленные, но не исполненные платежи). Этих сведений более чем достаточно, чтобы заранее предвидеть перерасход бюджета. Кстати, по мере добавления заявок в реестр (или изменения их статуса), благодаря функции «СУММЕСЛИ» данные будут мгновенно меняться.

Автоматически форматируемые таблицы и отчеты

Когда необходимо добавить строки и столбцы в таблицу (ввести дополнительные статьи доходов и расходов, новые наименования продукции и т. д.), каждый раз приходится повторять ввод формул, следить, чтобы не поплыли ссылки на ячейки, исправлять выпадающие спис­ки, заново определять диапазоны исходных данных для сводных таблиц. Элегантным решением может стать создание динамического именованного диапазона, который будет автоматически подстраи­ваться под вновь добавляемые строки и столбцы.

Тема: Средства графического представления статистических данных (деловая графика). Представление результатов выполнения расчетных задач средствами деловой графики

Количество часов : 2

Цель

Отчет должен содержать:

1.Название работы

2.Цель работы

4.Вывод по работе (

Просмотр содержимого документа
«ПР16 Средства графического представления статистических данных (деловая графика).»

Методические указания к практическому занятию № 16

Тема: Средства графического представления статистических данных (деловая графика). Представление результатов выполнения расчетных задач средствами деловой графики

Количество часов : 2

Цель : закрепление практических навыков и умений в работе c технологиями работы в MS EXCEL:

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

Изучить системы деловой графики;

Рассмотреть возможности Excel для создания деловой графики

Задание: Ознакомиться с теоретическими положениями по данной теме, выполнить задания практического занятия, сформулировать вывод.

Отчет должен содержать:

1.Название работы

2.Цель работы

3.Результаты выполнения задания 1, 2, 3

4.Вывод по работе (необходимо указать виды выполняемых работ, достигнутые цели, какие умения и навыки приобретены в ходе ее выполнения)

Методические указания к выполнению:

1 . Краткие теоретические сведения

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

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

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

Основной целью деловой графики, является оперативная выдача информации в графической форме в процессе анализа задачи при ее решении средствами электронной таблицы. Главным критерием является быстрота подготовки и отображения графических образов, соответствующих оперативно изменяющейся числовой информации.

В современном мире существует большое количество информации, которую трудно сразу усвоить обычному человеку. А если нужно обработать, проанализировать какие-то данные, то задача становится сложной вдвойне. В настоящее время, с помощью компьютерных технологий, можно хранить и обрабатывать любую информацию.

Деловая графика – это область компьютерной графики. Она предназначена для построения гистограмм, графиков, диаграмм, схем на основе отчетов, различной документации. Обеспечивает возможностью более лаконичного и выразительного представления данных, выполнения более сложных задач, улучшенное восприятие данных, повышение эффективности работы.

Для отображения графической информации используется специальное программное обеспечение, которое называют графическими программами или графическими пакетами.

Применение деловой графики различно и многообразно. Для каждого вида задач используются разные программы. Одни нужны для оперативной обработки цифровых данных, другие для построения чертежей высокого качества. С помощью специальных прикладных средств составляются годовые отчеты фирм, статистические справочники, обрабатываются аналитические данные.

Правильно составленные схемы или графики превращают скучные цифры в наглядные изображения и помогают пользователям сориентироваться в «море» информации и принять нужное решение. Деловая графика позволяет в удобной форме сравнивать различные данные, выявлять закономерности и тенденции развития. Можно также с помощью различных графических программ решать задачи в области информационных технологий, архитектуры и инженерии. В настоящее время деловая графика прочно вошла в нашу жизнь, сейчас невозможно представить никаких сводных документов или презентаций без таблиц, схем, диаграмм, различных изображений.

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

С помощью богатой библиотеки диаграмм Excel можно составлять диаграммы и графики разных видов: гистограммы, круговые диаграммы, столбчатые, графики и др., их можно снабжать заголовками и пояснениями, можно задавать цвет и вид штриховки в диаграммах, печатать их на бумаге, изменяя размеры и расположение на листе, и вставлять диаграммы в нужное место листа.

2.Задание

Задание №1. Создать таблицу продажи акций брокерской фирмы. Произвести расчеты по заданию.

ЗАДАНИЕ 2. Создание и обработка БД в Excel. Связывание листов рабочей книги

1.Создать в новом файле таблицу по образцу (см. рис.1). Знаки?, указанные в некоторых ячейках, означают, что данные показатели необходимо вычислить.

3 .Отформатировать таблицу.

4 .Выполнить указанные ниже действия:

3.На листе 4 создать таблицу изображенную на рис4.

3.1.Для добавления Листа необходимо щёлкнуть правой кнопкой мыши на ярлыке Листа и из контекстного меню выбрать команду Вставить . Во вкладке Общие выбрать Лист и нажать Ok .

3.2.В ячейку B2 ввести формулу для суммарного подсчёта проданной продукции за январь, февраль и март месяцы, а именно

ЛИСТ1!G4+ЛИСТ2!G4+ЛИСТ3!G4, где знак! означает связь между листами.

Для оптимизации ввода формулы необходимо проделать следующее:

1.Поместить курсор в ячейку B2 и ввести знак =.

2.Щёлкнуть на ярлыке Лист1, щёлкнуть по ячейке G4.

3.Нажать клавишу +

4.Щёлкнуть на ярлыке Лист 2, ячейке G4, нажать +.

5.Щёлкнуть на ярлыке Лист 3, ячейке G4 и нажать Enter

6.Скопировать введённую формулу на остальные ячейки.

3.3.В ячейку С2 ввести формулу для подсчёта суммарной выручки за 1 квартал (т.е. месяцы январь, февраль, март)

ЛИСТ1!H4 + ЛИСТ2!H4+ЛИСТ3!H4

3.4.Лист 4 переименовать в Итоги продаж (товары).

4.На Листе5 создать таблицу, изображенную на рис5.

.

4.1.Самостоятельно ввести формулы для подсчёта итоговой продажи и итоговой выручки за январь, февраль и март.

4.2.Переименовать Лист5 в Итоги продаж (месяцы).

4.3. Построить диаграммы:

Наименование продукции и Продано

Наименование продукции и выручка

3. Контрольные вопросы

1.Что понимается под термином деловая графика?

2.Для чего предназначена деловая графика?

3.Кто пользуется деловой графикой?

4. Литература

1. https://superbotanik.net/referati/referaty-po-informatike/referat-delovaya-grafika

Критерий оценивания выполненных практических заданий

Первое задание оценивается в 2 баллов (максимально), а второе - в 3 балла (максимально)

В общей сумме, выполненная практическая работа может быть оценена от 0 до 100% , и тогда оценка знаний студента будет такова:

«отлично» - 86 – 100%,

«хорошо» - 66 – 85%,

«удовлетворительно» - 45 – 65%,

«неудовлетворительно» - менее 45%.