Слайд 2Понятие базы данных в Excel
Записи
Имена полей
Поле
Слайд 3Сортировка данных: Данные ► Сортировка
Слайд 4Фильтры
В Excel различают обычный Автофильтр и Расширенный фильтр.
Данные ►Фильтр ► Автофильтр
Слайд 5Расширенный фильтр
Данные ► Фильтр ► Расширенный фильтр
Слайд 6Функции баз данных
=БДФункция (база данных; поле; критерий)
Слайд 8БДПРОИЗВЕД
Функция перемножает значения в столбце, указанном в аргументе поле, которые берутся из
записей, соответствующих условиям аргумента критерий.
=БДПРОИЗВЕД(база данных; поле; критерий)
БДСУММ
Функция суммирует значения в столбце, указанном н аргументе поле, которые берутся из записей, соответствующих условиям аргумента критерий.
=БДСУММ(база данных; поле; критерий)
БСЧЁТ
Функция подсчитывает количество ячеек, содержащих числовые значения, в столбце, указанном в аргументе поле, которые берутся из записей, соответствующих условиям аргумента критерий.
=БСЧЁТ(база данных; поле; критерий)
Слайд 9ДМАКС
Возвращает наибольшее число в столбце, указанном в аргументе поле, при этом учитываются
только те записи, которые соответствуют условиям аргумента критерий.
=ДМАКС(база данных; поле; критерий)
ДМИН
Возвращает наименьшее число в столбце, указанном в аргументе поле, при этом учитываются только те записи, которые соответствуют условиям аргумента критерий.
=ДМИН(база данных; поле; критерий)
ДСРЗНАЧ
Возвращает среднее арифметическое значений в столбце, указанном в аргументе поле, при этом учитываются только те записи, которые соответствуют условиям аргумента критерий.
=ДСРЗНАЧ (база данных; поле; критерий)
Слайд 10Промежуточные итоги
Данные ► Итоги ► Промежуточные итоги
Слайд 11Сводные таблицы
Сводная таблица – динамическая таблица итоговых данных, извлечённых или рассчитанных на
основе информации, содержащейся в списках (базе данных)
Данные ► Сводная таблица
Слайд 12Создание сводной таблицы
Данные ► Сводная таблица
Слайд 13Работа с макетом сводной таблицы
В область строк
В область данных
В область столбцов
Слайд 14Готовая сводная таблица
Кнопка поля
Слайд 15Готовая сводная таблица
с добавлением в область столбцов группировки по полу
Слайд 16Установка пакета анализа
Сервис ► Надстройки
Выборка
Слайд 17Выборка
При проведении социологических и маркетинговых исследований в зависимости от полноты охвата изучаемого
объекта различают сплошное и не сплошное наблюдение. Выборочное наблюдение является примером не сплошного наблюдения.
Выборочным наблюдением называется метод статистического исследования, при котором обобщающие показатели изучаемой генеральной совокупности устанавливаются по некоторой ее части, называемой выборочной совокупностью или выборкой. Репрезентативность выборки означает, что ее объекты достаточно хорошо представляют генеральную совокупность.
В MS Excel реализована собственно случайная выборка, состоящая в том, что выборочная совокупность образуется в результате случайного отбора отдельных единиц из генеральной совокупности. Кроме того, возможен периодический метод выборки.
Слайд 18Режим Выборка
1. Поле Входной интервал содержит ссылку на ячейки с анализируемыми данными;
флажок Метки должен быть установлен, если первая строка (или столбец) во входном диапазоне содержит заголовки. Если заголовков нет, то флажок должен быть сброшен, и тогда будут автоматически созданы стандартные названия для данных выходного диапазона.
2. В положении Периодический становится активным поле Период, в которое вводится размер периодического интервала. При выборе положения Случайный в поле Число выборок необходимо ввести число размещаемых в выходном диапазоне случайных значений.
3. В поле Выходной интервал нужно ввести ссылку на левую верхнюю ячейку выходного диапазона. Размер выходного диапазона определяется автоматически. При выборе положения Новый рабочий лист результаты анализа будут размещены на новом листе, начиная с ячейки А1. В положении Новая рабочая книга открывается новая книга, в которой на первом листе, начиная с ячейки А1, размещаются результаты работы режима Выборка.
Слайд 19Корреляция
В маркетинговых и рекламных исследованиях часто приходится иметь дело со взаимосвязанными показателями.
Корреляционная связь является частным случаем стохастической связи. Зависимость называется стохастической, если проявляется не в каждом отдельном случае, а в общем, при большом числе наблюдений.
Признаки, вызывающие изменение других, связанных с ними признаков, называются факторными. Признаки, изменяющиеся под действием факторных признаков, называются результативными.
Наиболее простым случаем корреляционной связи является парная корреляция, т.е. зависимость между двумя признаками. Степень тесноты корреляции характеризуется коэффициентом корреляции.
По направлению связь может быть прямой и обратной. При прямой связи с увеличением или уменьшением значений факторного признака происходит увеличение или уменьшение результативного. В случае обратной связи увеличение значений факторного признака приводит к уменьшению значений результативного, т.е. изменения идут в противоположном направлении.
Слайд 20Режим Корреляция
Поле Входной интервал содержит ссылку на ячейки с анализируемыми данными, флажок
Метки должен быть установлен, если первая строка (или столбец) во входном диапазоне содержит заголовки.
Переключатель Группирование устанавливается в положение По столбцам или По строкам в зависимости от расположения данных во входном диапазоне.
3. В поле Выходной интервал нужно ввести ссылку на левую верхнюю ячейку выходного диапазона. При выборе положения Новый рабочий лист результаты анализа будут размещены на новом листе, начиная с ячейки А1. В положении Новая рабочая книга открывается новая книга, в которой на первом листе, начиная с ячейки А1, размещаются результаты работы режима Корреляция.
Слайд 21Определение коэффициента корреляции
Исходные
данные
Факторный
признак
Результативный признак
Коэффициент
корреляции
Слайд 22Режим «Корреляция»
Таблица оценки тесноты связи
Слайд 23Задание 5. Выбор оптимального медиа-плана кампании
Агентству необходимо составить оптимальную рекламную кампанию на
телевидении для своего клиента. Клиент своей рекламной кампанией хочет достичь трех целей (перечислены в порядке убывания важности):
цель 1: рекламу должны увидеть по крайней мере 65 млн мужчин с высоким уровнем дохода (ВУМ);
цель 2: рекламу должны увидеть по крайней мере 72 млн женщин с высоким уровнем дохода (ВУЖ);
цель 3: рекламу должны увидеть по крайней мере 70 млн людей с низким уровнем дохода (НУЛ).
Агентство может купить время для показа рекламных роликов в нескольких типах телепрограмм: в спортивных шоу, в развлекательных шоу, в новостях, во время показа комедийных фильмов, драм и во время показа сериалов. На рекламную кампанию не может быть потрачено больше 775 000 руб. Стоимости размещения рекламных роликов и охват потенциальной аудитории (в млн. человек) за одну минуту рекламного ролика в каждом типе телепрограмм представлены в таблице.
В рамках рекламной стратегии клиента требуется, чтобы, по крайней мере, два рекламных ролика были размещены в спортивных шоу, в новостях и показах драм. Также обязательным является условие, чтобы в каждом из типов телепрограмм было размещено не больше десяти рекламных роликов. Целью работы Агентства является нахождение плана рекламной кампании, который удовлетворял бы всем целям клиента и требовал бы минимальных затрат.
Слайд 24Исходные данные
Целевая ячейка
Изменяемые ячейки
Слайд 25Ввод формул
=СУММПРОИЗВ($E$3:$E$8;КолРол)
=СУММПРОИЗВ(В3:B8;КолРол)
Слайд 26Поиск решения
Поиск решения предоставляет возможность использовать одновременно большое количество (в общей сложности
до 200) изменяемых ячеек;
Поиск решения позволяет задавать ограничения для изменяемых ячеек.
Поиск решения предоставляет не заранее известный конкретный результат для целевой функции, как в случае использования метода подбора параметра, а отыскивает оптимальное (минимальное или максимальное), т. е. наилучшее из возможных, решение.
Для сложных задач средство Поиск решения способно генерировать множество различных решений.
Слайд 27Общие свойства задач, для решения которых можно воспользоваться надстройкой Поиск решения:
Существует единственная
целевая ячейка, содержащая формулу, значение которой должно быть сделано максимальным, минимальным или же равным какому- то конкретному значению.
Формула в целевой ячейке содержит ссылки (прямые или косвенные) на ряд изменяемых ячеек (содержащих неизвестные, или переменные решаемой задачи). Поиск решения заключается в том, чтобы подобрать такие значения этих переменных, которые бы давали оптимальное значение для формулы в целевой ячейке.
Может быть задано некоторое количество ограничений — условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек.
Слайд 28Сервис ►Поиск решения
В поле Установить целевую ячейку задается цель поиска решения.
На следующем
шаге указываются ячейки с переменными (Изменяя ячейки). Их значения будут изменяться в процессе поиска решения. Можно предоставить эту информацию, указав ссылки на ячейки или их имена либо выделив ячейки в рабочем листе.
Слайд 29Задание ограничений
Чтобы задать ограничения, в окне Поиск решения нажмите кнопку Добавить и
заполните Добавление ограничения.
Ограничение состоит из трех компонентов: ссылки на ячейку, оператора сравнения и значения ограничения. Задайте ссылку в поле Ссылка на ячейку, выберите оператор сравнения в раскрывающемся списке в середине этого окна и задайте значение ограничения в поле справа.
Чтобы поиск решений давал целые значения в окне Добавление ограничения задайте диапазон, значения которого должны быть целыми числами. Затем откройте раскрывающийся список в середине этого окна и выберите пункт Цел (Int). Поиск решения вставит слово Целое в поле Ограничение .
После задания ограничения нажмите кнопку ОК, чтобы вернуться в окно Поиск решения, или нажмите кнопку Добавить для задания следующего ограничения.
Слайд 30Результат Поиска решения
После заполнения окна диалога Поиск решения нажмите кнопку Выполнить.
Слайд 31Сценарии
Сценарий — это именованная комбинация значений, заданных для одной или нескольких
изменяемых ячеек в Модели «что-если».
Модель <<что-если>> — это любой рабочий лист, в котором можно подставлять различные значения для переменных, чтобы увидеть их влияние на другие величины, которые вычисляются по формулам, зависящим от этих переменных.
Изменяемые ячейки — это ячейки, содержащие значения, которые используются в качестве переменных.
Слайд 32 Определение сценария
В меню Сервис выберите команду Сценарии.
В окне диалога Диспетчер
сценариев, нажмите кнопку Добавить .
В окне диалога Добавление сценария, введите название сценария. В поле Изменяемые ячейки укажите, какие ячейки вы собираетесь изменять
Слайд 33Откроется окно диалога Значения ячеек сценария с полями для каждой изменяемой ячейки.
Эти поля содержат значения, которые в данный момент введены в рабочем листе.
В каждом поле можно ввести константу или формулу.
Слайд 34Создание отчетов по сценариям
Этот отчет показывает значения, которые каждый сценарий назначает
изменяемым ячейкам.
Слайд 35Таблица подстановки
Таблица подстановки позволяет представить результаты формул в виде зависимости от значений
одной или двух переменных, которые используются в этих формулах.
С помощью команды Таблица подстановки меню Данные можно создать два типа таблиц данных:
таблицу для одной переменной, которая проверяет воздействие этой переменной на несколько формул, или
таблицу для двух переменных, которая проверяет их влияние на одну формулу.
Слайд 36Таблицы подстановки для одной переменной
проверяет воздействие переменной на несколько формул
В рабочем
листе введите данные.
Введите формулу, в которой используется входная переменная.
Выделите диапазон таблицы данных — минимальный прямоугольный блок ячеек, включающий в себя формулу и все значения входного диапазона.
В меню Данные выберите команду Таблица подстановки. В окне диалога Таблица подстановки, задайте местонахождение входной ячейки в поле Подставлять значения по столбцам в или в поле Подставлять значения по строкам в.
Слайд 37Таблица с несколькими формулами
Функция ТАБЛИЦА используемая в формуле, имеет следующий синтаксис:
=ТАБЛИЦА(входная
ячейка для строки; входная ячейка для столбца)
Можно включить любое количество выходных формул при создании таблицы данных с одной переменной.
Если входной диапазон является столбцом, вторую формулу вводят непосредственно справа от первой, третью справа от второй и т. д.
Для различных столбцов допускаются разные формулы, но все они должны использовать одни и те же входные ячейки.
Слайд 38Таблица подстановки для двух переменных проверяет их влияние на одну формулу
Введите
первое множество входных значений в столбец.
Введите второе множество входных значений в строке, начинающейся выше и правее на одну ячейку от начала первого диапазона.
Формула должна быть введена в ячейку на пересечении строки и столбца, содержащих два множества входных значений. В таблице с двумя переменными допускается использование только одной формулы.