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

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

чем использовать ДВССЫЛ
 
 
 
#8
13.05.2015 17:10:35
Цитата
Lucien написал:  А про параметр "СТРОКА" можете сказать? Или просто исправить и всё.
можете его не трогать, то что он сылается на ячейку гдето сбоку не страшно главное чтобы на ячейку в первой строке для первой формулы.
 
 
 
#9
13.05.2015 17:15:20
Благодарю за понятные ответы. Попробую использовать рекомендованную формулу.
 
 
 
#10
13.05.2015 19:35:16
Оказалось что ячейка не совсем пустая, а с формулой. Как сделать чтобы массив срабатывал от значения "пусто" с формулой внутри?  
(проблема отмечена жёлтым цветом)
 
 
 
#11
13.05.2015 20:13:42
Код
?
1
=ЕСЛИОШИБКА(ИНДЕКС($F$2:$F$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2:$F$16="";"";СТРОКА($F$1:$F$15));СТРОКА(L1)));"")
 
 
 
#12
13.05.2015 20:19:54
Спасибо Большое. Что бы я без умных людей делал...
 
 
 
#13
10.08.2019 11:36:38
Цитата
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)));"")
Что-то не так, подскажите, почему выдает ошибку:
Изменено: farich_13 - 13.08.2019 16:37:34
 
 
 
#14
13.08.2019 16:28:10
farich_13, может достаточно добавить еще одну скобку в  - 18)));СТР
а лучше пример бы
 
 
 
#15
14.08.2019 01:03:33
спасибо. помогло.

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

Вот, что получилось в итоге:
Код
?
1
=ЕСЛИОШИБКА(ИНДЕКС($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)));"")

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

Пример во вложении.
 
 
 
#16
14.08.2019 01:32:13
Добавляем формулу для отметки нужных:
=ЕСЛИ(A2<>"";ЕСЛИ(СЧЁТЕСЛИ($E$2:$E$3;A2)=0;МАКС($B$1:B1)+1))
и подтягиваем обычной ВПР:
=ЕСЛИ(СТРОКА(A1)>$D$1;"";ИНДЕКС(A:A;ПОИСКПОЗ(СТРОКА(A1);B:B;)))
 
 
 
#17
14.08.2019 07:47:14
Цитата
Lucien написал:
через формулу. (для автоматизма, так как исходники меняются
а почему для автоматизма не годится PQ?
Код
?
1
2
3
4
5
6
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
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#18
14.08.2019 09:39:14
Потому что одни забыли формулы, а другие не хотят учить PQ :)
 
 
 
#19
14.08.2019 10:07:46
Цитата
vikttur написал:
забыли формулы
нет ну ни фига себе - оскорбления на форуме запрещены  :cry:  :cry:  :cry:
Соблюдение правил форума не освобождает от модераторского произвола
 
 
 
#20
14.08.2019 10:15:52
Дык, о PQ - это о себе. Равенство и братство :)
 
 
 
#21
14.08.2019 12:03:14
OFF
Цитата
vikttur написал:
Дык, о PQ - это о себе. Равенство и братство
Это я еще с SQL не подкатывал :-)
Код
?
1
2
3
4
5
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)
По вопросам из тем форума, личку не читаю.
 
 
 
#22
14.08.2019 12:14:26
Еще один скоро пропадет. А если учитывать, что в самокрутке, то это "скоро" настанет еще быстрее )
 
 
 
#23
15.08.2019 00:22:40
vikttur, спасибо, очень крутой вариант. Значительно лучше, чем массив тормознутый.
Но мой перфекционизм и мозг, любящий все сокращать и из за этого усложнять, не дает мне покоя из за дополнительного столбца с порядковыми цифрами и ЛОЖЬю.
Можно, конечно, просто скрыть текст невидимым шрифтом. Но как план Б.
Но осмелюсь спросить, есть вариант обойтись без доп столбца?

Цитата
buchlotnik написал: а почему для автоматизма не годится PQ?
не зашел мне PQ (не отрицаю, что я в нем просто не разобрался), но я больше формулы уважаю.
Спасибо за альтернативу.
 
 
 
#24
15.08.2019 02:44:38
Цитата
farich_13 написал: есть вариант обойтись без доп столбца?
Есть. Он у Вас и раньше был :)
Не всегда короче_меньше = лучше.
 
 
 
#25
15.08.2019 07:14:54
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)));"")
Только на больших объемах будет понятно, что из этого работает быстрее, но разница только в обработке запаса, когда формула должна вернуть пусто.
По вопросам из тем форума, личку не читаю.
 
 
 
#26
15.08.2019 08:28:48
Вернулись к массиву.
В общем, решений достаточно. Потестю по скорости, что меня устроит и в практику.

Всем большое спасибо.
 
 
 
#27
22.02.2021 02:22:40
Добрый день.

Есть очень похожая задача, но подтянуть нужно данные из другого столбца (находящегося слева) адаптировать вышепредложенное не удалось
 
 
 
#28
22.02.2021 03:36:01
Доброй ночи.
Код
?
1
=IFERROR(INDEX($B$9:$B$50;MATCH(AGGREGATE(15;6;COUNTIF($B$9:$B$50; "<"&$B$9:$B$50)/((COUNTIF(F$8:F8; $B$9:$B$50)=0)/($C$9:$C$50=F$8)/($B$9:$B$50<>"")); 1); INDEX(COUNTIF($B$9:$B$50; "<"&$B$9:$B$50);0); 0));"")
 
 
 
#29
22.02.2021 12:52:48
memo, спасибо большое!

Теперь предстоит в этом разобраться.
 
 
 
#30
22.02.2021 12:57:14
Дмитрий Левин,
Та формула отсекает дубликаты и сортирует список. Если этого не требуется, то можно попроще.
Код
?
1
=IFERROR(INDEX($B$9:$B$50;AGGREGATE(15;6;ROW($A$1:$A$50)/(($C$9:$C$50=F$8)/($B$9:$B$50<>"")); ROW(A1)));"")
Изменено: memo - 22.02.2021 12:57:30
 
 
 
Страницы: 1 2 След.
Читают тему
Loading...