Как построить диаграмму Ганта в 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).
Обратите внимание, что в критерии отбора заявок («Оплата товаров») после названия статьи бюджета должен стоять пробел. Помните, создавая технический столбец в реестре заявок, мы добавили знак пробела? Его и надо учесть. Иначе ничего работать не будет.
Теперь все готово. В бюджете есть данные о запланированных выплатах на месяц, фактически осуществленных (оплаченные заявки) и предстоящих (заявленные, но не исполненные платежи). Этих сведений более чем достаточно, чтобы заранее предвидеть перерасход бюджета. Кстати, по мере добавления заявок в реестр (или изменения их статуса), благодаря функции «СУММЕСЛИ» данные будут мгновенно меняться.
Автоматически форматируемые таблицы и отчеты
Когда необходимо добавить строки и столбцы в таблицу (ввести дополнительные статьи доходов и расходов, новые наименования продукции и т. д.), каждый раз приходится повторять ввод формул, следить, чтобы не поплыли ссылки на ячейки, исправлять выпадающие списки, заново определять диапазоны исходных данных для сводных таблиц. Элегантным решением может стать создание динамического именованного диапазона, который будет автоматически подстраиваться под вновь добавляемые строки и столбцы.
>>Информатика: Деловая графика в задачах планирования и управления
Деловая графика в задачах планирования и управления
При решении задач планирования и управления очень важна форма отображения результатов. Чем это отображение нагляднее, тем легче воспринимаются результаты человеком. Самой наглядной формой представления информации являются рисунки, графики и диаграммы.
Это породило в информатике целое направление, называемое компьютерной графикой. Компьютерная графика подразделяется на несколько видов: иллюстративная, деловая, инженерная я научная. Деловая графика состоит в визуализации, то есть представлении в наглядной форме больших массивов числовой информации.
Представьте себе огромный годовой отчет о работе предприятия , состоящий из множества числовых таблиц, содержание которых воспринять человеку в целом очень трудно. Но если эту же информацию представить в виде диаграмм, как она становится обозримой, лучше понимается характер изменения величин, легче производится их сравнение. Табличные процессоры содержат развитые средства деловой графики. Познакомимся с основными понятиями деловой графики.
Это средство графического представления количественной информации, предназначенное для сравнения значений величин или нескольких значений одной величины, слежения за изменением их значений и так далее.
Как правило, строятся в прямоугольной системе координат, где по горизонтальной оси ОХ откладываются значения независимой переменной (аргумента), а по вертикальной оси ОY - значения зависимой переменной (функции). На один рисунок может быть выведено одновременно несколько диаграмм.
При графической обработке числовой информации с помощью табличного процессора следует:
1) указать область данных (блок ячеек), по которым будет строиться диаграмма;
2) определить последовательность выбора данных (по строкам или по столбцам) из выбранного блока.
При выборе по столбцам х-координаты берутся из крайнего левого столбца выделенного блока. Остальные столбцы содержат у-координаты диаграмм. По количеству столбцов определяется количество строящихся диаграмм. При выборе по строкам самая верхняя строка выделенного блока является строкой х-координат, остальные строки содержат у-координаты. Типы диаграмм и способы их построения рассмотрим на конкретном примере.
Пример . Три друга Андрей, Илья и Сергей решили во время школьных каникул поработать распространителями популярной газеты « Садовод-огородник». Друзья работали в течение недели. Число газет, проданных каждым из ребят за каждый день, залесено в электронную таблицу в виде, представленном в табл. 2.1.
Гистограмма
(столбчатая диаграмма) применяется для отражения дискретного изменения одной или нескольких величин. На рис. 2.5 представлена гистограмма, отражающая результаты торговли Андрея в каждый день недели.
Рис. 2.5. Гистограмма в Excel
Вторая диаграмма (рис. 2.6) представляет собой множественную гистограмму. Она дает возможность наглядно сравнить три величины: результаты торговли Андрея, Ильи и Сергея.
На рис. 2.7 представлена ярусная диаграмма. Другое название - гистограмма с накоплением. Здесь каждый столбик - это сумма трех величин. Диаграмма дает представление о вкладе каждой величины в общую сумму.
"
Рис, 2.7. Ярусная диаграмма (гистограмма с накоплением)
График позволяет отразить изменение одной или нескольких величин в виде непрерывных линий. На рис. 2.8 в виде графика отражена та же информация, что приведена на рис. 2.5 в виде гистограммы. Аналогично рис. 2.9 отображает ту же информацию, что и рис. 2.6
Рис. 2.8 График в Excel Рис. 2.9 Отображение нескольких
графиков в Excel
Круговая диаграмма служит для сравнения значении нескольких величин в одной точке. Применение круговой диаграммы бывает особенно наглядным, если величины в сумме составляет некоторое единое целое (100%).
MS Excel содержит еще целый ряд других типов и видов диаграмм, с которыми вы можете познакомиться экспериментально.
Коротко о главном
Деловая графика - средство визуализации, то есть представления в наглядной форме, массивов числовых данных.
Данные для диаграммы выбираются из выделенного блока таблицы. Возможен выбор данных по столбцам и по строкам.
Столбчатые диаграммы называются гистограммами и отражают дискретное распределение величин. Можно построить как одиночную так и множественную гистограмму.
График рассматривается как разновидность диаграммы. На графике отражается непрерывный процесс изменения величины.
Круговая диаграмма используется для представления вклада нескольких величин в единое целое.
Вопросы и задания
1. В чем состоит назначение диаграмм?
2. Как определяется область выбора данных из таблицы для построения диаграммы и порядок выбора? Какие величины откладываются по горизонтальной (ОХ) оси и вертикальной (ОУ) оси?
3. В каких ситуациях предпочтительнее использовать: гистограммы; графики; круговые диаграммы?
4. Постройте таблицу, отражающую число уроков в вашем классе и в двух-трех других классах каждый день недели. На основании этих данных представьте эскизы различных видов диаграмм, рассмотренных в данном параграфе.
Семакин И.Г., Хеннер Е.К., Информатика и ИКТ, 11
Отослано читателями из интернет-сайтов
Содержание урока конспект урока опорный каркас презентация урока акселеративные методы интерактивные технологии Практика задачи и упражнения самопроверка практикумы, тренинги, кейсы, квесты домашние задания дискуссионные вопросы риторические вопросы от учеников Иллюстрации аудио-, видеоклипы и мультимедиа фотографии, картинки графики, таблицы, схемы юмор, анекдоты, приколы, комиксы притчи, поговорки, кроссворды, цитаты Дополнения рефераты статьи фишки для любознательных шпаргалки учебники основные и дополнительные словарь терминов прочие Совершенствование учебников и уроков исправление ошибок в учебнике обновление фрагмента в учебнике элементы новаторства на уроке замена устаревших знаний новыми Только для учителей идеальные уроки календарный план на год методические рекомендации программы обсуждения Интегрированные урокиЦель работы: Научиться представлять табличные данные в виде диаграмм и графиков различных типов.
Порядок выполнения работы
Годовой бюджет (см. рис.4.1.). Заполните данными первую строку таблицы (Продажа фруктов ) и рассчитайте значения остальных ячеек, вставив формулы в соответствии со следующими соотношениями:
Торговые издержки – 30% от продажи фруктов;
Затраты на маркетинг – 10% от продажи фруктов;
Накладные расходы – 20% от продажи фруктов.
Рассчитайте Общую сумму расходов (сумма по Торговым издержкам , Затратам на маркетинг и Накладным расходам ) и Чистую прибыль (разностьмежду Продажей фруктов и Общей суммой расходов ).
2. Постройте несколько диаграмм для наглядного представления полученных данных.
а) Диаграмма № 1: поквартальные расходы по трем категориям (торговые издержки, затраты на маркетинг, накладные расходы) в виде объемной гистограммы. Выделите диапазон данных А5:Е7 , вызовите Мастер диаграмм и укажите нужный тип гистограммы (см. рис.4.2.).
Замечание : чтобы вывести названия кварталов в качестве меток оси Х следует на втором шаге построения диаграммы перейти на вкладку Ряд и в поле Подписи по Х вести ссылку на диапазон В3:Е3 .
б) Диаграмма № 2: продажа фруктов по кварталам. Постройте плоскую гистограмму по диапазону А4:Е4 (чтобы на оси Х отображались названия кварталов следует выделить диапазон А3:Е4 ). Оформите построенную гистограмму как показано на рис.4.3.
Замечание : изменить любой элемент диаграммы можно в диалоговом окне форматирования, вызвав его двойным щелчком мыши по заданному объекту.
в) Диаграмма № 3: измените тип диаграммы №2 на круговую. Получив круговую диаграмму, следует вставить подписи данных, указав процент продаж в каждом квартале (см. рис.4.4.).
г) Диаграмма № 4: скопируйте диаграмму №3 и преобразуйте в гистограмму. На диаграмме получить поквартальную продажу фруктов и чистую прибыль, для этого к данным диаграммы № 3 следует добавить новые данные: выделить ячейки А9:Е9 , выбрать ПравкаÞ Копировать; щелкнуть на одном из столбцов гистограммы, выбрать ПравкаÞ Специальная вставка.
Рис. 4.2. Рис. 4.3.
д) Диаграмма № 5: добавьте к Диаграмме № 4 легенду (подписи рядов данных). Для этого необходимо правой кнопкой мыши щелкнуть по области диаграммы, выбрать в контекстном меню Параметры диаграммы .
е) Диаграмма № 6: измените Диаграмму № 5 так, чтобы данные по продаже фруктов отображались в виде гистограммы, а чистая прибыль – в виде графика (нестандартный тип диаграммы). Для этого следует изменить тип диаграммы, выбрав на вкладке Нестандартные тип График|гистограмма . Оформить полученную диаграмму как показано на рис.4.5.
Рис. 4.5. Рис. 4.4.
3. Постройте график поверхности z=x 2 -y 2
, где 
.
а) Для построения графика поверхности необходимо сначала создать таблицу данных. Заполните строку 1 значениями переменной x
: введите в ячейку В1
число -7,5 (левую границу диапазона), выделите ячейки В1:L1
и выберите ПравкаÞЗаполнитьÞПрогрессия, указав Шаг 1.5. Аналогично заполните ячейки А2:А12
значениями переменной у
- начальное значение -5, Шаг 1. Введите формулу для вычисления z
в первую ячейку таблицы: (B2)=B$1^2-$A2^2
, и продолжите ее на все ячейки с помощью маркера автозаполнения. До построения графика ячейку А1
следует оставить пустой.
б) Выделите диапазон А1:L12 и постройте график поверхности с помощью Мастера диаграмм. В итоге должен получиться гиперболический параболоид.
абораторная работа №5
Тема : Использование таблицы в качестве базы данных
Цель работы : Освоить методы ведения списков с помощью средств формы, сортировка, фильтрация.
Порядок выполнения работы
1. Подготовьте таблицу по образцу:
Ведомость
учёта работы транспорта в автохозяйстве за месяц
| № п/п | Марка автомашины | Номер | Плановый пробег, км | Фактический пробег, км | Простой, дн |
| ГАЗ-66 | СР 12-37 | ||||
| ЗИЛ-130 | ТД 21-18 | ||||
| ЗИЛ-130 | СР 28-12 | ||||
| УАЗ-3151 | НФ 19-67 | ||||
| ГАЗ-66 | АЯ 18-16 | ||||
| УАЗ-3962 | НС 96-12 | ||||
| УАЗ-3962 | НС 84-17 |
2. Последовательно выполните в созданной таблице сортировку записей:
По фактическому пробегу в возрастающем порядке;
По количеству дней простоя в убывающем порядке;
По маркам автомобилей, а внутри каждой полученной группы по плановому пробегу.
3. Восстановите первоначальный порядок записей в таблице.
4. Между столбцами Фактический пробег, км и Простой, дн разместите поле Разность , в котором вычислите отклонение фактического пробега от планового.
5. Создайте форму для таблицы (списка).
6. Пролистывая записи с помощью формы найдите сведения об автомобиле с номером СР 28-12.
7. Используя критерии отбора, с помощью формы последовательно определите (после вопросов приводятся правильные ответы):
а) у каких автомобилей фактический пробег составил более 500 км:
б) у какого из автомобилей ЗИЛ-130 простой составил менее 10 дней:
7. Используя форму, добавьте в таблицу запись:
8. Удалите введённую новую запись и закройте окно формы.
9. Удалите из таблицы поле Разность, км .
10. Применяя Автофильтр , последовательно определите (ниже вопроса приводится правильный результат работы):
а) у каких автомобилей простой составляет от 5 до10 дней включительно:
б) у какого автомобиля плановый пробег составляет 600 км, простой менее 15 дней, фактический пробег – более 500 км:
в) у каких автомобилей фактический пробег составляет менее 500 км или более 1000 км, а простой – менее 15 дней:
11. Применяя Расширенный фильтр и сохраняя критерии отбора выше таблицы, а результаты – ниже таблицы, (состав столбцов и приводимая в них информация должны соответствовать приведённым образцам) последовательно определите:
а) у какого автомобиля планировался пробег 1000 км, фактический пробег составил более 500 км, простой – менее 6 дней:
б) у каких автомобилей простой составляет 0, 7 или 14 дней:
г) какие автомобили имеют фактический пробег более 1000 км или простой от 5 до 10 дней:
12. Покажите результаты работы преподавателю.
13. Переименуйте лист с результатами работы, назвав его Фильтрация.
14. Завершите работу с MS Excel, сохранив результаты.
ариант 1
Создайте таблицу поступления товаров в магазин со следующими заголовками столбцов: Дата поступления, Наименование товара, Отдел, Количество, Цена, Стоимость товаров в партии . Заполнить таблицу данными для двух отделов (Одежда, Обувь) и трех дней поступления товаров (пальто, костюм, сапоги, туфли). Рассчитайте стоимость товаров.
1. Выполните сортировку по отделам, а внутри каждого отдела по наименованию товара.
2. Выберите сведения о костюмах, поступивших конкретного числа тремя способами:
С помощью формы;
С помощью автофильтра;
Какие товары из отдела Одежда поступили одного числа или другого;
Какие пальто имеют стоимость больше 3000 руб. В результирующую таблицу включить поля: Дата поступления, Наименование товара, Количество, Стоимость ;
Какая обувь имеет цену больше средней по отделу.
ариант 2
Создайте таблицу успеваемости студентов 456 и 457 групп, содержащую следующие поля: Ф.И.О., № группы, Дата сдачи, Предмет, Балл . Заполните таблицу данными для четырех студентов, сдающих экзамены по двум предметам (Математика, Информатика).
1. Выполните сортировку по № группы, а внутри каждой группы по возрастанию балла.
2. Выберите сведения о студентах, сдавших Информатику с баллом больше 4.0 тремя способами:
С помощью формы;
С помощью автофильтра;
С помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
4. Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты определите:
Какие студенты сдали математику и информатику с баллом больше 4,5. В результирующую таблицу включить поля: Ф.И.О., Предмет, Балл ;
Какие студенты из группы 457 получили баллы меньше 3 или больше 4;
Какие студенты получили балл на 20% меньше среднего по двум группам.
ариант 3
Создайте таблицу реализации печатной продукции, с заголовками столбцов: Дата реализации, Название, Тип издания (газета, журнал, календарь), Цена одного экземпляра, Количество, Сумма от реализации . Заполните данными десять строк таблицы для трех дней продаж. Рассчитайте сумму от реализации.
1. Выполните сортировку по дате реализации, а внутри каждой даты по названию.
2. Выберите сведения о журналах, проданных на сумму более 400 рублей тремя способами:
С помощью формы;
С помощью автофильтра;
С помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
Какие журналы или календари имеют цену меньше 30 рублей;
Какие издания были реализованы конкретного числа в количестве более 20 экземпляров. В результирующую таблицу включите поля: Дата, Название, Тип издания, Количество ;
Какие типы изданий были проданы на сумму меньше средней по всем изданиям.
ариант 4
Создайте таблицу реализации дисков для изучения английского, немецкого и французского языков со следующими заголовками столбцов: Месяц продаж, Наименование диска (Английский язык и т.д.), Тип (разговорный, деловой), Цена одного диска, Кол-во проданных дисков, Сумма от реализации . Заполните таблицу данными о продажах за три месяца (не менее 10 строк). Рассчитайте сумму от реализации.
1. Выполните сортировку по месяцу продаж, а внутри каждого месяца по количеству проданных дисков.
2. Выберите сведения о дисках делового типа, проданных в конкретном месяце, в количестве более 200 тремя способами:
С помощью формы;
С помощью автофильтра;
С помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
3. Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты определите:
Какие диски для изучения английского или немецкого языков разговорного типа были проданы в конкретном месяце;
В каком месяце диски делового типа для изучения французского языка были проданы на сумму более 2000 рублей. В результирующую таблицу включить поля: Месяц, Наименование диска, Тип, Сумма от реализации ;
Какие диски были проданы в количестве на 10% больше минимального количества.
ариант 5
Создайте таблицу поступления в магазин компьютерной продукции, содержащую следующие поля: Дата поступления, Наименование товара (системный блок, монитор, принтер), Фирма изготовитель, Цена, Количество, Стоимость товаров в партии . Заполните таблицу данными по двум дням работы магазина и двум фирмам изготовителям (не менее 10 строк). Рассчитайте стоимость товаров в партии.
1. Выполните сортировку по фирме изготовителю, а внутри каждой фирмы по наименованию товара.
2. Выберите сведения о фирмах, у которых цена монитора меньше 10000 рублей тремя способами:
С помощью формы;
С помощью автофильтра;
С помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
3. Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты определите:
Какие товары поступили конкретного числа в количестве более 20 штук. В результирующую таблицу включить поля: Дата, Наименование товара, Количество ;
Какие фирмы поставили мониторы или принтеры на общую стоимость меньше 80000 рублей;
Системные блоки какой фирмы имеют цену меньше средней цены системных блоков.
ариант 6
Создайте таблицу начисления заработной платы сотрудникам за последние три месяца работы, используя следующие заголовки столбцов: (бухгалтерия, канцелярия), Месяц, Оклад . Заполните таблицу данными для четырех сотрудников.
1. Выполните сортировку по отделу, а внутри отдела по Ф.И.О.
2. Выберите сведения о сотрудниках, которые были зачислены с окладом 5000 рублей в январе тремя способами:
С помощью формы;
С помощью автофильтра;
С помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
3. Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты определите:
Сотрудники каких отделов имеют оклады более 10000 рублей или менее 3000 рублей;
Какого числа в бухгалтерию была зачислена Петрова Ольга Ивановна. В результирующую таблицу включить поля: Ф.И.О., Дата зачисления, Отдел ;
Какие сотрудники из канцелярии имеют оклад на 10% больше среднего оклада по всем отделам.
ариант 7
Создайте таблицу отгрузки нефтепродуктов со следующими полями: Дата отгрузки, Покупатель, Наименование товара (нефть, мазут, и т.д.), Количество (т.) , Цена, Стоимость отгруженного товара . Заполните таблицу данными для трех покупателей (Завод 1, Завод 2, Завод 3) и двух дней работы. Рассчитайте стоимость отгруженного товара.
1. Выполните сортировку по покупателю, а внутри покупателя по наименованию товара.
2. Выберите сведения о товарах, которые были отгружены для Завода 1 конкретного числа тремя способами:
С помощью формы;
С помощью автофильтра;
С помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
3. Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты, определите:
Сведения о нефти или мазуте, которые были отгружены на Завод 3 в количестве более 5 тонн. В результирующую таблицу включите поля: Покупатель, Наименование товара, Количество ;
Какого числа нефть была отгружена стоимостью больше 100000 рублей;
Какие покупатели получили нефть в количестве, меньше среднего количества отгруженной нефти всем покупателям.
ариант 8
Создайте таблицу реализации продукции мебельной фабрики со следующими полями: Месяц, Наименование товара, Тип (кухня, спальня, детская), Цена гарнитура, Количество (шт.), Сумма от реализации . Заполните таблицу данными для двух месяцев продаж, используя 2-3 наименования товара по каждому типу мебели (например, спальни «Престиж» и «Ирина»). Рассчитайте сумму от реализации.
1. Выполните сортировку по типу товара, а внутри каждого типа по цене.
2. Выберите сведения о кухнях, которые были реализованы в конкретном месяце тремя способами:
С помощью формы;
С помощью автофильтра;
С помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
3. Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты, определите:
Сведения о спальнях, по цене больше 50000 рублей или меньше 25000 рублей;
Какой товар был реализован в конкретном месяце на сумму больше 20000 рублей. В результирующую таблицу включить поля: Месяц, Наименование товара, Тип, Сумма от реализации ;
Какие товары были реализованы в количестве на 20% больше среднего по всем товарам.
ариант 9
Создайте таблицу заказов авиабилетов туристическими агентствами, используя следующие поля: Дата вылета, Страна прибытия (Турция, Испания, Египет), Название тур. агентства, Цена билета, Количество билетов, Стоимость заказа . Заполните таблицу данными для двух тур. агентств и трех дат вылета.
1. Выполните сортировку по названию тур.агенства, а внутри каждого агентства по стране.
2. Выберите сведения о странах, в которые были реализованы билеты в количестве больше 100 в конкретном месяце тремя способами:
С помощью формы;
С помощью автофильтра;
С помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
3. Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты определите:
Сведения об агентствах, которые заказали билеты в Испанию на сумму больше 100000 рублей;
В какие из стран Турцию или Испанию конкретное агентство заказало билеты в количестве меньше 100 штук. В результирующую таблицу включить поля: Страна, Название агентства, Цена, Количество ;
В какую страну количество заказанных билетов меньше среднего количества билетов по всем странам.
ариант 10
Создайте таблицу сдачи вступительных экзаменов абитуриентами на различные факультеты. Один и тот же абитуриент может сдавать экзамены на различные факультеты. Используйте следующие заголовки полей: Дата сдачи, Фамилия абитуриента, Факультет, Название экзамена, Оценка . Заполните таблицу данными для 6 абитуриентов и экзаменов по математике, химии, русскому языку.
1. Выполните сортировку по факультету, а внутри факультета по фамилии абитуриента.
2. Выберите сведения об абитуриентах, которые сдавали экзамены конкретного числа по русскому языку тремя способами:
С помощью формы;
С помощью автофильтра;
С помощью расширенного фильтра, поместив результаты отбора ниже таблицы.
3. Применяя расширенный фильтр, сохраняя критерии отбора и полученные результаты определите:
Какие абитуриенты сдавали экзамены по математике и русскому языку на конкретный факультет. В результирующую таблицу включить поля: Фамилия, Факультет, Название экзамена ;
Какие абитуриенты по экзаменам получили оценки 4 или 5;
На какие факультеты оценки студентов по математике оказались меньше средней оценки по всем предметам.
абораторная работа №6
Тема: Подведение итогов и создание сводных таблиц
Цель работы : Освоить использование инструментов Промежуточные итоги и Сводные таблицы для обобщения и анализа данных.
Порядок выполнения работы
1. В новой рабочей книге создайте таблицу, аналогичную приведенной ниже, и заполните ее данными. В поле Сумма реализации вставьте расчетную формулу.
| Дата продажи | Название книги | Автор | Тип издания | Цена одного экз. | Кол-во продан. книг | Сумма реализации |
| 31.03.2006 | Сага о Фарсайтах | Дж.Голсуорси | худ. | 150р. | 300р. | |
| 31.03.2006 | Сумма технологий | С. Лем | науч. | 78р. | 390р. | |
| 31.03.2006 | Финансы предприятий | А.Д.Шеремет | учебн. | 56р. | 840р. | |
| 03.04.2006 | Пикник на обочине | А.СтругацкийБ.Стругацкий | худ. | 89р. | 356р. | |
| 03.04.2006 | Финансы предприятий | А.Д.Шеремет | учебн. | 56р. | 392р. | |
| 03.04.2006 | Сумма технологий | С. Лем | науч. | 78р. | 78р. | |
| 03.04.2006 | Сага о Фарсайтах | Дж.Голсуорси | худ. | 150р. | 750р. |
 |
Определите итоговую сумму реализации книг по каждому типу изданий и в целом по магазину, используя команду ДанныеÞИтоги (предварительно убедитесь, что таблица отсортирована по полю Тип издания ).
3. Нажимая кнопки группировки слева от таблицы, последовательно установите отображение:
¾ только общих итогов для всего магазина;
¾ только итогов по типам книг и всему магазину.
4. Покажите результаты Вашей работы преподавателю и отмените вычисление итоговых значений.
5. Определите общее количество книг, проданных в течение дня и среднюю сумму продаж по каждой дате. Для вставки нескольких итоговых функций используйте повторный вызов команды ДанныеÞИтоги, отключив при этом опцию Заменить текущие итоги .
| Дата продажи | Название книги | Автор | Тип издания | Цена одного экз. | Кол-во продан.книг | Сумма реализации |
| 31.03.2006 | Сага о Фарсайтах | Дж.Голсуорси | худ. | 150р. | 300р. | |
| 31.03.2006 | Финансы предприятий | А.Д. Шеремет | учебн. | 56р. | 840р. | |
| 31.03.2006 | Сумма технологий | С. Лем | науч. | 78р. | 390р. | |
| 31.03.2006 Итог | ||||||
| 31.03.2006 Среднее | 510р. | |||||
| 03.04.2006 | Пикник на обочине | А.Стругацкий, Б.Стругацкий | худ. | 89р. | 356р. | |
| 03.04.2006 | Сага о Фарсайтах | Дж.Голсуорси | худ. | 150р. | 750р. | |
| 03.04.2006 | Финансы предприятий | А.Д. Шеремет | учебн. | 56р. | 392р. | |
| 03.04.2006 | Сумма технологий | С. Лем | науч. | 78р. | 78р. | |
| 03.04.2006 Итог | ||||||
| 03.04.2006 Среднее | 394р. | |||||
| Общий итог | ||||||
| Общее среднее | 444р. |
6. Скопируйте Лист1 на Лист2 и переименуйте последний, назвав его Сводные таблицы . Отмените на скопированном листе вычисление итоговых значений.
7. Создайте сводную таблицу с данными о суммарной стоимости проданных книг различных типов и в целом по магазину. Измените надпись и формат поля заголовков, как это сделано в приведенной ниже таблице. Установите для отображения данных в сводной таблице денежный формат.
8. Измените представление данных в созданной Вами сводной таблице, поменяв местами строки и столбцы.
9. Измените количество книг, проданных 1.03.2006 на 300 и обновите информацию в сводной таблице. Проанализируйте, какие изменения произошли.
10. Восстановите прежнее количество книг, проданных 1.03.2006, и вновь обновите информацию в сводной таблице.
11. Измените представление данных в сводной таблице, переместив поле Название книги в область строк. Добавьте в область строк из списка полей сводной таблицы поле Дата продажи .
| Сумма реализации | |||
| Тип издания | Название книги | Дата продажи | Итог |
| худ. | Сага о Фарсайтах | 03.04.2006 | 750р. |
| 31.03.2006 | 300р. | ||
| Сага о Фарсайтах Итог | 1 050р. | ||
| Пикник на обочине | 03.04.2006 | 356р. | |
| Пикник на обочине Итог | 356р. | ||
| худ. Итог | 1 406р. | ||
| науч. | Сумма технологий | 03.04.2006 | 78р. |
| 31.03.2006 | 390р. | ||
| Сумма технологий Итог | 468р. | ||
| науч. Итог | 468р. | ||
| учебн. | Финансы предприятий | 03.04.2006 | 392р. |
| 31.03.2006 | 840р. | ||
| Финансы предприятий Итог | 1 232р. | ||
| учебн. Итог | 1 232р. | ||
| Общий итог | 3 106р. |
12. Уберите отображение промежуточных итогов, последовательно выбирая команду Скрыть детали для полей Название книги и Тип издания .
13. Восстановите прежний вид сводной таблицы с отображением промежуточных итогов.
14. Отобразите продажи книг по месяцам, применив инструмент группировки к полю Дата продажи .
| Сумма реалзизации | |||
| Тип издания | Название книги | Дата продажи | Итог |
| худ. | Сага о Фарсайтах | мар | 300р. |
| апр | 750р. | ||
| Сага о Фарсайтах Итог | 1 050р. | ||
| Пикник на обочине | апр | 356р. | |
| Пикник на обочине Итог | 356р. | ||
| худ. Итог | 1 406р. | ||
| науч. | Сумма технологий | мар | 390р. |
| апр | 78р. | ||
| Сумма технологий Итог | 468р. | ||
| науч. Итог | 468р. | ||
| учебн. | Финансы предприятий | мар | 840р. |
| апр | 392р. | ||
| Финансы предприятий Итог | 1 232р. | ||
| учебн. Итог | 1 232р. | ||
| Общий итог | 3 106р. |
15. Покажите результаты Вашей работы преподавателю и отмените группировку. Удалите из сводной таблицы поле Дата продажи.
16. Измените итоговую функцию сводной таблицы, получив среднее количество проданных книг. Установите новый формат представления данных, убрав денежный формат и округлив до целого.
17. Измените итоговую функцию для вычисления общего количества проданных книг.
18. Измените таблицу результатов, переместив поле Тип издания в область страниц. Выберите отображение данных только по учебной литературе.
19. Покажите результаты Вашей работы преподавателю и сохраните созданную Вами рабочую книгу.
Задания для самостоятельной работы
ариант 1
1. Определите итоговые количество и стоимость товаров, поступивших в магазин и его отделы.
2. Измените отображение данных, оставив только итоги по отделам и магазину в целом. Восстановите прежний вид таблицы.
5. На новом листе рабочей книги создайте сводную таблицу, взяв в качестве итоговой функции суммарную стоимость товаров, поступивших в магазин и его отделы. Отобразите в таблице списки товаров для каждого отдела, и даты продаж.
6. Примените группировку для двух дат поступления товара.
Отдел в область страниц. Выберите отображение данных только по отделу Обувь .
ариант 2
В качестве исходных данных используйте базовую таблицу из Лабораторной работы №5, предварительно скопировав ее в новую рабочую книгу.
1. С помощью вставки итоговой функции определите для каждой даты количество студентов, сдающих экзамен.
3. Отмените вычисление итоговых значений.
5. На новом листе рабочей книги создайте сводную таблицу, взяв в качестве итоговой функции общий балл. Отобразите в таблице списки студентов, обучающихся в каждой группе и названия предметов.
6. Примените инструмент группировки для двух групп.
7. С помощью редактирования итоговой функции сводной таблицы получите средний балл по группам.
8. Измените номер группы одного из студентов в исходной таблице. Проанализируйте, изменится ли значение в сводной таблице.
9. Измените таблицу результатов, вынося значение поля № группы в область страниц. Выберите отображение данных только по группе 457.
10. Покажите работу преподавателю и сохраните рабочую книгу.
ариант 3
В качестве исходных данных используйте базовую таблицу из Лабораторной работы №5, предварительно скопировав ее в новую рабочую книгу.
1. Определите итоговые количество и стоимость проданной печатной продукции по каждому типу издания и в целом по магазину.
3. Отмените вычисление итоговых значений.
4. Определите количество типов изданий, проданных в течение дня, и среднюю выручку по каждому дню продаж.
5. На новом листе рабочей книги создайте сводную таблицу, взяв в качестве итоговой функции общую сумму реализации печатной продукции. Отобразите в таблице списки названий для каждого типа издания, и даты продаж.
6. Примените группировку для двух типов печатной продукции.
7. С помощью редактирования итоговой функции сводной таблицы получите среднее количество проданных экземпляров.
8. Измените одно из значений в исходной таблице. Проанализируйте, изменится ли значение в сводной таблице.
9. Измените таблицу результатов, вынося значение поля Дата реализации в область страниц. Выберите отображение данных только по одной дате.
10. Покажите работу преподавателю и сохраните рабочую книгу.
ариант 4
В качестве исходных данных используйте базовую таблицу из Лабораторной работы №5, предварительно скопировав ее в новую рабочую книгу.
1. Определите итоговые количество и стоимость проданных дисков каждого типа и в целом по магазину.
2. Измените отображение данных, оставив только итоги по каждому типу издания, а затем только общие итого по магазину. Восстановите прежний вид таблицы.
3. Отмените вычисление итоговых значений.
4. Определите сколько различных наименований дисков было продано в каждом месяце и среднюю выручку по каждому месяцу продаж.
5. На новом листе рабочей книги создайте сводную таблицу, взяв в качестве итоговой функции общую сумму от реализации дисков. Отобразите в таблице списки названий дисков для каждого типа и даты продаж.
6. Примените группировку для двух типов дисков.
7. С помощью редактирования итоговой функции сводной таблицы получите среднее количество проданных дисков.
8. Измените одно из значений в исходной таблице. Проанализируйте, изменится ли значение в сводной таблице.
9. Измените таблицу результатов, вынося значение поля Наименование диска в область страниц. Выберите отображение данных только по английскому языку.
10. Покажите работу преподавателю и сохраните рабочую книгу.
ариант 5
В качестве исходных данных используйте базовую таблицу из Лабораторной работы №5, предварительно скопировав ее в новую рабочую книгу.
1. Определите итоговые количество и стоимость товаров по каждой фирме – изготовителю и в целом по магазину.
2. Измените отображение данных, оставив только общие и промежуточные итоги. Восстановите прежний вид таблицы.
3. Отмените вычисление итоговых значений.
4. Определите количество партий товаров, поступивших в течение дня, и среднюю стоимость товаров в партии.
5. На новом листе рабочей книги создайте сводную таблицу, взяв в качестве итоговой функции суммарную стоимость товаров, поступивших в магазин. Отобразите в таблице списки наименований товаров для каждой фирмы производителя, и даты поступления.
7. С помощью редактирования итоговой функции сводной таблицы получите среднюю стоимость товаров в каждой партии.
8. Измените одно из значений в исходной таблице. Проанализируйте, изменится ли значение в сводной таблице.
9. Измените таблицу результатов, вынося значение поля Наименование товара в область страниц. Выберите отображение данных только по поступлениям принтеров.
10. Покажите работу преподавателю и сохраните рабочую книгу.
ариант 6
В качестве исходных данных используйте базовую таблицу из Лабораторной работы №5, предварительно скопировав ее в новую рабочую книгу.
1. Определите общую сумму выплат по каждому месяцу и в целом за квартал.
2. Измените отображение данных, оставив только общие и промежуточные итоги. Восстановите прежний вид таблицы.
3. Отмените вычисление итоговых значений.
4. Вставьте итоговые функции для определения количества сотрудников и среднюю зарплату по каждому отделу.
5. На новом листе рабочей книги создайте сводную таблицу, взяв в качестве итоговой функции общие выплаты работникам (сумму по полю Оклад ). Отобразите в таблице списки сотрудников по каждому отделу, и наименования месяцев начисления зарплаты.
6. Примените группировку для двух месяцев работы.
7. С помощью редактирования итоговой функции сводной таблицы получите среднюю зарплату каждого сотрудника.
8. Измените одно из значений поля Оклад в исходной таблице. Проанализируйте, изменится ли значение в сводной таблице.
9. Измените таблицу результатов, вынося значение поля Отдел в область страниц. Выберите отображение данных только по канцелярии.
10. Покажите работу преподавателю и сохраните рабочую книгу.
ариант 7
В качестве исходных данных используйте базовую таблицу из Лабораторной работы №5, предварительно скопировав ее в новую рабочую книгу.
1. Определите итоговые количество и стоимость отгруженного товара по каждому покупателю и по нефтебазе в целом.
2. Измените отображение данных, оставив только общие итоги по покупателям и нефтебазе в целом. Восстановите прежний вид таблицы.
3. Отмените вычисление итоговых значений.
4. Определите количество партий товара, отгруженного в течение каждого дня, и среднюю стоимость товаров в партии.
5. На новом листе рабочей книги создайте сводную таблицу, взяв в качестве итоговой функции суммарную стоимость нефтепродуктов, отгруженных с базы. Отобразите в таблице для каждого покупателя списки наименований товаров и даты отгрузок.
6. Примените группировку для двух наименований товаров.
7. С помощью редактирования итоговой функции сводной таблицы получите среднюю стоимость товара в каждой отгрузке.
8. Измените одно из значений в исходной таблице. Проанализируйте, изменится ли значение в сводной таблице.
9. Измените таблицу результатов, вынося значение поля Наименование товара в область страниц. Выберите отображение данных только по отгрузке нефти.
10. Покажите работу преподавателю и сохраните рабочую книгу.
ариант 8
В качестве исходных данных используйте базовую таблицу из Лабораторной работы №5, предварительно скопировав ее в новую рабочую книгу.
1. Определите общее количество и стоимость проданных гарнитуров по каждому типу мебели и в целом по магазину.
2. Измените отображение данных, оставив только итоги по каждому типу мебели и общие итого по магазину. Восстановите прежний вид таблицы.
3. Отмените вычисление итоговых значений.
4. Определите, сколько различных наименований товара было продано в течение дня и среднюю выручку по каждому дню продаж.
5. На новом листе рабочей книги создайте сводную таблицу, взяв в качестве итоговой функции общую сумму от реализации мебельной продукции. Отобразите в таблице списки названий товаров для каждого типа мебели, и даты продаж.
6. Примените группировку для двух типов мебели.
7. С помощью редактирования итоговой функции сводной таблицы получите среднее количество проданных гарнитуров каждого типа.
8. Измените одно из значений в исходной таблице. Проанализируйте, изменится ли значение в сводной таблице.
9. Измените таблицу результатов, вынося значение поля Тип в область страниц. Выберите отображение данных только по кухонным гарнитурам.
10. Покажите работу преподавателю и сохраните рабочую книгу.
ариант 9
В качестве исходных данных используйте базовую таблицу из Лабораторной работы №5, предварительно скопировав ее в новую рабочую книгу.
1. Определите общее количество и стоимость заказанных билетов по каждому тур.агентству.
2. Измените отображение данных, оставив только общие и промежуточные итоги. Восстановите прежний вид таблицы.
3. Отмените вычисление итоговых значений.
4. Определите, сколько различных стран прибытия фигурируют в заказах в течение дня, а для каждой даты - среднюю стоимость заказов.
5. На новом листе рабочей книги создайте сводную таблицу, взяв в качестве итоговой функции общую стоимость заказанных билетов. Отобразите в таблице данные по каждому туристическому агентству со списками стран прибытия и дат вылета.
6. Примените группировку для двух дат вылета.
7. С помощью редактирования итоговой функции сводной таблицы получите среднее количество заказанных билетов.
8. Измените одно из значений в исходной таблице. Проанализируйте, изменится ли значение в сводной таблице.
9. Измените таблицу результатов, вынося значение поля Страна прибытия в область страниц. Выберите отображение данных только по Египту.
10. Покажите работу преподавателю и сохраните рабочую книгу.
ариант 10
В качестве исходных данных используйте базовую таблицу из Лабораторной работы №5, предварительно скопировав ее в новую рабочую книгу.
1. С помощью вставки итоговой функции определите для каждого факультета количество студентов, сдающих экзамен.
2. Измените отображение данных, оставив только общие итоги по факультетам и по институту в целом. Восстановите прежний вид таблицы.
3. Отмените вычисление итоговых значений.
4. Определить средний балл и количество студентов, сдающих экзамен по каждому предмету.
5. На новом листе рабочей книги создайте сводную таблицу, взяв в качестве итоговой функции общий балл. Отобразите в таблице данные по каждому факультету: названия экзаменов и списки абитуриентов.
6. Примените инструмент группировки для двух факультетов.
7. С помощью редактирования итоговой функции сводной таблицы получите средний балл по предметам.
8. Измените одно из значений в исходной таблице. Проанализируйте, изменится ли значение в сводной таблице.
9. Измените отображение данных в сводной таблице, вынося значение поля Факультет в область страниц. Выберите отображение данных только по одному факультету.
10.Покажите работу преподавателю и сохраните рабочую книгу.
абораторная работа №7
ема: Консолидация данных.
Цель работы : Научиться приемам автоматического агрегирования (объединения) данных, расположенных в отдельных таблицах-источниках.
Порядок выполнения работы
1. В новой рабочей книге создайте таблицу Начисление заработной платы за январь месяц (см. рис.7.1.). Заполните данными столбцы Фамилия и Зар.плата и рассчитайте значения остальных ячеек, вставив формулы в соответствии со следующими соотношениями:
Подоходный налог = 0,12* 3ар.плата
Пенсионный фонд = 0,01* Зар.плата
Сумма к выдаче = Зар.плата – Подоход.налог – Пенсион.фонд
В конце таблицы вставьте итоговую функцию (строка Итого ).
2. Скопируйте созданную на Листе1 таблицу на Лист2 и Лист3, изменив названия месяцев в заголовках таблиц (за февраль , за март ), соответственно, и числа в столбце Зарплата . Переименуйте листы рабочей книги в Январь , Февраль и Март, соответственно.
3. Вставьте новый лист и назовите его Консол_расположение. Введите заголовок в ячейку А1: Ведомость начисления зар.платы за I квартал.
4. Создайте таблицу начисления зар.платы за I квартал, объединив данные за три месяца. Для этого:
Ссылка Январь, Февраль, Март, выделяйте блоки ячеек-источников A2:Е7 и нажимайте кнопку Добавить ;
§ чтобы в новой таблице отображались заголовки полей, установите флажки подписи верхней строки , значения левого столбца;
§ нажмите кнопку ОК.
5. Вставьте новый лист и назовите его Консол_категор. Введите заголовок в ячейку А1: Ведомость начисления зар.платы за I квартал.
6. Внесите изменения в таблицу на листе Январь : перед столбцом Выдано на руки вставьте столбец Премия и заполните его данными; измените расчетную формулу в столбце Выдано на руки , добавив к общей сумме величину премии.
7. Внесите изменения в таблицы на листах Март, Февраль : добавьте строку с фамилией нового сотрудника (Пригожин) и с соответствующими числами.
8. Перейдите на лист Консол_категор. и создайте таблицу начисления зар.платы за I квартал, объединив данные за три месяца. Для этого:
§ выделите ячейку А2 и выполните команду ДанныеÞКонсолидация;
§ выберите функцию для объединения данных (Сумма);
§ сформируйте список диапазонов консолидации: установите курсор в окне Ссылка и, переходя последовательно к листам Январь, Февраль, Март, выделяйте блоки ячеек-источников и нажимайте кнопку Добавить ;
§ установите флажки подписи верхней строки , значения левого столбца;
§ чтобы изменения в исходных таблицах отражались в сведенной (консолидируемой) таблице, установите флажок напротив опции Создавать связи с исходными данными ; нажмите кнопку ОК .
9. Внесите изменения в исходные таблицы-источники (измените величину зар.платы для нескольких сотрудников); посмотрите, отразились ли эти изменения на листе Консол_категор.
10. Вставьте новый лист в рабочую книгу и создайте на нем сводную таблицу начисления зарплаты за I квартал, состоящую из двух столбцов: Фамилия и Выдано на руки . Для этого:
§ введите заголовок таблицы в ячейку А1;
§ введите заголовки полей будущей таблицы Фамилия и Выдано на руки в ячейки А2 и В2, соответственно;
§ выделите ячейки А2:В2 и выполните консолидацию данных по категориям, аналогично п.8.
11. Сохраните рабочую книгу под именем Консолидация.xls и покажите результаты своей работы преподавателю.
Похожая информация.
Важное место в современном статистическом анализе социально-экономических явлений и процессов занимает графический метод. Без графиков не обходится ни одно статистическое исследование - они позволяют с наименьшими временными затратами выявить закономерности в развитии явления и его структуру, а также наглядно представить взаимосвязи показателей. Графический образ часто более нагляден и понятен, чем многие страницы текста. Арсенал используемых в статистике графиков обширен. Более того, с появлением новых программных средств он непрерывно увеличивается: на замену плоскостным графикам приходят объемные, матричные, категоризованные графики и пиктографики.
График - это схематичное изображение статистической информации с помощью различных геометрических образов, которыми могут быть линии, точки, плоскостные либо объемные фигуры (круги, прямоугольники и т.д.), символы со многими элементами (звезды, лучи, многоугольники, "лица Чернова", "японские свечи", "ящики с усами" и т.д.).
Любой статистический график содержит графический образ и вспомогательные элементы. Под графическим образом понимают совокупность выбранных для изображения конкретной статистической информации линий, фигур, точек или символов, имеющих определенный формат изображения. Вспомогательные элементы графика - это, во-первых, поле графика ( пространство , на котором располагается геометрический образ, при этом длина и ширина поля графика , как правило, имеют между собой определенное соотношение), во-вторых, система координат и масштабные ориентиры (декартовы, полярные координаты , контурные линии или сетки с нанесенной на них масштабной шкалой), и, в-третьих, экспликация графика , которая представляет собой необходимый разъяснительный текст, прилагаемый к графику: его название, подписи масштабных шкал, смысловое содержание применяемых символов и знаков (легенда графика ).
Статистические графики можно классифицировать по следующим признакам:
- аналитическое предназначение;
- способ построения;
- символы геометрического образа.
По аналитическому предназначению различают графики сравнения, структуры, динамики, изображения вариационных рядов, графики взаимосвязи показателей.
По способу построения графики делятся на диаграммы и статистические карты.
Согласно используемым символам геометрического образа графики бывают точечные, линейные, фигурные (плоскостные или объемные) и пиктографики.
Для сравнения одноименных показателей, относящихся к различным временным периодам, объектам или территориям, применяют линейные графики и различные виды диаграмм: столбиковую, ленточную, фигурную; а также пиктографики.
У линейного графика по оси абсцисс отмечаются временные периоды, объекты или территории, а по оси ординат - соответствующие им значения рассматриваемого показателя. Например, по данным табл. 4.10 построим линейный график изменения удельного веса убыточных организаций за период 2002-2006 гг. для экономики в целом (рис. 4.1).
| Отрасль экономики | Год | ||||
|---|---|---|---|---|---|
| 2002 | 2003 | 2004 | 2005 | 2006 | |
| Всего в экономике, в том числе: | 53,2 | 40,8 | 39,8 | 37,9 | 43,5 |
| промышленность | 48,8 | 39,1 | 39,7 | 39,3 | 45,1 |
| сельское хозяйство | 84,4 | 52,7 | 50,7 | 46,3 | 55,6 |
| строительство | 40,6 | 37,7 | 37,2 | 35,4 | 38,6 |
| транспорт | 53,4 | 47,9 | 44,1 | 40,9 | 45,6 |
| связь | 44,3 | 28,4 | 26,1 | 25,4 | 35,1 |
| торговля и общественное питание | 45,3 | 32,7 | 31,4 | 27,7 | 31,2 |
Рис.
4.1.
Столбиковая диаграмма несет тот же аналитический смысл, что и линейный график . При ее построении на оси X располагаются элементы, подлежащие сравнению, которыми могут быть временные периоды, территории, либо объекты. Они находятся на одинаковом расстоянии друг от друга. Затем рисуются прямоугольники (столбики): сторона, являющаяся шириной, одинакова для всех сравниваемых элементов и располагается на оси X, высота прямоугольников откладывается по оси Y пропорционально значению сравниваемого показателя. Таким образом, ось Y должна иметь определенную масштабную шкалу, обязательно начинающуюся с нуля. Так, используя данные табл. 4.10, построим столбиковую диаграмму изменения удельного веса убыточных предприятий и организаций по всей экономике в целом (рис. 4.2).
Прямоугольники столбиковой диаграммы могут располагаться и вплотную друг к другу - расстояние между ними определяется произвольно, масштаб имеет лишь высота прямоугольников.
Рис. 4.2.
Столбиковые диаграммы могут одновременно демонстрировать изменение нескольких показателей. Для примера изобразим динамику удельного веса убыточных предприятий и организаций по отраслям экономики за несколько временных периодов (рис. 4.3).
Рис. 4.3.
Для четырех отраслей построим линейный график (рис. 4.4).
Рис. 4.4.
Ленточная (полосовая) диаграмма строится по тем же правилам, что и столбиковая, но прямоугольники, изображающие размеры показателя, располагаются не вертикально, а горизонтально. Данный вид диаграммы удобно применять в тех случаях, когда сравниваемые показатели могут принимать отрицательные значения. Например, магазин детской одежды "Сашенька" в течение года имел не только прибыль (+), но и нес убытки (рис. 4.5).
Рис. 4.5.
Для получения диаграмм сравнения могут использоваться и различные геометрические фигуры. Предположим, что количество заключенных договоров личного страхования, заключенных страховой компанией, составляло в 2003 г. 23 тыс., в 2004 г. - 64 тыс. Изобразим эти данные графически, для чего выберем в качестве фигурного знака квадрат. Чтобы найти стороны квадратов нужно извлечь квадратные корни из значений показателей: Выберем масштаб изображения, например, примем 1 см равным 3 тыс. Тогда сторона первого квадрата будет равна (4,8: 3) 1,6 см; второго (8: 3) 2,7 см. Итак, получим следующую диаграмму сравнения (рис. 4.6).
Рис. 4.6.
Вместо квадратов часто используются круги. Тогда изображаемые величины должны быть пропорциональны площади круга. Наглядность данного вида диаграмм тем больше, чем сильнее различаются между собой сравниваемые показатели. Действительно, если различия небольшие, то подобный график теряет свой смысл.
В динамических сравнениях, особенно если приводятся данные по месяцам года и в них присутствуют так называемые сезонные колебания, используются радиальные диаграммы. Для этого вычерчивается круг такого радиуса, чтобы при нанесении на него масштабной шкалы верхнее значение шкалы соответствовало наибольшему значению показателя. Затем весь круг делится на 12 частей (если мы рассматриваем помесячные данные) и проставляются номера либо названия месяцев около каждого радиуса. После этого на них откладываются в принятом масштабе значения показателей соответствующих месяцев, и полученные точки соединяются отрезками - образуется замкнутая ломаная линия. Пример построения радиальной диаграммы приведен на рис. 4.7.
Рис. 4.7.
Для изображения структуры явления используются прямоугольные или секторные диаграммы.
Продемонстрируем построение круговой секторной диаграммы на данных табл. 4.11.
Для того чтобы построить секторную диаграмму, необходимо определить величину углов секторов: 100% соответствует 360°, тогда 1% равен 3,6°. Пересчитаем наши данные:
- жилища: 12,8 * 3,6 = 46°;
- здания (кроме жилых) и сооружения: 41,9 * 3,6 = 151°;
- машины, оборудование, транспортные средства: 38,9 * 3,6 = 140°;
- прочие виды основных фондов: 6,4 * 3,6 = 23°.
Начертим круг произвольного радиуса и разделим его на четыре соответствующих сектора (рис. 4.8).
Рис. 4.8.
Изобразить графически структуру явления можно также с помощью ленточных (полосовых) диаграмм. В этом случае вычерчивается прямоугольник произвольной длины и ширины. Значение его длины принимается за 100%. Затем прямоугольник делится на части, соответствующие значениям долей тех компонент , из которых состоит явление. Так, по данным табл. 4.10 получим полосовую диаграмму, представленную на рис. 4.9.
Рис. 4.9.
Для одновременного изображения трех величин, одна из которых является произведением двух других, применяется особый график , называемый знаком Варзара. Поясним процедуру его построения на условном примере.
Знак Варзара имеет вид прямоугольника, длина и ширина которого соответствуют двум множителям произведения, а площадь - значению произведения, т.д. третьей величине. Так, в табл. 4.12 показатель "Капитализация" рассчитан как произведение рыночной стоимости акции на количество акций данного вида:
Основание прямоугольников примем за показатель количества, а высоту - за цену. Тогда площадь полученных прямоугольников будет изображать капитализацию. При построении знаков Варзара следует помнить, что основание и высота прямоугольников откладываются в своем масштабе независимо друг от друга (рис. 4.10).
Рис. 4.10.
Особое место в графическом анализе финансовой информации занимают биржевые статистические графики.
Для анализа данных фондовых, товарных и фьючерсных рынков чаще всего используют столбиковые биржевые графики (табл. 4.13).
| Дата торгов | Цена открытия | Максимальная цена дня | Минимальная цена дня | Цена закрытия | Объем торгов |
|---|---|---|---|---|---|
| 26 | 14,3 | 14,9 | 14,3 | 14,7 | 102 548 |
| 27 | 14,7 | 15,2 | 14,6 | 14,9 | 112 054 |
| 28 | 14,9 | 15,5 | 14,5 | 15,3 | 136 250 |
| 29 | 15,3 | 16,1 | 14,9 | 15,1 | 108 914 |
| 30 | 15,1 | 15,8 | 14,7 | 15,6 | 103 145 |
По данным табл. 4.13 построим столбиковый биржевой график (рис. 4.11).
На столбиковом биржевом графике для каждого дня строится вертикальная черта (столбик): начало столбика соответствует значению минимальной в течение дня цены на акцию, вершина - максимальной цене, горизонтальная черта на столбике - цена в момент закрытия торгов.
Рис. 4.11.
Для одновременного изображения цен открытия и закрытия торгов, а также минимального и максимального значений цены служит график , часто называемый в литературе "ящики с усами". Для данных табл. 4.13 он выглядит так, как показано на рис. 4.12.
Рис. 4.12. "Ящики с усами"
Здесь, в отличие от графика , приведенного на рис. 4.11, у каждого столбика имеется еще и "ящик" (отсюда и название - "ящики с усами"). Основание белого "ящика" соответствует цене открытия торгов, высота - цене закрытия; черный цвет "ящика" означает, что цена закрытия была ниже цены открытия торгов - в этом случае на графике они меняются местами.
Столбиковый график можно дополнить диаграммами показателя объема торгов. Для данных табл. 4.13 получим графический образ, представленный на рис. 4.13.
Рис. 4.13.
Поскольку график дополняется диаграммами, показывающими объемы торгов, то он имеет две вертикальные масштабные шкалы: слева находится шкала для показателя объема торгов, справа - для котировок акций.
График "ящики с усами" также можно дополнить диаграммами показателя объема торгов (рис. 4.14).
Рис. 4.14. Биржевой график "ящики с усами" с диаграммой объемов торгов
В современных статистических пакетах прикладных программ для графического представления статистической информации предлагается особый вид графиков - пиктографики.
Пиктографики составляются для каждого наблюдения, они имеют вид графических объектов (определенных символов) со многими элементами. Значения показателей соответствуют свойствам или размерам элементов пиктографика. С изменением значений показателей при переходе от одной единицы наблюдения к другой внешний вид пиктограммы меняется. Таким образом возникает возможность визуально классифицировать наблюдения по однородным группам.
Предположим, что имеется совокупность 10 промышленных предприятий, характеризующихся следующими показателями (табл. 4.14).
| Номер предприятия | Рентабельность, % | Удельный вес рабочих в составе промышленного производственного персонала, % | Коэффициент сменности оборудования | Удельный вес потерь от брака, % | Фондоотдача на 1 руб. фондов | Среднегодовая численность промышленного производственного персонала, чел. | Среднегодовая стоимость основных производственных фондов, млн. руб. | Оборачиваемость нормируемых оборотных средств, дн. | Оборачиваемость ненормируемых оборотных средств, дн. | Непроизводственные расходы, млн. руб. |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 13,28 | 0,80 | 1,14 | 0,27 | 1,07 | 1 257 | 50,79 | 80,12 | 22,46 | 18,20 |
| 2 | 22,31 | 0,80 | 1,85 | 0,38 | 2,45 | 1 687 | 58,12 | 80,12 | 22,37 | 38,45 |
| 3 | 15,27 | 0,78 | 1,14 | 0,26 | 1,14 | 1 588 | 44,20 | 80,45 | 21,74 | 22,13 |
| 4 | 12,99 | 0,79 | 1,33 | 0,28 | 1,05 | 1 696 | 44,57 | 68,17 | 20,11 | 24,56 |
| 5 | 25,78 | 0,78 | 1,74 | 0,29 | 2,12 | 1 804 | 51,43 | 70,82 | 20,37 | 46,75 |
| 6 | 28,47 | 0,79 | 1,90 | 0,30 | 2,09 | 1 512 | 53,96 | 73,47 | 21,38 | 38,16 |
| 7 | 12,97 | 0,80 | 1,16 | 0,35 | 1,03 | 1 499 | 57,58 | 76,12 | 21,52 | 24,58 |
| 8 | 23,47 | 0,81 | 1,86 | 0,32 | 2,11 | 1 403 | 65,34 | 78,77 | 23,58 | 41,78 |
| 9 | 10,47 | 0,81 | 1,17 | 0,33 | 0,87 | 1 451 | 59,34 | 81,42 | 22,47 | 22,79 |
| 10 | 13,58 | 0,82 | 1,23 | 0,32 | 0,97 | 1 327 | 57,83 | 84,07 | 23,17 | 22,47 |
Проанализируем имеющуюся информацию графически с помощью пиктографиков.