Базовые функции Excel для работы
SEO-специалисту важно уметь работать с данными, производить их сортировку, фильтрацию, преобразование. Для многих функций удобным оказывается стандартный Excel. В данной статье мы рассмотрим самые основные функции Excel, освоить которые должен каждый специалист для продуктивной работы.
Исходный файл (скачать CSV-файл, 1.5 КБ)
В качестве исходных данных рассмотрим файл типа «Распределение» в котором собраны продвигаемые поисковые запросы с указанием (Рис. 1):
Далее, рассмотрим, как можно быстро решить самые типовые задачи.
Сортировка по любому полю
Для этой операции будет достаточно преобразовать рабочую область таблицу с заголовками (Рис. 2). После чего будет доступна сортировка по любому из полей (Рис. 3) при нажатии на квадратик со стрелочкой справа от названия колонки.
Рис. 2. Вставка таблицы с заголовками в Excel файл для дальнейшей работы.
Рис. 3. Сортировка текстовых полей от «А до Я» и от «Я до А» в таблице в Excel. Для численных полей доступна сортировка от минимального к максимальному значению и наоборот.
Выделение дублей или уникальных значений
Рис. 4. Создание нового правила для условного форматирования выделенной области.
После, выбираете «Форматировать только уникальные или повторяющие значения», задаете тип, на примере это «Повторяющиеся» и Формат, на примере это оранжевый цвет (Рис. 5).
Рис. 5. Задание оранжевого цвета для форматирования повторяющихся значений в выделенной области.
Удаление повторяющихся значений
После применения правила повторяющиеся значения в выделенной области будут подсвечены оранжевым цветом (Рис. 6). По данному цвету можно осуществить сортировку в таблице и проработать или удалить данные строчки.
Рис. 6. Удаление повторяющегося ключевого запроса после сортировки по оранжевому цвету в таблице.
Выделение цветов значений в диапазоне
Рис. 7. Задание форматирования зеленых цветом для ячеек между 1 и 10 через функцию условного форматирования.
Далее, ячейки в заданном диапазоне будут выделены нужным цветом (зеленым), что упрощаем визуальное восприятие таблицы (Рис. 8).
Рис. 8. Пример выделения в таблице нужных ячеек с позициями в ТОП-10 зеленым цветом.
Поиск запросов с заданным словом
Часто, требуется быстро найти и выделить все запросы, в которых содержится заданное слово, скажем, слово «сайт». Для этого аналогично можно использовать функцию условного форматирования с заданием формата для ячеек, которые содержат текст «сайт» (Рис. 9).
Рис. 9. Пример быстрого поиска и работы с поисковыми запросами, в которых содержится слово «сайт».
Расчет значения по формуле
В таблице также удобным оказывается производить расчёт какого-либо показателя по формуле, опираясь на значения в других показателей. В частности, можно вычислить прогнозируемый бюджет как среднее значение между бюджетом из системы SeoPult и MegaIndex (Рис. 10). Для этого достаточно задать формулу для первой ячейки таблицы и значение вычиститься для всей таблицы.
Рис. 10. Расчёт ссылочного бюджета, в таблице Excel опираясь на значения от агрегаторов SeoPult и MegaIndex.
Копирование значений из колонки, вычисленной по формуле
- Выделяете значения, которые вам требуется скопировать мышкой.
- Нажимаете CTRL+C.
- Далее выбираете ячейку, начиная с которой вы планируете осуществить вставку.
- Нажимаете правку кнопку мышки.
- Выбираете «Специальная вставка».
- Задаете «Вставить значения».
Рис. 11. Функция специальной вставки в Excel для копирования и вставки именно числовых значений, а не исходной формулы, по которой они были вычислены.
В данном случае, скопированы будут именно значения из ячейки, а не формула, по которой они были вычислены.
Сравнение значений в двух столбцах
Рис. 12. Вызов функции логического «ЕСЛИ» в Excel для сравнения значений в двух столбцах.
После нажатия на кнопку «OK» столбец заполнится значениями «0» (если страницы не совпадают) и «1», если значения совпадают. Это позволит быстро найти все запросы, по которым релевантный и продвигаемый документ не совпадают, и начать анализ возможных причин данного поведения.
Использование формул: среднее значение и сумма значений в ячейках
Для вычисления среднего значения какого-либо параметра (скажем, средней позиции в Яндексе по всем запросам или средней частоты запросов), а также суммы значений (скажем, суммарная точная частота или суммарный бюджет на ссылки) требуется использовать математические функции. Наиболее популярные это: вычисление среднего, вычисление медианы, вычисление суммы значений в столбце.
На Рис. 13 показана последовательность действий для вставки функции. Сначала требуется выделить ячейку, в которую требуется вывести итоговое вычисленное значение, далее выбрать интересующую вас функцию и диапазон значений, над которым планируется производить вычисления.
Рис. 13. Выбор ячейки и вставка нужной математической функции ячейку.
После поиска нужной функции, требуется задать аргументы (значения с которыми будет работать функция) и нажать «OK». Если вы всё сделали верно, то значение будет вычислено и вставлено автоматически. Примеры вставки функций среднего значения (Рис. 14) и суммы значений (Рис. 15) представлены на иллюстрациях ниже.
Рис. 14. Вставка функции вычисления среднего значения ячеек для колонки «ЯНДЕКС».
Рис. 15. Вставка математической функции «Автосумма» для быстрого вычисления суммы значений в колонке.
В арсенале Excel (Эксель) много различных функций, которые могут пригодиться SEO-специалисту, вы можете осуществить поиск по ним вводя в строку поиска по функциям первые буквы искомой операции. Среди полезных, также могут оказаться такие функции как:
- Поиск максимального и минимального значения в колонке.
- Использование логических операторов: «И», «ИЛИ», «ЕСЛИ», «НЕ».
- Работы с датой и временем, вывод текущей даты по календарю.
- Сумма, сумма значений с условием, медиана.
Задание формата ячеек
Для задания требуемого формата ячеек (числового, денежного, финансового, временного, процентного, текстового и т.д.) достаточно использовать функцию «Формат ячеек», предварительно выделив интересующую область форматирования и нажав правую кнопку мыши (Рис. 16), во всплывающем модальном окне нажать «Формат ячеек…».
Рис. 16. Пример вызова функции «Форма ячеек» для выделенной области.
Рис. 17. Задание двух различных форматов (числовой и процентный) для двух соседних колонок.
Фиксация положения одной из ячеек в формуле
Если вам требуется зафиксировать положение (ячейку) для одной из переменных в формуле, то требуется просто заменить в самой формуле значение вида =F2 на значение =$F$2 (вставить знак доллара). После чего, вы сможете «протягивать» формулы для всей строки или столбца с фиксацией одной из переменный (ячеек). Пример использования:
* Стоит отметить, что функция условного форматирования быстро работает только с небольшими и средними таблицами и плохо справляется с большими массивами данных.
Как создать пользовательскую функцию в Excel? | Mister-Office
- Выделяете значения, которые вам требуется скопировать мышкой.
- Нажимаете CTRL+C.
- Далее выбираете ячейку, начиная с которой вы планируете осуществить вставку.
- Нажимаете правку кнопку мышки.
- Выбираете «Специальная вставка».
- Задаете «Вставить значения».
Если в диапазон ячеек входит только один столбец, достаточно будет указать только номер строки. Когда в выбранном диапазоне получается два столбца и больше, можно будет выбрать его номер, чтобы точно обозначить ячейку.
