Поиск  Пользователи  Правила 
Закрыть
Логин:
Пароль:
Забыли свой пароль?
Регистрация
Войти
 
Страницы: 1
RSS
Как получить список без пустых значений и с учетом исключений (из другого списка), формулой
 
Здравствуйте знатоки.
Как сделать столбец, с пропуском пустых ячеек? И чтобы это было через формулу. (для автоматизма, так как исходники меняются. А сам результат выводится на график.)
(так же в примере есть найденная формула, которая почему-то не работает)
P.S.Прочитал уже много форумов, но выложенные голые формулы не работают. Нужно видимо сразу в таблицу вписать.
Изменено: Lucien - 13 Май 2015 13:13:42
 
увеличьте диапазон до А16(весь исходник)
и введите формулу НЕ Enter, а CTRL+SHIFT+ENTER
- растяните формулу вниз...
... работает формула, представленная  в файле!
Код
=ЕСЛИОШИБКА(ДВССЫЛ("A"&НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($A$2:$A$16);"";СТРОКА($A$2:$A$16));СТРОКА(I1)));"")
Изменено: JeyCi - 12 Май 2015 20:51:41
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
О, Благодарю! Теперь сработало.  Раньше через CTRL+SHIFT+ENTER пытался запустить, но не получалось. Спасибо за быстрый ответ.
 
И ещё вопросик. В формуле какой-то странный параметр, куда-то вбок направлен. Он нужный или его можно как-то удалить?
=ЕСЛИОШИБКА(ДВССЫЛ("A"&НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($A$2:$A$16);"";СТРОКА($A$2:$A$16));СТРОКА(F1)));"";)

(этот вопрос решён)
После переноса формулы и создания массива, он продолжает работать только
со столбцом "А". Адрес столбца в формуле поменял на "F", но с ним
работать не хочет, а опять берёт значения из столбца "А".  На другой
лист перенёс - та же история.
Как сдвинуть эту хитрую формулу?
(этот вопрос решён) >> столбец меняется здесь >>        =ЕСЛИОШИБКА(ДВССЫЛ("A"&НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($A$2:$A$16);"";СТРОКА($A$2:$A$16));СТРОКА(F1)));""
Изменено: Lucien - 13 Май 2015 17:45:15
 
Цитата
Lucien написал: Адрес столбца в формуле поменял на "F"
это ни чего не даст, почитайте про ф-цию СТРОКА в справке.
 
Благодарю за ответ. Решение уже сам увидел. А про параметр "СТРОКА" можете сказать? Или просто исправить и всё. Спасибо.
(в справке написано очень умными словами там ничего не понял)
Изменено: Lucien - 13 Май 2015 17:44:43
 
лучше использовать такую формулу
Код
=ЕСЛИОШИБКА(ИНДЕКС($F$2:$F$16;НАИМЕНЬШИЙ(ЕСЛИ(ЕПУСТО($F$2:$F$16);"";СТРОКА($F$1:$F$15));СТРОКА(L1)));"")

чем использовать ДВССЫЛ
 
Цитата
Lucien написал:  А про параметр "СТРОКА" можете сказать? Или просто исправить и всё.
можете его не трогать, то что он сылается на ячейку гдето сбоку не страшно главное чтобы на ячейку в первой строке для первой формулы.
 
Благодарю за понятные ответы. Попробую использовать рекомендованную формулу.
 
Оказалось что ячейка не совсем пустая, а с формулой. Как сделать чтобы массив срабатывал от значения "пусто" с формулой внутри?  
(проблема отмечена жёлтым цветом)
 
Код
=ЕСЛИОШИБКА(ИНДЕКС($F$2:$F$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2:$F$16="";"";СТРОКА($F$1:$F$15));СТРОКА(L1)));"")
 
Спасибо Большое. Что бы я без умных людей делал...
 
Цитата
V написал: 1=ЕСЛИОШИБКА(ИНДЕКС($F$2:$F$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2:$F$16="";"";СТРОКА($F$1:$F$15));СТРОКА(L1)));"")
всем доброе утро.
мне помогла эта формула, но мне в нее необходимо добавить еще 1 условие, наподобие ЕСЛИ($F$2:$F$16="";"";

делаю вот так (в примере мои ячейки):
Цитата
=ЕСЛИОШИБКА(ИНДЕКС($E$1:$E$18;НАИМЕНЬШИЙ(ЕСЛИ($E$1:$E$18="";"";ЕСЛИ($E$1:$E$18="Иванов";"";СТРОКА($E$1:$E$18));СТРОКА(L1)));"")
Что-то не так, подскажите, почему выдает ошибку:
Снимок.PNG (14.23 КБ)
Изменено: farich_13 - 13 Авг 2019 16:37:34
 
farich_13, может достаточно добавить еще одну скобку в  - 18)));СТР
а лучше пример бы
 
спасибо. помогло.

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

Вот, что получилось в итоге:
Код
=ЕСЛИОШИБКА(ИНДЕКС($A$1:$A$18;НАИМЕНЬШИЙ(ЕСЛИ($A$1:$A$18="";"";ЕСЛИ($A$1:$A$18=$F$2;"";ЕСЛИ($A$1:$A$18=$F$3;"";СТРОКА($A$1:$A$18))));СТРОКА(H1)));"")

Есть ли другой способ (с помощью формулы) без массива, но и не макрос, чтобы добиться желаемого результата?

Пример во вложении.
 
Добавляем формулу для отметки нужных:
=ЕСЛИ(A2<>"";ЕСЛИ(СЧЁТЕСЛИ($E$2:$E$3;A2)=0;МАКС($B$1:B1)+1))
и подтягиваем обычной ВПР:
=ЕСЛИ(СТРОКА(A1)>$D$1;"";ИНДЕКС(A:A;ПОИСКПОЗ(СТРОКА(A1);B:B;)))
 
Цитата
Lucien написал:
через формулу. (для автоматизма, так как исходники меняются
а почему для автоматизма не годится PQ?
Код
let
    from = Excel.CurrentWorkbook(){[Name="Таблица1"]}[Content],
    iskl = Table.Column(Excel.CurrentWorkbook(){[Name="Таблица2"]}[Content],"Исключения"),
    filtr = Table.SelectRows(from, each ([Сотрудник] <> null and not List.ContainsAny(Record.ToList(_),iskl)))
in
    filtr
Каждому For свой Next
 
Потому что одни забыли формулы, а другие не хотят учить PQ :)
 
Цитата
vikttur написал:
забыли формулы
нет ну ни фига себе - оскорбления на форуме запрещены  :cry:  :cry:  :cry:
Каждому For свой Next
 
Дык, о PQ - это о себе. Равенство и братство :)
 
OFF
Цитата
vikttur написал:
Дык, о PQ - это о себе. Равенство и братство
Это я еще с SQL не подкатывал :-)
Код
SELECT Сотрудник
FROM ( SELECT T1.Сотрудник, T2.Исключения
FROM {oj [Лист1$A:A] as T1 LEFT OUTER JOIN [Лист1$E:E] as T2 ON T1.Сотрудник = T2.Исключения}
WHERE (T1.Сотрудник Is Not Null) )
WHERE (Исключения Is Null)
 
Еще один скоро пропадет. А если учитывать, что в самокрутке, то это "скоро" настанет еще быстрее )
 
vikttur, спасибо, очень крутой вариант. Значительно лучше, чем массив тормознутый.
Но мой перфекционизм и мозг, любящий все сокращать и из за этого усложнять, не дает мне покоя из за дополнительного столбца с порядковыми цифрами и ЛОЖЬю.
Можно, конечно, просто скрыть текст невидимым шрифтом. Но как план Б.
Но осмелюсь спросить, есть вариант обойтись без доп столбца?

Цитата
buchlotnik написал: а почему для автоматизма не годится PQ?
не зашел мне PQ (не отрицаю, что я в нем просто не разобрался), но я больше формулы уважаю.
Спасибо за альтернативу.
 
Цитата
farich_13 написал: есть вариант обойтись без доп столбца?
Есть. Он у Вас и раньше был :)
Не всегда короче_меньше = лучше.
 
farich_13, Подразгогнать формулу можно чуток
=IFERROR(INDEX(A:A;SMALL(IF(($A$2:$A$18<>"")*ISERROR(MATCH($A$2:$A$18;$E$1:$E$100;));ROW($A$2:$A$18));ROW(A1)));"")
или
=IF(ROW()<=COUNTIF(A:A;">""")-SUM(COUNTIF(A:A;$E$1:$E$3));INDEX(A:A;SMALL(IF(($A$2:$A$18<>"")*ISERROR(MATCH($A$2:$A$18;$E$1:$E$100;));ROW($A$2:$A$18));ROW(A1)));"")
Только на больших объемах будет понятно, что из этого работает быстрее, но разница только в обработке запаса, когда формула должна вернуть пусто.
 
Вернулись к массиву.
В общем, решений достаточно. Потестю по скорости, что меня устроит и в практику.

Всем большое спасибо.
Страницы: 1
Читают тему (гостей: 1)
Наверх