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

Вход

Регистрация

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

 

= Мир MS Excel/Формула выборки следующего значения исключая использованные - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
  • Страница 1 из 1
  • 1
Модератор форума: _Boroda_, Manyasha, SLAVICK, китин  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула выборки следующего значения исключая использованные (Формулы/Formulas)
Формула выборки следующего значения исключая использованные
Strateg_ru Дата: Суббота, 31.08.2019, 16:36 | Сообщение № 1
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Уважаемые эксперты! Нужна помощь с формулой поиска "следующего вопроса", в зависимости от корректирующего значения (не макрос!)

На листе «Ввод» в столбце E (Ответ) следует вручную ввести значение.
В зависимости от этого значения, на листе «Исходник» - столбец С «Коэффициент», должно появиться значение (эта формула не нужна – использовать статичные данные).
На листе «Ввод», в столбце «Следующий» должен появиться номер вопроса, больше или меньше предыдущего номера на значение этого коэффициента. Например, если номер вопроса был – 35, и коэффициент стоит 5, значит первая проверка должна попасть на 40й вопрос. Если число 40 присутствует уже в этом столбце напротив того же участника (столбец А – «Участник»), нужно двигаться вверх по числовому ряду – 41, 42, 43... проверяя значения последовательно. Если следующего значения у этого участника не выпадало, отобразить его, иначе – смотреть следующее с листа «Исходник», столбца А «№ вопроса». Если все значения из этого столбца (до 99) – ранее присутствовали, нужно вернуться к 40 и проверять вниз – также последовательно – 39, 38, 37… Когда все значения закончились до первого (или десятого, как в примере) – всплывает сообщение – Вопросов нет!

Номера заданий – цифровые. Каждый следующий – больше предыдущего, но не обязательно на 1.
К сообщению приложен файл: 9053921.xlsx(10.7 Kb)
 
Ответить
СообщениеУважаемые эксперты! Нужна помощь с формулой поиска "следующего вопроса", в зависимости от корректирующего значения (не макрос!)

На листе «Ввод» в столбце E (Ответ) следует вручную ввести значение.
В зависимости от этого значения, на листе «Исходник» - столбец С «Коэффициент», должно появиться значение (эта формула не нужна – использовать статичные данные).
На листе «Ввод», в столбце «Следующий» должен появиться номер вопроса, больше или меньше предыдущего номера на значение этого коэффициента. Например, если номер вопроса был – 35, и коэффициент стоит 5, значит первая проверка должна попасть на 40й вопрос. Если число 40 присутствует уже в этом столбце напротив того же участника (столбец А – «Участник»), нужно двигаться вверх по числовому ряду – 41, 42, 43... проверяя значения последовательно. Если следующего значения у этого участника не выпадало, отобразить его, иначе – смотреть следующее с листа «Исходник», столбца А «№ вопроса». Если все значения из этого столбца (до 99) – ранее присутствовали, нужно вернуться к 40 и проверять вниз – также последовательно – 39, 38, 37… Когда все значения закончились до первого (или десятого, как в примере) – всплывает сообщение – Вопросов нет!

Номера заданий – цифровые. Каждый следующий – больше предыдущего, но не обязательно на 1.

Автор - Strateg_ru
Дата добавления - 31.08.2019 в 16:36
Pelena Дата: Воскресенье, 01.09.2019, 08:37 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 14474
Репутация: 3172 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Не уверена, что правильно поняла
Код
=ЕСЛИОШИБКА(ЕСЛИОШИБКА(АГРЕГАТ(15;6;Исходник!$A$2:$A$77/(Исходник!$A$2:$A$77>=B2+E2)/(СЧЁТЕСЛИМН($B$2:B2;АГРЕГАТ(15;6;Исходник!$A$2:$A$77/(Исходник!$A$2:$A$77>=B2+E2);1);$A$2:A2;A2)=0);1);АГРЕГАТ(14;6;Исходник!$A$2:$A$77/(Исходник!$A$2:$A$77<B2+E2)/(СЧЁТЕСЛИМН($B$2:B2;АГРЕГАТ(14;6;Исходник!$A$2:$A$77/(Исходник!$A$2:$A$77<B2+E2);1);$A$2:A2;A2)=0);1));"Вопросов нет!")
К сообщению приложен файл: 9053921-1-.xlsx(12.0 Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеНе уверена, что правильно поняла
Код
=ЕСЛИОШИБКА(ЕСЛИОШИБКА(АГРЕГАТ(15;6;Исходник!$A$2:$A$77/(Исходник!$A$2:$A$77>=B2+E2)/(СЧЁТЕСЛИМН($B$2:B2;АГРЕГАТ(15;6;Исходник!$A$2:$A$77/(Исходник!$A$2:$A$77>=B2+E2);1);$A$2:A2;A2)=0);1);АГРЕГАТ(14;6;Исходник!$A$2:$A$77/(Исходник!$A$2:$A$77<B2+E2)/(СЧЁТЕСЛИМН($B$2:B2;АГРЕГАТ(14;6;Исходник!$A$2:$A$77/(Исходник!$A$2:$A$77<B2+E2);1);$A$2:A2;A2)=0);1));"Вопросов нет!")

Автор - Pelena
Дата добавления - 01.09.2019 в 08:37
AxeNow Дата: Воскресенье, 01.09.2019, 15:00 | Сообщение № 3
Группа: Пользователи
Ранг: Прохожий
Сообщений: 3
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Прошу помощи, сам никак не дойду до ответа. Делаю таблицу для викторины, где динамически отображается позиция участника в зависимости от набранных очков. Но столкнулся с ситуацией, когда значения очков у нескольких участников равны и формула выводит только первого по порядку участника. Как бы сделать так, что бы при равных очках, отсекался бы уже выведенный участник и выводился следующий по порядку? Сам новичок в этом деле, не ругайтесь. Таблицу со своими формулами приложил. Заранее спасибо!
К сообщению приложен файл: 3166471.xlsx(10.4 Kb)
 
Ответить
СообщениеПрошу помощи, сам никак не дойду до ответа. Делаю таблицу для викторины, где динамически отображается позиция участника в зависимости от набранных очков. Но столкнулся с ситуацией, когда значения очков у нескольких участников равны и формула выводит только первого по порядку участника. Как бы сделать так, что бы при равных очках, отсекался бы уже выведенный участник и выводился следующий по порядку? Сам новичок в этом деле, не ругайтесь. Таблицу со своими формулами приложил. Заранее спасибо!

Автор - AxeNow
Дата добавления - 01.09.2019 в 15:00
Pelena Дата: Воскресенье, 01.09.2019, 15:02 | Сообщение № 4
Группа: Админы
Ранг: Местный житель
Сообщений: 14474
Репутация: 3172 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
AxeNow, прочитайте Правила форума и создайте свою тему


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеAxeNow, прочитайте Правила форума и создайте свою тему

Автор - Pelena
Дата добавления - 01.09.2019 в 15:02
Strateg_ru Дата: Воскресенье, 01.09.2019, 15:27 | Сообщение № 5
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Не уверена, что правильно поняла

Давайте напишу прям в конкретных примерах (частично функционал сработал, пока нашёл два сбоя).
Первый - если вначале прибавить, а потом отнять одно и тоже число - выдаёт Вопросов нет, хотя должен проверять дальше.
Второй - если изменить номер участника - результат не меняется, а должен проверять значения уже для этого участника.
Добавлю даже столбец с примером и пояснением.
1. Если сейчас стоит №30, а коэффициент 2, то должно выдать 32.
2. Если следующий ставим №32, а коэффициент -2, то первая проверка должна быть 30, но 30 был в предыдущем случае, значит дальше надо проверять вниз по значениям (вверх по столбцу А листа Исходники). Это будет 19.
3. Если обратная ситуация - вначале было 35 и -2, должно выдать 33, а потом +2, то 35 уже было, значит надо проверять вверх - 36... 37 и т.д.
4. В случае, когда проверка пошла вверх (коэффициент был больше нуля) и значения вверх закончились, применяем правило "проверять вниз" (как будто коэффициент меньше нуля).
Важно! Проверка идёт для каждого участника в отдельности.
К сообщению приложен файл: 9253427.xlsx(12.3 Kb)
 
Ответить
Сообщение
Не уверена, что правильно поняла

Давайте напишу прям в конкретных примерах (частично функционал сработал, пока нашёл два сбоя).
Первый - если вначале прибавить, а потом отнять одно и тоже число - выдаёт Вопросов нет, хотя должен проверять дальше.
Второй - если изменить номер участника - результат не меняется, а должен проверять значения уже для этого участника.
Добавлю даже столбец с примером и пояснением.
1. Если сейчас стоит №30, а коэффициент 2, то должно выдать 32.
2. Если следующий ставим №32, а коэффициент -2, то первая проверка должна быть 30, но 30 был в предыдущем случае, значит дальше надо проверять вниз по значениям (вверх по столбцу А листа Исходники). Это будет 19.
3. Если обратная ситуация - вначале было 35 и -2, должно выдать 33, а потом +2, то 35 уже было, значит надо проверять вверх - 36... 37 и т.д.
4. В случае, когда проверка пошла вверх (коэффициент был больше нуля) и значения вверх закончились, применяем правило "проверять вниз" (как будто коэффициент меньше нуля).
Важно! Проверка идёт для каждого участника в отдельности.

Автор - Strateg_ru
Дата добавления - 01.09.2019 в 15:27
Pelena Дата: Воскресенье, 01.09.2019, 22:57 | Сообщение № 6
Группа: Админы
Ранг: Местный житель
Сообщений: 14474
Репутация: 3172 ±
Замечаний: ±

Excel 2010, 2016 & Mac Excel
Вот такой массивный монстр получился
Код
=ЕСЛИОШИБКА(ЕСЛИ(E2>0;ЕСЛИОШИБКА(АГРЕГАТ(15;6;Nзадания/(Nзадания>=B2+E2)/(МУМНОЖ(--(Nзадания*(Nзадания>=B2+E2)<>ТРАНСП($B$2:B2*($A$2:A2=A2)));СТРОКА($A$2:A2)^0)=ЧСТРОК($A$2:A2));1);АГРЕГАТ(14;6;Nзадания/(Nзадания<B2+E2)/(МУМНОЖ(--(Nзадания*(Nзадания<B2+E2)<>ТРАНСП($B$2:B2*($A$2:A2=A2)));СТРОКА($A$2:A2)^0)=ЧСТРОК($A$2:A2));1));ЕСЛИОШИБКА(АГРЕГАТ(14;6;Nзадания/(Nзадания<=B2+E2)/(МУМНОЖ(--(Nзадания*(Nзадания<=B2+E2)<>ТРАНСП($B$2:B2*($A$2:A2=A2)));СТРОКА($A$2:A2)^0)=ЧСТРОК($A$2:A2));1);АГРЕГАТ(15;6;Nзадания/(Nзадания>B2+E2)/(МУМНОЖ(--(Nзадания*(Nзадания>B2+E2)<>ТРАНСП($B$2:B2*($A$2:A2=A2)));СТРОКА($A$2:A2)^0)=ЧСТРОК($A$2:A2));1)));"Вопросов нет!")
К сообщению приложен файл: 7159561.xlsx(13.3 Kb)


"Черт возьми, Холмс! Но как??!!"
ЯД 41001765434816
 
Ответить
СообщениеВот такой массивный монстр получился
Код
=ЕСЛИОШИБКА(ЕСЛИ(E2>0;ЕСЛИОШИБКА(АГРЕГАТ(15;6;Nзадания/(Nзадания>=B2+E2)/(МУМНОЖ(--(Nзадания*(Nзадания>=B2+E2)<>ТРАНСП($B$2:B2*($A$2:A2=A2)));СТРОКА($A$2:A2)^0)=ЧСТРОК($A$2:A2));1);АГРЕГАТ(14;6;Nзадания/(Nзадания<B2+E2)/(МУМНОЖ(--(Nзадания*(Nзадания<B2+E2)<>ТРАНСП($B$2:B2*($A$2:A2=A2)));СТРОКА($A$2:A2)^0)=ЧСТРОК($A$2:A2));1));ЕСЛИОШИБКА(АГРЕГАТ(14;6;Nзадания/(Nзадания<=B2+E2)/(МУМНОЖ(--(Nзадания*(Nзадания<=B2+E2)<>ТРАНСП($B$2:B2*($A$2:A2=A2)));СТРОКА($A$2:A2)^0)=ЧСТРОК($A$2:A2));1);АГРЕГАТ(15;6;Nзадания/(Nзадания>B2+E2)/(МУМНОЖ(--(Nзадания*(Nзадания>B2+E2)<>ТРАНСП($B$2:B2*($A$2:A2=A2)));СТРОКА($A$2:A2)^0)=ЧСТРОК($A$2:A2));1)));"Вопросов нет!")

Автор - Pelena
Дата добавления - 01.09.2019 в 22:57
Strateg_ru Дата: Понедельник, 02.09.2019, 21:27 | Сообщение № 7
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Вот такой массивный монстр получился

То, что монстр - это не страшно. Главное, чтобы работало. Протестировал по всякому, вроде действительно всё так как нужно.
Спасибо огромное! Похоже на сказку (результат есть, а как - непонятно)) hands
 
Ответить
Сообщение
Вот такой массивный монстр получился

То, что монстр - это не страшно. Главное, чтобы работало. Протестировал по всякому, вроде действительно всё так как нужно.
Спасибо огромное! Похоже на сказку (результат есть, а как - непонятно)) hands

Автор - Strateg_ru
Дата добавления - 02.09.2019 в 21:27
bmv98rus Дата: Вторник, 03.09.2019, 13:01 | Сообщение № 8
Группа: Проверенные
Ранг: Участник клуба
Сообщений: 2528
Репутация: 437 ±
Замечаний: 0% ±

Excel 2013/2016
Монстр длинный, но мне кажется более эффективный
Код
=IF(E2>0;
  IFERROR(-LOOKUP(-B2-E2;IF(ISERROR(MATCH(INDEX(Исходник!$A$2:$A$77;N(INDEX(77-ROW($A$1:$A$76);)));$B$1:B2*($A$1:A2=A2);));-INDEX(Исходник!$A$2:$A$77;N(INDEX(77-ROW($A$1:$A$76);)));1/0));
    IFERROR(LOOKUP(B2+E2;IF(ISERROR(MATCH(Исходник!$A$2:$A$77;$B$1:B2*($A$1:A2=A2);));Исходник!$A$2:$A$77;1/0)); "Расчет закончен")

  );
  IFERROR(LOOKUP(B2+E2;IF(ISERROR(MATCH(Исходник!$A$2:$A$77;$B$1:B2*($A$1:A2=A2);));Исходник!$A$2:$A$77;1/0));
    IFERROR(-LOOKUP(-B2-E2;IF(ISERROR(MATCH(INDEX(Исходник!$A$2:$A$77;N(INDEX(77-ROW($A$1:$A$76);)));$B$1:B2*($A$1:A2=A2);));-INDEX(Исходник!$A$2:$A$77;N(INDEX(77-ROW($A$1:$A$76);)));1/0));
      "Расчет закончен")
  )
)
К сообщению приложен файл: Copy_of_1124.xlsx(20.0 Kb)


Замечательный Временно просто медведь , процентов на 20.
 
Ответить
СообщениеМонстр длинный, но мне кажется более эффективный
Код
=IF(E2>0;
  IFERROR(-LOOKUP(-B2-E2;IF(ISERROR(MATCH(INDEX(Исходник!$A$2:$A$77;N(INDEX(77-ROW($A$1:$A$76);)));$B$1:B2*($A$1:A2=A2);));-INDEX(Исходник!$A$2:$A$77;N(INDEX(77-ROW($A$1:$A$76);)));1/0));
    IFERROR(LOOKUP(B2+E2;IF(ISERROR(MATCH(Исходник!$A$2:$A$77;$B$1:B2*($A$1:A2=A2);));Исходник!$A$2:$A$77;1/0)); "Расчет закончен")

  );
  IFERROR(LOOKUP(B2+E2;IF(ISERROR(MATCH(Исходник!$A$2:$A$77;$B$1:B2*($A$1:A2=A2);));Исходник!$A$2:$A$77;1/0));
    IFERROR(-LOOKUP(-B2-E2;IF(ISERROR(MATCH(INDEX(Исходник!$A$2:$A$77;N(INDEX(77-ROW($A$1:$A$76);)));$B$1:B2*($A$1:A2=A2);));-INDEX(Исходник!$A$2:$A$77;N(INDEX(77-ROW($A$1:$A$76);)));1/0));
      "Расчет закончен")
  )
)

Автор - bmv98rus
Дата добавления - 03.09.2019 в 13:01
Strateg_ru Дата: Среда, 04.09.2019, 10:19 | Сообщение № 9
Группа: Пользователи
Ранг: Участник
Сообщений: 65
Репутация: 0 ±
Замечаний: 0% ±

Excel 2013
Монстр длинный, но мне кажется более эффективный

Проверил разные комбинации. Да, тоже всё работает. И да, формула попроще! И Вам тоже огромное спасибо!
PS. Особенно порадовал кодерский синтаксис с разненением формул на строки с открывашками/закрывашками. Первый раз вижу такое оформление в экселе и оно действительно воспринимается нагляднее. За эту идею - отдельное спасибо!
 
Ответить
Сообщение
Монстр длинный, но мне кажется более эффективный

Проверил разные комбинации. Да, тоже всё работает. И да, формула попроще! И Вам тоже огромное спасибо!
PS. Особенно порадовал кодерский синтаксис с разненением формул на строки с открывашками/закрывашками. Первый раз вижу такое оформление в экселе и оно действительно воспринимается нагляднее. За эту идею - отдельное спасибо!

Автор - Strateg_ru
Дата добавления - 04.09.2019 в 10:19
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула выборки следующего значения исключая использованные (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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