Excel диапазон с разрывом
Переносы строк внутри одной ячейки, добавляемые с помощью сочетания клавиш Alt + Enter - дело весьма частое и привычное. Иногда их делают сами пользователи, чтобы добавить красоты длинному тексту. Иногда такие переносы добавляются автоматически при выгрузке данных из каких-либо рабочих программ (привет 1С, SAP и т.д.) Проблема в том, что на такие таблицы приходится потом не просто любоваться, а с ними работать - и вот тогда эти невидимые символы переноса могут стать проблемой. А могут и не стать - если уметь правильно с ними обращаться.
Давайте-ка мы разберёмся в этом вопросе поподробнее.
Удаление переносов строк заменой
Если нам нужно избавиться от переносов, то первое, что обычно приходит в голову - это классическая техника "найти и заменить". Выделяем текст и затем вызываем окно замены сочетанием клавиш Ctrl + H или через Главная - Найти и выделить - Заменить (Home - Find&Select - Replace) . Одна неувязочка - не очень понятно, как ввести в верхнее поле Найти (Find what) наш невидимый символ переноса строки. Alt + Enter тут, к сожалению, уже не работает, скопировать этот символ непосредственно из ячейки и вставить его сюда тоже не получается.
Поможет сочетание Ctrl + J - именно оно является альтернативой Alt + Enter в диалоговых окнах или полях ввода Excel:
Обратите внимание, что после того, как вы поставите мигающий курсор в верхнее поле и нажмёте Ctrl + J - в самом поле ничего не появится. Не пугайтесь - это нормально, символ-то невидимый :)
В нижнее поле Заменить (Replace with) либо ничего не вводим, либо вводим пробел (если хотим не просто удалить переносы, а заменить их на пробел, чтобы строки не склеились в единое целое). Останется нажать на кнопку Заменить всё (Replace All) и наши переносы исчезнут:
Нюанс: после выполнения замены введённый с помощью Ctrl + J невидимый символ остаётся в поле Найти и может помешать в дальнейшем - не забудьте его удалить, установив курсор в это поле и несколько раз (для надёжности) нажав на клавиши Delete и Backspace .
Удаление переносов строк формулой
Если нужно решить задачу именно формулами, то можно использовать встроенную функцию ПЕЧСИМВ (CLEAN) , которая умеет очищать текст от всех непечатаемых символов, включая и наши злополучные переносы строк:
Такой вариант, однако, не всегда бывает удобен, т.к. строки после этой операции могут склеиваться между собой. Чтобы этого не происходило, нужно не просто удалять символ переноса, а заменять его на пробел (см. следующий пункт).
Замена переносов строк формулой
А если хочется не просто удалить, а именно заменить Alt + Enter на, например, пробел, то потребуется уже другая, чуть более сложная конструкция:
Чтобы задать невидимый символ переноса мы используем функцию СИМВОЛ (CHAR) , которая выводит символ по его коду (10). А потом функция ПОДСТАВИТЬ (SUBSTITUTE) ищет в исходных данных наши переносы и заменяет их на любой другой текст, например, на пробел.
Деление на столбцы по переносу строки
Знакомый многим и очень удобный инструмент Текст по столбцам с вкладки Данные (Data - Text to Columns) тоже может замечательно работать с переносами строк и разделить текст из одной ячейки на несколько, разбив его по Alt + Enter . Для этого на втором шаге мастера нужно выбрать вариант пользовательского символа-разделителя Другой (Custom) и использовать уже знакомое нам сочетание клавиш Ctrl + J как альтернативу Alt + Enter :
Если в ваших данных может встречаться несколько переносов строк подряд, то можно их "схлопнуть", включив флажок Считать последовательные разделители одним (Treat consecutive delimiters as one) .
После нажатия на Далее (Next) и прохождения всех трёх шагов мастера мы получим желаемый результат:
Обратите внимание, что до выполнения этой операции необходимо вставить справа от разделяемого столбца достаточное количество пустых колонок, чтобы образовавшийся текст не затёр те значения (цены), которые были справа.
Деление на строки по Alt+Enter через Power Query
Ещё одной любопытной задачей является деление многострочного текста из каждой ячейки не на столбцы, а на строки:
Вручную такое делать долго, формулами - сложно, макросом - не каждый напишет. А на практике подобная задача встречается чаще, чем хотелось бы. Самым простым и лёгким решением будет использовать для этой задачи возможности надстройки Power Query, которая встроена в Excel начиная с 2016 года, а для более ранних версий 2010-2013 её можно совершенно бесплатно скачать с сайта Microsoft.
Чтобы загрузить исходные данные в Power Query их нужно сначала преобразовать в "умную таблицу" сочетанием клавиш Ctrl + T или кнопкой Форматировать как таблицу на вкладке Главная (Home - Format as Table) . Если по каким-то причинам вы не хотите или не можете использовать "умные таблицы", то можно работать и с "глупыми". В этом случае просто выделите исходный диапазон и дайте ему имя на вкладке Формулы - Диспетчер имен - Создать (Formulas - Name Manager - New) .
После этого на вкладке Данные (если у вас Excel 2016 или новее) или на вкладке Power Query (если у вас Excel 2010-2013) можно жать на кнопку Из таблицы / диапазона (From Table/Range) , чтобы загрузить нашу таблицу в редактор Power Query:
После загрузки выделим столбец с многострочным текстом в ячейках и выберем на Главной вкладке команду Разделить столбец - По разделителю (Home - Split Column - By delimiter) :
Чтобы всё разделилось на строки, а не не столбцы - не забудьте переключить селектор Строки (By rows) в группе расширенных параметров.
Останется только нажать на ОК и получить желаемое:
Готовую таблицу можно выгрузить обратно на лист с помощью команды Закрыть и загрузить - Закрыть и загрузить в. на вкладке Главная (Home - Close&Load - Close&Load to. ) .
Важно отметить, что при использовании Power Query необходимо помнить о том, что при изменении исходных данных результаты автоматически не обновляются, т.к. это не формулы. Для обновления нужно обязательно щёлкнуть правой кнопкой мыши по итоговой таблице на листе и выбрать команду Обновить (Refresh) или нажать кнопку Обновить всё на вкладке Данные (Data - Refresh All) .
Макрос для деления на строки по Alt+Enter
Для полноты картины давайте упомянем решение предыдущей задачи ещё и с помощью макроса. Откройте редактор Visual Basic с помощью одноимённой кнопки на вкладке Разрабочик (Developer) или сочетания клавиш Alt + F11 . В появившемся окне вставьте новый модуль через меню Insert - Module и скопируйте туда нижеприведённый код:
Вернитесь в Excel и выделите ячейки с многострочным текстом, который надо разделить. Затем воспользуйтесь кнопкой Макросы на вкладке Разработчик (Developer - Macros) или сочетанием клавиш Alt + F8 , чтобы запустить созданный макрос, который и проделает за вас всю работу:
Вуаля! Программисты - это, на самом деле, просто очень ленивые люди, которые лучше один раз как следует напрягутся, чтобы потом ничего не делать :)
Как быстро оценить прерывистый диапазон в Excel?
Ранжируйте несмежные ячейки с помощью формул
Здесь я представляю несколько формул для быстрого ранжирования несмежных ячеек в Excel.
Скопируйте любую из формул ниже, вставьте в ячейку, в которой вы хотите отобразить ранжирование, а затем перетащите дескриптор автоматического заполнения вниз к ячейкам, для которых нужна формула.
Вы можете видеть только те ячейки, которые вам были ранжированы.
В формуле D1 - это первая ячейка списка, который вы хотите ранжировать, D2, D4, D8 и D10 - это ячейки, которые вы хотите ранжировать.
Ранжируйте несмежные ячейки с помощью имени диапазона
Если для ранжирования необходимо несколько прерывистых диапазонов, приведенные выше формулы будут слишком длинными для применения. В этом случае вы можете присвоить диапазонам имя диапазона, а затем применить функцию ранжирования.
1. Держать Ctrl , чтобы выбрать все диапазоны, которые вы хотите ранжировать, перейдите к Поле имени, введите желаемое имя и нажмите клавишу Enter. Смотрите скриншот:
2. Затем перейдите в ячейку, в которую вы хотите поместить результат ранжирования, введите эту формулу = РАНГ (A1; ранг) (A1 - первая ячейка списка, который вы хотите ранжировать, Ранг - это имя диапазона, которое вы указываете), заполните автоматический дескриптор до ячеек, к которым вы хотите применить эту формулу. Теперь ранжированы только нужные вам ячейки.
Как объединить диапазон / ячейки с запятой, пробелом, разрывом строки в Excel?
В этой статье говорится о объединении строки / столбца в одну ячейку с разделением запятой или пробелом, а также о объединении диапазона в одну строку / столбец / ячейку с разделением содержимого каждой ячейки любыми разделителями, такими как запятая, пробел, разрыв строки и т. д. в Excel.
- Объедините строку с запятой / пробелом по формуле
- Объедините столбец с запятой / пробелом по формуле
- Объедините диапазон / строку / столбец с запятой / пробелом / разрывом строки
Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!
Объедините строку с запятой / пробелом по формуле
Например, у вас есть строка имен, как показано на скриншоте ниже, и вам нужно объединить эту строку в одну ячейку и разделить содержимое ячейки запятой или пробелом. Вы можете применить функцию СЦЕПИТЬ следующим образом:
1. Выберите пустую ячейку, в которую вы поместите результат конкатенации, и введите формулу. = СЦЕПИТЬ (B1: J1 & ",") внутрь.
2. Выделите B1: J1 & "," в формуле и нажмите F9 клавиша для замены ссылок на ячейки содержимым ячейки.
3. Снимите фигурные скобки. < и >из формулы и нажмите Enter ключ. Смотрите скриншот:
Внимание: В приведенной выше формуле B1: J1 - это строка, которую вы объедините, а «,» означает, что вы разделите содержимое каждой ячейки запятой. Для разделения пробелом замените в формуле "," на "".
Пакетное объединение нескольких ячеек / строк / столбцов без потери данных и формата даты / числа в Excel
Обычно при объединении ячеек с помощью функции «Слияние» в Excel удаляется все содержимое ячеек, кроме содержимого первой ячейки. Однако с Kutools for Excel's Объединить (строки и столбцы)утилита, вы можете легко объединить несколько ячеек / строк / столбцов без потери данных. Более того, эта утилита также поддерживает оставшийся исходный формат даты и числовые форматы в результатах комбинации. Полнофункциональная бесплатная 30-дневная пробная версия!
Объедините столбец с запятой / пробелом по формуле
Предположим, у вас есть столбец с именами, как показано на скриншоте ниже, вы также можете применить функцию СЦЕПИТЬ, чтобы объединить этот столбец / список в одну ячейку и разделить содержимое каждой ячейки запятой или пробелом в Excel.
1. Выберите пустую ячейку, в которую вы поместите результат конкатенации, и введите формулу. = СЦЕПИТЬ (ТРАНСПОРТ (A2: A16) & ",") внутрь.
2. Выделите ТРАНСПОРТ (A2: A16) & "," в формуле и нажмите F9 клавиша для замены ссылки на ячейку содержимым ячейки.
3. Снимите фигурные скобки. < и >из формулы и нажмите Enter ключ. Смотрите скриншот выше:
Внимание: В приведенной выше формуле A2: A16 - это столбец / список, который вы объедините, и »,« означает, что вы разделите содержимое каждой ячейки запятой. Для разделения пробелом замените »,« с «» в формуле.
Объедините диапазон / строку / столбец с запятой / пробелом / разрывом строки Kutools for Excel
Например, у вас есть диапазон, как показано на скриншоте ниже, вы можете легко объединить этот диапазон в один столбец, одну строку или одну ячейку с помощью Kutools for Excel's Сочетать утилита.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
1. Выберите диапазон, который вы хотите объединить, и нажмите Кутулс > Слияние и разделение> Объединение строк, столбцов или ячеек без потери данных. Смотрите скриншот:
2. В открывшемся диалоговом окне сделайте следующее:
(1) В Чтобы объединить выбранные ячейки в соответствии со следующими параметрами В разделе укажите вариант комбинации в зависимости от ваших потребностей. В нашем случае мы проверяем Объединить столбцы вариант;
(2) В Укажите разделитель раздел, отметьте разделитель по мере необходимости. В нашем случае мы проверяем Новая линия вариант;
(3) Укажите вариант из Поместите результаты в раскрывающийся список. В нашем случае мы выбираем Левая ячейка.
(4) В Доступные опции раздел, пожалуйста, проверьте Удалить содержимое объединенных ячеек вариант. Смотрите скриншот:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
3. Нажмите Ok кнопку.
Затем вы увидите, что выбранные столбцы объединены в один столбец, а ячейки в каждой строке объединены в первую ячейку этой строки. Смотрите скриншот:
Выберите Объединить ряды вариант в Чтобы объединить выбранные ячейки в соответствии со следующей опцией раздел:
Выберите опцию Объединить в одну ячейку в Чтобы объединить выбранные ячейки в соответствии со следующей опцией раздел:
Демонстрация: объединение диапазона / ячеек с запятой, пробелом, разрывом строки в Excel
Обычно вы можете нажать клавиши Alt + Enter, чтобы разделить содержимое ячейки на несколько строк внутри ячейки, но как вы можете отфильтровать все ячейки с этими символами разрыва строки в Excel?
Отфильтруйте все ячейки с разрывом строки или возвратом каретки с помощью функции фильтра
Следующие шаги могут помочь вам отфильтровать все ячейки, содержащие символы разрыва строки, сделайте следующее:
1. Дважды щелкните ячейку под данными столбца, которые вы хотите отфильтровать, а затем нажмите Alt + Enter клавиши для ввода одинарного разрыва строки, см. снимок экрана:
2. Затем щелкните первую ячейку столбца, который вы хотите отфильтровать, а затем щелкните Данные > Фильтр, затем щелкните стрелку раскрывающегося списка, чтобы выбрать Текстовые фильтры > Пользовательский фильтр, см. снимок экрана:
3. В выскочившем Пользовательский автофильтр диалоговом окне выберите содержит из раскрывающегося списка в Текстовая строка раздел, а затем выберите пустую строку, которая содержит разрыв строки, который вы создали в соседнем раскрывающемся списке, см. снимок экрана:
4. Потом выберите И, вариант и продолжайте выбирать не равно из второго раскрывающегося списка, затем снова выберите пустую строку из раскрывающегося списка, см. снимок экрана:
5. Нажмите OK кнопку, и все ячейки с символами разрыва строки были отфильтрованы, как показано на следующем снимке экрана:
Обработать выделенный диапазон ячеек, результат записать в смежный диапазон
Напишите в среде Excel макрос, который в выделенном диапазоне ячеек должен выполнять заданные.
Вставить диапазон с 1-го листа на 2-ой и удалить диапазон
Добрый день, всем. Посоветуйте каким образом сделать своеобразную кнопку отмены после вставки.
Сворачивание массива с суммированием значений
Добрый день! Прошу помощи с такой проблемой: Вообщем, если всё упростить, то имеется массив А.
Создать список уникальных наименований с суммированием количества макросом
напримкр у меня есть первая таблица. назовем ее сурье. там 2 столбца. Название и Количество.
Решение
Просто и изящно), спасибо
Добавлено через 48 минут
А как решить аналогичную задачу, но необходимо чтобы суммирование диапазонов начиналось:
1) с определенной строки и вниз
2) с определенной строки и вверх
Добавлено через 17 минут
Вопрос №3 решил так:
For Each iSource In [S:S].SpecialCells(xlCellTypeFormulas, xlNumbers).Areas
Добавлено через 54 минуты
4) как вставить не конечный результат вычисления, а формулу , производящую сложение диапазонов?
Что то вроде (неверный код с ошибкой):
А как решить аналогичную задачу, но необходимо чтобы суммирование диапазонов начиналось:
1) с определенной строки и вниз
2) с определенной строки и вверх
Вместо [S:S] пишите (n - номер строки)
1) range(cells(n,"S"),cells(rows.count,"S"))
2) range("S1",cells(n,"S"))
Ещё пара вариантов :
1) columns(19).rows(n & ":" & rows.count)
1) rows(n & ":" & rows.count).columns("s")
2) range("s1:s" & n)
Подскажите , как можно сгруппировать такие диапазоны (В самом эксель реализуемый через :Данные - Группа и структура - Группировать)
Пробовал как то так, не получается:
Подскажите , как можно сгруппировать такие диапазоны (В самом эксель реализуемый через :Данные - Группа и структура - Группировать)
Пробовал как то так, не получается:
Есть ли возможность в каждом из таких прерывающихся диапазонов в определенном столбце построчно (от первой до последней заполненной ячейки) произвести поиск определенного текста в ячейке и удалить его, оставляя остальную часть текста нетронутой?
выручили снова)
еще такая штука. вопрос не по текущей теме но да ладно,
у меня 2 формы (при запуске макроса запускается одна - здесь кликаем по одной из OptionButton (далее - OВ) , затем текущая закрывается и открывается другая форма со своими ОВ. Проблема в том, что при каждом повторном запуске происходит запоминание выбора - поэтому нажать второй раз на уже выбранный OB невозможно)) поэтому приходится закрывать форму и уже во второй раз круглая галочка изчезает и можно сделать выбор . это же касается и второй формы. где бы чтобы прописать в коде или в свойствах где ткнуть , чтобы при повторном запуске форм каждый ОВ был доступным для активации (а не был уже выбран) . Надеюсь понятно изложил )
Читайте также: