Практические работы по olap технология в excel. Общие сведения об аналитической обработке в режиме реального времени (OLAP). Некоторые сведения о кубах OLAP и моделях данных Power Pivot

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

Если исходные данные содержатся в настольной СУБД, вычисление агрегатных данных производится самим OLAP-средством. Если же источник исходных данных - серверная СУБД, многие из клиентских OLAP-средств посылают на сервер SQL-запросы, содержащие оператор GROUP BY, и в результате получают агрегатные данные, вычисленные на сервере.

Как правило, OLAP-функциональность реализована в средствах статистической обработки данных (из продуктов этого класса на российском рынке широко распространены продукты компаний StatSoft и SPSS) и в некоторых электронных таблицах. В частности, средствами многомерного анализа обладает Microsoft Excel. С помощью этого продукта можно создать и сохранить в виде файла небольшой локальный многомерный OLAP-куб и отобразить его двух- или трехмерные сечения.

Надстройки к пакету приложений Microsoft Office для извлечения и обработки данных представляют собой ряд функций, обеспечивающих доступ к возможностям извлечения и обработки данных из приложений Microsoft Office, и тем самым позволяющих осуществлять прогностический анализ на локальном компьютере. Благодаря тому, что встроенные в службы платформы Microsoft SQL Server алгоритмы извлечения и обработки данных доступны из среды приложений Microsoft Office, бизнес-пользователи могут легко извлекать ценную информацию из сложных наборов данных всего несколькими щелчками мыши. Надстройки к пакету приложений Office для извлечения и обработки данных дают конечным пользователям возможность выполнять анализ непосредственно в приложениях Microsoft Excel и Microsoft Visio.

В состав Microsoft Office 2007 входят три отдельных OLAP-компонента:

  1. клиент извлечения и обработки данных для Excel позволяет создавать проекты извлечения и обработки данных на базе служб SSAS и управлять ими из Excel 2007;
  2. средства анализа таблиц для приложения Excel позволяют использовать встроенные в службы SSAS функции извлечения и обработки информации для анализа данных, хранящихся в таблицах Excel;
  3. шаблоны извлечения и обработки данных для приложения Visio позволяют визуализировать деревья решений, деревья регрессии, кластерные диаграммы и сети зависимостей на диаграммах Visio.
Таблица 1.1. Продукты Oracle для OLAP и бизнес-анализа
Тип средств Продукт

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

Термины многомерного анализа данных

Большинство экономистов слышали термины «многомерные данные», «виртуальный куб», «OLAP-технологии» и т.п. Но при детальном разговоре обычно выясняется, что почти все не очень представляют, о чем идет речь. То есть люди подразумевают нечто сложное и обычно не имеющее отношение к их повседневной деятельности. На самом деле это не так.

Многомерные данные, измерения

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

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

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

  • объем продаж;
  • цена продажи;
  • индивидуальная скидка
  • и т.п.

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

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

OLAP

Аббревиатура OLAP (online analytical processing) в дословном переводе звучит как «аналитическая обработка в реальном времени». Определение не очень конкретное, под него можно подвести практически любой отчет любого программного продукта. По смыслу OLAP подразумевает технологию работы со специальными отчетами, включая программное обеспечение, для получения и анализа как раз многомерных структурированных данных. Одним из популярных программных продуктов, реализующих OLAP-технологии, является SQL Server Analysis Server. Некоторые даже ошибочно считают его единственным представителем программной реализации данной концепции.

Виртуальный куб данных

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

Сводная таблица

«Сводный отчет» (сводная таблица, Pivot Table) - это пользовательский интерфейс для отображения многомерных данных. С помощью данного интерфейса можно группировать, сортировать, фильтровать и менять расположение данных с целью получения различных аналитических выборок. Обновление отчета производится простыми средствами пользовательского интерфейса, данные автоматически агрегируются по заданным правилам, при этом не требуется дополнительный или повторный ввод какой-либо информации. Интерфейс сводных таблиц Excel является, пожалуй, самым популярным программным продуктом для работы с многомерными данными. Он поддерживает в качестве источника данных как внешние источники данных (OLAP-кубам и реляционным базам данных), так и внутренние диапазоны электронных таблиц. Начиная с версии 2000 (9.0), Excel поддерживает также графическую форму отображения многомерных данных – сводная диаграмма (Pivot Chart).

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

Еще раз хочется обратить внимание, что сводная таблица Excel предназначена исключительно для анализа данных без возможности редактирования информации. Ближе по смыслу было бы повсеместное употребление термина «сводный отчет» (Pivot Report), и именно так этот интерфейс и назывался до 2000го года. Но почему-то в последующих версиях разработчики от него отказались.

Редактирование сводных таблиц

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

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

Подготовка многомерных данных

Подойдем к практическому применению сводных таблиц. Попробуем проанализировать данные о продажах в различных направлениях. Файл pivottableexample.xls состоит из нескольких листов. Лист Пример содержит основную информацию о продажах за определенный период. Для простоты примера будем анализировать единственный числовой показатель – объем продажи в кг. Имеются следующие ключевые измерения данных: продукция, покупатель и перевозчик (транспортная компания). Кроме того, имеются несколько дополнительных измерений данных, являющихся признаками продукта: тип, бренд, категория, поставщик, а также покупателя: тип. Эти данные собраны на листе Справочники. На практике подобных измерений может быть гораздо больше.

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

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

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

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

От автофильтра к сводному отчету

Теоретически на данных листа Продажи уже можно проводить анализ в трех измерениях: товары, покупатели и перевозчики. Данные о свойствах продукции и покупателей на данном листе отсутствуют, что, соответственно, не позволит показать их и в сводной таблице. В нормальном режиме создания сводной таблицы для исходных данных Excel не позволяет связывать данные нескольких таблиц по определенным полям. Обойти это ограничение можно программными средствами – см. пример-дополнение к данной статье на нашем сайте. Чтобы не прибегать к программным методам обработки информации (тем более, что они и не универсальны), следует добавить дополнительные характеристики непосредственно в форму ввода журнала – см. лист ПродажиАнализ.

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

Теперь посмотрим какие возможности дает интерфейс сводных таблиц. На листе СводАнализ построено несколько отчетов на основе диапазона ячеек с данными листа ПродажиАнализ .

Первая таблица анализа построена через интерфейс Excel 2007 Лента \ Вставка \ Сводная таблица (в Excel 2000-2003 меню Данные \ Сводная таблица ).

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

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

Свойства и форматирование

Кроме непосредственного отображения данных, имеется большой набор возможностей по отображению внешнего вида сводных таблиц. Лишние данные можно скрывать, используя фильтры. Для единичного элемента или поля проще пользоваться пунктом контекстного меню Удалить (в версии 2000-2003 Скрыть ).

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

Кроме того, в Excel 2007 появилось множество предопределенных стилей отображения сводной таблицы:

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

Доступ к внешним данным

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

Excel поддерживает множество типов источников внешних данных:

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

Удивительное - рядом...

По ходу работы мне часто требовалось делать сложные отчеты, я все время пытался найти в них что-то общее, чтобы составлять их более просто и универсально, даже написал и опубликовал по этому поводу статью «Дерево Осипова». Однако мою статью раскритиковали и сказали, что все те проблемы, которые я поднял, давно уже решены в MOLAP.RU v.2.4 (www.molap.rgtu.ru) и порекомендовали посмотреть сводные таблицы в EXCEL.
Это оказалось настолько простым, что приложив к этому свои гениальные ручонки, у меня получилась очень простая схема для выгрузки данных из 1С7 или любой другой базы данных (в дальнейшем под 1С подразумевается любая база данных) и анализа в OLAP.
Я думаю, многие схемы выгрузки в OLAP слишком усложнены, я выбираю простоту.

Характеристики :

1. Для работы требуется только EXCEL 2000.
2. Пользователь сам может конструировать отчеты без программирования.
3. Выгрузка из 1С7 в простом формате текстового файла.
4. Для бухгалтерских проводок уже имеется универсальная обработка для выгрузки, работающая в любой конфигурации. Для выгрузки других данных имеются обработки-образцы.
5. Можно заранее сконструировать формы отчетов, а затем применять их к разным данным без их повторного конструирования.
6. Довольно хорошая производительность. На первом длительном этапе данные сначала импортируются в EXCEL из текстового файла и строится куб OLAP, а затем довольно быстро на основе этого куба может быть построен любой отчет. Например, данные о продажах товара по магазину за 3 месяца с ассортиментом 6000 товаров, загружаются в EXCEL 8 минут на Cel600-128M, рейтинг по товарам и группам (OLAP-отчет) пересчитывается за 1 минуту.
7. Данные выгружаются из 1С7 полностью за указанный период (все движения, по всем складам, фирмам, счетам). При импорте в EXCEL возможно использование фильтров, загружающих для анализа только нужные данные (например, из всех движений, только продажи).
8. В настоящее время разработаны способы анализа движений или остатков, но не движений и остатков вместе, хотя это в принципе возможно.

Что такое OLAP : (www.molap.rgtu.ru)

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

Дата - дата операции
Месяц - месяц операции
Неделя - неделя операции
Вид - закуп, продажа, возврат, списание
Контрагент - внешняя организация, участвующая в операции
Автор - человек, выписавший накладную

В 1С, например, одна строка этой таблицы будет соответствовать одной строке накладной, некоторые поля (Контрагент, Дата) при этом берутся из шапки накладной.

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

Эта таблица является исходной для OLAP-анализа.

Отчет

Измерения

Данные

Фильтр

Сколько товара и на какую сумму продается за день?

Дата, Товар

Количество, Сумма

Вид="продажа"

Какие контрагенты поставили какой товар на какую сумму помесячно?

Месяц, Контрагент, Товар

Сумма

Вид="закуп"

На какую сумму выписали операторы накладных какого вида за весь период отчета?

Сумма

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


Как использовать у себя :

Данные из дистрибутива распаковать именно в каталог c:\fixin (для торговой системы возможно в c:\reports) . Прочитайте readme.txt и выполните все инструкции в нем.

Сначала вы должны написать обработку, которая выгружает данные из 1С в текстовый файл (таблицу). Вам нужно определить состав полей, которые будут выгружаться.
Например, уже готовая универсальная обработка, которая работает в любой конфигурации и выгружает для OLAP-анализа проводки за период, выгружает для анализа следующие поля:

Дата|ДеньНедели|Неделя|Год|Квартал|Месяц|Документ|Фирма|Дебет|ДтНоменклатура
|ДтГруппаНоменклатура|ДтРазделНоменклатура|Кредит|Сумма|ВалСумма|Количество
|Валюта|ДтКонтрагенты|ДтГруппаКонтрагенты|КтКонтрагенты|КтГруппаКонтрагенты|
КтРазныеОбъекты

Где под префиксами Дт(Кт) идут субконто Дебета (Кредита), Группа - это группа данного субконто (если имеется), Раздел - группа группы, Класс - группа раздела.

Для торговой системы поля могут быть такие:

Направление|ВидДвижения|ЗаНал|Товар|Количество|Цена|Сумма|Дата|Фирма
|Склад|Валюта|Документ|ДеньНедели|Неделя|Год|Квартал|Месяц|Автор
|КатегорияТовара|КатегорияДвижения|КатегорияКонтрагента|ГруппаТовара
|ВалСумма|Себестоимость|Контрагент

Для анализа данных используются таблицы "Анализ движений.xls" ("Анализ бухгалтерии.xls"). Открывая их, не отключайте макросы, иначе вы не сможете обновлять отчеты (они запускаются макросами на языке VBA). Исходные данные эти файлы берут из файлов C:\fixin\motions.txt (C:\fixin\buh.txt), в остальном они одинаковые. Поэтому возможно, вам придется скопировать ваши данные в один из этих файлов.
Чтобы в EXCEL загрузились ваши данные, выберите или напишите свой фильтр и нажмите кнопку "Сформировать" на листе "Условия".
Листы отчетов начинаются префиксом "Отч". Перейдите на лист отчета, нажмите "Обновить" и данные отчета изменятся в соответствии с последними загруженными данными.
Если вас не устраивают стандартные отчеты, есть лист ОтчШаблон. Скопируйте его в новый лист и настройте вид отчета, работая со сводной таблицей на этом листе (о работе со сводными таблицами - в любой книге по EXCEL 2000). Рекомендую настраивать отчеты на небольшом наборе данных, а затем уже запускать их на большом массиве, т.к. нет никакой возможности отключить перерисовку таблиц при каждом изменении макета отчета.

Технические комментарии :

При выгрузке данных из 1С пользователь выбирает папку, куда ему выгружать файл. Я сделал это потому, что вполне вероятно в ближайшем будущем будут выгружаться несколько файлов (остатки и движения). Затем по нажатию в Проводнике кнопки "Отправить" --> "На OLAP-анализ в EXCEL 2000" данные копируются из выбранной папки в папку C:\fixin. (чтобы эта команда появилась в списке команды "Отправить" и нужно скопировать файл "На OLAP-анализ в EXCEL 2000.bat" в каталог C:\Windows\SendTo) Поэтому выгружайте данные сразу давая имена файлам motions.txt или buh.txt.

Формат текстового файла:
Первая строка текстового файла - заголовки колонок разделенные "|", остальные строки содержат значения этих колонок, разделенные "|".

Для импорта текстовых файлов в Excel используется Microsoft Query (составная часть EXCEL) для его работы необходимо наличие в каталоге импорта (C:\fixin) файла shema.ini, содержащего следующую информацию:


ColNameHeader=True
Format=Delimited(|)
MaxScanRows=3
CharacterSet=ANSI
ColNameHeader=True
Format=Delimited(|)
MaxScanRows=3
CharacterSet=ANSI

Пояснение: motions.txt и buh.txt - это название раздела, соответствует имени импортируемого файла, описывает, как импортировать текстовый файл в Эксель. Остальные параметры означают, что первая строка содержит названия колонок, разделителем колонок является "|", набор символов - Windows ANSI (для ДОС - OEM).
Тип полей определяется автоматически исходя из содержащихся в колонке данных (дата, число, строка).
Перечень полей не нужно нигде описывать - EXCEL и OLAP сами определят, какие поля содержатся в файле по заголовкам в первой строке.

Внимание, проверьте ваши региональные настройки "Панель управления" --> "Региональные настройки" . В моих обработках числа выгружаются с разделителем запятая, а даты в формате "ДД.ММ.ГГГГ".

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

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

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

Описание работы в EXCEL (для пользователей):

Инструкция по использованию отчетов:
1. Отправьте на анализ выгруженные данные (уточните у администратора). Для этого нажмите правой кнопкой на папке, в которую у вас выгрузились данные из 1С и выберите команду "Отправить", затем "На OLAP-анализ в EXCEL 2000".
2. Откройте файл "Анализ движений.xls"
3. Выберите Значение фильтра, нужные вам фильтры можно дописать на закладке "Значения".
4. Нажмите кнопку "Сформировать", при этом выгруженные данные будут загружены в EXCEL.
5. После загрузки данных в EXCEL, можно смотреть различные отчеты. Для этого достаточно нажать кнопку "Обновить" в выбранном отчете. Листы с отчетами начинаются на Отч.
Внимание! После того как вы поменяете значение фильтра, нужно еще раз нажать кнопку "Сформировать", чтобы данные в EXCEL перезагрузились из файла выгрузки в соответствие с фильтрами.

Обработки из демо-примера:

Обработка motionsbuh2011.ert – последняя версия выгрузки проводок из Бухгалтерии 7.7 для анализа в Excel . В ней есть галочка «Присоединить в файл», которая позволяет выгружать данные частями по периодам, присоединяя их в один и тот же файл, а не выгружая в один и тот же файл заново:

Обработка motionswork.ert выгружает данные о продажах для анализа в Excel.

Примеры отчетов :

Шахматка по проводкам:

Загруженность операторов по видам накладных:

P.S. :

Понятно, что по аналогичной схеме можно организовать выгрузку данных из 1С8.
В 2011 году ко мне обращался пользователь, которому нужно было доработать эту обработку в 1С7, чтобы она выгружала большие объемы данных, я нашел аутсорсера и выполнил эту работу. Так что разработка вполне актуальна.

Обработка motionsbuh2011.ert доработана, чтобы справляться с выгрузкой большого объема данных.

Выберите документ из архива для просмотра:

18.5 КБ автомобили.xls

14 КБ страны.xls

Excel пр.р. 1.docx

Библиотека
материалов

Практическая работа 1

«Назначение и интерфейс MS Excel»

Выполнив задания этой темы, вы:

1. Научитесь запускать электронные таблицы;

2. Закрепите основные понятия: ячейка, строка, столбец, адрес ячейки;

3. Узнаете как вводить данные в ячейку и редактировать строку формул;

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

Задание: Познакомиться практически с основными элементами окна MS Excel.

    Запустите программу Microsoft Excel. Внимательно рассмотрите окно программы.

Документы, которые создаются с помощью EXCEL , называются рабочими книгами и имеют расширение . XLS . Новая рабочая книга имеет три рабочих листа, которые называются ЛИСТ1, ЛИСТ2 и ЛИСТ3. Эти названия указаны на ярлычках листов в нижней части экрана. Для перехода на другой лист нужно щелкнуть на названии этого листа.

Действия с рабочими листами:

    Переименование рабочего листа. Установить указатель мыши на корешок рабочего листа и два раза щелкнуть левой клавишей или вызвать контекстное меню и выбрать команду Переименовать. Задайте название листа "ТРЕНИРОВКА"

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

    Удаление рабочего листа. Выделить ярлычок листа "Лист 2", и с помощью контекстного меню удалите .

Ячейки и диапазоны ячеек.

Рабочее поле состоит из строк и столбцов. Строки нумеруются числами от 1 до 65536. Столбцы обозначаются латинскими буквами: А, В, С, …, АА, АВ, … , IV , всего – 256. На пересечении строки и столбца находится ячейка. Каждая ячейка имеет свой адрес: имя столбца и номер строки, на пересечении которых она находится. Например, А1, СВ234, Р55.

Для работы с несколькими ячейками их удобно объединять их в «диапазоны».

Диапазон – это ячейки, расположенные в виде прямоугольника. Например, А3, А4, А5, В3, В4, В5. Для записи диапазона используется « : »: А3:В5

8:20 – все ячейки в строках с 8 по 20.

А:А – все ячейки в столбце А.

Н:Р – все ячейки в столбцах с Н по Р.

В адрес ячейки можно включать имя рабочего листа: Лист8!А3:В6.

2. Выделение ячеек в Excel

Что выделяем

Действия

Одну ячейку

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

Строку

Щелчок на номере строки.

Столбец

Щелчок на имени столбца.

Диапазон ячеек

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

Несколько диапазонов

Выделить первый, нажать SCHIFT + F 8, выделить следующий.

Всю таблицу

Щелчок на кнопке «Выделить все» (пустая кнопка слева от имен столбцов)

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

Воспользуйтесь полосами прокрутки для того, чтобы определить сколько строк имеет таблица и каково имя последнего столбца.
Внимание!!! Чтобы достичь быстро конца таблицы по горизонтали или вертикали, необходимо нажать комбинации клавиш: Ctrl+→ - конец столбцов или Ctrl+↓ - конец строк. Быстрый возврат в начало таблицы - Ctrl+Home.

В ячейке А3 Укажите адрес последнего столбца таблицы.

Сколько строк содержится в таблице? Укажите адрес последней строки в ячейке B3.

3. В EXCEL можно вводить следующие типы данных:

    Числа.

    Текст (например, заголовки и поясняющий материал).

    Функции (например, сумма, синус, корень).

    Формулы.

Данные вводятся в ячейки. Для ввода данных нужную ячейку необходимо выделить. Существует два способа ввода данных:

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

    Щелкнуть в ячейке и в строке формул и ввести данные в строку формул.

Нажать ENTER .

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

4. Изменение данных.

    Выделить ячейку и нажать F 2 и изменить данные.

    Выделить ячейку e щелкнуть в строке формул и изменить данные там.

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

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

5. Ввод формул.

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

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

Действие

Примеры

+

Сложение

А1+В1

-

Вычитание

А1 - В2

*

Умножение

В3*С12

/

Деление

А1 / В5

Возведение в степень

А4 ^3

=, <,>,<=,>=,<>

Знаки отношений

А2

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

    Автозаполнение.

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

    Введите в первую ячейку нужный месяц, например январь.

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

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

Если необходимо заполнить какой-то числовой ряд, то нужно в соседние две ячейки ввести два первых числа (например, в А4 ввести 1, а в В4 – 2), выделить эти две ячейки и протянуть за маркер область выделения до нужных размеров.

Выбранный для просмотра документ Excel пр.р. 2.docx

Библиотека
материалов

Практическая работа 2

«Ввод данных и формул в ячейки электронной таблицы MS Excel»

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

Задание: Выполните в таблице ввод необходимых данных и простейшие расчеты.

Технология выполнения задания:

1. Запустите программу Microsoft Excel.

2. В ячейку А1 Листа 2 введите текст: "Год основания школы". Зафиксируйте данные в ячейке любым известным вам способом.

3. В ячейку В1 введите число –год основания школы (1971).

4. В ячейку C1 введите число –текущий год (2016).

Внимание! Обратите внимание на то, что в MS Excel текстовые данные выравниваются по левому краю, а числа и даты – по правому краю.

5. Выделите ячейку D1 , введите с клавиатуры формулу для вычисления возраста школы: = C1- B1

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

6. Удалите содержимое ячейки D1 и повторите ввод формулы с использованием мышки. В ячейке D1 установите знак «=» , далее щелкните мышкой по ячейке C1, обратите внимание адрес этой ячейки появился в D1, поставьте знак «–» и щелкните по ячейке B1 , нажмите {Enter}.

7. В ячейку А2 введите текст "Мой возраст".

8. В ячейку B2 введите свой год рождения.

9. В ячейку С2 введите текущий год.

10. Введите в ячейку D2 формулу для вычисления Вашего возраста в текущем году (= C2- B2).

11. Выделите ячейку С2. Введите номер следующего года. Обратите внимание, перерасчет в ячейке D2 произошел автоматически.

12. Определите свой возраст в 2025 году. Для этого замените год в ячейке С2 на 2025.

Самостоятельная работа

Упражнение: Посчитайте, используя ЭТ, хватит ли вам 130 рублей, чтоб купить все продукты, которые вам заказала мама, и хватит ли купить чипсы за 25 рублей?

Технология выполнения упражнения:
o В ячейку А1 вводим “№”
o В ячейки А2, А3 вводим “1”, “2”, выделяем ячейки А2,А3, наводим на правый нижний угол (должен появиться черный крестик), протягиваем до ячейки А6
o В ячейку В1 вводим “Наименование”
o В ячейку С1 вводим “Цена в рублях”
o В ячейку D1 вводим “Количество”
o В ячейку Е1 вводим “Стоимость” и т.д.
o В столбце “Стоимость” все формулы записываются на английском языке!
o В формулах вместо переменных записываются имена ячеек.
o После нажатия Enter вместо формулы сразу появляется число – результат вычисления

o Итого посчитайте самостоятельно.

Результат покажите учителю!!!

Выбранный для просмотра документ Excel пр.р. 3.docx

Библиотека
материалов

Практическая работа 3

«MS Excel. Создание и редактирование табличного документа»

Выполнив задания этой темы, вы научитесь:

Создавать и заполнять данными таблицу;

Форматировать и редактировать данные в ячейке;

Использовать в таблице простые формулы;

Копировать формулы.

Задание:

1. Создайте таблицу, содержащую расписание движения поездов от станции Саратов до станции Самара. Общий вид таблицы «Расписание» отображен на рисунке.

2. Выберите ячейку А3 , замените слово «Золотая» на «Великая» и нажмите клавишу Enter .

3. Выберите ячейку А6 , щелкните по ней левой кнопкой мыши дважды и замените «Угрюмово» на «Веселково»

4. Выберите ячейку А5 зайдите в строку формул и замените «Сенная» на «Сенная 1».

5. Дополните таблицу «Расписание» расчетами времени стоянок поезда в каждом населенном пункте. (вставьте столбцы) Вычислите суммарное время стоянок, общее время в пути, время, затрачиваемое поездом на передвижение от одного населенного пункта к другому.

Технология выполнения задания:

1. Переместите столбец «Время отправления» из столбца С в столбец D. Для этого выполните следующие действия:

Выделите блок C1:C7; выберите команду Вырезать .
Установите курсор в ячейку D1;
Выполните команду Вставить ;
Выровняйте ширину столбца в соответствии с размером заголовка.;

2. Введите текст «Стоянка» в ячейку С1. Выровняйте ширину столбца в соответствии с размером заголовка.

3. Создайте формулу, вычисляющую время стоянки в населенном пункте.

4. Необходимо скопировать формулу в блок С4:С7, используя маркер заполнения. Для этого выполните следующие действия:
Вокруг активной ячейки имеется рамка, в углу которой есть маленький прямоугольник, ухватив его, распространите формулу вниз до ячейки С7.

5. Введите в ячейку Е1 текст «Время в пути». Выровняйте ширину столбца в соответствии с размером заголовка.

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

7. Измените формат чисел для блоков С2:С9 и Е2:Е9. Для этого выполните следующие действия:

Выделите блок ячеек С2:С9;
Главная – Формат – Другие числовые форматы - Время и установите параметры (часы:минуты) .

Нажмите клавишу Ок .

8. Вычислите суммарное время стоянок.
Выберите ячейку С9;
Щелкните кнопку Автосумма на панели инструментов;
Подтвердите выбор блока ячеек С3:С8 и нажмите клавишу Enter .

9. Введите текст в ячейку В9. Для этого выполните следующие действия:

Выберите ячейку В9;
Введите текст «Суммарное время стоянок». Выровняйте ширину столбца в соответствии с размером заголовка.

10. Удалите содержимое ячейки С3.

Выберите ячейку С3;
Выполните команду основного меню Правка – Очистить или нажмите Delete на клавиатуре;
Внимание! Компьютер автоматически пересчитывает сумму в ячейке С9!!!

Выполните команду Отменить или нажмите соответствующую кнопку на панели инструментов.

11. Введите текст «Общее время в пути» в ячейку D9.

12. Вычислите общее время в пути.

13. Оформите таблицу цветом и выделите границы таблицы.

Самостоятельная работа

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

Выбранный для просмотра документ Excel пр.р. 4.docx

Библиотека
материалов

Практическая работа 4

"Ссылки. Встроенные функции MS Excel".

Выполнив задания этой темы, вы научитесь:

    Выполнять операции по копированию, перемещению и автозаполнению отдельных ячеек и диапазонов.

    Различать виды ссылок (абсолютная, относительная, смешанная)

    Использовать в расчетах встроенные математические и статистические функции Excel.

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

Таблица. Встроенные функции Excel

* Записывается без аргументов.

Таблица . Виды ссылок

Задание.

1. Заданы стоимость 1 кВт./ч. электроэнергии и показания счетчика за предыдущий и текущий месяцы. Необходимо вычислить расход электроэнергии за прошедший период и стоимость израсходованной электроэнергии.

Технология работы:

1. Выровняйте текст в ячейках. Выделите ячейки А3:Е3. Главная - Формат –Формат ячейки – Выравнивание: по горизонтали – по центру, по вертикали – по центру, отображение – переносить по словам.

2. В ячейку А4 введите: Кв. 1, в ячейку А5 введите: Кв. 2. Выделите ячейки А4:А5 и с помощью маркера автозаполнения заполните нумерацию квартир по 7 включительно.

5. Заполните ячейки B4:C10 по рисунку.

6. В ячейку D4 введите формулу для нахождения расхода эл/энергии. И заполните строки ниже с помощью маркера автозаполнения.

7. В ячейку E4 введите формулу для нахождения стоимости эл/энергии =D4*$B$1 . И заполните строки ниже с помощью маркера автозаполнения.

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

8. В ячейке А11 введите текст «Статистические данные» выделите ячейки A11:B11 и щелкните на панели инструментов кнопку «Объединить и поместить в центре».

9. В ячейках A12:A15 введите текст, указанный на рисунке.

10. Щелкнуть мышью по ячейке B12 и ввести математическую функцию СУММ , для этого необходимо щелкнуть в строке формул по знаку fx и выбрать функцию, а также подтвердить диапазон ячеек.

11. Аналогично функции задаются и в ячейках B13:B15.

12. Расчеты вы выполняли на Листе 1, переименуйте его в Электроэнергию.

Самостоятельная работа

Упражнение1:

Рассчитайте свой возраст, начиная с текущего года и по 2030 год, используя маркер автозаполнения. Год вашего рождения является абсолютной ссылкой. Расчеты выполняйте на Листе 2. Лист 2 переименуйте в Возраст.

Упражнение 2: Создайте таблицу по образцу. В ячейках I 5: L 12 и D 13: L 14 должны быть формулы: СРЗНАЧ, СЧЁТЕСЛИ, МАХ, МИН. Ячейки B 3: H 12 заполняются информацией вами.

Выбранный для просмотра документ Excel пр.р. 5.docx

Библиотека
материалов

Практическая работа 5

Выполнив задания этой темы, вы научитесь:

Технологии создания табличного документа;

Присваивать тип к используемым данным;

Созданию формулы и правилам изменения ссылок в них;

Использовать встроенные статистических функции Excel для расчетов.

Задание 1. Рассчитать количество прожитых дней.

Технология работы:

1. Запустить приложение Excel.

2. В ячейку A1 ввести дату своего рождения (число, месяц, год – 20.12.97). Зафиксируйте ввод данных.

3. Просмотреть различные форматы представления даты (Главная – Формат ячейки – Другие числовые форматы - Дата) . Перевести дату в тип ЧЧ.ММ.ГГГГ. Пример, 14.03.2001

4. Рассмотрите несколько типов форматов даты в ячейке А1.

5. В ячейку A2 ввести сегодняшнюю дату.

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

Задание 2. Возраст учащихся. По заданному списку учащихся и даты их рождения. Определить, кто родился раньше (позже), определить кто самый старший (младший).


Технология работы:

1. Получите файл Возраст. По локальной сети: Откройте папку Сетевое окружение– Boss –Общие документы– 9 класс, найдите файл Возраст. Скопируйте его любым известным вам способом или скачайте с этой страницы внизу приложения.

2. Рассчитаем возраст учащихся. Чтобы рассчитать возраст необходимо с помощью функции СЕГОДНЯ выделить сегодняшнюю текущую дату из нее вычитается дата рождения учащегося, далее из получившейся даты с помощью функции ГОД выделяется из даты лишь год. Из полученного числа вычтем 1900 – века и получим возраст учащегося. В ячейку D3 записать формулу =ГОД(СЕГОДНЯ()-С3)-1900 . Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой тип.

3. Определим самый ранний день рождения. В ячейку C22 записать формулу =МИН(C3:C21) ;

4. Определим самого младшего учащегося. В ячейку D22 записать формулу =МИН(D3:D21) ;

5. Определим самый поздний день рождения. В ячейку C23 записать формулу =МАКС(C3:C21) ;

6. Определим самого старшего учащегося. В ячейку D23 записать формулу =МАКС(D3:D21) .

Самостоятельная работа:
Задача. Произведите необходимые расчеты роста учеников в разных единицах измерения.

Выбранный для просмотра документ Excel пр.р. 6.docx

Библиотека
материалов

Практическая работа 6

«MS Excel. Статистические функции» Часть II.

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

Решение:
Заполним таблицу исходными данными и проведем необходимые расчеты. Обратите внимание на формат значений в ячейках "Средний балл" (числовой) и "Дата рождения" (дата)

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

=ЦЕЛОЕ((СЕГОДНЯ()-E4)/365,25)

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

Является ли девочка отличницей, определяется формулой (на примере ячейки H4):

=ЕСЛИ(И(D4=5;F4="ж");1;0)

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

=СУММЕСЛИ(F4:F15;"ж";D4:D15)/СЧЁТЕСЛИ(F4:F15;"ж")

Функция СУММЕСЛИ позволяет просуммировать значения только в тех ячейках диапазона, которые отвечают заданному критерию (в нашем случае ребенок является мальчиком). Функция СЧЁТЕСЛИ подсчитывает количество значений, удовлетворяющих заданному критерию. Таким образом и получаем требуемое.
Для подсчета доли отличниц среди всех девочек отнесем количество девочек-отличниц к общему количеству девочек (здесь и воспользуемся набором значений из одной из вспомогательных колонок):

=СУММ(H4:H15)/СЧЁТЕСЛИ(F4:F15;"ж")

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

=ABS(СУММЕСЛИ(G4:G15;15;D4:D15)/СЧЁТЕСЛИ(G4:G15;15)-
СУММЕСЛИ(G4:G15;16;D4:D15)/СЧЁТЕСЛИ(G4:G15;16))

Обратите внимание на то, что формат данных в ячейках G18:G20 – числовой, два знака после запятой. Таким образом, задача полностью решена. На рисунке представлены результаты решения для заданного набора данных.

Выбранный для просмотра документ Excel пр.р. 7.docx

Библиотека
материалов

Практическая работа 7

«Создание диаграмм средствами MS Excel»

Выполнив задания этой темы, вы научитесь:

Выполнять операции по созданию диаграмм на основе введенных в таблицу данных;

Редактировать данные диаграммы, ее тип и оформление.

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

Диаграмма сохраняется и печатается вместе с рабочей книгой.

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

Задача: С помощью электронной таблицы построить график функции Y=3,5x–5. Где X принимает значения от –6 до 6 с шагом 1.

Технология работы:

1. Запустите табличный процессор Excel.

2. В ячейку A1 введите «Х», в ячейку В1 введите «Y».

3. Выделите диапазон ячеек A1:B1 выровняйте текст в ячейках по центру.

4. В ячейку A2 введите число –6, а в ячейку A3 введите –5. Заполните с помощью маркера автозаполнения ячейки ниже до параметра 6.

5. В ячейке B2 введите формулу: =3,5*A2–5. Маркером автозаполнения распространите эту формулу до конца параметров данных.

6. Выделите всю созданную вами таблицу целиком и задайте ей внешние и внутренние границы.

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

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

9. Выделите таблицу целиком. Выберите на панели меню Вставка - Диаграмма , Тип: точечная, Вид: Точечная с гладкими кривыми.

10. Переместите диаграмму под таблицу.

Самостоятельная работа:

    Постройте график функции у= sin (x )/ x на отрезке [-10;10] с шагом 0,5.

    Вывести на экран график функции: а) у=х; б) у=х 3 ; в) у=-х на отрезке [-15;15] с шагом 1.

    Откройте файл "Города" (зайдите в папку сетевая - 9 класс-Города).

    Посчитайте стоимость разговора без скидки (столбец D) и стоимость разговора с учетом скидки (столбец F).

    Для нагладного представления постройте две круговые диаграммы. (1- диаграмма стоимости разговора без скидки; 2- диагамма стоимости разговора со скидкой).

Выбранный для просмотра документ Excel пр.р. 8.docx

Библиотека
материалов

Практическая работа 8

ПОСТРОЕНИЕ ГРАФИКОВ И РИСУНКОВ СРЕДСТВАМИ MS EXCEL

1. Построение рисунка «ЗОНТИК»

Приведены функции, графики которых участвуют в этом изображении:

у1= -1/18х 2 + 12, хÎ[-12;12]

y 2= -1/8х 2 +6, хÎ[-4;4]

y 3= -1/8(x +8) 2 + 6, хÎ[-12; -4]

y 4= -1/8(x -8) 2 + 6, хÎ

y 5= 2(x +3) 2 – 9, хÎ[-4;0]

y 6=1.5(x +3) 2 – 10, хÎ[-4;0]

- Запустить MS EXCEL

· - В ячейке А1 внести обозначение переменной х

· - Заполнить диапазон ячеек А2:А26 числами с -12 до 12.

Последовательно для каждого графика функции будем вводить формулы. Для у1= -1/8х 2 + 12, хÎ[-12;12], для
y 2= -1/8х 2 +6, хÎ[-4;4] и т.д.

Порядок выполнения действий:

    Устанавливаем курсор в ячейку В1 и вводим у1

    В ячейку В2 вводим формулу =(-1/18)*А2^2 +12

    Нажимаем Enter на клавиатуре

    Автоматически происходит подсчет значения функции.

    Растягиваем формулу до ячейки А26

    Аналогично в ячейку С10 (т.к значение функции находим только на отрезке х от [-4;4]) вводим формулу для графика функции y 2= -1/8х 2 +6. И.Т.Д.

В результате должна получиться следующая ЭТ

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

    Выделяем диапазон ячеек А1: G26

    На панели инструментов выбираем меню Вставка → Диаграмма

    В окне Мастера диаграмм выберите Точечная → Выбрать нужный вид→ Нажать Ok .

В результате должен получиться следующий рисунок:

Задание для индивидуальной работы:

Постройте графики функций в одной системе координат. х от -9 до 9 с шагом 1 . Получите рисунок.

1. «Очки»

2. «Кошка» Фильтрация (выборка) данных в таблице позволяет отображать только те строки, содержимое ячеек которых отвечает заданному условию или нескольким условиям. В отличие от сортировки данные при фильтрации не переупорядочиваются, а лишь скрываются те записи, которые не отвечают заданным критериям выборки.

Фильтрация данных может выполняться двумя способами: с помощью автофильтра или расширенного фильтра.

Для использования автофильтра нужно:

o установить курсор внутри таблицы;

o выбрать команду Данные - Фильтр - Автофильтр;

o раскрыть список столбца, по которому будет производиться выборка;

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

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

Для отмены режима фильтрации нужно установить курсор внутри таблицы и повторно выбрать команду меню Данные - Фильтр - Автофильтр (снять флажок).

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

Задание.

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

Технология выполнения задания:

1. Откройте документ Sort.xls

2.

3. Выполните команду меню Данные - Сортировка.

4. Выберите первый ключ сортировки "По возрастанию" (Все отделы в таблице расположатся по алфавиту).

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

5. Установите курсор-рамку внутри таблицы данных.

6. Выполните команду меню Данные - Фильтр

7. Снимите выделение в таблицы.

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

9. Щелкните по кнопке со стрелкой, появившейся в столбце Количество остатка . Раскроется список, по которому будет производиться выборка. Выберите строку Условие. Задайте условие: > 0. Нажмите ОК . Данные в таблице будут отфильтрованы.

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

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

12. Для того, чтобы снова увидеть перечень всех непроданных товаров по всем отделам, нужно в списке "Отдел" выбрать критерий "Все".

13. Чтобы не запутаться в своих отчетах, вставьте дату, которая будет автоматически меняться в соответствии с системным временем компьютера Формулы – Вставить функцию - Дата и время - Сегодня .

Самостоятельная работа

«MS Excel. Статистические функции»

1 задание (общее)(2 балла).

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

2.1 задание(2 балла).

Четверо друзей путешествуют на трех видах транспорта: поезде, самолете и пароходе. Николай проплыл 150 км на пароходе, проехал 140 км на поезде и пролетел 1100 км на самолете. Василий проплыл на пароходе 200 км, проехал на поезде 220 км и пролетел на самолете 1160 км. Анатолий пролетел на самолете 1200 км, проехал поездом 110 км и проплыл на пароходе 125 км. Мария проехала на поезде 130 км, пролетела на самолете 1500 км и проплыла на пароходе 160 км.
Построить на основе вышеперечисленных данных электронную таблицу.

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

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

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

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

    Определить среднее количество километров по всем видам транспорта.

2.2 задание(2 балла).

Создайте таблицу “Озера Европы”, используя следующие данные по площади (кв. км) и наибольшей глубине (м): Ладожское 17 700 и 225; Онежское 9510 и 110; Каспийское море 371 000 и 995; Венерн 5550 и 100; Чудское с Псковским 3560 и 14; Балатон 591 и 11; Женевское 581 и 310; Веттерн 1900 и 119; Боденское 538 и 252; Меларен 1140 и 64. Определите самое большое и самое маленькое по площади озеро, самое глубокое и самое мелкое озеро.

2.3 задание(2 балла).

Создайте таблицу “Реки Европы”, используя следующие данные длины (км) и площади бассейна (тыс. кв. км): Волга 3688 и 1350; Дунай 2850 и 817; Рейн 1330 и 224; Эльба 1150 и 148; Висла 1090 и 198; Луара 1020 и 120; Урал 2530 и 220; Дон 1870 и 422; Сена 780 и 79; Темза 340 и 15. Определите самую длинную и самую короткую реку, подсчитайте суммарную площадь бассейнов рек, среднюю протяженность рек европейской части России.

3 задание(2 балла).

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

Найдите материал к любому уроку,

Проблемы аналитики, OLAP, хранилищ данных вызывают все больший интерес у российских ИТ-специалистов. К настоящему времени в нашей компьютерной печати и Интернете опубликовано немало хороших, академичных по способу изложения материалов по этой тематике, в том числе и вводного характера. Мы же предлагаем вашему вниманию статью, в которой сознательно пытаемся объяснить OLAP “на пальцах”, на конкретном примере. Практика показывает, что такое объяснение необходимо и некоторым ИТ-специалистам и особенно конечным пользователям.

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

*1. OLAP - On-Line Analytical Processing, оперативный анализ данных.

В качестве инструмента будет рассмотрена OLAP-система самого простого и недорогого класса - OLAP-клиент *1. Для примера выбран наиболее простой продукт из числа OLAP-клиентов - “Контур Стандарт” компании Intersoft Lab. (Для наглядности далее в статье общепринятые термины OLAP будут обозначаться жирным шрифтом и сопровождаться англоязычными аналогами.)

*1. Более подробно о классификации OLAP-систем рассказано в статье "OLAP, сделано в России" в PC Week/RE, №3/2001.

Итак, приступим к работе с системой. Для начала потребуется описать Источник данных (data source) - путь к таблице и ее поля. Это задача пользователя, знающего физическую реализацию базы данных. Для конечных пользователей он переводит название таблицы и ее полей в термины предметной области. За “источником данных” стоит локальная таблица, таблица или представление (view) SQL-сервера или хранимая процедура.

Скорее всего в конкретной базе данных счета-фактуры хранятся не в одной, а в нескольких таблицах. Кроме того, часть полей или записей может не использоваться для анализа. Поэтому далее создается Выборка (result set или query), - в которой настраиваются: алгоритм объединения таблиц по ключевым полям, условия фильтрации и набор возвращаемых полей. Назовем нашу выборку “Счета-фактуры” и поместим в нее все поля источника данных “Счета-фактуры”. Таким образом, ИТ-специалист, создавая семантический слой, скрывает физическую реализацию базы данных от конечного пользователя.

Затем настраивается OLAP-отчет. Этим может заняться специалист в предметной области. Сначала поля плоской выборки данных разбиваются на две группы - факты (facts или measures) и измерения (dimensions). Факты - это цифры, а измерения - “разрезы”, в которых будут суммироваться факты. В нашем примере измерениями станут: “Регион”, “Город”, “Покупатель”, “Товар”, “Дата”, а факт будет один - поле “Сумма” счета-фактуры. Для факта нужно выбрать один или несколько алгоритмов агрегации. OLAP способен не только суммировать итоги, но и выполнять более сложные вычисления, вплоть до статистического анализа. При выборе нескольких алгоритмов агрегации будут созданы виртуальные, вычисляемые факты (calculated facts). В примере выбран один алгоритм агрегации - “Сумма”.

Особое свойство OLAP-систем - генерация измерений и данных по старшим временным периодам из даты и автоматическое вычисление итогов по этим периодам. Выберем периоды “Год”, “Квартал” и “Месяц”, при этом данных за каждый день в отчете не будет, но появятся сгенерированные измерения “Год”, “Квартал” и “Месяц”. Назовем отчет “Анализ продаж” и сохраним его. Работа по созданию интерфейса аналитического приложения закончена.

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

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


Например, перетащив (операция “move”) колонку “Товар” на первое место, мы получим отчет о сравнении - “Сравнение объемов продаж товаров за год”. Чтобы агрегировать данные за год, достаточно перетащить колонки “Квартал” и “Месяц” в верхнюю часть таблицы - “область неактивных измерений”. Измерения “Квартал” и “Месяц”, перенесенные в эту область, будут закрыты (операция “close dimension”), т. е. исключены из отчета; при этом факты просуммируются за год. Несмотря на то что измерения закрыты, по ним можно задавать конкретные годы, кварталы и месяцы для фильтрации данных (операция “filter”).

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

Углубление в данные (операция “drill down”) позволяет получить более детальные сведения о продажах интересующего нас товара. Щелкнув на знаке “+” напротив товара “Кофе”, мы увидим объемы его продаж в разрезе регионов. Раскрыв регион “Урал”, получим объемы продаж в разрезе городов Уральского региона, углубившись в данные по “Екатеринбургу”, сможем просмотреть данные по оптовым покупателям этого города.

Для установки фильтров можно использовать и открытые измерения. Чтобы сравнить динамику продаж конфет в Москве и Екатеринбурге, установим фильтры на измерения “Товар” и “Город”.

Закроем ненужные измерения и выберем тип графика “Линия”. На получившемся графике можно проследить динамику продаж, оценить сезонные колебания и связь падений и роста сбыта товара в разных городах.

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

Действительно, OLAP - это естественное продолжение и развитие идеи электронных таблиц. По сути, визуальный интерфейс OLAP - это тоже электронная таблица (spreadsheet), но оснащенная мощной машиной вычислений и особым стандартом представления данных и управления ими. Более того, некоторые OLAP-клиенты реализованы как add-in к MS Excel. Поэтому миллионная армия “белых воротничков”, уверенно владеющая электронными таблицами, очень быстро осваивает и OLAP-инструменты. Для них это “бархатная революция”, предоставляющая новые возможности, но не сопряженная с необходимостью переучиваться.

Если читатель, прочитав эту статью, не потерял интереса к OLAP, он может обратиться к упомянутым в начале материалам. Сборники таких материалов размещены на ряде сайтов в Интернете, включая сайт Intersoft lab - www.iso.ru. С него можно также скачать демонстрационную версию системы “Контур Стандарт” с описанным в статье примером.