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

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

Формулы в Excel для чайников

Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.

В Excel применяются стандартные математические операторы:

Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.

Программу Excel можно использовать как калькулятор. То есть вводить в формулу числа и операторы математических вычислений и сразу получать результат.

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

При изменении значений в ячейках формула автоматически пересчитывает результат.

Ссылки можно комбинировать в рамках одной формулы с простыми числами.

Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.

В нашем примере:

1. Поставили курсор в ячейку В3 и ввели =.
2. Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
3. Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.

Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности:

• %, ^;
• *, /;
• +, -.

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



Как в формуле Excel обозначить постоянную ячейку

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

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

1. Вручную заполним первые графы учебной таблицы. У нас – такой вариант:



2. Вспомним из математики: чтобы найти стоимость нескольких единиц товара, нужно цену за 1 единицу умножить на количество. Для вычисления стоимости введем формулу в ячейку D2: = цена за единицу * количество. Константы формулы – ссылки на ячейки с соответствующими значениями.



3. Нажимаем ВВОД – программа отображает значение умножения. Те же манипуляции необходимо произвести для всех ячеек. Как в Excel задать формулу для столбца: копируем формулу из первой ячейки в другие строки. Относительные ссылки – в помощь.

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

Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.

Ссылки в ячейке соотнесены со строкой.

Формула с абсолютной ссылкой ссылается на одну и ту же ячейку. То есть при автозаполнении или копировании константа остается неизменной (или постоянной).

Чтобы указать Excel на абсолютную ссылку, пользователю необходимо поставить знак доллара ($). Проще всего это сделать с помощью клавиши F4.

1. Создадим строку «Итого». Найдем общую стоимость всех товаров. Выделяем числовые значения столбца «Стоимость» плюс еще одну ячейку. Это диапазон D2:D9
2. Воспользуемся функцией автозаполнения. Кнопка находится на вкладке «Главная» в группе инструментов «Редактирование».
3. После нажатия на значок «Сумма» (или комбинации клавиш ALT+»=») слаживаются выделенные числа и отображается результат в пустой ячейке.

Сделаем еще один столбец, где рассчитаем долю каждого товара в общей стоимости. Для этого нужно:

1. Разделить стоимость одного товара на стоимость всех товаров и результат умножить на 100. Ссылка на ячейку со значением общей стоимости должна быть абсолютной, чтобы при копировании она оставалась неизменной.
2. Чтобы получить проценты в Excel, не обязательно умножать частное на 100. Выделяем ячейку с результатом и нажимаем «Процентный формат». Или нажимаем комбинацию горячих клавиш: CTRL+SHIFT+5
3. Копируем формулу на весь столбец: меняется только первое значение в формуле (относительная ссылка). Второе (абсолютная ссылка) остается прежним. Проверим правильность вычислений – найдем итог. 100%. Все правильно.

При создании формул используются следующие форматы абсолютных ссылок:

• $В$2 – при копировании остаются постоянными столбец и строка;
• B$2 – при копировании неизменна строка;
• $B2 – столбец не изменяется.

Как составить таблицу в Excel с формулами

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

Простейшие формулы заполнения таблиц в Excel:

1. Перед наименованиями товаров вставим еще один столбец. Выделяем любую ячейку в первой графе, щелкаем правой кнопкой мыши. Нажимаем «Вставить». Или жмем сначала комбинацию клавиш: CTRL+ПРОБЕЛ, чтобы выделить весь столбец листа. А потом комбинация: CTRL+SHIFT+»=», чтобы вставить столбец.
2. Назовем новую графу «№ п/п». Вводим в первую ячейку «1», во вторую – «2». Выделяем первые две ячейки – «цепляем» левой кнопкой мыши маркер автозаполнения – тянем вниз.
3. По такому же принципу можно заполнить, например, даты. Если промежутки между ними одинаковые – день, месяц, год. Введем в первую ячейку «окт.15», во вторую – «ноя.15». Выделим первые две ячейки и «протянем» за маркер вниз.
4. Найдем среднюю цену товаров. Выделяем столбец с ценами + еще одну ячейку. Открываем меню кнопки «Сумма» — выбираем формулу для автоматического расчета среднего значения.

Чтобы проверить правильность вставленной формулы, дважды щелкните по ячейке с результатом.

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

Наиболее распространенная функция, которую используют большинство пользователей Microsoft Excel, это функция СУММ. Неудивительно, что Microsoft Excel решили добавить специальную кнопку на ленту Excel, которая автоматически вставляет функцию СУММ.

По сути, Автосумма в Excel автоматически вводит формулу для суммирования чисел на вашем листе. В данной статье мы рассмотрим следующую информацию:

1. Где находится Автосумма в Excel
2. Как сделать автосумму в Excel
3. Автосумма в Excel не считает
4. Автосумма в Excel горячие клавиши
5. Как использовать Автосумму с другими функциями
6. Как использовать Автосумму в Excel только для видимых ячеек
7. Как использовать Автосумму для более чем одной ячейки за раз
8. Как суммировать выделенные ячейки по вертикали и по горизонтали

Где находится Автосумма в Excel

Кнопку Автосумма можно найти в двух местах на ленте Excel.

1. Вкладка «ГЛАВНАЯ» —> группа «Редактирование» —> «Автосумма»:

Автосумма в Excel – Кнопка Автосуммы в Excel

1. Вкладка «ФОРМУЛЫ» —> группа «Библиотека функций» —> «Автосумма»:

Автосумма в Excel – Кнопка Автосумма во вкладке Формулы

Теперь где Автосумма в Excel вы знаете.

Как сделать автосумму в Excel

Всякий раз, когда вам нужно посчитать автосумму одного диапазона ячеек, будь то столбец, строка или нескольких соседних столбцов или строк, вы можете использовать формулу в Excel Автосумма для автоматического создания соответствующей формулы СУММ.

Чтобы использовать Автосумму в Excel, выполните следующие 3 простых действия:

1. Выберите ячейку рядом с цифрами, которые вы хотите просуммировать:

• Чтобы суммировать столбец, выберите ячейку сразу под последним значением в столбце.
• Чтобы суммировать строку, выберите ячейку справа от последнего числа в строке.

Автосумма в Excel – Выбор ячейки, после который вы хотите посчитать сумму

1. Нажмите кнопку Автосумма на вкладке «ГЛАВНАЯ» или «ФОРМУЛЫ».

В выбранной ячейке появляется формула суммы, а диапазон суммируемых ячеек выделяется (B2:B6 в этом примере):

Автосумма в Excel – Кликните на кнопке Автосумма на вкладке Главная и Формулы (в данном случае на вкладке Формула)

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

Чтобы суммировать несколько столбцов или строк за раз, выберите несколько ячеек внизу или справа от вашей таблицы, соответственно, а затем нажмите кнопку Автосумма или используйте горячие клавиши автосуммы в Excel. Для получения дополнительной информации см. пункт Как использовать Автосумму для более чем одной ячейки за раз.

1. Нажмите клавишу Enter, чтобы завершить формулу.

Теперь вы можете увидеть вычисленную сумму в ячейке и формулу СУММ в строке формул:

Автосумма в Excel – Кликните клавишу Enter, чтобы завершить формулу СУММ

Разберем причины, по которым автосумма в Excel выдает 0.

Автосумма в Excel не считает

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

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

Автосумма в Excel – Преобразование числа в текстовом формате, в числовой

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

Автосумма в Excel горячие клавиши

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

Автосумма в Excel – Автосумма в Excel горячие клавиши

При удерживании клавиши Alt, и нажатии знака равенства (=), в выбранной ячейке (ячейках) вставляется формула СУММ точно так же, как при нажатии кнопки Автосумма на ленте, и затем вы нажимаете клавишу Enter для ввода формулы.

Как использовать Автосумму с другими функциями

Помимо суммирования ячеек, вы можете использовать кнопку Автосумма для вставки других функций, таких как:

СРЕДНЕЕ — возвращает среднее (среднее арифметическое) чисел.

СЧЕТЧИК — подсчитывает количество ячеек с числами.

МАКСИМУМ — возвращает наибольшее значение.

МИНИМУМ — возвращает наименьшее значение.

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

Например, именно так вы можете получить наибольшее число в столбце B:

Автосумма в Excel – Использование кнопки Автосумма для вставки других формул (в данном случае Максимум)

Если вы выберете «Другие функции» из раскрывающегося списка «Автосумма», Microsoft Excel откроет диалоговое окно «Вставить функцию», где вы можете найти необходимую формулу в списке или воспользоваться поиском.

Как использовать Автосумму в Excel только для видимых ячеек

Вы уже знаете, как использовать Автосумму в Excel для общего столбца или строки. Но знаете ли вы, что вы можете использовать эту функцию для суммирования только видимых ячеек, например, в отфильтрованном списке?

Если ваши данные организованы в таблице Excel (что можно легко сделать, нажав сочетание клавиш Ctrl+T), и вы отфильтровали свои данные, применив один из параметров фильтрации, нажатие кнопки Автосуммы вставляет формулу ПРОМЕЖУТОЧНЫЕ.ИТОГИ, а не СУММ, как показано на следующем изображении:

Автосумма в Excel – Использование Автосуммы для видимых ячеек

В исходной таблице были скрыты 3, 6, 9 и 12 строка. В данном случае, использование Автосуммы и функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ, были просуммированы только видимые ячейки.

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

Как использовать Автосумму для более чем одной ячейки за раз

Если вы хотите суммировать значения в нескольких столбцах или строках, выберите все ячейки, в которые вы хотите вставить формулу СУММ, а затем нажмите кнопку Автосумма на ленте или нажмите сочетание горячих клавиш Автосуммы в Excel.

Например, вы можете выбрать диапазон ячеек B7:D7, щелкнуть Автосумма, и как показано на следующем изображении, значения в каждом из трех столбцов суммируются по отдельности:

Автосумма в Excel – Автосумма нескольких столбцов одновременно

Как суммировать выделенные ячейки по вертикали и по горизонтали

Чтобы суммировать только определенные ячейки в столбце, выделите эти ячейки и нажмите кнопку Автосумма. Если вы хотите посчитать сумму выбранных ячеек по столбцу, то выделите ячейки и нажмите Автосумма.

Автосумма в Excel – Сумма выбранных ячеек вертикально по столбцам

Если вы хотите суммировать ячейки по строкам, выберите ячейки, которые вы хотите суммировать, и один пустой столбец справа. Excel будет суммировать выделенные ячейки по горизонтали и вставит формулу СУММ в пустой столбец, включенный в выделение:

Автосумма в Excel – Сумма выбранных ячеек горизонтально по строкам

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

Автосумма в Excel – Суммирование выбранных ячеек столбцам и строкам

В статье показано, как выполнять математические расчеты в Excel и изменять порядок операций в ваших формулах расчета.

Как сделать расчеты в Excel

Произвести расчеты в Excel легко. Вот как:

• Введите символ равенства (=) в ячейку. Это говорит Excel, что вы вводите формулу, а не только цифры.
• Введите уравнение, которое вы хотите посчитать. Например, чтобы посчитать сумму 5 и 7, введите =5+7
• Нажмите клавишу Enter, чтобы завершить расчет. Готово!

Вместо того, чтобы вводить цифры непосредственно в формулу расчета, вы можете поместить их в отдельные ячейки, а затем сослаться на эти ячейки в своей формуле, например, =A1+A2+A3

В следующей таблице показано, как выполнять основные арифметические расчеты в Excel.

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

Расчеты в Excel – Формулы расчетов в Excel

Кроме того, вы можете комбинировать значения из двух или более ячеек в одной ячейке, используя оператор конкатенации (&) следующим образом:

=A2&» «&B2&» «&C2

Символ пробела (» «) используется для разделения слов:

Расчеты в Excel – Объединение значений из нескольких ячеек

Расчеты в Excel – Сравнение ячеек с использованием логических операторов

Порядок выполнения расчетов в Excel

Когда вы выполняете два или более расчета в одной формуле, Microsoft Excel вычисляет формулу слева направо в соответствии с порядком операций, показанных в этой таблице:

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

Как изменить порядок расчета в Excel

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

Например, расчет =2*4+7 сообщает Excel умножить 2 на 4, а затем добавить 7 к значению. Результат этого вычисления равен 15. Заключив операцию сложения в скобках =2*(4+7), вы даете команду Excel сначала просуммировать 4 и 7, а затем умножить сумму на 2. И результатом этого расчета является значение 22.

Как вы можете видеть на изображении ниже, тот же расчет с круглыми скобками и без них дает разные результаты:

Расчеты в Excel – Изменение порядка расчетов в Excel

Вот так происходят расчеты в Excel.

Мы перевели для вас статью Нила Пателя. Читайте и применяйте.

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

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

Большинство маркетологов-профессионалов знают нужные функции как свои пять пальцев, но все как один скажут вам, что это всего лишь вопрос времени.

Однажды овладев этими навыками, вам больше не придется возвращаться к этому вновь и вновь.

Зачем учиться пользоваться Excel для SEO

Если вы до сих пор не пользуетесь никаким ПО с таблицами, то пора бы начать. Упорядочивание метрики – одна из самых важных частей разработки маркетинговой стратегии, будь то план, еженедельный SEO отчет или годовой доклад.

Таблицы помогают организовывать все данные в одном месте,где они всегда под рукой. А когда речь идет о чем-то вроде SEO, дисциплина и организация – это основа вашего успеха, особенно если у вас сотни или даже тысячи ключевых слов.

Причина, по которой Excel является такой потрясающей программой для SEO, – это наличие расширений и плагинов, созданных специально для того, чтобы сделать процесс пользования максимально простым.

Например, SEOTools для Excel идет с уже встроенными функциями специально для маркетологов.

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

Но кроме специальных утилит, Excel имеет множество формул, помогающих с курированием ключевых слов, сегментацией списков и анализом данных. А ведь все это неотъемлемые части процесса поисковой оптимизации.

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

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

7 способов применения встроенных функций Excel, которые непременно выведут вас на новый уровень пользования поисковой оптимизацией

Используйте формулу «=IF» для разбивки ключевых слов по категориям

Находясь в поиске ключевых слов, учишься спокойно относиться к длинным спискам. Привыкаешь к ним, так сказать. Даже пользуясь простой утилитой типа Google’s Keyword Planner, все равно получишь длинный список.

Другие утилиты вроде Ahrefs или Moz могут выдать вам списки с тысячами ключевых слов, показателей объема и данными о конкуренции. А это очень, очень много данных. Поместив эти данные в таблицу, мы сможете организовать их по рядам и колонкам. Но ведь нужно еще и как-то осмыслить все это.

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

Например, если использовать более одного семантического ядра в Keyword Planner,

то вам выдают колонку «Ad Group», куда можно экспортировать список запросов, которые отделят семантические ядра от остальных вариантов ключевых слов.

Предположим, я хочу разбить этот список только на ключевые запросы со словами «утилиты» в них. Если делать это вручную, придется продираться сквозь более чем 700 рядов. А мне не очень-то хочется это делать. Так что вместо этого я собираюсь использовать формулу Excel =IF (=ЕСЛИ), которая сделает все за меня.

Первым делом нужно удалить колонку «Ad Group», потому что я хочу искать среди всех запросов.

А потом я добавлю колонку «Category» («Категория”) рядом с поисковым объёмом.

В первой свободной клетке я пишу слово «Tools» и затем использую формулу:

=if(isnumber(search(«tools,A12)),”Tools”)

Когда ключевое слово «tools» найдется в первой колонке (мой список ключевых слов), то поместится в категорию «Tools».

Можно пользоваться этой формулой в таблицах, чтобы поместить все ключевые слова в определенные категории. Это переломный момент в игре под названием «организация поискового процесса”.

Если хотите только ключевые запросы со словом «local», можно создать категорию «Local». Удивительно нужная штука для организации всех ваших ключевых запросов для быстрого поиска.

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

У Moz есть хороший пример того, как можно сделать это для всех данных во всех таблицах.

Чтобы сделать это для всех рядов и колонок, используйте мульти-клеточную формулу ARRAY (МАССИВ).

Это освободит вас от необходимости вставлять одну и ту же сложную формулу в каждую клетку таблицы. И даже если у вас не 700+ клеток, с которыми нужно иметь дело, формула все равно пригодится.

Создавайте сводные таблицы для обнаружения выброса данных

Анализ данных в таблицах может быть довольно хаотичным, если данные не организованы.

Даже если вы фильтруете по категориям, не факт, что «неверные данные» и прочие лазутчики обнаружатся при одном взгляде на таблицу. Хорошие новости – у Excel есть легкий способ выявить положительные и отрицательные тренды в данных при помощи сводных таблиц.

Чтобы создать такую, выделите клетку таблицы и нажмите Insert > Pivot Table (Вставить > Сводная Таблица).

Затем вы увидите диалоговое окно, в котором вас попросят выбрать данные для анализа и куда поместить сводную таблицу.

Ваша таблица будет автоматически помещена в выделенную клетку, но также можно создать новую таблицу или поместить ее куда-то еще.

Когда нажмете «OK», то увидите поле, соответствующее вашим колонкам.

Можно перетаскивать поля, куда вам нужно, отфильтровать их, добавлять колонки и т.д. Полная кастомизация, и можно также добавлять или удалять элементы по необходимости.

Используйте эти таблицы для таких вещей как организация ссылок или URL, группировка внутренних ссылок по DA/PA, поиск ключевых слов в домене или создание всплывающих окон для определенных колонок.

Вот пример функциональной сводной таблицы:

Такая таблица не просто организует ваши данные в более читабельном виде, но и дает несколько опций для категоризации. В примере выше можно сразу заметить большие или маленькие цифровые значения CPA («cost-per-action», цена-за-действие) для определенных ключевых слов.

Это делается при помощи условного форматирования в рамках сводной таблицы (вы можете заметить цветное кодирование). Сводные таблицы типа этой могут оказаться полезными, если вы используете ключевые слова для транслирования своей стратегии Adwords, или если хотите быстро обнаружить высоко- и низкочастотные запросы и CPC («cost-per-click», цена-за-клик) для конкретных ключевых слов.

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

И выглядит такая таблица тоже неплохо.

Конвертируйте показатели объема при помощи SUBSTITUTE (ЗАМЕНИТЬ)

Существует множество разных способов сортировки данных в Excel. Тем не менее, ключ к продуктивности заключается в том, чтобы найти верный. Когда вы загружаете данные из утилиты для ключевых слов (типа Keyword Planner), CSV-файлы не всегда выглядят хорошо. Например, мои объемы ключевых слов выглядят так:

Справедливости ради, они выглядят так же при использовании Keyword Planner.

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

Excel не может отсортировать мой список, основываясь на понятиях типа «10К» или «1М». Он не дружит с К и М. Ему нужны реальные цифры. К счастью, Excel имеет нечто вроде быстрой формулы, которая поможет вам отсортировать объемы должным образом.

Во-первых, замените К и М и преобразуйте их в «000» или «000000». Создайте новую колонку с названием «От меньшего к большему» или типа того.

Выберите клетку в новой колонке и вставьте =SUBSTITUTE (=ЗАМЕНИТЬ):

Ваша формула должна выглядеть примерно так:

=SUBSTITUTE(C2,”K”,”000”)

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

Вот как это выглядит после вставки формулы:

И окончательные результаты:

Можно также заменить М на «000000» при помощи той же формулы. Выглядеть будет вот так:

=SUBSTITUTE(C2,”M”,”000000”)

Так вы разберетесь со всеми К и М в таблице.

А еще можно выполнить оба действия одновременно (если у вас диапазон от 100К до 1М, например), используя следующую формулу (изменяя номер клетки):

=SUBSTITUTE(SUBSTITUTE(C2,”K”,”000”), «M”, «000000”)

Есть еще несколько формул, чтобы найти минимум, максимум и среднее значение поисковых объемов в новой колонке.

Формула для минимума:

Максимума:

И среднего показателя:

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

Вы сможете быстро переключаться между колонками «от большего к меньшему» и «от меньшего к большему» без необходимости долго сортировать или переставлять вручную.

Эти формулы призваны экономить ваше время и энергию, в конце концов.

Извлекайте определенные данные при помощи «REGEXTRACT» («ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ ТАБЛИЦЫ”)

Конечно, ключевые слова – это не только данные в таблицах SEO.

Иногда вам придется сортировать URL, названия доменов, заголовки блогов или email адреса.

Возможно, вам потребуется найти конкретные посты в блоге или лэндинговые страницы с HTTP versus или HTTPS.

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

Вместо этого, примените REGEXTRACT (ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ), чтобы извлечь конкретные данные из списка.

REGEXTRACT не похожа на другие Excel формулы. Она требует специального синтакса (строка кода) для работы.

Полная формула с синтаксом для REGEXTRACT выглядит так:

=REGEXEXTRACT(A2,”^(?:https?:\/\/)?(?:+@)?(?:www\.)?(+)”)

Смешной кусочек в конце и есть синтакс.

Можно копировать и вставить полную формулу в новую колонку (изменяя номер колонки), и в результате вы получите вот что:

Можно воспользоваться формулой ARRAY (МАССИВ), чтобы добавить это в несколько рядов сразу.

Все вместе выглядит так:

=IFERROR(ARRAYFORMULA(REGEXEXTRACT(A2:A,”^(?:https?:\/\/)?(?:+@)?(?:www\.)?(+)”)),»”)

Громоздко, но работает.

Вот что получаем в нашем примере:

И в конечном итоге:

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

Если вы все еще не уверены в процессе или в том, как использовать синтаксис вместе с REGEXTRACT, у Ahrefs есть таблица, которая показывает, как работает эта формула.

Это просто палочка-выручалочка для тех, кто не испытывает особой нежности к Excel формулам.

Форматируйте теги названий с помощью формулы «PROPER» («ПРОПНАЧ”)

Еще одно утомительное занятие, с которым вы можете столкнуться при работе с таблицами для SEO, это переформатирование данных.

Возможно, теги заголовков или ключевые слова были импортированы в нижнем регистре, а вам надо, чтобы они были записаны заглавными буквами.

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

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

=PROPER(C2)

Просто, не так ли?

Создайте новую колонку и вставьте формулу туда. Вот так:

И конечный результат:

Если у вас много тегов, которые нужно записать заглавными буквами, это тоже сработает.

Можно искать отдельные теги, используя формулу HLOOKUP (ГПР) (вернемся к ней позже).

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

Если хотите изменить ключевое слово или тэн названия в нижнем регистре на верхний (например, акроним), воспользуйтесь формулой UPPER (ПРОПИСН). Вот так:

Вы можете мне не верить, но формула LOWER (СТРОЧН) совершит обратное действие 🙂

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

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

Так что вот вам быстрый и легкий способ решить этот вопрос.

Совершайте поиск в больших таблицах с помощью «VLOOKUP» («ВПР”)

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

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

Как я уже упоминал ранее, поисковые функции Excel ограничены, если ваши данные уже не организованы каким-то образом.

И тем тяжелее, чем больше у вас рядов и колонок с данными. Есть у Excel формула и для этого, куда ж без нее.

Это VLOOKUP (ВПР), которая выглядит так:

=VLOOKUP(A4;’Lookup Table’!A2:D1000;4;FALSE)

Если вы хотите найти специальное ключевое слово среди двух листов одной таблицы, то процесс таков:

Эта формула может показаться сложной на первый взгляд, но становится проще, когда вы ее освоите.

Первая секция – это название того, что вы ищете. Поставьте его в кавычки:

Затем добавьте наименование колонки.

После этого добавьте порядковый номер колонки. Первая колонка – под цифрой 1 и так далее.

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

Можно также использовать FALSE, если точное совпадение необязательно.

Обратите внимание на то, что VLOOKUP (ВПР) работает только если необходимые вам данные находятся в первой колонке (крайняя левая), а данные расположены в алфавитном порядке.

Так что вам возможно придётся форматировать колонки перед поиском VLOOKUP (ВПР). Но формула все равно очень пригодится.

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

Если нужно искать по рядам, используйте формулу HLOOKUP (ГПР).

V (В) для вертикального поиска и H (Г) для горизонтального.

Пример формулы HLOOKUP:

=HLOOKUP(D3;’Lookup Table’!A1:D10;2;FALSE)

Тут вы найдете пример этой функции:

Оба пути быстро приведут вас к тому, что вы ищете, особенно если найти нужно среди множества рядов, колонок или страниц в документе.

Также обе эти формулы можно использовать между двумя разными документами и искать в сворачивающихся окошках и сводных таблицах.

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

Немного тренировки, и вы освоите их на «отлично». Именно поэтому они во многом превосходят традиционное поисковое окно.

Быстро обнаруживайте дубликаты, используя «COUNTIF»(«СЧЁТЕСЛИ”)

Не все данные в таблице будут верными.

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

Формула Excel в данном случае – COUNTIF. И выглядит она так:

=COUNTIF(A:A,A2)

Допустим, у вас есть список элементов в первой колонке (колонка А), которые вы хотите проверить на дубликаты.

Во-первых, создайте новую колонку и вставьте формулу с номером клетки из колонки А, вот так:

Затем перетащите ее и скопируйте в другие клетки. После этого увидите вот что:

TRUE означает, что совпадения есть, а FALSE – что стопроцентных дубликатов нет. Эта формула находит только точные совпадения.

В примере выше вы видите, что даже если слова «seo strategy» показаны несколько раз (например, seo marketing strategy), то формула не сочтет это дубликатом.

Если вы просто хотите выделить эти дубликаты, можно сделать это при помощи условного форматирования.

Нажмите Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values

Можно выбрать цвет для дубликатов, если хотите просто выделить их, а не удалить.

Если хотите их удалить, нажмите Data > Remove Duplicates (Данные > Удалить Дубликаты), а затем под колонками выделите или снимите выделение с тех, в которых хотите удалить дубликаты.

Весь процесс занимает пару минут, но при этом действительно помогает почистить данные быстро.

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

И формула COUNTIF (СЧЁТЕСЛИ) – одна из них.

Заключение

Многие люди не любят пользоваться таблицами или разбираться с формулами. Я это понимаю. Некоторые формулы слишком сложны и громоздки, чтобы их запомнить (вспомним REGEXTRACT).

Но если вы используете таблицы – особенно Excel таблицы – для SEO или маркетингового исследования, то вам просто необходимо овладеть некоторыми из них.

В особенности, присмотритесь к тем, которые помогают при поиске или очистке данных и делают это быстро, как VLOOKUP (ВПР) или COUNTIF (СЧЁТТЕСЛИ).

Умение конвертировать показатели объема с помощью SUBSTITUTE (ЗАМЕНИТЬ) просто жизненно необходимо для поиска ключевых слов.

А научившись создавать более сложные (но нужные) элементы типа сводных таблиц, вы в итоге сэкономите себе кучу времени и энергии. SEO и так непростая штука.

Сбросьте с себя часть бремени, изучив несколько приемов пользования Excel.

Если вы хотите научиться оптимизировать сайты и стать суперменом-сеошником, то можем предложить курс «SEO-оптимизация: продвижение сайтов в поисковых системах». После курсов проведете аудит сайта и создадите стратегию продвижения. Научитесь анализировать конкурентов, сформируете семантическое ядро. Прогнозируя результаты продвижения, сможете оптимизировать бюджет. Привлекательно? Записывайтесь!