Функция сцепить на английском

Функция сцепить на английском

В этой заметке рассмотрим, как склеить содержимое из нескольких ячеек, чтобы все оказалось в одной ячейке. Это распространенная задача и она довольно легко решается с помощью функции Excel СЦЕПИТЬ. Эта функция умеет не только соединять ячейки, но и добавлять любые другие символы. В строке формул выглядит примерно так.

Здесь между ячейками вставлены пробелы (» «) и в конце дорисовывается восклицательный знак.

Однако эти самые пробелы являются первым неприятным моментом при использовании функции СЦЕПИТЬ, т.к. функция склеивает ячейки без «шва» и пробелы приходится добавлять вручную. Если нужно соединить только несколько слов, то беда не большая, но когда нужно склеить хотя бы с десяток ячеек, то ручная добавка пробелов слегка утомляет. Чтобы немного облегчить процесс, можно посоветовать вставить пробел в отдельную ячейку и затем просто делать на нее ссылку.

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

Кстати, не все знают, что в Excel для склеивания ячеек также предусмотрен специальный оператор – & (амперсанд). Его нужно просто поставить между связываемыми ячейками или символами.

Вставлять амперсанд не удобно – нужно вначале переключиться на английскую раскладку, а затем нажать Shift + 7. Поэтому рекомендуется воспользоваться специальной комбинацией – удерживая клавишу Alt нажать 3 и 8 на цифровой клавиатуре. Здорово помогает сохранить душевное равновесие.

Формула СЦЕПИТЬ и амперсанд исправно работают до тех пор, пока не приходится соединять большое количество ячеек. Замучаешься вставлять «;» либо «&». Более того, между ячейками, как привило, добавляется пробел. К сожалению, указать в качестве аргумента целый диапазон нельзя (уже можно). Выход из этой ситуации следующий.

Функция СЦЕПИТЬ для большого количества ячеек

Один из вариантов заключатся в том, чтобы в качестве ссылки на ячейки указать массив данных. Следует сразу отметить, что данные могут располагаться по горизонтали или вертикали. Если данные расположены в одной строке, то делаем следующее. Для примера, отдельные по ячейкам слова находится в 5-й строке. Теперь в пустой ячейке указываем весь диапазон для соединения и через амперсанд (&) добавляем пробел (» «).

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

Как видно к каждому слову добавился пробел, а между словами стоит точка с запятой – как раз то, что нужно для вставки в формулу СЦЕПИТЬ. Теперь убираем лишние скобки и вставляем этот массив в формулу. Нажимаем Enter.

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

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

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

Рассмотрим только вертикальное расположение данных (горизонтальное будет работать аналогично). Суть в том, чтобы рядом с данными создать столбец с формулой СЦЕПИТЬ, которая последовательно будет присоединять по одному слову по мере продвижения вниз. В нашем случае первая формула будет содержать только слово «Александр » (и пробел), следующая ниже добавляет «Сергеевич » (и пробел) и т.д.

Читайте также:  Быстрый дешевый интернет домашний

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

Соединение текста и даты

И еще один трюк с формулой СЦЕПИТЬ. Довольно часто приходится соединять текст с датой. Дата, как известно, это простое порядковое число (отсчет начинается с 1 января 1900 года), которому придается нужный формат даты. Если же соединить напрямую текст и дату, то на выходе получится указанный текст и простое число. Возьмем, например, слово «Сегодня » (не забываем добавить пробел в конце) и функцию СЕГОДНЯ (всегда возвращающую текущую дату).

Как видно, ничего не получилось.

Для правильной склейки нужно «обернуть» дату в формулу ТЕКСТ, которая придает указанный формат числу.

Формат ДД.ММ.ГГ указывает, в каком виде должна отражаться дата. Если, например, указать формат ДД.ММ.ГГГГ, то дата будет выглядеть так: 03.09.2015 (год состоит из 4-х цифр).

Кстати, добавлять текст к числу или дате можно не только с помощью функции СЦЕПИТЬ. Можно использовать пользовательский числовой формат. У меня когда-то в рабочем файле красовалась автоматически обновляемая надпись.

Поэтому я всегда был прекрасно осведомлен о текущей дате и дне недели. При этом содержимое ячейки состояло только из одной функции СЕГОДНЯ.

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

P.S. Все проблемы выше решены в новых функциях Excel СЦЕП и ОБЪЕДИНИТЬ .


Функция CONCAT объединяет текст из нескольких диапазонов и (или) строк, но не предоставляет разделители или аргументы аргументы ignoreempty.

Функция CONCAT заменяет функцию СЦЕПИТЬ. Функция СЦЕПИТЬ (CONCATENATE) также будет поддерживаться для совместимости с более ранними версиями Excel.

Примечание: Эта функция доступна в Windows или Mac, если у вас есть Office 2019 или подписка на Office 365. Если у вас есть подписка на Office 365, убедитесь в том, что у вас установлена последняя версия Office.

Синтаксис

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

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

Например, выражение =СЦЕП("Не";" ";"слышны";" ";"в";" ";"саду";" ";"даже";" ";"шорохи.") вернет строку Не слышны в саду даже шорохи.

Совет: Чтобы добавить разделители (например, пробелы или амперсанды ( &)) между текстом, который вы хотите объединить, и удалить пустые аргументы, которые не должны выводиться в Объединенном тексте, можно использовать функцию объединить.

Примечания

Если объединенная строка содержит свыше 32767 символов (ограничение для ячейки), функция СЦЕП вернет ошибку #ЗНАЧ!.

Примеры

Скопируйте данные примеров из приведенных ниже таблиц и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

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

Для начала мы изучим основы, а затем перейдем к более сложным вещам. Некоторые из этих базовых функций могут быть использованы в тандеме с другими, чтобы сформировать более мощные команды. ПОИСК и НАЙТИ

Эти простые функции позволяют определить расположение искомой текстовой строки внутри другой текстовой строки. Функции идентичны, за исключением того, что в случае НАЙТИ учитывается регистр, а в случае ПОИСК — нет. ПОИСК также поддерживает символы обобщения. ПОИСК и НАЙТИ возвращают позицию первого символа в строке.

Читайте также:  Java ошибка 1618 как исправить

Функция ПОИСК может быть очень полезна, когда вы хотите отсортировать длинный список доменов по ключевым словам. В случае доменов, которые не содержат искомые ключевые слова, будет возвращено «#ЗНАЧЕНИЕ», тогда как в случае содержащих ключевое слово доменов будет возвращено число. Пример может быть таким:

В этом примере мы ищем ключевое слово «dog» в URL. Эта функция возвращает число 12, потому что слово «dog» начинается с 12-го символа в строке с URL.

ЛЕВСИМВ, ПСТР и ПРАВСИМВ

ЛЕВСИМВ возвращает указанное количество первых символов текстовой строки.

Пример: = ЛЕВСИМВ (A1, 7) возвращает первые семь символов слева в строке из ячейки A1.

ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.

Пример: =ПСТР(A1,12,3) вернет три символа в строке, начиная с 12-го символа.

ПРАВСИМВ работает аналогичным ЛЕВСИМВ образом, но возвращает символы справа, то есть функция возвращает последний символ или несколько последних символов текстовой строки на основе заданного числа символов. Эта функция может оказаться полезной, например, при удалении блока «http://www.» из каждого доменного имени в списке доменов.

Пример: =ПРАВСИМВ(A1,14) возвращает 14 последних символов в строке.

Эти функции могут быть полезны для вычленения отдельных ключевых слов, доменных имен, данных LSI и другой информации из строки. Например, вы можете скопировать и вставить длинный список обратных ссылок в Excel, а затем создать новую функцию, которая ведет поиск по определенному ключевому слову. Затем следует скопировать и вставить функцию во все ячейки свободного столбца, чтобы задействовать каждый URL. Так вы сможете узнать количество сайтов, в доменных именах которых присутствует определенное ключевое слово.

СЦЕПИТЬ

СЦЕПИТЬ позволит вам объединять строки. Это может быть полезно при объединении отдельных частей URL в один адрес. Данная функция также может быть использована для создания запросов на построение ссылок, карт сайтов и списков ключевых слов.

Пример: = СЦЕПИТЬ(A1,B1) объединит ячейки A1 и B1.

СЛЧИС

Функция СЛЧИС создает случайное число. Она существенно упрощает рандомную сортировку списков информации. Рандомизация является одним из важнейших способов придания результатам вашей работы естественности, а это ключ к успешной кампании по построению ссылок.

Пример: поместив =СЛЧИС() в каждой строке в столбце мы сможем генерировать случайные числа.

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

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

Рандомная сортировка списка URL-адресов / доменов может быть полезна при сплит-тестирования различных шаблонов с минимизацией смещений, которые могут быть вызваны отсутствием рандомизации списка.

СМЕЩ

Вот отличный видео туториал (на английском) касательно использования функции «СМЕЩ». Согласно справке Microsoft, СМЕЩ «возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов». Эта функция может оказаться полезной с точки зрения SEO-оптимизатора, к примеру, при анализе флуктуаций ключевых слов в экспортированных файлах Google Analytics CSV.

Синтаксис: СМЕЩ (ссылка, смещ_по_строкам, смещ_по_столбцам, [высота], [ширина])

Продолжим работать с ключевой фразой «dog food» и экспортируем данные из Google Analytics (см. ниже):

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

С помощью команды СМЕЩ мы можем придать этому списку ключевых слов и диапазонов времени следующий вид:

Читайте также:  Kyocera fs 4020dn драйвер

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

Обратите внимание на то, что команда СТРОКА (ROW на рисунке выше) возвращает номер ячейки, в которой она находится. Это позволяет нам переходить к другим строкам и перемещать данные.

ИНДЕКС и ПОИСКПОЗ

Многие пользователи Excel охотно работают с командой ВПР. Но эта функция ограничена тем, что искомая информация должна находиться в самой первой (слева) колонке массива данных. Команда ИНДЕКС не имеют такого ограничения. Она эффективно возвращает значение из указанной строки и колонки в пределах заданного диапазона.

Синтаксис: ИНДЕКС(массив, номер_строки, [номер_столбца])

Для примера используем формулу =ИНДЕКС(A1:A5,3). С ее помощью мы можем извлечь текст из третьей строки массива в столбце A.

ПОИСКПОЗ повышает полезность ИНДЕКС. В отличие от ИНДЕКС, которая возвращает слово, функция ПОИСКПОЗ возвращает число.

Синтаксис: ПОИСКПОЗ(искомое_значение, просматриваемый_массив, [тип_сопоставления])

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

К числу опций Тип_сопоставления относятся:

  • «1» Меньше чем — Это значение по умолчанию. В данном случае функция найдет соответствие меньшее или равное искомому значению при условии, что список упорядочен в порядке возрастания.
  • «-1» Больше чем — Находит наименьшее значение, которое больше или равно искомому значению, при условии, что список упорядочен в порядке убывания.
  • «0» возвращает первое найденное значение, равное вашему искомому значению.Это рекомендуемая опция для целей SEO.

Используем пример выше с массивом данных по кормам для питомцев (pet food). Следующая формула вернет расположение (номер строки) фразы «guinea pig food» в массиве данных.

=ПОИСКПОЗ(«guinea pig food«,A1:A5,0)

В этом случае функция возвращает число 4, так как ключевая фраза «guinea pig food» проходит в нашем списке под четвертым номером.

Комбинируем ИНДЕКС и ПОИСКПОЗ

Вы можете комбинировать ИНДЕКС и ПОИСКПОЗ для решения более сложных задач. Комбинируйте их, используя следующий метод:

=ИНДЕКС(массив, формула ПОИСКПОЗ)

В качества примера давайте снова возьмем наш список ключевых слов о корме для собак (dog food) с несуществующего сайта dogfoodrus.com. В приведенной ниже таблице имеются разные ключевые слова, которые являются источником органического трафика для определенных URL-адресов целевой страницы. В каждой строке приводится количество посещений и переходов, связанных с конкретным ключевым словом.

Используя команды ИНДЕКС и ПОИСКПОЗ вместе, можно создать механизм поиска ключевых слов, который позволит вам получить информацию о количестве посетителей и переходов по определенному ключевому слову. Если у вас есть только четыре или пять ключевых слов (как в примере выше), искать нужные данные вручную несложно. Однако если вы скачали файлы Analytics или Google Webmaster Tools с данными о тысячах ключевых слов, то использование приведенных ниже формул существенно упрощает поиск определенных ключевых слов и создание определенного списка.

=ИНДЕКС($B$3:$E$7,ПОИСКПОЗ(B11,$B$3:$B$7,0),3) — число посещений

=ИНДЕКС($B$3:$E$7,ПОИСКПОЗ(B11,$B$3:$B$7,0),4) — число переходов

В этом примере функция ИНДЕКС распространяется на диапазон от B3 до E7, который охватывает все импортированные данные. Функция использует команду ПОИСКПОЗ, чтобы определить строку в массиве, где найдено совпадение. Цифры 3 и 4 в конце формулы относятся к столбцам, где находятся данные, которые вам нужно скопировать.

Есть много вариаций на тему применения ИНДЕКС и ПОИСКПОЗ вместе. С их помощью можно на порядок упростить поиск информации в огромной таблице. В отличие от ВПР, для эффективной работы с ИНДЕКС и ПОИСКПОЗ не требуется, чтобы поле поиска находилось в крайнем левом столбце массива.

Заключение

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

Ссылка на основную публикацию
Adblock detector