Лекция №4. Использование редактора электронных таблиц Ms Exсel в рекламной практике

1. Основные понятия электронной таблицы

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

Рабочая книга - представляет собой основной документ Excel. Она хранится в файле с произвольным именем с расширением «.xls». По умолчанию рабочая книга состоит из 16 листов, имеющих имена: Лист 1, …Лист 16. пользователь может изменить имя листа.

Лист - предназначен для создания и хранения таблиц, диаграмм и макросов. Состоит из 256 столбцов, которые имеют имя А, В, С,...Z,…AA,AB… и строки 1,2,3… 16384

Рисунок 1

Для хранения имени столбца используется 1 байт памяти, для хранения строки 256.

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

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

Адресация ячеек. Существует 2 вида ячеек: 1) относительная (пример с В3)- при проведении копирования и перемещении, относительный адрес ячейки всегда меняется: при копировании вниз № строки увеличивается на 1, при копировании вправо имя столбца изменяется на 1 букву в сторону увеличения; 2) абсолютная- не изменяется при проведении любых манипуляций с ячейками, обозначенных $B$3, не изменится № строки, ни название столбца. Если B$2-не изменится № строки, $B2-не изменится название столбца.

Формула - математическая запись вычислений, производимых над данными в таблицах. Она начинается со знака равенства или математического оператора и записанного в ячейку таблицы. Результатом выполнения формулы является вычисление значения. Пример: =$A$12+B2. Если ячейка содержащая формулу, активна, то формула проявляется в строке ввода, то есть становится видна. В противном случае в ячейки проявляется по формуле значение.

Функция - математическая запись, указывающая на выполнение определенных вычислительных операций. Она состоит из имени и 1 или несколько аргументов, заключенных в круглые скобки. Пример: =SUM(A1:A10);LOGIO(B2);lg(B2)

Форматирование - задание определенных параметров для внешнего представления данных, записанных в 1 или нескольких ячейках. К параметрам форматирования относится: вид и размер шрифта, рамка, цвет, выравнивание содержимого ячейки. Они задаются с помощью команд меню или кнопок панели инструментов. Его можно произвести до ввода данных в таблицы. Нельзя форматировать 1 ячейку.

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

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

Рисунок 2

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

2. Процессоры электронных таблиц

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

Предоставляет следующие возможности:

• Создание табличной структуры, традиционной для хранения данных.

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

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

• Создание сводных, то есть интерактивных таблиц, облегчающих анализ большого объема данных. Пример: сводная таблица финансов большого рекламного агентства, которая формируется в центральном офисе.

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

Рисунок3

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

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

• Автоматизированный поиск ошибок вычислений по формулам.

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

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

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

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

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

3. Особенности обработки числовых данных в электронных таблицах

К числовому типу относятся:

• константы (16Е +0,3).

• даты (02.03.04).

• время (14:34).

• дата и время (28 октябрь, 1999, 15:16).

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

Рисунок 4

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

При работе с электронными таблицами часто возникает необходимость их объединения.

Существуют инструменты объединения:

• Организация межтабличных связей. Связи между таблицами осуществляются путем путем использования внешних ссылок, адресов ячеек, содержащих кроме имени столбца и номера строки имя файла данные из которых используются [Exam.xls] C2. [Exam.xls]- имя файла,С2- ячейка. Таблица на которой нет внешней ссылки рассматривается как дополнительная, а таблица на которой есть внешние ссылки считается основной. Обе таблицы должны находиться в 1 папке или 1 каталоге. Между 2 таблицами могут существовать двух сторонние связи (перекрестные ссылки).

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

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

5. Построение диаграмм

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

В Еxcel используется около 20 различных диаграмм. Наиболее используемые столбцовые и круговые. Для того чтобы построить диаграмму можно воспользоваться мастером построения диаграмм, либо же вставить готовую диаграмму из меню Вставка --> Рисунок --> Диаграмма.

Рисунок 5

6. Технология решения задач коммерческой и рекламной практики средствами электронной таблицы

Существует обобщенная технология работы с электронными таблицами. Она включает 4 этапа:

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

• Работа с данными.

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

• Математические модели помогают на основе имеющихся таблицы, получить новую информацию, решением типовых задач компьютерного моделирования. Они звучат так: « что будет если…?», обратная задача: «как сделать чтобы…?».

Решение таких задач дает возможность пользователю узнать как изменятся входные параметры при изменении 1 или нескольких величин.

Расширением таких задач являются задачи анализа чувствительности, позволяющие определить как изменяется решение модели при изменении входных величин, заданным шагом в определенном диапазоне значений. Задача компьютерной модели «как сделать чтобы…?» возникает в случае если цель достижения определенного значения модели, пользователь ищет значение входных параметров, обеспечивающих достижение этой цели. Различные виды анализа данных, содержащихся в исходной таблице можно проводить с использованием функций и процедур. Пример: входящие в состав электронной таблицы статистические функции могут быть использованы в статистическом анализе или для прогноза, содержащихся в таблице данных. Использование финансовых функций позволяет произвести анализ эффективности, планируемых капиталовложений, рассчитать стоимость ценных бумаг. Для решения оптимизационных задач используются специальные модели математического программирования. Пример: если рекламная фирма имеет центральный офис и несколько филиалов, то появляется задача объединения документов и отчетов, приходящих из этих филиалов. Решение подобной задачи требует использования специальных многотабличных связей и программных методов для манипулирования с данными и генерации отчетных форм. Одновременная работа с несколькими таблицами - это одна из возможностей работы с данными в электронной таблицы. Если при работе с большими электронными таблицами требуется найти ту или иную строку (столбец) или отсортировать строки (столбцы) в нужном порядке, то следует использовать инструментарий СУБД, в этом случае электронная таблица интерпретируется как база данных.

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

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

Схематично обобщенная технология.

7. Электронные таблицы для 4 класса информационных технологий (поддержки принятия решений)

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

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

Рисунок 6

Вычисление ежемесячного платежа производится специальной функцией. Структура, которая имеет вид @PMT (PV,Rate,Nper) вычисляет сумму периодического платежа при условии, что PV- размер суды, Rate- процентная ставка, Nper-число платежных периодов, при этом значение, которое определено для Rate, должны корректироваться с единицами, используемыми для Nper, если платеж делается ежегодно, то N измеряется в годах, если ежемесячно, то в месяцах. Для 1 альтернативы функция будет иметь следующие параметры @PMT(14999,13/12,36) или @PMT(B6,B7/12,B9*12). Функция носит характер примера, в любой версии электронной таблицы можно найти подобную функцию в разделе финансовые функции. В версии Excel название финансовых функций используется на русском языке.

Разработанная таблица дает возможность выбрать покупателю 1 из 4 альтернатив и ответить на следующие вопросы: альтернатива 1- может ли покупатель выплачивать определенный месячный платеж за машину; альтернатива 2- что будет если вы согласитесь на дорогой авто и получите ссуду от его производителя; альтернатива 3- что будет если вы следующим летом заработаете некоторую сумму для первого платежа; альтернатива 4 - что будет, если вы увеличите срок возврата суды и получите более низкую процентную ставку.

Рассмотренные примеры иллюстрируют возможности электронной таблицы в решении задач вида « что будет, если…?» и обратного « как сделать, чтобы…?». Последняя задача возникает в том случае, если цель исследования- определение значений, параметра задач компьютерного моделирования. Различные виды анализа данных можно проводить с использованием встроенных функций и процедур: 1) статистических, 2) финансовых.

8. Примеры

Пример относительной и абсолютной адресации ячеек

• Относительная адресация: разработка электронной таблицы «Стоимость рекламных услуг в газете минутка», содержащую данные: № записи, название, размер, цена за единицу, количество, стоимость.

Рисунок 7

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

• Абсолютная адресация ячеек.

Рисунок 8

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

Пример объединения таблиц

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

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

Рисунок 9

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

Рисунок10

Таким образом возможно проведение одного экономического анализа.

Пример прайс - листа

Рисунок 11

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

Пример прогноза деятельности рекламной фирмы

Основными параметрами расчета являются : доход в любом году (определяется как произведение объема продаж в натуральную цену, прогнозные допущения (рост цен (от10-15%), рост продаж). В основе таблицы должны лежать финансовые показатели прошлого года.

Рисунок12

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

Вопросы:

1. Что такое электронная таблица?

2. Какие возможности предоставляет электронная таблица?

3. Какие типы данных могут храниться в электронной таблице?

4. Дайте определение относительной и абсолютной адресации ячеек, приведите пример.

5. Значение диаграмм и графиков в электронных таблицах.

6. Какие существуют инструменты объединения электронных таблиц?

7. Какие этапы решения задач в рекламной практике с помощью средств электронной таблицы вы знаете?

8. Роль электронных таблиц для 4 класса информационных технологий.

9. Какие типы задач можно решать с помощью электронных таблиц?

10. Приведите собственные примеры использования электронных таблиц?