Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Составление массива по последнему значению условия в базе данных
 
#1
03.11.2017 19:59:48
Добрый день! Много раз гуглил и искал по форуму, ничего похожего на ответ на мой вопрос не нашел. Возможно, я был просто не внимателен, поэтому простите, если так.
Вопрос:
Нужно вытащить Многоразовым ВПР только последнее значение, удовлетворяющее условию.
Прикрепляю файл примера.
Пояснения к файлу:
На листе Анализ ведется учет анализа котировок акций. Таблица будет бесконечной, каждый день она пополняется новыми строками. На лист Weekly Watchlist в столбец B методом Многоразового ВПР вытаскиваются все значения из столбца С листа Анализ по условию столбец AN листа Анализ = Yes.
Т.к. названия инструментов повторяются, а метод Многоразового ВПР вытаскивает все значения, то инструменты могут задваиваться на листе Weekly Watchlist.
В таблице приведен пример: строки 18 и 19 в Листе Анализ ведут к двум записям в листе Weekly Watchlist на строках 3 и 4.
Из-за этого приходится вручную менять старые значения столбца AN на No, на что уходит очень много времени каждый день.

Как вытащить только самое последнюю запись инструмента из листа Анализ по условию Yes В столбце AN?

Заранее буду очень признателен за помощь.

К сожалению, столкнулся с тем, что сайт требует размер загружаемого файла менее 100 Кб. Я уже все лишнее удалил из файла, он весит все равно 410 Кб. Поэтому вот ссылка на скачивание его с моего dropbox.
Изменено: yeti1249 - 03.11.2017 20:00:43
 
 
 
#2
03.11.2017 20:04:59
Что за многоразовый ВПР?
Понял, из приёмов...
Изменено: Hugo - 03.11.2017 20:09:20
 
 
 
#3
03.11.2017 20:08:01
Прошу прощения, забыл вставить ссылку на метод. Пользуюсь методом Многоразового ВПР из данной статьи: http://www.planetaexcel.ru/techniques/2/81/#8211
 
 
 
#4
08.11.2017 17:40:25
Кто-нибудь? Можно эту же задачу реализовать любым другим способом. Свои знания на адаптации многоразового ВПР-а заканчиваются, а в итоге трачу очень много времени без данного решения. Буду очень благодарен за помощь.
 
 
 
#5
08.11.2017 19:15:14
yeti1249, что же Вы хотите помощи, а файл не прикладываете. Ссылка не считается ;) . Потрудитесь создать пример, тогда и помощь будет.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#6
08.11.2017 19:24:14
Прошу прощения 2 часа бился над уменьшением размера файла. Все поудалял, все равно как ни крути весит 400 Кб. Сейчас со свежей головой создал новый, вроде получилось меньше 100 Кб. Прикрепляю файл примера.
 
 
 
#7
08.11.2017 19:43:12
Цитата
yeti1249 написал:
Сейчас со свежей головой создал новый
Ничего себе Вы пример создали. Проверка данных, гиперссылки, куча ненужной информации.
Проверяйте:
=ИНДЕКС(Анализ!$C$14:$C$19;МАКС(ЕСЛИ(Анализ!$AN$14:$AN$19="Yes";СТРОКА(Анализ!$AN$14:$AN$19)-13)))
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#8
08.11.2017 19:59:14
Прошу прощения, я просто удаляю все из текущей таблицы, которой сам пользуюсь.
Про формулу: она вытаскивает только 1 название инструмента из столбца С. Возможно я неправильно изначально сформулировал вопрос. В столбце С может быть сколько угодно различных названий, каждое повторяется много раз, и каждое название нужно вытащить только последнее. Прилагаю еще один пример, возможно тут будет более ясно.
Изменено: yeti1249 - 08.11.2017 20:00:47
 
 
 
#9
08.11.2017 20:00:19
*Последнее с Yes в столбце AN
 
 
 
#10
08.11.2017 20:23:44
Формула массива:
=ИНДЕКС($C$14:$C$27;НАИМЕНЬШИЙ(ЕСЛИОШИБКА(ЕСЛИ(ПОИСКПОЗ($C$14:$C$27;ЕСЛИ($AN$14:$AN$27=$AR$12;$C$14:$C$27);0)=СТРОКА($C$14:$C$27)-13;СТРОКА($C$14:$C$27)-13);"");СТРОКА()-13))
Результат на листе Анализ. Выделил желтым. Если правильно, перенесёте на нужный Вам лист.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#11
08.11.2017 21:13:58
Огромное спасибо! Формула действительно работает! В случае массива отлично начинает работать в случае добавления ЕСЛИОШИБКА в начале формулы, чтобы убрать из массива мусор.
Наверное последний вопрос по данной теме:
В этом же примере для каждого последнего значения инструмента из столбца С по условию Yes в столбце АN нужно вывести массив соответствующих значений столбца АО.
Вопрос простой, прошу прощения, но мне, чтобы разобраться в логике формулы, потребуется наверное вечерок курения мануала)
 
 
 
#12
08.11.2017 21:36:35
yeti1249, в столбце АО в пределах одного инструмента значения Trade одинаковые. Какой смысл извлекать последнее, если оно такое же как и первое? Скажу по секрету, моя формула извлекает не последнее значение, а первое. Но какая разница, если результат один и тот же.
Изменено: Bema - 08.11.2017 21:37:21
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#13
08.11.2017 21:49:21
Охххх)))) Проверил еще раз) Да, Ваша формула извлекает именно первое значение из массива, а нужно именно последнее. Прилагаю обновленный файл примера.
В нем показано два случая:
1) для BT Group столбец АО сменился с Buy на Sell, поэтому надо вытащить именно последние значения.
2) для Sage столбец AN сменился с Yes на No, поэтому нам более не нужно вытаскивать его значение.

Т.е. надо найти последнюю запись по инструменту в столбце С, проверить столбец AN, если он Yes, то выводим ее; если он No, то не выводим.
И в массив по столбцу АО нужно найти последнюю запись по инструменту в столбце С, проверить столбец AN, если он Yes, то выводим значение этой записи столбца AO, если No, то ничего не выводим.
 
 
 
#14
08.11.2017 21:59:06
Цитата
yeti1249 написал:
Да, Ваша формула извлекает именно первое значение из массива, а нужно именно последнее.
Для первой части Вашего вопроса это не принципиально. То что Вы описываете можно перефразировать- извлечь уникальные значения из столбца С соответствующие значению Yes в столбце AN.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#15
08.11.2017 22:07:22
Нет.Пример Sage из файла: В случае, если после строки с Yes, есть строка с No для этого же инструмента из столбца C, то нам его в массив выводить не нужно. Т.е. нужно в обоих случаях смотреть именно последнее значение для инструмента и по нему решать, выводить его в итоговый массив или нет.

Чтобы было яснее, почему именно так: на листе Анализ я веду логи анализа графиков для принятия решений я датами составления. На основе низ формирую массив, на какие инструменты следует обратить внимание сегодня для заключения сделок. Так каждый будний день.
Т.к. изо дня в день все параметры по инструменты могут меняться, то мне нужно по последней записи анализа инструмента все решать на следующий день. При этом инструменты обновляются не каждый день (если по ним ничего не происходит), а значит нельзя вытаскивать по дате, а нужно именно последнее значение по каждому инструменту анализировать.
Изменено: yeti1249 - 08.11.2017 22:08:35
 
 
 
#16
08.11.2017 22:09:02
Формула массива. Вводить сочетанием клавиш Ctrl+Shift+Enter. Если всё сделаете правильно, формула обернётся фигурными скобками { }.
=ЕСЛИОШИБКА(ИНДЕКС($AO$14:$AO$28;МАКС(ЕСЛИ(($C$14:$C$28=AR14)*($AN$14:$AN$28=$AR$12);СТРОКА($C$14:$C$28)-13)));"")
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#17
08.11.2017 22:19:39
Вроде выводит похожий массив. Но при добавлении строк, все-таки не добавляет нужного значения. Увеличил массив до 30й строки, добавил несколько строк с инструментом Sage. На данный момент должен был попасть в массив.
Так же добавляю комментарии в файл.
 
 
 
#18
08.11.2017 22:21:21
Т.е. если последнему инструменту в группе соответствует No, его не нужно переносить? В файле залил синим. BT Group и Home Depot в столбец AR не нужно переносить?
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#19
08.11.2017 22:28:11
Все верно, если последнее значение инструмента No, то его не переносим. Если Yes, то переносим.
 
 
 
#20
08.11.2017 22:30:54
Цитата
yeti1249 написал:
Но при добавлении строк
нужно ещё и за диапазонами в формуле следить ;) .
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#21
08.11.2017 22:38:56
Добавляются теперь строки абсолютно корректно. Но не удаляются. Смотрите, поставил последней строкой Sage No, т.е. его не должно быть в конечном массиве. Но строка не удаляется.
 
 
 
#22
08.11.2017 22:41:20
Цитата
yeti1249 написал:
Но не удаляются.
И не будет. Нужно новую формулу писать. Дайте подумать ;)  
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#23
08.11.2017 22:45:26
Прошу прощения! Огромное Вам спасибо уже за труд) Если получится, буду по паре часов в день экономить)
 
 
 
#24
08.11.2017 22:47:05
yeti1249, доп. столбцы допускаются?
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#25
08.11.2017 22:47:58
Название темы о чем?
 
 
 
#26
08.11.2017 22:50:32
yeti1249, пока я думаю над решением, Вы думайте о названии. А то получим шелбанов от модераторов :D  
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#27
08.11.2017 22:51:42
Bema, допускаются, конечно.
vikttur, прошу прощения, т.к. в своем изначальном файле использовал именно данный метод, то счел название адекватным. Сейчас уже вижу, что оно не подходит.
 
 
 
#28
08.11.2017 22:54:01
yeti1249, в сообщении предложите новое название. Модераторы поменяют, если название будет нормальным.
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
#29
08.11.2017 22:56:50
vikttur, считаю, что наиболее релевантное название для темы: Составление массива по последнему значению условия в базе данных
 
 
 
#30
08.11.2017 23:06:09
yeti1249, обратил внимание, что инструменты могут быть не сгруппированы. Для случая в файле нужно выводить BT Group? В ячейке AN21 No.
Изменено: Bema - 08.11.2017 23:07:30
Если в мире всё бессмысленно, — сказала Алиса, — что мешает выдумать какой-нибудь смысл? ©Льюис Кэрролл
 
 
 
Страницы: 1 2 След.
Наверх