Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Преобразование массива в список без пробелов и повторов - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Преобразование массива в список без пробелов и повторов (Формулы/Formulas)
Преобразование массива в список без пробелов и повторов
Ali_Stanov Дата: Понедельник, 30.05.2016, 20:53 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Добрый день!
Очень нужно решение с помощью формул.
Задача: преобразование массива в список без пробелов (или занчений 0) и повторений.
2 последовательные (почти однотипные задачи):
1. Из жёлтых ячеек (G6:G30) и разреженного и повторяющегося списка надо создать соответствующий список (выделен зелёным- H6:H30) таким образом, чтобы в него вошли только уникальные значения и без пропусков (или значений 0). В самом идеальном варианте еще и отсортировать автоматически по алфавиту. Подобная формула затем копируется в последуюшие блоки жёлтых и зелёных массивов (они нам нужны для решения задачи 2).
2. Из всех зелёных областей (их будет достаточно много) создаётся массив и этот массив нужно по тому же принципу (уникальность значений без пропусков и значений 0) как и в задаче 1 превратить в список B37:B75 (выделен синим цветом).
Заранее спасибо за помощь!
Уже более недели бьюсь над формулой и все взятые где-либо примеры не удалось применить к своей таблице.
P.S. Вариант с применением фильтра и сортировки вручную частично решает первую задачу (вторую никак), но проблема в том, жёлтые списки подгружаются через ИНДЕКС из другой таблицы и постоянно меняются и добавляются новые. Поэтому очень нужна формула, которая бы решала эту задачу автоматически.
К сообщению приложен файл: ____.xls(96Kb)


Сообщение отредактировал Ali_Stanov - Понедельник, 30.05.2016, 20:54
 
Ответить
СообщениеДобрый день!
Очень нужно решение с помощью формул.
Задача: преобразование массива в список без пробелов (или занчений 0) и повторений.
2 последовательные (почти однотипные задачи):
1. Из жёлтых ячеек (G6:G30) и разреженного и повторяющегося списка надо создать соответствующий список (выделен зелёным- H6:H30) таким образом, чтобы в него вошли только уникальные значения и без пропусков (или значений 0). В самом идеальном варианте еще и отсортировать автоматически по алфавиту. Подобная формула затем копируется в последуюшие блоки жёлтых и зелёных массивов (они нам нужны для решения задачи 2).
2. Из всех зелёных областей (их будет достаточно много) создаётся массив и этот массив нужно по тому же принципу (уникальность значений без пропусков и значений 0) как и в задаче 1 превратить в список B37:B75 (выделен синим цветом).
Заранее спасибо за помощь!
Уже более недели бьюсь над формулой и все взятые где-либо примеры не удалось применить к своей таблице.
P.S. Вариант с применением фильтра и сортировки вручную частично решает первую задачу (вторую никак), но проблема в том, жёлтые списки подгружаются через ИНДЕКС из другой таблицы и постоянно меняются и добавляются новые. Поэтому очень нужна формула, которая бы решала эту задачу автоматически.

Автор - Ali_Stanov
Дата добавления - 30.05.2016 в 20:53
Ali_Stanov Дата: Понедельник, 30.05.2016, 21:28 | Сообщение № 2
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Наиболее близко к решению 1 задачи я подошел с помощью следующей формулы (без сортировки по имени и без отсева значения "0")
Код
=ЕСЛИОШИБКА(ИНДЕКС($G$2:$G$2500;НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($G$2:$G$2500)=ЛОЖЬ;ЕСЛИ(ПОИСКПОЗ($G$2:$G$2500;$G$2:$G$2500;0)=СТРОКА($1:$2499);ПОИСКПОЗ($G$2:$G$2500;$G$2:$G$2500;0);"");"");СТРОКА()-1);1);"")

(массив)
Но кроме вышесказанной проблемы (сортировки и значения "0"), главная- при добавлении сверху строк (а вид как в первом примере крайне необходим) формула перестаёт работать, а корректировки не дают результата.
[moder]Формулы надо оформлять тегами (кнопка fx). На первый раз поправила[/moder] Спасибо, модератор, буду знать!
К сообщению приложен файл: 111.xls(98Kb)


Сообщение отредактировал Ali_Stanov - Понедельник, 30.05.2016, 23:57
 
Ответить
СообщениеНаиболее близко к решению 1 задачи я подошел с помощью следующей формулы (без сортировки по имени и без отсева значения "0")
Код
=ЕСЛИОШИБКА(ИНДЕКС($G$2:$G$2500;НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($G$2:$G$2500)=ЛОЖЬ;ЕСЛИ(ПОИСКПОЗ($G$2:$G$2500;$G$2:$G$2500;0)=СТРОКА($1:$2499);ПОИСКПОЗ($G$2:$G$2500;$G$2:$G$2500;0);"");"");СТРОКА()-1);1);"")

(массив)
Но кроме вышесказанной проблемы (сортировки и значения "0"), главная- при добавлении сверху строк (а вид как в первом примере крайне необходим) формула перестаёт работать, а корректировки не дают результата.
[moder]Формулы надо оформлять тегами (кнопка fx). На первый раз поправила[/moder] Спасибо, модератор, буду знать!

Автор - Ali_Stanov
Дата добавления - 30.05.2016 в 21:28
gling Дата: Понедельник, 30.05.2016, 21:50 | Сообщение № 3
Группа: Друзья
Ранг: Старожил
Сообщений: 1352
Репутация: 326 ±
Замечаний: 0% ±

2010
Уже более недели бьюсь над формулой
Здравствуйте. А может быть попробовать консолидацией, результат получится как в файле в столбцах A:D, далее это можно фильтровать. В ы же не написали какой конечный результат, поэтому предположил что данные из столбцов Пришло и Ушло должны суммироваться. Если нет, то столбцы C и D моно очистить, останутся только фамилии.
К сообщению приложен файл: 7039676.xls(96Kb)


Сообщение отредактировал gling - Понедельник, 30.05.2016, 21:52
 
Ответить
Сообщение
Уже более недели бьюсь над формулой
Здравствуйте. А может быть попробовать консолидацией, результат получится как в файле в столбцах A:D, далее это можно фильтровать. В ы же не написали какой конечный результат, поэтому предположил что данные из столбцов Пришло и Ушло должны суммироваться. Если нет, то столбцы C и D моно очистить, останутся только фамилии.

Автор - gling
Дата добавления - 30.05.2016 в 21:50
Ali_Stanov Дата: Понедельник, 30.05.2016, 23:55 | Сообщение № 4
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
gling, здраствуйте.
"Пришло" и "Ушло" это сравнение зеленых списков между собой на наличие тех или иных имен в списках справа и слева, для дальнейшего условного форматирования цветом появившихся или исчезнувших имен (в общем, это не является проблемой и давно решено- там нет формул, чтобы файл меньше весил, а то в 100кБ никак не влезть :) )
Цель искомых формул в том, чтобы на основании массива выводить уникальный список без пропусков сперва в зелёных ячейках на основании соответствующих жёлтых, а потом на основании зелёных формировать синий список (тоже уникальный и без пропусков)...
И если с первой задачей я в принципе готов справляться в ручном режиме фильтрами и сортировкой (если не получается найти решение формулой), то делать это вручную во втором случае (перевод данных из зелёных столбцов в синий список) кажется проблематичным, т.к. количество зелёных столбцов будет постоянно расти. Но что-то мне подсказывает, что при решении любой из этих задач, другая реашается автоматом.
Прикладываю файл с как это должно выглядеть в результате ввода формул.
[p.s.]Спасибо, с консолидацией я ещё ни разу не работал- попробую поизучать возможности. Но беглый осмотр этой функции мне подсказывает, что это не сможет решить мою проблему в автоматическом режиме, т.к. данные будут постоянно обновляться и увеличиваться. Если я не прав, поправьте меня!
К сообщению приложен файл: Res.xls(31Kb)


Сообщение отредактировал Ali_Stanov - Вторник, 31.05.2016, 00:04
 
Ответить
Сообщениеgling, здраствуйте.
"Пришло" и "Ушло" это сравнение зеленых списков между собой на наличие тех или иных имен в списках справа и слева, для дальнейшего условного форматирования цветом появившихся или исчезнувших имен (в общем, это не является проблемой и давно решено- там нет формул, чтобы файл меньше весил, а то в 100кБ никак не влезть :) )
Цель искомых формул в том, чтобы на основании массива выводить уникальный список без пропусков сперва в зелёных ячейках на основании соответствующих жёлтых, а потом на основании зелёных формировать синий список (тоже уникальный и без пропусков)...
И если с первой задачей я в принципе готов справляться в ручном режиме фильтрами и сортировкой (если не получается найти решение формулой), то делать это вручную во втором случае (перевод данных из зелёных столбцов в синий список) кажется проблематичным, т.к. количество зелёных столбцов будет постоянно расти. Но что-то мне подсказывает, что при решении любой из этих задач, другая реашается автоматом.
Прикладываю файл с как это должно выглядеть в результате ввода формул.
[p.s.]Спасибо, с консолидацией я ещё ни разу не работал- попробую поизучать возможности. Но беглый осмотр этой функции мне подсказывает, что это не сможет решить мою проблему в автоматическом режиме, т.к. данные будут постоянно обновляться и увеличиваться. Если я не прав, поправьте меня!

Автор - Ali_Stanov
Дата добавления - 30.05.2016 в 23:55
gling Дата: Вторник, 31.05.2016, 00:37 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1352
Репутация: 326 ±
Замечаний: 0% ±

2010
Если я не прав, поправьте меня!
Диапазоны консолидации взяты с запасом, поэтому при добавлении новых данных в таблицы достаточно активировать ячейку--нажать кнопку консолидации --ОК. Это конечно не автоматом, но думаю что можно записать эту процедуру макрорекордером и повесить её на активацию листа.
А в общем то не понятен смысл сбора в зеленом поле, если сразу можно собрать в синем. А для условного форматирования и подсчета Пришло - Ушло не обязательно собирать в кучу в зеленом поле. Зачем создавать еще столбец если можно обойтись без него?
 
Ответить
Сообщение
Если я не прав, поправьте меня!
Диапазоны консолидации взяты с запасом, поэтому при добавлении новых данных в таблицы достаточно активировать ячейку--нажать кнопку консолидации --ОК. Это конечно не автоматом, но думаю что можно записать эту процедуру макрорекордером и повесить её на активацию листа.
А в общем то не понятен смысл сбора в зеленом поле, если сразу можно собрать в синем. А для условного форматирования и подсчета Пришло - Ушло не обязательно собирать в кучу в зеленом поле. Зачем создавать еще столбец если можно обойтись без него?

Автор - gling
Дата добавления - 31.05.2016 в 00:37
Ali_Stanov Дата: Вторник, 31.05.2016, 03:42 | Сообщение № 6
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
gling,
Если вдаваться в подробности (отвечая на вопрос "Зачем"):
Зелёные поля- это помесячный список лидов (он подгружается помесячно из другого листа (точней- подгружается в жёлтый, но там много дублей и пропусков, для этого и создан зелёный), куда в онлайн-режиме вводится покупка лидом абонемента)- я хочу знать, сколько их было в этом месяце, сколько пришло новых и сколько ушло, а также: кто конкретно пришёл и ушёл для своевременных действий по возвращению клиентов или дополнительному вовлечению новых (у меня танцевальная студия). Также на основании этих данных я увижу среднее количество лидов в месяц и показатели текучести.
Голубое поле- сводные данные по каждому лиду: уникальное значение (ФИО, телефон и т.п.) и сколько месяцев он является активным (будет решаться в соседнем столбце путем подсчёта, сколько раз данный лид фигурирует в массиве всех зелёных столбцов). На основании этих данных я увижу продолжительность активности каждого лида конкретно, а также общие показатели, какое количество лидов активны в течении определенного количества месяцев, процентные соотнешения, себестоимость привлечения нового лида и т.п.
С Пришёл-Ушёл вопросов не возникает- уже введены формулы, решающие поставленные задачи с подсчётом новых и исчезнувших в том или ином месяце лидов в зелёном списке и необходимой информативности о них с помощью условного форматирования.
Так что мне нужны и зелёные поля (для оперативного учёта) и голубое (для общей статистики).
Если действительно с помощью Консолидации и (как вариант) обновления её кнопкой ОК можно выводить из массива (особенно разреженного во втором варианте) уникальный список- расскажите, пожалуйста, а то, как я сказал выше, о существоавнии её узнал только что от Вас.
Буду очень благодарен!:)


Сообщение отредактировал Ali_Stanov - Вторник, 31.05.2016, 03:48
 
Ответить
Сообщениеgling,
Если вдаваться в подробности (отвечая на вопрос "Зачем"):
Зелёные поля- это помесячный список лидов (он подгружается помесячно из другого листа (точней- подгружается в жёлтый, но там много дублей и пропусков, для этого и создан зелёный), куда в онлайн-режиме вводится покупка лидом абонемента)- я хочу знать, сколько их было в этом месяце, сколько пришло новых и сколько ушло, а также: кто конкретно пришёл и ушёл для своевременных действий по возвращению клиентов или дополнительному вовлечению новых (у меня танцевальная студия). Также на основании этих данных я увижу среднее количество лидов в месяц и показатели текучести.
Голубое поле- сводные данные по каждому лиду: уникальное значение (ФИО, телефон и т.п.) и сколько месяцев он является активным (будет решаться в соседнем столбце путем подсчёта, сколько раз данный лид фигурирует в массиве всех зелёных столбцов). На основании этих данных я увижу продолжительность активности каждого лида конкретно, а также общие показатели, какое количество лидов активны в течении определенного количества месяцев, процентные соотнешения, себестоимость привлечения нового лида и т.п.
С Пришёл-Ушёл вопросов не возникает- уже введены формулы, решающие поставленные задачи с подсчётом новых и исчезнувших в том или ином месяце лидов в зелёном списке и необходимой информативности о них с помощью условного форматирования.
Так что мне нужны и зелёные поля (для оперативного учёта) и голубое (для общей статистики).
Если действительно с помощью Консолидации и (как вариант) обновления её кнопкой ОК можно выводить из массива (особенно разреженного во втором варианте) уникальный список- расскажите, пожалуйста, а то, как я сказал выше, о существоавнии её узнал только что от Вас.
Буду очень благодарен!:)

Автор - Ali_Stanov
Дата добавления - 31.05.2016 в 03:42
Pelena Дата: Вторник, 31.05.2016, 06:20 | Сообщение № 7
Группа: Модераторы
Ранг: Экселист
Сообщений: 9871
Репутация: 2263 ±
Замечаний: 0% ±

Excel 2010 & Mac Excel 2011
А такой вариант: данные подгружаем не в новые столбцы, а в новые строки с указанием в отдельном столбце месяца. Получаем плоскую таблицу, на основе которой строим какие угодно сводные
К сообщению приложен файл: 9365840.xls(66Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеА такой вариант: данные подгружаем не в новые столбцы, а в новые строки с указанием в отдельном столбце месяца. Получаем плоскую таблицу, на основе которой строим какие угодно сводные

Автор - Pelena
Дата добавления - 31.05.2016 в 06:20
_Boroda_ Дата: Вторник, 31.05.2016, 11:51 | Сообщение № 8
Группа: Модераторы
Ранг: Экселист
Сообщений: 9375
Репутация: 3948 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Формула уникальных по алфавиту без нулей и пустых
Код
=ЕСЛИОШИБКА(ИНДЕКС(G$6:G$30;ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(G$6:G$30;"<"&ЕСЛИ(ПОИСКПОЗ(G$6:G$30;G$6:G$30;)=СТРОКА(H$1:H$25)*(G$6:G$30<>0)*(G$6:G$30<>"");G$6:G$30;"яяя"));СТРОКА(H1));ЕСЛИ((G$6:G$30<>0)*(G$6:G$30<>"");СЧЁТЕСЛИ(G$6:G$30;"<"&G$6:G$30);-1);));"")

Формула массива. Вводится одновременным нажатием Контрл Шифт Ентер
А синие можно сделать сводной таблицей с несколькими диапазонами консолидации. В файле сделано на 999 строк
Кстати, зеленые можно тоже сводными сделать. МИнус - каждый раз обновлять нужно или вручную, или макросом.
К сообщению приложен файл: 86786.xlsx(32Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеФормула уникальных по алфавиту без нулей и пустых
Код
=ЕСЛИОШИБКА(ИНДЕКС(G$6:G$30;ПОИСКПОЗ(НАИМЕНЬШИЙ(СЧЁТЕСЛИ(G$6:G$30;"<"&ЕСЛИ(ПОИСКПОЗ(G$6:G$30;G$6:G$30;)=СТРОКА(H$1:H$25)*(G$6:G$30<>0)*(G$6:G$30<>"");G$6:G$30;"яяя"));СТРОКА(H1));ЕСЛИ((G$6:G$30<>0)*(G$6:G$30<>"");СЧЁТЕСЛИ(G$6:G$30;"<"&G$6:G$30);-1);));"")

Формула массива. Вводится одновременным нажатием Контрл Шифт Ентер
А синие можно сделать сводной таблицей с несколькими диапазонами консолидации. В файле сделано на 999 строк
Кстати, зеленые можно тоже сводными сделать. МИнус - каждый раз обновлять нужно или вручную, или макросом.

Автор - _Boroda_
Дата добавления - 31.05.2016 в 11:51
Ali_Stanov Дата: Вторник, 31.05.2016, 15:48 | Сообщение № 9
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Pelena, спасибо огромное!
Вариант со сводными таблицами я не рассматривал... Этак ведь можно и не перегружать и до того перегруженный формулами файл, а просто брать из исходника (есть исходная постоянно заполняемая таблица, которая в оригинале и подгружалась в жёлтые столбцы. И эта таблица как раз плоская...). Если ещё вывести некое общее обновление всех сводных на этом листе одним нажатием, то это будет прям идеально!
Пошёл ваять сводные, надеюсь осилю:)
 
Ответить
СообщениеPelena, спасибо огромное!
Вариант со сводными таблицами я не рассматривал... Этак ведь можно и не перегружать и до того перегруженный формулами файл, а просто брать из исходника (есть исходная постоянно заполняемая таблица, которая в оригинале и подгружалась в жёлтые столбцы. И эта таблица как раз плоская...). Если ещё вывести некое общее обновление всех сводных на этом листе одним нажатием, то это будет прям идеально!
Пошёл ваять сводные, надеюсь осилю:)

Автор - Ali_Stanov
Дата добавления - 31.05.2016 в 15:48
_Boroda_ Дата: Вторник, 31.05.2016, 15:54 | Сообщение № 10
Группа: Модераторы
Ранг: Экселист
Сообщений: 9375
Репутация: 3948 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Ну вот! А я формулу писал!

общее обновление всех сводных на этом листе одним нажатием

Простенький макрос повесьте на кнопочку
[vba]
Код
Sub eee()
    ActiveWorkbook.RefreshAll
End Sub
[/vba]


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

общее обновление всех сводных на этом листе одним нажатием

Простенький макрос повесьте на кнопочку
[vba]
Код
Sub eee()
    ActiveWorkbook.RefreshAll
End Sub
[/vba]

Автор - _Boroda_
Дата добавления - 31.05.2016 в 15:54
Ali_Stanov Дата: Вторник, 31.05.2016, 16:00 | Сообщение № 11
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
_Boroda_, спасибо, формула работает!
Я так понял, что подобная формула не будет работать, если вместо исходного массива в виде 1 столбца использовать массив из 30-40-50... разрозненных столбцов (типа как я изначально хотел). Думается, эксель перейдёт в постоянное висение)))
Попробую альтернативный вариант со сводными таблицами из исходника на другом листе. Или приду к варианту совмещения Вашей формулы со сводными в синий диапозон, если не получу достаточное количество сводных для текущего анализа помесячно!
Ещё раз спасибо!
 
Ответить
Сообщение_Boroda_, спасибо, формула работает!
Я так понял, что подобная формула не будет работать, если вместо исходного массива в виде 1 столбца использовать массив из 30-40-50... разрозненных столбцов (типа как я изначально хотел). Думается, эксель перейдёт в постоянное висение)))
Попробую альтернативный вариант со сводными таблицами из исходника на другом листе. Или приду к варианту совмещения Вашей формулы со сводными в синий диапозон, если не получу достаточное количество сводных для текущего анализа помесячно!
Ещё раз спасибо!

Автор - Ali_Stanov
Дата добавления - 31.05.2016 в 16:00
Ali_Stanov Дата: Вторник, 31.05.2016, 16:04 | Сообщение № 12
Группа: Пользователи
Ранг: Прохожий
Сообщений: 9
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Ну вот! А я формулу писал!

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

Лишним не будет, у меня такое адское количество листов аналитики, что ещё пригодится и не раз. К тому же, возможно без её использования и эту задачу решить не получится (всё зависит от возможностей пока что малоизвестных для меня сводных таблиц).
За макрос отдельное!

Автор - Ali_Stanov
Дата добавления - 31.05.2016 в 16:04
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Преобразование массива в список без пробелов и повторов (Формулы/Formulas)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2016 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!