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

Суммирование. Для простейшего суммирования используют функцию СУММ, синтаксис которой СУММ(А), где: А - список от 1 до 30 аргументов. Аргумент может быть ячейкой, диапазоном ячеек, числом или формулой. Ссылки на пустые ячейки, текстовые или логические значения игнорируются. Фактически данная функция заменяет непосредственное суммирование с использованием оператора сложения (+). Формула СУММ(В2:В5), указанная в ячейке В6 (рис. 4.13), соответствует формуле =В2+В3+В4+В5.

Рис. 4.13. Суммирование с использованием функции СУММ

Для выполнения выборочного суммирования столбцов чисел, например требуется сложить только числа в столбцах В и D, а столбец C исключить формула сложения будет иметь вид: =СУММ(В2:В5; D2:D5). Для выполнения суммирования ячеек диапазона, удовлетворяющих заданным условиям, следует использовать функцию СУММЕСЛИ, имеющую следующий синтаксис:

СУММЕСЛИ(диапазон; критерий; диапазон_суммирования), где:

· диапазон - диапазон адресов вычисляемых ячеек;

· критерий - критерий в виде числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 24, ">22";

· диапазон_суммирования - фактические ячейки для суммирования.

Ячейки в области диапазон_суммирования суммируются, если соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию. Если диапазон_суммирования опущен, то суммируются ячейки в аргументе «диапазон». Excel предлагает дополнительные функции, которые можно применять для анализа данных с использованием условий. Например, для подсчета числа появлений текстовой строки или числа в пределах диапазона ячеек, используйте функцию СЧЁТЕСЛИ. Для получения формулы, возвращающей в зависимости от выполнения условия одно из двух значений, например вознаграждение по указанному объему продаж, используйте функцию ЕСЛИ. На рис. 4.14 представлен пример суммирования ставок комиссионных закупаемого оборудования, значения которых превышают 12000.

Рис. 4.14.Суммирование с использованием функции СУММЕСЛИ

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

Возведение в степень. Для возведения в степень используют функцию СТЕПЕНЬ, синтаксис которой СТЕПЕНЬ(число; степень), где:

· число - основание, которое может быть любым вещественным числом;

· степень - показатель степени, в которую возводится основание.

Вместо функции СТЕПЕНЬ для возведения в степень можно также использовать оператор ^, например 7^2. Отрицательные числа можно возводить в степень, значение которой является целым числом, других ограничений нет.

Рис. 4.15. Выборочное суммирование с использованием функции

"СУММЕСЛИ"

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

Синтаксис для всех обратных тригонометрических функций также имеет одинаковый вид и для функции АSIN синтаксис - АSIN(А), где:

А - число, равное синусу определяемого угла.

Следует обратить внимание, что все тригонометрические вычисления выполняются для углов, представленных в радианах. Для перевода в градусы необходимо использовать функции преобразования или самостоятельно переводить значения, используя функцию ПИ(). Функция ПИ() вставляет значение числа ПИ, функция не имеет аргументов, но скобки необходимо указывать. Например, для вычисления значения синуса угла в градусах, нужно его умножить на ПИ()/180. На рис.4.16. приведен пример вычисления функции SIN(A) для аргументов, заданных в градусах (от 0 до 90 градусов).

Расчет средних значений. В простом случае для расчета среднего арифметического значения используют функцию СРЗНАЧ, имеющую следующий синтаксис:

СРЗНАЧ(А), где:

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

Нахождение экстремальных значений. Для нахождения экстремальных значений (наибольшего или наименьшего) в массиве данных используются функции МАКС и МИН. Синтаксис функции МАКС:

МАКС(А), где:

А - список от 1 до 30 аргументов, среди которых требуется найти наибольшее значение. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. Функция МИН имеет такой же синтаксис, что и функция МАКС. Функции МАКС и МИН определяют только крайние значения, но не показывают, в какой ячейке эти значения находятся.

Рис. 4.16. Вычисление функции SIN(А)

На рис. 4.17 показан пример определения максимального значения стоимости тура.

Рис. 4.17. Нахождение максимальной стоимости тура

Расчет количества ячеек. Для определения количества ячеек, содержащих числовые значения, можно использовать функцию СЧЕТ, имеющей с интаксис:

СЧЕТ(А), где:

А - список аргументов от 1 до 30, среди которых требуется определить количество ячеек, содержащих числовые значения. Элемент может быть ячейкой, диапазоном ячеек, числом или формулой. В таблице на рис.4.х приведен пример определение в таблице числовых значений в ячейках А2:А6.

Рис.4.18. Расчет количества ячеек, содержащих числа, с использованием функции "СЧЕТ"

Для определения количества ячеек, содержащих числовые, текстовые, логические значения, следует использовать функцию СЧЕТЗ, синтаксис которой СЧЕТЗ(А), где:

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

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

· #ЗНАЧ! - используется недопустимый тип аргумента;

· #ДЕЛ/0! - в формуле выполняется деление на ноль;

· #ИМЯ? - Excel не может определить используемое в формуле имя;

· #ССЫЛКА! - используется недопустимая ссылка на ячейку;

· #Н/Д - неопределенные данные, при некорректном определении аргументов функции;

· #ПУСТО! - задано пересечение двух областей, не имеющих общих ячеек.

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

4.3.4. Копирование данных, адресация ячеек

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

1. Применение буфера обмена. Буфер обмена – это область оперативной памяти, предназначенная для временного хранения информации. Копируемая или перемещаемая информация помещается в буфер обмена после ее выделения и применения к выделенному боку команд Копировать или Вырезать. Из буфера обмена информация вставляется в помеченное место командой Вставить.

2. Метод перетаскивания. Курсор мыши наводится на рамку текущей ячейки и превращается из креста в стрелку. При нажатой левой клавиши выделенная информация перетаскивается в нужное место, для копирования данный шаг следует выполнять при нажатой клавише Ctrl.

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

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

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

Относительной называется ссылка, автоматически изменяющаяся при копировании содержащей ее формулы. Изменение происходит таким образом, что сохраняется расстояние по обеим частям адреса между ячейкой, на которую производится ссылка и той ячейкой, откуда она выполняется. Относительная ссылка выглядит как обычный адрес ячейки, состоящий из заголовков столбца и строки, например, А8. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании формулы вдоль строк или столбцов ссылка автоматически корректируется. Например, при копировании формулы с относительными ссылками из ячейки С1 в ячейки С2 и С3 ссылка в формуле автоматически изменяется: =A1+В1 на =A2+В2 и =А3+С3 соответственно. На рис.4.19 показан пример копирования формулы с использованием относительной ссылки.

Абсолютной называется ссылка, не изменяющая ссылок при копировании формулы, содержащей их. Запись абсолютной ссылки получается из записи относительной ссылки добавлением знака доллара $ в общем случае как перед заголовком столбца, так и перед заголовком строки, например $A$1. Рассмотрим пример, показывающий использование абсолютных ссылок (Рис.4.20.). При копировании формулы вычисления процента продаж товара отдельно по каждому месяцу необходимо изменять ссылки в числителе формулы и не изменять в знаменателе, что и обеспечивает использование абсолютной адресации.

	A	B	C
			=A1+B1	До копирования
			=A2+B2	После копирования
			=A3+B3	После копирования

Рис.4.19. Копирование формулы с относительной ссылкой

Для января эта доля может быть вычислена по формуле =B1/B4, все ссылки в этой формуле относительные. Поэтому при копировании этой формулы в ячейки С2 и С5 получим ошибку "#ДЕЛ/0!". Это произойдет потому,

	A	B	C	D
	Месяц	Товар	Процент по месяцам	Примечание
	Январь		= B1/B$4 (32%)	До копирования формулы
	февраль		= B2/B$4 (43%)	После копирования
	Март		= B3/B$4 (25%)	После копирования
	Итого	=B2+B3+B4 (16)	(100%)

Рис.4.20. Копирование формулы с абсолютной ссылкой

что обе ссылки будут изменены и для ячейки С2 получим: B1/B5, а ячейка В5 не заполнена, то есть содержит значение 0. Чтобы сделать формулу корректной для копирования, ссылка на В4 должна быть абсолютной. Для этого перед номером строки 4 необходимо поместить знак $. Такая ссылка на ячейку называется смешанной, то есть относительной для адреса столбца и абсолютной – для адреса строк таблицы. При необходимости ссылку можно сделать абсолютной как для строк, так и для столбцов таблицы, например $B$4.

Смешанные ссылки. Смешанная ссылка содержит абсолютный столбец и относительную строку или абсолютную строку и относительный столбец. Абсолютная ссылка столбцов имеет вид $A1, $B1 и т.д, а абсолютная ссылка строки имеет вид A$1, B$1 и т. д. При изменении позиции ячейки, содержащей формулу, относительная ссылка изменяется, а абсолютная не изменяется. При копировании формулы вдоль строк и столбцов относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Для быстрого изменения типа ссылки используется клавиша F4. Введите в ячейку А1 любое число, а в ячейку А2 введите формулу =А1. Затем нажимайте клавишу F4 и обратите внимание, что после каждого нажатия клавиши тип ссылки будет изменяться (рис.4.21).

Рис. 4.21. Изменение типа ссылки

4.4. Построение диаграмм и графиков в Excel

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

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

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

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

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

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

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

1. Выбор типа и вида диаграммы. После вызова Мастера диаграмм в диалоговом окне Мастер диаграмм (шаг 1 из 4): тип диаграммы следует выбрать тип и вид диаграммы. Диалоговое окно имеет две вкладки: Стандартные и Нестандартные. Во вкладке Стандартные расположены стандартные типы диаграмм Excel и их разновидности. Для просмотра внешнего вида выбранной диаграммы следует нажать и удерживать кнопку Просмотр результата. Выбранный тип и вид диаграммы можно будет изменить в последующем при редактировании и оформлении диаграммы.

2. Выбор источника данных. В диалоговом окне Мастер диаграмм (шаг 2 из 4): источник данных диаграммы необходимо выбрать источник данных для диаграммы (рис.4.22), диалоговое окно имеет две вкладки: Диапазон данных и Ряд.

Рис. 4.22. Выбор источника данных диаграммы

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

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

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

По окончании работы с источниками данных диаграммы в диалоговом окне Мастер диаграмм (шаг 2 из 4): источник данных диаграммы следует нажать кнопку Далее.

3. Выбор параметров диаграммы. Выполняется на 3-емшаге вокне Мастер диаграмм (шаг 3 из 4): параметры диаграммы. На данном шаге мастером предлагается определить заголовок создаваемой диаграммы, использовать линии сетки, включить легенду в любом месте диаграммы, дать имена осям Х и У, определить подписи данных и щелкнуть Далее для перехода на последний четвертый шаг.

4. Размещение диаграммы. Выполняется в окне Мастер Диаграмм (шаг 4 из 4): размещение диаграммы. На этом шаге пользователь определяет, где поместить диаграмму: на текущем рабочем листе или на отдельном листе книги. После нажатия кнопки Готово Excel создаст диаграмму. Затем пользователь может внести изменения данных в исходной таблице, что автоматически отразится на построенной диаграмме.

4.5. Обработка табличных данных в Excel

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

При обработке и анализе таблиц большой размерности часто требуется выделять данные, относящиеся к одной группе и это можно реализовать средствами Excel путем создания так называемой структуры. Данная структура позволяет показывать и скрывать строки дополнительных сведений о месячном объеме продаж, что особенно важно при большом значении строк, не умещающихся на одном экране. На рис. 4.23 в верхней таблице строки разбиты на две группы: 1 и 2 кварталы, кнопки “+” и “–“ раскрывают и скрывают группы, а кнопки 1 и 2 – уровни групп. На рис.4.24 (нижней таблице) данные по 1-му кварталу скрыты, а по 2-му кварталу открыты. Каждая из групп формируется отдельно. В меню Данные выберите команду Группа и структура, а затем - Создание структуры. Выделите строки или столбцы, содержащие сведения.

Рис.4.23. Группировка данных

Строки или столбцы сведений обычно прилегают к строке или столбцу, содержащему итоговые формулы или заголовки. Например, для рассматриваемого примера выделите строки 3- 5 и 7- 9. В общем случае необходимо продолжать выделение и группировку строк или столбцов сведений и выполнение команды «Группировать» до тех пор, пока не будут созданы все необходимые уровни структуры. Данная структура позволяет показывать и скрывать строки дополнительных сведений о месячном объеме продаж. Если итоговые строки расположены над строками данных или итоговые столбцы расположены слева от столбцов данных, измените параметры расположения.

4.5.2. Сортировка и фильтрация данных

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

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

Рис.4.24. Выполнение сортировки

Для выполнения сортировки курсор необходимо поместить в столбце, по которому выполняется сортировка. Выбрав на панели инструментов "Сортировка от А до Я", данный список будет отсортирован по первому столбцу, то есть по полю Турфирма. Для выполнения сортировки по нескольким столбцам можно также использовать данные кнопки сортировки, но более эффективным инструментом сортировки является команда Данные > Сортировка. Нарабочем листе появляется диалоговое окно Сортировка, вкотором можно задать три ключа сортировки. Это позволит расставить строки по выбранному критерию сортировки (возрастание или убывание) в столбце В, а внутри групп других столбцов с одинаковыми значениями столбца В расставит по выбранному критерию в столбце С (рис.4.24). Таким образом, выполнение сортировки в общем случае включает следующие этапы:

· необходимо выделить ячейку в списке, который требуется отсортировать;

· в меню Данные выберите команду Сортировка;

· выбор столбцов сортировки в полях Сортировать по и Затем по.

· выберите остальные параметры сортировки и нажмите кнопку OK.

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

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

· расширенный фильтр для более сложных условий отбора.

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

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

Для использования Автофильтра необходимо выделить какую-нибудь

ячейку исходной таблицы и выполнить команду Данные-Фильтр-Автофильтр.

Рис.4.25. Результаты работы турагентов

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

Рис.4.26. Таблица исходных данных

Если щелкнуть по кнопке-стрелке в столбце «город», то появляется список со всеми введенными в таблицу городами и можно выбрать интересующий город. Выберем город СПб для анализа деятельности в нем агентов (рис.4.27).

Рис.4.27. Выполнение фильтрации

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

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

4.6. Объединение электронных таблиц

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

Рис.4.28. Объединение таблиц

Две таблицы называются связанными, когда в ячейках одной из них присутствуют ссылки на ячейки другой. Пусть таблица Прайс-лист содержит цены на продаваемые книги, а таблица Калькуляция – количество и стоимость каждой книги. Обе таблицы принадлежат одной книге и размещены на Лист1 и Лист2 (рис.4.28). Стоимость книг на Лист2 в таблице Калькуляция вычисляется с использованием значения цены на Лист1. Формула для вычисления стоимости книг на Лист2 включает имя Лист1 и адреса ячеек, разделенных восклицательным знаком: =В4*Лист 1!В4. Если ссылка на ячейку осуществляется из другой книги, то в ссылку добавляется полное имя книги, то есть путь к файлу и имя файла. При этом имя книги заключается в квадратные скобки, а полное имя файла в апострофы.

4.7. Анализ данных с помощью сводных таблиц

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

Рис.4.29. Построение сводной таблицы

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

Для создания сводной таблицы выполняется команда Данные - Сводная таблица, в результате чего на экране появляется Мастер сводных таблиц, реализуемый данную процедуру за три шага:

· на первом шаге выбирается источник данных, для нашего примера они хранятся в базе данных листа Excel и нажимается кнопка Далее;

· на втором шаге определяется диапазон адресов исходных данных и выполняется переход к шагу 3 командой Далее;

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

Рис.4.30. Макет сводной таблицы

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

· страница – поле используется для заголовка страницы сводной таблицы;

· столбец – поле используется в качестве заголовка столбцов сводной таблицы;

· строка – поле используется для заголовков строк сводной таблицы;

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

На рис.4.31 приведена результирующая сводная таблица для рассматриваемого примера. Используя Кнопки в таблице (черные треугольники),

Рис.4.31.Результирующая сводная таблица

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

4.8. Решение типовых задач средствами Excel

4.8.1.Подбор параметров

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

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

Рассмотрим задачу подбора параметра на примере анализа объема продаж туров в соответствии с таблицей на рис.4.32. В ячейке В5 приведена формула расчета прибыли, в которой стоимость тура является переменной, а остальные параметры константами, поэтому нужную прибыль в 11000 у.е. в данном примере будем получать путем подбора стоимости тура (ячейка В2). Для подбора искомого параметра поместите курсор в ячейку В5 необходимо выполнить команду Сервис-Подбор параметра, в результате чего появится окно для подбора параметра (рис 4.32). Введите Значение прибыли 11000 уе, адрес изменяемой ячейки В2 и нажмите ОК. В результате перечисленных действий получены следующие значения (рис.4.33).

Рис.4.32. Окно подбора параметра

Рис.4.33. Результат подбора параметра

4.8.2.Анализ и прогнозирование данных

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

Для такой постановки задачи ее математическая формулировка может выглядеть следующим образом. Имеется зависимость переменной E от переменной X, полученная путем эксперимента E=F(X). Требуется построить аналитическое описаниедляфункции T =F(X),где T(X) - некоторая функция от X, наилучшим образом описывающая наблюдаемые экспериментальные значения E. Обычно T =F(X) следует выбирать так, чтобы минимизировать сумму квадратов разностей между экспериментальными и теоретическими значениями E и T, т.е. минимизировать некоторый функционал:

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

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

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

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

Рис.4.34. Подбор линии тренда

Затем в окне линия тренда выберете кнопку параметры и задайте прогноз, а также укажите необходимость отображения на графике полученного аналитического уравнения и коэффициента детерминации R². Степень приближения аппроксимирующей функции к экспериментальному закону изменения оценивается посредством коэффициента детерминации R². Чем ближе значение данного коэффициента к 1, чем выше степень близости. Как следует из подобранного математического описания коэффициент R²=0.9846, что соответствует очень хорошему выбору метода аппроксимации, а вид полученного уравнения приведен на графике.

Достаточно актуальной является задача построения аналитических зависимостей для функций от двух и более переменных, Для подобного рода зависимостей аппроксимацию можно выполнить, используя функции из статистической группы: ЛИНЕЙН и ЛГРФПРИБЛ. Функция ЛИНЕЙН выполняет статистическую оценку для ряда с использованием метода наименьших квадратов для вычисления аппроксимирующей зависимости. Функция возвращает массив, описывающий полученную функцию.

Дата добавления: 2014-10-17