Vba excel сцепить текст
Ребята, подскажите пожалуйста, можно ли, не прибегая к макросу, сцепить ячейки следующим образом:
в таблице 2 решающих столбца:
в первом - артикула (значения повторяются),
во втором - размеры.
Я создаю таблицу, сделав выборку только уникальных значений по столбцу 1 (артикула). И в столбец 2 нужно подтянуть значения из первой таблицы, сцепив между собой значения всех ячеек, соответствующих этому артикулу.
артикул размер
123_____2
123_____4
124_____2
124_____3
124_____5
В итоге надо получить таблицу вида:
артикул размер
123_____2 4
124_____2 3 5
Возможно ли реализовать такое с помощью формул?
Если нет - то подскажите - как использовать макросы?
Я с ними не дружна, однако нашла такой вот текст:
Однако я не умею ни вставлять в макросы (к стыду своему - даже не нашла, где открыть редактор в 2010 экселе - только запуск самого макроса), ни запускать их корректно по отношению к нужному диапазону.
Ребят, буду благодарна любому совету! Пожалуйста, поделитесь своими соображениями=) __________________
Помощь в написании контрольных, курсовых и дипломных работ здесь
Сцепить ячейки по условию
Доброго времени суток, Находил похожее, но у меня совсем другое условие.
Сцепить ячейки по условию с разделителем
Здравствуйте. Очень нужна помощь в написании макроса, который будет сцеплять ячейки (вставляя.
Сцепить ячейки с сохранением формата текста
Добрый вечер. В первой ячейке текст с подчёркнутыми словами. Во второй формула. Как сцепить.
Сложить ячейки в столбце отвечающие определенному цвету с учетом определенной иерархии
Друзья помогите пожалуйста в решении следующей задачи: 1. есть повторяющаяся цветовая иерархия;.
Где создавать новую таблицу - на другом листе? или в процессе формирования удалять дублирующиеся строки?
В образце: 123_____2 4
цифра 4 где должна стоять в соседней ячейке или в той же, что и "2", но через какой-нибуть разделитель?
Diskretor,
1. Где - не важно. Но удобнее на новом листе.
Вообще - я уже поместила на новый лист эту таблицу (без значений размеров) отфильтрованную по уникальным строкам. и вот хорошо бы в пустой столбец "Размер" новой (отфильтрованной по уникальности) таблицы поместить сцепленные значения из первой - неотфильтрованной
2. В той же ячейке через любой разделитель (пробел - оптимально). Дело в том, что для каждого артикула идет разное количество размеров - от 1ого до 20 - и плодить такое количество столбцов очень не комфортно!
Данные будут браться с листа № 1, начиная с ячейки A2 и записываться на лист № 2.
Открываем Excel. Нажимаем левый ALT+F8 набираем любое слово, нажимаем .
В появившемся окошке выделяем все, удаляем. В это же окошко копируем приведенный выше код (без цифровой нумерации).
Закрываем окно редактора, сохраняемся.
Для запуска макроса нажмите левый Alt+F8, .
Также для запуска можете создать кнопку и повесить ее прямо на лист, либо на панель инструментов.
Также может возникнуть проблема, если у Вас отключены макросы с целью безопасности.
Главное Меню (крайнее левое на ленте)-Параметры Excel-Центр управления безопасностью-Параметры центра управления безопасностью-Параметры макросов->уровень безопасности "Низкий" (офис 2007).
Стоит добавить, что перед выполнением макроса фильтрации необходимо заранее отсортировать колонку "А" (Данные -> Сортировка).
Если же это недопустимо, можно воспользоваться кодом на основе словаря:
Все чаще вижу на разных форумах вопросы типа: Есть таблица, в одном столбце фамилии, в другом оценки(виды работ и т.д.) . Как сцепить в одной ячейке для каждой фамилии только принадлежащие ей оценки? Стандартными функциями это сделать весьма проблематично, т.к. заранее неизвестно сколько будет этих оценок и фамилий. Или собрать в одну ячейку через запятую фамилии всех сотрудников одного отдела, но все сотрудники идут вразнобой. Т.е. из такой таблицы:
Получить такую:
Вот и решил написать небольшую функцию пользователя на VBA, которая решает данную проблему. Так же подобную функцию называют "многоразовый ВПР", потому что она по критерию возвращает ВСЕ значения для этого критерия, а не только первое.
Для применения функции в своем файле достаточно создать стандартный модуль (о том как создать стандартный модуль: Что такое модуль? Какие бывают модули?) и просто вставить приведенный код. После этого в диспетчере функций появиться новая категория (если до этого её не было) - Определенные пользователем (User Defined) . В ней эта функция - СцепитьЕсли.
Синтаксис:
=СцепитьЕсли( B5:B18 ; B5 ; C5:C18 ;"-";0)
По принципу работы функция похожа на стандартную СУММЕСЛИ. Указывается диапазон значений(где просматривать значение), критерий и диапазон значений для сцепления. Символ для разделения слов указывать необязательно.
Диапазон (B5:B18) - диапазон, в котором искать критерий(указывается один столбец)
Критерий (B5) - критерий. Значение, на основании которого необходимо сцеплять значения. Может содержать символы подстановки - * и ? и символы сравнения ( <>"", 0, "<>"&A1 и т.п.). Просматривается Диапазон. При совпадении значения ячейки в Диапазоне значение из Диапазона_Сцепления добавляется к результату с выбранным разделителем.
Диапазон_сцепления (C5:C18) - из этого диапазона берется значение для сцепления, если значение в аргументе Диапазон совпадает с аргументом Критерий(указывается один столбец). Если в Диапазоне значение 5-ой строки совпадает с критерием, то из Диапазона_Сцепления будет взято так же значение из 5-ой строк этого диапазона и сцеплено с результатом.
Разделитель ("-") - По умолчанию пробел, но можно задать любой другой символ или группу символов.
БезПовторов - если указать 1 или ИСТИНА, то в результате получится строка, в которой нет одинаковых значений. Если указать 0 или ЛОЖЬ, то будут выведены все значения. По умолчанию значение ЛОЖЬ.
Примечание: для работы функции должны быть разрешены макросы
Все чаще вижу на разных форумах вопросы типа: Есть таблица, в одном столбце фамилии, в другом оценки(виды работ и т.д.) . Как сцепить в одной ячейке для каждой фамилии только принадлежащие ей оценки? Стандартными функциями это сделать весьма проблематично, т.к. заранее неизвестно сколько будет этих оценок и фамилий. Или собрать в одну ячейку через запятую фамилии всех сотрудников одного отдела, но все сотрудники идут вразнобой. Т.е. из такой таблицы:
Получить такую:
Вот и решил написать небольшую функцию пользователя на VBA, которая решает данную проблему. Так же подобную функцию называют "многоразовый ВПР", потому что она по критерию возвращает ВСЕ значения для этого критерия, а не только первое.
Для применения функции в своем файле достаточно создать стандартный модуль (о том как создать стандартный модуль: Что такое модуль? Какие бывают модули?) и просто вставить приведенный код. После этого в диспетчере функций появиться новая категория (если до этого её не было) - Определенные пользователем (User Defined) . В ней эта функция - СцепитьЕсли.
Синтаксис:
=СцепитьЕсли( B5:B18 ; B5 ; C5:C18 ;"-";0)
По принципу работы функция похожа на стандартную СУММЕСЛИ. Указывается диапазон значений(где просматривать значение), критерий и диапазон значений для сцепления. Символ для разделения слов указывать необязательно.
Диапазон (B5:B18) - диапазон, в котором искать критерий(указывается один столбец)
Критерий (B5) - критерий. Значение, на основании которого необходимо сцеплять значения. Может содержать символы подстановки - * и ? и символы сравнения ( <>"", 0, "<>"&A1 и т.п.). Просматривается Диапазон. При совпадении значения ячейки в Диапазоне значение из Диапазона_Сцепления добавляется к результату с выбранным разделителем.
Диапазон_сцепления (C5:C18) - из этого диапазона берется значение для сцепления, если значение в аргументе Диапазон совпадает с аргументом Критерий(указывается один столбец). Если в Диапазоне значение 5-ой строки совпадает с критерием, то из Диапазона_Сцепления будет взято так же значение из 5-ой строк этого диапазона и сцеплено с результатом.
Разделитель ("-") - По умолчанию пробел, но можно задать любой другой символ или группу символов.
БезПовторов - если указать 1 или ИСТИНА, то в результате получится строка, в которой нет одинаковых значений. Если указать 0 или ЛОЖЬ, то будут выведены все значения. По умолчанию значение ЛОЖЬ.
Примечание: для работы функции должны быть разрешены макросы
Используйте функцию СЦЕПИТЬ, относящуюся к текстовым функциям, для объединения двух или нескольких текстовых строк в одну.
Важно: В Excel 2016, Excel Mobile и Excel в Интернете эта функция была заменена функцией СКАЙТ. Функция СЦЕПИТЬ пока еще доступна для обратной совместимости, однако рекомендуем использовать вместо нее функцию СЦЕП. В последующих версиях Excel функция СЦЕПИТЬ может быть недоступна.
Синтаксис: СЦЕПИТЬ(текст1;[текст2];…)
Ниже приведены примеры.
=СЦЕПИТЬ("Популяция рек для ";A2;" ";A3;" составляет ";A4;" на километр.")
Имя аргумента
текст1 (обязательный)
Первый элемент для объединения. Это может быть текстовое значение, число или ссылка на ячейку.
текст2. (необязательные)
Дополнительные текстовые элементы для объединения. Можно указать до 255 элементов и до 8 192 символов.
Примеры
Чтобы использовать эти примеры в Excel, скопируйте данные из приведенной ниже таблицы и вставьте их на новый лист в ячейку A1.
=СЦЕПИТЬ("Популяция рек для ";A2;" ";A3;" составляет ";A4;" на километр.")
Создает предложение, объединяя данные в столбце А с остальным текстом. Результат: "Популяция рек для вида речная форель составляет 32 на километр".
Объединяет строку в ячейке В2, пробел и значение в ячейке С2. Результат: "Виталий Токарев".
Объединяет текст в ячейке C2, строку, состоящую из запятой и пробела, и значение в ячейке B2. Результат: "Виталий Токарев".
Объединяет строку в ячейке B3, строку, состоящую из пробела, амперсанда и еще одного пробела, и значение в ячейке C3. Результат: Fourth & Pine.
Объединяет те же элементы, что и в предыдущем примере, но с помощью оператора & (амперсанд) вместо функции СЦЕПИТЬ. Результат: Fourth & Pine.
Распространенные неполадки
Кавычки отображаются в строке результата.
Используйте точку с запятой для разделения текстовых элементов. Например: Excel отобразит результат функции =СЦЕПИТЬ ("Привет, ""мир!") как "Привет,"мир!" с лишней кавычкой, так как пропущена точка с запятой между текстовыми аргументами.
Числа не нужно заключать в кавычки.
Слова расположены впритык друг к другу.
Если не поместить пробелы между текстовыми элементами, в строке результата они будут отображаться слитно. Добавьте пробелы в формулу с функцией СЦЕПИТЬ. Это можно сделать двумя способами.
Добавьте двойные кавычки с пробелом между ними (" "). Например: =СЦЕПИТЬ("Привет,"; " "; "мир!").
Добавьте пробел после текстового аргумента. Например: =СЦЕПИТЬ("Привет, "; "мир!"). К строке "Привет," добавлен пробел.
Рекомендации
Необходимые действия
Вместо функции СЦЕПИТЬ используйте знак & (амперсанд).
С помощью оператора & можно объединять текстовые элементы, не прибегая к функции.
Например, =A1 & B1 возвращает то же значение, что и =С CONCATENATE(A1;B1). Во многих случаях воспользоваться оператором амперсанда проще и быстрее, чем создавать строки с помощью функции СЦЕПИТЬ.
Для объединения и форматирования строк используйте функцию ТЕКСТ.
Функция ТЕКСТ преобразует числовое значение в текст и объединяет числа с текстом или символами.
Предположим, что ячейка A1 содержит число 23,5. Чтобы отформатировать число как сумму в рублях, можно использовать следующую формулу:
Дополнительные сведения
Для объединения и форматирования строк используйте функцию ТЕКСТ.
Ознакомьтесь с дополнительными сведениями об использовании операторов вычислений.
Читайте также: