Excel таблица через запятую - Word и Excel - помощь в работе с программами

Содержание

Слова через запятую сделать списком в столбик и наоборот — 3 способа
Преобразование в программе Notepad++ с помощью регулярных выражений
Преобразование столбца в строку через запятую в Excel
Столбик через запятую и обратно онлайн
Список через запятую > в столбик
Столбик > список через запятую
Склеивание текста по условию
Способ 0. Формулой
Способ 1. Макрофункция склейки по одному условию
Способ 2. Сцепить текст по неточному условию
Способ 3. Макрофункция склейки текста по двум условиям
Способ 4. Группировка и склейка в Power Query
Excel таблицы через запятую

Слова через запятую сделать списком в столбик и наоборот — 3 способа

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

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

Преобразование в программе Notepad++ с помощью регулярных выражений

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

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

Для примера я взял набор фраз через запятую. Нажимаем комбинацию клавиш Ctrl+H (поиск с заменой), в открывшемся окне ставим метку Режим поиска — Расширенный, Найти: , (запятая и пробел), Заменить на: n Заменить все. Получаем список столбиком.

Если нужно из столбика сделать список через запятую, тогда то же самое, только Найти: rn и Заменить на: , (запятая и пробел).
Если что-то заменилось не так, как надо, делаем отмену Ctrl+Z и пробуем по-другому, например, ищем только n.

Преобразование столбца в строку через запятую в Excel

Используем формулу «лесенка».
Скачать готовый файл можно здесь: 4memo-ru-stolbik-zapyataya.xls
Запятую в самой первой ячейке можно заменить, на что-то другое, если нужно.
Формулу во втором столбце растягиваем вниз, насколько потребуется.
Если полученный список нужно вставить в другой лист эксель, то не забываем избавиться от формул, вставив предварительно данные в блокнот.

Столбик через запятую и обратно онлайн

Этот вариант подойдет для тех, кому нужно срочно, прямо на этой странице, решить задачу конвертации списка — за вас все сделает скрипт.

Список через запятую > в столбик

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

Столбик > список через запятую

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

Ничего не получилось

А что именно «не получилось»?

Б — это второй вариант с эксель?
Только что проверил, скачав файл, добавив данных в первый столбец и протянув формулу во втором столбце — все работает.
Напишите подробно, как Вы делали пошагово, тогда можно будет понять, где ошибка.

Все работает! Спасибо!
Все способы сверху даже не стал использовать. А зачем? Если есть то же самое, но он-лайн.
Давно искал. Сохраню страницу в рабочие закладки.
Еще раз Спасибо!

Пожалуйста! Рад, что Вам пригодилось.

Всем доброго времени суток! В первую очередь спасибо автору за труды! Реально на работе нужна и пригодилась функция «Список через запятую > в столбик», которая через скрипт на сайте. Вот только не всегда есть возможность зайти на Ваш сайт для этого. С готовым файлом «4memo-ru-stolbik-zapyataya.xls» я так и не понял как это реализовать. Все таки как это сделать в файле excel, чтобы было примерно как на сайте, в какую-нибудь ячейку ввел данные с запятыми, а excel автоматически или по нажатию кнопки распределил их в столбик, чтоб можно было скопировать и пр. Только уже готовый файл, если можно?

Этот файл предназначен для преобразования только из столбца в список через запятую (или другой разделитель).
Вашу задачу (список через запятую или другой разделитель > в столбик) можно решить в следующие два этапа:
1. Открываем чистый лист Excel, вставляем в первую ячейку ваш список, потом идем в меню Данные > Текст по столбцам, в открывшемся диалоговом окне выбираем Формат данных > С разделителями, жмем Далее, выбираем разделитель «запятая», жмем Далее и потом Готово. Все, что между запятыми должно распределиться по отдельным столбцам.
2. Теперь то, что у нас распределено по горизонтали в столбцах, нужно распределить по вертикали в строках. Для этого выделяем, все ячейки, в которых есть данные, либо целиком строку, копируем Ctrl+C, опускаем курсор в свободную ячейку ниже, клик правой кнопкой мыши и в контекстном меню выбираем: Специальная вставка > Транспонировать.
Чтобы все это срабатывало по нажатию кнопки, как Вы хотите, наверное можно написать какой-нибудь макрос, но я сейчас не готов этим заниматься.

Если Вы или кто-то еще напишет такой макрос, то я готов разместить файл с ним в статье с указанием авторства.

Источник

Склеивание текста по условию

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

Допустим, что у нас имеется база данных по клиентам, где одному названию компании может соответствовать несколько разных email’ов ее сотрудников. Наша задача состоит в том, чтобы собрать все адреса по названиям компаний и сцепить их (через запятую или точку с запятой), чтобы сделать потом, например, почтовую рассылку по клиентам, т.е. получить на выходе что-то похожее на:

Другими словами, нам нужен инструмент, который будет склеивать (сцеплять) текст по условию — аналог функции СУММЕСЛИ (SUMIF) , но для текста.

Способ 0. Формулой

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

Минусы такого подхода очевидны: из всех ячеек полученного дополнительного столбца нам нужны только последние по каждой компании (желтые). Если список большой, то чтобы их быстро отобрать придется добавить еще один столбец, использующий функцию ДЛСТР (LEN) , проверяющий длину накопленных строк:

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

Способ 1. Макрофункция склейки по одному условию

Если исходный список не отсортирован по компаниям, то приведенная выше простая формула не работает, но можно легко выкрутиться с помощью небольшой пользовательской функции на VBA. Откройте редактор Visual Basic нажатием на сочетание клавиш Alt+F11 или с помощью кнопки Visual Basic на вкладке Разработчик (Developer) . В открывшемся окне вставьте новый пустой модуль через меню Insert — Module и скопируйте туда текст нашей функции:

Если теперь вернуться в Microsoft Excel, то в списке функций (кнопка fx в строке формул или вкладка Формулы — Вставить функцию) можно будет найти нашу функцию MergeIf в категории Определенные пользователем (User Defined) . Аргументы у функции следующие:

Способ 2. Сцепить текст по неточному условию

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

Поддерживаются стандартные спецсимволы подстановки:

звездочка (*) — обозначает любое количество любых символов (в т.ч. и их отсутствие)
вопросительный знак (?) — обозначает один любой символ
решетка (#) — обозначает одну любую цифру (0-9)

По умолчанию оператор Like регистрочувствительный, т.е. понимает, например, «Орион» и «оРиОн» как разные компании. Чтобы не учитывать регистр можно добавить в самое начало модуля в редакторе Visual Basic строчку Option Compare Text, которая переключит Like в режим, когда он невосприимчив к регистру.

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

?1##??777RUS — выборка по всем автомобильным номерам 777 региона, начинающимся с 1
ООО* — все компании, название которых начинается на ООО
##7## — все товары с пятизначным цифровым кодом, где третья цифра 7
. — все названия из пяти букв и т.д.

Способ 3. Макрофункция склейки текста по двум условиям

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

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

Способ 4. Группировка и склейка в Power Query

Решить проблему можно и без программирования на VBA, если использовать бесплатную надстройку Power Query. Для Excel 2010-2013 ее можно скачать здесь, а в Excel 2016 она уже встроена по умолчанию. Последовательность действий будет следующей:

Power Query не умеет работать с обычными таблицами, поэтому первым шагом превратим нашу таблицу в «умную». Для этого ее нужно выделить и нажать сочетание Ctrl + T или выбрать на вкладке Главная — Форматировать как таблицу (Home — Format as Table) . На появившейся затем вкладке Конструктор (Design) можно задать имя таблицы (я оставил стандартное Таблица1):

Теперь загрузим нашу таблицу в надстройку Power Query. Для этого на вкладке Данные (если у вас Excel 2016) или на вкладке Power Query (если у вас Excel 2010-2013) жмем Из таблицы (Data — From Table) :

В открывшемся окне редактора запросов выделяем щелчком по заголовку столбец Компания и сверху жмем кнопку Группировать (Group By) . Вводим имя нового столбца и тип операции в группировке — Все строки (All Rows) :

Жмем ОК и получаем для каждой компании мини-таблицу сгруппированных значений. Содержимое таблиц хорошо видно, если щелкать левой кнопкой мыши в белый фон ячеек (не в текст!) в получившемся столбце:

Теперь добавим еще один столбец, где с помощью функции склеим через запятую содержимое столбцов Адрес в каждой из мини-таблиц. Для этого на вкладке Добавить столбец жмем Пользовательский столбец (Add column — Custom column) и в появившемся окне вводим имя нового столбца и формулу сцепки на встроенном в Power Query языке М:

Обратите внимание, что все М-функции регистрочувствительные (в отличие от Excel). После нажатия на ОК получаем новый столбец со склееными адресами:

Осталось удалить ненужный уже столбец ТаблАдресов (правой кнопкой мыши по заголовку — Удалить столбец) и выгрузить результаты на лист, нажав на вкладке Главная — Закрыть и загрузить (Home — Close and load) :

Важный нюанс : в отличие от предыдущих способов (функций), таблицы из Power Query не обновляются автоматически. Если в будущем произойдут какие-либо изменения в исходных данных, то нужно будет щелкнуть правой кнопкой в любое место таблицы результатов и выбрать команду Обновить (Refresh) .

Источник

Excel таблицы через запятую

Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Вывод значений через запятую из таблицы (Формулы/Formulas)

Вывод значений через запятую из таблицы

azZazZello

Дата: Среда, 15.07.2015, 07:18 | Сообщение № 1

Nic70y

Дата: Среда, 15.07.2015, 07:35 | Сообщение № 2

azZazZello

Дата: Среда, 15.07.2015, 07:42 | Сообщение № 3

azZazZello

Дата: Среда, 15.07.2015, 08:45 | Сообщение № 4

Nic70y

Дата: Среда, 15.07.2015, 09:14 | Сообщение № 5

ShAM

Дата: Среда, 15.07.2015, 09:52 | Сообщение № 6

azZazZello

Дата: Среда, 15.07.2015, 10:05 | Сообщение № 7

_Boroda_

Дата: Среда, 15.07.2015, 10:06 | Сообщение № 8

Ответить

Rioran

Дата: Среда, 15.07.2015, 10:07 | Сообщение № 9

Ради спортивного интереса предлагаю решение пользовательской функцией.

Во вложении пример использования. Макросы должны быть разрешены. Первые два аргумента должны быть столбцами, 3-й аргумент — проверочное имя. Аргументы функции:

Ради спортивного интереса предлагаю решение пользовательской функцией.

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279

Сообщение azZazZello, здравствуйте.

Ради спортивного интереса предлагаю решение пользовательской функцией.

sv2014

Дата: Среда, 15.07.2015, 11:15 | Сообщение № 10

azZazZello, добрый день,
могу предложить вам макрос,который решает ваш вопрос при любом количестве ваших данных
в столбце F

Сообщение azZazZello, добрый день,
могу предложить вам макрос,который решает ваш вопрос при любом количестве ваших данных
в столбце F

sv2014

Дата: Среда, 15.07.2015, 11:43 | Сообщение № 11

azZazZello, или такой же вариант с пробелом при необходимости.
На вашем файле- примере проверил.

Сообщение azZazZello, или такой же вариант с пробелом при необходимости.
На вашем файле- примере проверил.

Alekc7711

Дата: Суббота, 22.02.2020, 02:05 | Сообщение № 12

sv2014, здравствуйте.
Подскажите, что не так делаю?
Немного покрутил макрос, выпала ошибка
http://prntscr.com/r5xauo

Сообщение sv2014, здравствуйте.
Подскажите, что не так делаю?
Немного покрутил макрос, выпала ошибка
http://prntscr.com/r5xauo

Alekc7711

Дата: Суббота, 22.02.2020, 02:30 | Сообщение № 13

Pelena

Дата: Суббота, 22.02.2020, 08:04 | Сообщение № 14

	«Черт возьми, Холмс! Но как. » Ю-money 41001765434816

Ответить

solniechnaya

Дата: Воскресенье, 05.03.2023, 09:57 | Сообщение № 15

Друзья,
Не получается решить аналогичную задачу- где в виде итоговой таблицы нужна сводная.
В кликабельной ячейке сводной таблицы нужны не значения (например суммы), а номера строк перечисленных через запятую.

Пример во вложении

С Уважением.
Solniechnaya@gmail.com

Пример во вложении

С Уважением.
Solniechnaya@gmail.com solniechnaya

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

Пример во вложении

С Уважением.
Solniechnaya@gmail.com Автор — solniechnaya
Дата добавления — 05.03.2023 в 09:57

jakim

Дата: Воскресенье, 05.03.2023, 10:23 | Сообщение № 16

jakim

Дата: Воскресенье, 05.03.2023, 10:46 | Сообщение № 17

jakim

Дата: Воскресенье, 05.03.2023, 12:13 | Сообщение № 18

прохожий2019

Дата: Воскресенье, 05.03.2023, 12:52 | Сообщение № 19

хотя задачка, конечно, для DAX:

solniechnaya

Дата: Воскресенье, 05.03.2023, 21:30 | Сообщение № 20

прохожий2019, jakim,
Благодарю за предложенные варианты решения,
А всё же можно сделать с использованием кликабельной сводной таблицы?
Если мы дабл-кликнем в примере на жёлтую ячейку с числом «2» в сводной таблице, то откроется лист с детальной информацией.
также ожидается при дабл-клике в итоговой таблице на ячейку с «6, 9»

Сообщение прохожий2019, jakim,
Благодарю за предложенные варианты решения,
А всё же можно сделать с использованием кликабельной сводной таблицы?
Если мы дабл-кликнем в примере на жёлтую ячейку с числом «2» в сводной таблице, то откроется лист с детальной информацией.
также ожидается при дабл-клике в итоговой таблице на ячейку с «6, 9»

С надеждой, Автор — solniechnaya
Дата добавления — 05.03.2023 в 21:30

Источник

Adblock
detector

Источник

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

Преобразовать программе Notepad++
Cтолбец в строку Excel через запятую
Использовать Онлайн-скрипт

Содержание

Преобразование в программе Notepad++ с помощью регулярных выражений
Преобразование столбца в строку через запятую в Excel
Столбик через запятую и обратно онлайн
Список через запятую > в столбик
Столбик > список через запятую

Преобразование в программе Notepad++ с помощью регулярных выражений

Преобразование столбца в строку через запятую в Excel

Столбик через запятую и обратно онлайн

Список через запятую > в столбик

Столбик > список через запятую

Источник

New

Пользователь

Сообщений: 4581
Регистрация: 06.01.2013

1. Выделяете мышкой столбец А (или просто ячейки с вашими данными мышкой выделите)
2. меню Данные — Текст по столбцам
3. в появившемся окне выбираете — с разделителями, Далее, мышкой выбираете разделитель «Запятая» — Готово

P.S. Правда access hash, состоящий у вас из набора цифр больше чем 15 символов (на первый взгляд их 19 чисел) — потеряется, а именно все числа после 15-го числа будут сконвертированы в 0. Если же вам нужны все числа в access hash, то можно придумать другой способ преобразования

Изменено: New — 14.11.2020 16:38:04

Источник

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

Преобразование списка столбцов в список, разделенный запятыми, с помощью функции TEXTJOIN
Преобразование списка столбцов в список, разделенный запятыми, с помощью функции CONCATENATE
Преобразование списка столбцов в список, разделенный запятыми, с помощью VBA
Преобразовать список столбцов в список, разделенный запятыми, с помощью Kutools for Excel
Обратное объединение и преобразование одной ячейки (зазубренный список с запятыми) в список строк / столбцов в Excel

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

Функция Excel TEXTJOIN объединяет несколько значений из строки, столбца или диапазона ячеек с определенным разделителем.

Обратите внимание, что эта функция доступна только в Excel для Office 365, Excel 2021 и Excel 2019.

Чтобы преобразовать список столбцов в список, разделенный запятыми, выберите пустую ячейку, например, ячейку C1, и введите эту формулу =ТЕКСТСОЕДИНИТЬ(«, «,ИСТИНА,A1:A7) (A1: A7 это столбец, который вы преобразуете в список с запятыми, , указывает, как вы хотите разделить список). Смотрите скриншот ниже:

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

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

1. Выберите пустую ячейку рядом с первыми данными списка, например ячейкой C1, и введите эту формулу = СЦЕПИТЬ (ТРАНСПОРТ (A1: A7) & «,») (A1: A7 это столбец, который вы преобразуете в список с запятыми, »,« указывает разделитель, который вы хотите разделить список). Смотрите скриншоты ниже:

2. Выделите ТРАНСПОРТ (A1: A7) & «,» в формуле и нажмите F9 .

3. Убрать фигурные скобки {и } из формулы и нажмите Enter .

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

Быстро преобразовать список столбцов в список, разделенный запятыми, с помощью Kutools for Excel

Kutools for ExcelАвтора Комбинируйте столбцы или строки без потери данных Утилита может помочь пользователям Excel легко объединить несколько столбцов или строк в один столбец / строку без потери данных. Кроме того, пользователи Excel могут заключать в эти комбинированные текстовые строки каретку или жесткий возврат.

Преобразование списка столбцов в список, разделенный запятыми, с помощью VBA

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

1. Держать ALT и нажмите F11 на клавиатуре, чтобы открыть Microsoft Visual Basic для приложений окно.

2. Нажмите Вставить > Модули, и скопируйте VBA в модуль.

VBA: преобразовать список столбцов в список, разделенный запятыми

Sub ChangeRange()
'Updateby20140310
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
outStr = ""
For Each rng In InputRng
    If outStr = "" Then
        outStr = rng.Value
    Else
        outStr = outStr & "," & rng.Value
    End If
Next
OutRng.Value = outStr
End Sub

3. Нажмите Run или нажмите F5 для запуска VBA.

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

5. Нажмите OK, затем появилось еще одно диалоговое окно для выбора ячейки. Смотрите скриншот:

6. Нажмите OK, и все значения в списке столбцов были преобразованы в список, разделенный запятой в ячейке.

Функции: В приведенном выше VBA «,» указывает на нужный разделитель, и вы можете изменить его по своему усмотрению.

Преобразовать список столбцов в список, разделенный запятыми, с помощью Kutools for Excel

Вы также можете подать заявку Kutools for ExcelАвтора Сочетать утилита для объединения списка столбцов и простого разделения каждого значения запятой.

Kutools for Excel — Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная пробная версия 30-день, кредитная карта не требуется! Get It Now

1. Выберите список столбцов, который вы преобразуете в список, разделенный запятыми, и нажмите Кутулс > Слияние и разделение> Объединение строк, столбцов или ячеек без потери данных.

2. В открывшемся диалоговом окне Объединить столбцы или строки вам необходимо:
(1) Проверьте Объединить ряды вариант в Чтобы объединить выбранные ячейки в соответствии со следующими параметрами раздел;
(2) В Укажите разделитель раздел, проверьте Другой разделитель вариант и введите запятую , в следующее поле;

3, Нажмите Ok кнопку.

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

Демо: преобразование списка столбцов в список, разделенный запятыми в Excel

Обратное объединение и преобразование одной ячейки (зазубренный список с запятыми) в список строк / столбцов в Excel

Обычно пользователи Excel могут применять Текст в столбцы возможность разбить одну ячейку на несколько столбцов, но нет прямого метода преобразования одной ячейки в несколько строк. Однако, Kutools for ExcelАвтора Разделить клетки Утилита может помочь вам легко сделать это, как показано на скриншоте ниже.

Относительные статьи:

Преобразование названия месяца в число в Excel
Преобразование военного времени в стандартное время
Преобразование одной ячейки в несколько ячеек / строк в Excel

Источник

Вывод значений через запятую из таблицы

azZazZello

Дата: Среда, 15.07.2015, 07:18 |
Сообщение № 1

Группа: Пользователи

Ранг: Прохожий

Сообщений: 9

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Добрый день!
Есть задачка, вроде простая, но синтаксис никак не могу сопоставить правильно.
Суть такая: есть огромная таблица, в которой указаны МОЛы, которых ведут БУХгалтера (для примера сократил её, но искомое значение может повторятся много раз). Требуется получить результат через запятую в другой таблице.
Не могу понять как правильно из массива получить данные через запятую. ВПР выдает только 1 значение

Ответить

Nic70y

Дата: Среда, 15.07.2015, 07:35 |
Сообщение № 2

Группа: Друзья

Ранг: Экселист

Сообщений: 8132

Репутация:
1998
±

Замечаний:
0% ±

Excel 2010

может так сойдет?
массивная:

Код

=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$11;НАИМЕНЬШИЙ(ЕСЛИ($F2=$A$2:$A$11;СТРОКА($1:$10));СТОЛБЕЦ(A1)));»»)

ЮMoney 41001841029809

Ответить

azZazZello

Дата: Среда, 15.07.2015, 07:42 |
Сообщение № 3

Группа: Пользователи

Ранг: Прохожий

Сообщений: 9

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Да это просто праздник какой-то!!! Такой вариант тоже имеет право на жизнь!!! Если что, через СЦЕПИТЬ потом допинаю… Спасибо большое! hands

Ответить

azZazZello

Дата: Среда, 15.07.2015, 08:45 |
Сообщение № 4

Группа: Пользователи

Ранг: Прохожий

Сообщений: 9

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Чего то логику выражения понять не могу. Можете по пунктам разложить?
Непонятная часть выражения:

Код

НАИМЕНЬШИЙ(ЕСЛИ($F2=$A$2:$A$11;СТРОКА($1:$10));СТОЛБЕЦ(D1))

Ответить

Nic70y

Дата: Среда, 15.07.2015, 09:14 |
Сообщение № 5

Группа: Друзья

Ранг: Экселист

Сообщений: 8132

Репутация:
1998
±

Замечаний:
0% ±

Excel 2010

ЕСЛИ($F2=$A$2:$A$11 соот. строки массива на примере G2 это массив {ЛОЖЬ:ЛОЖЬ:3:4:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ}
НАИМЕНЬШИЙ( … СТОЛБЕЦ(A1))это наименьший 1 из массива = 3; H2 => СТОЛБЕЦ(B1) это наименьший 2 из массива = 4

ЮMoney 41001841029809

Сообщение отредактировал Nic70y — Среда, 15.07.2015, 09:15

Ответить

ShAM

Дата: Среда, 15.07.2015, 09:52 |
Сообщение № 6

Группа: Друзья

Ранг: Старожил

Сообщений: 1347

Репутация:
249
±

Замечаний:
0% ±

Excel 2010

Ответить

azZazZello

Дата: Среда, 15.07.2015, 10:05 |
Сообщение № 7

Группа: Пользователи

Ранг: Прохожий

Сообщений: 9

Репутация:
0
±

Замечаний:
0% ±

Excel 2010

Всё, разобрался. Всем спасибо!
Пошёл дальше читать матчасть deal

Ответить

_Boroda_

Дата: Среда, 15.07.2015, 10:06 |
Сообщение № 8

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Еще вариант немассивной формулой

Код

=ЕСЛИОШИБКА(ПРОСМОТР(;-1/($F2=$A$2:$A$11)/ЕНД(ПОИСКПОЗ($B$2:$B$11;$F2:F2;));$B$2:$B$11);»»)

Объяснялку смотрите здесь

Скажи мне, кудесник, любимец ба’гов…
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

Rioran

Дата: Среда, 15.07.2015, 10:07 |
Сообщение № 9

Группа: Авторы

Ранг: Ветеран

Сообщений: 903

Репутация:
290
±

Замечаний:
0% ±

Excel 2013

azZazZello, здравствуйте.

Ради спортивного интереса предлагаю решение пользовательской функцией.

Код

=СЦЕПИТЬЕСЛИ( [«Кого будем сцеплять»] ; [«Диапазон для проверки критерия»] ; [«Критерий»] )

Код функции:

[vba]

Код

Function СЦЕПИТЬЕСЛИ$(Data As Range, Condition As Range, Target$)
      Dim i As Long, j As Long, ArrX() As String, rNum As Long
      rNum = Data.Rows.Count
      If rNum <> Condition.Rows.Count Then
          СЦЕПИТЬЕСЛИ = «- Укажите равные диапазоны -»
          Exit Function
      End If
      ReDim ArrX(rNum)
      For i = 1 To rNum
          If Condition.Cells(i, 1).Value = Target Then
              ArrX(j) = Data.Cells(i, 1).Value
              j = j + 1
          End If
      Next i
      ReDim Preserve ArrX(j — 1)
      СЦЕПИТЬЕСЛИ = Join(ArrX, «, «)
End Function

[/vba]

Роман, Москва, voronov_rv@mail.ru
Яндекс-Деньги: 41001312674279

Сообщение отредактировал Rioran — Среда, 15.07.2015, 10:10

Ответить

sv2014

Дата: Среда, 15.07.2015, 11:15 |
Сообщение № 10

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 226

Репутация:
61
±

Замечаний:
0% ±

Excel 2013

[vba]

Код

Sub incert()
   Dim i&, j&, n&, s$
  n = Range(«F» & Cells.Rows.Count).End(xlUp).Row
For j = 2 To n
    s = «»
    For i = 2 To 11
      If Range(«A» & i) = Range(«F» & j) Then
          s = s & «,» & Range(«B» & i)
      End If
    Next i
     Range(«G» & j) = Right(s, Len(s) — 1)
    Next j
  End Sub

[/vba]

Ответить

sv2014

Дата: Среда, 15.07.2015, 11:43 |
Сообщение № 11

Группа: Проверенные

Ранг: Форумчанин

Сообщений: 226

Репутация:
61
±

Замечаний:
0% ±

Excel 2013

azZazZello, или такой же вариант с пробелом при необходимости.
На вашем файле- примере проверил.

[vba]

Код

Sub incert1()
   Dim i&, j&, n&, s$
  n = Range(«F» & Cells.Rows.Count).End(xlUp).Row
For j = 2 To n
    s = «»
    For i = 2 To 11
      If Range(«A» & i) = Range(«F» & j) Then
          s = s & «,» & Chr(32) & Range(«B» & i)
      End If
    Next i
     Range(«G» & j) = Right(s, Len(s) — 1)
    Next j
  End Sub

[/vba]

Ответить

Alekc7711

Дата: Суббота, 22.02.2020, 02:05 |
Сообщение № 12

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация:
0
±

Замечаний:
0% ±

Excel 2019

Ответить

Alekc7711

Дата: Суббота, 22.02.2020, 02:30 |
Сообщение № 13

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация:
0
±

Замечаний:
0% ±

Excel 2019

Излишнее цитирование удалено. Нарушение Правил форума
Что нужно прописать, чтобы перебор не останавливался, а пропускал ячейку ?

Сообщение отредактировал Pelena — Суббота, 22.02.2020, 08:05

Ответить

Pelena

Дата: Суббота, 22.02.2020, 08:04 |
Сообщение № 14

Группа: Админы

Ранг: Местный житель

Сообщений: 18797

Репутация:
4284
±

Замечаний:
±

Excel 2016 & Mac Excel

Попробуйте в этой строке поставить проверку
[vba]

Код

If Len(s)>1 Then Range(«G» & j) = Right(s, Len(s) — 1)

[/vba]

«Черт возьми, Холмс! Но как??!!»
Ю-money 41001765434816

Ответить

solniechnaya

Дата: Воскресенье, 05.03.2023, 09:57 |
Сообщение № 15

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация:
0
±

Замечаний:
0% ±

Пример во вложении

С Уважением…
Solniechnaya@gmail.com

Ответить

jakim

Дата: Воскресенье, 05.03.2023, 10:23 |
Сообщение № 16

Группа: Друзья

Ранг: Старожил

Сообщений: 1149

Репутация:
305
±

Замечаний:
0% ±

Excel 2010

Power Query

[vba]

Код

let
    Source = Excel.CurrentWorkbook(){[Name=»Table1″]}[Content],
    #»Grouped Rows» = Table.Group(Source, {«mol»}, {{«Count», each _, type table}}),
    #»Added Custom» = Table.AddColumn(#»Grouped Rows», «Custom», each [Count][buh]),
    #»Removed Columns» = Table.RemoveColumns(#»Added Custom»,{«Count»}),
    #»Extracted Values» = Table.TransformColumns(#»Removed Columns», {«Custom», each Text.Combine(List.Transform(_, Text.From), «, «), type text})
in
    #»Extracted Values»

[/vba]

К сообщению приложен файл:

6945041.xlsx
(16.5 Kb)

Ответить

jakim

Дата: Воскресенье, 05.03.2023, 10:46 |
Сообщение № 17

Группа: Друзья

Ранг: Старожил

Сообщений: 1149

Репутация:
305
±

Замечаний:
0% ±

Excel 2010

Исправляю ошибку, т.к. мой ответ был сделан по первому посту.

[vba]

Код

let
    Source = Excel.CurrentWorkbook(){[Name=»Table1″]}[Content],
    #»Changed Type» = Table.TransformColumnTypes(Source,{{«ном-стр», Int64.Type}, {«mol», type text}, {«buh», type text}}),
    #»Grouped Rows» = Table.Group(#»Changed Type», {«mol», «buh»}, {{«Count», each Table.RowCount(_), type number}}),
    #»Pivoted Column» = Table.Pivot(#»Grouped Rows», List.Distinct(#»Grouped Rows»[buh]), «buh», «Count»)
in
    #»Pivoted Column»

[/vba]

Ответить

jakim

Дата: Воскресенье, 05.03.2023, 12:13 |
Сообщение № 18

Группа: Друзья

Ранг: Старожил

Сообщений: 1149

Репутация:
305
±

Замечаний:
0% ±

Excel 2010

Последний вариант

[vba]

Код

let
    Source = Excel.CurrentWorkbook(){[Name=»Table1″]}[Content],
    #»Grouped Rows» = Table.Group(Source, {«mol», «buh»}, {{«Count», each _, type table}}),
    #»Added Custom» = Table.AddColumn(#»Grouped Rows», «Custom», each [Count][номстр]),
    #»Removed Columns» = Table.RemoveColumns(#»Added Custom»,{«Count»}),
    #»Extracted Values» = Table.TransformColumns(#»Removed Columns», {«Custom», each Text.Combine(List.Transform(_, Text.From), «, «), type text}),
    #»Pivoted Column» = Table.Pivot(#»Extracted Values», List.Distinct(#»Extracted Values»[buh]), «buh», «Custom»)
in
    #»Pivoted Column»

[/vba]

К сообщению приложен файл:

1724779.xlsx
(22.4 Kb)

Ответить

прохожий2019

Дата: Воскресенье, 05.03.2023, 12:52 |
Сообщение № 19

Группа: Проверенные

Ранг: Старожил

Сообщений: 1037

Репутация:
292
±

Замечаний:
0% ±

365 Beta Channel

[vba]

Код

let
    from = Excel.CurrentWorkbook(){[Name=»Table1″]}[Content],
    gr = Table.Group(from, {«mol», «buh»}, {«tmp», (t)=>Text.Combine(List.Transform(t[номстр],Text.From),», «)}),
    to = Table.Pivot(gr, List.Distinct(gr[buh]), «buh», «tmp»)
in
    to

[/vba]

хотя задачка, конечно, для DAX:

Код

=CONCATENATEX(‘Table1’;’Table1′[номстр];», «)

К сообщению приложен файл:

12345.xlsx
(94.7 Kb)

Сообщение отредактировал прохожий2019 — Воскресенье, 05.03.2023, 13:06

Ответить

solniechnaya

Дата: Воскресенье, 05.03.2023, 21:30 |
Сообщение № 20

Группа: Пользователи

Ранг: Прохожий

Сообщений: 2

Репутация:
0
±

Замечаний:
0% ±

С надеждой,

Ответить

Источник

Слова через запятую сделать списком в столбик и наоборот — 3 способа

Преобразование в программе Notepad++ с помощью регулярных выражений

Преобразование столбца в строку через запятую в Excel

Столбик через запятую и обратно онлайн

Список через запятую > в столбик

Столбик > список через запятую

Склеивание текста по условию

Способ 0. Формулой

Способ 1. Макрофункция склейки по одному условию

Способ 2. Сцепить текст по неточному условию

Способ 3. Макрофункция склейки текста по двум условиям

Способ 4. Группировка и склейка в Power Query

Excel таблицы через запятую

Преобразование в программе Notepad++ с помощью регулярных выражений

Преобразование столбца в строку через запятую в Excel

Столбик через запятую и обратно онлайн

Список через запятую > в столбик

Столбик > список через запятую

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

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

Быстро преобразовать список столбцов в список, разделенный запятыми, с помощью Kutools for Excel

Преобразование списка столбцов в список, разделенный запятыми, с помощью VBA

Преобразовать список столбцов в список, разделенный запятыми, с помощью Kutools for Excel

Демо: преобразование списка столбцов в список, разделенный запятыми в Excel

Обратное объединение и преобразование одной ячейки (зазубренный список с запятыми) в список строк / столбцов в Excel

Относительные статьи:

А вот еще интересные статьи: