2. Статистические графики.

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

Статистическая таблица отличается от других табличных форм следующим:

является итогом сводки первоначальной информации.

Таким образом, статистическая таблица – способ рационального изложения и обобщения данных о социально-экономических явлениях при помощи цифр, расположенных в определенном порядке.

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

Название таблицы (общий заголовок)

Рис. 4.1. Остов (основа) статистической таблицы.

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

Статистическая таблица содержит три вида заголовков:

общий – отражает содержание всей таблицы (к какому месту и времени она относится), располагается над её макетом по центру и является внешним заголовком.;

верхние заголовки характеризуют содержание граф;

боковые характеризуют содержание строк.

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

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

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

Статистическая таблица

Полная статистическая таблица

заголовок

Цифровые статистические данные по графам и строкам

внутренний

Рис. 4.2. Основные составляющие элементы статистической таблицы.

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

По построению подлежащего таблицы могут быть:

простыми,

групповыми,

комбинационными.

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

монографические характеризуют не всю совокупность единиц, а только одну какую-либо группу из него, выделенную по определенному признаку (пример табл. 4.1 );

Цель курса: формирование ИКТ компетенции.

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

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

Цель занятия: Формирование компетенции, связанной с работой с автоматизированными информационными системами, (приложением МS Excel).

Задачи занятия:

Воспитательная (аксеологический компонент компетенции)

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

Учебная (когнитивный компонент компетенции)

познакомить с основными технологиями преобразования информации (из числовой в графическую) с помощью деловой графики программного приложения МS Excel;

Практическое и оперативное применение знаний (операциональный компонент компетенции)

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

Методы обучения:

метод решения конкретной ситуации (кейс-метод):

Суть метода довольно проста: для организации обучения используются описания конкретных ситуаций (от английского “case” – случай). Учащимся предлагают осмыслить реальную жизненную ситуацию, описание которой одновременно отражает не только какую-либо практическую проблему, но и актуализирует определенный комплекс знаний, который необходимо усвоить при разрешении данной проблемы. При этом сама проблема не имеет однозначных решений.

словесные:

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

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

наглядные:

компьютерная демонстрация, обладает динамичностью и наглядностью;

практические:

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

Формы обучения:

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

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

форма “студент и компьютер” – студент в своём темпе овладевает знаниями, сам выбирает индивидуальный маршрут изучения учебного материала.

Контроль знаний

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

Применение здоровьесберегающих технологий на уроке:

смена видов деятельности;
соблюдение воздушно-теплового режима;
выполнение эрг-упражнений;
нормирование времени при работе с компьютерной техникой.

Оборудование:

компьютерный класс,
мультимедийный проектор,
экран,
программное обеспечение (приложение MS Excel),
тесты,
карточки-инструкции для дифференцирования заданий
презентации к уроку (Приложение 1 и Приложение 2 )

Тип урока: урок объяснение нового материала и систематизации знаний. Его логика соответствует структуре урока данного типа.

План урока (Хронометраж)

Организация урока – 1-2 мин
Вступительное слово (сообщение целей и задач урока) – 2 мин
Актуализация опорных знаний и умений

Фронтальный опрос: тестирование с помощью презентации (Вопрос-ответ, самопроверка – 14 мин

Объяснение нового материала с применением ПК – 25-30 мин

Физминутка – 2 мин

Формирование и применение знаний, умений и навыков

Практическая работа 1– 3 (различные уровни сложности) – 30 мин

Итог урока – 1-2мин

Рефлексия – 5 мин (подведение итогов – подсчет жетонов, построение диаграммы)

Домашнее задание 2-3 мин

Ход урока

I. Организационный момент.

– Наш сегодняшний урок мне хочется начать словами В.Г. Белинского: “Человек страшится только того чего не знает, знанием побеждается всякий страх”.

– Объявление темы и целей урока.

II. Актуализация опорных знани й.

а) Фронтальный опрос учащихся по теме “Электронные таблицы” с использованием презентации (Слайд ) (Приложение 2)

1.В электронной таблице основной элемент рабочего листа – это:

ячейка;
строка;
столбец;
формула.

2. В электронной таблице ячейкой называют:

горизонтальную строку;
вертикальный столбец;
курсор – рамку на экране;
пересечение строки и столбца.

3. В электронной таблице невозможно удалить:

строку;
столбец;
содержимое ячейки;
имя ячейки.

4. Выберите верное обозначение строки в электронной таблице:

18D;
К13;
34;
АВ.

5. Выберите верное обозначение столбца в электронной таблице:

DF;
F12;
АБ;
113.
АВ.

6. Выберите верный адрес ячейки в электронной таблице:

11D;
F12;
АБ3;
В1А.

7. В ячейке электронной таблицы не может находиться:

число;
текст;
лист;
формула.

8. Если в ячейке электронной таблицы отображается последовательность символов ######, то это означает, что:

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

9. Содержимое текущей (выделенной) ячейки отображается:

в поле имени;
в строке состояния;
в строке формул;
в пункте “Вид” текстового меню.

10. Как обычно (то есть по умолчанию) выравниваются числа в ячейках электронной таблицы?

по центру;
по центру выделения;
по правому краю;
по левому краю.

11. Сколько ячеек содержит диапазон D4: E5 в электронной таблице:

4;
8;
9;
10.

12. В ячейке D7 записана формула: (С3+C5)/D6. Как она изменится при переносе этой формулы E8?

(С3+C5)/D6;
(С3+C5)/E6;
(D4+D6)/E7;
(С4+C6)/D7.

13. Дана электронная таблица. В ячейку D2 введена формула: (A2*B1+C1) Какое значение появится в ячейке D2?

	A	B	C	D
1	5	2	4
2	10	1	6

14. В ячейке B1 записана формула =2*A1. Какой вид приобретет формула, после того как ячейку B1 скопируют в ячейку C1?

2*B1
=2*A2
=3*A2

б) Письменный опрос

– Подпишите листочки, ответ выбираете один. (см. Приложение)

III. Изложение нового материала

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

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

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

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

– Для построения диаграммы с помощью графического процессора следует:

1) составить таблицу с набором данных;

2) выделить необходимый диапазон данных, по которым будет строиться диаграмма;
б) выбрать наиболее подходящий тип диаграммы: меню Вставка – Диаграмма далее по шагам

– Сегодня на уроке мы рассмотрим самые распространенные типы диаграммы.

1. Круговая диаграмма (Слайд) (Приложение 1)

а) служит для отображения величин от целого; б)служит для сравнения нескольких величин в одной точке.

Например, 1)Доля цены комплектующих в компьютере,

2) Незнайка продавец канцтоваров (самостоятельно)

2. Столбчатая диаграмма (Гистограмма) (Слайд) служит для сравнения нескольких величин в нескольких точках. Высота столбиков определяется значениями сравниваемых величин. Каждый столбик привязан к некоторой опорной точке.

Например,1) Незнайка торгует газетами. Опорная точка соответствует дню недели. (Слайд)

2) Теперь рассмотрим более сложную задачу, в которой требуется несколько раз сравнить несколько величин. (Слайд)

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

Копируем таблицу с данными на новый лист. Добавляем данные. Строим диаграмму. Определите разницу с предыдущей диаграммой. (Теперь в каждой опорной точке будут стоять не один столбик, а три - по одному для каждого продавца. Все столбики одного продавца будут закрашены одинаково, нужна ЛЕГЕНДА)

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

– Например (Слайд) ,

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

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

ВЫВОД: Электронные таблицы Excel позволяют строить диаграммы различных типов. Построение диаграмм осуществляется с помощью специальной подпрограммы, которая называется Мастер диаграмм .

Физкультминутка, гимнастика для глаз – 2 мин.

IV. Закрепление. Практическая работа.

Цель: сформировать умения выполнять задания: по набору данных строить диаграммы определенного типа.

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

Практическую работу проведем используя метод разбора конкретной ситуации. Этот метод часто называют кейс – метод.

(Слайд) Кейс – технология (case study – "разбор конкретных ситуаций”)

“Родиной” данного метода является Школа бизнеса Гарвардского университета.

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

– У вас было задание во временном промежутке – Одна неделя – Провести мониторинг температурного режима в помещениях колледжа. (Карточки – инструкции: Придложение 2)

Задание 1 : Построить диаграмму, отражающую мониторинг температурного режима в помещениях колледжа .

Приготовьте ваши материалы. Разберем алгоритм выполнения данного задания:

1)Составить таблицу данных. (Какие столбцы нужно создать в таблице? Сколько строк? Как озаглавить столбцы?)

2) Вставить диаграмму . (Какой тип диаграммы нужно выбрать в данном случае?)

(Слайд с изображением диаграммы)

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

Задание 2: Построить диаграмму, отражающую мониторинг температурного режима в кабинетах в сравнении с нормами температур СанПиНа (СанПиН – санитарные нормы и правила, утвержденные главным санитарным врачом РФ).

По одному набору данных постройте две диаграммы разного типа.

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

	Значения, мм
Сотка
Аршин
Четверть
Вершок
Фут
Дюйм
Линия

V. Итог занятия

– Какие новые понятия вы сегодня узнали на уроке? (Слайд )

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

– Давайте подведем итог занятия: Возьмите каждый конвертик, там три карточки, выберите то, что Вам ближе и положите карточку в коробку. Галя и Наташа (Счетная комиссия) введут результаты вашего выбора в таблицу и мы увидим в % уровень усвоения новой темы. (Слайд )

VI. Домашнее задание

Составить карточки – инструкции по теме “Построение диаграмм”, включая задания на построение диаграмм всех рассмотренных типов диаграмм (Инструкции прописывать в соответствии с Office 3, 7, 10)

Проиллюстрировать лекционный материал – зарисовать все рассмотренные типы диаграмм

Выставление оценок за урок.

– Урок окончен. Спасибо за работу.

Занятие 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. Для прогнозирования поведения каких величин можно использовать возможности тренда?

Практическая работа: «Использование средств деловой графики для наглядного представления данных средствами электронной таблицы»

Теоретический материал:

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

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

Цель: формирование ключевых компетенций.

Задание: Сформируйте и заполните накопительную ведомость начисления оплаты номеров гостиницы «Старт» за март 2012 года. Номера в гостинице 1-местные стоят для каждою клиента 1800 руб. в день, 2-местные 1200 руб. с каждого клиента. Номер можно забронировать. Бронь в гостинице может быть двух видов: групповая и индивидуальная и оплачивается отдельно. При бронировании на группу оплат за первый день проживания увеличивается на 25% от стоимости номера, в тех случаях, когда брони нет или она индивидуальная, доплаты нет.

Тип брони и количество дней проживания в каждом номере представлены в таблице.

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

Ход работы:

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

Ведомость начисления оплаты номеров гостиницы «Старт» за март 2012 г.

Номер комнаты	Тип занимаемого номера	Стоимость номера с человека в день (руб.)	Тип брони	Оплата за бронь (руб.)	Кол-во дней проживания	Оплата за дни проживания (руб.)
	1-местный		группа
	1-местный		группа
	1-местный		инд.
	2-местный		инд.
	2-местный
	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 – не что иное, как проверка того, позже ли дата текущей ячейки (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).

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

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

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

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