Как вставить каждое слово в отдельную ячейку excel
Например мне надо в столбец 3 там где госты вставить к вонце текста в каждую ячейку одно слово. Как это проще сделать ?
Например мне надо в столбец 3 там где госты вставить к вонце текста в каждую ячейку одно слово. Как это проще сделать ? koyaanisqatsi
Например мне надо в столбец 3 там где госты вставить к вонце текста в каждую ячейку одно слово. Как это проще сделать ? Автор - koyaanisqatsi
Дата добавления - 17.07.2015 в 19:01
1.Скопировать столбец в эксель
2. Прописать формулу
в верхней ячейке соседнего столбца (или на 1 строку ниже)
3. размножить на рабочий диапазон
4. вставить значения обратно
[p.s.] Добавляемое слово в ячейке R2C3
На мой взгляд1.Скопировать столбец в эксель
2. Прописать формулу
в верхней ячейке соседнего столбца (или на 1 строку ниже)
3. размножить на рабочий диапазон
4. вставить значения обратно
[p.s.] Добавляемое слово в ячейке R2C3 Udik
3. размножить на рабочий диапазон
4. вставить значения обратно
[p.s.] Добавляемое слово в ячейке R2C3 Автор - Udik
Дата добавления - 17.07.2015 в 19:17
Возможно из-за встречающихся переносов строки. "Ентеров" был бы хороший метод если бы совпадали ячейки. А так эксель придумывает какое-то свое количество ячеек (
Возможно из-за встречающихся переносов строки. "Ентеров" koyaanisqatsi
[p.s.]Не видел, что в конце 2 строки "Итого" (не листал так далеко ), спасибо Роману (Rioran), подправил.[/p.s.]
[p.s.]Не видел, что в конце 2 строки "Итого" (не листал так далеко ), спасибо Роману (Rioran), подправил.[/p.s.] KSV
[p.s.]Не видел, что в конце 2 строки "Итого" (не листал так далеко ), спасибо Роману (Rioran), подправил.[/p.s.] Автор - KSV
Дата добавления - 17.07.2015 в 19:28
koyaanisqatsi, здравствуйте.
Предлагаю подумать в этом направлении. Обратите внимание, нужная вам таблица в этом документе носит номер 2, поэтому Tables(2). Счётчик бежит от 2-й строки до последней минус 2, т.к. там заголовки и итоги.
[/vba]
***
UPD: Вставка от KSV выглядит производительнее моей, ещё учесть 2 строки "Итого" - и будет ближе к идеалу =)
UPD: Раннее связывание в Эксель документе будет производительнее, чем позднее. Для этого стоит в VBA редакторе Excel через Tools => References добавить Microsoft Word XX.0 Object Library и переписать строку объявления переменной:
[/vba]
UPD: Сергей, наши с тобой коды делают перенос строки при добавлении =) Если бы не пятница, я бы попробовал убрать этот эффект =)
UPD: Сергей, не сразу заметил, что если в твоём коде убрать "vbCr &" - то переноса не будет.
koyaanisqatsi, здравствуйте.
Предлагаю подумать в этом направлении. Обратите внимание, нужная вам таблица в этом документе носит номер 2, поэтому Tables(2). Счётчик бежит от 2-й строки до последней минус 2, т.к. там заголовки и итоги.
[/vba]
***
UPD: Вставка от KSV выглядит производительнее моей, ещё учесть 2 строки "Итого" - и будет ближе к идеалу =)
UPD: Раннее связывание в Эксель документе будет производительнее, чем позднее. Для этого стоит в VBA редакторе Excel через Tools => References добавить Microsoft Word XX.0 Object Library и переписать строку объявления переменной:
[/vba]
UPD: Сергей, наши с тобой коды делают перенос строки при добавлении =) Если бы не пятница, я бы попробовал убрать этот эффект =)
UPD: Сергей, не сразу заметил, что если в твоём коде убрать "vbCr &" - то переноса не будет. Rioran
Предлагаю подумать в этом направлении. Обратите внимание, нужная вам таблица в этом документе носит номер 2, поэтому Tables(2). Счётчик бежит от 2-й строки до последней минус 2, т.к. там заголовки и итоги.
[/vba]
***
UPD: Вставка от KSV выглядит производительнее моей, ещё учесть 2 строки "Итого" - и будет ближе к идеалу =)
UPD: Раннее связывание в Эксель документе будет производительнее, чем позднее. Для этого стоит в VBA редакторе Excel через Tools => References добавить Microsoft Word XX.0 Object Library и переписать строку объявления переменной:
[/vba]
UPD: Сергей, наши с тобой коды делают перенос строки при добавлении =) Если бы не пятница, я бы попробовал убрать этот эффект =)
UPD: Сергей, не сразу заметил, что если в твоём коде убрать "vbCr &" - то переноса не будет. Автор - Rioran
Дата добавления - 17.07.2015 в 19:29
А я его специально добавил
IMHO, так нагляднее, когда добавленная строка "отделена" от исходного текста. не сразу заметил, что если в твоём коде убрать "vbCr &"
А я его специально добавил
IMHO, так нагляднее, когда добавленная строка "отделена" от исходного текста. KSV не сразу заметил, что если в твоём коде убрать "vbCr &"
А я его специально добавил
IMHO, так нагляднее, когда добавленная строка "отделена" от исходного текста. Автор - KSV
Дата добавления - 17.07.2015 в 19:48 лучше пожертвовать лишние "полсекунды", но решение будет более универсальным.
Сергей, ты прав. Сначала подумал - в чём вопрос? Просто добавить в Excel самую раннюю библиотеку Word - 11.0 например - чтобы на 2003 вордах работало, но. Оказывается, в поздних версиях Excel её нет в свободном доступе для добавления в References. Во всяком случае, в своём 2013 Excel я ссылки моложе 15.0 не нашёл. лучше пожертвовать лишние "полсекунды", но решение будет более универсальным.
Сергей, ты прав. Сначала подумал - в чём вопрос? Просто добавить в Excel самую раннюю библиотеку Word - 11.0 например - чтобы на 2003 вордах работало, но. Оказывается, в поздних версиях Excel её нет в свободном доступе для добавления в References. Во всяком случае, в своём 2013 Excel я ссылки моложе 15.0 не нашёл. Rioran лучше пожертвовать лишние "полсекунды", но решение будет более универсальным.
Сергей, ты прав. Сначала подумал - в чём вопрос? Просто добавить в Excel самую раннюю библиотеку Word - 11.0 например - чтобы на 2003 вордах работало, но. Оказывается, в поздних версиях Excel её нет в свободном доступе для добавления в References. Во всяком случае, в своём 2013 Excel я ссылки моложе 15.0 не нашёл. Автор - Rioran
Дата добавления - 17.07.2015 в 19:59
Хм. Прям ГИК вечеринка образовалась ))))
Вот и поговорили ))))
Думал будет какоето простое решение. Типа на подобии как в фаре или эсидиси переименовывать файлы ставишь какойто супер символ с чего менять и потом такой же супер символ и после него что добавить. и получаются новые файлы уже со старым именем + добавочка.
Но подобного как в Far организации не придумал (
А можно тогда этот макрос переделать не под номерной столбец а под выделенный фрагмент ?
Хм. Прям ГИК вечеринка образовалась ))))
Вот и поговорили ))))
Думал будет какоето простое решение. Типа на подобии как в фаре или эсидиси переименовывать файлы ставишь какойто супер символ с чего менять и потом такой же супер символ и после него что добавить. и получаются новые файлы уже со старым именем + добавочка.
Но подобного как в Far организации не придумал (
А можно тогда этот макрос переделать не под номерной столбец а под выделенный фрагмент ? koyaanisqatsi
Думал будет какоето простое решение. Типа на подобии как в фаре или эсидиси переименовывать файлы ставишь какойто супер символ с чего менять и потом такой же супер символ и после него что добавить. и получаются новые файлы уже со старым именем + добавочка.
Но подобного как в Far организации не придумал (
А можно тогда этот макрос переделать не под номерной столбец а под выделенный фрагмент ? Автор - koyaanisqatsi
Дата добавления - 17.07.2015 в 22:27
Про поиск и подсветку дубликатов в разных ячейках и диапазонах я уже не раз писал, но что делать если нужно найти и, возможно, удалить повторяющиеся слова внутри ячейки? Например, мы имеем вот такую таблицу с данными (разделителями могут быть не обязательно пробелы):
Хорошо видно, что некоторые имена в списках внутри ячеек повторяются. Давайте посмотрим, что можно с этим сделать.
Способ 1. Ищем повторения: текст по столбцам и формула массива
Это не самый удобный и быстрый, но зато самый простой вариант решения задачи "на коленке". Выделим исходный список и разобъем его на столбцы по пробелам с помощью команды Данные - Текст по столбцам (Data - Text to columns) . В открывшемся окне трёхшагового Мастера выберем формат По разделителю (By delimiter) на первом шаге и поставим флажок Пробел (Space) на втором:
Если в исходных данных могут быть лишние пробелы, то лучше включить и опцию Считать последовательные разделители одним (Treat consecutive delimiters as one) - это избавит нас от лишних столбцов.
На третьем шаге в поле Поместить в зададим пустую ячейку рядом с таблицей, чтобы результаты не затёрли нам исходные данные и нажмём на Готово (Finish) :
Наши данные разделятся по ячейкам. Останется подсчитать количество повторов в каждой строке с помощью небольшой, но хитрой формулы массива:
В английской версии это будет =SUMPRODUCT(N(COUNTIF(B2:G2,B2:G2)>1))
Давайте разберём логику её работы на примере первой строки.
- Сначала мы с помощью формулы СЧЁТЕСЛИ( B2:G2 ; B2:G2 ) вычисляем по очереди количество вхождений каждого имени в диапазон B2:G2 и получаем на выходе массив 2,1,2,1>, т.к. Иван встречается в первой строке 1 раз, Елена - 2 раза, Сергей - 1 и т.д.
- Проверяем с помощью СЧЁТЕСЛИ(B2:G2;B2:G2) >1 какие из полученных чисел больше единицы, т.е. где у нас повторы. На выходе эта формула выдаст нам массив результатов проверки в виде .
- Переводим логические значения ЛОЖЬ и ИСТИНА в более удобные для подсчета 0 и 1, соответственно, с помощью функции Ч. На выходе получаем массив >.
- Суммируем все элементы получившегося массива функцией СУММПРОИЗВ. Можно было бы использовать и обычную функцию СУММ, но тогда пришлось бы жать вместо привычного Enter сочетание клавиш Ctrl + Shift + Enter , чтобы ввести формулу как формулу массива.
По получившемуся столбцу можно легко отфильтровать строки с повторами и работать потом с ними дальше уже вручную.
Минусы такого способа, впрочем, весьма очевидны: при изменении в исходных данных придётся повторять всю процедуру заново, дубликаты не очень заметны и удалять их тоже надо врукопашную. Поэтому идём дальше.
Способ 2. Выделение цветом повторов внутри ячейки макросом
Если дубликаты нужно именно наглядно показать, то удобнее будет использовать для этого специальный макрос. Откроем редактор Visual Basic одноимённой кнопкой на вкладке Разработчик (Developer - Visual Basic) или сочетанием клавиш Alt + F11 . Вставим в книгу новый пустой модуль через меню Insert - Module и скопируем туда вот такой код:
Теперь можно вернуться в главное окно Excel, выделить ячейки с текстом и запустить созданный макрос через кнопку Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt + F8 . Этот макрос проходит по всем выделенным ячейкам и помечает повторения красным цветом шрифта прямо внутри ячейки:
Если нужно, чтобы цветом выделялись только клоны, но не первые вхождения (т.е. только вторая и третья, но не первая Алиса, например), то достаточно будет просто убрать из кода строку 20.
Способ 3. Выводим повторы в соседний столбец
Если повторы внутри ячеек нужно не просто подсветить, а явным образом вывести, например, в соседний столбец, то удобнее будет использовать для этого макрофункцию, созданную по образу предыдущего макроса. Добавим в редакторе Visual Basic новый модуль и вставим туда код нашей функции GetDuplicates:
Эта функция, как легко догадаться, принимает в качестве единственного аргумента ячейку с текстом и выводит в качестве результата все повторы, которые там найдет:
Способ 4. Удаление повторов внутри ячейки макросом
Если нужно просто удалить дубликаты внутри ячейки, чтобы все оставшиеся там слова не повторялись, то макрос будет похож на предыдущий, но попроще:
Способ 5. Удаление повторов внутри ячейки через Power Query
Этот способ использует бесплатную надстройку Excel для обработки данных под названием Power Query. Для Excel 2010-2013 скачать её можно с сайта Microsoft, а в Excel 2016-2019 она уже встроена по умолчанию. Огромным плюсом этого варианта является возможность автоматического обновления - если в будущем исходные данные изменятся, то нам не придется заново проделывать всю обработку (как в Способе 1) или запускать макрос (как в Способе 4) - достаточно будет просто обновить созданный запрос.
Сначала наши данные нужно загрузить в Power Query. Проще всего для этого превратить нашу таблицу в "умную" сочетанием клавиш Ctrl + T или кнопкой Форматировать как таблицу на вкладке Главная (Home - Format as Table) , а затем нажать кнопку Из таблицы/диапазона (From table/range) на вкладке Power Query (если у вас Excel 2010-2013) или на вкладке Данные (если у вас Excel 2016 или новее):
Поверх окна Excel откроется окно редактора запросов Power Query с загруженными туда нашими данными:
Дальше делаем следующую цепочку действий:
Удаляем ненужный пока шаг Измененный тип (Changed Type) справа в панели применённых шагов с помощью крестика слева от шага.
Чтобы можно было потом идентифицировать принадлежность каждого имени к исходной строке - добавляем столбец с нумерацией строк на вкладке Добавление столбца - Столбец индекса - От 1 (Add Column - Index Column - From 1) :
Выделяем столбец с именами и жмём на вкладке Преобразование - Разделить столбец - По разделителю (Transform - Split Column - By delimiter) , а в открывшемся окне выбираем деление по каждому пробелу и - главное - деление на строки, а не на столбцы в расширенных параметрах:
После нажатия на ОК увидим следующее:
Теперь выделяем оба столбца (удерживая клавишу Ctrl или Shift ) и удаляем дубликаты через Главная - Удалить строки - Удалить дубликаты (Home - Remove Rows - Remove Duplicates) .
Осталось собрать всё обратно в ячейки :) Для этого выделим столбец Индекс и используем команду Группировать по на вкладке Преобразование (Transform - Group By) со следующими параметрами:
После нажатия на ОК наши имена сгруппируются во вложенные таблицы, имитирующие начальные ячейки - только уже без повторов. Увидеть содержимое свёрнутых таблиц можно, если щёлкнуть мышью в фон ячейки рядом со словом Table (но не в слово Table!):
Осталось вытащить все имена из первой колонки каждой таблицы и склеить их через пробел. Это можно сделать с помощью небольшой формулы на встроенном в Power Query языке М. Выберем на вкладке Добавление столбца команду Настраиваемый столбец (Add Column - Custom Column) и введём в открывшееся окно имя нового столбца и формулу (с соблюдением регистра!):
Осталось удалить ненужные более столбцы Индекс и Ячейки, щелкнув по их заголовкам правой кнопкой мыши и выбрав команду Удалить столбцы (Remove Columns) и выгрузить результаты на лист через Главная - Закрыть и загрузить - Закрыть и загрузить в (Home - Close & Load - Close & Load to..) :
Задача решена! Если в будущем данные в исходной "умной" таблице изменятся или к ней будут дописаны новые строки, то достаточно будет просто обновить запрос, щёлкнув по результирующей зелёной таблице правой кнопкой мыши и выбрав команду Обновить или нажав сочетание клавиш Ctrl + Alt + F5 .
Пробовали ли вы когда-нибудь разбить предложение на отдельные слова и поместить каждое слово в одну ячейку? Здесь я расскажу вам, как быстро разбить предложение на отдельные ячейки по словам в Excel.
Разделить предложение на отдельные слова с помощью функции «Текст в столбец»
Разделить предложение на отдельные слова с помощью Kutools for Excel
Разделить предложение на отдельные слова с помощью функции Text to Column
1. Выделите предложения, которые хотите разбить на слова, и нажмите Данные > Текст в столбцы . См. Снимок экрана:
2. На шаге 1 мастера преобразования текста в столбцы установите флажок с разделителями и нажмите Далее , чтобы продолжить. См. Снимок экрана:
3. На шаге 2 мастера установите флажок Пробел в разделе Разделители . См. Снимок экрана:
4. Нажмите Далее , чтобы перейти к последнему шагу мастера, и выберите ячейку для размещения отдельных слов. См. Снимок экрана:
5. Нажмите Готово , и выбранные предложения будут разбиты на слова в отдельных ячейках.
Разделить предложение на отдельные слова с помощью Kutools for Excel
С функцией Text to Columns шаги мастера немного сложны, но с помощью Split Cells в Kutools for Excel , операции будут намного сложнее проще.
Бесплатная загрузкаБесплатная полнофункциональная версия через 30 дней
После бесплатной установки Kutools for Excel, сделайте следующее:
1. Выделите ячейки предложения и нажмите Kutools > Объединить и разделить > Разделить ячейки . См. Снимок экрана:
2. В диалоговом окне Разделить ячейки отметьте нужный тип разделения в разделе Тип и установите флажок Spac e в раздел Разбить по . См. Снимок экрана:
3. Нажмите Ok, выберите ячейку для вывода разделенных ячеек в всплывающем диалоговом окне и нажмите OK , чтобы закончить. Смотрите снимок экрана:
После этого предложения были разбиты на отдельные слова.
Чисто теоретически это задача на строки при помощи макроса VBA. В цикле ищем второй пробел с конца, ставим маркер на 1 символ вправо и с этой позиции по конце фразы копируем текст в отдельную ячейку.
Как провернуть через формулы я ХЗ
Сергей Явин,
Да нет, несложная. Хотя и нетипичная. В VBA делается за несколько минут.
Можно было бы и формулами, но тогда не получится именно переместить В последнюю ячейку.
А так вариант решения через формулы:
- найти n-ный пробел от конца строки в ячейке (FIND)
- отрезать все слева от него, поместить в другой столбец (LEFT)
- отрезать все справа от него, поместить в третий столбец (RIGHT)
Тогда будут столбцы с искомыми данными, просто исходный останется в оригинальном виде.
=ЕСЛИОШИБКА(ПРАВСИМВ(RC[-1];ДЛСТР(RC[-1])-ПОИСК(" ";RC[-1]; ПОИСК(" ";RC[-1])+1));"")
Здесь RC[-1] - соседняя (слева от клетки с формулой) клетка. В клетку с формулой копируется все что после первых двух пробелов (двух слов). Если нужно переносить меньше слов, вложить во второй "ПОИСК" еще один поиск, в качестве 3-го параметра (стартовой позиции для поиска), етц.
Вам нужна поддержка регулярных выражений. По умолчанию её нет в Excel.
- Использовать Google Sheets, там есть поддержка (например, функция REGEXMATCH)
- Добавить поддержку в Excel, для этого нужно погуглить "excel регулярные выражения". Здесь расписывать нет смысла, т.к. уже всё расписано в других местах.
Само выражение зависит от того, что вы хотите конкретно. Ведь 2 или 3 слова - это не однозначная формула. Так 2 слова или 3 слова нужно? Или 50/50 случайным образом? Или 3 слова, когда есть 3, а когда нет 3, но есть 2, тогда 2? А что если всего 1 слово, какой должен быть результат? И так далее.
Для двух последних слов выражение примерно такое:
\s+(\S+\s+\S+)\s*$
Но, как я сказал выше, зависит от нюансов.
Читайте также: