Excel заменить формулу на значение

Содержание

1 Возможности Excel, без которых не обойтись специалисту по контекстной рекламе

Возможности Excel, без которых не обойтись специалисту по контекстной рекламе

Статья написана в рамках статейного конкурса Serpstat и SEOnews. Условия конкурса.

Эта статья для тех, кто пользуется Excel на продвинутом уровне. Здесь вы не найдёте информацию об использовании стандартных фильтров и сортировок, о форматировании и видах разбивки по столбцам. Я расскажу, как использую для решения реальных задач в кампаниях некоторые средства Excel: условия, формулы внутри формул, продвинутые замены, регулярные выражения, формулу =ВПР.

Математические действия с учетом условия/нескольких условий

Чаще всего в работе с одним или несколькими условиями я сталкиваюсь с суммированием:

=СУММЕСЛИ: применяется, когда необходимо сложить значения по одному условию;
=СУММЕСЛИМН: применяется, когда необходимо вычислить сумму значений с учётом нескольких условий.

Реже в моей практике встречаются ситуации, где с учётом выполнения одного или нескольких условий необходимо получить среднее значение : =СРЗНАЧЕСЛИ или =СРЗНАЧЕСЛИМН.

Эти действия — суммирование и получение среднего значения — работают по одному принципу. Покажу решение на примере суммирования.

Синтаксис выглядит следующим образом:

=СУММЕСЛИ(Диапазон;Условие;Диапазон суммирования);

=СУММЕСЛИМН(Диапазон суммирования;Диапазон условия1;условие1;Диапазон условия2;условие2;…).

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

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

Диапазон суммирования — область таблицы для вычисления суммы по заданному условию.

Диапазон условия — область таблицы для задания условия, по которому будет происходить суммирование.

Пример использования

Представьте, что при заливке кампании в Google AdWords мы забыли разбить расписание показов на дни недели и часы. При этом наша задача — расставить корректировки по временным сегментам.

Сначала заходим в отчёт:

И выгружаем его:

Далее работаем с выгрузкой. С помощью формулы =СУММЕСЛИМН мы можем по трём условиям свести данные по дням недели, где каждый день разбит на несколько сегментов времени. Красной линией подчёркнут диапазон условий, зелёной — условия и диапазоны условий.

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

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

Использование формул внутри формул

Для решения большинства реальных задач я часто использую сложные составные формулы. Например:

=ПОДСТАВИТЬ(ЗАМЕНИТЬ(C3;ПОИСК(D3;C3)—1;ДЛСТР(C3)—ПОИСК(D3;C3)+2;«»);«http://»;«http://»&D3&«.«)

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

=ЕСЛИ(Условие;Значение, если условие выполняется;Значение, если условие не выполняется);

=ЕСЛИОШИБКА(Значение;Значение, если ошибка);

=ПОИСК(Подстрока, которую ищем;Строка, в которой ищем).

Пример использования

Перед нами стоит задача — выгрузить все url, которые есть в аккаунте, и провести проверку определённой их части на наличие конкретного символа. Это позволит клиенту заменить искомый символ на другой и регулировать замену лендинга, если она необходима. Как выделить определенную часть url я расскажу далее в статье, а пока предположим, что она у нас уже есть:

Что мы делаем дальше:

С помощью формулы =ПОИСК ищем позицию, на которой встречается символ «~».
Мы понимаем, что если символ «~» не содержится в исходной строке, то будет возникать ошибка. Поэтому подключаем формулу =ЕСЛИОШИБКА и задаём в случае ошибки значение 0.
Получается, если значение 0, то замена лендинга не требуется. Если значение >0, то замена требуется. Для определения этого условия используем формулу =ЕСЛИ и по условию формируем значение «Да» или «Нет» для колонки «Требуется замена лендинга».

Разумеется, задачу можно решить иначе: разбить url на сегменты по знаку «/» и поместить каждый сегмент в отдельный столбец. Однако надо учитывать, что этих сегментов может быть три, а может — двадцать. Представьте, сколько времени это займёт и как будет выглядеть итоговая таблица.

NB Символ тильда «~» является служебным в Excel. Он используется перед служебными знаками «?», «*» или «~» для поиска соответственно вопросительных знаков, звездочек и других знаков тильды. Поэтому в формуле мы используем выражение «~~».

Выделение подстроки с помощью регулярных выражений

Отмечу, что речь идёт о решении для Google-таблиц, так как в Excel вы можете решить задачу через макросы VBA.

В Google-таблицах есть 3 формулы, которые позволяют работать с регулярными выражениями:

=REGEXREPLACE: заменяет часть строки на другой текст с помощью регулярного выражения;
=REGEXEXTRACT: извлекает определенную часть текста, соответствующую регулярному выражению;
=REGEXMATCH: проверяет, соответствует ли текст регулярному выражению.

Синтаксис выглядит следующим образом:

=REGEXREPLACE(Исходная строка;Регулярное Выражение;Выражение для замены);

=REGEXEXTRACT(Исходная строка;Регулярное Выражение);

=REGEXMATCH(Исходная строка;Регулярное Выражение).

Исходная строка — текст, на который применяется регулярное выражение.

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

Выражение для замены — текст, на который необходимо заменить найденное регулярное выражение.

Пример использования

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

Строим шаблон на весь url «(.+)/(.+)/(.+)+$». Фактически получается, что мы его разделяем на 3 группы:

Первая группа выделяет протокол сайта;

Вторая группа выделяет домен;

Третья группа выделяет часть url, которая стоит между последним «/» и концом строки.

У каждой такой группы на выходе будет соответственно свой уникальный номер $1, $2, $3. Так как нам нужна последняя группа, мы заменяем всю строку на третью группу, то есть на «$3»:

И проверяем, соответствует ли url регулярному выражению, например, с помощью =REGEXMATCH:

Если же мы хотим решить задачу с помощью формулы =REGEXEXTRACT, то действуем иначе. Строим шаблон на часть url, которая идет с конца строки до последнего одинарного «/»: «[\w.+\-\~]+$».

Проверяем url на соответствие регулярному выражению:

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

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

Продвинутая замена

Заменять значения можно с помощью двух формул: =ЗАМЕНИТЬ и =ПОДСТАВИТЬ.

Формула =ЗАМЕНИТЬ позволяет делать любые замены указанного числа знаков на новую подстроку. Формула =ПОДСТАВИТЬ используется, когда нужно заменить заранее известную подстроку в строке.

Синтаксис выглядит следующим образом:

=ЗАМЕНИТЬ(исходный текст;начальная позиция;число знаков;текст для замены);

=ПОДСТАВИТЬ(исходный текст;текст, который заменяем;текст для замены).

Исходный текст — строка, в которой будем производить замену.

Начальная позиция — позиция в исходном тексте, начиная с которой производится замена.

Число знаков — общее число знаков, которое подвергается замене.

Текст для замены — текст, на который заменяем выбранную подстроку.

Текст, который заменяем — подстрока, которую заменяем.

Пример использования

Задача — разместить часть url в области субдомена, а из самого url убрать её.

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

Решение с регулярными выражениями выглядит следующим образом:

Манипуляции с url бывают разными и зависят от задачи, которую надо решить в рекламной кампании. Например, полученные здесь субдомены мы использовали в объявлениях. Наше решение однозначно ускорило работу, так как в выборке было более 5000 url.

Формула =ВПР

Формула =ВПР выполняет вертикальный поиск в таблице, позволяет найти и забрать данные из неё и переставить значения в соответствующие поля другой таблицы.

Синтаксис выглядит следующим образом:

=ВПР(искомое значение;таблица для поиска;номер столбца;интервальный просмотр).

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

Таблица для поиска — таблица, по которой осуществляется поиск и из которой забираем данные в исходную таблицу.

Номер столбца — порядковый номер столбца,из которого мы забираем данные в таблице для поиска.

Интервальный просмотр — точное или приблизительное соответствие исходного значения значениям из таблицы для поиска.

Пример использования

Предположим, есть задача — свести онлайн-данные с площадки с офлайн-данными в разрезе регионов. На входе мы имеем две таблицы. В таблице № 1 представлены онлайн-данные: показы, клики, расход, лиды. В таблице № 2 представлены офлайн-данные: заключённые договоры, отказы.

Используем формулу =ВПР и переставляем данные из таблицы № 2 в таблицу № 1. Сначала забираем в первую таблицу значения из столбца «Заключённый договор»:

Затем меняем в формуле номер столбца и забираем данные по «Отказам»:

Благодаря формуле =ВПР данные переносятся в соответствии с регионами, даже если порядок значений в этом столбце или точное соответствие в написании нарушены.

Важно помнить, что делать такую интеграцию данных вручную на большом проекте сложно и неэффективно. В этом случае лучше один раз выстроить систему аналитики на базе Google BigQuery, например. Затем автоматизированно в одном месте собирать данные из Google Analytics, «Яндекс.Метрики», CRM-системы и других источников. Наше решение удобно для локальных задач, когда нет возможности быстро настроить полноценную систему аналитики, а данные нужны здесь и сейчас.