1. Получаем разрешение на доступ к OLAP-кубу SQL Server Analysis Services (SSAS)
2. На вашем компьютере должен быть установлен MS Excel 2016 / 2013 / 2010 (можно и MS Excel 2007, но в нем работать не удобно, и совсем бедная функциональность MS Excel 2003)
3. Открываем MS Excel, запускаем мастер настройки соединения с аналитической службой:
3.1 Указываем имя или IP-адрес действующего сервера OLAP (иногда требуется указать номер открытого порта, например, 192.25.25.102:80); используется доменная аутентификация:
3.2 Выбираем многомерную базу данных и аналитический куб (в случае наличия прав доступа к кубу):
3.3 Настройки соединения с аналитической службой будут сохранены в odc-файле на Вашем компьютере:
3.4 Выбираем вид отчета (сводная таблица/график) и указываем место для его размещения:
Если в книге Excel уже создано подключение, то им можно воспользоваться повторно: главное меню «Данные» -> «Существующие подключения» -> выбираем подключение в этой книге -> вставляем сводную таблицу в указанную ячейку.
4. Успешно подключились к кубу, можно приступать к интерактивному анализу данных:
Приступая к интерактивному анализу данных необходимо определить, какие из полей будут участвовать в формировании строк, столбцов и фильтров (страниц) сводной таблицы. В общем случае сводная таблица является трехмерной, и можно считать, что третье измерение расположено перпендикулярно экрану, а мы наблюдаем сечения, параллельные плоскости экрана и определяемые тем, какая «страница» выбрана для отображения. Фильтрацию можно осуществить путем перетаскивания мышью соответствующих атрибутов измерений в область фильтров отчета. Фильтрация ограничивает пространство куба, уменьшая нагрузку на сервер OLAP, поэтому предпочтительнее в первую очередь установить необходимые фильтры . Затем следует размещать атрибуты измерений в областях строк, столбцов и показатели в область данных сводной таблицы.
Каждый раз, когда изменяется сводная таблица, на сервер OLAP автоматически отправляется MDX-инструкция, по исполнении которой возвращаются данные. Чем больше и сложнее объем обрабатываемых данных, рассчитываемых показателей, тем дольше время исполнения запроса. Отменить исполнение запроса можно нажатием клавиши Escape . Последние выполненные операции можно отменить (Ctrl+Z) или вернуть (Ctrl+Y).
Как правило, для наиболее часто используемых сочетаний атрибутов измерений в кубе хранятся заранее рассчитанные агрегированные данные, поэтому время отклика таких запросов несколько секунд. Однако все возможное комбинации агрегаций просчитать невозможно, так как для этого может потребоваться очень много времени и места для хранения. Для исполнения массивных запросов к данным на уровне детализации могут потребоваться значительные вычислительные ресурсы сервера, поэтому время их исполнения может быть продолжительным. После чтения данных с дисковых накопителей сервер помещает их в кэш оперативной памяти, что позволяет последующим таким запросам выполняться мгновенно, поскольку данные будут извлекаться уже из кэша.
Если Вы считаете, что ваш запрос будет часто использоваться и время его исполнения неудовлетворительно, Вы можете обратиться в службу сопровождения аналитических разработок для оптимизации выполнения запроса.
После размещения иерархии в области строк / столбцов возможно скрыть отдельные уровни:
У ключевых атрибутов (реже - для атрибутов выше по иерархии) измерений могут быть свойства - описательные характеристики, которые могут отображаться как во всплывающих подсказках, так и в виде полей:
Если требуется отобразить сразу несколько свойств полей, то можно воспользоваться соответствующим диалоговым списком:
В Excel 2010 появилась возможность интерактивного создания собственных (определяемых пользователем) наборов из элементов измерения:
В отличие от наборов создаваемых и хранящихся централизованно на стороне куба, пользовательские наборы сохраняются локально в книге Excel и могут использоваться в дальнейшем:
Продвинутые пользователи могут создавать наборы, используя MDX конструкции:
Посредством пункта "Параметры сводной таблицы..." контекстного меню (щелчок правой кнопкой мыши в рамках сводной таблицы) предоставляется возможность настройки сводной таблицы, например:
- вкладка "Вывод", параметр "Классический макет сводной таблицы" - сводная таблица становится интерактивной, можно перетаскивать поля (Drag&Drop);
- вкладка "Вывод", параметр "Показывать элементы без данных в строках" - в сводной таблице будут отображаться пустые строки, не содержащие ни одного значения показателя по соответствующим элементам измерений;
- вкладка "Разметка и формат", параметр "Сохранять форматирование ячеек при обновлении" - в сводной таблице можно переопределить и сохранить формат ячеек при обновлении данных;
Для имеющейся сводной OLAP-таблицы можно создать сводную диаграмму – круговую, линейчатую, гистограмму, график, точечную и другие виды диаграмм:
При этом сводная диаграмма будет синхронизирована со сводной таблицей – при изменении состава показателей, фильтров, измерений в сводной таблице также обновляется сводная таблица.
Выделим исходную сводную таблицу, скопируем ее в буфер обмена (Ctrl+C) и вставим её копию (Ctrl+V), в которой изменим состав показателей:
Для одновременного управления несколькими сводными таблицами вставим срез (новый функционал, доступный, начиная с версии MS Excel 2010). Подключим наш Slicer к сводным таблицам – щелчок правой кнопкой мыши в рамках среза, выбор в контекстном меню пункта "Подключения к сводной таблице...". Следует отметить, что может быть несколько панелей срезов, которые могут обслуживать одновременно сводные таблицы на разных листах, что позволяет создавать скоординированные информационные панели (Dashboard).
Панели срезов можно настраивать: необходимо выделить панель, затем см. пункты "Размер и свойства...", "Настройки среза", "Назначить макрос" в контекстном меню, активируемого по правому щелку мыши или пункт "Параметры" главного меню. Так, возможно установить кличество столбцов для элементов (кнопок) среза, размеры кнопок среза и панели, определить для среза цветовую гамму и стиль оформления из имеющегося набора (или создать свой стиль), определить собственный заголовок панели, назначить программный макрос, посредством которого можно расширить функционал панели.
Аналитический куб поддерживает локализацию на русский и английский языки (при необходимости возможна локализация на другие языки). Переводы распространяются на наименования измерений, иерархий, атрибутов, папок, мер, а также элементы отдельных иерархий в случае наличия для них переводов на стороне учетных систем/ хранилища данных. Чтобы сменить язык, необходимо открыть свойства подключения и в строке подключения добавить следующую опцию:
Extended Properties="Locale=1033"
где 1033 - локализация на английский язык
1049 - локализация на русский язык
Возможности работы с OLAP-кубами Microsoft возрастут, если использовать дополнительные расширения, например, OLAP PivotTable Extensions, благодаря которому можно пользоваться быстрым поиском по измерению:
Проблемы аналитики, 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. С него можно также скачать демонстрационную версию системы “Контур Стандарт” с описанным в статье примером.
Аналитическая аналитическая обработка (OLAP) - это технология, которая используется для упорядочения баз данных большого бизнеса и поддержки бизнес-аналитики. Базы данных OLAP делятся на один или несколько кубов, и каждый из них организуется администратором куба так, чтобы он соответствовал способу получения и анализа данных, чтобы облегчить создание и использование отчетов сводной таблицы и отчетов сводной диаграммы, которые вам понадобятся.
Бизнес-аналитик часто хочет получить большую картину бизнеса, чтобы просматривать более широкие тенденции на основе агрегированных данных, а также просматривать тенденции с разбивкой на любое количество переменных. Бизнес-аналитика - это процесс извлечения данных из базы данных OLAP и анализа этих данных для получения сведений, которые можно использовать для принятия информированных бизнес-решений и выполнения действий. Например, с помощью OLAP и бизнес-аналитики вы можете ответить на следующие вопросы о бизнес-данных.
Как общая сумма продаж всех продуктов в 2007 сравнивается с объемом продаж с 2006?
Как это сравнивается с датой и временем по выгодным периодом за последние пять лет?
Сколько денег клиенты потратили на 35 в прошлый год и каким образом изменилось это поведение с течением времени?
Сколько продуктов было продано в двух определенных странах/регионах в этом месяце, в отличие от того же месяца прошлого года?
Для каждой группы возрастов клиентов Каково разделение рентабельности (как процент маржи, так и итог) по категориям товаров?
Поиск лучших и нижних продавцов, дистрибьюторов, поставщиков, клиентов, партнеров и клиентов.
Базы данных OLAP (Online Analytical Processing) упрощают запросы бизнес-аналитики. OLAP - это технология баз данных, оптимизированная для запросов и отчетов, а не для обработки транзакций. Источник данных для OLAP - это оперативные базы данных обработки транзакций (OLTP), которые обычно хранятся в хранилищах данных. Данные OLAP извлекаются из этих исторических данных и объединяются в структуры, которые допускают сложный анализ. Данные OLAP также упорядочиваются иерархически и хранятся в кубах, а не в таблицах. Это сложная технология, использующая многомерные структуры для обеспечения быстрого доступа к данным для анализа. В этой Организации для отчета сводной таблицы или отчета сводной диаграммы можно легко отобразить сводные данные высокого уровня, такие как итоги продаж по всей стране или региону, а также отобразить сведения о сайтах, где продажи особенно велики или слабы.
Базы данных OLAP предназначены для ускорения загрузки данных. Поскольку OLAP-сервер, а не Microsoft Office Excel, вычисляет обобщенные значения, при создании или изменении отчета необходимо отправлять в Excel меньшие данные. Этот подход позволяет работать с большим объемом исходных данных, чем в случае, если данные были организованы в традиционной базе данных, где Excel извлекает все отдельные записи и вычисляет обобщенные значения.
Базы данных OLAP содержат два основных типа данных: меры, которые представляют собой числовые данные, количества и средние значения, которые используются для принятия информированных бизнес-решений, и измерения, которые являются категориями, используемыми для упорядочения этих мер. Базы данных OLAP помогают упорядочить данные с помощью множества уровней детализации, используя те же категории, которые вы знаете для анализа данных.
В следующих разделах каждый компонент подробно описан ниже.
Кубическ Структура данных, объединяющая меры по уровням и иерархиям каждого измерения, которое вы хотите проанализировать. Кубы объединяют несколько измерений, таких как время, география и строки продукта, с итоговыми данными, такими как продажи и запасы. Кубы - это не "Кубы" в строгом математическом смысле, так как они не обязательно имеют одинаковые стороны. Тем не менее, они представляют собой метафору Апт для сложного понятия.
Измерений Набор значений в Кубе, основанных на столбце в таблице фактов куба и который обычно является числовым значением. Меры - это центральные значения в Кубе, которые предварительно обрабатываются, обрабатываются и анализируются. Наиболее распространенными примерами являются продажи, доходы, доходы и затраты.
Member Элемент в иерархии, представляющий один или несколько вхождений данных. Элемент может быть как уникальным, так и неуникальным. Например, 2007 и 2008 представляют уникальные элементы на уровне года измерения времени, в то время как Январь представляет неуникальные элементы на уровне месяца, так как в измерении времени есть более одного января, так как в нем содержатся данные для более чем одного года.
Вычисляемый элемент Элемент измерения, значение которого вычисляется во время выполнения с помощью выражения. Значения вычисляемых элементов могут быть производными от значений других членов. Например, вычисляемый элемент "прибыль" может быть определен путем вычитания значения элемента, а также затрат из значения элемента, продажи.
измерение Набор из одной или нескольких упорядоченных иерархий уровней Куба, которые пользователь понимает и использует как основу для анализа данных. Например, географическое измерение может включать уровни для страны/региона, штат/район и город. Кроме того, измерение времени может включать иерархию с уровнем "год", "квартал", "месяц" и "день". В отчете сводной таблицы или отчете сводной диаграммы каждая иерархия становится набором полей, которые можно разворачивать и сворачивать, чтобы показать более низкие или более высокие уровни.
Иерархия Логическая структура дерева, которая упорядочивает элементы измерения, так что у каждого элемента есть один родительский элемент и ноль или более дочерних элементов. Дочерний элемент - это член более ранней группы в иерархии, непосредственно связанный с текущим членом. Например, в иерархии времени, содержащей уровни квартал, месяц и день, Январь является дочерним элементом Qtr1. Родительский элемент - это член более низкого уровня в иерархии, непосредственно связанный с текущим членом. Родительским значением обычно является консолидация значений всех дочерних элементов. Например, в иерархии времени, содержащей уровни "квартал", "месяц" и "день", Qtr1 является родительским для января.
Уровень В иерархии данные могут быть организованы в более низкие и более высокие уровни детализации, такие как годы, кварталы, месяцы и дневные уровни в иерархии времени.
Получение данных OLAP Вы можете подключаться к источникам данных OLAP точно так же, как и к другим внешним источникам данных. Вы можете работать с базами данных, созданными с помощью служб Microsoft SQL Server OLAP версии 7,0, служб Microsoft SQL Server Analysis Services версии 2000 и Microsoft SQL Server Analysis Services версии 2005, серверных продуктов Microsoft OLAP. Excel также может работать с продуктами OLAP третьих лиц, которые совместимы с OLE-DB для OLAP.
Данные OLAP можно отобразить только в виде отчета сводной таблицы или отчета сводной диаграммы или в функции листа, преобразованной из отчета сводной таблицы, но не в виде диапазон внешних данных. Вы можете сохранять отчеты сводных таблиц OLAP и сводных диаграмм в шаблонах отчетов, а также создавать файлы подключения к данным Office (ODC) для подключения к базам данных OLAP для запросов OLAP. При открытии ODC-файла в Excel отображается пустой отчет сводной таблицы, который готов для размещения.
Создание файлов куба для автономного использования Вы можете создать файл автономного куба (. cub) с подмножеством данных из базы данных сервера OLAP. Автономные файлы куба используются для работы с данными OLAP, если вы не подключены к сети. С помощью куба вы можете работать с большим объемом данных в отчете сводной таблицы или отчете сводной диаграммы, чем в противном случае, и ускорить получение данных. Вы можете создавать файлы Куба только в том случае, если вы используете поставщик OLAP, например Microsoft SQL Analysis Services версии 2005, которая поддерживает эту функцию.
Действия сервера Действие сервера - это необязательная функция, которую администратор куба OLAP может определять на сервере, который использует элемент куба или меру в качестве параметра в запросе для получения сведений в Кубе, или для запуска другого приложения, например браузера. В Excel поддерживается URL-адреса, отчеты, наборы строк, детализация и развертывание подробных серверных действий, но не поддерживает собственный собственный оператор и набор данных.
КПЭ Ключевой показатель эффективности - это особая вычисляемая мера, определенная на сервере, позволяющая отслеживать "ключевые показатели эффективности", в том числе состояние (текущее значение соответствует определенному номеру). и тенденция (значения с течением времени). Когда они отображаются, сервер может отсылать соответствующие значки, похожие на новый значок Excel, чтобы они выстроили выше или ниже уровней состояния (например, для значка остановки), а также раскрутка значения вверх или вниз (например, значок стрелки направления).
Форматирование на сервере Администраторы кубов могут создавать меры и вычисляемые элементы с помощью цветового форматирования, форматирования шрифта и правил условного форматирования, которые могут быть назначены как корпоративное стандартное бизнес-правило. Например, серверный формат для дохода может представлять собой числовой формат валюты, цвет ячейки зеленого цвета, если значение больше или равно 30 000, и красный, если значение меньше 30 000, и стиль шрифта полужирный, если значение меньше 30 000, и при положительном значении - обычный. больше или равно 30 000. Дополнительные сведения можно найти .
Язык интерфейса Office Администратор куба может определять переводы для данных и ошибок на сервере для пользователей, которые должны просматривать сведения сводной таблицы на другом языке. Эта функция определена как свойство соединения с файлом, а региональные параметры компьютера пользователя и страны должны соответствовать языку интерфейса.
Поставщик OLAP Для настройки источников данных OLAP для Excel необходим один из указанных ниже провайдеров OLAP.
Поставщик Microsoft OLAP В Excel входит драйвер источника данных и клиентское программное обеспечение для доступа к базам данных, созданным с помощью служб Microsoft SQL Server olap версии 7,0, Microsoft SQL Server olap версии 2000 (8,0) и Microsoft SQL Server Analysis services версии 2005 (9,0).
Сторонние поставщики OLAP Для других продуктов OLAP необходимо установить дополнительные драйверы и клиентское программное обеспечение. Чтобы использовать возможности Excel для работы с данными OLAP, продукт стороннего поставщика должен соответствовать стандарту OLE-DB для OLAP и быть совместимым с Microsoft Office. Для получения сведений об установке и использовании стороннего поставщика OLAP обратитесь к системному администратору или поставщику продукта OLAP.
Базы данных сервера и файлы Куба Клиентское программное обеспечение Excel OLAP поддерживает соединения с двумя типами баз данных OLAP. Если база данных на сервере OLAP доступна в сети, вы можете получать из нее исходные данные прямо из нее. Если у вас есть автономный файл куба, который содержит данные OLAP или файл определение кубов, вы можете подключаться к этому файлу и получать из него исходные данные.
Источники данных Источник данных предоставляет доступ ко всем данным в базе данных OLAP или файле автономного куба. Создав источник данных OLAP, вы можете основывать на нем отчеты и возвращать данные OLAP в Excel в виде отчета сводной таблицы или отчета сводной диаграммы, а также в функции листа, преобразованной из отчета сводной таблицы.
Microsoft Query С помощью Query можно получать данные из внешней базы данных, например Microsoft SQL или Microsoft Access. Для получения данных из сводной таблицы OLAP, связанной с файлом куба, использовать запрос не требуется. Дополнительные сведения .
Если вы работаете с отчетами сводных таблиц и сводными диаграммами из OLAP исходные данные и других типов исходных данных, вы увидите некоторые отличия функций.
Извлечение данных Сервер OLAP возвращает новые данные в Excel при каждом изменении макета отчета. При использовании других типов внешних источников данных вы запрашиваете все исходные данные за один раз или можете задавать параметры для запроса только при отображении разных элементов полей фильтра отчета. Кроме того, у вас есть еще несколько вариантов обновления отчета.
В отчетах, основанных на исходных данных OLAP, параметры поля фильтра отчета недоступны, фоновый запрос недоступен, а параметр оптимизации памяти недоступен.
Примечание: Параметр оптимизации памяти также недоступен для источников данных OLEDB и отчетов сводных таблиц, основанных на диапазоне ячеек.
Типы полей Исходные данные OLAP. поля измерение могут использоваться только как строки (ряды), столбцы (категория) или поля страницы. Поля мер можно использовать только в качестве полей значений. Для других типов исходных данных все поля можно использовать в любой части отчета.
Доступ к подробным данным Для исходных данных OLAP сервер определяет доступные уровни детализации и вычисляет сводные значения, поэтому записи с подробными сведениями, которые составляют суммарные значения, могут быть недоступны. Тем не менее, сервер может предоставить поля свойств, который вы можете отобразить. Другие типы исходных данных не имеют полей свойств, но вы можете отобразить основные сведения для значений полей данных и элементов, а также отобразить элементы без данных.
У полей фильтра отчета OLAP может быть не все элементы, а команда Показать страницы фильтра отчета недоступна.
Первоначальный порядок сортировки Для исходных данных OLAP элементы сначала отображаются в том порядке, в котором они возвращаются OLAP сервером. Вы можете отсортировать или вручную изменить порядок элементов. Для других типов исходных данных элементы нового отчета сначала сортируются по имени элемента в возрастающем порядке.
Ними Серверы OLAP предоставляют обобщенные значения непосредственно для отчета, поэтому невозможно изменить итоговые функции для полей значений. Для других типов исходных данных вы можете изменить статистическую функцию для поля значения и использовать несколько итоговых функций для одного и того же поля значения. Нельзя создавать вычисляемые поля и вычисляемые элементы в отчетах с исходными данными OLAP.
Промежуточных итогов В отчетах с исходными данными OLAP нельзя изменить итоговую функцию для промежуточных итогов. С другими типами исходных данных вы можете изменить итоговые функции для промежуточных итогов и отобразить или скрыть промежуточные итоги для всех полей строк и столбцов.
Для исходных данных OLAP вы можете включить или исключить скрытые элементы при расчете промежуточных и общих итогов. Для других типов исходных данных вы можете включать скрытые элементы полей фильтра отчета в промежуточные итоги, но скрытые элементы в других полях будут исключены по умолчанию.
Выберите документ из архива для просмотра:
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 Итого посчитайте самостоятельно.
Результат покажите учителю!!!
Выбранный для просмотра документ 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 EXCEL1. Построение рисунка «ЗОНТИК»
Приведены функции, графики которых участвуют в этом изображении:
у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 процентов. Посчитать штраф для каждой организации, средний штраф, общее количество денег, которые банк собирается получить дополнительно. Определить средний штраф бюджетных организаций.
Найдите материал к любому уроку,
Первый интерфейс сводных таблиц, называемых также сводными отчеты, был включен в состав Excel еще в 1993м году (версии Excel 5.0). Несмотря на множество полезных функциональных возможностей, он практически не применяется в работе большинством пользователей Excel. Даже опытные пользователи зачастую подразумевают под термином «сводный отчет» нечто построенное с помощью сложных формул. Попробуем популяризировать использование сводных таблиц в повседневной работе экономистов. В статье обсуждаются теоретические основы создания сводных отчетов, даются практические рекомендации по их использованию, а также приводится пример доступа к данным на основе нескольких таблиц.
Большинство экономистов слышали термины «многомерные данные», «виртуальный куб», «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) как для получения данных, так и для их предварительной обработки в сводных таблицах.
