#excel #excel-formula #dashboard
#excel #excel-формула #Информационная панель
Вопрос:
У меня есть ежедневный менеджер производительности для рекламы Google и тому подобного. Каждый день или каждые несколько дней я копирую базовые данные показателей в таблицу и могу видеть производительность и бюджетные затраты с течением времени.
Это выглядит примерно так:
date Clicks Impression CTR Cost Conversions CPA CPC CVR
01/03/2019 23 150 15% $20 2 $10 $0.67 8.7%
02/03/2019 23 150 15% $20 2 $10 $0.67 8.7%
03/03/2019 23 150 15% $20 2 $10 $0.67 8.7%
04/03/2019 23 150 15% $20 2 $10 $0.67 8.7%
05/03/2019 23 150 15% $20 2 $10 $0.67 8.7%
06/03/2019 23 150 15% $20 2 $10 $0.67 8.7%
07/03/2019 23 150 15% $20 2 $10 $0.67 8.7%
08/03/2019 23 150 15% $20 2 $10 $0.67 8.7%
09/03/2019 23 150 15% $20 2 $10 $0.67 8.7%
И так далее до конца месяца. Это повторяется для каждого месяца. В конце каждого раздела месяца указывается название месяца и итоговые данные за каждый месяц.
Я управляю большим количеством клиентов и создал одну книгу Excel, которая позволяет мне выполнять все это с помощью таблицы для каждого клиента. Моя проблема в том, что я хотел бы иметь панель мониторинга, содержащую моментальный снимок производительности каждого клиента за месяц, чтобы упростить ее для себя.
Моей первой попыткой было использование сопоставления индексов, однако для его работы вам нужна таблица. Поэтому я попытался использовать именованные диапазоны для создания таблицы. Я создал Client1StatsTable, где я выбрал только строки с итогами за месяц и диапазон Client1MonthName, в котором было название месяца.
Это означало, что я думал, что смогу выполнить диапазон в сопоставлении ИНДЕКСОВ, как если бы это была реальная таблица. Я написал следующие 2 формулы:
INDEX(Client1StatsTable, MATCH(TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),"mmmm"),Client1MonthName,0), 3)
INDEX(Client1StatsTable,MATCH(K12,Client1MonthName,0),3)
Я бы повторил это для каждого клиента, чтобы получить моментальный снимок производительности по различным показателям за последний месяц.
Однако я получил ошибку #N / A. Когда я прогнал это через функцию Evaluate Formula в Excel, я обнаружил, что, хотя диапазон был проанализирован правильно как несколько строк и ячеек, которые были разъединены, он застрял при попытке поместить месяц в соответствие.
Он правильно проанализировал месяц, но просто не смог найти его в диапазоне.
Конечная таблица должна содержать всех клиентов в одной таблице и выглядеть следующим образом:
Date ClientName CTR CPC CPA CVR Spend Amount of Budget Used
04/03/2019 Client 1 5% $0.86 $15 2% $30 15%
Я бы предпочел выполнить это без использования VBA.
Комментарии:
1. Рассматривали ли вы возможность использования сводных таблиц?
2. Сводная таблица теоретически будет работать только для моментального снимка с разбивкой по годам, который представлен 2-й формулой. Потому что в этом случае мне пришлось бы создавать отдельную таблицу для каждого клиента. Для текущего месяца я хочу создать снимок за текущий месяц для каждого клиента в одной таблице. Хотелось бы что-то вроде: Дата, имя клиента, CTR, CPC, CPA, CVR, Потраченный объем используемого бюджета
3. Для индекса и сопоставления не обязательно, чтобы данные находились в таблице: индексированный диапазон может быть на листе 2, а диапазон соответствия — на листе 1, все , что необходимо, — это чтобы оба диапазона были одинаковой длины.
4. У меня сработали обе формулы. Мне пришлось изменить форматирование даты на «ММММ», хотя, поскольку «мммм» означало минуты, а не месяцы (это может зависеть от системного языка). Вы также можете заглянуть в PowerQuery (надстройка для 2010/2013, интегрирована в 2016) для этого, если вы еще этого не сделали.
5. @SolarMike да, но на основе инструмента вычисления формулы . Оно застревает внутри сопоставления само по себе
Ответ №1:
Вы можете воссоздать pivot table по своему усмотрению, используя sumproduct-filters с booleans
=Sumproduct(--(condition_1);--(condition_2); Sum_of_range)
Вот ваш пример:
CTR для клиента A на дату 03.04.19
= Sumproduct(--(Client_range='A');--(Date_range='4/3/19'); Sum_of_CTR)
Сделайте то же самое для остальных полей вашей информационной панели
Комментарии:
1. У каждого клиента есть свой собственный лист, и в нижней части каждого раздела месяца уже есть подсчет для каждого листа. Я пытаюсь создать таблицу на панели мониторинга, которая выводит эту сумму за текущий месяц таким образом, чтобы динамически выбирать соответствующий месяц.
Ответ №2:
В конце концов я нашел ответ, благодаря @hsan, я изначально думал, что значения между названиями отдельных месяцев будут мешать сопоставлению ИНДЕКСОВ. Я просмотрел исходный код, в котором он тестировался, и установил конечное значение в MATCH равным -1. Итак, это было:
INDEX(ClientName1!$A$42:$K$420,MATCH(TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),"mmmm"),ClientName1!$A$42:$A$420,-1),3)
Но когда я сделал
INDEX(ClientName1!$A$42:$K$420,MATCH(TEXT(DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())),"mmmm"),ClientName1!$A$42:$A$420,0),3)
это сработало