Добрый день! Очень нужно решение с помощью формул. Задача: преобразование массива в список без пробелов (или занчений 0) и повторений. 2 последовательные (почти однотипные задачи): 1. Из жёлтых ячеек (G6:G30) и разреженного и повторяющегося списка надо создать соответствующий список (выделен зелёным- H6:H30) таким образом, чтобы в него вошли только уникальные значения и без пропусков (или значений 0). В самом идеальном варианте еще и отсортировать автоматически по алфавиту. Подобная формула затем копируется в последуюшие блоки жёлтых и зелёных массивов (они нам нужны для решения задачи 2). 2. Из всех зелёных областей (их будет достаточно много) создаётся массив и этот массив нужно по тому же принципу (уникальность значений без пропусков и значений 0) как и в задаче 1 превратить в список B37:B75 (выделен синим цветом). Заранее спасибо за помощь! Уже более недели бьюсь над формулой и все взятые где-либо примеры не удалось применить к своей таблице. P.S. Вариант с применением фильтра и сортировки вручную частично решает первую задачу (вторую никак), но проблема в том, жёлтые списки подгружаются через ИНДЕКС из другой таблицы и постоянно меняются и добавляются новые. Поэтому очень нужна формула, которая бы решала эту задачу автоматически.
Добрый день! Очень нужно решение с помощью формул. Задача: преобразование массива в список без пробелов (или занчений 0) и повторений. 2 последовательные (почти однотипные задачи): 1. Из жёлтых ячеек (G6:G30) и разреженного и повторяющегося списка надо создать соответствующий список (выделен зелёным- H6:H30) таким образом, чтобы в него вошли только уникальные значения и без пропусков (или значений 0). В самом идеальном варианте еще и отсортировать автоматически по алфавиту. Подобная формула затем копируется в последуюшие блоки жёлтых и зелёных массивов (они нам нужны для решения задачи 2). 2. Из всех зелёных областей (их будет достаточно много) создаётся массив и этот массив нужно по тому же принципу (уникальность значений без пропусков и значений 0) как и в задаче 1 превратить в список B37:B75 (выделен синим цветом). Заранее спасибо за помощь! Уже более недели бьюсь над формулой и все взятые где-либо примеры не удалось применить к своей таблице. P.S. Вариант с применением фильтра и сортировки вручную частично решает первую задачу (вторую никак), но проблема в том, жёлтые списки подгружаются через ИНДЕКС из другой таблицы и постоянно меняются и добавляются новые. Поэтому очень нужна формула, которая бы решала эту задачу автоматически.Ali_Stanov
(массив) Но кроме вышесказанной проблемы (сортировки и значения "0"), главная- при добавлении сверху строк (а вид как в первом примере крайне необходим) формула перестаёт работать, а корректировки не дают результата. [moder]Формулы надо оформлять тегами (кнопка fx). На первый раз поправила[/moder] Спасибо, модератор, буду знать!
Наиболее близко к решению 1 задачи я подошел с помощью следующей формулы (без сортировки по имени и без отсева значения "0")
(массив) Но кроме вышесказанной проблемы (сортировки и значения "0"), главная- при добавлении сверху строк (а вид как в первом примере крайне необходим) формула перестаёт работать, а корректировки не дают результата. [moder]Формулы надо оформлять тегами (кнопка fx). На первый раз поправила[/moder] Спасибо, модератор, буду знать!Ali_Stanov
Здравствуйте. А может быть попробовать консолидацией, результат получится как в файле в столбцах A:D, далее это можно фильтровать. В ы же не написали какой конечный результат, поэтому предположил что данные из столбцов Пришло и Ушло должны суммироваться. Если нет, то столбцы C и D моно очистить, останутся только фамилии.
Здравствуйте. А может быть попробовать консолидацией, результат получится как в файле в столбцах A:D, далее это можно фильтровать. В ы же не написали какой конечный результат, поэтому предположил что данные из столбцов Пришло и Ушло должны суммироваться. Если нет, то столбцы C и D моно очистить, останутся только фамилии.gling
gling, здраствуйте. "Пришло" и "Ушло" это сравнение зеленых списков между собой на наличие тех или иных имен в списках справа и слева, для дальнейшего условного форматирования цветом появившихся или исчезнувших имен (в общем, это не является проблемой и давно решено- там нет формул, чтобы файл меньше весил, а то в 100кБ никак не влезть ) Цель искомых формул в том, чтобы на основании массива выводить уникальный список без пропусков сперва в зелёных ячейках на основании соответствующих жёлтых, а потом на основании зелёных формировать синий список (тоже уникальный и без пропусков)... И если с первой задачей я в принципе готов справляться в ручном режиме фильтрами и сортировкой (если не получается найти решение формулой), то делать это вручную во втором случае (перевод данных из зелёных столбцов в синий список) кажется проблематичным, т.к. количество зелёных столбцов будет постоянно расти. Но что-то мне подсказывает, что при решении любой из этих задач, другая реашается автоматом. Прикладываю файл с как это должно выглядеть в результате ввода формул. [p.s.]Спасибо, с консолидацией я ещё ни разу не работал- попробую поизучать возможности. Но беглый осмотр этой функции мне подсказывает, что это не сможет решить мою проблему в автоматическом режиме, т.к. данные будут постоянно обновляться и увеличиваться. Если я не прав, поправьте меня!
gling, здраствуйте. "Пришло" и "Ушло" это сравнение зеленых списков между собой на наличие тех или иных имен в списках справа и слева, для дальнейшего условного форматирования цветом появившихся или исчезнувших имен (в общем, это не является проблемой и давно решено- там нет формул, чтобы файл меньше весил, а то в 100кБ никак не влезть ) Цель искомых формул в том, чтобы на основании массива выводить уникальный список без пропусков сперва в зелёных ячейках на основании соответствующих жёлтых, а потом на основании зелёных формировать синий список (тоже уникальный и без пропусков)... И если с первой задачей я в принципе готов справляться в ручном режиме фильтрами и сортировкой (если не получается найти решение формулой), то делать это вручную во втором случае (перевод данных из зелёных столбцов в синий список) кажется проблематичным, т.к. количество зелёных столбцов будет постоянно расти. Но что-то мне подсказывает, что при решении любой из этих задач, другая реашается автоматом. Прикладываю файл с как это должно выглядеть в результате ввода формул. [p.s.]Спасибо, с консолидацией я ещё ни разу не работал- попробую поизучать возможности. Но беглый осмотр этой функции мне подсказывает, что это не сможет решить мою проблему в автоматическом режиме, т.к. данные будут постоянно обновляться и увеличиваться. Если я не прав, поправьте меня!Ali_Stanov
Диапазоны консолидации взяты с запасом, поэтому при добавлении новых данных в таблицы достаточно активировать ячейку--нажать кнопку консолидации --ОК. Это конечно не автоматом, но думаю что можно записать эту процедуру макрорекордером и повесить её на активацию листа. А в общем то не понятен смысл сбора в зеленом поле, если сразу можно собрать в синем. А для условного форматирования и подсчета Пришло - Ушло не обязательно собирать в кучу в зеленом поле. Зачем создавать еще столбец если можно обойтись без него?
Диапазоны консолидации взяты с запасом, поэтому при добавлении новых данных в таблицы достаточно активировать ячейку--нажать кнопку консолидации --ОК. Это конечно не автоматом, но думаю что можно записать эту процедуру макрорекордером и повесить её на активацию листа. А в общем то не понятен смысл сбора в зеленом поле, если сразу можно собрать в синем. А для условного форматирования и подсчета Пришло - Ушло не обязательно собирать в кучу в зеленом поле. Зачем создавать еще столбец если можно обойтись без него?gling
gling, Если вдаваться в подробности (отвечая на вопрос "Зачем"): Зелёные поля- это помесячный список лидов (он подгружается помесячно из другого листа (точней- подгружается в жёлтый, но там много дублей и пропусков, для этого и создан зелёный), куда в онлайн-режиме вводится покупка лидом абонемента)- я хочу знать, сколько их было в этом месяце, сколько пришло новых и сколько ушло, а также: кто конкретно пришёл и ушёл для своевременных действий по возвращению клиентов или дополнительному вовлечению новых (у меня танцевальная студия). Также на основании этих данных я увижу среднее количество лидов в месяц и показатели текучести. Голубое поле- сводные данные по каждому лиду: уникальное значение (ФИО, телефон и т.п.) и сколько месяцев он является активным (будет решаться в соседнем столбце путем подсчёта, сколько раз данный лид фигурирует в массиве всех зелёных столбцов). На основании этих данных я увижу продолжительность активности каждого лида конкретно, а также общие показатели, какое количество лидов активны в течении определенного количества месяцев, процентные соотнешения, себестоимость привлечения нового лида и т.п. С Пришёл-Ушёл вопросов не возникает- уже введены формулы, решающие поставленные задачи с подсчётом новых и исчезнувших в том или ином месяце лидов в зелёном списке и необходимой информативности о них с помощью условного форматирования. Так что мне нужны и зелёные поля (для оперативного учёта) и голубое (для общей статистики). Если действительно с помощью Консолидации и (как вариант) обновления её кнопкой ОК можно выводить из массива (особенно разреженного во втором варианте) уникальный список- расскажите, пожалуйста, а то, как я сказал выше, о существоавнии её узнал только что от Вас. Буду очень благодарен!:)
gling, Если вдаваться в подробности (отвечая на вопрос "Зачем"): Зелёные поля- это помесячный список лидов (он подгружается помесячно из другого листа (точней- подгружается в жёлтый, но там много дублей и пропусков, для этого и создан зелёный), куда в онлайн-режиме вводится покупка лидом абонемента)- я хочу знать, сколько их было в этом месяце, сколько пришло новых и сколько ушло, а также: кто конкретно пришёл и ушёл для своевременных действий по возвращению клиентов или дополнительному вовлечению новых (у меня танцевальная студия). Также на основании этих данных я увижу среднее количество лидов в месяц и показатели текучести. Голубое поле- сводные данные по каждому лиду: уникальное значение (ФИО, телефон и т.п.) и сколько месяцев он является активным (будет решаться в соседнем столбце путем подсчёта, сколько раз данный лид фигурирует в массиве всех зелёных столбцов). На основании этих данных я увижу продолжительность активности каждого лида конкретно, а также общие показатели, какое количество лидов активны в течении определенного количества месяцев, процентные соотнешения, себестоимость привлечения нового лида и т.п. С Пришёл-Ушёл вопросов не возникает- уже введены формулы, решающие поставленные задачи с подсчётом новых и исчезнувших в том или ином месяце лидов в зелёном списке и необходимой информативности о них с помощью условного форматирования. Так что мне нужны и зелёные поля (для оперативного учёта) и голубое (для общей статистики). Если действительно с помощью Консолидации и (как вариант) обновления её кнопкой ОК можно выводить из массива (особенно разреженного во втором варианте) уникальный список- расскажите, пожалуйста, а то, как я сказал выше, о существоавнии её узнал только что от Вас. Буду очень благодарен!:)Ali_Stanov
Сообщение отредактировал Ali_Stanov - Вторник, 31.05.2016, 03:48
А такой вариант: данные подгружаем не в новые столбцы, а в новые строки с указанием в отдельном столбце месяца. Получаем плоскую таблицу, на основе которой строим какие угодно сводные
А такой вариант: данные подгружаем не в новые столбцы, а в новые строки с указанием в отдельном столбце месяца. Получаем плоскую таблицу, на основе которой строим какие угодно сводныеPelena
Формула массива. Вводится одновременным нажатием Контрл Шифт Ентер А синие можно сделать сводной таблицей с несколькими диапазонами консолидации. В файле сделано на 999 строк Кстати, зеленые можно тоже сводными сделать. МИнус - каждый раз обновлять нужно или вручную, или макросом.
Формула массива. Вводится одновременным нажатием Контрл Шифт Ентер А синие можно сделать сводной таблицей с несколькими диапазонами консолидации. В файле сделано на 999 строк Кстати, зеленые можно тоже сводными сделать. МИнус - каждый раз обновлять нужно или вручную, или макросом._Boroda_
Pelena, спасибо огромное! Вариант со сводными таблицами я не рассматривал... Этак ведь можно и не перегружать и до того перегруженный формулами файл, а просто брать из исходника (есть исходная постоянно заполняемая таблица, которая в оригинале и подгружалась в жёлтые столбцы. И эта таблица как раз плоская...). Если ещё вывести некое общее обновление всех сводных на этом листе одним нажатием, то это будет прям идеально! Пошёл ваять сводные, надеюсь осилю:)
Pelena, спасибо огромное! Вариант со сводными таблицами я не рассматривал... Этак ведь можно и не перегружать и до того перегруженный формулами файл, а просто брать из исходника (есть исходная постоянно заполняемая таблица, которая в оригинале и подгружалась в жёлтые столбцы. И эта таблица как раз плоская...). Если ещё вывести некое общее обновление всех сводных на этом листе одним нажатием, то это будет прям идеально! Пошёл ваять сводные, надеюсь осилю:)Ali_Stanov
_Boroda_, спасибо, формула работает! Я так понял, что подобная формула не будет работать, если вместо исходного массива в виде 1 столбца использовать массив из 30-40-50... разрозненных столбцов (типа как я изначально хотел). Думается, эксель перейдёт в постоянное висение))) Попробую альтернативный вариант со сводными таблицами из исходника на другом листе. Или приду к варианту совмещения Вашей формулы со сводными в синий диапозон, если не получу достаточное количество сводных для текущего анализа помесячно! Ещё раз спасибо!
_Boroda_, спасибо, формула работает! Я так понял, что подобная формула не будет работать, если вместо исходного массива в виде 1 столбца использовать массив из 30-40-50... разрозненных столбцов (типа как я изначально хотел). Думается, эксель перейдёт в постоянное висение))) Попробую альтернативный вариант со сводными таблицами из исходника на другом листе. Или приду к варианту совмещения Вашей формулы со сводными в синий диапозон, если не получу достаточное количество сводных для текущего анализа помесячно! Ещё раз спасибо!Ali_Stanov
Лишним не будет, у меня такое адское количество листов аналитики, что ещё пригодится и не раз. К тому же, возможно без её использования и эту задачу решить не получится (всё зависит от возможностей пока что малоизвестных для меня сводных таблиц). За макрос отдельное!
Лишним не будет, у меня такое адское количество листов аналитики, что ещё пригодится и не раз. К тому же, возможно без её использования и эту задачу решить не получится (всё зависит от возможностей пока что малоизвестных для меня сводных таблиц). За макрос отдельное!Ali_Stanov
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
)
