Сводные таблицы в экселе задания и решения
Сводные таблицы, мощный инструмент Excel. Помогают собрать из солянки данных, нужные показатели по заданным критериям (чаще всего это сумма по нескольким наименованиям).
Если, не найдёте ответ на свой вопрос, пишите в комментариях.
Плюсы Сводных таблиц:
- Лёгкость создания отчетов по большому объему данных;
- Простота редактирования и изменения вида;
- Возможность группировать данные в диапазоны (например, даты объединить в кварталы или месяца, числа в интервалы);
- Можно добавить поля с расчетами, которых в исходной таблице нет;
- Данные из Сводных можно быстро визуализировать, построив график или диаграмму и даже создать не сложный интерактивный Дашборд (Dashboard).
Всё это делается в пару кликов мыши.
Главное правило при создании Сводной – исходные данные, по которым будет строится таблица, должны быть правильными. Что это значит?
- Данные исходной таблицы не должны содержать пустых заголовков, объединенных ячеек, заголовков внутри данных;
- Все данные, по которым будут производиться вычисления или группировка, должны иметь верный формат: дата или число;
- В одном столбце таблицы должны быть данные одинакового формата.
Как правило, исходные данные должны иметь плоскую, иерархическую организацию. Создав правильную структуру исходной таблицы, в Сводной можно легко просмотреть информацию на любом из ее уровней. Структура часто используется при создании отчетов для представления руководителям разных уровней. Так, высшему руководству фирмы представляются только итоги по подразделениям, руководителям подразделений — итоги по отделам, а каждому руководителю отдела — все данные по его отделу.
Делаем расчеты и приводим в заданный вид данные о расходах в Yandex Direct
В данном случае количество кликов - это столбец "взаимодействия".
Вводим формулу: =(E3/F3)*100
В некоторых ячейках видим ошибку, которая появляется при делении на нуль "0" :
Найдите материал к любому уроку, указав свой предмет (категорию), класс, учебник и тему:
5 863 112 материалов в базе
Анализ расходов рекламных кампаний, создание и настройка сводных таблиц
«Домашнее обучение. Лайфхаки для родителей»
Подарочные сертификаты
Ответственность за разрешение любых спорных моментов, касающихся самих материалов и их содержания, берут на себя пользователи, разместившие материал на сайте. Однако администрация сайта готова оказать всяческую поддержку в решении любых вопросов, связанных с работой и содержанием сайта. Если Вы заметили, что на данном сайте незаконно используются материалы, сообщите об этом администрации сайта через форму обратной связи.
Все материалы, размещенные на сайте, созданы авторами сайта либо размещены пользователями сайта и представлены на сайте исключительно для ознакомления. Авторские права на материалы принадлежат их законным авторам. Частичное или полное копирование материалов сайта без письменного разрешения администрации сайта запрещено! Мнение администрации может не совпадать с точкой зрения авторов.
Наименование рекламной кампании в таблице с расходами по Yandex Direct не совпадают с наименованиями из таблицы "Доходы". Значения ячеек с наименованиями кампаний Яндекса на листе доходов выглядят следующим образом:
chelyabinsk_brand_context|20112731
, где правая часть, состоящая из 8 символов (цифр) - это номер рекламной кампании. Его и будем использовать при суммировании дохода.
Для этого нам нужно:
- отфильтровать таблицу "Доходы" по столбцу "А" и критерию "yandex / cpc"
- скопировать полученный диапазон на новый лист (назовем его "Доходы Яндекс")
- отделить номер кампании от наименования при помощи текстовой функции "ПРАВСИМВ"
Функция "ПРАВСИМВ" отделяет заданное количество знаков/символов, начиная с правой части строки
Вид функции: =ПРАВСИМВ(текст ; количество знаков)
- Применяем фильтр и копируем таблицу с информацией о доходах с рекламы на Яндексе на новый лист
- Вставляем столбец правее столбца "С"
2.3. Подтянем информацию о доходах из таблицы "Доходы"
Другие материалы
В данном случае расходы - это стоимость, поэтому вписываем формулу: =О3
1.7. Считаем ROI (Доходы - Расходы) / Расходы
- Считаем значение показателя в связке с функцией "ЕСЛИОШИБКА", поскольку столбец расходы (делитель) содержит нулевые значения. Таким образом, формула будет иметь следующий вид: =ЕСЛИОШИБКА((I3-H3)/H3;0)
- Протягиваем вниз и получаем столбец, содержащий только числовые значения
Для дальнейшей обработки данных нам необходимо сделать элементы таблицы взаимосвязанными. Для этого нужно заполнить каждую пустую строку столбца "В" соответствующими наименованиями рекламных кампаний.
- Выделяем весь столбец "В"
- На вкладке меню "Главная" выбираем команду "Найти и выделить" и выбираем "Выделить группу ячеек"
- В появившемся диалоговом окне выбираем пункт "Пустые ячейки" и нажимаем "ОК"
- Выделяются все пустые ячейки
- В строке формул вводим координаты первой не пустой ячейки, значением которой нужно заполнить ячейки ниже нее.
- Нажимаем комбинацию CTRL+Enter
- Пустые ячейки заполнились значениями
Протягиваем вниз и получаем диапазон числовых значений:
2.1. Рассчитаем CR (Количество кликов / Количество показов) * 100%
- Присваиваем наименование заголовку и вводим формулу:
Теория и методика педагогического проектирования
Воспользуемся функцией "ЕСЛИОШИБКА" и уберем не числовые значения
Открываем "Мастер функций" и в категории логических функций находим "ЕСЛИОШИБКА"
В поле "Значение" вписываем формулу CR
2. Подтянуть информацию о доходах в обе таблицы.
3. Рассчитать показатели CR , CPO , ROI для каждой рекламной кампании.
4. Объединить данные в одну таблицу.
5. Сформировать сводные таблицы, настроить условное форматирование и вывести графики.
1. CR (коэффициент конверсии) = (Количество показов / Количество кликов) * 100%
2. CPO (стоимость покупки) = Расходы / Количество показов
3. ROI (рентабельность) = (Доходы - Расходы) / Расходы
Создание сводной таблицы
Чтобы быстро создать Сводную таблицу, встаньте на любую ячейку таблицы с источником данных, выберите на вкладке Вставка ► Рекомендуемые Сводные таблицы и выберите подходящий вариант из предложенных и нажмите Ok :
Убедиться, что Excel верно выбрал диапазон данных исходной таблицы, можно нажав на Изменить источник данных. , и прокрутив до конца текущей таблицы.
Чтобы не возникло проблем при выборе исходного диапазона, используйте в качестве источника данных Умную таблицу .
Если, не нашли подходящий вариант, создайте свой. Выберите на вкладке Вставка ► Сводная таблица :
В появившемся окне можно выбрать таблицу или диапазон, вариант ставки будущей таблицы, на текущий или на новый лист.
После настройки, нажмите Ok .
На месте вставки таблицы появится такое поле:
Слева откроется окно со списком полей Сводной таблицы:
Если окно со списком полей не открылось или закрылось в процессе работы с таблицей, нажмите на любую ячейку Сводной таблицы, правую кнопку мыши ► Показать список полей :
Профилактика синдрома «профессионального выгорания» у педагогов
Курс повышения квалификации
Чек-лист ► обратить внимание при подготовке данных:
Спасибо, что дочитали до конца!
Если Вам было интересно, ставьте лайк , пишите, что думаете в комментариях и подписывайтесь на канал, если еще не сделали этого.
2. Исследовать и применить приемы решения задач с помощью списков и сводных таблиц:
3. Развивать самостоятельность при выполнении задания и анализе результатов работы с числовой информацией
4. Развивать мышление через сравнение и анализ методов обработки числовой информации
5. Воспитывать нормы работы за ПК (правовая культура): правила работы с вычислительной техникой, с информацией, правила техники безопасности
ПРИОБРЕТАЕМЫЕ УМЕНИЯ И НАВЫКИ:
1) Навыки работы с мышкой
2) Навыки работы с встроенными функциями
3) Навыки построения экономической м математической моделей
СРЕДСТВА: инструкционная карта, ПК, электронные таблицы EXCEL , опыт студентов, опыт преподавателя
НОРМА ВРЕМЕНИ : 6 часов
ТЕХНИКА БЕЗОПАСНОСТИ : ЗАПРЕЩАЕТСЯ:
· трогать разъемы соединительных кабелей,
· включать и выключать аппаратуру без указания преподавателя,
· прикасаться к экрану и тыльной стороне монитора,
· класть дискеты, книги, тетради, ручки и т.п. на клавиатуру и монитор.
При длительной работе за ПК необходимо соблюдать следующие санитарные правила :
· при продолжительности работы 1,5 – 2 часа делать перерыв 10 мин. через каждый час;
· в случае возникновения у работающего зрительного дискомфорта и других неблагоприятных ощущений целесообразно выполнять комплекс упражнений для глаз и туловища.
1) При появлении запаха гари немедленно прекратить работу, отключить питание ПК и сообщить об этом преподавателю.
2) Не пытайтесь самостоятельно устранять неисправности в работе аппаратуры.
Вы отвечаете за сохранность рабочего места .
ПЛАН РАБОТЫ:
1) Подготовительный этап
2) Практический этап:
ð Исследовательский этап
ð Исполнительский этап
3) Аналитический этап
4) Домашнее задание
Подготовительный этап: Ответьте устно на вопросы:
1. При вводе числовых данных вместо десятичной дроби вы получаете данные в формате ДАТА. В чем причина? Ваши действия в этом случае.
3. Какие правила следует выполнять при вводе формул?
4. Какие действия необходимо выполнить, что бы настроить перенос слов по слогам внутри ячейки?
5. Какие действия необходимо выполнить, для того чтобы объединить ячейки?
6. Какие действия необходимо выполнить для автозаполнения содержимым ячеек
Практический этап: Ознакомьтесь с теоретическим материалом и выполните предложенные упражнения.
Совокупность данных в виде таблиц полей и записей называется списком или базой данных. MS EXCEL понятия список и база данных взаимозаменяемы. Действия, выполняемые со списком: сортировка (упорядочивание по определенному признаку), фильтрация (выборка данных по условию), автоввод (возможность ускорить ввод повторяющихся элементов списка), проверка вводимых (контроль на соответствие допустимому типу и значению), получение промежуточных итогов, построение сводных таблиц, подбор параметров (выбор из множества решений наиболее оптимального), связывание объектов (зависимость данных одного листа от данных, расположенных на другом листе), консолидация (автоматическое получение итоговых результатов на основе данных, которые могут располагаться в разных местах), импорт и экспорт данных (объединение текстовых и графических объектов из других приложений на рабочих листах).
1. Исследуем приемы работы в режиме списка:
В программе предусмотрены возможности работы со списками как с простыми базами данных. Пользователь имеет возможность поиска данных по ключу, использовать различные фильтры, вести упорядочение списков по значению.
Исследуем возможности программы на примере таблицы лучших теннисистов.
Исследуем прием работы с формой . ФОРМА – своеобразный шаблон записи в списке, состоящий из нескольких полей.
1. Создайте на первом листе под именем «Теннис» таблицу по образцу.
2. Введите заголовок таблицы «15 лучших теннисистов мира»
3. Установите курсорную рамку на любой столбец (поле) таблицы (списка)
4. выполните команду Данные – Форма.
5. На экране появиться диалоговое окно - ФОРМА
6. Данное окно можно использовать для ввода новой записи в список (через кнопку ДОБАВИТЬ ) или удалять существующие в списке записи (через кнопку УДАЛИТЬ ).
7. Режим ФОРМЫ позволяет осуществить выбор данных из списка по критериям (через кнопку КРИТЕРИИ ). В значения критерия можно использовать символ * (указывает произвольное количество неизвестных символов) и ? (указывает один неизвестный символ). При поиске числовых значений можно применять операторы сравнения: >, , =
8. выход из режима ФОРМЫ осуществляется через кнопку Закрыть или через пиктограмму Х
Упражнение 1 : (используя режим ФОРМА)
1. Добавьте в список запись: 16(21) Томас Мустер Австрия 1611
2. Удалите из списка запись: 12 (13) Марк Филлиппусис Австралия 1992
3. Осуществите поиск спортсменов из Испании:
§ Установите на полосе прокрутки бегунок в самое верхнее положение
§ нажмите кнопку Критерии
§ в поле Страна введите значение Испания
§ нажмите кнопку Далее
§ просмотрите содержимое списка по критерию выбора
4. Осуществите поиск спортсменов Испании, набравших не более 2000 очков (примените операторы сравнения)
5. Выполните поиск всех спортсменов, чьи имена начинаются на букву «С» из Франции
6.Выполните поиск всех спортсменов, чьи имена начинаются на букву «М» с набранными очками не менее 2700 очков
Исследуем прием работы Сортировки данных . Сортировка, или упорядочение, данных в списке может пригодиться для лучшего восприятия записей списка и удобного извлечения сведений. Сортировку в списке можно выполнить сразу по трем уровням.
1. установите курсорную рамку в область списка
2. выполните команду Данные – Сортировка
3. в появившемся диалоговом окне укажите область сортировки и ее параметры
4. выбрав в диалоговом окне команду Параметры… на экране появиться новое диалоговое окно:
В данном окне можно уточнить вид сортировки, задающее направление сортировки: по строкам или столбцам, а так же необходимость сортировки по первому ключу: дни недели, месяцы, числа
Упражнение 2 : Установите параметры сортировки таким образом, чтобы новая база приобрела следующий вид:
Практическая ситуация: из разных филиалов ежедневно поступают отчеты и их надо объединить в один общий отчет. Отчеты филиалов имеют общий формат.
Например, что-то в это роде
Для получения быстрой аналитики можно воспользоваться сводными таблицами Excel. Чтобы сформировать сводную таблицу в которой будут все три филиала нужно их собрать на одном листе. Для этого создаем новый лист "Свод" и копируем туда данные из листов с отчетами.
Важно. Так как эта сводная таблица будет обновляться каждый день, то нужно ее сделать "умной", тогда не нужно будет каждый раз переписывать ссылки в формулах.
Делаем умную таблицу.
Практические советы по работе с экономическими данными в Excel, читайте в моей книге " Excel для экономистов. 10 статей про отчеты, сверки, сопоставления"
Копируем на лист "Свод" таблицу с первым филиалом, выделяем ее, заходим во вкладку "Вставка" и нажимаем кнопку "Таблица"
Ваша таблица примет следующий вид (если такой вид не нравится можно его поменять)
Теперь просто копируем данные из отчетов двух оставшихся филиалов, они тоже примут такой же формат. Кроме того, если ввести в эту таблицу формулу (например, средняя цена, то она будет автоматически копироваться при добавлении новых данных)
Если какой-то диапазон не попал при копировании в умную таблицу, ее границы можно легко расширить, достаточно потянуть за правый крайний угол (там есть небольшой синий значок)
В итоге получается такая умная таблица
На основании этой таблицы можно сделать сводные таблицы, которые позволят получать быструю аналитику. Например, такую
Или такую (все зависит от целей анализа)
Сводные таблицы, конечно, удобны для быстрой аналитики, но работать с ними не всегда удобно: сложно добавлять формулы, строки могут меняться в зависимости от состава данных, поэтому я больше люблю работать с таблицами, в которые данные подтягиваются при помощи формул
При помощи формул мы можем сделать, например, такую таблицу (такой же формат можно сделать и в сводной, кому что нравится)
Вот эта же таблица с формулами
Используется формула СУММЕСЛИМН(Таблица2[Сумма];Таблица2[Менеджер];$B$2;Таблица2[Номенклатура];A3)
Таблица2 - это автоматические название нашей умной таблицы (при необходимости его можно поменять, но я на практике никогда этого не делаю за ненадобностью).
В квадратных скобках [ ] ссылки на столбцы из умной таблицы
B2 и А3 ссылки на критерий поиска: конкретного менеджера (в данном случае Менеджера 1) и конкретную номенклатуру (в данном случае Чайник).
Если ссылку на менеджера закрепить знаками доллара $B$2, то формула легко и правильно скопируется вниз по столбцу.
По строке закрепление долларами не помогает (проверьте сами). В данном примере для копирования по строке я во все столбцы по строке 3 скопировала формулу из ячейки В3 без знака равно, потом в каждой формуле поставила знак равно, изменила ссылку на нужного Менеджера, закрепила ее знаком доллара и скопировала вниз по столбцу. (В сети есть рекомендации по доработке формулы, чтобы ссылка на умную таблицу тянулась и по строке, я не использовала, так как формула и без того длинная)
Подсветка красным - это условное форматирование (максимальные продажи по строке номенклатура)
В этой таблице по полученным данным можно добавлять любые формулы (например, мотивацию, штрафы, доли и прочее).
Если появится новая номенклатура или новый менеджер, то нужно будет заводить новую строку или столбец, автоматически он не добавиться.
Проверим, как работают умные таблицы при вводе новых данных. В умную таблицу добавим продажи пульта по "Менеджеру 1" за 3.02.19 на сумму 2000 руб. (Если вы буде вводить прямо под умной таблицей, ее диапазон автоматически расширится, если не расширится расширьте его сами, как я писала выше)
Цель работы: освоить навыки создания, редактирования и анализа данных на основе сводных таблиц.
Построить сводную таблицу для расчета месячной заработной платы рабочих при повременной форме оплаты труда, начисления премии и учета удержаний. Премия дифференцирована по разрядам: 2 разряд 20%, 3 разряд 30%, 4 разряд 40% к тарифу, 5 разряд – 50%. Удержания берутся со всех видов начислений (зарплата, премия) и составляют 13% от суммы начислений.
Методика выполнения работы
1. Открыть новую книгу.
2. Переименовать лист в Картотека.
3. Подготовить исходные данные (см. табл. 1)
Таблица 1.
4. Установить курсор в список, выполнить команду меню Вставка → Сводная таблица для вызова Мастера сводных таблиц и диаграмм.
5. Указать тип источника – Создать таблицу на основе данных, находящихся в списке или базе данных Microsoft Excel . Выбрать вид создаваемого отчета → Сводная таблица.
6. Выполнить проверку диапазон выделенных ячеек списка. Диапазон включает имена столбцов и все заполненные строки таблицы.
7. Разместить поля в макете сводной таблицы:
Фильтр отчета – Профессия, Названия строк – ФИО, Названия столбцов –
Разряд работающего, Значения – Тариф, Операция – Сумма.
Макет сводной таблицы представлен на рисунке 1.
Рис 1. Макет сводной таблицы
8. На ленте Конструктор выполнить команды: Общие итоги → Включить по столбцам; Выбрать стиль сводной таблицы. На ленте Параметры: Сводная таблица → Параметры . В открывшемся окне задать Для пустых ячеек отображать – пробел; Сохранять форматирование ячеек. Нажать кнопку ОК.
В сводной таблице (рис. 2) представлен список всех работающих. Для каждого работающего указан только один тариф, соответствующий его разряду.
Для преобразования сводной таблицы следует:
1. Установить курсор в область сводной таблицы.
2. Выполнить команду Параметры (Анализ – MS Excel 2013) → Формулы → Вычисляемое поле
для создания вычисляемого поля.
Рис. 2. Сводная таблица.
3. На рис. 3 представлено диалоговое окно для формирования вычисляемого поля. Имя поля – Зарплата, Формула вычисления: =Тариф*168.
(Коэффициент 168 зависит от количества рабочих часов в текущем учетном периоде.). Для добавления поля в формулу можно воспользоваться кнопкой Добавить.
Рис. 3. Создание вычисляемого поля
4. Установить курсор в область сводной таблицы.
5. С помощью кнопки Список полей на ленте Параметры откройте макет
сводной табл ицы для корректировки.
6. Удалить поле Сумма по полю Тариф (простым перетаскиванием мышкой за поле окна).
7. Установить курсор в области сводной таблицы на поле Сумма по полю Зарплата .
8. На ленте Параметры выполнить команду Активное поле → Параметры поля (рис. 4):
- Изменить имя поля в сводной таблице – Месячная зарплата. Нажать кнопку Числовой формат и указать формат поля – Денежный.
- Нажать кнопку ОК.
Рис. 4. Задание параметров вычисляемого поля
9. Установить курсор в область сводной таблицы на поле Разряд работающего.
10. Создать вычисляемый объект Премия. Премия выплачивается как процент к начисленной заработной плате, дифференцируется по разрядам: 2 разряд – 20%, 3 разряд – 30%, 4 разряд – 40%, 5 разряд – 50%.
- На ленте Параметры выполнить команду Формулы → Вычисляемый объект (рис. 5). Указать имя объекта – Премия.
- Для построения формулы в окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы
- Нажать кнопку Добавить.
- Закрыть окно – кнопка ОК.
Рис. 5. Создание вычисляемого объекта
11. Установить курсор в область сводной таблицы на поле Разряд работающего. Создать вычисляемый объект Вычеты, сумма вычетов это 13% от суммы заработка и премии.
Выполнить команду Формулы → Вычисляемый объект. Указать имя объекта – Вычеты (рис. 6).
Рис. 6. Создание вычисляемого объекта
В окне Поля выбрать поле Разряд работающего, в окне Элементы выбрать элементы для построения формулы вида:
- Нажать кнопку Добавить.
- Закрыть окно – кнопка ОК.
12. Выполнить команду Параметры → Формулы → Вывести формулы для просмотра выражений вычисляемых полей и объектов (рис. 7).
Если потребуется изменить нормативы (количество отработанных часов, % премии, % вычетов), следует отредактировать вычисляемые поля и объекты – команда меню Формулы → Вывести формулы , вызывать поле/объект, внести изменения
Рис. 7. Вывод формул
13. Переименовать лист, содержащий сводную таблицу, присвоив имя, Сводная таблица 1.
14. Поставить курсор внутрь сводной таблицы и на ленте Конструктор выполнить команду Макет отчета. Выбрать тип отчета.
15. Поставить курсор внутрь сводной таблицы и щелкнуть на ленте Параметры кнопку Сводная диаграмма.
16. В готовой диаграмме перетащить Разряд работающего в область Поле ряда. Выбирая вид профессии просмотреть данные по различным профессиям.
17. Сохранить рабочую книгу.
Курс повышения квалификации
Задачи
Наименование рекламной кампании в таблице с расходами по Yandex Direct не совпадают с наименованиями из таблицы "Доходы". Значения ячеек с наименованиями кампаний Яндекса на листе доходов выглядят следующим образом:
chelyabinsk_brand_context|20112731
, где правая часть, состоящая из 8 символов (цифр) - это номер рекламной кампании. Его и будем использовать при суммировании дохода.
Для этого нам нужно:
- отфильтровать таблицу "Доходы" по столбцу "А" и критерию "yandex / cpc"
- скопировать полученный диапазон на новый лист (назовем его "Доходы Яндекс")
- отделить номер кампании от наименования при помощи текстовой функции "ПРАВСИМВ"
1.8. Удаляем все лишнее и оставляем только значения без формул
- Нажимаем левой кнопкой мыши в левый верхний угол таблицы. Выделяется вся область листа. Копируем ее и вставляем значениями на новый лист через инструмент "Специальная вставка".
- Для этого встаем на ячейку нового листа, нажимаем правой кнопкой мыши и выбираем команду "Специальная вставка":
- Так же это можно сделать, выбрав соответствующий параметр вставки:
- Присваиваем листу, на который вставили значение, наименование "Данные":
Делаем то же самое для столбца "А". Таблица содержит строки с итогами по каждой рекламной кампании, их нужно удалить, что бы они не задвоили данные в итоговом расчете.
- Ставим фильтр на заголовок
- В заголовке наименования выбираем значения фильтра "по всем кампаниям"
- Нажимаем "ОК" и удаляем фрагмент отобразившейся таблицы кроме заголовка
- Далее переходим к фильтру заголовка "Дата" и выбираем поля "итого" и "пустые"
- Удаляем полученные данные и снимаем фильтры
- Приводим таблицу к необходимому виду. Добавляем пять столбцов после столбца "Е"
Настройка внешнего вида таблицы
После добавления Сводной таблицы. если нажать на её поле на панели вкладок появится две новых вкладки, Конструктор и Анализ :
Вкладка Конструктор отвечает за настройку внешнего вида, а вкладка Анализ за работу с данными. Попробуйте самостоятельно настроить внешний вид таблицы. Файл для тренировки .
Первое видео из серии Сводные таблицы в Excel , о том, как создать сводную таблицу, изменить вид, как группировать данные, как использовать фильтры в сводных, изменить источник исходных данных таблицы ⬇⬇⬇
Полезное по теме:
Специфика преподавания дисциплины «Информационные технологии» в условиях реализации ФГОС СПО по ТОП-50
Курс повышения квалификации
Делаем расчеты и приводим в заданный вид данные о расходах в Google Adwords
Поля сводной таблицы
Теперь поговорим о полях.
Черный треугольник в правом верхнем углу:
Нажав на него вы сможете переместить поле, изменить его размер или закрыть. Крест закрывает окно.
Нажав на нее вы сможете выбрать расположение разделов и полей окна, развернуть или свернуть данные, например группированные в Кубах по определённому признаку, провести сортировку для удобства поиска, группировать связанные таблицы.
Поле поиск , говорит само за себя, если столбцов с данными в исходной много, удобно использовать именно поиск чтобы найти нужный показатель. Просто введите его название.
Поле Фильтры . Перетащите в него необходимые показатели, по которым в дальнейшем вы сможете фильтровать Сводную таблицу выбором похожим на стандартный Фильтр.
Поле Столбцы. Перенесите в него показатели по которым необходимо вывести данные в столбцах, например месяца, годы, статусы заявок.
Поле Строки. Аналогично, полю Столбцы. Наполнение обоих полей зависит от того какие показатели и в каком разрезе вы хотите получить.
Поле Значения. После заполнения полей в это поле можно добавить значения числа, по ним Excel проведет суммирование или текстовые показатели, по которым он проведет суммирование по количеству.
Отложить обновление макета. Если, ваша книга Excel содержит много формул или исходная таблица имеет большой объем данных, установите галку в поле Отложить обновление макета , тогда таблица не будет обновляться, а Excel зависать, до настройки всех её полей. Когда полностью настроите поля нажмите Обновить или снимите галку.
1.5. Доходы
- Встаем на ячейку "I2" (Доходы), заходим в мастер функций и выбираем функцию "СУММЕСЛИМН"
- Для поля "Диапазон суммирования" указываем на листе "Доходы" столбец "F" (Total Value)
- Для поля "Диапазон условия1" указываем на листе "Доходы" столбец "С" (Campaign)
- Для поля "Условие 1" указываем столбец "В" таблица с расходами по рекламной кампании Гугл
- Для поля "Диапазон условия2" указываем на листе "Доходы" столбец "А" (Source / Medium)
- Для поля "Условие 2" указываем значение "google/ cpc". По этому значения мы выберем данные по расходам на рекламу в Google Adwords.Обязательно указывать текстовое значение в кавычках.
1.3. Добавляем столбцы в таблицу
1.6. Считаем CPO (расходы/количество показов)
- Вводим в ячейке J3 формулу: =H3/E3, протягиваем до конца таблицы
В правой части таблице есть столбец, содержащий информацию о расходах.
Просто скопируем и вставим его в столбец соседний "CR".
Оставьте свой комментарий
- 03.02.2017 6380
- DOCX 809.7 кбайт
- 237 скачиваний
- Рейтинг: 5 из 5
- Оцените материал:
Настоящий материал опубликован пользователем Пономарев Сергей Васильевич. Инфоурок является информационным посредником и предоставляет пользователям возможность размещать на сайте методические материалы. Всю ответственность за опубликованные материалы, содержащиеся в них сведения, а также за соблюдение авторских прав несут пользователи, загрузившие материал на сайт
Если Вы считаете, что материал нарушает авторские права либо по каким-то другим причинам должен быть удален с сайта, Вы можете оставить жалобу на материал.
Автор материала
40%
59 минут
«Как поддержать и уберечь подростка на первых трудовых шагах»
65 минут
«Роль социального педагога в инклюзивном образовании»
22 минуты
«Творческая работа с детьми (3-10 лет) по теме Горы»
Учителя о ЕГЭ: секреты успешной подготовки
Время чтения: 11 минут
Тысячи учителей в Австралии вышли на забастовку
Время чтения: 2 минуты
Правила приема в вузы детей военнослужащих начнут работать с предстоящего учебного года
Время чтения: 1 минута
Вам будут интересны эти курсы:
Таблица "Доходы" содержит информацию о расходах по нескольким рекламным кампаниям с детализацией до уровня дня. Для того, что бы получить данные о расходах каждой рекламной кампании Google Adwords воспользуемся функцией "СУММЕСЛИ".
1.2. Считаем коэффициент конверсии CR
1.1. Добавляем столбцы в таблицу
Протягиваем формулу до конца таблицы
Для того, чтобы избавиться от этой ошибки, воспользуемся логической функцией "ЕСЛИОШИБКА"
Напомним, что эта функция имеет следующий вид: =ЕСЛИОШИБКА(Значение; Значение если ошибка). В качестве значения нужно указать введенную нашу функцию "ВПР". Именно значения этой функции мы будем проверять на наличие ошибок.Таким образом вид измененной формулы: =ЕСЛИОШИБКА(Н3/Е3; 0 ).
Сопоставляя номера рекламных кампаний, подтянем информацию о доходах:
2.2. Заполняем поле "Расходы" значениями
1. Привести структуру таблиц расходов к одному виду:
Задача 1 : По данным о расходах на рекламные кампании в интернете и данным о доходах из этих источников необходимо сделать отчет, в котором должны быть представлены данные в разрезе показателей:
- Сессии, Транзакции, CR, Расходы, Доходы, CPO, ROI
Задача 2: Создать правила условного форматирования для итоговой таблицы,
Построить 2 диаграммы:
- Круговая диаграмма по доходам, Гистограмма ROI в разрезе каналов
- Таблица с расходами по рекламной кампании на Google Adwords:
- Таблица с расходами по рекламной кампании на Yandex Direct:
- Таблица с информацией о доходам с группировкой по источникам рекламы:
В поле "Значение если ошибка" вписываем "0"
Нажимаем "ОК" и протягиваем до конца таблицы
Столбец "В" таблицы содержит информацию о наименованиях рекламных кампаний. Для каждой рекламной компании имеются данные о расходах с детализацией до уровня дня. Наименование кампании содержится только рядом с первой датой периода.
1.4. Расходы
Читайте также: