3 лайфхака по работе с Excel, которые облегчат жизнь маркетологу

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

• ВПР по двум или более столбцам

• Автоматическое протягивание формулы ВПР по столбцам

• Преобразование содержимого ячейки в число

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

Формула — это выражение, которое позволяет производить расчеты и преобразования с содержимым ячеек в Excel. Они всегда начинаются со знака «=» и вводятся в ячейке или в строке формул. Простой пример формулы — сложение чисел: формула «=СУММ(А1;В1)» позволяет сложить числа в ячейках А1 и В1.

Функция — это встроенные в Excel заготовки формул, которые проводят расчеты и преобразования по определенным алгоритмам. Пример функции — формула суммы: «=СУММ(А1;В1)». СУММ — это заранее подготовленная формула в Excel, которую можно использовать.

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

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

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

ВПР по двум или более столбцам

ВПР — чрезвычайно полезная функция. При этом большинство специалистов умеют ВПРить массивы только по одному столбцу. Но что, если вы хотите сВПРить таблицы, нужные данные в которых находятся сразу в двух столбцах или могут дублироваться (например, одинаковые ключевые слова находятся в разных группах)?

Объясню на примере. У меня такая необходимость часто возникает, когда нужно собрать в одну сводную таблицу статистику по большому количеству ключевых фраз, которая содержит данные из Мастера отчетов в Директе и из отчета Метрики. Например, отчет из Директа содержит данные о показах, кликах, позиции показа и расходах с детализацией «группа» — «ключевая фраза». А отчет из Метрики с такой же детализацией содержит данные о конверсиях и доходе Вот, как на скриншотах.

В чем сложность. Нужно подтянуть данные из одного отчета к другому. Но обычный ВПР по столбцу «Условие показа объявления» здесь не поможет, поскольку ключевые слова в нем дублируются в разных группах. В этом случае Excel просто подтянет данные из строки, которую найдет первой. Например, у ключа <купить белый диван> из группы <диваны_белые_рф> окажется статистика аналогичного ключа из группы <диваны_белые_мск>, поскольку этот ключ попадется функции первой. Нам это не подходит.

Что делать? Использовать ВПР. Загвоздка только в том, по какому столбцу функции давать ей команду искать данные.

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

1. Используйте формулу <=...&...>, чтобы объединить содержимое нужных ячеек (группа, условие показа). В одной ячейке можно объединить данные из двух и более столбцов.

2. Протяните данные по всему массиву — для этого зажмите правый нижний угол ячейки и протащите вниз до конца таблицы или просто нажмите два раза на правый нижний угол ячейки.

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

3. Проделайте то же самое с массивом данных из Метрики (сохраняя порядок объединения ячеек <Группа объявлений&Условие показа объявления>).

4. Теперь скопируйте полностью новый столбец и вставьте содержимое как значение. Это поможет избавиться от формулы.

Готово! Теперь обе наши таблицы содержат столбец, по которому можно сВПРить данные.

Осталось воспользоваться обычной формулой ВПР, где аргументом <искомое_значение> будет ячейка нашего нового столбца.

Не забудьте добавить знаки $ для фиксации нужных столбцов/строк, чтобы при протягивании они не смещались.

Автоматическое протягивание формулы ВПР по столбцам

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

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

В чем сложность. В том, чтобы протянуть формулу вниз, проблем обычно не возникает, если правильно закрепить нужные ячейки — так, чтобы массив, по которому функция совершает просмотр, не смещался при протягивании формулы. А вот если просто протянуть обычную формулу ВПР вправо, то в ней придется руками менять аргумент <номер_столбца>, из которого нужно подтянуть данные. Дело всё в том, что в формуле останется то значение, которое мы изначально ей задали, на сколько бы столбцов вправо мы ни протянули.

В данном случае на скриншоте нужно заменить «4» на «5», в следующем столбце — на «6» и так далее. Многие делают это вручную, что неудобно — особенно, когда это нужно делать часто и в больших таблицах.

Что делать? Автоматизировать с помощью дополнительной функции «Столбец». Она принимает в качестве аргумента адрес ячейки, а возвращает номер столбца этой ячейки.

То есть функция «СТОЛБЕЦ» приняла в качестве аргумента адрес ячейки А1, а вернула ячейке значение <1>. Столбец А идет первым, поэтому ему соответствует число 1. Столбец В идет вторым, поэтому ему соответствует число 2. Столбец С идет третьим, поэтому ему соответствует число 3 и т. д.

Вернемся к нашей формуле ВПР и посмотрим, как она будет выглядеть в новом виде.

Число 4 мы заменили соответствующей этому числу функцией "СТОЛБЕЦ(D1)«.Если теперь протягивать формулу вправо, то аргумент D1 автоматически сменится на Е1, поскольку мы сдвинулись вправо на 1 ячейку, и т. д.

В общем, в формуле ВПР удобнее не писать числом аргумент <номер_столбца>, а использовать функцию «СТОЛБЕЦ» — с указанием столбца, соответствующего этому числу.

Это не единственный способ заполнить формулой ВПР все столбцы новой таблицы, но, пожалуй, самый простой.

Преобразование содержимого ячейки в число

Часто бывает, что Excel не воспринимает число как число, несмотря на смену формата. Тогда нужно заходить в каждую ячейку и нажимать Enter. Только после этих действий нужный формат становится доступен. Аналогичная проблема бывает и с датами.

Объясню на примере ситуации. Excel часто не воспринимает число как число в отчетах из Roistat или Метрики. А вам, допустим, нужно отсортировать данные, создать сводные таблицы, применить вычислительные формулы в Excel. Тогда приходится исправлять некорректные значения.

В чем сложность. Таких чисел, которые Excel воспринимает некорректно, может быть сотни, тысячи или даже десятки и сотни тысяч. Вручную такой объем не поправить.

Что делать? Использовать несложные математические вычисления, которые превратят содержимое ячейки в число. Рассмотрим абстрактный пример, где Excel читает числа как текст.

В таком случае нужно создать дополнительный столбец, в котором мы и произведем математические операции с нужными ячейками:

1. Умножьте в дополнительном столбце числа на 1 — <*1>. Можно также использовать действие <+0> — прибавление нуля отлично подходит для ячеек с датами.

2. Протяните формулу вниз и вправо, чтобы каждое число из массива умножилось на 1. Так вы получите новый массив с данными, которые Excel уже безошибочно воспринимает как числа.

3. Присвойте полученным значениям любой формат — числовой, финансовый, процентный и пр.

Теперь рассмотрим, как работать с датами. Для них отлично подходит прибавление 0:

1. Примените формулу +0.

2. Нажмите Enter и получите число.

   На первый взгляд кажется, что что-то не получилось. Это не так — теперь число легко превращается в дату любого типа.

3. Смените формат даты.

4. Протяните формулу на весь массив. В ней автоматически будет применяться выбранный тип даты.

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

На сегодня всё! Лёгких вам задач и высокого KPI! А если остались вопросы, пишите их в комментариях 😉